



CREATE  VIEW [dbo].[ApiCustomerView]
AS
SELECT  top 100 PERCENT knt.ID  Id, knt.ID  ErpId, knt.ID AS CustId, knt.Nazwa AS Name, knt.Kod AS Code, knt.EuVAT AS TAXID, 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,  Adresy.AdresKodKraju AdrrCountryCode,
					   knt.Blokada AS Locked,ISNULL(opegroup.Parent,0) vendorId, 
                       cast(0 as float)  geolat,cast(0 as float) AS geolong, 
                      0 as Type, adresy.AdresTelefon AS Phone, tel2.Kontakt AS Phone2, email.Kontakt AS Email, knt.Termin AS paymentTime, fp.Nazwa AS payment, ISNULL(Kategoria.Data, '') AS CategoryName, 0 AS CategoryId, 
                      ISNULL(knt.Uwagi,'') Notes, '' as EAN,
                      CURRENT_TIMESTAMP AS TS,
                      CAST(0 as DECIMAL(18,2)) LoyaltyScore, '' LoyaltyPasswd, '' VendorName , '' VendorPhone,'' VendorMail, 0 as Status,
					  ISNULL(www.Adres,'') WWW
					  ,dbo.ApiGetTSSec('Kontrahenci', knt.Guid) [SaveTS] ,'' DeviceUid, 1 as [State] ,cast(0 as decimal) Discount, fp.Id paymentId,
					  0 as RegionId , 0 as PriceId, knt.LimitKredytuValue PaymentLimit, knt.LimitKredytuSymbol  PaymentCurrency
FROM         dbo.Kontrahenci AS knt 
--JOIN dbo.Opiekunowie op On (op.Kontrahent = knt.ID and op.Typ = 0 and op.Aktywny=1 and knt.ID<>1) or knt.ID=831
LEFT OUTER JOIN
                      dbo.Features AS Grupa ON Grupa.Parent = knt.ID 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 INNER JOIN
                      dbo.Adresy AS adresy ON adresy.Host = knt.ID AND adresy.HostType = 'Kontrahenci' AND adresy.Typ = 1 INNER JOIN
                      dbo.FormyPlatnosci AS fp ON fp.ID = knt.SposobZaplaty LEFT OUTER JOIN
                      dbo.Features AS Kategoria ON Kategoria.Parent = knt.ID AND Kategoria.Name = 'Kategoria' AND Kategoria.ParentType = 'Kontrahenci' LEFT OUTER JOIN
                      dbo.DaneKnt AS tel2 ON tel2.HostType = 'Kontrahenci' AND tel2.Domyslny = 1 AND tel2.Rodzaj = 2 AND tel2.Host = knt.ID LEFT OUTER JOIN
                      dbo.DaneKnt AS email ON email.HostType = 'Kontrahenci' AND email.Domyslny = 1 AND email.Rodzaj = 3 AND email.Host = knt.ID
					   LEFT OUTER JOIN dbo.AdresyWWW www ON www.ZapisType='Kontrahenci' and www.Domyslny=1 and www.Zapis = knt.ID
 ORDER BY Code


 


 --831
GO


