def insert_user_from_form(dbo, username, data):
"""
Creates a user record from posted form data. Uses
the roles key (which should be a comma separated list of
role ids) to create userrole records.
"""
nuserid = db.get_id(dbo, "users")
sql = db.make_insert_sql("users", (
( "ID", db.di(nuserid)),
( "UserName", utils.df_t(data, "username")),
( "RealName", utils.df_t(data, "realname")),
( "EmailAddress", utils.df_t(data, "email")),
( "Password", db.ds(hash_password(utils.df_ks(data, "password"), True))),
( "SuperUser", utils.df_s(data, "superuser")),
( "RecordVersion", db.di(0)),
( "SecurityMap", db.ds("dummy")),
( "OwnerID", utils.df_s(data, "person")),
( "LocationFilter", utils.df_t(data, "locationfilter")),
( "IPRestriction", utils.df_t(data, "iprestriction"))
))
db.execute(dbo, sql)
audit.create(dbo, username, "users", str(nuserid))
roles = utils.df_ks(data, "roles").strip()
if roles != "":
for rid in roles.split(","):
if rid.strip() != "":
db.execute(dbo, "INSERT INTO userrole VALUES (%d, %d)" % (nuserid, int(rid)))
return nuserid
def create_document_media(dbo, username, linktype, linkid, template, content):
"""
Creates a new media record for a document for the link given.
linktype: ANIMAL, PERSON, etc
linkid: ID for the link
template: The name of the template used to create the document
content: The document contents
"""
mediaid = db.get_id(dbo, "media")
sql = db.make_insert_sql(
"media",
(("ID", db.di(mediaid)), ("MediaName", db.ds("%d.html" % mediaid)),
("MediaType", db.di(0)), ("MediaNotes", db.ds(template)),
("WebsitePhoto", db.di(0)), ("WebsiteVideo", db.di(0)),
("DocPhoto", db.di(0)), ("ExcludeFromPublish", db.di(0)),
("NewSinceLastPublish", db.di(1)),
("UpdatedSinceLastPublish", db.di(0)), ("LinkID", db.di(linkid)),
("LinkTypeID", db.di(linktype)), ("Date", db.nowsql())))
db.execute(dbo, sql)
path = ""
if linktype == ANIMAL:
path = "/animal"
elif linktype == PERSON:
path = "/owner"
elif linktype == LOSTANIMAL:
path = "/lostanimal"
elif linktype == FOUNDANIMAL:
path = "/foundanimal"
path += "/" + str(linkid)
name = str(mediaid) + ".html"
dbfs.put_string(dbo, name, path, content)
audit.create(dbo, username, "media",
str(mediaid) + ": for " + str(linkid) + "/" + str(linktype))
def insert_diary(dbo, username, linktypeid, linkid, diarydate, diaryfor, subject, note):
"""
Creates a diary note from the form data
username: User creating the diary
linktypeid, linkid: The link
diarydate: The date to stamp on the note (python format)
diaryfor: Who the diary note is for
subject, note
"""
linkinfo = ""
if linkid != 0:
linkinfo = get_link_info(dbo, linktypeid, linkid)
diaryid = db.get_id(dbo, "diary")
sql = db.make_insert_user_sql(dbo, "diary", username, (
( "ID", db.di(diaryid)),
( "LinkID", db.di(linkid) ),
( "LinkType", db.di(linktypeid) ),
( "LinkInfo", db.ds(linkinfo) ),
( "DiaryDateTime", db.dd(diarydate) ),
( "DiaryForName", db.ds(diaryfor) ),
( "Subject", db.ds(subject) ),
( "Note", db.ds(note) ),
( "DateCompleted", db.dd(None) )
))
db.execute(dbo, sql)
audit.create(dbo, username, "diary", str(diaryid))
return diaryid
def insert_user_from_form(dbo, username, post):
"""
Creates a user record from posted form data. Uses
the roles key (which should be a comma separated list of
role ids) to create userrole records.
"""
# Verify the username is unique
l = dbo.locale
if 0 != db.query_int(dbo, "SELECT COUNT(*) FROM users WHERE LOWER(UserName) LIKE LOWER(%s)" % post.db_string("username")):
raise utils.ASMValidationError(i18n._("Username '{0}' already exists", l).format(post["username"]))
nuserid = db.get_id(dbo, "users")
sql = db.make_insert_sql("users", (
( "ID", db.di(nuserid)),
( "UserName", post.db_string("username")),
( "RealName", post.db_string("realname")),
( "EmailAddress", post.db_string("email")),
( "Password", db.ds(hash_password(post["password"]))),
( "SuperUser", post.db_integer("superuser")),
( "RecordVersion", db.di(0)),
( "SecurityMap", db.ds("dummy")),
( "OwnerID", post.db_integer("person")),
( "SiteID", post.db_integer("site")),
( "LocationFilter", post.db_string("locationfilter")),
( "IPRestriction", post.db_string("iprestriction"))
))
db.execute(dbo, sql)
audit.create(dbo, username, "users", nuserid, audit.dump_row(dbo, "users", nuserid))
roles = post["roles"].strip()
if roles != "":
for rid in roles.split(","):
if rid.strip() != "":
db.execute(dbo, "INSERT INTO userrole VALUES (%d, %d)" % (nuserid, int(rid)))
return nuserid
def insert_user_from_form(dbo, username, data):
"""
Creates a user record from posted form data. Uses
the roles key (which should be a comma separated list of
role ids) to create userrole records.
"""
nuserid = db.get_id(dbo, "users")
sql = db.make_insert_sql(
"users",
(("ID", db.di(nuserid)), ("UserName", utils.df_t(data, "username")),
("RealName", utils.df_t(data, "realname")),
("EmailAddress", utils.df_t(data, "email")),
("Password", db.ds(hash_password(utils.df_ks(data, "password"),
True))),
("SuperUser", utils.df_s(data, "superuser")),
("RecordVersion", db.di(0)), ("SecurityMap", db.ds("dummy")),
("OwnerID", utils.df_s(data, "person")),
("LocationFilter", utils.df_t(data, "locationfilter")),
("IPRestriction", utils.df_t(data, "iprestriction"))))
db.execute(dbo, sql)
audit.create(dbo, username, "users", str(nuserid))
roles = utils.df_ks(data, "roles").strip()
if roles != "":
for rid in roles.split(","):
if rid.strip() != "":
db.execute(
dbo, "INSERT INTO userrole VALUES (%d, %d)" %
(nuserid, int(rid)))
return nuserid
def insert_lookup(dbo, lookup, name, desc="", speciesid=0, pfbreed="", pfspecies="", defaultcost=0):
t = LOOKUP_TABLES[lookup]
sql = ""
nid = 0
if lookup == "breed":
nid = db.get_id(dbo, "breed")
sql = "INSERT INTO breed (ID, BreedName, BreedDescription, PetFinderBreed, SpeciesID) VALUES (%s, %s, %s, %s, %s)" % (
db.di(nid), db.ds(name), db.ds(desc), db.ds(pfbreed), db.di(speciesid))
elif lookup == "species":
nid = db.get_id(dbo, "species")
sql = "INSERT INTO species (ID, SpeciesName, SpeciesDescription, PetFinderSpecies) VALUES (%s, %s, %s, %s)" % (
db.di(nid), db.ds(name), db.ds(desc), db.ds(pfspecies))
elif lookup == "donationtype" or lookup == "costtype" or lookup == "testtype" or lookup == "voucher" or lookup == "vaccinationtype":
nid = db.get_id(dbo, lookup)
sql = "INSERT INTO %s (ID, %s, %s, DefaultCost) VALUES (%s, %s, %s, %s)" % (
lookup, t[LOOKUP_NAMEFIELD], t[LOOKUP_DESCFIELD], db.di(nid), db.ds(name), db.ds(desc), db.ds(defaultcost))
# Create a matching account for the donation type
financial.insert_account_from_donationtype(dbo, nid, name, desc)
elif t[LOOKUP_DESCFIELD] == "":
# No description
nid = db.get_id(dbo, lookup)
sql = "INSERT INTO %s (ID, %s) VALUES (%s, %s)" % (
lookup, t[LOOKUP_NAMEFIELD], db.di(nid), db.ds(name))
else:
# Name/Description
nid = db.get_id(dbo, lookup)
sql = "INSERT INTO %s (ID, %s, %s) VALUES (%s, %s, %s)" % (
lookup, t[LOOKUP_NAMEFIELD], t[LOOKUP_DESCFIELD], db.di(nid), db.ds(name), db.ds(desc))
db.execute(dbo, sql)
return nid
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 update_user_settings(dbo, username, email = "", realname = "", locale = "", theme = ""):
userid = db.query_int(dbo, "SELECT ID FROM users WHERE Username = '******'" % username)
sql = db.make_update_sql("users", "ID=%d" % userid, (
( "RealName", db.ds(realname) ),
( "EmailAddress", db.ds(email) ),
( "ThemeOverride", db.ds(theme) ),
( "LocaleOverride", db.ds(locale) )
))
preaudit = db.query(dbo, "SELECT * FROM users WHERE ID = %d" % int(userid))[0]
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM users WHERE ID = %d" % int(userid))[0]
audit.edit(dbo, username, "users", audit.map_diff(preaudit, postaudit, [ "USERNAME", ]))
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 cset(dbo, key, value = "", ignoreDBLock = False):
"""
Update a configuration item in the table.
"""
# MySQL returns wrong affected value (AFFECTED_ROWS switch in newer), delete before insert
if dbo.dbtype == "MYSQL":
db.execute(dbo, "DELETE FROM configuration WHERE ItemName LIKE %s" % db.ds(key), ignoreDBLock)
db.execute(dbo, "INSERT INTO configuration (ItemName, ItemValue) VALUES (%s, %s)" % (db.ds(key), db.ds(value)), ignoreDBLock)
else:
# Otherwise, attempt the update and if no rows matched, do the insert
affected = db.execute(dbo, "UPDATE configuration SET ItemValue = %s WHERE ItemName LIKE %s" % (db.ds(value), db.ds(key)))
if affected == 0:
db.execute(dbo, "INSERT INTO configuration VALUES (%s, %s)" % ( db.ds(key), db.ds(value) ), ignoreDBLock)
def cset(dbo, key, value = "", ignoreDBLock = False):
"""
Update a configuration item in the table.
"""
# Use MySQL ON DUPLICATE KEY UPDATE to do it in one query if on MySQL
if dbo.dbtype == "MYSQL":
db.execute(dbo, "INSERT INTO configuration (ItemName, ItemValue) VALUES (%s, %s) ON DUPLICATE KEY UPDATE ItemValue = VALUES(ItemName)" % \
(db.ds(key), db.ds(value)), ignoreDBLock)
else:
# Otherwise, attempt the update and if no rows matched, do the insert
affected = db.execute(dbo, "UPDATE configuration SET ItemValue = %s WHERE ItemName LIKE %s" % (db.ds(value), db.ds(key)))
if affected == 0:
db.execute(dbo, "INSERT INTO configuration VALUES (%s, %s)" % ( db.ds(key), db.ds(value) ), ignoreDBLock)
def add_message(dbo, createdby, email, message, forname = "*", priority = 0, expires = add_days(now(), 7), added = now()):
l = dbo.locale
db.execute(dbo, db.make_insert_sql("messages", (
( "ID", db.di(db.get_id(dbo, "messages"))),
( "Added", db.dd(added)),
( "Expires", db.dd(expires)),
( "CreatedBy", db.ds(createdby)),
( "Priority", db.di(priority)),
( "ForName", db.ds(forname)),
( "Message", db.ds(message)))))
# If email is set, we email the message to everyone that it would match
if email == 1:
utils.send_user_email(dbo, createdby, forname, _("Message from {0}", l).format(createdby), message)
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 insert_account_from_donationtype(dbo, dtid, name, desc):
"""
Creates an account from a donation type record
"""
l = dbo.locale
aid = db.get_id(dbo, "accounts")
acode = i18n._("Income::", l) + name.replace(" ", "")
sql = db.make_insert_user_sql(dbo, "accounts", "system",
(("ID", db.di(aid)), ("Code", db.ds(acode)),
("AccountType", db.di(INCOME)),
("DonationTypeID", db.di(dtid)),
("Description", db.ds(desc))))
db.execute(dbo, sql)
audit.create(dbo, "system", "accounts", str(aid))
def insert_onlineform_from_form(dbo, username, data):
"""
Create an onlineform record from posted data
"""
formid = db.get_id(dbo, "onlineform")
sql = db.make_insert_sql(
"onlineform",
(("ID", db.di(formid)), ("Name", db.ds(utils.df_ks(data, "name"))),
("RedirectUrlAfterPOST", db.ds(utils.df_ks(data, "redirect"))),
("SetOwnerFlags", db.ds(utils.df_ks(data, "flags"))),
("Description", db.ds(utils.df_ks(data, "description")))))
db.execute(dbo, sql)
audit.create(dbo, username, "onlineform", str(formid))
return formid
def update_onlineform_from_form(dbo, username, data):
"""
Update an onlineform record from posted data
"""
formid = utils.df_ki(data, "formid")
sql = db.make_update_sql("onlineform", "ID=%d" % formid, (
( "Name", db.ds(utils.df_ks(data, "name"))),
( "RedirectUrlAfterPOST", db.ds(utils.df_ks(data, "redirect"))),
( "SetOwnerFlags", db.ds(utils.df_ks(data, "flags"))),
( "Description", db.ds(utils.df_ks(data, "description")))
))
preaudit = db.query(dbo, "SELECT * FROM onlineform WHERE ID = %d" % formid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM onlineform WHERE ID = %d" % formid)
audit.edit(dbo, username, "onlineform", audit.map_diff(preaudit, postaudit))
def insert_onlineform_from_form(dbo, username, data):
"""
Create an onlineform record from posted data
"""
formid = db.get_id(dbo, "onlineform")
sql = db.make_insert_sql("onlineform", (
( "ID", db.di(formid)),
( "Name", db.ds(utils.df_ks(data, "name"))),
( "RedirectUrlAfterPOST", db.ds(utils.df_ks(data, "redirect"))),
( "SetOwnerFlags", db.ds(utils.df_ks(data, "flags"))),
( "Description", db.ds(utils.df_ks(data, "description")))
))
db.execute(dbo, sql)
audit.create(dbo, username, "onlineform", str(formid))
return formid
def add_message(dbo, createdby, email, message, forname = "*", priority = 0, expires = add_days(now(), 7), added = now()):
l = dbo.locale
mid = db.get_id(dbo, "messages")
db.execute(dbo, db.make_insert_sql("messages", (
( "ID", db.di(mid)),
( "Added", db.dd(added)),
( "Expires", db.dd(expires)),
( "CreatedBy", db.ds(createdby)),
( "Priority", db.di(priority)),
( "ForName", db.ds(forname)),
( "Message", db.ds(message)))))
# If email is set, we email the message to everyone that it would match
if email == 1:
utils.send_user_email(dbo, createdby, forname, _("Message from {0}", l).format(createdby), message)
return mid
def update_latlong(dbo, personid, latlong):
"""
Updates the latlong field.
"""
db.execute(
dbo, "UPDATE owner SET LatLong = %s WHERE ID = %d" %
(db.ds(latlong), int(personid)))
def save_values_for_link(dbo, post, linkid, linktype="animal"):
"""
Saves incoming additional field values from a form, clearing any
existing values first.
"""
delete_values_for_link(dbo, linkid, linktype)
af = get_field_definitions(dbo, linktype)
l = dbo.locale
for f in af:
key = "a." + str(f["MANDATORY"]) + "." + str(f["ID"])
if post.has_key(key):
val = post[key]
if f["FIELDTYPE"] == YESNO:
val = str(post.boolean(key))
elif f["FIELDTYPE"] == MONEY:
val = str(post.integer(key))
elif f["FIELDTYPE"] == DATE:
if len(val.strip()) > 0 and post.date(key) == None:
raise utils.ASMValidationError(
_(
"Additional date field '{0}' contains an invalid date.",
l).format(f["FIELDNAME"]))
val = python2display(dbo.locale, post.date(key))
sql = db.make_insert_sql("additional",
(("LinkType", db.di(f["LINKTYPE"])),
("LinkID", db.di(int(linkid))),
("AdditionalFieldID", db.di(f["ID"])),
("Value", db.ds(val))))
try:
db.execute(dbo, sql)
except Exception, err:
al.error("Failed saving additional field: %s" % str(err),
"animal.update_animal_from_form", dbo, sys.exc_info())
def attach_link_from_form(dbo, username, linktype, linkid, data):
"""
Attaches a link to a web resource from a form
"""
existingvid = db.query_int(dbo, "SELECT COUNT(*) FROM media WHERE WebsiteVideo = 1 " \
"AND LinkID = %d AND LinkTypeID = %d" % ( int(linkid), int(linktype) ))
defvid = 0
if existingvid == 0 and utils.df_ki(data,
"linktype") == MEDIATYPE_VIDEO_LINK:
defvid = 1
mediaid = db.get_id(dbo, "media")
url = utils.df_ks(data, "linktarget")
if url.find("://") == -1:
url = "http://" + url
sql = db.make_insert_sql(
"media",
(("ID", db.di(mediaid)), ("MediaName", db.ds(url)),
("MediaType", utils.df_s(data, "linktype")),
("MediaNotes", utils.df_t(data, "comments")),
("WebsitePhoto", db.di(0)), ("WebsiteVideo", db.di(defvid)),
("DocPhoto", db.di(0)), ("ExcludeFromPublish", db.di(0)),
("NewSinceLastPublish", db.di(1)),
("UpdatedSinceLastPublish", db.di(0)), ("LinkID", db.di(linkid)),
("LinkTypeID", db.di(linktype)), ("Date", db.nowsql())))
db.execute(dbo, sql)
audit.create(
dbo, username, "media",
str(mediaid) + ": for " + str(linkid) + "/" + str(linktype) +
": link to " + utils.df_ks(data, "linktarget"))
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 save_values_for_link(dbo, post, linkid, linktype = "animal"):
"""
Saves incoming additional field values from a form, clearing any
existing values first.
"""
delete_values_for_link(dbo, linkid, linktype)
af = get_field_definitions(dbo, linktype)
l = dbo.locale
for f in af:
key = "a." + str(f["MANDATORY"]) + "." + str(f["ID"])
if post.has_key(key):
val = post[key]
if f["FIELDTYPE"] == YESNO:
val = str(post.boolean(key))
elif f["FIELDTYPE"] == MONEY:
val = str(post.integer(key))
elif f["FIELDTYPE"] == DATE:
if len(val.strip()) > 0 and post.date(key) == None:
raise utils.ASMValidationError(_("Additional date field '{0}' contains an invalid date.", l).format(f["FIELDNAME"]))
val = python2display(dbo.locale, post.date(key))
sql = db.make_insert_sql("additional", (
( "LinkType", db.di(f["LINKTYPE"]) ),
( "LinkID", db.di(int(linkid)) ),
( "AdditionalFieldID", db.di(f["ID"]) ),
( "Value", db.ds(val) ) ))
try:
db.execute(dbo, sql)
except Exception,err:
al.error("Failed saving additional field: %s" % str(err), "animal.update_animal_from_form", dbo, sys.exc_info())
def attach_link_from_form(dbo, username, linktype, linkid, data):
"""
Attaches a link to a web resource from a form
"""
existingvid = db.query_int(dbo, "SELECT COUNT(*) FROM media WHERE WebsiteVideo = 1 " \
"AND LinkID = %d AND LinkTypeID = %d" % ( int(linkid), int(linktype) ))
defvid = 0
if existingvid == 0 and utils.df_ki(data, "linktype") == MEDIATYPE_VIDEO_LINK:
defvid = 1
mediaid = db.get_id(dbo, "media")
url = utils.df_ks(data, "linktarget")
if url.find("://") == -1:
url = "http://" + url
sql = db.make_insert_sql("media", (
( "ID", db.di(mediaid) ),
( "MediaName", db.ds(url) ),
( "MediaType", utils.df_s(data, "linktype") ),
( "MediaNotes", utils.df_t(data, "comments") ),
( "WebsitePhoto", db.di(0) ),
( "WebsiteVideo", db.di(defvid) ),
( "DocPhoto", db.di(0) ),
( "ExcludeFromPublish", db.di(0) ),
( "NewSinceLastPublish", db.di(1) ),
( "UpdatedSinceLastPublish", db.di(0) ),
( "LinkID", db.di(linkid) ),
( "LinkTypeID", db.di(linktype) ),
( "Date", db.nowsql() )
))
db.execute(dbo, sql)
audit.create(dbo, username, "media", str(mediaid) + ": for " + str(linkid) + "/" + str(linktype) + ": link to " + utils.df_ks(data, "linktarget"))
def update_dispatch_latlong(dbo, incidentid, latlong):
"""
Updates the dispatch latlong field.
"""
db.execute(
dbo, "UPDATE animalcontrol SET DispatchLatLong = %s WHERE ID = %d" %
(db.ds(latlong), int(incidentid)))
def reschedule_vaccination(dbo, username, vaccinationid, newdays):
"""
Marks a vaccination completed today (if it's not already completed)
and reschedules it for given + newdays onwards.
"""
av = db.query(
dbo, "SELECT * FROM animalvaccination WHERE ID = %d" %
int(vaccinationid))[0]
given = av["DATEOFVACCINATION"]
if given is None:
given = now(dbo.timezone)
db.execute(
dbo,
"UPDATE animalvaccination SET DateOfVaccination = %s WHERE ID = %d"
% (db.dd(now(dbo.timezone)), int(vaccinationid)))
audit.edit(dbo, username, "animalvaccination",
str(vaccinationid) + " => given")
nvaccid = db.get_id(dbo, "animalvaccination")
db.execute(
dbo,
db.make_insert_user_sql(
dbo, "animalvaccination", username,
(("ID", db.di(nvaccid)), ("AnimalID", db.di(av["ANIMALID"])),
("VaccinationID", db.di(av["VACCINATIONID"])),
("DateOfVaccination", db.dd(None)),
("DateRequired", db.dd(add_days(given, int(newdays)))),
("Cost", db.di(av["COST"])),
("Comments", db.ds(av["COMMENTS"])))))
audit.create(dbo, username, "animalvaccination", str(nvaccid))
def attach_link_from_form(dbo, username, linktype, linkid, post):
"""
Attaches a link to a web resource from a form
"""
existingvid = db.query_int(dbo, "SELECT COUNT(*) FROM media WHERE WebsiteVideo = 1 " \
"AND LinkID = %d AND LinkTypeID = %d" % ( int(linkid), int(linktype) ))
defvid = 0
if existingvid == 0 and post.integer("linktype") == MEDIATYPE_VIDEO_LINK:
defvid = 1
mediaid = db.get_id(dbo, "media")
url = post["linktarget"]
if url.find("://") == -1:
url = "http://" + url
al.debug("attached link %s" % url, "media.attach_file_from_form")
sql = db.make_insert_sql("media", (
( "ID", db.di(mediaid) ),
( "MediaName", db.ds(url) ),
( "MediaType", post.db_integer("linktype") ),
( "MediaNotes", post.db_string("comments") ),
( "WebsitePhoto", db.di(0) ),
( "WebsiteVideo", db.di(defvid) ),
( "DocPhoto", db.di(0) ),
( "ExcludeFromPublish", db.di(0) ),
# ASM2_COMPATIBILITY
( "NewSinceLastPublish", db.di(1) ),
( "UpdatedSinceLastPublish", db.di(0) ),
# ASM2_COMPATIBILITY
( "LinkID", db.di(linkid) ),
( "LinkTypeID", db.di(linktype) ),
( "Date", db.nowsql() )
))
db.execute(dbo, sql)
audit.create(dbo, username, "media", mediaid, str(mediaid) + ": for " + str(linkid) + "/" + str(linktype) + ": link to " + post["linktarget"])
def insert_account_from_donationtype(dbo, dtid, name, desc):
"""
Creates an account from a donation type record
"""
l = dbo.locale
aid = db.get_id(dbo, "accounts")
acode = i18n._("Income::", l) + name.replace(" ", "")
sql = db.make_insert_user_sql(dbo, "accounts", "system", (
( "ID", db.di(aid)),
( "Code", db.ds(acode)),
( "AccountType", db.di(INCOME)),
( "DonationTypeID", db.di(dtid)),
( "Description", db.ds(desc))
))
db.execute(dbo, sql)
audit.create(dbo, "system", "accounts", str(aid))
def update_media_notes(dbo, username, mid, notes):
sql = db.make_update_sql("media", "ID=%d" % int(mid), (
( "MediaNotes", db.ds(notes)),
( "MediaName", "MediaName" ),
( "UpdatedSinceLastPublish", db.di(1)),
))
db.execute(dbo, sql)
audit.edit(dbo, username, "media", str(mid) + "notes => " + notes)
def update_media_notes(dbo, username, mid, notes):
sql = db.make_update_sql("media", "ID=%d" % int(mid), (
("MediaNotes", db.ds(notes)),
("MediaName", "MediaName"),
("UpdatedSinceLastPublish", db.di(1)),
))
db.execute(dbo, sql)
audit.edit(dbo, username, "media", str(mid) + "notes => " + notes)
def update_onlineform_from_form(dbo, username, data):
"""
Update an onlineform record from posted data
"""
formid = utils.df_ki(data, "formid")
sql = db.make_update_sql(
"onlineform", "ID=%d" % formid,
(("Name", db.ds(utils.df_ks(data, "name"))),
("RedirectUrlAfterPOST", db.ds(utils.df_ks(data, "redirect"))),
("SetOwnerFlags", db.ds(utils.df_ks(data, "flags"))),
("Description", db.ds(utils.df_ks(data, "description")))))
preaudit = db.query(dbo, "SELECT * FROM onlineform WHERE ID = %d" % formid)
db.execute(dbo, sql)
postaudit = db.query(dbo,
"SELECT * FROM onlineform WHERE ID = %d" % formid)
audit.edit(dbo, username, "onlineform",
audit.map_diff(preaudit, postaudit))
def update_onlineformfield_from_form(dbo, username, data):
"""
Update an onlineformfield record from posted data
"""
formfieldid = utils.df_ki(data, "formfieldid")
sql = db.make_update_sql("onlineformfield", "ID=%d" % formfieldid, (
( "FieldName", db.ds(utils.df_ks(data, "fieldname"))),
( "FieldType", db.di(utils.df_ki(data, "fieldtype"))),
( "Label", db.ds(utils.df_ks(data, "label"))),
( "DisplayIndex", db.di(utils.df_ki(data, "displayindex"))),
( "Lookups", db.ds(utils.df_ks(data, "lookups"))),
( "Tooltip", db.ds(utils.df_ks(data, "tooltip")))
))
preaudit = db.query(dbo, "SELECT * FROM onlineformfield WHERE ID = %d" % formfieldid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM onlineformfield WHERE ID = %d" % formfieldid)
audit.edit(dbo, username, "onlineformfield", audit.map_diff(preaudit, postaudit))
def insert_onlineformfield_from_form(dbo, username, data):
"""
Create an onlineformfield record from posted data
"""
formfieldid = db.get_id(dbo, "onlineformfield")
sql = db.make_insert_sql(
"onlineformfield",
(("ID", db.di(formfieldid)),
("OnlineFormID", db.di(utils.df_ki(data, "formid"))),
("FieldName", db.ds(utils.df_ks(data, "fieldname"))),
("FieldType", db.di(utils.df_ki(data, "fieldtype"))),
("Label", db.ds(utils.df_ks(data, "label"))),
("DisplayIndex", db.di(utils.df_ki(data, "displayindex"))),
("Lookups", db.ds(utils.df_ks(data, "lookups"))),
("Tooltip", db.ds(utils.df_ks(data, "tooltip")))))
db.execute(dbo, sql)
audit.create(dbo, username, "onlineformfield", str(formfieldid))
return formfieldid
def insert_onlineformfield_from_form(dbo, username, data):
"""
Create an onlineformfield record from posted data
"""
formfieldid = db.get_id(dbo, "onlineformfield")
sql = db.make_insert_sql("onlineformfield", (
( "ID", db.di(formfieldid)),
( "OnlineFormID", db.di(utils.df_ki(data, "formid"))),
( "FieldName", db.ds(utils.df_ks(data, "fieldname"))),
( "FieldType", db.di(utils.df_ki(data, "fieldtype"))),
( "Label", db.ds(utils.df_ks(data, "label"))),
( "DisplayIndex", db.di(utils.df_ki(data, "displayindex"))),
( "Lookups", db.ds(utils.df_ks(data, "lookups"))),
( "Tooltip", db.ds(utils.df_ks(data, "tooltip")))
))
db.execute(dbo, sql)
audit.create(dbo, username, "onlineformfield", str(formfieldid))
return formfieldid
def create_blank_document_media(dbo, username, linktype, linkid):
"""
Creates a new media record for a blank document for the link given.
linktype: ANIMAL, PERSON, etc
linkid: ID for the link
returns the new media id
"""
mediaid = db.get_id(dbo, "media")
sql = db.make_insert_sql(
"media",
(
("ID", db.di(mediaid)),
("MediaName", db.ds("%d.html" % mediaid)),
("MediaType", db.di(0)),
("MediaNotes", db.ds("New document")),
("WebsitePhoto", db.di(0)),
("WebsiteVideo", db.di(0)),
("DocPhoto", db.di(0)),
("ExcludeFromPublish", db.di(0)),
# ASM2_COMPATIBILITY
("NewSinceLastPublish", db.di(1)),
("UpdatedSinceLastPublish", db.di(0)),
# ASM2_COMPATIBILITY
("LinkID", db.di(linkid)),
("LinkTypeID", db.di(linktype)),
("Date", db.nowsql())))
db.execute(dbo, sql)
path = ""
if linktype == ANIMAL:
path = "/animal"
elif linktype == PERSON:
path = "/owner"
elif linktype == LOSTANIMAL:
path = "/lostanimal"
elif linktype == FOUNDANIMAL:
path = "/foundanimal"
path += "/" + str(linkid)
name = str(mediaid) + ".html"
dbfs.put_string(dbo, name, path, "")
audit.create(dbo, username, "media",
str(mediaid) + ": for " + str(linkid) + "/" + str(linktype))
return mediaid
def add_log(dbo, username, linktype, linkid, logtypeid, logtext):
logid = db.get_id(dbo, "log")
sql = db.make_insert_user_sql(
dbo, "log", username,
(("ID", db.di(logid)), ("LogTypeID", db.di(logtypeid)),
("LinkID", db.di(linkid)), ("LinkType", db.di(linktype)),
("Date", db.dd(i18n.now(dbo.timezone))),
("Comments", db.ds(logtext))))
db.execute(dbo, sql)
audit.create(dbo, username, "log", str(logid))
return logid
def update_onlineformfield_from_form(dbo, username, data):
"""
Update an onlineformfield record from posted data
"""
formfieldid = utils.df_ki(data, "formfieldid")
sql = db.make_update_sql(
"onlineformfield", "ID=%d" % formfieldid,
(("FieldName", db.ds(utils.df_ks(data, "fieldname"))),
("FieldType", db.di(utils.df_ki(data, "fieldtype"))),
("Label", db.ds(utils.df_ks(data, "label"))),
("DisplayIndex", db.di(utils.df_ki(data, "displayindex"))),
("Lookups", db.ds(utils.df_ks(data, "lookups"))),
("Tooltip", db.ds(utils.df_ks(data, "tooltip")))))
preaudit = db.query(
dbo, "SELECT * FROM onlineformfield WHERE ID = %d" % formfieldid)
db.execute(dbo, sql)
postaudit = db.query(
dbo, "SELECT * FROM onlineformfield WHERE ID = %d" % formfieldid)
audit.edit(dbo, username, "onlineformfield",
audit.map_diff(preaudit, postaudit))
def df_t(data, field):
""" Returns a posted text field for the database, turns it from unicode into
ascii with XML entities to represent codepoints > 128 """
if data.has_key(field):
if type(data[field]) == str:
s = unicode(data[field], "utf8").encode("ascii", "xmlcharrefreplace")
else:
s = data[field].encode("ascii", "xmlcharrefreplace")
return db.ds(s.strip())
else:
return "''"
def df_t(data, field):
""" Returns a posted text field for the database, turns it from unicode into
ascii with XML entities to represent codepoints > 128 """
if data.has_key(field):
if type(data[field]) == str:
s = unicode(data[field], "utf8").encode("ascii",
"xmlcharrefreplace")
else:
s = data[field].encode("ascii", "xmlcharrefreplace")
return db.ds(s.strip())
else:
return "''"
def update_pass_homecheck(dbo, user, personid, comments):
"""
Marks a person as homechecked and appends any comments supplied to their record.
"""
by = users.get_personid(dbo, user)
if by != 0:
db.execute(dbo, "UPDATE owner SET HomeCheckedBy = %d WHERE ID = %d" % (by, personid))
db.execute(dbo, "UPDATE owner SET IDCheck = 1, DateLastHomeChecked = %s WHERE ID = %d" % ( db.dd(now(dbo.timezone)), personid ))
if comments != "":
com = db.query_string(dbo, "SELECT Comments FROM owner WHERE ID = %d" % personid)
com += "\n" + comments
db.execute(dbo, "UPDATE owner SET Comments = %s WHERE ID = %d" % ( db.ds(com), personid ))
def add_log(dbo, username, linktype, linkid, logtypeid, logtext):
logid = db.get_id(dbo, "log")
sql = db.make_insert_user_sql(dbo, "log", username, (
( "ID", db.di(logid) ),
( "LogTypeID", db.di(logtypeid) ),
( "LinkID", db.di(linkid) ),
( "LinkType", db.di(linktype) ),
( "Date", db.dd(i18n.now(dbo.timezone)) ),
( "Comments", db.ds(logtext) )
))
db.execute(dbo, sql)
audit.create(dbo, username, "log", str(logid))
def create_document_media(dbo, username, linktype, linkid, template, content):
"""
Creates a new media record for a document for the link given.
linktype: ANIMAL, PERSON, etc
linkid: ID for the link
template: The name of the template used to create the document
content: The document contents
"""
mediaid = db.get_id(dbo, "media")
sql = db.make_insert_sql("media", (
( "ID", db.di(mediaid) ),
( "MediaName", db.ds("%d.html" % mediaid) ),
( "MediaType", db.di(0)),
( "MediaNotes", db.ds(template) ),
( "WebsitePhoto", db.di(0) ),
( "WebsiteVideo", db.di(0) ),
( "DocPhoto", db.di(0) ),
( "ExcludeFromPublish", db.di(0) ),
# ASM2_COMPATIBILITY
( "NewSinceLastPublish", db.di(1) ),
( "UpdatedSinceLastPublish", db.di(0) ),
# ASM2_COMPATIBILITY
( "LinkID", db.di(linkid) ),
( "LinkTypeID", db.di(linktype) ),
( "Date", db.nowsql() )
))
db.execute(dbo, sql)
path = ""
if linktype == ANIMAL:
path = "/animal"
elif linktype == PERSON:
path = "/owner"
elif linktype == LOSTANIMAL:
path = "/lostanimal"
elif linktype == FOUNDANIMAL:
path = "/foundanimal"
path += "/" + str(linkid)
name = str(mediaid) + ".html"
dbfs.put_string(dbo, name, path, content)
audit.create(dbo, username, "media", mediaid, str(mediaid) + ": for " + str(linkid) + "/" + str(linktype))
def insert_movement_from_form(dbo, username, data):
"""
Creates a movement record from posted form data
"""
movementid = db.get_id(dbo, "adoption")
adoptionno = utils.df_ks(data, "adoptionno")
animalid = utils.df_ki(data, "animal")
if adoptionno == "":
# No adoption number was supplied, generate a
# unique number from the movementid
idx = movementid
while True:
adoptionno = utils.padleft(idx, 6)
data["adoptionno"] = adoptionno
if 0 == db.query_int(
dbo,
"SELECT COUNT(*) FROM adoption WHERE AdoptionNumber LIKE '%s'"
% adoptionno):
break
else:
idx += 1
validate_movement_form_data(dbo, data)
l = dbo.locale
sql = db.make_insert_user_sql(
dbo, "adoption", username,
(("ID", db.di(movementid)), ("AdoptionNumber", db.ds(adoptionno)),
("OwnerID", db.di(utils.df_ki(data, "person"))),
("RetailerID", db.di(utils.df_ki(data, "retailer"))),
("AnimalID", db.di(utils.df_ki(data, "animal"))),
("OriginalRetailerMovementID",
db.di(utils.df_ki(data, "originalretailermovement"))),
("MovementDate", utils.df_d(data, "movementdate", l)),
("MovementType", utils.df_s(data, "type")),
("ReturnDate", utils.df_d(data, "returndate", l)),
("ReturnedReasonID", utils.df_s(data, "returncategory")),
("Donation", utils.df_m(data, "donation", l)),
("InsuranceNumber", utils.df_t(data, "insurance")),
("ReasonForReturn", utils.df_t(data, "reason")),
("ReservationDate", utils.df_d(data, "reservationdate", l)),
("ReservationCancelledDate",
utils.df_d(data, "reservationcancelled",
l)), ("IsTrial", utils.df_c(data, "trial")),
("IsPermanentFoster", utils.df_c(data, "permanentfoster")),
("TrialEndDate", utils.df_d(data, "trialenddate", l)),
("Comments", utils.df_t(data, "comments"))))
db.execute(dbo, sql)
audit.create(dbo, username, "adoption", str(movementid))
animal.update_animal_status(dbo, animalid)
animal.update_variable_animal_data(dbo, animalid)
update_movement_donation(dbo, movementid)
return movementid
def update_user_settings(dbo,
username,
email="",
realname="",
locale="",
theme=""):
userid = db.query_int(
dbo, "SELECT ID FROM users WHERE Username = '******'" % username)
sql = db.make_update_sql("users", "ID=%d" % userid,
(("RealName", db.ds(realname)),
("EmailAddress", db.ds(email)),
("ThemeOverride", db.ds(theme)),
("LocaleOverride", db.ds(locale))))
preaudit = db.query(dbo,
"SELECT * FROM users WHERE ID = %d" % int(userid))[0]
db.execute(dbo, sql)
postaudit = db.query(dbo,
"SELECT * FROM users WHERE ID = %d" % int(userid))[0]
audit.edit(dbo, username, "users",
audit.map_diff(preaudit, postaudit, [
"USERNAME",
]))
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 insert_treatments(dbo, username, amid, requireddate, isstart = True):
"""
Creates new treatment records for the given medical record
with the required date given. isstart says that the date passed
is the real start date, so don't look at the timing rule to
calculate the next date.
"""
am = db.query(dbo, "SELECT * FROM animalmedical WHERE ID = %d" % amid)[0]
nofreq = int(am["TIMINGRULENOFREQUENCIES"])
if not isstart:
if am["TIMINGRULEFREQUENCY"] == DAILY:
requireddate += datetime.timedelta(days=nofreq)
if am["TIMINGRULEFREQUENCY"] == WEEKLY:
requireddate += datetime.timedelta(days=nofreq*7)
if am["TIMINGRULEFREQUENCY"] == MONTHLY:
requireddate += datetime.timedelta(days=nofreq*31)
if am["TIMINGRULEFREQUENCY"] == YEARLY:
requireddate += datetime.timedelta(days=nofreq*365)
# Create correct number of records
norecs = am["TIMINGRULE"]
if norecs == 0: norecs = 1
for x in range(1, norecs+1):
sql = db.make_insert_user_sql(dbo, "animalmedicaltreatment", username, (
( "ID", db.di(db.get_id(dbo, "animalmedicaltreatment"))),
( "AnimalID", db.di(am["ANIMALID"]) ),
( "AnimalMedicalID", db.di(amid)),
( "DateRequired", db.dd(requireddate)),
( "DateGiven", db.dd(None)),
( "GivenBy", db.ds("")),
( "TreatmentNumber", db.di(x)),
( "TotalTreatments", db.di(norecs)),
( "Comments", db.ds(""))
))
db.execute(dbo, sql)
# Update the number of treatments given and remaining
calculate_given_remaining(dbo, amid)
def create_blank_document_media(dbo, username, linktype, linkid):
"""
Creates a new media record for a blank document for the link given.
linktype: ANIMAL, PERSON, etc
linkid: ID for the link
returns the new media id
"""
mediaid = db.get_id(dbo, "media")
sql = db.make_insert_sql("media", (
( "ID", db.di(mediaid) ),
( "MediaName", db.ds("%d.html" % mediaid) ),
( "MediaType", db.di(0)),
( "MediaNotes", db.ds("New document") ),
( "WebsitePhoto", db.di(0) ),
( "WebsiteVideo", db.di(0) ),
( "DocPhoto", db.di(0) ),
( "ExcludeFromPublish", db.di(0) ),
( "NewSinceLastPublish", db.di(1) ),
( "UpdatedSinceLastPublish", db.di(0) ),
( "LinkID", db.di(linkid) ),
( "LinkTypeID", db.di(linktype) ),
( "Date", db.nowsql() )
))
db.execute(dbo, sql)
path = ""
if linktype == ANIMAL:
path = "/animal"
elif linktype == PERSON:
path = "/owner"
elif linktype == LOSTANIMAL:
path = "/lostanimal"
elif linktype == FOUNDANIMAL:
path = "/foundanimal"
path += "/" + str(linkid)
name = str(mediaid) + ".html"
dbfs.put_string(dbo, name, path, "")
audit.create(dbo, username, "media", str(mediaid) + ": for " + str(linkid) + "/" + str(linktype))
return mediaid
def insert_stockusage(dbo, username, slid, diff, usagedate, usagetype, comments):
"""
Inserts a new stock usage record
"""
nid = db.get_id(dbo, "stockusage")
db.execute(dbo, db.make_insert_user_sql(dbo, "stockusage", username, (
( "ID", db.di(nid)),
( "StockUsageTypeID", db.di(usagetype) ),
( "StockLevelID", db.di(slid) ),
( "UsageDate", db.dd(usagedate) ),
( "Quantity", db.df(diff) ),
( "Comments", db.ds(comments) )
)))
audit.create(dbo, username, "stockusage", nid, audit.dump_row(dbo, "stockusage", nid))
def insert_diary(dbo, username, linktypeid, linkid, diarydate, diaryfor,
subject, note):
"""
Creates a diary note from the form data
username: User creating the diary
linktypeid, linkid: The link
diarydate: The date to stamp on the note (python format)
diaryfor: Who the diary note is for
subject, note
"""
linkinfo = ""
if linkid != 0:
linkinfo = get_link_info(dbo, linktypeid, linkid)
diaryid = db.get_id(dbo, "diary")
sql = db.make_insert_user_sql(
dbo, "diary", username,
(("ID", db.di(diaryid)), ("LinkID", db.di(linkid)),
("LinkType", db.di(linktypeid)), ("LinkInfo", db.ds(linkinfo)),
("DiaryDateTime", db.dd(diarydate)),
("DiaryForName", db.ds(diaryfor)), ("Subject", db.ds(subject)),
("Note", db.ds(note)), ("DateCompleted", db.dd(None))))
db.execute(dbo, sql)
audit.create(dbo, username, "diary", str(diaryid))
return diaryid
def create_additional_fields(dbo, row, errors, rowno, csvkey = "ANIMALADDITIONAL", linktype = "animal", linkid = 0):
# Identify any additional fields that may have been specified with
# ANIMALADDITIONAL<fieldname>
for a in additional.get_field_definitions(dbo, linktype):
v = gks(row, csvkey + str(a["FIELDNAME"]).upper())
if v != "":
sql = db.make_insert_sql("additional", (
( "LinkType", db.di(a["LINKTYPE"]) ),
( "LinkID", db.di(int(linkid)) ),
( "AdditionalFieldID", db.di(a["ID"]) ),
( "Value", db.ds(v) ) ))
try:
db.execute(dbo, sql)
except Exception,e:
errors.append( (rowno, str(row), str(e)) )
def insert_treatments(dbo, username, amid, requireddate, isstart=True):
"""
Creates new treatment records for the given medical record
with the required date given. isstart says that the date passed
is the real start date, so don't look at the timing rule to
calculate the next date.
"""
am = db.query(dbo, "SELECT * FROM animalmedical WHERE ID = %d" % amid)[0]
nofreq = int(am["TIMINGRULENOFREQUENCIES"])
if not isstart:
if am["TIMINGRULEFREQUENCY"] == DAILY:
requireddate += datetime.timedelta(days=nofreq)
if am["TIMINGRULEFREQUENCY"] == WEEKLY:
requireddate += datetime.timedelta(days=nofreq * 7)
if am["TIMINGRULEFREQUENCY"] == MONTHLY:
requireddate += datetime.timedelta(days=nofreq * 31)
if am["TIMINGRULEFREQUENCY"] == YEARLY:
requireddate += datetime.timedelta(days=nofreq * 365)
# Create correct number of records
norecs = am["TIMINGRULE"]
if norecs == 0: norecs = 1
for x in range(1, norecs + 1):
sql = db.make_insert_user_sql(
dbo, "animalmedicaltreatment", username,
(("ID", db.di(db.get_id(dbo, "animalmedicaltreatment"))),
("AnimalID", db.di(am["ANIMALID"])),
("AnimalMedicalID", db.di(amid)),
("DateRequired", db.dd(requireddate)), ("DateGiven", db.dd(None)),
("GivenBy", db.ds("")), ("TreatmentNumber", db.di(x)),
("TotalTreatments", db.di(norecs)), ("Comments", db.ds(""))))
db.execute(dbo, sql)
# Update the number of treatments given and remaining
calculate_given_remaining(dbo, amid)
def authenticate(dbo, username, password):
"""
Authenticates whether a username and password are valid.
Returns None if authentication failed, or a user row
"""
username = db.escape(username).replace("\\", "")
pypassword = hash_password(password)
javapassword = hash_password(password, True)
users = db.query(dbo, "SELECT * FROM users WHERE UPPER(UserName) LIKE UPPER(" + db.ds(username) + ")")
for u in users:
dbpass = u["PASSWORD"].strip()
if dbpass == pypassword or dbpass == javapassword:
return u
return None
def insert_movement_from_form(dbo, username, data):
"""
Creates a movement record from posted form data
"""
movementid = db.get_id(dbo, "adoption")
adoptionno = utils.df_ks(data, "adoptionno")
animalid = utils.df_ki(data, "animal")
if adoptionno == "":
# No adoption number was supplied, generate a
# unique number from the movementid
idx = movementid
while True:
adoptionno = utils.padleft(idx, 6)
data["adoptionno"] = adoptionno
if 0 == db.query_int(dbo, "SELECT COUNT(*) FROM adoption WHERE AdoptionNumber LIKE '%s'" % adoptionno):
break
else:
idx += 1
validate_movement_form_data(dbo, data)
l = dbo.locale
sql = db.make_insert_user_sql(dbo, "adoption", username, (
( "ID", db.di(movementid)),
( "AdoptionNumber", db.ds(adoptionno)),
( "OwnerID", db.di(utils.df_ki(data, "person"))),
( "RetailerID", db.di(utils.df_ki(data, "retailer"))),
( "AnimalID", db.di(utils.df_ki(data, "animal"))),
( "OriginalRetailerMovementID", db.di(utils.df_ki(data, "originalretailermovement"))),
( "MovementDate", utils.df_d(data, "movementdate", l)),
( "MovementType", utils.df_s(data, "type")),
( "ReturnDate", utils.df_d(data, "returndate", l)),
( "ReturnedReasonID", utils.df_s(data, "returncategory")),
( "Donation", utils.df_m(data, "donation", l)),
( "InsuranceNumber", utils.df_t(data, "insurance")),
( "ReasonForReturn", utils.df_t(data, "reason")),
( "ReservationDate", utils.df_d(data, "reservationdate", l)),
( "ReservationCancelledDate", utils.df_d(data, "reservationcancelled", l)),
( "IsTrial", utils.df_c(data, "trial")),
( "IsPermanentFoster", utils.df_c(data, "permanentfoster")),
( "TrialEndDate", utils.df_d(data, "trialenddate", l)),
( "Comments", utils.df_t(data, "comments"))
))
db.execute(dbo, sql)
audit.create(dbo, username, "adoption", str(movementid))
animal.update_animal_status(dbo, animalid)
animal.update_variable_animal_data(dbo, animalid)
update_movement_donation(dbo, movementid)
return movementid
def check_create_next_donation(dbo, username, odid):
"""
Checks to see if a donation is now received and the next in
a sequence needs to be created for donations with a frequency
"""
al.debug("Create next donation %d" % int(odid),
"financial.check_create_next_donation", dbo)
d = db.query(dbo, "SELECT * FROM ownerdonation WHERE ID = %d" % int(odid))
if d is None or len(d) == 0:
al.error("No donation found for %d" % int(odid),
"financial.check_create_next_donation", dbo)
return
d = d[0]
# If we have a frequency > 0, the nextcreated flag isn't set
# and there's a datereceived and due then we need to create the
# next donation in the sequence
if d["DATEDUE"] != None and d["DATE"] != None and d["FREQUENCY"] > 0 and d[
"NEXTCREATED"] == 0:
nextdue = d["DATEDUE"]
if d["FREQUENCY"] == 1:
nextdue = i18n.add_days(nextdue, 7)
if d["FREQUENCY"] == 2:
nextdue = i18n.add_months(nextdue, 1)
if d["FREQUENCY"] == 3:
nextdue = i18n.add_months(nextdue, 3)
if d["FREQUENCY"] == 4:
nextdue = i18n.add_years(nextdue, 1)
al.debug("Next donation due %s" % str(nextdue),
"financial.check_create_next_donation", dbo)
# Update nextcreated flag for this donation
db.execute(
dbo, "UPDATE ownerdonation SET NextCreated = 1 WHERE ID = %d" %
int(odid))
# Create the new donation due record
did = db.get_id(dbo, "ownerdonation")
sql = db.make_insert_user_sql(
dbo, "ownerdonation", username,
(("ID", db.di(did)), ("AnimalID", db.di(d["ANIMALID"])),
("OwnerID", db.di(d["OWNERID"])),
("MovementID", db.di(d["MOVEMENTID"])),
("DonationTypeID", db.di(d["DONATIONTYPEID"])),
("DateDue", db.dd(nextdue)), ("Date", db.dd(None)),
("Donation", db.di(d["DONATION"])),
("IsGiftAid", db.di(d["ISGIFTAID"])),
("DonationPaymentID", db.di(d["DONATIONPAYMENTID"])),
("Frequency", db.di(d["FREQUENCY"])), ("NextCreated", db.di(0)),
("Comments", db.ds(d["COMMENTS"]))))
db.execute(dbo, sql)
def authenticate(dbo, username, password):
"""
Authenticates whether a username and password are valid.
Returns None if authentication failed, or a user row
"""
username = db.escape(username).replace("\\", "")
pypassword = hash_password(password)
javapassword = hash_password(password, True)
users = db.query(
dbo, "SELECT * FROM users WHERE UPPER(UserName) LIKE UPPER(" +
db.ds(username) + ")")
for u in users:
dbpass = u["PASSWORD"].strip()
if dbpass == pypassword or dbpass == javapassword:
return u
return None
def update_treatment_today(dbo, username, amtid):
"""
Marks a treatment record as given today.
"""
amid = db.query_int(dbo, "SELECT AnimalMedicalID FROM animalmedicaltreatment WHERE ID = %d" % amtid)
db.execute(dbo, db.make_update_user_sql(dbo, "animalmedicaltreatment", username, "ID = %d" % amtid, (
( "DateGiven", db.dd(now(dbo.timezone)) ),
( "GivenBy", db.ds(username))
)))
audit.edit(dbo, username, "animalmedicaltreatment", "%d => given" % amtid)
# Update number of treatments given and remaining
calculate_given_remaining(dbo, amid)
# Generate next treatments in sequence or complete the
# medical record appropriately
update_medical_treatments(dbo, username, amid)
def check_create_next_donation(dbo, username, odid):
"""
Checks to see if a donation is now received and the next in
a sequence needs to be created for donations with a frequency
"""
al.debug("Create next donation %d" % int(odid), "financial.check_create_next_donation", dbo)
d = db.query(dbo, "SELECT * FROM ownerdonation WHERE ID = %d" % int(odid))
if d is None or len(d) == 0:
al.error("No donation found for %d" % int(odid), "financial.check_create_next_donation", dbo)
return
d = d[0]
# If we have a frequency > 0, the nextcreated flag isn't set
# and there's a datereceived and due then we need to create the
# next donation in the sequence
if d["DATEDUE"] != None and d["DATE"] != None and d["FREQUENCY"] > 0 and d["NEXTCREATED"] == 0:
nextdue = d["DATEDUE"]
if d["FREQUENCY"] == 1:
nextdue = i18n.add_days(nextdue, 7)
if d["FREQUENCY"] == 2:
nextdue = i18n.add_months(nextdue, 1)
if d["FREQUENCY"] == 3:
nextdue = i18n.add_months(nextdue, 3)
if d["FREQUENCY"] == 4:
nextdue = i18n.add_years(nextdue, 1)
al.debug("Next donation due %s" % str(nextdue), "financial.check_create_next_donation", dbo)
# Update nextcreated flag for this donation
db.execute(dbo, "UPDATE ownerdonation SET NextCreated = 1 WHERE ID = %d" % int(odid))
# Create the new donation due record
did = db.get_id(dbo, "ownerdonation")
sql = db.make_insert_user_sql(dbo, "ownerdonation", username, (
( "ID", db.di(did)),
( "AnimalID", db.di(d["ANIMALID"])),
( "OwnerID", db.di(d["OWNERID"])),
( "MovementID", db.di(d["MOVEMENTID"])),
( "DonationTypeID", db.di(d["DONATIONTYPEID"])),
( "DateDue", db.dd(nextdue)),
( "Date", db.dd(None)),
( "Donation", db.di(d["DONATION"])),
( "IsGiftAid", db.di(d["ISGIFTAID"])),
( "DonationPaymentID", db.di(d["DONATIONPAYMENTID"])),
( "Frequency", db.di(d["FREQUENCY"])),
( "NextCreated", db.di(0)),
( "Comments", db.ds(d["COMMENTS"]))
))
db.execute(dbo, sql)
def update_pass_homecheck(dbo, user, personid, comments):
"""
Marks a person as homechecked and appends any comments supplied to their record.
"""
by = users.get_personid(dbo, user)
if by != 0:
db.execute(
dbo, "UPDATE owner SET HomeCheckedBy = %d WHERE ID = %d" %
(by, personid))
db.execute(
dbo,
"UPDATE owner SET IDCheck = 1, DateLastHomeChecked = %s WHERE ID = %d"
% (db.dd(now(dbo.timezone)), personid))
if comments != "":
com = db.query_string(
dbo, "SELECT Comments FROM owner WHERE ID = %d" % personid)
com += "\n" + comments
db.execute(
dbo, "UPDATE owner SET Comments = %s WHERE ID = %d" %
(db.ds(com), personid))
def create_additional_fields(dbo,
row,
errors,
rowno,
csvkey="ANIMALADDITIONAL",
linktype="animal",
linkid=0):
# Identify any additional fields that may have been specified with
# ANIMALADDITIONAL<fieldname>
for a in additional.get_field_definitions(dbo, linktype):
v = gks(row, csvkey + str(a["FIELDNAME"]).upper())
if v != "":
sql = db.make_insert_sql("additional",
(("LinkType", db.di(a["LINKTYPE"])),
("LinkID", db.di(int(linkid))),
("AdditionalFieldID", db.di(a["ID"])),
("Value", db.ds(v))))
try:
db.execute(dbo, sql)
except Exception, e:
errors.append((rowno, str(row), str(e)))
def insert_diary_from_form(dbo, username, linktypeid, linkid, post):
"""
Creates a diary note from the form data
username: User creating the diary
linktypeid, linkid: The link
post: A PostedData object
"""
l = dbo.locale
if post["diarydate"] == "":
raise utils.ASMValidationError(i18n._("Diary date cannot be blank", l))
if post.date("diarydate") is None:
raise utils.ASMValidationError(i18n._("Diary date is not valid", l))
if post["subject"] == "":
raise utils.ASMValidationError(
i18n._("Diary subject cannot be blank", l))
if post["note"] == "":
raise utils.ASMValidationError(i18n._("Diary note cannot be blank", l))
diarytime = post["diarytime"].strip()
if diarytime != "":
if diarytime.find(":") == -1:
raise utils.ASMValidationError(
i18n._("Invalid time, times should be in HH:MM format", l))
if not utils.is_numeric(diarytime.replace(":", "")):
raise utils.ASMValidationError(
i18n._("Invalid time, times should be in HH:MM format", l))
linkinfo = get_link_info(dbo, linktypeid, linkid)
diaryid = db.get_id(dbo, "diary")
sql = db.make_insert_user_sql(
dbo, "diary", username,
(("ID", db.di(diaryid)), ("LinkID", db.di(linkid)),
("LinkType", db.di(linktypeid)), ("LinkInfo", db.ds(linkinfo)),
("DiaryDateTime", post.db_datetime("diarydate", "diarytime")),
("DiaryForName", post.db_string("diaryfor")),
("Subject", post.db_string("subject")),
("Note", post.db_string("note")),
("DateCompleted", post.db_date("completed"))))
db.execute(dbo, sql)
audit.create(dbo, username, "diary", str(diaryid))
return diaryid