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

class Hook extends MY_Controller
{

    public $secure = false;
    public function __construct()
    {
        parent::__construct();
        date_default_timezone_set('Asia/Jakarta');
        $this->load->model('finance/bank_reconsile_detail_model');
        $this->load->model('finance/bank_reconsile_model');
        $this->load->model('finance/bank_statement_model');
        // $this->bc->set_title('HOOK');
				$this->load->helper('auto_reconsile');
    }

    public function index()
    {

    }
		
    public function autoReconsile()
    {
        // get data dari bank reconsile detail
        // where
				$request = getInputs();
				$limit = isset($request['limit'])?$request['limit']:50;
        $where_bank_reconsile = array();
        $where_bank_reconsile['fidBankStatement is null'] = null;
        // $where_bank_reconsile['transDateDeposit is not null'] = null;
        $where_bank_reconsile['processTime is null'] = null;
				$where_bank_reconsile['((left("ncCode",1) =\'1\' AND "ncCode" <> \'150\') or ("ncCode" = \'000\'))'] = null;
        $where_bank_reconsile['"transDate" >= \'2021-02-01\''] = null;
        $this->bank_reconsile_detail_model->set_limit($limit);
        $this->bank_reconsile_detail_model->set_where($where_bank_reconsile);
        $order = array();
        $order['lastCheck'] = 'ASC NULLS FIRST';
        $this->bank_reconsile_detail_model->set_order($order);
        $data_reconsile_detail = $this->bank_reconsile_detail_model->get_list();
				// echo $this->db->last_query();

        $job_processed = 0;
        $job_failed = 0;
				$reconsile_type = '';
        $whr = array();
        foreach ($data_reconsile_detail->result_array() as $key => $row) {
					$ncCode = $row['ncCode'];
					if($ncCode == '000')
					{
						$reconsile_type = 'Cash';
						$reconsile = autoReconsileCash($row,0,0);
						$whr[] = array('reconsile_type'=>$reconsile_type,'is_processed'=>$reconsile['is_processed'],'query'=>$reconsile['query_list'],'error'=>$reconsile['error']);
						if($reconsile['is_processed'])
						$job_processed++;
						if($reconsile['error'])
						$job_failed++;
					}
					if($ncCode != '150')
					{
						if(substr($ncCode, 0, 1) == '1')
						{
							$reconsile_type = 'Transfer';
							$reconsile = autoReconsileTransfer($row,0,0);
							$whr[] = array('reconsile_type'=>$reconsile_type,'is_processed'=>$reconsile['is_processed'],'query'=>$reconsile['query_list'],'error'=>$reconsile['error']);
							if($reconsile['is_processed'])
							$job_processed++;
							if($reconsile['error'])
							$job_failed++;
						}
					}
        }

				$this->result['job_processed'] = $job_processed;
				$this->result['job_failed'] = $job_failed;
				$this->result['jobs'] = $whr;
				$this->success('OK');
    }

    public function autoReconsileEDC_()
    {
        // 1. filter bank statement yang refTransaction kosong dan deskripsinya ada kata MID
        $where_bank_statement = array();
        $where_bank_statement["(\"description\" ~* 'MID')"] = null;
        $where_bank_statement['refTransaction is null'] = null;
        $this->bank_statement_model->set_where($where_bank_statement);

        $bank_statement = $this->bank_statement_model->get_list();
        foreach ($bank_statement->result_array() as $key => $row_bs) {

            $description = $row_bs['description'];
            echo ($description);
            // 2. parsing deskripsinya
            $arr = preg_split('/\s+/', $description);
            print_r($arr);
            // 3. ambil array bagian amount dan biaya admin
            // 4. looping (bandingkan amount nya) join nomor mid dan nomor rekeningnya
        }

        // $this->result['description'] = $description;
        // $this->success('Oke');
    }
    function autoReconsileEDC(){
        $this->autoReconsileEDC_BCA();
        $this->autoReconsileEDC_BRI();
    }
    function autoReconsileEDC_BCA(){
            $rowPerExec = 10;
            $id_operator = 0;
            //1. get data payment 
            //   - edc BRI yg belum tervalidasi
            //   - langsung sandingkan dengan rekening koran BRI
            $query =
<<<EOT
  select tr.*,to_char(tr."transDateEDC",'YYYYMMDD') as "TransDateText"
      ,COALESCE((tr."TotEDC"-b1.amount),0) as selesih
      ,cast(COALESCE(((tr."TotEDC"-b1.amount) / tr."TotEDC") * 100,0) as numeric(10,2)) as mdr_persen
      ,b1.amount,b1.*
  from
   (
  SELECT "businessUnitCode","storeCode","transDate" as "transDateEDC",mid,right(mid,8) as mid9,sum("amountSystem") as "TotEDC","lastCheck","ncCode"
  FROM "facc"."bankReconsileDetail" 
  WHERE "ncCode" = '610' 
      AND "fidBankStatement" is null
  GROUP BY "businessUnitCode","storeCode","transDate",mid,"lastCheck","ncCode"
  ORDER BY "lastCheck" ASC NULLS FIRST LIMIT $rowPerExec) tr 
  LEFT JOIN  (select * FROM "facc"."bankStatement" where "bankCode" ='014' AND description like '%KR OTOMATIS%') b1 ON  (b1.description LIKE '%'||tr.mid9||'% '||tr."TotEDC"||'%')
  order by "lastCheck" desc
  -- limit 100
EOT;
            $dataEDC = $this->db->query($query);
            if ($dataEDC->num_rows()==0){
                echo 'data tidak ditemukan';
                die;
            }
            //2. update untuk data-data yg sudah ketemu
            //   udpate data, tidak walau partial
            $now = date("Y-m-d H:i:s");
            foreach($dataEDC->result_array() as $row){
                $dataJurnal = array();
                $dataUpdate = array();
                $dataBankState = array();
                $comment = '';
                
                //
                if ($row['fidRekeningKoran']){
                    if ($row['mdr_persen'] >= 0 && $row['mdr_persen'] <= 2){
                        $comment = 'macthed';
                        // data bankReconsile
                        $dataUpdate['fidBankStatement'] = $row['idBankStatement'];
                        $dataUpdate['bankCode'] = $row['bankCode'];
                        $dataUpdate['transDateBS'] = $row['transDateEDC'];
                        $dataUpdate['accountNumberBS'] = $row['accountNumber'];
                        $dataUpdate['accountNameBS'] = $row['accountName'];
                        $dataUpdate['amountBS'] = $row['amount'];
                        $dataUpdate['descriptionBS'] = $row['description'];
                        $dataUpdate['processTime'] = $now;
                        $dataUpdate['updateTime'] = $now;
                        $dataUpdate['verifiedBy'] = $id_operator;
                        $dataUpdate['postedBy'] = $id_operator;//'bot-aur-edc-bri';
                        $dataUpdate['postedDate'] = $now;
                        $dataUpdate['tranStatus'] = 1;
                        $dataUpdate['verifiedDate'] = $now;
                        // data bank Statement
                        $dataBankState['refTransaction'] = $row['storeCode'].'-'.$row['TransDateText'].'-610';
                        // generate jurnal
                        $data_journal_temp = array();
                        $currency_rate = 1;
                        $data_journal_temp['transNum'] 		= '';//$data['transNum'];
                        $data_journal_temp['businessUnitCode']= $row['businessUnitCode'];
                        $data_journal_temp['dateJournal'] 	= $row['transDateEDC'];
                        $data_journal_temp['note'] 			= $row['description'];
                        $data_journal_temp['idJournal'] 	= 36;
                        $data_journal_temp['currencyRate'] 	= $currency_rate;
                        $dataJurnal[] = $data_journal_temp;
                        // generate buku kas
                    }else{
                        $comment = 'unmatched, MDR '.$row['mdr_persen'].'%';
                    }
                }else{
                    // data tidak ditemukan
                    $comment = 'data not found';
                }
                $dataUpdate['lastCheckComment'] = $comment;
                $dataUpdate['lastCheck'] = $now;
                // save data
                // BankRecsileDetail
                $where = array();
                $where['storeCode'] =$row['storeCode'];
                $where['transDate'] =$row['transDateEDC'];
                $where['mid'] =$row['mid'];
                $this->db->where($where);
                $this->db->update('facc.bankReconsileDetail',$dataUpdate);
                // BankStatement
                if (count($dataBankState)>0){
                    // jika data matched saja diupdate datanya
                    $where = array();
                    $where['idBankStatement'] =$row['idBankStatement'];
                    $this->db->where($where);
                    $this->db->update('facc.bankStatement',$dataBankState);
                    // update status heade
                    $this->bank_reconsile_model->update_edc_status($row['storeCode'],$row['TransDateText']);

                }
                //
                print_r($row);
                echo '<br>';
                // echo $this->db->last_query().'<br>';
                print_r($dataUpdate);
                echo '<br>';
                echo '<br>';
            }
    
            echo 'ok';
        }
    function autoReconsileEDC_BRI(){
        $rowPerExec = 10;
        $id_operator = 0;
        //1. get data payment 
        //   - edc BRI yg belum tervalidasi
        //   - langsung sandingkan dengan rekening koran BRI
        $query =
<<<EOT
select tr.*,to_char(tr."transDateEDC",'YYYYMMDD') as "TransDateText"
    ,COALESCE((tr."TotEDC"-b1.amount),0) as selesih
    ,cast(COALESCE(((tr."TotEDC"-b1.amount) / tr."TotEDC") * 100,0) as numeric(10,2)) as mdr_persen
    ,b1.amount,b1.*
from
 (
SELECT "businessUnitCode","storeCode","transDate" as "transDateEDC",mid,right(mid,8) as mid9,sum("amountSystem") as "TotEDC","lastCheck","ncCode"
FROM "facc"."bankReconsileDetail" 
WHERE "ncCode" = '620' 
    AND "fidBankStatement" is null
GROUP BY "businessUnitCode","storeCode","transDate",mid,"lastCheck","ncCode"
ORDER BY "lastCheck" ASC NULLS FIRST LIMIT $rowPerExec) tr 
LEFT JOIN  "facc"."bankStatement" b1 ON b1.description  like '%'||to_char(tr."transDateEDC",'YYMMDD')||'%'||tr.mid9||'%'
-- where b1."bankCode" = '002'
order by "lastCheck" desc
-- limit 100
EOT;
        $dataEDC = $this->db->query($query);
        if ($dataEDC->num_rows()==0){
            echo 'data tidak ditemukan';
            die;
        }
        //2. update untuk data-data yg sudah ketemu
        //   udpate data, tidak walau partial
        $now = date("Y-m-d H:i:s");
        foreach($dataEDC->result_array() as $row){
            $dataJurnal = array();
            $dataUpdate = array();
            $dataBankState = array();
            $comment = '';
            
            //
            if ($row['fidRekeningKoran']){
                if ($row['mdr_persen'] >= 0 && $row['mdr_persen'] <= 2){
                    $comment = 'macthed';
                    // data bankReconsile
                    $dataUpdate['fidBankStatement'] = $row['idBankStatement'];
                    $dataUpdate['bankCode'] = $row['bankCode'];
                    $dataUpdate['transDateBS'] = $row['transDateEDC'];
                    $dataUpdate['accountNumberBS'] = $row['accountNumber'];
                    $dataUpdate['accountNameBS'] = $row['accountName'];
                    $dataUpdate['amountBS'] = $row['amount'];
                    $dataUpdate['descriptionBS'] = $row['description'];
                    $dataUpdate['processTime'] = $now;
                    $dataUpdate['updateTime'] = $now;
                    $dataUpdate['verifiedBy'] = $id_operator;
                    $dataUpdate['postedBy'] = $id_operator;//'bot-aur-edc-bri';
                    $dataUpdate['postedDate'] = $now;
                    $dataUpdate['tranStatus'] = 1;
                    $dataUpdate['verifiedDate'] = $now;
                    // data bank Statement
                    $dataBankState['refTransaction'] = $row['storeCode'].'-'.$row['TransDateText'].'-620';
                    // generate jurnal
                    $data_journal_temp = array();
                    $currency_rate = 1;
					$data_journal_temp['transNum'] 		= '';//$data['transNum'];
					$data_journal_temp['businessUnitCode']= $row['businessUnitCode'];
					$data_journal_temp['dateJournal'] 	= $row['transDateEDC'];
					$data_journal_temp['note'] 			= $row['description'];
					$data_journal_temp['idJournal'] 	= 36;
					$data_journal_temp['currencyRate'] 	= $currency_rate;
                    $dataJurnal[] = $data_journal_temp;
                    // generate buku kas
                }else{
                    $comment = 'unmatched, MDR '.$row['mdr_persen'].'%';
                }
            }else{
                // data tidak ditemukan
                $comment = 'data not found';
            }
            $dataUpdate['lastCheckComment'] = $comment;
            $dataUpdate['lastCheck'] = $now;
            // save data
            // BankRecsileDetail
            $where = array();
            $where['storeCode'] =$row['storeCode'];
            $where['transDate'] =$row['transDateEDC'];
            $where['mid'] =$row['mid'];
            $this->db->where($where);
            $this->db->update('facc.bankReconsileDetail',$dataUpdate);
            // BankStatement
            if (count($dataBankState)>0){
                // jika data matched saja diupdate datanya
                $where = array();
                $where['idBankStatement'] =$row['idBankStatement'];
                $this->db->where($where);
                $this->db->update('facc.bankStatement',$dataBankState);
                // update status heade
                $this->bank_reconsile_model->update_edc_status($row['storeCode'],$row['TransDateText']);
            }
            //
            print_r($row);
            echo '<br>';
            // echo $this->db->last_query().'<br>';
            print_r($dataUpdate);
            echo '<br>';
            echo '<br>';
        }

        echo 'ok';
    }

    public function addDate()
    {
        $Date = "2020-07-29";
        echo date('Y-m-d', strtotime($Date . ' + 5 days'));
    }

}
