

CREATE View [dbo].[ApiCrmTaskZadaniowkaView] as
SELECT t.Id
      ,[TaskId]
      ,t.[ErpId]
      ,[DefId]
      ,[CustId]
      ,[CustLocationId]
	  ,CustContactPersonId
      ,[ParentId]
      ,[Priority]
      ,[Title]
      ,[Description]
      ,dbo.ApiTaskDevices(t.Id)  [Notes]
      ,[PlanDateFrom]
      ,[PlanDateTo]
      ,[StateId]
      ,[ProductId]
      ,[TypeId]
      ,[CategoryId]
      ,[DocumentId]
      ,[DeviceId]
      ,ISNULL((select sum(h.TimeSpend) from dbo.ApiTaskHistories h where h.TaskId = t.Id and h.TimeSpend<>0),0)  [TimeSpend]
	  ,ISNULL((select sum(h.Distance) from dbo.ApiTaskHistories h where h.TaskId = t.Id and h.Distance<>0),0)  Distance
	  ,ISNULL((select sum(h.Value) from dbo.ApiTaskHistories h where h.TaskId = t.Id and h.Value<>0 and h.ObjectType=301),0)  Cost
	  ,ISNULL((select sum(h.Value) from dbo.ApiTaskHistories h where h.TaskId = t.Id and h.Value<>0 and h.ObjectType=302),0)  Income
	  ,ISNULL((select max(h.TS) from dbo.ApiTaskHistories h where h.TaskId = t.Id ),TS)  [ModTS]
      ,[Number]
      ,[Year]
      ,[Month]
      ,[Series]
      ,[NumberString]
      ,[Modified]
      ,[DayLp]
      ,[RouteDistanceTo]
      ,[RouteTimeTo]
      ,[OwnerId]
      ,[VendorId]
      ,[EventId]
      ,[TS]
      ,[CustInfo]
      ,[DocumentId2]
      ,[DocumentId3]
      ,[SaveTS]
      ,[DeviceUid]
      ,[CustCustId]
      ,[CustLocLocId]
      ,[TaskSummaryId]
	  ,cast(d.NumberValue as int)  [PriorityValue],
	  DepartamentId
	  ,t.ProjectId
	  ,ProjectLineId
	  , (select top 1  [dbo].[api_StripHTML](hh.Description) +'('+ cast(hh.TimeSpend as nvarchar) +' min)' from dbo.ApiTaskHistories hh  where ObjectType = 110 and hh.TaskId = t.Id ORDER BY TS DESC) Info
  FROM [ApiCrmTasks] t
  JOIN dbo.ApiDictionaries d ON d.Id = t.Priority;
GO


