SELECT IFNULL(contacts.id,'') primaryid
,IFNULL(contacts.first_name,'') contacts_first_name
,IFNULL(contacts.last_name,'') contacts_last_name
,IFNULL(contacts.salutation,'') contacts_salutation,IFNULL(contacts.title,'') contacts_title
,contacts_cstm.middle_name_c contacts_cstm_middle_name_c,IFNULL(l2.id,'') l2_id
,IFNULL(l2.email_address,'') l2_email_address
,contacts.date_modified + INTERVAL 660 MINUTE contacts_date_modified,contacts.date_entered + INTERVAL 660 MINUTE contacts_date_entered,IFNULL(l3.id,'') l3_id
,IFNULL(l3.name,'') l3_name
,contacts_cstm.last_login_c + INTERVAL 660 MINUTE contacts_cstm_last_login_c,IFNULL(l1.id,'') l1_id
,IFNULL(l1.opportunity_type,'') l1_opportunity_type,IFNULL(l1.name,'') l1_name
,l1.date_closed l1_date_closed,l1.amount l1_amount , l1.currency_id l1_amount_currency,l1_cstm.amount_charged_c l1_cstm_amount_charged_c , l1.currency_id L1_CSTM_AMOUNT_CHARGEDE89427,l1_cstm.amount_remaining_c l1_cstm_amount_remaining_c , l1.currency_id L1_CSTM_AMOUNT_REMAINIC3C89B,IFNULL(contacts.department,'') contacts_department
,IFNULL(l1.sales_stage,'') l1_sales_stage
FROM contacts
 INNER JOIN  opportunities_contacts l1_1 ON contacts.id=l1_1.contact_id AND l1_1.deleted=0

 INNER JOIN  opportunities l1 ON l1.id=l1_1.opportunity_id AND l1.deleted=0
 INNER JOIN  email_addr_bean_rel l2_1 ON contacts.id=l2_1.bean_id AND l2_1.deleted=0
 AND l2_1.bean_module = 'Contacts' AND l2_1.primary_address = 1
 INNER JOIN  email_addresses l2 ON l2.id=l2_1.email_address_id AND l2.deleted=0
LEFT JOIN  tag_bean_rel l3_1 ON contacts.id=l3_1.bean_id AND l3_1.deleted=0
 AND l3_1.bean_module = 'Contacts'
LEFT JOIN  tags l3 ON l3.id=l3_1.tag_id AND l3.deleted=0
LEFT JOIN contacts_cstm contacts_cstm ON contacts.id = contacts_cstm.id_c
LEFT JOIN opportunities_cstm l1_cstm ON l1.id = l1_cstm.id_c

 WHERE (((l1.sales_stage IN ('Prospecting','Agreed_to_meet','Needs Analysis','Proposal_preparation','Proposal_presentation','Verbal_acceptance','Closed Won','Closed Lost','Do it Yourself','Signed up','Application Completed Subscribed','Application Sent to Adviser','Application Withdrawn After Payment','Application on hold','Application on hold after payment','Went to Centrelink','Application Approved','Application Rejected','Content Viewed','Direct Contact','Intention to Implement','Financial Planning Referral','Interested','Subscribed','Not Interested','Client Unresponsive','Expired','Booked','Paid','Payment reminder sent','Admin done')
) AND (l1.date_closed >= '2024-10-01' AND l1.date_closed <= '2024-10-31'
)))
AND  contacts.deleted=0
 ORDER BY CASE WHEN IFNULL(l1.sales_stage,'')='Prospecting' THEN 0
WHEN IFNULL(l1.sales_stage,'')='Agreed_to_meet' THEN 1
WHEN IFNULL(l1.sales_stage,'')='Needs Analysis' THEN 2
WHEN IFNULL(l1.sales_stage,'')='Proposal_preparation' THEN 3
WHEN IFNULL(l1.sales_stage,'')='Proposal_presentation' THEN 4
WHEN IFNULL(l1.sales_stage,'')='Verbal_acceptance' THEN 5
WHEN IFNULL(l1.sales_stage,'')='Closed Won' THEN 6
WHEN IFNULL(l1.sales_stage,'')='Closed Lost' THEN 7
WHEN IFNULL(l1.sales_stage,'')='Closed_Cancelled' THEN 8
WHEN IFNULL(l1.sales_stage,'')='Logged Guest' THEN 9
WHEN IFNULL(l1.sales_stage,'')='Eligible Guest' THEN 10
WHEN IFNULL(l1.sales_stage,'')='Eligible future date' THEN 11
WHEN IFNULL(l1.sales_stage,'')='Financially Ineligible Guest' THEN 12
WHEN IFNULL(l1.sales_stage,'')='Non financially Ineligible Age' THEN 13
WHEN IFNULL(l1.sales_stage,'')='Non financially Ineligible Guest Citizenship Residency' THEN 14
WHEN IFNULL(l1.sales_stage,'')='Do it Yourself' THEN 15
WHEN IFNULL(l1.sales_stage,'')='Signed up' THEN 16
WHEN IFNULL(l1.sales_stage,'')='Extended QA Complex Situation' THEN 17
WHEN IFNULL(l1.sales_stage,'')='Disqualified Residency' THEN 18
WHEN IFNULL(l1.sales_stage,'')='Disqualified Complex Situation' THEN 19
WHEN IFNULL(l1.sales_stage,'')='Application in progress 1 section completed' THEN 20
WHEN IFNULL(l1.sales_stage,'')='Application in progress 2 sections completed' THEN 21
WHEN IFNULL(l1.sales_stage,'')='Application in progress 3 sections completed' THEN 22
WHEN IFNULL(l1.sales_stage,'')='Application in progress 4 sections completed' THEN 23
WHEN IFNULL(l1.sales_stage,'')='Application in progress 5 sections completed' THEN 24
WHEN IFNULL(l1.sales_stage,'')='Ineligible 3 sections' THEN 25
WHEN IFNULL(l1.sales_stage,'')='Ineligible 5 sections' THEN 26
WHEN IFNULL(l1.sales_stage,'')='Application Completed Subscribed' THEN 27
WHEN IFNULL(l1.sales_stage,'')='Application Sent to Adviser' THEN 28
WHEN IFNULL(l1.sales_stage,'')='Application Withdrawn' THEN 29
WHEN IFNULL(l1.sales_stage,'')='Application Withdrawn After Payment' THEN 30
WHEN IFNULL(l1.sales_stage,'')='Application on hold' THEN 31
WHEN IFNULL(l1.sales_stage,'')='Application on hold after payment' THEN 32
WHEN IFNULL(l1.sales_stage,'')='Went to Centrelink' THEN 33
WHEN IFNULL(l1.sales_stage,'')='Application Approved' THEN 34
WHEN IFNULL(l1.sales_stage,'')='Application Rejected' THEN 35
WHEN IFNULL(l1.sales_stage,'')='Content Viewed' THEN 36
WHEN IFNULL(l1.sales_stage,'')='Direct Contact' THEN 37
WHEN IFNULL(l1.sales_stage,'')='Intention to Implement' THEN 38
WHEN IFNULL(l1.sales_stage,'')='Financial Planning Referral' THEN 39
WHEN IFNULL(l1.sales_stage,'')='Interested' THEN 40
WHEN IFNULL(l1.sales_stage,'')='Subscribed' THEN 41
WHEN IFNULL(l1.sales_stage,'')='Not Interested' THEN 42
WHEN IFNULL(l1.sales_stage,'')='Client Unresponsive' THEN 43
WHEN IFNULL(l1.sales_stage,'')='Expired' THEN 44
WHEN IFNULL(l1.sales_stage,'')='Booked' THEN 45
WHEN IFNULL(l1.sales_stage,'')='Paid' THEN 46
WHEN IFNULL(l1.sales_stage,'')='Payment reminder sent' THEN 47
WHEN IFNULL(l1.sales_stage,'')='Cancelled due to non payment' THEN 48
WHEN IFNULL(l1.sales_stage,'')='Cancelled before payment at clients request' THEN 49
WHEN IFNULL(l1.sales_stage,'')='Cancelled after payment' THEN 50
WHEN IFNULL(l1.sales_stage,'')='Cancelled' THEN 51
WHEN IFNULL(l1.sales_stage,'')='Completed' THEN 52
WHEN IFNULL(l1.sales_stage,'')='Admin done' THEN 53 ELSE 54 END ASC
,CASE WHEN (IFNULL(l1.opportunity_type,'')='' OR IFNULL(l1.opportunity_type,'') IS NULL) THEN 0
WHEN IFNULL(l1.opportunity_type,'')='Calendly_Booking' THEN 1
WHEN IFNULL(l1.opportunity_type,'')='Age Pension Membership' THEN 2
WHEN IFNULL(l1.opportunity_type,'')='Age Pension phone app' THEN 3
WHEN IFNULL(l1.opportunity_type,'')='Keeping Your Pension' THEN 4
WHEN IFNULL(l1.opportunity_type,'')='Health Card Application' THEN 5
WHEN IFNULL(l1.opportunity_type,'')='Health Card phone app' THEN 6
WHEN IFNULL(l1.opportunity_type,'')='CSHC Standard Application' THEN 7
WHEN IFNULL(l1.opportunity_type,'')='Phone_Application' THEN 8
WHEN IFNULL(l1.opportunity_type,'')='Claim_Review' THEN 9
WHEN IFNULL(l1.opportunity_type,'')='Entitlements Consultation' THEN 10
WHEN IFNULL(l1.opportunity_type,'')='Financial Advice' THEN 11
WHEN IFNULL(l1.opportunity_type,'')='Financial Advice Strategy' THEN 12
WHEN IFNULL(l1.opportunity_type,'')='Asset_Organisation_Advice' THEN 13
WHEN IFNULL(l1.opportunity_type,'')='Debt_Advice' THEN 14
WHEN IFNULL(l1.opportunity_type,'')='Debt_Becoming_Eligible_Advice' THEN 15
WHEN IFNULL(l1.opportunity_type,'')='Personal_Assets_Advice' THEN 16
WHEN IFNULL(l1.opportunity_type,'')='Retirement_Health_Check' THEN 17
WHEN IFNULL(l1.opportunity_type,'')='Webinar' THEN 18
WHEN IFNULL(l1.opportunity_type,'')='ManagedAccount' THEN 19
WHEN IFNULL(l1.opportunity_type,'')='B2B_Partnership' THEN 20
WHEN IFNULL(l1.opportunity_type,'')='Nom Ben' THEN 21
WHEN IFNULL(l1.opportunity_type,'')='Product_consulting' THEN 22
WHEN IFNULL(l1.opportunity_type,'')='Other_consulting' THEN 23
WHEN IFNULL(l1.opportunity_type,'')='Advice_Triage' THEN 24
WHEN IFNULL(l1.opportunity_type,'')='Website' THEN 25
WHEN IFNULL(l1.opportunity_type,'')='Other' THEN 26 ELSE 27 END ASC
,l1_date_closed DESC;
