<?php

namespace Sts\CmsActivity\BO;

use DB;
use Sts\CmsActivity\Model\Partner;
use Sts\CmsActivity\Model\PartnerAddress;
use Sts\CmsActivity\Model\SalesmanVisitPlan;
use Sts\CmsActivity\Model\SalesmanVisitRealization;
use Sts\CmsCommon\Model\UserPartner;
use Sts\CmsOrder\Model\Order;
use Sts\CmsOrder\Model\OrderItem;
use Sts\PleafCore\BusinessFunction;
use Sts\PleafCore\CreateNativeQuery;
use Sts\PleafCore\DefaultBusinessFunction;
use Sts\PleafCore\QueryBuilder;

class GetRealizationVisitPlanList extends DefaultBusinessFunction implements BusinessFunction {

    public function getDescription(){
        return "Get Realization Visit Plan List";
    }

    public function process($dto){

        \Log::debug("DTO REALIZATION VISIT PLAN LIST");
        \Log::debug($dto);

        $tenant_id = $dto["record_owner_id"];
        $start_date = $dto["start_date"];
        $end_date = $dto["end_date"];
        $salesman_id = $dto["salesman_id"];
        $status = $dto["status"];
        $duration = $dto["duration"];

        $order_by = $dto["order_by"];
        $order_type = $dto["order_type"];

        $durationParams = '';
        if($duration == 'MAX'){
          $durationParams = '> 15';
        }else if($duration == 'MIDDLE'){
          $durationParams = 'BETWEEN 5 AND 15';
        }else if($duration == 'LESS'){
          $durationParams = '< 5';
        }

        if($order_by == NULL || $order_by == ''){
            $orderParams = 'visit_date DESC, status_visit DESC, check_in_time_server ASC';
        }else{
            $orderParams = $order_by.' '.$order_type;
        }

        $limit = $dto["limit"];
        $offset = ($dto["offset"] - 1) * $dto["limit"];

        if($status == 'A'){
            $status = '';
        }

        $queryBuilder = new QueryBuilder();
        $queryBuilder
                ->add(" SELECT ")
                ->add(" A.salesman_visit_plan_id, ")
                ->add(" A.salesman_visit_realization_id, ")
                ->add(" A.salesman_id, ")
                ->add(" A.customer_id, ")
                ->add(" A.salesman_name, ")
                ->add(" A.customer_name, ")
                ->add(" A.visit_date, ")
                ->add(" A.check_in_time_local, ")
                ->add(" A.check_in_time_server, ")
                ->add(" A.check_out_time_local, ")
                ->add(" A.check_out_time_server, ")
                ->add(" A.check_in_latitude, ")
                ->add(" A.check_in_longitude, ")
                ->add(" A.check_in_photo_file_name, ")
                ->add(" A.status_visit, ")
                ->add(" A.status, ")
                ->add(" A.final_total_received, ")
                ->add(" A.longitude as toko_longitude, ")
                ->add(" A.latitude as toko_latitude, ")
                ->add(" A.flg_get_order, ")
                ->add(" A.total_sku, ")
                ->add(" A.total_order_amount, ")
                ->add(" A.visit_duration ")
                ->add(" FROM ( ")
                ->add(" SELECT  ")
                ->add(" A.salesman_visit_plan_id, ")
                ->add(" A.salesman_visit_realization_id, ")
                ->add(" A.salesman_id, ")
                ->add(" A.customer_id, ")
                ->add(" C.partner_name as salesman_name, ")
                ->add(" D.partner_name as customer_name, ")
                ->add(" A.visit_date, ")
                ->add(" A.check_in_time_local, ")
                ->add(" A.check_in_time_server, ")
                ->add(" A.check_out_time_local, ")
                ->add(" A.check_out_time_server, ")
                ->add(" A.check_in_latitude, ")
                ->add(" A.check_in_longitude, ")
                ->add(" A.check_in_photo_file_name, ")
                ->add(" A.status_visit, ")
                ->add(" F.status, ")
                ->add(" F.final_total_received, ")
                ->add(" H.longitude, ")
                ->add(" H.latitude, ")
                ->add(" CASE WHEN COUNT(F) > 0 THEN 'Y' ELSE 'N' END flg_get_order, ")
                ->add(" COALESCE(COUNT(G), 0) as total_sku, ")
                ->add(" COALESCE(SUM(F.total_order_amount), 0) as total_order_amount, ")
                ->add(" COALESCE(TRUNC((A.check_out_time_server - A.check_in_time_server)/60), 0) as visit_duration ")
                ->add(" FROM ")->add(SalesmanVisitRealization::getTableName())->add(" A ")
                ->add(" JOIN ")->add(SalesmanVisitPlan::getTableName())->add(" B ")
                ->add(" ON A.salesman_visit_plan_id = B.salesman_visit_plan_id ")
                ->add(" JOIN ")->add(Partner::getTableName())->add(" C ")
                ->add(" ON A.salesman_id = C.partner_id AND A.tenant_id = C.tenant_id ")
                ->add(" JOIN ")->add(Partner::getTableName())->add(" D ")
                ->add(" ON A.customer_id = D.partner_id AND A.tenant_id = D.tenant_id ")
                ->add(" JOIN ")->add(UserPartner::getTableName())->add(" E ")
                ->add(" ON C.partner_id = E.partner_id AND A.tenant_id = E.tenant_id ")
                ->add(" LEFT JOIN ")->add(Order::getTableName())->add(" F ")
                ->add(" ON A.visit_date = F.doc_date AND A.tenant_id = F.tenant_id ")
                ->add(" AND E.user_id = F.salesman_user_id AND A.customer_id = F.toko_partner_id ")
                ->add(" LEFT JOIN ")->add(OrderItem::getTableName())->add(" G ")
                ->add(" ON F.order_id = G.order_id AND A.tenant_id = G.tenant_id ")
                ->add(" JOIN ")->add(PartnerAddress::getTableName())->add(" H ")
                ->add(" ON D.partner_id = H.partner_id AND A.tenant_id = H.tenant_id ")
                ->add(" WHERE A.tenant_id = :tenant_id ")
                ->add(" AND A.visit_date >= '".$start_date."' ")
                ->add(" AND A.visit_date <= '".$end_date."' ")
                ->addIfNotEmpty($salesman_id, " AND A.salesman_id = ".$salesman_id." ")
                ->addIfNotEmpty($status, " AND A.status_visit = '".$status."' ")
                ->add(" GROUP BY ")
                ->add(" A.salesman_visit_plan_id,   ")
                ->add(" A.salesman_visit_realization_id, ")
                ->add(" A.salesman_id, ")
                ->add(" A.customer_id, ")
                ->add(" A.visit_date, ")
                ->add(" C.partner_name, ")
                ->add(" D.partner_name,   ")
                ->add(" A.check_in_time_local,   ")
                ->add(" A.check_in_time_server,   ")
                ->add(" A.check_out_time_local,   ")
                ->add(" A.check_out_time_server,   ")
                ->add(" A.check_in_latitude,   ")
                ->add(" A.check_in_longitude,   ")
                ->add(" A.check_in_photo_file_name, ")
                ->add(" A.status_visit,   ")
                ->add(" F.status,   ")
                ->add(" F.final_total_received, ")
                ->add(" H.longitude, ")
                ->add(" H.latitude ")
                ->add(" ) A ")
                ->addIfNotEmpty($duration, " WHERE A.visit_duration $durationParams ")
                ->add(" ORDER BY ".$orderParams." ")
                ->add(" LIMIT :limit OFFSET :offset ");

        $query = new CreateNativeQuery($queryBuilder->toString());
        $query->setParameter("tenant_id", $tenant_id);
        $query->setParameter("limit", $limit);
        $query->setParameter("offset", $offset);

        \Log::debug("QUERY GET REALIZATION VISIT PLAN BY SALESMAN");
        \Log::debug($queryBuilder->toString());

        $result = $query->getResultList();

        return [
            "realization_visit_plan_list" => $result
        ];

    }
}