АСНА
Материал из wiki.standart-n.ru
Версия от 13:43, 11 июля 2016; Agk (обсуждение | вклад)
Содержание
Общие сведения
АСсоциация Независимых Аптек (АСНА)
Описание формата взаимодействия есть в регламенте информационного взаимодействия Приложение 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 хранится соответствие АСНА-кода и кода профиля аптеки. НЕ ОЧИЩАТЬ!
3) Таблица ASNA_AGENT_REGION: хранится соответствие ИНН и кода региона контрагента; НЕ ОЧИЩАТЬ!
4) Таблица ASNA_WAREBASE: временная таблица для сборки остатков (файл *_RST.txt), заполняется в момент работы ТМС, потом данные не нужны, каждый раз очищается автоматически.
5) Таблица ASNA_AGENTS_G$PROFILES: т.к. контрагенты у нас не централизованы, в этой таблице хранится соответствие ID/G$PROFILE_ID из AGENTS и кода профиля в таблице G$PROFILES. НЕ ОЧИЩАТЬ!
Выгрузка
Файлы выгрузки формируется ТМС "Выгрузка в АСНА" в spacepro, запуск из планировщика заданий. Файлы формируются примерно 1 час, после чего запускается из планировщика новое задание (т.к. spacepro не умеет работать с ftp сам) по выгрузке их на ftp (IP, логин/пароль в файле "Коды АСНА.xls "). АСНА просит выгружать ежедневно, чтобы файлы в 22-00 были на ftp. Каждый раз выгрузка идет за последнюю неделю, при этом данные в хранилище АСНЫ удаляются и загружаются заново, после чего на ftp файлы переносятся в папку с бекапами. Если на ftp положить 2 раза одни и те же движения/остатки, то при выгрузке данные задублируются!
