

/****** Object:  View [dbo].[ApiCustomerPersonsView]    Script Date: 13.09.2021 14:49:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE  VIEW [dbo].[ApiCustomerPersonsView] AS
SELECT 


osoba.ID Id,
osoba.ID  CustPersonId , osoba.ID  ErpId, 
knt.ID AS CustId, knt.ID AS CustCustId, 
Cast( ISNULL(Lokalizacja.DataKey,0) as int) CustLocId, 
Cast( ISNULL(Lokalizacja.DataKey,0) as int) CustLocLocId, 
osoba.Imie AS Name, osoba.Nazwisko AS LastName, osoba.Stanowisko Position 
 					,0 VendorId,
                    0 as Type, ISNULL(telefon.Kontakt,'') AS Phone, ISNULL(telefon2.Kontakt,'') AS Phone2, ISNULL(daneEmail.Kontakt,'') AS Email, 
                      ISNULL(osoba.Uwagi,'') Notes, 
                      CURRENT_TIMESTAMP AS TS
                   ,dbo.ApiGetTSSec('KontaktyOsoby', osoba.Guid) [SaveTS] 
				   ,'' DeviceUid, 1 as [State]
				   ,ISNULL(OsobaPlatnosci.Data,0) NotyficationType
FROM         dbo.KontaktyOsoby AS osoba
JOIN dbo.ApiCustomers knt on osoba.Kontrahent =knt.ErpId
--JOIN dbo.Opiekunowie op On op.Kontrahent = knt.ID and op.Typ = 0 and op.Aktywny=1
                 --LEFT OUTER JOIN  dbo.Adresy AS adresy ON adresy.Host = osoba.ID AND adresy.HostType = 'KontaktyOsoby'-- AND adresy.Typ = 1 
LEFT OUTER JOIN dbo.DaneKnt as daneEmail  ON daneEmail.Rodzaj =3 and daneEmail.HostType = 'KontaktyOsoby' and daneEmail.Host = osoba.ID
LEFT OUTER JOIN dbo.DaneKnt as telefon  ON telefon.Rodzaj =1 and telefon.HostType = 'KontaktyOsoby' and telefon.Host = osoba.ID
LEFT OUTER JOIN dbo.DaneKnt as telefon2  ON telefon2.Rodzaj =2 and telefon2.HostType = 'KontaktyOsoby' and telefon2.Host = osoba.ID

LEFT OUTER JOIN    dbo.Features AS Lokalizacja ON Lokalizacja.Parent = osoba.ID AND Lokalizacja.Name = 'Lokalizacja' AND Lokalizacja.ParentType = 'KontaktyOsoby' 
LEFT OUTER JOIN dbo.Features OsobaPlatnosci ON OsobaPlatnosci.ParentType='KontaktyOsoby' and OsobaPlatnosci.Parent= osoba.ID and OsobaPlatnosci.Name = 'OsobaPlatnosci'
-- LEFT OUTER JOIN dbo.Features AS Grupa ON Grupa.Parent = knt.ErpId   AND Grupa.Name = 'Rejon' AND Grupa.ParentType = 'Kontrahenci' 
--LEFT OUTER JOIN dbo.Features AS opegroup ON opegroup.ParentType = 'Operators' AND opegroup.Name = 'Rejon' AND opegroup.DataKey = Grupa.DataKey 



GO


