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