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

   1 <?php
   2 require_once 'Verify.php';
   3 class Pman_Xtuple_VerifyAR extends Pman_Xtuple_Verify {
   4
   5     function get() {
   6
   7         $sd = strtoupper(array_pop(explode('xtuple', HTML_FlexyFramework::get()->database)));
   8
   9
  10         $lprefix  = preg_match('/(lsg\.php|lhk.php)$/', $this->baseURL) ? 'l' : '';
  11
  12         // dump old db -> jsondecode -> get file contents ~/Dropbox/olddb...... [TBD]
  13         // get Netsuite_Accounts from SG and HK and merge
  14         //
  15         // review the "View Financial Report" to see how it's pulling
  16         //  the Balances for the internal accounting accounts
  17         //
  18         //
  19         // DB_DataObject::debugLevel(1);
  20
  21
  22            //DB_DataObject::DebugLevel(1);
  23         $db = DB_DataObject::factory('custinfo');
  24         $db->selectAdd("
  25             cust_number,
  26             (SELECT
  27                 COALESCE(
  28                     SUM(
  29                         CASE WHEN (aropen_doctype IN ('C', 'R')) THEN
  30                            (aropen_amount - aropen_paid) * -1.0
  31                            ELSE
  32                            (aropen_amount - aropen_paid)
  33                            END
  34                     ),
  35                 0.0)
  36                 FROM aropen
  37                 WHERE
  38                     aropen_cust_id = cust_id
  39                     AND
  40                     aropen_open
  41             ) as balance
  42         ");
  43         $db->orderBy('cust_number ASC');
  44
  45         $xdb =$db->fetchAll('cust_number', 'balance');
  46 ;       //ksort($xtd);
  47
  48
  49         //if ($sd != 'HK') {
  50             // we only report on HK data..
  51         //    echo json_encode($xdb);
  52         //    exit;
  53         //}
  54         $hkdata = $xdb;
  55
  56         //$sgdata = (array) json_decode(file_get_contents("http://localhost/xtuple/{$lprefix}sg.php/Xtuple/VerifyAR"));
  57        // echo '<PRE>';var_dump($sgdata);exit;
  58
  59        //echo '<PRE>';print_R($sgdatas['97']);exit;
  60
  61        //  echo '<PRE>';var_dump( $sgdata );exit;
  62         // at this point we
  63         //$old = $this->oldParse('HK');
  64
  65
  66         foreach($this->oldParse('SG') as $k =>$v) {
  67             $old[$k] = $v;
  68         }
  69
  70
  71
  72         //echo '<PRE>';
  73         //var_dump($old);
  74        //(int) print_r(array_keys($sgdata));
  75        // print_r(array_keys($old));
  76         //exit;
  77
  78         $out = array();
  79
  80
  81         $out[] = array("Customer",  "spacer","spacer", "NETSUITE", "HK", "SG", "difference");
  82         foreach($old as $k=>$v) {
  83             $k = trim($k);
  84             $hk = isset($hkdata[''.$k]) ? $hkdata[''.$k] : 0;
  85             $sg = isset($sgdata[''.$k]) ? $sgdata[''.$k] : 0;
  86
  87             if ( ( ($hk + $sg) - $v) == 0.0) {
  88                 continue;
  89             }
  90
  91             if (abs(( ($hk + $sg) - $v)) < 1.5) {
  92                 continue;
  93             }
  94
  95
  96             $historyfor[$k] = $v;
  97
  98
  99
 100             $out[] =  array(
 101                 $k,
 102                 '',
 103                 '',
 104                 $v,
 105                 $hk,
 106                 $sg,
 107                 ($hk + $sg) - $v
 108             );
 109         }
 110
 111
 112           header('Content-type: text/csv');
 113         header( 'Content-Disposition: attachment;filename=ARreport.csv');
 114         $fh = fopen('php://output','w');
 115         foreach($out as $o) {
 116             fputcsv($fh, $o);
 117         }
 118         fclose($fh);
 119         exit;
 120         //exit;
 121         // we now have all the data...
 122
 123         // for ar - look in aropen..
 124         // eg. sgtru = SG based (224
 125
 126         // aropen  - columns... docdate, doctype, docnumber,amount, paid,
 127         $max = 99999;
 128         $i = 0;
 129         foreach($historyfor as $cust=>$expect) {
 130             $i++;
 131             if ($i > $max) {
 132                 break;
 133             }
 134             $args = $this->toParams(array(
 135                 'query[cust_number]'=> trim($cust),
 136
 137                 'sort'=>'aropen_docdate,aropen_id',
 138                 'dir'=>'ASC',
 139
 140
 141                 'csvCols[0]' => 'aropen_docdate',
 142                 'csvCols[1]' => 'aropen_doctype',
 143                 'csvCols[2]' => 'aropen_docnumber_r',
 144                 'csvCols[3]' => 'aropen_amount',
 145                 'csvCols[4]' => 'aropen_paid',
 146                 'csvCols[5]' => 'aropen_remaining',
 147                 'csvCols[6]' => 'aropen_running',
 148
 149                 'csvTitles[0]' => 'Date',
 150                 'csvTitles[1]' => 'Type',
 151                 'csvTitles[2]' => 'Doc no.',
 152                 'csvTitles[3]' => 'Amount',
 153                 'csvTitles[4]' => 'Paid',
 154                 'csvTitles[5]' => 'Outstanding',
 155                 'csvTitles[6]' => 'Running',
 156                 'start' => 0,
 157                 'limit' => 800
 158
 159             ));
 160
 161             //fwrite($fh, "\n\n\"http://www.bloomandgrowdirect.com/xtuple/{$lprefix}hk.php/Roo/aropen?$args\"\n");
 162             //fwrite($fh, "\n\n\"http://www.bloomandgrowdirect.com/xtuple/{$lprefix}sg.php/Roo/aropen?$args\"\n");
 163
 164             fwrite($fh, "\n\n-- ". $cust ." ,,Expecting:, $expect\n");
 165
 166
 167             fwrite($fh, file_get_contents("http://localhost/xtuple/{$lprefix}hk.php/Roo/aropen?$args"));
 168             fwrite($fh, file_get_contents("http://localhost/xtuple/{$lprefix}sg.php/Roo/aropen?$args"));
 169         }
 170         //echo '<PRE>';
 171         //print_R($old);
 172         //exit;
 173
 174
 175
 176         exit;
 177
 178
 179
 180
 181
 182
 183
 184     }
 185     function oldParse($cn)
 186     {
 187         $har = explode('/', realpath(__FILE__));
 188         $home = '/home/'. $har[2];
 189         $fn = "$home/Dropbox/xtuple_working/old_database_snapshot/$cn/Netsuite_Customer.sql.json.all";
 190         $ar = file($fn);
 191         $ret = array();
 192         foreach($ar as $i=>$l) {
 193             if (!$i) continue;
 194             $line = json_decode($l);
 195
 196             $ret[strtoupper($line->entityId)] =    $line->balance;
 197         }
 198         return $ret;
 199
 200
 201
 202     }
 203
 204 }