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

#122 مايو 2012 في 10:20 صاحب الموضوع

السلام عليكم

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

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 منها يتم قراءة المبلغ

اللهم صلّ على سيدنا محمد وعلى آله وصحبه وسلّم

موقعي الرياضي

#229 مايو 2012 في 11:39

up

اللهم صلّ على سيدنا محمد وعلى آله وصحبه وسلّم

موقعي الرياضي

#330 مايو 2012 في 09:15

up

اللهم صلّ على سيدنا محمد وعلى آله وصحبه وسلّم

موقعي الرياضي

#430 مايو 2012 في 12:53

وصلت للحل بإستخدام 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

اللهم صلّ على سيدنا محمد وعلى آله وصحبه وسلّم

موقعي الرياضي

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

مواضيع مشابهة

عدد الزوار حالياً