АСНА — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Общие сведения)
Строка 9: Строка 9:
 
  4) *_RST.txt - остатки в каждой аптеке на последний день периода выгрузки;
 
  4) *_RST.txt - остатки в каждой аптеке на последний день периода выгрузки;
  
−
==Выполнить скрипт (сводная или серверная базы)==
+
==Скрипт для обновления структуры базы (сводная или серверная базы)==
 
  <nowiki>
 
  <nowiki>
 
/******************************************************************************/
 
/******************************************************************************/
Строка 409: Строка 409:
 
  </nowiki>
 
  </nowiki>
  
−
==Общие сведения==
+
==Описание изменения структуры базы==
 +
1) Таблица ASNA_GOODS: приведенный справочник товаров. Соответствует формату файла goods.dbf из регламента.
 +
Наполнение автоматически триггером WARES_BU0_MGN_NAME_ASNA.
 +
Логика: при появлении новой позиции в wares с пустым mgn_name триггер ищет по ШК уже приведенное наименование и подставляет его,
 +
если по ШК не найдено ищем по name_id, если не найдено, подставляем из VALS по name_id.
 +
Далее если этой позции не найдено в ASNA_GOODS ни по имени ни по ШК, добавляем новую запись. НЕ ОЧИЩАТЬ!
 +
 
 +
2) Таблица ASNA_G$PROFILES. От АСНЫ получен файл "Коды АСНА.xls", там соответствие имени аптеки и внутреннего АСНА-кода.
 +
В таблице ASNA_G$PROFILES хранится соответствие АСНА-кода и кода профиля аптеки. НЕ ОЧИЩАТЬ!
 +
[[Файл:АСНА_аптеки.png|left|Пример]]

Версия 13:37, 11 июля 2016

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

АСсоциация Независимых Аптек (АСНА)

Описание формата взаимодействия есть в регламенте информационного взаимодействия Приложение 2 июль 2015 Файл:АСНА Приложение 2 июль 2015.zip 

Согласно него нужно выгрузить 4 файла:
1) goods.dbf - справочник товаров;
2) vendor.dbf - контрагенты;
3) *.txt - движения товаров по каждой аптеке за выгружаемый период;
4) *_RST.txt - остатки в каждой аптеке на последний день периода выгрузки;

Скрипт для обновления структуры базы (сводная или серверная базы)

/******************************************************************************/
/***               Generated by IBExpert 11.07.2016 11:33:31                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



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



CREATE TABLE ASNA_AGENTS_G$PROFILES (
    AGENT_ID          DM_ID /* DM_ID = BIGINT NOT NULL */,
    G$PROFILE_ID      DM_ID /* DM_ID = BIGINT NOT NULL */,
    G$PROFILE_ID_APT  DM_ID /* DM_ID = BIGINT NOT NULL */
);




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

ALTER TABLE ASNA_AGENTS_G$PROFILES ADD CONSTRAINT PK_ASNA_AGENTS_G$PROFILES_1 PRIMARY KEY (AGENT_ID, G$PROFILE_ID);


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


/******************************************************************************/
/***               Generated by IBExpert 11.07.2016 11:33:40                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



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



CREATE TABLE ASNA_AGENT_REGION (
    INN      DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    REGION   INTEGER NOT NULL,
    COMMENT  DM_TEXT /* DM_TEXT = VARCHAR(250) */
);




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

ALTER TABLE ASNA_AGENT_REGION ADD CONSTRAINT PK_ASNA_AGENT_REGION_1 PRIMARY KEY (INN);


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


/******************************************************************************/
/***               Generated by IBExpert 11.07.2016 11:33:47                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



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



CREATE TABLE ASNA_G$PROFILES (
    ID         DM_ID /* DM_ID = BIGINT NOT NULL */,
    ASNA_CODE  DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */
);




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

ALTER TABLE ASNA_G$PROFILES ADD CONSTRAINT PK_ASNA_G$PROFILES_1 PRIMARY KEY (ID);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE UNIQUE DESCENDING INDEX IDX_ASNA_G$PROFILES_1 ON ASNA_G$PROFILES (ID);
ALTER INDEX IDX_ASNA_G$PROFILES_1 INACTIVE;


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


/******************************************************************************/
/***               Generated by IBExpert 11.07.2016 11:33:53                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



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



CREATE TABLE ASNA_GOODS (
    ID        DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    MGN_NAME  DM_TEXT1024 NOT NULL /* DM_TEXT1024 = VARCHAR(1024) */,
    PRODUCER  DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    COUNTRY   DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    EAN       DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    INSERTDT  DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);




/******************************************************************************/
/***                           Unique Constraints                           ***/
/******************************************************************************/

ALTER TABLE ASNA_GOODS ADD CONSTRAINT UNQ1_ASNA_GOODS UNIQUE (MGN_NAME);


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

ALTER TABLE ASNA_GOODS ADD CONSTRAINT PK_ASNA_GOODS PRIMARY KEY (ID);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX ASNA_GOODS_IDX1 ON ASNA_GOODS (EAN);
CREATE INDEX ASNA_GOODS_IDX2 ON ASNA_GOODS (INSERTDT);


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


SET TERM ^ ;



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



/* Trigger: ASNA_GOODS_BI0 */
CREATE OR ALTER TRIGGER ASNA_GOODS_BI0 FOR ASNA_GOODS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.id is null) then
   new.id = UUID_TO_CHAR(GEN_UUID());
  new.insertdt = CURRENT_TIMESTAMP;
end
^


SET TERM ; ^



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


/******************************************************************************/
/***               Generated by IBExpert 11.07.2016 11:34:00                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



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



CREATE TABLE ASNA_WAREBASE (
    PART_ID        DM_ID /* DM_ID = BIGINT NOT NULL */,
    G$PROFILE_ID   DM_ID /* DM_ID = BIGINT NOT NULL */,
    MOTHERPART_ID  DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    QUANT          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    DDATE          DM_DATE /* DM_DATE = DATE */,
    DOC_ID         DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    PRICE          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    PRICE_O        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    NDS            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    WARE_ID        DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */
);




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

ALTER TABLE ASNA_WAREBASE ADD CONSTRAINT PK_ASNA_WAREBASE_1 PRIMARY KEY (PART_ID, G$PROFILE_ID);


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


SET TERM ^ ;



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



/* Trigger: ASNA_WAREBASE_AI0 */
CREATE OR ALTER TRIGGER ASNA_WAREBASE_AI0 FOR ASNA_WAREBASE
ACTIVE AFTER INSERT POSITION 0
AS
begin
  if (abs(coalesce(new.quant,0))< 0.01) then
   delete from ASNA_WAREBASE w where w.part_id = new.part_id and w.g$profile_id = new.g$profile_id;
end
^


/* Trigger: ASNA_WAREBASE_BI0 */
CREATE OR ALTER TRIGGER ASNA_WAREBASE_BI0 FOR ASNA_WAREBASE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.DDATE is not null) then
    select sum(dd2.quant) from doc_detail dd2 left join docs d on dd2.doc_id = d.id and dd2.g$profile_id = d.g$profile_id
     where dd2.part_id = new.part_id and dd2.g$profile_id = new.g$profile_id and dd2.doc_commitdate <= new.DDATE and d.status = 1 into new.quant;

  if (abs(coalesce(new.quant,0)) > 0.01) then
  begin
   select MOTHERPART_ID,DOC_ID from PR_GETMOTHERPART(new.part_id,new.g$profile_id) into new.MOTHERPART_ID, new.DOC_ID;

   select
    PRICE,
    PRICE_O ,
    WARE_ID,
    dp.nds
   from parts p
   inner join deps dp on p.g$profile_id = dp.g$profile_id and p.dep = dp.id
   where p.g$profile_id = new.g$profile_id and p.id = new.part_id into
    new.PRICE,
    new.PRICE_O,
    new.WARE_ID,
    new.nds;
  end
end
^


SET TERM ; ^



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


SET TERM ^ ;

create or alter procedure PR_ASNA_GET_GOODS (
    WARE_ID DM_UUID_NULL)
returns (
    GOODS_ID DM_UUID_NULL)
as
declare variable MGN_NAME DM_TEXT1024;
declare variable BARCODE DM_TEXT;
begin
  GOODS_ID = Null;

  select w.mgn_name, w.barcode from WARES w where id = :ware_id into :mgn_name, :barcode;
  if (:mgn_name is null and coalesce(:barcode, '') <> '') then
   select first 1 w.mgn_name from WARES w where barcode = :barcode into :mgn_name;

  if (:mgn_name is null and coalesce(:barcode, '') = '') then
    GOODS_ID = Null;
  else
  begin
   select id from asna_goods ag where ag.mgn_name = coalesce(Trim(:mgn_name),'$$$') into :GOODS_ID;
   if (:GOODS_ID is null) Then
    select first 1 id from asna_goods ag where ag.ean = coalesce(:barcode,'$$$') into :GOODS_ID;
  end

  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON WARES TO PROCEDURE PR_ASNA_GET_GOODS;
GRANT SELECT ON ASNA_GOODS TO PROCEDURE PR_ASNA_GET_GOODS;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_ASNA_GET_GOODS TO SYSDBA;

SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER WARES_BU0_MGN_NAME_ASNA FOR WARES
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
  --20150922 А.К. авто приведение, чтоб не запускать Менеджер
  if ((new.mgn_name is null) and (coalesce(Trim(new.barcode),'') <> '')) then
    select first 1 mgn_name from wares w where w.barcode = new.barcode and mgn_name <> '' into new.mgn_name;
  if (new.mgn_name is null) then
    select first 1 mgn_name from wares w where w.name_id = new.name_id and mgn_name <> '' into new.mgn_name;
  if (new.mgn_name is null) then
    select svalue from vals v where v.id = new.name_id into new.mgn_name;

  if (new.MGN_NAME is null) Then exit;
    --если в справочнике нет ни по названию ни по ШК, то заносим в ASNA_GOODS
  if ( (((select count(*) from ASNA_GOODS where MGN_NAME = coalesce(Trim(new.MGN_NAME),'$$$')) > 0) and new.MGN_NAME is not null) or
       (((select count(*) from ASNA_GOODS where ean = coalesce(new.barcode, '$$$')) > 0) and new.barcode is not null) )
       Then begin end
  else
  Begin
   insert into ASNA_GOODS(MGN_NAME, producer, country, ean)
   select
    Trim(new.MGN_NAME),
    (select svalue from vals where id = new.izg_id),
    (select svalue from vals where id = new.country_id),
    iif(coalesce(new.barcode,'') = '', (select first 1 barcode from wares w where w.MGN_NAME = Trim(new.MGN_NAME) and coalesce(new.barcode,'') <> ''), new.barcode)
   from rdb$database;
  end

end
^


SET TERM ; ^


update WARES set id = id;

Описание изменения структуры базы

1) Таблица ASNA_GOODS: приведенный справочник товаров. Соответствует формату файла goods.dbf из регламента.
Наполнение автоматически триггером WARES_BU0_MGN_NAME_ASNA.
Логика: при появлении новой позиции в wares с пустым mgn_name триггер ищет по ШК уже приведенное наименование и подставляет его, 
если по ШК не найдено ищем по name_id, если не найдено, подставляем из VALS по name_id. 
Далее если этой позции не найдено в ASNA_GOODS ни по имени ни по ШК, добавляем новую запись. НЕ ОЧИЩАТЬ!

2) Таблица ASNA_G$PROFILES. От АСНЫ получен файл "Коды АСНА.xls", там соответствие имени аптеки и внутреннего АСНА-кода.
В таблице ASNA_G$PROFILES хранится соответствие АСНА-кода и кода профиля аптеки. НЕ ОЧИЩАТЬ!
Пример
Источник — «http://wiki.standart-n.ru/index.php?title=АСНА&oldid=4252»