EC logoEliot Cornu

Case study · Professional project

Production ERP

Custom management software for a stone-cutting workshop: from quote to delivery, with stock, scheduling and statistics.

  • Python
  • SQLite
  • LibreOffice
  • Security
  • pywebview

Anonymised project. The client, its data, prices and documents are not shown: the diagram and examples are generic.

Context

The workshop ran its orders with spreadsheets: a quote template, a price list and a customer tracking file kept by hand. Every document (quote, order acknowledgement, production sheet, delivery note) was copied from one file to the next, each time with a risk of error on prices or tracking.

Goal

A single piece of software, installed on the company computer and working offline, that follows an order end to end with no re-typing, without changing the documents customers know: the files it produces are still those of the existing spreadsheet template.

Working mock-up

Working mock-up of the production ERPAn order goes through six steps: customer record filled from the SIRET company number, priced quote, order acknowledgement with discount, production sheet split across machines, delivery notes per pallet, then tracking and statistics.SIRETDVARC-%BL
The diagram follows an order from first contact to closing. Everything runs on the computer: only the SIRET lookup queries the public French company API.
  1. Customer: the record is filled from the SIRET number (public French company API), with the address split into number, street, postcode and town.
  2. Quote: stone lines (dimensions, quantity, finishes); the calculation reproduces the spreadsheet template formulas exactly, then the document is generated from that template.
  3. Order: order acknowledgement with lead time and customer discount; prices are frozen at quote stage and never move afterwards.
  4. Production: production sheet: each line is split across the workshop machines, week by week, in a Gantt-style schedule. The stock of blocks and cuts is tracked in three dimensions, with overlap checks.
  5. Delivery: partial delivery notes, quantities split per pallet, remaining quantity line by line; the order closes once everything is delivered.
  6. Tracking: the tracking file updates itself (with a dated copy before each write); turnover, indicators, stock losses and machine load, each chart with its calculation method.

Screenshots

ERP screen listing jobs with their quote or order status; names and figures hidden.
Job list: number, client, status (quote, order confirmation…), date, material, volume, amount and owner.
ERP statistics screen: bar chart of monthly revenue and job table; figures hidden.
Statistics: monthly revenue over the chosen period, with the jobs it counts.
ERP stock screen: 3D view of a block with two cut pieces, and the table of cuts; figures hidden.
Stock: a stone block in 3D with the pieces already cut, their position and category.

Anonymised screenshots: names, numbers, dates and amounts are hidden.

Approach

  1. Gathering needs and writing the specification: interviews with the workshop, study of the existing files (quote template, price list, tracking), and a record of business rules that had never been written down: categories by thickness, rounding to standard thicknesses, document numbering.
  2. Analysing what existed before coding: the spreadsheet template formulas were rewritten in Python as a mirror, then checked by tests comparing the software totals with the spreadsheet totals.
  3. Evolving in short versions: about ten versions, each driven by feedback from use: customer discount, partial deliveries, machine schedule, block status, statistics, choice of output folders. Earlier versions are archived.
  4. Testing at every change: about 130 automated tests cover calculations, document generation, deliveries, stock and licensing.
  5. Protecting the data: no network port opened, nothing loaded from the Internet; source files opened read-only; a dated copy before any write, and no write if the file is open elsewhere; deleting sends documents to a recycle bin, never to erasure.
  6. Shipping an installable application: Windows build with an installer, licence bound to the computer (key signed with HMAC-SHA256 from a machine identifier); the key generator stays outside the shipped package.

The life of an order at a glance

Quote

Prices are computed from the price list, then frozen for the whole order.

Order and production

The acknowledgement confirms lead time and discount, the production sheet schedules the workshop.

Delivery and closing

Delivery notes follow one another until everything is delivered: the order is then closed.

Results

Limitations