Установка МНН
Материал из wiki.standart-n.ru
Версия от 16:50, 22 апреля 2015; Natali (обсуждение | вклад)
1) Просим всех выйти из базы.
2) Создаем резервную копию базы данных
3) Выполняем скрипт
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;
4) Проверяем, есть ли логирование таблицы VALS, если есть, то убираем. (Лоирование на все остальные таблицы тоже должно быть отключено).
5) Заполняем таблицу 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) В сетеке остатков в менеджере (Под каждым пользователем) и в кассире добавляем колонку mnn.
8) В кассире у текущего пользователя в верхн панель выводим кнопку МНН (Горячие клавиши Ctrl+M)
9) Открываем справочник по этой кнопке
10) Загружаем сетку для справочника в кассире Файл:Сетка для справочника mnn в кассире.zip.
11) В настройках в полях поиска добавляем поле mnn.
12) После закрытия настроек проверяем поиск по МНН, должен работать.
13) Применяем профиль текущего пользователя ко остальным кассирам (кроме специфических, например пользователь "Заказ" или "Льгота" и что то в этом роде).
14) В менеджере нажимаем F3 проверяем наличие справочника МНН.Файл:Сетка для справочника mnn в менеджере.zip.
12. В менеджере проверяем редактируется ли поле МНН, если нет то идем в подпрограмму "редактирование поля" и добавляем это поле в разрешенное для редактирования (UPPERCASE(program_result.values['fieldname'])='MNN')or
13. Если поле все таки не обновляется, то обновляем версию менеджера до последней с соответсвующим обновлением базы
14. После обновления менеджера, проверить приходуется ли электронная накладная
