Upload remaining 2018 data

hancush commented 3 years ago

Update City Colleges responding agency:

# Get the ID for the correct responding agency
select name, id from data_import_respondingagency where name ilike 'City Colleges of Chicago';

           name           | id
--------------------------+-----
 City Colleges Of Chicago | 476
(1 row)

# Update the responding agency
UPDATE payroll_unitrespondingagency AS ura
SET responding_agency_id = 476
FROM payroll_employer AS u
WHERE ura.unit_id = u.id
AND u.name ilike 'City Colleges of Chicago--%';

hancush commented 3 years ago

See for queries to remove data associated with particular employers: https://github.com/datamade/bga-payroll/issues/248#issuecomment-784499863

hancush commented 3 years ago

There is no Chicago Park District data to remove for 2018. Remove top-level City Colleges of Chicago data:

Before upload:

# remove salaries for amended unit
Unit.objects.get(slug=<SLUG>).get_salaries(year=<YEAR>)
# remove jobs without salaries
Job.objects.filter(salaries__isnull=True)

After upload:

# remove departments without jobs
Department.objects.annotate(n_employees=Count('positions__jobs')).filter(n_employees=0)
# remove people without jobs
Person.objects.annotate(n_jobs=Count('jobs')).filter(n_jobs=0)

Updated "City Colleges Of Chicago" to "City Colleges of Chicago" so employers are linked on production upload.

hancush commented 3 years ago

For the production upload:

[x] Back up database: ssh ubuntu@salary.bettergov.org sudo docker exec bga-payroll-postgres pg_dump -U postgres -Fc -O bga_payroll_relaunch > prod.dump
[x] Remove 2018 data for Cook County and City Colleges of Chicago, per https://github.com/datamade/bga-payroll/issues/511#issuecomment-794036458
[x] Update the responding agency of City Colleges school units, per https://github.com/datamade/bga-payroll/issues/511#issuecomment-793002098
[x] Concatenate the processed payroll data and upload

hancush commented 3 years ago

Mission log:

In [1]: from payroll.models import *

In [2]: cook = Unit.objects.get(name='Cook County')

In [3]: cook
Out[3]: <Unit: Cook County>

In [4]: cook.get_salaries(year=2018)
Out[4]: <QuerySet [<Salary: 27300.93 India R. Green – Cook County Adult Probation Dept. Adult Probation Officer>, <Salary: 59131.63 Marc M Hamilton – Cook County Administrative Hearing Board Director>, <Salary: 21999.68 Chiquira T Frazier – Cook County Administrative Hearing Board Admin Assistant I>, <Salary: 21866.03 Patsy L Gregory – Cook County Administrative Hearing Board Admin Assistant I>, <Salary: 21974.14 Elia M Montejano – Cook County Administrative Hearing Board Admin Assistant I>, <Salary: 21224.59 Tiffany D Brown – Cook County Administrative Hearing Board Administrative Hearings Clerk>, <Salary: 22060.05 Renee F Murphy – Cook County Administrative Hearing Board Administrative Hearings Clerk>, <Salary: 32876.94 Karla L Williams – Cook County Adult Probation Dept. Adult Probation Officer>, <Salary: 34049.43 Joseph A Carchedi – Cook County Adult Probation Dept. Adult Probation Officer>, <Salary: 63394.30 Matthew J Sobieski – Cook County Adult Probation Dept. Asst Chief Adult Probation>, <Salary: 30601.21 Krystopher A Watkins – Cook County Adult Probation Dept. Adult Probation Officer>, <Salary: 34823.67 Terri L Varrige – Cook County Adult Probation Dept. Adult Probation Officer>, <Salary: 7426.50 Le'roi J. Catlin – Cook County Adult Probation Dept. Adult Probation Officer>, <Salary: 35100.90 Janice S Anthony – Cook County Adult Probation Dept. Adult Probation Officer>, <Salary: 27201.34 Gregory T. Calhoun – Cook County Adult Probation Dept. Adult Probation Officer>, <Salary: 65079.64 Juan C Hinojosa – Cook County Adult Probation Dept. Assistant Director Pretrial>, <Salary: 32200.44 Lashonda D Collier – Cook County Adult Probation Dept. Adult Probation Officer>, <Salary: 49644.45 James B Brannigan Iv – Cook County Adult Probation Dept. Adult Probation Weapons Supv>, <Salary: 33127.44 Elizabeth A Owens – Cook County Adult Probation Dept. Adult Probation Officer>, <Salary: 50298.57 Mary E Kennedy – Cook County Adult Probation Dept. Adult Probation Weapons Supv>, '...(remaining elements truncated)...']>

In [5]: cook.get_salaries(year=2018).delete()
Out[5]: (21450, {'payroll.Salary': 21450})

In [6]: colleges = Unit.objects.get(name='City Colleges of Chicago')

In [7]: colleges.get_salaries(year=2018)
Out[7]: <QuerySet [<Salary: 41336.35 Karen M Fucinato – City Colleges of Chicago Press Office Marketing Assistant>, <Salary: 41597.64 Keith P Popiela – City Colleges of Chicago West Lawn Park Attendant (M)>, <Salary: 41597.65 Cassandra R Brown – City Colleges of Chicago Stanton Park Attendant (M)>, <Salary: 41606.96 Frank A Gallegos Jr – City Colleges of Chicago Harrison Park Attendant (M)>, <Salary: 41597.64 Gwendolyn Duncan – City Colleges of Chicago Chg Womens' Pk & Gardens Attendant (M)>, <Salary: 41597.64 Priscilla M Granadon – City Colleges of Chicago Dunham Park Attendant (M)>, <Salary: 41597.64 Reginald E Reed – City Colleges of Chicago Ogden Park Attendant (M)>, <Salary: 41597.65 Darrell J Walton – City Colleges of Chicago Fosco Park Attendant (M)>, <Salary: 41597.65 Eligio Rivera – City Colleges of Chicago Humboldt Park Attendant (M)>, <Salary: 41597.65 Ronald J Jordan – City Colleges of Chicago Armour Sqr Prk Attendant (M)>, <Salary: 41606.95 Michelle S Brogaard – City Colleges of Chicago Sauganash Park Attendant (M)>, <Salary: 41606.96 Ruby J Campbell – City Colleges of Chicago Robichaux Park Attendant (M)>, <Salary: 42323.88 Deangelo Johnson – City Colleges of Chicago LF Landscape Laborer (Maintenance)>, <Salary: 42562.25 Vincent E Patterson – City Colleges of Chicago Revenue Permits Coordinator>, <Salary: 42120.54 Ignacio J Saucedo – City Colleges of Chicago Horner Park Attendant (M)>, <Salary: 42120.54 Paul Linta – City Colleges of Chicago Calumet Park Attendant (M)>, <Salary: 42120.54 Kevin M Norris – City Colleges of Chicago Marquette Park Attendant (M)>, <Salary: 42120.54 Leo Maggio – City Colleges of Chicago Kosciuszko Park Attendant (M)>, <Salary: 42120.54 Larry Shulich – City Colleges of Chicago Revere Park Attendant (M)>, <Salary: 42323.88 Rochelle B Billingsley – City Colleges of Chicago LF Landscape Laborer (Maintenance)>, '...(remaining elements truncated)...']>

In [8]: colleges.get_salaries(year=2018).delete()
Out[8]: (12756, {'payroll.Salary': 12756})

In [9]: Job.objects.filter(salaries__isnull=True)
Out[9]: <QuerySet [<Job: Lakshmi Jha – Cook County Public Defender Assistant Public Defender IV>, <Job: Matthew Davila – City Colleges of Chicago Athletics Intern>, <Job: Sally Joyce Hill – City Colleges of Chicago City Wide Education General Day-To-Day Sub  Teacher>, <Job: Vivian F Yancy – Cook County Clerk Of Crct Crt Off.of Clerk Warehouse Records Clerk I Sr>, <Job: Michael A Gerhart – Cook County County Highway Department Motor Vehicle Driver- Rr>, <Job: Quinn E Harris – City Colleges of Chicago CW - Early College and Career Hourly>, <Job: Melanie M Li-Kastanes – Stroger Hospital Of Cook County Administrative Asst V-Cchhs>, <Job: Joi Ortiz Taylor – Cook County Department Of Corrections Inmate Discplinary Hearing Off>, <Job: Terry Anita Prewitt – City Colleges of Chicago City Wide Education General Day-To-Day Sub  Teacher>, <Job: Caitlin A Bills – Cook County Department Of Corrections Correctional Officer>, <Job: Joseph M Pinkowski – Cook County Juvenile Temporary Detent.cntr Youth Development Specialist>, <Job: Rahel Sibhatu – City Colleges of Chicago Financial Aid College Work Study Student>, <Job: Janice Helene Mazz – City Colleges of Chicago City Wide Education General Day-To-Day Sub  Teacher>, <Job: Chanda T Sanders – Cook County Juvenile Temporary Detent.cntr Assistant Team Leader Jtdc>, <Job: Bradley John Obrien – City Colleges of Chicago City Wide Education General Day-To-Day Sub  Teacher>, <Job: Dennis M Dalton – City Colleges of Chicago Florr Crew North Plumber (M)>, <Job: Justin D Webster – City Colleges of Chicago Talent Office - Citywide Day-to-Day Teacher Assistant>, <Job: Antonia Aranda – City Colleges of Chicago John Barry School Hourly - 701 to 1200 Hours>, <Job: Arthur J Anthony – Cook County Office Of The Sheriff Deputy Sheriff D2b>, <Job: Luva Dorris – City Colleges of Chicago Washington Irving School Lunchroom Attendant>, '...(remaining elements truncated)...']>

In [10]: Job.objects.filter(salaries__isnull=True).delete()
Out[10]: (22571, {'payroll.Job': 22571})

In [11]: exit()

bga_payroll_relaunch=# select name, id from data_import_respondingagency where name ilike 'City Colleges of Chicago';
           name           | id
--------------------------+-----
 City Colleges Of Chicago | 873
(1 row)

bga_payroll_relaunch=# UPDATE payroll_unitrespondingagency AS ura
bga_payroll_relaunch-# SET responding_agency_id = 873
bga_payroll_relaunch-# FROM payroll_employer AS u
bga_payroll_relaunch-# WHERE ura.unit_id = u.id
bga_payroll_relaunch-# AND u.name ilike 'City Colleges of Chicago--%';
UPDATE 8

datamade / bga-payroll

Upload remaining 2018 data #511