A method and system for multi-user data reporting and collection

Abstract

The present invention discloses a method and system for multi-user data reporting and collection, wherein the method comprises: obtaining a template file uploaded by an administrator, parsing the template file, and creating a database table; obtaining the account information selected by the administrator, and sending the data upload information to the account corresponding to the account information according to the account information, so that the data reporting personnel corresponding to the account information download the template file and upload the reporting data; obtaining the reporting data uploaded by the data reporting personnel, and performing a program review on the reporting data. If the review is passed, the reporting data is stored to realize the collection of file data. After the administrator uploads the template file, the present invention automatically creates a database table, and after the data reporting personnel upload the data, the audited data is stored and sorted to realize the function of data aggregation, thereby solving the problem of cumbersome data reporting task distribution and data aggregation. The present invention can be widely used in the field of data statistics.

Description

Technical Field

[0001] The present invention relates to the field of transportation statistics, and in particular to a method and system for reporting and collecting multi-user data. Background Art

[0002] With the continuous deepening of reform and opening up, my country's social economy has developed rapidly. Transportation plays a vital role in economic and social development. Transportation statistics are an important part of social and economic statistics. Its fundamental function is to use scientific statistical theories and technologies to collect, organize and calculate relevant data reflecting the economic activities of highway transportation, and obtain several indicators that can reflect the transportation industry and transportation economic phenomena, so as to provide a basis and reference for planning, decision-making, and industry management.

[0003] However, from a practical perspective, there are many problems in my country's transportation statistics work, such as: the distribution of multi-user data reporting tasks and data aggregation are cumbersome; data reporting personnel arbitrarily modify the data reporting template format, resulting in chaotic reported data formats, and heavy workload for data review administrators; weak progress control of data reporting tasks; and untimely feedback on reporting success and errors. Summary of the invention

[0004] In order to solve at least one of the technical problems existing in the prior art to a certain extent, the present invention aims to provide a method and system for multi-user data reporting and collection to improve the efficiency of data collection.

[0005] To achieve the above object, an embodiment of the present invention provides a method for multi-user data reporting and aggregation, comprising the following steps:

[0006] Obtain the template file uploaded by the administrator, parse the template file, and create a database table;

[0007] Obtaining the account information selected by the administrator, and sending the data upload information to the account corresponding to the account information according to the account information, so that the data reporting personnel corresponding to the account information download the template file and upload the reported data;

[0008] Obtain the reported data uploaded by the data reporter, and perform a program review on the reported data. If the review is passed, store and organize the reported data according to the database table to achieve the collection of file data.

[0009] To achieve the above purpose, an embodiment of the present invention further proposes a system for multi-user data reporting and aggregation, comprising:

[0010] at least one processor;

[0011] at least one memory for storing at least one program;

[0012] When the at least one program is executed by the at least one processor, the at least one processor implements the steps of the aforementioned method.

[0013] The present invention proposes a method and system for multi-user data reporting and collection. After the administrator uploads the template file, the background system automatically parses the template file and creates a database table to facilitate the operation of the administrator; after several data reporters upload the data, the background system automatically performs a program review on the data and stores the data that has passed the review, thereby realizing the function of data collection. It can be used for multi-user data reporting, collection, and analysis in the process of transportation statistics to overcome the problems of cumbersome data reporting task distribution and data collection, chaotic format of reported data, and heavy workload of data review managers. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the embodiments of the present invention or the drawings of related technical solutions in the prior art are introduced below. It should be understood that the drawings introduced below are only for the convenience of clearly describing some embodiments of the technical solutions of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.

[0015] Figure 1 is a workflow diagram of a SaaS system in an embodiment of the present invention;

[0016] Figure 2 This is a schematic diagram of a template for summarizing and statistically analyzing basic information of transportation projects in an embodiment of the present invention;

[0017] Figure 3 is a schematic diagram of the database table modelconcat in an embodiment of the present invention;

[0018] Figure 4 is a schematic diagram of the database table limit_sum in an embodiment of the present invention;

[0019] Figure 5 Schematic diagram of the table structure of a database data processing table in an embodiment of the present invention;

[0020] Figure 6 Schematic diagram of the table structure of the database data storage table in the embodiment of the present invention

[0021] Figure 7 This is a schematic diagram of a task distribution interface of a data audit system in an embodiment of the present invention;

[0022] Figure 8 1 is a schematic diagram of a task list after a data reporting task is successfully distributed in an embodiment of the present invention;

[0023] Fig. 9 It is a task progress diagram of the data audit system in the embodiment of the present invention for real-time statistics of all submitted tasks;

[0024] Fig.10 This is a schematic diagram of template file downloading and template file content in an embodiment of the present invention;

[0025] Fig.11 It is a schematic diagram of data reporting by task 12132035090 in an embodiment of the present invention;

[0026] Fig.12 It is a schematic diagram of task 12132035091 reporting data in an embodiment of the present invention;

[0027] Fig.13 Schematic diagram of data audit rules in an embodiment of the present invention;

[0028] Fig.14 Schematic diagram of system audit results in an embodiment of the present invention

[0029] Fig.15 This is a schematic diagram of an error data file for task 12132035091 in an embodiment of the present invention;

[0030] Fig.16 This is a schematic diagram of task 12132035090 failing the review in an embodiment of the present invention;

[0031] Fig.17 This is a schematic diagram of successful data reporting in an embodiment of the present invention.

[0032] Fig.18 It is a schematic diagram of a database data storage table in an embodiment of the present invention;

[0033] Fig.19 Schematic diagram of an Excel file exported in an embodiment of the present invention;

[0034] Fig. 20 is a schematic diagram of task deletion in an embodiment of the present invention;

[0035] Fig.21 This is a schematic diagram of the system clearing template related data in an embodiment of the present invention;

[0036] Fig. 22 It is a flow chart of generating audit rules (SQL statements) in an embodiment of the present invention. DETAILED DESCRIPTION

[0037] The embodiments of the present invention are described in detail below, and examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and are not to be construed as limitations of the present invention. For the step numbers in the following embodiments, they are only provided for the convenience of explanation, and the order between the steps is not limited in any way, and the execution order of each step in the embodiment can be adaptively adjusted according to the understanding of those skilled in the art.

[0038] In the description of the present invention, it should be understood that descriptions involving orientations, such as up, down, front, back, left, right, etc., and orientations or positional relationships indicated are based on the orientations or positional relationships shown in the accompanying drawings, and are only for the convenience of describing the present invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore should not be understood as a limitation on the present invention.

[0039] In the description of the present invention, "several" means one or more, "more" means more than two, "greater than", "less than", "exceed" etc. are understood as not including the number itself, and "above", "below", "within" etc. are understood as including the number itself. If there is a description of "first" or "second", it is only used for the purpose of distinguishing the technical features, and cannot be understood as indicating or implying the relative importance or implicitly indicating the number of the indicated technical features or implicitly indicating the order of the indicated technical features.

[0040] In the description of the present invention, unless otherwise clearly defined, terms such as setting, installing, connecting, etc. should be understood in a broad sense, and technicians in the relevant technical field can reasonably determine the specific meanings of the above terms in the present invention based on the specific content of the technical solution.

[0041] This embodiment provides a method for defining and parsing a data reporting template, the details of which are as follows:

[0042] 1. Definition of data submission template

[0043] Each data reporting task corresponds to a data reporting template, and each data template corresponds to two database tables with the same table structure. The database tables are designed to facilitate data verification of data reported using the template and to temporarily store the reported data. The present invention does not involve the final database table for storing the reported data.

[0044] The template content includes: data column alias, data column storage type, data column name 1, description of whether it is required, data column format requirements, data column name 2, detailed description of data filling, and data filling examples. The following is a description of each content:

[0045] 1. The data column alias is the English alias of the column data, which is the same as the field name of the database table storing the column data.

[0046] 2. The data column storage type is the same as the field data type of the database table storing the data in the column. It has nothing to do with the content and format of the data in the column. It is stipulated to facilitate data verification of the submitted data. There are three data types in this method: "varchar(255)", "varchar(50)" and "varchar(20)".

[0047] 3. Data column name one is the Chinese meaning of the data in this column. Data column name one is the same as data column name two.

[0048] 4. Whether the description is required, describe whether the data in this column is required.

[0049] 5. Data column format restrictions refer to the content and format requirements of the data in this column. They correspond to the data column storage type. When the data column storage type is "varchar(255)", there are two types of data column format restrictions: one is the minimum character length and the maximum character length, and the other is "ComboBox()", which means that the data in this column is limited to the value in the drop-down box. The options in the drop-down box are in brackets. When the data column storage type is "varchar(50)", the data column format restrictions refer to the number of decimal places, the minimum and maximum values. When the data column storage type is "varchar(20)", the data column format restrictions refer to the minimum and maximum values ​​of the data value.

[0050] 6. Detailed description of data filling, including whether the data is required and the data column format restrictions.

[0051] 7. Data filling example, two lines of sample data.

[0052] The first five items in the template content (data column alias, data column storage type, data column name 1, description of whether it is required, and data column format requirements) are for the internal system, hidden in the template file, and invisible to data reporting personnel. The last three items (data column name 2, detailed description of data filling, and data filling examples) are for data reporting personnel to understand the data submission template.

[0053] 2. Analysis of data submission template

[0054] Data reporting template parsing is to parse and store the relevant information of each data column in the template content into a specified database table, which is used for subsequent operations such as creating a database table for temporary storage of reported data and generating reporting data verification rules.

[0055] The database table modelconcat is used to store the basic content of each data template after parsing. As shown in Table 1, the fields of this database table include:

[0056] Table 1

[0057] Field Name meaning Field Name meaning modelCode Template Number dataName Field Name dataDescribe Field Description dataRequireType Field Data Type dataLimit Drop-down box type number dataRequireNull Is this field required? maxCount Maximum value minCount Minimum value maxLengths Maximum character length minLengths Minimum character length decimalCount Decimal Places

[0058] The database table limit_sum is used to store the data column drop-down box option content after each data template is parsed. As shown in Table 2, the fields of this database table include:

[0059] Table 2

[0060]

[0061]

[0062] Parse the template file, parse the data column alias, data column storage type, data column name, required description, and data column format requirements of all data columns and store them in the database tables modelconcat and limit_sum. The parsing steps for each data column are as follows:

[0063] 1. Data column alias, stored in the "dataName" of the database table modelconcat;

[0064] 2. Data column storage type, stored in the "dataRequireType" of the database table modelconcat;

[0065] 3. The data column name is stored in the "dataDescribe" of the database table modelconcat;

[0066] 4. Whether the description is required is stored in the "dataRequireNull" of the database table modelconcat;

[0067] 5. Data column format requirements, parsing method is as follows:

[0068] 1) Determine whether the data column format limit contains "ComboBox". If so, store the options in the drop-down box in the ComboBox brackets into the database table limit_sum.

[0069] In the database table limit_sum, the template number is stored as "modelName", the data column alias is stored as "limit_code", and the drop-down box option content is stored in "limit_content".

[0070] Store the data column alias in the "dataLimit" of the database table modelconcat. The other constraints (maxCount, minCount, maxLengths, minLengths, decimalCount) default to 0; if none, the "dataLimit" of the database table modelconcat is "None", and proceed to the next step.

[0071] 2) Determine whether the data column storage type is "varchar(255)". If so, parse the data column format restrictions into the minimum and maximum character lengths in turn, and store them in the "minLengths" and "maxLengths" of the database table modelconcat respectively. The other constraints (maxCount, minCount, decimalCount) default to 0; if not, proceed to the next step.

[0072] 3) Determine whether the data column storage type is "varchar(50)". If so, parse the data column format restrictions into the decimal places, minimum value and maximum value of the value in sequence, and store them in the "decimalCount", "minCount" and "maxCount" of the database table modelconcat respectively. The other constraints (maxLengths, minLengths) are set to 0 by default; if not, proceed to the next step.

[0073] 4) The data column storage type is "varchar(20)". The data column format restrictions are parsed into the minimum and maximum values ​​of the values ​​in turn, and stored in the "minCount" and "maxCount" of the database table modelconcat respectively. The other constraints (maxLengths, minLengths, decimalCount) are 0 by default.

[0074] This embodiment also provides a data verification method, comprising the following steps:

[0075] 1) Create a database table to store the submitted data;

[0076] 2) Store the reported data into the database table;

[0077] 3) Generate data verification rules (SQL query statements);

[0078] 4) Verify the submitted data;

[0079] 5) Clear the database table. Clear the database table to facilitate the next data verification.

[0080] The step 1) includes the following contents:

[0081] 1.1) Query the database table modelconcat according to the template number of the data submission template used to obtain relevant information of all data columns of the template.

[0082] 1.2) Use the template number as the table name, create a database table according to the alias and storage type of each data column, and use it to temporarily store the data submitted using the data template.

[0083] The step 2) includes the following contents:

[0084] 2.1) Parse the data submission file and store the submitted data into the database table created in 1).

[0085] The step 3) includes the following contents:

[0086] 3.1) According to the template number of the data submission template used, query the database table modelconcat to obtain relevant information of all data columns of the template.

[0087] 3.2) Based on the data column related information obtained in 3.1), assemble SQL query statements to query data rows that do not meet the data content and format requirements of any data column. The steps for designing an SQL statement for a single data column are as follows:

[0088] 1. Determine whether the "dataRequireNull" of the data column is "No requirement". If so, it means that the data column has no mandatory requirements, and the SQL design is: "If the field data is empty, no judgment is made. If the field data is not empty, splice the SQL judgment of the following steps". If not, it means that the data column has mandatory requirements, and the SQL design is: "If the field data is empty, query the rows with empty field data. If the field data is not empty, splice the SQL judgment of the following steps";

[0089] 2. Determine whether the "dataLimit" of the data column is "None". If so, proceed to the next step; if not, it means that the data in this column is limited to the value in the drop-down box. The spliced ​​SQL content is: "Query the database table limit_sum based on the template number and the alias of the data column, obtain the drop-down box option content of the data column in the template, and query the rows where the field data does not belong to the drop-down box option content";

[0090] 3. Determine whether the "dataRequireType" of the field is "varchar(255)". If not, proceed to the next step. If so, it means that the data in this column only needs to determine the character length. The concatenated SQL content is: "Query the rows whose character length is less than "minLengths" or greater than "maxLengths"";

[0091] 4. Determine whether the "dataRequireType" of the field is "varchar(50)". If not, proceed to the next step. If so, it means that the data in this column only needs to determine the value and the number of decimal places. The concatenated SQL content is: "Query the rows whose value is less than "minCount" or greater than "maxCount" or whose number of decimal places is not "decimalCount"";

[0092] 5. Determine whether the "dataRequireType" of the field is "varchar(20)". If so, it means that the data in this column only needs to determine the value. The concatenated SQL content is: "Query the rows whose values ​​are less than "minCount" or greater than "maxCount"".

[0093] The step 4) includes the following contents:

[0094] 4.1) Use the SQL statement generated in step 3) to query the database table created in step 2).

[0095] 4.2) If the query result is not empty, it means that there is erroneous data. The erroneous data should be exported and fed back to the data reporting personnel;

[0096] 4.3) If the query result is empty, it means that the reported data is correct. The information will be fed back to the data reviewer for the next step of manual review.

[0097] Based on the above-mentioned definition and parsing method of a data reporting template and a data verification method, this embodiment also provides a system for multi-user data reporting and aggregation, which can be constructed through a variety of platforms. In this embodiment, it is constructed through a SaaS platform (therefore hereinafter referred to as a SaaS system), but it should be noted that corresponding systems can also be built and corresponding effects can be achieved through other platforms, which are not elaborated here one by one, but the corresponding solutions should fall within the scope of protection of the present invention.

[0098] Using the SaaS system to complete a data report includes the following steps:

[0099] 1) The administrator designs the data reporting template;

[0100] 2) The administrator logs in to the system and uploads the data template;

[0101] 3) The system parses the template file and stores the template information;

[0102] 4) Create a database table to store the submitted data;

[0103] 5) The administrator distributes data reporting tasks;

[0104] 6) Data reporting personnel submit data after receiving the task;

[0105] 7) The system reviews the submitted data;

[0106] 8) The administrator manually reviews the reported data that has passed the system review;

[0107] 9) Summarize and export all successfully submitted data for this distribution task;

[0108] 10) When the administrator deletes the task records and template files, the system clears the related files and data.

[0109] The step 1) includes the following contents:

[0110] Each data reporting task corresponds to a data reporting template, and each data template corresponds to two database tables with the same table structure. The database tables are designed to facilitate data verification of data reported using the template and to temporarily store the reported data. The present invention does not involve the final database table for storing the reported data.

[0111] The template content includes: data column alias, data column storage type, data column name 1, description of whether it is required, data column format requirements, data column name 2, detailed description of data filling, and data filling examples. The following is a description of each content:

[0112] 1. The data column alias is the English alias of the column data, which is the same as the field name of the database table storing the column data.

[0113] 2. The data column storage type is the same as the field data type of the database table storing the data in the column. It has nothing to do with the content and format of the data in the column. It is stipulated to facilitate data verification of the submitted data. There are three data types in this method: "varchar(255)", "varchar(50)" and "varchar(20)".

[0114] 3. Data column name one is the Chinese meaning of the data in this column. Data column name one is the same as data column name two.

[0115] 4. Whether the description is required, describe whether the data in this column is required.

[0116] 5. Data column format restrictions refer to the content and format requirements of the data in this column. They correspond to the data column storage type. When the data column storage type is "varchar(255)", there are two types of data column format restrictions: one is the minimum character length and the maximum character length, and the other is "ComboBox()", which means that the data in this column is limited to the value in the drop-down box. The options in the drop-down box are in brackets. When the data column storage type is "varchar(50)", the data column format restrictions refer to the number of decimal places, the minimum and maximum values. When the data column storage type is "varchar(20)", the data column format restrictions refer to the minimum and maximum values ​​of the data value.

[0117] 6. Detailed description of data filling, including whether the data is required and the data column format restrictions.

[0118] 7. Data filling example, two lines of sample data.

[0119] The first five items in the template content (data column alias, data column storage type, data column name 1, description of whether it is required, and data column format requirements) are for the internal system, hidden in the template file, and invisible to data reporting personnel. The last three items (data column name 2, detailed description of data filling, and data filling examples) are for data reporting personnel to understand the data submission template.

[0120] The step 2) includes the following contents:

[0121] 1. The system matches the administrator's username and password and enters the data review system according to the account type;

[0122] 2. The data review system includes five modules: task release, template upload, data review, task warehouse and template warehouse. The task release module is used to distribute tasks, the template upload module is used to upload data submission templates, the data review module is used by administrators to manually review the submitted data that has passed the system review, the task warehouse stores all task distribution records in the system, and is used by administrators to query the relevant data of a certain task, and the template warehouse stores all template data in the system;

[0123] 3. The administrator selects a data reporting template and defines a template number for the template.

[0124] 4. The system determines whether there is a database table named with the template number in the database. If not, uploading is allowed.

[0125] 5. The system uploads the template file to the server and stores the file name as the original template file name.

[0126] 6. The system stores the template file upload record in the database table modelfile. The template file upload record includes the template file name, template file uploader, template upload time, template number, etc.

[0127] The step 3) includes the following contents:

[0128] Data reporting template parsing is to parse and store the relevant information of each data column in the template content into a specified database table, which is used for subsequent operations such as creating a database table for temporary storage of reported data and generating reporting data verification rules.

[0129] The database table modelconcat is used to store the basic content of each data template after parsing. The content of the fields of this database table is shown in Table 1.

[0130] The database table limit_sum is used to store the data column drop-down box option content after each data template is parsed. The fields of this database table are shown in Table 2.

[0131] Parse the template file, parse the data column alias, data column storage type, data column name, required description, and data column format requirements of all data columns and store them in the database tables modelconcat and limit_sum. The parsing steps for each data column are as follows:

[0132] 1. Data column alias, stored in the "dataName" of the database table modelconcat;

[0133] 2. Data column storage type, stored in the "dataRequireType" of the database table modelconcat;

[0134] 3. The data column name is stored in the "dataDescribe" of the database table modelconcat;

[0135] 4. Whether the description is required is stored in the "dataRequireNull" of the database table modelconcat;

[0136] 5. Data column format requirements, parsing method is as follows:

[0137] 1) Determine whether the data column format restriction contains "ComboBox". If so, store the options in the drop-down box in the ComboBox brackets into the database table limit_sum;

[0138] In the database table limit_sum, the template number is stored as "modelName", the data column alias is stored as "limit_code", and the drop-down box option content is stored in "limit_content";

[0139] Store the data column alias in the "dataLimit" of the database table modelconcat. The other constraints (maxCount, minCount, maxLengths, minLengths, decimalCount) default to 0; if none, the "dataLimit" of the database table modelconcat is "None", and proceed to the next step.

[0140] 2) Determine whether the data column storage type is "varchar(255)". If so, parse the data column format restrictions into the minimum and maximum character lengths in turn, and store them in the "minLengths" and "maxLengths" of the database table modelconcat respectively. The other constraints (maxCount, minCount, decimalCount) default to 0; if not, proceed to the next step.

[0141] 3) Determine whether the data column storage type is "varchar(50)". If so, parse the data column format restrictions into the decimal places, minimum value and maximum value of the value in sequence, and store them in the "decimalCount", "minCount" and "maxCount" of the database table modelconcat respectively. The other constraints (maxLengths, minLengths) are set to 0 by default; if not, proceed to the next step.

[0142] 4) The data column storage type is "varchar(20)". The data column format restrictions are parsed into the minimum and maximum values ​​of the values ​​in turn, and stored in the "minCount" and "maxCount" of the database table modelconcat respectively. The other constraints (maxLengths, minLengths, decimalCount) are 0 by default.

[0143] The step 4) includes the following contents:

[0144] 1. Query the database table modelconcat according to the template number of the data submission template used to obtain relevant information of all data columns of the template.

[0145] 2. Use the template number as the table name, and create a database table according to the alias and storage type of each data column, including a data processing table and a data storage table. The data processing table is used to temporarily store data submitted using the data template. System data verification is performed in the data processing table. The data storage table is used to store all data successfully submitted using the data template.

[0146] The step 5) includes the following contents:

[0147] 1. The administrator distributes tasks and specifies data reporting personnel and data reporting templates. One task distribution corresponds to one data template and one or more data reporting personnel. When the task is successfully released, the task progress is initialized to "data to be uploaded".

[0148] 2. The data audit system counts the progress of all submitted tasks in real time, making it easier for administrators to understand the progress of data submission.

[0149] The step 6) includes the following contents:

[0150] 1. The system matches the user name and password of the data reporter and enters the data reporting system according to the account type. The data reporting system includes two modules: task list and data reporting. The task list module stores all task records of the data reporter in the system and counts the progress of the task reported by the data reporter in real time, so that the data reporter can grasp their respective task status. The data reporting module is used for data reporting.

[0151] 2. The data reporting personnel receive the task, download the data reporting template corresponding to the task, and fill in the data according to the format and content requirements specified in the template;

[0152] 3. The data reporter uploads the data file, the system stores the file on the server, and the task progress is updated to "template verification in progress".

[0153] 4. Parse the data submission file and store the submitted data into the database data processing table created in step 4).

[0154] The step 7) includes the following contents:

[0155] 7.1) Query the database table modelconcat according to the template number of the data submission template used to obtain relevant information of all data columns of the template;

[0156] 7.2) Based on the data column related information obtained in 7.1), SQL query statements are concatenated to query data rows that do not meet the data content and format requirements of any data column. Fig. 22 As shown, the steps for designing an SQL statement for a single data column are as follows:

[0157] 1. Determine whether the "dataRequireNull" of the data column is "No requirement". If so, it means that the data column has no mandatory requirements, and the SQL design is: "If the field data is empty, no judgment is made. If the field data is not empty, splice the SQL judgment of the following steps". If not, it means that the data column has mandatory requirements, and the SQL design is: "If the field data is empty, query the rows with empty field data. If the field data is not empty, splice the SQL judgment of the following steps";

[0158] 2. Determine whether the "dataLimit" of the data column is "None". If so, proceed to the next step; if not, it means that the data in this column is limited to the value in the drop-down box. The spliced ​​SQL content is: "Query the database table limit_sum based on the template number and the alias of the data column, obtain the drop-down box option content of the data column in the template, and query the rows where the field data does not belong to the drop-down box option content";

[0159] 3. Determine whether the "dataRequireType" of the field is "varchar(255)". If not, proceed to the next step. If so, it means that the data in this column only needs to determine the character length. The concatenated SQL content is: "Query the rows whose character length is less than "minLengths" or greater than "maxLengths"";

[0160] 4. Determine whether the "dataRequireType" of the field is "varchar(50)". If not, proceed to the next step. If so, it means that the data in this column only needs to determine the value and the number of decimal places. The concatenated SQL content is: "Query the rows whose value is less than "minCount" or greater than "maxCount" or whose number of decimal places is not "decimalCount"";

[0161] 5. Determine whether the "dataRequireType" of the field is "varchar(20)". If so, it means that the data in this column only needs to determine the value. The concatenated SQL content is: "Query the rows whose values ​​are less than "minCount" or greater than "maxCount"".

[0162] 7.3) Use the SQL statement generated in step 7.2) to query the database data processing table created in step 4);

[0163] 7.4) Clear the database data processing table;

[0164] 7.5) If the query result is empty, it means that the reported data is correct, and the task progress is updated to "Administrator review", and the information is fed back to the administrator to remind the administrator to conduct the next step of manual review;

[0165] 7.6) If the query result is not empty, it means that there is erroneous data. The task progress is updated to "Template verification failed". The erroneous data is exported in Excel format and fed back to the submitter, who then re-submits the data.

[0166] The step 8) includes the following contents:

[0167] 1. The administrator downloads the data file approved by the system and reviews the submitted data.

[0168] By parsing the data file, the reported data is stored in the database data storage table created in step 4), and the task progress is updated to "data reporting successful".

[0169] 2. If the review fails, the task progress will be updated to "Administrator review rejection". The data submitter will be reminded to resubmit the data.

[0170] The step 9) includes the following contents:

[0171] 1. After all data reporting tasks for the template are completed, the administrator will export all data in the database data storage table corresponding to the template to an Excel file.

[0172] 2. A data reporting task is completed.

[0173] The step 10) includes the following contents:

[0174] In order to improve the user experience of the system, it is necessary to clean up the historical task progress and data upload templates. The task warehouse module and template warehouse module of the administrator system store all task records and template data.

[0175] 1. When the administrator deletes the selected task record, the system deletes the task record, and deletes the reporting data files and error data files related to the task;

[0176] 2. The administrator deletes the selected template file. Before deleting, it is determined whether there is a reporting task for the template. If not, deletion is allowed. The system clears the template upload record, drop-down box alternative content and template information record of the template in the database table, deletes the database data processing table and data storage table, and deletes the template file and template summary data export file on the server.

[0177] The following uses a specific example to explain the above method and system in detail. It is necessary to collect basic information on transportation projects in each district and city.

[0178] like Figure 1 The steps of human-computer interaction on a SaaS system for multi-user data reporting and aggregation are specifically as follows:

[0179] 1. The administrator designs the data reporting template.

[0180] The first five lines in the template content (data column alias, data column storage type, data column name 1, description of whether it is required, data column format requirements) are for the internal system, and are hidden in the template file, and are not visible to data reporting personnel. The last four lines (data column name 2, detailed description of data filling, and data filling examples) are for data reporting personnel. The template file name is "Basic Information Summary Statistics Template for Transportation Projects", and the template content is as follows: Figure 2 shown.

[0181] 2. The administrator logs in to the system and uploads the data template.

[0182] 2.1) The system matches the administrator username and password and enters the data review system according to the account type;

[0183] 2.2) The administrator selects a data reporting template and defines a template number for the template.

[0184] 2.3) The system determines whether there is a database table named with the template number in the database. If not, uploading is allowed.

[0185] 2.4) The system uploads the template file to the server and stores the file name as the original template file name.

[0186] 2.5) The system stores the template file upload record in the database table modelfile. The template file upload record includes the template file name, template file uploader, template upload time, template number, etc.

[0187] 3. The system parses the template file, stores the template information, and creates a database table. The database table modelconcat is as follows Figure 3 As shown, the database table limit_sum is as follows Figure 4 shown.

[0188] 4. Create a database table to store the reported data. The database data processing table is as follows: Figure 5 As shown, the database data storage table is as follows Figure 6 shown.

[0189] 5. The administrator distributes data reporting tasks.

[0190] The administrator designates data reporters to report data, and the administrator system counts the task progress of all distributed tasks in real time. For the convenience of explanation, two data reporters are designated to report data in this embodiment. However, it should be noted that not only two data reporters can be designated, but also multiple (more than two) data reporters can be designated. In real examples, multiple (such as dozens or hundreds) data reporters are often involved. Figure 7This is a schematic diagram of the task distribution interface of the data audit system. Figure 8 This is a diagram of the task list after the data reporting task is successfully distributed. Fig. 9 A task progress diagram that provides real-time statistics of all submitted tasks for the data review system.

[0191] 6. The data reporting personnel report the data after receiving the task.

[0192] 6.1) The system matches the user name and password and enters the data reporting system according to the account type.

[0193] 6.2) The data reporting personnel receives the task and downloads the template file corresponding to the task; Fig.10 shown.

[0194] Fill in the data according to the format and content requirements specified in the template. In this example, task 12132035090 uploads the correct test data, such as Fig.11 Task 12132035091 uploaded incorrect test data (possibly incorrect data is highlighted), as shown in Fig.12 shown.

[0195] 6.3) The data reporter uploads the data file, and the system stores the file on the server (if the system audit fails, the data reporting file will be deleted and the error data file will be exported).

[0196] 6.4) Parse the data reporting file and store the reported data into the database data processing table corresponding to the template.

[0197] It should be noted that: due to the extremely short system operation gaps and time of steps 6.3, 6.4, and 7, the time when the task is in the "template verification" progress and the time when the database data processing table content is not empty are both extremely short, and there may be a delay between the pictures and the sequence of steps.

[0198] 7. The system reviews the reported data;

[0199] 7.1) The system generates data audit rules (SQL query statements) based on the template corresponding to the task, and queries the database data processing table storing the reported data. The SQL content is as follows: Fig.13 shown.

[0200] 7.3) If the query result is not empty, it means that there is erroneous data. The task progress is updated to "Template verification failed". The data reporting file is deleted, the erroneous data is exported in Excel format, and feedback is given to the reporting personnel. The reporting personnel downloads the erroneous data fed back by the system, re-submits the correct data, and repeats steps 6.3, 6.4, 7.1, and 7.2. Fig.14 and Fig.15 shown.

[0201] 7.4) If the query result is empty, it means that the reported data is correct, and the task progress is updated to "Under review by administrator". The information is fed back to the data reviewer to remind him to conduct manual review.

[0202] 8. The administrator enters the data review module of the system, downloads the data file approved by the system, and manually reviews the reported data. In this example, let task 12132035090 fail the review first.

[0203] 8.1) If the review fails, the task progress will be updated to "Administrator review rejected". Fig.16 shown.

[0204] 8.2) The data reporter re-reports the data and repeats steps 6, 7, and 8.

[0205] 8.3) After the review is passed, the reported data file is parsed and the reported data is stored in the database data storage table corresponding to the template. The task progress is updated to "data reporting successful". Fig.17 As shown, at this time, the table data of the database data storage table is as follows Fig.18 shown.

[0206] 9. Export all data in the database data storage table corresponding to the template, such as Fig.19 shown.

[0207] 10. When the administrator deletes the task record and template file, the system clears the relevant files and data, such as Fig. 20 and Fig.21 shown.

[0208] In summary, this embodiment provides a method and SaaS system for multi-user data reporting and aggregation, which can be used for multi-user data reporting, aggregation, and analysis in the process of transportation statistics to overcome the problems of cumbersome data reporting task distribution and data aggregation, confusing reporting data formats, and heavy workload of data review management personnel.

[0209] This embodiment also provides a system for multi-user data reporting and aggregation, including:

[0210] at least one processor;

[0211] at least one memory for storing at least one program;

[0212] When the at least one program is executed by the at least one processor, the at least one processor implements the following Figure 1 Steps shown.

[0213] A system for multi-user data reporting and aggregation of this embodiment can execute a method for multi-user data reporting and aggregation provided by the method embodiment of the present invention, can execute any combination of implementation steps of the method embodiment, and has the corresponding functions and beneficial effects of the method.

[0214] In some selectable embodiments, the function / operation mentioned in the block diagram may not occur in the order mentioned in the operation diagram. For example, depending on the function / operation involved, the two boxes shown in succession can actually be executed substantially simultaneously or the boxes can sometimes be executed in reverse order. In addition, the embodiment presented and described in the flow chart of the present invention is provided by way of example, for the purpose of providing a more comprehensive understanding of technology. The disclosed method is not limited to the operation and logic flow presented herein. Selectable embodiments are expected, wherein the order of various operations is changed and the sub-operation of a part for which is described as a larger operation is performed independently.

[0215] In the above description of this specification, the description with reference to the terms "one embodiment / example", "another embodiment / example" or "certain embodiments / examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner.

[0216] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the claims and their equivalents.

[0217] The above is a specific description of the preferred implementation of the present invention, but the present invention is not limited to the above embodiments. Those skilled in the art may make various equivalent modifications or substitutions without violating the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of this application.

Claims

1. A method for reporting and collecting multi-user data, It is characterized in that The following steps are involved: Obtain the template file uploaded by the administrator, parse the template file, and create a database table; Obtaining the account information selected by the administrator, and sending the data upload information to the account corresponding to the account information according to the account information, so that the data reporting personnel corresponding to the account information download the template file and upload the reported data; Obtain the submitted data uploaded by the data reporting personnel, conduct a program review on the submitted data, and if the review is passed, store the submitted data according to the database table to realize the collection of file data; The method for multi-user data reporting and collection includes a rule expression method using character strings and oriented to the legitimacy control of relational data fields: The contents of the template file include: data column alias, data column storage type, data column name 1, description of whether it is required, data column format requirements, data column name 2, detailed description of data filling, and data filling examples. The following is a description of each content:

1. Data column alias is the English alias of the column data, which is the same as the field name of the database table storing the column data; 2. The data column storage type is the same as the field data type of the database table storing the data in the column. It has nothing to do with the content and format of the data in the column. It is stipulated to facilitate data verification of the submitted data. There are three data types in this method: "varchar(255)", "varchar(50)" and "varchar(20)"; 3. Data column name 1 is the Chinese meaning of the data in this column. Data column name 1 is the same as data column name 2.

4. Whether the column data is required or not; 5. Data column format restrictions refer to the content and format requirements of the data in this column. They correspond to the data column storage type. When the data column storage type is "varchar(255)", there are two types of data column format restrictions. One is to indicate the minimum character length and the maximum character length, and the other is "ComboBox()", which means that the data in this column is limited to the value in the drop-down box. The options in the drop-down box are in brackets. When the data column storage type is "varchar(50)", the data column format restrictions refer to the number of decimal places, the minimum value and the maximum value. When the data column storage type is "varchar(20)", the data column format restrictions refer to the minimum value and the maximum value of the data value.

6. Detailed description of data filling, including whether the data is required and the data column format restrictions; 7. Data filling example, two lines of sample data; The method for multi-user data reporting and collection includes a rule parsing method using character strings and oriented to the legitimacy control of relational data fields: Parse and store the relevant information of each data column in the template file into a preset database table for subsequent creation of a database table for temporary storage of submitted data and for the generation of data review rules; The database tables include: The database table modelconcat is used to store the basic content of each data template after parsing; the fields of the database table modelconcat include: The database table limit_sum is used to store the data column drop-down box option content after each data template is parsed; the fields of the database table limit_sum include: Parse the template file, parse the data column alias, data column storage type, data column name, whether the description is required, and data column format requirements, and store them in the database tables modelconcat and limit_sum. The parsing steps are as follows:

1. Data column alias, stored in "dataName" of database table modelconcat; 2. Data column storage type, stored in "dataRequireType" of database table modelconcat; 3. The data column name is stored in "dataDescribe" of the database table modelconcat; 4. Whether the description is required is stored in "dataRequireNull" of the database table modelconcat; 5. Data column format requirements, parsing method is as follows: 1) Determine whether the data column format restriction contains "ComboBox". If so, store the options in the drop-down box in the ComboBox brackets into the database table limit_sum; In the database table limit_sum, the template number is stored as "modelName", the data column alias is stored as "limit_code", and the drop-down box option content is stored in "limit_content"; Store the data column alias in the "dataLimit" of the database table modelconcat. The other constraints are set to 0 by default. If none exists, the "dataLimit" of the database table modelconcat is "None", and proceed to the next step. 2) Determine whether the data column storage type is "varchar(255)". If so, parse the data column format restrictions into the minimum and maximum character lengths, and store them in the "minLengths" and "maxLengths" of the database table modelconcat respectively. The other constraints are set to 0 by default. If not, proceed to the next step. 3) Determine whether the data column storage type is "varchar(50)". If so, parse the data column format restrictions into the decimal places, minimum value and maximum value of the value in sequence, and store them in the "decimalCount", "minCount" and "maxCount" of the database table modelconcat respectively. The other constraints are set to 0 by default. If not, proceed to the next step. 4) The data column storage type is "varchar(20)". The data column format restrictions are parsed into the minimum and maximum values ​​of the values, and stored in the "minCount" and "maxCount" of the database table modelconcat respectively. The other constraints are set to 0 by default.

2. A method for multi-user data reporting and collection according to claim 1, It is characterized in that The steps to create a database table to store reporting data include: According to the template number of the template file, query the database table modelconcat to obtain relevant information of all data columns of the template file; Use the template file number as the table name and create a database table according to the alias and storage type of each data column; The database table includes a data processing table and a data storage table. The data processing table is used to temporarily store the data reported using the data template. The system data verification is performed in the data processing table; the data storage table is used to store and collect all the data successfully reported using the data template.

3. A method for multi-user data reporting and collection according to claim 1, It is characterized in that The procedural review of the submitted data includes: According to the template number of the template file, query the database table modelconcat to obtain relevant information of all data columns of the template file; Based on the obtained data column related information, an SQL query statement is obtained to query data rows that do not meet the data column data content and format requirements; Query the data processing table corresponding to the database table according to the obtained SQL query statement; If the query result is empty, the review is considered to be passed, and the first prompt information is generated and sent to the corresponding account of the administrator; If the query result is not empty, the review is judged to be unsuccessful, and a second prompt message is generated and sent to the corresponding account of the person reporting the data.

4. A method for multi-user data reporting and collection according to claim 3, It is characterized in that The design steps of the SQL query statement are:

1. Determine whether the "dataRequireNull" of the data column is "No requirement". If so, it means that the data in this data column has no mandatory requirements. The SQL design is: "If the data in this field is empty, no judgment is made. If the data in this field is not empty, the SQL judgment in the following steps is spliced". If not, it means that the data in this data column has mandatory requirements. The SQL design is: "If the data in this field is empty, query the rows with empty data in this field. If the data in this field is not empty, the SQL judgment in the following steps is spliced".

2. Determine whether the "dataLimit" of the data column is "None". If it is "None", proceed to the next step; if not, it means that the data in this column is limited to the value in the drop-down box. The spliced ​​SQL content is: "Query the database table limit_sum according to the template number and the alias of the data column, obtain the drop-down box option content of the data column in the template, and query the rows whose data in this field does not belong to the drop-down box option content"; 3. Determine whether the "dataRequireType" of the field is "varchar(255)". If not, proceed to the next step. If so, it means that the data in this column only needs to determine the character length. The concatenated SQL content is: "Query the rows whose character length is less than "minLengths" or greater than "maxLengths"".

4. Determine whether the "dataRequireType" of the field is "varchar(50)". If not, proceed to the next step. If so, it means that the data in this column only needs to determine the value and the number of decimal places. The concatenated SQL content is: "Query the rows whose value is less than "minCount" or greater than "maxCount" or whose number of decimal places is not "decimalCount""; 5. Determine whether the "dataRequireType" of the field is "varchar(20)". If so, it means that the data in this column only needs to determine the value. The concatenated SQL content is: "Query the rows whose values ​​are less than "minCount" or greater than "maxCount"".

5. A method for multi-user data reporting and collection according to claim 3, It is characterized in that The SQL query result in the program audit is not empty, indicating that there is erroneous data, and also includes the following steps: Export the erroneous data in Excel format, keep the current task node in the account corresponding to the data reporter, and remind the data reporter to resubmit the data.

6. A method for multi-user data reporting and collection according to claim 1, It is characterized in that The step of obtaining the template file uploaded by the administrator includes: Match the administrator's username and password and enter the data review system according to the account type; Get the template file that the administrator needs to upload and the template number entered; The audit system determines whether there is a database table named with the template number in the database. If not, uploading is allowed. The audit system uploads the template file to the server and stores the file name as the original template file name; The audit system stores the template file upload record in the database table modelfile; the template file upload record includes the template file name, the template file uploader, the template upload time, and the template number.

7. A method for multi-user data reporting and collection according to claim 1, It is characterized in that It also includes steps for administrators to delete task records and template files: When the administrator deletes the selected task record, the system deletes the task record, and deletes the reporting data file and error data file related to the task; The administrator deletes the selected template file. Before deleting, it is determined whether there is a reporting task for the template. If not, deletion is allowed. The system clears the template upload record, drop-down box alternative content and template information record of the template in the database table, deletes the database data processing table and data storage table, and deletes the template file and template summary data export file on the server.

8. A system for multi-user data reporting and collection, It is characterized in that include: at least one processor; at least one memory for storing at least one program; When the at least one program is executed by the at least one processor, the at least one processor implements the method according to any one of claims 1 to 7.

Images