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

class M_sales_quotation extends CI_Model {
    public $table 	= "at_salesquotation";
    public $detail      = "at_squodetail";
    public $branch      = "am_branch";
    public $customer    = "am_customer";
    public $employee    = "am_employee";
	
	function insert_all_new($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]);
		}
		
		$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 update_all_new($trans_data){
		$this->db->trans_start();

		$this->update($trans_data['update_group']['data'],$trans_data['update_group']['TransNumber']);
		
		for($x=0;$x<count($trans_data['update_detail']['data']);$x++){
			$this->update_detail($trans_data['update_detail']['data'][$x],$trans_data['update_detail']['SQuoDetailID'][$x]);
		}
		
		if($trans_data['delete_detail']['total']>0){
			for($x=0;$x<count($trans_data['delete_detail']['SQuoDetailID']);$x++){
				$this->delete_detail_by_squdetail_id($trans_data['delete_detail']['SQuoDetailID'][$x]);
			}
		}
		
		$this->db->trans_complete();

		if ($this->db->trans_status() === FALSE) {
			return 0;
		}else{
			return 1;
		}
	}
	
	function update_suspend_item($arr_qty_spd, $arr_transstatus, $squo_number){
		$this->db->trans_start();
		for($x=0; $x<count($arr_qty_spd); $x++){
			$SQuoDetailID = $arr_qty_spd[$x]['SQuoDetailID'];
			
			$data_preq = array(
				'ItemQty' 		=> $arr_qty_spd[$x]['ItemQty'],
				'ItemQtySpd' 	=> $arr_qty_spd[$x]['ItemQtySpd'],
				'ItemSpdStatus' => $arr_qty_spd[$x]['ItemSpdStatus'],
				'LastUser' 		=> $arr_qty_spd[$x]['LastUser']
			);
			$this->update_detail($data_preq, $SQuoDetailID);
		}
		
		for($i=0; $i<count($arr_transstatus); $i++){
			$data = array(
				'TransStatus' 	=> $arr_transstatus[$i]['TransStatus'],
				'LastUser' 		=> $arr_transstatus[$i]['LastUser']
			);
			$this->update($data, $squo_number);
		}
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE) {
			return 0;
		}else{
			return 1;
		}
	}
	
    function update($data,$id) {
        $this->db->update($this->table, $data, array('SQuoNumber' => $id));
    }
	
    function update_detail($data, $id) {
        $this->db->update($this->detail, $data, array('SQuoDetailID' => $id));
    }
	
    function delete($id) {
        $this->db->delete($this->table, array('SQuoNumber' => $id));
        $this->db->delete($this->detail, array('SQuoNumber' => $id));
    }
	
    function delete_detail($SQuoNumber){
        $this->db->delete($this->detail,array('SQuoNumber' => $SQuoNumber));
    }
	
	function delete_detail_by_squdetail_id($id){
        $this->db->delete($this->detail,array('SQuoDetailID' => $id));	
    }
	
	
	/*======================penomeran transaksi new=======================================================*/
	function check_code_exist($new_sq_number) {
        $query = $this->db->get_where($this->table, array('SQuoNumber' => $new_sq_number), 1, 0);
    }
	
	function create_sq_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');
        }
		
        $sq_number	= $branch ."/". $month . $year ."SQ/";
		
        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(SQuoNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(SQuoNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('SQuoNumber', $sq_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_sq_number = $sq_number . $number;

        while($this->check_code_exist($new_sq_number)){
            $num++;
            $long = strlen($num);
    
            if($long == 1){
                $number = '00' . $num;
            }
            elseif($long == 2){
                $number = '0' . $num;
            }
            else{
                $number = $num;
            }
            
            $new_sq_number = $sq_number . $number;		
        }
        
        return  $new_sq_number;
    }
	/*==============================================================end=================================*/
	
    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_salesquotation.SQuoNumber, at_salesquotation.SQuoDate, at_salesquotation.CustPONumber, 
		at_salesquotation.CustPODate, at_salesquotation.TransStatus, at_salesquotation.SQuoDesc, 
		at_salesquotation.SalesmanCode, at_salesquotation.CustomerCode, at_salesquotation.BranchCode, emp.EmployeeName as CreatedBy,
		am_employee.EmployeeName, am_customer.CustomerName, am_branch.BranchName");			
        $this->db->from($this->table);
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesquotation.SalesmanCode', 'inner');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesquotation.CustomerCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesquotation.BranchCode', 'inner');		
        
		$this->db->join('am_employee as emp', 'emp.EmployeeCode = at_salesquotation.CreatedBy', 'inner');
		
		/*===================================find by number dan desc=========================================================*/
		if($Number != ""){$this->db->like('at_salesquotation.SQuoNumber', $Number);}
		if($Desc != ""){$this->db->like('at_salesquotation.SQuoDesc', $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_salesquotation.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_salesquotation.BranchCode', $sess_branch);
			//-----------------------end-------------------------------------------------//
		}
		/*=======================================================end========================================================*/
		
		/*=====================================================find by date==================================================*/
		if((($DateFrom != '') && ($DateTo != ''))){
            if($DateFrom <= $DateTo){
				$this->db->where('at_salesquotation.SQuoDate >=', date("Y-m-d", strtotime($DateFrom)));
            	$this->db->where('at_salesquotation.SQuoDate <=', date("Y-m-d", strtotime($DateTo)));
			}
        }
		/*======================================================end===========================================================*/
		
		/*=====================================================find by status==================================================*/
		if($TransStatus!=''){ 
			$this->db->where_in('at_salesquotation.TransStatus',$TransStatus); 
		}
		else{
			/*
				trans status 0,1 adalah aktif
			*/
			$this->db->where_in('at_salesquotation.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.'.SQuoNumber', 'ASC');
		/*===============================end=================================================================================*/
		$query  = $this->db->limit($end, $beginning);
		$query = $this->db->get();
		return $query->result();
    }
	
	function get_all_to_print($data_filter) {
        /* =================================post dari find di kiri==================================================== */
        $BranchCode     = $data_filter['cmb_branch'];
        $Number         = $data_filter['txt_IDNumber'];
        $Desc           = $data_filter['txt_Description'];
        $DateFrom       = $data_filter['txt_DateFrom'];
        $DateTo         = $data_filter['txt_DateTo'];
        $TransStatus    = $data_filter['chk_TransStatus'];
        /* ============================================end============================================================ */

        /* ==========================================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_salesquotation.SQuoNumber, at_salesquotation.SQuoDate, at_salesquotation.CustPONumber, 
							at_salesquotation.CustPODate, (case 
								when at_salesquotation.TransStatus = 0 then 'Waiting' 
								when at_salesquotation.TransStatus = 1 then 'On Progress' 
								when at_salesquotation.TransStatus = 2 then 'Finished' 
								when at_salesquotation.TransStatus = 3 then 'Suspended'
								when at_salesquotation.TransStatus = 4 then 'Need App. Mgr.'  
								when at_salesquotation.TransStatus = 5 then 'Need App. Dir.' 
								when at_salesquotation.TransStatus = 6 then 'App. Dir & Mgr' end) as TransStatus, at_salesquotation.SQuoDesc, 
							at_salesquotation.SalesmanCode, at_salesquotation.CustomerCode, at_salesquotation.BranchCode, emp.EmployeeName as CreatedBy,
							am_employee.EmployeeName, am_customer.CustomerName, am_branch.BranchName");
        $this->db->from($this->table);
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesquotation.SalesmanCode', 'inner');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesquotation.CustomerCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesquotation.BranchCode', 'inner');		
        
		$this->db->join('am_employee as emp', 'emp.EmployeeCode = at_salesquotation.CreatedBy', 'inner');

        /* ===================================find by number dan desc========================================================= */
        if ($Number != "") {
            $this->db->like('at_salesquotation.SQuoNumber', $Number);
        }
        if ($Desc != "") {
            $this->db->like('at_salesquotation.SQuoDesc', $Desc);
        }
        /* =============================================end=================================================================== */

        /* ===================================find by branch code========================================================= */
        /*
          get data dari  combobox find di view
         */
        if ($BranchCode != "") {
            $this->db->where('at_salesquotation.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_salesquotation.BranchCode', $sess_branch);
            //-----------------------end-------------------------------------------------//
        }
        /* =======================================================end======================================================== */

        /* =====================================================find by date================================================== */
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where('at_salesquotation.SQuoDate >=', date("Y-m-d", strtotime($DateFrom)));
                $this->db->where('at_salesquotation.SQuoDate <=', date("Y-m-d", strtotime($DateTo)));
            }
        }
        /* ======================================================end=========================================================== */

        /* =====================================================find by status================================================== */
        if (!empty($TransStatus)) {
            $this->db->where_in('at_salesquotation.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_salesquotation.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 . '.SQuoNumber', 'ASC');
        /* ===============================end================================================================================= */
        $query = $this->db->get();
        return $query->result();
    }
	
	function get_all_print_list($BranchCode){
		$this->db->select("at_salesquotation.SQuoNumber, at_salesquotation.SQuoDate, at_salesquotation.CustPONumber, 
		at_salesquotation.CustPODate, (case 
                            when at_salesquotation.TransStatus = 0 then 'Waiting' 
                            when at_salesquotation.TransStatus = 1 then 'On Progress' 
                            when at_salesquotation.TransStatus = 2 then 'Active' 
                            when at_salesquotation.TransStatus = 3 then 'Suspended' end) as Status, at_salesquotation.SQuoDesc, 
		at_salesquotation.SalesmanCode, at_salesquotation.CustomerCode, at_salesquotation.BranchCode, emp.EmployeeName as CreatedBy,
		am_employee.EmployeeName, am_customer.CustomerName, am_branch.BranchName");			
        $this->db->from($this->table);
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesquotation.SalesmanCode', 'inner');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesquotation.CustomerCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesquotation.BranchCode', 'inner');		
        
		$this->db->join('am_employee as emp', 'emp.EmployeeCode = at_salesquotation.CreatedBy', 'inner');
		$this->db->where('at_salesquotation.BranchCode', $BranchCode);
		
		/*==============================================sort tabel asc==========================================================*/
		$this->db->order_by($this->table.'.SQuoNumber', 'ASC');
		/*===============================end=================================================================================*/
		$query = $this->db->get();
		return $query->result();
    }
	
    public function get_by_cust($id) {
        $this->db->select("at_salesquotation.SQuoNumber, at_salesquotation.SQuoDate, at_salesquotation.CustPONumber, 
                           at_salesquotation.CustPODate, at_salesquotation.TransStatus, at_salesquotation.SQuoDesc, 
                           at_salesquotation.SalesmanCode, at_salesquotation.CustomerCode, at_salesquotation.BranchCode, 
                           am_employee.EmployeeName, am_customer.CustomerName, am_branch.BranchName");
        $this->db->from($this->table);
        $this->db->join('at_squodetail', 'at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber', 'INNER');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesquotation.SalesmanCode', 'INNER');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesquotation.CustomerCode', 'INNER');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesquotation.BranchCode', 'INNER');
        $this->db->where('at_salesquotation.BranchCode',$this->session->userdata('branch'));
        $this->db->where('at_salesquotation.CustomerCode', $id);
        $this->db->where('(at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySPD) >', 0);
        $this->db->where_in('at_salesquotation.TransStatus', array(1));
        $this->db->order_by($this->table.'.SQuoNumber','ASC');

        return $this->db->get()->result();
    }
	
    /*Added By : Hasan  | Added Date : 15 Jan 2015*/
	/*Added By : Fachri | Added Date : 03 Jan 2015*/
	
	//function get_sq_by_cust($CustomerCode, $SelectedSQuoNumber){
//		$this->db->_protect_identifiers = true;
//		/*======================================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;
//		/*==============================================================================================*/
//		
//		$this->db->distinct();
//		$this->db->select('at_salesquotation.SQuoNumber, at_salesquotation.SQuoDate, at_salesquotation.CustPONumber,
//							at_salesquotation.SQuoDesc');
//		$this->db->from($this->table);
//		$this->db->join($this->detail,'at_salesquotation.SQuoNumber=at_squodetail.SQuoNumber','LEFT');
//		
//		if($SelectedSQuoNumber!=''){
//			$this->db->where_in('at_salesquotation.SQuoNumber',$SelectedSQuoNumber);
//			$this->db->or_where('at_salesquotation.BranchCode',$this->session->userdata('branch'));
//		}
//		
//		$this->db->where('at_salesquotation.BranchCode',$this->session->userdata('branch'));
//		$this->db->where('at_salesquotation.CustomerCode',$CustomerCode);
//		$this->db->where_in('at_salesquotation.TransStatus',array(0,1));
//		$this->db->where('(at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) >',0);
//		
//		$this->db->limit($end, $beginning);
//		
//		$this->db->_protect_identifiers = false;
//		return $this->db->get()->result();
//    }
	
    public function get_sq_by_cust($CustomerCode, $SelectedSQuoNumber){
		$this->db->_protect_identifiers = FALSE;
		
		$this->db->distinct();
		$this->db->select('at_salesquotation.SQuoNumber, at_salesquotation.SQuoDate, at_salesquotation.CustPONumber,
		at_salesquotation.SQuoDesc');
		$this->db->from($this->table);
		$this->db->join($this->detail,'at_salesquotation.SQuoNumber=at_squodetail.SQuoNumber','LEFT');
		if($SelectedSQuoNumber!=''){
			$this->db->where_in('at_salesquotation.SQuoNumber',$SelectedSQuoNumber);
			$this->db->or_where('at_salesquotation.BranchCode',$this->session->userdata('branch'));
		}
		$this->db->where('at_salesquotation.BranchCode',$this->session->userdata('branch'));
		$this->db->where('at_salesquotation.CustomerCode',$CustomerCode);
		$this->db->where_in('at_salesquotation.TransStatus',array(0,1));
		$this->db->where('(at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) >',0);
		$this->db->_protect_identifiers = TRUE;
		
		$query = $this->db->get();
		return $query->result();
    }
	
    function get_detail($id){
        $this->db->select($this->table.".*, ".$this->detail.".ItemQtySO, ".$this->branch.".BranchName, ".$this->customer.".CustomerName, ".$this->employee.".EmployeeName");
        $this->db->join($this->detail, $this->detail.".SQuoNumber = " . $this->table.".SQuoNumber", "INNER");
        $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");
        $query = $this->db->get_where($this->table, array($this->table.'.SQuoNumber' => $id));

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

    //Daswan -> get detail to print
    function get_detail_print($id){
        $this->db->select($this->table.".*, ".$this->detail.".ItemQtySO, ".$this->branch.".BranchName, ".$this->customer.".CustomerName, "."em_sales.EmployeeName as nm_sales,".$this->customer.".PhoneNumber,".$this->customer.".CustomerAddress,"."em_createby.EmployeeName as nm_createby");
        $this->db->join($this->detail, $this->detail.".SQuoNumber = " . $this->table.".SQuoNumber", "INNER");
        $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." as em_createby","em_createby.EmployeeCode = " . $this->table.".CreatedBy", "LEFT");
        $this->db->join($this->employee." as em_sales","em_sales.EmployeeCode = " . $this->table.".SalesmanCode", "INNER");
        $query = $this->db->get_where($this->table, array($this->table.'.SQuoNumber' => $id));

        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }    
    }
    
    function get_detail_item($SQuoNumber){
        $BranchCode = $this->session->userdata('branch');
		$CurrencyCode = $this->input->post('currency_code')?'_'. $this->input->post('currency_code'):'';

        $this->db->_protect_identifiers = FALSE;
			
		$this->db->select('
			at_salesquotation.BranchCode, at_squodetail.ItemCode, at_squodetail.ItemDesc, at_squodetail.ItemUnit,at_squodetail.ItemQty, at_squodetail.ItemCategoryCode, at_squodetail.ItemCategoryID, 
			at_squodetail.RequiredDate, at_squodetail.ItemSpdStatus, Concat(at_squodetail.ItemQtySpd, " item suspend") as Memo, at_squodetail.ItemQty as ItemQtyOld,
			at_squodetail.ItemQtySO, at_squodetail.ItemQtySpd, at_squodetail.SQuoDetailID, at_squodetail.ItemType as ItemStatus,
			at_squodetail.ItemQty as TempQty, "1" as RowStatus, at_squodetail.ItemQtyDO as ItemQtyDO, "0" as ItemDisc, 
			(case when at_squodetail.ItemSpdStatus = 0 then "No" else "Yes" end) as Status
		');
		
		if($CurrencyCode!=''){
			$this->db->select('
				(at_squodetail.ItemQty-at_squodetail.ItemQtySO-at_squodetail.ItemQtySpd) as ItemQty, at_squodetail.SQuoNumber, at_salesquotation.SQuoDate,at_salesquotation.CustPONumber, at_salesquotation.SQuoDesc, at_inventories.ItemAverage, 
				at_salesquotation.SalesmanCode, am_employee.EmployeeName, (at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) as ItemMax, 
				"0" as ItemTax
			');
		}
		
        $this->db->join('at_squodetail', 'at_squodetail.SQuoNumber = at_salesquotation.SQuoNumber', 'INNER');
        //$this->db->join('am_item', 'am_item.ItemCode = at_squodetail.ItemCode', 'INNER');
		
		if($CurrencyCode!=''){
			$this->db->join('at_inventories', 'at_squodetail.ItemCode = at_inventories.ItemCode AND at_inventories.BranchCode = "'. $BranchCode .'"', 'LEFT');
			$this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesquotation.SalesmanCode', 'INNER');
		}
		
		$this->db->where_in('at_salesquotation.SQuoNumber', $SQuoNumber);
		
		if($CurrencyCode!=''){
			$this->db->where('(at_squodetail.ItemQty-at_squodetail.ItemQtySO-at_squodetail.ItemQtySpd) <>',0);
			
		}
		
        $query = $this->db->get($this->table);
        $this->db->_protect_identifiers = TRUE;
		
        return $query->result();
    }

    //Get detail item to prrint preview
    function get_detail_item_print($SQuoNumber){
        $BranchCode = $this->session->userdata('branch');
		$CurrencyCode = $this->input->post('currency_code')?'_'. $this->input->post('currency_code'):'';

        $this->db->_protect_identifiers = FALSE;
//        $this->db->select("at_salesquotation.SalesmanCode, am_employee.EmployeeName, am_item.ItemDesc, am_item.ItemCategoryID, am_item.ItemWHTypeID, 
//                           am_item.ItemTypeID, at_squodetail.SQuoDetailID, at_squodetail.SQuoNumber, at_squodetail.ItemCode, at_squodetail.ItemQty, '0' as ItemTax,
//                           at_squodetail.ItemQty as TempQty, at_squodetail.RequiredDate, at_squodetail.ItemQtySO, at_squodetail.ItemQtySpd, at_squodetail.ItemUnit,  
//                           at_squodetail.ItemSpdStatus, at_inventories.ItemAverage, am_itemprice.ItemPrice, am_itemprice.ItemPrice as TempPrice, '0' as RowStatus,
//                          (case when (at_inventories.ItemQty - at_inventories.ItemBooks) > (at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) 
//                                then (at_inventories.ItemQty - at_inventories.ItemBooks)
//                                else (at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) end) as ItemMax,
//                          (case when (at_inventories.ItemQty - at_inventories.ItemBooks) > (at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) 
//                                then (at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) * am_itemprice.ItemPrice
//                                else (at_inventories.ItemQty - at_inventories.ItemBooks) * am_itemprice.ItemPrice end) as ItemAmount,
//                          (case when at_squodetail.ItemSpdStatus = 0 then 'No' else 'Yes' end) as Status,
//                           Concat(at_squodetail.ItemQtySpd, ' item suspend') as Memo");

//        $this->db->select("at_salesquotation.SalesmanCode, am_employee.EmployeeName, am_item.ItemDesc, am_item.ItemCategoryID, 
//			am_item.ItemWHTypeID, am_item.ItemTypeID, at_squodetail.SQuoDetailID, at_squodetail.SQuoNumber, at_squodetail.ItemCode, 
//			at_squodetail.ItemQty, '0' as ItemTax, at_squodetail.ItemQty as TempQty, at_squodetail.RequiredDate, at_squodetail.ItemQtySO, 
//			at_squodetail.ItemQtySpd, at_squodetail.ItemUnit, at_squodetail.ItemSpdStatus, at_inventories.ItemAverage, 
//			(at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) as ItemMaxSO, 
//			am_itemprice.ItemPrice". $CurrencyCode ." as ItemPrice, am_itemprice.ItemPrice as TempPrice, '0' as RowStatus,
//			(case when (at_inventories.ItemQty - at_inventories.ItemBooks) > (at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) 
//				then (at_inventories.ItemQty - at_inventories.ItemBooks)
//				else (at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) end) as ItemMax,
//			(at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) * am_itemprice.ItemPrice". $CurrencyCode ." as ItemAmount,
//			(case when at_squodetail.ItemSpdStatus = 0 then 'No' else 'Yes' end) as Status,
//			Concat(at_squodetail.ItemQtySpd, ' item suspend') as Memo");
			
		$this->db->select('
			at_squodetail.ItemCode, at_squodetail.ItemDesc, at_squodetail.ItemQty, at_squodetail.ItemUnit, at_squodetail.ItemCategoryCode, at_squodetail.ItemCategoryID, 
			at_squodetail.RequiredDate, at_squodetail.ItemSpdStatus, Concat(at_squodetail.ItemQtySpd, " item suspend") as Memo, 
			at_squodetail.ItemQtySO, at_squodetail.ItemQtySpd, at_squodetail.SQuoDetailID, at_squodetail.ItemType as ItemStatus,
			at_squodetail.ItemQty as TempQty, "1" as RowStatus, at_squodetail.ItemQtyDO as ItemQtyDO,
			(case when at_squodetail.ItemSpdStatus = 0 then "No" else "Yes" end) as Status
		');
		
		if($CurrencyCode!=''){
			$this->db->select('
				(at_squodetail.ItemQty-at_squodetail.ItemQtySO-at_squodetail.ItemQtySpd) as ItemQty, at_squodetail.SQuoNumber, at_inventories.ItemAverage, 
				(case when am_itemprice.ItemPrice'. $CurrencyCode .'=0 then "0" else am_itemprice.ItemPrice'. $CurrencyCode .' end) as ItemPrice, 
				(at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) * am_itemprice.ItemPrice'. $CurrencyCode .' as ItemAmount, 
				at_salesquotation.SalesmanCode, am_employee.EmployeeName, (at_squodetail.ItemQty - at_squodetail.ItemQtySO - at_squodetail.ItemQtySpd) as ItemMax, 
				"0" as ItemTax
			');
		}
		
        $this->db->join('at_squodetail', 'at_squodetail.SQuoNumber = at_salesquotation.SQuoNumber', 'INNER');
        //$this->db->join('am_item', 'am_item.ItemCode = at_squodetail.ItemCode', 'INNER');
		
		if($CurrencyCode!=''){
			$this->db->join('at_inventories', 'at_squodetail.ItemCode = at_inventories.ItemCode AND at_inventories.BranchCode = "'. $BranchCode .'"', 'LEFT');
			$this->db->join('am_itemprice', ('at_inventories.ItemCode = am_itemprice.ItemCode AND am_itemprice.BranchCode = "'. $BranchCode .'"'), 'LEFT');
			$this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesquotation.SalesmanCode', 'INNER');
		}
		
		$this->db->where_in('at_salesquotation.SQuoNumber', $SQuoNumber);
		
		if($CurrencyCode!=''){
			$this->db->where('(at_squodetail.ItemQty-at_squodetail.ItemQtySO-at_squodetail.ItemQtySpd) <>',0);
		}
		
        $query = $this->db->get($this->table);
        $this->db->_protect_identifiers = TRUE;
		
        return $query->result_array();
    }
	
    function get_detail_not_suspend($SQuoNumber){
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('at_salesquotation.SalesmanCode, am_item.ItemDesc, am_item.ItemCategoryID, am_item.ItemWHTypeID, 
                           am_item.ItemTypeID, at_squodetail.SQuoDetailID, at_squodetail.SQuoNumber, at_squodetail.ItemCode, 
                           at_squodetail.ItemQty, at_squodetail.ItemUnit, at_squodetail.ItemPrice, at_squodetail.ItemAmount, 
                           at_squodetail.ItemSN, at_squodetail.ItemSuspend, at_squodetail.ItemQtySO, 
                          (at_squodetail.ItemQty - at_squodetail.ItemQtySO) as max_item');
        $this->db->join('at_salesquotation', 'at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber', 'inner');
        $this->db->join('am_item', 'am_item.ItemCode = at_squodetail.ItemCode', 'inner');
        $this->db->where('at_squodetail.SQuoNumber', $SQuoNumber);
        $this->db->where('at_squodetail.ItemSuspend', 0);		
        $query = $this->db->get('at_squodetail');        
        $this->db->_protect_identifiers = TRUE;
        
        return $query->result();
    }
	
    function get_detail_data($id) {
        $query = $this->db->get_where($this->detail, array('SQuoNumber'=>$id));
        return $query->result();
    }

    function get_detail_item_by_id($id){	   
        $query = $this->db->get_where($this->detail, array('SQuoDetailID' => $id));
        
        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }      
    }
	
    function get_all_customer(){ 
        $this->db->select('*');
        $this->db->from('am_customer');
        $this->db->where('BranchCode',$this->session->userdata('branch'));
        $this->db->where('TransStatus', 0);
        $query 	= $this->db->get();
		
        return $query->result();
    }
        
    //Noneed to use
//    function get_id_by_detail($id) {
//        $this->db->select('SQuoNumber');
//        $this->db->from($this->detail);
//        $this->db->where('SQuoDetailID', $id); 
//        $query = $this->db->get();    
//
//        if ($query->num_rows() > 0) {
//            foreach ($query->result() as $row) {
//                $SQuoNumber = $row->SQuoNumber;
//            }
//            
//            return $SQuoNumber;
//        }
//        else {
//            return FALSE;
//        }	
//    }

    //Noneed to use
//    function get_sales($id) {
//        $this->db->select('SalesmanCode');
//        $this->db->from($this->table);
//        $this->db->where('SQuoNumber', $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_sq_date($id) {
        $this->db->select('SQuoDate');
        $this->db->from($this->table);
        $this->db->where('SQuoNumber', $id); 
        $query = $this->db->get();    
        
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $SQuoDate = $row->SQuoDate;
            }    

            return $SQuoDate;
        }
        else {
            return FALSE;
        }	
    }
	
    function get_po_date($id) {
        $this->db->select('CustPODate');
        $this->db->from($this->table);
        $this->db->where('CustPONumber', $id); 
        $query = $this->db->get();    

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

            return $CustPODate;
        }
        else {
            return FALSE;
        }	
    }

    function get_so_no($id) {
        $this->db->select('SOrdNumber');
        $this->db->from('at_salesorder');
        $this->db->where('SQuoNumber', $id);
        $query = $this->db->get();    

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

            return $SOrdNumber;
        }
        else {
            return FALSE;
        }	
    }
	
    function get_qty_so($id) {
        $this->db->select('ItemQtySO');
        $this->db->from($this->detail);
        $this->db->where('SQuoDetailID', $id); 
        $query = $this->db->get();    

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

            return $ItemQtySO;
        }
        else{
            return 0;
        }	
    }
	
    function get_item_suspend($id) {
        $this->db->select('ItemSpdStatus');
        $this->db->from($this->detail);
        $this->db->where('ItemSpdStatus', 1); 
        $this->db->where('SQuoDetailID', $id); 
        $query = $this->db->get();    

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

            return $ItemSuspend;
        }
        else {
            return FALSE;
        }	
    }

    function get_item_suspended($id){
        $query = $this->db->get_where($this->detail, array('SQuoNumber'=>$id, 'ItemSpdStatus'=>1));
        return $query->result();
    }
    
    function get_transstatus($id) {
        $this->db->select('TransStatus');
        $this->db->from($this->table);
        $this->db->where('SQuoNumber', $id); 
        $query = $this->db->get();    

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

            return $TransStatus;
        }
        else {
            return FALSE;
        }	
    }
	
	function set_trans_status($id, $status) {
        $this->db->set('TransStatus', $status);
        $this->db->where('SQuoNumber', $id);
        $this->db->update($this->table);
    }
	
	function set_finish_squo($id){
		$this->db->select('sum(at_squodetail.ItemQty) as TotalQtySQ, at_squodetail.ItemQtySpd as ItemQtySpd, at_salesquotation.TransStatus');
		$this->db->from('at_squodetail');
		$this->db->join('at_salesquotation', 'at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber', 'INNER');
		$this->db->where('at_salesquotation.SQuoNumber', $id);
		$this->db->where_in('at_salesquotation.TransStatus', array(1,2));
		$query_pr = $this->db->get();
		if($query_pr->num_rows() > 0){
			return $query_pr->row();
        }
        else{
            return false;
        }
	}
	
    /*function set_finish_squo($SQuoNumber) {
        $finish_sq		= 1;
		$approved_so	= 1;
		
		$this->db->select('(ItemQty - ItemQtySpd - ItemQtySO) as ItemQty');
		$this->db->from($this->detail);
		$this->db->where('SQuoNumber',$SQuoNumber);
		
		$query = $this->db->get();
		if($query->num_rows()>0){
			$result = $query->result();
			
			foreach($result as $row){
				if($row->ItemQty!=0){
					$finish_sq = 0;
				}
			}
			
			if($finish_sq==1){
				$this->db->select('at_salesorder.TransStatus');
				$this->db->from($this->table);
				$this->db->join('at_salesorder','at_salesquotation.SQuoNumber=at_salesorder.SQuoNumber','INNER');
				$this->db->where('at_salesorder.SQuoNumber',$SQuoNumber);
				
				$query = $this->db->get();
				$result = $query->result();
				
				foreach($result as $row){
					if($row->TransStatus!=1){
						$approved_so = 0;
					}
				}
				
				if($approved_so==1){
					$data_sq = array(
						'TransStatus'	=> 2, 
						'LastUser'		=> $this->session->userdata('username')
					);
					
					$this->update($data_sq, $SQuoNumber);
					
					if($this->db->trans_status() === FALSE){
						return 0;
					}
					else{
						return 1;
					}
				}
				else{
					return 1;
				}
			}
			else{
				return 1;
			}
		}else{
			return 0;
		}
    }*/
	
	function check_item_suspend($id){
		$this->db->select('ItemQtySO');
		$this->db->from('at_squodetail');
		
		$this->db->where('at_squodetail.SQuoNumber', $id);
		$this->db->where('(at_squodetail.ItemQtySO > 0)');
		$query_pr = $this->db->get();
		
		if ($query_pr->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }
	}
	
	function check_so_exist($id) {
        $query = $this->db->get_where('at_salesorder', array('SQuoNumber' => $id), 1, 0);

        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }	
    }
	
	function check_detail_row($SQuoNumber, $row){
		$this->db->select('*');
		$this->db->from($this->detail);
		$this->db->where('SQuoNumber',$SQuoNumber);
		$query = $this->db->get();
		
		if($row==count($query->num_rows())){
			return TRUE;
		}else{
			return FALSE;
		}
	}
	
	function get_deatil_squo($id){	   
        $query = $this->db->get_where($this->detail, array('SQuoNumber'=>$id));
        
        if($query->num_rows()>0){
            return $query->result();
        }
        else{
            return false;
        }      
    }
	
	function get_detail_sum_squo($SQuoNumber, $ItemCode){
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("(sqd.ItemQtySO + sqd.ItemQtySpd) as ItemQtySO, (sqd.ItemQty) as ItemQtySQ, sqd.ItemQtySpd");
		$this->db->from('at_salesquotation as sq');
		$this->db->join("at_squodetail as sqd", "sqd.SQuoNumber = sq.SQuoNumber", "INNER");
		 
        $this->db->where('sqd.SQuoNumber', $SQuoNumber);
		$this->db->where('sqd.ItemCode', $ItemCode);
		 
		//$this->db->where_in('pr.TransStatus', array(1,2));
		
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        
        return $query->row();
    }
	
	function set_finish($id){
		
        $this->db->select('sum(at_sorddetail.ItemQty) as TotalQtySO');
        $this->db->from('at_sorddetail');
		$this->db->join('at_salesorder', 'at_salesorder.SOrdNumber = at_sorddetail.SOrdNumber', 'INNER');
        $this->db->where('at_sorddetail.SQuoNumber', $id);
		$this->db->where_in('at_salesorder.TransStatus', array(0,1,2,8));
        $this->db->group_by('at_sorddetail.SQuoNumber');
		
        $query_po=$this->db->get();
		
		if ($query_po->num_rows() > 0) {
			foreach ($query_po->result() as $row) {
				$TotalQtySO = $row->TotalQtySO;
			}    

			return $TotalQtySO;
		}
		else {
			return 0;
		}
    }
	
	function get_total_qty_sq_spd($id){
		
        $this->db->select('sum(at_squodetail.ItemQtySpd) as TotalQtySpd');
        $this->db->from('at_squodetail');
		$this->db->join('at_salesquotation', 'at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber', 'INNER');
        $this->db->where('at_squodetail.SQuoNumber', $id);
		$this->db->where_in('at_salesquotation.TransStatus', array(0,1,2));
        $this->db->group_by('at_squodetail.SQuoNumber');
		
        $query_po=$this->db->get();
		
		if ($query_po->num_rows() > 0) {
			foreach ($query_po->result() as $row) {
				$TotalQtySpd = $row->TotalQtySpd;
			}    

			return $TotalQtySpd;
		}
		else {
			return 0;
		}
    }
	
	function set_finish_sq($id){
		$this->db->select('sum(at_squodetail.ItemQty) as TotalQtySQ, at_squodetail.ItemQtySpd as ItemQtySpd, at_salesquotation.TransStatus');
		$this->db->from('at_squodetail');
		$this->db->join('at_salesquotation', 'at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber', 'INNER');
		$this->db->where('at_salesquotation.SQuoNumber', $id);
		$this->db->where_in('at_salesquotation.TransStatus', array(1,2));
		$query_sq = $this->db->get();
		if($query_sq->num_rows() > 0){
			return $query_sq->row();
        }
        else{
            return false;
        }
	}
	
	function get_qty_sales_order($id, $sord_number){
		
        $this->db->select('sum(at_sorddetail.ItemQty) as TotalQtySO, sum(at_sorddetail.ItemQtySpd) as TotalQtySOSpd');
        $this->db->from('at_sorddetail');
		$this->db->join('at_salesorder', 'at_salesorder.SOrdNumber = at_sorddetail.SOrdNumber', 'INNER');
        $this->db->where('at_sorddetail.SQuoNumber', $id);
		$this->db->where('at_sorddetail.SOrdNumber', $sord_number);
		
        $this->db->group_by('at_sorddetail.SQuoNumber');
		
        $query=$this->db->get();
		
		if($query->num_rows() > 0){
			return $query->row();
        }
        else{
            return false;
        }
    }
	
}

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