Ускорение режима все партии
Материал из wiki.standart-n.ru
Версия от 10:18, 26 апреля 2017; Agk (обсуждение | вклад)
Выполнить скрипт
/******************************************************************************/
/*** Generated by IBExpert 08.03.2016 11:37:13 ***/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE TABLE WAREBASEPARTS (
PART_ID DM_ID NOT NULL /* DM_ID = BIGINT */,
WARE_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
SNAME DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SIZG DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SCOUNTRY DM_TEXT /* DM_TEXT = VARCHAR(250) */,
ORIG_CODE DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SORIG_NAME DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SORIG_IZG DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SORIG_COUNTRY DM_TEXT /* DM_TEXT = VARCHAR(250) */,
BCODE_IZG DM_TEXT /* DM_TEXT = VARCHAR(250) */,
PRICE DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
PRICE_O DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
PRICE_Z DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
PRICE_R DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
QUANT DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
BARCODE DM_TEXT /* DM_TEXT = VARCHAR(250) */,
BARCODE1 DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DEP DM_ID /* DM_ID = BIGINT */,
KRITK DM_ID /* DM_ID = BIGINT */,
GODENDO DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
SERIA DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SUM_NDSO DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
SERT DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DATESERT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
KEMVSERT DM_TEXT /* DM_TEXT = VARCHAR(250) */,
SDSERT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
REGN DM_TEXT /* DM_TEXT = VARCHAR(250) */,
NGTD DM_TEXT /* DM_TEXT = VARCHAR(250) */,
EDIZM DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DOC_ID DM_ID /* DM_ID = BIGINT */,
DOCNUM DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DOCDATE DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
DOCCAPTION DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DOCAGENT DM_TEXT /* DM_TEXT = VARCHAR(250) */,
DOCVNUM DM_ID /* DM_ID = BIGINT */,
DOCVSHIFT DM_ID /* DM_ID = BIGINT */,
INSERTDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
UPDATEDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
ENDDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
NDS DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
REALQUANT DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
PART_PARENT_ID DM_ID /* DM_ID = BIGINT */,
NAME_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
IZG_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
COUNTRY_ID DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
NAC DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
BLOCK_QUANT DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
BLOCK_COUNT DM_STATUS /* DM_STATUS = INTEGER */,
MMBSH DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
PART_TYPE DM_STATUS /* DM_STATUS = INTEGER */,
BASE_AGENT_ID DM_ID /* DM_ID = BIGINT */,
SBASE_AGENT_ID DM_TEXT /* DM_TEXT = VARCHAR(250) */,
CONTRACT_ID DM_ID /* DM_ID = BIGINT */,
SCONTRACT_ID DM_TEXT /* DM_TEXT = VARCHAR(250) */,
GROUP_ID DM_ID /* DM_ID = BIGINT */,
ISFOLDER DM_STATUS /* DM_STATUS = INTEGER */,
MNN DM_TEXT /* DM_TEXT = VARCHAR(250) */
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE WAREBASEPARTS ADD CONSTRAINT PK_WAREBASEPARTS PRIMARY KEY (PART_ID);
/******************************************************************************/
/*** Indices ***/
/******************************************************************************/
CREATE INDEX WAREBASEPARTS_IDX1 ON WAREBASEPARTS (BCODE_IZG);
CREATE INDEX WAREBASEPARTS_IDX2 ON WAREBASEPARTS (SNAME);
CREATE INDEX WAREBASEPARTS_IDX3 ON WAREBASEPARTS (SERIA);
CREATE INDEX WAREBASEPARTS_IDX4 ON WAREBASEPARTS (SORIG_NAME);
CREATE INDEX WAREBASEPARTS_IDX5 ON WAREBASEPARTS (DOCAGENT);
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Views ***/
/******************************************************************************/
/* View: VW_WAREBASEPARTS */
CREATE OR ALTER VIEW VW_WAREBASEPARTS(
PART_ID,
WARE_ID,
SNAME,
SIZG,
SCOUNTRY,
ORIG_CODE,
SORIG_NAME,
SORIG_IZG,
SORIG_COUNTRY,
BCODE_IZG,
PRICE,
PRICE_O,
PRICE_Z,
PRICE_R,
QUANT,
BARCODE,
BARCODE1,
DEP,
KRITK,
GODENDO,
SERIA,
SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
DOC_ID,
DOCNUM,
DOCDATE,
DOCCAPTION,
DOCAGENT,
DOCVNUM,
DOCVSHIFT,
INSERTDT,
UPDATEDT,
ENDDT,
NDS,
REALQUANT,
PART_PARENT_ID,
NAME_ID,
IZG_ID,
COUNTRY_ID,
NAC,
BLOCK_QUANT,
BLOCK_COUNT,
MMBSH,
PART_TYPE,
BASE_AGENT_ID,
SBASE_AGENT_ID,
CONTRACT_ID,
SCONTRACT_ID,
GROUP_ID,
ISFOLDER,
MNN)
AS
select
wb.PART_ID,
wb.WARE_ID,
wb.SNAME,
wb.SIZG,
wb.SCOUNTRY,
wb.ORIG_CODE,
wb.SORIG_NAME,
wb.SORIG_IZG,
wb.SORIG_COUNTRY,
wb.BCODE_IZG,
wb.PRICE,
wb.PRICE_O,
wb.PRICE_Z,
wb.PRICE_R,
w.QUANT,
wb.BARCODE,
wb.BARCODE1,
wb.DEP,
wb.KRITK,
wb.GODENDO,
wb.SERIA,
wb.SUM_NDSO,
wb.SERT,
wb.DATESERT,
wb.KEMVSERT,
wb.SDSERT,
wb.REGN,
wb.NGTD,
wb.EDIZM,
wb.DOC_ID,
wb.DOCNUM,
wb.DOCDATE,
wb.DOCCAPTION,
wb.DOCAGENT,
wb.DOCVNUM,
wb.DOCVSHIFT,
wb.INSERTDT,
w.UPDATEDT,
wb.ENDDT,
wb.NDS,
w.REALQUANT,
wb.PART_PARENT_ID,
wb.NAME_ID,
wb.IZG_ID,
wb.COUNTRY_ID,
wb.NAC,
w.BLOCK_QUANT,
w.BLOCK_COUNT,
(select membership from PR_MEMBERSHIPS('PARTS=' || wb.part_id || ';PARTS.NAME_ID=' || wb.name_id || ';PARTS.IZG_ID=' || wb.izg_id ||';',ascii_char(13)||ascii_char(10),1)),
wb.PART_TYPE,
wb.BASE_AGENT_ID,
wb.SBASE_AGENT_ID,
wb.CONTRACT_ID,
wb.SCONTRACT_ID,
wb.GROUP_ID,
wb.ISFOLDER,
wb.MNN
from WAREBASEPARTS wb left join warebase w on w.part_id=wb.part_id
;
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
SET TERM ^ ;
create or alter procedure UPDPR_WAREBASEPARTS (
IN_PART_ID DM_ID,
IN_WARE_ID DM_UUID_NULL,
IN_DOC_ID DM_ID,
IN_AGENT_ID DM_ID)
as
declare variable PART_ID DM_ID not null;
declare variable WARE_ID DM_UUID not null;
declare variable SNAME DM_TEXT;
declare variable SIZG DM_TEXT;
declare variable SCOUNTRY DM_TEXT;
declare variable ORIG_CODE DM_TEXT;
declare variable SORIG_NAME DM_TEXT;
declare variable SORIG_IZG DM_TEXT;
declare variable SORIG_COUNTRY DM_TEXT;
declare variable BCODE_IZG DM_TEXT;
declare variable PRICE DM_DOUBLE;
declare variable PRICE_O DM_DOUBLE;
declare variable PRICE_Z DM_DOUBLE;
declare variable PRICE_R DM_DOUBLE;
declare variable QUANT DM_DOUBLE;
declare variable BARCODE DM_TEXT;
declare variable BARCODE1 DM_TEXT;
declare variable DEP DM_ID;
declare variable KRITK DM_ID;
declare variable GODENDO DM_DATETIME;
declare variable SERIA DM_TEXT;
declare variable SUM_NDSO DM_DOUBLE;
declare variable SERT DM_TEXT;
declare variable DATESERT DM_DATETIME;
declare variable KEMVSERT DM_TEXT;
declare variable SDSERT DM_DATETIME;
declare variable REGN DM_TEXT;
declare variable NGTD DM_TEXT;
declare variable EDIZM DM_TEXT;
declare variable DOC_ID DM_ID;
declare variable DOCNUM DM_TEXT;
declare variable DOCDATE DM_DATETIME;
declare variable DOCCAPTION DM_TEXT;
declare variable DOCAGENT DM_TEXT;
declare variable DOCVNUM DM_ID;
declare variable DOCVSHIFT DM_ID;
declare variable INSERTDT DM_DATETIME;
declare variable UPDATEDT DM_DATETIME;
declare variable ENDDT DM_DATETIME;
declare variable NDS DM_DOUBLE;
declare variable REALQUANT DM_DOUBLE;
declare variable PART_PARENT_ID DM_ID;
declare variable NAME_ID DM_UUID_NULL not null;
declare variable IZG_ID DM_UUID_NULL not null;
declare variable COUNTRY_ID DM_UUID_NULL not null;
declare variable NAC DM_DOUBLE;
declare variable BLOCK_QUANT DM_DOUBLE;
declare variable BLOCK_COUNT DM_STATUS;
declare variable MMBSH DM_TEXT1024;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable SBASE_AGENT_ID DM_TEXT;
declare variable CONTRACT_ID DM_ID;
declare variable SCONTRACT_ID DM_TEXT;
declare variable GROUP_ID DM_ID;
declare variable ISFOLDER DM_STATUS;
declare variable MNN DM_TEXT;
declare variable S DM_TEXT_BIG;
begin
s = 'select p.id, p.ware_id,
vname.svalue, vizg.svalue, vcountry.svalue, w.orig_code, vorig_name.svalue, vorig_izg.svalue, vorig_country.svalue, w.barcode,
p.price, p.price_o, p.price_z, p.price_r, 0, p.barcode, p.barcode1, p.dep,
(select intvalue from vals where id=w.name_id),
p.godendo, p.seria, p.sum_ndso, p.sert, p.datesert, p.kemvsert, p.sdsert, p.regn, p.ngtd, p.edizm, p.doc_id,
d.docnum, d.docdate, d.caption, a.caption, d.vnum, d.vshift,
p.insertdt, Null, p.enddt, p.nds, 0, p.parent_id,
w.name_id, w.izg_id, w.country_id,
p.nac,
0, 0,
(select membership from PR_MEMBERSHIPS(''PARTS='' || p.id || '';PARTS.NAME_ID='' || w.name_id || '';PARTS.IZG_ID='' || w.izg_id ||'';'',ascii_char(13)||ascii_char(10),1)),--mmbsh,
p.part_type, p.base_agent_id,
(select ab.caption from agents ab where ab.id=p.BASE_AGENT_ID),
p.contract_id,
(select caption from contracts c where c.id=p.contract_id), --scontract_id,
p.group_id, 1, vname.preparedvalue
from parts p
inner join WARES w on p.ware_id=w.id
inner join docs d on p.doc_id=d.id
inner join agents a on a.id = d.agent_id
inner join vals vname on w.name_id=vname.id
inner join vals vizg on w.izg_id=vizg.id
inner join vals vcountry on w.country_id=vcountry.id
inner join vals vorig_name on w.orig_name_id=vorig_name.id
inner join vals vorig_izg on w.orig_izg_id=vorig_izg.id
inner join vals vorig_country on w.orig_country_id=vorig_country.id
-- left join warebase wb on p.id=wb.part_id
where (p.id>0) ';
if (:IN_PART_ID is not null) then s = s || ' and p.id =' || :IN_PART_ID;
if (:IN_WARE_ID is not null) then s = s || ' and p.ware_id =''' || :IN_WARE_ID||'''';
if (:IN_DOC_ID is not null) then s = s || ' and p.doc_id =' || :IN_DOC_ID;
if (:IN_AGENT_ID is not null) then s = s || ' and d.agent_id =' || :IN_AGENT_ID;
for EXECUTE STATEMENT :s into
:PART_ID, :WARE_ID, :SNAME, :SIZG, :SCOUNTRY, :ORIG_CODE, :SORIG_NAME, :SORIG_IZG, :SORIG_COUNTRY, :BCODE_IZG, :PRICE, :PRICE_O, :PRICE_Z, :PRICE_R, :QUANT, :BARCODE, :BARCODE1, :DEP, :KRITK, :GODENDO, :SERIA, :SUM_NDSO, :SERT, :DATESERT, :KEMVSERT, :SDSERT, :REGN, :NGTD, :EDIZM, :DOC_ID, :DOCNUM, :DOCDATE, :DOCCAPTION, :DOCAGENT, :DOCVNUM, :DOCVSHIFT, :INSERTDT, :UPDATEDT, :ENDDT, :NDS, :REALQUANT, :PART_PARENT_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :NAC, :BLOCK_QUANT, :BLOCK_COUNT, :MMBSH, :PART_TYPE, :BASE_AGENT_ID, :SBASE_AGENT_ID, :CONTRACT_ID, :SCONTRACT_ID, :GROUP_ID, :ISFOLDER, :MNN
do
begin
update or insert into warebaseparts (
PART_ID, WARE_ID, SNAME, SIZG, SCOUNTRY, ORIG_CODE, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, BCODE_IZG, PRICE, PRICE_O, PRICE_Z, PRICE_R, QUANT, BARCODE, BARCODE1, DEP, KRITK, GODENDO, SERIA, SUM_NDSO, SERT, DATESERT, KEMVSERT, SDSERT, REGN, NGTD, EDIZM, DOC_ID, DOCNUM, DOCDATE, DOCCAPTION, DOCAGENT, DOCVNUM, DOCVSHIFT, INSERTDT, UPDATEDT, ENDDT, NDS, REALQUANT, PART_PARENT_ID, NAME_ID, IZG_ID, COUNTRY_ID, NAC, BLOCK_QUANT, BLOCK_COUNT, MMBSH, PART_TYPE, BASE_AGENT_ID, SBASE_AGENT_ID, CONTRACT_ID, SCONTRACT_ID, GROUP_ID, ISFOLDER, MNN
) values (
:PART_ID, :WARE_ID, :SNAME, :SIZG, :SCOUNTRY, :ORIG_CODE, :SORIG_NAME, :SORIG_IZG, :SORIG_COUNTRY, :BCODE_IZG, :PRICE, :PRICE_O, :PRICE_Z, :PRICE_R, :QUANT, :BARCODE, :BARCODE1, :DEP, :KRITK, :GODENDO, :SERIA, :SUM_NDSO, :SERT, :DATESERT, :KEMVSERT, :SDSERT, :REGN, :NGTD, :EDIZM, :DOC_ID, :DOCNUM, :DOCDATE, :DOCCAPTION, :DOCAGENT, :DOCVNUM, :DOCVSHIFT, :INSERTDT, :UPDATEDT, :ENDDT, :NDS, :REALQUANT, :PART_PARENT_ID, :NAME_ID, :IZG_ID, :COUNTRY_ID, :NAC, :BLOCK_QUANT, :BLOCK_COUNT, :MMBSH, :PART_TYPE, :BASE_AGENT_ID, :SBASE_AGENT_ID, :CONTRACT_ID, :SCONTRACT_ID, :GROUP_ID, :ISFOLDER, :MNN
) matching (part_id);
end
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT,INSERT,UPDATE ON WAREBASEPARTS TO PROCEDURE UPDPR_WAREBASEPARTS;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE UPDPR_WAREBASEPARTS TO SYSDBA;
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER AGENTS_WP_AIU0 FOR AGENTS
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
begin
execute procedure updpr_warebaseparts(null, null, null, new.id);
end
^
SET TERM ; ^
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER DOCS_WP_AIU0 FOR DOCS
INACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
begin
execute procedure updpr_warebaseparts(null, null, new.id, null);
end
^
SET TERM ; ^
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER PARTS_WP_AIU0 FOR PARTS
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
begin
execute procedure updpr_warebaseparts(new.id,null,null,null);
end
^
SET TERM ; ^
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
SET TERM ^ ;
CREATE OR ALTER TRIGGER WARES_WP_AIU0 FOR WARES
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
begin
if ((old.name_id<>new.name_id) or
(old.izg_id<>new.izg_id) or
(old.country_id<>new.country_id) or
(old.orig_name_id<>new.orig_name_id) or
(old.orig_izg_id<>new.orig_izg_id) or
(old.orig_country_id<>new.orig_country_id) or
(old.barcode<>new.barcode))
Then
execute procedure updpr_warebaseparts(null,new.id,null,null);
end
^
SET TERM ; ^
execute procedure updpr_warebaseparts(null,null,null,null);
update selects s set s.sqltext='select wb.* from VW_WAREBASEPARTS wb where 1=1 order by sname' where s.id=-6;
Для быстрого режима в инвентаризации (исправление VW_PARTSINV)
CREATE INDEX WAREBASEPARTS_IDX6 ON WAREBASEPARTS (PART_ID,SNAME);
CREATE INDEX WAREBASEPARTS_IDX7 ON WAREBASEPARTS (sname,part_id);
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Views ***/
/******************************************************************************/
/* View: VW_PARTSINV */
CREATE OR ALTER VIEW VW_PARTSINV(
PART_ID,
WARE_ID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
ORIG_CODE,
SORIG_NAME,
SORIG_IZG,
SORIG_COUNTRY,
BCODE_IZG,
PRICE,
PRICE_O,
PRICE_Z,
PRICE_R,
QUANT,
BARCODE,
BARCODE1,
DEP,
GODENDO,
SERIA,
SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
DOC_ID,
INSERTDT,
NDS,
BASE_AGENT_ID,
SBASE_AGENT_ID,
QUANT2)
AS
select
PART_ID,
WARE_ID,
NAME_ID,
SNAME,
IZG_ID,
SIZG,
COUNTRY_ID,
SCOUNTRY,
ORIG_CODE,
SORIG_NAME,
SORIG_IZG,
SORIG_COUNTRY,
BCODE_IZG,
PRICE,
PRICE_O,
PRICE_Z,
PRICE_R,
QUANT,
BARCODE,
BARCODE1,
DEP,
GODENDO,
SERIA,
SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
DOC_ID,
INSERTDT,
NDS,
BASE_AGENT_ID,
SBASE_AGENT_ID,
(select w.quant from warebase w where w.part_id=p.part_id)
from warebaseparts p
/* p left join WARES w on p.ware_id=w.id
left join vals vname on w.name_id=vname.id
left join vals vizg on w.izg_id=vizg.id
left join vals vcountry on w.country_id=vcountry.id
left join vals vorig_name on w.orig_name_id=vorig_name.id
left join vals vorig_izg on w.orig_izg_id=vorig_izg.id
left join vals vorig_country on w.orig_country_id=vorig_country.id
left join agents a1 on p.base_agent_id=a1.id */
;
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
