Menu
This is a work in progress. Projected completion date of the project is 4/30/2024, and I will update the post as I complete everything.
Our sales and marketing department have a huge amount of work to complete every time prices changes. The sales department hands them a really screwy Excel spreadsheet with the parts they want on the price sheet, along with the target price and various discount price levels (10%, 8%, 5%, etc.). The marketing department takes that and adds the descriptions, images, section titles, table of contents, and introductory pages. Different customers get different discount pricing, so they have to produce multiple different versions.
It is a huge undertaking. And it is happening more frequently.
They spoke to a 3rd party service that specializes in automating this process. But, that service was priced a little outrageously and our company prefers to never pay more than 5¢ for anything. So, they came to me to see if they could automate the process of producing the price guides in a more automated fashion.
Example image showing someone else’s price guide.
So, this post is going to detail the process I went through to accomplish all of this. Below is some information regarding the technology stack used.
API
Rest API, custom designed, but not entirely different from the BezKoder Node.js Rest API. If you follow his documentation you can create something that will work for this project.
Web Server
Simple Nginx web server running internally
Web Pages
All web pages are bootstrap and mostly vanilla JS
The API has 4 tables dedicated to this process.
Part List
Exactly what it sounds like. Simple database table with the part number, active/inactive/disabled, description, 3-4 price levels, product family, product sub-family, some standard specification fields.
Templates
Some versions of the price guide will show all the price levels. Some will only show the target price. Some might have a field for a distributors contact info at the top, including their logo. Some might be missing certain parts. Some might include different product spec fields in the table. The templates are the information that needs to be included in each guide.
They are used by the scripts that run on our internal scripting server to build the guide. The script will check the API, nightly, to see if any data has been updated. If so, it will rebuild the Guides. Old guides will be moved to the Versions table.
Guides
Once the parts and templates are created, a script will run on our internal script server that processes the data. It will build the finished JSON data used to create the PDF files. That info will be stored in the Guides table.
When generating new Guides, it will check the Versions table to find the last version number. That number will be incremented and included in the Guides table, along with a link to the PDF file that will be generated.
Versions
When a new Guide is generated, the old guides are archived in the Versions table. The table will contain a field for version number, date archived, and similar information. An exact copy of the old data in the Guide table will be included. The script that performs the archive action will also archive the old PDF files, changing their file names to include a version numbers. The Versions table will include a link to that file, and thee version number in the table will match that in the file name.
The script that builds the guide will substitute the fields in the HTML template with the data from the JSON. In the example below, you can see one subsection that has title, images and dummy data has been added. Below the initial subsection are two more subsections, with only the variable fields.
{sectionName}
SNEAKERS
| PRICE LEVEL | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| IMAGE | PART NUMBER | DESCRIPTION | 10% | 8% | 5% | COLOR | MATERIAL | ||
 |
SNKR 1 B | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Brown | Leather | ||
| SNKR 1 C | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Chocolate | Leather | |||
| SNKR 1 G | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Green | Leather | |||
| SNKR 1 W | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | White | Leather | |||
 |
SNKR 2 B | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Brown | Cotton | ||
| SNKR 2 C | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Chocolate | Cotton | |||
| SNKR 2 G | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Green | Cotton | |||
| SNKR 2 W | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | White | Cotton | |||
 |
SNKR 3 B | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Brown | Nylon | ||
| SNKR 3 C | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Chocolate | Nylon | |||
| SNKR 3 G | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Green | Nylon | |||
| SNKR 3 W | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | White | Nylon | |||
 |
SNKR 4 B | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Brown | Snakeskin | ||
| SNKR 4 C | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Chocolate | Snakeskin | |||
| SNKR 4 G | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | Green | Snakeskin | |||
| SNKR 4 W | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat | $25.00 | $25.00 | $25.00 | White | Snakeskin | |||
{subSectionName}
| {prcColumnGroupHeader} | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | ||
| {prGrpImg} | {partNumber} | {description} | {prc1} | {prc2} | {prc3} | {color} | {special} | ||
| {partNumber} | {description} | {prc1} | {prc2} | {prc3} | {color} | {special} | |||
| {partNumber} | {description} | {prc1} | {prc2} | {prc3} | {color} | {special} | |||
| {partNumber} | {description} | {prc1} | {prc2} | {prc3} | {color} | {special} | |||
| {prcColumnGroupHeader} | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | ||
| {prGrpImg} | {partNumber} | {description} | {prc1} | {prc2} | {prc3} | {color} | {special} | ||
| {partNumber} | {description} | {prc1} | {prc2} | {prc3} | {color} | {special} | |||
This site is primarily for screwing around. There is no need to contact me, but if you insist you can find me on Mastadon at https://mastodon.social/@djinnsour.