Manitou Data Converter

Introduction

This solution was developed taking into account the absence of a web API for the Manitou application as well as the fact that it is easier to use data from a database than a batch of XML files.

This solution:

1. Expects XML files (i.e., data/input/xml/*.xml), manually extracted from the Manitou application (i.e., Log In → General → Export data (beta));
2. Reads the said files;
3. Converts them to the JSON data format;
4. Imports the data to Azure SQL Database;
5. Which can then be used by solutions such as Power BI.

To do:

• Manitou should include access to live data and its statistics, through to a web API, with secure authentication (i.e., OAuth 2.0);
• Manitou data should be segregated (i.e. one database per customer) and fully accessible by its owner, in order to comply with the Act to modernize legislative provisions as regards the protection of personal information, which will come into force on 2023-09-22, in the province of Quebec;
• Manitou data should be segregated so that a superuser account is not required to export basic information;
• Manitou should include a reporting module (i.e., Power BI);
• Manitou data export module must be automatic, recurring and its data must be live (i.e., based on a replication of the production database, via a queuing mechanism).

Flowchart

flowchart TD
    A[Manitou] -- Daily extraction, at 22 h --> B[XML]
    B[XML] -- Convert --> C[JSON]
    C[JSON] -- Import --> D[Azure SQL Database]
    D[Azure SQL Database] -- Refresh --> E[Power BI]

Loading

---

Install this solution

1. Download and extract the project from GitHub (e.g., Code → Download ZIP → go to This PC → Downloads → select file → context menu → Extract All... to C:\temp\manitou);
2. Run Windows PowerShell as an administrator;
3. Execute: & "C:\temp\manitou\install.ps1";
4. Copy "conf/sql_template.json" to "conf/sql.json" and contact your system administrator for assistance in completing the Azure SQL Database configuration.

Export data from Manitou

1. Log in to Manitou, with a superuser account;
2. Go to General → Functions → Export data (beta);
3. Click + Add, select a Start of extraction (i.e., 2022 January), enter a Number of periods to export (i.e., 6 (months, including the selected Start of extraction)) and select a Requestor (i.e., yourself);
4. Wait after 22 h that night for the query to run;
5. Click on the corresponding Consult button (paperclip icon) to download the archive;
6. Right-click the archive → Extract All... → Select a destination folder (i.e., C:\temp\manitou\data\input\xml) → Extract;

Convert and import data using this solution

1. Run Windows PowerShell;
2. Execute python "mdc.py" --input "data/input/xml/*.xml" --output "data/output/json" --convert --sql --verbose.

Execution example:

[2022-08-09 13:21:22] Deleted 15 JSON files in data/output/json.
[2022-08-09 13:21:22] Found 35 XML files in data/input/xml.
[2022-08-09 13:21:22] Wrote 235 documents to data/output/json/absences.json.
[...]
[2022-08-09 13:21:30] Importing JSON data to 15 tables in Azure SQL Database...
[2022-08-09 13:21:30] Dropped 3 tables.
[2022-08-09 13:21:40] Imported 424 documents to absences.
[...]
[2022-08-09 13:59:16] Creating columns and indexes...
[2022-08-09 13:59:16] Created index_absences_absenceId on absences.absenceId.
[...]
[2022-08-09 13:59:42] ** DONE **

For help, use: python "mdc.py" --help.

The data will be imported, as JSON, into the "value" field, of the following tables, in a database named "manitoudb", in an Azure SQL Database instance.

• absences;
• activitiesTransfer;
• billedIncomes;
• CRMAccounts;
• employees;
• employeeSalaries;
• projectActivities;
• projectAssignments;
• projectContracts;
• projectDeliverables;
• projects;
• subcontractors;
• summaryDayAssignments;
• summaryDayEmployees;
• timeMoneyAdjustments.

Export queries in HTML table and Chart.js formats (optional)

1. Run Windows PowerShell;
2. Execute python "mdc.py" --output "data/output/html" --export --verbose;
3. Find the resulting HTML files in the output subdirectory.

Import data into Power BI

Create new report

1. Install Power BI;
2. Run Power BI.

Get data from JSON data files

3. File → Get Data → Get data to get started → JSON → Connect;
4. Select the first file → Open;
5. Home → New Source → JSON → JSON;
6. Select next file → Open;
7. Repeat steps 5 and 6 for each file.

Save Power BI file

9. Close & Apply;
10. File → Save → File name: "C:\temp\manitou\data\manitou.pbix".

Refresh reports

1. Repeat export and convert steps;
2. Refresh data in Power BI, using Home → Refresh;
3. Save Power BI file, using File → Save.

---

About Manitou

• Application: Manitou;
• Developer: Manitou Solution;
• Address: 412-825, Lebourgneuf Boulevard, Quebec, QC, G2J 0B9;
• Technologies: APEX, JS, PHP, WordPress;
• Web server: Nginx;
• Web API: Oracle REST Data Services (ORDS).

Related solutions

• Dynamics 365;
• JIRA;
• Nethris;
• Sage.

About this solution

© Simon Charest | Forensik | In Fidem | Atos. All rights reserved.

Requirements

• Azure SQL Database;
• Git (optional);
• GitHub (optional);
• Manitou;
• SQL Server Management Studio (optional);
• Power BI (optional);
• PowerShell;
• PyCharm (optional);
• Python;
  • colorama;
  • pip;
  • pyodbc;
  • Python O365;
  • setuptools;
  • xmltodict.

About the author

Mr. Simon Charest, Software Developer and CEO at SLCIT Inc.

• Email;
• GitHub;
• LinkedIn.