[Spike] Passing information from app onto Spreadsheet to print - Attendance Calendar

[rebeccakarasiktw]

📋 Summary

The goal of this spike is to make a plan & estimate for implementing the "Paper Calendar" feature.

🇺🇸 States this change applies to

• [] ALL
• [X] Nebraska
• [ ] Illinois

💻 Implementation Details

Data we will need to pull into the spreadsheet

• Provider Name
• Month & Year
• Child Name
• Check in & Check out time (including if there are 2 check in s& check outs)
• Total Hour Units
• Total Day Units

Questions we want to answer

• What, of the list above, are we able to pull into the spreadsheet? We can pull all the data.

• What is the best way to get the information from the app to the spreadsheet? Using RubyXL, we can parse the xlsx files and then write the information to a new file.

• Keep in mind that there are two tabs. We can separate them as 2 documents, from 1-15th and 16-31/30th.

• Are we able to / how can we make sure that there is only spreadsheet filled out for each case (i.e. all siblings go on one spreadsheet)? Yes.

• Will it be possible to add up the days 1-15 added. up? What is the estimate on doing this part?

  

  Yes.

• As per the policy guidelines, when there is an absence you need to put (A) . How can we make sure to do that? Check if there is a service day and then check whether the service day is an absence.

• Would it be possible to limit the Absences to the first 5 of the month (as I believe that is what Providers are supposed to do on the calendar as well.) Yes. We can iterate over the days chronologically and then track the number of absences for a child.

• Would it be possible / how much additional effort would it take to have different spreadsheets depending on the provider type? (Keep in mind this would be low priority as the only important difference is License Exempt providers)

Spreadsheets attached Child Care Center Fillable Calendar (edited) (3).xls In-Home (License Exempt) Fillable Calendar (edited) (2).xls License Exempt Home & Licensed Family Child Care Home I & II Fillable Calendar (edited) (3).xls

[katelovescode]

Open questions:

• is PDF or CSV preferred for MVP? We can do both, but we should prioritize one first.

[rebeccakarasiktw]

@katelovescode updated to just limit to spreadsheet

[OliverCai0]

@rebeccakarasiktw @katelovescode, looking into this, a current issue is that the spreadsheets are in xls format which is largely unsupported by ruby and a lot of other languages, meaning that a lot of the common ways to parse and write to the file results in the formatting being disrupted. I was wondering if it's a necessary requirement to keep the files in xls or if it is possible to just convert them into xlsx files?

[OliverCai0]

All implementation is currently on the PIE-2785/spreadsheet_info branch. There is currently a job along with a service that outputs a set of xlsx files for each approval of a specified user for a given month and year. The forms are also temporarily in the same directory as the service app/services/attendance_spreadsheet, and the resulting files are, for now, stored in the storage/ directory. Remaining considerations include the appropriate storage mechanism for the generated files (RubyXL seems to have an option to write to a stream rather than saving directly to a disk, documentation can be found here: https://github.com/weshatheleopard/rubyXL).

To test, run

1. rails c
2. SpreadsheetAttendancesImporterJob.new.perform_now(user_id: USER_ID, month: MONTH_NUM, year: YEAR)

The resulting files have the current naming convention: attendancesfor{date-range}_{approval_index}.xlsx. There should be two workbooks for every approval, one for the date range of 1-15 and another for 16-31.