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 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122
| SELECT
exceptions.id,
exceptions.exception_field_value,
exceptions.priority,
exceptions.is_inclusif,
exceptions.exception_field_id,
exceptions_languages.short_description,
exceptions_languages.long_description,
function_exceptions.function
FROM (
SELECT
PosException.id,
PosException.exception_field_value,
PosException.priority,
PosException.is_inclusif,
PosException.exception_field_id,
PosException.function_exception_id,
( SELECT COUNT(conditions.exception_field_id)
FROM conditions
where
PosException.id = conditions.exception_id
AND conditions.exception_field_id =9
AND conditions.status_id <> 10
) as countCondition_9,
( SELECT COUNT(conditions.exception_field_id)
FROM conditions
INNER JOIN condition_values
ON
conditions.id = condition_values.condition_id
AND condition_values.status_id <> 10
AND condition_values.value IN (337,353,431,475,511,632,638,633,634,635,640,661,669,1880,1878,1883,1882)
WHERE
PosException.id = conditions.exception_id
AND conditions.exception_field_id = 9
AND conditions.status_id <> 10
Group by conditions.exception_field_id
) as countConditionValue_9,
( SELECT COUNT(conditions.exception_field_id)
FROM conditions
where
PosException.id = conditions.exception_id
AND conditions.exception_field_id =46
AND conditions.status_id <> 10
) as countCondition_46,
( SELECT COUNT(conditions.exception_field_id)
FROM conditions
INNER JOIN condition_values
ON
conditions.id = condition_values.condition_id
AND condition_values.status_id <> 10
AND condition_values.value IN (102,104,106,210,212,214,235,247,321,331,337,339,348,356,357,374,500,515,1224,1226)
WHERE
PosException.id = conditions.exception_id
AND conditions.exception_field_id = 46
AND conditions.status_id <> 10
Group by conditions.exception_field_id
) as countConditionValue_46
FROM exceptions AS PosException
WHERE
(
(PosException.exception_type_id =5)
AND (PosException.status_id = 6)
AND (PosException.family_id = 7 )
AND (((PosException.valid_to >= '2011-11-14 09:05:59') OR (PosException.valid_to IS NULL)))
AND (((PosException.valid_from <= '2011-11-14 09:05:59') OR (PosException.valid_from IS NULL)))
AND (((PosException.customer_parent_id =1 ) OR (PosException.customer_parent_id IS NULL)))
)
AND (PosException.exception_field_id in ( 71 ))
AND PosException.priority BETWEEN 0 AND 100
)
as exceptions
INNER JOIN
exceptions_languages
ON
exceptions_languages.exception_id = exceptions.id
AND exceptions_languages.language_id = 1
LEFT JOIN
function_exceptions
on
exceptions.function_exception_id = function_exceptions.id
INNER JOIN
exception_details AS ed_8
ON
ed_8.exception_id = exceptions.id
AND ed_8.exception_field_id = 8
AND (ed_8.count = 0 OR FIND_IN_SET(63 ,ed_8.values))
INNER JOIN
exception_details AS ed_11
ON
ed_11.exception_id = exceptions.id
AND ed_11.exception_field_id = 11
AND (ed_11.count = 0 OR FIND_IN_SET(1 ,ed_11.values))
INNER JOIN
exception_details AS ed_44
ON
ed_44.exception_id = exceptions.id
AND ed_44.exception_field_id = 44
AND (ed_44.count = 0)
INNER JOIN
exception_details AS ed_45
ON
ed_45.exception_id = exceptions.id
AND ed_45.exception_field_id = 45
AND (
ed_45.count = 0
OR FIND_IN_SET(78 ,ed_45.values)
OR FIND_IN_SET(79 ,ed_45.values)
OR FIND_IN_SET(81 ,ed_45.values)
OR FIND_IN_SET(82 ,ed_45.values)
)
WHERE
((exceptions.countCondition_9 = 0) OR (exceptions.countCondition_9 > 0 AND exceptions.countConditionValue_9 >= exceptions.countCondition_9))
AND
((exceptions.countCondition_46 = 0) OR (exceptions.countCondition_46 > 0 AND exceptions.countConditionValue_46 >=exceptions.countCondition_46))
ORDER BY exceptions.priority |
Partager