$res= db('admin_login_record')
->alias('login')
->join('__ADMIN_USER__ user','user.id=login.create_user_id','LEFT')
->where(function ($query) use ($param) {
if (!empty($param['startTime'])) $query->where('login.create_time', '>=', strtotime($param['startTime']));
if (!empty($param['endTime'])) $query->where('login.create_time', '<=', strtotime($param['endTime']));
if (!empty($param['userIds'])) $query->whereIn('login.create_user_id', $param['userIds']);
})
->field('login.*,user.realname as username')
->page($param['page'],$param['limit'])
->order('login.id', 'desc')
->select();
$sql = CustomerModel::alias('a')
->field([
"FROM_UNIXTIME(`a`.`create_time`, '{$time['time_format']}')" => 'type',
'COUNT(*)' => 'customer_num',
'SUM(
CASE WHEN ISNULL(`b`.`order_date`) THEN 0 ELSE (
UNIX_TIMESTAMP(`b`.`order_date`) - `a`.`create_time`
) / 86400 END
)' => 'cycle_sum'
])
->join(
"(
SELECT
`customer_id`, MIN(`order_date`) AS `order_date`
FROM
`{$prefix}crm_contract`
WHERE
`check_status` = 2
GROUP BY
`customer_id`
) b",
'`a`.`customer_id` = `b`.`customer_id`',
'LEFT'
)
->where([
'a.deal_status' => ['IN', ['已到诊', '已到诊(流失)', '已到诊(流回)']],
'a.create_time' => ['BETWEEN', $time['between']],
'a.owner_user_id' => ['IN', !empty($userIds) ? $userIds : '9999999999']
])
->group('type')
->fetchsql()
->select();
$res = queryCache($sql);
$res = array_column($res, null, 'type');
$where = $this->getWhere($request);
$join = [
['__CRM_CONTRACT__ contract', 'contract.contract_id = a.contract_id', 'LEFT'],
['__ADMIN_USER__ user', 'user.id = contract.owner_user_id', 'LEFT'],
['__CRM_PRODUCT__ product', 'product.product_id = a.product_id', 'LEFT'],
['__CRM_PRODUCT_CATEGORY__ product_category', 'product_category.category_id = product.category_id', 'LEFT'],
];
$sql = db('crm_contract_product')
->alias('a')
->where($where)
->join($join)
->group('a.product_id')
->field('a.product_id,sum(a.num) as num,product.name as product_name,product_category.name as category_id_info,product_category.category_id')
->fetchSql()
->select();
$list = queryCache($sql);
$data = [];
foreach ($list AS $key => $value) {
if (empty($data[$value['category_id']])) {
$data[$value['category_id']] = $value;
} else {
$data[$value['category_id']]['num'] += $value['num'];
}
} ```
php 多表查询
最新推荐文章于 2023-07-27 15:21:25 发布