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

   1 <?php
   2 /**
   3  * SFX express delivery formant..
   4  *
   5  */
   6 require_once 'Pman/Xtuple/Reports/Base.php';
   7
   8 class Pman_Xtuple_Reports_SalesOrderBookingDetail extends Pman_Xtuple_Reports_Base
   9 {
  10
  11     function get()
  12     {
  13
  14         $coitem = DB_DataObject::factory('coitem');
  15         $coitem->autoJoin();
  16         $coitem->joinAddItem();
  17         $coitem->joinAddCust();
  18
  19         $coitem->_join .= "
  20             LEFT JOIN
  21                     cntct AS join_billto_cntct
  22                 ON
  23                     join_billto_cntct.cntct_id = join_coitem_cohead_id_cohead_id.cohead_billto_cntct_id
  24
  25             LEFT JOIN
  26                     cntct AS join_shipto_cntct
  27                 ON
  28                     join_shipto_cntct.cntct_id = join_coitem_cohead_id_cohead_id.cohead_shipto_cntct_id
  29             LEFT JOIN
  30                     vendaddrinfo  AS join_shipto_vendaddrinfo
  31                 ON
  32                     join_shipto_vendaddrinfo.vendaddr_addr_id = join_shipto_cntct.cntct_addr_id
  33             LEFT JOIN
  34                     vend AS join_shipto_vend
  35                 ON
  36                     join_shipto_vend.vend_id = join_shipto_vendaddrinfo.vendaddr_vend_id
  37
  38
  39         ";
  40
  41         $cntct = DB_DataObject::Factory('cntct');
  42         $coitem->selectAs($cntct, 'billto_%s', 'join_billto_cntct');
  43
  44         $cntct = DB_DataObject::Factory('cntct');
  45         $coitem->selectAs($cntct, 'shipto_%s', 'join_shipto_cntct');
  46
  47         $coitem->selectAdd("
  48                 COALESCE(join_shipto_vend.vend_name, '') as vend_name,
  49                 COALESCE(join_shipto_vendaddrinfo.vendaddr_code, '') as vendaddr_code
  50         ");
  51
  52
  53         $coitem->whereAddIn("coitem_cohead_id",  explode(',',$_REQUEST['cohead_id']), 'int');
  54
  55         $coitem->orderBy('coitem_id ASC');
  56
  57         $count = $coitem->count();
  58
  59         $items = $coitem->fetchAll();
  60
  61         require_once 'Pman/Core/SimpleExcel.php';
  62
  63         $head = array(
  64             array(
  65                 array('訂單序號','center'),
  66                 array('收件方信息','center'),
  67                 '','','','',
  68                 array('商品資訊','center'),
  69                 '','','',
  70                 array('附加服務','center'),
  71                 '','',
  72                 array('其他','center'),
  73                 '',
  74             )
  75         );
  76
  77         $merged_ranges = array(
  78             array(0,0,1,0),
  79             array(0,1,0,5),
  80             array(0,6,0,9),
  81             array(0,10,0,12),
  82             array(0,13,0,19),
  83         );
  84
  85         $x = new Pman_Core_SimpleExcel(
  86             array($items,array()),
  87
  88             array(
  89                 'workbooks' => array(
  90                     array(
  91                         'formats' => array(
  92                             'center' => array('Align'=>'center'),
  93                         ),
  94                         'workbook' => 'Sales Orders',
  95                         'nonspacer' => true,
  96                         'head'  => $head,
  97                         'merged_ranges' => $merged_ranges,
  98                         'cols' =>  array(
  99                             array(
 100                                 'header' => '',
 101                                 'width' => 75,
 102                                 'dataIndex' => 'coitem_id',
 103                             ),
 104                             array(
 105                                 'header' => '連絡人',
 106                                 'dataFormat' => 'string',
 107                                 'width' => 75,
 108                                 'dataIndex' => 'coitem_cohead_id_cohead_shiptoname',
 109                                 'format' => 'center',
 110                             ),
 111                             array(
 112                                 'header' => '聯繫電話',
 113                                 'dataFormat' => 'string',
 114                                 'width' => 75,
 115                                 'dataIndex' => 'billto_cntct_phone',
 116                                 'format' => 'center',
 117                             ),
 118                             array(
 119                                 'header' => '手機號碼',
 120                                 'dataFormat' => 'string',
 121                                 'width' => 75,
 122                                 'dataIndex' => 'shipto_cntct_phone2',
 123                                 'format' => 'center',
 124                             ),
 125                             array(
 126                                 'header' => '收件詳細地址',
 127                                 'dataFormat' => 'string',
 128                                 'width' => 75,
 129                                 'dataIndex' => 'coitem_cohead_id_cohead_shiptoaddress1',
 130                                 'format' => 'center',
 131                                 'txtrenderer' => function( $v, $worksheet, $row, $col, $o) {
 132                                     $address = '香港';
 133                                     $address .= strlen($o->vend_name) ? "[{$o->vend_name}]" : '';
 134                                     $address .= ($v) ? preg_replace('/,|\n/', '|', $v) : '';
 135                                     $address .= ($o->coitem_cohead_id_cohead_shiptoaddress2) ? "|" . preg_replace('/,|\n/', '|', $o->coitem_cohead_id_cohead_shiptoaddress2) : '';
 136                                     $address .= ($o->coitem_cohead_id_cohead_shiptoaddress3) ? "|" . preg_replace('/,|\n/', '|', $o->coitem_cohead_id_cohead_shiptoaddress3) : '';
 137                                     $address .= ($o->coitem_cohead_id_cohead_shiptoaddress4) ? "|" . preg_replace('/,|\n/', '|', $o->coitem_cohead_id_cohead_shiptoaddress4) : '';
 138                                     $address .= ($o->coitem_cohead_id_cohead_shiptoaddress5) ? "|" . preg_replace('/,|\n/', '|', $o->coitem_cohead_id_cohead_shiptoaddress5) : '';
 139                                     $address .= ($o->coitem_cohead_id_cohead_shiptocity) ? "|" . preg_replace('/,|\n/', '|', $o->coitem_cohead_id_cohead_shiptocity) : '';
 140                                     $address .= ($o->coitem_cohead_id_cohead_shiptostate) ? "|" . preg_replace('/,|\n/', '|', $o->coitem_cohead_id_cohead_shiptostate) : '';
 141                                     $address .= ($o->coitem_cohead_id_cohead_shiptozipcode) ? "|" . preg_replace('/,|\n/', '|', $o->coitem_cohead_id_cohead_shiptozipcode) : '';
 142                                     $address .= ($o->coitem_cohead_id_cohead_shiptocountry) ? "|" . preg_replace('/,|\n/', '|', $o->coitem_cohead_id_cohead_shiptocountry) : '';
 143                                     $address .= strlen($o->vendaddr_code) ? "({$o->vendaddr_code})" : '';
 144
 145                                     return "$address";
 146                                 }
 147                             ),
 148                             array(
 149                                 'header' => '買家昵稱',
 150                                 'dataFormat' => 'string',
 151                                 'width' => 75,
 152                                 'dataIndex' => '',
 153                                 'format' => 'center',
 154                             ),
 155                             array(
 156                                 'header' => '商品名稱',
 157                                 'dataFormat' => 'string',
 158                                 'width' => 75,
 159                                 'dataIndex' => 'itemsite_id_item_number',
 160                                 'format' => 'center',
 161                             ),
 162                             array(
 163                                 'header' => '商品數量',
 164                                 'dataFormat' => 'number',
 165                                 'width' => 75,
 166                                 'dataIndex' => 'coitem_qtyord',
 167                             ),
 168                             array(
 169                                 'header' => '訂單金額',
 170                                 'dataFormat' => 'string',
 171                                 'width' => 75,
 172                                 'dataIndex' => '',
 173                                 'format' => 'center',
 174                             ),
 175                             array(
 176                                 'header' => '商品金額',
 177                                 'dataFormat' => 'string',
 178                                 'width' => 75,
 179                                 'dataIndex' => '',
 180                                 'format' => 'center',
 181                             ),
 182                             array(
 183                                 'header' => '代收金額',
 184                                 'dataFormat' => 'string',
 185                                 'width' => 75,
 186                                 'dataIndex' => '',
 187                                 'format' => 'center',
 188                             ),
 189                             array(
 190                                 'header' => '保價金額',
 191                                 'dataFormat' => 'string',
 192                                 'width' => 75,
 193                                 'dataIndex' => '',
 194                                 'format' => 'center',
 195                             ),
 196                             array(
 197                                 'header' => '紙箱費',
 198                                 'dataFormat' => 'string',
 199                                 'width' => 75,
 200                                 'dataIndex' => '',
 201                                 'format' => 'center',
 202                             ),
 203                             array(
 204                                 'header' => '業務類型',
 205                                 'dataFormat' => 'string',
 206                                 'width' => 75,
 207                                 'dataIndex' => '',
 208                                 'format' => 'center',
 209                             ),
 210                              array(
 211                                 'header' => '件數',
 212                                 'dataFormat' => 'string',
 213                                 'width' => 75,
 214                                 'dataIndex' => '',
 215                                 'format' => 'center',
 216                             ),
 217                               array(
 218                                 'header' => '買家備註',
 219                                 'dataFormat' => 'string',
 220                                 'width' => 75,
 221                                 'dataIndex' => '',
 222                                 'format' => 'center',
 223                             ),
 224
 225                             array(
 226                                 'header' => '賣家備註',
 227                                 'dataFormat' => 'string',
 228                                 'width' => 75,
 229                                 'dataIndex' => 'coitem_cohead_id_cohead_number',
 230                                 'format' => 'center',
 231                                 'txtrenderer' => function( $v, $worksheet, $row, $col, $o) {
 232
 233                                     return  $v;
 234                                 }
 235                             ),
 236
 237                             array(
 238                                 'header' => '自定義1',
 239                                 'dataFormat' => 'string',
 240                                 'width' => 75,
 241                                 'dataIndex' => '',
 242                                 'format' => 'center',
 243                             ),
 244                             array(
 245                                 'header' => '自定義2',
 246                                 'dataFormat' => 'string',
 247                                 'width' => 75,
 248                                 'dataIndex' => '',
 249                                 'format' => 'center',
 250                             ),
 251                             array(
 252                                 'header' => '自定義3',
 253                                 'dataFormat' => 'string',
 254                                 'width' => 75,
 255                                 'dataIndex' => '',
 256                                 'format' => 'center',
 257                             ),
 258                         )
 259                     ),
 260                     array(
 261                         'workbook' => 'COUNT',
 262                         'head'  => array(
 263                             array('',''),
 264                             array('',''),
 265                             array('',''),
 266                             array('',''),
 267                             array('',''),
 268                             array('',$count)
 269                         ),
 270                         'cols' => array(
 271
 272                         )
 273                     )
 274                 )
 275             )
 276         );
 277
 278
 279         $x->send("order delivery report - ".date('Y-m-d').'.xls');
 280
 281 //        if (!empty($this->workbook)) {
 282 //            $this->workbook->close();
 283 //            $this->workbook = false;
 284 //        }
 285 //
 286 //        require_once 'File/Convert.php';
 287 //        $fc=  new File_Convert($x->outfile2, "application/vnd.ms-excel");
 288 //        $fn = $fc->convert("application/vnd.ms-excel");
 289 //
 290 //        $attachment = new stdClass();
 291 //        $attachment->ext = 'application/vnd.ms-excel';
 292 //        $attachment->file = chunk_split(base64_encode(file_get_contents($fn)));
 293 //        $attachment->filename = $cohead->cohead_number.'-'.date('Y-m-d').'.xls';
 294 //
 295 //        unlink($fn);
 296 //
 297 //        $data = new stdClass();
 298 //        $data->subject = $cohead->cohead_number . ' Order Booking Detail';
 299 //        $data->body = $cohead->cohead_number . ' Order Booking Detail is Attached';
 300
 301 //        require_once 'Pman/Core/Mailer.php';
 302 //
 303 //        $template = 'SalesOrderBookingDetail';
 304 //
 305 //        $random_hash = md5(date('r', time()));
 306 //
 307 //
 308 //        $r = new Pman_Core_Mailer(array(
 309 //            'template'=> $template,
 310 //            'page' => $this,
 311 //            'contents' => array(
 312 //                'random_hash' => $random_hash,
 313 //                'person' => $cohead->cohead_shipto_cntct_email,
 314 //                'data' => $data,
 315 //                'attach' => $attachment
 316 //
 317 //            )
 318 //        ));
 319 //
 320 //        $sent = $r->send();
 321 //
 322 //        if(!is_object($sent)){
 323 //            $this->jok('SUCCESS');
 324 //        }
 325 //        $this->jerr('error!!:' . $sent->toString());
 326
 327         exit;
 328     }
 329 }