INSERT INTO PARAMS (ID, PARENT_ID, PARAM_ID, PARAM_CAPTION, PARAM_TYPE, PARAM_VALUE, AUDIT_ID, IMAGEINDEX, STATUS, INSERTDT, SORTING, PARAM_TYPE_DATA, PACKET, D$UUID, D$SRVUPDDT)
            VALUES (10034, -30, 'EXPORT_DBF_PATH', 'Директория для выгрузки автозаказа', 10, 'C:\ФСМ_Накладные\AutozakExport\', 9806, -1, 0, '2023-10-17 13:25:46', 3, 'InitialDir=
Filter=*.txt
Title=папка для выгрузки
ofFileMustExist=0', 58306, 'CF250F22-1056-4EC0-9BBF-92999F9B8A52', '2000-01-01 00:00:00');


INSERT INTO PARAMS (ID, PARENT_ID, PARAM_ID, PARAM_CAPTION, PARAM_TYPE, PARAM_VALUE, AUDIT_ID, IMAGEINDEX, STATUS, INSERTDT, SORTING, PARAM_TYPE_DATA, PACKET, D$UUID, D$SRVUPDDT)
            VALUES (10071, -100, 'PHARM_SM_CODE', 'Код аптеки в ФармСМ', 1, '31168', 9806, -1, 0, '2023-10-17 13:27:05', 18, NULL, 58298, '4E1F9D4D-5CC7-431A-AB3D-EB3E06EF890C', '2000-01-01 00:00:00');


INSERT INTO GROUPS (ID, PARENT_ID, CAPTION, GROUPTABLE, STATUS, INSERTDT, SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, SID, D$UUID, D$SRVUPDDT, EAN13) VALUES (2005, -520, 'Выгрузка автозаказа', 'TMS', 0, '17-OCT-2023 13:35:42.331', 0, NULL, -1, NULL, NULL, 99, 0, '1005', 'A70B80D3-6EBC-4406-8BC6-3F6E66148811', '17-JUN-2019 09:02:06', NULL);


/******************************************************************************/
/***               Generated by IBExpert 17.10.2023 15:42:21                ***/
/******************************************************************************/

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



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


CREATE GENERATOR GEN_ORDER_DATA_ID;

CREATE TABLE ORDER_DATA (
    ID                       DM_ID NOT NULL /* DM_ID = BIGINT */,
    MGN_NAME                 DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */,
    ORDER_QUANT              DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SHORT_ORDER_QUANT        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    OPTIMAL_ORDER_QUANT      DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    ASSORT_ORDER_QUANT       DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    LAST_PRIHOD_QUANT        SMALLINT,
    REALQUANT                DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    LAST_PRIHOD_DATE         DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    LAST_PRIHOD_PRICE        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    LAST_WEEK_SUM            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    LAST_2WEEKS_SUM          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    LAST_PERIOD_SUM          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    LAST_MONTH_SUM           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SEASON_SUM               DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    RASHOD_DOC_QUANT         DM_STATUS /* DM_STATUS = INTEGER */,
    PRIHOD_DOC_QUANT         DM_STATUS /* DM_STATUS = INTEGER */,
    LAST_OZ_DATE             DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    ELNAKL_QUANT             DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    QUANT                    DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    LAST_OZ_QUANT            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    ACTIVE_PRIHOD_DOC_QUANT  DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    LAST_OZ_PRICE            DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    LAST_RASHOD_DATE         DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    ACTIVE_ORDER_QUANT       DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    MGN_ID                   DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    BAD_FLAG                 DM_STATUS /* DM_STATUS = INTEGER */,
    INSERTDT                 DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    G$PROFILE_ID             DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    LOG_DATA                 DM_BLOBTEXT /* DM_BLOBTEXT = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */,
    LAST_PRIHOD_PRICEO       DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SIZG                     DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SCOUNTRY                 DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    BARCODE                  DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    STATUS                   DM_STATUS /* DM_STATUS = INTEGER */,
    MIN_ORDER                DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    MAX_ORDER                DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    IZG_ID                   DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
    ON_THE_WAY               DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    QUANT_BASE               DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    LAST_PERIOD_SUM_BASE     DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    ON_THE_WAY_SUM_BASE      DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    PARENT_ID                DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    ASSORT_ID                DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    ORIG_CODE                DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    PRICE_O                  DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */
);



/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE ORDER_DATA ADD CONSTRAINT PK_ORDER_DATA PRIMARY KEY (ID);


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

CREATE INDEX AUTO_INDEX_15 ON ORDER_DATA (MGN_NAME, IZG_ID);
CREATE INDEX ORDER_DATA_IDX1 ON ORDER_DATA (MGN_NAME);
CREATE DESCENDING INDEX ORDER_DATA_IDX2 ON ORDER_DATA (MGN_NAME);
CREATE DESCENDING INDEX ORDER_DATA_IDX3 ON ORDER_DATA (ID);


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



SET TERM ^ ;



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



/* Trigger: ORDER_DATA_BI */
CREATE OR ALTER TRIGGER ORDER_DATA_BI FOR ORDER_DATA
ACTIVE BEFORE INSERT POSITION 0
as
declare variable name_id dm_uuid;
begin
  if (new.id is null) then
    new.id = gen_id(gen_order_data_id,1);
  --Испр. AVO - 939237 31.05.2019 - из-за множеcтва пустых значений изготовителя, страны и ШК
   select first 1 id from vals where vtype=0 and svalue =  new.mgn_name order by id desc into :name_id;
   select first 1 w.BARCODE,
        (select svalue from vals where id = w.IZG_ID),
        (select svalue from vals where id = w.COUNTRY_ID),w.IZG_ID
   from WARES w where w.NAME_ID = :name_id and w.barcode <>'' into new.barcode, new.sizg,new.scountry, new.izg_id;

   if (new.barcode ='') then
    begin
     select first 1 name_id from warebase where sname =  new.mgn_name and bcode_izg is not null into :name_id;

     select first 1
        (select svalue from vals where id = w.IZG_ID),
        (select svalue from vals where id = w.COUNTRY_ID),
        w.BARCODE,w.IZG_ID
     from WARES w

     --where w.NAME_ID = (select first 1 id from vals where svalue =  new.mgn_name order by id desc)
     --where w.NAME_ID = (select first 1 id from vals where vtype=0 and svalue =  new.mgn_name order by id desc)
     where w.NAME_ID = :name_id
     --конец испр.
     order by id desc
     into new.sizg,new.scountry,new.barcode, new.izg_id;
    end
end
^

/* Trigger: ORDER_DATA_BIU999 */
CREATE OR ALTER TRIGGER ORDER_DATA_BIU999 FOR ORDER_DATA
ACTIVE BEFORE INSERT OR UPDATE POSITION 999
AS
begin
  --939063 AK 2019-06-04
  if ((coalesce(new.order_quant,0) > 0) or (coalesce(new.optimal_order_quant,0) > 0)) Then
  begin
      select first 1 p.orig_code, p.price_o from warebase w left join parts p on p.id = w.part_id where sname = new.mgn_name and coalesce(p.orig_code,'') <> '' order by part_id desc into new.orig_code, new.price_o;
      if (coalesce(new.orig_code,'') = '') then
        select first 1 p.orig_code, p.price_o from vw_parts p where sname = new.mgn_name and coalesce(p.orig_code,'') <> '' order by part_id desc into new.orig_code, new.price_o;
  end
end
^

/* Trigger: ORDER_DATA_BU0 */
CREATE OR ALTER TRIGGER ORDER_DATA_BU0 FOR ORDER_DATA
ACTIVE BEFORE UPDATE POSITION 0
AS
declare variable NAME_ID dm_uuid;
begin
  --Добавлено AVO - 939237 31.05.2019 - из-за множеcтва пустых значений изготовителя, страны и ШК
  if ((old.barcode = '') or
      (old.sizg = '') or
      (old.scountry = '') )  then
   begin
    select first 1 id from vals where vtype=0 and svalue =  new.mgn_name order by id desc into :name_id;
    select first 1 w.BARCODE,
        (select svalue from vals where id = w.IZG_ID),
        (select svalue from vals where id = w.COUNTRY_ID)
    from WARES w where w.NAME_ID = :name_id and w.barcode <>'' into new.barcode, new.sizg,new.scountry;

    if (new.barcode = '') then
     begin
       select first 1 name_id from warebase where sname =  new.mgn_name and bcode_izg is not null into :name_id;

       select first 1
         (select svalue from vals where id = w.IZG_ID),
         (select svalue from vals where id = w.COUNTRY_ID),w.BARCODE,w.IZG_ID
      from WARES w
      where w.NAME_ID = :name_id
      order by id desc
      into new.sizg,new.scountry,new.barcode,new.izg_id;
     end
   end
end
^
SET TERM ; ^



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


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

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



/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: VW_ORDER_DATA */
CREATE OR ALTER VIEW VW_ORDER_DATA(
    PARENT_ID,
    ON_THE_WAY_SUM_BASE,
    LAST_PERIOD_SUM_BASE,
    QUANT_BASE,
    ON_THE_WAY,
    ASSORT_ID,
    ID,
    MGN_NAME,
    ORDER_QUANT,
    SHORT_ORDER_QUANT,
    OPTIMAL_ORDER_QUANT,
    ASSORT_ORDER_QUANT,
    LAST_PRIHOD_QUANT,
    REALQUANT,
    LAST_PRIHOD_DATE,
    LAST_PRIHOD_PRICE,
    LAST_WEEK_SUM,
    LAST_2WEEKS_SUM,
    LAST_PERIOD_SUM,
    LAST_MONTH_SUM,
    SEASON_SUM,
    RASHOD_DOC_QUANT,
    PRIHOD_DOC_QUANT,
    LAST_OZ_DATE,
    ELNAKL_QUANT,
    QUANT,
    LAST_OZ_QUANT,
    ACTIVE_PRIHOD_DOC_QUANT,
    LAST_OZ_PRICE,
    LAST_RASHOD_DATE,
    ACTIVE_ORDER_QUANT,
    MGN_ID,
    BAD_FLAG,
    INSERTDT,
    G$PROFILE_ID,
    LOG_DATA,
    LAST_PRIHOD_PRICEO,
    STATUS,
    MIN_ORDER,
    MAX_ORDER,
    IZG_ID,
    SIZG,
    ORIG_CODE)
AS
select
parent_id,
on_the_way_sum_base,
last_period_sum_base,
QUANT_BASE,
ON_THE_WAY,
ASSORT_ID,
ID,
MGN_NAME,
ORDER_QUANT,
SHORT_ORDER_QUANT,
OPTIMAL_ORDER_QUANT,
ASSORT_ORDER_QUANT,
LAST_PRIHOD_QUANT,
REALQUANT,
LAST_PRIHOD_DATE,
LAST_PRIHOD_PRICE,
LAST_WEEK_SUM,
LAST_2WEEKS_SUM,
LAST_PERIOD_SUM,
LAST_MONTH_SUM,
SEASON_SUM,
RASHOD_DOC_QUANT,
PRIHOD_DOC_QUANT,
LAST_OZ_DATE,
ELNAKL_QUANT,
QUANT,
LAST_OZ_QUANT,
ACTIVE_PRIHOD_DOC_QUANT,
LAST_OZ_PRICE,
LAST_RASHOD_DATE,
ACTIVE_ORDER_QUANT,
MGN_ID,
BAD_FLAG,
INSERTDT,
G$PROFILE_ID,
LOG_DATA,
LAST_PRIHOD_PRICEO,
STATUS,
MIN_ORDER,
MAX_ORDER,
IZG_ID,
(select svalue from vals where id = od.izg_id),
ORIG_CODE
from order_data od
;




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