<?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_BaoguanUpload
{
    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.");
            }
        }
        $PHPReader->setReadDataOnly(true);
        $this->phpExcel = $PHPReader->load($this->filePath);
    }

    public function getBaseInfo()
    {
        $headerTitle = [
            '境内发货人' => ['C4', 'trade_co'],
            '境内发货人名称' => ['A5', 'trade_name'],
            '出境关别' => ['F5', 'declare_port'],
            '出境关别' => ['F5', 'ie_port'],
            '境外收货人' => ['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'],

            '合同协议号' => ['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'],
        ];
        $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+).*$/", $headInfo['ie_port'], $match)) {
            $headInfo['ie_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;
    }

    /**
     * @todo 校验表头表体
     **/
    public function validateInfo($headInfo,$bodyInfo,$receiving_code){
        $error = [];
        try {
            if(!empty($receiving_code)){
                $receiving = Service_Receiving::getByField($receiving_code,'receiving_code');
                if(empty($receiving)){
                    $error[] = "入库单信息不存在";
                }else{
                    if($receiving['receiving_status']!='2'){
                        $error[] = "入库单状态不允许上传报关单";
                    }else{
                        if($receiving['receiving_code']!=$headInfo['bpd_ref_no']){
                            $error[] = "上传的报关单和系统不一致";
                        }else{
                            //
                            if(!($headInfo['trade_co']!=''&&strlen($headInfo['trade_co'])==10)){
                                $error[] = "境内发货人代码必填并且是十位";
                            }
                            if($headInfo['trade_name']==''){
                                $error[] = "境内发货人名称必填";
                            }
                            if($headInfo['overseas_consignor_ename']==''){
                                $error[] = "境外收货人必填";
                            }
                            if(!($headInfo['owner_code']!=''&&strlen($headInfo['owner_code'])==10)){
                                $error[] = "生产销售单位代码必填并且是十位";
                            }
                            if($headInfo['owner_name']==''){
                                $error[] = "生产销售单位名称必填";
                            }
                            if($headInfo['owner_name']==''){
                                $error[] = "生产销售单位名称必填";
                            }
                            if($headInfo['traf_mode']==''){
                                $error[] = "运输方式必填";
                            }
                            if(!($headInfo['pack_no']>0 && preg_match("/^[1-9][0-9]*$/" ,$headInfo['pack_no']))){
                                $error[] = "总件数必填并且是正整数";
                            }
                            if(!($headInfo['gross_wt']>0 && is_numeric($headInfo['gross_wt']))){
                                $error[] = "总毛重必填并且是正数";
                            }
                            if(!($headInfo['net_wt']>0 && is_numeric($headInfo['net_wt']))){
                                $error[] = "总净重必填并且是正数";
                            }
                            if(!($headInfo['gross_wt'] > $headInfo['net_wt'])){
                                $error[] = "总毛重必须大于总净总";
                            }
                            if($headInfo['ie_port']!=$receiving['ie_port']){
                                $error[] = "上传的报关单进出境关别{$headInfo['ie_port']}和入库单进出口岸{$receiving['ie_port']}不一致";
                            }
                            /*if($headInfo['trans_mode']!=$receiving['trans_mode']){
                                $error[] = "上传的报关单进出境关别{$headInfo['ie_port']}和入库单进出口关别{$receiving['ie_port']}不一致";
                            }*/

                            //获取入库单详情
                            $asnObj = new Service_AsnProccess();
                            $return = $asnObj->downTemplateBaoguan($receiving['receiving_code'], $receiving['customer_id'],'1');
                            $asnRow = $return['data']['asnRow'];
                            $ASNDetail = $return['data']['ASNDetail'];

                            if(!empty($bodyInfo)){
                                $totalweight = 0;
                                $totalprice = 0;
                                foreach ($bodyInfo as $k=>$body){
                                    $totalweight += $body['total_weight'];
                                    $totalprice += $body['declared_value'];
                                }

                                $bodyResult = $this->validateBodyDetail($bodyInfo,$ASNDetail);
                                if($bodyInfo['state']==0){
                                    $bodyError = $bodyResult['error'];
                                    $error = array_merge($bodyError,$error);
                                }

                            }else{
                                $error[] = "商品项不能为空";
                            }
                        }
                    }
                }
            }else{
                $error[] = "入库单号为空";
            }
        }catch (Exception $e){
            $error[] = $e->getMessage();
        }
        return $error;
    }

    /**
     * @todo 校验入库单详情和上传的是否一致
     */
    public function validateBodyDetail($bodyInfo,$ASNDetail){
        $result = array(
            'state'=>0,
            'message'=>'',
            'data'=>[],
            'error'=>[],
        );
        $error = [];
        try {
            //校验excel上传的数据
            $excelBodys = []; //按料号合并excel上传的数量
            foreach ($bodyInfo as $k=>$b){
                $tipkey = ($k+1);
                if(isset($excelBodys[$b['goods_id']])){
                    $excelBodys[$b['goods_id']]['gqty'] = $excelBodys[$b['goods_id']]['gqty']+$b['gqty'];
                }else{
                    $excelBodys[$b['goods_id']] = $b;
                }
                if($b['goods_id']==''){
                    $error[] = "第{$tipkey}个商品料号为空";
                }
                if($b['code_ts']==''){
                    $error[] = "第{$tipkey}个商品编码为空";
                }
                if($b['gname']==''){
                    $error[] = "第{$tipkey}个商品品名为空";
                }
                if($b['gmodel']==''){
                    $error[] = "第{$tipkey}个商品申报要素不能为空";
                }
                if(!($b['gqty']>0)){
                    $error[] = "第{$tipkey}个商品申报数量必须是大于0的数字";
                }
                if($b['gunit']==''){
                    $error[] = "第{$tipkey}申报单位不能为空";
                }else{
                    $puRow = Service_ProductUom::getByField($b['gunit'],'pu_code');
                    if(empty($puRow)){
                        $error[] = "第{$tipkey}申报单位{$b['pu_code']}无效";
                    }
                }
                if($b['first_unit']==''){
                    $error[] = "第{$tipkey}法定单位不能为空";
                }else{
                    //
                    $puCodeLaw = Service_ProductUom::getByField($b['first_unit'],'pu_code');
                    if(empty($puCodeLaw)){
                        $error[] = "第{$tipkey}法定单位{$b['pu_code']}无效";
                    }
                }
                if($b['second_unit']!=''){
                    $secondCodeLaw = Service_ProductUom::getByField($b['second_unit'],'pu_code');
                    if(empty($secondCodeLaw)){
                        $error[] = "第($k+1)第二单位{$b['pu_code']}无效";
                    }
                }

                if(!($b['decl_price']>0)){
                    $error[] = "第{$tipkey}申报单价必须是大于0的数字";
                }
                if(!($b['decl_total']>0)){
                    $error[] = "第{$tipkey}申报总价必须是大于0的数字";
                }
                $countDeclPrice = sprintf("%.2f",$b['decl_price']*$b['gqty']);
                $decl_total = sprintf("%.2f",$b['decl_total']);
                if($countDeclPrice!=$decl_total){
                    $error[] = "第{$tipkey}申报总价{$b['decl_total']}不等于申报单价乘以申报数量{$countDeclPrice}";
                }
                if($b['trade_curr']==''){
                    $error[] = "第{$tipkey}币值不能为空";
                }
                if($b['origin_country']==''){
                    $error[] = "第{$tipkey}原产国不能为空";
                }
                if($b['destination_country']==''){
                    $error[] = "第{$tipkey}目的国不能为空";
                }
                if($b['district_code']==''){
                    $error[] = "第{$tipkey}境内货源地不能为空";
                }
                if($b['duty_mode']==''){
                    $error[] = "第{$tipkey}征免不能为空";
                }
            }
            //按料号合并入库单的数量
            $receiveBodys = [];
            foreach($ASNDetail as $k=>$rd){
                if(isset($receiveBodys[$rd['goods_id']])){
                    $receiveBodys[$rd['goods_id']]['quantity'] = $receiveBodys[$rd['goods_id']]['quantity']+$rd['quantity'];
                }else{
                    $receiveBodys[$rd['goods_id']] = $rd;
                }
            }
            foreach ($excelBodys as $k=>$eb){
                if($eb['gqty']!=$receiveBodys[$eb['goods_id']]['quantity']){
                    $error[] = "表格导入的料号".$eb['goods_id']."数量为".$eb['gqty']."和入库单汇总的料号数量:".$receiveBodys[$eb['goods_id']]['quantity']."不一致";
                }
            }
            //通过入库单汇总的料号在反查一遍
            foreach ($receiveBodys as $k=>$rb){
                if($rb['quantity']!=$excelBodys[$rb['goods_id']]['gqty']){
                    $error[] = "入库单的料号".$rb['goods_id']."数量为".$rb['quantity']."和表格导入的料号数量:".$excelBodys[$rb['goods_id']]['gqty']."不一致";
                }
            }
            //校验其他数据
            /*if(){

            }*/


            if(empty($error)){
                $result['state'] = 1;
            }else{
                $result['error'] = $error;
            }
        }catch (Exception $e){
            $result['error'] = array($e->getMessage());
        }
        return $result;
    }

}