public function run(array $params)
{
global $f3;
$goodsBasicService = new GoodsBasicService();
$goods = $goodsBasicService->loadGoodsById('20');
$sql = 'update ' . DataMapper::tableName('goods') . ' set goods_after_service = ? ';
$dbEngine = DataMapper::getDbEngine();
$dbEngine->exec($sql, array(1 => $goods['goods_after_service']));
}
/**
* 记录帐户变动
* @param int $userId 用户id
* @param float $userMoney 可用余额变动
* @param float $frozenMoney 冻结余额变动
* @param int $rankPoints 等级积分变动
* @param int $payPoints 消费积分变动
* @param string $changeDesc 变动说明
* @param int $changeType 变动类型：参见常量文件
*
* @return void
*/
function logChange($userId, $userMoney = 0, $frozenMoney = 0, $rankPoints = 0, $payPoints = 0, $changeDesc = '', $changeType = AccountLog::ACT_OTHER, $adminUserId = 0)
{
/* 插入帐户变动记录 */
$accountLogInfo = array('user_id' => $userId, 'user_money' => $userMoney, 'frozen_money' => $frozenMoney, 'rank_points' => $rankPoints, 'pay_points' => $payPoints, 'change_time' => Time::gmTime(), 'change_desc' => $changeDesc, 'change_type' => $changeType, 'admin_user_id' => $adminUserId);
// 插入一条记录
$dataMapper = new DataMapper('account_log');
$dataMapper->copyFrom($accountLogInfo);
$dataMapper->save();
// 更新用户信息
$sql = "UPDATE " . DataMapper::tableName('users') . " SET user_money = user_money + ('{$userMoney}')," . " frozen_money = frozen_money + ('{$frozenMoney}')," . " rank_points = rank_points + ('{$rankPoints}')," . " pay_points = pay_points + ('{$payPoints}')" . " WHERE user_id = ? Order By user_id asc LIMIT 1 ";
$dbEngine = DataMapper::getDbEngine();
$dbEngine->exec($sql, $userId);
}
public function run(array $params)
{
global $f3;
// 每次处理多少条记录
$batchProcessCount = 100;
$baseService = new BaseService();
$totalGoodsCount = $baseService->_countArray('goods', null);
// 记录处理开始
for ($offset = 0; $offset < $totalGoodsCount; $offset += $batchProcessCount) {
$goodsArray = $baseService->_fetchArray('goods', 'goods_id', null, array('order' => 'goods_id asc'), $offset, $batchProcessCount);
foreach ($goodsArray as $goodsItem) {
$sql = "update " . DataMapper::tableName('goods') . ' set ' . ' user_buy_number = (select sum(goods_number) from ' . DataMapper::tableName('order_goods') . ' where goods_id = ? )' . ' ,user_pay_number = (select sum(goods_number) from ' . DataMapper::tableName('order_goods') . ' where goods_id = ? and order_goods_status > 0)' . ' where goods_id = ? order by goods_id asc limit 1 ';
$dbEngine = DataMapper::getDbEngine();
$dbEngine->exec($sql, array(1 => $goodsItem['goods_id'], $goodsItem['goods_id'], $goodsItem['goods_id']));
}
unset($goodsArray);
printLog('calculate goods buy number offset : ' . $offset);
}
printLog('calculate goods buy number finished , offset : ' . $offset);
}
/**
* 商品数据转化
*/
private function convertGoods()
{
global $f3;
// 在这里配置源商品图片的路径前缀
$f3->set('srcImagePrefix', "http://img.bangzhufu.com/static/");
$tableMigrate = new TableMigrate();
$tableMigrate->batchProcessCount = 100;
// 每次批处理 100 个商品，防止商品过多搞死系统
// 清空数据
$dstTable = new DstDataMapper('goods');
$tableMigrate->clearTable($dstTable);
$goodsGalleryTable = new DstDataMapper('goods_gallery');
$tableMigrate->clearTable($goodsGalleryTable);
$tableMigrate->setAutoIncValue($goodsGalleryTable, 1);
$goodsAttrTable = new DstDataMapper('goods_attr');
$tableMigrate->clearTable($goodsAttrTable);
$tableMigrate->setAutoIncValue($goodsAttrTable, 1);
//转化数据
$currentTime = time();
$tableMigrate->convertTable('team', array(array('begin_time < ? and end_time > ?', $currentTime, $currentTime)), array('order' => 'id asc'), 'goods', array('id' => 'goods_id', 'user_id' => 'goods_sn', 'title' => 'goods_name', 'summary' => 'goods_brief', 'group_id' => 'cat_id', 'partner_id' => 'suppliers_id', 'team_price' => 'shop_price', 'market_price' => 'market_price', 'agent_price' => 'suppliers_price', 'product' => 'goods_name_short', 'condbuy' => 'condbuy', 'image' => null, 'image1' => null, 'image2' => null, 'agent_fare' => 'suppliers_shipping_fee', 'farefree' => 'shipping_free_number', 'detail' => 'goods_desc', 'notice' => 'goods_notice', 'sort_order' => 'sort_order', 'seo_title' => 'seo_title', 'seo_keyword' => 'seo_keyword', 'seo_description' => 'seo_description', 'express_relate' => 'shipping_fee', 'city_id' => 'goods_number'), array('user_id' => function ($user_id, $record) {
global $f3;
//在这里生成 goods_sn
return $f3->get('sysConfig[goods_sn_prefix]') . $record->id;
}, 'express_relate' => function ($express_relate, $record) {
// 最土系统 -1 表示免邮费，我们这里修改邮费为 0
if ($record['farefree'] < 0) {
return 0;
}
$dataArray = unserialize($express_relate);
$maxShippingFee = 0;
// 取最大的快递费
foreach ($dataArray as $data) {
$maxShippingFee = $data['price'] > $maxShippingFee ? $data['price'] : $maxShippingFee;
}
return $maxShippingFee;
}, 'farefree' => function ($farefree, $record) {
// 最土 -1 表示免运费，我们这里一律改成不免邮费
if ($farefree < 0) {
return 0;
}
return $farefree;
}, 'city_id' => function ($city_id, $record) {
return 1000;
// 所有库存缺省都设置为 1000
}), function ($srcRecord) {
global $f3;
$srcImagePrefix = $f3->get('srcImagePrefix');
$fetchImageArray = array();
if (!empty($srcRecord->image)) {
$fetchImageArray[] = $srcImagePrefix . $srcRecord->image;
}
if (!empty($srcRecord->image1)) {
$fetchImageArray[] = $srcImagePrefix . $srcRecord->image1;
}
if (!empty($srcRecord->image2)) {
$fetchImageArray[] = $srcImagePrefix . $srcRecord->image2;
}
if (!empty($fetchImageArray)) {
// 设置 fetchImageArray 的值，用于后面图片抓取
$f3->set('fetchImageArray_' . $srcRecord->id, $fetchImageArray);
}
}, function ($srcRecord, $dstRecord) {
global $f3;
$fetchImageArray = $f3->get('fetchImageArray_' . $srcRecord->id);
if (empty($fetchImageArray)) {
return;
}
// 我们在这里做图片的抓取操作
foreach ($fetchImageArray as $fetchImageUrl) {
fetchGoodsImage($srcRecord->id, $fetchImageUrl);
//usleep(200000); // 睡 200 ms，防止抓取太快服务器不响应
}
// 释放资源
$f3->clear('fetchImageArray_' . $srcRecord->id);
// 处理 condbuy 字段
if (empty($srcRecord->condbuy)) {
return;
}
// 需要更新商品的选择
$dstRecord->goods_type = $f3->get('sysConfig[condbuy_goods_type]');
// 删除旧的数据
$sql = 'delete from ' . DataMapper::tableName('goods_attr') . ' where goods_id = ?';
$dbEngine = DstDataMapper::getDbEngine();
$dbEngine->exec($sql, $srcRecord->id);
// 解析 condbuy {红色}{绿色}{蓝色}
$condBuyArray = explode('}{', '}' . $srcRecord->condbuy . '{');
foreach ($condBuyArray as $condBuyItem) {
if (empty($condBuyItem)) {
continue;
}
$dataMapper = new DataMapper('goods_attr');
$dataMapper->goods_id = $srcRecord->id;
$dataMapper->attr_id = $f3->get('sysConfig[condbuy_attr_id]');
$dataMapper->attr_value = $condBuyItem;
$dataMapper->attr_price = 0;
$dataMapper->save();
unset($dataMapper);
}
});
// 重设表的 AUTO_INCREMENT 值
$tableMigrate->resetAutoIncValue($dstTable, 'goods_id');
// 清理数据
unset($tableMigrate);
unset($dstTable);
unset($result);
}
/**
* 删除 order_info 下面对应的所有 order_goods 记录
*
* @param int $orderId 订单ID号
*
*/
public function removeAllOrderGoods($orderId)
{
// 参数验证
$validator = new Validator(array('orderId' => $orderId));
$orderId = $validator->required()->digits()->min(1)->validate('orderId');
$this->validate($validator);
$dbEngine = DataMapper::getDbEngine();
$dbEngine->exec('DELETE from ' . DataMapper::tableName('order_goods') . ' where order_id = ' . $orderId);
}
/**
* 统计每个商品分类有多少商品，不计算子分类的商品数量（比如 A 下面有 B, C 分类，
* 这里计算的 A 包含的商品数量不计算 B, C 的在内）
*
* @param int $ttl 缓存时间
*
* @return array
* 格式 array(array(cat_id, goods_count), ...)
*/
public function calcCategoryGoodsCount($ttl = 0)
{
$dbEngine = DataMapper::getDbEngine();
return $dbEngine->exec('select cat_id, count(1) as goods_count from ' . DataMapper::tableName('goods') . ' group by cat_id ', null, $ttl);
}
/**
* 取得一个商品在某个属性组中的属性
*
* @param int $groupId
* @param int $goods_id
* @param int $ttl
*
* @return array|null
*/
public function fetchGoodsAttributeArrayOfAttrGroup($groupId, $goods_id, $ttl = 0)
{
// 参数验证
$validator = new Validator(array('groupId' => $groupId, 'goods_id' => $goods_id, 'ttl' => $ttl));
$groupId = $validator->required()->digits()->min(1)->validate('groupId');
$goods_id = $validator->required()->digits()->min(1)->validate('goods_id');
$ttl = $validator->digits()->min(0)->validate('ttl');
$this->validate($validator);
// 取得商品的属性
$sql = "SELECT a.*, " . "g.goods_attr_id, g.attr_value, g.attr_price " . 'FROM ' . DataMapper::tableName('meta') . ' AS a ' . 'LEFT JOIN ' . DataMapper::tableName('goods_attr') . ' AS g ON a.meta_id = g.attr_id and g.goods_id = ? ' . "WHERE a.parent_meta_id = ? and a.meta_type = ? " . 'ORDER BY a.meta_sort_order, g.attr_price, g.goods_attr_id';
$dbEngine = DataMapper::getDbEngine();
$result = $dbEngine->exec($sql, array(1 => $goods_id, $groupId, GoodsAttrItem::META_TYPE), $ttl);
if (empty($result)) {
// 没有属性，则返回
return null;
}
// 处理属性，属性分为 2 类，一类用于显示，一类用于选择价格
$attrubuteArray = array();
$attrubuteArray['prop'] = array();
//属性 0: 直接用于显示的属性
$attrubuteArray['spec'] = array();
//规格 1: 用户单选属性，不同价格 2: 用户多选属性，不同价格
foreach ($result as $attrItem) {
$attrData = GoodsAttrItem::decodeGoodsAttrItemData($attrItem['meta_data']);
if (GoodsAttrItem::ATTR_TYPE_DISPLAY == $attrData['attr_type']) {
// 只是用于显示的属性
$attrubuteArray['prop'][$attrItem['meta_id']] = array('name' => $attrItem['meta_name'], 'value' => $attrItem['attr_value'], 'goods_attr_id' => $attrItem['goods_attr_id'], 'attr_input_type' => $attrData['attr_input_type'], 'attr_type' => $attrData['attr_type'], 'attr_desc' => $attrItem['meta_desc'], 'meta_id' => $attrItem['meta_id']);
} else {
// 用于选择不同价格的属性
$attrubuteArray['spec'][$attrItem['meta_id']]['meta_id'] = $attrItem['meta_id'];
$attrubuteArray['spec'][$attrItem['meta_id']]['attr_input_type'] = $attrData['attr_input_type'];
$attrubuteArray['spec'][$attrItem['meta_id']]['attr_type'] = $attrData['attr_type'];
$attrubuteArray['spec'][$attrItem['meta_id']]['attr_desc'] = $attrItem['meta_desc'];
$attrubuteArray['spec'][$attrItem['meta_id']]['name'] = $attrItem['meta_name'];
$attrubuteArray['spec'][$attrItem['meta_id']]['values'][] = array('label' => $attrItem['attr_value'], 'price' => $attrItem['attr_price'], 'goods_attr_id' => $attrItem['goods_attr_id']);
}
}
return $attrubuteArray;
}
public function __construct()
{
$this->searchTable = DataMapper::tableName('goods_attr') . ' as ga INNER JOIN ' . DataMapper::tableName('goods') . ' as g on ga.goods_id = g.goods_id';
}
/**
* 取得商品的属性值列表
*
* @param int $goods_id 商品 ID
* @param int $typeId 商品类型 ID
* @param int $ttl 缓存时间
* @return array
*/
public function fetchGoodsAttrItemValueArray($goods_id, $typeId, $ttl = 0)
{
// 首先验证参数
$validator = new Validator(array('goods_id' => $goods_id, 'typeId' => $typeId));
$goods_id = $validator->required()->digits()->min(1)->validate('goods_id');
$typeId = $validator->required()->digits()->min(1)->validate('typeId');
$this->validate($validator);
$tableJoin = DataMapper::tableName('meta') . ' as m LEFT JOIN (select * from ' . DataMapper::tableName('goods_attr') . ' where goods_id = ' . $goods_id . ')' . ' as ga on m.meta_id = ga.attr_item_id';
return $this->_fetchArray($tableJoin, 'm.meta_id, m.meta_type, m.meta_name, m.meta_key, m.meta_ename, m.meta_data, ga.goods_attr_id, ga.attr_item_value', array(array('m.meta_type = ? and m.parent_meta_id = ? ', self::META_TYPE_GOODS_TYPE_ATTR_ITEM, $typeId)), array('order' => 'm.meta_key asc, m.meta_sort_order desc, m.meta_id asc'), 0, 0, $ttl);
}
/**
*
* 对 order_info , order_goods 做 inner_join 查询
*
* @param array $condArray
* @param int $offset
* @param int $limit
* @param int $ttl
*
* @return array
*/
public function fetchOrderInfoOrderGoodsArray(array $condArray, $offset = 0, $limit = 10, $ttl = 0)
{
// 参数验证
$validator = new Validator(array('condArray' => $condArray), '');
$condArray = $validator->requireArray(true)->validate('condArray');
$this->validate($validator);
$tableInnerJoinStr = DataMapper::tableName('order_goods') . ' as og INNER JOIN ' . DataMapper::tableName('order_info') . ' as oi ON og.order_id = oi.order_id';
return $this->_fetchArray($tableInnerJoinStr, 'og.*, oi.user_id, oi.pay_time, oi.add_time, oi.order_sn, ' . 'oi.consignee, oi.address, oi.mobile', $condArray, array('order' => 'og.order_id desc, og.rec_id desc'), $offset, $limit, $ttl);
}
protected function prepareSearchParam($searchParamArray)
{
if (!is_array($searchParamArray)) {
throw new \InvalidArgumentException('searchParam illegal : ' . var_export($searchParamArray, true));
}
// 调用父类先处理
$searchParamArray = parent::prepareSearchParam($searchParamArray);
$resultParamArray = array();
foreach ($searchParamArray as $searchParam) {
$addParam = true;
if (is_array($searchParam) && count($searchParam) == 3) {
switch ($searchParam[0]) {
/** 根据过滤规则，我们构造子查询
* 结构 array('ga.filter', '123.321.45', '100_20.34.67_78')
* 其中 123.321.45 为 attr_item_id
* 100_20.34.67_78 为 goods_attr_id 对应的值
*/
case 'ga.filter':
// 不加入这个参数
$addParam = false;
// 没有值，不需要过滤
$trimSearchParam2 = trim(str_replace('.', '', $searchParam[2]));
// 有可能没有值，全部为点 "..."
if (empty($searchParam[1]) || empty($searchParam[2]) || empty($trimSearchParam2)) {
break;
}
$goodsTypeService = new GoodsTypeService();
// 构造子查询
$queryJoinTable = '';
$firstJoinTable = '';
$queryCondArray = array();
// 构造子查询
$attrItemIdArray = explode('.', $searchParam[1]);
$goodsAttrIdStrArray = explode('.', $searchParam[2]);
$count = min(count($attrItemIdArray), count($goodsAttrIdStrArray));
for ($index = 0; $index < $count; $index++) {
$attrItemId = abs(intval($attrItemIdArray[$index]));
$goodsAttrIdArray = explode('_', $goodsAttrIdStrArray[$index]);
// 跳过无效值
if ($attrItemId <= 0 || empty($goodsAttrIdArray)) {
continue;
}
$goodsAttrItemCond = array();
foreach ($goodsAttrIdArray as $goodsAttrId) {
$goodsAttrId = abs(intval($goodsAttrId));
$goodsAttr = $goodsTypeService->loadGoodsAttrById($goodsAttrId);
// 无效的属性，返回
if ($goodsAttr->isEmpty()) {
continue;
}
$goodsAttrItemCond[] = array("attr_item_value = ?", $goodsAttr['attr_item_value']);
}
if (!empty($goodsAttrItemCond)) {
$condArray = QueryBuilder::buildAndFilter(array(array('attr_item_id = ?', $attrItemId), QueryBuilder::buildOrFilter($goodsAttrItemCond)));
$tmpTableName = 'ga' . $index;
$tmpTable = '(select distinct(goods_id) from ' . DataMapper::tableName('goods_attr') . ' where ' . array_shift($condArray) . ') as ' . $tmpTableName;
$queryCondArray = array_merge($queryCondArray, $condArray);
if (empty($queryJoinTable)) {
$queryJoinTable = $tmpTable;
$firstJoinTable = $tmpTableName;
} else {
$queryJoinTable .= ' INNER JOIN ' . $tmpTable . ' on ' . $firstJoinTable . '.goods_id = ' . $tmpTableName . '.goods_id ';
}
}
}
// 构造子查询
$this->searchTable = DataMapper::tableName('goods') . ' as g INNER JOIN ' . '(select distinct(' . $firstJoinTable . '.goods_id) from (' . $queryJoinTable . ')) as ga on g.goods_id = ga.goods_id';
/**
* 这里是一个很 tricky 的构造查询的方法
*
* 我们不想拼接 SQL 语句，比如 attr_item_value = $attr_item_value，
* 而是采用 array('attr_item_value = ?', $attr_item_value)，这样可以 SQL Bind 避免 SQL 注入
*
* 由于前面的 子查询带了很多 ? 查询，所以我们需要把参数值 unshift 到第一个的位置
*
*/
// 头部压入一个空条件
array_unshift($queryCondArray, '1=1');
// 把这个参数压入到头部
array_unshift($resultParamArray, $queryCondArray);
break;
default:
break;
}
}
// 是否加入参数
if ($addParam) {
$resultParamArray[] = $searchParam;
}
}
return $resultParamArray;
}
public function __construct()
{
$this->searchTable = DataMapper::tableName('order_info') . ' as oi LEFT JOIN ' . DataMapper::tableName('order_refer') . ' as orf on oi.order_id = orf.order_id';
}
public function __construct()
{
$this->searchTable = DataMapper::tableName('order_goods') . ' as og LEFT JOIN ' . DataMapper::tableName('order_info') . ' as oi ON og.order_id = oi.order_id ';
}
public function __construct()
{
$this->searchTable = DataMapper::tableName('goods') . ' as g LEFT JOIN ' . DataMapper::tableName('goods_promote') . ' as gp on g.goods_id = gp.goods_id';
}
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);
}
