Версия 22:51, 13 декабря 2015

Поиск расхождений по чекам в базе

Расхождения можно найти спомощью запроса

 
select
r.zdate,
r.zid,
abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) as rash_nal,
abs(r.zsumm2 - r.csumm2) - abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) as rash_beznal,
doc.vnum as vnum,
doc.id as doc_id,
doc.summ1 as nal,
doc.summ2 as bezna,
iif(doc.status = 1, 'Не удален', 'Удален')
from (
  select
  cast(d.commitdate as dm_date) as zdate,
  d.id as zid,
  d.vnum as zvnum,
  d.vshift as vshift,
  d.device_num as device_num,
  d.summ1 as zsumm1,
  d.summ2 as zsumm2,
  coalesce((select sum(-c.summ1) from docs c where 1=1
    and c.doc_type in (3,9)
    and c.device_num=d.device_num
    and c.vshift=d.vshift
    and c.status=1
    and cast(c.commitdate as dm_date) between cast(d.commitdate as dm_date) -7 and cast(d.commitdate as dm_date) +7
  ), 0) as csumm1,
  coalesce((select sum(-(c.summ1+c.summ2)) from docs c where 1=1
    and c.doc_type in (3)
    and c.device_num=d.device_num
    and c.vshift=d.vshift
    and c.status=1
    and cast(c.commitdate as dm_date) between cast(d.commitdate as dm_date) -7 and cast(d.commitdate as dm_date) +7
  ), 0) as csumm2,
  coalesce((select sum(-h.summa) from cash_docs h where 1=1
    and h.doc_type in (1,2)
    and h.summa < d.summ1
    and h.device_num=d.device_num
    and h.vshift=d.vshift
    and h.status=1
    and cast(h.commitdate as dm_date) between cast(d.commitdate as dm_date) -7 and cast(d.commitdate as dm_date) +7
  ), 0) as summ_cash
  from docs d
  where 1=1
  and d.doc_type=13
  order by d.commitdate desc
) r
left join docs doc on 1=1
  and doc.doc_type in (3)
  and doc.vshift=r.vshift
  and doc.device_num=r.device_num
  and cast(doc.commitdate as dm_date) between r.zdate -7 and r.zdate +7
where
(
  abs(r.zsumm1 - (r.csumm1 + r.summ_cash)) > 0.01
    or
  abs(r.zsumm2 - r.csumm2) > 0.01
)
and
(
  abs(doc.summ1)=abs(r.zsumm1 - (r.csumm1 + r.summ_cash))
    or
  abs(doc.summ2)=abs(r.zsumm2 - (r.csumm2))
)
 

Исправление расхождений

Дублирование чека

Иногда возникает необходимость полностью задублировать чек в базе, при этом не проводя его ни по ККМ ни по банковскому терминалу. Для этого выполните скрипт ниже

Создание процедуры

 
SET TERM ^ ;

create or alter procedure PR_CREATE_DUPLICATE_CHECK (
    DOC_ID DM_ID,
    DOC_QUANT DM_ID = 1)
returns (
    O$DOC_ID DM_ID)
as
declare variable I integer;
declare variable DOC_TYPE DM_STATUS;
declare variable AGENT_ID DM_ID;
declare variable PARENT_ID DM_ID;
declare variable DOCNUM DM_TEXT;
declare variable DOCDATE DM_DATETIME;
declare variable SESSION_ID DM_ID;
declare variable PARAM_VALUE DM_TEXT1024;
declare variable COMMENTS DM_BLOBTEXT;
declare variable SUMM1 DM_DOUBLE;
declare variable SUMM2 DM_DOUBLE;
declare variable SUMM3 DM_DOUBLE;
declare variable SUMM4 DM_DOUBLE;
declare variable VSHIFT DM_ID;
declare variable DEVICE_NUM DM_TEXT;
declare variable VNUM DM_ID;
declare variable BONUS DM_DOUBLE;
declare variable CAPTION DM_TEXT;
begin
  i=0;

  --Включаем режим расход в минус, предварительно запоминая значение режима 1-Разрешен, 0-Запрещен
  select PARAM_VALUE from params where param_id = 'RASHOD_MINUS' into :PARAM_VALUE;
  Update params set param_value = '1' where param_id = 'RASHOD_MINUS';

  select DOC_TYPE, AGENT_ID, PARENT_ID, DOCNUM, DOCDATE, AUDIT_ID, coalesce(COMMENTS,''), SUMM1,SUMM2,SUMM3,SUMM4,VSHIFT,DEVICE_NUM,VNUM,BONUS,CAPTION from docs where id = :DOC_ID into :DOC_TYPE, :AGENT_ID, :PARENT_ID, :DOCNUM, :DOCDATE, :SESSION_ID, :COMMENTS, :SUMM1,SUMM2,SUMM3,SUMM4,VSHIFT,DEVICE_NUM,VNUM,BONUS,CAPTION;

  while (:DOC_QUANT > i) do
  Begin
   i=i+1;

    --Новый документ
    select DOC_ID from PR_NEWDOC(:DOC_TYPE, :AGENT_ID, :PARENT_ID, :DOCNUM, :DOCDATE, :SESSION_ID) into :O$DOC_ID;

    --Детализация
    INSERT INTO DOC_DETAIL_ACTIVE (PARENT_ID, DOC_ID, PART_ID, PART_PARENT_ID, DOC_DETAIL_ID, PRICE, NAC, QUANT, DISCOUNT,
                 SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, BARCODE, BARCODE1, GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT,
                 KEMVSERT, SDSERT, REGN, NGTD, EDIZM, NAME_ID, IZG_ID, COUNTRY_ID, ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, Z_ID,
                 SKLAD_ID, SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, INSERTDT, KOEF, MOTHERPART_ID, DEP, BCODE_IZG, HUMAN_QUANT,
                 SUM_DSC, CUSTOMDRAW, STATUS, PART_TYPE, BASE_AGENT_ID, GROUP_ID, PACKET, VPART_ID)
    select PARENT_ID, :O$DOC_ID, PART_ID, PART_PARENT_ID, 0, PRICE, NAC, QUANT, DISCOUNT,
                 SUMMA, SUMMA_O, DCARD, WARE_ID, PRICE_O, PRICE_Z, PRICE_R, dd.BARCODE, BARCODE1, GODENDO, SERIA, NDS, SUM_NDSO, SERT, DATESERT,
                 KEMVSERT, SDSERT, REGN, NGTD, EDIZM, dd.NAME_ID, dd.IZG_ID, dd.COUNTRY_ID, dd.ORIG_CODE, ORIG_NAME_ID, ORIG_IZG_ID, ORIG_COUNTRY_ID, Z_ID,
                 dd.SKLAD_ID, dd.SNAME, SIZG, SCOUNTRY, SORIG_NAME, SORIG_IZG, SORIG_COUNTRY, dd.INSERTDT, 0, 0, DEP, BCODE_IZG, HUMAN_QUANT,
                 SUM_DSC, Null, 0, PART_TYPE, BASE_AGENT_ID, GROUP_ID, dd.PACKET, 0
    from vw_doc_detail dd left join wares w on dd.ware_id = w.id where doc_id = :DOC_ID ;

    --Проводим
    execute procedure pr_doc_commit(:O$DOC_ID,:SESSION_ID);

    update docs set comments = :COMMENTS||'(Дубль чека ID='||cast(:DOC_ID as DM_TEXT)||')',
    SUMM1 = :SUMM1,
    SUMM2 = :SUMM2,
    SUMM3 = :SUMM3,
    SUMM4 = :SUMM3,
    VSHIFT = :VSHIFT,
    DEVICE_NUM = :DEVICE_NUM,
    VNUM = :VNUM,
    BONUS = :BONUS,
    CAPTION = :CAPTION
    where id = :O$DOC_ID;

   suspend;
  end

  --Возвращаем режим как было
  Update params set param_value = :PARAM_VALUE where param_id = 'RASHOD_MINUS';

end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT,UPDATE ON PARAMS TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT SELECT,UPDATE ON DOCS TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT EXECUTE ON PROCEDURE PR_NEWDOC TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT INSERT ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT SELECT ON WARES TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE PR_CREATE_DUPLICATE_CHECK;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_CREATE_DUPLICATE_CHECK TO SYSDBA;
 

Как использовать

Запустите процедуру, укажите ID чека продажи или возврата и кол-во дублей. Выполните процедуру, подтвердите транзакцию

Теги: дублирование чека, дубль, задублировать чек