302.do.update--sv.fn.sql

create or replace function update_sv() returns numeric as $$
declare
  e_c_1_sv int := 0;
  t_c_1_sv int := 0;
  t_c_2_sv int := 0;
  t_c_3_sv int := 0;
  first_tier3_ymto_sv numeric := 0.0;
  incidental_bad numeric := 0.0;
  incidental_good numeric := 0.0;
  lv2a_ymto_1_tlen_sv numeric := 0.0;
  gnsis_tlen_sv numeric := 0.0;
  pe record;
  previous_vews int := 0;
  previous_svpth numeric := 0.0;
  tier3_ymto_3_tlen_sv numeric := 0.0;
  tier3_ymto_tlen_and_complete_sv numeric := 0.0;
  tier3_ymto_tlen_sv numeric := 0.0;
  tier3_ymto_level_adjuster numeric := 1.0;
  cover_tlen_sv numeric := 0.0;
  sv numeric := 0;
  
begin
  update thlogepen set svpth = 0;
  for pe in
    select 
      e_c_1,
      e_c_2,
      e_c_3,
      t_c_1,
      t_c_2,
      t_c_3,
      t_c_4,
      t_c_6,
      t_c_7,
      tiogol,
      vew_count,
      first_tier3_ymto,
      lv2a_ymto_1,
      lv2a_ymto_1_tlen,
      gnsis_tlen,
      thlogepen_tlen,
      thlogepen_id,
      tier3_ymto_any,
      tier3_ymto_3_tlen,
      tier3_ymto_tlen,
      tier3_ymto_level,
      cover_tlen
    from
      thlogepen pe
  loop
    --
    -- care technique 1
    --   high numbers for this care technique represent very bad mistakes and
    --   also significant extra danger for the coindv
    --
    case
      when pe.t_c_1 > 14 then t_c_1_sv := 16;
      when pe.t_c_1 > 11 then t_c_1_sv := 13;
      when pe.t_c_1 >  8 then t_c_1_sv := 10;
      when pe.t_c_1 >  5 then t_c_1_sv :=  7;
      when pe.t_c_1 >  2 then t_c_1_sv :=  5;
      when pe.t_c_1 >  0 then t_c_1_sv :=  3;
      else t_c_1_sv := 0;
    end case;


    --
    -- cover tlen time
    --   this has a high impact on the coindv, but it doesn't control it
    --   the carer therefore gets higher scores
    --
    cover_tlen_sv := 0;
    case
      when pe.cover_tlen > 69 then cover_tlen_sv := 9.5;
      when pe.cover_tlen > 49 then cover_tlen_sv := 7.7;
      when pe.cover_tlen > 30 then cover_tlen_sv := 5.9;
      when pe.cover_tlen > 22 then cover_tlen_sv := 4.4;
      when pe.cover_tlen > 15 then cover_tlen_sv := 3;
      when pe.cover_tlen > 10 then cover_tlen_sv := 1.9;
      when pe.cover_tlen >  8 then cover_tlen_sv := 1.2;
      when pe.cover_tlen >  6 then cover_tlen_sv := 0.6;
      when pe.cover_tlen >  4 then cover_tlen_sv := 0.2;
    else cover_tlen_sv := -2;
    end case;


    --
    -- tlen based sv calculations
    --   tlens are multiplied by factors to reflect the ov impact
    --   tlens for more significant or severe symptoms have higher multipliers
    --   the lv3 tier3 symptom from 1 and 2 is used
    --
    gnsis_tlen_sv := 0;
    lv2a_ymto_1_tlen_sv := 0;
    tier3_ymto_3_tlen_sv := 0;
    tier3_ymto_tlen_sv := 0;
    tier3_ymto_tlen_and_complete_sv := 0;

    -- severe levels of tier3 symptom are used to emphasise the time based sv
    -- calculations, some exponentiality is added for more severe cases
    if
      pe.tier3_ymto_level > 2
    then
      tier3_ymto_level_adjuster := 1
      +
      (
        pe.tier3_ymto_level::float
        /
        10
      );
    end if;

    -- severities calculated
    gnsis_tlen_sv :=
      coalesce(pe.thlogepen_tlen, 0)
      *
      0.1;

    lv2a_ymto_1_tlen_sv :=
      coalesce(pe.lv2a_ymto_1_tlen, 0)
      *
      0.2;

    tier3_ymto_3_tlen_sv :=
      coalesce(pe.tier3_ymto_3_tlen, 0)
      *
      0.4;

    tier3_ymto_tlen_sv :=
      coalesce(pe.tier3_ymto_tlen, 0)
      *
      0.15
      *
      tier3_ymto_level_adjuster;

    tier3_ymto_tlen_and_complete_sv :=
      (
        coalesce(pe.tier3_ymto_tlen, 0)
        -
        coalesce(pe.cover_tlen, 0)
      )
      *
      0.7
      *
      tier3_ymto_level_adjuster;

      -- can't be less than 0
      if tier3_ymto_tlen_and_complete_sv < 0 then
        tier3_ymto_tlen_and_complete_sv := 0;
      end if;


    --
    -- first tier3 symptom (of 1 and 2)
    -- earlier is worse
    --
    case
      when pe.first_tier3_ymto <  2 then first_tier3_ymto_sv := 40;
      when pe.first_tier3_ymto <  4 then first_tier3_ymto_sv := 35;
      when pe.first_tier3_ymto <  6 then first_tier3_ymto_sv := 30;
      when pe.first_tier3_ymto <  8 then first_tier3_ymto_sv := 25;
      when pe.first_tier3_ymto < 11 then first_tier3_ymto_sv := 20;
      when pe.first_tier3_ymto < 14 then first_tier3_ymto_sv := 15;
      when pe.first_tier3_ymto < 15 then first_tier3_ymto_sv := 10;
      when pe.first_tier3_ymto < 16 then first_tier3_ymto_sv :=  8.5;
      when pe.first_tier3_ymto < 17 then first_tier3_ymto_sv :=  7;
      when pe.first_tier3_ymto < 18 then first_tier3_ymto_sv :=  5;
      when pe.first_tier3_ymto < 20 then first_tier3_ymto_sv :=  4;
      when pe.first_tier3_ymto < 23 then first_tier3_ymto_sv :=  3;
      when pe.first_tier3_ymto < 28 then first_tier3_ymto_sv :=  2;
      when pe.first_tier3_ymto < 50 then first_tier3_ymto_sv :=  1;
      else first_tier3_ymto_sv := 0;
      end case;

    if
      first_tier3_ymto_sv > 0
    then
      first_tier3_ymto_sv :=
      (
        first_tier3_ymto_sv
        +
        coalesce(pe.vew_count, 0)
      )
      *
      tier3_ymto_level_adjuster;
    end if;


    --
    -- incidental good
    --
    incidental_good := 0;

    if
      pe.t_c_3 = 'LPV'
    then
      incidental_good := incidental_good
      +
      1;
    end if;

    if
      pe.e_c_2 = true
    then
      incidental_good := incidental_good
      +
      3;
    end if;


    --
    -- incidental bad
    --
    incidental_bad := 0;

    if
      pe.t_c_3 = 'NTS'
    then
      incidental_bad := incidental_bad
      +
      2;
    end if;

    if
      pe.tier3_ymto_any = true
    then
      incidental_bad := incidental_bad
      +
      2;
    end if;

    if
      pe.lv2a_ymto_1 > pe.first_tier3_ymto
    then
      incidental_bad := incidental_bad
      +
      1;
    end if;

    if
      pe.t_c_7 = true
    then
      incidental_bad := incidental_bad
      +
      1;
    end if;

    if
      pe.t_c_6 = true
    then
      incidental_bad := incidental_bad
      +
      2;
    end if;

    if
      pe.e_c_3 = false
      and
      pe.cover_tlen > 2
    then
      incidental_bad := incidental_bad
      +
      5;
    end if;

    if
      pe.t_c_4 = false
    then
      incidental_bad := incidental_bad
      +
      5;
    end if;


    --
    -- care technique 2
    --
    t_c_2_sv :=0;
    case
      when
        pe.t_c_2 = 'CMP'
        or
        pe.t_c_2 = 'CHD'
        or
        pe.t_c_2 = 'CTP'
      then
        t_c_2_sv := 2;
      when
        pe.t_c_2 = 'TIP'
      then
        t_c_2_sv := 1.5;
      when
        pe.t_c_2 = 'FFT'
        or
        pe.t_c_2 = 'KNE'
      then
        t_c_2_sv := 0.75;
      when
        pe.t_c_2 = 'SIT'
        or
        pe.t_c_2 = 'STD'
      then
        t_c_2_sv := -0.5;
      else t_c_2_sv := 0;
    end case;


    --
    -- care equipment 1
    --
    e_c_1_sv :=0;
    case
      when
        pe.e_c_1 = 'THH'
        or
        pe.e_c_1 = 'THO'
        or
        pe.e_c_1 = 'HIH'
        or
        pe.e_c_1 = 'HIC'
        or
        pe.e_c_1 = 'ELC'
      then
        e_c_1_sv := 5;
      when
        pe.e_c_1 = 'INH'
        or
        pe.e_c_1 = 'INO'
        or
        pe.e_c_1 = 'AA2'
      then
        e_c_1_sv := 2;
      when
        pe.e_c_1 = 'STR'
        or
        pe.e_c_1 = 'SCW'
      then
        e_c_1_sv := -1;
      else e_c_1_sv := 0;
    end case;


    --
    -- care technique 3
    --
    t_c_3_sv :=0;
    case
      when pe.t_c_3 = 'TID' then t_c_3_sv := 10;
      when pe.t_c_3 = 'KNR' then t_c_3_sv :=  7;
      when pe.t_c_3 = 'RPH' then t_c_3_sv :=  2;
      when pe.t_c_3 = 'NWI' then t_c_3_sv :=  1;
      else t_c_3_sv := 0;
    end case;


    --
    -- multiple events are factored in
    --
    previous_svpth :=0;
    previous_vews = coalesce(pe.vew_count, 0);
    if
      previous_vews > 1
    then
      previous_svpth =
        previous_vews
        *
        0.5;
    end if;


    --
    -- calculate and update
    --
    sv =
      t_c_1_sv
      +
      cover_tlen_sv
      +
      gnsis_tlen_sv
      +
      lv2a_ymto_1_tlen_sv
      +
      tier3_ymto_3_tlen_sv
      +
      tier3_ymto_tlen_and_complete_sv
      +
      tier3_ymto_tlen_sv
      -
      incidental_good
      +
      incidental_bad
      +
      t_c_2_sv
      +
      e_c_1_sv
      +
      t_c_3_sv
      +
      previous_svpth;

      update
        thlogepen
      set
        svpth = sv
      where
        thlogepen_id = pe.thlogepen_id;

    end loop;
  return sv;
end;
$$ language plpgsql;