Get a Teacher Relocation Payment - Report Template

Description

The report template model captures information on how to use a saved template. It includes the file name, file payload, the report that can use the template, and its configuration.

Home Office Excel

For Reports::HomeOfficeExcel, the configuration is a hash data containing the following keys:

• worksheet_name: identifies the name of the sheet in the Excel file to save the report output.
• header_mapping: a hash mapping the template column name to the application fields to be extracted. Each key is a column in the Excel file, and the value is an array of application fields.

If the array of application fields contains more than one field, the output is concatenated using the SQL CONCAT function.

Validation

The ReportTemplate model uses the HomeOfficeReportConfigValidator to perform basic checks on the report template file. These checks include:

• Parsing the Excel file.
• Verifying the existence of a worksheet name specified in the configuration.
• Checking the presence of the header_mapping in the configuration.

Usage

Adding a template

To add a report template, follow these steps:

1. Create a report_template_seed.rb file, assuming you have an Excel file ready at ./tmp/template.xlsx and you are in the Rails console. The Excel file should have a sheet named sheet2, and the first row of that sheet should contain the columns: Id, fullname, and field 1.

template_path = Rails.root.join('tmp/template.xlsx')
template = File.open(template_path)
configuration = {
  worksheet_name: "sheet2",
  header_mapping: {
    "Id" => ["reference"],
    "fullname" => ["first_name", "last_name"],
    "field 1" => ["relations.field_1"],
  },
}

report_template = ReportTemplate.new(
  filename: "template.xlsx",
  file: file,
  config: configuration,
  report_class: Reports::HomeOffice
)

if report_template.valid?
open(Rails.root.join('db/report_template.rb', 'w')) do |f|
  f.write("ReportTemplate.create(#{report_template.attributes})")
end
else
  report_template.errors
end

2. Copy the seed file to the environment:

$ kubectl -n <namespace> cp db/report_template_seed.rb <pod-id>:/app/db/report_template_seed.rb
$ make <environment> ssh
$ rails c

3. Load the seed file:

load(Rails.root.join('db/report_template_seed.rb')

Updating a template

To update a template, follow these steps:

1. Identify the previous report template.
2. Create a backup seed file.
3. Reproduce the steps for "Adding a template".
4. Delete the previous report template.

If an error occurs, rollback the report template using the backup seed file.