<?php
class Dashboard_model extends CI_Model {

  public function __construct()
  {
      parent::__construct();
      defined('DASHBOARDS_MODULE') or exit(blank_page());
      $this->load->model('dashboard_period_filter');
      $this->load->model('dashboard_period_filter_v2');
      $this->load->library('dashboard');
  }

    public function sales_agents_with_conversions($params){
      $this->db->select(db_prefix().'staff.firstname, '.db_prefix().'staff.lastname,CONCAT(COALESCE('.db_prefix().'staff.firstname,"")," ",COALESCE('.db_prefix().'staff.lastname,"")) as SalesAgent');
      $this->db->select('designations.name as PositionName');


      $wh_date_converted = '';
      $wh_lead_created = '';
      if(isset($params['period_filter'])){
        if($params['period_filter'] == 't'){
          $wh_date_converted = ' AND  DATE(tbl_owned_converted_leads.last_opportunity_status_change) = DATE(NOW()) ';
          $wh_lead_created = ' AND DATE(tbl_owned_leads.dateadded) = DATE(NOW()) ';

        }else if($params['period_filter'] == 'y'){
          $wh_date_converted = ' AND  DATE(tbl_owned_converted_leads.last_opportunity_status_change) BETWEEN DATE(NOW() - INTERVAL 1 DAY) AND CURRENT_DATE() ';
          $wh_lead_created = ' AND DATE(tbl_owned_leads.dateadded) BETWEEN DATE(NOW() - INTERVAL 1 DAY) AND CURRENT_DATE() ';

        }else if($params['period_filter'] == 'w'){
          $wh_date_converted = ' AND  YEARWEEK(tbl_owned_converted_leads.last_opportunity_status_change) = YEARWEEK(NOW()) ';
          $wh_lead_created = ' AND YEARWEEK(tbl_owned_leads.dateadded) = YEARWEEK(NOW()) ';

        }else if($params['period_filter'] == 'lw'){
          $wh_date_converted = ' AND  DATE(tbl_owned_converted_leads.last_opportunity_status_change) >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY AND DATE(tbl_owned_converted_leads.date_converted) < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY ';
          $wh_lead_created = ' AND DATE(tbl_owned_leads.dateadded) >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY AND DATE(tbl_owned_leads.date_converted) < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY ';

        }else if($params['period_filter'] == 'm'){
          $wh_date_converted = ' AND  MONTH(tbl_owned_converted_leads.last_opportunity_status_change) = MONTH(CURRENT_DATE()) AND  YEAR(tbl_owned_converted_leads.date_converted) = YEAR(CURRENT_DATE()) ';
          $wh_lead_created = ' AND MONTH(tbl_owned_leads.dateadded) = MONTH(CURRENT_DATE()) AND YEAR(tbl_owned_leads.dateadded) = YEAR(CURRENT_DATE()) ';

        }else if($params['period_filter'] == 'lm'){
          $wh_date_converted = ' AND  MONTH(tbl_owned_converted_leads.last_opportunity_status_change) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) AND  YEAR(tbl_owned_converted_leads.date_converted) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) ';
          $wh_lead_created = ' AND MONTH(tbl_owned_leads.dateadded) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) AND YEAR(tbl_owned_leads.dateadded) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) ';

        }else if($params['period_filter'] == 'yr'){
          $wh_date_converted = ' AND  YEAR(tbl_owned_converted_leads.last_opportunity_status_change) = YEAR(CURRENT_DATE()) ';
          $wh_lead_created = ' AND YEAR(tbl_owned_leads.dateadded) = YEAR(CURRENT_DATE()) ';

        }else if($params['period_filter'] == 'lyr'){
          $wh_date_converted = ' AND  YEAR(tbl_owned_converted_leads.last_opportunity_status_change) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) ';
          $wh_lead_created = ' AND YEAR(tbl_owned_leads.dateadded) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) ';

        }else if($params['period_filter'] == 'p'){
          $wh_date_converted = ' AND  DATE(tbl_owned_converted_leads.last_opportunity_status_change) BETWEEN DATE('.$params['date_from'].') AND DATE('.$params['date_to'].') ';
          $wh_lead_created = ' AND DATE(tbl_owned_leads.dateadded) BETWEEN DATE('.$params['date_from'].') AND DATE('.$params['date_to'].') ';
        }else if($params['period_filter'] == '30d'){
          $wh_date_converted = ' AND  DATE(tbl_owned_converted_leads.last_opportunity_status_change) BETWEEN CURRENT_DATE - INTERVAL 30 DAY AND CURRENT_DATE() ';
          $wh_lead_created = ' AND DATE(tbl_owned_leads.dateadded) BETWEEN CURRENT_DATE - INTERVAL 30 DAY AND CURRENT_DATE() ';
        }

      }



      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as tbl_owned_leads
        WHERE '.db_prefix().'staff.staffid = tbl_owned_leads.assigned
        '.$wh_lead_created.'
      ) as owned_leads
      ');

      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as tbl_owned_converted_leads
        WHERE '.db_prefix().'staff.staffid = tbl_owned_converted_leads.assigned
        AND is_opportunity != 0 AND (status_opportunity = 9)
        AND date_converted_to_opp IS NOT NULL
        '.$wh_date_converted.'
      ) as owned_converted_leads
      '); //AND client_id != 0
      $this->db->select('staff.staffid');
      $this->db->from('staff');
      //$this->db->join('leads','staff.staffid = leads.assigned','LEFT');
      $this->db->join('hr_employees','hr_employees.id_staff = staff.staffid','LEFT');
      $this->db->join('designations','designations.id = hr_employees.designation_id','LEFT');

      $this->db->where('(staff.sales_kpi_category != 0 AND staff.sales_kpi_category IS NOT NULL)' );
      $this->db->order_by(db_prefix().'staff.firstname','ASC');
      $this->db->order_by(db_prefix().'staff.lastname','ASC');
      $this->db->group_by('staff.staffid');
      $query = $this->db->get();
      $results = $query->result_array();
      if(!empty($results)){
        foreach($results as $k=>$result){
          $results[$k]['profile_pic'] = staff_profile_image_url($result['staffid']);
        }
      }
      return $results;
    }
    public function converted_leads($params){
      $period = false;
      if(isset($params['period_filter'])){
        if($params['period_filter'] == 't'){

          $sql = '
          SELECT
                COUNT(l.id) as total_counted
              , CONCAT(HOUR(l.date_converted), "h.") AS the_date
              , id
          FROM  tblleads l
          WHERE DATE(l.last_opportunity_status_change) = CURRENT_DATE()
          AND is_opportunity != 0 AND (status_opportunity = 9)
          AND (status != 0 AND status IS NOT NULL)

          GROUP BY HOUR(l.last_opportunity_status_change)
          ORDER BY
                HOUR(l.date_converted) ASC
          ';

        }else if($params['period_filter'] == 'y'){

          $sql = '
          SELECT
                COUNT(l.id) as total_counted
                , CONCAT(HOUR(l.last_opportunity_status_change), "h.") AS the_date
              , id
          FROM  tblleads l
          WHERE DATE(l.last_opportunity_status_change) = CURRENT_DATE() - INTERVAL 1 DAY
          AND is_opportunity != 0 AND (status_opportunity = 9)
          AND (status != 0 AND status IS NOT NULL)

          GROUP BY HOUR(l.last_opportunity_status_change)
          ORDER BY
                HOUR(l.last_opportunity_status_change) ASC
          ';

        }else if($params['period_filter'] == 'w'){
          $day = date('w');
          $week_start = date('m-d-Y', strtotime('-'.$day.' days'));
          $week_end = date('m-d-Y', strtotime('+'.(6-$day).' days'));

          if($day == 1){
            $group = 'HOUR(the_date)';
            $period_selector = 'CONCAT(HOUR(l.last_opportunity_status_change), "h.")';
          }else{
            $group = 'the_date';
            $period_selector = 'DAYNAME(all_dates.Date)';
          }

          /*
          $day contains a number from 0 to 6 representing the day of the week (Sunday = 0, Monday = 1, etc.).
          $week_start contains the date for Sunday of the current week as mm-dd-yyyy.
          $week_end contains the date for the Saturday of the current week as mm-dd-yyyy.
          */

          $date = date('Y-m-d');
          $from_date = date("Y-m-d", strtotime('monday this week', strtotime($date)));
          $to_date = date("Y-m-d", strtotime('sunday this week', strtotime($date)));
          $period = true;

          $where = ' all_dates.Date <= CURRENT_DATE() ';
        }else if($params['period_filter'] == 'lw'){
          $date = date('Y-m-d');
          $from_date = date("Y-m-d", strtotime('monday last week', strtotime($date)));
          $to_date = date("Y-m-d", strtotime('sunday this week', strtotime($date)));
          $period = true;
          $group = 'all_dates.Date';
          $where  = ' 1 = 1 ';
          $period_selector = 'DAYNAME(all_dates.Date)';
        }else if($params['period_filter'] == 'm'){
          $date = date('Y-m-d');
          $from_date = date('Y-m-d', mktime(0,0,0,date('n'),1,date('Y')));
          $to_date = date("Y-m-t", strtotime($date)); //last day of the month of current date
          $period = true;
          $group = 'all_dates.Date';
          $where = ' all_dates.Date <= CURRENT_DATE() ';
          $period_selector = 'all_dates.Date';
        }else if($params['period_filter'] == 'lm'){
          $date = date('Y-m-d');
          $from_date = date('Y-m-d', mktime(0,0,0,date('n')-1,1,date('Y')));
          $to_date = date('Y-m-d', strtotime('last day of previous month'));
          $period = true;
          $group = 'all_dates.Date';
          $where  = ' 1 = 1 ';
          $period_selector = 'all_dates.Date';
        }else if($params['period_filter'] == 'yr'){
          $month  = date('m');

          $date = date('Y-m-d');
          $from_date = date('Y-m-d', strtotime('first day of this year'));
          $to_date = date('Y-m-d', strtotime('last day of this year'));

          if((int)$month == 1){
            $period = false;

            $group = 'all_dates.Date';
            $where = ' all_dates.Date <= CURRENT_DATE() ';
            $period_selector = 'all_dates.Date';
          }else{

            $group = ' MONTHNAME(all_dates.Date)';
            $period = false;
            $where = ' all_dates.Date <= CURRENT_DATE() ';
            $period_selector = 'MONTHNAME(all_dates.Date)';
          }



          $sql = '

          SELECT
                COUNT(l.id) as total_counted
              , '.$period_selector.' AS the_date
              , id
          FROM (
                  SELECT DATE_ADD(curdate(), INTERVAL 2 MONTH) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
                  FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
                  CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
                ) all_dates
                LEFT OUTER JOIN tblleads l
                            ON  DATE(all_dates.Date) = DATE(l.last_opportunity_status_change)

          WHERE all_dates.Date BETWEEN "'.$from_date.'" AND "'.$to_date.'" AND ('.$where.')
          AND is_opportunity != 0 AND (status_opportunity = 9)
          AND (status != 0 AND status IS NOT NULL)
          GROUP BY
                '.$group.'
          ORDER BY
                 MONTHNAME(all_dates.Date) ASC

          ';



          $year = date('Y');

          $period_selector = 'CONCAT(MONTHNAME(l.last_opportunity_status_change), " '.$year.'")';
          $group = ' MONTHNAME(l.last_opportunity_status_change)';
          $sql = '

          SELECT
                COUNT(l.id) as total_counted
              , '.$period_selector.' AS the_date
              , id
          FROM tblleads l
          LEFT JOIN (
          SELECT 1 AS `month`
          UNION
          SELECT 2 AS `month`
          UNION
          SELECT 3 AS `month`
          UNION
          SELECT 4 AS `month`
          UNION
          SELECT 5 AS `month`
          UNION
          SELECT 6 AS `month`
          UNION
          SELECT 7 AS `month`
          UNION
          SELECT 8 AS `month`
          UNION
          SELECT 9 AS `month`
          UNION
          SELECT 10 AS `month`
          UNION
          SELECT 11 AS `month`
          UNION
          SELECT 12 AS `month`
          ) AS t on t.month = MONTH(l.last_opportunity_status_change)


          WHERE YEAR(l.last_opportunity_status_change) = "'.$year.'"
          AND is_opportunity != 0 AND (status_opportunity = 9)
          AND (status != 0 AND status IS NOT NULL)

          GROUP BY
                '.$group.'
          ORDER BY
                 month(l.last_opportunity_status_change) ASC

          ';





        }else if($params['period_filter'] == 'lyr'){
          $date = date('Y-m-d');
          $this->from_date = $year.'-01-01';
          $this->to_date = $year.'-12-31';
          $group = ' MONTHNAME(l.last_opportunity_status_change)';
          $period = false;
          $where  = ' 1 = 1 ';

          //WHERE all_dates.Date BETWEEN "'.$from_date.'" AND "'.$to_date.'" AND ('.$where.')
          $period_selector = 'CONCAT(MONTHNAME(l.last_opportunity_status_change), "")';
          $year = date('Y')-1; //, strtotime('last day of previous year'));
          $sql = '
          SELECT
                COUNT(l.id) as total_counted
                , '.$period_selector.' AS the_date
              , id
          FROM  tblleads l
          WHERE YEAR(l.last_opportunity_status_change) = "'.$year.'"
          AND is_opportunity != 0 AND (status_opportunity = 9)
          AND (status != 0 AND status IS NOT NULL)
          GROUP BY '.$group.'
          ORDER BY
                 month(l.last_opportunity_status_change)
          ';

          $period_selector = 'CONCAT(MONTHNAME(l.last_opportunity_status_change), "")';
          $group = ' MONTHNAME(l.last_opportunity_status_change)';
          $sql = '

          SELECT
                COUNT(l.id) as total_counted
              , '.$period_selector.' AS the_date
              , id
          FROM (
                  SELECT DATE_ADD(curdate(), INTERVAL 2 MONTH) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
                  FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
                  CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
                ) all_dates
                LEFT OUTER JOIN tblleads l
                            ON  DATE(all_dates.Date) = DATE(l.last_opportunity_status_change)

          WHERE YEAR(l.last_opportunity_status_change) = "'.$year.'"
          AND is_opportunity != 0 AND (status_opportunity = 9)
          AND (status != 0 AND status IS NOT NULL)
          GROUP BY
                '.$group.'
          ORDER BY
                 month(all_dates.Date) ASC

          ';



          $period_selector = 'CONCAT(MONTHNAME(l.last_opportunity_status_change), " '.$year.'")';
          $group = ' MONTHNAME(l.last_opportunity_status_change)';
          $sql = '

          SELECT
                COUNT(l.id) as total_counted
              , '.$period_selector.' AS the_date
              , id
          FROM tblleads l
          LEFT JOIN (
          SELECT 1 AS `month`
          UNION
          SELECT 2 AS `month`
          UNION
          SELECT 3 AS `month`
          UNION
          SELECT 4 AS `month`
          UNION
          SELECT 5 AS `month`
          UNION
          SELECT 6 AS `month`
          UNION
          SELECT 7 AS `month`
          UNION
          SELECT 8 AS `month`
          UNION
          SELECT 9 AS `month`
          UNION
          SELECT 10 AS `month`
          UNION
          SELECT 11 AS `month`
          UNION
          SELECT 12 AS `month`
          ) AS t on t.month = MONTH(l.last_opportunity_status_change)


          WHERE YEAR(l.last_opportunity_status_change) = "'.$year.'"
          AND is_opportunity != 0 AND (status_opportunity = 9)
          AND (status != 0 AND status IS NOT NULL)
          GROUP BY
                '.$group.'
          ORDER BY
                 month(l.last_opportunity_status_change) ASC

          ';


        }else if($params['period_filter'] == 'p'){
          $from_date = $params['date_from'];
          $to_date = $params['date_to'];
          $period = true;
          $group = 'all_dates.Date';
          $where  = ' 1 = 1  ';
          $period_selector = 'all_dates.Date';
        }else if($params['period_filter'] == '30d'){
          $from_date = date('Y-m-d',strtotime('-30 days',strtotime(date('Y-m-d'))));
          $to_date =  date('Y-m-d');
          $period = true;
          $group = 'all_dates.Date';
          $where  = ' 1 = 1  ';
          $period_selector = 'all_dates.Date';
        }

      }

      if($period){
        $sql = '

        SELECT
              COUNT(l.id) as total_counted
            , '.$period_selector.' AS the_date
            , id
        FROM (
                SELECT DATE_ADD(curdate(), INTERVAL 2 MONTH) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
                FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
                CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
              ) all_dates
              LEFT OUTER JOIN tblleads l
                          ON  DATE(all_dates.Date) = DATE(l.last_opportunity_status_change)

        WHERE all_dates.Date BETWEEN "'.$from_date.'" AND "'.$to_date.'" AND ('.$where.')
        AND is_opportunity != 0 AND (status_opportunity = 9)
        AND (status != 0 AND status IS NOT NULL)
        GROUP BY
              '.$group.'
        ORDER BY
              l.last_opportunity_status_change ASC

        ';
      }



      $query = $this->db->query($sql); $results = $query->result_array();


      return $results;


    }

    public function count_conversions($params){
      $from_date = date('Y-m-d');
      $to_date = date('Y-m-d');
      if(isset($params['period_filter'])){
        if($params['period_filter'] == 't'){
          $from_date = date('Y-m-d');
          $to_date = date('Y-m-d');
        }else if($params['period_filter'] == 'y'){
          $from_date = date('Y-m-d',strtotime("yesterday"));
          $to_date = date('Y-m-d');
        }else if($params['period_filter'] == 'w'){
          $date = date('Y-m-d');
          $from_date = date("Y-m-d", strtotime('monday this week', strtotime($date)));
          $to_date = date("Y-m-d", strtotime('sunday this week', strtotime($date)));
        }else if($params['period_filter'] == 'lw'){
          $date = date('Y-m-d');
          $from_date = date("Y-m-d", strtotime('monday last week', strtotime($date)));
          $to_date = date("Y-m-d", strtotime('sunday this week', strtotime($date)));
        }else if($params['period_filter'] == 'm'){
          $date = date('Y-m-d');
          $from_date = date('Y-m-d', mktime(0,0,0,date('n'),1,date('Y')));
          $to_date = date("Y-m-t", strtotime($date)); //last day of the month of current date
        }else if($params['period_filter'] == 'lm'){
          $date = date('Y-m-d');
          $from_date = date('Y-m-d', mktime(0,0,0,date('n')-1,1,date('Y')));
          $to_date = date('Y-m-d', strtotime('last day of previous month'));
        }else if($params['period_filter'] == 'yr'){
          $from_date = date('Y').'-01-01'; //date('Y-m-d', strtotime('first day of this year'));
          $to_date = date('Y').'-12-31';  //date('Y-m-d', strtotime('last day of this year'));
        }else if($params['period_filter'] == 'lyr'){
          $date = date('Y-m-d');

          $from_date = (date('Y')-1).'-01-01';
          $to_date = (date('Y')-1).'-12-31';

        }else if($params['period_filter'] == 'p'){
          $from_date = $params['date_from'];
          $to_date = $params['date_to'];
        }else if($params['period_filter'] == '30d'){
          $from_date = date('Y-m-d',strtotime('-30 days',strtotime(date('Y-m-d'))));
          $to_date =  date('Y-m-d');
        }
      }

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

      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as t
        WHERE DATE(dateadded) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
        AND client_id != 0 AND junk = 0
      ) as new_customers
      ');

      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as t
        WHERE DATE(dateadded) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
         AND junk = 0
      ) as total_valid_entries
      ');

      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as t
        WHERE DATE(dateadded) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
         AND junk = 0 and status IS NOT NULL and status != 0
      ) as total_leads
      '); //AND is_opportunity = 0

      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as t
        WHERE DATE(last_opportunity_status_change) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
        AND is_opportunity = 1 AND junk = 0
      ) as total_opportunitites
      ');

      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as t
        WHERE DATE(date_converted_to_opp) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
        AND is_opportunity = 1 AND junk = 0 AND status IS NOT NULL and status_opportunity IS NOT NULL
      ) as total_leads_to_opportunitites
      ');

      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as t
        WHERE DATE(last_opportunity_status_change) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
         AND is_opportunity = 1 AND lost = 0 AND junk = 0 AND (status_opportunity = 1 OR status_opportunity = 2 OR status_opportunity = 3 OR status_opportunity = 4 OR status_opportunity = 5 OR status_opportunity = 6 OR status_opportunity = 7 OR status_opportunity = 8)
      ) as total_negociation
      '); //AND assigned != 0  AND client_id != 0

      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'proposals as t
        WHERE DATE(datecreated) BETWEEN "'.$from_date.'" AND "'.$to_date.'"

      ) as total_proposals
      ');

      /*$this->db->select('
      (SELECT count(*) FROM '.db_prefix().'proposals as t
        WHERE DATE(datecreated) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
       AND status = 3
      ) as total_wins
      ');*/
      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as t
        WHERE DATE(last_opportunity_status_change) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
         AND is_opportunity = 1 AND lost = 0 AND junk = 0 AND (status_opportunity = 9)
      ) as total_wins
      ');
      //AND (status != 0 AND status IS NOT NULL) AND date_converted_to_opp IS NOT NULL


      if(!empty($statuses)){
      foreach($statuses as $status){
        $this->db->select('
        (SELECT count(*) FROM '.db_prefix().'leads as t
          WHERE DATE(last_opportunity_status_change) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
           AND is_opportunity = 1 AND lost = 0 AND junk = 0 AND (status_opportunity = '.$status['id'].')
        ) as total_status_'.$status['id']);
      }}

      /*$this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as t
        WHERE DATE(last_opportunity_status_change) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
         AND is_opportunity = 1 AND lost = 0 AND junk = 0 AND (status_opportunity = 0 OR status_opportunity IS NULL)
      ) as total_without_status');*/

      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as t
        WHERE DATE(last_opportunity_status_change) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
         AND is_opportunity = 1 AND lost = 1 AND junk = 0 AND (status_opportunity = 0 OR status_opportunity IS NULL)
      ) as total_lost');

      $this->db->select('
      (SELECT count(*) FROM '.db_prefix().'leads as t
        WHERE DATE(last_opportunity_status_change) BETWEEN "'.$from_date.'" AND "'.$to_date.'"
         AND is_opportunity = 1 AND lost = 0 AND junk = 1 AND (status_opportunity = 0 OR status_opportunity IS NULL)
      ) as total_junk');



      $query = $this->db->get();
      $results = $query->row_array();
      foreach($results as $k=>$v){
        $results[$k] = (int)$v;
      }
      $results['from_date'] = $from_date;
      $results['to_date'] = $to_date;

      return $results;


    }


    public function opportunity_incremental_count($payload,$wh){
      $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']);
      return $this->dashboard_period_filter
        ->prepare_filter()
        ->where('is_opportunity', 1)
        ->where_in('status_opportunity', $wh)
        ->add_select('count(tblleads.id) as total')
        ->number('total');
    }
    public function opportunity_status_count($payload,$wh){
      $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']);
      return $this->dashboard_period_filter
        ->prepare_filter()
        ->where('is_opportunity', 1)
        ->where('status_opportunity', $wh)
        ->add_select('count(tblleads.id) as total')
        ->number('total');
    }


}
