php_script/excel_orderv...

<?php

require_once 'DB_PDO.class.php';
require_once 'TBK.class.php';
require_once 'confv2.class.php';
require_once 'PHPExcel-1.8/Classes/PHPExcel.php';
require_once 'PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';
require_once 'PHPExcel-1.8/Classes/PHPExcel/Reader/Excel5.php';

# 定义下载订单时间段
define("END_TIME", date('Y-m-d%20H:i:s', strtotime('2019-01-15 00:00:00')) );  
define("START_TIME", date('Y-m-d%20H:i:s', strtotime('2018-12-01 00:00:00')) );
define("DETAILDEBUG", 0);//打印流程详细信息，如有需要，设为1
//define("LIMIT_NUM", 0);//限制导入条数,0不限制,方便测试,上线设为0
/**
 * 导入及更新 order_rebate 维权订单数据
 */
#设置时区
date_default_timezone_set('PRC');
set_time_limit(0);
ini_set('memory_limit','1024M');
error_reporting(1);

function readDataFromDb(){
    # 1.下载excel
    $res = down_excel();    
    if( !$res ){
        echo '下载excel文件失败';
        exit;
    }

    # 遍历订单
    # 统计
    $res_count = array(
        'total_num' => 0,
        'generalupdate_num' => 0,
        'fail_update_num'  => 0,
    );
    #初始化pdo
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    # 读取excel
    $templateName = $res;
    //实例化Excel读取类
    $objReader = new PHPExcel_Reader_Excel2007();
    if(!$objReader->canRead($templateName)){
        $objReader = new PHPExcel_Reader_Excel5();
        if(!$objReader->canRead($templateName)){
            echo "\n".'无法识别的Excel文件！';
            return false;
        }
    }
    $objPHPExcel=$objReader->load($templateName);
    $sheet=$objPHPExcel->getSheet(0);//获取第一个工作表
    $highestRow=$sheet->getHighestRow();//取得总行数
    $highestColumn=$sheet->getHighestColumn(); //取得总列数
    if( strlen($highestColumn) == 2 ){
        $highestColumn = 'Z';
    }
    $orderList = array();
    //循环读取excel文件,读取一条,插入一条
    for($j=2;$j<=$highestRow;$j++){//从第二行开始读取数据
        $str='';
        for($k='A';$k<=$highestColumn;$k++){            //从A列读取数据
            //这种方法简单，但有不妥，以'\\'合并为数组，再分割\\为字段值插入到数据库,实测在excel中，如果某单元格的值包含了\\导入的数据会为空        
            $str.=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'\\';//读取单元格
            if( $k=='AE' ){ 
                break;
            }
        }
        //explode:函数把字符串分割为数组。
        $orderDetail = explode('\\',trim($str,'\\') );  
	if($orderDetail[6]<'2018-12-01') continue;  //只处理12月之后结算的

        $res_count['total_num']++;
        echo "\n\n/ ************************ order{$res_count['total_num']}-Start ************************* / \n";
        if( DETAILDEBUG == 1){
            echo 'orderDetail:';
            var_dump($orderDetail);
        }

	//0淘宝订单编号 1淘宝子订单编号 2商品名称 3维权退款金额 4应返还金额  5维权状态 6订单结算时间 7维权创建时间 8维权完成时间
	

        $order_id=$orderDetail[0];
	if($orderDetail[5] == '维权创建' || $orderDetail[5] == '等待处理' ){
		$maintain_power = 1;
	}elseif($orderDetail[5] == '维权失败'){
		$maintain_power = 2;
	}elseif($orderDetail[5] == '维权成功'){
                $maintain_power = 3;
        }
	$power_create_at = $orderDetail[7];
	$power_end_at = isset($orderDetail[8]) ? $orderDetail[8] : null;
        
        echo 'order_id:'.$order_id.'|| goods_name:'.$orderDetail[2].'|| maintain_power:'.$maintain_power.'|| power_create_at:'.$power_create_at;
        echo "\n";
	
	try{
             	$_PDO->beginTransaction();               	
		//更新order_rebate
		$sql="update order_rebate set maintain_power=:maintain_power,power_create_at=:power_create_at,power_end_at=:power_end_at where order_id=:order_id";
		if($maintain_power==3) $sql="update order_rebate set maintain_power=:maintain_power,power_create_at=:power_create_at,power_end_at=:power_end_at,status=0 where order_id=:order_id";
		$stmt = $_PDO->prepare($sql);
		$stmt->execute(array(':maintain_power'=>$maintain_power,':power_create_at'=>$power_create_at,':power_end_at'=>$power_end_at,':order_id'=>$order_id));
		$res = $stmt->rowCount();
		echo "更新order_rebate:".$res."\n";

		//更新order_rebate_profit
		$sql="update order_rebate_profit set maintain_power=:maintain_power,power_create_at=:power_create_at,power_end_at=:power_end_at where order_id=:order_id";
		if($maintain_power==3) $sql="update order_rebate_profit set maintain_power=:maintain_power,power_create_at=:power_create_at,power_end_at=:power_end_at,status=0 where order_id=:order_id";
		
		$stmt = $_PDO->prepare($sql);
		$stmt->execute(array(':maintain_power'=>$maintain_power,':power_create_at'=>$power_create_at,':power_end_at'=>$power_end_at,':order_id'=>$order_id));
		$res = $stmt->rowCount(); 
		echo "更新order_rebate_profit:".$res."\n";
	
		if($res){ 
			$res_count['generalupdate_num']++;
		}else{
			$res_count['fail_update_num']++;
		}
		$_PDO->commit();

	}catch(Exception $e){
		 echo 'err_msg'.$e->getMessage()."\n";
		$_PDO->rollback();
	}
        echo "/ ***order{$res_count['total_num']}-End *** / \n";
	}
echo "<pre>";
print_r($res_count);

}

# curl调用接口push
function curl_post($url, $params){
	$ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($ch, CURLOPT_POST, 1);
	curl_setopt($ch, CURLOPT_POSTFIELDS, $params);
	$response = curl_exec($ch);
	curl_close($ch);
	return $response;
}

# 下载excel订单文件
function down_excel(){
    $str_cookie = 'cna=gGnDEwQLQAACATqEyYplpj5I; account-path-guide-s1=true; 209580064_yxjh-filter-1=true; undefined_yxjh-filter-1=true; t=8c549c3cedcac7891a2c3c7f36ca91f4; l=bBEoP2SHvN8ixwV1BOfgcuIJcTbOWBOjCsPzw4TZ1ICPOWW91RJ1WZgLeCtpC3GVa6GHR3Wc5ILBBDYm8yUIg; cookie2=15a20b1fd27c84e51094f4c723192cf1; v=0; _tb_token_=117bd3eafd3e; alimamapwag=TW96aWxsYS81LjAgKFdpbmRvd3MgTlQgMTAuMDsgV2luNjQ7IHg2NCkgQXBwbGVXZWJLaXQvNTM3LjM2IChLSFRNTCwgbGlrZSBHZWNrbykgQ2hyb21lLzY5LjAuMzQ5Ny4xMDAgU2FmYXJpLzUzNy4zNg%3D%3D; cookie32=2d2dbd51714bd91fa4e604f2e1050dd6; alimamapw=FnABFCAlFCZcQ3dVHXF2FncCFnF5FCAlFCFWQ3YjHXBxFnAEFnF7FCEgFCFXQ3BRHXFxFnd3FnEB%0AFCIhFCYmQ3YkMQICVwcEUgQPAQFWBlxWBVJRWwEAAABQAgQJV1dSVQAG; cookie31=OTk1MDYxNzMsJUU0JUI4JThBJUU5JUE1JUI2JUU1JUI4JTgyJUU1JTkwJTg4JUU0JUI4JTgwJUU3JUE3JTkxJUU2JThBJTgwJUU2JTlDJTg5JUU5JTk5JTkwJUU1JTg1JUFDJUU1JThGJUI4LGppYW5nbmFuQGZhbnRvdXRpYW8uY29tLFRC; login=UtASsssmOIJ0bQ%3D%3D; JSESSIONID=018F7419C637C160FB8FAF00FFF5EA82; apush848645ac0bf07b717f88c18a7e296d1e=%7B%22ts%22%3A1547610379990%2C%22parentId%22%3A1547610320224%7D; isg=BFJSBj1kWtMR26EtN5ueyw4Foxj0y0I3Zk3l-xyrpYXwL_MpBPOPDNoJm8u2X86V';
    $nowTime = END_TIME;
    $pastTime = START_TIME;

    //$url = "https://pub.alimama.com/report/getTbkPaymentDetails.json?DownloadID=DOWNLOAD_REPORT_INCOME_NEW&queryType=1&payStatus=&startTime=".$pastTime."&endTime=".$nowTime;
    $url = "https://pub.alimama.com/report/getNewTbkRefundPaymentDetails.json?spm=a219t.7664554.1998457203.54.1ef735d9aJK7cQ&refundType=1&searchType=1&DownloadID=DOWNLOAD_EXPORT_CPSPAYMENT_REFUND_OVERVIEW&startTime=".$pastTime."&endTime=".$nowTime;;
    echo $url."\n";

    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_COOKIE, $str_cookie); //使用上面获取的cookies
    $response = curl_exec($ch);
    curl_close($ch);
    if( $response ){
        $file = fopen("./order_excel_vq.csv", "w+");
        $a = fputs($file, $response);
        fclose($file);
        return './order_excel_vq.csv';
    }
    else{
        return false;
    }
        
}

readDataFromDb();