git.roojs.org Git - Pman.Xtuple/blob - Reports/SalesByCountry.php

   1 <?php
   2 require_once 'Pman/Xtuple/Reports/Base.php';
   3
   4 class Pman_Xtuple_Reports_SalesByCountry extends Pman_Xtuple_Reports_Base
   5 {
   6
   7
   8     function get()
   9     {
  10         $date_from = date('Y-m-d', strtotime($_REQUEST['date_from']));
  11         $date_to   = date('Y-m-d', strtotime($_REQUEST['date_to']));
  12         $sd = strtoupper(substr(HTML_FlexyFramework::get()->database,-2));
  13
  14         $name = $sd == 'HK' ? 'From Hong Kong' : 'From Signapore';
  15
  16         //DB_DataObject::DebugLevel(1);
  17         $q = DB_DataObject::factory('custinfo');
  18         $q->query("
  19
  20             SELECT
  21                 addr_country,
  22                 SUM(qtysold) as qtysold,
  23                 ROUND(SUM(soldat),2) as soldat_hkd
  24
  25             FROM
  26
  27
  28                 (SELECT
  29                     CASE WHEN (addr_country IS NULL OR addr_country = '') THEN
  30                         '$name'
  31                     ELSE
  32                         addr_country
  33                     END AS addr_country,
  34
  35                     ROUND(sum(invcitem_billed),0) as qtysold,
  36                     sum(invcitem_billed * currtocurr(invchead_curr_id, getcurrid('HKD'), invcitem_custprice, invchead_invcdate)) as soldat
  37
  38                     FROM
  39
  40                     invcitem
  41
  42
  43                     LEFT JOIN
  44                         invchead
  45                     ON
  46                         invchead_id = invcitem_invchead_id
  47
  48
  49
  50
  51                     LEFT JOIN
  52                         custinfo
  53                     ON
  54                         invchead_cust_id = cust_id
  55
  56                     LEFT JOIN
  57                         cntct
  58                     ON
  59                         cntct_id = cust_cntct_id
  60
  61                     LEFT JOIN
  62                         addr
  63                     ON
  64                         addr_id = cntct_addr_id
  65
  66
  67
  68                     WHERE
  69
  70                         invchead_invcdate >= '$date_from'
  71                         AND
  72                         invchead_invcdate  <= '$date_to'
  73                         AND
  74                         invchead_void = false
  75
  76                     GROUP BY
  77                         addr_country
  78
  79
  80                 )  rq
  81             GROUP BY
  82                   addr_country
  83
  84             ORDER BY
  85                 soldat_hkd DESC
  86
  87
  88         ");
  89         if (!empty($_REQUEST['format']) && $_REQUEST['format'] == 'json' ) {
  90             $ret = array();
  91             $sd = strtoupper(substr(HTML_FlexyFramework::get()->database,-2));
  92             while ($q->fetch()) {
  93
  94                 $ret[$q->addr_country] = $q->toArray($sd.'_%s',true);
  95
  96
  97             }
  98             $this->jdata($ret);
  99         }
 100
 101          $sg = array();
 102
 103         if ($sd == 'HK') {
 104         // fetch SG DATA...
 105             $xtd = json_decode(file_get_contents(
 106                     "http://localhost/xtuple/sg.php/Xtuple/Reports/SalesByCountry?date_from=$date_from&date_to=$date_to&format=json"));
 107            // echo '<PRE>';print_r($xtd);exit;
 108             $sg = (array) ($xtd->data);
 109         }
 110
 111
 112         $ours = array();
 113         while ($q->fetch()) {
 114             $add = $q->toArray('%s', true);
 115             if (!isset($head)) {
 116                 $head = array_keys($add);
 117             }
 118             $add['total_sold'] = $add['qtysold'] ;
 119             $add['total_hkd'] = $add['soldat_hkd'];
 120             $ours[$q->addr_country  ] = $add;
 121         }
 122
 123
 124
 125         $radd = false;
 126         foreach($sg as $k=>$v) {
 127             $add = (array)$v;
 128
 129
 130             unset($add['SG_addr_country']);
 131
 132             if (!$radd) {
 133                 $head = array_merge($head, array_keys( $add ));
 134                 $radd = true;
 135             }
 136             if (!isset($ours[$k]) ) {
 137
 138                 $ours[$k] = array();
 139                 $add['addr_country'] = $v->SG_addr_country;
 140                  $add['qty_sold'] = $add['SG_qtysold'];
 141                 $add['total_hkd'] = $add['SG_soldat_hkd'];
 142
 143             } else {
 144                 $ours[$k]['total_sold'] += $add['SG_qtysold'];
 145                 $ours[$k]['total_hkd'] += $add['SG_soldat_hkd'];
 146             }
 147             $ours[$k] = ($ours[$k] + $add);
 148
 149
 150         }
 151         $head[] = 'total_sold';
 152         $head[] = 'total_hkd';
 153
 154
 155         $ret = array_values($ours);
 156         usort($ret, function($a,$b) {
 157             return $a['total_hkd'] >  $b['total_hkd'] ? 1 : -1;
 158
 159
 160         });
 161
 162         //echo '<PRE>';print_r($head);
 163         //echo '<PRE>';print_r($ret);exit;
 164
 165
 166
 167
 168         $this->toCsv($ret,   $head);
 169
 170
 171
 172
 173     }
 174
 175
 176 }
 177
 178