[السيد الجوهري]

   


فى المثال المرفق والسابق ارفاقة بنفس البيانات فى المشاركة السابقة

نجد انه تم تحميل الارصدة الافتتاحية  مدين   ودائن
 وبالنظر الي حساب الصندوق نجد التالي  مدين ودائن به مبالغ

والتعديل المطلوب هو ان المدين اكبر من الدائن  لذا وجب ان ايكون الرصيد الافتتاحى مدين بمبلغ   3471.363 
وان تكون الخلية المقابله فى الدائن  تساوي صفر

وهذا هو البروسيدجر

كود :

ALTER proc [dbo].[get_mizan]
@B_DATE DATE,
@E_DATE DATE,
@TYPE_PR int

as
begin
declare @Debit decimal(18, 0)
declare @Credit decimal(18, 0)
DECLARE @ID INT
DECLARE @CODE NVARCHAR(MAX)
DECLARE @ACCOUNT NVARCHAR(MAX)
DECLARE @BD decimal(18,3)
DECLARE @BC decimal(18,3)

DECLARE @D decimal(18,3)
DECLARE @C decimal(18,3)

DECLARE @TD decimal(18,3)
DECLARE @TC decimal(18,3)
DECLARE @GUID uniqueidentifier
DECLARE @PARENT uniqueidentifier
DECLARE @END_ACCOUNT INT



 CREATE TABLE [dbo].[ggg](
    [guid] [nvarchar](50) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[MIZAN](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CODE] [nvarchar](max) NULL,
    [NAME] [nvarchar](max) NULL,
    [BD] [decimal](18, 3) NULL,
    [BC] [decimal](18, 3) NULL,
    [D] [decimal](18, 3) NULL,
    [C] [decimal](18, 3) NULL,
    [TD] [decimal](18, 3) NULL,
    [TC] [decimal](18, 3) NULL,
    [GUID] [uniqueidentifier] NULL,
    [PARENT] [uniqueidentifier] ROWGUIDCOL  NULL,
    [END_ACCOUNT] [int] NULL
) ON [PRIMARY]

  delete from MIZAN
       
declare @result bit
set @result = (SELECT CAST(CASE WHEN EXISTS(select guid    from ACCOUNT  where ParentGUID   in (select GUID   from acc) and guid  not in (select GUID    from acc)) THEN 1 ELSE 0 END  AS BIT ))
while(@result = 1)



begin
insert into acc select guid   from ACCOUNT   where ParentGUID in (select GUID   from acc) and guid  not in (select guid   from acc)
set @result = (SELECT CAST(CASE WHEN EXISTS(select guid   from ACCOUNT  where ParentGUID in (select GUID  from acc) and guid  not in (select GUID   from acc)) THEN 1 ELSE 0 END  AS BIT ))
IF @result = 0
       BREAK;
end
IF(@TYPE_PR=0)
BEGIN

INSERT INTO [dbo].[MIZAN] SELECT CODE ,NAME ,0,0,0,0,0,0,GUID,GUID,1 FROM ACCOUNT  
         
END
IF(@TYPE_PR=1)
BEGIN
INSERT INTO [dbo].[MIZAN] SELECT CODE ,NAME ,0,0,0,0,0,0 ,GUID,GUID,1 FROM ACCOUNT  WHERE GUID     IN (SELECT GUID FROM ACC)
END


declare c cursor for
SELECT [ID]
     ,[CODE]
     ,[NAME]
     ,[BD]
     ,[BC]
     ,[D]
     ,[C]
     ,[TD]
     ,[TC]
     ,[GUID]
     ,[PARENT]
     ,[END_ACCOUNT]
 FROM [dbo].[MIZAN] ORDER BY CODE
open c
fetch next from c
into @ID,@CODE,@ACCOUNT,@BD,@BC,@D,@C,@TD,@TC,@GUID,@PARENT,@END_ACCOUNT
while @@FETCH_STATUS=0
begin



DECLARE @SEARSH_BD decimal(18,3)
SET @SEARSH_BD=(SELECT    ISNULL( SUM(DEBIT) ,0) FROM         dbo.DAY1 INNER JOIN               dbo.DAY2 ON dbo.DAY1.GUID = dbo.DAY2.PAENT_GUID  WHERE     (DAY2.ACCOUNT_GUID = @GUID) AND (DAY1.DATE < CONVERT(DATE, @B_DATE, 102)))

DECLARE @SEARSH_BC decimal(18,3)
SET @SEARSH_BC=(SELECT    ISNULL( SUM(CREDIT) ,0) FROM         dbo.DAY1 INNER JOIN               dbo.DAY2 ON dbo.DAY1.GUID = dbo.DAY2.PAENT_GUID  WHERE     (DAY2.ACCOUNT_GUID = @GUID) AND (DAY1.DATE < CONVERT(DATE, @B_DATE, 102)))



DECLARE @SEARSH_D decimal(18,3)
SET @SEARSH_D=(SELECT    ISNULL( SUM(DEBIT) ,0) FROM         dbo.DAY1 INNER JOIN               dbo.DAY2 ON dbo.DAY1.GUID = dbo.DAY2.PAENT_GUID  WHERE      (day2.ACCOUNT_GUID = @GUID) AND (day1.DATE BETWEEN CONVERT(DATE, @B_DATE, 102) AND CONVERT(DATE, @E_DATE, 102)))



DECLARE @SEARSH_C decimal(18,3)

SET @SEARSH_C=(SELECT    ISNULL( SUM(CREDIT) ,0) FROM         dbo.DAY1 INNER JOIN               dbo.DAY2 ON dbo.DAY1.GUID = dbo.DAY2.PAENT_GUID  WHERE      (day2.ACCOUNT_GUID = @GUID) AND (day1.DATE BETWEEN CONVERT(DATE, @B_DATE, 102) AND CONVERT(DATE, @E_DATE, 102)))


delete from ggg
insert into ggg select  @GUID  from ACCOUNT where GUID = @GUID


declare @result1 bit
set @result1 = (SELECT CAST(CASE WHEN EXISTS(select ParentGUID    from ACCOUNT where GUID   in (select GUID    from ggg) and ParentGUID   not in (select GUID     from ggg)) THEN 1 ELSE 0 END  AS BIT ))
print @result1

while(@result1 = 1)
begin
insert into ggg select ParentGUID    from ACCOUNT  where GUID in (select GUID    from ggg) and ParentGUID   not in (select guid    from ggg)
set @result1 = (SELECT CAST(CASE WHEN EXISTS(select ParentGUID   from ACCOUNT where GUID in (select GUID   from ggg) and ParentGUID     not in (select GUID    from ggg)) THEN 1 ELSE 0 END  AS BIT ))
IF @result1 = 0
       BREAK;
end
update MIZAN set BD=BD+@SEARSH_BD,BC=BC+@SEARSH_BC,D=D+@SEARSH_D,C=C+@SEARSH_C ,TD=TD+@SEARSH_BD + @SEARSH_D,TC=TC+@SEARSH_BC + @SEARSH_C where GUID in (select GUID     from ggg)







fetch next from c
into @ID,@CODE,@ACCOUNT,@BD,@BC,@D,@C,@TD,@TC,@GUID,@PARENT,@END_ACCOUNT
end
close c
deallocate c
SELECT      CODE AS [رقم الحساب], NAME AS [اسم الحساب], BD AS [افتتاحي مدين], BC AS [افتتاحي دائن], D AS المدين, C AS الدائن, TD AS [مجموع المدين], TC AS [مجموع الدائن]
FROM         dbo.MIZAN
ORDER BY [رقم الحساب]


drop table ggg
drop table MIZAN

end

[/code]
[/code]