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