<?php
defined('BASEPATH') or exit('No direct script access allowed');
include APPPATH.'/third_party/faker/autoload.php';
class Sales_cycle extends AdminController
{
    public function __construct()
    {
        parent::__construct();
        defined('DASHBOARDS_MODULE') or exit(blank_page());
        $this->load->model('dashboard_model');
        $this->load->model('dashboard_period_filter');
        $this->load->model('dashboard_period_filter_v2');

        $this->load->library('dashboard');
    }

    public function index()
    {
        close_setup_menu();
        $data['title']  = _l(DASHBOARDS_MODULE.'_'.$this->router->fetch_class());
        if (!has_permission(DASHBOARDS_MODULE, '', $this->router->fetch_class())  && !is_admin()) {
            access_denied(DASHBOARDS_MODULE);
        }
        $this->load->view($this->router->fetch_class(), $data);
    }
    public function funnel(){
      $payload = file_get_contents('php://input'); //$this->input->post('params');
      $payload = json_decode($payload,true);


      if(get_option('demo_mode') == '1'){
        $faker = Faker\Factory::create();
        $faker->addProvider(new  Faker\Provider\LeadsProvider($faker));
        $leads = $faker->leads(1000,80000);
        die('{
          "status": true,
          "labels_reversed": [
            "Closed Won",
            "Negociation Review",
            "Proposal / Price Quote",
            "Perception Analysis",
            "Id. Decision Makers",
            "Value Preposition",
            "Need Analysis",
            "Qualification",
            "Oportunități"
          ],
          "labels": [
            "Oportunități",
            "Qualification",
            "Need Analysis",
            "Value Preposition",
            "Id. Decision Makers",
            "Perception Analysis",
            "Proposal / Price Quote",
            "Negociation Review",
            "Closed Won"
          ],
          "subLabels": [
        
          ],
          "values": [
            100,
            90,
            75,
            70,
            60,
            50,
            20,
            10,
            5
          ],
          "extraData": [
            '.$leads.',
            '.($leads-10/100*$leads).',
            '.($leads-50/100*$leads).',
            '.($leads-50/100*$leads).',
            '.($leads-49/100*$leads).',
            '.($leads-20/100*$leads).',
            '.($leads-10/100*$leads).',
            '.($leads-20/100*$leads).',
            '.($leads-60/100*$leads).'
          ]
        }');

      }


      $this->load->model('leads_model');
      $this->load->model('opportunities_model');
      $op_statuses = $this->opportunities_model->get_status();

      $this->dashboard_period_filter->reset();
      $this->dashboard_period_filter->table = 'tblleads';
      $this->dashboard_period_filter->date_field = 'dateadded';
      $this->dashboard_period_filter->set_filtering($payload['params']);
      $opportunities = $this->dashboard_period_filter
        ->prepare_filter()
        ->where('is_opportunity', 1)
        ->add_select('count(tblleads.id) as total')
        ->number('total');

        $qualif = $this->dashboard_model->opportunity_status_count($payload,2);
        $need_analysis = $this->dashboard_model->opportunity_status_count($payload,3);
        $value_preposition = $this->dashboard_model->opportunity_status_count($payload,4);
        $id_decision_makers = $this->dashboard_model->opportunity_status_count($payload,5);
        $perception_analysis = $this->dashboard_model->opportunity_status_count($payload,6);
        $proposal_price_quote = $this->dashboard_model->opportunity_status_count($payload,7);
        $negociation_review = $this->dashboard_model->opportunity_status_count($payload,8);
        $closed_won = $this->dashboard_model->opportunity_status_count($payload,9);




      /*
      $this->dashboard_period_filter->reset();
      $this->dashboard_period_filter->table = 'tblleads';
      $this->dashboard_period_filter->date_field = 'dateadded';
      $this->dashboard_period_filter->set_filtering($payload['params']);
      $proposal = $this->dashboard_period_filter
        ->prepare_filter()
        ->where('is_opportunity', 1)
        ->where_in('status_opportunity', [7,8,9])
        ->add_select('count(tblleads.id) as total')
        ->number('total');

      $this->dashboard_period_filter->reset();
      $this->dashboard_period_filter->table = 'tblleads';
      $this->dashboard_period_filter->date_field = 'dateadded';
      $this->dashboard_period_filter->set_filtering($payload['params']);
      $negotiation = $this->dashboard_period_filter
        ->prepare_filter()
        ->where('is_opportunity', 1)
        ->where_in('status_opportunity', [7,8])
        ->add_select('count(tblleads.id) as total')
        ->number('total');

      $this->dashboard_period_filter->reset();
      $this->dashboard_period_filter->table = 'tblleads';
      $this->dashboard_period_filter->date_field = 'dateadded';
      $this->dashboard_period_filter->set_filtering($payload['params']);
      $closing = $this->dashboard_period_filter
        ->prepare_filter()
        ->where('is_opportunity', 1)
        ->where_in('status_opportunity', [9])
        ->add_select('count(tblleads.id) as total')
        ->number('total');
      */


        $return = [
            'status'=>true,
            //'labels'=>['Opportunities', 'Proposal', 'Negociation','Closing'],
            'labels_reversed'=>[
              _l('closed_won'),_l('negociation_review'),_l('proposal_price_quote'),
              _l('perception_analysis'),_l('id_decision_makers'),_l('value_preposition'),
              _l('need_analysis'), _l('qualification'),  _l('opportunities')
              ],
          'labels'=>[
              _l('opportunities'),_l('qualification'), _l('need_analysis'),
              _l('value_preposition'),_l('id_decision_makers'),_l('perception_analysis'),
              _l('proposal_price_quote'),_l('negociation_review'),_l('closed_won')
            ],
            'subLabels'=>[],
            'values'=>[100,90,75,70,60,50,20,10,5],
            'extraData'=>[
              $opportunities,$qualif,$need_analysis,
              $value_preposition,$id_decision_makers,$perception_analysis,
              $proposal_price_quote,$negociation_review,$closed_won],
        ];
        echo json_encode($return);

    }
    public function avarage_sales_cycle(){
      $payload = file_get_contents('php://input'); //$this->input->post('params');
      $payload = json_decode($payload,true);

      if(get_option('demo_mode') == '1'){
        $faker = Faker\Factory::create();
        $faker->addProvider(new  Faker\Provider\LeadsProvider($faker));
        
        die('{
          "status": true,
          "series2": [
            [
              1,
              21.5
            ],
            [
              2,
              22.10000000000000142108547152020037174224853515625
            ],
            [
              1246579200000,
              23
            ],
            [
              1246665600000,
              23.800000000000000710542735760100185871124267578125
            ],
            [
              1246752000000,
              21.39999999999999857891452847979962825775146484375
            ],
            [
              1246838400000,
              21.300000000000000710542735760100185871124267578125
            ],
            [
              1246924800000,
              18.300000000000000710542735760100185871124267578125
            ],
            [
              1247011200000,
              15.4000000000000003552713678800500929355621337890625
            ],
            [
              1247097600000,
              16.39999999999999857891452847979962825775146484375
            ],
            [
              1247184000000,
              17.699999999999999289457264239899814128875732421875
            ],
            [
              1247270400000,
              17.5
            ],
            [
              1247356800000,
              17.60000000000000142108547152020037174224853515625
            ],
            [
              1247443200000,
              17.699999999999999289457264239899814128875732421875
            ],
            [
              1247529600000,
              16.800000000000000710542735760100185871124267578125
            ],
            [
              1247616000000,
              17.699999999999999289457264239899814128875732421875
            ],
            [
              1247702400000,
              16.300000000000000710542735760100185871124267578125
            ],
            [
              1247788800000,
              17.800000000000000710542735760100185871124267578125
            ],
            [
              1247875200000,
              18.10000000000000142108547152020037174224853515625
            ],
            [
              1247961600000,
              17.199999999999999289457264239899814128875732421875
            ],
            [
              1248048000000,
              14.4000000000000003552713678800500929355621337890625
            ],
            [
              1248134400000,
              13.699999999999999289457264239899814128875732421875
            ],
            [
              1248220800000,
              15.699999999999999289457264239899814128875732421875
            ],
            [
              1248307200000,
              14.5999999999999996447286321199499070644378662109375
            ],
            [
              1248393600000,
              15.300000000000000710542735760100185871124267578125
            ],
            [
              1248480000000,
              15.300000000000000710542735760100185871124267578125
            ],
            [
              1248566400000,
              15.800000000000000710542735760100185871124267578125
            ],
            [
              1248652800000,
              25.199999999999999289457264239899814128875732421875
            ],
            [
              1248739200000,
              14.800000000000000710542735760100185871124267578125
            ],
            [
              1248825600000,
              14.4000000000000003552713678800500929355621337890625
            ],
            [
              1248912000000,
              15
            ],
            [
              1248998400000,
              13.5999999999999996447286321199499070644378662109375
            ]
          ],
          "series": [
            {
              "name": "2022, week #37",
              "y": '.$faker->numberBetween(1,6).'
            },
            {
              "name": "2022, week #38",
              "y": '.$faker->numberBetween(1,6).'
            },
            {
              "name": "2022, week #39",
              "y": '.$faker->numberBetween(1,6).'
            },
            {
              "name": "2022, week #40",
              "y": '.$faker->numberBetween(1,6).'
            },
            {
              "name": "2022, week #41",
              "y": '.$faker->numberBetween(1,6).'
            }
          ],
          "series3": [
            {
              "0": "2021, Week 52",
              "1": 21.5,
              "customProp": "test"
            },
            {
              "0": "2022, Week 1",
              "1": 21.5,
              "customProp": "test"
            },
            {
              "0": "2022, Week 2",
              "1": 21.5,
              "customProp": "test"
            },
            {
              "0": "2022, Week 3",
              "1": 21.5,
              "customProp": "test"
            },
            {
              "0": "2022, Week 4",
              "1": 21.5,
              "customProp": "test"
            }
          ],
          "categories": [
            "2022, week #37",
            "2022, week #38",
            "2022, week #39",
            "2022, week #40",
            "2022, week #41"
          ]
        }');

      }

      $this->dashboard_period_filter_v2->set_params($payload['params']);
      $series = $this->dashboard_period_filter_v2
        ->from('tblleads')->set_period_on($payload['params'],'date_converted_to_opp')
        ->select('date_converted_to_opp')
        ->select('
        (SELECT count(*) FROM '.db_prefix().'leads as tbl_opportunities
          WHERE tbl_opportunities.assigned IS NOT NULL
          AND is_opportunity != 0 AND (status_opportunity = 9)

          '.  $this->dashboard_period_filter_v2->return_date_filtering_on('date_converted_to_opp','and').'
        ) as converted_to_opp
        ')
        ->select('
        (SELECT count(*) FROM '.db_prefix().'leads as tbl_opportunities
          WHERE tbl_opportunities.assigned IS NOT NULL
          '.  $this->dashboard_period_filter_v2->return_date_filtering_on('date_converted_to_opp','and').'
        ) as total_leads
        ')  //AND is_opportunity = 0 OR (is_opportunity = 1 AND status_opportunity != 9)

        ->select('
        (SELECT ABS(ROUND(AVG(DATEDIFF(date_converted_to_opp,dateassigned)))) FROM '.db_prefix().'leads as tbl_opportunities
          WHERE tbl_opportunities.assigned IS NOT NULL
          AND is_opportunity != 0 AND (status_opportunity = 9)

          '.  $this->dashboard_period_filter_v2->return_date_filtering_on('date_converted_to_opp','and').'
        ) as scl
        ')


        ->get_by_weeknumber();

        $series_data = array_column($series,'scl');
        $series_data = $this->dashboard->cast_int($series_data);
        $categories = array_column($series,'display_format');
        foreach($series_data as $k=>$v){
          $series_data2[$k] = [
            'name'=>$categories[$k],
            'y'=>$v,
          ];
        }

      //$categories = $weekNumbers;
        $return = [
            'status'=>true,
            'series2'=>[
                [1, 21.5],
                [2, 22.1],
                [1246579200000, 23],
                [1246665600000, 23.8],
                [1246752000000, 21.4],
                [1246838400000, 21.3],
                [1246924800000, 18.3],
                [1247011200000, 15.4],
                [1247097600000, 16.4],
                [1247184000000, 17.7],
                [1247270400000, 17.5],
                [1247356800000, 17.6],
                [1247443200000, 17.7],
                [1247529600000, 16.8],
                [1247616000000, 17.7],
                [1247702400000, 16.3],
                [1247788800000, 17.8],
                [1247875200000, 18.1],
                [1247961600000, 17.2],
                [1248048000000, 14.4],
                [1248134400000, 13.7],
                [1248220800000, 15.7],
                [1248307200000, 14.6],
                [1248393600000, 15.3],
                [1248480000000, 15.3],
                [1248566400000, 15.8],
                [1248652800000, 25.2],
                [1248739200000, 14.8],
                [1248825600000, 14.4],
                [1248912000000, 15],
                [1248998400000, 13.6]
            ],
            'series'=>$series_data2,
            'series3'=>[
                ['2021, Week 52', 21.5,'customProp'=>'test'],
                ['2022, Week 1', 21.5,'customProp'=>'test'],
                ['2022, Week 2', 21.5,'customProp'=>'test'],
                ['2022, Week 3', 21.5,'customProp'=>'test'],
                ['2022, Week 4', 21.5,'customProp'=>'test'],
            ],
            'categories'=>$categories
        ];
        echo json_encode($return);

    }
    public function employee_performance(){
      $payload = file_get_contents('php://input'); //$this->input->post('params');
      $payload = json_decode($payload,true);


      if(get_option('demo_mode') == '1'){
        $faker = Faker\Factory::create();
        $faker->addProvider(new  Faker\Provider\LeadsProvider($faker));
        
        die('{
          "status": true,
          "employees": [
            {
              "staffid": "1",
              "firstname": "'.$faker->firstName().'",
              "lastname": "'.$faker->lastName().'",
              "SalesAgent": "'.$faker->firstName().' '.$faker->lastName().'",
              "PositionName": "'.$faker->jobTitle().'",
              "converted_to_opp": "'.$faker->numberBetween(3,10).'",
              "total_leads": "'.$faker->numberBetween(8,20).'",
              "scl": null,
              "conversion_rate": '.$faker->numberBetween(0,100).',
              "profile_pic": "https://www.fillmurray.com/250/250",
              "conversion_hitory": [
        
              ],
              "scl_hitory": [
                {
                  "name": "2022, week #37",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #38",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #39",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #40",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #41",
                  "y": '.$faker->numberBetween(1,8).'
                }
              ]
            },

            {
              "staffid": "2",
              "firstname": "'.$faker->firstName().'",
              "lastname": "'.$faker->lastName().'",
              "SalesAgent": "'.$faker->firstName().' '.$faker->lastName().'",
              "PositionName": "'.$faker->jobTitle().'",
              "converted_to_opp": "'.$faker->numberBetween(3,10).'",
              "total_leads": "'.$faker->numberBetween(8,20).'",
              "scl": null,
              "conversion_rate": '.$faker->numberBetween(0,100).',
              "profile_pic": "https://www.fillmurray.com/300/300",
              "conversion_hitory": [
        
              ],
              "scl_hitory": [
                {
                  "name": "2022, week #37",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #38",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #39",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #40",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #41",
                  "y": '.$faker->numberBetween(1,8).'
                }
              ]
            },

            {
              "staffid": "3",
              "firstname": "'.$faker->firstName().'",
              "lastname": "'.$faker->lastName().'",
              "SalesAgent": "'.$faker->firstName().' '.$faker->lastName().'",
              "PositionName": "'.$faker->jobTitle().'",
              "converted_to_opp": "'.$faker->numberBetween(3,10).'",
              "total_leads": "'.$faker->numberBetween(8,20).'",
              "scl": null,
              "conversion_rate": '.$faker->numberBetween(0,100).',
              "profile_pic": "https://www.fillmurray.com/200/200",
              "conversion_hitory": [
        
              ],
              "scl_hitory": [
                {
                  "name": "2022, week #37",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #38",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #39",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #40",
                  "y": '.$faker->numberBetween(1,8).'
                },
                {
                  "name": "2022, week #41",
                  "y": '.$faker->numberBetween(1,8).'
                }
              ]
            }

          ]
        }');

      }


      $this->dashboard_period_filter_v2->set_params($payload['params']);
      $employees = $this->dashboard_period_filter_v2
        ->select('staff.staffid')
        ->select(db_prefix().'staff.firstname, '.db_prefix().'staff.lastname,CONCAT(COALESCE('.db_prefix().'staff.firstname,"")," ",COALESCE('.db_prefix().'staff.lastname,"")) as SalesAgent')
        ->select('designations.name as PositionName')
        ->from('staff')//->set_period_on($payload['params'],'tblleads.dateadded')
        ->select('
        (SELECT count(*) FROM '.db_prefix().'leads as tbl_opportunities
          WHERE '.db_prefix().'staff.staffid = tbl_opportunities.assigned
          AND is_opportunity != 0 AND (status_opportunity = 9)

          '.  $this->dashboard_period_filter_v2->return_date_filtering_on('date_converted_to_opp','and').'
        ) as converted_to_opp
        ')
        ->select('
        (SELECT count(*) FROM '.db_prefix().'leads as tbl_opportunities
          WHERE '.db_prefix().'staff.staffid = tbl_opportunities.assigned
          '.  $this->dashboard_period_filter_v2->return_date_filtering_on('date_converted_to_opp','and').'
        ) as total_leads
        ')  //AND is_opportunity = 0 OR (is_opportunity = 1 AND status_opportunity != 9)

        ->select('
        (SELECT ABS(ROUND(AVG(DATEDIFF(date_converted_to_opp,dateassigned)))) FROM '.db_prefix().'leads as tbl_opportunities
          WHERE '.db_prefix().'staff.staffid = tbl_opportunities.assigned
          AND is_opportunity != 0 AND (status_opportunity = 9)

          '.  $this->dashboard_period_filter_v2->return_date_filtering_on('date_converted_to_opp','and').'
        ) as scl
        ')

        /*          AND (status != 0 AND status IS NOT NULL)
          AND date_converted_to_opp IS NOT NULL*/

        ->join('hr_employees','hr_employees.id_staff = staff.staffid','LEFT')
        ->join('designations','designations.id = hr_employees.designation_id','LEFT')
        ->where('(tblstaff.sales_kpi_category != 0 AND tblstaff.sales_kpi_category IS NOT NULL)' )
        ->group_by('tblstaff.staffid')
        ->order_by(db_prefix().'staff.firstname','ASC')
        ->order_by(db_prefix().'staff.lastname','ASC')
        ->result_array();
        //->compiled();

        //pre($employees);
        foreach($employees as $k=>$employee){

          $employees[$k]['conversion_rate'] = $this->dashboard->percent_in($employee['converted_to_opp'],$employee['total_leads']);
          $employees[$k]['profile_pic'] = staff_profile_image_url($employee['staffid']);
          $this->dashboard_period_filter->reset();
          $this->dashboard_period_filter->table = 'tblleads';
          $this->dashboard_period_filter->date_field = 'date_converted_to_opp';
          $this->dashboard_period_filter->set_filtering($payload['params']);
          $this->dashboard_period_filter->formula('sum(tblleads.opportunity_ot_value + (tblleads.opportunity_mo_value*12) )');
          $this->dashboard_period_filter->group_only_by_monthday();
          $conversion_hitory = $this->dashboard_period_filter->select()
          ->where('tblleads.assigned = "'.$employee['staffid'].'" ')
          ->result_array();
          $employees[$k]['conversion_hitory'] = $conversion_hitory;

          /*
          $this->dashboard_period_filter->reset();
          $this->dashboard_period_filter->table = 'tblleads';
          $this->dashboard_period_filter->date_field = 'date_converted_to_opp';
          $this->dashboard_period_filter->set_filtering($payload['params']);
          $this->dashboard_period_filter->formula('ABS(ROUND(AVG(DATEDIFF(date_converted_to_opp,dateassigned))))');
          $this->dashboard_period_filter->group_only_by_monthday();
          $scl_hitory = $this->dashboard_period_filter->select()
          ->where('tblleads.assigned = "'.$employee['staffid'].'" AND is_opportunity != 0 AND (status_opportunity = 9) '.  $this->dashboard_period_filter_v2->return_date_filtering_on('date_converted_to_opp','and').'')
          //->result_array()
          ->result_format_indexed_series(['the_date','total_counted'],'filldays',0);
          $employees[$k]['scl_hitory'] = $this->dashboard->cast_int($scl_hitory);
          */

          $this->dashboard_period_filter_v2->set_params($payload['params']);
          $series = $this->dashboard_period_filter_v2
            ->from('tblleads')->set_period_on($payload['params'],'date_converted_to_opp')
            ->select('date_converted_to_opp')
            ->select('
            (SELECT ABS(ROUND(AVG(DATEDIFF(date_converted_to_opp,dateassigned)))) FROM '.db_prefix().'leads as tbl_opportunities
              WHERE tbl_opportunities.assigned IS NOT NULL
              AND is_opportunity != 0 AND (status_opportunity = 9)

              '.  $this->dashboard_period_filter_v2->return_date_filtering_on('date_converted_to_opp','and').'
            ) as scl
            ')


            ->get_by_weeknumber();

            $series_data = array_column($series,'scl');
            $series_data = $this->dashboard->cast_int($series_data);
            $categories = array_column($series,'display_format');
            foreach($series_data as $kk=>$v){
              $scl_hitory[$kk] = [
                'name'=>$categories[$kk],
                'y'=>$v,
              ];
            }

          $employees[$k]['scl_hitory'] = $scl_hitory;

        }

        $return = [
            'status'=>true,
            'employees'=>$employees
        ];
        echo json_encode($return);

    }
}
