
/****** Object:  View [dbo].[ApiItemsView]    Script Date: 13.09.2021 15:06:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE  VIEW [dbo].[ApiItemsView] AS
SELECT     top 100 percent  tow.ID AS Id,  tow.ID AS ItemId, tow.ID ErpId,tow.Kod AS Code, tow.Nazwa AS Name, jedn.Kod AS UM, 
 tow.Typ ItemType, tow.EAN CatalogNumber , --cast(0 as decimal) Quantity,
  --tow.EAN,
   ISNULL(jednAlt.Kod,'') UM2, 
   case when (ISNULL(prz.WspolczynnikNum,0)<>0) then
    cast( prz.WspolczynnikDen as decimal(16,4)) /prz.WspolczynnikNum
   else
   cast(0 as decimal(16,4))
   end 
   UMRatio, 
   
   0 IntegerFormat  ,
 CONVERT(DECIMAL(16, 4), tow.MasaBruttoValue) AS Weight, 
                      ISNULL(CONVERT(DECIMAL(16, 4), zakupu.NettoValue), 0) AS BuyPrice, ISNULL(CONVERT(DECIMAL(16, 4), hurtowa.NettoValue), 0) AS SellPriceNetto, ISNULL(CONVERT(DECIMAL(16, 4), 
                      hurtowa.BruttoValue), 0) AS SellPriceBrutto,   hurtowa.NettoSymbol Currency,
                      --ISNULL(CONVERT(DECIMAL(16, 4), Zasoby.Ilosc), 0) AS Quantity, 
					  --cast(0 as decimal) AS Quantity, 
                      ISNULL(stawka.Kod, '') AS TaxDesc, ISNULL(CONVERT(DECIMAL(16, 4), 
                      stawka.StawkaProcent * 100), 0) AS TaxValue, /*grupa.DataKey*/'' AS GroupName1, ISNULL(dgr.Id,0)  AS GroupId1,
					   --Lokalizacja.DataKey AS GroupName2, 0 AS GroupId2, 
					   '' GroupName2, 0 AS GroupId2,
					   --ISNULL(CONVERT(INTEGER, stat.DataKey), 0) AS 'Status', 
					   cast ( 0 as int) 'Status',
					   CURRENT_TIMESTAMP AS TS, 'opis' AS Description, CONVERT(DECIMAL(16, 4), 0) AS MinPriceNetto,
					   /*ISNULL(CONVERT(decimal, grubosc.DataKey), 0)*/CONVERT(DECIMAL(16, 4), 0) AS Size,
					   ISNULL(FeatureType.Data,'') as FeatureType,
					   --'' FeatureType,
					   /*ISNULL(CONVERT(int, WaseItem.Data), 0)*/ 0  as WaseItem,
					   '' Material,
					   ISNULL(Dostawca,0) SupplierId
					      ,cast (0 as float) StockMax, cast (0 as float) StockMin
FROM         dbo.Towary AS tow LEFT OUTER JOIN
                      dbo.Ceny AS zakupu ON zakupu.Towar = tow.ID AND zakupu.Definicja = 9 LEFT OUTER JOIN
                      dbo.Ceny AS hurtowa ON hurtowa.Towar = tow.ID AND hurtowa.Definicja = 8 LEFT OUTER JOIN
                      dbo.DefStawekVat AS stawka ON tow.DefinicjaStawki = stawka.ID LEFT OUTER JOIN
                      dbo.Jednostki AS jedn ON jedn.ID = tow.Jednostka  
					  LEFT OUTER JOIN dbo.Jednostki jednAlt ON jednAlt.ID = tow.JednostkaAlternatywna
					  LEFT OUTER JOIN dbo.PrzelTow prz ON prz.Wyliczana = tow.JednostkaAlternatywna and prz.Bazowa = tow.Jednostka and prz.Towar = tow.ID
                      /*
                      JOIN
                        
                          (SELECT     Towar, SUM(IloscValue * Kierunek) AS Ilosc
                            FROM          dbo.Zasoby AS Zasoby_1
                         WHERE      (Kierunek = 1 and PartiaTyp = 1)
                            GROUP BY Towar) AS Zasoby ON Zasoby.Towar = tow.ID 
                        */    
--                            LEFT OUTER JOIN
                            
                     -- dbo.Features AS Lokalizacja ON Lokalizacja.ParentType = 'Towary' AND Lokalizacja.Name = 'Lokalizacja' AND Lokalizacja.Parent = tow.ID LEFT OUTER JOIN
                      LEFT OUTER JOIN  dbo.Features AS grupa ON grupa.ParentType = 'Towary' AND grupa.Name = 'Marka' AND grupa.Parent = tow.ID 
					  LEFT OUTER JOIN dbo.ApiDictionaries dgr ON dgr.ErpKey = grupa.Data
					  --LEFT OUTER JOIN dbo.ApiDictionaries gr ON gr.ParentDefId=401 and gr.ErpKey = grupa.DataKey
                     -- dbo.Features AS stat ON stat.ParentType = 'Towary' AND stat.Name = 'StatusTwr' AND stat.Parent = tow.ID
					  --LEFT OUTER JOIN dbo.Features AS grubosc ON grubosc.ParentType = 'Towary' AND grubosc.Name = 'Grubosc' AND grubosc.Parent = tow.ID
					 LEFT OUTER JOIN dbo.Features AS FeatureType ON FeatureType.ParentType = 'Towary' AND FeatureType.Name = 'WMSFeatureType' AND FeatureType.Parent = tow.ID
					  --LEFT OUTER JOIN dbo.Features AS WaseItem ON WaseItem.ParentType = 'Towary' AND WaseItem.Name = 'WaseItem' AND WaseItem.Parent = tow.ID
					  where tow.Blokada=0
ORDER BY Code










GO


