
/****** Object:  View [dbo].[ApiCustLocationView]    Script Date: 13.09.2021 14:47:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE  VIEW [dbo].[ApiCustLocationView]
AS
SELECT 
lok.ID Id, 
lok.ID  CustLocId , lok.ID  ErpId, 
knt.ID AS CustId,
knt.ID AS CustCustId,
CAST (ISNULL(OwnerCust.DataKey,0) as int)  OwnerId,
CAST (ISNULL(OwnerCust.DataKey,0) as int)  OwnerCustId,
 lok.Nazwa AS Name, lok.Kod AS Code,
 CONVERT(varchar(10), adresy.AdresKodPocztowy) AS AdrrZip, adresy.AdresMiejscowosc AS AdrrCity, 
                      adresy.AdresUlica AS AdrrStreet, adresy.AdresNrDomu AS AdrrNumber, adresy.AdresNrLokalu AS AdrrAppNumber,
					  Adresy.AdresKraj AdrrCountry
					  , op.Operator vendorId, 
                       cast(0 as float)  geolat,cast(0 as float) AS geolong, 
                      0 as Type, adresy.AdresTelefon AS Phone, '' AS Phone2, '' AS Email, 
                      ISNULL(lok.Uwagi,'') Notes, '' as EAN,
                      CURRENT_TIMESTAMP AS TS,
					  ISNULL(WWW.Data,'') WWW,
					  dbo.ApiGetTSSec('Lokalizacje', lok.Guid) [SaveTS] 
					  ,'' DeviceUid
                   , 1 as [State]

FROM          dbo.Lokalizacje AS lok
JOIN dbo.Kontrahenci knt on lok.Kontrahent =knt.ID
JOIN dbo.Opiekunowie op On op.Kontrahent = knt.ID and op.Typ = 0 and op.Aktywny=1
LEFT OUTER JOIN
                      dbo.Features AS Grupa ON Grupa.Parent = knt.ID AND Grupa.Name = 'Grupa' AND Grupa.ParentType = 'Kontrahenci' 
					  
                     -- dbo.Features AS opegroup ON opegroup.ParentType = 'Operators' AND opegroup.Name = 'OpeKntGrupa' AND opegroup.DataKey = Grupa.DataKey INNER JOIN
                 LEFT OUTER JOIN  dbo.Adresy AS adresy ON adresy.Host = lok.ID AND adresy.HostType = 'Lokalizacje'-- AND adresy.Typ = 1 
 	LEFT OUTER 	JOIN dbo.Features AS OwnerCust ON OwnerCust.ParentType = 'Lokalizacje' AND OwnerCust.Name = 'Wlasciciel' AND OwnerCust.Lp=0
	LEFT OUTER 	JOIN dbo.Features AS WWW ON WWW.ParentType = 'Lokalizacje' AND WWW.Name = 'WWW' AND WWW.Lp=0


GO


