

/****** Object:  View [dbo].[ApiDocumentsInfoView]    Script Date: 13.09.2021 15:08:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE VIEW [dbo].[ApiDocumentsInfoView] AS

SELECT     d.ID AS Id, d.ID AS ErpId, 0 AS DocId, ISNULL(d.Kontrahent,0) AS CustId, d.NumerNumer AS Number, YEAR(d.Data) AS Year, MONTH(d.Data) AS Month, d.Definicja AS DefId, d.NumerPelny AS NumberString, 
                      d.NumerPelny AS ErpNumberString, d.Seria AS Series, d.SumaNetto AS Value, ISNULL(d.Opis, '') AS Description, d.Data AS Date, d.DataOperacji Date2,d.Stan+1 AS State, 0 vendorId, CURRENT_TIMESTAMP AS TS, 
                       CAST(0 AS BIT) AS VeryfiedDoc, REPLACE(REPLACE(d.NumerPelny,'/',''),'RWŚT','RWST') AS BarCode, - 1 AS Lines,
                     
					  ISNULL(l.Nazwa,ISNULL(k.Nazwa,'')) + '-' + ISNULL(cast(AdresyLok.AdresUlica as varchar), ISNULL(cast(AdresyKnt.AdresUlica as varchar),'')) +
					  ' ' + ISNULL(cast(AdresyLok.AdresNrDomu as varchar), ISNULL(cast( AdresyKnt.AdresNrDomu as varchar),'')) +
					  ', ' + ISNULL(cast(AdresyLok.AdresKodPocztowy as varchar), ISNULL(cast(AdresyKnt.AdresKodPocztowy as varchar),'')) +
					  ' ' + ISNULL(cast( AdresyLok.AdresPoczta as varchar), ISNULL(cast(AdresyKnt.AdresPoczta as varchar),'')) Info, 
					  
					   d.KierunekMagazynu FlowDirection,
					   ISNULL(d.Kontrahent,0) AS CustCustId, 0 CustLocId, 0 CustLocLocId, d.BruttoCySymbol Currency,
					   d.Magazyn RepositoryId, 0 PrecedentDoc, '' PrecedentDocNumber, 0 ConfirmTransfer, 
					   dbo.ApiGetTSSec('DokHandlowe', d.Guid) [SaveTS] , '' DeviceUid, ObcyNumer ForeginNumber,
					   ISNULL(sped.Data,'') Category,
					      def.LiczonaOd NettoBrutto, d.SumaNetto Netto, d.SumaBrutto Brutto
		,0 StateDoc, ISNULL(c.Operator,0) OperatorId, 0 ReservationResource ,0 ReservationType, '' LinesInfo , '' Notes, ISNULL(c.Time, CURRENT_TIMESTAMP)  CreateTS
				,0 [ProjectId], 0 [ProjectLineId],  ISNULL(k.CenaTowaru,0) PriceId
FROM         dbo.DokHandlowe d

              JOIN        dbo.DefDokHandlowych  def ON  def.Id = d.Definicja --AND def.Behaviour= 3 
			 LEFT OUTER  JOIN dbo.ChangeInfos c ON c.SourceGuid = d.Guid and c.SourceTable = 'DokHandlowe' and c.Type = 2
				--	JOIN dbo.Features  f ON f.Parent = d.ID and f.Name='StanWeryfikacji' and f.ParentType='DokHandlowe' and f.DataKey='Do wydania'
					LEFT OUTER JOIN dbo.Features  sped ON sped.Parent = d.ID and sped.Name='Spedytor' and sped.ParentType='DokHandlowe'
					LEFT OUTER JOIN dbo.Kontrahenci k ON k.ID = d.Kontrahent
					LEFT OUTER JOIN dbo.Adresy AdresyKnt ON AdresyKnt.Host = k.Id and AdresyKnt.HostType='Kontrahenci' and AdresyKnt.Typ=1
					LEFT OUTER JOIN dbo.Lokalizacje l ON  l.ID= d.OdbiorcaMiejsceDostawy
					LEFT OUTER JOIN dbo.Adresy AdresyLok ON AdresyLok.Host = l.Id and AdresyLok.HostType='Lokalizacje' and AdresyLok.Typ=1



/*					  
where --Magazyn =1 --and Stan =0 
--and 
d.Definicja in (8,64,42,50)
*/




GO


