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

   1 <?php
   2 require_once 'Verify.php';
   3 class Pman_Xtuple_VerifyAP 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('vendinfo');
  24
  25         $db->selectAdd();
  26        /*   currtocurr(
  27                             apopen_curr_id,
  28                             getcurrid('HKD') ,
  29                             CASE WHEN (apopen_doctype IN ('C', 'R')) THEN
  30                                 (apopen_amount - apopen_paid) * -1.0
  31                                 ELSE
  32                                 (apopen_amount - apopen_paid)
  33                                 END,
  34                             apopen_distdate
  35
  36
  37                     ) as apopen_remaining_hkd
  38
  39
  40         ");
  41        */
  42        //DB_DataObject::DebugLevel(1);
  43        $db->selectAdd("
  44             vend_number,
  45
  46
  47
  48             ROUND(
  49                 (SELECT
  50                     SUM(
  51                          currtocurr(
  52                              apopen_curr_id,
  53                              getcurrid('HKD' ) ,
  54
  55                              CASE WHEN (apopen_doctype IN ('C', 'R')) THEN
  56                                 (apopen_amount - apopen_paid) * -1.0
  57                                 ELSE
  58                                 (apopen_amount - apopen_paid)
  59                                 END
  60                              ,
  61                              apopen_distdate
  62                          )
  63                      )
  64                      FROM apopen
  65
  66                      WHERE
  67                          apopen_vend_id = vend_id
  68                          AND
  69                          apopen_open
  70                  ),
  71                 3) as   balance
  72
  73
  74
  75         ");
  76
  77          /*
  78         $db->selectAdd("
  79             vend_number,
  80
  81
  82             (SELECT
  83                  COALESCE(SUM(currtocurr(
  84                     apopen_curr_id,
  85                     getcurrid('HKD' ) ,
  86                     open_amount,
  87                     NOW()::date
  88                 )),0)
  89                 FROM
  90                     (SELECT
  91
  92                         apopen_curr_id,
  93                         COALESCE(
  94                             SUM(
  95                                 CASE WHEN (apopen_doctype IN ('C', 'R')) THEN
  96                                    (apopen_amount - apopen_paid) * -1.0
  97                                    ELSE
  98                                    (apopen_amount - apopen_paid)
  99                                    END
 100                                 ),
 101                                 0
 102                         ) AS open_amount
 103
 104                         FROM apopen
 105                             WHERE
 106                                 apopen_vend_id = vend_id
 107                                 AND
 108                                 apopen_open
 109                             GROUP BY
 110                                 apopen_curr_id
 111                     ) subsel
 112             ) as balance
 113             */
 114         $db->orderBy('vend_number ASC');
 115
 116         $xdb =$db->fetchAll('vend_number', 'balance');
 117         //ksort($xtd);
 118        //  exit;
 119         if ($sd != 'HK') {
 120             // we only report on HK data..
 121             echo json_encode($xdb);
 122             exit;
 123         }
 124         $hkdata = $xdb;
 125
 126         $sgdata = (array) json_decode(file_get_contents("http://localhost/xtuple/{$lprefix}sg.php/Xtuple/VerifyAP"));
 127
 128         //print_R($sgdata);exit;
 129         //echo '<PRE>' ;print_R( $hkdata);
 130
 131        //  echo '<PRE>';var_dump( $sgdata );exit;
 132         // at this point we
 133         $old = $this->oldParse('HK');
 134         //echo"<PRE>"; print_r($old);
 135
 136         foreach($this->oldParse('SG') as $k =>$v) {
 137             $old[$k] = $v;
 138         }
 139
 140
 141
 142         //echo '<PRE>';
 143         //var_dump($old);
 144        //(int) print_r(array_keys($sgdata));
 145        // print_r(array_keys($old));
 146         //exit;
 147         $historyfor = array();
 148         header('Content-type: text/csv');
 149         header( 'Content-Disposition: attachment;filename=APsummary.csv');
 150         $fh = fopen('php://output','w');
 151
 152         fputcsv($fh, array("Customer",  '','',"NETSUITE", "HK", "SG", "difference"));
 153         foreach($old as $k=>$v) {
 154             $hk = isset($hkdata[$k]) ? $hkdata[$k] : 0;
 155             $sg = isset($sgdata[$k]) ? $sgdata[$k] : 0;
 156
 157             if ( ( ($hk + $sg) - $v) == 0.0) {
 158                 continue;
 159             }
 160
 161             if (abs(( ($hk + $sg) - $v)) > 1.5) {
 162                 $historyfor[$k] = $v;
 163             } else {
 164                 continue;
 165             }
 166
 167             fputcsv($fh, array(
 168                 $k,
 169                 '','',
 170                 $v,
 171                 $hk,
 172                 $sg,
 173                 ($hk + $sg) - $v
 174             ));
 175         }
 176
 177
 178
 179
 180
 181         // aropen  - columns... docdate, doctype, docnumber,amount, paid,
 182         $max = 999;
 183         $i = 0;
 184         foreach($historyfor as $vend=>$expect) {
 185             $i++;
 186             if ($i > $max) {
 187                 break;
 188             }
 189             $args = $this->toParams(array(
 190                 'query[vend_number]'=> trim($vend),
 191                  'query[in_currency]'=> 'HKD',
 192                 'sort'=>'apopen_docdate,apopen_id',
 193                 'dir'=>'ASC',
 194
 195
 196                 'csvCols[0]' => 'apopen_docdate',
 197                 'csvCols[1]' => 'apopen_doctype',
 198                 'csvCols[2]' => 'apopen_docnumber_r',
 199                 'csvCols[3]' => 'apopen_amount_hkd',
 200                 'csvCols[4]' => 'apopen_paid_hkd',
 201                 'csvCols[5]' => 'apopen_remaining_hkd',
 202                 'csvCols[6]' => 'apopen_running_hkd',
 203
 204                 'csvTitles[0]' => 'Date',
 205                 'csvTitles[1]' => 'Type',
 206                 'csvTitles[2]' => 'Doc no.',
 207                 'csvTitles[3]' => 'Amount',
 208                 'csvTitles[4]' => 'Paid',
 209                 'csvTitles[5]' => 'Outstanding',
 210                 'csvTitles[6]' => 'Running',
 211                 'start' => 0,
 212                 'limit' => 800
 213
 214             ));
 215
 216             //fwrite($fh, "\n\n\"http://www.bloomandgrowdirect.com/xtuple/{$lprefix}hk.php/Roo/apopen?$args\"\n");
 217             //exit;
 218             // fwrite($fh, "\n\n\"http://www.bloomandgrowdirect.com/xtuple/{$lprefix}sg.php/Roo/apopen?$args\"\n");
 219             $hk = isset($hkdata[$vend]) ? $hkdata[$vend] : 0;
 220             $sg = isset($sgdata[$vend]) ? $sgdata[$vend] : 0;
 221
 222             fwrite($fh, "\n\n-- ". $vend ." ,,Expecting:, $expect, HK:, $hk, SG: $sg\n");
 223
 224
 225             fwrite($fh, file_get_contents("http://localhost/xtuple/{$lprefix}hk.php/Roo/apopen?$args"));
 226             fwrite($fh, file_get_contents("http://localhost/xtuple/{$lprefix}sg.php/Roo/apopen?$args"));
 227         }
 228         //echo '<PRE>';
 229         //print_R($old);
 230         //exit;
 231
 232
 233         exit;
 234
 235
 236
 237
 238
 239
 240
 241
 242
 243
 244
 245     }
 246     function oldParse($cn)
 247     {
 248         $har = explode('/', realpath(__FILE__));
 249         $home = '/home/'. $har[2];
 250         $fn = "$home/Dropbox/xtuple_working/old_database_snapshot/$cn/Netsuite_Vendor.sql.json.all";
 251         $ar = file($fn);
 252         $ret = array();
 253         foreach($ar as $i=>$l) {
 254             if (!$i) continue;
 255             $line = json_decode($l);
 256
 257             $ret[strtoupper($line->entityId)] =    $line->balance;
 258         }
 259         return $ret;
 260
 261
 262
 263     }
 264
 265 }