<?php
/*	
 *	Author 	: Tagor
 *	Tanggal : 06-10-2014	
 *	Revisi 	: 
 */
 /*
 	Author 	: Fachri Nugraha
	Tanggal : 23-01-2014
	Revisi 	: Daswan
 */
 
class M_piutang_afiliasi extends CI_Model {
	public $table					= "at_afiliasidebt";
	public $journal					= "at_adebtjournal";
	public $tbl_payment 			= "at_afiliasiinstallment";
	public $tbl_payment_journal 	= "at_afiliasiinstallment_journal";
	public $branch					= "am_branch";
	public $reconcile 				= "at_bankreconcille";
	public $period 					= "at_periodend";
	public $comp_gl 				= "at_compaccountgl";
	public $acc_gl 					= "am_accountgl";
	public $sub_type  				= "am_accountsubtype";
	public $acc_type  				= "am_accounttype";
	public $employee 				= "am_employee";
	public $department 				= "am_department";
	

	function get_all() {
        /* =========================lemparan parah dari custom.js untuk searching_data_transaksi===================== */
        $param = $this->input->post('param');
        /* ====================================end=================================================================== */

        /* =================================post dari find di kiri==================================================== */
        $CompanyNameFrom = isset($param['txt_TransferFrom']) ? mysql_real_escape_string($param['txt_TransferFrom']) : '';
        $CompanyNameTo = isset($param['txt_TransferTo']) ? mysql_real_escape_string($param['txt_TransferTo']) : '';

        $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'] : '';
        /* ============================================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("CompanyFrom.CompanyName as FromCompany,CompanyTo.CompanyName as ToCompany,GLBankFrom.AccountName as BankFrom,
							GLBankTo.AccountName as BankTo,at_afiliasidebt.ADebtDate,at_afiliasidebt.ADebtNumber as AfiliasiNo,at_afiliasidebt.CompTransCode,
							at_afiliasidebt.BankTransfer,at_afiliasidebt.CompRecpCode,at_afiliasidebt.BankReceipt,at_afiliasidebt.ADebtDesc,at_afiliasidebt.ADebtAmount,at_afiliasidebt.CreditStatus,
							at_afiliasidebt.ChequeNumber,at_afiliasidebt.CqTermStatus,at_afiliasidebt.TransStatus as status,at_afiliasidebt.ChequeDate,at_afiliasidebt.LinkNumber,at_afiliasidebt.BranchCode,am_branch.BranchName,
							(case 
                                when at_afiliasidebt.TransStatus = 0 then 'Waiting' 
                                when at_afiliasidebt.TransStatus = 1 then 'On Progress' 
                                when at_afiliasidebt.TransStatus = 2 then 'Active' 
                                when at_afiliasidebt.TransStatus = 3 then 'Suspended' 
                                when at_afiliasidebt.TransStatus = 4 then 'Need Mgr. App.' 
                                when at_afiliasidebt.TransStatus = 5 then 'Need Dir. App.' 
                                when at_afiliasidebt.TransStatus = 6 then 'Need Dir & Mgr App.' end) as TransStatus, am_employee.EmployeeName as Created");
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_afiliasidebt.BranchCode', 'INNER');
        $this->db->join('am_company as CompanyFrom', 'CompanyFrom.CompanyCode = at_afiliasidebt.CompTransCode', 'INNER');
        $this->db->join('am_company as CompanyTo', 'CompanyTo.CompanyCode = at_afiliasidebt.CompRecpCode', 'INNER');
        $this->db->join('am_accountgl as GLBankFrom', 'GLBankFrom.AccountNumber = at_afiliasidebt.BankTransfer', 'INNER');
        $this->db->join('am_accountgl as GLBankTo', 'GLBankTo.AccountNumber = at_afiliasidebt.BankReceipt', 'INNER');
        $this->db->join('am_employee', $this->table . '.CreatedBy=am_employee.EmployeeCode', 'LEFT');

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

        /* ===================================find by trasnfer from dan transfer to========================================================= */
        if ($CompanyNameFrom != '') {
            $this->db->like('CompanyFrom.CompanyName', $CompanyNameFrom);
        }
        if ($CompanyNameTo != '') {
            $this->db->like('CompanyTo.CompanyName', $CompanyNameTo);
        }
        /* =============================================end=================================================================== */

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

        /* =====================================================find by status================================================== */
        if ($TransStatus != '') {
            $this->db->where_in('at_afiliasidebt.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_afiliasidebt.TransStatus', array(0, 1, 4, 5, 6, 7, 8));
        }

        /* =======================================================end======================================================== */

        /* ==============================================sort tabel asc========================================================== */
        $this->db->order_by($this->table . '.ADebtNumber', 'ASC');
        $this->db->order_by($this->table . '.TransStatus', 'DESC');
        /* ===============================end================================================================================= */
        $query = $this->db->limit($end, $beginning);
        $query = $this->db->get();
        return $query->result();
    }

    function get_company_by_branch($sess_branch){
		$this->db->select('CompanyName,BranchName');
		$this->db->from('am_company');
		$this->db->join('am_branch','am_branch.CompanyCode = am_company.CompanyCode','inner');
		// $this->db->join($this->table,$this->table.'.BranchCode = am_branch.BranchCode','inner');
		$this->db->where('am_branch.BranchCode', $sess_branch);
		$query = $this->db->get();
		return $query->row();
	}
	
	function get_afiliasi($link_number){
		$this->db->select('CreditStatus, ADebtNumber');
		$this->db->from($this->table);
		$this->db->where($this->table.'.LinkNumber', $link_number);
		$query = $this->db->get();
		return $query->result();
		
	}
	
	function get_by_afiliasi_number($piutang_number){
		$this->db->select('CompanyFrom.CompanyName as FromCompany,CompanyTo.CompanyCode as ToCompanyCode,CompanyTo.CompanyName as ToCompany,GLBankFrom.AccountName as BankFrom,at_afiliasidebt.CreatedBy,
							GLBankTo.AccountName as BankTo,at_afiliasidebt.BranchCode,at_afiliasidebt.ADebtDate,at_afiliasidebt.ADebtNumber as AfiliasiNo,at_afiliasidebt.CompTransCode,
							at_afiliasidebt.BankTransfer,at_afiliasidebt.CompRecpCode,at_afiliasidebt.BankReceipt,at_afiliasidebt.ADebtDesc,at_afiliasidebt.ADebtAmount,
							at_afiliasidebt.ChequeNumber,at_afiliasidebt.CqTermStatus,at_afiliasidebt.TransStatus as status,at_afiliasidebt.ChequeDate,at_afiliasidebt.AppLevel1Status,at_afiliasidebt.AdebtIsLock,
							at_afiliasidebt.AppLevel2Status,am_branch.BranchName,at_afiliasidebt.LinkNumber, at_afiliasidebt.CreditStatus,em.DepartmentCode');
		$this->db->from($this->table);
		$this->db->join('am_branch','am_branch.BranchCode = at_afiliasidebt.BranchCode','INNER');
		$this->db->join('am_company as CompanyFrom','CompanyFrom.CompanyCode = at_afiliasidebt.CompTransCode','INNER');
		$this->db->join('am_company as CompanyTo','CompanyTo.CompanyCode = at_afiliasidebt.CompRecpCode','INNER');
		$this->db->join('am_accountgl as GLBankFrom','GLBankFrom.AccountNumber = at_afiliasidebt.BankTransfer','INNER');
		$this->db->join('am_accountgl as GLBankTo','GLBankTo.AccountNumber = at_afiliasidebt.BankReceipt','INNER');
		$this->db->join($this->employee.' as em','em.EmployeeCode=at_afiliasidebt.CreatedBy','inner');
		$this->db->where($this->table.'.ADebtNumber', $piutang_number);
		$query = $this->db->get();
		return $query->result();
    }
	
	function get_latest_afiliasi_number($sess_branch){
		$this->db->select_max('ADebtNumber');
		$query = $this->db->get_where($this->table, array('BranchCode' => $sess_branch));
		return $query->result();
	}
	
	// function create_piutang_afiliasi_number($date){        
 //        $branch_code    = $this->session->userdata('branch');
 //        $month          = date('m', strtotime($date));
 //        $year           = date('y', strtotime($date));
	
 //        $piutang_afiliasi_number    = $branch_code."/".$month.$year."AP/";

 //        $this->db->like('ADebtNumber',$piutang_afiliasi_number);
	
 //        $num    = $this->db->get($this->table)->num_rows()+1;
 //        $long   = strlen($num);
		
 //        if($long == 1){
 //            $number='00'.$num;
 //        }
 //        elseif($long==2){
 //            $number='0'.$num;
 //        }
 //        else{
 //            $number=$num;
 //        }
		
 //        return  $piutang_afiliasi_number.$number;
 //    }

	/*======================penomeran transaksi new=======================================================*/
	function check_code_exist($ADebtNumber) {
        $query = $this->db->get_where($this->table, array('ADebtNumber' => $ADebtNumber), 1, 0);
		
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }	
    }
	
	function create_piutang_afiliasi_number($date = '',$BranchCode= null){

		if($BranchCode == null){
        	$branch = $this->session->userdata('branch');
		}else{
			$branch = $BranchCode;
		}
		
        if($date != ''){
            $month  = date('m', strtotime($date));
            $year   = date('y', strtotime($date));
        }
        else{
        	
            $month  = date('m');
            $year   = date('y');
        }
		
        $pa_number	= $branch ."/". $month . $year ."AP/";
		
        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(ADebtNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(ADebtNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('ADebtNumber', $pa_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;
        }

        $pa_new_number = $pa_number . $number;

        while($this->check_code_exist($pa_new_number)){
            $num++;
            $long = strlen($num);
    
            if($long == 1){
                $number = '00' . $num;
            }
            elseif($long == 2){
                $number = '0' . $num;
            }
            else{
                $number = $num;
            }
            
            $pa_new_number = $pa_number . $number;		
        }
        
        return  $pa_new_number;
    }
	/*==============================================================end=================================*/
	
	function get_latest_afiliasi_number_comp_to($BranchCode){
		$this->db->select_max('ADebtNumber');
		$query = $this->db->get_where($this->table, array('BranchCode' => $BranchCode));
        
		if ($query->num_rows() > 0){
			foreach ($query->result() as $row){
				$ADebtNumber = $row->ADebtNumber;
			}
			return $ADebtNumber;
		}
		else{
			return FALSE;
		}
	}
	
	// Function Join
	function get_accountgl(){
        $this->db->select('*');
        $this->db->from('at_compaccountgl');
        $this->db->join('am_accountgl','am_accountgl.AccountNumber = at_compaccountgl.AccountNumber');
        $this->db->join('am_accounttype','am_accounttype.AccountTypeID = am_accountgl.AccountTypeID');
        $this->db->like('am_accounttype.AccountTypeName', 'Petty Cash');
		$query	= $this->db->get();
        return $query->result();
    }
	
	function get_coa_comapny_cmb_from($cmb_fromcompany, $nomor_akun_recip){
		$this->db->select('at_compaccountgl.AccountNumber');
		$this->db->from('at_compaccountgl');
		//$this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber', 'inner');
		$this->db->join('am_branch', 'am_branch.BranchCode = at_compaccountgl.BranchCode', 'INNER');
		$this->db->where('at_compaccountgl.CompanyCode', $cmb_fromcompany);
		$this->db->where('at_compaccountgl.AccountNumber', $nomor_akun_recip);
		$this->db->where('(case when am_branch.BranchStatus = 0 then at_compaccountgl.PLStatus in (0, 1) else at_compaccountgl.PLStatus = 1 end)');
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		//$this->db->where('am_accountgl.TransStatus', 2);
		$query	= $this->db->get();

		if ($query->num_rows() > 0){
			return $query->row();
		}
		else{
			return FALSE;
		}
	}
	
	function get_coa_comapny_cmb_to($cmb_tocompany, $nomor_akun){
		$this->db->select('at_compaccountgl.AccountNumber');
		$this->db->from('at_compaccountgl');
		//$this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber', 'inner');
		$this->db->join('am_branch', 'am_branch.BranchCode = at_compaccountgl.BranchCode', 'INNER');
		$this->db->where('at_compaccountgl.CompanyCode', $cmb_tocompany);
		$this->db->where('at_compaccountgl.AccountNumber', $nomor_akun);
		$this->db->where('(case when am_branch.BranchStatus = 0 then at_compaccountgl.PLStatus in (0, 1) else at_compaccountgl.PLStatus = 1 end)');
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		//$this->db->where('am_accountgl.TransStatus', 2);
		$query	= $this->db->get();
       if ($query->num_rows() > 0){
			return $query->row();
		}
		else{
			return FALSE;
		}
	}
	
	function get_sys_otheraccount_paymen_afiliasi($cmb_tocompany){
		/*$this->db->select('am_accountgl.AccountNumber');
		$this->db->from('am_accountgl');
		$this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber = am_accountgl.AccountNumber', 'inner');
		$this->db->where('am_accountgl.AccountParent', '2-1-05-000-00');
		$this->db->where('at_compaccountgl.CompanyCode', $cmb_fromcompany);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('am_accountgl.TransStatus', 2);
		$query = $this->db->get();
		return $query->result();*/
		
		$this->db->select('at_compaccountgl.AccountNumber');
		$this->db->from('sys_otheraccount');
		$this->db->join('sys_othercategory', 'sys_othercategory.SysOtherCategoryID = sys_otheraccount.SysOtherCategoryID', 'inner');
		$this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber = sys_otheraccount.AccountNumber', 'inner');
		$this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber', 'inner');
		$this->db->join('am_branch', 'am_branch.BranchCode = at_compaccountgl.BranchCode', 'INNER');
		
		$this->db->where('sys_otheraccount.AccountCode', 'PaymentAfiliasi');
		$this->db->where('sys_othercategory.CompanyCode', $cmb_tocompany);
		$this->db->where('at_compaccountgl.CompanyCode', $cmb_tocompany);
		$this->db->where('(case when am_branch.BranchStatus = 0 then at_compaccountgl.PLStatus in (0, 1) else at_compaccountgl.PLStatus = 1 end)');
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('am_accountgl.TransStatus', 2);
		$query = $this->db->get();
		return $query->result();
	}
	
	function get_sys_otheraccount_recip_afiliasi($cmb_fromcompany){
		
		/*$this->db->select('am_accountgl.AccountNumber');
		$this->db->from('am_accountgl');
		$this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber = am_accountgl.AccountNumber', 'inner');
		$this->db->where('am_accountgl.AccountParent', '1-3-04-003-00');
		$this->db->where('at_compaccountgl.CompanyCode', $cmb_tocompany);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('am_accountgl.TransStatus', 2);
		$query = $this->db->get();
		return $query->result();*/
		
		$this->db->select('at_compaccountgl.AccountNumber');
		$this->db->from('sys_otheraccount');
		$this->db->join('sys_othercategory', 'sys_othercategory.SysOtherCategoryID = sys_otheraccount.SysOtherCategoryID', 'inner');
		$this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber = sys_otheraccount.AccountNumber', 'inner');
		$this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber', 'inner');
		$this->db->join('am_branch', 'am_branch.BranchCode = at_compaccountgl.BranchCode', 'INNER');
		 
		$this->db->where('sys_otheraccount.AccountCode', 'ReceiptAfiliasi');
		$this->db->where('sys_othercategory.CompanyCode', $cmb_fromcompany);
		$this->db->where('at_compaccountgl.CompanyCode', $cmb_fromcompany);
		$this->db->where('(case when am_branch.BranchStatus = 0 then at_compaccountgl.PLStatus in (0, 1) else at_compaccountgl.PLStatus = 1 end)');
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('am_accountgl.TransStatus', 2);
		$query = $this->db->get();
		return $query->result();
	}
	
	function branch_code_comp_to($company_code_to){
		 $this->db->select('am_branch.BranchCode');
        $this->db->from('am_branch');
        $this->db->join('am_company','am_branch.CompanyCode = am_company.CompanyCode','inner');
        $this->db->where('am_branch.CompanyCode', $company_code_to);
        $this->db->where('am_branch.BranchStatus', 0);
		$query	= $this->db->get();
        return $query->row();
	}
	
	function insert_group($data_group) {
		$this->db->insert($this->table, $data_group);
	}

	function update_group($data_group, $link_number) {
        $this->db->update($this->table, $data_group, array('LinkNumber' => $link_number));
	}
	
	function update_status_suspen($status_and_approval, $link_number) {
        $this->db->update($this->table, $status_and_approval, array('LinkNumber' => $link_number));
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_status($data, $ADebtNumber, $link_number) {
        $this->db->update($this->table, $data, array('ADebtNumber' => $ADebtNumber));
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_status_hutang($status_and_approval_hutang, $ADebtNumber) {
        $this->db->update($this->table, $status_and_approval_hutang, array('ADebtNumber' => $ADebtNumber));
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	/*---update jurnal fachri*/
	function update_journal_debet($update_journal_debet, $id) {
        $this->db->update('at_adebtjournal', $update_journal_debet, array('ADebtNumber' => $id, 'ADebtAccountNumber' =>'1-3-04-003-00'));
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_journal_credit($update_journal_credit, $id, $cmb_bankfrom) {
        $this->db->update('at_adebtjournal', $update_journal_credit, array('ADebtNumber' => $id, 'ADebtAccountNumber' => $cmb_bankfrom));
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_journal_debet_comp_to($update_journal_debet_comp_to, $id, $cmb_bankto) {
        $this->db->update($this->journal, $update_journal_debet_comp_to, array('ADebtNumber' => $id, 'ADebtAccountNumber' => $cmb_bankto));
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else{
			return 1;
		}
	}
	
	function update_journal_credit_comp_to($update_journal_credit_comp_to, $id) {
        $this->db->update($this->journal, $update_journal_credit_comp_to, array('ADebtNumber' => $id, 'ADebtAccountNumber' =>'2-1-05-000-00'));
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			return 1;
		}
	}
	/*---end jurnal ---*/
	
	function suspend_data($data, $id) {
        $this->db->update($this->table, $data, array('LinkNumber' => $id));
	}
	
	function get_branch_by_comp_and_accnumb(){
        $this->db->select('*');
        $this->db->from('at_compaccountgl');
        $this->db->join('am_company','am_company.CompanyCode = at_compaccountgl.CompanyCode');
        $this->db->join('am_branch','am_branch.CompanyCode = am_company.CompanyCode');
		$query	= $this->db->get();
        return $query->result();
	}
	
	function get_bank_bycompany($comp_code){
		$this->db->select('ag.AccountName,ag.AccountNumber,ag.CurrencyCode');
		$this->db->from($this->comp_gl.' as ac');
		$this->db->join($this->acc_gl.' as ag','ag.AccountNumber = ac.AccountNumber','INNER');
		$this->db->join($this->branch.' as br','br.BranchCode = ac.BranchCode', 'INNER');
		$this->db->join($this->sub_type.' as sub','sub.AccountSubTypeID = ag.AccountSubTypeID', 'INNER');
		$this->db->where('ac.CompanyCode', $comp_code);
		$this->db->where('ac.GLSaldoYears', date('Y'));
		$this->db->where('sub.AccountTypeName', 'Bank');
		$this->db->where('ag.AccountChild !=', 0);
		$this->db->where('ac.TransStatus =', 2);
		$this->db->where('ag.CurrencyCode =', 'IDR');
		$this->db->where('br.BranchStatus', 0);
		
		$query	= $this->db->get();
		return $query->result();
	}
	
	function company_by_login($company_code){
		$this->db->select('am_company.CompanyCode,am_company.CompanyName');
		$this->db->from('am_company');
		//$this->db->join('am_branch','am_company.CompanyCode = am_branch.CompanyCode','inner');
		if($this->session->userdata("jobtitle") != "DIR"){
			$this->db->where('am_company.CompanyCode', $company_code);
		}
		//$this->db->where('am_branch.BranchStatus', 0);
		
		$query = $this->db->get();
		return $query->result();
	}
	
	function company_new_form($company_code){
		if($this->session->userdata("jobtitle_status") != 0){ 
			$where = array('CompanyCode' => $company_code);
		}
		
		$query = $this->db->get_where('am_company', array('CompanyCode' => $company_code));
		if($query->num_rows()>0){
			return $query->row();
		}
		else{
			return false;
		}
	}
	
	function company_not_by_login($company_code){

		$this->db->select('am_company.CompanyCode,am_company.CompanyName');
		$this->db->from('am_company');
	
		$this->db->where('am_company.CompanyCode !=', $company_code);
		
		$query = $this->db->get();
		
		return $query->result();
	}
	
	function get_all_by_no($afiliasi_number){
		$this->db->select('TransStatus');
		$this->db->from($this->table);
		$this->db->where('at_afiliasidebt.ADebtNumber', $afiliasi_number);
		$query = $this->db->get();
		return $query->result();
	}
	
	##== function to Journal ==##
	function insert_journal($data_journal){
		$this->db->insert($this->journal, $data_journal);
	}
	
	function get_datatojournal_from($piutang_number){
		$this->db->select('am_branch.BranchName,am_accountgl_transfer.AccountName as AccountTrasfer,am_accountgl_receipt.AccountName as AccountReceipt,
							at_afiliasidebt.ADebtNumber,at_afiliasidebt.ADebtDate,at_afiliasidebt.ADebtAmount,at_afiliasidebt.TransStatus as AfiliasiStatus,
							company_from.CompanyName as FromCompany,company_to.CompanyName as ToCompany,');
		$this->db->from($this->table);
		$this->db->join('am_branch','am_branch.BranchCode = at_afiliasidebt.BranchCode');
		$this->db->join('am_accountgl as am_accountgl_transfer','am_accountgl_transfer.AccountNumber = at_afiliasidebt.BankTransfer');
		$this->db->join('am_accountgl as am_accountgl_receipt','am_accountgl_receipt.AccountNumber = at_afiliasidebt.BankReceipt');
		$this->db->join('am_company as company_from','company_from.CompanyCode = at_afiliasidebt.CompTransCode');
		$this->db->join('am_company as company_to','company_to.CompanyCode = at_afiliasidebt.CompRecpCode');
		$this->db->where('at_afiliasidebt.ADebtNumber', $piutang_number);
		$query = $this->db->get();
		return $query->result();
    }

	function get_datatojournal_detail_from($piutang_number, $CompTransCode){
		$this->db->select('am_accountgl.AccountNumber,at_afiliasidebt.BankTransfer as AccountNumberCredit,am_accountgl.AccountName,at_afiliasidebt.ADebtAmount as DebetAmount,
							at_afiliasidebt.ADebtAmount as CreditAmount');
		$this->db->from('at_compaccountgl');
		$this->db->join('am_accountgl','am_accountgl.AccountNumber = at_compaccountgl.AccountNumber');
		$this->db->join('at_afiliasidebt','at_afiliasidebt.BankTransfer = am_accountgl.AccountNumber');
		$this->db->join('am_accountgl as ADebtDebet','ADebtDebet.AccountNumber = at_afiliasidebt.BankTransfer');
		//if($this->session->userdata('jobtitle') != "DIR"){	
			$this->db->where('at_compaccountgl.CompanyCode', $CompTransCode);
		//}
		$this->db->where('at_afiliasidebt.ADebtNumber', $piutang_number);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$query = $this->db->get();
		return $query->row();
	}
	
	function get_datatojournal_to($id){
		$this->db->select('am_branch.BranchName,am_accountgl_transfer.AccountName as AccountTrasfer,
							am_accountgl_receipt.AccountName as AccountReceipt,
							at_afiliasidebt.ADebtNumber,at_afiliasidebt.ADebtDate,
							at_afiliasidebt.ADebtAmount,
							at_afiliasidebt.CompRecpCode,
							at_afiliasidebt.CompTransCode,
							at_afiliasidebt.TransStatus as AfiliasiStatus,
							company_from.CompanyName as FromCompany,
							company_to.CompanyName as ToCompany,');
		$this->db->from($this->table);
		$this->db->join('am_branch','am_branch.BranchCode = at_afiliasidebt.BranchCode','inner');
		$this->db->join('am_accountgl as am_accountgl_transfer','am_accountgl_transfer.AccountNumber = at_afiliasidebt.BankTransfer','inner');
		$this->db->join('am_accountgl as am_accountgl_receipt','am_accountgl_receipt.AccountNumber = at_afiliasidebt.BankReceipt','inner');
		$this->db->join('am_company as company_from','company_from.CompanyCode = at_afiliasidebt.CompTransCode','inner');
		$this->db->join('am_company as company_to','company_to.CompanyCode = at_afiliasidebt.CompRecpCode','inner');
		$this->db->where('at_afiliasidebt.ADebtNumber', $id);
		$query = $this->db->get();
		return $query->result();
    }

	function get_datatojournal_detail_to($piutang_number, $company_receipt){
		$this->db->select('am_accountgl.AccountNumber,at_afiliasidebt.BankReceipt as AccountNumberDebet,am_accountgl.AccountName,at_afiliasidebt.ADebtAmount as DebetAmount,
							at_afiliasidebt.ADebtAmount as CreditAmount');
		$this->db->from('at_compaccountgl');
		$this->db->join('am_accountgl','am_accountgl.AccountNumber = at_compaccountgl.AccountNumber');
		$this->db->join('at_afiliasidebt','at_afiliasidebt.BankReceipt = am_accountgl.AccountNumber');
		$this->db->join('am_accountgl as ADebtDebet','ADebtDebet.AccountNumber = at_afiliasidebt.BankReceipt');
		//if($this->session->userdata('jobtitle') != "DIR"){	
			$this->db->where('at_compaccountgl.CompanyCode', $company_receipt);
		//}
		$this->db->where('at_afiliasidebt.ADebtNumber', $piutang_number);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$query = $this->db->get();
		return $query->row();
	}

	function hutang_afiliasi(){
		$this->db->select('am_accountgl.AccountNumber,am_accountgl.AccountName');
		$this->db->from('am_accountgl');
		$this->db->join('at_compaccountgl','at_compaccountgl.AccountNumber = am_accountgl.AccountNumber');
		$this->db->where('am_accountgl.AccountName', 'Hutang Afiliasi');
		$this->db->where('at_compaccountgl.CompanyCode', $this->session->userdata('company'));
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$query = $this->db->get();
		return $query->result();
	}
	
	function akun_payment_afiliasi($company_code){
		
		$this->db->select('at_compaccountgl.AccountNumber, am_accountgl.AccountName');
		$this->db->from('sys_otheraccount');
		$this->db->join('sys_othercategory', 'sys_othercategory.SysOtherCategoryID = sys_otheraccount.SysOtherCategoryID', 'inner');
		$this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber = sys_otheraccount.AccountNumber', 'inner');
		$this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber', 'inner');
		$this->db->where('sys_otheraccount.AccountCode', 'PaymentAfiliasi');
		$this->db->where('sys_othercategory.CompanyCode', $company_code);
		$this->db->where('at_compaccountgl.CompanyCode', $company_code);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('am_accountgl.TransStatus', 2);
		$query = $this->db->get();
		return $query->row();
	}
	
	function akun_penerimaan_afiliasi($company_code){
		
		$this->db->select('at_compaccountgl.AccountNumber, am_accountgl.AccountName');
		$this->db->from('sys_otheraccount');
		$this->db->join('sys_othercategory', 'sys_othercategory.SysOtherCategoryID = sys_otheraccount.SysOtherCategoryID', 'inner');
		$this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber = sys_otheraccount.AccountNumber', 'inner');
		$this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber', 'inner');
		$this->db->where('sys_otheraccount.AccountCode', 'ReceiptAfiliasi');
		$this->db->where('sys_othercategory.CompanyCode', $company_code);
		$this->db->where('at_compaccountgl.CompanyCode', $company_code);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('am_accountgl.TransStatus', 2);
		$query = $this->db->get();
		return $query->row();
	}
	##== End function to Journal ==##
	
	function get_department_name($sess_department){
		$this->db->select('DepartmentName');
		$this->db->from('am_department');
		$this->db->where('am_department.DepartmentCode', $sess_department);
		return $this->db->get()->row();
	}
	
	function get_all_to_print($data_filter) {
        /* =================================post dari find di kiri==================================================== */
        $CompanyNameFrom = isset($data_filter['txt_TransferFrom']) ? mysql_real_escape_string($data_filter['txt_TransferFrom']) : '';
        $CompanyNameTo = isset($data_filter['txt_TransferTo']) ? mysql_real_escape_string($data_filter['txt_TransferTo']) : '';
       
        $BranchCode     = $data_filter['cmb_branch'];
        $DateFrom       = $data_filter['txt_DateFrom'];
        $DateTo         = $data_filter['txt_DateTo'];
        $TransStatus    = $data_filter['chk_TransStatus'];
        /* ============================================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("CompanyFrom.CompanyName as FromCompany,CompanyTo.CompanyName as ToCompany,GLBankFrom.AccountName as BankFrom,
							GLBankTo.AccountName as BankTo,at_afiliasidebt.ADebtDate,at_afiliasidebt.ADebtNumber as AfiliasiNo,at_afiliasidebt.CompTransCode,
							at_afiliasidebt.BankTransfer,at_afiliasidebt.CompRecpCode,at_afiliasidebt.BankReceipt,at_afiliasidebt.ADebtDesc,at_afiliasidebt.ADebtAmount,at_afiliasidebt.CreditStatus,
							at_afiliasidebt.ChequeNumber,at_afiliasidebt.CqTermStatus,at_afiliasidebt.TransStatus as status,at_afiliasidebt.ChequeDate,at_afiliasidebt.LinkNumber,at_afiliasidebt.BranchCode,am_branch.BranchName,
							(case 
                                when at_afiliasidebt.TransStatus = 0 then 'Waiting' 
                                when at_afiliasidebt.TransStatus = 1 then 'On Progress' 
                                when at_afiliasidebt.TransStatus = 2 then 'Active' 
                                when at_afiliasidebt.TransStatus = 3 then 'Suspended' 
                                when at_afiliasidebt.TransStatus = 4 then 'Need Mgr. App.' 
                                when at_afiliasidebt.TransStatus = 5 then 'Need Dir. App.' 
                                when at_afiliasidebt.TransStatus = 6 then 'Need Dir & Mgr App.' end) as TransStatus, am_employee.EmployeeName as Created");
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_afiliasidebt.BranchCode', 'INNER');
        $this->db->join('am_company as CompanyFrom', 'CompanyFrom.CompanyCode = at_afiliasidebt.CompTransCode', 'INNER');
        $this->db->join('am_company as CompanyTo', 'CompanyTo.CompanyCode = at_afiliasidebt.CompRecpCode', 'INNER');
        $this->db->join('am_accountgl as GLBankFrom', 'GLBankFrom.AccountNumber = at_afiliasidebt.BankTransfer', 'INNER');
        $this->db->join('am_accountgl as GLBankTo', 'GLBankTo.AccountNumber = at_afiliasidebt.BankReceipt', 'INNER');
        $this->db->join('am_employee', $this->table . '.CreatedBy=am_employee.EmployeeCode', 'LEFT');

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

        /* ===================================find by trasnfer from dan transfer to========================================================= */
        if ($CompanyNameFrom != '') {
            $this->db->like('CompanyFrom.CompanyName', $CompanyNameFrom);
        }
        if ($CompanyNameTo != '') {
            $this->db->like('CompanyTo.CompanyName', $CompanyNameTo);
        }
        /* =============================================end=================================================================== */

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

        /* =====================================================find by status================================================== */
        if (!empty($TransStatus)) {
            $this->db->where_in('at_afiliasidebt.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_afiliasidebt.TransStatus', array(0, 1, 4, 5, 6, 7, 8));
        }

        /* =======================================================end======================================================== */

        /* ==============================================sort tabel asc========================================================== */
        $this->db->order_by($this->table . '.ADebtNumber', 'ASC');
        $this->db->order_by($this->table . '.TransStatus', 'DESC');
        /* ===============================end================================================================================= */
        $query = $this->db->limit($end, $beginning);
        $query = $this->db->get();
        return $query->result();
    }
	function get_all_to_print_LAMA($sess_branch){
		$this->db->select('CompanyFrom.CompanyName as FromCompany,CompanyTo.CompanyName as ToCompany,GLBankFrom.AccountName as BankFrom,
							GLBankTo.AccountName as BankTo,at_afiliasidebt.ADebtDate,at_afiliasidebt.ADebtNumber as AfiliasiNo,at_afiliasidebt.CompTransCode,
							at_afiliasidebt.BankTransfer,at_afiliasidebt.CompRecpCode,at_afiliasidebt.BankReceipt,at_afiliasidebt.ADebtDesc,at_afiliasidebt.ADebtAmount,
							at_afiliasidebt.ChequeNumber,at_afiliasidebt.CqTermStatus,at_afiliasidebt.TransStatus as status,at_afiliasidebt.ChequeDate,am_branch.BranchName');
		$this->db->from($this->table);
		$this->db->join('am_branch','am_branch.BranchCode = at_afiliasidebt.BranchCode','INNER');
		$this->db->join('am_company as CompanyFrom','CompanyFrom.CompanyCode = at_afiliasidebt.CompTransCode','INNER');
		$this->db->join('am_company as CompanyTo','CompanyTo.CompanyCode = at_afiliasidebt.CompRecpCode','INNER');
		$this->db->join('am_accountgl as GLBankFrom','GLBankFrom.AccountNumber = at_afiliasidebt.BankTransfer','INNER');
		$this->db->join('am_accountgl as GLBankTo','GLBankTo.AccountNumber = at_afiliasidebt.BankReceipt','INNER');
		$this->db->where($this->table.'.BranchCode', $sess_branch);
		
		$query = $this->db->get();
		return $query->result();
	}

	function get_afiliasi_number_company_receipt($LinkNumber,$BranchCode){
		$this->db->select($this->table.'.ADebtNumber,'.$this->table.'.BranchCode');
		$this->db->from($this->table);
		$this->db->where('LinkNumber',$LinkNumber);
		$this->db->where('BranchCode !=',$BranchCode);
		$query = $this->db->get()->result_array();
		if(count($query) > 0){
			return $query[0];
		}else{
			return 0;
		}
	}

	function delete_journal($arr_afiliasi_no){
		$this->db->where_in('ADebtNumber',$arr_afiliasi_no);
		$this->db->delete($this->journal);
	}

	function m_update_group_company($AfiliasiNo,$data_group){
		$this->db->where('ADebtNumber',$AfiliasiNo);
		$this->db->update($this->table,$data_group);
	}

	function update_status_approval($link_number,$data_approval){
		$this->db->where('LinkNumber',$link_number);
		$this->db->update($this->table,$data_approval);
	}

	function update_to_reverse_piutang($link_number,$data_reversed){
		$this->db->where('LinkNumber',$link_number);
		$this->db->update($this->table,$data_reversed);
	}

	function get_detail_item_print($link_number,$BranchCode,$operasi){
		$this->db->select($this->table.'.*,b_transfer.AccountName as Bank_transfer,b_receipt.AccountName as Bank_receipt,branch.BranchName,branch.BranchAddress,am_prepared.EmployeeName as nm_prepared,am_dir.EmployeeName as nm_direksi');
        $this->db->from($this->table);
        $this->db->join('am_accountgl as b_transfer','b_transfer.AccountNumber = '.$this->table.'.BankTransfer','INNER');
        $this->db->join('am_accountgl as b_receipt','b_receipt.AccountNumber = '.$this->table.'.BankReceipt','INNER');
        $this->db->join('am_branch as branch','branch.BranchCode = '.$this->table.'.BranchCode','INNER');
        $this->db->join('am_employee as am_prepared',$this->table.'.CreatedBy=am_prepared.EmployeeCode','LEFT');
        $this->db->join('am_employee as am_dir',$this->table.'.AppLevel2Code=am_dir.EmployeeCode','LEFT');
		$this->db->where('LinkNumber', $link_number);
		$this->db->where($this->table.'.BranchCode '.$operasi,$BranchCode);
		$query	= $this->db->get();
        return $query->row();
	}

	function get_link_number($no_afiliasi){
		$this->db->where('ADebtNumber',$no_afiliasi);
		$query = $this->db->get($this->table)->result_array();
		return $query[0]['LinkNumber'];
	}

	function get_no_afiliasi($link_number,$BranchCode,$operasi){
		$this->db->select('ADebtNumber');
		$this->db->from($this->table);
		$this->db->where('LinkNumber',$link_number);
		$this->db->where('BranchCode '.$operasi,$BranchCode);
		$query = $this->db->get()->result_array();
		return $query[0]['ADebtNumber'];
	}

	function cek_payment_transaction($no_afiliasi_receipt){
		$this->db->select('ap.AInsNumber');
		$this->db->from($this->tbl_payment.' as ap');
		$this->db->where('ap.ADebtNumber',$no_afiliasi_receipt);
		$this->db->where_in('ap.TransStatus',array(1,2,5));
		$query = $this->db->get()->result_array();
		if(count($query) > 0){
			return true;
		}else{
			return false;
		}
	}

	function cek_payment_transaction_journal($no_afiliasi_receipt){
		$this->db->select('apj.AInjNumber');
		$this->db->from($this->tbl_payment_journal.' as apj');
		$this->db->where('apj.ADebtNumber',$no_afiliasi_receipt);
		$this->db->where_in('apj.TransStatus',array(1,2,5));
		$query = $this->db->get()->result_array();
		if(count($query) > 0){
			return true;
		}else{
			return false;
		}
	}

	function get_link_number_payment($no_afiliasi_receipt){
		$this->db->select('LinkNumber');
		$this->db->where('ADebtNumber',$no_afiliasi_receipt);
		$query = $this->db->get('at_afiliasiinstallment')->result_array();

		if(count($query) > 0){
			return $query[0]['LinkNumber'];
		}else{
			return false;
		}
	}

	function get_no_payment_afiliasi($no_afiliasi_receipt,$sess_branch){
		$this->db->select('AInsNumber');
		$this->db->where('ADebtNumber',$no_afiliasi_receipt);
		$this->db->where('BranchCode',$sess_branch);
		$this->db->where_in('TransStatus',array(1,2,5));
		$query = $this->db->get($this->tbl_payment)->result_array();
		return $query[0]['AInsNumber'];
	}

	function get_no_payment_afiliasi_journal($no_afiliasi_receipt,$sess_branch){
		$this->db->select('AInjNumber');
		$this->db->where('ADebtNumber',$no_afiliasi_receipt);
		$this->db->where('BranchCode',$sess_branch);
		$this->db->where_in('TransStatus',array(1,2,5));
		$query = $this->db->get($this->tbl_payment_journal)->result_array();
		return $query[0]['AInjNumber'];
	}

	function delete_reconcile($TransNumber){
		$this->db->where('TransNumber',$TransNumber);
		$this->db->delete($this->reconcile);
	}

	function check_period_end($periodend){
		$this->db->select('PeriodEnd');
		$this->db->from($this->period);
		$this->db->where('PeriodEnd',$periodend);

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

		if(count($query) > 0){
			return true;
		}else{
			return false;
		}
	}

	function insert_transsaction($trans_data){

		$this->db->trans_start();

		/*=========================================== insert group  ========================================*/
		$this->insert_group($trans_data['insert_group_piutang']['data']);
		$this->insert_group($trans_data['insert_group_hutang']['data']);

		/*============================================ insert joural ========================================*/
		for($x=0;$x<count($trans_data['insert_journal']['data']);$x++){
			$this->insert_journal($trans_data['insert_journal']['data'][$x]);
		}

		$this->db->trans_complete();

		if ($this->db->trans_status() === FALSE) {
			return false;
		}else{
			return true;
		}
	}

	function update_transsaction($trans_data){

		$this->db->trans_start();

		/*=========================================== insert group  ========================================*/
		$this->update_group($trans_data['update_group']['data'],$trans_data['update_group']['link_number']);

		/*==================================== delete journal =======================================*/
		$this->delete_journal($this->trans_data['delete_journal']['TransNumber']);

		/*============================================ insert joural ========================================*/
		for($x=0;$x<count($trans_data['insert_journal']['data']);$x++){
			$this->insert_journal($trans_data['insert_journal']['data'][$x]);
		}

		$this->db->trans_complete();

		if ($this->db->trans_status() === FALSE) {
			return false;
		}else{
			return true;
		}
	}

	function reverse_transsaction($trans_data){

		$this->db->trans_start();

		/*====================================== reverse data ======================================*/
		$this->update_to_reverse_piutang($trans_data['reverse_data']['link_number'],$trans_data['reverse_data']['data']);

		/*======================================= delete reconcile =================================*/
		$this->delete_reconcile($trans_data['delete_reconcile']['TransNumber']);

		$this->db->trans_complete();

		if ($this->db->trans_status() === FALSE) {
			return false;
		}else{
			return true;
		}
	}

	function approve_transsaction($trans_data){

		$this->db->trans_start();

		/*====================================== approve data ======================================*/
		$this->update_status_approval($trans_data['approve_data']['link_number'],$trans_data['approve_data']['data']);

		$this->db->trans_complete();

		if ($this->db->trans_status() === FALSE) {
			return false;
		}else{
			return true;
		}
	}

	function suspend_transsaction($trans_data){

		$this->db->trans_start();

		/*===================================== suspend payment ============================================*/
		$this->suspend_data($trans_data['suspend_data']['data'],$trans_data['suspend_data']['link_number']);

		$this->db->trans_complete();

		if ($this->db->trans_status() === FALSE) {
			return false;
		}else{
			return true;
		}
	}
}