Workbooks

Workbooks

Goal

Workbooks try to reproduce financial datas management with Excel or LibreOffice workbooks.

Their meaning is to store financial datas to use with many formulas to calculate financial operations.

Structure owns Workbooks but they can’t determine their status that is the prerogative of Accompanists or possibly admins.

Workbooks contains multiple sheets, that contains multiple cells that contains datas. A workbook structure (its sheets and cells) is automatically created from its matrix, there is no possibility to manually modify it.

Scope

  • Can only be created from a Structure;
  • Have multiple level status which determine the progress of accompanying;
  • Can be exported to a valid Excel file;
  • Can allow to create an invoice from Accompanists;
  • Validated workbook enable Structure to evolve it each new year;
  • Structure can only fill datas during the phase 1;
  • Can be in a Simplified mode when the Structure has allready at least one validated Workbook;

Accompanying

During its registration a structure can possibly choose a Guide that will be associated to its account and will be automatically used when creating a new workbook.

Other accompanist are associated on the same way but they are automatically selected from the Structure profile during the structure account validation.

Although they are automatically filled, workbooks have their own assigned Accompanists that can be changed by an admin in workbook paramaters.

And so accompanist can only access to workbooks that they are assigned to.

Simplified mode

In Simplified mode :

  • Invoices are blocked;
  • Accompanying is optional depending on structure choice;
  • Workbook status choices is limited to a very simple set (Status #1 - En cours and Status #11 - Validé);
  • Only Structure (and possibly admins) can decides about and changes the workbook status;
  • Structure is free to evolve its workbook to a new one on each new year with or without its Accompanists;

Structure automatically gains the Simplified mode as soon as they have a first validated workbook (with Status #11 - Validé).

This mode is virtual for a Structure profile, meaning it don’t have a dedicated attribute to store it. Only workbooks created under this mode are marked as simplified. For the Structure, this mode is dynamically determined from its workbooks mode (if at least one is marked as simplified, the Structure is under this mode).

Evolution

When a workbook has been validated at least for phase 1, each new year the Structure can evolve it. That means, the Structure can duplicate it, choosing a new year to evolve to and then workbook financial years are move to fit it. The financial datas for years out of the financial years are dropped and new blank years are appended.

Resulting in the workbook keeps datas for financial years that are still in the year scope and can fill datas for new added financial years.

All evolved workbooks are created under the Simplified mode and so inherit from its behavior (optional accompanying, etc..).

Rules

The rules to enable workbook evolution are:

Status

There are many status that the workbook will goes on before to be validated or stopped.

There are two main phases, each of one has its own accompanist.

A phase can be validated or stopped, for all phase a stopped status will freeze the workbook therefore it cannot be filled anymore. A validated phase 1 will continue the workflow to the next phase, a validated phase 2 finalize the workbook workflow and validate it definitively.

A Structure and its assigned Accompanists should receive email notifications about status changes depending on the new status, their assignation and their rights.

Sheets

A workbook is divised into multiple sheets that contains cells.

  • Sheets are not removable or addable, they are automatically created from the workbook matrix;
  • Each sheet has its own unique name for a same workbook;
  • Each sheet can be hided to Structure or/and Guide though there is no interface to change it, this is automatic depending on the workbook matrix;
  • Store a boolean (True/False) value about finalized marks for Structure and each accompanist. This is a value manually setted from the user when they think they are done with the sheet datas, it does not apply real behavior on workbook or sheet excepting user cannot edit sheet data again. Also it is used to calculate progress bar on workbook;
  • Finalized mark cannot be checked for a sheet if the previous sheet has not been checked (to ensure linear progress);

Cells

Sheets contains many cells that are either :

A formula cell

It cannot be edited and are not stored in database so this out of scope of this documentation part (this is more related to Workbook matrix).

The formula comes from the workbook matrix and are evaluated on demand (when they are displayed or when another formula cell depend on it).

A data cell

Where a user can fill a data (numeric or text).

This is stored in database and used by formula to calculate their value.

Additionaly to its inputed value, a data cell store other infos:

  • Its Excel coordinates code into the sheets like “A1” or “D21” (like in Excel);
  • A kind (equivalent to a type) that determines how to manage value (a numeric, a text, a long text, etc..);
  • A format that determines the format type to apply on display or to reverse when saving submitted value;
  • A mark about the user kind that edited it, that will display a different font color for each kind, so the user can determine visually who has edited the value lastly;

The inputed value is stored in database into a text field and so its real type is resolved from Python code when used.

Also like a sheet, a cell cannot be removed or added because it depends on workbook matrix that define them.

Cell help bullet

For each cells, a help text can be selected that will result into a tiny help bullet: an icon displaying a help text when hovered.

Help text are managed by admins only and defined for workbook matrix types and so are the same for workbooks of the same type.

A help bullet on a cell is just a relation to the help text, stored into its own model and so the help text can be shared into all matrix types.

Note

On displayed sheets, you can see other cells that are simple static text out of the matrix (they are not formula nor data cells). These fake cells cannot embed help bullets because they don’t have a real existence into the matrix.

Invoices

When its phase has been validated, an accompanist can produce an unique invoice for a workbook.

  • This is only for Guide and Supervisor, not for DLA Accompanist;
  • An admin can block invoices on a workbook if needed, this is an option for each phase, by default invoices are allways enabled;
  • Invoices cannot be produced for a workbook in Simplified mode;
  • Invoices are unique for a same workbook and a same accompanist kind. If assignated accompanist for a phase is changed after he produced an invoice, the new assignated accompanist will not be able to produce a new invoice;
  • An invoice is available on download as a PDF file for admins (that have the right permission) and the accompanist that produced it;
  • To produce an invoice, the accompanist have to anwser to some questions to determine if it is possible. If not, the invoice will be blocked at the end of the form process;

Warning

Culturdiag has an additional restriction layer for invoices that is not implemented on inserdiag.

This restriction is called “Forbidden Invoice Region” (Région interdite à la facturation) and store zipcodes.

When workbook owner zipcode (in its Structure profile) exist in “Forbidden Invoice Region”, accompanists won’t be able to produce an invoice, regardless of workbook attributes to enable or disable invoices on it.

Because it an unobtrusive feature, it should probably be merged into autodiag to be available also for other plateforms like inserdiag.