php_script/onlive/excel_vq.php

<?php
require_once 'DB_PDO.class.php';
require_once 'conf.class.php';
require_once '/root/php_script/PHPExcel-1.8/Classes/PHPExcel.php';
require_once '/root/php_script/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';
require_once '/root/php_script/PHPExcel-1.8/Classes/PHPExcel/Reader/Excel5.php';

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

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,'\\') );  

        $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[1];
	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";
	
		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; 250470096_yxjh-filter-1=true; 299550010_yxjh-filter-1=true; cookie2=1b3c8c127c250da4d7147c8d98b87161; v=0; _tb_token_=ee810369b3a4b; alimamapwag=TW96aWxsYS81LjAgKFdpbmRvd3MgTlQgMTAuMDsgV2luNjQ7IHg2NCkgQXBwbGVXZWJLaXQvNTM3LjM2IChLSFRNTCwgbGlrZSBHZWNrbykgQ2hyb21lLzY5LjAuMzQ5Ny4xMDAgU2FmYXJpLzUzNy4zNg%3D%3D; cookie32=4a03ef32fe3708949595117d92de750b; alimamapw=FnF%2BFCZSFCAnQ3dUCgIGCjoCBlcMBwJUBlUGUwNYClFRA1ABBAALU1JUAANQV1AEWg%3D%3D; cookie31=Mjk5NTUwMDEwLCVFOSU4NSVCNyVFNyU4MiVBQjIwMTksa3V4dWFubGl1aGFvQDE2My5jb20sVEI%3D; login=U%2BGCWk%2F75gdr5Q%3D%3D; l=bBEoP2SHvN8ixrI8BOfNmuI-hd_TBCRfcsPzw4ww3ICP_yW9Q0rhWZ_Al6tpC3GVa6BDJ3Wc5ILQBPTSWyUIh; isg=BJWV2rh0ZWSC9kYU_GrxAuWEpJGPOlO1_WyCIhc-qIwzbr1g3uZfdH1sODL97mFc';
    $nowTime = END_TIME;
    $pastTime = START_TIME;

    //$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;
    $url = "https://pub.alimama.com/report/getNewTbkRefundPaymentDetails.json?spm=a219t.7664554.1998457203.50.1ef735d99JauPX&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();