<?php
class M_piutang_karyawan extends CI_Model {
    
	public $table = "at_employeedebt";
	public $company = "am_company";
	public $account = "at_compaccountgl";
	public $employee = "am_employee";
	
	function count_piutang_number($bulan, $cek_tahun, $BranchCode){
	    $this->db->select('*');
		$this->db->from($this->table);
		$this->db->where('MONTH(EDebtDate)', $bulan);
		$this->db->where('YEAR(EDebtDate)', $cek_tahun);
		$this->db->where('BranchCode', $BranchCode);
		return $this->db->get()->num_rows();
	}
	
	function suspend($data, $id) {
        $this->db->update($this->table, $data, array('EDebtNumber' => $id));
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function get_last_piutang_number($BranchCode){
		$qty="SELECT MAX(EDebtNumber) as EDebtNumber FROM at_employeedebt WHERE BranchCode='$BranchCode'";
		return $this->db->query($qty)->result();
	}
	
	//custom data
	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_company($CompanyCode, $branch){
		$this->db->select('*');
		$this->db->from('am_branch');

		$this->db->where('CompanyCode', $CompanyCode);
		$this->db->where('BranchCode', $branch);
		
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_akun_bank_edit($branch_explode){
		$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.AccountSubTypeID', 7);
		$this->db->where('am_accountgl.AccountChild', 1);
		$this->db->where('am_accountgl.CurrencyCode', 'IDR');
		$this->db->where('am_accountgl.TransStatus', 2);
		
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_karyawan_bycompany_edit($branch_explode){
	    
		$this->db->select('am_employee.EmployeeCode, am_employee.EmployeeName');
		$this->db->from($this->employee);
		$this->db->join('am_department','am_employee.DepartmentCode=am_department.DepartmentCode');
		$this->db->join('am_branch','am_employee.BranchCode=am_branch.BranchCode');
		
		//if($BranchStatus == 1){
			$this->db->where('am_employee.BranchCode', $branch_explode);
		//}
		$this->db->where('am_employee.ResignDate', NULL);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_akun_bank($BranchCode){
		$jobtitle = $this->session->userdata("jobtitle");
		$BranchCode 	= $this->session->userdata('branch');
		$company 	= $this->session->userdata('company');
		$BranchStatus 	= $this->session->userdata('branch_status');
		
		$this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, am_accountgl.CurrencyCode');
		$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', $BranchCode);
		//$this->db->where('am_branch.BranchStatus', 0);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));			
		
		$this->db->where('am_accountgl.AccountSubTypeID', 7);
		$this->db->where('am_accountgl.CurrencyCode', 'IDR');
		$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_coa_piutang_karyawan($com_trnas, $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', $com_trnas);
		$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 get_coa_piutang_karyawan_edit($com_trnas){
		$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', $com_trnas);
		$this->db->where('at_compaccountgl.BranchCode', $com_trnas);
		$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 get_karyawan_bycompany($branch, $company_perusahaan){
	    $jobtitle = $this->session->userdata("jobtitle");
		$BranchStatus 	= $this->session->userdata('branch_status');
		 
		$this->db->select('am_employee.EmployeeCode, am_employee.EmployeeName');
		$this->db->from($this->employee);
		$this->db->join('am_department','am_employee.DepartmentCode=am_department.DepartmentCode');
		$this->db->join('am_branch','am_employee.BranchCode=am_branch.BranchCode');
		$this->db->where('am_branch.CompanyCode', $company_perusahaan);
		$this->db->where('am_department.CompanyCode', $company_perusahaan);
		$this->db->where('am_employee.ResignDate', NULL);
		
		//if($BranchStatus == 1){
			$this->db->where('am_employee.BranchCode', $branch);
		//}
		
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	// end custom
	
	function insert_piutang_karyawan($data_piutang){
	    //$this->db->trans_begin();
		$this->db->insert($this->table, $data_piutang);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function get_expt_piutang($CompanyCode){
		$this->db->select('ExpCategoryID');
		$this->db->from('sys_expensecategory');
		$this->db->where('ExpCategoryName', 'Piutang Karyawan');
		$this->db->where('CompanyCode', $CompanyCode);
		$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_piutang($CompanyCode, $ExpCategoryID){
		$this->db->select('AccountNumber');
		$this->db->from('sys_expenseaccount');
		$this->db->join('sys_expensecategory','sys_expenseaccount.ExpCategoryID=sys_expensecategory.ExpCategoryID','INNER');
		$this->db->where('sys_expenseaccount.ExpCategoryID', $ExpCategoryID);
		$this->db->where('sys_expenseaccount.AccountCode', 'Expense');
		$this->db->where('sys_expensecategory.CompanyCode', $CompanyCode);
		$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_debit_piutang($journal_debet){
	    
		$this->db->insert('at_edebtjournal', $journal_debet);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			
			return 1;
		}
	}
	
	function create_piutang_karyawan_number($date){        
        $branch_code    = $this->session->userdata('branch');
        $month          = date('m', strtotime($date));
        $year           = date('y', strtotime($date));
	
        $piutang_karyawan_number    = $branch_code."/".$month.$year."AE/";

        $this->db->like('EDebtNumber',$piutang_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  $piutang_karyawan_number.$number;
    }
	
	function insert_journal_credit_piutang($journal_credit){
	    
		$this->db->insert('at_edebtjournal', $journal_credit);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function update_piutang_karyawan($data_piutang, $EDebtNumber){
		$this->db->where('EDebtNumber', $EDebtNumber);
		$this->db->update($this->table, $data_piutang);
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function update_journal_debit_piutang($journal_debet, $EDebtNumber){
		$this->db->where('EDebtNumber', $EDebtNumber);
		$this->db->where('EDebtSaldoCredit', 0);
		$this->db->update('at_edebtjournal', $journal_debet);
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function update_journal_credit_piutang($journal_credit, $EDebtNumber){
		$this->db->where('EDebtNumber', $EDebtNumber);
		$this->db->where('EDebtSaldoDebet', 0);
		$this->db->update('at_edebtjournal', $journal_credit);
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function get_detail($id){
        $query = $this->db->get_where($this->table, array('EDebtNumber' => $id));
		if($query->num_rows()>0){
			return $query->row();
		}
		else{
			return false;
		}      
    }	
	
	function get_date_journal_piutang($piutang_number){
		$this->db->select('*');
		$this->db->from($this->table);
		$this->db->where('EDebtNumber', $piutang_number);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_all_journal_piutang($piutang_number, $branch_explode){
		$this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, at_edebtjournal.EDebtSaldoCredit, 
						   at_edebtjournal.EDebtSaldoDebet, at_edebtjournal.EDebtDesc, at_edebtjournal.EDebtNumber, at_edebtjournal.EDebtAccountNumber');
		$this->db->from('at_compaccountgl');
		$this->db->join('at_edebtjournal','at_compaccountgl.AccountNumber=at_edebtjournal.EDebtAccountNumber');
		$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_edebtjournal.EDebtNumber', $piutang_number);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function approval_status($data,$sw_number){
		$this->db->where('EDebtNumber', $sw_number);
		$this->db->update($this->table, $data);
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function cek_app($piutang_no){
		$this->db->select('AppLevel1Status, AppLevel2Status');
		$this->db->from('at_employeedebt');
		$this->db->where('EDebtNumber', $piutang_no);
		$this->db->where('AppLevel1Status', 1);
		$this->db->where('AppLevel2Status', 1);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $app = 1;
            }
            return $app;
        }
        else {
		    $app = 0;
            return $app;
        }	
	}
	
	function cek_status_app_mgr($piutang_no){
		$this->db->select('AppLevel1Status');
		$this->db->from('at_employeedebt');
		$this->db->where('EDebtNumber', $piutang_no);
		$this->db->where('AppLevel1Status', 1);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AppLevel1Status = $row->AppLevel1Status;
            }
            return $AppLevel1Status;
        }
        else {
		    return FALSE;
        }	
	}
	
	function cek_status_app_dir($piutang_no){
		$this->db->select('AppLevel2Status');
		$this->db->from('at_employeedebt');
		$this->db->where('EDebtNumber', $piutang_no);
		$this->db->where('AppLevel2Status', 1);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AppLevel2Status = $row->AppLevel2Status;
            }
            return $AppLevel2Status;
        }
        else {
		    return FALSE;
        }	
	}
	
	function status_finished($app_finished, $piutang_no){
		$this->db->where('EDebtNumber', $piutang_no);
		$this->db->update('at_employeedebt', $app_finished);
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function cek_trans_status($id){
		$this->db->select('TransStatus');
		$this->db->from('at_employeedebt');
		$this->db->where('EDebtNumber', $id);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $TransStatus = $row->TransStatus;
            }
            return $TransStatus;
        }
        else {
            return FALSE;
        }	
	}
	
	function pembatalan_transaksi($id, $data){
		$this->db->where('EDebtNumber', $id);
		return $this->db->update('at_employeedebt', $data);
		/*if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			return 1;
		}*/
	}
	
}