Marketer Books Dump

marketer-books-export.sql

SET @pay_period_year = 2015,
    @pay_period_number = 2,
    @pay_period_start = '2015-01-05',
    @pay_period_end = '2015-01-18';

SELECT
  t.marketer_id AS user_id
 ,CONCAT(u.last_name, ', ', u.first_name) AS name
 ,territories.abbr AS territory
 ,t.comp_type
 ,SUM(t.amount) AS pay_period_total
 ,1000.00 AS base_salary
 ,SUM(IF(t.comp_type = 'EstimateCommission', t.amount, 0.00)) - 1000.00 AS total_commission
 ,SUM(IF(t.comp_type <> 'EstimateCommission', t.amount, 0.00)) AS total_bonuses
 ,(SELECT COUNT(DISTINCT DATE(ma.start_time)) FROM marketer_activities ma INNER JOIN attendance_statuses st ON st.id = ma.attendance_status_id WHERE ma.user_id = t.marketer_id and st.working = 1 AND ma.start_time BETWEEN @pay_period_start AND @pay_period_end) AS days_work 
 ,(SELECT SUM(appointment_count) FROM marketer_summaries s WHERE s.marketer_id = t.marketer_id AND WEEK(s.day_affected,1) = MIN(WEEK(t.day_affected,1))) AS appts_week_1
 ,(SELECT SUM(estimate_count) FROM marketer_summaries s WHERE s.marketer_id = t.marketer_id AND WEEK(s.day_affected,1) = MIN(WEEK(t.day_affected,1))) AS ests_week_1
 ,(SELECT SUM(appointment_count) FROM marketer_summaries s WHERE s.marketer_id = t.marketer_id AND WEEK(s.day_affected,1) = MAX(WEEK(t.day_affected,1))) AS appts_week_2
 ,(SELECT SUM(estimate_count) FROM marketer_summaries s WHERE s.marketer_id = t.marketer_id AND WEEK(s.day_affected,1) = MAX(WEEK(t.day_affected,1))) AS ests_week_2
 ,SUM(IF(t.comp_type = 'DailyAppointment', t.amount, 0.00)) AS daily_appt_bonus
 ,SUM(IF(t.comp_type = 'WeeklyAppointment', t.amount, 0.00)) AS weekly_appt_bonus
 ,SUM(IF(t.comp_type = 'WeeklyEstimate', t.amount, 0.00)) AS weekly_est_bonus
 ,SUM(IF(t.comp_type = 'QuarterlyEstimate', t.amount, 0.00)) AS qtr_est_bonus
 ,SUM(IF(t.comp_type = 'YearlyEstimate', t.amount, 0.00)) AS yearly_est_bonus
 ,SUM(IF(t.comp_type = 'GrossMonthlySales', t.amount, 0.00)) AS monthly_gross_vol_bonus
 ,(SELECT SUM(gross_sales_amount) FROM marketer_summaries s WHERE s.marketer_id = t.marketer_id AND s.year_affected = t.year_affected AND s.pay_period_affected = t.pay_period_affected) AS monthly_gross_vol
FROM marketer_transactions t
 INNER JOIN users u ON u.id = t.marketer_id
 INNER JOIN territories ON territories.id = u.territory_id
WHERE
 t.year_affected = @pay_period_year
 AND t.pay_period_affected = @pay_period_number
# AND t.marketer_id = 1234
GROUP BY
 t.marketer_id, t.posting_year, t.posting_pay_period;