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

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Создаём таблицы, представления и процедуры)
 
(не показано 14 промежуточных версии 4 участников)
Строка 1: Строка 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;
 +
 
 +
/* добавляем справочники - контрагенты-бонусы, правила накопления */
 +
 
 +
/* справочник Бонусы */
 +
 
 +
INSERT INTO SP$WDICTS ( PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS,  INI, BMP, SORTING, FRAMECLASS) VALUES ( 0, 'Бонусы', 'Бонусы', 'AGENTS_DR', 2, 
 +
'[insertsql]
 +
 
 +
[deletesql_selected]
 +
 
 +
[deletesql]
 +
 
 +
[refreshsql]
 +
select * from VW_AGENTS  where id=:id
 +
 
 +
[selectsqlwithdeleted]
 +
select * from vw_AGENTS where mmbsh containing ''покупа'' and DELETEDT is null  order by caption, id
 +
 
 +
[selectsql]
 +
select * from vw_AGENTS where mmbsh containing ''покупа'' and DELETEDT is null  order by caption, id
 +
 
 +
[main]
 +
sourcetablename=AGENTS
 +
returnfieldname=id
 +
captionfieldname=caption
 +
keyfieldname=id
 +
ViewID=AGENTS_DR
 +
RootGroupTableName=
 +
ShowCaption=Дни рождения
 +
GetCaption=Дни рождения
 +
GroupSelect=0
 +
foldergroup=
 +
initfolder_id=
 +
dataset=1
 +
InitTMSGroup_id=836
 +
folders_visible=0
 +
Canfloating=0
 +
hidetoppanel=0
 +
ActivateDictAction=0
 +
 
 +
[cfSelect]
 +
selectfieldexpression=caption
 +
AllwaysPartial=1
 +
 
 +
[form_show]
 +
position=0
 +
left=0
 +
Top=0
 +
Width=600
 +
Height=400
 +
MaxWidth=0
 +
MaxHeight=0
 +
MinWidth=0
 +
MinHeight=0
 +
 
 +
[form_get]
 +
position=0
 +
left=0
 +
Top=0
 +
Width=900
 +
Height=400
 +
MaxWidth=0
 +
MaxHeight=0
 +
MinWidth=0
 +
MinHeight=0
 +
 
 +
[childs]
 +
bottomdock_units=1
 +
bottomdock_size=50
 +
rightdock_units=0
 +
rightdock_size=0
 +
 
 +
[child_0]
 +
caption=Детализация использования бонусов
 +
wdict=AGENTS_DSC
 +
oninit=tek_id=id
 +
afterscroll=tek_id=id
 +
onselectedchange=dcard=DOVER_FACE
 +
efaultdocksite - где будет по-умолчанию: none - не показывается, right - справа, bottom - снизу в виде закладки, custom - просто показывается
 +
defaultdocksite=bottom
 +
ShortCut=
 +
afterupd=tek_id=id
 +
 
 +
[editfields]
 +
num_pssw=default', NULL, NULL, NULL);
 +
 
 +
 
 +
/* справочник Правила накопления */
 +
 
 +
INSERT INTO SP$WDICTS ( PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS,  INI, BMP, SORTING, FRAMECLASS) VALUES ( 0, 'Правила накопления', NULL, 'DSC_RULES', 0, 
 +
'[insertsql]
 +
insert into dsc_rules
 +
(id,caption)
 +
values (:id,:caption)
 +
 
 +
[deletesql_selected]
 +
update dsc_rules set status=-1 where id in (:selected_ids)
 +
 
 +
[deletesql]
 +
update dsc_rules set status=-1 where id=:id
 +
 
 +
[refreshsql]
 +
select * from DSC_RULES where id=:id
 +
 
 +
[selectsqlwithdeleted]
 +
select * from DSC_RULES order by dsc, id
 +
 
 +
[selectsql]
 +
select * from DSC_RULES where status<>-1 order by dsc, id
 +
 
 +
[main]
 +
sourcetablename=DSC_RULES
 +
returnfieldname=id
 +
captionfieldname=caption
 +
keyfieldname=id
 +
ViewID=DSC_RULES
 +
RootGroupTableName=
 +
ShowCaption=Правила накопления
 +
GetCaption=Правила накопления
 +
GroupSelect=0
 +
foldergroup=
 +
initfolder_id=
 +
dataset=1
 +
InitTMSGroup_id=0
 +
folders_visible=0
 +
Canfloating=0
 +
 
 +
[cfSelect]
 +
selectfieldexpression=caption
 +
AllwaysPartial=1
 +
 
 +
[form_show]
 +
position=8
 +
left=0
 +
Top=0
 +
Width=600
 +
Height=400
 +
MaxWidth=0
 +
MaxHeight=0
 +
MinWidth=0
 +
MinHeight=0
 +
 
 +
[form_get]
 +
position=8
 +
left=0
 +
Top=0
 +
Width=900
 +
Height=400
 +
MaxWidth=0
 +
MaxHeight=0
 +
MinWidth=0
 +
MinHeight=0
 +
 
 +
[childs]
 +
bottomdock_units=0
 +
bottomdock_size=0
 +
rightdock_units=0
 +
rightdock_size=0
 +
 
 +
[editfields]
 +
caption=default
 +
summa_beg=default
 +
summa_end=default
 +
dsc=default
 +
datebeg=default
 +
dateend=default
 +
 
 +
[addfields]
 +
id=select gen_id(gen_DSC_RULES_id,1) from rdb$database
 +
caption=default', NULL, NULL, NULL);
 +
 
 +
 
 +
/* справочник Движение бонусов */
 +
 
 +
INSERT INTO SP$WDICTS (PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS,  INI, BMP, SORTING, FRAMECLASS) VALUES ( 0, 'Движение бонусов', NULL, 'AGENTS_DSC', 1, 
 +
'[insertsql]
 +
 
 +
[deletesql_selected]
 +
 
 +
[deletesql]
 +
update agents_dsc set status=-1 where id=:id
 +
 
 +
[refreshsql]
 +
select * from VW_AGENTS_DSC where id=:ID
 +
 
 +
[selectsqlwithdeleted]
 +
select * from VW_AGENTS_DSC where agent_id=:tek_id and STATUS<>1 order by id
 +
 
 +
[selectsql]
 +
select * from VW_AGENTS_DSC where agent_id=:tek_id and STATUS=1 order by id
 +
 
 +
[form_show]
 +
position=8
 +
left=0
 +
Top=0
 +
Width=600
 +
Height=400
 +
MaxWidth=0
 +
MaxHeight=0
 +
MinWidth=0
 +
MinHeight=0
 +
 
 +
[form_get]
 +
position=8
 +
left=0
 +
Top=0
 +
Width=600
 +
Height=400
 +
MaxWidth=0
 +
MaxHeight=0
 +
MinWidth=0
 +
MinHeight=0
 +
 
 +
[main]
 +
sourcetablename=AGENTS_DSC
 +
returnfieldname=ID
 +
captionfieldname=ID
 +
keyfieldname=ID
 +
ViewID=DOC_DETAIL
 +
RootGroupTableName=
 +
ShowCaption=Детализация использования бонусов
 +
GetCaption=Детализация использования бонусов
 +
GroupSelect=0
 +
foldergroup=
 +
initfolder_id=
 +
dataset=1
 +
InitTMSGroup_id=
 +
folders_visible=0
 +
Canfloating=0
 +
hidetoppanel=0
 +
ActivateDictAction=0
 +
 
 +
[cfSelect]
 +
selectfieldexpression=
 +
AllwaysPartial=1
 +
 
 +
[childs]
 +
bottomdock_units=2
 +
bottomdock_size=50
 +
rightdock_units=0
 +
rightdock_size=0', NULL, NULL, NULL);
 +
</PRE>
 +
 
 +
===Исправить ТМСки в кассире===
 +
*после печати чека
 +
<PRE>
 +
uses
 +
  zkassa, fr, chequelist,
 +
  Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, IBDatabase, IBQuery,
 +
  Buttons, ExtCtrls;
 +
 
 +
var
 +
  qWork: TIBQuery;
 +
  i:integer;
 +
begin
 +
  qWork:=tibquery.create(nil);
 +
  qWork.Transaction:=CreateWT(CurrDB);
 +
  qWork.Transaction.StartTransaction;
 +
  qWork.Active:=false;
 +
  qWork.SQL.Text:='update agents_dsc set status=1 where status=0  and doc_id='+
 +
  inttostr(ChequeList.Active.ID);
 +
  qWork.ExecSQL;
 +
 
 +
  qWork.Transaction.Commit;
 +
  qWork.Free;
 +
 
 +
  if FR_IsActive then TagSetContent(fmMain.ewbInfo, 'fr_total', format('?aeia: %d; iae. %.2f; a/i %.2f',[FR_CountCheques, FR_SummCash, FR_SummRegPay1]));
 +
end;
 +
</PRE>
 +
*перед сканированием штрих кода
 +
<PRE>
 +
uses
 +
  StdCtrls, ComCtrls, ExtCtrls, IBQuery, DB, ChequeList, FR,
 +
  ScriptRes, Barcode, ZKassa, StrUtils, Windows, Classes, IBDatabase,
 +
  Classes, Graphics, Controls, Forms, Dialogs;         
 +
       
 +
var             
 +
  mainForm: TForm2;       
 +
  checkQ: TIBQuery;
 +
  agent_id: integer;
 +
begin               
 +
  checkQ := Tibquery.create(nil);
 +
  checkQ.Transaction := CreateRT(CurrDB);
 +
  checkQ.Transaction.starttransaction;
 +
 
 +
  checkQ.Active :=false;
 +
  checkQ.sql.text:='select id, caption from agents where status=0 and dover_face='''+Barcode.Text+'''';
 +
  checkQ.Active:=true;
 +
  if Barcode.Text='' Then exit;
 +
  if ChequeList.Count=0 Then exit;                         
 +
  if checkQ.Eof and checkQ.Bof Then
 +
  begin
 +
    exit;
 +
  end;
 +
  agent_id:=checkQ.FieldByName('id').AsInteger;
 +
  SetDocsAgent(agent_id);
 +
end;
 +
</PRE>
 +
*перед отменой чека
 +
<PRE>
 +
 
 +
uses
 +
  Graphics, Controls, Forms, Dialogs, StdCtrls,
 +
  ComCtrls, ExtCtrls, ibquery, DB, ChequeList, FR,
 +
  ScriptRes, Barcode,ZKassa, StrUtils, Windows, Classes,
 +
  IBDataBase, Buttons;   
 +
       
 +
var
 +
  qWork: TIBQuery;
 +
  i:integer;
 +
begin
 +
  qWork:=tibquery.create(nil);
 +
  qWork.Transaction:=CreateWT(CurrDB);
 +
  qWork.Transaction.StartTransaction;
 +
  for i:=0 to ChequeList.Count-1 do
 +
  begin
 +
    qWork.Active:=false;
 +
    qWork.SQL.Text:='delete from agents_dsc where status=0 and agent_id='+inttostr(ChequeList.Items[i].AgentID)+' and doc_id='+inttostr(ChequeList.Items[i].ID);
 +
    qWork.ExecSQL;                                         
 +
  end;
 +
 
 +
  qWork.Transaction.Commit;
 +
  qWork.Free;
 +
end;
 +
</PRE>
 +
===добавить в groups в После выбора агента документа содержимое архива как ТМСку===
 +
[[Медиа:14_после_выбора_агента_TMS.ZIP]]
 +
===Изменяем pr_doc_commit===
 +
*в pr_doc_commit
 +
перед execute procedure pr_doevent('EV_DOCCOMMIT');
 +
добавить
 +
<PRE>
 +
  -----bonus 20141020        //viv
 +
--  select d.doc_type from docs d where d.id=:doc_id into :doc_type;
 +
  select d.doc_type, d.agent_id from docs d where d.id=:doc_id into :doc_type, :doc_agent;
 +
  if (doc_type=24) then
 +
  update agents_dsc set status=-1 where doc_id=:doc_id and oper_type=1 and status=1;
 +
  if ((doc_type=9) and (:doc_agent<>-1)) then
 +
    execute procedure PR_MINUS_BONUS(:doc_id, :doc_agent);
 +
  -----bonus 20141020
 +
</PRE>
 +
===Добавить ТМСку===
 +
<PRE>
 +
INSERT INTO GROUPS ( PARENT_ID, CAPTION, GROUPTABLE, STATUS,  SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, PACKET, SID) VALUES ( -430, 'Инит бонусы', 'TMS', 0, 0, NULL, -1, NULL, NULL, NULL, 0, 2606, NULL);
 +
</PRE>
 +
*значение из архива
 +
[[Медиа:Инитбонусы.zip]]
 +
===ПРАВА НАЗНАЧИТЬ ДЛЯ  СПРАВОЧНИКОВ===
 +
*иначе в менеджере их не будет видно
 +
===изменить ID ТМСинициализации всправочнике===
 +
===изменить вьюху VW_AGENTS===
 +
добавить
 +
<PRE>
 +
SUMMA_BONUS
 +
</PRE>
 +
<PRE>
 +
(select sum(ad.summa_dsc) from PR_GETSUM_DSC_AGENT_ID(a.id) ad)
 +
</PRE>
 +
===в форме списания бонусов - сделать поле редактируемым/нередактируемым по необходимости===
 +
==Пример-Шаблон==
 
  alecsandr:D:\clients\bolgar\20150518\ZTRADE.FDB
 
  alecsandr:D:\clients\bolgar\20150518\ZTRADE.FDB
 
  На основании данного шаблона нужно перенести необходимые данные.
 
  На основании данного шаблона нужно перенести необходимые данные.
 
  '''Внимание!''' в обновлении участвуют типовые процедуры и скрипты, не "затереть" старый функционал!
 
  '''Внимание!''' в обновлении участвуют типовые процедуры и скрипты, не "затереть" старый функционал!
  
−
=Структура базы=
+
==Структура базы==
 
  AGENTS_DSC
 
  AGENTS_DSC
 
  VW_AGENTS_DSC
 
  VW_AGENTS_DSC
 +
DSC_RULES
 
  PR_CLOSE_BONUS
 
  PR_CLOSE_BONUS
 +
PR_MINUS_BONUS
 
  PR_DOC_COMMIT
 
  PR_DOC_COMMIT
 
  PR_GETSUM_DSC
 
  PR_GETSUM_DSC
 
  PR_GETSUM_DSC_AGENT_ID
 
  PR_GETSUM_DSC_AGENT_ID
 
  PR_GETSUM_DSC_KREDIT
 
  PR_GETSUM_DSC_KREDIT
−
PR_MINUS_BONUS
 
 
  PR_PREPARE_SALE
 
  PR_PREPARE_SALE
−
+
 
−
=SP$WDICTS=
+
==SP$WDICTS==
 
  Перенести из шаблона справочники, где sid:
 
  Перенести из шаблона справочники, где sid:
 
  AGENTS_DR + TMS инициализации
 
  AGENTS_DR + TMS инициализации
 
  AGENTS_DSC
 
  AGENTS_DSC
  
−
=Скрипты касса:=
+
==Скрипты касса:==
 
  Перенести скрипты
 
  Перенести скрипты
 
  -307 После печати чека
 
  -307 После печати чека
Строка 26: Строка 1222:
 
  -315 Перед сканированием штрихкода
 
  -315 Перед сканированием штрихкода
 
  -317 Перед отменой чека
 
  -317 Перед отменой чека
 +
 +
==Руководство пользователя==
 +
1.
 +
При выборе контрагента на чек откроется окно
 +
[[Файл:Начислен бонусов.png]]
 +
На данной форме можно посмотреть информацию
 +
Сумма накопления - сумма всех продаж по этому контрагенту с учетом скидок
 +
Сумма доступных бонусов
 +
Сумма бонусов от текущей покупки
 +
 +
В данном примере стоит ограничение на использование бонусов, не более 25% от суммы чека.
 +
Нажимаем на кнопку "ок".
 +
 +
2.
 +
В чеке использованные бонусы суммируются со скидкой и отображаются в колонке "Изменение в руб."
 +
[[Файл:Скидка бонусами.png]]
 +
 +
3.
 +
Для просмотра истории бонусов в программе "Менеджер" на закладке "Бонусы" набираем фамилию контрагента.
 +
В нижней части программы можно увидеть когда, как и на каком основании были начислены или списаны бонусы.
 +
[[Файл:История бонусов.png]]
 +
 +
4.
 +
При необходимости бонусы клиентам можно подарить.
 +
Для этого в соответствующем поле вводим сумму и нажимаем кнопку "Подарить бонусы"
 +
[[Файл:Подарить бонусы.png]]

Текущая версия на 17:00, 12 апреля 2016

Установка бонусной системы

Создаём таблицы, представления и процедуры

/******************************************************************************/
/***                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;

/* добавляем справочники - контрагенты-бонусы, правила накопления */

/* справочник Бонусы */

INSERT INTO SP$WDICTS ( PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS,  INI, BMP, SORTING, FRAMECLASS) VALUES ( 0, 'Бонусы', 'Бонусы', 'AGENTS_DR', 2,  
'[insertsql]

[deletesql_selected]

[deletesql]

[refreshsql]
select * from VW_AGENTS  where id=:id

[selectsqlwithdeleted]
select * from vw_AGENTS where mmbsh containing ''покупа'' and DELETEDT is null  order by caption, id

[selectsql]
select * from vw_AGENTS where mmbsh containing ''покупа'' and DELETEDT is null  order by caption, id

[main]
sourcetablename=AGENTS
returnfieldname=id
captionfieldname=caption
keyfieldname=id
ViewID=AGENTS_DR
RootGroupTableName=
ShowCaption=Дни рождения
GetCaption=Дни рождения
GroupSelect=0
foldergroup=
initfolder_id=
dataset=1
InitTMSGroup_id=836
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=caption
AllwaysPartial=1

[form_show]
position=0
left=0
Top=0
Width=600
Height=400
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0

[form_get]
position=0
left=0
Top=0
Width=900
Height=400
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0

[childs]
bottomdock_units=1
bottomdock_size=50
rightdock_units=0
rightdock_size=0

[child_0]
caption=Детализация использования бонусов
wdict=AGENTS_DSC
oninit=tek_id=id
afterscroll=tek_id=id
onselectedchange=dcard=DOVER_FACE
efaultdocksite - где будет по-умолчанию: none - не показывается, right - справа, bottom - снизу в виде закладки, custom - просто показывается
defaultdocksite=bottom
ShortCut=
afterupd=tek_id=id

[editfields]
num_pssw=default', NULL, NULL, NULL);


/* справочник Правила накопления */

INSERT INTO SP$WDICTS ( PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS,  INI, BMP, SORTING, FRAMECLASS) VALUES ( 0, 'Правила накопления', NULL, 'DSC_RULES', 0,  
'[insertsql]
insert into dsc_rules
(id,caption)
values (:id,:caption)

[deletesql_selected]
update dsc_rules set status=-1 where id in (:selected_ids)

[deletesql]
update dsc_rules set status=-1 where id=:id

[refreshsql]
select * from DSC_RULES where id=:id

[selectsqlwithdeleted]
select * from DSC_RULES order by dsc, id

[selectsql]
select * from DSC_RULES where status<>-1 order by dsc, id

[main]
sourcetablename=DSC_RULES
returnfieldname=id
captionfieldname=caption
keyfieldname=id
ViewID=DSC_RULES
RootGroupTableName=
ShowCaption=Правила накопления
GetCaption=Правила накопления
GroupSelect=0
foldergroup=
initfolder_id=
dataset=1
InitTMSGroup_id=0
folders_visible=0
Canfloating=0

[cfSelect]
selectfieldexpression=caption
AllwaysPartial=1

[form_show]
position=8
left=0
Top=0
Width=600
Height=400
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0

[form_get]
position=8
left=0
Top=0
Width=900
Height=400
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0

[childs]
bottomdock_units=0
bottomdock_size=0
rightdock_units=0
rightdock_size=0

[editfields]
caption=default
summa_beg=default
summa_end=default
dsc=default
datebeg=default
dateend=default

[addfields]
id=select gen_id(gen_DSC_RULES_id,1) from rdb$database
caption=default', NULL, NULL, NULL);


/* справочник Движение бонусов */

INSERT INTO SP$WDICTS (PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS,  INI, BMP, SORTING, FRAMECLASS) VALUES ( 0, 'Движение бонусов', NULL, 'AGENTS_DSC', 1,  
'[insertsql]

[deletesql_selected]

[deletesql]
update agents_dsc set status=-1 where id=:id

[refreshsql]
select * from VW_AGENTS_DSC where id=:ID

[selectsqlwithdeleted]
select * from VW_AGENTS_DSC where agent_id=:tek_id and STATUS<>1 order by id

[selectsql]
select * from VW_AGENTS_DSC where agent_id=:tek_id and STATUS=1 order by id

[form_show]
position=8
left=0
Top=0
Width=600
Height=400
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0

[form_get]
position=8
left=0
Top=0
Width=600
Height=400
MaxWidth=0
MaxHeight=0
MinWidth=0
MinHeight=0

[main]
sourcetablename=AGENTS_DSC
returnfieldname=ID
captionfieldname=ID
keyfieldname=ID
ViewID=DOC_DETAIL
RootGroupTableName=
ShowCaption=Детализация использования бонусов
GetCaption=Детализация использования бонусов
GroupSelect=0
foldergroup=
initfolder_id=
dataset=1
InitTMSGroup_id=
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=
AllwaysPartial=1

[childs]
bottomdock_units=2
bottomdock_size=50
rightdock_units=0
rightdock_size=0', NULL, NULL, NULL);

Исправить ТМСки в кассире

  • после печати чека 
uses
  zkassa, fr, chequelist,
  Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, IBDatabase, IBQuery,
  Buttons, ExtCtrls;

 var
   qWork: TIBQuery;
   i:integer;
 begin
   qWork:=tibquery.create(nil);
   qWork.Transaction:=CreateWT(CurrDB);
   qWork.Transaction.StartTransaction;
   qWork.Active:=false;
   qWork.SQL.Text:='update agents_dsc set status=1 where status=0  and doc_id='+
   inttostr(ChequeList.Active.ID);
   qWork.ExecSQL;

   qWork.Transaction.Commit;
   qWork.Free;

   if FR_IsActive then TagSetContent(fmMain.ewbInfo, 'fr_total', format('?aeia: %d; iae. %.2f; a/i %.2f',[FR_CountCheques, FR_SummCash, FR_SummRegPay1]));
 end;
  • перед сканированием штрих кода 
uses
  StdCtrls, ComCtrls, ExtCtrls, IBQuery, DB, ChequeList, FR, 
  ScriptRes, Barcode, ZKassa, StrUtils, Windows, Classes, IBDatabase,
  Classes, Graphics, Controls, Forms, Dialogs;          
         
var               
  mainForm: TForm2;        
  checkQ: TIBQuery;
  agent_id: integer;
begin                
   checkQ := Tibquery.create(nil);
   checkQ.Transaction := CreateRT(CurrDB);
   checkQ.Transaction.starttransaction;

   checkQ.Active :=false;
   checkQ.sql.text:='select id, caption from agents where status=0 and dover_face='''+Barcode.Text+'''';
   checkQ.Active:=true;
   if Barcode.Text='' Then exit;
   if ChequeList.Count=0 Then exit;                           
   if checkQ.Eof and checkQ.Bof Then 
   begin 
     exit;
   end;
   agent_id:=checkQ.FieldByName('id').AsInteger;
   SetDocsAgent(agent_id);
end;
  • перед отменой чека 

uses
  Graphics, Controls, Forms, Dialogs, StdCtrls, 
  ComCtrls, ExtCtrls, ibquery, DB, ChequeList, FR, 
  ScriptRes, Barcode,ZKassa, StrUtils, Windows, Classes,
  IBDataBase, Buttons;    
        
 var
   qWork: TIBQuery;
   i:integer;
 begin
   qWork:=tibquery.create(nil);
   qWork.Transaction:=CreateWT(CurrDB);
   qWork.Transaction.StartTransaction;
   for i:=0 to ChequeList.Count-1 do
   begin
     qWork.Active:=false;
     qWork.SQL.Text:='delete from agents_dsc where status=0 and agent_id='+inttostr(ChequeList.Items[i].AgentID)+' and doc_id='+inttostr(ChequeList.Items[i].ID);
     qWork.ExecSQL;                                           
   end;
   
   qWork.Transaction.Commit;
   qWork.Free;
 end;

добавить в groups в После выбора агента документа содержимое архива как ТМСку

Медиа:14_после_выбора_агента_TMS.ZIP

Изменяем pr_doc_commit

  • в pr_doc_commit

перед execute procedure pr_doevent('EV_DOCCOMMIT'); добавить 

  -----bonus 20141020        //viv
--  select d.doc_type from docs d where d.id=:doc_id into :doc_type;
  select d.doc_type, d.agent_id from docs d where d.id=:doc_id into :doc_type, :doc_agent;
  if (doc_type=24) then
   update agents_dsc set status=-1 where doc_id=:doc_id and oper_type=1 and status=1;
  if ((doc_type=9) and (:doc_agent<>-1)) then
    execute procedure PR_MINUS_BONUS(:doc_id, :doc_agent);
  -----bonus 20141020

Добавить ТМСку

INSERT INTO GROUPS ( PARENT_ID, CAPTION, GROUPTABLE, STATUS,  SYSTEMFLAG, DESCRIPTION, IMAGEINDEX, DATA, COLOR, SORTING, BASE_AGENT_ID, PACKET, SID) VALUES ( -430, 'Инит бонусы', 'TMS', 0, 0, NULL, -1, NULL, NULL, NULL, 0, 2606, NULL);
  • значение из архива

Медиа:Инитбонусы.zip

ПРАВА НАЗНАЧИТЬ ДЛЯ СПРАВОЧНИКОВ

  • иначе в менеджере их не будет видно

изменить ID ТМСинициализации всправочнике

изменить вьюху VW_AGENTS

добавить 

SUMMA_BONUS

(select sum(ad.summa_dsc) from PR_GETSUM_DSC_AGENT_ID(a.id) ad)

в форме списания бонусов - сделать поле редактируемым/нередактируемым по необходимости

Пример-Шаблон

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
Источник — «http://wiki.standart-n.ru/index.php?title=Обновление:Бонусы&oldid=3872»