System Design Specification



System Design SpecificationforEstates Operational ReportsEST092Version: 1.0Date: 04/11/16Contents TOC \o "1-3" \h \z 1Document Management PAGEREF _Toc466036037 \h 31.1Contributors PAGEREF _Toc466036038 \h 31.2Version Control PAGEREF _Toc466036039 \h 32OVERVIEW PAGEREF _Toc466036040 \h 42.1Description of terms PAGEREF _Toc466036041 \h 42.2Report delivery strategy PAGEREF _Toc466036042 \h 53DEVELOPMENT TOOLS AND STANDARDS PAGEREF _Toc466036043 \h 63.1Development Tools PAGEREF _Toc466036044 \h 63.2Development Standards PAGEREF _Toc466036045 \h 64Web Central reports OP03 and OP33 PAGEREF _Toc466036046 \h 74.1OP03: Reactive and Planned Maintenance Response Times PAGEREF _Toc466036047 \h 74.1.1Web Central navigation PAGEREF _Toc466036048 \h 74.1.2Web Central view files PAGEREF _Toc466036049 \h 74.1.3Example existing report PAGEREF _Toc466036050 \h 84.1.4Mock-up PAGEREF _Toc466036051 \h 104.1.5Input parameters PAGEREF _Toc466036052 \h 104.1.6Report output layout PAGEREF _Toc466036053 \h 114.1.7Calculating Response and Completion times PAGEREF _Toc466036054 \h 124.1.8RAG thresholds PAGEREF _Toc466036055 \h 124.1.9Reference: Original Business Analysis PAGEREF _Toc466036056 \h 134.2OP33: eIT Costs by Team PAGEREF _Toc466036057 \h 164.2.1Web Central navigation PAGEREF _Toc466036058 \h 164.2.2Web Central view files PAGEREF _Toc466036059 \h 164.2.3Example existing report PAGEREF _Toc466036060 \h 164.2.4Mock-up PAGEREF _Toc466036061 \h 164.2.5Input parameters PAGEREF _Toc466036062 \h 174.2.6Report output layout PAGEREF _Toc466036063 \h 184.2.7Reference: Original Business Analysis PAGEREF _Toc466036064 \h 195Python/Django report OP02: Search Work Requests PAGEREF _Toc466036065 \h 215.1Application Architecture Overview PAGEREF _Toc466036066 \h 215.2Authentication and Authorisation PAGEREF _Toc466036067 \h 215.3Front-end application PAGEREF _Toc466036068 \h 225.3.1Names PAGEREF _Toc466036069 \h 225.3.2Integrations PAGEREF _Toc466036070 \h 225.3.3Python dependencies PAGEREF _Toc466036071 \h 235.3.4Project structure PAGEREF _Toc466036072 \h 235.3.5User interface PAGEREF _Toc466036073 \h 245.3.6Reference data PAGEREF _Toc466036074 \h 275.3.7Reference: Original Business Analysis PAGEREF _Toc466036075 \h 285.4API PAGEREF _Toc466036076 \h 315.4.1Names PAGEREF _Toc466036077 \h 315.4.2Integrations PAGEREF _Toc466036078 \h 315.4.3Python dependencies PAGEREF _Toc466036079 \h 315.4.4Endpoints PAGEREF _Toc466036080 \h 326USER INTERFACE PAGEREF _Toc466036081 \h 376.1Transactional Interface PAGEREF _Toc466036082 \h 376.1.1OP02 (Django) PAGEREF _Toc466036083 \h 376.1.2OP03, OP33 (Web Central) PAGEREF _Toc466036084 \h 377APPLICATION SECURITY PAGEREF _Toc466036085 \h 387.1Authentication PAGEREF _Toc466036086 \h 387.2Authorisation PAGEREF _Toc466036087 \h 387.3Business Objects PAGEREF _Toc466036088 \h 388DATABASE DESIGN PAGEREF _Toc466036089 \h 399APPLICATION INTERFACES PAGEREF _Toc466036090 \h 4010DATA PAGEREF _Toc466036091 \h 4110.1Data Migration PAGEREF _Toc466036092 \h 4110.2Archiving Policy PAGEREF _Toc466036093 \h 4111IMPLEMENTATION PAGEREF _Toc466036094 \h 4211.1Build, CI, & Deployment PAGEREF _Toc466036095 \h 4211.1.1Web Central PAGEREF _Toc466036096 \h 4211.1.2Django PAGEREF _Toc466036097 \h 4211.2Version Control PAGEREF _Toc466036098 \h 4211.2.1Web Central PAGEREF _Toc466036099 \h 4211.2.2Django PAGEREF _Toc466036100 \h 42Document ManagementContributorsRoleUnitNameSystems Analyst Designer (Owner)Development ServicesJohn AllisonBusiness AnalystProject ServicesElaine WightonProject Manager Project ServicesNikki Hackett ReedProject Sponsor EstatesGrant FergusonBusiness Area Manager Other document contributorsVersion ControlDateVersionAuthorSectionAmendment12/10/160.1JAAllInitial draft25/10/160.2JAAllCompleted OP02 and OP0301/11/160.3JAAllAdded OP33There are many outstanding Todos which require clarification from customer.03/11/16JA5.3.5.3Clarification to WR status parameter04/11/161.0JA4Clarification to Request/Completion time calculations and RAG thresholds.Clarification to OP33 WR-PO relationship.Clarification to OP02 service request type.OVERVIEWThis project will deliver 3 operational reports.Report codeAreaUsersNameOP02HelpdeskUoE staffSearch Work RequestsOP03HelpdeskEstates staffReactive and Planned Maintenance Response TimesOP33FinanceEstates staffeIT Costs by TeamDescription of termsThere is often confusion around the terms used to describe the various Estates systems. This document uses the terms in the following way:ARCHIBUSThe full suite of facilities management software used by Estates, including the desktop client, Web Central, EBIS, and the AFM database.Also the name of the company who create and supply the ARCHIBUS product.EBISThe ColdFusion web application which was developed in-house in conjunction with Mass PLC. This application provides many of the reports and interfaces currently used by Estates.In other places, the term ‘EBIS’ is often used in a broader sense, to refer to the wider ARCHIBUS system as a whole.‘EBIS’ stands for ‘Estates and Buildings Information System’.ARCHIBUS Desktop ClientThe windows application which is currently used to deliver some reporting and processes.Web CentralThe official web application provided by ARCHIBUS. This is currently in use by some users for a limited set of functions.AFMThe single database which is shared by all three clients above. It is part of the official ARCHIBUS software. We use the term AFM simply because it is the name of the database schema. It broader terms it stands for ‘ARCHIBUS Facilities Manager’, which is synonymous with ARCHIBUS, in describing the full software suite.Report delivery strategyOP03 and OP33 have a target audience of Estates staff, so the reports can be written natively and delivered within Web Central.OP02 has a target audience of all UoE staff, so we are delivering it as a Django application.There will be one Django app which is the report front end, and a separate Django app which will provide an API to the ARCHIBUS database (AFM).This API application may be reused and extended in the future for further reports or other purposes. DEVELOPMENT TOOLS AND STANDARDSDevelopment ToolsARCHIBUS Web CentralPython 3 / Django 1.10Development StandardsTick the appropriate box to indicate the standards being followed for this application:Standard√ indicates complianceDatabase Design√Cold FusionN/AJava√Uportal DevelopmentN/AAccessibility√Web Style Standards√Supported Web Browsers√Python PEP 8√Web Central reports OP03 and OP33Web Central is well placed for the creation and delivery of this kind of operational report.These reports are implemented by creating Archibus AXVW view definition files.The ‘View Definition Wizard’ can be used to create these files, at least initially. The files should then be edited manually to implement more advanced features.The views are placed in the Web Central menu by configuring the database via a SQL script. Permissions for the views are also configured in this manner, if required.For both reports, the historic data should be reported on in additional to the live data, where it exists.OP03: Reactive and Planned Maintenance Response TimesThis report informs on performance, and will be an important tool to help assess resource allocation and performance.It will be used to report the reactive and planned preventative maintenance work requests that met/did not meet their service level agreement.Web Central navigationThe report should be placed in the Web Central menu in the following location:Buildings Operations ManagementOn Demand WorkOperational ReportsLabor AnalysisReactive and Planned Maintenance Response TimesTrade and Craftsperson WorkloadSee this file for how this is done.Web Central view filesThe view definition file(s) should be placed in the following location in the Web Central source:schemaper-siteuoe-productsreportsue-rpt-maint-response-times.axvwA stub of the report view has been created and placed here.Example existing reportA version of this report exists on EBIS:The CFM files for this existing report are:wk_trade_response.cfmwk_trade_response_action.cfmResults page:Mock-upA mock-up has been produced by the business analysis:Input parametersThe requirement is for the parameters in the original report to be expanded.ParameterOptionsReq?Validation/NotesDate rangeUser must be able to select:Work Requests completed this/last weekWork Requests completed this/last MonthWork Requests completed by date (allow user to select date range)YMust be one of the predefined date ranges, or a date range comprised of valid dates.Services teamDrop down menuUser must be able to select one, multiple, or all.YFK in work_team table.BuildingDrop down menuNFK in bl table.Problem typeDrop down menuNprobtype.prob_type (first list element)Fault typeDrop down menuNprobtype.prob_type (last list element)Response timeDrop down menu:Include allPPMEmergencyUrgent5 days10 daysBy agreed dateNMust be one of the defined options.Service request typeRadio buttons:ReactivePlannedBothNMust be one of the 3 defined options.Report output layoutThe report should be presented as a 3-tier drill-down report.Tier 1 should group by:Services TeamService Request TypeProblem TypeResponse TimeTier 2 should group by:Services TeamService Request TypeProblem TypeResponse TimeFault TypeTier 3 should not group and should display all Work Requests.Each tier should display aggregate data:CountWithin Response Time% Response Time Success RateOutwith Response TimeResponse Time RAGCompletion TimeWithin Completion Time% Completion Time Success RateOutwith Completion TimeCompletion Time RAGColumnTierAggregateData source123Services TeamYYYNwork_team.descriptionService Request TypeYYYNwo.wo_typeProblem TypeYYYNprobtype.prob_type (first list element)Response TimeYYYNThis is determined by the SLA attached to the problem pletion TimeYYYNThis is determined by the SLA attached to the problem type.Fault TypeNYYNprobtype.prob_type (last list element)Work Request NoNNYNwr.wr_idCountYYNcountcount(*)Within Response TimeYYYcountSee section below: Calculating Response and Completion times% Response Time Success RateYYY%ageOutwith Response TimeYYYcountResponse Time RAGYYYcalculatedSee section below: RAG thresholdsWithin Completion TimeYYYcountSee section below: Calculating Response and Completion times% Completion Time Success RateYYY%ageOutwith Completion TimeYYYcountCompletion Time RAGYYY?See section below: RAG thresholdsCalculating Response and Completion timesThese columns display results of calculations comparing the actual response and completion times against the target response and completion times.The target response and completion times are determined by the SLA which is attached to the problem type of the Work Request.The actual response and completion times are recorded as data in the work request, and are generated by changes to the status of the work request:Status fromStatus toResponse timeRequested (R)Issued and in process (I)Completion timeIssues and in process (I)Completed (Com)RAG thresholdsRedAmberGreenPPMResponse< 90%90% - 94%>= 95%Completion< 90%90% - 94%>= 95%EmergencyResponse< 85%85% - 94%>= 95%Completion< 85%85% - 94%>= 95%UrgentResponse< 85%85% - 94%>= 95%Completion< 85%85% - 94%>= 95%5 daysResponse< 75%75% - 84%>= 85%Completion< 75%75% - 84%>= 85%10 daysResponse< 75%75% - 84%>= 85%Completion< 75%75% - 84%>= 85%By agreed daysResponse< 85%85% - 94%>= 95%Completion< 85%85% - 94%>= 95%Reference: Original Business AnalysisThe original BA which this design is based on is as follows:Unique Report ReferenceOP03Tranche No.Tranche 1Report detail validated byColin PritchardPriority (H/M/L)HighReport format Complexity (H/M/L)HighReport CategorisationDExisting report nameTrade Response Times for Reactive Maintenance(Authorised Information - Maintenance - Trade Response Time 57)New report nameReactive and Planned Maintenance Response Times15/08 CP - This informs on performance, and will be an important tool to help assess resource allocation and performance. Not key to operating business, but important to understand how the system is impacting on work progress/performance. Rated: Critical report.Example existing report available?Yes, see OP03: Example existing report.Mock Up produced?Yes, see OP03: Mock-up.Process(es) supported/short descriptionUsed to report the reactive and planned preventative maintenance work requests that met/did not meet their service level agreementOwner/Main contactColin PritchardOwning areaHelpdeskCurrently accessed/used byEstates Staff (Maintenance and Trades Managers and Building Operations Manager) - access provided on requestWho will access/use the report?Estate Staff (Helpdesk, Managers, Team Leaders)SecurityEstates staff only to access these reportsEnd users?None - self serviceFrequency used?WeeklyRun 27 times between 1/1/16 and 31/5/16 How up to date does data need to be?Live dataSelf service or scheduled?Self ServiceSearch/selection criteria1. Work Requests completed this/last week*2. Work Requests completed this/last Month*3. Work Requests completed by date - select the date range from calendar date picker* 4. Services Team - drop down (able to select one, multiple (consecutive or non consecutive) or all teams listed*5. Building - alphabetical drop down of active buildings where keying first letter takes you to that place on the list followed by building code (for example ABDEN HOUSE - 0905)6. Problem Type - drop down7. Fault Type - drop down8. Response Time - drop down9. Service Request type radio- breakdown planned both*As a minimum date range and services team must be entered.Fields displayed on report and database tableHeading 1. (Report Name & Unique Ref) 'Reactive and Planned Maintenance Response Times/OP03'Heading 2. Search criteria entered including date rangeView 11. Services Team2. Service Request Type3. Problem Type4. Count - link to View 25. Response Time6. No. Within Response Time7. % Response Time Success8. No. Outwith Response Time9. Response Time RAG* 10. Completion Time11. Within Completion Time12. % Completion Time Success Rate13. Outwith Completion Time14. Completion Time RAG*View 21. Services Team2. Service Request Type3. Problem Type4. Fault Type 5. Count - link to View 36. Response Time7. No. Within Response Time8. % Response Time Success9. No. Outwith Response Time10. Response Time RAG*11. Completion Time12. Within Completion Time13. % Completion Time Success Rate14. Outwith Completion Time15. Completion Time RAG*View 31. Work Request No 2. Services Team3. Service Request Type4. Problem Type5. Fault Type6. Response Time7. Craftsperson Comments8. No. Within Response Time9. % Response Time Success10. No. Outwith Response Time11. Response Time RAG*12. Completion Time13. Within Completion Time14. % Completion Time Success Rate15. Outwith Completion Time16. Completion Time RAG** RAG - visual representation of the % success rate displayed as a coloured field/cell. Rules for setting RAG status to be specified by Colin Pritchard.Graph to display the weekly/monthly/annual volumes and response success rates for planned, reactive or all Service Request TypesWhere is the data sourced from?ArchibusHow is returned data viewed?Table format displaying RAG statusAdditionally present data in graphical format (without requiring the production of graphs using pivot tables in excel)Summary/totals required?Yes display grand total (along with overall RAG)Able to download to excel?YesAble to print?No Logical grouping/menu optionHelpdeskAny existing performance issues?None Impact if not available?Without this information there would be no way to access data on the work carried out in relation to the agreed response times.OP33: eIT Costs by TeamThis report will give data to allow EIT's to be populated and therefore income collected for outstanding rechargeable expenditure.It has two main modes of operation:show completed work requestsshow closed work requests where invoice has been processedWeb Central navigationThe report should be placed in the Web Central menu in the following location:Buildings Operations ManagementFinanceOperational ReportseIT Costs by TeamThis structure requires the creation of a new ‘Finance’ activity. See this file for how this is done.Web Central view filesThe view definition file should be placed in the following location in the Web Central source:schemaper-siteuoe-productsreportsue-rpt-eit-costs-by-team.axvwA stub of the report view has been created and placed here.Example existing reportThis is a new report so no current version exists.Mock-upA mock-up has been produced by the business analysis:Input parametersThe report should support the following input parameters:ParameterOptionsReq?Validation/NotesStatusRadio buttons. User must select one of:Completed Work Requests (Not closed)Closed Work Requests (Invoice processed)YMust be one of the two options.Date rangeUser must be able to select:This weekLast weekThis monthLast monthBy financial year (show drop-down)Custom (show start/end date pickers)YIf user is searching Completed Work Requests, the dates apply to date of WR completion.If user is searching Closed Work Requests, the dates apply to date invoice was processed.TeamRadio buttons:Contract ServicesMaintenance ServicesYContract ServicesDrop down menuNEnabled only if Team == ‘Contract Services’.The data should come from table: work_teamInclude all rows with prefix ‘CS_’. Maintenance ServicesDrop down menuNEnabled only if Team == ‘Maintenance Services’The data should come from table: work_teamInclude all rows with prefix ‘MS_’.Assumptions:“Team” refers to the work team associated with the WR by the work_team_id FK.“Contract Services” refers to all teams in work_team table with ID starting ‘CS_’.“Maintenance Services” refers to all teams in work_team table with ID starting ‘MS_’.The requirements do not specify if ‘all teams’ is a valid default option for Contract/Maintenance Services dropdowns. We will assume there is no such option and that a team must be specified.Report output layoutThe report should be presented as a 1-tier interactive report.Please note that there is a 1-many relationship between WR and POs/Invoices, so multiple rows per WR may be returned.The report output should include the following columns:ColumnNotesData sourceWork Request Nowr.wr_ideIT Nowr.it_noBuilding Codewr.bl_idBuilding Namewr.bl_id -> bl.nameDirect LabourThis is the internal labour cost. Mass please advise.Material CostFrom goods POPurchase Order No(For material cost)Purchase Order Status(For material cost)Invoice No(For material cost)From goods InvoiceGross Amount(For material cost)Financial Year(For material cost)Period(For material cost)Contractor CostFrom contract POPurchase Order No(For contractor cost)Purchase Order Status(For contractor cost)Invoice No(For contractor cost)From contract invoiceGross Amount(For contractor cost)Financial Year(For contractor cost)Period(For contractor cost)Requested bywr.requestor -> em.first_name + last_namePhone No (Landline)wr.requestor -> em.phoneThe report should include a summary row for ‘grand total’ for financial columns.It should include an option to export to Excel.Reference: Original Business AnalysisThe original BA which this design is based on is as follows:Unique Report ReferenceOP33Tranche No.Tranche 1Report detail validated byLiz TorranceColin PritchardPriority H/M/L)HighReport format Complexity (H/M/L)MediumReport CategorisationC Existing report nameNANew report nameeIT Costs by Team15/08 CP - Rated: Essential. Example existing report available?NAProcess(es) supported/short descriptionReport will give data to allow EIT's to be populated and therefore income collected for outstanding rechargeable expenditureOwner/Main contactLiz TorranceOwning areaEstates FinanceCurrently accessed/used byNAWho will access/use the report?Primarily Estates Finance but also Helpdesk and Estates Admin SecurityEstates staff only to access these reportsEnd users?None - self serviceFrequency used?Can be used daily if required How up to date does data need to be?Live dataSelf service or scheduled?Self serviceSearch/selection criteriaOption required to search on either:Work requests that are updated as Completed within a date range where eIT field is populated1. Work requests completed by date - select the date from calendar date picker2. Team - radio button (depending on selection either 4 or 5 below is enabled)3. Contract Services - dropdown4. Maintenance Services - dropdownor alternativelyClosed work requests where eIT field is populated and invoice has been processed (last updated) within a date range1. Invoices processed by date - select the date from calendar date picker2. Team - radio button (depending on selection either 4 or 5 below is enabled)3. Contract Services - dropdown4. Maintenance Services - dropdownFields displayed on report and database tableHeading 1. (Report Name & Unique Ref) 'eIT costs by Team/OP33'Heading 2. Search criteria entered including date range1. Work Request No2. eIT No3. Building Code4. Building Name5. Direct Labour6. Material Cost7. Purchase Order No8. Purchase Order Status9. Invoice No10. Gross Amount11. Financial Year12. Period13. Contractor Cost14. Purchase Order No15. Purchase Order Status16. Invoice No17. Gross Amount18. Financial Year19. Period20. Requested by21. Phone No (Landline)Where is the data sourced from?ArchibusHow is returned data viewed?Table FormatSummary/totals required?Yes display grand totalAble to download to excel?YesAble to print?NoLogical grouping/menu optionFinanceAny existing performance issues?NAImpact if not available?Unable recover incomePython/Django report OP02: Search Work RequestsTo deliver this report to an audience wider than Estates staff, but still restricted to UoE staff, we need to implement the user-facing report UI with an authorisation/authentication wrapper.This will be delivered as a Django application.An API will be created to feed data to the front-end application. This API will be a separate Django application which has a connection to the Estates AFM database.Application Architecture OverviewAuthentication and AuthorisationFront-end applicationNamesGitlab repoest-op-reportsFunctional user accountestoprepDjango project nameestoprepVirtualenv nameestoprepIntegrationsThe Django project must support the following:EASEEntire site should be EASE protected, except for the healthcheck, as standard.Local environment should simulate this using Apache basic auth.Central AuthWe will integrate with Central Auth to provide AuthZ capacity. Users who authorise as UoE staff will be allowed access. Users who don’t, will be shown a ‘forbidden’ page.Python packages pyldap and django-auth-ldap should be used to provide LDAP support to query Central Auth.The authorise.is.ed.ac.uk directory server should be used for live environment.The authorise-test.is.ed.ac.uk directory server should be used for all other environments.The eduniType attribute should be used to determine staff status. A value of 100 means that the user is a member of staff. Users with any other value should not be allowed access.Please note that there is no existing proof of concept example of Central Auth support in our Django applications. There is ongoing work by Greg Carter to implement this, but at time of writing it is not complete.OAuth2We will use 2 legged OAuth2 for Auth between the front-end and API. This is sometimes called 2LO, or server to server OAuth2.This application is the client in the OAuth2 configuration.SQL scripts must be created to add the new OAuth2 client definition to the OAuth2 database. See ‘Adding a new OAuth2 client’ wiki page for details on this process.The scripts should not be added to version control. But run manually against the GEN* databases, with a different secret for each environment.Python package django-oauth-toolkit should be used to provide this capability.The documentation for this package does not explicitly specify support for Django 1.10. If this is found not to be compatible, Django 1.9 should be used.APIAll Estates data will be requested from the Estates API application. See section API.Python dependenciesThe Django project should include the following packages in its requirements files.Unless indicated otherwise, all packages should be fixed to the most recent stable version at time of build.PackageVersionAreaEnvStandard packagesdjangocorealldjango-environcoreallgunicorncorealltornadocoreallpython-memcachedcoreallpytest-djangotestinglocal/devpytest-seleniumtestinglocal/devfactory_boytestinglocal/devflake8testinglocal/devflake8-junit-reporttestinglocal/devdjango-oauth-toolkitoauthallpyldapldapalldjango-auth-ldapldapallProject structureThe project should follow the standard Django architecture as defined in django-starter.The main app should be called estoprep.User interfaceExample existing reportA version of this report exists on EBIS:Results page:Mock-upA mock-up has also been produced by the business analysis:Input parametersThe new parameters will not exactly match the parameters in the previous report.ParameterOptionsReq?ValidationWork request created:date rangeUser must be able to select radio button:This weekLast weekThis monthLast monthFinancial year:This year…include previous 3 years (Aug-July)Custom:Allow selection of ‘from’ and ‘to’ date picker.Y*Must be one of the predefined date ranges, or a date range comprised of valid dates.Services teamDrop down menuNPresent in ref data.BuildingDrop down menuNPresent in ref data.CollegeDrop down menuNPresent in ref data.SchoolDrop down menuNPresent in ref data.Subject AreaDrop down menuNPresent in ref data.Problem typeDrop down menuNPresent in ref data.RequestorDrop down menuNPresent in ref data.Work request noText inputY*Service request typeRadio buttons:On demandPPM EquipmentPPM HousekeepingAll typesYMust be present, and one of the predefined options.(Hardcoded values)Job status**Radio buttons:CompleteIncompleteAll typesYMust be present, and one of the predefined options.Work request statusDrop down menuPresent in ref data.* One or more of “Date range” or “Work request no” must be entered. ** Reference: Original Business Analysis specifies that these two fields are mutually exclusive. This does not make sense given that one of them is a radio button control type. Please note: Job status and Work request status parameters have been combined into one: work request status. They both originally referred to the same property, but were an attempt to make the form more usable. The current opinion is that one single dropdown is sufficient to drive this.Report result dataThe report should have the following columns:ColumnWork request noDate createdRequested byServices teamJob statusProblem typeFault typeDescriptionBuildingEstates Department craftsperson commentsLook and feelThe application should use EdGEL to provide a look and feel consistent with other UoE website, in particular ed.ac.uk.Reference dataThe form makes use of Estates data to populate drop down menus, and to validate user input. This reference data must be requested from the API. CachingIt should be cached by the application to prevent repeated requests to the API.The integration with memcached should be used to provide this caching mechanism. See this section of the Django docs for details on how to implement this.The default caching timeout of 5 minutes should be appropriate for this use.Requesting the dataDjango should access the API application to get the data. The API will provide the data as JSON. See the API section of this document for the endpoint reference for each data type.Reference: Original Business AnalysisThe original BA which this design is based on is as follows:Unique Report ReferenceOP02Tranche No.Tranche 1Report detail validated byColin PritchardPriority (H/M/L)HighReport format Complexity (H/M/L)LowReport CategorisationEExisting report nameMultiple Criteria(Reports - Fault Reports* - Multiple Criteria 7)Amalgamates:Reports - Fault Reports* - Building 5Reports - Fault Reports* - Data Range 6Reports - Fault Reports* - Trade 8Reports - Fault Reports* - WO Requestor 9Reports - Fault Reports* - Work Order 10* General AccessNew report nameSearch Work Requests15/08 CP - Essential tool for managing business. Rated: Critical ReportExample existing report available?Yes. See OP02: Example existing report.Mock Up produced?Yes. See OP02: Mock-up.Process(es) supported/short descriptionUsed to find out what work orders are allocated to teams and show the work teams workloadOwner/Main contactColin PritchardOwning areaHelpdeskCurrently accessed/used byGeneral access - all UoE staff able to access without signing into EBIS onlineWho will access/use the report?General access - all internal UoE staffSecurityUoE Internal staff only to access these reports, not available to external usersEnd users?None - self service Frequency used?DailyRun 13,168 times between 1/1/16 and 31/5/16 (includes all reports being amalgamated)How up to date does data need to be?Live dataSelf service or scheduled?Self serviceSearch/selection criteria1. Services Team - drop down from new table2. Building - alphabetical drop down of active buildings where keying first letter takes you to that place on the list followed by building code (for example ABDEN HOUSE - 0905)3. College - drop down 4. School - alphabetical drop down listing name of school followed by school code (for example BUSINESS SCHOOL S21)5. Subject Area - alphabetical drop down listing name of school followed by school code (for example SPORTS UNION D836)6. Problem Type - drop down (problem type code) and Fault Type - drop down (problem type level 2)7. Requestor - drop down8. Work Requests created this/last week*9. Work Requests created this/last Month*10. Work Requests created by Financial Year (1/8-31/7)*11. Work Requests created by date - select the date from calendar date picker* 12. Work Request No - free format*13. Service Request Type - radio buttons (On Demand, PPM - Equipment, PPM - Housekeeping, All types)14. Job Status - radio buttons (Complete, Incomplete, Any status)15. Report a specific status list - drop down containing the new work request status list)User will select from either 14. Job Status or 15. Report a specific status list and will not be able to select from both of these sections*As a minimum date range or Work Request No must be entered.Fields displayed on report and database tableHeading 1. (Report Name & Unique Ref) 'Search Work Requests/OP02'Heading 2. Search criteria entered including date range1. Work Request No2. Date Work Request Created3. Requested By4. Services Team5. Status6. Problem Type7. Fault Type8. Description9. Building10. Estates Department Craftsperson CommentsWhere is the data sourced from?ArchibusHow is returned data viewed?Table formatSummary/totals required?Yes display grand totalAble to download to excel?YesAble to print?No Logical grouping/menu optionHelpdeskAny existing performance issues?Currently the report only returns 50 records at a time which is as a result of performance issues. Ideally the system will return all records found without the requirement to select 'View more results'Impact if not available?UoE staff would be unable to access information on the work order position without having to contact an individual for the information.APIThis application will provide an interface to provide AFM data for use in the front end application.The interface will be implemented with RESTful principles, although at this point it will only support GET operations (i.e. be read-only).NamesGitlab repoest-apiFunctional user accountestapiDjango project nameestapiVirtualenv nameestapiIntegrationsNote there will be no EASE support configured for this app. Auth will be provided with OAuth2.The Django project must support the following:OAuth2This application is the resource server in the OAuth2 configuration.ARCHIBUS Oracle database (AFM)There will be a direct connection to the AFM database, using the Django oracle driver.A user and role must be created: estapi/estapi_role.This user should be granted SELECT permissions on required database objects. These grants should be implemented with a SQL script to be run manually.Python dependenciesThe Django project should include the following packages in its requirements files.Unless indicated otherwise, all packages should be fixed to the most recent stable version at time of build.PackageVersionAreaEnvStandard packagesdjangocorealldjango-environcoreallgunicorncorealltornadocoreallpython-memcachedcoreallpytest-djangotestinglocal/devpytest-seleniumtestinglocal/devfactory_boytestinglocal/devflake8testinglocal/devflake8-junit-reporttestinglocal/devdjangorestframeworkRESTalldjango-oauth-toolkitOAuth2allEndpointsAll endpoints should return data in JSON format.OverviewThe following table describes the endpoints which will be used for reference data.Resource URIMethodParams supported/workTeamsGETNone/buildingsGETNone/collegesGETNone/schoolsGETNone/subjectAreasGETNone/problemTypesGETNone/faultTypesGETNone/workRequestRequestorsGETNone/workRequestStatusesGETNone/workRequestsGETwr_id*service_request_typejob_statuswork_request_statuswork_teambuildingcollegeschoolsubject_areaproblem_typefault_typerequestorcreated_after*created_before** Mandatory paramters: wr_id or created_after and created_before must be provided. All other parameters are optional./workTeamsReturn columnsWORK_TEAM_IDDESCRIPTIONFrom tableWORK_TEAMOrdered byDESCRIPTIONFilterNone/buildingsReturn columnsBL_IDNAMEFrom tableBLOrdered byNAMEFilterNone/collegesReturn columnsBU_IDNAMEFrom tableBUOrdered byNAMEFilterNone/schoolsReturn columnsDV_IDNAMEBU_IDFrom tableDVOrdered byNAMEFilterNone/subjectAreasReturn columnsDP_IDNAMEDV_IDFrom tableDPOrdered byNAMEFilterNone/problemTypesReturn columnsPROB_TYPEDESCRIPTIONFrom tablePROBTYPEOrdered byDESCRIPTIONFilterInclude only level 1 problem types.I.e. Only those which have one item in hierarchy_ids./faultTypesReturn columnsPROB_TYPE (last list element) (as FAULT_TYPE)DESCRIPTIONPROB_TYPE (first list element)From tablePROBTYPEOrdered byDESCRIPTIONFilterInclude only level 2 problem types.I.e. Only those which have two items in hierarchy_ids./workRequestRequestorsReturn columnsEM_IDNAMEFrom tableEMOrdered byNAMEFilterInclude only employees who have requested a work request./workRequestStatusesReturn columnsENUM_LIST (Parsed as STATUS andDESCRIPTION)From tableAFM_FLDSOrdered bySTATUSFilterTABLE_NAME=wrFIELD_NAME=status/workRequestsReturn columnsColumnData SourceWork request noWR.WR_IDDate createdWR.DATE_REQUESTEDRequested byWR.REQUESTOR -> EM.NAMEServices teamWR.WORK_TEAM_ID -> WORK_TEAM.DESCRIPTIONJob statusWR.STATUSProblem typeWR.PROB_TYPE -> PROBTYPE.DESCRIPTION(join on first list element)Fault typeWR.PROB_TYPE -> PROBTYPE.DESCRIPTION(join on last list element)DescriptionWR.DESCRIPTIONBuildingWR.BL_ID -> BL.NAMEEstates Department craftsperson commentsWR.CF_NOTESFrom tablesWREMWORK_TEAMBLPROBTYPEOrdered byWR_IDFilterFilter results according to parameters. See ERD for relationship of parameters to data.ParametersParameter nameValidationMandatory?wr_idMust be integerYes, unless date range is provided.service_request_typeMust match a WO_TYPE within data from /workOrderTypesNojob_statusMust be one of:C (complete)I (Incomplete)Nowork_request_statusMust match a STATUS within data from /workRequestStatusesNowork_teamMust match a WORK_TEAM_ID within data from /workTeamsNobuildingMust match a BL_ID within data from /buildingsNocollegeMust match a BU_ID within data from /collegesNoschoolMust match a DV_ID within data from /schoolsNosubject_areaMust match a DP_ID within data from /subjectAreasNoproblem_typeMust match a PROB_TYPE within data from /problemTypesNofault_typeMust match a FAULT_TYPE within data from /faultTypesNorequestorMust match a EM_ID within data from /workRequestRequestorsNocreated_afterParameter created_before must also be provided, which must be a time after this.Must be a valid date string in the format:2012-04-23T18:25:43.511ZYes, unless wr_id is provided.created_beforeParameter created_after must also be provided, which must be a time before this.Must be a valid date string in the format:2012-04-23T18:25:43.511ZYes, unless wr_id is provided.The following ERD describes the data structure which should be used to build the result set which is returned by this endpoint:Work requests in both live and archive tables must be returned. To accomplish this, the views should be used, rather than the tables.USER INTERFACETransactional InterfaceOP02 (Django)See section Example existing report for the existing report plus a basic mock-up of the new intended screen.The new report should use EdGEL, and conform to EdGEL conventions.At this point there is no requirement for a menu because only one report is being delivered.A breadcrumb trail must be implemented to allow the user to navigate back to where they came from.OP03, OP33 (Web Central)The interfaces for these reports will be implemented in line with standard Web Central UI conventions.APPLICATION SECURITYAuthenticationEASE authenticated must be provided for the Django est-op-reports application.OAuth2 must be provided between est-api and est-op-reports.AuthorisationCentral Auth must be used to authorise users of the est-op-reports application. They must be members of staff.See sections Central Auth, and Authentication and Authorisation.Web Central reports OP03 and OP33 should be available to all Web Central users.Business ObjectsNot required.DATABASE DESIGNNo change to existing schemas.APPLICATION INTERFACESSee section Application Architecture Overview.DATAData MigrationNot required.Archiving PolicyNot required.IMPLEMENTATIONCare must be taken to work in conjunction with any changes being made by the EST080 project, which will be being deployed at around the same time.Build, CI, & DeploymentWeb CentralThere are no existing bamboo plans for Web Central, and this project will not create them.Implementation will consist of manually running scripts against the AFM database, and copying files into the Web Central web app directory.The implementation plan will detail these steps.DjangoThe Django apps estapi and estoprep will be build and deployed with Bamboo.Each application should have its own separate build and deployment plans.These plans should be based on the Django Starter example plans.The plans should be named as follows:Estates APIEstates Operational ReportsVersion ControlWeb CentralChanges should be stored in the existing Subversion repository at: Django apps should be stored in Gitlab, with the following repo names:est-op-reportsest-apiThe local build environment configuration should also be stored in Gitlab:est-op-reports-vagrantA new user plus grant will need to be created on AFM for this application. The sql script(s) for this should be stored in Subversion at: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download