| Additional Information | Вот блок который я написал...
execute block (ZID integer=:ZID, Dat_ timestamp=:aDate)
returns (result varchar(80))
as
declare variable dgvdtkey integer;
declare variable dgvktkey integer;
declare variable gid integer;
declare variable gddktkey integer;
declare variable kid integer;
declare variable X double precision;
declare variable Y double precision;
declare variable Z double precision;
declare variable qnt integer;
declare variable quant double precision;
declare variable sum_quant double precision;
declare variable source double precision;
declare variable sum_source double precision;
declare variable gdddtkey integer;
declare variable price double precision;
declare variable nds double precision;
declare variable proc_nds double precision;
declare variable storid integer;
declare variable stor integer;
declare variable wcid integer;
declare variable NBR varchar(20);
declare variable summa double precision;
declare variable snds double precision;
declare variable DGVEMPTY integer;
declare variable materialtyp integer;
begin
dgvdtkey=GEN_ID(dgvdt_id,1);
dgvktkey=GEN_ID(dgvkt_id,1);
--обновляем c.sizez из справочника Товары для упрощения расчета объема
update WORKZCUTTING c set c.sizez =(select g.sizez from goods g where g.id=c.godsid) where c.sizez=0.0 and c.zid=:zid
and (select materialtyp from goods where c.godsid=id)=3;
Select Z.Stor, Z.InpStor2, Z.Nbr, S.NdsProc from WorkZakaz Z, StorList S
where Z.ID=:ZID and S.ID=Z.Stor
into :stor, :storid, :nbr, :proc_nds;
insert into DGVDT(id, nbr, dat_, saledate, out_date, stor, storid, comment, typ, subtyp, zid, ProcNds, RealTyp, NdsTyp)
values(:dgvdtkey, :nbr, :dat_, :dat_, :dat_, :stor, :storid,'Возврат делового отхода '||:nbr ,1 ,2, :zid, :proc_nds, 0, 1) ;
insert into DGVkt(id, nbr, dat_, dgvkey, stor, storid, typ, zid)
values(:dgvktkey, :nbr, :dat_,:dgvdtkey, :storid, :stor,1,:zid) ;
-- в цикле расчитываем общее кол-во в ед.измерения в зависимости от типа мат-ла по наименованиям
for select wc.godsid, sum(wc.qnt_one*(wc.sizex/1000)*(wc.sizey/1000)*((
case
when g.MATERIALTYP=3 then wc.sizez
else 1000 end)/1000))
from workzcutting wc
join goods g on g.id=wc.godsid
where wc.cutstortyp=2 and wc.zid=:zid and wc.Qnt_One > 0.0
and wc.SizeX <> 0 and wc.SizeY <> 0 and wc.GddKey is Null
and wc.Deleted = 0
group by 1
into :gid, :sum_quant
do
begin
quant=0.0;
summa=0.0;
snds=0.0;
source=:sum_quant;
--проверяем кол-во доступного материала в заказе
select coalesce(sum(gk.quant),0.0)
from gddkt gk
join gdddt gd on gk.gddkey=gd.id
join dgvdt d on d.id=gd.dgvkey
where d.typ=1 and d.subtyp=1 and d.zid=:zid
and gk.gdskey = :gid
into :sum_source;
if (:sum_source-:sum_quant>0.0) then
begin
-- списываем общий по наименованию объем/площадь общим кол-вом отбирая кол-во по каждой приходной партии в заказе
for select gk.id, gk.price, gk.nds, gk.quant
from gddkt gk
join gdddt gd on gk.gddkey=gd.id
join dgvdt d on d.id=gd.dgvkey
where d.typ=1 and d.subtyp=1 and d.zid=:zid
and gk.gdskey = :gid
order by gk.quant desc
into :kid, :price, :nds, :quant
do
begin
-- кол-во в партии больше кол-ва общего списания
if ((:quant-:sum_quant) >=0.0) then
begin
quant=:sum_quant;
gdddtkey=GEN_ID(gdddt_id, 1);
insert into gdddt(id, dgvkey, gddkey, quant, zid, price, pricen, nds)
values( :gdddtkey, :dgvdtkey, :kid, :quant, :zid, :price, :price-:nds, :nds);
break;
end
-- кол-во в партии меньше общего кол-ва к списанию
if ((:quant-:sum_quant) <0.0) then
begin
gdddtkey=GEN_ID(gdddt_id, 1);
insert into gdddt(id, dgvkey, gddkey, quant, zid, price, pricen, nds)
values( :gdddtkey, :dgvdtkey, :kid, :quant,:zid, :price, :price-:nds, :nds);
sum_quant=:sum_quant-:quant;
summa = summa+:quant*:price;
snds = snds+:quant*:nds;
end
end
-- если списание происходило из нескольких партий - расчитываем среднюю цену обрезка
if (:summa<>0.0) then
begin
price = :summa/:source;
nds = :snds/:source;
end
for select c.id, c.sizex, c.sizey, c.sizez, c.qnt_one, coalesce(g.materialtyp,0)
from workzcutting c
join goods g on g.id=c.godsid
where c.cutstortyp=2 and c.godsid=:gid and c.Qnt_One > 0.0
and c.SizeX <> 0 and c.SizeY <> 0 and C.GddKey is Null
and C.Deleted = 0 and c.zid= :zid
into :wcid, :x, :y, :z, :qnt, :materialtyp
do
begin
gddktkey=GEN_ID(gddkt_id, 1);
quant=:qnt*(:x/1000)*(:y/1000);
if (:materialtyp=3) then quant=quant*(:z/1000);
insert into gddkt(id, dgvkey, gdskey, source, quant, sizex, sizey, sizez, price, nds, pricens, src_one, qnt_one)
values(:gddktkey, :dgvktkey, :gid, :quant, :quant, :x, :y, :z, :price, :nds, :price-:nds,:qnt, :qnt);
update workzcutting wc set wc.gddkey=:gddktkey where wc.id=:wcid;
end
end
end
DgvEmpty = 0;
/* списано ли что-нибудь - не пустая ли накладная */
Select 1 from DgvDt DD where DD.ID=:DgvDtkey and
not Exists(Select ID from GddDt where DgvKey=:DgvDtKey)
into :DgvEmpty;
if (:DgvEmpty = 1) then begin
delete from DgvKt where ID=:DgvKtKey;
Delete from DgvDt where ID=:DGvDtKey;
DgvDtKey = Null;
DgvKtKey = Null;
end
else begin
Update DgvDt Set DgvKey=:DgvKtKey where ID=:DgvDtKey;
Update DgvKt Set DgvKey=:DgvDtKey where ID=:DgvKtKey;
end
result='Расчет окончен!';
suspend;
end
|
|---|
