<?php
require_once "PHPExcel1.8/PHPExcel.php";
require_once "PHPExcel1.8/PHPExcel/Reader/Excel2007.php";
require_once "PHPExcel1.8/PHPExcel/Reader/Excel5.php";
class Process_CisBaoguanDanUpload
{
    protected $phpExcel;
    protected $filePath;
    protected $headInfo;
    protected $bodyInfo;

    public function setFilePath($path)
    {
        $this->filePath = $path;
        $this->getExcelReader();
        return $this;
    }

    protected function getExcelReader()
    {
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($this->filePath)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($this->filePath)) {
                throw new Exception("Invalid File.");
            }
        }
        $this->phpExcel = $PHPReader->load($this->filePath);
    }

    public function getBaseInfo()
    {
        $headerTitle = [
            '境内发货人' => ['C4', 'trade_co'],
            '境内发货人名称' => ['A5', 'trade_name'],
            '出境关别' => ['F5', 'declare_port'],
//            '出口日期' => ['I4', 'iedate'],
//            '申报日期' => ['B4', 'overseas_consignor_code'],
//            '备案号' => ['B5', 'owner_code'],
            '境外收货人' => ['A7', 'overseas_consignor_ename'],
            '运输方式' => ['F7', 'traf_mode'],
            '运输工具名称' => ['H7', 'traf_name'],
            '航次号' => ['I7', 'voyage_no'],

            '提运单号' => ['J7', 'bill_no'],
            '生产销售单位' => ['C8', 'owner_code'],
            '生产销售单位名称' => ['A9', 'owner_name'],

            '监管方式' => ['F9', 'trade_mode'],
            '征免性质' => ['H9', 'duty_mode'],
//            '许可证号' => ['J9', 'entry_type'],

            '合同协议号' => ['A11', 'turn_no'],
            '贸易国（地区）' => ['F11', 'trade_area_code'],
            '运抵国（地区）' => ['H11', 'trade_country'],
            '指运港' => ['J11', 'destination_port'],
            '离境口岸' => ['N11', 'enty_port_code'],
            '包装种类' => ['A13', 'wrap_type'],
            '件数' => ['F13', 'pack_no'],

            '毛重（千克）' => ['G13', 'gross_wt'],
            '净重（千克）' => ['H13', 'net_wt'],
            '成交方式' => ['I13', 'trans_mode'],
            /*'运费' => ['J13', 'trade_mode'],
            '保费' => ['M13', 'trade_country'],
            '杂费'=>['O13'],*/ //这三个预录入没有字段暂时不写了。

            '入库单号' => ['B15', 'bpd_ref_no'],
//            '总金额' => ['F15', 'insur_rate'],//不需要？

        ];
        $currentSheet = $this->phpExcel->getSheet(0);
        $headData = [];
        foreach ($headerTitle as $colName => $cellInfo) {
            $value = $currentSheet->getCell($cellInfo[0])->getValue();
            if (is_object($value)) {
                $value = $value->__toString();
            }
            if ($value == NULL) {
                $value = '';
            }
            $headData[$cellInfo[1]] = $value;
        }

        $this->headInfo = $this->convertToDbInfo($headData);
        return $this->headInfo;
    }

    public function getBodyInfo(){
        $currentSheet = $this->phpExcel->getSheet(0);
        $maxRow = $currentSheet->getHighestRow();
        $bodyInfo = [];
        $bodyTitle = [
            'g_no' => 'A',
            'contr_item' => 'B',
            'goods_id' => 'C',//料号
            'code_ts' => 'D',//商品编码
            'gname' => 'E',//商品名称
            'gmodel' => 'F',//规格型号
            'gqty' => 'G',
            'gunit' => 'H',
            'first_qty' => 'I',
            'first_unit' => 'J',
            'second_qty' => 'K',
            'second_unit' => 'L',
            'decl_price' => 'M',
            'decl_total' => 'N',
            'trade_curr' => 'O',
            'origin_country' => 'P',
            'destination_country' => 'Q',
            'district_code' => 'R',
            'duty_mode' => 'S',

        ];
        for ($i = 18; $i <= $maxRow; $i++) {
            $bodyRow = [];
            foreach ($bodyTitle as $column => $cell) {
                $value = $currentSheet->getCell($cell . $i)->getValue();

                if (is_object($value)) {
                    $value = $value->__toString();
                }
                if ($value != NULL) {
                    $bodyRow[$column] = $value;
                } else {
                    if($cell == 'A'){
                        break 2;
                    }
                    $bodyRow[$column] = '';
                }
            }
            if (!empty($bodyRow)) {
                $bodyInfo[] = $bodyRow;
            }
        }
        $this->bodyInfo = $this->convertBody($bodyInfo);
        return $this->bodyInfo;
    }
    /**
     * @date 2022/7/12
     * @todo
     * @auther 46935
     * @param $headInfo
     * @return mixed
     */
    protected function convertToDbInfo($headInfo) {
        if (preg_match("/^(\d+).*$/", $headInfo['declare_port'], $match)) {
            $headInfo['declare_port'] = $match[1];
        }
        if (preg_match("/^((\d|[A-Z])+)-.*$/", $headInfo['traf_mode'], $match)) {
            $headInfo['traf_mode'] = $match[1];
        }
        if (preg_match("/^(\d+).*$/", $headInfo['trade_mode'], $match)) {
            $headInfo['trade_mode'] = $match[1];
        }
        if (preg_match("/^(\d+).*$/", $headInfo['trade_area_code'], $match)) {
            $headInfo['trade_area_code'] = $match[1];
        }

        if (preg_match("/^(\d+).*$/", $headInfo['trade_country'], $match)) {
            $headInfo['trade_country'] = $match[1];
        }

        if (preg_match("/^(\d+).*$/", $headInfo['wrap_type'], $match)) {
            $headInfo['wrap_type'] = $match[1];
        }
        if (preg_match("/^(\d+).*$/", $headInfo['trans_mode'], $match)) {
            $headInfo['trans_mode'] = $match[1];
        }
        if (preg_match("/^(\d+).*$/", $headInfo['duty_mode'], $match)) {
            $headInfo['duty_mode'] = $match[1];
        }
        if (preg_match("/^(\d+).*$/", $headInfo['enty_port_code'], $match)) {
            $headInfo['enty_port_code'] = $match[1];
        }
        if (preg_match("/^((\d|[A-Z])+).*$/", $headInfo['destination_port'], $match)) {
            $headInfo['destination_port'] = $match[1];
        }
        return $headInfo;
    }
    protected function convertBody($bodyInfo){
        $uom = Service_ProductUom::getKeyVal();
        foreach ($bodyInfo as &$body){
            if (preg_match("/^(\d+).*$/", $body['trade_curr'], $match)) {
                $body['trade_curr'] = $match[1];
            }
            if($body['gunit']){
                $puCode = array_search($body['gunit'],$uom);
                if($puCode){
                    $body['gunit'] = $puCode;
                }
            }
            if($body['first_unit']){
                $puCode = array_search($body['first_unit'],$uom);
                if($puCode){
                    $body['first_unit'] = $puCode;
                }
            }
            if($body['second_unit']){
                $puCode = array_search($body['second_unit'],$uom);
                if($puCode){
                    $body['second_unit'] = $puCode;
                }
            }
            if (preg_match("/^(\d+).*$/", $body['origin_country'], $match)) {
                $body['origin_country'] = $match[1];
            }
            if (preg_match("/^(\d+).*$/", $body['destination_country'], $match)) {
                $body['destination_country'] = $match[1];
            }
            if (preg_match("/^(\d+).*$/", $body['district_code'], $match)) {
                $body['district_code'] = $match[1];
            }
            if (preg_match("/^(\d+).*$/", $body['duty_mode'], $match)) {
                $body['duty_mode'] = $match[1];
            }
        }
        return $bodyInfo;
    }
}