


--select * from  dbo.ApiDefinitions
CREATE VIEW [dbo].[ApiTaskView] 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ć
dateadd (minute,z.CzasOd ,z.DataOd) [PlanDateFrom],
dateadd (minute,z.CzasDo ,z.DataDo) PlanDateTo,


 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]
,0[ProjectId], 0 [ProjectLineId]
from dbo.Zadania z 
JOIN dbo.ApiDefinitions def ON def.ErpId = z.Definicja 
--LEFT OUTER 
JOIN dbo.ApiCustomerView 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 = 150 and pr.ErpId = z.PriorytetZadania
LEFT OUTER JOIN dbo.ApiDictionaries st ON st.ParentDefId = 1 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 = 130 and cat.ErpKey = fkat.Data
--LEFT OUTER JOIN dbo.Features WypDataUslugi ON WypDataUslugi.Name='WypDataUslugi' and WypDataUslugi.lp=0 and WypDataUslugi.ParentType='Zadania' and WypDataUslugi.Parent = z.ID
--LEFT OUTER JOIN dbo.Features WypDataZwrotu ON WypDataZwrotu.Name='WypDataZwrotu' and WypDataZwrotu.lp=0 and WypDataZwrotu.ParentType='Zadania' and WypDataZwrotu.Parent = z.ID
GO


