MoS в 12:27, 29 августа 2019

2019-08-29T12:27:18Z

MoS в 12:21, 29 августа 2019

2019-08-29T12:21:43Z

MoS в 12:20, 29 августа 2019

2019-08-29T12:20:42Z

MoS в 12:19, 29 августа 2019

2019-08-29T12:19:49Z

MoS: Новая страница: « CREATE OR ALTER PROCEDURE USR$INV_STATREPORT ( DEPOTKEY DFOREIGNKEY, BEGINDATE DATE, ENDDATE DATE, DEPARTSKEYS VARC…»

2019-08-29T12:18:08Z

Новая страница: « <syntaxhighlight lang="sql"> CREATE OR ALTER PROCEDURE USR$INV_STATREPORT ( DEPOTKEY DFOREIGNKEY, BEGINDATE DATE, ENDDATE DATE, DEPARTSKEYS VARC…»

Новая страница

<syntaxhighlight lang="sql">

CREATE OR ALTER PROCEDURE USR$INV_STATREPORT (
DEPOTKEY DFOREIGNKEY,
BEGINDATE DATE,
ENDDATE DATE,
DEPARTSKEYS VARCHAR(200))
RETURNS (
USR$NAMEGOODGROUP VARCHAR(1024),
SUMTORGBEL DOUBLE PRECISION,
SUMTORGIMP DOUBLE PRECISION,
SUMBEL DOUBLE PRECISION,
SUMIMP DOUBLE PRECISION,
VALUEKEY DFOREIGNKEY,
NUMBERGOODGROUP VARCHAR(10),
SUMBALANCEBEL DOUBLE PRECISION,
SUMBALANCEIMP DOUBLE PRECISION,
BALANCEBEL DOUBLE PRECISION,
BALANCEIMP DOUBLE PRECISION,
LB DINTEGER,
RB DINTEGER,
PARENT DFOREIGNKEY)
AS
declare variable spenddoctypekey DFOREIGNKEY;
declare variable todoctypekey DFOREIGNKEY;
declare variable fromdoctypekey DFOREIGNKEY;
BEGIN
BalanceBel = 0;
BalanceImp = 0;
sumBalanceBel = 0;
sumBalanceImp = 0;
spenddoctypekey = null;
todoctypekey = null;
fromdoctypekey = null;

select
p.id
from
gd_p_getid(147006557, 63934951) p
into
:spenddoctypekey;

select
p.id
from
gd_p_getid(147005934, 63934951) p
into
:todoctypekey;

select
p.id
from
gd_p_getid(147006209, 63934951) p
into
:fromdoctypekey;

FOR
select iif(coalesce(r.id,0) > 0, r.LB, rg.LB) as LB, iif(coalesce(r.id,0) > 0, r.RB, rg.RB) as RB, iif(coalesce(r.id,0) > 0, r.parent, rg.parent) as parent,
iif(coalesce(r.id,0) > 0, r.usr$namegoodgroup, rg.usr$namegoodgroup) as usr$namegoodgroup,
iif(coalesce(r.id,0) > 0, r.USR$VALUEKEY, rg.USR$VALUEKEY) as USR$VALUEKEY,
iif(coalesce(r.id,0) > 0, r.USR$NUMBERROW, rg.USR$NUMBERROW) as USR$NUMBERROW,
sum(iif(c.usr$inv_origincountrykey=1800010, m.credit * coalesce(g.USR$INV_WEIGHT,1) * iif(coalesce(r.id,0) > 0, coalesce(g.usr$inv_coeff,1), coalesce(gr.usr$inv_refformtorgkoeff,1)), 0)) as SumTorgBel,
sum(iif(c.usr$inv_origincountrykey=1800010,0, m.credit * coalesce(g.USR$INV_WEIGHT,1) * iif(coalesce(r.id,0) > 0, coalesce(g.usr$inv_coeff,1), coalesce(gr.usr$inv_refformtorgkoeff,1)))) as SumTorgImp,
sum(iif(c.usr$inv_origincountrykey=1800010, c.usr$inv_costretailncu * m.credit, 0)) as SumBel,
sum(iif(c.usr$inv_origincountrykey=1800010,0, c.usr$inv_costretailncu * m.credit)) as SumImp
from
inv_movement m
join gd_good g on g.id = m.goodkey
left join gd_goodgroup gr on gr.id = g.groupkey
left join usr$inv_refformtorg r on r.id = g.usr$inv_refformtorgkey
left join usr$inv_refformtorg rg on rg.id = gr.usr$inv_refformtorgkey
left join inv_movement m1 on m1.movementkey = m.movementkey and m1.debit > 0
left join inv_card c on c.id = m.cardkey
LEFT JOIN gd_document doc ON m.documentkey = doc.id
where
m.movementdate >= :BeginDate
and
m.movementdate <= :EndDate
and
m.credit > 0
and
m.contactkey = :depotkey
and (r.id is not null or rg.id is not null)
and doc.documenttypekey = :spenddoctypekey
group by
1, 2, 3, 4, 5, 6
union all
select iif(coalesce(r.id,0) > 0, r.LB, rg.LB) as LB, iif(coalesce(r.id,0) > 0, r.RB, rg.RB) as RB, iif(coalesce(r.id,0) > 0, r.parent, rg.parent) as parent,
iif(coalesce(r.id,0) > 0, r.usr$namegoodgroup, rg.usr$namegoodgroup) as usr$namegoodgroup,
iif(coalesce(r.id,0) > 0, r.USR$VALUEKEY, rg.USR$VALUEKEY) as USR$VALUEKEY,
iif(coalesce(r.id,0) > 0, r.USR$NUMBERROW, rg.USR$NUMBERROW) as USR$NUMBERROW,
sum(iif(c.usr$inv_origincountrykey=1800010, (-m.debit+m.credit) * coalesce(g.USR$INV_WEIGHT,1) * iif(coalesce(r.id,0) > 0, coalesce(g.usr$inv_coeff,1), coalesce(gr.usr$inv_refformtorgkoeff,1)), 0)) as SumTorgBel,
sum(iif(c.usr$inv_origincountrykey=1800010,0, (-m.debit+m.credit) * coalesce(g.USR$INV_WEIGHT,1) * iif(coalesce(r.id,0) > 0, coalesce(g.usr$inv_coeff,1), coalesce(gr.usr$inv_refformtorgkoeff,1)))) as SumTorgImp,
sum(iif(c.usr$inv_origincountrykey=1800010, c.usr$inv_costretailncu * (-m.debit+m.credit), 0)) as SumBel,
sum(iif(c.usr$inv_origincountrykey=1800010,0, c.usr$inv_costretailncu * (-m.debit+m.credit))) as SumImp
from
inv_movement m
join gd_good g on g.id = m.goodkey
left join gd_goodgroup gr on gr.id = g.groupkey
left join usr$inv_refformtorg r on r.id = g.usr$inv_refformtorgkey
left join usr$inv_refformtorg rg on rg.id = gr.usr$inv_refformtorgkey
left join inv_movement m1 on m1.movementkey = m.movementkey and m1.debit > 0
left join inv_card c on c.id = m.cardkey
LEFT JOIN gd_document doc ON m.documentkey = doc.id
where
m.movementdate >= :BeginDate
and
m.movementdate <= :EndDate
and
m.credit > 0
and
m.contactkey = :depotkey
and (r.id is not null or rg.id is not null)
and doc.documenttypekey in (:todoctypekey, :fromdoctypekey)
group by
1, 2, 3, 4, 5, 6
INTO :LB, :RB, :PARENT, :USR$NAMEGOODGROUP, :VALUEKEY, :NUMBERGOODGROUP,
:SUMTORGBEL, :SUMTORGIMP, :SUMBEL, :SUMIMP
DO
BEGIN
SUSPEND;
END
SumTorgBel = 0;
SumTorgImp = 0;
SumBel = 0;
SumImp = 0;
FOR
select iif(coalesce(r.id,0) > 0, r.LB, rg.LB) as LB, iif(coalesce(r.id,0) > 0, r.RB, rg.RB) as RB, iif(coalesce(r.id,0) > 0, r.parent, rg.parent) as parent,
iif(coalesce(r.id,0) > 0, r.usr$namegoodgroup, rg.usr$namegoodgroup) as usr$namegoodgroup,
iif(coalesce(r.id,0) > 0, r.USR$VALUEKEY, rg.USR$VALUEKEY) as USR$VALUEKEY,
iif(coalesce(r.id,0) > 0, r.USR$NUMBERROW, rg.USR$NUMBERROW) as USR$NUMBERROW,
sum(iif(c.usr$inv_origincountrykey=1800010,((m.debit - m.credit) * coalesce(g.USR$INV_WEIGHT,1) * iif(coalesce(r.id,0) > 0, coalesce(g.usr$inv_coeff,1), coalesce(gr.usr$inv_refformtorgkoeff,1))),0)) as BalanceBEL,
sum(iif(c.usr$inv_origincountrykey=1800010,0,((m.debit - m.credit) * coalesce(g.USR$INV_WEIGHT,1) * iif(coalesce(r.id,0) > 0, coalesce(g.usr$inv_coeff,1), coalesce(gr.usr$inv_refformtorgkoeff,1))))) as Balance,
sum(iif(c.usr$inv_origincountrykey=1800010, c.usr$inv_costretailncu * (m.debit - m.credit), 0)) as SumBalanceBel,
sum(iif(c.usr$inv_origincountrykey=1800010,0, c.usr$inv_costretailncu * (m.debit - m.credit))) as SumBalanceImp
from
inv_movement m
join gd_good g on g.id = m.goodkey
left join gd_goodgroup gr on gr.id = g.groupkey
left join usr$inv_refformtorg r on r.id = g.usr$inv_refformtorgkey
left join usr$inv_refformtorg rg on rg.id = gr.usr$inv_refformtorgkey
join gd_contact con on con.id = m.contactkey
join gd_contact maincon on maincon.lb <= con.lb and maincon.rb >= con.rb
left join inv_card c on c.id = m.cardkey
where
m.movementdate <= :EndDate
and (r.id is not null or rg.id is not null) and
maincon.id = :depotkey
group by
1, 2, 3, 4, 5, 6
INTO :LB, :RB, :PARENT, :USR$NAMEGOODGROUP, :VALUEKEY, :NUMBERGOODGROUP,
:BALANCEBEL, :BALANCEIMP, :SUMBALANCEBEL, :SUMBALANCEIMP
DO
BEGIN
SUSPEND;
END
END

</syntaxhighlight>

[[Category:База данных]]

← Предыдущая		Версия 12:20, 29 августа 2019
Строка 1:		Строка 1:
−	~~Процедура~~	+	[[Хранимые процедуры]]
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">

← Предыдущая		Версия 12:19, 29 августа 2019
Строка 1:		Строка 1:
−		+	Процедура
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">

@@ Строка 22: / Строка 22: @@
      PARENT DFOREIGNKEY)
   AS
-      declare variable spenddoctypekey DFOREIGNKEY;
+    declare variable spenddoctypekey DFOREIGNKEY;
      declare variable todoctypekey DFOREIGNKEY;
      declare variable fromdoctypekey DFOREIGNKEY;

@@ Строка 1: / Строка 1: @@
 <syntaxhighlight lang="sql">
@@ Строка 160: / Строка 159: @@
 </syntaxhighlight>
-[[Category:База данных]]
+[[Category:Хранимые процедуры]]

USR$INV STATREPORT - История изменений

MoS в 12:27, 29 августа 2019

MoS в 12:21, 29 августа 2019

MoS в 12:20, 29 августа 2019

MoS в 12:19, 29 августа 2019

MoS: Новая страница: « CREATE OR ALTER PROCEDURE USR$INV_STATREPORT ( DEPOTKEY DFOREIGNKEY, BEGINDATE DATE, ENDDATE DATE, DEPARTSKEYS VARC…»