API tools faq
paste
Advertisement
ankdroid

MNF-CRM Report

ankdroid
Aug 25th, 2023
1,304
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 11.67 KB | None | 0 0
raw download clone embed print report
  1. -- plan -- adhoc plan -- create date < schedule date
  2. -- adhoc -- adhoc plan -- create date + 7 = schedule date
  3.  
  4. select
  5.     rc.name as KS,
  6.     rb.name as branch_name,
  7.     cl.user_id ,
  8.     ru.login ,
  9.     cl.schedule_date,
  10.     count(cl.id) as activity_per_day,
  11.     coalesce(max(cap.total_adhoc),0) as total_adhoc,
  12.     coalesce(max(cap.call_adhoc),0) as call_adhoc,
  13.     coalesce(max(cap.visit_adhoc),0) as visit_adhoc,
  14. --  EXTRACT(DOW FROM cl.schedule_date) AS day_of_week_number,
  15. --  CASE
  16. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 0 THEN 'Sunday'
  17. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 1 THEN 'Monday'
  18. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 2 THEN 'Tuesday'
  19. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 3 THEN 'Wednesday'
  20. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 4 THEN 'Thursday'
  21. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 5 THEN 'Friday'
  22. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 6 THEN 'Saturday'
  23. --    END AS day_of_week_name
  24.     case when count(cl.id) - max(cap.total_adhoc) > 0 then count(cl.id) - max(cap.total_adhoc) else count(cl.id) end as system_generated,
  25.     coalesce(max(cap.customer_adhoc),0) as customer_adhoc,
  26.     coalesce(max(cap.leads_adhoc),0) as leads_adhoc,
  27.     SUM(CASE WHEN cl.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as complete,
  28.     SUM(CASE WHEN cl.status_crm_pipeline != 'close_and_complete' THEN 1 ELSE 0 END) as incomplete,
  29.     SUM(CASE WHEN cl.status_meet_visit = 'meet_customer' and cl.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as meet,
  30.     SUM(CASE WHEN cl.status_meet_visit != 'meet_customer' and cl.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as not_meet
  31. from crm_lead cl
  32. left join (
  33.     select
  34.         cap2.schedule_date,
  35.         cap2.user_id,
  36.         count(cap2.id) as total_adhoc,
  37.         SUM(CASE WHEN cl2.type_pipeline like '%call%' THEN 1 ELSE 0 END) as call_adhoc,
  38.         SUM(CASE WHEN cl2.type_pipeline like '%visit%' THEN 1 ELSE 0 END) as visit_adhoc,
  39.         SUM(CASE WHEN cap2.type_pipeline = 'visit_plan_customer' THEN 1 ELSE 0 END) as customer_adhoc,
  40.         SUM(CASE WHEN cap2.type_pipeline = 'visit_plan_leads' THEN 1 ELSE 0 END) as leads_adhoc
  41.     from crm_adhoc_plan cap2
  42.     left join crm_lead cl2 on cap2.pipeline_id = cl2.id
  43.     where cap2.type_pipeline in ('visit_plan_customer','visit_plan_leads') and TO_CHAR((cap2.create_date + INTERVAL '7 hours')::DATE, 'YYYY-MM-DD') < TO_CHAR(cap2.schedule_date, 'YYYY-MM-DD')
  44.     group by
  45.         cap2.schedule_date,
  46.         cap2.user_id) cap on cl.user_id  = cap.user_id and cl.schedule_date = cap.schedule_date
  47. left join res_users ru on cl.user_id = ru.id
  48. left join res_partner rp on cl.partner_id = rp.id
  49. left join crm_lead_pool clp ON cl.crm_lead_pool_id = clp.id
  50. left join res_branch rb on ru.branch_id = rb.id
  51. left join master_company_mnf rc on rb.company_id = rc.id
  52. --where
  53. where cl.schedule_date between '2023-08-01' and '2023-08-25'
  54. and ru.login = 'htr-pwb-01'
  55. group by cl.user_id , cl.schedule_date, ru.login, rc.name, rb.name
  56. order by cl.user_id ,cl.schedule_date asc
  57.  
  58. ---------------------------------------------------------------------------------------------------
  59.  
  60. --adhoc hunter
  61.  
  62. select
  63.     rc.name as KS,
  64.     rb.name as branch_name,
  65.     ru.login ,
  66.     cap.user_id ,
  67.     cap.schedule_date ,
  68.     count(cl_customer.id) as adhoc_customer,
  69.     count(cl_lead.id) as adhoc_lead,
  70.     count(cl_customer.id) + count(cl_lead.id) as total_adhoc,
  71.     SUM(CASE WHEN cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as complete_customer,
  72.     SUM(CASE WHEN cl_customer.status_crm_pipeline != 'close_and_complete' THEN 1 ELSE 0 END) as incomplete_customer,
  73.     SUM(CASE WHEN cl_lead.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as complete_lead,
  74.     SUM(CASE WHEN cl_lead.status_crm_pipeline != 'close_and_complete' THEN 1 ELSE 0 END) as incomplete_lead,
  75.     SUM(CASE WHEN cl_customer.status_meet_visit = 'meet_customer' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as meet_customer,
  76.     SUM(CASE WHEN cl_customer.status_meet_visit != 'meet_customer' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as not_meet_customer,
  77.     SUM(CASE WHEN cl_lead.status_meet_visit = 'meet_customer' and cl_lead.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as meet_lead,
  78.     SUM(CASE WHEN cl_lead.status_meet_visit != 'meet_customer' and cl_lead.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as not_meet_lead
  79. from crm_adhoc_plan cap
  80. left join crm_lead cl_customer on cap.schedule_date = cl_customer.schedule_date and cap.user_id = cl_customer.user_id and cap.partner_id = cl_customer.partner_id
  81. left join crm_lead cl_lead on cap.schedule_date = cl_lead.schedule_date and cap.user_id = cl_lead.user_id and cap.crm_lead_pool_id  = cl_lead.crm_lead_pool_id
  82. left join res_users ru on cap.user_id = ru.id
  83. left join res_partner rp on cap.partner_id = rp.id
  84. left join crm_lead_pool clp ON cap.crm_lead_pool_id = clp.id
  85. left join res_branch rb on ru.branch_id = rb.id
  86. left join master_company_mnf rc on rb.company_id = rc.id
  87. where cap.schedule_date  between '2023-08-01' and '2023-08-16' and cap.type_pipeline in ('visit_plan_customer','visit_plan_leads')
  88. and TO_CHAR((cap.create_date + INTERVAL '7 hours')::DATE, 'YYYY-MM-DD') = TO_CHAR(cap.schedule_date, 'YYYY-MM-DD')
  89. group by cap.schedule_date, cap.user_id ,rc.name, rb.name,ru.login
  90.    
  91.  
  92. ------------------------------------------------------------
  93. -- farmer
  94.  
  95. select
  96.     rc.name as KS,
  97.     rb.name as branch_name,
  98.     cl.user_id ,
  99.     ru.login ,
  100.     cl.schedule_date,
  101.     count(cl.id) as activity_per_day,
  102.     coalesce(max(cap.total_adhoc),0) as total_adhoc,
  103.     coalesce(max(cap.call_adhoc),0) as call_adhoc,
  104.     coalesce(max(cap.visit_adhoc),0) as visit_adhoc,
  105. --  EXTRACT(DOW FROM cl.schedule_date) AS day_of_week_number,
  106. --  CASE
  107. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 0 THEN 'Sunday'
  108. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 1 THEN 'Monday'
  109. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 2 THEN 'Tuesday'
  110. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 3 THEN 'Wednesday'
  111. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 4 THEN 'Thursday'
  112. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 5 THEN 'Friday'
  113. --        WHEN EXTRACT(DOW FROM cl.schedule_date) = 6 THEN 'Saturday'
  114. --    END AS day_of_week_name
  115.     case when count(cl.id) - max(cap.total_adhoc) > 0 then count(cl.id) - max(cap.total_adhoc) else count(cl.id) end as system_generated,
  116.     coalesce(max(cap.customer_adhoc),0) as customer_adhoc,
  117.     coalesce(max(cap.leads_adhoc),0) as leads_adhoc,
  118.     SUM(CASE WHEN cl.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as complete,
  119.     SUM(CASE WHEN cl.status_crm_pipeline != 'close_and_complete' THEN 1 ELSE 0 END) as incomplete,
  120.     SUM(CASE WHEN cl.status_call = 'invalid' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as invalid,
  121.     SUM(CASE WHEN cl.status_call = 'not_answer' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as not_answer,
  122.     SUM(CASE WHEN cl.status_call = 'answer' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as answer,
  123.     SUM(CASE WHEN cl.status_call = 'chat_only' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as chat_only
  124. from crm_lead cl
  125. left join (
  126.     select
  127.         cap2.schedule_date,
  128.         cap2.user_id,
  129.         count(cap2.id) as total_adhoc,
  130.         SUM(CASE WHEN cl2.type_pipeline like '%call%' THEN 1 ELSE 0 END) as call_adhoc,
  131.         SUM(CASE WHEN cl2.type_pipeline like '%visit%' THEN 1 ELSE 0 END) as visit_adhoc,
  132.         SUM(CASE WHEN cap2.type_pipeline = 'call_plan_customer' THEN 1 ELSE 0 END) as customer_adhoc,
  133.         SUM(CASE WHEN cap2.type_pipeline = 'call_plan_leads' THEN 1 ELSE 0 END) as leads_adhoc
  134.     from crm_adhoc_plan cap2
  135.     left join crm_lead cl2 on cap2.pipeline_id = cl2.id
  136.     where cap2.type_pipeline in ('call_plan_customer','call_plan_leads') and TO_CHAR((cap2.create_date + INTERVAL '7 hours')::DATE, 'YYYY-MM-DD') < TO_CHAR(cap2.schedule_date, 'YYYY-MM-DD')
  137.     group by
  138.         cap2.schedule_date,
  139.         cap2.user_id) cap on cl.user_id  = cap.user_id and cl.schedule_date = cap.schedule_date
  140. left join res_users ru on cl.user_id = ru.id
  141. left join res_partner rp on cl.partner_id = rp.id
  142. left join crm_lead_pool clp ON cl.crm_lead_pool_id = clp.id
  143. left join res_branch rb on ru.branch_id = rb.id
  144. left join master_company_mnf rc on rb.company_id = rc.id
  145. --where
  146. where cl.schedule_date between '2023-08-01' and '2023-08-16'
  147. and cl.type_pipeline in ('call_plan_customer','call_plan_leads')
  148. --and cl.user_id = 6
  149. group by cl.user_id , cl.schedule_date, ru.login, rc.name, rb.name
  150. order by cl.user_id ,cl.schedule_date asc
  151.    
  152.  
  153.  
  154.  
  155. ---------------------------------------------------------------------------------------------------
  156.  
  157. --adhoc farmer
  158.  
  159. select
  160.     rc.name as KS,
  161.     rb.name as branch_name,
  162.     ru.login ,
  163.     cap.user_id ,
  164.     cap.schedule_date ,
  165.     count(cl_customer.id) as adhoc_customer,
  166.     count(cl_lead.id) as adhoc_lead,
  167.     count(cl_customer.id) + count(cl_lead.id) as total_adhoc,
  168.     SUM(CASE WHEN cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as complete_customer,
  169.     SUM(CASE WHEN cl_customer.status_crm_pipeline != 'close_and_complete' THEN 1 ELSE 0 END) as incomplete_customer,
  170.     SUM(CASE WHEN cl_lead.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as complete_lead,
  171.     SUM(CASE WHEN cl_lead.status_crm_pipeline != 'close_and_complete' THEN 1 ELSE 0 END) as incomplete_lead,
  172.     SUM(CASE WHEN cl_customer.status_call = 'invalid' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as invalid_customer,
  173.     SUM(CASE WHEN cl_customer.status_call = 'not_answer' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as not_answer_customer,
  174.     SUM(CASE WHEN cl_customer.status_call = 'answer' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as answer_customer,
  175.     SUM(CASE WHEN cl_customer.status_call = 'chat_only' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as chat_only_customer,
  176.     SUM(CASE WHEN cl_lead.status_call = 'invalid' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as invalid_lead,
  177.     SUM(CASE WHEN cl_lead.status_call = 'not_answer' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as not_answer_lead,
  178.     SUM(CASE WHEN cl_lead.status_call = 'answer' and cl_customer.status_crm_pipeline = 'close_and_complete'THEN 1 ELSE 0 END) as answer_lead,
  179.     SUM(CASE WHEN cl_lead.status_call = 'chat_only' and cl_customer.status_crm_pipeline = 'close_and_complete' THEN 1 ELSE 0 END) as chat_only_lead
  180. --  SUM(CASE WHEN cl.status_crm_pipeline != 'close_and_complete' THEN 1 ELSE 0 END) as incomplete,
  181. --  SUM(CASE WHEN cl.status_meet_visit = 'meet_customer' THEN 1 ELSE 0 END) as meet,
  182. --  SUM(CASE WHEN cl.status_meet_visit != 'meet_customer' THEN 1 ELSE 0 END) as not_meet
  183. from crm_adhoc_plan cap
  184. left join crm_lead cl_customer on cap.schedule_date = cl_customer.schedule_date and cap.user_id = cl_customer.user_id and cap.partner_id = cl_customer.partner_id
  185. left join crm_lead cl_lead on cap.schedule_date = cl_lead.schedule_date and cap.user_id = cl_lead.user_id and cap.crm_lead_pool_id  = cl_lead.crm_lead_pool_id
  186. left join res_users ru on cap.user_id = ru.id
  187. left join res_partner rp on cap.partner_id = rp.id
  188. left join crm_lead_pool clp ON cap.crm_lead_pool_id = clp.id
  189. left join res_branch rb on ru.branch_id = rb.id
  190. left join master_company_mnf rc on rb.company_id = rc.id
  191. where cap.schedule_date  between '2023-08-01' and '2023-08-16' and cap.type_pipeline in ('call_plan_customer','call_plan_leads')
  192. and TO_CHAR((cap.create_date + INTERVAL '7 hours')::DATE, 'YYYY-MM-DD') = TO_CHAR(cap.schedule_date, 'YYYY-MM-DD')
  193. group by cap.schedule_date, cap.user_id ,rc.name, rb.name,ru.login
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!