<?php
/* @todo:根据给出的列名MD5，动态生成模板
 * 注意，这里仅是根据模板列名的变化 才会导致模板重新生成
 * @author:gilbert
 * @date:2017-05-18
 */
class Common_ExcelTemplate {

    protected $_template;//模板
    protected $_type;//类型
    protected $_templatePath;//模板路径
    protected $_excel;//PHPExcel对象
    protected $_excelWriter;//PHPExcel对象
    protected $_excelType = 'xls';
    protected $_templateFile;//模板文件（全路径+文件名）

    public function __construct( $init = array() ) {
        if( is_array( $init ) && !empty( $init ) ) {
            foreach( $init as $key => $value ) {
                switch( $key ) {
                    case 'template':
                        $this->setTemplate( $value );
                        break;
                    case 'type':
                        $this->setType( $value );
                        break;
                    case 'templatePath':
                        $this->setTemplatePath( $value );
                        break;
                    case 'excelType':
                        $this->setExcelType( $value );
                        break;
                }
            }
        }
        //设置默认的path
        if( empty( $this->_templateDir ) ) {
            $path = dirname( APPLICATION_PATH ) . DIRECTORY_SEPARATOR .implode( DIRECTORY_SEPARATOR, array( 'data','file','excel_template' ) );
            $this->setTemplatePath( $path );
        }
    }
    /* @todo:设置模板
     *
     */
    public function setTemplate( $template ) {
        $this->_template = $template;
    }
    /* @todo:设置模板路径
     *
     */
    public function setTemplatePath( $path ) {
        $this->_templatePath = $path;
    }
    /* @todo:设置excel类型,也就是xls、xlsx、csv
     *
     */
    public function setExcelType( $type ) {
        $this->_excelType = $type;
    }
    /* @todo:获取模板名字，包含路径。如果模板不存在或标题列有改变都会自动重新生成一个模板
     *
     */
    public function getTemplate() {
        $name = '';
        switch( $this->_template ) {
            case 'order':
                $name = $this->getOrderTemplate();
                break;
        }
        if( $name === '' ) {
            return '';
        }
        //检查文件是否存在
        $file = $this->_templatePath . DIRECTORY_SEPARATOR . $name;

        if( !file_exists( $file ) ) {
            try{
                $this->createTemplate( $file );
            } catch( Exception $e ) {

            }
        }
        return $file;
    }
    /* @todo:获取订单模板名字,根据名字来决定是否重新生成excel内容
     *
     */
    public function getOrderTemplate() {
        $columns = $this->getOrderColumns();
        $string = md5( print_r( $columns, true ) );
        return ( $this->_type == 2 ? 'ordersameline_' : 'orderdiffline_') . $string . '.' . $this->_excelType;
    }
    /* @todo:设置相关类型
     *
     */
    public function setType( $type ) {
        $this->_type = $type;
    }
    /* @todo:获取订单模板的字段
     * @param int $type 类型
     */
    public function getOrderColumns() {
       return $this->_type == 1 ? $this->productInDiffRow() : $this->productInSameRow();
    }
    /* @todo；创建模板
     *
     */
    public function createTemplate( $file ) {
        if( empty( $this->_template ) )
            throw new Exception('请先设置模板');

        $this->setTemplateFile( $file );
        $this->createTemplateDir();
        //设置phpexcel
        $this->setPHPExcel();

        switch( $this->_template ) {
            case 'order':
                $this->createOrderTemplate();
                break;
        }
    }
    /* @todo:设置模板文件
     *
     */
    protected function setTemplateFile( $file ) {
        $this->_templateFile = $file;
    }
    /* @todo:创建模板目录
     *
     */
    protected function createTemplateDir() {
        $directory = dirname( $this->_templateFile );
        if( false === file_exists( $directory ) ) {
            if( false === mkdir( $directory, 755, true ) )
                throw new Exception('创建模板失败');
        }
    }
    /* @todo:创建PHPExcel对象
     *
     */
    protected function setPHPExcel() {
        $lib = '';
        if( $this->_excelType == 'xls') {
            $lib = 'Excel5';
        } else if( $this->_excelType == 'xlsx') {
            $lib = 'Excel2007';
        } else {
            throw new Exception('设置的Excel类型不正确');
        }

        include_once("PHPExcel.php");
        include_once("PHPExcel/IOFactory.php");

        $this->_excel = new PHPExcel();
        $this->_excelWriter = PHPExcel_IOFactory::createWriter( $this->_excel, $lib );
    }
    /* @todo:根据数字索引获取列名 A,B,C,D..........
     *
     */
    protected function getColumnName( $index ) {
        $charAt = 65;
        $quotient = intval( $index / 26);
        $remainder = $index % 26;
        $f = $quotient>0 ? chr( $charAt + $quotient - 1 ) : '';
        $s = $remainder>=0 ? chr( $charAt + $remainder ) : '';
        return $f . $s;
    }
    /* @todo:创建订单模板
     *
     */
    public function createOrderTemplate( ) {

        $this->_excel->createSheet();
        $sheet = $this->_excel->getSheet(1);
        $sheet->setTitle('Sheet2');

        $i = 0;
        $data = $this->getOrderFilterData();
        $dataLen = array();
        foreach( $data as $key => $value ) {
            $column = $this->getColumnName($i);
            $dataLen[$column] = count( $value );
            $sheet->getColumnDimension( $column )->setWidth(15);
            $j = 1;
            foreach( $value as $k => $v ) {
                $columnName = $column.$j;
                $sheet->setCellValue( $columnName, $v );
                $sheet->getStyle( $columnName )->getAlignment()->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER );
                $j++;
            }
            ++$i;
        }

        $sheet = $this->_excel->getSheet(0);
        $sheet->setTitle('Sheet1');


        $title = $this->getOrderColumns();
        $filterDataIndex = $this->getOrderFilterDataIndex();
        $comment = $this->getOrderComment();
        $requiredColumns = $this->getOrderRequiredColumn();
        $textColumns = $this->getOrderTextColumn();

        $i = 0;
        foreach ( $title as $key => $value){
            $column = $this->getColumnName($i);
            $columnName = $column.'1';
            $sheet->setCellValue( $columnName, $value );

            $style = array(
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                ),
               'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    ),
                ),
            );
            if( in_array( $key, $requiredColumns ) ) {
                $style['font'] = array(
                    'bold' => true,
                    'color' => array ( 'argb' => PHPExcel_Style_Color::COLOR_RED ),
                );
            }
            $sheet->getStyle( $columnName )->applyFromArray( $style );

            if( in_array( $key, $textColumns ) ) {
                $sheet->getStyle( $column )->getNumberFormat() ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
            }
            //$sheet->getColumnDimension( $column )->setAutoSize(true);
            $sheet->getColumnDimension( $column )->setWidth(15);
            //设置下拉菜单
            if( isset( $filterDataIndex[ $key ] ) && isset( $dataLen[ $filterDataIndex[ $key ] ] ) ) {
                $filterColumn = $filterDataIndex[ $key ];
                $valid = $sheet->getCell( $column.'2' )->getDataValidation();
                $valid->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
                $valid->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
                //$valid->setAllowBlank(false);
                $valid->setShowInputMessage(true);
                //$valid->setShowErrorMessage(true);
                $valid->setShowDropDown(true);
                //$valid->setErrorTitle('输入的值有误');
                //$valid->setError('您输入的值不在下拉框列表内.');
                //$valid->setPromptTitle('设备类型');
                $valid->setFormula1( 'Sheet2!$'.$filterColumn.'$1:$'.$filterColumn .'$'.$dataLen[$filterColumn] );//试了很多，PHPExcel不能在Excell2005里生成下拉列表，只有Excel2007才有效 (下面的代码在07里也是有效的)
                //->setFormula1( 'INDIRECT("Sheet2!$'.$filterColumn.'$1:$'.$filterColumn .'$'.$dataLen[$filterColumn].'")' );

            }
            //设置批注
            if( isset( $comment[ $key ] ) ) {
                $sheet->getComment( $columnName )->getText()->createTextRun( $comment[ $key ] );
            }
            ++$i;
        }

        $this->_excel->setActiveSheetIndex(0);

        $this->_excelWriter->save( $this->_templateFile );
    }
    /* @todo；产品在不同行
     * 自由配置用于生成模板标题
     */
    protected function productInDiffRow() {
        return array(
            'mode'                      => '订单模式',
            'selfChannel'              => '是否自有渠道',
            'serviceCode'              => '服务商单号',
            'warehouse'                => '仓库',
            'type'                      => '订单类型',
            'consigneeCountry'        => '收件人国家',
            'shipMethod'               => '运输方式',
            'transCode'                => '交易订单号',
            'consigneeName'           => '收件人姓名',
            'consigneeCompany'        => '收件人公司名',
            'consigneeState'          => '收件人州\区域',
            'consigneeCity'           => '收件人城市',
            'consigneePostcode'      => '收件人邮编',
            'consigneeAddress1'      => '收件人地址1',
            'consigneeAddress2'      => '收件人地址2',
            'consigneeTel'            => '收件人电话',
            'consigneeEmail'          => '收件人电子邮件',
            'currency'                 => '成交币种',
            'totalDealPrice'          => '成交总价',
            'HKDispatchAddress'      => '香港派送地址',
            'FBA'                      => 'FBA',
            'destinationDuty'        => '目的地关税',
            'POD'                      => 'POD',
            'remark'                  => '备注',
            'buyInsurance'           => '购买保险',
            'insuranceName'          => '保险名称',
            'insuranceRate'          => '投保金额率',
            'notifyTel'               => '通知方电话',
            'notifyContact'          => '通知方联系人',
            'notifyAddr'             => '通知方地址',
            'insuredAccount'         => '投保金额',
            'isDGD'                   => '是否危险货品',
            'destination'            => '目的港',
            'hsDescription'          => '货物描述及唛头',
            'dispatch_notice'          => '派送通知服务',
            'guarantee'                => '保障服务',
            'sku'                      => 'sku',
            'skuEnName'               => '英文品名',
            'DeclareUnitPrice'       => '申报单价（USD）',
            'TargetDeclareUnitPrice' => '目的海关申报单价(USD)',
            'qty'                      => '数量',
        );

    }
    /* @todo:产品在同一行
     * 自由配置用于生成模板标题
     */
    protected function productInSameRow() {
        return array(
            'mode'                      => '订单模式',
            'selfChannel'              => '是否自有渠道',
            'serviceCode'              => '服务商单号',
            'warehouse'                => '仓库',
            'type'                      => '订单类型',
            'consigneeCountry'        => '收件人国家',
            'shipMethod'               => '运输方式',
            'transCode'                => '交易订单号',
            'consigneeName'           => '收件人姓名',
            'consigneeCompany'        => '收件人公司名',
            'consigneeState'          => '收件人州\区域',
            'consigneeCity'           => '收件人城市',
            'consigneePostcode'      => '收件人邮编',
            'consigneeAddress1'      => '收件人地址1',
            'consigneeAddress2'      => '收件人地址2',
            'consigneeTel'            => '收件人电话',
            'consigneeEmail'          => '收件人电子邮件',
            'currency'                 => '成交币种',
            'totalDealPrice'          => '成交总价',
            'HKDispatchAddress'      => '香港派送地址',
            'FBA'                      => 'FBA',
            'destinationDuty'        => '目的地关税',
            'POD'                      => 'POD',
            'remark'                  => '备注',
            'buyInsurance'           => '购买保险',
            'insuranceName'          => '保险名称',
            'insuranceRate'          => '投保金额率',
            'notifyTel'               => '通知方电话',
            'notifyContact'          => '通知方联系人',
            'notifyAddr'             => '通知方地址',
            'insuredAccount'         => '投保金额',
            'isDGD'                   => '是否危险货品',
            'destination'            => '目的港',
            'hsDescription'          => '货物描述及唛头',
            'dispatch_notice'          => '派送通知服务',
            'guarantee'                => '保障服务',
            'sku'                      => 'sku1',
            'skuEnName'               => '英文品名',
            'DeclareUnitPrice'       => '申报单价（USD）',
            'TargetDeclareUnitPrice' => '目的海关申报单价(USD)',
            'qty'                      => '数量1',
            'sku2'                     => 'sku2',
            'skuEnName2'              => '英文品名',
            'DeclareUnitPrice2'      => '申报单价（USD）',
            'TargetDeclareUnitPrice2' => '目的海关申报单价(USD)',
            'qty2'                     => '数量2',
        );
    }
    /* @todo:获取备注
     *
     */
    public function getOrderComment() {
        return array(
            'mode'                      => '必填项',
            'selfChannel'              => '集货模式下，是否自有渠道为必填项，备货模式无须填写',
            'serviceCode'              => '当自有渠道中选择是时，服务商单号为必填，最大长度为32个字符',
            'warehouse'                => '必填项,备货模式即发货仓库',
            'type'                      => '订单类型',
            'consigneeCountry'        => '必填项,收件人国家',
            'shipMethod'               => '必填项,当集货模式下，是否换单选择否时，运输方式必须是Other',
            'transCode'                => '选填项,最大长度为32位,',
            'consigneeName'           => '必填项,最大长度为64个字符',
            'consigneeCompany'        => '选填项，如果为空，则取收件人姓名的数据作为收件人公司名',
            'consigneeState'          => '国家为美国的时候必填，最大长度为60个字符，运输方式为CJ-DGMP\CJ-DGMG\SZY-EUB\HKPG时也是必填',
            'consigneeCity'           => '只有DHL、CJ-DGMP\CJ-DGMG\SZY-EUB\HKPG运输方式的才必填，最大长度为60个字符',
            'consigneePostcode'      => '只有DHL、CJ-DGMP\CJ-DGMG\SZY-EUB\HKPG运输方式的才必填，最大长度为60个字符',
            'consigneeAddress1'      => '必填项，最大长度为255个字符,如果运输方式为HKPG的，长度限定最大为60个字符',
            'consigneeAddress2'      => '选填项,最大长度为255个字符,如果运输方式为HKPG的，长度限定最大为60个字符',
            'consigneeTel'            => '必填项，最大长度为16个字符',
            'consigneeEmail'          => '选填项，最大长度为60个字符',
            'currency'                 => '如果不选，系统默认是RMB',
            'totalDealPrice'          => '必填项，必须是大于0的数值，最大长度为10个字符，小数位默认为2位',
            'HKDispatchAddress'      => '选择自有渠道时必填',
            'FBA'                      => 'FBA',
            'destinationDuty'        => '目的地关税',
            'POD'                      => 'POD',
            'remark'                  => '备注',
            'buyInsurance'           => '购买保险',
            'insuranceName'          => '保险名称',
            'insuranceRate'          => '投保金额率',
            'notifyTel'               => '当运输方式是DGF-CARGO时选填，其他运输方式请勿填写',
            'notifyContact'          => '当运输方式是DGF-CARGO时选填，其他运输方式请勿填写',
            'notifyAddr'             => '当运输方式是DGF-CARGO时选填，其他运输方式请勿填写',
            'insuredAccount'         => '当运输方式是DGF-CARGO时选填，其他运输方式请勿填写',
            'isDGD'                   => '当运输方式是DGF-CARGO时选填，其他运输方式请勿填写',
            'destination'            => '当运输方式是DGF-CARGO时为必填项',
            'hsDescription'          => '当运输方式是DGF-CARGO时为必填项',
            'sku'                      => '必填项，必须是系统中已备案状态的产品，长度最大不超过12位',
            'skuEnName'               => '运输方式为HKDHL时必填',
            'DeclareUnitPrice'       => '必须是大于0的正数，如果为空，系统默认为产品备案时填写的申报价值，此列不填写时不能删除，必须保留，否则上传时产品的取值会发生错误',
            'TargetDeclareUnitPrice' => '必须是大于0的正数，如果为空，系统默认为申报价值*0.7，此列不填写时不能删除，必须保留，否则上传时产品的取值会发生错误',
            'qty'                      => '必填项，必须是正整数',
            'sku2'                      => '如订单还有其他SKU，则在后面继续添加，非必填项',
            'dispatch_notice'          => '运输方式为BH-DHL-HKDELIVERY时必填,填写值为"是"或者"否"',
            'guarantee'                => '运输方式为BH-DHL-HKDELIVERY时必填,填写值为"是"或者"否"'
        );
    }
    /* @todo:获取订单下拉内容
     *
     */
    public function getOrderFilterData() {
        $warehouseRows = Service_Warehouse::getByCondition( array('warehouse_id'=>1), array( 'warehouse_code' ) );
        $countryRows = Service_Country::getByCondition( array(), array( 'country_code', 'country_name' ), 0, 0, 'country_code'  );
        $shipMethodRows = Service_ShippingMethod::getByCondition( array('warehouse_id_arr'=> array(0,1),'sm_status'=>1), array( 'sm_code', 'sm_name_cn' ), 0, 0, array('sm_code') );
        $currencyRows = Service_Currency::getByCondition( array(), array('currency_code') );
        $airportCodeRows = Service_AirportCountryMap::getByCondition( array(), array( 'airport_destination', 'airport_name_ch' ) );

        $warehouse = $country = $shipMethod = $currency = $airport = array();

        $warehouseLen = count( $warehouseRows );
        $countryLen = count( $countryRows );
        $shipMethodLen = count( $shipMethodRows );
        $currencyLen = count( $currencyRows );
        $airportLen = count( $airportCodeRows );

        $len = max( $warehouseLen, $countryLen, $shipMethodLen, $currencyLen, $airportLen );

        for( $i = 0; $i < $len; $i++ ) {
            if( $i < $warehouseLen ) {
                $warehouse[] = $warehouseRows[$i]['warehouse_code'];
            }
            if( $i < $countryLen ) {
                $country[] = $countryRows[$i]['country_code'].' '.$countryRows[$i]['country_name'];
            }
            if( $i < $shipMethodLen ) {
                $shipMethod[] = $shipMethodRows[$i]['sm_code'].' '.$shipMethodRows[$i]['sm_name_cn'];
            }
            if( $i < $currencyLen ) {
                $currency[] = $currencyRows[$i]['currency_code'];
            }
            if( $i < $airportLen ) {
                $airport[] = $airportCodeRows[$i]['airport_destination'].' '.$airportCodeRows[$i]['airport_name_ch'];
            }

        }

        $orderMode = array('备货模式','集货模式');
        $yesOrNo = array('是','否');
        $payType = array( '到付', '预付' );
        $insurance = array( '货物运输保险', '订单延误保险', '订单取消保险', '产品责任保险');
        $orderType = array('普通');
        return array(
            'A' => $orderMode,
            'B' => $yesOrNo,
            'C' => $warehouse,
            'D' => $country,
            'E' => $shipMethod,
            'F' => $currency,
            'G' => $payType,
            'H' => $insurance,
            'I' => $orderType,
            'J' => $airport,
        );
    }
    /* @todo；获取订单下拉数据的索引
     * 在这里配置excel下拉菜单的绑定关系
     */
    public function getOrderFilterDataIndex() {
        //索引就是上面方法里列的索引，键值是模板里sheet2里的内容
        return array(
            'mode'                          => 'A',
            'selfChannel'                  => 'B',
            'warehouse'                    => 'C',
            'type'                          => 'I',
            'consigneeCountry'            => 'D',
            'shipMethod'                   => 'E',
            'currency'                     => 'F',
            'FBA'                           => 'B',
            'destinationDuty'             => 'G',
            'POD'                           => 'B',
            'buyInsurance'                => 'B',
            'insuranceName'               => 'H',
            'isDGD'                        => 'B',
            'destination'                 => 'J'
        );
    }

    /* @todo:获取必填列
     *
     */
    public function getOrderRequiredColumn() {
        return array(
            'mode',
            'warehouse',
            'consigneeCountry',
            'shipMethod',
            'consigneeName',
            'consigneeAddress1',
            'consigneeTel',
            'totalDealPrice',
            'sku',
            'qty',
            'type',
            'currency'
        );
    }
    /* @todo:获取需要设置成字符串的列
     *
     */
    public function getOrderTextColumn() {
        return array(
            'serviceCode',
            'transCode',
            'consigneePostcode',
            'consigneeEmail',
            'consigneeTel',
            'sku',
            'sku2'
        );
    }

}