Changes between Initial Version and Version 1 of Ticket #1172


Ignore:
Timestamp:
May 30, 2023, 10:29:49 AM (3 years ago)
Author:
Denis_N
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #1172

    • Property Priority minor → critical
    • Property Summary Необходимо изменить процедуры для работы ревизии № → Необходимо изменить процедуры для работы ревизии r242/base

  • Ticket #1172 – Description

    initial v1  
    1 Необходимо изменить процедуры для работы ревизии №, где был исправлен баг: несоответствия должны выводить только непустые комментарии типа Запись, а выводят и пустые тоже
     1Необходимо изменить процедуры для работы ревизии r242/base, где был исправлен баг: несоответствия должны выводить только непустые комментарии типа Запись, а выводят и пустые тоже
     2
     3
     4
     5drop procedure mismatchQuery;
     6
     7DELIMITER }
     8
     9CREATE PROCEDURE mismatchQuery(min int, max int, searchName varchar (100), searchSerial varchar (100), searchComment varchar (10000)) READS SQL DATA
     10BEGIN
     11set @prevtotal := 0;
     12set @mintotal := 0;
     13select * from (select *, @mintotal as mintotal, @prevtotal := @mintotal - total as prevtotal from
     14(select products.serial, history.uid, history.date, hist1.total from history inner join
     15(select uid, count(uid) as total from history where (type_write = "mismatch" or type_write = 'record' or type_write = "repair" or
     16(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
     17inner join products on products.uid = history.uid where (products.name like searchName
     18and 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
     19(type_write = "otk" and status = "fail") or (type_write = "testing" and status = "fail" )) and comment is not null and comment != '' group by uid )
     20group by products.serial, history.uid, history.date, hist1.total order by history.date desc) as hist2
     21where (@mintotal := @minTotal + total)) as hust2 where hust2.mintotal > min and hust2.prevtotal < max;
     22
     23END}
     24DELIMITER ;
     25
     26
     27-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     28
     29
     30drop procedure mismatchQueryWithoutOtkRows;
     31
     32DELIMITER }
     33
     34CREATE PROCEDURE mismatchQueryWithoutOtkRows(min int, max int, searchName varchar (100), searchSerial varchar (100), searchComment varchar (10000)) READS SQL DATA
     35BEGIN
     36set @prevtotal := 0;
     37set @mintotal := 0;
     38select * from (select *, @mintotal as mintotal, @prevtotal := @mintotal - total as prevtotal from
     39(select products.serial, history.uid, history.date, hist1.total from history inner join
     40(select uid, count(uid) as total from history where (type_write = "mismatch" or type_write = 'record'
     41or 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
     42inner join products on products.uid = history.uid where (products.name like searchName
     43and products.serial like searchSerial and history.comment like searchComment) and (history.uid, history.date) in
     44(select uid, max(date) from history where (type_write = "mismatch"  or type_write = 'record' or type_write = "repair" or
     45(type_write = "testing" and status = "fail") or `history`.type_write = 'record') and comment is not null and  comment != '' group by uid)
     46group by products.serial, history.uid, history.date, hist1.total order by history.date desc) as hist2
     47where (@mintotal := @minTotal + total)) as hust2 where hust2.mintotal > min and hust2.prevtotal < max;
     48
     49END}
     50DELIMITER ;
    251
    352