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