沃爾瑪拉取listing和庫存

天心PHP發表於2024-07-25

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';
}

大批Listing突變狗，幾百萬庫存的賣家哭了
2020-09-07
拉取易倉API的亞馬遜Listing資料-listing表現介面
2024-11-02
API亞馬遜
讀取和儲存Excel表
2019-02-21
Excel
Listing Chained Rows of Tables and Clusters
2009-05-07
AI
Android內部儲存和外部儲存，以及讀取Android讀取RAM,ROM內部儲存和外部儲存卡容量
2017-12-02
Android
java mysql儲存和讀取檔案
2011-12-11
JavaMySql
sql 2k中的圖片儲存和獲取----引申到檔案儲存和獲取 (轉)
2007-08-14
SQL
SAP RETAIL 商品LISTING方法之一
2021-10-10
AI
Recipe 5.2. Listing a Table's Columns
2010-12-07
Web快取知多少(快取機制和資料儲存)
2019-03-04
Web快取
（slam工具）1檔案讀取和儲存
2024-06-17
SLAM
使用Hibernate和Struts向資料庫中儲存、讀取並顯示圖片
2010-09-01
資料庫
獲取某庫某個儲存過程內容
2008-01-22
儲存過程
HTTP快取和瀏覽器的本地儲存
2019-08-16
HTTP快取瀏覽器
redis叢集資料儲存和獲取原理
2018-12-28
Redis
Git 工作區、暫存區和版本庫
2015-05-18
Git
python讀取檔案——python讀取和儲存mat檔案
2017-11-20
Python
java+pgsql實現儲存圖片到資料庫，以及讀取資料庫儲存的圖片
2020-11-13
JavaSQL資料庫
OU、庫存組織與子庫存
2008-09-24
沃爾瑪申請專利，欲在區塊鏈上儲存支付資料
2018-04-23
區塊鏈
web 的一些基本的快取和儲存
2017-03-29
Web快取
什麼是庫存？什麼是零庫存？庫存的定義
2021-11-23
找關鍵詞-SEO及Listing優化
2022-04-25
優化
SAP MM 特殊庫存之T庫存初探
2019-07-01
安全庫存
2009-07-06
Electrode: 沃爾瑪React和Node.js開源應用框架
2016-10-07
ReactNode.js框架
Mybatis讀取和儲存json型別的資料
2024-09-12
MyBatisJSON型別
EA Job Listing: Madden Could Be Coming to a Switch
2021-08-16
聊聊資料庫和快取同步機制
2017-07-24
資料庫快取
一文詳解 JuiceFS 讀效能：預讀、預取、快取、FUSE 和物件儲存
2024-07-26
UI快取物件
IIHS：賓士、沃爾沃和特斯拉新車裝備AEB系統比例最高
2019-03-15
C++(2) 從yml或者txt讀取和儲存資料
2024-07-15
C++
c++ (2-0) 從txt讀取和儲存資料
2024-08-17
C++
如何使用SAP零售系統中的LISTING？
2021-09-01
[資料庫系統]儲存和檔案結構
2018-07-24
資料庫
食品庫存管理
2018-09-04
SKU_庫存
2018-07-17
庫存檔點
2008-07-11

沃爾瑪拉取listing和庫存

相關文章