CREATE VIEW dbo.WEBAPI_GMS350327I
AS
SELECT        ITM.LOGICALREF AS Ref, ITM.CODE AS SicilKodu, dbo.RepOzel(dbo.InitCap({ fn CONCAT({ fn CONCAT({ fn CONCAT(ISNULL(ITM.SPECODE2, ''), ' ') }, ISNULL(dbo.RepLhRh(ITM.NAME), '')) }, ISNULL(dbo.RepLhRh(ITM.NAME3), '')) 
                         })) AS SicilAdi, ISNULL(ITM.SPECODE4, '') AS Marka, ISNULL(ITM.STGRPCODE, '') AS UrunTipi, CASE WHEN (ISNULL(GNT.ONHAND, 0) - ISNULL(GNT.TEMPIN, 0) - ISNULL(GNT.RESERVED, 0)) 
                         > 0 THEN 'VAR' ELSE 'YOK' END AS StokDurumu, CASE WHEN ISNULL(TLF.TL, 0) BETWEEN 0 AND 200 THEN ISNULL(TLF.TL, 0) * 2.50 WHEN ISNULL(TLF.TL, 0) BETWEEN 201 AND 500 THEN ISNULL(TLF.TL, 0) 
                         * 1.90 WHEN ISNULL(TLF.TL, 0) BETWEEN 501 AND 1000 THEN ISNULL(TLF.TL, 0) * 1.50 WHEN ISNULL(TLF.TL, 0) BETWEEN 1001 AND 2500 THEN ISNULL(TLF.TL, 0) * 1.45 WHEN ISNULL(TLF.TL, 0) BETWEEN 2501 AND 
                         4000 THEN ISNULL(TLF.TL, 0) * 1.40 WHEN ISNULL(TLF.TL, 0) BETWEEN 4001 AND 7000 THEN ISNULL(TLF.TL, 0) * 1.35 WHEN ISNULL(TLF.TL, 0) BETWEEN 7001 AND 20000 THEN ISNULL(TLF.TL, 0) 
                         * 1.30 WHEN ISNULL(TLF.TL, 0) > 20000 THEN ISNULL(TLF.TL, 0) * 1.25 ELSE 0 END AS ListeFiyati, 'TL' AS Kur, 'ADET' AS Birim, ISNULL(ITM.VAT, 20) AS Kdv, CASE WHEN KR.LOGICALREF IS NULL 
                         THEN CONCAT('https://universaloto.com/foto/photos/', ISNULL(dbo.formatNumber(ITM.LOGICALREF), '0'), '.jpg') ELSE '' END AS Resim, ITM.SPECODE2 AS Arac, CASE WHEN (ISNULL(GNT.ONHAND, 0) - ISNULL(GNT.TEMPIN, 0) 
                         - ISNULL(GNT.RESERVED, 0)) > 10 THEN 10 ELSE (ISNULL(GNT.ONHAND, 0) - ISNULL(GNT.TEMPIN, 0) - ISNULL(GNT.RESERVED, 0)) END AS StokAdeti, ISNULL(MAM.KAT1, '') AS Model, ITM.GTIPCODE AS Kategori, 
                         ISNULL(MAM.KAT1, '') AS Kategori1
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.MALZEME_ARAC_MODELLERI AS MAM ON MAM.LOGREF = ITM.LOGICALREF LEFT OUTER JOIN
                         dbo.KirikResimler AS KR ON KR.LOGICALREF = ITM.LOGICALREF
WHERE        (ITM.ACTIVE = 0) AND (TLF.TL IS NOT NULL) AND (ISNULL(ITM.STGRPCODE, '') NOT IN ('ARMA', 'JANT GÖBEGI', 'YAZI')) AND (LOWER(dbo.RepOzel(dbo.InitCap({ fn CONCAT({ fn CONCAT({ fn CONCAT(ISNULL(ITM.SPECODE2, ''), 
                         ' ') }, ISNULL(dbo.RepLhRh(ITM.NAME), '')) }, ISNULL(dbo.RepLhRh(ITM.NAME3), '')) }))) NOT LIKE '%volvo%') AND (LOWER(ISNULL(ITM.SPECODE2, '')) NOT LIKE '%volvo%') AND (ITM.SPECODE2 LIKE '%NISSAN%') OR
                         (ITM.ACTIVE = 0) AND (TLF.TL IS NOT NULL) AND (ISNULL(ITM.STGRPCODE, '') NOT IN ('ARMA', 'JANT GÖBEGI', 'YAZI')) AND (LOWER(dbo.RepOzel(dbo.InitCap({ fn CONCAT({ fn CONCAT({ fn CONCAT(ISNULL(ITM.SPECODE2, ''), 
                         ' ') }, ISNULL(dbo.RepLhRh(ITM.NAME), '')) }, ISNULL(dbo.RepLhRh(ITM.NAME3), '')) }))) NOT LIKE '%volvo%') AND (LOWER(ISNULL(ITM.SPECODE2, '')) NOT LIKE '%volvo%') AND (ITM.SPECODE2 NOT LIKE '%NISSAN%') AND 
                         (ISNULL(TLF.TL, 0) >= 500)