Обновление:Бонусы — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Структура базы)
Строка 53: Строка 53:
 
  Для этого в соответствующем поле вводим сумму и нажимаем кнопку "Подарить бонусы"
 
  Для этого в соответствующем поле вводим сумму и нажимаем кнопку "Подарить бонусы"
 
  [[Файл:Подарить бонусы.png]]
 
  [[Файл:Подарить бонусы.png]]
 +
 +
 +
==Перепись установки==
 +
*1. Создаём таблицы, вьюхи и процедуры
 +
<PRE>
 +
/******************************************************************************/
 +
/***                Generated by IBExpert 20.02.2016 8:40:36                ***/
 +
/******************************************************************************/
 +
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
 +
 +
 +
 +
/******************************************************************************/
 +
/***                                Tables                                ***/
 +
/******************************************************************************/
 +
 +
 +
CREATE GENERATOR GEN_AGENTS_DSC_ID;
 +
 +
CREATE TABLE AGENTS_DSC (
 +
    ID            DM_ID NOT NULL /* DM_ID = BIGINT */,
 +
    AGENT_ID      DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    DCARD        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    SUMMA        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    SUMMA_DSC    DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    DOC_ID        DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    INSERTDT      DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
 +
    STATUS        DM_STATUS /* DM_STATUS = INTEGER */,
 +
    LINK_ID      DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    OPER_TYPE    DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    DSC_RULE_ID  DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    D$UUID        DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
 +
    D$SRVUPDDT    DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
 +
    G$PROFILE_ID  DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    ENDDATE      DM_DATETIME /* DM_DATETIME = TIMESTAMP */
 +
);
 +
 +
 +
 +
 +
/******************************************************************************/
 +
/***                              Primary keys                              ***/
 +
/******************************************************************************/
 +
 +
ALTER TABLE AGENTS_DSC ADD CONSTRAINT PK_AGENTS_DSC PRIMARY KEY (ID);
 +
 +
 +
/******************************************************************************/
 +
/***                                Indices                                ***/
 +
/******************************************************************************/
 +
 +
CREATE INDEX AGENTS_DSC_IDX1 ON AGENTS_DSC (DCARD);
 +
CREATE INDEX AGENTS_DSC_IDX2 ON AGENTS_DSC (D$UUID);
 +
CREATE INDEX AGENTS_DSC_IDX3 ON AGENTS_DSC (AGENT_ID);
 +
CREATE INDEX AGENTS_DSC_IDX4 ON AGENTS_DSC (DOC_ID);
 +
CREATE INDEX AGENTS_DSC_IDX5 ON AGENTS_DSC (OPER_TYPE);
 +
 +
 +
/******************************************************************************/
 +
/***                                Triggers                                ***/
 +
/******************************************************************************/
 +
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
/******************************************************************************/
 +
/***                          Triggers for tables                          ***/
 +
/******************************************************************************/
 +
 +
 +
 +
/* Trigger: AGENTS_DSC_AD_DISTR */
 +
CREATE OR ALTER TRIGGER AGENTS_DSC_AD_DISTR FOR AGENTS_DSC
 +
ACTIVE AFTER DELETE POSITION 0
 +
AS
 +
begin
 +
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('AGENTS_DSC',old.d$uuid,2,NULL) matching (TABLENAME,UUID);
 +
end
 +
^
 +
 +
 +
/* Trigger: AGENTS_DSC_BI */
 +
CREATE OR ALTER TRIGGER AGENTS_DSC_BI FOR AGENTS_DSC
 +
ACTIVE BEFORE INSERT POSITION 0
 +
as
 +
begin
 +
  if (new.id is null) then
 +
    new.id = gen_id(gen_agents_dsc_id,1);
 +
  new.insertdt='now';
 +
  if (new.status is null) then
 +
    new.status=0;
 +
  if (new.summa is null) then
 +
    new.summa=0;
 +
  if (new.summa_dsc is null) then
 +
    new.summa_dsc=0;
 +
 +
  if (new.doc_id is not null) then
 +
  begin
 +
    if (new.link_id is null) then
 +
    new.link_id=(select link_id from docs where id=new.doc_id);
 +
  end
 +
  if (new.dsc_rule_id is null) then
 +
    new.dsc_rule_id=0;
 +
  if (new.enddate is null) then
 +
    new.enddate=new.insertdt+180;
 +
end
 +
^
 +
 +
 +
/* Trigger: AGENTS_DSC_BI_DISTR */
 +
CREATE OR ALTER TRIGGER AGENTS_DSC_BI_DISTR FOR AGENTS_DSC
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
  if (new.d$uuid is null) then
 +
  begin
 +
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
 +
    new.g$profile_id=2;
 +
  end
 +
  if (new.d$srvupddt is null) then
 +
    new.d$srvupddt='2000-01-01';
 +
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID)
 +
    values ('AGENTS_DSC',new.d$uuid,0,null) matching (TABLENAME,UUID);
 +
end
 +
^
 +
 +
 +
/* Trigger: AGENTS_DSC_BU0 */
 +
CREATE OR ALTER TRIGGER AGENTS_DSC_BU0 FOR AGENTS_DSC
 +
ACTIVE BEFORE UPDATE POSITION 0
 +
AS
 +
begin
 +
if (new.doc_id<>old.doc_id) then
 +
  new.link_id=(select link_id from docs where id=new.doc_id);
 +
end
 +
^
 +
 +
 +
/* Trigger: AGENTS_DSC_BU_DISTR */
 +
CREATE OR ALTER TRIGGER AGENTS_DSC_BU_DISTR FOR AGENTS_DSC
 +
ACTIVE BEFORE UPDATE POSITION 0
 +
AS
 +
begin
 +
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
 +
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('AGENTS_DSC',new.d$uuid,1,NULL) matching (TABLENAME,UUID);
 +
end
 +
^
 +
 +
SET TERM ; ^
 +
 +
 +
 +
/******************************************************************************/
 +
/***                          Fields descriptions                          ***/
 +
/******************************************************************************/
 +
 +
COMMENT ON COLUMN AGENTS_DSC.OPER_TYPE IS
 +
'1 - Приход
 +
2 - Расход
 +
3 - Подарок
 +
4 - Закрытие бонусов';
 +
 +
 +
 +
/******************************************************************************/
 +
/***                              Privileges                              ***/
 +
/******************************************************************************/
 +
CREATE OR ALTER VIEW VW_AGENTS_DSC(
 +
    ID,
 +
    AGENT_ID,
 +
    DCARD,
 +
    SUMMA,
 +
    SUMMA_DSC,
 +
    DOC_ID,
 +
    INSERTDT,
 +
    STATUS,
 +
    LINK_ID,
 +
    OPER_TYPE,
 +
    DSC_RULE_ID,
 +
    D$UUID,
 +
    D$SRVUPDDT,
 +
    SOPER_TYPE,
 +
    ENDDATE)
 +
AS
 +
select
 +
    id,
 +
    agent_id,
 +
    dcard,
 +
    summa,
 +
    summa_dsc,
 +
    doc_id,
 +
    insertdt,
 +
    status,
 +
    link_id,
 +
    oper_type,
 +
    dsc_rule_id,
 +
    d$uuid,
 +
    d$srvupddt,
 +
    iif(oper_type=1,'Приход',iif(oper_type=2,'Расход',iif(oper_type=3,'Подарок','Закрытие бонусов'))),
 +
    enddate
 +
from agents_dsc;
 +
/******************************************************************************/
 +
/***                Generated by IBExpert 20.02.2016 8:42:32                ***/
 +
/******************************************************************************/
 +
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
 +
 +
 +
 +
/******************************************************************************/
 +
/***                                Tables                                ***/
 +
/******************************************************************************/
 +
 +
 +
CREATE GENERATOR GEN_DSC_RULES_ID;
 +
 +
CREATE TABLE DSC_RULES (
 +
    ID          DM_ID NOT NULL /* DM_ID = BIGINT */,
 +
    CAPTION    DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    SUMMA_BEG  DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    SUMMA_END  DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    DSC        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    DATEBEG    DM_DATE /* DM_DATE = DATE */,
 +
    DATEEND    DM_DATE /* DM_DATE = DATE */,
 +
    STATUS      DM_STATUS /* DM_STATUS = INTEGER */,
 +
    D$UUID      DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
 +
    D$SRVUPDDT  DM_DATETIME /* DM_DATETIME = TIMESTAMP */
 +
);
 +
 +
 +
 +
 +
/******************************************************************************/
 +
/***                              Primary keys                              ***/
 +
/******************************************************************************/
 +
 +
ALTER TABLE DSC_RULES ADD CONSTRAINT PK_DSC_RULES PRIMARY KEY (ID);
 +
 +
 +
/******************************************************************************/
 +
/***                                Triggers                                ***/
 +
/******************************************************************************/
 +
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
/******************************************************************************/
 +
/***                          Triggers for tables                          ***/
 +
/******************************************************************************/
 +
 +
 +
 +
/* Trigger: DSC_RULES_AD_DISTR */
 +
CREATE OR ALTER TRIGGER DSC_RULES_AD_DISTR FOR DSC_RULES
 +
ACTIVE AFTER DELETE POSITION 0
 +
AS
 +
begin
 +
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('DSC_RULES',old.d$uuid,2,NULL) matching (TABLENAME,UUID);
 +
end
 +
^
 +
 +
 +
/* Trigger: DSC_RULES_BI */
 +
CREATE OR ALTER TRIGGER DSC_RULES_BI FOR DSC_RULES
 +
ACTIVE BEFORE INSERT POSITION 0
 +
as
 +
begin
 +
  if (new.id is null) then
 +
    new.id = gen_id(gen_DSC_RULES_id,1);
 +
  if (new.status is null) then
 +
  new.status=0;
 +
end
 +
^
 +
 +
 +
/* Trigger: DSC_RULES_BI_DISTR */
 +
CREATE OR ALTER TRIGGER DSC_RULES_BI_DISTR FOR DSC_RULES
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
  if (new.d$uuid is null) then
 +
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
 +
  if (new.d$srvupddt is null) then
 +
    new.d$srvupddt='2000-01-01';
 +
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID)
 +
    values ('DSC_RULES',new.d$uuid,0,null) matching (TABLENAME,UUID);
 +
end
 +
^
 +
 +
 +
/* Trigger: DSC_RULES_BU_DISTR */
 +
CREATE OR ALTER TRIGGER DSC_RULES_BU_DISTR FOR DSC_RULES
 +
ACTIVE BEFORE UPDATE POSITION 0
 +
AS
 +
begin
 +
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
 +
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('DSC_RULES',new.d$uuid,1,NULL) matching (TABLENAME,UUID);
 +
end
 +
^
 +
 +
SET TERM ; ^
 +
 +
 +
 +
/******************************************************************************/
 +
/***                              Privileges                              ***/
 +
/******************************************************************************/
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_CLOSE_BONUS
 +
as
 +
declare variable AGENT_ID DM_ID_NULL;
 +
declare variable DCARD DM_TEXT;
 +
declare variable SUMMA DM_DOUBLE;
 +
declare variable SUMMA_DSC DM_DOUBLE;
 +
begin
 +
  for select distinct ad.dcard from agents_dsc ad
 +
  where ad.status=1 into :dcard do
 +
  begin
 +
    select first 1 id from agents where dover_face=:dcard order by id into :agent_id;
 +
    select sum(summa), sum(round(summa_dsc)) from agents_dsc where dcard=:dcard and status=1 into :summa, :summa_dsc;
 +
    if (summa is null) then summa=0;
 +
    if (summa_dsc is null) then summa_dsc=0;
 +
    insert into agents_dsc (agent_id,dcard,summa,summa_dsc,doc_id,status,oper_type,link_id)
 +
    values (:agent_id, :dcard,:summa*(-1),:summa_dsc*(-1),0,1,4,0);
 +
  end
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statements are generated automatically */
 +
 +
GRANT SELECT,INSERT ON AGENTS_DSC TO PROCEDURE PR_CLOSE_BONUS;
 +
GRANT SELECT ON AGENTS TO PROCEDURE PR_CLOSE_BONUS;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_CLOSE_BONUS TO SYSDBA;
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_MINUS_BONUS (
 +
    DOC_ID DM_ID,
 +
    AGENT_ID DM_ID_NULL)
 +
as
 +
declare variable DCARD DM_TEXT;
 +
declare variable PART_ID DM_ID_NULL;
 +
declare variable SUMMA DM_DOUBLE;
 +
declare variable DSC_RULE_ID DM_ID_NULL;
 +
declare variable SUM_DSC DM_DOUBLE;
 +
declare variable PARENT_ID DM_ID_NULL;
 +
declare variable TEMP_ID DM_ID_NULL;
 +
begin
 +
if (agent_id is null) then exit;
 +
select parent_id from docs d where d.id=:doc_id into :parent_id;
 +
select dover_face from agents where id=:agent_id into :dcard;
 +
/*select first 1 ad.dsc_rule_id from agents_dsc  ad order by ad.insertdt desc into :dsc_rule_id;
 +
  for select dd.part_id, (dd.price*dd.quant-abs(dd.sum_dsc)) from doc_detail dd
 +
  where dd.doc_id=:doc_id into :part_id, :summa do
 +
  begin
 +
    if (:part_id in (select part_id from doc_detail dd
 +
    where dd.doc_id in (select doc_id from agents_dsc ad where ad.agent_id=:agent_id and ad.oper_type=1 and ad.status=1))) then
 +
    begin
 +
      --sum_dsc=round(:price*:quant*(-1)*:dsc_rule_id/100,-2);    -- исправить тут
 +
      sum_dsc=:summa*(-1)*:dsc_rule_id/100;
 +
      INSERT INTO AGENTS_DSC (ID, AGENT_ID, DCARD, SUMMA, SUMMA_DSC, DOC_ID, STATUS, LINK_ID, OPER_TYPE, DSC_RULE_ID)
 +
      VALUES (null, :agent_id,:dcard ,:summa*(-1) , :sum_dsc, :doc_id, 1, :doc_id, 5,:dsc_rule_id) ;
 +
    end
 +
  end
 +
  */
 +
---2012 11 13 - ann ---->
 +
 +
  select ad.summa_dsc*(-1), ad.summa*(-1), ad.dsc_rule_id, ad.id
 +
  from agents_dsc ad where ad.doc_id=:parent_id and ad.oper_type=1 and ad.status=1 and g$profile_id = 2
 +
  into :sum_dsc,:summa, :dsc_rule_id, :temp_id;
 +
  if (:sum_dsc is not null and :sum_dsc<>0 and temp_id is not null) then
 +
    INSERT INTO AGENTS_DSC (ID, AGENT_ID, DCARD, SUMMA, SUMMA_DSC, DOC_ID, STATUS, LINK_ID, OPER_TYPE, DSC_RULE_ID)
 +
    VALUES (null, :agent_id,:dcard ,:summa, :sum_dsc, :doc_id, 1, :doc_id, 5,:dsc_rule_id) ;
 +
 +
  select abs(ad.summa_dsc), ad.dsc_rule_id, ad.id from agents_dsc ad where ad.doc_id=:parent_id and ad.oper_type=2
 +
  and ad.status=1 and g$profile_id = 2 into :sum_dsc, :dsc_rule_id, :part_id;
 +
  if (:sum_dsc is not null and :sum_dsc<>0 and part_id is not null) then
 +
    INSERT INTO AGENTS_DSC (ID, AGENT_ID, DCARD, SUMMA, SUMMA_DSC, DOC_ID, STATUS, LINK_ID, OPER_TYPE, DSC_RULE_ID)
 +
    VALUES (null, :agent_id,:dcard ,0, :sum_dsc, :doc_id, 1, :doc_id, 5,:dsc_rule_id) ;
 +
---2012 11 13 - ann <----
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statements are generated automatically */
 +
 +
GRANT SELECT ON DOCS TO PROCEDURE PR_MINUS_BONUS;
 +
GRANT SELECT ON AGENTS TO PROCEDURE PR_MINUS_BONUS;
 +
GRANT SELECT,INSERT ON AGENTS_DSC TO PROCEDURE PR_MINUS_BONUS;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_MINUS_BONUS TO SYSDBA;
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_GETSUM_DSC (
 +
    ACTIVE_DOC_ID DM_ID_NULL)
 +
returns (
 +
    SUMMA DM_DOUBLE,
 +
    SUMMA_DSC DM_DOUBLE,
 +
    DSC_ALL DM_DOUBLE,
 +
    SUMMA_ACTIVE DM_DOUBLE,
 +
    TEK_DSC DM_DOUBLE,
 +
    SUMMA_DSC_USE_ACTIVE DM_DOUBLE,
 +
    LINK_ID DM_ID_NULL,
 +
    AGENT_ID DM_ID_NULL,
 +
    DSC_RULE_ID DM_ID_NULL,
 +
    SAGENT DM_TEXT,
 +
    CALCSUMMA_ACTIVE DM_DOUBLE,
 +
    SUM_DSC_ACTIVE DM_DOUBLE)
 +
as
 +
declare variable LAST_RASHOD_DT DM_DATETIME;
 +
declare variable LAST_DOC_ID DM_ID_NULL;
 +
begin
 +
  select a.id, a.caption from docs d left join agents a on a.id=d.agent_id where d.id=:active_doc_id
 +
  into :agent_id, :sagent;
 +
 +
  select first 1 insertdt,doc_id from agents_dsc ad where agent_id=:agent_id and ad.status=1 and ad.oper_type=2
 +
  into :last_rashod_dt,:last_doc_id;
 +
 +
--if (last_doc_id is null) then
 +
  begin
 +
    last_doc_id=-1;
 +
    last_rashod_dt=current_timestamp-180;
 +
  end
 +
 +
 +
  select sum(ad.summa), sum(round(ad.summa_dsc))
 +
  from agents_dsc ad
 +
  where  --(ad.doc_id=:last_doc_id and ad.oper_type<>2) or
 +
  agent_id=:agent_id and ad.status=1 and
 +
  ((ad.oper_type in (1,2,3,4,5) and ad.insertdt > :last_rashod_dt) or (ad.doc_id=:last_doc_id and ad.oper_type<>2))
 +
  into :summa, :summa_dsc;
 +
 +
  select sum(abs(round(ad.summa_dsc)))
 +
  from agents_dsc ad
 +
  where agent_id=:agent_id and ad.status=1 and ad.status=0 and ad.oper_type=2
 +
  into :summa_dsc_use_active;
 +
 +
  if (summa_dsc_use_active is null) then
 +
    summa_dsc_use_active=0;
 +
 +
  if (summa is null) then summa=0;
 +
  if (summa_dsc is null) then summa_dsc=0;
 +
 +
  select d2.link_id from docs d2 where d2.id=:active_doc_id into :link_id;
 +
 +
  select abs(sum(da.summa)), abs(sum(da.price*da.quant)),abs(sum(da.sum_dsc)) from doc_detail_active da
 +
  left join docs d on da.doc_id=d.id
 +
  where d.link_id=:link_id
 +
  into :summa_active,:calcsumma_active,:sum_dsc_active;
 +
  if (summa_active is null) then summa_active=0;
 +
 +
  --select round((dr.dsc*:summa_active/100)), id
 +
  select (dr.dsc*:summa_active/100), id
 +
  from dsc_rules dr where dr.summa_beg<=(:summa+:summa_active) and dr.summa_end>(:summa+:summa_active)
 +
  and current_date between dr.datebeg and dr.dateend
 +
  and dr.status<>-1
 +
  into :tek_dsc, :dsc_rule_id;
 +
 +
  if (tek_dsc is null) then tek_dsc=0;
 +
  -- tek_dsc=round(tek_dsc,-2);
 +
  tek_dsc=round(tek_dsc);
 +
  dsc_all=summa_dsc-summa_dsc_use_active;
 +
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statements are generated automatically */
 +
 +
GRANT SELECT ON DOCS TO PROCEDURE PR_GETSUM_DSC;
 +
GRANT SELECT ON AGENTS TO PROCEDURE PR_GETSUM_DSC;
 +
GRANT SELECT ON AGENTS_DSC TO PROCEDURE PR_GETSUM_DSC;
 +
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_GETSUM_DSC;
 +
GRANT SELECT ON DSC_RULES TO PROCEDURE PR_GETSUM_DSC;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_GETSUM_DSC TO SYSDBA;
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_GETSUM_DSC_AGENT_ID (
 +
    AGENT_ID DM_ID)
 +
returns (
 +
    SUMMA_DSC DM_DOUBLE)
 +
as
 +
declare variable LAST_RASHOD_DT DM_DATETIME;
 +
declare variable LAST_DOC_ID DM_ID;
 +
begin
 +
  select first 1 insertdt,doc_id from agents_dsc ad where agent_id=:agent_id and ad.status=1 and ad.oper_type=2
 +
  into :last_rashod_dt,:last_doc_id;
 +
 +
-- if (last_doc_id is null) then
 +
  begin
 +
    last_doc_id=-1;
 +
    last_rashod_dt=current_timestamp-180;
 +
  end
 +
 +
 +
  select sum(round(ad.summa_dsc))
 +
  from agents_dsc ad
 +
  where  --(ad.doc_id=:last_doc_id and ad.oper_type<>2) or
 +
  agent_id=:agent_id and ad.status=1 and
 +
  ((ad.oper_type in (1,2,3,4,5) and ad.insertdt > :last_rashod_dt) or (ad.doc_id=:last_doc_id and ad.oper_type<>2))
 +
  into :summa_dsc;
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statements are generated automatically */
 +
 +
GRANT SELECT ON AGENTS_DSC TO PROCEDURE PR_GETSUM_DSC_AGENT_ID;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_GETSUM_DSC_AGENT_ID TO SYSDBA;
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_GETSUM_DSC_KREDIT (
 +
    CARD DM_TEXT,
 +
    ACTIVE_DOC_ID DM_ID_NULL,
 +
    ACTIVE_SUMMA DM_DOUBLE)
 +
returns (
 +
    SUMMA DM_DOUBLE,
 +
    SUMMA_DSC DM_DOUBLE,
 +
    DSC_ALL DM_DOUBLE,
 +
    SUMMA_ACTIVE DM_DOUBLE,
 +
    TEK_DSC DM_DOUBLE,
 +
    SUMMA_DSC_USE_ACTIVE DM_DOUBLE,
 +
    LINK_ID DM_ID_NULL,
 +
    AGENT_ID DM_ID_NULL,
 +
    DSC_RULE_ID DM_ID_NULL)
 +
as
 +
begin
 +
  select first 1 id from agents where dover_face=:card order by id into :agent_id;
 +
 +
  select sum(ad.summa), sum(round(ad.summa_dsc))
 +
  from agents_dsc ad
 +
  where dcard=:card
 +
  and ad.status=1 and ad.oper_type in (1,2,3,4,5) into :summa, :summa_dsc;
 +
 +
  select sum(abs(round(ad.summa_dsc)))
 +
  from agents_dsc ad
 +
  where dcard=:card and ad.status=0 and ad.oper_type=2
 +
  into :summa_dsc_use_active;
 +
 +
  if (summa_dsc_use_active is null) then
 +
    summa_dsc_use_active=0;
 +
 +
  if (summa is null) then summa=0;
 +
  if (summa_dsc is null) then summa_dsc=0;
 +
 +
  select d2.link_id from docs d2 where d2.id=:active_doc_id into :link_id;
 +
 +
  select abs(sum(da.summa)) from doc_detail_active da
 +
  left join docs d on da.doc_id=d.id
 +
  where d.link_id=:link_id
 +
  into :summa_active;
 +
  if (summa_active is null) then summa_active=:active_summa;
 +
  if (summa_active is null) then summa_active=0;
 +
 +
  select round((dr.dsc*:summa_active/100)), id
 +
  from dsc_rules dr where dr.summa_beg<=(:summa+:summa_active) and dr.summa_end>(:summa+:summa_active)
 +
  and current_date between dr.datebeg and dr.dateend
 +
  and dr.status<>-1
 +
  into :tek_dsc, :dsc_rule_id;
 +
 +
  if (tek_dsc is null) then tek_dsc=0;
 +
  tek_dsc=round(tek_dsc,-2);
 +
  dsc_all=summa_dsc-summa_dsc_use_active;
 +
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statements are generated automatically */
 +
 +
GRANT SELECT ON AGENTS TO PROCEDURE PR_GETSUM_DSC_KREDIT;
 +
GRANT SELECT ON AGENTS_DSC TO PROCEDURE PR_GETSUM_DSC_KREDIT;
 +
GRANT SELECT ON DOCS TO PROCEDURE PR_GETSUM_DSC_KREDIT;
 +
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_GETSUM_DSC_KREDIT;
 +
GRANT SELECT ON DSC_RULES TO PROCEDURE PR_GETSUM_DSC_KREDIT;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_GETSUM_DSC_KREDIT TO SYSDBA;
 +
/******************************************************************************/
 +
/***              Generated by IBExpert 20.02.2016 11:14:23                ***/
 +
/******************************************************************************/
 +
 +
/******************************************************************************/
 +
/***      Following SET SQL DIALECT is just for the Database Comparer      ***/
 +
/******************************************************************************/
 +
SET SQL DIALECT 3;
 +
 +
 +
 +
/******************************************************************************/
 +
/***                                Tables                                ***/
 +
/******************************************************************************/
 +
 +
 +
CREATE GENERATOR GEN_PREPARE_SALE_ID;
 +
 +
CREATE TABLE PREPARE_SALE (
 +
    ID              DM_ID NOT NULL /* DM_ID = BIGINT */,
 +
    Y              DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    M              DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    D              DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    W              DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    DOC_COMMITDATE  DM_DATE /* DM_DATE = DATE */,
 +
    GROUP_ID        DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    TRADER_ID      DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
 +
    SNAME          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    SIZG            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    SCOUNTRY        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    PART_ID        DM_ID /* DM_ID = BIGINT */,
 +
    WARE_ID        DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
 +
    NAME_ID        DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
 +
    COUNTRY_ID      DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
 +
    IZG_ID          DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
 +
    REGN            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    SERIA          DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    SERT            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
 +
    QUANT          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    PRICE          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    PRICE_Z        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    DOC_ID          DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    AGENT_ID        DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    DOC_TYPE        DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    CONTRACT_ID    DM_ID_NULL /* DM_ID_NULL = BIGINT */,
 +
    SUMM1          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    SUMM2          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    SUMM3          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    SUMMA          DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    SUMMA_BONUS    DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    SUMM_DSC        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    SUMM_RASR      DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
 +
    KOEF_TRADER    DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */
 +
);
 +
 +
 +
 +
 +
/******************************************************************************/
 +
/***                              Primary keys                              ***/
 +
/******************************************************************************/
 +
 +
ALTER TABLE PREPARE_SALE ADD CONSTRAINT PK_PREPARE_SALE PRIMARY KEY (ID);
 +
 +
 +
/******************************************************************************/
 +
/***                                Indices                                ***/
 +
/******************************************************************************/
 +
 +
CREATE INDEX PREPARE_SALE_IDX1 ON PREPARE_SALE (DOC_COMMITDATE);
 +
CREATE UNIQUE INDEX PREPARE_SALE_IDX2 ON PREPARE_SALE (ID);
 +
 +
 +
/******************************************************************************/
 +
/***                                Triggers                                ***/
 +
/******************************************************************************/
 +
 +
 +
 +
SET TERM ^ ;
 +
 +
 +
 +
/******************************************************************************/
 +
/***                          Triggers for tables                          ***/
 +
/******************************************************************************/
 +
 +
 +
 +
/* Trigger: PREPARE_SALE_BI0 */
 +
CREATE OR ALTER TRIGGER PREPARE_SALE_BI0 FOR PREPARE_SALE
 +
ACTIVE BEFORE INSERT POSITION 0
 +
AS
 +
begin
 +
  if (new.id is null) then
 +
    new.id = gen_id(gen_prepare_sale_id,1);
 +
  select ware_id,regn,seria,sert,price,price_z from parts where id=new.part_id into
 +
  new.ware_id,new.regn,new.seria,new.sert,new.price,new.price_z;
 +
  --select caption from agents a where a.id=new.agent_id into new.sagent;
 +
  new.y = extract (year from new.doc_commitdate);
 +
  new.m = extract (month from new.doc_commitdate);
 +
  new.d = extract (day from new.doc_commitdate);
 +
  new.w = extract (week from new.doc_commitdate);
 +
  select name_id,izg_id,country_id from wares where id=new.ware_id into new.name_id,new.izg_id,new.country_id;
 +
  select first 1 gd.group_id from group_detail gd where gd.grouptable='PARTS' and gd.grouptable_id=new.part_id into new.group_id;
 +
  if (new.group_id is null) then
 +
    select first 1 gd.group_id from group_detail gd where gd.grouptable='PARTS.NAME_ID' and gd.grouptable_id=new.name_id into new.group_id;
 +
  select svalue from vals where id=new.name_id into new.sname;
 +
  select svalue from vals where id=new.izg_id into new.sizg;
 +
  select svalue from vals where id=new.country_id into new.scountry;
 +
end
 +
^
 +
 +
SET TERM ; ^
 +
 +
 +
 +
/******************************************************************************/
 +
/***                              Privileges                              ***/
 +
/******************************************************************************/
 +
SET TERM ^ ;
 +
 +
create or alter procedure PR_PREPARE_SALE (
 +
    DATEBEG DM_DATE,
 +
    DATEEND DM_DATE)
 +
returns (
 +
    VERB DM_TEXT)
 +
as
 +
declare variable DOC_ID DM_ID;
 +
declare variable DOC_SUMM1 DM_DOUBLE;
 +
declare variable DOC_SUMM2 DM_DOUBLE;
 +
declare variable DOC_SUMM3 DM_DOUBLE;
 +
declare variable DOC_SUMMA DM_DOUBLE;
 +
declare variable CONTRACT_ID DM_ID;
 +
declare variable AGENT_ID DM_ID;
 +
declare variable DOC_TYPE DM_ID;
 +
declare variable DOC_DETAIL_ID DM_ID;
 +
declare variable DOC_COMMITDATE DM_DATE;
 +
declare variable PART_ID DM_ID;
 +
declare variable QUANT DM_DOUBLE;
 +
declare variable SUMMA DM_DOUBLE;
 +
declare variable DOCTOR_ID DM_UUID_NULL;
 +
declare variable DOCTOR_ID2 DM_UUID_NULL;
 +
declare variable DOCTOR_ID3 DM_UUID_NULL;
 +
declare variable DOCTOR_ID4 DM_UUID_NULL;
 +
declare variable DOCTOR_ID5 DM_UUID_NULL;
 +
declare variable DOCTOR_ID6 DM_UUID_NULL;
 +
declare variable COUNT_PROD DM_STATUS;
 +
declare variable SUM_DSC DM_DOUBLE;
 +
declare variable SUMMA_BONUS DM_DOUBLE;
 +
begin
 +
  delete from prepare_sale where doc_commitdate between :DATEBEG and :DATEEND;
 +
  for select id,summ1,summ2,summ3,summa,contract_id,agent_id,doc_type from docs where-- id=723 and
 +
  commitdate between :datebeg and :dateend+1 and doc_type in (3,9) and status=1 into
 +
  :doc_id,:doc_summ1,:doc_summ2,:doc_summ3,:doc_summa,:contract_id,:agent_id,:doc_type do
 +
  begin
 +
    summa_bonus=0;
 +
    select sum(coalesce(ad.summa,0)) from agents_dsc ad where ad.doc_id=:doc_id and ad.status=1 and ad.oper_type=2 into :summa_bonus;
 +
    for select id,doc_commitdate,part_id,quant,summa,sum_dsc from doc_detail dd where dd.doc_id=:doc_id into
 +
    :DOC_DETAIL_ID,:doc_commitdate,:part_id,:quant,:summa,:sum_dsc  do
 +
    begin
 +
      for select DOCTOR_ID
 +
            from doc_detail_doctor where DOC_DETAIL_ID=:doc_detail_id and doc_id=:doc_id
 +
      into DOCTOR_ID do
 +
      begin
 +
      COUNT_PROD=0;
 +
      if (DOCTOR_ID<>'0' and DOCTOR_ID is not null and DOCTOR_ID<>'') then COUNT_PROD=COUNT_PROD+1;
 +
      --if (DOCTOR_ID2<>'0' and DOCTOR_ID2 is not null and DOCTOR_ID2<>'') then COUNT_PROD=COUNT_PROD+1;
 +
      --if (DOCTOR_ID3<>'0' and DOCTOR_ID3 is not null and DOCTOR_ID3<>'') then COUNT_PROD=COUNT_PROD+1;
 +
      --if (DOCTOR_ID4<>'0' and DOCTOR_ID4 is not null and DOCTOR_ID4<>'') then COUNT_PROD=COUNT_PROD+1;
 +
      --if (DOCTOR_ID5<>'0' and DOCTOR_ID5 is not null and DOCTOR_ID5<>'') then COUNT_PROD=COUNT_PROD+1;
 +
      --if (DOCTOR_ID6<>'0' and DOCTOR_ID6 is not null and DOCTOR_ID6<>'') then COUNT_PROD=COUNT_PROD+1;
 +
      end
 +
 +
      for select DOCTOR_ID from DOC_DETAIL_DOCTOR where
 +
      DOC_DETAIL_ID=:doc_detail_id into :DOCTOR_ID do
 +
      begin
 +
        if (DOCTOR_ID<>'0' and DOCTOR_ID is not null and DOCTOR_ID<>'') then
 +
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
 +
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
 +
                              (:DOC_COMMITDATE,:DOCTOR_ID,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
 +
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
 +
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
 +
 +
        if (DOCTOR_ID2<>'0' and DOCTOR_ID2 is not null and DOCTOR_ID2<>'') then
 +
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
 +
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
 +
                              (:DOC_COMMITDATE,:DOCTOR_ID2,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
 +
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
 +
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
 +
        if (DOCTOR_ID3<>'0' and DOCTOR_ID3 is not null and DOCTOR_ID3<>'') then
 +
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
 +
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
 +
                              (:DOC_COMMITDATE,:DOCTOR_ID3,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
 +
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
 +
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
 +
        if (DOCTOR_ID4<>'0' and DOCTOR_ID4 is not null and DOCTOR_ID4<>'') then
 +
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
 +
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
 +
                              (:DOC_COMMITDATE,:DOCTOR_ID4,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
 +
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
 +
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
 +
        if (DOCTOR_ID5<>'0' and DOCTOR_ID5 is not null and DOCTOR_ID5<>'') then
 +
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
 +
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
 +
                              (:DOC_COMMITDATE,:DOCTOR_ID5,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
 +
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
 +
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
 +
          if (DOCTOR_ID6<>'0' and DOCTOR_ID6 is not null and DOCTOR_ID6<>'') then
 +
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
 +
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
 +
                              (:DOC_COMMITDATE,:DOCTOR_ID6,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
 +
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
 +
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
 +
      end
 +
    end
 +
  end
 +
  VERB='';
 +
  suspend;
 +
end^
 +
 +
SET TERM ; ^
 +
 +
/* Following GRANT statements are generated automatically */
 +
 +
GRANT SELECT,INSERT,DELETE ON PREPARE_SALE TO PROCEDURE PR_PREPARE_SALE;
 +
GRANT SELECT ON DOCS TO PROCEDURE PR_PREPARE_SALE;
 +
GRANT SELECT ON AGENTS_DSC TO PROCEDURE PR_PREPARE_SALE;
 +
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_PREPARE_SALE;
 +
GRANT SELECT ON DOC_DETAIL_DOCTOR TO PROCEDURE PR_PREPARE_SALE;
 +
 +
/* Existing privileges on this procedure */
 +
 +
GRANT EXECUTE ON PROCEDURE PR_PREPARE_SALE TO SYSDBA;
 +
</PRE>

Версия 18:09, 20 февраля 2016

Шаблон

alecsandr:D:\clients\bolgar\20150518\ZTRADE.FDB
На основании данного шаблона нужно перенести необходимые данные.
Внимание! в обновлении участвуют типовые процедуры и скрипты, не "затереть" старый функционал!

Структура базы

AGENTS_DSC
VW_AGENTS_DSC
DSC_RULES
PR_CLOSE_BONUS
PR_MINUS_BONUS
PR_DOC_COMMIT
PR_GETSUM_DSC
PR_GETSUM_DSC_AGENT_ID
PR_GETSUM_DSC_KREDIT
PR_PREPARE_SALE

SP$WDICTS

Перенести из шаблона справочники, где sid:
AGENTS_DR + TMS инициализации
AGENTS_DSC

Скрипты касса:

Перенести скрипты
-307 После печати чека
-310 После выбора агента документа
-315 Перед сканированием штрихкода
-317 Перед отменой чека

Руководство пользователя

1.
При выборе контрагента на чек откроется окно
Начислен бонусов.png
На данной форме можно посмотреть информацию 
Сумма накопления - сумма всех продаж по этому контрагенту с учетом скидок
Сумма доступных бонусов
Сумма бонусов от текущей покупки

В данном примере стоит ограничение на использование бонусов, не более 25% от суммы чека.
Нажимаем на кнопку "ок". 

2. 
В чеке использованные бонусы суммируются со скидкой и отображаются в колонке "Изменение в руб."
Скидка бонусами.png

3.
Для просмотра истории бонусов в программе "Менеджер" на закладке "Бонусы" набираем фамилию контрагента.
В нижней части программы можно увидеть когда, как и на каком основании были начислены или списаны бонусы.
История бонусов.png

4.
При необходимости бонусы клиентам можно подарить.
Для этого в соответствующем поле вводим сумму и нажимаем кнопку "Подарить бонусы"
Подарить бонусы.png


Перепись установки

  • 1. Создаём таблицы, вьюхи и процедуры 
/******************************************************************************/
/***                Generated by IBExpert 20.02.2016 8:40:36                ***/
/******************************************************************************/

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



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


CREATE GENERATOR GEN_AGENTS_DSC_ID;

CREATE TABLE AGENTS_DSC (
    ID            DM_ID NOT NULL /* DM_ID = BIGINT */,
    AGENT_ID      DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    DCARD         DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SUMMA         DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUMMA_DSC     DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    DOC_ID        DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    INSERTDT      DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    STATUS        DM_STATUS /* DM_STATUS = INTEGER */,
    LINK_ID       DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    OPER_TYPE     DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    DSC_RULE_ID   DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    D$UUID        DM_UUID NOT NULL /* DM_UUID = CHAR(36) NOT NULL */,
    D$SRVUPDDT    DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    G$PROFILE_ID  DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    ENDDATE       DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);




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

ALTER TABLE AGENTS_DSC ADD CONSTRAINT PK_AGENTS_DSC PRIMARY KEY (ID);


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

CREATE INDEX AGENTS_DSC_IDX1 ON AGENTS_DSC (DCARD);
CREATE INDEX AGENTS_DSC_IDX2 ON AGENTS_DSC (D$UUID);
CREATE INDEX AGENTS_DSC_IDX3 ON AGENTS_DSC (AGENT_ID);
CREATE INDEX AGENTS_DSC_IDX4 ON AGENTS_DSC (DOC_ID);
CREATE INDEX AGENTS_DSC_IDX5 ON AGENTS_DSC (OPER_TYPE);


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



SET TERM ^ ;



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



/* Trigger: AGENTS_DSC_AD_DISTR */
CREATE OR ALTER TRIGGER AGENTS_DSC_AD_DISTR FOR AGENTS_DSC
ACTIVE AFTER DELETE POSITION 0
AS
begin
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('AGENTS_DSC',old.d$uuid,2,NULL) matching (TABLENAME,UUID);
end
^


/* Trigger: AGENTS_DSC_BI */
CREATE OR ALTER TRIGGER AGENTS_DSC_BI FOR AGENTS_DSC
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_agents_dsc_id,1);
  new.insertdt='now';
  if (new.status is null) then
    new.status=0;
  if (new.summa is null) then
    new.summa=0;
  if (new.summa_dsc is null) then
    new.summa_dsc=0;

  if (new.doc_id is not null) then
  begin
    if (new.link_id is null) then
    new.link_id=(select link_id from docs where id=new.doc_id);
  end
  if (new.dsc_rule_id is null) then
    new.dsc_rule_id=0;
  if (new.enddate is null) then
    new.enddate=new.insertdt+180;
end
^


/* Trigger: AGENTS_DSC_BI_DISTR */
CREATE OR ALTER TRIGGER AGENTS_DSC_BI_DISTR FOR AGENTS_DSC
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
    new.g$profile_id=2;
  end
  if (new.d$srvupddt is null) then
    new.d$srvupddt='2000-01-01';
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID)
    values ('AGENTS_DSC',new.d$uuid,0,null) matching (TABLENAME,UUID);
end
^


/* Trigger: AGENTS_DSC_BU0 */
CREATE OR ALTER TRIGGER AGENTS_DSC_BU0 FOR AGENTS_DSC
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
 if (new.doc_id<>old.doc_id) then
   new.link_id=(select link_id from docs where id=new.doc_id);
end
^


/* Trigger: AGENTS_DSC_BU_DISTR */
CREATE OR ALTER TRIGGER AGENTS_DSC_BU_DISTR FOR AGENTS_DSC
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('AGENTS_DSC',new.d$uuid,1,NULL) matching (TABLENAME,UUID);
end
^

SET TERM ; ^



/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

COMMENT ON COLUMN AGENTS_DSC.OPER_TYPE IS 
'1 - Приход
2 - Расход
3 - Подарок
4 - Закрытие бонусов';



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/
CREATE OR ALTER VIEW VW_AGENTS_DSC(
    ID,
    AGENT_ID,
    DCARD,
    SUMMA,
    SUMMA_DSC,
    DOC_ID,
    INSERTDT,
    STATUS,
    LINK_ID,
    OPER_TYPE,
    DSC_RULE_ID,
    D$UUID,
    D$SRVUPDDT,
    SOPER_TYPE,
    ENDDATE)
AS
select
    id,
    agent_id,
    dcard,
    summa,
    summa_dsc,
    doc_id,
    insertdt,
    status,
    link_id,
    oper_type,
    dsc_rule_id,
    d$uuid,
    d$srvupddt,
    iif(oper_type=1,'Приход',iif(oper_type=2,'Расход',iif(oper_type=3,'Подарок','Закрытие бонусов'))),
    enddate
from agents_dsc;
/******************************************************************************/
/***                Generated by IBExpert 20.02.2016 8:42:32                ***/
/******************************************************************************/

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



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


CREATE GENERATOR GEN_DSC_RULES_ID;

CREATE TABLE DSC_RULES (
    ID          DM_ID NOT NULL /* DM_ID = BIGINT */,
    CAPTION     DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SUMMA_BEG   DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUMMA_END   DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    DSC         DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    DATEBEG     DM_DATE /* DM_DATE = DATE */,
    DATEEND     DM_DATE /* DM_DATE = DATE */,
    STATUS      DM_STATUS /* DM_STATUS = INTEGER */,
    D$UUID      DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    D$SRVUPDDT  DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);




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

ALTER TABLE DSC_RULES ADD CONSTRAINT PK_DSC_RULES PRIMARY KEY (ID);


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



SET TERM ^ ;



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



/* Trigger: DSC_RULES_AD_DISTR */
CREATE OR ALTER TRIGGER DSC_RULES_AD_DISTR FOR DSC_RULES
ACTIVE AFTER DELETE POSITION 0
AS
begin
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('DSC_RULES',old.d$uuid,2,NULL) matching (TABLENAME,UUID);
end
^


/* Trigger: DSC_RULES_BI */
CREATE OR ALTER TRIGGER DSC_RULES_BI FOR DSC_RULES
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_DSC_RULES_id,1);
  if (new.status is null) then
   new.status=0;
end
^


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


/* Trigger: DSC_RULES_BU_DISTR */
CREATE OR ALTER TRIGGER DSC_RULES_BU_DISTR FOR DSC_RULES
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('DSC_RULES',new.d$uuid,1,NULL) matching (TABLENAME,UUID);
end
^

SET TERM ; ^



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/
SET TERM ^ ;

create or alter procedure PR_CLOSE_BONUS
as
declare variable AGENT_ID DM_ID_NULL;
declare variable DCARD DM_TEXT;
declare variable SUMMA DM_DOUBLE;
declare variable SUMMA_DSC DM_DOUBLE;
begin
  for select distinct ad.dcard from agents_dsc ad
  where ad.status=1 into :dcard do
  begin
    select first 1 id from agents where dover_face=:dcard order by id into :agent_id;
    select sum(summa), sum(round(summa_dsc)) from agents_dsc where dcard=:dcard and status=1 into :summa, :summa_dsc;
    if (summa is null) then summa=0;
    if (summa_dsc is null) then summa_dsc=0;
    insert into agents_dsc (agent_id,dcard,summa,summa_dsc,doc_id,status,oper_type,link_id)
     values (:agent_id, :dcard,:summa*(-1),:summa_dsc*(-1),0,1,4,0);
  end
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT,INSERT ON AGENTS_DSC TO PROCEDURE PR_CLOSE_BONUS;
GRANT SELECT ON AGENTS TO PROCEDURE PR_CLOSE_BONUS;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_CLOSE_BONUS TO SYSDBA;
SET TERM ^ ;

create or alter procedure PR_MINUS_BONUS (
    DOC_ID DM_ID,
    AGENT_ID DM_ID_NULL)
as
declare variable DCARD DM_TEXT;
declare variable PART_ID DM_ID_NULL;
declare variable SUMMA DM_DOUBLE;
declare variable DSC_RULE_ID DM_ID_NULL;
declare variable SUM_DSC DM_DOUBLE;
declare variable PARENT_ID DM_ID_NULL;
declare variable TEMP_ID DM_ID_NULL;
begin
if (agent_id is null) then exit;
select parent_id from docs d where d.id=:doc_id into :parent_id;
select dover_face from agents where id=:agent_id into :dcard;
/*select first 1 ad.dsc_rule_id from agents_dsc  ad order by ad.insertdt desc into :dsc_rule_id;
  for select dd.part_id, (dd.price*dd.quant-abs(dd.sum_dsc)) from doc_detail dd
  where dd.doc_id=:doc_id into :part_id, :summa do
  begin
     if (:part_id in (select part_id from doc_detail dd
     where dd.doc_id in (select doc_id from agents_dsc ad where ad.agent_id=:agent_id and ad.oper_type=1 and ad.status=1))) then
     begin
       --sum_dsc=round(:price*:quant*(-1)*:dsc_rule_id/100,-2);    -- исправить тут
       sum_dsc=:summa*(-1)*:dsc_rule_id/100;
       INSERT INTO AGENTS_DSC (ID, AGENT_ID, DCARD, SUMMA, SUMMA_DSC, DOC_ID, STATUS, LINK_ID, OPER_TYPE, DSC_RULE_ID)
       VALUES (null, :agent_id,:dcard ,:summa*(-1) , :sum_dsc, :doc_id, 1, :doc_id, 5,:dsc_rule_id) ;
     end
  end
  */
---2012 11 13 - ann ---->

  select ad.summa_dsc*(-1), ad.summa*(-1), ad.dsc_rule_id, ad.id
  from agents_dsc ad where ad.doc_id=:parent_id and ad.oper_type=1 and ad.status=1 and g$profile_id = 2
  into :sum_dsc,:summa, :dsc_rule_id, :temp_id;
  if (:sum_dsc is not null and :sum_dsc<>0 and temp_id is not null) then
    INSERT INTO AGENTS_DSC (ID, AGENT_ID, DCARD, SUMMA, SUMMA_DSC, DOC_ID, STATUS, LINK_ID, OPER_TYPE, DSC_RULE_ID)
    VALUES (null, :agent_id,:dcard ,:summa, :sum_dsc, :doc_id, 1, :doc_id, 5,:dsc_rule_id) ;

  select abs(ad.summa_dsc), ad.dsc_rule_id, ad.id from agents_dsc ad where ad.doc_id=:parent_id and ad.oper_type=2
  and ad.status=1 and g$profile_id = 2 into :sum_dsc, :dsc_rule_id, :part_id;
  if (:sum_dsc is not null and :sum_dsc<>0 and part_id is not null) then
    INSERT INTO AGENTS_DSC (ID, AGENT_ID, DCARD, SUMMA, SUMMA_DSC, DOC_ID, STATUS, LINK_ID, OPER_TYPE, DSC_RULE_ID)
    VALUES (null, :agent_id,:dcard ,0, :sum_dsc, :doc_id, 1, :doc_id, 5,:dsc_rule_id) ;
---2012 11 13 - ann <----
  suspend;
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT ON DOCS TO PROCEDURE PR_MINUS_BONUS;
GRANT SELECT ON AGENTS TO PROCEDURE PR_MINUS_BONUS;
GRANT SELECT,INSERT ON AGENTS_DSC TO PROCEDURE PR_MINUS_BONUS;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_MINUS_BONUS TO SYSDBA;
SET TERM ^ ;

create or alter procedure PR_GETSUM_DSC (
    ACTIVE_DOC_ID DM_ID_NULL)
returns (
    SUMMA DM_DOUBLE,
    SUMMA_DSC DM_DOUBLE,
    DSC_ALL DM_DOUBLE,
    SUMMA_ACTIVE DM_DOUBLE,
    TEK_DSC DM_DOUBLE,
    SUMMA_DSC_USE_ACTIVE DM_DOUBLE,
    LINK_ID DM_ID_NULL,
    AGENT_ID DM_ID_NULL,
    DSC_RULE_ID DM_ID_NULL,
    SAGENT DM_TEXT,
    CALCSUMMA_ACTIVE DM_DOUBLE,
    SUM_DSC_ACTIVE DM_DOUBLE)
as
declare variable LAST_RASHOD_DT DM_DATETIME;
declare variable LAST_DOC_ID DM_ID_NULL;
begin
  select a.id, a.caption from docs d left join agents a on a.id=d.agent_id where d.id=:active_doc_id
  into :agent_id, :sagent;

  select first 1 insertdt,doc_id from agents_dsc ad where agent_id=:agent_id and ad.status=1 and ad.oper_type=2
  into :last_rashod_dt,:last_doc_id;

 --if (last_doc_id is null) then
  begin
    last_doc_id=-1;
    last_rashod_dt=current_timestamp-180;
  end


  select sum(ad.summa), sum(round(ad.summa_dsc))
  from agents_dsc ad
  where   --(ad.doc_id=:last_doc_id and ad.oper_type<>2) or
  agent_id=:agent_id and ad.status=1 and
  ((ad.oper_type in (1,2,3,4,5) and ad.insertdt > :last_rashod_dt) or (ad.doc_id=:last_doc_id and ad.oper_type<>2))
  into :summa, :summa_dsc;

  select sum(abs(round(ad.summa_dsc)))
  from agents_dsc ad
  where agent_id=:agent_id and ad.status=1 and ad.status=0 and ad.oper_type=2
  into :summa_dsc_use_active;

  if (summa_dsc_use_active is null) then
    summa_dsc_use_active=0;

  if (summa is null) then summa=0;
  if (summa_dsc is null) then summa_dsc=0;

  select d2.link_id from docs d2 where d2.id=:active_doc_id into :link_id;

  select abs(sum(da.summa)), abs(sum(da.price*da.quant)),abs(sum(da.sum_dsc)) from doc_detail_active da
  left join docs d on da.doc_id=d.id
  where d.link_id=:link_id
  into :summa_active,:calcsumma_active,:sum_dsc_active;
  if (summa_active is null) then summa_active=0;

  --select round((dr.dsc*:summa_active/100)), id
  select (dr.dsc*:summa_active/100), id
  from dsc_rules dr where dr.summa_beg<=(:summa+:summa_active) and dr.summa_end>(:summa+:summa_active)
  and current_date between dr.datebeg and dr.dateend
  and dr.status<>-1
  into :tek_dsc, :dsc_rule_id;

  if (tek_dsc is null) then tek_dsc=0;
  -- tek_dsc=round(tek_dsc,-2);
  tek_dsc=round(tek_dsc);
  dsc_all=summa_dsc-summa_dsc_use_active;

  suspend;
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT ON DOCS TO PROCEDURE PR_GETSUM_DSC;
GRANT SELECT ON AGENTS TO PROCEDURE PR_GETSUM_DSC;
GRANT SELECT ON AGENTS_DSC TO PROCEDURE PR_GETSUM_DSC;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_GETSUM_DSC;
GRANT SELECT ON DSC_RULES TO PROCEDURE PR_GETSUM_DSC;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GETSUM_DSC TO SYSDBA;
SET TERM ^ ;

create or alter procedure PR_GETSUM_DSC_AGENT_ID (
    AGENT_ID DM_ID)
returns (
    SUMMA_DSC DM_DOUBLE)
as
declare variable LAST_RASHOD_DT DM_DATETIME;
declare variable LAST_DOC_ID DM_ID;
begin
  select first 1 insertdt,doc_id from agents_dsc ad where agent_id=:agent_id and ad.status=1 and ad.oper_type=2
  into :last_rashod_dt,:last_doc_id;

 -- if (last_doc_id is null) then
  begin
    last_doc_id=-1;
    last_rashod_dt=current_timestamp-180;
  end


  select sum(round(ad.summa_dsc))
  from agents_dsc ad
  where   --(ad.doc_id=:last_doc_id and ad.oper_type<>2) or
  agent_id=:agent_id and ad.status=1 and
  ((ad.oper_type in (1,2,3,4,5) and ad.insertdt > :last_rashod_dt) or (ad.doc_id=:last_doc_id and ad.oper_type<>2))
  into :summa_dsc;
  suspend;
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT ON AGENTS_DSC TO PROCEDURE PR_GETSUM_DSC_AGENT_ID;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GETSUM_DSC_AGENT_ID TO SYSDBA;
SET TERM ^ ;

create or alter procedure PR_GETSUM_DSC_KREDIT (
    CARD DM_TEXT,
    ACTIVE_DOC_ID DM_ID_NULL,
    ACTIVE_SUMMA DM_DOUBLE)
returns (
    SUMMA DM_DOUBLE,
    SUMMA_DSC DM_DOUBLE,
    DSC_ALL DM_DOUBLE,
    SUMMA_ACTIVE DM_DOUBLE,
    TEK_DSC DM_DOUBLE,
    SUMMA_DSC_USE_ACTIVE DM_DOUBLE,
    LINK_ID DM_ID_NULL,
    AGENT_ID DM_ID_NULL,
    DSC_RULE_ID DM_ID_NULL)
as
begin
  select first 1 id from agents where dover_face=:card order by id into :agent_id;

  select sum(ad.summa), sum(round(ad.summa_dsc))
  from agents_dsc ad
  where dcard=:card
  and ad.status=1 and ad.oper_type in (1,2,3,4,5) into :summa, :summa_dsc;

  select sum(abs(round(ad.summa_dsc)))
  from agents_dsc ad
  where dcard=:card and ad.status=0 and ad.oper_type=2
  into :summa_dsc_use_active;

  if (summa_dsc_use_active is null) then
    summa_dsc_use_active=0;

  if (summa is null) then summa=0;
  if (summa_dsc is null) then summa_dsc=0;

  select d2.link_id from docs d2 where d2.id=:active_doc_id into :link_id;

  select abs(sum(da.summa)) from doc_detail_active da
  left join docs d on da.doc_id=d.id
  where d.link_id=:link_id
  into :summa_active;
  if (summa_active is null) then summa_active=:active_summa;
  if (summa_active is null) then summa_active=0;

  select round((dr.dsc*:summa_active/100)), id
  from dsc_rules dr where dr.summa_beg<=(:summa+:summa_active) and dr.summa_end>(:summa+:summa_active)
  and current_date between dr.datebeg and dr.dateend
  and dr.status<>-1
  into :tek_dsc, :dsc_rule_id;

  if (tek_dsc is null) then tek_dsc=0;
  tek_dsc=round(tek_dsc,-2);
  dsc_all=summa_dsc-summa_dsc_use_active;

  suspend;
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT ON AGENTS TO PROCEDURE PR_GETSUM_DSC_KREDIT;
GRANT SELECT ON AGENTS_DSC TO PROCEDURE PR_GETSUM_DSC_KREDIT;
GRANT SELECT ON DOCS TO PROCEDURE PR_GETSUM_DSC_KREDIT;
GRANT SELECT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_GETSUM_DSC_KREDIT;
GRANT SELECT ON DSC_RULES TO PROCEDURE PR_GETSUM_DSC_KREDIT;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GETSUM_DSC_KREDIT TO SYSDBA;
/******************************************************************************/
/***               Generated by IBExpert 20.02.2016 11:14:23                ***/
/******************************************************************************/

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



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


CREATE GENERATOR GEN_PREPARE_SALE_ID;

CREATE TABLE PREPARE_SALE (
    ID              DM_ID NOT NULL /* DM_ID = BIGINT */,
    Y               DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    M               DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    D               DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    W               DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    DOC_COMMITDATE  DM_DATE /* DM_DATE = DATE */,
    GROUP_ID        DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    TRADER_ID       DM_UUID_NULL /* DM_UUID_NULL = CHAR(36) */,
    SNAME           DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SIZG            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SCOUNTRY        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    PART_ID         DM_ID /* DM_ID = BIGINT */,
    WARE_ID         DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    NAME_ID         DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    COUNTRY_ID      DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    IZG_ID          DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    REGN            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SERIA           DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SERT            DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    QUANT           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    PRICE           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    PRICE_Z         DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    DOC_ID          DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    AGENT_ID        DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    DOC_TYPE        DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    CONTRACT_ID     DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    SUMM1           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUMM2           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUMM3           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUMMA           DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUMMA_BONUS     DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUMM_DSC        DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    SUMM_RASR       DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    KOEF_TRADER     DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */
);




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

ALTER TABLE PREPARE_SALE ADD CONSTRAINT PK_PREPARE_SALE PRIMARY KEY (ID);


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

CREATE INDEX PREPARE_SALE_IDX1 ON PREPARE_SALE (DOC_COMMITDATE);
CREATE UNIQUE INDEX PREPARE_SALE_IDX2 ON PREPARE_SALE (ID);


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



SET TERM ^ ;



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



/* Trigger: PREPARE_SALE_BI0 */
CREATE OR ALTER TRIGGER PREPARE_SALE_BI0 FOR PREPARE_SALE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.id is null) then
    new.id = gen_id(gen_prepare_sale_id,1);
  select ware_id,regn,seria,sert,price,price_z from parts where id=new.part_id into
  new.ware_id,new.regn,new.seria,new.sert,new.price,new.price_z;
  --select caption from agents a where a.id=new.agent_id into new.sagent;
  new.y = extract (year from new.doc_commitdate);
  new.m = extract (month from new.doc_commitdate);
  new.d = extract (day from new.doc_commitdate);
  new.w = extract (week from new.doc_commitdate);
  select name_id,izg_id,country_id from wares where id=new.ware_id into new.name_id,new.izg_id,new.country_id;
  select first 1 gd.group_id from group_detail gd where gd.grouptable='PARTS' and gd.grouptable_id=new.part_id into new.group_id;
  if (new.group_id is null) then
    select first 1 gd.group_id from group_detail gd where gd.grouptable='PARTS.NAME_ID' and gd.grouptable_id=new.name_id into new.group_id;
  select svalue from vals where id=new.name_id into new.sname;
  select svalue from vals where id=new.izg_id into new.sizg;
  select svalue from vals where id=new.country_id into new.scountry;
end
^

SET TERM ; ^



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/
SET TERM ^ ;

create or alter procedure PR_PREPARE_SALE (
    DATEBEG DM_DATE,
    DATEEND DM_DATE)
returns (
    VERB DM_TEXT)
as
declare variable DOC_ID DM_ID;
declare variable DOC_SUMM1 DM_DOUBLE;
declare variable DOC_SUMM2 DM_DOUBLE;
declare variable DOC_SUMM3 DM_DOUBLE;
declare variable DOC_SUMMA DM_DOUBLE;
declare variable CONTRACT_ID DM_ID;
declare variable AGENT_ID DM_ID;
declare variable DOC_TYPE DM_ID;
declare variable DOC_DETAIL_ID DM_ID;
declare variable DOC_COMMITDATE DM_DATE;
declare variable PART_ID DM_ID;
declare variable QUANT DM_DOUBLE;
declare variable SUMMA DM_DOUBLE;
declare variable DOCTOR_ID DM_UUID_NULL;
declare variable DOCTOR_ID2 DM_UUID_NULL;
declare variable DOCTOR_ID3 DM_UUID_NULL;
declare variable DOCTOR_ID4 DM_UUID_NULL;
declare variable DOCTOR_ID5 DM_UUID_NULL;
declare variable DOCTOR_ID6 DM_UUID_NULL;
declare variable COUNT_PROD DM_STATUS;
declare variable SUM_DSC DM_DOUBLE;
declare variable SUMMA_BONUS DM_DOUBLE;
begin
  delete from prepare_sale where doc_commitdate between :DATEBEG and :DATEEND;
  for select id,summ1,summ2,summ3,summa,contract_id,agent_id,doc_type from docs where-- id=723 and
  commitdate between :datebeg and :dateend+1 and doc_type in (3,9) and status=1 into
  :doc_id,:doc_summ1,:doc_summ2,:doc_summ3,:doc_summa,:contract_id,:agent_id,:doc_type do
  begin
    summa_bonus=0;
    select sum(coalesce(ad.summa,0)) from agents_dsc ad where ad.doc_id=:doc_id and ad.status=1 and ad.oper_type=2 into :summa_bonus;
    for select id,doc_commitdate,part_id,quant,summa,sum_dsc from doc_detail dd where dd.doc_id=:doc_id into
    :DOC_DETAIL_ID,:doc_commitdate,:part_id,:quant,:summa,:sum_dsc  do
    begin
      for select DOCTOR_ID
            from doc_detail_doctor where DOC_DETAIL_ID=:doc_detail_id and doc_id=:doc_id
      into DOCTOR_ID do
      begin
       COUNT_PROD=0;
       if (DOCTOR_ID<>'0' and DOCTOR_ID is not null and DOCTOR_ID<>'') then COUNT_PROD=COUNT_PROD+1;
       --if (DOCTOR_ID2<>'0' and DOCTOR_ID2 is not null and DOCTOR_ID2<>'') then COUNT_PROD=COUNT_PROD+1;
       --if (DOCTOR_ID3<>'0' and DOCTOR_ID3 is not null and DOCTOR_ID3<>'') then COUNT_PROD=COUNT_PROD+1;
       --if (DOCTOR_ID4<>'0' and DOCTOR_ID4 is not null and DOCTOR_ID4<>'') then COUNT_PROD=COUNT_PROD+1;
       --if (DOCTOR_ID5<>'0' and DOCTOR_ID5 is not null and DOCTOR_ID5<>'') then COUNT_PROD=COUNT_PROD+1;
       --if (DOCTOR_ID6<>'0' and DOCTOR_ID6 is not null and DOCTOR_ID6<>'') then COUNT_PROD=COUNT_PROD+1;
      end

      for select DOCTOR_ID from DOC_DETAIL_DOCTOR where
      DOC_DETAIL_ID=:doc_detail_id into :DOCTOR_ID do
      begin
        if (DOCTOR_ID<>'0' and DOCTOR_ID is not null and DOCTOR_ID<>'') then
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
                              (:DOC_COMMITDATE,:DOCTOR_ID,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);

         if (DOCTOR_ID2<>'0' and DOCTOR_ID2 is not null and DOCTOR_ID2<>'') then
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
                              (:DOC_COMMITDATE,:DOCTOR_ID2,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
         if (DOCTOR_ID3<>'0' and DOCTOR_ID3 is not null and DOCTOR_ID3<>'') then
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
                              (:DOC_COMMITDATE,:DOCTOR_ID3,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
         if (DOCTOR_ID4<>'0' and DOCTOR_ID4 is not null and DOCTOR_ID4<>'') then
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
                              (:DOC_COMMITDATE,:DOCTOR_ID4,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
         if (DOCTOR_ID5<>'0' and DOCTOR_ID5 is not null and DOCTOR_ID5<>'') then
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
                              (:DOC_COMMITDATE,:DOCTOR_ID5,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
          if (DOCTOR_ID6<>'0' and DOCTOR_ID6 is not null and DOCTOR_ID6<>'') then
          insert into prepare_sale (DOC_COMMITDATE,TRADER_ID,PART_ID,QUANT,DOC_ID,AGENT_ID,DOC_TYPE,CONTRACT_ID,SUMM1,SUMM2,SUMM3,
                              SUMMA,SUMMA_BONUS,SUMM_DSC,SUMM_RASR,KOEF_TRADER) values
                              (:DOC_COMMITDATE,:DOCTOR_ID6,:PART_ID,:QUANT/:COUNT_PROD,:DOC_ID,:AGENT_ID,:DOC_TYPE,:CONTRACT_ID,
                              :doc_summ1*:summa/(:doc_summa*:COUNT_PROD),:doc_summ2*:summa/(:doc_summa*:COUNT_PROD),:doc_summ3*:summa/(:doc_summa*:COUNT_PROD),
                              :SUMMA/:COUNT_PROD,:SUMMA_BONUS*:summa/(:doc_summa*:COUNT_PROD),:sum_dsc,0,1.0000000/:COUNT_PROD);
      end
    end
  end
  VERB='';
  suspend;
end^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT,INSERT,DELETE ON PREPARE_SALE TO PROCEDURE PR_PREPARE_SALE;
GRANT SELECT ON DOCS TO PROCEDURE PR_PREPARE_SALE;
GRANT SELECT ON AGENTS_DSC TO PROCEDURE PR_PREPARE_SALE;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_PREPARE_SALE;
GRANT SELECT ON DOC_DETAIL_DOCTOR TO PROCEDURE PR_PREPARE_SALE;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_PREPARE_SALE TO SYSDBA;
Источник — «http://wiki.standart-n.ru/index.php?title=Обновление:Бонусы&oldid=3542»