def user_confirmation(token):
try:
ts = URLSafeTimedSerializer(app.config["SECRET_KEY"])
user_id = ts.loads(token, salt="email-confirm-key")
except:
abort(403)
dbUser = DBAccess.GetDBUserById(user_id)
DBAccess.ExecuteUpdate('update users set level=1 where id=%s', (user_id, ))
email_text = f'''<html> Dobrý den,
<br>
<br> Váš účet byl ověřen a nyní se můžete <a href="https://app.seniore.org/login/">přihlásit </a>. (Pokud odkaz nefunguje, prosíme, je nutné ho zkopírovat a celý vložit do vašeho prohlížeče.)<br>
<br>
Věnujte prosím chviličku instrukcím, jak aplikaci používat. <br>
1. Na mapce uvidíte svojí polohu. V blízkosti se zobrazí lidé, kteří mohou pomoci, nebo pomoc potřebují. <br>
Je možné, že se ve Vaší lokalitě zatím nikdo nepřihlásil. Kontaktujte prosím kohokoliv, kdo by se mohl zapojit. <br>
2. Pro ostatní uživatele jste zatím neviditení! Abyste se i vy zobrazil jiným uživatelům, je potřeba kliknout na tlačítko “Zobrazit mě na mapě” na kartě "Vyhledat".<br>
V následujícím kroku vyplníte, zda pomoc potřebujete, nebo jí můžete poskytnout.<br>
3. Kliknutím na Pin (znaménko v mapě) u jiného uživatele jej můžete kontaktovat. Přijde Vám i jí/jemu mail, který Vás vzájemně propojí. Domluvíte se potom už sami.<br>
<br>
Budete-li mít jakékoliv dotazy, pište na [email protected].<br>
Pojďme společně obnovit svět, kde si sousedé pomáhají.<br>
<br>
Váš tým Seniore
</html>'''
SendMail(GetEmail('noreplyMail'), dbUser.email,
'Seniore.org - ověření účtu', email_text)
return f'Uživatel {dbUser.first_name} {dbUser.surname} byl nastaven jako schválený a byl mu odeslán informační email.'
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 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 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 comment():
form = TextFormular()
if form.validate_on_submit():
dbUser = DBUser.LoadFromSession('dbUserRegistration')
dbUser.info = form.comment.data
dbUser.id = DBAccess.GetSequencerNextVal('users_id_seq')
dbUser.telephone2 = form.telephone.data
dbUser.InsertDB()
response = RenameImageToPrivate(session['cloudinaryId'],
(str(dbUser.id) + 'OP'))
imageUrl = response['url']
ts = URLSafeTimedSerializer(app.config["SECRET_KEY"])
token = ts.dumps(dbUser.id, salt='email-confirm-key')
confirm_url = url_for('login_bp.user_confirmation',
token=token,
_external=True)
SendMail(
GetEmail('noreplyMail'), GetEmail('adminMail'),
'Zaregistrován nový uživatel',
f'''<html>Nový uživatel zaregistrovan, čeká na schválení. <br>
<img src={GetImageUrl(dbUser.id)}>foto</img>
<br> <img src={imageUrl}>OP</img>
<br> jméno a příjmení: {dbUser.first_name} {dbUser.surname}
<br> email: {dbUser.email}
<br> telefon: {dbUser.telephone}
<br> adresa: {dbUser.street}, {dbUser.town}
<br> info: {dbUser.info}
<br> telefon na kontaktní osobu (seniora registruje někdo jiný): {dbUser.telephone2}
<br> Pro schválení uživatele klikněte na následující link {confirm_url} </html>'''
)
SendMail(
GetEmail('noreplyMail'), '*****@*****.**',
'Zaregistrován nový uživatel',
f'''<html>Nový uživatel zaregistrovan, čeká na schválení. <br>
<img src={GetImageUrl(dbUser.id)}>foto</img>
<br> <img src={imageUrl}>OP</img>
<br> jméno a příjmení: {dbUser.first_name} {dbUser.surname}
<br> email: {dbUser.email}
<br> telefon: {dbUser.telephone}
<br> adresa: {dbUser.street}, {dbUser.town}
<br> info: {dbUser.info},
<br> telefon na kontaktní osobu (seniora registruje někdo jiný): {dbUser.telephone2},
<br> Pro schválení uživatele klikněte na následující link {confirm_url} </html>'''
)
SendMail(
GetEmail('noreplyMail'), '*****@*****.**',
'Zaregistrován nový uživatel',
f'<html>Nový uživatel zaregistrovan, čeká na schválení. <br> <img src={GetImageUrl(dbUser.id)}>foto</img> <br> <img src={imageUrl}>OP</img> <br> údaje: {dbUser.__dict__} <br> Pro schválení uživatele klikněte na následující link {confirm_url}'
)
flash(
f'Registrace uživatele {dbUser.first_name} {dbUser.surname} úspěšně dokončena. Váš profil nyní musíme zkontrolovat. Zabere nám to maximálně 48 hodin. Prosíme, mějte strpení. Ruční ověřování považujeme za nezbytnost kvůli bezpečnosti. O schválení vás budeme informovat emailem.',
FlashStyle.Success)
SendMail(
GetEmail('noreplyMail'), dbUser.email, 'Registrace na Seniore.org',
'Děkujeme za vaši registraci na Seniore.org. Váš profil nyní musíme zkontrolovat. Zabere nám to maximálně 48 hodin. Prosíme, mějte strpení. Ruční ověřování považujeme za nezbytnost kvůli bezpečnosti. O schválení vás budeme informovat emailem. Děkujeme, tým Seniore.org'
)
return redirect(url_for("login_bp.login"))
return render_template("/registraceComment.html", form=form)
def remove_service():
id = request.args.get("id", type=int)
#check if there was argument
if (id is None):
abort(403)
#check if service belongs to logged user..
dbUser = DBUser.LoadFromSession('dbUser')
user_service = DBAccess.ExecuteScalar(
"select id from users_services where id = %s and id_users=%s",
(id, dbUser.id))
if (user_service is None):
abort(403)
#delete service
DBAccess.ExecuteUpdate("delete from users_services where id=%s", (id, ))
return redirect(url_for("profile_bp.profil"))
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 registration_email():
emailForm = EmailForm()
if emailForm.validate_on_submit():
sleep(3)
if request.form.getlist('conditionsAccept') != ['1', '2']:
flash(f'Je potřeba souhlasit s podmínkami.', FlashStyle.Danger)
return render_template("registrace_email.html", form=emailForm)
if DBAccess.ExecuteScalar(
'select id from users where trim(email) ilike %s',
(emailForm.email.data.strip(), )) is not None:
flash(
f'Uživatel {emailForm.email.data} je již zaregistrován, zvolte jiný email.',
FlashStyle.Danger)
emailForm.email.data = None
return render_template("registrace_email.html", form=emailForm)
else:
ts = URLSafeTimedSerializer(app.config["SECRET_KEY"])
token = ts.dumps(emailForm.email.data.strip(),
salt='email-confirm-key')
confirm_url = url_for('login_bp.email_confirmation',
token=token,
_external=True)
email_text = f'Prosím klikněte na následující odkaz pro ověření vašeho emailu a pokračování v registraci.<br>Tento odkaz bude platný následujících 24 hodin.<br>{confirm_url} <br> Pokud odkaz nefunguje, prosíme, je nutné ho zkopírovat a celý vložit do vašeho prohlížeče.'
SendMail(GetEmail('noreplyMail'), emailForm.email.data.strip(),
'Seniore.org - ověření emailu', email_text)
#flash("Na zadanou adresu byl odeslán email s odkazem na pokračování v registraci.",FlashStyle.Success)
emailForm.submit.label.text = "Odeslat ověřovací email znovu"
return render_template("registrace_email2.html", form=emailForm)
return render_template("registrace_email.html", form=emailForm)
def lost_password():
emailForm = EmailForm()
if emailForm.validate_on_submit():
sleep(3)
if DBAccess.ExecuteScalar('select id from users where email ilike %s',
(emailForm.email.data, )) is None:
flash(
f'Uživatel {emailForm.email.data} nebyl nalezen, zvolte jiný email.',
FlashStyle.Danger)
emailForm.email.data = None
return render_template("registrace_email.html", form=emailForm)
else:
ts = URLSafeTimedSerializer(app.config["SECRET_KEY"])
token = ts.dumps(emailForm.email.data, salt='email-renew-key')
confirm_url = url_for('login_bp.new_password',
token=token,
_external=True)
email_text = f'Prosím klikněte na následující odkaz pro zadání nového hesla.<br>Tento odkaz bude platný následujících 24 hodin.<br>{confirm_url}'
SendMail(GetEmail('noreplyMail'), emailForm.email.data,
'Seniore.cz - obnova zapomenutého hesla', email_text)
flash(
"Na zadanou adresu byl odeslán email s odkazem na obnovu hesla.",
FlashStyle.Success)
emailForm.submit.label.text = "Odeslat email znovu"
return render_template("lost_password.html", form=emailForm)
return render_template("lost_password.html", form=emailForm)
def registrace():
form = RegistrationForm()
if form.validate_on_submit():
if (form.password.data != form.passwordAgain.data):
flash('Hesla nejsou stejná.', FlashStyle.Danger)
email = session['confirmed_email']
form.email.data = email
return render_template("registrace.html", form=form)
email = session['confirmed_email']
form.email.data = email
dbUser = DBUser()
dbUser.email = form.email.data
dbUser.password = form.password.data
dbUser.level = 0
if DBAccess.ExecuteScalar('select id from users where email=%s',
(dbUser.email, )) is not None:
flash(
f'Uživatel {dbUser.email} je již zaregistrován, zvolte jiný email.',
FlashStyle.Danger)
dbUser.email = None
form.email.data = None
return render_template("registrace.html", form=form)
dbUser.salt = salt = DBAccess.ExecuteScalar("select salt()")
#md% tranform password use md5 function on password + salt
# md5Pass = hashlib.md5((dbUser.password+dbUser.salt).encode()).hexdigest()
# dbUser.password = md5Pass
bcrypt = Bcrypt()
dbUser.password = bcrypt.generate_password_hash(
dbUser.password).decode('UTF-8')
dbUser.SaveToSession('dbUserRegistration')
return redirect(url_for("login_bp.registrace_name"))
#email = session.pop('confirmed_email',None)
email = session.get('confirmed_email')
if (email is None):
abort(403)
form.email.data = email
return render_template("registrace.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 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 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 registrace_address():
form = RegistrationFormAddress()
if form.validate_on_submit():
dbUser = DBUser.LoadFromSession('dbUserRegistration')
dbUser.town = form.town.data
dbUser.street = form.street.data
# dbUser.street_number = form.street_number.data
dbUser.post_code = form.post_code.data
kwargs = dbUser.__dict__
address = "{} {} {}".format(kwargs["street"], kwargs["town"],
kwargs["post_code"])
# address = "{} {} {} {}".format(kwargs["street"], kwargs["street_number"], kwargs["town"], kwargs["post_code"])
coordinates = GetCoordinates(address)
if (coordinates is not None):
# dbUser.latitude = coordinates[0]
# dbUser.longitude = coordinates[1]
dbUser.latitude = round(coordinates[0], 5)
dbUser.longitude = round(coordinates[1], 5)
x = 1
y = 1
difference = 0.00001
originalLatitude = dbUser.latitude
originalLongitue = dbUser.longitude
#check if same coordinates already exists
while DBAccess.ExecuteScalar(
'select id from users where latitude=%s and longitude=%s',
(
dbUser.latitude,
dbUser.longitude,
)) is not None:
#if exists add difference and try again and again..
dbUser.latitude = originalLatitude + x * difference
dbUser.longitude = originalLongitue + y * difference
if x != -1:
x -= 1
elif y != -1:
y -= 1
else:
x = 1
y = 1
difference += 0.00001
else:
flash('Nenalezeny souřadnice pro vaši adresu', FlashStyle.Danger)
return render_template("registrace_address.html", form=form)
dbUser.SaveToSession('dbUserRegistration')
#return redirect(url_for("login_bp.photo"))
return redirect(url_for("login_bp.registrace_idCard"))
return render_template("registrace_address.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 new_password(token):
try:
ts = URLSafeTimedSerializer(app.config["SECRET_KEY"])
email = ts.loads(token, salt="email-renew-key", max_age=86400)
except:
abort(403)
form = NewPasswordForm()
if (form.validate_on_submit()):
if (form.password.data != form.passwordAgain.data):
flash('Hesla nejsou stejná.', FlashStyle.Danger)
return render_template('new_password.html', form=form, email=email)
#salt = DBAccess.ExecuteScalar("select salt()")
#md5Pass = hashlib.md5((form.password.data+salt).encode()).hexdigest()
bcrypt = Bcrypt()
bcryptHash = bcrypt.generate_password_hash(
form.password.data).decode('UTF -8')
DBAccess.ExecuteUpdate(
'update users set password=%s where email like %s',
(bcryptHash, email))
flash('Nové heslo nastaveno, nyní se zkuste přihlásit.',
FlashStyle.Success)
return redirect(url_for('login_bp.login'), )
return render_template('new_password.html', form=form, email=email)
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 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)
def sluzby_delete():
DBAccess.ExecuteUpdate('delete from users_services where id_users = %s',(session['id_user'],))
flash('''Byly smazány všechny vaše poskytované/poptávané služby a proto budete skryti na mapě dobrovolníků/seniorů.
Pro znovuzobrazení na mapě stačí kliknout na "Zobrazit mě ostatním" a přidat nějakou službu.''',FlashStyle.Success)
return redirect(url_for("overview_bp.prehled_all"))
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 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)
class TestApp(TestCase):
@classmethod
def setUpClass(self):
self.engine = create_engine("sqlite:///:memory:")
db_session = scoped_session(
sessionmaker(autocommit=False, autoflush=False, bind=self.engine))
self.acc = DBAccess(db_session)
init_db(engine=self.engine)
def testQueryResort(self):
res = Resort()
res.name = "Test Query Resort"
res.id = 50
self.acc.insertData([res])
self.assertEqual(self.acc.queryResort(50).name, "Test Query Resort")
def testQueryTrail(self):
trail = Trail()
trail.name = "Test Query Trail"
trail.id = 50
self.acc.insertData([trail])
self.assertEqual(self.acc.queryTrail(50).name, "Test Query Trail")
def testQueryPhoto(self):
photo = Photo()
photo.name = "Test Query Photo"
photo.id = 50
photo.trailid = 50
self.acc.insertData([photo])
self.assertEqual(self.acc.queryPhoto("Test Query Photo").id, 50)
self.assertEqual(self.acc.queryPhoto("Test Query Photo").trailid, 50)
def testInsertMerge1(self):
res = Resort()
res.name = "Test Insert Resort"
res.id = 1
res2 = Resort()
res2.name = "Test Insert Resort Copy"
res2.id = 1
self.acc.insertData([res, res2])
self.assertEqual(
self.acc.queryResort(1).name, "Test Insert Resort Copy")
def testInsertMerge2(self):
res = Resort()
res.name = "Test Insert Resort"
res.id = 2
t1 = Trail()
t1.id = 1
t2 = Trail()
t2.id = 1
res.trails.append(t1)
res.trails.append(t2)
self.acc.insertData([res])
self.assertIsNone(self.acc.queryResort(2))
def testRelationships(self):
res = Resort()
res.name = "Test Resort"
res.id = 123
trail = Trail()
trail.name = "Test Trail"
trail.id = 222
photo = Photo()
photo.name = "Test Trail photo"
photo.id = 222
photo.trail = trail
trail.photos.append(photo)
res.trails.append(trail)
res.photos.append(photo)
self.acc.insertData([res])
self.assertEqual(self.acc.queryResort(123).name, "Test Resort")
self.assertEqual(
self.acc.queryResort(123).trails[0].name, "Test Trail")
self.assertEqual(
self.acc.queryResort(123).photos[0].name, "Test Trail photo")
self.assertEqual(
self.acc.queryTrail(222).photos[0].name, "Test Trail photo")
def __init__(self):
# Connexion a la BD
self.db = DBAccess()
# On charge la configuration actuelle
self.config = Config()
def senior_registration():
form = SeniorRegistrationForm()
if (form.validate_on_submit()):
if form.password.data != form.passwordAgain.data:
flash('Hesla nejsou totožná!', FlashStyle.Danger)
return render_template('senior_registration.html', form=form)
if DBAccess.ExecuteScalar(
'select id from users where trim(email) ilike %s',
(form.email.data.strip(), )) is not None:
flash(
f'Uživatel {form.email.data} je již zaregistrován, zvolte jiný email.',
FlashStyle.Danger)
form.email.data = None
return render_template('senior_registration.html', form=form)
dbUser = DBUser()
dbUser.first_name = form.first_name.data
dbUser.surname = form.surname.data
dbUser.email = form.email.data
dbUser.telephone = form.telephone.data
dbUser.telephone2 = form.telephone2.data
dbUser.comment = form.comment.data
dbUser.street = form.street.data
dbUser.street_number = form.street_number.data
if (form.street_number.data == ''):
dbUser.street_number = 1
dbUser.town = form.town.data
dbUser.post_code = form.post_code.data
dbUser.password = form.password.data
bcrypt = Bcrypt()
dbUser.password = bcrypt.generate_password_hash(
dbUser.password).decode('UTF-8')
dbUser.level = 1
address = '{} {} {}'.format(dbUser.street, dbUser.town,
dbUser.post_code)
coordinates = GetCoordinates(address)
if (coordinates is not None):
dbUser.latitude = round(coordinates[0], 5)
dbUser.longitude = round(coordinates[1], 5)
x = 1
y = 1
difference = 0.00001
originalLatitude = dbUser.latitude
originalLongitue = dbUser.longitude
#check if same coordinates already exists
while DBAccess.ExecuteScalar(
'select id from users where latitude=%s and longitude=%s',
(
dbUser.latitude,
dbUser.longitude,
)) is not None:
#if exists add difference and try again and again..
dbUser.latitude = originalLatitude + x * difference
dbUser.longitude = originalLongitue + y * difference
if x != -1:
x -= 1
elif y != -1:
y -= 1
else:
x = 1
y = 1
difference += 0.00001
else:
flash('Nenalezeny souřadnice pro vaši adresu', FlashStyle.Danger)
return render_template('senior_registration.html', form=form)
dbUser.salt = salt = DBAccess.ExecuteScalar("select salt()")
dbUser.id = DBAccess.GetSequencerNextVal('users_id_seq')
dbUser.InsertDB()
flash(
f'Senior {dbUser.first_name} {dbUser.surname} email: {dbUser.email} vložen do databáze a nastaven jako ověřený.',
FlashStyle.Success)
return redirect(url_for("login_bp.login"))
return render_template('senior_registration.html', form=form)
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 setUpClass(self):
self.engine = create_engine("sqlite:///:memory:")
db_session = scoped_session(
sessionmaker(autocommit=False, autoflush=False, bind=self.engine))
self.acc = DBAccess(db_session)
init_db(engine=self.engine)
class Crisis:
DELAY = 10
def __init__(self):
# Connexion a la BD
self.db = DBAccess()
# On charge la configuration actuelle
self.config = Config()
def run(self):
try:
while True:
# Les conditions de crises sont prises de la configuration
maxCPU = self.config.get("crisis/max_cpu")
maxRAM = self.config.get("crisis/max_ram")
maxSwap = self.config.get("crisis/max_swap")
maxDisk = self.config.get("crisis/max_disk")
# On cherche les serveurs dans la BDD
for s in self.db.get_all("server"):
server = Server(s["name"], s["ip"], s["uptime"])
# Obtention de la date la plus actuelle
lastDate = self.db.get_last_date(server.name).next()[0]
# Obtention des stats
res = self.db.get_by_fields("stat",
["server_name", "timestamp"],
[server.name, lastDate]).next()
resDisks = self.db.get_by_fields("statDisk",
["server_name", "timestamp"],
[server.name, lastDate])
# Création d'objets à partir des données
cpu = ArrayDataObject();
cpu.used = float(res["cpu_used"])
ram = ArrayDataObject();
ram.total = int(res["ram_total"]);
ram.used = float(res["ram_used"])
swap = ArrayDataObject();
swap.total = int(res["swap_total"]);
swap.used = float(res["swap_used"])
disks = []
for line in resDisks:
d = ArrayDataObject()
d.mnt = line["mnt"]
d.total = int(line["total"])
d.used = float(line["used"])
disks.append(d)
# Détection de situation de crise
ramPercent = ram.used * 100 / ram.total
swapPercent = ram.used * 100 / ram.total
disksTotalUsed = 0
disksTotal = 0
for d in disks:
disksTotalUsed += d.used
disksTotal += d.total
disksPercent = disksTotalUsed * 100 / disksTotal
if (cpu.used >= float(maxCPU)
or ramPercent >= float(maxRAM)
or swapPercent >= float(maxSwap)
or disksPercent >= float(maxDisk)):
# Classe pour envoyer des mails
mail = Mail()
mail.set_param("server.name", server.name)
mail.set_param("server.ip", server.ip)
mail.set_param("cpu.used", "{:.1f}".format(cpu.used))
mail.set_param("ram.percent", "{:.1f}".format(ramPercent))
mail.set_param("swap.percent", "{:.1f}".format(swapPercent))
mail.set_param("disks.percent", "{:.1f}".format(disksPercent))
mail.send(self.config.get("email/address"), self.config.get("email/subject"),
self.config.get("email/template_html"), self.config.get("email/template_txt"))
print "Crise détectée. Un email a été envoyé à " + self.config.get("email/address")
# Fin for
time.sleep(self.DELAY)
# Recharge de la configuration
self.config.recharger()
# Fin while
except Exception, e:
raise