/** * 订单下载 * * @param $f3 */ public function Download($f3) { // 权限检查 $this->requirePrivilege('manage_stat_order_refer_download'); $errorMessage = ''; if (!$f3->get('GET')) { // 没有任何查询,直接显示空页面 $errorMessage = '查询参数不能为空'; goto out_fail; } // 做数据查询 $orderGoodsArray = $this->doQuery($f3); // 没有数据,不需要计算 if (empty($orderGoodsArray)) { $errorMessage = '没有订单可以下载'; goto out_fail; } // 转换显示格式 foreach ($orderGoodsArray as &$orderGoodsItem) { $orderGoodsItem['add_time'] = Time::gmTimeToLocalTimeStr($orderGoodsItem['add_time'], 'Y-m-d H:i:s'); $orderGoodsItem['pay_time'] = Time::gmTimeToLocalTimeStr($orderGoodsItem['pay_time'], 'Y-m-d H:i:s'); $orderGoodsItem['order_goods_status_desc'] = OrderGoodsService::$orderGoodsStatusDesc[$orderGoodsItem['order_goods_status']]; } unset($orderGoodsItem); // 生成 Excel 并且提供下载 require_once PROTECTED_PATH . '/Vendor/PHPExcel/Settings.php'; // 设置Excel缓存,防止数据太多拖死了程序 \PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp); // 导出为 Excel 格式 $objPHPExcel = new \PHPExcel(); // 设置工作 sheet $objPHPExcel->setActiveSheetIndex(0); $activeSheet = $objPHPExcel->getActiveSheet(); // 输出数据 $rowIndex = 1; $lastOrderId = 0; $orderGoodsArraySize = count($orderGoodsArray); // 输出表头 $this->outputHeaderRow($activeSheet, $rowIndex); $rowIndex++; // 用于订单计算的 order_goods $calOrderGoodsArray = array(); for ($orderGoodsIndex = 0; $orderGoodsIndex < $orderGoodsArraySize; $orderGoodsIndex++) { // 取得这行数据 $orderGoodsItem = $orderGoodsArray[$orderGoodsIndex]; if ($lastOrderId != $orderGoodsItem['order_id']) { // 计算整个订单的 CPS 费用 if (!empty($calOrderGoodsArray)) { $tmpIndex = 0; $orderStatResult = $this->calculateOrder($lastOrderId, $tmpIndex, $calOrderGoodsArray); $orderStatResult['utm_source'] = '订单CPS总计-->'; unset($orderStatResult['utm_medium']); // 输出订单的计算结果 $this->outputDataRow($activeSheet, $rowIndex, $orderStatResult); $rowIndex += 2; // 留出一个空行 // 释放内存 unset($calOrderGoodsArray); unset($orderStatResult); $calOrderGoodsArray = array(); } // 更新订单 id $lastOrderId = $orderGoodsItem['order_id']; } // 加入需要计算的 order_goods 记录 $calOrderGoodsArray[] = $orderGoodsItem; // 输出 order_goods 行 $this->outputDataRow($activeSheet, $rowIndex, $orderGoodsItem); // 数据换行 $rowIndex++; } // 最后一次计算整个订单的 CPS 费用 if (!empty($calOrderGoodsArray)) { $tmpIndex = 0; $orderStatResult = $this->calculateOrder($lastOrderId, $tmpIndex, $calOrderGoodsArray); $orderStatResult['utm_source'] = '订单CPS总计-->'; unset($orderStatResult['utm_medium']); // 输出订单的计算结果 $this->outputDataRow($activeSheet, $rowIndex, $orderStatResult); $rowIndex += 2; // 留出一个空行 // 释放内存 unset($calOrderGoodsArray); unset($orderStatResult); } // 输出为 Excel5 格式 $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); $fileName = '来源渠道订单下载'; header('Content-Type: application/vnd.ms-excel'); if (strpos($_SERVER['HTTP_USER_AGENT'], "MSIE")) { header('Content-Disposition: attachment; filename="' . urlencode($fileName) . '.xls"'); } else { header('Content-Disposition: attachment; filename="' . $fileName . '.xls"'); } header('Cache-Control: max-age=0'); $objWriter->save('php://output'); //输出到浏览器 die; out_fail: echo $errorMessage; }
/** * 系统使用的是 GM 时间,这个方法用于转换为 Local Time,并且显示 */ function smarty_helper_modifier_localtime($gmTime, $format = null) { return Time::gmTimeToLocalTimeStr($gmTime, $format); }
/** * 下载 拣货单 * * @param $f3 * @param $validator */ public function downloadJianHuo($f3, $validator) { $outputColumnArray = array('warehouse' => '仓库', 'shelf' => '货架', 'goods_sn' => '货号', 'goods_name' => '商品名', 'goods_attr' => '属性规格', 'goods_number' => '数量', 'suppliers_price' => '供货单价', 'total_suppliers_price' => '供货总价', 'total_suppliers_shipping_fee' => '供货快递'); $outputColumnMoneyArray = array('suppliers_price', 'total_suppliers_price', 'total_suppliers_shipping_fee'); //表单查询 $searchFormQuery = array(); $searchFormQuery['og.goods_id'] = $validator->digits()->min(1)->filter('ValidatorIntValue')->validate('goods_id'); //付款时间 $payTimeStartStr = $validator->validate('pay_time_start'); $payTimeStart = Time::gmStrToTime($payTimeStartStr) ?: null; $payTimeEndStr = $validator->validate('pay_time_end'); $payTimeEnd = Time::gmStrToTime($payTimeEndStr) ?: null; $searchFormQuery['oi.pay_time'] = array($payTimeStart, $payTimeEnd); // 快递信息 $expressType = $validator->digits()->min(0)->filter('ValidatorIntValue')->validate('expressType'); switch ($expressType) { case 1: $searchFormQuery['og.shipping_id'] = 0; break; case 2: $searchFormQuery['og.shipping_id'] = array('>', 0); break; default: break; } if (!$this->validate($validator)) { goto out_fail; } if (Utils::isBlank($searchFormQuery['og.goods_id']) && Utils::isBlank($payTimeStart)) { $this->addFlashMessage('查询参数非法'); goto out_fail; } // 构造查询条件 $authSupplierUser = AuthHelper::getAuthUser(); $searchFormQuery['og.suppliers_id'] = $authSupplierUser['suppliers_id']; $searchParamArray = array(); $searchParamArray[] = array('oi.order_id = og.order_id'); //供货商,只查看有效订单,其它订单不显示 $searchParamArray[] = array('og.order_goods_status > 0'); // 表单查询 $searchParamArray = array_merge($searchParamArray, QueryBuilder::buildSearchParamArray($searchFormQuery)); $orderGoodsArray = SearchHelper::search(SearchHelper::Module_OrderGoodsOrderInfo, 'og.warehouse, og.shelf, og.goods_id, og.goods_sn, og.goods_attr, sum(og.goods_number) as goods_number, sum(og.suppliers_price * og.goods_number) as total_suppliers_price, sum(og.suppliers_shipping_fee) as total_suppliers_shipping_fee', $searchParamArray, array(array('og.warehouse', 'asc'), array('og.shelf', 'asc')), 0, $f3->get('sysConfig[max_query_record_count]'), 'og.warehouse, og.shelf, og.goods_id, og.goods_sn, og.goods_attr'); // 没有数据,退出 if (empty($orderGoodsArray)) { goto out; } // 查询订单对应的商品 $goodsIdArray = array(); foreach ($orderGoodsArray as $orderGoodsItem) { $goodsIdArray[] = $orderGoodsItem['goods_id']; } $goodsIdArray = array_unique($goodsIdArray); $goodsArray = SearchHelper::search(SearchHelper::Module_Goods, 'goods_id, goods_name_short, suppliers_price', array(array(QueryBuilder::buildInCondition('goods_id', $goodsIdArray, \PDO::PARAM_INT))), null, 0, $f3->get('sysConfig[max_query_record_count]')); $goodsIdToGoodsMap = array(); foreach ($goodsArray as $goodsItem) { $goodsIdToGoodsMap[$goodsItem['goods_id']] = $goodsItem; } require_once PROTECTED_PATH . '/Vendor/PHPExcel/Settings.php'; // 设置Excel缓存,防止数据太多拖死了程序 \PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp); // 导出为 Excel 格式 $objPHPExcel = new \PHPExcel(); // 设置工作 sheet $objPHPExcel->setActiveSheetIndex(0); $activeSheet = $objPHPExcel->getActiveSheet(); // 格式化数据 $rowIndex = 1; $lastWarehouseShelf = null; $orderGoodsArraySize = count($orderGoodsArray); // 输出头部信息 $colIndex = 1; foreach ($outputColumnArray as $value) { $activeSheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $value); $activeSheet->getStyleByColumnAndRow($colIndex, $rowIndex)->getFont()->setBold(true); $activeSheet->getStyleByColumnAndRow($colIndex, $rowIndex)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID); $activeSheet->getStyleByColumnAndRow($colIndex, $rowIndex)->getFill()->getStartColor()->setARGB('FFB0B0B0'); $colIndex++; } $rowIndex++; // 换行 for ($orderGoodsIndex = 0; $orderGoodsIndex < $orderGoodsArraySize; $orderGoodsIndex++) { // 取得这行数据 $orderGoodsItem = $orderGoodsArray[$orderGoodsIndex]; // 填入商品数据 $orderGoodsItem['goods_name'] = $goodsIdToGoodsMap[$orderGoodsItem['goods_id']]['goods_name_short']; $orderGoodsItem['suppliers_price'] = $goodsIdToGoodsMap[$orderGoodsItem['goods_id']]['suppliers_price']; if ($lastWarehouseShelf != $orderGoodsItem['warehouse'] . '$' . $orderGoodsItem['shelf']) { $lastWarehouseShelf = $orderGoodsItem['warehouse'] . '$' . $orderGoodsItem['shelf']; // 不同的取货地点,需要特殊处理 $rowIndex += 2; // 跳过 2 行 } // 输出一行数据 $colIndex = 1; foreach ($outputColumnArray as $key => $value) { $cellValue = isset($orderGoodsItem[$key]) ? $orderGoodsItem[$key] : ''; if (!in_array($key, $outputColumnMoneyArray)) { $activeSheet->getCellByColumnAndRow($colIndex, $rowIndex)->setDataType(\PHPExcel_Cell_DataType::TYPE_STRING); } else { // 转换价格显示 $cellValue = Money::toSmartyDisplay($cellValue); } $activeSheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $cellValue); $colIndex++; } $rowIndex++; // 换行 } $fileName = '拣货单_' . $searchFormQuery['og.goods_id'] . '_' . Time::gmTimeToLocalTimeStr($payTimeStart, 'Y-m-d_H-i-s') . '__' . Time::gmTimeToLocalTimeStr($payTimeEnd, 'Y-m-d_H-i-s'); // 输出为 Excel5 格式 $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); header('Content-Type: application/vnd.ms-excel'); if (strpos($_SERVER['HTTP_USER_AGENT'], "MSIE")) { header('Content-Disposition: attachment; filename="' . urlencode($fileName) . '.xls"'); } else { header('Content-Disposition: attachment; filename="' . $fileName . '.xls"'); } header('Cache-Control: max-age=0'); $objWriter->save('php://output'); //输出到浏览器 die; out: echo "没有数据"; die; out_fail: // 失败,打印错误消息 $flashMessageArray = $this->flashMessageArray; foreach ($flashMessageArray as $flashMessage) { echo $flashMessage . '<br />'; } }
/** * * 批量下载订单 * */ public function Download($f3) { // 权限检查 $this->requirePrivilege('manage_order_excel_download'); // 参数验证 $validator = new Validator($f3->get('GET')); //表单查询 $formQuery = array(); $formQuery['suppliers_id'] = $validator->digits()->min(1)->filter('ValidatorIntValue')->validate('suppliers_id'); $formQuery['goods_id'] = $validator->digits()->min(1)->filter('ValidatorIntValue')->validate('goods_id'); if (empty($formQuery['suppliers_id']) && empty($formQuery['goods_id'])) { $this->addFlashMessage('供货商ID 和 商品ID 不能同时为空'); goto out_fail; } //付款时间 $payTimeStartStr = $validator->validate('pay_time_start'); $payTimeStart = Time::gmStrToTime($payTimeStartStr) ?: null; $payTimeEndStr = $validator->validate('pay_time_end'); $payTimeEnd = Time::gmStrToTime($payTimeEndStr) ?: null; $formQuery['pay_time'] = array($payTimeStart, $payTimeEnd); //额外退款,时间 $extraRefundTimeStartStr = $validator->validate('extra_refund_time_start'); $extraRefundTimeStart = Time::gmStrToTime($extraRefundTimeStartStr) ?: null; $extraRefundTimeEndStr = $validator->validate('extra_refund_time_end'); $extraRefundTimeEnd = Time::gmStrToTime($extraRefundTimeEndStr) ?: null; $formQuery['extra_refund_time'] = array($extraRefundTimeStart, $extraRefundTimeEnd); if (!$this->validate($validator)) { goto out_fail; } if (Utils::isBlank($formQuery['suppliers_id']) || (Utils::isBlank($payTimeStart) || Utils::isBlank($payTimeEnd)) && (Utils::isBlank($extraRefundTimeStart) || Utils::isBlank($extraRefundTimeEnd))) { $this->addFlashMessage('按照供货商下载订单必须提供时间段参数'); goto out_fail; } $condArray = array(); $condArray[] = array('oi.order_id = og.order_id'); $condArray[] = array('order_goods_status > 0'); // 表单查询 $condArray = array_merge($condArray, QueryBuilder::buildQueryCondArray($formQuery)); $orderGoodsService = new OrderGoodsService(); $orderGoodsArray = $orderGoodsService->_fetchArray(array('order_info' => 'oi', 'order_goods' => 'og'), 'og.*, oi.add_time, oi.pay_time, oi.consignee, oi.mobile, oi.tel, oi.zipcode, oi.postscript, oi.address', $condArray, array('order' => 'oi.order_id asc, og.rec_id asc, og.goods_id asc'), 0, $f3->get('sysConfig[max_query_record_count]'), 0); //最多限制 max_query_record_count 条记录 // 转换显示格式 foreach ($orderGoodsArray as &$orderGoodsItem) { $orderGoodsItem['add_time'] = Time::gmTimeToLocalTimeStr($orderGoodsItem['add_time'], 'Y-m-d H:i:s'); $orderGoodsItem['pay_time'] = Time::gmTimeToLocalTimeStr($orderGoodsItem['pay_time'], 'Y-m-d H:i:s'); $orderGoodsItem['extra_refund_time'] = Time::gmTimeToLocalTimeStr($orderGoodsItem['extra_refund_time'], 'Y-m-d H:i:s'); $orderGoodsItem['suppliers_total_price'] = $orderGoodsItem['suppliers_price'] * $orderGoodsItem['goods_number']; $orderGoodsItem['order_goods_status_desc'] = OrderGoodsService::$orderGoodsStatusDesc[$orderGoodsItem['order_goods_status']]; } unset($orderGoodsItem); require_once PROTECTED_PATH . '/Vendor/PHPExcel/Settings.php'; // 设置Excel缓存,防止数据太多拖死了程序 \PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp); // 导出为 Excel 格式 $objPHPExcel = new \PHPExcel(); // 设置工作 sheet $objPHPExcel->setActiveSheetIndex(0); $activeSheet = $objPHPExcel->getActiveSheet(); // 取得快递公司列表 $expressService = new ExpressService(); $expressArray = $expressService->fetchExpressArray(); //输出快递公司参考表 $this->outputExpressArray($activeSheet, $expressArray); //释放内存 unset($expressArray); unset($expressService); // 格式化数据 $rowIndex = 1; $lastOrderId = 0; $orderGoodsArraySize = count($orderGoodsArray); // 输出头部信息 $this->outputHeaderRow($activeSheet, $rowIndex); $rowIndex++; // 换行 for ($orderGoodsIndex = 0; $orderGoodsIndex < $orderGoodsArraySize; $orderGoodsIndex++) { // 取得这行数据 $orderGoodsItem = $orderGoodsArray[$orderGoodsIndex]; if ($lastOrderId != $orderGoodsItem['order_id']) { $lastOrderId = $orderGoodsItem['order_id']; // 不同的订单,需要特殊处理 $rowIndex += 2; // 跳过 2 行 } // 输出数据 //$this->outputDataRow($activeSheet, $rowIndex, $orderGoodsItem, array('goods_name', 'goods_id')); $this->outputDataRow($activeSheet, $rowIndex, $orderGoodsItem); $rowIndex++; // 换行 } $fileName = '订单下载_' . $formQuery['goods_id'] . '_' . Time::gmTimeToLocalTimeStr($payTimeStart, 'Y-m-d_H-i-s') . '__' . Time::gmTimeToLocalTimeStr($payTimeEnd, 'Y-m-d_H-i-s'); // 输出为 Excel5 格式 $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); header('Content-Type: application/vnd.ms-excel'); if (strpos($_SERVER['HTTP_USER_AGENT'], "MSIE")) { header('Content-Disposition: attachment; filename="' . urlencode($fileName) . '.xls"'); } else { header('Content-Disposition: attachment; filename="' . $fileName . '.xls"'); } header('Cache-Control: max-age=0'); $objWriter->save('php://output'); //输出到浏览器 die; out_fail: // 失败,打印错误消息 $flashMessageArray = $this->flashMessageArray; foreach ($flashMessageArray as $flashMessage) { echo $flashMessage . '<br />'; } }