/**
* 海外仓销售报表数据导出，未开发完成
*/
public function act_ebayOversea()
{
$user = $_SESSION['userName'];
$objPHPExcel = new PHPExcel();
$sendreplacement = array('1' => '补寄全部', '2' => '补寄主体', '3' => '补寄配件');
$ebay_splitorder_logs = array('0' => '拆分 订单', '1' => '复制 订单', '2' => '异常 订单', '3' => '合并 包裹', '4' => '邮局退回补寄', '5' => '自动部分包货拆分', '7' => '同步异常订单');
$MAILWAYCONFIG = array(0 => 'EUB', 1 => '深圳', 2 => '福州', 3 => '三泰', 4 => '泉州', 5 => '义乌', 6 => '福建', 7 => '中外联', 8 => 'GM', 9 => '香港', 10 => '快递');
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '日期');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', 'ebay store');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '交易号');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '客户ID');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', '仓位号');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', '料号');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G1', '数量');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H1', '国家');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I1', '产品价格');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J1', 'ebay运费');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K1', '包裹总价值');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L1', '币种');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M1', '包装员');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N1', '挂号条码');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('O1', '是/否');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P1', '重量');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q1', '邮费');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R1', '运输方式');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('S1', '订单编号');
/* 王民伟 2012-04-18*/
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T1', '产品货本');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('U1', '交易ID');
/*---end Tt Uu Vv Ww Xx*/
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V1', 'ItemID');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('W1', '是否复制订单');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('X1', '是否补寄');
//add by Herman.Xi 2012-09-13
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y1', '是否拆分订单');
//add by Herman.Xi 2012-09-14
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z1', '包材');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA1', '包材费用');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB1', '是否组合料号');
//add by Herman.Xi 2012-12-17
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC1', '发货分区');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD1', '是否邮局退回后补寄');
//add by Herman.Xi 2013-03-09
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE1', 'PayPal邮箱');
//add by Herman.Xi 2013-03-09
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF1', '采购');
//add by chenwei 2013-09-07
$start = strtotime($_REQUEST['start']);
$end = strtotime($_REQUEST['end']);
//echo $start."||".$end;exit;
$account = $_REQUEST['account'];
$tjstr = '';
if ($account != '') {
$account = explode("#", $account);
for ($i = 0; $i < count($account); $i++) {
$a0 = $account[$i];
if ($a0 != '') {
$tjstr .= " a.accountId = '" . $a0 . "' or ";
}
}
}
$packinglists = GoodsModel::getMaterInfo();
//需要调产品中心的接口才可以使用。
$packings = array();
foreach ($packinglists as $packinglist) {
//$packings[$packinglist['id']] = $packinglist['pmCost'];
$packings[$packinglist['pmAlias']] = $packinglist['pmCost'];
}
unset($packinglists);
$tjstr = substr($tjstr, 0, strlen($tjstr) - 3);
//$ret = OrderInfoModel::getShipOrderList($start,$end,$tjstr);
$ret = WarehouseAPIModel::getAbOrderList();
$a = 2;
for ($i = 0; $i < count($shipData); $i++) {
$ordersn = $ret[$i]['ebay_ordersn'];
$paidtime = @date('Y-m-d', $ret[$i]['ebay_paidtime']);
$ebay_usermail = $ret[$i]['ebay_usermail'];
$ebay_userid = $ret[$i]['ebay_userid'];
$name = @html_entity_decode($ret[$i]['ebay_username'], ENT_QUOTES, 'UTF-8');
$street1 = @$ret[$i]['ebay_street'];
$street2 = @$ret[$i]['ebay_street1'];
$city = $ret[$i]['ebay_city'];
$state = $ret[$i]['ebay_state'];
$countryname = $ret[$i]['ebay_countryname'];
//客服部小霞提出 导出列 国家 显示英文 方便退款处理
//$cnname = $country[$countryname];
$cnname = $countryname;
$zip = $ret[$i]['ebay_postcode'];
$tel = $ret[$i]['ebay_phone'];
$ebay_shipfee = $ret[$i]['ebay_shipfee'];
$ebay_note = $ret[$i]['ebay_note'];
$ebay_total = @round($ret[$i]['ebay_total'], 2);
$ebay_tracknumber = @$ret[$i]['ebay_tracknumber'];
$ebay_account = @$ret[$i]['ebay_account'];
$recordnumber0 = @$ret[$i]['recordnumber'];
$ebay_carrier = @$ret[$i]['ebay_carrier'];
$ebay_phone = $ret[$i]['ebay_phone'];
$ebay_currency = $ret[$i]['ebay_currency'];
$packinguser = $ret[$i]['packagingstaff'];
$ordershipfee = $ret[$i]['ordershipfee'];
$ebay_id = $ret[$i]['ebay_id'];
$ebay_ptid = $ret[$i]['ebay_ptid'];
$ebay_pp = $ret[$i]['PayPalEmailAddress'];
$ebay_noteb = $ret[$i]['ebay_noteb'];
//=='复制 订单' ? $ret[$i]['ebay_noteb'] : ''
$is_sendreplacement = $ret[$i]['is_sendreplacement'];
$is_main_order = $ret[$i]['is_main_order'];
$mailway = $ret[$i]['mailway'];
$address = $street1 . "\n" . $street2 . "\n" . $city . "\n" . $state . "\n" . $zip . "\n" . $countryname;
$scantime = date('Y-m-d', $sql[$i]['scantime']);
//$calculate_weight = $sql[$i]['orderweight']; //计算重量
$orderweight2 = number_format($sql[$i]['orderweight2'] / 1000, 3);
//实际重量
$totalweight = $orderweight2;
$ordershipfee = round(calctrueshippingfee($ebay_carrier, $totalweight, $countryname, $ebay_id), 2);
$ebay_noteb = $is_main_order == 2 ? '复制 订单' : ($is_main_order == 1 ? '被复制 订单' : '');
$ebay_splitorder = judge_is_splitorder($ebay_id) == 1 ? '拆分 订单' : '';
$ebay_combineorder = judge_contain_combinesku($ordersn) ? '组合 料号' : '';
$splitorder_log = func_readlog_splitorder($ebay_id);
$ebay_splitorder_log = '';
if ($splitorder_log != false) {
$ebay_splitorder_log = $ebay_splitorder_logs[$splitorder_log];
}
$is_sendreplacement = isset($sendreplacement[$is_sendreplacement]) ? $sendreplacement[$is_sendreplacement] : '';
$sl = "select * from ebay_orderdetail where ebay_ordersn='{$ordersn}'";
$sl = $dbcon->execute($sl);
$sl = $dbcon->getResultArray($sl);
if ($mailway === null) {
$mailsql = "SELECT mailway FROM ebay_scan_mailway WHERE ebay_id={$sql[$i]['combine_package']}";
$mailsql = $dbcon->execute($mailsql);
$mailllist = $dbcon->getResultArray($mailsql);
$mailway_c = !empty($mailllist[0]['mailway']) ? $MAILWAYCONFIG[$mailllist[0]['mailway']] . '合并包裹' : '';
} else {
$mailway_c = $MAILWAYCONFIG[$mailway];
}
}
}
function auto_contrast_intercept($ebay_orders)
{
//订单自动拦截完整版 支持虚拟料号
//add Herman.Xi 2012-12-20
/*
* 订单进入系统首先判断 是否为超大订单，如果为超大订单，文件夹为640；
* B2B 抓取超大订单自动进入 超大订单待确认 modified by Herman.Xi @ 2013.05.20
* 判断订单下，料号是否全部有货，部分有货，全部没货：
如果部分有货，判断其运输方式，如果为快递，文件夹为659；非快递则为660；（订单自动部分包货）
如果全部没货，判断其运输方式，如果为快递，文件夹为658；非快递则为661；（订单自动拦截）
如果全部有货：
先判断如果为组合订单，文件夹为606；
如果超重订单，文件夹为608；
如果快递订单，文件夹为639；
全部不满足则为导入状态
自动拦截时，判断自动拦截快递，非快递，自动部分包货快递，非快递里面的订单，自动每隔十五分钟执行一次
//支持 速卖通导入，敦煌导入，ebay线下导入，dresslink导入，快递导入，出口通导入
*/
global $dbcon, $defaultstoreid, $SYSTEM_ACCOUNTS, $order_statistics, $__liquid_items_fenmocsku, $__liquid_items_SuperSpecific, $__liquid_items_postbyhkpost, $__liquid_items_cptohkpost, $__liquid_items_BuiltinBattery, $__liquid_items_Paste;
$log_data = "";
$express_delivery = array('UPS', 'DHL', 'TNT', 'EMS', 'FedEx');
$no_express_delivery = array('中国邮政平邮', '中国邮政挂号', '香港小包平邮', '香港小包挂号', 'EUB', 'Global Mail');
foreach ($ebay_orders as $ebay_order) {
//$import_status = now_order_status_log($osn, false);
$ebay_id = $ebay_order['ebay_id'];
$ebay_status = $ebay_order['ebay_status'];
//$ebay_orderid = $ebay_order['ebay_orderid'];
$ebay_note = $ebay_order['ebay_note'];
$order_sn = $ebay_order['ebay_ordersn'];
$ebay_carrier = $ebay_order['ebay_carrier'];
$ebay_countryname = $ebay_order['ebay_countryname'];
$ebay_account = $ebay_order['ebay_account'];
$ebay_username = $ebay_order['ebay_username'];
$totalweight = $ebay_order['orderweight'];
$ebay_total = $ebay_order['ebay_total'];
$recal_weight = recalcorderweight($order_sn, $ebay_packingmaterial);
//modified by Herman.Xi 2012-10-17
if (empty($totalweight)) {
//按照如果没有重量的即默认为
$totalweight = $recal_weight;
}
$sql = "SELECT sku,ebay_amount FROM ebay_orderdetail WHERE ebay_ordersn='{$order_sn}'";
$result = $dbcon->execute($sql);
$orderdetaillist = $dbcon->getResultArray($result);
$contain_special_item = false;
$shippment_cptohkpost = false;
foreach ($orderdetaillist as $orderdetail) {
$sku_arr = get_realskuinfo($orderdetail['sku']);
foreach ($sku_arr as $or_sku => $or_nums) {
if (in_array($or_sku, $__liquid_items_fenmocsku) || in_array($or_sku, $__liquid_items_SuperSpecific)) {
//粉末状，超规格产品 走福建邮局
$contain_special_item = true;
}
if (in_array($or_sku, $__liquid_items_postbyhkpost) || in_array($or_sku, $__liquid_items_cptohkpost) || in_array($or_sku, $__liquid_items_BuiltinBattery) || in_array($or_sku, $__liquid_items_Paste)) {
//包含特殊料号，中国邮政转香港小包
$shippment_cptohkpost = true;
}
}
}
if (!empty($ebay_carrier)) {
//$fees = calcshippingfee($totalweight,$ebay_countryname,$ebay_id,$ebay_account,$ebay_total);
if ($shippment_cptohkpost && strpos($ebay_carrier, '中国邮政') !== false) {
if (count($orderdetaillist) == 1) {
$ebay_carrier = '香港小包挂号';
} else {
$ebay_carrier = '';
$aliexpress_ebay_noteb = "系统提示：包含特殊料号不走中国邮政，请重新选择运输方式或者拆包！";
}
//$fee = $fees[1];
//$totalweight = $fees[2];
$bb = "update ebay_order set ebay_carrier = '{$ebay_carrier}', orderweight ='{$totalweight}', packingtype ='{$ebay_packingmaterial}', ebay_noteb = '{$aliexpress_ebay_noteb}' where ebay_id ='{$ebay_id}' ";
} else {
//$ebay_carrier = $fees[0];
//$fee = $fees[1];
//$totalweight = $fees[2];
$shipfee = calctrueshippingfee($ebay_carrier, $totalweight, $ebay_countryname, $ebay_id);
$bb = "update ebay_order set ebay_carrier = '{$ebay_carrier}', ordershipfee='{$shipfee}', orderweight ='{$totalweight}', packingtype ='{$ebay_packingmaterial}' where ebay_id ='{$ebay_id}' ";
}
$dbcon->execute($bb);
}
if ($contain_special_item) {
$sql = "update ebay_order set ebay_carrierstyle ='1' where ebay_id ={$ebay_id}";
//add by Herman.Xi 记录该订单含有特殊料号
$dbcon->execute($sql);
$log_data .= "[" . date("Y-m-d H:i:s") . "]\t包含粉末状超规格产品---{$ebay_id}---!\n\n";
}
$record_details = array();
$is_640 = false;
foreach ($orderdetaillist as $orderdetail) {
$sku_arr = get_realskuinfo($orderdetail['sku']);
$hava_goodscount = true;
foreach ($sku_arr as $or_sku => $or_nums) {
$allnums = $or_nums * $orderdetail['ebay_amount'];
if (!check_sku($or_sku, $allnums, $ebay_id)) {
//超大订单状态
if (in_array($ebay_account, $SYSTEM_ACCOUNTS['aliexpress']) || in_array($ebay_account, $SYSTEM_ACCOUNTS['B2B外单']) || in_array($ebay_account, $SYSTEM_ACCOUNTS['DHgate']) || in_array($ebay_account, $SYSTEM_ACCOUNTS['出口通']) || in_array($ebay_account, $SYSTEM_ACCOUNTS['线下结算客户'])) {
$sql = "UPDATE ebay_order SET ebay_status='698' WHERE ebay_id ='{$ebay_id}' ";
$log_data .= "[" . date("Y-m-d H:i:s") . "]\t自动跳转---{$ebay_id}---的状态为---698!\n\n";
} else {
$sql = "UPDATE ebay_order SET ebay_status='640' WHERE ebay_id ='{$ebay_id}' ";
$log_data .= "[" . date("Y-m-d H:i:s") . "]\t自动跳转---{$ebay_id}---的状态为---640!\n\n";
}
$dbcon->execute($sql) or die("Fail : {$sql}");
//$order_statistics->replaceData($order_sn, array('mask'=>1), array('mask'=>1));
insert_mark_shipping($ebay_id);
write_log('contrast_intercept_' . date("Ymd") . '/' . date("H") . '.txt', $log_data . "\n\n");
$is_640 = true;
break;
} else {
$skuinfo = get_sku_info($or_sku);
$salensend = getsaleandnosendall($or_sku, $defaultstoreid);
$sql = "UPDATE ebay_sku_statistics SET salensend = {$salensend} WHERE sku = 'or_sku' ";
$dbcon->execute($sql);
$log_data .= "[" . date("Y-m-d H:i:s") . "]\t---{$sql}\n\n";
if ($skuinfo['realnums'] - $salensend < 0) {
$hava_goodscount = false;
break;
}
}
}
if ($hava_goodscount) {
$record_details[] = $orderdetail;
}
}
if ($is_640) {
continue;
}
$count_record_details = count($record_details);
$count_orderdetaillist = count($orderdetaillist);
$final_status = $ebay_status;
if ($count_record_details == 0) {
//更新至自动拦截发货状态
if (!in_array($ebay_carrier, $no_express_delivery)) {
$final_status = 658;
} else {
$final_status = 661;
}
$sql = "UPDATE ebay_order SET ebay_status='{$final_status}' WHERE ebay_id ='{$ebay_id}' ";
$log_data .= "[" . date("Y-m-d H:i:s") . "]\t自动跳转---{$ebay_id}---的状态为---{$final_status}!\n\n";
$dbcon->execute($sql) or die("Fail : {$sql}");
//$order_statistics->replaceData($order_sn, array('mask'=>1), array('mask'=>1));
insert_mark_shipping($ebay_id);
write_log('contrast_intercept_' . date("Ymd") . '/' . date("H") . '.txt', $log_data . "\n\n");
continue;
} else {
if ($count_record_details < $count_orderdetaillist) {
//更新至自动部分发货状态
if (!in_array($ebay_carrier, $no_express_delivery)) {
//$final_status = 659;
$final_status = 640;
//modified by Herman.Xi @ 20130401
} else {
$final_status = 660;
}
$sql = "UPDATE ebay_order SET ebay_status='{$final_status}' WHERE ebay_id ='{$ebay_id}' ";
$log_data .= "[" . date("Y-m-d H:i:s") . "]\t自动跳转---{$ebay_id}---的状态为---{$final_status}!\n\n";
$dbcon->execute($sql) or die("Fail : {$sql}");
//$order_statistics->replaceData($order_sn, array('mask'=>1), array('mask'=>1));
insert_mark_shipping($ebay_id);
write_log('contrast_intercept_' . date("Ymd") . '/' . date("H") . '.txt', $log_data . "\n\n");
continue;
} else {
if ($count_record_details == $count_orderdetaillist) {
//正常发货状态
if (in_array($ebay_account, $SYSTEM_ACCOUNTS['ebay平台'])) {
if ($ebay_note != '') {
$final_status = 593;
} else {
$final_status = 1;
}
} else {
if (in_array($ebay_account, $SYSTEM_ACCOUNTS['海外销售平台'])) {
$final_status = 629;
//德国订单
$sql = "UPDATE ebay_order SET ebay_status='{$final_status}' WHERE ebay_id ='{$ebay_id}' ";
$log_data .= "[" . date("Y-m-d H:i:s") . "]\t自动跳转---{$ebay_id}---的状态为---{$final_status}!\n\n";
$dbcon->execute($sql) or die("Fail : {$sql}");
//$order_statistics->replaceData($order_sn, array('mask'=>1), array('mask'=>1));
write_log('contrast_intercept_' . date("Ymd") . '/' . date("H") . '.txt', $log_data . "\n\n");
continue;
} else {
if (in_array($ebay_account, $SYSTEM_ACCOUNTS['aliexpress']) || in_array($ebay_account, $SYSTEM_ACCOUNTS['B2B外单'])) {
if ($ebay_note != '') {
$final_status = 593;
} else {
$final_status = 595;
}
if (in_array($ebay_countryname, array('Russian Federation', 'Russia')) && strpos($ebay_carrier, '中国邮政') !== false && str_word_count($ebay_username) < 2) {
$final_status = 683;
$sql = "UPDATE ebay_order SET ebay_status='{$final_status}' WHERE ebay_id ='{$ebay_id}' ";
$log_data .= "[" . date("Y-m-d H:i:s") . "]\t自动跳转---{$ebay_id}---的状态为---{$final_status}!\n\n";
$dbcon->execute($sql) or die("Fail : {$sql}");
//$order_statistics->replaceData($order_sn, array('mask'=>1), array('mask'=>1));
write_log('contrast_intercept_' . date("Ymd") . '/' . date("H") . '.txt', $log_data . "\n\n");
continue;
}
} else {
if (in_array($ebay_account, $SYSTEM_ACCOUNTS['DHgate'])) {
$final_status = 620;
} else {
if (in_array($ebay_account, $SYSTEM_ACCOUNTS['dresslink'])) {
$final_status = 1;
} else {
if (in_array($ebay_account, $SYSTEM_ACCOUNTS['cndirect'])) {
$final_status = 1;
} else {
$final_status = 1;
}
}
}
}
}
}
if (judge_contain_combinesku($order_sn)) {
$final_status = 606;
}
if ($totalweight > 2) {
$final_status = 608;
}
if (!in_array($ebay_carrier, $no_express_delivery)) {
if (in_array($ebay_account, $SYSTEM_ACCOUNTS['ebay平台']) || in_array($ebay_account, $SYSTEM_ACCOUNTS['海外销售平台'])) {
$final_status = 641;
//ebay和海外都跳转到 待打印线下和异常订单
} else {
$final_status = 639;
}
}
$sql = "UPDATE ebay_order SET ebay_status='{$final_status}' WHERE ebay_id ='{$ebay_id}' ";
$log_data .= "[" . date("Y-m-d H:i:s") . "]\t自动跳转---{$ebay_id}---的状态为---{$final_status}!\n\n";
$dbcon->execute($sql) or die("Fail : {$sql}");
//$order_statistics->replaceData($order_sn, array('mask'=>1), array('mask'=>1));
write_log('contrast_intercept_' . date("Ymd") . '/' . date("H") . '.txt', $log_data . "\n\n");
} else {
$log_data .= "[" . date("Y-m-d H:i:s") . "]\t订单{$ebay_id同步状态有误，请联系IT解决！}";
}
}
}
}
if (!empty($log_data)) {
write_log('contrast_intercept_' . date("Ymd") . '/' . date("H") . '.txt', $log_data . "\n\n");
}
}