Load HRVHS Students Script

--Script for Loading HRVHS Patrons and adding Freshman/Grad Year Stat Cats--

BEGIN;

--DROP TABLE IF EXISTS hrvhs_freshman_load;
--DROP TABLE IF EXISTS hrvhs_freshman_load_ids;
--DROP TABLE IF EXISTS hrvhs_stat_cat_1;
--DROP TABLE IF EXISTS hrvhs_stat_cat_2;


-- Hints --
-- test db perm group = 16, org_unit = 246
-- statcat1 = Class Standing
-- statcat2 = Grad Year

    --Create main student staging table.
    CREATE TABLE hrvhs_freshman_load (
        h_studentid text, -- Student ID # // ident_value
        h_usrname text, -- will concat 'hrv+studentid' later
        h_first_name text,
        h_middle_name text,
        h_last_name text,
        h_dob date,
        h_home_ou int DEFAULT 214, -- Hood River High School = 246
        h_profile int DEFAULT 90, -- STUDENT - HIGH SCHOOL
        h_ident_type int DEFAULT 2, -- Student ID
        h_net_access_level int DEFAULT 2,
        h_passwd text, -- student id
        --
        h_address_type text,
        h_street1 text,
        h_street2 text,
        h_city text,
        h_state text,
        h_country text,
        h_postal_code text,
        --
        h_statcat1 text,
        h_statcat2 text
    );

    --Copy records from your import text file
    COPY hrvhs_freshman_load (
        h_studentid, -- Student ID
        h_first_name,
        h_middle_name,
        h_last_name,
        h_dob,
        --
        h_address_type,
        h_street1,
        h_street2,
        h_city,
        h_state,
        h_country,
        h_postal_code,
        --
        h_statcat1,
        h_statcat2
        )
        FROM '/home/bmills/hrvhs_freshman_load.csv' WITH CSV HEADER;

    --Create the usrname(barcode) entry from the student id (ident_value)
    UPDATE hrvhs_freshman_load SET h_usrname='hrv'||h_studentid;

    -- Set password=student ID for now
    UPDATE hrvhs_freshman_load SET h_passwd=h_studentid;

    --Insert records from the hrvhs_freshman_load table into the actor.usr table.
    INSERT INTO actor.usr (
        profile,
        usrname,
        passwd,
        ident_type,
        ident_value,
        first_given_name,
        family_name,
        home_ou,
        dob,
        net_access_level)
    SELECT
        h_profile,
        h_usrname,
        h_passwd,
        h_ident_type,
        h_studentid,
        h_first_name,
        h_last_name,
        h_home_ou,
        h_dob,
        h_net_access_level
        FROM hrvhs_freshman_load;

        -- Make our lives easier and create a table of actor.usr ids of the incoming students for later consulation
        CREATE TABLE hrvhs_freshman_load_ids(
            h_actor_usr_id int,
            h_usrname text,
            h_studentid text,
            h_create_time date DEFAULT now(),
            h_class_and_year text DEFAULT '2020 - Freshman'
        );
        -- Load it up
        INSERT INTO hrvhs_freshman_load_ids(h_actor_usr_id,h_usrname,h_studentid)
        SELECT au.id, hrvhs.h_usrname,hrvhs.h_studentid
        FROM hrvhs_freshman_load hrvhs
        INNER JOIN actor.usr au
        ON hrvhs.h_usrname=au.usrname;

    --Load barcodes into actor.card table from the usrname entries.
    INSERT INTO actor.card (usr, barcode)
        SELECT h_actor_usr_id,h_usrname
        FROM hrvhs_freshman_load_ids;

    --Update actor.usr.card field with actor.card.id to associate active card with the user:
    --Play it safe and only update when User ID is in the hrvhs id table
    UPDATE actor.usr au
        SET card = ac.id
        FROM actor.card ac
        WHERE ac.usr = au.id AND au.id IN (SELECT h_actor_usr_id FROM hrvhs_freshman_load_ids);

    --Update actor.usr_address from staging table.
    INSERT INTO actor.usr_address (usr, street1, street2, city, state, country, post_code)
        SELECT au.id,
        hrvhs.h_street1,
        hrvhs.h_street2,
        hrvhs.h_city,
        hrvhs.h_state,
        hrvhs.h_country,
        hrvhs.h_postal_code
        FROM hrvhs_freshman_load hrvhs
        INNER JOIN actor.usr au ON au.usrname=hrvhs.h_usrname;


   --Update actor.usr mailing address with id from actor.usr_address table:
    UPDATE actor.usr au
        SET mailing_address = aua.id, billing_address = aua.id
        FROM actor.usr_address aua
        WHERE au.id = aua.usr
            AND au.id IN (SELECT h_actor_usr_id FROM hrvhs_freshman_load_ids);

    --Insert Stat Cats (Freshman, 2020 Grad Date) -- "Freshman,43 | 2020,69"
    --80 (Grad Year), 81 (Class) on test server

    -- Create Scratch Tables for load
    -- Since I know of no nice way to do multiple inserts with one value dynamically changing

    --School Level--
    CREATE TABLE hrvhs_stat_cat_1 (
        stat_cat_entry text DEFAULT 'Freshman',
        stat_cat int DEFAULT 43,
        target_usr int
    );
    INSERT INTO hrvhs_stat_cat_1 (target_usr)
    SELECT h_actor_usr_id FROM hrvhs_freshman_load_ids;

    INSERT INTO actor.stat_cat_entry_usr_map (stat_cat_entry,stat_cat,target_usr)
    SELECT stat_cat_entry,stat_cat,target_usr FROM hrvhs_stat_cat_1;

    --Grad Year--
    CREATE TABLE hrvhs_stat_cat_2 (
        stat_cat_entry text DEFAULT '2020',
        stat_cat int DEFAULT 69,
        target_usr int
    );
    INSERT INTO hrvhs_stat_cat_2 (target_usr)
    SELECT h_actor_usr_id FROM hrvhs_freshman_load_ids;

    INSERT INTO actor.stat_cat_entry_usr_map (stat_cat_entry,stat_cat,target_usr)
    SELECT stat_cat_entry,stat_cat,target_usr FROM hrvhs_stat_cat_2;

    -- DONE! --

    COMMIT;