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 _upgrade(self, version, conn):
"""
"""
OAuthSchema._upgrade(self,version, conn)
if version < 3:
db.execute(conn, "CREATE TABLE smug_album (category TEXT, sub_category TEXT, title TEXT, last_updated TIMESTAMP, key TEXT, id TEXT, PRIMARY KEY (id))")
self.incrementDBVerson(conn)
def update_lookup(dbo,
iid,
lookup,
name,
desc="",
speciesid=0,
pfbreed="",
pfspecies="",
defaultcost=0):
t = LOOKUP_TABLES[lookup]
sql = ""
if lookup == "breed":
sql = "UPDATE breed SET BreedName=%s, BreedDescription=%s, PetFinderBreed=%s, SpeciesID=%s WHERE ID=%s" % (
db.ds(name), db.ds(desc), db.ds(pfbreed), db.di(speciesid),
db.di(iid))
elif lookup == "species":
sql = "UPDATE species SET SpeciesName=%s, SpeciesDescription=%s, PetFinderSpecies=%s WHERE ID=%s" % (
db.ds(name), db.ds(desc), db.ds(pfspecies), db.di(iid))
elif lookup == "donationtype" or lookup == "costtype" or lookup == "testtype" or lookup == "voucher" or lookup == "vaccinationtype":
sql = "UPDATE %s SET %s = %s, %s = %s, DefaultCost = %s WHERE ID=%s" % (
lookup, t[LOOKUP_NAMEFIELD], db.ds(name), t[LOOKUP_DESCFIELD],
db.ds(desc), db.di(defaultcost), db.di(iid))
elif t[LOOKUP_DESCFIELD] == "":
# No description
sql = "UPDATE %s SET %s=%s WHERE ID=%s" % (lookup, t[LOOKUP_NAMEFIELD],
db.ds(name), db.di(iid))
else:
# Name/Description
sql = "UPDATE %s SET %s=%s, %s=%s WHERE ID=%s" % (
lookup, t[LOOKUP_NAMEFIELD], db.ds(name), t[LOOKUP_DESCFIELD],
db.ds(desc), db.di(iid))
db.execute(dbo, sql)
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 register():
"""Register a new user.
Validates that the username is not already taken. Hashes the
password for security.
"""
if request.method == "POST":
username = request.form["username"]
password = request.form["password"]
db = get_db()
error = None
if not username:
error = "Username is required."
elif not password:
error = "Password is required."
elif (
db.execute("SELECT id FROM user WHERE username = ?", (username,)).fetchone()
is not None
):
error = "User {0} is already registered.".format(username)
if error is None:
# the name is available, store it in the database and go to
# the login page
db.execute(
"INSERT INTO user (username, password) VALUES (?, ?)",
(username, generate_password_hash(password)),
)
db.commit()
return redirect(url_for("login"))
flash(error)
return render_template("register.html")
def insert_donation_from_form(dbo, username, data):
"""
Creates a donation record from posted form data
"""
l = dbo.locale
donationid = db.get_id(dbo, "ownerdonation")
sql = db.make_insert_user_sql(dbo, "ownerdonation", username, (
( "ID", db.di(donationid)),
( "OwnerID", db.di(utils.df_ki(data, "person"))),
( "AnimalID", db.di(utils.df_ki(data, "animal"))),
( "MovementID", db.di(utils.df_ki(data, "movement"))),
( "DonationTypeID", utils.df_s(data, "type")),
( "DonationPaymentID", utils.df_s(data, "payment")),
( "Frequency", utils.df_s(data, "frequency")),
( "Donation", utils.df_m(data, "amount", l)),
( "DateDue", utils.df_d(data, "due", l)),
( "Date", utils.df_d(data, "received", l)),
( "NextCreated", db.di(0)),
( "IsGiftAid", utils.df_s(data, "giftaid")),
( "Comments", utils.df_t(data, "comments"))
))
db.execute(dbo, sql)
audit.create(dbo, username, "ownerdonation", str(donationid))
update_matching_transaction(dbo, username, donationid)
check_create_next_donation(dbo, username, donationid)
movement.update_movement_donation(dbo, utils.df_ki(data, "movement"))
return donationid
def update_trx_from_form(dbo, username, post):
"""
Updates a transaction from posted form data
"""
l = dbo.locale
amount = 0
source = 0
target = 0
deposit = post.money("deposit")
withdrawal = post.money("withdrawal")
account = post.integer("accountid")
trxid = post.integer("trxid")
other = get_account_id(dbo, post["otheraccount"])
if other == 0:
raise utils.ASMValidationError(i18n._("Account code '{0}' is not valid.", l).format(post["otheraccount"]))
if deposit > 0:
amount = deposit
source = other
target = account
else:
amount = withdrawal
source = account
target = other
sql = db.make_update_user_sql(dbo, "accountstrx", username, "ID=%d" % trxid, (
( "TrxDate", post.db_date("trxdate")),
( "Description", post.db_string("description")),
( "Reconciled", post.db_integer("reconciled")),
( "Amount", db.di(amount)),
( "SourceAccountID", db.di(source)),
( "DestinationAccountID", db.di(target))
))
preaudit = db.query(dbo, "SELECT * FROM accountstrx WHERE ID = %d" % trxid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM accountstrx WHERE ID = %d" % trxid)
audit.edit(dbo, username, "accountstrx", audit.map_diff(preaudit, postaudit))
def load(uid: int):
execute('SELECT * FROM reference_table WHERE student_id = %s', (uid,))
data = fetch()
if len(data) == 0:
return Reference(uid, [])
entry_list = [entry['professor_email'] for entry in data]
return Reference(uid, entry_list)
def do_import():
musicbrainzngs.set_useragent("SBUIfy", "0.0.1", "*****@*****.**")
logging.getLogger('musicbrainzngs').setLevel(logging.WARNING)
db.init()
# remove existing data
print('deleting existing data...')
# delete_images() todo: only delete artist & album images; move to image module
db.execute_script('truncate.sql')
print('done.', flush=True)
# import albums, songs, and artists for all labels
db.execute("SELECT * FROM label")
for label in db.get_cursor().fetchall():
print('importing label {}...'.format(label['name']))
import_label_releases(label['id'], label['mbid'])
print('done.', flush=True)
# wrap up
print('finishing...')
db.execute_script('set_hibernate_sequence.sql')
print('done.')
db.close()
def update_diary_from_form(dbo, username, post):
"""
Updates a diary note from form data
"""
l = dbo.locale
diaryid = post.integer("diaryid")
if post["diarydate"] == "":
raise utils.ASMValidationError(i18n._("Diary date cannot be blank", l))
if post.date("diarydate") is None:
raise utils.ASMValidationError(i18n._("Diary date is not valid", l))
if post["subject"] == "":
raise utils.ASMValidationError(
i18n._("Diary subject cannot be blank", l))
if post["note"] == "":
raise utils.ASMValidationError(i18n._("Diary note cannot be blank", l))
diarytime = post["diarytime"].strip()
if diarytime != "":
if diarytime.find(":") == -1:
raise utils.ASMValidationError(
i18n._("Invalid time, times should be in HH:MM format", l))
if not utils.is_numeric(diarytime.replace(":", "")):
raise utils.ASMValidationError(
i18n._("Invalid time, times should be in HH:MM format", l))
sql = db.make_update_user_sql(
dbo, "diary", username, "ID=%d" % diaryid,
(("DiaryDateTime", post.db_datetime("diarydate", "diarytime")),
("DiaryForName", post.db_string("diaryfor")),
("Subject", post.db_string("subject")),
("Note", post.db_string("note")),
("DateCompleted", post.db_date("completed"))))
preaudit = db.query(dbo, "SELECT * FROM diary WHERE ID=%d" % diaryid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM diary WHERE ID=%d" % diaryid)
audit.edit(dbo, username, "diary", audit.map_diff(preaudit, postaudit))
def add(self, professor_email):
execute("""INSERT INTO reference_table
VALUES (%s, %s)
ON DUPLICATE KEY UPDATE
student_id = VALUES(student_id),
professor_email = VALUES(professor_email)""",
(self.id, professor_email))
def reset_password(dbo, userid):
"""
Resets the password for the given user to "password"
"""
db.execute(
dbo, "UPDATE users SET Password = '******' WHERE ID = %d" %
(hash_password("password", True), int(userid)))
def insert_user_from_form(dbo, username, data):
"""
Creates a user record from posted form data. Uses
the roles key (which should be a comma separated list of
role ids) to create userrole records.
"""
nuserid = db.get_id(dbo, "users")
sql = db.make_insert_sql(
"users",
(("ID", db.di(nuserid)), ("UserName", utils.df_t(data, "username")),
("RealName", utils.df_t(data, "realname")),
("EmailAddress", utils.df_t(data, "email")),
("Password", db.ds(hash_password(utils.df_ks(data, "password"),
True))),
("SuperUser", utils.df_s(data, "superuser")),
("RecordVersion", db.di(0)), ("SecurityMap", db.ds("dummy")),
("OwnerID", utils.df_s(data, "person")),
("LocationFilter", utils.df_t(data, "locationfilter")),
("IPRestriction", utils.df_t(data, "iprestriction"))))
db.execute(dbo, sql)
audit.create(dbo, username, "users", str(nuserid))
roles = utils.df_ks(data, "roles").strip()
if roles != "":
for rid in roles.split(","):
if rid.strip() != "":
db.execute(
dbo, "INSERT INTO userrole VALUES (%d, %d)" %
(nuserid, int(rid)))
return nuserid
def get(query,args,docache=True):
#db.c.verbose = True
if hasattr(args,'values'):
vals = sorted(n+str(v) for n,v in args.items())
else:
args = list(args)
vals = args
name = hashlib.sha1((query +
''.join(str(arg) for arg in vals)).encode('utf-8'))
name = base64.b64encode(name.digest(),altchars=b'-_').decode().replace('=','')
if docache == False:
return name
print(query)
print("caching",args)
with db.transaction():
exists = db.execute("SELECT resultCache.cleanQuery($1)",(name,))[0][0]
if exists:
print("already cached")
else:
try:
db.execute('CREATE TABLE resultCache."q'+name+'" AS '+query,args)
except db.ProgrammingError as e:
if not 'already exists' in e.info['message'].decode('utf-8'): raise
db.execute('SELECT resultCache.updateQuery($1)',(name,))
return name;
def update_donation_from_form(dbo, username, data):
"""
Updates a donation record from posted form data
"""
l = dbo.locale
donationid = utils.df_ki(data, "donationid")
sql = db.make_update_user_sql(
dbo, "ownerdonation", username, "ID=%d" % donationid,
(("OwnerID", db.di(utils.df_ki(data, "person"))),
("AnimalID", db.di(utils.df_ki(data, "animal"))),
("MovementID", db.di(utils.df_ki(data, "movement"))),
("DonationTypeID", utils.df_s(data, "type")),
("DonationPaymentID", utils.df_s(data, "payment")),
("Frequency", utils.df_s(data, "frequency")),
("Donation", utils.df_m(data, "amount", l)),
("DateDue", utils.df_d(data, "due", l)),
("Date", utils.df_d(data, "received", l)),
("IsGiftAid", utils.df_s(data, "giftaid")),
("Comments", utils.df_t(data, "comments"))))
preaudit = db.query(
dbo, "SELECT * FROM ownerdonation WHERE ID = %d" % donationid)
db.execute(dbo, sql)
postaudit = db.query(
dbo, "SELECT * FROM ownerdonation WHERE ID = %d" % donationid)
audit.edit(dbo, username, "ownerdonation",
audit.map_diff(preaudit, postaudit))
update_matching_transaction(dbo, username, donationid)
check_create_next_donation(dbo, username, donationid)
movement.update_movement_donation(dbo, utils.df_ki(data, "movement"))
def rename_file(dbo, path, oldname, newname):
"""
Renames a file in the dbfs.
"""
db.execute(dbo, "UPDATE dbfs SET Name = '%s' WHERE Name = '%s' AND " \
"Path = '%s'" % \
(newname, oldname, path))
def delete_stocklevel(dbo, username, slid):
"""
Deletes a stocklevel record
"""
audit.delete(dbo, username, "stocklevel", slid, audit.dump_row(dbo, "stocklevel", slid))
db.execute(dbo, "DELETE FROM stockusage WHERE StockLevelID = %d" % slid)
db.execute(dbo, "DELETE FROM stocklevel WHERE ID = %d" % slid)
def create_path(dbo, path, name):
"""
Creates a new DBFS folder
"""
db.execute(
dbo, "INSERT INTO dbfs (ID, Name, Path) VALUES (%d, '%s', '%s')" %
(db.get_id(dbo, "dbfs"), name, path))
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 put_string_id(dbo, dbfsid, contents):
"""
Stores the file contents at the id given.
"""
s = base64.b64encode(contents)
db.execute(dbo,
"UPDATE dbfs SET Content = '%s' WHERE ID = %d" % (s, dbfsid))
def update_latlong(dbo, personid, latlong):
"""
Updates the latlong field.
"""
db.execute(
dbo, "UPDATE owner SET LatLong = %s WHERE ID = %d" %
(db.ds(latlong), int(personid)))
def update_movement_from_form(dbo, username, data):
"""
Updates a movement record from posted form data
"""
validate_movement_form_data(dbo, data)
l = dbo.locale
movementid = utils.df_ki(data, "movementid")
sql = db.make_update_user_sql(dbo, "adoption", username, "ID=%d" % movementid, (
( "AdoptionNumber", utils.df_t(data, "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"))
))
preaudit = db.query(dbo, "SELECT * FROM adoption WHERE ID = %d" % movementid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM adoption WHERE ID = %d" % movementid)
audit.edit(dbo, username, "adoption", audit.map_diff(preaudit, postaudit))
animal.update_animal_status(dbo, utils.df_ki(data, "animal"))
animal.update_variable_animal_data(dbo, utils.df_ki(data, "animal"))
update_movement_donation(dbo, movementid)
def delete_diarytask(dbo, username, taskid):
"""
Deletes a diary task
"""
audit.delete(dbo, username, "diarytaskhead", str(db.query(dbo, "SELECT * FROM diarytaskhead WHERE ID = %d" % int(taskid))))
db.execute(dbo, "DELETE FROM diarytaskdetail WHERE DiaryTaskHeadID = %d" % int(taskid))
db.execute(dbo, "DELETE FROM diarytaskhead WHERE ID = %d" % int(taskid))
def update(self):
'''
update data
>>> class User(Model):
... __table__ = 'user'
... id = IntegerField(primary_key=True)
... name = StringField()
>>> user1 = User.get(1)
>>> user1.name = 'hello'
>>> user1.update()
{'id': 1L, 'name': 'hello'}
>>> user1 = User.get(1)
>>> user1.name
u'hello'
'''
l = []
args = []
for k, v in self.__mapping__.iteritems():
if v.updateable:
l.append(v.name)
args.append(getattr(self, k, v.default))
l = ['`%s`=?' % x for x in l]
args.append(getattr(self, self.__primary_key__))
sql = 'update `%s` set %s where `%s`=?' % (self.__table__, ','.join(l), self.__mapping__[self.__primary_key__].name)
db.execute(sql, *args)
return self
def _upgrade(self, version, conn):
"""
"""
UserCategorySchema._upgrade(self,version, conn)
if version < 9:
db.execute(conn, "ALTER TABLE user_subcategory ADD category_id TEXT")
self.incrementDBVerson(conn)
def change_passwords(self, team_id, service_id, pwchange):
"""
Change the passwords for the given credentials.
Arguments:
team_id (int): The ID of the team the credential belongs to
service_id (int): The ID of the service the credential belongs to
pwchange (str): A series of user:pass combos separated by CRLFs
"""
pwchange = [line.split(':') for line in pwchange.split('\r\n')]
if service_id is not None:
cmd = ('UPDATE credential SET password=%s WHERE team_id=%s '
'AND service_id=%s AND username=%s')
elif domain_id is not None:
cmd = ('UPDATE credential SET password=%s WHERE team_id=%s '
'AND domain_id=%s AND username=%s')
for line in pwchange:
if len(line) >= 2:
username = re.sub('\s+', '', line[0]).lower()
password = re.sub('\s+', '', ':'.join(line[1:]))
if service_id is not None:
args = (password, team_id, service_id, username)
elif domain_id is not None:
args = (password, team_id, domain_id, username)
db.execute(cmd, args)
def delete_field(dbo, username, fid):
"""
Deletes the selected additional field, along with all data held by it.
"""
audit.delete(dbo, username, "additionalfield", fid, audit.dump_row(dbo, "additionalfield", fid))
db.execute(dbo, "DELETE FROM additionalfield WHERE ID = %d" % int(fid))
db.execute(dbo, "DELETE FROM additional WHERE AdditionalFieldID = %d" % int(fid))
def runner(user, sos=False):
try:
tweets = user_tweets[user]
conn = db.mk_connection()
for ts in tweets:
try:
tweet = db.execute(
conn,
'select tweet from connection_tweets where user_id = %s and tweet_id = %s'
% (user, ts))[0][0]
obj = json.loads(tweet)
t = obj['created_at']
t = datetime.datetime.strptime(
t, '%a %b %d %H:%M:%S +0000 %Y').strftime(
'%Y-%m-%d %H:%M:%S')
db.execute(
conn,
'update tweets set created_at = %s where user_id = %s and tweet_id = %s',
has_res=False,
args=[t, user, ts])
except:
print('Error', user, ts, t)
if sos:
raise
conn.commit()
conn.close()
print('Done', user)
except:
if sos:
raise
print('Error', user)
def save_values_for_link(dbo, post, linkid, linktype="animal"):
"""
Saves incoming additional field values from a form, clearing any
existing values first.
"""
delete_values_for_link(dbo, linkid, linktype)
af = get_field_definitions(dbo, linktype)
l = dbo.locale
for f in af:
key = "a." + str(f["MANDATORY"]) + "." + str(f["ID"])
if post.has_key(key):
val = post[key]
if f["FIELDTYPE"] == YESNO:
val = str(post.boolean(key))
elif f["FIELDTYPE"] == MONEY:
val = str(post.integer(key))
elif f["FIELDTYPE"] == DATE:
if len(val.strip()) > 0 and post.date(key) == None:
raise utils.ASMValidationError(
_(
"Additional date field '{0}' contains an invalid date.",
l).format(f["FIELDNAME"]))
val = python2display(dbo.locale, post.date(key))
sql = db.make_insert_sql("additional",
(("LinkType", db.di(f["LINKTYPE"])),
("LinkID", db.di(int(linkid))),
("AdditionalFieldID", db.di(f["ID"])),
("Value", db.ds(val))))
try:
db.execute(dbo, sql)
except Exception, err:
al.error("Failed saving additional field: %s" % str(err),
"animal.update_animal_from_form", dbo, sys.exc_info())
def makeResult():
result = db.execute("SELECT tags.name FROM tags WHERE id = ANY($1::INTEGER[])",(defaultTags.posi,))
for name in result:
yield name[0],False
result = db.execute("SELECT tags.name FROM tags WHERE id = ANY($1::INTEGER[])",(defaultTags.nega,))
for name in result:
yield name[0],True
def insert_donation_from_form(dbo, username, data):
"""
Creates a donation record from posted form data
"""
l = dbo.locale
donationid = db.get_id(dbo, "ownerdonation")
sql = db.make_insert_user_sql(
dbo, "ownerdonation", username,
(("ID", db.di(donationid)),
("OwnerID", db.di(utils.df_ki(data, "person"))),
("AnimalID", db.di(utils.df_ki(data, "animal"))),
("MovementID", db.di(utils.df_ki(data, "movement"))),
("DonationTypeID", utils.df_s(data, "type")),
("DonationPaymentID", utils.df_s(data, "payment")),
("Frequency", utils.df_s(data, "frequency")),
("Donation", utils.df_m(data, "amount", l)),
("DateDue", utils.df_d(data, "due", l)),
("Date", utils.df_d(data, "received", l)), ("NextCreated", db.di(0)),
("IsGiftAid", utils.df_s(data, "giftaid")),
("Comments", utils.df_t(data, "comments"))))
db.execute(dbo, sql)
audit.create(dbo, username, "ownerdonation", str(donationid))
update_matching_transaction(dbo, username, donationid)
check_create_next_donation(dbo, username, donationid)
movement.update_movement_donation(dbo, utils.df_ki(data, "movement"))
return donationid
def attach_link_from_form(dbo, username, linktype, linkid, post):
"""
Attaches a link to a web resource from a form
"""
existingvid = db.query_int(dbo, "SELECT COUNT(*) FROM media WHERE WebsiteVideo = 1 " \
"AND LinkID = %d AND LinkTypeID = %d" % ( int(linkid), int(linktype) ))
defvid = 0
if existingvid == 0 and post.integer("linktype") == MEDIATYPE_VIDEO_LINK:
defvid = 1
mediaid = db.get_id(dbo, "media")
url = post["linktarget"]
if url.find("://") == -1:
url = "http://" + url
al.debug("attached link %s" % url, "media.attach_file_from_form")
sql = db.make_insert_sql("media", (
( "ID", db.di(mediaid) ),
( "MediaName", db.ds(url) ),
( "MediaType", post.db_integer("linktype") ),
( "MediaNotes", post.db_string("comments") ),
( "WebsitePhoto", db.di(0) ),
( "WebsiteVideo", db.di(defvid) ),
( "DocPhoto", db.di(0) ),
( "ExcludeFromPublish", db.di(0) ),
# ASM2_COMPATIBILITY
( "NewSinceLastPublish", db.di(1) ),
( "UpdatedSinceLastPublish", db.di(0) ),
# ASM2_COMPATIBILITY
( "LinkID", db.di(linkid) ),
( "LinkTypeID", db.di(linktype) ),
( "Date", db.nowsql() )
))
db.execute(dbo, sql)
audit.create(dbo, username, "media", mediaid, str(mediaid) + ": for " + str(linkid) + "/" + str(linktype) + ": link to " + post["linktarget"])
def insert_stocklevel_from_form(dbo, post, username):
"""
Inserts a stocklevel item from a dialog. The post should include
the ID of the stocklevel to adjust and a usage record will be
written so usage data should be sent too.
"""
l = dbo.locale
slid = post.integer("id")
if post["name"] == "":
raise utils.ASMValidationError(_("Stock level must have a name", l))
if post["unitname"] == "":
raise utils.ASMValidationError(_("Stock level must have a unit", l))
nid = db.get_id(dbo, "stocklevel")
db.execute(dbo, db.make_insert_sql("stocklevel", (
( "ID", db.di(nid) ),
( "Name", post.db_string("name") ),
( "Description", post.db_string("description") ),
( "StockLocationID", post.db_integer("location") ),
( "UnitName", post.db_string("unitname") ),
( "Total", post.db_floating("total") ),
( "Balance", post.db_floating("balance") ),
( "Expiry", post.db_date("expiry") ),
( "BatchNumber", post.db_string("batchnumber") ),
( "Cost", post.db_integer("cost") ),
( "UnitPrice", post.db_integer("unitprice") ),
( "CreatedDate", db.todaysql() )
)))
insert_stockusage(dbo, username, slid, post.floating("balance"), post.date("usagedate"), post.integer("usagetype"), post["comments"])
audit.create(dbo, username, "stocklevel", nid, audit.dump_row(dbo, "stocklevel", nid))
return nid
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 update_stocklevel_from_form(dbo, post, username):
"""
Updates a stocklevel item from a dialog. The post should include
the ID of the stocklevel to adjust and a usage record will be
written so usage data should be sent too.
"""
l = dbo.locale
slid = post.integer("stocklevelid")
if post["name"] == "":
raise utils.ASMValidationError(_("Stock level must have a name", l))
if post["unitname"] == "":
raise utils.ASMValidationError(_("Stock level must have a unit", l))
preaudit = db.query(dbo, "SELECT * FROM stocklevel WHERE ID = %d" % slid)
if len(preaudit) == 0:
raise utils.ASMValidationError("stocklevel %d does not exist, cannot adjust" % slid)
db.execute(dbo, db.make_update_sql("stocklevel", "ID=%d" % slid, (
( "Name", post.db_string("name") ),
( "Description", post.db_string("description") ),
( "StockLocationID", post.db_integer("location") ),
( "UnitName", post.db_string("unitname") ),
( "Total", post.db_floating("total") ),
( "Balance", post.db_floating("balance") ),
( "Expiry", post.db_date("expiry") ),
( "BatchNumber", post.db_string("batchnumber") ),
( "Cost", post.db_integer("cost") ),
( "UnitPrice", post.db_integer("unitprice") )
)))
postaudit = db.query(dbo, "SELECT * FROM stocklevel WHERE ID = %d" % slid)
diff = postaudit[0]["BALANCE"] - preaudit[0]["BALANCE"]
if diff != 0: insert_stockusage(dbo, username, slid, diff, post.date("usagedate"), post.integer("usagetype"), post["comments"])
audit.edit(dbo, username, "animalcontrol", slid, audit.map_diff(preaudit, postaudit))
def check_and_scale_pdfs(dbo, force = False):
"""
Goes through all PDFs in the database to see if they have been
scaled (have a suffix of _scaled.pdf) and scales down any unscaled
ones.
If force is set, then all PDFs are checked and scaled again even
if they've been scaled before.
"""
if not configuration.scale_pdfs(dbo):
al.warn("ScalePDFs config option disabled in this database, not scaling pdfs", "media.check_and_scale_pdfs", dbo)
return
if force:
mp = db.query(dbo, \
"SELECT ID, MediaName FROM media WHERE LOWER(MediaName) LIKE '%.pdf' ORDER BY ID DESC")
else:
mp = db.query(dbo, \
"SELECT ID, MediaName FROM media WHERE LOWER(MediaName) LIKE '%.pdf' AND " \
"LOWER(MediaName) NOT LIKE '%_scaled.pdf' ORDER BY ID DESC")
for i, m in enumerate(mp):
filepath = db.query_string(dbo, "SELECT Path FROM dbfs WHERE Name='%s'" % m["MEDIANAME"])
original_name = str(m["MEDIANAME"])
new_name = str(m["ID"]) + "_scaled.pdf"
odata = dbfs.get_string(dbo, original_name)
data = scale_pdf(odata)
al.debug("scaling %s (%d of %d): old size %d, new size %d" % (new_name, i, len(mp), len(odata), len(data)), "check_and_scale_pdfs", dbo)
# Update the media entry with the new name
db.execute(dbo, "UPDATE media SET MediaName = '%s' WHERE ID = %d" % ( new_name, m["ID"]))
# Update the dbfs entry from old name to new name (will be overwritten in a minute but safer than delete)
dbfs.rename_file(dbo, filepath, original_name, new_name)
# Store the PDF file data with the new name - if there was a need to change it
if len(data) < len(odata):
dbfs.put_string(dbo, new_name, filepath, data)
al.debug("found and scaled %d pdfs" % len(mp), "media.check_and_scale_pdfs", dbo)
def update_donation_from_form(dbo, username, data):
"""
Updates a donation record from posted form data
"""
l = dbo.locale
donationid = utils.df_ki(data, "donationid")
sql = db.make_update_user_sql(dbo, "ownerdonation", username, "ID=%d" % donationid, (
( "OwnerID", db.di(utils.df_ki(data, "person"))),
( "AnimalID", db.di(utils.df_ki(data, "animal"))),
( "MovementID", db.di(utils.df_ki(data, "movement"))),
( "DonationTypeID", utils.df_s(data, "type")),
( "DonationPaymentID", utils.df_s(data, "payment")),
( "Frequency", utils.df_s(data, "frequency")),
( "Donation", utils.df_m(data, "amount", l)),
( "DateDue", utils.df_d(data, "due", l)),
( "Date", utils.df_d(data, "received", l)),
( "IsGiftAid", utils.df_s(data, "giftaid")),
( "Comments", utils.df_t(data, "comments"))
))
preaudit = db.query(dbo, "SELECT * FROM ownerdonation WHERE ID = %d" % donationid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM ownerdonation WHERE ID = %d" % donationid)
audit.edit(dbo, username, "ownerdonation", audit.map_diff(preaudit, postaudit))
update_matching_transaction(dbo, username, donationid)
check_create_next_donation(dbo, username, donationid)
movement.update_movement_donation(dbo, utils.df_ki(data, "movement"))
def delete_onlineform(dbo, username, formid):
"""
Deletes the specified onlineform and fields
"""
audit.delete(dbo, username, "onlineform", str(db.query(dbo, "SELECT * FROM onlineform WHERE ID=%d" % int(formid))))
db.execute(dbo, "DELETE FROM onlineformfield WHERE OnlineFormID = %d" % int(formid))
db.execute(dbo, "DELETE FROM onlineform WHERE ID = %d" % int(formid))
def insert_trx_from_form(dbo, username, post):
"""
Creates a transaction from posted form data
"""
l = dbo.locale
amount = 0
source = 0
target = 0
deposit = post.money("deposit")
withdrawal = post.money("withdrawal")
account = post.integer("accountid")
other = get_account_id(dbo, post["otheraccount"])
if other == 0:
raise utils.ASMValidationError(i18n._("Account code '{0}' is not valid.", l).format(post["otheraccount"]))
if deposit > 0:
amount = deposit
source = other
target = account
else:
amount = withdrawal
source = account
target = other
tid = db.get_id(dbo, "accountstrx")
sql = db.make_insert_user_sql(dbo, "accountstrx", username, (
( "ID", db.di(tid) ),
( "TrxDate", post.db_date("trxdate")),
( "Description", post.db_string("description")),
( "Reconciled", post.db_boolean("reconciled")),
( "Amount", db.di(amount)),
( "SourceAccountID", db.di(source)),
( "DestinationAccountID", db.di(target)),
( "OwnerDonationID", db.di(0))
))
db.execute(dbo, sql)
audit.create(dbo, username, "accountstrx", str(tid) + ": " + post["description"])
def cityPop(selectCity):
#selectCity
# added city selection dropdown
stateandcity =selectCity.split(',')
state = stateandcity[0][1::]
city = stateandcity[1][0:-1]
if city[0]=='\"':
city = city[1:-1]
print(state)
print(city)
statequery = \
"""
SELECT distinct(state, city_name) FROM city;
"""
_, cities = db.execute(statequery)
query = \
"""
SELECT state, city_name, city_population FROM city
WHERE state = \'%s\' and city_name = \'%s\';
"""
header, table = db.execute(query % (state, city))
return render_template('cityPopSelect.html', posts = cities, table=table, header=header)
def searchForLyrics(query):
query = cleanUpLyrics(query)
querySplit = query.split()
queryNoRepeat = list(set(querySplit))
wordsList = db.execute(DATA, 'SELECT word FROM words').fetchall()
wordsList = [word[0] for word in wordsList]
for qword in queryNoRepeat:
if qword not in wordsList:
return -2
masterlist = []
retlist = []
list1 = db.execute(
DATA, 'SELECT songs FROM words WHERE word = \"{}\"'.format(
queryNoRepeat[0])).fetchone()[0]
list2 = json.loads(list1)
masterlist.extend(list2)
for qword in queryNoRepeat:
list1 = db.execute(
DATA, 'SELECT songs FROM words WHERE word = \"{}\"'.format(
qword)).fetchone()[0]
list2 = json.loads(list1)
for songID in masterlist:
if songID not in list2:
masterlist.remove(songID)
for songID in masterlist:
lyricTemp = db.execute(
DATA, 'SELECT lyrics FROM names WHERE songID = {}'.format(
songID)).fetchone()[0]
if query in lyricTemp:
retlist.append(songID)
return retlist
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 insert_table_od(flightinfo):
colnames = [
'hex', 'squawk', 'flightnumber', 'aircraft', 'validposition',
'altitude', 'vert_rate', 'track', 'validtrack', 'speed', 'messages',
'seen', 'timestamp', 'precision', 'img_url', 'bound', 'object',
'callsign', 'origin', 'destination', 'airline', 'weather_status',
'weather_status_detail', 'temperature', 'pressure', 'humidity',
'visibility', 'wind_speed', 'wind_direction', 'cloud_coverage',
'detection_time', 'sunrise_time', 'sunset_time', 'location_name',
"framerate_avg", "det_dist_surf", "det_eta", "geom"
]
makepoint = "ST_TRANSFORM(ST_setSRID(ST_MakePoint(%s, %s),4326),32633)"
flightinfo.update({"geom": ""})
vals = []
for name in colnames:
if name == "geom":
pass
else:
vals.append(flightinfo[name])
vals_str_list = ["%s"] * len(vals)
vals_str = ", ".join(vals_str_list)
vals_str = vals_str + ", " + makepoint
vals.append(flightinfo["lon"])
vals.append((flightinfo["lat"]))
cols = ', '.join(colnames)
sql = """INSERT INTO public.od3 ({cols}) VALUES ({vals_str})""".format(
cols=cols, vals_str=vals_str)
db.execute((sql, vals))
def update_file_content(dbo, username, mid, content):
"""
Updates the dbfs content for the file pointed to by id
"""
dbfs.replace_string(dbo, content, get_name_for_id(dbo, mid))
db.execute(dbo, "UPDATE media SET Date = %s WHERE ID = %d" % ( db.nowsql(), int(mid) ))
audit.edit(dbo, username, "media", str(mid) + " changed file contents")
def update_diary_from_form(dbo, username, data):
"""
Updates a diary note from form data
"""
l = dbo.locale
diaryid = utils.df_ki(data, "diaryid")
if utils.df_ks(data, "diarydate") == "":
raise utils.ASMValidationError(i18n._("Diary date cannot be blank", l))
if utils.df_kd(data, "diarydate", l) is None:
raise utils.ASMValidationError(i18n._("Diary date is not valid", l))
if utils.df_ks(data, "subject") == "":
raise utils.ASMValidationError(i18n._("Diary subject cannot be blank", l))
if utils.df_ks(data, "note") == "":
raise utils.ASMValidationError(i18n._("Diary note cannot be blank", l))
diarytime = utils.df_ks(data, "diarytime").strip()
if diarytime != "":
if diarytime.find(":") == -1:
raise utils.ASMValidationError(i18n._("Invalid time, times should be in HH:MM format", l))
if not utils.is_numeric(diarytime.replace(":", "")):
raise utils.ASMValidationError(i18n._("Invalid time, times should be in HH:MM format", l))
sql = db.make_update_user_sql(dbo, "diary", username, "ID=%d" % diaryid, (
( "DiaryDateTime", utils.df_dt(data, "diarydate", "diarytime", l) ),
( "DiaryForName", utils.df_t(data, "diaryfor") ),
( "Subject", utils.df_t(data, "subject") ),
( "Note", utils.df_t(data, "note") ),
( "Comments", utils.df_t(data, "comments") ),
( "DateCompleted", utils.df_d(data, "completed", l) )
))
preaudit = db.query(dbo, "SELECT * FROM diary WHERE ID=%d" % diaryid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM diary WHERE ID=%d" % diaryid)
audit.edit(dbo, username, "diary", audit.map_diff(preaudit, postaudit))
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 get_fields_mapping(dbo):
tablename_fields = config.settings["db"]["tablename_fields"]
## extract the mapping
sql = "select field, rename_to from %s where rename_to is not null" % tablename
db.execute(dbo, sql)
mapping = dbo["cursor"].fetchall()
return dict(mapping)
def update_regimen_from_form(dbo, username, data):
"""
Updates a regimen record from posted form data
"""
l = dbo.locale
regimenid = utils.df_ki(data, "regimenid")
if utils.df_ks(data, "treatmentname") == "":
raise utils.ASMValidationError(_("Treatment name cannot be blank", l))
sql = db.make_update_user_sql(
dbo, "animalmedical", username, "ID=%d" % regimenid,
(("TreatmentName", utils.df_t(data, "treatmentname")),
("Dosage", utils.df_t(data, "dosage")),
("Status", utils.df_s(data, "status")),
("Cost", utils.df_m(data, "cost", l)),
("Comments", utils.df_t(data, "comments"))))
preaudit = db.query(dbo,
"SELECT * FROM animalmedical WHERE ID=%d" % regimenid)
db.execute(dbo, sql)
postaudit = db.query(dbo,
"SELECT * FROM animalmedical WHERE ID=%d" % regimenid)
audit.edit(
dbo, username, "animalmedical",
audit.map_diff(preaudit, postaudit, ["TREATMENTNAME", "DOSAGE"]))
update_medical_treatments(dbo, username, utils.df_ki(data, "regimenid"))
def _upgrade(self, version, conn):
"""
"""
SmugMugAlbumSchema._upgrade(self,version, conn)
if version < 4:
db.execute(conn, "CREATE TABLE smug_image (album_id TEXT, last_updated TIMESTAMP, md5_sum TEXT, key TEXT, id TEXT, filename TEXT, PRIMARY KEY (id), FOREIGN KEY (album_id) REFERENCES smug_album(id))")
self.incrementDBVerson(conn)
def update_vaccination_from_form(dbo, username, data):
"""
Updates a vaccination record from posted form data
"""
l = dbo.locale
vaccid = utils.df_ki(data, "vaccid")
if utils.df_kd(data, "required", l) is None:
raise utils.ASMValidationError(
_("Required date must be a valid date", l))
sql = db.make_update_user_sql(
dbo, "animalvaccination", username, "ID=%d" % vaccid,
(("AnimalID", db.di(utils.df_ki(data, "animal"))),
("VaccinationID", utils.df_s(data, "type")),
("DateOfVaccination", utils.df_d(data, "given", l)),
("DateRequired", utils.df_d(data, "required", l)),
("Cost", utils.df_m(data, "cost", l)),
("Comments", utils.df_t(data, "comments"))))
preaudit = db.query(
dbo, "SELECT * FROM animalvaccination WHERE ID = %d" % vaccid)
db.execute(dbo, sql)
postaudit = db.query(
dbo, "SELECT * FROM animalvaccination WHERE ID = %d" % vaccid)
audit.edit(dbo, username, "animalvaccination",
audit.map_diff(preaudit, postaudit))
def _upgrade(self, version, conn):
"""
"""
AddIndexesSchema._upgrade(self,version, conn)
if version < 11:
db.execute(conn, "ALTER TABLE image_log ADD action TEXT")
self.incrementDBVerson(conn)
def update_test_from_form(dbo, username, data):
"""
Updates a test record from posted form data
"""
l = dbo.locale
testid = utils.df_ki(data, "testid")
if utils.df_kd(data, "required", l) is None:
raise utils.ASMValidationError(
_("Required date must be a valid date", l))
sql = db.make_update_user_sql(
dbo, "animaltest", username, "ID=%d" % testid,
(("AnimalID", db.di(utils.df_ki(data, "animal"))),
("TestTypeID", utils.df_s(data, "type")),
("TestResultID", utils.df_s(data, "result")),
("DateOfTest", utils.df_d(data, "given", l)),
("DateRequired", utils.df_d(data, "required", l)),
("Cost", utils.df_m(data, "cost", l)),
("Comments", utils.df_t(data, "comments"))))
preaudit = db.query(dbo, "SELECT * FROM animaltest WHERE ID = %d" % testid)
db.execute(dbo, sql)
postaudit = db.query(dbo,
"SELECT * FROM animaltest WHERE ID = %d" % testid)
audit.edit(dbo, username, "animaltest",
audit.map_diff(preaudit, postaudit))
# ASM2_COMPATIBILITY
update_asm2_tests(dbo, testid)
def run():
statements = []
statements.append('DROP table if exists public.a_table')
statements.append('CREATE TABLE public.a_table (id varchar NULL)')
db.execute(";".join(statements))
def update_foundanimal_from_form(dbo, data, username):
"""
Updates a found animal record from the screen
data: The webpy data object containing form parameters
"""
l = dbo.locale
lfid = utils.df_ki(data, "id")
if utils.df_kd(data, "datefound", l) is None:
raise utils.ASMValidationError(_("Date found cannot be blank", l))
if utils.df_kd(data, "datereported", l) is None:
raise utils.ASMValidationError(_("Date reported cannot be blank", l))
if utils.df_ki(data, "owner") == "0":
raise utils.ASMValidationError(_("Found animals must have a contact", l))
preaudit = db.query(dbo, "SELECT * FROM animalfound WHERE ID = %d" % lfid)
db.execute(dbo, db.make_update_user_sql(dbo, "animalfound", username, "ID=%d" % lfid, (
( "AnimalTypeID", utils.df_s(data, "species")),
( "DateReported", utils.df_d(data, "datereported", l)),
( "ReturnToOwnerDate", utils.df_d(data, "returntoownerdate", l)),
( "DateFound", utils.df_d(data, "datefound", l)),
( "Sex", utils.df_s(data, "sex")),
( "BreedID", utils.df_s(data, "breed")),
( "AgeGroup", utils.df_t(data, "agegroup")),
( "BaseColourID", utils.df_s(data, "colour")),
( "DistFeat", utils.df_t(data, "markings")),
( "AreaFound", utils.df_t(data, "areafound")),
( "AreaPostcode", utils.df_t(data, "areapostcode")),
( "OwnerID", utils.df_s(data, "owner")),
( "Comments", utils.df_t(data, "comments"))
)))
additional.save_values_for_link(dbo, data, lfid, "foundanimal")
postaudit = db.query(dbo, "SELECT * FROM animalfound WHERE ID = %d" % lfid)
audit.edit(dbo, username, "animalfound", audit.map_diff(preaudit, postaudit))
def save_values_for_link(dbo, post, linkid, linktype = "animal"):
"""
Saves incoming additional field values from a form, clearing any
existing values first.
"""
delete_values_for_link(dbo, linkid, linktype)
af = get_field_definitions(dbo, linktype)
l = dbo.locale
for f in af:
key = "a." + str(f["MANDATORY"]) + "." + str(f["ID"])
if post.has_key(key):
val = post[key]
if f["FIELDTYPE"] == YESNO:
val = str(post.boolean(key))
elif f["FIELDTYPE"] == MONEY:
val = str(post.integer(key))
elif f["FIELDTYPE"] == DATE:
if len(val.strip()) > 0 and post.date(key) == None:
raise utils.ASMValidationError(_("Additional date field '{0}' contains an invalid date.", l).format(f["FIELDNAME"]))
val = python2display(dbo.locale, post.date(key))
sql = db.make_insert_sql("additional", (
( "LinkType", db.di(f["LINKTYPE"]) ),
( "LinkID", db.di(int(linkid)) ),
( "AdditionalFieldID", db.di(f["ID"]) ),
( "Value", db.ds(val) ) ))
try:
db.execute(dbo, sql)
except Exception,err:
al.error("Failed saving additional field: %s" % str(err), "animal.update_animal_from_form", dbo, sys.exc_info())
def insert_foundanimal_from_form(dbo, data, username):
"""
Inserts a new found animal record from the screen
data: The webpy data object containing form parameters
"""
l = dbo.locale
if utils.df_kd(data, "datefound", l) is None:
raise utils.ASMValidationError(_("Date found cannot be blank", l))
if utils.df_kd(data, "datereported", l) is None:
raise utils.ASMValidationError(_("Date reported cannot be blank", l))
if utils.df_ki(data, "owner") == "0":
raise utils.ASMValidationError(_("Found animals must have a contact", l))
nid = db.get_id(dbo, "animalfound")
db.execute(dbo, db.make_insert_user_sql(dbo, "animalfound", username, (
( "ID", db.di(nid)),
( "AnimalTypeID", utils.df_s(data, "species")),
( "DateReported", utils.df_d(data, "datereported", l)),
( "ReturnToOwnerDate", utils.df_d(data, "returntoownerdate", l)),
( "DateFound", utils.df_d(data, "datefound", l)),
( "Sex", utils.df_s(data, "sex")),
( "BreedID", utils.df_s(data, "breed")),
( "AgeGroup", utils.df_t(data, "agegroup")),
( "BaseColourID", utils.df_s(data, "colour")),
( "DistFeat", utils.df_t(data, "markings")),
( "AreaFound", utils.df_t(data, "areafound")),
( "AreaPostcode", utils.df_t(data, "areapostcode")),
( "OwnerID", utils.df_s(data, "owner")),
( "Comments", utils.df_t(data, "comments"))
)))
audit.create(dbo, username, "animalfound", str(nid))
return nid
def refreshProxy(proxy_list, source):
for proxy1 in proxy_list:
if util.verifyProxy(proxy1):
sql = 'insert ignore into http_proxy(proxy_addr,valid,source_site,created_time,modified_time) values("{proxy}",0,"{source_site}",now(),now())'.format(
proxy=proxy1, source_site=source)
print(sql)
db.execute(sql)
def mark_reconciled(dbo, trxid):
"""
Marks a transaction reconciled.
"""
db.execute(
dbo,
"UPDATE accountstrx SET Reconciled = 1 WHERE ID = %d" % int(trxid))
def check_and_scale_pdfs(dbo):
"""
Goes through all PDFs in the database to see if they have been
scaled (have a suffix of _scaled.pdf) and scales down any unscaled
ones.
"""
if not SCALE_PDF_DURING_BATCH:
al.warn("SCALE_PDF_DURING_BATCH is disabled, not scaling pdfs",
"media.check_and_scale_pdfs", dbo)
if not configuration.scale_pdfs(dbo):
al.warn(
"ScalePDFs config option disabled in this database, not scaling pdfs",
"media.check_and_scale_pdfs", dbo)
mp = db.query(dbo, \
"SELECT MediaName FROM media WHERE LOWER(MediaName) LIKE '%.pdf' AND " \
"LOWER(MediaName) NOT LIKE '%_scaled.pdf'")
for m in mp:
filepath = db.query_string(
dbo, "SELECT Path FROM dbfs WHERE Name='%s'" % m["MEDIANAME"])
original_name = str(m["MEDIANAME"])
new_name = str(m["MEDIANAME"])
new_name = new_name[0:len(new_name) - 4] + "_scaled.pdf"
odata = dbfs.get_string(dbo, original_name)
data = scale_pdf(odata)
# Update the media entry with the new name
db.execute(dbo, "UPDATE media SET MediaName = '%s' WHERE MediaName = '%s'" % \
( new_name, original_name))
# Update the dbfs entry with the new name
dbfs.rename_file(dbo, filepath, original_name, new_name)
# Update the PDF file data
dbfs.put_string(dbo, new_name, filepath, data)
al.debug("found and scaled %d pdfs" % len(mp),
"media.check_and_scale_pdfs", dbo)
def operation(change):
blurb = db.execute("SELECT blurb FROM descriptions WHERE id = $1",
(ident,))
if blurb:
blurb = blurb[0][0]
db.execute('BEGIN')
blurb = change(blurb,changed)
