<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class ExcelReport extends CI_Controller {

	public function __construct()
	{
		parent::__construct();
		$this->load->database();
		$this->load->library('session');
		$this->load->model('user_model');
		$this->load->model('master_model');
		$this->load->model('report_model');
		$this->load->model('issue_model');
		//$this->load->model('stockupdate_model');
		
		if(!isset($_SESSION['user_logged'])) {
			$this->session->set_flashdata("error", "please login first to view dashboard");
			redirect("Login/login");
		}
	}
	
	public function report(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		//$data['branch'] = $this->user_model->get_CWbranch();
		$data['outlet'] = $this->issue_model->get_useroutlet();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/report',$data);
	}
	
	public function user_report(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/user_report',$data);
	}
	
	public function acc_report(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/c_report',$data);
	}
		
	public function exportReport(){
		$branchname = $_SESSION['branch_name'];
		$branch_id = $_SESSION['branch_id'];
		$curdate=date('d-m-Y');
		$fromdate = $this->input->post('from_date');
		$todate = $this->input->post('to_date');
		$report_type = $this->input->post('report_type');
		$from_date = date('Y-m-d', strtotime($fromdate));
		$to_date = date('Y-m-d', strtotime($todate));
		$branch = $this->input->post('branch');
		$bname="('ESH','ESH-TDR','ESH-IND','ESH-CHN','ESH-PNT','ESH-PK','ESH-HK','ESH-SVC','ESH-KTN','ESH-STR2')";
		$br_id="('10','21','22','23','24','25','26','27','54','68')";
        if($report_type == 'Purchase Order')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Purchase-Order".$curdate.".csv";
			
			$query = "SELECT P.purchase_id,P.purchase_no,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date,P.purchase_time as time,P.venders_name as vendor_name,P.branchname,PO.purchase_order_id,PO.item_name,PO.unit_measurement as uom,PO.required_qty as qty,PO.item_price,PO.po_tax,PO.po_tax_amount,PO.actual_amount,PO.total_amount,PO.item_id,IT.itm_id as item_code,PO.cat_id from purchase_order P inner join purchase_order_item PO on P.purchase_id=PO.purchase_id inner join item_creation IT on PO.item_id=IT.item_id where (P.purchase_date between '$from_date' and '$to_date') and P.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'GRN')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "GRN".$curdate.".csv";
			
			$query = "SELECT G.gnr_id as grn_id,G.grn_no,G.invoice_no,G.vendor_name,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,G.grn_time,G.branchname,G.user_name,G.purchase_id,PG.purchase_grn_id,PI.item_name,PI.unit_measurement as uom,PG.grn_item_qty,PG.grn_item_price,PG.grn_tax,PG.grn_tax_amount,PG.grn_total_amount,PG.grn_grand,PG.purchase_order_id,PI.item_id,IT.itm_id as item_code,PI.cat_id from gnr_creation G inner join purchase_order_grn PG on G.gnr_id=PG.gnr_id inner join purchase_order_item PI on PG.purchase_order_id=PI.purchase_order_id inner join item_creation IT on PI.item_id=IT.item_id where (G.grn_date between '$from_date' and '$to_date') and G.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Rejection')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Rejection".$curdate.".csv";
			
			$query = "SELECT R.rejection_no,DATE_FORMAT(R.rejection_date, '%d-%m-%Y') as rejection_date,R.rtime as rjn_time,G.grn_no,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,R.branchname,I.item_name,I.uom,RI.rejection_qty as rejected_qty,RI.rejection_price as price,RI.rejection_tax as tax,RI.rejection_tax_amount as tax_amt,round((RI.rejection_qty*RI.rejection_price),2) as rejection_amt,RI.rejection_total_amount as total_amt,R.gnr_id as gnr_id,RI.item_id,RI.itm_id as item_code,RI.category_id FROM gnr_creation G inner join rejection_creation R on G.gnr_id=R.gnr_id inner join purchase_order_rejection RI on R.rejection_id=RI.rejection_id inner join item_creation I on I.item_id=RI.item_id WHERE (R.rejection_date between '$from_date' and '$to_date') and R.branchname NOT in  $bname and RI.rejection_qty!=0";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Godown Transfer')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Godown-Transfer".$curdate.".csv";
			
			$query = "SELECT S.stk_tr_no,DATE_FORMAT(S.stk_tr_date, '%d-%m-%Y') as stk_tr_date,S.tr_time,S.frm_branchname as frm_branch,S.to_branchname as to_branch,SI.item_name,SI.uom,SI.quantity as tr_quantity,SI.price,SI.total_amount,SI.item_id,SI.itm_id as item_code,SI.category_id FROM stock_transfer S inner join stock_transfer_item SI on S.stk_tr_id=SI.stk_tr_id WHERE (S.stk_tr_date between '$from_date' and '$to_date') and S.frm_branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Damage')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Damage".$curdate.".csv";
			
			$query = "SELECT D.damage_id,D.damage_no,DATE_FORMAT(D.damage_date, '%d-%m-%Y') as damage_date,D.dtime as time,D.branchname,DI.damage_order_id,DI.damage_item_name,DI.damage_uom as uom,DI.damage_qty,DI.item_id,IT.itm_id as item_code,DI.category_id,DI.damage_remarks from damage_order_creation D inner join damage_order_item DI on D.damage_id=DI.damage_id inner join item_creation IT on DI.item_id=IT.item_id where (D.damage_date between '$from_date' and '$to_date') and D.branchname='$branch'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Direct Issue')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Direct-Issue".$curdate.".csv";
			
			$query = "SELECT i.issue_id,i.issue_no,DATE_FORMAT(i.issue_date, '%d-%m-%Y') as issue_date,i.issue_time,i.outlet_name,i.total,i.branchname,it.indent_item_name as item_name,it.indent_uom as uom,it.indent_qty as issued_qty,it.issues_price as rate,it.tax,it.tax_amount,it.basic_price as amount,it.total_amount as total_price,it.item_id,IM.itm_id as item_code,it.cat_id from issues_order_creation i inner join issues_order_item it on i.issue_id=it.issue_id inner join item_creation IM on it.item_id=IM.item_id where i.issue_date between '$from_date' and '$to_date' and i.branchname='$branch'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Indent')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Indent".$curdate.".csv";
			
			$query = "SELECT m.request_id,m.mat_req_no,DATE_FORMAT(m.request_date, '%d-%m-%Y') as request_date,m.request_time,m.store,m.branchname,mr.request_item_id,mr.item_name,mr.uom,mr.required_qty as requested_qty,mr.recieved_qty as issued_qty,mr.receipt_qty,mr.item_id,IT.itm_id as item_code,mr.category_id from material_request m inner join material_request_item mr on m.request_id=mr.request_id inner join item_creation IT on mr.item_id=IT.item_id where (m.request_date between '$from_date' and '$to_date') and m.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Issue')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Issue".$curdate.".csv";
			
			$query = "SELECT i.issue_id,r.mat_req_no,i.issue_no,DATE_FORMAT(i.issue_date, '%d-%m-%Y') as issue_date,i.issue_time,i.outlet_name,i.total,i.branchname,i.request_id,i.user_name,it.indent_item_name as item_name,it.indent_uom as uom,it.indent_qty as issued_qty,it.issues_price as rate,it.tax,it.tax_amount,it.basic_price as amount,it.total_amount as total_price,it.request_item_id,it.item_id,IM.itm_id as item_code,mri.category_id from material_request r inner join issues_order_creation i on r.request_id=i.request_id inner join issues_order_item it on i.issue_id=it.issue_id inner join material_request_item mri on it.request_item_id=mri.request_item_id inner join item_creation IM on it.item_id=IM.item_id where (i.issue_date between '$from_date' and '$to_date') and i.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Inward".$curdate.".csv";
			
			$query = "SELECT MR.mat_req_no as request_no,DATE_FORMAT(MR.request_date, '%d-%m-%Y') as request_date,I.issue_no,DATE_FORMAT(I.issue_date, '%d-%m-%Y') as issue_date,R.req_rec_no,DATE_FORMAT(R.receipt_date, '%d-%m-%Y') as receipt_date,R.receipt_time,R.store,R.branchname,R.request_id,RP.receipt_item_id,mri.item_name,RP.quantity as received_qty,RP.req_rec_id,RP.requset_item_id,RP.item_id,IT.itm_id as item_code,mri.category_id from material_request MR inner join issues_order_creation I on MR.request_id=I.request_id inner join request_receipt R on I.issue_id=R.issue_id inner join request_receipt_item RP on R.req_rec_id=RP.req_rec_id inner join material_request_item mri on RP.requset_item_id=mri.request_item_id inner join item_creation IT on RP.item_id=IT.item_id where (R.receipt_date between '$from_date' and '$to_date') and R.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Issue-Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Issue-Inward-".$fromdate." to ".$todate.".csv";
			
		//	$query = "SELECT I.issue_id,MR.mat_req_no as request_no,I.issue_no,DATE_FORMAT(I.issue_date, '%d-%m-%Y') as issue_date,I.issue_time,I.outlet_name,I.branchname,I.request_id,I.user_name,IT.indent_item_name as item_name,IT.indent_uom as uom,IT.indent_qty as issued_qty,R.req_rec_no,DATE_FORMAT(R.receipt_date, '%d-%m-%Y') as receipt_date,R.receipt_time,RP.quantity as received_qty,RP.category_id from material_request MR inner join issues_order_creation I on MR.request_id=I.request_id inner join request_receipt R on I.issue_id=R.issue_id inner join request_receipt_item RP on R.req_rec_id=RP.req_rec_id inner join issues_order_item IT on RP.issue_order_id=IT.issue_order_id where R.receipt_date between '$from_date' and '$to_date' order by I.issue_no,IT.indent_item_name asc";
			$query = "SELECT I.issue_id,MR.mat_req_no as request_no,I.issue_no,DATE_FORMAT(I.issue_date, '%d-%m-%Y') as issue_date,I.issue_time,I.outlet_name,I.branchname,I.request_id,I.user_name,IT.indent_item_name as item_name,IT.indent_uom as uom,IT.indent_qty as issued_qty,R.req_rec_no,DATE_FORMAT(R.receipt_date, '%d-%m-%Y') as receipt_date,R.receipt_time,RP.quantity as received_qty,IT.cat_id from issues_order_creation I left join request_receipt R on I.issue_id=R.issue_id left join material_request MR on MR.request_id=I.request_id left join issues_order_item IT on I.issue_id=IT.issue_id left join request_receipt_item RP on RP.issue_order_id=IT.issue_order_id and RP.req_rec_id=R.req_rec_id where (I.issue_date between '$from_date' and '$to_date') and I.branchname NOT in  $bname and I.request_id!='0' order by I.issue_no,IT.indent_item_name asc";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Pride Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Pride-Inward".$curdate.".csv";
			
			$query = "SELECT r.req_rec_no,r.receipt_time,r.store,DATE_FORMAT(r.receipt_date, '%d-%m-%Y') as receipt_date,r.branchname,r.request_id,rp.receipt_item_id,mri.item_name,mri.uom,rp.quantity as received_qty,rp.rc_item_price as price,rp.rc_tax as tax,rp.rc_tax_amount as tax_amount,rp.rc_total_price as total,rp.rc_total_amount as total_value,rp.req_rec_id,rp.requset_item_id,rp.item_id,IT.itm_id as item_code,rp.category_id from request_receipt r inner join request_receipt_item rp on r.req_rec_id=rp.req_rec_id inner join material_request_item mri on rp.requset_item_id=mri.request_item_id inner join item_creation IT on rp.item_id=IT.item_id where r.receipt_date between '$from_date' and '$to_date' and r.store='Pride Bakers' and r.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Damage/Wastage')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Damage/Wastage".$curdate.".csv";
			
			$query = "SELECT D.damage_id,D.damage_no,DATE_FORMAT(D.damage_date, '%d-%m-%Y') as damage_date,D.dtime as time,D.return_type as type,D.branchname,DI.damage_order_id,DI.item_type,DI.damage_item_name,DI.damage_uom as uom,DI.damage_qty,DI.item_id,DI.category_id,DI.damage_remarks from damage_order_creation D inner join damage_order_item DI on D.damage_id=DI.damage_id where (D.damage_date between '$from_date' and '$to_date') and D.branchname='$branch'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Month End Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Month-End-Stock".$curdate.".csv";
			
			$query = "SELECT S.branchname as Branch,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as Date,S.stk_time as time,S.stock_no as Stock_Update_No,ST.item_type,ST.item_name as Product_Name,ST.uom as UOM,round(ST.rate,2) as rate,ST.present_stk as System_Stock,ST.closing_stk as Physical_Stock,(ST.present_stk-ST.closing_stk) as Difference,ST.cat_id as Cat_ID,ST.remarks from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id where (S.stk_date between '$from_date' and '$to_date') and S.branchname='$branch' and S.stock_no like 'S%'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Daily Stock Update')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Daily-Stock-Update".$curdate.".csv";
			
			$query = "SELECT S.stck_id,S.stock_no,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as stk_date,S.stk_time as time,S.branchname,ST.stk_item_id,ST.item_type,ST.item_name,ST.uom,round((((R.price*R.tax)/100)+R.price),2) as Rate,ST.present_stk as system_stock,ST.closing_stk,ST.item_id,ST.cat_id,ST.remarks from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id inner join recipe R on ST.item_id=R.recipe_id where (S.stk_date between '$from_date' and '$to_date') and S.branchname='$branch' and S.stock_no like 'D%'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Production')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Production".$curdate.".csv";
			
			$query = "SELECT b.billno as production_id,b.bill_id as ref_no,bp.pr_cat_id,DATE_FORMAT(b.orderdate, '%d-%m-%Y') as orderdate,b.ordertime as time,b.chef,IF(b.outlet='Cold Kitchen','CW4-Cold Kitchen',b.outlet) as outlet,bp.pname as product_name,bp.uom,bp.yeild as projected_yeild,bp.actual_yeild from billdetails b inner join bom_product bp on b.billno=bp.billno where (b.orderdate between '$from_date' and '$to_date') and b.branch_id='2'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'PIndent')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product_Indent".$curdate.".csv";
			
			$query = "SELECT P.request_id,P.pr_req_no,DATE_FORMAT(P.request_date, '%d-%m-%Y') as request_date,P.request_time,P.dept,P.branchname,P.orders as order_type,PI.request_item_id,PI.item_name,PI.uom,PI.required_qty as requested_qty,PI.recieved_qty as issued_qty,PI.receipt_qty,PI.pr_cat_id from product_request P inner join product_request_item PI on P.request_id=PI.request_id where (P.request_date between '$from_date' and '$to_date') and P.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'PIssue')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product_Issue_".$curdate.".csv";
			
			$query = "SELECT PI.issue_id,P.pr_req_no,PI.issue_no,DATE_FORMAT(PI.issue_date, '%d-%m-%Y') as issue_date,PI.issue_time,PI.outlet,PI.branchname,PI.request_id,PIT.issue_item_name as item_name,PIT.issue_uom as uom,PIT.issued_qty as issued_qty,PIT.price as rate,PIT.tax,PIT.tax_amount,PIT.basic_price as amount,PIT.total_price,PIT.request_item_id,PRI.pr_cat_id from product_request P inner join product_issues_creation PI on P.request_id=PI.request_id inner join product_issues_item PIT on PI.issue_id=PIT.issue_id inner join product_request_item PRI on PIT.request_item_id=PRI.request_item_id where (PI.issue_date between '$from_date' and '$to_date') and PI.branchname='$branch'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'PInward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product_Inward".$curdate.".csv";
			
			$query = "SELECT PR.req_rec_no,DATE_FORMAT(PR.receipt_date, '%d-%m-%Y') as receipt_date,PR.receipt_time,PR.dept,PR.branchname,PR.request_id,PRI.receipt_item_id,PRI.item_name,PRI.quantity as received_qty,PRI.req_rec_id,PRI.request_item_id,PRIT.pr_cat_id from product_req_rec PR inner join product_req_rec_item PRI on PR.req_rec_id=PRI.req_rec_id inner join product_request_item PRIT on PRI.request_item_id=PRIT.request_item_id where (PR.receipt_date between '$from_date' and '$to_date') and PR.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'PIssueInward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product-Issue-Inward-".$fromdate." to ".$todate.".csv";
			
		//	$query = "SELECT I.issue_id,MR.pr_req_no as request_no,I.issue_no,DATE_FORMAT(I.issue_date, '%d-%m-%Y') as issue_date,I.issue_time,I.outlet,I.branchname,I.request_id,IT.issue_item_name as item_name,IT.issue_uom as uom,IT.issued_qty,IT.request_item_id,R.req_rec_no,DATE_FORMAT(R.receipt_date, '%d-%m-%Y') as receipt_date,R.receipt_time,RP.quantity as received_qty,RP.pr_cat_id as category_id from product_request MR inner join product_issues_creation I on MR.request_id=I.request_id inner join product_req_rec R on I.issue_id=R.issue_id inner join product_req_rec_item RP on R.req_rec_id=RP.req_rec_id inner join product_issues_item IT on RP.issue_order_id=IT.issue_order_id where R.receipt_date between '$from_date' and '$to_date' and MR.dept!='Pride Bakers' order by I.issue_no,IT.issue_item_name asc";
			$query = "SELECT I.issue_id,MR.pr_req_no as request_no,I.issue_no,DATE_FORMAT(I.issue_date, '%d-%m-%Y') as issue_date,I.issue_time,I.outlet,I.branchname,I.request_id,IT.issue_item_name as item_name,IT.issue_uom as uom,IT.issued_qty,IT.request_item_id,R.req_rec_no,DATE_FORMAT(R.receipt_date, '%d-%m-%Y') as receipt_date,R.receipt_time,RP.quantity as received_qty,IT.pr_cat_id as category_id from product_issues_creation I left join product_req_rec R on I.issue_id=R.issue_id left join  product_request MR on MR.request_id=I.request_id left join product_issues_item IT on I.issue_id=IT.issue_id left join product_req_rec_item RP on RP.issue_order_id=IT.issue_order_id and RP.req_rec_id=R.req_rec_id where (I.issue_date between '$from_date' and '$to_date') and I.branchname NOT in  $bname and MR.dept!='Pride Bakers' and I.request_id!='0' order by I.issue_no,IT.issue_item_name asc";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Transfer')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Transfer".$curdate.".csv";
			
			$query = "SELECT T.tr_id,T.trans_no,DATE_FORMAT(T.trans_date, '%d-%m-%Y') as trans_date,T.trans_time,T.dept_name as to_dept,T.branchname,TI.tr_item_id,TI.item_type,TI.item_name,TI.uom,TI.quantity,TI.item_id,TI.cat_id from transfer T inner join transfer_item TI on T.tr_id=TI.tr_id where (T.trans_date between '$from_date' and '$to_date') and T.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Transfer Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Transfer-Inward".$curdate.".csv";
			
			$query = "SELECT TI.trans_in_no,DATE_FORMAT(TI.tr_in_date, '%d-%m-%Y') as tr_in_date,TI.in_time,TI.from_dept,TI.branchname,TI.tr_id,TIT.tr_in_item_id,TIT.item_type,TIT.item_name,TIT.quantity as received_qty,TIT.tr_in_id,TIT.tr_item_id,TIT.item_id,TIT.cat_id from transfer_inward TI inner join transfer_inward_item TIT on TI.tr_in_id=TIT.tr_in_id where (TI.tr_in_date between '$from_date' and '$to_date') and TI.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material-List".$curdate.".csv";
			
			$query = "SELECT I.itm_id as item_id,I.item_name,I.category_id,C.category_name,I.uom,DATE_FORMAT(I.creation_date, '%d-%m-%Y') as creation_date FROM item I inner join category C on C.category_id=I.category_id where I.active_status='active' order by I.itm_id desc";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material-Stock".$curdate.".csv";
			
			$query = "select c.category_name,i.item_name,i.uom,i.item_price,i.item_tax,i.avg_price,i.vendor_name,st.closing_stock,(i.item_price*st.closing_stock) as total from (select * from category) c inner join (select item_name,uom,item_price,item_tax,avg_price,vendor_name,category_id from item_creation where branchname='$branch' and status='Enabled') i on c.category_id=i.category_id inner join (select * from stock_detail) st on i.item_name=st.item_name inner join (select item_name,transaction_date,closing_stock,MAX(last_modified) as maxdate from stock_detail where branchname='$branch' and transaction_date <= '$to_date' group by item_name) stk on st.item_name=stk.item_name and st.last_modified=stk.maxdate";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Product Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product-Stock".$curdate.".csv";
			
			$query = "select c.pr_cat_name as category,i.rname as product_name,i.uom,i.recipe_price as price,st.closing_stock,(i.recipe_price*st.closing_stock) as total from (select * from product_category) c inner join (select recipe_no,rname,uom,recipe_price,pr_cat_id from recipe where branchname='$branch' and status='Enabled') i on c.pr_cat_id=i.pr_cat_id inner join (select * from stock_detail) st on i.recipe_no=st.recipe_no inner join (select item_name,recipe_no,transaction_date,closing_stock,MAX(last_modified) as maxdate from stock_detail where branchname='$branch' and transaction_date <= '$to_date' group by recipe_no) stk on st.recipe_no=stk.recipe_no and st.last_modified=stk.maxdate";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'CMS_Products')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "CMS_Products.csv";
			
			$query = "SELECT R.rname as product_name,R.uom,R.cw_price as price,R.cw_tax as tax,R.cw_tax_amount as tax_amount,R.cw_total_price as total_price,C.pr_cat_id as cat_id,C.pr_cat_name as cat_name from recipe R inner join product_category C on R.pr_cat_id=C.pr_cat_id where R.cw_price!='0' and R.branchname='CMS'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		
		else if($report_type == 'Store Invoice')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Store-Invoice.csv";
			
			$query = "SELECT PI.issue_id as inv_id,PI.invoice_no,DATE_FORMAT(PI.issue_date, '%d-%m-%Y') as invoice_date,PI.issue_time as invoice_time,PI.outlet,PI.branchname,PIT.issue_item_name as item_name,PIT.issue_uom as uom,PIT.issued_qty as qty,PIT.price as rate,PIT.tax,PIT.tax_amount,PIT.basic_price as amount,PIT.total_price,PIT.issue_id as invoice_id,PIT.pr_cat_id from product_issues_creation PI inner join product_issues_item PIT on PI.issue_id=PIT.issue_id where (PI.issue_date between '$from_date' and '$to_date') and PI.branchname NOT in  $bname and PI.invoice_no!=''";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material Invoice')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material-Invoice.csv";
			
			$query = "SELECT PI.invoice_id as inv_id,PI.invoice_no,DATE_FORMAT(PI.invoice_date, '%d-%m-%Y') as invoice_date,PI.invoice_time as invoice_time,PI.outlet_name,PI.branchname,PIT.item_name as item_name,PIT.uom as uom,PIT.required_qty as qty,PIT.item_price as rate,PIT.tax,PIT.tax_amount,(PIT.item_price*PIT.required_qty) as amount,PIT.total_amount as total_price,PIT.invoice_id as invoice_id,PIT.category_id from invoice PI inner join invoice_item PIT on PI.invoice_id=PIT.invoice_id where (PI.invoice_date between '$from_date' and '$to_date') and PI.branchname NOT in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Recipe')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Recipe-Report.csv";
			
			$query = "SELECT r.`recipe_id`, r.`rname`, r.`yeild`, r.`uom`, r.`pr_cat_id`, r.`branchname`,ri.`rc_item_id`, ri.`item_name`, ri.uom as uoms,ri.`qty`, ri.`price` as basicPrice, ri.`total` as totalA, ri.`item_id` as item_ids, ri.`category_id` as category_ids FROM `recipe` r inner join recipe_item ri on r.recipe_id=ri.recipe_id where r.branch_id='2' UNION ALL SELECT r.`recipe_id`,r.`rname`,r.`yeild`, r.`uom`,r.`pr_cat_id`, r.`branchname`,ri.`sub_rc_item_id`, ri.`item_name`,ri.uom as uoms,ri.`qty`, ri.`price` as basicPrice, ri.`total` as totalA, ri.`sub_recipe_id` as item_ids, ri.`pr_cat_id` as category_ids FROM `recipe` r inner join sub_recipe_item ri on r.recipe_id=ri.recipe_id where r.branch_id='2'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Carnival Issue')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Carnival-Issue".$curdate.".csv";
			
			$query = "SELECT i.issue_id,i.issue_no,DATE_FORMAT(i.issue_date, '%d-%m-%Y') as issue_date,i.issue_time,i.outlet,i.total_amount,i.branchname,it.issue_item_name as item_name,it.issue_uom as uom,it.issued_qty as issued_qty,it.price as rate,it.tax,it.tax_amount,it.basic_price as amount,it.total_price as total_price,it.recipe_id,it.recipe_no as item_code,it.pr_cat_id from product_issues_creation i inner join product_issues_item it on i.issue_id=it.issue_id where i.issue_date between '$from_date' and '$to_date' and i.request_id='0' and i.branchname NOT in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
	}

	public function reportC(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/c_report',$data);
	}
		
	public function exportCReport(){
		$branchname = $_SESSION['branch_name'];
		$branch_id = $_SESSION['branch_id'];
		$curdate=date('d-m-Y');
		$fromdate = $this->input->post('from_date');
		$todate = $this->input->post('to_date');
		$report_type = $this->input->post('report_type');
		$from_date = date('Y-m-d', strtotime($fromdate));
		$to_date = date('Y-m-d', strtotime($todate));
		//$branch = $this->input->post('branch');
				
        if($report_type == 'Purchase Order')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Purchase-Order".$curdate.".csv";
			
			$query = "SELECT P.purchase_id,P.purchase_no,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date,P.purchase_time as time,P.venders_name as vendor_name,P.branchname,PO.purchase_order_id,PO.item_name,PO.unit_measurement as uom,PO.required_qty as qty,PO.item_price,PO.po_tax,PO.po_tax_amount,PO.actual_amount,PO.total_amount,PO.item_id,IT.itm_id as item_code,PO.cat_id from purchase_order P inner join purchase_order_item PO on P.purchase_id=PO.purchase_id inner join item_creation IT on PO.item_id=IT.item_id where P.purchase_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'GRN')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "GRN".$curdate.".csv";
			
			$query = "SELECT G.gnr_id as grn_id,G.grn_no,G.invoice_no,G.vendor_name,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,G.grn_time,G.branchname,G.user_name,G.purchase_id,PG.purchase_grn_id,PI.item_name,PI.unit_measurement as uom,PG.grn_item_qty,PG.grn_item_price,PG.grn_tax,PG.grn_tax_amount,PG.grn_total_amount,PG.grn_grand,PG.purchase_order_id,PI.item_id,IT.itm_id as item_code,PI.cat_id from gnr_creation G inner join purchase_order_grn PG on G.gnr_id=PG.gnr_id inner join purchase_order_item PI on PG.purchase_order_id=PI.purchase_order_id inner join item_creation IT on PI.item_id=IT.item_id where G.grn_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Rejection')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Rejection".$curdate.".csv";
			
			$query = "SELECT R.rejection_no,DATE_FORMAT(R.rejection_date, '%d-%m-%Y') as rejection_date,R.rtime as rjn_time,G.grn_no,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,R.branchname,I.item_name,I.uom,RI.rejection_qty as rejected_qty,RI.rejection_price as price,RI.rejection_tax as tax,RI.rejection_tax_amount as tax_amt,RI.rejection_total_amount as total_amt,R.gnr_id as gnr_id,RI.item_id,RI.itm_id as item_code,RI.category_id FROM gnr_creation G inner join rejection_creation R on G.gnr_id=R.gnr_id inner join purchase_order_rejection RI on R.rejection_id=RI.rejection_id inner join item_creation I on I.item_id=RI.item_id WHERE R.rejection_date between '$from_date' and '$to_date' and RI.rejection_qty!=0";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Godown Transfer')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Godown-Transfer".$curdate.".csv";
			
			$query = "SELECT S.stk_tr_no,DATE_FORMAT(S.stk_tr_date, '%d-%m-%Y') as stk_tr_date,S.tr_time,S.frm_branchname as frm_branch,S.to_branchname as to_branch,SI.item_name,SI.uom,SI.quantity as tr_quantity,SI.price,SI.total_amount,SI.item_id,SI.itm_id as item_code,SI.category_id FROM stock_transfer S inner join stock_transfer_item SI on S.stk_tr_id=SI.stk_tr_id WHERE S.stk_tr_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Damage')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Damage".$curdate.".csv";
			
			$query = "SELECT D.damage_id,D.damage_no,DATE_FORMAT(D.damage_date, '%d-%m-%Y') as damage_date,D.dtime as time,D.branchname,DI.damage_order_id,DI.damage_item_name,DI.damage_uom as uom,DI.damage_qty,DI.item_id,IT.itm_id as item_code,DI.category_id from damage_order_creation D inner join damage_order_item DI on D.damage_id=DI.damage_id inner join item_creation IT on DI.item_id=IT.item_id where D.return_type IS NULL and (D.damage_date between '$from_date' and '$to_date')";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Direct Issue')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Direct-Issue".$curdate.".csv";
			
			$query = "SELECT i.issue_id,i.issue_no,DATE_FORMAT(i.issue_date, '%d-%m-%Y') as issue_date,i.issue_time,i.outlet_name,i.total,i.branchname,it.indent_item_name as item_name,it.indent_uom as uom,it.indent_qty as issued_qty,it.issues_price as rate,it.tax,it.tax_amount,it.basic_price as amount,it.total_amount as total_price,it.item_id,IM.itm_id as item_code,it.cat_id from issues_order_creation i inner join issues_order_item it on i.issue_id=it.issue_id inner join item_creation IM on it.item_id=IM.item_id where i.issue_date between '$from_date' and '$to_date' and i.request_id='0'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Indent')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Indent".$curdate.".csv";
			
			$query = "SELECT m.request_id,m.mat_req_no,DATE_FORMAT(m.request_date, '%d-%m-%Y') as request_date,m.request_time,m.store,m.branchname,mr.request_item_id,mr.item_name,mr.uom,mr.required_qty as requested_qty,mr.recieved_qty as issued_qty,mr.receipt_qty,mr.item_id,IT.itm_id as item_code,mr.category_id from material_request m inner join material_request_item mr on m.request_id=mr.request_id inner join item_creation IT on mr.item_id=IT.item_id where m.request_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Issue')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Issue".$curdate.".csv";
			
			$query = "SELECT i.issue_id,r.mat_req_no,i.issue_no,DATE_FORMAT(i.issue_date, '%d-%m-%Y') as issue_date,i.issue_time,i.outlet_name,i.total,i.branchname,i.request_id,i.user_name,it.indent_item_name as item_name,it.indent_uom as uom,it.indent_qty as issued_qty,it.issues_price as rate,it.tax,it.tax_amount,it.basic_price as amount,it.total_amount as total_price,it.request_item_id,it.item_id,IM.itm_id as item_code,mri.category_id from material_request r inner join issues_order_creation i on r.request_id=i.request_id inner join issues_order_item it on i.issue_id=it.issue_id inner join material_request_item mri on it.request_item_id=mri.request_item_id inner join item_creation IM on it.item_id=IM.item_id where i.issue_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Inward".$curdate.".csv";
			
			$query = "SELECT MR.mat_req_no as request_no,DATE_FORMAT(MR.request_date, '%d-%m-%Y') as request_date,I.issue_no,DATE_FORMAT(I.issue_date, '%d-%m-%Y') as issue_date,R.req_rec_no,DATE_FORMAT(R.receipt_date, '%d-%m-%Y') as receipt_date,R.receipt_time,R.store,R.branchname,R.request_id,RP.receipt_item_id,mri.item_name,RP.quantity as received_qty,RP.req_rec_id,RP.requset_item_id,RP.item_id,IT.itm_id as item_code,mri.category_id from material_request MR inner join issues_order_creation I on MR.request_id=I.request_id inner join request_receipt R on I.issue_id=R.issue_id inner join request_receipt_item RP on R.req_rec_id=RP.req_rec_id inner join material_request_item mri on RP.requset_item_id=mri.request_item_id inner join item_creation IT on RP.item_id=IT.item_id where R.receipt_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Pride Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Pride-Inward".$curdate.".csv";
			
			$query = "SELECT r.req_rec_no,r.receipt_time,r.store,DATE_FORMAT(r.receipt_date, '%d-%m-%Y') as receipt_date,r.branchname,r.request_id,rp.receipt_item_id,mri.item_name,mri.uom,rp.quantity as received_qty,rp.rc_item_price as price,rp.rc_tax as tax,rp.rc_tax_amount as tax_amount,rp.rc_total_price as total,rp.rc_total_amount as total_value,rp.req_rec_id,rp.requset_item_id,rp.item_id,IT.itm_id as item_code,rp.category_id from request_receipt r inner join request_receipt_item rp on r.req_rec_id=rp.req_rec_id inner join material_request_item mri on rp.requset_item_id=mri.request_item_id inner join item_creation IT on rp.item_id=IT.item_id where r.receipt_date between '$from_date' and '$to_date' and r.store='Pride Bakers'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Damage/Wastage')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Damage/Wastage".$curdate.".csv";
			
			$query = "SELECT D.damage_id,D.damage_no,DATE_FORMAT(D.damage_date, '%d-%m-%Y') as damage_date,D.dtime as time,D.return_type as type,D.branchname,DI.damage_order_id,DI.item_type,DI.damage_item_name,DI.damage_uom as uom,DI.damage_qty,DI.item_id,DI.category_id from damage_order_creation D inner join damage_order_item DI on D.damage_id=DI.damage_id where D.return_type IS NOT NULL and (D.damage_date between '$from_date' and '$to_date')";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Month End Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Month-End-Stock".$curdate.".csv";
			
			$query = "SELECT S.branchname as Branch,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as Date,S.stk_time as time,S.stock_no as Stock_Update_No,ST.item_type,ST.item_name as Product_Name,ST.uom as UOM,round(ST.rate,2) as rate,ST.present_stk as System_Stock,ST.closing_stk as Physical_Stock,(ST.present_stk-ST.closing_stk) as Difference,ST.cat_id as Cat_ID from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id where S.stk_date between '$from_date' and '$to_date' and S.stock_no like 'S%'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Daily Stock Update')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Daily-Stock-Update".$curdate.".csv";
			
			$query = "SELECT S.stck_id,S.stock_no,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as stk_date,S.stk_time as time,S.branchname,ST.stk_item_id,ST.item_type,ST.item_name,ST.uom,round(ST.rate,2) as rate,ST.present_stk as system_stock,ST.closing_stk,ST.item_id,ST.cat_id from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id where S.stk_date between '$from_date' and '$to_date' and S.stock_no like 'D%'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Production')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Production".$curdate.".csv";
			
			$query = "SELECT b.billno as production_id,b.bill_id as ref_no,bp.pr_cat_id,DATE_FORMAT(b.orderdate, '%d-%m-%Y') as orderdate,b.ordertime as time,b.chef,IF(b.outlet='Cold Kitchen','CW4-Cold Kitchen',b.outlet) as outlet,bp.pname as product_name,bp.uom,bp.yeild as projected_yeild,bp.actual_yeild from billdetails b inner join bom_product bp on b.billno=bp.billno where b.orderdate between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'PIndent')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product_Indent".$curdate.".csv";
			
			$query = "SELECT P.request_id,P.pr_req_no,DATE_FORMAT(P.request_date, '%d-%m-%Y') as request_date,P.request_time,P.dept,P.branchname,P.orders as order_type,PI.request_item_id,PI.item_name,PI.uom,PI.required_qty as requested_qty,PI.recieved_qty as issued_qty,PI.receipt_qty,PI.pr_cat_id from product_request P inner join product_request_item PI on P.request_id=PI.request_id where P.request_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'PIssue')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product_Issue".$curdate.".csv";
			
			$query = "SELECT PI.issue_id,P.pr_req_no,PI.issue_no,DATE_FORMAT(PI.issue_date, '%d-%m-%Y') as issue_date,PI.issue_time,PI.outlet,PI.branchname,PI.request_id,PIT.issue_item_name as item_name,PIT.issue_uom as uom,PIT.issued_qty as issued_qty,PIT.price as rate,PIT.tax,PIT.tax_amount,PIT.basic_price as amount,PIT.total_price,PIT.request_item_id,PRI.pr_cat_id from product_request P inner join product_issues_creation PI on P.request_id=PI.request_id inner join product_issues_item PIT on PI.issue_id=PIT.issue_id inner join product_request_item PRI on PIT.request_item_id=PRI.request_item_id where PI.issue_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'PInward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product_Inward".$curdate.".csv";
			
			$query = "SELECT PR.req_rec_no,DATE_FORMAT(PR.receipt_date, '%d-%m-%Y') as receipt_date,PR.receipt_time,PR.dept,PR.branchname,PR.request_id,PRI.receipt_item_id,PRI.item_name,PRI.quantity as received_qty,PRI.req_rec_id,PRI.request_item_id,PRIT.pr_cat_id from product_req_rec PR inner join product_req_rec_item PRI on PR.req_rec_id=PRI.req_rec_id inner join product_request_item PRIT on PRI.request_item_id=PRIT.request_item_id where PR.receipt_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Transfer')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Transfer".$curdate.".csv";
			
			$query = "SELECT T.tr_id,T.trans_no,DATE_FORMAT(T.trans_date, '%d-%m-%Y') as trans_date,T.trans_time,T.dept_name as to_dept,T.branchname,TI.tr_item_id,TI.item_type,TI.item_name,TI.uom,TI.quantity,TI.item_id,TI.cat_id from transfer T inner join transfer_item TI on T.tr_id=TI.tr_id where T.trans_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Transfer Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Transfer-Inward".$curdate.".csv";
			
			$query = "SELECT TI.trans_in_no,DATE_FORMAT(TI.tr_in_date, '%d-%m-%Y') as tr_in_date,TI.in_time,TI.from_dept,TI.branchname,TI.tr_id,TIT.tr_in_item_id,TIT.item_type,TIT.item_name,TIT.quantity as received_qty,TIT.tr_in_id,TIT.tr_item_id,TIT.item_id,TIT.cat_id from transfer_inward TI inner join transfer_inward_item TIT on TI.tr_in_id=TIT.tr_in_id where TI.tr_in_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material".$curdate.".csv";
			
			$query = "SELECT I.itm_id as item_id,I.item_name,I.category_id,C.category_name,I.uom,DATE_FORMAT(I.creation_date, '%d-%m-%Y') as creation_date FROM item I inner join category C on C.category_id=I.category_id where I.active_status='active' order by I.itm_id desc";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material-Stock".$curdate.".csv";
			
			$query = "select c.category_name,i.item_name,i.uom,i.item_price,i.item_tax,i.avg_price,i.vendor_name,st.closing_stock,(i.item_price*st.closing_stock) as total from (select * from category) c inner join (select item_name,uom,item_price,item_tax,avg_price,vendor_name,category_id from item_creation where branchname='$branch' and status='Enabled') i on c.category_id=i.category_id inner join (select * from stock_detail) st on i.item_name=st.item_name inner join (select item_name,transaction_date,closing_stock,MAX(last_modified) as maxdate from stock_detail where branchname='$branch' and transaction_date <= '$to_date' group by item_name) stk on st.item_name=stk.item_name and st.last_modified=stk.maxdate";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Product Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product-Stock".$curdate.".csv";
			
			$query = "select c.pr_cat_name as category,i.rname as product_name,i.uom,i.recipe_price as price,st.closing_stock,(i.recipe_price*st.closing_stock) as total from (select * from product_category) c inner join (select recipe_no,rname,uom,recipe_price,pr_cat_id from recipe where branchname='$branch' and status='Enabled') i on c.pr_cat_id=i.pr_cat_id inner join (select * from stock_detail) st on i.recipe_no=st.recipe_no inner join (select item_name,recipe_no,transaction_date,closing_stock,MAX(last_modified) as maxdate from stock_detail where branchname='$branch' and transaction_date <= '$to_date' group by recipe_no) stk on st.recipe_no=stk.recipe_no and st.last_modified=stk.maxdate";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'CMS_Products')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "CMS_Products".$curdate.".csv";
			
			$query = "SELECT R.rname as product_name,R.uom,R.cw_price as price,R.cw_tax as tax,R.cw_tax_amount as tax_amount,R.cw_total_price as total_price,C.pr_cat_id as cat_id,C.pr_cat_name as cat_name from recipe R inner join product_category C on R.pr_cat_id=C.pr_cat_id where R.cw_price!='0' and R.branchname='CMS'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Store Invoice')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Store-Invoice".$curdate.".csv";
			
			$query = "SELECT PI.issue_id as inv_id,PI.invoice_no,DATE_FORMAT(PI.issue_date, '%d-%m-%Y') as invoice_date,PI.issue_time as invoice_time,PI.outlet,PI.branchname,PIT.issue_item_name as item_name,PIT.issue_uom as uom,PIT.issued_qty as qty,PIT.price as rate,PIT.tax,PIT.tax_amount,PIT.basic_price as amount,PIT.total_price,PIT.issue_id as invoice_id,PIT.pr_cat_id from product_issues_creation PI inner join product_issues_item PIT on PI.issue_id=PIT.issue_id where PI.issue_date between '$from_date' and '$to_date' and PI.invoice_no!=''";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material Invoice')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material-Invoice".$curdate.".csv";
			
			$query = "SELECT PI.invoice_id as inv_id,PI.invoice_no,DATE_FORMAT(PI.invoice_date, '%d-%m-%Y') as invoice_date,PI.invoice_time as invoice_time,PI.outlet_name,PI.branchname,PIT.item_name as item_name,PIT.uom as uom,PIT.required_qty as qty,PIT.item_price as rate,PIT.tax,PIT.tax_amount,(PIT.item_price*PIT.required_qty) as amount,PIT.total_amount as total_price,PIT.invoice_id as invoice_id,PIT.category_id from invoice PI inner join invoice_item PIT on PI.invoice_id=PIT.invoice_id where PI.invoice_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material Invoice')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material-Invoice".$curdate.".csv";
			
			$query = "SELECT PI.invoice_id as inv_id,PI.invoice_no,DATE_FORMAT(PI.invoice_date, '%d-%m-%Y') as invoice_date,PI.invoice_time as invoice_time,PI.outlet_name,PI.branchname,PIT.item_name as item_name,PIT.uom as uom,PIT.required_qty as qty,PIT.item_price as rate,PIT.tax,PIT.tax_amount,(PIT.item_price*PIT.required_qty) as amount,PIT.total_amount as total_price,PIT.invoice_id as invoice_id,PIT.category_id from invoice PI inner join invoice_item PIT on PI.invoice_id=PIT.invoice_id where PI.invoice_date between '$from_date' and '$to_date'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
	}
	
	public function exportAccReport(){
		$branchname = $_SESSION['branch_name'];
		$branch_id = $_SESSION['branch_id'];
		$curdate=date('d-m-Y');
		$fromdate = $this->input->post('from_date');
		$todate = $this->input->post('to_date');
		$report_type = $this->input->post('report_type');
		$from_date = date('Y-m-d', strtotime($fromdate));
		$to_date = date('Y-m-d', strtotime($todate));
		$branch = $this->input->post('branch');
				
		if($branch == 'All')
		{
		    if($report_type == 'Month End Stock')
    		{
    		    $this->load->dbutil();
    			$this->load->helper('file');
    			$this->load->helper('download');
    			$delimiter = ",";
    			$newline = "\r\n";
    			$filename = "Month-End-Stock-Report".$curdate.".csv";
    			
    			$query = "SELECT S.branchname as Branch,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as Date,S.stk_time as time,S.stock_no as Stock_Update_No,ST.item_name as Product_Name,ST.uom as UOM,ST.present_stk as System_Stock,ST.closing_stk as Physical_Stock,(ST.present_stk-ST.closing_stk) as Difference,IT.category_id as Cat_ID from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id left join item_creation IT on ST.item_id=IT.item_id where S.stk_date between '$from_date' and '$to_date' and S.stock_no like 'S%'";
    		
    			$result = $this->db->query($query);
    			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
    			force_download($filename, $data);
    		}
    		else if($report_type == 'Month End Product Stock')
    		{
    			$this->load->dbutil();
    			$this->load->helper('file');
    			$this->load->helper('download');
    			$delimiter = ",";
    			$newline = "\r\n";
    			$filename = "Month-End-Stock-Report".$curdate.".csv";
    			
    			$query = "SELECT S.stck_id,S.stock_no,DATE_FORMAT(S.stdate, '%d-%m-%Y') as stk_date,S.stk_time as time,S.branchname,ST.stk_item_id,ST.item_type,ST.item_name,ST.uom,ST.present_stk as system_stock,ST.closing_stk,ST.stck_id,ST.item_id,ST.cat_id from (SELECT max(stk_date) as stdate,stck_id,stock_no,DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date,stk_time,branchname from stock_update where stk_date between '$from_date' and '$to_date' and stock_no like 'D%' GROUP BY month(stk_date), year(stk_date), branchname) S inner join (select stk_item_id,item_type,item_name,uom,present_stk,closing_stk,stck_id,item_id,cat_id,remarks from stock_update_item) ST on S.stck_id=ST.stck_id";
    		
    			$result = $this->db->query($query);
    			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
    			force_download($filename, $data);
    		}
    		else if($report_type == 'Daily Stock Update')
    		{
    			$this->load->dbutil();
    			$this->load->helper('file');
    			$this->load->helper('download');
    			$delimiter = ",";
    			$newline = "\r\n";
    			$filename = "Daily-Stock-Update-Report".$curdate.".csv";
    			
    			$query = "SELECT S.stck_id,S.stock_no,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as stk_date,S.stk_time as time,S.branchname,ST.stk_item_id,ST.item_type,ST.item_name,ST.uom,ST.present_stk as system_stock,ST.closing_stk,ST.stck_id,ST.item_id,ST.cat_id from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id where S.stk_date between '$from_date' and '$to_date' and S.stock_no like 'D%'";
    		
    			$result = $this->db->query($query);
    			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
    			force_download($filename, $data);
    		}
    		else if($report_type == 'PIssue')
    		{
    			$this->load->dbutil();
    			$this->load->helper('file');
    			$this->load->helper('download');
    			$delimiter = ",";
    			$newline = "\r\n";
    			$filename = "Product-Issue-Report".$curdate.".csv";
    			
    			$query = "SELECT PI.issue_id,P.pr_req_no,PI.issue_no,DATE_FORMAT(PI.issue_date, '%d-%m-%Y') as issue_date,PI.issue_time,PI.outlet,PI.branchname,PI.request_id,PIT.issue_item_name as item_name,PIT.issue_uom as uom,PIT.issued_qty as issued_qty,PIT.price as rate,PIT.tax,PIT.tax_amount,PIT.basic_price as amount,PIT.total_price,PIT.issue_id,PIT.request_item_id,PRI.pr_cat_id from product_request P inner join product_issues_creation PI on P.request_id=PI.request_id inner join product_issues_item PIT on PI.issue_id=PIT.issue_id inner join product_request_item PRI on PIT.request_item_id=PRI.request_item_id where PI.issue_date between '$from_date' and '$to_date'";
    			$result = $this->db->query($query);
    			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
    			force_download($filename, $data);
    		}
    		else if($report_type == 'Pride Inward')
    		{
    			$this->load->dbutil();
    			$this->load->helper('file');
    			$this->load->helper('download');
    			$delimiter = ",";
    			$newline = "\r\n";
    			$filename = "Pride-Inward".$curdate.".csv";
    			
    			$query = "SELECT r.req_rec_no,r.receipt_time,r.store,DATE_FORMAT(r.receipt_date, '%d-%m-%Y') as receipt_date,r.branchname,r.request_id,rp.receipt_item_id,mri.item_name,mri.uom,rp.quantity as received_qty,rp.rc_item_price as price,rp.rc_tax as tax,rp.rc_tax_amount as tax_amount,rp.rc_total_price as total,rp.rc_total_amount as total_value,rp.req_rec_id,rp.requset_item_id,rp.item_id,IT.itm_id as item_code,rp.category_id from request_receipt r inner join request_receipt_item rp on r.req_rec_id=rp.req_rec_id inner join material_request_item mri on rp.requset_item_id=mri.request_item_id inner join item_creation IT on rp.item_id=IT.item_id where r.receipt_date between '$from_date' and '$to_date' and r.store='Pride Bakers' and r.branchname not like 'CW4%' and r.branchname not like 'CW5%'";
    			$result = $this->db->query($query);
    			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
    			force_download($filename, $data);
    		}
		}
		else
		{
		    if($report_type == 'Month End Stock')
    		{
    		    if(stristr($branch,'CW4'))
    		    {
    		        $this->load->dbutil();
        			$this->load->helper('file');
        			$this->load->helper('download');
        			$delimiter = ",";
        			$newline = "\r\n";
        			$filename = "Month-End-Stock-Report".$curdate.".csv";
        			
        			$query = "SELECT S.branchname as Branch,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as Date,S.stk_time as time,S.stock_no as Stock_Update_No,ST.item_name as Product_Name,ST.uom as UOM,ST.present_stk as System_Stock,ST.closing_stk as Physical_Stock,IT.avg_price,(ST.closing_stk*IT.avg_price) as rate,(ST.present_stk-ST.closing_stk) as Difference,IT.category_id as Cat_ID from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id left join item_creation IT on ST.itm_id=IT.itm_id where S.stk_date between '$from_date' and '$to_date' and S.branchname='$branch' and S.stock_no like 'S%' and IT.branchname='CW4'";
    		
    				$result = $this->db->query($query);
        			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
        			force_download($filename, $data);
    		    }
    		    else
    		    {
    		        $this->load->dbutil();
        			$this->load->helper('file');
        			$this->load->helper('download');
        			$delimiter = ",";
        			$newline = "\r\n";
        			$filename = "Month-End-Stock-Report".$curdate.".csv";
        			
        			$query = "SELECT S.branchname as Branch,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as Date,S.stk_time as time,S.stock_no as Stock_Update_No,ST.item_name as Product_Name,ST.uom as UOM,ST.present_stk as System_Stock,ST.closing_stk as Physical_Stock,IT.avg_price,(ST.closing_stk*IT.avg_price) as rate,(ST.present_stk-ST.closing_stk) as Difference,IT.category_id as Cat_ID from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id left join item_creation IT on ST.itm_id=IT.itm_id where S.stk_date between '$from_date' and '$to_date' and S.branchname='$branch' and S.stock_no like 'S%' and IT.branchname='CW'";
    			 	$result = $this->db->query($query);
        			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
        			force_download($filename, $data);
    		    }
    		}
    		else if($report_type == 'Month End Product Stock')
    		{
    			$this->load->dbutil();
    			$this->load->helper('file');
    			$this->load->helper('download');
    			$delimiter = ",";
    			$newline = "\r\n";
    			$filename = "Month-End-Stock-Report".$curdate.".csv";
    			
    			$query = "SELECT S.stck_id,S.stock_no,DATE_FORMAT(S.stdate, '%d-%m-%Y') as stk_date,S.stk_time as time,S.branchname,ST.stk_item_id,ST.item_type,ST.item_name,ST.uom,ST.present_stk as system_stock,ST.closing_stk,ST.remarks as rate,ST.stck_id,ST.item_id,ST.cat_id from (SELECT max(stk_date) as stdate,stck_id,stock_no,DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date,stk_time,branchname from stock_update where stk_date between '$from_date' and '$to_date' and stock_no like 'D%' and branchname='$branch' GROUP BY month(stk_date), year(stk_date), branchname) S inner join (select stk_item_id,item_type,item_name,uom,present_stk,closing_stk,stck_id,item_id,cat_id,remarks from stock_update_item) ST on S.stck_id=ST.stck_id";
    		
    				$result = $this->db->query($query);
        			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
        			force_download($filename, $data);
    		}
    		else if($report_type == 'Daily Stock Update')
    		{
    			$this->load->dbutil();
    			$this->load->helper('file');
    			$this->load->helper('download');
    			$delimiter = ",";
    			$newline = "\r\n";
    			$filename = "Daily-Stock-Update-Report".$curdate.".csv";
    			
    			$query = "SELECT S.stck_id,S.stock_no,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as stk_date,S.stk_time as time,S.branchname,ST.stk_item_id,ST.item_type,ST.item_name,ST.uom,ST.present_stk as system_stock,ST.closing_stk,ST.stck_id,ST.item_id,ST.cat_id from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id where S.stk_date between '$from_date' and '$to_date' and S.stock_no like 'D%' and S.branchname='$branch'";
    		
    			$result = $this->db->query($query);
    			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
    			force_download($filename, $data);
    		}
    		else if($report_type == 'PIssue')
    		{
    			$this->load->dbutil();
    			$this->load->helper('file');
    			$this->load->helper('download');
    			$delimiter = ",";
    			$newline = "\r\n";
    			$filename = "Product-Issue-Report".$curdate.".csv";
    			
    			$query = "SELECT PI.issue_id,P.pr_req_no,PI.issue_no,DATE_FORMAT(PI.issue_date, '%d-%m-%Y') as issue_date,PI.issue_time,PI.outlet,PI.branchname,PI.request_id,PIT.issue_item_name as item_name,PIT.issue_uom as uom,PIT.issued_qty as issued_qty,PIT.price as rate,PIT.tax,PIT.tax_amount,PIT.basic_price as amount,PIT.total_price,PIT.issue_id,PIT.request_item_id,PRI.pr_cat_id from product_request P inner join product_issues_creation PI on P.request_id=PI.request_id inner join product_issues_item PIT on PI.issue_id=PIT.issue_id inner join product_request_item PRI on PIT.request_item_id=PRI.request_item_id where PI.issue_date between '$from_date' and '$to_date' and PI.branchname='$branch'";
    			$result = $this->db->query($query);
    			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
    			force_download($filename, $data);
    		}
    		else if($report_type == 'Pride Inward')
    		{
    			$this->load->dbutil();
    			$this->load->helper('file');
    			$this->load->helper('download');
    			$delimiter = ",";
    			$newline = "\r\n";
    			$filename = "Pride-Inward".$curdate.".csv";
    			
    			$query = "SELECT r.req_rec_no,r.receipt_time,r.store,DATE_FORMAT(r.receipt_date, '%d-%m-%Y') as receipt_date,r.branchname,r.request_id,rp.receipt_item_id,mri.item_name,mri.uom,rp.quantity as received_qty,rp.rc_item_price as price,rp.rc_tax as tax,rp.rc_tax_amount as tax_amount,rp.rc_total_price as total,rp.rc_total_amount as total_value,rp.req_rec_id,rp.requset_item_id,rp.item_id,IT.itm_id as item_code,rp.category_id from request_receipt r inner join request_receipt_item rp on r.req_rec_id=rp.req_rec_id inner join material_request_item mri on rp.requset_item_id=mri.request_item_id inner join item_creation IT on rp.item_id=IT.item_id where r.receipt_date between '$from_date' and '$to_date' and r.store='Pride Bakers' and r.branchname='$branch'";
    			$result = $this->db->query($query);
    			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
    			force_download($filename, $data);
    		}
		}
		if($report_type == 'Month-End Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Month-End-Stock-Report".$curdate.".csv";
			
			$query = "SELECT S.branchname as Branch,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as Date,S.stk_time as time,S.stock_no as Stock_Update_No,ST.item_type,ST.item_name as Product_Name,ST.uom as UOM,round(ST.rate,2) as rate,ST.present_stk as System_Stock,ST.closing_stk as Physical_Stock,(ST.present_stk-ST.closing_stk) as Difference,ST.cat_id as Cat_ID from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id where S.stk_date between '$from_date' and '$to_date' and S.stock_no like 'S%'";
		
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Daily-Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Daily-Stock-Update-Report".$curdate.".csv";
			
			$query = "SELECT S.stck_id,S.stock_no,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as stk_date,S.stk_time as time,S.branchname,ST.stk_item_id,ST.item_type,ST.item_name,ST.uom,round(ST.rate,2) as rate,ST.present_stk as system_stock,ST.closing_stk,ST.stck_id,ST.item_id,ST.cat_id from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id where S.stk_date between '$from_date' and '$to_date' and S.stock_no like 'D%'";
		
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Pride Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Pride-Inward".$curdate.".csv";
			
			$query = "SELECT r.req_rec_no,r.receipt_time,r.store,DATE_FORMAT(r.receipt_date, '%d-%m-%Y') as receipt_date,r.branchname,r.request_id,rp.receipt_item_id,mri.item_name,mri.uom,rp.quantity as received_qty,rp.rc_item_price as price,rp.rc_tax as tax,rp.rc_tax_amount as tax_amount,rp.rc_total_price as total,rp.rc_total_amount as total_value,rp.req_rec_id,rp.requset_item_id,rp.item_id,IT.itm_id as item_code,rp.category_id from request_receipt r inner join request_receipt_item rp on r.req_rec_id=rp.req_rec_id inner join material_request_item mri on rp.requset_item_id=mri.request_item_id inner join item_creation IT on rp.item_id=IT.item_id where r.receipt_date between '$from_date' and '$to_date' and r.store='Pride Bakers'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
	}
	
	//Dashboard report
	public function avg_cat_costReport()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/avg_cat_cost',$data);
	}
	
	public function fetchAvgCatCostReport(){
		// get data
		$postdata = file_get_contents("php://input");
		$request = json_decode($postdata);
		$fmonth = $request->fmonth;
		$tmonth = $request->tmonth;
		$branch1 = $request->branch;
		$branch =  json_encode($branch1); 
		//$branch=implode("''",$branch1);
		//echo $branch;
		
		$data = $this->report_model->fetchAvgCatCostReport($fmonth,$tmonth,$branch);
		echo json_encode($data);
	}
	
	public function avg_costReport()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/avg_item_cost',$data);
	}
	/*public function searchCostVolume()
	{
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch_name'] = $_SESSION['branch_name'];
		
		$branch = $this->input->post('branch');
		
		$data1 = array(
		'branch' =>$branch
		);
		if ($branch == "") {
			$data['date_range_error_message'] = "All feilds are required";
			} else {
			$result = $this->report_model->searchCostVolume($data1);
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
	    $data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/avg_item_cost',$data);
	}*/
	public function fetchAvgCostReport() {
		// get data
		$postdata = file_get_contents("php://input");
		$request = json_decode($postdata);
		$fmonth = $request->fmonth;
		$tmonth = $request->tmonth;
		$branch1 = $request->branch;
		$branch =  json_encode($branch1); 
		 
		$data = $this->report_model->fetchAvgCostReport($fmonth,$tmonth,$branch1,$branch);
		echo json_encode($data);
	}
	
	public function month_purchaseQty_volume()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$data['pvolume'] = $this->report_model->getPurchaseVoulme();
		$this->load->view('ExcelReport/purchaseVolume',$data);
	}
	
	
	
	public function searchPurchaseVolume()
	{
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch_name'] = $_SESSION['branch_name'];
		
		$branch = $this->input->post('branch');
		
		$data1 = array(
		'branch' =>$branch
		);
		if ($branch == "") {
			$data['date_range_error_message'] = "All feilds are required";
			} else {
			$result = $this->report_model->searchPurchaseVolume($data1);
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
	    $data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/purchaseVolume',$data);
	}
	
	public function month_qty_volume()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/month_qty_volume',$data);
	}
	
	public function fetchQtyVolume(){
		// get data
		$postdata = file_get_contents("php://input");
		$request = json_decode($postdata);
		$fmonth = $request->fmonth;
		$tmonth = $request->tmonth;
		$branch1 = $request->branch;
		$branch =  json_encode($branch1); 
		$data = $this->report_model->fetchQtyVolume($fmonth,$tmonth,$branch1,$branch);
		echo json_encode($data);
	}
	
	public function fetchPurchaseVolume(){
		// get data
		$postdata = file_get_contents("php://input");
		$request = json_decode($postdata);
		$fmonth = $request->fmonth;
		$tmonth = $request->tmonth;
		$branch1 = $request->branch;
		$branch =  json_encode($branch1); 
		$data = $this->report_model->fetchPurchaseVolume($fmonth,$tmonth,$branch1,$branch);
		echo json_encode($data);
	}
	
	public function month_locationWise()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		//$data['pvolume'] = $this->report_model->getLocationWise();
		$this->load->view('ExcelReport/locationwise',$data);
	}
	
	
	public function searchLocationWise()
	{
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch_name'] = $_SESSION['branch_name'];
		$from_date = $this->input->post('fmonth');
		$to_date = $this->input->post('tmonth');
		$branch = $this->input->post('branch');
		
		$data1 = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'branch' =>$branch
		);
		if ($branch == "") {
			$data['date_range_error_message'] = "All feilds are required";
			} else {
			$result = $this->report_model->searchLocationWise($data1);
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
	    $data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/locationwise',$data);
	}
	
	
	public function searchCostVolume()
	{
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch_name'] = $_SESSION['branch_name'];
		$from_date = $this->input->post('fmonth');
		$to_date = $this->input->post('tmonth');
		$branch = $this->input->post('branch');
	//	$fdate=date('Y-m-d', strtotime($from_date));
	//	$tdate=date('Y-m-d', strtotime($to_date));
		/*$data1 = array(
		'branch' =>$branch,
		'from_date'=>$from_date,
		'to_date'=>$to_date
		);
		
		'category' => $category,
		'item_name' => $item_name,
		'uom'=>$uom,*/
		$fdate=date('Y-m-d', strtotime($from_date));
		$tdate=date('Y-m-d', strtotime($to_date));
		$query = $this->db->query("select c.category_id,p.category_name,c.branchname,c.grn_price,c.item_name,c.uom,c.itm_id from grn_price c inner join add_category p on c.category_id=p.category_id where c.branchname='$branch' and grn_date between '$fdate' and '$tdate' group by c.item_name");
			//$results1 = $query->result_array();
		    
		    foreach($query->result_array() as $item1)
		    {
				$category_name = $item1['category_name'];
		        $item_name = $item1['item_name'];
		        $uom = $item1['uom'];
				$start = $month = strtotime($from_date);
				$end = strtotime($to_date);
				//echo $month;
				//echo $end;
				$stmonth=date('Y-m-d', strtotime($start));
				//echo $stmonth;
				unset($items);
				while($month <= $end)
				{
					//echo "sdf";
					//$iname=$data['item_name'];
					$smonth=date('Y-m-d', $month); 
					//echo $smonth;
					$lmonth=date('Y-m-t', $month); 
					//echo $lmonth;
					$q=$this->db->query("SELECT c.category_id,p.category_name,c.branchname,c.item_name,c.uom,c.itm_id,AVG(grn_price+((grn_price*grn_tax)/100)) as grn_price FROM grn_price c inner join add_category p on c.category_id=p.category_id  WHERE c.item_name='$item_name' and  c.branchname='$branch' and c.grn_date between '$smonth' and '$lmonth'");
					
					foreach($q->result_array() as $row2){
						$items[] = array(						
						'smonth'=>$smonth,
						'lmonth'=>$lmonth,
						'grn_price' => round($row2['grn_price'],2)
						);
						
					}
					PHP_EOL;
				    $month = strtotime("+1 month", $month);
				}
				$json[] = array(
		'category_name' => $category_name,
		'item_name' => $item_name,
		'uom' => $uom,
		'items' => $items
		);
			}
			$emparray = array(
	'products' => $json
	);
	//echo json_encode($emparray);
	file_put_contents('item_list3.json', json_encode($emparray));
		/*if ($branch == "") {
			$data['date_range_error_message'] = "All feilds are required";
			} else {
			$result = $this->report_model->searchCostVolume($data1);
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}*/
	    $data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		//echo json_encode($result);
		//file_put_contents('item_list3.json', json_encode($emparray));
		$this->load->view('ExcelReport/avg_item_cost',$data);
	}
	
	public function searchCategoryCostVolume()
	{
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch_name'] = $_SESSION['branch_name'];
		$from_date = $this->input->post('fmonth');
		$to_date = $this->input->post('tmonth');
		$branch = $this->input->post('branch');

		$fdate=date('Y-m-d', strtotime($from_date));
		$tdate=date('Y-m-d', strtotime($to_date));
		$query = $this->db->query("select c.category_id,p.category_name,c.branchname,c.grn_price,c.item_name,c.uom,c.itm_id from grn_price c inner join add_category p on c.category_id=p.category_id where c.branchname='$branch' and grn_date between '$fdate' and '$tdate' group by p.category_name");
			//$results1 = $query->result_array();
		    
		    foreach($query->result_array() as $item1)
		    {
				$category_name = $item1['category_name'];
		        //$item_name = $item1['item_name'];
		        //$uom = $item1['uom'];
				$start = $month = strtotime($from_date);
				$end = strtotime($to_date);
				//echo $month;
				//echo $end;
				$stmonth=date('Y-m-d', strtotime($start));
				//echo $stmonth;
				unset($items);
				while($month <= $end)
				{
					//echo "sdf";
					//$iname=$data['item_name'];
					$smonth=date('Y-m-d', $month); 
					//echo $smonth;
					$lmonth=date('Y-m-t', $month); 
					//echo $lmonth;
					$q=$this->db->query("SELECT c.category_id,p.category_name,c.branchname,c.item_name,c.uom,c.itm_id,AVG(grn_price+((grn_price*grn_tax)/100)) as grn_price FROM grn_price c inner join add_category p on c.category_id=p.category_id  WHERE p.category_name='$category_name' and  c.branchname='$branch' and c.grn_date between '$smonth' and '$lmonth'");
					
					foreach($q->result_array() as $row2){
						$items[] = array(						
						'smonth'=>$smonth,
						'lmonth'=>$lmonth,
						'grn_price' => round($row2['grn_price'],2)
						);
						
					}
					PHP_EOL;
				    $month = strtotime("+1 month", $month);
				}
				$json[] = array(
		'category_name' => $category_name,
		'items' => $items
		);
			}
			$emparray = array(
	'products' => $json
	);
	file_put_contents('avgcat_list.json', json_encode($emparray));
		/*if ($branch == "") {
			$data['date_range_error_message'] = "All feilds are required";
			} else {
			$result = $this->report_model->searchCostVolume($data1);
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}*/
	    $data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		//echo json_encode($result);
		//file_put_contents('item_list3.json', json_encode($emparray));
		$this->load->view('ExcelReport/avg_cat_cost',$data);
	}
	
	public function searchMonthQtyVolume()
	{
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch_name'] = $_SESSION['branch_name'];
		$from_date = $this->input->post('fmonth');
		$to_date = $this->input->post('tmonth');
		$branch = $this->input->post('branch');

		$fdate=date('Y-m-d', strtotime($from_date));
		$tdate=date('Y-m-d', strtotime($to_date));
		$query = $this->db->query("select c.category_id,p.category_name,c.branchname,c.grn_price,c.grn_date,c.item_name,c.uom,c.itm_id, a.grn_grand from grn_price c inner join purchase_order_grn a on c.purchase_grn_id = a.purchase_grn_id
             inner join add_category p on c.category_id=p.category_id
            where c.branchname='$branch' and grn_date between '$fdate' and '$tdate' group by c.item_name");
		
		
			//$results1 = $query->result_array();
		    
		    foreach($query->result_array() as $item1)
		    {
				$category_name = $item1['category_name'];
		        $item_name = $item1['item_name'];
		        //$uom = $item1['uom'];
				$start = $month = strtotime($from_date);
				$end = strtotime($to_date);
				//echo $month;
				//echo $end;
				$stmonth=date('Y-m-d', strtotime($start));
				//echo $stmonth;
				unset($items);
				while($month <= $end)
				{
					//echo "sdf";
					//$iname=$data['item_name'];
					$smonth=date('Y-m-d', $month); 
					//echo $smonth;
					$lmonth=date('Y-m-t', $month); 
					//echo $lmonth;
					
					
					$q=$this->db->query("select c.category_id,c.branchname,c.grn_price,c.grn_date,c.item_name,c.uom,c.itm_id,sum(a.grn_item_qty-a.rejected_qty) as qty, sum(a.grn_grand) as grn_grand  from grn_price c inner join purchase_order_grn a on c.purchase_grn_id = a.purchase_grn_id
             inner join add_category p on c.category_id=p.category_id
            where c.item_name='$item_name' and c.branchname='$branch' and c.grn_date between '$smonth' and '$lmonth'");
					
					foreach($q->result_array() as $row2){
					    if($row2['qty'] == ''){
					        $qty ='0';
					    }else{
					        $qty=$row2['qty'];
					    }
						$items[] = array(						
						'smonth'=>$smonth,
						'lmonth'=>$lmonth,
						'qty'=>$qty,
						'grn_grand' => number_format($row2['grn_grand'],2, '.', '')
						);
						
					}
					PHP_EOL;
				    $month = strtotime("+1 month", $month);
				}
				$json[] = array(
		'category_name' => $category_name,
		'item_name'=>$item_name,
		'items' => $items
		);
			}
			$emparray = array(
	'products' => $json
	);
	file_put_contents('monthQtyVolume_list.json', json_encode($emparray));
		/*if ($branch == "") {
			$data['date_range_error_message'] = "All feilds are required";
			} else {
			$result = $this->report_model->searchCostVolume($data1);
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}*/
	    $data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		//echo json_encode($result);
		//file_put_contents('item_list3.json', json_encode($emparray));
		$this->load->view('ExcelReport/month_qty_volume',$data);
	}
	
	public function dailyOperation()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_ctrbranch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$data['pcat'] = $this->report_model->getPcategory();
		$this->load->view('ExcelReport/daily_operation',$data);
	}

	public function searchdailyOperation()
	{
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch_name'] = $_SESSION['branch_name'];
		$from_date = $this->input->post('fmonth');
		$to_date = $this->input->post('tmonth');
		$branch = $this->input->post('branch');
		$pcat = $this->input->post('pcategory');
		
		$data1 = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'branch' =>$branch,
		'pcat' =>$pcat
		);
		if ($branch == "") {
			$data['date_range_error_message'] = "All feilds are required";
			} else {
			$result = $this->report_model->searchDailyOperation($data1);
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
	    $data['branch'] = $this->user_model->get_ctrbranch();
	    $data['pcat'] = $this->report_model->getPcategory();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/daily_operation',$data);
	}
	
	public function dailyOperationCtr()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_ctrbranch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$data['pcat'] = $this->report_model->getPcategory();
		$this->load->view('ExcelReport/daily_operationctr',$data);
	}

	public function searchdailyOperationCtr()
	{
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch_name'] = $_SESSION['branch_name'];
		$from_date = $this->input->post('fmonth');
		$to_date = $this->input->post('tmonth');
		$branch = $this->input->post('branch');
		$pcat = $this->input->post('pcategory');
		
		$data1 = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'branch' =>$branch,
		'pcat' =>$pcat
		);
		if ($branch == "") {
			$data['date_range_error_message'] = "All feilds are required";
			} else {
			$result = $this->report_model->searchDailyOperation($data1);
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
	    $data['branch'] = $this->user_model->get_ctrbranch();
	    $data['pcat'] = $this->report_model->getPcategory();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/daily_operationctr',$data);
	}
	
	public function monthlyCosting()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_ctrbranch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$data['pcat'] = $this->report_model->getAllPcategory();
		$this->load->view('ExcelReport/monthly_costing',$data);
	}
	
	public function rawmaterialConsumption()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_ktnbranch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$data['pcat'] = $this->report_model->getRawCategory();
		$this->load->view('ExcelReport/raw_consumption',$data);
	}
	
	public function searchRawConsumption()
	{
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch_name'] = $_SESSION['branch_name'];
		$from_date = $this->input->post('fmonth');
		$to_date = $this->input->post('tmonth');
		$branch = $this->input->post('branch');
		
		$data1 = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'branch' =>$branch
		);
		if ($branch == "") {
			$data['date_range_error_message'] = "All feilds are required";
			} else {
			$result = $this->report_model->searchRawConsumption($data1);
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
		$data['rcconsumption'] = $this->report_model->searchRecipeConsumption($data1);
	    $data['branch'] = $this->user_model->get_ktnbranch();
	    $data['pcat'] = $this->report_model->getRawCategory();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/raw_consumption',$data);
	}
	
	public function consumptionAnalyze($fmonth,$tmonth,$branch_id)
	{
	    $user_id = $_SESSION['user_id'];
		//$branch_id = $_SESSION['branch_id'];
		$data1 = array(
    		'from_date' => $fmonth,
    		'to_date' => $tmonth,
    		'branch' =>$branch_id
		);
		$data['pcons'] = $this->report_model->getPconsumption($data1);
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/raw_consumption_analyze',$data);
	}
	
	public function rawconsumptionAnalyze($fmonth,$tmonth,$branch_id)
	{
	    $user_id = $_SESSION['user_id'];
		//$branch_id = $_SESSION['branch_id'];
		$data1 = array(
    		'from_date' => $fmonth,
    		'to_date' => $tmonth,
    		'branch' =>$branch_id
		);
		$data['rcons'] = $this->report_model->searchRawConsumption($data1);
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/raw_consumption_analyze1',$data);
	}
	
	public function dailyOperationtest()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_ctrbranch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$data['pcat'] = $this->report_model->getPcategory();
		$this->load->view('ExcelReport/daily_operationtest',$data);
	}

	public function searchdailyOperationtest()
	{
	    $db2 = $this->load->database('otherdb', TRUE);
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch_name'] = $_SESSION['branch_name'];
		$from_date = $this->input->post('fmonth');
		$to_date = $this->input->post('tmonth');
		$branch = $this->input->post('branch');
		$bcode = $this->input->post('bcode');
		//$pcat = $this->input->post('pcategory');
		
		$fdate = date('Y-m-d',strtotime($from_date));
		$tdate = date('Y-m-d',strtotime($to_date));
		global $pcat1;
		
		
	/*	$data1 = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'branch' =>$branch,
		'bcode' =>$bcode,
		'pcat' =>$pcat
		);
		$this->report_model->searchDailyOperationtest($data1); */
		
	    //if(!empty($pcat))
	    //{
		$pcat1 = '1,2,3,4,5,6,7';//1,2,3,4,5,6,7implode(',',$pcat);
	    //}
	    $query1 = $this->db->query("SELECT pr_cat_id,pr_cat_name from product_category where pr_cat_id in ($pcat1) order by pr_cat_name");
	    foreach($query1->result() as $pr_cat)
	    {
	        $prod_cat = $pr_cat->pr_cat_name;
			$pr_cat_id = $pr_cat->pr_cat_id;
	       
	        //$results1 = $this->db->query("SELECT R.branchname,R.rname as item_name,R.recipe_no,R.product_no,K.transaction_date as start_date,IFNULL(K.opening_stock,0) as opening_stock,IFNULL(K.inward,0) as inward,IFNULL(K.rinward,0) as rinward,IFNULL((K.inward+K.rinward),0) as tinward,IFNULL(K.Transfer_in,0) as Transfer_in,IFNULL(K.Transfer_out,0) as Transfer_out,IFNULL(K.Wastage,0) as Wastage,R.pr_cat_id as category_id,IFNULL((K.opening_stock+K.inward+K.rinward+K.Transfer_in-K.Transfer_out-K.Wastage),0) as difference,K.opening_stock as opstk from(select * from recipe where branch_id='$branch' and pr_cat_id='$pr_cat_id'  and status='Enabled' and product_no!='') R left join (SELECT item_id,opening_stock,item_name,SUM(CASE WHEN trans_type = 'PrInward' THEN in_qty ELSE 0 END) AS inward,SUM(CASE WHEN trans_type = 'Inward' THEN in_qty ELSE 0 END) AS rinward,SUM(CASE WHEN trans_type = 'transPrInward' THEN in_qty ELSE 0 END) AS Transfer_in,SUM(CASE WHEN trans_type = 'transPrIssue' THEN out_qty ELSE 0 END) AS Transfer_out,SUM(CASE WHEN trans_ref_no like 'DMG%' THEN out_qty ELSE 0 END) as Wastage,branchname,recipe_no,transaction_date,category_id FROM `stock_detail` WHERE ((branch_id='$branch' and category_id='$pr_cat_id' and recipe_no!='') and (transaction_date between '$fdate' and '$tdate')) GROUP BY recipe_no)K on R.recipe_no=K.recipe_no");
	        $results1 = $this->db->query("SELECT R.branchname,R.rname as item_name,R.recipe_no,R.product_no,K.transaction_date as start_date,IFNULL(K.opening_stock,0) as opening_stock,IFNULL(K.inward,0) as inward,IFNULL(K.rinward,0) as rinward,IFNULL((K.inward+K.rinward),0) as tinward,IFNULL(K.Transfer_in,0) as Transfer_in,IFNULL(K.Transfer_out,0) as Transfer_out,IFNULL(K.Wastage,0) as Wastage,R.pr_cat_id as category_id,IFNULL((K.opening_stock+K.inward+K.rinward+K.Transfer_in-K.Transfer_out-K.Wastage),0) as difference,K.opening_stock as opstk from(select * from recipe where branch_id='$branch' and pr_cat_id='$pr_cat_id'  and status='Enabled' and product_no!='') R left join (SELECT item_id,opening_stock,item_name,SUM(CASE WHEN trans_type = 'PrInward' THEN in_qty ELSE 0 END) AS inward,SUM(CASE WHEN trans_type = 'Inward' THEN in_qty ELSE 0 END) AS rinward,SUM(CASE WHEN trans_type = 'transPrInward' THEN in_qty ELSE 0 END) AS Transfer_in,SUM(CASE WHEN trans_type = 'transPrIssue' THEN out_qty ELSE 0 END) AS Transfer_out,SUM(CASE WHEN trans_ref_no like 'DMG%' THEN out_qty ELSE 0 END) as Wastage,branchname,recipe_no,transaction_date,category_id FROM `stock_detail` WHERE ((branch_id='$branch' and category_id='$pr_cat_id' and recipe_no!='') and (transaction_date between '$fdate' and '$tdate') ) GROUP BY recipe_no)K on R.recipe_no=K.recipe_no");
	        // order by recipe_no asc limit 10
	        unset($product);
	        
	        //total
            $opttl = 0;
            $inttl = 0;
            $trinttl = 0;
            $troutttl = 0;
            $slttl = 0;
            $wstttl = 0;
            $clsttl = 0;
            $diffttl = 0;
            
            $op_stk = 0;
            $cls_stk = 0;
            $diff1 = 0;
            $s_qty = 0;
	        
	        foreach($results1->result() as $prod)
	        {
	            $opstk = $prod->opstk;
	            $rc_no = $prod->recipe_no;
	            $pr_no = $prod->product_no;
	            //echo $rc_no; echo '-test-';echo $pr_no;echo '--';
	            //echo $fdate;
	            if($opstk == '')
	            {
    	            $rcq = $this->db->query("SELECT closing_stock FROM stock_detail WHERE transaction_date<'$fdate' and recipe_no='$rc_no' and branch_id='$branch' and recipe_no!='' ORDER BY stock_id DESC limit 1");
    	            $rcrow = $rcq->row_array();
    	            //echo $rcrow['closing_stock'];
    	            if (isset($rcrow['closing_stock'])) {
                      //echo $rcrow['closing_stock'];  // This will not trigger an error
                       $closing_stock=$rcrow['closing_stock'];
                    } else {
                         $closing_stock='0';
                      //echo 'index does not exist';
                    }
    	            /*if ($rcrow['closing_stock']=='') {
                     // Throw some kind of exception
                        $closing_stock='0';
                        echo $closing_stock;echo '-gfd-';
                    }
                    else{
    	                $closing_stock=$rcrow['closing_stock'];
    	                echo $closing_stock;echo '-dfsdgsdg-';
    	            }*/
			        $opttl = $opttl + $closing_stock;
			        $op_stk = $closing_stock;
			        $clsttl = $clsttl + $closing_stock;
			        $cls_stk = $closing_stock;
			        $diff1 = $op_stk + $prod->tinward + $prod->Transfer_in - $prod->Transfer_out - $prod->Wastage - $cls_stk;
	            }
	            else
	            {
	                $rcq = $this->db->query("SELECT closing_stock FROM stock_detail WHERE transaction_date between '$fdate' and '$tdate' and recipe_no='$rc_no' and branch_id='$branch' and recipe_no!='' ORDER BY stock_id DESC limit 1");
    	            $rcrow = $rcq->row_array();
    	            $clsttl = $clsttl + $rcrow['closing_stock'];
	                $cls_stk = $rcrow['closing_stock'];
    	            
    	         
	               
	                $opttl = $opttl + $prod->opening_stock;
	                $op_stk = $prod->opening_stock;
	                
	                $diff1 = $op_stk + $prod->tinward + $prod->Transfer_in - $prod->Transfer_out - $prod->Wastage - $cls_stk;
	            }
	            $inttl = $inttl + $prod->tinward;
                $trinttl = $trinttl + $prod->Transfer_in;
                $troutttl = $troutttl + $prod->Transfer_out;
                $wstttl = $wstttl + $prod->Wastage;
                
             
                $bq = $db2->query("SELECT branch_title as branch,branch_slno from branch_creation where status ='Active' order by branch_slno ASC");
				$branches = $bq->result_array();
				$count = 1;
				foreach($branches as $br)
				{
				    $branch1 = $br['branch'];
				    $slno = $br['branch_slno'];
				    
				    if($bcode == $branch1)
					{
					    $spr_table = 'product_billdetail'.$slno;
    					$sbp_table = 'billed_product'.$slno;
    					
    					    $q1 = $db2->query("SELECT B.`product_name`,sum(B.`quantity`) as sales_qty FROM $spr_table A inner join $sbp_table B on A.bill_id=B.bill_id  WHERE ((A.invoice_date BETWEEN '$fdate' and '$tdate') and (B.`product_no`='$pr_no' and A.status='closed'))");
    					    $slrow = $q1->row_array();
    					    $iname = $slrow['product_name'];
    					    $s_qty = $slrow['sales_qty'];
    					
    				}
				
					$count++;
				}
				
		
    				$tl_qty = $s_qty; // $olrow['ol_qty'] + $icrow['ic_qty'] + 
    				$diff = $diff1 - $tl_qty;
				
				
        	    //total
        	    $slttl = $slttl + $tl_qty;
        	    $diffttl = $diffttl + $diff;
        	    
        	    $product[] = array(
        	        'pr_cat_name' => $prod_cat,
        			'item_name' => $prod->item_name,
        			'opening_stock' => $op_stk,
        			'tinward' => $prod->tinward,
        			//'Transfer_in' => $prod->Transfer_in,
        			//'Transfer_out' => $prod->Transfer_out,
        			'tl_qty' => $tl_qty,
        			'Wastage' => $prod->Wastage,
        			'closing_stock' => $cls_stk,
        			'diff' => $diff
				);
	        }
	        if(isset($product))
	        {
    	        $json[] = array(
    	            'prod_cat' => $prod_cat,
        			'pr_cat_id' => $pr_cat_id,
        			'opttl' => $opttl,
        			'inttl' => $inttl,
        			//'trinttl' => $trinttl,
        			//'troutttl' => $troutttl,
        			'slttl' => $slttl,
        			'wstttl' => $wstttl,
        			'clsttl' => $clsttl,
        			'diffttl' => $diffttl,
        			'product' => $product
    			);
    	    }
	    }
	    $sm_data = array(
		'sim_data' => $json
		);
		
		//echo json_encode($sm_data);
		file_put_contents('sim_data.json', json_encode($sm_data));
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_ctrbranch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$data['pcat'] = $this->report_model->getPcategory();
		$this->load->view('ExcelReport/daily_operationtest',$data);
	}
	
	public function esh_Report(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_ESHbranch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('Eshanya/Report/eshReport',$data);
	}
	
	public function exportEshReport(){
		$branchname = $_SESSION['branch_name'];
		$branch_id = $_SESSION['branch_id'];
		$curdate=date('d-m-Y');
		$fromdate = $this->input->post('from_date');
		$todate = $this->input->post('to_date');
		$report_type = $this->input->post('report_type');
		$from_date = date('Y-m-d', strtotime($fromdate));
		$to_date = date('Y-m-d', strtotime($todate));
		$branch = $this->input->post('branch');
		$bname="('ESH','ESH-TDR','ESH-IND','ESH-CHN','ESH-PNT','ESH-PK','ESH-HK','ESH-SVC','ESH-KTN','ESH-STR2')";	
        if($report_type == 'Purchase Order')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Purchase-Order".$curdate.".csv";
			
			$query = "SELECT P.purchase_id,P.purchase_no,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date,P.purchase_time as time,P.venders_name as vendor_name,P.branchname,PO.purchase_order_id,PO.item_name,PO.unit_measurement as uom,PO.required_qty as qty,PO.item_price,PO.po_tax,PO.po_tax_amount,PO.actual_amount,PO.total_amount,PO.item_id,IT.itm_id as item_code,PO.cat_id from purchase_order P inner join purchase_order_item PO on P.purchase_id=PO.purchase_id inner join item_creation IT on PO.item_id=IT.item_id where (P.purchase_date between '$from_date' and '$to_date') and P.branchname in  $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'GRN')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "GRN".$curdate.".csv";
			
			$query = "SELECT G.gnr_id as grn_id,G.grn_no,G.invoice_no,G.vendor_name,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,G.grn_time,G.branchname,G.user_name,G.purchase_id,PG.purchase_grn_id,PI.item_name,PI.unit_measurement as uom,PG.grn_item_qty,PG.grn_item_price,PG.grn_tax,PG.grn_tax_amount,PG.grn_total_amount,PG.grn_grand,PG.purchase_order_id,PI.item_id,IT.itm_id as item_code,PI.cat_id from gnr_creation G inner join purchase_order_grn PG on G.gnr_id=PG.gnr_id inner join purchase_order_item PI on PG.purchase_order_id=PI.purchase_order_id inner join item_creation IT on PI.item_id=IT.item_id where (G.grn_date between '$from_date' and '$to_date') and G.branchname in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Rejection')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Rejection".$curdate.".csv";
			
			$query = "SELECT R.rejection_no,DATE_FORMAT(R.rejection_date, '%d-%m-%Y') as rejection_date,R.rtime as rjn_time,G.grn_no,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,R.branchname,I.item_name,I.uom,RI.rejection_qty as rejected_qty,RI.rejection_price as price,RI.rejection_tax as tax,RI.rejection_tax_amount as tax_amt,round((RI.rejection_qty*RI.rejection_price),2) as rejection_amt,RI.rejection_total_amount as total_amt,R.gnr_id as gnr_id,RI.item_id,RI.itm_id as item_code,RI.category_id FROM gnr_creation G inner join rejection_creation R on G.gnr_id=R.gnr_id inner join purchase_order_rejection RI on R.rejection_id=RI.rejection_id inner join item_creation I on I.item_id=RI.item_id WHERE (R.rejection_date between '$from_date' and '$to_date')  and R.branchname in $bname and RI.rejection_qty!=0";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Godown Transfer')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Godown-Transfer".$curdate.".csv";
			
			$query = "SELECT S.stk_tr_no,DATE_FORMAT(S.stk_tr_date, '%d-%m-%Y') as stk_tr_date,S.tr_time,S.frm_branchname as frm_branch,S.to_branchname as to_branch,SI.item_name,SI.uom,SI.quantity as tr_quantity,SI.price,SI.total_amount,SI.item_id,SI.itm_id as item_code,SI.category_id FROM stock_transfer S inner join stock_transfer_item SI on S.stk_tr_id=SI.stk_tr_id WHERE (S.stk_tr_date between '$from_date' and '$to_date')  and S.frm_branchname in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Damage')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Damage".$curdate.".csv";
			
			$query = "SELECT D.damage_id,D.damage_no,DATE_FORMAT(D.damage_date, '%d-%m-%Y') as damage_date,D.dtime as time,D.branchname,DI.damage_order_id,DI.damage_item_name,DI.damage_uom as uom,DI.damage_qty,DI.item_id,IT.itm_id as item_code,DI.category_id from damage_order_creation D inner join damage_order_item DI on D.damage_id=DI.damage_id inner join item_creation IT on DI.item_id=IT.item_id where D.return_type IS NULL and (D.damage_date between '$from_date' and '$to_date')  and D.branchname in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Direct Issue')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Direct-Issue".$curdate.".csv";
			
			$query = "SELECT i.issue_id,i.issue_no,DATE_FORMAT(i.issue_date, '%d-%m-%Y') as issue_date,i.issue_time,i.outlet_name,i.total,i.branchname,it.indent_item_name as item_name,it.indent_uom as uom,it.indent_qty as issued_qty,it.issues_price as rate,it.tax,it.tax_amount,it.basic_price as amount,it.total_amount as total_price,it.item_id,IM.itm_id as item_code,it.cat_id from issues_order_creation i inner join issues_order_item it on i.issue_id=it.issue_id inner join item_creation IM on it.item_id=IM.item_id where i.issue_date between '$from_date' and '$to_date' and i.request_id='0' and i.branchname in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Indent')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Indent".$curdate.".csv";
			
			$query = "SELECT m.request_id,m.mat_req_no,DATE_FORMAT(m.request_date, '%d-%m-%Y') as request_date,m.request_time,m.store,m.branchname,mr.request_item_id,mr.item_name,mr.uom,mr.required_qty as requested_qty,mr.recieved_qty as issued_qty,mr.receipt_qty,mr.item_id,IT.itm_id as item_code,mr.category_id from material_request m inner join material_request_item mr on m.request_id=mr.request_id inner join item_creation IT on mr.item_id=IT.item_id where (m.request_date between '$from_date' and '$to_date') and m.branchname in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Issue')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Issue".$curdate.".csv";
			
			$query = "SELECT i.issue_id,r.mat_req_no,i.issue_no,DATE_FORMAT(i.issue_date, '%d-%m-%Y') as issue_date,i.issue_time,i.outlet_name,i.total,i.branchname,i.request_id,i.user_name,it.indent_item_name as item_name,it.indent_uom as uom,it.indent_qty as issued_qty,it.issues_price as rate,it.tax,it.tax_amount,it.basic_price as amount,it.total_amount as total_price,it.request_item_id,it.item_id,IM.itm_id as item_code,mri.category_id from material_request r inner join issues_order_creation i on r.request_id=i.request_id inner join issues_order_item it on i.issue_id=it.issue_id inner join material_request_item mri on it.request_item_id=mri.request_item_id inner join item_creation IM on it.item_id=IM.item_id where (i.issue_date between '$from_date' and '$to_date') and i.branchname in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Inward".$curdate.".csv";
			
			$query = "SELECT MR.mat_req_no as request_no,DATE_FORMAT(MR.request_date, '%d-%m-%Y') as request_date,I.issue_no,DATE_FORMAT(I.issue_date, '%d-%m-%Y') as issue_date,R.req_rec_no,DATE_FORMAT(R.receipt_date, '%d-%m-%Y') as receipt_date,R.receipt_time,R.store,R.branchname,R.request_id,RP.receipt_item_id,mri.item_name,RP.quantity as received_qty,RP.req_rec_id,RP.requset_item_id,RP.item_id,IT.itm_id as item_code,mri.category_id from material_request MR inner join issues_order_creation I on MR.request_id=I.request_id inner join request_receipt R on I.issue_id=R.issue_id inner join request_receipt_item RP on R.req_rec_id=RP.req_rec_id inner join material_request_item mri on RP.requset_item_id=mri.request_item_id inner join item_creation IT on RP.item_id=IT.item_id where (R.receipt_date between '$from_date' and '$to_date') and R.branchname in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Issue-Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Issue-Inward-".$fromdate." to ".$todate.".csv";
			
		//	$query = "SELECT I.issue_id,MR.mat_req_no as request_no,I.issue_no,DATE_FORMAT(I.issue_date, '%d-%m-%Y') as issue_date,I.issue_time,I.outlet_name,I.branchname,I.request_id,I.user_name,IT.indent_item_name as item_name,IT.indent_uom as uom,IT.indent_qty as issued_qty,R.req_rec_no,DATE_FORMAT(R.receipt_date, '%d-%m-%Y') as receipt_date,R.receipt_time,RP.quantity as received_qty,RP.category_id from material_request MR inner join issues_order_creation I on MR.request_id=I.request_id inner join request_receipt R on I.issue_id=R.issue_id inner join request_receipt_item RP on R.req_rec_id=RP.req_rec_id inner join issues_order_item IT on RP.issue_order_id=IT.issue_order_id where R.receipt_date between '$from_date' and '$to_date' order by I.issue_no,IT.indent_item_name asc";
			$query = "SELECT I.issue_id,MR.mat_req_no as request_no,I.issue_no,DATE_FORMAT(I.issue_date, '%d-%m-%Y') as issue_date,I.issue_time,I.outlet_name,I.branchname,I.request_id,I.user_name,IT.indent_item_name as item_name,IT.indent_uom as uom,IT.indent_qty as issued_qty,R.req_rec_no,DATE_FORMAT(R.receipt_date, '%d-%m-%Y') as receipt_date,R.receipt_time,RP.quantity as received_qty,IT.cat_id from issues_order_creation I left join request_receipt R on I.issue_id=R.issue_id left join material_request MR on MR.request_id=I.request_id left join issues_order_item IT on I.issue_id=IT.issue_id left join request_receipt_item RP on RP.issue_order_id=IT.issue_order_id and RP.req_rec_id=R.req_rec_id where (I.issue_date between '$from_date' and '$to_date') and I.branchname in $bname and I.request_id!='0' order by I.issue_no,IT.indent_item_name asc";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Pride Inward')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Pride-Inward".$curdate.".csv";
			
			$query = "SELECT r.req_rec_no,r.receipt_time,r.store,DATE_FORMAT(r.receipt_date, '%d-%m-%Y') as receipt_date,r.branchname,r.request_id,rp.receipt_item_id,mri.item_name,mri.uom,rp.quantity as received_qty,rp.rc_item_price as price,rp.rc_tax as tax,rp.rc_tax_amount as tax_amount,rp.rc_total_price as total,rp.rc_total_amount as total_value,rp.req_rec_id,rp.requset_item_id,rp.item_id,IT.itm_id as item_code,rp.category_id from request_receipt r inner join request_receipt_item rp on r.req_rec_id=rp.req_rec_id inner join material_request_item mri on rp.requset_item_id=mri.request_item_id inner join item_creation IT on rp.item_id=IT.item_id where (r.receipt_date between '$from_date' and '$to_date') and r.branchname in $bname and r.store='Pride Bakers'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Damage/Wastage')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Damage/Wastage".$curdate.".csv";
			
			$query = "SELECT D.damage_id,D.damage_no,DATE_FORMAT(D.damage_date, '%d-%m-%Y') as damage_date,D.dtime as time,D.return_type as type,D.branchname,DI.damage_order_id,DI.item_type,DI.damage_item_name,DI.damage_uom as uom,DI.damage_qty,DI.item_id,DI.category_id from damage_order_creation D inner join damage_order_item DI on D.damage_id=DI.damage_id where D.return_type IS NOT NULL and (D.damage_date between '$from_date' and '$to_date') and D.branchname in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Month End Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Month-End-Stock".$curdate.".csv";
			
			$query = "SELECT S.branchname as Branch,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as Date,S.stk_time as time,S.stock_no as Stock_Update_No,ST.item_type,ST.item_name as Product_Name,ST.uom as UOM,round(ST.rate,2) as rate,ST.present_stk as System_Stock,ST.closing_stk as Physical_Stock,(ST.present_stk-ST.closing_stk) as Difference,ST.cat_id as Cat_ID from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id where (S.stk_date between '$from_date' and '$to_date') and S.branchname in $bname and S.stock_no like 'S%'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Daily Stock Update')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Daily-Stock-Update".$curdate.".csv";
			
			$query = "SELECT S.stck_id,S.stock_no,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as stk_date,S.stk_time as time,S.branchname,ST.stk_item_id,ST.item_type,ST.item_name,ST.uom,round(ST.rate,2) as rate,ST.present_stk as system_stock,ST.closing_stk,ST.item_id,ST.cat_id from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id where (S.stk_date between '$from_date' and '$to_date') and S.branchname in $bname and S.stock_no like 'D%'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Production')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Production".$curdate.".csv";
			
			$query = "SELECT b.billno as production_id,b.bill_id as ref_no,bp.pr_cat_id,DATE_FORMAT(b.orderdate, '%d-%m-%Y') as orderdate,b.ordertime as time,b.chef,IF(b.outlet='Cold Kitchen','CW4-Cold Kitchen',b.outlet) as outlet,bp.pname as product_name,bp.uom,bp.yeild as projected_yeild,bp.actual_yeild from billdetails b inner join bom_product bp on b.billno=bp.billno where (b.orderdate between '$from_date' and '$to_date') and b.outlet in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material".$curdate.".csv";
			
			$query = "SELECT I.itm_id as item_id,I.item_name,I.category_id,C.category_name,I.uom,DATE_FORMAT(I.creation_date, '%d-%m-%Y') as creation_date FROM item I inner join category C on C.category_id=I.category_id where I.active_status='active' order by I.itm_id desc";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material-Stock".$curdate.".csv";
			
			$query = "select c.category_name,i.item_name,i.uom,i.item_price,i.item_tax,i.avg_price,i.vendor_name,st.closing_stock,(i.item_price*st.closing_stock) as total from (select * from category) c inner join (select item_name,uom,item_price,item_tax,avg_price,vendor_name,category_id from item_creation where branchname='$branch' and status='Enabled') i on c.category_id=i.category_id inner join (select * from stock_detail) st on i.item_name=st.item_name inner join (select item_name,transaction_date,closing_stock,MAX(last_modified) as maxdate from stock_detail where branchname='$branch' and transaction_date <= '$to_date' group by item_name) stk on st.item_name=stk.item_name and st.last_modified=stk.maxdate";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Product Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product-Stock".$curdate.".csv";
			
			$query = "select c.pr_cat_name as category,i.rname as product_name,i.uom,i.recipe_price as price,st.closing_stock,(i.recipe_price*st.closing_stock) as total from (select * from product_category) c inner join (select recipe_no,rname,uom,recipe_price,pr_cat_id from recipe where branchname='$branch' and status='Enabled') i on c.pr_cat_id=i.pr_cat_id inner join (select * from stock_detail) st on i.recipe_no=st.recipe_no inner join (select item_name,recipe_no,transaction_date,closing_stock,MAX(last_modified) as maxdate from stock_detail where branchname='$branch' and transaction_date <= '$to_date' group by recipe_no) stk on st.recipe_no=stk.recipe_no and st.last_modified=stk.maxdate";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'CMS_Products')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "CMS_Products.csv";
			
			$query = "SELECT R.rname as product_name,R.uom,R.cw_price as price,R.cw_tax as tax,R.cw_tax_amount as tax_amount,R.cw_total_price as total_price,C.pr_cat_id as cat_id,C.pr_cat_name as cat_name from recipe R inner join product_category C on R.pr_cat_id=C.pr_cat_id where R.cw_price!='0' and R.branchname='CMS'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material Invoice')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material-Invoice.csv";
			
			$query = "SELECT PI.invoice_id as inv_id,PI.invoice_no,DATE_FORMAT(PI.invoice_date, '%d-%m-%Y') as invoice_date,PI.invoice_time as invoice_time,PI.outlet_name,PI.branchname,PIT.item_name as item_name,PIT.uom as uom,PIT.required_qty as qty,PIT.item_price as rate,PIT.tax,PIT.tax_amount,(PIT.item_price*PIT.required_qty) as amount,PIT.total_amount as total_price,PIT.invoice_id as invoice_id,PIT.category_id from invoice PI inner join invoice_item PIT on PI.invoice_id=PIT.invoice_id where (PI.invoice_date between '$from_date' and '$to_date') and PI.branchname in $bname";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Recipe')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Recipe-Report.csv";
			
			$query = "SELECT r.`recipe_id`, r.`rname`, r.`yeild`, r.`uom`, r.`pr_cat_id`, r.`branchname`,ri.`rc_item_id`, ri.`item_name`, ri.uom as uoms,ri.`qty`, ri.`price` as basicPrice, ri.`total` as totalA, ri.`item_id` as item_ids, ri.`category_id` as category_ids FROM `eshRecipe` r inner join eshRecipe_item ri on r.recipe_id=ri.recipe_id where r.branch_id='86' UNION ALL SELECT r.`recipe_id`,r.`rname`,r.`yeild`, r.`uom`,r.`pr_cat_id`, r.`branchname`,ri.`sub_rc_item_id`, ri.`item_name`,ri.uom as uoms,ri.`qty`, ri.`price` as basicPrice, ri.`total` as totalA, ri.`sub_recipe_id` as item_ids, ri.`pr_cat_id` as category_ids FROM `eshRecipe` r inner join eshSub_recipe_item ri on r.recipe_id=ri.recipe_id where r.branch_id='86'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
	}
	
	public function product_hsnReport(){
		//$this->data['view_outlet'] = $this->super_model->outlet_list();
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		//$data['outlet'] = $this->issue_model->get_outletout();
		$data['outlet'] = $this->issue_model->get_outlets(); 
        $data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/hsnreport',$data);
	}
	
	
	public function search_hsnReport(){
	    $user_id = $_SESSION['user_id'];
		$from_date = $this->input->post('from_date');
		$to_date = $this->input->post('to_date');
		$outlet_name = $this->input->post('outlet_name');
		$product_name = $this->input->post('product_name');
		$data = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'outlet_name' => $outlet_name,
		'product_name' => $product_name
		);
		if ($from_date == "" && $to_date == "") {
			$data['date_range_error_message'] = "Both feilds are required";
			} else {
			$result = $this->report_model->search_HsnReport($data);
			
			
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
	    $data['outlet'] = $this->issue_model->get_outlets();
		$this->load->view('ExcelReport/hsnreport',$data);
	
	}
	
	public function itemReport(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$branch = $_SESSION['branch_name'];
		$sub_mod_id = $this->uri->segment(3);
		$data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$data['outlet'] = $this->issue_model->get_outletout();
		$data['branch'] = $this->issue_model->get_outlet_by_branch($branch,$sub_mod_id);
		$this->load->view('ExcelReport/itemReport',$data);
	}
	
	public function exportItemReport(){
		$branchname = $_SESSION['branch_name'];
		$branch_id = $_SESSION['branch_id'];
		$curdate=date('d-m-Y');
		$fromdate = $this->input->post('from_date');
		$todate = $this->input->post('to_date');
		$report_type = $this->input->post('report_type');
		$from_date = date('Y-m-d', strtotime($fromdate));
		$to_date = date('Y-m-d', strtotime($todate));
		$branch = $this->input->post('branch');
		$bname="('ESH','ESH-TDR','ESH-IND','ESH-CHN','ESH-PNT','ESH-PK','ESH-HK','ESH-SVC','ESH-KTN','ESH-STR2')";
		$br_id="('10','21','22','23','24','25','26','27','54','68')";
        if($report_type == 'GRN')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "GRN".$curdate.".csv";
			
			$query = "SELECT G.gnr_id as grn_id,G.grn_no,G.invoice_no,G.vendor_name,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,G.grn_time,G.branchname,G.user_name,PG.purchase_grn_id,PI.item_name,PI.unit_measurement as uom,PG.grn_item_qty,PG.grn_item_price,PG.grn_tax,PG.grn_tax_amount,PG.grn_total_amount,PG.grn_grand,PG.purchase_order_id,PI.item_id,IT.itm_id as item_code,PI.cat_id from gnr_creation G inner join purchase_order_grn PG on G.gnr_id=PG.gnr_id inner join item_creation IT on PI.item_id=PG.item_id where (G.grn_date between '$from_date' and '$to_date') and G.branchname='$branch'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Damage')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Damage".$curdate.".csv";
			
			$query = "SELECT D.damage_id,D.damage_no,DATE_FORMAT(D.damage_date, '%d-%m-%Y') as damage_date,D.dtime as time,D.branchname,DI.damage_order_id,DI.damage_item_name,DI.damage_uom as uom,DI.damage_qty,DI.item_id,IT.itm_id as item_code,DI.category_id from damage_order_creation D inner join damage_order_item DI on D.damage_id=DI.damage_id inner join item_creation IT on DI.item_id=IT.item_id where D.return_type IS NULL and (D.damage_date between '$from_date' and '$to_date') and D.branchname='$branch'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Direct Issue')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Direct-Issue".$curdate.".csv";
			
			$query = "SELECT i.issue_id,i.issue_no,DATE_FORMAT(i.issue_date, '%d-%m-%Y') as issue_date,i.issue_time,i.outlet_name,i.total,i.branchname,it.indent_item_name as item_name,it.indent_uom as uom,it.indent_qty as issued_qty,it.issues_price as rate,it.tax,it.tax_amount,it.basic_price as amount,it.total_amount as total_price,it.item_id,IM.itm_id as item_code,it.cat_id from issues_order_creation i inner join issues_order_item it on i.issue_id=it.issue_id inner join item_creation IM on it.item_id=IM.item_id where i.issue_date between '$from_date' and '$to_date' and i.branchname='$branch'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Month End Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Month-End-Stock".$curdate.".csv";
			
			$query = "SELECT S.branchname as Branch,DATE_FORMAT(S.stk_date, '%d-%m-%Y') as Date,S.stk_time as time,S.stock_no as Stock_Update_No,ST.item_type,ST.item_name as Product_Name,ST.uom as UOM,round(ST.rate,2) as rate,ST.present_stk as System_Stock,ST.closing_stk as Physical_Stock,(ST.present_stk-ST.closing_stk) as Difference,ST.cat_id as Cat_ID from stock_update S inner join stock_update_item ST on S.stck_id=ST.stck_id where (S.stk_date between '$from_date' and '$to_date') and S.branchname ='$branch' and S.stock_no like 'S%'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Production')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Production".$curdate.".csv";
			
			$query = "SELECT b.billno as production_id,b.bill_id as ref_no,bp.pr_cat_id,DATE_FORMAT(b.orderdate, '%d-%m-%Y') as orderdate,b.ordertime as time,b.chef,b.outle as outlet,bp.pname as product_name,bp.uom,bp.yeild as projected_yeild,bp.actual_yeild from billdetails b inner join bom_product bp on b.billno=bp.billno where (b.orderdate between '$from_date' and '$to_date') and b.outlet='$branch'";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material".$curdate.".csv";
			
			$query = "SELECT I.itm_id as item_id,I.item_name,I.category_id,C.category_name,I.uom,DATE_FORMAT(I.creation_date, '%d-%m-%Y') as creation_date FROM item I inner join category C on C.category_id=I.category_id where I.active_status='active' order by I.itm_id desc";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Raw Material Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Raw-Material-Stock".$curdate.".csv";
			
			$query = "select c.category_name,i.item_name,i.uom,i.item_price,i.item_tax,i.avg_price,i.vendor_name,st.closing_stock,(i.item_price*st.closing_stock) as total from (select * from category) c inner join (select item_name,uom,item_price,item_tax,avg_price,vendor_name,category_id from item_creation where branchname='$branch' and status='Enabled') i on c.category_id=i.category_id inner join (select * from stock_detail) st on i.item_name=st.item_name inner join (select item_name,transaction_date,closing_stock,MAX(last_modified) as maxdate from stock_detail where branchname='$branch' and transaction_date <= '$to_date' group by item_name) stk on st.item_name=stk.item_name and st.last_modified=stk.maxdate";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'Product Stock')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product-Stock".$curdate.".csv";
			
			$query = "select c.pr_cat_name as category,i.rname as product_name,i.uom,i.recipe_price as price,st.closing_stock,(i.recipe_price*st.closing_stock) as total from (select * from product_category) c inner join (select recipe_no,rname,uom,recipe_price,pr_cat_id from recipe where branchname='$branch' and status='Enabled') i on c.pr_cat_id=i.pr_cat_id inner join (select * from stock_detail) st on i.recipe_no=st.recipe_no inner join (select item_name,recipe_no,transaction_date,closing_stock,MAX(last_modified) as maxdate from stock_detail where branchname='$branch' and transaction_date <= '$to_date' group by recipe_no) stk on st.recipe_no=stk.recipe_no and st.last_modified=stk.maxdate";
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
	}
	public function productReport(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/productReport',$data);
	}
	
	public function user_IssueReport(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/issue_report',$data);
	}
	
	public function exportIssueReport(){
		$branchname = $_SESSION['branch_name'];
		$branch_id = $_SESSION['branch_id'];
		$curdate=date('d-m-Y');
		$fromdate = $this->input->post('from_date');
		$todate = $this->input->post('to_date');
		$report_type = $this->input->post('report_type');
		$f_date = date('Y-m-d', strtotime($fromdate));
		$t_date = date('Y-m-d', strtotime($todate));
		$outlet_name = $this->input->post('branch');
		$product_name = $this->input->post('item_name');
		$bname="('ESH','ESH-TDR','ESH-IND','ESH-CHN','ESH-PNT','ESH-PK','ESH-HK','ESH-SVC','ESH-KTN','ESH-STR2')";
		$br_id="('10','21','22','23','24','25','26','27','54','68')";
		
		if($report_type == 'Product Issue' && $outlet_name != 'All'){
				$filename = "Product_Issue.csv";
				$this->load->dbutil();
    			$this->load->helper('file');
    			$this->load->helper('download');
    			$delimiter = ",";
    			$newline = "\r\n";
			//	SELECT `issue_id`, `is_no`, `issue_no`, `in_no`, `invoice_no`, `vehicle_id`, `vehicle_name`, `issue_date`, `entry_date`, `issue_time`, `outlet`, `branchname`, `branch_id`, `request_id`, `issue_status`, `total_amount`, `user_name`, `tslot`, `invoice_type`, `cancel_date` FROM `product_issues_creation` WHERE 1
			//	SELECT `issue_order_id`, `issue_item_name`, `issue_uom`, `hsn_code`, `issued_qty`, `price`, `basic_price`, `tax`, `tax_amount`, `total_price`, `rec_issue_qty`, `issue_remarks`, `issue_id`, `request_item_id`, `recipe_id`, `recipe_no`, `pr_cat_id`, `rcv_status` FROM `product_issues_item` WHERE 1
				$query = "SELECT I.issue_id as issue_id,I.issue_no as issue_ref_no,I.vehicle_name,I.invoice_no,I.outlet,I.issue_date,I.entry_date,I.branchname as branch_name,TI.issue_item_name,TI.hsn_code,TI.issue_uom,TI.issued_qty,TI.price,TI.basic_price,TI.tax,TI.tax_amount,TI.total_price,TI.recipe_id,TI.pr_cat_id,TI.issue_remarks from product_issues_creation I inner join product_issues_item TI on I.issue_id=TI.issue_id where I.outlet='$outlet_name' and I.issue_date between '$f_date' AND '$t_date'";
				
			}
			else if($report_type == 'Product Issue' && $outlet_name == 'All'){
			    $this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
				$filename = "Product_Issue.csv";
				$query = "SELECT I.issue_id as issue_id,I.issue_no as issue_ref_no,I.vehicle_name,I.invoice_no,I.outlet,I.issue_date,I.entry_date,I.branchname as branch_name,TI.issue_item_name,TI.hsn_code,TI.issue_uom,TI.issued_qty,TI.price,TI.basic_price,TI.tax,TI.tax_amount,TI.total_price,TI.recipe_id,TI.pr_cat_id,TI.issue_remarks from product_issues_creation I inner join product_issues_item TI on I.issue_id=TI.issue_id where I.issue_date between '$f_date' AND '$t_date'";
				
			}
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
        /*if($report_type == 'product_tax')
		{
			$this->load->dbutil();
			$this->load->helper('file');
			$this->load->helper('download');
			$delimiter = ",";
			$newline = "\r\n";
			$filename = "Product_Tax".$curdate.".csv";
			
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}*/
		
	}
	
	public function user_TaxIssueReport(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/tax_report',$data);
	}
	
	public function search_issueReport(){
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		
		$from_date = $this->input->post('from_date');
		$to_date = $this->input->post('to_date');
		$report_type = 'product_tax'; //$this->input->post('report_type');
		$branch = $this->input->post('branch');
		$item_name = $this->input->post('item_name');
		$data = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'report_type' => $report_type,
		'branch' => $branch,
		'item_name' =>$item_name
		);
		
		
		if ($from_date == "" && $to_date == "") {
			$data['date_range_error_message'] = "Both feilds are required";
			} else {
			$result = $this->report_model->search_issueReport($data);
			
			
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
		$data['report_type'] = $report_type;
		$data['branch'] = $branch;
		$data['item_name'] = $item_name;
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
	    //$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$this->load->view('ExcelReport/tax_report',$data);
	
	}
	
	
	public function user_TaxIssueCancelReport(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/tax_cancel_report',$data);
	}
	
	public function search_issueCancelReport(){
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		
		$from_date = $this->input->post('from_date');
		$to_date = $this->input->post('to_date');
		$report_type = 'product_tax'; //$this->input->post('report_type');
		$branch = $this->input->post('branch');
		$item_name = $this->input->post('item_name');
		$data = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'report_type' => $report_type,
		'branch' => $branch,
		'item_name' =>$item_name
		);
		
		
		if ($from_date == "" && $to_date == "") {
			$data['date_range_error_message'] = "Both feilds are required";
			} else {
			$result = $this->report_model->search_issueCancelReport($data);
			
			
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
		$data['report_type'] = $report_type;
		$data['branch'] = $branch;
		$data['item_name'] = $item_name;
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
	    //$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$this->load->view('ExcelReport/tax_cancel_report',$data);
	
	}
	
	public function user_DCIssueReport(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/dc_report',$data);
	}
	public function search_issueDCReport(){
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		
		$from_date = $this->input->post('from_date');
		$to_date = $this->input->post('to_date');
		$report_type = $this->input->post('report_type');
		$branch = $this->input->post('branch');
		$item_name = $this->input->post('item_name');
		$data = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'report_type' => $report_type,
		'branch' => $branch,
		'item_name' =>$item_name
		);
		
		
		if ($from_date == "" && $to_date == "") {
			$data['date_range_error_message'] = "Both feilds are required";
			} else {
			$result = $this->report_model->search_issueReport($data);
			
			
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
		$data['report_type'] = $report_type;
		$data['branch'] = $branch;
		$data['item_name'] = $item_name;
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
	    //$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$this->load->view('ExcelReport/dc_report',$data);
	
	}
	
	public function user_TaxIssueReportPrev(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/tax_report_23',$data);
	}
	
	public function search_issueReportPRev(){
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		
		$from_date = $this->input->post('from_date');
		$to_date = $this->input->post('to_date');
		$report_type = 'product_tax'; //$this->input->post('report_type');
		$branch = $this->input->post('branch');
		$item_name = $this->input->post('item_name');
		$data = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'report_type' => $report_type,
		'branch' => $branch,
		'item_name' =>$item_name
		);
		
		
		if ($from_date == "" && $to_date == "") {
			$data['date_range_error_message'] = "Both feilds are required";
			} else {
			$result = $this->report_model->search_issueReportPrev($data);
			
			
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
		$data['report_type'] = $report_type;
		$data['branch'] = $branch;
		$data['item_name'] = $item_name;
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
	    //$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$this->load->view('ExcelReport/tax_report_23',$data);
	
	}
	
	public function user_DCIssueReportPrev(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/dc_report_23',$data);
	}
	public function search_issueDCReportPrev(){
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		
		$from_date = $this->input->post('from_date');
		$to_date = $this->input->post('to_date');
		$report_type = $this->input->post('report_type');
		$branch = $this->input->post('branch');
		$item_name = $this->input->post('item_name');
		$data = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'report_type' => $report_type,
		'branch' => $branch,
		'item_name' =>$item_name
		);
		
		
		if ($from_date == "" && $to_date == "") {
			$data['date_range_error_message'] = "Both feilds are required";
			} else {
			$result = $this->report_model->search_issueReportPrev($data);
			
			
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
		$data['report_type'] = $report_type;
		$data['branch'] = $branch;
		$data['item_name'] = $item_name;
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
	    //$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$this->load->view('ExcelReport/dc_report_23',$data);
	
	}
	
	public function consumption()
	{
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->report_model->get_branch();
		$data['consumption'] = $this->report_model->getConsumption();
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		$this->load->view('ExcelReport/consumption',$data);
	}
	
	public function searchConsumptionReport(){
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		
		$from_date = $this->input->post('from_date');
		$to_date = $this->input->post('to_date');
		$branch = $this->input->post('branch');
		$data = array(
		'from_date' => $from_date,
		'to_date' => $to_date,
		'branch' => $branch
		);
		
		
		if ($from_date == "" && $to_date == "") {
			$data['date_range_error_message'] = "Both feilds are required";
			} else {
			$result = $this->report_model->getSearchConsumption($data);
			
			
			if ($result != false) {
				$data['result_display'] = $result;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
		$data['branch'] = $branch;
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
	    $data['branch'] = $this->report_model->get_branch();
		$this->load->view('ExcelReport/consumption',$data);
	
	}
	
	public function taxMonthReport(){
		$user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
		
		$this->load->view('ExcelReport/month_report',$data);
	}
	
	public function search_MonthReport(){
	    $user_id = $_SESSION['user_id'];
		$branch_id = $_SESSION['branch_id'];
		
		$from_date = $this->input->post('from_date');
		$to_date = $this->input->post('to_date');
		
		$data = array(
		'from_date' => $from_date,
		'to_date' => $to_date
		);
		
		
		if ($from_date == "" && $to_date == "") {
			$data['date_range_error_message'] = "Both feilds are required";
			} else {
			$result = $this->report_model->search_MonthReport($data);
			$result1 = $this->report_model->search_SumMonthReport($data);
			
			if ($result != false) {
				$data['result_display'] = $result;
				$data['result_display1'] = $result1;
				} else {
				$data['result_display'] = "No record found !";
			}
		}
		
		$data['side_bar_data'] = $this->user_model->get_SideBarData($user_id);
	    //$data['branch'] = $this->user_model->get_branch();
		$data['outlet'] = $this->issue_model->get_outletuser();
		$data['items'] = $this->report_model->get_FPItems($branch_id);
		$this->load->view('ExcelReport/month_report',$data);
	
	}
}
?>