Opened 18 months ago

Closed 17 months ago

Last modified 17 months ago

#1175 closed улучшение (fixed)

Для ревизии 252 необходимо обновить код процедур для страницы "Несоответствия"

Reported by: Denis_N Owned by: san
Priority: critical Component: БД изделий АДС
Keywords: Cc:

Description (last modified by Denis_N)

r252/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 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 Denis_N, 18 months ago

Description: modified (diff)
Summary: Для ревизии необходимо обновить код процедур для страницы "Несоответствия"Для ревизии 252 необходимо обновить код процедур для страницы "Несоответствия"

comment:2 by san, 17 months ago

Priority: majorcritical

comment:3 by san, 17 months ago

mismatchQuery заменить не вышло.

Ответ MySQL:
#1064 - У вас ошибка в запросе. Изучите документацию по используемой версии MySQL на предмет корректного синтаксиса около 'group by uid) as hist1 on history.uid = hist1.uid
inner join products on produc' на строке 8

comment:4 by san, 17 months ago

mismatchQueryWithoutOtkRows та же история

comment:5 by Denis_N, 17 months ago

Description: modified (diff)

comment:6 by Denis_N, 17 months ago

Description: modified (diff)

comment:7 by Denis_N, 17 months ago

Last edited 17 months ago by Denis_N (previous) (diff)

comment:8 by Denis_N, 17 months ago

Почему-то TRAC изменяет текст, и поэтому нельзя скопировать и вставить отсюда

comment:9 by Denis_N, 17 months ago

Last edited 17 months ago by Denis_N (previous) (diff)

comment:10 by Denis_N, 17 months ago

Description: modified (diff)

comment:11 by Denis_N, 17 months ago

Изменил описание тикета, вставив код процедур, как блок кода. У меня работает

Last edited 17 months ago by Denis_N (previous) (diff)

comment:12 by san, 17 months ago

Теперь всё получилось.

comment:13 by san, 17 months ago

Resolution: fixed
Status: assignedclosed

In 272/base:

Изменения в структуре SQL

  • добавлены дефолтные значения для products.type и history.type
  • добавлены процедуры для интерфейса Несоответствия ( Closes #1175 )

in reply to:  13 comment:14 by alx, 17 months ago

Replying to san:

  • добавлены процедуры для интерфейса Несоответствия ( Closes #1175 )

??? Насколько я вижу, процедуры удалены, а не добавлены...

comment:15 by san, 17 months ago

Оу. Действительно...
Это странно, спасибо за замечание, сейчас проверю что пошло не так....

comment:16 by san, 17 months ago

In 287/base:

Добавлены процедуры для интерфейса Несоответствия, которые по ошибке были удалены в r272/base ( Closes #1175 )

Note: See TracTickets for help on using tickets.