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 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)
