php_script/special/excel_brandg...

<?php

require_once 'DB_PDO.class.php';
require_once 'TBK.class.php';
require_once 'conf.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-11-04 00:00:00')) );  
//define("START_TIME", date('Y-m-d%20H:i:s', strtotime('2018-11-03 14:00:00')) );
define("DETAILDEBUG", 1);//打印流程详细信息，如有需要，设为1
define("LIMIT_NUM", 0);//限制导入条数,0不限制,方便测试,上线设为0
//define("PUSH_URL",'http://yhq.quyaqu.com/api/v2/MessagePush/rebatepush');//push接口地址,测试
//define("PUSH_URL",'https://tbk.726p.com/api/v2/MessagePush/rebatepush');//push接口地址
define("KEYURL","https://item.taobao.com/item.htm?id=");
/**
 * 导入及更新 order/order_rebate数据
 */
#设置时区
date_default_timezone_set('PRC');

function readDataFromDb(){
	# 1.下载excel
	$res = './gua.xlsx';	

	# 统计
	$res_count = array(
		'total_num' => 0,
		'insert_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';
	}
	//循环读取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";
		if( DETAILDEBUG == 1){
			echo 'orderDetail:';
		//	var_dump($orderDetail);
		}
		$goods_id = $orderDetail[6];
		$cate_id = $orderDetail[1];
		$cate_id2 = $orderDetail[2];

		$keyword = KEYURL.$goods_id;
        	$result = TBK::search( $keyword );
		$title = !empty($result->short_title)? $result->short_title : $orderDetail[7];
		$goods_url = $orderDetail[8];
		$nick = $orderDetail[17];
		$sellerId = $result->seller_id;
		$img = !empty($result->white_image)? serialize($result->white_image) : serialize($orderDetail[9]);
		$small_img = serialize($result->small_images->string);
		$price = $result->zk_final_price;
		if(isset($result->coupon_id) && !empty($result->coupon_id)){
                	preg_match_all('/[\d]+/',$result->coupon_info,$d);
			$coupon_price = isset($d[0][2]) ? $d[0][2] : $d[0][1];
			$discount_price = ($price*100 - $coupon_price*100)/100;
			$discount_price = Number_format($discount_price,2, '.','');
	    	} else {
			$coupon_price = "0";
			$discount_price = $price;
	    	}
		$click_url = $result->url;
		$volume = $result->volume;
		$commission = $result->commission_rate/100;
		$shop_type = $result->user_type;
		$shop_title = $result->shop_title;
		$coupon_url = $result->coupon_share_url;
		$coupon_surplus = $result->coupon_remain_count;
		$coupon_total = $result->coupon_total_count;
		$coupon_explain = $result->coupon_info;
		$coupon_receive = $coupon_total-$coupon_surplus;
		$start_time = $result->coupon_start_time;	
		$end_time = $result->coupon_end_time;
		$is_coupon = 1;
		
		$sql = "select * from goods where goods_id = $goods_id ";
		$stmt = $_PDO->prepare($sql);
                $stmt->execute();
		$gResult = $stmt->fetch(PDO::FETCH_ASSOC);
		if(empty($gResult)){
		
		$sql = "insert into goods (goods_id,title,goods_url,nick,sellerId,img,small_img,price,discount_price,coupon_price,click_url,volume,commission,shop_type,shop_title,coupon_url,coupon_surplus,coupon_total,coupon_explain,coupon_receive,start_time,end_time,is_coupon) values ('$goods_id','$title','$goods_url','$nick','$sellerId','$img','$small_img','$price','$discount_price','$coupon_price','$click_url','$volume','$commission','$shop_type','$shop_title','$coupon_url','$coupon_surplus','$coupon_total','$coupon_explain','$coupon_receive','$start_time','$end_time','$is_coupon')";
		$stmt = $_PDO->prepare($sql);
		$stmt->execute();
		$res = $_PDO->lastinsertid();
echo "\ngoods:".$res;
		}

		if($cate_id>3000){
		$time = time();
		$sql = "insert into group_goods (group_id,goods_id,status,type,create_time) values($cate_id,$goods_id,1,1,$time)";
		$stmt = $_PDO->prepare($sql);
                $stmt->execute();
                $res = $_PDO->lastinsertid();
echo "\ncate1:".$res;
		}

		if($cate_id2>3000){
		$time = time();
                $sql = "insert into group_goods (group_id,goods_id,status,type,create_time) values($cate_id2,$goods_id,1,1,$time)";
                $stmt = $_PDO->prepare($sql);
                $stmt->execute();
                $res = $_PDO->lastinsertid();
echo "\ncate2:".$res;
		}

		echo "/ ***** order{$res_count['total_num']}-End *****/ \n";
	}
var_dump($res_count);
	       
}




#返利表订单插入运营商津贴信息
function YysInsertRebate($user_level,$order_balance_at,$order_create_at,$goods_img,$goods_name,$order_id,$userId,$rebaseType,$last_orderSelfRebaseRebase,$sum_price,$orderStatus,$goods_id,$pre_income){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( !empty($order_balance_at) ){
        $sql="insert into order_rebate_profit (user_level,order_balance_at,order_create_at,img,name,order_id,user_id,type,rebate,money,status,goods_id,pre_income) VALUES (:user_level,:order_balance_at,:order_create_at,:goods_img,:goods_name,:order_id,:userId,:rebaseType,:last_orderSelfRebaseRebase,:sum_price,:orderStatus,:goods_id,:pre_income)";
        $stmt = $_PDO->prepare($sql);  
		$stmt->execute(array(':user_level'=>$user_level,':order_balance_at'=>$order_balance_at,':order_create_at'=>$order_create_at,':goods_img'=>$goods_img,':goods_name'=>$goods_name,':order_id'=>$order_id,':userId'=>$userId,':rebaseType'=>$rebaseType,':last_orderSelfRebaseRebase'=>$last_orderSelfRebaseRebase,':sum_price'=>$sum_price,':orderStatus'=>$orderStatus,':goods_id'=>$goods_id,':pre_income'=>$pre_income));
		return $_PDO->lastinsertid();
    }
    else{
        $sql="insert into order_rebate_profit (user_level,order_create_at,img,name,order_id,user_id,type,rebate,money,status,goods_id,pre_income) VALUES (:user_level,:order_create_at,:goods_img,:goods_name,:order_id,:userId,:rebaseType,:last_orderSelfRebaseRebase,:sum_price,:orderStatus,:goods_id,:pre_income)";
        $stmt = $_PDO->prepare($sql);  
		$stmt->execute(array(':user_level'=>$user_level,':order_create_at'=>$order_create_at,':goods_img'=>$goods_img,':goods_name'=>$goods_name,':order_id'=>$order_id,':userId'=>$userId,':rebaseType'=>$rebaseType,':last_orderSelfRebaseRebase'=>$last_orderSelfRebaseRebase,':sum_price'=>$sum_price,':orderStatus'=>$orderStatus,':goods_id'=>$goods_id,':pre_income'=>$pre_income));
		return $_PDO->lastinsertid();
    }
}

#order订单表返利表同步更新
function generateSqlupdate($status,$order_balance_at,$order_id,$goods_id){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( !empty($order_balance_at) ){
        $sql="update `order` set status=:status,order_balance_at=:order_balance_at where order_id=:order_id and goods_id=:goods_id ";
        $stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_balance_at'=>$order_balance_at,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();

	//更新order_rebate
	$sql="update order_rebate set status=:status,order_balance_at=:order_balance_at where order_id=:order_id and goods_id=:goods_id ";
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_balance_at'=>$order_balance_at,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();

        //更新order_rebate_profit
        $sql="update order_rebate_profit set status=:status,order_balance_at=:order_balance_at where order_id=:order_id and goods_id=:goods_id ";
        $stmt = $_PDO->prepare($sql);  
        $stmt->execute(array(':status'=>$status,':order_balance_at'=>$order_balance_at,':order_id'=>$order_id,':goods_id'=>$goods_id));
        $res = $stmt->rowCount();
    }
    else{
        $sql="update `order` set status=:status where order_id=:order_id and goods_id=:goods_id ";
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();

       	$sql="update order_rebate set status=:status where order_id=:order_id and goods_id=:goods_id ";
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();

        //更新order_rebate_profit
        $sql="update order_rebate_profit set status=:status where order_id=:order_id and goods_id=:goods_id ";
        $stmt = $_PDO->prepare($sql);  
        $stmt->execute(array(':status'=>$status,':order_id'=>$order_id,':goods_id'=>$goods_id));
        $res = $stmt->rowCount();
    }  
    return $res;
}

#订单表单独更新
function generateSqlUpdateOther($status,$order_balance_at,$order_id,$goods_id){
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    if( !empty($order_balance_at) ){
        $sql="update `order` set status=:status,order_balance_at=:order_balance_at where order_id=:order_id and goods_id=:goods_id ";
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_balance_at'=>$order_balance_at,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();
    }
    else{
        $sql="update `order` set status=:status where order_id=:order_id and goods_id=:goods_id ";
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute(array(':status'=>$status,':order_id'=>$order_id,':goods_id'=>$goods_id));
	$res = $stmt->rowCount();
    }
    return $res;
}


readDataFromDb();