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();