<?php
class M_payment_karyawan extends CI_Model {
    
	public $table = "at_employeeinstallment";
	public $detail = "at_einsdetail";
	public $table_piutang = "at_employeedebt";
	public $company = "am_company";
	public $account = "at_compaccountgl";
	public $employee = "am_employee";
	
	function count_payment_number($bulan, $cek_tahun, $BranchCode){
	    $this->db->select('*');
		$this->db->from($this->table);
		$this->db->where('MONTH(EInsDate)', $bulan);
		$this->db->where('YEAR(EInsDate)', $cek_tahun);
		$this->db->where('BranchCode', $BranchCode);
		return $this->db->get()->num_rows();
	}
	
	function get_company_edit($branch_explode){
		//$jobtitle = $this->session->userdata("jobtitle");
		$this->db->select('*');
		$this->db->from('am_branch');

		//$this->db->where('CompanyCode', $CompanyCode);
		$this->db->where('BranchCode', $branch_explode);
		
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_last_payment_number($BranchCode){
		$qty="SELECT MAX(EInsNumber) as EInsNumber FROM at_employeeinstallment WHERE BranchCode='$BranchCode'";
		return $this->db->query($qty)->result();
	}
	
	function create_payment_karyawan_number($date){        
        $branch_code    = $this->session->userdata('branch');
        $month          = date('m', strtotime($date));
        $year           = date('y', strtotime($date));
	
        $payment_karyawan_number    = $branch_code."/".$month.$year."EI/";

        $this->db->like('EInsNumber',$payment_karyawan_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  $payment_karyawan_number.$number;
    }
	
	//custom data
	function get_company($CompanyCode){
		$this->db->select('*');
		$this->db->from($this->company);
		//$this->db->where('CompanyCode', $CompanyCode);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_akun_bank($ComCode){
		$this->db->select('*');
		$this->db->from($this->account);
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		$this->db->join('am_branch','at_compaccountgl.BranchCode=am_branch.BranchCode');		
		$this->db->where('at_compaccountgl.CompanyCode', $ComCode);
		$this->db->where('am_accountgl.AccountSubTypeID', 7);
		$this->db->where('am_accountgl.AccountChild', 1);
		$this->db->where('am_accountgl.TransStatus', 2);
		$this->db->where('am_branch.BranchStatus', 0);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function update_is_lock($data_islock_status,$EDebtNumber){
		$this->db->update('at_employeedebt', $data_islock_status, array('EDebtNumber' => $EDebtNumber));
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function get_piutang($BranchCode){
	    $CompanyCode    = $this->session->userdata('company');	
		$BranchStatus 	= $this->session->userdata('branch_status');
		
		$this->db->select('at_employeedebt.CompTransCode, am_branch.BranchName, at_employeedebt.BranchCode, am_accountgl.AccountName, at_employeedebt.EDebtNumber, am_employee.EmployeeName, at_employeedebt.EmplRecpCode, at_employeedebt.EDebtAmount, at_employeedebt.EdebtIsLock');
		$this->db->from($this->table_piutang);
		$this->db->join('am_employee','at_employeedebt.EmplRecpCode=am_employee.EmployeeCode');
		$this->db->join('am_accountgl','at_employeedebt.BankTransfer=am_accountgl.AccountNumber');
		$this->db->join('am_branch','am_branch.BranchCode=at_employeedebt.BranchCode');
		//$this->db->join('at_employeeinstallment','at_employeeinstallment.EDebtNumber = at_employeedebt.EDebtNumber');
		$this->db->where_in('at_employeedebt.TransStatus', array(1));
		//if($this->session->userdata("jobtitle") != "DIR"){
			//if($BranchStatus == 1){
				$this->db->where('at_employeedebt.BranchCode', $BranchCode);
			//}
			
			$this->db->where('at_employeedebt.CompTransCode', $CompanyCode);
		//}
		$query = $this->db->get();

		return $query->result();
	}
	
	function get_akun_bank_edit($branch_explode){
		$jobtitle = $this->session->userdata("jobtitle");
		$company 	= $this->session->userdata('company');
		$BranchStatus 	= $this->session->userdata('branch_status');
		
		$this->db->select('*');
		$this->db->from($this->account);
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');		
		$this->db->join('am_branch','at_compaccountgl.BranchCode=am_branch.BranchCode');		
		
		$this->db->where('at_compaccountgl.BranchCode', $branch_explode);			
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		
		$this->db->where('am_accountgl.CurrencyCode', 'IDR');
		$this->db->where('am_accountgl.AccountSubTypeID', 7);
		$this->db->where('am_accountgl.AccountChild', 1);
		$this->db->where('am_accountgl.TransStatus', 2);
		
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	
	
	function get_sisa_piutang($EDebtNumber){
		$this->db->select('SUM(EInsAmount) as total_piutang');
		$this->db->from('at_employeeinstallment');
		$this->db->where('at_employeeinstallment.EDebtNumber', $EDebtNumber);
		$this->db->where('at_employeeinstallment.TransStatus !=', 3);
		$query = $this->db->get();
		return $query->row();
	}
	
	function get_payment_periode($id, $uri7){
		$this->db->select("*,
							(case when at_employeeinstallment.TransStatus = 4 then 'Not Yet Paid'
                            when at_employeeinstallment.TransStatus = 2 then 'Paid'
                            end) as Status
							");
		$this->db->from($this->detail);
		$this->db->join('at_employeeinstallment','at_employeeinstallment.EInsNumber=at_einsdetail.EInsNumber');		
		$this->db->where('at_einsdetail.EDebtNumber', $id);
		$this->db->where('at_einsdetail.PaymentPeriod <=', $uri7);
		$this->db->where('at_employeeinstallment.TransStatus !=', 3);
		$query = $this->db->get();		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_payment_periode_cancel($payment_before){
		$this->db->select("*,
							(case when at_employeeinstallment.TransStatus = 4 then 'Not Yet Paid'
                            when at_employeeinstallment.TransStatus = 2 then 'Paid'
                            end) as Status
							");
		$this->db->from($this->detail);
		$this->db->join('at_employeeinstallment','at_employeeinstallment.EInsNumber=at_einsdetail.EInsNumber');		
		$this->db->where('at_einsdetail.EInsNumber', $payment_before);
		$this->db->where('at_employeeinstallment.TransStatus', 3);
		$query = $this->db->get();		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_no_payment_before($piutang_no){
		$this->db->select('EInsNumber');
		$this->db->from($this->table);
		$this->db->where('EDebtNumber', $piutang_no);
		$query = $this->db->get();

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

            return $EInsNumber;
        }
        else {
            return FALSE;
        }
	}
	// end custom
	
	function get_coa_piutang_karyawan($company, $branch_code){
		$this->db->select('at_compaccountgl.AccountNumber');
		$this->db->from('at_compaccountgl');
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		
		$this->db->where('at_compaccountgl.CompanyCode', $company);
		$this->db->where('at_compaccountgl.BranchCode', $branch_code);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('am_accountgl.TransStatus', 2);
		$this->db->like('am_accountgl.AccountName', 'Piutang Karyawan');
		
		$query = $this->db->get();

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

            return $AccountNumber;
        }
        else {
            return FALSE;
        }
	}
	
	function cek_payment_data($EDebtNumber){
		$this->db->select('EDebtNumber');
		$this->db->from('at_employeeinstallment');
		$this->db->where('EDebtNumber', $EDebtNumber);
		$this->db->where('TransStatus !=', 3);
		$query = $this->db->get();

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

            return $EDebtNumber;
        }
        else {
            return FALSE;
        }
	}
	
	function get_max_period($EDebtNumber){
		$this->db->select_max('PaymentPeriod');
		$this->db->from('at_einsdetail');
		$this->db->join('at_employeeinstallment','at_employeeinstallment.EInsNumber = at_einsdetail.EInsNumber');
		$this->db->where('at_employeeinstallment.EDebtNumber', $EDebtNumber);
		$this->db->where('at_employeeinstallment.TransStatus !=', 3);
		$query = $this->db->get();

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

            return $PaymentPeriod;
        }
        else {
            return FALSE;
        }
	}
	
	function get_payment_balance($EDebtNumber){
		$this->db->select_min('PaymentBalance');
		$this->db->from('at_einsdetail');
		$this->db->join('at_employeeinstallment','at_employeeinstallment.EInsNumber = at_einsdetail.EInsNumber');
		$this->db->where('at_employeeinstallment.EDebtNumber', $EDebtNumber);
		$this->db->where('at_employeeinstallment.TransStatus !=', 3);
		$query = $this->db->get();

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

            return $PaymentBalance;
        }
        else {
            return FALSE;
        }
	}
	
	function get_history_payment($piutang_num){
		$this->db->select('at_einsdetail.LoanPayment, at_einsdetail.PaymentPeriod, at_einsdetail.PaymentPerPeriod, at_einsdetail.PaymentBalance, at_einsdetail.EInsNumber');
		$this->db->from('at_einsdetail');
		$this->db->join('at_employeeinstallment','at_employeeinstallment.EInsNumber = at_einsdetail.EInsNumber');
		$this->db->where('at_employeeinstallment.EDebtNumber', $piutang_num);
		$this->db->where('at_employeeinstallment.TransStatus !=', 3);
		return $this->db->get()->result();	
	}
	
	function get_total_payment_period($EDebtNumber){
		$this->db->select('sum(PaymentPerPeriod) as total_payment_period');
		$this->db->from('at_einsdetail');
		$this->db->join('at_employeeinstallment','at_employeeinstallment.EInsNumber = at_einsdetail.EInsNumber');
		$this->db->where('at_employeeinstallment.EDebtNumber', $EDebtNumber);
		$this->db->where('at_employeeinstallment.TransStatus !=', 3);
		$query = $this->db->get();

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

            return $total_payment_period;
        }
        else {
            return FALSE;
        }
	}
	
	function get_piutang_no_detail($id){
		$this->db->select('EDebtNumber');
		$this->db->from($this->table);
		$this->db->where('EInsNumber', $id);
		$query = $this->db->get();

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

            return $EDebtNumber;
        }
        else {
            return FALSE;
        }
	}
	
	function get_total_piutang_terakhir($id){
		$this->db->select('LoanPayment');
		$this->db->from('at_einsdetail');
		$this->db->where('EInsNumber', $id);
		$query = $this->db->get();

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

            return $LoanPayment;
        }
        else {
            return FALSE;
        }
	}
	
	function insert_payment_karyawan($data_payment){
	    //$this->db->trans_begin();
		$this->db->insert($this->table, $data_payment);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_payment_karyawan($data_payment, $EInsNumber){
		$this->db->where('EInsNumber', $EInsNumber);
		return $this->db->update($this->table, $data_payment);
	}
	
	function insert_detail_payment_karyawan($detail_payment){
	    //$this->db->trans_begin();
		$this->db->insert($this->detail, $detail_payment);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function insert_journal_debit_payment($journal_debet){
	    //$this->db->trans_begin();
		$this->db->insert('at_einsjournal', $journal_debet);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function get_expt_payment(){
		$this->db->select('ExpCategoryID');
		$this->db->from('sys_expensedetail');
		$this->db->where('ExpDetailName', 'Pembayaran Piutang');
		$query = $this->db->get();

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

            return $ExpCategoryID;
        }
        else {
            return FALSE;
        }
	}
	
	function get_account_payment($ExpCategoryID){
		$this->db->select('AccountNumber');
		$this->db->from('sys_expenseaccount');
		$this->db->join('sys_expensedetail','sys_expenseaccount.ExpCategoryID=sys_expensedetail.ExpCategoryID','INNER');
		$this->db->where('sys_expenseaccount.ExpCategoryID', $ExpCategoryID);
		$this->db->where('sys_expenseaccount.AccountCode', 'Payable');
		$query = $this->db->get();

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

            return $AccountNumber;
        }
        else {
            return FALSE;
        }
	}
	
	function insert_journal_credit_payment($journal_credit){
	    //$this->db->trans_begin();
		$this->db->insert('at_einsjournal', $journal_credit);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function get_detail($id){
        /* $query = $this->db->get_where($this->table, array('EInsNumber' => $id));
		$query = $this->db->join('am_employee','at_employeeinstallment.EmplTransCode=am_employee.EmployeeCode');
		$query = $this->db->join('am_company','at_employeeinstallment.CompRecpCode=am_company.CompanyCode'); */
		
		$this->db->select('*, at_employeeinstallment.BankReceipt,at_einsdetail.PaymentPerPeriod,at_einsdetail.PaymentBalance,at_einsdetail.PaymentPeriod,'.$this->table.'.TransStatus, at_employeedebt.TransStatus as transstatus_debt');
		$this->db->from($this->table);
		$this->db->join('am_employee','at_employeeinstallment.EmplTransCode=am_employee.EmployeeCode');
		$this->db->join('at_einsdetail','at_employeeinstallment.EInsNumber=at_einsdetail.EInsNumber');
		$this->db->join('am_company','at_employeeinstallment.CompRecpCode=am_company.CompanyCode');
		$this->db->join('at_employeedebt','at_employeedebt.EDebtNumber=at_employeeinstallment.EDebtNumber');
		$this->db->where('at_employeeinstallment.EInsNumber', $id);  
		$query = $this->db->get();
		 
		if($query->num_rows()>0){
			return $query->row();
		}
		else{
			return false;
		}      
    }	
	
	function get_date_journal_payment($payment_number){
		$this->db->select('*, am_branch.BranchName, am_company.CompanyName');
		$this->db->from($this->table);
		$this->db->join('am_branch','am_branch.BranchCode=at_employeeinstallment.BranchCode');
		$this->db->join('am_company','am_company.CompanyCode=am_branch.CompanyCode');
		$this->db->where('EInsNumber', $payment_number);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_all_journal_payment($payment_number, $branch_explode){
		$this->db->select('am_accountgl.AccountName, am_accountgl.AccountNumber, at_einsjournal.EInsSaldoDebet, 
						   at_einsjournal.EInsSaldoCredit, at_einsjournal.EInsDesc, at_einsjournal.EInsNumber, at_einsjournal.EInsAccountNumber');
		$this->db->from('at_compaccountgl');
		$this->db->join('at_einsjournal','at_compaccountgl.AccountNumber=at_einsjournal.EInsAccountNumber');
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		$this->db->where('at_compaccountgl.BranchCode', $branch_explode);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('at_einsjournal.EInsNumber', $payment_number);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function cek_approval_payment($payment_no){
		$this->db->select('AppLevel1Status');
		$this->db->from($this->table);
		$this->db->where('EInsNumber', $payment_no);
		$query = $this->db->get();

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

            return $AppLevel1Status;
        }
        else {
            return FALSE;
        }
	}
	
	function update_trans_status($payment_no, $data){
		$this->db->where('EInsNumber', $payment_no);
		$this->db->update($this->table, $data);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function approval_status($data,$pay_number){
		$this->db->where('EInsNumber', $pay_number);
		$this->db->update($this->table, $data);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_status_piutang($EDebtNumber, $status_piutang){
		$this->db->where('EDebtNumber', $EDebtNumber);
		$this->db->update($this->table_piutang, $status_piutang);
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_group($data_group, $txt_payment_no){
		$this->db->where('EInsNumber', $txt_payment_no);
		$this->db->update($this->table, $data_group);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_detail($detail_data, $txt_payment_no){
		$this->db->where('EInsNumber', $txt_payment_no);
		$this->db->update($this->detail, $detail_data);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_payment_jurnal_debet($journal_debet, $txt_payment_no){
		$this->db->where('EInsNumber', $txt_payment_no);
		$this->db->where('EInsSignStatus', 0);
		$this->db->update('at_einsjournal', $journal_debet);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_payment_jurnal_credit($journal_credit, $txt_payment_no){
		$this->db->where('EInsNumber', $txt_payment_no);
		$this->db->where('EInsSignStatus', 1);
		$this->db->update('at_einsjournal', $journal_credit);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function get_adebt_number($id){
		$this->db->select('EDebtNumber');
		$this->db->from('at_employeeinstallment');
		$this->db->where('EInsNumber', $id);
		$query = $this->db->get();

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

            return $EDebtNumber;
        }
        else {
            return FALSE;
        }
	}
	
}