CREATE VIEW dbo.MAISUDA_JSON
AS
SELECT 
	ITM.LOGICALREF,
    ITM.CODE, 
    -- ITEMNAME_EN sütunundaki degerleri her kelimenin ilk harfi büyük olacak sekilde dönüstürüyoruz
    dbo.InitCap(dbo.RepLhRh(ITS.ITEMNAME_EN)) AS ITEMNAME_EN,
    -- GTIPCODE çevirileri
    CASE 
        WHEN ITM.GTIPCODE = 'PLASTIK AKSAM' THEN 'Plastic Components'
        WHEN ITM.GTIPCODE = 'KAPORTA' THEN 'Bodywork'
        WHEN ITM.GTIPCODE = 'SOGUTMA' THEN 'Cooling System'
        WHEN ITM.GTIPCODE = 'ELEKTRONIK' THEN 'Electronics'
        WHEN ITM.GTIPCODE = 'AYDINLATMA' THEN 'Lighting'
        WHEN ITM.GTIPCODE = 'SÜSPANSIYON' THEN 'Suspension'
        WHEN ITM.GTIPCODE = 'MEKANIK' THEN 'Mechanical Parts'
        ELSE ITM.GTIPCODE -- Diger GTIPCODE degerleri için orijinal deger korunur
    END AS GTIPCODE,
    -- STGRPCODE çevirileri
    CASE 
        WHEN ITM.STGRPCODE = 'SAFT ASKI' THEN 'Shaft Hanger'
        WHEN ITM.STGRPCODE = 'YUVA' THEN 'Housing'
        WHEN ITM.STGRPCODE = 'LAMBA' THEN 'Lamp'
        WHEN ITM.STGRPCODE = 'MANIFOLD' THEN 'Manifold'
        WHEN ITM.STGRPCODE = 'DISTRIBÜTÖR' THEN 'Distributor'
        WHEN ITM.STGRPCODE = 'POMPA' THEN 'Pump'
        WHEN ITM.STGRPCODE = 'IZALATÖR' THEN 'Insulator'
        WHEN ITM.STGRPCODE = 'VAKUM' THEN 'Vacuum'
        WHEN ITM.STGRPCODE = 'DEPO' THEN 'Tank'
        WHEN ITM.STGRPCODE = 'TAKOZ' THEN 'Mount'
        WHEN ITM.STGRPCODE = 'GAZ' THEN 'Gas'
        WHEN ITM.STGRPCODE = 'KALIPER' THEN 'Caliper'
        WHEN ITM.STGRPCODE = 'KLIPS' THEN 'Clip'
        WHEN ITM.STGRPCODE = 'KARSILIK' THEN 'Counterpart'
        WHEN ITM.STGRPCODE = 'ROLE' THEN 'Relay'
        WHEN ITM.STGRPCODE = 'BILYA' THEN 'Bearing'
        WHEN ITM.STGRPCODE = 'SET KILIT' THEN 'Lock Set'
        WHEN ITM.STGRPCODE = 'MANDAL' THEN 'Latch'
        WHEN ITM.STGRPCODE = 'SEKMAN' THEN 'Snap Ring'
        WHEN ITM.STGRPCODE = 'KAPUT' THEN 'Hood'
        WHEN ITM.STGRPCODE = 'MERKEZ' THEN 'Center'
        WHEN ITM.STGRPCODE = 'IZGARA' THEN 'Grille'
        WHEN ITM.STGRPCODE = 'ORING' THEN 'O-Ring'
        WHEN ITM.STGRPCODE = 'BODIK' THEN 'Bodikit'
        WHEN ITM.STGRPCODE = 'CONTA' THEN 'Gasket'
        WHEN ITM.STGRPCODE = 'SEPERATÖR' THEN 'Separator'
        WHEN ITM.STGRPCODE = 'SENSÖR' THEN 'Sensor'
        WHEN ITM.STGRPCODE = 'FITIL' THEN 'Seal'
        WHEN ITM.STGRPCODE = 'SALINCAK' THEN 'Control Arm'
        WHEN ITM.STGRPCODE = 'KAPAK' THEN 'Cover'
        WHEN ITM.STGRPCODE = 'TESISAT' THEN 'Wiring'
        WHEN ITM.STGRPCODE = 'SOMUN' THEN 'Nut'
        WHEN ITM.STGRPCODE = 'SAFT' THEN 'Shaft'
        WHEN ITM.STGRPCODE = 'VOLANT' THEN 'Flywheel'
        WHEN ITM.STGRPCODE = 'ZINCIR' THEN 'Chain'
        WHEN ITM.STGRPCODE = 'FREN TÜPÜ' THEN 'Brake Tube'
        WHEN ITM.STGRPCODE = 'MAHRUTI' THEN 'Hypoid Gear'
        WHEN ITM.STGRPCODE = 'BRAKET' THEN 'Bracket'
        WHEN ITM.STGRPCODE = 'SIVIC' THEN 'Switch'
        WHEN ITM.STGRPCODE = 'DINAMO' THEN 'Dynamo'
        WHEN ITM.STGRPCODE = 'ÇERCEVE' THEN 'Frame'
        WHEN ITM.STGRPCODE = 'GÖGÜS' THEN 'Dashboard'
        WHEN ITM.STGRPCODE = 'KABLO BUJI' THEN 'Spark Plug Cable'
        WHEN ITM.STGRPCODE = 'AIRBAG' THEN 'Airbag'
        WHEN ITM.STGRPCODE = 'AKS' THEN 'Axle'
        WHEN ITM.STGRPCODE = 'SENKROMEÇ' THEN 'Synchronizer'
        WHEN ITM.STGRPCODE = 'DEBRIYAJ' THEN 'Clutch'
        WHEN ITM.STGRPCODE = 'PISTON' THEN 'Piston'
        WHEN ITM.STGRPCODE = 'MIL' THEN 'Shaft'
        WHEN ITM.STGRPCODE = 'AYNA' THEN 'Mirror'
        WHEN ITM.STGRPCODE = 'FILTRE' THEN 'Filter'
        WHEN ITM.STGRPCODE = 'TAMPON' THEN 'Bumper'
        WHEN ITM.STGRPCODE = 'LASTIK' THEN 'Tire'
        WHEN ITM.STGRPCODE = 'PLAKALIK' THEN 'License Plate Holder'
        WHEN ITM.STGRPCODE = 'SUBAP' THEN 'Valve'
        WHEN ITM.STGRPCODE = 'DUY' THEN 'Socket'
        WHEN ITM.STGRPCODE = 'GERGI' THEN 'Tensioner'
        WHEN ITM.STGRPCODE = 'SIS' THEN 'Fog'
        WHEN ITM.STGRPCODE = 'SINYAL' THEN 'Signal'
        WHEN ITM.STGRPCODE = 'HALAT' THEN 'Cable'
        WHEN ITM.STGRPCODE = 'TERMOSTAT' THEN 'Thermostat'
        WHEN ITM.STGRPCODE = 'TRAVERS' THEN 'Crossmember'
        WHEN ITM.STGRPCODE = 'FAR' THEN 'Headlight'
        WHEN ITM.STGRPCODE = 'JIKLE' THEN 'Choke'
        WHEN ITM.STGRPCODE = 'ANTEN' THEN 'Antenna'
        WHEN ITM.STGRPCODE = 'PALET' THEN 'Pallet'
        WHEN ITM.STGRPCODE = 'AMPUL' THEN 'Bulb'
        WHEN ITM.STGRPCODE = 'BOBIN' THEN 'Coil'
        WHEN ITM.STGRPCODE = 'INTERCOOL' THEN 'Intercooler'
        WHEN ITM.STGRPCODE = 'OTOMATIK' THEN 'Automatic'
        WHEN ITM.STGRPCODE = 'CAM' THEN 'Glass'
        WHEN ITM.STGRPCODE = 'KOMPRESÖR' THEN 'Compressor'
        WHEN ITM.STGRPCODE = 'AMORTISÖR' THEN 'Shock Absorber'
        WHEN ITM.STGRPCODE = 'JANT GÖBEGI' THEN 'Wheel Hub'
        WHEN ITM.STGRPCODE = 'LIMITÖR' THEN 'Limiter'
        WHEN ITM.STGRPCODE = 'PANEL' THEN 'Panel'
        WHEN ITM.STGRPCODE = 'KÜTÜK' THEN 'Block'
        WHEN ITM.STGRPCODE = 'RÖLE' THEN 'Relay'
        WHEN ITM.STGRPCODE = 'KOPÇA' THEN 'Clip'
        WHEN ITM.STGRPCODE = 'KRIKO' THEN 'Jack'
        WHEN ITM.STGRPCODE = 'KIZAK' THEN 'Slider'
        WHEN ITM.STGRPCODE = 'REZIDANS' THEN 'Heater'
        WHEN ITM.STGRPCODE = 'KÖRÜK' THEN 'Boot'
        WHEN ITM.STGRPCODE = 'KABLO' THEN 'Cable'
        WHEN ITM.STGRPCODE = 'KILIT' THEN 'Lock'
        WHEN ITM.STGRPCODE = 'MODÜL' THEN 'Module'
        WHEN ITM.STGRPCODE = 'TEKER' THEN 'Wheel'
        WHEN ITM.STGRPCODE = 'YAY FREN' THEN 'Brake Spring'
        WHEN ITM.STGRPCODE = 'BASAMAK' THEN 'Step'
        WHEN ITM.STGRPCODE = 'KEÇE' THEN 'Seal'
        WHEN ITM.STGRPCODE = 'REKOR' THEN 'Fitting'
        WHEN ITM.STGRPCODE = 'GAZ KELEBEK' THEN 'Throttle Body'
        WHEN ITM.STGRPCODE = 'TASIYICI' THEN 'Carrier'
        WHEN ITM.STGRPCODE = 'DISLI' THEN 'Gear'
        WHEN ITM.STGRPCODE = 'SOGUTUCU' THEN 'Cooler'
        WHEN ITM.STGRPCODE = 'SAC' THEN 'Sheet Metal'
        WHEN ITM.STGRPCODE = 'MOTOR' THEN 'Engine'
        WHEN ITM.STGRPCODE = 'PIM KALIPER' THEN 'Caliper Pin'
        WHEN ITM.STGRPCODE = 'TORSIYON' THEN 'Torsion'
        WHEN ITM.STGRPCODE = 'ELEKTRIK' THEN 'Electric'
        WHEN ITM.STGRPCODE = 'KOL SILECEK' THEN 'Wiper Arm'
        WHEN ITM.STGRPCODE = 'ÇUBUK' THEN 'Rod'
        WHEN ITM.STGRPCODE = 'SIGORTA' THEN 'Fuse'
        WHEN ITM.STGRPCODE = 'ÇITA' THEN 'Trim'
        WHEN ITM.STGRPCODE = 'CIRCIR' THEN 'Ratchet'
        WHEN ITM.STGRPCODE = 'MEKSEFE' THEN 'Hub'
        WHEN ITM.STGRPCODE = 'TETIKLEYICI' THEN 'Trigger'
        WHEN ITM.STGRPCODE = 'BUTON' THEN 'Button'
        WHEN ITM.STGRPCODE = 'DIREKSIYON' THEN 'Steering'
        WHEN ITM.STGRPCODE = 'STOPER' THEN 'Stopper'
        WHEN ITM.STGRPCODE = 'MÜSÜR' THEN 'Sensor'
        WHEN ITM.STGRPCODE = 'YAZI' THEN 'Text'
        WHEN ITM.STGRPCODE = 'CIVATA' THEN 'Bolt'
        WHEN ITM.STGRPCODE = 'ENJEKTÖR' THEN 'Injector'
        WHEN ITM.STGRPCODE = 'ROT' THEN 'Tie Rod'
        WHEN ITM.STGRPCODE = 'PERVANE' THEN 'Propeller'
        WHEN ITM.STGRPCODE = 'DEVIRDAIM' THEN 'Water Pump'
        WHEN ITM.STGRPCODE = 'KAMERA' THEN 'Camera'
        WHEN ITM.STGRPCODE = 'KOL' THEN 'Arm'
        WHEN ITM.STGRPCODE = 'GÜNESLIK' THEN 'Sun Visor'
        WHEN ITM.STGRPCODE = 'PAÇALIK' THEN 'Mudguard'
        WHEN ITM.STGRPCODE = 'PLATIN' THEN 'Contact Breaker'
        WHEN ITM.STGRPCODE = 'PEDAL' THEN 'Pedal'
        WHEN ITM.STGRPCODE = 'KALORIFER' THEN 'Heater'
        WHEN ITM.STGRPCODE = 'AKÜLATÖR' THEN 'Accumulator'
        WHEN ITM.STGRPCODE = 'KUTU' THEN 'Box'
        WHEN ITM.STGRPCODE = 'BAKALIT' THEN 'Bakelite'
        WHEN ITM.STGRPCODE = 'PORYA' THEN 'Hub'
        WHEN ITM.STGRPCODE = 'SÜZGEÇ' THEN 'Strainer'
        WHEN ITM.STGRPCODE = 'FAN' THEN 'Fan'
        WHEN ITM.STGRPCODE = 'DAVLUMBAZ' THEN 'Mudguard'
        WHEN ITM.STGRPCODE = 'REFLEKTÖR' THEN 'Reflector'
        WHEN ITM.STGRPCODE = 'MENTESE' THEN 'Hinge'
        WHEN ITM.STGRPCODE = 'MERKEZI KILIT' THEN 'Central Lock'
        WHEN ITM.STGRPCODE = 'BIJON' THEN 'Lug Bolt'
        WHEN ITM.STGRPCODE = 'MUSLUK' THEN 'Tap'
        WHEN ITM.STGRPCODE = 'TAVAN TUTACAK' THEN 'Roof Handle'
        WHEN ITM.STGRPCODE = 'SAAT' THEN 'Clock'
        WHEN ITM.STGRPCODE = 'ÇAKMAK' THEN 'Lighter'
        WHEN ITM.STGRPCODE = 'RADYATÖR' THEN 'Radiator'
        WHEN ITM.STGRPCODE = 'PANJUR' THEN 'Grille'
        WHEN ITM.STGRPCODE = 'MAFSAL' THEN 'Joint'
        WHEN ITM.STGRPCODE = 'BORU' THEN 'Pipe'
        WHEN ITM.STGRPCODE = 'ÇATAL' THEN 'Fork'
        WHEN ITM.STGRPCODE = 'MEME' THEN 'Nozzle'
        WHEN ITM.STGRPCODE = 'KASNAK' THEN 'Pulley'
        WHEN ITM.STGRPCODE = 'TAMIR TAKIMI' THEN 'Repair Kit'
        WHEN ITM.STGRPCODE = 'BANT' THEN 'Tape'
        WHEN ITM.STGRPCODE = 'RAY' THEN 'Rail'
        WHEN ITM.STGRPCODE = 'ÜNITE' THEN 'Unit'
        WHEN ITM.STGRPCODE = 'OKUYUCU' THEN 'Reader'
        WHEN ITM.STGRPCODE = 'JANT' THEN 'Rim'
        WHEN ITM.STGRPCODE = 'ARMA' THEN 'Emblem'
        WHEN ITM.STGRPCODE = 'MUHAFAZA' THEN 'Cover'
        WHEN ITM.STGRPCODE = 'SAMANDIRA' THEN 'Float'
        WHEN ITM.STGRPCODE = 'VALF' THEN 'Valve'
        WHEN ITM.STGRPCODE = 'TERMIK' THEN 'Thermal'
        WHEN ITM.STGRPCODE = 'ZEMBEREK' THEN 'Spring'
        WHEN ITM.STGRPCODE = 'BEYIN' THEN 'ECU (Engine Control Unit)'
        WHEN ITM.STGRPCODE = 'AKÜ' THEN 'Battery'
        WHEN ITM.STGRPCODE = 'FLASÖR' THEN 'Flasher'
        WHEN ITM.STGRPCODE = 'YAY' THEN 'Spring'
        WHEN ITM.STGRPCODE = 'ÖZEL KOD 4' THEN 'Special Code 4'
        WHEN ITM.STGRPCODE = 'TURBO' THEN 'Turbo'
        WHEN ITM.STGRPCODE = 'ASKI' THEN 'Hanger'
        WHEN ITM.STGRPCODE = 'BURÇ' THEN 'Bushing'
        WHEN ITM.STGRPCODE = 'KARBÜRATÖR' THEN 'Carburetor'
        WHEN ITM.STGRPCODE = 'KANCA' THEN 'Hook'
        WHEN ITM.STGRPCODE = 'KARTER' THEN 'Oil Pan'
        WHEN ITM.STGRPCODE = 'STOP' THEN 'Stop Lamp'
        WHEN ITM.STGRPCODE = 'LED' THEN 'LED'
        WHEN ITM.STGRPCODE = 'TEL' THEN 'Wire'
        WHEN ITM.STGRPCODE = 'MARSBIYEL' THEN 'Side Skirt'
        WHEN ITM.STGRPCODE = 'TOPUZ' THEN 'Knob'
        WHEN ITM.STGRPCODE = 'MAKARA' THEN 'Pulley'
        WHEN ITM.STGRPCODE = 'SET' THEN 'Set'
        WHEN ITM.STGRPCODE = 'HORTUM' THEN 'Hose'
        WHEN ITM.STGRPCODE = 'KONTAK' THEN 'Ignition'
        WHEN ITM.STGRPCODE = 'DEFRANSIYEL' THEN 'Differential'
        WHEN ITM.STGRPCODE = 'TAPA' THEN 'Plug'
        WHEN ITM.STGRPCODE = 'DÜGME' THEN 'Button'
        WHEN ITM.STGRPCODE = 'ZENON' THEN 'Xenon'
        ELSE ITM.STGRPCODE -- Diger STGRPCODE degerleri için orijinal deger korunur
    END AS STGRPCODE,
    CASE 
        WHEN CHARINDEX(' ', ITM.SPECODE2) > 0 
        THEN CONCAT(UPPER(SUBSTRING(ITM.SPECODE2, 1, 1)), LOWER(SUBSTRING(ITM.SPECODE2, 2, LEN(ITM.SPECODE2))))
        ELSE UPPER(LEFT(ITM.SPECODE2, 1)) + LOWER(SUBSTRING(ITM.SPECODE2, 2, LEN(ITM.SPECODE2)))
    END AS SPECODE2, 
    STUFF(
        ISNULL(',' + NULLIF(MAM.KAT1, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT2, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT3, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT4, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT5, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT6, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT7, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT8, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT9, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT10, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT11, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT12, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT13, ''), '') + 
        ISNULL(',' + NULLIF(MAM.KAT14, ''), ''), 
        1, 
        1, 
        ''
    ) AS Kategoriler,
	ISNULL(PRC.PRICE, 0) AS PRICE,
	ISNULL(PRC.CURRENCY, 1) AS CURRENCY
FROM 
    [UNIVERSALDB].[dbo].[LG_011_ITEMS] AS ITM 
LEFT OUTER JOIN 
    [IHR].[dbo].[ITEMS] AS ITS ON ITS.LOGICALREF = ITM.LOGICALREF
LEFT OUTER JOIN 
    [UNIVERSALDB].[dbo].[MALZEME_ARAC_MODELLERI] AS MAM ON MAM.LOGREF = ITM.LOGICALREF
LEFT OUTER JOIN 
	[UNIVERSALDB].[dbo].[LG_011_PRCLIST] AS PRC ON PRC.CARDREF=ITM.LOGICALREF AND PRC.PTYPE=2
WHERE 
    ITM.ACTIVE = 0 AND PRC.PRICE IS NOT NULL;