Maintaining and publishing a church membership directory is a meticulous process that often requires managing sensitive data and adhering to strict timelines. Traditionally, this would involve significant manual effort, often taking days to complete. In this blog post, I will share how I streamlined this process by automating the workflow using open-source tools. This approach not only reduced the time from several hours to under 13 minutes but also ensured accuracy and repeatability, setting a benchmark for efficiency in handling similar projects. Specifically it should be noted that the complicated sorting they needed for the final output could have taken the same time if done manually in case of last minute changes like addition or removal of a member that too if a head of the family expired and has to be updated before taking final output the whole prayer group sorting can affect. Consider the head of the family name was starting with Z and when removed automatic upgrade of the next member to head of family and the name starts with A the whole prayer group layout has a chance to take drastic change and manual layout would be herculian in this case. But with this implementation of automation, that is another 15 minutes to the maximum just a flag change in the xls and the command line “make directory” will run through the full process.
Workflow Overview
The project involves converting an xls file containing membership data into a print-ready PDF. The data and member photographs are maintained by a volunteer team on Google Sheets and google drive, these are shared via Google Drive. Each family has a unique register number, and members are assigned serial numbers for photo organization. The workflow is orchestrated using GNU Make, with specific tasks divided into stages for better manageability.
Stage 1: Photo Processing
Tools Used:
- Bash Shell Scripts for automation
- ImageMagick for photo dimension checking and resizing
The photo directory is processed using identify (ImageMagick) to determine the dimensions of each image. This ensures that all photos meet the required quality (300 DPI for print). Images that are too large or too small are adjusted using convert, ensuring consistency across all member profiles.
Stage 2: Importing Data into MySQL
Tools Used:
- MySQL for data management
- Libre Office Calc to export xls to csv
- Bash and PHP Scripts for CSV import
The exported CSV data is imported into a MySQL database. This allows for sorting, filtering, and advanced layout calculations, providing a structured approach to organizing the data.
Stage 3: Data Sorting and Layout Preparation
Tools Used:
- MySQL Queries for layout calculations
The data is grouped and sorted based on location and family register numbers. For each member, a layout height and page number are calculated and updated in the database. This ensures a consistent and visually appealing directory design.
Stage 4: PDF Generation
Tools Used:
- PHP and FPDF Library
Using PHP and FPDF, the data is read from MySQL, and PDFs are generated for each of the 12 location-based groups. During this stage, indexes are also created to list register numbers and member names alongside their corresponding page numbers.
Stage 5: Final Assembly and Indexing
Tools Used:
- GNU Make for orchestration
- PDF Merge Tools
The 12 individual PDFs generated in the previous stage are stitched together into a single document. The two indexes (by register number and by member name) are combined and appended to the final PDF. This single document is then ready for print.
Efficiency Achieved
Running the entire workflow on an ASUS A17 with XUbuntu, the process completes in less than 13 minutes. By comparison, a traditional approach using desktop publishing (DTP) software could take 20–30 hours, even with a skilled team working in parallel. The automated workflow eliminates manual errors, ensures uniformity, and significantly improves productivity.
Key Advantages of the Automated Workflow
- Time Efficiency: From 20–30 hours to 13 minutes.
- Accuracy: Eliminates manual errors through automation.
- Scalability: Easily accommodates future data updates or layout changes.
- Cost-Effective: Utilizes free and open-source tools.
- Repeatability: The process can be executed multiple times with minimal adjustments.
Tools and Technology Stack
- Operating System: XUbuntu on ASUS A17
- Photo Processing: ImageMagick (
identifyandconvert) - Database Management: MySQL
- Scripting and Automation: Bash Shell, GNU Make
- PDF Generation: PHP, FPDF Library
- File Management: Google Drive for data sharing
Conclusion
This project highlights the power of automation in handling repetitive and labor-intensive tasks. By leveraging open-source tools and orchestrating the workflow with GNU Make, the entire process became not only faster but also more reliable. This method can serve as a template for similar projects, inspiring others to embrace automation for efficiency gains.
Feel free to share your thoughts or ask questions in the comments below. If you’d like to adopt a similar workflow for your organization, I’d be happy to provide guidance!