Bill data processing method and device, electronic equipment and storage medium

Abstract

The invention discloses a bill data processing method and device, electronic equipment and a computer readable storage medium. The method comprises the following steps: creating a bill checking template; wherein the account statement template comprises a header field and a parameterized SQL (Structured Query Language) query statement template; synchronizing the source table data in the business database to the analytical database; when a reconciliation statement generation request is received, obtaining a corresponding target bill template according to the merchant identifier and the reconciliation statement type in the reconciliation statement generation request; generating an SQL query statement according to an SQL query statement template in the target bill template and the current operation parameters, and querying bill data in an analytical database based on the SQL query statement; and generating a reconciliation statement file according to the queried statement data, the sequence of the header fields and the display names. According to the method, the bill generation efficiency and adaptability can be greatly improved on the premise of ensuring the bill generation accuracy, and the personalized bill generation requirements under multiple merchants and multiple scenes are met.

Description

Technical Field

[0001] This application relates to the field of computer technology, and more specifically, to a billing data processing method, apparatus, electronic device, and computer-readable storage medium. Background Technology

[0002] In existing bill generation systems, bill generation typically relies on pre-coded fixed templates and query logic. While this approach meets standardized bill output requirements, it exhibits significant limitations when dealing with the personalized bill formats and field requirements of different merchants. Current technologies often employ hard-coding, requiring code modifications and system redeployment for each new bill type or structural adjustment, resulting in poor system flexibility, slow response times, and high maintenance costs. Furthermore, traditional bill generation often involves complex join queries directly to the business database. As transaction data volumes surge, query performance degrades sharply, failing to meet the demands for efficient, low-latency bill generation.

[0003] Therefore, how to achieve dynamic configurability of the billing structure and how to improve the billing generation performance in a massive data environment have become technical problems that need to be solved by those skilled in the art. Summary of the Invention

[0004] The purpose of this application is to provide a billing data processing method, apparatus, electronic device, and computer-readable storage medium, which can significantly improve the efficiency and adaptability of billing generation while ensuring the accuracy of billing generation, and meet the personalized billing generation needs of multiple merchants and multiple scenarios.

[0005] To achieve the above objectives, this application provides a billing data processing method, comprising:

[0006] Create a statement template; wherein the statement template includes header fields and a parameterized SQL query statement template;

[0007] Synchronize source table data from the business database to the analytical database;

[0008] When a statement generation request is received, the corresponding target statement template is obtained based on the merchant identifier and statement type in the statement generation request.

[0009] Based on the SQL query statement template in the target bill template and the current running parameters, an SQL query statement is generated, and bill data is queried in the analytical database based on the SQL query statement.

[0010] Based on the retrieved billing data, a statement file is generated according to the order of the header fields and the display name.

[0011] The creation of the statement template includes:

[0012] The system receives template configuration requests through a visual configuration interface and creates statement templates based on these requests. The template configuration requests include merchant identifier, statement type, list of header fields, data source fields and display names corresponding to each header field, and a parameterized SQL query statement template containing placeholders.

[0013] In response to the template release command, the newly created statement template is marked as active, and other statement templates corresponding to the merchant identifier and the statement type are marked as historical.

[0014] Accordingly, obtaining the corresponding target bill template based on the merchant identifier and bill type in the bill generation request includes:

[0015] Obtain the corresponding active target bill template based on the merchant identifier and bill type in the bill generation request.

[0016] The statement template also includes a data processing mode, which includes a real-time join query mode or a wide table pre-construction mode.

[0017] Accordingly, synchronizing source table data from the business database to the analytical database includes:

[0018] If the data processing mode is the real-time join query mode, then the business data in the business database will be synchronized to the analytical database in the form of multiple source tables;

[0019] If the data processing mode is the wide table pre-construction mode, then a predefined wide table construction task is executed periodically to synchronize the associated business data to the analytical database in the form of a single wide table; wherein, the wide table construction task is to associate and integrate multiple source tables in the business database into a wide table.

[0020] The step of retrieving billing data from the analytical database based on the SQL query statement includes:

[0021] If the data processing mode is the real-time join query mode, then the multiple source tables in the analytical database are joined based on the SQL query statement;

[0022] If the data processing mode is the wide table pre-construction mode, then a single-table query is performed on the wide table in the analytical database based on the SQL query statement.

[0023] The SQL query template includes at least one placeholder.

[0024] Accordingly, generating the SQL query statement based on the SQL query statement template in the target bill template and the current running parameters includes:

[0025] Identify at least one placeholder in the SQL query statement template; wherein the placeholder includes any one or a combination of any of the following: merchant identifier, reconciliation date, accounting cycle, and business type identifier;

[0026] The parameter values ​​corresponding to each placeholder in the current running parameters are replaced in the corresponding positions of the SQL query statement template to generate an executable SQL query statement.

[0027] The step of generating an SQL query statement based on the SQL query statement template in the target bill template and the current running parameters further includes:

[0028] Based on the SQL query statement, query the billing data in the business database;

[0029] Compare the billing data retrieved from the analytical database with the billing data retrieved from the business database;

[0030] If the comparison is consistent, then the step of generating a statement file based on the retrieved statement data according to the order of the header fields and the display name is executed.

[0031] The process of generating a statement file based on the retrieved bill data according to the order of the header fields and the display name further includes:

[0032] The statement of account files are formatted and packaged to generate a delivery file package;

[0033] The delivery file package is automatically pushed to the target server storage path corresponding to the merchant identifier through a preset secure transmission channel.

[0034] To achieve the above objectives, this application provides a billing data processing apparatus, comprising:

[0035] A creation module is used to create a statement template; wherein, the statement template includes header fields and parameterized SQL query statement templates;

[0036] The synchronization module is used to synchronize source table data from the business database to the analytical database;

[0037] The acquisition module is used to obtain the corresponding target bill template based on the merchant identifier and bill type in the bill generation request when a bill generation request is received.

[0038] The query module is used to generate an SQL query statement based on the SQL query statement template in the target bill template and the current running parameters, and to query bill data in the analytical database based on the SQL query statement;

[0039] The generation module is used to generate a statement file based on the retrieved statement data, according to the order of the header fields and the display name.

[0040] To achieve the above objectives, this application provides an electronic device, comprising:

[0041] Memory, used to store computer programs;

[0042] A processor is used to implement the steps of the billing data processing method described above when executing the computer program.

[0043] To achieve the above objectives, this application provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the billing data processing method described above.

[0044] The billing data processing method provided in this application introduces a configurable reconciliation statement template mechanism, which encapsulates the header fields of the bill and the SQL (Structured Query Language) query logic in a templated manner, achieving dynamic separation between the billing structure and the query logic. When different formats or contents of reconciliation statements need to be generated, only the corresponding template needs to be configured, without modifying the program code, thereby significantly improving the system's flexibility and scalability, and reducing maintenance costs and response time. Simultaneously, by synchronizing business data to an analytical database and executing queries in that database based on the SQL statements generated from the template, the high-performance query capabilities of the analytical database are effectively utilized, avoiding performance bottlenecks caused by performing complex join operations directly in the business database. Therefore, this application can significantly improve the efficiency and adaptability of billing generation while ensuring the accuracy of bill generation, meeting the personalized billing generation needs of multiple merchants and multiple scenarios. This application also discloses a billing data processing device, an electronic device, and a computer-readable storage medium, which can achieve the same technical effects.

[0045] It should be understood that the above general description and the following detailed description are merely exemplary and do not limit this application. Attached Figure Description

[0046] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. The drawings are used to provide a further understanding of this disclosure and constitute a part of the specification. They are used together with the following detailed description to explain this disclosure, but do not constitute a limitation of this disclosure. In the drawings:

[0047] Figure 1 This is a flowchart illustrating a billing data processing method according to an exemplary embodiment;

[0048] Figure 2 A flowchart illustrating another billing data processing method according to an exemplary embodiment;

[0049] Figure 3 A flowchart of template configuration management is provided in one application embodiment of this application;

[0050] Figure 4 A flowchart of statement generation and delivery is provided in one application embodiment of this application;

[0051] Figure 5 A schematic diagram of the underlying data processing and storage architecture supporting the reconciliation system in one application embodiment provided in this application;

[0052] Figure 6 This is a structural diagram of a billing data processing apparatus according to an exemplary embodiment;

[0053] Figure 7 This is a structural diagram of an electronic device according to an exemplary embodiment. Detailed Implementation

[0054] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the protection scope of this application.

[0055] It should be noted that, in the description of this application, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. The terms "first," "second," etc., in this application are used to distinguish similar objects and are not used to describe a specific order or sequence.

[0056] To enable those skilled in the art to better understand the present application, the present application will be further described in detail below with reference to the accompanying drawings and specific embodiments.

[0057] This application discloses a billing data processing method that significantly improves the efficiency and adaptability of billing generation while ensuring the accuracy of bill generation, and meets the personalized billing generation needs of multiple merchants and multiple scenarios.

[0058] See Figure 1 A flowchart illustrating a billing data processing method according to an exemplary embodiment, such as... Figure 1 As shown, it includes:

[0059] S101: Create a statement template; wherein the statement template includes header fields and a parameterized SQL query statement template;

[0060] The statement template is a predefined structured configuration object used to describe the output format and data source rules for a specific type of statement. It mainly consists of two core components: first, header fields, which define the display names (e.g., "Merchant Order Number", "Bank Order Number", "Handling Fee"), order, and corresponding data mapping keys for each column in the final generated statement file, supporting both standard and fully custom formats; second, a parameterized SQL query template, which is an SQL script containing dynamic placeholders used to guide the system in retrieving statement data from the data source that meets specific conditions.

[0061] In this step, users can create statement templates through the system's graphical user interface (GUI) or application programming interface (API), allowing users to configure them flexibly according to their actual business needs.

[0062] For example, create the `statement_template` (statement template table), `template_header` (header definition table), and `template_query_config` (query configuration table). The core fields of the `statement_template` table are: `template_id` (template ID), `template_name`, `merchant_id` (associated merchant), `statement_type` (acquiring / paying on behalf / summarizing, etc.), `version` (template version), and `status` (enabled / disabled). The core fields of the `template_header` table are: `header_id`, `template_id`, `field_name` (front-end display name), `field_key` (database field or mapping key), and `sort_order` (sorting). The core fields of the `template_query_config` table are: `config_id`, `template_id`, `data_source` (pointing to the StarRocks data source), and `sql_template` (a core field storing parameterized SQL). The `sql_template` field stores the parameterized SQL statements. For example, a template SQL query for a buyer's detailed statement is as follows: select * from llyt.ads_cp_cashin_statement_di where pt >= concat('{dateAcct}', '01') and pt < DATE_ADD(concat('{dateAcct}', '01'), INTERVAL 1MONTH) and traderno = '{customerNo}' order by inner_order_no, where {dateAcct} and {customerNo} are placeholders that will be replaced at runtime.

[0063] As a feasible implementation method, the creation of the statement template includes: receiving a template configuration request through a visual configuration interface, and creating a statement template according to the template configuration request; wherein, the template configuration request includes a merchant identifier, statement type, a list of header fields, data source fields and display names corresponding to each header field, and a parameterized SQL query statement template containing placeholders; in response to the template release instruction, marking the newly created statement template as active, and marking other statement templates corresponding to the merchant identifier and the statement type as historical.

[0064] In practice, a RESTful API management backend is provided, allowing users to add and query templates through a UI interface. When a user saves a template, the system does not directly modify the original template, but instead generates a new version record (version + 1). The old version template is set to HISTORY, and the new version is set to ACTIVE. This mechanism ensures the immutability of templates and prevents fluctuations in the production environment.

[0065] As can be seen, this step decouples the billing generation logic from the program code by templating and configuring the billing format and data query logic. When it is necessary to support new billing formats or adjust the existing billing structure, it is only necessary to modify or create a template through the configuration interface, without modifying, testing, or redeploying the program code. This significantly improves the system's flexibility and scalability, reduces operation and maintenance costs and dependence on development resources, and achieves agile response capabilities of "configuration as development".

[0066] S102: Synchronize source table data from the business database to the analytical database;

[0067] Among them, the business database refers to the core database that supports online transaction processing such as daily transactions and order processing, such as MySQL (Structured Query Language) and Oracle, whose design focuses on high concurrency and fast response of short transactions. Source table data specifically refers to the raw data tables in the business database related to bill generation, such as the order table (order_base) and the transaction flow table (transaction_flow). Analytical databases refer to databases oriented towards complex analytical queries and massive data processing, such as StarRocks (an open-source MPP, or Massively Parallel Processing) and Apache Doris (a real-time analytics database), whose design focuses on high-performance ad-hoc queries and data analysis.

[0068] In this step, data is stably and accurately copied or migrated from the business database to the analytical database. This can be achieved using data synchronization tools or by writing scheduled batch processing tasks to periodically perform full or incremental data export and import. For example, a synchronization configuration file, `sync_to_starrocks.conf`, can be written to synchronize source tables such as `order_base`, `order_extend`, and `account_flow` from the business database to their corresponding tables in the StarRocks ODS layer using CDC mode or scheduled incremental mode. In StarRocks, it is recommended to use a Duplicate unique key model or an Aggregate aggregation model for the synchronized ODS (Operational Data Store) tables, and set appropriate bucketing keys (such as `merchant_id`, `order_date`) according to the query pattern.

[0069] As can be seen, this step decouples the analytical query load from the business database, avoiding the impact of complex report queries on the performance of the core transaction system. Utilizing analytical databases' storage engines (such as columnar storage) and computing architectures (such as distributed parallel computing) optimized for massive data queries lays a solid foundation for the efficient execution of complex queries such as multi-table joins and aggregations, thereby significantly improving the overall performance of the reconciliation data retrieval process.

[0070] As a feasible implementation method, the statement template also includes a data processing mode, which includes a real-time join query mode or a wide table pre-construction mode. Correspondingly, synchronizing source table data from the business database to the analytical database includes: if the data processing mode is the real-time join query mode, then synchronizing the business data from the business database to the analytical database in the form of multiple source tables; if the data processing mode is the wide table pre-construction mode, then periodically executing a predefined wide table construction task to synchronize the associated business data to the analytical database in the form of a single wide table; wherein, the wide table construction task involves associating and integrating multiple source tables in the business database into a wide table.

[0071] In practical implementation, a configuration element, namely the data processing mode, has been added to the definition of the statement template. The data processing mode is an enumerated configuration item used to specify the data query and organization strategy to be adopted when generating this type of statement. Specifically, it includes two optional modes: real-time join query mode and wide table pre-built mode. These two modes correspond to different data synchronization, processing, and query strategies, aiming to balance data real-time performance and query performance to adapt to different business scenarios and data volumes. The real-time join query mode emphasizes low latency and data integrity, while the wide table pre-built mode focuses on extreme query performance and efficient processing of massive historical data.

[0072] If the target bill template's data processing mode is configured for real-time join query mode, the system will use data synchronization tools to synchronize the structure and data of multiple source tables related to the bill in the business database to the analytical database in near real-time. In the analytical database, these tables typically exist independently in a form similar to or optimized from the source table structure, forming detailed layer data. If the target bill template's data processing mode is configured for wide table pre-construction mode, the system will adopt a "space-for-time" pre-computation strategy. In this mode, the system will set up a periodic (e.g., every 2 hours, daily) ETL (Extract, Transform, Load) task, i.e., a wide table construction task. This task will execute a predefined, complex SQL script whose core logic is to perform pre-join and aggregation calculations on multiple related source tables in the business database, integrate the results into a wide table containing all the required fields, and then synchronize it to the analytical database. This wide table integrates information scattered across multiple tables, so that a single record contains all the dimensions needed to generate a reconciliation statement.

[0073] This demonstrates that by introducing configurable data processing modes into the statement template, the system can intelligently select the optimal data processing path. This provides a flexible solution for addressing diverse business needs (such as varying real-time requirements, data volume, and query complexity), achieving a dynamic trade-off and optimization between real-time data processing and query performance, thereby improving the overall processing efficiency and scenario adaptability of the statement generation system.

[0074] S103: When a statement generation request is received, obtain the corresponding target statement template according to the merchant identifier and statement type in the statement generation request;

[0075] The statement generation request is a system call event or message, typically generated by a scheduled task, a user-triggered interface, or an external system call. Its request payload must contain at least two key parameters: Merchant ID (used to uniquely identify a specific merchant) and Statement Type (such as "Daily Transaction Details" or "Monthly Summary"). The target statement template refers to the pre-configured statement template that matches the current request parameters (Merchant ID and Statement Type) and is in an active state.

[0076] In this step, after receiving the statement generation request, the merchant identifier and statement type in the request are parsed out. These are used as query conditions to access the database or configuration center that stores template information. The template record returned by the query is the target statement template that needs to be used for this generation task.

[0077] As a possible implementation method, obtaining the corresponding target bill template based on the merchant identifier and bill type in the bill generation request includes: obtaining the corresponding target bill template that is in an active state based on the merchant identifier and bill type in the bill generation request.

[0078] In practice, when generating a statement, the target statement template that is currently in the ACTIVE state is obtained based on the merchant identifier and statement type.

[0079] As can be seen, this step achieves precise routing of bill generation tasks and automated template matching. The system does not need to hardcode processing logic for each merchant or each type of bill. Instead, it dynamically locates the predefined processing rules (templates) through request parameters. This greatly enhances the system's adaptability and manageability, making it possible to support personalized bill generation for a large number of heterogeneous merchants, while simplifying task scheduling and triggering logic.

[0080] S104: Generate an SQL query statement based on the SQL query statement template in the target bill template and the current running parameters, and query bill data in the analytical database based on the SQL query statement;

[0081] The current running parameters refer to the context information when the bill generation request is processed. They are usually automatically filled by the system, such as the merchant identifier currently being processed (from the request), the reconciliation date (usually the system date or the date specified in the request), the billing cycle, and the business type identifier.

[0082] In this step, a real-time SQL query statement is generated based on the SQL query statement template in the target bill template and the current running parameters. Subsequently, the system sends the SQL query statement to the analytical database for query execution through the database connection driver and obtains the returned bill dataset.

[0083] As a feasible implementation, the SQL query statement template includes at least one placeholder; correspondingly, generating the SQL query statement based on the SQL query statement template in the target bill template and the current running parameters includes: identifying the at least one placeholder in the SQL query statement template; wherein, the placeholder includes any one or a combination of any of the following: merchant identifier, reconciliation date, accounting cycle, and business type identifier; and replacing the parameter values ​​corresponding to each placeholder in the current running parameters with the corresponding positions in the SQL query statement template to generate an executable SQL query statement.

[0084] In practice, the SQL query statement template is read, and all placeholders in the template, such as merchant identifier, reconciliation date, accounting cycle, and business type identifier, are replaced with the actual values ​​of the current running parameters, thereby forming a complete and specific SQL statement that can be directly executed in the database.

[0085] As can be seen, this implementation method achieves dynamic and personalized query logic. By combining fixed query templates with dynamic operating parameters, it can flexibly adapt to the data retrieval needs of different merchants, different dates, and different business scenarios.

[0086] As a feasible implementation, querying bill data in the analytical database based on the SQL query statement includes: if the data processing mode is the real-time join query mode, then performing a join query on multiple source tables in the analytical database based on the SQL query statement; if the data processing mode is the wide table pre-construction mode, then performing a single table query on the wide table in the analytical database based on the SQL query statement.

[0087] In practical implementation, if the data processing mode is a real-time join query mode, the generated SQL query statement is used to directly execute join queries on multiple independent source tables in the analytical database. The analytical database's query optimizer receives this complex multi-table join query request and, utilizing its distributed computing framework and vectorized execution engine, scans relevant shards in parallel across multiple data nodes, performing operations such as join condition matching, filtering, and aggregation, ultimately returning the joined result set. In this mode, query performance primarily depends on the analytical database's ability to handle complex ad-hoc join queries. Its advantages lie in its flexible query logic, ability to reflect the latest join status between multiple tables in real time, and high data timeliness.

[0088] If the data processing mode is wide table pre-built mode, a simple single-table query is executed on a pre-built wide table in the analytical database based on the generated SQL query statement. In this mode, the FROM clause of the SQL query statement will refer to a specific wide table, and no longer includes complex JOIN operations. All necessary join and transformation logic has been pre-calculated and materialized into this wide table during the periodic wide table construction task. When the system executes the query, the analytical database only needs to scan and filter the single wide table, significantly reducing computational complexity. In this mode, query performance is greatly improved because runtime join overhead is avoided, resulting in stable and fast query response time, making it particularly suitable for reporting scenarios with huge data volumes and fixed query patterns. Its advantages include extremely fast query speed, providing an excellent user experience and system throughput.

[0089] S105: Generate a statement file based on the retrieved statement data, following the order of the header fields and the display name.

[0090] In this step, the header configuration of the target bill template is first read, and the display name of each field is written to the first line of the file as the header line, following the configuration order. Then, each record in the query result dataset is traversed, and the corresponding values ​​are extracted from the records according to the data mapping keys defined in the header fields (corresponding to the column names in the database query result set). These values ​​are then written to subsequent lines of the file in the same order as the header, generating the statement file.

[0091] As a preferred embodiment, after generating the statement file according to the order of the header fields and the display name based on the queried bill data, the method further includes: formatting and packaging the statement file to generate a delivery file package; and automatically pushing the delivery file package to the target server storage path corresponding to the merchant identifier through a preset secure transmission channel.

[0092] In practice, the generated statement files (such as CSV format) are compressed and packaged using a compression algorithm (such as ZIP) to create a smaller, easier-to-transmit delivery package. Files can also be renamed according to rules. This improves file management efficiency and reduces network transmission load. Subsequently, based on the merchant's identifier, the system locates the preset transmission configuration, such as the SFTP (Secure File Transfer Protocol) server address, path, and authentication information. Through an encrypted, secure file transfer channel, such as SFTP or FTPS (File Transfer Protocol over SSL / TLS), the delivery package is automatically uploaded to the merchant's designated remote server directory.

[0093] As can be seen, this implementation method achieves full automation and security of the delivery process, ensuring that billing documents are delivered to the customer's system in a timely, accurate and secure manner, eliminating manual operation and improving reliability.

[0094] The billing data processing method provided in this application introduces a configurable reconciliation statement template mechanism, which encapsulates the header fields of the bill and the SQL query logic in a templated manner, achieving dynamic separation between the billing structure and the query logic. When different formats or contents of reconciliation statements need to be generated, only the corresponding template needs to be configured, without modifying the program code, thereby significantly improving the system's flexibility and scalability, and reducing maintenance costs and response time. Simultaneously, by synchronizing business data to an analytical database and executing queries in that database based on the SQL statements generated from the template, the high-performance query capabilities of the analytical database are effectively utilized, avoiding performance bottlenecks caused by performing complex join operations directly in the business database. Therefore, this application embodiment can significantly improve the efficiency and adaptability of billing generation while ensuring the accuracy of billing generation, meeting the personalized billing generation needs of multiple merchants and multiple scenarios.

[0095] This application discloses a billing data processing method. Compared with the previous embodiment, this embodiment further explains and optimizes the technical solution. Specifically:

[0096] See Figure 2 A flowchart illustrating another billing data processing method according to an exemplary embodiment, such as... Figure 2 As shown, it includes:

[0097] S201: Create a statement template; wherein the statement template includes header fields and a parameterized SQL query statement template;

[0098] S202: Synchronize source table data from the business database to the analytical database;

[0099] S203: When a statement generation request is received, obtain the corresponding target statement template based on the merchant identifier and statement type in the statement generation request;

[0100] S204: Generate an SQL query statement based on the SQL query statement template in the target bill template and the current running parameters;

[0101] S205: Query billing data in the business database based on the SQL query statement, and query billing data in the analytical database based on the SQL query statement;

[0102] In this step, the same data queries are executed in parallel, but on different data sources. The first query is executed in the business database to retrieve raw, unprocessed or synchronized billing data. The second query is executed in the analytical database to retrieve synchronized, possibly processed, billing data. Both queries use the exact same SQL query statements generated in step S204, ensuring consistency in query logic and scope, laying the foundation for accurate comparison later.

[0103] S206: Compare the billing data retrieved from the analytical database with the billing data retrieved from the business database; if they match, proceed to S207;

[0104] In this step, the two bill datasets obtained from the two databases in step S204 are compared across multiple dimensions. The dimensions for comparison typically include, but are not limited to: total number of records, sum of key metrics (such as total transaction amount, total fees), and optional line-by-line comparison of detailed records (matching by primary key or unique key). The system will write comparison logic to calculate the numerical differences between the two datasets on the selected dimensions. If the differences in all preset comparison dimensions are within the allowable error range (e.g., the number of records is exactly the same, and the total amount difference is less than 0.01 yuan), the comparison is considered consistent. Once the comparison is consistent, the system considers the data synchronization and processing from the business database to the analysis database to be accurate and reliable, and the data in the analysis database to be sufficient to represent the true state of the source data. Therefore, the process can safely continue to step S206 to generate the final file based on the bill data (usually taken from the analysis database). If the comparison is inconsistent, the process is terminated, and an alarm mechanism is triggered to notify operations or development personnel to check for potential problems in the data synchronization link, ETL job, or the source data itself.

[0105] S207: Generate a statement file based on the retrieved statement data, following the order of the header fields and the display name.

[0106] This embodiment cross-validates the consistency of data between the business database (data source) and the analytical database (data application layer), enabling timely detection of potential data loss, duplication, distortion, or calculation errors during synchronization, ETL processing, or storage. Only verified data is used to generate official reconciliation statements, reducing the financial and compliance risks associated with generating incorrect reconciliation statements due to underlying data inconsistencies. Furthermore, this verification process is fully automated, requiring no manual intervention, thus ensuring both accuracy and processing efficiency.

[0107] The following describes an application embodiment of this application, where the template configuration management process is as follows: Figure 3As shown, template configuration begins with the user creating a new template. The core steps of template definition include defining header fields and configuring the SQL query template. The former determines the column structure of the statement, while the latter involves writing query statements with dynamic parameters. Next, the data source needs to be set up and parameter mapping configured to associate the query with the runtime environment. After initial configuration, the testing and verification phase begins. If verification passes, the template can be published and subsequently enters version management, eventually being marked as configured successfully. If verification fails, the user must return to the configuration adjustment steps, modify the changes, and re-verify until successful. This entire process demonstrates the standardized, testable, and traceable characteristics of statement template configuration.

[0108] The process of generating and delivering account statements is as follows: Figure 4 As shown, triggered by a scheduled task or external request, the process first retrieves the corresponding template configuration in parallel based on the statement type, such as detailed statement, summary statement, or monthly statement. Then, the process branches according to the preset data processing mode in the template: if it's Mode 1 (real-time table join mode), a real-time data query is performed; if it's Mode 2 (wide table mode), a pre-built wide table is queried directly. After data retrieval, the process proceeds to the data verification stage. If the verification fails, the process terminates. If the verification passes, subsequent steps are executed, including storing the result file on a file server, compressing and packaging it, and finally delivering the data by notifying the merchant system or generating a download link.

[0109] The underlying data processing and storage architecture supporting the reconciliation system is as follows: Figure 5As shown, the left side represents the data source, including the Business Online Database (typically the master database such as Oracle or MySQL) and the Business Slave Database (Oracle or MySQL slave database). Together, they are responsible for processing real-time transactions. Data is collected and buffered through Kafka (a distributed stream processing platform) to achieve asynchronous decoupling. Subsequently, data processing is divided into two paths: the real-time processing path utilizes Flink (a distributed streaming computing engine) to process the data in real time and writes it to a real-time database, which refers to a database with OLAP (Online Analytical Processing) capabilities such as Oracle or MySQL to support low-latency queries; the offline processing path persists the data to HDFS (Hadoop Distributed File System), performs batch ETL (Extract, Transform, Load) processing and wide table construction using Spark SQL (Apache Spark's SQL processing module), and stores the results in an offline database, which also refers to an OLAP (Online Analytical Processing) type Oracle or MySQL database, supporting efficient historical data queries. This architecture together constitutes a hybrid data platform that balances real-time performance and batch processing capabilities.

[0110] Taking the internal settlement system of an e-commerce platform as an example, it requires that the statement of account include specific technical dimension fields, such as instance type, availability zone, and service code, which do not exist in the standard statement of account.

[0111] Step 1: Template Configuration Phase (One-time configuration, permanent effect):

[0112] (1) Create an AWS (Amazon Web Services) exclusive template: Product operations personnel create a template named "AWS_Custom Daily Detail Bill" in the template management UI.

[0113] (2) Define dynamic headers: In the header configuration, in addition to the standard fields (merchant order number, amount, time), dynamically add AWS-specific fields: Instance type → Mapped to the data field instance_type; Availability zone → Mapped to the data field az_id; Service code → Mapped to the data field service_code.

[0114] (3) Write parameterized SQL templates: In the SQL configuration unit, write complex relational queries to extract the data required by AWS from multiple business tables. Python select customer_id as traderno, 'D' as c1, '${v_file_date}' as c2, 'Wells Fargo' as c3, '42350227' as c4, 'LianLianPay' as c5, '${v_file_date}' as c6, concat('LLAWSUS', '${v_file_date}', '01') as c7, 'USD' as c8, cast(cast(t.target_amount as double) / 1000 as decimal(32, 2)) as c9, '1' as c10, ',,' as c11, 2 as c12 from ods_cb_access_trade_order t where pt = '${v_file_date}' and t.customer_id = '${traderno}' and t.order_type = 'PAY' and t.target_currency = 'USD' and t.order_id_in3rd_sys = concat('EP', '${traderno}', 'USD', '${v_file_date}')

[0115] (4) Publish Template: After configuration, click the “Publish” button. The system performs a version snapshot in the background, saving the current configuration as a new version (e.g., v1.0) and marking the template status as ACTIVE.

[0116] Step 2: Data preparation and verification stage (automated execution):

[0117] (1) Data synchronization: The company's synchronization tool synchronizes the changes in tables such as order_base, order_extension, resource_info, and product_info in the business database (MySQL) to the ODS layer of StarRocks in real time.

[0118] (2) Wide table pre-construction: Due to the complexity and large amount of data in AWS reconciliation queries, the system selected the wide table mode for this template. A separate ETL task (such as running once every 2 hours) will execute the predefined wide table construction SQL to pre-associate the data of the above four tables to form the merchant_order_aws_wide wide table.

[0119] (3) Intelligent Data Verification: After the wide table construction task is successful, the system automatically triggers the data verification service. This service concurrently queries the source business database and the wide table in StarRocks to calculate the total number of AWS transaction records and the total amount for the day. The verification status is marked as "passed" only when the data on both sides is completely consistent (or the difference is within the allowable range).

[0120] Step 3: Statement Generation and Delivery Phase (End-of-Day Automation):

[0121] (1) Task triggering: Every day at 01:00, the XXL-JOB scheduling center triggers the "AWS reconciliation statement generation" task.

[0122] (2) Template loading and SQL rendering: The reconciliation core business service receives the task.

[0123] First, the dynamic template engine is invoked to load the published ACTIVE version template based on the merchant ID "AWS_CN" and the billing type. The engine replaces the placeholders #{merchantId} and #{accountDate} in the SQL template with the runtime values ​​'AWS_CN' and '2023-11-01', respectively. Because it's configured in wide table mode, the final executed SQL is optimized to: SELECT * FROM merchant_order_aws_wide WHERE merchant_id = 'AWS_CN' AND create_date = '2023-11-01'.

[0124] (3) Pre-verification check: Before executing the query, the business service will check the verification status of the merchant's data for the day. If the status is "passed", the process continues; if it is "failed", the task will be stopped immediately and an alarm will be sent.

[0125] (4) Execute query and generate file: Send the optimized query to StarRocks. Thanks to the wide table mode, complex join queries that originally took more than 10 minutes are completed in 10 seconds. The system generates the result set into a CSV (Comma-Separated Values) file AWS_CN_DETAIL_20231101.csv according to the AWS custom header.

[0126] (5) Packaging and Push: Compress the CSV file into a ZIP file AWS_CN_20231101.zip and push it automatically to the AWS-specified billing server directory via a secure SFTP channel.

[0127] (6) Status update: After the entire process is completed, the system records the generation log and updates the statement status to "generated".

[0128] This embodiment offers the following advantages: First, the system achieves exceptional flexibility and scalability. Through a dynamic template configuration mechanism, it enables configuration-as-development, allowing for rapid response to diverse merchant reconciliation statement formats and content requirements without modifying program code. This significantly shortens the product launch cycle for new requirements and effectively reduces development costs. Second, the system demonstrates superior big data processing performance. By leveraging StarRocks' online analytical processing capabilities and combining a dual-mode data processing pipeline with real-time table joins and wide table pre-built structures, it achieves a good balance between data real-time performance and query performance, enabling it to handle reconciliation statement generation and verification tasks involving hundreds of millions or even billions of transaction data points with ease. Third, the system ensures high business accuracy and reliability. Relying on multi-dimensional automatic verification between data sources and targets, a fully automated reconciliation process, and a rigorous error handling mechanism, it minimizes the risk of fund settlement errors, thereby building a solid foundation of trust between merchants and payment channels. Finally, the system has clear system boundaries and high availability. The microservice architecture allows each functional service to be developed, deployed and horizontally scaled independently, which not only improves the overall stability and maintainability of the system, but also meets the stringent high availability requirements of financial-grade business systems.

[0129] The following describes a bill data processing apparatus provided in an embodiment of this application. The bill data processing apparatus described below and the bill data processing method described above can be referred to each other.

[0130] See Figure 6 A structural diagram of a billing data processing apparatus according to an exemplary embodiment is shown, as follows: Figure 6 As shown, it includes:

[0131] A creation module 100 is used to create a statement template; wherein, the statement template includes header fields and a parameterized SQL query statement template;

[0132] Synchronization module 200 is used to synchronize source table data in the business database to the analytical database;

[0133] The acquisition module 300 is used to obtain the corresponding target bill template according to the merchant identifier and bill type in the bill generation request when a bill generation request is received.

[0134] The query module 400 is used to generate an SQL query statement based on the SQL query statement template in the target bill template and the current running parameters, and to query bill data in the analytical database based on the SQL query statement.

[0135] The generation module 500 is used to generate a statement file based on the queried statement data, according to the order of the header fields and the display name.

[0136] The billing data processing apparatus provided in this application introduces a configurable reconciliation bill template mechanism, which encapsulates the bill header fields and SQL query logic into templates, achieving dynamic separation between the bill structure and query logic. When different formats or content reconciliation bills need to be generated, only the corresponding template needs to be configured, without modifying the program code, thereby significantly improving the system's flexibility and scalability, and reducing maintenance costs and response time. Simultaneously, by synchronizing business data to an analytical database and executing queries in that database based on template-generated SQL statements, the high-performance query capabilities of the analytical database are effectively utilized, avoiding performance bottlenecks caused by performing complex join operations directly in the business database. Therefore, this application embodiment can significantly improve the efficiency and adaptability of bill generation while ensuring the accuracy of bill generation, meeting the personalized bill generation needs of multiple merchants and multiple scenarios.

[0137] Based on the above embodiments, as a preferred implementation, the creation module 100 is specifically used to: receive a template configuration request through a visual configuration interface, and create a statement template according to the template configuration request; wherein, the template configuration request includes a merchant identifier, statement type, a list of header fields, data source fields and display names corresponding to each header field, and a parameterized SQL query statement template containing placeholders; in response to the template release instruction, mark the newly created statement template as active, and mark other statement templates corresponding to the merchant identifier and the statement type as historical.

[0138] Accordingly, the acquisition module 300 is specifically used to: acquire the corresponding target bill template that is in an active state based on the merchant identifier and bill type in the bill generation request.

[0139] Based on the above embodiments, as a preferred implementation, the statement template further includes a data processing mode, which includes a real-time join query mode or a wide table pre-construction mode.

[0140] Accordingly, the synchronization module 200 is specifically used for: if the data processing mode is the real-time join query mode, then synchronizing the business data in the business database to the analytical database in the form of multiple source tables; if the data processing mode is the wide table pre-construction mode, then periodically executing a predefined wide table construction task, synchronizing the joined business data to the analytical database in the form of a single wide table; wherein, the wide table construction task is to join and integrate multiple source tables in the business database into a wide table.

[0141] Based on the above embodiments, as a preferred embodiment, the query module 400 includes:

[0142] The first query unit is used to perform a join query on multiple source tables in the analytical database based on the SQL query statement when the data processing mode is the real-time join query mode.

[0143] The second query unit is used to perform a single-table query on the wide table in the analytical database based on the SQL query statement when the data processing mode is the wide table pre-construction mode.

[0144] Based on the above embodiments, as a preferred implementation, the SQL query statement template includes at least one placeholder;

[0145] Accordingly, the query module 400 includes:

[0146] The identification unit is used to identify at least one placeholder in the SQL query statement template; wherein the placeholder includes any one or a combination of any of the following: merchant identifier, reconciliation date, accounting cycle, and business type identifier;

[0147] The replacement unit is used to replace the parameter values ​​corresponding to each placeholder in the current running parameters with the corresponding positions in the SQL query statement template to generate an executable SQL query statement.

[0148] Based on the above embodiments, as a preferred embodiment, it further includes:

[0149] The comparison module is used to query billing data in the business database based on the SQL query statement, and compare the billing data queried in the analytical database with the billing data queried in the business database; if the comparison is consistent, the workflow of the generation module 500 is started.

[0150] Based on the above embodiments, as a preferred embodiment, it further includes:

[0151] The push module is used to format and package the statement file to generate a delivery file package; and automatically push the delivery file package to the target server storage path corresponding to the merchant identifier through a preset secure transmission channel.

[0152] Regarding the apparatus in the above embodiments, the specific manner in which each module performs its operation has been described in detail in the embodiments related to the method, and will not be elaborated upon here.

[0153] Based on the hardware implementation of the above program modules, and in order to implement the method of the embodiments of this application, the embodiments of this application also provide an electronic device. Figure 7 This is a structural diagram of an electronic device according to an exemplary embodiment, such as... Figure 7 As shown, the electronic device includes:

[0154] Communication interface 1 enables information exchange with other devices, such as network devices;

[0155] Processor 2 is connected to communication interface 1 to enable information exchange with other devices and, when running a computer program, executes the billing data processing method provided by one or more of the above-mentioned technical solutions. The computer program is stored in memory 3.

[0156] Of course, in practical applications, the various components in an electronic device are coupled together through bus system 4. It can be understood that bus system 4 is used to achieve communication and connection between these components. In addition to the data bus, bus system 4 also includes a power bus, a control bus, and a status signal bus. However, for clarity, in... Figure 7 The general will label all buses as Bus System 4.

[0157] The memory 3 in this embodiment is used to store various types of data to support the operation of the electronic device. Examples of such data include any computer program used to operate on the electronic device.

[0158] It is understood that memory 3 can be volatile memory or non-volatile memory, or both. Non-volatile memory can be read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), ferromagnetic random access memory (FRAM), flash memory, magnetic surface memory, optical disc, or compact disc read-only memory (CD-ROM); magnetic surface memory can be disk storage or magnetic tape storage. Volatile memory can be random access memory (RAM), which is used as an external cache. By way of example, but not limitation, many forms of RAM are available, such as Static Random Access Memory (SRAM), Synchronous Static Random Access Memory (SSRAM), Dynamic Random Access Memory (DRAM), Synchronous Dynamic Random Access Memory (SDRAM), Double Data Rate Synchronous Dynamic Random Access Memory (DDRSDRAM), Enhanced Synchronous Dynamic Random Access Memory (ESDRAM), SyncLink Dynamic Random Access Memory (SLDRAM), and Direct Rambus Random Access Memory (DRRAM).The memory 3 described in the embodiments of this application is intended to include, but is not limited to, these and any other suitable types of memory.

[0159] The methods disclosed in the embodiments of this application can be applied to processor 2, or implemented by processor 2. Processor 2 may be an integrated circuit chip with signal processing capabilities. In the implementation process, each step of the above method can be completed by the integrated logic circuit of the hardware in processor 2 or by instructions in the form of software. The processor 2 may be a general-purpose processor, DSP, or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. Processor 2 can implement or execute the methods, steps and logic block diagrams disclosed in the embodiments of this application. The general-purpose processor may be a microprocessor or any conventional processor, etc. The steps of the methods disclosed in the embodiments of this application can be directly manifested as being executed by a hardware decoding processor, or being executed by a combination of hardware and software modules in the decoding processor. The software modules may be located in a storage medium, which is located in memory 3. Processor 2 reads the program in memory 3 and completes the steps of the aforementioned method in combination with its hardware.

[0160] When processor 2 executes the program, it implements the corresponding processes in the various methods of the embodiments of this application. For the sake of brevity, these will not be described in detail here.

[0161] In an exemplary embodiment, this application also provides a storage medium, namely a computer storage medium, specifically a computer-readable storage medium, such as a memory 3 that stores a computer program, which can be executed by a processor 2 to complete the steps described in the aforementioned method. The computer-readable storage medium may be a memory such as FRAM, ROM, PROM, EPROM, EEPROM, Flash Memory, magnetic surface memory, optical disc, or CD-ROM.

[0162] Those skilled in the art will understand that all or part of the steps of the above method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, it performs the steps of the above method embodiments. The aforementioned storage medium includes various media that can store program code, such as mobile storage devices, ROM, RAM, magnetic disks, or optical disks.

[0163] Alternatively, if the integrated units described above are implemented as software functional modules and sold or used as independent products, they can also be stored in a computer-readable storage medium. Based on this understanding, the technical solutions of the embodiments of this application, or the parts that contribute to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause an electronic device (which may be a personal computer, server, network device, etc.) to execute all or part of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as mobile storage devices, ROM, RAM, magnetic disks, or optical disks.

[0164] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any changes or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application.

Claims

1. A method for processing billing data, characterized in that, include: Create a statement template; wherein the statement template includes header fields and a parameterized SQL query statement template; Synchronize source table data from the business database to the analytical database; When a statement generation request is received, the corresponding target statement template is obtained based on the merchant identifier and statement type in the statement generation request. Based on the SQL query statement template in the target bill template and the current running parameters, an SQL query statement is generated, and bill data is queried in the analytical database based on the SQL query statement. Based on the retrieved billing data, a statement file is generated according to the order of the header fields and the display name.

2. The billing data processing method according to claim 1, characterized in that, The creation of the statement template includes: The system receives template configuration requests through a visual configuration interface and creates statement templates based on these requests. The template configuration requests include merchant identifier, statement type, list of header fields, data source fields and display names corresponding to each header field, and a parameterized SQL query statement template containing placeholders. In response to the template release command, the newly created statement template is marked as active, and other statement templates corresponding to the merchant identifier and the statement type are marked as historical. Accordingly, obtaining the corresponding target bill template based on the merchant identifier and bill type in the bill generation request includes: Obtain the corresponding active target bill template based on the merchant identifier and bill type in the bill generation request.

3. The billing data processing method according to claim 1, characterized in that, The statement template also includes a data processing mode, which includes a real-time join query mode or a wide table pre-construction mode. Accordingly, synchronizing source table data from the business database to the analytical database includes: If the data processing mode is the real-time join query mode, then the business data in the business database will be synchronized to the analytical database in the form of multiple source tables; If the data processing mode is the wide table pre-construction mode, then a predefined wide table construction task is executed periodically to synchronize the associated business data to the analytical database in the form of a single wide table; wherein, the wide table construction task is to associate and integrate multiple source tables in the business database into a wide table.

4. The billing data processing method according to claim 3, characterized in that, The process of retrieving billing data from the analytical database based on the SQL query statement includes: If the data processing mode is the real-time join query mode, then the multiple source tables in the analytical database are joined based on the SQL query statement; If the data processing mode is the wide table pre-construction mode, then a single-table query is performed on the wide table in the analytical database based on the SQL query statement.

5. The billing data processing method according to claim 1, characterized in that, The SQL query template includes at least one placeholder; Accordingly, generating the SQL query statement based on the SQL query statement template in the target bill template and the current running parameters includes: Identify at least one placeholder in the SQL query statement template; wherein the placeholder includes any one or a combination of any of the following: merchant identifier, reconciliation date, accounting cycle, and business type identifier; The parameter values ​​corresponding to each placeholder in the current running parameters are replaced in the corresponding positions of the SQL query statement template to generate an executable SQL query statement.

6. The billing data processing method according to claim 1, characterized in that, After generating the SQL query statement based on the SQL query statement template in the target bill template and the current running parameters, the process further includes: Based on the SQL query statement, query the billing data in the business database; Compare the billing data retrieved from the analytical database with the billing data retrieved from the business database; If the comparison is consistent, then the step of generating a statement file based on the retrieved statement data according to the order of the header fields and the display name is executed.

7. The billing data processing method according to claim 1, characterized in that, After generating the statement file based on the retrieved statement data according to the order of the header fields and the display name, the process further includes: The statement of account files are formatted and packaged to generate a delivery file package; The delivery file package is automatically pushed to the target server storage path corresponding to the merchant identifier through a preset secure transmission channel.

8. A billing data processing device, characterized in that, include: A creation module is used to create a statement template; wherein, the statement template includes header fields and parameterized SQL query statement templates; The synchronization module is used to synchronize source table data from the business database to the analytical database; The acquisition module is used to obtain the corresponding target bill template based on the merchant identifier and bill type in the bill generation request when a bill generation request is received. The query module is used to generate an SQL query statement based on the SQL query statement template in the target bill template and the current running parameters, and to query bill data in the analytical database based on the SQL query statement; The generation module is used to generate a statement file based on the retrieved statement data, according to the order of the header fields and the display name.

9. An electronic device, characterized in that, include: Memory, used to store computer programs; A processor for executing the computer program to implement the steps of the billing data processing method as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed, implements the steps of the billing data processing method as described in any one of claims 1 to 7.

Images