/****** Object:  View [dbo].[AccValuesInDocProject]    Script Date: 2023/03/13 03:08:17 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create  VIEW [dbo].[AccValuesInDocProjectFinal]
AS
	SELECT        Item.TotalID, Item.ControlID, Item.DetailID1, Item.DetailID2, SUM(Item.Credit) AS Credit, 
							 SUM(Item.Debit) AS Debit, dbo.AccTotal.Code AS TotalCode, dbo.AccControl.Code AS ControlCode, dbo.AccDetail.Code AS DetailCode1, 
							 Joze.Code AS DetailCode2, COALESCE (Joze.Title, dbo.AccDetail.Title, dbo.AccControl.Title, dbo.AccTotal.Title, '') AS AccTitle,
							Item.CostCenter, Item.Project , Cost.Title  as CostCenterTitle, Proj.Title as ProjectTitle
	FROM (select ID, TotalID, ControlID, DetailID1, DetailID2, Credit, Debit, CostCenter, Project from dbo.AccDocItem where HeaderID in (select id from AccDocHeader where Flags in (0,1,2))) as Item LEFT OUTER JOIN
			dbo.AccControl ON Item.ControlID = dbo.AccControl.ID LEFT OUTER JOIN
			dbo.AccDetail ON Item.DetailID1 = dbo.AccDetail.ID LEFT OUTER JOIN
			dbo.AccTotal ON Item.TotalID = dbo.AccTotal.ID LEFT OUTER JOIN
			dbo.AccDetail AS Joze ON Item.DetailID2 = Joze.ID LEFT OUTER JOIN
			dbo.AccDetail AS Cost ON Item.CostCenter = Cost.ID LEFT OUTER JOIN
			dbo.AccDetail AS Proj ON Item.Project = Proj.ID
	GROUP BY Item.TotalID, Item.ControlID, Item.DetailID1, Item.DetailID2, dbo.AccControl.Code, dbo.AccTotal.Code, 
			 dbo.AccDetail.Code, Joze.Code, dbo.AccDetail.Title, dbo.AccControl.Title, dbo.AccTotal.Title, Joze.Title, 
			 Item.CostCenter, Item.Project , Cost.Title, Proj.Title 
	HAVING        (Item.TotalID > 0) 
GO

--select distinct flags from AccDocHeader 