SHARE
TWEET

Untitled

a guest Dec 9th, 2019 75 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3. namespace Sts\CmsActivity\BO;
  4.  
  5. use DB;
  6. use Sts\CmsActivity\Model\Partner;
  7. use Sts\CmsActivity\Model\PartnerAddress;
  8. use Sts\CmsActivity\Model\SalesmanVisitPlan;
  9. use Sts\CmsActivity\Model\SalesmanVisitRealization;
  10. use Sts\CmsCommon\Model\UserPartner;
  11. use Sts\CmsOrder\Model\Order;
  12. use Sts\CmsOrder\Model\OrderItem;
  13. use Sts\PleafCore\BusinessFunction;
  14. use Sts\PleafCore\CreateNativeQuery;
  15. use Sts\PleafCore\DefaultBusinessFunction;
  16. use Sts\PleafCore\QueryBuilder;
  17.  
  18. class GetRealizationVisitPlanList extends DefaultBusinessFunction implements BusinessFunction {
  19.  
  20.     public function getDescription(){
  21.         return "Get Realization Visit Plan List";
  22.     }
  23.  
  24.     public function process($dto){
  25.  
  26.         \Log::debug("DTO REALIZATION VISIT PLAN LIST");
  27.         \Log::debug($dto);
  28.  
  29.         $tenant_id = $dto["record_owner_id"];
  30.         $start_date = $dto["start_date"];
  31.         $end_date = $dto["end_date"];
  32.         $salesman_id = $dto["salesman_id"];
  33.         $status = $dto["status"];
  34.         $duration = $dto["duration"];
  35.  
  36.         $order_by = $dto["order_by"];
  37.         $order_type = $dto["order_type"];
  38.  
  39.         $durationParams = '';
  40.         if($duration == 'MAX'){
  41.           $durationParams = '> 15';
  42.         }else if($duration == 'MIDDLE'){
  43.           $durationParams = 'BETWEEN 5 AND 15';
  44.         }else if($duration == 'LESS'){
  45.           $durationParams = '< 5';
  46.         }
  47.  
  48.         if($order_by == NULL || $order_by == ''){
  49.             $orderParams = 'visit_date DESC, status_visit DESC, check_in_time_server ASC';
  50.         }else{
  51.             $orderParams = $order_by.' '.$order_type;
  52.         }
  53.  
  54.         $limit = $dto["limit"];
  55.         $offset = ($dto["offset"] - 1) * $dto["limit"];
  56.  
  57.         if($status == 'A'){
  58.             $status = '';
  59.         }
  60.  
  61.         $queryBuilder = new QueryBuilder();
  62.         $queryBuilder
  63.                 ->add(" SELECT ")
  64.                 ->add(" A.salesman_visit_plan_id, ")
  65.                 ->add(" A.salesman_visit_realization_id, ")
  66.                 ->add(" A.salesman_id, ")
  67.                 ->add(" A.customer_id, ")
  68.                 ->add(" A.salesman_name, ")
  69.                 ->add(" A.customer_name, ")
  70.                 ->add(" A.visit_date, ")
  71.                 ->add(" A.check_in_time_local, ")
  72.                 ->add(" A.check_in_time_server, ")
  73.                 ->add(" A.check_out_time_local, ")
  74.                 ->add(" A.check_out_time_server, ")
  75.                 ->add(" A.check_in_latitude, ")
  76.                 ->add(" A.check_in_longitude, ")
  77.                 ->add(" A.check_in_photo_file_name, ")
  78.                 ->add(" A.status_visit, ")
  79.                 ->add(" A.status, ")
  80.                 ->add(" A.final_total_received, ")
  81.                 ->add(" A.longitude as toko_longitude, ")
  82.                 ->add(" A.latitude as toko_latitude, ")
  83.                 ->add(" A.flg_get_order, ")
  84.                 ->add(" A.total_sku, ")
  85.                 ->add(" A.total_order_amount, ")
  86.                 ->add(" A.visit_duration ")
  87.                 ->add(" FROM ( ")
  88.                 ->add(" SELECT  ")
  89.                 ->add(" A.salesman_visit_plan_id, ")
  90.                 ->add(" A.salesman_visit_realization_id, ")
  91.                 ->add(" A.salesman_id, ")
  92.                 ->add(" A.customer_id, ")
  93.                 ->add(" C.partner_name as salesman_name, ")
  94.                 ->add(" D.partner_name as customer_name, ")
  95.                 ->add(" A.visit_date, ")
  96.                 ->add(" A.check_in_time_local, ")
  97.                 ->add(" A.check_in_time_server, ")
  98.                 ->add(" A.check_out_time_local, ")
  99.                 ->add(" A.check_out_time_server, ")
  100.                 ->add(" A.check_in_latitude, ")
  101.                 ->add(" A.check_in_longitude, ")
  102.                 ->add(" A.check_in_photo_file_name, ")
  103.                 ->add(" A.status_visit, ")
  104.                 ->add(" F.status, ")
  105.                 ->add(" F.final_total_received, ")
  106.                 ->add(" H.longitude, ")
  107.                 ->add(" H.latitude, ")
  108.                 ->add(" CASE WHEN COUNT(F) > 0 THEN 'Y' ELSE 'N' END flg_get_order, ")
  109.                 ->add(" COALESCE(COUNT(G), 0) as total_sku, ")
  110.                 ->add(" COALESCE(SUM(F.total_order_amount), 0) as total_order_amount, ")
  111.                 ->add(" COALESCE(TRUNC((A.check_out_time_server - A.check_in_time_server)/60), 0) as visit_duration ")
  112.                 ->add(" FROM ")->add(SalesmanVisitRealization::getTableName())->add(" A ")
  113.                 ->add(" JOIN ")->add(SalesmanVisitPlan::getTableName())->add(" B ")
  114.                 ->add(" ON A.salesman_visit_plan_id = B.salesman_visit_plan_id ")
  115.                 ->add(" JOIN ")->add(Partner::getTableName())->add(" C ")
  116.                 ->add(" ON A.salesman_id = C.partner_id AND A.tenant_id = C.tenant_id ")
  117.                 ->add(" JOIN ")->add(Partner::getTableName())->add(" D ")
  118.                 ->add(" ON A.customer_id = D.partner_id AND A.tenant_id = D.tenant_id ")
  119.                 ->add(" JOIN ")->add(UserPartner::getTableName())->add(" E ")
  120.                 ->add(" ON C.partner_id = E.partner_id AND A.tenant_id = E.tenant_id ")
  121.                 ->add(" LEFT JOIN ")->add(Order::getTableName())->add(" F ")
  122.                 ->add(" ON A.visit_date = F.doc_date AND A.tenant_id = F.tenant_id ")
  123.                 ->add(" AND E.user_id = F.salesman_user_id AND A.customer_id = F.toko_partner_id ")
  124.                 ->add(" LEFT JOIN ")->add(OrderItem::getTableName())->add(" G ")
  125.                 ->add(" ON F.order_id = G.order_id AND A.tenant_id = G.tenant_id ")
  126.                 ->add(" JOIN ")->add(PartnerAddress::getTableName())->add(" H ")
  127.                 ->add(" ON D.partner_id = H.partner_id AND A.tenant_id = H.tenant_id ")
  128.                 ->add(" WHERE A.tenant_id = :tenant_id ")
  129.                 ->add(" AND A.visit_date >= '".$start_date."' ")
  130.                 ->add(" AND A.visit_date <= '".$end_date."' ")
  131.                 ->addIfNotEmpty($salesman_id, " AND A.salesman_id = ".$salesman_id." ")
  132.                 ->addIfNotEmpty($status, " AND A.status_visit = '".$status."' ")
  133.                 ->add(" GROUP BY ")
  134.                 ->add(" A.salesman_visit_plan_id,   ")
  135.                 ->add(" A.salesman_visit_realization_id, ")
  136.                 ->add(" A.salesman_id, ")
  137.                 ->add(" A.customer_id, ")
  138.                 ->add(" A.visit_date, ")
  139.                 ->add(" C.partner_name, ")
  140.                 ->add(" D.partner_name,   ")
  141.                 ->add(" A.check_in_time_local,   ")
  142.                 ->add(" A.check_in_time_server,   ")
  143.                 ->add(" A.check_out_time_local,   ")
  144.                 ->add(" A.check_out_time_server,   ")
  145.                 ->add(" A.check_in_latitude,   ")
  146.                 ->add(" A.check_in_longitude,   ")
  147.                 ->add(" A.check_in_photo_file_name, ")
  148.                 ->add(" A.status_visit,   ")
  149.                 ->add(" F.status,   ")
  150.                 ->add(" F.final_total_received, ")
  151.                 ->add(" H.longitude, ")
  152.                 ->add(" H.latitude ")
  153.                 ->add(" ) A ")
  154.                 ->addIfNotEmpty($duration, " WHERE A.visit_duration $durationParams ")
  155.                 ->add(" ORDER BY ".$orderParams." ")
  156.                 ->add(" LIMIT :limit OFFSET :offset ");
  157.  
  158.         $query = new CreateNativeQuery($queryBuilder->toString());
  159.         $query->setParameter("tenant_id", $tenant_id);
  160.         $query->setParameter("limit", $limit);
  161.         $query->setParameter("offset", $offset);
  162.  
  163.         \Log::debug("QUERY GET REALIZATION VISIT PLAN BY SALESMAN");
  164.         \Log::debug($queryBuilder->toString());
  165.  
  166.         $result = $query->getResultList();
  167.  
  168.         return [
  169.             "realization_visit_plan_list" => $result
  170.         ];
  171.  
  172.     }
  173. }
RAW Paste Data
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
 
Top