والتعديل المطلوب هو ان المدين اكبر من الدائن لذا وجب ان ايكون الرصيد الافتتاحى مدين بمبلغ 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