create or alter procedure create_zayavka (
data timestamp,
sclad varchar(5),
scladgruppa varchar(5))
returns (
doccount integer)
as
declare variable num integer;
declare variable znum integer;
declare variable resnum integer;
declare variable split_docs integer;
declare variable split_nds integer;
declare variable split_by_type_uchet integer;
declare variable split_tp integer;
declare variable sdocs integer;
declare variable snds integer;
declare variable vm integer;
declare variable zero_ves integer;
declare variable tp varchar(5);
declare variable zsclad varchar(5);
declare c_holes cursor for (
select num
from get_holes
order by num);
begin
-- получаем параметры (разбивать документы по группам, по ндс)
select coalesce(val,1)
from options where name='split_docs'
into :split_docs;
select coalesce(val,1)
from options where name='split_nds'
into :split_nds;
select coalesce(val,0)
from options where name='split_by_type_uchet'
into :split_by_type_uchet;
select coalesce(val,1)
from options where name='zero_unknown_ves'
into :zero_ves;
select coalesce(val,1)
from options where name='split_tp'
into :split_tp;
if (sclad='') then sclad = null;
if (scladgruppa='') then scladgruppa = null;
-- получаем кол-во документов, которое должно создасться(запросом с count-ом че то не получилось)
doccount = 0;
for
select ze.num
from zayavka_ed_ch ze
left join zayavka z on ze.num=z.num
left join sprtovar st on z.tovar=st.code
left join sprsclad ss on z.sclad=ss.code
where ze.data=:DATA and
z.sclad=iif(:sclad is not null, :sclad, z.sclad) and
ss.typetovar=iif(:scladgruppa is not null, :scladgruppa, ss.typetovar)
group by ze.num,
case :split_docs
when 1 then case
when :split_by_type_uchet=0 then st.tov_gr_f
when (:split_by_type_uchet=1) and (ze.uchet=0) then st.tov_gr_f
when (:split_by_type_uchet=1) and (ze.uchet=1) then 0
when (:split_by_type_uchet=2) and (ze.uchet=0) then 0
when (:split_by_type_uchet=2) and (ze.uchet=1) then st.tov_gr_f
end
else 0 end,
case :split_nds when 1 then st.nds else 0 end,
iif(:zero_ves=1,st.ves_mark,0)
into :znum
do begin
doccount=doccount+1;
end
-- удаляем зарезервированные, но не занятые номера
delete from factura_ed_ch
where firm is null and num>1 and num<1000000;
-- резервируем нужное кол-во номеров документов выставлением генератора gen_min_num
select gen_id(gen_min_num,(select max(num)+:doccount+2 from factura_ed_ch where num<1000000) - gen_id(gen_min_num,0))
from rdb$database
into :resnum;
-- разбиваем заявки, если требуется, по ндс, группе товара, весу и вставляем во временную таблицу
-- delete from t_num;
insert into t_num(znum, split_docs, split_nds, ves_mark, sclad, edinum)
select ze.num,
case :split_docs
when 1 then case
when :split_by_type_uchet=0 then st.tov_gr_f
when (:split_by_type_uchet=1) and (ze.uchet=0) then st.tov_gr_f
when (:split_by_type_uchet=1) and (ze.uchet=1) then 0
when (:split_by_type_uchet=2) and (ze.uchet=0) then 0
when (:split_by_type_uchet=2) and (ze.uchet=1) then st.tov_gr_f
end
else 0 end,
case :split_nds when 1 then st.nds else 0 end,
iif(:zero_ves=1,st.ves_mark,0),
ze.sclad,
ze.komment
from zayavka_ed_ch ze
left join zayavka z on ze.num=z.num
left join sprtovar st on z.tovar=st.code
where ze.data=:DATA and z.num is not null
group by 1,2,3,4,5,6
order by 5,1;
-- проставляем во временной таблице номера фактур,
-- сначала дырки, которые берутся из курсора C_HOLES
open C_HOLES;
for select znum, split_docs, split_nds, ves_mark
from t_num
order by sclad, znum
into :znum, :sdocs, :snds, :vm
do begin
fetch C_HOLES into :num;
if (row_count=0) then leave;
update t_num set num=:NUM
where znum=:znum and
split_docs=:sdocs and
split_nds=:snds and
ves_mark=:vm;
end
close C_HOLES;
-- затем просто порядковые номера
select coalesce(max(num)+2,1)
from factura_ed_ch
where num<1000000
into :resnum;
for select znum, split_docs, split_nds, ves_mark
from t_num
where num is null
order by znum
into :znum, :sdocs, :snds, :vm
do begin
update t_num
set num=:resnum
where znum=:znum and
split_docs=:sdocs and
split_nds=:snds and
ves_mark=:vm;
resnum=resnum+1;
end
-- заносим фактуры (factura)
insert into factura(NUM,DATA,FIRM,SCLAD,MAG,TOVAR,PIDTOVAR,KOL,TP,ZENA,ZENA_SV,
SUMMA,SUMMANDS,SUMMAALL,SUMMANP,VES,DEL_MARK,APPLY_MARK,
UCHET,MAG0,DAYOFWEEK,NDS,NUM_FACTURA,DATA_FACTURA,OSNOV,RASCHET,TYPEZEN)
select t.num, z.data, z.firm, z.sclad, z.mag, z.tovar, st.pid as pidtovar,
z.kol*(1-st.ves_mark*:zero_ves) as kol , -- если товар весовой, то в заявке почему то стоит кол-во 1
case when :split_tp=1 then coalesce(ktp.tp,'БезТП') else sk.tp end,
tz.zena, tz.zena/(1+cast(st.nds as float)/100) as zena_sv,
z.kol*(1-st.ves_mark*:zero_ves)*tz.zena/(1+cast(st.nds as float)/100) as summa,
z.kol*(1-st.ves_mark*:zero_ves)*tz.zena-z.kol*(1-st.ves_mark*:zero_ves)*tz.zena/(1+cast(st.nds as float)/100) as summands,
z.kol*(1-st.ves_mark*:zero_ves)*tz.zena as summaall, 0 as summanp,
z.kol*(1-st.ves_mark*:zero_ves)*st.ves as ves,
0 as del_mark, 0 as apply_mark, z.uchet, sk.mag0, null as dayofweek,
st.nds, null as num_factura, null as data_factura, null as osnov,
z.raschet,z.typezen
from zayavka z
left join sprtovar st on z.tovar=st.code
left join sprkontragent sk on z.mag=sk.code
--left join tovar_zena tz on z.tovar=tz.idtovar and z.typezen=tz.idzen
left join get_zena(z.tovar,z.typezen,z.mag) tz on 1=1
left join sprsclad ss on z.sclad=ss.code
left join kontragent_tp ktp on z.mag=ktp.mag and st.pid=ktp.pidtovar
left join t_num t on z.num=t.znum and
t.split_docs=(case :split_docs
when 1 then case
when :split_by_type_uchet=0 then st.tov_gr_f
when (:split_by_type_uchet=1) and (z.uchet=0) then st.tov_gr_f
when (:split_by_type_uchet=1) and (z.uchet=1) then 0
when (:split_by_type_uchet=2) and (z.uchet=0) then 0
when (:split_by_type_uchet=2) and (z.uchet=1) then st.tov_gr_f
end
else 0 end) and
t.split_nds=iif(:split_nds=1,st.nds,0) and
t.ves_mark=iif(:zero_ves=1,st.ves_mark,0) --and
-- t.tp=iif(:split_tp=1,ktp.tp,'0')
where z.data=:DATA and z.typezen is not null and
z.sclad=iif(cast(:sclad as varchar(5)) is not null, :sclad, z.sclad) and
ss.typetovar=iif(cast(:scladgruppa as varchar(5)) is not null, :scladgruppa, ss.typetovar) and
coalesce(tz.zena,0)<>0
order by z.num;
-- заносим заголовки фактур (factura_ed_ch)
for select distinct iif(:sclad is null, sclad, :sclad) from zayavka_ed_ch where data=:DATA
into :ZSCLAD
do begin
insert into factura_ed_ch(NUM,FIRM,SCLAD,MAG,DATA,SUMMA,SUMMANDS,SUMMANP,SUMMAALL,
VES,TYPEZEN,ST_NP,APPLY_MARK,DEL_MARK,UCHET,KOMMENT,
MAG0,DAYOFWEEK,RASCHET,TOVAR_TYPE,KOMENTBUX,DRIVER,EXP,ZAYAVKA_DATA)
select f.num, f.firm, f.sclad, f.mag, f.data,
sum(f.summa) as summa, sum(f.summands) as summands, 0 as summanp,
sum(summaall) as summaall, sum(ves) as ves, max(f.typezen), 0 as st_np,
0 as apply_mark, 0 as del_mark, f.uchet, null as komment, sk.mag0,
null as dayofweek, f.raschet, 0 as tovar_type,
(select first 1 edinum from t_num where num=f.num) as komentbux,
ss.driver1 as driver, ss.exp1 as exp,f.data-5 as zayavka_data
from factura f
left join sprkontragent sk on f.mag=sk.code
left join sprsclad ss on f.sclad=ss.code
where f.data=:DATA and
f.sclad=:ZSCLAD and
ss.typetovar=iif(cast(:scladgruppa as varchar(5)) is not null, :scladgruppa, ss.typetovar)
and not exists (select num from factura_ed_ch where num=f.num)
group by 1,2,3,4,5,8,12,13,14,15,16,17,18,19,20,21,22,23,24;
-- заносим в товародвижение (movetovar)
delete from movetovar
where typedoc='00002' and data=:DATA
and numdoc in (select f.num as numdoc
from factura f
left join sprsclad ss on f.sclad=ss.code
where f.data=:DATA and
f.sclad=:ZSCLAD and
-- f.sclad=iif(cast(:sclad as varchar(5)) is not null, :sclad , f.sclad) and
ss.typetovar=iif(cast(:scladgruppa as varchar(5)) is not null, :scladgruppa, ss.typetovar)
);
insert into movetovar(DATA,OUTFIRM,INFIRM,TYPEMOVE,TYPEDOC,NUMDOC,OUTSCLAD,
INSCLAD,PRIHOD,RASHOD,TOVAR,ZENA)
select f.data, f.firm as outfirm, f.mag as infirm, 1 as typemove,
'00002' as typedoc, f.num as numdoc, f.sclad as outsclad, '' as insclad,
0 as prihod, f.kol as rashod, f.tovar, f.zena
from factura f
left join sprsclad ss on f.sclad=ss.code
where f.data=:DATA and
f.sclad=:ZSCLAD and
-- f.sclad in (select distinct sclad from zayavka_ed_ch where data=:DATA) and
-- f.sclad=iif(cast(:sclad as varchar(5)) is not null, :sclad , f.sclad) and
ss.typetovar=iif(cast(:scladgruppa as varchar(5)) is not null, :scladgruppa, ss.typetovar);
end
-- сбрасываем резервирующий генератор в 0
select gen_id(gen_min_num,-gen_id(gen_min_num,0))
from rdb$database
into :resnum;
suspend;
end |
