Лабораторно фасовочный журнал
Материал из wiki.standart-n.ru
Версия от 14:01, 24 марта 2015; Agk (обсуждение | вклад)
Общие сведения
В соответствии с пунктом 2.8 "Порядка отпуска лекарственных средств", утвержденного Приказом Минздравсоцразвития РФ от 14.12.2005 N 785 (в ред. от 06.08.2007), в исключительных случаях нарушения аптечной организацией вторичной заводской упаковки лекарственное средство должно отпускаться в аптечной упаковке с обязательным указанием наименования, заводской серии, срока годности лекарственного средства, серии и даты по лабораторно-фасовочному журналу.
Подготовка базы данных
Выполните скрипт
/******************************************************************************/
/*** Generated by IBExpert 24.03.2015 13:20:20 ***/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE GENERATOR GEN_LABFAS_ID;
CREATE TABLE LABFAS (
ID DM_ID NOT NULL /* DM_ID = BIGINT */,
NAME_ID DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
PRICE DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
PRICEFAS DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
QUANTFAS DM_ID_NULL /* DM_ID_NULL = BIGINT */,
DOC_ID DM_ID /* DM_ID = BIGINT */,
QUANT DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
SERIA DM_TEXT COLLATE PXW_CYRL /* DM_TEXT = VARCHAR(250) */,
NAMEFAS DM_TEXT COLLATE PXW_CYRL /* DM_TEXT = VARCHAR(250) */,
PART_ID DM_ID /* DM_ID = BIGINT */,
SERL DM_TEXT /* DM_TEXT = VARCHAR(250) */
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE LABFAS ADD CONSTRAINT PK_LABFAS PRIMARY KEY (ID);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: LABFAS_BI */
CREATE OR ALTER TRIGGER LABFAS_BI FOR LABFAS
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_labfas_id,1);
if (new.quantfas<>0) then
new.pricefas=new.price/new.quantfas;
-- if ((new.namefas is null) or (new.namefas='')) then
select first 1 qf.namefas from quant_fas qf where qf.namefas<>'' and new.name_id=qf.name_id
into new.namefas;
if (new.namefas is null) then
select first 1 v.svalue from vals v where new.name_id=v.id
into new.namefas;
end
^
/* Trigger: LABFAS_BU0 */
CREATE OR ALTER TRIGGER LABFAS_BU0 FOR LABFAS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
if (((new.quantfas<>old.quantfas)
or (new.quantfas is not null and old.quantfas is null )) or ((new.namefas<>old.namefas)
or (new.namefas is not null and old.namefas is null )))
then
begin
if (new.quantfas<>0 and new.quantfas is not null) then
new.pricefas=new.price/new.quantfas;
--if (new.namefas<>'') then
-- new.namefas=
-- if (new.name_id) not in (select name_id from quant_fas) then
update or insert into quant_fas (name_id,quantfas,namefas) values (new.name_id,new.quantfas, new.namefas) matching (name_id);
if (new.quantfas=0 and new.quantfas is not null) then
new.pricefas=0;
end
/* insert into quant_fas
(id,
name_id,
quantfas
)
values
(
new.ID,
new.name_id,
new.quantfas
); */
end
^
SET TERM ; ^
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Views ***/
/******************************************************************************/
/* View: VW_LABFAS */
CREATE OR ALTER VIEW VW_LABFAS(
ID,
NAME_ID,
PRICE,
PRICEFAS,
QUANTFAS,
DOC_ID,
SNAME,
COMMITDATE,
VNUM,
DOCNUM,
POST,
SERIA,
NAMEFAS,
PART_ID,
GODENDO,
QUANT,
SERL)
AS
select
l.id,
l.name_id,
l.price,
l.pricefas,
l.quantfas,
l.doc_id,
v.svalue,
d.commitdate,
d.vnum,
d.docnum,
a.caption as post,
l.seria,
l.namefas as namefas,
l.part_id,
(select p.godendo from parts p where p.id=l.part_id ),
l.quant,
l.serl
from labfas l left join vals v on l.name_id=v.id
left join docs d on l.doc_id=d.id left join agents a on d.agent_id=a.id
;
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
SET TERM ^ ;
create or alter procedure PR_LABFAS (
PERIODBEG DM_DATE,
PERIODEND DM_DATE)
returns (
PART_ID DM_ID,
NAME_ID DM_UUID_NULL,
PRICE DM_DOUBLE,
DOC_ID DM_ID,
QUANT DM_DOUBLE,
SERIA DM_TEXT,
QUANTFAS DM_DOUBLE,
NAMEFAS DM_TEXT)
as
begin
for select dd.name_id, dd.price, dd.doc_id, dd.quant,dd.seria,qf.quantfas, qf.namefas, dd.part_id from vw_doc_detail dd
inner join docs d on d.id=dd.doc_id
left join quant_fas qf on dd.name_id=qf.name_id
where d.doc_type=1 and d.status=1 and dd.mmbsh containing 'делим' and (dd.doc_commitdate between :periodbeg and :periodend)
order by dd.name_id
into :name_id, :price, :doc_id, :quant, :seria, :quantfas, :namefas, :part_id do
begin
insert into labfas (name_id, price, doc_id, quant, seria, quantfas,namefas, part_id) values (:name_id, :price, :doc_id, :quant, :seria, :quantfas,:namefas, :part_id);
end
suspend;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_LABFAS;
GRANT SELECT ON DOCS TO PROCEDURE PR_LABFAS;
GRANT SELECT ON QUANT_FAS TO PROCEDURE PR_LABFAS;
GRANT INSERT ON LABFAS TO PROCEDURE PR_LABFAS;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_LABFAS TO SYSDBA;
SET TERM ^ ;
create or alter procedure PR_LABFAS_PROD (
PERIODBEG DM_DATE,
PERIODEND DM_DATE)
returns (
NAME_ID DM_UUID_NULL,
PRICE DM_DOUBLE,
DOC_ID DM_ID,
QUANT DM_DOUBLE,
SERIA DM_TEXT,
QUANTFAS DM_DOUBLE,
NAMEFAS DM_TEXT,
PART_ID DM_ID)
as
begin
for select dd.name_id, dd.price, dd.doc_id, dd.quant*(-1) as quant,dd.seria,qf.quantfas, qf.namefas, dd.part_id from vw_doc_detail dd inner join docs d
on d.id=dd.doc_id left join quant_fas qf on dd.name_id=qf.name_id
where d.doc_type in (3,11) and d.status=1 and mmbsh containing 'делим' and (dd.doc_commitdate between :periodbeg and :periodend)
order by dd.name_id
into :name_id, :price, :doc_id, :quant, :seria, :quantfas, :namefas , :part_id do
begin
insert into labfas (name_id, price, doc_id, quant, seria, quantfas,namefas, part_id ) values (:name_id, :price, :doc_id, :quant, :seria, :quantfas,:namefas, :part_id);
end
suspend;
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_LABFAS_PROD;
GRANT SELECT ON DOCS TO PROCEDURE PR_LABFAS_PROD;
GRANT SELECT ON QUANT_FAS TO PROCEDURE PR_LABFAS_PROD;
GRANT INSERT ON LABFAS TO PROCEDURE PR_LABFAS_PROD;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE PR_LABFAS_PROD TO SYSDBA;
SET TERM ^ ;
create or alter procedure OUT$LAB_CEN (
PART_ID DM_ID,
POVTOR integer)
returns (
ID DM_ID)
as
declare variable I DM_ID;
declare variable QUANTF DM_ID;
declare variable QUANT DM_ID;
begin
for select dd.id, iif(:povtor =1, trunc(dd.quant*dd.quantfas),1) from vw_labfas dd where dd.part_id=:part_id into :id,:quant do
begin
i=1;
while (i<=quant) do
begin
suspend;
i=i+1;
end
end
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON VW_LABFAS TO PROCEDURE OUT$LAB_CEN;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE OUT$LAB_CEN TO SYSDBA;
INSERT INTO SP$WDICTS (ID, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INSERTDT, INI, BMP, SORTING, FRAMECLASS) VALUES ((select max(id)+1 from SP$WDICTS), 0, 'Лабораторно-фасовочный журнал', NULL, 'labfas2', 0, NULL, '
[insertsql]
[deletesql_selected]
[deletesql]
[refreshsql]
select * from vw_labfas where id=:id
[selectsqlwithdeleted]
[selectsql]
select * from vw_labfas order by sname,id
[main]
sourcetablename=LABFAS
returnfieldname=ID
captionfieldname=ID
keyfieldname=ID
ViewID=labfas
RootGroupTableName=
ShowCaption=Лабораторно-фасовочный журнал
GetCaption=
GroupSelect=0
foldergroup=
initfolder_id=
dataset=0
InitTMSGroup_id=-600
[cfSelect]
selectfieldexpression=sname
AllwaysPartial=1
[form_show]
position=1
left=0
Top=0
Width=0
Height=0
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0
[form_get]
position=1
left=0
Top=0
Width=0
Height=0
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0
[childs]
bottomdock_units=0
bottomdock_size=0
rightdock_units=0
rightdock_size=0
[TMS_0]
group_id=794
caption=Сформировать
[editfields]
QUANTFAS=default
NAMEFAS=default
', NULL, NULL, NULL);
INSERT INTO GROUPS (ID, PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, SID, D$UUID, D$SRVUPDDT) VALUES (-600, -430, 'Инит лаб фас', 'TMS', 0, '14-NOV-2013 14:17:18', 0, NULL, -1, NULL, NULL, NULL, 0, NULL, 'GROUPS_M600', '30-DEC-2014 15:48:59');
