<?php	if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Customer_tracking_reports extends CI_Controller {

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

	}

	function index(){	

		 $this->show_tracking();
	}

	function show_tracking($query_id = 0,$sort_by = 'd.dispenser_sales_id',$sort_order = 'asc',$offset = 0,$sort_type=0){
	    admin_auth();
	    $limit = LIMIT_PER_PAGE;		
		$this->page_data['page_title']="Customer Tracking Reports";
		$this->page_data['bredcrum']=array(
										'Reports'=>'#',
										'Customer Tracking Reports'=>'#'										
										);


		$this->load->library('NF_Query_search');
		$this->nf_query_search->load_query($query_id);

		$query_array = array(						
				'dispenser_name'=>$this->input->get('dispenser_name'),
 				'customer_name'=>$this->input->get('customer_name'),
 				'tracking_number'=>$this->input->get('tracking_number'),

		);
		$dispenser_name=check_injections_filter($query_array['dispenser_name']);
		$customer_name=check_injections_filter($query_array['customer_name']);
		$tracking_number=check_injections_filter($query_array['tracking_number']);

		/*nityam:geetanjali 21-Feb-2020 added brach column in the list*/
		$access_branch= $this->session->userdata('access_branch');
		$condition = array();
		$condition[] = array('join',TABLE_CUSTOMER.' as c','c.customer_id = t.possessed_by','left');
		$condition[] = array('join',TABLE_PRODUCT.' as p','p.product_id = t.product_id','left');
		$condition[] = array('join',TABLE_COMPANY_BRANCH.' as b','b.company_branch_id = c.company_branch_id','left');
		$condition[] = array('join',TABLE_TRACKING_DETAILS.' as dt','dt.tracking_numbers_id = t.tracking_numbers_id','left');
		$condition[] = array('join',TABLE_DISPENSER_SALES.' as d','d.dispenser_sales_id = dt.dispenser_sales_id','left');
        $condition[] = array('where','t.is_status','Customer');

       	if ($access_branch!='') {
       		$acb =  explode(',',$access_branch);
            $condition[] = array('where_in','b.company_branch_id',$acb);
        }
        if ($customer_name!='') {
        	$condition[] = array('like','c.customer_name',$customer_name);
        	$this->page_data['customer_name_hidden']=$customer_name;
        }else{
        	$this->page_data['customer_name_hidden']='';
        }
        if ($dispenser_name!='') {
        	$condition[] = array('like','p.product_name',$dispenser_name);
        	$this->page_data['dispenser_name_hidden']=$dispenser_name;
        }else{
        	$this->page_data['dispenser_name_hidden']='';
        }
		if ($tracking_number!='') {
        	$condition[] = array('like','t.tracking_number',$tracking_number);
        	$this->page_data['tracking_number_hidden']=$tracking_number;
        }else{
        	$this->page_data['tracking_number_hidden']='';
        }
		
		

		$this->page_data['fields'] = array(
							'tracking_number' => 'Tracking Number',
							'customer_name' => 'Currently Possessed By',
							'installation_date' => 'First Transaction Date',
							'product_name' => 'Dispenser Name',
							'company_branch_name' => 'Branch',
						);
        
		$sort_columns = array('t.tracking_number','c.customer_name','d.installation_date','p.product_name','b.company_branch_name');
    
                              
		$selected_array=array(
                              'table_name'=>TABLE_TRACKING_NUMBERS.' as t',
                              'table_fields'=>'t.tracking_number,c.customer_name,c.customer_id, t.is_status,p.product_name,p.product_type, b.company_branch_name,dt.dispenser_sales_id,d.installation_date,d.dispenser_sales_id,d.product_id',
                              'group_by_field'=>'t.tracking_number',
                               'cond'=>$condition,
                               'order_by_field'=>'t.tracking_number',
								'order'=>'ASC',
                               'limit' => $limit,
								'offset' => $offset,
								'sort_by' => $sort_by,
								'sort_type' => $sort_type,
								'sort_order' => $sort_order,
								'sort_columns'=> $sort_columns,
								
                              );
		$results = $this->master_model->search($selected_array);
		$selected_array_data =array(
							'limit' =>'',
							'offset' =>'',
							'sort_by' => $sort_by,
							'sort_type' => $sort_type,
							'sort_order' => $sort_order,
							'sort_columns'=> $sort_columns,
							'table_fields'=>'t.tracking_number,c.customer_name, c.customer_id, t.is_status, p.product_name, p.product_type, b.company_branch_name,dt.dispenser_sales_id,d.installation_date,d.dispenser_sales_id,d.product_id',
							'group_by_field'=>'t.tracking_number',	
							'order_by_field'=>'t.tracking_number',
							'order'=>'ASC',					
							'table_name'=>TABLE_TRACKING_NUMBERS.' as t',
							'condition'=> $condition
							);
		$sale_data = $this->master_model->select_data($selected_array_data);
		$cnt_all_rows=count($sale_data);		
		if(!empty($results)&& is_array($sale_data))
		{
			$this->page_data['customer_report'] = $results['rows'];
			$this->page_data['num_results'] = $cnt_all_rows;
		}
		else
		{
			$results='';
		}	      
		$this->page_data['offset'] = $offset;
		/// pagination
		$this->load->library('pagination');
		$config = array();
		$config['base_url'] = site_url("gc/customer_tracking_reports/show_tracking/$query_id/$sort_by/$sort_order");
		$config['total_rows'] = $this->page_data['num_results'];
		$config['per_page'] = $limit;
		$config['uri_segment'] = 7;
		$config['full_tag_open'] = '<span class=\'paging\'>';
		$config['full_tag_close'] = '</span>';
		$config['first_link'] = 'First';
		$config['prev_link'] = '<<';
		$config['next_link'] = '>>';
		$config['last_link'] = 'Last';
		$this->pagination->initialize($config);
		
		$this->page_data['pagination'] = $this->pagination->create_links();
		$this->page_data['sort_by'] = $sort_by;
		$this->page_data['sort_order'] = $sort_order;
		$this->page_data['sort_type'] = $sort_type;
		$this->page_data['sort_columns'] = $sort_columns;
		$this->page_data['query_id'] = $query_id;
		$this->load->view('gc/customer_tracking_reports_list.php',$this->page_data);
			
	}
	function search_customer_report_tracking()
	{
		$query_array = array(
			
						'customer_name' =>check_injections_filter($this->input->post('customer_name')),
						'dispenser_name' =>check_injections_filter($this->input->post('dispenser_name')),
						'tracking_number' =>check_injections_filter($this->input->post('tracking_number')),
		
					);
		$this->load->library('NF_Query_search');
		$query_id = $this->nf_query_search->save_query($query_array);					
		redirect('gc/customer_tracking_reports/show_tracking/'.$query_id);		
	}
	function excel_data(){
		$customer_name = check_injections_filter($this->input->post('customer_excel_name'));
		$dispenser_name = check_injections_filter($this->input->post('dispenser_excel_name'));

		$access_branch= $this->session->userdata('access_branch');
		$condition = array();
		$condition[] = array('join',TABLE_CUSTOMER.' as c','c.customer_id = t.possessed_by','left');
		$condition[] = array('join',TABLE_PRODUCT.' as p','p.product_id = t.product_id','left');
		$condition[] = array('join',TABLE_COMPANY_BRANCH.' as b','b.company_branch_id = c.company_branch_id','left');
		$condition[] = array('join',TABLE_TRACKING_DETAILS.' as dt','dt.tracking_numbers_id = t.tracking_numbers_id','left');
		$condition[] = array('join',TABLE_DISPENSER_SALES.' as d','d.dispenser_sales_id = dt.dispenser_sales_id','left');
        $condition[] = array('where','t.is_status','Customer');

       	if ($access_branch!='') {
       		$acb =  explode(',',$access_branch);
            $condition[] = array('where_in','b.company_branch_id',$acb);
        }
        if ($customer_name!='') {
        	$condition[] = array('like','c.customer_name',$customer_name);
        	$this->page_data['customer_name_hidden']=$customer_name;
        }else{
        	$this->page_data['customer_name_hidden']='';
        }
        if ($dispenser_name!='') {
        	$condition[] = array('like','p.product_name',$dispenser_name);
        	$this->page_data['dispenser_name_hidden']=$dispenser_name;
        }else{
        	$this->page_data['dispenser_name_hidden']=$dispenser_name;
        }
        $selected_array_data = array(
                            'table_name'=>TABLE_TRACKING_NUMBERS.' as t',
                            'table_fields'=>'t.tracking_number,c.customer_name, c.customer_id, t.is_status, p.product_name, p.product_type, b.company_branch_name,dt.dispenser_sales_id,d.installation_date,d.dispenser_sales_id',
                            'condition'=>$condition,
                            'limit'=>'',
                            'order_by_field'=>'t.tracking_number',
                            'order'=>'ASC',
                            'group_by_field'=>'t.tracking_number',
                            'offset'=>''
                             );
		$excel_data['excel_data'] = $this->master_model->select_data($selected_array_data);
		if (!empty($excel_data['excel_data'])) {
		  $this->page_data['result_customer_report']=$excel_data['excel_data'];
		}else{
			$this->page_data['result_customer_report']='';
		}
		$this->load->view('gc/customer_tracking_reports_list_excel.php',$this->page_data);

	}
	
	function fetch_branch_name(){
      admin_auth();
      $access_branch= $this->session->userdata('access_branch');     
       if ($access_branch!='') {
       		$acb =  explode(',',$access_branch);
          $condition[] = array('where_in','company_branch_id',$acb);
       }
       $condition[] = array('where','is_active','1');
       $selected_array = array( 
                         'table_name' =>TABLE_COMPANY_BRANCH,
                         'table_fields' => 'company_branch_id,company_branch_name,is_active',
                         'condition'=>$condition,
                        'order_by_field'=>'company_branch_name',
						'order'=>'ASC', 
                         'limit'=>'',
                         'offset'=>''
		);
		
		return $this->master_model->select_data($selected_array);
		//dump($this->db->last_query());
	
    }
	
	function customer_refill_report(){

 		$this->_page_data['lable_sales_report_branch']=$this->lang->line('lable_sales_report_branch', FALSE);
 		$this->_page_data['lable_sales_report_date_from']=$this->lang->line('lable_sales_report_date_from', FALSE);
 		$this->_page_data['lable_sales_report_date_to']=$this->lang->line('lable_sales_report_date_to', FALSE);
 		$this->_page_data['lable_sales_report_sales_by']=$this->lang->line('lable_sales_report_sales_by', FALSE);
 		$this->_page_data['lable_sales_report_dispenser_name']=$this->lang->line('lable_sales_report_dispenser_name', FALSE);

		$this->_page_data['page_title']="Customer Refill Report";
		$this->_page_data['branch']=$this->fetch_branch_name();
		$this->_page_data['bredcrum']=array(
			                            'Reports'=>'#',
			                            'Customer Refill Report'=>'#',
										//'Manage Customer Master'=>'#',
									
										);
		$this->load->view('gc/customer_refill_data_report.php',$this->_page_data);
	}
	
	function get_dispenser()
	{
        
		$user_id=$this->input->post('user_id');       
	    $cond = array();
        //$cond[] = array('join',TABLE_PRODUCT.' as b','b.product_id = a.product_id','inner');

		$cond[] = array('where','b.is_active','1');
		//$cond[] = array('where','b.product_type','dispenser');
		$cond[] = array('where_in','.b.product_type',array('Dispenser','Diffuser','Oil'));
		if($user_id>0)
		{
			//$cond[] = array('where','a.sales_person_id',$user_id);
		}
		$select_array = array(
								'table_name'=>TABLE_PRODUCT.' as b',
								'table_fields'=>'b.product_name,b.product_id',
								'condition'=>$cond,
								'sort_by'=>'b.product_name',
								'order_by_field'=>'b.product_name',
								'group_by_field'=>'b.product_name',
								'order'=>'ASC',
								'limit'=>'',
								'offset'=>''
								);
		$salse_person_data = $this->master_model->select_data($select_array);
		//dump($this->db->last_query());
		$output='<option value="">Select Dispenser Name</option>';
		
		foreach($salse_person_data as $row)
		{
		  $output .= '<option value='.$row['product_id'].'>'.$row['product_name'].'</option>';

		}
          echo $output;	
    }
	
	function search_customer_refill_report()
	{


      
		$query_array = array(
						'branch_id' =>check_injections_filter($this->input->post('branch_id')),
						'from' => check_injections_filter($this->input->post('from')),
						'to' => check_injections_filter($this->input->post('to')),
						'sale' => check_injections_filter($this->input->post('sale')),
						'product_name_dispenser' => check_injections_filter($this->input->post('product_name_dispenser')),
						'customer_id' => check_injections_filter($this->input->post('customer_id')),
						'sales_type' => check_injections_filter($this->input->post('sales_type')),
						'refill_id' => check_injections_filter($this->input->post('refill_id')),
						
					);
		$this->load->library('NF_Query_search');
		$query_id = $this->nf_query_search->save_query($query_array);			
		
		redirect('gc/customer_tracking_reports/customer_refill_data_report_list/0/'.$query_id);		
	}
	
	
	function customer_refill_data_report_list($get_customer_id='0',$query_id = 0, $sort_by = 'dispenser_sales_id', $sort_order = 'asc', $offset = 0, $sort_type='')
	{

		error_reporting(0);
		// echo "query=".$query_id."<br>";
		// echo "sort_by=".$sort_by."<br>";
		// echo "sort_order=".$sort_order."<br>";
		// echo "offset=".$offset."<br>";
		// echo "sort_type=".$sort_type."<br>";
		// echo "get_customer_id=".$get_customer_id."<br>";
		admin_auth();
		$limit = LIMIT_PER_PAGE;
		//error_reporting(E_ALL);
		$this->_page_data['get_customer_id']=$get_customer_id;
		$this->_page_data['page_title']="List of Customer Refill Report";
		$this->_page_data['bredcrum']=array(
											'Reports'=>'#',
											'Customer Refill Report'=>'#',
											'List of Customer Refill Report'=>'#',
										
											);	

			$this->load->library('NF_Query_search');
			$this->nf_query_search->load_query($query_id);

			$query_array = array(
					'branch_id'=>$this->input->get('branch_id'),
					'from'=>$this->input->get('from'),
					'to'=>$this->input->get('to'),
					'sale'=>$this->input->get('sale'),
					'product_name_dispenser'=>$this->input->get('product_name_dispenser'),
					'customer_id'=>$this->input->get('customer_id'),
					'sales_type'=>$this->input->get('sales_type'),
					'refill_id'=>$this->input->get('refill_id'),

			);
			$branch_id=check_injections_filter($query_array['branch_id']);
			$from=check_injections_filter($query_array['from']);
			$to=check_injections_filter($query_array['to']);
			$sale=check_injections_filter($query_array['sale']);
			$sales_type=check_injections_filter($query_array['sales_type']);
			$refill_id=check_injections_filter($query_array['refill_id']);
			$product_name_dispenser=check_injections_filter($query_array['product_name_dispenser']);
		
		
		if($get_customer_id=='0')
		{
			//$customer_id=$this->input->post('customer_id');
			$customer_id=check_injections_filter($query_array['customer_id']);
		}
		else
		{
				
			$customer_id=$this->encrypt->decode($get_customer_id);
			$customer_id=encrypt_decrypt_parameters($get_customer_id,'decrypt');
		}
		
		$user_type= $this->session->userdata('user_type');
		$access_branch= $this->session->userdata('access_branch');
		$user_type_id= $this->session->userdata('user_type_id');
		$acb =  explode( ',',$access_branch);

		$this->_page_data['excel_branch_id']=$branch_id;
		$this->_page_data['excel_from']=$from;
		$this->_page_data['excel_to']=$to;
		$this->_page_data['excel_sale']=$sale;
		$this->_page_data['excel_product_name_dispenser']=$product_name_dispenser;
		$this->_page_data['excel_customer_id']=$customer_id;
		$this->_page_data['excel_refill_id']=$refill_id;
		$this->_page_data['excel_sales_type']=$sales_type;

		

		$this->_page_data['company_branch_name']=''; 
		$this->_page_data['from']='';  
		$this->_page_data['to']=''; 
		$this->_page_data['get_sale_person_firstname']=''; 
		$this->_page_data['get_sale_person_lastname']=''; 
		$this->_page_data['get_dispenser']='';
		$this->_page_data['customer_id']='';
		$this->_page_data['sales_type']='';
		$this->_page_data['refill_id']='';

		$access_branch= $this->session->userdata('access_branch');
		$acb =  explode( ',',$access_branch);
		
		
		if ($sales_type!="") {  
			$condition[] = array('where','d.sales_type',$sales_type);
			$this->_page_data['sales_type']=$sales_type; 
		}else{
				$this->_page_data['sales_type']=''; 
		}

		if ($branch_id>"0") {  
			$company_branch_names=get_data_value(TABLE_COMPANY_BRANCH,'company_branch_name','company_branch_id',$branch_id,'where');
			$this->_page_data['company_branch_names']=$company_branch_names;   	
			$condition[] = array('where','d.company_branch_id',$branch_id);
		}else{
				$this->_page_data['company_branch_names']=''; 
		}
		if ($from>"0" and $to<"0") { 
				$this->_page_data['from']=$from;       	
				//$condition[] = array('where','r.refill_date',$from);
				$condition[] = array('where','d.installation_date',date('Y-m-d', strtotime($from)));
		}else{
		$this->_page_data['from']='';  	
		}
		
		if ($to>"0" and $from<"0") {
				$this->_page_data['to']=$to;  	
				//$condition[] = array('where','r.next_refill_date',$to);
				$condition[] = array('where','d.installation_date',date('Y-m-d', strtotime($to)));
		}else{
			$this->_page_data['to']='';  	
		}
		
		if($from>"0" and $to>"0"){
			$this->_page_data['to']=$to; 
			$this->_page_data['from']=$from;  
			$query_part='d.installation_date BETWEEN "'.date('Y-m-d', strtotime($from)).'" AND "'.date('Y-m-d', strtotime($to)).'"';
				//$condition[] = array('where','r.refill_date',$from);
				$condition[] = array('where',$query_part);
		}else{
			$this->_page_data['from']='';  
		   $this->_page_data['to']=''; 
		
		}
		if ($sale>"0") {
				$get_first_name=get_data_value(TABLE_USERS,'first_name','user_id',$sale,'where');
				$get_last_name=get_data_value(TABLE_USERS,'last_name','user_id',$sale,'where');
				$this->_page_data['get_sale_person_firstname']=$get_first_name; 
				$this->_page_data['get_sale_person_lastname']=$get_last_name; 
				$condition[] = array('where','u.user_id',$sale);
		}else{
			$this->_page_data['get_sale_person_firstname']=''; 
		$this->_page_data['get_sale_person_lastname']=''; 
		}
		if ($product_name_dispenser>"0") {
				$get_dispenser=get_data_value(TABLE_PRODUCT,'product_name','product_id',$product_name_dispenser,'where');
				$this->_page_data['get_dispenser']=$get_dispenser; 
				$condition[] = array('where','p.product_id',$product_name_dispenser);
		}else{
			$this->_page_data['get_dispenser']='';
		}
		
		
		if ($refill_id!="" && $refill_id!=0) {
				$get_refill=get_data_value(TABLE_PRODUCT,'product_name','product_id',$refill_id,'where');				
				$this->_page_data['get_refill']=$get_refill; 
				$condition[] = array('where','r.product_id',$refill_id);
		}else{
			$this->_page_data['get_refill']='';
		}
		
		
		
		if ($customer_id>"0") {
				$get_customer=get_data_value(TABLE_CUSTOMER,'customer_name','customer_id',$customer_id,'where');
				$this->_page_data['customer_id']=$get_customer; 
				$condition[] = array('where','d.customer_id',$customer_id);
		}else{	
		$this->_page_data['customer_id']='';
		}

		

		 $this->_page_data['fields'] = array(
								'customer_name' => 'Customer Name',
								'first_name' => 'Sales Person Name',
								'product_name' => 'Product Name',
								'refill_name' => 'Refill Name',
								'installation_date' => 'Installation Date',
								'sales_type' => 'Sales Type',
								'stock_quantity' => 'Quantity',
								'refill_location' => 'Location',
								'tracking_number' => 'Tracking Numbers',
							);
			
		$condition[] = array('join',TABLE_CUSTOMER.' as c','c.customer_id = d.customer_id','inner');
		$condition[] = array('join',TABLE_COMPANY_BRANCH.' as b','b.company_branch_id = c.company_branch_id','left');
		$condition[] = array('join',TABLE_PRODUCT.' as p','p.product_id = d.product_id','left');
		$condition[] = array('join',TABLE_USERS.' as u','u.user_id = d.sales_person_id','left');
		$condition[] = array('join',TABLE_DISPENSER_REFILL_DETAILS.' as r','r.dispenser_sales_id = d.dispenser_sales_id','INNER');
		$condition[] = array('join',TABLE_PRODUCT.' as pa','pa.product_id = r.product_id','left');
		$condition[] = array('where','r.next_has_been_refilled','no');
		$condition[] = array('where','r.stop_refill','no');
		$condition[] = array('where','r.is_active','1');
		$condition[] = array('where','d.is_active','1');
		$condition[] = array('where','d.dispenser_quantity >','0');
		
			
		$sort_columns = array('customer_name','product_name','first_name','last_name','city_name','product_type','refill_location','stock_quantity','installation_date','comments');
		
			$search_array=array(
							'limit' => $limit,
							'offset' => $offset,
							'sort_by' => $sort_by,
							'sort_type' => $sort_type,
							'sort_order' => $sort_order,
							'sort_columns'=> $sort_columns,
							'table_fields'=>'d.dispenser_sales_id,c.customer_name,c.customer_id, u.first_name, u.last_name, b.company_branch_name, p.product_name, p.product_type, d.sales_type,d.dispenser_quantity as stock_quantity, d.comments, r.refill_location,d.price AS total,d.dispenser_sales_id,d.installation_date,r.dispenser_refill_details_id,p.product_id,r.quantity as refil_qty,pa.product_name as refill_name,r.tracking_numbers,r.unit_of_measurment',
							//'group_by_field'=>'d.dispenser_sales_id',
							'table_name'=>TABLE_DISPENSER_SALES.' as d',
							'cond'=> $condition,
							);     	
        	$results = $this->master_model->search($search_array);
			
			//dump($this->db->last_query());
		
			$selected_array=array(
                            'table_name'=>TABLE_DISPENSER_SALES.' as d',
                            'table_fields'=>'d.dispenser_sales_id',
                            'condition'=>$condition,
                            'limit'=>'',
                            'order_by_field'=>'',
                            'order'=>'DESC',
                           // 'group_by_field'=>'d.dispenser_sales_id',
                            'offset'=>''
                              );
			$sale_data = $this->master_model->select_data($selected_array);
		  
			$cnt_all_rows=count($sale_data);
			
			
			if(!empty($results)&& is_array($results))
			{
				$this->_page_data['sale_report'] = $results['rows'];
				$this->_page_data['num_results'] = $cnt_all_rows;
			}
			else
			{
				$results='';
			}
			$this->_page_data['offset'] = $offset;
			/// pagination
			$this->load->library('pagination');
			$config = array();
			

			$config['base_url'] = site_url("gc/customer_tracking_reports/customer_refill_data_report_list/$get_customer_id/$query_id/$sort_by/$sort_order/");
			
			$config['total_rows'] = $this->_page_data['num_results'];
			$config['per_page'] = $limit;
			$config['uri_segment'] = 8;
			$config['full_tag_open'] = '<span class=\'paging\'>';
			$config['full_tag_close'] = '</span>';
			$config['first_link'] = 'First';
			$config['prev_link'] = '<<';
			$config['next_link'] = '>>';
			$config['last_link'] = 'Last';
			$this->pagination->initialize($config);
			
			$this->_page_data['pagination'] = $this->pagination->create_links();
			$this->_page_data['sort_by'] = $sort_by;
			$this->_page_data['sort_order'] = $sort_order;
			$this->_page_data['sort_type'] = $sort_type;
			$this->_page_data['get_customer_id'] = $get_customer_id;

			
			//$data['purpose'] = $purpose;
			$this->_page_data['query_id'] = $query_id;
			$this->load->view('gc/customer_refill_data_report_list.php',$this->_page_data);

		
	}
	
	function get_refill_data()
	{
		$dispenser_id=$this->input->post('dispenser_id');
		
		$refill_data=get_disp_refill($dispenser_id);
		
		echo $refill_data;
	}
	

	
	function excel_customer_refill_data()
	{
		
		$branch_id=$this->input->post('excel_branch_id');
		$from=$this->input->post('excel_from');
		$to=$this->input->post('excel_to');
		$sale=$this->input->post('excel_sale');
		$product_name_dispenser=$this->input->post('excel_product_name_dispenser');
		$customer_id=$this->input->post('excel_customer_id');
		$refill_id=$this->input->post('excel_refill_id');
		$sales_type=$this->input->post('excel_sales_type');

		  
		if ($branch_id>"0") {  
			$company_branch_name=get_data_value(TABLE_COMPANY_BRANCH,'company_branch_name','company_branch_id',$branch_id,'where');
			$this->_page_data['company_branch_name']=$company_branch_name;   	
			$condition[] = array('where','d.company_branch_id',$branch_id);
		}else{
				$this->_page_data['company_branch_name']=''; 
		}
		if ($from>"0" and $to<"0") { 
				$this->_page_data['from']=$from;       	
				//$condition[] = array('where','r.refill_date',$from);
				$condition[] = array('where','d.installation_date',date('Y-m-d', strtotime($from)));
		}else{
		$this->_page_data['from']='';  	
		}
		
		if ($to>"0" and $from<"0") {
				$this->_page_data['to']=$to;  	
				//$condition[] = array('where','r.next_refill_date',$to);
				$condition[] = array('where','d.installation_date',date('Y-m-d', strtotime($to)));
		}else{
			$this->_page_data['to']='';  	
		}
		
		if($from>"0" and $to>"0"){
			$this->_page_data['to']=$to; 
			$this->_page_data['from']=$from;  
			$query_part='d.installation_date BETWEEN "'.date('Y-m-d', strtotime($from)).'" AND "'.date('Y-m-d', strtotime($to)).'"';
				//$condition[] = array('where','r.refill_date',$from);
				$condition[] = array('where',$query_part);
		}else{
			$this->_page_data['from']='';  
		   $this->_page_data['to']=''; 
		
		}
		if ($sale>"0") {
				$get_first_name=get_data_value(TABLE_USERS,'first_name','user_id',$sale,'where');
				$get_last_name=get_data_value(TABLE_USERS,'last_name','user_id',$sale,'where');
				$this->_page_data['get_sale_person_firstname']=$get_first_name; 
				$this->_page_data['get_sale_person_lastname']=$get_last_name; 
				$condition[] = array('where','u.user_id',$sale);
		}else{
			$this->_page_data['get_sale_person_firstname']=''; 
		   $this->_page_data['get_sale_person_lastname']=''; 
		}
		if ($product_name_dispenser>"0") {
				$get_dispenser=get_data_value(TABLE_PRODUCT,'product_name','product_id',$product_name_dispenser,'where');
				$this->_page_data['get_dispenser']=$get_dispenser; 
				$condition[] = array('where','d.product_id',$product_name_dispenser);
		}else{
			$this->_page_data['get_dispenser']='';
		}
		
		if ($refill_id!="" && $refill_id!=0) {
				$get_refill=get_data_value(TABLE_PRODUCT,'product_name','product_id',$refill_id,'where');				
				$this->_page_data['get_refill']=$get_refill; 
				$condition[] = array('where','r.product_id',$refill_id);
		}else{
			$this->_page_data['get_refill']='';
		}
		
		if ($customer_id>"0") {
				$get_customer=get_data_value(TABLE_CUSTOMER,'customer_name','customer_id',$customer_id,'where');
				$this->_page_data['customer_id']=$get_customer; 
				$condition[] = array('where','d.customer_id',$customer_id);
		}else{	
		$this->_page_data['customer_id']='';
		}
		
		if ($sales_type!="") {  
			$condition[] = array('where','d.sales_type',$sales_type);
			$this->_page_data['sales_type']=$sales_type; 
		}else{
				$this->_page_data['sales_type']=''; 
		}

		$condition[] = array('join',TABLE_CUSTOMER.' as c','c.customer_id = d.customer_id','inner');
		$condition[] = array('join',TABLE_COMPANY_BRANCH.' as b','b.company_branch_id = c.company_branch_id','left');
		$condition[] = array('join',TABLE_PRODUCT.' as p','p.product_id = d.product_id','left');
		$condition[] = array('join',TABLE_USERS.' as u','u.user_id = d.sales_person_id','left');
		$condition[] = array('join',TABLE_DISPENSER_REFILL_DETAILS.' as r','r.dispenser_sales_id = d.dispenser_sales_id','INNER');
		$condition[] = array('join',TABLE_PRODUCT.' as pa','pa.product_id = r.product_id','left');
		$condition[] = array('where','r.next_has_been_refilled','no');
		$condition[] = array('where','r.stop_refill','no');
		$condition[] = array('where','d.dispenser_quantity >','0');
		$condition[] = array('where','r.is_active','1');
		$condition[] = array('where','d.is_active','1');
		
		$selected_array=array(
								'table_name'=>TABLE_DISPENSER_SALES.' as d',
								//'table_fields'=>'c.customer_name, ca.address_line1, ca.address_line2, cy.city_name, u.first_name, u.last_name, b.company_branch_name,d.installation_date, p.product_name, p.product_type, d.sales_type,d.dispenser_quantity as stock_quantity, d.comments, r.refill_location,d.price AS total',
								'table_fields'=>'d.dispenser_sales_id,c.customer_name,c.customer_id, u.first_name, u.last_name, b.company_branch_name, p.product_name, p.product_type, d.sales_type,d.dispenser_quantity as stock_quantity, d.comments, r.refill_location,d.price AS total,d.dispenser_sales_id,d.installation_date,r.dispenser_refill_details_id,p.product_id,r.quantity as refil_qty,pa.product_name as refill_name,r.tracking_numbers,r.unit_of_measurment',
								'condition'=>$condition,
								'limit'=>'',
								'order_by_field'=>'c.customer_name',
								//'group_by_field'=>'d.dispenser_sales_id',
								'offset'=>''
							 );
		 $sale_report['sale_report'] = $this->master_model->select_data($selected_array);
		// dump($this->db->last_query());
		 //exit;
		 if(!empty($sale_report['sale_report'] )&& is_array($sale_report['sale_report']))
			{
				$this->_page_data['sale_report'] = $sale_report['sale_report'];		 	
			}
			   $this->load->view('gc/customer_refill_data_report_list_excel.php',$this->_page_data);
		
	}
}
?>