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

   1 <?php
   2
   3 /**
   4  *
   5  *Import accounts xls  - based on accnt.export.sql from the metasql directory.
   6  *
   7  */
   8
   9 require_once 'Pman/Roo.php';
  10
  11 class Pman_Xtuple_Import_Accounts extends Pman_Roo
  12 {
  13     static $cli_opts = array();
  14     /**
  15      *  get .. same as roo...
  16      *
  17      */
  18
  19     function getAuth()
  20     {
  21         if (HTML_FlexyFramework::get()->cli) {
  22             return true;
  23         }
  24         return parent::getAuth();
  25     }
  26
  27
  28     function get($tab, $opts = [])
  29     {
  30         PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));
  31         //$this->jerr("INVALID");
  32         //DB_DataObject::DebugLevel(1);
  33         require_once 'File/Convert.php';
  34         $cp = $this->tempName('xls');
  35         copy(dirname(__FILE__).'/../Setup/accounts_sample.xls', $cp);
  36         $fc = new File_Convert($cp, 'application/vnd.ms-excel');
  37         //var_Dump($img->getStoreName());
  38         $csv = $fc->convert('text/csv');
  39         unlink($cp);
  40         //var_dump($csv);
  41         $this->importCsv($csv);
  42     }
  43
  44
  45     function post($tab  )
  46     {
  47
  48         $this->sessionState(0); // turn off the session..
  49         PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));
  50
  51         if (empty($_FILES['imageUpload'])) {
  52             $this->jerr("no file?!");
  53
  54         }
  55         $img = DB_DataObject::Factory('images');
  56         $img->setFrom(array(
  57             'onid' => 0,
  58             'ontable' => 'accnt'
  59         ));
  60         $img->onUpload(false);
  61
  62         require_once 'File/Convert.php';
  63         $fc = new File_Convert($img->getStoreName(), $img->mimetype );
  64         //var_Dump($img->getStoreName());
  65         $csv = $fc->convert('text/csv');
  66         //print_R($fc);exit;
  67         //var_dump($csv);exit;
  68         $this->importCsv($csv);
  69         $this->jok("Done");
  70     }
  71     function importCsv($csv)
  72     {
  73
  74         ini_set("auto_detect_line_endings", true);
  75
  76
  77         $req = array(
  78                      "type",
  79                     "subaccnttype_code",
  80                     "profit",
  81                     "number",
  82                     "sub",
  83                     "descrip",
  84                     "comments",
  85                     "currency",
  86                     "active",
  87                     "ar_account",
  88                     "ap_account",
  89                     "sales_account",
  90                     "cost_account",
  91                     "warehouse_account",
  92                     "yearend_account",
  93                     "tax_account",
  94                     "tax_account_name",
  95                     "bankaccnt_type",
  96                     "bankaccnt_accntnumber"
  97             );
  98
  99         $fh = fopen($csv,'r');
 100         // we need to break this into cols..
 101         $cols = false;
 102         $rows = array();
 103         while(false !== ($n = fgetcsv($fh,10000, ',', '"'))) {
 104
 105             if (!$cols) {
 106
 107                 $cols = array();
 108                 foreach($n as $k) {
 109
 110                     $cols[] = 'accnt_' .strtolower(trim(str_replace(' ', '_', $k)));
 111                 }
 112                 if (empty($cols)) {
 113                     continue;
 114                 }
 115                // print_R($cols);
 116                 foreach($req as $r) {
 117
 118                     if (!in_array('accnt_' .$r,$cols)) {
 119                         $missing = $r;
 120                         $cols = false;
 121                         break;
 122
 123                     }
 124                 }
 125                 if (empty($cols)) {
 126                     break;
 127                 }
 128                 continue;
 129             }
 130             $row = array();
 131             foreach($cols as $i=>$k) {
 132
 133                 $row[$k] = $n[$i];
 134             }
 135             $rows[] = $row;
 136         }
 137
 138         if (empty($cols)) {
 139             $this->jerr("could not find a column named $missing");
 140         }
 141         fclose($fh);
 142         unlink($csv);
 143         $accnts = array(
 144             'ar' => array(),
 145             'ap'=> array(),
 146             'sales'=> array(),
 147             'cost'=> array(),
 148         );
 149         foreach($rows as $r) {
 150             // do we have an account..
 151             $acc = DB_DAtaObject::Factory('accnt');
 152
 153             $r['accnt_curr_id'] = DB_DAtaObject::Factory('curr_symbol')->lookup('curr_name', $r['accnt_currency'])->curr_id;
 154
 155             if (!$acc->get('accnt_number', $r['accnt_number'])) {
 156
 157                 $acc->setFrom($acc->defaults());
 158                 $acc->setFrom($r);
 159
 160                 $sa = DB_DAtaObject::factory('subaccnt');
 161                 if (!$sa->get('subaccnt_number', $r['accnt_sub'])) {
 162                     $sa->setFrom(array(
 163                         'subaccnt_number', $r['accnt_sub'],
 164                         'subaccnt_descrip', "Sub Account " . $r['accnt_sub']
 165
 166                     ));
 167                     $sa->insert();
 168                 }
 169
 170
 171                 $sa = DB_DAtaObject::factory('prftcntr');
 172                 if (!$sa->get('prftcntr_number', $r['accnt_profit'])) {
 173                     $sa->setFrom(array(
 174                         'prftcntr_number', $r['accnt_profit'],
 175                         'prftcntr_descrip', "Profit Center " . $r['accnt_profit']
 176
 177                     ));
 178                     $sa->insert();
 179                 }
 180
 181
 182
 183
 184
 185                 $r['accnt_name'] = implode('-', array(
 186                     $acc->accnt_company,
 187                     $acc->accnt_profit,
 188                     $acc->accnt_number,
 189                      $acc->accnt_sub
 190                 ));
 191
 192
 193
 194                 $acc->insert();
 195             }
 196             if (!empty($r['accnt_bankaccnt_type'])) {
 197                 // bank account.
 198                 $ba = DB_DataObject::factory('bankaccnt');
 199                 if (!$ba->get('bankaccnt_accnt_id', $acc->pid())) {
 200                     $ba->setFrom($ba->defaults());
 201                     $ba->setFrom(array(
 202                        'bankaccnt_name' =>  $acc->accnt_descrip,
 203                        'bankaccnt_descrip' => $acc->accnt_descrip,
 204                        'bankaccnt_bankname' => 'BANK NAME',
 205                        'bankaccnt_accntnumber' => $r['accnt_bankaccnt_accntnumber'],
 206                        'bankaccnt_ar' => true,
 207                        'bankaccnt_ap' => true,
 208                        'bankaccnt_nextchknum' => 5000,
 209                        'bankaccnt_type' =>  $r['accnt_bankaccnt_type'],
 210                        'bankaccnt_accnt_id' => $acc->pid(),
 211                        'bankaccnt_curr_id' => $r['accnt_curr_id'],
 212                        'bankaccnt_notes' => '',
 213                        'bankaccnt_routing' => '',
 214                        'bankaccnt_ach_enabled' => false,
 215                        'bankaccnt_ach_origintype' => 'I'
 216                    ));
 217                     $ba->insert();
 218                 }
 219             }
 220             // tax account.
 221
 222             // cost cat..
 223             foreach(array(
 224                 'ar',
 225                 'ap',
 226                 'sales',
 227                 'cost',
 228                 ) as $col) {
 229                 if (strlen($r['accnt_'. $col.'_account'])) {
 230                     $accnts[$col][$r['accnt_'. $col.'_account']] = $acc->pid();
 231                 }
 232             }
 233             if (strlen($r['accnt_tax_account'])) {
 234                 $t = DB_DataObject::Factory('tax');
 235                 if (!$t->get('tax_code',$r['accnt_tax_account'] )) {
 236                     $t->setFrom($t->default());
 237                     $t->tax_code = $r['accnt_tax_account'];
 238                     $t->tax_descrip = $r['accnt_tax_account_name'];
 239                     $t->tax_sales_accnt_id = $acc->pid();
 240                     $t->insert();
 241                 }
 242             }
 243             if (!empty($r['yearend_account']) && strlen($r['yearend_account'])) {
 244                 $t = DB_DataObject::Factory('metric');
 245                 $t->query("select setmetric('YearEndEquityAccount', {$acc->pid()}::text)");
 246
 247             }
 248             if (!empty($r['warehouse_account']) && strlen($r['warehouse_account'])) {
 249                 $t = DB_DataObject::Factory('whsinfo');
 250                 $t->query("UPDATE whsinfo set warehous_default_accnt_id=  {$acc->pid()}");
 251             }
 252
 253
 254         }
 255
 256
 257
 258         foreach($accnts as $type => $vals) {
 259             $tbl = $type . ($type=='cost' ? 'cat' : 'accnt');
 260             $d = DB_DataObject::factory($tbl);
 261
 262             switch($tbl) {
 263                 case 'costcat':
 264                     $d->get('costcat_code','CATEGORY1');
 265                     break;
 266
 267                 case 'araccnt':
 268                     $d->araccnt_custtype_id = -1;
 269                     $d->araccnt_custtype = '.*';
 270                     $d->limit(1);
 271                     $d->find(true);
 272                     break;
 273
 274                 case 'apaccnt':
 275                     $d->apaccnt_vendtype_id = -1;
 276                     $d->apaccnt_vendtype = '.*';
 277                     $d->limit(1);
 278                     $d->find(true);
 279                     break;
 280
 281                 case 'salesaccnt':
 282                      $d->setFrom(array(
 283                         'salesaccnt_custtype_id'=> -1,
 284                         'salesaccnt_prodcat_id'=>-1,
 285                         'salesaccnt_warehous_id'=>-1,
 286                         'salesaccnt_custtype'=>'.*',
 287                         'salesaccnt_prodcat'=>'.*',
 288                     ));
 289                     $d->limit(1);
 290                     $d->find(true);
 291
 292             }
 293             foreach($vals as $v=>$id) {
 294                 $v = strtolower($v);
 295                 //echo $tbl.'_'.$v .'_accnt_id = ' . $id ."\n";
 296                 $d->{$tbl.'_'.$v .'_accnt_id'} = $id;
 297             }
 298             if (!empty($d->{$tbl . '_id'})) {
 299                 $d->update();
 300             } else {
 301                 $d->insert();
 302             }
 303
 304         }
 305
 306
 307     }
 308
 309
 310
 311
 312 }