Установка МНН
Материал из wiki.standart-n.ru
Версия от 17:37, 29 апреля 2015; Natali (обсуждение | вклад)
1) Просим всех выйти из базы.
2) Создаем резервную копию базы данных
3) если нет таблицы OUT$MNN, то добавляем ее
/******************************************************************************/
/*** Generated by IBExpert 22.04.2015 17:43:35 ***/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE GENERATOR GEN_OUT$MNN_ID;
CREATE TABLE OUT$MNN (
ID DM_ID NOT NULL /* DM_ID = BIGINT */,
SNAME DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
MNN_LAT DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
MNN_RUS DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
BARCODE DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
PREPARED_SNAME DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
REPLACE_SNAME DM_TEXT /* DM_TEXT = VARCHAR(250) */,
STATUS DM_STATUS /* DM_STATUS = INTEGER */
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE OUT$MNN ADD PRIMARY KEY (ID);
/******************************************************************************/
/*** Indices ***/
/******************************************************************************/
CREATE INDEX OUT$MNN_IDX1 ON OUT$MNN (SNAME);
CREATE DESCENDING INDEX OUT$MNN_IDX2 ON OUT$MNN (SNAME);
CREATE INDEX OUT$MNN_IDX3 ON OUT$MNN (MNN_LAT);
CREATE DESCENDING INDEX OUT$MNN_IDX4 ON OUT$MNN (MNN_LAT);
CREATE INDEX OUT$MNN_IDX5 ON OUT$MNN (MNN_RUS);
CREATE DESCENDING INDEX OUT$MNN_IDX6 ON OUT$MNN (MNN_RUS);
CREATE INDEX OUT$MNN_IDX7 ON OUT$MNN (BARCODE);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: OUT$MNN_BI */
CREATE OR ALTER TRIGGER OUT$MNN_BI FOR OUT$MNN
ACTIVE BEFORE INSERT POSITION 0
as begin
if (new.id is null) then
NEW.id = GEN_ID(gen_out$MNN_id,1);
if (new.status is null) then
NEW.status =0;
end
^
SET TERM ; ^
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
4) Выполняем скрипт
SET SQL DIALECT 3;
/*--TEMP_MNN---------------------------------------------------------------*/
CREATE SEQUENCE GEN_TEMP_MNN_ID;
CREATE TABLE TEMP_MNN (
ID DM_ID,
SNAME DM_TEXT,
MNN DM_TEXT,
TNN DM_TEXT,
KOEF1 DM_DOUBLE,
STATUS DM_STATUS
);
COMMIT;
SET TERM ^ ;
CREATE OR ALTER TRIGGER TEMP_MNN_BI FOR TEMP_MNN
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.id IS NULL) THEN NEW.id = GEN_ID(gen_temp_mnn_id,1);
END
^
SET TERM ; ^
COMMIT;
/*--MNN_VALS---------------------------------------------------------------*/
CREATE TABLE MNN_VALS (SVALUE DM_TEXT);
COMMIT;
CREATE INDEX MNN_VALS_IDX1 ON MNN_VALS (SVALUE);
COMMIT;
/*--PR_REPART_SNAME--------------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_REPART_SNAME (
SNAME DM_TEXT)
returns (
R DM_TEXT)
as
declare variable RES DM_TEXT;
declare variable K DM_ID_NULL;
declare variable TEMP DM_TEXT;
declare variable TEK_NAME DM_TEXT;
begin
k=0;
res=sname||' ';
r='';
temp='';
res=replace(res,' ',' ');
while (position(' ',res)>0) do
begin
k=k+1;
if (position(' ',res)>1) then temp=SUBSTRING(res from 1 for position(' ',res)-2);
if (trim(temp)<>'') then r=r||temp||' ';
res=SUBSTRING(res from position(' ',res)+1);
if (k>1000) then exit;
end
if (char_length(r)>0) then r=substring(r from 1 for CHAR_LENGTH(r)-1);
suspend;
end^
SET TERM ; ^
COMMIT;
GRANT EXECUTE ON PROCEDURE PR_REPART_SNAME TO SYSDBA;
COMMIT;
/*--PR_DELETE_SYMBOL-------------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_DELETE_SYMBOL (
S DM_TEXT)
returns (
RES DM_TEXT)
as
declare variable TEMP DM_TEXT;
declare variable K DM_ID_NULL;
declare variable TEK_NAME DM_TEXT;
begin
k=0;
res=s||' ';
res=replace(res,',','');
res=replace(res,'.','');
res=replace(res,'1','');
res=replace(res,'2','');
res=replace(res,'3','');
res=replace(res,'4','');
res=replace(res,'5','');
res=replace(res,'6','');
res=replace(res,'7','');
res=replace(res,'8','');
res=replace(res,'9','');
res=replace(res,'0','');
res=replace(res,'(','');
res=replace(res,')','');
if (position('МАЗЬ',UPPER(:res))=1) then
begin
res=replace(res,'Мазь ','');
res=res||' Мазь';
end
if (position('КРЕМ',UPPER(:res))=1) then
begin
res=replace(res,'Крем ','');
res=res||' Крем';
end
if (position('БАЛЬЗАМ',UPPER(:res))=1) then
begin
res=replace(res,'Бальзам ','');
res=res||' Бальзам';
end
if (position('КАПЛИ',UPPER(:res))=1) then
begin
res=replace(res,'Капли ','');
res=res||' капли';
end
if (position('ПАСТИЛК',UPPER(:res))=1) then
begin
res=replace(res,'Пастилки ','');
res=res||' пастилки';
end
res=replace(res,' к-та ',' кислота ');
res=replace(res,' к-та-',' кислота ');
res=replace(res,'детский','детс');
res=replace(res,'/','');
res=replace(res,'№','');
res=replace(res,'N','');
res=replace(res,'+','');
res=replace(res,'%','');
res=replace(res,' мг ',' ');
res=replace(res,'-',' ');
res=replace(res,' мкг ',' ');
res=replace(res,' мл ',' ');
res=replace(res,' п/о ',' ');
res=replace(res,' н/ст ',' ');
res=replace(res,' ',' ');
res=replace(res,' ',' ');
res=(select r from pr_repart_sname(:res));
res=trim(res);
suspend;
end^
SET TERM ; ^
COMMIT;
GRANT EXECUTE ON PROCEDURE PR_REPART_SNAME TO PROCEDURE PR_DELETE_SYMBOL;
GRANT EXECUTE ON PROCEDURE PR_DELETE_SYMBOL TO PROCEDURE PR_GET_MNN;
GRANT EXECUTE ON PROCEDURE PR_DELETE_SYMBOL TO SYSDBA;
COMMIT;
/*--PR_GET_MNN-------------------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_GET_MNN (
SNAME DM_TEXT,
TYPE_FIND DM_TEXT)
returns (
MNN DM_TEXT,
TNN DM_TEXT,
PR_TNN DM_TEXT,
R_TNN DM_TEXT)
as
declare variable NAME DM_TEXT;
declare variable TEK_MNN DM_TEXT;
declare variable TEK_TNN DM_TEXT;
declare variable TEMP_TNN DM_TEXT;
declare variable P1 DM_ID_NULL;
declare variable P2 DM_ID_NULL;
declare variable PR_LENGTH DM_ID_NULL;
begin
if (type_find='SNAME') then
begin
name=(select res from pr_delete_symbol(:sname));
mnn=''; tnn='';
pr_length=4;
delete from temp_mnn;
for select m.sname, m.mnn_lat, m.prepared_sname, m.replace_sname
from out$mnn m order by m.sname
into :tek_tnn, :tek_mnn, :pr_tnn, :r_tnn do
begin
if (position(UPPER(trim(:tek_tnn)),UPPER(trim(:sname)))=1) then --неприведенные наименования вхождение с начало слова
begin
insert into temp_mnn(sname,mnn,tnn,koef1,status)
values (:sname,:tek_mnn,:tek_tnn,100*char_length(:tek_tnn)/char_length(:sname),2);
end
if (position(UPPER(trim(:r_tnn)),UPPER(trim(:name)))=1 and char_length(trim(replace(:r_tnn,' ','')))>:pr_length)
then --приведенные наименования, тнн поменяно местами вхождение c начала слова
begin
insert into temp_mnn(sname,mnn,tnn,koef1,status)
values (:name,:tek_mnn,:r_tnn||' == '||:tek_tnn,100*char_length(:r_tnn)/char_length(:name),1);
end
p1=position(UPPER(trim(:pr_tnn)),UPPER(trim(:name)));
if (p1=1 and char_length(trim(replace(:pr_tnn,' ','')))>:pr_length) then --приведенные наименования, вхождение с начало слова
begin
insert into temp_mnn(sname,mnn,tnn,koef1,status)
values (:name,:tek_mnn,:pr_tnn||' == '||:tek_tnn,100*char_length(:pr_tnn)/char_length(:name),1);
end
p1=position(UPPER(trim(:pr_tnn)),UPPER(trim(:name)));
if (p1>1 and p1<round(CHAR_LENGTH(:name)/2) and char_length(trim(replace(:pr_tnn,' ','')))>:pr_length) then --приведенные наименования, но вхождение раньше половины слова
begin
insert into temp_mnn(sname,mnn,tnn,koef1,status)
values (:name,:tek_mnn,:pr_tnn||' == '||:tek_tnn,100*char_length(:pr_tnn)/char_length(:name),0);
end
p2=position(UPPER(trim(:r_tnn)),UPPER(trim(:name)));
if (p2>1 and p2<round(char_length(:name)/2) and char_length(trim(replace(:r_tnn,' ','')))>:pr_length) then --приведенные наименования, тнн поменяно местами, но вхождение раньше половины слова
begin
insert into temp_mnn(sname,mnn,tnn,koef1,status)
values (:name,:tek_mnn,:r_tnn||' == '||:tek_tnn,100*char_length(:r_tnn)/char_length(:name),0);
end
end
mnn=(select first 1 mnn from temp_mnn where koef1>75 and status=2 order by koef1 desc);
tnn=(select first 1 tnn from temp_mnn where koef1>75 and status=2 order by koef1 desc);
if ((mnn='') or (mnn is null)) then mnn=(select first 1 mnn from temp_mnn where koef1>99 order by status desc);
if ((tnn='') or (tnn is null)) then tnn=(select first 1 tnn from temp_mnn where koef1>99 order by status desc);
if ((mnn='') or (mnn is null)) then mnn=(select first 1 mnn from temp_mnn where koef1>10 and status=2 order by koef1 desc); --можт меньше 20 ?
if ((tnn='') or (tnn is null)) then tnn=(select first 1 tnn from temp_mnn where koef1>10 and status=2 order by koef1 desc);
if ((mnn='') or (mnn is null)) then mnn=(select first 1 mnn from temp_mnn where koef1>10 and status=1 order by koef1 desc);
if ((tnn='') or (tnn is null)) then tnn=(select first 1 tnn from temp_mnn where koef1>10 and status=1 order by koef1 desc);
if ((mnn='') or (mnn is null)) then mnn=(select first 1 mnn from temp_mnn where koef1>10 order by koef1 desc);
if ((tnn='') or (tnn is null)) then tnn=(select first 1 tnn from temp_mnn where koef1>10 order by koef1 desc);
if ((mnn='') or (mnn is null)) then mnn=(select first 1 mnn from temp_mnn where char_length(tnn)>=3 and status=2 order by koef1 desc);
if ((tnn='') or (tnn is null)) then tnn=(select first 1 tnn from temp_mnn where char_length(tnn)>=3 and status=2 order by koef1 desc);
end
suspend;
end^
SET TERM ; ^
COMMIT;
COMMENT ON PARAMETER PR_GET_MNN.TYPE_FIND IS 'SNAME наименование, BARCODE - шк изготовителя';
GRANT EXECUTE ON PROCEDURE PR_DELETE_SYMBOL TO PROCEDURE PR_GET_MNN;
GRANT SELECT,INSERT,DELETE ON TEMP_MNN TO PROCEDURE PR_GET_MNN;
GRANT SELECT ON OUT$MNN TO PROCEDURE PR_GET_MNN;
GRANT EXECUTE ON PROCEDURE PR_GET_MNN TO SYSDBA;
COMMIT;
/*--PR_GETVAL_ID-----------------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_GETVAL_ID (
SVALUE type of DM_TEXT,
VTYPE type of DM_STATUS,
ALTTYPE DM_STATUS,
MNN DM_TEXT = '')
returns (
VAL_ID type of DM_ID)
as
declare variable MNN_2 DM_TEXT;
begin
if (:svalue is null) then svalue='';
select id from vals where vtype=:vtype and svalue=:svalue and alttype=:alttype into :val_id;
if ((val_id is not null) and (:mnn<>'') and (:mnn is not null) AND (vtype=0))
then update vals set preparedvalue=:mnn where id=:val_id;
if (val_id is null) then
begin
val_id=GEN_ID(GEN_VALS_ID,1);
mnn_2=null;
if ((:mnn<>'') and (:mnn is not null) and (vtype=0))
then mnn_2=:mnn;
if (vtype=0) then
if ((:mnn='') or (:mnn is null)) then
mnn_2=(select mnn from pr_get_mnn(:svalue,'SNAME'));
insert into vals (id, vtype, svalue, alttype, preparedvalue) values (:val_id,:vtype,:svalue, :alttype, :mnn_2);
end
suspend;
end^
SET TERM ; ^
COMMIT;
GRANT SELECT,INSERT,UPDATE ON VALS TO PROCEDURE PR_GETVAL_ID;
GRANT EXECUTE ON PROCEDURE PR_GET_MNN TO PROCEDURE PR_GETVAL_ID;
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO SYSDBA;
COMMIT;
/*--PR_GET_WARE------------------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_GET_WARE (
SNAME type of DM_TEXT,
SIZG type of DM_TEXT,
SCOUNTRY type of DM_TEXT,
ORIG_CODE type of DM_TEXT,
SORIG_NAME type of DM_TEXT,
SORIG_IZG type of DM_TEXT,
SORIG_COUNTRY type of DM_TEXT,
BARCODE type of DM_TEXT,
Z_ID type of DM_ID,
SKLAD_ID DM_TEXT,
ALTTYPE DM_STATUS,
MNN DM_TEXT = '')
returns (
W_ID type of DM_ID)
as
declare variable NAME_ID type of DM_ID;
declare variable IZG_ID type of DM_ID;
declare variable COUNTRY_ID type of DM_ID;
declare variable ORIG_NAME_ID type of DM_ID;
declare variable ORIG_IZG_ID type of DM_ID;
declare variable ORIG_COUNTRY_ID type of DM_ID;
begin
if (orig_code is null) then orig_code='';
select val_id from pr_getval_id(:sname,0,:alttype,:mnn) into :name_id;
select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
select first 1 id from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id -- into :w_id;
and barcode=:barcode into :w_id;
if (w_id is null) then
begin
-- exception EX_WRONG_OPER;
w_id=gen_id(gen_wares_id,1);
insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID)
values
(:W_ID,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,:BARCODE,:Z_ID,:SKLAD_ID);
end
suspend;
end^
SET TERM ; ^
COMMIT;
GRANT EXECUTE ON PROCEDURE PR_GETVAL_ID TO PROCEDURE PR_GET_WARE;
GRANT SELECT,INSERT ON WARES TO PROCEDURE PR_GET_WARE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO SYSDBA;
COMMIT;
/*--DOC_DETAIL_ACTIVE------------------------------------------------------*/
ALTER TABLE DOC_DETAIL_ACTIVE ADD MAKE_ID DM_ID_NULL;
ALTER TABLE DOC_DETAIL_ACTIVE ADD MNN DM_TEXT;
COMMIT;
SET TERM ^ ;
CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BI_MNN FOR DOC_DETAIL_ACTIVE
ACTIVE BEFORE INSERT POSITION 3
AS
begin
if (new.name_id>0) then new.mnn=(select preparedvalue from vals where id=new.name_id);
if (new.mnn is null) then new.mnn=(select MNN from pr_get_mnn(new.sname,'SNAME'));
end
^
SET TERM ; ^
COMMIT;
/*--PR_DOC_DETAIL_INSERT---------------------------------------------------*/
ALTER TABLE DOC_DETAIL ADD MAKE_ID DM_ID_NULL;
COMMIT;
SET TERM ^ ;
create or alter procedure PR_DOC_DETAIL_INSERT (
PARENT_ID bigint,
DOC_ID bigint,
PART_ID bigint,
QUANT type of DM_DOUBLE,
DISCOUNT double precision,
SUMMA double precision,
SUMMA_O double precision,
PRICE numeric(15,4),
SUM_NDSO numeric(15,4),
NAC numeric(15,4),
COMMITDATE type of DM_DATE,
HUMAN_QUANT type of DM_TEXT,
SUM_DSC type of DM_DOUBLE,
DCARD type of DM_TEXT,
ACTIVE_ID type of DM_ID,
PART_TYPE DM_STATUS,
MAKE_ID DM_ID_NULL = null)
as
declare variable NEW_ID type of DM_ID;
begin
new_id=gen_id(gen_doc_detail_id,1);
insert into doc_detail
(id,PARENT_ID,DOC_ID,PART_ID,QUANT,DISCOUNT,SUMMA,summa_o, sum_ndso, price, nac, doc_commitdate, human_quant,SUM_DSC,dcard, part_type,make_id)
/* (PARENT_ID,DOC_ID,PART_ID,QUANT,DISCOUNT,SUMMA,summa_o) */
values
/* (:PARENT_ID,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o); */
(:new_id,:PARENT_ID,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o, :sum_ndso, :price, :nac, :commitdate, :human_quant,:SUM_DSC,:dcard, :part_type,:make_id);
execute procedure PR_DOC_DETAIL_ACTIVE_ONCOMMIT(:active_id,:new_id,:doc_id,:commitdate);
end^
SET TERM ; ^
GRANT INSERT ON DOC_DETAIL TO PROCEDURE PR_DOC_DETAIL_INSERT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_ACTIVE_ONCOMMIT TO PROCEDURE PR_DOC_DETAIL_INSERT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO SYSDBA;
COMMIT;
/*--PR_DOC_RASHOD_COMMIT---------------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_DOC_RASHOD_COMMIT (
DOC_ID type of DM_ID,
DBG type of DM_STATUS)
as
declare variable PARENT_ID type of DM_ID;
declare variable PART_ID type of DM_ID;
declare variable PRICE type of DM_DOUBLE;
declare variable QUANT type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable NAC type of DM_DOUBLE;
declare variable DOC_COMMITDATE type of DM_DATE;
declare variable HUMAN_QUANT type of DM_TEXT;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable DCARD type of DM_TEXT;
declare variable ACTIVE_ID type of DM_ID;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable MAKE_ID DM_ID_NULL;
begin
select cast(commitdate as date) from docs where id=:doc_id into :doc_commitdate;
for
select PARENT_ID,DOC_ID,PART_ID,PRICE,QUANT,DISCOUNT,SUMMA,summa_o, sum_ndso, nac, human_quant, SUM_DSC, dcard, part_type, id, make_id
from doc_detail_active where doc_id=:doc_id
into :PARENT_ID,:DOC_ID,:PART_ID,:PRICE,:QUANT,:DISCOUNT,:SUMMA,:summa_o, :sum_ndso, :nac, :human_quant, :SUM_DSC, :dcard, :part_type, :active_id, :make_id
do
begin
execute procedure PR_DOC_DETAIL_INSERT(:PARENT_ID,:DOC_ID,:PART_ID,QUANT,:DISCOUNT,:SUMMA,:summa_o, :price,:sum_ndso, :nac, :doc_commitdate, :human_quant, :SUM_DSC,:dcard,:active_id,:part_type,:make_id);
end
end^
SET TERM ; ^
GRANT SELECT ON DOCS TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_RASHOD_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO SYSDBA;
/*--PR_DOC_PRIHOD_COMMIT_RECURSE-------------------------------------------*/
SET TERM ^ ;
create or alter procedure PR_DOC_PRIHOD_COMMIT_RECURSE (
DOC_ID type of DM_ID,
DOC_PARENT_ID type of DM_ID,
PART_PARENT_ID type of DM_ID,
DOC_COMMITDATE type of DM_DATE,
CONTRACT_ID DM_ID)
as
declare variable PART_ID type of DM_ID;
declare variable WARE_ID type of DM_ID;
declare variable SNAME type of DM_TEXT;
declare variable SIZG type of DM_TEXT;
declare variable SCOUNTRY type of DM_TEXT;
declare variable ORIG_CODE type of DM_TEXT;
declare variable SORIG_NAME type of DM_TEXT;
declare variable SORIG_IZG type of DM_TEXT;
declare variable SORIG_COUNTRY type of DM_TEXT;
declare variable BARCODE type of DM_TEXT;
declare variable Z_ID type of DM_ID;
declare variable SKLAD_ID DM_TEXT;
declare variable PRICE type of DM_DOUBLE;
declare variable PRICE_O type of DM_DOUBLE;
declare variable PRICE_Z type of DM_DOUBLE;
declare variable PRICE_R type of DM_DOUBLE;
declare variable QUANT type of DM_DOUBLE;
declare variable BARCODE1 type of DM_TEXT;
declare variable DEP type of DM_ID;
declare variable KRITK type of DM_DOUBLE;
declare variable GODENDO type of DM_DATETIME;
declare variable SERIA type of DM_TEXT;
declare variable NDS type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SERT type of DM_TEXT;
declare variable DATESERT type of DM_DATETIME;
declare variable KEMVSERT type of DM_TEXT;
declare variable SDSERT type of DM_DATETIME;
declare variable REGN type of DM_TEXT;
declare variable NGTD type of DM_TEXT;
declare variable EDIZM type of DM_TEXT;
declare variable PARENT_ID type of DM_ID;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable DISCOUNT type of DM_DOUBLE;
declare variable ACTIVE_ID type of DM_ID;
declare variable NAC type of DM_DOUBLE;
declare variable DOC_DETAIL_ID type of DM_ID;
declare variable MOTHERPART_ID type of DM_ID;
declare variable BCODE_IZG type of DM_TEXT;
declare variable HUMAN_QUANT type of DM_TEXT;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable DCARD type of DM_TEXT;
declare variable PART_TYPE DM_STATUS;
declare variable BASE_AGENT_ID DM_ID;
declare variable GROUP_ID DM_ID;
declare variable MAKE_ID DM_ID_NULL;
declare variable MNN DM_TEXT;
begin
for
select
/*ware_id*/ sname,sizg,scountry,orig_code,sorig_name,sorig_izg,sorig_country,barcode,z_id,sklad_id,
/*part_id*/ PRICE,PRICE_O,PRICE_Z,PRICE_R,QUANT,BARCODE1,/*KRITK,*/GODENDO,SERIA,NDS,SUM_NDSO,SERT,DATESERT,KEMVSERT,SDSERT,REGN,NGTD,EDIZM,
PARENT_ID,DISCOUNT,SUMMA,summa_o, id, part_id, nac, doc_detail_id, motherpart_id, dep, bcode_izg, human_quant, SUM_DSC, dcard,
part_type, BASE_AGENT_ID, group_id, make_id, mnn
from doc_detail_active where doc_id=:doc_id and part_parent_id=:doc_parent_id
into
:sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:barcode,:z_id,:sklad_id,
:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE1,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,
:PARENT_ID,:DISCOUNT,:SUMMA,:summa_o, :active_id, :part_id, :nac, :doc_detail_id, :motherpart_id, :dep, :bcode_izg, :human_quant, :SUM_DSC, :dcard,
:part_type, :BASE_AGENT_ID, :group_id,:make_id, :mnn
do
begin
-- if ((price<0.001) or (price is null)) then exception EX_PRICE;
if ((price<0) or (price is null)) then exception EX_PRICE;
if (/*(price_o<0.001) or */(price_o is null)) then exception EX_PRICE_O;
if (/*(quant<0.00001) or */(quant is null)) then exception EX_QUANT;
if (/*(summa<0.00001) or */(summa is null)) then exception EX_SUMMA;
if (/*(summa_o<0.00001) or */(summa_o is null)) then exception EX_SUMMA_O;
-- if ((NDS<0.00001) or (NDS is null)) then exception EX_NDS;
if (/*(SUM_NDSO<0) or */(SUM_NDSO is null)) then exception EX_SUM_NDSO;
select w_id from pr_get_ware(:sname,:sizg,:scountry,:orig_code,:sorig_name,:sorig_izg,:sorig_country,:bcode_izg,:z_id,:sklad_id,:part_type,:mnn)
into :ware_id;
if ((part_id=0) or (part_id is NULL)) then
select p_id from pr_new_part(:DOC_ID,:part_parent_id,:WARE_ID,:PRICE,:PRICE_O,:PRICE_Z,:PRICE_R,:QUANT,:BARCODE,:BARCODE1,:DEP,/*:KRITK,*/:GODENDO,:SERIA,:NDS,:SUM_NDSO,:SERT,:DATESERT,:KEMVSERT,:SDSERT,:REGN,:NGTD,:EDIZM,:NAC,:motherpart_id, :part_type,:BASE_AGENT_ID, :sklad_id, :contract_id, :active_id, :group_id) into :part_id;
execute procedure PR_DOC_DETAIL_INSERT(:doc_detail_id,:DOC_ID,:PART_ID,:QUANT,:DISCOUNT,:SUMMA,:summa_o,:price,:sum_ndso,:nac,:doc_commitdate,:human_quant,:SUM_DSC,:dcard,:active_id,:part_type,:make_id);
-- обновляем членство в группах
execute procedure PR_UPDGROUPDETAIL_ACTIVE2PARTS(:active_id,:part_id);
-- присваиваем ценам партии
update prices set part_id=:part_id where dda_id=:active_id;
update or insert into prices (part_id,price_type,currency_id, price,dda_id) values (:part_id,0,0,:price,:active_id) matching (PART_ID,PRICE_TYPE);
execute procedure PR_DOC_PRIHOD_COMMIT_RECURSE(:doc_id,:active_id,:part_id,:doc_commitdate,:contract_id);
end
end^
SET TERM ; ^
COMMIT;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_GET_WARE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_NEW_PART TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_DETAIL_INSERT TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_UPDGROUPDETAIL_ACTIVE2PARTS TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT SELECT,INSERT,UPDATE ON PRICES TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT_RECURSE TO SYSDBA;
COMMIT;
/*--VW_DOC_DETAIL_ACTIVE---------------------------------------------------*/
CREATE OR ALTER VIEW VW_DOC_DETAIL_ACTIVE(
ID,
PARENT_ID,
DOC_ID,
PART_ID,
PART_PARENT_ID,
DOC_DETAIL_ID,
PRICE,
NAC,
QUANT,
DISCOUNT,
SUMMA,
SUMMA_O,
DCARD,
WARE_ID,
PRICE_O,
PRICE_Z,
PRICE_R,
BARCODE,
BARCODE1,
BCODE_IZG,
KRITK,
GODENDO,
SERIA,
NDS,
SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
NAME_ID,
IZG_ID,
COUNTRY_ID,
ORIG_CODE,
ORIG_NAME_ID,
ORIG_IZG_ID,
ORIG_COUNTRY_ID,
Z_ID,
SKLAD_ID,
SNAME,
SIZG,
SCOUNTRY,
SORIG_NAME,
SORIG_IZG,
SORIG_COUNTRY,
INSERTDT,
INFO,
KOEF,
MOTHERPART_ID,
DEP,
MMBSH,
NDSBYDEP,
REALQUANT,
PRICE_DSC,
BLOCK_QUANT,
SUM_DSC,
HUMAN_QUANT,
CUSTOMDRAW,
STATUS,
OLDPRICE,
PART_TYPE,
PRICES,
BASE_AGENT_ID,
SBASE_AGENT_ID,
GNVLS,
GROUP_ID,
MNN)
AS
SELECT
da.ID,
PARENT_ID,
DOC_ID,
PART_ID,
PART_PARENT_ID,
DOC_DETAIL_ID,
PRICE,
NAC,
QUANT,
DISCOUNT,
SUMMA,
SUMMA_O,
DCARD,
WARE_ID,
PRICE_O,
PRICE_Z,
PRICE_R,
BARCODE,
BARCODE1,
BCODE_IZG,
(select intvalue from vals where id=da.name_id), --KRITK,
GODENDO,
SERIA,
da.NDS,
SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
NAME_ID,
IZG_ID,
COUNTRY_ID,
ORIG_CODE,
ORIG_NAME_ID,
ORIG_IZG_ID,
ORIG_COUNTRY_ID,
Z_ID,
SKLAD_ID,
SNAME,
SIZG,
SCOUNTRY,
SORIG_NAME,
SORIG_IZG,
SORIG_COUNTRY,
da.INSERTDT,
INFO,
KOEF,
MOTHERPART_ID,
DEP,
(select membership from PR_MEMBERSHIPS('PARTS=' || da.part_id || ';DOC_DETAIL_ACTIVE=' || da.id || ';PARTS.NAME_ID=' || da.name_id || ';PARTS.IZG_ID=' ||da.izg_id ||';',ascii_char(13)||ascii_char(10),1)),
deps.nds,
(select realquant from warebase w where w.part_id=da.part_id),
iif(da.quant between -0.000001 and 0.000001,0,cast(da.summa/da.quant as numeric(15,4))),
(select BLOCK_QUANT from warebase where part_id=da.part_id),
da.SUM_DSC,
da.human_quant,
DA.CUSTOMDRAW,
da.STATUS,
iif(da.parent_id<>0, (select ddd.price from DOC_DETAIL_ACTIVE ddd where ddd.id=da.parent_id),0),
part_type,
(select s from PR_GETPARTPRICES(da.part_id,da.id)),
da.BASE_AGENT_ID,
(select caption from agents where id=da.BASE_AGENT_ID),
da.gnvls,
da.group_id,
da.mnn
FROM
DOC_DETAIL_ACTIVE da left join deps on da.dep=deps.id
order by da.parent_id, da.sname, da.id
;
COMMIT;
GRANT SELECT ON VW_DOC_DETAIL_ACTIVE TO PROCEDURE PR_PART_HISTORY;
GRANT SELECT ON VW_DOC_DETAIL_ACTIVE TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT, UPDATE, DELETE, INSERT, REFERENCES ON VW_DOC_DETAIL_ACTIVE TO STANDART WITH GRANT OPTION;
COMMIT;
/*--VW_WAREBASE_KASSA------------------------------------------------------*/
CREATE OR ALTER VIEW VW_WAREBASE_KASSA(
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,
GROUPS,
CAPTION,
CONTRACT_ID,
SCONTRACT_ID,
GROUP_ID,
VPART_ID,
PACKET,
SORDERNAME,
MNN)
AS
select
w.PART_ID,
w.WARE_ID,
w.SNAME,
w.SIZG,
w.SCOUNTRY,
w.ORIG_CODE,
w.SORIG_NAME,
w.SORIG_IZG,
w.SORIG_COUNTRY,
w.BCODE_IZG,
w.PRICE,
w.PRICE_O,
w.PRICE_Z,
w.PRICE_R,
w.QUANT,
w.BARCODE,
w.BARCODE1,
w.DEP,
w.KRITK,
w.GODENDO,
w.SERIA,
w.SUM_NDSO,
w.SERT,
w.DATESERT,
w.KEMVSERT,
w.SDSERT,
w.REGN,
w.NGTD,
w.EDIZM,
w.doc_id,
w.docnum,
w.docdate,
w.doccaption,
w.docagent,
w.docvnum,
w.docvshift,
w.INSERTDT,
w.UPDATEDT,
w.ENDDT,
w.NDS ,
w.REALQUANT,
w.PART_PARENT_ID, w.NAME_ID, w.IZG_ID, w.COUNTRY_ID,w.NAC, w.BLOCK_QUANT, w.BLOCK_COUNT,
w.MMBSH,w.PART_TYPE,w.BASE_AGENT_ID,
(select membership from PR_MEMBERSHIPS('PARTS=' || w.part_id || ';PARTS.NAME_ID=' || w.name_id || ';PARTS.IZG_ID=' || w.izg_id ||';',ascii_char(13)||ascii_char(10))) as groups, a.caption,
W.contract_id,
W.scontract_id,
w.group_id,
w.vpart_id,
w.packet,
w.SNAME,
v.preparedvalue
from warebase w
join docs d on w.doc_id = d.id
join agents a on d.agent_id = a.id
join vals v on w.name_id=v.id and v.vtype=0
;
COMMIT;
/*--VW_WAREBASE------------------------------------------------------------*/
CREATE OR ALTER VIEW VW_WAREBASE(
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,
PRICES,
BASE_AGENT_ID,
SBASE_AGENT_ID,
CONTRACT_ID,
SCONTRACT_ID,
GROUP_ID,
SGROUP_ID,
VPART_ID,
SKLAD_ID,
MNN)
AS
select
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,
(select intvalue from vals where id=name_id), --KRITK,
GODENDO,
SERIA,
SUM_NDSO,
SERT,
DATESERT,
KEMVSERT,
SDSERT,
REGN,
NGTD,
EDIZM,
DOC_ID, /*(select pr.doc_id from pr_getmotherpart(part_id) pr), */
DOCNUM, /*(select (select ddd.docnum from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
cast (DOCDATE as dm_date), /*(select (select ddd.docdate from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
DOCCAPTION, /*(select (select ddd.caption from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
DOCAGENT, /*(select (select ddd.sagent from vw_docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
DOCVNUM, /*(select (select ddd.vnum from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
DOCVSHIFT, /*(select (select ddd.vshift from docs ddd where ddd.id=pr.doc_id) from pr_getmotherpart(part_id) pr),*/
INSERTDT,
UPDATEDT,
ENDDT,
NDS,
REALQUANT,
PART_PARENT_ID,
NAME_ID,
IZG_ID,
COUNTRY_ID,
NAC,
BLOCK_QUANT,
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)),
part_type,
(select s from PR_GETPARTPRICES(wb.part_id,0)),
BASE_AGENT_ID,
(select caption from agents where id=wb.BASE_AGENT_ID),
CONTRACT_ID,
SCONTRACT_ID,
GROUP_ID,
(select gr.caption from groups gr where gr.id=wb.group_id),
VPART_ID,
(select sklad_id from parts where parts.id=wb.part_id),
(select preparedvalue from vals where id=name_id) --mnn
from WAREBASE wb
;
COMMIT;
/*--SP$WDICTS--------------------------------------------------------------*/
INSERT INTO SP$WDICTS (PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INSERTDT, INI, BMP, SORTING, FRAMECLASS)
VALUES (0, 'МНН', NULL, 'MNN ', 3, NULL, '[insertsql]
[deletesql_selected]
[deletesql]
[refreshsql]
select * from OUT$MNN where id=:ID
[selectsqlwithdeleted]
[selectsql]
select * from OUT$MNN order by sname
[form_show]
position=8
left=0
Top=0
Width=1024
Height=800
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0
[form_get]
position=8
left=0
Top=0
Width=1024
Height=800
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0
[main]
sourcetablename=OUT$MNN
returnfieldname=ID
captionfieldname=SNAME
keyfieldname=ID
ViewID=OUT$MNN
RootGroupTableName=
ShowCaption=Справочник МНН
GetCaption=Справочник МНН
InitTMSGroup_id=
GroupSelect=0
foldergroup=
initfolder_id=
dataset=0
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0
[cfSelect]
selectfieldexpression=SNAME||'' ''||coalesce(mnn_rus,'''')
AllwaysPartial=1
[childs]
bottomdock_units=0
bottomdock_size=0
rightdock_units=0
rightdock_size=0
[editfields]', NULL, NULL, NULL);
COMMIT;
5) Проверяем, включено ли логирование таблицы VALS, если есть, то убираем. (Логирование на все остальные таблицы тоже должно быть отключено).
6) Заполняем таблицу VALS МНН-нами, действие очень долгое) формула для примерного расчета времени: ~1,5 сек* ассортимент = Время в секундах.
/*
execute block
as
declare variable id dm_id;
declare variable sname dm_text;
begin
for select id, svalue from vals where (vtype = 0) and ((preparedvalue is null) or (preparedvalue = '')) into :id, :sname do
begin
select mnn from pr_get_mnn(:sname, 'SNAME') into :sname;
update vals set preparedvalue = :sname where id = :id;
end
end
*/
execute block
as
declare variable id dm_id;
declare variable sname dm_text;
begin
for select id, svalue from vals where (vtype = 0) and ((preparedvalue is null) or (preparedvalue = '')) and
id in (select name_id from vw_warebase group by name_id) into :id, :sname do
begin
select mnn from pr_get_mnn(:sname, 'SNAME') into :sname;
update vals set preparedvalue = :sname where id = :id;
end
end
7) через программу "OST" прокачиваем актуальный "справочник МНН".
8) В сетке базы остатков в менеджере под каждым пользователем и в кассире добавляем колонку MNN.
9) В кассире под каждым пользователем в верхней панели выводим кнопку МНН (Горячая клавиша "Ctrl+M")
10) Открываем справочник по этой кнопке и загружаем сетку для справочника в кассире (необходимо под каждым пользователем открывать этот справочник и подгружать сетку)
Файл:Сетка для справочника mnn в кассире.zip.
11) В настройках кассира в поле поиска добавляем поле "mnn".
12) После закрытия настроек проверяем поиск по МНН, должен работать.
13) Применяем профиль текущего пользователя ко остальным кассирам (кроме специфических, например пользователь "Заказ" или "Выручка ФАП" или др.).
14) В менеджере нажимаем F3 проверяем наличие справочника МНН.
Файл:Сетка для справочника mnn в менеджере.zip.
15). В менеджере проверяем редактируется ли поле МНН, если нет то идем в подпрограмму "редактирование поля" и добавляем это поле в разрешенное для редактирования "
(UPPERCASE(program_result.values['fieldname'])='MNN')or
16) Если поле все таки не обновляется, то обновляем версию менеджера до последней, с соответсвующим обновлением базы.
17) После обновления менеджера, проверить приходуется ли электронная накладная.
18) Если после установки MNN тормозит компьютер, то выполняем скрипт
/******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; SET TERM ^ ; CREATE OR ALTER TRIGGER DOC_DETAIL_ACTIVE_BI_MNN FOR DOC_DETAIL_ACTIVE ACTIVE BEFORE INSERT POSITION 3 AS begin if (new.quant<=0) then exit; if (new.name_id>0) then new.mnn=(select preparedvalue from vals where id=new.name_id); if (new.mnn is null) then new.mnn=(select MNN from pr_get_mnn(new.sname,'SNAME')); end ^ SET TERM ; ^
19) Если в кассире нет кнопочки "МНН", то заходим в менеджере в "Сервис - Привилегии".
