﻿-- SP: [dbo].[Kardeks2]
-- ===============================================
IF OBJECT_ID(N'dbo.Kardeks2', N'P') IS NULL
BEGIN
    EXEC('CREATE PROCEDURE [dbo].[Kardeks2] AS BEGIN SET NOCOUNT ON; END')
END
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Alter PROCEDURE [dbo].[Kardeks2]
	-- Add the parameters for the stored procedure here
	@Codekala bigint,
	@ListCodeAnbar varchar(5000)=null,
	@DateFrom varchar(8),
	@DateTo varchar(8)
	--@CodeTa bigint,
	--@GroupAz bigint,
	--@GroupTa bigInt,
	--@Type nvarchar(10)
AS
IF 1 = 0 BEGIN
    SET FMTONLY OFF
END
BEGIN
	
	Create table #TempKardeks(
		[ID] bigint,
		[fldCodeTafsili] bigint,
		[CodeKala] bigint null,
		[NameKala] nvarchar(100) collate Persian_100_CI_AS,
		[CodeGroup] bigint,
		[NameGroup] nvarchar(80) collate Persian_100_CI_AS,
		[fldShomareFactor] bigint,
		[fldShomareEnteghal] bigint,
		[fldShomareSanad] bigint,
		[fldDate] bigint,
		[fldTime] nvarchar(10),
		[fldDateUpdate] nvarchar(19),
		[fldDateShow] nvarchar(25),
		[fldSharh] nvarchar(200) collate Persian_100_CI_AS,
		[fldCodeAnbar] bigint,
		[fldNameAnbar] nvarchar(50) collate Persian_100_CI_AS,
		[fldFeeJoz] bigint,
		[fldFeeYekKarton] bigint,
		[fldMablaghKol] bigint,
		[fldTedadeJozVorood] decimal(18,2),
		[fldTedadeJozVoroodT] nvarchar(30),
		[fldTedadeJozKhorooj] decimal(18,2),
		[fldTedadeJozKhoroojT] nvarchar(30),
		[fldTedadDarKartonShow] nvarchar(100) collate Persian_100_CI_AS,
		[SumMandeJoz] decimal(18,2),
		[SumMandeJozT] nvarchar(100),
		[fldTedadDarKarton] bigint,
		[fldJozMande] bigint,
		[fldTedadVoroodKartonShow] nvarchar(100) collate Persian_100_CI_AS,
		[fldTedadKhoroojKartonShow] nvarchar(100) collate Persian_100_CI_AS,
		[fldKolMande] nvarchar(100) collate Persian_100_CI_AS,
		[fldtype] nvarchar(20) collate Persian_100_CI_AS,
		[fldtypeShow] nvarchar(40) collate Persian_100_CI_AS,
		[fldNameVahed] NVarchar(100) collate Persian_100_CI_AS,
		[fldNameVahed2] Nvarchar(100) collate Persian_100_CI_AS,
		[ConditionSum] bigint,
		[IdOrder] bigint,
		[fldRadif] bigint
	)

	--Create table #TempKardeksShow(
	--	[CodeKala] bigint null,
	--	[NameKala] nvarchar(100) collate Persian_100_CI_AS,
	--	[CodeGroup] bigint,
	--	[NameGroup] nvarchar(80) collate Persian_100_CI_AS,
	--	[fldShomareFactor] bigint,
	--	[fldShomareSanad] bigint,
	--	[fldDate] bigint,
	--	[fldSharh] nvarchar(200) collate Persian_100_CI_AS,
	--	[fldCodeAnbar] bigint,
	--	[fldNameAnbar] nvarchar(50) collate Persian_100_CI_AS,
	--	[fldFeeJoz] bigint,
	--	[fldFeeYekKarton] bigint,
	--	[fldMablaghKol] bigint,
	--	[fldTedadeJozVorood] bigint,
	--	[fldTedadeJozKhorooj] bigint,
	--	[fldTedadDarKarton] bigint,
	--	[fldJozMande] bigint,
	--	[fldTedadVoroodKartonShow] nvarchar(100) collate Persian_100_CI_AS,
	--	[fldKolKhorooj] nvarchar(100) collate Persian_100_CI_AS,
	--	[fldKolMande] nvarchar(100) collate Persian_100_CI_AS,
	--	[fldtype] nvarchar(20) collate Persian_100_CI_AS,
	--	[fldNameVahed] NVarchar(100) collate Persian_100_CI_AS,
	--	[fldNameVahed2] Nvarchar(100) collate Persian_100_CI_AS
	--)

	declare @SumMandeGhablVorood bigint,@SumGhablKhorooj bigint,@SumTedad bigint





	insert into #TempKardeks
 	 (id,fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
Select id,fldCodeTafsili,fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,fldTedadJoz,0,-1,'','','',fldtype,'namevahed','namevahed2' from tblRizeKharid
	where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo

	insert into #TempKardeks
 	 (fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
		Select 0,fldCodeKala,'NameKala',-1,'namegroup',fldShomareTolid,0,fldShomareSanad,fldDate,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,fldTedadJozNahaei,0,-1,'','','',fldtype,'namevahed','namevahed2' from tblRizeTolid
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo and fldType='mahsool'




	--insert into #TempKardeks
	--	(CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareSanad,fldDate,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
	--	Select fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareSanad,fldDate,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,fldTedadJoz,0,-1,'kolvorood','kolkhorooj','kolmande','kharid','namevahed','namevahed2' from tblRizeKharid


	insert into #TempKardeks
		(fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareSanad,fldDate,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
		Select fldCodeTafsili, fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareSanad,fldDate,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,fldTedadJoz,0,-1,'','','','bargashtazforoosh','namevahed','namevahed2' from tblRizeBargashtAzForoosh
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo


	insert into #TempKardeks
		(id,fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2,fldRadif)
		Select id,fldCodeTafsili,fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,0,fldTedadJoz,-1,'','','',fldtype,'namevahed','namevahed2',fldRadif from tblRizeForoosh
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo
		
	insert into #TempKardeks
		(fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldTime,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
		Select 0,fldCodeKala,'NameKala',-1,'namegroup',fldShomareTolid,0,fldShomareSanad,fldDate,fldTime,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,0,fldTedadJozAvalie,-1,'','','',fldtype,'namevahed','namevahed2' from tblRizeTolid
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo and fldType='avalie'


	insert into #TempKardeks
		(fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareSanad,fldDate,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
		Select fldCodeTafsili,fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareSanad,fldDate,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,0,fldTedadJoz,-1,'','','','bargashtazkharid','namevahed','namevahed2' from tblRizeBargashtAzKharid
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo
	--Select * from #TempKardeks



--	--مانده قبل

--	set @SumTedad=(Select Sum(fldTedadJoz) as SumMande from tblRizeKharid where fldCodeKala=@Codekala and fldDate < @DateFrom and fldCodeAnbar  )	
--	if isnumeric(@SumTedad)=1
--		set @SumMandeGhablVorood=@SumTedad

--	set @SumTedad=(Select Sum(fldTedadJoz) as SumMande from tblRizeBargashtAzForoosh where fldCodeKala=@Codekala and fldDate < @DateFrom  )	
--		if isnumeric(@SumTedad)=1
--			set @SumMandeGhablVorood=@SumTedad

--	set @SumTedad=(Select Sum(fldTedadJoz) as SumMande from tblRizeForoosh where fldCodeKala=@Codekala and fldDate < @DateFrom  )	
--		if isnumeric(@SumTedad)=1
--			set @SumGhablKhorooj=@SumTedad

--	set @SumTedad=(Select Sum(fldTedadJoz) as SumMande from tblRizeBargashtAzKharid where fldCodeKala=@Codekala and fldDate < @DateFrom  )	
--		if isnumeric(@SumTedad)=1
--			set @SumGhablKhorooj=@SumTedad
--insert into #TempKardeks
--		(fldCodeTafsili,fldShomareFactor,fldShomareSanad,fldSharh,fldCodeAnbar,fldNameAnbar,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
--		(0,0,0,'نقل مانده',
----####################
	

	if @ListCodeAnbar <>''
		begin
		declare @StrDeleteAnbar varchar(5000)
		set  @StrDeleteAnbar='delete from #TempKardeks 
		    where fldCodeAnbar NOT IN ('+ @ListCodeAnbar +')'
			execute(@StrDeleteAnbar)
		end

	UPDATE       #TempKardeks
		SET                CodeGroup = tblKala.fldCodeGroup, NameKala = tblKala.fldNameKala,fldTedadDarKarton=tblKala.fldTedadJoz
		FROM            tblKala INNER JOIN
								 #TempKardeks ON tblKala.fldCodeKala = #TempKardeks.CodeKala


	UPDATE       #TempKardeks
		SET                #TempKardeks.NameGroup = tblGroupKala.fldGroupName
		FROM            tblGroupKala INNER JOIN
								 #TempKardeks ON #TempKardeks.CodeGroup = tblGroupKala.fldGroupId


UPDATE       #TempKardeks
	SET                fldNameAnbar = tblAnbar.fldNameAnbar
	FROM            tblAnbar INNER JOIN
							 #TempKardeks ON tblAnbar.fldCode = #TempKardeks.fldCodeAnbar




update #TempKardeks set #TempKardeks.fldNameVahed=tbl2.fldNameVahed,#TempKardeks.fldNameVahed2=tbl2.fldNameVahed2
from
(SELECT    fldCodeKala,    tbl1.fldCodeVahed1, tbl1.fldCodeVahed2, tbl1.fldNameVahed, tblVahed.fldNameVahed AS fldNameVahed2
FROM            tblVahed INNER JOIN
                             (SELECT       fldCodeKala, tblKala.fldCodeVahed1, tblKala.fldCodeVahed2, tblVahed_1.fldNameVahed
                                FROM            tblKala INNER JOIN
                                                         tblVahed AS tblVahed_1 ON tblKala.fldCodeVahed1 = tblVahed_1.ID) AS tbl1 ON tblVahed.ID = tbl1.fldCodeVahed2) tbl2
														 where tbl2.fldCodeKala=#TempKardeks.CodeKala

update #TempKardeks set fldNameVahed2='' where fldTedadDarKarton=0

update #TempKardeks set fldSharh='' Where fldSharh='Sharh'

update kdx set fldDateUpdate=CONVERT(varchar(19), rk.fldDateUpdate, 120)
from #TempKardeks kdx inner join tblRizeKharid rk on kdx.ID=rk.ID
where kdx.fldtype=rk.fldtype and kdx.fldTedadeJozVorood>0

update kdx set fldDateUpdate=CONVERT(varchar(19), rf.fldDateUpdate, 120)
from #TempKardeks kdx inner join tblRizeForoosh rf on kdx.ID=rf.ID
where kdx.fldtype=rf.fldtype and kdx.fldTedadeJozKhorooj>0

update kdx set fldDateUpdate=CONVERT(varchar(19), rb.fldDateUpdate, 120)
from #TempKardeks kdx inner join tblRizeBargashtAzForoosh rb on kdx.CodeKala=rb.fldCodeKala and kdx.fldShomareFactor=rb.fldShomareFactor and kdx.fldShomareSanad=rb.fldShomareSanad and kdx.fldDate=rb.fldDate
where kdx.fldtype='bargashtazforoosh'

update kdx set fldDateUpdate=CONVERT(varchar(19), rbk.fldDateUpdate, 120)
from #TempKardeks kdx inner join tblRizeBargashtAzKharid rbk on kdx.CodeKala=rbk.fldCodeKala and kdx.fldShomareFactor=rbk.fldShomareFactor and kdx.fldShomareSanad=rbk.fldShomareSanad and kdx.fldDate=rbk.fldDate
where kdx.fldtype='bargashtazkharid'

update kdx set fldDateUpdate=CONVERT(varchar(19), rt.fldDateUpdate, 120)
from #TempKardeks kdx inner join tblRizeTolid rt on kdx.CodeKala=rt.fldCodeKala and kdx.fldShomareFactor=rt.fldShomareTolid and kdx.fldShomareSanad=rt.fldShomareSanad and kdx.fldDate=rt.fldDate and kdx.fldtype=rt.fldType
where kdx.fldtype in ('avalie','mahsool')

--برای جمع ستونها مجبور شدم این کار رو بکنم
update #TempKardeks set ConditionSum=1

----##########
--update #TempKardeks set IdOrder=1 where fldType='kharid'
--update #TempKardeks set IdOrder=2 where fldType='bargashtazforoosh'

----##########
    --declare @EmpCodeTafzili nvarchar(10),@SumJam bigint    
    --declare EmpCursor Cursor For
    --        Select fldCodetafzili from #TempKardeks  With (NoLock) 
    --    		open EmpCursor
    --    		fetch Next From EmpCursor into @EmpCodeTafzili
    --    		While @@Fetch_Status = 0
    --    			Begin	
    --    				set @SumJam=Select SUM(fldTedadeJozVorood-fldTedadeJozKhorooj) from  #TempKardeks   where fldCodetafzili =@EmpCodeTafzili
    --    				update #TempKardeks  set  SumMandeJoz=(Select top(1)  fldShomare from tblDaftarcheTell Where fldCodetafzili=@EmpCodeTafzili)
    --    						where fldCodetafzili =@EmpCodeTafzili
    --    				Fetch Next from EmpCursor
    --    			into @EmpCodeTafzili
    --    		end
    --    Close EmpCurSor
    --Deallocate EmpCursor
--update  #TempKardeks set SumMandeJoz = tbl3.RunningTotal from
--	(Select fldShomareSanad,SUM(fldTedadeJozVorood-fldTedadeJozKhorooj) OVER (PARTITION BY ConditionSum   ) AS RunningTotal from #TempKardeks) as tbl3
--	where #TempKardeks.fldShomareSanad=tbl3.fldShomareSanad

--update  #TempKardeks set SumMandeJoz = tblS.b from
--SELECT     fldShomareSanad, fldTedadeJozVorood-fldTedadeJozKhorooj,
--              (SELECT     SUM(fldTedadeJozVorood-fldTedadeJozKhorooj) AS Expr1
--                 FROM         #TempKardeks AS b
--                 WHERE     (fldShomareSanad <= a.fldShomareSanad)) AS b
--FROM         #TempKardeks AS a


--update set      fld1, fld2,
--          (SELECT     SUM(fld2) AS Expr1
--             FROM         tblTest7 AS b
--             WHERE     (fld1 <= a.fld1)) AS b
--FROM         tblTest7 AS a


--UPDATE    #TempKardeks
--SET              SumMandeJoz = tblss.b
--FROM         (SELECT      fldShomareSanad, fldTedadeJozVorood-fldTedadeJozKhorooj,
--                                                  (SELECT     SUM(fldTedadeJozVorood-fldTedadeJozKhorooj) AS Expr1
--                                                     FROM         #TempKardeks AS b
--                                                     WHERE     (CONVERT(dec, fldShomareSanad) <= CONVERT(dec, a.fldShomareSanad))) AS b
--                        FROM         #TempKardeks AS a) AS tblss INNER JOIN
--                      #TempKardeks ON tblss.fldShomareSanad = #TempKardeks.fldShomareSanad
--where #TempKardeks.fldShomareSanad=tbls.fldShomareSanad 

;WITH KardeksForOrder AS (
    SELECT  *,
            CASE
                WHEN fldDateUpdate IS NULL OR LTRIM(RTRIM(fldDateUpdate)) = '' THEN '9999-12-31 23:59:59'
                ELSE LTRIM(RTRIM(fldDateUpdate))
            END AS OrderDateUpdate
    FROM #TempKardeks
),
OrderedKardeks AS (
    SELECT  IdOrder,
            ROW_NUMBER() OVER(
                ORDER BY
                    fldDate,
                    CASE WHEN fldtype='mojodi_ebteda_doreh' THEN 0 ELSE 1 END,
                    OrderDateUpdate,
                    CASE WHEN fldtype='enteghal' THEN ISNULL(fldShomareEnteghal,0) ELSE ISNULL(fldShomareFactor,0) END,
                    CASE WHEN fldTedadeJozVorood>0 THEN 1 ELSE 2 END,
                    ISNULL(fldShomareSanad,0),
                    ISNULL(fldRadif,0),
                    ISNULL(ID,0)
            ) AS NewIdOrder
    FROM KardeksForOrder
)
UPDATE OrderedKardeks
SET IdOrder = NewIdOrder


---جمع ورود و خروج
UPDATE    #TempKardeks
SET              SumMandeJoz = tblss.b
FROM         (SELECT     44 AS Expr1, IdOrder, fldTedadeJozVorood - fldTedadeJozKhorooj AS Expr2,
                                                  (SELECT     SUM(fldTedadeJozVorood - fldTedadeJozKhorooj) AS Expr1
                                                     FROM         #TempKardeks AS b
                                                     WHERE     (b.IdOrder <= a.IdOrder)) AS b
                        FROM         #TempKardeks AS a) AS tblss INNER JOIN
                      #TempKardeks ON tblss.IdOrder = #TempKardeks.IdOrder
        

update #TempKardeks set SumMandeJozT=SumMandeJoz,fldTedadeJozVoroodT=fldTedadeJozVorood,fldTedadeJozKhoroojT=fldTedadeJozKhorooj
update #TempKardeks set SumMandeJozT=LEFT(Convert(varchar,SumMandeJoz),len(Convert(varchar,SumMandeJoz))-3) where right(convert(varchar,SumMandeJoz),2)='00'
update #TempKardeks set fldTedadeJozVoroodT=LEFT(Convert(varchar,fldTedadeJozVorood),len(Convert(varchar,fldTedadeJozVorood))-3) where right(convert(varchar,fldTedadeJozVorood),2)='00'
update #TempKardeks set fldTedadeJozKhoroojT=LEFT(Convert(varchar,fldTedadeJozKhorooj),len(Convert(varchar,fldTedadeJozKhorooj))-3) where right(convert(varchar,fldTedadeJozKhorooj),2)='00'
              

update #TempKardeks Set fldTedadVoroodKartonShow=Convert(varchar,floor(fldTedadeJozVorood / fldTedadDarKarton)) + ' ' + fldNameVahed2 +  Convert(varchar, { fn MOD(Convert(dec,fldTedadeJozVorood), fldTedadDarKarton) } ) + ' ' + fldNameVahed
	where fldTedadDarKarton>0

update #TempKardeks Set fldTedadKhoroojKartonShow=Convert(varchar,floor(fldTedadeJozKhorooj / fldTedadDarKarton)) + ' ' + fldNameVahed2 +  Convert(varchar, { fn MOD(Convert(dec,fldTedadeJozKhorooj), fldTedadDarKarton) } ) + ' ' + fldNameVahed
	where fldTedadDarKarton>0

update #TempKardeks Set fldKolMande=Convert(varchar,floor(SumMandeJoz / fldTedadDarKarton)) + ' ' + fldNameVahed2 +  Convert(varchar, { fn MOD(Convert(dec,SumMandeJoz), fldTedadDarKarton) } ) + ' ' + fldNameVahed
	where fldTedadDarKarton>0

update #TempKardeks Set fldTedadVoroodKartonShow='' where fldTedadeJozVorood=0
update #TempKardeks Set fldTedadKhoroojKartonShow='' where fldTedadeJozKhorooj=0



--update #TempKardeks Set fldTedadKhoroojKartonShow=Convert(varchar,floor(fldTedadeJozKhorooj / fldTedadDarKarton)) + ' ' + fldNameVahed2 +  Convert(varchar, { fn MOD(fldTedadeJozKhorooj, fldTedadDarKarton) } ) + ' ' + fldNameVahed
--	where fldTedadDarKarton>0

--جمع ستونها
--SELECT
--  AccountNumber,
--  flddate,
--  bill,
--  SUM(bill) OVER (PARTITION BY AccountNumber ORDER BY flddate) AS RunningTotal
--FROM
--  dbo.Table1 


----فیلتر تا قبل از تاریخ 
--Insert into #TempKardeksShow
--	(fldTedadeJozVorood,fldTedadeJozKhorooj,fldTedadDarKarton, NameGroup, CodeGroup, CodeKala, NameKala, fldNameVahed, fldNameVahed2)
--SELECT        SUM(fldTedadeVorood) AS SumTedadeVorood, SUM(fldTedadeKhorooj) AS SumTedadeKhorooj, 
--                         fldTedadDarKarton, fldGroupName, fldCodeGroup, fldCodeKala, fldNameKala, fldNogheSefaresh, fldNameVahed, fldNameVahed2,0
--FROM            #TempKardeks
--GROUP BY CodeKala, NameGroup, CodeGroup, NameKala, fldNameVahed, fldNameVahed2, fldTedadDarKarton, fldTedadeKol




	--Select CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareSanad,fldDate,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2
	--	from #TempKardeks
	--update 

	--Select fldTedadeJozVorood,fldTedadeJozKhorooj,fldDate,fldShomareSanad,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldTedadDarKartonShow,SUM(fldTedadeJozVorood-fldTedadeJozKhorooj) OVER (PARTITION BY ConditionSum ORDER BY fldDate,fldShomareSanad) AS RunningTotal from #TempKardeks
	update #TempKardeks set fldTypeShow='خرید' where fldType='kharid'
	update #TempKardeks set fldTypeShow='ابتدای دوره' where fldType='mojodi_ebteda_doreh'	
	update #TempKardeks set fldTypeShow='انتقال بین انبار' where fldType='enteghal'
	update #TempKardeks set fldTypeShow='فروش' where fldType='foroosh'
	update #TempKardeks set fldTypeShow='برگشت از فروش' where fldType='bargashtazforoosh'
	update #TempKardeks set fldTypeShow='برگشت از خرید' where fldType='bargashtazkharid'
	update #TempKardeks set fldTypeShow='تبدیل به محصول نهایی' where fldType='avalie'
	update #TempKardeks set fldTypeShow='محصول تولید شده در فرایند تولید' where fldType='mahsool'
	update #TempKardeks set fldTypeShow='مغایرت',fldCodeTafsili=0,fldSharh='مغایرت' where fldType='Moghayrat'

	
	update #TempKardeks set fldShomareFactor=fldShomareEnteghal where fldType='enteghal'

	update #TempKardeks set fldSharh= tblTafzili.fldSharheTafzili + ' ' + fldSharh from tblTafzili 
		Where #TempKardeks.fldCodeTafsili=tblTafzili.fldCodeTafzili

	
	update #TempKardeks set flddateshow =substring(convert(nvarchar(10),fldDate),0,5) +'/' +substring(convert(nvarchar(10),fldDate),5,2) + '/' + substring(convert(nvarchar(10),fldDate),7,2)
	Select  ROW_NUMBER() OVER(ORDER BY IdOrder ) AS Row,id,fldSharh,fldtypeShow,fldShomareFactor,flddateshow,fldTedadeJozVoroodT As fldTedadeJozVorood,fldTedadeJozKhoroojT as fldTedadeJozKhorooj,SumMandeJozT as SumMandeJoz,fldNameVahed,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldTedadDarKarton,fldNameVahed2,fldKolMande,fldShomareSanad,fldNameAnbar,fldDate,fldtype,fldRadif from #TempKardeks order by IdOrder
	
--FROM
--  dbo.Table1 

END
GO

-- ===============================================
-- SP: [dbo].[Kardeks3]
-- ===============================================
IF OBJECT_ID(N'dbo.Kardeks3', N'P') IS NULL
BEGIN
    EXEC('CREATE PROCEDURE [dbo].[Kardeks3] AS BEGIN SET NOCOUNT ON; END')
END
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Alter PROCEDURE [dbo].[Kardeks3]
	-- Add the parameters for the stored procedure here
	@Codekala bigint,
	@ListCodeAnbar varchar(5000)=null,
	@DateFrom varchar(8),
	@DateTo varchar(8)
	--@CodeTa bigint,
	--@GroupAz bigint,
	--@GroupTa bigInt,
	--@Type nvarchar(10)
AS
IF 1 = 0 BEGIN
    SET FMTONLY OFF
END
BEGIN
	
	Create table #TempKardeks(
		[ID] bigint,
		[fldCodeTafsili] bigint,
		[CodeKala] bigint null,
		[NameKala] nvarchar(100) collate Persian_100_CI_AS,
		[CodeGroup] bigint,
		[NameGroup] nvarchar(80) collate Persian_100_CI_AS,
		[fldShomareFactor] bigint,
		[fldShomareEnteghal] bigint,
		[fldShomareSanad] bigint,
		[fldDate] bigint,
		[fldTime] nvarchar(10),
		[fldDateUpdate] nvarchar(19),
		[fldDateShow] nvarchar(25),
		[fldSharh] nvarchar(200) collate Persian_100_CI_AS,
		[fldCodeAnbar] bigint,
		[fldNameAnbar] nvarchar(50) collate Persian_100_CI_AS,
		[fldFeeJoz] bigint,
		[fldFeeYekKarton] bigint,
		[fldMablaghKol] bigint,

		[fldMablaghTakhfif] decimal(18,2),
		[fldFeeVahed1BadAzTakhfif] decimal(18,2),
		[fldFeeVahed2BadAzTakhfif] decimal(18,2),
		[fldArzeshAfzode]  decimal(18,2),
		[fldFeeKolBadAzTakhfif]  decimal(18,2),
		[fldMablaghKhales]  decimal(18,2),

		[fldTedadeJozVorood] decimal(18,2),
		[fldTedadeJozVoroodT] nvarchar(30),
		[fldTedadeJozKhorooj] decimal(18,2),
		[fldTedadeJozKhoroojT] nvarchar(30),
		[fldTedadDarKartonShow] nvarchar(100) collate Persian_100_CI_AS,
		[SumMandeJoz] decimal(18,2),
		[SumMandeJozT] nvarchar(100),
		[fldTedadDarKarton] bigint,
		[fldJozMande] bigint,
		[fldTedadVoroodKartonShow] nvarchar(100) collate Persian_100_CI_AS,
		[fldTedadKhoroojKartonShow] nvarchar(100) collate Persian_100_CI_AS,
		[fldKolMande] nvarchar(100) collate Persian_100_CI_AS,
		[fldtype] nvarchar(20) collate Persian_100_CI_AS,
		[fldtypeShow] nvarchar(40) collate Persian_100_CI_AS,
		[fldNameVahed] NVarchar(100) collate Persian_100_CI_AS,
		[fldNameVahed2] Nvarchar(100) collate Persian_100_CI_AS,
		[ConditionSum] bigint,
		[IdOrder] bigint,
		[fldRadif] bigint
	)

	--Create table #TempKardeksShow(
	--	[CodeKala] bigint null,
	--	[NameKala] nvarchar(100) collate Persian_100_CI_AS,
	--	[CodeGroup] bigint,
	--	[NameGroup] nvarchar(80) collate Persian_100_CI_AS,
	--	[fldShomareFactor] bigint,
	--	[fldShomareSanad] bigint,
	--	[fldDate] bigint,
	--	[fldSharh] nvarchar(200) collate Persian_100_CI_AS,
	--	[fldCodeAnbar] bigint,
	--	[fldNameAnbar] nvarchar(50) collate Persian_100_CI_AS,
	--	[fldFeeJoz] bigint,
	--	[fldFeeYekKarton] bigint,
	--	[fldMablaghKol] bigint,
	--	[fldTedadeJozVorood] bigint,
	--	[fldTedadeJozKhorooj] bigint,
	--	[fldTedadDarKarton] bigint,
	--	[fldJozMande] bigint,
	--	[fldTedadVoroodKartonShow] nvarchar(100) collate Persian_100_CI_AS,
	--	[fldKolKhorooj] nvarchar(100) collate Persian_100_CI_AS,
	--	[fldKolMande] nvarchar(100) collate Persian_100_CI_AS,
	--	[fldtype] nvarchar(20) collate Persian_100_CI_AS,
	--	[fldNameVahed] NVarchar(100) collate Persian_100_CI_AS,
	--	[fldNameVahed2] Nvarchar(100) collate Persian_100_CI_AS
	--)

	declare @SumMandeGhablVorood bigint,@SumGhablKhorooj bigint,@SumTedad bigint





	insert into #TempKardeks
 	 (id,fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldTime,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
Select id,fldCodeTafsili,fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldTime,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,fldTedadJoz,0,-1,'','','',fldtype,'namevahed','namevahed2' from tblRizeKharid
	where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo

	insert into #TempKardeks
 	 (fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldTime,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
		Select 0,fldCodeKala,'NameKala',-1,'namegroup',fldShomareTolid,0,fldShomareSanad,fldDate,fldTime,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,0,fldFeeJoz,fldFeeYekCarton,0,fldMablaghKhales,fldMablaghKhales,fldTedadJozNahaei,0,-1,'','','',fldtype,'namevahed','namevahed2' from tblRizeTolid
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo and fldType='mahsool'




	--insert into #TempKardeks
	--	(CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareSanad,fldDate,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
	--	Select fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareSanad,fldDate,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,fldTedadJoz,0,-1,'kolvorood','kolkhorooj','kolmande','kharid','namevahed','namevahed2' from tblRizeKharid


	insert into #TempKardeks
		(fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareSanad,fldDate,fldTime,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
		Select fldCodeTafsili, fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareSanad,fldDate,fldTime,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,fldTedadJoz,0,-1,'','','','bargashtazforoosh','namevahed','namevahed2' from tblRizeBargashtAzForoosh
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo


	insert into #TempKardeks
		(id,fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldTime,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2,fldRadif)
		Select id,fldCodeTafsili,fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldTime,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,0,fldTedadJoz,-1,'','','',fldtype,'namevahed','namevahed2',fldRadif from tblRizeForoosh
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo

	insert into #TempKardeks
		(id,fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldTime,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2,fldRadif)
		Select id,fldCodeTafsili,fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldTime,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,0,fldTedadJoz,-1,'','','',fldtype,'namevahed','namevahed2',fldRadif from tblRizHavaleHazine
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo
		
	insert into #TempKardeks
		(fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareEnteghal,fldShomareSanad,fldDate,fldTime,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
		Select 0,fldCodeKala,'NameKala',-1,'namegroup',fldShomareTolid,0,fldShomareSanad,fldDate,fldTime,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,0,fldTedadJozAvalie,-1,'','','',fldtype,'namevahed','namevahed2' from tblRizeTolid
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo and fldType='avalie'


	insert into #TempKardeks
		(fldCodeTafsili,CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareSanad,fldDate,fldTime,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
		Select fldCodeTafsili,fldCodeKala,'NameKala',-1,'namegroup',fldShomareFactor,fldShomareSanad,fldDate,fldTime,'Sharh',fldCodeAnbar,'nameanbar',fldFeeJoz,fldFeeYekCarton,fldFeeKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldArzeshAfzode,fldFeeKolBadAzTakhfif,fldMablaghKhales,0,fldTedadJoz,-1,'','','',fldtype,'namevahed','namevahed2' from tblRizeBargashtAzKharid
		where fldCodeKala=@Codekala and fldDate BETWEEN  @DateFrom  AND  @DateTo
	--Select * from #TempKardeks



--	--مانده قبل

--	set @SumTedad=(Select Sum(fldTedadJoz) as SumMande from tblRizeKharid where fldCodeKala=@Codekala and fldDate < @DateFrom and fldCodeAnbar  )	
--	if isnumeric(@SumTedad)=1
--		set @SumMandeGhablVorood=@SumTedad

--	set @SumTedad=(Select Sum(fldTedadJoz) as SumMande from tblRizeBargashtAzForoosh where fldCodeKala=@Codekala and fldDate < @DateFrom  )	
--		if isnumeric(@SumTedad)=1
--			set @SumMandeGhablVorood=@SumTedad

--	set @SumTedad=(Select Sum(fldTedadJoz) as SumMande from tblRizeForoosh where fldCodeKala=@Codekala and fldDate < @DateFrom  )	
--		if isnumeric(@SumTedad)=1
--			set @SumGhablKhorooj=@SumTedad

--	set @SumTedad=(Select Sum(fldTedadJoz) as SumMande from tblRizeBargashtAzKharid where fldCodeKala=@Codekala and fldDate < @DateFrom  )	
--		if isnumeric(@SumTedad)=1
--			set @SumGhablKhorooj=@SumTedad
--insert into #TempKardeks
--		(fldCodeTafsili,fldShomareFactor,fldShomareSanad,fldSharh,fldCodeAnbar,fldNameAnbar,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2)
--		(0,0,0,'نقل مانده',
----####################
	

	if @ListCodeAnbar <>''
		begin
		declare @StrDeleteAnbar varchar(5000)
		set  @StrDeleteAnbar='delete from #TempKardeks 
		    where fldCodeAnbar NOT IN ('+ @ListCodeAnbar +')'
			execute(@StrDeleteAnbar)
		end

	UPDATE       #TempKardeks
		SET                CodeGroup = tblKala.fldCodeGroup, NameKala = tblKala.fldNameKala,fldTedadDarKarton=tblKala.fldTedadJoz
		FROM            tblKala INNER JOIN
								 #TempKardeks ON tblKala.fldCodeKala = #TempKardeks.CodeKala


	UPDATE       #TempKardeks
		SET                #TempKardeks.NameGroup = tblGroupKala.fldGroupName
		FROM            tblGroupKala INNER JOIN
								 #TempKardeks ON #TempKardeks.CodeGroup = tblGroupKala.fldGroupId


UPDATE       #TempKardeks
	SET                fldNameAnbar = tblAnbar.fldNameAnbar
	FROM            tblAnbar INNER JOIN
							 #TempKardeks ON tblAnbar.fldCode = #TempKardeks.fldCodeAnbar




update #TempKardeks set #TempKardeks.fldNameVahed=tbl2.fldNameVahed,#TempKardeks.fldNameVahed2=tbl2.fldNameVahed2
from
(SELECT    fldCodeKala,    tbl1.fldCodeVahed1, tbl1.fldCodeVahed2, tbl1.fldNameVahed, tblVahed.fldNameVahed AS fldNameVahed2
FROM            tblVahed INNER JOIN
                             (SELECT       fldCodeKala, tblKala.fldCodeVahed1, tblKala.fldCodeVahed2, tblVahed_1.fldNameVahed
                                FROM            tblKala INNER JOIN
                                                         tblVahed AS tblVahed_1 ON tblKala.fldCodeVahed1 = tblVahed_1.ID) AS tbl1 ON tblVahed.ID = tbl1.fldCodeVahed2) tbl2
														 where tbl2.fldCodeKala=#TempKardeks.CodeKala

update #TempKardeks set fldNameVahed2='' where fldTedadDarKarton=0

update #TempKardeks set fldSharh='' Where fldSharh='Sharh'

update kdx set fldDateUpdate=CONVERT(varchar(19), rk.fldDateUpdate, 120)
from #TempKardeks kdx inner join tblRizeKharid rk on kdx.ID=rk.ID
where kdx.fldtype=rk.fldtype and kdx.fldTedadeJozVorood>0

update kdx set fldDateUpdate=CONVERT(varchar(19), rf.fldDateUpdate, 120)
from #TempKardeks kdx inner join tblRizeForoosh rf on kdx.ID=rf.ID
where kdx.fldtype=rf.fldtype and kdx.fldTedadeJozKhorooj>0

update kdx set fldDateUpdate=CONVERT(varchar(19), rb.fldDateUpdate, 120)
from #TempKardeks kdx inner join tblRizeBargashtAzForoosh rb on kdx.CodeKala=rb.fldCodeKala and kdx.fldShomareFactor=rb.fldShomareFactor and kdx.fldShomareSanad=rb.fldShomareSanad and kdx.fldDate=rb.fldDate
where kdx.fldtype='bargashtazforoosh'

update kdx set fldDateUpdate=CONVERT(varchar(19), rbk.fldDateUpdate, 120)
from #TempKardeks kdx inner join tblRizeBargashtAzKharid rbk on kdx.CodeKala=rbk.fldCodeKala and kdx.fldShomareFactor=rbk.fldShomareFactor and kdx.fldShomareSanad=rbk.fldShomareSanad and kdx.fldDate=rbk.fldDate
where kdx.fldtype='bargashtazkharid'

update kdx set fldDateUpdate=CONVERT(varchar(19), rt.fldDateUpdate, 120)
from #TempKardeks kdx inner join tblRizeTolid rt on kdx.CodeKala=rt.fldCodeKala and kdx.fldShomareFactor=rt.fldShomareTolid and kdx.fldShomareSanad=rt.fldShomareSanad and kdx.fldDate=rt.fldDate and kdx.fldtype=rt.fldType
where kdx.fldtype in ('avalie','mahsool')

--برای جمع ستونها مجبور شدم این کار رو بکنم
update #TempKardeks set ConditionSum=1

----##########
--update #TempKardeks set IdOrder=1 where fldType='kharid'
--update #TempKardeks set IdOrder=2 where fldType='bargashtazforoosh'

----##########
    --declare @EmpCodeTafzili nvarchar(10),@SumJam bigint    
    --declare EmpCursor Cursor For
    --        Select fldCodetafzili from #TempKardeks  With (NoLock) 
    --    		open EmpCursor
    --    		fetch Next From EmpCursor into @EmpCodeTafzili
    --    		While @@Fetch_Status = 0
    --    			Begin	
    --    				set @SumJam=Select SUM(fldTedadeJozVorood-fldTedadeJozKhorooj) from  #TempKardeks   where fldCodetafzili =@EmpCodeTafzili
    --    				update #TempKardeks  set  SumMandeJoz=(Select top(1)  fldShomare from tblDaftarcheTell Where fldCodetafzili=@EmpCodeTafzili)
    --    						where fldCodetafzili =@EmpCodeTafzili
    --    				Fetch Next from EmpCursor
    --    			into @EmpCodeTafzili
    --    		end
    --    Close EmpCurSor
    --Deallocate EmpCursor
--update  #TempKardeks set SumMandeJoz = tbl3.RunningTotal from
--	(Select fldShomareSanad,SUM(fldTedadeJozVorood-fldTedadeJozKhorooj) OVER (PARTITION BY ConditionSum   ) AS RunningTotal from #TempKardeks) as tbl3
--	where #TempKardeks.fldShomareSanad=tbl3.fldShomareSanad

--update  #TempKardeks set SumMandeJoz = tblS.b from
--SELECT     fldShomareSanad, fldTedadeJozVorood-fldTedadeJozKhorooj,
--              (SELECT     SUM(fldTedadeJozVorood-fldTedadeJozKhorooj) AS Expr1
--                 FROM         #TempKardeks AS b
--                 WHERE     (fldShomareSanad <= a.fldShomareSanad)) AS b
--FROM         #TempKardeks AS a


--update set      fld1, fld2,
--          (SELECT     SUM(fld2) AS Expr1
--             FROM         tblTest7 AS b
--             WHERE     (fld1 <= a.fld1)) AS b
--FROM         tblTest7 AS a


--UPDATE    #TempKardeks
--SET              SumMandeJoz = tblss.b
--FROM         (SELECT      fldShomareSanad, fldTedadeJozVorood-fldTedadeJozKhorooj,
--                                                  (SELECT     SUM(fldTedadeJozVorood-fldTedadeJozKhorooj) AS Expr1
--                                                     FROM         #TempKardeks AS b
--                                                     WHERE     (CONVERT(dec, fldShomareSanad) <= CONVERT(dec, a.fldShomareSanad))) AS b
--                        FROM         #TempKardeks AS a) AS tblss INNER JOIN
--                      #TempKardeks ON tblss.fldShomareSanad = #TempKardeks.fldShomareSanad
--where #TempKardeks.fldShomareSanad=tbls.fldShomareSanad 

;WITH KardeksForOrder AS (
    SELECT  *,
            CASE
                WHEN fldDateUpdate IS NULL OR LTRIM(RTRIM(fldDateUpdate)) = '' THEN '9999-12-31 23:59:59'
                ELSE LTRIM(RTRIM(fldDateUpdate))
            END AS OrderDateUpdate
    FROM #TempKardeks
),
OrderedKardeks AS (
    SELECT  IdOrder,
            ROW_NUMBER() OVER(
                ORDER BY
                    fldDate,
                    CASE WHEN fldtype='mojodi_ebteda_doreh' THEN 0 ELSE 1 END,
                    OrderDateUpdate,
                    CASE WHEN fldtype='enteghal' THEN ISNULL(fldShomareEnteghal,0) ELSE ISNULL(fldShomareFactor,0) END,
                    CASE WHEN fldTedadeJozVorood>0 THEN 1 ELSE 2 END,
                    ISNULL(fldShomareSanad,0),
                    ISNULL(fldRadif,0),
                    ISNULL(ID,0)
            ) AS NewIdOrder
    FROM KardeksForOrder
)
UPDATE OrderedKardeks
SET IdOrder = NewIdOrder


---جمع ورود و خروج
UPDATE    #TempKardeks
SET              SumMandeJoz = tblss.b
FROM         (SELECT     44 AS Expr1, IdOrder, fldTedadeJozVorood - fldTedadeJozKhorooj AS Expr2,
                                                  (SELECT     SUM(fldTedadeJozVorood - fldTedadeJozKhorooj) AS Expr1
                                                     FROM         #TempKardeks AS b
                                                     WHERE     (b.IdOrder <= a.IdOrder)) AS b
                        FROM         #TempKardeks AS a) AS tblss INNER JOIN
                      #TempKardeks ON tblss.IdOrder = #TempKardeks.IdOrder
        

update #TempKardeks set SumMandeJozT=SumMandeJoz,fldTedadeJozVoroodT=fldTedadeJozVorood,fldTedadeJozKhoroojT=fldTedadeJozKhorooj
update #TempKardeks set SumMandeJozT=LEFT(Convert(varchar,SumMandeJoz),len(Convert(varchar,SumMandeJoz))-3) where right(convert(varchar,SumMandeJoz),2)='00'
update #TempKardeks set fldTedadeJozVoroodT=LEFT(Convert(varchar,fldTedadeJozVorood),len(Convert(varchar,fldTedadeJozVorood))-3) where right(convert(varchar,fldTedadeJozVorood),2)='00'
update #TempKardeks set fldTedadeJozKhoroojT=LEFT(Convert(varchar,fldTedadeJozKhorooj),len(Convert(varchar,fldTedadeJozKhorooj))-3) where right(convert(varchar,fldTedadeJozKhorooj),2)='00'
              

update #TempKardeks Set fldTedadVoroodKartonShow=Convert(varchar,floor(fldTedadeJozVorood / fldTedadDarKarton)) + ' ' + fldNameVahed2 +  Convert(varchar, { fn MOD(Convert(dec,fldTedadeJozVorood), fldTedadDarKarton) } ) + ' ' + fldNameVahed
	where fldTedadDarKarton>0

update #TempKardeks Set fldTedadKhoroojKartonShow=Convert(varchar,floor(fldTedadeJozKhorooj / fldTedadDarKarton)) + ' ' + fldNameVahed2 +  Convert(varchar, { fn MOD(Convert(dec,fldTedadeJozKhorooj), fldTedadDarKarton) } ) + ' ' + fldNameVahed
	where fldTedadDarKarton>0


update #TempKardeks Set fldKolMande=convert(varchar,(-1)*Convert(varchar,floor((-1)*convert(dec,SumMandeJoz) / convert(dec,fldTedadDarKarton)))) + ' کل ' +  Convert(varchar, { fn MOD(Convert(dec, SumMandeJoz), convert(dec,fldTedadDarKarton)) } )  +  ' جز '--convert(varchar,SumMandeJoz)--Convert(varchar,floor(SumMandeJoz / fldTedadDarKarton)) + ' ' + fldNameVahed2 +  Convert(varchar, { fn MOD(Convert(dec,SumMandeJoz), fldTedadDarKarton) } ) + ' ' + fldNameVahed
	where SumMandeJoz<0 and fldTedadDarKarton>0


update #TempKardeks Set fldTedadVoroodKartonShow='' where fldTedadeJozVorood=0
update #TempKardeks Set fldTedadKhoroojKartonShow='' where fldTedadeJozKhorooj=0



--update #TempKardeks Set fldTedadKhoroojKartonShow=Convert(varchar,floor(fldTedadeJozKhorooj / fldTedadDarKarton)) + ' ' + fldNameVahed2 +  Convert(varchar, { fn MOD(fldTedadeJozKhorooj, fldTedadDarKarton) } ) + ' ' + fldNameVahed
--	where fldTedadDarKarton>0

--جمع ستونها
--SELECT
--  AccountNumber,
--  flddate,
--  bill,
--  SUM(bill) OVER (PARTITION BY AccountNumber ORDER BY flddate) AS RunningTotal
--FROM
--  dbo.Table1 


----فیلتر تا قبل از تاریخ 
--Insert into #TempKardeksShow
--	(fldTedadeJozVorood,fldTedadeJozKhorooj,fldTedadDarKarton, NameGroup, CodeGroup, CodeKala, NameKala, fldNameVahed, fldNameVahed2)
--SELECT        SUM(fldTedadeVorood) AS SumTedadeVorood, SUM(fldTedadeKhorooj) AS SumTedadeKhorooj, 
--                         fldTedadDarKarton, fldGroupName, fldCodeGroup, fldCodeKala, fldNameKala, fldNogheSefaresh, fldNameVahed, fldNameVahed2,0
--FROM            #TempKardeks
--GROUP BY CodeKala, NameGroup, CodeGroup, NameKala, fldNameVahed, fldNameVahed2, fldTedadDarKarton, fldTedadeKol




	--Select CodeKala,NameKala,CodeGroup,NameGroup,fldShomareFactor,fldShomareSanad,fldDate,fldSharh,fldCodeAnbar,fldNameAnbar,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldTedadeJozVorood,fldTedadeJozKhorooj,fldJozMande,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldKolMande,fldtype,fldNameVahed,fldNameVahed2
	--	from #TempKardeks
	--update 

	--Select fldTedadeJozVorood,fldTedadeJozKhorooj,fldDate,fldShomareSanad,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldTedadDarKartonShow,SUM(fldTedadeJozVorood-fldTedadeJozKhorooj) OVER (PARTITION BY ConditionSum ORDER BY fldDate,fldShomareSanad) AS RunningTotal from #TempKardeks
	update #TempKardeks set fldTypeShow='خرید' where fldType='kharid'
	update #TempKardeks set fldTypeShow='امانی' where fldType='amani'
	update #TempKardeks set fldTypeShow='برگشت امانی' where fldType='bargashtamani'
	update #TempKardeks set fldTypeShow='ابتدای دوره' where fldType='mojodi_ebteda_doreh'	
	update #TempKardeks set fldTypeShow='انتقال بین انبار' where fldType='enteghal'
	update #TempKardeks set fldTypeShow='حواله خروج' where fldType='havalekhorooj'
	update #TempKardeks set fldTypeShow='فروش' where fldType='foroosh'
	update #TempKardeks set fldTypeShow='برگشت از فروش' where fldType='bargashtazforoosh'
	update #TempKardeks set fldTypeShow='برگشت از خرید' where fldType='bargashtazkharid'
	update #TempKardeks set fldTypeShow='تبدیل به محصول نهایی' where fldType='avalie'
	update #TempKardeks set fldTypeShow='محصول تولید شده در فرایند تولید' where fldType='mahsool'
	update #TempKardeks set fldTypeShow='مغایرت',fldCodeTafsili=0,fldSharh='مغایرت' where fldType='Moghayrat'

	
	update #TempKardeks set fldShomareFactor=fldShomareEnteghal where fldType='enteghal'

	update #TempKardeks set fldSharh= tblTafzili.fldSharheTafzili + ' ' + fldSharh from tblTafzili 
		Where #TempKardeks.fldCodeTafsili=tblTafzili.fldCodeTafzili

	update #TempKardeks Set fldKolMande=Convert(varchar,floor(summandejoz / fldTedadDarKarton)) + ' ' + fldNameVahed2 +  Convert(varchar, { fn MOD(Convert(dec, summandejoz), fldTedadDarKarton) } ) + ' ' + fldNameVahed
	where fldTedadDarKarton>0

	update #TempKardeks set flddateshow =substring(convert(nvarchar(10),fldDate),0,5) +'/' +substring(convert(nvarchar(10),fldDate),5,2) + '/' + substring(convert(nvarchar(10),fldDate),7,2)
	Select  ROW_NUMBER() OVER(ORDER BY IdOrder ) AS Row,fldSharh,fldtypeShow,fldShomareFactor,flddateshow,fldTedadeJozVoroodT As fldTedadeJozVorood,fldTedadeJozKhoroojT as fldTedadeJozKhorooj,SumMandeJozT as SumMandeJoz,fldNameVahed,fldFeeJoz,fldFeeYekKarton,fldMablaghKol,fldMablaghTakhfif,fldFeeVahed1BadAzTakhfif,fldFeeVahed2BadAzTakhfif,fldFeeKolBadAzTakhfif,fldArzeshAfzode,fldMablaghKhales,fldTedadVoroodKartonShow,fldTedadKhoroojKartonShow,fldTedadDarKarton,fldNameVahed2,fldKolMande,fldShomareSanad,fldNameAnbar,fldDate,fldtype,fldRadif,id from #TempKardeks order by IdOrder
	
--FROM
--  dbo.Table1 

END
GO

-- ===============================================
