<?php
class Order_ExportAlfaHfManifestController extends Ec_Controller_Action {
    public function preDispatch() {
        set_time_limit(0);
        @ini_set('memory_limit','1024M');
        $this->exportFilename = 'Alfa-Manifest-' . date('YmdHis') . '.xlsx';
        header("Content-Type:APPLICATION/OCTET-STREAM;charset=utf-8");
        $this->templatePath   = APPLICATION_PATH . '/../data/file/ALFA-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);
        $smCodes      = $this->getRequest()->getParam('E7', '');
        $smCode = '';
        if( !empty( $smCodes ) ) {
            if( is_array( $smCodes ) ) {
                $smCode = $smCodes[0];//原来的程序就是一次只能导出一种运输方式的
            } else {
                $smCode = $smCodes;
            }
        }
        $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'] : ''),
                'city'         => $address['oab_city'],
                'country_name_en' => $country['country_name_en'],
        'tracking_number' => $shipOrder['tracking_number'],
        'product_title_en' => $products[0]['product_title_en'],
        'delivery'    => date('dmy', strtotime($operation['ship_time'])),
            );

            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'];
                }
            }

            // 订单发货时间
            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  = 24; // 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('E18', date('Y/n/j', $shipTime)); // 设置日期
        $objSheet->setCellValue('B21', $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('M7', date('Y/n/j', $shipTime)); // 设置日期
        $objSheet->setCellValue('F13', $totalOrder); // 设置订单数量
        $objSheet->setCellValue('J13', $allWeight); // 设置订单总重量
        $objSheet->setCellValue('L13', $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);
        }
        $objSheet->setCellValue('I' . ($startRow-1), 'Country'); // 国家
        $objSheet->setCellValue('J' . ($startRow-1), 'Ct'); // 
        $objSheet->setCellValue('K' . ($startRow-1), 'Wgt(g)');
        $objSheet->setCellValue('L' . ($startRow-1), 'Value(USD)');
        $objSheet->setCellValue('M' . ($startRow-1), 'Value(EUR)');
        $objSheet->setCellValue('N' . ($startRow-1), 'Tariff');
        $objSheet->setCellValue('D' . ($startRow-1), 'Customer Reference Number');
        $objSheet->setCellValue('O' . ($startRow-1), 'Goods Description');
        $objSheet->setCellValue('P' . ($startRow-1), 'Qty');
        $objSheet->setCellValue('Q' . ($startRow-1), 'Item barcode');
        $objSheet->setCellValue('R' . ($startRow-1), 'Bag number');
        $objSheet->setCellValue('S' . ($startRow-1), 'Return');
        $objSheet->setCellValue('T' . ($startRow-1), 'PostNL mailbag ID');
        foreach ($allowedOrder as $item) {
            $objSheet->setCellValue('A' . $startRow, $item['ship_time']); // 出货日期
            $objSheet->getStyle('A' . $startRow, $item['ship_time'])->getFont()->setSize('10');
            if ($smCode=='PNLG') {
                $objSheet->setCellValue('B' . $startRow, 'R'.$item['delivery']); // 
            }else{
                $objSheet->setCellValue('B' . $startRow, 'N'.$item['delivery']); // 
            }
            $objSheet->setCellValueExplicit('D' . $startRow, $item['rc_number'], PHPExcel_Cell_DataType::TYPE_STRING); // 跟踪号
            $objSheet->getStyle('D' . $startRow, $item['rc_number'])->getFont()->setSize('10');

            $objSheet->setCellValue('E' . $startRow, $item['receiver']); // 收件人姓名
            $objSheet->getStyle('E' . $startRow, $item['receiver'])->getFont()->setSize('10');

            $objSheet->setCellValue('F' . $startRow, $item['address']); // 收件人地址
            $objSheet->getStyle('F' . $startRow, $item['address'])->getFont()->setSize('10');

            $objSheet->setCellValueExplicit('G' . $startRow, $item['zip'], PHPExcel_Cell_DataType::TYPE_STRING); // 收件人邮编
            $objSheet->getStyle('G' . $startRow, $item['zip'])->getFont()->setSize('10');

            $objSheet->setCellValue('H' . $startRow, $item['city']); // 收件人城市
            $objSheet->getStyle('H' . $startRow, $item['city'])->getFont()->setSize('10');
            $objSheet->setCellValue('I' . $startRow, $item['country_name_en']); // 收件人城市
            $objSheet->getStyle('I' . $startRow, $item['country_name_en'])->getFont()->setSize('10');
            $objSheet->setCellValue('J' . $startRow, $item['country_code']); // 收件人国家二字码
            $objSheet->getStyle('J' . $startRow, $item['country_code'])->getFont()->setSize('10');

            $objSheet->setCellValue('K' . $startRow, $item['weight']); // 订单重量
            $objSheet->getStyle('K' . $startRow, $item['weight'])->getFont()->setSize('10');

            $objSheet->setCellValue('L' . $startRow, $item['declare']); // 申报价值USD
            $objSheet->getStyle('L' . $startRow, $item['declare'])->getFont()->setSize('10');

            $objSheet->setCellValueExplicit('M' . $startRow, $item['declare_eur'],PHPExcel_Cell_DataType::TYPE_STRING); // 申报价值EUR
            $objSheet->getStyle('M' . $startRow, $item['declare_eur'])->getFont()->setSize('10');

            $objSheet->setCellValueExplicit('N' . $startRow, $item['hs_code'], PHPExcel_Cell_DataType::TYPE_STRING); // 海关编码
            $objSheet->getStyle('N' . $startRow, $item['hs_code'])->getFont()->setSize('10');
            $objSheet->setCellValueExplicit('O' . $startRow, $item['product_title_en'], PHPExcel_Cell_DataType::TYPE_STRING); // 海关编码
            $objSheet->getStyle('O' . $startRow, $item['product_title_en'])->getFont()->setSize('10');
            $objSheet->setCellValueExplicit('P' . $startRow, '1', PHPExcel_Cell_DataType::TYPE_STRING); // 海关编码
            $objSheet->getStyle('P' . $startRow, '1')->getFont()->setSize('10');
            $objSheet->setCellValueExplicit('Q' . $startRow, $item['tracking_number'], PHPExcel_Cell_DataType::TYPE_STRING); // 海关编码
            $objSheet->getStyle('Q' . $startRow, $item['tracking_number'])->getFont()->setSize('10');
            ++$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();
        $smCodes      = $this->getRequest()->getParam('E7', '');
        $smCode = '';
        if( !empty( $smCodes ) ) {
            if( is_array( $smCodes ) ) {
                $smCode = $smCodes[0];//原来的程序就是一次只能导出一种运输方式的
            } else {
                $smCode = $smCodes;
            }
        }
        $allowedCode = array('PNLN', 'PNLG'); // 允许的运输方式
        $condition   = $this->serviceClass->getNewMatchFields($params);
        $allowedData = array();
        if (!in_array($smCode, $allowedCode, TRUE)) {
            exit('No Data ...');
        }
        if( is_array( $condition['oab_country_id'] ) && count( $condition['oab_country_id'] ) == 1 && $condition['oab_country_id'][0] === '' ) {
            unset($condition['oab_country_id']);
        }
        if ('orderCode' == $params['searchType']) {
            $condition['order_code'] = $params['searchCode'];
        }
        else {
            $condition['reference_no'] = $params['searchCode'];
        }

        $time = Common_Common::getStartAndEndTime($params['dateFor'],$params['dateTo']);
        $conditionTemp['dateFor'] = $time[0];
        $conditionTemp['dateTo'] = $time[1];
        unset($condition['dateFor']);
        unset($condition['dateTo']);
        $searchDateType = isset($params['searchDateType']) ? $params['searchDateType'] : '';

        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['oab_type']         = '0';
        $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);
    }
}