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

class M_sales_order extends CI_Model {
	public $table 			= "at_salesorder";
	public $detail	 		= "at_sorddetail";
	public $detail_dp 		= "at_sorddetaildp";
	public $branch          = "am_branch";
	public $customer        = "am_customer";
	public $employee        = "am_employee";
	public $shipment        = "am_shipment";
	public $termspayment    = "am_termspayment";
	public $invoicereceive  = "at_invoicereceipt";
	public $SInvoice 		= 'at_salesinvoice';
	public $SInvDetail 		= 'at_sinvdetail';
	
	
    function get_all(){
		/*=========================lemparan parah dari custom.js untuk searching_data_transaksi=====================*/
		$param = $this->input->post('param');
		/*====================================end===================================================================*/
		
		/*=================================post dari find di kiri====================================================*/
		$Number				= isset($param['txt_IDNumber']) ? $param['txt_IDNumber'] : '';
		$Desc				= isset($param['txt_Description']) ? $param['txt_Description'] : '';
		
		$DateFrom			= isset($param['txt_DateFrom']) ? $param['txt_DateFrom'] : '';
		$DateTo				= isset($param['txt_DateTo']) ? $param['txt_DateTo'] : '';
		$TransStatus		= isset($param['chk_TransStatus']) ? $param['chk_TransStatus'] : '';
		$BranchCode			= isset($param['cmb_branch']) ? $param['cmb_branch'] : '';
		$cmb_CustomerCode	= isset($param['cmb_CustomerCode']) ? $param['cmb_CustomerCode'] : '';
		/*============================================end============================================================*/
		
		/*======================================scrol view 1-150=======================================*/
		$page		= ($this->input->post('page')) ? intval($this->input->post('page')) : 1;
		$rows		= ($this->input->post('rows')) ? intval($this->input->post('rows')) : 150;
		
		$beginning 	= ($page - 1) * $rows;
		$end 		= $page * $rows;
		/*==============================================================================================*/
		
		/*==========================================by session=======================================================*/
		$sess_company		= $this->session->userdata('company');
        $sess_branch		= $this->session->userdata('branch');
        $sess_branch_status	= $this->session->userdata('branch_status');
		/*===========================================================================================================*/

        $this->db->select("at_salesorder.TransStatus, at_salesorder.SOrdNumber, at_salesorder.SOrdDate, at_salesorder.SOrdDesc, at_salesorder.SQuoNumber, 
		at_salesorder.SQuoDate, at_salesorder.CustPONumber, at_salesorder.CustPODate, at_salesorder.CustomerCode,cr.EmployeeName as CreatedBy, 
		at_salesorder.BranchCode, at_salesorder.SOrdTotalDP, at_salesorder.PPayTotalDP, am_customer.CustomerName, 
		at_salesorder.SOrdTotalAmount, am_branch.BranchName");
		$this->db->from('at_salesorder');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesorder.CustomerCode', 'INNER');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesorder.BranchCode', 'INNER');
        $this->db->join('am_company', 'am_company.CompanyCode = am_branch.CompanyCode', 'INNER');
		$this->db->join('am_employee as cr', 'cr.EmployeeCode = at_salesorder.CreatedBy', 'INNER');

       /*===================================find by number dan desc=========================================================*/
		if($Number != ""){$this->db->like('at_salesorder.SOrdNumber', $Number);}
		if($Desc != ""){$this->db->like('at_salesorder.SOrdDesc', $Desc);}
		if($cmb_CustomerCode != ""){$this->db->where('am_customer.CustomerCode', $cmb_CustomerCode);}
		/*=============================================end===================================================================*/
		
		/*===================================find by branch code=========================================================*/
		/*
			get data dari  combobox find di view
		*/
        if($BranchCode != ""){
			$this->db->where('at_salesorder.BranchCode', $BranchCode);
		}
		else{
			/*
				inisialisasi untuk loading pertama page branch info 
			*/
			//----------------kondisi by company-------------------------------------//
			/*
				branch status 0 = HO
				branch status 1 = depo/branch
				jangan lupa sesion data di atas untuk setiap kondisi
				jangan lupa tambahin case when trans status
			*/
			$this->db->where('at_salesorder.BranchCode', $sess_branch);
			//-----------------------end-------------------------------------------------//
		}
		/*=======================================================end========================================================*/
		
		/*=====================================================find by date==================================================*/
		if((($DateFrom != '') && ($DateTo != ''))){
            if($DateFrom <= $DateTo){
				$this->db->where('at_salesorder.SOrdDate >=', date("Y-m-d", strtotime($DateFrom)));
            	$this->db->where('at_salesorder.SOrdDate <=', date("Y-m-d", strtotime($DateTo)));
			}
        }
		/*======================================================end===========================================================*/
		
		/*=====================================================find by status==================================================*/
		if($TransStatus!=''){ 
			$this->db->where_in('at_salesorder.TransStatus',$TransStatus); 
		}
		else{
			/*
				trans status 0,1 adalah aktif
			*/
			$this->db->where_in('at_salesorder.TransStatus',array(0,1,4,5,6,7)); 
		}
		/*=======================================================end========================================================*/
		
		/*==============================================sort tabel asc==========================================================*/
		$this->db->order_by($this->table.'.TransStatus', 'ASC');
		$this->db->order_by($this->table.'.SOrdNumber', 'ASC');
		/*===============================end=================================================================================*/
		$query  = $this->db->limit($end, $beginning);
		$query = $this->db->get();
		return $query->result();
    }
	
	function get_all_print($param){
		/*=========================lemparan parah dari custom.js untuk searching_data_transaksi=====================*/
		//$param = $this->input->post('param');
		/*====================================end===================================================================*/
		
		/*=================================post dari find di kiri====================================================*/
		$Number				= isset($param['txt_IDNumber']) ? $param['txt_IDNumber'] : '';
		$Desc				= isset($param['txt_Description']) ? $param['txt_Description'] : '';
		
		$DateFrom			= isset($param['txt_DateFrom']) ? $param['txt_DateFrom'] : '';
		$DateTo				= isset($param['txt_DateTo']) ? $param['txt_DateTo'] : '';
		$TransStatus		= isset($param['chk_TransStatus']) ? $param['chk_TransStatus'] : '';
		$BranchCode			= isset($param['cmb_branch']) ? $param['cmb_branch'] : '';
		$cmb_CustomerCode	= isset($param['cmb_CustomerCode']) ? $param['cmb_CustomerCode'] : '';
		/*============================================end============================================================*/
		
		/*======================================scrol view 1-150=======================================*/
		$page		= ($this->input->post('page')) ? intval($this->input->post('page')) : 1;
		$rows		= ($this->input->post('rows')) ? intval($this->input->post('rows')) : 150;
		
		$beginning 	= ($page - 1) * $rows;
		$end 		= $page * $rows;
		/*==============================================================================================*/
		
		/*==========================================by session=======================================================*/
		$sess_company		= $this->session->userdata('company');
        $sess_branch		= $this->session->userdata('branch');
        $sess_branch_status	= $this->session->userdata('branch_status');
		/*===========================================================================================================*/

        $this->db->select("at_salesorder.TransStatus, at_salesorder.SOrdNumber, at_salesorder.SOrdDate, at_salesorder.SOrdDesc, at_salesorder.SQuoNumber, 
		at_salesorder.SQuoDate, at_salesorder.CustPONumber, at_salesorder.CustPODate, at_salesorder.CustomerCode,cr.EmployeeName as CreatedBy, 
		at_salesorder.BranchCode, at_salesorder.SOrdTotalDP, at_salesorder.PPayTotalDP, am_customer.CustomerName, 
		(case 
								when at_salesorder.TransStatus = 0 then 'Waiting' 
								when at_salesorder.TransStatus = 1 then 'On Progress' 
								when at_salesorder.TransStatus = 2 then 'Finished' 
								when at_salesorder.TransStatus = 3 then 'Suspended'
								when at_salesorder.TransStatus = 4 then 'Need App. Mgr.'  
								when at_salesorder.TransStatus = 5 then 'Need App. Dir.' 
								when at_salesorder.TransStatus = 6 then 'App. Dir & Mgr' end) as TransStatus,
		at_salesorder.SOrdTotalAmount, am_branch.BranchName");
		$this->db->from('at_salesorder');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesorder.CustomerCode', 'INNER');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesorder.BranchCode', 'INNER');
        $this->db->join('am_company', 'am_company.CompanyCode = am_branch.CompanyCode', 'INNER');
		$this->db->join('am_employee as cr', 'cr.EmployeeCode = at_salesorder.CreatedBy', 'INNER');

       /*===================================find by number dan desc=========================================================*/
		if($Number != ""){$this->db->like('at_salesorder.SOrdNumber', $Number);}
		if($Desc != ""){$this->db->like('at_salesorder.SOrdDesc', $Desc);}
		if($cmb_CustomerCode != ""){$this->db->where('am_customer.CustomerCode', $cmb_CustomerCode);}
		/*=============================================end===================================================================*/
		
		/*===================================find by branch code=========================================================*/
		/*
			get data dari  combobox find di view
		*/
        if($BranchCode != ""){
			$this->db->where('at_salesorder.BranchCode', $BranchCode);
		}
		else{
			/*
				inisialisasi untuk loading pertama page branch info 
			*/
			//----------------kondisi by company-------------------------------------//
			/*
				branch status 0 = HO
				branch status 1 = depo/branch
				jangan lupa sesion data di atas untuk setiap kondisi
				jangan lupa tambahin case when trans status
			*/
			$this->db->where('at_salesorder.BranchCode', $sess_branch);
			//-----------------------end-------------------------------------------------//
		}
		/*=======================================================end========================================================*/
		
		/*=====================================================find by date==================================================*/
		if((($DateFrom != '') && ($DateTo != ''))){
            if($DateFrom <= $DateTo){
				$this->db->where('at_salesorder.SOrdDate >=', date("Y-m-d", strtotime($DateFrom)));
            	$this->db->where('at_salesorder.SOrdDate <=', date("Y-m-d", strtotime($DateTo)));
			}
        }
		/*======================================================end===========================================================*/
		
		/*=====================================================find by status==================================================*/
		if($TransStatus!=''){ 
			$this->db->where_in('at_salesorder.TransStatus',$TransStatus); 
		}
		else{
			/*
				trans status 0,1 adalah aktif
			*/
			$this->db->where_in('at_salesorder.TransStatus',array(0,1,4,5,6,7)); 
		}
		/*=======================================================end========================================================*/
		
		/*==============================================sort tabel asc==========================================================*/
		$this->db->order_by($this->table.'.TransStatus', 'ASC');
		$this->db->order_by($this->table.'.SOrdNumber', 'ASC');
		/*===============================end=================================================================================*/
		$query  = $this->db->limit($end, $beginning);
		$query = $this->db->get();
		return $query->result();
    }
	
    public function get_by_cust($id) {
        $this->db->select("(case when at_salesorder.TransStatus=1 then 'On Progress'
                                 when at_salesorder.TransStatus=2 then 'Finished'
                                 when at_salesorder.TransStatus=3 then 'Susppend'
                                 when at_salesorder.TransStatus=4 then 'Need App. Mgr.'
                                 when at_salesorder.TransStatus=5 then 'Need App. Dir.'
                                 when at_salesorder.TransStatus=6 then 'Need App. Dir. & Mgr.'
                                 when at_salesorder.TransStatus=7 then 'Approval Limit'
                                 when at_salesorder.TransStatus=9 then 'Canceled by Dir.'
                                 else 'Waiting'
                                 end) as Status, 
			at_salesorder.SOrdNumber, at_salesorder.SOrdDate, at_salesorder.CustPONumber, 
			at_salesorder.CustPODate, at_salesorder.TransStatus, at_salesorder.ShipmentID, 
			at_salesorder.ShipingDate, at_salesorder.CustomerCode, at_salesorder.BranchCode, 
			at_salesorder.SOrdTaxesStatus, at_salesorder.SOrdInTaxStatus, 
			am_customer.CustomerName, am_branch.BranchName");
        $this->db->from($this->table);
        //$this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesorder.SalesmanCode', 'inner');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesorder.CustomerCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesorder.BranchCode', 'inner');
        $this->db->join('am_shipment', 'am_shipment.ShipmentID = at_salesorder.ShipmentID', 'inner');
        $this->db->where('at_salesorder.BranchCode', $this->session->userdata('branch'));
        $this->db->where('at_salesorder.CustomerCode', $id);
        $this->db->where_in('at_salesorder.TransStatus', array(0,1));
        $this->db->order_by($this->table.'.SOrdNumber','ASC');
		
        return $this->db->get()->result();
    }
	
	function get_so_for_do($CustomerCode){
		$this->db->distinct();
		$this->db->select('
			at_salesorder.SOrdNumber, at_salesorder.SOrdDate, at_salesorder.SOrdDesc,
			at_salesorder.CustPONumber, at_salesorder.ShipmentID, at_salesorder.ShipingDate, 
			at_salesorder.SOrdTaxesStatus, at_salesorder.SOrdInTaxStatus, at_salesorder.CurrencyCode, 
			at_salesorder.CurrencyRate, at_salesorder.SOrdSubTotal, at_salesorder.SOrdTaxes, at_salesorder.SOrdTotalAmount, 
			at_salesorder.SOrdTaxPcnt
		');
		$this->db->from($this->table);
		
		$this->db->join('at_sorddetail','at_sorddetail.SOrdNumber=at_salesorder.SOrdNumber','LEFT');
		
		$this->db->where('at_salesorder.BranchCode', $this->session->userdata('branch'));
		$this->db->where('at_salesorder.CustomerCode', $CustomerCode);
		$this->db->where('at_salesorder.TransStatus',1);
		$this->db->where('(at_sorddetail.ItemQty - at_sorddetail.ItemQtySpd - at_sorddetail.ItemQtyDO) <>',0);
		$this->db->where('(at_salesorder.SOrdTotalDP - at_salesorder.SRecTotalDP) =',0);
		$this->db->order_by('at_salesorder.SOrdNumber','ASC');
		
		return $this->db->get()->result();
	}
	
    function get_detail($id){
        $this->db->select("at_salesorder.*, am_branch.BranchName, am_customer.CustomerName, am_customer.CustomerAddress, am_customer.CustShipAddress, am_shipment.ShipmentName, am_termspayment.TermDesc,
                         (case when at_salesorder.FOBID = 1 then 'Shiping Point' when at_salesorder.FOBID = 2 then 'Destination' else 'No' end) as FOB, am_customer.SalesLimit");
        $this->db->join($this->branch,$this->branch.".BranchCode=".$this->table.".BranchCode","INNER");
        $this->db->join($this->customer,$this->customer.".CustomerCode=".$this->table.".CustomerCode","INNER");
//        $this->db->join($this->employee,$this->employee.".EmployeeCode=".$this->table.".SalesmanCode","INNER");
        $this->db->join($this->shipment,$this->shipment.".ShipmentID=".$this->table.".ShipmentID","INNER");
        $this->db->join($this->termspayment,$this->termspayment.".TermspaymentID=".$this->table.".TermsPaymentID","INNER");
        $query = $this->db->get_where($this->table, array($this->table.'.SOrdNumber' => $id));

        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }     
    }

    //Daswan get detail item to print
    function get_detail_print($id){
        $this->db->select("at_salesorder.*, am_branch.BranchName, am_customer.CustomerName, am_customer.CustomerAddress, am_customer.CustShipAddress, am_customer.PhoneNumber, am_shipment.ShipmentName, am_termspayment.TermDesc,
                         (case when at_salesorder.FOBID = 1 then 'Shiping Point' when at_salesorder.FOBID = 2 then 'Destination' else 'No' end) as FOB, em_createby.EmployeeName as created_by, mng.EmployeeName as nm_manager");
        $this->db->join($this->branch,$this->branch.".BranchCode=".$this->table.".BranchCode","INNER");
        $this->db->join($this->customer,$this->customer.".CustomerCode=".$this->table.".CustomerCode","INNER");
//        $this->db->join($this->employee,$this->employee.".EmployeeCode=".$this->table.".SalesmanCode","INNER");
        $this->db->join($this->shipment,$this->shipment.".ShipmentID=".$this->table.".ShipmentID","INNER");
        $this->db->join($this->termspayment,$this->termspayment.".TermspaymentID=".$this->table.".TermsPaymentID","INNER");
        $this->db->join($this->employee." as em_createby", "em_createby.EmployeeCode=at_salesorder.CreatedBy","LEFT");
        $this->db->join($this->employee." as mng", "mng.EmployeeCode=at_salesorder.AppLevel1Code","LEFT");
        $query = $this->db->get_where($this->table, array($this->table.'.SOrdNumber' => $id));

        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }     
    }	

    //Get Detail Item for Editing Sales Order
    function get_detail_item($SOrdNumber){
        $branch_code = $this->session->userdata('branch');

        $this->db->_protect_identifiers = FALSE;
        $this->db->select('
			"1" as RowStatus, at_sorddetail.ItemDesc, at_squodetail.ItemCategoryID, at_squodetail.ItemCategoryCode, at_sorddetail.ItemDiscPcnt as ItemDisc, am_item.ItemTypeID, 
			at_sorddetail.SOrdDetailID, at_sorddetail.SQuoNumber, at_salesquotation.SQuoDate,at_salesquotation.CustPONumber,at_salesquotation.SQuoDesc, at_sorddetail.SOrdNumber, at_sorddetail.ItemCode, at_sorddetail.ItemQtyDO, at_sorddetail.ItemQtySpd, at_sorddetail.ItemSpdStatus,
			at_sorddetail.ItemQty as TempQty, at_sorddetail.ItemUnit, at_sorddetail.ItemPrice, at_sorddetail.ItemPrice as ItemPrice_Hide, at_sorddetail.SalesmanCode, at_sorddetail.ItemType as ItemStatus, 
			am_employee.EmployeeName, at_sorddetail.SQuoDetailID, at_sorddetail.ItemTaxPPNOut as ItemTax, at_sorddetail.ItemAmount, at_sorddetail.ItemAmount as ItemAmountKoma, at_sorddetail.ItemAmount as ItemAmount_Hide, at_sorddetail.ItemTaxPPNOut as ItemTaxKoma, at_sorddetail.ItemQtyDO, 
			at_squodetail.ItemQty as ItemQtySQ, at_squodetail.ItemQtySpd, at_salesorder.SOrdInTaxStatus as IntaxStatus, at_sorddetail.ItemQty as ItemQty,
			((at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) + (at_sorddetail.ItemQty - at_sorddetail.ItemQtyDO)) as ItemMax,
			(case when at_sorddetail.ItemSpdStatus = 0 then "No" else "Yes" end) as Status,
			Concat(at_sorddetail.ItemQtySpd) as ItemQtySpd, at_sorddetail.ItemTaxPPh23 as Item_PPh23
		');
        $this->db->join('at_sorddetail', 'at_sorddetail.SOrdNumber = at_salesorder.SOrdNumber', 'INNER');
        $this->db->join('at_squodetail', 'at_squodetail.SQuoDetailID = at_sorddetail.SQuoDetailID', 'INNER');
		$this->db->join('at_salesquotation', 'at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber', 'INNER');
        //$this->db->join('at_inventories', 'at_inventories.ItemCode = at_sorddetail.ItemCode AND at_inventories.BranchCode="'. $branch_code .'"', 'LEFT');
        $this->db->join('am_item', 'am_item.ItemCode = at_sorddetail.ItemCode', 'LEFT');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_sorddetail.SalesmanCode', 'INNER');
        $this->db->where('at_salesorder.SOrdNumber', $SOrdNumber);
		
        if($this->session->userdata('jobtitle_status') != 0){
            //$this->db->where('at_inventories.BranchCode = ', $branch_code);
            $this->db->where('at_salesorder.BranchCode = ', $branch_code);
        }

        $query = $this->db->get($this->table);        
        $this->db->_protect_identifiers = TRUE;
        
        return $query->result();
    }

    //Get Detail Item to print detail
    function get_detail_item_print($SOrdNumber){
        $branch_code = $this->session->userdata('branch');

        $this->db->_protect_identifiers = FALSE;
        $this->db->select('
			"1" as RowStatus, at_sorddetail.ItemDesc, at_squodetail.ItemCategoryID, at_squodetail.ItemCategoryCode,  am_item.ItemTypeID, 
			at_sorddetail.SOrdDetailID, at_sorddetail.SQuoNumber, at_sorddetail.SOrdNumber, at_sorddetail.ItemCode, at_sorddetail.ItemQtyDO, at_sorddetail.ItemQtySpd, at_sorddetail.ItemSpdStatus, at_sorddetail.ItemDiscPcnt,
			at_sorddetail.ItemQty as TempQty, at_sorddetail.ItemUnit, at_sorddetail.ItemPrice, at_sorddetail.SalesmanCode, at_sorddetail.ItemType as ItemStatus, 
			am_employee.EmployeeName, at_sorddetail.SQuoDetailID, at_sorddetail.ItemTaxPPNOut as ItemTax, at_sorddetail.ItemAmount, at_sorddetail.ItemAmount as ItemAmountKoma, at_sorddetail.ItemTaxPPNOut as ItemTaxKoma, at_sorddetail.ItemQtyDO, 
			at_squodetail.ItemQty as ItemQtySQ, at_squodetail.ItemQtySpd, at_salesorder.SOrdInTaxStatus as IntaxStatus, at_sorddetail.ItemQty as ItemQty,
			((at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) + (at_sorddetail.ItemQty - at_sorddetail.ItemQtyDO)) as ItemMax,
			(case when at_sorddetail.ItemSpdStatus = 0 then "No" else "Yes" end) as Status,
			Concat(at_sorddetail.ItemQtySpd) as ItemQtySpd
		');
        $this->db->join('at_sorddetail', 'at_sorddetail.SOrdNumber = at_salesorder.SOrdNumber', 'INNER');
        $this->db->join('at_squodetail', 'at_squodetail.SQuoDetailID = at_sorddetail.SQuoDetailID', 'INNER');
        //$this->db->join('at_inventories', 'at_inventories.ItemCode = at_sorddetail.ItemCode AND at_inventories.BranchCode="'. $branch_code .'"', 'LEFT');
        $this->db->join('am_item', 'am_item.ItemCode = at_sorddetail.ItemCode', 'LEFT');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_sorddetail.SalesmanCode', 'INNER');
        $this->db->where('at_salesorder.SOrdNumber', $SOrdNumber);		
		
        if($this->session->userdata('jobtitle_status') != 0){
            //$this->db->where('at_inventories.BranchCode = ', $branch_code);
            $this->db->where('at_salesorder.BranchCode = ', $branch_code);
        }

        $query = $this->db->get($this->table);        
        $this->db->_protect_identifiers = TRUE;
        
        return $query->result_array();
    }
	
	function get_detail_item_for_do($SOrdNumber){
        $BranchCode = $this->session->userdata('branch');

        $this->db->_protect_identifiers = FALSE;
        $this->db->select('
			at_salesorder.SOrdDesc, at_sorddetail.SOrdNumber, at_sorddetail.ItemCode, at_sorddetail.ItemDesc, (at_sorddetail.ItemQty-at_sorddetail.ItemQtyDO - at_sorddetail.ItemQtySpd) as ItemQty, 
			at_sorddetail.ItemUnit, (case when at_sorddetail.ItemType=0 then at_inventories.ItemAverage when at_sorddetail.ItemType=1 then 0 else 0 end) as ItemAverage, at_sorddetail.ItemAmount, at_sorddetail.ItemCategoryID,at_sorddetail.ItemCategoryCode, at_sorddetail.SOrdDetailID, at_squodetail.SQuoDetailID, at_sorddetail.ItemType as ItemStatus, at_sorddetail.ItemDiscPcnt,
			((at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) + (at_sorddetail.ItemQty - at_sorddetail.ItemQtyDO)) as ItemMax, 
			(case when at_sorddetail.ItemType=0 then at_inventories.ItemQty-at_inventories.ItemBooks when at_sorddetail.ItemType=1 then 0 else 0 end) as InventoryQty, 
			"0" as AmountDisc, 
			(case when LockStatus=1 then "Yes" when LockStatus=0 then "No" else "-" end) as IsLock, 
			at_sorddetail.ItemPrice, at_sorddetail.ItemQtyDO, at_sorddetail.ItemTaxStatus, at_sorddetail.ItemAmount as TotalAmount, at_sorddetail.ItemTaxPPNOut as ItemTax,
			at_sorddetail.ItemTaxPPh23 as Item_pph23,
		');
        $this->db->join('at_sorddetail', 'at_sorddetail.SOrdNumber = at_salesorder.SOrdNumber', 'INNER');
        $this->db->join('at_squodetail', 'at_squodetail.SQuoDetailID = at_sorddetail.SQuoDetailID', 'INNER');
        $this->db->join('at_inventories', 'at_inventories.ItemCode = at_squodetail.ItemCode AND at_inventories.BranchCode="'. $BranchCode .'"', 'LEFT');
        $this->db->join('am_item', 'am_item.ItemCode = at_sorddetail.ItemCode', 'LEFT');
		
		if($this->session->userdata('jobtitle_status') != 0){
            /*if(){
				$this->db->where('at_inventories.BranchCode = ', $BranchCode);
			}*/
            
			$this->db->where('at_salesorder.BranchCode = ', $BranchCode);
        }
		
        $this->db->where_in('at_salesorder.SOrdNumber',$SOrdNumber);
		$this->db->where('(at_sorddetail.ItemQty - at_sorddetail.ItemQtySpd - at_sorddetail.ItemQtyDO) <>',0);

        $query = $this->db->get($this->table);        
        $this->db->_protect_identifiers = TRUE;
        
        return $query->result();
    }
    
    function get_item_amount($SOrdNumber){
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('ItemPrice, ItemCode');
        $this->db->where('SOrdNumber', $SOrdNumber);
        $query = $this->db->get('at_sorddetail');
        $this->db->_protect_identifiers = TRUE;
        
        return $query->result();
    }
	
	function insert_transaction_all($trans_data){
		$this->db->trans_start();
		$this->insert($trans_data['insert_group']['data']);
		
		for($x=0;$x<count($trans_data['insert_detail']['data']);$x++){
			$this->insert_detail($trans_data['insert_detail']['data'][$x]);
		}
		
		for($x=0;$x<count($trans_data['update_sq']['data']);$x++){
			$this->m_sales_quotation->update_detail($trans_data['update_sq']['data'][$x],$trans_data['update_sq']['SQuoDetailID'][$x]);
		}
		
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){
			return 0;
		}
		else{
			return 1;
		}
	}
	
	function update_transaction($trans_data){
		$this->db->trans_start();
		$this->update($trans_data['update_group']['data'],$trans_data['update_group']['SOrdNumber']);
		
		for($x=0;$x<count($trans_data['update_detail']['data']);$x++){
			$this->update_detail($trans_data['update_detail']['data'][$x],$trans_data['update_detail']['SOrdDetailID'][$x]);
		}
		
		for($x=0;$x<count($trans_data['update_sq']['data']);$x++){
			$this->m_sales_quotation->update_detail($trans_data['update_sq']['data'][$x],$trans_data['update_sq']['SQuoDetailID'][$x]);
		}
		
		if($trans_data['delete_detail']['total']>0){
			for($x=0;$x<count($trans_data['delete_detail']['SOrdDetailID']);$x++){
				$this->delete_detail_by_sodetail_id($trans_data['delete_detail']['SOrdDetailID'][$x]);
			}
			
			for($x=0;$x<count($trans_data['update_so']['data']);$x++){
				$this->update($trans_data['update_so']['data'][$x],$trans_data['update_so']['SOrdNumber'][$x]);
			}
			
			for($x=0;$x<count($trans_data['update_transaction_sq']['data']);$x++){
				$this->m_sales_quotation->update_detail($trans_data['update_transaction_sq']['data'][$x],$trans_data['update_transaction_sq']['SQuoDetailID'][$x]);
			}
		}
		
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){
			return 0;
		}
		else{
			return 1;
		}
	}
	
	function update_group_new($arr_update_qty_suspend,$arr_update_trans_status, $pord_number){
		$this->db->trans_start();
		for($data_item=0; $data_item<count($arr_update_qty_suspend); $data_item++){
			$SOrdDetailID = $arr_update_qty_suspend[$data_item]['SOrdDetailID'];
			
			$data_preq = array(
				'ItemQtySpd' 	=> $arr_update_qty_suspend[$data_item]['ItemQtySpd'],
				'ItemSpdStatus' => $arr_update_qty_suspend[$data_item]['ItemSpdStatus'],
				'LastUser' 		=> $arr_update_qty_suspend[$data_item]['LastUser']
				
			);
			$this->update_detail($data_preq, $SOrdDetailID);
		}
		
		for($data_group=0; $data_group<count($arr_update_trans_status); $data_group++){
			$data = array (
				'TransStatus'       => $arr_update_trans_status[$data_group]['TransStatus'],
				'LastUser'          => $arr_update_trans_status[$data_group]['LastUser']
			);
			$this->update($data, $pord_number);
		}
		
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){
			return 0;
		}
		else{
			return 1;
		}
	}
	
	
	function suspend_trasaction($trans_data){
		$this->db->trans_start();
		$this->update($trans_data['update_group']['data'],$trans_data['update_group']['SOrd_number']);
		
		for($x=0;$x<count($trans_data['update_detail']['data']);$x++){
			$this->m_sales_quotation->update_detail($trans_data['update_detail']['data'][$x],$trans_data['update_detail']['SQuoDetailID'][$x]);
		}
		
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){
			return 0;
		}
		else{
			return 1;
		}
	}
	
	function approved_transaction($trans_data){
		$this->db->trans_start();
		
		for($x=0;$x<count($trans_data['update_sq']['data']);$x++){
			$this->m_sales_quotation->update($trans_data['update_sq']['data'][$x],$trans_data['update_sq']['SQuoNumber'][$x]);
		}
		
		if(!empty($trans_data['insert_receive']['data'])){
			for($x=0;$x<count($trans_data['insert_receive']['data']);$x++){
				$this->insert_invoice_receive($trans_data['insert_receive']['data'][$x]);
			}
		}
		
		$this->update($trans_data['update_group']['data'],$trans_data['update_group']['SOrdNumber']);
		$this->m_customer->update($trans_data['update_customer']['data'],$trans_data['update_customer']['CustomerCode']);
		
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){
			return 0;
		}
		else{
			return 1;
		}
	}
	
	function approved_transaction_limit($trans_data){
		$this->db->trans_start();
		$this->update($trans_data['update_group']['data'],$trans_data['update_group']['SOrdNumber']);
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){
			return 0;
		}
		else{
			return 1;
		}
	}
	
	function approved_by_pass($trans_data){
		$this->db->trans_start();
		
		for($x=0;$x<count($trans_data['update_receive']['data']);$x++){
			$this->update_invoice_receive($trans_data['update_receive']['data'][$x],$trans_data['update_receive']['SOrdNumber'][$x]);
		}
		
		 if (!empty($trans_data['update_sordetail_dp']['data'])) {
			for($x=0;$x<count($trans_data['update_sordetail_dp']['data']);$x++){
				$this->update_dp($trans_data['update_sordetail_dp']['data'][$x],$trans_data['update_sordetail_dp']['SOrdNumber'][$x], $trans_data['update_sordetail_dp']['CustomerCode'][$x]);
			}
		 }
		
		$this->update($trans_data['update_group']['data'],$trans_data['update_group']['SOrdNumber']);
			
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){
			return 0;
		}
		else{
			return 1;
		}
	}
	
	function approved_reverse($trans_data) {
        $this->db->trans_start();
        $this->update($trans_data['reverse_so']['data'], $trans_data['reverse_so']['SOrdNumber']);
		for($x=0;$x<count($trans_data['update_group']['data']);$x++){
			$this->m_sales_quotation->update($trans_data['update_group']['data'][$x],$trans_data['update_group']['SQuoNumber'][$x]);
		}
		
        $this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }
	
    function insert($data) {
        $this->db->insert($this->table, $data);	
    }
	
    function insert_detail($data_detail) {		
        $this->db->insert($this->detail, $data_detail);	
    }
	
	function insert_invoice_receive($data_receive) {		
        $this->db->insert($this->invoicereceive, $data_receive);	
    }
	
    function update($data, $id) {
        $this->db->update($this->table, $data, array('SOrdNumber' => $id));
    }
	
    function update_detail($data, $id){
        $this->db->update($this->detail, $data, array('SOrdDetailID' => $id));

    }
	
	function update_invoice_receive($data, $id){
        $this->db->update($this->invoicereceive, $data, array('InvoiceNumber' => $id));

    }
	
	function update_dp($data, $id,$customercode) {
        $this->db->update($this->detail_dp, $data, array('SOrdNumber' => $id, 'CustomerCode' => $customercode));
    }
	
    function delete($id) {
        $this->db->delete($this->table, array('SQuoNumber' => $id));
        $this->db->delete($this->detail, array('SQuoNumber' => $id));
		
    }
	
    function delete_detail_by_id($SOrdNumber, $SOrdDetailID){
        $this->db->delete($this->detail,array('SOrdNumber' => $SOrdNumber,'SOrdDetailID' => $SOrdDetailID));
		
    }
	
	function delete_invoice_receipt($SOrdNumber){
        $this->db->delete('at_invoicereceipt',array('InvoiceNumber' => $SOrdNumber));
		
    }
	
	function delete_detail_by_sodetail_id($SOrdDetailID){
        $this->db->delete($this->detail,array('SOrdDetailID' => $SOrdDetailID));
		
    }
	
	/*======================penomeran transaksi new=======================================================*/
	function check_code_exist($new_so_number) {
        $query = $this->db->get_where($this->table, array('SOrdNumber' => $new_so_number), 1, 0);
		
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }	
    }
	
	function check_code_exist_sinreceipt($so_number) {
        $this->db->select('InvoiceNumber');
        $this->db->from('at_invoicereceipt');
        $this->db->where('InvoiceNumber', $so_number); 
        $query = $this->db->get();    

        return $query->result();
    }
	
	function check_code_exist_sinreceipt_distinct($so_number) {
        $this->db->distinct();
		$this->db->select('InvoiceNumber');
        $this->db->from('at_invoicereceipt');
        $this->db->where('InvoiceNumber', $so_number); 
        $query = $this->db->get();    

        return $query->row();
    }
	
	function create_so_number($date = ''){
        $branch = $this->session->userdata('branch');
		
        if($date != ''){
            $month  = date('m', strtotime($date));
            $year   = date('y', strtotime($date));
        }
        else{
            $month  = date('m');
            $year   = date('y');
        }
		
        $so_number	= $branch ."/". $month . $year ."SO/";
		
        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(SOrdNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(SOrdNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('SOrdNumber', $so_number);
        $query = $this->db->get($this->table);
        $this->db->_protect_identifiers = true;
                
        $num    = $query->row()->New_Number;
        $long   = strlen($num);
        
        if($long == 1){
            $number = '00' . $num;
        }
        elseif($long == 2){
            $number = '0' . $num;
        }
        else{
            $number = $num;
        }

        $new_so_number = $so_number . $number;

        while($this->check_code_exist($new_so_number)){
            $num++;
            $long = strlen($num);
    
            if($long == 1){
                $number = '00' . $num;
            }
            elseif($long == 2){
                $number = '0' . $num;
            }
            else{
                $number = $num;
            }
            
            $new_so_number = $so_number . $number;		
        }
        
        return  $new_so_number;
    }
	/*==============================================================end=================================*/
	
    function get_so_date($id) {
        $this->db->select('SOrdDate');
        $this->db->from($this->table);
        $this->db->where('SOrdNumber', $id); 
        $query = $this->db->get();    

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $SOrdDate = $row->SOrdDate;
            }    

            return $SOrdDate;
        }
        else {
            return FALSE;
        }	
    }
	
	function get_so_status($SOrdNumber){
		$this->db->select('TransStatus');
        $this->db->from($this->table);
        $this->db->where('SOrdNumber', $SOrdNumber); 
        $query = $this->db->get();    

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $TransStatus = $row->TransStatus;
            }    

            return $TransStatus;
        }
        else {
            return FALSE;
        }
	}
	
	function get_sales($id) {
        $this->db->select('SalesmanCode');
		$this->db->from($this->detail);
		$this->db->where('SOrdDetailID', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SalesmanCode = $row->SalesmanCode;
			}    

			return $SalesmanCode;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_po_date($id) {
        $this->db->select('CustPODate');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$CustPODate = $row->CustPODate;
			}    

			return $CustPODate;
		}
		else {
			return FALSE;
		}	
    }
	
    function set_tax($id, $status) {
        $this->db->set('SOrdTaxesStatus', $status);
        $this->db->where('SOrdNumber', $id);
        $this->db->update($this->table);
    }
	
    function set_tax_inclusive($id, $status) {
        $this->db->set('SOrdInTaxStatus', $status);
        $this->db->where('SOrdNumber', $id);
        $this->db->update($this->table);
    }
	
    function set_trans_status($id, $status) {
        $this->db->set('TransStatus', $status);
        $this->db->where('SOrdNumber', $id);
        $this->db->update($this->table);
    }
	
    function set_qty_do($id, $qty) {
        $this->db->set('ItemQtyDO', $qty);
        $this->db->where('SOrdDetailID', $id);
        $this->db->update($this->detail);

        if ($this->db->trans_status() === FALSE) {
            return 0;
        }
        else {
            return 1;
        }        
    }
	
	function get_qty_do($id) {
        $this->db->select('ItemQtyDO');
		$this->db->from($this->detail);
		$this->db->where('SOrdDetailID', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$ItemQtyDO = $row->ItemQtyDO;
			}    

			return $ItemQtyDO;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_transstatus($id) {
        $this->db->select('TransStatus');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$TransStatus = $row->TransStatus;
			}    

			return $TransStatus;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_aging_daily($code) {
        $this->db->select('PreferenceValue');
		$this->db->from('sys_preference');
		$this->db->where('PreferenceCode', $code); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$PreferenceValue = $row->PreferenceValue;
			}    

			return $PreferenceValue;
		}
		else {
			return 0;
		}	
    }
	
	function get_price_by_code($so, $code) {
		$this->db->select('ItemPrice');
		$this->db->from($this->detail);
		$this->db->where('SOrdNumber', $so); 
		$this->db->where('ItemCode', $code); 
        $query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$ItemPrice = $row->ItemPrice;
			}    

			return $ItemPrice;
		}
		else {
			return 0;
		}  	
	}
	
	function get_price_by_detail($id) {
		$this->db->select('ItemPrice');
		$this->db->from($this->detail);
		$this->db->where('SOrdDetailID', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$ItemPrice = $row->ItemPrice;
			}    

			return $ItemPrice;
		}
		else {
			return 0;
		}  	
	}
	
	function get_for_invoice(){
        $this->db->select("
			at_salesorder.SOrdNumber as invoice_number, at_salesorder.SOrdDate as invoice_date, at_salesorder.SOrdTotalDP as invoice_amount, at_salesorder.SRecTotalDP as invoice_owning
		");
		
		$this->db->from('at_salesorder');
		$this->db->where('at_salesorder.BranchCode',$this->session->userdata('branch'));
		$this->db->where('at_salesorder.SOrdTotalDP !=', 0);
		$this->db->order_by($this->table.'.SOrdNumber','ASC');
		return $this->db->get()->result();
    }
	
	function get_for_invoice_by_cust($id){
        $this->db->select("
			at_salesorder.SOrdNumber as invoice_number, at_salesorder.SOrdDate as invoice_date, 
			at_salesorder.SOrdTotalDP as invoice_amount, at_salesorder.SOrdTotalAmount as invoice_total, 
			at_salesorder.SRecTotalDP as invoice_owning
		");
		
		$this->db->from('at_salesorder');
		$this->db->where('at_salesorder.BranchCode',$this->session->userdata('branch'));
		$this->db->where('at_salesorder.CustomerCode', $id);
		$this->db->where('at_salesorder.SOrdTotalDP !=', 0);
		$this->db->where('at_salesorder.TransStatus !=', 7);
		$this->db->order_by($this->table.'.SOrdNumber','ASC');
		return $this->db->get()->result();
    }
	
	function get_downpayment($id) {
        $this->db->select('SOrdTotalDP');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SOrdTotalDP = $row->SOrdTotalDP;
			}    

			return $SOrdTotalDP;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_rec_total_dp($id) {
        $this->db->select('SRecTotalDP');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SRecTotalDP = $row->SRecTotalDP;
			}    

			return $SRecTotalDP;
		}
		else {
			return FALSE;
		}	
    }
	
	function set_rec_total_dp($id, $amount) {
		$this->db->set('SRecTotalDP', $amount);
		$this->db->where('SOrdNumber', $id);
		$this->db->update($this->table);
	}
	
	function get_sq_by_so($id) {
        $this->db->select('SQuoNumber');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SQuoNumber = $row->SQuoNumber;
			}    

			return $SQuoNumber;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_qty_so_by_detail($id) {
        $this->db->select('ItemQty');
		$this->db->from($this->detail);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$ItemQty = $row->ItemQty;
			}    

			return $ItemQty;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_qty_do_by_detail($id) {
        $this->db->select('ItemQtyDO');
		$this->db->from($this->detail);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$ItemQtyDO = $row->ItemQtyDO;
			}    

			return $ItemQtyDO;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_qty_by_so($so) {
        $this->db->select("at_sorddetail.ItemQty");
		$this->db->join('at_sorddetail','at_sorddetail.SOrdNumber = at_salesorder.SOrdNumber', 'inner');
		$this->db->where('at_salesorder.SOrdNumber', $so);
        $data = $this->db->get($this->table);
        return $data->result();
    }
	
	function get_app_cogs($id) {
        $this->db->select('AppPriceStatus');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$AppPriceStatus = $row->AppPriceStatus;
			}    

			return $AppPriceStatus;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_customer($id) {
        $this->db->select('CustomerCode');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$CustomerCode = $row->CustomerCode;
			}    

			return $CustomerCode;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_total_amount($id) {
        $this->db->select('SOrdTotalAmount');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$TotalAmount = $row->SOrdTotalAmount;
			}    

			return $TotalAmount;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_total_amount_with_rate($id){
		$this->db->select('(SOrdTotalAmount * CurrencyRate) as SOrdTotalAmount');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$TotalAmount = $row->SOrdTotalAmount;
			}    

			return $TotalAmount;
		}
		else {
			return FALSE;
		}
	}
	
	function get_app_limit($id) {
        $this->db->select('AppLimitStatus');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$AppLimitStatus = $row->AppLimitStatus;
			}    

			return $AppLimitStatus;
		}
		else {
			return 0;
		}	
    }
	
	function get_sales_order($id) {
        $this->db->select('SRecTotalDP');
		$this->db->from('at_salesorder');
		$this->db->where('SOrdNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SRecTotalDP = $row->SRecTotalDP;
			}    

			return $SRecTotalDP;
		}
		else {
			return 0;
		}	
    }
	
    function get_limit_app($amount) {
        //$this->db->select('JobTitleCode');
        $this->db->where('LimitBegin <= ', $amount);
        $this->db->where('LimitEnd >= ', $amount);
        $query = $this->db->get('sys_approvallimit');

        return $query->result();
    }

    function get_srec_totaldp($id){
        $this->db->select('SRecTotalDP'); 
        $this->db->where('SOrdNumber', $id); 
        $query = $this->db->get($this->table); 
        
        return $query->result();
    }
	
	
	function set_finish_so_dp_new($id) {
        $this->db->select('(SOrdTotalDP - SRecTotalDP) as SaldoDPBalance, TransStatus');
        $this->db->from('at_salesorder');
        $this->db->where('SOrdNumber', $id);
        $query = $this->db->get();

        if($query->num_rows() > 0){
            $data = $query->row();

            if(($data->SaldoDPBalance == 0) && ($data->TransStatus != 8)) { 
				
				$TransStatus = 4;
				
                $data_so = array (
					'TransStatus'   => $TransStatus,
					'LastUser'      => $this->session->userdata("username")
				);
                $this->update($data_so, $id);
				
                if ($this->db->trans_status() === FALSE) {
                    return 0;
                }
                else {
                    return 1;
                }
            }
            else{
                return 1;
            }
        }
        else{
            return 1;
        }
    }
	
    function set_finish_so_dp($id) {
        $this->db->select('(SOrdTotalDP - SRecTotalDP) as SaldoDPBalance, TransStatus, CustomerCode, SOrdTotalAmount, OverDueDays');
        $this->db->from('at_salesorder');
        $this->db->where('SOrdNumber', $id);
        $query = $this->db->get();

        if($query->num_rows() > 0){
            $data = $query->row();

            if(($data->SaldoDPBalance == 0) && ($data->TransStatus != 8)) { 
				//Check if Sales Order is need Approval Limit
				$CustBalance = $this->m_customer->get_saldo_balance($data->CustomerCode);
				if(doubleval($CustBalance) > doubleval($data->SOrdTotalAmount) || $data->OverDueDays > 0){
					$TransStatus = 4;
				}
				else{
					$TransStatus = 7;
				}
				
                $data_so = array (
					'TransStatus'   => $TransStatus,
					'LastUser'      => $this->session->userdata("username")
				);
                $this->update($data_so, $id);
				
                if ($this->db->trans_status() === FALSE) {
                    return 0;
                }
                else {
                    return 1;
                }
            }
            else{
                return 1;
            }
        }
        else{
            return 1;
        }
    }    

    function set_finish_so($id) {
        $this->db->select('(ItemQty - (ItemQtyDO + ItemQtySpd)) as Quantity');
        $this->db->from($this->detail);
        $this->db->where('SOrdNumber', $id);
        $query = $this->db->get();

        if($query->num_rows() > 0){
            $data = $query->row();

            if($data->Quantity == 0) {
                $data_so = array (
                            'TransStatus'   => 2,
                            'LastUser'      => $this->session->userdata("username")
                            );

                $this->update($data_so, $id);

                if ($this->db->trans_status() === FALSE) {
                    return 0;
                }
                else {
                    return 1;
                }
            }
            else{
                return 1;
            }
        }
        else{
            return 1;
        }
    }
	
	function set_finish_so_reverse($id) {
        $this->db->select('(ItemQty - (ItemQtyDO + ItemQtySpd)) as Quantity');
        $this->db->from($this->detail);
        $this->db->where('SOrdNumber', $id);
        $query = $this->db->get();

        if($query->num_rows() > 0){
            $data = $query->row();

            if($data->Quantity == 0) {
                $data_so = array (
                            'TransStatus'   => 1,
                            'LastUser'      => $this->session->userdata("username")
                            );

                $this->update($data_so, $id);

                if ($this->db->trans_status() === FALSE) {
                    return 0;
                }
                else {
                    return 1;
                }
            }
            else{
                return 1;
            }
        }
        else{
            return 1;
        }
    }
	
	function check_approval_limit($SOrdNumber){
		$this->db->select('AppLimitStatus');
		$this->db->from($this->table);
		$this->db->where('SOrdNumber',$SOrdNumber);
		
		$query = $this->db->get();
		if($query->num_rows()>0){
			$AppLimitStatus = $query->row()->AppLimitStatus;
			if($AppLimitStatus==1){
				return true;
			}
			else{
				return false;
			}
		}
		else{
			return false;
		}
	}
	
	public function check_sord_app_status($SQuoNumber){
		$finish_sq = 1;
		
		$this->db->select('at_salesorder.TransStatus');
		$this->db->from($this->table);
		$this->db->join($this->detail,'at_salesorder.SOrdNumber=at_sorddetail.SOrdNumber','INNER');
		$this->db->where('at_sorddetail.SQuoNumber',$SQuoNumber);
		
		$query = $this->db->get();
		if($query->num_rows()>0){
			$result = $query->result();
			foreach($result as $row){
				if(in_array($row->TransStatus, array(0,1,2))){
					$finish_sq = 1;
				}
			}
			
			if($finish_sq==0){
				return 0;
			}
			else{
				return 1;
			}
		}
		else{
			return 0;
		}
	}
	
	function get_detail_cek_sreq($sord_number){
        $this->db->distinct();
        $this->db->select('sod.SQuoNumber, sq.SQuoDate, sq.CustPONumber');
        $this->db->from($this->detail . ' as sod');
        $this->db->join('at_salesquotation as sq', 'sq.SQuoNumber = sod.SQuoNumber', 'INNER');
        $this->db->where('sod.SOrdNumber', $sord_number);
        $this->db->order_by('sod.SOrdNumber', 'Asc');
        $query = $this->db->get();
        
        return $query->result();
    }
	
	function get_item_price($ItemCode, $BranchCode, $CurrencyCode){
		
        $this->db->select('ItemCode, ItemPrice');
        $this->db->from('am_itemprice');
        $this->db->where('ItemCode', $ItemCode);
		$this->db->where('BranchCode', $BranchCode);
		$this->db->where('CurrencyCode', $CurrencyCode);
		
        $query = $this->db->get();
        
        if ($query->num_rows() > 0) {
			return $query->row();
		}
		else {
			return FALSE;
		}	
	}
	
	function get_detail_sum_sord($SOrdNumber, $ItemCode){
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("(sod.ItemQty) as ItemQtySO, (sod.ItemQtyDO + sod.ItemQtySpd) as ItemQtyDO");
		$this->db->from($this->table . ' as so');
		$this->db->join($this->detail . ' as sod', "sod.SOrdNumber = so.SOrdNumber", "INNER");
        $this->db->where('sod.SOrdNumber', $SOrdNumber);
		$this->db->where('sod.ItemCode', $ItemCode);
		 
		$this->db->where_in('so.TransStatus', array(1,2));
		
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        
        return $query->row();
    }
	
	function check_item_so_approved_by_squonumber($Squo_number){
        $this->db->select('at_salesorder.TransStatus, at_sorddetail.ItemQty');
        $this->db->from($this->table);
       	$this->db->join($this->detail, "at_sorddetail.SOrdNumber = at_salesorder.SOrdNumber", "INNER");
	    
	    $this->db->where($this->detail.'.SQuoNumber', $Squo_number);
        $this->db->where_in($this->table.'.TransStatus', array(4,5,6,7,8));
		$this->db->group_by($this->detail.'.SQuoNumber');
		
        $query = $this->db->get();
        
        if ($query->num_rows() > 0){
            return true;
        }
        else{
            return false;
        }
    }
	
	function get_sales_order_dp_end_for_limit($cmb_customer){
		$this->db->select('sum(SOrdTotalAmount) as SOrdTotalAmountDP');
		$this->db->from($this->table);
		$this->db->where('CustomerCode', $cmb_customer);
		$this->db->where_in('TransStatus', array(0));
		$this->db->where('AppLimitStatus', 1);
		$this->db->group_by('CustomerCode');
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row();
		}
		else {
			return FALSE;
		}	
	}
	
	function get_sales_order_header($cmb_customer){
		$this->db->select('sum(SOrdTotalAmount) as SOrdTotalAmount');
		$this->db->from($this->table);
		$this->db->where('CustomerCode', $cmb_customer);
		$this->db->where_in('TransStatus', array(0,1,2));
		$this->db->group_by('CustomerCode');
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row();
		}
		else {
			return FALSE;
		}	
	}
	
	function get_sales_order_header_app_limit($cmb_customer, $SOrdNumber){
		$this->db->select('sum(SOrdTotalAmount) as SOrdTotalAmount');
		$this->db->from($this->table);
		$this->db->where('CustomerCode', $cmb_customer);
		$this->db->where('SOrdNumber', $SOrdNumber);
		$this->db->where_in('TransStatus', array(4));
		$this->db->where('AppLimitStatus', 1);
		$this->db->group_by('CustomerCode');
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row();
		}
		else {
			return FALSE;
		}	
	}
	
	function get_sales_order_detail_itemspd($cmb_customer, $SordNumber){
		$this->db->select('sum((ItemTotalAmount / ItemQty) * ItemQtySpd) as AmountItemQtySpd');
		$this->db->from($this->detail);
		$this->db->join($this->table, "at_salesorder.SOrdNumber = at_sorddetail.SOrdNumber", "INNER");
		$this->db->where('at_salesorder.CustomerCode', $cmb_customer);
		$this->db->where('at_salesorder.SOrdNumber', $SordNumber);
		$this->db->where_in('at_salesorder.TransStatus', array(1,2));
		$this->db->group_by('at_salesorder.CustomerCode');
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row();
		}
		else {
			return FALSE;
		}	
	}
	
	function get_sales_order_limitstatus($cmb_customer, $txt_so_no){
		$this->db->select('AppLimitStatus, SOrdNumber, AppLevel1Status, TransStatus');
		$this->db->from($this->table);
		$this->db->where('CustomerCode', $cmb_customer);
		$this->db->where('SOrdNumber', $txt_so_no);
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row();
		}
		else {
			return FALSE;
		}	
	}
	
	function get_group_detail($SOrdNumber){
        $this->db->select('so.SOrdTotalDP, so.SRecTotalDP, sod.ItemType');
        $this->db->from($this->table . ' as so');
        $this->db->join($this->detail . ' as sod', 'sod.SOrdNumber = so.SOrdNumber', 'INNER');
        $this->db->join($this->customer . ' as cs', 'cs.CustomerCode = so.CustomerCode', 'INNER');
		
        $this->db->where('so.SOrdNumber', $SOrdNumber);
        $this->db->order_by('sod.SOrdDetailID', 'ASC');
        $query = $this->db->get();
        
        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }
    }
	
	function get_invoice_receive($SOrdNumber){
		$this->db->select('ir.ItemCategoryRatio, ir.InvoiceOwing, ir.ReceiptAmount, ir.ReceiptRounded, ir.InvoiceAmount');
        $this->db->from($this->invoicereceive . ' as ir');
		
        $this->db->where('ir.InvoiceNumber', $SOrdNumber);
        $query = $this->db->get();
        
        if($query->num_rows()>0){
            return $query->result();
        }
        else{
            return false;
        }
	}
	
	function set_on_progress_dp_paid($id) {
        $this->db->select('(SOrdTotalDP - SRecTotalDP) as TotalDP');
        $this->db->from('at_salesorder');
        $this->db->where('at_salesorder.SOrdNumber', $id);
        $query=$this->db->get();

        if($query->num_rows() > 0){
            $data = $query->row();            
            
            if($data->TotalDP == 0){
                $data_po = array (
					'TransStatus'   => 1, 
					'LastUser'      => $this->session->userdata("username")
				);

                $this->db->update($this->table, $data_po, array('SOrdNumber'=>$id));
            }
            else {
                return 1;                
            }
        }
        else {
            return 1;
        }		
    }
	
	function set_finish($id){
		
        $this->db->select('sum(at_dorddetail.ItemQty) as TotalQtyDO');
        $this->db->from('at_deliveryorder');
		$this->db->join('at_dorddetail', 'at_dorddetail.DOrdNumber = at_deliveryorder.DOrdNumber', 'INNER');
        $this->db->where('at_dorddetail.SOrdNumber', $id);
		$this->db->where_in('at_deliveryorder.TransStatus', array(0,1,2));
        //$this->db->group_by('at_dorddetail.DOrdNumber');
		
        $query_po=$this->db->get();
		
		if($query_po->num_rows() > 0){
			return $query_po->row();
        }
        else{
            return false;
        }
    }   
	
	function get_total_qty_so_spd($id){
		
        $this->db->select('sum(at_sorddetail.ItemQtySpd) as TotalQtySpd');
        $this->db->from('at_salesorder');
		$this->db->join('at_sorddetail', 'at_sorddetail.SOrdNumber = at_salesorder.SOrdNumber', 'INNER');
        $this->db->where('at_salesorder.SOrdNumber', $id);
		$this->db->where_in('at_salesorder.TransStatus', array(0,1,2));
        //$this->db->group_by('at_sorddetail.SOrdNumber');
		
        $query_po=$this->db->get();
		
		if($query_po->num_rows() > 0){
			return $query_po->row();
        }
        else{
            return false;
        }
    }
	
	function set_finish_so_new($id){
		$this->db->select('sum(at_sorddetail.ItemQty) as TotalQtySO, at_salesorder.TransStatus');
		$this->db->from('at_sorddetail');
		$this->db->join('at_salesorder', 'at_salesorder.SOrdNumber = at_sorddetail.SOrdNumber', 'INNER');
		$this->db->where('at_salesorder.SOrdNumber', $id);
		$this->db->where_in('at_salesorder.TransStatus', array(1,2));
		$query_pr = $this->db->get();
		if($query_pr->num_rows() > 0){
			return $query_pr->row();
        }
        else{
            return false;
        }
	}
	
	 function get_qty_delivery_order($id, $sord_number){
        $this->db->select('sum(at_dorddetail.ItemQty) as TotalQtyDO_All');
        $this->db->from('at_dorddetail');
        $this->db->where('at_dorddetail.SOrdNumber', $id);
		$this->db->where('at_dorddetail.DOrdNumber', $sord_number);
		
        $this->db->group_by('at_dorddetail.SOrdNumber');
		
        $query=$this->db->get();
		
		if($query->num_rows() > 0){
			return $query->row();
        }
        else{
            return false;
        }
    }
	
	function get_detail_sum_pord($SOrdDetailID, $ItemCode){
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("(sod.ItemQty) as ItemQtySO, (sod.ItemQtyDO + sod.ItemQtySpd) as ItemQtyDO, sod.ItemQtyDO as Item_DO, sod.ItemQtySpd");
		$this->db->from($this->table . ' as so');
		$this->db->join($this->detail . ' as sod', "sod.SOrdNumber = so.SOrdNumber", "INNER");
        $this->db->where('sod.SOrdDetailID', $SOrdDetailID);
		$this->db->where('sod.ItemCode', $ItemCode);
		 
		$this->db->where_in('so.TransStatus', array(1,2));
		
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        
        return $query->row();
    }

    function get_list_price_by_itemcode($ItemCode,$CustomerCode,$limit){
        $this->db->select('gr.SInvNumber,gr.SInvDate,dt.ItemCode,dt.ItemDesc,dt.ItemPrice,dt.ItemUnit,gr.TransStatus');
        $this->db->from($this->SInvDetail.' as dt');
        $this->db->join($this->SInvoice.' as gr','dt.SInvNumber=gr.SInvNumber','left');
        $this->db->where('dt.ItemCode',$ItemCode);
        $this->db->where('gr.CustomerCode',$CustomerCode);
        $this->db->where_in('gr.TransStatus',array(1,2,4,5,6));
        $this->db->order_by('gr.CreatedAt','ASC');
        $this->db->limit($limit,0);

        $query = $this->db->get()->result_array();
        return $query;

    }
}

/* End of file m_sales_order.php */
/* Location: ./application/models/m_sales_order.php */