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))