public function act_abroadSale()
{
$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 = new PHPExcel();
$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']);
$account = $_REQUEST['account'];
$accountStr = '';
if ($account != '') {
$account = explode("#", $account);
foreach ($account as $value) {
if ($value != '') {
$accountStr .= " b.account='" . $value . "' or ";
}
}
}
$accountStr = substr($accountStr, 0, strlen($accountStr) - 3);
$packinglists = GoodsModel::getMaterInfo();
foreach ($packinglists as $packinglist) {
$packings[$packinglist['pmName']] = $packinglist['pmCost'];
}
unset($packinglists);
$shipOrderList = OrderInfoModel::getShipOrderList($start, $end, $accountStr);
$a = 2;
foreach ($shipOrderList as $key => $shipOrder) {
$ebay_id = $shipOrder['id'];
$paidtime = @date('Y-m-d', $shipOrder['paymentTime']);
$ebay_usermail = $shipOrder['email'];
$ebay_userid = $shipOrder['platformUsername'];
$name = @html_entity_decode($shipOrder['username'], ENT_QUOTES, 'UTF-8');
$street1 = @$shipOrder['street'];
$street2 = @$shipOrder['address2'];
$city = $shipOrder['city'];
$state = $shipOrder['state'];
$countryname = $shipOrder['countryname'];
//客服部小霞提出 导出列 国家 显示英文 方便退款处理
//$cnname = $country[$countryname];
$cnname = $countryname;
$zip = $shipOrder['zipCode'];
$tel = $shipOrder['landline'];
$ebay_shipfee = $shipOrder['actualShipping'];
$ebay_note = $shipOrder['feedback'];
$ebay_total = @round($shipOrder['actualTotal'], 2);
$ebay_tracknumber = @$shipOrder['tracknumber'];
$ebay_account = @$shipOrder['account'];
$recordnumber0 = @$shipOrder['recordNumber'];
$ebay_phone = $shipOrder['phone'];
$ebay_currency = $shipOrder['currency'];
$packersId = $shipOrder['packersId'];
$packinguser = $staffLists[$packersId];
$ordershipfee = $shipOrder['calcShipping'];
$ebay_ptid = $shipOrder['PayPalPaymentId'];
//transId
$ebay_pp = $shipOrder['PayPalEmailAddress'];
$isCopy = $shipOrder['isCopy'];
$isCopy = $isCopy == '0' ? '' : ($isCopy == '1' ? '被复制订单' : '复制订单');
$isBuji = $shipOrder['isBuji'];
$is_sendreplacement = $isBuji;
$isSplit = $shipOrder['isSplit'];
$transportId = $shipOrder['transportId'];
$ebay_carrier = $carriers[$transportId];
$address = $street1 . "\n" . $street2 . "\n" . $city . "\n" . $state . "\n" . $zip . "\n" . $countryname;
$scantime = date('Y-m-d', $shipOrder['weighTime']);
$calculate_weight = $shipOrder['calcWeight'];
//计算重量
$orderweight2 = number_format($shipOrder['actualWeight'] / 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);*/
$all_orderweight = "";
$ordershipfee = "";
$ordershipfee = "";
$ebay_noteb = $isCopy;
$ebay_splitorder = $isSplit == '0' ? '' : ($isSplit == '1' ? '被拆分订单' : '拆分产生新订单');
$ebay_combineorder = CommonModel::judge_contain_combinesku($ebay_id);
$splitorder_log = "";
$ebay_splitorder_log = '';
if (!empty($splitorder_log)) {
$ebay_splitorder_log = $ebay_splitorder_logs[$splitorder_log];
}
$is_sendreplacement = isset($sendreplacement[$is_sendreplacement]) ? $sendreplacement[$is_sendreplacement] : '';
$shipOrderDetailList = OrderInfoModel::getShipOrderDetailByOrderId($ebay_id);
/*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];
}*/
$mailway_c = '';
if (count($shipOrderDetailList) == 1) {
$sku = $shipOrderDetailList[0]['sku'];
//$ebay_itemid = $shipOrderDetailList[0]['ebay_itemid'];
$amount = intval($shipOrderDetailList[0]['amount']);
$recordnumber = $shipOrderDetailList[0]['recordNumber'];
$ebay_itemprice = round($shipOrderDetailList[0]['itemPrice'], 2) * $amount;
$ebay_shipfee = round_num($ebay_total - $ebay_itemprice, 2);
$skus = GoodsModel::get_realskuinfo($sku);
$values_skus = array_values($skus);
$goods_location = CommonModel::getPositionBySku($sku);
$goodsInfo = GoodsModel::getSkuinfo($sku);
$goods_cost = isset($goodsInfo['goodsCost']) ? round($goodsInfo['goodsCost'], 2) : 0;
$pmId = isset($sq3['pmId']) ? $sq3['pmId'] : '';
$ebay_packingmaterial = $packings[$pmId]['pmName'];
$ebay_packingCost = $packings[$pmId]['pmCost'];
$purchaseId = isset($goodsInfo['purchaseId']) ? $goodsInfo['purchaseId'] : '';
$cguser = $staffLists[$purchaseId];
$combineSku = '';
//GoodsModel::getCombineBySku($sku);
$is_combineSku = count($combineSku);
if ($is_combineSku > 0) {
//为组合订单
$goods_costs = 0;
$combine_weight_list = array();
$goods_costs_list = array();
foreach ($skus as $k => $v) {
$goodsInfo2 = GoodsModel::getSkuinfo($k);
$combine_weight_list[$k] = $amount * $v * $goodsInfo2['goodsWeight'];
//组合订单重量数组
$goods_costs_list[$k] = $amount * $v * $goodsInfo2['goodsCost'];
//货本数组
$goods_costs += $amount * $v * $goodsInfo2['goodsCost'];
}
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $a, $scantime);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $a, $ebay_account);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $a, $recordnumber0);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $a, $ebay_userid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $a, '无');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $a, '无');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $a, $amount * array_sum($values_skus));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $a, $cnname);
//国家
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $a, $ebay_itemprice);
//产品价格
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $a, $ebay_shipfee);
//运费
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . $a, $is_main_order == 2 ? 0 : $ebay_total);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . $a, $ebay_currency);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . $a, $packinguser);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . $a, $ebay_tracknumber);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . $a, validate_trackingnumber($ebay_tracknumber) ? '是' : '否');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . $a, $orderweight2);
//实际重量
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . $a, $ordershipfee);
//实际运费
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . $a, $ebay_carrier);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . $a, $ebay_id);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . $a, $goods_costs);
//产品成本
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . $a, $ebay_ptid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . $a, '无');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . $a, $ebay_noteb);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . $a, $is_sendreplacement);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . $a, $ebay_splitorder);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . $a, $ebay_combineorder);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . $a, $mailway_c);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . $a, $ebay_splitorder_log);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . $a, $ebay_pp);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF' . $a, '');
$a++;
foreach ($skus as $k => $v) {
$goods_location = CommonModel::getPositionBySku($k);
$goodsInfo3 = GoodsModel::getSkuinfo($k);
$goods_cost = isset($goodsInfo3['goodsCost']) ? round($goodsInfo3['goodsCost'], 2) : 0;
$pmId = isset($goodsInfo3['pmId']) ? $goodsInfo3['pmId'] : '';
$ebay_packingmaterial = $packings[$pmId]['pmName'];
$ebay_packingCost = $packings[$pmId]['pmCost'];
$purchaseId = isset($goodsInfo3[0]['purchaseId']) ? $goodsInfo3[0]['purchaseId'] : '';
$cguser = $staffLists[$purchaseId];
$ishipfee = round_num($goods_costs_list[$k] / array_sum($goods_costs_list) * $ebay_shipfee, 2);
//根据货本比ebay运费
$iorderweight2 = round($combine_weight_list[$k] / array_sum($combine_weight_list) * $orderweight2, 3);
$iordershipfee = round_num($combine_weight_list[$k] / array_sum($combine_weight_list) * $ordershipfee, 2);
$iprice = round_num(($goods_costs_list[$k] + $iordershipfee) / (array_sum($goods_costs_list) + $ordershipfee) * $ebay_itemprice, 2);
//根据货本比产品价格 last modified by herman.xi @20130916
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $a, $scantime);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $a, $ebay_account);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $a, $recordnumber);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $a, $ebay_userid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $a, $goods_location);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $a, $k);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $a, $amount * $v);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $a, $cnname);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $a, $iprice);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $a, $ishipfee);
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$a, '');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . $a, $ebay_currency);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . $a, $packinguser);
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N'.$a, $ebay_tracknumber);
//$objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$a, $ebay_tracknumber, PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->getActiveSheet()->getStyle('N'.$a)->getNumberFormat()->setFormatCode("@");
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('O'.$a, validate_trackingnumber($ebay_tracknumber) ? '是' : '否');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . $a, $iorderweight2);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . $a, $iordershipfee);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . $a, $ebay_carrier);
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('S'.$a, $ebay_id);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . $a, $goods_cost * $amount * $v);
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('U'.$a, $ebay_ptid);
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V'.$a, $ebay_itemid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . $a, $ebay_noteb);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . $a, $is_sendreplacement);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . $a, $ebay_splitorder);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . $a, $ebay_packingmaterial);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA' . $a, $packings[$ebay_packingmaterial]);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . $a, $sku);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . $a, $mailway_c);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . $a, $ebay_splitorder_log);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . $a, $ebay_pp);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF' . $a, $cguser);
$a++;
}
} else {
//非组合订单
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $a, $scantime);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $a, $ebay_account);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $a, $recordnumber0);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $a, $ebay_userid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $a, $goods_location);
//仓位号
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $a, $sku);
//料号
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $a, $amount);
//数量
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $a, $cnname);
//国家
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $a, $ebay_itemprice);
//产品价格
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $a, $ebay_shipfee);
//ebay运费
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . $a, $ebay_total);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . $a, $ebay_currency);
//币种
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . $a, $packinguser);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . $a, $ebay_tracknumber);
//$objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$a, $ebay_tracknumber, PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->getActiveSheet()->getStyle('N'.$a)->getNumberFormat()->setFormatCode("@");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . $a, validate_trackingnumber($ebay_tracknumber) ? '是' : '否');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . $a, $orderweight2);
//实际重量
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . $a, $ordershipfee);
//实际运费
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . $a, $ebay_carrier);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . $a, $ebay_id);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . $a, $goods_cost * $amount);
//产品成本
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . $a, $ebay_ptid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . $a, $ebay_itemid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . $a, $ebay_noteb);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . $a, $is_sendreplacement);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . $a, $ebay_splitorder);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . $a, $ebay_packingmaterial);
//包材
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA' . $a, $packings[$ebay_packingmaterial]);
//包材信息
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . $a, '');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . $a, $mailway_c);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . $a, $ebay_splitorder_log);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . $a, $ebay_pp);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF' . $a, $cguser);
$a++;
}
unset($combine_weight_list);
unset($goods_costs_list);
} else {
$cctotal = 0;
$ebay_itemprice = 0;
$goods_costs = 0;
$goods_list = array();
$goods_lists = array();
$goods_weight_list = array();
$goods_costs_list = array();
$calculate_weight = 0;
foreach ($shipOrderDetailList as $detail_array) {
$detail_id = $detail_array['id'];
$sku = $detail_array['sku'];
$skus = GoodsModel::get_realskuinfo($sku);
$_ebay_itemprice = round($detail_array['itemPrice'], 2);
$ebay_itemprice += $detail_array['amount'] * $_ebay_itemprice;
foreach ($skus as $k => $v) {
$goodsInfo3 = GoodsModel::getSkuinfo($k);
$_ebay_amount = intval($detail_array['amount'] * $v);
$cctotal += $_ebay_amount;
$calculate_weight += $_ebay_amount * $goodsInfo3['goodsWeight'];
$goods_weight_list[$detail_id . $sku][$k] = $_ebay_amount * $goodsInfo3['goodsWeight'];
$goods_costs_list[$detail_id . $sku][$k] = round($goodsInfo3['goodsCost'], 2) * $_ebay_amount;
$goods_costs += round($goodsInfo3['goodsCost'], 2) * $_ebay_amount;
}
}
$ebay_shipfee = round_num($ebay_total - $ebay_itemprice, 2);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $a, $scantime);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $a, $ebay_account);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $a, $recordnumber0);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $a, $ebay_userid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $a, '无');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $a, '无');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $a, $cctotal);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $a, $cnname);
//国家
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $a, $ebay_itemprice);
//产品价格
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $a, $ebay_shipfee);
//运费
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . $a, $is_main_order == 2 ? 0 : $ebay_total);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . $a, $ebay_currency);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . $a, $packinguser);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . $a, $ebay_tracknumber);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . $a, validate_trackingnumber($ebay_tracknumber) ? '是' : '否');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . $a, $orderweight2);
//实际重量
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . $a, $ordershipfee);
//实际运费
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . $a, $ebay_carrier);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . $a, $ebay_id);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . $a, $goods_costs);
//产品成本
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . $a, $ebay_ptid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . $a, '无');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . $a, $ebay_noteb);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . $a, $is_sendreplacement);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . $a, $ebay_splitorder);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . $a, $ebay_combineorder);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . $a, $mailway_c);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . $a, $ebay_splitorder_log);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . $a, $ebay_pp);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF' . $a, '');
$a++;
foreach ($shipOrderDetailList as $detail_array) {
$detail_id = $detail_array['id'];
$sku = $detail_array['sku'];
$recordnumber = $detail_array['recordNumber'];
//$ebay_itemid = $detail_array['ebay_itemid'];
$amount = intval($detail_array['amount']);
$dshipingfee = $detail_array['shippingFee'];
$debay_itemprice = round($detail_array['itemPrice'], 2);
$goods_location = CommonModel::getPositionBySku($sku);
$goodsInfo3 = GoodsModel::getSkuinfo($k);
$goods_cost = isset($goodsInfo3['goodsCost']) ? round($goodsInfo3['goodsCost'], 2) : 0;
$pmId = isset($goodsInfo3['pmId']) ? $goodsInfo3['pmId'] : 0;
$ebay_packingmaterial = $packings[$pmId]['pmName'];
$ebay_packingCost = $packings[$pmId]['pmCost'];
$purchaseId = isset($goodsInfo3['purchaseId']) ? $goodsInfo3['purchaseId'] : '';
$cguser = $staffLists[$purchaseId];
$dordershipfee = round($ordershipfee * (array_sum($goods_weight_list[$detail_id . $sku]) / $calculate_weight), 2);
$dorderweight2 = round($orderweight2 * (array_sum($goods_weight_list[$detail_id . $sku]) / $calculate_weight), 3);
$combineSku = '';
//GoodsModel::getCombineBySku($sku);
$is_combineSku = count($combineSku);
if ($is_combineSku > 0) {
//为组合订单
$skus = GoodsModel::get_realskuinfo($sku);
foreach ($skus as $k => $v) {
$goods_location = CommonModel::getPositionBySku($sku);
$goodsInfo3 = GoodsModel::getSkuinfo($k);
$goods_cost = isset($goodsInfo3['goodsCost']) ? round($goodsInfo3['goodsCost'], 2) : 0;
$pmId = isset($sq3['pmId']) ? $sq3['pmId'] : '';
$ebay_packingmaterial = $packings[$pmId]['pmName'];
$ebay_packingCost = $packings[$pmId]['pmCost'];
$purchaseId = isset($goodsInfo3['purchaseId']) ? $goodsInfo3['purchaseId'] : '';
$cguser = $staffLists[$purchaseId];
//$iprice = round_num(($goods_costs_list[$detail_id.$sku][$k]/array_sum($goods_costs_list[$detail_id.$sku])) * $debay_itemprice * $amount,2); //根据货本比产品价格
$ishipfee = round_num($goods_costs_list[$detail_id . $sku][$k] / array_sum($goods_costs_list[$detail_id . $sku]) * $dshipingfee, 2);
//根据货本比ebay运费
$iorderweight2 = round($goods_weight_list[$detail_id . $sku][$k] / array_sum($goods_weight_list[$detail_id . $sku]) * $dorderweight2, 3);
$iordershipfee = round_num($goods_weight_list[$detail_id . $sku][$k] / array_sum($goods_weight_list[$detail_id . $sku]) * $dordershipfee, 2);
$iprice = round_num(($goods_costs_list[$detail_id . $sku][$k] + $iordershipfee) / (array_sum($goods_costs_list[$detail_id . $sku]) + $dordershipfee) * $debay_itemprice * $amount, 2);
//根据货本比产品价格 last modified by herman.xi @20130916
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $a, $scantime);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $a, $ebay_account);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $a, $recordnumber);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $a, $ebay_userid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $a, $goods_location);
//仓位号
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $a, $k);
//单料号
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $a, $v * $amount);
//数量
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $a, $cnname);
//国家名称
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $a, $iprice);
//料号产品价格
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $a, $ishipfee);
//料号ebay运费
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . $a, $ebay_currency);
//币种
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . $a, $packinguser);
//包装人
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . $a, $ebay_carrier);
//运输方式
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . $a, $goods_cost * $v * $amount);
//产品成本
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . $a, $iorderweight2);
//料号实际重量
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . $a, $iordershipfee);
//料号实际运费
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V'.$a, $ebay_itemid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . $a, $ebay_noteb);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . $a, $is_sendreplacement);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . $a, $ebay_splitorder);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . $a, $ebay_packingmaterial);
//包材
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA' . $a, $packings[$ebay_packingmaterial]);
//包材价值
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . $a, $sku);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . $a, $mailway_c);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . $a, $ebay_splitorder_log);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . $a, $ebay_pp);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF' . $a, $cguser);
$a++;
}
} else {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $a, $scantime);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $a, $ebay_account);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $a, $recordnumber);
//明细记录
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $a, $ebay_userid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $a, $goods_location);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $a, $sku);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $a, $amount);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $a, $cnname);
//国家
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $a, $debay_itemprice * $amount);
//明细产品价格
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $a, $dshipingfee);
//明细ebay运费
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . $a, $ebay_currency);
//币种
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . $a, $packinguser);
//包装人
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . $a, $dorderweight2);
//实际重量
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . $a, $dordershipfee);
//实际运费
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . $a, $ebay_carrier);
//运输方式
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . $a, $goods_cost * $amount);
//产品成本
//$objPHPExcel->setActiveSheetIndex(0)->setCellValue('U'.$a, $ebay_ptid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . $a, $ebay_itemid);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . $a, $ebay_noteb);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . $a, $is_sendreplacement);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . $a, $ebay_splitorder);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . $a, $ebay_packingmaterial);
//包材
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA' . $a, $packings[$ebay_packingmaterial]);
//包材信息
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . $a, '');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . $a, $mailway_c);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . $a, $ebay_splitorder_log);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . $a, $ebay_pp);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF' . $a, $cguser);
$a++;
}
}
unset($goods_weight_list);
unset($goods_costs_list);
}
}
$objPHPExcel->getActiveSheet(0)->getStyle('A1:AB' . ($a - 1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(14);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(25);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('K')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('L')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('M')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('N')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('O')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('P')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('Q')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('R')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('S')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('T')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('U')->setWidth(25);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('V')->setWidth(30);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('W')->setWidth(25);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('X')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('Y')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('AA')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('AB')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('AC')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('AD')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('AE')->setWidth(30);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('AF')->setWidth(15);
$objPHPExcel->getActiveSheet(0)->getStyle('A1:AF' . ($a - 1))->getAlignment()->setWrapText(true);
$title = "abroad_sale_" . date('Y-m-d', $end);
$titlename = "abroad_sale_" . date('Y-m-d', $end) . ".xls";
$objPHPExcel->getActiveSheet()->setTitle($title);
$objPHPExcel->setActiveSheetIndex(0);
//exit;
//Redirect output to a client's web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename={$titlename}");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
public function act_getExpressShipOrderInfo()
{
$recordnumber = !empty($_GET["recordnumber"]) ? addslashes(trim($_GET["recordnumber"])) : '';
$selleraccount = !empty($_GET["selleraccount"]) ? addslashes(trim($_GET["selleraccount"])) : '';
//$selleraccount='sunwebzone';
//$recordnumber='537995';
if (empty($recordnumber)) {
$data['errCode'] = '5806';
$data['errMsg'] = 'recordnumber is null.';
exit(json_encode($data));
}
if (empty($selleraccount)) {
$data['errCode'] = '5806';
$data['errMsg'] = 'selleraccount is null.';
exit(json_encode($data));
}
$field = "a.*";
$condition = "ORDER BY a.id DESC LIMIT 50";
$orders = OrderInfoModel::getExpressOrderInfo($selleraccount, $recordnumber, $field, $condition = '');
if (empty($orders)) {
$data['errCode'] = '5806';
$data['errMsg'] = 'No data or get a order information error';
exit(json_encode($data));
}
foreach ($orders as &$order) {
if ($order['orderStatus'] == 2) {
$order['catename'] = '已发货';
} else {
$select = 'statusName';
$where = " WHERE groupId = '{$order['orderStatus']}' AND statusCode = '{$order['orderType']}' LIMIT 1 ";
$cates = StatusMenuModel::getStatusMenuList($select, $where);
$order['catename'] = $cates[0]['statusName'];
}
$order['orderdetail'] = OrderInfoModel::getShipOrderDetailByOrderId($order['id']);
}
$data['data'] = $orders;
exit(json_encode($data));
}
