1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
|
SELECT tmp.customer_group_id AS id
FROM
(SELECT customer_group_id,
customer_group_order,
customer_id,
customer_visualized,
customer_group_is_active,
customer_group_banned,
customer_group_action_actived,
customer_status_fk,
file_settings.id AS file_settings_id,
customer_group_from_customer_true AS from_customer_true,
customer_order_fk
FROM customer_group
JOIN customer ON customer_group_fk = customer_group_id
JOIN customer_file_entry ON customer_file_entry_customer_fk = customer_id
JOIN file_entry ON customer_file_entry_file_entry_fk = file_entry_id
JOIN FILE ON file_id = file_entry_file_fk
JOIN file_settings ON file_settings.id = file_settings_fk
LEFT JOIN file_role_allowed ON file_entry_file_fk = file_role_allowed.file_fk
WHERE 1
AND customer_group_is_expired IS FALSE
AND customer_type_fk = :customerType
AND customer_status_fk IN (:customerstatusFKs)
GROUP BY customer_group_id,
customer_group_order,
customer_group_is_active,
customer_id,
customer_visualized,
customer_status_fk,
customer_group_from_customer_true,
customer_group_banned,
customer_group_action_actived,
file_settings_id,
customer_order_fk
HAVING MAX(file_role_allowed.file_fk) IS NULL
OR MAX(:role_1 = file_role_allowed.role_role) IS FALSE
ORDER BY NULL) AS tmp
JOIN order_customer ON tmp.customer_order_fk = order_id
JOIN file_settings ON tmp.file_settings_id = file_settings.id
LEFT JOIN bm ON tmp.customer_id = bm_customer_fk
LEFT JOIN customer_received_order ON tmp.customer_id = customer_received_order_customer_fk
LEFT JOIN share_order_preferences ON customer_received_order_received_order_fk = share_order_preferences.id
WHERE 1
GROUP BY tmp.customer_group_id,
tmp.customer_group_is_active,
tmp.customer_group_banned,
tmp.from_customer_enabled,
tmp.customer_group_action_actived,
tmp.customer_group_order
ORDER BY MIN(color_fk) ASC, MAX(priority) ASC, tmp.customer_group_is_active DESC,
MAX(order_datetime) DESC, NULL |
Partager