def get_future(dbo, user = ""):
"""
Gets a list of future diary notes
for the user supplied (or all users if no user passed)
LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO
"""
return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \
"FROM diary WHERE %s " \
"AND DiaryDateTime > %s" \
"ORDER BY DiaryDateTime" % (user_role_where_clause(dbo, user), db.ddt(i18n.now(dbo.timezone))))
def get_future(dbo, user=""):
"""
Gets a list of future diary notes
for the user supplied (or all users if no user passed)
LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO
"""
return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \
"FROM diary WHERE %s " \
"AND DiaryDateTime > %s" \
"ORDER BY DiaryDateTime" % (user_role_where_clause(dbo, user), db.ddt(i18n.now(dbo.timezone))))
def insert_onlineformincoming_from_form(dbo, post, remoteip):
"""
Create onlineformincoming records from posted data. We
create a row for every key/value pair in the posted data
with a unique collation ID.
"""
IGNORE_FIELDS = [ "formname", "flags", "redirect", "account", "filechooser", "method" ]
collationid = db.query_int(dbo, "SELECT MAX(CollationID) FROM onlineformincoming") + 1
formname = post["formname"]
posteddate = i18n.now(dbo.timezone)
flags = post["flags"]
for k, v in post.data.iteritems():
if k not in IGNORE_FIELDS:
label = ""
displayindex = 0
fieldname = k
# Form fields should have a _ONLINEFORMFIELD.ID suffix we can use to get the
# original label and display position
if k.find("_") != -1:
fid = utils.cint(k[k.rfind("_")+1:])
fieldname = k[0:k.rfind("_")]
if fid != 0:
fld = db.query(dbo, "SELECT Label, DisplayIndex FROM onlineformfield WHERE ID = %d" % fid)
if len(fld) > 0:
label = fld[0]["LABEL"]
displayindex = fld[0]["DISPLAYINDEX"]
sql = db.make_insert_sql("onlineformincoming", (
( "CollationID", db.di(collationid)),
( "FormName", db.ds(formname)),
( "PostedDate", db.ddt(posteddate)),
( "Flags", db.ds(flags)),
( "FieldName", db.ds(fieldname)),
( "Label", db.ds(label)),
( "DisplayIndex", db.di(displayindex)),
( "Host", db.ds(remoteip)),
( "Value", post.db_string(k))
))
db.execute(dbo, sql)
# Sort out the preview of the first few fields
fieldssofar = 0
preview = []
for fld in get_onlineformincoming_detail(dbo, collationid):
if fieldssofar < 3:
fieldssofar += 1
preview.append( fld["LABEL"] + ": " + fld["VALUE"] )
db.execute(dbo, "UPDATE onlineformincoming SET Preview = %s WHERE CollationID = %s" % ( db.ds(", ".join(preview)), db.di(collationid) ))
# Did the original form specify some email addresses to send
# incoming submissions to?
email = db.query_string(dbo, "SELECT o.EmailAddress FROM onlineform o " \
"INNER JOIN onlineformincoming oi ON oi.FormName = o.Name " \
"WHERE oi.CollationID = %d" % int(collationid))
if email is not None and email.strip() != "":
utils.send_email(dbo, configuration.email(dbo), email, "", "%s - %s" % (formname, ", ".join(preview)), get_onlineformincoming_plain(dbo, collationid))
return collationid
def get_completed_upto_today(dbo, user = ""):
"""
Gets a list of completed diary notes upto and including
today for the user supplied (or all users if no user passed)
LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO
"""
sixmonths = i18n.subtract_days(i18n.now(dbo.timezone), 182)
return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \
"FROM diary WHERE %s " \
"AND DateCompleted Is Not Null AND DiaryDateTime <= %s AND DiaryDateTime >= %s" \
"ORDER BY DiaryDateTime DESC" % (user_role_where_clause(dbo, user), db.ddt(i18n.now(dbo.timezone)), db.ddt(sixmonths)))
def get_all_upto_today(dbo, user=""):
"""f
Gets a list of all diary notes upto and including
today for the user supplied (or all users if no user passed)
LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO
"""
sixmonths = i18n.subtract_days(i18n.now(dbo.timezone), 182)
return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \
"FROM diary WHERE %s " \
"AND DiaryDateTime <= %s AND DiaryDateTime >= %s" \
"ORDER BY DiaryDateTime DESC" % (user_role_where_clause(dbo, user), db.ddt(i18n.now(dbo.timezone)), db.ddt(sixmonths)))
def insert_onlineformincoming_from_form(dbo, data, remoteip):
"""
Create onlineformincoming records from posted data. We
create a row for every key/value pair in the posted data
with a unique collation ID.
"""
IGNORE_FIELDS = [
"formname", "flags", "redirect", "account", "filechooser", "method"
]
collationid = db.query_int(
dbo, "SELECT MAX(CollationID) FROM onlineformincoming") + 1
formname = utils.df_ks(data, "formname")
posteddate = i18n.now(dbo.timezone)
flags = utils.df_ks(data, "flags")
for k, v in data.iteritems():
if k not in IGNORE_FIELDS:
label = ""
displayindex = 0
fieldname = k
# Form fields should have a _ONLINEFORMFIELD.ID suffix we can use to get the
# original label and display position
if k.find("_") != -1:
fid = utils.cint(k[k.rfind("_") + 1:])
fieldname = k[0:k.rfind("_")]
if fid != 0:
fld = db.query(
dbo,
"SELECT Label, DisplayIndex FROM onlineformfield WHERE ID = %d"
% fid)
if len(fld) > 0:
label = fld[0]["LABEL"]
displayindex = fld[0]["DISPLAYINDEX"]
sql = db.make_insert_sql(
"onlineformincoming",
(("CollationID", db.di(collationid)),
("FormName", db.ds(formname)),
("PostedDate", db.ddt(posteddate)), ("Flags", db.ds(flags)),
("FieldName", db.ds(fieldname)), ("Label", db.ds(label)),
("DisplayIndex", db.di(displayindex)),
("Host", db.ds(remoteip)), ("Value", db.ds(v))))
db.execute(dbo, sql)
# Sort out the preview of the first few fields
fieldssofar = 0
preview = []
for fld in get_onlineformincoming_detail(dbo, collationid):
if fieldssofar < 3:
fieldssofar += 1
preview.append(fld["LABEL"] + ": " + fld["VALUE"])
db.execute(
dbo,
"UPDATE onlineformincoming SET Preview = %s WHERE CollationID = %s" %
(db.ds(", ".join(preview)), db.di(collationid)))
return collationid
def login(dbo, username):
"""
Marks the given user as logged in
"""
logout(dbo, username)
db.execute(dbo, "DELETE FROM activeuser WHERE UPPER(UserName) LIKE '%s'" % str(username.upper()))
db.execute(dbo, db.make_insert_sql("activeuser", (
( "UserName", db.ds(username)),
( "Since", db.ddt(i18n.now())),
( "Messages", db.ds("asm3"))
)))
al.info("%s logged in" % username, "users.login", dbo)
def login(dbo, username):
"""
Marks the given user as logged in
"""
logout(dbo, username)
db.execute(
dbo, "DELETE FROM activeuser WHERE UPPER(UserName) LIKE '%s'" %
str(username.upper()))
db.execute(
dbo,
db.make_insert_sql("activeuser", (("UserName", db.ds(username)),
("Since", db.ddt(i18n.now())),
("Messages", db.ds("asm3")))))
al.info("%s logged in" % username, "users.login", dbo)
def get_uncompleted_upto_today(dbo, user=""):
"""
Gets a list of uncompleted diary notes upto and including
today for the user supplied (or all users if no user passed)
LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO
"""
sixmonths = i18n.subtract_days(i18n.now(dbo.timezone), 182)
current = i18n.now(dbo.timezone)
alltoday = datetime.datetime(current.year, current.month, current.day, 23,
59, 59)
return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \
"FROM diary WHERE %s " \
"AND DateCompleted Is Null AND DiaryDateTime <= %s AND DiaryDateTime >= %s" \
"ORDER BY DiaryDateTime DESC" % (user_role_where_clause(dbo, user), db.ddt(alltoday), db.ddt(sixmonths)))
def insert_onlineformincoming_from_form(dbo, data, remoteip):
"""
Create onlineformincoming records from posted data. We
create a row for every key/value pair in the posted data
with a unique collation ID.
"""
IGNORE_FIELDS = [ "formname", "flags", "redirect", "account", "filechooser", "method" ]
collationid = db.query_int(dbo, "SELECT MAX(CollationID) FROM onlineformincoming") + 1
formname = utils.df_ks(data, "formname")
posteddate = i18n.now(dbo.timezone)
flags = utils.df_ks(data, "flags")
for k, v in data.iteritems():
if k not in IGNORE_FIELDS:
label = ""
displayindex = 0
fieldname = k
# Form fields should have a _ONLINEFORMFIELD.ID suffix we can use to get the
# original label and display position
if k.find("_") != -1:
fid = utils.cint(k[k.rfind("_")+1:])
fieldname = k[0:k.rfind("_")]
if fid != 0:
fld = db.query(dbo, "SELECT Label, DisplayIndex FROM onlineformfield WHERE ID = %d" % fid)
if len(fld) > 0:
label = fld[0]["LABEL"]
displayindex = fld[0]["DISPLAYINDEX"]
sql = db.make_insert_sql("onlineformincoming", (
( "CollationID", db.di(collationid)),
( "FormName", db.ds(formname)),
( "PostedDate", db.ddt(posteddate)),
( "Flags", db.ds(flags)),
( "FieldName", db.ds(fieldname)),
( "Label", db.ds(label)),
( "DisplayIndex", db.di(displayindex)),
( "Host", db.ds(remoteip)),
( "Value", db.ds(v))
))
db.execute(dbo, sql)
# Sort out the preview of the first few fields
fieldssofar = 0
preview = []
for fld in get_onlineformincoming_detail(dbo, collationid):
if fieldssofar < 3:
fieldssofar += 1
preview.append( fld["LABEL"] + ": " + fld["VALUE"] )
db.execute(dbo, "UPDATE onlineformincoming SET Preview = %s WHERE CollationID = %s" % ( db.ds(", ".join(preview)), db.di(collationid) ))
return collationid
def action(dbo, action, username, tablename, description):
"""
Adds an audit record
"""
# Truncate description field to 16k if it's very long
if len(description) > 16384:
description = description[0:16384]
sql = db.make_insert_sql("audittrail", (
( "Action", db.ds(action) ),
( "AuditDate", db.ddt(i18n.now(dbo.timezone)) ),
( "UserName", db.ds(username) ),
( "TableName", db.ds(tablename) ),
( "Description", db.ds(description) )
))
db.execute(dbo, sql)
def df_dt(data, datefield, timefield, l):
""" Returns a datetime field for the database """
if data.has_key(datefield):
d = display2python(l, data[datefield])
if data.has_key(timefield):
tbits = data[timefield].split(":")
hour = 0
minute = 0
second = 0
if len(tbits) > 0:
hour = cint(tbits[0])
if len(tbits) > 1:
minute = cint(tbits[1])
if len(tbits) > 2:
second = cint(tbits[2])
t = datetime.time(hour, minute, second)
d = d.combine(d, t)
return db.ddt(d)
else:
return "Null"
def df_dt(data, datefield, timefield, l):
""" Returns a datetime field for the database """
if data.has_key(datefield):
d = display2python(l, data[datefield])
if data.has_key(timefield):
tbits = data[timefield].split(":")
hour = 0
minute = 0
second = 0
if len(tbits) > 0:
hour = cint(tbits[0])
if len(tbits) > 1:
minute = cint(tbits[1])
if len(tbits) > 2:
second = cint(tbits[2])
t = datetime.time(hour, minute, second)
d = d.combine(d, t)
return db.ddt(d)
else:
return "Null"
def update_animalcontrol_dispatchnow(dbo, acid, username):
"""
Updates an animal control incident record, marking it dispatched
now with the current user as ACO.
"""
db.execute(dbo, "UPDATE animalcontrol SET DispatchedACO=%s, DispatchDateTime=%s WHERE ID=%d" % (db.ds(username), db.ddt(now(dbo.timezone)), acid))
audit.edit(dbo, username, "animalcontrol", acid, "aco=%s, dispatch=%s" % (username, now(dbo.timezone)))
def get_uncompleted_upto_today(dbo, user = "", includecreatedby = True):
"""
Gets a list of uncompleted diary notes upto and including
today for the user supplied (or all users if no user passed)
LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO
"""
sixmonths = i18n.subtract_days(i18n.now(dbo.timezone), 182)
current = i18n.now(dbo.timezone)
alltoday = datetime.datetime(current.year, current.month, current.day, 23, 59, 59)
return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \
"FROM diary WHERE %s " \
"AND DateCompleted Is Null AND DiaryDateTime <= %s AND DiaryDateTime >= %s" \
"ORDER BY DiaryDateTime DESC" % (user_role_where_clause(dbo, user, includecreatedby), db.ddt(alltoday), db.ddt(sixmonths)))
def df_dt(data, datefield, timefield, l):
""" Returns a datetime field for the database """
return db.ddt(df_kdt(data, datefield, timefield, l))
def update_animalcontrol_respondnow(dbo, acid, username):
"""
Updates an animal control incident record, marking it responded to now
"""
db.execute(dbo, "UPDATE animalcontrol SET RespondedDateTime=%s WHERE ID=%d" % (db.ddt(now(dbo.timezone)), acid))
audit.edit(dbo, username, "animalcontrol", acid, "responded=%s" % now(dbo.timezone))
