<?php
defined('BASEPATH') OR exit('No direct script access allowed');
error_reporting(0);
class Excel_export extends CI_Controller {
	
    
    public function __construct(){

        parent::__construct();
  			$this->load->helper('url');
  	 		$this->load->model('user_model');
        $this->load->library('session');
    $this->load->model('department_model');
    $this->load->helper(array('form', 'url'));

		$this->load->model("excel_export_model");
		$this->load->library("excel");
}
	function index()
	{
           $this->load->model('department_model');
		$this->load->model("excel_export_model");
		$data["employee_data"] = $this->excel_export_model->fetch_data();
		$this->load->view("excel_export_view", $data);
	}
    
    
    public function dashboard(){
       $tab=$this->input->post('tab');
          $fdate=$this->input->post('from');
         $tdate=$this->input->post('to');
         $from= $date= date("Y-m-d",strtotime($fdate));
        $to= $date= date("Y-m-d",strtotime($tdate));
     
        $object = new PHPExcel();

		$object->setActiveSheetIndex(0);

		$table_columns = array("DATE", "TYPE","DEBIT", "CRADIT","BALANCE" );

		$column = 0;

		foreach($table_columns as $field)
		{
			$object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
			$column++;
		}

		$employee_data = $this->excel_export_model->fetch_data();

    
 $b_type= $this->department_model->get_buisness_type();
    
		$excel_row = 2;
        if($from and to ){
             $query=$this->db->query("select * from sale_purchases where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_id='$b_type'  and bank_id='$tab' order by CONVERT(DATE, date) ASC 
                           "  );
        }
        else{
            $query=$this->db->query("select * from sale_purchases where status=1 and buisness_id='$b_type' and bank_id='$tab'
  ORDER BY CONVERT(DATE, date) ASC "); 
            
        }
       


$bank[1]=0.0;
        $bank[2]=0.0;
         $bank[3]=0.0;
        $bank[4]=0.0;
         $bank[5]=0.0;
        $bank[6]=0.0;
  
  $total_balance=0.0;
        $totalpur=0.0;
        $mainbalance=0.0;
        $balance=0.0;
      foreach ($query->result() as $key => $bankvalue){
         $totalamount+=$bankvalue->amount;
          $totalpur+=$bankvalue->pur_amount;
          
          $type= $bankvalue->type;
          $date= date('d F Y',strtotime($bankvalue->date)) ;
            
          $debit=$bankvalue->pur_amount;
          $cradit=$bankvalue->amount;
          $total_balance+=$bankvalue->amount;
            $totalpur+=$bankvalue->pur_amount;
            $mainbalance=($total_balance-$totalpur);
          
          
			$object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $date);
			$object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $type);
			$object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $debit);
			$object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $cradit);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $mainbalance);
			$excel_row++;
     
 
  }
        $balance=($total_balance-$totalpur);
        $object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, Total);
        $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $totalpur);
        $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $totalamount);
         $object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $balance);
        //$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $mainbalance);
		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Employee Data.xls"');
		$object_writer->save('php://output');
	
    }

	function action()
	{
        $query = $this->db->query("SELECT * FROM bank_name where status=1 and record=1  ");
 $row= $query->num_rows();
        $tab=$this->input->post('tab');
        //$from=$this->input->post('from');
        //$to=$this->input->post('to');
           $from=$this->input->post('from');
        $to=$this->input->post('to');
    
         $b_type= $this->department_model->get_buisness_type();
        $sale_type= $this->department_model->get_tab_type($tab);
       
       
        $bank[1]=0.0;
        $bank[2]=0.0;
         $bank[3]=0.0;
        $bank[4]=0.0;
         $bank[5]=0.0;
        $bank[6]=0.0; 
	
		$object = new PHPExcel();

		$object->setActiveSheetIndex(0);
  $bank=$this->db->query("select * from bank_name where status=1 
   and buisness_id='$b_type' ");  
$result=$bank->result();
        
 
        $i=0;
                 
        $column = 0;
        $table_columns='';
        
         $a=array("DATE");
     
       $c=array("Date","TYPE");
foreach ($result as $key => $bankvalue){
     
      
      
    $c[]=($result[$key]->type);
    
      
          
          
      
    
    
}
array_push($c,"Total");
//$b=array($c,"red","green",$c);


      // $table_columns2 = array("DATE", "TYPE","DEBIT", "CRADIT","BALANCE" );
       // $table_columns2=("DATE","TYPE","CASH","CASH BANK","S Generale","LCL Bank","Ticket Resto","C depargne", "TOTAL");
		 
         $table_columns = $c;
		

		$column = 0;

		foreach($table_columns as $field)
		{
			$object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
			$column++;
		}

		$employee_data = $this->excel_export_model->fetch_data();

    

    
		$excel_row = 2;
        if($sale_type and !$from and !$to){
         $query=$this->db->query("select * from sale where status=1 and buisness_type='$b_type' and type='$tab' order by CONVERT(DATE, date) desc ");    
       }
        else if (!$sale_type and !$from and !$to){
            $query=$this->db->query("select * from sale where status=1 and buisness_type='$b_type'
             order by CONVERT(DATE, date) desc ");  
        }
        else if ($sale_type and $from and $to){
             $query=$this->db->query("select * from sale where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type' and type='$tab' order by CONVERT(DATE, date) desc 
                           "  );
        }
         else if (!$sale_type and $from and $to){
             $query=$this->db->query("select * from sale where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type'  order by CONVERT(DATE, date) desc 
                           "  );
        }
      

 
//$data=$query->result();
        $i=2;
              
$sumtotal=0.0;
      foreach ($query->result() as $key => $value){
         $sale_type= $this->department_model->get_tab_type($value->type);
          $bank=unserialize($value->bank_info);
          $type= $value->type;
          $date= date('d F Y',strtotime($value->date)) ;
          $cash_sale= $value->cash_sale;
         
       
          $total= $value->total;
     $sumtotal+=$value->total;
		  
			$object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $date);
          
		$object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $sale_type); 
             $k=2;
           foreach ($result as $key => $bankv){
              
                           
                            
               for ($s = 0; $s <=$row; ++$s) 
                            
                                       { 
                                           
                                        if($bankv->id==$s){
      
                                       
                                            $sum[$s]+=($bank[$s]);
                                       }
                                         
        
                                    }
                                
                  $object->getActiveSheet()->setCellValueByColumnAndRow($k, $excel_row, $bank[$bankv->id]);
                   
             
                                        
                                     
                                         
                                    $k++;
                                          
        
                                    }
           
                           
        
                                   
			/*$object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $bank[1]);
         $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $bank[2]);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $bank[3]);
          $object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $bank[4]);
          $object->getActiveSheet()->setCellValueByColumnAndRow(6, $excel_row, $bank[5]);
          $object->getActiveSheet()->setCellValueByColumnAndRow(7, $excel_row, $bank[6]);*/
			
          	
          $object->getActiveSheet()->setCellValueByColumnAndRow($k, $excel_row, $total);
			$excel_row++;
      }
        $f=2;
        $object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, Total);
       foreach ($result as $key => $bankv){
            $object->getActiveSheet()->setCellValueByColumnAndRow($f, $excel_row, $sum[$bankv->id]);
           $f++;
       }
        $object->getActiveSheet()->setCellValueByColumnAndRow($f, $excel_row, $sumtotal);
		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Employee Data.xls"');
		$object_writer->save('php://output');
	}

	function purchases()
	{
         $b_type= $this->department_model->get_buisness_type();
        $tab=$this->input->post('tab');
        $from=$this->input->post('from');
        $to=$this->input->post('to');
         $pur_type= $this->department_model->get_purtab_type($tab);
      $paytotal=0.0;
        $totalorder=0.0;
         $due_balance=0.0;
         
		$object = new PHPExcel();

		$object->setActiveSheetIndex(0);

		$table_columns = array("DATE", "CATEGORY", "PAY TYPE", "PAY TOTAL", "TOTAL ORDER","DUE BALANCE");

		$column = 0;

		foreach($table_columns as $field)
		{
			$object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
			$column++;
		}

		$employee_data = $this->excel_export_model->fetch_data();

    
 $b_type= $this->department_model->get_buisness_type();
    
		$excel_row = 2;
       
        
if($pur_type and !$from and !$to){
         $query=$this->db->query("select * from purchases where status=1 and buisness_type='$b_type' and type='$tab'
     order by CONVERT(DATE, date) desc ");    
       }
        else if (!$pur_type and !$from and !$to){
            $query=$this->db->query("select * from purchases where status=1 and buisness_type='$b_type'
    order by CONVERT(DATE, date) desc ");  
        }
        else if ($pur_type and $from and $to){
             $query=$this->db->query("select * from purchases where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type' and type='$tab' order by CONVERT(DATE, date) desc 
                           "  );
        }
         else if (!$pur_type and $from and $to){
             $query=$this->db->query("select * from purchases where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type'  order by CONVERT(DATE, date) desc 
                           "  );
        }
 
//$data=$query->result();
      foreach ($query->result() as $key => $value){
          $type= $value->type;
          $pur_type= $this->department_model->get_purtab_type($type);
          $date= date('d F Y',strtotime($value->date)) ;
          $cash_sale= $value->pay_type;
          $bank_card= $value->pay_total;
          $ticket= $value->total_order;
          $total= $value->due_balance;
     $paytotal+=$value->pay_total;
		$totalorder+=$value->total_order;
          $due_balance+=$value->due_balance;
			$object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $date);
			$object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $pur_type);
			$object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $cash_sale);
          $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $bank_card);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $ticket);
			$object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $total);
			$excel_row++;
      }
            $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, Total);
            $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $paytotal);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $totalorder);
			$object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $due_balance);

		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Employee Data.xls"');
		$object_writer->save('php://output');
	}
    function wholesale()
	{
         $b_type= $this->department_model->get_buisness_type();
        $tab=$this->input->post('tab');
        $from=$this->input->post('from');
        $to=$this->input->post('to');
         $whole_type= $this->department_model->get_wholetab_type($tab);
        
		$bank[1]=0.0;
        $bank[2]=0.0;
         $bank[3]=0.0;
        $bank[4]=0.0;
         $bank[5]=0.0;
        $bank[6]=0.0;
		$object = new PHPExcel();

		$object->setActiveSheetIndex(0);

		$table_columns = array("DATE", "CATEGORY", "PAY TYPE", "PAY TOTAL", "TOTAL ORDER","DUE BALANCE");

		$column = 0;

		foreach($table_columns as $field)
		{
			$object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
			$column++;
		}

		$employee_data = $this->excel_export_model->fetch_data();
  $b_type='';
         $bus_type=$this->db->query("select * from buisness_type  
  ");
  $data='';
 $row = $bus_type->row();
    $b_type= $this->department_model->get_buisness_type();
		$excel_row = 2;
        if($whole_type and !$from and !$to){
            $query=$this->db->query("select * from wholesale where status=1 and buisness_type='$b_type' and type='$tab'  order by CONVERT(DATE, date) desc ");   
        }
        else if(!$whole_type and !$from and !$to){
          $query=$this->db->query("select * from wholesale where status=1 and buisness_type='$b_type'
    order by CONVERT(DATE, date) desc ");   
        }
         else if ($whole_type and $from and $to){
             $query=$this->db->query("select * from wholesale where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type' and type='$tab' order by CONVERT(DATE, date) desc 
                           "  );
        }
         else if (!$whole_type and $from and $to){
             $query=$this->db->query("select * from wholesale where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type'  order by CONVERT(DATE, date) desc 
                           "  );
        }

            $paytotal=0.0;
        $totalorder=0.0;
        $due_balance=0.0;
//$data=$query->result();
      foreach ($query->result() as $key => $value){
          $type= $value->type;
          $whole_type= $this->department_model->get_wholetab_type( $type);
          $date= date('d F Y',strtotime($value->date)) ;
          $cash_sale= $value->pay_type;
          $bank_card= $value->pay_total;
          $ticket= $value->total_order;
          $total= $value->due_balance;
         $paytotal+=$value->pay_total;
		$totalorder+=$value->total_order;
          $due_balance+=$value->due_balance;
			$object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $date);
			$object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $whole_type);
			$object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $cash_sale);
          $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $bank_card);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $ticket);
			$object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $total);
			$excel_row++;
      }
$object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, Total);
            $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $paytotal);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $totalorder);
			$object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $due_balance);
		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Employee Data.xls"');
		$object_writer->save('php://output');
	}

  function loan()
	{
         $b_type= $this->department_model->get_buisness_type();
        $tab=$this->input->post('tab');
        $from=$this->input->post('from');
        $to=$this->input->post('to');
         $whole_type= $this->department_model->get_loan_option_name($tab);
        
		$bank[1]=0.0;
        $bank[2]=0.0;
         $bank[3]=0.0;
        $bank[4]=0.0;
         $bank[5]=0.0;
        $bank[6]=0.0;
		$object = new PHPExcel();

		$object->setActiveSheetIndex(0);

		$table_columns = array("DATE", "CATEGORY", "PAY TYPE", "PAY TOTAL", "TOTAL Loan","DUE BALANCE");

		$column = 0;

		foreach($table_columns as $field)
		{
			$object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
			$column++;
		}

		$employee_data = $this->excel_export_model->fetch_data();
  $b_type='';
         $bus_type=$this->db->query("select * from buisness_type  
  ");
  $data='';
     $row = $bus_type->row();
    $b_type= $this->department_model->get_buisness_type();
		$excel_row = 2;
        if($whole_type and !$from and !$to){
            $query=$this->db->query("select * from loan where status=1 and buisness_type='$b_type' and type='$tab'  order by CONVERT(DATE, date) desc ");   
        }
        else if(!$whole_type and !$from and !$to){
          $query=$this->db->query("select * from loan where status=1 and buisness_type='$b_type'
    order by CONVERT(DATE, date) desc ");   
        }
         else if ($whole_type and $from and $to){
             $query=$this->db->query("select * from loan where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type' and type='$tab' order by CONVERT(DATE, date) desc 
                           "  );
        }
         else if (!$whole_type and $from and $to){
             $query=$this->db->query("select * from loan where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type'  order by CONVERT(DATE, date) desc 
                           "  );
        }

            $paytotal=0.0;
        $totalorder=0.0;
        $due_balance=0.0;
//$data=$query->result();
      foreach ($query->result() as $key => $value){
          $type= $value->type;
          $date= date('d F Y',strtotime($value->date)) ;
          $cash_sale= $value->pay_type;
          $bank_card= $value->pay_total;
          $ticket= $value->total_loan;
          $total= $value->due_balance;
         $paytotal+=$value->pay_total;
		$totalorder+=$value->total_loan;
          $due_balance+=$value->due_balance;
           $whole_type= $this->department_model->get_loan_option_name($value->type);
			$object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $date);
			$object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $whole_type);
			$object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $cash_sale);
          $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $bank_card);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $ticket);
			$object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $total);
			$excel_row++;
      }
            $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, Total);
            $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $paytotal);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $totalorder);
			$object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $due_balance);
		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Employee Data.xls"');
		$object_writer->save('php://output');
	}
	function worker()
	{
        $cashbank=$this->department_model->cash_bank();
         $b_type= $this->department_model->get_buisness_type();
        $tab=$this->input->post('tab');
        $from=$this->input->post('from');
        $to=$this->input->post('to');
         $worker_type= $this->department_model->get_workertab_type($tab);
		$bank[1]=0.0;
        $bank[2]=0.0;
         $bank[3]=0.0;
        $bank[4]=0.0;
         $bank[5]=0.0;
        $bank[6]=0.0;
		$object = new PHPExcel();

		$object->setActiveSheetIndex(0);

		$table_columns = array("DATE", "MONTH", "NAME", "CASH PAY", "CHEQUE PAY","SALARY");

		$column = 0;

		foreach($table_columns as $field)
		{
			$object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
			$column++;
		}

		$employee_data = $this->excel_export_model->fetch_data();
  
     $b_type= $this->department_model->get_buisness_type();
		$excel_row = 2;
        if($worker_type and !$from and !$to){
           $query=$this->db->query("select * from worker where status=1 and buisness_type='$b_type' and name='$tab' order by CONVERT(DATE, date) desc ");  
        }
        else if(!$worker_type and !$from and !$to){
         $query=$this->db->query("select * from worker where status=1 and buisness_type='$b_type' order by CONVERT(DATE, date) desc ");    
        }
        else if ($worker_type and $from and $to){
             $query=$this->db->query("select * from worker where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type' and name='$tab' order by CONVERT(DATE, date) desc 
                           "  );
        }
         else if (!$worker_type and $from and $to){
             $query=$this->db->query("select * from worker where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type'  order by CONVERT(DATE, date) desc 
                           "  );
        }

        

 $bank[1]='';
        $cash=0.0;
        $chq=0.0;
        $sumtotal=0.0;
//$data=$query->result();
      foreach ($query->result() as $key => $value){
          $type= $value->name;
          $month= $value->month;
          $date= date('d F Y',strtotime($value->date)) ;
          $cash_sale= $value->cash_pay;
        $cash+=$bank[$cashbank];
          $worker_type= $this->department_model->get_workertab_type($value->name);
          $salary= $value->salary;
          $bank=unserialize($value->bank_info);
           $checque=$salary-$bank[$cashbank];
          $chq+=$value->salary-$bank[$cashbank];
		   $sumtotal+=$value->salary;
			$object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $date);
			$object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $month);
			$object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $worker_type);
          $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $bank[$cashbank]);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $checque);
			$object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $salary);
			$excel_row++;
      }
         $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, Total);
          $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $cash);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $chq);
        $object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $sumtotal);
		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Employee Data.xls"');
		$object_writer->save('php://output');
	}

	function expense()
	{
         $cashbank=$this->department_model->cash_bank();
		$b_type= $this->department_model->get_buisness_type();
        $tab=$this->input->post('tab');
        $from=$this->input->post('from');
        $to=$this->input->post('to');
         $expense_type= $this->department_model->get_expensetab_type($tab);
		$object = new PHPExcel();

		$object->setActiveSheetIndex(0);

		$table_columns = array("DATE", "TYPE",  "CASH PAY", "CHEQUE PAY","TOTAL");

		$column = 0;

		foreach($table_columns as $field)
		{
			$object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
			$column++;
		}

		$employee_data = $this->excel_export_model->fetch_data();
  
  $b_type= $this->department_model->get_buisness_type();
		$excel_row = 2;
        if($expense_type and !$from and !$to){
            $query=$this->db->query("select * from expense where status=1 and buisness_type='$b_type' and type='$tab' order by CONVERT(DATE, date) desc ");  
 
        }
        else if(!$expense_type and !$from and !$to){
           $query=$this->db->query("select * from expense where status=1 and buisness_type='$b_type' order by CONVERT(DATE, date) desc ");  
  
        }
         else if ($expense_type and $from and $to){
             $query=$this->db->query("select * from expense where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type' and type='$tab' order by CONVERT(DATE, date) desc 
                           "  );
        }
         else if (!$expense_type and $from and $to){
             $query=$this->db->query("select * from expense where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type'  order by CONVERT(DATE, date) desc 
                           "  );
        }
      
       $cash=0.0;
        $chq=0.0;
         $sumtotal=0.0;
        $bank[4]=0.0;
         $bank[5]=0.0;
        $bank[6]=0.0;
//$data=$query->result();
      foreach ($query->result() as $key => $value){
          $type= $value->type;
          
         $expense_type= $this->department_model->get_expensetab_type($value->type);
          $date= date('d F Y',strtotime($value->date)) ;
        
 
          $total= $value->total;
          
   $bank=unserialize($value->bank_info);
		
          $cheque=$total-$bank[$cashbank];
          $cash+=$bank[$cashbank];
          $chq+=$value->total-$bank[$cashbank];
          $sumtotal+=$value->total;
			$object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $date);
			$object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $expense_type);
			$object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $bank[$cashbank]);
        
			$object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $cheque);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $total);
			$excel_row++;
      }
        $object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, Total);
          $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $cash);
			$object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $chq);
            $object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $sumtotal);
		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Employee Data.xls"');
		$object_writer->save('php://output');
	}

    function payloan()
	{
         $cashbank=$this->department_model->cash_bank();
		$b_type= $this->department_model->get_buisness_type();
        $tab=$this->input->post('tab');
        $from=$this->input->post('from');
        $to=$this->input->post('to');
         $expense_type= $this->department_model->get_payloantab_type($tab);
		$object = new PHPExcel();

		$object->setActiveSheetIndex(0);

		$table_columns = array("DATE", "TYPE",  "CASH PAY", "CHEQUE PAY","TOTAL");

		$column = 0;

		foreach($table_columns as $field)
		{
			$object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
			$column++;
		}

		$employee_data = $this->excel_export_model->fetch_data();
  
         $b_type= $this->department_model->get_buisness_type();
		$excel_row = 2;
        if($expense_type and !$from and !$to){
            $query=$this->db->query("select * from pay_loan where status=1 and buisness_type='$b_type' and type='$tab' order by CONVERT(DATE, date) desc ");  
 
        }
        else if(!$expense_type and !$from and !$to){
           $query=$this->db->query("select * from pay_loan where status=1 and buisness_type='$b_type' order by CONVERT(DATE, date) desc ");  
  
        }
         else if ($expense_type and $from and $to){
             $query=$this->db->query("select * from pay_loan where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type' and type='$tab' order by CONVERT(DATE, date) desc 
                           "  );
        }
         else if (!$expense_type and $from and $to){
             $query=$this->db->query("select * from pay_loan where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type'  order by CONVERT(DATE, date) desc 
                           "  );
        }
      
       $cash=0.0;
        $chq=0.0;
         $sumtotal=0.0;
        $bank[4]=0.0;
         $bank[5]=0.0;
        $bank[6]=0.0;
//$data=$query->result();
      foreach ($query->result() as $key => $value){
          $type= $value->type;
          
         $expense_type= $this->department_model->get_payloantab_type($value->type);
          $date= date('d F Y',strtotime($value->date)) ;
        
 
          $total= $value->total;
          
   $bank=unserialize($value->bank_info);
		
          $cheque=$total-$bank[$cashbank];
          $cash+=$bank[$cashbank];
          $chq+=$value->total-$bank[$cashbank];
          $sumtotal+=$value->total;
			$object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $date);
			$object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $expense_type);
			$object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $bank[$cashbank]);
        
			$object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $cheque);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $total);
			$excel_row++;
      }
        $object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, Total);
          $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $cash);
			$object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $chq);
            $object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $sumtotal);
		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Employee Data.xls"');
		$object_writer->save('php://output');
	}

    
    function transfer()
	{
         $from=$this->input->post('from');
        $to=$this->input->post('to');
		$this->load->model("excel_export_model");
		$this->load->library("excel");
		$object = new PHPExcel();

		$object->setActiveSheetIndex(0);

		$table_columns = array("DATE", "FROM",  "AMOUNT","TO", "TOTAL");

		$column = 0;

		foreach($table_columns as $field)
		{
			$object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
			$column++;
		}

		$employee_data = $this->excel_export_model->fetch_data();
  $b_type='';
         $bus_type=$this->db->query("select * from buisness_type  
  ");
  $data='';
 $row = $bus_type->row();
     $b_type= $this->department_model->get_buisness_type();
		$excel_row = 2;
      
 if ($from and $to){
             $query=$this->db->query("select * from transfer where (date BETWEEN '".$from."' AND '".$to."' ) and  status=1 and buisness_type='$b_type'  order by CONVERT(DATE, date) desc 
                           "  );
        }
        else{
           $query=$this->db->query("select * from transfer where status=1 and buisness_type='$b_type'
    ");    
        }
 
//$data=$query->result();
      foreach ($query->result() as $key => $value){
          $type= $value->bank_from;
          $bank= $value->amount;
          $date= date('d F Y',strtotime($value->date)) ;
          $bank_to= $value->bank_to;
         
          $total= $value->total;
          
     
		
			$object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $date);
			$object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $type);
			$object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $bank);
          $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $bank_to);
			$object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $total);
			
			$excel_row++;
      }

		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Employee Data.xls"');
		$object_writer->save('php://output');
	}

	
	
	
}

















































	