Dylan's Portfolio

Quick Specs

• Company: EECOL Electric
• Language: Python
• Number of Databases: 11
• Reports Created Every Month: 25+

Libraries Used

Software Design UML

When designing the software for this project, I aimed to adhere to the principles of object-oriented design: SOLID. As well, I wanted to ensure the concepts of polymorphism, encapsulation, data abstraction, and inheritance were followed in the code. I wanted to use this methodology to ensure that whether it is I or a collaborator, future development progress can be made as smoothly as possible. The below UML follows these concepts as well as the principles of SOLID:

Project Details

Prior to the development of this program, employee's at EECOL were required to manually "run" over 25 different reports. These reports are Excel files that typically contain 1-12 SQL queries. These Excel files usually contain a summary tab that does calculations on the data and presents it in an easy to read format. Every month, employee's would open last month's report, change the dates of the SQL queries, refresh the query and verify it executed correctly, then would save the file with a new file name in the desired location. Sometimes these reports would need to be distributed to other employee's via specific email address or mailing lists. Currently, the program automates nearly all of these tasks.

EECOL was not looking to purchase additional software to automate these tasks, so it was decided to develop our own solution. When deciding on a language to use, python was chosen due to the well documented libraries for working with Excel files such as openpyxl and xlsxwriter as well as the fact that the EECOL team had prior experience working with python for other projects. The code was written and tested on a virtual machine running Red Het Enterprise Linux.

Every month, a scheduler starts the program with a Controller class. The Controller has details about each report it will be working on loaded in with a .yaml file. Based on how the user configured the Controller, it will run either all or a subset of the reports. Each report pulls its necessary SQL statement templates from a database that contains all of the SQL used for the reports. These templates then have the necessary date parameters inserted for the month the report is being ran for. These SQL statements are executed using QueryManager which contains all of the functions necessary for running the SQL through one of the eleven potential databases. The results of these queries are passed to a GEN_XX file which inherits from GEN and is customized for each Excel file. The GEN writes in all data to the .xlsx and applies report specific formatting. After the GEN is finished working on the Excel file, the program concludes by saving status and error info into the Excel file properties section and into a .txt file.

Future features I want to include with further development include auto-generated SQL date parameters, the option to have reports emailed after their creation, and an easy to use GUI.