def approve(TransID):
cur = Tlbx.dbConnectDict()
TransID = str(TransID)
query = (
"SELECT TransID, Name, Description, WithdrawDeposit, Company, DATE_FORMAT(Date, '%m-%d-%Y') AS Date, Amount, Comments, Receipt FROM tTransaction WHERE TransID ="
+ TransID + ";")
cur.execute(query)
result = cur.fetchone()
result = dict(result)
Name = result['Name']
Description = result['Description']
WithdrawDeposit = result['WithdrawDeposit']
Company = result['Company']
Date = (result['Date'])
Amount = (result['Amount'])
Comments = (result['Comments'])
Receipt = (result['Receipt'])
return render_template('EditTemplate/Edit.html',
TransID=TransID,
Name=Name,
Description=Description,
WithdrawDeposit=WithdrawDeposit,
Company=Company,
Comments=Comments,
Date=Date,
Amount=Amount,
Receipt=Receipt)
def sendMessage(userID):
#user userID to get both friendIDs to submit messages
#Get both friendIDs associated with friendship
cur, db = Tlbx.dbConnectDict()
query = ("SELECT friendID from tFriend \
WHERE tFriend.user = %s \
AND tFriend.friend = %s;")
data = (current_user.id, userID)
cur.execute(query, data)
UserFriendID = cur.fetchone()
data = (userID, current_user.id)
cur.execute(query, data)
FriendFriendID = cur.fetchone()
session = Database.Session()
message = request.form['message']
addMessageFriend = Database.tMessage(friendID = FriendFriendID['friendID'], sender = current_user.id, \
recipient = userID, time_Sent = datetime.datetime.now(), message = message)
addMessageUser = Database.tMessage(friendID = UserFriendID['friendID'], sender = current_user.id, \
recipient = userID, time_Sent = datetime.datetime.now(), message = message)
session.add(addMessageUser)
session.add(addMessageFriend)
session.commit()
return redirect('/Messages')
def finishpayment():
cur = Tlbx.dbConnectDict()
query = (
"SELECT tempID, WithdrawDeposit, Company, DATE_FORMAT(Date, '%m-%d-%Y') AS Date, Date AS Sort, Amount FROM tTemporary ORDER BY Sort DESC;"
)
cur.execute(query)
return render_template('FinishPayments/finishpaymentSearch.html',
items=cur.fetchall())
def routeMessages():
friendID = None
userID = int(current_user.id)
#Get all friends you have that you've sent messages with.
friends, db = Tlbx.dbConnectDict()
friendQuery = (
"Select MAX(tMessage.time_sent) as ts, tFriend.friendID, tUser.userID, tUser.firstName, tUser.lastName, tUser.image from tUser \
JOIN tFriend ON tUser.userID = tFriend.friend \
LEFT JOIN tMessage ON tMessage.friendID = tFriend.friendID\
WHERE tFriend.user = %s \
GROUP BY tFriend.friendID \
ORDER BY ts DESC;")
data = (current_user.id)
friends.execute(friendQuery, data)
latestMessageQuery = (
"Select tMessage.time_sent as ts, tFriend.friendID, tUser.userID, tUser.firstName, tUser.lastName, tMessage.message from tUser \
JOIN tFriend ON tUser.userID = tFriend.friend \
LEFT JOIN tMessage ON tMessage.friendID = tFriend.friendID\
JOIN (SELECT tMessage.friendID, MAX(tMessage.time_sent) as ts FROM tMessage \
GROUP by tMessage.friendID) \
AS t2 \
ON tMessage.friendID = t2.friendID AND tMessage.time_sent = t2.ts\
WHERE tFriend.user = %s \
ORDER BY tMessage.time_sent DESC;")
latest, db = Tlbx.dbConnectDict()
data = (current_user.id)
latest.execute(latestMessageQuery, data)
messageQuery = (
"Select tMessage.friendID, tMessage.message, tMessage.time_Sent, tMessage.recipient, tUser.firstName, tUser.lastName from tMessage \
JOIN tFriend ON tFriend.friendID = tMessage.friendID \
JOIN tUser ON tFriend.friend = tUser.userID \
WHERE tFriend.user = %s \
ORDER BY tMessage.time_sent")
cur, db = Tlbx.dbConnectDict()
data = (current_user.id)
cur.execute(messageQuery, data)
Messages.updateMessageTime(current_user.id)
return render_template('/Messages/Messages.html',
latest=latest.fetchall(),
friendID=friendID,
userID=userID,
messages=cur.fetchall(),
friends=friends.fetchall())
def getPostComments(postID):
cur, db = Tlbx.dbConnectDict()
query = "SELECT tPosts.postID, tComments.Comment, tUser.userID, tUser.firstName, tUser.lastName, tUser.image \
FROM tComments INNER JOIN tUser ON tComments.userID = tUser.userID INNER JOIN tPosts ON tComments.postID = tPosts.postID \
WHERE tPosts.postID = %s ORDER BY tComments.commentID DESC LIMIT 3;"
data = (postID)
cur.execute(query, data)
commentResults = cur.fetchall()
return commentResults
def askAvailability(dogID):
msg = ast.literal_eval(json.dumps(dogID))
cur, db = Tlbx.dbConnectDict()
query = "SELECT * FROM tAvailability WHERE DogID = %s"
data = (msg['dogID'])
cur.execute(query, data)
Availability = cur.fetchall()
for time in Availability:
time['Begin_ts'] = str(time['Begin_ts'])
time['End_ts'] = str(time['End_ts'])
socketio.emit('giveAvailability',
{'Availability': Availability}, namespace='/Availability/', room=request.sid)
def addPlaydate(msg):
msg = ast.literal_eval(json.dumps(msg))
eastern = timezone('US/Eastern')
cur, db = Tlbx.dbConnectDict()
query = "SELECT * FROM tPlayDate WHERE AvailabilityID = %s"
data = (msg['AvailabilityID'])
cur.execute(query, data)
playdate = cur.fetchall()
if (playdate != ()):
socketio.emit('PlayDateException',
{'Error': "This time is in use by another playdate."}, namespace='/Availability/', room=request.sid)
else:
Availability.commitPlayDate(msg['hostDogID'], msg['guestDogID'], msg['creatorID'] ,msg['AvailabilityID'], parser.parse(msg['Begin_ts']).astimezone(eastern), parser.parse(msg['End_ts']).astimezone(eastern), msg['message'])
def dogProfile(dogID):
session = Database.Session()
#Get information about the dog
dog = session.query(Database.tDog, Database.tBreed).filter(Database.tDog.dogID == dogID) \
.join(Database.tBreed, Database.tBreed.breedID == Database.tDog.dogID)
for dog in dog:
dog = dog
#Get comments of posts
commentResults = Comments.getComments()
#Get reacts of posts
reactResults = Reacts.getReacts()
yourReacts = Reacts.yourReacts()
#Get Posts
postResults = Posts.getPosts()
likes = {}
like = []
for react in postResults:
likes[react.postID] = 'Like'
for yourReact in yourReacts:
if yourReact.postID in likes.keys():
likes[yourReact.postID] = 'Unlike'
for react in postResults:
like.append(likes[react.postID])
cur, db = Tlbx.dbConnectDict()
query = "select tFamily.familyID from tFamily \
JOIN tUser on tFamily.familyID = tUser.familyID \
JOIN tDog on tUser.familyID = tDog.familyID \
WHERE tUser.userID = %s AND tDog.dogID = %s;"
data = (current_user.id, dogID)
cur.execute(query, data)
family = cur.fetchone()
if (family is not None):
return render_template('Account/AdminDogProfile.html',
postResults=zip(postResults, like),
commentResults=commentResults,
reactResults=reactResults,
yourReacts=yourReacts,
dog=dog)
else:
return render_template('Account/DogProfile.html',
postResults=zip(postResults, like),
commentResults=commentResults,
reactResults=reactResults,
yourReacts=yourReacts,
dog=dog)
def Paging():
Name = request.form['Name']
Description = request.form['Description']
WithdrawDeposit = request.form['WithdrawDeposit']
Company = request.form['Company']
Date = request.form['Date']
Amount = request.form['Amount']
cur = Tlbx.dbConnectDict()
query = (
"SELECT TransID, Name, Description, WithdrawDeposit, Company, DATE_FORMAT(Date, '%m-%d-%Y') AS Date, Date AS Sort, Amount FROM tTransaction"
)
if (Name != '' or Description != '' or WithdrawDeposit != ''
or Company != '' or Date != '' or Amount != ''):
query = query + " WHERE "
if (Name != ''):
query = query + "Name LIKE '" + Name + "'"
if (Description != '' or WithdrawDeposit != '' or Company != ''
or Date != '' or Amount != ''):
query = query + " AND "
if (Description != ''):
query = query + "Description LIKE '" + Description + "'"
if (WithdrawDeposit != '' or Company != '' or Date != ''
or Amount != ''):
query = query + " AND "
if (WithdrawDeposit != ''):
query = query + "WithdrawDeposit LIKE '" + WithdrawDeposit + "'"
if (Company != '' or Date != '' or Amount != ''):
query = query + " AND "
if (Company != ''):
query = query + "Company LIKE '" + Company + "'"
if (Date != '' or Amount != ''):
query = query + " AND "
if (Date != ''):
Date = Date.split("-")
Month = Date[0]
Day = Date[1]
Year = Date[2]
Date = Year + "-" + Month + "-" + Day
query = query + "Date LIKE '" + Date + "'"
if (Amount != ''):
query = query + " AND "
if (Amount != ''):
query = query + "Amount LIKE " + Amount
query = query + " ORDER BY Sort DESC;"
cur.execute(query)
return render_template('Search/Search.html', items=cur.fetchall())
def Delete(TransID):
TransID = str(TransID)
query = ("DELETE FROM tTransaction WHERE transID=" + TransID + ";")
cur = Tlbx.dbConnectDict()
cur.execute(query)
balance = Tlbx.Balance()
cur = Tlbx.dbConnect()
query = ("Select COUNT(tempID) FROM tTemporary")
cur.execute(query)
Transaction = cur.fetchone()[0]
if Transaction is None:
Transaction = "0"
return render_template('HomePage/index.html',
balance=balance,
Transaction=Transaction)
def askPlayDate(dogID):
msg = ast.literal_eval(json.dumps(dogID))
cur, db = Tlbx.dbConnectDict()
query = "SELECT *, hostDog.name, guestDog.name FROM tPlayDate \
JOIN tDog hostDog ON tPlayDate.hostDogID = hostDog.dogID \
JOIN tDog guestDog ON tPlayDate.guestDogID = guestDog.dogID \
WHERE tPlayDate.hostDogID = %s"
data = (msg['dogID'])
cur.execute(query, data)
Availability = cur.fetchall()
for time in Availability:
time['Begin_ts'] = str(time['Begin_ts'])
time['End_ts'] = str(time['End_ts'])
socketio.emit('givePlayDates',
{'PlayDates': Availability}, namespace='/Availability/', room=request.sid)
def finish(tempID):
#database connection
cur = Tlbx.dbConnectDict()
tempID = str(tempID)
query = (
"SELECT tempID, WithdrawDeposit, Company, DATE_FORMAT(Date, '%m-%d-%Y') AS Date, Amount FROM tTemporary WHERE tempID ="
+ tempID + ";")
cur.execute(query)
result = cur.fetchone()
result = dict(result)
WithdrawDeposit = result['WithdrawDeposit']
Company = result['Company']
Date = (result['Date'])
Amount = (result['Amount'])
return render_template('FinishPayments/finish.html',
tempID=tempID,
WithdrawDeposit=WithdrawDeposit,
Company=Company,
Date=Date,
Amount=Amount)
def familyCreation():
sqlalch = Database.Session()
HeadOfHouse = Database.tHeadofHouse(userID=current_user.id)
sqlalch.add(HeadOfHouse)
id = sqlalch.commit()
CreateFamilyform = CreateFamilyForm()
if CreateFamilyform.validate_on_submit():
CreateDogform = CreateDogForm()
familyName = CreateFamilyform.surName.data
cur, db = Tlbx.dbConnectDict()
cursor = Database.Session()
query = (
"INSERT INTO tFamily (familyName, headofHouseID) VALUES(%s, %s);")
data = (familyName, id)
cur.execute(query, data)
session['familyID'] = db.insert_id()
x = cursor.query(Database.tUser).get(current_user.id)
x.familyID = db.insert_id()
cursor.commit()
return render_template('Dog/NewDog.html', CreateDogform=CreateDogform)
flash("Please add a family name")
return render_template('/Family/FamilyCreate.html',
CreateFamilyform=CreateFamilyform)
def DogCreation():
FavoriteParkform = FavoriteParkForm()
if FavoriteParkform.validate_on_submit():
#insert Breed
cur, db = Tlbx.dbConnectDict()
breedQuery = "INSERT INTO tBreed (breed) VALUES (%s);"
data = (session.get('breed'))
cur.execute(breedQuery, data)
db.commit()
breedID = cur.lastrowid
#Insert Address then insert Favorite Park
image = None
addressQuery = "INSERT INTO tAddress (address, city, state) VALUES (%s, %s, %s);"
try:
address = request.form['desktopstreet_address']
city = request.form['desktopadministrative_area_level_1']
state = request.form['desktoplocality']
except:
address = request.form['mobilestreet_address']
city = request.form['mobileadministrative_area_level_1']
state = request.form['mobilelocality']
data = (address, city, state)
cur.execute(addressQuery, data)
favParkQuery = "INSERT INTO tFavoritePark (parkName, AddressID, image) VALUES (%s, LAST_INSERT_ID(), %s);"
data = (FavoriteParkform.parkName.data, image)
cur.execute(favParkQuery, data)
db.commit()
favParkID = cur.lastrowid
#Insert favorite toy.
image = None
favToyQuery = "INSERT INTO tFavoriteToy (ToyName, image) VALUES (%s, %s);"
data = (session['favToy'], image)
cur.execute(favToyQuery, data)
db.commit()
favToyID = cur.lastrowid
cursor = Database.Session()
familyID = session.get('familyID')
if familyID is None:
familyID = cursor.query(Database.tUser.familyID).filter(
Database.tUser.userID == current_user.id)
for familyID in familyID:
familyID = familyID.familyID
# commit all the dog data into the database.
dogQuery = "Insert into tDog (name, gender, breedID, fixed, age, Size, Weight, bio, image, favToyID, favParkID, familyID) VALUES (%s, %s, %s, %s,%s, %s, %s, %s,%s, %s, %s, %s)"
data = (session.get('dogName'), session.get('gender'), breedID,
session.get('fixed'), session.get('age'), session.get('size'),
session.get('weight'), session.get('bio'),
session.get('image'), favToyID, favParkID, familyID)
cur.execute(dogQuery, data)
db.commit()
#Follow the dog
sqlalch = Database.Session()
Query = Database.tFollowers(dogID=cur.lastrowid,
userID=current_user.id)
sqlalch.add(Query)
sqlalch.commit()
return redirect('/dashboard')
flash("please input your dogs favorite park")
return render_template('/Dog/NewPark.html',
FavoriteParkform=FavoriteParkform)
#finally commit all the dog data into the database.
dogQuery = "Insert into tDog (name, gender, breedID, fixed, age, Size, Weight, bio, image, favToyID, favParkID, familyID) VALUES (%s, %s, %s, %s,%s, %s, %s, %s,%s, %s, %s, %s)"
data = (session.get('dogName'), session.get('gender'), breedID,
session.get('fixed'), session.get('age'), session.get('size'),
session.get('weight'), session.get('bio'), image, favToyID,
favParkID, session.get('familyID'))
cur.execute(dogQuery, data)
db.commit()
#Default to follow the dog
dogID = cur.lastrowid
FollowDogs(dogID)
#Redirect to the dashboard
return redirect('/dashboard')
def Update(TransID):
name = ''
Name = request.form['Name']
Description = request.form['Description']
WithdrawDeposit = request.form['WithdrawDeposit']
Company = request.form['Company']
Date = request.form['Date']
split = Date.split("-")
month = split[0]
day = split[1]
year = split[2]
Date = year + "-" + month + "-" + day
Amount = request.form['Amount']
Amount = float(Amount)
Comments = request.form['Comments']
name = request.form['Image']
if (WithdrawDeposit == "Withdraw" and Amount > 0):
Amount = (Amount * -1)
if (WithdrawDeposit == "Deposit"
or WithdrawDeposit == "deposit" and Amount < 0):
Amount = abs(Amount)
Amount = str(Amount)
TransID = str(TransID)
request.get_data('Name')
request.get_data('Description')
request.get_data('WithdrawDeposit')
request.get_data('Company')
request.get_data('Amount')
request.get_data('Comments')
if (request.files['file'].filename == '' and request.form['Image'] == ''):
name = ''
elif (request.form['Image'] != ''
and request.files['file'].filename == ''):
name = request.form['Image']
elif (request.files['file'].filename != ''
and request.form['Image'] == ''):
name = ''
if request.files['file'].filename != '':
image = Image.open(request.files['file'].stream)
if hasattr(image, '_getexif'):
orientation = 0x0112
exif = image._getexif()
if exif is not None:
orientation = exif[orientation]
rotations = {
3: Image.ROTATE_180,
6: Image.ROTATE_270,
8: Image.ROTATE_90
}
if orientation in rotations:
image = image.transpose(rotations[orientation])
name = request.files['file'].filename
path = '/var/www/Flask/static/' + name
image.save(path, "JPEG", optimize=True, quality=85)
else:
if request.files['file'].filename != '':
name = ''
if request.files['file'].filename != '':
image = Image.open(request.files['file'].stream)
if hasattr(image, '_getexif'):
orientation = 0x0112
exif = image._getexif()
if exif is not None:
orientation = exif[orientation]
rotations = {
3: Image.ROTATE_180,
6: Image.ROTATE_270,
8: Image.ROTATE_90
}
if orientation in rotations:
image = image.transpose(rotations[orientation])
name = request.files['file'].filename
path = '/var/www/Flask/static/' + name
image.save(path, "JPEG", optimize=True, quality=85)
cur = Tlbx.dbConnectDict()
sql = "UPDATE tTransaction SET Name = (%s),Description= (%s),WithdrawDeposit= (%s), Company= (%s), Date=(%s), Amount= (%s), Comments=(%s), Receipt=(%s) WHERE TransID=(%s)"
cur.execute(sql, (Name, Description, WithdrawDeposit, Company, Date,
Amount, Comments, name, TransID))
Date = month + "-" + day + "-" + year
request.get_data('Date')
return render_template("UpdatePayment/Update.html",
Name=Name,
Description=Description,
WithdrawDeposit=WithdrawDeposit,
Company=Company,
Date=Date,
Amount=Amount,
Comments=Comments,
image=name)
def result():
#Request data from newpayment webpage
name = ''
Name = request.form['Name']
Description = request.form['Description']
WithdrawDeposit = request.form['WithdrawDeposit']
Company = request.form['Company']
Date = request.form['Date']
#Split data since it is submitted as mm/dd/yyyy and convert to yyyy/mm/dd
split = Date.split("-")
month = split[0]
day = split[1]
year = split[2]
Date = year + "-" + month + "-" + day
#implemented required html tag to prevent crash if no amount
Amount = request.form['Amount']
Amount = float(Amount)
Comments = request.form['Comments']
# a check to ensure Amount will be positive or negative
if (WithdrawDeposit == "Withdraw" or WithdrawDeposit == "withdraw"):
Amount = (Amount * -1)
if (WithdrawDeposit == "Deposit" or WithdrawDeposit == "deposit"):
Amount = abs(Amount)
# get data for new webpage, potentially unnecessary.
request.get_data('Name')
request.get_data('Description')
request.get_data('WithdrawDeposit')
request.get_data('Company')
request.get_data('Amount')
request.get_data('Comments')
#if image isn't null, rotate the image and PIL automatically sheds off EXIF tag
if request.files['file'].filename != '':
image = Image.open(request.files['file'].stream)
if hasattr(image, '_getexif'):
orientation = 0x0112
exif = image._getexif()
if exif is not None:
orientation = exif[orientation]
rotations = {
3: Image.ROTATE_180,
6: Image.ROTATE_270,
8: Image.ROTATE_90
}
if orientation in rotations:
image = image.transpose(rotations[orientation])
name = request.files['file'].filename
path = 'C:/Users/johns/Google Drive/ProgrammingProjects/Python/Flask_Desktop/static/' + name
#Does an optimization pass to compress without noticeably reducing quality
image.save(path, "JPEG", optimize=True, quality=85)
#Connect to database and commit data
cur = Tlbx.dbConnectDict()
query = (
"INSERT INTO tTransaction (Name, Description, WithdrawDeposit, Company, Date, Amount, Comments, Receipt) VALUES ( %s, %s, %s, %s, %s, %s, %s,%s)"
)
data = (Name, Description, WithdrawDeposit, Company, Date, Amount,
Comments, name)
cur.execute(query, data)
request.get_data('image')
#Convert date back to display mm-dd-yyyy
Date = month + "-" + day + "-" + year
request.get_data('Date')
return render_template("NewPayment/result.html",
Name=Name,
Description=Description,
WithdrawDeposit=WithdrawDeposit,
Company=Company,
Date=Date,
Amount=Amount,
Comments=Comments,
image=name)
def finishTemp(tempID):
name = ''
tempID = str(tempID)
#image orienting and saving
image = ''
if request.files['file'].filename != '':
image = Image.open(request.files['file'].stream)
if hasattr(image, '_getexif'):
orientation = 0x0112
exif = image._getexif()
if exif is not None:
orientation = exif[orientation]
rotations = {
3: Image.ROTATE_180,
6: Image.ROTATE_270,
8: Image.ROTATE_90
}
if orientation in rotations:
image = image.transpose(rotations[orientation])
name = request.files['file'].filename
path = '/var/www/Flask/static/' + name
image.save(path, "JPEG", optimize=True, quality=85)
#Get data from web page to upload to database
Name = request.form['Name']
Description = request.form['Description']
WithdrawDeposit = request.form['WithdrawDeposit']
Company = request.form['Company']
Date = request.form['Date']
split = Date.split("-")
month = split[0]
day = split[1]
year = split[2]
Date = year + "/" + month + "/" + day
Amount = request.form['Amount']
Amount = float(Amount)
Comments = request.form['Comments']
if (WithdrawDeposit == "Withdraw" and Amount > 0):
Amount = (Amount * -1)
request.get_data('Name')
request.get_data('Description')
request.get_data('WithdrawDeposit')
request.get_data('Company')
request.get_data('Amount')
request.get_data('Comments')
cur = Tlbx.dbConnectDict()
query = (
"INSERT INTO tTransaction (Name, Description, WithdrawDeposit, Company, Date, Amount, Comments, Receipt) VALUES (%s, %s, %s, %s, %s, %s, %s,%s)"
)
data = (Name, Description, WithdrawDeposit, Company, Date, Amount,
Comments, name)
cur.execute(query, data)
request.get_data('image')
Date = month + "-" + day + "-" + year
request.get_data('Date')
cur = Tlbx.dbConnectDict()
query = ("DELETE FROM tTemporary WHERE tempID=" + tempID + ";")
cur.execute(query)
return render_template("FinishPayments/finishResults.html",
Name=Name,
Description=Description,
WithdrawDeposit=WithdrawDeposit,
Company=Company,
Date=Date,
Amount=Amount,
Comments=Comments,
image=name)
