<?php
require_once 'Pman/Xtuple/Reports/Base.php';

class Pman_Xtuple_Reports_SalesByCountryItemYear extends Pman_Xtuple_Reports_Base
{
     
    
    function get()
    {
        $base_date = date('Y-m-d', strtotime($_REQUEST['date_from']));
        $cur_y = date('Y' , strtotime($_REQUEST['date_from']));
        
        if (empty($_REQUEST['brand'])) {
            $this->jerr("missing brand");
        }
        if (empty($_REQUEST['span'])) {
            $this->jerr("missing span");
        }
        // if we restrict this by brand then the output data should not be that bad...
        
        $sd = strtoupper(substr(HTML_FlexyFramework::get()->database,-2));
        
        
        
        
        $q = DB_DataObject::factory('cohist');
        $q->selectAdd();
        $q->selectAdd("
            itemcharvalue(item_id, 'BRAND') as item_brand,
            itemcharvalue(item_id, 'PRODUCTCATEGORY') as item_productcategory,
            item_number,
            item_descrip1,
            CASE WHEN (addr_country IS NULL OR addr_country = '') THEN
                'UNKNOWN'
            ELSE 
                addr_country
            END AS  cohist_country,
            
            ROUND( COALESCE(SUM(cohist_qtyshipped * currTocurr(cohist_curr_id, getcurrid('HKD'), cohist_unitprice, cohist_shipdate)),0),2) as ship_value,
            ROUND(COALESCE(SUM(cohist_qtyshipped),0),0) as ship_qty
                    
                      
                      
        ");
        $q->_join = "
            LEFT JOIN
                custinfo
            ON
                cust_id = cohist_cust_id
                
            LEFT JOIN
                cntct
            ON
                cntct_id = cust_cntct_id 
            
            LEFT JOIN
                addr
            ON
                addr_id = cntct_addr_id 
            LEFT JOIN
                itemsite
            ON
                itemsite_id = cohist_itemsite_id
            LEFT JOIN
                item
            ON
                itemsite_item_id = item_id
         
        ";
        
        $q->whereAdd(" item_type = 'P'
                            AND
                    itemcharvalue(item_id, 'BRAND')  = '{$q->escape($_REQUEST['brand'])}'
        ");
        
        $q->groupBy("item_id,item_number,item_descrip1,addr_country");
        
        $q->orderBy("item_number ASC");
        
        
        
        $data = array();
        $countries = array();
        $span = (int) $_REQUEST['span'];
        for ($i = 0 ; $i < 2; $i++) {
            $qq = clone($q);
            if (!$i) {
                $qq->whereAdd("cohist_invcdate BETWEEN '{$base_date}'::date - INTERVAL '1 YEAR' AND '{$base_date}'::date - INTERVAL '1 YEAR' + INTERVAL '{$span} MONTH'");
                $qq->selectAdd( ($cur_y - 1) . " as cohist_year");
            } else {
                $qq->whereAdd("cohist_invcdate BETWEEN '$base_date'::date AND '$base_date'::date + INTERVAL '{$span} MONTH' ");
                $qq->selectAdd( ($cur_y ) . " as cohist_year");
            }
            
            $qq->find();
            while ($qq->fetch())  {
                if (!isset($data[$qq->item_number])) {
                    $data[$qq->item_number] = array();
                }
                
                $data[$qq->item_number][] = (object) $qq->toArray('%s', true);
                if (!isset($countries[$qq->cohist_country])) {
                    $countries[$qq->cohist_country] = 1;
                    
                }
                
                
            }
        }
        
        
        if (!empty($_REQUEST['format']) && $_REQUEST['format'] == 'json' ) {
            
            $this->jdata(array( 'data'=> $data, 'countries'=>$countries));
        }
        
         
        
        if ($sd == 'HK') {
        // fetch SG DATA...
        
            //echo "http://localhost{$this->rootURL}/sg.php/Xtuple/Reports/SalesByCountryItemYear".
            //        "?date_from={$base_date}&format=json&brand=". urlencode($_REQUEST['brand']);
            
        
            $xtd = json_decode(file_get_contents(
                    "http://localhost{$this->rootURL}/sg.php/Xtuple/Reports/SalesByCountryItemYear".
                    "?date_from={$base_date}".
                    "&format=json".
                    "&brand=". urlencode($_REQUEST['brand']).
                    "&span=".$span
           ));
           //echo '<PRE>';print_r($xtd);exit;
             
            // merge in SG data..
            foreach((array)($xtd->data->countries) as $c=>$tr) {
                $countries[$c] = 1;
            }
            
            foreach((array)($xtd->data->data) as $item=>$rdata) {
                $data[$item] = array_merge(isset($data[$item]) ? $data[$item] : array(), $rdata);
            }
            
        }
       //echo '<PRE>'; print_R($data);exit;
        
        // work out what the head rows will be
        $heads = array(
            'item_brand' => array('Brand'),
            'item_productcategory' => array('Category'),
            'item_number' => array('Name'),
            'item_descrip1' => array('Description'),
        );
        ksort($countries);
        foreach($countries as $c=>$tr) {
            for ($i = 0 ; $i < 2; $i++) {
                
                $heads['ship_qty:'.$c.':'. ($cur_y +$i -1)] = array($c, $cur_y +$i -1, 'Sum qty');
                $heads['ship_value:'.$c.':'. ($cur_y +$i -1)] = array($c, $cur_y +$i -1, 'Sum Value');
            }
        }
        for ($i = 0 ; $i < 2; $i++) {
            $heads['ship_qty:'.($cur_y +$i -1)] = array('Total Qty' , ($cur_y +$i -1) );
            $heads['ship_value:'.($cur_y +$i -1)] = array('Total Value', ($cur_y +$i -1));
        }
        
        
        $fn = 'CountryItemYear-'.urlencode($_REQUEST['brand']) . '-' . $base_date .'-for-'. $span.'Months-rptdate-';
        header('Content-type: text/csv');
        //header('Content-type: text/plain');
        header('Content-Disposition: attachment; filename="'.$fn.date('Y-m-d') . '.csv"');
            //header('Content-type: text/plain');
        $fh = fopen('php://output', 'w');
        fwrite($fh,"\xEF\xBB\xBF"); // Stupid Excel and unicode!
        for($i =0; $i< 3;$i++) {
            $row = array();
            foreach($heads as $k=>$v) {
                $row[] = isset($v[$i]) ? $v[$i] : '';
                
            }
            fputcsv($fh, $row);
            
        }
        foreach($heads as $h=>$v) {
            $heads[$h] = explode(':', $h);
        }
        
        
        foreach($data as $item=>$recs) {
            fputcsv($fh, $this->toRow($recs, $heads));
            
        }
        
         
        exit;
          
        
    }
    function toRow($recs, $head) {
        $ret = array();
       //echo '<PRE>';print_r($head);exit;
        
        foreach($head as $h=>$har) {
            //print_R($har);
            
            if (count($har) == 1) {
                // simple key - eg itmems.
                $ret[] = $recs[0]->{$h};
                continue;
            }
          
            // complex stuff..
            $val = 0;
            foreach($recs as $r) {
                
                if (count($har) == 2) {
                    // total..
                    
                    if ($r->cohist_year != $har[1]) {
                        continue;
                    }
                    $val += $r->{$har[0]};
                    continue;
                        
                }
                //print_R($har);
                //print_r($r);
                
                if ($r->cohist_country != $har[1]) {
                    continue;
                }
                if ($r->cohist_year != $har[2]) {
                    continue;
                }
                //echo "GOT $har[0] \n";
                $val += $r->{$har[0]};
                // got matching year and country..
            }
            $ret[] = $val;
            
        }
        //print_R($ret);exit;
        
        // totals...
        return $ret;
        
        
        
        
    }
    
}