<?php
	class Stockupdate_model extends CI_model
	{
		public function __construct()
		{
			parent::__construct();
			$this->load->database();
			//$this->db2 = $this->load->database('otherdb', TRUE);
	
		}

		public function getcatdata($branch_id){
		    
			$query = $this->db->query("select I.item_id,I.category_id,C.category_name,I.item_name,I.uom from category C inner join item_creation I on C.category_id=I.category_id where I.branch_id='$branch_id' and I.status='Enabled' order by C.category_name,I.item_name");
			return $query->result();
		}
		
		public function getcatdata1($branchname){
		    $branchname = $_SESSION['branch_name'];
		    if($branchname=='GDW'){
		         $query = $this->db->query("select I.item_id,I.category_id,C.category_name,I.item_name,I.uom,'0' as closing_stk,I.branchname as branch_name from category C inner join godown_stock I on C.category_id=I.category_id order by C.category_name,I.item_name");
		    }else{
		        $query = $this->db->query("select I.item_id,I.category_id,C.category_name,I.item_name,I.uom,'0' as closing_stk,I.branchname as branch_name from category C inner join item_creation I on C.category_id=I.category_id where I.branchname='$branchname' and I.status='Enabled' order by C.category_name,I.item_name");
		    }
			
			return $query->result_array();
		}
		
		public function getProductDetail($branch_id){
			$query = $this->db->query("select R.recipe_id,R.pr_cat_id,C.pr_cat_name,R.rname as product_name,R.uom,'0' as closing_stk,R.branchname as branch_name from product_category C inner join recipe R on C.pr_cat_id=R.pr_cat_id where R.branch_id='$branch_id' and R.status='Enabled' order by C.pr_cat_name,R.rname");
			return $query->result_array();
		}

		public function stock_update($stk_id) {
			$query = $this->db->query("SELECT *,DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stck_id='$stk_id'");
			return $query->result();
		}

		public function stk_update_items($stk_id) {
			$query = $this->db->query("SELECT * FROM  stock_update_item where stck_id='$stk_id'");
			return $query->result();
		}

		public function stock_update_list($branch_id) {
			$query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where branch_id='$branch_id' and stock_no LIKE 'S%' ORDER BY stck_id desc");
			return $query->result();
		}
		
		public function daily_stock_update_list($branch_id) {
			$query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where branch_id='$branch_id' and stock_no LIKE 'D%' ORDER BY stck_id desc");
			return $query->result();
		}
		
		public function dailyStock_update_by_branch($data){
			$fdate =date('Y-m-d', strtotime($data['from_date']));
			$tdate =date('Y-m-d', strtotime($data['to_date']));
			$branch_id =$data['branch_id'] ;
			$query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stk_date between '$fdate' and '$tdate' and branch_id='$branch_id' and stock_no LIKE 'D%' ORDER BY stck_id desc");
			
			return $query->result();
		}

		public function stock_update_by_branch($data){
			$fdate =date('Y-m-d', strtotime($data['from_date']));
			$tdate =date('Y-m-d', strtotime($data['to_date']));
			$branch_id =$data['branch_id'] ;
			$query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stk_date between '$fdate' and '$tdate' and branch_id='$branch_id' and stock_no LIKE 'S%' ORDER BY stck_id desc");
			
			return $query->result();
		}
		
		public function getCounter($sub_mod_id,$branch_id)
		{
			$query = $this->db->query("SELECT * FROM user_access where branch_id='$branch_id' and module_id='$sub_mod_id'");
			return $query->result();	
		}
		
		public function fetchProduct($branch_id)
		{
			$query=$this->db->query("select * from recipe where branch_id='$branch_id' and status='Enabled'");
			$result = $query->result();
			return $result;
		}
		

		public function getCStockUpdate($data)
		{
			$f_date =date('Y-m-d', strtotime($data['from_date']));
			$t_date =date('Y-m-d', strtotime($data['to_date']));
			$branch = $data['branch'];
			$query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stk_date between '$f_date' and '$t_date' and branchname='$branch' and stock_no LIKE 'S%'");
			return $query->result_array();
		}

		public function getCDailyStockUpdate($data)
		{
			$f_date =date('Y-m-d', strtotime($data['from_date']));
			$t_date =date('Y-m-d', strtotime($data['to_date']));
			$branch = $data['branch'];
			
			$query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stk_date between '$f_date' and '$t_date' and branchname='$branch' and stock_no LIKE 'DS%'");
			return $query->result_array();
		}
		
		public function addStStock($postdata)
		{
			extract($postdata);
			$trans_date = date("Y-m-d", strtotime($trans_date));
			$tr_time = date("h:i A");
			$lm=date("Y-m-dH:i:s",time());
			
			$this->db->select('MAX(stk_no) as stk_no',false);
			$this->db->where('branch_id',$branch_id);
			$result = $this->db->get('stock_update')->row();
			$stk_no = $result->stk_no;
			if($stk_no == 0){
				$stk_no = 1;
			}
			else{
				$stk_no = $stk_no+1;
			}
			$stock_no = "DSTKUP"."/".$branch_name."/".$stk_no;
			
			
			$data = array(
			'stk_no'=> $stk_no,
			'stock_no'=> $stock_no,
			'stk_date'=> $trans_date,
			'branchname'=> $branch_name,
			'stk_time'=> $tr_time,
			'branch_id'=> $branch_id
			);
			$this->db->insert('stock_update', $data);
			$stkid = $this->db->insert_id();
			
			$count = count($product_name);
			for($i=0;$i<$count;$i++)
			{
				$q1 = $this->db->query("select * from recipe where recipe_id='$item_id[$i]'");
				if($q1->num_rows() > 0)
				{
					$row = $q1->row_array();
					$qty[$i] = $row['production_stock'];//opening stock
					$qty1[$i] = $qty[$i] - $quantity[$i];//closing stock
					
					$q2 = $this->db->query("UPDATE recipe set production_stock='$quantity[$i]' WHERE recipe_id='$item_id[$i]'");
				}
				$data2 = array(
					'item_type' => $item_type[$i],
					'item_name' => $product_name[$i],
					'uom' => $uom[$i],
					'present_stk' => $pr_stock[$i],
					'closing_stk' => $quantity[$i],
					'stck_id' => $stkid,
					'item_id' => $item_id[$i],
					'cat_id' => $cat_id[$i],
					'recipe_no' => $recipe_no[$i]
				);
				$this->db->insert('stock_update_item', $data2);
				
				$data3 = array(
					'item_name' => $product_name[$i],
					'trans_ref_no' => $stock_no,
					'trans_type'=>'StkUP',
					'transaction_date' => $trans_date,
					'opening_stock' => $qty[$i],
					'closing_stock' => $quantity[$i],
					'last_modified' => $lm,
					'branchname' => $branch_name,
					'item_id' => $item_id[$i],
					'category_id' => $cat_id[$i],
					'recipe_no' => $recipe_no[$i],
					'branch_id' => $branch_id
				);
				$this->db->insert('stock_detail', $data3);
			}
			
			$this->db->select('MAX(tr_no) as tr_no',false);
			$this->db->where('branch_id',$branch_id);
			$result = $this->db->get('transfer')->row();
			$tr_no = $result->tr_no;
			if($tr_no == 0){
				$tr_no = 1;
			}
			else{
				$tr_no = $tr_no+1;
			}
			$trans_no = "TRNS"."/".$branch_name."/".$to_branch."/".$tr_no;
			
			$data = array(
			'tr_no'=> $tr_no,
			'trans_no'=> $trans_no,
			'trans_date'=> $trans_date,
			'dept_name'=> $dept_name,
			'branchname'=> $branch_name,
			'to_branch'=> $to_branch,
			'trans_time'=> $tr_time,
			'branch_id'=> $branch_id
			);
			$this->db->insert('transfer', $data);
			$trid = $this->db->insert_id();
			
			$count1 = count($product_name);
			for($i=0;$i<$count1;$i++)
			{
				if($item_type[$i] == 'Finished Product')
				{
					$q1 = $this->db->query("select * from recipe where recipe_id='$item_id[$i]'");
					if($q1->num_rows() > 0)
					{
						$row = $q1->row_array();
						$qty[$i] = $row['production_stock'];//opening stock
						$qty1[$i] = $qty[$i] - $quantity[$i];//closing stock
						
						$q2 = $this->db->query("UPDATE recipe set production_stock='$qty1[$i]' WHERE recipe_id='$item_id[$i]'");
					}
					if(!empty($quantity[$i]))
					{
    					$data2 = array(
    						'item_type' => $item_type[$i],
    						'item_name' => $product_name[$i],
    						'uom' => $uom[$i],
    						'quantity' => $quantity[$i],
    						'status' => 'Pending',
    						'tr_id' => $trid,
    						'item_id' => $item_id[$i],
    						'cat_id' => $cat_id[$i],
    						'recipe_no' => $recipe_no[$i]
    					);
    					$this->db->insert('transfer_item', $data2);
					}
					
					$data3 = array(
						'item_name' => $product_name[$i],
						'trans_ref_no' => $trans_no,
						'trans_type'=>'TransIssue',
						'transaction_date' => $trans_date,
						'opening_stock' => $qty[$i],
						'out_qty' => $quantity[$i],
						'closing_stock' => $qty1[$i],
						'last_modified' => $lm,
						'branchname' => $branch_name,
						'item_id' => $item_id[$i],
						'category_id' => $cat_id[$i],
						'recipe_no' => $recipe_no[$i],
						'branch_id' => $branch_id
					);
					$this->db->insert('stock_detail', $data3);
				}
			}
			return $stkid;
		}
		
		public function alldailystock_update_list()
		{
			$branch_name = $_SESSION['branch_name'];
			$cw_ceo = $_SESSION['cw_ceo'];
			if($cw_ceo=='CW')
            {
                $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'S%' ORDER BY stck_id desc");
            }
            else{
                 $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'D%' and branchname LIKE '$cw_ceo%' ORDER BY stck_id desc");
            }
            /*if($branch_name=='CW4_CEO')
            {
			    $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'D%' and branchname LIKE 'CW4%' ORDER BY stck_id desc");
            }
            else if($branch_name=='CW5_CEO')
            {
                $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'D%' and branchname LIKE 'CW5%' ORDER BY stck_id desc");
            }
            else if($branch_name=='CW6_CEO')
            {
                $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'D%' and branchname LIKE 'CW6%' ORDER BY stck_id desc");
            }
            else{
                $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'S%' ORDER BY stck_id desc");
            }*/
			return $query->result();	
		}
		
		public function allstock_update_list()
		{
			$branch_name = $_SESSION['branch_name'];
			$cw_ceo = $_SESSION['cw_ceo'];
			if($cw_ceo=='CW')
            {
                $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'S%' ORDER BY stck_id desc");
            }
            else{
                $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'S%' and branchname LIKE '$cw_ceo%' ORDER BY stck_id desc");
            }
            /*if($branch_name=='CW4_CEO')
            {
			    $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'S%' and branchname LIKE 'CW4%' ORDER BY stck_id desc");
            }
            else if($branch_name=='CW5_CEO')
            {
                $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'S%' and branchname LIKE 'CW5%' ORDER BY stck_id desc");
            }
             else if($branch_name=='CW6_CEO')
            {
                $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'S%' and branchname LIKE 'CW6%' ORDER BY stck_id desc");
            }
            else{
			    $query = $this->db->query("SELECT *, DATE_FORMAT(stk_date, '%d-%m-%Y') as stk_date FROM stock_update where stock_no LIKE 'S%' ORDER BY stck_id desc");
            }*/
			return $query->result();	
		}
		
		public function get_fpCategory() {
    		$query = $this->db->query("SELECT pr_cat_id,pr_cat_name from product_category order by pr_cat_name");
    		return $query->result();
    	}
    		public function get_rwCategory() {
    		$query = $this->db->query("SELECT category_id,category_name from category order by category_name");
    		return $query->result();
    	}
	}
?>