class AddCompetence(FlaskForm):
title = StringField("Title", [DataRequired("Enter a Title")])
scope = StringField("Scope", [DataRequired("Enter a Scope")])
creator_id = StringField("Author Name", [DataRequired("Enter an Author Name")])
validity_period = QuerySelectField("Validity Period", query_factory=lambda:s.query(ValidityRef).all(), get_label="months")
competency_type= QuerySelectField("Competence Category", query_factory=lambda:s.query(CompetenceCategory).all(), get_label="category")
approval = StringField("Authoriser")
documents = QuerySelectMultipleField("Associated Documents",query_factory=lambda:s.query(Documents).all(), get_label="qpulse_no")
add_document = StringField("Add Related Document", [DataRequired("Enter a Q-Pulse Document Number")])
submit = SubmitField()
class AddSection(FlaskForm):
add_h_and_s = StringField("Add Health and Safety Hazard")
choose_section = QuerySelectField("Add Section" ,query_factory=lambda:s.query(Section).filter(Section.constant == 0).all(), get_label="name") #All sections in database
choose_constant_section = QuerySelectField("Add Constant Section",
query_factory=lambda: s.query(Section).filter(Section.constant == 1).all(),
get_label="name") # All sections in database
competence_sections = QuerySelectField("Add Section" ,query_factory=lambda:s.query(Section).all(), get_label="name") #All sections that are added for that specific competency
submit = SubmitField()
constant_section=QuerySelectMultipleField("Test")
add_constant_subsection = StringField("Add new item")
class UploadEvidence(FlaskForm):
"""
For to submit evidence for a competence
"""
file = FileField('Upload Evidence')
#TODO edit this so 'Inactivation Request' doesn't show up as an evidence upload type
evidence_type = QuerySelectField("What type of evidence do you want to send?",allow_blank=True, blank_text=u'-- please choose --',
query_factory=lambda: s.query(EvidenceTypeRef).all(),
get_label="type") # All sections in database
trainer = SelectField(label="Who was your trainer?")
datecompleted = DateField('What date did you attain this evidence?',
format='%Y-%m-%d', default=datetime.date.today)
datetrained = DateField('What date did you train?',
format='%Y-%m-%d', default=datetime.date.today)
assessor = SelectField(label="Who will sign-off this evidence?")
evidence_observation = TextAreaField(label="Evidence")
evidence_discussion = TextAreaField(label="Evidence")
case = StringField(label="Case")
result = StringField(label="Result")
fouryearstatus = RadioField("Is this evidence being submitted for a four year reassessment?",
choices=[(1, 'Yes'), (0, 'No')],
validators=[DataRequired()]
)
assid = HiddenField("AssesmentID")
submit = SubmitField('Submit Evidence')
class EditEvent(FlaskForm):
#TODO less tenous workaround to pre-populate the forms while editing
"""
Form to add CPD event
"""
event_name = StringField("Name")
event_type = QuerySelectField(
"Type",
query_factory=lambda: s.query(EventTypeRef).all(),
get_label="type")
date = DateField('Date', format='%Y-%m-%d', default=datetime.date.today)
role = QuerySelectField("Participation",
query_factory=lambda: s.query(EventRoleRef).all(),
get_label="role")
location = StringField("Location")
cpd_points = StringField("CPD points (if known)")
comments = TextAreaField("Description/Comments")
submit = SubmitField()
class UserForm(FlaskForm):
username = StringField("User Name", [DataRequired("Enter a Username")])
firstname = StringField("First Name", [DataRequired("Enter a Username")])
surname = StringField("Surname", [DataRequired("Enter a Username")])
email = StringField("Email", [DataRequired("Enter a Username")])
staff_no = StringField("Staff Number", [DataRequired("Enter a Username")])
linemanager = StringField("Line Manager")
jobrole = QuerySelectMultipleField(
"Job Role",
query_factory=lambda: s.query(JobRoles).all(),
get_label="job")
userrole = QuerySelectMultipleField(
"User Role",
query_factory=lambda: s.query(UserRolesRef).all(),
get_label="role")
section = QuerySelectField("Section",
query_factory=lambda: s.query(Service).all(),
get_label="name")
submit = SubmitField()
def get_doc_info(c_id, version):
"""
Method to get doc infor from database
:param c_id: ID of competence to be returned
:type c_id: INT
:return:
"""
competence_list = s.query(CompetenceDetails).\
filter(CompetenceDetails.c_id == c_id).filter(CompetenceDetails.intro == version).first()
return competence_list
def export_document_view():
"""
View to export competence to PDF
:return:
"""
if request.method == 'GET':
c_id = request.args.get('c_id')
version = request.args.get('version')
outfile = export_document(c_id, version)
uploads = app.config["UPLOAD_FOLDER"]
filename = s.query(CompetenceDetails).filter(CompetenceDetails.c_id==c_id).first().title + ".pdf"
return send_from_directory(directory=uploads, path=outfile, as_attachment=True, attachment_filename=filename)
class AddEvent(FlaskForm):
"""
Form to add CPD event
"""
event_name = StringField("Name")
event_type = QuerySelectField(
"Type",
query_factory=lambda: s.query(EventTypeRef).all(),
get_label="type",
allow_blank=True,
blank_text="---Please Choose---")
#event_type = QuerySelectField("Validity Period", query_factory=lambda: s.query(ValidityRef).all(), get_label="months")
date = DateField('Date', format='%Y-%m-%d', default=datetime.date.today)
role = QuerySelectField("Participation",
query_factory=lambda: s.query(EventRoleRef).all(),
get_label="role",
allow_blank=True,
blank_text="---Please Choose---")
#role = QuerySelectField("Validity Period", query_factory=lambda:s.query(ValidityRef).all(), get_label="months")
location = StringField("Location")
cpd_points = StringField("CPD points (if known)")
comments = TextAreaField("Description/Comments")
submit = SubmitField()
def get_qpulsenums(c_id, version):
# qpulse_no_list = s.query(Documents).\
# filter(Documents.c_id == c_id).\
# values(Documents.qpulse_no)
qpulse_no_list = s.query(Documents). \
join(CompetenceDetails). \
filter(CompetenceDetails.intro == version). \
filter(CompetenceDetails.c_id == c_id)
doc_list = []
for i in qpulse_no_list:
doc_list.append(i.qpulse_no)
return doc_list
def get_subsections(c_id):
"""
Method to get section and subsection info from database
:param c_id: ID of competence to be returned
:type c_id: INT
:return:
"""
subsections = s.query(Subsection). \
join(Section). \
join(SectionSortOrder). \
join(Competence).\
filter(Subsection.c_id == c_id).\
order_by(SectionSortOrder.sort_order.asc()).\
order_by(Subsection.sort_order.asc()). \
values(Subsection.name.label('subsec_name'),
Subsection.comments,
Section.name.label('sec_name'),
Section.id,
Section.constant,
Subsection.evidence)
subsection_list = []
for i in subsections:
subsection_list.append(i)
return subsection_list
def export_trial_report():
ids = [int(i) for i in request.args.get('ids').split(",")]
uploads = app.config["UPLOAD_FOLDER"]
current_data = s.query(CompetenceDetails).join(Competence).filter(CompetenceDetails.c_id.in_(ids)).filter(
Competence.current_version == CompetenceDetails.intro).all()
result = {}
for i in current_data:
print(i.title)
#find out how many are trained and partially and in training
#count how many subsections are in the competence
number_of_subsections = s.query(Subsection).\
join(Competence).\
filter(and_(Subsection.intro <= Competence.current_version,or_(Subsection.last >= Competence.current_version,Subsection.last == None))).\
filter(Subsection.c_id == i.c_id).count()
#get all assessments by user?
counts = s.query(func.count(Assessments.id).label("count"),
Assessments.user_id.label("user_id"),
Assessments.status.label("status_id"),
AssessmentStatusRef.status.label("status")).\
join(AssessmentStatusRef).\
join(Subsection).\
join(Competence).\
join(CompetenceDetails).\
filter(and_(CompetenceDetails.intro <= Competence.current_version,
or_(CompetenceDetails.last >= Competence.current_version,
CompetenceDetails.last == None))).\
filter(Subsection.c_id == i.c_id).\
filter(Assessments.ss_id == Subsection.id).\
group_by(Assessments.user_id).all()
trained=0
partial=0
in_training=0
expired=0
for user_entry in counts: ### loop through users that are on this competence
if (s.query(Users). \
filter(Users.id == user_entry.user_id). \
first()). \
active == 1: ### check if user is actually active
print("User "+str(user_entry.user_id))
statuses = []
### get all assessments for this user for this competence, put statuses in list
users_assessments = s.query(Assessments). \
join(Subsection). \
filter(Assessments.user_id == user_entry.user_id). \
filter(Subsection.c_id == i.c_id). \
all()
for entry in users_assessments:
if entry.status == 3: ### assessment is complete, check for expiry
if datetime.date.today() > entry.date_expiry:
statuses.append('Expired')
else:
statuses.append('Complete')
elif entry.status == 1 or entry.status == 7: ### assessment is active or waiting for sign-off
statuses.append('In training')
### from statuses list, decide on overall training status for this user for this competence, and add to counts
if "In training" in statuses:
in_training += 1
elif "Expired" in statuses:
expired+=1
elif "Complete" in statuses:
if len(statuses) == int(number_of_subsections):
trained+=1
elif len(statuses) < int(number_of_subsections):
partial+=1
#NEED TO TAKE INTO ACCOUNT EXPIRY
#Counter(z)
print(i.title)
print(trained)
print(expired)
print(partial)
print(in_training)
result[i.c_id]={"title":i.title,
"trained":trained,
"expired":expired,
"partial":partial,
"training":in_training,
"category":i.category_rel.category}
html_out = render_template('trial_view.html',result=result)
html = HTML(string=html_out)
main_doc = html.render(stylesheets=[CSS('static/css/simple_report_portrait.css')])
exists_links = False
# Add headers and footers
# header = html.render(stylesheets=[CSS(string='div {position: fixed; top: 1cm; left: 1cm;}')])
header_out = render_template('trial_view_header.html')
header_html = HTML(string=header_out,base_url=request.url)
header = header_html.render(
stylesheets=[CSS('static/css/simple_report_portrait.css'), CSS(string='div {position: fixed; top: -2.7cm; left: 0cm;}')])
header_page = header.pages[0]
exists_links = exists_links or header_page.links
header_body = get_page_body(header_page._page_box.all_children())
header_body = header_body.copy_with_children(header_body.all_children())
# Template of footer
footer_out = render_template('trial_view_footer.html',user=current_user.full_name,date=str(datetime.datetime.now()))
footer_html = HTML(string=footer_out)
footer = footer_html.render(stylesheets=[CSS('static/css/simple_report_portrait.css'),
CSS(string='div {position: fixed; bottom: -2.1cm; left: 0cm;}')])
footer_page = footer.pages[0]
exists_links = exists_links or footer_page.links
footer_body = get_page_body(footer_page._page_box.all_children())
footer_body = footer_body.copy_with_children(footer_body.all_children())
# Insert header and footer in main doc
for page in main_doc.pages:
page_body = get_page_body(page._page_box.all_children())
page_body.children += header_body.all_children()
page_body.children += footer_body.all_children()
if exists_links:
page.links.extend(header_page.links)
page.links.extend(footer_page.links)
outfile = str(uuid.uuid4())
out_name = main_doc.write_pdf(target=app.config["UPLOAD_FOLDER"] + "/" + outfile)
return send_from_directory(directory=uploads, path=outfile, as_attachment=True, attachment_filename="training_report.pdf")
def export_document(c_id, version):
"""
Method to export a blank competence document to PDF
:param c_id: ID of competence to be returned
:type c_id: INT
:return:
"""
document = Document()
# Get variables using queries
#comp = get_doc_info(c_id)
#subsec = competence_views.get_subsections(c_id, v)
#qpulse = get_qpulsenums(c_id)
## Get variables using competence_view query from mod_competence views
# v = get_latest_version(c_id)[0]
comp = get_doc_info(c_id, version)
const = competence_views.get_constant_subsections(c_id, version)
subsec = competence_views.get_subsections(c_id, version)
if config.get("QPULSE_MODULE") == True:
qpulse = get_qpulsenums(c_id, version)
print(f"QPulse: {qpulse}")
# Competence details
title = comp.title
# print "Qpulse number:"
# print comp.qpulsenum
# if config.get("QPULSE_MODULE") == True:
# docid = comp.qpulsenum
# else:
# docid = None
version_no = version
author = comp.creator_rel.first_name + ' ' + comp.creator_rel.last_name
authoriser = comp.approve_rel.first_name + ' ' + comp.approve_rel.last_name
scope = comp.scope
if comp.date_of_approval is not None:
date_of_issue = comp.date_of_approval.strftime("%d-%m-%Y")
else:
date_of_issue = "Not Approved"
# subsection details
subsection = OrderedDict()
for sub in subsec: ## This is each non-constant subsection
sec_name = sub.sec_name
subsection_name = sub.subsec_name
comments = sub.comments
evidence_type = sub.type
value_list = [subsection_name, comments, evidence_type]#, sub_version,constant, sub_id]
subsection.setdefault(sec_name,[]).append(value_list)
#constant section details
constant = OrderedDict()
for c in const: # this is each constant subsection
sec_type = c.sec_name #section name
sec_name = c.subsec_name#name,
sec_comments = c.comments
evidence = c[3]
value_list = [sec_name, sec_comments, evidence]
constant.setdefault(sec_type,[]).append(value_list)
#associated qpulse documents
qpulse_list = {}
if config.get("QPULSE_MODULE") == True:
for qpulse_no in qpulse:
#print(qpulse_no)
d = s.query(QPulseDetails).first()
qpulse_name = QPulseWeb().get_doc_by_id(d.username, d.password, qpulse_no)
if qpulse_name == "False":
qpulse_name = "There has been a problem locating this document in the Active register. " \
"Please contact the competence owner."
#print(qpulse_name)
qpulse_list[qpulse_no]=qpulse_name
#print(qpulse_list)
# evidence - this will be for downloading completed competences
evidence_list = {}
print('***Rendering main document***')
# Make main document
html_out = render_template('export_to_pdf.html', title=title, validity_period=comp.validity_rel.months, scope=scope, docid=c_id ,version_no=version_no, author=author, full_name=current_user.full_name, subsection=subsection, constant=constant, qpulse_list=qpulse_list)
html = HTML(string=html_out)
main_doc = html.render(stylesheets=[CSS('static/css/simple_report.css')])
exists_links = False
# Insert header and footer in main doc
for page in main_doc.pages:
header_out = render_template('header.html', title=title, docid=c_id)
header_html = HTML(string=header_out)
header = header_html.render(stylesheets=[CSS('static/css/simple_report.css'),
CSS(string='div {position: fixed; top: -2.7cm; left: 0cm;}')])
header_page = header.pages[0]
# exists_links = exists_links or header_page.links
exists_header_links = header_page.links
header_body = get_page_body(header_page._page_box.all_children())
header_body = header_body.copy_with_children(header_body.all_children())
# Template of footer
footer_out = render_template('footer.html', version_no=version_no, author=author,
full_name=current_user.full_name,
authoriser=comp.approve_rel.first_name + " " + comp.approve_rel.last_name,
date_of_issue=date_of_issue)
footer_html = HTML(string=footer_out)
footer = footer_html.render(stylesheets=[CSS('static/css/simple_report.css'),
CSS(string='div {position: fixed; bottom: -2.1cm; left: 0cm;}')])
footer_page = footer.pages[0]
exists_footer_links = footer_page.links
footer_body = get_page_body(footer_page._page_box.all_children())
footer_body = footer_body.copy_with_children(footer_body.all_children())
page_body = get_page_body(page._page_box.all_children())
page_body.children += header_body.all_children()
page_body.children += footer_body.all_children()
# if exists_links:
# page.links.extend(header_page.links)
# page.links.extend(footer_page.links)
page.links.extend(header_page.links)
page.links.extend(footer_page.links)
outfile = str(uuid.uuid4())
out_name = main_doc.write_pdf(target=app.config["UPLOAD_FOLDER"]+"/"+ outfile)
return outfile
class SectionForm(FlaskForm):
subsection_name = StringField("Name")
subsection_evidencetype = QuerySelectField("Evidence Type", query_factory=lambda: s.query(EvidenceTypeRef).all(),
get_label="type") # This belongs to a subsection
subsection_comments = TextAreaField("Comment")
def index():
"""
populates the head of service page
return: index template for head of service
"""
"""Get the service name"""
service_name_query = s.query(Service) \
.join(Users, Users.id == Service.head_of_service_id) \
.filter(Service.head_of_service_id == int(current_user.database_id)) \
.first()
if service_name_query is None:
service_name = "Service Name Not Found - please contact bioinformatics"
else:
service_name = service_name_query.name
"""Get data for service employees"""
linereports = s.query(Users) \
.join(Service, Service.id == Users.serviceid) \
.filter(Service.name == service_name) \
.filter(Users.active == 1) \
.all()
counts = {}
active_count = 0
assigned_count = 0
complete_count = 0
abandoned_count = 0
signoff_count = 0
failed_count = 0
expiring_count = 0
expired_count = 0
obsolete_count = 0
not_required_count = 0
for i in linereports:
counts[i.id] = {}
# Assigned competencies by staff member
counts[i.id]["assigned"] = len(
s.query(Assessments) \
.join(AssessmentStatusRef) \
.filter(Assessments.user_id == i.id) \
.filter(AssessmentStatusRef.status == "Assigned") \
.all()
)
assigned_count += counts[i.id]["assigned"]
# Active competencies by staff member
counts[i.id]["active"] = len(
s.query(Assessments) \
.join(AssessmentStatusRef) \
.filter(Assessments.user_id == i.id) \
.filter(AssessmentStatusRef.status == "Active") \
.all()
)
active_count += counts[i.id]["active"]
# Signed-off competencies by staff member
counts[i.id]["sign-off"] = len(
s.query(Assessments) \
.join(AssessmentStatusRef) \
.filter(Assessments.user_id == i.id) \
.filter(AssessmentStatusRef.status == "Sign-Off") \
.all()
)
signoff_count += counts[i.id]["sign-off"]
# Complete competencies by staff member
counts[i.id]["complete"] = len(
s.query(Assessments) \
.join(AssessmentStatusRef) \
.filter(Assessments.user_id == i.id) \
.filter(AssessmentStatusRef.status == "Complete") \
.all()
)
complete_count += counts[i.id]["complete"]
# Failed competencies by staff member
counts[i.id]["failed"] = len(
s.query(Assessments) \
.join(AssessmentStatusRef) \
.filter(Assessments.user_id == i.id) \
.filter(AssessmentStatusRef.status == "Failed") \
.all()
)
failed_count += counts[i.id]["failed"]
# Obsolete competencies by staff member
counts[i.id]["obsolete"] = len(
s.query(Assessments) \
.join(AssessmentStatusRef) \
.filter(Assessments.user_id == i.id) \
.filter(AssessmentStatusRef.status == "Obsolete") \
.all()
)
obsolete_count += counts[i.id]["obsolete"]
# Abandoned competencies by staff member
counts[i.id]["abandoned"] = len(
s.query(Assessments) \
.join(AssessmentStatusRef) \
.filter(Assessments.user_id == i.id) \
.filter(AssessmentStatusRef.status == "Abandoned") \
.all()
)
abandoned_count += counts[i.id]["abandoned"]
# Not required competencies by staff member
counts[i.id]["not_required"] = len(
s.query(Assessments). \
join(AssessmentStatusRef). \
filter(Assessments.user_id == i.id). \
filter(AssessmentStatusRef.status == "Not Required"). \
all()
)
not_required_count += counts[i.id]["not_required"]
# Find expired and expiring competencies
alerts = {}
alerts["Assessments"] = {}
for i in linereports:
assessments = s.query(Assessments) \
.filter(Assessments.user_id == i.id) \
.all()
for j in assessments:
if j.date_expiry is not None:
# Find expired assessments
if datetime.date.today() > j.date_expiry:
if "expired" not in counts[i.id]:
counts[i.id]["expired"] = 0
counts[i.id]["expired"] += 1
else:
counts[i.id]["expired"] += 1
expired_count += 1
# Find expiring assessments (within 1 month)
elif datetime.date.today() + relativedelta(
months=+1) > j.date_expiry:
if "expiring" not in counts[i.id]:
counts[i.id]["expiring"] = 0
counts[i.id]["expiring"] += 1
else:
counts[i.id]["expiring"] += 1
expiring_count += 1
#TODO is this needed?
# Find complete and incomplete competencies
competencies_incomplete = s.query(CompetenceDetails) \
.join(Competence) \
.filter(CompetenceDetails.creator_id == current_user.database_id) \
.filter(Competence.current_version != CompetenceDetails.intro) \
.filter(CompetenceDetails.date_of_approval == None) \
.all()
competencies_complete = s.query(CompetenceDetails) \
.join(Competence) \
.filter(CompetenceDetails.creator_id == current_user.database_id) \
.filter(Competence.current_version == CompetenceDetails.intro) \
.all()
#Find assigned competencies
assigned = s.query(Assessments) \
.join(Subsection) \
.join(Competence) \
.join(CompetenceDetails) \
.join(AssessmentStatusRef) \
.filter(Assessments.user_id == current_user.database_id) \
.group_by(Competence.id) \
.filter(or_(AssessmentStatusRef.status == "Assigned",
AssessmentStatusRef.status == "Active",
AssessmentStatusRef.status == "Sign-Off")) \
.all()
all_assigned = []
for k in assigned:
all_assigned.append(
get_competence_summary_by_user(c_id=k.ss_id_rel.c_id,
u_id=current_user.database_id,
version=k.version))
# Find active competencies
active = s.query(Assessments) \
.join(Subsection) \
.join(Competence) \
.join(CompetenceDetails) \
.join(AssessmentStatusRef) \
.group_by(CompetenceDetails.id) \
.filter(Assessments.user_id == current_user.database_id) \
.filter(CompetenceDetails.intro == Competence.current_version) \
.filter(AssessmentStatusRef.status == "Active") \
.all()
# Find complete competencies
complete = s.query(Assessments) \
.join(Subsection) \
.join(Competence) \
.join(CompetenceDetails) \
.join(AssessmentStatusRef) \
.filter(Assessments.user_id == current_user.database_id) \
.group_by(Subsection.c_id, Assessments.version) \
.filter(AssessmentStatusRef.status.in_(["Complete", "Four Year Due"])) \
.all()
all_complete = []
for l in complete:
result = get_competence_summary_by_user(c_id=l.ss_id_rel.c_id,
u_id=current_user.database_id,
version=l.version)
if result.completed != None:
all_complete.append(result)
# Find obsolete competencies
obsolete = s.query(Assessments) \
.join(Subsection) \
.join(Competence) \
.join(CompetenceDetails) \
.join(AssessmentStatusRef) \
.filter(Assessments.user_id == current_user.database_id) \
.filter(and_(CompetenceDetails.intro <= Assessments.version,
or_(CompetenceDetails.last >= Assessments.version,
CompetenceDetails.last == None))) \
.group_by(Competence.id) \
.filter(AssessmentStatusRef.status.in_(["Obsolete"])) \
.all()
return render_template('service_overview.html',
service_name=service_name,
expired_count=expired_count,
complete=all_complete,
obsolete=obsolete,
assigned_count=assigned_count,
active_count=active_count,
signoff_count=signoff_count,
failed_count=failed_count,
complete_count=complete_count,
linereports=linereports,
not_required_count=not_required_count,
abandoned_count=abandoned_count,
counts=counts,
assigned=all_assigned,
active=active,
expiring_count=expiring_count)
class AddSubsection(FlaskForm):
name = StringField("Area of Competence")
evidence = QuerySelectField("Evidence type", query_factory=lambda:s.query(EvidenceTypeRef).all(), get_label="type")
comments =StringField("Comments")
def get_latest_version(c_id):
version = s.query(Competence).filter(Competence.id==c_id).\
values(Competence.current_version)
for v in version:
return v
class EditCompetency(FlaskForm):
edit_title = StringField("Title")
edit_scope = StringField("Scope")
approval = StringField("Authoriser")
edit_validity_period = SelectField("Validity Period", coerce=int)
edit_competency_type = SelectField("Competence Category", coerce=int)
ass_documents = QuerySelectMultipleField("Associated Documents", query_factory=lambda:s.query(Documents).filter_by(c_id=18).all(), get_label="qpulse_no")
add_document = StringField("Add Related Document", [DataRequired("Enter a Q-Pulse Document Number")])
