def admin_overview():
users = DBAccess.ExecuteSQL(
"""select surname, first_name, email, telephone, town, street, telephone2, info from users order by surname"""
)
if (users == None):
users = []
return render_template("admin_overview.html", users=users)
def user_request_overview():
requests = DBAccess.ExecuteSQL(
"""select s.category,
case when ud.id = %s
then 'Pomůže mi pan/í'
else 'Pomáhám paní/panu'
end,
case when ud.id = %s
then uo.first_name
else ud.first_name
end,
case when ud.id = %s
then uo.surname
else ud.surname
end,
to_char(r.date_time, 'DD-MM-YYYY HH24:MI'),
rs.status,
r.id
from requests r
inner join services s on r.id_services = s.id
inner join users ud on r.id_users_demand = ud.id
inner join users uo on r.id_users_offer = uo.id
inner join requests_status rs on r.id_requests_status = rs.id
where ud.id = %s or uo.id = %s order by r.date_time desc""",
(session["id_user"], session["id_user"], session["id_user"],
session["id_user"], session["id_user"]))
if requests == None:
requests = []
return render_template("user_request_overview.html", requests=requests)
def match():
id_users_services = request.args.get("id", type=int)
user_service_requested = DBAccess.ExecuteSQL(
"""
SELECT d.demand_offer, s.category,d.id,u.id
FROM users u
LEFT JOIN users_services us on us.id_users = u.id
LEFT JOIN services s on s.id = us.id_services
LEFT JOIN demand_offer d on d.id = us.id_demand_offer
WHERE us.id = %s
""",
(id_users_services, ),
)[0]
demand_offer_text = 'poptává' if user_service_requested[
2] == 1 else 'nabízí'
tomorrowStr = (date.today() + timedelta(days=1)).strftime('%Y-%m-%d')
dbUser = DBAccess.GetDBUserById(user_service_requested[3])
headerText = f'{dbUser.first_name} {dbUser.surname} {demand_offer_text} činnost {user_service_requested[1]}'
kwargs = {
"demand_offer": user_service_requested[0],
"id_demand_offer": user_service_requested[2],
"services": user_service_requested[1],
"id": id_users_services,
"headerText": headerText
}
return render_template("/match.html", **kwargs)
def setImagePrivate():
ids = DBAccess.ExecuteSQL('select id from users order by id')
result = ""
for id in ids:
opId = str(id[0]) + 'OP'
response = SetImagePrivate(opId)
result += opId + ': ' + response + '<br>'
return result
def login():
form = LoginForm()
if form.validate_on_submit():
sleep(3)
user = form.user.data.strip()
userRow = DBAccess.ExecuteSQL(
"select email, password, first_name, surname, id, level,salt from "
"users where trim(email) ilike %s",
(user, ),
)
if userRow is None:
flash("Uživatel nenalezen", FlashStyle.Danger)
return render_template("login.html", form=form)
userRow = userRow[0]
# execute sql gets list with one item, ie:[(email, password, first_name,
# surname, id)], we need just (), ie tuple
salt = userRow[6]
def addSalt(passwordArg):
return passwordArg + salt
#md5Pass = hashlib.md5(addSalt(str(form.password.data)).encode()).hexdigest()
bcrypt = Bcrypt()
#bcryptHash = bcrypt.generate_password_hash(addSalt(str(form.password.data)))
# check if second item is equal to hashed password
try:
if bcrypt.check_password_hash(userRow[1],
form.password.data) == False:
flash("Špatné heslo", FlashStyle.Danger)
return render_template("login.html", form=form)
except:
flash("Špatné heslo", FlashStyle.Danger)
return render_template("login.html", form=form)
if userRow[5] == 0:
flash(
"Uživatel není ověřen, počkejte prosím na ověření"
" administrátorem stránek.", FlashStyle.Danger)
return render_template("login.html", form=form)
session["user"] = user
session["id_user"] = userRow[4]
session["level_user"] = userRow[5]
dbUser = DBAccess.GetDBUserById(userRow[4])
dbUser.SaveToSession('dbUser')
# flash("Uživatel/ka {0} {1} přihlášen/a".format(userRow[2], userRow[3]), FlashStyle.Success)
return redirect(url_for("overview_bp.prehled_all"))
return render_template("login.html", form=form)
def sluzby_update():
services = DBAccess.ExecuteSQL("select * from services")
form = regFormBuilder(
services
) # put all services to form, but I need to display it - by for cycle below
form.checkBoxes.clear() # not to have duplicates on website
form.checkBoxes = []
for index in form.checkBoxIndexes:
form.checkBoxes.append(
getattr(form, "checkbox%d" % index)
) # displaying checkboxes on
# set all existing services with checked button, to be developed
# for checkbox in form.checkBoxes:
# existing_services = DBAccess.ExecuteScalar(
# "select * from users_services where id_users=%s and "
# "id_services=%s and id_demand_offer=%s",
# (nextId, checkbox.id, form.demandOffer.data),
# )
# if service in existing_services:
# checkbox.data = True
if form.validate_on_submit(): # if validated, save in db
nextId = session["id_user"]
services_checked = []
for index in form.checkBoxIndexes:
checkbox = getattr(form, "checkbox%d" % index)
if checkbox.data:
existing_combination = DBAccess.ExecuteScalar(
"select count(*) from users_services where id_users=%s and "
"id_services=%s and id_demand_offer=%s",
(nextId, checkbox.id, form.demandOffer.data),
)
text = DictionaryDemandOffer.get(
form.demandOffer.data, "unknown").lower()
if existing_combination == 0:
flash(
f'Zadaná kombinace {session["user"]}, {text} a {checkbox.label.text} neexistuje.'
)
else:
DBAccess.ExecuteUpdate(
"delete from users_services where id_users = %s and id_services = %s and id_demand_offer= %s", (nextId, checkbox.id, form.demandOffer.data), )
services_checked.append(checkbox.label)
kwargs = {
"demand_offer": DictionaryDemandOffer.get(form.demandOffer.data, "unknown"),
"category": services_checked,
}
# return redirect(url_for("overview_bp.prehled_all"))
return render_template("sluzby_success.html", **kwargs)
return render_template("sluzby_update.html", form=form)
def requests_detail():
rid = request.args.get("id", type=int)
if request.method == "POST":
# status = request.form["submit_button"]
status = RequestStatus[request.form["submit_button"]]
DBAccess.ExecuteUpdate(
"UPDATE requests SET id_requests_status= %s where id= %s",
(status, rid))
requests = DBAccess.ExecuteSQL(
"""select
ud.first_name,
ud.surname,
ud.email,
ud.telephone,
ud.town,
uo.first_name,
uo.surname,
uo.email,
uo.telephone,
uo.town,
s.category,
r.date_time,
r.add_information,
to_char(r.timestamp, 'YYYY-mm-DD HH12:MI'),
rs.status,
r.id,
ud.id,
uo.id
from requests r
inner join services s on r.id_services = s.id
inner join users ud on r.id_users_demand = ud.id
inner join users uo on r.id_users_offer = uo.id
inner join requests_status rs on r.id_requests_status = rs.id
where r.id =%s""", (rid, ))
if (requests is None):
abort(403)
requests = requests[0]
dbUser = DBUser.LoadFromSession('dbUser')
if dbUser.level < 2 and dbUser.id != int(
requests[16]) and dbUser.id != int(requests[17]):
abort(403)
return render_template("requests_detail.html", entries=requests)
def sluzby_upload():
services = DBAccess.ExecuteSQL("select * from services")
form = regFormBuilder(
services
) # put all services to form, but I need to display it - by for cycle below
form.checkBoxes.clear() # not to have duplicates on website
for index in form.checkBoxIndexes:
form.checkBoxes.append(
getattr(form, "checkbox%d" % index)
) # displaying checkboxes on website
if form.validate_on_submit(): # if validated, save in db
nextId = session["id_user"]
services_checked = []
for index in form.checkBoxIndexes:
checkbox = getattr(form, "checkbox%d" % index)
if checkbox.data: # for every checked services in form, save..
existing_combination = DBAccess.ExecuteScalar(
"select count(*) from users_services where id_users=%s and "
"id_services=%s and id_demand_offer=%s",
(nextId, checkbox.id, form.demandOffer.data),
)
text = DictionaryDemandOffer.get(
form.demandOffer.data, "unknown"
).lower()
if existing_combination > 0:
flash(
f'Zadaná kombinace {session["user"]}, {text} a {checkbox.label.text} již existuje.', FlashStyle.Danger
)
else:
DBAccess.ExecuteInsert(
"insert into users_services "
"(id_users, id_services, id_demand_offer) values (%s, %s, %s)",
(nextId, checkbox.id, form.demandOffer.data),
)
services_checked.append(checkbox.label)
kwargs = {
"demand_offer": DictionaryDemandOffer.get(form.demandOffer.data, "unknown"),
"category": services_checked,
}
return render_template("sluzby_success.html", **kwargs)
return render_template("sluzby.html", form=form)
def feedback():
range_evaluation = range(1, 6)
form = FeedbackFormular()
rid = request.args.get("id", type=int)
dbUser = DBUser.LoadFromSession('dbUser')
id_user_review = dbUser.id
id_users = DBAccess.ExecuteSQL(
"""select
id_users_demand, id_users_offer
from requests
where id =%s""", (rid, ))
if id_users[0][0] == id_user_review:
id_user_evaluated = id_users[0][1]
else:
id_user_evaluated = id_users[0][0]
if form.validate_on_submit():
comment = form.comment.data
number_evaluation = request.form["number_evaluation"]
DBAccess.ExecuteInsert(
"""insert into feedback
(id_requests, id_user, id_user_review, comment, evaluation)
values (%s, %s, %s, %s, %s)""",
(rid, id_user_evaluated, id_user_review, comment,
number_evaluation))
DBAccess.ExecuteUpdate(
"""update requests
set id_requests_status = 5
where id =%s""", (rid, ))
return render_template("feedback_thanks.html")
return render_template("feedback.html",
form=form,
range_evaluation=range_evaluation)
def requests():
requests = DBAccess.ExecuteSQL("""select
ud.first_name,
ud.surname,
ud.email,
ud.telephone,
uo.first_name,
uo.surname,
uo.email,
uo.telephone,
s.category,
date_trunc('minute',r.date_time),
r.add_information,
r.timestamp,
rs.status,
r.id
from requests r
inner join services s on r.id_services = s.id
inner join users ud on r.id_users_demand = ud.id
inner join users uo on r.id_users_offer = uo.id
inner join requests_status rs on r.id_requests_status = rs.id""")
if (requests == None):
requests = []
return render_template("requests.html", entries=requests)
def requests_detail_user():
rid = request.args.get("id", type=int)
dbUser = DBUser.LoadFromSession('dbUser')
userId = dbUser.id
requests = DBAccess.ExecuteSQL(
"""select s.category,
case when ud.id = %s
then uo.first_name
else ud.first_name
end,
case when ud.id = %s
then uo.surname
else ud.surname
end,
case when ud.id = %s
then uo.email
else ud.email
end,
r.date_time,
r.id,
ud.id,
uo.id,
r.id_users_creator
from requests r
inner join services s on r.id_services = s.id
inner join users ud on r.id_users_demand = ud.id
inner join users uo on r.id_users_offer = uo.id
inner join requests_status rs on r.id_requests_status = rs.id
where r.id =%s""", (userId, userId, userId, rid))
if (requests is None):
abort(403)
requests = requests[0]
dbUser = DBUser.LoadFromSession('dbUser')
if dbUser.level < 2 and dbUser.id != int(requests[6]) and dbUser.id != int(
requests[7]):
abort(403)
acceptButtonVisible = (int(requests[8]) != userId)
if request.method == "POST":
# status = request.form["submit_button"]
status = RequestStatusUser[request.form["submit_button"]]
DBAccess.ExecuteUpdate(
"UPDATE requests SET id_requests_status= %s where id= %s",
(status, rid))
text = 'potvrzena' if status == '2' else 'zamítnuta'
SendMail(
GetEmail('noreplyMail'), requests[3],
'Seniore.org - změna stavu vaší žádosti',
f'Vaše žádost / nabídka na činnost {requests[0]} dne {requests[4]} byla {text}.'
)
return redirect(url_for("profile_bp.user_request_overview"))
return render_template("request_detail_user.html",
entries=requests,
acceptButtonVisible=acceptButtonVisible)
def email_sent():
# kdo oslovuje
user = session["user"]
id_users_services = request.form.get("id", type=int)
dbUser = DBUser.LoadFromSession('dbUser')
email_oslovujici = dbUser.email
name_oslovujici = dbUser.first_name
surname_oslovujici = dbUser.surname
# date = request.form.get("date", type=str)
# time = request.form.get("time", type=str)
# strDateTime = f"{date} {time}"
# dt = datetime.strptime(strDateTime, "%Y-%m-%d %H:%M")
info = request.form.get("info", type=str)
email_user_long = DBAccess.ExecuteSQL(
"""
SELECT u.email, u.id, s.id, d.id
FROM users u
LEFT JOIN users_services us on us.id_users = u.id
LEFT JOIN services s on s.id = us.id_services
LEFT JOIN demand_offer d on d.id = us.id_demand_offer
WHERE us.id = %s
""", (id_users_services, ))
email_user = email_user_long[0][0] # for testing emails are sent to admin
services_id = email_user_long[0][2]
id_demand_offer = email_user_long[0][3]
offeringUserId = email_user_long[0][
1] if id_demand_offer == 2 else session["id_user"]
demandingUserId = email_user_long[0][
1] if id_demand_offer == 1 else session["id_user"]
id_request = DBAccess.GetSequencerNextVal("requests_id_seq")
DBAccess.ExecuteInsert(
"INSERT INTO requests (id, id_users_demand, id_users_offer, id_services, "
"timestamp, date_time, add_information, id_requests_status, id_users_creator)"
" values (%s, %s,%s,%s,now(),now(),%s,%s, %s)",
(id_request, demandingUserId, offeringUserId, services_id, info, 1,
session["id_user"]))
# protistrana, kdo je osloven - email_user
dbUser_protistrana = DBAccess.GetDBUserByEmail(email_user)
name_protistrana = dbUser_protistrana.first_name
surname_protistrana = dbUser_protistrana.surname
text1 = 'Vaši nabídku' if id_demand_offer == 1 else 'Váš požadavek'
text2 = 'Vaši nabídky' if id_demand_offer == 1 else 'vašeho požadavku'
# mail to person who click on "contact"
SendMail(
GetEmail('noreplyMail'), f'{email_oslovujici}',
'Zaregistrována žádost o spolupráci',
f'''<html>Úspěšně jsme zaregistrovali Vaší žádost o spolupráci. <br>
Váš kontakt je {name_protistrana}, email: {email_user} <br>
Prosíme, spojte se, abyste se mohli domluvit na podrobnostech. Nezapomeňte dodržovat pravidla: <a href="https://app.seniore.org/podminky_dobrovolnici"> dobrovolníci</a> / <a href="https://app.seniore.org/podminky_seniori"> senioři</a><br>
V případě potíží, nebo nejasností nám neváhejte napsat na [email protected]. <br>
Děkujeme, Váš tým Seniore</html>''')
# mail to person who is being contacted
SendMail(
GetEmail('noreplyMail'), f'{email_user}',
'Zaregistrována žádost o spolupráci', f'''
<html> Pan / paní {name_oslovujici} by se s Vámi rád/a spojil/a ohledně možné pomoci.
Kontaktní email je: {email_oslovujici} <br>
Prosíme, spojte se, abyste se mohli domluvit na podrobnostech. Nezapomeňte dodržovat pravidla: <a href="https://app.seniore.org/podminky_dobrovolnici"> dobrovolníci</a> / <a href="https://app.seniore.org/podminky_seniori"> senioři</a><br>
V případě potíží, nebo nejasností nám neváhejte napsat na [email protected]. <br>
Děkujeme, Váš tým Seniore < / html > ''')
# mail to admins
SendMail(
GetEmail('noreplyMail'), GetEmail('adminMail'),
'Seniore - zažádáno o spolupráci',
f'''Uživatel {user} se s chce setkat s {email_user}! :-D <br>
Doplňující informace: {info}. <br>
Prosím, zkontrolujte žádost v http://seniore.herokuapp.com/requests_detail?id={id_request}.'''
)
# print(response.status_code)
# print(response.body)
# print(response.headers)
return render_template("email_sent.html", text1=text1, text2=text2)
def profil():
dbUser = DBAccess.GetDBUserById(session["id_user"])
name = f'{dbUser.first_name} {dbUser.surname}'
info = dbUser.info
mail = dbUser.email
phone = dbUser.telephone
latitude = str(
DBAccess.ExecuteScalar("select latitude from users where id = %s",
(session["id_user"], )))
longitude = str(
DBAccess.ExecuteScalar("select longitude from users where id = %s",
(session["id_user"], )))
username = session["user"]
imgCloudUrl = GetImageUrl(session["id_user"])
if request.method == "GET":
users_services = DBAccess.ExecuteSQL(
"select s.category as category, d.demand_offer as demand_offer,us.id from users_services us"
" left join users u on us.id_users = u.id"
" left join services s on s.id = us.id_services"
" left join demand_offer d on d.id = us.id_demand_offer where u.id = %s",
(session["id_user"], ))
addServiceText = 'Přidat službu'
if (users_services is None):
users_services = []
addServiceText = 'Zobrazit mě na mapě'
sndmap = Map(
identifier="sndmap",
style="height:100%;width:100%;margin:0;",
lat=latitude,
lng=longitude,
report_clickpos=True,
clickpos_uri="/clickpost/",
markers=[{
"icon":
"https://maps.google.com/mapfiles/kml/pal2/icon10.png",
"lat":
latitude,
"lng":
longitude,
"infobox":
f"<b>{username}</b><img class=img_mapa src= {imgCloudUrl} />"
}])
requests = DBAccess.ExecuteSQL(
"""select
ud.first_name,
ud.surname,
ud.email,
ud.telephone,
uo.first_name,
uo.surname,
uo.email,
uo.telephone,
s.category,
to_char(r.date_time, 'DD-MM-YYYY HH24:MI'),
r.add_information,
r.timestamp,
rs.status,
r.id
from requests r
inner join services s on r.id_services = s.id
inner join users ud on r.id_users_demand = ud.id
inner join users uo on r.id_users_offer = uo.id
inner join requests_status rs on r.id_requests_status = rs.id
where ud.id = %s or uo.id =%s """,
(session["id_user"], session["id_user"]))
if requests == None:
requests = []
return render_template("profil.html",
users_services=users_services,
nazev=imgCloudUrl,
sndmap=sndmap,
requests=requests,
name=name,
info=info,
mail=mail,
phone=phone,
addServiceText=addServiceText)
def prehled_all():
services = DBAccess.ExecuteSQL("select * from services order by pageorder")
onMap = DBAccess.ExecuteScalar(
'select 1 from users_services where id_users = %s',
(session['id_user'], )) != None
checked_services_id_list = []
for service in services:
checked_services_id_list.append(service[0])
form = regFormBuilder(
services
) # put all services to form, but I need to display it - by for cycle below
form.checkBoxes.clear()
form.demandOffer.choices = [('2', 'poskytují pomoc'),
('1', 'potřebují pomoc')]
for index in form.checkBoxIndexes:
checkbox = getattr(form, "checkbox%d" % index)
if (request.method == 'GET'):
checkbox.data = True
form.checkBoxes.append(getattr(
form, "checkbox%d" % index)) # displaying checkboxes on website
if form.validate_on_submit():
checked_services_id_list = []
for index in form.checkBoxIndexes:
checkbox = getattr(form, "checkbox%d" % index)
if checkbox.data:
checked_services_id_list.append(checkbox.id)
result = DBAccess.ExecuteSQL(
'''
SELECT u.id, u.first_name, u.surname, u.info, d.demand_offer, u.latitude, u.longitude, us.id,s.category
FROM users u
LEFT JOIN users_services us on us.id_users = u.id
LEFT JOIN services s on s.id = us.id_services
LEFT JOIN demand_offer d on d.id = us.id_demand_offer
WHERE d.id = %s AND
us.id_services in %s
ORDER BY u.id, us.id
''', (int(form.demandOffer.data), tuple(checked_services_id_list)))
if result is None:
result = []
flash(
"Bohužel v systému nejsou zadány žádné služby dle vašeho filtru.")
usersCatDict = {}
for row in result:
key = list(row)
key = tuple(key[:7])
# key = tuple(list(row)[:7])
value = row[-2:]
if (key in usersCatDict):
usersCatDict[key].append(value)
else:
usersCatDict[key] = [value]
demandOfferText = 'nabízené'
mapIconUrl = "https://maps.google.com/mapfiles/ms/icons/green-dot.png"
if (int(form.demandOffer.data) == 1):
demandOfferText = 'poptávané'
mapIconUrl = "https://maps.google.com/mapfiles/ms/icons/blue-dot.png"
dbUser = DBAccess.GetDBUserById(session['id_user'])
# markery pro kazdeho vyhledaneho
markers = []
marker = {}
marker["icon"] = "https://maps.google.com/mapfiles/kml/pal2/icon10.png"
marker["lat"] = str(dbUser.latitude)
marker["lng"] = str(dbUser.longitude)
marker[
"infobox"] = f'<b>{dbUser.first_name} </b><br>{dbUser.info}<img class=img_mapa src= {GetImageUrl(dbUser.id)} />'
markers.append(marker)
for user in usersCatDict.keys():
servicesHTML = '<ul>'
for service in usersCatDict[user]:
servicesHTML = servicesHTML + f'<li> {service[1]} <a href="/match?id={service[0]}">Kontaktovat</a></li>'
servicesHTML = servicesHTML + '</ul>'
pictureUrl = GetImageUrl(user[0])
marker = {}
marker["icon"] = mapIconUrl
marker["lat"] = str(user[5])
marker["lng"] = str(user[6])
marker["infobox"] = f'''<b>{user[1]} </b><p>{user[3]}</p>\
<img class=img_mapa src= {pictureUrl} /><br>\
<p>{demandOfferText} služby: {servicesHTML}</p>'''
markers.append(marker)
map = Map(
identifier="sndmap",
style="height:100%;width:100%;margin:0;",
lat=str(dbUser.latitude),
lng=str(dbUser.longitude),
markers=markers
) # get map, zoom on location of actual user, insert markers from select, ie users who provide specific required service
return render_template("prehled_all.html",
sndmap=map,
form=form,
onMap=onMap)
def prehled_filtr():
form = OverviewFormBase()
services = DBAccess.ExecuteSQL("select * from services")
addresses = DBAccess.ExecuteSQL("select distinct lower(town) from users")
if request.method == "GET":
return render_template("prehled.html",
form=form,
services=services,
addresses=addresses)
elif request.method == "POST":
vysledekselectu = DBAccess.ExecuteSQL(
"""
SELECT u.first_name, u.surname, s.category, d.demand_offer, u.town, us.id, u.latitude, u.longitude, u.id, u.info
FROM users u
LEFT JOIN users_services us on us.id_users = u.id
LEFT JOIN services s on s.id = us.id_services
LEFT JOIN demand_offer d on d.id = us.id_demand_offer
WHERE d.id = %s and s.id = %s and lower(u.town) = lower(%s)
ORDER BY us.id desc
""", (form.demandOffer.data, request.form["category"],
request.form["address"]))
if vysledekselectu is None:
vysledekselectu = []
dbUser = DBAccess.GetDBUserById(session['id_user'])
if len(vysledekselectu) == 0:
flash("Bohužel pro vámi zadanou kombinaci pro vás nemáme parťáka.")
# markery pro kazdeho vyhledaneho
markers = []
marker = {}
marker["icon"] = "https://maps.google.com/mapfiles/kml/pal2/icon10.png"
marker["lat"] = str(dbUser.latitude)
marker["lng"] = str(dbUser.longitude)
marker[
"infobox"] = f'<b>{dbUser.first_name} </b><br>{dbUser.info}<img class=img_mapa src= {GetImageUrl(dbUser.id)} />'
markers.append(marker)
for user in vysledekselectu:
pictureUrl = GetImageUrl(user[8])
marker = {}
marker[
"icon"] = "https://maps.google.com/mapfiles/ms/icons/green-dot.png"
marker["lat"] = str(user[6])
marker["lng"] = str(user[7])
marker[
"infobox"] = f'<b>{user[0]} {user[1]}</b><br>{user[9]}<img class=img_mapa src= {pictureUrl} /> <a href="/match?id={user[5]}">Kontaktovat</a>'
markers.append(marker)
map = Map(
identifier="sndmap",
style="height:100%;width:100%;margin:0;",
lat=str(dbUser.latitude),
lng=str(dbUser.longitude),
markers=markers
) # get map, zoom on location of actual user, insert markers from select, ie users who provide specific required service
return render_template("prehled_success.html",
entries=vysledekselectu,
sndmap=map)