
/****** Object:  View [dbo].[ApiSericeTaskView]    Script Date: 13.09.2021 15:03:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--select * from  dbo.ApiDefinitions
CREATE VIEW [dbo].[ApiServiceTaskView] as
select z.ID Id, z.ID TaskId, z.ID ErpId, ISNULL(def.Id,0) DefId,  
ISNULL(c.Id,0) [CustId], ISNULL(l.Id,0) [CustLocationId], 0 [ParentId],
 ISNULL(pr.Id,0)  [Priority], z.Nazwa [Title],
z.Opis [Description], z.Uwagi [Notes],
z.DataOd [PlanDateFrom], z.DataOd [PlanDateTo] -- tutaj poprawić
, st.Id [StateId], 0 [TypeId], ISNULL(cat.Id,0) [CategoryId],0 [ProductId] ,
0 [DocumentId] , 0 [DeviceId] ,0 [TimeSpend], z.NumerNumer [Number]
,YEAR(z.DataOd) [Year],MONTH(z.DataOd) [Month],z.Seria [Series] ,z.NumerPelny [NumberString],case when (z.Aktywny=0) then 1 else 0 end [Modified]
,0 [DayLp],0 [RouteDistanceTo],0 [RouteTimeTo],z.Prowadzacy [OwnerId],z.Wykonujacy [VendorId]
,0 [EventId],CURRENT_TIMESTAMP [TS],c.Code +' '+ c.Name [CustInfo],0 [DocumentId2],0 [DocumentId3]
,dbo.ApiGetTSSec('Zadania', z.Guid)  [SaveTS],'' [DeviceUid],ISNULL(c.Id,0) [CustCustId],ISNULL(l.Id,0) [CustLocLocId]
,0 [TaskSummaryId],CURRENT_TIMESTAMP [ModTS],0 [Distance],0 [Income],0 [Cost],z.Kontrahent [CustErpId]
,ISNULL(z.Lokalizacja,0) [CustLocErpId],0 [DepartamentId],ISNULL(z.Przedstawiciel,0) [CustContactPersonId]
, z.Seria ,0[ProjectId], 0 [ProjectLineId]
from dbo.Zadania z 
JOIN dbo.ApiDefinitions def ON def.ErpId = z.Definicja and def.ErpId=3
--LEFT OUTER 
JOIN dbo.ApiCustomers c ON c.ErpId = z.Kontrahent
LEFT OUTER JOIN dbo.ApiCustomerLocations l ON l.ErpId = z.Lokalizacja
LEFT OUTER JOIN dbo.ApiDictionaries pr ON pr.ParentDefId = 151 and pr.ErpId = z.PriorytetZadania
LEFT OUTER JOIN dbo.ApiDictionaries st ON st.ParentDefId = 2 and st.ErpId = z.StanZadania
--LEFT OUTER JOIN dbo.Features fkat ON  fkat.Name = 'KategoriaWypożyczenia' and fkat.lp=0 and fkat.ParentType='Zadania' and fkat.Parent = z.ID
LEFT OUTER JOIN dbo.ApiDictionaries cat ON cat.ParentDefId = 131 and cat.ErpKey = z.Seria

GO


