CREATE TABLE `yibai_walmart_listing_task` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `account_id` int(11) NOT NULL DEFAULT '0' COMMENT '账户ID', `account_name` varchar(50) NOT NULL DEFAULT '' COMMENT '账户名称', `site` varchar(10) NOT NULL DEFAULT '' COMMENT '站点', `report_type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1:ITEM 2:INVENTORY', `request_body` text COMMENT '请求报告参数', `response_body` text COMMENT '请求报告返回', `status` varchar(50) NOT NULL DEFAULT '' COMMENT '报告状态', `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0:未处理 1:已处理', `requestid` varchar(500) NOT NULL DEFAULT '' COMMENT '申请的requestid', `is_down` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0：未下载 1：已经下载', `down_url` varchar(1000) NOT NULL DEFAULT '' COMMENT '下载地址', `create_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间', `update_date` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间', PRIMARY KEY (`id`), KEY `idx_account_id` (`account_id`), KEY `idx_report_type` (`report_type`), KEY `idx_status` (`status`), KEY `idx_type` (`type`), KEY `idx_is_down` (`is_down`) ) ENGINE=InnoDB AUTO_INCREMENT=38826 DEFAULT CHARSET=utf8 COMMENT='拉取listing任务表';
1.创建任务
/services/walmart/walmartlisting/createreporttask?type=1 #type=1：listing任务创建 type=2：库存任务创建
public function actionCreatereporttask(){
$del = Yii::app()->request->getParam('del');
$type = Yii::app()->request->getParam('type');
$id = Yii::app()->request->getParam('id');
$model = YbModel::model('WalmartListingTask');
if($del==1){
if(!$type){
die('参数不对');
}
$model->deleteAll("report_type=".$type);
die('删除us拉取listing的申请报告');
}
if(!$type){
die('参数不对');
}
$where = '';
if($id){
$where .=' and id='.$id;
}
$sql = "select id,short_name,site from yibai_system.yibai_walmart_account where site in('us','mx','ca') and status=1 ".$where;
$list = $model->getDbConnection()->createCommand($sql)->queryAll();
if($list){
$inlist = $indata = [];
$datetime = date('Y-m-d H:i:s');
foreach ($list as $val){
$indata['account_id'] = $val['id'];
$indata['account_name'] = $val['short_name'];
$indata['site'] = $val['site'];
$indata['report_type'] = $type;
$indata['status'] = '';
$indata['type'] = 0;
$indata['is_down'] = 0;
$indata['create_date'] = $datetime;
$inlist[] = $indata;
$indata = [];
}
}
if($inlist){
$model->batchInsertAll($model->tableName(), array_keys($inlist[0]), $inlist, true);
}
echo 'DOLL';
}
2.申请报告
/services/walmart/walmartlisting/createreport
public function actionCreatereport(){
$model = YbModel::model('WalmartListingTask');
$accmodel = new WalmartAccount();
$datetime = date('Y-m-d');
$account_id = Yii::app()->request->getParam('account_id');
if($account_id){
$where = ' and account_id='.$account_id;
}
$sql = "select id,account_id,report_type from yibai_walmart_listing_task where report_type in(1,2) and type=0 and is_down=0 and create_date>".$datetime.$where;
$list = $model->getDbConnection()->createCommand($sql)->queryAll();
if($list){
foreach ($list as $val){
$data = [];
$account_info = $accmodel->findByPk($val['account_id']);
$walmartPostService = new WalmartListingData($val['account_id']);
if($account_info->site=='ca'){
$type=$val['report_type']==1?'ITEM_CA':'INVENTORY_CA';
}elseif ($account_info->site=='mx'){
$type=$val['report_type']==1?'ITEM_MX':'INVENTORY_MX';
}elseif ($account_info->site=='us'){
$type=$val['report_type']==1?'ITEM':'INVENTORY';
}
$res = json_decode($walmartPostService->createreportlisting($type),true);
$data['request_body'] = '';
$data['response_body'] = json_encode($res);
$data['update_date'] = date('Y-m-d H:i:s');
if(isset($res['requestId'])){
$data['requestid'] = $res['requestId'];
$data['status'] = $account_info->site=='ca'?'READY':$res['requestStatus'];
$data['type'] = 1;
}
$model->updateAll($data,"id=".$val['id']);
}
}
echo 'DOLL';
}
public function createreportlisting($type){
$accountInfo = [];
if($this->site=='ca'){
$reportVersion = $type=='ITEM_CA'?'v1':'v1';
$url = "https://marketplace.walmartapis.com/v3/ca/reports/reportRequests?reportType=".$type."&reportVersion=".$reportVersion;
$extra = [
"Accept"=>"application/json",
];
$body = '';
}elseif($this->site=='us'){
$reportVersion = $type=='ITEM'?'v4':'v1';
$url = "https://marketplace.walmartapis.com/v3/reports/reportRequests?reportType=".$type."&reportVersion=".$reportVersion;
$extra = [
"Accept"=>"application/json",
"content-type"=>"application/json",
];
$body='';
}elseif($this->site=='mx'){
$reportVersion = 'v1';
$url = "https://marketplace.walmartapis.com/v3/reports/reportRequests?reportType=".$type."&reportVersion=".$reportVersion;
$extra = [
"Accept"=>"application/json",
"content-type"=>"application/json",
];
}
$headers = $this->getHeaders($url, 'POST', $accountInfo, $extra);
if($this->site=='ca'){
$headers[9] = 'Content-Type:application/json';
}
$result = $this->postResult($url, $body, $headers,'json');
return $result;
}
protected function getHeaders($url, $method='GET', $account_info, $extra=array())
{
if (empty($url)) {
return false;
}
if($this->site =='ca'){
//获取签名和时间戳
list($signature, $timestamp) = $this->getSignatureAndTimestamp($url, $method, $account_info);
$default = [
"WM_SVC.NAME" => "Walmart Marketplace",
"WM_QOS.CORRELATION_ID" => $this->getRandStr(),
"WM_SEC.TIMESTAMP" => $timestamp,
"WM_SEC.AUTH_SIGNATURE" => $signature,
"WM_CONSUMER.CHANNEL.TYPE" => $this->channel_type,
"WM_CONSUMER.ID" => $this->consumer_id,
];
} elseif($this->site =='us') {
$default = [
"WM_SVC.NAME" => "Walmart Marketplace",
"WM_QOS.CORRELATION_ID" => $this->getRandStr(),
"Authorization" => "Basic " . base64_encode($this->client_id . ":" . $this->client_sec),
"WM_SEC.ACCESS_TOKEN" => $this->access_token,
"WM_CONSUMER.CHANNEL.TYPE" => null,//$this->channel_type, //新增字段2023-11-02
];
}elseif($this->site =='mx') {
$default = [
"WM_MARKET" => "mx",
"WM_SVC.NAME" => "Walmart Marketplace",
"WM_QOS.CORRELATION_ID" => $this->getRandStr(),
"Authorization" => "Basic " . base64_encode($this->client_id . ":" . $this->client_sec),
"WM_SEC.ACCESS_TOKEN" => $this->access_token,
"WM_CONSUMER.CHANNEL.TYPE" => null,//$this->channel_type, //新增字段2023-11-02
];
}
$ss = array_merge($this->header_params,$default,$extra);
return $this->structureHeaders($ss);
}
protected function postResult($url, $body, $header,$formart='json')
{
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER,false);
curl_setopt($ch, CURLOPT_TIMEOUT, 120);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch,CURLOPT_ENCODING,""); // 返回数据支持所有编码，不乱码 ；如果为空字符串""，会发送所有支持的编码类型。
curl_setopt($ch, CURLOPT_POSTFIELDS,$body);
$data = curl_exec($ch);
$curlInfo = curl_getinfo($ch);
if($_GET['istest']==1){
var_dump($data);
echo '<pre>';print_r($curlInfo);
$version = curl_version();
echo '<pre>';print_r($version);
}
curl_close($ch);
if($formart == 'json'){
}elseif($formart == 'xml'){
$preg = "/(<|<\/)(?:\w+:)/is";
$newXmlStr = preg_replace($preg,"\\1",$data);
//echo $newXmlStr;
$data = simplexml_load_string($newXmlStr);
$data = $data?$data:array('http_code'=>$curlInfo['http_code']);
}
return $data;
}
3.获取报告状态
/services/walmart/walmartlisting/getreportstatus
public function actionGetreportstatus(){
$model = YbModel::model('WalmartListingTask');
$datetime = date('Y-m-d');
$sql = "select id,account_id,requestid from yibai_walmart_listing_task where report_type in(1,2) and type=1 and is_down=0 and status in('INPROGRESS','RECEIVED') and create_date>'".$datetime."'";
$list = $model->getDbConnection()->createCommand($sql)->queryAll();
if(!$list){
die('没有数据需要处理');
}
foreach ($list as $val){
$walmartPostService = new WalmartListingData($val['account_id']);
$res = $walmartPostService->getreportstatus($val['requestid']);
if(isset($res['requestId'])){
$model->updateAll(['status'=>$res['requestStatus'],"update_date"=>date('Y-m-d H:i:s')],"id=".$val['id']);
}
}
echo 'DOLL';
}
public function getreportstatus($requestid){
$accountInfo = [];
if($this->site=='ca'){
$url = "https://marketplace.walmartapis.com/v3/ca/reports/reportRequests/".$requestid;
$extra = [
"Accept"=>"application/json",
"content-type"=>"application/json",
];
}elseif($this->site=='us'){
$url = "https://marketplace.walmartapis.com/v3/reports/reportRequests/".$requestid;
$extra = [
"Accept"=>"application/json",
"content-type"=>"application/json",
];
}elseif($this->site=='mx'){
$url = "https://marketplace.walmartapis.com/v3/reports/reportRequests/".$requestid;
$extra = [
"Accept"=>"application/json",
"content-type"=>"application/json",
];
}
$headers = $this->getHeaders($url, 'GET', $accountInfo, $extra);
$result = $this->getResult($url, $headers);
$result = json_decode($result,true);
return $result;
}
4.解析数据
/services/walmart/walmartlisting/analysislisting
public function actionAnalysislisting(){
set_time_limit(4000);
ini_set('memory_limit', '2048M');
$del = Yii::app()->request->getParam('del');
$account_id = Yii::app()->request->getParam('account_id');
$modeltask = YbModel::model('WalmartListingTask');
$model = new WalmartListing();
$datetime = date('Y-m-d');
if($del==1 && $account_id){
$model->deleteAll("account_id=".$account_id);
die('删除数据成功');
}
$where = '';
if($account_id){
$where = ' and account_id='.$account_id;
}
$sql = "select id,account_id,report_type,requestid from yibai_walmart_listing_task where report_type=1 and type=1 and is_down=0 and status='READY' and create_date>".$datetime." {$where} limit 1";
$list = $modeltask->getDbConnection()->createCommand($sql)->queryAll();
if(!$list){
$sql = "select id,account_id,report_type,requestid from yibai_walmart_listing_task where report_type=2 and type=1 and is_down=0 and status='READY' and create_date>".$datetime." {$where} limit 1";
$list = $modeltask->getDbConnection()->createCommand($sql)->queryAll();
if(!$list){
die('没有数据需要处理');
}
}
$date = date('Ymd');
$path = Yii::getPathOfAlias('webroot') . '/upload/erp_log/walmartreport/';
if (!file_exists($path)) {
mkdir(Yii::getPathOfAlias('webroot') . '/upload/erp_log/', 0777);
mkdir($path, 0777);
}
foreach ($list as $val){
$modeltask->updateAll(['type'=>2],"id=".$val['id']);//正在解析
$walmartPostService = new WalmartListingData($val['account_id']);
$res = $walmartPostService->downloadreport($val['requestid']);
//因为ca获取状态接口没有返回结果 申请成功的时候则给状态为READY
if(!isset($res['downloadURL']) || $res['requestStatus']!='READY'){
continue;
}
if(isset($res['requestId'])){
$modeltask->updateAll(['down_url'=>$res['downloadURL'],"update_date"=>date('Y-m-d H:i:s')],"id=".$val['id']);
}else{
$modeltask->updateAll(['is_down'=>1,"update_date"=>date('Y-m-d H:i:s')],"id=".$val['id']);
continue;
}
$walmartPostService = new WalmartListingData($val['account_id']);
$accountinfo = YbModel::model('WalmartAccount')->findByPk($val['account_id']);
$filename = 'walmartreport_'.$date.'_'.$val['account_id'].'_'.$val['report_type'].'.zip'; //设置文件名
if (!file_exists($path.$filename)){
@unlink($path.$filename);
}
$walmartPostService->getFile($res['downloadURL'],$path,$filename);
$zip = new ZipArchive;
$res = $zip->open($path.$filename);
$datatimes = date('Y-m-d H:i:s');
if ( ($res === TRUE && $val['report_type']==1) || ($res&& $val['report_type']==2) ) {
//解压缩到log文件夹
$zip->extractTo(Yii::getPathOfAlias('webroot') . '/upload/erp_log/walmartreport/');
$file = $zip->getNameIndex(0);
$zip->close();
$file_excel = Yii::getPathOfAlias('webroot') . '/upload/erp_log/walmartreport/'.$file;
$file = fopen($file_excel,'r');
$i =0;
$msg = '';
$walmartListingModel = new WalmartListing();
if($val['report_type']==1) {
while (($data = fgetcsv($file)) !== false) { //每次读取CSV里面的一行内容
if($accountinfo->site=='us'){
if ($data[0] == 'SKU') {
continue;
}
$item_id = trim($data[1]);
$sellerSku = trim($data[0], '="');
$sellerSku = trim(trim($sellerSku, '"'));
$accountId = $val['account_id'];
$skuMap = YbModel::model('Walmartskumap')->find("seller_sku ='{$sellerSku}'");
if(!in_array(trim($data[4]),['UNPUBLISHED','IN_PROGRESS','SYSTEM_PROBLEM','PUBLISHED'])){
continue;
}
$row = array(
'partner_id' => $accountinfo->partner_id,
'seller_sku' => $sellerSku,
'item_id' => $item_id,
'product_name' => isset($data[2]) && $data[2]?$data[2]:'',
'lifecycle_status' => isset($data[3]) && $data[3]?$data[3]:'',
'publish_status' => isset($data[4]) && $data[4]?$data[4]:'',
'status_change_reason' => isset($data[5]) && $data[5]?$data[5]:'',
'product_category' => isset($data[6]) && $data[6]?$data[6]:'',
'price' => isset($data[7])&& $data[7] ? $data[7] : 0.00,
'currency' => isset($data[8])&& $data[8]?$data[8]:'',
'buybox_item_price_ca' => isset($data[9])&& $data[9] ? $data[9] : '0.00',//购物车价格
'buybox_ship_price_ca' => isset($data[10])&& $data[10] ? $data[10] : '0.00',//购物车运费
'wpid' => isset($data[20]) && $data[20]? $data[20] : '',
'gtin' => isset($data[21])&& $data[21] ? $data[21] : '',
'upc' => isset($data[22])&& $data[22] ? $data[22] : '',
'offer_start_date' => date('Y-m-d H:i:s', strtotime($data[28])),
'offer_end_date' => date('Y-m-d H:i:s', strtotime($data[29])),
'item_creation_date' => date('Y-m-d H:i:s', strtotime($data[30])),
'item_last_updated' => date('Y-m-d H:i:s', strtotime($data[31])),
'reviews_count' => isset($data[32])&& $data[32]?(int)$data[32]:0,
'average_rating' => isset($data[33]) && $data[33]? $data[33] : 0,
'searchable' => isset($data[34])&& $data[34] ? $data[34] : '',
'original_price' => isset($data[7])&& $data[7] ? $data[7] : '0.00',
'account_id' => $accountId,
'item_page_url'=>isset($data[23])&& $data[23] ? $data[23] : '',
'main_url'=>isset($data[24])&& $data[24] ? $data[24] : '',
'sku' => $skuMap->sku,
);
if($row['offer_start_date']=='1970-01-01 08:00:00'){
$row['offer_start_date'] = date('Y-m-d H:i:s');
}
if($row['offer_end_date']=='1970-01-01 08:00:00'){
$row['offer_end_date'] = date('Y-m-d H:i:s');
}
if($row['item_creation_date']=='1970-01-01 08:00:00'){
$row['item_creation_date'] = date('Y-m-d H:i:s');
}
if($row['item_last_updated']=='1970-01-01 08:00:00'){
$row['item_last_updated'] = date('Y-m-d H:i:s');
}
if($row['offer_start_date']>'2038-01-01'){
$row['offer_start_date'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
if($row['offer_end_date']>'2038-01-01'){
$row['offer_end_date'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
if($row['item_creation_date']>'2038-01-01'){
$row['item_creation_date'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
if($row['item_last_updated']>'2038-01-01'){
$row['item_last_updated'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
$taskRow = $walmartPostService->getWalmartTaskRowByListing($row["item_id"], $row["sku"], $row["account_id"]);
if (!empty($taskRow)) {
$row["task_id"] = $taskRow["id"];
$row["source_type"] = $taskRow["source_type"];
}
$WalmartListingModel = YbModel::model('WalmartListing')->find("account_id = $accountId and seller_sku ='{$sellerSku}' and item_id='{$item_id}'");
if ($WalmartListingModel) {
if ($WalmartListingModel->original_price > 0) {
unset($row['original_price']);
}
$row['update_time'] = date('Y-m-d H:i:s');
$re_update = $walmartListingModel->getDbConnection()->createCommand()->update(
$walmartListingModel->tableName(),
$row,
"account_id = $accountId and seller_sku ='{$sellerSku}' and item_id='{$item_id}'"
);
if ($re_update) {
$i++;
}
} else {
$row['create_time'] = date('Y-m-d H:i:s');
if (!$model->getDbConnection()->createCommand()->insert($model->tableName(), $row)) {
$i++;
}
}
}elseif ($accountinfo->site=='mx'){
if ($data[0] == 'SKU') {
continue;
}
$sellerSku = trim($data[0]);
$accountId = $val['account_id'];
$item_id = trim($data[8]);
$skuMap = YbModel::model('Walmartskumap')->find("seller_sku ='{$sellerSku}'");
if(isset($data[14])){
$t1 = explode('/',$data[14]);
$t1d = $t1[2].'-'.$t1[1].'-'.$t1[0];
}
if(isset($data[15])){
$t2 = explode('/',$data[15]);
$t2d = $t2[2].'-'.$t2[1].'-'.$t2[0];
}
if(isset($data[16])){
$t3 = explode('/',$data[16]);
$t3d = $t3[2].'-'.$t3[1].'-'.$t3[0];
}
if(isset($data[17])){
$t4 = explode('/',$data[17]);
$t4d = $t4[2].'-'.$t4[1].'-'.$t4[0];
}
if(!in_array(trim($data[5]),['UNPUBLISHED','IN_PROGRESS','SYSTEM_PROBLEM','PUBLISHED'])){
continue;
}
$row = array(
'partner_id' => $accountinfo->partner_id,
'seller_sku' => $sellerSku,
'item_id' => $item_id,
'product_name' => isset($data[1])&& $data[1]?$data[1]:'',
'lifecycle_status' => isset($data[7])&& $data[7]?$data[7]:'',
'publish_status' => isset($data[5])&& $data[5]?$data[5]:'',
'product_category' => isset($data[2])&& $data[2]?$data[2]:'',
'price' => isset($data[3])&& $data[3] ? $data[3] : 0.00,
'currency' => isset($data[4])&& $data[4]?$data[4]:'',
'wpid' => isset($data[8])&& $data[8] ? $data[8] : '',
'gtin' => isset($data[9])&& $data[9] ? $data[9] : '',
'upc' => isset($data[10])&& $data[10] ? $data[10] : '',
'account_id' => $accountId,
'main_url'=>isset($data[11])&& $data[11] ? $data[11] : '',
'offer_start_date' => date('Y-m-d H:i:s',strtotime($t1d)),
'offer_end_date' => date('Y-m-d H:i:s',strtotime($t2d)),
'item_creation_date' => date('Y-m-d H:i:s',strtotime($t3d)),
'item_last_updated' => date('Y-m-d H:i:s',strtotime($t4d)),
'shelf_name' => isset($data[12])&& $data[12] ? $data[12] : '',
'primary_cat_path'=> isset($data[13])&& $data[13] ? $data[13] : '',
'sku' => $skuMap->sku,
);
if($row['offer_start_date']=='1970-01-01 08:00:00'){
$row['offer_start_date'] = date('Y-m-d H:i:s');
}
if($row['offer_end_date']=='1970-01-01 08:00:00'){
$row['offer_end_date'] = date('Y-m-d H:i:s');
}
if($row['item_creation_date']=='1970-01-01 08:00:00'){
$row['item_creation_date'] = date('Y-m-d H:i:s');
}
if($row['item_last_updated']=='1970-01-01 08:00:00'){
$row['item_last_updated'] = date('Y-m-d H:i:s');
}
if($row['offer_start_date']>'2038-01-01'){
$row['offer_start_date'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
if($row['offer_end_date']>'2038-01-01'){
$row['offer_end_date'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
if($row['item_creation_date']>'2038-01-01'){
$row['item_creation_date'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
if($row['item_last_updated']>'2038-01-01'){
$row['item_last_updated'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
$taskRow = $walmartPostService->getWalmartTaskRowByListing($row["item_id"], $row["sku"], $row["account_id"]);
if (!empty($taskRow)) {
$row["task_id"] = $taskRow["id"];
$row["source_type"] = $taskRow["source_type"];
}
$WalmartListingModel = YbModel::model('WalmartListing')->find("account_id = $accountId and seller_sku ='{$sellerSku}' and item_id='{$item_id}'");
if($WalmartListingModel){
$row['update_time'] = date('Y-m-d H:i:s');
$re_update = $walmartListingModel->getDbConnection()->createCommand()->update(
$walmartListingModel->tableName(),
$row,
"account_id = $accountId and seller_sku ='{$sellerSku}' and item_id='{$item_id}'"
);
if ($re_update) {
$i++;
}
}else{
$row['create_time'] = date('Y-m-d H:i:s');
$model = new WalmartListing();
if(!$model->getDbConnection()->createCommand()->insert($model->tableName(), $row)){
$i++;
}
}
}elseif ($accountinfo->site=='ca'){
if($data[0]=='SKU' && $data[1]=='PRODUCT NAME'){
continue;
}
$sellerSku =trim($data[0],'="');
$sellerSku =trim(trim($sellerSku,'"'));
$accountId = $val['account_id'];
$item_id = trim($data[14]);
$skuMap = YbModel::model('Walmartskumap')->find("seller_sku ='{$sellerSku}'");
if(!$sellerSku){
continue;
}
if(in_array(trim($data[8]),['UNPUBLISHED','IN_PROGRESS','SYSTEM_PROBLEM','PUBLISHED'])){
$row =array(
'partner_id' => $accountinfo->partner_id,
'seller_sku' => $sellerSku,
'product_name' => isset($data[1])&& $data[1]?$data[1]:'',
'product_category' => isset($data[2])&& $data[2]?$data[2]:'',
'price' => isset($data[3])&& $data[3]?$data[3]:0.00,
'currency' => isset($data[4])&& $data[4]?$data[4]:'',
'buybox_item_price_ca' => isset($data[5])&& $data[5] && $data[5]?$data[5]:0.00,//购物车价格
'buybox_ship_price_ca' => isset($data[6])&& $data[6]?(double)$data[6]:0.00,//购物车运费
'won_buy_box' => isset($data[7])&& $data[7]?$data[7]:'',//WON BUY BOX?
'publish_status' => isset($data[8])&& $data[8]?$data[8]:'',
'status_change_reason' => isset($data[9])&& $data[9]?$data[9]:'',
'lifecycle_status' => isset($data[10])&& $data[10]?$data[10]:'',
//'inventory_count' => $data[12]?$data[12]:0,
'ship_method' => isset($data[12])&& $data[12]?$data[12]:'',
'wpid' => isset($data[13])&& $data[13] ? $data[13] : '',
'item_id' => isset($data[14]) && $data[14]? $data[14]:'',
'gtin' => isset($data[15])&& $data[15] ? $data[15] : '',
'upc' => isset($data[16]) && $data[16]? $data[16] : '',
'main_url' => isset($data[17])&& $data[17]? $data[17] : '',
'shelf_name' => isset($data[18])&& $data[18] ? $data[18] : '',
'primary_cat_path' => isset($data[19])&& $data[19] ? $data[19] : '',
'offer_start_date' => date('Y-m-d H:i:s',strtotime($data[20])),
'offer_end_date' => date('Y-m-d H:i:s',strtotime($data[21])),
'item_creation_date' => date('Y-m-d H:i:s',strtotime($data[22])),
'item_last_updated' => date('Y-m-d H:i:s',strtotime($data[23])),
'item_page_url' => isset($data[24])&& $data[24] ? $data[24] : '',
'reviews_count' => isset($data[25])&& $data[25] ? $data[25] : 0,
'average_rating' => isset($data[26])&& $data[26]?$data[26]:0,
'searchable' => isset($data[27])&& $data[27] ? $data[27] : '',
'account_id' => $accountId,
//'sku' => $skuMap->sku,
'original_price' => isset($data[3])&& $data[3]?$data[3]:0,
);
}elseif(in_array(trim($data[7]),['UNPUBLISHED','IN_PROGRESS','SYSTEM_PROBLEM','PUBLISHED'])){
$row =array(
'partner_id' => $accountinfo->partner_id,
'seller_sku' => $sellerSku,
'product_name' => isset($data[1])&& $data[1]?$data[1]:'',
'product_category' => '',
'price' => isset($data[2])&& $data[2]?$data[2]:0.00,
'currency' => isset($data[3])&& $data[3]?$data[3]:'',
'buybox_item_price_ca' => isset($data[4])&& $data[4]?$data[4]:0.00,//购物车价格
'buybox_ship_price_ca' => isset($data[5])&& $data[5]?$data[5]:0.00,//购物车运费
'won_buy_box' => isset($data[6])&& $data[6]?$data[6]:'',//WON BUY BOX?
'publish_status' => isset($data[7])&& $data[7]?$data[7]:'',
'status_change_reason' => isset($data[8])&& $data[8]?$data[8]:'',
'lifecycle_status' => isset($data[9])&& $data[10]?$data[10]:'',
'ship_method' => isset($data[11])&& $data[11]?$data[11]:'',
'wpid' => isset($data[12])&& $data[12] ? $data[12] : '',
'item_id' => isset($data[13]) && $data[13]? $data[13]:'',
'gtin' => isset($data[14])&& $data[14] ? $data[14] : '',
'upc' => isset($data[15]) && $data[15]? $data[15] : '',
'main_url' => isset($data[16])&& $data[16]? $data[16] : '',
'shelf_name' => isset($data[17])&& $data[17] ? $data[17] : '',
'primary_cat_path' => isset($data[18])&& $data[18] ? $data[18] : '',
'offer_start_date' => date('Y-m-d H:i:s',strtotime($data[19])),
'offer_end_date' => date('Y-m-d H:i:s',strtotime($data[20])),
'item_creation_date' => date('Y-m-d H:i:s',strtotime($data[21])),
'item_last_updated' => date('Y-m-d H:i:s',strtotime($data[22])),
'item_page_url' => isset($data[23])&& $data[23] ? $data[23] : '',
'reviews_count' => isset($data[24])&& $data[24] ? $data[24] : 0,
'average_rating' => isset($data[25])&& $data[25]? $data[25]:0,
'searchable' => isset($data[26])&& $data[26] ? $data[26] : '',
'account_id' => $accountId,
'original_price' => isset($data[2])&& $data[2]?$data[2]:0,
);
}else{
continue;
}
if($row['offer_start_date']=='1970-01-01 08:00:00'){
$row['offer_start_date'] = date('Y-m-d H:i:s');
}
if($row['offer_end_date']=='1970-01-01 08:00:00'){
$row['offer_end_date'] = date('Y-m-d H:i:s');
}
if($row['item_creation_date']=='1970-01-01 08:00:00'){
$row['item_creation_date'] = date('Y-m-d H:i:s');
}
if($row['item_last_updated']=='1970-01-01 08:00:00'){
$row['item_last_updated'] = date('Y-m-d H:i:s');
}
if($row['offer_start_date']>'2038-01-01'){
$row['offer_start_date'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
if($row['offer_end_date']>'2038-01-01'){
$row['offer_end_date'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
if($row['item_creation_date']>'2038-01-01'){
$row['item_creation_date'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
if($row['item_last_updated']>'2038-01-01'){
$row['item_last_updated'] = date('Y-m-d H:i:s',strtotime('2038-01-01'));
}
if($skuMap->sku){
$row['sku'] = $skuMap->sku;
}
$WalmartListingModel = YbModel::model('WalmartListing')->find("account_id = $accountId and seller_sku ='{$sellerSku}' and item_id='{$item_id}'");
if($WalmartListingModel){
$row['update_time'] = date('Y-m-d H:i:s');
$re_update = $walmartListingModel->getDbConnection()->createCommand()->update(
$walmartListingModel->tableName(),
$row,
"account_id = $accountId and seller_sku ='{$sellerSku}' and item_id='{$item_id}'"
);
if ($re_update) {
$i++;
}
}else{
$row['create_time'] = date('Y-m-d H:i:s');
$row['update_time'] = date('Y-m-d H:i:s');
$model = new WalmartListing();
if(!$model->getDbConnection()->createCommand()->insert($model->tableName(), $row)){
$i++;
}
}
}
}
}else{
while (($data = fgetcsv($file)) !== false) { //每次读取CSV里面的一行内容
if($accountinfo->site=='us'){
if ($data[0] == 'SKU') {
continue;
}
$seller_sku = $data[0];
$inventory_count = isset($data[6])&&$data[6]?$data[6]:0;
$item_id = isset($data[1])?$data[1]:'';
$WalmartListingModel = $model->find("account_id = {$val['account_id']} and seller_sku='{$seller_sku}' and item_id='{$item_id}'");
if($WalmartListingModel){
$model->updateAll(['inventory_count'=>$inventory_count,'inventory_updatetime'=>$datatimes],"account_id = {$val['account_id']} and seller_sku='{$seller_sku}' and item_id='{$item_id}'");
}
}elseif ($accountinfo->site=='mx'){
if ($data[3] == 'SKU') {
continue;
}
$seller_sku = isset($data[3])&&$data[3]?$data[3]:'';
$inventory_count = isset($data[6])&&$data[6]?$data[6]:0;
$WalmartListingModel = $model->find("account_id = {$val['account_id']} and seller_sku='{$seller_sku}'");
if($WalmartListingModel){
$model->updateAll(['inventory_count'=>$inventory_count,'inventory_updatetime'=>$datatimes],"account_id = {$val['account_id']} and seller_sku='{$seller_sku}'");
}
}elseif ($accountinfo->site=='ca'){
if ($data[0] == 'Seller Sku ID') {
continue;
}
$seller_sku = isset($data[0])&&$data[0]?$data[0]:'';
$inventory_count = isset($data[2])&&$data[2]?$data[2]:0;
$wpid = isset($data[4])&&$data[4]?$data[4]:'';
$WalmartListingModel = $model->find("account_id = {$val['account_id']} and seller_sku='{$seller_sku}' and wpid='{$wpid}'");
if($WalmartListingModel){
$model->updateAll(['inventory_count'=>$inventory_count,'inventory_updatetime'=>$datatimes],"account_id = {$val['account_id']} and seller_sku='{$seller_sku}' and wpid='{$wpid}'");
}
}
}
}
}
$modeltask->updateAll(['is_down'=>1,'update_date'=>date('Y-m-d H:i:s')],"id=".$val['id']);
fclose($file);
fclose($file_excel);
unlink($file);
unlink($file_excel);
unlink($filename);
}
$url = sprintf('%s/services/walmart/walmartlisting/analysislisting', $_SERVER['HTTP_HOST']);
echo $url;echo '<br/>';
MHelper::curl_post_async($url);
echo 'DOLL';
}
标签：isset,沃尔玛,&&,listing,date,拉取,data,id,row From： https://www.cnblogs.com/xiangshihua/p/18323651