SET TERM ^ ;

create or alter procedure PR_DOCSCARE (
    DEVICE_NUM DM_TEXT,
    VSHIFT DM_STATUS,
    SUMM1 DM_DOUBLE,
    SUMM2 DM_DOUBLE,
    SUMM3 DM_DOUBLE)
returns (
    X_CASH DM_DOUBLE,
    X_CARD DM_DOUBLE,
    DOC_ID_OLD DM_ID,
    DOC_ID_NEW DM_ID,
    DOC_STATUS DM_STATUS)
as
declare variable ZDATE DM_DATE;
declare variable DSUMM1 DM_DOUBLE;
declare variable DSUMM2 DM_DOUBLE;
declare variable DSUMM34 DM_DOUBLE;
declare variable CDSUMM DM_DOUBLE;
begin
  if (summ1 is null) then
    select summ1, summ2, summ3, cast(commitdate as dm_date) from docs
    where device_num = :device_num and vshift = :vshift and doc_type = 13-- and status = 1
    and commitdate>=current_timestamp-30
     into :summ1, :summ2, :summ3, :zdate;
  else
    zdate = current_date;

  if (not exists (select id from docs where
  commitdate between cast(:zdate as dm_datetime) - 7 and cast(:zdate as dm_datetime) + 8 and
   vshift = :vshift and doc_type in (3, 9) and device_num = :device_num )) then
    exit;

  select coalesce(round(-sum(summ1), 2), 0), coalesce(round(-sum(summ2), 2), 0), coalesce(round(-sum(summ3 + summ4), 2), 0) -- суммы по продажам и возвратам
  from docs
  where device_num = :device_num and vshift = :vshift and doc_type in (3, 9) and status = 1
    and cast(commitdate as dm_date) between :zdate - 7 and :zdate + 7
  into :dsumm1, :dsumm2, :dsumm34;
  if (:dsumm1=0 and :dsumm2=0 and :dsumm34=0) then
    exit;
  select coalesce(round(-sum(cd.summa), 2), 0) -- суммы по документам кассы
  from cash_docs cd
  where cd.device_num = :device_num and cd.vshift = :vshift and cd.doc_type in (1, 2) and cd.status = 1
    and not cd.doc_id in (select id from docs where doc_type = 13) -- чтобы z-отчеты не попали
    and cast(commitdate as dm_date) between :zdate - 7 and :zdate + 7
  into :cdsumm;

  x_cash = round(summ1 - dsumm1 - cdsumm, 2); -- расхождения по наличности
  if (abs(dsumm34) < 0.01) then -- если не было движения по 3 и 4 видам оплаты
    x_card = round(summ3 - dsumm1 - dsumm2 - x_cash, 2);  -- расхождения по безналу

  if ((abs(x_cash) > 0.01) or (abs(x_card) > 0.01)) then -- есть расхождения
    begin
      doc_status = -1; -- решение: нет решения
      if ((x_cash > -0.01) or (x_card > -0.01)) then -- вариант 1: не хватает чека в базе, оплата наличными/безналом
        begin
          select first 1 d.id from docs d
          where vshift = :vshift and device_num = :device_num and doc_type = 3 and status = -1
            and cast(commitdate as dm_date) between :zdate - 7 and :zdate + 7
            and ((abs(summ1) - :x_cash) < 0.01) and ((abs(summ2) - :x_card) < 0.01) -- оплата налом, безналом или смешанная, совпадает с расхождением
            and (abs(summ3 + summ4) < 0.01) -- других оплат не было
            and vnum = ( -- номер следующего чека на 1 больше
              select first 1 vnum - 1
              from docs
              where id > d.id and vshift = :vshift and device_num = :device_num and doc_type = 3
              order by id
            ) into :doc_id_old;
          if (not doc_id_old is null) then
            begin
              doc_status = 0; -- решение: восстановление документа
              select o$doc_id from pr_create_duplicate_check(:doc_id_old, 1) into :doc_id_new;
            end
        end
      suspend;
    end
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON DOCS TO PROCEDURE PR_DOCSCARE;
GRANT SELECT ON CASH_DOCS TO PROCEDURE PR_DOCSCARE;
GRANT EXECUTE ON PROCEDURE PR_CREATE_DUPLICATE_CHECK TO PROCEDURE PR_DOCSCARE;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_DOCSCARE TO SYSDBA;