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