#1175 closed улучшение (fixed)
Для ревизии 252 необходимо обновить код процедур для страницы "Несоответствия"
Reported by: | Denis_N | Owned by: | san |
---|---|---|---|
Priority: | critical | Component: | БД изделий АДС |
Keywords: | Cc: |
Description (last modified by )
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 history.comment like searchComment 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 history.comment like searchComment 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 ;
Change History (16)
comment:1 by , 18 months ago
Description: | modified (diff) |
---|---|
Summary: | Для ревизии необходимо обновить код процедур для страницы "Несоответствия" → Для ревизии 252 необходимо обновить код процедур для страницы "Несоответствия" |
comment:2 by , 18 months ago
Priority: | major → critical |
---|
comment:3 by , 18 months ago
comment:5 by , 18 months ago
Description: | modified (diff) |
---|
comment:6 by , 18 months ago
Description: | modified (diff) |
---|
comment:7 by , 18 months ago
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 history.comment like searchComment 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 ;
comment:8 by , 18 months ago
Почему-то TRAC изменяет текст, и поэтому нельзя скопировать и вставить отсюда
comment:10 by , 18 months ago
Description: | modified (diff) |
---|
comment:11 by , 18 months ago
Изменил описание тикета, вставив код процедур, как блок кода. У меня работает
follow-up: 14 comment:13 by , 18 months ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
In 272/base:
comment:14 by , 18 months ago
comment:15 by , 18 months ago
Оу. Действительно...
Это странно, спасибо за замечание, сейчас проверю что пошло не так....
mismatchQuery заменить не вышло.