def setParent(ix, parent_ix): db = connect.connect() cursor = db.cursor() query = "update forum set parent_ix=%s where ix=%s" cursor.execute(query, (parent_ix, ix)) db.commit() cursor.close()
def get_course_dates(courseno): db = connect.connect() cursor = db.cursor() cursor.execute(DATE_QUERY, (courseno, )) row = cursor.fetchone() cursor.close() return row
def modify_pt_appt(aprix, serialno, practix, length, code0, code1="", code2="", note="", datespec="", flag1=80, flag0=1, flag2=0, flag3=0, flag4=0): ''' modifies the apr table by updating an existing appt ''' db = connect() cursor = db.cursor() changes = '''practix=%d,code0="%s",code1="%s",code2="%s",note="%s", length=%d,flag0=%d,flag1=%d,flag2=%d,flag3=%d,flag4=%d,datespec="%s"''' % ( practix, code0, code1, code2, note, length, flag0, flag1, flag2, flag3, flag4, datespec) fullquery = 'update apr set %s where serialno=%d and aprix=%d' % ( changes, serialno, aprix) result = True try: cursor.execute(fullquery) db.commit() except Exception as ex: print "exception in appointments.modify_pt_appt ", ex result = False cursor.close() # db.close() return result
def add(sno, cset, dent, trtid, t_dict, fee, ptfee, tx_hashes): ''' add a row to the daybook table ''' if trtid in (0, None): LOGGER.warning("no clinician login - daybook will contain junk!") db = connect.connect() cursor = db.cursor() values = (sno, cset, dent, trtid, t_dict["diagn"], t_dict["perio"], t_dict["anaes"], t_dict["misc"], t_dict["ndu"], t_dict["ndl"], t_dict["odu"], t_dict["odl"], t_dict["other"], t_dict["chart"], fee, ptfee, 0) LOGGER.debug('updating daybook with the following values: ' '%s %s %s %s %s %s %s %s' % ( sno, cset, dent, trtid, t_dict, fee, ptfee, 0)) cursor.execute(QUERY, values) daybook_id = db.insert_id() for tx_hash in tx_hashes: LOGGER.debug("%s %s %s" % (HASH_QUERY, daybook_id, tx_hash)) cursor.execute(HASH_QUERY, (daybook_id, tx_hash)) cursor.close()
def deletePost(ix): db = connect.connect() cursor = db.cursor() query = "update forum set open=False where ix=%s" cursor.execute(query, (ix,)) db.commit() cursor.close()
def update_database(self, xml, clinician_id): db = connect() cursor = db.cursor() result = cursor.execute(UPDATE_QUERY, (xml, clinician_id)) cursor.close() self._books = {} # forget any loaded books return result
def getBlocks(adate, dent): ''' get emergencies and blocked bits for date,dent ''' db = connect() cursor = db.cursor() query = ('SELECT start, end FROM aday ' 'WHERE adate=%s and apptix=%s AND (flag=1 OR flag=2)') values = (adate, dent) cursor.execute(query, values) retarg = cursor.fetchall() query = "" if retarg != (): query = ('SELECT start, end, 0, name, "block", "" FROM aslot ' 'WHERE adate=%s and apptix=%s AND flag0=-128 and name!="LUNCH" ' 'ORDER BY start') cursor.execute(query, values) results = cursor.fetchall() retarg = convertResults(results) cursor.close() return retarg
def make_appt(make_date, apptix, start, end, name, serialno, code0, code1, code2, note, flag0, flag1, flag2, flag3): ''' this makes an appointment in the aslot table a trigger in the mysql database checks to see if the appointment clashes with any already made (useful in multi client setups!) ''' db = connect() cursor = db.cursor() query = '''INSERT INTO aslot (adate,apptix,start,end,name,serialno, code0,code1,code2,note,flag0,flag1,flag2,flag3) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''' values = (make_date, apptix, start, end, name, serialno, code0, code1, code2, note, flag0, flag1, flag2, flag3) result = False try: result = cursor.execute(query, values) except OperationalError as exc: LOGGER.exception("couldn't insert into aslot %s %s %s serialno %d"% ( make_date,apptix,start,serialno)) cursor.close() return result
def get_memos(serialno): db = connect() if localsettings.station == "surgery": values = (serialno, "surg") elif localsettings.station == "reception": values = (serialno, "rec") else: values = (serialno, "all") cursor = db.cursor() cursor.execute(QUERY, values) rows = cursor.fetchall() cursor.close() for row in rows: memo = Memo() memo.ix = row[0] memo.serialno = row[1] memo.author = row[2] memo.type = row[3] memo.mdate = row[4] memo.setMessage(row[5]) memo.open = True yield memo
def day_summary(adate, dent): ''' gets start,finish and booked appointments for this date returned as (start,fin,appts) ''' db = connect() cursor = db.cursor() #--fist get start date and end date query = '''SELECT start, end FROM aday WHERE adate=%s and (flag=1 or flag=2) and apptix=%s''' values = (adate, dent) cursor.execute(query, values) daydata = cursor.fetchall() retarg = () #--now get data for those days so that we can find slots within if daydata != (): query = ('SELECT start, end, serialno, name, char(flag1), ' 'concat(code0, " ", code1," ", code2) FROM aslot ' 'WHERE adate = %s and apptix = %s AND flag0!=-128 ' 'ORDER BY start') cursor.execute(query, values) results = cursor.fetchall() retarg = convertResults(results) cursor.close() return retarg
def transfer(sno): print "transferring notes for serialnos %s"% sno, notes_dict = get_notes(sno) query = '''insert into formatted_notes (serialno, ndate, op1 , op2 , ntype, note) values (%s, %s, %s, %s, %s, %s)''' values = [] for key in notes_dict: date, ops = key op2=None if "/" in ops: op1, op2 = ops.split("/") else: op1=ops for ntype, note in notes_dict[key]: values.append((sno, date, op1, op2, ntype, note)) if values: db = connect.connect() cursor = db.cursor() rows = cursor.executemany(query, values) print "%d rows of notes inserted"% rows cursor.close() db.commit() else: print "no notes inserted"
def made_appt_to_proposed(appt): ''' modifies the apr table, when an appointment has been postponed, but not totally cancelled ''' db = connect() cursor = db.cursor() result = False if appt.aprix == "UNKNOWN": query = '''select aprix from apr WHERE serialno=%s AND adate=%s and practix=%s and atime=%s ''' values = (appt.serialno, appt.date, appt.dent, appt.atime) if not cursor.execute(query, values): LOGGER.warning("unable to get aprix from apr for %s" % appt) return False appt.aprix = cursor.fetchone()[0] query = '''UPDATE apr SET adate=NULL, atime=NULL WHERE serialno=%s AND aprix=%s''' values = (appt.serialno, appt.aprix) try: result = cursor.execute(query, values) db.commit() except Exception as ex: LOGGER.exception("appointments.made_appt_to_proposed") cursor.close() return True
def delete_appt_from_apr(appt): ''' this deletes an appointment from the apr table ''' db = connect() cursor = db.cursor() result = False query = '''DELETE FROM apr WHERE serialno=%s AND practix=%s ''' values = [appt.serialno, appt.dent] if appt.aprix != "UNKNOWN": query += 'AND aprix=%s' values.append(appt.aprix) else: if appt.date is None: query += ' and adate is NULL' else: query += ' and adate =%s' values.append(appt.date) if appt.atime is None: query += ' and atime is NULL' else: query += ' and atime =%s' values.append(appt.atime) try: result = cursor.execute(query, tuple(values)) db.commit() except Exception as ex: print "exception in appointments.delete_appt_from_apr ", ex cursor.close() return result
def transferData(self): ''' move data into the new tables ''' db = connect.connect() cursor = db.cursor() cursor.execute('lock tables patients read, exemptions write') cursor.execute('select serialno, exmpt, exempttext from patients') rows = cursor.fetchall() query = '''insert into exemptions (serialno, exemption, exempttext) values (%s, %s, %s)''' values = [] for row in rows: if row[1] != "" or row[2] != "": values.append(row) cursor.executemany(query, values) db.commit() cursor.execute("unlock tables") cursor.close() db.close() return True
def getWorkingDents(adate, dents=(0,), include_non_working=True): ''' dentists are part time, or take holidays...this proc takes a date, and optionally a tuple of dents then checks to see if they are flagged as off that day ''' db = connect() cursor = db.cursor() if 0 in dents: cond = "AND apptix != 0 " values = (adate,) else: cond = "and (" + "apptix=%s or " * (len(dents) - 1) + "apptix=%s ) " values = (adate,) + dents if not include_non_working: cond += " AND (flag=1 or flag=2)" query = 'SELECT apptix,start,end,memo,flag FROM aday WHERE adate=%s ' \ + cond cursor.execute(query, values) rows = cursor.fetchall() cursor.close() # originally I just return the rows here... for apptix, start, end, memo, flag in rows: d_day = DentistDay(apptix) d_day.start = start d_day.end = end d_day.memo = memo d_day.flag = bool(flag) yield d_day
def paymenttaken(sno, name, dent, csetyp, cash, cheque, card, sundry_cash, sundry_cheque, sundry_card, hdp, other, refund): ''' called when a payment has been taken at the desk ''' if csetyp[:1] == "N": codes = (1, 3, 5, 14, 15, 17, 21, 24, 125) else: codes = (2, 4, 6, 14, 15, 17, 21, 24, 125) queries = [] for i, amount in enumerate( (cash, cheque, card, sundry_cash, sundry_cheque, sundry_card, hdp, other, refund) ): if amount != 0: queries.append(''' insert into cashbook set cbdate = date(NOW()), ref="%06d", linkid=0, descr="%s", code=%d, dntid=%d, amt=%d '''%(sno, name, codes[i], dent, amount)) if queries != []: db = connect() cursor = db.cursor() dbOK = True for query in queries: dbOK = dbOK and cursor.execute(query) db.commit() cursor.close() #db.close() return dbOK
def getpatients(conditions="", values=()): ''' returns patients with a recall between the two dates ''' assert isinstance(conditions, bytes), "conditions must be a string" assert isinstance(values, tuple), "values must be a tuple" query = RECALL_QUERY.replace("CONDITIONS", conditions) db = connect() cursor = db.cursor() cursor.execute(query, values) rows = cursor.fetchall() cursor.close() patients = [] letterno = 1 patient = None for row in rows: prev_patient = patient patient = RecalledPatient(letterno, row) if patient == prev_patient: letterno -= 1 patient.letterno = letterno patient.grouped = True patients[-1].grouped = True letterno += 1 patients.append(patient) return patients
def create_alter_tables(self): ''' execute the above commands NOTE - this function may fail depending on the mysql permissions in place ''' db = connect.connect() db.autocommit(False) cursor = db.cursor() sucess = False try: i, commandNo = 0, len(SQLSTRINGS) for sql_string in SQLSTRINGS: try: cursor.execute(sql_string) except connect.GeneralError as e: print "FAILURE in executing sql statement", e print "erroneous statement was ", sql_string if 1060 in e.args: print "continuing, as column already exists issue" self.progressSig( 2 + 70 * i / commandNo, sql_string[:40] + "...") sucess = True except Exception as e: print "FAILURE in executing sql statements", e db.rollback() if sucess: db.commit() db.autocommit(True) else: raise UpdateException("couldn't execute all statements!")
def set_appt_memo(self, memo): LOGGER.debug("BriefPatient.set_appt_memo(%s" % memo) db = connect.connect() cursor = db.cursor() query = 'replace into appt_prefs (serialno, note) values (%s, %s)' cursor.execute(query, (self.serialno, memo)) cursor.close()
def load_values(self): db = connect() cursor = db.cursor() cursor.execute(QUERY, (self.ix,)) cbdate, ref, descr, code, dntid, amt = cursor.fetchone() cursor.close() self.serialno_le.setText(ref) self.patient_le.setText(descr) self.date_edit.setDate(cbdate) self.dentist_cb.setCurrentIndex(0) try: pos = localsettings.activedent_ixs.index(dntid) except ValueError: pos = -1 self.dentist_cb.setCurrentIndex(pos) code_str = localsettings.cashbookCodesDict.get(code) self.code_cb.setCurrentIndex(self.codestrings.index(code_str)) pounds = amt // 100 pence = amt % 100 double_val = float("%s.%s" % (pounds, pence)) self.amount_sb.setValue(double_val) self.check_enable()
def extend(dents, startdate, enddate): ''' inserts new days into the aday table for dents this is like buying next year's diary ''' delta = datetime.timedelta(days=1) query = '''insert into aday (adate, apptix, start, end, flag, memo) values (%s, %s, %s, %s, %s, %s)''' db = connect() cursor = db.cursor() for dent in dents: curdate = startdate while curdate <= enddate: values = (curdate, dent, 0, 0, 0, "") try: if cursor.execute(query, values): print "successfully added %s for dent %s"% (curdate, dent) except IntegrityError: print "%s already present for dent %s"% (curdate, dent) curdate+=delta cursor.close() db.commit() return True
def modify_aslot_appt(moddate, apptix, start, serialno, code0, code1, code2, note, flag1, flag0, flag2, flag3): ''' this modifies an appointment in the aslot table ''' db = connect() cursor = db.cursor() changes = '''code0="%s",code1="%s",code2="%s",note="%s",flag0=%d, flag1=%d,flag2=%d,flag3=%d''' % ( code0, code1, code2, note, flag0, flag1, flag2, flag3) query = '''update aslot set %s where adate=%%s and apptix=%%s and start=%%s and serialno=%%s''' % changes values = (moddate, apptix, start, serialno) try: cursor.execute(query, values) db.commit() result = True except Exception as ex: print "exception in appointments.modify_aslot_appt ", ex print "couldn't modify aslot %s %s %s serialno %d" % ( moddate, apptix, start, serialno) result = False cursor.close() # db.close() return result
def get_notes(sno): db = connect.connect() cursor = db.cursor() cursor.execute('''SELECT line from notes where serialno = %s order by lineno''', sno) results = cursor.fetchall() cursor.close() notes_dict = OrderedDict() ndate, op = "", "" #a line is like ('\x01REC\x0c\x08m\x0c\x08m\n\x08',) for line, in results: ntype, note, operator, date2 = notes.decipher_noteline(line) if date2 != "": ndate = date2 if operator != "": op = operator key = (ndate, op) if notes_dict.has_key(key): notes_dict[key].append((ntype, note)) else: notes_dict[key] = [(ntype, note)] return notes_dict
def is_locked(serialno): ''' check the records_in_use_table for a lock on serialno returns locked(bool), (op, surgery_no, timestamp) ''' LOGGER.debug("checking for a lock on record %s", serialno) values = (serialno, ) db = connect.connect() cursor = db.cursor() cursor.execute(QUERY6, values) rows = cursor.fetchall() cursor.close() if not rows: pass elif len(rows) > 1: raise IOError("multiple locks present - this shouldn't happen") else: op, surgeryno, timestamp = rows[0] if op != localsettings.operator or \ surgeryno != localsettings.surgeryno: message = "<h3>%s</h3>%s %s %s %s %s %s<hr />%s %s<hr />%s" % ( _("WARNING"), _("Record number"), serialno, _("is locked by"), op, _("in surgery number"), surgeryno, _("Lock aquired at"), timestamp, _("Please reload this record before making any changes") ) return True, message return False, None
def update_mh(ix, mh): assert isinstance(mh, MedHist), "bad object passed to insert mh" db = connect() cursor = db.cursor() values = (mh.warning_card, mh.medication_comments, mh.allergies, mh.respiratory, mh.heart, mh.diabetes, mh.arthritis, mh.bleeding, mh.infectious_disease, mh.endocarditis, mh.liver, mh.anaesthetic, mh.joint_replacement, mh.heart_surgery, mh.brain_surgery, mh.hospital, mh.cjd, mh.other, mh.alert, mh.chkdate, localsettings.operator, ix ) result = cursor.execute(UPDATE_QUERY, values) cursor.execute(DELETE_MEDS_QUERY, (ix,)) cursor.executemany( INSERT_MEDS_QUERY, [(ix, key, mh.medications[key]) for key in mh.medications] ) cursor.close() return result
def __init__(self, serialno): ''' initiate the class with default variables, then load from database ''' self.serialno = serialno self.recdent_period = None self.recdent = None self.rechyg_period = None self.rechyg = None self.recall_method = None self.note = "" self.recall_active = False db = connect.connect() cursor = db.cursor() cursor.execute(QUERY, (self.serialno,)) row = cursor.fetchone() if not row: return (self.recall_active, self.recdent_period, self.recdent, self.rechyg_period, self.rechyg, self.recall_method, self.note) = row if self.note is None: self.note = "" if self.recall_active is None: self.recall_active = False
def insert_clinician(clinician): result = False comments = "added by client - %s" % datetime.datetime.now().strftime( "%m %h %Y %H:%M") db = connect.connect() try: db.autocommit = False cursor = db.cursor() cursor.execute(INSERT_CLINICIAN_QUERIES[0], (clinician.initials, clinician.name, clinician.formal_name, clinician.qualifications, clinician.type, clinician.speciality, clinician.data, comments) ) ix = db.insert_id() cursor.execute(INSERT_CLINICIAN_QUERIES[1], (ix, clinician.start_date, clinician.end_date) ) if clinician.new_diary: cursor.execute(INSERT_CLINICIAN_QUERIES[2], (ix, ix)) cursor.close() db.commit() result = True except: LOGGER.exception("failed to insert clinician") db.rollback() finally: db.autocommit = True return result
def update_treatments(id, treatments): values = list(treatments) + [id] db = connect.connect() cursor = db.cursor() result = cursor.execute(UPDATE_TREATMENTS_QUERY, values) cursor.close() return result
def create_book(self, clinician_id): db = connect() cursor = db.cursor() result = cursor.execute(INSERT_QUERY, (BLANK_PHRASEBOOK, clinician_id)) cursor.close() self._books = {} # forget any loaded books return result
def copy_OMforum_into_forum(): ''' I am scrapping the omforum table, put these posts into the forum ''' db = connect.connect() cursor=db.cursor() cursor.execute('''lock tables omforum read,forum write''') cursor.execute('''select ix, parent_ix, inits, fdate, topic, comment, open from omforum order by ix''') rows=cursor.fetchall() cursor.execute('''select max(ix) from forum''') start_ix = cursor.fetchone()[0]+1 print "start_ix =", start_ix query = '''insert into forum (parent_ix, inits, fdate, topic, comment, open) values (%s, %s, %s, %s, %s, %s)''' for row in rows: if row[1]: parent_ix = row[1] + start_ix else: parent_ix = None values = (parent_ix, row[2], row[3], row[4], row[5], row[6]) cursor.execute(query, values) db.commit() cursor.execute("unlock tables") cursor.close() db.close() return True
def __init__(self, sno): ''' initiate the class with default variables, then load from database ''' if sno <= 0: raise localsettings.PatientNotFoundError self.serialno = sno db = connect.connect() cursor = db.cursor() cursor.execute(QUERY, (sno, )) row = cursor.fetchone() if not row: raise localsettings.PatientNotFoundError self.title, self.fname, self.sname, \ self.dob, self.cset, self.dnt1, self.dnt2 = row
def transfer_data(self): ''' function specific to this update. ''' db = connect.connect() db.autocommit(False) try: cursor = db.cursor() cursor.execute(SOURCE_QUERY) rows = cursor.fetchall() cursor.close() cursor = db.cursor() step = 1 / len(rows) count, prev_courseno, prev_cat_type = 1, 0, "" prev_hash = None for i, row in enumerate(rows): courseno, ix, category, type_, completed = row cat_type = "%s%s" % (category, type_) if courseno != prev_courseno: count = 1 elif cat_type != prev_cat_type: count = 1 else: count += 1 prev_courseno = courseno prev_cat_type = cat_type tx_hash = localsettings.hash_func( "%s%s%s%s" % (courseno, category, count, type_)) if completed is None: completed = False values = (ix, tx_hash, completed) cursor.execute(DEST_QUERY, values) if i % 1000 == 0: self.progressSig(85 * i / len(rows) + 10, _("transfering data")) db.commit() db.close() except Exception as exc: logging.exception("error transfering data") db.rollback() raise UpdateException(exc)
def getVersion(): try: db = connect.connect() cursor = db.cursor() query = 'select data from settings where value = "Schema_Version"' cursor.execute(query) rows = cursor.fetchall() except connect.ProgrammingError as ex: LOGGER.warning("no settings table! %s" % ex) LOGGER.warning("schema assumed to be 1.0") return "1.0" version = "" for row in rows: data = row[0] if data > version: version = data localsettings.DB_SCHEMA_VERSION = version return version
def apply(self): date_ = self.date_edit.date().toPyDate() ref = str(self.serialno_le.text().toAscii()) descr = str(self.patient_le.text().toAscii()) for key, value in localsettings.cashbookCodesDict.viewitems(): if self.code_cb.currentText() == value: code = key break dntid = localsettings.ops_reverse[str(self.dentist_cb.currentText())] currency = "%.02f" % self.amount_sb.value() amt = int(currency.replace(".", "")) values = (date_, ref, descr, code, dntid, amt, self.ix) db = connect() cursor = db.cursor() cursor.execute(UPDATE_QUERY, values) db.commit()
def block_appt(bldate, apptix, start, end, bl_start, bl_end, reason): ''' put a block in the book, with text set as reason ''' #- 1st check the block is free slots = future_slots(bldate, bldate, (apptix, )) date_time = datetime.datetime.combine(bldate, start) block_length = (localsettings.pyTimeToMinutesPastMidnight(end) - localsettings.pyTimeToMinutesPastMidnight(start)) this_slot = FreeSlot(date_time, apptix, block_length) #-- check block still available!! found = False for slot in slots: if slot == this_slot: found = True break if not found: return False db = connect() cursor = db.cursor() query = '''INSERT INTO aslot (adate, apptix, start, end, name, serialno, code0, code1, code2, note, flag0, flag1, flag2, flag3) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''' values = (bldate, apptix, localsettings.pyTimetoWystime(bl_start), localsettings.pyTimetoWystime(bl_end), reason, 0, "", "", "", "", -128, 0, 0, 0) if cursor.execute(query, values): #-- insert call.. so this will always be true unless we have key #-- value errors? db.commit() result = True else: print "couldn't insert into aslot %s %s %s" % (bldate, apptix, start) result = False cursor.close() # db.close() return result
def transferData(self): ''' move data into the new tables ''' db = connect.connect() cursor = db.cursor() for table, vals in (("feetable_scotNHS_08_Adult", "NF08, NF08_pt"), ("feetable_scotNHS_08_Child", "NF08, NF08_pt"), ("feetable_scotNHS_09_Adult", "NF09, NF09_pt"), ("feetable_scotNHS_09_Child", "NF09, NF09_pt"), ("feetable_Private_2009", "PFA"), ("feetable_Private_2010", "PFA"), ("feetable_HDP", "PFA")): cursor.execute('lock tables newfeetable read, %s write' % table) cursor.execute('''select section, code, oldcode, USERCODE, regulation, description, description1, %s from newfeetable order by code, ix''' % vals) rows = cursor.fetchall() query = 'insert into %s' % table query += ''' (section, code, oldcode, USERCODE, regulation, description, brief_description, fee''' if "," in vals: query += ' , pt_fee) values (%s, %s, %s, %s, %s, %s, %s, %s, %s)' else: query += ') values (%s, %s, %s, %s, %s, %s, %s, %s)' values = [] for row in rows: if "NHS" in table or row[7] != 0: values.append(row) cursor.executemany(query, values) db.commit() cursor.execute("unlock tables") cursor.close() db.close() return True
def pt_appt_made(serialno, aprix, date, time, dent): ''' modifies the apr table, finding the unscheduled version and putting scheduled data in ''' db = connect() cursor = db.cursor() result = True try: fullquery = '''UPDATE apr SET adate="%s" ,atime=%d, practix=%d WHERE serialno=%d AND aprix=%d''' % (date, time, dent, serialno, aprix) cursor.execute(fullquery) db.commit() except Exception as ex: print "exception in appointments.pt_appt_made ", ex result = False cursor.close() # db.close() return result
def getPosts(user=None, include_closed=False): ''' gets all active rows from a forum table ''' global HIGHESTID conditions, values = ["open"], [not include_closed] if user: conditions.append('recipient') values.append(user) db = connect.connect() cursor = db.cursor() query = ('SELECT ix, parent_ix, topic, inits, fdate, recipient, comment ' 'FROM forum where %s ORDER BY parent_ix, ix' % " and ".join(["%s=%%s" % val for val in conditions])) cursor.execute(query, values) rows = cursor.fetchall() cursor.close() retarg = [] update = False for row in rows: newpost = ForumPost() newpost.ix = row[0] if newpost.ix > HIGHESTID: HIGHESTID = newpost.ix update = True newpost.parent_ix = row[1] newpost.topic = row[2] newpost.inits = row[3] newpost.date = row[4] newpost.recipient = row[5] newpost.comment = row[6] newpost.briefcomment = row[6][:40] if newpost.comment != newpost.briefcomment: newpost.briefcomment += "...." retarg.append(newpost) if update: updateReadHistory() return retarg
def initiateUsers(changedServer=False): ''' just grab user names. necessary because the db schema could be OOD here ''' global allowed_logins from openmolar import connect if changedServer and connect.mainconnection: print "closing connection" connect.mainconnection.close() reload(connect) db = connect.connect() cursor = db.cursor() cursor.execute("select id from opid") # grab initials of those currently allowed to log in trows = cursor.fetchall() cursor.close() allowed_logins = [] for row in trows: allowed_logins.append(row[0])
def getBankHol(adate): ''' get Bank Hol for one specific date ''' db = connect() cursor = db.cursor() query = '''SELECT memo FROM calendar WHERE adate=%s''' retarg = "" try: cursor.execute(query, (adate, )) rows = cursor.fetchall() cursor.close() for row in rows: retarg += "%s " % row except ProgrammingError as e: # in case their is no bank holiday table. retarg = "couldn't get Bank Holiday details" return retarg
def addColumns(self): ''' fee tables need a new column ''' db = connect.connect() cursor = db.cursor() cursor.execute('select tablename from feetable_key') rows = cursor.fetchall() for row in rows: print "altering feetable", row[0] query = 'alter table %s add column pl_cmp char(20)' % row[0] cursor.execute(query) db.commit() cursor.close() db.close() return True
def clearEms(cedate): ''' a convenience function to remove all EMERGENCY apointments on day cedate ''' db = connect() cursor = db.cursor() number = 0 try: query = \ 'delete from aslot WHERE adate=%s and flag0=%s and name like %s' values = (cedate, -128, "%Emergency%") number = cursor.execute(query, values) db.commit() except Exception as ex: print "exception in appointments module, clearEms" print ex cursor.close() # db.close() return number
def getEsts(sno, courseno=None): db = connect() cursor = db.cursor() if courseno is None: cursor.execute(QUERY, (sno, )) else: cursor.execute(COURSE_QUERY, (sno, courseno)) rows = cursor.fetchall() cursor.close() estimates = OrderedDict() for row in rows: hash_ = row[10] completed = bool(row[9]) tx_hash = TXHash(hash_, completed) ix = row[0] est = estimates.get(ix, Estimate()) est.ix = ix est.courseno = row[11] est.number = row[1] est.itemcode = row[2] est.description = row[3] est.fee = row[4] est.ptfee = row[5] est.feescale = row[6] est.csetype = row[7] est.dent = row[8] try: est.tx_hashes.append(tx_hash) except AttributeError: est.tx_hashes = [tx_hash] estimates[ix] = est return list(estimates.values())
def commit(pt): sqlcond = "" values = [] for attr in patient_class.patientTableAtts: value = pt.__dict__[attr] if value: sqlcond += '%s = %%s,' % attr values.append(value) sqlcommand = "insert into patients SET %s serialno=%%s" % sqlcond query = "select max(serialno) from patients" Attempts = 0 while True: db = connect.connect() cursor = db.cursor() cursor.execute(query) currentMax = cursor.fetchone()[0] if currentMax: newSerialno = currentMax + 1 else: newSerialno = 1 try: cursor.execute(sqlcommand, tuple(values + [newSerialno])) cursor.close() db.commit() break except connect.IntegrityError as e: print "error saving new patient, will retry with new serialno" print e newSerialno = -1 Attempts += 1 if Attemps > 20: break # db.close() return newSerialno
def toNotes(serialno, newnotes): ''' new code with schema 1.9 ''' LOGGER.debug("write changes - toNotes for patient %d" % serialno) tstamp = localsettings.currentTime().strftime("%d/%m/%Y %H:%M:%S") notetuplets = [] notetuplets.append(("opened", "System date - %s" % tstamp)) # database version stores max line length of 80chars for ntype, notes in newnotes: line_end = "\n" if ntype == "newNOTE" else "" for line in note_splitter(notes, line_end): notetuplets.append((ntype, line)) notetuplets.append(("closed", "%s %s" % (localsettings.operator, tstamp))) try: op1, op2 = localsettings.operator.split("/") except ValueError: op1 = localsettings.operator op2 = None values = [] for ntype, noteline in notetuplets: values.append((serialno, op1, op2, ntype, noteline)) rows = 0 if values: db = connect() cursor = db.cursor() # this (superior code?) didn't work on older MySQLdb versions. # rows = cursor.executemany(query, tuple(values)) for value in values: rows += cursor.execute(INSERT_NOTE_QUERY, value) cursor.close() db.commit() return rows > 0
def discreet_changes(pt_changed, changes): ''' this updates only the selected atts (usually called by automated proc such as recalls... and accounts) only updates the patients table ''' LOGGER.debug("write changes - discreet changes") sqlcond = "" for change in changes: value = pt_changed.__dict__[change] LOGGER.debug("discreet change %s %s" % (change, type(value))) if change in patient_class.dateFields: if value != "" and value is not None: sqlcond += '%s="%s" ,' % (change, value) elif value is None: sqlcond += '%s=NULL ,' % change elif type(value) in (int, int): sqlcond += '%s=%s ,' % (change, value) else: sqlcond += '%s="%s" ,' % (change, value) sqlcommand = "update patients SET %s where serialno=%%s" % ( sqlcond.strip(",")) LOGGER.debug("%s (%s,)" % (sqlcommand, pt_changed.serialno)) result = True if sqlcond != "": db = connect() cursor = db.cursor() try: cursor.execute(sqlcommand, (pt_changed.serialno, )) db.commit() except Exception as e: LOGGER.exception("unable to write discreet changes") result = False cursor.close() return result
def insertValues(self): ''' fee tables need a new column ''' db = connect.connect() cursor = db.cursor() cursor.execute('select tablename from feetable_key') rows = cursor.fetchall() for row in rows: print "altering feetable", row[0] query = 'update %s set pl_cmp=%%s where code=%%s' % row[0] for key in TYPEDICT: for code in TYPEDICT[key]: values = (key, code) cursor.execute(query, values) db.commit() cursor.close() db.close() return True
def getFeeDictForModification(table): ''' a comprehensive dictionary formed from the entire table in the database ''' query = '''select column_name from information_schema.columns where table_name = %s and table_schema = %s''' values = (table, connect.myDb) db = connect.connect() cursor = db.cursor() cursor.execute(query, values) rows = cursor.fetchall() header = [] for row in rows: header.append(row[0]) query = 'select * from %s' % table cursor.execute(query) rows = cursor.fetchall() cursor.close() return (header, rows)
def newPosts(): result = False try: users = localsettings.operator.split("/") if users == []: return db = connect.connect() cursor = db.cursor() query = '''select max(ix) from forum''' cursor.execute(query) row = cursor.fetchone() query = "select max(id) from forumread where" for user in users: query += " op='%s' or" % user cursor.execute(query.strip("or")) row2 = cursor.fetchone() cursor.close() result = row[0] > row2[0] except connect.ProgrammingError as e: print e return result
def updateAday(uddate, arg): ''' takes an instance of the workingDay class and updates the database returns an omSQLresult ''' db = connect() cursor = db.cursor() result = omSQLresult() query = '''insert into aday (memo, adate, apptix, start, end, flag) values (%s,%s, %s, %s, %s, %s) on duplicate key update memo=%s, adate=%s, apptix=%s, start=%s, end=%s, flag=%s''' values = (arg.memo, uddate, arg.apptix, arg.sqlStart(), arg.sqlFinish(), arg.active) * 2 result.setNumber(cursor.execute(query, values)) if result: db.commit() return result
def get_feescales_from_database(self, in_use_only=True, priority_order=True): ''' connects and get the data from feetable_key ''' query = QUERY if in_use_only: query += ' where in_use = True' else: # if called by feescale editor self.ixs_in_db = set([]) if priority_order: query += ' order by priority desc' db = connect.connect() cursor = db.cursor() cursor.execute(query) rows = cursor.fetchall() cursor.close() LOGGER.debug("%d feescales retrieved" % len(rows)) for ix, xml_data in rows: self.ixs_in_db.add(ix) return rows
def allAppointmentData(adate, dents=()): ''' this gets appointment data for a specifc date and dents 2nd arg will frequently be provided by getWorkingDents(adate) ''' if dents == (): cond = "" else: cond = "and (" + "apptix=%s or " * (len(dents) - 1) + "apptix=%s ) " db = connect() cursor = db.cursor() query = '''select apptix,start,end,name,serialno,code0, code1,code2,note,flag0,flag1,flag2,flag3, timestamp from aslot where adate=%s''' query += " %s order by apptix, start" % cond cursor.execute(query, (adate, ) + dents) data = cursor.fetchall() cursor.close() return data
def set_patient(self, pt): ''' pass a patient object to set the serialnumber and name fields. ''' self.pt = pt db = connect() cursor = db.cursor() cursor.execute(PSEUDONYMS_QUERY, (pt.serialno, )) alts = [] for row in cursor.fetchall(): pseudonym = Pseudonym(*row) alts.append(pseudonym) self.pseudonyms = alts previous = '</li><li>'.join( [p.html() for p in alts if p.comment == "previous surname"] + ['<a href="om://add_psn">%s</a>' % _("Add New")]) alts = '</li><li>'.join( [p.html() for p in alts if p.comment != "previous surname"] + ['<a href="om://add_alt">%s</a>' % _("Add New")]) self.browser.setHtml(HTML % (self.fname, self.sname, previous, alts)) self.browser.delegate_links()
def insertRowsIntoNew(self, rows): ''' insert new row types into the newestimates table ''' db = connect.connect() cursor = db.cursor() progress_var = len(rows) i = 0 query = '''insert into newestimates (serialno, courseno, category, type, number, itemcode, description, fee, ptfee , csetype, feescale, dent, completed, carriedover , linked , modified_by , time_stamp) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, '1_0to1_1script', NOW())''' for values in rows: cursor.execute(query, values) i += 1 if i % 100 == 0: self.progressSig((i / progress_var) * 90 + 40) db.commit() db.close()
def n_hyg_visits(self): if self._n_hyg_visits is not None: pass elif not localsettings.hyg_ixs: self._n_hyg_visits = 0 else: if len(localsettings.hyg_ixs) == 1: conditional = "=" values = (self.serialno, localsettings.hyg_ixs[0]) else: conditional = "in" values = (self.serialno, localsettings.hyg_ixs) self._n_hyg_visits = 0 db = connect.connect() cursor = db.cursor() query = '''select count(*) from (select date from daybook where serialno=%%s and trtid %s %%s group by date) as t''' % conditional if cursor.execute(query, values): self._n_hyg_visits = cursor.fetchone()[0] cursor.close() return self._n_hyg_visits
def details(sno): ''' returns an html page showing pt's Treatment History ''' db = connect() cursor = db.cursor() fields = CURRTRT_ATTS query = "" for field in fields: if field in ('examd', 'accd', 'cmpd'): query += 'DATE_FORMAT(%s, "%s"),' % (field, localsettings.OM_DATE_FORMAT) else: query += field + "," query = query.strip(",") cursor.execute('''SELECT %s from currtrtmt2 where serialno = %d order by courseno desc''' % (query, sno)) rows = cursor.fetchall() cursor.close() courses = [] for row in rows: course = txCourse(row) courses.append(course) claimNo = len(courses) retarg = "<h2>Past Courses of Treatment - %d rows found</h2>" % claimNo for course in courses: retarg += course.toHtml() retarg += "<br /><hr /><br />" # db.close() return retarg
def getFromDB(self): try: db = connect.connect() cursor = db.cursor() query = '''SELECT %s,%s,%s,%s,%s,%s,%s,%s from plandata where serialno=%s''' % (planDBAtts[1:] + (self.serialno, )) cursor.execute(query) row = cursor.fetchone() cursor.close() i = 1 if row: for val in row: if val: att = planDBAtts[i] if att == "planjoin": self.planjoin = localsettings.formatDate(val) else: self.__dict__[att] = val i += 1 self.retrieved = True except Exception: LOGGER.exception("error loading from plandata")
def add_previous_surname(self, psn=""): LOGGER.info("add a surname") if psn == "": message = _("Please enter a previous surname") else: message = "%s '%s' %s" % (_("Save"), psn, _("as a previous surname?")) surname, result = \ QtWidgets.QInputDialog.getText(self, _("Input required"), message, QtWidgets.QLineEdit.Normal, psn) if result: LOGGER.info("adding %s as a previous surname", surname) db = connect() cursor = db.cursor() cursor.execute(INSERT_PSN_QUERY, (self.pt.serialno, surname.upper())) cursor.close() self.set_patient(self.pt) return True
def todays_patients(dents): ''' get todays patients for dents supplied as a tuple such as (4,5) or (0,) for all used to populate the combobox on the front page ''' db = connect() cursor = db.cursor() if 0 in dents: cond = "" values = (localsettings.currentDay(), ) else: cond = "and (" + "apptix=%s or " * (len(dents) - 1) + "apptix=%s )" values = (localsettings.currentDay(), ) + dents query = 'SELECT serialno,name FROM aslot WHERE adate=%s ' + cond + \ ' and serialno!=0 ORDER BY name' cursor.execute(query, values) rows = cursor.fetchall() cursor.close() return rows
def apply_changed(self): notes = str(self.text_edit.toPlainText()).rstrip(" \n") short_lines = list(patient_write_changes.note_splitter(notes, "\n")) LOGGER.debug(short_lines) values = [] i = 0 for ix, note in self.notes: try: values.append((short_lines[i], ix)) except IndexError: # a line has been deleted. values.append(("", ix)) i += 1 db = connect.connect() cursor = db.cursor() cursor.executemany(UPDATE_QUERY, values) cursor.close() if len(short_lines) > i: patient_write_changes.toNotes(self.sno, [("newNOTE", line) for line in short_lines[i:]])