def sendAssignmentEmail(assignmentID): import mailer setExits() resultString = "" assignmentID = cleanRecordID(assignmentID) assignment = Assignment.query.get(cleanRecordID(assignmentID)) if not assignment: resultString = "Invitaton Could not be sent. The Assignment Record could not be found" return resultString user = User.query.get(assignment.user_ID) if not user: resultString = "Invitaton Could not be sent. The User Record could not be found" return resultString countEvent = CountEvent.query.get(assignment.countEvent_ID) if not countEvent: resultString = "Invitaton Could not be sent. The Count Event record could not be found" return resultString countEventDict = getTimeDictionary(countEvent.startDate,countEvent.endDate) sendResult, resultString = mailer.sendInvite(assignment,user,countEventDict) # Record the fact that the email was sent if sendResult == True: sql = "UPDATE assignment set invitationSent = '%s' WHERE ID = %d;" % (datetime.strftime(datetime.now(), '%Y-%m-%d'), assignmentID) try: ass = db.engine.execute(sql) except Exception as e: printException('Error attempting to update invitationSent for '+g.title+' record.',"error",e) return resultString
def getAssignmentTripTotal(countEventID=0, locationID=0, travelerID=0, startTime=None, endTime=None, turnDir=None, seqNo = None): # Jun 3, 2016 - Modified to allow for selection of total count for a single turn Direction and or seqNo result = 0 countEventID = cleanRecordID(countEventID) locationID = cleanRecordID(locationID) travelerID = cleanRecordID(travelerID) sql = "select sum(tripCount) as tripTotal from trip where countEvent_ID = %d and location_ID = %d" % (int(countEventID), int(locationID)) if travelerID > 0: sql += " and traveler_ID = %d" % (travelerID) if startTime: timeStamp = startTime sql += " and tripDate >= '%s'" % (timeStamp) if endTime: timeStamp = endTime sql += " and tripDate <= '%s'" % (timeStamp) if turnDir: sql += " and turnDirection = '%s'" % (turnDir) if seqNo: sql += " and seqNo = '%s'" % (seqNo) sql += ";" cur = db.engine.execute(sql).fetchone() if cur: result = cur[0] if result == None: result = 0 return result
def isValidTrip(trip,startDate,endDate,localTime): #trip is a dictionary of trip data #trip['tripCount'] #trip['tripDate'] #trip['turnDirection'] #trip['location_ID'] #trip['traveler_ID'] #trip['countEvent_ID'] isValid = True errorMess = "" #test that the tripTime is in the time frame of the countEvent if getDatetimeFromString(trip['tripDate']) < localTime: isValid = False errorMess += "That trip date is before the count event. " if getDatetimeFromString(trip['tripDate']) > localTime: isValid = False errorMess += "That trip date is after the count event. " #test that all the trip data elements are present and valid if trip['turnDirection'] == "" or \ trip['turnDirection'] not in getTurnDirectionList() : isValid = False errorMess += trip['turnDirection'] + " is not a valid Turn Direction. " #test that location and count_event ID is valid tempTest = False try: cur = Assignment.query.filter(Assignment.location_ID == cleanRecordID(trip["location_ID"]), Assignment.countEvent_ID == cleanRecordID(trip["countEvent_ID"])) if cur: tempTest = True except: pass if not tempTest: isValid = False errorMess += str(trip["location_ID"]) + " is not a valid location ID. " #test that traveler ID is valid tempTest = False try: cur = EventTraveler.query.filter(EventTraveler.countEvent_ID == cleanRecordID(trip["countEvent_ID"]), EventTraveler.traveler_ID == cleanRecordID(trip["traveler_ID"])) if cur: tempTest = True except: pass if not tempTest: isValid = False errorMess += str(trip["traveler_ID"]) + " is not a valid Traveler ID. " # An error or testing #isValid = False #errorMess += "This should not have happened!" return isValid, errorMess
def getEventTravelerTripTotal(countEventID = 0, travelerID =0): result = 0 countEventID = cleanRecordID(countEventID) travelerID = cleanRecordID(travelerID) sql = "select sum(tripCount) as tripTotal from trip where countEvent_ID = %d and traveler_ID = %d;" % (countEventID, travelerID) cur = db.engine.execute(sql).fetchone() if cur: result = cur[0] if result == None: result = 0 return result
def getEventTravelerTripTotal(countEventID=0, travelerID=0): result = 0 countEventID = cleanRecordID(countEventID) travelerID = cleanRecordID(travelerID) sql = "select sum(tripCount) as tripTotal from trip where countEvent_ID = %d and traveler_ID = %d;" % ( countEventID, travelerID) cur = db.engine.execute(sql).fetchone() if cur: result = cur[0] if result == None: result = 0 return result
def makeUserOrgRecords(userID,orgIDs): """ create user_organization records for a user """ # orgIDs is a list, usually strings #first delete any current records sql = "delete from user_organization where user_ID = '%s';" % (str(userID)) db.engine.execute(sql) #db.session.commit() recs=getUserOrgs(id) for orgID in orgIDs: rec = UserOrganization(cleanRecordID(userID),cleanRecordID(orgID)) db.session.add(rec)
def edit(id=0): setExits() defaultLoc = {'lat': app.config['LOCATION_DEFAULT_LAT'], 'lng': app.config['LOCATION_DEFAULT_LNG']} #LOCATION_DEFAULT_LNG #LOCATION_DEFAULT_LAT id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) g.tripTotalCount = getLocationTripTotal(id) rec = None if id > 0: rec = Location.query.get(id) if not rec: flash(printException("Could not edit that "+g.title + " record. ID="+str(id)+")",'error')) return redirect(g.listURL) form = LocationForm(request.form, rec) if request.method == 'POST' and form.validate(): if not rec: rec = Location(form.locationName.data,g.orgID) db.session.add(rec) form.populate_obj(rec) db.session.commit() return redirect(g.listURL) return render_template('location/location_edit.html', rec=rec, form=form, defaultLoc=defaultLoc)
def delete(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) if getCountEventTripTotal(id) > 0 and g.role != "super": flash("You must be the super user to delete an event with trips.") return redirect(g.listURL) if id > 0: rec = CountEvent.query.filter(CountEvent.ID == id, CountEvent.organization_ID == g.orgID) if rec: try: #delete related records assigned = Assignment.query.filter(Assignment.countEvent_ID == id).delete() trav = EventTraveler.query.filter(EventTraveler.countEvent_ID == id).delete() rec.delete() db.session.commit() app.logger.info(g.title+' record (id='+str(id)+') Deleted by: ' + g.user + " on "+ datetime.now().isoformat()) except Exception as e: flash(printException('Error attempting to delete '+g.title+' record.',"error",e)) db.session.rollback() else: flash("Record could not be found.") return redirect(g.listURL)
def delete(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) rec = Traveler.query.get(id) if rec: ## Can't delete Traveler that has been used in a trip trip = Trip.query.filter_by(traveler_ID = str(id)).all() if trip: #can't delete flash("You can't delete this Traveler because there are Trip records that use it") return redirect(g.listURL) # Delete the related records et = EventTraveler.query.filter_by(traveler_ID = str(id)).delete(synchronize_session='fetch') tf = TravelerFeature.query.filter_by(traveler_ID = str(id)).delete(synchronize_session='fetch') ## delete the traveler try: db.session.delete(rec) db.session.commit() except Exception as e: flash(printException('Error attempting to delete '+g.title+' record.',"error",e)) db.session.rollback() return redirect(g.listURL)
def edit(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) rec = None if id > 0: rec = Trip.query.get(id) if not rec: flash(printException("Could not edit that "+g.title + " record. ID="+str(id)+")",'error')) return redirect(g.listURL) form = TripForm(request.form, rec) ## choices need to be assigned before rendering the form # AND before attempting to validate it form.countEvent_ID.choices = getCountEventChoices() form.location_ID.choices = getLocationChoices() form.traveler_ID.choices = getTravelerChoices() form.turnDirection.choices = getTurnDirectionChoices() if request.method == 'POST' and form.validate(): if not rec: rec = Trip(form.tripCount.data,form.tripDate.data,form.turnDirection.data,form.seqNo.data,form.location_ID.data,form.traveler_ID.data,form.countEvent_ID.data) db.session.add(rec) form.populate_obj(rec) db.session.commit() return redirect(g.listURL) return render_template('genericEditForm.html', rec=rec, form=form)
def createFromList(countEventID="0"): """ Create a new Assignment record from the CountEvent edit form """ #the template popupEditForm will substitue this for the missing countEvent_ID g.countEventID = cleanRecordID(countEventID) return editFromList(0)
def getOrgDefaultTimeZone(orgID): orgID = cleanRecordID(orgID) if orgID > 0: org = Organization.query.get(orgID) if org: return org.defaultTimeZone return ""
def getName(orgID): orgID = cleanRecordID(orgID) if orgID > 0: org = Organization.query.get(orgID) if org: return org.name return ''
def getLocationTripTotal(locationID): result = 0 locationID = cleanRecordID(locationID) sql = "select sum(tripCount) as tripTotal from trip where location_ID = %d;" % (locationID) cur = db.engine.execute(sql).fetchone() if cur: result = cur[0] if result == None: result = 0 return result
def getTravelersForEvent(countEventID): countEventID = cleanRecordID(countEventID) eventTravelers = getEventTravelers(countEventID) travelers = None if eventTravelers: # sql = "select * from traveler where ID in (select traveler_ID from event_traveler where countEvent_ID = %d)" % (countEventID) sql = "select traveler.*, event_traveler.sortOrder from traveler join event_traveler on event_traveler.traveler_ID = traveler.ID and event_traveler.countEvent_ID = %d " % (countEventID) sql += " where traveler.ID in (select traveler_ID from event_traveler where countEvent_ID = %d) order by event_traveler.sortOrder" % (countEventID) travelers = db.engine.execute(sql).fetchall() return travelers
def getLocationTripTotal(locationID): result = 0 locationID = cleanRecordID(locationID) sql = "select sum(tripCount) as tripTotal from trip where location_ID = %d;" % ( locationID) cur = db.engine.execute(sql).fetchone() if cur: result = cur[0] if result == None: result = 0 return result
def removeFromList(eventTravelerID): eventTravelerID=cleanRecordID(eventTravelerID) if eventTravelerID > 0: # remove the event_traveler record only if there are no trips for this traveler / event rec = EventTraveler.query.get(eventTravelerID) if rec: tripCnt = getEventTravelerTripTotal(eventTravelerID, rec.traveler_ID) if tripCnt == 0: db.session.delete(rec) db.session.commit() return "success" return "failure: Unable to Remove that record."
def getAssignmentList(countEventID=0): countEventID = cleanRecordID(countEventID) out = "" if countEventID > 0: recs = Assignment.query.filter(Assignment.countEvent_ID==countEventID).order_by(Assignment.locationName) if recs: out = "" for rec in recs: totalTrips = getAssignmentTripTotal(rec.countEvent_ID, rec.location_ID) out += render_template('assignment/listElement.html', rec=rec, totalTrips=totalTrips) return out
def deleteRecordID(id): id = cleanRecordID(id) g.orgID = cleanRecordID(g.orgID) # Can't delete an assignment with trips rec = Assignment.query.get(id) if rec: if getAssignmentTripTotal(rec.countEvent_ID, rec.location_ID) > 0: return False if id > 0: #rec = Assignment.query.get(id) sql = 'DELETE FROM assignment \ WHERE assignment."ID" = %d AND (SELECT count_event."organization_ID" \ FROM count_event \ WHERE count_event."organization_ID" = %d);' % (id,g.orgID) try: rec = db.engine.execute(sql) except: return False return True return False
def deleteRecordID(id): id = cleanRecordID(id) g.orgID = cleanRecordID(g.orgID) # Can't delete an assignment with trips rec = Assignment.query.get(id) if rec: if getAssignmentTripTotal(rec.countEvent_ID, rec.location_ID) > 0: return False if id > 0: #rec = Assignment.query.get(id) sql = 'DELETE FROM assignment \ WHERE assignment."ID" = %d AND (SELECT count_event."organization_ID" \ FROM count_event \ WHERE count_event."organization_ID" = %d);' % (id, g.orgID) try: rec = db.engine.execute(sql) except: return False return True return False
def sendAssignmentEmail(assignmentID): import mailer setExits() resultString = "" assignmentID = cleanRecordID(assignmentID) assignment = Assignment.query.get(cleanRecordID(assignmentID)) if not assignment: resultString = "Invitaton Could not be sent. The Assignment Record could not be found" return resultString user = User.query.get(assignment.user_ID) if not user: resultString = "Invitaton Could not be sent. The User Record could not be found" return resultString countEvent = CountEvent.query.get(assignment.countEvent_ID) if not countEvent: resultString = "Invitaton Could not be sent. The Count Event record could not be found" return resultString countEventDict = getTimeDictionary(countEvent.startDate, countEvent.endDate) sendResult, resultString = mailer.sendInvite(assignment, user, countEventDict) # Record the fact that the email was sent if sendResult == True: sql = "UPDATE assignment set invitationSent = '%s' WHERE ID = %d;" % ( datetime.strftime(datetime.now(), '%Y-%m-%d'), assignmentID) try: ass = db.engine.execute(sql) except Exception as e: printException( 'Error attempting to update invitationSent for ' + g.title + ' record.', "error", e) return resultString
def delete(id="0"): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) if deleteRecordID(id): pass else: flash(printException("Could not delete that "+g.title + " record ID="+str(id)+" could not be found.","error")) return redirect(g.listURL)
def createNewRecord(eventID=None): """ return a reference to a newly created record or elss None""" eventID=cleanRecordID(eventID) rec = None if eventID > 0: #test that countEvent record exits cnt = CountEvent.query.filter(CountEvent.ID == eventID).count() if cnt > 0: rec = Assignment(eventID,getUID()) db.session.add(rec) else: flash(printException("Invalid countEvent ID during Count Event creation.","error")) return rec
def getTravelerList(countEventID = 0): countEventID = cleanRecordID(countEventID) out = "" if countEventID > 0: recs = getEventTravelers(countEventID) if recs: for eventTraveler in recs: traveler = Traveler.query.get(eventTraveler.traveler_ID) if traveler: totalTrips = getEventTravelerTripTotal(countEventID, traveler.ID) out += render_template('traveler/travelerListElement.html', eventTraveler=eventTraveler, traveler=traveler, totalTrips=totalTrips) return out
def getAssignmentTripTotal(countEventID=0, locationID=0, travelerID=0, startTime=None, endTime=None, turnDir=None, seqNo=None): # Jun 3, 2016 - Modified to allow for selection of total count for a single turn Direction and or seqNo result = 0 countEventID = cleanRecordID(countEventID) locationID = cleanRecordID(locationID) travelerID = cleanRecordID(travelerID) sql = "select sum(tripCount) as tripTotal from trip where countEvent_ID = %d and location_ID = %d" % ( int(countEventID), int(locationID)) if travelerID > 0: sql += " and traveler_ID = %d" % (travelerID) if startTime: timeStamp = startTime sql += " and tripDate >= '%s'" % (timeStamp) if endTime: timeStamp = endTime sql += " and tripDate <= '%s'" % (timeStamp) if turnDir: sql += " and turnDirection = '%s'" % (turnDir) if seqNo: sql += " and seqNo = '%s'" % (seqNo) sql += ";" cur = db.engine.execute(sql).fetchone() if cur: result = cur[0] if result == None: result = 0 return result
def delete(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) if id > 0: rec = Trip.query.get(id) if rec: db.session.delete(rec) db.session.commit() else: flash(printException("Could not delete that "+g.title + " record ID="+str(id)+" could not be found.","error")) return redirect(g.listURL)
def delete(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid record ID") return redirect(g.listURL) if id > 0: rec = Location.query.filter(Location.ID == id, Location.organization_ID == g.orgID).first() if rec: db.session.delete(rec) db.session.commit() else: flash(printException("Could not delete that "+g.title + " record ID="+str(id)+" could not be found or was wrong org.","error")) return redirect(g.listURL)
def org_delete(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) if id > 0: rec = Organization.query.get(id) if rec: db.session.delete(rec) db.session.commit() else: flash("Record could not be deleted.") return redirect(url_for('.org_list'))
def createNewRecord(eventID=None): """ return a reference to a newly created record or elss None""" eventID = cleanRecordID(eventID) rec = None if eventID > 0: #test that countEvent record exits cnt = CountEvent.query.filter(CountEvent.ID == eventID).count() if cnt > 0: rec = Assignment(eventID, getUID()) db.session.add(rec) else: flash( printException( "Invalid countEvent ID during Count Event creation.", "error")) return rec
def sendInvite(assignment, user, countEventDict): """ Send a single counting Assignment email """ hostName = app.config["HOST_NAME"] organization = Organization.query.get(cleanRecordID(g.orgID)) with mail.record_messages() as outbox: if user and assignment and organization: subject = "Your assignment from %s" % (organization.name) msg = Message(subject, sender=(organization.name, organization.email), recipients=[(user.name, user.email)]) msg.body = render_template( "email/standardInvite.txt", assignment=assignment, countEventDict=countEventDict, user=user, hostName=hostName, organization=organization, ) msg.html = render_template( "email/standardInvite.html", assignment=assignment, countEventDict=countEventDict, user=user, hostName=hostName, organization=organization, ) else: mes = "Email is missing parameters" printException(mes, "error", e) return (False, mes) try: mail.send(msg) except Exception as e: mes = "Error Sending email" printException(mes, "error", e) return (False, mes) if mail.suppress: mes = '%d email(s) would have been sent if we were not testing' % ( len(outbox)) return (True, mes) return (True, "Email Sent Successfully")
def delete(id="0"): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) if deleteRecordID(id): pass else: flash( printException( "Could not delete that " + g.title + " record ID=" + str(id) + " could not be found.", "error")) return redirect(g.listURL)
def getAssignmentList(countEventID=0): countEventID = cleanRecordID(countEventID) out = "" if countEventID > 0: recs = Assignment.query.filter( Assignment.countEvent_ID == countEventID).order_by( Assignment.locationName) if recs: out = "" for rec in recs: totalTrips = getAssignmentTripTotal(rec.countEvent_ID, rec.location_ID) out += render_template('assignment/listElement.html', rec=rec, totalTrips=totalTrips) return out
def delete(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) if id > 0: rec = User.query.get(id) if rec: try: db.session.delete(rec) db.session.commit() except Exception as e: flash(printException('Error attempting to delete '+g.title+' record.',"error",e)) else: flash("Record could not be deleted.") return redirect(g.listURL)
def delete(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) if id > 0: rec = Trip.query.get(id) if rec: db.session.delete(rec) db.session.commit() else: flash( printException( "Could not delete that " + g.title + " record ID=" + str(id) + " could not be found.", "error")) return redirect(g.listURL)
def edit(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) if db: if not request.form: """ if no form object, send the form page """ # get the Org record if you can rec = None if id > 0: rec = Feature.query.filter_by(ID=id).first_or_404() return render_template('feature/feature_edit.html', rec=rec) #have the request form if validForm(): try: if int(id) > 0: rec = Feature.query.get(id) else: ## create a new record stub rec = Feature(request.form['featureClass'],request.form['featureValue']) db.session.add(rec) #update the record rec.featureClass = request.form['featureClass'] rec.featureValue = request.form['featureValue'] db.session.commit() return redirect(url_for('.display')) except Exception as e: flash(printException('Could not save record. Unknown Error',"error",e)) # form not valid - redisplay return render_template('feature/feature_edit.html', rec=request.form) else: flash(printException('Could not open database'),"info") return redirect(url_for('.display'))
def delete(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) if db: if id > 0: rec = Feature.query.get(id) if rec: db.session.delete(rec) db.session.commit() else: flash(printException(g.title + " Record ID "+str(id)+" could not be found.","info")) else: flash(printException("Could not open database","info")) return redirect(url_for('.display'))
def delete(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid record ID") return redirect(g.listURL) if id > 0: rec = Location.query.filter( Location.ID == id, Location.organization_ID == g.orgID).first() if rec: db.session.delete(rec) db.session.commit() else: flash( printException( "Could not delete that " + g.title + " record ID=" + str(id) + " could not be found or was wrong org.", "error")) return redirect(g.listURL)
def org_edit(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) timeZones = getTimeZones() if not request.form: """ if no form object, send the form page """ # get the Org record if you can rec = None if int(id) > 0: rec = Organization.query.get(id) return render_template('org/org_edit.html', rec=rec, timeZones=timeZones) #have the request form if validForm(): if id > 0: rec = Organization.query.get(id) else: ## create a new record stub rec = Organization(request.form['name'],request.form['email'],request.form["defaultTimeZone"]) db.session.add(rec) #update the record rec.name = request.form['name'] rec.email = request.form['email'] rec.defaultTimeZone = request.form["defaultTimeZone"] try: db.session.commit() except Exception as e: flash(printException('Error attempting to create '+g.title+' record.',"error",e)) db.session.rollback() return redirect(url_for('.org_list')) # form not valid - redisplay return render_template('org/org_edit.html', rec=request.form, timeZones=timeZones)
def edit(id=0): setExits() defaultLoc = { 'lat': app.config['LOCATION_DEFAULT_LAT'], 'lng': app.config['LOCATION_DEFAULT_LNG'] } #LOCATION_DEFAULT_LNG #LOCATION_DEFAULT_LAT id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) g.tripTotalCount = getLocationTripTotal(id) rec = None if id > 0: rec = Location.query.get(id) if not rec: flash( printException( "Could not edit that " + g.title + " record. ID=" + str(id) + ")", 'error')) return redirect(g.listURL) form = LocationForm(request.form, rec) if request.method == 'POST' and form.validate(): if not rec: rec = Location(form.locationName.data, g.orgID) db.session.add(rec) form.populate_obj(rec) db.session.commit() return redirect(g.listURL) return render_template('location/location_edit.html', rec=rec, form=form, defaultLoc=defaultLoc)
def edit(id="0"): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) rec = None if id > 0: rec = Assignment.query.get(id) if not rec: flash( printException( "Could not edit that " + g.title + " record. ID=" + str(id) + ")", 'error')) return redirect(g.listURL) form = AssignmentForm(request.form, rec) ## choices need to be assigned before rendering the form # AND before attempting to validate it form.user_ID.choices = getUserChoices() form.countEvent_ID.choices = getCountEventChoices() form.location_ID.choices = getLocationChoices() g.AssignedUserIDs = () if rec: g.AssignedUserIDs = getAssignedUsers(int(rec.countEvent_ID)) if request.method == 'POST' and form.validate(): if not rec: rec = createNewRecord(form.countEvent_ID.data) if not rec: return redirect(g.listURL) form.populate_obj(rec) db.session.commit() return redirect(g.listURL) return render_template('genericEditForm.html', rec=rec, form=form)
def edit(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) rec = None if id > 0: rec = Trip.query.get(id) if not rec: flash( printException( "Could not edit that " + g.title + " record. ID=" + str(id) + ")", 'error')) return redirect(g.listURL) form = TripForm(request.form, rec) ## choices need to be assigned before rendering the form # AND before attempting to validate it form.countEvent_ID.choices = getCountEventChoices() form.location_ID.choices = getLocationChoices() form.traveler_ID.choices = getTravelerChoices() form.turnDirection.choices = getTurnDirectionChoices() if request.method == 'POST' and form.validate(): if not rec: rec = Trip(form.tripCount.data, form.tripDate.data, form.turnDirection.data, form.seqNo.data, form.location_ID.data, form.traveler_ID.data, form.countEvent_ID.data) db.session.add(rec) form.populate_obj(rec) db.session.commit() return redirect(g.listURL) return render_template('genericEditForm.html', rec=rec, form=form)
def delete(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) if getCountEventTripTotal(id) > 0 and g.role != "super": flash("You must be the super user to delete an event with trips.") return redirect(g.listURL) if id > 0: rec = CountEvent.query.filter(CountEvent.ID == id, CountEvent.organization_ID == g.orgID) if rec: try: #delete related records assigned = Assignment.query.filter( Assignment.countEvent_ID == id).delete() trav = EventTraveler.query.filter( EventTraveler.countEvent_ID == id).delete() rec.delete() db.session.commit() app.logger.info(g.title + ' record (id=' + str(id) + ') Deleted by: ' + g.user + " on " + datetime.now().isoformat()) except Exception as e: flash( printException( 'Error attempting to delete ' + g.title + ' record.', "error", e)) db.session.rollback() else: flash("Record could not be found.") return redirect(g.listURL)
def edit(id="0"): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) rec = None if id > 0: rec = Assignment.query.get(id) if not rec: flash(printException("Could not edit that "+g.title + " record. ID="+str(id)+")",'error')) return redirect(g.listURL) form = AssignmentForm(request.form, rec) ## choices need to be assigned before rendering the form # AND before attempting to validate it form.user_ID.choices = getUserChoices() form.countEvent_ID.choices = getCountEventChoices() form.location_ID.choices = getLocationChoices() g.AssignedUserIDs = () if rec: g.AssignedUserIDs = getAssignedUsers(int(rec.countEvent_ID)) if request.method == 'POST' and form.validate(): if not rec: rec = createNewRecord(form.countEvent_ID.data) if not rec: return redirect(g.listURL) form.populate_obj(rec) db.session.commit() return redirect(g.listURL) return render_template('genericEditForm.html', rec=rec, form=form)
def duplicate(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) #duplicate this event without any assignments or trips rec = CountEvent.query.get(id) if rec: #Duplicate the Count Event record newRec = createEventRecord() #ID = db.Column(db.Integer, primary_key=True) #title = db.Column(db.Text) #weather = db.Column(db.Text) #startDate = db.Column(db.Text, nullable=False) #endDate = db.Column(db.Text, nullable=False) #timeZone = db.Column(db.Text) #isDST = db.Column(db.Integer, default=0) #organization_ID = db.Column(db.Integer, db.ForeignKey('organization.ID'), nullable=False) newRec.title = "Copy of " + rec.title newRec.startDate = rec.startDate newRec.endDate = rec.endDate newRec.timeZone = rec.timeZone newRec.isDST = rec.isDST db.session.commit() #Get the new ID newID = newRec.ID #Duplicate the Travelers travs = EventTraveler.query.filter(EventTraveler.countEvent_ID == id) if travs: for trav in travs: newTrav = EventTraveler(newRec.ID, trav.traveler_ID) db.session.add(newTrav) newTrav.sortOrder = trav.sortOrder db.session.commit() #Duplicate the Locations #ID = db.Column(db.Integer, primary_key=True) #assignmentUID = db.Column(db.Text, unique=True) #countEvent_ID = db.Column(db.Integer, db.ForeignKey('count_event.ID')) #location_ID = db.Column(db.Integer, db.ForeignKey('location.ID')) #user_ID = db.Column(db.Integer, db.ForeignKey('user.ID')) #invitationSent = db.Column(db.Text, default="") assignments = Assignment.query.filter(Assignment.countEvent_ID == id) if assignments: for assignment in assignments: newAssignment = createNewAssignment(newRec.ID) newAssignment.location_ID = assignment.location_ID newAssignment.user_ID = 0 newAssignment.invitationSent = "" db.session.commit() return redirect(url_for('.edit', id=newRec.ID)) flash("That Count Event Record does not exsist") return redirect(g.listURL)
def edit(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) assignmentList = getAssignmentList(id) #fully rendered HTML travelerList = getTravelerList(id) timeZones = getTimeZones() g.tripTotalCount = getCountEventTripTotal(id) if not request.form: """ if no form object, send the form page """ #Set up a default time for the event # Today with time set to something reasonable start = datetime.now().replace(hour=16, minute=0, second=0) end = start + timedelta(hours=2) theTime = getTimeDictionary(start.isoformat(),end.isoformat()) cur = None #Get the default timeZone for this Organization g.timeZone = getDefaultTimeZone() if id > 0: cur = CountEvent.query.filter_by(ID=id).first() if not cur: mes = g.title +" Record could not be found." + " ID:" + str(id) flash(printException(mes,"error")) return redirect(g.listURL) theTime = getTimeDictionary(cur.startDate,cur.endDate) g.timeZone = None return render_template('count_event/count_event_edit.html', rec=cur ,theTime=theTime, timeZones=timeZones, assignmentList=assignmentList, travelerList=travelerList, ) #have the request form # handle the checkbox for Daylite savings time isDST = 0 if request.form["isDST"]: isDST = request.form["isDST"] if validForm(): startingDate = startDateFromForm() endingDate = endDateFromForm() if id > 0: cur = CountEvent.query.get(id) #update the record cur.title = request.form["title"] cur.startDate = startingDate.isoformat()[:19] cur.endDate = endingDate.isoformat()[:19] cur.isDST = isDST cur.timeZone = request.form["timeZone"] cur.weather = request.form["weather"] cur.organization_ID = request.form['organization_ID'] else: ## create a new record cur = CountEvent(request.form["title"],startingDate.isoformat()[:19],endingDate.isoformat()[:19],request.form["timeZone"],isDST,request.form['organization_ID']) db.session.add(cur) try: db.session.commit() except Exception as e: db.session.rollback() flash(printException('Error attempting to save '+g.title+' record.',"error",e)) return redirect(g.listURL) # form not valid - redisplay #restore theTime to the values as entered theTime = dict() theTime["hour"] = int(request.form["hour"]) theTime["minute"] = int(request.form["minute"]) theTime["duration"] = int(request.form["duration"]) theTime["AMPM"] = request.form["AMPM"] theTime["eventDate"] = request.form["eventDate"] return render_template('count_event/count_event_edit.html', rec=request.form, theTime=theTime, timeZones=timeZones, assignmentList=assignmentList)
def duplicate(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) #duplicate this event without any assignments or trips rec = CountEvent.query.get(id) if rec: #Duplicate the Count Event record newRec = createEventRecord() #ID = db.Column(db.Integer, primary_key=True) #title = db.Column(db.Text) #weather = db.Column(db.Text) #startDate = db.Column(db.Text, nullable=False) #endDate = db.Column(db.Text, nullable=False) #timeZone = db.Column(db.Text) #isDST = db.Column(db.Integer, default=0) #organization_ID = db.Column(db.Integer, db.ForeignKey('organization.ID'), nullable=False) newRec.title = "Copy of " + rec.title newRec.startDate = rec.startDate newRec.endDate = rec.endDate newRec.timeZone = rec.timeZone newRec.isDST = rec.isDST db.session.commit() #Get the new ID newID = newRec.ID #Duplicate the Travelers travs = EventTraveler.query.filter(EventTraveler.countEvent_ID == id ) if travs: for trav in travs: newTrav = EventTraveler(newRec.ID,trav.traveler_ID) db.session.add(newTrav) newTrav.sortOrder = trav.sortOrder db.session.commit() #Duplicate the Locations #ID = db.Column(db.Integer, primary_key=True) #assignmentUID = db.Column(db.Text, unique=True) #countEvent_ID = db.Column(db.Integer, db.ForeignKey('count_event.ID')) #location_ID = db.Column(db.Integer, db.ForeignKey('location.ID')) #user_ID = db.Column(db.Integer, db.ForeignKey('user.ID')) #invitationSent = db.Column(db.Text, default="") assignments = Assignment.query.filter(Assignment.countEvent_ID == id) if assignments: for assignment in assignments: newAssignment = createNewAssignment(newRec.ID) newAssignment.location_ID = assignment.location_ID newAssignment.user_ID = 0 newAssignment.invitationSent = "" db.session.commit() return redirect(url_for('.edit', id=newRec.ID)) flash("That Count Event Record does not exsist") return redirect(g.listURL)
def edit(id=0): setExits() id = cleanRecordID(id) if id < 0: flash("That is not a valid ID") return redirect(g.listURL) assignmentList = getAssignmentList(id) #fully rendered HTML travelerList = getTravelerList(id) timeZones = getTimeZones() g.tripTotalCount = getCountEventTripTotal(id) if not request.form: """ if no form object, send the form page """ #Set up a default time for the event # Today with time set to something reasonable start = datetime.now().replace(hour=16, minute=0, second=0) end = start + timedelta(hours=2) theTime = getTimeDictionary(start.isoformat(), end.isoformat()) cur = None #Get the default timeZone for this Organization g.timeZone = getDefaultTimeZone() if id > 0: cur = CountEvent.query.filter_by(ID=id).first() if not cur: mes = g.title + " Record could not be found." + " ID:" + str( id) flash(printException(mes, "error")) return redirect(g.listURL) theTime = getTimeDictionary(cur.startDate, cur.endDate) g.timeZone = None return render_template( 'count_event/count_event_edit.html', rec=cur, theTime=theTime, timeZones=timeZones, assignmentList=assignmentList, travelerList=travelerList, ) #have the request form # handle the checkbox for Daylite savings time isDST = 0 if request.form["isDST"]: isDST = request.form["isDST"] if validForm(): startingDate = startDateFromForm() endingDate = endDateFromForm() if id > 0: cur = CountEvent.query.get(id) #update the record cur.title = request.form["title"] cur.startDate = startingDate.isoformat()[:19] cur.endDate = endingDate.isoformat()[:19] cur.isDST = isDST cur.timeZone = request.form["timeZone"] cur.weather = request.form["weather"] cur.organization_ID = request.form['organization_ID'] else: ## create a new record cur = CountEvent(request.form["title"], startingDate.isoformat()[:19], endingDate.isoformat()[:19], request.form["timeZone"], isDST, request.form['organization_ID']) db.session.add(cur) try: db.session.commit() except Exception as e: db.session.rollback() flash( printException( 'Error attempting to save ' + g.title + ' record.', "error", e)) return redirect(g.listURL) # form not valid - redisplay #restore theTime to the values as entered theTime = dict() theTime["hour"] = int(request.form["hour"]) theTime["minute"] = int(request.form["minute"]) theTime["duration"] = int(request.form["duration"]) theTime["AMPM"] = request.form["AMPM"] theTime["eventDate"] = request.form["eventDate"] return render_template('count_event/count_event_edit.html', rec=request.form, theTime=theTime, timeZones=timeZones, assignmentList=assignmentList)
def location(orgID="0"): setExits() g.title = 'Count Locations' g.mapURL = url_for('.location') searchOrgs = [] searchEvents = [] # User specified a particular organization to list orgID = cleanRecordID(orgID) if orgID > 0: g.orgID = orgID searchOrgs.append(str(orgID)) if db : queryData = {} queryData['searchType'] = 'locations' queryData['selectType'] = 'multiple' queryData['includeAllOption'] = True #Get all orgs searchForm.orgsToDict(queryData) if not request.form and g.orgID: queryData['searchOrgs'] = [str(g.orgID)] else: searchForm.getSelectValues(request.form, searchOrgs,searchEvents) # all parameters must be empty lists queryData['searchOrgs'] = searchOrgs #We don't need searchEvents for this map sql = "select locationName, ID, latitude, longitude from location " if '0' not in searchOrgs: orgIDs = "" for i in searchOrgs: orgIDs += i + "," sql += " where organization_ID in (%s) " % (orgIDs[0:-1]) recs = db.engine.execute(sql).fetchall() markerData = {} markerData["cluster"] = True markerData["zoomToFit"] = False # can't zoom if there are no markers if recs: markerData["markers"] = [] for rec in recs: #db.engine.execute returns a list of sets without column names #namedtuple creates an object that getMarkerDict can access with dot notation Fields = namedtuple('record', 'locationName ID latitude longitude') record = Fields(rec[0], rec[1], rec[2], rec[3]) marker = getMarkerDict(record) # returns a dict or None if marker: popup = render_template('map/locationListPopup.html', rec=record) popup = escapeTemplateForJson(popup) marker['popup'] = popup markerData["markers"].append(marker) markerData["zoomToFit"] = True return render_template('map/JSONmap.html', markerData=markerData, queryData=queryData) else: flash(printException('Could not open Database',"info")) return redirect(url_for('home'))
def editFromList(id="0"): """ handle the editing from the count event form. Intended for use with AJAX request There should always be POST data """ ## when creating a new record, g.countEventID will contain the ID of the countEvent record setExits() data = None if not data: data = request.form if "ID" in data: id = data["ID"] id = cleanRecordID(id) if id < 0: flash("Invalid Record ID") return redirect(g.listURL) locations = None if id == 0: if "countEvent_ID" in data: g.countEventID = data["countEvent_ID"] ceID = cleanRecordID(g.countEventID) g.orgID = cleanRecordID(g.orgID) ## It's important to call fetchAll() or fetchOne() after executing sql this way or the ## database will be left in a locked state. sql = 'select ID,locationName from location where organization_ID = %d \ and ID not in \ (select location_ID from assignment where countEvent_ID = %d) \ order by locationName;' \ % (g.orgID, ceID) locations = db.engine.execute(sql).fetchall() if len(locations) == 0: return "failure: There are no more Locations to use." rec = None if id > 0: rec = Assignment.query.get(id) if not rec: flash( printException( "Could not edit that " + g.title + " record. (ID=" + str(id) + ")", 'error')) return redirect(g.listURL) form = AssignmentEditFromListForm(data, rec) ## choices need to be assigned before rendering the form # AND before attempting to validate it form.user_ID.choices = getUserChoices() if request.method == "POST" and form.validate(): if not rec: rec = createNewRecord(form.countEvent_ID.data) if not rec: return "failure: Unable to create a new Assignment record" rec.location_ID = form.location_ID.data rec.countEvent_ID = form.countEvent_ID.data rec.user_ID = form.user_ID.data try: db.session.commit() except Exception as e: printException("Unable to save Assignment from list", "error", e) return "failure: Sorry. Unable to save your changes." return "success" # the success function looks for this... assignedUserIDs = () if rec: g.countEventID = int(rec.countEvent_ID) assignedUserIDs = getAssignedUsers(g.countEventID) return render_template( 'assignment/popupEditForm.html', form=form, locations=locations, assigned=assignedUserIDs, )
def editTripsFromList(id): setExits() tripData = {} #empty dictionary id = cleanRecordID(id) if id > 0: rec = Assignment.query.get(id) if not rec: return "failure: Assignment record not found" tripCount = getAssignmentTripTotal(id) if tripCount > 0: return "failure: There are already trips recorded." # Get travelers travelers = getTravelersForEvent(rec.countEvent_ID) #populate tripData with info on manually enterd counts tripData = getTurnData(rec) countEvent = CountEvent.query.get(rec.countEvent_ID) timeFrames = getTripTimeFrames( getDatetimeFromString(countEvent.startDate), getDatetimeFromString(countEvent.endDate)) if request.method == "POST": # Validate form? result = True # record trips for countInputName in tripData.keys(): if not request.form[countInputName]: tripCount = 0 else: tripCount = request.form[countInputName] turnLeg = tripData[countInputName][1] travelerID = tripData[countInputName][2] try: tripCount = int(tripCount) #this may throw a ValueError if tripCount < 0: result = False raise ValueError("Negative values are not allowed.") if tripCount != tripData[countInputName][0]: #delete the previous manual trips, if any # gin up the possible start and end times startTime, endTime = getTimeStampFromTimeFrame( tripData[countInputName][3].split("-")[0], countEvent.startDate) try: trips = Trip.query.filter( Trip.countEvent_ID == rec.countEvent_ID, Trip.location_ID == rec.location_ID, Trip.traveler_ID == travelerID, Trip.tripDate >= startTime, Trip.tripDate <= endTime, Trip.turnDirection == turnLeg, Trip.seqNo == "000").delete() except: pass #the trip records may not exist if tripCount > 0: # genterate the trip time stamp startTime, endTime = getTimeStampFromTimeFrame( tripData[countInputName][3].split("-")[0], countEvent.startDate) try: cur = Trip(tripCount, endTime, turnLeg, "000", rec.location_ID, travelerID, rec.countEvent_ID) db.session.add(cur) except Exception as e: result = False flash( printException( 'Could not record Trip for ' + turnLeg, "error", e)) except ValueError as e: result = False #remove the 'standard' errpr message mes = "%s" % (e) if mes[0:7] == 'invalid': mes = '' trav = Traveler.query.get(travelerID) errTrav = "a traveler" if trav: errTrav = trav.name flash("The value '%s' in turn %s of %s is invalid. %s" % (tripCount, turnLeg, errTrav, mes)) if result: db.session.commit() return "success" # this is an ajax request else: db.session.rollback() tripData = getTurnData(rec, request.form) flash("No changes were saved.") # render form return render_template('assignment/editTrips.html', rec=rec, travelers=travelers, tripData=tripData, timeFrames=timeFrames) return "failure: Unable to edit trips for Assignment"
def deleteFromList(id): id = cleanRecordID(id) if deleteRecordID(id): return "success" else: return "failure: Unable to Delete that record."
def getEventTravelers(countEventID): countEventID = cleanRecordID(countEventID) return EventTraveler.query.filter(EventTraveler.countEvent_ID==countEventID).order_by(EventTraveler.sortOrder)