• 0
mahmoud osili

ما هي طريقة ال Grouping لشجرة الحسابات و حساب الدائن و المدين

سؤال

السلام عليكم

لدي ثلاث جداول

ChartOfAccount

JournalSeries

JournalTransaction

انشاء الجدول الاول




CREATE TABLE [dbo].[ChartOfAccount](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,


[nvarchar](max) NULL,
[ParentChartOfAccountId] [int] NULL,
[SequenceMaxValue] [int] NULL,
[IsActive] [bit] NULL,
[Level] [int] NULL,
[Shortcut] [nvarchar](max) NULL,
[ChartOfAccountTypeId] [int] NULL,
[UserType] [nvarchar](max) NULL,
[CurrencyId] [int] NULL,
[CurrencyCode] [nvarchar](5) NULL,
[Reconcile] [bit] NULL,
[CreatedOnUtc] [datetime] NULL,
[UpdatedOnUtc] [datetime] NULL,
[LastActivityDateUtc] [datetime] NULL,
[Comments] [nvarchar](max) NULL,
[CompanyId] [int] NULL,
[ForwardUpdate] [bit] NULL,
[ClosedPost] [bit] NULL,
[SubChartOfAccountTypeId] [int] NULL,
[SubChartOfAccountTypeName] [nvarchar](1000) NULL,
[ChartOfAccountExternalReference] [nvarchar](1000) NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ChartOfAccount] WITH CHECK ADD CONSTRAINT [FK_ChartOfAccount_ChartOfAccountType] FOREIGN KEY([ChartOfAccountTypeId])
REFERENCES [dbo].[ChartOfAccountType] ([Id])
GO

ALTER TABLE [dbo].[ChartOfAccount] CHECK CONSTRAINT [FK_ChartOfAccount_ChartOfAccountType]
GO

انشاء الجدول الثاني





CREATE TABLE [dbo].[JournalTransaction](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Exported] [bit] NULL,
[CreatedOnUtc] [datetime] NULL,
[UpdatedOnUtc] [datetime] NULL,
[TransSequence] [int] NULL,
[ChartOfAccountId] [int] NULL,
[MiscId] [int] NULL,
[Amount_LC] [decimal](18, 8) NULL,
[TransNotes] [nvarchar](1000) NULL,
[JournalNumber] [int] NULL,
[Posted] [bit] NULL,
[Rec] [bit] NULL,
[Deleted] [bit] NULL,
[JournalSeriesId] [int] NULL,
[JouranlDirection] [int] NULL,
[CurrencyId] [int] NULL,
[CurrencyRate] [decimal](18, 8) NULL,
[Amount_FC] [decimal](18, 8) NULL,
[ProfitCenterId] [int] NULL,
CONSTRAINT [PK_GeneralLedgerSeries] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[JournalTransaction] WITH CHECK ADD CONSTRAINT [FK_GeneralLedgerTransaction_Account1] FOREIGN KEY([ChartOfAccountId])
REFERENCES [dbo].[ChartOfAccount] ([Id])
GO

ALTER TABLE [dbo].[JournalTransaction] CHECK CONSTRAINT [FK_GeneralLedgerTransaction_Account1]
GO

ALTER TABLE [dbo].[JournalTransaction] ADD CONSTRAINT [DF_Table_1_gltrans_rec] DEFAULT ((0)) FOR [Rec]
GO

ALTER TABLE [dbo].[JournalTransaction] ADD CONSTRAINT [DF_Table_1_gltrans_deleted] DEFAULT ((0)) FOR [Deleted]
GO

انشاء الجدول الثالث





CREATE TABLE [dbo].[JournalSeries](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SeriesSequence] [int] NULL,
[DocumentType] [nchar](3) NULL,
[DocumentNumber] [nvarchar](100) NULL,
[Amount] [decimal](18, 8) NULL,
[Source] [nvarchar](1000) NULL,
[DistributionDate] [datetime] NULL,
[SeriesNotes] [nvarchar](1000) NULL,
[MiscId] [int] NULL,
[Type] [int] NULL,
[TypeId] [int] NULL,
[CurrencyId] [int] NULL,
CONSTRAINT [PK_GeneralLed] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

واريد عمل Grouping لشجرة الحسابات بالطريقة التاليه

مثال :





الحساب ------ رقم الحساب ------- الدائن ------- المدين ---------- الدائن-المدين

الايرادات ----------1------------------90------------77--------------------13-------
ايراد الاراضي-------11----------------90------------77--------------------13-------
ايراد الاراضي الزراعية--111------------80------------70------------------10-------
ايراد الاراضي الاخرى ---112----------10-------------7-------------------3---------

ملاحظة :

JournalTransaction.JouranlDirection = 1 اي انه مدين

JournalTransaction.JouranlDirection = 2 دائن

ChartOfAccount.Code رقم الحساب

ChartOfAccount.Name اسم الحساب

JournalSeries.Amount منها يتم قراءة المبلغ

0

شارك هذا الرد


رابط المشاركة
شارك الرد من خلال المواقع ادناه

3 إجابة على هذا السؤال .

  • 0

up

0

شارك هذا الرد


رابط المشاركة
شارك الرد من خلال المواقع ادناه
  • 0

up

0

شارك هذا الرد


رابط المشاركة
شارك الرد من خلال المواقع ادناه
  • 0

وصلت للحل بإستخدام ROLLUP

sql query



;WITH ROLLUP(AccountID,Code,Name,Parent,Amount,JouranlDirection,[Type])
AS (SELECT Chart.Id,
Chart.Code,
Chart.Name,
Chart.ParentChartOfAccountId,
JournalSeries.Amount_Lc as Amount,
JournalTransaction.JouranlDirection,
'Master'

FROM ChartOfAccount as Chart INNER JOIN
JournalTransaction ON Chart.Id = JournalTransaction.ChartOfAccountId INNER JOIN
JournalSeries ON JournalTransaction.JournalSeriesId = JournalSeries.Id
UNION ALL
SELECT R1.Id,
R1.Code,
R1.Name,
R1.ParentChartOfAccountId,
R2.Amount,
R2.JouranlDirection,
'Rollup'

FROM ChartOfAccount R1
JOIN ROLLUP R2
ON R1.Id = R2.Parent)

select temp.AccountId,temp.Code,temp.Name,sum(Debit) as Debit,sum(Credit) as Credit,sum(Balance) as Balance from
(
SELECT AccountId = AccountID,
Code=Code,
Name=Name,
Debit = SUM(Amount),
Credit=0,
Balance=SUM(Amount)-0
--,[Type]

FROM ROLLUP
where JouranlDirection = 1
GROUP BY [Type],
AccountID,Name,Code,JouranlDirection
--ORDER BY Code
union all
SELECT AccountId = AccountID,
Code=Code,
Name=Name,
Debit=0,
Credit = SUM(Amount),
Balance=0-SUM(Amount)
--,[Type]

FROM ROLLUP
where JouranlDirection = 2
GROUP BY [Type],
AccountID,Name,Code,JouranlDirection
) as temp

GROUP BY temp.AccountID,temp.Name,temp.Code
ORDER BY temp.Code

0

شارك هذا الرد


رابط المشاركة
شارك الرد من خلال المواقع ادناه

من فضلك سجل دخول لتتمكن من التعليق

ستتمكن من اضافه تعليقات بعد التسجيل



سجل دخولك الان

  • يستعرض القسم حالياً   0 members

    لا يوجد أعضاء مسجلين يشاهدون هذه الصفحة .