id	summary	reporter	owner	description	type	status	priority	component	resolution	keywords	cc
1172	Необходимо изменить процедуры для работы ревизии r242/base	Denis_N	Denis_N	"Необходимо изменить процедуры для работы ревизии r242/base, где был исправлен баг: несоответствия должны выводить только непустые комментарии типа Запись, а выводят и пустые тоже



drop procedure mismatchQuery;

DELIMITER }

CREATE PROCEDURE mismatchQuery(min int, max int, searchName varchar (100), searchSerial varchar (100), searchComment varchar (10000)) READS SQL DATA
BEGIN
set @prevtotal := 0;
set @mintotal := 0;
select * from (select *, @mintotal as mintotal, @prevtotal := @mintotal - total as prevtotal from
(select products.serial, history.uid, history.date, hist1.total from history inner join
(select uid, count(uid) as total from history where (type_write = ""mismatch"" or type_write = 'record' or type_write = ""repair"" or
(type_write = ""otk"" and status = ""fail"") or (type_write = ""testing"" and status = ""fail"")) and comment is not null  and  comment != '' group by uid) as hist1 on history.uid = hist1.uid
inner join products on products.uid = history.uid where (products.name like searchName
and products.serial like searchSerial and history.comment like searchComment) and (history.uid, history.date) in (select uid, max(date) from history where (type_write = ""mismatch"" or type_write = 'record' or type_write = ""repair"" or
(type_write = ""otk"" and status = ""fail"") or (type_write = ""testing"" and status = ""fail"" )) and comment is not null and comment != '' group by uid )
group by products.serial, history.uid, history.date, hist1.total order by history.date desc) as hist2
where (@mintotal := @minTotal + total)) as hust2 where hust2.mintotal > min and hust2.prevtotal < max;

END}
DELIMITER ;


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------


drop procedure mismatchQueryWithoutOtkRows;

DELIMITER }

CREATE PROCEDURE mismatchQueryWithoutOtkRows(min int, max int, searchName varchar (100), searchSerial varchar (100), searchComment varchar (10000)) READS SQL DATA
BEGIN
set @prevtotal := 0;
set @mintotal := 0;
select * from (select *, @mintotal as mintotal, @prevtotal := @mintotal - total as prevtotal from
(select products.serial, history.uid, history.date, hist1.total from history inner join
(select uid, count(uid) as total from history where (type_write = ""mismatch"" or type_write = 'record' 
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
inner join products on products.uid = history.uid where (products.name like searchName
and products.serial like searchSerial and history.comment like searchComment) and (history.uid, history.date) in 
(select uid, max(date) from history where (type_write = ""mismatch""  or type_write = 'record' or type_write = ""repair"" or
(type_write = ""testing"" and status = ""fail"") or `history`.type_write = 'record') and comment is not null and  comment != '' group by uid)
group by products.serial, history.uid, history.date, hist1.total order by history.date desc) as hist2
where (@mintotal := @minTotal + total)) as hust2 where hust2.mintotal > min and hust2.prevtotal < max;

END}
DELIMITER ;




"	дефект	closed	critical	БД изделий АДС	дубликат