CREATE VIEW dbo.WEBAPI_SEN160130B
AS
WITH SlideRN AS (
    SELECT
        S.LOGICALREF,
        ROW_NUMBER() OVER (PARTITION BY S.LOGICALREF ORDER BY S.[REF]) AS rn,
        'https://slide.universaloto.com/slides/' + LTRIM(RTRIM(S.IMGNAME)) AS SlideUrl
    FROM dbo.SlaytResimleri AS S
    WHERE S.LOGICALREF IS NOT NULL
        AND S.IMGNAME IS NOT NULL
        AND LTRIM(RTRIM(S.IMGNAME)) <> ''
),
SlidePivot AS (
    SELECT
        LOGICALREF,
        MAX(CASE WHEN rn = 1 THEN SlideUrl END) AS Resim2,
        MAX(CASE WHEN rn = 2 THEN SlideUrl END) AS Resim3,
        MAX(CASE WHEN rn = 3 THEN SlideUrl END) AS Resim4,
        MAX(CASE WHEN rn = 4 THEN SlideUrl END) AS Resim5,
        MAX(CASE WHEN rn = 5 THEN SlideUrl END) AS Resim6
    FROM SlideRN
    WHERE rn <= 5
    GROUP BY LOGICALREF
)
SELECT
    ITM.LOGICALREF AS Ref,
    ITM.CODE AS SicilKodu,
    LTRIM(RTRIM(REPLACE(dbo.RepOzel(dbo.InitCap(
        { fn CONCAT({ fn CONCAT({ fn CONCAT(ITM.SPECODE2, ' ') }, dbo.RepLhRh(ITM.NAME)) }, dbo.RepLhRh(ITM.NAME3)) }
    )), '(' + dbo.RepLhRh(ITM.NAME3) + ')', ''))) AS SicilAdi,
    ITM.SPECODE4 AS Marka,
    ITM.STGRPCODE AS UrunTipi,
    '' AS KampanyaFiyati,
    CASE WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) > 0 THEN 'VAR' ELSE 'YOK' END AS StokDurumu,
    TLF.TL * 0.7 * 1.2 AS ListeFiyati,
    'TL' AS Kur,
    { fn CONCAT({ fn CONCAT('https://universaloto.com/foto/photos/', dbo.formatNumber(ITM.LOGICALREF)) }, '.jpg') } AS Resim,
    SP.Resim2, SP.Resim3, SP.Resim4, SP.Resim5, SP.Resim6,
    ITM.SPECODE2 AS Arac,
    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 Kategori1,
    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,
    ITM.CAPIBLOCK_CREADEDDATE AS Eklenme,
    ITM.CAPIBLOCK_MODIFIEDDATE AS Guncellenme,
    ISNULL(ITM.PRODUCERCODE, '') AS Producercode,
    ITM.GTIPCODE AS Kategori
FROM dbo.LG_011_ITEMS AS ITM
LEFT JOIN dbo.LV_011_08_GNTOTST AS GNT ON GNT.STOCKREF = ITM.LOGICALREF AND GNT.INVENNO = -1
LEFT JOIN dbo.UNI_TL_FIYAT AS TLF ON TLF.CARDREF = ITM.LOGICALREF
LEFT JOIN dbo.MALZEME_ARAC_MODELLERI AS MAM ON MAM.LOGREF = ITM.LOGICALREF
LEFT JOIN SlidePivot AS SP ON SP.LOGICALREF = ITM.LOGICALREF
WHERE ITM.ACTIVE = 0 AND TLF.TL IS NOT NULL;