UPDATE OR INSERT INTO ANALYTICS (ID, PARENT_ID, CAPTION, STATUS, DESCRIPTION, DETAIL_TABLENAME, DATA, USERDATA, BINDATA)
                         VALUES (6, 0, 'Проверка фальсификат', 0, NULL, '                               ', ';createsql - запрос создания документа аналитики
;----:session_id - предустановленный параметр - передается программой менеджер - номер текущей сессии
;:analytic_doc_id - предустановленный параметр - передается программой менеджер - код заранее созданного документа аналитики
; остальные параметры - создаются во время выполнения на основании ANALYTICS.USERDATA
[createsql]
select verb from PR_FALSECHECK(:analytic_doc_id,:SUSPEND_MODE,:SOURCE,:periodbeg,:periodend)

;в таблице детализации должно быть поле ANALYTIC_DOC_ID = ANALYTIC_DOCS.id
[selectsql]
select * from analytic_FALSIFICAT order by id,sname

[deletesql]
delete from analytic_FALSIFICAT


[main]
SelectFieldExpression=sname
;cfFRtmpl - путь к шаблону пакетов cfFastReport
cfFRtmpl=analytics_proverka_false.cffr3
tablename=analytic_FALSIFICAT
report_id=34', ';NOW|STARTOFMONTH|STARTOFWEEK

[SOURCE]
type=list
caption=Поиск среди
mode=FIXEDITEMS SHOWONLYVALUES RETURNNAME
default=0
0=Остатков
1=Приходов
2=Расходов

[periodbeg]
type=date
caption=Начало периода
mode=
default=STARTOFMONTH

[periodend]
type=date
caption=Конец периода
mode=
default=NOW

[end]', NULL)
                       MATCHING (ID);

COMMIT WORK;


SET TERM ^ ;

create or alter procedure PR_FALSECHECK (
    ANALYTIC_DOC_ID DM_ID_NULL,
    SUSPEND_MODE DM_STATUS,
    SOURCE DM_STATUS,
    PERIODBEG DM_DATE,
    PERIODEND DM_DATE)
returns (
    VERB varchar(1024))
as
begin

    DELETE FROM analytic_falsificat ad where ad.analytic_doc_id in (select id from analytic_docs a where a.analytic_type=6 and CURRENT_DATE-Cast(a.insertdt as dm_date)>=31);

    DELETE from analytic_docs a where a.analytic_type=6 and CURRENT_DATE-Cast(a.insertdt as dm_date)>=31;

    --Поиск среди остатков
    if (:source=0) then
    insert into analytic_FALSIFICAT (
      ANALYTIC_DOC_ID,
      VID,
      NAME_ID,
      SNAME,
      SNAME_FALSE,
      IZG_ID,
      SIZG,
      IZG_FALSE,
      COUNTRY_ID,
      SCOUNTRY,
      QUANT,
      PRICE,
      NUMLETTER,
      SERIA,
      SERIA_FALSE,
      COMMENTS,
      PART_ID,
      MMBSH
      )
    select
      :analytic_doc_id,
      'Подозрение на фальсификат',
      w.name_id,
      w.sname,
      gb.tovar,
      w.izg_id,
      w.sizg,
      gb.izg,
      w.country_id,
      w.scountry,
      w.quant,
      w.price,
      gb.numletter,
      w.seria,
      gb.seria,
      gb.comments,
      w.part_id,
      (select membership from PR_MEMBERSHIPS('PARTS=' || w.part_id || ';PARTS.NAME_ID=' || w.name_id || ';PARTS.NAME_ID=' || w.izg_id ||';',ascii_char(13)||ascii_char(10),1))
     from warebase w left join out$false gb on (w.seria = gb.seria)and(UPPER(Left(w.sname,4)) = UPPER(Left(gb.tovar,4)))
      where w.quant>0 and trim(gb.seria)<>'' and gb.seria is not null;

    --Поиск среди расходов перемещением
    if (:source=2) then
    insert into analytic_FALSIFICAT (
      ANALYTIC_DOC_ID,
      VID,
      NAME_ID,
      SNAME,
      SNAME_FALSE,
      IZG_ID,
      SIZG,
      IZG_FALSE,
      COUNTRY_ID,
      SCOUNTRY,
      QUANT,
      PRICE,
      NUMLETTER,
      SERIA,
      SERIA_FALSE,
      COMMENTS,
      PART_ID,
      MMBSH
      )
    select
      :analytic_doc_id,
      'Подозрение на фальсификат',
      w.name_id,
      w.sname,
      gb.tovar,
      w.izg_id,
      w.sizg,
      gb.izg,
      w.country_id,
      w.scountry,
      w.quant,
      w.price,
      gb.numletter,
      w.seria,
      gb.seria,
      gb.comments,
      w.part_id,
      w.mmbsh
     from vw_doc_detail w left join docs d on d.id=w.doc_id left join out$false gb on (w.seria = gb.seria)and(UPPER(Left(w.sname,4)) = UPPER(Left(gb.tovar,4)))
      where w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11) and trim(gb.seria)<>'' and gb.seria is not null;


     --Поиск среди прих. от поставщика
    if (:source=1) then
    insert into analytic_FALSIFICAT (
      ANALYTIC_DOC_ID,
      VID,
      NAME_ID,
      SNAME,
      SNAME_FALSE,
      IZG_ID,
      SIZG,
      IZG_FALSE,
      COUNTRY_ID,
      SCOUNTRY,
      QUANT,
      PRICE,
      NUMLETTER,
      SERIA,
      SERIA_FALSE,
      COMMENTS,
      PART_ID,
      MMBSH
      )
    select
      :analytic_doc_id,
      'Подозрение на фальсификат',
      w.name_id,
      w.sname,
      gb.tovar,
      w.izg_id,
      w.sizg,
      gb.izg,
      w.country_id,
      w.scountry,
      w.quant,
      w.price,
      gb.numletter,
      w.seria,
      gb.seria,
      gb.comments,
      w.part_id,
      w.mmbsh
     from vw_doc_detail w left join docs d on d.id=w.doc_id  left join doc_types dt on dt.id=d.doc_type left join out$false gb on (w.seria = gb.seria)and(UPPER(Left(w.sname,4)) = UPPER(Left(gb.tovar,4)))
      where w.doc_commitdate between :periodbeg and :periodend and dt.base_type=1 and trim(gb.seria)<>'' and gb.seria is not null;

      verb='Готово';
   suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT,INSERT,DELETE ON ANALYTIC_FALSIFICAT TO PROCEDURE PR_FALSECHECK;
GRANT SELECT,DELETE ON ANALYTIC_DOCS TO PROCEDURE PR_FALSECHECK;
GRANT EXECUTE ON PROCEDURE PR_MEMBERSHIPS TO PROCEDURE PR_FALSECHECK;
GRANT SELECT ON WAREBASE TO PROCEDURE PR_FALSECHECK;
GRANT SELECT ON OUT$FALSE TO PROCEDURE PR_FALSECHECK;
GRANT SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_FALSECHECK;
GRANT SELECT ON DOCS TO PROCEDURE PR_FALSECHECK;
GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_FALSECHECK;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_FALSECHECK TO SYSDBA;