<?php
class Dashboard extends CI_Controller {

	function __construct(){
		parent::__construct();
    $language = $this->session->userdata('language'); 
    if ($language=='hindi') {
      $this->lang->load('hindi', '');
    }else{
      $this->lang->load('masters', '');
    }
    //error_reporting(E_ALL);
	}

	function index(){	
		$this->dashboard();
	}
	
	function dashboard(){
		
		
		admin_auth();
    $this->_page_data['d_Customer_Name']=$this->lang->line('d_Customer_Name', FALSE);
    $this->_page_data['d_City']=$this->lang->line('d_City', FALSE);
    $this->_page_data['d_Dispenser']=$this->lang->line('d_Dispenser', FALSE);
    $this->_page_data['d_Last_refill_date']=$this->lang->line('d_Last_refill date', FALSE);
    $this->_page_data['d_Total_Customers']=$this->lang->line('d_Total_Customers', FALSE);
    $this->_page_data['d_Total_Sales Person']=$this->lang->line('d_Total_Sales Person', FALSE);
    $this->_page_data['d_Total_Dispenser']=$this->lang->line('d_Total_Dispenser', FALSE);
    $this->_page_data['d_Total_Refill']=$this->lang->line('d_Total_Refill', FALSE);
    $this->_page_data['d_From_Date']=$this->lang->line('d_From_Date', FALSE);
    $this->_page_data['d_Branch']=$this->lang->line('d_Branch', FALSE);
    $this->_page_data['d_To_Date']=$this->lang->line('d_To_Date', FALSE);
    $this->_page_data['d_Total_Sales']=$this->lang->line('d_Total_Sales', FALSE);
    $this->_page_data['d_Total_Quantity']=$this->lang->line('d_Total_Quantity', FALSE);
    $this->_page_data['d_Refill_Name']=$this->lang->line('d_Refill_Name', FALSE);
    $this->_page_data['d_Dispenser_Name']=$this->lang->line('d_Dispenser_Name', FALSE);
    $this->_page_data['d_Quantity']=$this->lang->line('d_Quantity', FALSE);
    $this->_page_data['d_Todays_Due']=$this->lang->line('d_Todays_Due', FALSE);
    $this->_page_data['d_All_Previous_Due']=$this->lang->line('d_All_Previous_Due', FALSE);
    $this->_page_data['d_Next_30_Days_Due']=$this->lang->line('d_Next_30_Days_Due', FALSE);
    $this->_page_data['d_Sales_Person_Name']=$this->lang->line('d_Sales_Person_Name', FALSE);
    $this->_page_data['d_Top_5_Customer']=$this->lang->line('d_Top_5_Customer', FALSE);
    $this->_page_data['d_Top_5_Sales_Person']=$this->lang->line('d_Top_5_Sales_Person', FALSE);
    $this->_page_data['d_Top_5_Dispenser']=$this->lang->line('d_Top_5_Dispenser', FALSE);
    $this->_page_data['Top_5_Refills']=$this->lang->line('Top_5_Refills', FALSE);
    $this->_page_data['d_total_sales_person_name']=$this->lang->line('d_total_sales_person_name', FALSE);
     $this->_page_data['d_Last_refill_date']=$this->lang->line('d_Last_refill_date', FALSE);
    $this->_page_data['d_No_refill_due_today']=$this->lang->line('d_No_refill_due_today', FALSE);
    $this->_page_data['d_view_all']=$this->lang->line('d_view_all', FALSE);
    $this->_page_data['d_no_data_available']=$this->lang->line('d_no_data_available', FALSE);
    $this->_page_data['d_no_records_found']=$this->lang->line('d_no_records_found', FALSE);
    $this->_page_data['d_total_sales_price']=$this->lang->line('d_total_sales_price', FALSE);

		$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','b.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','left');
        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);
		 $condition[] = array('where','a.is_active','1');
		 $condition[] = array('where','b.is_active','1');
		 $condition[] = array('where','a.stop_refill','no');
		$condition[] = array('where','a.next_has_been_refilled','no');
		$condition[] = array('where','b.dispenser_quantity >','0');
         $selected_array=array(
                              'table_name'=>TABLE_DISPENSER_SALES.' as b',
                              'table_fields'=>'a.dispenser_refill_details_id,ci.city_name,p.product_name as dispenser,c.customer_name, b.installation_date,a.refill_date,a.next_refill_date,b.product_id as dispenser_id,b.dispenser_quantity',
                               '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','b.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_CUSTOMER_ADDRESSS.' as cb','cb.customer_id = c.customer_id','left');
        $condition[] = array('join',TABLE_CITY.' as ci','ci.city_id = cb.city_id','left');
        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);
		$condition[] = array('where','a.is_active','1');
		 $condition[] = array('where','b.is_active','1');
		$condition[] = array('where','a.stop_refill','no');
		$condition[] = array('where','a.next_has_been_refilled','no');
		$condition[] = array('where','b.dispenser_quantity >','0');
        $selected_array=array(
                              'table_name'=>TABLE_DISPENSER_SALES.' as b',
                              'table_fields'=>'a.dispenser_refill_details_id,ci.city_name,p.product_name as dispenser,c.customer_name, b.installation_date,a.refill_date,a.next_refill_date,b.product_id as dispenser_id,b.dispenser_quantity',
                               'condition'=>$condition,
                               'limit'=>'5',
                               'order_by_field'=>'a.refill_date',
                               'order'=>'DESC',
                               'offset'=>''
                              );
         return $this->master_model->select_data($selected_array);
         //dump($this->db->last_query());
	}

  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','b.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','left');
        $condition[] = array('join',TABLE_CITY.' as ci','ci.city_id = cb.city_id','left');
       
        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);
		$condition[] = array('where','a.stop_refill','no');
		$condition[] = array('where','a.is_active','1');
		 $condition[] = array('where','b.is_active','1');
		$condition[] = array('where','a.next_has_been_refilled','no');
		$condition[] = array('where','b.dispenser_quantity >','0');
        $selected_array=array(
                              'table_name'=>TABLE_DISPENSER_SALES.' as b',
                              'table_fields'=>'a.dispenser_refill_details_id,ci.city_name,p.product_name as dispenser,c.customer_name, b.installation_date,a.refill_date,a.next_refill_date,b.product_id as dispenser_id,b.dispenser_quantity',
                               'condition'=>$condition,
                               'limit'=>'5',
                               'order_by_field'=>'a.refill_date',
                               'order'=>'DESC',
							   'group_by_field'=>'b.dispenser_sales_id',
                               '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.is_active','1');
         // $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_customer[] = 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'=>''
                  );
	
    $cust_result= $this->master_model->select_data($customer_details);
	//dump($this->db->last_query());
	 return $cust_result;
  }
  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','2');
    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);
		   $condition_dispenser[] = array('where','b.is_active','1');
     }
     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.is_active','1');
         // $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(){
	    $user_type= $this->session->userdata('user_type');
     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); */
	  
	   $condition_dispenser[] = array('where','p.product_type','refill');
       
        if ($user_type=='Sales Person') {
          
          $condition_dispenser[] = array('where','d.sales_person_id',$user_id);
        }
      
	  
	  $condition_dispenser[] = array('join',TABLE_PRODUCT. ' as p','p.product_id=a.product_id','inner');
      $condition_dispenser[] = array('join',TABLE_DISPENSER_SALES. ' as d','d.dispenser_sales_id=a.dispenser_sales_id','inner');
     if ($access_branch!='') {
         $condition_dispenser[] = array('where_in','d.company_branch_id',$acb);
     }
      $selected_array=array(
                            'table_name'=>TABLE_DISPENSER_REFILL_DETAILS.' as a',
                            'table_fields'=>'p.product_name',
                            'condition'=>$condition_dispenser,
                            'limit'=>'',
                            'order_by_field'=>'refill_date',
                            'group_by_field'=>'p.product_id',
                            'order'=>'DESC',
                            'offset'=>''
                         );
    	return $this->master_model->select_data($selected_array);
	  //dump($this->db->last_query());
     
   }
  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');
   

    $condition[] = array('join',TABLE_DISPENSER_SALES.' as d','d.customer_id = c.customer_id','inner');
	$condition[] = array('where','d.is_active','1');
    if ($access_branch!='') {
         $acb =  explode( ',',$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',
                            '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);
        //dump($this->db->last_query());
        $count=count($customer_data);
        $output='';
        if($count>0){
        
        
        foreach($customer_data as $row)
        {
            $output .= '<tr>';          
            $output .= '<td>'.$row['customer_name'].'</td>';
            $output .= '<td>'.number_format($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');
		$condition_dispenser[] = array('where','d.is_active','1');
      $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);
       $output='';
      if($count>0){
        //dump($this->db->last_query());
         
          foreach($sale_data as $row)
          {
            $output .= '<tr>';           
            $output .= '<td>'.$row['first_name']." ".$row['last_name'].'</td>';
            $output .= '<td>'.number_format($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( d.price ) AS dispenser_price';
		$condition_dispenser[] = array('where','d.is_active','1');
        $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);
           $output='';
        if($count>0){
        //dump($this->db->last_query());
     
        foreach($dispenser_data as $row)
        {
            $output .= '<tr>';           
            $output .= '<td>'.$row['product_name'].'</td>';
            $output .= '<td>'.number_format($row['dispenser_price'], 2,'.',',').'</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); */
		
		
		
		$condition_dispenser[] = array('where','p.product_type','refill');
       
        if ($access_branch!='') {
          $condition_dispenser[] = array('where_in','d.company_branch_id',$acb);
        }
        
        $fieldsarray = 'p.product_name,d.dispenser_sales_id,SUM( d.price ) AS dispenser_price';

		$condition_dispenser[] = array('join',TABLE_PRODUCT. ' as p','p.product_id=a.product_id','inner');
		$condition_dispenser[] = array('join',TABLE_DISPENSER_SALES. ' as d','d.dispenser_sales_id=a.dispenser_sales_id','inner');
     
		$selected_array=array(
                            'table_name'=>TABLE_DISPENSER_REFILL_DETAILS.' as a',
                            'table_fields'=>$fieldsarray,
                            'condition'=>$condition_dispenser,
                            'limit'=>'5',
                            'order_by_field'=>'dispenser_price',
                            'group_by_field'=>'p.product_id',
                            'order'=>'DESC',
                            'offset'=>''
                         );
    	$dispenser_data = $this->master_model->select_data($selected_array);
		
		//dump($this->db->last_query());
        $count=count($dispenser_data);
        $output='';
        if($count>0){
        
        
        foreach($dispenser_data as $row)
        {
            $output .= '<tr>';           
            $output .= '<td>'.$row['product_name'].'</td>';
            $output .= '<td>'.number_format($row['dispenser_price'],2,'.',',').'</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')));

    $date1=check_injections_filter($this->input->post('date_picker2'));
    if ($date1!='') {
      $date_picker2=get_date_ymd_format_wo_time_new($date1);
    }else{
      $date_picker2 = $date1;
    }
    $date2=check_injections_filter($this->input->post('date_picker3'));
    if ($date2!='') {
       $date_picker3=get_date_ymd_format_wo_time_new($date2);
    }else{
      $date_picker3 = $date2;
    }

    $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);
    
  } 
// ---------------------------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){
    $qry_part = 'b.company_branch_id = "'.$branch.'" ';

    $condition[] = array('where',$qry_part);
   }
   if($date_picker2!='' AND $date_picker3!=''){
    $qry_part = '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);
	//dump($this->db->last_query());
        
    $count=count($customer_data);
    $output='';
      if($count>0){
        
          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!='' AND $date_picker3!=''){
      $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);
       $output_sale_person='';
      if($count>0){
      //dump($this->db->last_query());
         
          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( d.price ) AS dispenser_price';
        if($branch>0 AND $date_picker2!='' AND $date_picker3!=''){
        $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);
        $output_dispenser='';
        if($count>0){
        //dump($this->db->last_query());
        
        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( d.price ) AS dispenser_price';

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

	   
      $condition_dispenser[] = array('join',TABLE_PRODUCT. ' as p','p.product_id=a.product_id','inner');
      $condition_dispenser[] = array('join',TABLE_DISPENSER_SALES. ' as d','d.dispenser_sales_id=a.dispenser_sales_id','inner');
     
      $selected_array=array(
                            'table_name'=>TABLE_DISPENSER_REFILL_DETAILS.' as a',
                            'table_fields'=>$fieldsarray,
                            'condition'=>$condition_dispenser,
                            'limit'=>'5',
                            'order_by_field'=>'dispenser_price',
                            'group_by_field'=>'p.product_id',
                            'order'=>'DESC',
                            'offset'=>''
                         );
    	$dispenser_data = $this->master_model->select_data($selected_array);
        

       /*  $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);
        $output_refill='';
        if($count>0){
        
        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 ;
  }
  	
}
?>