<?php

namespace Sts\CmsActivity\BO;

use Sts\PleafCore\BusinessFunction;
use Sts\PleafCore\CreateNativeQuery;
use Sts\PleafCore\DefaultBusinessFunction;
use DB;
use Log;
use Sts\PleafCore\QueryBuilder;

/**
 *
 * @in
 *
 * @out
 */
class GetRealizationVisitPlanSummary extends DefaultBusinessFunction implements BusinessFunction {

    public function getDescription(){
    	return "change here";
    }

    public function process($dto){

        $output_total_visit_plan = self::getTotalVisitPlan($dto);
        $output_total_realization_visit_plan = self::getTotalRealizationVisitPlan($dto);
        $output_total_customer_order = self::getTotalCustomerOrder($dto);


        Log::info('A TOTAL VISIT PLAN');
        Log::debug($output_total_visit_plan);
        Log::info('A TOTAL REALIZATION VISIT PLAN');
        Log::debug($output_total_realization_visit_plan);
        Log::info('A TOTAL CUSTOMER ORDER');
        Log::debug($output_total_customer_order);

        $total_visit_plan = $output_total_visit_plan['total_visit_plan']->count;
        $total_realization_visit_plan = $output_total_realization_visit_plan['total_realization_visit_plan']->count;
        $total_customer_order = $output_total_customer_order['total_customer_order']->count;

        Log::info('TOTAL VISIT PLAN');
        Log::debug($total_visit_plan);
        Log::info('TOTAL REALIZATION VISIT PLAN');
        Log::debug($total_realization_visit_plan);
        Log::info('TOTAL CUSTOMER ORDER');
        Log::debug($total_customer_order);

        $realization_visit_plan_percentage = ($total_visit_plan != 0) ? number_format(($total_realization_visit_plan / $total_visit_plan) * 100, 2) : 0;
        $customer_order_percentage = ($total_visit_plan != 0) ? number_format(($total_customer_order / $total_visit_plan) * 100, 2) : 0;


        return[
          "total_visit_plan" => $total_visit_plan,
          "total_realization_visit_plan" => $total_realization_visit_plan,
          "total_order_customer" => $total_customer_order,
          "realization_visit_plan_percentage" => $realization_visit_plan_percentage,
          "order_customer_percentage" => $customer_order_percentage
        ];

    }


    private function getTotalVisitPlan($input){

      $user_id      = $input['user_id'];
      $tenant_id    = $input['tenant_id'];
      $ou_id        = $input['ou_id'];
      $salesman_id  = $input['salesman_id'];
      $date_from    = $input['date_from'];
      $date_to      = $input['date_to'];

      \Log::info('QUERY TOTAL RENCANA KUNJUNGAN');
      $queryBuilder = new QueryBuilder();
      $queryBuilder
        ->add(" SELECT COUNT(1) ")
        ->add(" FROM sl_salesman_visit_plan A ")
        ->add(" INNER JOIN m_partner_ou B ON A.salesman_id = B.partner_id ")
        ->add(" WHERE EXISTS( ")
        ->add(" SELECT 1 ")
        ->add(" FROM t_user X, t_user_role Y, t_policy_ou Z ")
        ->add(" WHERE X.tenant_id = A.tenant_id AND ")
        ->add(" X.user_id = $user_id AND ")
        ->add(" X.user_id = Y.user_id AND ")
        ->add(" X.policy_default_id = Z.policy_id AND ")
        ->add(" B.ou_id = Z.ou_id ")
        ->add(" ) AND  A.tenant_id = $tenant_id ")
        ->add(" AND A.visit_date >= '".$date_from."' ")
        ->add(" AND A.visit_date <= '".$date_to."' ")
        ->addIfNotEmpty($salesman_id, " AND A.salesman_id = $salesman_id ")
        ->addIfNotEmpty($ou_id, " AND B.ou_id = $ou_id ");

      \Log::debug($queryBuilder->toString());
      $query = new CreateNativeQuery($queryBuilder->toString());

      $result = $query->getFirstRow();

      return [
        "total_visit_plan" => $result
      ];
    }


    private function getTotalRealizationVisitPlan($input){

      $user_id      = $input['user_id'];
      $tenant_id    = $input['tenant_id'];
      $ou_id        = $input['ou_id'];
      $salesman_id  = $input['salesman_id'];
      $date_from    = $input['date_from'];
      $date_to      = $input['date_to'];

      \Log::info('QUERY TOTAL REALISASI RENCANA KUNJUNGAN');
      $queryBuilder = new QueryBuilder();
      $queryBuilder
        ->add(" SELECT COUNT(1) ")
        ->add(" FROM sl_salesman_visit_plan A ")
        ->add(" INNER JOIN m_partner_ou B ON A.salesman_id = B.partner_id ")
        ->add(" INNER JOIN sl_salesman_visit_realization C ON A.salesman_id = C.salesman_id ")
        ->add(" WHERE EXISTS( ")
        ->add(" SELECT 1 ")
        ->add(" FROM t_user X, t_user_role Y, t_policy_ou Z ")
        ->add(" WHERE X.tenant_id = A.tenant_id AND ")
        ->add(" X.user_id = $user_id AND ")
        ->add(" X.user_id = Y.user_id AND ")
        ->add(" X.policy_default_id = Z.policy_id AND ")
        ->add(" B.ou_id = Z.ou_id ")
        ->add(" ) AND  A.tenant_id = $tenant_id ")
        ->add(" AND A.visit_date >= '".$date_from."' ")
        ->add(" AND A.visit_date <= '".$date_to."' ")
        ->addIfNotEmpty($salesman_id, " AND A.salesman_id = $salesman_id ")
        ->addIfNotEmpty($ou_id, " AND B.ou_id = $ou_id ");

      \Log::debug($queryBuilder->toString());
      $query = new CreateNativeQuery($queryBuilder->toString());

      $result = $query->getFirstRow();

      return [
        "total_realization_visit_plan" => $result
      ];
    }

    private function getTotalCustomerOrder($input){

      $user_id      = $input['user_id'];
      $tenant_id    = $input['tenant_id'];
      $ou_id        = $input['ou_id'];
      $salesman_id  = $input['salesman_id'];
      $date_from    = $input['date_from'];
      $date_to      = $input['date_to'];

      \Log::info('QUERY TOTAL ORDER TOKO');
      $queryBuilder = new QueryBuilder();
      $queryBuilder
        ->add(" SELECT COUNT(1) ")
        ->add(" FROM sl_salesman_visit_plan A ")
        ->add(" INNER JOIN m_partner_ou B ON A.salesman_id = B.partner_id ")
        ->add(" INNER JOIN m_user_partner D ON A.salesman_id = D.partner_id ")
        ->add(" INNER JOIN sl_order C ON A.visit_date = C.doc_date ")
        ->add("      AND A.customer_id = C.toko_partner_id ")
        ->add("      AND C.salesman_user_id = D.user_id ")
        ->add(" WHERE EXISTS( ")
        ->add(" SELECT 1 ")
        ->add(" FROM t_user X, t_user_role Y, t_policy_ou Z ")
        ->add(" WHERE X.tenant_id = A.tenant_id AND ")
        ->add(" X.user_id = $user_id AND ")
        ->add(" X.user_id = Y.user_id AND ")
        ->add(" X.policy_default_id = Z.policy_id AND ")
        ->add(" B.ou_id = Z.ou_id ")
        ->add(" ) AND  A.tenant_id = $tenant_id ")
        ->add(" AND A.visit_date >= '".$date_from."' ")
        ->add(" AND A.visit_date <= '".$date_to."' ")
        ->addIfNotEmpty($salesman_id, " AND A.salesman_id = $salesman_id ")
        ->addIfNotEmpty($ou_id, " AND B.ou_id = $ou_id ");

      \Log::debug($queryBuilder->toString());
      $query = new CreateNativeQuery($queryBuilder->toString());

      $result = $query->getFirstRow();

      return [
        "total_customer_order" => $result
      ];
    }



    /**protected function rules(){
    *  Add Validation Data
    *return [
    * "code" => "required|val_tenant"
    *];

    }*/
}