Page Comparison

User Story description:

Anchor
top top

As a Submitter (i.e., Health Authority user), I would like to be able to submit forms in batch via a file upload functionality in order to save a significant amount of time when I have to submit data for multiple facilities/operators.

This Story focuses on the file submission only. The following points describe the whole workflow for the file submission:

• Once authenticated, a submitter should be able to select a form to which they have access, and then decide to submit this form either manually or, if the option is available, via file upload.

• Once the file upload selected, the user can download the template for the user is prompted to “browse“ their system submission

• Once the user has filled out the file, they can now submit it using the “browse“ button to select the file or they can also simply drag and drop the file into the dedicated area on the screen.

• Once the file pre-loaded, the user can confirm their submission by clicking the “submit“ button.

• From there, the file will be parsed and validated using the validation rules defined by the form designer on each field. Every row within the file will be the equivalent of one form submission.

• During the import and validation, the user is prompted with a loading screen

• Once the validation completed, a message will appear on the user screen indicating the Successful file processing or the Failed file processing, in which case a validation report will be available for download. If the import was successful, then the user will see the current status of their submission to be “Review pending“

• The validation report will point out the different issues found within the files (e.g. First name missing, must be provided. Or, Incorrect format for birthdate, format must be yyyy-mm-dd)

Only the section in bold is part of this User Story.

Business Rationale:

top page

Currently, Health Authorities gather data from multiple facilities/operators to then submit this data to the Ministry of Health.

It is important for the Health Authorities to gather this data first as it feeds their own system. The current issue is that the HAs have no easy way to submit the data to the Ministry of Health. They can only do so through time consuming and painful manual form entries. As a solution they would like to have the ability to make batch submissions through a file upload functionality.

Dependencies:

top page

[List potential dependencies with other User Stories or Tasks]

Technical Details:

top page

Questions:

• Is it Its is an all or nothing import? . If one issue is found on one record, we do we still not insert the other valid records? All or nothing yes.

• Do we accept only .csv files or also .xlsx files? XLSX would allow for cleaner uploads with advanced layout within Excel to better control input

• Do we limit the size of the file.

• Front-end initial checks:

  • We accept only .csv files (comma separated values, including the header row)

  • We limit the size of the file to 10MB

  • We control the number of columns submitted. This should match the number of columns in the template

  • The column name should match to a field name.

• Parsing:

  • We do not care about the order of the columns

  • We read the rows of the file one by one and try to create a form submission from each of them

  • As we parse the data we generate the Validation report containing the different errors identified when trying to enter a value for a given field

  • There shouldn’t be any actual submission if 1 error is detected. We only actually create the submissions if there is no error.

• We will need to agree on the Validation Report format (it will be a .csv):

  • What will be the columns?

  • Example from other application:

Image Added

We could add a column to identify an “error #” just in case the user needs additional support and it would make it easier .

We could add a column to identify the “value“ of the field that threw the error

If we are able to identify a “unique identifier“ in the form, then we could also have that ID in the Validation Report to better identify the row that threw an error.

• We will need to think about how to manage interruptions:

  • If the processing is interrupted, how do we let the user know? What type of error is generated?

• Audit need?

  • We will need to confirm whether we should keep track of all the attempts at uploading files for Audit purposes

Solution :

Let me resume what we are going to do in simple way :

1. Read the form data and convert it to an csv empty .

2. When the user upload the data check if the column name is match to form field.

3. If every thing is OK we convert each row to form submission and convert them to a List of submission and transfer it to the server.

4. if there is an error in a field format or field missing any thing else we abort the file submission and allow the submitter to download a file with a complete report .

First to generate the empty template csv file for a specific form for the submitter , we can use this package “export-from-json” from this command :

npm i export-from-json

In the project there is function that return all fields of a form but it sends only an array with the name of the fields , So now we are going to create a new function but it will return an array of this object .

[{
 name : "fullname",
 type : "string"; \\ STring | Date | number,
 required: true | false,
 date_format: "MM-DD-YYYY"
},
 {
 name : "Gender",
 type : "enum | options";
 required: true,
 date_format: "",
 value : ["M", "F"]
 }
]

We have the name of the field , its type, if it is required and if it is a date which format it should be create. After calling this function now we can create the csv file and let the user to download it .

Next when the user upload the file we must read the data from CSV file to analyse them, check if there are no errors, to do that we can use this package “Papaparse” from this command

npm install papaparse --save-dev

This package is going to separate the header to the rows , so now you can check if header is compatible to the form by testing if the field is match and the number of column is equal .

How to generate the template csv file ?

Get the form, the extract all fields of the forms, create a JSON object with them and use the package vue-json-csv to generate the csv file .

How to read the csv file ?

Create A Drag-and-Drop File uploader, catch the file use papaparse to convert the file to an object.

check if the file is a CVS , read the data with papaparse. So we will have 2 sections of data one for the header and one for the data .

https://www.papaparse.com/docs

 // this.file is the local csv file
 Papa.parse( this.file, {
        header: true,
        skipEmptyLines: true,
        complete: function( results ){
            this.content = results;
            this.parsed = true;
        }.bind(this)
    } );

Check if the header is match to the fields of the form here is a sample of code to make the check but you can use your own logic:

var array1= ["Name", "Gender"];
var array2= ["Fullname", "Gender"];
if(array1.sort().join(',') === array2.sort().join(',')){
    alert('same members');
}
else
alert('not a match');

Remember the fields would be an object we supose to convert it to simple array like above. If everything is OK the header now we are going to check if the rows are ok;

 var fields = [{
 name : "Fullname",
 type : "string"; \\ STring | Date | number,
 required: true | false,
 date_format: "MM-DD-YYYY"
},{
 name : "Gender",
 type : "enum";
 required: true,
 date_format: "",
 value : ["M", "F"]
 }
 ,{
 name : "Date of birth",
 type : "Date";
 required: true,
 date_format: "MM-DD-YYYY",
 value : []
 }
];

var report = [];
var submissions = [][];
function isKey(obj, value) {
  return  obj.name === value ;
}

function addReport(obj,col, text) {
    let find = false;
    for (let k = 0; k < report.length ; k++) {
            if(report[k].column === col ){
               find = true;
               setReportError(k,text);
               break;
            }
      }
      if (report.length==0 || !find ) {
           addNewReport(obj.name, col, text);
      }
}
function generateReport(obj, case, i, j) {
   var col = getExcelColumnName(j);
   if (obj.required && case==="" || case == undefined) {
          var error = "Row "+(i+1)+" :  This field is required. ";
          addReport(obj,col,error);
          return;
   }
   
   switch(obj.type){
     case "String" : 
       if (typeof case !== 'string' || case instanceof String){
          var error = "Row "+(i+1)+" :  This field should be a string . ";
          addReport(obj,col,error)
       }
     break; 
     case "Number" : 
     if(typeof case != 'number'){
        var error = "Row "+(i+1)+" :  This field should be a number. ";
        addReport(obj,col,error)
        return;
     }
     break;
     case "Date" : 
     // to complete  
     if (moment(case, 'YYYY-MM-DD').format('YYYY-MM-DD') === case){
     var error = "Row "+(i+1)+" :  This field format is not correct. ";
     addReport(obj,col,error)
     }
     break;
    case "DateTime" : 
        // to complete  
     if (moment(case, 'YYYY-MM-DD HH:MN:SS').format('YYYY-MM-DD HH:MN:SS') === case){
     var error = "Row "+(i+1)+" :  This field format is not correct. ";
     addReport(obj,col,error)
     }
     break;
     case "Enum" || "Options" : 
      let error = true;
      for(let i = 0; i< obj.value.length; i++) {
           if (obj.value[i]===case) {
             error = false;
             break;
           }
      }
      if (error) {
        var error = "Row "+(i+1)+" :  This field format is not correct.it must contain one of those "+obj.value.join(',');
        addReport(obj,col,error)
      }
     break;
   }
}
function setReportError(k, text){
      report[k].error.push(text);
   }
   addNewReport(name, column, error){
     report.push({ name, column, error: [error] });
   }
function getExcelColumnName(num){
  num++;
  let columnLetter = "",
  t;
  while (num > 0) {
    t = (num - 1) % 26;
    columnLetter = String.fromCharCode(65 + t) + columnLetter;
    num = (num - t) / 26 | 0;
  }
  return columnLetter || undefined;
}

  downloadCsvFile() {
      const data = this.json_csv.data;
      const fileName = this.json_csv.file_name;
      const exportType = exportFromJSON.types.csv;
      if (data) exportFromJSON({ data, fileName, exportType });
    }
    
    addFile(e,type) {
      if(this.file!=undefined) {
        this.addNotification({message:'Sorry, you can upload only one file.',consoleError: 'Only one file can be drag.',});
        return;
      }
      let droppedFiles = (type==0)?  e.dataTransfer.files : this.$refs.file.files;
      if(!droppedFiles) return;

      if(droppedFiles.length>1) {
        this.addNotification({message:'Sorry, you can drag only one file.',consoleError: 'Only one file can be drag.',});
        return;
      }
      this.file = droppedFiles [0];
      this.parseFile();
    },
    handleFile(){
      if(this.file==undefined) {
        this.$refs.file.click();
      }
    },
    removeFile(file){
      this.files = this.files.filter(f => {
        return f != file;
      });
    },
    parseFile(){
      Papa.parse( this.file, {
        header: true,
        skipEmptyLines: true,
        complete: function( results ){
          this.content = results;
          this.parsed = true;
          this.generateSubmission();
        }.bind(this)
      });
    },
    generateSubmission(){
      // eslint-disable-next-line no-unused-vars
      this.createSubmissions(this.content).then((data)=>{
        this.value = 100;
        this.error = false;
        this.upload_state = 10;
        this.message = data.message;
        this.sendSubmission();
      //eslint-disable-next-line no-unused-vars
      }).catch((error)=>{
        this.value = 100;
        this.error = true;
        this.message = error.message;
        this.upload_state = 10;
        if(error.code==500) this.generateAndShowCsvErrorFile();
      });
    },
    createSubmissions(content){
      return new Promise((resolve, reject) => {
        if(content.data.length==0) reject({ message: 'Csv file is empty', code : 400 });
        let entries = Object.entries(content.data[0]);
        if (entries.length != this.formFields.length) reject({ code : 401, message: 'Header of this csv file is not compatible to this form.'});
        for (let i = 0; i<content.data.length; i++) {
          this.value = this.pourcentage((i+1));
          let entries = Object.entries(this.content.data[i]);
          for (let j = 0; j< entries.length; j++){
            // eslint-disable-next-line no-unused-vars
            let key = entries[j][0];
            // eslint-disable-next-line no-unused-vars
            let value = entries[j][1];
            let obj = this.fields.find(o => o.name === key);
            this.submissions[i][key] = value;
            this.generateReport(obj, value, i, j);
          }
        }
        if (report.length != 0){
            reject({ code : 500, message: 'Some errors have been found please download the report csv file to see them all.' });
         } else {
            resolve({ message: 'Your Bulk submission has been successful' });
        }
      });
  }

function generateAndShowCsvErrorFile() {
    const new_report = []
    for (let i = 0; i< this.report.length; i++){
    name, column, error: [error]
       new_report.push({
       name : this.report[i].name,
        column: this.report[i].column,
        error: this.report[i].error.join('|'),
       })
    }
     const data = new_report;
     const fileName = "error_report_"+moment();
     const exportType = exportFromJSON.types.csv;
    if (data) exportFromJSON({ data, fileName, exportType });
}

function sendSubmission() {
  const subsObj = [];
  for (let i = 0; i< this.submissions.length; i++){
    subsObj.push({ data: Object.assign({}, this.submissions[i]) });
  }
  service.sendListSubmission(subsObj);
}

This snippet above allow to check and report error in the csv file. Now if there is no error to report now we are going to create submission for each row an push theme to the server .

Other notes:

• Overall parsing process if submitters are uploaded a CSV file:

  • First we try to translate the .Csv row into a Json submission. ← Do we build the validation report based on that translation?

  • Once the Json submission is created for each row, we send the POST request using the CHEFS API. ← What type of error messages can the API return?

Acceptance Criteria:

top page

[List the Acceptance Criteria]

Prototype/Mockups

top page

Provide high fidelity prototype or mockoup

Out of Scope:

top page

• The setup of a file upload from the perspective of the form designer is not part of this User Story

• The review and validation of a file submission is not part of this User Story

• The download of the validation report is not included in this scope

Contact(s):

top page

This User Story is primarily built for the LTC