/**
* 给出一组商品 ID，取得所有商品的图片，
* 这是一个大查询，可能会很多数据，之所以加这个大查询的目的是，
* 我们宁可要一个几百条记录的大查询，也不要几百个一条记录的小查询
*
* @return array 图像集合 array(array(图片1), array(图片2))
*
* @param array $goodsIdArray 商品的 ID 数组
* @param int $ttl 缓存时间
*/
public function fetchGoodsGalleryArrayByGoodsIdArray(array $goodsIdArray, $ttl = 0)
{
if (!is_array($goodsIdArray) || empty($goodsIdArray)) {
throw new \InvalidArgumentException('goodsIdArray must be an array not empty');
}
// 参数验证
$validator = new Validator(array('ttl' => $ttl));
$ttl = $validator->digits()->min(0)->validate('ttl');
$this->validate($validator);
$dataMapper = new DataMapper('goods_gallery');
$sqlInClause = QueryBuilder::buildInCondition('goods_id', $goodsIdArray, \PDO::PARAM_INT);
return $dataMapper->find(array($sqlInClause), array('order' => 'goods_id asc , img_sort_order desc, img_id asc'), $ttl);
}
/**
* 对查询参数做一些处理，使得它能适合我们的 sql 搜索
*
* @param $searchParamArray
*
* @return array
* @throws \InvalidArgumentException
*/
protected function prepareSearchParam($searchParamArray)
{
if (!is_array($searchParamArray)) {
throw new \InvalidArgumentException('searchParam illegal : ' . var_export($searchParamArray, true));
}
$resultParamArray = array();
foreach ($searchParamArray as $searchParam) {
if (is_array($searchParam) && count($searchParam) == 3) {
switch ($searchParam[0]) {
// 取分类下面的商品，包括子分类的商品，我们需要取得所有子分类的 ID 然后重新构造查询条件
case 'category_id':
case 'g.category_id':
case 'cat_id':
case 'g.cat_id':
$goodsCategoryService = new GoodsCategoryService();
// 最多取 5 层分类，缓存 10 分钟
$childrenIdArray = $goodsCategoryService->fetchCategoryChildrenIdArray($searchParam[2], 5, 600);
$childrenIdArray[] = $searchParam[2];
// 加入父节点
// 构造新的查询条件
$searchParam = array(QueryBuilder::buildInCondition('g.cat_id', $childrenIdArray, \PDO::PARAM_INT));
break;
// 允许多品牌查询，需要生成 OR 查询条件，多品牌查询的情况 brand_id = 123_45_65_35 ，采用 _ 作为分隔
// 允许多品牌查询，需要生成 OR 查询条件，多品牌查询的情况 brand_id = 123_45_65_35 ，采用 _ 作为分隔
case 'brand_id':
case 'g.brand_id':
// 只处理 = 的情况
if ('=' != $searchParam[1] || false === strpos($searchParam[2], '_')) {
break;
}
$brandValueArray = explode('_', $searchParam[2]);
$brandQueryCondArray = array();
foreach ($brandValueArray as $brandValue) {
if (!empty($brandValue)) {
$brandQueryCondArray[] = array('g.brand_id = ?', intval($brandValue));
}
}
// 生成一串的 OR 查询
$searchParam = QueryBuilder::buildOrFilter($brandQueryCondArray);
break;
default:
break;
}
}
$resultParamArray[] = $searchParam;
}
return $resultParamArray;
}
/**
* 根据一组词返回一组词条
*
* @param array $wordArray 例如： array('360cps','yiqifacps',...)
* @param int $ttl
*
* @return array
*
* 格式为 array(
* array('word' => $word, 'name' => '用于显示的名字', 'desc' => '解释描述', 'data' => '额外数据'),
* array('word' => $word, 'name' => '用于显示的名字', 'desc' => '解释描述', 'data' => '额外数据'),
* )
*/
public function getWordArray($wordArray, $ttl = 0)
{
$inCond = QueryBuilder::buildInCondition('meta_key', $wordArray, \PDO::PARAM_STR);
$queryResult = $this->_fetchArray('meta', '*', array(array('meta_type = ? ', Dictionary::META_TYPE), array($inCond)), array('order' => 'meta_sort_order desc, meta_id desc'), 0, 0, $ttl);
// 建立 word --> 记录 的倒查表
$wordToMetaArray = array();
foreach ($queryResult as $resultItem) {
$wordToMetaArray[$resultItem['meta_key']] = $resultItem;
}
// 构造最后的查询结果
$wordDict = array();
foreach ($wordArray as $word) {
if (array_key_exists($word, $wordToMetaArray)) {
$meta = $wordToMetaArray[$word];
$wordDict[] = array('word' => $word, 'name' => $meta['meta_name'], 'desc' => $meta['meta_desc'], 'data' => $meta['meta_data']);
} else {
$wordDict[] = array('word' => $word, 'name' => $word, 'desc' => $word, 'data' => '');
}
}
return $wordDict;
}
public function Search($f3)
{
// 权限检查
$this->requirePrivilege('manage_order_goods_search');
/**
* 我们使用搜索模块做搜索操作
*/
$searchFieldSelector = 'og.*, oi.user_id, oi.system_id, oi.kefu_user_id, oi.kefu_user_name, oi.kefu_user_rate';
global $smarty;
// 参数验证
$validator = new Validator($f3->get('GET'));
$pageNo = $validator->digits()->min(0)->validate('pageNo');
$pageSize = $validator->digits()->min(0)->validate('pageSize');
// 设置缺省值
$pageNo = isset($pageNo) && $pageNo > 0 ? $pageNo : 0;
$pageSize = isset($pageSize) && $pageSize > 0 ? $pageSize : 10;
// shippingStatus, 0 全部，1 已发货，2 未发货
$shippingStatus = $validator->digits()->min(1)->validate('shippingStatus');
//订单表单查询
$searchFormQuery = array();
$searchFormQuery['og.order_id'] = $validator->digits()->min(1)->filter('ValidatorIntValue')->validate('order_id');
$searchFormQuery['og.rec_id'] = $validator->digits()->min(1)->filter('ValidatorIntValue')->validate('rec_id');
$searchFormQuery['og.goods_id'] = $validator->digits()->min(1)->filter('ValidatorIntValue')->validate('goods_id');
$searchFormQuery['og.goods_name'] = $validator->validate('goods_name');
$searchFormQuery['oi.order_sn'] = $validator->validate('order_sn');
$searchFormQuery['oi.pay_no'] = $validator->validate('pay_no');
$order_goods_status = $validator->min(-1)->filter('ValidatorIntValue')->validate('order_goods_status');
if ($order_goods_status >= 0) {
$searchFormQuery['og.order_goods_status'] = $order_goods_status;
}
$searchFormQuery['oi.consignee'] = $validator->validate('consignee');
$searchFormQuery['oi.mobile'] = $validator->validate('mobile');
$searchFormQuery['oi.address'] = $validator->validate('address');
$searchFormQuery['oi.postscript'] = $validator->validate('postscript');
$searchFormQuery['og.memo'] = $validator->validate('memo');
$searchFormQuery['oi.kefu_user_id'] = $validator->filter('ValidatorIntValue')->validate('kefu_user_id');
$searchFormQuery['og.goods_admin_user_id'] = $validator->filter('ValidatorIntValue')->validate('goods_admin_user_id');
//下单时间
$createTimeStartStr = $validator->validate('create_time_start');
$createTimeStart = Time::gmStrToTime($createTimeStartStr) ?: null;
$createTimeEndStr = $validator->validate('create_time_end');
$createTimeEnd = Time::gmStrToTime($createTimeEndStr) ?: null;
$searchFormQuery['og.create_time'] = array($createTimeStart, $createTimeEnd);
//付款时间
$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);
if (!$this->validate($validator)) {
goto out_display;
}
// 构造查询条件
$searchParamArray = array();
// 用户取消的订单商品我们就不再显示了
$searchParamArray[] = array(QueryBuilder::buildNotInCondition('oi.order_status', array(OrderBasicService::OS_CANCELED, OrderBasicService::OS_INVALID), \PDO::PARAM_INT));
if (1 == $shippingStatus) {
// 1 未发货
$searchParamArray[] = array('og.shipping_id = 0');
} elseif (2 == $shippingStatus) {
// 2 已发货
$searchParamArray[] = array('og.shipping_id <> 0');
} else {
// do nothing
}
// 用户查询，目前支持 用户名、邮箱 查询
$user_name = $validator->validate('user_name');
$email = $validator->validate('email');
if (!empty($user_name) || !empty($email)) {
$userQuery = array();
$userQuery['user_name'] = $user_name;
$userQuery['email'] = $email;
$userBasicService = new UserBasicService();
$queryUserArray = $userBasicService->_fetchArray('users', 'user_id', QueryBuilder::buildQueryCondArray($userQuery), array('order' => 'user_id desc'), 0, 1000);
unset($userBasicService);
if (empty($queryUserArray)) {
$this->addFlashMessage('搜索的用户不存在');
} else {
$userIdArray = array();
foreach ($queryUserArray as $queryUser) {
$userIdArray[] = $queryUser['user_id'];
}
if (!empty($userIdArray)) {
$searchParamArray[] = array(QueryBuilder::buildInCondition('oi.user_id', $userIdArray, \PDO::PARAM_INT));
}
unset($userIdArray);
unset($queryUserArray);
}
}
$utmSource = $validator->validate('utm_source');
if ('SELF' == $utmSource) {
$searchParamArray[] = array('orf.utm_source is null');
} else {
$searchFormQuery['orf.utm_source'] = array('=', $utmSource);
}
$utmMedium = $validator->validate('utm_medium');
if ('SELF' == $utmMedium) {
$searchParamArray[] = array('orf.utm_medium is null');
} else {
$searchFormQuery['orf.utm_medium'] = array('=', $utmMedium);
}
// 表单查询
$searchParamArray = array_merge($searchParamArray, QueryBuilder::buildSearchParamArray($searchFormQuery));
// 使用哪个搜索模块
$searchModule = SearchHelper::Module_OrderGoodsOrderInfo;
if (!empty($utmSource) || !empty($utmMedium)) {
$searchModule = SearchHelper::Module_OrderGoodsOrderInfoOrderRefer;
}
// 查询订单列表
$totalCount = SearchHelper::count($searchModule, $searchParamArray);
if ($totalCount <= 0) {
// 没订单，可以直接退出了
goto out_display;
}
// 页数超过最大值，返回第一页
if ($pageNo * $pageSize >= $totalCount) {
RouteHelper::reRoute($this, '/Order/Goods/Search');
}
// 查询订单列表
$orderGoodsArray = SearchHelper::search($searchModule, $searchFieldSelector, $searchParamArray, array(array('og.order_id', 'desc')), $pageNo * $pageSize, $pageSize);
// 订单的支付状态显示
foreach ($orderGoodsArray as &$orderGoodsItem) {
$orderGoodsItem['order_goods_status_desc'] = OrderGoodsService::$orderGoodsStatusDesc[$orderGoodsItem['order_goods_status']];
}
// 前面用了引用，这里一定要清除，防止影响后面的数据
unset($orderGoodsItem);
// 取得用户 id 列表
$userIdArray = array();
foreach ($orderGoodsArray as $orderGoodsItem) {
$userIdArray[] = $orderGoodsItem['user_id'];
}
$userIdArray = array_unique($userIdArray);
//取得用户信息
$userBasicService = new UserBasicService();
$userArray = $userBasicService->fetchUserArrayByUserIdArray($userIdArray);
// 建立 user_id --> user 的反查表，方便快速查询
$userIdToUserArray = array();
foreach ($userArray as $user) {
$userIdToUserArray[$user['user_id']] = $user;
}
// 放入用户信息
foreach ($orderGoodsArray as &$orderGoodsItem) {
if (isset($userIdToUserArray[$orderGoodsItem['user_id']])) {
// 很老的订单，用户可能被删除了
$orderGoodsItem['user_name'] = $userIdToUserArray[$orderGoodsItem['user_id']]['user_name'];
$orderGoodsItem['email'] = $userIdToUserArray[$orderGoodsItem['user_id']]['email'];
}
}
// 前面用了引用，这里一定要清除，防止影响后面的数据
unset($orderGoodsItem);
// 给模板赋值
$smarty->assign('totalCount', $totalCount);
$smarty->assign('pageNo', $pageNo);
$smarty->assign('pageSize', $pageSize);
$smarty->assign('orderGoodsArray', $orderGoodsArray);
out_display:
$smarty->display('order_goods_search.tpl');
}
public function get($f3)
{
global $smarty;
// 参数验证
$validator = new Validator($_GET);
$cid = $validator->required()->validate('cid');
$queryCreateDate = $validator->digits()->validate('d');
$queryUpdateDate = $validator->digits()->validate('ud');
// 参数有错误
if (!$this->validate($validator)) {
goto out_fail;
}
// 不能同时为空
if (empty($queryCreateDate) && empty($queryUpdateDate)) {
goto out_fail;
}
if (!empty($queryCreateDate)) {
// 参数检查，格式只能为 20120304 或者 2012030410
$dateStrLen = strlen($queryCreateDate);
if (8 != $dateStrLen && 10 != $dateStrLen) {
goto out_fail;
}
}
if (!empty($queryUpdateDate)) {
// 参数检查，格式只能为 20120304 或者 2012030410
$dateStrLen = strlen($queryUpdateDate);
if (8 != $dateStrLen && 10 != $dateStrLen) {
goto out_fail;
}
}
enableSmartyCache(true, 60);
//缓存1分钟，防止访问很频繁从而变成对网站的攻击
$smartyCacheId = 'YiqifaCps|' . md5(json_encode(QueryOrder::$extraQueryCond) . '_' . $cid . '_' . $queryCreateDate . '_' . $queryUpdateDate);
// 判断是否有缓存
if ($smarty->isCached('yiqifacps_empty.tpl', $smartyCacheId)) {
goto out_display;
}
$txtOutput = '';
// 构造查询条件
$condArray = array();
$condArray[] = array('orf.utm_source = ? or (orf.login_type = ? and orf.utm_source is null)', 'YIQIFACPS', 'qqlogin');
// add_time 查询
if (!empty($queryCreateDate)) {
$startTime = 0;
$endTime = 0;
if (strlen($queryCreateDate) == 8) {
$startTime = strtotime($queryCreateDate);
$endTime = $startTime + 24 * 60 * 60;
}
if (strlen($queryCreateDate) == 10) {
$datePart = substr($queryCreateDate, 0, 8);
$hourPart = substr($queryCreateDate, 8, 2);
$startTime = strtotime($datePart . " " . $hourPart . ":00:00");
$endTime = $startTime + 60 * 60;
}
$condArray[] = array('oi.add_time >= ? and oi.add_time < ?', $startTime, $endTime);
}
// update_time 查询
if (!empty($queryUpdateDate)) {
$startTime = 0;
$endTime = 0;
if (strlen($queryUpdateDate) == 8) {
$startTime = strtotime($queryUpdateDate);
$endTime = $startTime + 24 * 60 * 60;
}
if (strlen($queryUpdateDate) == 10) {
$datePart = substr($queryUpdateDate, 0, 8);
$hourPart = substr($queryUpdateDate, 8, 2);
$startTime = strtotime($datePart . " " . $hourPart . ":00:00");
$endTime = $startTime + 60 * 60;
}
$condArray[] = array('oi.update_time >= ? and oi.update_time < ?', $startTime, $endTime);
}
/**
* 加入额外的查询条件
*/
if (!empty(QueryOrder::$extraQueryCond)) {
$condArray[] = QueryOrder::$extraQueryCond;
}
// 查询订单，每次最多 2000 条数据，防止拖死系统
$orderReferService = new OrderReferService();
$orderReferInfoArray = $orderReferService->fetchOrderReferInfo($condArray, array('order' => 'oi.order_id asc'), 0, 2000);
// 过滤 cid，只留下需要查询的 cid 数据，同时收集 order_id 用于查询 order_goods 信息
$cidOrderReferInfoArray = array();
$orderIdArray = array();
foreach ($orderReferInfoArray as $orderReferInfoItem) {
$referParamArray = json_decode($orderReferInfoItem['refer_param'], true);
if (!empty($referParamArray['cid']) && $referParamArray['cid'] != $cid) {
//不是要查询的订单，跳过
continue;
// ==== 为了测试临时注释的代码
}
$cidOrderReferInfoArray[] = $orderReferInfoItem;
$orderIdArray[] = $orderReferInfoItem['order_id'];
}
unset($orderReferInfoArray);
//清除旧数据
$orderReferInfoArray = $cidOrderReferInfoArray;
unset($cidOrderReferInfoArray);
//清除临时数据
if (empty($orderReferInfoArray)) {
// 没有数据，跳出
goto out;
}
$orderGoodsArray = $orderReferService->_fetchArray('order_goods', '*', array(array(QueryBuilder::buildInCondition('order_id', $orderIdArray))), array('order' => 'order_id asc, rec_id asc'), 0, 10000);
//最多查询 10000 条记录，防止拖死系统
// 建立 order_id --> array( order_goods ) 的反查表
$orderIdToOrderGoodsArray = array();
foreach ($orderGoodsArray as $orderGoodsItem) {
if (!isset($orderIdToOrderGoodsArray[$orderGoodsItem['order_id']])) {
$orderIdToOrderGoodsArray[$orderGoodsItem['order_id']] = array();
}
$orderIdToOrderGoodsArray[$orderGoodsItem['order_id']][] = $orderGoodsItem;
}
// 根据订单构建 CPS 返回记录
foreach ($orderReferInfoArray as $orderReferItem) {
if (isset($orderIdToOrderGoodsArray[$orderReferItem['order_id']])) {
$txtOutput .= $this->getOrderTxt($orderReferItem, $orderIdToOrderGoodsArray[$orderReferItem['order_id']]);
}
}
out:
$smarty->assign('outputContent', $txtOutput);
out_display:
//成功从这里返回
header('Content-Type:text/plain;charset=utf-8');
header("Cache-Control: no-cache, must-revalidate");
// HTTP/1.1 //查询信息
$smarty->display('yiqifacps_empty.tpl', $smartyCacheId);
return;
out_fail:
// 失败从这里返回
echo "paramters error";
}
/**
* 根据 id array 取得一组 brand 的名字
*
* @param array $idArray
* @param int $ttl
* @return array
*/
public function fetchBrandArrayByIdArray($idArray, $ttl = 0)
{
return $this->_fetchArray('brand', '*', array(array(QueryBuilder::buildInCondition('brand_id', $idArray, \PDO::PARAM_INT))), array('order' => 'sort_order desc, brand_id desc'), 0, 0, $ttl);
}
/**
* 下载 拣货单
*
* @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 />';
}
}
/**
* 取得对应分类下面商品的总数，用于分页显示
*
* @return int 商品总数
*
* @param int $categoryId 分类的ID
* @param int $level 取得多少层，子分类有可能很深，我们只取有限层次
* @param string $systemTag 系统标记
* @param int $ttl 缓存时间
*/
public function countGoodsArray($categoryId, $level, $systemTag, $ttl = 0)
{
// 参数验证
$validator = new Validator(array('categoryId' => $categoryId, 'level' => $level, 'systemTag' => $systemTag, 'ttl' => $ttl));
$categoryId = $validator->digits()->min(0)->validate('categoryId');
$level = $validator->required()->digits()->min(1)->validate('level');
$systemTag = $validator->validate('systemTag');
$ttl = $validator->digits()->min(0)->validate('ttl');
$this->validate($validator);
$childrenIdArray = $this->fetchCategoryChildrenIdArray($categoryId, $level, $ttl);
$childrenIdArray[] = $categoryId;
// 加入父节点
$queryCondArray = array();
$queryCondArray[] = array('is_delete = 0 AND is_on_sale = 1 AND is_alone_sale = 1');
// 构建 SQL 的 in 语句， cat_id in (100,20,30)
$queryCondArray[] = array(QueryBuilder::buildInCondition('cat_id', $childrenIdArray, \PDO::PARAM_INT));
if (!empty($systemTag)) {
$queryCondArray[] = array('system_tag_list like ? ', '%' . Utils::makeTagString(array($systemTag)) . '%');
}
$dataMapper = new DataMapper('goods');
return $dataMapper->count(QueryBuilder::buildAndFilter($queryCondArray), null, $ttl);
}
/**
* 根据 goods_id 数组取得一组商品
*
* @param array $goodsIdArray
* @param int $ttl
*
* @return array
*/
public function fetchGoodsArrayByGoodsIdArray(array $goodsIdArray, $ttl = 0)
{
return $this->_fetchArray('goods', '*', array(array(QueryBuilder::buildInCondition('goods_id', $goodsIdArray))), null, 0, 0, $ttl);
}
/**
* 根据类型ID数组取得一组类型
*
* @param array $typeIdArray 类型ID数组
* @param int $ttl
* @return array
*/
public function fetchGoodsTypeArrayByTypeIdArray(array $typeIdArray, $ttl = 0)
{
return $this->fetchGoodsTypeArray(array(array(QueryBuilder::buildInCondition('meta_id', $typeIdArray, \PDO::PARAM_INT))), 0, 0, $ttl);
}
/**
* 根据一组ID取得对应的分类
*
* @param array $categoryIdArray
* @param int $ttl
*
* @return array
*/
public function fetchCategoryArrayByIdArray($categoryIdArray, $ttl = 0)
{
return $this->_fetchArray('meta', '*', array(array('meta_type = ?', self::META_TYPE), array(QueryBuilder::buildInCondition('meta_id', $categoryIdArray))), null, 0, 0, $ttl);
}
/**
* 用一组供货商 ID 一次批量取得供货商记录
*
* @param array $supplierIdArray
* @param int $ttl
*
* @return array
*/
public function fetchSupplierArrayBySupplierIdArray(array $supplierIdArray, $ttl = 0)
{
// 参数验证
$validator = new Validator(array('supplierIdArray' => $supplierIdArray), '');
$supplierIdArray = $validator->required()->requireArray(false)->validate('supplierIdArray');
$this->validate($validator);
return $this->_fetchArray('suppliers', '*', array(array(QueryBuilder::buildInCondition('suppliers_id', $supplierIdArray, \PDO::PARAM_INT))), array('order' => 'suppliers_id asc'), 0, 0, $ttl);
}
public function post($f3)
{
// 权限检查
$this->requirePrivilege('manage_order_settle');
// 参数验证
$validator = new Validator($f3->get('POST'));
$payTimeStart = $validator->required()->digits()->validate('payTimeStart');
$payTimeEnd = $validator->required()->digits()->validate('payTimeEnd');
$suppliers_id = $validator->required()->digits()->validate('suppliers_id');
if (!$this->validate($validator)) {
goto out;
}
$orderGoodsIdArray = $validator->validate('orderGoodsIdArray');
$memo = $validator->validate('memo');
if (empty($orderGoodsIdArray) || !is_array($orderGoodsIdArray)) {
$this->addFlashMessage('没有订单需要结算');
goto out;
}
// 取得供货商的信息
$userSupplierService = new UserSupplierService();
$supplier = $userSupplierService->loadSupplierById($suppliers_id);
if ($supplier->isEmpty()) {
$this->addFlashMessage('供货商不存在');
goto out;
}
// 取得所有 order_goods 记录
$orderGoodsService = new OrderGoodsService();
$orderGoodsArray = $orderGoodsService->_fetchArray('order_goods', 'rec_id, order_goods_status, goods_number,suppliers_id, suppliers_price, suppliers_shipping_fee, suppliers_refund, shipping_id', array(array(QueryBuilder::buildInCondition('rec_id', $orderGoodsIdArray))), array('order' => 'rec_id asc'), 0, $f3->get('sysConfig[max_query_record_count]'), 0);
//最多限制 max_query_record_count 条记录
if (empty($orderGoodsArray)) {
$this->addFlashMessage('没有订单需要结算');
goto out;
}
// 检查订单,计算订单结算金额
$totalGoodsPrice = 0;
$totalShippingFee = 0;
$totalRefund = 0;
$totalOrderGoodsCount = 0;
//剔除非法的 orderGoodsId
$invalidOrderGoodsIdArray = array();
foreach ($orderGoodsArray as $orderGoodsItem) {
if (OrderGoodsService::OGS_UNPAY == $orderGoodsItem['order_goods_status'] || $orderGoodsItem['suppliers_id'] != $suppliers_id || $orderGoodsItem['shipping_id'] <= 0) {
// 非法订单，剔除掉
$invalidOrderGoodsIdArray[] = $orderGoodsItem['rec_id'];
continue;
}
$totalGoodsPrice += $orderGoodsItem['goods_number'] * $orderGoodsItem['suppliers_price'];
$totalShippingFee += $orderGoodsItem['suppliers_shipping_fee'];
$totalRefund += $orderGoodsItem['suppliers_refund'];
$totalOrderGoodsCount++;
}
//剔除非法的 orderGoodsId
$orderGoodsIdArray = array_diff($orderGoodsIdArray, $invalidOrderGoodsIdArray);
if (empty($orderGoodsIdArray)) {
$this->addFlashMessage('没有订单需要结算');
goto out;
}
// 取得当前结算的管理员
$authAdminUser = AuthHelper::getAuthUser();
$dbEngine = DataMapper::getDbEngine();
try {
// 我们这里需要事务保障
$dbEngine->begin();
//创建 order_settle 记录
$orderSettleService = new OrderSettleService();
$orderSettle = $orderSettleService->loadOrderSettleBySettleId(0);
$orderSettle->user_id = $authAdminUser['user_id'];
$orderSettle->user_name = $authAdminUser['user_name'];
$orderSettle->settle_start_time = $payTimeStart;
$orderSettle->settle_end_time = $payTimeEnd;
$orderSettle->suppliers_id = $suppliers_id;
$orderSettle->suppliers_name = $supplier['suppliers_name'];
$orderSettle->suppliers_goods_price = $totalGoodsPrice;
$orderSettle->suppliers_shipping_fee = $totalShippingFee;
$orderSettle->suppliers_refund = $totalRefund;
$orderSettle->create_time = Time::gmTime();
$orderSettle->memo = $memo;
$orderSettle->save();
// 更新 order_goods ，设置上 settle_id
$sql = "update " . DataMapper::tableName('order_goods') . ' set settle_id = ? where ' . QueryBuilder::buildInCondition('rec_id', $orderGoodsIdArray);
$dbEngine->exec($sql, $orderSettle->settle_id);
$dbEngine->commit();
$this->addFlashMessage('成功创建结算记录');
} catch (\Exception $e) {
$dbEngine->rollback();
$this->addFlashMessage('数据库读写错误');
}
out:
// 回到结算页面
RouteHelper::reRoute($this, RouteHelper::getRefer(), false);
}
