UPDATE OR INSERT INTO ANALYTICS (ID, PARENT_ID, CAPTION, STATUS, DESCRIPTION, DETAIL_TABLENAME, DATA, USERDATA, BINDATA) VALUES (3, 0, 'Проверка ЖНВЛС', 0, NULL, ' ', ';createsql - запрос создания документа аналитики ;----:session_id - предустановленный параметр - передается программой менеджер - номер текущей сессии ;:analytic_doc_id - предустановленный параметр - передается программой менеджер - код заранее созданного документа аналитики ; остальные параметры - создаются во время выполнения на основании ANALYTICS.USERDATA [createsql] select verb from PR_GNVLSCHECK(:analytic_doc_id,:SOURCE,:periodbeg,:periodend) ;в таблице детализации должно быть поле ANALYTIC_DOC_ID = ANALYTIC_DOCS.id [selectsql] select * from ANALYTIC_GNVLS order by id,sname [deletesql] delete from ANALYTIC_GNVLS [main] SelectFieldExpression=sname ;cfFRtmpl - путь к шаблону пакетов cfFastReport cfFRtmpl=analytics_ProverkaGV.cffr3 tablename=ANALYTIC_GNVLS report_id=31', ';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_GNVLSCHECK ( ANALYTIC_DOC_ID DM_ID, SOURCE DM_STATUS, PERIODBEG DM_DATE, PERIODEND DM_DATE) returns ( VERB DM_TEXT1024) as declare variable PARAM_VALUE DM_DOUBLE; declare variable PARAM_ID DM_TEXT; declare variable NAC3 DM_DOUBLE; declare variable NAC2 DM_DOUBLE; declare variable NAC1 DM_DOUBLE; begin DELETE FROM analytic_GNVLS ad where ad.analytic_doc_id in (select id from analytic_docs a where a.analytic_type=3 and CURRENT_DATE-Cast(a.insertdt as dm_date)>=31); DELETE from analytic_docs a where a.analytic_type=3 and CURRENT_DATE-Cast(a.insertdt as dm_date)>=31; for select param_id,Cast(REPLACE(param_value,',','.') as dm_double) as param_value from PARAMS where parent_id =-50 into :param_id, :param_value do begin if (param_id='NAC1') then nac1 = param_value; if (param_id='NAC2') then nac2 = param_value; if (param_id='NAC3') then nac3 = param_value; end --Поиск среди приходов if (:source=1) then insert into analytic_GNVLS( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Превышение максимальной розничной цены', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, gb.price, w.seria ,w.part_id 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$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode)) and abs(gb.price_r-w.price_r)<=0.01 where w.doc_commitdate between :periodbeg and :periodend and dt.base_type=1 and (trim(gb.barcode)<>'' and gb.barcode is not null and (w.price>=gb.price)) group by 'Превышение максимальной розничной цены',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id; if (:source=0) then insert into analytic_GNVLS( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Превышение максимальной розничной цены', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, gb.price, w.seria ,w.part_id from warebase w left join out$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode)) and abs(gb.price_r-w.price_r)<=0.01 where (w.quant>0 and trim(gb.barcode)<>'' and gb.barcode is not null and (w.price>=gb.price)) group by 'Превышение максимальной розничной цены',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id; if (:source=2) then insert into analytic_GNVLS( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Превышение максимальной розничной цены', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, gb.price, w.seria ,w.part_id from vw_doc_detail w left join docs d on d.id=w.doc_id left join out$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode)) and abs(gb.price_r-w.price_r)<=0.01 where w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11,39) and (trim(gb.barcode)<>'' and gb.barcode is not null and (w.price>=gb.price)) group by 'Превышение максимальной розничной цены',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id; if (:source=0) then insert into analytic_GNVLS( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Не найден в реестре ЖНВЛС или не верная цена реестра', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id from warebase w left join out$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode)) and abs(gb.price_r-w.price_r)<=0.01 where exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id) and w.quant>0 and gb.barcode is null group by 'Не найден в реестре ЖНВЛС или не верная цена реестра' ,w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id; if (:source=1) then insert into analytic_GNVLS( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Не найден в реестре ЖНВЛС или не верная цена реестра', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id 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$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode)) and abs(gb.price_r-w.price_r)<=0.01 where w.doc_commitdate between :periodbeg and :periodend and dt.base_type = 1 and exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id) and gb.barcode is null group by 'Не найден в реестре ЖНВЛС или не верная цена реестра' ,w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id; if (:source=2) then insert into analytic_GNVLS( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Не найден в реестре ЖНВЛС или не верная цена реестра', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id from vw_doc_detail w left join docs d on d.id=w.doc_id left join out$gnvls_barcode gb on ((w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode)) and abs(gb.price_r-w.price_r)<=0.01 where w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11,39) and exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id) and gb.barcode is null group by 'Не найден в реестре ЖНВЛС или не верная цена реестра' ,w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price, gb.price,w.seria,w.part_id; if (:source=0) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Превышение наценки', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, iif(w.price_z>w.price_r*1.1 and w.price_r>0,w.price_r*1.1,w.price_z)*iif(w.price_z*100/110>=500,:nac3,iif(w.price_z*100/110>=50,:nac2,:nac1))/100+w.price_o, w.seria ,w.part_id from warebase w where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and w.price_r<>0 and w.price_z<>0 and 100*(w.price-w.Price_O)/iif(w.price_z*100/110>w.price_r and w.price_r>0,w.price_r*1.1,w.price_z) >iif(w.price_z*100/110>=500,:nac3,iif(w.price_z*100/110>=50,:nac2,:nac1)) and w.price_z>0 and w.quant>0 and w.mmbsh not containing 'Сильно'; if (:source=1) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Превышение наценки', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, iif(w.price_z>w.price_r*1.1 and w.price_r>0,w.price_r*1.1,w.price_z)*iif(w.price_z*100/110>=500,:nac3,iif(w.price_z*100/110>=50,:nac2,:nac1))/100+w.price_o, w.seria ,w.part_id 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 where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and w.price_r<>0 and w.price_z<>0 and 100*(w.price-w.Price_O)/iif(w.price_z*100/110>w.price_r and w.price_r>0,w.price_r*1.1,w.price_z) >iif(w.price_z*100/110>=500,:nac3,iif(w.price_z*100/110>=50,:nac2,:nac1)) and w.price_z>0 and dt.base_type=1 and w.doc_commitdate between :periodbeg and :periodend and w.mmbsh not containing 'Сильно'; if (:source=2) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Превышение наценки', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, iif(w.price_z>w.price_r*1.1 and w.price_r>0,w.price_r*1.1,w.price_z)*iif(w.price_z*100/110>=500,:nac3,iif(w.price_z*100/110>=50,:nac2,:nac1))/100+w.price_o, w.seria ,w.part_id from vw_doc_detail w left join docs d on d.id=w.doc_id where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and w.price_r<>0 and w.price_z<>0 and 100*(w.price-w.Price_O)/iif(w.price_z*100/110>w.price_r and w.price_r>0,w.price_r*1.1,w.price_z) >iif(w.price_z*100/110>=500,:nac3,iif(w.price_z*100/110>=50,:nac2,:nac1)) and w.price_z>0 and d.doc_type in (6,11,39) and w.doc_commitdate between :periodbeg and :periodend and w.mmbsh not containing 'Сильно'; if (:source=0) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Не указана цена завода', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id from warebase w -- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (w.price_z=0 or (w.price_z is null) ) and where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_z between -0.000001 and 0.000001) or (w.price_z is null) ) and w.quant>0 ; if (:source=1) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Не указана цена завода', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id 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 -- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (w.price_z=0 or (w.price_z is null) ) and where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_z between -0.000001 and 0.000001) or (w.price_z is null) ) and w.doc_commitdate between :periodbeg and :periodend and dt.base_type=1; if (:source=2) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Не указана цена завода', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id from vw_doc_detail w left join docs d on d.id=w.doc_id -- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (w.price_z=0 or (w.price_z is null) ) and where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_z between -0.000001 and 0.000001) or (w.price_z is null) ) and w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11,39); if (:source=0) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Не указана цена рестра', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id from warebase w -- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (w.price_r=0 or (w.price_r is null) ) and where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_r between -0.00001 and 0.000001) or (w.price_r is null) ) and w.quant>0 ; if (:source=1) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Не указана цена рестра', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id 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 -- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (w.price_r=0 or (w.price_r is null) ) and where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_r between -0.00001 and 0.000001) or (w.price_r is null) ) and w.doc_commitdate between :periodbeg and :periodend and dt.base_type=1; if (:source=2) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Не указана цена рестра', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id from vw_doc_detail w left join docs d on d.id=w.doc_id -- where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (w.price_r=0 or (w.price_r is null) ) and where (exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and ((w.price_r between -0.00001 and 0.000001) or (w.price_r is null) ) and w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11,39); if (:source=0) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Укажите группу ЖНВЛС', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id from warebase w left join out$gnvls_barcode gb on (w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode) where (not exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (trim(gb.barcode)<>'' and gb.barcode is not null) and w.quant>0 group by 'Укажите группу ЖНВЛС',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price,w.seria,w.part_id; if (:source=1) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Укажите группу ЖНВЛС', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id 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$gnvls_barcode gb on (w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode) where (not exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (trim(gb.barcode)<>'' and gb.barcode is not null) and w.doc_commitdate between :periodbeg and :periodend and dt.base_type =1 group by 'Укажите группу ЖНВЛС',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price,w.seria,w.part_id; if (:source=2) then insert into analytic_GNVLS ( ANALYTIC_DOC_ID, VID, NAME_ID, SNAME, IZG_ID, SIZG, COUNTRY_ID, SCOUNTRY, PRICE, MAXPRICE, SERIA, PART_ID) select :analytic_doc_id, 'Укажите группу ЖНВЛС', w.name_id, w.sname, w.izg_id, w.sizg, w.country_id, w.scountry, w.price, 0, w.seria ,w.part_id from vw_doc_detail w left join docs d on d.id=w.doc_id left join out$gnvls_barcode gb on (w.bcode_izg = gb.barcode)or(w.barcode = gb.barcode) where (not exists(select id from group_detail where grouptable='PARTS.NAME_ID' and group_id=1 and grouptable_id=w.name_id)) and (trim(gb.barcode)<>'' and gb.barcode is not null) and w.doc_commitdate between :periodbeg and :periodend and d.doc_type in (6,11,39) group by 'Укажите группу ЖНВЛС',w.name_id, w.sname,w.izg_id,w.sizg, w.country_id,w.scountry,w.price,w.seria,w.part_id; verb='Готово'; delete from analytic_GNVLS ag where (select gd.id from group_detail gd left join groups g on g.id=gd.group_id where gd.grouptable_id=ag.PART_ID and gd.grouptable='PARTS' and g.caption containing 'исклю') is not null; suspend; end^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT,INSERT,DELETE ON ANALYTIC_GNVLS TO PROCEDURE PR_GNVLSCHECK; GRANT SELECT,DELETE ON ANALYTIC_DOCS TO PROCEDURE PR_GNVLSCHECK; GRANT SELECT ON PARAMS TO PROCEDURE PR_GNVLSCHECK; GRANT SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_GNVLSCHECK; GRANT SELECT ON DOCS TO PROCEDURE PR_GNVLSCHECK; GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_GNVLSCHECK; GRANT SELECT ON OUT$GNVLS_BARCODE TO PROCEDURE PR_GNVLSCHECK; GRANT SELECT ON WAREBASE TO PROCEDURE PR_GNVLSCHECK; GRANT SELECT ON GROUP_DETAIL TO PROCEDURE PR_GNVLSCHECK; GRANT SELECT ON GROUPS TO PROCEDURE PR_GNVLSCHECK; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE PR_GNVLSCHECK TO SYSDBA;