CREATE   VIEW [dbo].[WEBAPI_TRS330406M]
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, 
       MX.BaseMaliyet 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/* 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;