def export_cases(self):
reports = self.reports()
cols = ['idx'] + list(PositiveCase.attrs)
cases_by_report = {}
curs = db.cursor()
try:
execute(curs, 'SELECT report_id, %s FROM lab_cases' %
', '.join(cols))
for row in curs.fetchall():
cases = cases_by_report.setdefault(row[0], [])
cases.append(row[1:])
finally:
curs.close()
heading = ['Week Ending', 'Lab', 'Completed']
for col in cols:
heading.append(col.title())
yield heading
for report in reports:
cases = cases_by_report.get(report.report_id)
if cases:
cases.sort()
report_row = [mx_to_iso_date(report.week), report.lab,
mx_to_iso_datetime(report.completed)]
for case in cases:
row = list(report_row)
for a, v in zip(col, case):
if a == 'age':
v = float_to_age(v)
row.append(v)
yield row
def update_diags(self):
assert self.report_id
updated = False
curs = db.cursor()
try:
for td in self.test_diags:
for i, c in enumerate(td.counts):
diagnosis = TestDiags.diagnoses[i][0]
if c:
try:
td.counts[i] = c = int(c)
except ValueError:
raise ReportError(
'%s, %s count must be an integer' %
(td.name, diagnosis))
else:
td.counts[i] = c = None
if c == td.load_counts[i]:
continue
execute(
curs, 'UPDATE lab_diags SET count=%s WHERE'
' report_id=%s AND test=%s AND diagnosis=%s',
(c, self.report_id, td.name, diagnosis))
if not curs.rowcount:
execute(
curs, 'INSERT INTO lab_diags'
' VALUES (%s,%s,%s,%s)',
(self.report_id, td.name, diagnosis, c))
updated = True
finally:
curs.close()
if updated:
db.commit()
self.diag_set_initial()
def export_diags(self):
reports = self.reports()
diags_by_report = {}
curs = db.cursor()
try:
execute(
curs, 'SELECT report_id, test, diagnosis, count'
' FROM lab_diags')
for report_id, test, diagnosis, count in curs.fetchall():
diags = diags_by_report.setdefault(report_id, {})
diags[(test, diagnosis)] = count
finally:
curs.close()
heading = ['Week Ending', 'Lab', 'Completed']
for test_name, test_label in TestDiags.tests:
for diag_name, diag_label in TestDiags.diagnoses:
heading.append('%s_%s' % (test_name, diag_name))
yield heading
for report in reports:
row = [
mx_to_iso_date(report.week), report.lab,
mx_to_iso_datetime(report.completed)
]
totals = diags_by_report.get(report.report_id, {})
for test_name, test_label in TestDiags.tests:
for diag_name, diag_label in TestDiags.diagnoses:
row.append(totals.get((test_name, diag_name), ''))
yield row
def update_totals(self):
assert self.report_id
updated = False
curs = db.cursor()
try:
for tt in self.test_totals:
if tt.count:
try:
tt.count = int(tt.count)
except ValueError:
raise ReportError('%s count must be an integer' %
tt.label)
else:
tt.count = None
if tt.count == tt.load_count:
continue
execute(curs, 'UPDATE lab_totals SET count=%s'
' WHERE report_id=%s AND test=%s',
(tt.count, self.report_id, tt.name))
if not curs.rowcount:
execute(curs, 'INSERT INTO lab_totals VALUES (%s,%s,%s)',
(self.report_id, tt.name, tt.count))
updated = True
finally:
curs.close()
if updated:
db.commit()
self.totals_set_initial()
def update_totals(self):
assert self.report_id
updated = False
curs = db.cursor()
try:
for tt in self.test_totals:
if tt.count:
try:
tt.count = int(tt.count)
except ValueError:
raise ReportError('%s count must be an integer' %
tt.label)
else:
tt.count = None
if tt.count == tt.load_count:
continue
execute(
curs, 'UPDATE lab_totals SET count=%s'
' WHERE report_id=%s AND test=%s',
(tt.count, self.report_id, tt.name))
if not curs.rowcount:
execute(curs, 'INSERT INTO lab_totals VALUES (%s,%s,%s)',
(self.report_id, tt.name, tt.count))
updated = True
finally:
curs.close()
if updated:
db.commit()
self.totals_set_initial()
def update_diags(self):
assert self.report_id
updated = False
curs = db.cursor()
try:
for td in self.test_diags:
for i, c in enumerate(td.counts):
diagnosis = TestDiags.diagnoses[i][0]
if c:
try:
td.counts[i] = c = int(c)
except ValueError:
raise ReportError('%s, %s count must be an integer'
% (td.name, diagnosis))
else:
td.counts[i] = c = None
if c == td.load_counts[i]:
continue
execute(curs, 'UPDATE lab_diags SET count=%s WHERE'
' report_id=%s AND test=%s AND diagnosis=%s',
(c, self.report_id, td.name, diagnosis))
if not curs.rowcount:
execute(curs, 'INSERT INTO lab_diags'
' VALUES (%s,%s,%s,%s)',
(self.report_id, td.name, diagnosis, c))
updated = True
finally:
curs.close()
if updated:
db.commit()
self.diag_set_initial()
def export_diags(self):
reports = self.reports()
diags_by_report = {}
curs = db.cursor()
try:
execute(curs, 'SELECT report_id, test, diagnosis, count'
' FROM lab_diags')
for report_id, test, diagnosis, count in curs.fetchall():
diags = diags_by_report.setdefault(report_id, {})
diags[(test, diagnosis)] = count
finally:
curs.close()
heading = ['Week Ending', 'Lab', 'Completed']
for test_name, test_label in TestDiags.tests:
for diag_name, diag_label in TestDiags.diagnoses:
heading.append('%s_%s' % (test_name, diag_name))
yield heading
for report in reports:
row = [mx_to_iso_date(report.week), report.lab,
mx_to_iso_datetime(report.completed)]
totals = diags_by_report.get(report.report_id, {})
for test_name, test_label in TestDiags.tests:
for diag_name, diag_label in TestDiags.diagnoses:
row.append(totals.get((test_name, diag_name), ''))
yield row
def export_cases(self):
reports = self.reports()
cols = ['idx'] + list(PositiveCase.attrs)
cases_by_report = {}
curs = db.cursor()
try:
execute(curs,
'SELECT report_id, %s FROM lab_cases' % ', '.join(cols))
for row in curs.fetchall():
cases = cases_by_report.setdefault(row[0], [])
cases.append(row[1:])
finally:
curs.close()
heading = ['Week Ending', 'Lab', 'Completed']
for col in cols:
heading.append(col.title())
yield heading
for report in reports:
cases = cases_by_report.get(report.report_id)
if cases:
cases.sort()
report_row = [
mx_to_iso_date(report.week), report.lab,
mx_to_iso_datetime(report.completed)
]
for case in cases:
row = list(report_row)
for a, v in zip(col, case):
if a == 'age':
v = float_to_age(v)
row.append(v)
yield row
def p1_pay_set(dt):
dbapi.execute(sql.p1_pay_set['add_partition'] %
{'dt': datetime.datetime.strftime(dt, "%Y%m%d")})
dbapi.execute(
sql.p1_pay_set['insert'] % {
'dt': datetime.datetime.strftime(dt, "%Y%m%d"),
'dashed-dt': datetime.datetime.strftime(dt, "%Y%m%d")
})
def new_report(self):
assert self.lab
assert self.week
curs = db.cursor()
try:
execute(curs, 'INSERT INTO lab_reports (lab, week) VALUES (%s, %s)',
self.lab, iso_to_mx_date(self.week))
finally:
curs.close()
def new_report(self):
assert self.lab
assert self.week
curs = db.cursor()
try:
execute(curs,
'INSERT INTO lab_reports (lab, week) VALUES (%s, %s)',
self.lab, iso_to_mx_date(self.week))
finally:
curs.close()
def update_report(self):
assert self.report_id
if self.__monitor.check():
curs = db.cursor()
try:
execute(curs, 'UPDATE lab_reports SET completed=%s, notes=%s'
' WHERE report_id=%s',
self.completed, self.notes, self.report_id)
finally:
curs.close()
db.commit()
self.__monitor.clear()
def export_notes(self):
curs = db.cursor()
try:
execute(curs, 'SELECT week, lab, completed, notes FROM lab_reports'
' ORDER BY week, lab')
rows = curs.fetchall()
finally:
curs.close()
yield ['Week Ending', 'Lab', 'Completed', 'Notes']
for week, lab, completed, notes in rows:
yield (mx_to_iso_date(week), lab,
mx_to_iso_datetime(completed), notes)
def export_notes(self):
curs = db.cursor()
try:
execute(
curs, 'SELECT week, lab, completed, notes FROM lab_reports'
' ORDER BY week, lab')
rows = curs.fetchall()
finally:
curs.close()
yield ['Week Ending', 'Lab', 'Completed', 'Notes']
for week, lab, completed, notes in rows:
yield (mx_to_iso_date(week), lab, mx_to_iso_datetime(completed),
notes)
def update_report(self):
assert self.report_id
if self.__monitor.check():
curs = db.cursor()
try:
execute(
curs, 'UPDATE lab_reports SET completed=%s, notes=%s'
' WHERE report_id=%s', self.completed, self.notes,
self.report_id)
finally:
curs.close()
db.commit()
self.__monitor.clear()
def load_positive_cases(self):
assert self.report_id
curs = db.cursor()
try:
execute(curs, 'SELECT idx, %s FROM lab_cases WHERE report_id=%%s' %
', '.join(PositiveCase.attrs), self.report_id)
for row in curs.fetchall():
idx = row[0]
while len(self.positive_cases) <= idx:
self.add_case_page()
self.positive_cases[idx].from_db(row[1:])
finally:
curs.close()
def load_positive_cases(self):
assert self.report_id
curs = db.cursor()
try:
execute(
curs, 'SELECT idx, %s FROM lab_cases WHERE report_id=%%s' %
', '.join(PositiveCase.attrs), self.report_id)
for row in curs.fetchall():
idx = row[0]
while len(self.positive_cases) <= idx:
self.add_case_page()
self.positive_cases[idx].from_db(row[1:])
finally:
curs.close()
def reports(self):
cols = 'report_id', 'lab', 'week', 'completed'
class Report(object): __slots__ = cols
reports = []
curs = db.cursor()
try:
execute(curs, 'SELECT %s FROM lab_reports ORDER BY week, lab' %
','.join(cols))
for row in curs.fetchall():
report = Report()
for a, v in zip(cols, row):
setattr(report, a, v)
reports.append(report)
finally:
curs.close()
return reports
def load_report(self):
if not self.lab:
raise ReportError('Please select a lab')
curs = db.cursor()
try:
execute(curs, 'SELECT report_id, completed, notes'
' FROM lab_reports WHERE lab=%s AND week=%s',
self.lab, iso_to_mx_date(self.week))
row = curs.fetchone()
if not row:
return False
self.report_id, self.completed, self.notes = row
self.__monitor.clear()
return True
finally:
curs.close()
def load_report(self):
if not self.lab:
raise ReportError('Please select a lab')
curs = db.cursor()
try:
execute(
curs, 'SELECT report_id, completed, notes'
' FROM lab_reports WHERE lab=%s AND week=%s', self.lab,
iso_to_mx_date(self.week))
row = curs.fetchone()
if not row:
return False
self.report_id, self.completed, self.notes = row
self.__monitor.clear()
return True
finally:
curs.close()
def load_totals(self):
assert self.report_id
tt_by_name = {}
for tt in self.test_totals:
tt_by_name[tt.name] = tt
curs = db.cursor()
try:
execute(curs, 'SELECT test, count FROM lab_totals'
' WHERE report_id=%s', self.report_id)
for test, count in curs.fetchall():
try:
tt = tt_by_name[test]
except KeyError:
continue
tt.count = count
finally:
curs.close()
self.totals_set_initial()
def load_totals(self):
assert self.report_id
tt_by_name = {}
for tt in self.test_totals:
tt_by_name[tt.name] = tt
curs = db.cursor()
try:
execute(curs, 'SELECT test, count FROM lab_totals'
' WHERE report_id=%s', self.report_id)
for test, count in curs.fetchall():
try:
tt = tt_by_name[test]
except KeyError:
continue
tt.count = count
finally:
curs.close()
self.totals_set_initial()
def update(self, curs, report_id):
set_sql = []
args = []
for a in self.attrs:
v = getattr(self, a)
if a == 'age':
v = age_to_float(v)
set_sql.append('%s=%%s' % a)
args.append(v)
args.append(report_id)
args.append(self.idx)
execute(curs, 'UPDATE lab_cases SET %s WHERE'
' report_id=%%s AND idx=%%s'
% (', '.join(set_sql)), args)
if not curs.rowcount:
cols = list(self.attrs) + ['report_id', 'idx']
fmt = ['%s'] * len(cols)
execute(curs, 'INSERT INTO lab_cases (%s) VALUES (%s)'
% (','.join(cols), ','.join(fmt)), args)
def load_diags(self):
assert self.report_id
td_by_name = {}
for td in self.test_diags:
td_by_name[td.name] = td
curs = db.cursor()
try:
execute(curs, 'SELECT test, diagnosis, count FROM lab_diags'
' WHERE report_id=%s', self.report_id)
for test, diagnosis, count in curs.fetchall():
try:
td = td_by_name[test]
d_index = TestDiags.diagnosis_map[diagnosis]
except KeyError:
continue
td.counts[d_index] = count
finally:
curs.close()
self.diag_set_initial()
def update(self, curs, report_id):
set_sql = []
args = []
for a in self.attrs:
v = getattr(self, a)
if a == 'age':
v = age_to_float(v)
set_sql.append('%s=%%s' % a)
args.append(v)
args.append(report_id)
args.append(self.idx)
execute(
curs, 'UPDATE lab_cases SET %s WHERE'
' report_id=%%s AND idx=%%s' % (', '.join(set_sql)), args)
if not curs.rowcount:
cols = list(self.attrs) + ['report_id', 'idx']
fmt = ['%s'] * len(cols)
execute(
curs, 'INSERT INTO lab_cases (%s) VALUES (%s)' %
(','.join(cols), ','.join(fmt)), args)
def reports(self):
cols = 'report_id', 'lab', 'week', 'completed'
class Report(object):
__slots__ = cols
reports = []
curs = db.cursor()
try:
execute(
curs, 'SELECT %s FROM lab_reports ORDER BY week, lab' %
','.join(cols))
for row in curs.fetchall():
report = Report()
for a, v in zip(cols, row):
setattr(report, a, v)
reports.append(report)
finally:
curs.close()
return reports
def load_diags(self):
assert self.report_id
td_by_name = {}
for td in self.test_diags:
td_by_name[td.name] = td
curs = db.cursor()
try:
execute(
curs, 'SELECT test, diagnosis, count FROM lab_diags'
' WHERE report_id=%s', self.report_id)
for test, diagnosis, count in curs.fetchall():
try:
td = td_by_name[test]
d_index = TestDiags.diagnosis_map[diagnosis]
except KeyError:
continue
td.counts[d_index] = count
finally:
curs.close()
self.diag_set_initial()
def export_totals(self):
reports = self.reports()
totals_by_report = {}
curs = db.cursor()
try:
execute(curs, 'SELECT report_id, test, count FROM lab_totals')
for report_id, test, count in curs.fetchall():
totals_by_report.setdefault(report_id, {})[test] = count
finally:
curs.close()
heading = ['Week Ending', 'Lab', 'Completed']
for n, l in TestTotals.tests:
heading.append(n)
yield heading
for report in reports:
row = [mx_to_iso_date(report.week), report.lab,
mx_to_iso_datetime(report.completed)]
totals = totals_by_report.get(report.report_id, {})
for n, l in TestTotals.tests:
row.append(totals.get(n, ''))
yield row
def export_totals(self):
reports = self.reports()
totals_by_report = {}
curs = db.cursor()
try:
execute(curs, 'SELECT report_id, test, count FROM lab_totals')
for report_id, test, count in curs.fetchall():
totals_by_report.setdefault(report_id, {})[test] = count
finally:
curs.close()
heading = ['Week Ending', 'Lab', 'Completed']
for n, l in TestTotals.tests:
heading.append(n)
yield heading
for report in reports:
row = [
mx_to_iso_date(report.week), report.lab,
mx_to_iso_datetime(report.completed)
]
totals = totals_by_report.get(report.report_id, {})
for n, l in TestTotals.tests:
row.append(totals.get(n, ''))
yield row
def p2_pay_set(dt):
dbapi.execute(sql.p2_pay_set_daily['add_partition'] %
{'dt': datetime.datetime.strftime(dt, "%Y%m%d")})
dbapi.execute(sql.p2_pay_set_daily['insert'] %
{'dt': datetime.datetime.strftime(dt, "%Y%m%d")})
def p2_pay_set(dt):
dbapi.execute(sql.p2_pay_set_monthly['add_partition'] %
{'ym': datetime.datetime.strftime(dt, "%Y%m")})
dbapi.execute(sql.p2_pay_set_monthly['insert'] %
{'ym': datetime.datetime.strftime(dt, "%Y%m")})
def create_table():
dbapi.execute(sql.P1PaySet.create())
dbapi.execute(sql.P2PaySetDaily.create())
dbapi.execute(sql.P2PaySetMonthly.create())
def partition_and_insert(set, dt):
dbapi.execute(set.add_partition(dt))
dbapi.execute(set.insert(dt))
