Custom Query (1134 matches)

drop procedure mismatchQuery;

DELIMITER $$

CREATE PROCEDURE `mismatchQuery` (`min` INT, `max` INT, `searchName` VARCHAR(100), `searchSerial` VARCHAR(100), `searchComment` VARCHAR(10000))
BEGIN
set @prevtotal := 0;
set @mintotal := 0;
select * from (select *  from
(select products.serial, history.uid, history.date, hist1.total from history inner join
(select uid, count(uid) as total from history where ((find_in_set ('mismatch', `history`.`type_write`) and (`history`.`status` is null or `history`.`status` = 'fail'))
    or (find_in_set ('mismatch', `history`.`type_write`) and `history`.`status` = 'ok' and `history`.bond is null)) 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 ((find_in_set ('mismatch', `history`.`type_write`) and (`history`.`status` is null or `history`.`status` = 'fail'))
    or (find_in_set ('mismatch', `history`.`type_write`) and `history`.`status` = 'ok' and `history`.bond is null)) 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 ;

Необходимо изменить adcproducts_tables.sql Также этот тикет связан с тикетом: https://trac.adc-line.ru/mc-04/ticket/1254#ticket.

Не загружает данные таблицы когда снята галочка "Показывать только несоответствия"

drop procedure mismatchQuery;

DELIMITER $$

CREATE PROCEDURE `mismatchQuery` (`min` INT, `max` INT, `searchName` VARCHAR(100), `searchSerial` VARCHAR(100), `searchComment` VARCHAR(10000))
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 ((find_in_set ('mismatch', `history`.`type_write`) and (`history`.`status` is null or `history`.`status` = 'fail'))
    or (find_in_set ('mismatch', `history`.`type_write`) and `history`.`status` = 'ok' and `history`.bond is null)) 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 ((find_in_set ('mismatch', `history`.`type_write`) and (`history`.`status` is null or `history`.`status` = 'fail'))
    or (find_in_set ('mismatch', `history`.`type_write`) and `history`.`status` = 'ok' and `history`.bond is null)) 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 ;

Алексей, как ты считаешь? Лучше упразднить столбцы "ОТК", "Тестирование", "Местоположение" в таблице Products в бд, как столбец "Несоответствия" (сейчас он генерируется на Главной из таблицы history в бд), и генерировать их из таблицы history в бд или оставить их?