def search(q):
like_str = '{}%'.format(q)
with db.cursor() as c:
alliances = db.query(c, '''
SELECT alliance_id, alliance_name FROM alliances
WHERE alliance_name LIKE ? LIMIT 25
''', like_str)
corps = db.query(c, '''
SELECT corporation_id, corporation_name FROM corporations
WHERE corporation_name LIKE ? LIMIT 25
''', like_str)
chars = db.query(c, '''
SELECT character_id, character_name FROM characters
WHERE character_name LIKE ? LIMIT 25
''', like_str)
systems = db.query(c, '''
SELECT solarSystemID AS system_id, solarSystemName AS system_name
FROM eve.mapSolarSystems
WHERE solarSystemName LIKE ? LIMIT 5
''', like_str)
ships = db.query(c, '''
SELECT typeID AS ship_id, typeName AS ship_name FROM eve.invTypes
JOIN eve.invGroups ON invTypes.groupID = invGroups.groupID
WHERE typeName LIKE ? AND invGroups.categoryID = 6 LIMIT 5
''', like_str) # 6 == ship
return {
'alliances': alliances,
'corporations': corps,
'characters': chars,
'systems': systems,
'ships': ships,
}
def update_movement_from_form(dbo, username, post):
"""
Updates a movement record from posted form data
"""
validate_movement_form_data(dbo, post)
movementid = post.integer("movementid")
sql = db.make_update_user_sql(dbo, "adoption", username, "ID=%d" % movementid, (
( "AdoptionNumber", post.db_string("adoptionno")),
( "OwnerID", post.db_integer("person")),
( "RetailerID", post.db_integer("retailer")),
( "AnimalID", post.db_integer("animal")),
( "OriginalRetailerMovementID", post.db_integer("originalretailermovement")),
( "MovementDate", post.db_date("movementdate")),
( "MovementType", post.db_integer("type")),
( "ReturnDate", post.db_date("returndate")),
( "ReturnedReasonID", post.db_integer("returncategory")),
( "Donation", post.db_integer("donation")),
( "InsuranceNumber", post.db_string("insurance")),
( "ReasonForReturn", post.db_string("reason")),
( "ReservationDate", post.db_date("reservationdate")),
( "ReservationCancelledDate", post.db_date("reservationcancelled")),
( "ReservationStatusID", post.db_integer("reservationstatus")),
( "IsTrial", post.db_boolean("trial")),
( "IsPermanentFoster", post.db_boolean("permanentfoster")),
( "TrialEndDate", post.db_date("trialenddate")),
( "Comments", post.db_string("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", movementid, audit.map_diff(preaudit, postaudit))
animal.update_animal_status(dbo, post.integer("animal"))
animal.update_variable_animal_data(dbo, post.integer("animal"))
update_movement_donation(dbo, 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 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 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 update_field_from_form(dbo, username, post):
"""
Updates an additional field record. All aspects of an additional
field can be changed after creation since only the ID ties things
together.
"""
aid = post.integer("id")
sql = db.make_update_sql(
"additionalfield",
"ID=%d" % aid,
(
("FieldName", post.db_string("name")),
("FieldLabel", post.db_string("label")),
("ToolTip", post.db_string("tooltip")),
("LookupValues", post.db_string("lookupvalues")),
("Mandatory", post.db_boolean("mandatory")),
("FieldType", post.db_integer("type")),
("LinkType", post.db_integer("link")),
("DisplayIndex", post.db_integer("displayindex")),
),
)
preaudit = db.query(dbo, "SELECT * FROM additionalfield WHERE ID = %d" % aid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM additionalfield WHERE ID = %d" % aid)
audit.edit(dbo, username, "additionalfield", audit.map_diff(preaudit, postaudit))
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 get(self, year):
"""Display planet and faction data for a timeline
Changes planet colors, titles, and classes of all planets, displaying
only those whose faction has not been unchecked in the faction index. Pulls
faction information (e.g. total number of planets) in order to update the
faction key."""
r = Response()
planets = db.query('SELECT P_ID, P. P_Name, F.F_ShortName, F_Name, F_HexColor FROM Planets P INNER JOIN Planets_Timelines P_T ON P.P_Name = P_T.P_Name INNER JOIN Timelines T ON P_T.T_StartYear = T.T_StartYear INNER JOIN Factions F ON P_T.F_ShortName = F.F_ShortName WHERE T.T_StartYear = %s' % year)
data = {}
for planet in planets:
data[planet['P_ID']] = planet
# Update the planets' color, tooltip, and classes (based on owning faction)
r.script('universe.update_planets(%s, %s)' % (year, json.dumps(data, default=str)))
factions = db.query('SELECT COUNT(P_Name) as num_planets, F.* FROM Factions F LEFT OUTER JOIN Planets_Timelines PT ON F.F_ShortName = PT.F_ShortName AND T_StartYear = %s GROUP BY F_Name ORDER BY F_Class, num_planets DESC', year)
data = {}
for faction in factions:
data[faction['F_ShortName']] = faction
# update faction key with # owned planets and display only factions which own planets
r.script('universe.update_factions(%s, %s)' % (year, json.dumps(data, default=str)))
return r.get_json()
def thread(self, path):
content = urllib.unquote(path)
comparator = '<'
if self['comparator'] == 'gt': comparator = '>'
posts = yield db.query("""
select ops.page_id, posts.thread_id, posts.content, posts.dt, posts.user_id, files.url as avatar_url
from posts, ops, files, users
where posts.thread_id = ops.id
and ops.content = %%(content)s
and posts.user_id = users.id
and files.id = posts.avatar_id
and posts.dt %(comparator)s %%(dt)s
order by posts.dt desc
limit 10
""" % {'comparator': comparator},
{'content':content, 'dt':self.arg('dt', '3000-01-01T01:01:01') })
if not posts: raise Stink('no thread named %s' % path)
page = yield db.query("""
select files.id, files.url from files, pages
where files.set_id = %(page)s
and pages.id = %(page)s""",
{'page': posts[0]['page_id']})
user = yield self.user()
defer.returnValue({'user': user, 'thread': content, 'page' : page, 'posts' : list(reversed(
[{'content': post['content'], 'dt': post['dt'], 'user':{'avatar': post['avatar_url'], 'id':post['user_id']}} for post in posts]
))})
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 update_user_from_form(dbo, username, post):
"""
Updates 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.
"""
userid = post.integer("userid")
sql = db.make_update_sql("users", "ID=%d" % userid, (
( "RealName", post.db_string("realname")),
( "EmailAddress", post.db_string("email")),
( "SuperUser", post.db_integer("superuser")),
( "OwnerID", post.db_integer("person")),
( "SiteID", post.db_integer("site")),
( "LocationFilter", post.db_string("locationfilter")),
( "IPRestriction", post.db_string("iprestriction"))
))
preaudit = db.query(dbo, "SELECT * FROM users WHERE ID = %d" % userid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM users WHERE ID = %d" % userid)
audit.edit(dbo, username, "users", userid, audit.map_diff(preaudit, postaudit, [ "USERNAME", ]))
db.execute(dbo, "DELETE FROM userrole WHERE UserID = %d" % userid)
roles = post["roles"].strip()
if roles != "":
for rid in roles.split(","):
if rid.strip() != "":
db.execute(dbo, "INSERT INTO userrole VALUES (%d, %d)" % (userid, int(rid)))
def update_transport_from_form(dbo, username, post):
"""
Updates a movement record from posted form data
"""
transportid = post.integer("transportid")
sql = db.make_update_user_sql(dbo, "animaltransport", username, "ID=%d" % transportid, (
( "AnimalID", post.db_integer("animal")),
( "DriverOwnerID", post.db_integer("driver")),
( "PickupOwnerID", post.db_integer("pickup")),
( "PickupAddress", post.db_string("pickupaddress")),
( "PickupTown", post.db_string("pickuptown")),
( "PickupCounty", post.db_string("pickupcounty")),
( "PickupDateTime", post.db_datetime("pickupdate", "pickuptime")),
( "PickupPostcode", post.db_string("pickuppostcode")),
( "DropoffOwnerID", post.db_integer("dropoff")),
( "DropoffAddress", post.db_string("dropoffaddress")),
( "DropoffTown", post.db_string("dropofftown")),
( "DropoffCounty", post.db_string("dropoffcounty")),
( "DropoffPostcode", post.db_string("dropoffpostcode")),
( "DropoffDateTime", post.db_datetime("dropoffdate", "dropofftime")),
( "Status", post.db_integer("status")),
( "Miles", post.db_integer("miles")),
( "Cost", post.db_integer("cost")),
( "CostPaidDate", post.db_date("costpaid")),
( "Comments", post.db_string("comments"))
))
preaudit = db.query(dbo, "SELECT * FROM animaltransport WHERE ID = %d" % transportid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM animaltransport WHERE ID = %d" % transportid)
audit.edit(dbo, username, "animaltransport", transportid, audit.map_diff(preaudit, postaudit))
def update_waitinglist_from_form(dbo, data, username):
"""
Updates a waiting list record from the screen
data: The webpy data object containing form parameters
"""
l = dbo.locale
wlid = utils.df_ki(data, "id")
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))
preaudit = db.query(dbo, "SELECT * FROM animalwaitinglist WHERE ID = %d" % wlid)
db.execute(dbo, db.make_update_user_sql(dbo, "animalwaitinglist", username, "ID=%d" % wlid, (
( "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", utils.df_d(data, "dateoflastownercontact", l)),
( "ReasonForRemoval", utils.df_t(data, "reasonforremoval")),
( "Comments", utils.df_t(data, "comments"))
)))
additional.save_values_for_link(dbo, data, wlid, "waitinglist")
postaudit = db.query(dbo, "SELECT * FROM animalwaitinglist WHERE ID = %d" % wlid)
audit.edit(dbo, username, "animalwaitinglist", audit.map_diff(preaudit, postaudit))
def get_image_media(dbo, linktype, linkid, ignoreexcluded = False):
if not ignoreexcluded:
return db.query(dbo, "SELECT * FROM media WHERE LinkTypeID = %d AND LinkID = %d " \
"AND (LOWER(MediaName) Like '%%.jpg' OR LOWER(MediaName) Like '%%.jpeg')" % ( linktype, linkid ))
else:
return db.query(dbo, "SELECT * FROM media WHERE (ExcludeFromPublish = 0 OR ExcludeFromPublish Is Null) " \
"AND LinkTypeID = %d AND LinkID = %d AND (LOWER(MediaName) Like '%%.jpg' OR LOWER(MediaName) Like '%%.jpeg')" % ( linktype, linkid ))
def get_waitinglist_ranks(dbo):
"""
Returns a dictionary of waiting list IDs with their current ranks.
"""
byspecies = configuration.waiting_list_rank_by_species(dbo)
if not byspecies:
rows = db.query(dbo, "SELECT a.ID, a.SpeciesID FROM animalwaitinglist a " \
"INNER JOIN owner o ON a.OwnerID = o.ID " \
"WHERE a.DateRemovedFromList Is Null " \
"ORDER BY a.Urgency, a.DatePutOnList")
else:
rows = db.query(dbo, "SELECT a.ID, a.SpeciesID FROM animalwaitinglist a " \
"INNER JOIN owner o ON a.OwnerID = o.ID " \
"WHERE a.DateRemovedFromList Is Null " \
"ORDER BY a.SpeciesID, a.Urgency, a.DatePutOnList")
ranks = {}
lastspecies = 0
rank = 1
for r in rows:
if byspecies:
if not lastspecies == r["SPECIESID"]:
lastspecies = r["SPECIESID"]
rank = 1
ranks[r["ID"]] = rank
rank += 1
return ranks
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_media(dbo, username, mid):
"""
Deletes a media record from the system
"""
mr = db.query(dbo, "SELECT * FROM media WHERE ID=%d" % int(mid))
if len(mr) == 0: return
mr = mr[0]
mn = mr["MEDIANAME"]
audit.delete(dbo, username, "media", str(mr))
dbfs.delete(dbo, mn)
db.execute(dbo, "DELETE FROM media WHERE ID = %d" % int(mid))
# Was it the web or doc preferred? If so, make the first image for the link
# the web or doc preferred instead
if mr["WEBSITEPHOTO"] == 1:
ml = db.query(dbo, "SELECT * FROM media WHERE LinkID=%d AND LinkTypeID=%d " \
"AND (LOWER(MediaName) LIKE '%%.jpg' OR LOWER(MediaName) LIKE '%%.jpeg') " \
"ORDER BY ID" % ( mr["LINKID"], mr["LINKTYPEID"] ))
if len(ml) > 0:
db.execute(dbo, "UPDATE media SET WebsitePhoto = 1 WHERE ID = %d" % ml[0]["ID"])
if mr["DOCPHOTO"] == 1:
ml = db.query(dbo, "SELECT * FROM media WHERE LinkID=%d AND LinkTypeID=%d " \
"AND (LOWER(MediaName) LIKE '%%.jpg' OR LOWER(MediaName) LIKE '%%.jpeg') " \
"ORDER BY ID" % ( mr["LINKID"], mr["LINKTYPEID"] ))
if len(ml) > 0:
db.execute(dbo, "UPDATE media SET DocPhoto = 1 WHERE ID = %d" % ml[0]["ID"])
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 forum(self, forum_id):
forum_id = forum_id or 1
threads = yield db.query("""
with recent_threads AS (
select posts.thread_id, max(posts.dt) as last_post from posts, ops
where posts.thread_id = ops.id
and ops.forum_id = %(forum_id)s
group by posts.thread_id
order by last_post desc
)
select ops.*, recent_threads.last_post, files.url as avatar_url from ops, recent_threads, files
where files.id = ops.avatar_id
and ops.id = recent_threads.thread_id
""", {'forum_id':forum_id})
page = yield db.query("""
select files.id, files.url from files, users
where users.id = %(forum_id)s
and files.set_id = users.page""", {'forum_id': forum_id})
user = yield self.user()
defer.returnValue({
'user':user,
'forum':forum_id,
'page' : page,
'threads': [
{
'id': thread['id'],
'content': thread['content'],
'dt': thread['last_post'],
'user': {
'id':thread['user_id'],
'avatar':thread['avatar_url']
}
} for thread in threads]})
def top_cost():
with db.cursor() as c:
last_kill = db.get(c, 'SELECT MAX(kill_id) AS kill_id FROM kills')
kills = db.query(c, '''
SELECT kills.kill_id, cost, solar_system_id, kill_time,
ship_type_id, typeName AS ship_name
FROM kills
JOIN kill_costs ON kill_costs.kill_id = kills.kill_id
JOIN kill_characters ON kill_characters.kill_id = kills.kill_id
JOIN eve.invTypes ON typeID = ship_type_id
WHERE victim = 1 AND kills.kill_id > ?
ORDER BY cost DESC
LIMIT 25
''', last_kill['kill_id'] - 2500)
# joining eve.mapSolarSystems on the initial query causes filesort on large dbs for some reason
# do a manual join
system_ids = set(map(operator.itemgetter('solar_system_id'), kills))
system_rows = db.query(c, '''
SELECT solarSystemID as solar_system_id, solarSystemName AS system_name,
security, class AS wh_class
FROM eve.mapSolarSystems
LEFT JOIN wh_systems ON solarSystemID = wh_systems.id
WHERE solarSystemID IN ({})
'''.format(','.join(map(str, system_ids))))
systems = {}
for system in system_rows:
systems[system['solar_system_id']] = system
for kill in kills:
kill.update(systems[kill['solar_system_id']])
del kill['solar_system_id']
kill['security_status'] = _security_status(kill['security'], kill['wh_class'])
kill['kill_time'] = _format_kill_time(kill['kill_time'])
return kills
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 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 updateQ(self,db,strid):
# get the data, update the status from 'U' - unsent to 'Q' - inside queue
# so that this data do not come again in the queue
# later on, the 'Q' status will be updated to 'S' - sent
updateQ = 'update smsserver_out set status = \'Q\',transfer_success=\'pending\' where id in '+ strid
self.logger.info('Update query ' + updateQ )
if len(strid) > 2:
db.query(updateQ)
def test_query(self):
res = db.query('select name from users limit 5', key='test')
self.assertEqual(len(res), 5)
res = db.query('select name from users limit %s', (100,), many=20, key='test')
rows = []
for r in res:
rows.append(r)
self.assertTrue(10, len(rows))
def admin_get_users(db):
session = bottle.request.environ.get('beaker.session')
user_id = session.get('user_id')
try:
user = db.query(User).filter_by(id=user_id, admin=True).one()
users = db.query(User).all()
return json.dumps([usr.to_dict() for usr in users], ensure_ascii=False);
except NoResultFound:
return {}
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 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 update_diarytaskhead_from_form(dbo, username, data):
"""
Updates a diary task header from form data
"""
tid = utils.df_ki(data, "diarytaskid")
sql = db.make_update_sql("diarytaskhead", "ID=%d" % tid, (
( "Name", utils.df_t(data, "name")),
( "RecordType", utils.df_s(data, "type"))
))
preaudit = db.query(dbo, "SELECT * FROM diarytaskhead WHERE ID=%d" % tid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM diarytaskhead WHERE ID=%d" % tid)
audit.edit(dbo, username, "diarytaskhead", audit.map_diff(preaudit, postaudit))
def update_role_from_form(dbo, username, post):
"""
Updates a role record from posted form data
"""
roleid = post.integer("roleid")
sql = db.make_update_sql("role", "ID=%d" % roleid, (
( "Rolename", post.db_string("rolename")),
( "SecurityMap", post.db_string("securitymap"))
))
preaudit = db.query(dbo, "SELECT * FROM role WHERE ID = %d" % roleid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM role WHERE ID = %d" % roleid)
audit.edit(dbo, username, "role", roleid, audit.map_diff(preaudit, postaudit, [ "ROLENAME", ]))
def update_diarytaskhead_from_form(dbo, username, post):
"""
Updates a diary task header from form data
"""
tid = post.integer("diarytaskid")
sql = db.make_update_sql("diarytaskhead", "ID=%d" % tid, (
( "Name", post.db_string("name")),
( "RecordType", post.db_integer("type"))
))
preaudit = db.query(dbo, "SELECT * FROM diarytaskhead WHERE ID=%d" % tid)
db.execute(dbo, sql)
postaudit = db.query(dbo, "SELECT * FROM diarytaskhead WHERE ID=%d" % tid)
audit.edit(dbo, username, "diarytaskhead", tid, audit.map_diff(preaudit, postaudit))
def count(self):
return db.query('SELECT COUNT(*) FROM ' + self.table)[0][0]
def registratura():
print('Вы зашли как работник регистратуры\n Выберите действие:')
print('1. Адрес, дата заболевания, диагноз данного больного')
print('2. ФИО лечащего врача больного')
print('3. Номер кабинета, дни и часы приема данного врача')
print('4. Больные, находяшиеся на лечении у данного врача')
print('5. Симптомы заболевания и лечение')
print('6. Выдать справку')
print('7. Отчет')
choose = input()
if (choose == '1'):
print("Введите ФИО:")
fio = input()
cur, count = db.query(
"SELECT "
"diagnosis_of_the_patient.DIAGNOSIS, patients.STREET, patients.HOME, diagnosis_of_the_patient.DATE, diagnosis_of_the_patient.patients_FIO "
"from diagnosis_of_the_patient INNER "
"JOIN "
"patients "
"on "
"diagnosis_of_the_patient.patients_FIO = patients.FIO "
"INNER "
"JOIN "
"adress_area "
"ON "
"patients.STREET = adress_area.STREET "
"INNER "
"JOIN "
"doctors "
"ON "
"doctors.AREA = adress_area.AREA WHERE patients_FIO='" + fio + "'")
if (count != 0):
rows = cur.fetchall()
for r in rows:
print(r[0], r[3], r[4], r[1], r[2])
if (choose == '2'):
print("Введите ФИО:")
fio = input()
cur, count = db.query(
"SELECT diagnosis_of_the_patient.DIAGNOSIS, doctors.DOCTOR_FIO, diagnosis_of_the_patient.DATE, diagnosis_of_the_patient.patients_FIO from diagnosis_of_the_patient INNER JOIN patients on diagnosis_of_the_patient.patients_FIO = patients.FIO INNER JOIN adress_area ON patients.STREET = adress_area.STREET INNER JOIN doctors ON doctors.AREA = adress_area.AREA WHERE patients_FIO = '"
+ fio + "'")
print("Врач")
if (count != 0):
rows = cur.fetchall()
for r in rows:
print(r[1])
else:
print("Не найдено")
if (choose == '3'):
print("Введите ФИО:")
fio = input()
cur, count = db.query("SELECT * FROM DOCTORS WHERE DOCTOR_FIO='" +
fio + "'")
if (count != 0):
rows = cur.fetchall()
for r in rows:
print(r[1], r[2], r[3])
else:
print("Не найдено")
if (choose == '4'):
print("Введите ФИО:")
fio = input()
cur, count = db.query(
" SELECT * "
" from patients INNER "
" join "
" adress_area "
"on "
" adress_area.STREET = patients.STREET "
" where "
" adress_area.AREA = (SELECT doctors.AREA from doctors WHERE doctors.DOCTOR_FIO='"
+ fio + "') ")
print("Пациенты")
if (count != 0):
rows = cur.fetchall()
for r in rows:
print(r[0], r[1], r[2])
else:
print("Не найдено")
if (choose == '5'):
print("Введите заболевание:")
bol = input()
cur, count = db.query(
"SELECT * from description_disease where diagnosis = '" + bol +
"'")
if (count != 0):
rows = cur.fetchall()
print('Симптомы:')
for r in rows:
print(r[1])
else:
print("Не найдено")
cur, count = db.query("SELECT * from medicaments where diagnosis = '" +
bol + "'")
if (count != 0):
rows = cur.fetchall()
print('Лечение:')
for r in rows:
print(r[1])
else:
print("Не найдено")
if (choose == '6'):
print('Ведите ФИО пациента:')
fio = input()
cur, count = db.query(
"SELECT diagnosis_of_the_patient.DIAGNOSIS, doctors.DOCTOR_FIO, diagnosis_of_the_patient.DATE, diagnosis_of_the_patient.patients_FIO from diagnosis_of_the_patient INNER JOIN patients on diagnosis_of_the_patient.patients_FIO = patients.FIO INNER JOIN adress_area ON patients.STREET = adress_area.STREET INNER JOIN doctors ON doctors.AREA = adress_area.AREA WHERE patients_FIO = '"
+ fio + "'")
if (count != 0):
rows = cur.fetchall()
print(rows)
for r in rows:
my_file = open("справка.txt", "w")
my_file.write("Пациент:\t" + r[3])
my_file.write("\nДиагноз:\t" + r[0])
my_file.write("\nВрач:\t" + r[1] + "\t Дата \t" +
r[2].strftime('%m/%d/%Y'))
my_file.close()
if (choose == '7'):
cur, count = db.query("SELECT COUNT(patients.FIO) FROM patients")
if (count != 0):
rows = cur.fetchall()
print("Количество пациентов:")
for r in rows:
print(r[0])
print("-----------------")
cur, count = db.query("SELECT "
"count(FIO), doctors.DOCTOR_FIO "
"from patients INNER "
"join "
"adress_area "
"on "
"patients.STREET = adress_area.STREET "
"INNER "
"JOIN "
"doctors "
"on "
"doctors.AREA = adress_area.AREA "
"GROUP "
"by "
"doctors.DOCTOR_FIO ")
print("Количество пациентов у врачей:")
if (count != 0):
rows = cur.fetchall()
print("Количество пациентов:")
for r in rows:
print(r[1], " - ", r[0])
print("-----------------")
cur, count = db.query(
"SELECT "
"COUNT(diagnosis_of_the_patient.FIO), diagnosis_of_the_patient.FIO "
"FROM "
"diagnosis_of_the_patient "
"GROUP "
"by "
"diagnosis_of_the_patient.FIO ")
print("Количество заболеваний:")
if (count != 0):
rows = cur.fetchall()
for r in rows:
print(r[1], " - ", r[0])
print("-----------------")
cur, count = db.query("SELECT * FROM DOCTORS ")
print("Расписание врачей:")
if (count != 0):
rows = cur.fetchall()
for r in rows:
print(r[1], r[2], r[3])
print("-----------------")
print('---------------------------------------------------')
x.active = registratura()
def admin():
print('Вы зашли как администратор \n Выберите действие:')
print('1. Добавить пациента')
print('2. Удалить пациента')
print('3. Добавить Врача')
print('4. Уволить Врача')
print('5. Изменить диагноз')
print('6. Создание нового пользователя')
print('7. Удалить пользователя')
choose = input()
if (choose == '1'):
print('Ведите ФИО пациента:')
fio = input()
print('Ведите улицу проживания пациента:')
street = input()
print('Ведите номер дома пациента:')
num = input()
db.query("INSERT INTO `patients` VALUES ('" + fio + "','" + street +
"','" + num + "')")
print('Пациент успешно добавлен')
x.active = admin()
if (choose == '2'):
print('Ведите ФИО пациента:')
fio = input()
try:
if (db.query("DELETE FROM `patients` WHERE FIO='" + fio + "'")[1]
!= 0):
print('Успешно')
else:
print('Ничего не найдено')
except Exception:
print('Не выполнено')
x.active = admin()
if (choose == '4'):
print('Ведите ФИО врача:')
fio = input()
try:
if (db.query("DELETE FROM DOCTORS WHERE DOCTOR_FIO='" + fio +
"'")[1] != 0):
print('Успешно')
else:
print('Ничего не найдено')
except Exception:
print('Не выполнено')
x.active = admin()
if (choose == '5'):
print('Ведите ФИО пациента:')
fio = input()
if (db.query("SELECT * FROM PATIENTS WHERE FIO = '" + fio + "'")[1] !=
0):
print('Ведите Диагноз:')
dia = input()
try:
if (db.query(
"SELECT * FROM `diagnosis_of_the_patient` WHERE patients_FIO='"
+ fio + "'")[1] != 0):
db.query("UPDATE `diagnosis_of_the_patient` SET FIO='" +
dia + "' WHERE patients_FIO='" + fio + "'")
print('Обновлено')
else:
db.query(
"INSERT INTO `diagnosis_of_the_patient` (`FIO`, `DIAGNOSIS`, `patients_FIO`) VALUES('"
+ dia + "','" + dia + "','" + fio + "') ")
print('Добавлено')
except Exception:
print('Не выполнено')
x.active = admin()
else:
print('Пациент не найден')
if (choose == '6'):
print("Введите логин")
login = input()
print("Введите пароль")
password = input()
print(
"Назначьте роль нового пользователя. Введите слово-'админ' или слово-'пользователь' "
)
role = input()
if (role == 'админ'):
role = 'admin'
elif (role == 'пользователь'):
role = 'regis'
cur, count = db.query("SELECT User FROM mysql.user where User='******'")
if (count != 0):
print("Такой пользователь уже существует")
else:
if (role == 'admin'):
db.query("CREATE user '" + login + "'@'%' identified by '" +
password + "'")
db.query(
"GRANT SELECT, INSERT, DELETE, UPDATE, CREATE user on *.* TO '"
+ login + "'@'%' ")
db.query("FLUSH PRIVILEGES;")
db.query("INSERT INTO `accounts` VALUES ('" + login + "','" +
password + "','" + role + "')")
elif (role == 'regis'):
db.query("CREATE USER '" + login + "'@'%' identified by '" +
password + "'")
db.query("GRANT SELECT on mydb.* TO '" + login + "'@'%' ")
db.query("FLUSH PRIVILEGES;")
db.query("INSERT INTO `accounts` VALUES ('" + login + "','" +
password + "','" + role + "')")
cur, count = db.query("SELECT User FROM mysql.user where User='******'")
if (count != 0):
if (role == 'admin'):
print('Админ успешно добавлен')
elif (role == 'regis'):
print('Пользователь успешно добавлен')
elif (count == 0):
print('Не удалось добавить нового пользователя')
print('---------------------------------------------------')
x.active = admin()
if (choose == '7'):
print("Введите логин пользователя, которого хотите удалить")
login = input()
cur, count = db.query("SELECT User FROM mysql.user where User='******'")
if (count == 0):
print("Такого пользователя не существует")
elif (count != 0):
db.query("DROP user '" + login + "'@'%' ")
db.query("FLUSH PRIVILEGES;")
db.query("DELETE FROM accounts where login='******'")
cur, count = db.query("SELECT User FROM mysql.user where User='******'")
if (count == 0):
print('Пользователь удален')
elif (count != 0):
print('Не удалось удалить пользователя')
print('---------------------------------------------------')
x.active = admin()
def get_ynun(dbo):
return db.query(dbo, "SELECT * FROM lksynun ORDER BY Name")
def post(self):
parser = reqparse.RequestParser()
parser.add_argument('roll_no',
type=int,
required=True,
help='roll_no cannot be left blank')
parser.add_argument('dept_name',
type=str,
required=True,
help='dept_name cannot be left blank')
parser.add_argument('first_name',
type=str,
required=True,
help='first_name cannot be left blank')
parser.add_argument('last_name',
type=str,
required=True,
help='last_name cannot be left blank')
parser.add_argument('gpa',
type=str,
required=True,
help='gpa cannot be left blank')
parser.add_argument('password',
type=str,
required=True,
help='password cannot be left blank')
parser.add_argument('dateregst',
type=str,
required=True,
help='dateregst cannot be left blank')
parser.add_argument('stemail',
type=str,
required=True,
help='stemail cannot be left blank')
parser.add_argument('stphno',
type=int,
required=True,
help='stphno cannot be left blank')
parser.add_argument('currentsem',
type=int,
required=True,
help='currentsem cannot be left blank')
data = parser.parse_args()
try:
x = query(
f"""SELECT * FROM stdnt.deptdetails where dept_name='{data['dept_name']}';""",
return_json=False)
if (len(x) == 0):
query(
f"""insert into stdnt.deptdetails values('{data['dept_name']}');""",
return_json=False)
except:
return {"message": "There was an error cannot connect"}, 500
try:
x = query(
f"""select * from stdnt.stdetails where roll_no={data['roll_no']};""",
return_json=False)
if len(x) > 0:
return {
"message": "student details with that info already exists"
}, 400
except:
return {"message": "There was an error cannot connect"}, 500
try:
query(f"""insert into stdnt.stdetails values({data['roll_no']},
'{data['dept_name']}',
'{data['first_name']}',
'{data['last_name']}',
'{data['gpa']}',
'{data['password']}',
'{data['dateregst']}',
'{data['stemail']}',
{data['stphno']},
{data['currentsem']});"""
)
except:
return {"message": "there was an error in signup insert "}, 500
return {"message": "success signup."}, 201
def clean_up_NPO_DB():
db.query("DELETE FROM NPO where id > 0");
def get(self):
with db.conn.cursor() as c:
log_rows = db.query(c, 'SELECT time, username, log_message FROM logs ORDER BY time DESC LIMIT 50')
log = map(operator.attrgetter('__dict__'), log_rows)
self.render('log.html', log=log)
def prepare_ETY_DB():
# GOOD:
clean_up_ETY_DB()
# For conversion 1
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("45", "Donald Trump", "PERSON", "alex", "1:31 PM - 22 Apr 2017", "45 threatens to shut down government over funding for the wall that he promised Mexico would pay for.", 1, "False", ""));
# For conversion 2
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("First of his name", "The King of the Seven Kingdoms", "PERSON", "Cain Snow", "2:48 PM - 22 Feb 2017", "First of his name. King of the Andals and the First Men, Lord of the Seven Kingdoms, and Protector of the Realm. Long may he reign.", 2, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("King of the Andals and the First Men", "The King of the Seven Kingdoms", "PERSON", "Cain Snow", "2:48 PM - 22 Feb 2017", "First of his name. King of the Andals and the First Men, Lord of the Seven Kingdoms, and Protector of the Realm. Long may he reign.", 2, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Lord of the Seven Kingdoms", "The King of the Seven Kingdoms", "PERSON", "Cain Snow", "2:48 PM - 22 Feb 2017", "First of his name. King of the Andals and the First Men, Lord of the Seven Kingdoms, and Protector of the Realm. Long may he reign.", 2, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Protector of the Realm", "The King of the Seven Kingdoms", "PERSON", "Cain Snow", "2:48 PM - 22 Feb 2017", "First of his name. King of the Andals and the First Men, Lord of the Seven Kingdoms, and Protector of the Realm. Long may he reign.", 2, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("The Unburnt Queen of the Andals", "Daenerys Targaryen", "PERSON", "mat morgan", "4:51 PM - 22 Feb 2017", "The Unburnt Queen of the Andals, Queen of Meereen, Khaleesi of the Great Grass Sea, Breaker of Chains, Mother of Dragons.", 2, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Queen of Meereen", "Daenerys Targaryen", "PERSON", "mat morgan", "4:51 PM - 22 Feb 2017", "The Unburnt Queen of the Andals, Queen of Meereen, Khaleesi of the Great Grass Sea, Breaker of Chains, Mother of Dragons.", 2, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Khaleesi of the Great Grass Sea", "Daenerys Targaryen", "PERSON", "mat morgan", "4:51 PM - 22 Feb 2017", "The Unburnt Queen of the Andals, Queen of Meereen, Khaleesi of the Great Grass Sea, Breaker of Chains, Mother of Dragons.", 2, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Breaker of Chains", "Daenerys Targaryen", "PERSON", "mat morgan", "4:51 PM - 22 Feb 2017", "The Unburnt Queen of the Andals, Queen of Meereen, Khaleesi of the Great Grass Sea, Breaker of Chains, Mother of Dragons.", 2, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Mother of Dragons", "Daenerys Targaryen", "PERSON", "mat morgan", "4:51 PM - 22 Feb 2017", "The Unburnt Queen of the Andals, Queen of Meereen, Khaleesi of the Great Grass Sea, Breaker of Chains, Mother of Dragons.", 2, "False", ""));
# For conversion 3
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Hoosiers", "Indiana", "LOCATION", "Not Jerry Tipton", "5:19 PM - 21 Apr 2017", "Indiana fans celebrating the Hoosiers' most recent national championship.", 3, "False", ""));
# For conversion 4
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Oscar", "Academy Awards", "ORGANIZATION", "yung da bejar", "10:50 PM - 27 Mar 2017", "I liked it overall but it felt very \"Oscar nominated movie\". will checkout Manchester soon ", 4, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Manchester", "Manchester by the Sea", "OTHERS", "yung da bejar", "10:50 PM - 27 Mar 2017", "I liked it overall but it felt very \"Oscar nominated movie\". will checkout Manchester soon ", 4, "False", ""));
# For conversion 5
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("United", "United Airlines", "ORGANIZATION", "Hazha", "3:06 PM - 19 Apr 2017", "United #Overbooked hazha.com/yay/xGg", 5, "False", ""));
# For conversion 6
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Boilermakers", "Purdue University", "ORGANIZATION", "Purdue Basketball", "7:01 PM - 8 Apr 2017", "SEE YA!! Logan Poisall goes big fly to left field. #Purdue's 2nd 2-run blast tonight. It's 6-0 #Boilermakers as they've jumped all over IU.", 6, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Purdue", "Purdue University", "ORGANIZATION", "Purdue Basketball", "7:01 PM - 8 Apr 2017", "SEE YA!! Logan Poisall goes big fly to left field. #Purdue's 2nd 2-run blast tonight. It's 6-0 #Boilermakers as they've jumped all over IU.", 6, "False", ""));
# For conversion 7
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
("Great Successor", "Kim Jong-un", "PERSON", "Kevin-Economy&Beyond", "4:23 AM - 18 Aug 2016", "This will not amuse The Great Successor. BBC News -China to restrict North Korea's Air Koryo after emergency landing", 7, "False", ""));
# Additional information
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)", ("Orange Julius", "Donald Trump", "PERSON", "alex", "201704010102", "is Orange Julius a good man?", 0, "False", ""));
db.query("INSERT INTO ETY (name, source, class, user, tweet_time, context, convers_id, isAuto, comment) values (?, ?, ?, ?, ?, ?, ?, ?, ?)", ("BoilerUp", "Purdue University", "ORGANIZATION", "alex", "201704010103",
"We come from Purdue University! We are Boiler Makers!", 0, "False", ""));
def clean_up_TWEETS_DB():
db.query("DELETE FROM TWEETS where id > 0");
def get_animalcontrol_find_advanced(dbo, criteria, limit=0):
"""
Returns rows for advanced animal control searches.
criteria: A dictionary of criteria
number - string partial pattern
callername - string partial pattern
victimname - string partial pattern
callerphone - string partial pattern
incidenttype - -1 for all or ID
dispatchedaco - string partial pattern
completedtype - -1 for all or ID
citationtype - -1 for all or ID
address - string partial pattern
postcode - string partial pattern
description - string partial pattern
agegroup - agegroup text to match
sex - -1 for all or ID
species - -1 for all or ID
filter - unpaid, incomplete, undispatched, requirefollowup
incidentfrom - incident date from in current display locale format
incidentto - incident date to in current display locale format
dispatchfrom - dispatch date from in current display locale format
dispatchto - dispatch date from in current display locale format
respondedfrom - responded date from in current display locale format
respondedto - responded date to in current display locale format
followupfrom - follow up date from in current display locale format
followupto - follow up date to in current display locale format
completedfrom - completed date from in current display locale format
completedto - completed date to in current display locale format
"""
c = []
l = dbo.locale
post = utils.PostedData(criteria, l)
def hk(cfield):
return post[cfield] != ""
def crit(cfield):
return post[cfield]
def addid(cfield, field):
if hk(cfield) and int(crit(cfield)) != -1:
c.append("%s = %s" % (field, crit(cfield)))
def addstr(cfield, field):
if hk(cfield) and crit(cfield) != "":
c.append("LOWER(%s) LIKE '%%%s%%'" %
(field, crit(cfield).lower().replace("'", "`")))
def adddate(cfieldfrom, cfieldto, field):
if hk(cfieldfrom) and hk(cfieldto):
c.append("%s >= %s AND %s <= %s" %
(field, db.dd(display2python(l, crit(cfieldfrom))), field,
db.dd(display2python(l, crit(cfieldto)))))
def addcomp(cfield, value, condition):
if hk(cfield) and crit(cfield) == value:
c.append(condition)
c.append("ac.ID > 0")
if crit("number") != "":
c.append("ac.ID = " + str(utils.cint(crit("number"))))
addstr("callername", "co.OwnerName")
addstr("victimname", "vo.OwnerName")
addstr("callerphone", "co.HomeTelephone")
addid("incidenttype", "ac.IncidentTypeID")
if (crit("dispatchedaco") != "-1"):
addstr("dispatchedaco", "ac.DispatchedACO")
adddate("incidentfrom", "incidentto", "ac.IncidentDateTime")
adddate("dispatchfrom", "dispatchto", "ac.DispatchDateTime")
adddate("respondedfrom", "respondedto", "ac.RespondedDateTime")
adddate("followupfrom", "followupto", "ac.FollwupDateTime")
adddate("completedfrom", "completedto", "ac.CompletedDate")
addid("completedtype", "ac.IncidentCompletedID")
addid("citationtype", "ac.CitationTypeID")
addstr("address", "ac.DispatchAddress")
addstr("postcode", "ac.DispatchPostcode")
addstr("description", "ac.AnimalDescription")
if (crit("agegroup") != "-1"): addstr("agegroup", "ac.AgeGroup")
addid("sex", "ac.Sex")
addid("species", "ac.SpeciesID")
addcomp("filter", "incomplete", "ac.CompletedDate Is Null")
addcomp(
"filter", "undispatched",
"ac.CompletedDate Is Null AND ac.CallDateTime Is Not Null AND ac.DispatchDateTime Is Null"
)
addcomp("filter", "requirefollowup", "ac.CompletedDate Is Null AND (" \
"(ac.FollowupDateTime Is Not Null AND ac.FollowupDateTime < %(now)s) OR " \
"(ac.FollowupDateTime2 Is Not Null AND ac.FollowupDateTime2 < %(now)s) OR " \
"(ac.FollowupDateTime3 Is Not Null AND ac.FollowupDateTime3 < %(now)s) " \
")" % { "now": db.dd(now(dbo.timezone))} )
where = ""
if len(c) > 0:
where = " WHERE " + " AND ".join(c)
sql = get_animalcontrol_query(dbo) + where + " ORDER BY ac.ID"
if limit > 0: sql += " LIMIT " + str(limit)
return db.query(dbo, sql)
def test_excute(self):
res = db.execute('insert into users values(%s, %s)', [(10L, 'thomas'),
(11L, 'animer')],
key='test')
res = db.query('SELECT count(*) FROM users WHERE uid>=10', key='test')
self.assertEqual(2, res[0][0])
def onUserMsg(msg):
content_type, chat_type, chat_id = telepot.glance(msg)
r = ''
if content_type == 'text':
if msg['text'] == '/start':
r = newUser(msg)
elif not DB.query(DB.CHECK_USER, (chat_id, )):
r = "`Start the bot using `/start"
elif msg['text'][0] == '/':
##########################
# JOIN #
if msg['text'] == "/join":
if DB.check_join(chat_id):
r = "`You are already, in a room.\n`/leave` to exit`"
else:
if not DB.join_room(chat_id):
r = "`There are not free room now.\nTry later please!`"
else:
partner_id = DB.get_partner_by_id(chat_id)
if partner_id:
try:
bot.sendMessage(
partner_id[0][0],
"`You have a partner! Say Hi!`",
parse_mode="Markdown")
except Exception as e:
log("Exception")
all_exception_handler()
r = "`You are not alone in this room! Say Hi!`"
else:
r = "`You are now in a waiting room\nWait for a user`"
# LEAVE #
elif msg['text'] == "/leave":
if DB.check_join(chat_id):
partner_id = DB.get_partner_by_id(chat_id)
DB.leave_room(chat_id)
if partner_id:
try:
bot.sendMessage(
partner_id[0][0],
"`Your partner has left this room. Change room /skip or wait for a new user`",
parse_mode="Markdown")
except Exception as e:
log("Exception")
all_exception_handler()
r = "`You left the room, click `/join` to join another room`"
else:
r = "`You are not in a room, click `/join` to join one`"
# SKIP #
elif msg['text'] == "/skip":
if DB.check_join(chat_id):
partner_id = DB.get_partner_by_id(chat_id)
#Leave
DB.leave_room(chat_id)
#Notice the partner
if partner_id:
try:
bot.sendMessage(
partner_id[0][0],
"`Your partner has left this room. Change room /skip or wait for a new user`",
parse_mode="Markdown")
except Exception as e:
log("Exception")
all_exception_handler()
#Join
if not DB.join_room(chat_id):
r = "`There are not free room now.\nTry later please!`"
else:
partner_id = DB.get_partner_by_id(chat_id)
if partner_id:
try:
bot.sendMessage(partner_id[0][0],
"`You have a partner! Say Hi!`",
parse_mode="Markdown")
except Exception as e:
log("Exception")
all_exception_handler()
r = "You are not alone in this room! Say Hi!"
else:
r = "`You have changed the waiting room\nWait for a user`"
#########################
elif msg['text'][:8] == "/segnala":
message = msg['text'].split(' ')
if len(message) > 1:
bot.sendMessage(
conf.ID_ADMIN,
"Message from " + str(chat_id) + " " + msg['text'][9:])
else:
r = "`Usage /segnala messaggio`"
else:
r = "`Command unknown`"
else:
#Search for partner id
if not DB.check_join(chat_id):
r = "`You are not in a room.. `/join"
else:
partner_id = DB.get_partner_by_id(chat_id)
if not partner_id:
r = "`You are alone here..wait or `/skip `this room`"
else:
try:
bot.sendMessage(partner_id[0][0], msg['text'])
except Exception as e:
log("Exception")
all_exception_handler()
elif content_type == 'photo':
if not DB.query(DB.CHECK_USER, (chat_id, )):
r = "`Start the bot using `/start"
else:
partner_id = DB.get_partner_by_id(chat_id)
if not partner_id:
r = "`Your partner has left this room`"
else:
try:
cap = ''
if 'caption' in msg:
cap = msg['caption']
bot.sendPhoto(partner_id[0][0],
msg['photo'][-1]['file_id'],
caption=cap)
except Exception as e:
log("Exception")
all_exception_handler()
return r
def get_r2_data():
sql = "select content " \
"from hotsearch order by id desc limit 20"
res = db.query(sql)
return res
def get_l1_data():
sql = "select ds,confirm,suspect,heal,dead,importedCase from history"
res = db.query(sql)
return res
def test_query(self):
self.assertEqual(1, db.query('SELECT 1')[0][0])
self.assertEqual(0, len(db.query('SELECT * FROM users')))
def q(n):
for i in range(10):
res = db.query('select count(*) from users')
self.assertEqual(0, res[0][0])
def prepare_NPO_DB():
# GOOD:
clean_up_NPO_DB()
# For conversion 1
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("Donald Trump", "PERSON", "https://en.wikipedia.org/wiki/Donald_Trump", "45"));
# For conversion 2
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("The King of the Seven Kingdoms", "PERSON", "https://en.wikipedia.org/wiki/Daenerys_Targaryen",
"First of his name, King of the Andals and the First Men, Lord of the Seven Kingdoms, Protector of the Realm, "));
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("Daenerys Targaryen", "PERSON", "https://en.wikipedia.org/wiki/Daenerys_Targaryen",
"The Unburnt Queen of the Andals, Queen of Meereen, Khaleesi of the Great Grass Sea, Breaker of Chains, Mother of Dragons, "));
# For conversion 3
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("Indiana", "LOCATION", "https://en.wikipedia.org/wiki/Indiana", "Hoosiers"));
# For conversion 4
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("Academy Awards", "ORGANIZATION", "https://en.wikipedia.org/wiki/Oscar", "Oscar"));
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("Manchester by the Sea", "OTHERS", "https://en.wikipedia.org/wiki/Manchester_by_the_Sea_(film)", "Manchester"));
# For conversion 5
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("United Airlines", "ORGANIZATION", "https://en.wikipedia.org/wiki/Oscar", "United"));
# For conversion 6
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("Purdue University", "ORGANIZATION", "https://en.wikipedia.org/wiki/Purdue_University", "Boilermakers, Purdue"));
# For conversion 7
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("Kim Jong-un", "PERSON", "https://en.wikipedia.org/wiki/Purdue_University", "Great Successor"));
# Additional information
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("Mitch Daniels", "PERSON", "https://en.wikipedia.org/wiki/Mitch_Daniels", ""));
db.query("INSERT INTO NPO (name, class, description, dest) values (?, ?, ?, ?)", ("Star Craft", "OTHERS", "https://en.wikipedia.org/wiki/StarCraft", ""));
def zf_list():
sql = 'select id, title, imgs, updatetime from house order by updatetime desc'
ret = db.query(sql)
return jsonify(ret)
def clean_up_ETY_DB():
db.query("DELETE FROM ETY where id > 0");
def update(self, technology):
db.query(
'update technology set name=:name, resource_id=:resource_id where technology_id=:technology_id',
technology)
return technology['technology_id']
def prepare_TWEETS_DB():
# GOOD:
clean_up_TWEETS_DB()
#Conversation 1
db.query("INSERT INTO TWEETS (user, tweet_time, content, convers_id) values (?, ?, ?, ?)",
("Nick Decaro",
"1:31 PM - 22 Apr 2017",
"45 threatens to shut down government over funding for the wall that he promised Mexico would pay for.",
1));
#Conversation 2
db.query("INSERT INTO TWEETS (user, tweet_time, content, convers_id) values (?, ?, ?, ?)",
("Cain Snow",
"2:48 PM - 22 Feb 2017",
"First of his name. King of the Andals and the First Men, Lord of the Seven Kingdoms, and Protector of the Realm. Long may he reign.",
2));
db.query("INSERT INTO TWEETS (user, tweet_time, content, convers_id) values (?, ?, ?, ?)",
("mat morgan",
"4:51 PM - 22 Feb 2017",
"The Unburnt Queen of the Andals, Queen of Meereen, Khaleesi of the Great Grass Sea, Breaker of Chains, Mother of Dragons.",
2));
#Conversation 3
db.query("INSERT INTO TWEETS (user, tweet_time, content, convers_id) values (?, ?, ?, ?)",
("Not Jerry Tipton",
"5:19 PM - 21 Apr 2017",
"Indiana fans celebrating the Hoosiers' most recent national championship.",
3));
#Conversation 4
db.query("INSERT INTO TWEETS (user, tweet_time, content, convers_id) values (?, ?, ?, ?)",
("yung da bejar",
"10:50 PM - 27 Mar 2017",
"I liked it overall but it felt very \"Oscar nominated movie\". will checkout Manchester soon ",
4));
#Conversation 5
db.query("INSERT INTO TWEETS (user, tweet_time, content, convers_id) values (?, ?, ?, ?)",
("Hazha",
"3:06 PM - 19 Apr 2017",
"United #Overbooked hazha.com/yay/xGg",
5));
#Conversation 6
db.query("INSERT INTO TWEETS (user, tweet_time, content, convers_id) values (?, ?, ?, ?)",
("Purdue Basketball",
"7:01 PM - 8 Apr 2017",
"SEE YA!! Logan Poisall goes big fly to left field. #Purdue's 2nd 2-run blast tonight. It's 6-0 #Boilermakers as they've jumped all over IU.",
6));
#Conversation 7
db.query("INSERT INTO TWEETS (user, tweet_time, content, convers_id) values (?, ?, ?, ?)",
("Kevin-Economy&Beyond",
"4:23 AM - 18 Aug 2016",
"This will not amuse The Great Successor. BBC News -China to restrict North Korea's Air Koryo after emergency landing",
7));
def insert(self, technology):
db.query(
'insert into technology (name,resource_id) values (:name,:technology_id)',
technology)
return db.lastInsertId()
'sender': sms_meta[2].split("\"")[1],
'date': sms_meta[4].split("\"")[1],
'text': sms_text
}
if __name__ == '__main__':
init(pin=config.sim_card_pin)
while (True):
# check received messages
for sms_message in get_sms_messages():
print('new message arrived')
msg = ReceivedMessage(
phone_from=sms_message['sender'],
msg_body=sms_message['text'],
)
db.session.add(msg)
db.session.commit()
delete_sms_message(sms_message["index"])
# send messages waiting to be sent
messages_to_send = (db.query(MessageToSend).filter(
MessageToSend.sent_at.is_(None)).all())
for message in messages_to_send:
send_sms_message(message.phone_to, message.msg_body)
message.sent_at = datetime.utcnow()
db.session.commit()
def post(self):
parser = reqparse.RequestParser()
parser.add_argument('roll_no',
type=int,
required=True,
help='roll_no cannot be left blank')
parser.add_argument('company_name',
type=str,
required=True,
help='company_name cannot be left blank')
parser.add_argument('dateregco',
type=str,
required=True,
help='dateregco cannot be left blank')
data = parser.parse_args()
k = query(
f"""select roll_no,gpa from stdnt.stdetails where roll_no={data['roll_no']};""",
return_json=False)
if (len(k) > 0):
y = query(
f"""select min_gpa from stdnt.posting where company_name='{data['company_name']}';""",
return_json=False)
if len(y) > 0:
if float(k[0]['gpa']) >= float(y[0]['min_gpa']):
try:
x = query(
f"""select * from stdnt.companyreg where roll_no={data['roll_no']};""",
return_json=False)
if len(x) > 0:
return {
"message":
"Student with that info already registered"
}, 400
except:
return {
"message": "There was an error cannot connect"
}, 500
try:
query(
f"""insert into stdnt.companyreg values('{data['company_name']}',
{data['roll_no']},
'{data['dateregco']}');"""
)
except:
return {
"message":
"there was an error inserting to companies"
}, 500
return {"message": "successfully registered."}, 201
else:
return {
"message":
"Your gpa is not sufficient to register for this company."
}, 400
else:
return {"message": "There was no company with that name"}, 500
else:
return {
"message":
"Your Details are not supported first sign up as student."
}, 400
def getAdminByAid(cls, aid):
result = query(f"""SELECT aid,pword FROM Admin WHERE aid='{aid}'""",
return_json=False)
if len(result) > 0: return Admin(result[0]['aid'], result[0]['pword'])
return None
def get(self):
try:
return query(f"""select * from stdnt.stdetails;""",
return_json=False)
except:
return {"message": "Error to connect"}, 500
import db
from BodyModel import BodyModel
bm = BodyModel()
bm.initialize_planets()
q = db.query(
'select b.body_id,b.parent_id,b.name,p.name as parent_name from body as b left join body as p on p.body_id=b.parent_id '
)
for r in q:
print r
def get(self):
try:
return query("""Select event_id, event_name, event_details, head, certificate, allow, event_date from
eventregister where allow=1""")
except:
return {"message": "couldn't show all events"}
def post(self):
parser = reqparse.RequestParser()
parser.add_argument('roll_no',
type=int,
required=True,
help='roll_no cannot be left blank')
parser.add_argument('dept_name', type=str)
parser.add_argument('first_name', type=str)
parser.add_argument('last_name', type=str)
parser.add_argument('gpa', type=str)
parser.add_argument('password', type=str)
parser.add_argument('dateregst', type=str)
parser.add_argument('stemail', type=str)
parser.add_argument('stphno', type=int)
parser.add_argument('currentsem', type=int)
data = parser.parse_args()
try:
if data['dept_name'] == None and data[
'first_name'] == None and data[
'last_name'] == None and data['gpa'] == None and data[
'password'] == None and data[
'dateregst'] == None and data[
'stemail'] == None and data[
'stphno'] == None and data[
'currentsem'] == None:
return {"message": "No details for updation."}, 400
if data['dept_name'] != None:
x = query(
f"""SELECT * FROM stdnt.deptdetails where dept_name='{data['dept_name']}';""",
return_json=False)
if (len(x) == 0):
query(
f"""insert into stdnt.deptdetails values('{data['dept_name']}');""",
return_json=False)
query(
f"""update stdnt.stdetails set dept_name='{data['dept_name']}' where roll_no={data['roll_no']};""",
return_json=False)
if data['first_name'] != None:
query(
f"""update stdnt.stdetails set first_name='{data['first_name']}' where roll_no={data['roll_no']};""",
return_json=False)
if data['last_name'] != None:
query(
f"""update stdnt.stdetails set last_name='{data['last_name']}' where roll_no={data['roll_no']};""",
return_json=False)
if data['gpa'] != None:
query(
f"""update stdnt.stdetails set gpa='{data['gpa']}' where roll_no={data['roll_no']};""",
return_json=False)
if data['password'] != None:
query(
f"""update stdnt.stdetails set password='******'password']}' where roll_no={data['roll_no']};""",
return_json=False)
if data['dateregst'] != None:
query(
f"""update stdnt.stdetails set dateregst='{data['dateregst']}' where roll_no={data['roll_no']};""",
return_json=False)
if data['stemail'] != None:
query(
f"""update stdnt.stdetails set stemail='{data['stemail']}' where roll_no={data['roll_no']};""",
return_json=False)
if data['stphno'] != None:
query(
f"""update stdnt.stdetails set stphno={data['stphno']} where roll_no={data['roll_no']};""",
return_json=False)
if data['currentsem'] != None:
query(
f"""update stdnt.stdetails set currentsem={data['currentsem']} where roll_no={data['roll_no']};""",
return_json=False)
return {"message": "success updated."}, 201
except:
return {"message": "There was an error cannot connect"}, 500
def step_impl(context,n):
r = endpoints.get_n_post(n)
data = db.query("select * from employees limit 3")
print (data[2])
print (r.text)
