def __init__(self, app):
self.app = app
self.baseJS = app.baseJS
self.stellen = self.baseJS.get("gps").get("nachkommastellen")
self.getSSheet()
self.spreadsheet_id = self.baseJS.get("spreadsheet_id")
sheet_basename = self.baseJS.get(
"db_tabellenname") # gleicher Name wie DB
self.sheet_names = [
sheet_basename + "_daten", sheet_basename + "_images"
]
if self.baseJS.get("zusatz") is not None:
self.sheet_names.append(sheet_basename + "_zusatz")
self.ist_headers = {}
self.soll_headers = {}
self.checkSheets()
for sheet_name in self.sheet_names:
try:
headers = self.ssheet.values().get(
spreadsheetId=self.spreadsheet_id,
range=sheet_name + "!1:1").execute().get('values', [])
self.ist_headers[sheet_name] = headers if len(
headers) == 0 else headers[0]
except Exception as e:
utils.printEx(
"Kann Arbeitsblatt " + self.spreadsheet_id + "/" +
sheet_name + " nicht laden", e)
raise (e)
self.checkColumns()
def checkBoxEvent(self, x):
fnr = self.fnr
tag = self.tag.name
tag = utils.num2Tag(tag)
# print("checkBoxEvent state", x.state, "active", x.active, "Famnr", fnr, "Tag", tag)
try:
with conn:
c = conn.cursor()
r1 = c.execute(
"UPDATE arbeitsblatt set kh = ? where tag=? and fnr=?",
(int(x.active), tag, fnr))
if r1.rowcount == 0: # row did not yet exist
vals = {
"tag": tag,
"fnr": fnr,
"einsatzstelle": "",
"beginn": "",
"ende": "",
"fahrtzeit": "",
"mvv_euro": "",
"kh": int(x.active)
}
c.execute(
"INSERT INTO arbeitsblatt VALUES(:tag,:fnr,:einsatzstelle,:beginn,:ende,:fahrtzeit,:mvv_euro,:kh)",
vals)
except Exception as e:
utils.printEx("fam0:", e)
def insert_daten_from_osm(self,
values): # values = { [lat,lon]: properties }
conn = self.getConn()
colnames = self.colnames["daten"]
kcolnames = [":" + k for k in colnames]
# now = time.strftime("%Y.%m.%d %H:%M:%S")
for value in values.items():
lon = value[0][0]
lat = value[0][1]
lat_round = str(round(lat, self.stellen))
lon_round = str(round(lon, self.stellen))
value = value[1]
vals = {}
for col in colnames[7:]:
vals[col] = None
vals["lat"] = lat
vals["lon"] = lon
vals["lat_round"] = lat_round
vals["lon_round"] = lon_round
vals["creator"] = "OSM"
vals["created"] = "OSM"
vals["modified"] = "OSM"
vals.update(value)
try:
with conn:
c = conn.cursor()
c.execute(
"INSERT INTO " + self.tabellenname + "_daten VALUES(" +
",".join(kcolnames) + ")", vals)
except sqlite3.IntegrityError as e:
print("duplicate", vals)
utils.printEx("insert_daten_from_osm:", e)
def famEvent(self, x):
fnr = self.fnr
tag = self.tag.name
tag = utils.num2Tag(tag)
# print("famEvent", x.text, "Feld", x.name, "Famnr", fnr, "Tag", tag)
x.normalize()
try:
with conn:
c = conn.cursor()
r1 = c.execute(
"UPDATE arbeitsblatt set " + x.name +
" = ? where tag=? and fnr=?", (x.text, tag, fnr))
if r1.rowcount == 0: # row did not yet exist
vals = {
"tag": tag,
"fnr": fnr,
"einsatzstelle": "",
"beginn": "",
"ende": "",
"fahrtzeit": "",
"mvv_euro": "",
"kh": 0
}
vals[x.name] = x.text
c.execute(
"INSERT INTO arbeitsblatt VALUES(:tag,:fnr,:einsatzstelle,:beginn,:ende,:fahrtzeit,:mvv_euro,:kh)",
vals)
except Exception as e:
utils.printEx("fam0:", e)
def insert_image(self, filename, lat, lon):
lat_round = str(round(lat, self.stellen))
lon_round = str(round(lon, self.stellen))
now = time.strftime("%Y.%m.%d %H:%M:%S")
try:
conn = self.getConn()
with conn:
c = conn.cursor()
vals = {
"creator": self.aliasname,
"created": now,
"lat": lat,
"lon": lon,
"lat_round": lat_round,
"lon_round": lon_round,
"image_path": filename,
"image_url": None
}
colnames = self.colnames["images"]
kcolnames = [":" + k for k in colnames]
c.execute(
"INSERT INTO " + self.tabellenname + "_images VALUES(" +
",".join(kcolnames) + ")", vals)
self.app.add_marker(lat, lon) # TODO weg
except Exception as e:
utils.printEx("insert_image:", e)
def update_daten(self, name, text, lat, lon):
lat_round = str(round(lat, self.stellen))
lon_round = str(round(lon, self.stellen))
now = time.strftime("%Y.%m.%d %H:%M:%S")
try:
conn = self.getConn()
with conn:
c = conn.cursor()
r1 = c.execute(
"UPDATE " + self.tabellenname + "_daten set " +
"creator = ?, modified = ?, " + name +
" = ? where lat_round = ? and lon_round = ?",
(self.aliasname, now, text, lat_round, lon_round))
if r1.rowcount == 0: # row did not yet exist
vals = {
"creator": self.aliasname,
"created": now,
"modified": now,
"lat": lat,
"lon": lon,
"lat_round": lat_round,
"lon_round": lon_round
}
colnames = self.colnames["daten"]
for col in colnames[7:]:
vals[col] = None
vals[name] = text
kcolnames = [":" + k for k in colnames]
c.execute(
"INSERT INTO " + self.tabellenname + "_daten VALUES(" +
",".join(kcolnames) + ")", vals)
self.app.add_marker(lat, lon) # TODO weg
except Exception as e:
utils.printEx("update_daten:", e)
def update_imagepath(self, old_image_path, new_image_path, new_image_url,
lat_round, lon_round):
try:
conn = self.getConn()
with conn:
c = conn.cursor()
c.execute(
"UPDATE " + self.tabellenname +
"_images set image_path = ?, image_url = ? where " +
"lat_round=? and lon_round=? and image_path=?",
(new_image_path, new_image_url, lat_round, lon_round,
old_image_path))
except Exception as e:
utils.printEx("insert_image:", e)
def clear(self):
self.ids.einsatzstelle.text = ""
self.ids.beginn.text = ""
self.ids.ende.text = ""
self.ids.fahrtzeit.text = ""
self.ids.mvv_euro.text = ""
self.ids.kh.active = 0
tag = self.tag.name
tag = utils.num2Tag(tag)
try:
with conn:
c = conn.cursor()
r1 = c.execute("DELETE FROM arbeitsblatt where tag=?", (tag, ))
except Exception as e:
utils.printEx("fam0:", e)
def get_user_info(self, creds):
"""Send a request to the UserInfo API to retrieve the user's information.
Args:
credentials: oauth2client.client.OAuth2Credentials instance to authorize the
request.
Returns:
User information as a dict.
"""
user_info_service = build('oauth2', 'v2', credentials=creds)
try:
user_info = user_info_service.userinfo().get().execute()
if user_info and user_info.get('id'):
return user_info
except Exception as e:
utils.printEx('cannot get user info', e)
raise (e)
return None
def show_markers(self, fromSheets, *args):
with Spinner():
clat = self.mapview.centerlat
clon = self.mapview.centerlon
# roughly a square on the map
delta = float(self.getConfigValue("delta", "5")) / 1000.0
minlat = clat - delta
maxlat = clat + delta
minlon = clon - 2 * delta
maxlon = clon + 2 * delta
for k in list(self.markerMap.keys()):
# lat, lon = k.split(":")
# lat = float(lat)
# lon = float(lon)
# if not (minlat < lat < maxlat and minlon < lon < maxlon):
# markerOld = self.markerMap.get(k)
# self.mapview.remove_marker(markerOld)
# del self.markerMap[k]
markerOld = self.markerMap.get(k)
self.mapview.remove_marker(markerOld)
del self.markerMap[k]
if fromSheets:
if self.useGoogle:
self.message("Lade Daten von Google Sheets")
try:
sheetValues = self.gsheet.getValuesWithin(minlat, maxlat, minlon, maxlon)
self.dbinst.fillWithSheetValues(sheetValues)
except Exception as e:
self.message("Konnte Daten nicht von Google Sheets laden: " + str(e))
raise (e)
else:
self.message("Lade Daten vom LocationsServer")
try:
dbValues = self.serverIntf.getValuesWithin(minlat, maxlat, minlon, maxlon)
self.dbinst.fillWithDBValues(dbValues)
except Exception as e:
utils.printEx("Konnte Daten nicht vom LocationsServer laden", e)
self.message("Konnte Daten nicht vom LocationsServer laden: " + str(e))
raise (e)
self.message("Lade Map Marker")
markers = self.dbinst.getMarkerLocs(minlat, maxlat, minlon, maxlon)
self.show_markers2(markers)
def vorschlag1(self, t):
t = int(t)
tag = utils.num2Tag(t)
wt = utils.num2WT(t)
if self.app.dokorrektur or wt == "Sa" or wt == "So":
return (tag, 1, "", "", "", "", "", 0)
try:
with conn:
c = conn.cursor()
for tv in range(t - 1, t - 5, -1):
wtv = utils.num2WT(tv)
if wtv == "Sa" or wtv == "So":
continue
tvtag = utils.num2Tag(tv)
c.execute(
"SELECT einsatzstelle, beginn, ende, fahrtzeit, mvv_euro, kh"
" from arbeitsblatt WHERE tag = ? and fnr = 1",
(tvtag, ))
r = c.fetchmany(2)
r = utils.elimEmpty(r, 0)
if len(r) == 0:
break
elif len(r) == 1:
if r[0][0].lower() not in utils.skipES:
vals = r[0]
if self.app.menu.ids.wochenstunden.text == "38,5": # übertrieben?
if wt == "Fr":
vals = (vals[0], "08:00", "15:00",
*vals[3:])
elif wtv == "Fr":
vals = (vals[0], "08:00", "16:30",
*vals[3:])
self.parent.vorschlagsTag = tvtag
return (tag, 1, *vals)
else:
raise ValueError(
"mehr als ein Eintrag für Tag {}, Fnr 1".format(
tag))
except Exception as e:
utils.printEx("fam1:", e)
tag = utils.num2Tag(t)
return (tag, 1, "", "", "", "", "", 0)
def menuEvent(self, x):
x.normalize()
try:
with conn:
c = conn.cursor()
r1 = c.execute("UPDATE eigenschaften set " + x.name + " = ?",
(x.text, ))
if r1.rowcount == 0: # row did not yet exist
vals = {
"vorname": "",
"nachname": "",
"wochenstunden": "",
"emailadresse": ""
}
vals[x.name] = x.text
r2 = c.execute(
"INSERT INTO eigenschaften VALUES(:vorname,:nachname,:wochenstunden,:emailadresse)",
vals)
except Exception as e:
utils.printEx("main1:", e)
if x.name == "wochenstunden":
self.setWtagStunden(x.text)
def update_zusatz(self, nr, name, text, lat, lon):
now = time.strftime("%Y.%m.%d %H:%M:%S")
rowid = nr
try:
conn = self.getConn()
with conn:
c = conn.cursor()
if nr:
r1 = c.execute(
"UPDATE " + self.tabellenname + "_zusatz set " +
"creator = ?, modified = ?, " + name +
" = ? where nr = ?", (self.aliasname, now, text, nr))
if not nr or r1.rowcount == 0: # row did not yet exist
lat_round = str(round(lat, self.stellen))
lon_round = str(round(lon, self.stellen))
vals = {
"nr": None,
"creator": self.aliasname,
"created": now,
"modified": now,
"lat": lat,
"lon": lon,
"lat_round": lat_round,
"lon_round": lon_round
}
colnames = self.colnames["zusatz"]
for col in colnames[8:]:
vals[col] = None
vals[name] = text
kcolnames = [":" + k for k in colnames]
c.execute(
"INSERT INTO " + self.tabellenname +
"_zusatz VALUES(" + ",".join(kcolnames) + ")", vals)
rowid = c.lastrowid
self.app.add_marker(lat, lon) # TODO weg
return rowid
except Exception as e:
utils.printEx("update_zusatz:", e)
def fillin(self, t, fnr, app):
self.app = app
tag = utils.num2Tag(t)
if fnr == 1:
self.parent.vorschlagsTag = None
try:
with conn:
c = conn.cursor()
c.execute(
"SELECT einsatzstelle, beginn, ende, fahrtzeit, mvv_euro, kh"
" from arbeitsblatt WHERE tag = ? and fnr = ?", (tag, fnr))
r = c.fetchmany(2)
r = utils.elimEmpty(r, 0)
if len(r) == 0:
if int(t) < 2:
vals = self.vorschlag1(
t) if fnr == 1 else self.vorschlag23(t, fnr)
if vals[2] != "" or vals[3] != "" or vals[
4] != "" or vals[5] != "" or vals[6] != "":
c.execute(
"INSERT INTO arbeitsblatt VALUES(?,?,?,?,?,?,?,?)",
vals)
else:
vals = (tag, fnr, "", "", "", "", "", 0)
elif len(r) == 1:
vals = (tag, fnr, *r[0])
else:
raise ValueError(
"mehr als ein Eintrag für Tag {}, Fnr {}".format(
tag, fnr))
self.ids.einsatzstelle.text = vals[2]
self.ids.beginn.text = vals[3]
self.ids.ende.text = vals[4]
self.ids.fahrtzeit.text = vals[5]
self.ids.mvv_euro.text = vals[6]
self.ids.kh.active = vals[7]
except Exception as e:
utils.printEx("fam3:", e)
def init(self):
try:
with conn:
c = conn.cursor()
c.execute(
"SELECT vorname, nachname, wochenstunden, emailadresse from eigenschaften"
)
r = c.fetchmany(2)
r = utils.elimEmpty(r, 1)
if len(r) == 0:
vals = ("", "", "", "")
elif len(r) == 1:
vals = r[0]
else:
raise ValueError("mehr als ein Eintrag für eigenschaften")
self.ids.vorname.text = vals[0]
self.ids.nachname.text = vals[1]
wochenstunden = vals[2]
self.setWtagStunden(wochenstunden)
self.ids.wochenstunden.text = vals[2]
self.ids.emailadresse.text = vals[3]
except Exception as e:
utils.printEx("main0:", e)
def __init__(self):
configDir = utils.getDataDir()
self.configs = {}
self.errors = []
for dir in set([configDir, "."]):
self.file_list = sorted(glob.glob(dir + "/config/*.json"))
for f in self.file_list:
try:
with open(f, "r", encoding="UTF-8") as jsonFile:
confJS = json.load(jsonFile)
try:
self.checkSyntax(confJS, syntax)
except Exception as e:
utils.printEx("Kann Datei " + f + " nicht parsen:",
e)
self.errors.append("Kann Datei " + f +
" nicht parsen:" + str(e))
continue
nm = confJS.get("name")
self.configs[nm] = confJS
print("gelesen:", f, nm)
except Exception as e:
utils.printEx("Fehler beim Lesen von " + f, e)
def vorschlag23(self, t, fnr):
tag = utils.num2Tag(t)
tvtag = self.parent.vorschlagsTag
if tvtag is None:
return (tag, fnr, "", "", "", "", "", 0)
try:
with conn:
c = conn.cursor()
c.execute(
"SELECT einsatzstelle, beginn, ende, fahrtzeit, mvv_euro, kh"
" from arbeitsblatt WHERE tag = ? and fnr = ?",
(tvtag, fnr))
r = c.fetchmany(2)
r = utils.elimEmpty(r, 0)
if len(r) == 1:
vals = r[0]
return (tag, fnr, *vals)
elif len(r) > 1:
raise ValueError(
"mehr als ein Eintrag für Tag {}, Fnr {}".format(
tag, fnr))
except Exception as e:
utils.printEx("fam2:", e)
return (tag, fnr, "", "", "", "", "", 0)
def checkComplete(self):
rows = []
try:
with conn:
c = conn.cursor()
c.execute(
"SELECT tag,fnr,einsatzstelle,beginn,ende,fahrtzeit,mvv_euro,kh "
"from arbeitsblatt WHERE tag like ?",
("__." + self.month, ))
while True:
r = c.fetchmany(100)
r = utils.elimEmpty(r, 2)
if len(r) == 0:
break
rows.extend(r)
except Exception as e:
utils.printEx("arbex0:", e)
rows.sort(key=lambda xr: xr[0] + str(xr[
1])) # sort by tag and fnr, i.e. 01.01.20, 02.01.20,...,31.01.20
lastFnr = 0
lastTnr = 999
nrows = []
for row in rows:
# print("row", row)
if row[R_Einsatzstelle] == "" and row[R_Beginn] == "" and row[
R_Ende] == "":
continue
self.tag = row[R_Tag]
tnr = utils.tag2Nummer(self.tag)
wday = utils.day2WT(self.tag)
if wday == "Sa" or wday == "So":
continue
dayMiss = fnrMiss = False
if lastTnr != 999 and tnr > lastTnr + 1:
if wday != "Mo":
self.tag = utils.num2Tag(lastTnr + 1)
wday = utils.day2WT(self.tag)
dayMiss = True
fnr = row[R_Fnr]
if lastTnr != tnr:
if fnr != 1:
fnrMiss = True
elif fnr != lastFnr + 1:
fnrMiss = True
lastTnr = tnr
lastFnr = fnr
if dayMiss or fnrMiss or (row[R_Einsatzstelle] == ""
or row[R_Beginn] == ""
or row[R_Ende] == ""):
dia = MDDialog(size_hint=(.8, .4),
title="Daten unvollständig",
text="Bitte Daten von " + wday + ", " +
self.tag + " vervollständigen",
text_button_ok="OK",
events_callback=self.evcb)
dia.open()
return None
text = self.checkRow(row)
if text is not None:
dia = MDDialog(size_hint=(.8, .4),
title="Daten falsch",
text=text + ", bitte Daten von " + wday + ", " +
self.tag + " ausbessern",
text_button_ok="OK",
events_callback=self.evcb)
dia.open()
return None
nrows.append(row)
# print("Vollständig!")
return nrows
conn = self.getConn()
c = conn.cursor()
for feld in removedFields:
stmt = "ALTER TABLE " + self.tabellenname + suffix + " DROP " + feld["name"]
c.execute(stmt)
conn.commit()
class App:
def __init__(self):
self.baseConfig = config.Config()
if __name__ == "__main__":
try:
# this seems to have no effect on android for strftime...
locale.setlocale(locale.LC_ALL, "")
except Exception as e:
utils.printEx("setlocale", e)
app = App()
db = MySqlCreateTables()
for name in []: # ["Abstellanlagen", "Abstellplätze", "Alte Bäume", "Nistkästen", "Sitzbänke"]:
baseJSVersions = app.baseConfig.getBaseVersions(name)
baseJS = baseJSVersions[0]
vers = baseJS["version"]
for bv in baseJSVersions[1:]:
if bv["version"] > vers:
baseJS = bv
vers = bv["version"]
db.initDB(baseJS)
def row2db(self, row):
tag = row[Tag].value
if tag is None or len(tag) != 12:
return
tag = tag[4:] # Mi, 01.07.20 -> 01.07.20
if len(tag.split(".")) != 3:
return
# infer month and first day of month (to become screen "0")
if self.month == "00.00":
self.month = tag[3:] # 07.20
utils.firstDay = datetime.date(2000 + int(tag[6:8]), int(tag[3:5]),
1)
elif self.month != tag[3:]:
return
# infer wochenstunden from sollstunden
# see main.py, Menu, setWtagStunden
if self.wochenStunden == "00":
sollStunden = self.xval(row[Sollstunden])
if sollStunden == "04:00":
self.wochenStunden = "20"
elif sollStunden == "06:00":
self.wochenStunden = "30"
elif sollStunden == "07:00":
self.wochenStunden = "35"
elif sollStunden == "08:00" or sollStunden == "06:30":
self.wochenStunden = "38,5"
if self.wochenStunden != "00":
self.app.menu.setWtagStunden(self.wochenStunden)
if self.xval(row[Einsatzstelle1]) != "":
try:
with conn:
c = conn.cursor()
vals = {
"tag": tag,
"fnr": 1,
"einsatzstelle": row[Einsatzstelle1].value,
"beginn": self.xval(row[Beginn1]),
"ende": self.xval(row[Ende1]),
"fahrtzeit": self.xval(row[Fahrt1]),
"mvv_euro": self.xval(row[MVVEuro]),
"kh": self.kh(row[Kh1])
}
c.execute(
"INSERT INTO arbeitsblatt VALUES(:tag,:fnr,:einsatzstelle,:beginn,:ende,:fahrtzeit,:mvv_euro,:kh)",
vals)
except Exception as e:
utils.printEx("fam0:", e)
if self.xval(row[Einsatzstelle2]) != "":
try:
with conn:
c = conn.cursor()
vals = {
"tag": tag,
"fnr": 2,
"einsatzstelle": row[Einsatzstelle2].value,
"beginn": self.xval(row[Beginn2]),
"ende": self.xval(row[Ende2]),
"fahrtzeit": self.xval(row[Fahrt2]),
"mvv_euro": "",
"kh": self.kh(row[Kh2])
}
c.execute(
"INSERT INTO arbeitsblatt VALUES(:tag,:fnr,:einsatzstelle,:beginn,:ende,:fahrtzeit,:mvv_euro,:kh)",
vals)
except Exception as e:
utils.printEx("fam2:", e)
if self.xval(row[Einsatzstelle3]) != "":
try:
with conn:
c = conn.cursor()
vals = {
"tag": tag,
"fnr": 3,
"einsatzstelle": row[Einsatzstelle3].value,
"beginn": self.xval(row[Beginn3]),
"ende": self.xval(row[Ende3]),
"fahrtzeit": "",
"mvv_euro": "",
"kh": self.kh(row[Kh1])
}
c.execute(
"INSERT INTO arbeitsblatt VALUES(:tag,:fnr,:einsatzstelle,:beginn,:ende,:fahrtzeit,:mvv_euro,:kh)",
vals)
except Exception as e:
utils.printEx("fam3:", e)