/* prcPersonCardDetail 25 , 207 ,209 select sum(Bestankar) - sum(bedehkar) from prcPersonCardDetail 18 , 0,209 */ /* select * from Tafsili where Code = 18 select * from domain where name = 'HoghooghDastmozdKol' select * from domain where dom_ID = 252 select Id,Title,name from domain where dom_ID = 165 select Id,Title,name from domain where dom_ID = 27 select Id,Title,name from domain where dom_ID = 37 select Id,Title,name from domain where dom_ID = 43 select Id,Title,name from domain where dom_ID = 105 */ IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='prcPersonCardDetail') DROP proc prcPersonCardDetail GO --select * from tafsili where codeHesab = 1001 CREATE proc prcPersonCardDetail @pcode int , @NoeGardesh smallint , @NoeGozaresh int as begin declare @NoeTaf smallInt; set @NoeTaf = (select noe from Tafsili where Code = @pcode) select ------------------------------------------- مانده اول دوره 0[Radif], 0[Tarikh], 0[Saat], cast('مانده اول دوره' as nvarchar(max))[Sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], case t.noe when 50 then -- shakhs case when t.MojoodiAvalDore<0 then abs(t.MojoodiAvalDore) else 0.0 end when 49 then t.MojoodiAvalDore--bank when 51 then t.MojoodiAvalDore--Sandoogh else 0.0 end as [Bedehkar], case t.noe when 50 then -- Shakhs case when t.MojoodiAvalDore>0 then abs(t.MojoodiAvalDore) else 0.0 end else 0.0 end as [Bestankar], 0.0[Mandeh], cast('' as nvarchar(200))[Mahiat], 166 [NoeRadif], t.code[Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from Tafsili t where t.code= @pcode and (@NoeGozaresh = 209 /*نقد*/) union all -------------------------------- ------------------------------------------- پرداخت --------------------- برای بانک و صندوق و غریه بررسی شود --------------------- اگر بدهکار بانک باشد select 0[Radif], case m.NoeDetail when 44 then -- chek (select g.tarikh from GardesheChek g where g.Code = (select max(gg.code) from GardesheChek gg where gg.CodeParent = m.code)) else m.Tarikh end as [Tarikh], m.SaatSabt[Saat], case m.NoeMaster when 38 then case m.noeDetail when 44 then 'دریافت ('+ cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') از '+(select name from tafsili where Code =m.CodeTafsiliBestankar ) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'') +' '+isnull(m.sharh,'') +' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'دریافت حواله بانکي ('+ cast(m.CodeBargeh as nvarchar) + '):'+' ش.حواله ('+m.ShomarehSanad+') از '+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' '+isnull(m.sharh,'') when 46 then 'دریافت وجه ('+ cast(m.CodeBargeh as nvarchar) + ') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') end --when 3 then 'دريافت ('+ cast(m.code as nvarchar) + '):'+' شماره حواله حسابداري ('+d.ShomarehSanad+')' +m.sharh+' '+d.sharh end when 39 then case m.noeDetail when 44 then 'پرداخت ('+ cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'') +' ' +isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'پرداخت حواله بانکي ('+ cast(m.CodeBargeh as nvarchar) + '):'+' ش.حواله ('+m.ShomarehSanad+') از '+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') when 46 then 'پرداخت وجه ('+ cast(m.CodeBargeh as nvarchar) + ') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') end --when 3 then 'دريافت (' + cast(m.code as nvarchar) + '):'+' شماره حواله حسابداري ('+d.ShomarehSanad+')'+m.sharh+' '+d.sharh end when 40 then 'برگه هزينه ('+ cast(m.CodeBargeh as nvarchar) + ') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' + isnull(m.sharh,'') when 41 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 38 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 2 --DaryaftChek when 1 then 1 end --DaryaftChekAvalDoreh when 45 then 3 --DaryaftHavaleh when 46 then 4 --DaryaftVajh end when 39 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 171 --PardakhtChek when 1 then 170 end --PardakhtChekAvalDoreh when 45 then 172 --PardakhtHavaleh when 46 then 173 -- PardakhtVajh end when 40 then 174--Hazineh when 41 then 175--HavalehHesabdari when 42 then 176--DarAmad end [NoeRadif], --2[NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], case m.NoeMaster when 41 then isnull(m.CodeProjehBedehKar,0) /*حواله حسابداری*/ when 40 then isnull(m.CodeProjehBedehKar,0)/*هزینه*/ else 0 end [CodeProjehBedehkar], 0 [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat !=101 and m.CodeBargehParent is null and (m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and m.CodeTafsiliBedehkar = @pcode and @NoeTaf = 49 /*Bank*/ and (m.vaziateChek is null or m.vaziateChek in ( case m.NoeDetail when 44 /*chek*/ then 112 --پاس شده else m.VaziateChek end )) and (@NoeGozaresh = 209 /*نقد*/) union all --------------------- اگر بدهکار صندوق باشد select 0[Radif], case m.NoeDetail when 44 /*chek*/ then ( select g.tarikh from GardesheChek g where g.CodeParent = m.code and g.vaziat = m.vaziateChek ) else m.Tarikh end as [Tarikh], --m.tarikh[Tarikh], m.SaatSabt[Saat], case m.NoeMaster when 38 then case m.noeDetail when 44 then 'دریافت ('+ cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') از '+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' '+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'دریافت حواله بانکي ('+ cast(m.CodeBargeh as nvarchar) + '):'+' ش.حواله ('+m.ShomarehSanad+') از '+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' '+isnull(m.sharh,'') when 46 then 'دریافت وجه ('+ cast(m.CodeBargeh as nvarchar) + ') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') end when 39 then case m.noeDetail when 44 then 'پرداخت ('+ cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' +isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'پرداخت حواله بانکي ('+ cast(m.CodeBargeh as nvarchar) + '):'+' ش.حواله ('+m.ShomarehSanad+') از '+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') when 46 then 'پرداخت وجه ('+ cast(m.CodeBargeh as nvarchar) + ') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') end when 40 then 'برگه هزينه ('+ cast(m.CodeBargeh as nvarchar) + ') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' + isnull(m.sharh,'') when 41 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 38 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 204 --DaryaftChek when 1 then 167 end --DaryaftChekAvalDoreh when 45 then 168 --DaryaftHavaleh when 46 then 169 --DaryaftVajh end when 39 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 171 --PardakhtChek when 1 then 170 end --PardakhtChekAvalDoreh when 45 then 172 --PardakhtHavaleh when 46 then 173 -- PardakhtVajh end when 40 then 174 --Hazineh when 41 then 175 --HavalehHesabdari when 42 then 176 --DarAmad end [NoeRadif], --2[NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], case m.NoeMaster when 41 then isnull(m.CodeProjehBedehKar,0) /*حواله حسابداری*/ when 40 then isnull(m.CodeProjehBedehKar,0)/*هزینه*/ else 0 end [CodeProjehBedehkar], 0 [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat != 101 and m.CodeBargehParent is null and (m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and m.CodeTafsiliBedehkar = @pcode and @NoeTaf = 51 and /*Sandoogh*/ (m.vaziateChek is null or m.vaziateChek in (114 , 116 , 117)) -- نقد شدن چک برگشتي,نقد شدن چک,دريافت نقد از مشتري and (@NoeGozaresh = 209 /*نقد*/) union all --------------------- اگر بدهکار بانک یا صندوق نباشد select 0[Radif], m.tarikh[Tarikh], m.SaatSabt[Saat], case m.NoeMaster when 38 then case m.noeDetail when 44 then 'دریافت ('+ cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') از '+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' '+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'دریافت حواله بانکي ('+ cast(m.CodeBargeh as nvarchar) + '):'+' ش.حواله ('+m.ShomarehSanad+') از '+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' '+isnull(m.sharh,'') when 46 then 'دریافت وجه ('+ cast(m.CodeBargeh as nvarchar) + ') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') end --when 3 then 'دريافت ('+ cast(m.code as nvarchar) + '):'+' شماره حواله حسابداري ('+d.ShomarehSanad+')' +m.sharh+' '+d.sharh end when 39 then case m.noeDetail when 44 then 'پرداخت ('+ cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' +isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'پرداخت حواله بانکي ('+ cast(m.CodeBargeh as nvarchar) + '):'+' ش.حواله ('+m.ShomarehSanad+') از '+(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') when 46 then 'پرداخت وجه ('+ cast(m.CodeBargeh as nvarchar) + ') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' +isnull(m.sharh,'') end --when 3 then 'دريافت (' + cast(m.code as nvarchar) + '):'+' شماره حواله حسابداري ('+d.ShomarehSanad+')'+m.sharh+' '+d.sharh end when 40 then 'برگه هزينه ('+ cast(m.CodeBargeh as nvarchar) + ') از ' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' + isnull(m.sharh,'') when 41 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 38 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 204 --DaryaftChek when 1 then 167 end --DaryaftChekAvalDoreh when 45 then 168 --DaryaftHavaleh when 46 then 169 --DaryaftVajh end when 39 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 171 --PardakhtChek when 1 then 170 end --PardakhtChekAvalDoreh when 45 then 172 --PardakhtHavaleh when 46 then 173 -- PardakhtVajh end when 40 then 174 --Hazineh when 41 then 175 --HavalehHesabdari when 42 then 176--DarAmad end [NoeRadif], --2[NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], case m.NoeMaster when 41 then isnull(m.CodeProjehBedehKar,0) /*حواله حسابداری*/ when 42 then isnull(m.CodeProjehBedehKar,0)/*هزینه*/ else 0 end [CodeProjehBedehkar], 0 [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat != 101 and m.CodeBargehParent is null and (m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and m.CodeTafsiliBedehkar = @pcode and @noeTaf not in (49,51) and (@NoeGozaresh = 209 /*نقد*/) union all ----------------------------------------------- ------------------------------------------- دریافت --------------------- برای بانک و صندوق و غریه بررسی شود --------------------- اگر بستانکار بانک باشد select 0[Radif], case m.NoeDetail when 44 /*chek*/ then (select g.tarikh from GardesheChek g where g.Code = (select max(gg.code) from GardesheChek gg where gg.CodeParent = m.code)) else m.Tarikh end as [Tarikh], m.SaatSabt[Saat], case m.noeMaster when 38 then case m.noeDetail when 44 then 'دریافت (' + cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' +isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'دریافت حواله بانکي (' + cast(m.CodeBargeh as nvarchar) + ') ش.حواله ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') when 46 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 39 then case m.noeDetail when 44 then 'پرداخت (' + cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' +isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 45 then 'پرداخت حواله بانکي (' + cast(m.CodeBargeh as nvarchar) + ') ش.حواله ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') when 46 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 40 then 'برگه هزينه ('+ cast(m.CodeBargeh as nvarchar) + ') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') when 41 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 38 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 204 --DaryaftChek when 1 then 167 end --DaryaftChekAvalDoreh when 45 then 168 --DaryaftHavaleh when 46 then 169 --DaryaftVajh end when 39 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 171 --PardakhtChek when 1 then 170 end --PardakhtChekAvalDoreh when 45 then 172 --PardakhtHavaleh when 46 then 173 -- PardakhtVajh end when 40 then 174 --Hazineh when 41 then 175 --HavalehHesabdari when 42 then 176 --DarAmad end [NoeRadif], --3 [NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], case m.NoeMaster when 41 then isnull(m.CodeProjehBestanKar,0) /*حواله حسابداری*/ when 42 then isnull(m.CodeProjehBestanKar,0)/*هزینه*/ else 0 end [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat !=101 and m.CodeBargehParent is null and (m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and m.CodeTafsiliBestankar = @pcode and @NoeTaf = 49 /*Bank*/ and (m.vaziateChek is null or m.vaziateChek in ( case m.NoeDetail when 44 then 112 else m.VaziateChek end )) and (@NoeGozaresh = 209 /*نقد*/) union all --------------------- اگر بستانکار صندوق باشد select 0[Radif], m.tarikh[Tarikh], m.SaatSabt[Saat], case m.noeMaster when 38 then case m.noeDetail when 44 then 'دریافت (' + cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' +isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'دریافت حواله بانکي (' + cast(m.CodeBargeh as nvarchar) + ') ش.حواله ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') when 46 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 39 then case m.noeDetail when 44 then 'پرداخت (' + cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' +isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'پرداخت حواله بانکي (' + cast(m.CodeBargeh as nvarchar) + ') ش.حواله ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') when 46 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 40 then 'برگه هزينه ('+ cast(m.CodeBargeh as nvarchar) + ') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') when 41 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 38 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 204 --DaryaftChek when 1 then 167 end --DaryaftChekAvalDoreh when 45 then 168 --DaryaftHavaleh when 46 then 169 --DaryaftVajh end when 39 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 171 --PardakhtChek when 1 then 170 end --PardakhtChekAvalDoreh when 45 then 172 --PardakhtHavaleh when 46 then 173 -- PardakhtVajh end when 40 then 174 --Hazineh when 41 then 175 --HavalehHesabdari when 42 then 176 --DarAmad end [NoeRadif], --3 [NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], case m.NoeMaster when 41 then isnull(m.CodeProjehBestanKar,0) /*حواله حسابداری*/ when 40 then isnull(m.CodeProjehBestanKar,0)/*هزینه*/ else 0 end [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat !=101 and m.CodeBargehParent is null and (m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and m.CodeTafsiliBestankar = @pcode and @NoeTaf = 51 /*Sandoogh*/ and m.NoeDetail != 44 /*Chek*/ and (@NoeGozaresh = 209 /*نقد*/) union all --------------------- اگر بستانکار صندوق یا بانک نباشد select 0[Radif], m.tarikh[Tarikh], m.SaatSabt[Saat], case m.noeMaster when 38 then case m.noeDetail when 44 then 'دریافت (' + cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' +isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'دریافت حواله بانکي (' + cast(m.CodeBargeh as nvarchar) + ') ش.حواله ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') when 46 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 39 then case m.noeDetail when 44 then 'پرداخت (' + cast(m.CodeBargeh as nvarchar) + '):'+'چک شماره ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' +isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'پرداخت حواله بانکي (' + cast(m.CodeBargeh as nvarchar) + ') ش.حواله ('+m.ShomarehSanad+') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') when 46 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 40 then 'برگه هزينه ('+ cast(m.CodeBargeh as nvarchar) + ') به '+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') when 41 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 38 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 204 --DaryaftChek when 1 then 167 end --DaryaftChekAvalDoreh when 45 then 168 --DaryaftHavaleh when 46 then 169 --DaryaftVajh end when 39 then case m.noeDetail when 44 then case m.IsAvalDoreh when 0 then 171 --PardakhtChek when 1 then 170 end --PardakhtChekAvalDoreh when 45 then 172 --PardakhtHavaleh when 46 then 173 -- PardakhtVajh end when 40 then 174 --Hazineh when 41 then 175 --HavalehHesabdari when 42 then 176 --DarAmad end [NoeRadif], --3 [NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], case m.NoeMaster when 41 then isnull(m.CodeProjehBestanKar,0) /*حواله حسابداری*/ when 40 then isnull(m.CodeProjehBestanKar,0)/*هزینه*/ else 0 end [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat !=101 and m.CodeBargehParent is null and (m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and m.CodeTafsiliBestankar = @pcode and @noeTaf not in (49,51) -- بانک , صندوق and (@NoeGozaresh = 209 /*نقد*/) 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.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'') + ' '+isnull(m.sharh,'') + ' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' + case d.IsChekOwner -- when 0 then 'شريک چک' -- when 1 then 'صاحب چک' end -- when 1 then 'پرداخت حواله بانکي به چند نفر (' + cast(m.CodeBargeh as nvarchar)+') :' +'حواله شماره ('+m.ShomarehSanad+')' +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+ ' '+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 !=101 and -- m.CodeTafsiliBedehkar is null and -- d.CodeTafsiliMoshtari = @pcode -- and (@NoeGozaresh = 209 /*نقد*/) --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.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'') + ' '+isnull(m.sharh,'') +' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')'+ case d.IsChekOwner -- when 0 then 'شريک چک' -- when 1 then 'صاحب چک' end -- when 1 then 'دريافت حواله بانکی از چند نفر (' + cast(m.CodeBargeh as nvarchar)+') :'+ 'حواله شماره ('+m.ShomarehSanad+')' +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'') + ' '+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 !=101 and -- m.CodeTafsiliBestankar is null and -- d.CodeTafsiliMoshtari = @pcode -- and (@NoeGozaresh = 209 /*نقد*/) --union all select --------------------------------------------------------------------------- اعمال بدهي براي فاکتور فروش - بصورت تک تک کالا گزارش می گیرد 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 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 29 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 30 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 31 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 32 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 33 then '' when 34 then '' when 35 then '' when 36 then '' end as [sharh], d.Tedad[Meghdar], case m.noe when 28 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 29 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 31 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 32 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 28 then cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad ) as decimal(19,4)) when 29 then 0.0 when 30 then 0.0 when 31 then 0.0 when 32 then cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad) as decimal(19,4)) when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bedehkar], case m.noe when 28 then 0.0 when 29 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 30 then 0.0 when 31 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 32 then 0.0 when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 30 then 182 -- PishFactorForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and @NoeGardesh = 207 and cast(m.CodeMoshtari as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اعمال تخفیف ردیف فاکتور فروش - بصورت تک تک کالا گزارش می گیرد 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 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 29 then 'تخفیف ردیف برگشت از فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'') when 30 then 'تخفیف ردیف پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'') when 31 then 'تخفیف ردیف خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') when 32 then 'تخفیف ردیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') when 33 then '' when 34 then '' when 35 then '' when 36 then '' end as [sharh], 0 [Meghdar], '' [Vahed], isnull(d.MablagheTakhfif,0.0) [Fi], case m.noe when 28 then 0.0 when 29 then isnull(d.MablagheTakhfif,0.0) when 30 then 0.0 when 31 then isnull(d.MablagheTakhfif,0.0) when 32 then 0.0 when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bedehkar], case m.noe when 28 then isnull(d.MablagheTakhfif,0.0) when 29 then 0.0 when 30 then 0.0 when 31 then 0.0 when 32 then isnull(d.MablagheTakhfif,0.0) when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 30 then 182 -- PishFactorForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and m.noe not in (2,5,6,7)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and @NoeGardesh = 207 and d.MablagheTakhfif > 0 and cast(m.CodeMoshtari as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اعمال ارزش افزوده ردیف فاکتور فروش - بصورت تک تک کالا گزارش می گیرد 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 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 29 then 'ارزش افزوده ردیف برگشت از فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'') when 30 then 'ارزش افزوده ردیف پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'') when 31 then 'ارزش افزوده ردیف خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') when 32 then 'ارزش افزوده ردیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') when 33 then '' when 34 then '' when 35 then '' when 36 then '' end as [sharh], 0 [Meghdar], '' [Vahed], isnull(d.MablaghArzeshAfzoodeh,0.0)[Fi], case m.noe when 28 then isnull(d.MablaghArzeshAfzoodeh,0.0) when 29 then 0.0 when 30 then 0.0 when 31 then 0.0 when 32 then isnull(d.MablaghArzeshAfzoodeh,0.0) when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bedehkar], case m.noe when 28 then 0.0 when 29 then isnull(d.MablaghArzeshAfzoodeh,0.0) when 30 then 0.0 when 31 then isnull(d.MablaghArzeshAfzoodeh,0.0) when 32 then 0.0 when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 30 then 182 -- PishFactorForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and @NoeGardesh = 207 and d.MablaghArzeshAfzoodeh > 0 and cast(m.CodeMoshtari as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اعمال بدهي براي فاکتور فروش بصورت کلی گزارش می گیرد 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 then 'فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '') when 29 then 'برگشت از فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '') when 30 then 'پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '') when 31 then 'خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '') when 32 then 'برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '') when 33 then '' when 34 then '' when 35 then '' when 36 then '' end as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], case m.noe when 28 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 = 40 and d.vaziat != 101) as decimal(19,4)) ,0.0) + isnull ( cast((select sum(d.mablagh) from DaryaftPardakht d where d.codeBargehParent = m.code and d.noeMaster = 42 and d.vaziat != 101) 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 29 then 0.0 when 30 then 0.0 when 31 then 0.0 when 32 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 = 40 and d.vaziat != 101) as decimal(19,4)) ,0.0) + isnull ( cast((select sum(d.mablagh) from DaryaftPardakht d where d.codeBargehParent = m.code and d.noeMaster = 42 and d.vaziat != 101) 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 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bedehkar], case m.noe when 28 then 0.0 when 29 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 = 42 and d.vaziat != 101) as decimal(19,4)) ,0.0) + isnull ( cast((select sum(d.mablagh) from DaryaftPardakht d where d.codeBargehParent = m.code and d.noeMaster = 40 and d.vaziat != 101) 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 30 then 0.0 when 31 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 = 42 and d.vaziat != 101) as decimal(19,4)) ,0.0) + isnull ( cast((select sum(d.mablagh) from DaryaftPardakht d where d.codeBargehParent = m.code and d.noeMaster = 40 and d.vaziat != 101) 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 32 then 0.0 when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 --BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and cast(m.CodeMoshtari as int) = @pcode and @NoeGardesh = 206 and (@NoeGozaresh = 209 /*نقد*/) union all --*********** select ------------------------------------------------------------------------- ويزيتوري کل فاکتور 0[Radif], b.Tarikh[tarikh], b.SaatSabt[Saat], case b.Noe when 28 then 'ويزيتوري فروش (' + cast( b.CodeBargeh as nvarchar) + ')' when 29 then 'ويزيتوري برگشت از فروش (' + cast( b.CodeBargeh as nvarchar) + ')' when 30 then 'ويزيتوري پيش فاکتور فروش (' + cast( b.CodeBargeh as nvarchar) + ')' when 31 then 'ويزيتوري خريد (' + cast( b.CodeBargeh as nvarchar) + ')' when 32 then 'ويزيتوري برگشت از خريد (' + cast( b.CodeBargeh as nvarchar) + ')' when 33 then 'ويزيتوري پيش فاکتور خريد (' + cast( b.CodeBargeh as nvarchar) + ')' when 34 then 'ويزيتوري حواله انبار (' + cast( b.CodeBargeh as nvarchar) + ')' when 35 then 'ويزيتوري برگشت به انبار (' + cast( b.CodeBargeh as nvarchar) + ')' when 36 then 'ويزيتوري انتقال بين انبار (' + cast( b.CodeBargeh as nvarchar) + ')' end as[Sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], case b.noe when 28 then 0.0 -- Foroosh when 29 then b.MablagheVisitor -- BargashtAzForoosh when 31 then 0.0-- Kharid when 32 then b.MablagheVisitor -- BargashtAzKharid end [BedehKar], case b.noe when 28 then b.MablagheVisitor -- Foroosh when 29 then 0.0 -- BargashtAzForoosh when 31 then b.MablagheVisitor -- Kharid when 32 then 0.0 -- BargashtAzKharid end [BestanKar], 0.0 [Mandeh], ''[Mahiat], case b.noe when 28 then 177 --Foroosh when 29 then 180 --BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and b.noe not in (31,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and b.CodeVisitor = @pcode and b.MablagheVisitor is not null and b.MablagheVisitor > 0 and b.MablagheVisitor is not null and (@NoeGozaresh = 209 /*نقد*/) union all ------------------------------------------------------------------------- ويزيتوري در تک تک کالاها select 0[Radif], m.tarikh[Tarikh], m.SaatSabt[Saat], case m.Noe when 28 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 31 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 29 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 32 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 28 then 0.0 -- Foroosh when 29 then d.MablagheVisitor -- BargashtAzForoosh when 31 then 0.0-- Kharid when 32 then d.MablagheVisitor -- BargashtAzKharid end [Bedehkar], case m.noe when 28 then d.MablagheVisitor -- Foroosh when 29 then 0.0 -- BargashtAzForoosh when 31 then d.MablagheVisitor -- Kharid when 32 then 0.0 -- BargashtAzKharid end [Bestankar], 0.0[Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 31 then 179 -- Kharid when 32 then 179 -- 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 != 101 and m.noe not in (30,33,34,35)/*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 and (@NoeGozaresh = 209 /*نقد*/) union all -------------------------------------------------------------------------تخصیص خدمات فاکتور فروش select 0[Radif], m.tarikh[Tarikh], m.SaatSabt[Saat], case m.Noe when 28 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 28 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 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 31 then 179 -- Kharid when 32 then 179 -- 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 != 101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and cast(d.CodeTakhsisKhadamat as int) = @pcode and d.MablaghTakhsisKhadamat is not null and d.MablaghTakhsisKhadamat > 0 and m.noe = 28 and (@NoeGozaresh = 209 /*نقد*/) union all ------------------------------------------------------------------------------------------ اعمال مبالغ در فاکتورها -------------------------------------------- باید برای حالت بانک و صندوق و بقیه بررسی شود -------------------------------------------- اگر بدهکار بانک باشد select 0[Radif], case m.noeDetail when 44 then (select g.tarikh from GardesheChek g where g.Code = (select max(gg.code) from GardesheChek gg where gg.CodeParent = m.code)) else m.tarikh end as [Tarikh], m.SaatSabt[Saat], case (select b.Noe from Bargeh b where b.code = m.codeBargehParent) when 28 then case M.NoeDetail when 44 then 'چک طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 29 then case M.NoeDetail when 44 then 'چک طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 30 then case M.NoeDetail when 44 then 'چک طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکی طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وچه طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 31 then case M.NoeDetail when 44 then 'چک طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ') '+' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 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 32 then case M.NoeDetail when 44 then 'چک طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 33 then '' when 34 then '' when 35 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 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 30 then 182 -- PishFactorForoosh when 31 then 179 -- Kharid when 32 then 179 -- 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 != 101 and m.CodeBargehParent is not null and m.CodeTafsiliBedehkar = @pcode and @NoeTaf = 49 /*Bank*/ and (m.vaziateChek is null or m.vaziateChek in ( case m.NoeDetail when 44 then 112 else m.VaziateChek end )) and (@NoeGozaresh = 209 /*نقد*/) union all -------------------------------------------- اگر بدهکار صندوق باشد select 0[Radif], --(select b.tarikh from Bargeh b where b.code = m.codeBargehParent) as [Tarikh], case m.NoeDetail when 44 then ( select g.tarikh from GardesheChek g where g.CodeParent = m.code and g.vaziat = m.vaziateChek ) else m.Tarikh end as [Tarikh], m.SaatSabt[Saat], case (select b.Noe from Bargeh b where b.code = m.codeBargehParent) when 28 then case M.NoeDetail when 44 then 'چک طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46then 'وجه طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 29 then case M.NoeDetail when 44 then 'چک طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 30 then case M.NoeDetail when 44 then 'چک طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکی طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 31 then case M.NoeDetail when 44 then 'چک طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ') '+' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه نقد طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 32 then case M.NoeDetail when 44 then 'چک طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 33 then '' when 34 then '' when 35 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 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 30 then 182 -- PishFactorForoosh when 31 then 179 -- Kharid when 32 then 179 -- 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 != 101 and m.CodeBargehParent is not null and m.CodeTafsiliBedehkar = @pcode and @NoeTaf = 51 and/*Sandoogh*/ (m.vaziateChek is null or m.vaziateChek in (114 , 116 , 117)) /*Chek*/ and (@NoeGozaresh = 209 /*نقد*/) 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 28 then case M.NoeDetail when 44 then 'چک طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ') '+isnull(m.sharh,'') end when 29 then case M.NoeDetail when 44 then 'چک طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 30 then case M.NoeDetail when 44 then 'چک طی پیش فاکتور فروش(' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکی طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 31 then case M.NoeDetail when 44 then 'چک طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ') '+' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه نقد طي خريد ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 32 then case M.NoeDetail when 44 then 'چک طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 33 then '' when 34 then '' when 35 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 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 30 then 182 -- PishFactorForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 != 101 and m.CodeBargehParent is not null and m.CodeTafsiliBedehkar = @pcode and @noeTaf not in (49,51)-- بانک , صندوق and (@NoeGozaresh = 209 /*نقد*/) -------------------------------------------- باید برای حالت بانک و صندوق و بقیه بررسی شود --------------------------- اگر بستانکار بانک باشد union all select 0[Radif], case m.noeDetail when 44 then (select g.tarikh from GardesheChek g where g.Code = (select max(gg.code) from GardesheChek gg where gg.CodeParent = m.code)) else m.tarikh end as [Tarikh], m.SaatSabt[Saat], case (select b.Noe from Bargeh b where b.code = m.codeBargehParent) when 28 then case m.NoeDetail when 44 then 'چک طي فروش ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' +' شماره چک ('+m.ShomarehSanad+')' +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي فروش ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+m.sharh+' '+isnull(m.sharh,'') end when 29 then case M.NoeDetail when 44 then 'چک طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 30 then case M.NoeDetail when 44 then 'چک طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکی طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 31 then case m.NoeDetail when 44 then 'جک طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه نقد طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 32 then case M.NoeDetail when 44 then 'چک طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 33 then '' when 34 then '' when 35 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 28 then 177 --Foroosh when 29 then 180 --BargashtAzForoosh when 30 then 182 -- PishFactorForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 != 101 and m.CodeBargehParent is not null and m.CodeTafsiliBestankar = @pcode and @NoeTaf = 49 /*Bank*/ and (m.vaziateChek is null or m.vaziateChek in ( case m.NoeDetail when 44 then 112 else m.VaziateChek end )) and (@NoeGozaresh = 209 /*نقد*/) --------------------------- اگر بستانکار صندوق باشد 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 28 then case m.NoeDetail when 44 then 'چک طي فروش ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' +' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي فروش ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+m.sharh+' '+isnull(m.sharh,'') end when 29 then case M.NoeDetail when 44 then 'چک طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 30 then case M.NoeDetail when 44 then 'چک طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکی طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 31 then case m.NoeDetail when 44 then 'جک طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه نقد طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 32 then case M.NoeDetail when 44 then 'چک طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 33 then '' when 34 then '' when 35 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 28 then 177 --Foroosh when 29 then 180 --BargashtAzForoosh when 30 then 182 --PishFactorForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 != 101 and m.CodeBargehParent is not null and m.CodeTafsiliBestankar = @pcode and @NoeTaf = 51 /*Sandoogh*/ and m.noeDetail != 44 /*Chek*/ and (@NoeGozaresh = 209 /*نقد*/) --------------------------- اگر بستانکار بانک یا صندوق نباشد 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 28 then case m.NoeDetail when 44 then 'چک طي فروش ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' +' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي فروش ('+ cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 29 then case M.NoeDetail when 44 then 'چک طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 30 then case M.NoeDetail when 44 then 'چک طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکی طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طی پیش فاکتور فروش (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 31 then case m.NoeDetail when 44 then 'جک طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره چک ('+m.ShomarehSanad+')'+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhsodoor,0))+')' when 45 then 'حواله بانکي طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'') when 46 then 'وجه نقد طي خريد (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 32 then case M.NoeDetail when 44 then 'چک طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):' + ' شماره چک ('+m.ShomarehSanad+')'+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+isnull(m.sharh,'')+' (' + dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 45 then 'حواله بانکي طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ '):'+' شماره حواله ('+m.ShomarehSanad+')'+isnull(m.sharh,'') when 46 then 'وجه طي برگشت از خرید (' + cast((select CodeBargeh from Bargeh b where b.code = m.codeBargehParent ) as nvarchar)+ ')'+isnull(m.sharh,'') end when 33 then '' when 34 then '' when 35 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 28 then 177 --Foroosh when 29 then 180 --BargashtAzForoosh when 30 then 182 --PishFactorForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 != 101 and m.CodeBargehParent is not null and m.CodeTafsiliBestankar = @pcode and @NoeTaf not in (49,51)-- بانک , صندوق and (@NoeGozaresh = 209 /*نقد*/) union all -------------------------------------------------------- برای خدمات و هزینه در فاکتور خرید و فروش select 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 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 29 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 30 then '' when 31 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 32 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 33 then '' when 34 then '' when 35 then '' when 36 then '' end as [sharh], d.Tedad[Meghdar], case m.noe when 28 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 29 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 31 then (select isnull(name,'') from vahed v where v.code = (select isnull(k.CodeVahed,0) from Kala k where k.Code = d.CodeKala)) when 32 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 28 then 0.0 when 29 then cast((d.mablagh * d.Tedad - isnull(d.MablagheTakhfif , 0.0)) as decimal(19,4)) -- هزینه when 30 then 0.0 when 31 then cast((d.mablagh * d.Tedad - isnull(d.MablagheTakhfif , 0.0)) as decimal(19,4)) -- هزینه when 32 then 0.0 when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bedehkar], case m.noe when 28 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)=52 then isnull(d.mablaghtakhsisKhadamat , 0.0) else 0 end when 29 then 0.0 when 30 then 0.0 when 31 then 0.0 when 32 then 0.0 when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 <> 101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and d.mablagh > 0 and cast(d.CodeKhadamat2 as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) -------------------------------------------------------- گردش چک ها --------------------------------------------------------------------------------- گردش چک دریافتی --------------------------------------------------------------------------- باید برای بانک و صندوق و غیره بررسی شود -------------------------------------------------------------- اگر بدهکار بانک باشد union all select 0[Radif], (select g.tarikh from GardesheChek g where g.Code = (select max(gg.code) from GardesheChek gg where gg.CodeParent = m.code)) as [Tarikh], d.Saat[Saat], case d.Vaziat when 106 then 'دریافت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'در جریان وصول '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 107 then 'برگشت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'از '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 108 then 'چک پاک شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 109 then 'چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 110 then 'چک موجود ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 111 then 'چک ('+cast(m.shomarehSanad as nvarchar)+') '+'برگشت به مشتری '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 112 then 'چک پاس شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 113 then 'چک برگشت از مشتری ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 114 then 'دریافت نقدی از مشتری ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 115 then 'پس دادن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 116 then 'نقد شدن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 117 then 'نقد شدن چک ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 118 then 'موجود شدن چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' 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 38 then -- گردش چک دریافتی case d.Vaziat when 106 then 188 --GardeshChekDaryaftiDarJaryanVosool when 107 then 189 --GardeshChekDaryaftiBargashtChek when 108 then 190 --GardeshChekDaryaftiDeleted when 109 then 191 --GardeshChekDaryaftiKharjShodeh when 110 then 192 --GardeshChekDaryaftiMojood when 111 then 193 --GardeshChekDaryaftiBargashtBeMoshtari when 112 then 194 --GardeshChekDaryaftiPassShodeh --when 113 then -- در پرداخت کاربرد دارد when 114 then 195 --GardeshChekDaryaftiDaryaftNaghdi when 115 then 196 --GardeshChekDaryaftiPasDadanChekBargashti when 116 then 197 --GardeshChekDaryaftiNaghdShodanChekBargashti when 117 then 198 --GardeshChekDaryaftiNaghdKardanChek when 118 then 199 --GardeshDaryaftMojoodShodanChekKharjShodeh else 1000 end when 39 then -- گردش چک پرداختی case d.Vaziat when 106 then 184 --GardeshChekPardakhtiDarJaryanVosool when 107 then 185 --GardeshChekPardakhtiBargashtChek when 112 then 186 --GardeshChekPardakhtiPassShodan when 113 then 187 --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 != 101 and d.CodeBedehKar = @pcode and @NoeTaf = 49 and m.vaziateChek = ( case @NoeTaf when 49 then 112 else 1000 end -- عدد بزرگ که هیچ وقت تورو نشود ) and (@NoeGozaresh = 209 /*نقد*/) union all -------------------------------------------------------------- اگر بدهکار صندوق باشد select 0[Radif], (select g.tarikh from GardesheChek g where g.Code = (select max(gg.code) from GardesheChek gg where gg.CodeParent = m.code)) as [Tarikh], --d.tarikh as [Tarikh], d.Saat[Saat], --case m.VaziateChek case d.Vaziat when 106 then 'دریافت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'در جریان وصول '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 107 then 'برگشت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'از '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 108 then 'چک پاک شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 109 then 'چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 110 then 'چک موجود ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 111 then 'چک ('+cast(m.shomarehSanad as nvarchar)+') '+'برگشت به مشتری '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 112 then 'چک پاس شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 113 then 'چک برگشت از مشتری ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 114 then 'دریافت نقدی از مشتری ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 115 then 'پس دادن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 116 then 'نقد شدن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 117 then 'نقد شدن چک ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 118 then 'موجود شدن چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' 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 38 then -- گردش چک دریافتی case d.Vaziat when 106 then 188 --GardeshChekDaryaftiDarJaryanVosool when 107 then 189 --GardeshChekDaryaftiBargashtChek when 108 then 190 --GardeshChekDaryaftiDeleted when 109 then 191 --GardeshChekDaryaftiKharjShodeh when 110 then 192 --GardeshChekDaryaftiMojood when 111 then 193 --GardeshChekDaryaftiBargashtBeMoshtari when 112 then 194 --GardeshChekDaryaftiPassShodeh --when 113 then -- در پرداخت کاربرد دارد when 114 then 195 --GardeshChekDaryaftiDaryaftNaghdi when 115 then 196 --GardeshChekDaryaftiPasDadanChekBargashti when 116 then 197 --GardeshChekDaryaftiNaghdShodanChekBargashti when 117 then 198 --GardeshChekDaryaftiNaghdKardanChek when 118 then 199 --GardeshDaryaftMojoodShodanChekKharjShodeh else 1000 end when 39 then -- گردش چک پرداختی case d.Vaziat when 106 then 184 --GardeshChekPardakhtiDarJaryanVosool when 107 then 185 --GardeshChekPardakhtiBargashtChek when 112 then 186 --GardeshChekPardakhtiPassShodan when 113 then 187 --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 != 101 and --Not Deleted d.CodeBedehKar = @pcode and @NoeTaf = 51 and d.vaziat in (109) --KharjShode and (@NoeGozaresh = 209 /*نقد*/) union all -------------------------------------------------------------- اگر بدهکار صندوق یا بانک نباشد select 0[Radif], d.Tarikh as [Tarikh], d.Saat[Saat], case d.Vaziat when 106 then 'دریافت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'در جریان وصول '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 107 then 'برگشت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'از '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 108 then 'چک پاک شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 109 then 'چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 110 then 'چک موجود ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 111 then 'چک ('+cast(m.shomarehSanad as nvarchar)+') '+'برگشت به مشتری '+(select name from tafsili where code= d.CodeBestanKar) +' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 112 then 'چک پاس شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 113 then 'چک برگشت از مشتری ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 114 then 'دریافت نقدی از مشتری ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 115 then 'پس دادن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 116 then 'نقد شدن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 117 then 'نقد شدن چک ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' when 118 then 'موجود شدن چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.Tarikhevosool,0))+')' 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 38 then -- گردش چک دریافتی case d.Vaziat when 106 then 188 --GardeshChekDaryaftiDarJaryanVosool when 107 then 189 --GardeshChekDaryaftiBargashtChek when 108 then 190 --GardeshChekDaryaftiDeleted when 109 then 191 --GardeshChekDaryaftiKharjShodeh when 110 then 192 --GardeshChekDaryaftiMojood when 111 then 193 --GardeshChekDaryaftiBargashtBeMoshtari when 112 then 194 --GardeshChekDaryaftiPassShodeh --when 113 then -- در پرداخت کاربرد دارد when 114 then 195 --GardeshChekDaryaftiDaryaftNaghdi when 115 then 196 --GardeshChekDaryaftiPasDadanChekBargashti when 116 then 197 --GardeshChekDaryaftiNaghdShodanChekBargashti when 117 then 198 --GardeshChekDaryaftiNaghdKardanChek when 118 then 199 --GardeshDaryaftMojoodShodanChekKharjShodeh else 1000 end when 39 then -- گردش چک پرداختی case d.Vaziat when 106 then 184 --GardeshChekPardakhtiDarJaryanVosool when 107 then 185 --GardeshChekPardakhtiBargashtChek when 112 then 186 --GardeshChekPardakhtiPassShodan when 113 then 187 --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 != 101 and d.CodeBedehKar = @pcode and @NoeTaf not in (49,51)-- بانک , صندوق and (@NoeGozaresh = 209 /*نقد*/) union all --------------------------------------------------------------------------------- گردش چک پرداختی ------------------------------------ باید برای بانک و صندوق و غیره بررسی شود ---------------------------------------------- اگر بستانکار بانک باشد select 0[Radif], d.Tarikh as [Tarikh], d.Saat[Saat], case d.Vaziat when 106 then 'پرداخت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'در جریان وصول '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 107 then 'برگشت چک ('+cast(m.shomarehSanad as nvarchar)+')از '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 108 then 'چک پاک شده ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 109 then 'چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 110 then 'چک موجود ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 111 then 'چک ('+cast(m.shomarehSanad as nvarchar)+') '+'برگشت به مشتری'+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 112 then 'چک پاس شده ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 113 then 'چک برگشت از مشتری ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 114 then 'دریافت نقدی از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 115 then 'پس دادن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 116 then 'نقد شدن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 117 then 'نقد شدن چک ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 118 then 'موجود شدن چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' 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 38 then -- گردش چک دریافتی case d.Vaziat when 106 then 188 --GardeshChekDaryaftiDarJaryanVosool when 107 then 189 --GardeshChekDaryaftiBargashtChek when 108 then 190 --GardeshChekDaryaftiDeleted when 109 then 191 --GardeshChekDaryaftiKharjShodeh when 110 then 192 --GardeshChekDaryaftiMojood when 111 then 193 --GardeshChekDaryaftiBargashtBeMoshtari when 112 then 194 --GardeshChekDaryaftiPassShodeh --when 113 then -- در پرداخت کاربرد دارد when 114 then 195 --GardeshChekDaryaftiDaryaftNaghdi when 115 then 196 --GardeshChekDaryaftiPasDadanChekBargashti when 116 then 197 --GardeshChekDaryaftiNaghdShodanChekBargashti when 117 then 198 --GardeshChekDaryaftiNaghdKardanChek when 118 then 199 --GardeshDaryaftMojoodShodanChekKharjShodeh else 1000 end when 39 then -- گردش چک پرداختی case d.Vaziat when 106 then 184 --GardeshChekPardakhtiDarJaryanVosool when 107 then 185 --GardeshChekPardakhtiBargashtChek when 112 then 186 --GardeshChekPardakhtiPassShodan when 113 then 187 --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 != 101 and d.CodeBestanKar = @pcode and @NoeTaf = 49 and m.vaziateChek = ( case @NoeTaf when 49 /*Bank */ then 112 else 1000 end -- عدد بزرگ که هیچ وقت تورو نشود ) and (@NoeGozaresh = 209 /*نقد*/) union all ---------------------------------------------- اگر بستانکار صندوق باشد select 0[Radif], d.Tarikh as [Tarikh], d.Saat[Saat], case d.Vaziat when 106 then 'پرداخت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'در جریان وصول '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 107 then 'برگشت چک ('+cast(m.shomarehSanad as nvarchar)+')از '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 108 then 'چک پاک شده ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 109 then 'چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 110 then 'چک موجود ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 111 then 'چک ('+cast(m.shomarehSanad as nvarchar)+') '+'برگشت به مشتری'+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 112 then 'چک پاس شده ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 113 then 'چک برگشت از مشتری ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 114 then 'دریافت نقدی از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 115 then 'پس دادن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 116 then 'نقد شدن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 117 then 'نقد شدن چک ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 118 then 'موجود شدن چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' 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 38 then -- گردش چک دریافتی case d.Vaziat when 106 then 188 --GardeshChekDaryaftiDarJaryanVosool when 107 then 189 --GardeshChekDaryaftiBargashtChek when 108 then 190 --GardeshChekDaryaftiDeleted when 109 then 191 --GardeshChekDaryaftiKharjShodeh when 110 then 192 --GardeshChekDaryaftiMojood when 111 then 193 --GardeshChekDaryaftiBargashtBeMoshtari when 112 then 194 --GardeshChekDaryaftiPassShodeh --when 113 then -- در پرداخت کاربرد دارد when 114 then 195 --GardeshChekDaryaftiDaryaftNaghdi when 115 then 196 --GardeshChekDaryaftiPasDadanChekBargashti when 116 then 197 --GardeshChekDaryaftiNaghdShodanChekBargashti when 117 then 198 --GardeshChekDaryaftiNaghdKardanChek when 118 then 199 --GardeshDaryaftMojoodShodanChekKharjShodeh else 1000 end when 39 then -- گردش چک پرداختی case d.Vaziat when 106 then 184 --GardeshChekPardakhtiDarJaryanVosool when 107 then 185 --GardeshChekPardakhtiBargashtChek when 112 then 186 --GardeshChekPardakhtiPassShodan when 113 then 187 --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 != 101 and d.CodeBestanKar = @pcode and @NoeTaf = 51 and d.vaziat = 118 -- MojoodShodanChekKharjShodeh and (@NoeGozaresh = 209 /*نقد*/) union all ---------------------------------------------- اگر بستانکار بانک یا صندوق نباشد select 0[Radif], d.Tarikh as [Tarikh], d.Saat[Saat], case d.Vaziat when 106 then 'پرداخت چک ('+cast(m.shomarehSanad as nvarchar)+') '+'در جریان وصول '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 107 then 'برگشت چک ('+cast(m.shomarehSanad as nvarchar)+')از '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 108 then 'چک پاک شده ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 109 then 'چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 110 then 'چک موجود ('+cast(m.shomarehSanad as nvarchar)+') ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 111 then 'چک ('+cast(m.shomarehSanad as nvarchar)+') '+'برگشت به مشتری'+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 112 then 'چک پاس شده ('+cast(m.shomarehSanad as nvarchar)+') ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 113 then 'چک برگشت از مشتری ('+cast(m.shomarehSanad as nvarchar)+') '+(select name from tafsili where code= d.CodeBedehKar) +' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 114 then 'دریافت نقدی از مشتری ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 115 then 'پس دادن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 116 then 'نقد شدن چک برگشتی ('+cast(m.shomarehSanad as nvarchar)+')'+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 117 then 'نقد شدن چک ش.چک ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' when 118 then 'موجود شدن چک خرج شده ('+cast(m.shomarehSanad as nvarchar)+') '+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'')+' ' + isnull(d.sharh , '')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' 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 38 then -- گردش چک دریافتی case d.Vaziat when 106 then 188 --GardeshChekDaryaftiDarJaryanVosool when 107 then 189 --GardeshChekDaryaftiBargashtChek when 108 then 190 --GardeshChekDaryaftiDeleted when 109 then 191 --GardeshChekDaryaftiKharjShodeh when 110 then 192 --GardeshChekDaryaftiMojood when 111 then 193 --GardeshChekDaryaftiBargashtBeMoshtari when 112 then 194 --GardeshChekDaryaftiPassShodeh --when 113 then -- در پرداخت کاربرد دارد when 114 then 195 --GardeshChekDaryaftiDaryaftNaghdi when 115 then 196 --GardeshChekDaryaftiPasDadanChekBargashti when 116 then 197 --GardeshChekDaryaftiNaghdShodanChekBargashti when 117 then 198 --GardeshChekDaryaftiNaghdKardanChek when 118 then 199 --GardeshDaryaftMojoodShodanChekKharjShodeh else 1000 end when 39 then -- گردش چک پرداختی case d.Vaziat when 106 then 184 --GardeshChekPardakhtiDarJaryanVosool when 107 then 185 --GardeshChekPardakhtiBargashtChek when 112 then 186 --GardeshChekPardakhtiPassShodan when 113 then 187 --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 != 101 and d.CodeBestanKar = @pcode and @NoeTaf not in (49,51)-- بانک , صندوق and (@NoeGozaresh = 209 /*نقد*/) 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.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'') + isnull(m.sharh,'')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikheVosool,0))+')' as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], m.mablagh as [Bedehkar], 0.0 as [Bestankar], 0.0[Mandeh], ''[Mahiat], 167 [NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat != 101 and m.CodeBargehParent is null and m.IsAvalDoreh = 1 and m.NoeMaster = 38 and -- Daryaft m.NoeDetail = 44 and -- Chek m.CodeTafsiliBedehkar = @pcode and @NoeTaf = 49 /*Bank*/ and m.vaziateChek = 112 and (@NoeGozaresh = 209 /*نقد*/) union all ---------------------------------------- برای صندوق لازم نیست select 0[Radif], --m.tarikh[Tarikh], (select g.Tarikh from GardesheChek g where g.codeParent = m.code and g.Vaziat = m.vaziateChek) [Tarikh], m.SaatSabt[Saat], 'دریافت چک اول دوره ('+ cast(m.code as nvarchar) + '):'+'به شماره ('+m.ShomarehSanad+')'+ (select name from tafsili where code = m.CodeTafsiliBestankar)+' بانک '+ isnull(m.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'') + isnull(m.sharh,'')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikheVosool,0))+')' as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], m.mablagh as [Bedehkar], 0.0 as [Bestankar], 0.0[Mandeh], ''[Mahiat], 167 [NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat != 101 and m.CodeBargehParent is null and m.IsAvalDoreh = 1 and m.NoeMaster = 38 and -- Daryaft m.NoeDetail = 44 and -- Chek m.CodeTafsiliBedehkar = @pcode and @NoeTaf = 51 /*Sandoogh*/ and m.vaziateChek = 117 -- NaghdShodanChek and (@NoeGozaresh = 209 /*نقد*/) 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.NameBank,'') + ' شعبه ' + isnull(m.NameShobe,'') + isnull(m.sharh,'')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikheVosool,0))+')' as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], m.mablagh as [Bedehkar], 0.0 as [Bestankar], 0.0[Mandeh], ''[Mahiat], 167[NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat != 101 and m.CodeBargehParent is null and m.IsAvalDoreh = 1 and m.NoeMaster = 38 and -- Daryaft m.NoeDetail = 44 and -- Chek m.CodeTafsiliBedehkar = @pcode and @NoeTaf not in (49,51)-- بانک , صندوق and (@NoeGozaresh = 209 /*نقد*/) 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,'')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], 0.0 as [Bedehkar], m.mablagh as [Bestankar], 0.0[Mandeh], ''[Mahiat], 170 [NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat != 101 and m.CodeBargehParent is null and m.IsAvalDoreh = 1 and m.NoeMaster = 39 and -- Pardakht m.NoeDetail = 44 and -- Chek m.CodeTafsiliBestankar = @pcode and @NoeTaf =49 /*Bank*/ and m.vaziateChek = 112 and (@NoeGozaresh = 209 /*نقد*/) 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,'')+' تاریخ سررسید چک '+ dbo.ToStringDate(isnull(m.TarikhSodoor,0))+')' as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], 0.0 as [Bedehkar], m.mablagh as [Bestankar], 0.0[Mandeh], ''[Mahiat], 170 [NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat != 101 and m.CodeBargehParent is null and m.IsAvalDoreh = 1 and m.NoeMaster = 39 and -- Pardakht m.NoeDetail = 44 and -- Chek m.CodeTafsiliBestankar = @pcode and @NoeTaf not in (49,51) -- بانک , صندوق and (@NoeGozaresh = 209 /*نقد*/) union all select ------------------------------------------- تخفیفات برگه دیتیل 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 then 'تخفیف فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') when 29 then 'تخفیف برگشت از فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') when 30 then '' when 31 then 'تخفیف خرید (' + cast(m.codeBargeh as nvarchar)+ ')' +isnull(d.sharheRadif,'') when 32 then 'تخفیف برگشت از خرید ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') when 33 then '' when 34 then '' when 35 then '' when 36 then '' end as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], case m.noe when 28 then d.mablagheTakhfif when 29 then d.mablagheTakhfif else 0.0 end as [Bedehkar], case m.noe when 31 then d.mablagheTakhfif else 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 <> 101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and d.mablagheTakhfif > 0 and cast(d.CodeTakhfif as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اضافات و تخفیفات فاکتور فروش و خرید 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 then 'تخفیف فروش (' + cast(m.codeBargeh as nvarchar)+ ')' when 29 then 'اضافات برگشت از فروش (' + cast(m.codeBargeh as nvarchar)+ ')' when 30 then '' when 31 then 'اضافات خرید (' + cast(m.codeBargeh as nvarchar)+ ')' when 32 then 'تخفیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ')' when 33 then '' when 34 then '' when 35 then '' when 36 then '' end as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], case m.noe when 28 then 0.0 when 29 then 0.0 when 30 then 0.0 when 31 then 0.0 when 32 then 0.0 when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bedehkar], case m.noe when 28 then cast((d.mablagh) as decimal(19,4)) when 29 then cast((d.mablagh) as decimal(19,4)) when 30 then 0.0 when 31 then cast((d.mablagh) as decimal(19,4)) when 32 then cast((d.mablagh) as decimal(19,4)) when 33 then 0.0 when 34 then 0.0 when 35 then 0.0 when 36 then 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 --BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and @NoeGardesh = 207 and d.NoeMaster = 40 and d.mablagh > 0 and cast(m.CodeMoshtari as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اضافات و تخفیفات فاکتور فروش و خرید 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 then 'اضافات فروش (' + cast(m.codeBargeh as nvarchar)+ ')' when 29 then 'تخفیف برگشت از فروش (' + cast(m.codeBargeh as nvarchar)+ ')' when 30 then '' when 31 then 'تخفیف خرید (' + cast(m.codeBargeh as nvarchar)+ ')' when 32 then 'اضافات برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ')' else '' end as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], case m.noe when 28 then cast((d.mablagh) as decimal(19,4)) when 29 then cast((d.mablagh) as decimal(19,4)) when 30 then 0.0 when 31 then cast((d.mablagh) as decimal(19,4)) when 32 then cast((d.mablagh) as decimal(19,4)) else 0.0 end as [Bedehkar], 0.0 [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 --BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and @NoeGardesh = 207 and d.NoeMaster = 42 and d.mablagh > 0 and cast(m.CodeMoshtari as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) 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 38 then 194 -- GardeshDaryaftPassShodan when 39 then 186 -- GardeshPardakhtPassShodan 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 != 101 and g.mablagh > 0 and d.noeDetail = 44 and (@NoeGozaresh = 209 /*نقد*/) union all ------------------------------------------------------------------------------ هزینه ویزیتوری ها ----------------------------------------------------- Visitori Dar BargehDetail select 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 then 'ویزیتوری فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') when 29 then 'ویزیتوری برگشت از فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') when 30 then '' when 31 then 'ویزیتوری خرید (' + cast(m.codeBargeh as nvarchar)+ ')' +isnull(d.sharheRadif,'') when 32 then 'ویزیتوری برگشت از خرید ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') else '' end as [sharh], 0[Meghdar], ''[Vahed], d.MablagheVisitor[Fi], case m.noe when 28 then d.MablagheVisitor-- Foroosh when 29 then 0.0-- BargashtAzForoosh when 31 then d.MablagheVisitor-- Kharid when 32 then 0.0-- BargashtAzKharid end as [Bedehkar], case m.noe when 28 then 0.0-- Foroosh when 29 then d.MablagheVisitor -- BargashtAzForoosh when 31 then 0.0 -- Kharid when 32 then d.MablagheVisitor -- BargashtAzKharid end [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 <> 101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and d.MablagheVisitor > 0 and cast(d.CodeFarayandVisitor as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all ----------------------------------------------------- Visitori Dar Bargeh select 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 then 'ویزیتوری فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') when 29 then 'ویزیتوری برگشت از فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') when 30 then '' when 31 then 'ویزیتوری خرید (' + cast(m.codeBargeh as nvarchar)+ ')' +isnull(d.sharheRadif,'') when 32 then 'ویزیتوری برگشت از خرید ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') else '' end as [sharh], 0[Meghdar], ''[Vahed], d.MablagheVisitor[Fi], case m.noe when 28 then m.MablagheVisitor-- Foroosh when 29 then 0.0-- BargashtAzForoosh when 31 then m.MablagheVisitor-- Kharid when 32 then 0.0-- BargashtAzKharid end as [Bedehkar], case m.noe when 28 then 0.0-- Foroosh when 29 then m.MablagheVisitor -- BargashtAzForoosh when 31 then 0.0 -- Kharid when 32 then m.MablagheVisitor -- BargashtAzKharid end [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 <> 101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid ,HavalehAnbar,ResidAnbar*/ and m.MablagheVisitor > 0 and cast(m.HazineVisitor as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- برای بدهکاری یا بستانکاری انبارگردانی 0 [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case d.noe when 253 then 'کسورات انبارگردانی('+cast(m.code as nvarchar)+')' when 254 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 253 then cast(d.tedad as decimal(19,4)) * d.mablagh when 254 then 0.0 end as [Bedehkar], case d.noe when 253 then 0.0 when 254 then cast(d.tedad as decimal(19,4)) * d.mablagh end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], 181 [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 != 101 and d.mablagh > 0 and cast(d.CodeTafsili as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all ------------------------------------------------------------------------------ اضافات در برگه دیتیل select 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 then 'اضافات فروش ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') when 29 then 'اضافات برگشت از فروش('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') when 30 then '' when 31 then 'اضافات خرید (' + cast(m.codeBargeh as nvarchar)+ ')' +isnull(d.sharheRadif,'') when 32 then 'اضافات برگشت از خرید ('+ cast(m.codeBargeh as nvarchar)+ ')' + isnull(d.sharheRadif,'') else '' end as [sharh], 0[Meghdar], ''[Vahed], d.MablaghArzeshAfzoodeh[Fi], case m.noe when 28 then 0.0 -- Foroosh when 29 then d.MablaghArzeshAfzoodeh -- BargashtAzForoosh when 31 then d.MablaghArzeshAfzoodeh -- Kharid when 32 then 0.0 -- BargashtAzKharid end as [Bedehkar], case m.noe when 28 then d.MablaghArzeshAfzoodeh -- Foroosh when 29 then 0.0 -- BargashtAzForoosh when 31 then 0.0 -- Kharid when 32 then d.MablaghArzeshAfzoodeh -- BargashtAzKharid end [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 <> 101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and d.MablaghArzeshAfzoodeh > 0 and cast(d.CodeArzeshAfzoodeh as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all -------------------------------------------------------------------- تولید select 0 as [Radif], d.Tarikh[tarikh], m.SaatSabt[Saat], case d.Dom_ID_Noe when 261 then 'هزینه تولید ('+ cast(m.Code as nvarchar)+ ') ' + isnull(d.sharh,'') when 262 then 'دستمزد تولید (' + cast(m.Code as nvarchar)+ ') ' +isnull(d.sharh,'') end [sharh], 0 [Meghdar], '' [Vahed], d.Price [Fi], case when d.Dom_ID_Noe = 261 /*Hazineh*/ then isnull(d.Price,0.0) else 0.0 end [Bedehkar], case when d.Dom_ID_Noe = 262 /*Dastmozd*/ then isnull(d.Price,0.0) else 0.0 end [Bestankar], 0.0 as [Mandeh], ''[Mahiat], 200 [NoeRadif], -- Tolid m.ID [Code] , 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from Product m join ProductDetail d on m.ID= d.Prod_ID where m.Dom_ID_Vaziat <> 101 and d.Taf_ID is not null and --@NoeGardesh = 207 and d.Taf_ID = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select 0 as [Radif], d.Tarikh[tarikh], m.SaatSabt[Saat], 'پرداخت هزینه تولید ('+ cast(m.Code as nvarchar)+ ') ' + isnull(d.sharh,'') [sharh], 0 [Meghdar], '' [Vahed], d.Price [Fi], 0.0 [Bedehkar], isnull(d.Price,0.0) [Bestankar], 0.0 as [Mandeh], ''[Mahiat], 200 [NoeRadif], -- Tolid m.ID [Code] , 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from Product m join ProductDetail d on m.ID= d.Prod_ID where m.Dom_ID_Vaziat <> 101 and d.Taf_ID is not null and --@NoeGardesh = 207 and d.Taf_ID_Bestankar = @pcode and d.Dom_ID_Noe = 261 /*هزینه*/ and (@NoeGozaresh = 209 /*نقد*/) union all --------------------------------------------------------- تولید بصورت کلی گزارش می گیرد --select -- 0 as [Radif], -- d.Tarikh[tarikh], -- m.SaatSabt[Saat], -- case d.Dom_ID_Noe -- when 261 then -- 'هزینه تولید ('+ cast(m.Code as nvarchar)+ ') ' + isnull(d.sharh,'') -- when 262 then -- 'دستمزد تولید (' + cast(m.Code as nvarchar)+ ') ' +isnull(d.sharh,'') -- end [sharh], -- 0 [Meghdar], -- '' [Vahed], -- 0.0 [Fi], -- case when d.Dom_ID_Noe = 261 /*Hazineh*/ then isnull((select sum(isnull(d.Price,0.0)) from ProductDetail d where m.ID = d.Prod_ID and d.Taf_ID = @pcode),0.0) else 0.0 end [Bedehkar], -- case when d.Dom_ID_Noe = 262 /*Dastmozd*/ then isnull((select sum(isnull(d.Price,0.0)) from ProductDetail d where m.ID = d.Prod_ID and d.Taf_ID = @pcode),0.0) else 0.0 end [Bestankar], -- --case when d.Dom_ID_Noe = 262 /*Dastmozd*/ then isnull(d.Price,0.0) else 0.0 end [Bestankar], -- 0.0 as [Mandeh], -- ''[Mahiat], -- 200 [NoeRadif], -- Tolid -- m.ID [code] , -- 0.0[TedadEmtiaz], -- 0.0[JamEmtiaz], -- 0 [CodeProjehBedehkar], -- 0 [CodeProjehBestankar] --from Product m --where -- m.Dom_ID_Vaziat <> 101 and -- @NoeGardesh = 206 and -- (@NoeGozaresh = 209 /*نقد*/) --------------------------------------------- --union all ---------------------------------------------------------------------- تبدیل بصورت جرئی گزارش می گیرد --select -- 0 as [Radif], -- m.Tarikh[tarikh], -- m.SaatSabt[Saat], -- case d.noe -- when 261 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 262 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], -- 201 [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 <> 101 and -- d.CodeTafsili is not null and -- @NoeGardesh = 207 and -- cast(d.CodeTafsili as int) = @pcode and -- m.noe = 265 -- tabdil -- and (@NoeGozaresh = 209 /*نقد*/) --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], -- 201 [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 <> 101 and -- d.CodeTafsili = @pcode and -- @NoeGardesh = 206 and -- m.noe = 265 -- Tabdil -- and (@NoeGozaresh = 209 /*نقد*/) --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], 202 [NoeRadif], -- Tamirat m.code[code] , 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from Tamirat m where m.VaziatTamir not in (289,290) and -- LaghvShodeh , TabdilBeFactorForoosh m.codeMoshtari = @pcode and (@NoeGozaresh = 209 /*نقد*/) 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], 202 [NoeRadif], -- Tamirat m.code[code] , 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from Tamirat m where m.VaziatTamir not in (289,290) and -- LaghvShodeh , TabdilBeFactorForoosh m.codeMoshtari = @pCode and m.MablaghBeiaaneh > 0 and (@NoeGozaresh = 209 /*نقد*/) 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], 202 [NoeRadif], -- Tamirat m.code[code] , 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from Tamirat m where m.VaziatTamir not in (289,290) and -- LaghvShodeh , TabdilBeFactorForoosh m.CodeKhadamat = @pcode and (@NoeGozaresh = 209 /*نقد*/) 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], 202 [NoeRadif], -- Tamirat m.code[code] , 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from Tamirat m where m.VaziatTamir not in (289,290) and -- LaghvShodeh , TabdilBeFactorForoosh m.CodeSandoogh = @pcode and m.MablaghBeiaaneh > 0 and (@NoeGozaresh = 209 /*نقد*/) 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], 202 [NoeRadif], -- Tamirat m.code[code] , 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from Tamirat m where m.VaziatTamir not in (289,290) and -- LaghvShodeh , TabdilBeFactorForoosh m.codeTamirKar = @pCode and m.MablaghTamirKar > 0 and (@NoeGozaresh = 209 /*نقد*/) 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], 202 [NoeRadif], -- Tamirat m.code[code] , 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from Tamirat m where m.VaziatTamir not in (289,290) and -- LaghvShodeh , TabdilBeFactorForoosh m.codeHazinehTamirKar = @pCode and m.MablaghTamirKar > 0 and (@NoeGozaresh = 209 /*نقد*/) 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], 203 [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 = 288 and -- TahvilBeMoshtari d.CodeTafsiliBedehKar = @pcode and d.Mablagh > 0 and (@NoeGozaresh = 209 /*نقد*/) 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], 203 [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 = 288 and -- TahvilBeMoshtari m.CodeMoshtari = @pcode and d.Mablagh > 0 and (@NoeGozaresh = 209 /*نقد*/) ---------------------------------------------------------------------------------------- پایان تعمیرات 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 <> 101 and m.CodeShakhs = @pCode and @NoeGardesh = 206 and (@NoeGozaresh = 209 /*نقد*/) 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 <> 101 and d.CodeTafsili = @pCode and @NoeGardesh = 207 and (@NoeGozaresh = 209 /*نقد*/) 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 <> 101 and m.codeshakhs != d.codeTafsili and d.CodeTafsili = @pCode and (@NoeGozaresh = 209 /*نقد*/) 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 <> 101 and m.codeshakhs != d.codeTafsili and d.CodeTafsili2 = @pCode and (@NoeGozaresh = 209 /*نقد*/) 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 <> 101 and m.codeshakhs != d.codeTafsili and d.CodeTafsili3 = @pCode and (@NoeGozaresh = 209 /*نقد*/) union all */ ------------------------------------------------------ پروژه --------------------- بدهکار پروژه باشد select 0[Radif], m.tarikh[Tarikh], m.SaatSabt[Saat], case m.NoeMaster when 40 then -- هزینه 'هزينه ('+ cast(m.code as nvarchar) + ')' +(select name from tafsili where Code =m.CodeTafsiliBestankar )+' ' + isnull(m.sharh,'') when 41 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 40 then 174--Hazineh when 41 then 175--HavalehHesabdari end [NoeRadif], --2[NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat != 101 and m.CodeBargehParent is null and (m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and m.CodeProjehBedehKar = @pcode and @noeTaf in (53/*پروژه*/) and (@NoeGozaresh = 209 /*نقد*/) union all ----------------------------------------- بستانکار پروژه باشد select 0[Radif], m.tarikh[Tarikh], m.SaatSabt[Saat], case m.noeMaster when 40 then 'هزينه ('+ cast(m.code as nvarchar) + ')'+(select name from tafsili where Code =m.CodeTafsiliBedehkar )+' ' +isnull(m.sharh,'') when 41 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 40 then 174--Hazineh when 41 then 175--HavalehHesabdari end [NoeRadif], --3 [NoeRadif], m.code [Code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], 0 [CodeProjehBedehkar], 0 [CodeProjehBestankar] from daryaftpardakht m where m.Vaziat !=101 and m.CodeBargehParent is null and (m.IsAvalDoreh = 0 or m.IsAvalDoreh is null) and m.CodeProjehBestanKar = @pcode and @noeTaf in (53/*پروژه*/) and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اعمال بدهي براي فاکتور فروش (پروژه) - بصورت تک تک کالا گزارش می گیرد 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 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 29 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 30 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 31 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 32 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,'') else '' end as [sharh], d.Tedad[Meghdar], case m.noe when 28 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 29 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 31 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 32 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 28 then 0.0 when 29 then cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad ) as decimal(19,4)) when 30 then 0.0 when 31 then cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad ) as decimal(19,4)) when 32 then 0.0 else 0.0 end as [Bedehkar], case m.noe when 28 then cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad) as decimal(19,4)) when 29 then 0.0 when 30 then 0.0 when 31 then 0.0 when 32 then cast((d.mablagh * d.Meghdar1 * d.Meghdar2 * d.Tedad ) as decimal(19,4)) else 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 30 then 182 -- PishFactorForoosh when 31 then 178 -- Kharid when 32 then 179 -- BargashtAzKharid end as [NoeRadif], m.code[code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], case m.noe when 28 then 0 when 29 then m.CodeProjeh when 31 then m.CodeProjeh when 32 then 0 end [CodeProjehBedehkar], case m.noe when 28 then m.CodeProjeh when 29 then 0 when 31 then 0 when 32 then m.CodeProjeh end [CodeProjehBestankar] from (Bargeh m join BargehDetail d on m.code = d.CodeBargeh) where m.Vaziat !=101 and m.noe in (28,29,31,32)/*FactorForoosh ,BargashtAzForoosh , FactorKharid , BargashtAzKharid*/ and @NoeGardesh = 207 and cast(m.CodeProjeh as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اعمال تخفیف ردیف فاکتور فروش (پروژه) - بصورت تک تک کالا گزارش می گیرد 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 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 29 then 'تخفیف ردیف برگشت از فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'') when 30 then 'تخفیف ردیف پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'') when 31 then 'تخفیف ردیف خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') when 32 then 'تخفیف ردیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') else '' end as [sharh], 0 [Meghdar], '' [Vahed], isnull(d.MablagheTakhfif,0.0) [Fi], case m.noe when 28 then isnull(d.MablagheTakhfif,0.0) when 29 then 0.0 when 30 then 0.0 when 31 then 0.0 when 32 then isnull(d.MablagheTakhfif,0.0) else 0.0 end as [Bedehkar], case m.noe when 28 then 0.0 when 29 then isnull(d.MablagheTakhfif,0.0) when 30 then 0.0 when 31 then isnull(d.MablagheTakhfif,0.0) else 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 30 then 182 -- PishFactorForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and @NoeGardesh = 207 and d.MablagheTakhfif > 0 and cast(m.CodeProjeh as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اعمال ارزش افزوده ردیف فاکتور فروش (پروژه) - بصورت تک تک کالا گزارش می گیرد 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 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 29 then 'ارزش افزوده ردیف برگشت از فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'') when 30 then 'ارزش افزوده ردیف پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar) + ') ' + isnull(d.sharheRadif,'') when 31 then 'ارزش افزوده ردیف خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') when 32 then 'ارزش افزوده ردیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(d.sharheRadif,'') else '' end as [sharh], 0 [Meghdar], '' [Vahed], isnull(d.MablaghArzeshAfzoodeh,0.0)[Fi], case m.noe when 28 then 0.0 when 29 then isnull(d.MablaghArzeshAfzoodeh,0.0) when 30 then 0.0 when 31 then isnull(d.MablaghArzeshAfzoodeh,0.0) else 0.0 end as [Bedehkar], case m.noe when 28 then isnull(d.MablaghArzeshAfzoodeh,0.0) when 29 then 0.0 when 30 then 0.0 when 31 then 0.0 when 32 then isnull(d.MablaghArzeshAfzoodeh,0.0) else 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 -- BargashtAzForoosh when 30 then 182 -- PishFactorForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and @NoeGardesh = 207 and d.MablaghArzeshAfzoodeh > 0 and cast(m.CodeProjeh as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اضافات و تخفیفات فاکتور فروش و خرید (/*پروژه*/)؛ 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 then 'تخفیف فروش (' + cast(m.codeBargeh as nvarchar)+ ')' when 29 then 'اضافات برگشت از فروش (' + cast(m.codeBargeh as nvarchar)+ ')' when 30 then '' when 31 then 'اضافات خرید (' + cast(m.codeBargeh as nvarchar)+ ')' when 32 then 'تخفیف برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ')' else '' end as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], case m.noe when 28 then cast((d.mablagh) as decimal(19,4)) when 29 then cast((d.mablagh) as decimal(19,4)) when 30 then 0.0 when 31 then cast((d.mablagh) as decimal(19,4)) when 32 then cast((d.mablagh) as decimal(19,4)) else 0.0 end as [Bedehkar], 0.0 [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 --BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and @NoeGardesh = 207 and d.NoeMaster = 40 and cast(m.CodeProjeh as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اضافات و تخفیفات فاکتور فروش و خرید (/*پروژه*/) ؛ 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 then 'اضافات فروش (' + cast(m.codeBargeh as nvarchar)+ ')' when 29 then 'تخفیف برگشت از فروش (' + cast(m.codeBargeh as nvarchar)+ ')' when 30 then '' when 31 then 'تخفیف خرید (' + cast(m.codeBargeh as nvarchar)+ ')' when 32 then 'اضافات برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ')' else '' end as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], 0.0 [Bedehkar], case m.noe when 28 then cast((d.mablagh) as decimal(19,4)) when 29 then cast((d.mablagh) as decimal(19,4)) when 30 then 0.0 when 31 then cast((d.mablagh) as decimal(19,4)) when 32 then cast((d.mablagh) as decimal(19,4)) else 0.0 end [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 --BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- 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 !=101 and m.noe not in (30,33,34,35)/*PishFactorForoosh , PishFactorKharid,HavalehAnbar,ResidAnbar*/ and @NoeGardesh = 207 and d.NoeMaster = 42 and cast(m.CodeProjeh as int) = @pcode and (@NoeGozaresh = 209 /*نقد*/) union all select --------------------------------------------------------------------------- اعمال بدهي براي فاکتور فروش (/*پروژه*/) بصورت کلی گزارش می گیرد 0 as [Radif], m.Tarikh[tarikh], m.SaatSabt[Saat], case m.noe when 28 then 'فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '') when 29 then 'برگشت از فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '') when 30 then 'پیش فاکتور فروش ('+ cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '') when 31 then 'خريد (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '') when 32 then 'برگشت از خرید (' + cast(m.codeBargeh as nvarchar)+ ') ' + isnull(m.Sharh , '') else '' end as [sharh], 0[Meghdar], ''[Vahed], 0.0[Fi], case m.noe when 28 then 0.0 when 29 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 = 42 and d.vaziat != 101) as decimal(19,4)) ,0.0) + isnull ( cast((select sum(d.mablagh) from DaryaftPardakht d where d.codeBargehParent = m.code and d.noeMaster = 40 and d.vaziat != 101) 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 30 then 0.0 when 31 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 = 42 and d.vaziat != 101) as decimal(19,4)) ,0.0) + isnull ( cast((select sum(d.mablagh) from DaryaftPardakht d where d.codeBargehParent = m.code and d.noeMaster = 40 and d.vaziat != 101) 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) else 0.0 end as [Bedehkar], case m.noe when 28 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 = 40 and d.vaziat != 101) as decimal(19,4)) ,0.0) + isnull ( cast((select sum(d.mablagh) from DaryaftPardakht d where d.codeBargehParent = m.code and d.noeMaster = 42 and d.vaziat != 101) 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 29 then 0.0 when 30 then 0.0 when 31 then 0.0 when 32 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 = 40 and d.vaziat != 101) as decimal(19,4)) ,0.0) + isnull ( cast((select sum(d.mablagh) from DaryaftPardakht d where d.codeBargehParent = m.code and d.noeMaster = 42 and d.vaziat != 101) 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) else 0.0 end as [Bestankar], 0.0 as [Mandeh], ''[Mahiat], case m.noe when 28 then 177 --Foroosh when 29 then 180 --BargashtAzForoosh when 31 then 178 -- Kharid when 32 then 179 -- BargashtAzKharid end as [NoeRadif], m.code[code], 0.0[TedadEmtiaz], 0.0[JamEmtiaz], case m.noe when 28 then 0 when 29 then m.CodeProjeh when 31 then m.CodeProjeh when 32 then 0 end [CodeProjehBedehkar], case m.noe when 28 then m.CodeProjeh when 29 then 0 when 31 then 0 when 32 then m.CodeProjeh end [CodeProjehBestankar] from Bargeh m where m.Vaziat !=101 and m.noe in (28,29,31,32)/*FactorForoosh ,BargashtAzForoosh , FactorKharid , BargashtAzKharid*/ and cast(m.CodeProjeh as int) = @pcode and @NoeGardesh = 206 and (@NoeGozaresh = 209 /*نقد*/) order by tarikh , Saat end