1 回答

TA貢獻1877條經驗 獲得超1個贊
$institution = $request['inst']??'';
$start = $request['start'];
$end = $request['end'];
$joins = [
1 => ['date_field' => 'meeting_set_first', 'is_active' => false],
2 => ['date_field' => 'occurred_date', 'is_active' => true],
3 => ['date_field' => 'not_show_date', 'is_active' => true],
4 => ['date_field' => 'cancel_meeting_date', 'is_active' => true],
5 => ['date_field' => 'sale_date', 'is_active' => true],
6 => ['date_field' => 'cancel_sale_date', 'is_active' => true],
7 => ['date_field' => 'extended_date', 'is_active' => false],
8 => ['date_field' => 'meeting_set_date', 'is_active' => true],
];
$sql = 'SELECT 0 as type ,ll.id as lead_name, ll.name, ll.email, ll.updated_at, u.employee_name, ls.meeting_set_date, ls.meeting_set_first FROM customers ll LEFT JOIN leads_statistics ls ON ll.id = ls.lead_id LEFT JOIN users u ON ll.userUpdate = u.employee_id WHERE '.($institution?" ll.inst_id = :inst0 AND ":'').' ll.created_at BETWEEN :start0 AND :end0 UNION ';
if ($institution) {
$placeHolders[':inst0'] = $institution;
}
$placeHolders[':start0'] = $start;
$placeHolders[':end0'] = $end;
$queryJoin = [];
foreach($joins as $key => $join) {
$queryJoin[] = 'SELECT '.$key.' as type, ll.id, ll.name, ll.email, ll.updated_at, u.employee_name, ls.meeting_set_date, ls.meeting_set_first FROM leads_statistics ls LEFT JOIN customers ll ON ls.lead_id = ll.id LEFT JOIN users u ON ll.userUpdate = u.employee_id WHERE '.($institution?" ll.inst_id = :inst$key AND ":'').($join['is_active']?' ls.is_active = 1 AND ':'').' ls.'.$join['date_field'] ." BETWEEN :start$key AND :end$key";
if ($institution) {
$placeHolders[':inst'.$key] = $institution;
}
$placeHolders[':start'.$key] = $start;
$placeHolders[':end'.$key] = $end;
}
$sql .= implode(' UNION ', $queryJoin);
$leads = DB::select($sql, $placeHolders);
- 1 回答
- 0 關注
- 135 瀏覽
添加回答
舉報