--select * from errors--select * from bargeh-- PrcPersonCardDetailNaghdAndSanad 4 , 1
--select * from tafsili
IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='PrcPersonCardDetailNaghdAndSanad')
DROP proc PrcPersonCardDetailNaghdAndSanad
GO
CREATE proc PrcPersonCardDetailNaghdAndSanad @pcode int , @NoeGardesh smallint as
begin
declare @NoeTaf smallInt;
set @NoeTaf = (select noe From Tafsili where code = @pcode);
select ------------------------------------------- مانده اول دوره
0[Radif],
0[Tarikh],
0[Saat],
'مانده اول دوره'[Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case t.noe
when 2 then -- shakhs
case when t.MojoodiAvalDore<0 then abs(t.MojoodiAvalDore) else 0.0 end
when 1 then t.MojoodiAvalDore--bank
when 3 then t.MojoodiAvalDore--Sandoogh
else 0.0
end as [Bedehkar],
case t.noe
when 2 then -- Shakhs
case when t.MojoodiAvalDore>0 then abs(t.MojoodiAvalDore) else 0.0 end
else 0.0 end as [Bestankar],
0.0[Mandeh],
''[Mahiat],
0[NoeRadif],
t.code[Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tafsili t
where t.code= @pcode
union all
select ------------------------------------------- پرداخت
0[Radif],
m.Tarikh [Tarikh],
m.SaatSabt[Saat],
case m.NoeMaster
when 0 then
case m.noeDetail
when 0 then 'دریافت ('+ cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+')'+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' '+isnull(m.sharh,'')
when 1 then 'دریافت حواله بانکي ('+ cast(m.CodeBargeh as nvarchar) + '):'+' ش.حواله ('+m.ShomarehSanad+')'+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' '+isnull(m.sharh,'')
when 2 then 'دریافت وجه ('+ cast(m.CodeBargeh as nvarchar) + ')' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') end
when 1 then
case m.noeDetail
when 0 then 'پرداخت ('+ cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+')' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'')
when 1 then 'پرداخت حواله بانکي ('+ cast(m.CodeBargeh as nvarchar) + '):'+' ش.حواله ('+m.ShomarehSanad+')'+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'')
when 2 then 'پرداخت وجه ('+ cast(m.CodeBargeh as nvarchar) + ')' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') end
when 2 then
'پرداخت هزينه ('+ cast(m.CodeBargeh as nvarchar) + ')' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' + isnull(m.sharh,'')
when 3 then
'حواله حسابداري ('+ cast( m.CodeBargeh as nvarchar) + ')' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'')
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
m.mablagh as [Bedehkar],
0.0 as [Bestankar],
0.0[Mandeh],
''[Mahiat],
case m.NoeMaster
when 0 then
case m.noeDetail
when 0 then
case m.IsAvalDoreh
when 0 then 2 --DaryaftChek
when 1 then 1 end --DaryaftChekAvalDoreh
when 1 then 3 --DaryaftHavaleh
when 2 then 4 --DaryaftVajh
end
when 1 then
case m.noeDetail
when 0 then
case m.IsAvalDoreh
when 0 then 6 --PardakhtChek
when 1 then 5 end --PardakhtChekAvalDoreh
when 1 then 7 --PardakhtHavaleh
when 2 then 8 -- PardakhtVajh
end
when 2 then 9--Hazineh
when 3 then 10--HavalehHesabdari
when 4 then 11--DarAmad
end [NoeRadif],
--2[NoeRadif],
m.code [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
case m.NoeMaster
when 3 then isnull(m.CodeProjehBedehKar,0) /*حواله حسابداری*/
when 2 then isnull(m.CodeProjehBedehKar,0)/*هزینه*/
else 0
end [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from daryaftpardakht m
where
m.Vaziat != 1 and
m.CodeBargehParent is null and
(m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and
m.CodeTafsiliBedehkar = @pcode
union all
select ------------------------------------------- دریافت
0[Radif],
m.Tarikh [Tarikh],
m.SaatSabt[Saat],
case m.noeMaster
when 0 then
case m.noeDetail
when 0 then 'دریافت (' + cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+')'+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'')
when 1 then 'دریافت حواله بانکي (' + cast(m.CodeBargeh as nvarchar) + '):'+' ش.حواله ('+m.ShomarehSanad+')'+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'')
when 2 then 'دریافت وجه (' + cast(m.CodeBargeh as nvarchar) + ')'+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') end
--when 3 then 'پرداخت حواله حسابداري (' + cast(m.code as nvarchar) + ')'+isnull(m.sharh,'') end
when 1 then
case m.noeDetail
when 0 then 'پرداخت (' + cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+')'+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'')
when 1 then 'پرداخت حواله بانکي (' + cast(m.CodeBargeh as nvarchar) + '):'+' ش.حواله ('+m.ShomarehSanad+')'+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'')
when 2 then 'پرداخت وجه ('+ cast(m.CodeBargeh as nvarchar) + ')'+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') end
--when 3 then 'پرداخت حواله حسابداري (' + cast(m.codeBargeh as nvarchar) + ')' +m.sharh+' '+d.sharh end
when 2 then
'دریافت هزينه ('+ cast(m.CodeBargeh as nvarchar) + ')'+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'')
when 3 then
'حواله حسابداري ('+ cast( m.CodeBargeh as nvarchar) + ')' +(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'')
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0.0 as [Bedehkar],
m.mablagh as [Bestankar],
0.0[Mandeh],
''[Mahiat],
case m.NoeMaster
when 0 then
case m.noeDetail
when 0 then
case m.IsAvalDoreh
when 0 then 2 --DaryaftChek
when 1 then 1 end --DaryaftChekAvalDoreh
when 1 then 3 --DaryaftHavaleh
when 2 then 4 --DaryaftVajh
end
when 1 then
case m.noeDetail
when 0 then
case m.IsAvalDoreh
when 0 then 6 --PardakhtChek
when 1 then 5 end --PardakhtChekAvalDoreh
when 1 then 7 --PardakhtHavaleh
when 2 then 8 -- PardakhtVajh
end
when 2 then 9--Hazineh
when 3 then 10--HavalehHesabdari
when 4 then 11--DarAmad
end [NoeRadif],
--3 [NoeRadif],
m.code [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
case m.NoeMaster
when 3 then isnull(m.CodeProjehBestanKar,0) /*حواله حسابداری*/
when 2 then isnull(m.CodeProjehBestanKar,0)/*هزینه*/
else 0
end [CodeProjehBestankar]
from daryaftpardakht m
where
m.Vaziat !=1 and
m.CodeBargehParent is null and
(m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and
m.CodeTafsiliBestankar = @pcode
union all
select ------------------------------------------- پرداخت به چند نفر
0[Radif],
m.Tarikh[Tarikh],
m.SaatSabt[Saat],
case m.NoeDetail
when 0 then 'پرداخت چک به چند نفر (' + cast(m.CodeBargeh as nvarchar)+') :'+'چک شماره ('+m.ShomarehSanad+')' + ' '+isnull(m.sharh,'') + case d.IsChekOwner
when 0 then 'شريک چک'
when 1 then 'صاحب چک' end
when 1 then 'پرداخت حواله بانکي به چند نفر (' + cast(m.CodeBargeh as nvarchar)+') :' +'حواله شماره ('+m.ShomarehSanad+')' + ' '+isnull(m.sharh,'') + case d.IsChekOwner
when 0 then 'شريک حواله'
when 1 then 'صاحب حواله' end
end as [Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
d.Mablagh as [BedehKar],
0.0 as [BestanKar],
0.0[Mandeh],
''[Mahiat],
case m.NoeMaster
when 1 then
case m.noeDetail
when 0 then
case m.IsAvalDoreh
when 0 then 6 --PardakhtChek
when 1 then 5 end --PardakhtChekAvalDoreh
when 1 then 7 --PardakhtHavaleh
end
end [NoeRadif],
--3[NoeRadif],
m.code as [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from DaryaftPardakht m join ChekDetail d on m.Code = d.CodeParent
where
m.Vaziat !=1 and
m.CodeTafsiliBedehkar is null and
d.CodeTafsiliMoshtari = @pcode
union all
select ------------------------------------------- دريافت از چند نفر
0[Radif],
m.Tarikh[Tarikh],
m.SaatSabt[Saat],
case m.NoeDetail
when 0 then 'دريافت چک از چند نفر (' + cast(m.CodeBargeh as nvarchar)+') :'+ 'چک شماره ('+m.ShomarehSanad+')' + ' '+isnull(m.sharh,'') + case d.IsChekOwner
when 0 then 'شريک چک'
when 1 then 'صاحب چک' end
when 1 then 'دريافت حواله بانکی از چند نفر (' + cast(m.CodeBargeh as nvarchar)+') :'+ 'حواله شماره ('+m.ShomarehSanad+')' + ' '+isnull(m.sharh,'') + case d.IsChekOwner
when 0 then 'شريک حواله'
when 1 then 'صاحب حواله' end
end as [Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0.0 as [BedehKar],
d.Mablagh as [BestanKar],
0.0[Mandeh],
''[Mahiat],
case m.NoeMaster
when 0 then
case m.noeDetail
when 0 then
case m.IsAvalDoreh
when 0 then 2 --DaryaftChek
when 1 then 1 end --DaryaftChekAvalDoreh
when 1 then 3 --DaryaftHavaleh
end
end [NoeRadif],
--2[NoeRadif],
m.code as [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from DaryaftPardakht m join ChekDetail d on m.Code = d.CodeParent
where
m.Vaziat !=1 and
m.CodeTafsiliBestankar is null and
d.CodeTafsiliMoshtari = @pcode
union all
select --------------------------------------------------------------------------- اعمال بدهي براي فاکتور فروش - بصورت تک تک کالا گزارش می گیرد
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
case d.IsKala
when 0 then
'فروش ('+ cast(m.codeBargeh as nvarchar)+ ')نام خدمات :' +(select name from tafsili where Code = d.CodeKhadamat2)+'('+ cast((select CodeHesab from Tafsili where Code = d.codeKhadamat2)as nvarchar) +')'+isnull(d.sharheRadif,'')
when 1 then
' فروش ('+ cast(m.codeBargeh as nvarchar)+ ')نام کالا :' +(select name from Kala where Code = d.CodeKala)+'('+(select codekala from Kala where Code = d.CodeKala) +')' + isnull(d.sharheRadif,'') end
when 1 then
'برگشت از فروش ('+ cast(m.codeBargeh as nvarchar) + ')نام کالا :' + (select name from Kala where Code = d.CodeKala) + '(' + (select codekala from Kala where Code = d.CodeKala) + ')' + isnull(d.sharheRadif,'')
when 2 then
'پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar) + ')نام کالا :' + (select name from Kala where Code = d.CodeKala) + '(' + (select codekala from Kala where Code = d.CodeKala) + ')' + isnull(d.sharheRadif,'')
when 3 then
'خريد (' + cast(m.codeBargeh as nvarchar)+ ')نام کالا :' + (select name from Kala where Code = d.CodeKala) + '(' + (select codekala from Kala where Code = d.CodeKala) + ')' + isnull(d.sharheRadif,'')
when 4 then
'برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ')نام کالا :' + (select name from Kala where Code = d.CodeKala) + '(' + (select codekala from Kala where Code = d.CodeKala) + ')' + isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
d.Tedad[Meghdar],
case m.noe
when 0 then
case d.IsKala
when 0 then --Khadamat
(select isnull(name,'') from vahed v where v.code = (select isnull(t.CodeVahed,0) from tafsili t where t.Code = d.CodeKhadamat2))
when 1 then -- Kala
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
end
when 1 then -- BargashtAzForoosh
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
when 3 then -- Kharid
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
when 4 then -- BargashtAzKharid
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
end [Vahed],
d.Mablagh[Fi],
case m.noe
when 0 then
cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad ) as decimal(19,4))
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad) as decimal(19,4))
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
0.0
when 1 then
cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad - isnull(d.MablagheTakhfif,0.0) + isnull(d.MablaghArzeshAfzoodeh,0.0)) as decimal(19,4))
when 2 then
0.0
when 3 then
cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad -isnull( d.MablagheTakhfif , 0.0) + isnull(d.MablaghArzeshAfzoodeh,0.0)) as decimal(19,4))
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 2 then 17 -- PishFactorForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat !=1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and
@NoeGardesh = 1 and
cast(m.CodeMoshtari as int) = @pcode
union all
select --------------------------------------------------------------------------- اعمال تخفیف ردیف فاکتور فروش - بصورت تک تک کالا گزارش می گیرد
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
case d.IsKala
when 0 then
'تخفیف ردیف فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' +isnull(d.sharheRadif,'')
when 1 then
'تخفیف ردیف فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') end
when 1 then
'تخفیف ردیف برگشت از فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'')
when 2 then
'تخفیف ردیف پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'')
when 3 then
'تخفیف ردیف خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'')
when 4 then
'تخفیف ردیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0 [Meghdar],
'' [Vahed],
isnull(d.MablagheTakhfif,0.0) [Fi],
case m.noe
when 0 then
0.0
when 1 then
isnull(d.MablagheTakhfif,0.0)
when 2 then
0.0
when 3 then
isnull(d.MablagheTakhfif,0.0)
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
isnull(d.MablagheTakhfif,0.0)
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
isnull(d.MablagheTakhfif,0.0)
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 2 then 17 -- PishFactorForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat !=1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and
@NoeGardesh = 1 and
d.MablagheTakhfif > 0 and
cast(m.CodeMoshtari as int) = @pcode
union all
select --------------------------------------------------------------------------- اعمال ارزش افزوده ردیف فاکتور فروش - بصورت تک تک کالا گزارش می گیرد
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
case d.IsKala
when 0 then
'ارزش افزوده ردیف فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' +isnull(d.sharheRadif,'')
when 1 then
'ارزش افزوده ردیف فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') end
when 1 then
'ارزش افزوده ردیف برگشت از فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'')
when 2 then
'ارزش افزوده ردیف پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'')
when 3 then
'ارزش افزوده ردیف خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'')
when 4 then
'ارزش افزوده ردیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0 [Meghdar],
'' [Vahed],
isnull(d.MablaghArzeshAfzoodeh,0.0)[Fi],
case m.noe
when 0 then
isnull(d.MablaghArzeshAfzoodeh,0.0)
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
isnull(d.MablaghArzeshAfzoodeh,0.0)
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
0.0
when 1 then
isnull(d.MablaghArzeshAfzoodeh,0.0)
when 2 then
0.0
when 3 then
isnull(d.MablaghArzeshAfzoodeh,0.0)
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 2 then 17 -- PishFactorForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat !=1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and
@NoeGardesh = 1 and
d.MablaghArzeshAfzoodeh > 0 and
cast(m.CodeMoshtari as int) = @pcode
union all
select --------------------------------------------------------------------------- اعمال بدهي براي فاکتور فروش بصورت کلی گزارش می گیرد
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '')
when 1 then
'برگشت از فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '')
when 2 then ') ' + isnull(m.Sharh , '')
when 3 then
'خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '')
when 4 then
'برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case m.noe
when 0 then
isnull
(
cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0))
from BargehDetail d
where d.codeBargeh = m.code) as decimal(19,4))
,0.0) -
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 2 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 4 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablaghArzeshAfzoodeh)
from BargehDetail d
where d.codeBargeh = m.code ) as decimal(19,4))
,0.0)
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
isnull
(
cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif,0))
from BargehDetail d
where d.codeBargeh = m.code) as decimal(19,4))
,0.0) -
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 2 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 4 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablaghArzeshAfzoodeh)
from BargehDetail d
where d.codeBargeh = m.code ) as decimal(19,4))
,0.0)
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
0.0
when 1 then
isnull
(
cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0))
from BargehDetail d
where d.codeBargeh = m.code) as decimal(19,4))
,0.0) -
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 4 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 2 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablaghArzeshAfzoodeh)
from BargehDetail d
where d.codeBargeh = m.code ) as decimal(19,4))
,0.0)
when 2 then
0.0
when 3 then
isnull
(
cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0))
from BargehDetail d
where d.codeBargeh = m.code) as decimal(19,4))
,0.0) -
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 4 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 2 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablaghArzeshAfzoodeh)
from BargehDetail d
where d.codeBargeh = m.code ) as decimal(19,4))
,0.0)
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 --BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from Bargeh m
where
m.Vaziat !=1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid, HavalehAnbar,ResidAnbar*/ and
cast(m.CodeMoshtari as int) = @pcode and
@NoeGardesh = 0
union all
select ------------------------------------------------------------------------- ويزيتوري کل فاکتور
0[Radif],
b.Tarikh[tarikh],
b.SaatSabt[Saat],
case b.Noe
when 0 then 'ويزيتوري فروش (' + cast( b.CodeBargeh as nvarchar) + ')'
when 1 then 'ويزيتوري برگشت از فروش (' + cast( b.CodeBargeh as nvarchar) + ')'
when 2 then 'ويزيتوري پيش فاکتور فروش (' + cast( b.CodeBargeh as nvarchar) + ')'
when 3 then 'ويزيتوري خريد (' + cast( b.CodeBargeh as nvarchar) + ')'
when 4 then 'ويزيتوري برگشت از خريد (' + cast( b.CodeBargeh as nvarchar) + ')'
when 5 then 'ويزيتوري پيش فاکتور خريد (' + cast( b.CodeBargeh as nvarchar) + ')'
when 6 then 'ويزيتوري حواله انبار (' + cast( b.CodeBargeh as nvarchar) + ')'
when 7 then 'ويزيتوري برگشت به انبار (' + cast( b.CodeBargeh as nvarchar) + ')'
when 8 then 'ويزيتوري انتقال بين انبار (' + cast( b.CodeBargeh as nvarchar) + ')'
end as[Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case b.noe
when 0 then 0.0-- Foroosh
when 1 then b.MablagheVisitor-- BargashtAzForoosh
when 3 then 0.0-- Kharid
when 4 then b.MablagheVisitor-- BargashtAzKharid
end [BedehKar],
case b.noe
when 0 then b.MablagheVisitor-- Foroosh
when 1 then 0.0-- BargashtAzForoosh
when 3 then b.MablagheVisitor-- Kharid
when 4 then 0.0-- BargashtAzKharid
end [BestanKar],
0.0 [Mandeh],
''[Mahiat],
case b.noe
when 0 then 12 --Foroosh
when 1 then 15 --BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
b.code[Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(b.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(b.codeprojeh,0) [CodeProjehBestankar]
from Bargeh b
where
b.Vaziat !=1 and
b.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid , HavalehAnbar,ResidAnbar*/ and
b.CodeVisitor = @pcode and
b.MablagheVisitor > 0 and
b.MablagheVisitor is not null and
b.MablagheVisitor is not null
union all
------------------------------------------------------------------------- ويزيتوري در تک تک کالاها
select
0[Radif],
m.tarikh[Tarikh],
m.SaatSabt[Saat],
case m.Noe
when 0 then-- 'ويزيتوري فاکتور فروش
case d.isKala
when 0 then -- Khadamat
'ويزيتوري فروش ('+ cast( m.CodeBargeh as nvarchar) + ') نام خدمات :'+(select name +'('+ cast (CodeHesab as nvarchar)+')' from tafsili where Code = d.CodeKhadamat2)
when 1 then --Kala
'ويزيتوري فروش ('+ cast( m.CodeBargeh as nvarchar) + ') نام کالا :' + (select name +'('+ cast (CodeKala as nvarchar)+')' from Kala where Code = d.codeKala) end
when 3 then --'ويزيتوري خريد
case d.isKala
when 1 then --Kala
'ويزيتوري خريد ('+ cast( m.CodeBargeh as nvarchar) + ') نام کالا :' + (select name +'('+ cast (CodeKala as nvarchar)+')' from Kala where Code = d.codeKala) end
when 1 then --'ويزيتوري برگشت از فروش
case d.isKala
when 1 then --Kala
'ويزيتوري برگشت از فروش ('+ cast( m.CodeBargeh as nvarchar) + ') نام کالا :' + (select name +'('+ cast (CodeKala as nvarchar)+')' from Kala where Code = d.codeKala) end
when 4 then --'ويزيتوري برگشت از خرید
case d.isKala
when 1 then --Kala
'ويزيتوري برگشت از خرید ('+ cast( m.CodeBargeh as nvarchar) + ') نام کالا :' + (select name +'('+ cast (CodeKala as nvarchar)+')' from Kala where Code = d.codeKala) end
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case m.noe
when 0 then 0.0-- Foroosh
when 1 then d.MablagheVisitor-- BargashtAzForoosh
when 3 then 0.0-- Kharid
when 4 then d.MablagheVisitor-- BargashtAzKharid
end as [Bedehkar],
case m.noe
when 0 then d.MablagheVisitor-- Foroosh
when 1 then 0.0-- BargashtAzForoosh
when 3 then d.MablagheVisitor-- Kharid
when 4 then 0.0-- BargashtAzKharid
end as [Bestankar],
0.0[Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 3 then 13 -- Kharid
when 1 then 15--BargashtAzForoosh
when 4 then 14--BargashtAzKharid
end as [NoeRadif],
m.code [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat != 1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid, HavalehAnbar,ResidAnbar*/ and
cast(d.CodeVisitor as int) = @pcode and
d.DarsadeVisitor is not null and
d.MablagheVisitor > 0 and
d.MablagheVisitor is not null
union all
-------------------------------------------------------------------------تخصیص خدمات فاکتور فروش
select
0[Radif],
m.tarikh[Tarikh],
m.SaatSabt[Saat],
case m.Noe
when 0 then-- 'تخصیص خدمات فاکتور فروش
case d.isKala
when 0 then -- Khadamat
'تخصیص خدمات فروش ('+ cast( d.CodeBargeh as nvarchar) + ') نام خدمات :'+(select name +'('+ cast (CodeHesab as nvarchar)+')' from tafsili where Code = d.CodeTakhsisKhadamat)
end
end as [sharh],
1[Meghdar],
case m.Noe
when 0 then
case d.isKala
when 0 then
(Select isnull(name , '') from vahed v where v.code = (select isnull(t.codeVahed , 0) from tafsili t where t.code = d.CodeTakhsisKhadamat))
end
end [Vahed],
isnull(d.MablaghTakhsisKhadamat,0)[Fi],
0.0 as [Bedehkar],
d.MablaghTakhsisKhadamat as [Bestankar],
0.0[Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat != 1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid, HavalehAnbar,ResidAnbar*/ and
cast(d.CodeTakhsisKhadamat as int) = @pcode and
d.MablaghTakhsisKhadamat is not null and
d.MablaghTakhsisKhadamat > 0 and
m.noe = 0
union all
------------------------------------------------------------------------------------------ اعمال مبالغ در فاکتورها
select
0[Radif],
(select b.tarikh from Bargeh b where b.code = m.codeBargehParent) as [Tarikh],
m.SaatSabt[Saat],
case (select b.Noe from Bargeh b where b.code = m.codeBargehParent)
when 0 then
case M.NoeDetail
when 0 then 'چک طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 1 then 'حواله بانکي طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 2 then 'وجه طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end
--when 3 then 'حواله حسابداري طي فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله حسابداري ('+d.ShomarehSanad+')'+m.sharh+' '+d.sharh end
when 1 then
case M.NoeDetail
when 0 then 'چک طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 1 then 'حواله بانکي طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 2 then 'وجه طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end
when 2 then ''
when 3 then
case M.NoeDetail
when 0 then 'چک طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ') '+' شماره چک ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 1 then 'حواله بانکي طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 2 then 'وجه نقد طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end
--when 3 then 'حواله حسابداري طي فاکتور خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله حسابداري ('+d.ShomarehSanad+')'+m.sharh+' '+d.sharh end
when 4 then
case M.NoeDetail
when 0 then 'چک طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 1 then 'حواله بانکي طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 2 then 'وجه طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end
when 5 then ''
when 6 then ''
when 7 then ''
end as [Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
m.mablagh as [BedehKar],
0.0 as [BestanKar],
0.0 as [Mandeh],
'' [Mahiat],
case (select b.Noe from Bargeh b where b.code = m.codeBargehParent)
when 0 then 12 --Foroosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashAzKharid
end as [NoeRadif],
(select b.code from Bargeh b where b.code = m.codeBargehParent) as [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(b.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(b.codeprojeh,0) [CodeProjehBestankar]
from daryaftpardakht m join Bargeh b on b.Code = m.CodeBargehParent
where
m.Vaziat != 1 and
m.CodeBargehParent is not null and
m.CodeTafsiliBedehkar = @pcode
union all
select
0[Radif],
(select b.tarikh from Bargeh b where b.code = m.codeBargehParent) as [Tarikh],
m.SaatSabt[Saat],
case (select b.Noe from Bargeh b where b.code = m.codeBargehParent)
when 0 then
case m.NoeDetail
when 0 then 'چک طي فروش ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' +' شماره چک ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 1 then 'حواله بانکي طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 2 then 'وجه طي فروش ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+m.sharh+' '+isnull(m.sharh,'') end
--when 3 then 'حواله حسابداري طي فاکتور فروش'+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله حسابداري ('+d.ShomarehSanad+')' +m.sharh+' '+d.sharh end
when 1 then
case M.NoeDetail
when 0 then 'چک طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 1 then 'حواله بانکي طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 2 then 'وجه طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end
when 2 then
case M.NoeDetail
when 0 then 'چک طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 1 then 'حواله بانکی طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 2 then 'وجه طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end
when 3 then
case m.NoeDetail
when 0 then 'جک طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره چک ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 1 then 'حواله بانکي طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 2 then 'وجه نقد طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end
--when 3 then 'حواله حسابداري طي فاکتور خريد' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله حسابداري ('+d.ShomarehSanad+')'+m.sharh+' '+d.sharh end
when 4 then
case M.NoeDetail
when 0 then 'چک طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 1 then 'حواله بانکي طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'')
when 2 then 'وجه طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end
when 5 then ''
when 6 then ''
when 7 then ''
end as [Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0.0 as [BedehKar],
m.mablagh as [BestanKar],
0.0 as [Mandeh],
'' [Mahiat],
case (select b.Noe from Bargeh b where b.code = m.codeBargehParent)
when 0 then 12 --Foroosh
when 1 then 15 --BargashtAzForoosh
when 2 then 17 --PishFactorForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
(select b.code from Bargeh b where b.code = m.codeBargehParent) as [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(b.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(b.codeprojeh,0) [CodeProjehBestankar]
from daryaftpardakht m join Bargeh b on b.Code = m.CodeBargehParent
where
m.Vaziat != 1 and
m.CodeBargehParent is not null and
m.CodeTafsiliBestankar = @pcode
union all
-------------------------------------------------------- برای خدمات و هزینه در فاکتور خرید و فروش
select
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + ')نام خدمات :' +(select name from tafsili where Code = d.CodeKhadamat2)+'('+ cast((select CodeHesab from Tafsili where Code = d.codeKhadamat2)as nvarchar) +')'+isnull(d.sharheRadif,'')
when 1 then
'برگشت از فروش (' + cast(m.codeBargeh as nvarchar)+ ')' + ')نام هزینه:' +(select name from tafsili where Code = d.CodeKhadamat2)+'('+ cast((select CodeHesab from Tafsili where Code = d.codeKhadamat2)as nvarchar) +')'+isnull(d.sharheRadif,'')
when 2 then ''
when 3 then
'خرید (' + cast(m.codeBargeh as nvarchar)+ ')' + ')نام هزینه:' +(select name from tafsili where Code = d.CodeKhadamat2)+'('+ cast((select CodeHesab from Tafsili where Code = d.codeKhadamat2)as nvarchar) +')'+isnull(d.sharheRadif,'')
when 4 then
'برگشت از خرید ('+ cast(m.codeBargeh as nvarchar)+ ')' + ')نام خدمات :' +(select name from tafsili where Code = d.CodeKhadamat2)+'('+ cast((select CodeHesab from Tafsili where Code = d.codeKhadamat2)as nvarchar) +')'+isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
d.Tedad[Meghdar],
case m.noe
when 0 then
case d.IsKala
when 0 then --Khadamat
(select isnull(name,'') from vahed v where v.code = (select isnull(t.CodeVahed,0) from tafsili t where t.Code = d.CodeKhadamat2))
when 1 then -- Kala
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
end
when 1 then -- BargashtAzForoosh
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
when 3 then
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
when 4 then -- BargashtAzKharid
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
end [Vahed],
d.Mablagh[Fi],
case m.noe
when 0 then
0.0
when 1 then
cast((d.mablagh * d.Tedad - isnull(d.MablagheTakhfif , 0.0)) as decimal(19,4)) -- هزینه
when 2 then
0.0
when 3 then
cast((d.mablagh * d.Tedad - isnull(d.MablagheTakhfif , 0.0)) as decimal(19,4)) -- هزینه
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
cast((d.mablagh * d.Tedad * d.meghdar1 * d.meghdar2 - isnull(d.MablagheTakhfif , 0.0)) as decimal(19,4)) -- خدمات
-
case when d.codetakhsisKhadamat is not null and (select noe from tafsili where code=@pcode)=4 then d.mablaghtakhsisKhadamat else 0 end
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat <> 1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid , HavalehAnbar,ResidAnbar*/ and
d.Mablagh > 0 and
cast(d.CodeKhadamat2 as int) = @pcode
-------------------------------------------------------- گردش چک ها
union all
select -------------------------------------------------------- گردش چک دریافتی
0[Radif],
/*case @NoeTaf
when 1 then --Bank
(select g.tarikh from GardesheChek g where g.Code = (select max(gg.code) from GardesheChek gg where gg.CodeParent = m.code))
when 3 then --sandoogh
m.Tarikh
end as [Tarikh],*/
d.Tarikh as [Tarikh],
d.Saat[Saat],
case @NoeTaf
when 1 then --Bank
case m.VaziateChek
when 0 then 'دریافت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'در جریان وصول '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 1 then 'برگشت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'از '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 2 then 'چک پاک شده ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 3 then 'چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 4 then 'چک موجود ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 5 then 'چک ('+cast(m.shomarehSanad as nvarchar)+') '+'برگشت به مشتری '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 6 then 'چک پاس شده ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 7 then 'چک برگشت از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 8 then 'دریافت نقدی از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 9 then 'پس دادن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 10 then 'نقد شدن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')' +' '+ isnull(d.Sharh ,'') end
when 11 then 'نقد شدن چک ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')
when 12 then 'موجود شدن چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')
when 3 then --Sandoogh
case d.Vaziat
when 0 then 'دریافت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'در جریان وصول '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 1 then 'برگشت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'از '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 2 then 'چک پاک شده ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 3 then 'چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 4 then 'چک موجود ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 5 then 'چک ('+cast(m.shomarehSanad as nvarchar)+') '+'برگشت به مشتری '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 6 then 'چک پاس شده ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 7 then 'چک برگشت از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 8 then 'دریافت نقدی از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 9 then 'پس دادن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 10 then 'نقد شدن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 11 then 'نقد شدن چک ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')
when 12 then 'موجود شدن چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')end
end as [Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
m.mablagh as [BedehKar],
0.0 as [BestanKar],
0.0 as [Mandeh],
'' [Mahiat],
case m.NoeMaster
when 0 then -- گردش چک دریافتی
case d.Vaziat
when 0 then 31 --GardeshChekDaryaftiDarJaryanVosool
when 1 then 32 --GardeshChekDaryaftiBargashtChek
when 2 then 33 --GardeshChekDaryaftiDeleted
when 3 then 34 --GardeshChekDaryaftiKharjShodeh
when 4 then 35 --GardeshChekDaryaftiMojood
when 5 then 36 --GardeshChekDaryaftiBargashtBeMoshtari
when 6 then 37 --GardeshChekDaryaftiPassShodeh
--when 7 then -- در پرداخت کاربرد دارد
when 8 then 38 --GardeshChekDaryaftiDaryaftNaghdi
when 9 then 39 --GardeshChekDaryaftiPasDadanChekBargashti
when 10 then 40 --GardeshChekDaryaftiNaghdShodanChekBargashti
when 11 then 41 --GardeshChekDaryaftiNaghdKardanChek
when 12 then 42 --GardeshDaryaftMojoodShodanChekKharjShodeh
else 1000
end
when 1 then -- گردش چک پرداختی
case d.Vaziat
when 0 then 21 --GardeshChekPardakhtiDarJaryanVosool
when 1 then 22 --GardeshChekPardakhtiBargashtChek
when 6 then 23 --GardeshChekPardakhtiPassShodan
when 7 then 24 --GardeshChekPardakhtiBargashtAzMoshtari
else 1000
end
end as [NoeRadif],
-- -1[NoeRadif],
m.Code [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from daryaftpardakht m join GardesheChek d
on m.code = d.codeParent
where
m.Vaziat != 1 and
d.CodeBedehKar = @pcode
union all
select -------------------------------------------------------- گردش چک پرداختی
0[Radif],
/*case @NoeTaf
when 1 then --Bank
(select g.tarikh from GardesheChek g where g.Code = (select max(gg.code) from GardesheChek gg where gg.CodeParent = m.code))
when 3 then --sandoogh
m.Tarikh
end as [Tarikh],*/
d.Tarikh as [Tarikh],
d.Saat[Saat],
case @NoeTaf
when 1 then --Bank
case m.VaziateChek
when 0 then 'دریافت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'در جریان وصول '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 1 then 'برگشت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'از '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 2 then 'چک پاک شده ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 3 then 'چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+')'+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 4 then 'چک موجود ('+cast(m.shomarehSanad as nvarchar)+') '+' '+ isnull(d.Sharh ,'')+' '+ isnull(d.Sharh ,'')
when 5 then 'چک ('+cast(m.shomarehSanad as nvarchar)+') '+'برگشت به مشتری '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 6 then 'چک پاس شده ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 7 then 'چک برگشت از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 8 then 'دریافت نقدی از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 9 then 'پس دادن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 10 then 'نقد شدن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')' +' '+ isnull(d.Sharh ,'') end
when 11 then 'نقد شدن چک ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')
when 12 then 'موجود شدن چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')
when 3 then --Sandoogh
case d.Vaziat
when 0 then 'دریافت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'در جریان وصول '+(select name from tafsili where code= d.CodeBestanKar)+' '+ isnull(d.Sharh ,'')
when 1 then 'برگشت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'از '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 2 then 'چک پاک شده ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 3 then 'چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 4 then 'چک موجود ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 5 then 'چک ('+cast(m.shomarehSanad as nvarchar)+') '+'برگشت به مشتری '+(select name from tafsili where code= d.CodeBestanKar) +' '+ isnull(d.Sharh ,'')
when 6 then 'چک پاس شده ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 7 then 'چک برگشت از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 8 then 'دریافت نقدی از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 9 then 'پس دادن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' '+ isnull(d.Sharh ,'')
when 10 then 'نقد شدن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')' +' '+ isnull(d.Sharh ,'')
when 11 then 'نقد شدن چک ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')
when 12 then 'موجود شدن چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')end
end as [Sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0.0 as [BedehKar],
m.mablagh as [BestanKar],
0.0 as [Mandeh],
'' [Mahiat],
case m.NoeMaster
when 0 then -- گردش چک دریافتی
case d.Vaziat
when 0 then 31 --GardeshChekDaryaftiDarJaryanVosool
when 1 then 32 --GardeshChekDaryaftiBargashtChek
when 2 then 33 --GardeshChekDaryaftiDeleted
when 3 then 34 --GardeshChekDaryaftiKharjShodeh
when 4 then 35 --GardeshChekDaryaftiMojood
when 5 then 36 --GardeshChekDaryaftiBargashtBeMoshtari
when 6 then 37 --GardeshChekDaryaftiPassShodeh
--when 7 then -- در پرداخت کاربرد دارد
when 8 then 38 --GardeshChekDaryaftiDaryaftNaghdi
when 9 then 39 --GardeshChekDaryaftiPasDadanChekBargashti
when 10 then 40 --GardeshChekDaryaftiNaghdShodanChekBargashti
when 11 then 41 --GardeshChekDaryaftiNaghdKardanChek
when 12 then 42 --GardeshDaryaftMojoodShodanChekKharjShodeh
else 1000
end
when 1 then -- گردش چک پرداختی
case d.Vaziat
when 0 then 21 --GardeshChekPardakhtiDarJaryanVosool
when 1 then 22 --GardeshChekPardakhtiBargashtChek
when 6 then 23 --GardeshChekPardakhtiPassShodan
when 7 then 24 --GardeshChekPardakhtiBargashtAzMoshtari
else 1000
end
end as [NoeRadif],
-- -1[NoeRadif],
m.Code [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from daryaftpardakht m join GardesheChek d
on m.code = d.codeParent
where m.Vaziat != 1 and d.CodeBestanKar = @pcode
union all
select ------------------------------------------- دریافت اول دوره
0[Radif],
m.tarikh[Tarikh],
m.SaatSabt[Saat],
'دریافت چک اول دوره ('+ cast(m.code as nvarchar) + '):'+'به شماره ('+m.ShomarehSanad+')'+ (select name from tafsili where code = m.CodeTafsiliBestankar) + isnull(m.sharh,'') as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
m.mablagh as [Bedehkar],
0.0 as [Bestankar],
0.0[Mandeh],
''[Mahiat],
1[NoeRadif],
m.code [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from daryaftpardakht m
where
m.Vaziat != 1 and
m.CodeBargehParent is null and
m.IsAvalDoreh = 1 and
m.NoeMaster = 0 and -- Daryaft
m.NoeDetail = 0 and -- Chek
m.CodeTafsiliBedehkar = @pcode
union all
select ------------------------------------------- پرداخت اول دوره
0[Radif],
m.tarikh[Tarikh],
m.SaatSabt[Saat],
'پرداخت چک اول دوره ('+ cast(m.code as nvarchar) + '):'+'به شماره ('+m.ShomarehSanad+')'+(select name from tafsili where code = m.CodeTafsiliBedehkar) + isnull(m.sharh,'') as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0.0 as [Bedehkar],
m.mablagh as [Bestankar],
0.0[Mandeh],
''[Mahiat],
5[NoeRadif],
m.code [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from daryaftpardakht m
where
m.Vaziat != 1 and
m.CodeBargehParent is null and
m.IsAvalDoreh = 1 and
m.NoeMaster = 1 and -- Daryaft
m.NoeDetail = 0 and -- Chek
m.CodeTafsiliBestankar = @pcode
union all
select ------------------------------------------- تخفیفات برگه دیتیل
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'تخفیف فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 1 then
'تخفیف برگشت از فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 2 then ''
when 3 then
'تخفیف خرید (' + cast(m.codeBargeh as nvarchar)+ ')' +isnull(d.sharheRadif,'')
when 4 then
'تخفیف برگشت از خرید ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case m.noe
when 0 then d.mablagheTakhfif
when 4 then d.mablagheTakhfif
else 0.0 end as [Bedehkar],
case m.noe when 3 then d.mablagheTakhfif else 0.0 end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat <> 1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid , HavalehAnbar,ResidAnbar*/ and
d.mablagheTakhfif > 0 and
cast(d.CodeTakhfif as int) = @pcode
union all
select --------------------------------------------------------------------------- اضافات و تخفیفات فاکتور فروش و خرید
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'تخفیف فروش (' + cast(m.codeBargeh as nvarchar)+ ')'
when 1 then
'اضافات برگشت از فروش (' + cast(m.codeBargeh as nvarchar)+ ')'
when 2 then ''
when 3 then
'اضافات خرید (' + cast(m.codeBargeh as nvarchar)+ ')'
when 4 then
'تخفیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ')'
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case m.noe
when 0 then
0.0
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
cast((d.mablagh) as decimal(19,4))
when 1 then
cast((d.mablagh) as decimal(19,4))
when 2 then
0.0
when 3 then
cast((d.mablagh) as decimal(19,4))
when 4 then
cast((d.mablagh) as decimal(19,4))
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 --BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join daryaftPardakht d on m.code = d.CodeBargehParent)
where
m.Vaziat !=1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid , HavalehAnbar,ResidAnbar*/ and
@NoeGardesh = 1 and
d.NoeMaster = 2 and
d.mablagh > 0 and
cast(m.CodeMoshtari as int) = @pcode
union all
select --------------------------------------------------------------------------- اضافات و تخفیفات فاکتور فروش و خرید
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'اضافات فروش (' + cast(m.codeBargeh as nvarchar)+ ')'
when 1 then
'تخفیف برگشت از فروش (' + cast(m.codeBargeh as nvarchar)+ ')'
when 2 then ''
when 3 then
'تخفیف خرید (' + cast(m.codeBargeh as nvarchar)+ ')'
when 4 then
'اضافات برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ')'
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case m.noe
when 0 then
cast((d.mablagh) as decimal(19,4))
when 1 then
cast((d.mablagh) as decimal(19,4))
when 2 then
0.0
when 3 then
cast((d.mablagh) as decimal(19,4))
when 4 then
cast((d.mablagh) as decimal(19,4))
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
0.0
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 --BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join daryaftPardakht d on m.code = d.CodeBargehParent)
where
m.Vaziat !=1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid , HavalehAnbar,ResidAnbar*/ and
@NoeGardesh = 1 and
d.NoeMaster = 4 and
d.mablagh > 0 and
cast(m.CodeMoshtari as int) = @pcode
union all
select ------------------------------------------- تخفیف در گردش چک
0[Radif],
g.Tarikh[Tarikh],
g.Saat[Saat],
'هزینه پاس چک (' +d.shomarehSanad +') '+ isnull(g.Sharh ,'') [Sharh],
1[Meghdar],
(select isnull(name,'') from vahed v where v.code = (select t.CodeVahed from Tafsili t where t.code = g.CodeHazineh))[Vahed],
g.Mablagh[Fi],
g.mablagh as [Bedehkar],
0.0 as [Bestankar],
0.0[Mandeh],
''[Mahiat],
case d.noeMaster
when 0 then 37
when 0 then 23 end
[NoeRadif],
g.codeParent[Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from gardesheChek g join DaryaftPardakht d on d.code = g.codeParent
where
cast(g.codeHazineh as int) = @pcode and
d.vaziat != 1 and
d.noeDetail = 0 and
g.Mablagh > 0
union all
------------------------------------------------------------------------------------- هزینه ویزیتوری ها
----------------------------------------------------- Visitori Dar Bargeh
select
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'ویزیتوری فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 1 then
'ویزیتوری برگشت از فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 2 then ''
when 3 then
'ویزیتوری خرید (' + cast(m.codeBargeh as nvarchar)+ ')' +isnull(d.sharheRadif,'')
when 4 then
'ویزیتوری برگشت از خرید ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0[Meghdar],
''[Vahed],
d.MablagheVisitor[Fi],
case m.noe
when 0 then d.MablagheVisitor -- Foroosh
when 3 then d.MablagheVisitor -- Kharid
when 1 then 0.0 -- BargashtAzForoosh
when 4 then 0.0 -- BargashtAzKharid
end as [Bedehkar],
case m.noe
when 0 then 0.0 -- Foroosh
when 3 then 0.0 -- Kharid
when 1 then d.MablagheVisitor -- BargashtAzForoosh
when 4 then d.MablagheVisitor -- BargashtAzKharid
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat <> 1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid , HavalehAnbar,ResidAnbar*/ and
d.MablagheVisitor > 0 and
cast(d.CodeFarayandVisitor as int) = @pcode
union all
----------------------------------------------------- Visitori Dar Bargeh
select
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'ویزیتوری فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 1 then
'ویزیتوری برگشت از فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 2 then ''
when 3 then
'ویزیتوری خرید (' + cast(m.codeBargeh as nvarchar)+ ')' +isnull(d.sharheRadif,'')
when 4 then
'ویزیتوری برگشت از خرید ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0[Meghdar],
''[Vahed],
d.MablagheVisitor[Fi],
case m.noe
when 0 then m.MablagheVisitor-- Foroosh
when 1 then 0.0-- BargashtAzForoosh
when 3 then m.MablagheVisitor-- Kharid
when 4 then 0.0-- BargashtAzKharid
end as [Bedehkar],
case m.noe
when 0 then 0.0-- Foroosh
when 1 then m.MablagheVisitor -- BargashtAzForoosh
when 3 then 0.0 -- Kharid
when 4 then m.MablagheVisitor -- BargashtAzKharid
end [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat <> 1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid , HavalehAnbar,ResidAnbar*/ and
d.MablagheVisitor > 0 and
cast(m.HazineVisitor as int) = @pcode
union all
select --------------------------------------------------------------------------- برای بدهکاری یا بستانکاری انبارگردانی
0 [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case d.noe
when 0 then
'کسورات انبارگردانی('+cast(m.code as nvarchar)+')'
when 1 then
'اضافات انبارگردانی('+cast(m.code as nvarchar)+')'
end as [sharh],
d.Tedad[Meghdar],
(select name from Vahed where Code = (select CodeVahed from Kala where Code = d.codeKala)) [Vahed],
d.Mablagh[Fi],
case d.noe
when 0 then
cast(d.tedad as decimal(19,4)) * d.mablagh
when 1 then
0.0
end as [Bedehkar],
case d.noe
when 0 then
0.0
when 1 then
cast(d.tedad as decimal(19,4)) * d.mablagh
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
16 [NoeRadif], -- AnbarGardani
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from AnbarGardani m join AnbarGardaniDetail d on m.code = d.codeAnbarGardani
where
m.Vaziat != 1 and
d.mablagh > 0 and
cast(d.CodeTafsili as int) = @pcode
union all
------------------------------------------------------------------------------ اضافات در برگه دیتیل
select
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'اضافات فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 1 then
'اضافات برگشت از فروش('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 2 then ''
when 3 then
'اضافات خرید (' + cast(m.codeBargeh as nvarchar)+ ')' +isnull(d.sharheRadif,'')
when 4 then
'اضافات برگشت از خرید ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0[Meghdar],
''[Vahed],
d.MablaghArzeshAfzoodeh[Fi],
case m.noe
when 0 then 0.0 -- Foroosh
when 1 then d.MablaghArzeshAfzoodeh -- BargashtAzForoosh
when 3 then d.MablaghArzeshAfzoodeh -- Kharid
when 4 then 0.0 -- BargashtAzKharid
end as [Bedehkar],
case m.noe
when 0 then d.MablaghArzeshAfzoodeh -- Foroosh
when 1 then 0.0 -- BargashtAzForoosh
when 3 then 0.0 -- Kharid
when 4 then d.MablaghArzeshAfzoodeh -- BargashtAzKharid
end [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
ISNULL(m.codeprojeh,0) [CodeProjehBedehkar],
ISNULL(m.codeprojeh,0) [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat <> 1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid , HavalehAnbar,ResidAnbar*/ and
d.MablaghArzeshAfzoodeh > 0 and
cast(d.CodeArzeshAfzoodeh as int) = @pcode
union all
-------------------------------------------------------------------- تولید بصورت جرئی گزارش می گیرد
select
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case d.noe
when 2 then
'کنترل سربار '+ cast(m.CodeTolid as nvarchar)+ ') بازای تولید ' + cast(m.tedad as nvarchar) +' '+isnull((select v.name from vahed v where v.Code = (select k.codeVahed From Kala k where k.Code = m.codeKala)),'') + ' ' + (select k.name from Kala k where k.code = m.codeKala) + ' ' + isnull(d.sharh,'')
when 3 then
'دستمزد تولید (' + cast(m.CodeTolid as nvarchar)+ ') بازای تولید ' + cast(m.tedad as nvarchar) +' '+isnull((select v.name from vahed v where v.Code = (select k.codeVahed From Kala k where k.Code = m.codeKala)),'') + ' ' +(select k.name from Kala k where k.code = m.codeKala) + ' به مدت ' + isnull(dbo.fCalculateWatch(isnull(d.saat , '00:00') , m.tedad) ,'00:00') + ' ساعت' +isnull(d.sharh,'')
end [sharh],
m.tedad [Meghdar],
'' [Vahed],
d.Mablagh [Fi],
0.0 [Bedehkar],
cast(d.Mablagh * m.tedad as decimal(19,4)) [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
50 [NoeRadif], -- Tolid
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from tolid m join toliddetail d
on m.code= d.codetolid
where
m.Vaziat <> 1 and
d.CodeTafsili is not null and
@NoeGardesh = 1 and
cast(d.CodeTafsili as int) = @pcode and
m.Noe = 0
union all
------------------------------------------------------- تولید بصورت کلی گزارش می گیرد
select
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
'تولید ('+ cast(m.CodeTolid as nvarchar)+ ') ' + isnull(m.Sharh,'') [sharh],
0 [Meghdar],
'' [Vahed],
0.0 [Fi],
0.0 [Bedehkar],
cast(d.mablagh * m.tedad as decimal(19,4)) [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
50 [NoeRadif], -- Tolid
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tolid m join TolidDetail d
on m.code = d.codeTolid
where
m.Vaziat <> 1 and
d.CodeTafsili = @pcode and
@NoeGardesh = 0 and
m.Noe = 0
---------------------------------------------
union all
-------------------------------------------------------------------- تبدیل بصورت جرئی گزارش می گیرد
select
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case d.noe
when 2 then
'کنترل سربار ('+ cast(m.CodeTolid as nvarchar)+ ') بازای تبدیل ' + cast(m.tedad as nvarchar) +' '+isnull((select v.name from vahed v where v.Code = (select k.codeVahed From Kala k where k.Code = m.codeKala)),'') + ' ' + (select k.name from Kala k where k.code = m.codeKala) + ' ' + isnull(d.sharh,'')
when 3 then
'دستمزد تبدیل (' + cast(m.CodeTolid as nvarchar)+ ') بازای تبدیل ' + cast(m.tedad as nvarchar) +' '+isnull((select v.name from vahed v where v.Code = (select k.codeVahed From Kala k where k.Code = m.codeKala)),'') + ' ' +(select k.name from Kala k where k.code = m.codeKala) + ' به مدت ' + isnull(dbo.fCalculateWatch(isnull(d.saat , '00:00') , m.tedad) ,'00:00') + ' ساعت' +isnull(d.sharh,'')
end [sharh],
m.tedad [Meghdar],
(select v.name from vahed v where v.Code = (select k.codeVahed From Kala k where k.Code = m.codeKala)) [Vahed],
d.Mablagh [Fi],
0.0 [Bedehkar],
cast(d.Mablagh * m.tedad as decimal(19,4)) [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
51 [NoeRadif], -- Tabdil
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from tolid m join toliddetail d
on m.code= d.codetolid
where
m.Vaziat <> 1 and
d.CodeTafsili is not null and
@NoeGardesh = 1 and
cast(d.CodeTafsili as int) = @pcode and
m.noe = 1 -- tabdil
union all
------------------------------------------------------- تبدیل بصورت کلی گزارش می گیرد
select
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
'تبدیل ('+ cast(m.CodeTolid as nvarchar)+ ') ' + isnull(m.Sharh,'') [sharh],
0 [Meghdar],
'' [Vahed],
0.0 [Fi],
0.0 [Bedehkar],
cast(d.mablagh * m.tedad as decimal(19,4)) [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
51 [NoeRadif], -- Tabdil
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tolid m join TolidDetail d
on m.code = d.codeTolid
where
m.Vaziat <> 1 and
d.CodeTafsili = @pcode and
@NoeGardesh = 0 and
m.noe = 1 -- Tabdil
union all
----------------------------------------------------------------------------------------------------- تعمیرات
select -------------------------------------------- بدهکاری مشتری کل تعمیرات
0 as [Radif],
m.TarikhPaziresh[tarikh],
m.SaatSabt[Saat],
'تعمیرات ('+ cast(m.CodeTamirat as nvarchar)+ ') ' + isnull(m.Sharh,'') [sharh],
1 [Meghdar],
isnull((select v.name from Vahed v where v.code = (select t.codevahed from Tafsili t where t.code = m.codekhadamat)),'') [Vahed],
m.MablaghHazineh [Fi],
m.MablaghHazineh [Bedehkar],
0.0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
52 [NoeRadif], -- Tamirat
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tamirat m
where
m.VaziatTamir not in (4,5) and -- LaghvShodeh , TabdilBeFactorForoosh
m.codeMoshtari = @pcode
union all
select -------------------------------------------- بستانکاری مشتری برای بیعانه
0 as [Radif],
m.TarikhPaziresh[tarikh],
m.SaatSabt[Saat],
'بیعانه تعمیرات ('+ cast(m.CodeTamirat as nvarchar)+ ') ' + isnull(m.Sharh,'') [sharh],
1 [Meghdar],
isnull((select v.name from Vahed v where v.code = (select t.codevahed from Tafsili t where t.code = m.codekhadamat)),'') [Vahed],
m.MablaghBeiaaneh [Fi],
0.0 [Bedehkar],
m.MablaghBeiaaneh [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
52 [NoeRadif], -- Tamirat
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tamirat m
where
m.VaziatTamir not in (4,5) and -- LaghvShodeh , TabdilBeFactorForoosh
m.codeMoshtari = @pCode and
m.MablaghBeiaaneh > 0
union all
select -------------------------------------------- بستانکاری خدمات
0 as [Radif],
m.TarikhPaziresh[tarikh],
m.SaatSabt[Saat],
'تعمیرات ('+ cast(m.CodeTamirat as nvarchar)+ ') ' + isnull(m.Sharh,'') [sharh],
1 [Meghdar],
isnull((select v.name from Vahed v where v.code = (select t.codevahed from Tafsili t where t.code = m.codekhadamat)),'') [Vahed],
m.MablaghHazineh [Fi],
0.0 [Bedehkar],
m.MablaghHazineh [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
52 [NoeRadif], -- Tamirat
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tamirat m
where
m.VaziatTamir not in (4,5) and -- LaghvShodeh , TabdilBeFactorForoosh
m.CodeKhadamat = @pcode
union all
select -------------------------------------------- بدهکاری صندوق بابت بیعانه
0 as [Radif],
m.TarikhPaziresh[tarikh],
m.SaatSabt[Saat],
'بیعانه تعمیرات ('+ cast(m.CodeTamirat as nvarchar)+ ') ' + isnull(m.Sharh,'') [sharh],
1 [Meghdar],
isnull((select v.name from Vahed v where v.code = (select t.codevahed from Tafsili t where t.code = m.codekhadamat)),'') [Vahed],
m.MablaghBeiaaneh [Fi],
m.MablaghBeiaaneh [Bedehkar],
0.0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
52 [NoeRadif], -- Tamirat
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tamirat m
where
m.VaziatTamir not in (4,5) and -- LaghvShodeh , TabdilBeFactorForoosh
m.CodeSandoogh = @pcode and
m.MablaghBeiaaneh > 0
union all
select -------------------------------------------- بستانکاری تکنسین
0 as [Radif],
m.TarikhPaziresh[tarikh],
m.SaatSabt[Saat],
'تعمیرات تکنسین ('+ cast(m.CodeTamirat as nvarchar)+ ') ' + isnull(m.Sharh,'') [sharh],
1 [Meghdar],
isnull((select v.name from Vahed v where v.code = (select t.codevahed from Tafsili t where t.code = m.codekhadamat)),'') [Vahed],
m.MablaghTamirKar [Fi],
0.0 [Bedehkar],
m.MablaghTamirKar [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
52 [NoeRadif], -- Tamirat
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tamirat m
where
m.VaziatTamir not in (4,5) and -- LaghvShodeh , TabdilBeFactorForoosh
m.codeTamirKar = @pCode and
m.MablaghTamirKar > 0
union all
select -------------------------------------------- هزینه تکنسین
0 as [Radif],
m.TarikhPaziresh[tarikh],
m.SaatSabt[Saat],
'هزینه تکنسین تعمیرات ('+ cast(m.CodeTamirat as nvarchar)+ ') ' + isnull(m.Sharh,'') [sharh],
1 [Meghdar],
isnull((select v.name from Vahed v where v.code = (select t.codevahed from Tafsili t where t.code = m.codekhadamat)),'') [Vahed],
m.MablaghTamirKar [Fi],
m.MablaghTamirKar [Bedehkar],
0.0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
52 [NoeRadif], -- Tamirat
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tamirat m
where
m.VaziatTamir not in (4,5) and -- LaghvShodeh , TabdilBeFactorForoosh
m.codeHazinehTamirKar = @pCode and
m.MablaghTamirKar > 0
union all
select -------------------------------------------- بدهکاری صندوق بابت دریافت نقدی در گردش تعمیرات
0 as [Radif],
d.Tarikh [tarikh],
d.SaatSabt[Saat],
'دریافت نقدی تعمیرات ('+ cast(m.CodeTamirat as nvarchar)+ ') ' + isnull(d.Sharh,'') [sharh],
1 [Meghdar],
isnull((select v.name from Vahed v where v.code = (select t.codevahed from Tafsili t where t.code = m.codekhadamat)),'') [Vahed],
d.Mablagh [Fi],
d.Mablagh [Bedehkar],
0.0 [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
53 [NoeRadif], -- TamiratGardesh
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tamirat m join GardeshTamirat d on m.code = d.CodeParent
where
m.VaziatTamir = 3 and -- TahvilBeMoshtari
d.CodeTafsiliBedehKar = @pcode and
d.Mablagh > 0
union all
select -------------------------------------------- بستانکاری شخص بابت دریافت نقدی در گردش تعمیرات
0 as [Radif],
d.Tarikh [tarikh],
d.SaatSabt[Saat],
'دریافت نقدی تعمیرات ('+ cast(m.CodeTamirat as nvarchar)+ ') ' + isnull(d.Sharh,'') [sharh],
1 [Meghdar],
isnull((select v.name from Vahed v where v.code = (select t.codevahed from Tafsili t where t.code = m.codekhadamat)),'') [Vahed],
d.Mablagh [Fi],
0.0 [Bedehkar],
d.Mablagh [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
53 [NoeRadif], -- TamiratGardesh
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from Tamirat m join GardeshTamirat d on m.code = d.CodeParent
where
m.VaziatTamir = 3 and -- TahvilBeMoshtari
m.CodeMoshtari = @pcode and
d.Mablagh > 0
---------------------------------------------------------------------------------------- پایان تعمیرات
/*
union all
select -------------------------------------------- حقوق و دستمزد برای اشخاص به صورت کلی
0 as [Radif],
m.TarikhMohasebeh[tarikh],
m.SaatSabt[Saat],
case m.Mah
when 1 then 'حقوق فرودین ماه '
when 2 then 'حقوق اردیبهشت ماه '
when 3 then 'حقوق خرداد ماه '
when 4 then 'حقوق تیر ماه '
when 5 then 'حقوق مرداد ماه '
when 6 then 'حقوق شهریور ماه '
when 7 then 'حقوق مهر ماه '
when 8 then 'حقوق آبان ماه '
when 9 then 'حقوق آذر ماه '
when 10 then 'حقوق دی ماه '
when 11 then 'حقوق بهمن ماه '
when 12 then 'حقوق اسفند ماه '
end as [sharh],
1 [Meghdar],
'' [Vahed],
0.0 [Fi],
0.0 [Bedehkar],
cast((select sum(d.MablaghBestanKar) - sum(d.MablaghBedehKar) from MohasebehHoghooghDetail d where d.codeParent = m.code and m.codeshakhs = d.codeTafsili ) as decimal(19,4)) [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
55 [NoeRadif], -- HoghooghDastmozdKol
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from MohasebehHoghoogh m
where
m.Vaziat <> 1 and
m.CodeShakhs = @pCode and
@NoeGardesh = 0
union all
select -------------------------------------------- حقوق و دستمزد برای اشخاص به صورت جزیی
0 as [Radif],
m.TarikhMohasebeh[tarikh],
m.SaatSabt[Saat],
d.Sharh [sharh],
1 [Meghdar],
'' [Vahed],
0.0 [Fi],
d.MablaghBedehKar [Bedehkar],
d.MablaghBestanKar [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
55 [NoeRadif], -- HoghooghDastmozdKol
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from MohasebehHoghoogh m , MohasebehHoghooghDetail d
where
m.code = d.codeParent and
m.Vaziat <> 1 and
d.CodeTafsili = @pCode and
@NoeGardesh = 1
union all
select -------------------------------------------- حقوق و دستمزد برای تفصیلی1 (هزینه و درآمد و ...)ِ
0 as [Radif],
m.TarikhMohasebeh[tarikh],
m.SaatSabt[Saat],
d.Sharh [sharh],
1 [Meghdar],
'' [Vahed],
0.0 [Fi],
d.MablaghBedehKar [Bedehkar],
d.MablaghBestanKar [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
55 [NoeRadif], -- HoghooghDastmozdKol
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from MohasebehHoghoogh m , MohasebehHoghooghDetail d
where
m.code = d.codeParent and
m.Vaziat <> 1 and
m.codeshakhs != d.codeTafsili and
d.CodeTafsili = @pCode
union all
select -------------------------------------------- حقوق و دستمزد برای تفصیلی2 (هزینه و درآمد و ...)ِ
0 as [Radif],
m.TarikhMohasebeh[tarikh],
m.SaatSabt[Saat],
d.Sharh [sharh],
1 [Meghdar],
'' [Vahed],
0.0 [Fi],
d.MablaghBedehKar [Bedehkar],
d.MablaghBestanKar [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
55 [NoeRadif], -- HoghooghDastmozdKol
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from MohasebehHoghoogh m , MohasebehHoghooghDetail d
where
m.code = d.codeParent and
m.Vaziat <> 1 and
m.codeshakhs != d.codeTafsili and
d.CodeTafsili2 = @pCode
union all
select -------------------------------------------- حقوق و دستمزد برای تفصیلی3 (هزینه و درآمد و ...)ِ
0 as [Radif],
m.TarikhMohasebeh[tarikh],
m.SaatSabt[Saat],
d.Sharh [sharh],
1 [Meghdar],
'' [Vahed],
0.0 [Fi],
d.MablaghBedehKar [Bedehkar],
d.MablaghBestanKar [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
55 [NoeRadif], -- HoghooghDastmozdKol
m.code[code] ,
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from MohasebehHoghoogh m , MohasebehHoghooghDetail d
where
m.code = d.codeParent and
m.Vaziat <> 1 and
m.codeshakhs != d.codeTafsili and
d.CodeTafsili3 = @pCode
*/
union all
------------------------------------------------------ پروژه
--------------------- بدهکار پروژه باشد
select
0[Radif],
m.tarikh[Tarikh],
m.SaatSabt[Saat],
case m.NoeMaster
when 2 then -- هزینه
'هزينه ('+ cast(m.code as nvarchar) + ')' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' + isnull(m.sharh,'')
when 3 then -- حواله
'حواله ('+ cast( m.code as nvarchar) + ')' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'')
else
''
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
m.mablagh as [Bedehkar],
0.0 as [Bestankar],
0.0[Mandeh],
''[Mahiat],
case m.NoeMaster
when 2 then 9--Hazineh
when 3 then 10--HavalehHesabdari
end [NoeRadif],
--2[NoeRadif],
m.code [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from daryaftpardakht m
where
m.Vaziat != 1 and
m.CodeBargehParent is null and
(m.IsAvalDoreh = 0 or m.IsAvalDoreh is null)
and m.CodeProjehBedehKar = @pcode
and @noeTaf in (5)
union all
----------------------------------------- بستانکار پروژه باشد
select
0[Radif],
m.tarikh[Tarikh],
m.SaatSabt[Saat],
case m.noeMaster
when 2 then
'هزينه ('+ cast(m.code as nvarchar) + ')'+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'')
when 3 then
'حواله ('+ cast( m.code as nvarchar) + ')' +(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'')
else
''
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
0.0 as [Bedehkar],
m.mablagh as [Bestankar],
0.0[Mandeh],
''[Mahiat],
case m.NoeMaster
when 2 then 9--Hazineh
when 3 then 10--HavalehHesabdari
end [NoeRadif],
--3 [NoeRadif],
m.code [Code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from daryaftpardakht m
where
m.Vaziat !=1 and
m.CodeBargehParent is null and
(m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and
m.CodeProjehBestanKar = @pcode and
@noeTaf in (5)
union all
select --------------------------------------------------------------------------- اعمال بدهي براي فاکتور فروش (پروژه) - بصورت تک تک کالا گزارش می گیرد
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
case d.IsKala
when 0 then
'فروش ('+ cast(m.codeBargeh as nvarchar)+ ')نام خدمات :' +(select name from tafsili where Code = d.CodeKhadamat2)+'('+ cast((select CodeHesab from Tafsili where Code = d.codeKhadamat2)as nvarchar) +')'+isnull(d.sharheRadif,'')
when 1 then
' فروش ('+ cast(m.codeBargeh as nvarchar)+ ')نام کالا :' +(select name from Kala where Code = d.CodeKala)+'('+(select codekala from Kala where Code = d.CodeKala) +')' + isnull(d.sharheRadif,'') end
when 1 then
'برگشت از فروش ('+ cast(m.codeBargeh as nvarchar) + ')نام کالا :' + (select name from Kala where Code = d.CodeKala) + '(' + (select codekala from Kala where Code = d.CodeKala) + ')' + isnull(d.sharheRadif,'')
when 2 then
'پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar) + ')نام کالا :' + (select name from Kala where Code = d.CodeKala) + '(' + (select codekala from Kala where Code = d.CodeKala) + ')' + isnull(d.sharheRadif,'')
when 3 then
'خريد (' + cast(m.codeBargeh as nvarchar)+ ')نام کالا :' + (select name from Kala where Code = d.CodeKala) + '(' + (select codekala from Kala where Code = d.CodeKala) + ')' + isnull(d.sharheRadif,'')
when 4 then
'برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ')نام کالا :' + (select name from Kala where Code = d.CodeKala) + '(' + (select codekala from Kala where Code = d.CodeKala) + ')' + isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
d.Tedad[Meghdar],
case m.noe
when 0 then
case d.IsKala
when 0 then --Khadamat
(select isnull(name,'') from vahed v where v.code = (select isnull(t.CodeVahed,0) from tafsili t where t.Code = d.CodeKhadamat2))
when 1 then -- Kala
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
end
when 1 then -- BargashtAzForoosh
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
when 3 then -- Kharid
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
when 4 then -- BargashtAzKharid
(select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala))
end [Vahed],
d.Mablagh[Fi],
case m.noe
when 0 then
0.0
when 1 then
cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad ) as decimal(19,4))
when 2 then
0.0
when 3 then
cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad ) as decimal(19,4))
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad) as decimal(19,4))
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad ) as decimal(19,4))
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 2 then 17 -- PishFactorForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
case m.noe
when 0 then 0
when 1 then m.CodeProjeh
when 3 then m.CodeProjeh
when 4 then 0
end [CodeProjehBedehkar],
case m.noe
when 0 then m.CodeProjeh
when 1 then 0
when 3 then 0
when 4 then m.CodeProjeh
end [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat !=1 and
m.noe in (0,1,3,4)/*FactorForoosh ,BargashtAzForoosh , FactorKharid , BargashtAzKharid*/ and
@NoeGardesh = 1 and
cast(m.CodeProjeh as int) = @pcode
union all
select --------------------------------------------------------------------------- اعمال تخفیف ردیف فاکتور فروش (پروژه) - بصورت تک تک کالا گزارش می گیرد
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
case d.IsKala
when 0 then
'تخفیف ردیف فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' +isnull(d.sharheRadif,'')
when 1 then
'تخفیف ردیف فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') end
when 1 then
'تخفیف ردیف برگشت از فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'')
when 2 then
'تخفیف ردیف پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'')
when 3 then
'تخفیف ردیف خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'')
when 4 then
'تخفیف ردیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0 [Meghdar],
'' [Vahed],
isnull(d.MablagheTakhfif,0.0) [Fi],
case m.noe
when 0 then
isnull(d.MablagheTakhfif,0.0)
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
isnull(d.MablagheTakhfif,0.0)
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
0.0
when 1 then
isnull(d.MablagheTakhfif,0.0)
when 2 then
0.0
when 3 then
isnull(d.MablagheTakhfif,0.0)
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 2 then 17 -- PishFactorForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat !=1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and
@NoeGardesh = 1 and
d.MablagheTakhfif > 0 and
cast(m.CodeProjeh as int) = @pcode
union all
select --------------------------------------------------------------------------- اعمال ارزش افزوده ردیف فاکتور فروش (پروژه) - بصورت تک تک کالا گزارش می گیرد
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
case d.IsKala
when 0 then
'ارزش افزوده ردیف فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' +isnull(d.sharheRadif,'')
when 1 then
'ارزش افزوده ردیف فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') end
when 1 then
'ارزش افزوده ردیف برگشت از فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'')
when 2 then
'ارزش افزوده ردیف پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'')
when 3 then
'ارزش افزوده ردیف خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'')
when 4 then
'ارزش افزوده ردیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0 [Meghdar],
'' [Vahed],
isnull(d.MablaghArzeshAfzoodeh,0.0)[Fi],
case m.noe
when 0 then
0.0
when 1 then
isnull(d.MablaghArzeshAfzoodeh,0.0)
when 2 then
0.0
when 3 then
isnull(d.MablaghArzeshAfzoodeh,0.0)
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
isnull(d.MablaghArzeshAfzoodeh,0.0)
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
isnull(d.MablaghArzeshAfzoodeh,0.0)
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 -- BargashtAzForoosh
when 2 then 17 -- PishFactorForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh)
where
m.Vaziat !=1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and
@NoeGardesh = 1 and
d.MablaghArzeshAfzoodeh > 0 and
cast(m.CodeProjeh as int) = @pcode
union all
select --------------------------------------------------------------------------- اضافات و تخفیفات فاکتور فروش و خرید (پروژه) ؛
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'تخفیف فروش (' + cast(m.codeBargeh as nvarchar)+ ')'
when 1 then
'اضافات برگشت از فروش (' + cast(m.codeBargeh as nvarchar)+ ')'
when 2 then ''
when 3 then
'اضافات خرید (' + cast(m.codeBargeh as nvarchar)+ ')'
when 4 then
'تخفیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ')'
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case m.noe
when 0 then
cast((d.mablagh) as decimal(19,4))
when 1 then
cast((d.mablagh) as decimal(19,4))
when 2 then
0.0
when 3 then
cast((d.mablagh) as decimal(19,4))
when 4 then
cast((d.mablagh) as decimal(19,4))
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
0.0
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 --BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from (Bargeh m join daryaftPardakht d on m.code = d.CodeBargehParent)
where
m.Vaziat !=1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and
@NoeGardesh = 1 and
d.NoeMaster = 2 and
d.mablagh > 0 and
cast(m.CodeProjeh as int) = @pcode
union all
select --------------------------------------------------------------------------- اضافات و تخفیفات فاکتور فروش و خرید (پروژه) ؛
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'اضافات فروش (' + cast(m.codeBargeh as nvarchar)+ ')'
when 1 then
'تخفیف برگشت از فروش (' + cast(m.codeBargeh as nvarchar)+ ')'
when 2 then ''
when 3 then
'تخفیف خرید (' + cast(m.codeBargeh as nvarchar)+ ')'
when 4 then
'اضافات برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ')'
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case m.noe
when 0 then
0.0
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
cast((d.mablagh) as decimal(19,4))
when 1 then
cast((d.mablagh) as decimal(19,4))
when 2 then
0.0
when 3 then
cast((d.mablagh) as decimal(19,4))
when 4 then
cast((d.mablagh) as decimal(19,4))
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 --BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
0 [CodeProjehBedehkar],
0 [CodeProjehBestankar]
from (Bargeh m join daryaftPardakht d on m.code = d.CodeBargehParent)
where
m.Vaziat !=1 and
m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and
@NoeGardesh = 1 and
d.NoeMaster = 4 and
d.mablagh > 0 and
cast(m.CodeProjeh as int) = @pcode
union all
select --------------------------------------------------------------------------- اعمال بدهي براي فاکتور فروش (/*پروژه*/) بصورت کلی گزارش می گیرد
0 as [Radif],
m.Tarikh[tarikh],
m.SaatSabt[Saat],
case m.noe
when 0 then
'فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '')
when 1 then
'برگشت از فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '')
when 2 then
'پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '')
when 3 then
'خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '')
when 4 then
'برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '')
when 5 then ''
when 6 then ''
when 7 then ''
when 8 then ''
end as [sharh],
0[Meghdar],
''[Vahed],
0.0[Fi],
case m.noe
when 0 then
0.0
when 1 then
isnull
(
cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0.0))
from BargehDetail d
where d.codeBargeh = m.code) as decimal(19,4))
,0.0) -
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 4 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 2 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.MablaghArzeshAfzoodeh)
from BargehDetail d
where d.codeBargeh = m.code ) as decimal(19,4))
,0.0)
when 2 then
0.0
when 3 then
isnull
(
cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0.0))
from BargehDetail d
where d.codeBargeh = m.code) as decimal(19,4))
,0.0) -
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 4 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 2 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.MablaghArzeshAfzoodeh)
from BargehDetail d
where d.codeBargeh = m.code ) as decimal(19,4))
,0.0)
when 4 then
0.0
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bedehkar],
case m.noe
when 0 then
isnull
(
cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0.0))
from BargehDetail d
where d.codeBargeh = m.code) as decimal(19,4))
,0.0) -
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 2 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 4 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.MablaghArzeshAfzoodeh)
from BargehDetail d
where d.codeBargeh = m.code ) as decimal(19,4))
,0.0)
when 1 then
0.0
when 2 then
0.0
when 3 then
0.0
when 4 then
isnull
(
cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif,0.0))
from BargehDetail d
where d.codeBargeh = m.code) as decimal(19,4))
,0.0) -
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 2 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.mablagh)
from DaryaftPardakht d
where d.codeBargehParent = m.code and
d.noeMaster = 4 and
d.vaziat != 1) as decimal(19,4))
,0.0) +
isnull
(
cast((select sum(d.MablaghArzeshAfzoodeh)
from BargehDetail d
where d.codeBargeh = m.code ) as decimal(19,4))
,0.0)
when 5 then
0.0
when 6 then
0.0
when 7 then
0.0
when 8 then
0.0
end as [Bestankar],
0.0 as [Mandeh],
''[Mahiat],
case m.noe
when 0 then 12 --Foroosh
when 1 then 15 --BargashtAzForoosh
when 3 then 13 -- Kharid
when 4 then 14 -- BargashtAzKharid
end as [NoeRadif],
m.code[code],
0.0[TedadEmtiaz],
0.0[JamEmtiaz],
case m.noe
when 0 then 0
when 1 then m.CodeProjeh
when 3 then m.CodeProjeh
when 4 then 0
end [CodeProjehBedehkar],
case m.noe
when 0 then m.CodeProjeh
when 1 then 0
when 3 then 0
when 4 then m.CodeProjeh
end [CodeProjehBestankar]
from Bargeh m
where
m.Vaziat !=1 and
m.noe in (0,1,3,4)/*FactorForoosh ,BargashtAzForoosh , FactorKharid , BargashtAzKharid*/ and
cast(m.CodeProjeh as int) = @pcode and
@NoeGardesh = 0
order by tarikh , Saat
end
GO