CREATE   VIEW [dbo].[WEBAPI_EDG210281D]
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

/* %34,9 iskonto ? katsayi = 0.651
   Üzerine %20 kâr ? * 1.2 */
CROSS APPLY
(
    SELECT TLF.TL * 0.651 * 1.2 AS BaseMaliyet
) MX

WHERE ITM.ACTIVE = 0 
  AND TLF.TL IS NOT NULL;