1 | | |
2 | | {{{ |
3 | | drop procedure mismatchQueryWithoutOtkRows; |
4 | | |
5 | | DELIMITER } |
6 | | |
7 | | CREATE PROCEDURE mismatchQueryWithoutOtkRows(min int, max int, searchName varchar (100), searchSerial varchar (100), searchComment varchar (10000)) READS SQL DATA |
8 | | BEGIN |
9 | | set @prevtotal := 0; |
10 | | set @mintotal := 0; |
11 | | select * from (select *, @mintotal as mintotal, @prevtotal := @mintotal - total as prevtotal from |
12 | | (select products.serial, history.uid, history.date, hist1.total from history inner join |
13 | | (select uid, count(uid) as total from history where (type_write = "mismatch" or type_write = 'record' |
14 | | or type_write = "repair" or (type_write = "testing" and status = "fail")) and comment is not null and comment != '' group by uid) as hist1 on history.uid = hist1.uid |
15 | | inner join products on products.uid = history.uid where (products.name like searchName |
16 | | and products.serial like searchSerial and history.comment like searchComment) and (history.uid, history.date) in |
17 | | (select uid, max(date) from history where (type_write = "mismatch" or type_write = 'record' or type_write = "repair" or |
18 | | (type_write = "testing" and status = "fail") or `history`.type_write = 'record') and history.comment like searchComment and comment is not null and comment != '' group by uid) |
19 | | group by products.serial, history.uid, history.date, hist1.total order by history.date desc) as hist2 |
20 | | where (@mintotal := @minTotal + total)) as hust2 where hust2.mintotal > min and hust2.prevtotal < max; |
21 | | |
22 | | END} |
23 | | DELIMITER ; |
24 | | |
25 | | }}} |