<?php
/**
 * Created by PhpStorm.
 * User: boyer
 * Date: 2019/12/31
 * Time: 15:18
 */
require_once ('../config.php');
ini_set("memory_limit",'6G');
require_once("PHPExcel.php");
require_once("PHPExcel/Reader/Excel2007.php");
require_once("PHPExcel/Reader/Excel5.php");
function getUpdate($row){
    $str = '';
    foreach ($row as $key =>$v){
        $str .=$key .'= \''.$v.'\',';
    }
    return rtrim($str,',');
}
$fileName ='.xls';
$fileName = mb_convert_encoding($fileName,'gbk');
$title = array(
    'hs_code'=>'hs_code',
    'hu_id'=>'hu_id',
    '新的版本里面的'=>'new_hs_code',
    '新的uom1'=>'new_uom1',
    '新的uom1是否一致'=>'new_uom1_c',
    '新的uom2'=>'new_uom2',
    '新uom2是否一致'=>'new_uom2_c',
    '是否在作废的'=>'is_not_use',
    '新的增值税'=>'new_add_value',
    '增值税是否一样'=>'new_add_value_c',
    '新的消费税'=>'new_consumption',
    '新的消费税是否一致'=>'new_consumption_c',
    '系统是否有库存'=>'has_inventory',
);
$data = Common_Upload::readXlsFromTitle($fileName,$title);
foreach ($data as $hs){
    // echo $hs['hs_code'];
    // echo PHP_EOL;
    $sql = '';
    $update = array();
    $uomUpdate = array();
    if(!empty($hs['has_inventory'])){

        //系统不存在的，要改为不可用
        if($hs['new_hs_code'] =='#N/A'){
            $update['hs_code_status'] ='-1';
        }else{
            // if($hs['hs_code'] == '9102210010') {
            //     var_dump($hs);
            //     die();
            // }
            if($hs['new_uom1_c'] == ''){
                $uomUpdate['pu_code_law'] = $hs['new_uom1'];
            }
            if($hs['new_uom2_c'] == ''){
                if($hs['new_uom2'] == 0){
                    $uomUpdate['pu_code_second'] = '';
                }else{
                    $uomUpdate['pu_code_second'] = $hs['new_uom2'];
                }
            }
        }
        if(!empty($update)){
            $sql .= "update hs_codes set ".getUpdate($update).' where hs_code =\''.$hs['hs_code'].'\';'.PHP_EOL;

        }
        if(!empty($uomUpdate)){
            if(empty($hs['hu_id'])){
                $sql .= "insert into  hs_uom (hs_code,pu_code_law,pu_code_second,hu_add_time) values('{$hs['hs_code']}','{$uomUpdate['pu_code_law']}','{$uomUpdate['pu_code_second']}','2020-01-03 09:00:00');" . PHP_EOL;
            }else {
                $sql .= "update hs_uom set " . getUpdate($uomUpdate) . ' where hs_code =\'' . $hs['hs_code'] . '\';' . PHP_EOL;
            }
        }

    }
    if($sql) {
        file_put_contents('code.txt',$hs['hs_code'].PHP_EOL,FILE_APPEND);
        file_put_contents('updatehs_product.sql', $sql, FILE_APPEND);
    }
}