An automatic logistics document system and a processing method thereof

Abstract

This invention relates to the fields of data processing and logistics information technology, and particularly to an automated logistics document system, comprising: a user interaction module, a data processing engine module, a configuration management module, a report generation module, an automatic update module, and a progress feedback module. This invention also provides an automated logistics document processing method, including file recognition and loading, data initialization and cleaning, multi-source data association, automatic model extraction and replacement, classification and summarization, header data generation, report generation, and result saving and feedback. This invention is applicable to scenarios involving the automatic generation of standardized logistics documents such as packing lists, invoices, and customs declarations from multi-source Excel data; it significantly improves processing efficiency, offers extremely high accuracy, intelligent model processing, high flexibility and configurability, multi-source data fusion capabilities, and a user-friendly experience.

Description

Technical Field

[0001] This invention relates to the field of data processing and logistics information technology, and in particular to an automated logistics document system and its processing method. Background Technology

[0002] In import and export trade, the preparation of logistics documents (such as packing lists, invoices, customs declarations, etc.) is a tedious yet crucial task. Currently, companies typically export multiple raw data reports from enterprise resource planning systems such as SAP, including delivery note quality data (Y00_DN_MASS), price data (Y00_PRCI), packing list data (INST_LIST), processing unit information (Y00_HUMO), and commodity declaration element tables. These reports are stored in Excel format and have related fields (such as material number, customer material number, packing list number, etc.), but they are scattered across different files.

[0003] In existing technology, documentation personnel need to manually open multiple Excel files, integrate the data through copying, pasting, VLOOKUP, etc., calculate and summarize according to the customs broker's or internal requirements (such as total net weight, total amount, unit price), and finally fill it into a fixed template to generate formal documents. This process has the following drawbacks: Inefficient: Manually processing large amounts of data takes hours, and each operation is highly repetitive.

[0004] Common errors: When dealing with large amounts of data, issues such as pasting the wrong line, incorrect formula references, and incorrect summary calculations can easily occur.

[0005] Model verification is difficult: Product models are usually described in text form in the declaration elements, and it is easy to miss or replace them incorrectly when manually extracting and verifying the standard model.

[0006] Difficult to adapt to changes: When the report format, field names, or calculation rules change, Excel formulas need to be redesigned or macros need to be written, resulting in high maintenance costs.

[0007] Poor data consistency: Data between different documents (such as packing lists and invoices) should be consistent, but manual operation makes it difficult to guarantee complete synchronization.

[0008] In conclusion, the existing technology obviously has inconveniences and defects in practical use, so it is necessary to improve it. Summary of the Invention

[0009] To address the aforementioned shortcomings, the present invention aims to provide an automated logistics document system and its processing method, thereby solving the problems of low efficiency, error-proneness, and poor data consistency in the existing logistics document production process.

[0010] To achieve the above objectives, the present invention provides an automated logistics document system, comprising: User interaction module: Used to receive multiple Excel files selected by the user, and allow the user to specify the type for each file or have the system automatically match the type; Data processing engine module: Using the Python programming language and the Pandas data processing library, it performs core data cleaning, association, calculation, and summarization according to the configuration file to generate intermediate data; Configuration management module: Uses JSON format configuration files to define the reading methods of each data source, field mapping relationships, data initialization rules, classification and summarization rules, data association rules, sorting rules, report template paths, etc., to achieve separation of business rules and code; Report generation module: Based on the configuration file and template, the processed data is written into multiple Excel worksheets to generate a standardized logistics document containing multiple worksheets, i.e., the final document; Automatic update module: Asynchronously checks the server version upon startup, downloads the new installation package, and automatically upgrades; Progress feedback module: During data processing, it sends the current step, progress percentage and log information to the user interaction module in real time through signals, and pops up a prompt when the processing is completed.

[0011] This invention also provides a method for automated processing of logistics documents, comprising the following steps: Step 1, File Recognition and Loading: Read multiple Excel files selected by the user, each file corresponding to a data source, and determine the reading method, field mapping relationship and data initialization rules for each data source according to a predefined configuration file; Step 2, Data Initialization and Cleaning: According to the data initialization rules in the configuration file, the data of each data source is cleaned. Step 3, Multi-source data association: According to the data association rules in the configuration file, data from different data sources are associated with one or more key fields. Data matching and fusion between different data sources are achieved through hash indexing to complete information and form an associated dataset. Step 4, Automatic Model Number Extraction and Replacement: The system iterates through the associated dataset, uses regular expressions to extract product model information from specified fields, compares it with the standard model, and automatically replaces it with the standard model if they do not match, and records the change information.

[0012] Step 5, Categorize and summarize: Based on the subtotaling rules in the configuration file, the dataset is grouped according to the specified grouping field, and aggregation calculations are performed on the fields within each group; Step 6, Generate header data: Based on the header generation rules in the configuration file, global information is extracted from the aggregated data to generate report header data; Step 7: Report generation, result saving and feedback: The report header data and the summarized detailed data are written into multiple worksheets of the same Excel file according to a predefined Excel template to generate standardized logistics documents.

[0013] As a preferred technical solution, in step 3, the data association rule is a multi-level association, including: Level 1: Perform equality matching on the primary association key; Level 2: If the primary association key fails to match, a multi-field combination backup key will be automatically used for matching. Level 3: If the combination of multiple fields fails to match the backup key, the key field is truncated and a regular expression fuzzy match is performed, allowing partial character matching; If all three levels fail, the data is marked as unrelated and an exception list is generated to prevent missing documents.

[0014] As a preferred technical solution, the conflict resolution of the data association rules is as follows: When a master record matches multiple records from a secondary table: First priority: Newest timestamp takes precedence, so the most recently updated record is used; Second priority: Data source weight takes precedence; If a conflict still exists, take the first result and record the conflict in the conflict log.

[0015] As a preferred technical solution, the data association employs a hash mapping acceleration algorithm, including the following steps: S31, determine the main table and the subordinate table, and specify the key fields for association; S32, iterate through all data in the table, using the key field value as the key and the entire record as the value, and build a hash dictionary; S33: Traverse each record in the main table and extract the key field values ​​of the current record; S34: Quickly search for the corresponding key in the hash dictionary to locate the matching sub-table data; S35, if the match is successful, the field from the secondary table will be merged into the current record of the primary table to complete the information completion; S36. After all records have been traversed, output the complete associated dataset.

[0016] As a preferred technical solution, in step 5, the classification and summarization rule adopts a classification and summarization algorithm: traverse the data list, store the records into the dictionary according to the grouping key, and then perform an aggregation operation on each list; The categorical aggregation algorithm performs grouping and aggregation according to the configuration file BG_LIST_COLLECT_RULE, and the steps are as follows: S51 reads configuration parameters such as grouping field, aggregation method, precision, and concatenation limit; S52, initialize an empty group dictionary, where the key is the group value and the value is a list of records in the same group; S53, iterate through the detailed data line by line, calculate the grouping key of the current record, and insert the record into the list of the corresponding group in the dictionary; S54, perform aggregation calculations for each group list: SUM: Summates numeric fields and rounds them to the specified precision; JOIN: Concatenates duplicate field values, supports a maximum number of values, and automatically wraps lines if the limit is exceeded; FIRST: Retrieves the specified field from the first record in the list; S55, grouping and aggregation completed, output the summarized dataset.

[0017] As a preferred technical solution, in step 2, the initialization rule types include: FILTER filter: Filters out rows that do not meet the criteria; ENUM enumeration conversion: Maps code values ​​to text descriptions; VALUE Fixed Fill: Fills the field with a fixed value; RE Regular Expression Cleaning: Uses regular expressions to remove specific characters from a field; EXIST Existence Flag: Sets a flag to 1 or 0 depending on whether another field exists; APPEND field concatenation: Concatenates the values ​​of multiple fields into a new field using spaces.

[0018] As a preferred technical solution, FILTER filtering filters records based on logical expressions, including the following steps: S211, Parse the filter expression with logical operators in the JSON configuration; S212 parses the expression into an abstract syntax tree (AST) to perform syntax verification, priority sorting, and redundant condition removal. S213, dynamically compiles the AST into a memory-executable function; S214 employs an incremental row-by-row judgment mechanism, filtering as it reads, without loading all data into memory at once. S215: Records that do not meet the conditions are discarded and recorded in the filter log, while records that meet the conditions proceed to the next stage.

[0019] As a preferred technical solution, the RE regular expression cleaning, which performs character replacement or format standardization based on regular expressions, includes the following steps; S241: Load a multi-level regular expression rule chain from the configuration and execute it sequentially according to priority; S242 performs rule-by-rule matching and replacement on the target field: first remove special characters, then remove extra spaces, and finally unify the format; S243 supports retaining the whitelist of valid characters while removing only illegal characters; S244: After cleaning, the format is automatically checked. If it does not conform, it is marked and the default format is used. S245, record a comparison log before and after cleaning.

[0020] As a preferred technical solution, step 1, file identification and loading, includes the following steps: S11 allows users to drag / select multiple Excel files via the interface; S12, the system matches the type based on filename keywords; S13, reads the specified worksheet according to the JSON configuration, and maps the original columns to standard fields according to LOAD_FIELDS; S14: Remove all blank lines, convert to list / dictionary format, and complete data loading.

[0021] This invention provides an automated logistics document system and its processing method, applicable to scenarios requiring the automatic generation of standardized logistics documents such as packing lists, invoices, and customs declarations from multi-source Excel data. Compared with existing technologies, it has the following significant advantages: 1. Significantly improve processing efficiency: Reduce the original manual operation of several hours to several minutes, and generate a complete set of documents at once without the need for repetitive work.

[0022] 2. Extremely high accuracy: Automated data association and calculation eliminate errors caused by manual copying and pasting, and the automatic model replacement function avoids errors in model entry, ensuring that the data on the documents is accurate and consistent.

[0023] 3. Intelligent model processing: Model extraction and replacement based on regular expressions solves the problem of difficulty in manually verifying models, and records change logs for easy traceability.

[0024] 4. Highly flexible and configurable: Field mapping, cleaning rules, aggregation logic, and template selection can be adjusted through JSON configuration files, quickly adapting to different customer or business changes without modifying the source code.

[0025] 5. Multi-source data fusion capability: It can automatically link multiple Excel reports exported from SAP, complete information, and break down data silos.

[0026] 6. User-friendly experience: The graphical interface supports drag and drop, progress feedback, and error prompts, making operation simple and intuitive; the built-in automatic update function ensures that users always use the latest version. Attached Figure Description

[0027] Figure 1 This is a structural block diagram of the automated logistics document system in Embodiment 1 of the present invention; Figure 2 This is a flowchart of the automated logistics document processing method in Embodiment 2 of the present invention; Figure 3 A flowchart for file recognition and loading; Figure 4 A flowchart for data initialization and cleaning; Figure 5 A flowchart for multi-source data association; Figure 6 A flowchart for automatic model extraction, replacement, and categorization; Figure 7 A flowchart generated for the header data; Figure 8 A flowchart for report generation, result saving, and feedback. Detailed Implementation

[0028] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0029] Example 1: See Figure 1 This invention provides an automated logistics document system, comprising: User interaction module: Used to receive multiple Excel files selected by the user, and allows the user to specify the type of each file or have the system automatically match the type; a graphical interface based on the Qt framework, supporting drag-and-drop file addition, manual / automatic type matching, processing progress display, and result saving (supports drag-and-drop file addition, and displays the file name, type selection drop-down box and delete button in the table; the system automatically matches the file type based on keywords in the file name, and if the match fails, the user can manually select the type).

[0030] Data processing engine module: Using the Python programming language and the Pandas data processing library, it performs core data cleaning, association, calculation, and summarization according to the configuration file to generate intermediate data.

[0031] Configuration management module: It uses JSON format configuration files to define the reading methods of each data source, field mapping relationships, data initialization rules, classification and summary rules, data association rules, sorting rules, report template paths, etc., to achieve separation of business rules and code.

[0032] Report generation module: Based on the configuration file and template, the processed data (intermediate data) is written into multiple worksheets in Excel to generate a standardized logistics document containing multiple worksheets, i.e., the final document.

[0033] Automatic update module: Asynchronously checks the server version upon startup, downloads the new installation package, and automatically upgrades.

[0034] Progress feedback module: During data processing, it sends the current step, progress percentage and log information to the user interaction module in real time through signals, and pops up a prompt when the processing is completed.

[0035] Example 2: See Figure 2 The present invention also provides an automated processing method for logistics documents, comprising the following steps: Step 1: File recognition and loading.

[0036] See Figure 3 The file recognition and loading steps are as follows: S11 allows users to drag / select multiple Excel files via the interface; S12, the system matches the type by file name keywords: INST_LIST (packing list), Y00_DN_MASS (quality), Y00_PRCI (price), Y00_HUMO (processing unit), declaration element table; S13, reads the specified worksheet according to the JSON configuration, and maps the original columns to standard fields according to LOAD_FIELDS; S14: Remove all blank lines, convert to list / dictionary format, and complete data loading.

[0037] Specifically, it reads multiple Excel files selected by the user, with each file corresponding to a data source. Based on a predefined configuration file, it determines the reading method, field mapping relationship, and data initialization rules for each data source.

[0038] The configuration file is a structured rule file written in JSON format, stored locally on the system or on the server. It is used to uniformly define the business logic of the entire automated logistics document processing process, achieving decoupling of data processing rules from program code. When business requirements, data source formats, or document templates change, there is no need to modify the program source code; only the configuration file needs to be modified to complete the adaptation, greatly improving the system's flexibility and maintainability. It can be predefined and selected by the user.

[0039] The configuration file includes: Define the mapping relationship between the file identifier, worksheet name, original column name and unified fields within the system for each Excel data source; Define data cleaning rules for each data source, including filtering conditions, field value transformation relationships, fixed value filling content, regular expressions, existence marker conditions, and field concatenation rules; The DATA_RELATION node defines the rules for data association between multiple data sources, including the association order, key fields for association, and association method. The BG_LIST_COLLECT_RULE node defines the rules for categorizing and summarizing the dataset, including the grouping fields and aggregation calculation methods. The HEAD_COLLECT_RULE node defines the rules for extracting information from the header of logistics documents from detailed data and the method for extracting global information. SORT node: Defines the sorting field and ascending / descending order for each report.

[0040] In use, users select or drag and drop multiple Excel files through the interface. The system automatically matches the file type based on keywords in the filename (e.g., files containing "INST_LIST" are identified as packing list files), and allows users to manually correct the type. Each file corresponds to a data source, such as: Packing list data (INST_LIST): contains two worksheets: Component Information (COMP) and Processing Unit Information (HANDLING).

[0041] Delivery order quality data (Y00_DN_MASS): includes materials, customer materials, quantity, etc.

[0042] Price data (Y00_PRCI): Includes customer materials, net price, etc.

[0043] Processing unit data (Y00_HUMO): includes packing list number, gross weight, etc.

[0044] Declaration data elements include material number, HS code, English product name, and element description.

[0045] The system reads the specified worksheet of each file and renames the original column names to system-wide unified field names according to the field mapping (LOAD_FIELDS) in the configuration file LOAD_FILE. At the same time, it deletes empty rows, retains non-empty data, converts it into a list dictionary format, and completes the data loading.

[0046] Step 2, data initialization and cleaning.

[0047] See Figure 4 According to the data initialization rules in the configuration file, the data of each data source is cleaned.

[0048] Initialization rule types include: FILTER filter: Filter out rows that do not meet the criteria (e.g., keep only specific field values).

[0049] ENUM enumeration conversion: maps code values ​​to text descriptions (e.g., country code to country name).

[0050] VALUE Fixed Fill: Fills the field with a fixed value.

[0051] RE Regular Expression Cleaning: Use regular expressions to remove specific characters from a field (such as spaces and special symbols).

[0052] EXIST Existence Flag: Sets a flag (1 or 0) based on whether another field exists.

[0053] APPEND field concatenation: Concatenates the values ​​of multiple fields into a new field using spaces.

[0054] The FILTER filter, which filters records based on logical expressions, includes the following steps: S211, Parse the filter expression with logical operators in the JSON configuration; S212 parses the expression into an abstract syntax tree (AST) to perform syntax verification, priority sorting, and redundant condition removal. S213 dynamically compiles the AST into in-memory executable functions, avoiding repeated parsing and improving the speed of processing large amounts of data. S214 employs an incremental row-by-row judgment mechanism, filtering as it reads, without loading all data into memory at once. S215: Records that do not meet the conditions are discarded and recorded in the filter log, while records that meet the conditions proceed to the next stage.

[0055] The ENUM enumeration conversion performs data type conversion and enumeration mapping, including the following steps: S221: Read the first-level code mapping table and the second-level standard mapping table in the configuration to achieve two-level conversion between code, text, and standard text; S222 automatically determines the original data type of a field, recognizing text, numeric, and mixed-type codes; S223 calls the type adaptive conversion function to automatically convert between str / int / date; S224: The process is not interrupted when the conversion fails; default values ​​are automatically filled and abnormal fields are marked. S225, generate a transformation log, retaining the original and transformed values ​​for easy traceability.

[0056] The VALUE is fixed, and default values ​​are assigned to null or missing fields, including the following steps; S231, read the target field, populated value, and trigger condition specified in the configuration file; S232, Perform intelligent null value judgment: simultaneously judge null, empty string, full space, and illegal placeholder (such as " / ", "N / A"); S233: When the null value judgment is met and the trigger condition is met, write a preset fixed value; S234 supports multi-field linked filling: if field A is empty, field B is filled; if field B is empty, field C is filled. S235, after filling, uniform format is used to ensure consistency across the entire table.

[0057] The RE regular expression cleaning, which performs character replacement or format standardization based on regular expressions, includes the following steps; S241: Load a multi-level regular expression rule chain from the configuration and execute it sequentially according to priority; S242 performs rule-by-rule matching and replacement on the target field: first remove special characters, then remove extra spaces, and finally unify the format; S243 supports retaining the whitelist of valid characters while removing only illegal characters; S244: After cleaning, the format is automatically checked. If it does not conform, it is marked and the default format is used. S245, record a comparison log before and after cleaning.

[0058] The EXIST existence flag is generated as a Boolean flag field based on the associated field, including the following steps; S251, specify one or more judgment fields according to the configuration, and perform a combined existence judgment; S252, if all target fields are not empty → mark as fully exist (2); S253, if some fields are not empty → mark them as partially present (1); S254, if all values ​​are empty → mark as non-existent (0); S255, generate a new status flag field for subsequent association priority determination.

[0059] The APPEND field concatenation, which involves concatenating multiple fields in a preset order to generate a new field, includes the following steps: S261, Read the fields to be concatenated in the configured order; S262, perform automatic null value removal and filter invalid items; S263, connects by delimiter and automatically removes duplicate content; S264: When the maximum configured splicing length / number is reached, a newline character is automatically inserted. S265 generates new fields while preserving the original fields, without damaging the original data.

[0060] Step 3: Multi-source data association.

[0061] See Figure 5 According to the data association rules in the configuration file, data from different data sources are associated (Join) using one or more key fields as the link. Data matching and fusion between different data sources are achieved through hash index, information is completed, and a related dataset is formed.

[0062] The data association rules include: The price (PRICE_LIST) and quality data (MASS_DATA_LIST) are linked by "customer part number" and merged into intermediate result 1 (which includes material information and price information, etc.). Link the declaration element data (YS_DATA_LIST) with the quality data through the "material number", supplement information such as HS code and English product name, and obtain the master data; Associate the packing list component information (COMP) with the packing list processing unit information (HANDLING) through "handling_unit" (associate with the packing list processing unit information and supplement its internal material details); Associate the processing unit data (Y00_HUMO) with the packing list processing unit information (HANDLING) through the packing list number (associate to the processing unit, supplement gross weight, etc.); Link the country code lookup table (built-in Excel) with the master data by country name or country code, and convert the country name to SAP standard code.

[0063] If a data entry is missing a necessary field (such as an empty English product name) during association, the system will report an error and prompt the specific material number to avoid generating incomplete documents.

[0064] The data association rules are multi-level associations, including: Level 1: Perform equal-value matching on the primary associated key (customer material number, material number, packing list number); Level 2: If the primary association key fails to match, a multi-field combination of backup keys will be automatically used for matching (such as customer code + material number + date). Level 3: If the combination of multiple fields fails to match the backup key, the key field is truncated and a regular expression fuzzy match is performed, allowing partial character matching; If all three levels fail, the data is marked as unrelated and an exception list is generated to prevent missing documents.

[0065] Conflict ruling: When a master record matches multiple records from a secondary table: First priority: Newest timestamp takes precedence, so the most recently updated record is used; Second priority: Data source weight priority (declaration elements > price > packing list); If a conflict still exists, take the first result and record the conflict in the conflict log.

[0066] The data association uses a hash mapping acceleration algorithm. First, a hash dictionary is built for the data to be associated according to the key fields. Then, the main data is traversed to achieve fast search and merging. The time complexity of the algorithm is O(n), which is several times more efficient than the traditional traversal matching.

[0067] The data association algorithm includes the following steps: S31, determine the main table and the subordinate table, and specify the key fields for association; S32, iterate through all data in the table, using the key field value as the key and the entire record as the value, and build a hash dictionary; S33: Traverse each record in the main table and extract the key field values ​​of the current record; S34: Quickly search for the corresponding key in the hash dictionary to locate the matching sub-table data; S35, if the match is successful, the field from the secondary table will be merged into the current record of the primary table to complete the information completion; S36. After all records have been traversed, output the complete associated dataset.

[0068] Step 4: Automatic model number extraction and replacement.

[0069] See Figure 6 It iterates through the associated dataset, uses regular expressions to extract product model information from specified fields, compares it with the standard model, and automatically replaces it with the standard model if they do not match, and records the change information.

[0070] The regular expression consists of multiple predefined patterns, which are attempted to match sequentially. Once a match is successful, the pattern is replaced, and the change information from "original model to new model" is recorded.

[0071] Specifically, for the "Element" field in the customs declaration details, the system has built-in regular expression rules to automatically match the content after "Model:" and compare it with the correct standard model (from the declaration element or other data source). If they do not match, the original model in the "Element" is replaced with the standard model, and the change information (original model → new model) is recorded for subsequent verification.

[0072] Step 5: Categorize and summarize.

[0073] See Figure 6 Based on the classification and summarization rules in the configuration file, the dataset is grouped according to the specified grouping field, and aggregation calculations are performed on the fields in each group, including one or more of summation, taking the first record, or concatenation and deduplication.

[0074] The aggregation calculation supports setting a maximum number of splices and automatically wraps the text when the maximum number is reached.

[0075] Specifically, the system groups data according to the configured `collect_field` and performs aggregation calculations on the data within each group. For example, for processing unit data, it groups by material / HS code and summarizes the total net weight and total number of pieces; for master data, it groups by HS code and summarizes the total amount and total quantity. Aggregation rules are defined in `collect_config` and support: SUM: Summation, with optional rounding to the nearest whole number.

[0076] FIRST: Retrieves the value of a specific field from the first record in the group.

[0077] JOIN: Combines all duplicate values ​​of a field within a group, and allows setting a maximum number of values ​​to be combined with automatic line breaks.

[0078] The classification and summarization rules employ a classification and summarization algorithm: traverse the data list, store records into a dictionary by grouping key (key is the grouping key, value is the list of records), and then perform an aggregation operation on each list.

[0079] The categorical aggregation algorithm performs grouping and aggregation according to the configuration file BG_LIST_COLLECT_RULE, and the steps are as follows: S51 reads configuration parameters such as grouping field, aggregation method, precision, and concatenation limit; S52, initialize an empty group dictionary, where the key is the group value and the value is a list of records in the same group; S53, iterate through the detailed data line by line, calculate the grouping key of the current record, and insert the record into the list of the corresponding group in the dictionary; S54, perform aggregation calculations for each group list: SUM: Summates numeric fields and rounds them to the specified precision; JOIN: Concatenates duplicate field values, supports a maximum number of values, and automatically wraps lines if the limit is exceeded; FIRST: Retrieves the specified field from the first record in the list; S55, grouping and aggregation completed, output the summarized dataset.

[0080] Step 6: Generate header data.

[0081] See Figure 7 According to the header generation rules in the configuration file, global information is extracted from the summarized data to generate report header data.

[0082] The header generation rule is as follows: Based on HEAD_COLLECT_RULE, global information (such as invoice number, total amount, total number of items, and current date) is extracted from the summarized detailed data to generate the header data. Simultaneously, company information (such as exporter name and address) is obtained from the built-in configuration based on the factory code and also filled into the header.

[0083] Specifically, the system uses pre-configured Excel templates (differentiated by factory, such as "SH01.xlsx" and "OTHER.xlsx") to write header data and detailed data into different worksheets within the same Excel file. Each worksheet's data can be independently configured with sorting rules (ascending / descending order by field, supporting Chinese Pinyin sorting). The final generated file contains: Customs broker data source worksheet: Contains all customs declaration details for customs brokers to enter into the system.

[0084] Packing List (PL) worksheet: contains the details and header of the processed units.

[0085] Invoice (INV) worksheet: contains customs declaration details and header.

[0086] Customs declaration form: Similar to the data source of the customs broker, but the format is more in line with human filling habits.

[0087] Step 7: Report generation, result saving and feedback.

[0088] See Figure 8 The report header data and the summarized detailed data are written into multiple worksheets of the same Excel file according to a predefined Excel template to generate standardized logistics documents.

[0089] The multiple worksheets include a customs brokerage data source worksheet, a packing list worksheet, an invoice worksheet, and a customs declaration manual form worksheet, and each worksheet can be configured with its own sorting rules.

[0090] The generated file is saved in the system's temporary directory. Users can click the "Save" button on the interface to save the file to a specified location (such as the desktop). During processing, the interface displays the current step, progress percentage, and detailed logs in real time, and a prompt appears when processing is complete.

[0091] All business rules are configured via JSON files. When business rules change, only the configuration file needs to be modified; no code changes are required.

[0092] This invention provides an automated logistics document processing system and method, significantly improving processing efficiency by reducing manual operations that previously took hours to minutes. It generates a complete set of documents in one go, eliminating repetitive work. It boasts extremely high accuracy; automated data association and calculation eliminate errors from manual copying and pasting, and the automatic model replacement function prevents errors in model entry, ensuring accurate and consistent document data. Intelligent model processing, based on regular expressions for model extraction and replacement, solves the problem of difficult manual model verification, and records change logs for easy traceability. It is highly flexible and configurable; field mapping, cleaning rules, summary logic, and template selection can be adjusted via a JSON configuration file, quickly adapting to different customer or business changes without modifying the source code. It features multi-source data fusion capabilities, automatically associating multiple Excel reports exported from SAP to complete information and break down data silos. It offers a user-friendly experience with a graphical interface supporting drag-and-drop, progress feedback, and error prompts, making operation simple and intuitive. A built-in automatic update function ensures users always have the latest version. It reduces maintenance costs; rules and code are separated, allowing non-technical personnel to adapt to business adjustments by modifying configuration files, reducing reliance on developers.

[0093] Of course, the present invention may have other various embodiments. Without departing from the spirit and essence of the present invention, those skilled in the art can make various corresponding changes and modifications according to the present invention, but these corresponding changes and modifications should all fall within the protection scope of the appended claims.

Claims

1. An automated logistics document system, characterized in that, include: User interaction module: Used to receive multiple Excel files selected by the user, and allow the user to specify the type for each file or have the system automatically match the type; Data processing engine module: Using the Python programming language and the Pandas data processing library, it performs core data cleaning, association, calculation, and summarization according to the configuration file to generate intermediate data; Configuration management module: Uses JSON format configuration files to define the reading methods of each data source, field mapping relationships, data initialization rules, classification and summarization rules, data association rules, sorting rules, report template paths, etc., to achieve separation of business rules and code; Report generation module: Based on the configuration file and template, the processed data is written into multiple Excel worksheets to generate a standardized logistics document containing multiple worksheets, i.e., the final document; Automatic update module: Asynchronously checks the server version upon startup, downloads the new installation package, and automatically upgrades; Progress feedback module: During data processing, it sends the current step, progress percentage and log information to the user interaction module in real time through signals, and pops up a prompt when the processing is completed.

2. A method for automating the processing of logistics documents, characterized in that, Includes the following steps: Step 1, File Recognition and Loading: Read multiple Excel files selected by the user, each file corresponding to a data source, and determine the reading method, field mapping relationship and data initialization rules for each data source according to a predefined configuration file; Step 2, Data Initialization and Cleaning: According to the data initialization rules in the configuration file, the data of each data source is cleaned. Step 3, Multi-source data association: According to the data association rules in the configuration file, data from different data sources are associated with one or more key fields. Data matching and fusion between different data sources are achieved through hash indexing to complete information and form an associated dataset. Step 4, Automatic Model Number Extraction and Replacement: Traverse the associated dataset, use regular expressions to extract product model information from specified fields, compare it with the standard model, and automatically replace it with the standard model if they do not match, and record the change information. Step 5, Categorize and summarize: Based on the subtotaling rules in the configuration file, the dataset is grouped according to the specified grouping field, and aggregation calculations are performed on the fields within each group; Step 6, Generate header data: Based on the header generation rules in the configuration file, global information is extracted from the aggregated data to generate report header data; Step 7: Report generation, result saving and feedback: The report header data and the summarized detailed data are written into multiple worksheets of the same Excel file according to a predefined Excel template to generate standardized logistics documents.

3. The automated processing method for logistics documents according to claim 2, characterized in that, In step 3, the data association rule is a multi-level association, including: Level 1: Perform equality matching on the primary association key; Level 2: If the primary association key fails to match, a multi-field combination backup key will be automatically used for matching. Level 3: If the combination of multiple fields fails to match the backup key, the key field is truncated and a regular expression fuzzy match is performed, allowing partial character matching; If all three levels fail, the data is marked as unrelated and an exception list is generated to prevent missing documents.

4. The automated processing method for logistics documents according to claim 3, characterized in that, Conflict resolution of the data association rules: When a master record matches multiple records from a secondary table: First priority: Newest timestamp takes precedence, so the most recently updated record is used; Second priority: Data source weight takes precedence; If a conflict still exists, take the first result and record the conflict in the conflict log.

5. The automated processing method for logistics documents according to claim 3, characterized in that, The data association uses a hash mapping acceleration algorithm, including the following steps: S31, determine the main table and the subordinate table, and specify the key fields for association; S32, iterate through all data in the table, using the key field value as the key and the entire record as the value, and build a hash dictionary; S33: Traverse each record in the main table and extract the key field values ​​of the current record; S34: Quickly search for the corresponding key in the hash dictionary to locate the matching sub-table data; S35, if the match is successful, the field from the secondary table will be merged into the current record of the primary table to complete the information completion; S36. After all records have been traversed, output the complete associated dataset.

6. The automated processing method for logistics documents according to claim 2, characterized in that, In step 5, the classification and summarization rules adopt a classification and summarization algorithm: traverse the data list, store the records into a dictionary according to the grouping key, and then perform an aggregation operation on each list; The categorical aggregation algorithm performs grouping and aggregation according to the configuration file BG_LIST_COLLECT_RULE, and the steps are as follows: S51 reads configuration parameters such as grouping field, aggregation method, precision, and concatenation limit; S52, initialize an empty group dictionary, where the key is the group value and the value is a list of records in the same group; S53, iterate through the detailed data line by line, calculate the grouping key of the current record, and insert the record into the list of the corresponding group in the dictionary; S54, perform aggregation calculations for each group list: SUM: Summates numeric fields and rounds them to the specified precision; JOIN: Concatenates duplicate field values, supports a maximum number of values, and automatically wraps lines if the limit is exceeded; FIRST: Retrieves the specified field from the first record in the list; S55, grouping and aggregation completed, output the summarized dataset.

7. The automated processing method for logistics documents according to claim 2, characterized in that, In step 2, the initialization rule types include: FILTER filter: Filters out rows that do not meet the criteria; ENUM enumeration conversion: Maps code values ​​to text descriptions; VALUE Fixed Fill: Fills the field with a fixed value; RE Regular Expression Cleaning: Uses regular expressions to remove specific characters from a field; EXIST Existence Flag: Sets a flag to 1 or 0 depending on whether another field exists; APPEND field concatenation: Concatenates the values ​​of multiple fields into a new field using spaces.

8. The automated processing method for logistics documents according to claim 7, characterized in that, FILTER filtering filters records based on logical expressions, including the following steps: S211, Parse the filter expression with logical operators in the JSON configuration; S212 parses the expression into an abstract syntax tree (AST) to perform syntax verification, priority sorting, and redundant condition removal. S213, dynamically compiles the AST into a memory-executable function; S214 employs an incremental row-by-row judgment mechanism, filtering as it reads, without loading all data into memory at once. S215: Records that do not meet the conditions are discarded and recorded in the filter log, while records that meet the conditions proceed to the next stage.

9. The automated processing method for logistics documents according to claim 7, characterized in that, The RE regular expression cleaning, which performs character replacement or format standardization based on regular expressions, includes the following steps; S241: Load a multi-level regular expression rule chain from the configuration and execute it sequentially according to priority; S242 performs rule-by-rule matching and replacement on the target field: first remove special characters, then remove extra spaces, and finally unify the format; S243 supports retaining the whitelist of valid characters while removing only illegal characters; S244: After cleaning, the format is automatically checked. If it does not conform, it is marked and the default format is used. S245, record a comparison log before and after cleaning.

10. The automated processing method for logistics documents according to claim 2, characterized in that, Step 1, file recognition and loading, includes the following steps: S11 allows users to drag / select multiple Excel files via the interface; S12, the system matches the type based on filename keywords; S13, reads the specified worksheet according to the JSON configuration, and maps the original columns to standard fields according to LOAD_FIELDS; S14: Remove all blank lines, convert to list / dictionary format, and complete data loading.

Images