SELECT & UPDATE agreement.monthly_fee

New agreements for KELA vehicles 1.4.2025 -> (https://app.clickup.com/t/2496230/TMS-6364) This is used to bulk update monthly fees in agreement when customers want to raise them.

-- STABLE
-- Nimi: <cost-center>
-- Kustannuspaikka UUID: <ccu>
-- Vanha kuukausihinta 2023: <vk23>
-- Vanha kuukausihinta 2024: <vk24>
-- Uusi kuukausihinta: <uk>
-- MONTHLY FEE
-- STABLE SELECT new agreements with old monthly_fee for TH Kela org, Uusimaa cost center
SELECT index, uuid, org_uuid, company_uuid, valid_from, valid_until, created, updated, cost_center_uuids, monthly_fee, trip_fee_percentage, deleted_state 
FROM agreement
WHERE
  deleted_state = 0
  AND org_uuid = '87057c8c-5548-4a99-a10e-d1e50385d0c5'
  AND cost_center_uuids = '<ccu>'
  AND valid_from >= '2025-04-01'
  AND monthly_fee->'units' = '<vk24>' OR monthly_fee->'units' = '<vk23>';

-- STABLE
-- Nimi: <cost-center>
-- Kustannuspaikka UUID: <ccu>
-- Vanha kuukausihinta 2023: <vk23>
-- Vanha kuukausihinta 2024: <vk24>
-- Uusi kuukausihinta: <uk>
-- MONTHLY FEE
-- UPDATE new monthly_fee of new agreements starting on 2025-04-01 
UPDATE agreement
SET monthly_fee = jsonb_set(
                     jsonb_set(
                         jsonb_set(
                             jsonb_set(monthly_fee, '{units}', '<uk>'),
                         '{serialized}', '"<uk>.00"'),
                     '{display_float}', '<uk>'),
                 '{serialized_currency}', '"<uk>.00 €"')
WHERE
  deleted_state = 0
  AND org_uuid = '87057c8c-5548-4a99-a10e-d1e50385d0c5'
  AND cost_center_uuids = '<ccu>'
  AND valid_from >= '2025-04-01'
  AND monthly_fee->'units' = '<vk24>' OR monthly_fee->'units' = '<vk23>'
RETURNING index, uuid, org_uuid, company_uuid, valid_from, valid_until, created, updated, cost_center_uuids, monthly_fee, trip_fee_percentage, deleted_state;

Another way to update: Wrong information in Kela Pirkanmaa agreements (https://app.clickup.com/t/2496230/TMS-5744)

SELECT index, uuid, org_uuid, monthly_fee->'units' AS monthly_fee_units, monthly_fee->'serialized' AS monthly_fee_serialized, monthly_fee->'display_float' AS monthly_fee_display_float, monthly_fee->'serialized_currency' AS monthly_fee_serialized_currency, cost_center_uuids FROM agreement WHERE org_uuid = '87057c8c-5548-4a99-a10e-d1e50385d0c5' AND monthly_fee->>'display_float' = '220' AND cost_center_uuids = 'bea0b09e-e33a-4a64-98e8-fb33b8edb579';

UPDATE agreement
SET monthly_fee = monthly_fee || jsonb_build_object(
    'units', 200, 
    'serialized', '200.00',  
    'display_float', 200, 
    'serialized_currency', '200.00 €'  
)
WHERE org_uuid = '87057c8c-5548-4a99-a10e-d1e50385d0c5'
  AND monthly_fee->>'display_float' = '220'
  AND cost_center_uuids = 'bea0b09e-e33a-4a64-98e8-fb33b8edb579'
RETURNING
  index, uuid, org_uuid, monthly_fee->'units' AS monthly_fee_units, monthly_fee->'serialized' AS monthly_fee_serialized, monthly_fee->'display_float' AS monthly_fee_display_float, monthly_fee->'serialized_currency' AS monthly_fee_serialized_currency, cost_center_uuids;