<?php
class Dashboard extends CI_Controller {

	function __construct(){
		parent::__construct();

	}

	function index(){	
		$this->dashboard();
	}
	
	function dashboard(){
		admin_auth();

		$this->_page_data['todays']=$this->Todays_due();
		$this->_page_data['previous']=$this->All_previous_due();
    $this->_page_data['customer']=$this->count_customer();
    $this->_page_data['user']=$this->count_user();
    $this->_page_data['dispenser']=$this->count_dispenser();
    //$this->_page_data['refill']=$this->count_refill();
    $this->_page_data['seven_days']=$this->next_seven_days();
    $this->_page_data['branch']=$this->fetch_branch_name();
		$this->_page_data['page_title']="Dashboard";
		$this->_page_data['bredcrum']=array();
		$this->load->view('gc/dashboard',$this->_page_data);
	}

	function Todays_due(){
  

        $user_type_id = $this->session->userdata('user_type_id');
        $user_type= $this->session->userdata('user_type');
        $user_id = $this->session->userdata('user_id');
        $company_branch_id=$this->session->userdata('company_branch_id');
        $access_branch= $this->session->userdata('access_branch');
        $acb =  explode( ',',$access_branch);
      
		    $condition[] = array('join',TABLE_DISPENSER_REFILL_DETAILS.' as a','b.dispenser_sales_id = a.dispenser_sales_id','inner');

		    $condition[] = array('join',TABLE_PRODUCT.' as p','a.product_id = p.product_id','inner');
		    $condition[] = array('join',TABLE_PRODUCT.' as p1','p.dispenser_name = p1.product_id','inner');
        $condition[] = array('join',TABLE_CUSTOMER.' as c','c.customer_id = b.customer_id','inner');
        $condition[] = array('join',TABLE_COMPANY_BRANCH.' as cb','cb.company_branch_id = c.company_branch_id','inner');
        $condition[] = array('join',TABLE_CITY.' as ci','ci.city_id = cb.city_id','inner');
        if ($user_type=='Sales Person') {
          $condition[] = array('where','b.sales_person_id',$user_id);
        }
        if ($access_branch!='') {
          $condition[] = array('where_in','b.company_branch_id',$acb);
        }
        //$condition[] = array('join',TABLE_USERS.' as u','u.user_id = b.sales_person_id','inner');
        

        $qry_part = 'a.next_refill_date = ( CURDATE( ) ) ';
        $condition[] = array('where',$qry_part);
         $selected_array=array(
                              'table_name'=>TABLE_DISPENSER_SALES.' as b',
                              'table_fields'=>'a.dispenser_refill_details_id,ci.city_name,p1.product_name as dispenser,c.customer_name, b.installation_date,a.refill_date,a.next_refill_date',
                               'condition'=>$condition,
                               'limit'=>'5',
                               'order_by_field'=>'a.refill_date',
                               'order'=>'DESC',
                               //'group_by_field'=>'a.customer_name',
                               'offset'=>''
                              );
         return $this->master_model->select_data($selected_array);
        
	}

	function All_previous_due(){


       $user_type_id = $this->session->userdata('user_type_id');
       $user_type= $this->session->userdata('user_type');
       $user_id = $this->session->userdata('user_id');
       $company_branch_id=$this->session->userdata('company_branch_id');
       $access_branch= $this->session->userdata('access_branch');
       $acb =  explode( ',',$access_branch);

		    $condition[] = array('join',TABLE_DISPENSER_REFILL_DETAILS.' as a','b.dispenser_sales_id = a.dispenser_sales_id','inner');

		    $condition[] = array('join',TABLE_PRODUCT.' as p','a.product_id = p.product_id','inner');
		    $condition[] = array('join',TABLE_PRODUCT.' as p1','p.dispenser_name = p1.product_id','inner');
        $condition[] = array('join',TABLE_CUSTOMER.' as c','c.customer_id = b.customer_id','inner');
        $condition[] = array('join',TABLE_COMPANY_BRANCH.' as cb','cb.company_branch_id = c.company_branch_id','inner');
        $condition[] = array('join',TABLE_CITY.' as ci','ci.city_id = cb.city_id','inner');
        if ($user_type=='Sales Person') {
          $condition[] = array('where','b.sales_person_id',$user_id);
        }
        if ($access_branch!='') {
          $condition[] = array('where_in','b.company_branch_id',$acb);
        }
        //$condition[] = array('join',TABLE_USERS.' as u','u.user_id = b.sales_person_id','inner');

        $qry_part = 'next_refill_date < ( CURDATE( ) ) ';
        $condition[] = array('where',$qry_part);
        $selected_array=array(
                              'table_name'=>TABLE_DISPENSER_SALES.' as b',
                              'table_fields'=>'a.dispenser_refill_details_id,ci.city_name,p1.product_name as dispenser,c.customer_name, b.installation_date,a.refill_date,a.next_refill_date',
                               'condition'=>$condition,
                               'limit'=>'5',
                               'order_by_field'=>'a.refill_date',
                               'order'=>'DESC',
                               //'group_by_field'=>'a.customer_name',
                               'offset'=>''
                              );
        return $this->master_model->select_data($selected_array);
	}

  function next_seven_days(){


    //print_r($this->session->userdata);

       $user_type_id = $this->session->userdata('user_type_id');
       $user_id = $this->session->userdata('user_id');
       $user_type= $this->session->userdata('user_type');
       //echo $user_type;
       $company_branch_id=$this->session->userdata('company_branch_id');
       $user_id = $this->session->userdata('user_id');
       $access_branch= $this->session->userdata('access_branch');
       $acb =  explode( ',',$access_branch);

       // echo $user_type_id;
       // echo $company_branch_id;

        $condition[] = array('join',TABLE_DISPENSER_REFILL_DETAILS.' as a','b.dispenser_sales_id = a.dispenser_sales_id','inner');

        $condition[] = array('join',TABLE_PRODUCT.' as p','a.product_id = p.product_id','inner');
        $condition[] = array('join',TABLE_PRODUCT.' as p1','p.dispenser_name = p1.product_id','inner');
        $condition[] = array('join',TABLE_CUSTOMER.' as c','c.customer_id = b.customer_id','inner');
      
         $condition[] = array('join',TABLE_CUSTOMER_ADDRESSS.' as cb','cb.customer_id = c.customer_id','inner');
        $condition[] = array('join',TABLE_CITY.' as ci','ci.city_id = cb.city_id','inner');
       
        if ($user_type=='Sales Person') {
          $condition[] = array('where','b.sales_person_id',$user_id);
        }
        if ($access_branch!='') {
          $condition[] = array('where_in','b.company_branch_id',$acb);
        }
        
        $qry_part = 'next_refill_date BETWEEN NOW( ) AND DATE_ADD( NOW( ) , INTERVAL 30 DAY )';
        $condition[] = array('where',$qry_part);
        $selected_array=array(
                              'table_name'=>TABLE_DISPENSER_SALES.' as b',
                              'table_fields'=>'a.dispenser_refill_details_id,ci.city_name,p1.product_name as dispenser,c.customer_name, b.installation_date,a.refill_date,a.next_refill_date',
                               'condition'=>$condition,
                               'limit'=>'5',
                               'order_by_field'=>'a.refill_date',
                               'order'=>'DESC',
                               //'group_by_field'=>'a.customer_name',
                               'offset'=>''
                              );
        return  $this->master_model->select_data($selected_array);
         //dump($this->db->last_query());
       
        
  }

  function count_customer(){

    $user_type_id = $this->session->userdata('user_type_id');
    $user_type= $this->session->userdata('user_type');
    $user_id = $this->session->userdata('user_id');
    $company_branch_id=$this->session->userdata('company_branch_id');
    $access_branch= $this->session->userdata('access_branch');
    $acb =  explode( ',',$access_branch);
    if ($user_type=='Sales Person') {
          $condition_customer[]  = array('join',TABLE_DISPENSER_SALES.' as b','b.customer_id = c.customer_id','inner');
          $condition_customer[] = array('where','b.sales_person_id',$user_id);
        }
    if ($access_branch!='') {
        $condition_customer[]  = array('join',TABLE_COMPANY_BRANCH.' as d','d.company_branch_id = c.company_branch_id','inner');
          $condition[] = array('where_in','d.company_branch_id',$acb);
    }
  
    $condition_customer[] = array('where','c.is_active','1');
    $customer_details=array(
                      'table_name'=>TABLE_CUSTOMER.' as c',
                      'table_fields'=>'c.customer_name',
                      'condition'=>$condition_customer,
                      'limit'=>'',
                      'offset'=>''
                  );
    return $this->master_model->select_data($customer_details);
  }
  function count_user(){
    
    $user_type_id = $this->session->userdata('user_type_id');
    $user_id = $this->session->userdata('user_id');
    $user_type= $this->session->userdata('user_type');
    $company_branch_id=$this->session->userdata('company_branch_id');
    $access_branch= $this->session->userdata('access_branch');
    $acb =  explode( ',',$access_branch);

    $condition_user[] = array('where','a.is_active','1');
    $condition_user[] = array('where','a.user_type_id','6');
    if ($user_type=='Sales Person') {
          //$condition_user[]  = array('join',TABLE_DISPENSER_SALES.' as b','b.customer_id = c.customer_id','inner');
          $condition_user[] = array('where','a.user_id',$user_id);
    }
    
    if ($access_branch!='') {
          $condition_user[] = array('where_in','a.company_branch_id',$acb);
    }
    
    $user=array(
                      'table_name'=>TABLE_USERS.' as a',
                      'table_fields'=>'a.user_name',
                      'condition'=>$condition_user,
                      'limit'=>'',
                      'offset'=>''
                  );
   
       return $this->master_model->select_data($user);
       //dump($this->db->last_query());
    // $count=count($field);
    // return $count;

  }
  function count_dispenser(){
   
    $user_type_id = $this->session->userdata('user_type_id');
    $user_id = $this->session->userdata('user_id');
    $user_type= $this->session->userdata('user_type');
    $access_branch= $this->session->userdata('access_branch');
    $acb =  explode( ',',$access_branch);
  
     $condition_dispenser[] = array('where','product_type','dispenser');
     $condition_dispenser[] = array('where','p.is_active','1');
     if ($access_branch!='') {
       $condition_dispenser[] = array('join',TABLE_DISPENSER_SALES.' as b','b.product_id = p.product_id','inner');
          $condition_dispenser[] = array('where_in','b.company_branch_id',$acb);
     }
     if ($user_type=='Sales Person') {
       $condition_dispenser[] = array('join',TABLE_DISPENSER_SALES.' as d','d.product_id = p.product_id','inner');
          $condition_dispenser[] = array('where','d.sales_person_id',$user_id);
     }
     $dispenser_data=array(
                      'table_name'=>TABLE_PRODUCT.' as p',
                      'table_fields'=>'p.product_name',
                      'condition'=>$condition_dispenser,
                      'limit'=>'',
                      'group_by_field'=>'p.product_name',
                      'offset'=>''
                    );
    
      return $this->master_model->select_data($dispenser_data);
      // dump($this->db->last_query());

  } 
  // function count_refill(){
  //   admin_auth();
  //   $access_branch= $this->session->userdata('access_branch');
  //   $acb =  explode( ',',$access_branch);

  //   $condition_refill[] = array('join',TABLE_DISPENSER_SALES.' as d','d.product_id = p.product_id','left');
  //   $condition_refill[]=array('where','p.product_type','refill');
  //   $condition_refill[] = array('where','p.is_active','1');
  //   if ($access_branch!='') {
  //         $condition[] = array('where_in','d.company_branch_id',$acb);
  //   }
  //   $refill_data=array(
  //                     'table_name'=>TABLE_PRODUCT.' as p',
  //                     'table_fields'=>'p.product_name',
  //                     'condition'=>$condition_refill,
  //                     'limit'=>'5',
  //                     'offset'=>''
  //                   );
    
  //    return $this->master_model->select_data($refill_data);
     
  // }
  function fetch_branch_name(){
      
      $user_type_id = $this->session->userdata('user_type_id');
      $user_id = $this->session->userdata('user_id');
      $user_type= $this->session->userdata('user_type');
      $access_branch= $this->session->userdata('access_branch');
      $acb =  explode( ',',$access_branch);
      $condition[] = array('where','a.is_active','1');
       if ($access_branch!='') {
          $condition[] = array('where_in','a.company_branch_id',$acb);
       }
      if ($user_type=='Sales Person') {
          $condition[]  = array('join',TABLE_DISPENSER_SALES.' as b','b.company_branch_id= a.company_branch_id','inner');
          $condition[] = array('where','b.sales_person_id',$user_id);
      } 
      $selected_array = array( 
                         'table_name' =>TABLE_COMPANY_BRANCH.' as a',
                         'table_fields' => 'a.company_branch_id,a.company_branch_name,a.is_active',
                         'condition'=>$condition,
                         'order_by_field'=>'a.company_branch_name',
                         'group_by_field'=>'a.company_branch_name',
                         'order'=>'ASC',
                         'limit'=>'',
                         'offset'=>''
    );

    return $this->master_model->select_data($selected_array);             
    }


 function fetch_customer(){
    
    $user_type_id = $this->session->userdata('user_type_id');
    $user_id = $this->session->userdata('user_id');
    $user_type= $this->session->userdata('user_type');
    $access_branch= $this->session->userdata('access_branch');
    $acb =  explode( ',',$access_branch);

    $condition[] = array('join',TABLE_DISPENSER_SALES.' as d','d.customer_id = c.customer_id','left');
    if ($access_branch!='') {
          $condition[] = array('where_in','d.company_branch_id',$acb);
       }
    if ($user_type=='Sales Person') {
          
          $condition[] = array('where','d.sales_person_id',$user_id);
      }

    $selected_array=array(
                            'table_name'=>TABLE_CUSTOMER.' as c',
                            'table_fields'=>'c.customer_name,COUNT(d.dispenser_sales_id) as total_sales,c.customer_pan_no',
                            'condition'=>$condition,
                            'limit'=>'5',
                            'order_by_field'=>'total_sales',
                            'order'=>'DESC',
                            'group_by_field'=>'c.customer_pan_no',
                            'offset'=>''
                              );
        $customer_data = $this->master_model->select_data($selected_array);
       // dump($this->db->last_query());
        $count=count($customer_data);
        if($count>0){
        
        $output='';
        foreach($customer_data as $row)
        {
            $output .= '<tr>';          
            $output .= '<td>'.$row['customer_name'].'</td>';
            $output .= '<td>'.$row['total_sales'].'</td>';
            $output .= '</tr>'; 

        }
      }else{
           $output .= '<tr>'; 
           $output .= '<td colspan="12"><strong>no records found </strong></td>';
           $output .= '</tr>'; 
      }

        echo $output;
  }
  function fetch_sale_person(){
   
     $user_id = $this->session->userdata('user_id');
     $user_type= $this->session->userdata('user_type');
   
      $access_branch= $this->session->userdata('access_branch');
      $acb =  explode( ',',$access_branch);
      if ($access_branch!='') {
        $condition_dispenser[] = array('where_in','d.company_branch_id',$acb);
      }
      if ($user_type=='Sales Person') {
          
          $condition_dispenser[] = array('where','d.sales_person_id',$user_id);
      }
        $condition_dispenser[] = array('join',TABLE_USERS.' as u','u.user_id = d.sales_person_id','inner');
      $fieldsarray = 'u.first_name, u.last_name, COUNT( d.dispenser_sales_id ) AS sales';
      //$condition_dispenser[] = array('where','u.user_type_id','6');

      $selected_array=array(
                            'table_name'=>TABLE_DISPENSER_SALES.' as d',
                            'table_fields'=>$fieldsarray,
                            'condition'=>$condition_dispenser,
                            'limit'=>'5',
                            'order_by_field'=>'sales',
                            'order'=>'DESC',
                            'group_by_field'=>'u.first_name, u.last_name',
                            'offset'=>''
                              );
      $sale_data = $this->master_model->select_data($selected_array);
      $count=count($sale_data);
      if($count>0){
        //dump($this->db->last_query());
          $output='';
          foreach($sale_data as $row)
          {
            $output .= '<tr>';           
            $output .= '<td>'.$row['first_name']." ".$row['last_name'].'</td>';
            $output .= '<td>'.$row['sales'].'</td>';
            $output .= '</tr>'; 

          }
      }else{
        $output .= '<tr>'; 
        $output .= '<td colspan="12"><strong>no records found </strong></td>';
        $output .= '</tr>'; 
      }

        echo $output;
  }

  function fetch_dispenser(){

    
    $user_id = $this->session->userdata('user_id');
     $user_type= $this->session->userdata('user_type');
        $access_branch= $this->session->userdata('access_branch');
        $acb =  explode( ',',$access_branch);
        $condition_dispenser[] = array('where','p.product_type','dispenser');
        if ($access_branch!='') {
          $condition_dispenser[] = array('where_in','d.company_branch_id',$acb);
        }
        if ($user_type=='Sales Person') {
          
          $condition_dispenser[] = array('where','d.sales_person_id',$user_id);
      }
        $condition_dispenser[] = array('join',TABLE_DISPENSER_SALES.' as d','d.product_id = p.product_id','inner');
        $fieldsarray = 'p.product_name,d.dispenser_sales_id,SUM( p.price ) AS dispenser_price';

        $selected_array=array(
                            'table_name'=>TABLE_PRODUCT.' as p',
                            'table_fields'=>$fieldsarray,
                            'condition'=>$condition_dispenser,
                            'limit'=>'5',
                            'order_by_field'=>'dispenser_price',
                            'group_by_field'=>'p.product_name',
                            'order'=>'DESC',
                            'offset'=>''
                              );
        $dispenser_data = $this->master_model->select_data($selected_array);
        $count=count($dispenser_data);
        if($count>0){
        //dump($this->db->last_query());
        $output='';
        foreach($dispenser_data as $row)
        {
            $output .= '<tr>';           
            $output .= '<td>'.$row['product_name'].'</td>';
            $output .= '<td>'.$row['dispenser_price'].'</td>';
            $output .= '</tr>'; 

        }
      }else{
        $output .= '<tr>'; 
        $output .= '<td colspan="12"><strong>no records found </strong></td>';
        $output .= '</tr>'; 
      }

        echo $output;
  }
  function fetch_refill(){
 

        $user_id = $this->session->userdata('user_id');
        $user_type= $this->session->userdata('user_type');
        $access_branch= $this->session->userdata('access_branch');
        $acb =  explode( ',',$access_branch);
        $condition_dispenser[] = array('where','p.product_type','refill');
        if ($access_branch!='') {
          $condition_dispenser[] = array('where_in','d.company_branch_id',$acb);
        }
        if ($user_type=='Sales Person') {
          
          $condition_dispenser[] = array('where','d.sales_person_id',$user_id);
      }
        $condition_dispenser[] = array('join',TABLE_DISPENSER_SALES.' as d','d.product_id = p.product_id','inner');
        $fieldsarray = 'p.product_name,d.dispenser_sales_id,SUM( p.price ) AS dispenser_price';

        $selected_array=array(
                            'table_name'=>TABLE_PRODUCT.' as p',
                            'table_fields'=>$fieldsarray,
                            'condition'=>$condition_dispenser,
                            'limit'=>'5',
                            'order_by_field'=>'dispenser_price',
                            'group_by_field'=>'p.product_name',
                            'order'=>'DESC',
                            'offset'=>''
                              );
        $dispenser_data = $this->master_model->select_data($selected_array);
        $count=count($dispenser_data);
        if($count>0){
        //dump($this->db->last_query());
        $output='';
        foreach($dispenser_data as $row)
        {
            $output .= '<tr>';           
            $output .= '<td>'.$row['product_name'].'</td>';
            $output .= '<td>'.$row['dispenser_price'].'</td>';
            $output .= '</tr>'; 

        }
      }else{
        $output .= '<tr>';
        $output .= '<td colspan="12"><strong>no records found </strong></td>';
        $output .= '</tr>';
      }

        echo $output;
  }  

  function fetch_search_result(){
   

    $branch= check_injections_filter($this->input->post('branch'));
    $date_picker2= check_injections_filter(get_date_ymd_format_wo_time_new($this->input->post('date_picker2')));
    $date_picker3= check_injections_filter(get_date_ymd_format_wo_time_new($this->input->post('date_picker3')));

    $data[]='';
    $data['customer']=$this->fetch_customer_search($date_picker2,$date_picker3,$branch);
    $data['sale_person']=$this->fetch_sale_person_search($date_picker2,$date_picker3,$branch);
    $data['dispenser']=$this->fetch_dispenser_search($date_picker2,$date_picker3,$branch);
    $data['refill']=$this->fetch_refill_search($date_picker2,$date_picker3,$branch);
    echo json_encode($data);
    // print_r($data);
    // exit;
  } 
// ---------------------------search----------------------------------------------
  function fetch_customer_search($date_picker2,$date_picker3,$branch){
   
    $user_id = $this->session->userdata('user_id');
    $user_type= $this->session->userdata('user_type');
    $access_branch= $this->session->userdata('access_branch');
    $acb =  explode( ',',$access_branch);
    $condition[] = array('join',TABLE_CUSTOMER.' as c','c.company_branch_id = b.company_branch_id','left');
    $condition[] = array('join',TABLE_DISPENSER_SALES.' as d','d.customer_id = c.customer_id','left');
    if($branch>0 AND $date_picker2!='1970-01-01' AND $date_picker3!='1970-01-01'){
    $qry_part = 'b.company_branch_id = "'.$branch.'" AND d.installation_date BETWEEN "'.$date_picker2.'" AND "'.$date_picker3.' "';

    $condition[] = array('where',$qry_part);
   }
     if ($access_branch!='') {
          $condition[] = array('where_in','b.company_branch_id',$acb);
      }
      if ($user_type=='Sales Person') {
          
          $condition[] = array('where','d.sales_person_id',$user_id);
      }
      if($branch>0){
       $condition[] = array('where','b.company_branch_id',$branch);
      }
      
    $selected_array=array(
                            'table_name'=>TABLE_COMPANY_BRANCH.' as b',
                            'table_fields'=>'c.customer_name, b.company_branch_name, COUNT(d.dispenser_sales_id) as total_sales',
                            'condition'=>$condition,
                            'limit'=>'5',
                            'order_by_field'=>'total_sales',
                            'order'=>'DESC',
                            'group_by_field'=>'c.customer_name',
                            'offset'=>''
                              );
    $customer_data = $this->master_model->select_data($selected_array);
    $count=count($customer_data);
      if($count>0){
        $output='';
          foreach($customer_data as $row)
           {
            $output .='<tr>';         
            $output .= '<td>'.$row['customer_name'].'</td>';
            $output .= '<td>'.$row['total_sales'].'</td>';
            $output .='</tr>'; 
           }
      }else{
        $output .='<tr>';
        $output .= '<td colspan="12"><strong>no records found</strong></td>';
        $output .='</tr>'; 
      }

        return $output;

  }
  function fetch_sale_person_search($date_picker2,$date_picker3,$branch){
    

      $user_id = $this->session->userdata('user_id');
      $user_type= $this->session->userdata('user_type');
      $access_branch= $this->session->userdata('access_branch');
      $acb =  explode( ',',$access_branch);
       if($branch>0){
       $condition_dispenser[] = array('where','d.company_branch_id',$branch);
      }
      if ($access_branch!='') {
        $condition_dispenser[] = array('where_in','d.company_branch_id',$acb);
      }

      if ($user_type=='Sales Person') {
          
          $condition_dispenser[] = array('where','d.sales_person_id',$user_id);
      }
        $condition_dispenser[] = array('join',TABLE_USERS.' as u','u.user_id = d.sales_person_id','inner');
       $fieldsarray = 'u.first_name, u.last_name, COUNT( d.dispenser_sales_id ) AS sales';
      //$condition_dispenser[] = array('where','u.user_type_id','6');
       if($branch>0 AND $date_picker2!='1970-01-01' AND $date_picker3!='1970-01-01'){
      $qry_part = 'd.company_branch_id = "'.$branch.'" AND d.installation_date BETWEEN "'.$date_picker2.'" AND "'.$date_picker3.' "';

      $condition_dispenser[] = array('where',$qry_part);
    }

      $selected_array=array(
                            'table_name'=>TABLE_DISPENSER_SALES.' as d',
                            'table_fields'=>$fieldsarray,
                            'condition'=>$condition_dispenser,
                            'limit'=>'5',
                            'order_by_field'=>'sales',
                            'order'=>'DESC',
                            'group_by_field'=>'u.first_name, u.last_name',
                            'offset'=>''
                              );
      $sale_data = $this->master_model->select_data($selected_array);
      $count=count($sale_data);
      if($count>0){
      //dump($this->db->last_query());
          $output_sale_person='';
          foreach($sale_data as $row)
          {
            $output_sale_person .= '<tr>';           
            $output_sale_person .= '<td>'.$row['first_name']." ".$row['last_name'].'</td>';
            $output_sale_person .= '<td>'.$row['sales'].'</td>';
            $output_sale_person .= '</tr>'; 

          }
      }else{
        $output_sale_person .= '<tr>'; 
        $output_sale_person .= '<td colspan="12"><strong>no records found </strong></td>';
        $output_sale_person .= '</tr>'; 
      }

        return $output_sale_person;

  }
  function fetch_dispenser_search($date_picker2,$date_picker3,$branch){
  

        $user_id = $this->session->userdata('user_id');
        $user_type= $this->session->userdata('user_type');
        $access_branch= $this->session->userdata('access_branch');
        $acb =  explode( ',',$access_branch);
        $condition_dispenser[] = array('where','p.product_type','dispenser');
        if($branch>0){
          $condition_dispenser[] = array('where','d.company_branch_id',$branch);
        }
        if ($access_branch!='') {
          $condition_dispenser[] = array('where_in','d.company_branch_id',$acb);
        }
         if ($user_type=='Sales Person') {
          
          $condition_dispenser[] = array('where','d.sales_person_id',$user_id);
        }
        $condition_dispenser[] = array('join',TABLE_DISPENSER_SALES.' as d','d.product_id = p.product_id','inner');
        $fieldsarray = 'p.product_name,d.dispenser_sales_id,SUM( p.price ) AS dispenser_price';
        if($branch>0 AND $date_picker2!='1970-01-01' AND $date_picker3!='1970-01-01'){
        $qry_part = 'd.company_branch_id = "'.$branch.'" AND d.installation_date BETWEEN "'.$date_picker2.'" AND "'.$date_picker3.' "';

        $condition_dispenser[] = array('where',$qry_part);
      }

        $selected_array=array(
                            'table_name'=>TABLE_PRODUCT.' as p',
                            'table_fields'=>$fieldsarray,
                            'condition'=>$condition_dispenser,
                            'limit'=>'5',
                            'order_by_field'=>'dispenser_price',
                            'order'=>'DESC',
                            'group_by_field'=>'p.product_name',
                            'offset'=>''
                              );
        $dispenser_data = $this->master_model->select_data($selected_array);
        $count=count($dispenser_data);
        if($count>0){
        //dump($this->db->last_query());
        $output='';
        foreach($dispenser_data as $row)
        {
            $output_dispenser .= '<tr>';           
            $output_dispenser .= '<td>'.$row['product_name'].'</td>';
            $output_dispenser .= '<td>'.$row['dispenser_price'].'</td>';
            $output_dispenser .= '</tr>'; 

        }
      }else{
        $output_dispenser .= '<tr>'; 
        $output_dispenser .= '<td colspan="12"><strong>no records found </strong></td>';
        $output_dispenser .= '</tr>';
      }

        return $output_dispenser;
  }
    function fetch_refill_search($date_picker2,$date_picker3,$branch){
       
        $user_id = $this->session->userdata('user_id');
        $user_type= $this->session->userdata('user_type');
        $access_branch= $this->session->userdata('access_branch');
        $acb =  explode( ',',$access_branch);
        $condition_dispenser[] = array('where','p.product_type','refill');
        if($branch>0){
          $condition_dispenser[] = array('where','d.company_branch_id',$branch);
        }

        if ($access_branch!='') {
          $condition_dispenser[] = array('where_in','d.company_branch_id',$acb);
        }
        if ($user_type=='Sales Person') {
          
          $condition_dispenser[] = array('where','d.sales_person_id',$user_id);
        }
        $condition_dispenser[] = array('join',TABLE_DISPENSER_SALES.' as d','d.product_id = p.product_id','inner');
        $fieldsarray = 'p.product_name,d.dispenser_sales_id,SUM( p.price ) AS dispenser_price';

        if($branch>0 AND $date_picker2!='1970-01-01' AND $date_picker3!='1970-01-01'){
         $qry_part = 'd.company_branch_id = "'.$branch.'" AND d.installation_date BETWEEN "'.$date_picker2.'" AND "'.$date_picker3.' "';
         $condition_dispenser[] = array('where',$qry_part);
        }

        

        $selected_array=array(
                            'table_name'=>TABLE_PRODUCT.' as p',
                            'table_fields'=>$fieldsarray,
                            'condition'=>$condition_dispenser,
                            'limit'=>'5',
                            'order_by_field'=>'dispenser_price',
                            'order'=>'DESC',
                            'group_by_field'=>'p.product_name',
                            'offset'=>''
                              );
        $dispenser_data = $this->master_model->select_data($selected_array);
       // dump($this->db->last_query());
        $count=count($dispenser_data);
        if($count>0){
        $output='';
        foreach($dispenser_data as $row)
        {
            $output_refill.= '<tr>';           
            $output_refill.= '<td>'.$row['product_name'].'</td>';
            $output_refill.= '<td>'.$row['dispenser_price'].'</td>';
           $output_refill.= '</tr>'; 

        }
      }else{
        $output_refill.= '<tr>';
        $output_refill .= '<td colspan="12"><strong>no records found </strong></td>';
        $output_refill.= '</tr>'; 
      }

        return $output_refill ;
  }
  	
}
?>