<?php

require_once 'Pman/Roo.php';

class Pman_Xtuple_Import_HSBC extends Pman_Roo
{
    function getAuth()
    {
        if (HTML_FlexyFramework::get()->cli) {
            return true;
        }
        return parent::getAuth();
    }
    
    function post()
    {
        $this->transObj = DB_DataObject::Factory('cohead');
        
        $this->transObj->query('BEGIN');
        
        PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));
        
        $img = DB_DataObject::Factory('images');
        $img->setFrom(array(
            'onid' => 0,
            'ontable' => 'ipshead'
        ));
        $img->onUpload(false);
        
        require_once 'File/Convert.php';
        $fc = new File_Convert($img->getStoreName(), $img->mimetype );
        $csv = $fc->convert('text/csv');
        $this->importCsv($csv);
        
    }
    
    function importCsv($csv)
    {
        if(empty($_REQUEST['bankaccnt_id'])){
            $this->jerr("invalid bank account");
        }
        
        $bankaccnt_id = (int)$_REQUEST['bankaccnt_id'];
        
        $bank = DB_DataObject::factory('bankaccnt');
        if(!$bank->get($bankaccnt_id)){
            $this->jerr("invalid bank account");
        }
        
        
        ini_set("auto_detect_line_endings", true);
        
        $fh = fopen($csv, 'r');
        if (!$fh) {
            $this->jerr("invalid file");
        }
        
        $req = array(
            'DATE', 'DESCRIPTION', 
            'WITHDRAWAL (HKD)', 'DEPOSIT (HKD)', 
            'BALANCE (HKD)'
        );
        
        $cols = false;
        $rows = array();
        
        while(false !== ($n = fgetcsv($fh,10000, ',', '"'))) {
            
            if (!strlen(implode('', $n))) {
                continue;
            }
            
            if (!$cols) {
                $cols = array();
                foreach($n as $k) {
                    $cols[] = strtoupper(trim($k));
                }
                
                if (empty($cols)) {
                    continue;
                }
                
                foreach($req as $r) {
                    if (!in_array($r,$cols)) {
                        $cols = false;
                        break;
                    }
                }
                continue;
            }
            foreach($cols as $i=>$k) {
                $row[$k] = $n[$i];
            }
            $rows[] = $row;
        }
        
        if (empty($cols)) {
            $this->jerr("could not find a row with " . implode(' / ', $req));
        }
        
        fclose($fh);
        
        $errMsg = array();
        $okMsg = array();
        
        foreach ($rows as $row){
            if(empty($row['DESCRIPTION'])){
                continue;
            }
            if(!preg_match('/HK[0-9][0-9][0-9][0-9][0-9]/', $row['DESCRIPTION'], $matches)){
                continue;
            }
            $number = $matches[0];
            $amount = (empty($row['DEPOSIT (HKD)']) || !is_numeric($row['DEPOSIT (HKD)'])) ? 0 : $row['DEPOSIT (HKD)'];
            
            $cohead = DB_DataObject::factory('cohead');
            if(!$cohead->get('cohead_number', $number)){
                $errMsg[] = "Could not process payments: $number - no matching order";
                continue;
            }
            
            $cobmisc = DB_DataObject::factory('cobmisc');
            $cobmisc->cobmisc_cohead_id = $cohead->pid();
            $cobmisc->cobmisc_posted = 1;
            
            if($cobmisc->count() != 1){
                $errMsg[] = "Could not process payments: $number - no matching invoice";
                continue;
            }
            
            $cobmisc->find(true);
            
            $invoice = $cobmisc->invchead();
            $aropen = $invoice->aropen();
            
            $unpaid = ($aropen->aropen_amount - $aropen->aropen_paid) * 1;
            
            if($unpaid > 0){
                $cr = DB_DataObject::Factory('cashrcpt');
                $cr->setFrom(array(
                    'cashrcpt_amount' => $unpaid,
                    'cashrcpt_curr_id' => $cohead->cohead_curr_id,
                    'cashrcpt_fundstype' => 'C',
                    'cashrcpt_docdate' => $cr->sqlValue("NOW()::DATE"),
                    'cashrcpt_distdate' => $cr->sqlValue("NOW()::DATE"),
                    'cashrcpt_applydate' => $cr->sqlValue("NOW()::DATE"),
                    'cashrcpt_cust_id' => $cohead->cohead_cust_id,
                    'cashrcpt_salescat_id' => -1,
                    'cashrcpt_discount' => 0,
                    'cashrcpt_usecustdeposit' => 1,
                    'cashrcpt_number' => $cr->sqlValue("fetchCashRcptNumber()"),
                    'cashrcpt_bankaccnt_id' => $bank->pid(),
                ));
                $cr->insert();

                $cr->onInsert(array('cashrcpt_aropen_id' => $aropen->pid()), $this);
                
                $cohead->sendInvoice($this);
                
                $aropen = $invoice->aropen();
            }
            
            if(ROUND($aropen->aropen_paid,2) != ROUND($amount,2)){
                $errMsg[] = "$number payment amount not correct! Order : $aropen->aropen_paid, HSBC : $amount";
                continue;
            }
            
            $okMsg[] = "succesfully imported $number";
            
        }
        
        if(!empty($errMsg)){
            $this->jerr(implode("\n", $errMsg));
        }
        
        if(!empty($okMsg)){
            $this->jok(implode("<br/>", $okMsg));
        }
        
    }
    
}