



  CREATE VIEW [dbo].[ApiCrmTaskTupleView] as
  select 
 t.ID Id,
  t.ID TaskId, t.ID [ErpId], defs.Id [DefId],
  ISNULL(c.Id,0) [CustId], ISNULL(l.ErpId,0) [CustLocationId],
  ISNULL(c.Id,0) [CustCustId], ISNULL(l.ErpId,0) [CustLocLocId],
  0 [ParentId]  , 8 [Priority], REPLACE(CAST(RodzajKontaktu as nvarchar),'\','') Title,
  REPLACE(CAST (Notatka as nvarchar(max)),'\','') Description,    
  '' as Notes,    
		 t.Data+  CONVERT( DATETIME,Cast( Czas.Czas as varchar) ) PlanDateFrom,
		
		 
		 case when (ISNULL(CAST(DataDo.DataDo as varchar),'')='') then DATEADD(minute,60,t.Data+  CONVERT( DATETIME,Cast( Czas.Czas as varchar) )) 
		 else CONVERT( DATETIME,Cast( DataDo.DataDo as varchar),101) + CONVERT( DATETIME,Cast( CzasDo.CzasDo as varchar))
		 end PlanDateTo,		 
		 Stan.Stan,
		 ISNULL(dictState.Id,0) StateId, ISNULL(cat.Id,0) [CategoryId],0 [ProductId] , 0 TypeId ,
		 /*
		 case when(cast(stan.Stan as varchar) ='Planowana') then 1 else
			 case when (cast(stan.Stan as varchar) ='W realizacji') 
			 then 2 else case when (cast(stan.Stan as varchar) ='Zakończona') then 3 else 1 end end end*/ 
			  0 as[DocumentId]
      ,0 as[DeviceId]
      ,0 as[TimeSpend]
	  , t.NumerNumer Number, YEAR(t.Data) Year, MONTH(t.Data) [Month], Seria [Series], t.NumerPelny [NumberString]
	        
      ,1 [Modified]
      , 0 [DayLp]
      ,cast(0 as decimal) [RouteDistanceTo]
      ,0 [RouteTimeTo]
		,operator.Host [OwnerId]
      , operator.Host [VendorId]
      ,'' [EventId]
	  ,'['+ISNULL(c.Code,'')+']'+ISNULL(c.Name,'')  [CustInfo]
		,0 [DocumentId2]
      ,0 [DocumentId3]
  ,dbo.ApiGetTSSec('Tuples', t.Guid) [SaveTS] ,
  CURRENT_TIMESTAMP TS
  ,'' DeviceUid
 ,0 [TaskSummaryId],CURRENT_TIMESTAMP [ModTS], cast(0 as decimal) [Distance], cast(0 as decimal)  [Income],cast(0 as decimal)  [Cost]
           ,ISNULL(c.Id,0) [CustErpId],ISNULL(l.ErpId,0) [CustLocErpId],0 [DepartamentId], 0 [CustContactPersonId],0 [ProjectId],0 [ProjectLineId]
  from  dbo.Tuples t 
  JOIN dbo.TuplesRelations knt ON  knt.HostType='Kontrahenci' and knt.DbTuple = t.ID
  LEFT OUTER JOIN dbo.TuplesRelations lok ON  lok.HostType='Lokalizacje' and lok.DbTuple = t.ID
  JOIN dbo.TuplesRelations operator ON  operator.HostType='Operators' and operator.DbTuple = t.ID
  LEFT OUTER JOIN 
  (select Parent Tuple , Data RodzajKontaktu from [dbo].[RuntimeIndexedFields] rf JOIN [dbo].[RuntimeFieldDefs] rfd ON rf.FieldDef = rfd.ID 
  and rfd.Name='RodzajKontaktu' ) RodzajKontaktu ON t.ID = RodzajKontaktu.Tuple
  LEFT OUTER JOIN 
  (select Parent Tuple , Data KntImportInfo from [dbo].[RuntimeIndexedFields] rf JOIN [dbo].[RuntimeFieldDefs] rfd ON rf.FieldDef = rfd.ID 
  and rfd.Name='KntImportInfo' ) KntImportInfo ON t.ID = KntImportInfo.Tuple
  LEFT OUTER JOIN 
  (select Parent Tuple , Data Czas from [dbo].[RuntimeIndexedFields] rf JOIN [dbo].[RuntimeFieldDefs] rfd ON rf.FieldDef = rfd.ID 
  and rfd.Name='Czas' ) Czas ON t.ID = Czas.Tuple 
  LEFT OUTER JOIN 
  (select Parent Tuple , Data CzasDo from [dbo].[RuntimeIndexedFields] rf JOIN [dbo].[RuntimeFieldDefs] rfd ON rf.FieldDef = rfd.ID 
  and rfd.Name='CzasDo' ) CzasDo ON t.ID = CzasDo.Tuple 
    LEFT OUTER JOIN 
  (select Parent Tuple , Data DataDo from [dbo].[RuntimeIndexedFields] rf JOIN [dbo].[RuntimeFieldDefs] rfd ON rf.FieldDef = rfd.ID 
  and rfd.Name='DataDo' ) DataDo ON t.ID = DataDo.Tuple 

    LEFT OUTER JOIN 
  (select Parent Tuple , Data Stan from [dbo].[RuntimeIndexedFields] rf JOIN [dbo].[RuntimeFieldDefs] rfd ON rf.FieldDef = rfd.ID 
  and rfd.Name='Stan' ) Stan ON t.ID = Stan.Tuple   
  
  

LEFT OUTER JOIN 
  (select Parent Tuple , Data Notatka from [dbo].[RuntimeIndexedFields] rf JOIN [dbo].[RuntimeFieldDefs] rfd ON rf.FieldDef = rfd.ID 
  and rfd.Name='Notatka' ) Notatka ON t.ID = Notatka.Tuple
 
JOIN dbo.ApiDefinitions defs ON  defs.ErpId = t.Definicja 
JOIN dbo.ApiDictionaries dictState ON dictState.ParentDefId  = 4 and dictState.Value = REPLACE(CAST (stan.Stan as nvarchar(max)),'\','')
LEFT JOIN dbo.ApiDictionaries cat ON cat.ParentDefId  = 133 and cat.ErpKey = REPLACE(CAST (RodzajKontaktu.RodzajKontaktu as nvarchar(max)),'\','')
--and REPLACE(CAST (stan.Stan as nvarchar(max)),'\','')= dict.Value
LEFT OUTER JOIN dbo.ApiCustomers c ON knt.Host = c.ErpId
LEFT OUTER JOIN dbo.ApiCustomerLocations l on lok.Host = c.ErpId
  where t.Definicja = 24  --and t.ID=4485
  --and ISNULL(cast(stan.Stan as varchar),'Planowana')='Planowana'
  
  --select * from dbo.RuntimeFieldDefs where Definition=24

  --select * from dbo.ApiDictionaries where ParentDefId = 4 


  --select * from dbo.ApiWorkers

  

GO


