/**
* 订单下载
*
* @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 />';
}
}
