CREATE VIEW dbo.WEBAPI_TNY240001E
AS
SELECT ITM.LOGICALREF AS Ref, ITM.CODE AS SicilKodu, { fn CONCAT({ fn CONCAT(ITM.NAME, ' ') }, ITM.NAME3) } AS SicilAdi, ISNULL(ITM.SPECODE4, '') AS Marka, ISNULL(ITM.STGRPCODE, '') AS UrunTipi, '0' AS KampanyaFiyati, 
                  CASE WHEN ISNULL(GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED, 0) > 0 THEN 'VAR' ELSE 'YOK' END AS StokDurumu, ISNULL(ROUND(TLF.TL, 3), 0) * 1.2 AS ListeFiyati, 
                  /* ----------------- MaliyetFiyati (ZAMLI) ----------------- */ MX.BaseMaliyet * (1 + CASE WHEN MX.BaseMaliyet >= 0 AND MX.BaseMaliyet < 15 THEN 400 / 100.0 WHEN MX.BaseMaliyet >= 15 AND 
                  MX.BaseMaliyet < 25 THEN 300 / 100.0 WHEN MX.BaseMaliyet >= 25 AND MX.BaseMaliyet < 50 THEN 200 / 100.0 WHEN MX.BaseMaliyet >= 50 AND MX.BaseMaliyet < 100 THEN 200 / 100.0 WHEN MX.BaseMaliyet >= 100 AND 
                  MX.BaseMaliyet < 150 THEN 200 / 100.0 WHEN MX.BaseMaliyet >= 150 AND MX.BaseMaliyet < 250 THEN 80 / 100.0 WHEN MX.BaseMaliyet >= 250 AND MX.BaseMaliyet < 500 THEN 60 / 100.0 WHEN MX.BaseMaliyet >= 500 AND 
                  MX.BaseMaliyet < 1000 THEN 40 / 100.0 WHEN MX.BaseMaliyet >= 1000 AND MX.BaseMaliyet < 2500 THEN 40 / 100.0 WHEN MX.BaseMaliyet >= 2500 AND MX.BaseMaliyet < 5000 THEN 35 / 100.0 WHEN MX.BaseMaliyet >= 5000 AND 
                  MX.BaseMaliyet < 10000 THEN 40 / 100.0 ELSE 45 / 100.0 END) AS MaliyetFiyati, MX.BaseMaliyet AS Nakit, 'TL' AS Kur, CASE WHEN KR.LOGICALREF IS NULL THEN { fn CONCAT({ fn CONCAT('https://universaloto.com/foto/photos/', 
                  dbo.formatNumber(ITM.LOGICALREF)) }, '.jpg') } ELSE '' END AS Resim, ITM.SPECODE2 AS Arac, CASE WHEN ISNULL(GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED, 0) 
                  > 10 THEN 10 ELSE ISNULL(GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED, 0) END AS StokAdeti, ITM.EXPCTGNO AS Barcode, ITM.GTIPCODE AS Kategori
FROM     dbo.LG_011_ITEMS AS ITM LEFT OUTER JOIN
                  dbo.LV_011_08_GNTOTST AS GNT ON GNT.STOCKREF = ITM.LOGICALREF AND GNT.INVENNO = - 1 LEFT OUTER JOIN
                  dbo.UNI_TL_FIYAT AS TLF ON TLF.CARDREF = ITM.LOGICALREF LEFT OUTER JOIN
                  dbo.KirikResimler AS KR ON KR.LOGICALREF = ITM.LOGICALREF /* Tek seferlik “taban maliyet” hesabi */ CROSS APPLY
                      (SELECT CASE WHEN ISNULL(NULLIF (ITM.GTIPCODE, ''), 'DIGER') = 'AYDINLATMA' THEN TLF.TL * 0.7 * 1.2 ELSE TLF.TL * 0.85 * 1.2 END AS BaseMaliyet) MX
WHERE  ITM.ACTIVE = 0 AND TLF.TL IS NOT NULL;