Load Slater Students Script

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

BEGIN;

--DROP TABLE IF EXISTS slater_2016_load;
--DROP TABLE IF EXISTS slater_2016_load_ids;
--DROP TABLE IF EXISTS slater_2016_stat_cat_1;
--DROP TABLE IF EXISTS slater_2016_stat_cat_2;


-- Hints --
-- perm group = 88 (STUDENT - ELEMENTARY SCHOOL), org_unit = 243 (HC-SE Slater Elementary)
-- statcat1 = Gender
-- statcat2 = n/a

    --Create main student staging table.
    CREATE TABLE slater_2016_load2 (
        s_studentid text, -- Student ID # // ident_value
        s_usrname text, -- Blank for now (s+(studentID) later)
        s_barcode text,
        s_first_name text,
        s_middle_name text,
        s_last_name text,
        s_dob date,
        s_phone text,
        s_home_ou int DEFAULT 243, -- Slater Elementary
        s_profile int DEFAULT 88, -- STUDENT - ELEMENTARY SCHOOL
        s_ident_type int DEFAULT 2, -- Student ID
        s_net_access_level int DEFAULT 2,
        s_passwd text, -- Student ID for now
        --
        s_street1 text,
        s_street2 text,
        s_city text,
        s_state text,
        s_country text,
        s_postal_code text,
        --
        s_ident2 text,
        s_statcat1 text
    );

    --Copy records from your import text file
    COPY slater_2016_load2 (
        s_studentid, -- Student ID
        s_usrname,
        s_barcode,
        s_first_name,
        s_middle_name,
        s_last_name,
        s_dob,
        s_phone,
        --
        s_street1,
        s_street2,
        s_city,
        s_state,
        s_country,
        s_postal_code,
        --
        s_ident2,
        s_statcat1
        )
        FROM '/home/bmills/slater_import.csv' WITH CSV HEADER;

    --Create the usrname entry from the student id (ident_value)
    UPDATE slater_2016_load SET s_usrname='s'||s_studentid;

    -- Set password = student ID for now
    UPDATE slater_2016_load SET s_passwd=s_studentid;

    --Insert records from the slater_2016_load table into the actor.usr table.
    INSERT INTO actor.usr (
        profile,
        usrname,
        passwd,
        ident_type,
        ident_value,
        ident_value2,
        first_given_name,
        family_name,
        home_ou,
        dob,
        day_phone
        net_access_level)
    SELECT
        s_profile,
        s_usrname,
        s_passwd,
        s_ident_type,
        s_studentid,
        s_ident2,
        s_first_name,
        s_last_name,
        s_home_ou,
        s_dob,
        s_phone,
        s_net_access_level
        FROM slater_2016_load;

        -- Make our lives easier and create a table of actor.usr ids of the incoming students for later consulation
        CREATE TABLE slater_2016_load_ids(
            s_actor_usr_id int,
            s_usrname text,
            s_studentid text,
            s_create_time date DEFAULT now(),
            s_year_added text DEFAULT '2016'
        );
        -- Load it up
        INSERT INTO slater_2016_load_ids(s_actor_usr_id,s_usrname,s_studentid)
        SELECT au.id, sla.s_usrname, sla.s_studentid
        FROM slater_2016_load sla
        INNER JOIN actor.usr au
        ON sla.s_usrname=au.usrname
        ;

    --Load barcodes into actor.card table
    INSERT INTO actor.card (usr, barcode)
        SELECT slaid.s_actor_usr_id, s.s_barcode
        FROM slater_2016_load_ids slaid
        INNER JOIN slater_2016_load s
        ON s.s_usrname=slaid.s_usrname
        ;

    --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 Slater ID table
    UPDATE actor.usr au
        SET card = ac.id
        FROM actor.card ac
        WHERE ac.usr = au.id AND au.id IN (SELECT s_actor_usr_id FROM slater_2016_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,
        sla2.s_street1,
        sla2.s_street2,
        sla2.s_city,
        sla2.s_state,
        sla2.s_country,
        sla2.s_postal_code
        FROM slater_2016_load sla2
        INNER JOIN actor.usr au ON au.usrname=sla2.s_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 s_actor_usr_id FROM slater_2016_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

    -- Gender --
    CREATE TABLE slater_2016_stat_cat_1 (
        stat_cat_entry text,
        stat_cat int DEFAULT 25,
        target_usr int
    );
    INSERT INTO slater_2016_stat_cat_1 (target_usr, stat_cat_entry)
        SELECT slaid.s_actor_usr_id, s.s_statcat1
        FROM slater_2016_load_ids slaid
        INNER JOIN slater_2016_load s ON s.s_usrname=slaid.s_usrname;

    INSERT INTO actor.stat_cat_entry_usr_map (stat_cat_entry,stat_cat,target_usr)
    SELECT stat_cat_entry,stat_cat,target_usr FROM slater_2016_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;