CREATE VIEW dbo.WEBAPI_HSA340686I
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 CROSS APPLY
                                  (SELECT        CASE WHEN ISNULL(NULLIF (ITM.GTIPCODE, ''), 'DIGER') = 'AYDINLATMA' THEN TLF.TL * 0.7 * 1.2 ELSE TLF.TL * 0.7 * 1.2 END AS BaseMaliyet) MX
     WHERE        ITM.ACTIVE = 0 AND TLF.TL IS NOT NULL AND ITM.SPECODE2 IN ('BYD','CHERY','JAECOO','SKYWELL','MG','NISSAN','MITSUBISHI') OR ITM.STGRPCODE IN ('FAR','STOP')