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)