git.roojs.org Git - Pman.Xtuple/blob - VerifyAccounts.php

   1 <?php
   2 require_once 'Verify.php';
   3 class Pman_Xtuple_VerifyAccounts extends Pman_Xtuple_Verify {
   4
   5     function get()
   6     {
   7
   8        // die("START?");
   9         $sd = strtoupper(array_pop(explode('xtuple', HTML_FlexyFramework::get()->database)));
  10
  11
  12         $lprefix  = preg_match('/(lsg\.php|lhk.php)$/', $this->baseURL) ? 'l' : '';
  13
  14         // dump old db -> jsondecode -> get file contents ~/Dropbox/olddb...... [TBD]
  15         // get Netsuite_Accounts from SG and HK and merge
  16         //
  17         // review the "View Financial Report" to see how it's pulling
  18         //  the Balances for the internal accounting accounts
  19         //
  20         //
  21         // DB_DataObject::debugLevel(1);
  22
  23         $db = DB_DataObject::factory('accnt');
  24         $db->selectAdd();
  25         $db->selectAdd("accnt_number,
  26                     accnt_descrip,
  27                     currtocurr(
  28                         baseCurrId(),
  29                         getcurrid('HKD' ) ,
  30                         COALESCE((SELECT SUM(gltrans_amount) FROM gltrans WHERE gltrans_accnt_id=accnt_id),0),
  31                         NOW()::date
  32                     ) as balance");
  33         // $db->whereAdd('accnt_curr_id NOT IN ( 9,6) '); // THB, TWD - not conversion rates avail..
  34         $db->find();
  35         $xdb =array();
  36         while($db->fetch()) {
  37             $row = $db->toArray(FALSE, TRUE);
  38             $xtd[$row['accnt_number']] = $row;
  39         }
  40         //ksort($xtd);
  41
  42
  43         if ($sd != 'HK') {
  44             // we only report on HK data..
  45             echo json_encode($xtd);
  46             exit;
  47         }
  48         $hkdata = $xtd;
  49
  50         $xtd = (array) json_decode(file_get_contents("http://localhost/xtuple/{$lprefix}sg.php/Xtuple/VerifyAccounts"));
  51         $sgdata = array();
  52         foreach($xtd as $k=> $v) {
  53             $sgdata[(int)$k] = $v;
  54         }
  55         //print_R($sgdata);exit;
  56
  57        //  echo '<PRE>';var_dump( $sgdata );exit;
  58         // at this point we
  59         $old = $this->oldParse('HK');
  60
  61
  62         foreach($this->oldParse('SG') as $k =>$v) {
  63             $old[$k] = $v;
  64         }
  65
  66
  67
  68         foreach($hkdata as $k=>$v) {
  69             $k = (int)$k;
  70             if (isset($old[$k])) {
  71                 continue;
  72             }
  73             $v = (object)$v;
  74             $old[$k] = array(
  75                 'description' => $v->accnt_descrip,
  76                 'id' =>  $v->accnt_number,
  77
  78             );
  79         }
  80         foreach($sgdata as $k=>$v) {
  81             $k = (int)$k;
  82             if (isset($old[$k])) {
  83                 continue;
  84             }
  85             $v = (object)$v;
  86             $old[$k] = array(
  87                 'description' => $v->accnt_descrip,
  88                 'id' =>  $v->accnt_number,
  89
  90             );
  91         }
  92         //echo '<PRE>';
  93         //var_dump($old);
  94        //(int) print_r(array_keys($sgdata));
  95        // print_r(array_keys($old));
  96         foreach($old as $k=>$xx) {
  97
  98             //var_dump(array($k, $sgdata[(string)$k]));
  99
 100             $old[$k]['hkbalance']  = 0;
 101             $old[$k]['sgbalance']  = 0;
 102             if (isset($hkdata[(int)$k])) {
 103                 $v =(object)($hkdata[(int)$k]);
 104                 //print_R($v);
 105                 $old[(int)$k]['hkbalance'] = $v->balance;
 106             }
 107             if (isset($sgdata[(string)$k])) {
 108                 //echo "GOT SG? $k<BR>";
 109                 $v =(object) ($sgdata[(int)$k]);
 110                 $old[(int)$k]['sgbalance'] = $v->balance;
 111             }
 112
 113         }
 114         //die("GOT THIS FAR?");
 115         //exit;
 116
 117
 118         header('Content-type: text/csv');
 119         header( 'Content-Disposition: attachment;filename=test.csv');
 120         $fh = fopen('php://output','w');
 121
 122         fputcsv($fh, array("ID", "type", "Description", "NETSUITE", "HK", "SG", "difference"));
 123         foreach($old as $k=>$ar) {
 124             $ar = (object)$ar;
 125             $ar->netsuite_balance = isset( $ar->netsuite_balance) ? $ar->netsuite_balance : 0;
 126             $ar->acctType = empty($ar->acctType) ? '' : $ar->acctType;
 127             if ($ar->acctType == '_expense') {
 128                 $ar->netsuite_balance  = -1 * $ar->netsuite_balance  ;
 129             }
 130
 131             if (!(1 * $ar->netsuite_balance ) && !(1 *$ar->hkbalance) && !(1 * $ar->sgbalance)) {
 132                 continue;
 133             }
 134             if ( abs(abs($ar->hkbalance + $ar->sgbalance) - abs($ar->netsuite_balance)) < 100.0) {
 135                 continue;
 136             }
 137
 138
 139             fputcsv($fh, array(
 140                 $ar->id,
 141                 $ar->acctType,
 142                 $ar->description,
 143                 $ar->netsuite_balance,
 144                 $ar->hkbalance,
 145                 $ar->sgbalance,
 146                 abs($ar->hkbalance + $ar->sgbalance) - abs($ar->netsuite_balance)));
 147         }
 148         exit;
 149         // we now have all the data...
 150
 151         echo '<PRE>';
 152         print_R($old);
 153         exit;
 154
 155
 156
 157
 158
 159
 160
 161
 162
 163
 164
 165     }
 166     function oldParse($cn)
 167     {
 168
 169
 170         $har = explode('/', realpath(__FILE__));
 171         $home = '/home/'. $har[2];
 172         $fn = "$home/Dropbox/xtuple_working/old_database_snapshot/$cn/Netsuite_Account.sql.json.all";
 173         $ar = file($fn);
 174         $ret = array();
 175         foreach($ar as $i=>$l) {
 176             if (!$i) continue;
 177             $line = json_decode($l);
 178
 179             $ret[$line->id] =   array(
 180                     'id' => $line->id ,
 181                     'description' => $line->description,
 182                     'netsuite_balance' => $line->balance,
 183                     'acctType' => $line->acctType,
 184                     'is_old' => 1
 185                 );
 186         }
 187         return $ret;
 188
 189
 190
 191     }
 192     function addEndOfDay($accnt_id , $data, $bbal, $bal, $last_exchange)
 193     {
 194         $ac = DB_DataObject::factory('accnt');
 195         $ac->get($accnt_id);
 196         $num = $ac->accnt_number;
 197
 198
 199         $sd = strtoupper(substr(HTML_FlexyFramework::get()->database,-2));
 200
 201         $base = $sd == 'SG' ? 'SGD' : 'HKD';
 202
 203         $file = "/home/alan/netsuite_accounts/{$sd}-Account-". $num . '.csv';
 204
 205         $last_exchange = false;
 206
 207         // fill in dragon data..
 208
 209
 210         foreach($data as $i=>$row) {
 211
 212             // if the exchange rate changes..
 213             // then the balance will get adjusted...
 214             // EG. old rate is 5
 215             // NEW rate is 10
 216             // balance = [oldbalance] / 5 * 10
 217             if ($last_exchange !== false && $last_exchange != $row['gltrans_curr_rate']) {
 218                 $bal = ($bal / $last_exchange) * $row['gltrans_curr_rate'];
 219             }
 220             $last_exchange = $row['gltrans_curr_rate'];
 221
 222
 223
 224
 225             $bal += $row['gltrans_amount_normal'];
 226             $data[$i]['gltrans_balance'] = $bal;
 227             $bbal += $row['gltrans_base_normal'];
 228             $data[$i]['gltrans_base_balance'] = $bbal;
 229
 230
 231             $data[$i]['gltrans_nsbalance'] = '';
 232             $data[$i]['gltrans_nsbalance_diff'] = '';
 233
 234
 235         }
 236
 237
 238         if (!file_exists($file)) {
 239             //var_dump($file);exit;
 240             $bal = 0;
 241             $bbal = 0;
 242
 243
 244             return $data;
 245         }
 246         // find the end of day totals
 247
 248
 249         $fh = fopen($file ,'r');
 250         if (!$fh) {
 251             die("failed to open $file\n");
 252         }
 253
 254         $head = false;
 255         $daybal = array();
 256         $daybal_fc = array();
 257         $last_date = '';
 258         $last_balance = 0;
 259         $bal = 0.0;
 260
 261         $fcbal = 0.0;
 262         $fc = false;
 263
 264
 265         $daybal_base = array();
 266         $basebal = 0.0;
 267
 268
 269
 270         while (false !== ($row = fgetcsv($fh, 4000))) {
 271
 272             if ($head == false) {
 273                 //if (trim($row[0]) != 'Date') {
 274                 //    continue;
 275                 //}
 276
 277                 $head = array();
 278                 foreach($row as $v) {
 279                     $head[] = trim($v);
 280                 }
 281                 continue;
 282             }
 283
 284             //echo '<PRE>';print_r($head);print_r($row);exit;
 285            // print_R($head);exit;
 286             $line = array();
 287
 288             foreach($head as $i=>$k) {
 289                 if ($k == 'Date') {
 290                     $line[$k] = date('Y-m-d', strtotime(implode('-', array_reverse(explode('/', $row[$i])))));
 291                     continue;
 292                 }
 293                 if ($k == 'Amount') {
 294                     $line[$k] =$row[$i];
 295
 296                     continue;
 297
 298                 }
 299                 if (($k == 'Currency') || ($k == 'Amount (Foreign Currency)') || ($k == 'Number') ||  ($k == 'Posting')) {
 300                     $line[$k] =$row[$i];
 301                 }
 302                 // do not care about other data!?! at present.
 303                 continue;
 304
 305             }
 306             if ($line['Posting'] == 'No') {
 307                 continue;
 308             }
 309             if ($line['Number'] == 'Memorized') {
 310                 continue;
 311             }
 312
 313             $bal += 1.0*$line['Amount'];
 314             $line['Balance'] = $bal ; //$neg . preg_replace('#[^0-9.-]#','', $row[$i]);
 315
 316
 317
 318
 319             if ($fc === false) {
 320                 //echo "FC SET TO :". $line['Currency'];
 321                 $fc = $line['Currency'];
 322             } else if ($fc === '') {
 323
 324             } else if ($fc != $line['Currency']) {
 325                 $fc = '';
 326             }
 327
 328             $am = preg_replace('/[^0-9.]+/', '', $line['Amount (Foreign Currency)']);
 329             if ($line['Amount'] < 0) {
 330                 $am *= -1.0;
 331             }
 332
 333
 334             $basebal += $this->currtobase($line['Currency'] , $base, $am , $line['Date']);
 335             $daybal_base[$line['Date']] = $basebal;
 336
 337             if ($fc) {
 338
 339                 $fcbal += $am;
 340                 $daybal_fc[$line['Date']] = $fcbal;
 341             }
 342
 343
 344             if (($last_date != $line['Date']) && !empty($last_date)) {
 345                 $daybal[$last_date] = $last_balance;
 346
 347             }
 348             $last_balance = $line['Balance'];
 349             $last_date = $line['Date'];
 350
 351         }
 352         //echo '<PRE>';print_R($daybal); exit;
 353         // now let's add them to the return values..
 354         $last_date = '';
 355         $last_diff = 0;
 356         $lastbase_balance = 0.0;
 357         $bal = 0;
 358         $bbal = 0;
 359         foreach($data as $i=>$row) {
 360
 361             if (($last_date != $row['gltrans_date']) && !empty($last_date)) {
 362                  // new day..
 363                 if (!$fc) {
 364                     $data[$i-1]['gltrans_nsbalance'] = isset($daybal[$last_date]) ? $daybal[$last_date] : 0;
 365                     $data[$i-1]['gltrans_nsbalance_diff'] = sprintf("%0.2f", $data[$i-1]['gltrans_nsbalance']  - $data[$i-1]['gltrans_balance'] );
 366                     if ($last_diff != $data[$i-1]['gltrans_nsbalance_diff']) {
 367                         $bad_days[] = $last_date;
 368                     }
 369                     $last_diff = $data[$i-1]['gltrans_nsbalance_diff'];
 370                 } else {
 371                     $data[$i-1]['gltrans_nsbalance'] = isset($daybal_fc[$last_date]) ? $daybal_fc[$last_date] : 0;
 372                     $data[$i-1]['gltrans_nsbalance_diff'] = sprintf("%0.2f", $data[$i-1]['gltrans_nsbalance']  - $data[$i-1]['gltrans_balance'] );
 373
 374                 }
 375
 376                 $data[$i-1]['gltrans_nsbalance_base'] = isset($daybal_base[$last_date]) ? $daybal_base[$last_date] : $lastbase_balance;
 377
 378                 $data[$i-1]['gltrans_nsbalance_base_diff'] = sprintf("%0.2f", $data[$i-1]['gltrans_nsbalance_base']  - $data[$i-1]['gltrans_base_balance'] );
 379                 $lastbase_balance = $data[$i-1]['gltrans_nsbalance_base'];
 380
 381             }
 382             $last_date =  $row['gltrans_date'];
 383
 384
 385             $data[$i]['gltrans_nsbalance'] = '';
 386             $data[$i]['gltrans_nsbalance_diff'] = '';
 387
 388         }
 389         return $data;
 390     /*
 391         $final = array();
 392         foreach($data as $row) {
 393             if (!in_array($row['gltrans_date'], $bad_days)) {
 394                 continue;
 395             }
 396             $final[] = $row;
 397         }
 398
 399
 400
 401
 402         //echo '<PRE>';print_R($data); exit;
 403         return $final;
 404         */
 405
 406     }
 407
 408     function currtobase($from , $to, $amt , $date)
 409     {
 410         if ($from == $to) {
 411             return $amt;
 412         }
 413         $do = DB_DataObject::factory('curr_symbol');
 414         $do->query("select currtobase(getcurrid('{$from}'), {$amt}, '{$date}') as result");
 415         $do->fetch();
 416         return $do->result;
 417
 418
 419     }
 420
 421 }