<?php
require_once "PHPExcel.php";
require_once "PHPExcel/Reader/Excel2007.php";
require_once "PHPExcel/Reader/Excel5.php";

class Process_InvUpload
{
    protected $phpExcel;
    protected $filePath;
    protected $bodyTitle = [
        '序号' => 'gds_seqno',
        '备案序号' => 'putrec_seqno',
        '商品料号' => 'gds_mtno',
        '商品编码(必填)' => 'gdecd',
        '商品名称' => 'gds_nm',
        '规格型号' => 'gds_spcf_model_desc',
        '申报单位' => 'dcl_unitcd',
        '法定单位' => 'lawf_unitcd',
        '法定第二单位' => 'secd_lawf_unitcd',
        '国别' => 'natcd',
        '申报单价' => 'dcl_uprc_amt',
        '申报总价' => 'dcl_total_amt',
        '申报币制' => 'dcl_currcd',
        '申报数量' => 'dcl_qty',
        '法定数量' => 'lawf_qty',
        '第二法定数量' => 'secd_lawf_qty',
        '重量比例因子' => 'wt_sf_val',
        '第一比例因子' => 'fst_sf_val',
        '第二比例因子' => 'secd_sf_val',
        '毛重' => 'gross_wt',
        '净重' => 'net_wt',
        '用途' => 'use_cd',
        '征免方式' => 'lvyrlf_modecd',
        '单耗版本号' => 'ucns_verno',
        '报关单商品序号' => 'entry_gds_seqno',
        '申请表序号' => 'apl_seqno',
        '备注' => 'rmk',
        '最终目的国' => 'destination_natcd',
    ];

    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 getHeadInfo() {
        $sheet = $this->phpExcel->getSheet(0);
        $dclTypecd = $sheet->getCell("B6")->getValue();//申报类型
        $bwlType = $sheet->getCell("D6")->getValue();//底账类型
        $warehouseAccountNo = $sheet->getCell("F6")->getValue();//底账海关编号（必填）
        $invtType = $sheet->getCell("B7")->getValue();//清单类型
        $bizopEtpsNo = $sheet->getCell("B8")->getValue();//经营企业编号（必填）
        $bizopEtpsNm = $sheet->getCell("D8")->getValue();//经营企业名称（必填）
        $bizopEtpsSccd = $sheet->getCell("F8")->getValue();//经营企业信用代码
        $mtpckEndprdMarkcd = $sheet->getCell("F9")->getValue();//料件成品标记
        $supvModecd = $sheet->getCell("B9")->getValue();//监管方式（必填）
        $trspModecd = $sheet->getCell("D9")->getValue();//运输方式
        $impexpMarkcd = $sheet->getCell("B10")->getValue();//进出口标记
        $dclcusFlag = $sheet->getCell("D10")->getValue();//报关标记
        $impexpPortcd = $sheet->getCell("B11")->getValue();//进出境关别（必填）
        $stshipTrsarvNatcd = $sheet->getCell("D11")->getValue();//起运/运抵国(地区）（必填）
        $genDecFlage = $sheet->getCell("D13")->getValue();//是否生成报关单
        $rmk = $sheet->getCell('B12')->getValue();//备注
        $rvsngDetpsSccd = $sheet->getCell('D21')->getValue();//收发货企业社会信用代码
        $rcvgdEtpsno = $sheet->getCell('B21')->getValue();//收发货企业海关代码
        $rcvgdEtpsNm = $sheet->getCell('F21')->getValue();//收发货企业中文名称
        $rltInvtNo = $sheet->getCell('D15')->getValue();//关联清单号
        $rltPutrecNo = $sheet->getCell('F15')->getValue();//关联账册备案编号
        $rltEntryNo = $sheet->getCell('B15')->getValue();//关联报关单号
        $dclcusTypecd = $sheet->getCell('F10')->getValue();//报关类型
        $entryNo = $sheet->getCell('F11')->getValue();//对应报关单号
        $decType = $sheet->getCell("F12")->getValue();//报关单类型
        $corr_entry_dcl_etps_no = $sheet->getCell("B20")->getValue();//对应报关单申报单位代码
        $corr_entry_dcl_etps_sccd = $sheet->getCell("D20")->getValue();//对应报关单申报单位社会统一信用代码
        $corr_entry_dcl_etps_nm = $sheet->getCell("F20")->getValue();//对应报关单申报单位名称

        return [
            'dcl_typecd' => $dclTypecd,
            'bwl_type' => $bwlType,
            'bwl_no' => $warehouseAccountNo,
            'invt_type' => $invtType,
            'bizop_etps_no' => $bizopEtpsNo,
            'bizop_etps_sccd' => $bizopEtpsSccd,
            'bizop_etps_nm' => $bizopEtpsNm,
            'supv_modecd' => $supvModecd,
            'trsp_modecd' => $trspModecd,
            'impexp_markcd' => $impexpMarkcd,
            'dclcus_flag' => $dclcusFlag,
            'impexp_portcd' => $impexpPortcd,
            'mtpck_endprd_markcd' => $mtpckEndprdMarkcd,
            'stship_trsarv_natcd' => $stshipTrsarvNatcd,
            'gen_dec_flag'=>empty($genDecFlage)?'2':$genDecFlage,
            'remark'=>$rmk,
            'rvsngd_etps_sccd'=>$rvsngDetpsSccd,
            'rcvgd_etps_no'=>$rcvgdEtpsno,
            'rcvgd_etps_nm'=>$rcvgdEtpsNm,
            'rlt_invt_no'=>$rltInvtNo,
            'rlt_putrec_no'=>$rltPutrecNo,
            'rlt_entry_no'=>$rltEntryNo,
            'dclcus_typecd'=>$dclcusTypecd,
            'dec_type'=>$decType,
            'entry_no'=>$entryNo,
            'corr_entry_dcl_etps_no'=>$corr_entry_dcl_etps_no,
            'corr_entry_dcl_etps_nm'=>$corr_entry_dcl_etps_nm,
            'corr_entry_dcl_etps_sccd'=>$corr_entry_dcl_etps_sccd,
        ];
    }

    /**
     * Note:把表头转成数据库字段信息
     * User: Yao
     * Date: 2020/8/7
     * Time: 17:55
     * @param $headInfo
     */
    public function convertToDbInfo($headInfo) {
        if (preg_match("/^(\d+?).*$/", $headInfo['dcl_typecd'], $match)) {
            $headInfo['dcl_typecd'] = $match[1];
        }
        if (preg_match("/^(\d+?).*$/", $headInfo['bwl_type'], $match)) {
            $headInfo['bwl_type'] = $match[1];
        }
        if (preg_match("/^(\d+?).*$/", $headInfo['invt_type'], $match)) {
            $headInfo['invt_type'] = $match[1];
        }
        if (preg_match("/^(\d+).*$/", $headInfo['supv_modecd'], $match)) {
            $headInfo['supv_modecd'] = $match[1];
        }
        if (preg_match("/^([A-Z]+?).*$/", $headInfo['impexp_markcd'], $match)) {
            $headInfo['impexp_markcd'] = $match[1];
        }
        if (preg_match("/^(\d+?).*$/", $headInfo['dclcus_flag'], $match)) {
            $headInfo['dclcus_flag'] = $match[1];
        }
        if (preg_match("/^([A-Z]+?).*$/", $headInfo['mtpck_endprd_markcd'], $match)) {
            $headInfo['mtpck_endprd_markcd'] = $match[1];
        }
        if (preg_match("/^(\d+).*$/", $headInfo['stship_trsarv_natcd'], $match)) {
            $headInfo['stship_trsarv_natcd'] = $match[1];
        }
        if (preg_match("/^(\d+).*$/", $headInfo['trsp_modecd'], $match)) {
            $headInfo['trsp_modecd'] = $match[1];
        }
        if (preg_match("/^(\d+).*$/", $headInfo['impexp_portcd'], $match)) {
            $headInfo['impexp_portcd'] = $match[1];
        }
        if (preg_match("/^(\d+).*$/", $headInfo['dclcus_typecd'], $match)) {
            $headInfo['dclcus_typecd'] = $match[1];
        }
        if (preg_match("/^([A-Z]+|\d+).*$/", $headInfo['dec_type'], $match)) {
            $headInfo['dec_type'] = $match[1];
        }else{
            $declType = Service_InvtList::getDecType();
            if(in_array($headInfo['dec_type'],$declType)){
                $headInfo['dec_type'] = array_search($headInfo['dec_type'],$declType);
            }
        }
        return $headInfo;
    }

    /**
     * Note:获取表体信息
     * User: Yao
     * Date: 2020/8/7
     * Time: 17:59
     * @return array
     */
    public function getBodyInfo() {
        $currentSheet = $this->phpExcel->getSheet(1);
        $maxColumn = $currentSheet->getHighestColumn();
        /**取得一共有多少行*/
        $rowCount = $currentSheet->getHighestRow();
        $maxColumn = PHPExcel_Cell::columnIndexFromString($maxColumn);
        $sheetTitle = array();
        for ($column = 0; $column < $maxColumn; $column++) {
            $value = $currentSheet->getCellByColumnAndRow($column, 1)->getValue();
            if (is_object($value)) {
                $value = $value->__toString();
            }
            $sheetTitle[$column] = trim($value);
        }
        foreach ($sheetTitle as $col => $value) {
            foreach ($this->bodyTitle as $t => $index) {
                if ($value == $t) {
                    $readCol[$col] = $index;
                }
            }
        }

        $result = array();
        if (empty($readCol)) {
            return $result;
        }
        $key = 0;
        for ($row = 2; $row <= $rowCount; $row++) {
            $totalLen = 0; //记录行总长度
            foreach ($readCol as $column => $index) {
                $value = $currentSheet->getCellByColumnAndRow($column, $row)->getValue();
                if (is_object($value)) {
                    $value = $value->__toString();
                }
                if($index == 'dcl_unitcd'){
                    if(preg_match("/^(\d+)*-.*$/",$value,$match)){
                        $value = $match[1];
                    }
                }else if($index == 'lawf_unitcd'){
                    if(preg_match("/^(\d+)*-.*$/",$value,$match)){
                        $value = $match[1];
                    }
                }else if($index == 'secd_lawf_unitcd'){
                    if(preg_match("/^(\d+)*-.*$/",$value,$match)){
                        $value = $match[1];
                    }
                }else if($index == 'natcd'){
                    if(preg_match("/^(\d+)*-.*$/",$value,$match)){
                        $value = $match[1];
                    }
                }else if($index == 'destination_natcd'){
                    if(preg_match("/^(\d+)*-.*$/",$value,$match)){
                        $value = $match[1];
                    }
                }else if($index == 'lvyrlf_modecd'){
                    if(preg_match("/^(\d+)*-.*$/",$value,$match)){
                        $value = $match[1];
                    }
                }
                $result[$key][$index] = trim($value);
                $totalLen += strlen(trim($value));
            }
            if ($totalLen == 0) {
                unset($result[$key]);
            } else {
                $result[$key]['metiral_type'] = '料件';
            }
            $key++;
        }
        return $result;
    }
}