128 lines
4.6 KiB
Python
128 lines
4.6 KiB
Python
# import the library and its Marker clusterization service
|
|
import folium
|
|
from models.database import SessionLocal
|
|
from folium.plugins import MarkerCluster
|
|
from models.models import Base, Members, Payments, Deliveries
|
|
import logging
|
|
from geopy.geocoders import Nominatim
|
|
from sqlalchemy import *
|
|
import logging
|
|
import webbrowser
|
|
import re
|
|
from config_loader import load_config
|
|
|
|
def payment_slip_delivered(member: Members, year: int):
|
|
return db.query(Deliveries.delivery_date)\
|
|
.filter(Deliveries.member_id == member.id, extract('year', Deliveries.delivery_date) == year)\
|
|
.count()
|
|
|
|
def payment_made(member: Members, year: int):
|
|
return db.query(Payments.payment_year)\
|
|
.filter(Payments.member_id == member.id, extract('year', Payments.payment_year) == year)\
|
|
.count()
|
|
|
|
def update_missing_locs():
|
|
members = db.query(Members).filter(Members.member_geo_latitude == None).all()
|
|
|
|
for member in members:
|
|
address = f'{member.member_street} {member.member_streetnumber}, {member.member_zip} {member.member_city}, Austria'
|
|
location = geolocator.geocode(address)
|
|
|
|
if location != None:
|
|
#member.member_coordinates = [location.latitude, location.longitude]
|
|
member.member_geo_latitude = location.latitude
|
|
member.member_geo_longitude = location.longitude
|
|
|
|
logger.info(f'Member: {member.id} updated with geo-coordinates')
|
|
else:
|
|
logger.info(f'Member: {member.id} address couldn\'t be found')
|
|
|
|
db.commit()
|
|
|
|
def checkEmail(email):
|
|
regex = r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,7}\b'
|
|
|
|
if email is None: return False
|
|
|
|
# pass the regular expression
|
|
# and the string into the fullmatch() method
|
|
if(re.fullmatch(regex, email)):
|
|
return True
|
|
|
|
else:
|
|
return False
|
|
|
|
cfg = load_config()
|
|
year = cfg['year']
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
geolocator = Nominatim(user_agent="sust", timeout=10)
|
|
#geolocator = Bing(api_key='ApbTfEJ024nQCu6DC7TPIlqAcRMfZT8lPjAyMnVRcmo9rqxde5x-IS4fW-nGu0FI')
|
|
|
|
db = SessionLocal()
|
|
#models.Base.metadata.create_all(bind=engine)
|
|
|
|
update_missing_locs()
|
|
|
|
#members = db.query(models.Members).filter(models.Members.id.in_([2,4,5,6])).all()
|
|
|
|
#TODO try
|
|
""" members = Members.query.all()
|
|
for member in members:
|
|
for delivery in members.deliveries:
|
|
print(f'{delivery.delivery_date}') """
|
|
|
|
members = db.query(Members.member_geo_latitude,
|
|
Members.member_geo_longitude,
|
|
Members.member_firstname,
|
|
Members.member_surname,
|
|
Members.id,
|
|
Members.member_email)\
|
|
.filter(Members.member_geo_latitude != None, Members.member_count != 0)\
|
|
.all()
|
|
|
|
# .join(models.Members.member_deliveries)\
|
|
# Create a map object and center it to the avarage coordinates to m
|
|
coords = [48.284793, 13.880550]
|
|
map = folium.Map(location=coords, zoom_start = 13)
|
|
|
|
# if the points are too close to each other, cluster them, create a cluster overlay with MarkerCluster, add to m
|
|
marker_cluster = MarkerCluster().add_to(map)
|
|
|
|
# draw the markers and assign popup and hover texts
|
|
# add the markers the the cluster layers so that they are automatically clustered
|
|
#folium.Marker([lat, lon], popup=str(name)+': '+color+'-'+str(clname), icon=folium.Icon(color='black',icon_color='#FFFF00')).add_to(feature_group)
|
|
for member in members:
|
|
if member.member_geo_latitude != None and member.member_geo_latitude != None:
|
|
delivered = payment_slip_delivered(member, year)
|
|
paid = payment_made(member, year)
|
|
|
|
iconColor = "blue"
|
|
hasMail = checkEmail(member.member_email)
|
|
if not paid:
|
|
iconColor = "red"
|
|
elif delivered:
|
|
iconColor = "green"
|
|
elif hasMail:
|
|
iconColor = "orange"
|
|
|
|
# if hasMail and delivered:
|
|
# iconColor = "darkgreen"
|
|
|
|
#delivered = member.member_deliveries
|
|
location = (member.member_geo_latitude, member.member_geo_longitude)
|
|
folium.Marker(location=location,
|
|
popup=f'{member.member_firstname} {member.member_surname}',
|
|
tooltip=f'\nZugestellt & bezahlt' if (delivered and paid) else
|
|
'Nicht zugestellt' if not delivered else
|
|
'Zugestellt, aber nicht bezahlt' if delivered and not paid else
|
|
'Weder zugestellt, noch bezahlt',
|
|
icon=folium.Icon(color=iconColor))\
|
|
.add_to(marker_cluster)
|
|
|
|
#Display the map
|
|
map.save("map.html")
|
|
webbrowser.open("map.html")
|
|
|
|
#func.count(models.Members.member_deliveries).label("count"))\ |