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 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 insert_waitinglist_from_form(dbo, data, username):
"""
Creates a waiting list record from the screen
data: The webpy data object containing form parameters
"""
l = dbo.locale
if utils.df_ks(data, "description") == "":
raise utils.ASMValidationError(_("Description cannot be blank", l))
if utils.df_ki(data, "owner") == "0":
raise utils.ASMValidationError(_("Waiting list entries must have a contact", l))
if utils.df_ks(data, "dateputon") == "":
raise utils.ASMValidationError(_("Date put on cannot be blank", l))
nwlid = db.get_id(dbo, "animalwaitinglist")
db.execute(dbo, db.make_insert_user_sql(dbo, "animalwaitinglist", username, (
( "ID", db.di(nwlid)),
( "SpeciesID", utils.df_s(data, "species")),
( "DatePutOnList", utils.df_d(data, "dateputon", l)),
( "OwnerID", utils.df_s(data, "owner")),
( "AnimalDescription", utils.df_t(data, "description")),
( "ReasonForWantingToPart", utils.df_t(data, "reasonforwantingtopart")),
( "CanAffordDonation", utils.df_c(data, "canafforddonation")),
( "Urgency", utils.df_s(data, "urgency")),
( "DateRemovedFromList", utils.df_d(data, "dateremoved", l)),
( "AutoRemovePolicy", utils.df_s(data, "autoremovepolicy")),
( "DateOfLastOwnerContact", db.dd(now(dbo.timezone))),
( "ReasonForRemoval", utils.df_t(data, "reasonforremoval")),
( "Comments", utils.df_t(data, "comments")),
( "UrgencyLastUpdatedDate", db.dd(now(dbo.timezone))),
( "UrgencyUpdateDate", db.dd(add_days(now(dbo.timezone), configuration.waiting_list_urgency_update_period(dbo))))
)))
audit.create(dbo, username, "animalwaitinglist", str(nwlid))
return nwlid
def toDB(self):
""" Writes a lostfoundmatch record from this object """
sql = db.make_insert_sql("animallostfoundmatch", (
( "AnimalLostID", db.di(self.lid) ),
( "AnimalFoundID", db.di(self.fid) ),
( "AnimalID", db.di(self.fanimalid) ),
( "LostContactName", db.ds(self.lcontactname) ),
( "LostContactNumber", db.ds(self.lcontactnumber) ),
( "LostArea", db.ds(self.larealost) ),
( "LostPostcode", db.ds(self.lareapostcode) ),
( "LostAgeGroup", db.ds(self.lagegroup) ),
( "LostSex", db.di(self.lsexid) ),
( "LostSpeciesID", db.di(self.lspeciesid) ),
( "LostBreedID", db.di(self.lbreedid) ),
( "LostFeatures", db.ds(self.ldistinguishingfeatures) ),
( "LostBaseColourID", db.di(self.lbasecolourid) ),
( "LostDate", db.dd(self.ldatelost) ),
( "FoundContactName", db.ds(self.fcontactname) ),
( "FoundContactNumber", db.ds(self.fcontactnumber) ),
( "FoundArea", db.ds(self.fareafound) ),
( "FoundPostcode", db.ds(self.fareapostcode) ),
( "FoundAgeGroup", db.ds(self.fagegroup) ),
( "FoundSex", db.di(self.fsexid) ),
( "FoundSpeciesID", db.di(self.fspeciesid) ),
( "FoundBreedID", db.di(self.fbreedid) ),
( "FoundFeatures", db.ds(self.fdistinguishingfeatures) ),
( "FoundBaseColourID", db.di(self.fbasecolourid) ),
( "FoundDate", db.dd(self.fdatefound) ),
( "MatchPoints", db.di(self.matchpoints) ) ))
db.execute(self.dbo, sql)
def update_asm2_tests(dbo, testid):
"""
Used for asm2 compatibility, checks the test with testid and if it's
a FIV, FLV or Heartworm test updates the old ASM2 fields for them.
"""
# ASM2_COMPATIBILITY
testid = int(testid)
t = db.query(dbo, "SELECT AnimalID, TestName, DateOfTest, ResultName FROM animaltest " \
"INNER JOIN testtype ON testtype.ID = animaltest.TestTypeID " \
"INNER JOIN testresult ON testresult.ID = animaltest.TestResultID " \
"WHERE animaltest.ID=%d" % testid)[0]
# If there's no date, forget it
if t["DATEOFTEST"] is None: return
# Get an old style result
result = 0
if t["RESULTNAME"].find("egativ") != -1: result = 1
if t["RESULTNAME"].find("ositiv") != -1: result = 2
# Update for the correct test if it's one we know about
if t["TESTNAME"].find("FIV") != -1:
db.execute(dbo, "UPDATE animal SET CombiTested = 1, CombiTestDate = %s, CombiTestResult = %d WHERE ID = %d" % \
(db.dd(t["DATEOFTEST"]), result, t["ANIMALID"]))
if t["TESTNAME"].find("FLV") != -1:
db.execute(dbo, "UPDATE animal SET CombiTested = 1, CombiTestDate = %s, FLVResult = %d WHERE ID = %d" % \
(db.dd(t["DATEOFTEST"]), result, t["ANIMALID"]))
if t["TESTNAME"].find("eartworm") != -1:
db.execute(dbo, "UPDATE animal SET HeartwormTested = 1, HeartwormTestDate = %s, HeartwormTestResult = %d WHERE ID = %d" % \
(db.dd(t["DATEOFTEST"]), result, t["ANIMALID"]))
def update_asm2_tests(dbo, testid):
"""
Used for asm2 compatibility, checks the test with testid and if it's
a FIV, FLV or Heartworm test updates the old ASM2 fields for them.
"""
# ASM2_COMPATIBILITY
testid = int(testid)
t = db.query(dbo, "SELECT AnimalID, TestName, DateOfTest, ResultName FROM animaltest " \
"INNER JOIN testtype ON testtype.ID = animaltest.TestTypeID " \
"INNER JOIN testresult ON testresult.ID = animaltest.TestResultID " \
"WHERE animaltest.ID=%d" % testid)[0]
# If there's no date, forget it
if t["DATEOFTEST"] is None: return
# Get an old style result
result = 0
if t["RESULTNAME"].find("egativ") != -1: result = 1
if t["RESULTNAME"].find("ositiv") != -1: result = 2
# Update for the correct test if it's one we know about
if t["TESTNAME"].find("FIV") != -1:
db.execute(dbo, "UPDATE animal SET CombiTested = 1, CombiTestDate = %s, CombiTestResult = %d WHERE ID = %d" % \
(db.dd(t["DATEOFTEST"]), result, t["ANIMALID"]))
if t["TESTNAME"].find("FLV") != -1:
db.execute(dbo, "UPDATE animal SET CombiTested = 1, CombiTestDate = %s, FLVResult = %d WHERE ID = %d" % \
(db.dd(t["DATEOFTEST"]), result, t["ANIMALID"]))
if t["TESTNAME"].find("eartworm") != -1:
db.execute(dbo, "UPDATE animal SET HeartwormTested = 1, HeartwormTestDate = %s, HeartwormTestResult = %d WHERE ID = %d" % \
(db.dd(t["DATEOFTEST"]), result, t["ANIMALID"]))
def get_treatments_outstanding(dbo, offset="m31", locationfilter=""):
"""
Returns a recordset of shelter animals awaiting medical treatments:
offset is m to go backwards, or p to go forwards with a number of days.
ANIMALID, SHELTERCODE, ANIMALNAME, LOCATIONNAME, WEBSITEMEDIANAME,
TREATMENTNAME, COST, COMMENTS, NAMEDFREQUENCY, NAMEDNUMBEROFTREATMENTS,
NAMEDSTATUS, DOSAGE, STARTDATE, TREATMENTSGIVEN, TREATMENTSREMAINING,
TIMINGRULE, TIMINGRULEFREQUENCY, TIMINGRULENOFREQUENCIES, TREATMENTRULE
TOTALNUMBEROFTREATMENTS, DATEREQUIRED, DATEGIVEN, TREATMENTCOMMENTS,
TREATMENTNUMBER, TOTALTREATMENTS, GIVENBY, REGIMENID, TREATMENTID
"""
ec = ""
offsetdays = utils.cint(offset[1:])
if offset.startswith("m"):
ec = " AND DateRequired >= %s AND DateRequired <= %s" % (db.dd(
subtract_days(now(dbo.timezone),
offsetdays)), db.dd(now(dbo.timezone)))
if offset.startswith("p"):
ec = " AND DateRequired >= %s AND DateRequired <= %s" % (db.dd(
now(dbo.timezone)), db.dd(add_days(now(dbo.timezone), offsetdays)))
if locationfilter != "":
locationfilter = " AND ShelterLocation IN (%s)" % locationfilter
return embellish_regimen(dbo.locale, db.query(dbo, "SELECT * FROM v_animalmedicaltreatment " \
"WHERE DateRequired Is Not Null AND DateGiven Is Null " \
"AND Status = 0 " \
"AND DeceasedDate Is Null AND (Archived = 0 OR ActiveMovementType = 2) %s %s " \
"ORDER BY DateRequired, AnimalName" % (ec, locationfilter)))
def get_donations(dbo, offset="m31"):
"""
Returns a recordset of donations
offset is m to go backwards, or p to go forwards with a number of days.
ID, DONATIONTYPEID, DONATIONNAME, DATE, DATEDUE, DONATION,
ISGIFTAID, FREQUENCY, FREQUENCYNAME, NEXTCREATED, COMMENTS, OWNERNAME,
ANIMALNAME, SHELTERCODE, OWNERID, ANIMALID
"""
ec = ""
order = ""
offsetdays = utils.cint(offset[1:])
if offset.startswith("m"):
ec = "Date >= %s AND Date <= %s" % (db.dd(
i18n.subtract_days(i18n.now(dbo.timezone),
offsetdays)), db.dd(i18n.now(dbo.timezone)))
order = "Date DESC"
elif offset.startswith("p"):
ec = "Date Is Null AND DateDue >= %s AND DateDue <= %s" % (
db.dd(i18n.now(dbo.timezone)),
db.dd(i18n.add_days(i18n.now(dbo.timezone), offsetdays)))
order = "DateDue DESC"
elif offset.startswith("d"):
ec = "Date Is Null AND DateDue <= %s" % (db.dd(i18n.now(dbo.timezone)))
order = "DateDue"
return db.query(dbo, "SELECT * FROM v_ownerdonation " \
"WHERE %s "
"ORDER BY %s" % (ec, order))
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 auto_remove_old_incoming_forms(dbo):
"""
Automatically removes incoming forms older than the daily amount set
"""
removeafter = configuration.auto_remove_incoming_forms_days(dbo)
if removeafter <= 0:
al.debug("auto remove incoming forms is off.", "onlineform.auto_remove_old_incoming_forms")
return
removecutoff = i18n.subtract_days(i18n.now(dbo.timezone), removeafter)
al.debug("remove date: incoming forms < %s" % db.dd(removecutoff), "onlineform.auto_remove_old_incoming_forms")
sql = "DELETE FROM onlineformincoming WHERE PostedDate < %s" % db.dd(removecutoff)
count = db.execute(dbo, sql)
al.debug("removed %d incoming forms older than %d days" % (count, int(removeafter)), "onlineform.auto_remove_old_incoming_forms", dbo)
def auto_remove_old_incoming_forms(dbo):
"""
Automatically removes incoming forms older than the daily amount set
"""
removeafter = configuration.auto_remove_incoming_forms_days(dbo)
if removeafter <= 0:
al.debug("auto remove incoming forms is off.", "onlineform.auto_remove_old_incoming_forms")
return
removecutoff = i18n.subtract_days(i18n.now(dbo.timezone), removeafter)
al.debug("remove date: incoming forms < %s" % db.dd(removecutoff), "onlineform.auto_remove_old_incoming_forms")
sql = "DELETE FROM onlineformincoming WHERE PostedDate < %s" % db.dd(removecutoff)
count = db.execute(dbo, sql)
al.debug("removed %d incoming forms older than %d days" % (count, int(removeafter)), "onlineform.auto_remove_old_incoming_forms", dbo)
def auto_cancel_reservations(dbo):
"""
Automatically cancels reservations after the daily amount set
"""
cancelafter = configuration.auto_cancel_reserves_days(dbo)
if cancelafter <= 0:
al.debug("auto reserve cancel is off.", "movement.auto_cancel_reservations")
return
cancelcutoff = i18n.subtract_days(i18n.now(dbo.timezone), cancelafter)
al.debug("cutoff date: reservations < %s" % db.dd(cancelcutoff), "movement.auto_cancel_reservations")
sql = "UPDATE adoption SET ReservationCancelledDate = %s " \
"WHERE MovementDate Is Null AND ReservationCancelledDate Is Null AND " \
"MovementType = 0 AND ReservationDate < %s" % ( db.dd(i18n.now(dbo.timezone)), db.dd(cancelcutoff))
count = db.execute(dbo, sql)
al.debug("cancelled %d reservations older than %d days" % (count, int(cancelafter)), "movement.auto_cancel_reservations", dbo)
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 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 auto_update_urgencies(dbo):
"""
Finds all animals where the next UrgencyUpdateDate field is greater
than or equal to today and the urgency is larger than High (so we
can never reach Urgent).
"""
update_period_days = configuration.waiting_list_urgency_update_period(dbo)
if update_period_days == 0:
al.debug(
"urgency update period is 0, not updating waiting list entries",
"waitinglist.auto_update_urgencies", dbo)
return
rows = db.query(dbo, "SELECT a.* " \
"FROM animalwaitinglist a WHERE UrgencyUpdateDate <= %s " \
"AND Urgency > 2" % db.dd(now(dbo.timezone)))
updates = []
for r in rows:
al.debug("increasing urgency of waitinglist entry %d" % int(r["ID"]),
"waitinglist.auto_update_urgencies", dbo)
updates.append(
(now(dbo.timezone),
add_days(r["URGENCYUPDATEDATE"],
update_period_days), r["URGENCY"] - 1, r["ID"]))
if len(updates) > 0:
db.execute_many(dbo, "UPDATE animalwaitinglist SET " \
"UrgencyLastUpdatedDate=%s, " \
"UrgencyUpdateDate=%s, " \
"Urgency=%s " \
"WHERE ID=%s ", updates)
def auto_logout(dbo):
"""
Marks all users logged out who have been logged in for longer than
8 hours to account for people not logging out manually.
"""
cutoff = i18n.now() - datetime.timedelta(hours = -8)
db.execute(dbo, "DELETE FROM activeuser WHERE Since <= %s" % db.dd(cutoff))
def get_animalcontrol_find_simple(dbo, query = "", limit = 0):
"""
Returns rows for simple animal control searches.
query: The search criteria
"""
ors = []
query = query.replace("'", "`")
def add(field):
return utils.where_text_filter(dbo, field, query)
# If no query has been given, show open animal control records
# from the last 30 days
if query == "":
ors.append("ac.IncidentDateTime > %s AND ac.CompletedDate Is Null" % db.dd(subtract_days(now(dbo.timezone), 30)))
else:
if utils.is_numeric(query):
ors.append("ac.ID = " + str(utils.cint(query)))
ors.append(add("co.OwnerName"))
ors.append(add("ti.IncidentName"))
ors.append(add("ac.DispatchAddress"))
ors.append(add("ac.DispatchPostcode"))
ors.append(add("o1.OwnerName"))
ors.append(add("o2.OwnerName"))
ors.append(add("o3.OwnerName"))
ors.append(add("vo.OwnerName"))
ors.append(u"EXISTS(SELECT ad.Value FROM additional ad " \
"INNER JOIN additionalfield af ON af.ID = ad.AdditionalFieldID AND af.Searchable = 1 " \
"WHERE ad.LinkID=ac.ID AND ad.LinkType IN (%s) AND LOWER(ad.Value) LIKE '%%%s%%')" % (additional.INCIDENT_IN, query.lower()))
if not dbo.is_large_db:
ors.append(add("ac.CallNotes"))
ors.append(add("ac.AnimalDescription"))
sql = get_animalcontrol_query(dbo) + " WHERE " + " OR ".join(ors)
if limit > 0: sql += " LIMIT " + str(limit)
return db.query(dbo, sql)
def get_messages(dbo, user, roles, superuser):
"""
Returns a list of messages for the user given.
user: The user to get messages for
roles: A pipe separated list of roles the user is in (session.roles)
superuser: 1 if the user is a superuser
"""
messages = db.query(
dbo, "SELECT * FROM messages WHERE Expires >= %s ORDER BY Added DESC" %
db.dd(now(dbo.timezone)))
rv = []
unused = superuser
# Add messages our user can see
for m in messages:
if m["FORNAME"] == "*":
rv.append(m)
elif m["FORNAME"] == user:
rv.append(m)
elif m["CREATEDBY"] == user:
rv.append(m)
#elif superuser == 1:
# rv.append(m)
elif roles is not None and roles.find(m["FORNAME"]) != -1:
rv.append(m)
return rv
def auto_logout(dbo):
"""
Marks all users logged out who have been logged in for longer than
8 hours to account for people not logging out manually.
"""
cutoff = i18n.now() - datetime.timedelta(hours=-8)
db.execute(dbo, "DELETE FROM activeuser WHERE Since <= %s" % db.dd(cutoff))
def get_foundanimal_find_simple(dbo, query = "", limit = 0):
"""
Returns rows for simple found animal searches.
query: The search criteria
"""
ors = []
query = query.replace("'", "`")
def add(field):
return utils.where_text_filter(dbo, field, query)
# If no query has been given, show unreturned found animal records
# for the last 30 days
if query == "":
ors.append("a.DateFound > %s AND a.ReturnToOwnerDate Is Null" % db.dd(subtract_days(now(dbo.timezone), 30)))
else:
if utils.is_numeric(query):
ors.append("a.ID = " + str(utils.cint(query)))
ors.append(add("o.OwnerName"))
ors.append(add("a.AreaFound"))
ors.append(add("a.AreaPostcode"))
ors.append(u"EXISTS(SELECT ad.Value FROM additional ad " \
"INNER JOIN additionalfield af ON af.ID = ad.AdditionalFieldID AND af.Searchable = 1 " \
"WHERE ad.LinkID=a.ID AND ad.LinkType IN (%s) AND LOWER(ad.Value) LIKE '%%%s%%')" % (additional.FOUNDANIMAL_IN, query.lower()))
if not dbo.is_large_db:
ors.append(add("x.Sex"))
ors.append(add("b.BreedName"))
ors.append(add("c.BaseColour"))
ors.append(add("s.SpeciesName"))
ors.append(add("a.AgeGroup"))
ors.append(add("a.DistFeat"))
ors.append(add("a.Comments"))
sql = get_foundanimal_query(dbo) + " WHERE " + " OR ".join(ors)
if limit > 0: sql += " LIMIT " + str(limit)
return db.query(dbo, sql)
def get_animalcontrol_find_simple(dbo, query="", limit=0, onlyindexed=False):
"""
Returns rows for simple animal control searches.
query: The search criteria
"""
ors = []
query = query.replace("'", "`")
def add(field):
return utils.where_text_filter(dbo, field, query)
# If no query has been given, show open animal control records
# from the last 30 days
if query == "":
ors.append("ac.IncidentDateTime > %s AND ac.CompletedDate Is Null" %
db.dd(subtract_days(now(dbo.timezone), 30)))
else:
if utils.is_numeric(query):
ors.append("ac.ID = " + str(utils.cint(query)))
ors.append(add("co.OwnerName"))
ors.append(add("ti.IncidentName"))
ors.append(add("ac.DispatchAddress"))
ors.append(add("ac.DispatchPostcode"))
ors.append(add("o1.OwnerName"))
ors.append(add("o2.OwnerName"))
ors.append(add("o3.OwnerName"))
ors.append(add("vo.OwnerName"))
if not onlyindexed:
ors.append(add("ac.CallNotes"))
ors.append(add("ac.AnimalDescription"))
sql = get_animalcontrol_query(dbo) + " WHERE " + " OR ".join(ors)
if limit > 0: sql += " LIMIT " + str(limit)
return db.query(dbo, sql)
def get_lostanimal_find_simple(dbo, query = "", limit = 0, onlyindexed = False):
"""
Returns rows for simple lost animal searches.
query: The search criteria
"""
ors = []
query = query.replace("'", "`")
def add(field):
return utils.where_text_filter(dbo, field, query)
# If no query has been given, show unfound lost animal records
# for the last 30 days
if query == "":
ors.append("a.DateLost > %s AND a.DateFound Is Null" % db.dd(subtract_days(now(dbo.timezone), 30)))
else:
if utils.is_numeric(query):
ors.append("a.ID = " + str(utils.cint(query)))
ors.append(add("OwnerName"))
ors.append(add("AreaLost"))
ors.append(add("AreaPostcode"))
if not onlyindexed:
ors.append(add("SexName"))
ors.append(add("BreedName"))
ors.append(add("BaseColourName"))
ors.append(add("SpeciesName"))
ors.append(add("AgeGroup"))
ors.append(add("DistFeat"))
ors.append(add("Comments"))
sql = get_lostanimal_query() + " WHERE " + " OR ".join(ors)
if limit > 0: sql += " LIMIT " + str(limit)
return db.query(dbo, sql)
def get_lostanimal_find_simple(dbo, query="", limit=0, onlyindexed=False):
"""
Returns rows for simple lost animal searches.
query: The search criteria
"""
ors = []
query = query.replace("'", "`")
def add(field):
return utils.where_text_filter(dbo, field, query)
# If no query has been given, show unfound lost animal records
# for the last 30 days
if query == "":
ors.append("a.DateLost > %s AND a.DateFound Is Null" %
db.dd(subtract_days(now(dbo.timezone), 30)))
else:
if utils.is_numeric(query):
ors.append("a.ID = " + str(utils.cint(query)))
ors.append(add("o.OwnerName"))
ors.append(add("a.AreaLost"))
ors.append(add("a.AreaPostcode"))
if not onlyindexed:
ors.append(add("x.Sex"))
ors.append(add("b.BreedName"))
ors.append(add("c.BaseColour"))
ors.append(add("s.SpeciesName"))
ors.append(add("a.AgeGroup"))
ors.append(add("a.DistFeat"))
ors.append(add("a.Comments"))
sql = get_lostanimal_query(dbo) + " WHERE " + " OR ".join(ors)
if limit > 0: sql += " LIMIT " + str(limit)
return db.query(dbo, sql)
def update_vaccination_today(dbo, username, vaccid):
"""
Marks a vaccination record as given today.
"""
db.execute(dbo, db.make_update_user_sql(dbo, "animalvaccination", username, "ID = %d" % vaccid, (
( "DateOfVaccination", db.dd(now(dbo.timezone)) ),
None )
))
def complete_diary_note(dbo, username, diaryid):
"""
Marks a diary note completed as of right now
"""
db.execute(
dbo, "UPDATE diary SET DateCompleted = %s WHERE ID = %d" %
(db.dd(i18n.now(dbo.timezone)), int(diaryid)))
audit.edit(dbo, username, "diary", str(diaryid) + " => complete")
def get_overdue_donations(dbo):
"""
Returns owners that have an overdue regular donation
"""
sql = get_person_query()
sql += " INNER JOIN ownerdonation od ON od.OwnerID = o.ID " \
"WHERE od.Date Is Null AND od.DateDue Is Not Null AND od.DateDue <= %s" % (db.dd(now(dbo.timezone)))
return db.query(dbo, sql)
def get_recent_transfers(dbo, months = 1):
"""
Returns a list of transfers in the last "months" months.
"""
return db.query(dbo, "SELECT * FROM v_adoption " \
"WHERE MovementType = 3 AND MovementDate Is Not Null AND ReturnDate Is Null " \
"AND MovementDate > %s " \
"ORDER BY MovementDate DESC" % db.dd(i18n.subtract_days(i18n.now(dbo.timezone), months * 31)))
def get_overdue_donations(dbo):
"""
Returns owners that have an overdue regular donation
"""
sql = get_person_query()
sql += " INNER JOIN ownerdonation od ON od.OwnerID = o.ID " \
"WHERE od.Date Is Null AND od.DateDue Is Not Null AND od.DateDue <= %s" % (db.dd(now(dbo.timezone)))
return db.query(dbo, sql)
def return_movement(dbo, movementid, animalid, returndate):
"""
Returns a movement with the date given
"""
db.execute(
dbo, "UPDATE adoption SET ReturnDate = %s WHERE ID = %d" %
(db.dd(returndate), int(movementid)))
animal.update_animal_status(dbo, int(animalid))
def get_trial_adoptions(dbo, mode="ALL"):
"""
Returns a list of trial adoption movements.
If mode is EXPIRING, shows trials that end today or before.
If mode is ACTIVE, shows trials that end after today.
If mode is ALL, returns all trials.
"""
where = ""
if mode == "ALL":
where = ""
elif mode == "EXPIRING":
where = "AND TrialEndDate <= %s " % db.dd(i18n.now(dbo.timezone))
elif mode == "ACTIVE":
where = "AND TrialEndDate > %s " % db.dd(i18n.now(dbo.timezone))
return db.query(dbo, "SELECT * FROM v_adoption " \
"WHERE IsTrial = 1 AND MovementType = 1 AND (ReturnDate Is Null OR ReturnDate > %s) %s" \
"ORDER BY TrialEndDate" % (db.dd(i18n.now(dbo.timezone)), where))
def get_recent_transfers(dbo, months=1):
"""
Returns a list of transfers in the last "months" months.
"""
return db.query(dbo, "SELECT * FROM v_adoption " \
"WHERE MovementType = 3 AND MovementDate Is Not Null AND ReturnDate Is Null " \
"AND MovementDate > %s " \
"ORDER BY MovementDate DESC" % db.dd(i18n.subtract_days(i18n.now(dbo.timezone), months * 31)))
def set_web_preferred(dbo, username, mid):
"""
Makes the media with id the preferred for the web in the link
"""
link = db.query(dbo, "SELECT LinkID, LinkTypeID FROM media WHERE ID = %d" % int(mid))[0]
db.execute(dbo, "UPDATE media SET WebsitePhoto = 0 WHERE LinkID = %d AND LinkTypeID = %d" % ( int(link["LINKID"]), int(link["LINKTYPEID"])))
db.execute(dbo, "UPDATE media SET WebsitePhoto = 1, Date = %s WHERE ID = %d" % (db.dd(i18n.now(dbo.timezone)), int(mid) ))
audit.edit(dbo, username, "media", mid, str(mid) + ": web preferred for " + str(link["LINKID"]) + "/" + str(link["LINKTYPEID"]))
def update_test_today(dbo, username, testid, resultid):
"""
Marks a test record as performed today.
"""
db.execute(dbo, db.make_update_user_sql(dbo, "animaltest", username, "ID = %d" % testid, (
( "DateOfTest", db.dd(now(dbo.timezone)) ),
( "TestResultID", db.di(resultid) )
)))
def get_trial_adoptions(dbo, mode = "ALL"):
"""
Returns a list of trial adoption movements.
If mode is EXPIRING, shows trials that end today or before.
If mode is ACTIVE, shows trials that end after today.
If mode is ALL, returns all trials.
"""
where = ""
if mode == "ALL":
where = ""
elif mode == "EXPIRING":
where = "AND TrialEndDate <= %s " % db.dd(i18n.now(dbo.timezone))
elif mode == "ACTIVE":
where = "AND TrialEndDate > %s " % db.dd(i18n.now(dbo.timezone))
return db.query(dbo, "SELECT * FROM v_adoption " \
"WHERE IsTrial = 1 AND MovementType = 1 AND (ReturnDate Is Null OR ReturnDate > %s) %s" \
"ORDER BY TrialEndDate" % (db.dd(i18n.now(dbo.timezone)), where))
def get_recent_transfers(dbo, months = 1):
"""
Returns a list of transfers in the last "months" months.
"""
return db.query(dbo, get_movement_query(dbo) + \
"WHERE m.MovementType = 3 AND m.MovementDate Is Not Null AND m.ReturnDate Is Null " \
"AND m.MovementDate > %s " \
"ORDER BY m.MovementDate DESC" % db.dd(i18n.subtract_days(i18n.now(dbo.timezone), months * 31)))
def get_recent_adoptions(dbo, months=1):
"""
Returns a list of adoptions in the last "months" months.
"""
return db.query(dbo, get_movement_query(dbo) + \
"WHERE m.MovementType = 1 AND m.MovementDate Is Not Null AND m.ReturnDate Is Null " \
"AND m.MovementDate > %s " \
"ORDER BY m.MovementDate DESC" % db.dd(i18n.subtract_days(i18n.now(dbo.timezone), months * 31)))
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_vaccination_required(dbo, username, vaccid, newdate):
"""
Gives a vaccination record a required date of newdate, assuming
that newdate is valid.
"""
db.execute(dbo, db.make_update_user_sql(dbo, "animalvaccination", username, "ID = %d" % vaccid, (
( "DateRequired", db.dd(newdate) ),
)))
audit.edit(dbo, username, "animalvaccination", "%d required => %s" % (vaccid, str(newdate)))
def get_tests_outstanding(dbo, offset = "m31", locationfilter = ""):
"""
Returns a recordset of animals awaiting tests:
offset is m to go backwards, or p to go forwards with a number of days.
ID, ANIMALID, SHELTERCODE, ANIMALNAME, LOCATIONNAME, WEBSITEMEDIANAME, DATEREQUIRED, DATEOFTEST, COMMENTS, TESTNAME, RESULTNAME, TESTTYPEID
"""
ec = ""
offsetdays = utils.cint(offset[1:])
if offset.startswith("m"):
ec = " AND DateRequired >= %s AND DateRequired <= %s" % (db.dd( subtract_days(now(dbo.timezone), offsetdays)), db.dd(now(dbo.timezone)))
if offset.startswith("p"):
ec = " AND DateRequired >= %s AND DateRequired <= %s" % (db.dd(now(dbo.timezone)), db.dd( add_days(now(dbo.timezone), offsetdays)))
if locationfilter != "":
locationfilter = " AND ShelterLocation IN (%s)" % locationfilter
return db.query(dbo, "SELECT * FROM v_animaltest " \
"WHERE DateRequired Is Not Null AND DateOfTest Is Null " \
"AND DeceasedDate Is Null AND (Archived = 0 OR ActiveMovementType = 2) %s %s " \
"ORDER BY DateRequired, AnimalName" % (ec, locationfilter))
def get_recent_unneutered_adoptions(dbo, months = 1):
"""
Returns a list of adoptions in the last "months" months where the
animal remains unneutered.
"""
return db.query(dbo, "SELECT * FROM v_adoption " \
"WHERE MovementType = 1 AND MovementDate Is Not Null AND ReturnDate Is Null " \
"AND MovementDate > %s AND Neutered = 0 " \
"ORDER BY MovementDate DESC" % db.dd(i18n.subtract_days(i18n.now(dbo.timezone), months * 31)))
def get_recent_nonfosteradoption(dbo, months = 1):
"""
Returns a list of active movements that aren't reserves,
fosters, adoptions or transfers in the last "months" months.
"""
return db.query(dbo, "SELECT * FROM v_adoption " \
"WHERE MovementType > 3 AND MovementDate Is Not Null AND ReturnDate Is Null " \
"AND MovementDate > %s " \
"ORDER BY MovementDate DESC" % db.dd(i18n.subtract_days(i18n.now(dbo.timezone), months * 31)))
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 update_vaccination_today(dbo, username, vaccid):
"""
Marks a vaccination record as given today.
"""
db.execute(
dbo,
db.make_update_user_sql(
dbo, "animalvaccination", username, "ID = %d" % vaccid,
(("DateOfVaccination", db.dd(now(dbo.timezone))), None)))
def get_recent_nonfosteradoption(dbo, months=1):
"""
Returns a list of active movements that aren't reserves,
fosters, adoptions or transfers in the last "months" months.
"""
return db.query(dbo, "SELECT * FROM v_adoption " \
"WHERE MovementType > 3 AND MovementDate Is Not Null AND ReturnDate Is Null " \
"AND MovementDate > %s " \
"ORDER BY MovementDate DESC" % db.dd(i18n.subtract_days(i18n.now(dbo.timezone), months * 31)))
def rediarise_diary_note(dbo, username, diaryid, newdate):
"""
Moves a diary note on to the date supplied (newdate is a python date)
"""
db.execute(
dbo, "UPDATE diary SET DiaryDateTime = %s WHERE ID = %d" %
(db.dd(newdate), int(diaryid)))
audit.edit(dbo, username, "diary",
str(diaryid) + " => moved on to " + str(newdate))
def get_recent_unneutered_adoptions(dbo, months=1):
"""
Returns a list of adoptions in the last "months" months where the
animal remains unneutered.
"""
return db.query(dbo, "SELECT * FROM v_adoption " \
"WHERE MovementType = 1 AND MovementDate Is Not Null AND ReturnDate Is Null " \
"AND MovementDate > %s AND Neutered = 0 " \
"ORDER BY MovementDate DESC" % db.dd(i18n.subtract_days(i18n.now(dbo.timezone), months * 31)))
def receive_donation(dbo, username, did):
"""
Marks a donation received
"""
if id is None or did == "": return
db.execute(dbo, "UPDATE ownerdonation SET Date = %s WHERE ID = %d" % ( db.dd(i18n.now(dbo.timezone)), int(did)))
audit.edit(dbo, username, "ownerdonation", str(did) + ": received")
update_matching_transaction(dbo, username, int(did))
check_create_next_donation(dbo, username, did)
def get_accounts(dbo):
"""
Returns all of the accounts with reconciled/balance figures
ID, CODE, DESCRIPTION, ACCOUNTTYPE, DONATIONTYPEID, RECONCILED, BALANCE, VIEWROLEIDS, VIEWROLES, EDITROLEIDS, EDITROLES
If an accounting period has been set, balances are calculated from that point
"""
l = dbo.locale
pfilter = ""
aperiod = configuration.accounting_period(dbo)
if aperiod != "":
pfilter = " AND TrxDate >= " + db.dd(i18n.display2python(l, aperiod))
roles = db.query(
dbo,
"SELECT ar.*, r.RoleName FROM accountsrole ar INNER JOIN role r ON ar.RoleID = r.ID"
)
accounts = db.query(dbo, "SELECT a.*, at.AccountType AS AccountTypeName, " \
"dt.DonationName, " \
"(SELECT SUM(Amount) FROM accountstrx WHERE DestinationAccountID = a.ID%s) AS dest," \
"(SELECT SUM(Amount) FROM accountstrx WHERE SourceAccountID = a.ID%s) AS src," \
"(SELECT SUM(Amount) FROM accountstrx WHERE Reconciled = 1 AND DestinationAccountID = a.ID%s) AS recdest," \
"(SELECT SUM(Amount) FROM accountstrx WHERE Reconciled = 1 AND SourceAccountID = a.ID%s) AS recsrc " \
"FROM accounts a " \
"INNER JOIN lksaccounttype at ON at.ID = a.AccountType " \
"LEFT OUTER JOIN donationtype dt ON dt.ID = a.DonationTypeID " \
"ORDER BY a.AccountType, a.Code" % (pfilter, pfilter, pfilter, pfilter))
for a in accounts:
dest = a["DEST"]
src = a["SRC"]
recdest = a["RECDEST"]
recsrc = a["RECSRC"]
if dest is None: dest = 0
if src is None: src = 0
if recdest is None: recdest = 0
if recsrc is None: recsrc = 0
a["BALANCE"] = dest - src
a["RECONCILED"] = recdest - recsrc
if a["ACCOUNTTYPE"] == INCOME or a["ACCOUNTTYPE"] == EXPENSE:
a["BALANCE"] = abs(a["BALANCE"])
a["RECONCILED"] = abs(a["RECONCILED"])
viewroleids = []
viewrolenames = []
editroleids = []
editrolenames = []
for r in roles:
if r["ACCOUNTID"] == a["ID"] and r["CANVIEW"] == 1:
viewroleids.append(str(r["ROLEID"]))
viewrolenames.append(str(r["ROLENAME"]))
if r["ACCOUNTID"] == a["ID"] and r["CANEDIT"] == 1:
editroleids.append(str(r["ROLEID"]))
editrolenames.append(str(r["ROLENAME"]))
a["VIEWROLEIDS"] = "|".join(viewroleids)
a["VIEWROLES"] = "|".join(viewrolenames)
a["EDITROLEIDS"] = "|".join(editroleids)
a["EDITROLES"] = "|".join(editrolenames)
return accounts
def get_vaccinations_outstanding(dbo, offset = "m31", locationfilter = ""):
"""
Returns a recordset of animals awaiting vaccinations:
offset is m to go backwards, or p to go forwards with a number of days.
locationfilter is a comma separated list of internal locations to include animals in
ID, ANIMALID, SHELTERCODE, ANIMALNAME, LOCATIONNAME, WEBSITEMEDIANAME, DATEREQUIRED, DATEOFVACCINATION, COMMENTS, VACCINATIONTYPE, VACCINATIONID
"""
ec = ""
offsetdays = utils.cint(offset[1:])
if offset.startswith("m"):
ec = " AND DateRequired >= %s AND DateRequired <= %s" % (db.dd( subtract_days(now(dbo.timezone), offsetdays)), db.dd(now(dbo.timezone)))
if offset.startswith("p"):
ec = " AND DateRequired >= %s AND DateRequired <= %s" % (db.dd(now(dbo.timezone)), db.dd( add_days(now(dbo.timezone), offsetdays)))
if locationfilter != "":
locationfilter = " AND ShelterLocation IN (%s)" % locationfilter
return db.query(dbo, "SELECT * FROM v_animalvaccination " \
"WHERE DateRequired Is Not Null AND DateOfVaccination Is Null " \
"AND DeceasedDate Is Null AND (Archived = 0 OR ActiveMovementType = 2) %s %s " \
"ORDER BY DateRequired, AnimalName" % (ec, locationfilter))
def auto_cancel_reservations(dbo):
"""
Automatically cancels reservations after the daily amount set
"""
cancelafter = configuration.auto_cancel_reserves_days(dbo)
if cancelafter <= 0:
al.debug("auto reserve cancel is off.",
"movement.auto_cancel_reservations")
return
cancelcutoff = i18n.subtract_days(i18n.now(dbo.timezone), cancelafter)
al.debug("cutoff date: reservations < %s" % db.dd(cancelcutoff),
"movement.auto_cancel_reservations")
sql = "UPDATE adoption SET ReservationCancelledDate = %s " \
"WHERE MovementDate Is Null AND ReservationCancelledDate Is Null AND " \
"MovementType = 0 AND ReservationDate < %s" % ( db.dd(i18n.now(dbo.timezone)), db.dd(cancelcutoff))
count = db.execute(dbo, sql)
al.debug(
"cancelled %d reservations older than %d days" %
(count, int(cancelafter)), "movement.auto_cancel_reservations", dbo)
def update_test_today(dbo, username, testid, resultid):
"""
Marks a test record as performed today.
"""
db.execute(
dbo,
db.make_update_user_sql(dbo, "animaltest", username,
"ID = %d" % testid,
(("DateOfTest", db.dd(now(dbo.timezone))),
("TestResultID", db.di(resultid)))))
def get_movements(dbo, movementtype):
"""
Gets the list of movements of a particular type
(unreturned or returned after today and for animals who aren't deceased)
"""
return db.query(dbo, get_movement_query(dbo) + \
"WHERE m.MovementType = %d AND " \
"(m.ReturnDate Is Null OR m.ReturnDate > %s) " \
"AND a.DeceasedDate Is Null " \
"ORDER BY m.MovementDate DESC" % (int(movementtype), db.dd(i18n.now(dbo.timezone))))
def get_movements(dbo, movementtype):
"""
Gets the list of movements of a particular type
(unreturned or returned after today and for animals who aren't deceased)
"""
return db.query(dbo, "SELECT * FROM v_adoption " \
"WHERE MovementType = %d AND " \
"(ReturnDate Is Null OR ReturnDate > %s) " \
"AND DeceasedDate Is Null " \
"ORDER BY MovementDate DESC" % (int(movementtype), db.dd(i18n.now(dbo.timezone))))
def get_active_traploans(dbo):
"""
Returns all active traploan records
ID, TRAPTYPEID, TRAPTYPENAME, LOANDATE, DEPOSITRETURNDATE,
TRAPNUMBER, RETURNDUEDATE, RETURNDATE,
OWNERNAME
"""
return db.query(dbo, get_traploan_query(dbo) + \
"WHERE ot.ReturnDate Is Null OR ot.ReturnDate > %s " \
"ORDER BY ot.LoanDate DESC" % db.dd(now(dbo.timezone)))
def get_balance_fromto_date(dbo, accountid, fromdate, todate):
"""
Returns the balance of accountid from fromdate to todate.
"""
aid = int(accountid)
rows = db.query(dbo, "SELECT a.AccountType, " \
"(SELECT SUM(Amount) FROM accountstrx WHERE SourceAccountID = a.ID AND TrxDate >= %s AND TrxDate < %s) AS withdrawal," \
"(SELECT SUM(Amount) FROM accountstrx WHERE DestinationAccountID = a.ID AND TrxDate >= %s AND TrxDate < %s) AS deposit " \
"FROM accounts a " \
"WHERE a.ID = %d" % ( db.dd(fromdate), db.dd(todate), db.dd(fromdate), db.dd(todate), aid ))
r = rows[0]
deposit = r["DEPOSIT"]
withdrawal = r["WITHDRAWAL"]
if deposit is None: deposit = 0
if withdrawal is None: withdrawal = 0
balance = deposit - withdrawal
if r["ACCOUNTTYPE"] == INCOME or r["ACCOUNTTYPE"] == EXPENSE:
balance = abs(balance)
return balance
def get_movements(dbo, movementtype):
"""
Gets the list of movements of a particular type
(unreturned or returned after today and for animals who aren't deceased)
"""
return db.query(dbo, get_movement_query(dbo) + \
"WHERE m.MovementType = %d AND " \
"(m.ReturnDate Is Null OR m.ReturnDate > %s) " \
"AND a.DeceasedDate Is Null " \
"ORDER BY m.MovementDate DESC" % (int(movementtype), db.dd(i18n.now(dbo.timezone))))
def get_movements(dbo, movementtype):
"""
Gets the list of movements of a particular type
(unreturned or returned after today and for animals who aren't deceased)
"""
return db.query(dbo, "SELECT * FROM v_adoption " \
"WHERE MovementType = %d AND " \
"(ReturnDate Is Null OR ReturnDate > %s) " \
"AND DeceasedDate Is Null " \
"ORDER BY MovementDate DESC" % (int(movementtype), db.dd(i18n.now(dbo.timezone))))
def get_balance_fromto_date(dbo, accountid, fromdate, todate):
"""
Returns the balance of accountid from fromdate to todate.
"""
aid = int(accountid)
rows = db.query(dbo, "SELECT a.AccountType, " \
"(SELECT SUM(Amount) FROM accountstrx WHERE SourceAccountID = a.ID AND TrxDate >= %s AND TrxDate < %s) AS withdrawal," \
"(SELECT SUM(Amount) FROM accountstrx WHERE DestinationAccountID = a.ID AND TrxDate >= %s AND TrxDate < %s) AS deposit " \
"FROM accounts a " \
"WHERE a.ID = %d" % ( db.dd(fromdate), db.dd(todate), db.dd(fromdate), db.dd(todate), aid ))
r = rows[0]
deposit = r["DEPOSIT"]
withdrawal = r["WITHDRAWAL"]
if deposit is None: deposit = 0
if withdrawal is None: withdrawal = 0
balance = deposit - withdrawal
if r["ACCOUNTTYPE"] == INCOME or r["ACCOUNTTYPE"] == EXPENSE:
balance = abs(balance)
return balance
def complete_vaccination(dbo, username, vaccinationid):
"""
Marks a vaccination completed today
"""
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")
def get_active_traploans(dbo):
"""
Returns all active traploan records
ID, TRAPTYPEID, TRAPTYPENAME, LOANDATE, DEPOSITRETURNDATE,
TRAPNUMBER, RETURNDUEDATE, RETURNDATE,
OWNERNAME
"""
return db.query(dbo, get_traploan_query(dbo) + \
"WHERE ot.ReturnDate Is Null OR ot.ReturnDate > %s " \
"ORDER BY ot.LoanDate DESC" % db.dd(now(dbo.timezone)))