Building a Safety Equipment Warning System with FineReport – Step‑by‑Step Guide

1. Data Import

Create a MySQL table to store safety‑equipment records. In FineReport Designer, build a new CPT template, define the header, and enable the toolbar with Submit, Export, and Import‑Mark functions.

Set up the import page to include a batch identifier using the formula CONCATENATE($fine_username, '-', now()), so all rows from the same upload share the same batch number.

Define column formulas to convert the "Detection (Warranty) Time (Months)" field to the first day of the month, e.g.,

IF(G2='无', NULL, DATEINMONTH(REPLACE(concatenate(G2, '.01'), ".", "-"), 1))

, and similarly for the disposal date column.

Calculate the remaining usage months with if(len(Q2)=0, NULL, DATEDIF(today(), Q2, "M", -1)).

2. Template Configuration

In the template’s "Report Fill‑in Properties", configure the Submit button to trigger the data‑save action. Add built‑in validation rules for each column, for example:

if(K2="根据实际坏损情况报废", true, regexp(K2, "^20\\d{2}\.(0[1-9]|1[0-2]|[1-9])$"))

– validates the disposal date format.

if(G2="无", true, regexp(G2, "^20\\d{2}\.(0[1-9]|1[0-2]|[1-9])$"))

– validates the warranty date. if(len(map(B2, "地市校验", 1, 1)) <> 0, true, false) – checks that the city exists. if(map(C2, "所属部门校验", 2, 1) = B2, true, false) – validates department‑city mapping. if(L2 <> '无' || len(R2) <> 0, R2 = L2, true) – ensures remaining months match the disposal date. regexp(H2, "^[1-9]\d*$|^0$") – validates inventory numbers.

Two template datasets are created: one for distinct city names ( SELECT DISTINCT city FROM `au`) and one for city‑department pairs ( SELECT DISTINCT city, department FROM `au`).

3. Decision Report and Pagination

Create a decision report with an absolute canvas (1200×600). Add a data set to count records per import batch:

SELECT import_batch, count(*) as counts, creator, created_at FROM `lifecycle_inventory` WHERE creator='${fine_username}' GROUP BY import_batch, creator, created_at ORDER BY created_at DESC

Build pagination controls (labels, dropdowns, previous/next buttons) using widgets such as curPage, totalPageNum, and pageSize. Example JavaScript for the previous button: if(curPage <= 1){ this.setEnable(false); } Configure hidden widgets to store total records, total pages, start index, etc., with formulas like ROUNDUP($totalRecords / $pageSize) and if(($curPage-1)*$pageSize < 0, 0, ($curPage-1)*$pageSize).

4. Batch Deletion

Design a plain report to delete a specific import batch using the query:

SELECT * FROM `lifecycle_inventory` WHERE import_batch='${ib}' AND creator='${cr}' AND created_at='${ct}'

Attach JavaScript actions to buttons for confirming or cancelling the deletion, e.g., window.parent.FR.closeDialog(); and contentPane.verifyAndWriteReport();.

5. Warning Tables and Detail Views

Define a warning table dataset that calculates red, yellow, and blue warning counts for disposal and maintenance dates using CASE statements and DATEDIFF against CURDATE(). Example snippet for disposal warnings:

SUM(CASE WHEN DATEDIFF(disposal_date, CURDATE()) <= 0 THEN 1 WHEN DATEDIFF(disposal_date, CURDATE()) <= 30 THEN 1 ELSE 0 END) AS red_disposal_warning_count

Similar logic is applied for yellow (31‑60 days) and blue (61‑90 days) warnings, both for disposal and maintenance intervals.

Create detail‑view reports (e.g., "查看预警详情.cpt") that filter by city, line, warning category, and level using parameters ${city_param}, ${line_param}, ${warning_category}, and ${warning_level}. The SQL includes conditional WHERE clauses built with ${if(...) constructs.

6. Dashboard Design

Use FineReport’s chart components (pie, bar, gauge) to visualise warning statistics. Four main datasets are defined: ds1: warning‑type proportion (disposal, maintenance, none). ds2: warning‑level distribution per type. ds3: city‑wise warning distribution. ds4: equipment‑wise warning distribution. ds5: city‑wise warning counts for bar charts.

Each dataset uses WITH clauses and UNION ALL to combine red, yellow, blue, and no‑warning rows, then filters with dynamic parameters to enable drill‑down.

7. Final Remarks

By following these steps, a complete safety‑equipment warning management system can be built in FineReport, turning raw inventory data into actionable visual insights for management.