CREATE VIEW dbo.WEBAPI_ECD210408D
AS
WITH MarkaGruplari AS (
    SELECT 
        REPLACE(
            LEFT(ITM.SPECODE4, 
                 CASE WHEN CHARINDEX('-', ITM.SPECODE4) > 0 
                      THEN CHARINDEX('-', ITM.SPECODE4) - 1 
                      ELSE LEN(ITM.SPECODE4) 
                 END
            ), 
            'I', 'I'
        ) AS Marka,
        COUNT(*) AS Adet
    FROM LG_011_ITEMS AS ITM
    WHERE ITM.ACTIVE = 0 
      AND ITM.SPECODE4 IS NOT NULL 
      AND LTRIM(RTRIM(ITM.SPECODE4)) <> ''
    GROUP BY 
        REPLACE(
            LEFT(ITM.SPECODE4, 
                 CASE WHEN CHARINDEX('-', ITM.SPECODE4) > 0 
                      THEN CHARINDEX('-', ITM.SPECODE4) - 1 
                      ELSE LEN(ITM.SPECODE4) 
                 END
            ), 
            'I', 'I'
        )
    HAVING COUNT(*) >= 50
),
MarkaIDs AS (
    SELECT 
        Marka,
        Adet,
        ROW_NUMBER() OVER (ORDER BY Marka ASC) AS MarkaID
    FROM MarkaGruplari
),
AracGruplari AS (
    SELECT 
        REPLACE(
            LEFT(ITM.SPECODE2, 
                 CASE WHEN CHARINDEX('-', ITM.SPECODE2) > 0 
                      THEN CHARINDEX('-', ITM.SPECODE2) - 1 
                      ELSE LEN(ITM.SPECODE2) 
                 END
            ), 
            'I', 'I'
        ) AS Arac,
        COUNT(*) AS Adet
    FROM LG_011_ITEMS AS ITM
    WHERE ITM.ACTIVE = 0 
      AND ITM.SPECODE2 IS NOT NULL 
      AND LTRIM(RTRIM(ITM.SPECODE2)) <> ''
    GROUP BY 
        REPLACE(
            LEFT(ITM.SPECODE2, 
                 CASE WHEN CHARINDEX('-', ITM.SPECODE2) > 0 
                      THEN CHARINDEX('-', ITM.SPECODE2) - 1 
                      ELSE LEN(ITM.SPECODE2) 
                 END
            ), 
            'I', 'I'
        )
    HAVING COUNT(*) > 2
),
AracIDs AS (
    SELECT 
        Arac,
        Adet,
        ROW_NUMBER() OVER (ORDER BY Arac ASC) AS AracID
    FROM AracGruplari
),
KategoriGruplari AS (
    SELECT 
        REPLACE(
            LEFT(ITM.GTIPCODE, 
                 CASE WHEN CHARINDEX('-', ITM.GTIPCODE) > 0 
                      THEN CHARINDEX('-', ITM.GTIPCODE) - 1 
                      ELSE LEN(ITM.GTIPCODE) 
                 END
            ), 
            'I', 'I'
        ) AS Kategori,
        COUNT(*) AS Adet
    FROM LG_011_ITEMS AS ITM
    WHERE ITM.ACTIVE = 0 
      AND ITM.GTIPCODE IS NOT NULL 
      AND LTRIM(RTRIM(ITM.GTIPCODE)) <> ''
    GROUP BY 
        REPLACE(
            LEFT(ITM.GTIPCODE, 
                 CASE WHEN CHARINDEX('-', ITM.GTIPCODE) > 0 
                      THEN CHARINDEX('-', ITM.GTIPCODE) - 1 
                      ELSE LEN(ITM.GTIPCODE) 
                 END
            ), 
            'I', 'I'
        )
),
KategoriIDs AS (
    SELECT 
        Kategori,
        Adet,
        ROW_NUMBER() OVER (ORDER BY Kategori ASC) AS KategoriID
    FROM KategoriGruplari
)
SELECT 
    ITM.LOGICALREF AS Ref, 
    ITM.CODE AS SicilKodu, 
    ITM.EXPCTGNO AS Barkod, 
    dbo.RepOzel(
        dbo.InitCap(
            { fn CONCAT(
                { fn CONCAT(
                    { fn CONCAT(ITM.SPECODE2, ' ') }, 
                    dbo.RepLhRh(ITM.NAME)
                ) }, 
                dbo.RepLhRh(ITM.NAME3)
            ) }
        )
    ) AS SicilAdi, 
    MID.MarkaID,   
    MID.Marka,     
    ITM.STGRPCODE AS UrunTipi, 
    '' AS KampanyaFiyati, 
    CASE WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) > 0 THEN 'VAR' ELSE 'YOK' END AS StokDurumu, 
    MX.BaseMaliyet AS MaliyetFiyati,   
    TLF.TL AS ListeFiyati, 
    TLF.TL 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, 
    SP.Resim2, SP.Resim3, SP.Resim4, SP.Resim5, SP.Resim6,   
    AID.AracID,   
    AID.Arac,     
    CASE WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) > 10 
         THEN 10 
         ELSE ISNULL((GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED), 0) 
    END AS StokAdeti, 
    KID.KategoriID,
    KID.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.KirikResimler AS KR 
       ON KR.LOGICALREF = ITM.LOGICALREF 
LEFT JOIN (
    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 (
        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)) <> ''
    ) SlideRN
    WHERE rn <= 5
    GROUP BY LOGICALREF
) SP ON SP.LOGICALREF = ITM.LOGICALREF
CROSS APPLY (
    SELECT CASE 
             WHEN ISNULL(NULLIF(ITM.GTIPCODE, ''), 'DIGER') = 'AYDINLATMA' 
                  THEN TLF.TL * 0.7 * 1.2 
             ELSE TLF.TL * 0.73 * 1.2 
           END AS BaseMaliyet
) MX
INNER JOIN MarkaIDs MID 
    ON REPLACE(
        LEFT(ITM.SPECODE4, 
             CASE WHEN CHARINDEX('-', ITM.SPECODE4) > 0 
                  THEN CHARINDEX('-', ITM.SPECODE4) - 1 
                  ELSE LEN(ITM.SPECODE4) 
             END
        ), 
        'I', 'I'
    ) = MID.Marka
INNER JOIN AracIDs AID
    ON REPLACE(
        LEFT(ITM.SPECODE2, 
             CASE WHEN CHARINDEX('-', ITM.SPECODE2) > 0 
                  THEN CHARINDEX('-', ITM.SPECODE2) - 1 
                  ELSE LEN(ITM.SPECODE2) 
             END
        ), 
        'I', 'I'
    ) = AID.Arac
INNER JOIN KategoriIDs KID
    ON REPLACE(
        LEFT(ITM.GTIPCODE, 
             CASE WHEN CHARINDEX('-', ITM.GTIPCODE) > 0 
                  THEN CHARINDEX('-', ITM.GTIPCODE) - 1 
                  ELSE LEN(ITM.GTIPCODE) 
             END
        ), 
        'I', 'I'
    ) = KID.Kategori
WHERE ITM.ACTIVE = 0 
  AND TLF.TL IS NOT NULL;