<?php
require_once 'Verify.php';
class Pman_Xtuple_VerifyAP extends Pman_Xtuple_Verify {

    function get() {
        
        $sd = strtoupper(array_pop(explode('xtuple', HTML_FlexyFramework::get()->database)));
        
        
        $lprefix  = preg_match('/(lsg\.php|lhk.php)$/', $this->baseURL) ? 'l' : '';

        // dump old db -> jsondecode -> get file contents ~/Dropbox/olddb...... [TBD]
        // get Netsuite_Accounts from SG and HK and merge 
        //
        // review the "View Financial Report" to see how it's pulling
        //  the Balances for the internal accounting accounts
        //
        //
        // DB_DataObject::debugLevel(1);

        
        //DB_DataObject::DebugLevel(1);
        $db = DB_DataObject::factory('vendinfo');
        
        $db->selectAdd(); 
       /*   currtocurr(
                            apopen_curr_id,
                            getcurrid('HKD') ,
                            CASE WHEN (apopen_doctype IN ('C', 'R')) THEN
                                (apopen_amount - apopen_paid) * -1.0
                                ELSE
                                (apopen_amount - apopen_paid)
                                END,
                            apopen_distdate
                            
                   
                    ) as apopen_remaining_hkd
              
             
        ");
       */
       //DB_DataObject::DebugLevel(1);
       $db->selectAdd("
            vend_number,
            
            
           
            ROUND(
                (SELECT
                    SUM(
                         currtocurr(
                             apopen_curr_id,
                             getcurrid('HKD' ) ,
                        
                             CASE WHEN (apopen_doctype IN ('C', 'R')) THEN
                                (apopen_amount - apopen_paid) * -1.0
                                ELSE
                                (apopen_amount - apopen_paid)
                                END
                             ,
                             apopen_distdate
                         )
                     )
                     FROM apopen
                         
                     WHERE
                         apopen_vend_id = vend_id
                         AND
                         apopen_open
                 ),
                3) as   balance
            
              
             
        ");
        
         /*
        $db->selectAdd("
            vend_number,
            
        
            (SELECT
                 COALESCE(SUM(currtocurr(
                    apopen_curr_id,
                    getcurrid('HKD' ) ,
                    open_amount,
                    NOW()::date
                )),0)
                FROM
                    (SELECT 
                            
                        apopen_curr_id,
                        COALESCE(
                            SUM(
                                CASE WHEN (apopen_doctype IN ('C', 'R')) THEN
                                   (apopen_amount - apopen_paid) * -1.0
                                   ELSE
                                   (apopen_amount - apopen_paid)
                                   END
                                ),
                                0
                        ) AS open_amount
                    
                        FROM apopen
                            WHERE
                                apopen_vend_id = vend_id
                                AND
                                apopen_open
                            GROUP BY
                                apopen_curr_id
                    ) subsel
            ) as balance
            */
        $db->orderBy('vend_number ASC');
        
        $xdb =$db->fetchAll('vend_number', 'balance');
        //ksort($xtd);
       //  exit;
        if ($sd != 'HK') {
            // we only report on HK data..
            echo json_encode($xdb);
            exit;
        }
        $hkdata = $xdb;
        
        $sgdata = (array) json_decode(file_get_contents("http://localhost/xtuple/{$lprefix}sg.php/Xtuple/VerifyAP"));
        
        //print_R($sgdata);exit;
        //echo '<PRE>' ;print_R( $hkdata);
        
       //  echo '<PRE>';var_dump( $sgdata );exit;
        // at this point we
        $old = $this->oldParse('HK');
        //echo"<PRE>"; print_r($old);
        
        foreach($this->oldParse('SG') as $k =>$v) {
            $old[$k] = $v;
        }
        
        
          
        //echo '<PRE>';
        //var_dump($old);
       //(int) print_r(array_keys($sgdata));
       // print_r(array_keys($old));
        //exit;
        $historyfor = array();
        header('Content-type: text/csv');
        header( 'Content-Disposition: attachment;filename=APsummary.csv');
        $fh = fopen('php://output','w');
        
        fputcsv($fh, array("Customer",  '','',"NETSUITE", "HK", "SG", "difference"));
        foreach($old as $k=>$v) {
            $hk = isset($hkdata[$k]) ? $hkdata[$k] : 0;
            $sg = isset($sgdata[$k]) ? $sgdata[$k] : 0;
            
            if ( ( ($hk + $sg) - $v) == 0.0) {
                continue;
            }
            
            if (abs(( ($hk + $sg) - $v)) > 1.5) {
                $historyfor[$k] = $v;
            } else {
                continue;
            }
            
            fputcsv($fh, array(
                $k,
                '','',
                $v,
                $hk,
                $sg,
                ($hk + $sg) - $v
            ));
        }
        
        
        
        
        
        // aropen  - columns... docdate, doctype, docnumber,amount, paid,
        $max = 999;
        $i = 0;
        foreach($historyfor as $vend=>$expect) {
            $i++;
            if ($i > $max) {
                break;
            }
            $args = $this->toParams(array(
                'query[vend_number]'=> trim($vend),
                 'query[in_currency]'=> 'HKD', 
                'sort'=>'apopen_docdate,apopen_id',
                'dir'=>'ASC',
                
                
                'csvCols[0]' => 'apopen_docdate',
                'csvCols[1]' => 'apopen_doctype',
                'csvCols[2]' => 'apopen_docnumber_r',
                'csvCols[3]' => 'apopen_amount_hkd',
                'csvCols[4]' => 'apopen_paid_hkd',
                'csvCols[5]' => 'apopen_remaining_hkd',
                'csvCols[6]' => 'apopen_running_hkd',
                 
                'csvTitles[0]' => 'Date',
                'csvTitles[1]' => 'Type',
                'csvTitles[2]' => 'Doc no.',
                'csvTitles[3]' => 'Amount',
                'csvTitles[4]' => 'Paid',
                'csvTitles[5]' => 'Outstanding',
                'csvTitles[6]' => 'Running',
                'start' => 0,
                'limit' => 800
                
            ));
            
            //fwrite($fh, "\n\n\"http://www.bloomandgrowdirect.com/xtuple/{$lprefix}hk.php/Roo/apopen?$args\"\n");
            //exit;
            // fwrite($fh, "\n\n\"http://www.bloomandgrowdirect.com/xtuple/{$lprefix}sg.php/Roo/apopen?$args\"\n");
            $hk = isset($hkdata[$vend]) ? $hkdata[$vend] : 0;
            $sg = isset($sgdata[$vend]) ? $sgdata[$vend] : 0;
            
            fwrite($fh, "\n\n-- ". $vend ." ,,Expecting:, $expect, HK:, $hk, SG: $sg\n");
                       
                       
            fwrite($fh, file_get_contents("http://localhost/xtuple/{$lprefix}hk.php/Roo/apopen?$args"));
            fwrite($fh, file_get_contents("http://localhost/xtuple/{$lprefix}sg.php/Roo/apopen?$args"));
        }
        //echo '<PRE>';
        //print_R($old);
        //exit;
        
        
        exit;
        
        
        
        
        
        
        
        
        
        
        
    }
    function oldParse($cn)
    {
        $har = explode('/', realpath(__FILE__));
        $home = '/home/'. $har[2];
        $fn = "$home/Dropbox/xtuple_working/old_database_snapshot/$cn/Netsuite_Vendor.sql.json.all";
        $ar = file($fn);
        $ret = array();
        foreach($ar as $i=>$l) {
            if (!$i) continue;
            $line = json_decode($l);
         
            $ret[strtoupper($line->entityId)] =    $line->balance;
        }
        return $ret;
         

        
    }
    
}