V3

Using the code created in ticket 806, please optimize it and create a .pl script and cron job to run it quarterly. The date ranges to query for will be Jan 1- March 31; April 1 - June 30; July 1 - September 30; October 1 - December 31. the report will need to rescheduled for 12:30A on the first day of each quarter for the previous quarter's data. Upon completion, the report and email subject line should be Quarterly Policy List <dates covered> ie "Quarterly Policy List Jan 1 2025 - March 31 2025". Upon completion, the report should be emailed to Tina.henry@nfp.com

Code to optimize:

SELECT pd.policy_num,
       p.user_id,
       CONCAT(pdvr.first_name, ' ', pdvr.last_name) AS client_name,
       un.company_name AS carrier_name,
       pc.underwriter_premium AS carrier_premium,
       pc.opt_travel_premium AS mexvisit_premium,
       pc.evac_premium AS tme_premium,
       DATE_FORMAT(FROM_UNIXTIME(pd.sales_time), '%Y-%m') AS sales_date,
       afd.business_name AS affiliate_name,
       af.full_affid AS affiliate_id,
       NULL AS producer_commission_amount,
       ac.agent_premium AS affiliate_commission_rate,
       pdvr.city AS billing_city,
       s.name AS billing_state,
       pdvr.zip AS billing_postalcode,
       NULL AS classification,
       CASE
                  WHEN p.leave_date < UNIX_TIMESTAMP() THEN 'Expired'
                  ELSE 'Active'
       END AS policy_status
FROM policydetails pd
JOIN policies p ON pd.policy_id = p.id
JOIN underwriters un ON pd.underwriter_id = un.id
LEFT JOIN (
    SELECT policy_id,
           MAX(underwriter_premium) AS underwriter_premium,
           MAX(opt_travel_premium) AS opt_travel_premium,
           MAX(evac_premium) AS evac_premium
    FROM policyCosts
    GROUP BY policy_id
) pc ON pd.policy_id = pc.policy_id
LEFT JOIN (
    SELECT aff_id, underwriter_id, sales_type, MAX(agent_premium) AS agent_premium
    FROM affComm
    GROUP BY aff_id, underwriter_id, sales_type
) ac ON p.aff_id = ac.aff_id AND pd.underwriter_id = ac.underwriter_id AND pd.sales_type = ac.sales_type
JOIN affDetails afd ON p.aff_id = afd.aff_id
JOIN policydrivers pdvr ON pd.policy_id = pdvr.policy_id AND pdvr.isowner = 1
JOIN states s ON pdvr.state = s.id
JOIN aff af ON p.aff_id = af.id
WHERE pd.sales_time >= 1704067200
  AND pd.sales_time < 1735680000;