Подарочные карты 2го типа — различия между версиями

Версия 17:44, 28 февраля 2017

Устанока-Настройка Подарочных карт 2го типа

выполняем скрипт

SET SQL DIALECT 3;

CREATE GENERATOR GEN_GIFTCARD_DETAIL_ID;

CREATE TABLE GIFTCARD_DETAIL (
    S_ID        DM_ID /* DM_ID = BIGINT */,
    S_DATE      DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    BARCODE     DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    OPER_TYPE   DM_STATUS NOT NULL /* DM_STATUS = INTEGER */,
    DOC_ID      DM_ID /* DM_ID = BIGINT */,
    D$UUID      DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    D$SRVUPDDT  DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);

CREATE INDEX GIFTCARD_DETAIL_IDX1 ON GIFTCARD_DETAIL (BARCODE);


SET TERM ^ ;

CREATE OR ALTER TRIGGER GIFTCARD_DETAIL_BI FOR GIFTCARD_DETAIL
ACTIVE BEFORE INSERT POSITION 0
as
declare variable COMMITSESSION_ID type of dm_id;
declare variable agent_id type of dm_id;
declare variable DOC_ID type of dm_id;
declare variable PART_ID type of dm_id;
declare variable SNAME type of dm_text;
declare variable PRICE type of dm_double;
declare variable ID type of dm_ID;
begin
  if (new.s_id is null) then
    new.s_id = gen_id(gen_giftcard_detail_id,1);

  if (new.s_date is null) then
    new.s_date = 'Now';

  if (new.oper_type = 1) then
  begin
    select d.COMMITSESSION_ID,d.agent_id from docs d where d.id = new.doc_id into :COMMITSESSION_ID, :agent_id ;
    select DOC_ID from pr_newdoc(20,:agent_id,0,null,null,:COMMITSESSION_ID) into DOC_ID;
    PART_ID = (select first 1 p.id from parts p where p.barcode = new.barcode or (p.barcode1 = new.barcode) );
    select first 1 wb.sname,wb.price from warebase wb where wb.part_id = PART_ID into :SNAME, :PRICE;
    select ID from PR_PRIHODPART(:DOC_ID,:PART_ID,1,:SNAME,:PRICE,0,null,0,0,null) into :id;
    execute procedure pr_doc_commit(DOC_ID,COMMITSESSION_ID);
  end
end
^

CREATE OR ALTER TRIGGER GIFTCARD_DETAIL_BI_DISTR FOR GIFTCARD_DETAIL
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
    new.d$srvupddt='2000-01-01';
  end
end
^

SET TERM ; ^

COMMENT ON COLUMN GIFTCARD_DETAIL.OPER_TYPE IS 
'0 - продажа карты, активация
1 - оплата картой, карта неактивна';

SET TERM ^ ;

create or alter procedure PR_GET_ZREPORT_GIFTCARD (
    AUDIT_ID integer)
returns (
    Z_CAPTION DM_TEXT,
    SNAME DM_TEXT,
    PRICE DM_TEXT,
    QUANT DM_DOUBLE,
    NAL DM_DOUBLE,
    BEZNAL DM_DOUBLE)
as
declare variable VSHIFT DM_ID;
declare variable DEVICE_NUM DM_TEXT;
declare variable Z_ID DM_ID;
begin

  select max(id) from docs d where status = 1 and doc_type in (13) and d.audit_id = :audit_id into z_id;

  select VSHIFT, DEVICE_NUM, caption||' от '||cast(d.commitdate as dm_date) from docs d where id = :z_id into :VSHIFT, :DEVICE_NUM, :Z_CAPTION;

  for select SNAME, round(PRICE,2),
    iif(dd.doc_type=3, 1, -1)*abs(dd.QUANT),
    iif(dd.doc_type=3, 1, -1)*round(iif(abs(d.summ1)<=abs(dd.summa),abs(d.summ1), abs(dd.summa)),2) as nal,
    iif(dd.doc_type=3, 1, -1)*round(abs(dd.summa)-iif(abs(d.summ1)<=abs(dd.summa),abs(d.summ1), abs(dd.summa)),2) as beznal
   from vw_doc_detail dd
    left join docs d on dd.doc_id = d.id
    where dd.doc_commitdate between current_date - 10 and current_date + 10 and dd.dep = 4 and d.status = 1 and d.doc_type in (3,9) and d.VSHIFT = :VSHIFT and d.DEVICE_NUM = :DEVICE_NUM
  into :SNAME, :PRICE, :QUANT, :NAL, :BEZNAL  do

  suspend;

end^

SET TERM ; ^

GRANT SELECT ON DOCS TO PROCEDURE PR_GET_ZREPORT_GIFTCARD;
GRANT SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_GET_ZREPORT_GIFTCARD;

GRANT EXECUTE ON PROCEDURE PR_GET_ZREPORT_GIFTCARD TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_GIFTCARD (
    BARCODE type of DM_TEXT not null)
returns (
    S_ID DM_ID,
    S_DATE DM_DATETIME,
    OPER_TYPE DM_STATUS,
    SUMM DM_DOUBLE)
as
begin
  select first 1 s_id, s_date, oper_type from giftcard_detail where s_id is not null and barcode = :barcode order by s_id desc
    into :s_id, :s_date, :oper_type;
  if (oper_type is not null) then
    begin
--      select first 1 price from parts where barcode = :barcode or (bcode_izg=:barcode)  order by id desc into :summ;
        select first 1 p.price
        from parts p
        join wares w on w.id = p.ware_id
        where p.barcode = :barcode or (w.barcode = :barcode)
        order by p.id desc into :summ;
      suspend;
    end
end^

SET TERM ; ^

GRANT SELECT ON GIFTCARD_DETAIL TO PROCEDURE PR_GIFTCARD;
GRANT SELECT ON PARTS TO PROCEDURE PR_GIFTCARD;

GRANT EXECUTE ON PROCEDURE PR_GIFTCARD TO SYSDBA;

Добавить в триггер DOC_DETAIL_AI0

if ((select d.doc_type from docs d where d.id = new.doc_id) in (3)) then
begin
    if ((select wb.sname from warebase wb where wb.part_id = new.part_id ) like '%Подаро%карт%') then
    begin
        insert into giftcard_detail (BARCODE,OPER_TYPE,DOC_ID) values ((select iif(wb.bcode_izg='' or (wb.bcode_izg is null),wb.barcode,wb.bcode_izg ) from warebase wb where wb.part_id = new.part_id), 0,new.doc_id);
    end
end
if ((select d.doc_type from docs d where d.id = new.doc_id) in (9)) then
begin
    if ((select wb.sname from warebase wb where wb.part_id = new.part_id ) like '%Подаро%карт%') then
    begin
        insert into giftcard_detail (BARCODE,OPER_TYPE,DOC_ID) values ((select iif(wb.bcode_izg='' or (wb.bcode_izg is null),wb.barcode,wb.bcode_izg ) from warebase wb where wb.part_id = new.part_id), 1,new.doc_id);
    end
end

В кассире в событии После закрытия смены меняем на

uses
  Graphics, Controls, Forms, Dialogs, StdCtrls, 
  ComCtrls, ExtCtrls, ibquery, DB, ChequeList, FR,
  ScriptRes, Barcode, ZKassa, StrUtils, Windows, Classes,
  IBDatabase;
var
  doc_id : integer;    
  LengthCheak,i , fl: integer;
  iq: tibquery;
  str_temp : string;
  nal, beznal: float;

begin
   Fr_activate(0);
   //Создаем соединение с базой 
   iq := tibquery.create(nil);
   iq.database := CurrDB;
   iq.transaction := creatert(CurrDB);
   iq.transaction.starttransaction;     
   iq.active := False;
   //msgbox(IntToStr(ChequeList.Items[0].ID), 'Ошибка!', mb_ok);
   //запросик к базе   
   fl:=0;
   iq.sql.text := 'select z_caption, sname, price, sum(quant) as quant, sum(nal) as nal, sum(beznal) as beznal from PR_GET_ZREPORT_GIFTCARD ('+IntToStr(SessionID)+') group by z_caption, sname, price having sum(quant) <> 0';
   iq.active := True;
   str_temp := '';
   nal:= 0;
   beznal:= 0;

   //забираем данные
   if not(iq.Eof) then
    begin
     //Длина чековой ленты
     LengthCheak := FR_LINELENGTH;
     str_temp := str_temp + myFormatString('ДОПОЛНЕНИЕ ПО ПОДАРОЧНЫМ КАРТАМ',' ',' ',LengthCheak,0,true)+#13#10;
     str_temp := str_temp + myFormatString(iq.FieldByName('z_caption').AsString,' ',' ',LengthCheak,0,true)+#13#10;
     str_temp := str_temp + myFormatString('Подар. карта (Цена Х Кол-во) Нал/Безнал',' ',' ',LengthCheak,0,true)+#13#10;
     str_temp := str_temp + dupestring('-',LengthCheak)+#13#10;
    end
   else
    begin
     str_temp := '';
//     msgbox('Ошибка. Отчет по подарочным картам не будет распечатан!', 'Ошибка!', mb_ok);
     exit;
    end;
   while not iq.Eof do
    begin
     str_temp := str_temp + myFormatString(iq.FieldByName('sname').AsString + '('+iq.FieldByName('price').AsString+' x '+iq.FieldByName('quant').AsString+')',' ',' ',LengthCheak,1,true)+#13#10;
     str_temp := str_temp + myFormatString(' = ' + iq.FieldByName('nal').AsString+'/'+iq.FieldByName('beznal').AsString,' ',' ',LengthCheak,1,true)+#13#10;
     nal:= nal + iq.FieldByName('nal').AsFloat;
     beznal:= beznal + iq.FieldByName('beznal').AsFloat;
     iq.Next;
    end;
   str_temp := str_temp + dupestring('-',LengthCheak)+#13#10;    
   str_temp := str_temp + myFormatString('ИТОГО:',' НАЛ='+Format('%.2f',[nal])+' БЕЗНАЛ='+Format('%.2f',[beznal]),' ',LengthCheak,1,true)+#13#10;

  //закрываем соединение
   iq.active := False;
   iq.sql.text := 'select USERNAME from USERS where id = '+IntToStr(UserID);
   iq.active := True;

   str_temp := str_temp + dupestring('*',LengthCheak)+#13#10;
   str_temp := str_temp + myFormatString(DateToStr(Date)+' '+TimeToStr(Time),' ',' ',LengthCheak,1,true)+#13#10;
   str_temp := str_temp + myFormatString('Кассир:',iq.FieldByName('USERNAME').AsString,' ',LengthCheak,1,true)+#13#10;

 //закрываем соединение
   iq.active := False;

   // Для select можно не завершать транзакцию
   iq.Transaction.Commit;
   iq.Transaction.Free;
   iq.Free;

   //str_temp := str_temp + dupestring(' ',LengthCheak)+#13#10;
   //str_temp := str_temp + dupestring(' ',LengthCheak)+#13#10;
   //str_temp := str_temp + myFormatCheque('Подпись: _______________________',' ',' ',LengthCheak,1,true)+#13#10;
   //str_temp := str_temp + myFormatCheque('                   М.П.',' ',' ',LengthCheak,1,true)+#13#10;
   str_temp := str_temp + dupestring(' ',LengthCheak)+#13#10;
   str_temp := str_temp + dupestring(' ',LengthCheak)+#13#10;
   //Устанавливаем сформированную строку
   FR_STRING_SET(str_temp);
   //Печатаем строку на фискальнике
   FR_PRINTSTRING;
   FR_GETADVTEXT;
   FR_PRINTSTRING;
//   FR_PRINTCLICHE;
   //протяжка
   FR_NUMBER_SET(2);
   FR_FEEDTAPE;
   //отрезка
   FR_BOOLEAN_SET(false);
   FR_CUTTAPE;
   ChequeList.CloseAll;
   //Отключаем оборудование                 
   FR_DEACTIVATE;
end

В Кассире устанавливаем галочку в пункте Подарочные карты 2

В итоге

Оплата подарочными картами идёт как отдельный тип оплаты - настраивается на ККМ
Приходование подарочной карты после оплаты ею идёт в триггере GIFTCARD_DETAIL_BI, по
тем настройкам как сейчас - документом оприходования(тип документа 20)