ChalkUp Specification

This is a small specification which describes the ChalkUp application.

ChalkUp is the software which is used for management of checklists (surveys or questionnaires).

ChalkUp have two UI parts:

• Web UI for administrator
• Android app for “field workers”

For saving data ChalkUp core uses a relational database. The database consists of following tables:

• questionnaire
• qnrresult

CREATE TABLE questionnaire
(
   id INT PRIMARY KEY NOT NULL,
   org_id INT NOT NULL,
   title longtext,
   json longtext,
   template longtext,
   createdAt DECIMAL(13,0) NOT NULL,
   createdBy INT NOT NULL,
   updatedAt DECIMAL(13,0) NOT NULL,
   updatedBy INT NOT NULL,
   deletedAt DECIMAL(13,0) NOT NULL,
   deletedBy INT NOT NULL,
   TEMPORARY INT DEFAULT 0
);

• id
  • the questionnaire id
• org_id
  • the “organization” id
    • ensures that the questionnaires are visible only for this “organization” (usually it is a company)
• title
  • questionnaire title
• json
  • contains the full JSON description of the questionnaire
  • at the moment it is written in Form.io-compatible “dialect”
• template
  • the URL of the “template printer”
    • at the moment it is my “JRServer”
    • Examples:
      • https://avril2021.avril.work/jrserver/geolead_install
      • https://avril2021.avril.work/jrserver/informsvyaz
• createdAt
  • “created” timestamp (millis)
• createdBy
  • id of the user, who created this questionnaire
• updatedAt
  • “last updated” timestamp (millis)
• updatedBy
  • id of the user, who updated this questionnaire last time
• deletedAt
  • “deleted” timestamp (millis)
• deletedBy
  • id of the user, who deleted this questionnaire
• temporary
  • the flag which is used to save questionnaires temporarily e.g. at first time when the questionnaire is created, but not saved by the user

The qnrresult table contains information about questionnaires which are already filled out.

CREATE TABLE qnrresult
(
   id INT PRIMARY KEY NOT NULL,
   org_id INT NOT NULL,
   qnr_id INT NOT NULL,
   title longtext,
   qnrJson longtext,
   resJson longtext,
   createdAt DECIMAL(13,0) NOT NULL,
   createdBy INT NOT NULL,
   updatedAt DECIMAL(13,0) NOT NULL,
   updatedBy INT NOT NULL,
   deletedAt DECIMAL(13,0) NOT NULL,
   deletedBy INT NOT NULL
);

• id
  • the result id
• org_id
  • the “organization” id
    • ensures that the questionnaire results are visible only for this “organization” (usually it is a company)
• qnr_id
  • the id of the questionnaire on which is this result based
• title
  • title of this questionnaire result
    • usually the same as questionnaire title, but it can be changed by the user during the creation of the questionnaire result
• qnrJson
  • contains the full JSON description of the questionnaire
  • at the moment it is written in Form.io-compatible “dialect”
  • normally the JSON of the questionnaire can be fetched from the questionnaire table using the qnr_id, but sometimes it is not possible because the questionnaire was deleted or it could be changed in the meanwhile. So the better strategy would be to save the questionnaire's JSON in the moment the result of the questionnaire is completed.
• resJson
  • contains the JSON of the questionnaire result (⇒ Json of the filled out questionnaire)
• createdAt
  • “created” timestamp (millis)
• createdBy
  • id of the user, who created this questionnaire result
• updatedAt
  • “last updated” timestamp (millis)
• updatedBy
  • id of the user, who updated this questionnaire result last time
• deletedAt
  • “deleted” timestamp (millis)
• deletedBy
  • id of the user, who deleted this questionnaire result

The REST API is used by both: the web UI and mobile application.

TODO: describe the REST API here

ChalkUp web UI should be realized on the base of Angular framework.

Screens:

• questionnaires list
• questionnaire details
• results list
• result details

The list screen is the first screen of the web application.

The list screen is used to:

• display the list of all available questionnaires for this “organization”
• search for questionnaires (based on title)
• route the user to the corresponding details screen after the click on one list entry
• add questionnaire
• copy questionnaire
• delete one or multiple questionnaires

This screens is used to edit questionnaires details.

The editable fields are:

• Title
• JSON
  • On the right side of the JSON field, there is a button, which redirects the user to the Form.io-based form editor. The form editor will be opened in a pop-up window.
• Template URL

Also the details screen contains buttons to delete and copy the selected questionnaire.

A list screen for questionnaire results.

The list contains following columns:

• title
• updatedAt
• updatedBy → user name

It should be possible to filter entries by:

• title
• user
• updatedAt timestamp

The following operations should be possible:

• deletion

In the later versions maybe it would make sense to have also the printing functionality on the list screen.

Details screen for one result (filled out questionnaire).

The details screens should have the following fields:

• id
• org_id INT NOT NULL,
• qnr_id INT NOT NULL,
• title longtext,
• qnrJson longtext,
• resJson longtext,
• createdAt DECIMAL(13,0) NOT NULL,
• createdBy INT NOT NULL,
• updatedAt DECIMAL(13,0) NOT NULL,
• updatedBy INT NOT NULL,
• deletedAt DECIMAL(13,0) NOT NULL,
• deletedBy INT NOT NULL

On this screen it should be possible to show and print the results of the questionnaire.

Because we use Form.io for formulars, the results can be represented as JSON and as rendered form. Also, to print results, we should use the JRServer. The printing template URL can be fetched from the questionnaire table (TODO: think about the solution for the problem if the questionnaire is already deleted).

The mobile app is used by “field workers” which can fill out the surveys.

The app consists of following screens:

• results list
• create new result (select form)
• edit result details
• result

Necessary things which should be developed:

• account creation
  • with the help of standard Android mechanism
• synchronization
  • with the help of standard Android mechanism

This screen contains the list of already filled questionnaires. The list contains :

• the title of the result
• timestamp of the result

The screen contains also an “Add” button. If the user click on this button, the “Create new result” screen should be shown.

LATER: the screen should have the “Search” functionality with at least two fields: title and date.

On this screen the user should have the possibility to select the questionnaire to fill out and to type in the title of the result. The value of the title should be initialized with the name of the questionnaire.

This screen is normally not one screen, but the series of the screens, where one screen displays one question of the survey.

Result details contains the title and date of the result.

LATER: it should be possible to view the result in form of HTML (using Form.io) and PDF.