2 require_once 'Verify.php';
3 class Pman_Xtuple_VerifyMigrate extends Pman_Xtuple_Verify
8 ini_set('memory_limit', '500M');
10 if (isset($_REQUEST['prods'])) {
12 $data = $this->txRecords($_REQUEST['loc'], explode(',',$_REQUEST['prods']));
13 echo json_encode($data);exit;
18 if (isset($_REQUEST['prod'])) {
20 $data = $this->txRecordsAll($_REQUEST['loc'], $_REQUEST['prod'],isset($_REQUEST['day']) ? $_REQUEST['day'] : false);
21 echo json_encode($data);exit;
27 $sd = strtoupper(array_pop(explode('xtuple', HTML_FlexyFramework::get()->database)));
30 $lprefix = preg_match('/(lsg\.php|lhk.php)$/', $this->baseURL) ? 'l' : '';
34 //DB_DataObject::debugLevel(1);
36 // A) grab the stock levels from the old database.
38 // B) grab the stock levels from the new database
40 // compare the values..
43 // use "Location: XXXX Product : XXXX" = $n
44 // we might even be able to use array_diff between the two..
45 $il = DB_DataObject::Factory('itemloc');
48 'itemloc_location_id' => 'location:location_id',
49 'itemloc_itemsite_id' => 'itemsite:itemsite_id',
55 'Location : ' || join_itemloc_location_id_location_id.location_name ||
56 ' Product : ' || (SELECT item_number FROM item WHERE
57 item_id = join_itemloc_itemsite_id_itemsite_id.itemsite_item_id LIMIT 1) as pn,
61 $il->whereAdd('itemloc_qty <> 0.0');
62 $data = $il->fetchAll('pn', 'itemloc_qty');
66 // we only report on HK data..
67 echo json_encode($data);
71 $har = explode('/', realpath(__FILE__));
72 $home = '/home/'. $har[2];
73 $fn = "$home/Dropbox/xtuple_working/old_database_snapshot/stock.json";
74 // var_dump($fn);exit;
75 $base_data = (array)json_decode(file_get_contents($fn));
76 //echo '<PRE>'; print_r($base_data);exit;
77 //var_dump($base_data['Location : Dymocks-CB Product : DM05001']);
79 //print_r($base_data);exit;
80 // run all the child processes...
83 $other_data = (array) json_decode(file_get_contents(
84 "http://localhost/xtuple/{$lprefix}sg.php/Xtuple/VerifyMigrate"));
88 $core = array_merge($data,$base_data, $other_data);
96 $out[] = array("location / sku", "OLD", "HK", "SG", "difference");
97 // merge the data and produce a sensible report..
99 foreach($core as $k=>$blah) {
102 isset($base_data[$k]) ? $base_data[$k] : 0,
103 isset($data[$k]) ? $data[$k] : 0,
104 isset($other_data[$k]) ? $other_data[$k] : 0,
106 $row[4] = $row[1] - ($row[2] + $row[3]);
110 $errors[$row[0]] = $row[1];
117 foreach($errors as $r=>$qty) {
119 $exp = explode(' Product : ', preg_replace('/^Location :/', '', $r));
121 list($loc, $prod) = $exp;
123 if (!isset($error_ar[$loc])) {
124 $error_ar[$loc] = array();
126 $error_ar[$loc][] = $prod;
128 // echo '<PRE>';print_R($error_ar);exit;
132 foreach($error_ar as $loc => $prods) {
136 $out = array_merge($out,$this->processMismatch($loc, $prods));
139 //die("got this far");
141 foreach($out as $o) {
147 header('Content-type: text/csv');
148 header( 'Content-Disposition: attachment;filename=stock_report-'.date('Y-m-d').'.csv');
149 $fh = fopen('php://output','w');
150 foreach($out as $o) {
162 foreach($errors as $r=>$qty) {
165 $exp = explode(' Product : ', preg_replace('/^Location :/', '', $r));
167 list($loc, $prod) = $exp;
172 $el[$loc] = isset($el[$loc]) ? $el[$loc] : 0;
173 if ($locmax && $el[$loc] > $locmax) {
176 if ($totmax && $eltot > $totmax) {
183 $url = 'http://localhost/xtuple/hk.php/Roo/invdetail';
184 $args = $this->toParams(array(
185 'query[item_number]'=> trim($prod),
186 'query[location_name]'=> trim($loc),
188 'sort'=>'invhist_transdate,invdetail_id',
192 'csvCols[0]' => 'invhist_transdate',
193 'csvCols[1]' => 'invhist_docnumber',
194 'csvCols[2]' => 'invhist_ordnumber',
195 'csvCols[3]' => 'invdetail_qty',
196 'csvCols[4]' => 'invdetail_bydate_qty',
197 'csvCols[5]' => 'invhist_comments',
199 'csvTitles[0]' => 'Date',
200 'csvTitles[1]' => 'Doc number',
201 'csvTitles[2]' => 'Order no.',
202 'csvTitles[3]' => 'Qty',
203 'csvTitles[4]' => 'Qty After',
204 'csvTitles[5]' => 'comments',
213 fwrite($fh, "\n\n-- ". $r ." ,,Expecting:, $qty\n");
216 fwrite($fh, file_get_contents("http://localhost/xtuple/{$lprefix}hk.php/Roo/invdetail?$args"));
217 fwrite($fh, file_get_contents("http://localhost/xtuple/{$lprefix}sg.php/Roo/invdetail?$args"));
220 $rows = $this->fcsv("http://localhost/xtuple/{$lprefix}hk.php/Roo/invdetail?$args");
221 $head = array_shift($rows);
224 $add = $this->fcsv("http://localhost/xtuple/{$lprefix}sg.php/Roo/invdetail?$args");
228 usort($rows, function($a, $b) {
229 $aa =strtotime($a[0]);
230 $bb = strtotime($b[0]);
231 if ($aa == $bb) return 0;
232 return $a < $b ? -1 : 1;
236 foreach($rows as $row)
260 echo '<PRE>';print_r($data);
267 $handle = fopen($fn, "r");
268 while (($data = fgetcsv($handle)) !== false) {
274 function fullTx($loc,$pods) {
277 return array(array('old style tx log'));
283 we have a list of location / products that do not match
285 1. load the full tx list from the database. (grouping by day) from both databases.
286 2. load the csv for that.
287 3. compare the day change totals
288 4. any day that does not match
289 - output the tx records from our system
290 - output the tx records from netsuite (csv)
296 function processMismatch($loc, $prods)
299 // we need to cache the results from this..
300 $cache = '/tmp/stock-'. $loc . '-' . date('Y-m-d');
301 if (file_exists($cache)) {
302 return unserialize(file_get_contents($cache));
307 $loc_data = $this->loadCSV($loc );
309 if (empty($loc_data)) {
310 //echo "SKIP - not data for $loc\n";
311 $ret = array(array("LOCATION: $loc"));
312 foreach($prods as $prod) {
313 $ret[] = array("-- ONLY XT DATA: $prod");
315 $data =$this->xtData($loc, $prod,false);
317 foreach($data as $tx) {
319 $ret[] = array_keys($tx);
322 $ret[] = array_values($tx);
325 file_put_contents($cache,serialize($ret));
326 exit('<meta http-equiv="refresh" content="0; url=' . urldecode('http://'.$_SERVER['HTTP_HOST'].'/xtuple/hk.php/Xtuple/VerifyMigrate?ts='.urlencode($loc)) . '"/>
328 Processing more data...... ('. $loc .')
333 echo "DONE CACHE $loc\n";exit;
338 $ourtx = $this->txRecords($loc, $prods);
340 $sd = strtoupper(array_pop(explode('xtuple', HTML_FlexyFramework::get()->database)));
343 $lprefix = preg_match('/(lsg\.php|lhk.php)$/', $this->baseURL) ? 'l' : '';
346 $other = (array) json_decode(file_get_contents(
347 "http://localhost/xtuple/{$lprefix}sg.php/Xtuple/VerifyMigrate?loc=".urlencode($loc).'&prods='.implode(',', $prods)));
351 foreach($other as $prod=>$data) {
352 $data = (array) $data;
353 foreach($data as $day=>$qty) {
354 if (isset($ourtx[$prod][$day])) {
355 $ourtx[$prod][$day] += $qty;
358 $ourtx[$prod][$day] = $qty;
365 //print_R($ourtx);exit;
366 // we should now have day summaries for them all.. compare it to the data in the database.
368 foreach($ourtx as $prod=>$data) {
369 if (!isset($loc_data[$prod])) {
370 // $ret[] = array("NO location data for $loc / $prod");
374 $prod_data = isset($loc_data[$prod]) ? $loc_data[$prod] : array();
377 $prod_changes = $this->dayChanges($prod_data);
381 //print_R($prod_changes);
384 foreach($prod_changes as $day =>$qty) {
385 if (!isset($data[$day]) || $data[$day] != $qty) {
386 $ns = isset($data[$day] ) ? $data[$day] : 0 ;
387 //echo "$day : XTUPLE: $qty : NS $ns\n";
393 foreach($data as $day =>$qty) {
394 if (!isset($prod_changes [$day]) || $prod_changes [$day] != $qty) {
395 $xt = isset($prod_changes [$day] ) ? $prod_changes [$day] : 0 ;
396 //echo "$day : XTUPLE: $xt : NS $qty\n";
402 $bad_day = array_unique($bad_day);
403 $bad_days[$prod] = $bad_day;
405 //print_R($bad_day);exit;
409 // we now have a list of bad days for that product..
413 // print_r($bad_days);
414 foreach($bad_days as $prod => $days) {
415 $prod_data = isset($loc_data[$prod]) ? $loc_data[$prod] : array();
417 $ret[] = array('LOCATION : ' . $loc . ' Product : ' . $prod);
418 $ret[] = array("--- NETSUITE: ");
420 foreach($days as $day) {
423 $day_data = isset($prod_data[$day]) ? $prod_data[$day] : array();
428 foreach($day_data as $tx) {
430 $ret[] = array_keys($tx);
433 $ret[] = array_values($tx);
436 // now dump the data from
440 $ret[] = array("--- MIGRATED: ");
443 foreach($days as $day) {
446 $day_data = isset($prod_data[$day]) ? $prod_data[$day] : array();
448 $data = $this->xtData($loc, $prod, $day);
449 foreach($data as $tx) {
451 $ret[] = array_slice(array_keys($tx), 0,7);
454 $ret[] = array_values($tx);
457 // now dump the data from
465 file_put_contents($cache,serialize($ret));
466 exit('<meta http-equiv="refresh" content="0; url=' . urldecode('http://'.$_SERVER['HTTP_HOST'].'/xtuple/hk.php/Xtuple/VerifyMigrate?ts='.urlencode($loc)) . '"/>');
467 echo "DONE CACHE $loc\n";exit;
479 function xtData($loc, $prod,$day) {
484 $locs = $this->txRecordsAll($loc,$prod,$day);
485 $sd = strtoupper(array_pop(explode('xtuple', HTML_FlexyFramework::get()->database)));
488 $lprefix = preg_match('/(lsg\.php|lhk.php)$/', $this->baseURL) ? 'l' : '';
489 //echo "http://localhost/xtuple/{$lprefix}sg.php/Xtuple/VerifyMigrate?loc=".urlencode($loc).'&prod='.$prod.'&day='.$day;
490 //print_r($loc);exit;
491 $remote = (array) json_decode(file_get_contents(
492 "http://localhost/xtuple/{$lprefix}sg.php/Xtuple/VerifyMigrate?loc=".urlencode($loc).'&prod='.$prod . ($day !== false ? ('&day='.$day) : '')));
493 foreach($remote as $line) {
494 $locs[] = (array)$line;
496 //print_r($locs);exit;
497 $GLOBALS['_DB_DATAOBJECT']['RESULTFIELDS'] = array();
498 $GLOBALS['_DB_DATAOBJECT']['RESULTS'] = array();
505 function txRecordsAll($loc, $prod, $day)
509 //DB_DataObject::debugLevel(1);
510 $d = DB_DataObject::factory('invdetail');
512 $d->applyFilters(array(
514 'item_number'=> trim($prod),
515 'location_name'=> trim($loc),
521 ),$this->authUser, $this);
522 if ($day !== false) {
523 $d->whereAdd("invhist_transdate::date = '$day'");
527 join_invhist.invhist_transdate,
528 join_invhist.invhist_docnumber,
529 join_invhist.invhist_ordnumber,
531 invdetail_bydate(invdetail_id) AS invdetail_bydate_qty ,
532 join_invhist.invhist_comments
535 $d->orderBy('invdetail_id ASC');
538 return $d->fetchAll(false,false, 'toArray');
543 function txRecords($loc, $prods)
546 foreach($prods as $prod) {
547 // DB_DataObject::debugLevel(1);
548 $d = DB_DataObject::factory('invdetail');
550 $d->applyFilters(array(
552 'item_number'=> trim($prod),
553 'location_name'=> trim($loc),
556 ),$this->authUser, $this);
557 $d->orderBy('invhist_transdate ASC');
559 $d->selectAdd('distinct(invhist_transdate::date) as invhist_transdate, sum(invdetail_qty) as invdetail_qty');
560 $d->groupBy('invhist_transdate');
561 $ret[$prod] = $d->fetchAll('invhist_transdate', 'invdetail_qty');
570 function dayChanges($cvsdata)
574 foreach($cvsdata as $day => $rows) {
577 foreach($rows as $row) {
578 $qty += $row['Qty.'];
586 // returns array of day -> [ tx, tx, tx ]
588 function loadCSV($loc)
592 $file = '/home/alan/netsuite_accounts/iad-'. $loc. '.csv';
593 if (!file_exists($file)) {
594 // echo "NO FILE: $file\n";
595 $files[$loc]= array();
599 $fh = fopen($file ,'r');
601 die("failed to open $file\n");
608 while (false !== ($row = fgetcsv($fh, 4000))) {
609 if ($head === false) {
610 if (trim($row[0]) != 'Item') {
615 foreach($row as $v) {
620 // print_R($head);exit;
622 // skip the intro line.
623 if ($row[0] == 'Inventory Item') {
627 if (preg_match('/^Total /', $row[0])) {
630 if (!strlen(trim($row[1]))) {
631 // then it's a new product.
633 $out[$product] = array( );
636 if (empty($product)) {
640 // got a normal line.
641 $day = date('Y-m-d', strtotime(implode('-', array_reverse(explode('/', $row[1])))));
642 if (!isset($out[$product][$day])) {
643 $out[$product][$day] = array();
646 foreach($head as $i=>$k) {
648 $line[$k] = date('Y-m-d', strtotime(implode('-', array_reverse(explode('/', $row[$i])))));
653 $line[$k] = trim($row[$i]);
656 $out[$product][$day][] = $line;
660 //print_R($out);exit;