/**
* 把一个表的数据转换到另外一个表中
*
* @param string $srcTableName 源表名
* @param array $condArray
* array(
* array('supplier_id = ?', $supplier_id)
* array('is_on_sale = ?', 1)
* array('supplier_price > ? or supplier_price < ?', $priceMin, $priceMax)
* )
* @param array $optionArray 排序分页等条件 ，格式例如 array('order' => 'goods_id asc', 'offset' => 100, 'limit' => '10')
* @param string $dstTableName 目的表名
* @param array $columnMap 列对应，格式如 array('id' => 'goods_id' , 'name' => 'goods_name')
* @param array $srcValueConvertFuncArray
* 对源数据做装换，例如： array('id' => function($srcValue, $srcRecord){ return $srcValue + 1;} , ...)，用闭包函数做转化
* @param callable $recordPreFunc 在查询到 src 的记录之后调用 ，函数原型 function(&$srcRecord){ ... }
* @param callable $recordPostFunc 在 src 已经往 dst 赋值完成之后，dst 还没有写入数据库之前调用,函数原型 function(&$srcRecord, &dstRecord){...}
*
*/
public function convertTable($srcTableName, $condArray, $optionArray, $dstTableName, $columnMap, $srcValueConvertFuncArray, $recordPreFunc = null, $recordPostFunc = null)
{
$srcFieldArray = array();
$dstFieldArray = array();
foreach ($columnMap as $srcField => $dstField) {
$srcFieldArray[] = $srcField;
$dstFieldArray[] = $dstField;
}
$srcTable = new SrcDataMapper($srcTableName);
// 构造查询条件
$filter = null;
if (!empty($condArray)) {
$filter = QueryBuilder::buildAndFilter($condArray);
}
// 获得总数
$totalRecordCount = $srcTable->count($filter);
printLog('begin convertTable ' . $srcTableName . '-->' . $dstTableName . ' totalRecordCount:' . $totalRecordCount, self::$loggerSource);
$recordLeft = $totalRecordCount;
// 剩余多少记录需要处理
$queryOffset = 0;
// 查询的起始位置
while ($recordLeft > 0) {
// 这次需要处理多少记录
$processCount = $recordLeft > $this->batchProcessCount ? $this->batchProcessCount : $recordLeft;
// 记录处理进度
printLog('totalRecordCount:' . $totalRecordCount . ' recordLeft:' . $recordLeft . ' processCount:' . $processCount, self::$loggerSource);
$recordLeft -= $processCount;
// 从源表查询数据
$srcField = '*';
if (!empty($srcFieldArray)) {
$srcField = implode(',', $srcFieldArray);
}
// 处理查询的起始位置
$optionArray = array_merge($optionArray, array('offset' => $queryOffset, 'limit' => $processCount));
$queryOffset += $processCount;
// 查询数据
$srcRecordList = $srcTable->select($srcField, $filter, $optionArray);
// 转换数据到目标表中
foreach ($srcRecordList as $srcRecordItem) {
// PreFunc 处理
if ($recordPreFunc) {
$recordPreFunc($srcRecordItem);
}
$dstTable = new DstDataMapper($dstTableName);
//字段复制
foreach ($columnMap as $srcField => $dstField) {
// 无意义的字段，不复制
if (null == $dstField) {
continue;
}
if (isset($srcValueConvertFuncArray) && isset($srcValueConvertFuncArray[$srcField])) {
if (!is_callable($srcValueConvertFuncArray[$srcField])) {
printLog($srcField . ' value convert is not function ', self::$loggerSource, \Core\Log\Base::ERROR);
continue;
}
// 做数据转化
$dstTable->{$dstField} = $srcValueConvertFuncArray[$srcField]($srcRecordItem[$srcField], $srcRecordItem);
} else {
$dstTable->{$dstField} = $srcRecordItem[$srcField];
}
}
// postFunc 处理
if ($recordPostFunc) {
$recordPostFunc($srcRecordItem, $dstTable);
}
$dstTable->save();
unset($dstTable);
// 及时释放数据，优化内存使用
}
unset($srcRecordList);
// 及时释放数据，优化内存使用
gc_collect_cycles();
// 主动做一次垃圾回收
}
printLog('finish convertTable ' . $srcTableName . '-->' . $dstTableName . ' totalRecordCount:' . $totalRecordCount, self::$loggerSource);
}
/**
*
* 取得一组记录的数目，用于分页
*
* @return int 查询条数
*
* @param mixed $table 需要查询的数据表，可以是单个表，例如：'user'，
* 也可以是多个表的数组，例如：array('user' ,'order_info' => 'oi')
*
* @param array $condArray 查询条件数组，例如：
* array(
* array('supplier_id = ?', $supplier_id)
* array('is_on_sale = ?', 1)
* array('supplier_price > ? or supplier_price < ?', $priceMin, $priceMax)
* )
* 这些查询条件最终会用 and 拼接起来
* @param array $optionArray 目前不支持 Having 查询，留待以后扩展
* @param int $ttl 缓存多少时间
*
*/
public function _countArray($table, array $condArray = null, array $optionArray = null, $ttl = 0)
{
// 构造参数验证数组
$validatorArray = array();
$validatorArray['table'] = $table;
$validatorArray['ttl'] = $ttl;
if (null != $condArray) {
$validatorArray['condArray'] = $condArray;
}
if (null != $optionArray) {
$validatorArray['optionArray'] = $optionArray;
}
// 参数验证
$validator = new Validator($validatorArray, '');
$table = $validator->required()->validate('table');
$ttl = $validator->digits()->min(0)->validate('ttl');
if (null != $condArray) {
$condArray = $validator->requireArray(false)->validate('condArray');
}
if (null != $optionArray) {
$optionArray = $validator->requireArray(false)->validate('optionArray');
}
$this->validate($validator);
// 构造查询条件
$filter = null;
if (!empty($condArray)) {
$filter = QueryBuilder::buildAndFilter($condArray);
}
// 创建 DataMapper
$dataMapper = new DataMapper($table);
if (is_string($table)) {
//简单的单表查询
$table = array($table);
}
if (is_array($table)) {
// 复杂的多表查询
return $dataMapper->selectCount($table, $filter, $optionArray, $ttl);
}
throw new \InvalidArgumentException('table should be string or array');
}
/**
* 取得对应分类下面商品的总数，用于分页显示
*
* @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);
}
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;
}
