Multi-source data reconciliation system based on HASH structure and color marking

Abstract

The invention relates to a multi-source data reconciliation system based on a HASH structure and color marking, the searching and matching efficiency is improved by adopting the Hash structure and matched reconciliation data, then random colors are generated by using an HSL algorithm and color marking is performed on a matching result, the readability of the reconciliation result and the user experience are enhanced, and then the reconciliation efficiency is improved by adopting a Timsort sorting algorithm. The colors are sorted according to the sequence of hue, saturation and brightness, the rows with the same color are arranged together, and the rows with unmarked colors are arranged at the last, so that the sorting efficiency and stability are improved; according to the multi-source data reconciliation system provided by the invention, the complexity of maintenance and management is reduced while the automation and accuracy are improved, and an efficient and reliable multi-source data reconciliation solution is provided for various enterprises and merchants.

Description

Technical Field

[0001] The present invention relates to the technical field of financial data management, and in particular to a multi-source data reconciliation system based on HASH structure and color marking. Background Art

[0002] With the development of Internet technology, there are more and more payment methods and channels. From traditional cash payment and card payment to now scan code payment, aggregate payment and credit payment, the payment method has a clear trend of diversification. Along with the payment method, the payment channel has expanded from a single bank transfer to multiple platforms such as WeChat and Alipay, which has greatly facilitated the lives of users and improved payment efficiency. However, the diversified payment methods have brought considerable challenges to the reconciliation work of merchants. For small and medium-sized merchants, different payment methods have different corresponding reconciliation processes and bill formats, which increases the complexity of reconciliation. Secondly, the continuous increase in the number of third-party payments has made manual reconciliation inefficient and prone to errors. In addition, the diversification of payment methods will also make the imbalance of accounts more complicated.

[0003] At present, common reconciliation methods include manual reconciliation and automated reconciliation. There are two main automated reconciliation methods: (1) Database table-based reconciliation method, which uses ETL tools to extract data from different payment channels, converts the data into a unified format and loads it into the database, and compares the core fields of business order data and payment channel bill data through SQL queries to find the same and different data; (2) Redis-based reconciliation method configures cron expressions for scheduled execution, stores business order data and payment channel bill data in a Redis collection, and uses Redis string concatenation and set operations to reconcile, find the same and different data, and perform subsequent processing;

[0004] Although these two automated reconciliation methods have significant advantages in improving reconciliation efficiency and accuracy, they also have obvious disadvantages; the reconciliation method based on the database system has complex database maintenance and performance optimization issues in maintenance and management, and the relational database consumes a lot of system resources when processing large-scale data; the Redis-based reconciliation system has certain challenges in memory management, persistence mechanism, technical requirements and ecosystem support, which increases the difficulty of system maintenance and management; Overall, the automated reconciliation system based on database tables and Redis has significant advantages in efficiency and accuracy, but a lot of resources and expertise are required in the implementation process to ensure the stable operation of the system. For small and medium-sized enterprises, it is difficult to have sufficient resources and technical support to maintain and manage these complex systems, and they cannot fully utilize the advantages brought by these reconciliation systems. Summary of the invention

[0005] In view of this, the present invention provides a multi-source data reconciliation system based on HASH structure and color marking, which adapts to the reconciliation needs of different payment channels through flexible reconciliation rule configuration, greatly simplifies the merchant's reconciliation work, and improves the reconciliation efficiency and accuracy.

[0006] To achieve the above purpose, a multi-source data reconciliation system based on HASH structure and color marking includes the following steps:

[0007] S1. Parse the general ledger file;

[0008] The user selects a general ledger file, and the system matches the file name with the keywords in the configuration file to obtain the matching column names in the general ledger file and displays them in the interactive interface. The matching methods include exact matching and fuzzy matching.

[0009] Matching via QMultiHash<QStringList,QStringList> Implementation, where the first QStringList represents KEY, which is used to store the keyword list and supports multiple keywords, and the second QStringList represents VALUE, which is used to store the column name to be matched in the keyword file name;

[0010] S2. Parse the received file;

[0011] S3. Read the general ledger and receipt file data respectively according to the matching column names;

[0012] Read and write the incoming file through the QtXlsx library, obtain the column number according to the matching column name, read the data of all rows into memory, and use QMultiHash<double,DD> Stores bill information, where the first column of double type represents the total amount. The DD structure includes the incoming file reading and writing class, payment total amount, completion time, payment object, row number, column number, color, and file name;

[0013] S4, generating random colors and removing dark colors, the algorithm for generating random colors is implemented by adjusting the HSL value of the color;

[0014] S401, initializing a random number seed;

[0015] S402, generating a color with a hue value between 0 and 360, a saturation value between 0 and 100, and a brightness value between 50 and 90;

[0016] S403, the algorithm checks whether the color generated in step S402 already exists in the list of generated colors, and if the color is repeated, a new color is generated;

[0017] S404, the algorithm converts the generated HSL value into RGB value and stores it in the color list;

[0018] S5, data matching;

[0019] S6, sort by color, sort the matched data by color, put the rows with the same color together, and put the rows without color marked at the end;

[0020] S601, the system converts all colors from RGB values ​​to HSL values;

[0021] S602, sorting in order of hue, saturation and brightness; sorting the hue values ​​from small to large, if the hue values ​​are the same, sorting them from small to large according to the saturation values, if the saturation values ​​are the same, sorting them from small to large according to the brightness values;

[0022] S603, converting the sorted colors into RGB values;

[0023] S7. Write the sorted results into a new form, output the data and retain the original data.

[0024] Preferably, the exact match requires that the column names are completely consistent after removing spaces and line breaks; the fuzzy match only needs to include keywords. If multiple columns are fuzzily matched, the system will pop up a dialog box for the user to manually select and confirm.

[0025] Preferably, the arrival file is matched with the keywords in the configuration file to obtain the matching column name in the general ledger file, and displayed in the interactive interface; the system supports selecting multiple arrival files, and the arrival files are displayed in a list, with each row displaying the arrival file path, arrival file name and column name to be matched.

[0026] Preferably, the uniqueKeys method of QMultiHash is used to obtain the KEYs of the general ledger file and the receipt file respectively, and the KEYs of the general ledger file are traversed; if the number of VALUEs corresponding to the KEY is the same as the number of VALUEs of the corresponding KEY of the receipt file, it is considered that the match is successful; if the number of VALUEs corresponding to the KEY is different from the number of VALUEs of the corresponding KEY of the receipt file, then the match is based on the rule that the receipt time is within two days after the general ledger file time and the payer must be the same;

[0027] If the data is matched successfully, the system will mark the corresponding row and column data in the general ledger file and the account file with the same color, and the unmatched entries will be marked in red.

[0028] Preferably, the color sorting adopts the Timsort sorting algorithm, and the rows without color marking are marked separately during the sorting process and appended to the end of the list after the sorting is completed.

[0029] Preferably, the system creates a new EXCEL sheet, writes the matched results into the sheet in color order, and appends rows without color markings.

[0030] Compared with the prior art, the present invention has the following beneficial effects:

[0031] 1. The present invention adopts a Hash structure to store and match data, which has the advantages of fast search and matching when processing large amounts of data, making the reconciliation process more efficient, especially when processing multi-source data reconciliation, and significantly improving the processing speed and performance of the system;

[0032] 2. The present invention is developed on the basis of the QT framework, which not only provides a rich function library, supports high-performance graphical interface and data processing, but also has excellent cross-platform capabilities, so that the system can maintain excellent performance, smooth operation and good user experience on multiple systems such as Windows, Linux, macOS, etc.;

[0033] 3. The present invention uses color to mark the reconciliation results, so that users can quickly identify matches and abnormal items, enhance the readability of the reconciliation results, and significantly improve work efficiency and accuracy, helping users to quickly locate in complex data;

[0034] 4. The system provided by the present invention can run on a single machine, is not affected by the network environment, reduces external dependence, and improves the stability and security of the system; at the same time, the independent operation feature simplifies maintenance work and reduces maintenance costs, and is suitable for the reconciliation needs of various enterprises and merchants. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] Figure 1 The figure is a flow chart of a multi-source data reconciliation system based on a hash structure and color marking according to the present invention. DETAILED DESCRIPTION

[0036] In order to further explain the technical means and effects adopted by the present invention to achieve the predetermined invention purpose, the specific implementation mode, structure, characteristics and effects of the present invention are described in detail below in combination with the accompanying drawings and preferred embodiments.

[0037] In order to overcome the problem that a large amount of system resources are consumed and the reconciliation efficiency is low when performing multi-source data reconciliation in the prior art, the present invention provides a multi-source data reconciliation system based on a HASH structure and color marking, comprising the following steps:

[0038] S1. Parse the general ledger file;

[0039] The user selects the general ledger file, and the system matches the file name with the keywords in the configuration file to obtain the matching column names in the general ledger file and displays them in the interactive interface.

[0040] Matching via QMultiHash<QStringList,QStringList> Implementation, where the first QStringList represents KEY, which is used to store the keyword list and supports multiple keywords, and the second QStringList represents VALUE, which is used to store the column name to be matched in the keyword file name;

[0041] The matching methods include exact matching and fuzzy matching. Exact matching requires that the column names are exactly the same after removing spaces and line breaks. Fuzzy matching only requires the inclusion of keywords. If multiple columns are fuzzily matched, the system will pop up a dialog box for the user to manually select and confirm.

[0042] In this embodiment, by traversing QMultiHash and matching the file name of the general ledger file, the matching column name of the general ledger file is obtained after a successful match, and it is displayed on the interface for the user to confirm or modify;

[0043] S2. Parse the received file;

[0044] The received files are matched with the keywords in the configuration file to obtain the matching column names in the general ledger file and displayed in the interactive interface; the system supports selecting multiple received files, which are displayed in a list, with each line showing the received file path, received file name, and column name to be matched;

[0045] In this embodiment, multiple EXCEL files are selected through the file dialog box, and the matching column name acquisition method is called for each file to obtain the corresponding column name. The system displays the file path, file name and name to be matched in a table, and the user can manually adjust the matching relationship of the column name;

[0046] S3. Read the general ledger and receipt file data respectively according to the matching column names;

[0047] Read and write the incoming file through the QtXlsx library, obtain the column number according to the matching column name, read the data of all rows into memory, and use QMultiHash<double,DD> Stores bill information, where the first column of double type represents the total amount. The DD structure includes the incoming file reading and writing class, payment total amount, completion time, payment object, row number, column number, color, and file name;

[0048] S4, generating random colors and removing dark colors, the algorithm for generating random colors is implemented by adjusting the HSL value of the color;

[0049] HSL value is a method used to represent color, which defines color based on three parameters: Hue, Saturation and Lightness.

[0050] S401, initializing a random number seed to ensure that the generated color is random;

[0051] S402, generating a color with a hue value between 0 and 360, a saturation value between 0 and 100, and a brightness value between 50 and 90, so as to ensure that the production color is not too dim, thereby improving the visual effect;

[0052] S403, the algorithm checks whether the color generated in step S402 already exists in the list of generated colors. If the color is repeated, a new color is generated to ensure the uniqueness of each color.

[0053] S404, the algorithm converts the generated HSL value into RGB value and stores it in the color list;

[0054] Generate random colors by adjusting the HSL value of the color to ensure rich colors while avoiding overly dark tones, thereby improving visual recognition and user experience;

[0055] S5, data matching;

[0056] Use the uniqueKeys method of QMultiHash to obtain the KEY of the general ledger file and the receipt file respectively, and traverse the KEY of the general ledger file; if the number of VALUEs corresponding to the KEY is the same as the number of VALUEs of the corresponding KEY of the receipt file, it is considered that the match is successful; if the number of VALUEs corresponding to the KEY is different from the number of VALUEs of the corresponding KEY of the receipt file, match according to the rule that the receipt time is within two days after the general ledger file time and the payer must be the same;

[0057] If the data is matched successfully, the system will mark the corresponding rows and columns of the general ledger file and the received account file in the same color. The unmatched items will be marked in red, indicating that some of them are abnormal.

[0058] S6. Sort by color. Use the Timsort algorithm to sort the matched data by color to ensure the stability and efficiency of sorting. Sort by color. Rows with the same color are placed together, and rows without color are placed at the end.

[0059] S601, the system converts all colors from RGB values ​​to HSL values;

[0060] S602, sorting the colors in order of hue, saturation and brightness; sorting the hue values ​​from small to large to ensure that the colors are arranged in order on the hue wheel;

[0061] If the hue values ​​are the same, sort them from small to large according to the saturation value to ensure the color purity is orderly;

[0062] If the saturation values ​​are the same, sort them from smallest to largest according to the brightness value to ensure that the colors are arranged from darkest to brightest.

[0063] S603, converting the sorted colors into RGB values, marking the rows without color markings separately during the sorting process and appending them to the end of the list after the sorting is completed;

[0064] S7. The system creates a new EXCEL sheet, writes the sorted results into the new sheet in color order, appends rows without color markings, outputs the data and retains the original data.

[0065] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Although the present invention has been disclosed as a preferred embodiment as above, it is not used to limit the present invention. Any technical personnel in this field can make some changes or modify the technical contents disclosed above into equivalent embodiments without departing from the scope of the technical solution of the present invention. However, any brief modifications, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solution of the present invention are still within the scope of the technical solution of the present invention.

Claims

1. A multi-source data reconciliation system based on HASH structure and color marking, characterized in that: The following steps are involved: S1. Parse the general ledger file; The user selects a general ledger file, and the system matches the file name with the keywords in the configuration file to obtain the matching column names in the general ledger file and displays them in the interactive interface. The matching methods include exact matching and fuzzy matching. Matching via QMultiHash<QStringList,QStringList> Implementation, where the first QStringList represents KEY, which is used to store the keyword list and supports multiple keywords, and the second QStringList represents VALUE, which is used to store the column name to be matched in the keyword file name; S2. Parse the received file; S3. Read the general ledger and receipt file data respectively according to the matching column names; Read and write the incoming file through the QtXlsx library, obtain the column number according to the matching column name, read the data of all rows into memory, and use QMultiHash<double,DD> Stores bill information, where the first column of double type represents the total amount. The DD structure includes the incoming file reading and writing class, payment total amount, completion time, payment object, row number, column number, color, and file name; S4, generating random colors and removing dark colors, the algorithm for generating random colors is implemented by adjusting the HSL value of the color; S401, initializing a random number seed; S402, generating a color with a hue value between 0 and 360, a saturation value between 0 and 100, and a brightness value between 50 and 90; S403, the algorithm checks whether the color generated in step S402 already exists in the list of generated colors, and if the color is repeated, a new color is generated; S404, the algorithm converts the generated HSL value into RGB value and stores it in the color list; S5, data matching; S6, sort by color, sort the matched data by color, put the rows with the same color together, and put the rows without color marked at the end; S601, the system converts all colors from RGB values ​​to HSL values; S602, sorting in order according to hue, saturation and brightness; Sort by hue value from small to large. If the hue values ​​are the same, sort by saturation value from small to large. If the saturation values ​​are the same, sort by brightness value from small to large. S603, converting the sorted colors into RGB values; S7. Write the sorted results into a new form, output the data and retain the original data.

2. According to claim 1, a multi-source data reconciliation system based on HASH structure and color marking is characterized in that: The exact match requires that the column names are completely consistent after removing spaces and line breaks; the fuzzy match only needs to include keywords. If multiple columns are fuzzily matched, the system will pop up a dialog box for the user to manually select and confirm.

3. According to claim 1, a multi-source data reconciliation system based on HASH structure and color marking is characterized in that: The arrival file is matched with the keywords in the configuration file to obtain the matching column name in the general ledger file, and displayed in the interactive interface; the system supports selecting multiple arrival files, and the arrival files are displayed in a list, with each row displaying the arrival file path, arrival file name and column name to be matched.

4. A multi-source data reconciliation system based on HASH structure and color marking according to claim 1, characterized in that: Use the uniqueKeys method of QMultiHash to obtain the KEY of the general ledger file and the receipt file respectively, and traverse the KEY of the general ledger file; if the number of VALUEs corresponding to the KEY is the same as the number of VALUEs of the corresponding KEY of the receipt file, it is considered that the match is successful; if the number of VALUEs corresponding to the KEY is different from the number of VALUEs of the corresponding KEY of the receipt file, match according to the rule that the receipt time is within two days after the general ledger file time and the payer must be the same; If the data is matched successfully, the system will mark the corresponding row and column data in the general ledger file and the received account file with the same color, and the unmatched entries will be marked in red.

5. According to claim 1, a multi-source data reconciliation system based on HASH structure and color marking is characterized in that: Color sorting uses the Timsort sorting algorithm. Rows without color labels are marked separately during the sorting process and appended to the end of the list after the sorting is completed.

6. A multi-source data reconciliation system based on HASH structure and color marking according to claim 1, characterized in that: The system creates a new EXCEL sheet, writes the matching results into the sheet in color order, and appends rows without color markings.

Images