/******************************************************************************/
/****                      Creating stored procedures                      ****/
/******************************************************************************/
SET TERM ^ ;

CREATE PROCEDURE MA$PR_COMMIT_ORDER(
  ORDER_UUID /* TYPE OF DM_UUID */ CHAR(36) /* COLLATE WIN1251 - default */,
  STATUS INTEGER,
  STATUS_INFO /* TYPE OF DM_TEXT */ VARCHAR(250) CHARACTER SET WIN1251 /* COLLATE WIN1251 - default */,
  SUMMA /* TYPE OF DM_DOUBLE */ DOUBLE PRECISION)
RETURNS (
  ERROR_CODE INTEGER,
  ERROR_TEXT /* TYPE OF DM_TEXT */ VARCHAR(250) CHARACTER SET WIN1251 /* COLLATE WIN1251 - default */)
AS
BEGIN
  SUSPEND;
END
^

CREATE PROCEDURE MA$PR_CONSTRUCTOR
AS
BEGIN
  EXIT;
END
^

CREATE PROCEDURE MA$PR_NEW_ORDER(
  VNUM /* TYPE OF DM_ID */ BIGINT,
  ORDER_DATE /* TYPE OF DM_DATETIME */ TIMESTAMP,
  CLIENT_NAME /* TYPE OF DM_TEXT */ VARCHAR(250) CHARACTER SET WIN1251 /* COLLATE WIN1251 - default */,
  CLIENT_EMAIL /* TYPE OF DM_TEXT */ VARCHAR(250) CHARACTER SET WIN1251 /* COLLATE WIN1251 - default */,
  CLIENT_PHONE /* TYPE OF DM_TEXT */ VARCHAR(250) CHARACTER SET WIN1251 /* COLLATE WIN1251 - default */,
  CLIENT_UUID /* TYPE OF DM_UUID */ CHAR(36) /* COLLATE WIN1251 - default */,
  I_STATUS INTEGER)
RETURNS (
  ORDER_UUID /* TYPE OF DM_UUID */ CHAR(36) /* COLLATE WIN1251 - default */,
  DOC_ID /* TYPE OF DM_ID */ BIGINT)
AS
BEGIN
  SUSPEND;
END
^

CREATE PROCEDURE MA$PR_ORDER_ADD(
  I_ID /* TYPE OF DM_ID */ BIGINT,
  ORDER_UUID /* TYPE OF DM_UUID */ CHAR(36) /* COLLATE WIN1251 - default */,
  PART_ID /* TYPE OF DM_ID */ BIGINT,
  QUANT /* TYPE OF DM_DOUBLE */ DOUBLE PRECISION,
  PRICE /* TYPE OF DM_DOUBLE */ DOUBLE PRECISION)
RETURNS (
  ERROR_CODE /* TYPE OF DM_ID */ BIGINT,
  ERROR_TEXT /* TYPE OF DM_TEXT */ VARCHAR(250) CHARACTER SET WIN1251 /* COLLATE WIN1251 - default */)
AS
BEGIN
  SUSPEND;
END
^

CREATE PROCEDURE PR_I_DOC_COMMIT(
  DOC_ID /* DM_ID */ BIGINT)
AS
BEGIN
  EXIT;
END
^


/******************************************************************************/
/****                   Creating generators (sequences)                    ****/
/******************************************************************************/
SET TERM ; ^

CREATE GENERATOR GEN_I_ORDER_DETAIL_ID;

CREATE GENERATOR GEN_I_ORDER_TASKS_ID;

CREATE GENERATOR GEN_I_ORDERS_ID;

CREATE GENERATOR GEN_MA$PROT_ID;


/******************************************************************************/
/****              Creating tables (without computed fields)               ****/
/******************************************************************************/
CREATE TABLE I_ORDER_DETAIL (
    ID DM_ID NOT NULL,
    D$UUID DM_UUID NOT NULL /* COLLATE WIN1251 - default */,
    D$SRVUPDDT DM_DATETIME,
    ORDER_UUID DM_UUID /* COLLATE WIN1251 - default */,
    PART_UUID DM_UUID_NULL /* COLLATE WIN1251 - default */,
    PART_ID DM_ID,
    QUANT DM_DOUBLE,
    I_PRICE DM_DOUBLE,
    NAME_ID DM_UUID_NULL /* COLLATE WIN1251 - default */,
    SNAME DM_TEXT /* COLLATE WIN1251 - default */,
    IZG_ID DM_UUID_NULL /* COLLATE WIN1251 - default */,
    SIZG DM_TEXT /* COLLATE WIN1251 - default */,
    SERIA DM_TEXT /* COLLATE WIN1251 - default */,
    GODENDO DM_DATE,
    INSERTDT DM_DATETIME,
    WARE_ID DM_UUID_NULL /* COLLATE WIN1251 - default */,
    STATUS DM_STATUS NOT NULL,
    STATUS_INFO DM_TEXT /* COLLATE WIN1251 - default */,
    I_ID DM_ID);

CREATE TABLE I_ORDER_TASKS (
    ID DM_ID NOT NULL,
    TASK_TYPE DM_UUID /* COLLATE WIN1251 - default */,
    INSERTDT DM_DATETIME,
    SENDDATA DM_BLOBTEXT /* COLLATE WIN1251 - default */,
    STATUS DM_STATUS NOT NULL,
    STARTDT DM_DATETIME,
    ENDDT DM_DATETIME,
    ENDFLAG DM_STATUS,
    ENDTEXT DM_BLOBTEXT /* COLLATE WIN1251 - default */);

CREATE TABLE I_ORDERS (
    ID DM_ID NOT NULL,
    D$UUID DM_UUID /* COLLATE WIN1251 - default */,
    D$SRVUPDDT DM_DATETIME,
    VNUM DM_ID NOT NULL,
    ORDER_DATE DM_DATETIME NOT NULL,
    CLIENT_NAME DM_TEXT NOT NULL /* COLLATE WIN1251 - default */,
    CLIENT_EMAIL DM_TEXT NOT NULL /* COLLATE WIN1251 - default */,
    CLIENT_PHONE DM_TEXT NOT NULL /* COLLATE WIN1251 - default */,
    CLIENT_UUID DM_UUID_NULL /* COLLATE WIN1251 - default */,
    STATUS DM_STATUS NOT NULL,
    STATUS_INFO DM_TEXT /* COLLATE WIN1251 - default */,
    INSERTDT DM_DATETIME,
    SUMMA DM_DOUBLE,
    DOC_UUID DM_UUID_NULL /* COLLATE WIN1251 - default */,
    DOC_ID DM_ID NOT NULL,
    UPDATEDT DM_DATETIME,
    CHECKDOC_ID DM_ID_NULL,
    I_STATUS DM_STATUS,
    I_SOURCE DM_BLOBTEXT /* COLLATE WIN1251 - default */);

CREATE TABLE MA$PROT (
    ID DM_ID NOT NULL,
    METHOD DM_TEXT /* COLLATE WIN1251 - default */,
    CMD DM_TEXT /* COLLATE WIN1251 - default */,
    SENDDATA DM_BLOBTEXT /* COLLATE WIN1251 - default */,
    STATUS DM_STATUS,
    STARTDT DM_DATETIME,
    ENDDT DM_DATETIME,
    ENDFLAG DM_STATUS,
    ENDTEXT DM_BLOBTEXT /* COLLATE WIN1251 - default */,
    TASK_ID DM_ID);


/******************************************************************************/
/****                           Creating indices                           ****/
/******************************************************************************/
CREATE INDEX I_ORDER_DETAIL_IDX1
  ON I_ORDER_DETAIL (ID);

CREATE INDEX I_ORDER_DETAIL_IDX2
  ON I_ORDER_DETAIL (ORDER_UUID);

CREATE INDEX I_ORDER_DETAIL_IDX4
  ON I_ORDER_DETAIL (PART_ID);

CREATE INDEX I_ORDER_DETAIL_IDX5
  ON I_ORDER_DETAIL (SNAME);

CREATE INDEX I_ORDER_DETAIL_IDX6
  ON I_ORDER_DETAIL (SERIA);

CREATE INDEX I_ORDERS_IDX1
  ON I_ORDERS (VNUM);

CREATE INDEX I_ORDERS_IDX2
  ON I_ORDERS (ORDER_DATE);

CREATE INDEX I_ORDERS_IDX3
  ON I_ORDERS (CLIENT_NAME);

CREATE INDEX I_ORDERS_IDX4
  ON I_ORDERS (CLIENT_PHONE);

CREATE INDEX I_ORDERS_IDX5
  ON I_ORDERS (CLIENT_EMAIL);

CREATE INDEX I_ORDERS_IDX6
  ON I_ORDERS (DOC_ID);

CREATE DESC INDEX I_ORDERS_IDX7
  ON I_ORDERS (ID);

CREATE INDEX I_ORDERS_IDX8
  ON I_ORDERS (STATUS_INFO);

CREATE INDEX I_ORDERS_IDX9
  ON I_ORDERS (CHECKDOC_ID);


/******************************************************************************/
/****                            Creating views                            ****/
/******************************************************************************/

CREATE VIEW VW_I_ORDER_DETAIL (
    ID,
    I_ID,
    D$UUID,
    D$SRVUPDDT,
    ORDER_UUID,
    PART_UUID,
    PART_ID,
    QUANT,
    I_PRICE,
    NAME_ID,
    SNAME,
    IZG_ID,
    SIZG,
    SERIA,
    GODENDO,
    INSERTDT,
    WARE_ID,
    BCODE_IZG,
    BARCODE,
    SCOUNTRY,
    STATUS,
    STATUS_INFO,
    QUANT_REZERVED)
AS
select
    i.id,
    i.i_id,
    i.d$uuid,
    i.d$srvupddt,
    i.order_uuid,
    i.part_uuid,
    i.part_id,
    i.quant,
    i.i_price,
    i.name_id,
    i.sname,
    i.izg_id,
    i.sizg,
    i.seria,
    i.godendo,
    i.insertdt,
    i.ware_id,
    w.barcode,
    (select barcode from parts where parts.id=i.part_id),
    (select svalue from vals where vals.id=w.country_id),
    STATUS,
    STATUS_INFO,
    (select sum(quant) from doc_detail_active dda where dda.ware_id=i.ware_id and dda.part_id=0 and dda.doc_id=(select doc_id from i_orders o where o.d$uuid=i.order_uuid))
from i_order_detail i
  left join wares w on w.id=i.ware_id;

CREATE VIEW VW_I_ORDERS (
    ID,
    D$UUID,
    D$SRVUPDDT,
    VNUM,
    ORDER_DATE,
    CLIENT_NAME,
    CLIENT_EMAIL,
    CLIENT_PHONE,
    CLIENT_UUID,
    STATUS,
    STATUS_INFO,
    INSERTDT,
    SUMMA,
    DOC_UUID,
    DOC_ID,
    UPDATEDT,
    SSTATUS,
    I_STATUS,
    SI_STATUS)
AS
select     ID,
    D$UUID,
    D$SRVUPDDT,
    VNUM,
    ORDER_DATE,
    CLIENT_NAME,
    CLIENT_EMAIL,
    CLIENT_PHONE,
    CLIENT_UUID,
    STATUS,
    STATUS_INFO,
    INSERTDT,
    SUMMA,
    DOC_UUID,
    DOC_ID,
    UPDATEDT,
    case status
      when 0 then 'создание'
      when 1 then 'новый'
      when 2 then 'в сборке'
      when 3 then 'готов к выдаче'
      when 4 then 'выдан'
      when -1 then 'отменен'
      when -2 then 'расформирован'
      when -3 then 'проблемы с резервацией'
      when 5 then 'изменен, ожидает подтверждения'

      else status || ' - не определен'
    end,
    I_STATUS,
    case I_STATUS
      when 1 then 'Новый'
      when 3 then 'Отменен'
      when 8 then 'Подтвержден (изменение состава заказа подтверждено покупателем)'
      when -1 then 'устарел, есть новая версия заказа'
      else i_status || ' - не определен'
    end
 from i_orders;


/******************************************************************************/
/****                   Creating primary key constraints                   ****/
/******************************************************************************/
RECONNECT;

ALTER TABLE I_ORDER_DETAIL ADD CONSTRAINT PK_I_ORDER_DETAIL PRIMARY KEY (D$UUID);

ALTER TABLE I_ORDER_TASKS ADD CONSTRAINT PK_I_ORDER_TASKS PRIMARY KEY (ID);

ALTER TABLE I_ORDERS ADD CONSTRAINT PK_I_ORDERS PRIMARY KEY (D$UUID);

ALTER TABLE MA$PROT ADD CONSTRAINT PK_MA$PROT PRIMARY KEY (ID);


/******************************************************************************/
/****                     Creating unique constraints                      ****/
/******************************************************************************/
RECONNECT;

ALTER TABLE I_ORDER_DETAIL ADD CONSTRAINT UNQ1_I_ORDER_DETAIL UNIQUE (ORDER_UUID, PART_ID);

ALTER TABLE I_ORDERS ADD CONSTRAINT UNQ1_I_ORDERS UNIQUE (ID);


/******************************************************************************/
/****                          Creating triggers                           ****/
/******************************************************************************/
SET TERM ^ ;

CREATE TRIGGER I_ORDER_DETAIL_AD_DISTR FOR I_ORDER_DETAIL
ACTIVE
AFTER DELETE
POSITION 0 
AS
begin
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('I_ORDER_DETAIL',old.d$uuid,2,null)
  matching (TABLENAME,UUID);
end
^

CREATE TRIGGER I_ORDER_DETAIL_AU_DISTR FOR I_ORDER_DETAIL
ACTIVE
AFTER UPDATE
POSITION 0 
AS
begin
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
  begin
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('I_ORDER_DETAIL',new.d$uuid,1,null)
    matching (TABLENAME,UUID);
  end
end
^

CREATE TRIGGER I_ORDER_DETAIL_BI FOR I_ORDER_DETAIL
ACTIVE
BEFORE INSERT
POSITION 0 
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_i_order_detail_id,1);
  select p.d$uuid, w.name_id, (select svalue from vals where id=name_id),
    w.izg_id, (select svalue from vals where id=izg_id), seria, godendo
    , p.ware_id
    from parts p left join wares w on p.ware_id=w.id
    where p.id=new.part_id
   into new.part_uuid, new.name_id, new.sname, new.izg_id, new.sizg, new.seria, new.godendo, new.ware_id;
  new.insertdt='now';
  if (new.status is null) then new.status=0;
end
^

CREATE TRIGGER I_ORDER_DETAIL_BI_DISTR FOR I_ORDER_DETAIL
ACTIVE
BEFORE INSERT
POSITION 0 
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
    new.d$srvupddt='2000-01-01';
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('I_ORDER_DETAIL',new.d$uuid,0,null) matching (TABLENAME,UUID);
  end

end
^

CREATE TRIGGER I_ORDER_TASKS_BI FOR I_ORDER_TASKS
ACTIVE
BEFORE INSERT
POSITION 0 
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_i_order_tasks_id,1);
  new.insertdt='now';
end
^

CREATE TRIGGER I_ORDERS_AU_DISTR FOR I_ORDERS
ACTIVE
AFTER UPDATE
POSITION 0 
AS
begin
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
  begin
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('I_ORDERS',new.d$uuid,1,null)
    matching (TABLENAME,UUID);
  end
end
^

CREATE TRIGGER I_ORDERS_BI FOR I_ORDERS
ACTIVE
BEFORE INSERT
POSITION 0 
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_i_orders_id,1);
  new.insertdt='now';
  new.updatedt='now';
  select d$uuid from docs where id=new.doc_id into new.doc_uuid;
end
^

CREATE TRIGGER I_ORDERS_BI_DISTR FOR I_ORDERS
ACTIVE
BEFORE INSERT
POSITION 0 
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
    new.d$srvupddt='2000-01-01';
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('I_ORDERS',new.d$uuid,0,null) matching (TABLENAME,UUID);
  end

end
^

CREATE TRIGGER I_ORDERS_BU0 FOR I_ORDERS
ACTIVE
BEFORE UPDATE
POSITION 0 
AS
begin
  new.updatedt='now';
end
^

CREATE TRIGGER MA$PROT_BI FOR MA$PROT
ACTIVE
BEFORE INSERT
POSITION 0 
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_ma$prot_id,1);
end
^


/******************************************************************************/
/****                      Altering stored procedures                      ****/
/******************************************************************************/
ALTER PROCEDURE MA$PR_COMMIT_ORDER(
  ORDER_UUID TYPE OF DM_UUID /* COLLATE WIN1251 - default */,
  STATUS INTEGER,
  STATUS_INFO TYPE OF DM_TEXT /* COLLATE WIN1251 - default */,
  SUMMA TYPE OF DM_DOUBLE)
RETURNS (
  ERROR_CODE INTEGER,
  ERROR_TEXT TYPE OF DM_TEXT /* COLLATE WIN1251 - default */)
AS
declare variable ORDER_STATUS integer;
declare variable DOC_ID type of DM_ID;
declare variable CHECKDOC_ID type of DM_ID;
begin
  if (STATUS not in (1,-1)) then
  begin
    ERROR_CODE=5;
    error_text='Передан некорректный статус';
    suspend;
    exit;
  end
  ORDER_STATUS=-100;
  doc_id=null;
  select doc_id, status, coalesce(CHECKDOC_ID,0) from I_ORDERS where d$uuid=:order_uuid into doc_id, ORDER_STATUS, CHECKDOC_ID;
--  if (ORDER_STATUS<>0) then
  if (ORDER_STATUS not in (0,1,2,3)) then
  begin
    ERROR_CODE=1;
    error_text='Некорректный статус интернет-заказа ('||ORDER_STATUS||')';
    suspend;
    exit;
  end
  ORDER_STATUS=null;
  select status from docs where id=:doc_id into ORDER_STATUS;
  if (ORDER_STATUS is null) then
  begin
    ERROR_CODE=2;
    error_text='Ошибка создания сопроводительного документа для резервирования товара';
    suspend;
    exit;
  end
  if ((ORDER_STATUS<>2) and (ORDER_STATUS<>1)) then
  begin
    ERROR_CODE=3;
    error_text='Некорректный статус сопроводительного документа для резервирования товара';
    suspend;
    exit;
  end

  --994080 A.K. 2021-06-29
  --проблемы с отменой заказ из за переоценки
  if (STATUS=-1) then -- отмена заказа
  begin
    if (checkdoc_id<>0) then
    begin
      ORDER_STATUS=null;
      select status from docs where id=:checkdoc_id into ORDER_STATUS;
      if (ORDER_STATUS = 0) then
      begin
        ERROR_CODE=4;
        error_text='Найден текущий чек продажи, отмена невозможна!';
        suspend;
        exit;
      end 
    end

    update i_orders set STATUS=:status where d$uuid=:order_uuid;

    select CANCEL_RESULT from pr_doc_cancel(:doc_id, 1, 0, 0) into ORDER_STATUS;
  end

  if (summa is null) then summa=0;
  if (summa=0) then
    select sum(i_price*quant) from i_order_detail where order_uuid=:order_uuid into summa;
  update i_orders set STATUS=:status, STATUS_INFO=:STATUS_INFO, summa=:summa where d$uuid=:order_uuid;

  ERROR_CODE=0;
  error_text='';
  suspend;
end
^

ALTER PROCEDURE MA$PR_CONSTRUCTOR
AS
declare variable SID DM_TEXT;
begin
  if (not exists(select id from params where param_id='MEGAPTEKA')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS)
      values (0, 'MEGAPTEKA', 'МЕГАПТЕКА',0,'',0,-1,0);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_NETWORK_UUID')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_NETWORK_UUID', 'Идентификатор торговой сети в системе',1, 'B3FEC7DF-0204-4FA3-961C-82375D365E64',0,-1,0,5);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_NETWORK_NAME')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_NETWORK_NAME', 'Наименование торговой сети',1, 'ООО "Аптеки Айболит"',0,-1,0,10);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_STORE_UUID')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_STORE_UUID', 'Идентификатор торговой точки в системе',1, uuid_to_char(gen_uuid()),0,-1,0,15);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_WB_UPDATEDT')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_WB_UPDATEDT', 'Дата последнего обновления WAREBASE',6, null ,0,-1,0,20);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_REGION')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_REGION', 'Регион',1, 'Удмуртская Республика' ,0,-1,0,25);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_CITY')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_CITY', 'Населенный пункт',1, 'Ижевск' ,0,-1,0,30);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_ADDRESS')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_ADDRESS', 'Адрес',1, null ,0,-1,0,35);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_BRAND')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_BRAND', 'Название бренда аптеки',1, 'Айболит' ,0,-1,0,40);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_LATITUDE')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_LATITUDE', 'Широта',3, null ,0,-1,0,45);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_LONGITUDE')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_LONGITUDE', 'Долгота',3, null ,0,-1,0,50);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_PHONE')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_PHONE', 'Телефон',1, null ,0,-1,0,55);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_EMAIL')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_EMAIL', 'E-mail',1, null ,0,-1,0,60);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_SCHEDULE')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_SCHEDULE', 'График работы',1, 'Пн-Пт: 08 - 21, Сб-Вс: 08 - 20' ,0,-1,0,65);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_OGRN')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_OGRN', 'ОГРН',1, '1101841001217' ,0,-1,0,70);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_PRICESSQL')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_PRICESSQL', 'SQL запрос экспорта остатков',1, 'select part_id, sname, price, quant, round(wb.realquant) as realquant, seria, sizg, barcode, wb.bcode_izg, wb.barcode1, updatedt from warebase wb where part_type=0 and quant>0.999 and  not exists (select id from group_detail gd where gd.grouptable_id=cast(wb.part_id as dm_text) and gd.group_id  in (3,49,2070,-44,2069,2070)) and (updatedt>:updatedt or (updatedt is null and INSERTDT > :updatedt ))' ,0,-1,0,75);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_PARTCOUNT')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_PARTCOUNT', 'Макс кол-во товаров на один запрос',2, '500' ,0,-1,0,80);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_URL')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_URL', 'URL',1, 'https://exch-api.megapteka.ru' ,0,-1,0,85);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_MC')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_MC', 'MC',1, 'standart' ,0,-1,0,90);
  end 
  if (not exists(select id from params where param_id='MEGAPTEKA_AUTH_TOKEN')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MEGAPTEKA_AUTH_TOKEN', 'AUTH_TOKEN',1, '436aab60cc195bd45cad96ed01e3af7cc10b163b027721f45f12217be989c58e' ,0,-1,0,95);
  end 
  if (not exists(select id from params where param_id='MA_PARTPRICES_INTERVAL')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MA_PARTPRICES_INTERVAL', 'Интервал частичного обновления прайсов, в минутах',2, '10' ,0,-1,0,100);
  end
  if (not exists(select id from params where param_id='MA_MAIN_INTERVAL')) then
  begin
    insert into params (PARENT_ID,PARAM_ID,PARAM_CAPTION,PARAM_TYPE,PARAM_VALUE,AUDIT_ID,IMAGEINDEX,STATUS,SORTING)
      values ((select id from params where param_id='MEGAPTEKA'), 'MA_MAIN_INTERVAL', 'Секунд между обменом с сервером',2, '60' ,0,-1,0,105);
  end   
end
^

ALTER PROCEDURE MA$PR_NEW_ORDER(
  VNUM TYPE OF DM_ID,
  ORDER_DATE TYPE OF DM_DATETIME,
  CLIENT_NAME TYPE OF DM_TEXT /* COLLATE WIN1251 - default */,
  CLIENT_EMAIL TYPE OF DM_TEXT /* COLLATE WIN1251 - default */,
  CLIENT_PHONE TYPE OF DM_TEXT /* COLLATE WIN1251 - default */,
  CLIENT_UUID TYPE OF DM_UUID /* COLLATE WIN1251 - default */,
  I_STATUS INTEGER)
RETURNS (
  ORDER_UUID TYPE OF DM_UUID /* COLLATE WIN1251 - default */,
  DOC_ID TYPE OF DM_ID)
AS
declare variable ERROR_CODE integer;
declare variable ERROR_TEXT type of DM_TEXT;
begin
--    status_id: <integer> # ид статуса
--                         # 1 - Новый
--                         # 3 - Отменен
--                         # 8 - Подтвержден (изменение состава заказа подтверждено покупателем)

  select d$uuid, STATUS, STATUS_INFO from i_orders where vnum=:vnum and I_STATUS>0 into ORDER_UUID, ERROR_CODE, ERROR_TEXT;
  if (ORDER_UUID is not null) then
  begin -- записываем новую версию, помечаем все старые, что делать дальше, решим позже
    update i_orders set i_status=-1 where vnum=:vnum and I_STATUS>0;
  end 

  ORDER_UUID = uuid_to_char(gen_uuid());
--  if (i_status<>3) then
--  begin
    select DOC_ID from pr_newdoc(8,-1,0,'MA'||:vnum, current_timestamp,0) into DOC_ID;
    update docs set status=2 where id=:doc_id;
--  end
  insert into i_orders (D$UUID,VNUM,ORDER_DATE,CLIENT_NAME,CLIENT_EMAIL,CLIENT_PHONE,CLIENT_UUID,STATUS,DOC_ID, I_STATUS)
    values (:order_uuid,:VNUM,:ORDER_DATE,:CLIENT_NAME,:CLIENT_EMAIL,:CLIENT_PHONE,:CLIENT_UUID,0,:DOC_ID, :i_status);
  suspend;
end
^

create or alter procedure MA$PR_ORDER_ADD (
    I_ID type of DM_ID,
    ORDER_UUID type of DM_UUID,
    PART_ID type of DM_ID,
    QUANT type of DM_DOUBLE,
    PRICE type of DM_DOUBLE)
returns (
    ERROR_CODE type of DM_ID,
    ERROR_TEXT type of DM_TEXT)
as
declare variable AVLB_QNT type of DM_DOUBLE;
declare variable DOC_ID type of DM_ID;
declare variable ORDER_STATUS integer;
declare variable CURQUANT type of DM_DOUBLE;
begin
  doc_id=null;
  ORDER_STATUS=-100;
  select doc_id, status from I_ORDERS where d$uuid=:order_uuid into doc_id, ORDER_STATUS;
  if (ORDER_STATUS=-100) then
  begin
    ERROR_CODE=-1;
    error_text='Не найден заказ';
    suspend;
    exit;
  end

  ERROR_CODE=0;
  error_text='';
  if ((quant is null) or (quant<0)) then
  begin
    ERROR_CODE=1;
    error_text='Передано некорректное значение кол-ва';
--    suspend;
--    exit;
  end

  if (ORDER_STATUS not in (0,1,2,3)) then
  begin
    ERROR_CODE=2;
    error_text='Некорректный статус интернет-заказа';
--    suspend;
--    exit;
  end

  ORDER_STATUS=null;
  select status from docs where id=:doc_id into ORDER_STATUS;
  if (ORDER_STATUS is null) then
  begin
    ERROR_CODE=3;
    error_text='Ошибка создания сопроводительного документа для резервирования товара';
--    suspend;
--    exit;
  end
  if (ORDER_STATUS<>2) then
  begin
    ERROR_CODE=4;
    error_text='Некорректный статус сопроводительного документа для резервирования товара';
--    suspend;
--    exit;
  end
  curquant=0;

  delete from doc_detail_active dda where parent_id=(select id from doc_detail_active dda1 where dda1.doc_id=:doc_id and dda1.part_id=:part_id);
  delete from doc_detail_active where doc_id=:doc_id and part_id=:part_id;

  avlb_qnt=null;
  select
    quant+coalesce((select sum(quant) from doc_detail_active dda where dda.part_id=wb.part_id and quant<0),0) as avlb_qnt,
    coalesce((select sum(quant) from doc_detail_active dda1 where dda1.doc_id=:doc_id and part_id=wb.part_id),0) as cur_qnt
   from warebase wb where part_id=:part_id into AVLB_QNT, curquant;
  if (avlb_qnt is null) then
  begin
    ERROR_CODE=6;
    error_text='Партия не найдена';
  end
  else
  begin
    if ((AVLB_QNT-curquant)<(quant-0.000001)) then
    begin
--      ERROR_CODE=5;
--      error_text='Недостаточное количество';
    end
    else
    begin
      AVLB_QNT=quant;
    end 
  end

  if (ERROR_CODE=0) then
  begin
    select id, msg from pr_correctpart(:doc_id,:part_id,:AVLB_QNT) into ERROR_CODE, error_text;
    if (ERROR_CODE<=0) then
    begin
      ERROR_CODE=6;
    end
    else
    begin
      update doc_detail_active set price=:price, summa=:price*quant where id=:ERROR_CODE;
      update or insert into group_detail (group_id,parent_id,grouptable_id,grouptable) values (-1000, 0, :ERROR_CODE, 'DOC_DETAIL_ACTIVE') matching (GROUP_ID,GROUPTABLE_ID,GROUPTABLE);
      ERROR_CODE=0;
    end 
  end
  update or insert into I_ORDER_DETAIL (ORDER_UUID,PART_ID,QUANT,I_PRICE, status, status_info,i_id) values (:ORDER_UUID,:PART_ID,:QUANT,:PRICE,:ERROR_CODE,:error_text,:i_id) matching (ORDER_UUID,PART_ID);
  suspend;
end
^


ALTER PROCEDURE PR_I_DOC_COMMIT(
  DOC_ID DM_ID)
AS
declare variable ORDER_UUID type of DM_UUID;
declare variable ORDER_STATUS integer;
declare variable VNUM type of DM_ID;
begin
  select d$uuid, status, vnum from i_orders where CHECKDOC_ID=:doc_id into order_uuid, order_status, vnum;
  if (order_uuid is null) then -- не интернет заказ
    exit;
  update i_orders set status=4 where d$uuid=:order_uuid;
  insert into I_ORDER_TASKS (TASK_TYPE, SENDDATA, STATUS) values ('SET_ORDER_STATUS','id='||:VNUM||ascii_char(13)||ascii_char(10)||'status_id=2' , 0);
end
^

ALTER PROCEDURE PR_MEMBERSHIPS(
  DATA TYPE OF DM_TEXT1024 /* COLLATE WIN1251 - default */,
  SEPARATOR TYPE OF DM_TEXT /* COLLATE WIN1251 - default */,
  INCLUDEIMAGE DM_STATUS = null)
RETURNS (
  MEMBERSHIP TYPE OF DM_TEXT1024 /* COLLATE WIN1251 - default */)
AS
declare variable L integer;
declare variable I integer;
declare variable P integer;
declare variable C char(1);
declare variable GROUPTABLE_ID DM_TEXT;
declare variable GROUPTABLE type of DM_METADATANAME;
declare variable G_ID type of DM_ID;
declare variable CAPTION type of DM_TEXT;
declare variable COLOR varchar(250);
declare variable IMAGEINDEX DM_STATUS;
begin
--  if (INCLUDEIMAGE is NULL) then INCLUDEIMAGE=0;
  INCLUDEIMAGE=0; -- для интернет заказа в касисре
  membership='';
  l=CHARACTER_LENGTH(data);
  i=0; p=1;
  grouptable_id='0';
  while (i<l) do
  begin
    i=i+1;
    c=substring(data from i for 1);
    if (c='=') then
    begin
      grouptable=substring(data from p for i-p);
      p=i+1;
    end
    if (c=';') then
    begin
      grouptable_id=substring(data from p for i-p);
      p=i+1;
    end
    if (grouptable_id<>'0') then
    begin
      for
        select distinct g.id, g.caption, g.color, g.imageindex
          from GROUP_DETAIL d join GROUPS g on d.group_id=g.id and g.status <> 1
          where d.GROUPTABLE_ID=:grouptable_id and d.GROUPTABLE=:grouptable
          order by g.sorting,g.id
          into :g_id, :caption, :color, :imageindex
      do
      begin
        if (color is null) then color='16777215';
        if (includeimage<>0) then color=color || '|' || imageindex;
        membership=membership || ' ' || g_id || '=' || '{' || color || '}' || caption || 
SEPARATOR;
      end
      grouptable_id='0';
    end
  end

  if (grouptable_id<>'0') then
  begin
    grouptable_id=substring(data from p for l-p);
    for
      select distinct g.id, g.caption, g.color, g.imageindex
        from GROUP_DETAIL d join GROUPS g on d.group_id=g.id and g.status <> 1
        where d.GROUPTABLE_ID=:grouptable_id and d.GROUPTABLE=:grouptable
        order by g.sorting,g.id
        into :g_id, :caption, :color, :imageindex
    do
    begin
      if (color is null) then color='16777215';
      if (includeimage<>0) then color=color || '|' || imageindex;
      membership=membership || ' ' || g_id || '=' || '{' || color || '}' || caption || 
SEPARATOR;
    end
  end

  suspend;
end
^


/******************************************************************************/
/****                       Updating object comments                       ****/
/******************************************************************************/
SET TERM ; ^

DESCRIBE FIELD I_PRICE TABLE I_ORDER_DETAIL
'интернет-цена';

DESCRIBE FIELD STATUS TABLE I_ORDER_DETAIL
'0 - товар зарезервирован
-1 - недостаточное кол-во';

DESCRIBE FIELD TASK_TYPE TABLE I_ORDER_TASKS
' - GET_ORDERS
GET /exch/v4/orders
Импорт заказов
 - SET_ORDER_STATUS';

DESCRIBE FIELD VNUM TABLE I_ORDERS
'Номер заказа (тип Число, длина 9)';

DESCRIBE FIELD CLIENT_NAME TABLE I_ORDERS
'Имя заказчика';

DESCRIBE FIELD CLIENT_UUID TABLE I_ORDERS
'на будущее, наверняка будет код клиента для личного кабинета и истории';

DESCRIBE FIELD STATUS TABLE I_ORDERS
'0 - в процессе создания
1 - Новый
2 - В Сборке
3 - Готов к выдаче
4 - Выдан
5 - Изменен, ожидает подтверждения
-1 - Отмена
-2 - Расформирован
-3 - ошибка коммита';

DESCRIBE FIELD I_STATUS TABLE I_ORDERS
'статус сайта
1 - Новый
3 - Отменен
8 - Подтвержден (изменение состава заказа подтверждено покупателем)
-1 - устарел, есть новая версия заказа';

DESCRIBE PARAMETER SUMMA PROCEDURE MA$PR_COMMIT_ORDER
'если 0 или NULL - считается автоматом';