IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='prcKartexAnbar')
DROP proc prcKartexAnbar
GO
create proc prcKartexAnbar
@TarikhAz int=0,
@TarikhEla int=0,
@GroohKalaAz int=0,
@GroohKalaEla int=0,
@CodeKalaAz nvarchar(20)='0',
@CodeKalaEla nvarchar(20)='0',
@GroohShakhsAz int=0,
@GroohShakhsEla int=0,
@TafsiliAz bigint=0,
@TafsiliEla bigint=0,
@AnbarAz int=0,
@AnbarEla int=0,
@WithPishFactorForoosh bit=0, -- 0=false , 1=True
@WithDeleteFactors bit=0, -- 0=false , 1=True
@CodeProjeh bigint
as
begin
----------------------------------- موجودی اول دوره
select
0[Radif],
0[Code],
0[CodeBargeh],
0[Tarikh],
cast( 0 as smallint)[Noe],
cast( 0 as bigint)[CodeTafsili],
0[CodeHesabTafsili],
0 [CodeGoroohAshkhas],
'--'[NameTafsili],
'موجودی اول دوره'[Sharh],
0[CodeKarbar],
cast(100 as smallint)[Vaziat],
d.CodeAnbar[CodeAnbar],
d.codeKala[CodeKala],
k.CodeKala[CodeKalaNamayeshi],
k.CodeGrooh[CodeGorooh],
k.name[NameKala],
d.MojoodiAvalDoreh[Tedad],
d.GheymatAvalDoreh[Mablagh],
0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش
0[SaatSabt],
d.MojoodiAvalDoreh[MeghdarVaredeh],
0.0[MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.mojoodiAvalDoreh,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
d.GheymatAvalDoreh[MablaghVaredeh],
0.0[MablaghSadereh],
cast(d.MojoodiAvalDoreh * d.GheymatAvalDoreh as decimal(19,4) )[MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
0 [CodeProjeh]
from
KalaDarAnbar d join Kala k
on
d.CodeKala = k.Code
where
(@codeKalaAz='0' or k.codeKala>=@codeKalaAz) and
(@codeKalaEla='0' or k.codeKala<=@codeKalaEla) and
(@GroohKalaAz=0 or k.CodeGrooh>=@GroohKalaAz) and
(@GroohKalaEla=0 or k.CodeGrooh<=@GroohKalaEla) and
(@AnbarAz=0 or d.CodeAnbar>=@AnbarAz) and
(@AnbarEla=0 or d.CodeAnbar<=@AnbarEla)
union all
------------------------------------------------------------ برگه
select
0[Radif],
m.code[Code],
m.codeBargeh[CodeBargeh],
m.Tarikh[Tarikh],
case m.noe
when 0 then cast( 10 as smallint) -- Foroosh
when 1 then cast( 2 as smallint) -- BargashtAzForoosh
when 3 then cast( 1 as smallint) -- Kharid
when 4 then cast( 11 as smallint) -- BargashtAzKharid
when 2 then cast( 13 as smallint) -- PishFactororoosh
end [Noe],
m.CodeMoshtari[CodeTafsili],
t.codeHesab[CodeHesabTafsili],
isnull(t.CodeGroohAshkas,0)[CodeGoroohAshkhas],
t.name[NameTafsili],
case m.noe
when 0 then 'فاکتور فروش (' + cast ( m.CodeBargeh as nvarchar ) + ')'-- Foroosh
when 1 then 'فاکتور برگشت از فروش (' + cast ( m.CodeBargeh as nvarchar ) + ')' -- BargashtAzForoosh
when 3 then 'فاکتور خرید (' + cast ( m.CodeBargeh as nvarchar ) + ')' -- Kharid
when 4 then 'فاکتور برگشت از خرید (' + cast ( m.CodeBargeh as nvarchar ) + ')' -- BargashtAzKharid
when 2 then 'پیش فاکتور فروش (' + cast ( m.CodeBargeh as nvarchar ) + ')' -- PishFactorForoosh
end [Sharh],
m.codeKarbar[CodeKarbar],
m.vaziat[Vaziat],
d.codeAnbar[CodeAnbar],
d.codeKala[CodeKala],
k.CodeKala[CodeKalaNamayeshi],
k.CodeGrooh[CodeGorooh],
k.name[NameKala],
d.Tedad[Tedad],
d.mablagh[Mablagh],
d.GheimatBarghashtiBeAnbar[MablaghBargashtBeAnbar],
m.SaatSabt[SaatSabt],
case m.noe
when 0 then 0.0 -- Foroosh
when 1 then d.tedad -- BargashtAzForoosh
when 3 then d.tedad -- Kharid
when 4 then 0.0 -- BargashtAzKharid
when 2 then 0.0 -- PishFactorForoosh
end [MeghdarVaredeh],
case m.noe
when 0 then cast(d.tedad as decimal(19,4)) -- Foroosh
when 1 then 0.0 -- BargashtAzForoosh
when 3 then 0.0 -- Kharid
when 4 then cast(d.tedad as decimal(19,4)) -- BargashtAzKharid
when 2 then cast(d.tedad as decimal(19,4)) -- PishFactorForoosh
end [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
case m.noe
when 0 then 0.0 -- Foroosh
when 1 then d.GheimatBarghashtiBeAnbar -- BargashtAzForoosh
when 3 then d.mablagh -- Kharid
when 4 then 0.0 -- BargashtAzKharid
when 2 then 0.0 -- PishFactorForoosh
end [MablaghVaredeh],
0.0[MablaghSadereh],
case m.noe
when 0 then 0.0-- Foroosh
when 1 then cast(d.tedad * d.GheimatBarghashtiBeAnbar as decimal(19,4)) -- BargashtAzForoosh
when 3 then cast(d.tedad * d.mablagh as decimal(19,4)) -- Kharid
when 4 then 0.0 -- BargashtAzKharid
when 2 then 0.0 -- PishFactorForoosh
end [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
ISNULL(m.CodeProjeh,0) [CodeProjeh]
from
Bargeh m join BargehDetail d
on
m.code= d.codeBargeh
join Kala k
on
d.CodeKala = k.Code
join tafsili t
on
m.codeMoshtari=t.code
where
d.isKala = 1 and /*m.noe != 2 and*/ m.noe != 5 and
(@codeKalaAz='0' or k.codeKala>=@codeKalaAz) and
(@codeKalaEla='0' or k.codeKala<=@codeKalaEla) and
(@GroohKalaAz=0 or k.CodeGrooh>=@GroohKalaAz) and
(@GroohKalaEla=0 or k.CodeGrooh<=@GroohKalaEla) and
(@AnbarAz=0 or d.CodeAnbar>=@AnbarAz) and
(@AnbarEla=0 or d.CodeAnbar<=@AnbarEla) and
(@TafsiliAz=0 or t.codeHesab>=@TafsiliAz) and
(@TafsiliEla=0 or t.codeHesab<=@TafsiliEla) and
(@GroohShakhsAz=0 or t.CodeGroohAshkas>=@GroohShakhsAz) and
(@GroohShakhsEla=0 or t.CodeGroohAshkas<=@GroohShakhsEla) and
(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
(
m.vaziat not in
(
case @WithDeleteFactors
when 0 then 1 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
)and
(
m.noe not in
(
case @WithPishFactorForoosh
when 0 then 2
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
) and
(@CodeProjeh=0 or m.CodeProjeh = @CodeProjeh)
union all
------------------------------------------------------------ حواله انتقالی بین انبار وارده
select
0[Radif],
m.code[Code],
m.codeBargeh[CodeBargeh],
m.Tarikh[Tarikh],
cast( 8 as smallint) /*EnteghalBeynAnbarVoroodi*/ [Noe],
cast( 0 as bigint)[CodeTafsili],
0[CodeHesabTafsili],
0[CodeGoroohAshkhas],
''[NameTafsili],
'حواله انتقالی بین انبار وارده (' + cast ( m.CodeBargeh as nvarchar ) + ')',
m.codeKarbar[CodeKarbar],
m.vaziat[Vaziat],
d.codeAnbar[CodeAnbar],
d.codeKala[CodeKala],
k.CodeKala[CodeKalaNamayeshi],
k.CodeGrooh[CodeGorooh],
k.name[NameKala],
d.Tedad[Tedad],
0[Mablagh],
0[MablaghBargashtBeAnbar],
m.SaatSabt[SaatSabt],
cast(d.tedad as decimal(19,4)) [MeghdarVaredeh],
0.0 [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
0.0 [MablaghVaredeh],
0.0[MablaghSadereh],
0 [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
0 [CodeProjeh]
from
Bargeh m join BargehDetail d
on
m.code= d.codeBargeh
join Kala k
on
d.CodeKala = k.Code
where
d.isKala = 1 and m.noe in (8) /*HavalehEnteghaliBeinAnbar*/ and
(@codeKalaAz='0' or k.codeKala>=@codeKalaAz) and
(@codeKalaEla='0' or k.codeKala<=@codeKalaEla) and
(@GroohKalaAz=0 or k.CodeGrooh>=@GroohKalaAz) and
(@GroohKalaEla=0 or k.CodeGrooh<=@GroohKalaEla) and
(@AnbarAz=0 or d.CodeAnbar>=@AnbarAz) and
(@AnbarEla=0 or d.CodeAnbar<=@AnbarEla) and
(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
(
m.vaziat not in
(
case @WithDeleteFactors
when 0 then 1 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
)
union all
------------------------------------------------------------ حواله انتقالی بین انبار صادره
select
0[Radif],
m.code[Code],
m.codeBargeh[CodeBargeh],
m.Tarikh[Tarikh],
cast( 16 as smallint) /*EnteghalBeynAnbarKhorooji*/ [Noe],
cast( 0 as bigint)[CodeTafsili],
0[CodeHesabTafsili],
0[CodeGoroohAshkhas],
''[NameTafsili],
'حواله انتقالی بین انبار صادره (' + cast ( m.CodeBargeh as nvarchar ) + ')',
m.codeKarbar[CodeKarbar],
m.vaziat[Vaziat],
isnull(d.codeAnbarMaghsad , 0)[CodeAnbar],
d.codeKala[CodeKala],
k.CodeKala[CodeKalaNamayeshi],
k.CodeGrooh[CodeGorooh],
k.name[NameKala],
d.Tedad[Tedad],
0[Mablagh],
0[MablaghBargashtBeAnbar],
m.SaatSabt[SaatSabt],
0.0 [MeghdarVaredeh],
cast(d.tedad as decimal(19,4)) [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
0.0 [MablaghVaredeh],
0.0[MablaghSadereh],
0 [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
0 [CodeProjeh]
from
Bargeh m join BargehDetail d
on
m.code= d.codeBargeh
join Kala k
on
d.CodeKala = k.Code
where
d.isKala = 1 and m.noe in (8) /*HavalehEnteghaliBeinAnbar*/ and
(@codeKalaAz='0' or k.codeKala>=@codeKalaAz) and
(@codeKalaEla='0' or k.codeKala<=@codeKalaEla) and
(@GroohKalaAz=0 or k.CodeGrooh>=@GroohKalaAz) and
(@GroohKalaEla=0 or k.CodeGrooh<=@GroohKalaEla) and
(@AnbarAz=0 or d.CodeAnbar>=@AnbarAz) and
(@AnbarEla=0 or d.CodeAnbar<=@AnbarEla) and
(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
(
m.vaziat not in
(
case @WithDeleteFactors
when 0 then 1 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
)
union all
--------------------------------------------------- انبار گردانی
select
0[Radif],
m.code[Code],
isnull(m.codeBargeh,0)[CodeBargeh],
m.Tarikh[Tarikh],
case d.noe
when 0 then cast( 12 as smallint) -- Hazienh
when 1 then cast( 3 as smallint) -- Daramad
end [Noe],
d.CodeTafsili[CodeTafsili],
t.CodeHesab[CodeHesabTafsili],
isnull(t.CodeGroohAshkas,0)[CodeGoroohAshkhas],
t.name[NameTafsili],
case d.noe
when 0 then 'کسورات انبار گردانی (' + cast (m.code as nvarchar) + ')' -- Hazienh
when 1 then 'اضافات انبارگردانی (' + cast (m.code as nvarchar) + ')' -- Daramad
end [Sharh],
m.codeKarbar[CodeKarbar],
m.vaziat[Vaziat],
d.codeAnbar[CodeAnbar],
d.codeKala[CodeKala],
k.CodeKala[CodeKalaNamayeshi],
k.CodeGrooh[CodeGorooh],
k.name[NameKala],
d.Tedad[Tedad],
d.mablagh[Mablagh],
0.0[MablaghBargashtBeAnbar],
m.SaatSabt[SaatSabt],
case d.noe
when 0 then 0.0 -- Hazienh
when 1 then cast(d.tedad as decimal(19,4)) -- Daramad
end [MeghdarVaredeh],
case d.noe
when 0 then cast(d.tedad as decimal(19,4))-- Hazienh
when 1 then 0.0 -- Daramad
end [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
case d.noe
when 0 then 0.0 -- Hazienh
when 1 then d.mablagh -- Daramad
end [MablaghVaredeh],
0.0[MablaghSadereh],
case d.noe
when 0 then 0.0 -- Hazienh
when 1 then cast(d.tedad * d.mablagh as decimal(19,4)) -- Daramad
end [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
0 [CodeProjeh]
from AnbarGardani m
join AnbarGardaniDetail d on m.code = d.codeAnbarGardani
join tafsili t on t.code=d.codeTafsili
join kala k on k.code=d.codeKala
where
m.vaziat != 1 and
(@codeKalaAz='0' or k.codeKala>=@codeKalaAz) and
(@codeKalaEla='0' or k.codeKala<=@codeKalaEla) and
(@GroohKalaAz=0 or k.CodeGrooh>=@GroohKalaAz) and
(@GroohKalaEla=0 or k.CodeGrooh<=@GroohKalaEla) and
(@AnbarAz=0 or d.CodeAnbar>=@AnbarAz) and
(@AnbarEla=0 or d.CodeAnbar<=@AnbarEla) and
(@TafsiliAz=0 or t.codeHesab>=@TafsiliAz) and
(@TafsiliEla=0 or t.codeHesab<=@TafsiliEla) and
(@GroohShakhsAz=0 or t.CodeGroohAshkas>=@GroohShakhsAz) and
(@GroohShakhsEla=0 or t.CodeGroohAshkas<=@GroohShakhsEla) and
(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
(@tarikhEla=0 or m.tarikh<=@tarikhEla)and
(
m.vaziat not in
(
case @WithDeleteFactors
when 0 then 1 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
)
union all
-------------------------------------------------- تولید
select
0[Radif],
t.code[Code],
t.CodeTolid[CodeBargeh],
t.tarikh[Tarikh],
cast( 4 as smallint)[Noe], -- TolidKalaTolidi
cast( 0 as bigint)[CodeTafsili],
0[CodeHesabTafsili],
0 [CodeGoroohAshkhas],
'--'[NameTafsili],
'تولید کالا (' + cast(t.codeTolid as nvarchar) + ')'[Sharh],
t.codeKarbar[CodeKarbar],
t.Vaziat[Vaziat],
t.CodeAnbar[CodeAnbar],
t.codeKala[CodeKala],
k.CodeKala[CodeKalaNamayeshi],
k.CodeGrooh[CodeGorooh],
k.name[NameKala],
t.tedad[Tedad],
t.GheimatTamamShodeh [Mablagh],
0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش
0[SaatSabt],
t.tedad[MeghdarVaredeh],
0.0[MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.code,k.codevahed,t.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
t.GheimatTamamShodeh [MablaghVaredeh],
0.0[MablaghSadereh],
cast(t.tedad * t.GheimatTamamShodeh as decimal(19,4) )[MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
0 [CodeProjeh]
from
Tolid t join Kala k
on
t.CodeKala = k.Code
where
t.vaziat != 1 and
t.noe = 0 and -- Tolid
(@codeKalaAz = '0' or k.codeKala >= @codeKalaAz) and
(@codeKalaEla = '0' or k.codeKala <= @codeKalaEla) and
(@GroohKalaAz = 0 or k.CodeGrooh >= @GroohKalaAz) and
(@GroohKalaEla = 0 or k.CodeGrooh <= @GroohKalaEla) and
(@AnbarAz = 0 or t.CodeAnbar >= @AnbarAz) and
(@AnbarEla = 0 or t.CodeAnbar <= @AnbarEla) and
(@tarikhAz = 0 or t.tarikh >= @tarikhAz) and
(@tarikhEla = 0 or t.tarikh <= @tarikhEla) and
(
t.vaziat not in
(
case @WithDeleteFactors
when 0 then 1 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
)
--------------------------------------------------
union all
--------------------------------------------------
select
0[Radif],
m.code[Code],
m.CodeTolid[CodeBargeh],
m.tarikh[Tarikh],
case d.noe
when 0 then cast( 5 as smallint) -- TolidZayeaat
when 1 then cast( 14 as smallint) -- TolidKalaMasrafi
end [Noe],
cast( 0 as bigint)[CodeTafsili],
0[CodeHesabTafsili],
0 [CodeGoroohAshkhas],
'--'[NameTafsili],
case d.noe
when 0 then 'تولید ضایعات ('+ cast(m.codeTolid as nvarchar) + ')' -- TolidZayeaat
when 1 then 'کالای مصرفی تولید ('+ cast(m.codeTolid as nvarchar) + ')' -- TolidKalaMasrafi
end [Sharh],
m.codeKarbar[CodeKarbar],
m.Vaziat[Vaziat],
d.CodeAnbar[CodeAnbar],
d.codeKala[CodeKala],
k.CodeKala[CodeKalaNamayeshi],
k.CodeGrooh[CodeGorooh],
k.name[NameKala],
d.tedad * m.tedad[Tedad],
d.mablagh [Mablagh],
0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش
0[SaatSabt],
case d.noe
when 0 then cast(d.tedad * m.tedad as decimal(19,4)) -- TolidZayeaat
when 1 then 0.0 -- TolidKalaMasrafi
end [MeghdarVaredeh],
case d.noe
when 0 then 0.0 -- TolidZayeaat
when 1 then cast(d.tedad * m.tedad as decimal(19,4))-- TolidKalaMasrafi
end [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad * m.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
case d.noe
when 0 then d.mablagh -- TolidZayeaat
when 1 then 0.0 -- TolidKalaMasrafi
end [MablaghVaredeh],
0.0[MablaghSadereh],
case d.noe
when 0 then cast(d.tedad * d.mablagh as decimal(19,4) ) -- TolidZayeaat
when 1 then 0.0 -- TolidKalaMasrafi
end [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
0 [CodeProjeh]
from
Tolid m join TolidDetail d
on
m.code = d.CodeTolid
join Kala k
on
d.CodeKala = k.Code
where
m.vaziat != 1 and
m.noe = 0 and -- Tolid
(@codeKalaAz = '0' or k.codeKala >= @codeKalaAz) and
(@codeKalaEla = '0' or k.codeKala <= @codeKalaEla) and
(@GroohKalaAz = 0 or k.CodeGrooh >= @GroohKalaAz) and
(@GroohKalaEla = 0 or k.CodeGrooh <= @GroohKalaEla) and
(@AnbarAz = 0 or d.CodeAnbar >= @AnbarAz) and
(@AnbarEla = 0 or d.CodeAnbar <= @AnbarEla) and
(@tarikhAz = 0 or m.tarikh >= @tarikhAz) and
(@tarikhEla = 0 or m.tarikh <= @tarikhEla) and
(
m.vaziat not in
(
case @WithDeleteFactors
when 0 then 1 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
)
--------------------------------------------------
union all
-------------------------------------------------- تبدیل
select -- select * from TolidDetail
0[Radif],
t.code[Code],
t.CodeTolid[CodeBargeh],
t.tarikh[Tarikh],
cast( 15 as smallint)[Noe], -- TabdilKalaMasrafi
cast( 0 as bigint)[CodeTafsili],
0[CodeHesabTafsili],
0 [CodeGoroohAshkhas],
'--'[NameTafsili],
'تبدیل کالا (' + cast(t.codeTolid as nvarchar) + ')'[Sharh],
t.codeKarbar[CodeKarbar],
t.Vaziat[Vaziat],
t.CodeAnbar[CodeAnbar],
t.codeKala[CodeKala],
k.CodeKala[CodeKalaNamayeshi],
k.CodeGrooh[CodeGorooh],
k.name[NameKala],
t.tedad[Tedad],
t.GheimatTamamShodeh [Mablagh],
0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش
0[SaatSabt],
0.0[MeghdarVaredeh],
cast(t.tedad as decimal(19,4)) [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.code,k.codevahed,t.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
0.0 [MablaghVaredeh],
0.0 [MablaghSadereh],
0.0 [MablaghJam],
0.0 [MablaghMiangin],
0.0 [MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
0 [CodeProjeh]
from
Tolid t join Kala k on t.CodeKala = k.Code
where
t.vaziat != 1 and
t.noe = 1 and -- Tabdil
(@codeKalaAz = '0' or k.codeKala >= @codeKalaAz) and
(@codeKalaEla = '0' or k.codeKala <= @codeKalaEla) and
(@GroohKalaAz = 0 or k.CodeGrooh >= @GroohKalaAz) and
(@GroohKalaEla = 0 or k.CodeGrooh <= @GroohKalaEla) and
(@AnbarAz = 0 or t.CodeAnbar >= @AnbarAz) and
(@AnbarEla = 0 or t.CodeAnbar <= @AnbarEla) and
(@tarikhAz = 0 or t.tarikh >= @tarikhAz) and
(@tarikhEla = 0 or t.tarikh <= @tarikhEla) and
(
t.vaziat not in
(
case @WithDeleteFactors
when 0 then 1 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
)
--------------------------------------------------
union all
--------------------------------------------------
select
0[Radif],
m.code[Code],
m.CodeTolid[CodeBargeh],
m.tarikh[Tarikh],
case d.noe
when 0 then cast( 7 as smallint) -- TabdilZayeaat
when 1 then cast( 15 as smallint) -- TabdilKalaMasrafi
end [Noe],
cast( 0 as bigint)[CodeTafsili],
0[CodeHesabTafsili],
0 [CodeGoroohAshkhas],
'--'[NameTafsili],
case d.noe
when 0 then 'تبدیل ، کالای بوجود آمده ('+ cast(m.codeTolid as nvarchar) + ')' -- TabdilZayeaat
when 1 then 'کالای مصرفی تبدیل ('+ cast(m.codeTolid as nvarchar) + ')' -- TabdilKalaMasrafi
end [Sharh],
m.codeKarbar[CodeKarbar],
m.Vaziat[Vaziat],
d.CodeAnbar[CodeAnbar],
d.codeKala[CodeKala],
k.CodeKala[CodeKalaNamayeshi],
k.CodeGrooh[CodeGorooh],
k.name[NameKala],
d.tedad * m.tedad[Tedad],
d.mablagh [Mablagh],
0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش
0[SaatSabt],
case d.noe
when 0 then cast(d.tedad * m.tedad as decimal(19,4)) -- TabdilZayeaat
when 1 then 0.0 -- TabdilKalaMasrafi
end [MeghdarVaredeh],
case d.noe
when 0 then 0.0 -- TabdilZayeaat
when 1 then cast(d.tedad * m.tedad as decimal(19,4))-- TabdilKalaMasrafi
end [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad * m.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
case d.noe
when 0 then d.mablagh -- TabdilZayeaat
when 1 then 0.0 -- TabdilKalaMasrafi
end [MablaghVaredeh],
0.0[MablaghSadereh],
case d.noe
when 0 then cast(d.tedad * d.mablagh as decimal(19,4) ) -- TabdilZayeaat
when 1 then 0.0 -- TabdilKalaMasrafi
end [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
0 [CodeProjeh]
from
Tolid m join TolidDetail d
on
m.code = d.CodeTolid
join Kala k
on
d.CodeKala = k.Code
where
m.vaziat != 1 and
m.noe = 1 and -- Tabdil
(@codeKalaAz = '0' or k.codeKala >= @codeKalaAz) and
(@codeKalaEla = '0' or k.codeKala <= @codeKalaEla) and
(@GroohKalaAz = 0 or k.CodeGrooh >= @GroohKalaAz) and
(@GroohKalaEla = 0 or k.CodeGrooh <= @GroohKalaEla) and
(@AnbarAz = 0 or d.CodeAnbar >= @AnbarAz) and
(@AnbarEla = 0 or d.CodeAnbar <= @AnbarEla) and
(@tarikhAz = 0 or m.tarikh >= @tarikhAz) and
(@tarikhEla = 0 or m.tarikh <= @tarikhEla) and
(
m.vaziat not in
(
case @WithDeleteFactors
when 0 then 1 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
)
union all
-------------------------------------------------- مصرف کالا و تولید کالا
select
0[Radif],
m.code [Code],
m.CodeBargeh [CodeBargeh],
m.tarikh [Tarikh],
case m.noe
when 0 then cast( 17 as smallint) -- useKala
when 1 then cast( 9 as smallint) -- ProuductKala
end [Noe],
m.CodeTafsili [CodeTafsili],
t.codeHesab [CodeHesabTafsili],
0 [CodeGoroohAshkhas],
t.name [NameTafsili],
case m.noe
when 0 then 'کالای مصرفی ('+ cast(m.codeBargeh as nvarchar) + ')' -- UseKala
when 1 then 'کالای تولیدی ('+ cast(m.codeBargeh as nvarchar) + ')' -- ProuductKala
end [Sharh],
m.codeKarbar[CodeKarbar],
m.Vaziat[Vaziat],
d.CodeAnbar[CodeAnbar],
d.codeKala[CodeKala],
k.CodeKala[CodeKalaNamayeshi],
k.CodeGrooh[CodeGorooh],
k.name[NameKala],
d.tedad [Tedad],
d.mablagh [Mablagh],
0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش
0[SaatSabt],
case m.noe
when 0 then 0.0 -- UseKala
when 1 then cast(d.tedad as decimal(19,4)) -- ProuductKala
end [MeghdarVaredeh],
case m.noe
when 0 then cast(d.tedad as decimal(19,4)) -- UseKala
when 1 then 0.0
end [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad ,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
case m.noe
when 0 then 0.0 -- UseKala
when 1 then d.mablagh -- ProuductKala
end [MablaghVaredeh],
0.0[MablaghSadereh],
cast(d.tedad * d.mablagh as decimal(19,4)) [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
ISNULL(m.CodeProjeh , 0) [CodeProjeh]
from UseProductionKala m
join UseProductionKalaDetail d on m.code = d.CodeBargeh
join Kala k on d.CodeKala = k.Code
join tafsili t on t.code= m.codeTafsili
where
m.vaziat != 1 and
(@codeKalaAz = '0' or k.codeKala >= @codeKalaAz) and
(@codeKalaEla = '0' or k.codeKala <= @codeKalaEla) and
(@GroohKalaAz = 0 or k.CodeGrooh >= @GroohKalaAz) and
(@GroohKalaEla = 0 or k.CodeGrooh <= @GroohKalaEla) and
(@AnbarAz = 0 or d.CodeAnbar >= @AnbarAz) and
(@AnbarEla = 0 or d.CodeAnbar <= @AnbarEla) and
(@tarikhAz = 0 or m.tarikh >= @tarikhAz) and
(@tarikhEla = 0 or m.tarikh <= @tarikhEla) and
(
m.vaziat not in
(
case @WithDeleteFactors
when 0 then 1 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
) and
(@CodeProjeh=0 or m.CodeProjeh = @CodeProjeh)
order by CodeKala , Noe , Tarikh
end
GO