

/****** Object:  View [dbo].[ApiImpTaskView]    Script Date: 13.09.2021 14:57:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW  [dbo].[ApiImpTaskView]
as
SELECT t.[Id],t.Id [TaskId],t.[ErpId],d.ErpId [DefId], ISNULL(c.ErpId,0) [CustId],ISNULL(l.ErpId,0) [CustLocationId]
      ,ISNULL(parentTask.ErpId,0) [ParentId]
      , ISNULL(pr.ErpId,0) [Priority] ,t.[Title],t.[Description],t.[Notes]
      ,t.[PlanDateFrom],t.[PlanDateTo]
      ,stan.ErpId [StateId]
      ,ISNULL(product.ErpKey,'') [Product] ,ISNULL(cat.ErpKey,'') [Category], ISNULL(ttype.ErpKey,'') [Type]
	  ,ISNULL(product.ErpId,0) [ProductId] ,ISNULL(cat.ErpId,0) [CategoryId], ISNULL(ttype.ErpId,0) [TypeId]
      ,t.[DocumentId],t.[DeviceId],t.[TimeSpend]
      ,t.[Number],t.[Year],t.[Month],t.[Series],t.[NumberString],t.[Modified],t.[DayLp],t.[RouteDistanceTo],t.[RouteTimeTo]
      ,t.[OwnerId],t.[VendorId],t.[EventId],t.[TS],t.[CustInfo]
      ,t.[DocumentId2],t.[DocumentId3]
      ,t.[SaveTS],t.[DeviceUid],t.[CustCustId],t.[CustLocLocId],t.[TaskSummaryId]
      ,t.[ModTS],t.[Distance],t.[Income],t.[Cost]
      ,t.[CustErpId],t.[CustLocErpId],t.[DepartamentId],ISNULL(cperson.ErpId,0) [CustContactPersonId],t.[ProjectId],t.[ProjectLineId]
  FROM [dbo].[ApiCrmTasks] t
  JOIN dbo.ApiDefinitions d ON t.DefId = d.Id
  JOIN dbo.ApiDictionaries stan ON stan.Id = t.StateId
  LEFT OUTER JOIN dbo.ApiCustomers c ON  c.Id = t.CustId
  LEFT OUTER JOIN dbo.ApiCustomerLocations l ON l.Id = t.CustLocLocId
  LEFT OUTER JOIN dbo.ApiDictionaries pr ON pr.Id = t.Priority
  LEFT OUTER JOIN dbo.ApiDictionaries cat ON cat.Id = t.CategoryId
  LEFT OUTER JOIN dbo.ApiDictionaries product ON product.Id = t.ProductId
  LEFT OUTER JOIN dbo.ApiDictionaries ttype ON ttype.Id = t.TypeId
  LEFT OUTER JOIN dbo.ApiCustomerPersons cperson ON  cperson.Id = t.[CustContactPersonId]
  LEFT OUTER JOIN dbo.ApiCrmTasks parentTask ON parentTask.Id = t.ParentId
  where t.ParentId =0 OR (t.ParentId<>0 and ISNULL(parentTask.ErpId,0)<>0)

GO


