CREATE VIEW dbo.WEBAPI_GMS350327I0
AS
SELECT
    *
FROM
(
    -- Ürün verilerini çeken ve hesaplanan alanlari olusturan ana sorgu
    SELECT
        ITM.LOGICALREF AS Ref,
        ITM.CODE AS SicilKodu,
        -- Ürün Adi olusturuluyor: SPECODE2 + NAME + NAME3 (Temizlenmis ve Bas Harfi Büyük)
        dbo.RepOzel(dbo.InitCap({ fn CONCAT({ fn CONCAT({ fn CONCAT(ITM.SPECODE2, ' ') }, dbo.RepLhRh(ITM.NAME)) }, dbo.RepLhRh(ITM.NAME3)) })) AS SicilAdi,
        ISNULL(ITM.SPECODE4, '') AS Marka,
        ISNULL(ITM.STGRPCODE, '') AS UrunTipi,
        -- Stok Durumu Kontrolü (VAR/YOK)
        CASE
            WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) > 0
            THEN 'VAR'
            ELSE 'YOK'
        END AS StokDurumu,
        -- Liste Fiyati Hesaplamasi (TLF.TL'ye göre kademeli artis oranlari)
        CASE
            WHEN TLF.TL BETWEEN 0 AND 200      THEN TLF.TL * 2.50
            WHEN TLF.TL BETWEEN 201 AND 500     THEN TLF.TL * 2.00
            WHEN TLF.TL BETWEEN 501 AND 1000    THEN TLF.TL * 1.80
            WHEN TLF.TL BETWEEN 1001 AND 2500   THEN TLF.TL * 1.70
            WHEN TLF.TL BETWEEN 2501 AND 4000   THEN TLF.TL * 1.65
            WHEN TLF.TL BETWEEN 4001 AND 7000   THEN TLF.TL * 1.60
            WHEN TLF.TL BETWEEN 7001 AND 10000  THEN TLF.TL * 1.50
            WHEN TLF.TL BETWEEN 10001 AND 20000 THEN TLF.TL * 1.40
            WHEN TLF.TL > 20000                 THEN TLF.TL * 1.35
            ELSE 0 -- NULL veya 0 degerler için eklendi (Ancak WHERE sartinda TLF.TL IS NOT NULL)
        END AS ListeFiyati,
        'TL' AS Kur,
        'ADET' AS Birim,
        ISNULL(ITM.VAT, 20) AS Kdv,
        -- Resim URL'si (KirikResimler tablosunda yoksa LOGICALREF ile olusturulur)
        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,
        -- Stok Adeti (Maksimum 10 adeti geçmeyecek sekilde)
        CASE
            WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) > 10
            THEN 10
            ELSE ISNULL((GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED), 0)
        END AS StokAdeti,
        ISNULL(MAM.KAT1, '') AS Model,
        ITM.GTIPCODE AS Kategori,
        ISNULL(MAM.KAT1, '') AS Kategori1,
        -- Sira Numarasi (Kaydin olusturulma tarihine göre)
        ROW_NUMBER() OVER (ORDER BY ITM.CAPIBLOCK_CREADEDDATE) AS SIRA,
        -- Kategori Alanlari (MALZEME_ARAC_MODELLERI tablosundan)
        ISNULL(MAM.KAT2, '') AS Kategori2,
        ISNULL(MAM.KAT3, '') AS Kategori3,
        ISNULL(MAM.KAT4, '') AS Kategori4,
        ISNULL(MAM.KAT5, '') AS Kategori5,
        ISNULL(MAM.KAT6, '') AS Kategori6,
        ISNULL(MAM.KAT7, '') AS Kategori7,
        ISNULL(MAM.KAT8, '') AS Kategori8,
        ISNULL(MAM.KAT9, '') AS Kategori9,
        ISNULL(MAM.KAT10, '') AS Kategori10,
        ISNULL(MAM.KAT11, '') AS Kategori11,
        ISNULL(MAM.KAT12, '') AS Kategori12,
        ISNULL(MAM.KAT13, '') AS Kategori13,
        ISNULL(MAM.KAT14, '') AS Kategori14
    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
        -- Kayit aktif degil (pasif)
        (ITM.ACTIVE = 0) AND
        -- TL Fiyati mevcut
        (TLF.TL IS NOT NULL) AND
        -- Belirtilen Ürün Tipi Gruplarinda degil
        ISNULL(ITM.STGRPCODE, '') NOT IN ('ARMA', 'JANT GÖBEGI', 'YAZI') AND
        -- Ürün Adi veya SPECODE2 alaninda "volvo" geçmiyor
        (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%')
) AS QQ;