<?php
class Order_ExportPnlpHfManifestController extends Ec_Controller_Action {
  public function preDispatch() {
    $this->exportFilename = 'PNL-Manifest-' . date('YmdHis') . '.xlsx';
    $this->templatePath   = APPLICATION_PATH . '/../data/file/PNL-Manifest-template.xlsx';
    $this->serviceClass   = new Service_Orders();
  }

  public function printAction() {
    set_time_limit(0);

    $exportType = (int) trim($this->getRequest()->getParam('type', 0));

    if (1 === $exportType) {
      $this->printSelected();
    }
    else {
      $this->printAll();
    }
  }

  private function printSelected($orderId = array()) {
    $selectedOrder  = $this->getRequest()->getPost('orderId', $orderId);
    $smCode         = trim($this->getRequest()->getParam('code', ''));
    $allowedCode    = array('PNLN', 'PNLG'); // 允许的运输方式
    $allowedProduct = array();
    $allowedOrder   = array();
    $allowedHsCode  = array();
    $shipTime       = 0;
    $allWeight      = 0;
    $totalOrder     = 0;

    if (empty($selectedOrder)) {
      exit('No Data ...');
    }

    if (!in_array($smCode, $allowedCode, TRUE)) {
      exit('No Data ...');
    }

    foreach ($selectedOrder as $selected) {
      if (!is_numeric($selected)) {
        continue ;
      }

      $selected = (int) $selected;

      if ($selected < 1) {
        continue ;
      }

      $order = Service_Orders::getByField($selected, 'order_id');

      if (empty($order) || $smCode !== $order['sm_code']) {
        continue ;
      }

      $address   = Service_OrderAddressBook::getByField($order['order_code'], 'order_code');
      $shipOrder = Service_ShipOrder::getByField($order['order_code'], 'order_code');
      $country   = Service_Country::getByField((int) $address['oab_country_id'], 'country_id');
      $products  = Service_OrderProduct::getByCondition(array('order_code' => $order['order_code']));
      $operation = Service_OrderOperationTime::getByField($order['order_code'], 'order_code');

      ++$totalOrder; // 统计订单数量
      $allWeight += $shipOrder ? $shipOrder['so_weight'] : 0; // 统计订单总重量
      $allowedOrder[$order['order_code']] = array(
        'ship_time'    => date('Y/n/j', strtotime($operation['ship_time'])),
        'receiver'     => $address['oab_firstname'] . ' ' . $address['oab_lastname'],
        'address'      => $address['oab_street_address1'] . ' '
                        . $address['oab_street_address2'] . ' '
                        . $address['oab_city'] . ' '
                        . $address['oab_state'] . ' '
                        . $address['oab_postcode'],
        'zip'          => $address['oab_postcode'],
        'country_code' => $country['country_code'],
        'weight'       => $shipOrder['so_weight'] * 1000,
        'declare'      => 0,
        'declare_eur'  => 0,
        'hs_code'      => '',
        'rc_number'    => (('PNLG' === $smCode) ? $shipOrder['tracking_number'] : ''),
      );

      foreach ($products as $item) {
        $product   = Service_Product::getByField((int) $item['product_id'], 'product_id');
        $allowedOrder[$order['order_code']]['declare'] += $item['op_quantity'] * $item['op_purpose_declared_value'];
        $allowedOrder[$order['order_code']]['declare_eur'] += $item['op_quantity'] * Service_Currency::converByCode('USD', 'EUR', $item['op_purpose_declared_value']);

        if ('' === $allowedOrder[$order['order_code']]['hs_code']) {
          $allowedOrder[$order['order_code']]['hs_code'] = $product['hs_code'];
        }

        if (!isset($allowedProduct[(int) $product['product_id']])) {
          $allowedProduct[(int) $product['product_id']] = array(
            'title'    => $product['product_title'], // 产品名称
            'title_en' => $item['product_title_en'], // 产品英文名
            'hs_code'  => $product['hs_code'], // 海关编码
            'origin'   => 'China', // 原产国，默认中国
            'weight'   => $product['product_weight'], // 产品重量
            'declare'  => $item['op_quantity'] * Service_Currency::converByCode('USD', 'EUR', $item['op_purpose_declared_value']), // 目的海关申报价值，需转换为欧元
            'total'    => $item['op_quantity'], // 产品数量
          );
        }
        else {
          $allowedProduct[(int) $product['product_id']]['declare'] += $item['op_quantity'] * Service_Currency::converByCode('USD', 'EUR', $item['op_purpose_declared_value']);
          $allowedProduct[(int) $product['product_id']]['total']   += $item['op_quantity'];
        }
      }

      //$allowedOrder[$order['order_code']]['declare_eur'] = Service_Currency::converByCode('USD', 'EUR', $allowedOrder[$order['order_code']]['declare']);

      // 订单发货时间
      if (!empty($operation) && strtotime($operation['ship_time']) > $shipTime) {
        $shipTime = strtotime($operation['ship_time']);
      }
    }

    if ($totalOrder < 1) {
      exit('No Data ...');
    }

    foreach ($allowedOrder as $v) {
      if (!isset($allowedHsCode[$v['hs_code']])) {
        $allowedHsCode[$v['hs_code']] = array(
          'hs_code' => $v['hs_code'],
          'declare' => $v['declare_eur'],
          'weight'  => $v['weight'],
          'total'   => 1,
        );
      }
      else {
        $allowedHsCode[$v['hs_code']]['declare'] += $v['declare_eur'];
        $allowedHsCode[$v['hs_code']]['weight']  += $v['weight'];
        $allowedHsCode[$v['hs_code']]['total']   += 1;
      }
    }

    require_once APPLICATION_PATH . '/../libs/PHPExcel.php';
    require_once APPLICATION_PATH . '/../libs/PHPExcel/IOFactory.php';

    // Summary Manifest Worksheet
    $total     = count($allowedProduct);
    $startRow  = 23; // Excel模板中开始写入数据的行号
    $objLoad   = PHPExcel_IOFactory::load($this->templatePath);
    $objWriter = PHPExcel_IOFactory::createWriter($objLoad, 'Excel2007');
    $objPe     = $objWriter->getPHPExcel();
    $objSheet  = $objPe->setActiveSheetIndex(0);

    // 根据数据量，对需要填写的行进行增减
    if ($total < 3) {
      $objSheet->removeRow($startRow, 3 - $total);
    }
    elseif ($total > 3) {
      $objSheet->insertNewRowBefore($startRow + 1, $total - 3);
    }

    // 重新保存输出首行标题有问题，在这里修正
    $objRichText = new PHPExcel_RichText();
    $objRichText->createText('Summary Manifest - ');
    $objLabel = $objRichText->createTextRun('ALFA GROUP Limited');
    $objLabel->getFont()
             ->setBold(TRUE)
             ->setSize(18)
             ->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED));
    $objSheet->getStyle('A1:H1')->getFont()->setName('Calibri');
    $objSheet->getStyle('A1:H1')->getFont()->setSize('18');
    $objSheet->setCellValue('A1', $objRichText);

    $objSheet->setCellValue('E16', date('Y/n/j', $shipTime)); // 设置日期
    $objSheet->setCellValue('B20', $totalOrder); // 设置订单数量
    $allDeclare = 0;
    $allItems   = 0;

    // 往Excel模板文件写内容
    foreach ($allowedProduct as $key => $item) {
      $allDeclare += $item['declare'];
      $allItems   += $item['total'];
      $objSheet->setCellValue('A' . $startRow, $item['title_en']); // 产品英文名称
      $objSheet->setCellValueExplicit('B' . $startRow, $item['hs_code'], PHPExcel_Cell_DataType::TYPE_STRING); // 海关编码
      $objSheet->setCellValue('C' . $startRow, $item['origin']); // 原产国
      $objSheet->setCellValue('D' . $startRow, $item['declare']); // 目的海关申报价值
      $objSheet->setCellValue('E' . $startRow, $item['weight'] * $item['total']); // 产品重量
      $objSheet->setCellValue('F' . $startRow, $item['total']); // 产品数量
      ++$startRow;
    }

    $objSheet->setCellValue('D' . $startRow, $allDeclare);
    $objSheet->setCellValue('E' . $startRow, $allWeight);
    $objSheet->setCellValue('F' . $startRow, $totalOrder);

    // Manifest Worksheet
    $objSheet  = $objPe->setActiveSheetIndex(1);
    $startRow  = 16;

    // 重新保存输出首行标题有问题，在这里修正
    $objRichText = new PHPExcel_RichText();
    $objRichText->createText('Manifest - ');
    $objLabel = $objRichText->createTextRun('ALFA GROUP Limited');
    $objLabel->getFont()
             ->setBold(TRUE)
             ->setSize(18)
             ->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED));
    $objSheet->getStyle('A1:H1')->getFont()->setName('Calibri');
    $objSheet->getStyle('A1:H1')->getFont()->setSize('18');
    $objSheet->setCellValue('A1', $objRichText);
    $objSheet->getRowDimension(2)->setRowHeight(13.5);
    $objSheet->getRowDimension(3)->setRowHeight(13.5);

    $objSheet->setCellValue('L7', date('Y/n/j', $shipTime)); // 设置日期
    $objSheet->setCellValue('F13', $totalOrder); // 设置订单数量
    $objSheet->setCellValue('I13', $allWeight); // 设置订单总重量
    $objSheet->setCellValue('K13', $allDeclare); // 设置目的海关总申报价值

    if (count($allowedHsCode) < 2) {
      $objSheet->removeRow($startRow, 1);
    }
    elseif (count($allowedHsCode) > 2) {
      $objSheet->insertNewRowBefore($startRow + 1, count($allowedHsCode) - 2);
    }

    foreach ($allowedHsCode as $item) {
      $objSheet->setCellValueExplicit('E' . $startRow, $item['hs_code'], PHPExcel_Cell_DataType::TYPE_STRING); // 海关编码
      $objSheet->setCellValue('F' . $startRow, $item['declare']); // 目的海关申报总价
      $objSheet->setCellValue('G' . $startRow, $item['total']); // 订单数量
      $objSheet->setCellValue('H' . $startRow, $item['weight']); // 订单重量
      ++$startRow;
    }

    ++$startRow;
    $objSheet->setCellValue('F' . $startRow, $allDeclare);
    $objSheet->setCellValue('G' . $startRow, $totalOrder);
    $objSheet->setCellValue('H' . $startRow, $allWeight * 1000);

    $startRow += 3;

    if (count($allowedOrder) < 2) {
      $objSheet->removeRow($startRow, 1);
    }
    elseif (count($allowedOrder) > 2) {
      $objSheet->insertNewRowBefore($startRow + 1, count($allowedOrder) - 2);
    }

    foreach ($allowedOrder as $item) {
      $objSheet->setCellValue('A' . $startRow, $item['ship_time']); // 出货日期
      $objSheet->setCellValueExplicit('D' . $startRow, $item['rc_number'], PHPExcel_Cell_DataType::TYPE_STRING); // 跟踪号
      $objSheet->setCellValue('E' . $startRow, $item['receiver']); // 收件人姓名
      $objSheet->setCellValue('F' . $startRow, $item['address']); // 收件人地址
      $objSheet->setCellValueExplicit('G' . $startRow, $item['zip'], PHPExcel_Cell_DataType::TYPE_STRING); // 收件人邮编
      $objSheet->setCellValue('H' . $startRow, $item['country_code']); // 收件人国家二字码
      $objSheet->setCellValue('I' . $startRow, $item['weight']); // 订单重量
      $objSheet->setCellValue('J' . $startRow, $item['declare']); // 申报价值USD
      $objSheet->setCellValue('K' . $startRow, $item['declare_eur']); // 申报价值EUR
      $objSheet->setCellValueExplicit('L' . $startRow, $item['hs_code'], PHPExcel_Cell_DataType::TYPE_STRING); // 海关编码
      ++$startRow;
    }

    header('pragma: public');
    header('expires: 0');
    header('accept-ranges: bytes');
    header('cache-control: must-revalidate');
    header('content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8');
    header('content-disposition: attachment; filename=' . $this->exportFilename);
    $objWriter->save('php://output');
  }

  private function printAll() {
    $params      = $this->_request->getParams();
    $smCode      = trim($this->getRequest()->getParam('code', ''));
    $allowedCode = array('PNLN', 'PNLG'); // 允许的运输方式
    $condition   = $this->serviceClass->getMatchFields($params);
    $allowedData = array();

    if (!in_array($smCode, $allowedCode, TRUE)) {
      exit('No Data ...');
    }

    if ('orderCode' == $params['searchType']) {
      $condition['order_code'] = $params['searchCode'];
    }
    else {
      $condition['reference_no'] = $params['searchCode'];
    }

    $conditionTemp['dateFor'] = isset($params['dateFor']) ? $params['dateFor'] : '';
    $conditionTemp['dateTo']  = isset($params['dateTo']) ? $params['dateTo'] : '';
    $searchDateType = isset($params['searchDateType']) ? $params['searchDateType'] : '';
    $isSearchDate = FALSE;

    if (!empty($conditionTemp['dateFor'])) {
      $isSearchDate = TRUE;
      $conditionTemp['dateFor']=date('Y-m-d H:i:s',strtotime($conditionTemp['dateFor']));
    }

    if (!empty($conditionTemp['dateTo'])) {
      $isSearchDate = TRUE;
      $conditionTemp['dateTo'] = date('Y-m-d H:i:s', strtotime($conditionTemp['dateTo']));
    }

    if ($isSearchDate) {
      switch ($searchDateType) {
        case 'createDate':
          $condition['dateFor'] = $conditionTemp['dateFor'];
          $condition['dateTo']  = $conditionTemp['dateTo'];
          break;
        case 'printTime':
          $condition['printDateFor'] = $conditionTemp['dateFor'];
          $condition['printDateTo']  = $conditionTemp['dateTo'];
          break;
        case 'packTime':
          $condition['packDateFor'] = $conditionTemp['dateFor'];
          $condition['packDateTo']  = $conditionTemp['dateTo'];
          break;
        case 'shipTime':
          $condition['shipDateFor'] = $conditionTemp['dateFor'];
          $condition['shipDateTo']  = $conditionTemp['dateTo'];
          break;
        default:
          break;
      }
    }

    $condition['product_barcode']  = isset($params['product_barcode']) ? $params['product_barcode'] : '';
    $condition['product_category'] = isset($params['product_category']) ? $params['product_category'] : '';
    $condition['oab_type']         = '0';
    $condition['tracking_number']  = $params['tracking_number'];
    $condition['oab_country_id']   = $params['oab_country_id'];
    $condition['sm_code']          = $smCode;

    if ($condition['tracking_number']) {
      $shipOrder = Service_ShipOrder::getByField($condition['tracking_number'], 'tracking_number');

      if (!empty($shipOrder)) {
        $condition['order_id'] = $shipOrder['order_id'];
      }
      else {
        $condition['order_id'] = 'none';
      }
    }

    $selectedOrder = $this->serviceClass->getOrderInnerJoinProductByCondition($condition);

    foreach ($selectedOrder as $order) {
      if (empty($order) || $smCode !== $order['sm_code']) {
        continue ;
      }

      $allowedData[] = $order['order_id'];
    }

    if (empty($allowedData)) {
      exit('No Data ...');
    }

    $this->printSelected($allowedData);
  }
}