Лабораторно фасовочный журнал — различия между версиями

Текущая версия на 13:09, 3 ноября 2016

Общие сведения

В соответствии с пунктом 2.8 "Порядка отпуска лекарственных средств", утвержденного Приказом Минздравсоцразвития РФ от 14.12.2005 N 785 (в ред. от 06.08.2007), в исключительных случаях нарушения аптечной организацией вторичной заводской упаковки лекарственное средство должно отпускаться в аптечной упаковке с обязательным указанием наименования, заводской серии, срока годности лекарственного средства, серии и даты по лабораторно-фасовочному журналу.

Подготовка базы данных для установки

Выполните скрипт.

Если полей D$UUID, D$SRVUPDDT нет - исправьте запрос, создавать эти поля не нужно 

/******************************************************************************/

/******************************************************************************/
/***      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,
    QUANT)
AS
select
    0,--l.id,
    0,--l.name_id,
    0,--l.price,
    0,--l.pricefas,
    0,--l.quantfas,
    0,--l.doc_id,
    0,--v.svalue,
    d.commitdate,
    d.vnum,
    d.docnum,
    a.caption as post,
    0,--l.seria,
    0,--l.namefas as namefas,
    0--l.quant
from docs d join agents a on d.agent_id=a.id
;




/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


SET SQL DIALECT 3;

drop procedure OUT$LAB_CEN;
drop procedure PR_LABFAS_PROD;
drop procedure PR_LABFAS;
drop procedure PR_LABFAS_WAREBASE;
drop view VW_LABFAS;
delete from labfas;
drop table LABFAS;
drop table QUANT_FAS;


/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/


CREATE GENERATOR GEN_QUANT_FAS_ID;

CREATE TABLE QUANT_FAS (
    ID        BIGINT NOT NULL,
    NAME_ID   DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
    QUANTFAS  BIGINT,
    NAMEFAS   DM_TEXT COLLATE PXW_CYRL /* DM_TEXT = VARCHAR(250) */
);




/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE QUANT_FAS ADD CONSTRAINT PK_QUANT_FAS PRIMARY KEY (ID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: QUANT_FAS_BI0 */
CREATE OR ALTER TRIGGER QUANT_FAS_BI0 FOR QUANT_FAS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.id is null) then
    new.id = gen_id(GEN_QUANT_FAS_ID,1);
end
^


SET TERM ; ^

/******************************************************************************/
/***               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 in (1,2,20) and d.status=1 and dd.part_type =0 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 dd.part_type =0 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;

delete from SP$WDICTS where SID = 'labfas';

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, 'labfas', 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, '', '30-DEC-2014 15:48:59');

INSERT INTO REPORTS (ID, PARENT_ID, STATUS, REPORTTYPE, SORTING, CAPTION, PARAMS, WDICT_ID, DATA, D$UUID, D$SRVUPDDT) VALUES ((select max(id)+1 from REPORTS), 0, 0, 10, 100, 'Лабораторно-фасовочный журнал', NULL, (select max(id) from SP$WDICTS), NULL, '', '18-FEB-2015 12:09:51');

 

Импорт подпрограммы инициализации журнала

В таблице GROUPS для записи с ID -600 в поле INI импортируйте Файл:-600 LAB FAS.zip

Импорт печатной формы журнала

В таблице REPORTS для последней записи в поле DATA импортируйте Файл:Lab fas.zip (предварительно разархивируйте)

Импорт сетки отображения

Импортируйте сетку(настройки отображения) в программе менеджер Файл:Лаб фас сетка.zip (предварительно разархивируйте)

Запуск журнала

Программа Менеджер -> Справочники -> Дополнительные возможности -> Лабораторно-фасовочный журнал

Первоначальное окно запроса параметров расчета

Рассчитанные данные

Поля "Наименование фасовки" и "Кол-во частей" доступны для редактирования

Печать журнала / ценников (параметры)

Печать журнала / ценников (примеры печатных форм)

Теги: лабораторно фасовочный журнал, лаб фас, ЛФЖ