Начну с себя.
SELECT
'1' as type,
c.id,c.contract_nr,c.clerk_code,c.datetime,
CONCAT(c.clerk_name, ' ', c.clerk_surname) as clerk_fullname,
CONCAT(c.contact_name, ' ', c.contact_surname) as client,
IF(c.archive = '+', 1, 0) as archive,
DATEDIFF(NOW(), c.datetime) as diff,
(SELECT COUNT(*) as count FROM private inter WHERE inter.contract_id = c.id AND (inter.porting NOT LIKE "denied" AND inter.porting NOT LIKE "canceled")) as activeCount,
IF(s.status,s.status,
IF(c.clerk_code <>'DBA' AND m.pack_status IN (1,2), 5,
IF(c.clerk_code ='DBA' AND m.pack_status IN (1,2), 1,
IF(m.pack_status = 3, 2,
IF(m.pack_status=4 AND c.archive='-', 3,
IF(c.archive = '+', 4,0
)))))) as status
FROM p_contracts c
LEFT JOIN mode_on_request m ON m.contract_id = c.id
LEFT JOIN p_logistic_status s ON s.id = c.id
WHERE c.cancelled = 0 AND m.web_request = 0 AND
m.pack_status=4 AND c.archive='-'
AND
IF(s.status IS NULL,1,IF(s.status = 3,1,0)) = 1 AND c.datetime > '2011-07-01 00:00:00' AND c.datetime < '2011-09-07 23:59:59' HAVING activeCount > 0 UNION SELECT
'2' as type,
c.id,c.contract_nr,c.clerk_code,c.datetime,
CONCAT(c.clerk_name, ' ', c.clerk_surname) as clerk_fullname,
CONCAT(c.contact_name, ' ', c.contact_surname) as client,
IF(c.archive = '+', 1, 0) as archive,
DATEDIFF(NOW(), c.datetime) as diff,
'7' as activeCount ,
IF(s.status,s.status,
IF(c.sent_datetime > 0 AND cc.tracking_id = 0, 1,
IF(cc.tracking_id > 0 AND cc.dpd_status <> 'OK', 2,
IF(cc.dpd_status ='OK' AND c.archive='-', 3,
IF(c.archive = '+', 4,
IF(c.sent_datetime = 0 AND c.review_datetime >0 ,5,0
))))))
as status
FROM w_contracts c
JOIN w_contracts_connections cc ON cc.contract_id = c.id
LEFT JOIN p_logistic_status s ON s.id = c.id
WHERE c.datetime < NOW() AND c.cancelled = 0
AND cc.porting <> 'denied' AND cc.porting <> 'cancelled' AND
cc.dpd_status ='OK' AND c.archive='-'
AND
IF(s.status IS NULL,1,IF(s.status = 3,1,0)) = 1 AND c.datetime > '2011-07-01 00:00:00' AND c.datetime < '2011-09-07 23:59:59' UNION SELECT
'3' as type,
c.id,c.contract_nr,c.clerk_code,c.datetime,
CONCAT(c.clerk_name, ' ', c.clerk_surname) as clerk_fullname,
CONCAT(c.contact_name, ' ', c.contact_surname) as client,
IF(c.archive = '+', 1, 0) as archive,
DATEDIFF(NOW(), c.datetime) as diff,
(SELECT COUNT(*) as count FROM private inter
WHERE inter.contract_id = c.id
AND (inter.porting NOT LIKE "denied" AND inter.porting NOT LIKE "canceled")) as activeCount,
IF(s.status,s.status,
IF(c.sent_datetime > 0 AND c.activated_datetime = 0, 5,
IF(c.sent_datetime =0 AND c.delay > 6, 2,
IF(c.activated_datetime > 0 AND c.archive='-', 3,
IF(c.archive = '+', 4,0
))))) as status
FROM p_contracts c
LEFT JOIN p_logistic_status s ON s.id = c.id
WHERE c.cancelled = 0 AND
c.activated_datetime > 0 AND c.archive='-'
AND
IF(s.status IS NULL,1,IF(s.status = 3,1,0)) = 1 AND c.datetime > '2011-07-01 00:00:00' AND c.datetime < '2011-09-07 23:59:59' HAVING activeCount > 0 UNION SELECT
'4' as type,
c.id,c.contract_nr,c.clerk_code,c.datetime,
CONCAT(c.clerk_name, ' ', c.clerk_surname) as clerk_fullname,
CONCAT(c.contact_name, ' ', c.contact_surname) as client,
IF(c.archive = '+', 1, 0) as archive,
DATEDIFF(NOW(), c.datetime) as diff,
(SELECT COUNT(*) as count FROM con_connections inter
WHERE inter.contract_id = c.id
AND (inter.porting NOT LIKE "denied" AND inter.porting NOT LIKE "canceled")) as activeCount,
IF(s.status,s.status,
IF(c.sent_datetime > 0 AND c.activated_datetime = 0, 5,
IF(c.sent_datetime =0 AND c.delay > 6, 2,
IF(c.activated_datetime > 0 AND c.archive='-', 3,
IF(c.archive = '+', 4,0
))))) as status
FROM contracts c
LEFT JOIN p_logistic_status s ON s.id = c.id
WHERE (clerk_code LIKE '%S%' OR clerk_code LIKE '%R%') AND c.cancelled = 0 AND
c.activated_datetime > 0 AND c.archive='-'
AND
IF(s.status IS NULL,1,IF(s.status = 3,1,0)) = 1 AND c.datetime > '2011-07-01 00:00:00' AND c.datetime < '2011-09-07 23:59:59' HAVING activeCount > 0