/****** Object:  View [dbo].[ViwAccounts]    Script Date: 25/9/2024 11:52:30 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[ViwAccounts]
AS
SELECT   Total.ID AS TotalID, Total.Code AS TotalCode, Total.Title AS TotalTitle, Total.Type AS TotalType, Total.CreditType AS TotalCreditType, Total.Description AS TotalDescription, Total.CommitteRef AS TotalCommitteRef, Total.Credit AS TotalCredit, 
                Total.Debit AS TotalDebit, Total.AttachPath AS TotalAttachPath, Total.TitleNoSpace AS TotalTitleNoSpace, COALESCE(Control.ID,0) AS ControlID, Control.Code AS ControlCode, Control.Title AS ControlTitle, Control.Type AS ControlType, 
                Control.CreditType AS ControlCreditType, Control.Description AS ControlDescription, Control.Credit AS ControlCredit, Control.Debit AS ControlDebit, Control.Active AS ControlActive, Control.AttachPath AS ControlAttachPath, 
                Control.TitleNoSpace AS ControlTitleNoSpace, COALESCE(Detail1.ID,0) AS Detail1ID, Detail1.Code AS Detail1Code, Detail1.Title AS Detail1Title, Detail1.Type AS DetailType, Detail1.CreditType AS Detail1CreditType, Detail1.Description AS Detail1Description, 
                Detail1.Credit AS Detail1Credit, Detail1.Debit AS Detail1Debit, Detail1.Active AS Detail1Active, Detail1.AttachPath AS Detail1AttachPath, Detail1.TitleNoSpace AS Detail1TitleNoSpace, COALESCE(Detail2.ID,0) AS Detail2ID, Detail2.Code AS Detail2Code, 
                Detail2.Title AS Detail2Title, Detail2.Type AS Detail2Type, Detail2.CreditType AS Detail2CreditType, Detail2.Description AS Detail2Description, Detail2.Debit AS Detail2Debit, Detail2.Active AS Detail2Active, 
                Detail2.AttachPath AS Detail2AttachPath, Detail2.TitleNoSpace AS Detail2TitleNoSpace, dbo.ConcatAcc(N'-', Total.Code, Control.Code, Detail1.Code, Detail2.Code) AS FullCode, dbo.ConcatAcc(N'-', Total.Title, Control.Title, Detail1.Title, 
                Detail2.Title) AS FullTitle, COALESCE (Detail2.Code, Detail1.Code, Control.Code, Total.Code) AS Code, COALESCE (Detail2.Title, Detail1.Title, Control.Title, Total.Title) AS Title
FROM      dbo.AccTotal AS Total LEFT OUTER JOIN
                dbo.AccTotalControl AS TotalControl ON Total.ID = TotalControl.TotalID LEFT OUTER JOIN
                dbo.AccControl AS Control ON TotalControl.ControlID = Control.ID LEFT OUTER JOIN
                dbo.AccControlDetail AS ControlDetail ON Control.ID = ControlDetail.ControlID LEFT OUTER JOIN
                dbo.AccDetail AS Detail1 ON Detail1.ID = ControlDetail.DetailID LEFT OUTER JOIN
                dbo.AccDetailDetail AS DetailDetail ON Detail1.ID = DetailDetail.DetailID1 LEFT OUTER JOIN
                dbo.AccDetail AS Detail2 ON DetailDetail.DetailID2 = Detail2.ID
WHERE   (COALESCE (Detail2.Active, Detail1.Active, Control.Active, 1) = 1) AND (COALESCE (Detail1.Active, Control.Active, 1) = 1) AND (COALESCE (Control.Active, 1) = 1)
GO

