def delete_person(dbo, username, personid):
"""
Deletes a person and all its satellite records.
"""
l = dbo.locale
if db.query_int(dbo, "SELECT COUNT(ID) FROM adoption WHERE OwnerID=%d OR RetailerID=%d" % (personid, personid)):
raise utils.ASMValidationError(_("This person has movements and cannot be removed.", l))
if db.query_int(dbo, "SELECT COUNT(ID) FROM animal WHERE BroughtInByOwnerID=%d OR OriginalOwnerID=%d OR CurrentVetID=%d OR OwnersVetID=%d" % (personid, personid, personid, personid)):
raise utils.ASMValidationError(_("This person is linked to an animal and cannot be removed.", l))
if db.query_int(dbo, "SELECT COUNT(ID) FROM ownerdonation WHERE OwnerID=%d" % personid):
raise utils.ASMValidationError(_("This person has donations and cannot be removed.", l))
animals = db.query(dbo, "SELECT AnimalID FROM adoption WHERE OwnerID = %d" % personid)
audit.delete(dbo, username, "owner", str(db.query(dbo, "SELECT * FROM owner WHERE ID=%d" % personid)))
db.execute(dbo, "DELETE FROM media WHERE LinkID = %d AND LinkTypeID = %d" % (personid, 1))
db.execute(dbo, "DELETE FROM diary WHERE LinkID = %d AND LinkType = %d" % (personid, 2))
db.execute(dbo, "DELETE FROM log WHERE LinkID = %d AND LinkType = %d" % (personid, 1))
db.execute(dbo, "DELETE FROM additional WHERE LinkID = %d AND LinkType IN (%s)" % (personid, additional.PERSON_IN))
db.execute(dbo, "DELETE FROM adoption WHERE OwnerID = %d" % personid)
db.execute(dbo, "DELETE FROM ownerdonation WHERE OwnerID = %d" % personid)
db.execute(dbo, "DELETE FROM ownervoucher WHERE OwnerID = %d" % personid)
dbfs.delete_path(dbo, "/owner/%d" % personid)
db.execute(dbo, "DELETE FROM owner WHERE ID = %d" % personid)
# Now that we've removed the person, update any animals that were previously
# attached to it so that they return to the shelter.
for a in animals:
animal.update_animal_status(dbo, int(a["ANIMALID"]))
animal.update_variable_animal_data(dbo, int(a["ANIMALID"]))
def insert_reserve_for_animal_name(dbo, username, personid, animalname):
"""
Creates a reservation for the animal with animalname to personid.
animalname can either be just the name of a shelter animal, or it
can be in the form name::code. If a code is present, that will be
used to locate the animal.
"""
l = dbo.locale
if animalname.find("::") != -1:
animalcode = animalname.split("::")[1]
aid = db.query_int(dbo, "SELECT ID FROM animal WHERE ShelterCode = %s ORDER BY ID DESC" % db.ds(animalcode))
else:
aid = db.query_int(dbo, "SELECT ID FROM animal WHERE LOWER(AnimalName) LIKE '%s' ORDER BY ID DESC" % animalname.lower())
# Bail out if we couldn't find a matching animal
if aid == 0: return
move_dict = {
"person" : str(personid),
"animal" : str(aid),
"reservationdate" : i18n.python2display(l, i18n.now(dbo.timezone)),
"reservationstatus" : configuration.default_reservation_status(dbo),
"movementdate" : "",
"type" : str(NO_MOVEMENT),
"returncategory" : configuration.default_return_reason(dbo)
}
return insert_movement_from_form(dbo, username, utils.PostedData(move_dict, l))
def check_default_web_doc_pic(dbo, mediaid, linkid, linktype):
"""
Checks if linkid/type has a default pic for the web or documents. If not,
sets mediaid to be the default.
"""
existing_web = db.query_int(dbo, "SELECT COUNT(*) FROM media WHERE WebsitePhoto = 1 " \
"AND LinkID = %d AND LinkTypeID = %d" % ( int(linkid), int(linktype) ))
existing_doc = db.query_int(dbo, "SELECT COUNT(*) FROM media WHERE DocPhoto = 1 " \
"AND LinkID = %d AND LinkTypeID = %d" % ( int(linkid), int(linktype) ))
if existing_web == 0:
db.execute(dbo, "UPDATE media SET WebsitePhoto = 1 WHERE ID = %d" % mediaid)
if existing_doc == 0:
db.execute(dbo, "UPDATE media SET DocPhoto = 1 WHERE ID = %d" % mediaid)
def delete_person(dbo, username, personid):
"""
Deletes a person and all its satellite records.
"""
l = dbo.locale
if db.query_int(
dbo,
"SELECT COUNT(ID) FROM adoption WHERE OwnerID=%d OR RetailerID=%d"
% (personid, personid)):
raise utils.ASMValidationError(
_("This person has movements and cannot be removed.", l))
if db.query_int(
dbo,
"SELECT COUNT(ID) FROM animal WHERE BroughtInByOwnerID=%d OR OriginalOwnerID=%d OR CurrentVetID=%d OR OwnersVetID=%d"
% (personid, personid, personid, personid)):
raise utils.ASMValidationError(
_("This person is linked to an animal and cannot be removed.", l))
if db.query_int(
dbo,
"SELECT COUNT(ID) FROM ownerdonation WHERE OwnerID=%d" % personid):
raise utils.ASMValidationError(
_("This person has donations and cannot be removed.", l))
animals = db.query(
dbo, "SELECT AnimalID FROM adoption WHERE OwnerID = %d" % personid)
audit.delete(
dbo, username, "owner",
str(db.query(dbo, "SELECT * FROM owner WHERE ID=%d" % personid)))
db.execute(
dbo, "DELETE FROM media WHERE LinkID = %d AND LinkTypeID = %d" %
(personid, 1))
db.execute(
dbo, "DELETE FROM diary WHERE LinkID = %d AND LinkType = %d" %
(personid, 2))
db.execute(
dbo,
"DELETE FROM log WHERE LinkID = %d AND LinkType = %d" % (personid, 1))
db.execute(
dbo, "DELETE FROM additional WHERE LinkID = %d AND LinkType IN (%s)" %
(personid, additional.PERSON_IN))
db.execute(dbo, "DELETE FROM adoption WHERE OwnerID = %d" % personid)
db.execute(dbo, "DELETE FROM ownerdonation WHERE OwnerID = %d" % personid)
db.execute(dbo, "DELETE FROM ownervoucher WHERE OwnerID = %d" % personid)
dbfs.delete_path(dbo, "/owner/%d" % personid)
db.execute(dbo, "DELETE FROM owner WHERE ID = %d" % personid)
# Now that we've removed the person, update any animals that were previously
# attached to it so that they return to the shelter.
for a in animals:
animal.update_animal_status(dbo, int(a["ANIMALID"]))
animal.update_variable_animal_data(dbo, int(a["ANIMALID"]))
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 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 sign_document(dbo, username, mid, sigurl, signdate):
"""
Signs an HTML document.
sigurl: An HTML5 data: URL containing an image of the signature
"""
SIG_PLACEHOLDER = "signature:placeholder"
date, medianame, mimetype, content = get_media_file_data(dbo, mid)
# Is this an HTML document?
if content.find("<p") == -1 and content.find("<td") == -1:
raise utils.ASMValidationError("Cannot sign a non-HTML document")
# Has this document already been signed?
if 0 != db.query_int(dbo, "SELECT COUNT(*) FROM media WHERE ID = %d AND SignatureHash Is Not Null AND SignatureHash <> ''" % mid):
raise utils.ASMValidationError("Document is already signed")
# Does the document have a signing placeholder image? If so, replace it
if content.find(SIG_PLACEHOLDER) != -1:
content = content.replace(SIG_PLACEHOLDER, sigurl)
else:
# Create the signature at the foot of the document
sig = "<hr />\n"
sig += '<p><img src="' + sigurl + '" /></p>\n'
sig += "<p>%s</p>\n" % signdate
content += sig
# Create a hash of the contents and store it with the media record
db.execute(dbo, "UPDATE media SET SignatureHash = '%s' WHERE ID = %d" % (utils.md5_hash(content), mid))
# Update the dbfs contents
update_file_content(dbo, username, mid, content)
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 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_account_from_form(dbo, username, post):
"""
Updates an account from posted form data
"""
l = dbo.locale
accountid = post.integer("accountid")
if post["code"] == "":
raise utils.ASMValidationError(i18n._("Account code cannot be blank.", l))
if 0 != db.query_int(dbo, "SELECT COUNT(*) FROM accounts WHERE Code Like '%s' AND ID <> %d" % (post["code"], accountid)):
raise utils.ASMValidationError(i18n._("Account code '{0}' has already been used.", l).format(post["code"]))
sql = db.make_update_user_sql(dbo, "accounts", username, "ID=%d" % accountid, (
( "Code", post.db_string("code")),
( "AccountType", post.db_integer("type")),
( "DonationTypeID", post.db_integer("donationtype")),
( "Description", post.db_string("description"))
))
preaudit = db.query(dbo, "SELECT * FROM accounts WHERE ID = %d" % accountid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM accounts WHERE ID = %d" % accountid)
audit.edit(dbo, username, "accounts", audit.map_diff(preaudit, postaudit))
db.execute(dbo, "DELETE FROM accountsrole WHERE AccountID = %d" % accountid)
for rid in post.integer_list("viewroles"):
db.execute(dbo, "INSERT INTO accountsrole (AccountID, RoleID, CanView, CanEdit) VALUES (%d, %d, 1, 0)" % (accountid, rid))
for rid in post.integer_list("editroles"):
if rid in post.integer_list("viewroles"):
db.execute(dbo, "UPDATE accountsrole SET CanEdit = 1 WHERE AccountID = %d AND RoleID = %d" % (accountid, rid))
else:
db.execute(dbo, "INSERT INTO accountsrole (AccountID, RoleID, CanView, CanEdit) VALUES (%d, %d, 0, 1)" % (accountid, rid))
def insert_account_from_form(dbo, username, post):
"""
Creates an account from posted form data
"""
l = dbo.locale
if post["code"] == "":
raise utils.ASMValidationError(i18n._("Account code cannot be blank.", l))
if 0 != db.query_int(dbo, "SELECT COUNT(*) FROM accounts WHERE Code Like '%s'" % post["code"]):
raise utils.ASMValidationError(i18n._("Account code '{0}' has already been used.", l).format(post["code"]))
aid = db.get_id(dbo, "accounts")
sql = db.make_insert_user_sql(dbo, "accounts", username, (
( "ID", db.di(aid)),
( "Code", post.db_string("code")),
( "AccountType", post.db_integer("type")),
( "DonationTypeID", post.db_integer("donationtype")),
( "Description", post.db_string("description"))
))
db.execute(dbo, sql)
audit.create(dbo, username, "accounts", str(aid))
accountid = post.integer("accountid")
for rid in post.integer_list("viewroles"):
db.execute(dbo, "INSERT INTO accountsrole (AccountID, RoleID, CanView, CanEdit) VALUES (%d, %d, 1, 0)" % (accountid, rid))
for rid in post.integer_list("editroles"):
if rid in post.integer_list("viewroles"):
db.execute(dbo, "UPDATE accountsrole SET CanEdit = 1 WHERE AccountID = %d AND RoleID = %d" % (accountid, rid))
else:
db.execute(dbo, "INSERT INTO accountsrole (AccountID, RoleID, CanView, CanEdit) VALUES (%d, %d, 0, 1)" % (accountid, rid))
return aid
def update_session(session):
"""
Updates and reloads stored session data
"""
dbo = session.dbo
locale = configuration.locale(dbo)
theme = configuration.system_theme(dbo)
loverride = get_locale_override(dbo, session.user)
if loverride != "":
al.debug(
"user %s has locale override of %s set, switching." %
(session.user, loverride), "users.update_session", dbo)
locale = loverride
toverride = get_theme_override(dbo, session.user)
if toverride != "":
al.debug(
"user %s has theme override of %s set, switching." %
(session.user, toverride), "users.update_session", dbo)
theme = toverride
dbo.locale = locale
dbo.is_large_db = db.query_int(dbo, "SELECT COUNT(*) FROM animal") > 10000
session.locale = locale
session.theme = theme
session.config_ts = time.strftime("%Y%m%d%H%M%S",
datetime.datetime.today().timetuple())
def delete_lookup(dbo, lookup, iid):
l = dbo.locale
t = LOOKUP_TABLES[lookup]
for fv in t[LOOKUP_FOREIGNKEYS]:
table, field = fv.split(".")
if 0 < db.query_int(dbo, "SELECT COUNT(*) FROM %s WHERE %s = %s" % (table, field, str(iid))):
raise utils.ASMValidationError(_("This item is referred to in the database ({0}) and cannot be deleted until it is no longer in use.", l).format(fv))
db.execute(dbo, "DELETE FROM %s WHERE ID = %s" % (lookup, str(iid)))
def guess_species(dbo, s):
""" Guesses a species, returns the default if no match is found """
s = str(s).lower()
guess = db.query_int(
dbo, "SELECT ID FROM species WHERE LOWER(SpeciesName) LIKE '%" +
db.escape(s) + "%'")
if guess != 0: return guess
return configuration.default_species(dbo)
def delete_lookup(dbo, lookup, iid):
l = dbo.locale
t = LOOKUP_TABLES[lookup]
for fv in t[LOOKUP_FOREIGNKEYS]:
table, field = fv.split(".")
if 0 < db.query_int(dbo, "SELECT COUNT(*) FROM %s WHERE %s = %s" % (table, field, str(iid))):
raise utils.ASMValidationError(_("This item is referred to in the database ({0}) and cannot be deleted until it is no longer in use.", l).format(fv))
db.execute(dbo, "DELETE FROM %s WHERE ID = %s" % (lookup, str(iid)))
def guess_colour(dbo, s):
""" Guesses a colour, returns the default if no match is found """
s = str(s).lower()
guess = db.query_int(
dbo, "SELECT ID FROM basecolour WHERE LOWER(BaseColour) LIKE '%" +
db.escape(s) + "%'")
if guess != 0: return guess
return configuration.default_colour(dbo)
def guess_breed(dbo, s):
""" Guesses a breed, returns the default if no match is found """
s = str(s).lower()
guess = db.query_int(
dbo, "SELECT ID FROM breed WHERE LOWER(BreedName) LIKE '%" +
db.escape(s) + "%'")
if guess != 0: return guess
return configuration.default_breed(dbo)
def delete_transport(dbo, username, tid):
"""
Deletes a transport record
"""
animalid = db.query_int(dbo, "SELECT AnimalID FROM animaltransport WHERE ID = %d" % int(tid))
if animalid == 0:
raise utils.ASMError("Trying to delete a transport that does not exist")
audit.delete(dbo, username, "animaltransport", tid, audit.dump_row(dbo, "animaltransport", tid))
db.execute(dbo, "DELETE FROM animaltransport WHERE ID = %d" % int(tid))
def clean(dbo):
"""
Deletes audit trail records older than three months
"""
d = db.today()
d = i18n.subtract_days(d, 93);
count = db.query_int(dbo, "SELECT COUNT(*) FROM audittrail WHERE AuditDate< %s" % db.dd(d))
al.debug("removing %d audit records older than 93 days." % count, "audit.clean", dbo)
db.execute(dbo, "DELETE FROM audittrail WHERE AuditDate < %s" % db.dd(d))
def delete_treatment(dbo, username, amtid):
"""
Deletes a treatment record
"""
audit.delete(dbo, username, "animalmedicaltreatment", str(db.query(dbo, "SELECT * FROM animalmedicaltreatment WHERE ID = %d" % int(amtid))))
amid = db.query_int(dbo, "SELECT AnimalMedicalID FROM animalmedicaltreatment WHERE ID = %d" % int(amtid))
db.execute(dbo, "DELETE FROM animalmedicaltreatment WHERE ID = %d" % amtid)
calculate_given_remaining(dbo, amid)
update_medical_treatments(dbo, username, amid)
def calculate_given_remaining(dbo, amid):
"""
Calculates the number of treatments given and remaining
"""
given = db.query_int(dbo, "SELECT COUNT(*) FROM animalmedicaltreatment " +
"WHERE AnimalMedicalID = %d AND DateGiven Is Not Null" % amid)
db.execute(dbo, "UPDATE animalmedical SET " \
"TreatmentsGiven = %d, TreatmentsRemaining = " \
"((TotalNumberOfTreatments * TimingRule) - %d) WHERE ID = %d" % (given, given, amid))
def update_animalcontrol_addlink(dbo, username, acid, animalid):
"""
Adds a link between an animal and an incident.
"""
l = dbo.locale
if 0 != db.query_int(dbo, "SELECT COUNT(*) FROM animalcontrolanimal WHERE AnimalControlID = %d AND AnimalID = %d" % (acid, animalid)):
raise utils.ASMValidationError(_("That animal is already linked to the incident", l))
db.execute(dbo, "INSERT INTO animalcontrolanimal (AnimalControlID, AnimalID) VALUES (%d, %d)" % (acid, animalid))
audit.create(dbo, username, "animalcontrolanimal", acid, "incident %d linked to animal %d" % (acid, animalid))
def return_movement(dbo, movementid, animalid = 0, returndate = None):
"""
Returns a movement with the date given. If animalid is not supplied, it
will be looked up from the movement given. If returndate is not supplied,
now() will be used.
"""
if returndate is None: returndate = i18n.now(dbo.timezone)
if animalid == 0: animalid = db.query_int(dbo, "SELECT AnimalID FROM adoption WHERE ID = %d" % int(movementid))
db.execute(dbo, "UPDATE adoption SET ReturnDate = %s WHERE ID = %d" % (db.dd(returndate), int(movementid)))
animal.update_animal_status(dbo, int(animalid))
def calculate_given_remaining(dbo, amid):
"""
Calculates the number of treatments given and remaining
"""
given = db.query_int(
dbo, "SELECT COUNT(*) FROM animalmedicaltreatment " +
"WHERE AnimalMedicalID = %d AND DateGiven Is Not Null" % amid)
db.execute(dbo, "UPDATE animalmedical SET " \
"TreatmentsGiven = %d, TreatmentsRemaining = " \
"((TotalNumberOfTreatments * TimingRule) - %d) WHERE ID = %d" % (given, given, amid))
def delete_donation(dbo, username, did):
"""
Deletes a donation record
"""
audit.delete(dbo, username, "ownerdonation", str(db.query(dbo, "SELECT * FROM ownerdonation WHERE ID=%d" % int(did))))
movementid = db.query_int(dbo, "SELECT MovementID FROM ownerdonation WHERE ID = %d" % int(did))
db.execute(dbo, "DELETE FROM ownerdonation WHERE ID = %d" % int(did))
# Delete any existing transaction for this donation if there is one
db.execute(dbo, "DELETE FROM accountstrx WHERE OwnerDonationID = %d" % int(did))
movement.update_movement_donation(dbo, movementid)
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 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 delete_role(dbo, username, rid):
"""
Deletes the selected role. If it's in use, throws an ASMValidationError
"""
l = dbo.locale
if db.query_int(dbo, "SELECT COUNT(*) FROM userrole WHERE RoleID = %d" % int(rid)) > 0:
raise utils.ASMValidationError(i18n._("Role is in use and cannot be deleted.", l))
audit.delete(dbo, username, "role", rid, audit.dump_row(dbo, "role", rid))
db.execute(dbo, "DELETE FROM accountsrole WHERE RoleID = %d" % int(rid))
db.execute(dbo, "DELETE FROM customreportrole WHERE RoleID = %d" % int(rid))
db.execute(dbo, "DELETE FROM role WHERE ID = %d" % int(rid))
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 delete_movement(dbo, username, mid):
"""
Deletes a movement record
"""
animalid = db.query_int(dbo, "SELECT AnimalID FROM adoption WHERE ID = %d" % int(mid))
if animalid == 0:
raise utils.ASMError("Trying to delete a movement that does not exist")
db.execute(dbo, "UPDATE ownerdonation SET MovementID = 0 WHERE MovementID = %d" % int(mid))
audit.delete(dbo, username, "adoption", str(db.query(dbo, "SELECT * FROM adoption WHERE ID=%d" % int(mid))))
db.execute(dbo, "DELETE FROM adoption WHERE ID = %d" % int(mid))
animal.update_animal_status(dbo, animalid)
animal.update_variable_animal_data(dbo, animalid)
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_session(session):
"""
Updates and reloads stored session data
"""
dbo = session.dbo
locale = configuration.locale(dbo)
theme = "asm"
loverride = get_locale_override(dbo, session.user)
if loverride != "":
al.debug("user %s has locale override of %s set, switching." % (session.user, loverride), "users.update_session", dbo)
locale = loverride
toverride = get_theme_override(dbo, session.user)
if toverride != "":
al.debug("user %s has theme override of %s set, switching." % (session.user, toverride), "users.update_session", dbo)
theme = toverride
dbo.locale = locale
if not dbo.is_large_db:
dbo.is_large_db = db.query_int(dbo, "SELECT COUNT(*) FROM owner") > 4000 or \
db.query_int(dbo, "SELECT COUNT(*) FROM animal") > 2000
session.locale = locale
session.theme = theme
session.config_ts = i18n.format_date("%Y%m%d%H%M%S", i18n.now())
def delete_old_publish_logs(dbo):
"""
Retains only the last MAX_LOGS publish logs
"""
MAX_LOGS = 30
rows = db.query(dbo, "SELECT Name FROM dbfs WHERE Path Like '/logs/publish%%' ORDER BY Name DESC LIMIT %d" % MAX_LOGS)
if len(rows) < MAX_LOGS: return
names = []
for r in rows: names.append("'" + r["NAME"] + "'")
notin = ",".join(names)
count = db.query_int(dbo, "SELECT COUNT(*) FROM dbfs WHERE Path Like '/logs/publish%%' AND Name NOT IN (%s)" % notin)
al.debug("removing %d publishing logs (keep latest 30)." % count, "dbfs.delete_old_publish_logs", dbo)
rq = "DELETE FROM dbfs WHERE Path Like '/logs/publish%%' AND Name NOT IN (%s)" % notin
db.execute(dbo, rq)
def delete_donation(dbo, username, did):
"""
Deletes a donation record
"""
audit.delete(
dbo, username, "ownerdonation",
str(db.query(dbo,
"SELECT * FROM ownerdonation WHERE ID=%d" % int(did))))
movementid = db.query_int(
dbo, "SELECT MovementID FROM ownerdonation WHERE ID = %d" % int(did))
db.execute(dbo, "DELETE FROM ownerdonation WHERE ID = %d" % int(did))
# Delete any existing transaction for this donation if there is one
db.execute(dbo,
"DELETE FROM accountstrx WHERE OwnerDonationID = %d" % int(did))
movement.update_movement_donation(dbo, movementid)
def gkl(dbo, m, f, table, namefield, create):
""" reads lookup field f from map m, returning a str(int) that
corresponds to a lookup match for namefield in table.
if create is True, adds a row to the table if it doesn't
find a match and then returns str(newid)
returns "0" if key not present, or if no match was found and create is off """
if not m.has_key(f): return "0"
lv = m[f]
matchid = db.query_int(dbo, "SELECT ID FROM %s WHERE %s = '%s'" % (table, namefield, lv.replace("'", "`")))
if matchid == 0 and create:
nextid = db.get_id(dbo, table)
sql = "INSERT INTO %s (ID, %s) VALUES (%d, '%s')" % (table, namefield, nextid, lv.replace("'", "`"))
db.execute(dbo, sql)
return str(nextid)
return str(matchid)
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_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_account_from_form(dbo, username, post):
"""
Updates an account from posted form data
"""
l = dbo.locale
accountid = post.integer("accountid")
if post["code"] == "":
raise utils.ASMValidationError(
i18n._("Account code cannot be blank.", l))
if 0 != db.query_int(
dbo,
"SELECT COUNT(*) FROM accounts WHERE Code Like '%s' AND ID <> %d" %
(post["code"], accountid)):
raise utils.ASMValidationError(
i18n._("Account code '{0}' has already been used.",
l).format(post["code"]))
sql = db.make_update_user_sql(
dbo, "accounts", username, "ID=%d" % accountid,
(("Code", post.db_string("code")),
("AccountType", post.db_integer("type")),
("DonationTypeID", post.db_integer("donationtype")),
("Description", post.db_string("description"))))
preaudit = db.query(dbo,
"SELECT * FROM accounts WHERE ID = %d" % accountid)
db.execute(dbo, sql)
postaudit = db.query(dbo,
"SELECT * FROM accounts WHERE ID = %d" % accountid)
audit.edit(dbo, username, "accounts", audit.map_diff(preaudit, postaudit))
db.execute(dbo,
"DELETE FROM accountsrole WHERE AccountID = %d" % accountid)
for rid in post.integer_list("viewroles"):
db.execute(
dbo,
"INSERT INTO accountsrole (AccountID, RoleID, CanView, CanEdit) VALUES (%d, %d, 1, 0)"
% (accountid, rid))
for rid in post.integer_list("editroles"):
if rid in post.integer_list("viewroles"):
db.execute(
dbo,
"UPDATE accountsrole SET CanEdit = 1 WHERE AccountID = %d AND RoleID = %d"
% (accountid, rid))
else:
db.execute(
dbo,
"INSERT INTO accountsrole (AccountID, RoleID, CanView, CanEdit) VALUES (%d, %d, 0, 1)"
% (accountid, rid))
def delete_treatment(dbo, username, amtid):
"""
Deletes a treatment record
"""
audit.delete(
dbo, username, "animalmedicaltreatment",
str(
db.query(
dbo, "SELECT * FROM animalmedicaltreatment WHERE ID = %d" %
int(amtid))))
amid = db.query_int(
dbo,
"SELECT AnimalMedicalID FROM animalmedicaltreatment WHERE ID = %d" %
int(amtid))
db.execute(dbo, "DELETE FROM animalmedicaltreatment WHERE ID = %d" % amtid)
calculate_given_remaining(dbo, amid)
update_medical_treatments(dbo, username, amid)
def delete_role(dbo, username, rid):
"""
Deletes the selected role. If it's in use, throws an ASMValidationError
"""
l = dbo.locale
if db.query_int(
dbo,
"SELECT COUNT(*) FROM userrole WHERE RoleID = %d" % int(rid)) > 0:
raise utils.ASMValidationError(
i18n._("Role is in use and cannot be deleted.", l))
audit.delete(
dbo, username, "role",
str(db.query(dbo, "SELECT * FROM role WHERE ID=%d" % int(rid))))
db.execute(dbo, "DELETE FROM accountsrole WHERE RoleID = %d" % int(rid))
db.execute(dbo,
"DELETE FROM customreportrole WHERE RoleID = %d" % int(rid))
db.execute(dbo, "DELETE FROM role WHERE ID = %d" % int(rid))
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 delete_movement(dbo, username, mid):
"""
Deletes a movement record
"""
animalid = db.query_int(
dbo, "SELECT AnimalID FROM adoption WHERE ID = %d" % int(mid))
if animalid == 0:
raise utils.ASMError("Trying to delete a movement that does not exist")
db.execute(
dbo, "UPDATE ownerdonation SET MovementID = 0 WHERE MovementID = %d" %
int(mid))
audit.delete(
dbo, username, "adoption",
str(db.query(dbo, "SELECT * FROM adoption WHERE ID=%d" % int(mid))))
db.execute(dbo, "DELETE FROM adoption WHERE ID = %d" % int(mid))
animal.update_animal_status(dbo, animalid)
animal.update_variable_animal_data(dbo, animalid)
def gkl(dbo, m, f, table, namefield, create):
""" reads lookup field f from map m, returning a str(int) that
corresponds to a lookup match for namefield in table.
if create is True, adds a row to the table if it doesn't
find a match and then returns str(newid)
returns "0" if key not present, or if no match was found and create is off """
if not m.has_key(f): return "0"
lv = m[f]
matchid = db.query_int(
dbo, "SELECT ID FROM %s WHERE %s = '%s'" %
(table, namefield, lv.replace("'", "`")))
if matchid == 0 and create:
nextid = db.get_id(dbo, table)
sql = "INSERT INTO %s (ID, %s) VALUES (%d, '%s')" % (
table, namefield, nextid, lv.replace("'", "`"))
db.execute(dbo, sql)
return str(nextid)
return str(matchid)
def update_treatment_given(dbo, username, amtid, newdate):
"""
Marks a treatment record as given on newdate, assuming
that newdate is valid.
"""
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(newdate) ),
( "GivenBy", db.ds(username))
)))
audit.edit(dbo, username, "animalmedicaltreatment", "%d given => %s" % (amtid, str(newdate)))
# 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 gkbr(dbo, m, f, speciesid, create):
""" reads lookup field f from map m, returning a str(int) that
corresponds to a lookup match for BreedName in breed.
if create is True, adds a row to the table if it doesn't
find a match and then returns str(newid)
speciesid is the linked species for any newly created breed
returns "0" if key not present, or if no match was found and create is off """
if not m.has_key(f): return "0"
lv = m[f]
matchid = db.query_int(
dbo,
"SELECT ID FROM breed WHERE BreedName = '%s'" % (lv.replace("'", "`")))
if matchid == 0 and create:
nextid = db.get_id(dbo, "breed")
sql = "INSERT INTO breed (ID, SpeciesID, BreedName) VALUES (%d, %s, '%s')" % (
nextid, speciesid, lv.replace("'", "`"))
db.execute(dbo, sql)
return str(nextid)
return str(matchid)
def insert_account_from_form(dbo, username, post):
"""
Creates an account from posted form data
"""
l = dbo.locale
if post["code"] == "":
raise utils.ASMValidationError(
i18n._("Account code cannot be blank.", l))
if 0 != db.query_int(
dbo, "SELECT COUNT(*) FROM accounts WHERE Code Like '%s'" %
post["code"]):
raise utils.ASMValidationError(
i18n._("Account code '{0}' has already been used.",
l).format(post["code"]))
aid = db.get_id(dbo, "accounts")
sql = db.make_insert_user_sql(
dbo, "accounts", username,
(("ID", db.di(aid)), ("Code", post.db_string("code")),
("AccountType", post.db_integer("type")),
("DonationTypeID", post.db_integer("donationtype")),
("Description", post.db_string("description"))))
db.execute(dbo, sql)
audit.create(dbo, username, "accounts", str(aid))
accountid = post.integer("accountid")
for rid in post.integer_list("viewroles"):
db.execute(
dbo,
"INSERT INTO accountsrole (AccountID, RoleID, CanView, CanEdit) VALUES (%d, %d, 1, 0)"
% (accountid, rid))
for rid in post.integer_list("editroles"):
if rid in post.integer_list("viewroles"):
db.execute(
dbo,
"UPDATE accountsrole SET CanEdit = 1 WHERE AccountID = %d AND RoleID = %d"
% (accountid, rid))
else:
db.execute(
dbo,
"INSERT INTO accountsrole (AccountID, RoleID, CanView, CanEdit) VALUES (%d, %d, 0, 1)"
% (accountid, rid))
return aid
def update_session(session):
"""
Updates and reloads stored session data
"""
dbo = session.dbo
locale = configuration.locale(dbo)
theme = configuration.system_theme(dbo)
loverride = get_locale_override(dbo, session.user)
if loverride != "":
al.debug("user %s has locale override of %s set, switching." % (session.user, loverride), "users.update_session", dbo)
locale = loverride
toverride = get_theme_override(dbo, session.user)
if toverride != "":
al.debug("user %s has theme override of %s set, switching." % (session.user, toverride), "users.update_session", dbo)
theme = toverride
dbo.locale = locale
dbo.is_large_db = db.query_int(dbo, "SELECT COUNT(*) FROM animal") > 10000
session.locale = locale
session.theme = theme
session.config_ts = time.strftime("%Y%m%d%H%M%S", datetime.datetime.today().timetuple())
def attach_animal(dbo, username, collationid):
"""
Finds the existing shelter animal with "animalname" and
attaches the form to it as person media.
Return value is a tuple of collationid, animalid, animal code/name
"""
l = dbo.locale
fields = get_onlineformincoming_detail(dbo, collationid)
animalname = ""
for f in fields:
if f["FIELDNAME"] == "animalname": animalname = f["VALUE"]
if animalname == "":
raise utils.ASMValidationError(i18n._("There is not enough information in the form to attach to a shelter animal record (need an animal name).", l))
# Find the animal
aid = db.query_int(dbo, "SELECT ID FROM animal WHERE LOWER(AnimalName) LIKE '%s' ORDER BY ID DESC" % animalname.lower())
if aid == 0:
raise utils.ASMValidationError(i18n._("Could not find animal with name '{0}'", l).format(animalname))
formname = get_onlineformincoming_name(dbo, collationid)
formhtml = get_onlineformincoming_html_print(dbo, [collationid,])
media.create_document_media(dbo, username, media.ANIMAL, aid, formname, formhtml )
return (collationid, aid, animal.get_animal_namecode(dbo, aid))
def insert_reserve_for_animal_name(dbo, username, personid, animalname):
"""
Creates a reservation for the animal with animalname to personid
"""
l = dbo.locale
aid = db.query_int(
dbo,
"SELECT ID FROM animal WHERE LOWER(AnimalName) LIKE '%s' ORDER BY ID DESC"
% animalname.lower())
# Bail out if an invalid animal name was given - we can't create the reservation
if aid == 0: return
move_dict = {
"person": str(personid),
"animal": str(aid),
"reservationdate": i18n.python2display(l, i18n.now(dbo.timezone)),
"movementdate": "",
"type": str(NO_MOVEMENT),
"returncategory": configuration.default_return_reason(dbo)
}
return insert_movement_from_form(dbo, username,
utils.PostedData(move_dict, l))
def delete_old_publish_logs(dbo):
"""
Retains only the last MAX_LOGS publish logs
"""
MAX_LOGS = 30
rows = db.query(
dbo,
"SELECT Name FROM dbfs WHERE Path Like '/logs/publish%%' ORDER BY Name DESC LIMIT %d"
% MAX_LOGS)
if len(rows) < MAX_LOGS: return
names = []
for r in rows:
names.append("'" + r["NAME"] + "'")
notin = ",".join(names)
count = db.query_int(
dbo,
"SELECT COUNT(*) FROM dbfs WHERE Path Like '/logs/publish%%' AND Name NOT IN (%s)"
% notin)
al.debug("removing %d publishing logs (keep latest 30)." % count,
"dbfs.delete_old_publish_logs", dbo)
rq = "DELETE FROM dbfs WHERE Path Like '/logs/publish%%' AND Name NOT IN (%s)" % notin
db.execute(dbo, rq)
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 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 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",
str(mediaid) + ": for " + str(linkid) + "/" + str(linktype) +
": link to " + post["linktarget"])
def update_treatment_given(dbo, username, amtid, newdate):
"""
Marks a treatment record as given on newdate, assuming
that newdate is valid.
"""
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(newdate)),
("GivenBy", db.ds(username)))))
audit.edit(dbo, username, "animalmedicaltreatment",
"%d given => %s" % (amtid, str(newdate)))
# 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 get_total_seq(dbo, linktype, linkid):
return db.query_int(dbo, "SELECT COUNT(ID) FROM media WHERE LinkTypeID = %d AND " \
"LinkID = %d AND LOWER(MediaName) LIKE '%%.jpg' AND ExcludeFromPublish = 0" % (linktype, linkid))
def attach_file_from_form(dbo, username, linktype, linkid, post):
"""
Attaches a media file from the posted form
data is the web.py data object and should contain
comments, the filechooser object, with filename and value
props - OR a parameter called base64image containing a base64
encoded jpg image.
"""
ext = ""
base64data = ""
base64image = post["base64image"]
if base64image != "":
ext = ".jpg"
# If an HTML5 data url was used, strip the prefix so we just have base64 data
if base64image.find("data:") != -1:
base64data = base64image[base64image.find(",") + 1:]
# Browser escaping turns base64 pluses back into spaces, so switch back
base64data = base64data.replace(" ", "+")
else:
base64data = base64image
al.debug(
"received HTML5 base64 image data (%d bytes)" % (len(base64image)),
"media.attach_file_from_form", dbo)
else:
ext = post.filename()
ext = ext[ext.rfind("."):].lower()
mediaid = db.get_id(dbo, "media")
medianame = "%d%s" % (mediaid, ext)
ispicture = ext == ".jpg" or ext == ".jpeg"
ispdf = ext == ".pdf"
# Does this link have anything with web/doc set? If not, set the
# web/doc flags
web = 0
doc = 0
existing_web = db.query_int(dbo, "SELECT COUNT(*) FROM media WHERE WebsitePhoto = 1 " \
"AND LinkID = %d AND LinkTypeID = %d" % ( int(linkid), int(linktype) ))
existing_doc = db.query_int(dbo, "SELECT COUNT(*) FROM media WHERE DocPhoto = 1 " \
"AND LinkID = %d AND LinkTypeID = %d" % ( int(linkid), int(linktype) ))
if existing_web == 0 and ispicture:
web = 1
if existing_doc == 0 and ispicture:
doc = 1
if base64image != "":
filedata = base64.b64decode(base64data)
else:
filedata = post.filedata()
al.debug(
"received POST file data '%s' (%d bytes)" %
(post.filename(), len(filedata)), "media.attach_file_from_form",
dbo)
# Is it a picture?
if ispicture:
# Autorotate it to match the EXIF orientation
filedata = auto_rotate_image(dbo, filedata)
# Scale it down to the system set size
scalespec = configuration.incoming_media_scaling(dbo)
if scalespec != "None":
filedata = scale_image(filedata, scalespec)
al.debug(
"scaled image to %s (%d bytes)" % (scalespec, len(filedata)),
"media.attach_file_from_form", dbo)
# Is it a PDF? If so, compress it if we can and the option is on
if ispdf and SCALE_PDF_DURING_ATTACH and configuration.scale_pdfs(dbo):
filedata = scale_pdf(filedata)
medianame = "%d_scaled.pdf" % mediaid
al.debug("compressed PDF (%d bytes)" % (len(filedata)),
"media.attach_file_from_form", dbo)
# Attach the file in the dbfs
path = get_dbfs_path(linkid, linktype)
dbfs.put_string(dbo, medianame, path, filedata)
# Are the notes for an image blank and we're defaulting them from animal comments?
comments = post["comments"]
if comments == "" and ispicture and linktype == ANIMAL and configuration.auto_media_notes(
dbo):
comments = animal.get_comments(dbo, int(linkid))
# Are the notes blank and we're defaulting them from the filename?
elif comments == "" and configuration.default_media_notes_from_file(
dbo) and base64image == "":
comments = utils.filename_only(post.filename())
# Create the media record
sql = db.make_insert_sql(
"media",
(
("ID", db.di(mediaid)),
("MediaName", db.ds(medianame)),
("MediaType", db.di(0)),
("MediaNotes", db.ds(comments)),
("WebsitePhoto", db.di(web)),
("WebsiteVideo", db.di(0)),
("DocPhoto", db.di(doc)),
("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.dd(i18n.now(dbo.timezone)))))
db.execute(dbo, sql)
audit.create(dbo, username, "media",
str(mediaid) + ": for " + str(linkid) + "/" + str(linktype))
def get_account_id(dbo, code):
"""
Returns the id for an account code
"""
return db.query_int(dbo, "SELECT ID FROM accounts WHERE Code = '%s'" % str(code))
def get_donation_default(dbo, donationtypeid):
return db.query_int(
dbo, "SELECT DefaultCost FROM donationtype WHERE ID = %d" %
int(donationtypeid))
def get_donation_default(dbo, donationtypeid):
return db.query_int(dbo, "SELECT DefaultCost FROM donationtype WHERE ID = %d" % int(donationtypeid))
def validate_movement_form_data(dbo, data):
"""
Verifies that form data is valid for a movement
"""
l = dbo.locale
movementid = utils.df_ki(data, "movementid")
movement = None
if movementid != 0:
movement = db.query(
dbo, "SELECT * FROM adoption WHERE ID = %d" % movementid)[0]
adoptionno = utils.df_ks(data, "adoptionno")
movementtype = utils.df_ki(data, "type")
movementdate = utils.df_kd(data, "movementdate", l)
returndate = utils.df_kd(data, "returndate", l)
reservationdate = utils.df_kd(data, "reservationdate", l)
reservationcancelled = utils.df_kd(data, "reservationcancelled", l)
personid = utils.df_ki(data, "person")
animalid = utils.df_ki(data, "animal")
retailerid = utils.df_ki(data, "retailer")
al.debug(
"validating saved movement %d for animal %d" % (movementid, animalid),
"movement.validate_movement_form_data", dbo)
# If we have a date but no type, get rid of it
if movementdate is None and movementtype == 0:
data["movementdate"] = ""
al.debug("blank date and type", "movement.validate_movement_form_data",
dbo)
# If we've got a type, but no date, default today
if movementtype > 0 and movementdate is None:
movementdate = i18n.now()
data["movementdate"] = i18n.python2display(l, movementdate)
al.debug("type set and no date, defaulting today",
"movement.validate_movement_form_data", dbo)
# If we've got a reserve cancellation without a reserve, remove it
if reservationdate is None and reservationcancelled is not None:
data["reservationdate"] = ""
al.debug("movement has no reserve or cancelled date",
"movement.validate_movement_form_data", dbo)
# Animals are always required
if animalid == 0:
al.debug("movement has no animal",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(i18n._("Movements require an animal",
l))
# Owners are required unless type is escaped, stolen or released
if personid == 0 and movementtype != ESCAPED and movementtype != STOLEN and movementtype != RELEASED:
al.debug("movement has no person and is not ESCAPED|STOLEN|RELEASED",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("A person is required for this movement type.", l))
# Is the movement number unique?
if 0 != db.query_int(
dbo,
"SELECT COUNT(*) FROM adoption WHERE AdoptionNumber LIKE '%s' AND ID <> %d"
% (adoptionno, movementid)):
raise utils.ASMValidationError(
i18n._("Movement numbers must be unique.", l))
# If we're updating an existing record, we only need to continue validation
# if one of the important fields has changed (movement date/type, return date, reservation, animal)
if movement is not None:
if movementtype == movement[
"MOVEMENTTYPE"] and movementdate == movement[
"MOVEMENTDATE"] and returndate == movement[
"RETURNDATE"] and reservationdate == movement[
"RESERVATIONDATE"] and animalid == movement[
"ANIMALID"]:
al.debug(
"movement type, dates and animalid have not changed. Abandoning further validation",
"movement.validate_movement_form_data", dbo)
return
# If the animal is held in case of reclaim, it can't be adopted
if movementtype == ADOPTION:
if 1 == db.query_int(
dbo, "SELECT IsHold FROM animal WHERE ID = %d" % animalid):
al.debug("movement is adoption and the animal is on hold",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("This animal is currently held and cannot be adopted.",
l))
# If it's a foster movement, make sure the owner is a fosterer
if movementtype == FOSTER:
if 0 == db.query_int(
dbo, "SELECT IsFosterer FROM owner WHERE ID = %d" % personid):
al.debug("movement is a foster and the person is not a fosterer.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._(
"This person is not flagged as a fosterer and cannot foster animals.",
l))
# If it's a retailer movement, make sure the owner is a retailer
if movementtype == RETAILER:
if 0 == db.query_int(
dbo, "SELECT IsRetailer FROM owner WHERE ID = %d" % personid):
al.debug(
"movement is a retailer and the person is not a retailer.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._(
"This person is not flagged as a retailer and cannot handle retailer movements.",
l))
# If a retailer is selected, make sure it's an adoption
if retailerid != 0 and movementtype != ADOPTION:
al.debug("movement has a retailerid set and this is not an adoption.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("From retailer is only valid on adoption movements.", l))
# If a retailer is selected, make sure there's been a retailer movement in this animal's history
if retailerid != 0:
if 0 == db.query_int(
dbo,
"SELECT COUNT(*) FROM adoption WHERE AnimalID = %d AND MovementType = %d"
% (animalid, RETAILER)):
al.debug(
"movement has a retailerid set but has never been to a retailer.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._(
"This movement cannot be from a retailer when the animal has no prior retailer movements.",
l))
# You can't have a return without a movement
if movementdate is None and returndate is not None:
al.debug("movement is returned without a movement date.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("You can't have a return without a movement.", l))
# Return should be after or same day as movement
if movementdate is not None and returndate != None and movementdate > returndate:
al.debug("movement return date is before the movement date.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("Return date cannot be before the movement date.", l))
# Can't have multiple open movements
if movementdate is not None:
existingopen = db.query_int(dbo, "SELECT COUNT(*) FROM adoption WHERE MovementDate Is Not Null AND " \
"ReturnDate Is Null AND AnimalID = %d AND ID <> %d" % (animalid, movementid))
if existingopen > 0:
al.debug(
"movement is open and animal already has another open movement.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("An animal cannot have multiple open movements.", l))
# If we have a movement and return, is there another movement with a
# movementdate between the movement and return date on this one?
if movementdate is not None and returndate != None:
clash = db.query_int(dbo, "SELECT COUNT(*) FROM adoption WHERE " \
"AnimalID = %d AND ID <> %d AND ((ReturnDate > %s AND ReturnDate < %s) " \
"OR (MovementDate < %s AND MovementDate > %s))" % ( animalid, movementid,
db.dd(movementdate), db.dd(returndate), db.dd(returndate), db.dd(movementdate) ))
if clash > 0:
al.debug("movement dates overlap an existing movement.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("Movement dates clash with an existing movement.", l))
# Does this movement date fall within the date range of an already
# returned movement for the same animal?
if movementdate is not None and returndate is None:
clash = db.query_int(dbo, "SELECT COUNT(*) FROM adoption WHERE AnimalID = %d AND ID <> %d AND " \
"MovementDate Is Not Null AND ReturnDate Is Not Null AND " \
"%s > MovementDate AND %s < ReturnDate" % ( animalid, movementid, db.dd(movementdate), db.dd(movementdate)))
if clash > 0:
al.debug("movement dates overlap an existing movement.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("Movement dates clash with an existing movement.", l))
# If there's a cancelled reservation, make sure it's after the reserve date
if reservationdate is not None and reservationcancelled != None and reservationcancelled < reservationdate:
al.debug("reserve date is after cancelled date.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("Reservation date cannot be after cancellation date.", l))
# If this is a new reservation, make sure there's no open movement (fosters do not count)
if movementid == 0 and movementtype == 0 and movementdate is None and reservationdate is not None:
om = db.query_int(dbo, "SELECT COUNT(*) FROM adoption WHERE AnimalID = %d AND " \
"MovementDate Is Not Null AND ReturnDate Is Null AND MovementType <> 2" % animalid)
if om > 0:
al.debug(
"movement is a reservation but animal has active movement.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("Can't reserve an animal that has an active movement.",
l))
# Make sure the adoption number is unique
an = db.query_int(dbo, "SELECT COUNT(*) FROM adoption WHERE ID <> %d AND " \
"AdoptionNumber LIKE %s" % ( movementid, utils.df_t(data, "adoptionno" )))
if an > 0:
al.debug("movement number is not unique.",
"movement.validate_movement_form_data", dbo)
raise utils.ASMValidationError(
i18n._("The movement number '{0}' is not unique.",
l).format(utils.df_ks(data, "adoptionno")))
# If this is an adoption and the owner had some criteria, expire them
if movementtype == ADOPTION and personid > 0:
al.debug("movement is an adoption, expiring person criteria.",
"movement.validate_movement_form_data", dbo)
sql = "UPDATE owner SET MatchActive = 0, MatchExpires = %s WHERE ID = %d" % (
db.dd(i18n.now(dbo.timezone)), int(personid))
db.execute(dbo, sql)
# If the option to cancel reserves on adoption is set, cancel any outstanding reserves for the animal
if movementtype == ADOPTION and configuration.cancel_reserves_on_adoption(
dbo):
al.debug("movement is an adoption, cancelling outstanding reserves.",
"movement.validate_movement_form_data", dbo)
sql = "UPDATE adoption SET ReservationCancelledDate = %s " \
"WHERE ReservationCancelledDate Is Null AND MovementDate Is Null " \
"AND AnimalID = %d AND ID <> %d" % ( db.dd(i18n.now(dbo.timezone)), animalid, int(movementid) )
db.execute(dbo, sql)
