<?php
	class Report_model extends CI_model
	{
		public function __construct()
		{
			parent::__construct();
			$this->load->database();
		}
		var $invoice_type = 'tax_invoice25';
		public function getRawCategory(){
			$query = $this->db->query("select category_id,category_name,status from category");
			return $query->result();
		}
		
		public function getCategoryId($category_id)
		{
			$query = $this->db->query("select category_id,category_name,status from category where category_id='$category_id'");
			return $query->result();
		}
		public function getRawItem(){
			$query = $this->db->query("select * from item");
			return $query->result();
		}
		
		public function getApproveRawItem(){
			$query = $this->db->query("SELECT * FROM category C inner join item I on C.category_id=I.category_id where active_status='Pending' order by I.item_name");
			return $query->result();
		}
		
		public function getProductCategory(){
			$query = $this->db->query("select pr_cat_id,pr_cat_name,status from product_category");
			return $query->result();
		}
		
		public function getRecipeCategoryId($pr_cat_id){
			$query = $this->db->query("select pr_cat_id,pr_cat_name,status,effective_from,combination,combo_qty_limit,applicable_for,social_order from product_category where pr_cat_id='$pr_cat_id'");
			return $query->result();
		}
		
		public function RawItemId($item_id){
			$query = $this->db->query("select I.itm_id, I.item_name,I.creation_date, I.uom, I.category_id, I.creation_date, I.active_status, I.status, I.direct_sales, I.santhe, I.updated_date, I.updated_time,IC.category_id,IC.item_id, IC.branchname, IC.branch_id,IC.direct_sales,IC.expiry_date,IC.min_stk from item I inner join item_creation IC on I.itm_id=IC.itm_id where I.itm_id='$item_id'");
			return $query->result();			
		}
		
		public function Rbranch($item_id){
			$query = $this->db->query("select I.itm_id, I.item_name,I.creation_date, I.uom, I.category_id, I.creation_date, I.active_status, I.status, I.direct_sales, I.santhe, I.updated_date, I.updated_time,IC.category_id,IC.item_id, IC.branchname, IC.branch_id,IC.direct_sales,IC.expiry_date,IC.min_stk from item I inner join item_creation IC on I.itm_id=IC.itm_id where I.itm_id='$item_id'");
			return $query->result();			
		}
		
		
		public function godownPresent(){
		    $query = $this->db->query("select * from godown_stock");
			return $query->result();
		}		
		
		public function getRawItems($branch_id){
			$query = $this->db->query("select * from item_creation where branch_id='$branch_id'");
			return $query->result();
		}
		
		public function fetchAvgCatCostReport($fmonth,$tmonth,$branch){
			$fmonth1 = date('Y-m-d',strtotime($fmonth));
			$tmonth1 = date('Y-m-d',strtotime($tmonth));
			
			//$get_avgcost = "CALL AvgCategory(?, ?)";
			//$data = array('fdate' => $fmonth1, 'tdate' => $tmonth1);
			$get_avgcost = "CALL AvgCategory(?, ?, ?)";
			$data = array('fdate' => $fmonth1, 'tdate' => $tmonth1, 'branch'=>$branch);
			$query = $this->db->query($get_avgcost, $data);
			$results = $query->result_array();
			return $results;
		}
		
		public function fetchAvgCostReport($fmonth,$tmonth,$branch1,$branch){
			$fmonth1 = date('Y-m-d',strtotime($fmonth));
			$tmonth1 = date('Y-m-d',strtotime($tmonth));
			
			$frmonth = date('Y-m-d',strtotime($fmonth));
			$tomonth = date('Y-m-d',strtotime($tmonth));
			
			$frmonth2 =  json_encode($frmonth); 
		    $tomonth2 =  json_encode($tmonth);
			$get_avgcost = "CALL AMC(?, ?, ?, ?, ?, ?)";
			
			$data = array('fdate' => $fmonth1, 'tdate' => $tmonth1, 'branch' => $branch1, 'branchn' => $branch, 'fdaten'=> $frmonth2 ,'tdaten'=>$tomonth2);
			$query = $this->db->query($get_avgcost, $data);
			$results = $query->result_array();
			return $results;
		}
		
		public function getPurchaseVoulme(){
		    $month = date('m');
		    $year = date('Y');
		    $query = $this->db->query("select c.category_id,sum(a.grn_grand) as grn_grand,p.category_name,c.branchname,c.grn_price,MONTH(c.grn_date) as grn_date,MONTHNAME(c.grn_date) as grn_month,c.item_name,c.uom,c.itm_id, a.grn_item_qty, a.rejected_qty,sum(a.grn_item_qty-a.rejected_qty) as qty 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='CW' and MONTH(c.grn_date)='$month' and YEAR(c.grn_date)='$year' group by c.item_name order by sum(a.grn_grand) desc limit 10");
			return $query->result();
		}
		
		public function searchPurchaseVolume($data)
		{
			$month = date('m');
		    $year = date('Y');
			$branch = $data['branch'];
			$query = $this->db->query("select c.category_id,sum(a.grn_grand) as grn_grand,p.category_name,c.branchname,c.grn_price,MONTH(c.grn_date) as grn_date,MONTHNAME(c.grn_date) as grn_month,c.item_name,c.uom,c.itm_id, a.grn_item_qty, a.rejected_qty,sum(a.grn_item_qty-a.rejected_qty) as qty 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 MONTH(c.grn_date)='$month' and YEAR(c.grn_date)='$year' group by c.item_name order by sum(a.grn_grand) desc limit 10");
			return $query->result_array();
		}
		
		
	/*	public function searchCostVolume($data)
		{
			$month = date('m');
		    $year = date('Y');
			$branch = $data['branch'];
			$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'");
			return $query->result_array();
		}*/
		public function fetchQtyVolume($fmonth,$tmonth,$branch1,$branch){
			$fmonth1 = date('Y-m-d',strtotime($fmonth));
			$tmonth1 = date('Y-m-d',strtotime($tmonth));
			$get_qtyvolume = "CALL QtyPurchaseVolume(?, ?, ?, ?)";
			$data = array('fdate' => $fmonth1, 'tdate' => $tmonth1, 'branch' => $branch1, 'branchn' => $branch);
			$query = $this->db->query($get_qtyvolume, $data);
			$results = $query->result_array();
			return $results;
		}
		
		public function fetchPurchaseVolume($fmonth,$tmonth,$branch1,$branch){
			$fmonth1 = date('Y-m-d',strtotime($fmonth));
			$tmonth1 = date('Y-m-d',strtotime($tmonth));
			$get_purchasevolume = "CALL MPV(?, ?, ?, ?)";
			$data = array('fdate' => $fmonth1, 'tdate' => $tmonth1, 'branch' => $branch1, 'branchn' => $branch);
			$query = $this->db->query($get_purchasevolume, $data);
			$results = $query->result_array();
			return $results;
		}
		
		
		public function searchLocationWise($data)
		{
			$from_date = date('Y-m-d',strtotime($data['from_date']));
			$to_date = date('Y-m-d',strtotime($data['to_date']));
			$br_id = $data['branch'];
			$query = $this->db->query("select s2.branchname,s2.item_name,s2.opening_stock,s2.outward,s2.item_id,s3.closing_stock,i.item_price,i.item_tax,round((s3.closing_stock*i.totalamt),2) as Total from (select opening_stock,item_name,item_id,transaction_date,sum(in_qty)as inward,sum(out_qty) as outward,branchname from stock_detail where (transaction_date between '$from_date' and '$to_date') and `branch_id`='$br_id' group by item_name) s2 inner join (select * from stock_detail) s3 on s2.item_name=s3.item_name inner join (select item_id,item_name,transaction_date,closing_stock,max(last_modified) as maxdate,branchname from stock_detail where (transaction_date between '$from_date' and '$to_date') and `branch_id`='$br_id' group by item_name) s4 on s3.item_name=s4.item_name and s3.last_modified=s4.maxdate inner join (select item_id,item_name,item_price,item_tax,((item_price*item_tax)/100)+item_price as totalamt from item_creation where `branch_id`='$br_id' and status='Enabled') i on s4.item_name=i.item_name and s4.item_id=i.item_id group by s2.item_name");
			return $query->result_array();
		}
		
		public function searchCostVolume($data)
		{
			$month = date('m');
		    $year = date('Y');
			$from_date = date('Y-m-d',strtotime($data['from_date']));
			$to_date = date('Y-m-d',strtotime($data['to_date']));
			$branch = $data['branch'];
			$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' group by c.item_name");
			$results1 = $query->result_array();
		    foreach($results1 as $item1)
		    {
				$category = $item1['category_name'];
		        $item_name = $item1['item_name'];
		        $uom = $item1['uom'];
				$start = $month = $from_date;
				$end = $to_date;
				$smonth=date('Y-m-d', strtotime($month));
				while($month <= $end)
				{
					//$iname=$data['item_name'];
					$smonth=date('Y-m-d', strtotime($month)); 
					$lmonth=date('Y-m-t', strtotime($month)); 
					/*$q=$this->db->query("SELECT AVG(grn_price+((grn_price*grn_tax)/100)) as grn_price FROM grn_price WHERE item_name='$item_name' and `branchname`='$branch' and grn_date between '$smonth' and '$lmonth' ");*/
					//foreach($q->result_array() as $row2){
						$items[] = array(
						'smonth' => $smonth,
						'lmonth' => $lmonth,
						);
						
						PHP_EOL;
						$month = strtotime("+1 month", $month);
					//}
					
		
				}
				$json[] = array(
		'category' => $category,
		'item_name' => $item_name,
		'start'=>$start,
		'end'=>$end,
		'mm'=>$smonth,
		'items'=>$items
		);
		
		    }
			$emparray = array(
		'products' => $json
		);
		echo json_encode($emparray);
		file_put_contents('item_list3.json', json_encode($emparray));
	   return $emparray;
		}
		
		public function getRawItemId($item_id){
			$query = $this->db->query("select * from item_creation where item_id='$item_id'");
			return $query->result();			
		}
		
		public function getPcategory()
		{
			$query = $this->db->query("SELECT * FROM `product_category` WHERE `applicable_for`='Yes'");
			return $query->result();
		}
		
		public function getAllPcategory()
		{
			$query = $this->db->query("SELECT * FROM `product_category` order by pr_cat_name asc");
			return $query->result();
		}
		
		public function searchDailyOperation($data)
		{
			$from_date = date('Y-m-d',strtotime($data['from_date']));
			$to_date = date('Y-m-d',strtotime($data['to_date']));
			$br_id = $data['branch'];
			$pcat = $data['pcat'];
		//	$query = $this->db->query("SELECT s2.branchname,s2.item_name,s2.transaction_date as start_date,s2.opening_stock,s2.inward,s2.rinward,(s2.inward+s2.rinward) as tinward,s2.Transfer_in,s2.Transfer_out,s2.Wastage,s3.closing_stock,s2.category_id,(s2.opening_stock+s2.inward+s2.rinward+s2.Transfer_in-s2.Transfer_out-s2.Wastage-s3.closing_stock) as difference from (select opening_stock,item_name,transaction_date,category_id,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` = 'transIn' THEN `in_qty` ELSE 0  END) AS Transfer_in,SUM(CASE WHEN `trans_type` = 'transOut' 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 from stock_detail where branch_id='$br_id' and category_id='$pcat' and recipe_no!='' and (transaction_date between '$from_date' and '$to_date') group by item_name) s2 inner join (select * from stock_detail where branch_id='$br_id') s3 on s2.item_name=s3.item_name inner join (select item_name,item_id,transaction_date,closing_stock,max(last_modified) as maxdate,branchname from stock_detail where branch_id='$br_id' and (transaction_date between '$from_date' and '$to_date') group by item_name) s4 on s3.item_name=s4.item_name and s3.last_modified=s4.maxdate group by s2.item_name");
		    $query = $this->db->query("SELECT s2.branchname,s2.item_name,s2.recipe_no,s2.transaction_date as start_date,s2.opening_stock,s2.inward,s2.rinward,(s2.inward+s2.rinward) as tinward,s2.Transfer_in,s2.Transfer_out,s2.Wastage,s3.closing_stock,s2.category_id,(s2.opening_stock+s2.inward+s2.rinward+s2.Transfer_in-s2.Transfer_out-s2.Wastage-s3.closing_stock) as difference from (select opening_stock,item_name,recipe_no,transaction_date,category_id,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 from stock_detail where branch_id='$br_id' and category_id='$pcat' and recipe_no!='' and (transaction_date between '$from_date' and '$to_date') group by recipe_no) s2 inner join (select * from stock_detail where branch_id='$br_id') s3 on s2.recipe_no=s3.recipe_no inner join (select item_name,recipe_no,item_id,transaction_date,closing_stock,max(last_modified) as maxdate,branchname from stock_detail where branch_id='$br_id' and (transaction_date between '$from_date' and '$to_date') group by recipe_no) s4 on s3.recipe_no=s4.recipe_no and s3.last_modified=s4.maxdate group by s2.recipe_no order by s2.item_name asc");
			return $query->result_array();
		}
		
		public function searchRawConsumption($data)
		{
			$from_date = date('Y-m-d',strtotime($data['from_date']));
			$to_date = date('Y-m-d',strtotime($data['to_date']));
			$br_id = $data['branch'];
		    
		    $query = $this->db->query("SELECT rc.category_name,s2.branchname,s2.item_name,s2.itm_id,s2.transaction_date as start_date,s2.opening_stock,s2.inward,s2.Transfer_in,(s2.inward+s2.Transfer_in) as tinward,s2.Transfer_out,s2.Wastage,s3.closing_stock,s2.category_id,round(s2.opening_stock+s2.inward+s2.Transfer_in-s2.Transfer_out-s2.Wastage-s3.closing_stock) as difference,s2.cons_qty from (select * from category) rc inner join (select opening_stock,item_name,itm_id,transaction_date,category_id,SUM(CASE WHEN `trans_type` = 'Inward' THEN `in_qty` ELSE 0  END) AS inward,SUM(CASE WHEN `trans_type` = 'transInward' THEN `in_qty` ELSE 0 END) AS Transfer_in,SUM(CASE WHEN `trans_type` = 'transIssue' 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,SUM(CASE WHEN `trans_type` = 'Consumption' THEN `consumption_qty` ELSE 0  END) AS cons_qty,branchname from stock_detail where branch_id='$br_id' and itm_id!='' and (transaction_date between '$from_date' and '$to_date') group by itm_id) s2 on rc.category_id=s2.category_id inner join (select * from stock_detail where branch_id='$br_id') s3 on s2.itm_id=s3.itm_id inner join (select item_name,itm_id,item_id,transaction_date,closing_stock,max(last_modified) as maxdate,branchname from stock_detail where branch_id='$br_id' and (transaction_date between '$from_date' and '$to_date') group by itm_id) s4 on s3.itm_id=s4.itm_id and s3.last_modified=s4.maxdate group by s2.itm_id order by rc.category_name asc");
			return $query->result_array();
		}
		
		public function searchRecipeConsumption($data)
		{
			$from_date = date('Y-m-d',strtotime($data['from_date']));
			$to_date = date('Y-m-d',strtotime($data['to_date']));
			$br_id = $data['branch'];
		    
		    $query = $this->db->query("SELECT rc.pr_cat_name as category_name,s2.branchname as branchname,s2.item_name as item_name,s2.recipe_no as itm_id,s2.transaction_date as start_date,s2.opening_stock as opening_stock,s2.inward as inward,s2.Transfer_in as Transfer_in,(s2.inward+s2.Transfer_in) as tinward,s2.Transfer_out as Transfer_out,s2.Wastage as Wastage,s3.closing_stock as closing_stock,s2.category_id as category_id,(s2.opening_stock+s2.inward+s2.Transfer_in-s2.Transfer_out-s2.Wastage-s3.closing_stock) as difference,s2.cons_qty as cons_qty from (select * from product_category) rc inner join (select opening_stock,item_name,recipe_no,transaction_date,category_id,SUM(CASE WHEN `trans_type` = 'PrInward' THEN `in_qty` ELSE 0  END) AS inward,SUM(CASE WHEN `trans_type` = 'transIn' THEN `in_qty` ELSE 0  END) AS Transfer_in,SUM(CASE WHEN `trans_type` = 'transOut' 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,SUM(CASE WHEN `trans_type` = 'Consumption' THEN `consumption_qty` ELSE 0  END) AS cons_qty,branchname from stock_detail where branch_id='$br_id' and consumption_qty!='' and recipe_no!='' and (transaction_date between '$from_date' and '$to_date') group by recipe_no) s2 on rc.pr_cat_id=s2.category_id inner join (select * from stock_detail where branch_id='$br_id') s3 on s2.recipe_no=s3.recipe_no inner join (select item_name,recipe_no,item_id,transaction_date,closing_stock,max(last_modified) as maxdate,branchname from stock_detail where branch_id='$br_id' and (transaction_date between '$from_date' and '$to_date') group by recipe_no) s4 on s3.recipe_no=s4.recipe_no and s3.last_modified=s4.maxdate group by s2.recipe_no order by rc.pr_cat_name asc");
			return $query->result_array();
		}
		
		public function getPconsumption($data)
		{
		    $from_date = date('Y-m-d',strtotime($data['from_date']));
			$to_date = date('Y-m-d',strtotime($data['to_date']));
			$br_id = $data['branch'];
			
			$query = $this->db->query("SELECT sum(B.actual_yeild) as prod_qty,B.pname,B.yeild,B.recipe_id,B.recipe_no FROM `billdetails` A inner join bom_product B on A.`billno`=B.billno WHERE A.`orderdate` BETWEEN '$from_date' and '$to_date' and `branch_id`='$br_id' GROUP BY B.pname");
			return $query->result();
		}
		
		public function search_HsnReport($data){
    		$f_date =date('Y-m-d', strtotime($data['from_date'])) ;
    		$t_date =date('Y-m-d', strtotime($data['to_date'])) ;
    		$outlet_name = $data['outlet_name'];
    		if($outlet_name == 'All' ){
    			$query=$this->db->query("SELECT pi.issue_item_name,pi.hsn_code,p.`issue_date`,pi.`tax`,sum(pi.issued_qty) as qty,pi.price,round(sum(pi.basic_price)) as basic_amt,round(sum(CASE WHEN pi.`tax` = '0' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN pi.`tax` = '5' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN pi.`tax` = '12' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN pi.`tax` = '18' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(pi.total_price) as total_amount FROM product_issues_creation p inner join product_issues_item pi on p.`issue_id`=pi.`issue_id` where  p.issue_date between '$f_date' AND '$t_date' and p.`invoice_type`='tax_invoice25'  group by pi.`issue_item_name` order by pi.issue_item_name asc");
    		}		
    		else if($outlet_name != 'All')
    		{
    			$query=$this->db->query("SELECT pi.issue_item_name,pi.hsn_code,p.`issue_date`,pi.`tax`,sum(pi.issued_qty) as qty,pi.price,round(sum(pi.basic_price)) as basic_amt,round(sum(CASE WHEN pi.`tax` = '0' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN pi.`tax` = '5' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN pi.`tax` = '12' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN pi.`tax` = '18' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(pi.total_price) as total_amount FROM product_issues_creation p inner join product_issues_item pi on p.`issue_id`=pi.`issue_id` where p.outlet='$outlet_name' and  p.issue_date between '$f_date' AND '$t_date' and p.`invoice_type`='tax_invoice25'  group by pi.`issue_item_name` order by pi.issue_item_name asc");
    		
    		}
    		return $query->result_array();
    	}
    	
    	public function get_FPItems($branch_id)
    	{
    	    $branch_id='2';
    		$query = $this->db->query("SELECT * from recipe where branch_id='$branch_id' and status='Enabled'");
    		return $query->result();
    	}
    	
    	
    	
    /*public function search_issueReport($data){
		
		$f_date =date('Y-m-d', strtotime($data['from_date'])) ;
		$t_date =date('Y-m-d', strtotime($data['to_date'])) ;
		$report_type = $data['report_type'];
		$outlet_name = $data['branch'];
		$product_name = $data['item_name'];
        if($report_type == 'product_tax')
		{
		
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){

    				$query="SELECT P.`invoice_no` as DC_NO,P.issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  group by P.`issue_id`";
    				//and P.issue_status!='Cancel'
    				
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = "SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`issued_qty`)*PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  PI.issue_item_name='$product_name'  group by P.outlet";
    				//and P.issue_status!='Cancel'
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`issued_qty`)*PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='tax_invoice23'  group by PI.issue_item_name");
    			//and P.issue_status!='Cancel'
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query="SELECT P.`invoice_no` as DC_NO,P.issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='tax_invoice23'  group by P.`issue_id`";
    				//and P.issue_status!='Cancel'
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
			    
    			    $query = "SELECT P.`issue_id`,P.`invoice_no`,P.`vehicle_name`, P.`issue_date`,P.`issue_time`, P.`outlet`, P.`branchname`,PI.`issue_item_name`, PI.`issue_uom`, PI.`hsn_code`, sum(PI.`issued_qty`), PI.`price`, sum(PI.`basic_price`), PI.`tax`, sum(PI.`tax_amount`), sum(PI.`total_price`),PI.`recipe_id`, PI.`recipe_no`, PI.`pr_cat_id` FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and PI.issue_item_name='$product_name' and P.invoice_type='tax_invoice23'  group by PI.issue_item_name";
    			    //and issue_status!='Cancel'
    			    }
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query="SELECT P.`invoice_no` as DC_NO,P.issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`entry_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  group by P.`issue_id`";
    			//and P.issue_status!='Cancel'
    		}
			
			$result = $this->db->query($query);
			$data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
			force_download($filename, $data);
		}
		else if($report_type == 'product_dc')
		{
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){
    				$query = "SELECT P.`issue_id`,  P.`issue_no`,  P.`invoice_no`,  P.`vehicle_name`, P.`issue_date`,  P.`issue_time`, P.`outlet`, P.`branchname`,  P.`total_amount` FROM product_issues_creation P where P.issue_date between '$f_date' AND '$t_date' and P.invoice_type='DC_invoice23'";
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = "SELECT ANY_VALUE(PI.issue_item_name) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.issued_qty)) as quantity,ANY_VALUE(sum(PI.issued_qty)*PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and PI.issue_item_name='$product_name' and P.invoice_type='DC_invoice23' group by P.outlet";
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{

    			$query = "SELECT ANY_VALUE(PI.`issue_item_name`) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`total_price`)) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='DC_invoice23' group by PI.issue_item_name";
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query = "SELECT `issue_id`,  `issue_no`,  `invoice_no`,  `vehicle_name`, `issue_date`,  `issue_time`, `outlet`, `branchname`,  `total_amount` FROM product_issues_creation  where outlet='$outlet_name' and issue_date between '$f_date' AND '$t_date' and invoice_type='DC_invoice23'";
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
    			    
    				$query = "SELECT ANY_VALUE(PI.issue_item_name) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`issued_qty`)*PI.`total_price`) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and PI.issue_item_name='$product_name' and P.invoice_type='DC_invoice23' group by PI.issue_item_name";
    			}
    			
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query="SELECT P.`invoice_no` as DC_NO,P.issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`entry_date` between '$f_date' AND '$t_date' and P.invoice_type='DC_invoice23'  group by P.`issue_id`";
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'item_tax')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){
    				$query="SELECT P.`dc_no` as DC_NO,P.issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  group by P.`issue_id`";
    				//and P.issue_status!='Cancel'
    				
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = "SELECT PI.`indent_item_name` as product_name,ANY_VALUE(P.`outlet_name`) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  PI.indent_item_name='$product_name'  group by P.outlet_name";
    				//and P.issue_status!='Cancel'
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT PI.`indent_item_name` as product_name,ANY_VALUE(P.`outlet_name`) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='tax_invoice23'  group by PI.indent_item_name");
    			//and P.issue_status!='Cancel'
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query="SELECT P.`dc_no` as DC_NO,P.issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='tax_invoice23'  group by P.`issue_id`";
    				//and P.issue_status!='Cancel'
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
			    
    			    $query = "SELECT P.`issue_id`,P.`dc_no`,P.`vehicle_name`, P.`issue_date`,P.`issue_time`, P.`outlet_name`, P.`branchname`,PI.`indent_item_name`, PI.`issue_uom`, PI.`hsn_code`, sum(PI.`indent_qty`), PI.`price`, sum(PI.`basic_price`), PI.`tax`, sum(PI.`tax_amount`), sum(PI.`total_amount`),PI.`recipe_id`, PI.`recipe_no`, PI.`pr_cat_id` FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and PI.indent_item_name='$product_name' and P.invoice_type='tax_invoice23'  group by PI.indent_item_name";
    			    //and issue_status!='Cancel'
    			    }
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query="SELECT P.`dc_no` as DC_NO,P.issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  group by P.`issue_id`";
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'item_dc')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){

    				$query = "SELECT P.`issue_id`,  P.`dc_no`,   P.`vehicle_name`, P.`issue_date`,  P.`issue_time`, P.`outlet_name`, P.`branchname`,  P.`total` FROM issues_order_creation  where issue_date between '$f_date' AND '$t_date' and invoice_type='dc_invoice_23'";
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = "SELECT ANY_VALUE(PI.indent_item_name) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.indent_qty)) as quantity,ANY_VALUE(sum(PI.indent_qty)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and PI.indent_item_name='$product_name' and P.invoice_type='dc_invoice_23' group by P.outlet_name";
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = "SELECT ANY_VALUE(PI.`indent_item_name`) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`total_amount`)) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='dc_invoice_23' group by PI.indent_item_name";
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query = "SELECT `issue_id`,  `issue_no`,  `dc_no`,  `vehicle_name`, `issue_date`,  `issue_time`, `outlet_name`, `branchname`,  `total` FROM issues_order_creation  where outlet_name='$outlet_name' and issue_date between '$f_date' AND '$t_date' and invoice_type='dc_invoice_23'";
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
    			    
    				$query = "SELECT ANY_VALUE(PI.indent_item_name) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.`total_amount`) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and PI.indent_item_name='$product_name' and P.invoice_type='dc_invoice_23' group by PI.indent_item_name";
    			}
    		}
		}
		
	}*/
	
	public function search_issueReport($data){
    	    $f_date =date('Y-m-d', strtotime($data['from_date'])) ;
    		$t_date =date('Y-m-d', strtotime($data['to_date'])) ;
    		$outlet_name = $data['branch'];
    		$product_name = $data['item_name'];
    		$report_type = $data['report_type'];
    	    if($report_type == 'product_tax')
		    {
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){

    				$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,PI.`tax`,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25' and P.issue_status!='Cancel'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    				
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				//$query = $this->db->query("SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`issued_qty`)*PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23' and PI.issue_item_name='$product_name'  group by P.outlet");
    				$query = $this->db->query("SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,PI.price,sum(PI.basic_price) as basic,PI.tax,sum(PI.tax_amount) as tax_amt,sum(PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25' and P.issue_status!='Cancel' and PI.issue_item_name='$product_name'  group by P.outlet");
    				
    				//and P.issue_status!='Cancel'
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,PI.price,sum(PI.basic_price) as basic,PI.tax,sum(PI.tax_amount) as tax_amt,ANY_VALUE(sum(PI.total_price)) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='tax_invoice25' and P.issue_status!='Cancel'   group by PI.issue_item_name");
    			//and P.issue_status!='Cancel'
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='tax_invoice25' and P.issue_status!='Cancel'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
			    
    			    $query = $this->db->query("SELECT P.`issue_id`,P.`invoice_no`,P.`vehicle_name`, DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,P.`issue_time`, P.`outlet`, P.`branchname`,PI.`issue_item_name`, PI.`issue_uom`, PI.`hsn_code`, sum(PI.`issued_qty`) as quantity, PI.`price`, sum(PI.`basic_price`) as basic_price, PI.`tax`, sum(PI.`tax_amount`) as tax_amount, sum(PI.`total_price`) as total_price,PI.`recipe_id`, PI.`recipe_no`, PI.`pr_cat_id` FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and PI.issue_item_name='$product_name' and P.invoice_type='tax_invoice25' and P.issue_status!='Cancel'   group by PI.issue_item_name");
    			    //and issue_status!='Cancel'
    			    }
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`entry_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25' and P.issue_status!='Cancel'  group by P.`issue_id`");
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'product_dc')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){
    				$query = $this->db->query("SELECT P.`issue_id`,  P.`issue_no`,  P.`invoice_no`,  P.`vehicle_name`, DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,  P.`issue_time`, P.`outlet`, P.`branchname`,  P.`total_amount` FROM product_issues_creation P where P.issue_date between '$f_date' AND '$t_date' and P.invoice_type='DC_invoice25'");
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = $this->db->query("SELECT ANY_VALUE(PI.issue_item_name) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.issued_qty)) as quantity,ANY_VALUE(sum(PI.issued_qty)*PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and PI.issue_item_name='$product_name' and P.invoice_type='DC_invoice25' group by P.outlet");
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{

    			$query = $this->db->query("SELECT ANY_VALUE(PI.`issue_item_name`) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`total_price`)) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='DC_invoice25' group by PI.issue_item_name");
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query = $this->db->query("SELECT `issue_id`,  `issue_no`,  `invoice_no`,  `vehicle_name`, DATE_FORMAT(issue_date, '%d-%m-%Y') as issue_date,  `issue_time`, `outlet`, `branchname`,  `total_amount` FROM product_issues_creation  where outlet='$outlet_name' and issue_date between '$f_date' AND '$t_date' and invoice_type='DC_invoice25'");
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
    			    
    				$query = $this->db->query("SELECT ANY_VALUE(PI.issue_item_name) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`issued_qty`)*PI.`total_price`) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and PI.issue_item_name='$product_name' and P.invoice_type='DC_invoice25' group by PI.issue_item_name");
    			}
    			
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`entry_date` between '$f_date' AND '$t_date' and P.invoice_type='DC_invoice25'  group by P.`issue_id`");
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'item_tax')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){
    				$query=$this->db->query("SELECT P.`dc_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    				
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = $this->db->query("SELECT PI.`indent_item_name` as product_name,ANY_VALUE(P.`outlet_name`) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25'  PI.indent_item_name='$product_name'  group by P.outlet_name");
    				//and P.issue_status!='Cancel'
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT PI.`indent_item_name` as product_name,ANY_VALUE(P.`outlet_name`) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='tax_invoice25'  group by PI.indent_item_name");
    			//and P.issue_status!='Cancel'
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query=$this->db->query("SELECT P.`dc_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='tax_invoice25'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
			    
    			    $query = $this->db->query("SELECT P.`issue_id`,P.`dc_no`,P.`vehicle_name`, DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,P.`issue_time`, P.`outlet_name`, P.`branchname`,PI.`indent_item_name`, PI.`issue_uom`, PI.`hsn_code`, sum(PI.`indent_qty`), PI.`price`, sum(PI.`basic_price`), PI.`tax`, sum(PI.`tax_amount`), sum(PI.`total_amount`),PI.`recipe_id`, PI.`recipe_no`, PI.`pr_cat_id` FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and PI.indent_item_name='$product_name' and P.invoice_type='tax_invoice25'  group by PI.indent_item_name");
    			    //and issue_status!='Cancel'
    			    }
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query=$this->db->query("SELECT P.`dc_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25'  group by P.`issue_id`");
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'item_dc')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){

    				$query = $this->db->query("SELECT P.`issue_id`,  P.`dc_no`,   P.`vehicle_name`, P.`issue_date`,  P.`issue_time`, P.`outlet_name`, P.`branchname`,  P.`total` FROM issues_order_creation  where issue_date between '$f_date' AND '$t_date' and invoice_type='dc_invoice_23'");
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = $this->db->query("SELECT ANY_VALUE(PI.indent_item_name) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.indent_qty)) as quantity,ANY_VALUE(sum(PI.indent_qty)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and PI.indent_item_name='$product_name' and P.invoice_type='dc_invoice_23' group by P.outlet_name");
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT ANY_VALUE(PI.`indent_item_name`) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`total_amount`)) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='dc_invoice_23' group by PI.indent_item_name");
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query = $this->db->query("SELECT `issue_id`,  `issue_no`,  `dc_no`,  `vehicle_name`, `issue_date`,  `issue_time`, `outlet_name`, `branchname`,  `total` FROM issues_order_creation  where outlet_name='$outlet_name' and issue_date between '$f_date' AND '$t_date' and invoice_type='dc_invoice_23'");
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
    			    
    				$query = $this->db->query("SELECT ANY_VALUE(PI.indent_item_name) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.`total_amount`) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and PI.indent_item_name='$product_name' and P.invoice_type='dc_invoice_23' group by PI.indent_item_name");
    			}
    		}
			
		}
		return $query->result_array();
    	}
    	public function search_issueCancelReport($data){
    	    $f_date =date('Y-m-d', strtotime($data['from_date'])) ;
    		$t_date =date('Y-m-d', strtotime($data['to_date'])) ;
    		$outlet_name = $data['branch'];
    		$product_name = $data['item_name'];
    		$report_type = $data['report_type'];
    	    if($report_type == 'product_tax')
		    {
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){

    				$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,PI.`tax`,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25' and P.issue_status='Cancel'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    				
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				//$query = $this->db->query("SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`issued_qty`)*PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23' and PI.issue_item_name='$product_name'  group by P.outlet");
    				$query = $this->db->query("SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,PI.price,sum(PI.basic_price) as basic,PI.tax,sum(PI.tax_amount) as tax_amt,sum(PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25' and P.issue_status='Cancel' and PI.issue_item_name='$product_name'  group by P.outlet");
    				
    				//and P.issue_status!='Cancel'
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,PI.price,sum(PI.basic_price) as basic,PI.tax,sum(PI.tax_amount) as tax_amt,ANY_VALUE(sum(PI.total_price)) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='tax_invoice25' and P.issue_status='Cancel'   group by PI.issue_item_name");
    			//and P.issue_status!='Cancel'
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='tax_invoice25' and P.issue_status='Cancel'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
			    
    			    $query = $this->db->query("SELECT P.`issue_id`,P.`invoice_no`,P.`vehicle_name`, DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,P.`issue_time`, P.`outlet`, P.`branchname`,PI.`issue_item_name`, PI.`issue_uom`, PI.`hsn_code`, sum(PI.`issued_qty`) as quantity, PI.`price`, sum(PI.`basic_price`) as basic_price, PI.`tax`, sum(PI.`tax_amount`) as tax_amount, sum(PI.`total_price`) as total_price,PI.`recipe_id`, PI.`recipe_no`, PI.`pr_cat_id` FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and PI.issue_item_name='$product_name' and P.invoice_type='tax_invoice25' and P.issue_status='Cancel'   group by PI.issue_item_name");
    			    //and issue_status!='Cancel'
    			    }
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`entry_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25' and P.issue_status='Cancel'  group by P.`issue_id`");
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'product_dc')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){
    				$query = $this->db->query("SELECT P.`issue_id`,  P.`issue_no`,  P.`invoice_no`,  P.`vehicle_name`, DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,  P.`issue_time`, P.`outlet`, P.`branchname`,  P.`total_amount` FROM product_issues_creation P where P.issue_date between '$f_date' AND '$t_date' and P.invoice_type='DC_invoice25'");
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = $this->db->query("SELECT ANY_VALUE(PI.issue_item_name) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.issued_qty)) as quantity,ANY_VALUE(sum(PI.issued_qty)*PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and PI.issue_item_name='$product_name' and P.invoice_type='DC_invoice25' group by P.outlet");
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{

    			$query = $this->db->query("SELECT ANY_VALUE(PI.`issue_item_name`) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`total_price`)) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='DC_invoice25' group by PI.issue_item_name");
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query = $this->db->query("SELECT `issue_id`,  `issue_no`,  `invoice_no`,  `vehicle_name`, DATE_FORMAT(issue_date, '%d-%m-%Y') as issue_date,  `issue_time`, `outlet`, `branchname`,  `total_amount` FROM product_issues_creation  where outlet='$outlet_name' and issue_date between '$f_date' AND '$t_date' and invoice_type='DC_invoice25'");
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
    			    
    				$query = $this->db->query("SELECT ANY_VALUE(PI.issue_item_name) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`issued_qty`)*PI.`total_price`) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and PI.issue_item_name='$product_name' and P.invoice_type='DC_invoice25' group by PI.issue_item_name");
    			}
    			
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`entry_date` between '$f_date' AND '$t_date' and P.invoice_type='DC_invoice25'  group by P.`issue_id`");
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'item_tax')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){
    				$query=$this->db->query("SELECT P.`dc_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    				
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = $this->db->query("SELECT PI.`indent_item_name` as product_name,ANY_VALUE(P.`outlet_name`) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25'  PI.indent_item_name='$product_name'  group by P.outlet_name");
    				//and P.issue_status!='Cancel'
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT PI.`indent_item_name` as product_name,ANY_VALUE(P.`outlet_name`) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='tax_invoice25'  group by PI.indent_item_name");
    			//and P.issue_status!='Cancel'
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query=$this->db->query("SELECT P.`dc_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='tax_invoice25'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
			    
    			    $query = $this->db->query("SELECT P.`issue_id`,P.`dc_no`,P.`vehicle_name`, DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,P.`issue_time`, P.`outlet_name`, P.`branchname`,PI.`indent_item_name`, PI.`issue_uom`, PI.`hsn_code`, sum(PI.`indent_qty`), PI.`price`, sum(PI.`basic_price`), PI.`tax`, sum(PI.`tax_amount`), sum(PI.`total_amount`),PI.`recipe_id`, PI.`recipe_no`, PI.`pr_cat_id` FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and PI.indent_item_name='$product_name' and P.invoice_type='tax_invoice25'  group by PI.indent_item_name");
    			    //and issue_status!='Cancel'
    			    }
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query=$this->db->query("SELECT P.`dc_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice25'  group by P.`issue_id`");
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'item_dc')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){

    				$query = $this->db->query("SELECT P.`issue_id`,  P.`dc_no`,   P.`vehicle_name`, P.`issue_date`,  P.`issue_time`, P.`outlet_name`, P.`branchname`,  P.`total` FROM issues_order_creation  where issue_date between '$f_date' AND '$t_date' and invoice_type='dc_invoice_23'");
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = $this->db->query("SELECT ANY_VALUE(PI.indent_item_name) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.indent_qty)) as quantity,ANY_VALUE(sum(PI.indent_qty)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and PI.indent_item_name='$product_name' and P.invoice_type='dc_invoice_23' group by P.outlet_name");
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT ANY_VALUE(PI.`indent_item_name`) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`total_amount`)) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='dc_invoice_23' group by PI.indent_item_name");
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query = $this->db->query("SELECT `issue_id`,  `issue_no`,  `dc_no`,  `vehicle_name`, `issue_date`,  `issue_time`, `outlet_name`, `branchname`,  `total` FROM issues_order_creation  where outlet_name='$outlet_name' and issue_date between '$f_date' AND '$t_date' and invoice_type='dc_invoice_23'");
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
    			    
    				$query = $this->db->query("SELECT ANY_VALUE(PI.indent_item_name) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.`total_amount`) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and PI.indent_item_name='$product_name' and P.invoice_type='dc_invoice_23' group by PI.indent_item_name");
    			}
    		}
			
		}
		return $query->result_array();
    	}
    	
    	
    	public function getConsumption()
		{
			$query = $this->db->query("select * from recipe where branchname='STR'");
			return $query->result();
		}
		
		public function get_branch()
		{
			$query = $this->db->query("select branch_id,branch_name from user where branch_code!=''");
			return $query->result();
		}
		
		public function getSearchConsumption($data)
		{
			$query = $this->db->query("select * from recipe where branchname='STR'");
			return $query->result_array();
		}
		public function search_issueReportPrev($data){
    	    $f_date =date('Y-m-d', strtotime($data['from_date'])) ;
    		$t_date =date('Y-m-d', strtotime($data['to_date'])) ;
    		$outlet_name = $data['branch'];
    		$product_name = $data['item_name'];
    		$report_type = $data['report_type'];
    	    if($report_type == 'product_tax')
		    {
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){

    				$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    				
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				//$query = $this->db->query("SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`issued_qty`)*PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23' and PI.issue_item_name='$product_name'  group by P.outlet");
    				$query = $this->db->query("SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,PI.price,sum(PI.basic_price) as basic,PI.tax,sum(PI.tax_amount) as tax_amt,ANY_VALUE(sum(PI.total_price)) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23' and PI.issue_item_name='$product_name'  group by P.outlet");
    				
    				//and P.issue_status!='Cancel'
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT PI.`issue_item_name` as product_name,ANY_VALUE(P.`outlet`) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,PI.price,sum(PI.basic_price) as basic,PI.tax,sum(PI.tax_amount) as tax_amt,ANY_VALUE(sum(PI.total_price)) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='tax_invoice23'  group by PI.issue_item_name");
    			//and P.issue_status!='Cancel'
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='tax_invoice23'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
			    
    			    $query = $this->db->query("SELECT P.`issue_id`,P.`invoice_no`,P.`vehicle_name`, DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,P.`issue_time`, P.`outlet`, P.`branchname`,PI.`issue_item_name`, PI.`issue_uom`, PI.`hsn_code`, sum(PI.`issued_qty`) as quantity, PI.`price`, sum(PI.`basic_price`) as basic_price, PI.`tax`, sum(PI.`tax_amount`) as tax_amount, sum(PI.`total_price`) as total_price,PI.`recipe_id`, PI.`recipe_no`, PI.`pr_cat_id` FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and PI.issue_item_name='$product_name' and P.invoice_type='tax_invoice23'  group by PI.issue_item_name");
    			    //and issue_status!='Cancel'
    			    }
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`entry_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  group by P.`issue_id`");
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'product_dc')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){
    				$query = $this->db->query("SELECT P.`issue_id`,  P.`issue_no`,  P.`invoice_no`,  P.`vehicle_name`, DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,  P.`issue_time`, P.`outlet`, P.`branchname`,  P.`total_amount` FROM product_issues_creation P where P.issue_date between '$f_date' AND '$t_date' and P.invoice_type='DC_invoice23'");
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = $this->db->query("SELECT ANY_VALUE(PI.issue_item_name) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.issued_qty)) as quantity,ANY_VALUE(sum(PI.issued_qty)*PI.total_price) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and PI.issue_item_name='$product_name' and P.invoice_type='DC_invoice23' group by P.outlet");
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{

    			$query = $this->db->query("SELECT ANY_VALUE(PI.`issue_item_name`) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`total_price`)) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and P.invoice_type='DC_invoice23' group by PI.issue_item_name");
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query = $this->db->query("SELECT `issue_id`,  `issue_no`,  `invoice_no`,  `vehicle_name`, DATE_FORMAT(issue_date, '%d-%m-%Y') as issue_date,  `issue_time`, `outlet`, `branchname`,  `total_amount` FROM product_issues_creation  where outlet='$outlet_name' and issue_date between '$f_date' AND '$t_date' and invoice_type='DC_invoice23'");
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
    			    
    				$query = $this->db->query("SELECT ANY_VALUE(PI.issue_item_name) as product_name,ANY_VALUE(P.outlet) as outlet_name,ANY_VALUE(sum(PI.`issued_qty`)) as quantity,ANY_VALUE(sum(PI.`issued_qty`)*PI.`total_price`) as total FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet='$outlet_name' and PI.issue_item_name='$product_name' and P.invoice_type='DC_invoice23' group by PI.issue_item_name");
    			}
    			
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query=$this->db->query("SELECT P.`invoice_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet`,sum(PI.total_price) as total_price,P.issue_id FROM product_issues_creation P inner join product_issues_item PI on P.issue_id=PI.issue_id where P.`entry_date` between '$f_date' AND '$t_date' and P.invoice_type='DC_invoice23'  group by P.`issue_id`");
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'item_tax')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){
    				$query=$this->db->query("SELECT P.`dc_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    				
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = $this->db->query("SELECT PI.`indent_item_name` as product_name,ANY_VALUE(P.`outlet_name`) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  PI.indent_item_name='$product_name'  group by P.outlet_name");
    				//and P.issue_status!='Cancel'
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT PI.`indent_item_name` as product_name,ANY_VALUE(P.`outlet_name`) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='tax_invoice23'  group by PI.indent_item_name");
    			//and P.issue_status!='Cancel'
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query=$this->db->query("SELECT P.`dc_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.basic_price ELSE 0 END),3) AS basic_0,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.basic_price ELSE 0 END),3) AS basic_5,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.basic_price ELSE 0 END),3) AS basic_12,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.basic_price ELSE 0 END),3) AS basic_18,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='tax_invoice23'  group by P.`issue_id`");
    				//and P.issue_status!='Cancel'
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
			    
    			    $query = $this->db->query("SELECT P.`issue_id`,P.`dc_no`,P.`vehicle_name`, DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,P.`issue_time`, P.`outlet_name`, P.`branchname`,PI.`indent_item_name`, PI.`issue_uom`, PI.`hsn_code`, sum(PI.`indent_qty`), PI.`price`, sum(PI.`basic_price`), PI.`tax`, sum(PI.`tax_amount`), sum(PI.`total_amount`),PI.`recipe_id`, PI.`recipe_no`, PI.`pr_cat_id` FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and PI.indent_item_name='$product_name' and P.invoice_type='tax_invoice23'  group by PI.indent_item_name");
    			    //and issue_status!='Cancel'
    			    }
    		}
    		else if($outlet_name == 'All' && $product_name == 'All')
    		{
    			$query=$this->db->query("SELECT P.`dc_no` as DC_NO,DATE_FORMAT(P.issue_date, '%d-%m-%Y') as issue_date,sum(PI.basic_price) as basic_price,P.`outlet_name`,round(sum(CASE WHEN PI.`tax` = '0' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN PI.`tax` = '5' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN PI.`tax` = '12' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN PI.`tax` = '18' THEN PI.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(PI.total_amount) as total_price,P.issue_id FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.`issue_date` between '$f_date' AND '$t_date' and P.invoice_type='tax_invoice23'  group by P.`issue_id`");
    			//and P.issue_status!='Cancel'
    		}
			
		}
		else if($report_type == 'item_dc')
		{
			
			if($outlet_name == 'All' && $product_name != 'All'){
    			if($outlet_name == 'All' && $product_name == ''){

    				$query = $this->db->query("SELECT P.`issue_id`,  P.`dc_no`,   P.`vehicle_name`, P.`issue_date`,  P.`issue_time`, P.`outlet_name`, P.`branchname`,  P.`total` FROM issues_order_creation  where issue_date between '$f_date' AND '$t_date' and invoice_type='dc_invoice_23'");
    			}
    			else if($outlet_name == 'All' && $product_name != ''){
    				$query = $this->db->query("SELECT ANY_VALUE(PI.indent_item_name) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.indent_qty)) as quantity,ANY_VALUE(sum(PI.indent_qty)*PI.total_amount) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and PI.indent_item_name='$product_name' and P.invoice_type='dc_invoice_23' group by P.outlet_name");
    			}
    		}		
    		else if($outlet_name != 'All' && $product_name == 'All')
    		{
    			$query = $this->db->query("SELECT ANY_VALUE(PI.`indent_item_name`) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`total_amount`)) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and P.invoice_type='dc_invoice_23' group by PI.indent_item_name");
    		}
    		else if($outlet_name != 'All' && $product_name != 'All')
    		{
    			if($outlet_name != 'All' && $product_name == ''){
    				$query = $this->db->query("SELECT `issue_id`,  `issue_no`,  `dc_no`,  `vehicle_name`, `issue_date`,  `issue_time`, `outlet_name`, `branchname`,  `total` FROM issues_order_creation  where outlet_name='$outlet_name' and issue_date between '$f_date' AND '$t_date' and invoice_type='dc_invoice_23'");
    			}
    			else if($outlet_name != 'All' && $product_name != ''){
    			    
    				$query = $this->db->query("SELECT ANY_VALUE(PI.indent_item_name) as product_name,ANY_VALUE(P.outlet_name) as outlet_name,ANY_VALUE(sum(PI.`indent_qty`)) as quantity,ANY_VALUE(sum(PI.`indent_qty`)*PI.`total_amount`) as total FROM issues_order_creation P inner join issues_order_item PI on P.issue_id=PI.issue_id where P.issue_date between '$f_date' AND '$t_date' and P.outlet_name='$outlet_name' and PI.indent_item_name='$product_name' and P.invoice_type='dc_invoice_23' group by PI.indent_item_name");
    			}
    		}
			
		}
		return $query->result_array();
    	}
    	
    	public function search_MonthReport($data){
    	    $f_date =date('Y-m-d', strtotime($data['from_date'])) ;
    		$t_date =date('Y-m-d', strtotime($data['to_date'])) ;
    		
    	    
    		//$query=$this->db->query("SELECT p.`issue_date`,pi.`tax`,sum(pi.issued_qty) as qty,pi.price,round(sum(pi.basic_price)) as basic_amt,round(sum(CASE WHEN pi.`tax` = '0' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN pi.`tax` = '5' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN pi.`tax` = '12' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN pi.`tax` = '18' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(pi.total_price) as total_amount FROM product_issues_creation p inner join product_issues_item pi on p.`issue_id`=pi.`issue_id` where p.issue_date between '$f_date' AND '$t_date' and p.`invoice_type`='tax_invoice24' group by p.`issue_date` order by p.`issue_date` asc");
    		$query=$this->db->query("SELECT p.`issue_date`,pi.`tax` as taxp,sum(pi.issued_qty) as qty,pi.price,round(sum(pi.basic_price)) as basic_amt,round(sum(CASE WHEN pi.`tax` = '0' THEN pi.basic_price ELSE 0 END),3) AS Tax_0B,round(sum(CASE WHEN pi.`tax` = '0' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN pi.`tax` = '5' THEN pi.basic_price ELSE 0 END),3) AS Tax_5B,round(sum(CASE WHEN pi.`tax` = '5' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN pi.`tax` = '12' THEN pi.basic_price ELSE 0 END),3) AS Tax_12B,round(sum(CASE WHEN pi.`tax` = '12' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN pi.`tax` = '18' THEN pi.basic_price ELSE 0 END),3) AS Tax_18B,round(sum(CASE WHEN pi.`tax` = '18' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(pi.total_price) as total_amount FROM product_issues_creation p inner join product_issues_item pi on p.`issue_id`=pi.`issue_id` where p.issue_date between '$f_date' AND '$t_date' and p.`invoice_type`='tax_invoice25' group by p.`issue_date` order by p.`issue_date` asc");
    		return $query->result_array();
    	}
    	
    	public function search_SumMonthReport($data){
    	    $f_date =date('Y-m-d', strtotime($data['from_date'])) ;
    		$t_date =date('Y-m-d', strtotime($data['to_date'])) ;
    		
    	    
    		$query=$this->db->query("SELECT p.`issue_date`,pi.`tax`,sum(pi.issued_qty) as qty,pi.price,round(sum(pi.basic_price)) as basic_amt,round(sum(CASE WHEN pi.`tax` = '0' THEN pi.`basic_price` ELSE 0 END),3) AS Tax_0_basic,round(sum(CASE WHEN pi.`tax` = '5' THEN pi.`basic_price` ELSE 0 END),3) AS Tax_5_basic,round(sum(CASE WHEN pi.`tax` = '12' THEN pi.`basic_price` ELSE 0 END),3) AS Tax_12_basic,round(sum(CASE WHEN pi.`tax` = '18' THEN pi.`basic_price` ELSE 0 END),3) AS Tax_18_basic,round(sum(CASE WHEN pi.`tax` = '0' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_0,round(sum(CASE WHEN pi.`tax` = '5' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_5,round(sum(CASE WHEN pi.`tax` = '12' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_12,round(sum(CASE WHEN pi.`tax` = '18' THEN pi.`tax_amount` ELSE 0 END),3) AS Tax_18,sum(pi.total_price) as total_amount FROM product_issues_creation p inner join product_issues_item pi on p.`issue_id`=pi.`issue_id` where p.issue_date between '$f_date' AND '$t_date' and p.`invoice_type`='tax_invoice25'");
    		return $query->result_array();
    	}
		
	}