<?php
	class Invoice_model extends CI_model
	{
		public function __construct()
		{
			parent::__construct();
			$this->load->database();
			//$this->db2 = $this->load->database('otherdb', TRUE);
		}
		public function addInvice($postdata){
			extract($postdata);
			$branch_id = $_SESSION['branch_id'];
			$invoice_date = date("Y-m-d", strtotime($invoice_date));
			$order_time = date("h:i A");
			$lm=date("Y-m-dH:i:s",time());
			$branchname = $_SESSION['branch_name'];
			$sql = $this->db->query("SELECT MAX(in_no) FROM invoice where branch_id='$branch_id'");
			if($sql->num_rows()>0)
			{
				foreach($sql->result_array() as $rows)
				if($rows['MAX(in_no)'] == 0) {
					
					$in_no = 1;
				}
				else {
					$in_no = $rows['MAX(in_no)'] + 1;
				} 
				$in_order_no = "INV/".$branchname."/".$in_no;
			}
			
			$k=0;
			for($j=0;$j<count($quantity);$j++)
			{
				if($quantity[$j]!='0' && $quantity[$j]!='')
				{
					$k=$k+1;
				}
			}
			if($k>0)
			{
			    $sql1 = $this->db->query("insert into invoice(in_no,invoice_no,invoice_date,outlet_name,branchname,user_name,invoice_time,branch_id) values ('$in_no','$in_order_no','$invoice_date','$outlet_name','$branchname','$full_name','$order_time','$branch_id')");
			    $in_id = $this->db->insert_id();
			}
			if($in_id)
			{
			for($i=0;$i<count($it_name),$i<count($item_sid),$i<count($uom),$i<count($quantity),$i<count($pr_stock),$i<count($price),$i<count($tax),$i<count($tax_amount),$i<count($total_amount),$i<count($remarks);$i++) {
				
				$sql3=$this->db->query("select * from item_creation where item_id='$item_sid[$i]'");
				
				if($sql3->num_rows()>0)
				{
					foreach($sql3->result_array() as $res)
					$qty[$i] = $res['stock_item'];//opening_stock
					$qty1[$i] = $qty[$i] - $quantity[$i];//closing_stock
					$itm_id[$i] = $res['itm_id'];
					$cat_id[$i] = $res['category_id'];
					
					$sql3 =  $this->db->query("UPDATE item_creation set stock_item='$qty1[$i]' WHERE  item_id= '$item_sid[$i]'");
				}
				$sql2 =  $this->db->query("insert into  invoice_item (item_name,uom,required_qty,present_stock,item_price,tax,tax_amount,total_amount,remarks,invoice_id,item_id,itm_id,category_id) values ('$it_name[$i]','$uom[$i]','$quantity[$i]','$pr_stock[$i]','$price[$i]','$tax[$i]','$tax_amount[$i]','$total_amount[$i]','$remarks[$i]','$in_id','$item_sid[$i]','$itm_id[$i]','$cat_id[$i]')");
				$sql5 = $this->db->query("insert into stock_detail(item_name,trans_type,trans_ref_no,transaction_date,opening_stock,out_qty,closing_stock,last_modified,branchname,item_id,itm_id,category_id,branch_id)values('$it_name[$i]','Invoice','$in_order_no','$invoice_date','$qty[$i]','$quantity[$i]','$qty1[$i]','$lm','$branchname','$item_sid[$i]','$itm_id[$i]','$cat_id[$i]','$branch_id')");
			}
		}
			//echo $in_id;
			return $in_id;
		}

		public function invoice_receipt($invoice_id) {
			$query = $this->db->query("SELECT *,DATE_FORMAT(invoice_date, '%d-%m-%Y') as invoice_date FROM invoice where invoice_id='$invoice_id'");
			return $query->result();
		}
		
		public function invoice_receipt_item($invoice_id) {
			$query = $this->db->query("SELECT *,ROUND(T.tax_amount,2) as tax_amount,ROUND(T.total_amount,2) as total_amount FROM  invoice i inner join invoice_item T on i.invoice_id=T.invoice_id where i.invoice_id='$invoice_id'");
			return $query->result();
		}
		
		public function invoice_total_sum($invoice_id) {
			$query = $this->db->query("SELECT round(sum(I.total_amount),2) as `sum_amount`,  round(sum(I.tax_amount),2) as `sum_tax_amount` FROM  invoice P inner join invoice_item I on P.invoice_id=I.invoice_id where P.invoice_id='$invoice_id'");
			return $query->result();
		}

		public function invoice_list($branch_id) {
			$query = $this->db->query("select i.invoice_id,i.invoice_no,i.outlet_name,DATE_FORMAT(i.invoice_date, '%d-%m-%Y') as invoice_date,i.branchname,round(sum(p.total_amount),2) as value from invoice i inner join invoice_item p on i.invoice_id=p.invoice_id where i.branch_id='$branch_id' group by p.invoice_id order by i.invoice_id asc");
			return $query->result();
		}

		public function search_invoice($data){
		$from_date =date('Y-m-d', strtotime($data['from_date'])) ;
		$to_date =date('Y-m-d', strtotime($data['to_date'])) ;
		$outlet_name = $data['outlet_name'];
		$item_name = $data['item_name'];
		$branch_name = $_SESSION['branch_name'];
		if($from_date!="" && $to_date!="" && $outlet_name!="" && $item_name==""){
			if($outlet_name != 'All') {
				//$purchase->get_invoice_report_byOutlet
				$query = $this->db->query("SELECT *, DATE_FORMAT(i.invoice_date, '%d-%m-%Y') as invoice_date, sum(it.total_amount) as total FROM invoice i inner join invoice_item it on i.invoice_id=it.invoice_id where branchname='$branch_name' and outlet_name='$outlet_name' and (invoice_date between '$from_date' and '$to_date') group by it.invoice_id");
			}
			else if($outlet_name == 'All')
			{
				 //$purchase->get_all_invoice_report_byOutlet
				 $query = $this->db->query("SELECT *, DATE_FORMAT(i.invoice_date, '%d-%m-%Y') as invoice_date, sum(it.total_amount) as total FROM invoice i inner join invoice_item it on i.invoice_id=it.invoice_id where branchname='$branch_name' and (invoice_date between '$from_date' and '$to_date') group by it.invoice_id");
			}
		}
		else if($from_date!="" && $to_date!="" && $outlet_name!="" && $item_name!="")
		{
			if($outlet_name != 'All' && $item_name == 'All')
			{
				//$purchase->get_invoice_item_outlet_report
				$query = $this->db->query("SELECT i.item_name,c.outlet_name,sum(i.required_qty)as qty,round(sum(i.total_amount),2) as total FROM invoice_item i inner join invoice c on i.invoice_id=c.invoice_id WHERE c.outlet_name='$outlet_name' and c.branchname='$branch_name' and invoice_date between '$from_date' and '$to_date' group by i.item_name");
			}
			else if($outlet_name == 'All' && $item_name != 'All')
			{
				//$purchase->get_invoice_item_outlet_report
				$query = $this->db->query("SELECT i.item_name,c.outlet_name,sum(i.required_qty)as qty,round(sum(i.total_amount),2) as total FROM invoice_item i inner join invoice c on i.invoice_id=c.invoice_id WHERE  c.branchname='$branch_name' and i.item_name='$item_name' and invoice_date between '$from_date' and '$to_date' group by i.item_name");
			}
			else if($outlet_name != 'All' && $item_name != 'All')
			{
				//$purchase->get_invoice_item_outlet_report
				$query = $this->db->query("SELECT i.item_name,c.outlet_name,sum(i.required_qty)as qty,round(sum(i.total_amount),2) as total FROM invoice_item i inner join invoice c on i.invoice_id=c.invoice_id WHERE c.outlet_name='$outlet_name' and c.branchname='$branch_name' and i.item_name='$item_name' and invoice_date between '$from_date' and '$to_date' group by i.item_name");
			}
		}
	
		return $query->result_array();
	}
	
	public function addProductInvoice($postdata)
	{
		extract($postdata);
		$invoice_date = date("Y-m-d", strtotime($invoice_date));
		$inv_time = date("h:i A");
		$lm=date("Y-m-dH:i:s",time());
		$this->db->select('MAX(is_no) as is_no',false);
		$this->db->where('branch_id',$branch_id);
		$result = $this->db->get('product_issues_creation')->row();
		$is_no = $result->is_no;
		if($is_no == 0){
			$is_no = 1;
		}
		else{
			$is_no = $is_no+1;
		}
		$issue_no = "ISSU"."/".$branch_name."/".$outlet_branch."/".$is_no;
		
		$this->db->select('MAX(in_no) as in_no',false);
		$this->db->where('branch_id',$branch_id);
		$result = $this->db->get('product_issues_creation')->row();
		$in_no = $result->in_no;
		if($in_no == 0){
			$in_no = 1;
		}
		else{
			$in_no = $in_no+1;
		}
		$invoice_no = "INV"."/".$branch_name."/".$in_no;
		
		$k=0;
		for($j=0;$j<count($quantity);$j++)
		{
			if($quantity[$j]!='0' && $quantity[$j]!='')
			{
				$k=$k+1;
			}
		}

		if($k>0)
		{
		$data = array('is_no'=> $is_no,
		'issue_no'=> $issue_no,
		'in_no'=> $in_no,
		'invoice_no'=> $invoice_no,
		'issue_date'=> $invoice_date,
		'issue_time'=> $inv_time,
		'outlet'=> $outlet_name,
		'branchname'=> $branch_name,
		'request_id'=> '0',
		'issue_status'=> 'Accepted',
		'total_amount'=> $total_amt,
		'user_name'=> $full_name,
		'branch_id'=> $branch_id
		);
		$this->db->insert('product_issues_creation', $data);
		$is_id = $this->db->insert_id();
		}
		
		if($is_id)
		{
		$count = count($item_name);
		$j=0;
		for($i=0;$i<$count;$i++)
		{
			if($quantity[$i]!='0' && $quantity[$i]!='')
			{
				$q1 = $this->db->query("select * from recipe where recipe_id='$recipe_id[$i]'");
				if($q1->num_rows() > 0)
				{
					$row = $q1->row_array();
					$qty[$i] = $row['production_stock'];
					$qty1[$i] = $qty[$i] - $quantity[$i];
					
					$q2 = $this->db->query("update recipe set production_stock='$qty1[$i]' where recipe_id='$recipe_id[$i]'");
				}
				$data2 = array(
					'issue_item_name' => $item_name[$i],
					'issue_uom' => $uom[$i],
					'issued_qty' => $quantity[$i],
					'price' => $price[$i],
					'basic_price' => $ttl_price[$i],
					'tax' => $tax[$i],
					'tax_amount' => $tax_amt[$i],
					'total_price' => $total[$i],
					'issue_id' => $is_id,
					'request_item_id' => '0',
					'recipe_id' => $recipe_id[$i],
					'rcv_status' => 'Closed',
					'recipe_no' => $recipe_no[$i],
					'pr_cat_id' => $cat_id[$i]
					);
				$this->db->insert('product_issues_item', $data2);
				$is_itid = $this->db->insert_id();
				
				$data3 = array(
					'item_name' => $item_name[$i],
					'trans_ref_no' => $issue_no,
					'trans_type'=>'PrIssue',
					'transaction_date' => $invoice_date,
					'opening_stock' => $qty[$i],
					'out_qty' => $quantity[$i],
					'closing_stock' => $qty1[$i],
					'last_modified' => $lm,
					'branchname' => $branch_name,
					'item_id' => $recipe_id[$i],
					'recipe_no' => $recipe_no[$i],
					'category_id' => $cat_id[$i],
					'branch_id' => $branch_id
					);
				$this->db->insert('stock_detail', $data3);
				$j = $j + 1;
			}
		}
		if($j == 0)
		{
			$del = $this->db->query("delete from product_issues_creation where issue_id='$is_id'");
		}
	}
		return $is_id;
	}
	
	public function getProductInvoice($isid) {
		$query = $this->db->query("SELECT * FROM product_issues_creation where issue_id='$isid'");
		return $query->result();
	}
	
	public function getProductInvoiceItem($isid) {
		$query = $this->db->query("SELECT P.*,R.hsn_code FROM product_issues_item P inner join recipe R on P.recipe_id=R.recipe_id where P.issue_id='$isid'");
		return $query->result();
	}
	
	public function getProductInvoiceList($branch_id) {
		$query = $this->db->query("SELECT * FROM product_issues_creation where branch_id='$branch_id' and invoice_no!=''");
		return $query->result();
	}
	
	public function cInvoice_list(){
		$query = $this->db->query("SELECT i.invoice_id,i.invoice_no,i.outlet_name,DATE_FORMAT(i.invoice_date, '%d-%m-%Y') as invoice_date,i.branchname,round(sum(p.total_amount),2) as value from invoice i inner join invoice_item p on i.invoice_id=p.invoice_id group by p.invoice_id");
		return $query->result();
	}

	public function search_Cinvoice($data){
		$branch = $data['branch'];
		$query = $this->db->query("SELECT i.invoice_id,i.invoice_no,i.outlet_name,DATE_FORMAT(i.invoice_date, '%d-%m-%Y') as invoice_date,i.branchname,round(sum(p.total_amount),2) as value from invoice i inner join invoice_item p on i.invoice_id=p.invoice_id where i.branchname='$branch' group by p.invoice_id");
		return $query->result();
		
	}
}
?>