API tools faq
paste
ankdroid

Untitled

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