<?php
	class Purchase_model extends CI_model
	{
		public function __construct()
		{
			parent::__construct();
			$this->load->database();
			$this->db2 = $this->load->database('otherdb', TRUE);
		}
		public function addPurchase($postdata){
			extract($postdata);
			$order_date = date("Y-m-d", strtotime($order_date));
			$order_time = date("h:i A");
			$this->db->select('MAX(bl_no) as bl_no',false);
			$this->db->where('branch_id',$branch_id);
			$result = $this->db->get('purchase_order')->row();
			$bl_no = $result->bl_no;
			if($bl_no == 0){
				$po_no = 1;
			}
			else{
				$po_no = $bl_no+1;
			}
			$purchase_no = $branch_name."/".$po_no;
			
			$k=0;
			for($j=0;$j<count($quantity);$j++)
			{
				if($quantity[$j]!='0' && $quantity[$j]!='')
				{
					$k=$k+1;
				}
			}
			if($k>0)
			{
			$data = array('bl_no'=> $po_no,
			'purchase_no'=> $purchase_no,
			'purchase_date'=> $order_date,
			'purchase_status'=> 'Pending',
			'venders_name'=> $vender_name,
			'branchname'=> $branch_name,
			'user_name'=> $full_name,
			'purchase_time'=>$order_time,
			'branch_id'=> $branch_id);
			$this->db->insert('purchase_order', $data);
			$pid = $this->db->insert_id();
			}
			
			if($pid)
			{
			//insert to sales price
			$icount = count($item_name);
			//echo $icount;
			for($i=0;$i<$icount;$i++)
			{
				$data2 = array(
					'item_name' => $item_name[$i],
					'unit_measurement' => $unit_measurements[$i],
					'required_qty' => $quantity[$i],
					'close_stock' => $close_stock[$i],
					'item_price' => $price[$i],
					'actual_amount' => $basic_amount[$i],
					'po_tax' => $tax[$i],
					'po_tax_amount' => $tax_amount[$i],
					'total_amount' => $total_amount[$i],
					'remarks' => $remarks[$i],
					'purchase_id' => $pid,
					'item_id' => $item_sid[$i],
					'cat_id' => $cat_id[$i],
					'purchase_order_status' => 'Pending',
					'itm_id' => $itm_id[$i]);
				$this->db->insert('purchase_order_item', $data2);
			}
		}
			//echo $pid;
			return $pid;
		}
		
		public function view_order($pid){
			$query = $this->db->query("select purchase_id,purchase_no,purchase_date,purchase_status,venders_name,branchname,user_name,purchase_time,branch_id from purchase_order where purchase_id='$pid'");
			return $query->result();
			
		}
		public function view_order_item($pid){
			//$query = $this->db->query("select item_name,unit_measurement,required_qty,close_stock,item_price,actual_amount,po_tax,po_tax_amount,total_amount,remarks,purchase_id,item_id,cat_id,purchase_order_status,itm_id from purchase_order_item where purchase_id='$pid'");
			$query = $this->db->query("SELECT *,ROUND(I.actual_amount,2) as actual_amount,ROUND(I.po_tax_amount,2) as po_tax_amount,ROUND(I.total_amount,2) as total_amount FROM  purchase_order P inner join purchase_order_item I on P.purchase_id=I.purchase_id where P.purchase_id='$pid'");
			return $query->result();
			
		}
		
		public function total_sum_amount($pid) {
			$query = $this->db->query("SELECT ROUND(SUM(total_amount),2) as `sum_amount`,ROUND(SUM(po_tax_amount),2) as `sum_tax_amount`,ROUND(SUM(actual_amount),2) as `sum_act_amount` FROM  purchase_order P inner join purchase_order_item I on P.purchase_id=I.purchase_id where P.purchase_id='$pid'");
			return $query->result();
		} 
		
		public function pending_purchase($branch_id){
			$query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(po_apporval_date, '%d-%m-%Y') as po_apporval_date FROM purchase_order  where purchase_status!='Closed' and branch_id='$branch_id' ORDER BY purchase_id desc");
			return $query->result();
		}
		
		/* ceo */
		public function all_pending_purchase(){
		    $branch_name = $_SESSION['branch_name'];
		    $cw_ceo = $_SESSION['cw_ceo'];
			if($cw_ceo=='CW')
            {
                $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(po_apporval_date, '%d-%m-%Y') as po_apporval_date FROM purchase_order  where purchase_status!='Closed' ORDER BY purchase_id desc");
            }
            else{
                 $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(po_apporval_date, '%d-%m-%Y') as po_apporval_date FROM purchase_order  where purchase_status!='Closed' and branchname='$cw_ceo' ORDER BY purchase_id desc");
            }
		    /*if($branch_name =='CW4_CEO'){
		        $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(po_apporval_date, '%d-%m-%Y') as po_apporval_date FROM purchase_order  where purchase_status!='Closed' and branchname='CW4' ORDER BY purchase_id desc");
		    }
		    else if($branch_name =='CW5_CEO'){
		        $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(po_apporval_date, '%d-%m-%Y') as po_apporval_date FROM purchase_order  where purchase_status!='Closed' and branchname='CW5' ORDER BY purchase_id desc");
		    }
		    else if($branch_name =='CW6_CEO'){
		        $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(po_apporval_date, '%d-%m-%Y') as po_apporval_date FROM purchase_order  where purchase_status!='Closed' and branchname='CW6' ORDER BY purchase_id desc");
		    }
		    else{
			    $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(po_apporval_date, '%d-%m-%Y') as po_apporval_date FROM purchase_order  where purchase_status!='Closed' ORDER BY purchase_id desc");
		    }*/
			return $query->result();
		}
		
		public function updatePurchase($postdata){
			extract($postdata);
			$pid = $purchase_id;
			$data = array(
			'purchase_date'=> $order_date,
			'venders_name'=> $vender_name,
			'purchase_id'=> $purchase_id);
			$this->db->where('purchase_id',$purchase_id);
			$this->db->update('purchase_order', $data);
			
			$icounts = count($item_name1);
			for($i=0;$i<$icounts;$i++)
			{
				$data2 = array(
					'item_name' => $item_name1[$i],
					'unit_measurement' => $unit_measurements1[$i],
					'required_qty' => $quantity1[$i],
					'close_stock' => $close_stock1[$i],
					'item_price' => $price1[$i],
					'actual_amount' => $basic_amount1[$i],
					'po_tax' => $tax1[$i],
					'po_tax_amount' => $tax_amount1[$i],
					'total_amount' => $total_amount1[$i],
					'remarks' => $remarks1[$i],
					'purchase_order_status' => 'Pending');
				$this->db->where('purchase_order_id',$purchase_order_id[$i]);
				$this->db->where('purchase_id',$purchase_id);				
				$this->db->update('purchase_order_item', $data2);
			}
			$icount = count($item_name);
			for($i=0;$i<$icount;$i++)
			{
				$data2 = array(
					'item_name' => $item_name[$i],
					'unit_measurement' => $unit_measurements[$i],
					'required_qty' => $quantity[$i],
					'close_stock' => $close_stock[$i],
					'item_price' => $price[$i],
					'actual_amount' => $basic_amount[$i],
					'po_tax' => $tax[$i],
					'po_tax_amount' => $tax_amount[$i],
					'total_amount' => $total_amount[$i],
					'remarks' => $remarks[$i],
					'purchase_id' => $purchase_id,
					'item_id' => $item_sid[$i],
					'cat_id' => $cat_id[$i],
					'purchase_order_status' => 'Pending',
					'itm_id' => $itm_id[$i]);
				$this->db->insert('purchase_order_item', $data2);
			}
			return $pid;
		}
		
		public function ordered_purchase_item($mid){
			$query = $this->db->query("SELECT *,ROUND(I.actual_amount,2) as actual_amount,ROUND(I.po_tax_amount,2) as po_tax_amount,ROUND(I.total_amount,2) as total_amount,(I.required_qty - I.grn_balance_qty) AS total_balance_qty FROM purchase_order P inner join purchase_order_item I on P.purchase_id=I.purchase_id inner join item_creation T on T.item_id=I.item_id where I.purchase_id='$mid'");
			return $query->result();
		}
		
		public function addGrn($postdata){
		    global $insertfiles;
			global $insertValuesSQL;
			extract($postdata);
			$k=0;
			$gtotal=0;
			$gtotal1=0;
			$gtotal2=0;
			for($j=0;$j<count($inward_quantity);$j++)
			{
				if($inward_quantity[$j]!='0' && $inward_quantity[$j]!='')
				{
					$k=$k+1;
					$totalprice[$j] = round(($inward_price[$j] * $inward_quantity[$j]),2);
            		$taxamt[$j] = round((($totalprice[$j] * $grn_tax[$j])/100),2);
            		$totalamount[$j] = round(($totalprice[$j] + $taxamt[$j]),2);
                    $gtotal=$gtotal+$totalamount[$j];
                    $gtotal1 = $gtotal + $courier + $transport + $insurance;
                    $gtotal2 = round((float)$gtotal1 + (float)$roundoff);
                }
			}
			if($k>0)
			{
				
				$branch_id = $_SESSION['branch_id'];							 
				$grn_date = date("Y-m-d", strtotime($grn_date));
			//	$ginv_date = date("Y-m-d", strtotime($grn_inv_date));
				if($grn_inv_date != '')
			    {
			        $ginv_date = date("Y-m-d", strtotime($grn_inv_date));
			    }
			    else
			    {
			        $ginv_date = $grn_inv_date;
			    }
				$order_time = date("h:i A");
				$lm=date("Y-m-dH:i:s",time());
				$this->db->select('MAX(gn_no) as gn_no',false);
				$this->db->where('branch_id',$branch_id);
				$result = $this->db->get('gnr_creation')->row();
				$gn_no = $result->gn_no;
				if($gn_no == 0){
					$grnno = 1;
				}
				else{
					$grnno = $gn_no+1;
				}
				$grn_no = "GRN"."/".$branchname."/".$grnno;
			    
				$randno=rand(10000, 990000);
				foreach($_FILES['in_upload']['name'] as $key=>$val){
					$extension = pathinfo($_FILES["in_upload"]["name"][$key], PATHINFO_EXTENSION);
					$fileName = 'uploads/'.rand(10000, 990000). "_". time().".".$extension;
					$insertfiles .= $fileName;
				}
				if(!empty(array_filter($_FILES['in_upload']['name']))){
					$data =array(
					'gn_no'=>$grnno,	
					'grn_no'=>$grn_no,
					'invoice_no'=>$grn_invoice,
					'grn_date'=>$grn_date,
					'gnr_status'=>'Pending',
					'branchname'=>$branchname,
					'vendor_name'=>$supplier_name,
					'purchase_id'=>$indent_id,
					'rjn_status'=>'Pending',
					'invoice_upload'=>$insertfiles,
					'courier'=>$courier,
					'transport'=>$transport,
					'insurance'=>$insurance,
					'total_amount'=>$gtotal2,
					'reject_amt'=>$gtotal2,
					'user_name'=>$user_name,
					'tot_amt'=>$gtotal1,
					'round_off'=>$roundoff,
					'grn_time'=>$order_time,
					'branch_id'=>$branch_id,
					'invoice_date'=>$ginv_date
					);
					$this->db->insert('gnr_creation', $data);
				}
				else
				{
					$data1 =array('gn_no'=>$grnno,
					'grn_no'=>$grn_no,
					'invoice_no'=>$grn_invoice,
					'grn_date'=>$grn_date,
					'gnr_status'=>'Pending',
					'branchname'=>$branchname,
					'vendor_name'=>$supplier_name,
					'purchase_id'=>$indent_id,
					'rjn_status'=>'Pending',
					'courier'=>$courier,
					'transport'=>$transport,
					'insurance'=>$insurance,
					'total_amount'=>$gtotal2,
					'reject_amt'=>$gtotal2,
					'user_name'=>$user_name,
					'tot_amt'=>$gtotal1,
					'round_off'=>$roundoff,
					'grn_time'=>$order_time,
					'branch_id'=>$branch_id,
					'invoice_date'=>$ginv_date
					);
					$this->db->insert('gnr_creation', $data1);
				}
					$gid = $this->db->insert_id();
					
					function compressImage($source, $destination, $quality) {

						$info = getimagesize($source);

						if ($info['mime'] == 'image/jpeg') 
						$image = imagecreatefromjpeg($source);

						elseif ($info['mime'] == 'image/gif') 
						$image = imagecreatefromgif($source);

						elseif ($info['mime'] == 'image/png') 
						$image = imagecreatefrompng($source);

						imagejpeg($image, $destination, $quality);

					}
					
					$folderName = date('M-Y');
					$targetDir = "uploads/".$folderName."/";
					if ( ! file_exists($targetDir) )
                    {
                        $create = mkdir($targetDir, 0777, TRUE);
                        $createThumbsFolder = mkdir($targetDir . '/thumbs', 0777, TRUE);
                        if ( ! $create || ! $createThumbsFolder)
                        return;
                    }
					$allowTypes = array('jpg','png','jpeg','gif');
			
					//$statusMsg = $errorMsg = $insertValuesSQL = $errorUpload = $errorUploadType = '';
					if(!empty(array_filter($_FILES['in_upload']['name']))){
						foreach($_FILES['in_upload']['name'] as $key=>$val){
							// File upload path
							//$fileName = basename($_FILES['in_upload']['name'][$key]);
							$extension = pathinfo($_FILES["in_upload"]["name"][$key], PATHINFO_EXTENSION);
							$fileName = rand(10000, 990000). "_". time().".".$extension;
							//$fileName1 = $grn_no . $fileName;
							$targetFilePath = $targetDir . $fileName;
						
							// Check whether file type is valid
							$fileType = pathinfo($_FILES["in_upload"]["name"][$key],PATHINFO_EXTENSION);
							//$fileType = pathinfo($targetFilePath,PATHINFO_EXTENSION);
							if(in_array($fileType, $allowTypes)){
								// Upload file to server
								if(move_uploaded_file($_FILES["in_upload"]["tmp_name"][$key], $targetFilePath)){
								$location = "uploads/".$folderName."/".$fileName;
									compressImage($targetFilePath,$location,25);
									// Image db insert sql
									$insertValuesSQL .= "('".$location."', NOW(), '".$gid."','Grn Status'),";
								}else{
									//$errorUpload .= $_FILES['in_upload']['name'][$key].', ';
								}
							}else{
								//$errorUploadType .= $_FILES['in_upload']['name'][$key].', ';
							}
						}
					
					$insertValuesSQL = trim($insertValuesSQL,',');
					$this->db->query("INSERT INTO uploaded_invoice (file_name, uploaded_on,invoice_ref_id,gstatus) VALUES $insertValuesSQL");
						//
					}
					/*if(!empty($_FILES['in_upload']['name'])){
						$config['upload_path'] = 'upload/';
						$config['allowed_types'] = 'jpg|jpeg|png|gif|JPG|JPEG|PNG|GIF';
						$filename = $_FILES['in_upload']['name'];
						$extension = pathinfo($_FILES['in_upload']['name'], PATHINFO_EXTENSION);
					    $file_name = 'upload/'.$randno. "_". time().".".$extension;
					    $file_name1 = $randno. "_". time().".".$extension;
						//$file_name = 'upload/'.$_FILES['in_upload']['name'];
						$config['file_name'] = $file_name1;
						//Load upload library and initialize configuration
						$this->load->library('upload',$config);
						$this->upload->initialize($config);
						
						if($this->upload->do_upload('in_upload')){
							
							$data = $this->upload->data();  
							 $config['image_library'] = 'gd2';  
							 $config['source_image'] = 'upload/'.$data["file_name"];  
							 $config['create_thumb'] = FALSE;  
							 $config['maintain_ratio'] = FALSE;  
							 $config['quality'] = '80%';  
							 $config['width'] = 750;  
							 $config['height'] = 750;  
							 $config['new_image'] = 'upload/'.$data["file_name"];  
							 $this->load->library('image_lib', $config);  
							 $this->image_lib->resize();  
							 $image_data = array(  
								  'file_name' => $file_name,
								  'uploaded_on'=>date('Y-m-d H:i:s'),
								   'invoice_ref_id'=>$gid,
								   'gstatus'=>'Grn Status'
								  );  
							  $this->db->insert("uploaded_invoice", $image_data); 
							}else{
							$in_upload = '';
						}
					}else{
						$in_upload = '';
					}*/
					
					
					if($gid)
					{
					$gtotal=0;	
					for($i=0;$i<count($inward_quantity);$i++) {
					    if($inward_quantity[$i]!='0' && $inward_quantity[$i]!='')
				        {
						
						$q1 = $this->db->query("select * from item_creation where item_id='$item_sid[$i]'");
						
						if($q1->num_rows() > 0){
						{
							foreach($q1->result_array() as $row)
							$stock_item[$i] = $row['stock_item'];
							$itm_id[$i] = $row['itm_id'];
							$cat_id[$i] = $row['category_id'];
							
						
							$qty[$i] = (float)$inward_quantity[$i] + (float)$grn_updated[$i];
							$opeing_stock[$i] = (float)$inward_quantity[$i] + (float)$stock_item[$i];
							$grannd_amount[$i] = (float)$tax_amount[$i] + (float)$inward_total_amount[$i];
							$closing_stock[$i] = (float)$inward_quantity[$i] + (float)$stock_item[$i];//closing stock of particular item
							$gtotal=(float)$gtotal+(float)$grannd_amount[$i];
							
							$uname = $_SESSION['username'];
							$login_id = $_SESSION['user_id'];
							
							if($expiry_date[$i] != '')
            			    {
            			        $exp_date = date("Y-m-d", strtotime($expiry_date[$i]));
            			    }
            			    else
            			    {
            			        $exp_date = $expiry_date[$i];
            			    }
						
						$data2 =  array('grn_item' => $item_name[$i],
							'grn_item_qty' =>$inward_quantity[$i],
							'grn_item_price' =>$inward_price[$i],
							'grn_tax' =>$grn_tax[$i],
							'grn_tax_amount' =>$tax_amount[$i],
							'grn_total_amount' =>$inward_total_amount[$i],
							'grn_grand' =>$grannd_amount[$i],
							'grn_remarks' =>$remarks[$i],
							'gnr_id' =>$gid,
							'purchase_order_id' =>$item_id[$i],
							'rjn_status' =>'Pending',
							'item_id' =>$item_sid[$i],
							'itm_id' =>$itm_id[$i],
							'category_id' =>$cat_id[$i],
							'expiry_date' => $exp_date
							);
						$this->db->insert('purchase_order_grn', $data2);
						$last_id = $this->db->insert_id();
						
						if($remarks[$i] != '')
						{
							$data3 =  array('last_remark'=>$lm,
								'frm_user'=>$uname,
								'remark'=>$remarks[$i],
								'item_id'=>$item_sid[$i],
								'gnr_id'=>$gid,
								'purchase_grn_id'=>$last_id,
								'login_id'=>$login_id,
								'status'=>'UnSeen');
							$this->db->insert('remarks', $data3);
						}
						$grn_month = date('Y-m',strtotime($grn_date));	
						$data4 =  array('item_name'=>$item_name[$i],
							'uom'=>$uom[$i],
							'grn_price'=>$inward_price[$i],
							'grn_tax'=>$grn_tax[$i],
							'grn_date'=>$grn_date,
							'grn_month'=>$grn_month,
							'purchase_grn_id'=>$last_id,
							'branchname'=>$branchname,
							'item_id'=>$item_sid[$i],
							'itm_id'=>$itm_id[$i],
							'category_id'=>$cat_id[$i],
							'branch_id'=>$branch_id);
						$this->db->insert('grn_price', $data4);	

						$data5 =  array('grn_balance_qty'=>$qty[$i],
							'grn_last_price'=>$inward_price[$i],
							'grn_last_tax'=>$grn_tax[$i],
							'grn_last_amount'=>$inward_total_amount[$i],
							'grn_last_remarks'=>$remarks[$i]);
							$this->db->where('purchase_order_id', $item_id[$i]);
							$this->db->update('purchase_order_item', $data5);	

						if($inward_quantity[$i]!='')
						{
							$data6 = array('item_price'=>$inward_price[$i],
								'item_tax'=>$grn_tax[$i],
								'stock_item'=>$opeing_stock[$i],
								'vendor_name'=>$supplier_name); 
							$this->db->where('item_id', $item_sid[$i]);
							$this->db->update('item_creation', $data6);
						}
						
						
						$q3 = $this->db->query("select * from purchase_order_grn where grn_item_qty='0' and gnr_id='$gid'");
						if($q3->num_rows() > 0)
						{
							//if (empty($inward_quantity[$i])) {
							$this->db->query("DELETE FROM grn_price WHERE purchase_grn_id='$last_id'");
							$this->db->query("DELETE FROM purchase_order_grn WHERE purchase_grn_id='$last_id' and grn_item_qty='0' and gnr_id='$gid'");
							
						}
						if($inward_quantity[$i]!='0' && $inward_quantity[$i]!='')
						{
							 $data7 = array('item_name'=>$item_name[$i],
								'trans_ref_no'=>$grn_no,
								'trans_type'=>'GRN',
								'transaction_date'=>$grn_date,
								'opening_stock'=>$stock_item[$i],
								'in_qty'=>$inward_quantity[$i],
								'closing_stock'=>$closing_stock[$i],
								'last_modified'=>$lm,
								'branchname'=>$branchname,
								'item_id'=>$item_sid[$i],
								'itm_id'=>$itm_id[$i],
								'category_id'=>$cat_id[$i],
								'branch_id'=>$branch_id);
							 $this->db->insert('stock_detail', $data7);	

						}
						if($qty[$i] > $quantity[$i] && $inward_quantity[$i]!='') {
							$status = "Approved";
							$this->db->query("update purchase_order_item set purchase_order_status='$status' where purchase_order_id='$item_id[$i]'");				
						}
						else if($qty[$i] < $quantity[$i] && $inward_quantity[$i]!='') {
							$status = "Approved";
							$this->db->query("update purchase_order_item set purchase_order_status='$status' where purchase_order_id='$item_id[$i]'");						
						}
						else if($qty[$i] == $quantity[$i] && $inward_quantity[$i]!='') {
							$status = "Closed";
							$this->db->query("update purchase_order_item set purchase_order_status='$status' where purchase_order_id='$item_id[$i]'");
						}
						$r4 = $this->db->query("SELECT count(*) as `num` FROM purchase_order_item where purchase_id='$indent_id'");
						
						foreach($r4->result_array() as $row4)
						
						$count1 = $row4['num'];
						
						$r5 = $this->db->query("SELECT count(*) as `num1` FROM purchase_order_item where purchase_order_status='Closed' and purchase_id='$indent_id'");

						
						foreach($r5->result_array() as $row5)
						
						$count2 = $row5['num1'];
						
						
						if($count1 == $count2 ) {
						
							$status = "Closed";
							$this->db->query("update purchase_order set purchase_status='$status' where purchase_id='$indent_id'");

						}
						else {
							
							$status = "Approved";
						
							$this->db->query("update purchase_order set purchase_status='$status' where purchase_id='$indent_id'");
						}
					}
				}
					}
			}
			}
		}
		//echo $gid;
		return $gid;
	}

	public function get_grndata($gid) {
		$query = $this->db->query("SELECT *,ROUND(G.total_amount,2) as total_amount,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation  G on P.purchase_id=G.purchase_id where G.gnr_id='$gid'");
		return $query->result();
	}
	public function get_grn_item($gid) {
		$query = $this->db->query("SELECT *,ROUND(R.grn_tax_amount,2) as grn_tax_amount,ROUND(R.grn_grand,2) as grn_grand FROM purchase_order P inner join purchase_order_item I on P.purchase_id=I.purchase_id inner join purchase_order_grn R on I.purchase_order_id=R.purchase_order_id where R.gnr_id='$gid' and R.grn_item_qty !=''");
		return $query->result();
	}

	public function grn_total_amount($gid) {
		$query = $this->db->query("SELECT ROUND(SUM(O.grn_grand),2) as sum_amount,ROUND(SUM(O.grn_tax_amount),2) as sum_tax_amount FROM  gnr_creation G inner join purchase_order_grn O on G.gnr_id=O.gnr_id where G.gnr_id='$gid'");
		return $query->result();
	}
	public function get_uploaded_invoice($gid)
	{
		$query = $this->db->query("select * from uploaded_invoice where invoice_ref_id='$gid'");
		return $query->result();
	}
	
	public function getPoHistory($item_id,$bname)
	{
		$query = $this->db->query("SELECT DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,G.grn_no,GI.grn_item_price,GI.grn_item_qty,G.vendor_name from gnr_creation as G,purchase_order_item as PI,purchase_order_grn as GI where PI.item_id ='$item_id' and G.purchase_id=PI.purchase_id and PI.purchase_order_id=GI.purchase_order_id and G.branchname='$bname' order by G.grn_date desc limit 10");
		return $query->result();
	}

	public function get_grn_closed_report($mid) {
		$query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid,round(sum(O.grn_grand),2) as sum_amount,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join purchase_order_grn O on G.gnr_id=O.gnr_id where G.purchase_id='$mid' group by O.gnr_id ORDER BY G.gnr_id desc");
		return $query->result();
	}

	public function get_ordered_gnr($mid) {
		$query = $this->db->query("SELECT *,ROUND(G.total_amount,2) as total_amount,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM  purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.gnr_id='$mid'");
		return $query->result();
	}
	public function get_ordered_gnr_item($mid) {
		$query = $this->db->query("SELECT *,ROUND(I.actual_amount,2) as actual_amount,ROUND(I.po_tax_amount,2) as po_tax_amount,ROUND(I.total_amount,2) as total_amount,ROUND(O.grn_total_amount,2) as grn_total_amount,ROUND(O.grn_tax_amount,2) as grn_tax_amount,ROUND(O.grn_grand,2) as grn_grand FROM purchase_order P inner join purchase_order_item I on P.purchase_id=I.purchase_id inner join purchase_order_grn O on I.purchase_order_id=O.purchase_order_id where O.gnr_id='$mid' and grn_item_qty !=''");
		return $query->result();
	}

	public function grn_sumamount($mid) {
		$query = $this->db->query("SELECT *,ROUND(sum(I.actual_amount),2) as sum_actual_amount,ROUND(sum(I.po_tax_amount), 2) as sum_po_tax_amount,ROUND(sum(I.total_amount), 2) as sum_total_amount,ROUND(sum(O.grn_total_amount),2) as sum_grn_total_amount,ROUND(sum(O.grn_grand), 2) as `sum_amount`, ROUND(sum(O.grn_tax_amount), 2) as `sum_tax_amount` FROM purchase_order P inner join purchase_order_item I on P.purchase_id=I.purchase_id inner join purchase_order_grn O on I.purchase_order_id=O.purchase_order_id where O.gnr_id='$mid' and grn_item_qty !=''");
		return $query->result();
	}
	
	public function get_pending_grn() {
	    $branch_name = $_SESSION['branch_name'];
	    $cw_ceo = $_SESSION['cw_ceo'];
		if($cw_ceo=='CW')
        {
             $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and (G.gnr_status='Pending' or G.gnr_status='Holded') and G.branchname!='CW4' and G.branchname!='CW5' and G.branchname!='CW6' ORDER BY G.gnr_id desc");
        }
        else{
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and (G.gnr_status='Pending' or G.gnr_status='Holded') and G.branchname='$cw_ceo' ORDER BY G.gnr_id desc");
        }
        /*if($branch_name=='CW4_CEO')
        {
		    $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and (G.gnr_status='Pending' or G.gnr_status='Holded') and G.branchname='CW4' ORDER BY G.gnr_id desc");
        }
        else if($branch_name=='CW5_CEO')
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and (G.gnr_status='Pending' or G.gnr_status='Holded') and G.branchname='CW5' ORDER BY G.gnr_id desc");
        }
        else if($branch_name=='CW6_CEO')
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and (G.gnr_status='Pending' or G.gnr_status='Holded') and G.branchname='CW6' ORDER BY G.gnr_id desc");
        }
        else
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and (G.gnr_status='Pending' or G.gnr_status='Holded') and G.branchname!='CW4' and G.branchname!='CW5' ORDER BY G.gnr_id desc");
        }*/
		return $query->result();
	}
	
	public function grn_AllGrnList() {
	    $branch_name = $_SESSION['branch_name'];
	    $cw_ceo = $_SESSION['cw_ceo'];
		if($cw_ceo=='CW')
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and (G.gnr_status='Pending' or G.gnr_status='Holded') and G.branchname!='CW4' ORDER BY G.gnr_id desc");
        }
        else{
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and G.branchname='$cw_ceo' ORDER BY G.gnr_id desc");
        }
        /*if($branch_name=='CW4_CEO')
        {
		    $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and G.branchname='CW4' ORDER BY G.gnr_id desc");
        }
        else if($branch_name=='CW5_CEO')
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and G.branchname='CW5' ORDER BY G.gnr_id desc");
        }
        else if($branch_name=='CW6_CEO')
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and G.branchname='CW6' ORDER BY G.gnr_id desc");
        }
        else
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and (G.gnr_status='Pending' or G.gnr_status='Holded') and G.branchname!='CW4' ORDER BY G.gnr_id desc");
        }*/
		return $query->result();
	}
	
	public function get_dgrn() {
		$query = $this->db->query("SELECT *, DATE_FORMAT(grn_date, '%d-%m-%Y') as grn_date FROM gnr_creation where branchname='CW5' and purchase_id IS NULL ORDER BY gnr_id desc");
		return $query->result();
	}
	
	public function get_approved_grn() {
	    $branch_name = $_SESSION['branch_name'];
	    $cw_ceo = $_SESSION['cw_ceo'];
		if($cw_ceo=='CW')
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.gnr_status='Approved' and G.branchname!='CW4' ORDER BY G.gnr_id desc");
        }
        else{
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.gnr_status='Approved' and G.branchname='$cw_ceo' ORDER BY G.gnr_id desc");
        }
        /*if($branch_name=='CW4_CEO')
        {
		    $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.gnr_status='Approved' and G.branchname='CW4' ORDER BY G.gnr_id desc");
        }
        else if($branch_name=='CW5_CEO')
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.gnr_status='Approved' and G.branchname='CW5' ORDER BY G.gnr_id desc");
        }
        else if($branch_name=='CW6_CEO')
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.gnr_status='Approved' and G.branchname='CW6' ORDER BY G.gnr_id desc");
        }
        else
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.gnr_status='Approved' and G.branchname!='CW4' ORDER BY G.gnr_id desc");
        }*/
		return $query->result();
	}
	
	public function get_cw5_grn() {
	    $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.branchname='CW5' ORDER BY G.gnr_id desc");
       	return $query->result();
	}
	
	public function get_holded_grn($branch_id) {
		$query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date,G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.branch_id='$branch_id' and G.gnr_status='Holded' ORDER BY G.gnr_id desc");
		return $query->result();
	}

	public function rejection_view($mid) {
		$query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date, DATE_FORMAT(C.rejection_date, '%d-%m-%Y') as rejection_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join rejection_creation C on G.gnr_id=C.gnr_id  where C.gnr_id='$mid' ORDER BY C.rejection_id desc");
		return $query->result();
	}
	
	public function rejection_view_detail($rid) {
		$query = $this->db->query("SELECT *,ROUND(J.rejection_total_amount,2) as rejection_total_amount FROM purchase_order P inner join purchase_order_item  I on  P.purchase_id=I.purchase_id inner join purchase_order_grn O on I.purchase_order_id=O.purchase_order_id inner join  purchase_order_rejection J on O.purchase_grn_id=J.purchase_grn_id where J.rejection_id='$rid' and J.rejection_qty !=''");
		return $query->result();
	}

	public function ordered_gnr_approve_id($mid) {
		$query = $this->db->query("SELECT * FROM purchase_order P join gnr_creation G on P.purchase_id=G.purchase_id where G.gnr_id='$mid'");
		return $query->result();
	}
	
	public function get_rjection_purchase_id($mid) {
		$query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.gnr_id='$mid'");
		return $query->result();
	}
	public function get_ordered_rejection_item_id($mid) {
		$query = $this->db->query("SELECT *, (O.grn_item_qty-O.rejected_qty) as rejection_bal_qty FROM purchase_order P inner join purchase_order_item  I on  P.purchase_id=I.purchase_id inner join purchase_order_grn O on I.purchase_order_id=O.purchase_order_id where O.gnr_id='$mid'");
		return $query->result();
	}
	
	public function addRejection($postdata){
			extract($postdata);
			$branch_id = $_SESSION['branch_id'];
	
			$rejection_date = date("Y-m-d", strtotime($rejection_date));
			$order_time = date("h:i A");
			$lm=date("Y-m-dH:i:s",time());
			
			$this->db->select('MAX(rn_no) as rn_no',false);
			$this->db->where('branch_id',$branch_id);
			$result = $this->db->get('rejection_creation')->row();
			/*if($result->num_rows()>0)
			{*/
				$rnno = $result->rn_no;
				if($rnno == 0){
					$rn_no = 1;
				}
				else{
					$rn_no = $rnno+1;
				}
				$rejection_no = "RJN"."/".$branchname."/".$rn_no;
			//}
			$k=0;
			for($j=0;$j<count($rjn_quantity);$j++)
			{
				if($rjn_quantity[$j]!='0' && $rjn_quantity[$j]!='')
				{
					$k=$k+1;
				}
			}
			if($k>0)
			{
			$this->db->query("insert into rejection_creation (rn_no,rejection_no,rejection_date,branchname,gnr_id,user_name,rej_amt,rtime,branch_id) values ('$rn_no','$rejection_no','$rejection_date','$branchname','$gnr_id','$uname','$rej_amt','$order_time','$branch_id')");
			$rid = $this->db->insert_id();
			}
			if(isset($rid))
			{
			for($i=0;$i<count($item_name),$i<count($rjn_quantity),$i<count($rejected_qty),$i<count($rjn_price),$i<count($rjn_tax),$i<count($rjn_tax_amount),$i<count($rjn_inward_grand_amount),$i<count($rjn_remarks),$i<count($gnr_po_id);$i++) {
				
				$rejctd_qty[$i] = (float)$rjn_quantity[$i] + (float)$rejected_qty[$i];//rejected qty
				$sq3 = $this->db->query("select * from purchase_order_grn where purchase_grn_id='$gnr_po_id[$i]'");				
				$re3 = $sq3->row_array();
				$rjn_amt[$i] = (float)$re3['grn_grand']-(float)$rjn_inward_grand_amount[$i];
				$final_grand[$i]=(float)$re3['grn_grand']-(float)$re3['rejected_amt'];
				$grn_qtys =(float)$re3['grn_item_qty']-(float)$rejctd_qty[$i];
				
				$this->db->query("update purchase_order_grn set rejected_qty='$rejctd_qty[$i]',rejected_amt='$rjn_inward_grand_amount[$i]',final_grn_amt='$rjn_amt[$i]' where purchase_grn_id='$gnr_po_id[$i]'");				
				$this->db->query("update grn_price set grn_tot_qty='$grn_qtys',rejected_qty='$rejctd_qty[$i]',rejected_amt='$rjn_inward_grand_amount[$i]',final_grn_amt='$rjn_amt[$i]' where purchase_grn_id='$gnr_po_id[$i]'");
				//$this->db->query("update purchase_order_grn set rejected_qty='$rejctd_qty[$i]' where purchase_grn_id='$gnr_po_id[$i]'");
				
				$sql3 = $this->db->query("select * from item_creation where itm_id='$itm_no[$i]' and branchname='$branchname'");
				if($sql3->num_rows() > 0){
					//foreach($sql3->result_array() as $res)
					$res = $sql3->row_array();		
					$qty[$i] = $res['stock_item'];//opening stock
					$qty1[$i] = (float)$qty[$i] - (float)$rjn_quantity[$i];//closing stock
				    $item_id[$i] = $res['item_id'];
				    $itm_id[$i] = $res['itm_id'];
				    $cat_id[$i] = $res['category_id'];
					
					$this->db->query("UPDATE item_creation set stock_item='$qty1[$i]' WHERE item_name='$item_name[$i]' and branchname='$branchname'");
					//$sql5 = mysqli_query($this->con,"insert into stock_detail(item_name,trans_ref_no,transaction_date,opening_stock,out_qty,closing_stock)values('$item_name[$i]','$rejection_no','$rejection_date','$qty[$i]','$rjn_quantity[$i]','$qty1[$i]')");
				}
				$this->db->query("insert into  purchase_order_rejection(rejection_qty,rejection_price,rejection_tax,rejection_tax_amount,rejection_total_amount,rejection_remarks,rejection_id,purchase_grn_id,item_id,itm_id,category_id) values ('$rjn_quantity[$i]','$rjn_price[$i]','$rjn_tax[$i]','$rjn_tax_amount[$i]','$rjn_inward_grand_amount[$i]','$rjn_remarks[$i]','$rid','$gnr_po_id[$i]','$item_id[$i]','$itm_id[$i]','$cat_id[$i]')");
				$last_id = $this->db->insert_id();
				if($rjn_quantity[$i]!='0' && $rjn_quantity[$i]!='')
				{
					$this->db->query("insert into stock_detail(item_name,trans_ref_no,transaction_date,opening_stock,out_qty,closing_stock,last_modified,branchname,item_id,itm_id,category_id,branch_id)values('$item_name[$i]','$rejection_no','$rejection_date','$qty[$i]','$rjn_quantity[$i]','$qty1[$i]','$lm','$branchname','$item_id[$i]','$itm_id[$i]','$cat_id[$i]','$branch_id')");
				}
				
				$sql6 = $this->db->query("select grn_item_qty,rejected_qty from purchase_order_grn where purchase_grn_id='$gnr_po_id[$i]'");
				if($sql6->num_rows() > 0){
					
					$res2 = $sql6->row_array();	
					$grn_qty[$i] = $res2['grn_item_qty'];
					$rej_qty[$i] = $res2['rejected_qty'];
					
					if($grn_qty[$i] == $rej_qty[$i] && (float)$rjn_quantity[$i]!='') {
						$status = "Closed";
						$this->db->query("update purchase_order_grn set rjn_status='$status' where purchase_grn_id='$gnr_po_id[$i]'");
					}
					else {
						//echo "failure|0";
					}
				}
				
				$sql8 = $this->db->query("SELECT count(*) as `num` FROM purchase_order_grn where gnr_id='$gnr_id'");
				
				foreach($sql8->result_array() as $row)
				$count1 = $row['num'];
				
				$sql9 = $this->db->query("SELECT count(*) as `num1` FROM purchase_order_grn where rjn_status='Closed' and gnr_id='$gnr_id'");
				
				foreach($sql9->result_array() as $row1)
				$count2 = $row1['num1'];
				
				
				if($count1 == $count2 ) {
				
					$status = "Closed";
					$sql10 = $this->db->query("update gnr_creation set rjn_status='$status' where gnr_id='$gnr_id'");
					
				}
			
			}
			$sql11 = $this->db->query("select * from gnr_creation where gnr_id='$gnr_id'");
			if($sql11->num_rows()>0)
			{
				foreach($sql11->result_array() as $res12)
				$total = $res12['reject_amt'];
				$update_total = $total - $rej_amt;
				$this->db->query("update gnr_creation set reject_amt='$update_total' where gnr_id='$gnr_id'");
			}
	}
		//echo $rid;
		if(isset($rid))
		{
		    return $rid;
		}
	}
	
	public function get_rejection_receipt_details($rejection_id) {
		$query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date, DATE_FORMAT(C.rejection_date, '%d-%m-%Y') as rejection_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join rejection_creation C on G.gnr_id=C.gnr_id  where C.rejection_id='$rejection_id'");
		return $query->result();
		
	}
	
	public function get_receipt_rejection_print_list($rejection_id) {
		$query = $this->db->query("SELECT *,ROUND(J.rejection_total_amount,2) as rejection_total_amount FROM purchase_order P inner join purchase_order_item  I on  P.purchase_id=I.purchase_id inner join purchase_order_grn O on I.purchase_order_id=O.purchase_order_id inner join  purchase_order_rejection J on O.purchase_grn_id=J.purchase_grn_id where J.rejection_id='$rejection_id' and J.rejection_qty !=''");
		return $query->result();
	}
	
	public function total_sum_rejection_amount($rejection_id) {
		$query = $this->db->query("SELECT ROUND(SUM(D.rejection_total_amount),2) as sum_amount FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join rejection_creation C on G.gnr_id=C.gnr_id inner join purchase_order_rejection D on C.rejection_id=D.rejection_id where C.rejection_id='$rejection_id'");
		return $query->result();
	}
	
	public function get_rejection_list($branch_id) {
		$query = $this->db->query("SELECT *,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,C.rejection_id as mid, DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(C.rejection_date, '%d-%m-%Y') as rejection_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join rejection_creation C on G.gnr_id=C.gnr_id where C.branch_id='$branch_id' ORDER BY C.rejection_id desc");
		return $query->result();
	}
	
	public function get_ceorejection_list() {
	    $branch_name = $_SESSION['branch_name'];
	    $cw_ceo = $_SESSION['cw_ceo'];
		if($cw_ceo=='CW')
        {
            $query = $this->db->query("SELECT *,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,C.rejection_id as mid, DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(C.rejection_date, '%d-%m-%Y') as rejection_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join rejection_creation C on G.gnr_id=C.gnr_id where C.branchname!='CW4' ORDER BY C.rejection_id desc");
        }
        else{
            $query = $this->db->query("SELECT *,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,C.rejection_id as mid, DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(C.rejection_date, '%d-%m-%Y') as rejection_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join rejection_creation C on G.gnr_id=C.gnr_id where C.branchname='$cw_ceo' ORDER BY C.rejection_id desc");
        }
		/*if($branch_name =='CW4_CEO'){
	        $query = $this->db->query("SELECT *,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,C.rejection_id as mid, DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(C.rejection_date, '%d-%m-%Y') as rejection_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join rejection_creation C on G.gnr_id=C.gnr_id where C.branchname='CW4' ORDER BY C.rejection_id desc");
		}
		else if($branch_name =='CW5_CEO'){
		    $query = $this->db->query("SELECT *,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,C.rejection_id as mid, DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(C.rejection_date, '%d-%m-%Y') as rejection_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join rejection_creation C on G.gnr_id=C.gnr_id where C.branchname='CW5' ORDER BY C.rejection_id desc");
		}
		else if($branch_name =='CW6_CEO'){
		    $query = $this->db->query("SELECT *,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,C.rejection_id as mid, DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(C.rejection_date, '%d-%m-%Y') as rejection_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join rejection_creation C on G.gnr_id=C.gnr_id where C.branchname='CW6' ORDER BY C.rejection_id desc");
		}
		else{
		    $query = $this->db->query("SELECT *,DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date,C.rejection_id as mid, DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(C.rejection_date, '%d-%m-%Y') as rejection_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id inner join rejection_creation C on G.gnr_id=C.gnr_id where C.branchname!='CW4' ORDER BY C.rejection_id desc");
		}*/
		return $query->result();
	}
	
	public function grn_in_upload($postdata){
		
		extract($postdata);
		
		
		    function compressImage($source, $destination, $quality) {
				$info = getimagesize($source);
				if ($info['mime'] == 'image/jpeg') 
				$image = imagecreatefromjpeg($source);
				elseif ($info['mime'] == 'image/gif') 
				$image = imagecreatefromgif($source);
				elseif ($info['mime'] == 'image/png') 
				$image = imagecreatefrompng($source);
				imagejpeg($image, $destination, $quality);
			}
				
			$insertValuesSQL = $insertfiles = '';
			$randno=rand(10000, 990000);
			
			foreach($_FILES['grn_in_upload']['name'] as $key=>$val){
				$extension = pathinfo($_FILES["grn_in_upload"]["name"][$key], PATHINFO_EXTENSION);
				$fileName = 'uploads/'.rand(10000, 990000). "_". time().".".$extension;
				$insertfiles .= $fileName;
			}		
			if(!empty(array_filter($_FILES['grn_in_upload']['name']))){
				$this->db->query("update gnr_creation set invoice_upload='$insertfiles' where gnr_id='$gid'");
			}
			
			//invoice upload
			$folderName = date('M-Y');
			$targetDir = "uploads/".$folderName."/";
			if ( ! file_exists($targetDir) )
            {
                $create = mkdir($targetDir, 0777, TRUE);
                $createThumbsFolder = mkdir($targetDir . '/thumbs', 0777, TRUE);
                if ( ! $create || ! $createThumbsFolder)
                return;
            }
			$allowTypes = array('jpg','png','jpeg','gif');
    
			if(!empty(array_filter($_FILES['grn_in_upload']['name']))){
				foreach($_FILES['grn_in_upload']['name'] as $key=>$val){
					// File upload path
					//$fileName = basename($_FILES['grn_in_upload']['name'][$key]);
					$extension = pathinfo($_FILES["grn_in_upload"]["name"][$key], PATHINFO_EXTENSION);
					$fileName = rand(10000, 990000). "_". time().".".$extension;
					//$fileName1 = $grn_no . $fileName;
					$targetFilePath = $targetDir . $fileName;
				
					// Check whether file type is valid
					$fileType = pathinfo($_FILES["grn_in_upload"]["name"][$key],PATHINFO_EXTENSION);
					//$fileType = pathinfo($targetFilePath,PATHINFO_EXTENSION);
					if(in_array($fileType, $allowTypes)){
						// Upload file to server
						if(move_uploaded_file($_FILES["grn_in_upload"]["tmp_name"][$key], $targetFilePath)){
						$location = "uploads/".$folderName."/".$fileName;
							compressImage($targetFilePath,$location,25);
							// Image db insert sql
							$insertValuesSQL .= "('".$location."', NOW(), '".$gid."','Grn Status'),";
						}else{
							//$errorUpload .= $_FILES['grn_in_upload']['name'][$key].', ';
						}
					}else{
						//$errorUploadType .= $_FILES['grn_in_upload']['name'][$key].', ';
					}
				}
			
			$insertValuesSQL = trim($insertValuesSQL,',');
			$this->db->query("INSERT INTO uploaded_invoice (file_name, uploaded_on,invoice_ref_id,gstatus) VALUES $insertValuesSQL");
			}
		/*if(!empty($_FILES['grn_in_upload']['name'])){
			$config['upload_path'] = 'upload/';
			$config['allowed_types'] = 'jpg|jpeg|png|gif|JPG|JPEG|PNG|GIF';
			$filename = 'upload/'.$_FILES['grn_in_upload']['name'];
		}
		$this->db->query("update gnr_creation set invoice_upload='$filename' where gnr_id='$gid'");
		
		if(!empty($_FILES['grn_in_upload']['name'])){
				$config['upload_path'] = 'upload/';
				$config['allowed_types'] = 'jpg|jpeg|png|gif|JPG|JPEG|PNG|GIF';
				$filename = $_FILES['grn_in_upload']['name'];
				$file_name = 'upload/'.$_FILES['grn_in_upload']['name'];
				$config['file_name'] = $filename;
				//Load upload library and initialize configuration
				$this->load->library('upload',$config);
				$this->upload->initialize($config);
				
				if($this->upload->do_upload('grn_in_upload')){
					
					$data = $this->upload->data();  
					 $config['image_library'] = 'gd2';  
					 $config['source_image'] = 'upload/'.$data["file_name"];  
					 $config['create_thumb'] = FALSE;  
					 $config['maintain_ratio'] = FALSE;  
					 $config['quality'] = '80%';  
					 $config['width'] = 750;  
					 $config['height'] = 750;  
					 $config['new_image'] = 'upload/'.$data["file_name"];  
					 $this->load->library('image_lib', $config);  
					 $this->image_lib->resize();  
					 $image_data = array( 
						  'file_name' => $file_name,
						  'uploaded_on'=>date('Y-m-d H:i:s'),
						   'invoice_ref_id'=>$gid,
						   'gstatus'=>'Grn Status'
						  );  
					  $this->db->insert("uploaded_invoice", $image_data); 
				}else{
					$in_upload = '';
				}
			}else{
				$in_upload = '';
			}*/
		
		return $gid;	
	}
	
	public function add_DRejection($postdata){
			extract($postdata);
			$branch_id = $_SESSION['branch_id'];
	
			$rejection_date = date("Y-m-d", strtotime($rejection_date));
			$order_time = date("h:i A");
			$lm=date("Y-m-dH:i:s",time());
			
			$this->db->select('MAX(rn_no) as rn_no',false);
			$this->db->where('branch_id',$branch_id);
			$result = $this->db->get('rejection_creation')->row();
			/*if($result->num_rows()>0)
			{*/
				$rnno = $result->rn_no;
				if($rnno == 0){
					$rn_no = 1;
				}
				else{
					$rn_no = $rnno+1;
				}
				$rejection_no = "RJN"."/".$branchname."/".$rn_no;
			//}
			$this->db->query("insert into rejection_creation (rn_no,rejection_no,rejection_date,branchname,gnr_id,user_name,rej_amt,rtime,branch_id) values ('$rn_no','$rejection_no','$rejection_date','$branchname','$gnr_id','$uname','$rej_amt','$order_time','$branch_id')");
			$rid = $this->db->insert_id();
			for($i=0;$i<count($item_name),$i<count($rjn_quantity),$i<count($rejected_qty),$i<count($rjn_price),$i<count($rjn_tax),$i<count($rjn_tax_amount),$i<count($rjn_inward_grand_amount),$i<count($rjn_remarks),$i<count($gnr_po_id);$i++) {
				
				$rejctd_qty[$i] = $rjn_quantity[$i] + $rejected_qty[$i];//rejected qty
								
				
				$this->db->query("update purchase_order_grn set rejected_qty='$rejctd_qty[$i]' where purchase_grn_id='$gnr_po_id[$i]'");
				
				$sql3 = $this->db->query("select * from item_creation where itm_id='$itm_no[$i]' and branchname='$branchname'");
				if($sql3->num_rows() > 0){
					//foreach($sql3->result_array() as $res)
					$res = $sql3->row_array();		
					$qty[$i] = $res['stock_item'];//opening stock
					$qty1[$i] = $qty[$i] - $rjn_quantity[$i];//closing stock
				    $item_id[$i] = $res['item_id'];
				    $itm_id[$i] = $res['itm_id'];
				    $cat_id[$i] = $res['category_id'];
					
					$this->db->query("UPDATE item_creation set stock_item='$qty1[$i]' WHERE item_name='$itm_no[$i]' and branchname='$branchname'");
					//$sql5 = mysqli_query($this->con,"insert into stock_detail(item_name,trans_ref_no,transaction_date,opening_stock,out_qty,closing_stock)values('$item_name[$i]','$rejection_no','$rejection_date','$qty[$i]','$rjn_quantity[$i]','$qty1[$i]')");
				}
				$this->db->query("insert into  purchase_order_rejection(rejection_qty,rejection_price,rejection_tax,rejection_tax_amount,rejection_total_amount,rejection_remarks,rejection_id,purchase_grn_id,item_id,itm_id,category_id) values ('$rjn_quantity[$i]','$rjn_price[$i]','$rjn_tax[$i]','$rjn_tax_amount[$i]','$rjn_inward_grand_amount[$i]','$rjn_remarks[$i]','$rid','$gnr_po_id[$i]','$item_id[$i]','$itm_id[$i]','$cat_id[$i]')");
				$last_id = $this->db->insert_id();
				if($rjn_quantity[$i]!='0' && $rjn_quantity[$i]!='')
				{
					$this->db->query("insert into stock_detail(item_name,trans_ref_no,transaction_date,opening_stock,out_qty,closing_stock,last_modified,branchname,item_id,itm_id,category_id,branch_id)values('$item_name[$i]','$rejection_no','$rejection_date','$qty[$i]','$rjn_quantity[$i]','$qty1[$i]','$lm','$branchname','$item_id[$i]','$itm_id[$i]','$cat_id[$i]','$branch_id')");
				}
				
				$sql6 = $this->db->query("select grn_item_qty,rejected_qty from purchase_order_grn where purchase_grn_id='$gnr_po_id[$i]'");
				if($sql6->num_rows() > 0){
					foreach($sql6->result_array() as $res)
					$grn_qty[$i] = $res['grn_item_qty'];
					$rej_qty[$i] = $res['rejected_qty'];
					
					if($grn_qty[$i] == $rej_qty[$i] && $rjn_quantity[$i]!='') {
						$status = "Closed";
						$this->db->query("update purchase_order_grn set rjn_status='$status' where purchase_grn_id='$gnr_po_id[$i]'");
					}
					else {
						//echo "failure|0";
					}
				}
				
				$sql8 = $this->db->query("SELECT count(*) as `num` FROM purchase_order_grn where gnr_id='$gnr_id'");
				
				foreach($sql8->result_array() as $row)
				$count1 = $row['num'];
				
				$sql9 = $this->db->query("SELECT count(*) as `num1` FROM purchase_order_grn where rjn_status='Closed' and gnr_id='$gnr_id'");
				
				foreach($sql9->result_array() as $row1)
				$count2 = $row1['num1'];
				if($count1 == $count2 ) {
					$status = "Closed";
					$sql10 = $this->db->query("update gnr_creation set rjn_status='$status' where gnr_id='$gnr_id'");
				}
			}
			$sql11 = $this->db->query("select * from gnr_creation where gnr_id='$gnr_id'");
			if($sql11->num_rows()>0)
			{
				foreach($sql11->result_array() as $res12)
				$total = $res12['reject_amt'];
				$update_total = $total - $rej_amt;
				$this->db->query("update gnr_creation set reject_amt='$update_total' where gnr_id='$gnr_id'");
			}
		//echo $rid;
		return $rid;
	}
	
	public function get_cms_grn_list() {
		$query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date, G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.rjn_status='Pending' and G.branchname='CMS-STR' ORDER BY G.gnr_id desc");
		return $query->result();
	}

    public function getPurchaseDetail($pid){
        $query = $this->db->query("Select item_name,unit_measurement,required_qty,close_stock,item_price,actual_amount,po_tax,po_tax_amount,total_amount,remarks from purchase_order_item where purchase_id='$pid'");
		return $query->result();
    }
    
    
    public function get_exp_date(){
        $cur_date = date('Y-m-d');
        $branch_name = $_SESSION['branch_name'];
        $cw_ceo = $_SESSION['cw_ceo'];
		if($cw_ceo=='CW')
        {
            $query = $this->db->query("SELECT G.branchname,GI.item_id,I.item_name,G.grn_no,G.vendor_name,G.grn_date,GI.expiry_date,GI.grn_item_qty,I.stock_item,I.uom,DATEDIFF(GI.expiry_date, '$cur_date') as left_days FROM gnr_creation G inner join purchase_order_grn GI on G.gnr_id=GI.gnr_id inner join item_creation I on GI.item_id=I.item_id where GI.expiry_date!='' and I.stock_item>0 order by left_days");
        }
        else{
           $query = $this->db->query("SELECT G.branchname,GI.item_id,I.item_name,G.grn_no,G.vendor_name,G.grn_date,GI.expiry_date,GI.grn_item_qty,I.stock_item,I.uom,DATEDIFF(GI.expiry_date, '$cur_date') as left_days FROM gnr_creation G inner join purchase_order_grn GI on G.gnr_id=GI.gnr_id inner join item_creation I on GI.item_id=I.item_id where G.branchname='$cw_ceo' and GI.expiry_date!='' and I.stock_item>0 order by left_days"); 
        }
        /*if($branch_name=='CW4_CEO')
        {
            $query = $this->db->query("SELECT G.branchname,GI.item_id,I.item_name,G.grn_no,G.vendor_name,G.grn_date,GI.expiry_date,GI.grn_item_qty,I.stock_item,I.uom,DATEDIFF(GI.expiry_date, '$cur_date') as left_days FROM gnr_creation G inner join purchase_order_grn GI on G.gnr_id=GI.gnr_id inner join item_creation I on GI.item_id=I.item_id where G.branchname='CW4' and GI.expiry_date!='' and I.stock_item>0 order by left_days");
        }
        else if($branch_name=='CW5_CEO')
        {
            $query = $this->db->query("SELECT G.branchname,GI.item_id,I.item_name,G.grn_no,G.vendor_name,G.grn_date,GI.expiry_date,GI.grn_item_qty,I.stock_item,I.uom,DATEDIFF(GI.expiry_date, '$cur_date') as left_days FROM gnr_creation G inner join purchase_order_grn GI on G.gnr_id=GI.gnr_id inner join item_creation I on GI.item_id=I.item_id where G.branchname='CW5' and GI.expiry_date!='' and I.stock_item>0 order by left_days");
        }
        else if($branch_name=='CW6_CEO')
        {
            $query = $this->db->query("SELECT G.branchname,GI.item_id,I.item_name,G.grn_no,G.vendor_name,G.grn_date,GI.expiry_date,GI.grn_item_qty,I.stock_item,I.uom,DATEDIFF(GI.expiry_date, '$cur_date') as left_days FROM gnr_creation G inner join purchase_order_grn GI on G.gnr_id=GI.gnr_id inner join item_creation I on GI.item_id=I.item_id where G.branchname='CW6' and GI.expiry_date!='' and I.stock_item>0 order by left_days");
        }
        else{
            $query = $this->db->query("SELECT G.branchname,GI.item_id,I.item_name,G.grn_no,G.vendor_name,G.grn_date,GI.expiry_date,GI.grn_item_qty,I.stock_item,I.uom,DATEDIFF(GI.expiry_date, '$cur_date') as left_days FROM gnr_creation G inner join purchase_order_grn GI on G.gnr_id=GI.gnr_id inner join item_creation I on GI.item_id=I.item_id where GI.expiry_date!='' and I.stock_item>0 order by left_days");
        }*/
		return $query->result();
    }
    
    public function get_Pendingdata(){
        $branch_id = $_SESSION['branch_id'];
        $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date, DATE_FORMAT(po_apporval_date, '%d-%m-%Y') as po_apporval_date FROM purchase_order  where purchase_status!='Closed' and branch_id='$branch_id' ORDER BY purchase_id desc");
			return $query->result();
    }
    
    public function get_Closingdata(){
        $branch_id = $_SESSION['branch_id'];
        $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date FROM purchase_order  where purchase_status='Closed' and branch_id='$branch_id' ORDER BY purchase_id");
			return $query->result();
    }
    
    public function get_Grn_data(){
        $branch_id = $_SESSION['branch_id'];
        $query = $this->db->query("SELECT *,DATE_FORMAT(P.purchase_date, '%d-%m-%Y') as purchase_date,G.gnr_id as mid, DATE_FORMAT(G.grn_date, '%d-%m-%Y') as grn_date FROM purchase_order P inner join gnr_creation G on P.purchase_id=G.purchase_id where G.branch_id='$branch_id' and G.rjn_status='Pending' ORDER BY G.gnr_id desc");
			return $query->result();
    }
    
    public function get_dgnr($id) {
		$query = $this->db->query("SELECT *,ROUND(total_amount,2) as total_amount,DATE_FORMAT(grn_date, '%d-%m-%Y') as grn_date FROM gnr_creation where gnr_id='$id'");
		return $query->result();
	}
	
	public function get_dgnrDetail($id) {
		$query = $this->db->query("SELECT *,ROUND(grn_tax_amount,2) as grn_tax_amount,ROUND(grn_grand,2) as grn_grand FROM purchase_order_grn where gnr_id='$id' and grn_item_qty !=''");
		return $query->result();
	}
	
	public function get_dgnr_sumamount($id) {
		$query = $this->db->query("SELECT ROUND(SUM(O.grn_grand),2) as sum_amount,ROUND(SUM(O.grn_tax_amount),2) as sum_tax_amount FROM  gnr_creation G inner join purchase_order_grn O on G.gnr_id=O.gnr_id where G.gnr_id='$id'");
		return $query->result();
	}
	
	public function get_dgnr_forapprove($id) {
		$query = $this->db->query("SELECT * FROM gnr_creation where gnr_id='$id'");
		return $query->result();
	}
	
	public function all_ClosedData() {
		$branch_id = $_SESSION['branch_id'];
		$branch_name = $_SESSION['branch_name'];
		$cw_ceo = $_SESSION['cw_ceo'];
		if($cw_ceo=='CW')
        {
            $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date FROM purchase_order  where purchase_status='Closed' ORDER BY purchase_id desc");
        }
        else{
            $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date FROM purchase_order  where purchase_status='Closed' and branchname='$cw_ceo' ORDER BY purchase_id desc");
        }
		/*if($branch_name =='CW4_CEO'){
		    $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date FROM purchase_order  where purchase_status='Closed' and branchname='CW4' ORDER BY purchase_id desc");
		}
		else if($branch_name =='CW5_CEO'){
		    $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date FROM purchase_order  where purchase_status='Closed' and branchname='CW5' ORDER BY purchase_id desc");
		}
		else if($branch_name =='CW6_CEO'){
		    $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date FROM purchase_order  where purchase_status='Closed' and branchname='CW6' ORDER BY purchase_id desc");
		}
		else {
		    $query = $this->db->query("SELECT *,purchase_id as mid, DATE_FORMAT(purchase_date, '%d-%m-%Y') as purchase_date FROM purchase_order  where purchase_status='Closed' ORDER BY purchase_id desc");
		}*/
		return $query->result();
	}
	
}