-- prcKartexAnbar 0,0,0,0,'0','0',0,0,0,0,0,0,0,0,0,0,33,0,'','','',0,0,'','','','','',''
alter proc dbo.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
@TimeAz int=0,
@TimeEla int=0,
@Fc_ID int=0,
@Pro_ID_VahedMali int=0,
@strType nvarchar(max)='',
@strKala nvarchar(max)='' ,
@strRestorationCode nvarchar(max)='',
@factorCodeAz int=0,
@factorCodeEla int=0,
@strProIdColors nvarchar(max)='',
@strProIdSizes nvarchar(max)='',
@strConIds nvarchar(max)='',
@strPpIds nvarchar(max)='',
@strPpiIds nvarchar(max)='',
@strBoothIds nvarchar(max)=''
as
begin
----------------------------------- موجودی اول دوره
select
0[Radif],
d.Fc_ID [FC_ID],
d.Pro_ID_VahedMali [Pro_ID_VahedMali],
d.ID[ID],
0[Bar_Code],
d.tarikh[Tarikh],
0[Saat],
cast( 213 as smallint)[Noe],
cast( 0 as bigint)[Taf_ID],
0[Taf_Code],
0 [Pro_ID_GroohAshkhas],
'--'[Taf_Name],
'موجودی اول دوره'[Sharh],
0[Kar_ID],
cast(100 as smallint) [Vaziat],
d.Pro_ID_Anbar [Anb_ID],
d.Kal_ID [Kal_ID],
k.Code [Kal_Code],
k.TechnicalCode [TechnicalCode],
k.Pro_ID_Vahed [Pro_ID_Unit],
k.Pro_ID_Grooh [Pro_ID_GroohKala],
k.name[Kal_Name],
d.Dom_ID_RestorationCode [Dom_ID_Restoration],
isnull(d.Pro_ID_Color,0) [Pro_ID_Color],
isnull(d.Pro_ID_Size,0) [Pro_ID_Size],
d.MojoodiAvalDoreh[Tedad],
d.GheymatAvalDoreh[Mablagh],
0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش
0[SaatSabt],
d.MojoodiAvalDoreh[MeghdarVaredeh],
0.0[MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.ID,k.Pro_ID_Vahed,d.mojoodiAvalDoreh,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,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],
cast(1 as bit) [IsEntry],
0 [BD_ID],
0 [Con_ID],
0 [PP_ID],
0 [PPI_ID],
0 [Booth_ID]
from
KalaDarAnbar d
join Kala k on d.Kal_ID = k.ID
where
(@Fc_ID=0 or d.Fc_ID = @Fc_ID) and
(@Pro_ID_VahedMali=0 or d.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
(cast(@codeKalaAz as int)='0' or k.Code >=cast(@codeKalaAz as int)) and
(cast(@codeKalaEla as int)='0' or k.Code <=cast(@codeKalaEla as int)) and
(@GroohKalaAz=0 or k.Pro_ID_Grooh>=@GroohKalaAz) and
(@GroohKalaEla=0 or k.Pro_ID_Grooh<=@GroohKalaEla) and
(@AnbarAz=0 or d.Pro_ID_Anbar>=@AnbarAz) and
(@AnbarEla=0 or d.Pro_ID_Anbar<=@AnbarEla) and
(@Fc_ID=0 or d.Fc_ID = @Fc_ID) and
(@Pro_ID_VahedMali=0 or d.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
(@strType='' or 213 in (select * from dbo.CastStringToTable(@strType))) and
(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and
(@factorCodeAz = 0 and @factorCodeEla = 0)and
(@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and
(@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
(@strConIds = '') and
(@strPpIds = '') and
(@strPpiIds = '') and
(@strBoothIds = '')
union all
------------------------------------------------------------ برگه
select
0[Radif],
m.Fc_ID [FC_ID],
m.Pro_ID_VahedMali [Pro_ID_VahedMali],
m.ID[ID] ,
m.Code [Bar_Code],
m.Tarikh[Tarikh],
m.Saat [Saat] ,
case m.Dom_ID_Noe
when 28 then cast( 223 as smallint) -- Foroosh
when 29 then cast( 215 as smallint) -- BargashtAzForoosh
when 31 then cast( 214 as smallint) -- Kharid
when 32 then cast( 224 as smallint) -- BargashtAzKharid
when 30 then cast( 226 as smallint) -- PishFactororoosh
when 791 then cast( 793 as smallint) -- اضافات انبارگرداني
when 792 then cast( 794 as smallint) -- کسورات انبارگرداني
when 1162 then cast( 1166 /*TrustedGoodsPurchase*/as smallint) -- خرید کالای امانی
when 1163 then cast( 1167 /*TrustedGoodsSale*/as smallint) -- فروش کالای امانی
when 1190 then cast( 1196 /*PlayStationFactor*/as smallint) -- فروش خدمات ایستگاهی
when 1275 then cast( 1279 /*ProjectDeductionFactor*/as smallint) -- فاکتور مصرفی پروژه
when 1276 then cast( 1280 /*ProjectAdditionProject*/as smallint) -- فاکتور مانده پروژه
end [Noe],
m.Taf_ID_Moshtari[Taf_ID],
t.Code [Taf_Code],
--isnull(t.Pro_ID,0)[Pro_ID_GroohAshkhas],
0 [Pro_ID_GroohAshkhas],
t.Title[Taf_Name],
case m.Dom_ID_noe
when 28 then 'فاکتور فروش ('
when 29 then 'فاکتور برگشت از فروش ('
when 31 then 'فاکتور خرید ('
when 32 then 'فاکتور برگشت از خرید ('
when 30 then 'پیش فاکتور فروش ('
when 791 then 'اضافات انبار ('
when 792 then 'کسورات انبار ('
when 1162 then 'خرید کالای امانی ('
when 1163 then 'فروش کالای امانی ('
when 1190 then 'فروش خدمات ایستگاهی ('
when 1275 then 'فاکتور مصرفی پروژه ('
when 1276 then 'فاکتور مانده پروژه ('
end + cast ( m.Code as nvarchar ) + ')' +(case when d.Dom_ID_Noe = 1871 then ' اشانتیون' else '' end)[Sharh],
m.Kar_ID [Kar_ID],
m.Dom_ID_Vaziat [Vaziat],
d.Pro_ID_Anbar [Anb_ID],
d.Kal_ID [Kal_ID],
k.Code [Kal_Code],
k.TechnicalCode [TechnicalCode],
k.Pro_ID_Vahed [Pro_ID_Unit],
k.Pro_ID_Grooh [Pro_ID_GroohKala],
k.name[Kal_Name],
d.Dom_ID_Restoration,
isnull(d.Pro_ID_Color,0),
isnull(d.Pro_ID_Size,0),
d.Tedad[Tedad],
cast((d.mablagh + isnull(d.MablaghSupplement,0.0)-isnull((d.MablagheTakhfif/d.tedad),0.0))as decimal(19,4))[Mablagh],
cast((isnull(d.GheimatBarghashtiBeAnbar,0.0) + isnull(d.MablaghSupplement,0.0)-isnull((d.MablagheTakhfif/d.tedad),0.0))as decimal(19,4)) [MablaghBargashtBeAnbar],
m.SaatSabt[SaatSabt],
case m.Dom_ID_noe
when 28 then 0.0 -- Foroosh
when 29 then d.tedad -- BargashtAzForoosh
when 31 then d.tedad -- Kharid
when 32 then 0.0 -- BargashtAzKharid
when 30 then 0.0 -- PishFactorForoosh
when 791 then d.Tedad -- اضافات انبارگرداني
when 792 then 0.0 -- کسورات انبارگرداني
when 1162 then d.Tedad -- خرید کالای امانی
when 1275 then 0.0 -- فاکتور مصرفی پروژه
when 1276 then d.Tedad -- فاکتور مانده پروژه
end [MeghdarVaredeh],
case m.Dom_ID_noe
when 28 then cast(d.tedad as decimal(19,4)) -- Foroosh
when 29 then 0.0 -- BargashtAzForoosh
when 31 then 0.0 -- Kharid
when 32 then cast(d.tedad as decimal(19,4)) -- BargashtAzKharid
when 30 then cast(d.tedad as decimal(19,4)) -- PishFactorForoosh
when 791 then 0.0 -- اضافات انبارگرداني
when 792 then cast(d.tedad as decimal(19,4)) -- کسورات انبارگرداني
when 1163 then cast(d.tedad as decimal(19,4)) -- فروش کالای امانی
when 1190 then cast(d.tedad as decimal(19,4)) -- فروش خدمات ایستگاهی
when 1275 then cast(d.tedad as decimal(19,4)) -- فاکتور مصرفی پروژه
when 1276 then 0.0 -- فاکتور مانده پروژه
end [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.ID,k.Pro_ID_Vahed,d.tedad,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
cast((case when m.Dom_ID_noe in (29/*BargashtAzForoosh*/,31/*Kharid*/,791/*اضافات انبارگرداني*/,1162/*خرید کالای امانی*/ , 1276 /*فاکتور مانده پروژه*/) then d.mablagh+isnull(d.MablaghSupplement,0.0)-isnull((d.MablagheTakhfif/d.tedad),0.0) else 0.0 end) as decimal(19,4)) [MablaghVaredeh],
0.0[MablaghSadereh],
case when m.Dom_ID_noe in (29,31,791,1162,1276) then cast(d.tedad * (d.mablagh+isnull(d.MablaghSupplement,0.0)-isnull((d.MablagheTakhfif/d.tedad),0.0)) as decimal(19,4)) else 0.0 end [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
cast(case when m.Dom_ID_noe in (29,31,791,1162/*TrustedGoodsPurchase*/,1276 /*فاکتور مانده پروژه*/) then 1 else 0 end as bit) [IsEntry],
ISNULL(d.BD_ID,0) [BD_ID],
ISNULL(m.Con_ID,0) [Con_ID],
ISNULL(m.PP_ID,0) [PP_ID],
ISNULL(m.PPI_ID,0) [PPI_ID],
ISNULL(d.Booth_ID,0) [Booth_ID]
from Bargeh m
join BargehDetail d on m.ID= d.Bar_ID
join Kala k on d.Kal_ID = k.ID
join tafsili t on m.Taf_ID_Moshtari=t.ID
where
d.Dom_ID_Noe in (666,1871) and m.Dom_ID_noe not in (33 /*PishFactorKharid*/,1158 /*TrustedGoodsDaryafti*/,1159/*TrustedGoodsPardakhti*/) and
(cast(@codeKalaAz as int)='0' or k.Code >=cast(@codeKalaAz as int)) and
(cast(@codeKalaEla as int)='0' or k.Code <=cast(@codeKalaEla as int)) and
(@GroohKalaAz=0 or k.Pro_ID_Grooh>=@GroohKalaAz) and
(@GroohKalaEla=0 or k.Pro_ID_Grooh<=@GroohKalaEla) and
(@AnbarAz=0 or d.Pro_ID_Anbar>=@AnbarAz) and
(@AnbarEla=0 or d.Pro_ID_Anbar<=@AnbarEla) and
(@TafsiliAz=0 or t.Code >=@TafsiliAz) and
(@TafsiliEla=0 or t.Code <=@TafsiliEla) and
(@Fc_ID=0 or m.Fc_ID = @Fc_ID) and
(@Pro_ID_VahedMali=0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
(@TimeAz=0 or m.saat>=@TimeAz) and
(@TimeEla=0 or m.saat<=@TimeEla) and
(@factorCodeAz=0 or m.Code<=@factorCodeAz) and
(@factorCodeEla=0 or m.Code<=@factorCodeEla) and
(
@strType='' or
(
case m.Dom_ID_Noe
when 28 then cast( 223 as int) -- Foroosh
when 29 then cast( 215 as int) -- BargashtAzForoosh
when 31 then cast( 214 as int) -- Kharid
when 32 then cast( 224 as int) -- BargashtAzKharid
when 30 then cast( 226 as int) -- PishFactororoosh
when 791 then cast( 793 as int) -- اضافات انبارگرداني
when 792 then cast( 794 as int) -- کسورات انبارگرداني
when 1162 then cast( 1166 as int) -- خرید کالای امانی
when 1163 then cast( 1167 as int) -- فروش کالای امانی
when 1190 then cast( 1196 as int) -- فاکتور بازی
when 1275 then cast( 1279 as int) -- فاکتور مصرفی پروژه
when 1276 then cast( 1280 as int) -- فاکتور مانده پروژه
end in (select * from dbo.CastStringToTable(@strType))
)
)and
(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and
(
m.Dom_ID_vaziat not in
(
case @WithDeleteFactors
when 0 then 101 -- deleted
when 1 then 20000000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
)and
(
m.Dom_ID_noe not in
(
case @WithPishFactorForoosh
when 0 then 30
when 1 then 20000000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
) and
(@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and
(@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
(@strConIds = '' or m.Con_ID in (select * from dbo.CastStringToTable(@strConIds))) and
(@strPpIds = '' or m.PP_ID in (select * from dbo.CastStringToTable(@strPpIds))) and
(@strPpiIds = '' or m.PPI_ID in (select * from dbo.CastStringToTable(@strPpiIds))) and
(@strBoothIds = '' or d.Booth_ID in (select * from dbo.CastStringToTable(@strBoothIds)))
union all
------------------------------------------------------------ حواله انتقالی بین انبار صادره
select
0[Radif],
m.Fc_ID [FC_ID],
isnull(d.Pro_ID_VahedMali,0) [Pro_ID_VahedMali],
m.ID [ID],
m.Code [Bar_Code],
m.Tarikh[Tarikh],
m.Saat [Saat],
cast( 229 as smallint) /*EnteghalBeynAnbarKhorooji*/ [Noe],
cast( 0 as bigint)[Taf_ID],
0[Taf_Code],
0[Pro_ID_GroohAshkhas],
''[Taf_Name],
'حواله انتقالی بین انبار صادره (' + cast ( m.Code as nvarchar ) + ')',
m.Kar_ID [Kar_ID],
m.Dom_ID_Vaziat [Vaziat],
d.Pro_ID_Anbar [Anb_ID],
d.Kal_ID [Kal_ID],
k.Code [Kal_Code],
k.TechnicalCode [TechnicalCode],
k.Pro_ID_Vahed [Pro_ID_Unit],
k.Pro_ID_Grooh [Pro_ID_GroohKala],
k.name[Kal_Name],
d.Dom_ID_Restoration,
isnull(d.Pro_ID_Color,0),
isnull(d.Pro_ID_Size,0),
d.Tedad[Tedad],
d.Mablagh [Mablagh],
0[MablaghBargashtBeAnbar],
m.SaatSabt[SaatSabt],
0.0 [MeghdarVaredeh],
cast(d.tedad as decimal(19,4)) [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.ID,k.Pro_ID_Vahed,d.tedad,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,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],
cast(0 as bit) [IsEntry],
0 [BD_ID],
ISNULL(m.Con_ID,0) [Con_ID],
ISNULL(m.PP_ID,0) [PP_ID],
ISNULL(m.PPI_ID,0) [PPI_ID],
ISNULL(d.Booth_ID,0) [Booth_ID]
from Bargeh m
join BargehDetail d on m.ID= d.Bar_ID
join Kala k on d.Kal_ID = k.ID
where
d.Dom_ID_Noe = 666 and
m.Dom_ID_Noe in (36) /*HavalehEnteghaliBeinAnbar*/ and
(cast(@codeKalaAz as int)='0' or k.Code >=cast(@codeKalaAz as int)) and
(cast(@codeKalaEla as int)='0' or k.Code <=cast(@codeKalaEla as int)) and
(@GroohKalaAz=0 or k.Pro_ID_Grooh >=@GroohKalaAz) and
(@GroohKalaEla=0 or k.Pro_ID_Grooh <=@GroohKalaEla) and
(@AnbarAz=0 or d.Pro_ID_Anbar >=@AnbarAz) and
(@AnbarEla=0 or d.Pro_ID_Anbar <=@AnbarEla) and
(@Fc_ID=0 or m.Fc_ID = @Fc_ID) and
(@Pro_ID_VahedMali=0 or d.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
(@TimeAz=0 or m.saat>=@TimeAz) and
(@TimeEla=0 or m.saat<=@TimeEla) and
(@factorCodeAz=0 or m.Code<=@factorCodeAz) and
(@factorCodeEla=0 or m.Code<=@factorCodeEla) and
(
@strType='' or (229 /*EnteghalBeynAnbarKhorooji*/ in (select * from dbo.CastStringToTable(@strType)))
)and
(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and
(
m.Dom_ID_vaziat not in
(
case @WithDeleteFactors
when 0 then 101 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
) and
(@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and
(@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
(@strConIds = '' or m.Con_ID in (select * from dbo.CastStringToTable(@strConIds))) and
(@strPpIds = '' or m.PP_ID in (select * from dbo.CastStringToTable(@strPpIds))) and
(@strPpiIds = '' or m.PPI_ID in (select * from dbo.CastStringToTable(@strPpiIds))) and
(@strBoothIds = '' or d.Booth_ID in (select * from dbo.CastStringToTable(@strBoothIds)))
union all
------------------------------------------------------------ حواله انتقالی بین انبار وارده
select
0[Radif],
m.Fc_ID [FC_ID],
isnull(d.Pro_ID_VahedMaliMaghsad,0) [Pro_ID_VahedMali],
m.ID [ID],
m.Code [Bar_Code],
m.Tarikh[Tarikh],
m.Saat [Saat],
cast( 221 as smallint) /*EnteghalBeynAnbarVoroodi*/ [Noe],
cast( 0 as bigint)[Taf_ID],
0[Taf_Code],
0[Pro_ID_GroohAshkhas],
''[Taf_Name],
'حواله انتقالی بین انبار وارده (' + cast ( m.Code as nvarchar ) + ')',
m.Kar_ID [Kar_ID],
m.Dom_ID_Vaziat [Vaziat],
isnull(d.Pro_ID_AnbarMaghsad , 0)[Anb_ID],
d.Kal_ID [Kal_ID],
k.Code [Kal_Code],
k.TechnicalCode [TechnicalCode],
k.Pro_ID_Vahed [Pro_ID_Unit],
k.Pro_ID_Grooh [Pro_ID_GroohKala],
k.name[Kal_Name],
d.Dom_ID_Restoration,
isnull(d.Pro_ID_Color,0),
isnull(d.Pro_ID_Size,0),
d.Tedad[Tedad],
d.Mablagh [Mablagh],
0[MablaghBargashtBeAnbar],
m.SaatSabt[SaatSabt],
cast(d.tedad as decimal(19,4)) [MeghdarVaredeh],
0.0 [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.ID,k.Pro_ID_Vahed,d.tedad,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,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],
cast(1 as bit) [IsEntry],
0 [BD_ID] ,
ISNULL(m.Con_ID,0) [Con_ID],
ISNULL(m.PP_ID,0) [PP_ID],
ISNULL(m.PPI_ID,0) [PPI_ID],
ISNULL(d.Booth_ID,0) [Booth_ID]
from Bargeh m
join BargehDetail d on m.ID= d.Bar_ID
join Kala k on d.Kal_ID = k.ID
where
d.Dom_ID_Noe = 666 and m.Dom_ID_noe in (36) /*HavalehEnteghaliBeinAnbar*/ and
(cast(@codeKalaAz as int)='0' or k.Code >=cast(@codeKalaAz as int)) and
(cast(@codeKalaEla as int)='0' or k.Code <=cast(@codeKalaEla as int)) and
(@GroohKalaAz=0 or k.Pro_ID_Grooh >=@GroohKalaAz) and
(@GroohKalaEla=0 or k.Pro_ID_Grooh <=@GroohKalaEla) and
(@AnbarAz=0 or d.Pro_ID_AnbarMaghsad >=@AnbarAz) and
(@AnbarEla=0 or d.Pro_ID_AnbarMaghsad <=@AnbarEla) and
(@Fc_ID=0 or m.Fc_ID = @Fc_ID) and
(@Pro_ID_VahedMali=0 or d.Pro_ID_VahedMaliMaghsad = @Pro_ID_VahedMali) and
(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
(@TimeAz=0 or m.saat>=@TimeAz) and
(@TimeEla=0 or m.saat<=@TimeEla) and
(@factorCodeAz=0 or m.Code<=@factorCodeAz) and
(@factorCodeEla=0 or m.Code<=@factorCodeEla) and
(
@strType='' or (221 /*EnteghalBeynAnbarVoroodi*/ in (select * from dbo.CastStringToTable(@strType)))
)and
(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and
(
m.Dom_ID_vaziat not in
(
case @WithDeleteFactors
when 0 then 101 -- deleted
when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
end
)
) and
(@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and
(@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
(@strConIds = '' or m.Con_ID in (select * from dbo.CastStringToTable(@strConIds))) and
(@strPpIds = '' or m.PP_ID in (select * from dbo.CastStringToTable(@strPpIds))) and
(@strPpiIds = '' or m.PPI_ID in (select * from dbo.CastStringToTable(@strPpiIds))) and
(@strBoothIds = '' or d.Booth_ID in (select * from dbo.CastStringToTable(@strBoothIds)))
union all
---------------------------------------------------- تولید فرمول متغیر
select
0[Radif],
d.Fc_ID [FC_ID],
d.Pro_ID_VahedMali [Pro_ID_VahedMali],
d.ID [ID],
m.Code [Bar_Code],
d.tarikh[Tarikh],
d.Saat [Saat],
case d.Dom_ID_Noe
when 259 then cast( 218 as smallint) -- 259 ضايعات
when 260 then cast( 227 as smallint) -- 260 کالاي مصرفي
when 463 then cast( 217 as smallint) -- 463 کالاي توليدي
end [Noe],
cast( 0 as bigint)[Taf_ID],
cast( 0 as bigint)[Taf_Code],
0 [Pro_ID_GroohAshkhas],
'--' [Taf_Name],
case d.Dom_ID_Noe
when 259 then 'تولید ضایعات ('+ cast(m.code as nvarchar) + ') فرمول متغیر' -- TolidZayeaat
when 260 then 'کالای مصرفی تولید ('+ cast(m.code as nvarchar) + ') فرمول متغیر' -- TolidKalaMasrafi
when 463 then 'تولید ('+ cast(m.code as nvarchar) + ') فرمول متغیر' --
end [Sharh],
m.codeKarbar[Kar_ID],
cast(m.Dom_ID_Vaziat as smallint) [Vaziat],
isnull(d.Pro_ID_Anbar,0) [Anb_ID],
isnull(d.Kal_ID,0) [Kal_ID],
k.Code [Kal_Code],
k.TechnicalCode [TechnicalCode],
k.Pro_ID_Vahed [Pro_ID_Unit],
k.Pro_ID_Grooh [Pro_ID_GroohKala],
k.name [Kal_Name],
d.Dom_ID_Restoration,
isnull(d.Pro_ID_Color,0),
isnull(d.Pro_ID_Size,0),
CAST(d.tedad as float) [Tedad],
case d.Dom_ID_Noe
when 259 then d.Price -- 259 ضايعات
when 260 then d.Price -- 260 کالاي مصرفي
when 463 then d.Price -- 463 کالاي توليدي
end [Mablagh],
0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش
0[SaatSabt],
case d.Dom_ID_Noe
when 259 then cast(d.tedad as decimal(19,4)) -- TolidZayeaat
when 260 then 0.0 -- TolidKalaMasrafi
when 463 then cast(d.tedad as decimal(19,4)) -- tolid
end [MeghdarVaredeh],
case d.Dom_ID_Noe
when 259 then 0.0 -- TolidZayeaat
when 260 then cast(d.tedad as decimal(19,4))-- TolidKalaMasrafi
when 463 then 0.0 -- tolid
end [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.ID,k.Pro_ID_Vahed,d.tedad,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
case d.Dom_ID_Noe
when 259 then isnull(d.Price,0.0) -- TolidZayeaat
when 260 then 0.0 -- TolidKalaMasrafi
when 463 then isnull(d.Price,0.0) -- tolid
end [MablaghVaredeh],
0.0[MablaghSadereh],
case d.Dom_ID_Noe
when 259 then cast(d.tedad * d.Price as decimal(19,4) ) -- TolidZayeaat
when 260 then 0.0 -- TolidKalaMasrafi
when 463 then cast(d.tedad * d.Price as decimal(19,4) ) -- tolid
end [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
cast(case when d.Dom_ID_Noe in (259,463) /*TolidZayeaat,tolid*/ then 1 else 0 end as bit) [IsEntry],
0 [BD_ID] ,
0 [Con_ID],
0 [PP_ID],
0 [PPI_ID],
0 [Booth_ID]
from Product m
join ProductDetail d on m.ID = d.Prod_ID
join Kala k on d.Kal_ID = k.ID
where
m.Dom_ID_Type = 1583 /*تولید متغیر*/ and
m.Dom_ID_Vaziat != 101 and
(cast(@codeKalaAz as int) = '0' or k.Code >= cast(@codeKalaAz as int)) and
(cast(@codeKalaEla as int) = '0' or k.Code <= cast(@codeKalaEla as int)) and
(@GroohKalaAz = 0 or k.Pro_ID_Grooh >= @GroohKalaAz) and
(@GroohKalaEla = 0 or k.Pro_ID_Grooh <= @GroohKalaEla) and
(@AnbarAz = 0 or d.Pro_ID_Anbar >= @AnbarAz) and
(@AnbarEla = 0 or d.Pro_ID_Anbar <= @AnbarEla) and
(@Fc_ID=0 or d.Fc_ID = @Fc_ID) and
(@Pro_ID_VahedMali=0 or d.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
(@tarikhAz = 0 or d.tarikh >= @tarikhAz) and
(@tarikhEla = 0 or d.tarikh <= @tarikhEla) and
(@TimeAz=0 or d.saat>=@TimeAz) and
(@TimeEla=0 or d.saat<=@TimeEla) and
(@factorCodeAz=0 or m.Code<=@factorCodeAz) and
(@factorCodeEla=0 or m.Code<=@factorCodeEla) and
(
@strType='' or
(
case d.Dom_ID_Noe
when 259 then cast( 218 as smallint) -- 259 ضايعات
when 260 then cast( 227 as smallint) -- 260 کالاي مصرفي
when 463 then cast( 217 as smallint) -- 463 کالاي توليدي
end in (select * from dbo.CastStringToTable(@strType))
)
) and
(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and
(@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and
(@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
(@strConIds = '') and
(@strPpIds = '') and
(@strPpiIds = '') and
(@strBoothIds = '')
union all
---------------------------------------------------- تولید فرمول ثابت
select
0[Radif],
m.Fc_ID [FC_ID],
m.Pro_ID_VahedMali [Pro_ID_VahedMali],
case m.Dom_ID_Type when 1582 /*تولید ثابت*/ then m.ID when 1584 /*تولید ترکیبی*/ then isnull(m.Prod_ID,0) else 0 end [ID],
m.Code [Bar_Code],
m.tarikh[Tarikh],
m.Saat [Saat],
cast( case m.Dom_ID_Type when 1582 /*تولید ثابت*/ then 1587 when 1584 /*تولید ترکیبی*/ then 1589 else 0 end as smallint) /*تولید فرمول ثابت*/ [Noe],
cast( 0 as bigint)[Taf_ID],
cast( 0 as bigint)[Taf_Code],
0 [Pro_ID_GroohAshkhas],
'--' [Taf_Name],
case d.Dom_ID_Noe
when 259 then 'تولید ضایعات ('+ cast(m.code as nvarchar) + ') ' + case m.Dom_ID_Type when 1582 /*تولید ثابت*/ then 'فرمول ثابت' when 1584 /*تولید ترکیبی*/ then 'فرمول ترکیبی' else 'تولید ** ' end -- TolidZayeaat
when 260 then 'کالای مصرفی تولید ('+ cast(m.code as nvarchar) + ') ' + case m.Dom_ID_Type when 1582 /*تولید ثابت*/ then 'فرمول ثابت' when 1584 /*تولید ترکیبی*/ then 'فرمول ترکیبی' else 'تولید ** ' end -- TolidKalaMasrafi
when 463 then 'تولید ('+ cast(m.code as nvarchar) + ') ' + case m.Dom_ID_Type when 1582 /*تولید ثابت*/ then 'فرمول ثابت' when 1584 /*تولید ترکیبی*/ then 'فرمول ترکیبی' else 'تولید ** ' end
end [Sharh],
m.codeKarbar[Kar_ID],
cast(m.Dom_ID_Vaziat as smallint) [Vaziat],
isnull(d.Pro_ID_Anbar,0) [Anb_ID],
isnull(d.Kal_ID,0) [Kal_ID],
k.Code [Kal_Code],
k.TechnicalCode [TechnicalCode],
k.Pro_ID_Vahed [Pro_ID_Unit],
k.Pro_ID_Grooh [Pro_ID_GroohKala],
k.name [Kal_Name],
d.Dom_ID_Restoration,
isnull(d.Pro_ID_Color,0),
isnull(d.Pro_ID_Size,0),
CAST(d.tedad as float) [Tedad],
case d.Dom_ID_Noe
when 259 then d.Price -- 259 ضايعات
when 260 then d.Price -- 260 کالاي مصرفي
when 463 then d.Price -- 463 کالاي توليدي
end [Mablagh],
0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش
0[SaatSabt],
case d.Dom_ID_Noe
when 259 then cast(d.tedad as decimal(19,4)) -- TolidZayeaat
when 260 then 0.0 -- TolidKalaMasrafi
when 463 then cast(d.tedad as decimal(19,4)) -- tolid
end [MeghdarVaredeh],
case d.Dom_ID_Noe
when 259 then 0.0 -- TolidZayeaat
when 260 then cast(d.tedad as decimal(19,4))-- TolidKalaMasrafi
when 463 then 0.0 -- tolid
end [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.ID,k.Pro_ID_Vahed,d.tedad,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
case d.Dom_ID_Noe
when 259 then isnull(d.Price,0.0) -- TolidZayeaat
when 260 then 0.0 -- TolidKalaMasrafi
when 463 then isnull(d.Price,0.0) -- tolid
end [MablaghVaredeh],
0.0[MablaghSadereh],
case d.Dom_ID_Noe
when 259 then cast(d.tedad * d.Price as decimal(19,4) ) -- TolidZayeaat
when 260 then 0.0 -- TolidKalaMasrafi
when 463 then cast(d.tedad * d.Price as decimal(19,4) ) -- tolid
end [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
cast(case when d.Dom_ID_Noe in (259,463) /*TolidZayeaat,tolid*/ then 1 else 0 end as bit) [IsEntry],
0 [BD_ID] ,
0 [Con_ID],
0 [PP_ID],
0 [PPI_ID],
0 [Booth_ID]
from Product m
join ProductDetail d on m.ID = d.Prod_ID
join Kala k on d.Kal_ID = k.ID
where
m.Dom_ID_Type in (1582 /*تولید ثابت*/,1584 /*تولید ترکیبی*/) and
m.Dom_ID_Vaziat != 101 and
(cast(@codeKalaAz as int) = '0' or k.Code >= cast(@codeKalaAz as int)) and
(cast(@codeKalaEla as int) = '0' or k.Code <= cast(@codeKalaEla as int)) and
(@GroohKalaAz = 0 or k.Pro_ID_Grooh >= @GroohKalaAz) and
(@GroohKalaEla = 0 or k.Pro_ID_Grooh <= @GroohKalaEla) and
(@AnbarAz = 0 or d.Pro_ID_Anbar >= @AnbarAz) and
(@AnbarEla = 0 or d.Pro_ID_Anbar <= @AnbarEla) and
(@Fc_ID=0 or m.Fc_ID = @Fc_ID) and
(@Pro_ID_VahedMali=0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
(@tarikhAz = 0 or m.tarikh >= @tarikhAz) and
(@tarikhEla = 0 or m.tarikh <= @tarikhEla) and
(@TimeAz=0 or m.saat>=@TimeAz) and
(@TimeEla=0 or m.saat<=@TimeEla) and
(@factorCodeAz=0 or m.Code<=@factorCodeAz) and
(@factorCodeEla=0 or m.Code<=@factorCodeEla) and
(
@strType='' or
(
case d.Dom_ID_Noe
when 259 then cast( 218 as smallint) -- 259 ضايعات
when 260 then cast( 227 as smallint) -- 260 کالاي مصرفي
when 463 then cast( 217 as smallint) -- 463 کالاي توليدي
end in (select * from dbo.CastStringToTable(@strType))
)
) and
(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and
(@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and
(@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
(@strConIds = '') and
(@strPpIds = '') and
(@strPpiIds = '') and
(@strBoothIds = '')
union all
---------------------------------------------------- تبدیل کالا
select
0[Radif],
m.Fc_ID [FC_ID],
m.Pro_ID_VahedMali [Pro_ID_VahedMali],
m.ID [ID],
m.Code [Bar_Code],
m.tarikh[Tarikh],
m.Saat [Saat],
case d.Dom_ID_Noe
when 260 then cast( 228 as smallint) -- 260 کالاي مصرفي
when 463 then cast( 219 as smallint) -- 463 کالاي توليدي
end [Noe],
cast( 0 as bigint)[Taf_ID],
cast( 0 as bigint)[Taf_Code],
0 [Pro_ID_GroohAshkhas],
'--' [Taf_Name],
case d.Dom_ID_Noe
when 260 then 'کالای مصرفی تبدیل ('+ cast(m.code as nvarchar) + ')' --
when 463 then 'تبدیل ('+ cast(m.code as nvarchar) + ')' --
end [Sharh],
m.Kar_ID_Creator [Kar_ID],
cast(m.Dom_ID_Vaziat as smallint) [Vaziat],
isnull(d.Pro_ID_Anbar,0) [Anb_ID],
isnull(d.Kal_ID,0) [Kal_ID],
k.Code [Kal_Code],
k.TechnicalCode [TechnicalCode],
k.Pro_ID_Vahed [Pro_ID_Unit],
k.Pro_ID_Grooh [Pro_ID_GroohKala],
k.name [Kal_Name],
d.Dom_ID_Restoration,
isnull(d.Pro_ID_Color,0),
isnull(d.Pro_ID_Size,0),
CAST(d.tedad as float) [Tedad],
case d.Dom_ID_Noe
when 260 then d.Price -- 260 کالاي مصرفي
when 463 then d.Price -- 463 کالاي توليدي
end [Mablagh],
0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش
0[SaatSabt],
case d.Dom_ID_Noe
when 260 then 0.0 -- TolidKalaMasrafi
when 463 then cast(d.tedad as decimal(19,4)) -- tolid
end [MeghdarVaredeh],
case d.Dom_ID_Noe
when 260 then cast(d.tedad as decimal(19,4))-- TolidKalaMasrafi
when 463 then 0.0 -- tolid
end [MeghdarSadereh],
dbo.fGetVahedBasteBandi(k.ID,k.Pro_ID_Vahed,d.tedad,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
0.0[MeghdarMandeh],
case d.Dom_ID_Noe
when 260 then 0.0 -- KalaMasrafi
when 463 then isnull(d.Price,0.0) -- tolid
end [MablaghVaredeh],
case d.Dom_ID_Noe
when 260 then isnull(d.Price,0.0) -- KalaMasrafi
when 463 then 0.0 -- tolid
end [MablaghSadereh],
case d.Dom_ID_Noe
when 260 then cast(d.tedad * d.Price as decimal(19,4) ) -- کالا مصرفی
when 463 then cast(d.tedad * d.Price as decimal(19,4) ) -- رسید تبدیل کالا
end [MablaghJam],
0.0[MablaghMiangin],
0.0[MablaghMandeh],
0.0[MablaghJamVaredeh],
0.0[MablaghJamSadereh],
cast(case when d.Dom_ID_Noe in (463) /*رسید تبدیل*/ then 1 else 0 end as bit) [IsEntry],
0 [BD_ID] ,
0 [Con_ID],
0 [PP_ID],
0 [PPI_ID],
0 [Booth_ID]
from ConvertGood m
join ConvertGoodDetail d on m.ID = d.CG_ID
join Kala k on d.Kal_ID = k.ID
where
m.Dom_ID_Vaziat != 101 and
(cast(@codeKalaAz as int) = '0' or k.Code >= cast(@codeKalaAz as int)) and
(cast(@codeKalaEla as int) = '0' or k.Code <= cast(@codeKalaEla as int)) and
(@GroohKalaAz = 0 or k.Pro_ID_Grooh >= @GroohKalaAz) and
(@GroohKalaEla = 0 or k.Pro_ID_Grooh <= @GroohKalaEla) and
(@AnbarAz = 0 or d.Pro_ID_Anbar >= @AnbarAz) and
(@AnbarEla = 0 or d.Pro_ID_Anbar <= @AnbarEla) and
(@Fc_ID=0 or m.Fc_ID = @Fc_ID) and
(@Pro_ID_VahedMali=0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
(@tarikhAz = 0 or m.tarikh >= @tarikhAz) and
(@tarikhEla = 0 or m.tarikh <= @tarikhEla) and
(@TimeAz=0 or m.saat>=@TimeAz) and
(@TimeEla=0 or m.saat<=@TimeEla) and
(@factorCodeAz=0 or m.Code<=@factorCodeAz) and
(@factorCodeEla=0 or m.Code<=@factorCodeEla) and
(
@strType='' or
(
case d.Dom_ID_Noe
when 260 then cast( 228 as smallint) -- تبديل کالاي مصرفي
when 463 then cast( 219 as smallint) -- تبديل کالا
end in (select * from dbo.CastStringToTable(@strType))
)
) and
(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and
(@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and
(@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
(@strConIds = '') and
(@strPpIds = '') and
(@strPpiIds = '') and
(@strBoothIds = '')
----------------------------------------------------
order by Code , Noe , Tarikh
end