USE [namedatabase] GO /****** Object: StoredProcedure [dbo].[SooratHesab] Script Date: 08/01/2026 02:19:30 ب.ظ ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[SooratHesab] @fast nvarchar(5), @CodeTafzili nvarChar(10), @bigDateMin bigint , @bigDateMax bigint , @flagMande int , @intOrder int, @SumBedGhabl bigint output, @SumBesGhabl bigint output as begin declare @EmpId bigint,@Select nvarchar(500),@Type nvarchar(50),@ShomareFactor bigint,@CodeKolMoin nvarchar(50),@CodeKolMoinTanzimat nvarchar(50),@CodeKolTanzimat nvarchar(50),@CodeKolSanad nvarchar(50) declare @ShomareSanad bigint,@CodekolMoinDaryaftTanzimat nvarchar(10),@ShomareSanadEbteda bigint declare @ShomareDaryaft bigint,@ShomarePardakht bigint, @strAlaki nvarchar(50),@lngAlaki bigint,@SharheKol nvarchar(200) ,@lngNaghd bigint,@strMoinKamelSandoogh nvarchar(10),@strSharhenaghd nvarchar(200) ,@lngBank bigint,@lngBank2 bigint,@lngBank3 bigint,@strMoinKamelBank nvarchar(10),@strMoinKamelBank2 nvarchar(10),@strMoinKamelBank3 nvarchar(10),@strShareBank nvarchar(200),@strShareBank2 nvarchar(200),@strShareBank3 nvarchar(200),@lngCheck bigint,@strSharheCheck nvarchar(200),@strMoinKamelCheckDaryafti nvarchar(10),@strMoinKamelCheckPardakhti nvarchar(10),@lngMablagheCheckeVagozari bigint,@strSCheckVaghozari nvarchar(200),@strMoinKamelCheckVagozari nvarchar(10),@lngTakhfif bigint,@strSharheTakhfifat nvarchar(200) declare @str1 nvarchar(200),@str2 nvarchar(200),@str3 nvarchar(200),@str4 nvarchar(200),@str5 nvarchar(200),@str6 nvarchar(200),@str7 nvarchar(200),@lngAlaki2 bigint declare @CodekolMoinHesabhayeDaryaftaniTanzimat nvarchar(10),@CodekolMoinHesabhayePardakhtaniTanzimat nvarchar(10),@CodekolHesabhayePardakhtaniTanzimat nvarchar(10),@strSharheHesab nvarchar(50),@bigBed bigint,@bigBes bigint, @CodeMoinKamelHesabhayePardakhtaniTanzimat nvarchar(10) CREATE TABLE #tmpSooratHesab2( [ID] [nvarchar](20) COLLATE Persian_100_CI_AS NULL , [radif] [bigint] null, [shomare] [nvarchar](100) COLLATE Persian_100_CI_AS null , [fldSanad] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldSharh] [nvarchar](250) COLLATE Persian_100_CI_AS NULL , [fldRiz] [nvarchar](250) COLLATE Persian_100_CI_AS NULL , [fldDate] [int] NULL, [fldTedad][float] null, [fldNameVahed] [nvarchar](100) COLLATE Persian_100_CI_AS, [fldNameVahed2] [nvarchar](100) COLLATE Persian_100_CI_AS, [fldFee][bigint] null, [fldFeeYekCarton][bigint] null, [fldFeeKol][bigint] null, [fldDarsadTakhfif][bigint] null, [fldMablaghTakhfif][bigint] null, [fldFeeVahed1BadAzTakhfif][bigint] null, [fldFeeVahed2BadAzTakhfif][bigint] null, [fldArzeshAfzode][bigint] null, [fldFeeKolBadAzTakhfif][bigint] null, [fldMablaghKhales][bigint] null, [fldCodeAnbar] [bigint] null, [fldNameAnbar][nvarchar](50) COLLATE Persian_100_CI_AS null , [fldCodeKarbar] bigint, [fldNameKarbar] [nvarchar](25) COLLATE Persian_100_CI_AS NULL , [fldMablagh][bigint] null, [fldBedehkar] [decimal](18, 0) NULL, [fldBestankar] [decimal](18, 0) NULL, [fldMande][bigint] null, [fldCodeKol] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldCodeMoin] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldCodeTafzili] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldCodeMarkaz1] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldCodeMarkaz2] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldRadif] [varchar](50) COLLATE Persian_100_CI_AS NULL , [fldCodeSharh] [varchar](50) COLLATE Persian_100_CI_AS NULL , [fldMoinKamel] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldCodeNoeTafzili] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldGhateiMovaghat] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldGhateiDaem] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldSanadGhadim] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldShomarePeigiri] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldMoghayerat] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldType] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldVaziat][nvarchar](20) COLLATE Persian_100_CI_AS NULL , [fldVasete] [nvarchar](50) COLLATE Persian_100_CI_AS NULL , [fldNameVasete] [nvarchar](250) COLLATE Persian_100_CI_AS NULL ) ON [PRIMARY] ---اسناد مربوط به کدتفصیلی یک به یک بررسی میشه و متناسب با نوع سند تحلیل و گسترش میباشد declare @bigDateMinForSanad bigint ,@bigDateMaxForSanad bigint ,@bigCodeKolHazine nvarchar(50),@bigCodeKolDaramad nvarchar(50) ,@bigCodeMoinKharid nvarchar(50),@strStartHazine nvarchar(1),@strStartDaramad nvarchar(1),@sanadCheckDaryaftEbteda bigint,@sanadCheckEbtedaPardakht bigint declare @tooleKol as int,@strKolForoosh nvarchar(50),@Asnadepardakhtani nvarchar(50),@Asnadepardaryaftani nvarchar(50),@CodemoinForoosh nvarchar(20),@KolSooratHesab1 nvarchar(6),@KolSooratHesab2 nvarchar(6),@KolSooratHesab3 nvarchar(6) set @tooleKol=(Select top(1) fldKol from tblPaye) execute codemoinhesab 'foroosh' ,@strKolForoosh output set @bigDateMinForSanad=@bigDateMin set @bigDateMaxForSanad=@bigDateMax execute CodeKolHesab 'hazine',@bigCodeKolHazine output execute CodeKolHesab 'daramad',@bigCodeKolDaramad output execute CodeMoinHesab 'kharid',@bigCodeMoinKharid output execute CodeMoinHesab 'checkpardakhti',@Asnadepardakhtani output execute CodeMoinHesab 'foroosh',@CodemoinForoosh output set @strStartHazine=substring(Convert(varchar,@bigCodeKolHazine),1,1) set @strStartDaramad=substring(Convert(varchar,@bigCodeKolDaramad),1,1) execute codemoinhesab 'hesabhayepardakhtani' ,@CodeMoinKamelHesabhayePardakhtaniTanzimat output execute codemoinhesab 'checkpardakhti' ,@strMoinKamelCheckPardakhti output execute codemoinhesab 'check' ,@Asnadepardaryaftani output execute codemoinhesab 'check' ,@strMoinKamelCheckDaryafti output execute CodeKolHesab 'kolhesabhayepardakhtani' ,@CodekolHesabhayePardakhtaniTanzimat output set @KolSooratHesab1=(select fldMeghdar from tblTanzimateKol where fldtype='kolsoorathesab1') set @KolSooratHesab2=(select fldMeghdar from tblTanzimateKol where fldtype='kolsoorathesab2') set @KolSooratHesab3=(select fldMeghdar from tblTanzimateKol where fldtype='kolsoorathesab3') update tblSodooreSanad set fldType='' where fldType is null set @ShomareSanadEbteda=(select fldShomareEftetahie from tblControlPanel) set @sanadCheckDaryaftEbteda=(select top(1)fldSanad from tblCheckeDaryafti Where fldShomareDaryaft=0) if @sanadCheckDaryaftEbteda is null set @sanadCheckDaryaftEbteda=0 set @sanadCheckEbtedaPardakht=(select top(1)fldSanad from tblCheckePardakhti Where fldShomarePardakht=0) if @sanadCheckEbtedaPardakht is null set @sanadCheckEbtedaPardakht=0 --(fldSanad<>@ShomareSanadEbteda and fldMoinKamel<>@strMoinKamelCheckDaryafti) and delete #tmpSooratHesab2 update tblSodooreSanad set fldType='' Where fldtype is null if @fast='ok' begin declare @CodeKoleHesabhayeDaryaftani nvarchar(20),@CodeKoleHesabhayePardakhtani nvarchar(20) execute CodeKolHesab 'kolhesabhayedaryaftani',@CodeKoleHesabhayeDaryaftani output execute CodeKolHesab 'kolhesabhayepardakhtani',@CodeKoleHesabhayePardakhtani output insert into #tmpSooratHesab2 (id,fldCodeTafzili,fldSanad,fldRiz,fldDate,fldBedehkar,fldBestankar,fldCodeKol,fldCodeMoin,fldMoinkamel,fldType) Select id,fldCodeTafzili,fldSanad,fldSharh,fldDate,fldBedehkar,fldBestankar,fldCodeKol,fldCodeMoin,fldMoinkamel,fldType from tblSodooreSanad With (READCOMMITTED) where ((fldCodeKol=@KolSooratHesab1 or fldCodeKol=@KolSooratHesab2 or fldCodeKol=@KolSooratHesab3) and fldCodeTafzili=@CodeTafzili) or not( fldType='pasecheck') and fldCodeTafzili=@CodeTafzili and (Convert(dec,fldDate) between @bigDateMinForSanad and @bigDateMaxForSanad) and (fldCodeKol=@CodeKoleHesabhayeDaryaftani or fldCodeKol=@CodeKoleHesabhayePardakhtani) ORDER BY CONVERT(dec, fldSanad), CONVERT(dec, fldRadif) --update #tmpSooratHesab2 set fldRiz =fldRiz + ' ' + tblTafzili.fldSharheTafzili from tblTafzili Where #tmpSooratHesab2.fldCodeTafzili=tblTafzili.fldCodeTafzili update #tmpSooratHesab2 set fldSharh='افتتاحیه' Where fldType='eftetahie' update #tmpSooratHesab2 set fldSharh='خرید' Where fldType='kharid' update #tmpSooratHesab2 set fldSharh='پرداخت' Where fldType='pardakht' update #tmpSooratHesab2 set fldSharh='دریافت' Where fldType='daryaft' update #tmpSooratHesab2 set fldSharh='برگشت از خرید' Where fldType='bargashtazkharid' update #tmpSooratHesab2 set fldSharh='فروش' Where fldType='foroosh' update #tmpSooratHesab2 set fldSharh='برگشت از فروش' Where fldType='bargashtazforoosh' update #tmpSooratHesab2 set fldriz='دریافت شماره' + ' ' + Convert(varchar,fldShomareDaryaft), shomare=fldShomareDaryaft from tblDaryaft Where #tmpSooratHesab2.fldSanad=tblDaryaft.fldShomareSanad update #tmpSooratHesab2 set fldriz='پرداخت شماره' + ' ' + Convert(varchar,fldShomarePardakht), shomare=fldShomarePardakht from tblPardakht Where #tmpSooratHesab2.fldSanad=tblPardakht.fldShomareSanad update #tmpSooratHesab2 set fldriz='فاکتور خرید' + ' ' + Convert(varchar,fldShomareFactorKharid),shomare=fldShomareFactorKharid from tblKharid Where #tmpSooratHesab2.fldSanad=tblKharid.fldShomareSanad update #tmpSooratHesab2 set fldriz='برگشت از خرید ' + ' ' + Convert(varchar,fldShomareFactorKharid),shomare=fldShomareFactorKharid from tblBargashtAzKharid Where #tmpSooratHesab2.fldSanad=tblBargashtAzKharid.fldShomareSanad update #tmpSooratHesab2 set fldriz='فاکتور فروش' + ' ' + Convert(varchar,fldShomareFactorForoosh), shomare=fldShomareFactorforoosh from tblForoosh Where #tmpSooratHesab2.fldSanad=tblForoosh.fldShomareSanad update #tmpSooratHesab2 set fldriz='برگشت از فروش' + ' ' + Convert(varchar,fldShomareFactorForoosh), shomare=fldShomareFactorforoosh from tblBargashtAzForoosh Where #tmpSooratHesab2.fldSanad=tblBargashtAzForoosh.fldShomareSanad if @flagMande =1 --با نقل مانده begin declare @decMandeGhablBed decimal(18,0) ,@decMandeGhablBes decimal(18,0) ,@decMandeGhabl decimal(18,0) declare @CodeKol nvarchar(100),@MandeTalab bigint,@MandeBedehi bigint execute CodeKolHesab 'kolhesabhayedaryaftani',@CodeKol output set @MandeTalab=(SELECT ISNULL( SUM(ISNULL(fldBedehkar, 0)-ISNULL(fldBestankar, 0)),0) AS SumM FROM tblSodooreSanad WHERE (fldCodeKol=@CodeKol) and (fldCodeTafzili = @CodeTafzili ) and Convert(bigint,fldDate)<@bigDateMin) execute CodeKolHesab 'kolhesabhayepardakhtani',@CodeKol output set @MandeBedehi=(SELECT ISNULL( SUM(ISNULL(fldBestankar, 0)-ISNULL(fldBedehkar, 0)),0) AS SumM FROM tblSodooreSanad WHERE (fldCodeKol=@CodeKol) and (fldCodeTafzili = @CodeTafzili ) and Convert(bigint,fldDate)<@bigDateMin) set @decMandeGhabl=@MandeTalab-@MandeBedehi if @decMandeGhabl<>0 insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldSharh,fldDate,fldBedehkar,fldBestankar,fldMande,fldtype) values ('0','','0','مانده قبل',@bigDateMin,@MandeTalab,@MandeBedehi,@decMandeGhabl,'ghabl') end set @SumBedGhabl=(Select Sum(fldBedehkar) as SumBed from #tmpSooratHesab2 Where fldBestankar is not null and fldBedehkar is not null and Convert(dec,fldDate)<@bigDateMin) if @SumBedGhabl is null set @SumBedGhabl =0 set @SumBesGhabl=(Select Sum(fldBestankar) as SumBes from #tmpSooratHesab2 Where fldBestankar is not null and fldBedehkar is not null and Convert(dec,fldDate)<@bigDateMin) if @SumBesGhabl is null set @SumBesGhabl=0 --update #tmpSooratHesab2 set id=0 if @intOrder=0 --مرتب سازی براساس تاریخ Select ID,radif,shomare,fldSanad,fldSharh,fldRiz,substring(convert(nvarchar(10),fldDate),0,5) +'/' +substring(convert(nvarchar(10),fldDate),5,2) + '/' + substring(convert(nvarchar(10),fldDate),7,2) as fldDate,fldTedad,fldNameVahed, fldNameVahed2,fldFee,fldFeeYekCarton,fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif, fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif, fldMablaghKhales,fldCodeAnbar,fldNameAnbar,fldMablagh,fldBedehkar,fldBestankar,fldMande, fldType,fldVaziat,fldVasete,fldCodeKarbar,fldNameKarbar from #tmpSooratHesab2 order by Convert(dec,fldDate),Convert(dec,fldSanad) --select ID,radif,shomare,fldSanad,fldSharh,fldRiz,substring(convert(nvarchar(10),fldDate),0,3) +'/' +substring(convert(nvarchar(10),fldDate),3,2) + '/' + substring(convert(nvarchar(10),fldDate),5,2) as fldDate,fldTedad,fldNameVahed, -- fldNameVahed2,fldFee,fldFeeYekCarton,fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif, -- fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif, -- fldMablaghKhales,fldCodeAnbar,fldNameAnbar,fldMablagh,fldBedehkar,fldBestankar,fldMande, -- fldType,fldVaziat,fldVasete,fldCodeKarbar,fldNameKarbar else if @intOrder=1 --مرتب سازی براساس سند Select ID,radif,shomare,fldSanad,fldSharh,fldRiz,substring(convert(nvarchar(10),fldDate),0,5) +'/' +substring(convert(nvarchar(10),fldDate),5,2) + '/' + substring(convert(nvarchar(10),fldDate),7,2) as fldDate,fldTedad,fldNameVahed, fldNameVahed2,fldFee,fldFeeYekCarton,fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif, fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif, fldMablaghKhales,fldCodeAnbar,fldNameAnbar,fldMablagh,fldBedehkar,fldBestankar,fldMande, fldType,fldVaziat,fldVasete,fldCodeKarbar,fldNameKarbar from #tmpSooratHesab2 order by Convert(dec,fldSanad) end else begin --select * from tblsodooresanad where fldsanad=331 --(fldSanad<>@ShomareSanadEbteda and fldMoinKamel<>@strMoinKamelCheckDaryafti) and declare @kolhesabhayepardakhtani nvarchar(50),@kolhesabhayedaryaftani nvarchar(50),@CodeKoleHesabhayeDaryaftani2 nvarchar(20),@CodeKoleHesabhayePardakhtani2 nvarchar(20),@MoinKamel nvarchar(50),@fldCodeKol nvarchar(50),@CodeKoleMoinCheck nvarchar(20) execute CodeKolHesab 'kolhesabhayepardakhtani' ,@kolhesabhayepardakhtani output execute CodeKolHesab 'kolhesabhayedaryaftani' ,@kolhesabhayedaryaftani output execute CodeKolHesab 'check' ,@CodeKoleMoinCheck output --Select Id from tblSodooreSanad With (READCOMMITTED) where (fldType='' and ( (fldType<>'checkebteda') and fldCodeTafzili=10015) ) or ( (fldType<>'checkebteda') and fldsanad<>3 and fldSanad<>12 and fldCodeTafzili=10015 ) and ((fldtype<>'Vosool' and fldtype<>'pasecheck')) and fldType<>'ebteda' and not(fldtype='ekhtetamie') and Not( fldType='eftetahie' and fldMoinKamel=31701) and Not( fldType='eftetahie' and fldMoinKamel=11701) and Not( fldType='eftetahie' and fldcodekol='811') and fldCodeKol<>612 and fldMoinKamel<>@bigCodeMoinKharid and substring(fldMoinKamel,1,1)<>@strStartHazine and (substring(fldMoinKamel,1,@tooleKol)<>@strStartDaramad or fldMoinKamel=@strKolForoosh) and (substring(fldMoinKamel,1,1)<>@CodeMoinKamelHesabhayePardakhtaniTanzimat and fldType<>'hazine') and fldMoinKamel<>@CodeKoleMoinCheck) ORDER BY CONVERT(dec, fldSanad), CONVERT(dec, fldRadif) declare EmpCursor Cursor For --Select Id from tblSodooreSanad With (READCOMMITTED) where not(fldMoinKamel=@strMoinKamelCheckDaryafti and fldtype='dasti') and (fldCodeKol<>@bigCodeKolDaramad) and (fldMoinKamel<> @strMoinKamelCheckPardakhti) and ((fldType='' and ( (fldType<>'checkebteda') and fldCodeTafzili=@CodeTafzili ) ) or ( (fldType<>'checkebteda') and fldsanad<>@sanadCheckDaryaftEbteda and fldSanad<>@sanadCheckEbtedaPardakht and fldCodeTafzili=@CodeTafzili and (Convert(dec,fldDate) between @bigDateMinForSanad and @bigDateMaxForSanad) and ((fldtype<>'Vosool' and fldtype<>'pasecheck')) and fldType<>'ebteda' and not(fldtype='ekhtetamie') and Not( fldType='eftetahie' and fldMoinKamel=@strMoinKamelCheckPardakhti) and Not( fldType='eftetahie' and fldCodeKol='412') and Not( fldType='eftetahie' and fldMoinKamel=@strMoinKamelCheckDaryafti) and Not( fldType='eftetahie' and fldcodekol='811') and fldCodeKol<>@bigCodeKolHazine and fldMoinKamel<>@bigCodeMoinKharid and substring(fldMoinKamel,1,1)<>@strStartHazine and (substring(fldMoinKamel,1,@tooleKol)<>@strStartDaramad or fldMoinKamel=@strKolForoosh) and (substring(fldMoinKamel,1,1)<>@CodeMoinKamelHesabhayePardakhtaniTanzimat and fldType<>'hazine'))) ORDER BY CONVERT(dec, fldSanad), CONVERT(dec, fldRadif) --Select Id from tblSodooreSanad With (READCOMMITTED) where not(fldCodeKol like '7%' and fldtype='dasti') and (((fldCodeKol=@KolSooratHesab1 or fldCodeKol=@KolSooratHesab2 or fldCodeKol=@KolSooratHesab3) and fldCodeTafzili=@CodeTafzili) or not(fldMoinKamel=@strMoinKamelCheckDaryafti and fldtype='dasti') and (fldMoinKamel<> 90101) and (fldMoinKamel<> @strMoinKamelCheckPardakhti) and ((fldType='' and ( (fldType<>'checkebteda') and fldCodeTafzili=@CodeTafzili ) ) or ( (fldType<>'checkebteda') and fldsanad<>@sanadCheckDaryaftEbteda and fldSanad<>@sanadCheckEbtedaPardakht and fldCodeTafzili=@CodeTafzili and (Convert(dec,fldDate) between @bigDateMinForSanad and @bigDateMaxForSanad) and ((fldtype<>'Vosool' and fldtype<>'pasecheck')) and fldType<>'ebteda' and not(fldtype='ekhtetamie') and Not( fldType='eftetahie' and fldMoinKamel=@strMoinKamelCheckPardakhti) and Not( fldType='eftetahie' and fldCodeKol='412') and Not( fldType='eftetahie' and fldMoinKamel=@strMoinKamelCheckDaryafti) and Not( fldType='eftetahie' and fldcodekol='811') and fldCodeKol<>@bigCodeKolHazine and fldMoinKamel<>@bigCodeMoinKharid and substring(fldMoinKamel,1,1)<>@strStartHazine and (substring(fldMoinKamel,1,@tooleKol)<>@strStartDaramad or fldMoinKamel=@strKolForoosh) and (substring(fldMoinKamel,1,1)<>@CodeMoinKamelHesabhayePardakhtaniTanzimat and fldType<>'hazine')))) ORDER BY CONVERT(dec, fldSanad), CONVERT(dec, fldRadif) --Select Id from tblSodooreSanad With (READCOMMITTED) where not(fldCodeKol in (select fldCodeHesab from tblkolmoin where fldCodeNoeHesab='3') and fldtype='dasti') and (((fldCodeKol=@KolSooratHesab1 or fldCodeKol=@KolSooratHesab2 or fldCodeKol=@KolSooratHesab3) and fldCodeTafzili=@CodeTafzili) or not(fldMoinKamel=@strMoinKamelCheckDaryafti and fldtype='dasti') and (fldMoinKamel<> 90101) and (fldMoinKamel<> @strMoinKamelCheckPardakhti) and ((fldType='' and ( (fldType<>'checkebteda') and fldCodeTafzili=@CodeTafzili ) ) or ( (fldType<>'checkebteda') and fldsanad<>@sanadCheckDaryaftEbteda and fldSanad<>@sanadCheckEbtedaPardakht and fldCodeTafzili=@CodeTafzili and (Convert(dec,fldDate) between @bigDateMinForSanad and @bigDateMaxForSanad) and ((fldtype<>'Vosool' and fldtype<>'pasecheck')) and fldType<>'ebteda' and not(fldtype='ekhtetamie') and Not( fldType='eftetahie' and fldMoinKamel=@strMoinKamelCheckPardakhti) and Not( fldType='eftetahie' and fldCodeKol='412') and Not( fldType='eftetahie' and fldMoinKamel=@strMoinKamelCheckDaryafti) and Not( fldType='eftetahie' and fldcodekol='811') and fldCodeKol<>@bigCodeKolHazine and fldMoinKamel<>@bigCodeMoinKharid and substring(fldMoinKamel,1,1)<>@strStartHazine and (substring(fldMoinKamel,1,@tooleKol)<>@strStartDaramad or fldMoinKamel=@strKolForoosh) and (substring(fldMoinKamel,1,1)<>@CodeMoinKamelHesabhayePardakhtaniTanzimat and fldType<>'hazine')))) ORDER BY CONVERT(dec, fldSanad), CONVERT(dec, fldRadif) Select Id from tblSodooreSanad With (READCOMMITTED) where fldcodekol<>@bigCodeKolDaramad and not(fldCodeKol in (SELECT distinct s.fldCodeKol FROM tblsodooresanad s WHERE EXISTS ( SELECT 1 FROM tblkolmoin k WHERE (k.fldCodeNoeHesab = '3' or k.fldCodeNoeHesab = '2' or k.fldCodeNoeHesab = '8') and len(fldCodeHesab)=1 AND s.fldcodekol LIKE k.fldCodeHesab + '%' ) ) and fldtype='dasti') and (((fldCodeKol=@KolSooratHesab1 or fldCodeKol=@KolSooratHesab2 or fldCodeKol=@KolSooratHesab3) and fldCodeTafzili=@CodeTafzili) or not(fldMoinKamel=@strMoinKamelCheckDaryafti and fldtype='dasti') and (fldMoinKamel<> 90101) and (fldMoinKamel<> @strMoinKamelCheckPardakhti) and ((fldType='' and ( (fldType<>'checkebteda') and fldCodeTafzili=@CodeTafzili ) ) or ( (fldType<>'checkebteda') and fldsanad<>@sanadCheckDaryaftEbteda and fldSanad<>@sanadCheckEbtedaPardakht and fldCodeTafzili=@CodeTafzili and (Convert(dec,fldDate) between @bigDateMinForSanad and @bigDateMaxForSanad) and ((fldtype<>'Vosool' and fldtype<>'pasecheck')) and fldType<>'ebteda' and not(fldtype='ekhtetamie') and Not( fldType='eftetahie' and fldMoinKamel=@strMoinKamelCheckPardakhti) and Not( fldType='eftetahie' and fldCodeKol='412') and Not( fldType='eftetahie' and fldMoinKamel=@strMoinKamelCheckDaryafti) and Not( fldType='eftetahie' and fldcodekol='811') and fldCodeKol<>@bigCodeKolHazine and fldMoinKamel<>@bigCodeMoinKharid and substring(fldMoinKamel,1,1)<>@strStartHazine and (substring(fldMoinKamel,1,@tooleKol)<>@strStartDaramad or fldMoinKamel=@strKolForoosh) and (substring(fldMoinKamel,1,1)<>@CodeMoinKamelHesabhayePardakhtaniTanzimat and fldType<>'hazine')))) ORDER BY CONVERT(dec, fldSanad), CONVERT(dec, fldRadif) open EmpCursor fetch Next From EmpCursor into @EmpId While @@Fetch_Status = 0 Begin --Update tblSodooreSanad With (RowLock) Set [fldGhateiMovaghat]='False' --Where ID=@EmpId execute CodeKolHesab 'kolhesabhayedaryaftani',@CodeKoleHesabhayeDaryaftani2 output execute CodeKolHesab 'kolhesabhayepardakhtani',@CodeKoleHesabhayePardakhtani2 output set @type =(Select top(1)fldType from tblSodooresanad Where ID=@EmpId) --برای سند دستی که اگر چیزی غیر از حسابهای دریافتنی و پرداختنی بود نمایش نده --################################################################################ if @type='' begin set @fldCodeKol=(Select fldCodeKol from tblSodooreSanad Where ID=Convert(varchar,@EmpId)) if @fldCodeKol<>@CodeKoleHesabhayeDaryaftani2 and @fldCodeKol<>@CodeKoleHesabhayePardakhtani2 begin set @type='' end else begin INSERT INTO #tmpSooratHesab2 (ID, fldSanad, fldCodeKol, fldCodeMoin, fldCodeTafzili, fldCodeMarkaz1, fldCodeMarkaz2, fldBedehkar, fldBestankar, fldRadif, fldCodeSharh, fldDate, fldSharh, fldMoinKamel, fldCodeNoeTafzili, fldGhateiMovaghat, fldGhateiDaem, fldSanadGhadim, fldShomarePeigiri, fldMoghayerat, fldType, fldVasete) Select ID, fldSanad,fldCodeKol, fldCodeMoin, fldCodeTafzili, fldCodeMarkaz1, fldCodeMarkaz2, fldBedehkar, fldBestankar, fldRadif, fldCodeSharh, fldDate, fldSharh, fldMoinKamel, fldCodeNoeTafzili, fldGhateiMovaghat, fldGhateiDaem, fldSanadGhadim, fldShomarePeigiri, fldMoghayerat, fldType, fldVasete from tblSodooreSanad Where ID=Convert(varchar,@EmpId) end end --################################################################################ else begin -- ریختن کلیه اسناد مربوط به تفصیلی در جدول موقت ######### INSERT INTO #tmpSooratHesab2 (ID, fldSanad, fldCodeKol, fldCodeMoin, fldCodeTafzili, fldCodeMarkaz1, fldCodeMarkaz2, fldBedehkar, fldBestankar, fldRadif, fldCodeSharh, fldDate, fldSharh, fldMoinKamel, fldCodeNoeTafzili, fldGhateiMovaghat, fldGhateiDaem, fldSanadGhadim, fldShomarePeigiri, fldMoghayerat, fldType, fldVasete) Select ID, fldSanad,fldCodeKol, fldCodeMoin, fldCodeTafzili, fldCodeMarkaz1, fldCodeMarkaz2, fldBedehkar, fldBestankar, fldRadif, fldCodeSharh, fldDate, fldSharh, fldMoinKamel, fldCodeNoeTafzili, fldGhateiMovaghat, fldGhateiDaem, fldSanadGhadim, fldShomarePeigiri, fldMoghayerat, fldType, fldVasete from tblSodooreSanad Where ID=Convert(varchar,@EmpId) end set @CodeKolMoin =(Select top(1)fldMoinKamel from tblSodooresanad Where ID=@EmpId) execute codemoinhesab 'foroosh' ,@CodekolmoinTanzimat output execute codemoinhesab 'hesabhayedaryaftani' ,@CodekolMoinDaryaftTanzimat output execute codemoinhesab 'check' ,@strMoinKamelCheckDaryafti output execute codemoinhesab 'checkpardakhti' ,@strMoinKamelCheckPardakhti output set @ShomareSanad=(Select top(1)Convert(bigint,fldSanad) from tblSodooresanad Where ID=@EmpId) execute codemoinhesab 'hesabhayepardakhtani' ,@CodekolMoinHesabhayePardakhtaniTanzimat output set @CodekolMoinHesabhayeDaryaftaniTanzimat = @CodekolMoinDaryaftTanzimat --###### فروش ################## if @type='foroosh' ---برای نمایش ریز فاکتور فروش begin if @CodekolmoinTanzimat = @CodeKolMoin --اگر سند فروش بود begin declare @bigDate bigint,@codeKarbar bigint,@NameKarbar nvarchar(20) set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareFactor =(Select fldShomareFactorForoosh from tblForoosh Where fldShomareSanad = @ShomareSanad) update #tmpSooratHesab2 set fldVaziat='delete' where fldSanad=@ShomareSanad And fldMoinKamel<> @CodekolMoinHesabhayeDaryaftaniTanzimat update #tmpSooratHesab2 set fldSharh=' فروش' +case when (fldSharh='' or fldSharh is null) then '' else '-' + fldSharh end where fldSanad=@ShomareSanad And fldMoinKamel= @CodekolMoinHesabhayeDaryaftaniTanzimat --,fldSharh=' فروش' + iif( fldSharh='','', '-' + fldSharh ) update #tmpSooratHesab2 set shomare = 'ف'+Convert(varchar, @ShomareFactor) where fldSanad= @ShomareSanad insert into #tmpSooratHesab2 (ID,shomare,fldSanad,fldRiz, fldDate,fldTedad,fldNameVahed,fldNameVahed2,fldFee,fldFeeYekCarton, fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif, fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales, fldCodeAnbar,fldNameAnbar,fldType) Select Convert(varchar,@EmpId) + Convert(varchar,fldRadif+100),'ف'+Convert(varchar, @ShomareFactor),@ShomareSanad, dbo.tblKala.fldNameKala + ' ' + dbo.tblRizeForoosh.fldSharhKala, @bigDate,dbo.tblRizeForoosh.fldTedadJoz,tblVahed_2.fldNameVahed, dbo.tblVahed.fldNameVahed AS fldNameVahed2,dbo.tblRizeForoosh.fldFeeJoz,dbo.tblRizeForoosh.fldFeeYekCarton, dbo.tblRizeForoosh.fldFeeKol,dbo.tblRizeForoosh.fldDarsadTakhfif,dbo.tblRizeForoosh.fldMablaghTakhfif,dbo.tblRizeForoosh.fldFeeVahed1BadAzTakhfif, dbo.tblRizeForoosh.fldFeeVahed2BadAzTakhfif, dbo.tblRizeForoosh.fldArzeshAfzode, dbo.tblRizeForoosh.fldFeeKolBadAzTakhfif,dbo.tblRizeForoosh.fldMablaghKhales, dbo.tblRizeForoosh.fldCodeAnbar,dbo.tblAnbar.fldNameAnbar,'foroosh' FROM dbo.tblAnbar INNER JOIN dbo.tblRizeForoosh ON dbo.tblAnbar.fldCode = dbo.tblRizeForoosh.fldCodeAnbar INNER JOIN dbo.tblKala ON dbo.tblRizeForoosh.fldCodeKala = dbo.tblKala.fldCodeKala INNER JOIN dbo.tblVahed AS tblVahed_2 ON dbo.tblKala.fldCodeVahed1 = tblVahed_2.ID INNER JOIN dbo.tblVahed ON dbo.tblKala.fldCodeVahed2 = dbo.tblVahed.ID WHERE (dbo.tblRizeForoosh.fldShomareFactor = @ShomareFactor) --اضافه کردن نام کاربر set @codekarbar= (select top(1) fldCodeKarbar from tblAmalkardekarbar where fldShomare=@ShomareFactor and fldNameForm=N'فروش' and NoeAmaliat=N'جدید' order by id desc) if @codekarbar is not null set @namekarbar =(select top(1) fldNameKarbar from tblAmalkardekarbar where fldShomare=@ShomareFactor and fldNameForm=N'فروش' and NoeAmaliat=N'جدید' order by id desc) else begin set @codekarbar=0 set @namekarbar ='' end update #tmpSooratHesab2 set fldCodeKarbar = @codeKarbar,fldNameKarbar=@NameKarbar where fldSanad= @ShomareSanad --### حذف ردیف سندی که کد کل و معینش فروشه ، در حقیقت فقط باید حسابهای دریافتنی و پرداختنی و سندهای دستی نمایش داده بشه end else if @CodekolMoinHesabhayeDaryaftaniTanzimat = @CodeKolMoin -- اگر سند مربوط به دریافت در فروش بود begin set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareSanad=(Select top(1)Convert(bigint,fldSanad) from tblSodooresanad Where ID=@EmpId) set @ShomareFactor =(Select fldShomareFactorForoosh from tblForoosh Where fldShomareSanad = @ShomareSanad) set @ShomareDaryaft =(Select fldShomareDaryaft from tblDaryaft Where fldShomareSanad = @ShomareSanad) set @bigbed = (Select fldBedehkar from tblSodooresanad Where ID=@EmpId) set @bigbes = (Select fldBestankar from tblSodooresanad Where ID=@EmpId) --execute ShowDaryaftInfo @ShomareDaryaft, @str1, @lngAlaki, @lngAlaki2, @SharheKol output, 'daryaft', @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngCheck output, @strSharheCheck output, @lngTakhfif output, @strSharheTakhfifat output, @str2, @str3 --execute ShowDaryaftInfoChandBank @ShomareDaryaft, @str1, @lngAlaki, @lngAlaki2, @SharheKol output, 'daryaft', @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngBank2 output, @strMoinKamelBank2 output, @strShareBank2 output, @lngBank3 output, @strMoinKamelBank3 output, @strShareBank3 output, @lngCheck output, @strSharheCheck output, @lngTakhfif output, @strSharheTakhfifat output, @str2, @str3 execute ShowDaryaftInfoChandBank @ShomareDaryaft, @str1, @lngAlaki, @lngAlaki2, @SharheKol output, 'daryaft', @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngBank2 output, @strMoinKamelBank2 output, @strShareBank2 output, @lngBank3 output, @strMoinKamelBank3 output, @strShareBank3 output, @lngCheck output, @strSharheCheck output, @lngTakhfif output, @strSharheTakhfifat output, @str2, @str3 UPDATE #tmpSooratHesab2 SET fldSharh = @SharheKol where #tmpSooratHesab2.ID = @EmpId update #tmpSooratHesab2 set fldSharh ='دریافت' +case when @SharheKol='' then '' else ' - ' +@SharheKol end , shomare ='ش د' + Convert(varchar,@ShomareDaryaft) + ' ش ف' + Convert(varchar,@ShomareFactor) where fldSanad=@ShomareSanad And fldMoinKamel = @CodekolMoinHesabhayeDaryaftaniTanzimat and fldBestankar>0 update #tmpSooratHesab2 set fldVaziat='delete' where fldSanad=@ShomareSanad And fldMoinKamel<> @CodekolMoinHesabhayeDaryaftaniTanzimat if @bigbes>0 begin if @lngNaghd>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelSandoogh) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '101' ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ش ف' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab +case when @strSharhenaghd='' then '' else '-' +@strSharhenaghd end ,@bigDate,@lngNaghd,'foroosh') end if @lngBank>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '102' ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ش ف' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strShareBank='' then '' else '-' +@strShareBank end ,@bigDate,@lngBank,'foroosh') end if @lngBank2>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank2) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '102' ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ش ف' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strShareBank2='' then '' else '-' +@strShareBank2 end ,@bigDate,@lngBank2,'foroosh') end if @lngBank3>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank3) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '102' ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ش ف' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strShareBank3='' then '' else '-' +@strShareBank3 end ,@bigDate,@lngBank3,'foroosh') end if @lngCheck>0 begin --set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelCheckDaryafti) --set @strSharheHesab ='چک شماره ' insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) select Convert(varchar,@EmpId) +'103' + Convert(varchar,id) ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ش ف' + Convert(varchar,@ShomareFactor) ,@ShomareSanad,'چک شماره ' + Convert(varchar,fldShomareCheck) + ' تاریخ ' + Convert(varchar,fldDateSarResid) + case when @strSharheCheck='' then '' else '-' +@strSharheCheck end ,@bigDate,fldMablagh,'foroosh' from tblCheckeDaryafti Where fldSanad = @ShomareSanad update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad and fldMoinKamel = @strMoinKamelCheckDaryafti end if @lngTakhfif>0 begin set @strSharheHesab = 'تخفیفات فروش ' insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '104' ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ش ف' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab +case when @strSharhenaghd='' then '' else '-' +@strSharhenaghd end ,@bigDate,@lngTakhfif,'foroosh') end end end end set @CodeKolMoin =(Select top(1)fldMoinKamel from tblSodooresanad Where ID=@EmpId)--11301 set @CodeKolSanad =(Select top(1)fldCodeKol from tblSodooresanad Where ID=@EmpId)--113 set @CodeKolTanzimat =(Select top(1)fldCodeKol from tblSodooresanad Where ID=@EmpId)--110 execute CodeKolHesab 'hesabhayedaryaftani' ,@CodeKolTanzimat output--11301 if @type='kharid' ---برای نمایش ریز فاکتور خرید begin execute codemoinhesab 'kharid' ,@CodekolmoinTanzimat output execute codemoinhesab 'hesabhayepardakhtani' ,@CodekolMoinHesabhayePardakhtaniTanzimat output set @ShomareSanad=(Select top(1)Convert(bigint,fldSanad) from tblSodooresanad Where ID=@EmpId) update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad=@ShomareSanad and fldMoinKamel = @strMoinKamelCheckPardakhti update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad=@ShomareSanad and fldMoinKamel = @strMoinKamelCheckdaryafti if @CodekolmoinTanzimat = @CodeKolMoin --اگر سند خرید بود begin set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) end else if @CodekolMoinHesabhayePardakhtaniTanzimat = @CodeKolMoin -- اگر سند مربوط به پرداخت در خرید بود begin set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareSanad=(Select top(1)Convert(bigint,fldSanad) from tblSodooresanad Where ID=@EmpId) set @ShomareFactor =(Select fldShomareFactorKharid from tblKharid Where fldShomareSanad = @ShomareSanad) set @ShomarePardakht =(Select fldShomarePardakht from tblPardakht Where fldShomareSanad = @ShomareSanad) set @bigbed = (Select fldBedehkar from tblSodooresanad Where ID=@EmpId) set @bigbes = (Select fldBestankar from tblSodooresanad Where ID=@EmpId) execute ShowPardakhtInfo @ShomarePardakht, @str1, @lngAlaki, @str1, @SharheKol output, @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngCheck output, @strSharheCheck output,@lngMablagheCheckeVagozari output, @strSCheckVaghozari output, @lngTakhfif output, @strSharheTakhfifat output,0, @str2, @str3 set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareFactor =(Select fldShomareFactorkharid from tblKharid Where fldShomareSanad = @ShomareSanad) if @bigbes>0 or (@bigbes=0 and @bigbed=0) begin update #tmpSooratHesab2 set shomare = 'خ'+Convert(varchar, @ShomareFactor) ,fldSharh=' خرید' +case when fldSharh='' then '' else '-' +fldSharh end where ID=@EmpId update #tmpSooratHesab2 set shomare = 'خ'+Convert(varchar, @ShomareFactor) where fldSanad= @ShomareSanad insert into #tmpSooratHesab2 (ID,shomare,fldSanad,fldRiz, fldDate,fldTedad,fldNameVahed,fldNameVahed2,fldFee,fldFeeYekCarton, fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif, fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales, fldCodeAnbar,fldNameAnbar,fldType) Select Convert(varchar,@EmpId) + Convert(varchar,fldRadif+100),'خ'+Convert(varchar, @ShomareFactor),@ShomareSanad, dbo.tblKala.fldNameKala + ' ' + dbo.tblRizekharid.fldSharhKala, @bigDate,dbo.tblRizekharid.fldTedadJoz,tblVahed_2.fldNameVahed, dbo.tblVahed.fldNameVahed AS fldNameVahed2,dbo.tblRizekharid.fldFeeJoz,dbo.tblRizekharid.fldFeeYekCarton, dbo.tblRizekharid.fldFeeKol,dbo.tblRizekharid.fldDarsadTakhfif,dbo.tblRizekharid.fldMablaghTakhfif,dbo.tblRizekharid.fldFeeVahed1BadAzTakhfif, dbo.tblRizekharid.fldFeeVahed2BadAzTakhfif, dbo.tblRizekharid.fldArzeshAfzode, dbo.tblRizekharid.fldFeeKolBadAzTakhfif,dbo.tblRizekharid.fldMablaghKhales, dbo.tblRizekharid.fldCodeAnbar,dbo.tblAnbar.fldNameAnbar,'kharid' FROM dbo.tblAnbar INNER JOIN dbo.tblRizekharid ON dbo.tblAnbar.fldCode = dbo.tblRizekharid.fldCodeAnbar INNER JOIN dbo.tblKala ON dbo.tblRizekharid.fldCodeKala = dbo.tblKala.fldCodeKala INNER JOIN dbo.tblVahed AS tblVahed_2 ON dbo.tblKala.fldCodeVahed1 = tblVahed_2.ID INNER JOIN dbo.tblVahed ON dbo.tblKala.fldCodeVahed2 = dbo.tblVahed.ID WHERE (dbo.tblRizekharid.fldShomareFactor = @ShomareFactor) end --اضافه کردن نام کاربر set @codekarbar= (select top(1) fldCodeKarbar from tblAmalkardekarbar where fldShomare=@ShomareFactor and fldNameForm=N'خرید' and NoeAmaliat=N'جدید' order by id desc) if @codekarbar is not null set @namekarbar =(select top(1) fldNameKarbar from tblAmalkardekarbar where fldShomare=@ShomareFactor and fldNameForm=N'خرید' and NoeAmaliat=N'جدید' order by id desc) else begin set @codekarbar=0 set @namekarbar ='' end update #tmpSooratHesab2 set fldCodeKarbar = @codeKarbar,fldNameKarbar=@NameKarbar where fldSanad= @ShomareSanad update #tmpSooratHesab2 set fldVaziat='delete' where fldSanad=@ShomareSanad And fldMoinKamel<> @CodekolMoinHesabhayePardakhtaniTanzimat if @bigbed>0 begin update #tmpSooratHesab2 set fldSharh='پرداخت ' + fldSharh ,shomare ='ش پ' + Convert(varchar,@ShomarePardakht) + ' ش خ' + Convert(varchar,@ShomareFactor) where id=@EmpId if @lngNaghd>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelSandoogh) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '100' ,'ش پ' + Convert(varchar,@ShomarePardakht) + ' ش خ' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strSharhenaghd='' then '' else '-' +@strSharhenaghd end ,@bigDate,@lngNaghd,'kharid') end if @lngBank>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '200' ,'ش پ' + Convert(varchar,@ShomarePardakht) + ' ش خ' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strShareBank='' then '' else '-' + @strShareBank end,@bigDate,@lngBank,'kharid') end if @lngCheck>0 begin insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) select Convert(varchar,@EmpId ) + '300' + + Convert(varchar,id) ,'ش پ' + Convert(varchar,@ShomarePardakht) + ' ش خ' + Convert(varchar,@ShomareFactor) ,@ShomareSanad,'شماره چک ' + Convert(varchar,fldShomareCheck) + ' ' + ' تاریخ ' + Convert(varchar,fldDateSarResid) + case when @strSharheCheck='' then '' else '-' +@strSharheCheck end ,@bigDate,fldMablagh,'kharid' from tblCheckePardakhti Where fldSanad = @ShomareSanad end if @lngMablagheCheckeVagozari>0 begin insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) select Convert(varchar,@EmpId ) + '400' + Convert(varchar,id) ,'ش پ' + Convert(varchar,@ShomarePardakht) + ' ش خ' + Convert(varchar,@ShomareFactor) ,@ShomareSanad,'واگذاری چک شماره ' + Convert(varchar,fldShomareCheck) + ' ' + ' تاریخ ' + Convert(varchar,fldDateSarResid) + case when @strSCheckVaghozari='' then '' else '-' +@strSCheckVaghozari end ,@bigDate,fldMablagh,'kharid' from tblCheckeDaryafti Where fldShomarePardakht = @ShomarePardakht end if @lngTakhfif>0 begin set @strSharheHesab = 'تخفیفات خرید ' insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId ) + '500' ,'ش پ' + Convert(varchar,@ShomarePardakht) + ' ش خ' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strSharheTakhfifat='' then '' else '-' + @strSharheTakhfifat end ,@bigDate,@lngTakhfif,'kharid') end end end end execute codemoinhesab 'foroosh' ,@CodekolmoinTanzimat output --###### برگشت از فروش ################## if @type='bargashtazforoosh' ---برای نمایش ریز برگشت از فروش begin set @ShomareSanad=(Select top(1)Convert(bigint,fldSanad) from tblSodooresanad Where ID=@EmpId) update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad=@ShomareSanad and fldMoinKamel = @strMoinKamelCheckPardakhti update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad=@ShomareSanad and fldMoinKamel = @strMoinKamelCheckdaryafti update #tmpSooratHesab2 set fldVaziat='delete' where fldSanad=@ShomareSanad And fldMoinKamel<> @CodekolMoinHesabhayeDaryaftaniTanzimat begin if @CodekolmoinTanzimat = @CodeKolMoin --اگر سند برگشت از فروش بود begin set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareFactor =(Select fldShomareFactorForoosh from tblBargashtAzForoosh Where fldShomareSanad = @ShomareSanad) end else if @CodekolMoinHesabhayeDaryaftaniTanzimat = @CodeKolMoin -- اگر سند مربوط به پرداخت در برگشت از فروش بود begin set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareSanad=(Select top(1)Convert(bigint,fldSanad) from tblSodooresanad Where ID=@EmpId) set @ShomareFactor =(Select fldShomareFactorForoosh from tblBargashtAzForoosh Where fldShomareSanad = @ShomareSanad) set @ShomarePardakht =(Select fldShomarePardakht from tblPardakht Where fldShomareSanad = @ShomareSanad) set @bigbed = (Select fldBedehkar from tblSodooresanad Where ID=@EmpId) set @bigbes = (Select fldBestankar from tblSodooresanad Where ID=@EmpId) execute ShowPardakhtInfo @ShomarePardakht, @str1, @lngAlaki, @str1, @SharheKol output, @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngCheck output, @strSharheCheck output,@lngMablagheCheckeVagozari output, @strSCheckVaghozari output, @lngTakhfif output, @strSharheTakhfifat output,0, @str2, @str3 set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareFactor =(Select fldShomareFactorForoosh from tblBargashtAzForoosh Where fldShomareSanad = @ShomareSanad) --اضافه کردن نام کاربر set @codekarbar= (select top(1) fldCodeKarbar from tblAmalkardekarbar where fldShomare=@ShomareFactor and fldNameForm=N'برگشت از فروش' and NoeAmaliat=N'جدید' order by id desc) if @codekarbar is not null set @namekarbar =(select top(1) fldNameKarbar from tblAmalkardekarbar where fldShomare=@ShomareFactor and fldNameForm=N'برگشت از فروش' and NoeAmaliat=N'جدید' order by id desc) else begin set @codekarbar=0 set @namekarbar ='' end update #tmpSooratHesab2 set fldCodeKarbar = @codeKarbar,fldNameKarbar=@NameKarbar where fldSanad= @ShomareSanad if @bigbes>0 or (@bigbes=0 and @bigbed=0) begin update #tmpSooratHesab2 set shomare = 'ب ف'+Convert(varchar, @ShomareFactor) ,fldSharh=' برگشت از فروش' + case when fldSharh ='' then '' else '-' + fldSharh end where ID=@EmpId update #tmpSooratHesab2 set shomare = 'ب ف'+Convert(varchar, @ShomareFactor) where fldSanad= @ShomareSanad insert into #tmpSooratHesab2 (ID,shomare,fldSanad,fldRiz, fldDate,fldTedad,fldNameVahed,fldNameVahed2,fldFee,fldFeeYekCarton, fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif, fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales, fldCodeAnbar,fldNameAnbar,fldType) Select Convert(varchar,@EmpId) + Convert(varchar,fldRadif+100),'ب ف'+Convert(varchar, @ShomareFactor),@ShomareSanad, dbo.tblKala.fldNameKala + ' ' + dbo.tblRizeBargashtAzForoosh.fldSharhKala, @bigDate,dbo.tblRizeBargashtAzForoosh.fldTedadJoz,tblVahed_2.fldNameVahed, dbo.tblVahed.fldNameVahed AS fldNameVahed2,dbo.tblRizeBargashtAzForoosh.fldFeeJoz,dbo.tblRizeBargashtAzForoosh.fldFeeYekCarton, dbo.tblRizeBargashtAzForoosh.fldFeeKol,dbo.tblRizeBargashtAzForoosh.fldDarsadTakhfif,dbo.tblRizeBargashtAzForoosh.fldMablaghTakhfif,dbo.tblRizeBargashtAzForoosh.fldFeeVahed1BadAzTakhfif, dbo.tblRizeBargashtAzForoosh.fldFeeVahed2BadAzTakhfif, dbo.tblRizeBargashtAzForoosh.fldArzeshAfzode, dbo.tblRizeBargashtAzForoosh.fldFeeKolBadAzTakhfif,dbo.tblRizeBargashtAzForoosh.fldMablaghKhales, dbo.tblRizeBargashtAzForoosh.fldCodeAnbar,dbo.tblAnbar.fldNameAnbar,'bargashtazforoosh' FROM dbo.tblAnbar INNER JOIN dbo.tblRizeBargashtAzForoosh ON dbo.tblAnbar.fldCode = dbo.tblRizeBargashtAzForoosh.fldCodeAnbar INNER JOIN dbo.tblKala ON dbo.tblRizeBargashtAzForoosh.fldCodeKala = dbo.tblKala.fldCodeKala INNER JOIN dbo.tblVahed AS tblVahed_2 ON dbo.tblKala.fldCodeVahed1 = tblVahed_2.ID INNER JOIN dbo.tblVahed ON dbo.tblKala.fldCodeVahed2 = dbo.tblVahed.ID WHERE (dbo.tblRizeBargashtAzForoosh.fldShomareFactor = @ShomareFactor) end if @bigbed>0 begin update #tmpSooratHesab2 set fldSharh='پرداخت ' + fldSharh ,shomare = 'ش پ' + Convert(varchar,@ShomarePardakht) + ' ب ف' + Convert(varchar,@ShomareFactor) where id=@EmpId if @lngNaghd>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelSandoogh) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '100' ,'ش پ' + Convert(varchar,@ShomarePardakht) + ' ب ف' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strSharhenaghd='' then '' else '-' +@strSharhenaghd end,@bigDate,@lngNaghd,'bargashtazforoosh') end if @lngBank>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '200' ,'ش پ' + Convert(varchar,@ShomarePardakht) + ' ش خ' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strShareBank='' then '' else '-' + @strShareBank end ,@bigDate,@lngBank,'bargashtazforoosh') end if @lngCheck>0 begin insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) select Convert(varchar,@EmpId ) + '300' + + Convert(varchar,id) ,'ش پ' + Convert(varchar,@ShomarePardakht) + ' ش خ' + Convert(varchar,@ShomareFactor) ,@ShomareSanad,'شماره چک ' + Convert(varchar,fldShomareCheck) + ' ' + ' تاریخ ' + Convert(varchar,fldDateSarResid) + case when @strSharheCheck='' then '' else '-' +@strSharheCheck end ,@bigDate,fldMablagh,'bargashtazforoosh' from tblCheckePardakhti Where fldSanad = @ShomareSanad end if @lngMablagheCheckeVagozari>0 begin insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) select Convert(varchar,@EmpId ) + '400' + Convert(varchar,id) ,'ش پ' + Convert(varchar,@ShomarePardakht) + ' ش خ' + Convert(varchar,@ShomareFactor) ,@ShomareSanad,'واگذاری چک شماره ' + Convert(varchar,fldShomareCheck) + ' ' + ' تاریخ ' + Convert(varchar,fldDateSarResid) + case when @strSCheckVaghozari='' then '' else '-' +@strSCheckVaghozari end ,@bigDate,fldMablagh,'bargashtazforoosh' from tblCheckeDaryafti Where fldShomarePardakht = @ShomarePardakht end if @lngTakhfif>0 begin set @strSharheHesab = 'تخفیفات فروش ' insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId ) + '500' ,'ش پ' + Convert(varchar,@ShomarePardakht) + ' ش خ' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strSharheTakhfifat='' then '' else ' - ' +@strSharheTakhfifat end,@bigDate,@lngTakhfif,'bargashtazforoosh') end end end end end if @type='bargashtazkharid' ---برای نمایش ریز برگشت از خرید begin execute codemoinhesab 'kharid' ,@CodekolmoinTanzimat output execute codemoinhesab 'hesabhayepardakhtani' ,@CodekolMoinHesabhayePardakhtaniTanzimat output set @ShomareSanad=(Select top(1)Convert(bigint,fldSanad) from tblSodooresanad Where ID=@EmpId) update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad=@ShomareSanad and fldMoinKamel = @strMoinKamelCheckPardakhti update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad=@ShomareSanad and fldMoinKamel = @strMoinKamelCheckdaryafti set @ShomareFactor =(Select fldShomareFactorKharid from tblBargashtAzKharid Where fldShomareSanad = @ShomareSanad) update #tmpSooratHesab2 set fldSharh ='دریافت' + case when @SharheKol='' then '' else ' - ' +@SharheKol end ,Shomare='ش د' + Convert(varchar,@ShomareDaryaft) + ' ب خ' + Convert(varchar, @ShomareFactor ) where fldSanad=@ShomareSanad And fldMoinKamel = @CodekolMoinHesabhayePardakhtaniTanzimat and fldBestankar>0 if @CodekolmoinTanzimat = @CodeKolMoin --اگر سند خرید بود begin set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) end else if @CodekolMoinHesabhayePardakhtaniTanzimat = @CodeKolMoin -- اگر سند مربوط به دریافت در برگشت از خرید بود begin set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareSanad=(Select top(1)Convert(bigint,fldSanad) from tblSodooresanad Where ID=@EmpId) set @ShomareFactor =(Select fldShomareFactorKharid from tblBargashtAzKharid Where fldShomareSanad = @ShomareSanad) set @ShomareDaryaft =(Select fldShomareDaryaft from tblDaryaft Where fldShomareSanad = @ShomareSanad) set @bigbed = (Select fldBedehkar from tblSodooresanad Where ID=@EmpId) set @bigbes = (Select fldBestankar from tblSodooresanad Where ID=@EmpId) --execute ShowDaryaftInfo @ShomareDaryaft, @str1, @lngAlaki, @lngAlaki2, @SharheKol output, 'bargashtazkharid', @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngCheck output, @strSharheCheck output, @lngTakhfif output, @strSharheTakhfifat output, @str2, @str3 execute ShowDaryaftInfoChandBank @ShomareDaryaft, @str1, @lngAlaki, @lngAlaki2, @SharheKol output, 'bargashtazkharid', @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngBank2 output, @strMoinKamelBank2 output, @strShareBank2 output, @lngBank3 output, @strMoinKamelBank3 output, @strShareBank3 output, @lngCheck output, @strSharheCheck output, @lngTakhfif output, @strSharheTakhfifat output, @str2, @str3 --execute ShowDaryaftInfoChandBank @ShomareDaryaft, @str1, @lngAlaki, @lngAlaki2, @SharheKol output, 'bargashtazkharid', @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngCheck output, @strSharheCheck output, @lngTakhfif output, @strSharheTakhfifat output, @str2, @str3 set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareFactor =(Select fldShomareFactorkharid from tblBargashtAzKharid Where fldShomareSanad = @ShomareSanad) --اضافه کردن نام کاربر set @codekarbar= (select top(1) fldCodeKarbar from tblAmalkardekarbar where fldShomare=@ShomareFactor and fldNameForm=N'برگشت از خرید' and NoeAmaliat=N'جدید' order by id desc) if @codekarbar is not null set @namekarbar =(select top(1) fldNameKarbar from tblAmalkardekarbar where fldShomare=@ShomareFactor and fldNameForm=N'برگشت از خرید' and NoeAmaliat=N'جدید' order by id desc) else begin set @codekarbar=0 set @namekarbar ='' end update #tmpSooratHesab2 set fldCodeKarbar = @codeKarbar,fldNameKarbar=@NameKarbar where fldSanad= @ShomareSanad if @bigbed>0 or (@bigbes=0 and @bigbed=0) begin update #tmpSooratHesab2 set shomare = 'ب خ'+Convert(varchar, @ShomareFactor) ,fldSharh=' برگشت از خرید' + case when fldSharh='' then '' else '-' +fldSharh end where ID=@EmpId update #tmpSooratHesab2 set shomare = 'ب خ'+Convert(varchar, @ShomareFactor) where fldSanad= @ShomareSanad insert into #tmpSooratHesab2 (ID,shomare,fldSanad,fldRiz, fldDate,fldTedad,fldNameVahed,fldNameVahed2,fldFee,fldFeeYekCarton, fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif, fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales, fldCodeAnbar,fldNameAnbar,fldType) Select Convert(varchar,@EmpId) + Convert(varchar,fldRadif+100),'ب خ'+Convert(varchar, @ShomareFactor),@ShomareSanad, dbo.tblKala.fldNameKala + ' ' + dbo.tblRizeBargashtAzkharid.fldSharhKala, @bigDate,dbo.tblRizeBargashtAzkharid.fldTedadJoz,tblVahed_2.fldNameVahed, dbo.tblVahed.fldNameVahed AS fldNameVahed2,dbo.tblRizeBargashtAzkharid.fldFeeJoz,dbo.tblRizeBargashtAzkharid.fldFeeYekCarton, dbo.tblRizeBargashtAzkharid.fldFeeKol,dbo.tblRizeBargashtAzkharid.fldDarsadTakhfif,dbo.tblRizeBargashtAzkharid.fldMablaghTakhfif,dbo.tblRizeBargashtAzkharid.fldFeeVahed1BadAzTakhfif, dbo.tblRizeBargashtAzkharid.fldFeeVahed2BadAzTakhfif, dbo.tblRizeBargashtAzkharid.fldArzeshAfzode, dbo.tblRizeBargashtAzkharid.fldFeeKolBadAzTakhfif,dbo.tblRizeBargashtAzkharid.fldMablaghKhales, dbo.tblRizeBargashtAzkharid.fldCodeAnbar,dbo.tblAnbar.fldNameAnbar,'bargashtazkharid' FROM dbo.tblAnbar INNER JOIN dbo.tblRizeBargashtAzkharid ON dbo.tblAnbar.fldCode = dbo.tblRizeBargashtAzkharid.fldCodeAnbar INNER JOIN dbo.tblKala ON dbo.tblRizeBargashtAzkharid.fldCodeKala = dbo.tblKala.fldCodeKala INNER JOIN dbo.tblVahed AS tblVahed_2 ON dbo.tblKala.fldCodeVahed1 = tblVahed_2.ID INNER JOIN dbo.tblVahed ON dbo.tblKala.fldCodeVahed2 = dbo.tblVahed.ID WHERE (dbo.tblRizeBargashtAzkharid.fldShomareFactor = @ShomareFactor) update #tmpSooratHesab2 set fldVaziat='delete' where fldSanad=@ShomareSanad And fldMoinKamel<> @CodekolMoinHesabhayePardakhtaniTanzimat begin --set @ShomareSanad=@ShomareSanad if @lngNaghd>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelSandoogh) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId+1) + '101' ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ب خ' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strSharhenaghd='' then '' else '-' +@strSharhenaghd end ,@bigDate,@lngNaghd,'bargashtazkharid') end if @lngBank>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId+1) + '102' ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ب خ' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strShareBank='' then '' else '-' +@strShareBank end ,@bigDate,@lngBank,'bargashtazkharid') end if @lngBank2>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank2) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId+1) + '102' ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ب خ' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strShareBank2='' then '' else '-' +@strShareBank2 end ,@bigDate,@lngBank2,'bargashtazkharid') end if @lngBank3>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank3) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId+1) + '102' ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ب خ' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strShareBank3='' then '' else '-' +@strShareBank3 end ,@bigDate,@lngBank3,'bargashtazkharid') end if @lngCheck>0 begin insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) select Convert(varchar,@EmpId+1) +'103' + Convert(varchar,id) ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ب خ' + Convert(varchar,@ShomareFactor) ,@ShomareSanad,'چک شماره ' + Convert(varchar,fldShomareCheck) + ' تاریخ ' + Convert(varchar,fldDateSarResid) + case when @strSharheCheck='' then '' else '-' +@strSharheCheck end ,@bigDate,fldMablagh,'bargashtazkharid' from tblCheckeDaryafti Where fldSanad = @ShomareSanad update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad and fldMoinKamel = @strMoinKamelCheckDaryafti end if @lngTakhfif>0 begin set @strSharheHesab = 'تخفیفات خرید ' insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId+1) + '104' ,'ش د' + Convert(varchar,@ShomareDaryaft) + ' ب خ' + Convert(varchar,@ShomareFactor),@ShomareSanad,@strSharheHesab + case when @strSharhenaghd='' then '' else '-' +@strSharhenaghd end ,@bigDate,@lngTakhfif,'bargashtazkharid') end end end end end if @type='daryaft' ---برای نمایش ریز دریافت مستقیم begin set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareDaryaft =(Select fldShomareDaryaft from tblDaryaft Where fldShomareSanad = @ShomareSanad) --if @ShomareSanad=408 -- begin -- INSERT INTO dbo.tblTest5 -- (name1, name2, name3) -- VALUES (@bigDate,@ShomareDaryaft,'aa') --end set @bigbed = (Select fldBedehkar from tblSodooresanad Where ID=@EmpId) set @bigbes = (Select fldBestankar from tblSodooresanad Where ID=@EmpId) update #tmpSooratHesab2 set fldSharh='دریافت' ,Shomare='ش د' + Convert(varchar,@ShomareDaryaft) Where ID=@EmpId --execute ShowDaryaftInfo @ShomareDaryaft, @str1, @lngAlaki, @lngAlaki2, @SharheKol output, 'bargashtazkharid', @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngCheck output, @strSharheCheck output, @lngTakhfif output, @strSharheTakhfifat output, @str2, @str3 execute ShowDaryaftInfoChandBank @ShomareDaryaft, @str1, @lngAlaki, @lngAlaki2, @SharheKol output, 'bargashtazkharid', @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngBank2 output, @strMoinKamelBank2 output, @strShareBank2 output, @lngBank3 output, @strMoinKamelBank3 output, @strShareBank3 output, @lngCheck output, @strSharheCheck output, @lngTakhfif output, @strSharheTakhfifat output, @str2, @str3 --اضافه کردن نام کاربر set @codekarbar= (select top(1) fldCodeKarbar from tblAmalkardekarbar where fldShomare=@ShomareDaryaft and fldNameForm='دريافت' and NoeAmaliat='جدید' order by id desc) if @codekarbar is not null set @namekarbar =(select top(1) fldNameKarbar from tblAmalkardekarbar where fldShomare=@ShomareDaryaft and fldNameForm=N'دريافت' and NoeAmaliat='جدید' order by id desc) else begin set @codekarbar=0 set @namekarbar ='' end update #tmpSooratHesab2 set fldCodeKarbar = @codeKarbar,fldNameKarbar=@NameKarbar where fldSanad= @ShomareSanad if @bigbes>0 begin if @lngNaghd>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelSandoogh) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '101' ,'ش د' + Convert(varchar,@ShomareDaryaft) ,@ShomareSanad,@strSharheHesab + case when @strSharhenaghd='' then '' else '-' +@strSharhenaghd end ,@bigDate,@lngNaghd,'daryaft') end if @lngBank>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId)+ '102' ,'ش د' + Convert(varchar,@ShomareDaryaft) ,@ShomareSanad,@strSharheHesab + case when @strShareBank='' then '' else '-' +@strShareBank end ,@bigDate,@lngBank,'daryaft') end if @lngBank2>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank2) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId)+ '102' ,'ش د' + Convert(varchar,@ShomareDaryaft) ,@ShomareSanad,@strSharheHesab + case when @strShareBank2='' then '' else '-' +@strShareBank2 end ,@bigDate,@lngBank2,'daryaft') end if @lngBank3>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank3) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId)+ '102' ,'ش د' + Convert(varchar,@ShomareDaryaft) ,@ShomareSanad,@strSharheHesab + case when @strShareBank3='' then '' else '-' +@strShareBank3 end ,@bigDate,@lngBank3,'daryaft') end if @lngCheck>0 begin insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) select Convert(varchar,@EmpId)+'103' + Convert(varchar,id) ,'ش د' + Convert(varchar,@ShomareDaryaft) ,@ShomareSanad,'چک شماره ' + Convert(varchar,fldShomareCheck) + ' تاریخ ' + Convert(varchar,fldDateSarResid) + case when @strSharheCheck='' then '' else '-' +@strSharheCheck end ,@bigDate,fldMablagh,'daryaft' from tblCheckeDaryafti Where fldSanad = @ShomareSanad update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad and fldMoinKamel = @strMoinKamelCheckDaryafti end if @lngTakhfif>0 begin set @strSharheHesab = 'تخفیفات دریافت ' insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '104' ,'ش د' + Convert(varchar,@ShomareDaryaft) ,@ShomareSanad,@strSharheHesab + case when @strSharhenaghd='' then '' else '-' +@strSharhenaghd end ,@bigDate,@lngTakhfif,'daryaft') end end end if @type = 'pardakht' or @type = 'pardakhtsayer' or @Type='pardakhtpersonel' -- اگر سند مربوط به پرداخت اصلی بود begin set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareSanad=(Select top(1)Convert(bigint,fldSanad) from tblSodooresanad Where ID=@EmpId) set @ShomarePardakht =(Select fldShomarePardakht from tblPardakht Where fldShomareSanad = @ShomareSanad) set @bigbed = (Select fldBedehkar from tblSodooresanad Where ID=@EmpId) set @bigbes = (Select fldBestankar from tblSodooresanad Where ID=@EmpId) execute ShowPardakhtInfo @ShomarePardakht, @str1, @lngAlaki, @str1, @SharheKol output, @lngNaghd output, @strMoinKamelSandoogh output, @strSharhenaghd output, @lngBank output, @strMoinKamelBank output, @strShareBank output, @lngCheck output, @strSharheCheck output,@lngMablagheCheckeVagozari output, @strSCheckVaghozari output, @lngTakhfif output, @strSharheTakhfifat output,0, @str2, @str3 --اضافه کردن نام کاربر set @codekarbar= (select top(1) fldCodeKarbar from tblAmalkardekarbar where fldShomare=@ShomarePardakht and fldNameForm='پرداخت' and NoeAmaliat='جدید' order by id desc) if @codekarbar is not null set @namekarbar =(select top(1) fldNameKarbar from tblAmalkardekarbar where fldShomare=@ShomarePardakht and fldNameForm=N'پرداخت' and NoeAmaliat='جدید' order by id desc) else begin set @codekarbar=0 set @namekarbar ='' end update #tmpSooratHesab2 set fldCodeKarbar = @codeKarbar,fldNameKarbar=@NameKarbar where fldSanad= @ShomareSanad set @bigDate=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) if @bigbes>0 begin declare @CheckKolInSanad nvarchar(10) set @CheckKolInSanad=(Select fldCodeKol from tblSodooreSanad Where id=@EmpId) if @CodeKoleMoinCheck=@CheckKolInSanad delete #tmpSooratHesab2 where id =@EmpId --update #tmpSooratHesab2 set fldSharh=@CodeKoleMoinCheck + ' ' +@CheckKolInSanad where ID=@EmpId end --if @bigbes>0 -- begin --update #tmpSooratHesab2 set shomare = 'ب ف'+Convert(varchar, @ShomareFactor) ,fldSharh=' برگشت از فروش' + iif( fldSharh ='','','-' + fldSharh ) where ID=@EmpId --update #tmpSooratHesab2 set shomare = 'ب ف'+Convert(varchar, @ShomareFactor) where fldSanad= @ShomareSanad --insert into #tmpSooratHesab2 (ID,shomare,fldSanad,fldRiz, -- fldDate,fldTedad,fldNameVahed,fldNameVahed2,fldFee,fldFeeYekCarton, -- fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif, -- fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales, -- fldCodeAnbar,fldNameAnbar) --Select Convert(varchar,@EmpId) + Convert(varchar,fldRadif+100),'ب ف'+Convert(varchar, @ShomareFactor),@ShomareSanad, dbo.tblKala.fldNameKala + ' ' + dbo.tblRizeBargashtAzForoosh.fldSharhKala, -- @bigDate,dbo.tblRizeBargashtAzForoosh.fldTedadJoz,tblVahed_2.fldNameVahed, dbo.tblVahed.fldNameVahed AS fldNameVahed2,dbo.tblRizeBargashtAzForoosh.fldFeeJoz,dbo.tblRizeBargashtAzForoosh.fldFeeYekCarton, -- dbo.tblRizeBargashtAzForoosh.fldFeeKol,dbo.tblRizeBargashtAzForoosh.fldDarsadTakhfif,dbo.tblRizeBargashtAzForoosh.fldMablaghTakhfif,dbo.tblRizeBargashtAzForoosh.fldFeeVahed1BadAzTakhfif, -- dbo.tblRizeBargashtAzForoosh.fldFeeVahed2BadAzTakhfif, dbo.tblRizeBargashtAzForoosh.fldArzeshAfzode, dbo.tblRizeBargashtAzForoosh.fldFeeKolBadAzTakhfif,dbo.tblRizeBargashtAzForoosh.fldMablaghKhales, -- dbo.tblRizeBargashtAzForoosh.fldCodeAnbar,dbo.tblAnbar.fldNameAnbar --FROM dbo.tblAnbar INNER JOIN -- dbo.tblRizeBargashtAzForoosh ON dbo.tblAnbar.fldCode = dbo.tblRizeBargashtAzForoosh.fldCodeAnbar INNER JOIN -- dbo.tblKala ON dbo.tblRizeBargashtAzForoosh.fldCodeKala = dbo.tblKala.fldCodeKala INNER JOIN -- dbo.tblVahed AS tblVahed_2 ON dbo.tblKala.fldCodeVahed1 = tblVahed_2.ID INNER JOIN -- dbo.tblVahed ON dbo.tblKala.fldCodeVahed2 = dbo.tblVahed.ID --WHERE (dbo.tblRizeBargashtAzForoosh.fldShomareFactor = @ShomareFactor) --end if @bigbed>0 begin -- declare @mmm nvarchar(10) -- set @mmm=(select fldMoinKamel from #tmpSooratHesab2 where fldSanad=@ShomareSanad) -- delete tblTest5 -- INSERT INTO dbo.tblTest5 -- (name1, name2, name3) -- VALUES (@mmm,@CodeMoinKamelHesabhayePardakhtaniTanzimat,0) --select * from tbltest5 update #tmpSooratHesab2 set fldVaziat='delete' where fldSanad=@ShomareSanad And (fldcodekol not like @CodekolHesabhayePardakhtaniTanzimat + '%' and fldMoinKamel not like @CodeMoinKamelHesabhayePardakhtaniTanzimat and fldCodeKol<>@KolSooratHesab1 and fldCodeKol<>@KolSooratHesab2 and fldCodeKol<>@KolSooratHesab3) update #tmpSooratHesab2 set fldSharh='پرداخت ' + fldSharh ,Shomare='ش پ' + Convert(varchar,@ShomarePardakht) where id=@EmpId if @lngNaghd>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelSandoogh) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '100' ,'ش پ' + Convert(varchar,@ShomarePardakht) ,@ShomareSanad,@strSharheHesab + case when @strSharhenaghd='' then '' else '-' +@strSharhenaghd end,@bigDate,@lngNaghd,'pardakht') end if @lngBank>0 begin set @strSharheHesab = (Select fldSharhehesab from tblKolMoin Where fldCodeHesab =@strMoinKamelBank) insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId) + '200' ,'ش پ' + Convert(varchar,@ShomarePardakht) ,@ShomareSanad,@strSharheHesab + case when @strShareBank='' then '' else '-' + @strShareBank end ,@bigDate,@lngBank,'pardakht') end if @lngCheck>0 begin insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) select Convert(varchar,@EmpId ) + '300' + + Convert(varchar,id) ,'cc' + Convert(varchar,@ShomarePardakht) ,@ShomareSanad,'شماره چک ' + Convert(varchar,fldShomareCheck) + ' ' + ' تاریخ ' + Convert(varchar,fldDateSarResid) + case when @strSharheCheck='' then '' else '-' +@strSharheCheck end ,@bigDate,fldMablagh,'pardakht' from tblCheckePardakhti Where fldSanad = @ShomareSanad end ----INSERT INTO dbo.tblTest5 ---- (name1, name2, name3) ---- VALUES (@lngMablagheCheckeVagozari,'','hh') if @lngMablagheCheckeVagozari>0 begin insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) select Convert(varchar,@EmpId ) + '400' + Convert(varchar,id) ,'ش پ' ,@ShomareSanad,'واگذاری چک شماره ' + Convert(varchar,fldShomareCheck) + ' ' + ' تاریخ ' + Convert(varchar,fldDateSarResid) + case when @strSCheckVaghozari='' then '' else '-' +@strSCheckVaghozari end ,@bigDate,fldMablagh,'pardakht' from tblCheckeDaryafti Where fldShomarePardakht = @ShomarePardakht end if @lngTakhfif>0 begin set @strSharheHesab = 'تخفیفات پرداخت ' insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) values (Convert(varchar,@EmpId ) + '500' ,'ش پ' + Convert(varchar,@ShomarePardakht) ,@ShomareSanad,@strSharheHesab + case when @strSharheTakhfifat='' then '' else ' - ' +@strSharheTakhfifat end ,@bigDate,@lngTakhfif,'pardakht') end end end if @type='EbtedaAshkhas' begin update #tmpSooratHesab2 set fldSharh='طلب ابتدای دوره' + case when fldSharh<>'' and fldSharh is not null then '-' +fldSharh else '' end,fldType='ebtedadore' Where fldBestankar>0 and ID=@EmpId update #tmpSooratHesab2 set fldSharh='بدهی ابتدای دوره' + case when fldSharh<>'' and fldSharh is not null then '-' +fldSharh else '' end,fldType='ebtedadore' Where fldBedehkar>0 and ID=@EmpId end if @type is null begin declare @strNameHesab nvarchar(50),@strCodeMoinKamel nvarchar(50) set @strcodemoinkamel = (Select fldMoinKamel from #tmpSooratHesab2 Where ID=@EmpId) execute NameKolMoin @strcodemoinkamel ,@strNameHesab output update #tmpSooratHesab2 set fldSharh=@strNameHesab + case when fldSharh<>'' and fldSharh is not null then '-' +fldSharh else '' end,fldType='other' Where ID=@EmpId end if @type='amani' begin declare @ShomareAmani bigint declare @bigDate2 bigint set @bigDate2=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareAmani =(Select Top(1)fldShomareAmani from tblRizeForoosh Where fldShomareSanad = @ShomareSanad) update #tmpSooratHesab2 set shomare = 'امانی'+Convert(varchar, @ShomareAmani), fldBedehkar=0, fldSharh='فروش امانی' + case when fldSharh<>'' and fldSharh is not null then '-' +fldSharh else '' end,fldType='amani' Where fldBedehkar>0 and ID=@EmpId insert into #tmpSooratHesab2 (ID,shomare,fldSanad,fldRiz, fldDate,fldTedad,fldNameVahed,fldNameVahed2,fldFee,fldFeeYekCarton, fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif, fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales, fldCodeAnbar,fldNameAnbar,fldType) Select Convert(varchar,@EmpId) + Convert(varchar,fldRadif+100),'امانی'+Convert(varchar, @ShomareAmani),@ShomareSanad, dbo.tblKala.fldNameKala + ' ' + dbo.tblRizeForoosh.fldSharhKala, @bigDate2,dbo.tblRizeForoosh.fldTedadJoz,tblVahed_2.fldNameVahed, dbo.tblVahed.fldNameVahed AS fldNameVahed2,dbo.tblRizeForoosh.fldFeeJoz,dbo.tblRizeForoosh.fldFeeYekCarton, dbo.tblRizeForoosh.fldFeeKol,dbo.tblRizeForoosh.fldDarsadTakhfif,dbo.tblRizeForoosh.fldMablaghTakhfif,dbo.tblRizeForoosh.fldFeeVahed1BadAzTakhfif, dbo.tblRizeForoosh.fldFeeVahed2BadAzTakhfif, dbo.tblRizeForoosh.fldArzeshAfzode, dbo.tblRizeForoosh.fldFeeKolBadAzTakhfif,dbo.tblRizeForoosh.fldMablaghKhales, dbo.tblRizeForoosh.fldCodeAnbar,dbo.tblAnbar.fldNameAnbar,'amani' FROM dbo.tblAnbar INNER JOIN dbo.tblRizeForoosh ON dbo.tblAnbar.fldCode = dbo.tblRizeForoosh.fldCodeAnbar INNER JOIN dbo.tblKala ON dbo.tblRizeForoosh.fldCodeKala = dbo.tblKala.fldCodeKala INNER JOIN dbo.tblVahed AS tblVahed_2 ON dbo.tblKala.fldCodeVahed1 = tblVahed_2.ID INNER JOIN dbo.tblVahed ON dbo.tblKala.fldCodeVahed2 = dbo.tblVahed.ID WHERE (dbo.tblRizeForoosh.fldShomareAmani = @ShomareAmani) end if @type='bargashtamani' begin --declare @ShomareAmani bigint --declare @bigDate2 bigint set @bigDate2=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareAmani =(Select Top(1)fldShomareBargashtAmani from tblRizeBargashtAzForoosh Where fldShomareSanad = @ShomareSanad) update #tmpSooratHesab2 set shomare = 'برگشت امانی'+Convert(varchar, @ShomareAmani), fldBestankar=0, fldSharh='برگشت امانی' + case when fldSharh<>'' and fldSharh is not null then '-' +fldSharh else '' end,fldType='bargashtamani' Where fldBestankar>0 and ID=@EmpId insert into #tmpSooratHesab2 (ID,shomare,fldSanad,fldRiz, fldDate,fldTedad,fldNameVahed,fldNameVahed2,fldFee,fldFeeYekCarton, fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif, fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales, fldCodeAnbar,fldNameAnbar,fldType) Select Convert(varchar,@EmpId) + Convert(varchar,fldRadif+100),'برگشت امانی'+Convert(varchar, @ShomareAmani),@ShomareSanad, dbo.tblKala.fldNameKala + ' ' + dbo.tblRizeBargashtAzForoosh.fldSharhKala, @bigDate2,dbo.tblRizeBargashtAzForoosh.fldTedadJoz,tblVahed_2.fldNameVahed, dbo.tblVahed.fldNameVahed AS fldNameVahed2,dbo.tblRizeBargashtAzForoosh.fldFeeJoz,dbo.tblRizeBargashtAzForoosh.fldFeeYekCarton, dbo.tblRizeBargashtAzForoosh.fldFeeKol,dbo.tblRizeBargashtAzForoosh.fldDarsadTakhfif,dbo.tblRizeBargashtAzForoosh.fldMablaghTakhfif,dbo.tblRizeBargashtAzForoosh.fldFeeVahed1BadAzTakhfif, dbo.tblRizeBargashtAzForoosh.fldFeeVahed2BadAzTakhfif, dbo.tblRizeBargashtAzForoosh.fldArzeshAfzode, dbo.tblRizeBargashtAzForoosh.fldFeeKolBadAzTakhfif,dbo.tblRizeBargashtAzForoosh.fldMablaghKhales, dbo.tblRizeBargashtAzForoosh.fldCodeAnbar,dbo.tblAnbar.fldNameAnbar,'bargashtamani' FROM dbo.tblAnbar INNER JOIN dbo.tblRizeBargashtAzForoosh ON dbo.tblAnbar.fldCode = dbo.tblRizeBargashtAzForoosh.fldCodeAnbar INNER JOIN dbo.tblKala ON dbo.tblRizeBargashtAzForoosh.fldCodeKala = dbo.tblKala.fldCodeKala INNER JOIN dbo.tblVahed AS tblVahed_2 ON dbo.tblKala.fldCodeVahed1 = tblVahed_2.ID INNER JOIN dbo.tblVahed ON dbo.tblKala.fldCodeVahed2 = dbo.tblVahed.ID WHERE (dbo.tblRizeBargashtAzForoosh.fldShomareBargashtAmani = @ShomareAmani) end if @type='checkebteda' begin --declare @ShomareAmani bigint --declare @bigDate2 bigint set @bigDate2=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) --set @ShomareAmani =(Select Top(1)fldShomareAmani from tblRizeForoosh Where fldShomareSanad = @ShomareSanad) update #tmpSooratHesab2 set fldBedehkar=0,fldBestankar=0, shomare = 'چک دریافتی ابتدای دوره', fldSharh='چک ابتدای دوره' + case when fldSharh<>'' and fldSharh is not null then '-' +fldSharh else '' end,fldType='checkebteda' Where fldBedehkar>0 and ID=@EmpId insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) select Convert(varchar,@EmpId)+'103' + Convert(varchar,id) ,'چک ابتدای دوره' ,@ShomareSanad,'چک شماره ' + Convert(varchar,fldShomareCheck) + ' تاریخ ' + Convert(varchar,fldDateSarResid) + case when @strSharheCheck='' then '' else '-' +@strSharheCheck end ,@bigDate2,fldMablagh,'checkebteda' from tblCheckeDaryafti Where fldSanad = @ShomareSanad and fldCodeSahebeCheck=12326 --insert into #tmpSooratHesab2 (ID,shomare,fldSanad,fldRiz, -- fldDate,fldTedad,fldNameVahed,fldNameVahed2,fldFee,fldFeeYekCarton, -- fldFeeKol,fldDarsadTakhfif,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif, -- fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales, -- fldCodeAnbar,fldNameAnbar,fldType) -- Select Convert(varchar,@EmpId) + Convert(varchar,fldRadif+100),'امانی'+Convert(varchar, @ShomareAmani),@ShomareSanad, dbo.tblKala.fldNameKala + ' ' + dbo.tblRizeForoosh.fldSharhKala, -- @bigDate2,dbo.tblRizeForoosh.fldTedadJoz,tblVahed_2.fldNameVahed, dbo.tblVahed.fldNameVahed AS fldNameVahed2,dbo.tblRizeForoosh.fldFeeJoz,dbo.tblRizeForoosh.fldFeeYekCarton, -- dbo.tblRizeForoosh.fldFeeKol,dbo.tblRizeForoosh.fldDarsadTakhfif,dbo.tblRizeForoosh.fldMablaghTakhfif,dbo.tblRizeForoosh.fldFeeVahed1BadAzTakhfif, -- dbo.tblRizeForoosh.fldFeeVahed2BadAzTakhfif, dbo.tblRizeForoosh.fldArzeshAfzode, dbo.tblRizeForoosh.fldFeeKolBadAzTakhfif,dbo.tblRizeForoosh.fldMablaghKhales, -- dbo.tblRizeForoosh.fldCodeAnbar,dbo.tblAnbar.fldNameAnbar,'amani' -- FROM dbo.tblAnbar INNER JOIN -- dbo.tblRizeForoosh ON dbo.tblAnbar.fldCode = dbo.tblRizeForoosh.fldCodeAnbar INNER JOIN -- dbo.tblKala ON dbo.tblRizeForoosh.fldCodeKala = dbo.tblKala.fldCodeKala INNER JOIN -- dbo.tblVahed AS tblVahed_2 ON dbo.tblKala.fldCodeVahed1 = tblVahed_2.ID INNER JOIN -- dbo.tblVahed ON dbo.tblKala.fldCodeVahed2 = dbo.tblVahed.ID -- WHERE (dbo.tblRizeForoosh.fldShomareAmani = @ShomareAmani) end if @type='BargashteCheckeVagoz' ---برگشت چک واگذارشده begin declare @ShomarePardakht2 bigint,@CodeTafzili2 nvarchar(10),@SumBed2 bigint,@Bed2 bigint,@ShomareSanad2 bigint,@cnt2 bigint,@ShomareEnteghal bigint,@CodeMoin2 nvarchar(50) set @bigDate2=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareSanad2=(Select fldSanad from tblSodooresanad Where ID=@EmpId) set @CodeTafzili2=(Select fldCodeTafzili from tblSodooresanad Where ID=@EmpId) set @SumBed2=(Select Sum(fldBedehkar) as SumBed from tblSodooreSanad Where fldCodeTafzili=@CodeTafzili2 And fldBedehkar>0 and fldSanad=@ShomareSanad2) set @Bed2=(Select top(1)fldBedehkar from tblSodooreSanad Where ID=@EmpId) set @ShomareEnteghal=(Select top(1)fldShomareEnteghal from tblCheckeDaryafti Where fldShomareSanadEnteghal=@ShomareSanad2) execute CodeMoinHesab 'check',@codemoin2 output --insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) -- values ( 999 ,'621' ,132,'345',990205,111,'dfg') --set @cnt2=(Select count(fldCodeTafzili) from tblTafzili Where fldCodeTafzili=@CodeTafzili2 and ID<@EmpId ) --if @cnt2=0 -- begin if @Bed2>0 begin set @ShomarePardakht2=(Select top(1)fldShomarePardakht from tblCheckeDaryafti Where fldShomareSanadEnteghal=@ShomareSanad2) --update #tmpSooratHesab2 set fldBestankar=6666, shomare =@ShomareEnteghal, fldSharh='' + case when fldSharh<>'' and fldSharh is not null then ' ' +fldSharh else '' end,fldType='BargashteCheckeVagozarShode' Where fldBedehkar>0 and ID=@EmpId --insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) -- select Convert(varchar,@EmpId)+'103' ,'bbb' ,@ShomareSanad2,'چک شماره ' + Convert(varchar,fldShomareCheck) + ' تاریخ ' + Convert(varchar,fldDateSarResid) ,@bigDate2,fldMablagh,'BargashteCheckeVagozarShode' -- from tblCheckeDaryafti Where fldShomareSanadEnteghal = @ShomareSanad2 and fldShomarePardakht=21 --update #tmpSooratHesab2 set shomare =34 Where id=@EmpId --and fldMoinKamel <> @codemoin2 and fldCodeTafzili=@CodeTafzili2 update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad2 and fldMoinKamel = @codemoin2 and fldCodeTafzili=@CodeTafzili2 end else begin update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad2 and fldMoinKamel = @codemoin2 and fldCodeTafzili=@CodeTafzili2 update #tmpSooratHesab2 set shomare =@ShomareEnteghal Where id=@EmpId --and fldMoinKamel <> @codemoin2 and fldCodeTafzili=@CodeTafzili2 end -- end --else -- update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad2 and fldMoinKamel = '31301' and fldCodeTafzili=@CodeTafzili2 --set @ShomarePardakht2=(Select top(1)fldShomarePardakht from tblCheckeDaryafti Where fldShomareCheck) --set @ShomarePardakht2=(select fldCodeTafzili from tblPardakht where fldShomarePardakht=@ShomarePardakht2) --select * from tblCheckeDaryafti Where fldShomareSanadEnteghal = 132 end if @type='OdateCheck' ---عودت چک فروخته شده begin declare @ShomarePardakht3 bigint,@CodeTafzili3 nvarchar(10),@SumBed3 bigint,@BeS3 bigint,@ShomareSanad3 bigint,@cnt3 bigint,@ShomareEnteghal3 bigint,@CodeMoin3 nvarchar(50) set @bigDate2=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareSanad3=(Select fldSanad from tblSodooresanad Where ID=@EmpId) set @CodeTafzili3=(Select fldCodeTafzili from tblSodooresanad Where ID=@EmpId) set @SumBed3=(Select Sum(fldBedehkar) as SumBed from tblSodooreSanad Where fldCodeTafzili=@CodeTafzili3 And fldBedehkar>0 and fldSanad=@ShomareSanad3) set @BeS3=(Select top(1)fldBestankar from tblSodooreSanad Where ID=@EmpId) set @ShomareEnteghal=(Select top(1)fldShomareOdat from tblCheckeDaryafti Where fldShomareSanadOdat=@ShomareSanad3) execute CodeMoinHesab 'check',@codemoin3 output --insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) -- values ( 999 ,'621' ,132,'345',990205,111,'dfg') --set @cnt2=(Select count(fldCodeTafzili) from tblTafzili Where fldCodeTafzili=@CodeTafzili2 and ID<@EmpId ) --if @cnt2=0 -- begin if @BeS3>0 begin set @ShomarePardakht3=(Select top(1)fldShomarePardakht from tblCheckeDaryafti Where fldShomareSanadEnteghal=@ShomareSanad3) update #tmpSooratHesab2 set shomare =@ShomareEnteghal, fldSharh='' + case when fldSharh<>'' and fldSharh is not null then ' ' +fldSharh else '' end,fldType='OdateCheck' Where fldBedehkar>0 and ID=@EmpId --insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) -- select Convert(varchar,@EmpId)+'103' ,'bbb' ,@ShomareSanad2,'چک شماره ' + Convert(varchar,fldShomareCheck) + ' تاریخ ' + Convert(varchar,fldDateSarResid) ,@bigDate2,fldMablagh,'BargashteCheckeVagozarShode' -- from tblCheckeDaryafti Where fldShomareSanadEnteghal = @ShomareSanad2 and fldShomarePardakht=21 update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad3 and fldMoinKamel = @codemoin3 and fldCodeTafzili=@CodeTafzili3 end else begin update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad3 and fldMoinKamel = @codemoin3 and fldCodeTafzili=@CodeTafzili3 update #tmpSooratHesab2 set shomare =@ShomareEnteghal Where id=@EmpId end -- end --else -- update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad2 and fldMoinKamel = '31301' and fldCodeTafzili=@CodeTafzili2 --set @ShomarePardakht2=(Select top(1)fldShomarePardakht from tblCheckeDaryafti Where fldShomareCheck) --set @ShomarePardakht2=(select fldCodeTafzili from tblPardakht where fldShomarePardakht=@ShomarePardakht2) --select * from tblCheckeDaryafti Where fldShomareSanadEnteghal = 132 end if @Type='bazpascheckekhodeman' --بازپسگیری چک خود begin declare @ShomarePardakht4 bigint,@CodeTafzili4 nvarchar(10),@SumBed4 bigint,@Bed4 bigint,@ShomareSanad4 bigint,@CodeMoin4 nvarchar(50) set @bigDate2=(Select Convert(bigint,flddate) from tblSodooresanad Where ID=@EmpId) set @ShomareSanad4=(Select fldSanad from tblSodooresanad Where ID=@EmpId) set @CodeTafzili4=(Select fldCodeTafzili from tblSodooresanad Where ID=@EmpId) set @SumBed4=(Select Sum(fldBedehkar) as SumBed from tblSodooreSanad Where fldCodeTafzili=@CodeTafzili4 And fldBedehkar>0 and fldSanad=@ShomareSanad4) set @Bed4=(Select top(1)fldBedehkar from tblSodooreSanad Where ID=@EmpId) set @ShomareEnteghal=(Select top(1)fldShomareOdat from tblCheckeDaryafti Where fldShomareSanadOdat=@ShomareSanad4) execute CodeMoinHesab 'checkpardakhti',@codemoin4 output if @Bed4>0 begin set @ShomarePardakht4=(Select top(1)fldShomarePardakht from tblCheckeDaryafti Where fldShomareSanadEnteghal=@ShomareSanad4) update #tmpSooratHesab2 set shomare =@ShomareEnteghal, fldSharh='' + case when fldSharh<>'' and fldSharh is not null then ' ' +fldSharh else '' end,fldType='OdateCheck' Where fldBedehkar>0 and ID=@EmpId update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad3 and fldMoinKamel = @codemoin4 and fldCodeTafzili=@CodeTafzili4 end else begin update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad4 and fldMoinKamel = @codemoin4 and fldCodeTafzili=@CodeTafzili4 update #tmpSooratHesab2 set shomare =@ShomareEnteghal Where id=@EmpId end end if @type='' --سند دستی begin --پاک کردن اسناد دستی که ربطی به حسابهای دریافتنی و پرداختنی ندارن declare @CodeKol4 nvarchar(20),@ShomareSanad5 nvarchar(20) set @CodeKol4=(Select top(1) fldCodeKol from tblSodooreSanad Where ID=@EmpId) set @ShomareSanad5=(Select top(1) fldSanad from tblSodooreSanad Where ID=@EmpId) if @CodeKol4<>@kolhesabhayedaryaftani if @CodeKol4<>@kolhesabhayepardakhtani update #tmpSooratHesab2 set fldVaziat='delete' Where fldSanad= @ShomareSanad5 and fldCodeKol = @CodeKol4 end --if @type='ekhtetamie' -- begin -- update #tmpSooratHesab2 -- set fldVaziat='delete' where fldSanad=@ShomareSanad And fldMoinKamel= @strMoinKamelCheckDaryafti -- end --if @type='eftetahie' -- begin -- update #tmpSooratHesab2 -- set fldVaziat='delete' where fldSanad=@ShomareSanad And fldMoinKamel= @strMoinKamelCheckDaryafti -- end Fetch Next from EmpCursor into @EmpId end Close EmpCurSor Deallocate EmpCursor --set @select ='Select ID, fldSanad, fldDay, fldMonth, fldYear, fldCodeKol, fldCodeMoin, fldCodeTafzili, fldCodeMarkaz1, fldCodeMarkaz2, fldBedehkar, fldBestankar, fldRadif, -- fldCodeSharh, fldDate, fldSharh, fldMoinKamel, fldCodeNoeTafzili, fldGhateiMovaghat, fldGhateiDaem, fldSanadGhadim, fldShomarePeigiri, fldMoghayerat, fldType, -- fldVasete from #tmpSooratHesab2' --execute (@Select) --Select ID, fldSanad, fldCodeKol, fldCodeMoin, fldCodeTafzili, fldCodeMarkaz1, fldCodeMarkaz2, fldBedehkar, fldBestankar, fldRadif, -- fldCodeSharh, fldDate, fldSharh, fldMoinKamel, fldCodeNoeTafzili, fldGhateiMovaghat, fldGhateiDaem, fldSanadGhadim, fldShomarePeigiri, fldMoghayerat, fldType, -- fldVasete from #tmpSooratHesab2 execute codemoinhesab 'hesabhayedaryaftani' ,@CodekolMoinDaryaftTanzimat output INSERT INTO #tmpSooratHesab2 (ID, fldSanad, fldCodeKol, fldCodeMoin, fldCodeTafzili, fldCodeMarkaz1, fldCodeMarkaz2, fldBedehkar, fldBestankar, fldRadif, fldCodeSharh, fldDate, fldSharh, fldMoinKamel, fldCodeNoeTafzili, fldGhateiMovaghat, fldGhateiDaem, fldSanadGhadim, fldShomarePeigiri, fldMoghayerat, fldType, fldVasete) Select ID, fldSanad,fldCodeKol, fldCodeMoin, fldCodeTafzili, fldCodeMarkaz1, fldCodeMarkaz2, fldBedehkar, fldBestankar, fldRadif, fldCodeSharh, fldDate, fldSharh, fldMoinKamel, fldCodeNoeTafzili, fldGhateiMovaghat, fldGhateiDaem, fldSanadGhadim, fldShomarePeigiri, fldMoghayerat, fldType, fldVasete from tblSodooreSanad Where fldCodeTafzili=@CodeTafzili and fldMoinKamel=@CodekolMoinDaryaftTanzimat and fldType='ebteda' --رکوردهایی که در پایان باید حذف بشن delete #tmpSooratHesab2 where fldVaziat='delete' --#### حذف محدوده ها ###### set @SumBedGhabl=(Select Sum(fldBedehkar) as SumBed from #tmpSooratHesab2 Where fldBestankar is not null and fldBedehkar is not null and Convert(dec,fldDate)<@bigDateMin) if @SumBedGhabl is null set @SumBedGhabl =0 set @SumBesGhabl=(Select Sum(fldBestankar) as SumBes from #tmpSooratHesab2 Where fldBestankar is not null and fldBedehkar is not null and Convert(dec,fldDate)<@bigDateMin) if @SumBesGhabl is null set @SumBesGhabl=0 delete #tmpSooratHesab2 Where Convert(dec,fldDate)<@bigDateMin delete #tmpSooratHesab2 Where Convert(dec,fldDate)>@bigDateMax update #tmpSooratHesab2 set fldNameVahed2='' Where fldNameVahed2=N'انتخاب کنید' if @flagMande =1 --با نقل مانده --if (@SumBedGhabl-@SumBesGhabl)<>0 -- insert into #tmpSooratHesab2 -- (id,shomare,fldSanad,fldSharh,fldDate,fldBedehkar,fldBestankar,fldMande,fldtype) -- values -- ('0','','0','مانده قبل','0',@SumBedGhabl,@SumBesGhabl,@SumBedGhabl-@SumBesGhabl,'ghabl') begin declare @CodeKol2 nvarchar(100),@MandeTalab2 bigint,@MandeBedehi2 bigint execute CodeKolHesab 'kolhesabhayedaryaftani',@CodeKol2 output set @MandeTalab2=(SELECT ISNULL( SUM(ISNULL(fldBedehkar, 0)-ISNULL(fldBestankar, 0)),0) AS SumM FROM tblSodooreSanad WHERE (fldCodeKol=@CodeKol2) and (fldCodeTafzili = @CodeTafzili ) and Convert(bigint,fldDate)<@bigDateMin) execute CodeKolHesab 'kolhesabhayepardakhtani',@CodeKol2 output set @MandeBedehi2=(SELECT ISNULL( SUM(ISNULL(fldBestankar, 0)-ISNULL(fldBedehkar, 0)),0) AS SumM FROM tblSodooreSanad WHERE (fldCodeKol=@CodeKol2) and (fldCodeTafzili = @CodeTafzili ) and Convert(bigint,fldDate)<@bigDateMin) set @decMandeGhabl=@MandeTalab2-@MandeBedehi2 if @decMandeGhabl<>0 insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldSharh,fldDate,fldBedehkar,fldBestankar,fldMande,fldtype) values ('0','','0','مانده قبل',@bigDateMin,@MandeTalab2,@MandeBedehi2,@decMandeGhabl,'ghabl') end --select * from #tmpSooratHesab2 order by Convert(dec,fldSanad),Convert(varchar,id) declare @strOrder nvarchar(30) --if @intOrder=0 --مرتب سازی براساس تاریخ -- select * from #tmpSooratHesab2 order by Convert(dec,fldDate),Convert(varchar,id) --else --مرتب سازی براساس سند -- select * from #tmpSooratHesab2 order by Convert(dec,fldSanad),Convert(varchar,id) --update #tmpSooratHesab2 set flddate=substring(convert(nvarchar(10),fldDate),0,5) +'/' +substring(convert(nvarchar(10),fldDate),5,2) + '/' + substring(convert(nvarchar(10),fldDate),7,2) delete from #tmpSooratHesab2 where fldSanad in (Select fldSanadPas from tblCheckePardakhti Where fldCodeSahebeCheck=@CodeTafzili ) declare @MaxId bigint set @Maxid =(Select Max(Convert(dec,Id)) as MaxId from #tmpSooratHesab2) if isnumeric(@maxId)=0 set @maxId=1 else set @maxId=@maxId+1 --set @MaxId=1000 insert into #tmpSooratHesab2 (id,shomare,fldSanad,fldRiz,fldDate,fldMablaghKhales,fldType) Select Convert(varchar,@MaxId),'برگشت چک' ,'0','برگشت چک به شماره ' + Convert(varchar,fldShomareCheck),Convert(varchar,fldDateBargasht),fldMablagh,'bargasht' FROM tblCheckeDaryafti Where fldVaziat='Bargasht' and fldCodeSahebeCheck=@CodeTafzili --values ('2000' ,'ش برگشت' ,'','' ,0,0,'bargasht') --values (Convert(varchar,@EmpId) + '200' ,'ش برگشت' + Convert(varchar,@ShomarePardakht) ,@ShomareSanad,@strSharheHesab + case when @strShareBank='' then '' else '-' + @strShareBank end ,@bigDate,@lngBank,'pardakht') update #tmpSooratHesab2 set fldnamevasete = tblTafzili.fldSharheTafzili from tblTafzili where #tmpSooratHesab2.fldVasete=tblTafzili.fldCodeTafzili if @intOrder=0 --مرتب سازی براساس تاریخ select radif,shomare,fldSanad,fldSharh,fldRiz,substring(convert(nvarchar(10),fldDate),0,5) +'/' +substring(convert(nvarchar(10),fldDate),5,2) + '/' + substring(convert(nvarchar(10),fldDate),7,2) as fldDate,fldTedad, fldFee,fldMablaghKhales,fldBedehkar,fldBestankar,fldMande,fldnamevasete,fldNameAnbar,fldType,fldNameKarbar, fldNameVahed,fldNameVahed2,fldFeeYekCarton,fldDarsadTakhfif,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif, fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablagh,fldfeekol,fldVasete, fldVaziat,fldCodeAnbar,fldCodeKarbar,id from #tmpSooratHesab2 order by Convert(dec,fldDate),Convert(varchar,id) --select shomare,fldSanad,fldSharh,fldRiz,fldDate,fldTedad,fldNameVahed, -- fldFee,fldMablaghKhales,fldBedehkar,fldBestankar,fldMande -- from #tmpSooratHesab2 order by Convert(dec,fldDate),Convert(varchar,id) else if @intOrder=1 --مرتب سازی براساس سند select radif,shomare,fldSanad,fldSharh,fldRiz,substring(convert(nvarchar(10),fldDate),0,5) +'/' +substring(convert(nvarchar(10),fldDate),5,2) + '/' + substring(convert(nvarchar(10),fldDate),7,2) as fldDate,fldTedad, fldFee,fldMablaghKhales,fldBedehkar,fldBestankar,fldMande,fldnamevasete,fldNameAnbar,fldType,fldNameKarbar, fldNameVahed,fldNameVahed2,fldFeeYekCarton,fldDarsadTakhfif,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif, fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablagh,fldfeekol,fldVasete, fldVaziat,fldCodeAnbar,fldCodeKarbar,id from #tmpSooratHesab2 order by Convert(dec,fldSanad),Convert(varchar,id) end end