Обновление:Бонусы
Материал из wiki.standart-n.ru
Версия от 17:00, 12 апреля 2016; BeTePoK (обсуждение | вклад)
Содержание
- 1 Установка бонусной системы
- 1.1 Создаём таблицы, представления и процедуры
- 1.2 Исправить ТМСки в кассире
- 1.3 добавить в groups в После выбора агента документа содержимое архива как ТМСку
- 1.4 Изменяем pr_doc_commit
- 1.5 Добавить ТМСку
- 1.6 ПРАВА НАЗНАЧИТЬ ДЛЯ СПРАВОЧНИКОВ
- 1.7 изменить ID ТМСинициализации всправочнике
- 1.8 изменить вьюху VW_AGENTS
- 1.9 в форме списания бонусов - сделать поле редактируемым/нередактируемым по необходимости
- 2 Пример-Шаблон
- 3 Структура базы
- 4 SP$WDICTS
- 5 Скрипты касса:
- 6 Руководство пользователя
Установка бонусной системы
Создаём таблицы, представления и процедуры
/******************************************************************************/
/*** 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);
- значение из архива
ПРАВА НАЗНАЧИТЬ ДЛЯ СПРАВОЧНИКОВ
- иначе в менеджере их не будет видно
изменить 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.
При выборе контрагента на чек откроется окно
На данной форме можно посмотреть информацию
Сумма накопления - сумма всех продаж по этому контрагенту с учетом скидок
Сумма доступных бонусов
Сумма бонусов от текущей покупки
В данном примере стоит ограничение на использование бонусов, не более 25% от суммы чека.
Нажимаем на кнопку "ок".
2.
В чеке использованные бонусы суммируются со скидкой и отображаются в колонке "Изменение в руб."
3.
Для просмотра истории бонусов в программе "Менеджер" на закладке "Бонусы" набираем фамилию контрагента.
В нижней части программы можно увидеть когда, как и на каком основании были начислены или списаны бонусы.
4.
При необходимости бонусы клиентам можно подарить.
Для этого в соответствующем поле вводим сумму и нажимаем кнопку "Подарить бонусы"
