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

   1 <?php
   2
   3 require_once 'Pman/Roo.php';
   4
   5 class Pman_Xtuple_Import_JournalEntry extends Pman_Roo
   6 {
   7
   8     function getAuth()
   9     {
  10         if (HTML_FlexyFramework::get()->cli) {
  11             return true;
  12         }
  13         return parent::getAuth();
  14     }
  15
  16     function post()
  17     {
  18         $this->transObj = DB_DataObject::Factory('gltrans');
  19
  20         $this->transObj->query('BEGIN');
  21
  22         PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));
  23
  24         $img = DB_DataObject::Factory('images');
  25         $img->setFrom(array(
  26             'onid' => 0,
  27             'ontable' => 'ipshead'
  28         ));
  29         $img->onUpload(false);
  30
  31         require_once 'File/Convert.php';
  32         $fc = new File_Convert($img->getStoreName(), $img->mimetype );
  33         $csv = $fc->convert('text/csv');
  34         $this->adjustBalances($csv);
  35
  36     }
  37
  38     function adjustBalances($csv)
  39     {
  40
  41         ini_set("auto_detect_line_endings", true);
  42
  43         $reference = $_REQUEST['reference'];
  44         $apply_date = $_REQUEST['apply_date'];
  45
  46         $fh = fopen($csv, 'r');
  47         if (!$fh) {
  48             $this->jerr("invalid file");
  49         }
  50
  51         $cols = false;
  52         $rows = array();
  53
  54         while(false !== ($n = fgetcsv($fh,10000, ',', '"'))) {
  55             if (!$cols) {
  56
  57                 $cols = array();
  58                 foreach($n as $k) {
  59                     $cols[] = strtoupper(trim($k));
  60                 }
  61
  62                 if (!in_array('ADJUST',$cols)) {
  63                     $this->jerr('Missing ADJUST value : ' . implode(' , ' , $cols));
  64
  65                 }
  66                 continue;
  67             }
  68             foreach($cols as $i=>$k) {
  69                 $row[$k] = $n[$i];
  70             }
  71             // ignore the accnt if the adjust is 0
  72             if (empty($row['ADJUST']) || $row['ADJUST'] == 0) {
  73                 continue;
  74             }
  75             $rows[] = $row;
  76         }
  77
  78         fclose($fh);
  79         $gl = DB_DataObject::factory('gltrans');
  80         $gl->query("
  81             SELECT
  82                 fetchGLSequence()
  83             AS
  84                 glsequence
  85         ");  $gl->fetch();
  86          $seq = $gl->glsequence;       // v_glsequence
  87         foreach ($rows as $i => $row){
  88             if(empty($row['NAME'])){
  89                 continue;
  90             }
  91             $accnt = DB_DataObject::factory('accnt');
  92             if(!$accnt->get('accnt_name', $row['NAME'])){
  93                 continue;
  94             }
  95
  96             $gl = DB_DataObject::factory('gltrans');
  97             $gl->query("
  98                 SELECT
  99                     insertIntoGLSeries(
 100                          $seq,
 101                         'G/L',
 102                         'JE',
 103                         '{$gl->escape($reference)}',
 104                         {$accnt->pid()},
 105                         {$row['ADJUST']},
 106                         '$apply_date'::date
 107                     )
 108                 AS
 109                     result
 110             ");
 111             $gl->fetch();
 112             if ($gl->result < 1) {
 113                 $this->jerr("insertIntoGLSeries INV ASS failed");
 114             }
 115         }
 116         $gl = DB_DataObject::Factory('gltrans');
 117         $gl->query("SELECT sum(glseries_amount) as result FROM glseries WHERE  glseries_sequence = $seq ");
 118         $gl->fetch();
 119         $sum = $gl->result; // the type here is string
 120
 121         if(abs($sum) > 1.0){
 122             $this->jerr('glseries amount is greater than 1.0');
 123         }
 124
 125         if($sum != 0){
 126             $rounding = $sum * -1;
 127             $gl = DB_DataObject::factory('gltrans');
 128             $gl->query("
 129                 SELECT
 130                     insertIntoGLSeries(
 131                          $seq,
 132                         'G/L',
 133                         'JE',
 134                         '{$gl->escape($reference)}',
 135                         fetchMetricValue('CurrencyGainLossAccount')::integer,
 136                         {$rounding},
 137                         '$apply_date'::date
 138                     )
 139                 AS
 140                     result
 141             ");
 142             $gl->fetch();
 143             if ($gl->result < 1) {
 144                 $this->jerr("rounding insertIntoGLSeries failed");
 145             }
 146         }
 147
 148         $gl = DB_DataObject::Factory('gltrans');
 149         $gl->query("
 150             UPDATE
 151                 glseries
 152             SET
 153                 glseries_notes = '{$gl->escape($reference)}'
 154             WHERE
 155                 glseries_sequence = $seq
 156         ");
 157
 158         $gl = DB_DataObject::Factory('gltrans');
 159         $gl->query("
 160             SELECT
 161                 postGLSeriesNoSumm(
 162                     $seq,
 163                     COALESCE(NULL,fetchJournalNumber('G/L'))
 164                 )
 165             AS
 166                 result
 167         ");
 168         $gl->fetch();
 169
 170         if ($gl->result < 1) {
 171             $this->jerr("post GL seriese failed");
 172         }
 173
 174
 175         $this->jok("DONE");
 176
 177         exit;
 178     }
 179
 180 }