git.roojs.org Git - Pman.Xtuple/blob - Import/HSBC.php

   1 <?php
   2
   3 require_once 'Pman/Roo.php';
   4
   5 class Pman_Xtuple_Import_HSBC extends Pman_Roo
   6 {
   7     function getAuth()
   8     {
   9         if (HTML_FlexyFramework::get()->cli) {
  10             return true;
  11         }
  12         return parent::getAuth();
  13     }
  14
  15     function post()
  16     {
  17         $this->transObj = DB_DataObject::Factory('cohead');
  18
  19         $this->transObj->query('BEGIN');
  20
  21         PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));
  22
  23         $img = DB_DataObject::Factory('images');
  24         $img->setFrom(array(
  25             'onid' => 0,
  26             'ontable' => 'ipshead'
  27         ));
  28         $img->onUpload(false);
  29
  30         require_once 'File/Convert.php';
  31         $fc = new File_Convert($img->getStoreName(), $img->mimetype );
  32         $csv = $fc->convert('text/csv');
  33         $this->importCsv($csv);
  34
  35     }
  36
  37     function importCsv($csv)
  38     {
  39         if(empty($_REQUEST['bankaccnt_id'])){
  40             $this->jerr("invalid bank account");
  41         }
  42
  43         $bankaccnt_id = (int)$_REQUEST['bankaccnt_id'];
  44
  45         $bank = DB_DataObject::factory('bankaccnt');
  46         if(!$bank->get($bankaccnt_id)){
  47             $this->jerr("invalid bank account");
  48         }
  49
  50
  51         ini_set("auto_detect_line_endings", true);
  52
  53         $fh = fopen($csv, 'r');
  54         if (!$fh) {
  55             $this->jerr("invalid file");
  56         }
  57
  58         $req = array(
  59             'DATE', 'DESCRIPTION',
  60             'WITHDRAWAL (HKD)', 'DEPOSIT (HKD)',
  61             'BALANCE (HKD)'
  62         );
  63
  64         $cols = false;
  65         $rows = array();
  66
  67         while(false !== ($n = fgetcsv($fh,10000, ',', '"'))) {
  68
  69             if (!strlen(implode('', $n))) {
  70                 continue;
  71             }
  72
  73             if (!$cols) {
  74                 $cols = array();
  75                 foreach($n as $k) {
  76                     $cols[] = strtoupper(trim($k));
  77                 }
  78
  79                 if (empty($cols)) {
  80                     continue;
  81                 }
  82
  83                 foreach($req as $r) {
  84                     if (!in_array($r,$cols)) {
  85                         $cols = false;
  86                         break;
  87                     }
  88                 }
  89                 continue;
  90             }
  91             foreach($cols as $i=>$k) {
  92                 $row[$k] = $n[$i];
  93             }
  94             $rows[] = $row;
  95         }
  96
  97         if (empty($cols)) {
  98             $this->jerr("could not find a row with " . implode(' / ', $req));
  99         }
 100
 101         fclose($fh);
 102
 103         $errMsg = array();
 104         $okMsg = array();
 105
 106         foreach ($rows as $row){
 107             if(empty($row['DESCRIPTION'])){
 108                 continue;
 109             }
 110             if(!preg_match('/HK[0-9][0-9][0-9][0-9][0-9]/', $row['DESCRIPTION'], $matches)){
 111                 continue;
 112             }
 113             $number = $matches[0];
 114             $amount = (empty($row['DEPOSIT (HKD)']) || !is_numeric($row['DEPOSIT (HKD)'])) ? 0 : $row['DEPOSIT (HKD)'];
 115
 116             $cohead = DB_DataObject::factory('cohead');
 117             if(!$cohead->get('cohead_number', $number)){
 118                 $errMsg[] = "Could not process payments: $number - no matching order";
 119                 continue;
 120             }
 121
 122             $cobmisc = DB_DataObject::factory('cobmisc');
 123             $cobmisc->cobmisc_cohead_id = $cohead->pid();
 124             $cobmisc->cobmisc_posted = 1;
 125
 126             if($cobmisc->count() != 1){
 127                 $errMsg[] = "Could not process payments: $number - no matching invoice";
 128                 continue;
 129             }
 130
 131             $cobmisc->find(true);
 132
 133             $invoice = $cobmisc->invchead();
 134             $aropen = $invoice->aropen();
 135
 136             $unpaid = ($aropen->aropen_amount - $aropen->aropen_paid) * 1;
 137
 138             if($unpaid > 0){
 139                 $cr = DB_DataObject::Factory('cashrcpt');
 140                 $cr->setFrom(array(
 141                     'cashrcpt_amount' => $unpaid,
 142                     'cashrcpt_curr_id' => $cohead->cohead_curr_id,
 143                     'cashrcpt_fundstype' => 'C',
 144                     'cashrcpt_docdate' => $cr->sqlValue("NOW()::DATE"),
 145                     'cashrcpt_distdate' => $cr->sqlValue("NOW()::DATE"),
 146                     'cashrcpt_applydate' => $cr->sqlValue("NOW()::DATE"),
 147                     'cashrcpt_cust_id' => $cohead->cohead_cust_id,
 148                     'cashrcpt_salescat_id' => -1,
 149                     'cashrcpt_discount' => 0,
 150                     'cashrcpt_usecustdeposit' => 1,
 151                     'cashrcpt_number' => $cr->sqlValue("fetchCashRcptNumber()"),
 152                     'cashrcpt_bankaccnt_id' => $bank->pid(),
 153                 ));
 154                 $cr->insert();
 155
 156                 $cr->onInsert(array('cashrcpt_aropen_id' => $aropen->pid()), $this);
 157
 158                 $cohead->sendInvoice($this);
 159
 160                 $aropen = $invoice->aropen();
 161             }
 162
 163             if(ROUND($aropen->aropen_paid,2) != ROUND($amount,2)){
 164                 $errMsg[] = "$number payment amount not correct! Order : $aropen->aropen_paid, HSBC : $amount";
 165                 continue;
 166             }
 167
 168             $okMsg[] = "succesfully imported $number";
 169
 170         }
 171
 172         if(!empty($errMsg)){
 173             $this->jerr(implode("\n", $errMsg));
 174         }
 175
 176         if(!empty($okMsg)){
 177             $this->jok(implode("<br/>", $okMsg));
 178         }
 179
 180     }
 181
 182 }