php_script/excel_ftxia_...

<?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('2018-12-13 00:00:00')) );  
define("START_TIME", date('Y-m-d%20H:i:s', strtotime('2018-12-12 14:00:00')) );
define("DETAILDEBUG", 0);//打印流程详细信息，如有需要，设为1
define("LIMIT_NUM", 0);//限制导入条数,0不限制,方便测试,上线设为0
/**
 * 导入及更新 ftxia数据
 */
#设置时区
date_default_timezone_set('PRC');
set_time_limit(0);
ini_set('memory_limit','1024M');

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

	# 遍历订单
	# 统计
	$res_count = array(
		'total_num' => 0,
		'insert_num' => 0,
		'already_num' => 0,
		'fail_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';
	}
	$now = date("Y-m-d H:i:s");
	# 记录此次插入订单信息
	$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 "/ ************************ order{$res_count['total_num']}-Start ************************* / \n";
    		echo 'order_id:'.$orderDetail[24].'|| goods_id:'.$orderDetail[3].'|| orderstatus:'.$orderDetail[8].'|| order_balance_at:'.$orderDetail[16].'|| update_time:'.$now;
		if( DETAILDEBUG == 1){
			echo 'orderDetail:';
			var_dump($orderDetail);
		}

		$sql = "select id from ftxia_taoke_detail2 where order_sn = '$orderDetail[24]' and goods_id = '$orderDetail[3]' limit 1";
		$stmt = $_PDO->prepare($sql);
        	$stmt->execute();
        	$result = $stmt->fetch(PDO::FETCH_ASSOC);
		if(!empty($result) && !in_array($orderDetail[24],$orderList)){
			echo "\n 该订单已存在";
			$res_count['already_num']++;
		}else{
		
	#普通//0创建时间     1点击时间       2商品信息       3商品ID 4掌柜旺旺       5所属店铺       6商品数 7商品单价       8订单状态       9订单类型       10收入比率      11分成比率      12付款金额      13效果预估      14结算金额      15预估收入      16结算时间      17佣金比率      18佣金金额      19技术服务费比率        20补贴比率      21补贴金额      22补贴类型      23成交平台      24第三方服务来源        25订单编号      26类目名称      27来源媒体ID    28来源媒体名称  29广告位ID      30广告位名称


	#三方 //0创建时间     1点击时间       2商品信息       3商品ID 4掌柜旺旺       5所属店铺       6商品数 7商品单价       8订单状态       9订单类型       10收入比率      11分成比率      12付款金额      13效果预估      14结算金额      15预估收入      16结算时间      17佣金比率      18佣金金额      19补贴比率      20补贴金额      21补贴类型      22成交平台      23产品类型 24订单编号      25类目名称      26来源媒体ID    27来源媒体名称  28广告位ID      29广告位名称
	
		$orderDetail[2] = addslashes($orderDetail[2]);
		$orderDetail[4] = addslashes($orderDetail[4]);
		$orderDetail[5] = addslashes($orderDetail[5]);
		$sql = "insert into ftxia_taoke_detail2(create_time,click_time,goods_name,goods_id,wangwang,shop,goods_number,goods_price,order_status,order_type,income_ratio,divided_ratio,order_amount,effect_prediction,balance_amount,estimated_revenue,is_balanced,is_balanced2,is_balanced3,is_balanced4,balance_time,commission_ratio,commission_amount,subsidy_ratio,subsidy_amount,subsidy_type,order_platform,serve_from,order_sn,category,media_id,media_name,adv_id,adv_name,updatetime) values('$orderDetail[0]','$orderDetail[1]','$orderDetail[2]','$orderDetail[3]','$orderDetail[4]','$orderDetail[5]','$orderDetail[6]','$orderDetail[7]','$orderDetail[8]','$orderDetail[9]','$orderDetail[10]','$orderDetail[11]','$orderDetail[12]','$orderDetail[13]','$orderDetail[14]','$orderDetail[15]',0,0,0,0,'$orderDetail[16]','$orderDetail[17]','$orderDetail[18]','$orderDetail[19]','$orderDetail[20]','$orderDetail[21]','$orderDetail[22]','$orderDetail[23]','$orderDetail[24]','$orderDetail[25]','$orderDetail[26]','$orderDetail[27]','$orderDetail[28]','$orderDetail[29]','$now')";	

		$stmt = $_PDO->prepare($sql);
                $stmt->execute();
		$res = $_PDO->lastinsertid();
		if($res){
			$orderList[] = $orderDetail[24];
			echo "\n 处理成功 +1";
			$res_count['insert_num']++;
		}else{
			echo "\n 处理失败 +1";
                        $res_count['fail_num']++;
		}
		}
	echo "\n/ ** order{$res_count['total_num']}-End ** /";
                echo "\n\n\n";

    } 

print_r($res_count);
print_r($orderList);
}



# 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; cookie2=18e5dd4b80f2447f75db5ddbb9aaf2e2; _tb_token_=f558ebe16ee4d; v=0; alimamapwag=TW96aWxsYS81LjAgKFdpbmRvd3MgTlQgMTAuMDsgV2luNjQ7IHg2NCkgQXBwbGVXZWJLaXQvNTM3LjM2IChLSFRNTCwgbGlrZSBHZWNrbykgQ2hyb21lLzY5LjAuMzQ5Ny4xMDAgU2FmYXJpLzUzNy4zNg%3D%3D; cookie32=2d2dbd51714bd91fa4e604f2e1050dd6; alimamapw=FnABFCAlFCZcQ3dVHXF2FncCFnF5FCAlFCFWQ3YjHXBxFnAEFnF7FCEgFCFXQ3BRHXFxFnd3FnEB%0AFCIhFCYmQ3YkMQICVwcEUgQPAQFWBlxWBVJRWwEAAABQAgQJV1dSVQAG; cookie31=OTk1MDYxNzMsJUU0JUI4JThBJUU5JUE1JUI2JUU1JUI4JTgyJUU1JTkwJTg4JUU0JUI4JTgwJUU3JUE3JTkxJUU2JThBJTgwJUU2JTlDJTg5JUU5JTk5JTkwJUU1JTg1JUFDJUU1JThGJUI4LGppYW5nbmFuQGZhbnRvdXRpYW8uY29tLFRC; login=VFC%2FuZ9ayeYq2g%3D%3D; apush848645ac0bf07b717f88c18a7e296d1e=%7B%22ts%22%3A1546581072755%2C%22parentId%22%3A1546581056722%7D; l=aBqVVZ45yJCvdTzK2MaiaW9iV707KJBPnf1C1MwL8TEhNAhiErWjI1-x-OwINaNFerYdua7_505j0; isg=BAUFZrtq9aqHd9bkDJoB0pXUFEH_aq1o7VwycgdrXzxLniAQzxJTJrG4rILN3tEM';
    $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/getTbkThirdPaymentDetails.json?DownloadID=DOWNLOAD_REPORT_TK3_PUB&queryType=2&payStatus=&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.csv", "w+");
    	$a = fputs($file, $response);
    	fclose($file);
		return './order_excel.csv';
	}
	else{
		return false;
	}
		
}


readDataFromDb();