Версия 12:44, 29 августа 2017

Содержание

1 Премии. Вариант 1
2 Премии. Вариант 2

Премии. Вариант 1

Требования

1) Версия Кассира zkassa.exe 2.17.9 от 25.09.2014 или выше;
2) ОС не ниже WinXP Sp3.

Установить ТМС

Создать новую ТМС в Кассире и назвать ее "Бонусы кассирам"
Исходный код данной ТМС:

uses
  Graphics, Controls, Forms, Dialogs, StdCtrls,
  ComCtrls, ExtCtrls, ibquery, DB, ChequeList, FR,
  ScriptRes, Barcode,ZKassa, StrUtils, Windows, Classes,
  IBDataBase, SysUtils;
       
 Procedure BonusRecalc;
 var
  iq: tibquery;
  BONUS_CALCULATE_DAY: string;
  i: integer;
 begin
   Try
   //Создаем соединение с базой
   iq := tibquery.create(nil);
   iq.transaction := CreateRT(CurrDB);
   iq.transaction.starttransaction;
   iq.active := False;

   //Определяем день, с которого считать бонусы
   iq.sql.text := 'select param_value from params where param_id=''BONUS_CALCULATE_DAY''';
   iq.active := True;
   //забираем данные
   if iq.Eof then exit;
   BONUS_CALCULATE_DAY:= Trim(iq.FieldByName('param_value').AsString);

      TagVisible(fmMain.ewbInfo, 'tr_bonus_all', false);
      TagVisible(fmMain.ewbInfo, 't_bonus_all', false);
      TagVisible(fmMain.ewbInfo, 't_bonus_all_cap', false);
      TagVisible(fmMain.ewbInfo, 'tr_bonus_smena', false);
      TagVisible(fmMain.ewbInfo, 't_bonus_smena_cap', false);
      TagVisible(fmMain.ewbInfo, 't_bonus_smena', false);
      TagVisible(fmMain.ewbInfo, 'tr_bonus', false);
      TagVisible(fmMain.ewbInfo, 't_bonus', false);
      TagVisible(fmMain.ewbInfo, 't_bonus_cap', false);

   //Отображать график выручки?
   iq.active := False;
   iq.sql.text := 'select param_value from params where param_id=''CASH_GRAPH_ENABLE''';
   iq.active := True;
   if Not iq.Eof then
   Begin
    if iq.FieldByName('param_value').AsInteger = 1  Then
    Begin
      //Определяем суммарную выручку за месяц по всем кассирам
      iq.active := False;
      iq.sql.text := 'select first 10 * from '+
      '(select u.username, '+
      ' (select coalesce(-1*sum(summa),0) as summa_all from DOCS d Where d.status = 1 and d.commitdate between '''+BONUS_CALCULATE_DAY+''' || ''.'' || iif('+BONUS_CALCULATE_DAY+' > EXTRACT(DAY from CURRENT_DATE), EXTRACT(MONTH from DATEADD(month, -1, CURRENT_DATE)),EXTRACT(MONTH from CURRENT_DATE)) || ''.'' || iif(1 = EXTRACT(MONTH from CURRENT_DATE) and '+BONUS_CALCULATE_DAY+' > EXTRACT(DAY from CURRENT_DATE), EXTRACT(YEAR from DATEADD(YEAR, -1, CURRENT_DATE)),EXTRACT(YEAR from CURRENT_DATE)) || '' 00:00'' and CURRENT_DATE || '' 23:59'' and ' +
      '   ((doc_type = 3 and d.owner = u.id) or (doc_type = 9 and (select d2.owner from docs d2 where d2.id = d.parent_id)=u.id))) as summa_all '+
      ' from users u '+
      ' where u.status = 0 and exists(select * from GROUP_DETAIL where GROUPTABLE_ID = u.id and GROUPTABLE = ''USERS'' and GROUP_ID = -1) ) as data '+
      ' order by summa_all desc';
      iq.active := True;
      i:=0;
      While not(iq.Eof) do
      begin
        i:=i+1;
        TagSetContent(fmMain.ewbInfo, 't_kassir_caption'+IntToStr(i), iq.FieldByName('username').AsString);
        TagSetContent(fmMain.ewbInfo, 't_kassir_bonus'+IntToStr(i), StringReplace(iq.FieldByName('summa_all').AsString, '.', ',', 1) );
//           showmessage(iq.FieldByName('username').AsString + ':' +iq.FieldByName('summa_all').AsString);
        iq.Next;
      end
      TagSetContent(fmMain.ewbInfo, 't_bonus', '0' );
      TagSetContent(fmMain.ewbInfo, 't_bonus_smena', '0' );
      TagSetContent(fmMain.ewbInfo, 't_bonus_all', '0' );
    end;

   //Отображать бонусы?
   iq.active := False;
   iq.sql.text := 'select param_value from params where param_id=''BONUS_ENABLE''';
   iq.active := True;
   if Not iq.Eof then
   Begin
    if iq.FieldByName('param_value').AsInteger = 1  Then
    Begin
      TagVisible(fmMain.ewbInfo, 'tr_bonus_all', True);
      TagVisible(fmMain.ewbInfo, 't_bonus_all', True);
      TagVisible(fmMain.ewbInfo, 't_bonus_all_cap', True);
      TagVisible(fmMain.ewbInfo, 'tr_bonus_smena', True);
      TagVisible(fmMain.ewbInfo, 't_bonus_smena_cap', True);
      TagVisible(fmMain.ewbInfo, 't_bonus_smena', True);
      TagVisible(fmMain.ewbInfo, 'tr_bonus', True);
      TagVisible(fmMain.ewbInfo, 't_bonus', True);
      TagVisible(fmMain.ewbInfo, 't_bonus_cap', True);

      //Определяем бонус за последний чек
      iq.active := False;
      iq.sql.text := 'select Round(coalesce(sum(bonus),0),2) as sum_bonus from DOCS d where d.id = '+
                     '(select max(id) from DOCS d2 where status = 1 and ( (doc_type = 3 and owner = ' + inttostr(userid) + ') or (doc_type = 9 and (select owner from docs d3 where d3.id = d2.parent_id) = ' +  inttostr(userid) + ') ))'
      iq.active := True;
      TagSetContent(fmMain.ewbInfo, 't_bonus', StringReplace(iq.FieldByName('sum_bonus').AsString, '.', ',', 1) );
      //Определяем суммарный бонус за смену
      iq.active := False;
      iq.sql.text := 'select Round(coalesce(sum(bonus),0),2) as sum_bonus from DOCS d where status = 1 and' +
      '((doc_type = 3 and d.owner = ' + inttostr(userid) + ') or (doc_type = 9 and (select owner from docs d2 where d2.id = d.parent_id) = ' + inttostr(userid) + ')) and ' +
      'd.commitdate between CURRENT_DATE || '' 00:00'' and CURRENT_DATE || '' 23:59''';
      iq.active := True;
      TagSetContent(fmMain.ewbInfo, 't_bonus_smena', StringReplace(iq.FieldByName('sum_bonus').AsString, '.', ',', 1) );
      //Определяем суммарный бонус за месяц
      iq.active := False;
      iq.sql.text := 'select Round(coalesce(sum(bonus),0),2) as sum_bonus from DOCS d where status = 1 and' +
      '((doc_type = 3 and d.owner = ' + inttostr(userid) + ') or (doc_type = 9 and (select owner from docs d2 where d2.id = d.parent_id) = ' + inttostr(userid) + ')) and ' +
      'd.commitdate between '''+BONUS_CALCULATE_DAY+''' || ''.'' || iif('+BONUS_CALCULATE_DAY+' > EXTRACT(DAY from CURRENT_DATE), EXTRACT(MONTH from DATEADD(month, -1, CURRENT_DATE)),EXTRACT(MONTH from CURRENT_DATE)) || ''.'' || iif(1 = EXTRACT(MONTH from CURRENT_DATE) and '+BONUS_CALCULATE_DAY+' > EXTRACT(DAY from CURRENT_DATE), EXTRACT(YEAR from DATEADD(YEAR, -1, CURRENT_DATE)),EXTRACT(YEAR from CURRENT_DATE)) || '' 00:00''' +
      ' and CURRENT_DATE || '' 23:59''';
      iq.active := True;
      TagSetContent(fmMain.ewbInfo, 't_bonus_all', StringReplace(iq.FieldByName('sum_bonus').AsString, '.', ',', 1) );
    end;
   end;

  end;

  finally 
   iq.active := False;
   iq.transaction.free;
   iq.free;
  end;

 end;

Включить вызов ТМС

Включить вызов ТМС из п.1 в "После печати очереди чеков" (а если позволяет производительность системы, то и в "После смены позиции", для более оперативного обновления данных), p84u1 заменить на соответствующий из п.1.
Затем Перекомпилировать скрипты.

 uses
  Graphics, Controls, Forms, Dialogs, StdCtrls,
  ComCtrls, ExtCtrls, ibquery, DB, ChequeList, FR,
  ScriptRes, Barcode,ZKassa, StrUtils, Windows, Classes,
  IBDataBase, SysUtils, p84u1;

 begin
   BonusRecalc;
 end;

Отображение кол-ва бонус в окне информации о чеке

В кассире в правом окне сумм итогов чека заменить код на:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1251" />
<meta charset="utf-8">
<script type="text/javascript" language="JavaScript">
    function _show(id) {var el=document.getElementById(id); if (el!=undefined) {el.className=el.className.replace(/(?:^|\b)hide(?!\S)/g, "");}}
    function _hide(id) {var el=document.getElementById(id); _show(id); if (el!=undefined) {el.className+=" hide";}}
    function _setContent(id, content) {
      var el=document.getElementById(id); if (el!=undefined) {el.innerHTML=content;}                       
      if (id=='t_bonus_all') {
        drawChart(); 
      }
    }
</script>                                        
  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
  <script type="text/javascript" language="JavaScript">                                   
   google.charts.load('current', {packages: ['corechart', 'bar']});
   google.setOnLoadCallback(function(){                                     
     setTimeout(function() { drawChart(); }, 100);
     /*drawChart();*/
   });                                                                                                       
   function drawChart() {
   var ms = [];
   var rec_count = 0;
   ms.push(['№', 'руб.', { role: 'annotation' }]);
     
   for (var i=1; i<11; i++) {
     if (parseFloat(document.getElementById("t_kassir_bonus" + i).innerHTML) > 0) {
       rec_count = rec_count+1;
       ms.push(['№ '+i, parseFloat(document.getElementById("t_kassir_bonus" + i).innerHTML), document.getElementById("t_kassir_caption" + i).innerHTML]);
     }
   }
 
   if (rec_count > 0) {
      var data = google.visualization.arrayToDataTable(ms);
      var options = {
        bar: {groupWidth: "95%"},
        annotations: {
         textStyle: {
          fontSize: 12,
          bold: true }
         }    
      
        };
      var chart = new google.visualization.BarChart(document.getElementById('bonus'));
       chart.draw(data, options); 
    }
             
   }     
  </script>
   
</head>
<style type="text/css"> 
    * {margin:0; padding:0; border:0; color:#626262; text-decoration:none; font-family:Arial, Helvetica, sans-serif; font-style:normal; font-weight:bold; font-size:12px; border-collapse:collapse; overflow:hidden;}
    html {overflow:hidden;}
    .size2 {font-size:14px;} .size3 {font-size:20px;} .size4 {font-size:50px;}
    .ac {text-align:center;} .ar {text-align:right;}
    .pa {position:absolute; left:0; width:100%;} .pr {position:relative; left:0; width:100%;}
    .hide {display:none;} .mark {color:#CC4139;}
    .total1 {font-family:"Courier New", Courier, monospace; color:#FFF;} .total2 {font-family:Impact;}
    .red1 {filter:progid:DXImageTransform.Microsoft.gradient(startColorstr="#FEEDCD", endColorstr="#FED78D");}
    .red2 {filter:progid:DXImageTransform.Microsoft.gradient(startColorstr="#FDC966", endColorstr="#FCA80C");}
    .gray1 {filter:progid:DXImageTransform.Microsoft.gradient(startColorstr="#c8c8c8", endColorstr="#b0b0b0");}
    .gray2 {filter:progid:DXImageTransform.Microsoft.gradient(startColorstr="#FFFFFF", endColorstr="#EAEAEA");}
    .gray3 {filter:progid:DXImageTransform.Microsoft.gradient(startColorstr="#E4E4E4", endColorstr="#B8B8B8");}
    .dlm {width:4px;}
</style>                             
<body>
    <div id="fr_content" class="pr" style="height:54px;">
        <div class="pa red1" style="top:0; height:50%;"></div>
        <div class="pa red2" style="top:50%; height:50%;"></div>
        <div class="pa ac" style="top:0; height:54px;">
            <div id="fr_model" class="pr" style="height:33%; font-weight:normal;">ККМ не инициализирована</div>
            <div id="fr_sess" class="pr" style="height:33%; font-weight:normal;">Смена: 0 (0/0), до конца 24 часа</div>
            <div id="fr_total" class="pr mark" style="height:33%;">Чеков: 0, сумма д/я = 0.00</div>
        </div>
    </div>
    <div class="pr" style="height:6px;"></div>
    <table id="tbl_info" style="width:100%;">
            <tr id="tr_pos"><td class="dlm" /><td id="t_pos_cap" class="size2">Позиций</td><td id="t_pos" class="ar size3">0</td><td class="dlm" /></tr>
        <tr id="tr_sum"><td class="dlm" /><td id="t_sum_cap" class="size2">Сумма</td><td id="t_sum" class="ar size3">0.00</td><td class="dlm" /></tr>
        <tr id="tr_dsc"><td class="dlm" /><td id="t_dsc_cap" class="size2">Скидка</td><td id="t_dsc" class="ar size3">0.00</td><td class="dlm" /></tr>
        <tr id="tr_cash"><td class="dlm" /><td id="t_cash_cap" class="mark size2">Наличные</td><td id="t_cash" class="ar mark size3">0.00</td><td class="dlm" /></tr>
        <tr id="tr_del"><td class="dlm" /><td id="t_del_cap" class="mark size2">Сдача</td><td id="t_del" class="ar mark size3">0.00</td><td class="dlm" /></tr>
        <tr id="tr_bonus"><td class="dlm" /><td id="t_bonus_cap" class="size1">Премия за посл. чек</td><td id="t_bonus" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr id="tr_bonus_smena"><td class="dlm" /><td id="t_bonus_smena_cap" class="size1">Премия за день</td><td id="t_bonus_smena" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr id="tr_bonus_all"><td class="dlm" /><td id="t_bonus_all_cap" class="size1">Премия за мес.</td><td id="t_bonus_all" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr style = "display:none" id="tr_kassir_bonus1"><td class="dlm" /><td id="t_kassir_caption1" class="size1">????</td><td id="t_kassir_bonus1" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr style = "display:none" id="tr_kassir_bonus2"><td class="dlm" /><td id="t_kassir_caption2" class="size1">????</td><td id="t_kassir_bonus2" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr style = "display:none" id="tr_kassir_bonus3"><td class="dlm" /><td id="t_kassir_caption3" class="size1">????</td><td id="t_kassir_bonus3" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr style = "display:none" id="tr_kassir_bonus4"><td class="dlm" /><td id="t_kassir_caption4" class="size1">????</td><td id="t_kassir_bonus4" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr style = "display:none" id="tr_kassir_bonus5"><td class="dlm" /><td id="t_kassir_caption5" class="size1">????</td><td id="t_kassir_bonus5" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr style = "display:none" id="tr_kassir_bonus6"><td class="dlm" /><td id="t_kassir_caption6" class="size1">????</td><td id="t_kassir_bonus6" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr style = "display:none" id="tr_kassir_bonus7"><td class="dlm" /><td id="t_kassir_caption7" class="size1">????</td><td id="t_kassir_bonus7" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr style = "display:none" id="tr_kassir_bonus8"><td class="dlm" /><td id="t_kassir_caption8" class="size1">????</td><td id="t_kassir_bonus8" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr style = "display:none" id="tr_kassir_bonus9"><td class="dlm" /><td id="t_kassir_caption9" class="size1">????</td><td id="t_kassir_bonus9" class="ar size1">0.00</td><td class="dlm" /></tr>
        <tr style = "display:none" id="tr_kassir_bonus10"><td class="dlm" /><td id="t_kassir_caption10" class="size1">????</td><td id="t_kassir_bonus10" class="ar size1">0.00</td><td class="dlm" /></tr>

    </table>                              
    <div id="bonus" style="bottom:0; width: 100%; height: 150px;"></div>                                   
    <div id="ftr_gray1" class="pa ac gray1 total1 size3" style="bottom:60px; height:22px;">ИТОГО</div>
    <div id="ftr_gray2" class="pa gray2" style="bottom:30px; height:30px;"></div><div id="ftr_gray3" class="pa gray3" style="bottom:0; height:30px;"></div>
    <div id="ftr_total" class="pa ac total2 mark size4" style="bottom:0; height:60px;">0.00</div>
</body>
</html>

Скопировать настройки для всех пользователей

Расчет премии

Кассир -> настройки -> администратор -> бонусы -> добавить "Премия" с текстом:

 uses zkassa;
 begin
  result:= ::summa*-0.04; //Премия 4% от суммы чека
 end;

или

 result:= (::summa - ::summa_o)*-0.2; //Премия 20% от прибыли

или

 uses Zkassa;
 begin                                
  Chequelist.Active.FillGroups();
  //Премия 10% от продажи товаров из группы Акция
  If (::doc_type = 3) Then
    result:=Chequelist.Active.GroupSumm(960)*0.02
  Else
    result:=-1*Chequelist.Active.GroupSumm(960)*0.02;

 end;

Настройка с какого дня расчитывать премию/выручку

Выполнить запрос, который добавляет новый параметр в менеджер "С какого дня месяца рассчитывать премию кассирам?"

 INSERT INTO PARAMS (ID, PARENT_ID, PARAM_ID, PARAM_CAPTION, PARAM_TYPE, PARAM_VALUE, AUDIT_ID, IMAGEINDEX, STATUS, INSERTDT, SORTING, PARAM_TYPE_DATA, PACKET) 
 VALUES ((SELECT MAX(ID)+1 FROM PARAMS),-30, 'BONUS_CALCULATE_DAY', 'С какого дня  месяца рассчитывать премию/выручку кассирам? (от 1 до 28)', 2, '1', 5082, 0, 0, '30-JUL-2014 08:34:35.812', 4, NULL, 16685);

Параметры для отображения премии и графика выручки, по умолчанию все выключено

INSERT INTO PARAMS (ID, PARENT_ID, PARAM_ID, PARAM_CAPTION, PARAM_TYPE, PARAM_VALUE, AUDIT_ID, IMAGEINDEX, STATUS, INSERTDT, SORTING, PARAM_TYPE_DATA, PACKET) VALUES 
((SELECT MAX(ID)+1 FROM PARAMS), -30, 'BONUS_ENABLE', 'Отображать премии(бонусы) на кассах?', 14, '1', 633, 0, 0, '16-DEC-2014 17:19:39.964', 46, 'FIXEDITEMS SHOWONLYVALUES RETURNNAME
0=Нет
1=Да', 26225);

INSERT INTO PARAMS (ID, PARENT_ID, PARAM_ID, PARAM_CAPTION, PARAM_TYPE, PARAM_VALUE, AUDIT_ID, IMAGEINDEX, STATUS, INSERTDT, SORTING, PARAM_TYPE_DATA, PACKET) VALUES 
((SELECT MAX(ID)+1 FROM PARAMS), -30, 'CASH_GRAPH_ENABLE', 'Отображать график выручки на кассах? (треб. перезапуск касс)', 14, '0', 633, 0, 0, '16-DEC-2014 17:19:39.964', 46, 'FIXEDITEMS SHOWONLYVALUES RETURNNAME
0=Нет
1=Да', 26225);

Добавить отчет по премиям

В папку с формами скопировать bonus.cffr3 и добавить его спомощью sql-запроса:

 INSERT INTO REPORTS (PARENT_ID, STATUS, REPORTTYPE, SORTING, CAPTION, PARAMS, DATA, WDICT_ID) 
 VALUES (0, 0, 0, 12, 'Премии кассирам', '[freereport] tmplpath=bonus.cffr3', NULL, NULL);

Как себя проверить

Проверить все можно отбив любой чек на кассе, при этом должен рассчитаться бонус(премия), выручка в АРМ Кассира и в отчете "Премии кассирам" в АРМ Менеджера.

Полезные ссылки: https://google-developers.appspot.com/chart/interactive/docs/gallery/barchart

Теги: премии, бонусы кассирам, кассиры, продавцы, з/п, зарплата, заработная плата, материальное стимулирование, рублемер

Премии. Вариант 2

Работают частично поверх версии 1 т.е. вначале установить версию 1 - потом доделать под версию 2 Без использования премий в кассире, есть возможность назначить двух продавцов на 1 день с заранее заданными процентами, или указать уже на прошедший день с указанием процентов, + редактировать проценты от продаж на уже прошедшие смены

Создать таблицу соответствия - продавец - процент продаж

/******************************************************************************/
/***               Generated by IBExpert 22.01.2016 13:58:45                ***/
/******************************************************************************/

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

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

CREATE GENERATOR GEN_BONUSES_ID;

CREATE TABLE BONUSES (
    ID             DM_ID NOT NULL /* DM_ID = BIGINT */,
    USER_ID        DM_ID /* DM_ID = BIGINT */,
    BONUS_PERCENT  DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */
);

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

ALTER TABLE BONUSES ADD CONSTRAINT PK_BONUSES PRIMARY KEY (ID);


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

SET TERM ^ ;

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

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

SET TERM ; ^

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

Изменить триггер - перед добавлением или после добавления пользователя - USERS_BI, добавить строку

insert into bonuses (user_id,BONUS_PERCENT) values (new.id,0);

создать таблицу - журнал смен

/******************************************************************************/
/***               Generated by IBExpert 25.01.2016 14:55:48                ***/
/******************************************************************************/

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

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

CREATE GENERATOR GEN_AGENTS_BONUS_ID;

CREATE TABLE AGENTS_BONUS (
    ID             DM_ID NOT NULL /* DM_ID = BIGINT */,
    USER1_ID       DM_ID /* DM_ID = BIGINT */,
    USER2_ID       DM_ID /* DM_ID = BIGINT */,
    DATE_TEAMWORK  DM_DATE /* DM_DATE = DATE */,
    USER1_PERC     DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */,
    USER2_PERC     DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */
);

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

ALTER TABLE AGENTS_BONUS ADD CONSTRAINT PK_AGENTS_BONUS PRIMARY KEY (ID);

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

SET TERM ^ ;

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

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

SET TERM ; ^

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

создать представление - продавцы-процентыпродаж

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

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

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

/* View: VW_PREMI */
CREATE OR ALTER VIEW VW_PREMI(
    USERNAME,
    ID,
    BONUS_PERCENT)
AS
select u.username,u.id,b.bonus_percent from users u
left join bonuses b on u.id=b.user_id
where u.status <> -1
;

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

SET TERM ^ ;

/******************************************************************************/
/***                      Triggers for updatable views                      ***/
/******************************************************************************/

/* Trigger: VW_PREMI_BU0 */
CREATE OR ALTER TRIGGER VW_PREMI_BU0 FOR VW_PREMI
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
  POST_EVENT 'DUMMY_EVENT';
END
^

/* Trigger: VW_PREMI_BU0 */
CREATE OR ALTER TRIGGER VW_PREMI_BU0 FOR VW_PREMI
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.bonus_percent<>old.bonus_percent) then
    update bonuses b set b.bonus_percent= new.bonus_percent where b.user_id=new.id;
end
^

SET TERM ; ^

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

создать представление - журнал смен

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

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

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

/* View: VW_AGENTS_BONUS */
CREATE OR ALTER VIEW VW_AGENTS_BONUS(
    DOC_ID,
    USER_ID,
    SUMMA,
    SUM_BONUS)
AS
select aaa.DOC_ID,aaa.user_id,aaa.summa,coalesce(SUM(aaa.bonus),0) as bonuses
from
(
select d.id as DOC_ID,d.summa as summa,ab.user1_id as user_id,
(abs(d.summa))*(ab.user1_perc/100) as bonus,d.doc_type,d.status
from docs d
left join agents_bonus ab on (ab.user1_id = d.owner) and (ab.date_teamwork = cast(d.commitdate as date))
where ab.user1_id is not null

union distinct

select d.id as DOC_ID,d.summa as summa,ab.user2_id as user_id,
(abs(d.summa))*(ab.user2_perc/100) as bonus,d.doc_type,d.status
from docs d
left join agents_bonus ab on ((ab.user1_id = d.owner) or (ab.user2_id = d.owner)) and (ab.date_teamwork = cast(d.commitdate as date)) and (ab.user1_id <> ab.user2_id)
where (ab.user2_id is not null) and ab.user1_id is not null

) aaa
group by aaa.DOC_ID,aaa.user_id,aaa.summa
order by aaa.DOC_ID
;


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

Добавить и заполнить справочники

INSERT INTO SP$WDICTS (ID, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INSERTDT, INI, BMP, SORTING, FRAMECLASS) VALUES (34, 0, 'Смены работников', 'Смены работников', 'smeni', 0, '21-JAN-2016 10:24:20.759', NULL, NULL, 1, NULL);

[insertsql]

[deletesql_selected]

[deletesql]

[refreshsql]
select first 100 ab.ID,
ab.user1_id,(select username from users u where ab.user1_id=u.id) as username1,
ab.user2_id,(select username from users u where ab.user2_id=u.id) as username2,
ab.date_teamwork,ab.user1_perc,ab.user2_perc
from AGENTS_BONUS ab
where id=:ID
order by DATE_TEAMWORK desc

[selectsqlwithdeleted]

[selectsql]
select first 100 ab.ID,
ab.user1_id,(select username from users u where ab.user1_id=u.id) as username1,
ab.user2_id,(select username from users u where ab.user2_id=u.id) as username2,
ab.date_teamwork,ab.user1_perc,ab.user2_perc
from AGENTS_BONUS ab
order by DATE_TEAMWORK desc

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

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

[main]
sourcetablename=AGENTS_BONUS
returnfieldname=ID
captionfieldname=DATE_TEAMWORK
keyfieldname=ID
ViewID=VW_AGENTS_BONUS
RootGroupTableName=
ShowCaption=Журнал смен
GetCaption=Журнал смен
GroupSelect=0
foldergroup=
initfolder_id=
dataset=1
InitTMSGroup_id=0
folders_visible=0
Canfloating=1
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=DATE_TEAMWORK
AllwaysPartial=1

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

[editfields]
USER2_ID=WDICTS.USERS
username2=USER2_ID
DATE_TEAMWORK=default
USER1_PERC=default
USER2_PERC=default

[addfields]
id=select gen_id(gen_agents_bonus_id,1) from rdb$database
DATE_TEAMWORK=default

INSERT INTO SP$WDICTS (ID, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INSERTDT, INI, BMP, SORTING, FRAMECLASS) VALUES (35, 0, 'Пользователи просто', 'Пользователи просто', 'USERS', 1, '21-JAN-2016 10:36:31.930', NULL, NULL, NULL, NULL);

[insertsql]

[deletesql_selected]

[deletesql]

[refreshsql]
Select
*
from users
where
ID=:ID

[selectsqlwithdeleted]
select * from users order by username,id

[selectsql]
select * from users where status=0 order by username,id

[updatesql]

[cfSelect]
selectfieldexpression=username
AllwaysPartial=1

[main]
showintheweb=true
returnfieldname=id
captionfieldname=username
ViewID=USERS
RootGroupTableName=
ShowCaption=Пользователи
GetCaption=Выберите пользователя
GroupSelect=0
InitGroup_id=
sourcetablename=USERS
keyfieldname=id
foldergroup=
initfolder_id=
dataset=0
InitTMSGroup_id=290
folders_visible=0
Canfloating=0
hidetoppanel=1
ActivateDictAction=0

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

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

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

[editfields]
FIO=Default

INSERT INTO SP$WDICTS (ID, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INSERTDT, INI, BMP, SORTING, FRAMECLASS) VALUES (36, 0, 'Премии кассирам', 'Премии кассирам', 'premi', 0, '22-JAN-2016 09:46:25.380', NULL, NULL, 1, NULL);

[insertsql]

[deletesql_selected]

[deletesql]

[refreshsql]
select * from VW_PREMI
where ID=:ID

[selectsqlwithdeleted]
select * from VW_PREMI
order by username,id

[selectsql]
select * from VW_PREMI
order by username,id

[updatesql]

[cfSelect]
selectfieldexpression=username
AllwaysPartial=1

[main]
showintheweb=true
returnfieldname=ID
captionfieldname=USERNAME
ViewID=VW_PREMI
RootGroupTableName=
ShowCaption=Пользователи
GetCaption=Выберите пользователя
GroupSelect=0
InitGroup_id=
sourcetablename=VW_PREMI
keyfieldname=id
foldergroup=
initfolder_id=
dataset=0
InitTMSGroup_id=290
folders_visible=0
Canfloating=0
hidetoppanel=1
ActivateDictAction=0

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

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

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

[editfields]
bonus_percent=default

перенести уже существующих продавцов в таблицу соответствия процентам

insert into bonuses (user_id, bonus_percent)
select id,0 from users where status <> -1

Добавить в PR_DOC_COMMIT учёт смен

if ((doc_type=3) or (doc_type=9))  then begin
    if ((select count(*) from agents_bonus where DATE_TEAMWORK = current_date)=0) then begin
        insert into agents_bonus (USER1_ID,DATE_TEAMWORK,USER1_PERC) values ((select d.owner from docs d where d.id=:doc_id),current_date,(select BONUS_PERCENT from bonuses where user_id=(select d.owner from docs d where d.id=:doc_id)));
    end
    else begin
        if (((select user1_id from agents_bonus where DATE_TEAMWORK = current_date )<>(select d.owner from docs d where d.id=:doc_id)) and
        ((select user2_id from agents_bonus where DATE_TEAMWORK = current_date)is null)) then begin
            update  agents_bonus  set user2_id = (select d.owner from docs d where d.id=:doc_id),USER2_PERC = (select BONUS_PERCENT from bonuses where user_id=(select d.owner from docs d where d.id=:doc_id))   where DATE_TEAMWORK = current_date;
        end
    end
end

изменить отображение бонусов в кассире

   //Определяем бонус за последний чек
   iq.active := False;
   iq.sql.text :='select ab.SUM_BONUS ' +
                  'from vw_agents_bonus ab  ' +
                  'where ab.doc_id =(select max(id) from DOCS d2 where status = 1 and ( (doc_type = 3 and owner = ' + inttostr(userid) + ') or (doc_type = 9 and (select owner from docs d3 where d3.id = d2.parent_id) = ' + inttostr(userid) + ') )) ' +
                  'and ab.user_id = ' + inttostr(userid) + ';';

   //Определяем суммарный бонус за смену
   iq.active := False;
   iq.sql.text := 'select Round(coalesce(sum(ab.SUM_BONUS),0),2) as sum_bonus ' +
                  'from vw_agents_bonus ab ' +
                  'left join docs d on ab.doc_id = d.id ' +
                  'where d.status = 1 and ' +
                  'd.commitdate between CURRENT_DATE || '' 00:00'' and CURRENT_DATE || '' 23:59''' +
                  'and ab.user_id = ' + inttostr(userid) + ' ;';

   //Определяем суммарный бонус за месяц
   iq.active := False;
   iq.sql.text := 'select Round(coalesce(sum(ab.SUM_BONUS),0),2) as sum_bonus ' +
                  'from vw_agents_bonus ab ' +
                  'left join docs d on ab.doc_id = d.id ' +
                  'where d.status = 1 and ' +
                  'd.commitdate between '''+BONUS_CALCULATE_DAY+''' || ''.'' || iif('+BONUS_CALCULATE_DAY+' > EXTRACT(DAY from CURRENT_DATE), EXTRACT(MONTH from DATEADD(month, -1, CURRENT_DATE)),EXTRACT(MONTH from CURRENT_DATE)) || ''.'' || iif(1 > EXTRACT(DAY from CURRENT_DATE), EXTRACT(YEAR from DATEADD(MONTH, -1, CURRENT_DATE)),EXTRACT(YEAR from CURRENT_DATE)) || '' 00:00'' ' +
                  'and CURRENT_DATE || '' 23:59'' ' +
                  'and ab.user_id = ' + inttostr(userid) + ';';

отчёт + отчёт детальный по премиям по 2 варианту

Медиа:Bonus.zip

Установка бонусов кассирам — различия между версиями