def sql_query(self):
"""
method to execute sql query in the database
:return: ResultProxy from SqlAlchemy, or None if
query failed.
"""
result = None
try:
dbquery = self.config.get('database', 'query')
if self.last_value_name is not None:
dbquery = dbquery.format(
**{self.last_value_name: self.last_value})
q = SQLQuery(dbquery)
result = self.conn.execute(q)
except SQLError as err:
self.logger.error('Impossible to query database: %r',
err,
exc_info=1)
except ConfigError as err:
self.logger.error(
'Impossible to read database.query from config: %r',
err,
exc_info=1)
return result
def setNoShow_SP(recordID,noShow):
# STORED PROCEDURE APPROACH
print('STORED PROCEDURE APPROACH test')
if (noShow == 'True'):
noShowValue = 1
else:
noShowValue = 0
sp = "EXEC setNoShow " + str(recordID) + "," + str(noShowValue)
sql = SQLQuery(sp)
result = db.engine.execute(sql)
msg = 'No_Show updated'
return (msg)
def getCourseMembers():
specifiedSection = request.args.get('sectionNumber')
#destination = request.args.get('destination')
# todays_date = date.today()
# displayDate = todays_date.strftime('%-B %d, %Y')
term = db.session.query(ControlVariables.Current_Course_Term).filter(ControlVariables.Shop_Number == 1).scalar()
# BUILD CLASS LISTS ARRAY FOR THE CURRENT TERM
classListDict = []
classListItems = []
try:
sp = "EXEC classLists '" + term + "'"
sql = SQLQuery(sp)
classLists = db.engine.execute(sql)
except (SQLAlchemyError, DBAPIError) as e:
errorMsg = "ERROR retrieving classLists "
flash(errorMsg,'danger')
return 'ERROR in classList list build.'
if classLists == None:
flash('There are no classLists available for this term.','info')
else:
for classList in classLists:
memberName = classList.lastName + ', ' + classList.firstName
if classList.nickName != None and classList.nickName != '':
memberName += ' (' + classList.nickName + ')'
sectionName = classList.courseNumber + '-' + classList.sectionID
classListItems = {
'memberName':memberName,
'sectionName':sectionName,
'dateEnrolled':classList.dateEnrolled.strftime('%m-%d-%Y'),
'homePhone':classList.homePhone,
'cellPhone':classList.cellPhone,
'eMail':classList.eMail
}
if sectionName == specifiedSection:
classListDict.append(classListItems)
return jsonify(classListDict=classListDict)
def checkIn():
#print('... begin checkIn routine ...')
shopID = getShopID()
if request.method != 'POST':
return
#restricted = False
requestData = request.get_json()
villageID = requestData.get("memberID")
location = requestData.get("location")
if location == 'RA':
shopNumber = 1
else:
if location == 'BW':
shopNumber = 2
else:
shopNumber = 0
# IS MEMBER ALREADY CHECKED IN?
# Look for current checkin in the table tblMember_Activity
memberCheckedIn = False
typeOfWorkAtCheckIn = ""
todaysDate = date.today()
# sqlCheckInRecord = "SELECT ID, Member_ID, Check_In_Date_Time, Check_Out_Date_Time, "
# sqlCheckInRecord += "Type_Of_Work, Shop_Number "
# sqlCheckInRecord += "FROM tblMember_Activity "
# sqlCheckInRecord += "WHERE Member_ID = '" + villageID + "' "
# sqlCheckInRecord += "AND Check_Out_Date_Time Is Null "
# sqlCheckInRecord += "AND Format(Check_In_Date_Time,'yyyy-MM-dd') = '" + str(todaysDate) + "' "
#print('todaysDate - ',todaysDate)
# activity = db.engine.execute(sqlCheckInRecord)
sp = "EXEC memberCheckInsNotOut '" + villageID + "', '" + str(
todaysDate) + "'"
sql = SQLQuery(sp)
activity = db.engine.execute(sql)
#print('display current checkIn records without a checkOut time ...')
for a in activity:
recordID = a.ID
typeOfWorkAtCheckIn = a.Type_Of_Work
checkInTime = a.Check_In_Date_Time
checkInLocation = a.Shop_Number
memberCheckedIn = True
#print('data in tblMember_Activity table: villageID - ', villageID, ' checkInTime - ',checkInTime)
#Is member already checked in?
est = timezone('America/New_York')
if memberCheckedIn:
processCheckOut(recordID)
# WAS MEMBER CHECKED INTO THIS LOCATION? IF SO, CHECK THEM OUT AND RETURN TO INPUT PROMPT
if checkInLocation == shopNumber:
est = timezone('America/New_York')
member = db.session.query(Member).filter(
Member.Member_ID == villageID).first()
memberName = member.First_Name + " " + member.Last_Name
response_body = {
"status": "Check Out",
"memberName": memberName,
"checkInTime": checkInTime.strftime('%I:%M %p'),
"checkOutTime":
datetime.datetime.now(est).strftime('%I:%M %p'),
"typeOfWork": typeOfWorkAtCheckIn,
"note": ""
}
res = make_response(jsonify(response_body), 200)
return (res)
# IF MEMBER WAS CHECKED IN TO ANOTHER LOCATION OR WAS NOT CHECKED IN
# THEN CONTINUE WITH CHECK IN ROUTINE
typeOfWorkOverride = requestData.get("typeOfWork")
# Retrieve member name, certifications, restrictions, notes
sqlSelect = "SELECT Member_ID, First_Name, Last_Name, NonMember_Volunteer, Certified, Certified_2,Default_Type_Of_Work, "
sqlSelect += "Restricted_From_Shop, Reason_For_Restricted_From_Shop, noteToMember, "
sqlSelect += "Villages_Waiver_Signed, Temporary_ID_Expiration_Date "
sqlSelect += "FROM tblMember_Data LEFT JOIN notesToMembers ON tblMember_Data.Member_ID = notesToMembers.memberID "
sqlSelect += "WHERE tblMember_Data.Member_ID='" + villageID + "'"
try:
member = db.engine.execute(sqlSelect)
except SQLAlchemyError as e:
error = str(e.__dict__['orig'])
response_body = {"status": "Member Not in database.", "note": "None"}
res = make_response(jsonify(response_body), 200)
return (res)
row = 0
for m in member:
row += 1
villageID = m.Member_ID
memberName = m.First_Name + " " + m.Last_Name
typeOfWorkToUse = "General"
if (m.Default_Type_Of_Work != None and m.Default_Type_Of_Work != ''):
typeOfWorkToUse = m.Default_Type_Of_Work
if (typeOfWorkOverride != ""):
typeOfWorkToUse = typeOfWorkOverride
certified1 = m.Certified
certified2 = m.Certified_2
restricted = m.Restricted_From_Shop
reasonRestricted = m.Reason_For_Restricted_From_Shop
volunteer = m.NonMember_Volunteer
# CHECK FOR VILLAGES WAIVER NOT SIGNED
if (m.Villages_Waiver_Signed != True):
restricted = True
reasonRestricted += "\nThe Villages Waiver form has not been signed."
# CHECK FOR EXPIRED TEMPORARY VILLAGE ID
est = timezone('America/New_York')
curDateTime = datetime.datetime.now()
emptyDate = datetime.datetime(1900, 1, 1, 0, 0)
if m.Temporary_ID_Expiration_Date != None \
and m.Temporary_ID_Expiration_Date != '' \
and m.Temporary_ID_Expiration_Date != emptyDate:
if (m.Temporary_ID_Expiration_Date < curDateTime):
restricted = True
reasonRestricted = "\nYour Village ID is no longer valid."
# Were any records found?
if row == 0:
response_body = {"status": "Not Found", "note": "None"}
res = make_response(jsonify(response_body), 200)
return (res)
# Member record was found
# Is their a note for this member?
note = db.session.query(NotesToMembers.noteToMember).filter(
NotesToMembers.memberID == villageID).first()
if note == None:
note = "None"
# Is member restricted?
if restricted:
response_body = {
"status": "Restricted",
"memberName": memberName,
"reason": reasonRestricted,
"note": note
}
res = make_response(jsonify(response_body), 200)
return (res)
# Is member certified?
if shopNumber == 1 and not certified1 and not volunteer:
response_body = {
"status": "Not Certified",
"msg": "Not certified for ROLLING ACRES",
"note": note
}
res = make_response(jsonify(response_body), 200)
return (res)
if shopNumber == 2 and not certified2 and not volunteer:
response_body = {
"status": "Not Certified",
"msg": "Not certified for BROWNWOOD",
"note": note
}
res = make_response(jsonify(response_body), 200)
return (res)
# MEMBER, OR VOLUNTEER, IS NOT RESTRICTED
# AND THEY ARE NOT ALREADY CHECKED IN TO EITHER LOCATION,
# SO THEY MAY BE CHECKED IN
# if not restricted:
# # Retrieve today's check in record, if any, for this member
# todaysDate = date.today()
# sqlCheckInRecord = "SELECT ID, Member_ID, Check_In_Date_Time, Check_Out_Date_Time, "
# sqlCheckInRecord += "Type_Of_Work, Shop_Number "
# sqlCheckInRecord += "FROM tblMember_Activity "
# sqlCheckInRecord += "WHERE Member_ID = '" + villageID + "' "
# sqlCheckInRecord += "AND Check_Out_Date_Time Is Null "
# sqlCheckInRecord += "AND Format(Check_In_Date_Time,'yyyy-MM-dd') = '" + str(todaysDate) + "' "
# #sqlCheckInRecord += "AND Shop_Number = '" + str(shopNumber) + "'"
# # Look for current checkin in the table tblMember_Activity
# memberCheckedIn = False
# typeOfWorkAtCheckIn=""
# activity = db.engine.execute(sqlCheckInRecord)
# for a in activity:
# recordID = a.ID
# typeOfWorkAtCheckIn = a.Type_Of_Work
# checkInTime = a.Check_In_Date_Time
# checkInLocation = a.Shop_Number
# memberCheckedIn = True
# #Is member checked in?
# est = timezone('America/New_York')
# if not memberCheckedIn:
processCheckIn(villageID, typeOfWorkToUse, shopNumber)
response_body = {
"status": "Check In",
"memberName": memberName,
"checkInTime": datetime.datetime.now(est).strftime('%I:%M %p'),
"typeOfWork": typeOfWorkToUse,
"note": note
}
res = make_response(jsonify(response_body), 200)
return (res)
def prtClassList():
specifiedSection = request.args.get('sectionNumber')
destination = request.args.get('destination')
todays_date = date.today()
displayDate = todays_date.strftime('%-B %d, %Y')
term = db.session.query(ControlVariables.Current_Course_Term).filter(ControlVariables.Shop_Number == 1).scalar()
# BUILD CLASS LISTS ARRAY FOR THE CURRENT TERM
classListDict = []
classListItems = []
try:
sp = "EXEC classLists '" + term + "'"
sql = SQLQuery(sp)
classLists = db.engine.execute(sql)
except (SQLAlchemyError, DBAPIError) as e:
errorMsg = "ERROR retrieving classLists "
flash(errorMsg,'danger')
return 'ERROR in classList list build.'
if classLists == None:
flash('There are no classLists available for this term.','info')
else:
for classList in classLists:
memberName = classList.lastName + ', ' + classList.firstName
if classList.nickName != None and classList.nickName != '':
memberName += ' (' + classList.nickName + ')'
sectionName = classList.courseNumber + '-' + classList.sectionID
classListItems = {
'memberName':memberName,
'sectionName':sectionName,
'dateEnrolled':classList.dateEnrolled.strftime('%m-%d-%Y'),
'homePhone':classList.homePhone,
'cellPhone':classList.cellPhone,
'eMail':classList.eMail
}
if sectionName == specifiedSection:
classListDict.append(classListItems)
courseNumber = specifiedSection[0:4]
sectionID = specifiedSection[5:6]
courseTitle = db.session.query(Course.Course_Title).filter(Course.Course_Number == courseNumber).scalar()
instructorName = 'Not assigned.'
instructorEmail = ''
classDates = 'N/A'
classTimes = ''
maxSize = ''
enrolled = ''
available = ''
section = db.session.query(CourseOffering)\
.filter(CourseOffering.Course_Term == term)\
.filter(CourseOffering.Course_Number == courseNumber)\
.filter(CourseOffering.Section_ID == sectionID).first()
if section:
instructorID = section.Instructor_ID
member = db.session.query(Member).filter(Member.Member_ID == instructorID).first()
if member:
instructorName = member.First_Name
if member.Nickname != None and member.Nickname != '':
instructorName += ' (' + member.Nickname + ')'
instructorName += " " + member.Last_Name
instructorEmail = member.eMail
classDates = section.Section_Dates
classTimes = section.Section_Dates_Note
maxSize= section.Section_Size
enrolled = db.session.query(func.count(CourseEnrollee.Member_ID))\
.filter(CourseEnrollee.Course_Term == term)\
.filter(CourseEnrollee.Course_Number == courseNumber)\
.filter(CourseEnrollee.Section_ID == sectionID).scalar()
available = maxSize - enrolled
else:
response = 'Section " + courseNumber + sectionID + " not found. Process aborted.'
return make_response (f"{response}")
html = render_template("rptClassList.html",enrolleeDict=classListDict,\
sectionNumber=specifiedSection,courseTitle=courseTitle,\
instructor=instructorName,classDates=classDates,classTimes=classTimes,\
maxSize=maxSize,enrolled=enrolled,available=available,displayDate=displayDate)
if destination != 'PDF':
return html
# CREATE PDF FROM HTML VARIABLE
# use rptClassListPDF for creating PDF; it has no external CSS file
html = render_template("rptClassListPDF.html",enrolleeDict=classListDict,\
sectionNumber=specifiedSection,courseTitle=courseTitle,\
instructor=instructorName,classDates=classDates,classTimes=classTimes,\
maxSize=maxSize,enrolled=enrolled,available=available,displayDate=displayDate)
currentWorkingDirectory = os.getcwd()
pdfDirectoryPath = currentWorkingDirectory + "/app/static/pdf"
filePath = pdfDirectoryPath + "/rptClassList.pdf"
# GET EITHER 'pdfkit' OR 'headless' FROM .env FILE
pdf_api = app.config['PDF_API']
if pdf_api == 'headless':
options = {
'quiet':''
}
ret = headless_pdfkit.generate_pdf(html, options=options)
with open(filePath, 'wb') as w:
w.write(ret)
else:
options = {
'enable-local-file-access': None,
'quiet':'',
'print-media-type':''
}
pdfkit.from_string(html,filePath, options=options)
# GET RECIPIENT
cc = ''
subject = 'Class list for ' + specifiedSection
if instructorEmail == None or instructorEmail == '':
response = "ERROR - Missing email address for member ID " + instructorID + ". Mail not sent."
return make_response (f"{response}")
recipient = instructorEmail
recipientList = []
recipientList.append(recipient)
message = 'Attached is a list of the members enrolled in ' + specifiedSection + '.'
# SMTPLIB approach; call function sendMail()
response = sendMail(recipient, subject, message, filePath, html)
if (response == "ERROR - Message could not be sent."):
return make_response (f"{response}")
else:
flash("Email sent.",'success')
return redirect(url_for('ClassLists'))
def ClassLists():
destination = request.args.get('destination')
todays_date = datetime.today()
displayDate = todays_date.strftime('%-B %d, %Y')
term = db.session.query(ControlVariables.Current_Course_Term).filter(ControlVariables.Shop_Number == 1).scalar()
# BUILD COURSE OFFERING ARRAY FOR THE CURRENT TERM
offeringDict = []
offeringItems = []
try:
sp = "EXEC offerings '" + term + "'"
sql = SQLQuery(sp)
offerings = db.engine.execute(sql)
except (SQLAlchemyError, DBAPIError) as e:
errorMsg = "ERROR retrieving offerings "
flash(errorMsg,'danger')
return 'ERROR in offering list build.'
if offerings == None:
flash('There are no courses offerings for this term.','info')
else:
for offering in offerings:
# GET CLASS SIZE LIMIT
capacity = offering.Section_Size
seatsAvailable = capacity - offering.seatsTaken
if (offering.Section_Closed_Date == None):
statusClosed = ''
else:
if (todays_date >= offering.Section_Closed_Date):
statusClosed = 'C'
else:
statusClosed = ''
seatsAvailable = capacity - offering.seatsTaken
if (seatsAvailable > 0):
statusFull = ''
else:
statusFull = 'F'
fee = offering.courseFee
if (offering.datesNote == None):
datesNote = ''
else:
datesNote = 'Meets - ' + offering.datesNote
if (offering.prereq == None):
prereq = ''
else:
prereq = offering.prereq
offeringItems = {
'sectionName':offering.courseNumber + '-' + offering.sectionID,
'term':term,
'courseNumber':offering.courseNumber,
'title':offering.title,
'instructorName':offering.instructorName,
'dates':offering.Section_Dates,
'notes':datesNote,
'capacity':capacity,
'seatsTaken':offering.seatsTaken,
'seatsAvailable':seatsAvailable,
'fee':fee,
'prereq':prereq,
'supplies':offering.Section_Supplies,
'suppliesFee':offering.Section_Supplies_Fee,
'fullMsg':statusFull,
'closedMsg':statusClosed
}
# if statusFull != 'F' and statusClosed != 'C':
offeringDict.append(offeringItems)
return render_template("classLists.html",offeringDict=offeringDict,term=term,displayDate=displayDate)
def index():
shopID = getShopID()
staffID = getStaffID()
# GET CURRENT TERM
term = db.session.query(ControlVariables.Current_Course_Term).filter(ControlVariables.Shop_Number == 1).scalar()
# BUILD ARRAY OF NAMES FOR DROPDOWN LIST OF MEMBERS
nameArray=[]
sqlSelect = "SELECT Last_Name, First_Name, Member_ID FROM tblMember_Data "
sqlSelect += "ORDER BY Last_Name, First_Name "
nameList = db.engine.execute(sqlSelect)
position = 0
for n in nameList:
position += 1
lastFirst = n.Last_Name + ', ' + n.First_Name + ' (' + n.Member_ID + ')'
nameArray.append(lastFirst)
# BUILD COURSE OFFERING ARRAY FOR THE CURRENT TERM
offeringDict = []
offeringItems = []
try:
sp = "EXEC offerings '" + term + "'"
sql = SQLQuery(sp)
offerings = db.engine.execute(sql)
except (SQLAlchemyError, DBAPIError) as e:
errorMsg = "ERROR retrieving offerings "
flash(errorMsg,'danger')
return 'ERROR in offering list build.'
if offerings == None:
flash('There are no courses offerings for this term.','info')
else:
for offering in offerings:
# GET CLASS SIZE LIMIT
capacity = offering.Section_Size
seatsAvailable = capacity - offering.seatsTaken
if (offering.Section_Closed_Date):
statusClosed = 'C'
else:
statusClosed = ''
seatsAvailable = capacity - offering.seatsTaken
if (seatsAvailable > 0):
statusFull = ''
else:
statusFull = 'F'
fee = offering.courseFee
if (offering.datesNote == None):
datesNote = ''
else:
datesNote = 'Meets - ' + offering.datesNote
if (offering.prereq == None):
prereq = ''
else:
prereq = offering.prereq
offeringItems = {
'sectionName':offering.courseNumber + '-' + offering.sectionID,
'term':term,
'courseNumber':offering.courseNumber,
'title':offering.title,
'instructorName':offering.instructorName,
'dates':offering.Section_Dates,
'notes':datesNote,
'capacity':capacity,
'seatsTaken':offering.seatsTaken,
'seatsAvailable':seatsAvailable,
'fee':fee,
'prereq':prereq,
'supplies':offering.Section_Supplies,
'suppliesFee':offering.Section_Supplies_Fee,
}
if statusFull != 'F' and statusClosed != 'C':
offeringDict.append(offeringItems)
# RECENT TRAINING DATES, 30 DAYS OR LESS
firstWeek = date.today() - timedelta(30)
firstTrainingDate = firstWeek.strftime('%m-%d-%Y')
# now showing just RA via Last_Monitor_Training;
# need second dropdown for BW Last_Monitor_Training_Shop_2
sqlTrainingDates = "SELECT Last_Monitor_Training, format(Last_Monitor_Training,'MMM d, yyyy') AS displayDate "
sqlTrainingDates += "FROM tblMember_Data "
sqlTrainingDates += "WHERE Last_Monitor_Training >= '" + firstTrainingDate + "' "
sqlTrainingDates += "GROUP BY Last_Monitor_Training "
sqlTrainingDates += "ORDER BY Last_Monitor_Training"
trainingDates = db.engine.execute(sqlTrainingDates)
return render_template("index.html",nameList=nameArray,offeringDict=offeringDict,\
trainingDates=trainingDates,shopID=shopID,courseTerm=term)