-- The chargeback grind

-- This is the SQL script that updates the chargeback information to reflect
-- changes in ownership/sponsorship of machines and users in CSDB.

/* NOTE: really old versions of the Sybase chargeback grind had the clause
 *   "and u.sponsor_login = c.bill_login"
 * at the end of the "delete from tmp relation" queries.
 * According to Jerel:
 *   Needed to get rid of sponsor = bill_login matchup because this query
 *   doesn't ignore certain machine names like ex-whatever and this was
 *   leading to duplicate inserts in the next query.
 *   If they only have one sponsor where they are the prime_user, we don't
 *   want them showing up on multiple bills anyway.
 */

-- we want to be searching through csdb by default
set search_path to csdb;

-- create temporary users table to handle data translation
create temporary table userunividtmp (
  univid text not null,
  sponsor_univid text,
  sponsor_login text
);

-- set the grind to be "in progress" so race conditions don't happen
-- race conditions shouldn't happen because of postgres's ACIDity,
-- but being explicit here is a good idea
delete from chargeback_grind;
insert into chargeback_grind(inprogress) values ('t');

-- Here we go...
begin;

-- psql is weird when it comes to variables
-- Maybe it would be better to do sed substitution and pipe it to psql
\set semester '''' S ''''
\set year 2023

-- Delete people from chargeback_users for this year/semester
-- iff they have not already been paid for and they are not set to
-- ignore or they have been split with another person.
\echo 'Deleting unpaid-for users'

delete from chargeback_users u
where u.bill_year = :year and u.bill_semester = :semester
and (u.bill_percent != 100 OR (u.transfer_number IS NULL or not exists
(select * from chargeback_transfers t where
t.transfer_number = u.transfer_number and t.bill_year = u.bill_year
and t.bill_semester = u.bill_semester))) and u.ignore != 't';

-- Get all people that should be billed according to status bits
\echo 'Put all billable people in tmp relation'

insert into userunividtmp (univid, sponsor_univid, sponsor_login)
select distinct p.univid, x.univid, x.login
from people p, accounts a, people x
where 
   (p.cs_student = 1 OR p.stat_student = 1 OR p.csmajor = 1 OR
   p.labstaff = 1 OR p.program = 1 OR p.grad = 1 OR p.alum = 1 OR p.ra = 1
   OR (p.faculty = 1 AND coalesce(p.status, '') NOT LIKE '%E%') 
   OR p.vfaculty = 1 OR
   p.lecturer = 1 OR p.staff = 1 OR p.lte = 1 OR p.secretary = 1 OR
   p.secondmasters = 1 OR p.special = 1 OR p.status IS NOT NULL)
   and p.uid = a.uid
   and a.machine != 'inststat'
   and a.machine != 'ccluster'
   and a.machine != 'ncluster'
   and a.machine != 'adcluster'
   and a.machine != 'c2cluster'
   and a.machine != 'dbccluster'
   and a.machine != 'fcluster'
   and a.machine != 'pcluster'
   and a.machine != 's0cluster'
   and a.machine != 's1cluster'
   and a.machine != 'instunix'
   and a.machine != 'alumni.cs.wisc.edu'
   and a.machine != 'instnt'
   and a.machine != 'upl.cs.wisc.edu'
   and a.machine != 'rschunix'
   and a.machine NOT LIKE 'crash%.cs.wisc.edu'
   and a.machine not like '%.stat.wisc.edu'
   and a.machine not like 'SKID%'
   and a.sponsor = x.login and a.sponsor != 'orp'
   and a.account_type != 'instructional';

-- reset bill_login and bill_univid to themselves if they are faculty.
\echo 'Reset faculty to sponsor themselves'

update userunividtmp u
set sponsor_login = p.login,
  sponsor_univid = p.univid
from people p
where p.univid = u.univid and p.faculty = 1 and coalesce(p.status, '') NOT LIKE '%E%';

-- The above query may lead to multiple rows for faculty (with multiple
-- sponsors) which might cause them to show up in the UNKNOWN sponsor
-- category, therefore we should really  XXXXXXXXXXXXXXXXXX

-- Basically, users get added to the chargeback_users relations based on
-- certain criteria and then get removed from the temporary table

-- Delete people from the temporary table who have already been paid for
\echo 'Delete from tmp relation user/sponsor already paid for'

delete from userunividtmp u using chargeback_users c
where u.univid = c.user_univid and
c.bill_year = :year and c.bill_semester = :semester;

-- Delete any with sponsor = "labacct" if they have another sponsor
\echo 'Delete sponsor=labacct if they have another non-labacct sponsor'

delete from userunividtmp u
where u.sponsor_login = 'labacct' and exists (select * from userunividtmp z
where z.univid = u.univid and z.sponsor_login != 'labacct');

-- Insert into chargeback_users users with only one sponsor for all accounts
\echo 'Insert those with only one sponsor for all accounts'

insert into chargeback_users (user_univid, user_class, bill_semester,
bill_year, fund, account, udds, bill_univid, bill_login, bill_amount,
bill_percent, hidden_notes)
select distinct univid, 'UNKNOWN', :semester , :year, 'XXX', '', 'X-XX-XXXX',
   sponsor_univid, sponsor_login, 0, 100, 'autoinsert rule 1'
   from userunividtmp u
   where ((select count(*) from userunividtmp x where u.univid = x.univid) = 1
      /* Faculty may have more than one row but they are all sponsored by */
      /* themselves.  This will be turned into one row by the "distinct"  */
      /* keyword above. */
      OR (select count(*) from people p where p.univid = u.univid and
	   p.faculty = 1) = 1
      );

\echo 'Delete from temp relation those already in chargeback_users'

delete from userunividtmp u using chargeback_users c
where u.univid = c.user_univid and
c.bill_year = :year and c.bill_semester = :semester;

-- Temp relation now contains people who have multiple account sponsors
-- for their non-instructional accounts.

-- Insert into permanent relation all users who only have one sponsor
-- for accounts on machines where they are the prime_user.
\echo 'insert into permanent relation users with only one sponsor for accounts where they are the prime_user'

insert into chargeback_users (user_univid, user_class, bill_semester,
bill_year, fund, account, udds, bill_univid, bill_login, bill_amount,
bill_percent, hidden_notes)
select distinct u.univid, 'UNKNOWN', :semester, :year, 'XXX', '', 'X-XX-XXXX',
x.univid, x.login, 0, 100, 'autoinsert rule 2'
from userunividtmp u, accounts a, machines m, people p, people x
where u.univid = p.univid and p.login = m.prime_user
and p.uid = a.uid and a.machine = m.cpuname and a.sponsor = x.login
and m.cpuname not like 'crash%.cs.wisc.edu'
and (select count(distinct a2.sponsor) from machines m2, accounts a2
   where a2.uid = a.uid and a2.machine = m2.cpuname
   and m2.cpuname not like 'crash%.cs.wisc.edu'
   and m2.prime_user = p.login) = 1;

\echo 'Delete from temp relation again'

delete from userunividtmp u using chargeback_users c
where u.univid = c.user_univid and
c.bill_year = :year and c.bill_semester = :semester;

/* The tmp relation now contains people in one of the following situations:
	1) They are not the prime_user of any machine and they have more
	   than one sponsor for their non-instructional accounts.
     OR
	2) They are the prime_user for more than one machine but have
	   more than one sponsor listed for those accounts where they
	   are the prime_user.
*/

-- For the remaining people, if they get their mail at someplace other
-- than imap, set their sponsor to be the sponsor of the account where
-- they receive their mail.
\echo 'Insert the difficult ones'

insert into chargeback_users (user_univid, user_class, bill_semester,
bill_year, fund, account, udds, bill_univid, bill_login, bill_amount,
bill_percent, hidden_notes)
select distinct u.univid, 'UNKNOWN', :semester, :year, 'XXX', '', 'X-XX-XXXX',
x.univid, x.login, 0, 100, 'autoinsert rule 3'
from userunividtmp u, accounts a, aliases a2, people p, people x
where u.univid = p.univid and p.login = a2.alias
and p.uid = a.uid
and substring(a2.emaillist from position('@' in a2.emaillist)+1) = a.machine
and a.sponsor = x.login
and a.sponsor not in ('upluse', 'alumni');
/* possibly add:  and a.sponsor not in ('labacct','operator', ...????) */

\echo 'And once again delete them from tmp'

delete from userunividtmp u using chargeback_users c
where u.univid = c.user_univid and
c.bill_year = :year and c.bill_semester = :semester;

-- If they are the prime_user of only one machine sponsored by a faculty
-- member, insert them with that sponsor.  (Ignores machines where they
-- are the prime_user but the sponsor is cs-dept, grad, bobh, perryk, etc.
\echo 'Insert into permanent relation users with only one *faculty* sponsor for accounts where they are the prime_user'

select distinct u.univid as user_univid, 'UNKNOWN' as user_class,
  :semester as semester, :year as year, 'XXX' as fund, '' as account,
  'X-XX-XXXX' as udds, x.univid as bill_univid, x.login as bill_login,
  0 as bill_amount, 100 as bill_percent, 'autoinsert rule 2' as hidden_notes
from userunividtmp u, accounts a, machines m, people p, people x
where u.univid = p.univid and p.login = m.prime_user
and p.uid = a.uid and a.machine = m.cpuname and a.sponsor = x.login
and (x.faculty = 1 or x.vfaculty = 1)
and m.cpuname not like 'crash%.cs.wisc.edu'
and (select count(distinct a2.sponsor) from machines m2, accounts a2, people p2
   where a2.uid = a.uid and a2.machine = m2.cpuname
   and m2.cpuname not like 'crash%.cs.wisc.edu'
   and m2.prime_user = p.login and a2.sponsor = p2.login
   and (p2.faculty = 1 or p2.vfaculty = 1)) = 1;

insert into chargeback_users (user_univid, user_class, bill_semester,
bill_year, fund, account, udds, bill_univid, bill_login, bill_amount,
bill_percent, hidden_notes)
select distinct u.univid, 'UNKNOWN', :semester, :year, 'XXX', '', 'X-XX-XXXX',
x.univid, x.login, 0, 100, 'autoinsert rule 2'
from userunividtmp u, accounts a, machines m, people p, people x
where u.univid = p.univid and p.login = m.prime_user
and p.uid = a.uid and a.machine = m.cpuname and a.sponsor = x.login
and (x.faculty = 1 or x.vfaculty = 1)
and m.cpuname not like 'crash%.cs.wisc.edu'
and (select count(distinct a2.sponsor) from machines m2, accounts a2, people p2
   where a2.uid = a.uid and a2.machine = m2.cpuname
   and m2.cpuname not like 'crash%.cs.wisc.edu'
   and m2.prime_user = p.login and a2.sponsor = p2.login
   and (p2.faculty = 1 or p2.vfaculty = 1)) = 1;

\echo 'Delete from temp relation again.'

delete from userunividtmp u using chargeback_users c
where u.univid = c.user_univid and
c.bill_year = :year and c.bill_semester = :semester;

-- Handle the rest by hand???
-- For now, just throw them in with sponsor 'NOCLUE'
-- XXXX Maybe I should use their sponsor from last billing period, if any?
\echo 'Insert the UNKNOWN users'

insert into chargeback_users (user_univid, user_class, bill_semester,
bill_year, fund, account, udds, bill_univid, bill_login, bill_amount,
bill_percent,hidden_notes)
select distinct u.univid, 'UNKNOWN', :semester, :year, 'XXX', '', 'X-XX-XXXX',
'0000000000', 'NOCLUE', 0, 100, 'autoinsert rule 4'
from userunividtmp u;

-- debug -- print the tmp table

\echo '----- SELECT * from userunividtmp (debug) -----'
select * from userunividtmp;

-- we're done with the temporary table, drop it
drop table userunividtmp;

-- NEXT: set user_class for accounts which haven't yet been paid for

-- set to "project" project accounts

\echo 'Setting to project those marked as project accounts.'

update chargeback_users c set user_class = 'project',
hidden_notes = hidden_notes || ' CLASS: project'
from people p
where c.user_univid = p.univid and
p.uid IS NOT NULL and p.status = 'P'
and c.user_class = 'UNKNOWN' and c.bill_year = :year and c.bill_semester = :semester
and not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester) and c.ignore != 't';

-- set to "program" program accounts
\echo 'Setting to program those marked as program accounts.'

update chargeback_users c set user_class = 'program',
hidden_notes = hidden_notes || ' CLASS: program'
from people p
where c.user_univid = p.univid and
p.uid IS NOT NULL and p.program = 1
and c.user_class = 'UNKNOWN' and c.bill_year = :year and c.bill_semester = :semester
and not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester) and c.ignore != 't';

-- Emeritus faculty are courtesy users
\echo 'About to set emeritus (status = ''E'') faculty to courtesy if they are not prime_user of any machine'

update chargeback_users c set user_class = 'courtesy',
hidden_notes = hidden_notes || ' CLASS: emeritus faculty'
from people p
where c.bill_year = :year and c.bill_semester = :semester
and c.user_univid = p.univid and p.status like '%E%' and p.faculty = 1
and not exists (select * from machines m where m.prime_user = p.login)
and not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester) and c.ignore != 't';

-- if their account sponsor is rachel and they have more than one account,
-- they are user_class scout.
-- I should point out that there are no accounts in CSDB sponsored by rachel,
-- This is likely not needed anymore (PDK)
\echo 'About to set user_class = scout where sponsor is rachel and user has more than 1 account'

update chargeback_users c set user_class = 'scout',
hidden_notes = hidden_notes || ' CLASS: scout project person'
where c.bill_login = 'rachel'
and c.bill_year = :year and c.bill_semester = :semester
and c.user_class = 'UNKNOWN' and (select count(*) from
accounts a, people p where p.univid = c.user_univid
and p.uid = a.uid) > 1
and not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester) and c.ignore != 't';

-- student help are class 'studenthelp'
\echo 'About to set user_class = studenthelp for student staff'

update chargeback_users c set user_class = 'studenthelp',
hidden_notes = hidden_notes || ' CLASS: studenthelp'
from people p
where c.user_univid = p.univid and (p.status IS NOT NULL and p.status LIKE '%S%') 
and c.user_class = 'UNKNOWN' and c.bill_year = :year and c.bill_semester = :semester
and not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester) and c.ignore != 't';

-- prime users of a machine are account type "regular"
\echo 'About to set user_class = regular for prime users of machines'

update chargeback_users c set user_class = 'regular',
hidden_notes = hidden_notes || ' CLASS: prime user of machine(s)'
from people p, machines m
where c.user_univid = p.univid and p.login = m.prime_user
and c.user_class = 'UNKNOWN' and c.bill_year = :year and c.bill_semester = :semester
and not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester) and c.ignore != 't';

-- for all remaining: if they are "here", they are also a "regular"
-- user. I am guessing that "here" can be roughly equal to any of the
-- following status bits: cs_student, csmajor, grad, faculty, vfaculty,
-- lecturer, staff, lte, ta, ra, pa, OR exists in the employment relation
\echo 'Set to "regular" those that are "here" according to status or employment'

update chargeback_users c set user_class = 'regular',
hidden_notes = hidden_notes || ' CLASS: regular status and/or cs dept employment'
from people p
where c.user_univid = p.univid and
(p.cs_student = 1 or p.csmajor = 1 or p.grad = 1 or p.faculty = 1 or
p.vfaculty = 1 or p.lecturer = 1 or p.staff = 1 or p.lte = 1 or p.ta = 1
or p.ra = 1 or p.pa = 1 or 
(p.status IS NOT NULL and p.status != 'L' and p.status not like '%J%' and
p.status NOT LIKE '%P%') or
exists (select * from employment e where
   e.univid = p.univid and e.empyear = :year and e.empsemester = :semester))
and c.user_class = 'UNKNOWN' and c.bill_year = :year and c.bill_semester = :semester
and not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester) and c.ignore != 't';

-- if they have more than one account (not including inst* and alumni)
-- then they are "regular" otherwise "courtesy"
\echo 'Set to "regular" those who have more than one non-inst/alumni accounts'

update chargeback_users c set user_class = 'regular',
hidden_notes = hidden_notes || ' CLASS: more than one account = regular'
from people p
where c.user_univid = p.univid and
p.uid IS NOT NULL and (select count(*) from accounts a where
a.uid = p.uid and a.sponsor != 'orp' and a.machine not in
('alumni.cs.wisc.edu','dukat.cs.wisc.edu','upl.cs.wisc.edu','rschunix',
'ccluster','dbccluster','ncluster','adcluster','c2cluster','fcluster','pcluster','s0cluster','s1cluster')
and a.machine not like '%.stat.wisc.edu') > 1
and c.user_class = 'UNKNOWN' and c.bill_year = :year and c.bill_semester = :semester
and not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester) and c.ignore != 't';

\echo 'Set the rest as courtesy'

update chargeback_users c set user_class = 'courtesy'
where c.user_class = 'UNKNOWN' and c.bill_year = :year and c.bill_semester = :semester
and not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester) and c.ignore != 't';

-- Set users with people.status like '%c%' to courtesy
\echo 'Setting the following non-courtesy users to courtesy, based on people.status.'
\echo '   Check these and make sure they should still be courtesy:'

/* First delete any chargeback_transfers that have not been committed	*/
/* for courtesy users, otherwise the query below will *not* change	*/
/* them to courtesy.  Can't change class if they've already committed	*/
/* to paying (it has a bill number)					*/
\echo 'Deleting uncommitted transfers for users who have been changed to courtesy or offsite collaborator so the next query can change them to courtesy or offsite users.'

/* Took offsite collaborators out of this query because bart complained
 * that the selected fund (not yet paid for) kept disappearing for his
 * offsite collaborators. (CSL #296004)
 * There is *no* good way around this because of the complexities of
 * split fund transfers, etc.  so once they choose a fund, the user is
 * stuck at that class unless they unselect the fund.  The sole exception
 * is unpaid-for courtesy users:
 */
delete from chargeback_transfers t using chargeback_users u, people p
where (p.status like '%c%')
and p.univid = u.user_univid and u.bill_year = :year and u.bill_semester = :semester
and u.user_class != 'courtesy'
and t.transfer_number = u.transfer_number and t.bill_year = u.bill_year
and t.bill_semester = u.bill_semester and t.bill_number IS NULL;

update chargeback_users u set user_class = 'offsite'
from people p
where p.status like '%o%'
and p.univid = u.user_univid and u.bill_year = :year and u.bill_semester = :semester
and u.user_class != 'offsite'
and not exists (select * from chargeback_transfers t where
t.transfer_number = u.transfer_number and t.bill_year = u.bill_year
and t.bill_semester = u.bill_semester) and u.ignore != 't';

update chargeback_users u set user_class = 'courtesy'
from people p
where p.status like '%c%'
and p.univid = u.user_univid and u.bill_year = :year and u.bill_semester = :semester
and u.user_class != 'courtesy'
and not exists (select * from chargeback_transfers t where
t.transfer_number = u.transfer_number and t.bill_year = u.bill_year
and t.bill_semester = u.bill_semester) and u.ignore != 't';

-- set first year fellows
-- You DID remember to update the firstyearfellows relation, didn't you?
-- Of COURSE you did...
update chargeback_users u set user_class = '1st yr fellow'
from firstyearfellows f
where f.bill_year = :year and f.bill_semester = :semester
and u.user_univid = f.univid;

-- Reset non-faculty, non-staff users that are onleave to courtesy.
\echo 'Reset non-faculty, non-staff users that are onleave to courtesy.'

update chargeback_users u set user_class = 'courtesy'
from people p
where u.bill_year = :year and u.bill_semester = :semester
and u.user_univid = p.univid and p.faculty = 0 and p.staff = 0
and onleave = 1;

-- update NOCLUE and UNKNOWN users if there is info in the special_case table

\echo 'Apply special user table for NOCLUE/UNKNOWN bill users'
update chargeback_users u set 
user_class = s.user_class, 
bill_univid = s.bill_univid, 
bill_login = s.bill_login,
hidden_notes = 'Special Case: ' || s.note
from chargeback_special_users s 
where u.user_univid = s.user_univid 
and u.bill_year = s.bill_year 
and u.bill_semester = u.bill_semester 
and u.bill_login in ('NOCLUE','UNKNOWN') 
and s.bill_year = :year and s.bill_semester = :semester
and not exists (select * from chargeback_transfers t where
t.transfer_number = u.transfer_number and t.bill_year = u.bill_year
and t.bill_semester = u.bill_semester) and u.ignore != 't';

insert into chargeback_users (user_univid, user_class, bill_year, bill_semester, bill_login, hidden_notes, bill_univid, fund, account, udds, bill_amount, bill_percent)
select user_univid, user_class, bill_year, bill_semester, bill_login, 'Special Case: ' || note, bill_univid, 'XXX', '', 'X-XX-XXXX', 0 , 100
from chargeback_special_users
where bill_year = :year and bill_semester = :semester
  and user_univid not in (
    select user_univid
    from chargeback_users
    where bill_year = :year and bill_semester = :semester
  );

-- Set rates. 
\echo 'Set rates for users'
update chargeback_users c set bill_amount = r.bill_amount
from chargeback_users_rates r
where c.user_class = r.user_class
and c.bill_year = :year and c.bill_semester = :semester
and not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester) and c.ignore != 't';

/* 
\echo 'Checking for users with class types that do not have rates in chargeback_users_rates.'
select * from chargeback_users where user_class not in (select distinct user_class from chargeback_users_rates);
*/


/* XXXXXXXXXXX BEGIN MACHINES PROCESSING XXXXXXXXXXXXXXX */

/* IMPORTANT NOTES.  DO THIS FIRST: make sure to change all occurances	*/
/* of year and semester appropriately.  Also:				*/
/* Make sure you run the chargeback_users.sql file first because that	*/
/* relation is used to determine the bill_univid for the machine	*/
/* (machine is billed to sponsor of prime_user).			*/

/* Delete this year/semester's entries and reload them. */
/* iff they haven't been paid for already and they are.	*/
/* not set to ignore.					*/

\echo 'Deleting machine rows for this year/semester.'
/* special case added for spring 2002 */
/* where cluster was divided among    */
/* several users e.g.		      */
/* markhill.clusternodes, etc.        */
/* created by hand and we don't want  */
/* to modify/delete it.               */
/* Does not effect the creation of    */
/* "all.cluster.nodes" which is       */
/* just going to be ignored this      */
/* billing period.                    */

delete from chargeback_machines c
where c.bill_year = :year and c.bill_semester = :semester
and c.ignore != 't' 
and c.hostname not like 'clusternodes%'
and (c.transfer_number IS NULL OR not exists (select * from chargeback_transfers t where
t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester));

/* Insert all machines with CPU's into relation.	*/

/* Insert all machine's whose prime_user is in chargeback_users.	*/
/* Use the fund-account-udds from that relation.			*/
\echo 'Inserting all machines whose prime_user is in chargeback_users'

/* Unfortunately this gives us multiple rows for machines with multiple */
/* processors if the extra processors were given invnums and		*/
/* model.type = 'CPU'.  Perhaps a way around this is to only include	*/
/* invnum like 'U%' and ignore all invnum like 'C%' ?   ASK DAVID	*/
/* We don't want to ignore C machines that *should* be billed for...	*/
insert into chargeback_machines(invnum, hostname, machine_class, bill_semester,
   bill_year, fund, account, udds, prime_user_univid, prime_user_login,
   bill_univid, bill_login, bill_amount, bill_percent, hidden_notes)
select x.invnum, m.cpuname, 'UNKNOWN', :semester, :year, c.fund, c.account,
   c.udds, p.univid, m.prime_user, c.bill_univid, c.bill_login, 0, 100,
   'INSERT 1 (in chargeback_users)'
from machines m, chargeback_users c, people p, parts x, models z
where m.prime_user = p.login and p.univid = c.user_univid
and c.bill_year = :year and c.bill_semester = :semester and m.cpuname = x.cpuname
and x.modelnum = z.modelnum and z.type = 'CPU'
/* and this machine doesn't already exist for this year/semester */
and not exists (select * from chargeback_machines q where
q.hostname = m.cpuname and q.bill_semester = :semester and q.bill_year = :year)
and not exists (select * from chargeback_machines q where
q.invnum = x.invnum and q.bill_semester = :semester and q.bill_year = :year)
;

-- remove duplicates
\echo 'Deleting multiple rows for machines ending in .cs.wisc.edu (multiple CPU machines) and all machines not billed to labacct'

delete from chargeback_machines m
where m.bill_year = :year and m.bill_semester = :semester
and (m.hostname like '%.cs.wisc.edu' OR m.bill_login != 'labacct')
and m.invnum != (select max(z.invnum) from chargeback_machines z
   where z.hostname = m.hostname
      and z.bill_year = :year and z.bill_semester = :semester)
and (m.transfer_number IS NULL or not exists (select * from chargeback_transfers t where
t.transfer_number = m.transfer_number and t.bill_year = m.bill_year
and t.bill_semester = m.bill_semester)) and m.ignore != 't';

/* Insert machines whose prime_user is NOT in chargeback_users	*/
/* and assign a bogus fund-account-udds.			*/
\echo 'Inserting all machines whose prime_user is NOT in chargeback_users'

insert into chargeback_machines(invnum, hostname, machine_class, bill_semester,
  bill_year, fund, account, udds, prime_user_univid, prime_user_login,
  bill_univid, bill_login, bill_amount, bill_percent, hidden_notes)
select x.invnum, m.cpuname, 'UNKNOWN', :semester, :year, 'XXX', '',
  'X-XX-XXXX', p.univid, m.prime_user, '0000000666', 'UNKNOWN', 0, 100,
  'INSERT 2 (NOT in chargeback_users)'
from machines m, people p, parts x, models z
where m.prime_user = p.login and not exists
(select * from chargeback_users c where c.user_univid = p.univid
and c.bill_year = :year and c.bill_semester = :semester)
and m.cpuname = x.cpuname and x.modelnum = z.modelnum and z.type = 'CPU'
/* and this machine doesn't already exist for this year/semester */
and not exists (select * from chargeback_machines q where
q.hostname = m.cpuname and q.bill_semester = :semester and q.bill_year = :year);

/* for chargeback_machines that have UNKNOWN as bill_login, set	*/
/* bill_login to the sponsor of the account for the prime_user	*/
update chargeback_machines c set bill_login = a.sponsor
from accounts a, people p
where c.bill_login in ('UNKNOWN', 'NOCLUE') and c.prime_user_login = p.login
and p.uid = a.uid and a.machine = c.hostname
/* Shouldn't need to check if it was paid for since bill_login is UNKNOWN */
/* who could have possibly paid for it? */
and c.ignore != 't';

\echo 'Apply special machine table for NOCLUE/UNKNOWN bill machines'
update chargeback_machines m set 
machine_class = s.machine_class, 
bill_univid = s.bill_univid, 
bill_login = s.bill_login,
hidden_notes = 'special case table'
from chargeback_special_machines s 
where m.hostname = s.hostname 
and m.bill_year = s.bill_year 
and m.bill_semester = s.bill_semester 
and m.bill_login in ('NOCLUE','UNKNOWN') 
and s.bill_year = :year and s.bill_semester = :semester
and not exists (select * from chargeback_transfers t where
t.transfer_number = m.transfer_number and t.bill_year = m.bill_year
and t.bill_semester = m.bill_semester) and m.ignore != 't';

insert into chargeback_machines (hostname, machine_class, bill_year, bill_semester, bill_login, hidden_notes, bill_univid, fund, account, udds, bill_amount, bill_percent)
select hostname, machine_class, bill_year, bill_semester, bill_login, 'Special Case: ' || note, bill_univid, 'XXX', '', 'X-XX-XXXX', 0 , 100
from chargeback_special_machines
where bill_year = :year and bill_semester = :semester
  and hostname not in (
    select hostname
    from chargeback_machines
    where bill_year = :year and bill_semester = :semester
  );

/* Purge Reserved machines */
\echo 'Purging Reserved Machines:'
/*select * from chargeback_machines m where bill_semester = :semester and bill_year= :year and m.hostname IN (select cpuname from machines where status='Reserved'); */
delete from chargeback_machines m where bill_semester = :semester and bill_year= :year and m.hostname IN (select cpuname from machines where status='Reserved');

/* Set regular rate machines. */
\echo 'Setting "regular" rate machines'

update chargeback_machines c set machine_class = 'regular'
from machines m
where c.hostname = m.cpuname
and c.bill_year = :year and c.bill_semester = :semester
and m.billtype = '1'
/* and it hasn't already been paid for. */
and (c.transfer_number IS NULL or not exists (select * from chargeback_transfers t
where t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester))
and c.ignore != 't';

/* set minimal rate machines. */
\echo 'Setting "minimal" rate machines'

update chargeback_machines c set machine_class = 'minimal'
from machines m
where c.hostname = m.cpuname
and c.bill_year = :year and c.bill_semester = :semester
and
  (m.billtype = '2' or c.hostname in ('flank.cs.wisc.edu',
   'filet.cs.wisc.edu', 'sirloin.cs.wisc.edu', 'strip.cs.wisc.edu')
   or c.hostname like 'cow%.cs.wisc.edu'
   or c.hostname like 'cops%.cs.wisc.edu'
   or c.hostname like 'sp2-%.cs.wisc.edu'
   )
and c.machine_class = 'UNKNOWN'
/* and it hasn't already been paid for. */
and (c.transfer_number IS NULL or not exists (select * from chargeback_transfers t
where t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester))
and c.ignore != 't';

/* Set stat machines. */
\echo 'Setting "stat" rate machines'

update chargeback_machines c set machine_class = 'stat'
from machines m
where c.hostname = m.cpuname
and c.bill_year = :year and c.bill_semester = :semester
and (c.hostname like '%.stat.wisc.edu' or m.billtype = 'X')
and c.machine_class = 'UNKNOWN'
/* and it hasn't already been paid for. */
and (c.transfer_number IS NULL OR not exists (select * from chargeback_transfers t
where t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester))
and c.ignore != 't';

/* Set infrastructure machines. */
\echo 'Setting infrastructure machines'

update chargeback_machines c set machine_class = 'infrastruct'
from machines m
where c.hostname = m.cpuname
and c.bill_year = :year and c.bill_semester = :semester
and m.billtype = '4'
and c.machine_class = 'UNKNOWN'
/* and it hasn't already been paid for. */
and (c.transfer_number IS NULL OR not exists (select * from chargeback_transfers t
where t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester))
and c.ignore != 't';

/* Set instructional machines. */
\echo 'Setting instructional machines'

update chargeback_machines c set machine_class = 'inst'
from machines m
where c.hostname = m.cpuname
and c.bill_year = :year and c.bill_semester = :semester
and m.billtype = '5'
and c.machine_class = 'UNKNOWN'
/* and it hasn't already been paid for. */
and (c.transfer_number IS NULL OR not exists (select * from chargeback_transfers t
where t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester))
and c.ignore != 't';

/* Set cluster machines. */
\echo 'Setting cluster machines'

update chargeback_machines c set machine_class = 'clusternode'
from machines m
where c.hostname = m.cpuname
and c.bill_year = :year and c.bill_semester = :semester
and m.billtype = '7'
and c.machine_class = 'UNKNOWN'
/* and it hasn't already been paid for. */
and (c.transfer_number IS NULL OR not exists (select * from chargeback_transfers t
where t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester))
and c.ignore != 't';

/* Set NMI machines. */
\echo 'Setting NMI machines'

update chargeback_machines c set machine_class = 'NMI'
from machines m
where c.hostname = m.cpuname
and c.bill_year = :year and c.bill_semester = :semester
and m.billtype = '8'
and c.machine_class = 'UNKNOWN'
/* and it hasn't already been paid for. */
and (c.transfer_number IS NULL OR not exists (select * from chargeback_transfers t
where t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester))
and c.ignore != 't';

/* Set no bill machines */
\echo 'Setting "none" rate machines'

update chargeback_machines c set machine_class = 'none'
from machines m
where c.hostname = m.cpuname
and c.bill_year = :year and c.bill_semester = :semester
and m.billtype in ('3','6')
/* added type 6 for items that are set to 6 (even though they probably	*/
/* shouldn't be. */
and c.machine_class = 'UNKNOWN'
/* and it hasn't already been paid for. */
and (c.transfer_number IS NULL OR not exists (select * from chargeback_transfers t
where t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester))
and c.ignore != 't';

/* Set bill_amount based on rates relation. 	*/
\echo 'Setting bill_amount based on rates relation'

update chargeback_machines c set bill_amount = r.bill_amount
from chargeback_machines_rates r
where c.machine_class = r.machine_class
and c.bill_year = :year and c.bill_semester = :semester
/* and it hasn't already been paid for. */
and (c.transfer_number IS NULL OR not exists (select * from chargeback_transfers t
where t.transfer_number = c.transfer_number and t.bill_year = c.bill_year
and t.bill_semester = c.bill_semester))
and c.ignore != 't'
and c.hostname not like '%clusternode%';

/* Clusters are a fun time. The following query attempts to insert
 * the "clusternodes-##" meta-machines on a person's bill.
 *
 * As a fake example, koczan owns pdk-01.cs.wisc.edu through 
 * pdk-30.cs.wisc.edu, collectively known as the pdk-cluster. This
 * will make "clusternodes-30" appear on koczan's bill.
 *
 * This query *MUST* only be run for a semester's initial grind.
 * Uncomment, run the chargeback grind once, then recomment.
 */

/* This query does not account for things that have been paid already.
 * However, since this should only be run once at the start of a 
 * semeseter, it's OK. The crazy GROUP BY clause is done out of necessity.
 */
/* <CLUSTER QUERY> */

/* SKIP dparter 2023-05-03
\echo 'Inserting clusternode entries.'
insert into chargeback_machines (
  invnum, hostname, machine_class, bill_semester, bill_year, fund,
  account, udds, prime_user_univid, prime_user_login, bill_univid,
  bill_login, transfer_date, transfer_number, bill_amount,
  bill_percent, print_notes, hidden_notes
)
select 'U000666', 'clusternodes-' || count(*), 'clusternode',
  bill_semester, bill_year, fund, account, udds, prime_user_univid,
  prime_user_login, bill_univid, bill_login, transfer_date, transfer_number,
  sum(bill_amount), bill_percent, print_notes, hidden_notes
from chargeback_machines
where bill_year = :year 
  and bill_semester = :semester
  and machine_class = 'clusternode'
  and ignore != 't'
group by bill_semester, bill_year, fund, account, udds, prime_user_univid,
  prime_user_login, bill_univid, bill_login, transfer_date, transfer_number,
  bill_percent, print_notes, hidden_notes;
*/

/* </CLUSTER QUERY> */


/* 
\echo 'Checking for machines with class types that do not have rates in chargeback_machines_rates.'
select * from chargeback_machines where user_class not in (select distinct user_class from chargeback_machines_rates);
*/

/* <Jerel's old comments> */

/* XXXXXXXX  New Fall 2002   XXXXXXXXX 		*/
/* Yet ANOTHER special cluster(fuck) case	*/
/* Why does this need to change EVERY semester?	*/
/* convert cluster machines to a single "clusternodes (#)" entry	*/
/* for each faculty member paying for # cluster nodes.			*/

/* XXXXX  This DOES NOT WORK correctly. XXXXX	*/
/* It is much harder than it is worth doing	*/
/* These clusternode groups should just be	*/
/* created by hand each semester.		*/
/* As currently written, it will try to		*/
/* create a new clusternodes-# group each	*/
/* time regardless of whether or not they	*/
/* have already paid for the nodes.  In other	*/
/* words, they can pay for 32 nodes and there	*/
/* will be a paid row for clusternodes-32	*/
/* but the actual nodes themselves are still	*/
/* marked as unpaid and so it will try to	*/
/* create another clusternodes-32 row and puke	*/
/* on the duplicate.  There is no good way to	*/
/* tie the clusternode-# row to the actual rows	*/
/* from which it was derived.			*/
/* I'm not sure how to do this right now, but	*/
/* this code needs to be commented out for now	*/
/* and cluster nodes completely ignored (and	*/
/* done by hand.)				*/
/* UPDATE:  Actually I think this works just 	*/
/* fine for the VERY FIRST RUN of a new billing	*/
/* period but should then be commented out.	*/

/* 
 * insert into chargeback_machines(invnum, hostname, machine_class,
 * bill_semester, bill_year, fund, account, udds, prime_user_univid,
 * prime_user_login, bill_univid, bill_login, transfer_date,
 * transfer_number, bill_amount, bill_percent, print_notes,
 * hidden_notes)
 * select distinct "U000666", "clusternodes-" + rtrim(convert(char(5),count(*))),
 *  "clusternode", c.bill_semester, c.bill_year, c.fund, c.account, c.udds,
 *  c.prime_user_univid, c.prime_user_login, c.bill_univid, c.bill_login,
 *  c.transfer_date, c.transfer_number, sum(c.bill_amount), c.bill_percent,
 *  c.print_notes, c.hidden_notes 
 *  from chargeback_machines c
 *  where c.bill_year = @year and c.bill_semester = @semester and
 *  c.machine_class = "clusternode"
 *  and c.ignore != 't' and (c.transfer_number IS NULL OR not exists
 *  (select * from chargeback_transfers t where t.transfer_number =
 *   c.transfer_number and t.bill_year = c.bill_year and t.bill_semester =
 *   c.bill_semester))
 *  group by c.bill_login
 *  having c.bill_year = @year and c.bill_semester = @semester and
 *  c.machine_class = "clusternode"
 *  and c.ignore != 't' and (c.transfer_number IS NULL OR not exists
 *     (select * from chargeback_transfers t where t.transfer_number =
 *      c.transfer_number and t.bill_year = c.bill_year and t.bill_semester =
 *      c.bill_semester))
 */

/* insert one row for all cluster machines unless it already exists	*/
/* (and was therefore paid for or had fund selected).			*/

/* insert into chargeback_machines(invnum, hostname, machine_class,
 *  bill_semester, bill_year, fund, account, udds, prime_user_univid,
 *  prime_user_login, bill_univid, bill_login, transfer_date,
 *  transfer_number, bill_amount, bill_percent, print_notes,
 *  hidden_notes)
 *  select "U000666", "all.cluster.nodes", "minimal",
 *  @semester, @year, c.fund, c.account, c.udds, p.univid,
 *  "cluster", c.bill_univid, c.bill_login, NULL,
 *  NULL, 0, 100, NULL,
 *  "INSERT mega-cluster"
 *from chargeback_users c, people p
 *where p.login = "cluster" and p.univid = c.user_univid
 *and c.bill_year = @year and c.bill_semester = @semester
*/
/* and this machine doesn't already exist for this year/semester */
/* and not exists (select * from chargeback_machines q where
 *q.hostname = "all.cluster.nodes" and q.bill_semester = @semester
 *and q.bill_year = @year)
 *
*/

/* Set bill_amount for megacluster to sum of minimal rate cluster machines */
/* update chargeback_machines set bill_amount = 
 *  isnull(
 *  (select sum(isnull(x.bill_amount,0))
 *  from chargeback_machines x where x.hostname != "all.cluster.nodes"
 *  and x.prime_user_login = "cluster" and x.machine_class = "minimal"
 *  and x.bill_year = @year and x.bill_semester = @semester),0)
 *  where bill_year = @year and bill_semester = @semester
 *  and hostname = "all.cluster.nodes" and transfer_number IS NULL
 */

/* select individual cluster machines not already paid for */
/* select substring (hostname,1,30), bill_login from chargeback_machines
 * where bill_year = @year and bill_semester = @semester
 *    and machine_class = "clusternode" and invnum != "U000666"
 *    and transfer_number IS NULL and ignore != 't'
 */

/* </Jerel's old notes> */

/* Delete individual cluster machines not already paid for */
delete from chargeback_machines
where bill_year = :year and bill_semester = :semester
   and machine_class = 'clusternode' and invnum != 'U000666'
   and transfer_number IS NULL and ignore != 't';

/* Delete minimal rate cluster machines except the megacluster. */
/* delete from chargeback_machines
 * where bill_year = @year and bill_semester = @semester
 *  and prime_user_login = "cluster" and machine_class = "minimal"
 *  and transfer_number IS NULL
 */

delete from chargeback_grind;

\echo '----- SELECT USERS AND MACHINES WITH UNKNOWN SPONSOR -----'

select p.login, u.bill_login, bill_amount from chargeback_users u, people p
where u.bill_login in ('NOCLUE','UNKNOWN')
and u.user_class != 'courtesy' and u.user_univid = p.univid
and u.bill_year = :year and u.bill_semester = :semester order by login;

select hostname, bill_login, bill_amount
from chargeback_machines
where bill_login in ('NOCLUE','UNKNOWN')
and machine_class != 'courtesy'
and bill_year = :year and bill_semester = :semester order by hostname;

commit;

\echo 'make sure to add cluster nodes'
