bcgov / exitsurveyadmin

The Exit Survey Administration Tool will assist BC Stats in administering the BCPS Exit Survey.

[]

ExitSurveyAdmin

The Exit Survey Administration Tool will assist BC Stats in administering the BCPS Exit Survey.

Development tasks

Running a development environment

Prerequisites

1. Ensure the .NET Core SDK 3.1 is installed.
2. Ensure you have the .NET EF Core CLI installed: dotnet tool install dotnet-ef --version 6.0.0
3. Ensure the .NET Core HTTPS development certificate is trusted.
4. Install Postgres and create a database named esa.
5. Check out the code from this repository.

NB. To be fully functional, the application should be run in conjunction with the CallWeb API. The code for the CallWeb API is not publicly available. Please reach out to the project team for access. However, the project will still build and run without the CallWeb API.

Install EF Core dependencies

dotnet tool install dotnet-ef --version 6.0.0 dotnet add package Microsoft.EntityFrameworkCore --version 3.1.4 dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.4

Config + secret settings

The application uses two appsettings.json files, one in the /config directory and one in /secret. This mirrors how the application gets deployed to OpenShift. /config/appsettings.json contains non-sensitive configuration for the application, while /secret/appsettings.json contains configuration that should not readily visible.

See also the comments in Program.cs about how the files get resolved when the application is deployed to OpenShift.

To get set up:

5. Copy the contents of /config/appsettings.config-template.json into a new file, /config/appsettings.json, and update the values as appropriate.

6. Do the same with /secret/appsettings.secret-template.json, copying it into /secret/appsettings.json.

7. We also need to set up the frontend environment. In the /ClientApp directory, copy env.example into .env, and update the values as appropriate.

Run migrations

8. From the root project directory, run dotnet ef database update. This will run the migrations and set up your development database. ENSURE THAT dotnet add package Microsoft.EntityFrameworkCore --version 3.1.4 dotnet add package Microsoft.EntityFrameworkCore.design --version 3.1.4

   Note that the database will be seeded automatically when the application is started.

Start the API

9. Open the root code directory in Visual Studio Code. You may be prompted to add required assets and/or resolve dependencies; do so.
10. While in Visual Studio Code, press CTRL + F5 to launch the API.
11. Test that the API is running correctly by checking the HealthStatus. If the project is running at the default location and port: curl http://localhost:5050/api/HealthStatus/Status.

Install frontend dependencies

12. From the /ClientApp directory run yarn install.

Start the frontend

13. Still in the /ClientApp directory, run yarn start to launch the front-end. You should see the application open in a new browser.

Required sample input for development

The following sample documents should be placed in the /SampleInput folder, and can be obtained from the project team.

• PSA-CSV-Sample.csv
• CallWeb-Sample.csv

Quick database reset

This command will quickly drop the database, delete migrations, create an initial migration, and update the database.

dotnet ef database drop --force;rm -rf Migrations/*.cs;dotnet ef migrations add InitialCreate;dotnet ef database update

Copyright and license

Copyright 2019 Province of British Columbia

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at 

   http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

exitsurveyadmin's Issues

Clean-up of existing data

We will need to clean up the existing data before importing it into the new system.

Implement draft AdminUser

Sub-task of #24.

Hide all interface pages behind Keycloak login

The user must log in to see every page on the site, except a splash screen.

Add API access controls

Ensure that API methods can only be called by appropriate clients.

This will be some combination of whitelisting of server access and authentication/authorization.

Implement draft EmployeeActionTypeEnum

Sub-task of #24.

Connect to (test) email server

The app will need to connect to the SMTP server and test sending email.

Implement EmployeeTimelineEntry CRUD API

Deploy application to test

Add versioning to API

Right now the API is at server/api/method. Add /api/1.x/method.

Implement draft EmployeeTimelineEntry

Sub-task of #24.

Test shorter access token lifespan

Currently the realm setting is for 2 hours, but that is because of the shared application on the realm. See if a shorter setting is viable.

Implement draft receive CSV API function

Sub-task of #19. Will substantially re-implement the existing VB Script.

Deployment of initial app to dev

The code in the repo right now should be ready for deployment to the dev server.

Implement initial draft scheduled services API

Per document described in #13. Will create sub-tickets for each of these.

Scheduled services

• Retrieve CSV: docs, issue #20
• Reconcile CSV: docs, issue #21
• Send all email: docs, issue #22
• Retrieve from CallWeb: docs, issue #23

Questions around "add admin" employee search

For the add admin functionality:

• What system are we searching employee data for? Is there an API?
• What criteria do we want to search on? Employee number, employee name...?

Test CSV import against live data

Implement Employee CRUD API

Implement draft TaskTypeEnum

Sub-task of #24.

Email connection details

@robinwn We'll need the connection details for the email server.

VPN access

I will likely need VPN access to check deployed versions of the app, connect to TFS, etc.

Build employee detail screen

Complete with timeline history.

Questions around sign-in

For the log in functionality:

• What credentials are required?
• Is there already a standard login screen?
• Is there SSO for government apps?
• What are best practices here for BC government apps?

@robinwn you said you should be able to share code from an existing system, which might be helpful in answering some of these.

Add BC government bootstrap theme

Incorporate from https://github.com/bcgov/bootstrap-theme.

LDAP lookup for employee emails

@robinwn As we discussed, I will need some information (server, port, user ID, password) to make an LDAP lookup request to obtain an employee's email address. Feel free to send by whatever secure method makes sense!

Implement draft TaskLogEntry

Sub-task of #24.

Add missing topics

TL;DR

Topics greatly improve the discoverability of repos; please add the short code from the table below to the topics of your repo so that ministries can use GitHub's search to find out what repos belong to them and other visitors can find useful content (and reuse it!).

Why Topic

In short order we'll add our 800th repo. This large number clearly demonstrates the success of using GitHub and our Open Source initiative. This huge success means its critical that we work to make our content as discoverable as possible; Through discoverability, we promote code reuse across a large decentralized organization like the Government of British Columbia as well as allow ministries to find the repos they own.

What to do

Below is a table of abbreviation a.k.a short codes for each ministry; they're the ones used in all @gov.bc.ca email addresses. Please add the short codes of the ministry or organization that "owns" this repo as a topic.

That's in, you're done!!!

How to use

Once topics are added, you can use them in GitHub's search. For example, enter something like org:bcgov topic:citz to find all the repos that belong to Citizens' Services. You can refine this search by adding key words specific to a subject you're interested in. To learn more about searching through repos check out GitHub's doc on searching.

Pro Tip 🤓

• If your org is not in the list below, or the table contains errors, please create an issue here.

• While you're doing this, add additional topics that would help someone searching for "something". These can be the language used javascript or R; something like opendata or data for data only repos; or any other key words that are useful.

• Add a meaningful description to your repo. This is hugely valuable to people looking through our repositories.

• If your application is live, add the production URL.

Ministry Short Codes

Short Code	Organization Name
AEST	Advanced Education, Skills & Training
AGRI	Agriculture
ALC	Agriculture Land Commission
AG	Attorney General
MCF	Children & Family Development
CITZ	Citizens' Services
DBC	Destination BC
EMBC	Emergency Management BC
EAO	Environmental Assessment Office
EDUC	Education
EMPR	Energy, Mines & Petroleum Resources
ENV	Environment & Climate Change Strategy
FIN	Finance
FLNR	Forests, Lands, Natural Resource Operations & Rural Development
HLTH	Health
FLNR	Indigenous Relations & Reconciliation
JEDC	Jobs, Economic Development & Competitiveness
LBR	Labour Policy & Legislation
LDB	BC Liquor Distribution Branch
MMHA	Mental Health & Addictions
MAH	Municipal Affairs & Housing
BCPC	Pension Corporation
PSA	Public Safety & Solicitor General & Emergency B.C.
SDPR	Social Development & Poverty Reduction
TCA	Tourism, Arts & Culture
TRAN	Transportation & Infrastructure

NOTE See an error or omission? Please create an issue here to get it remedied.

Rebuild the Exit Survey application for BC Stats to support automation processes and improve administrative functionality

This issue has been auto-generated to facilitate questions about a paid opportunity that has just been posted on the BCDevExchange.

To learn more or apply, visit the opportunity page. The opportunity closes on Tuesday, November 5th, 2019 at 16:00 PST.

Design new CSV reconciliation process

Based on #4 and #6, we will need to design the new process. This will be a detailed description (including state diagram?) of the reconciliation process and how it works.

Implement draft CRUD APIs

Implement APIs, as appropriate, to create / read / update / delete the basic models:

• Employee: #36
• EmployeeTimelineEntry: #37
• TaskLogEntry: #34

Extract variables from existing email templates

@robinwn if you could send those to me that would be great. Thank you.

Lightweight API for connecting to CallWeb?

@robinwn mentioned that rather than connecting directly to the MySQL CallWeb database, he might be able to spin up an HTTP service that abstracts the connection.

Don't use `Type` in column names

e.g. TaskType on TaskLogEntry.

Extract CSV handling rules from existing scheduled task

Will use the existing code as a basis for identifying the rules for handling the CSV. @robinwn if you could send me the code that would be great. Thanks!

Implement draft EmployeeStatusEnum

Sub-task of #24.

Add filtering interface to employee listing

The employee list needs the ability to add filters, such as:

• Name
• Email
• Effective date
• Status

Implement draft reconcile CSV API function

Sub-task of #19.

Draft API design

Draft the new API. In progress here.

Implement draft send email API function

Sub-task of #19.

Draft data model

Currently working on this here: https://github.com/bcgov/ExitSurveyAdmin/wiki/Data-model

Test live CallWeb connection

Deploy application to production

Implement TaskLogEntry CRUD API

Implement draft Employee

Sub-task of #24.

Load CallWeb sample data into app

Implement draft retrieve from CallWeb API function

Sub-task of #19.

Build sample source data CSV

A sample CSV needs to be available for building the CSV extraction script (#20). Can use the existing extract but must edit to anonymize.

Implement draft TaskOutcomeEnum

Sub-task of #24.

Implement draft data model

Per #5, implement the draft data models in code.

• Add AdminUser: docs
• Add Employee: docs, issue #26
• Add EmployeeStatusEnum: docs, issue #27
• Add EmployeeTimelineEntry: docs, issue #28
• Add EmployeeActionTypeEnum: docs, issue #29
• Add TaskLogEntry: docs, issue #30
• Add TaskTypeEnum: docs, issue #31
• Add TaskOutcomeEnum: docs, issue #32

Identify rules governing CSV changes from PSA side

Related to #4. How does the CSV change day-to-day? What does it mean if an employee exits, enters, etc.? To discuss with Alan.

Build basic employee listing interface

Per documentation on the wiki, implement a basic employee listing interface.