Methods, systems, equipment, and media for aggregate analysis of income and expenditure in engineering contracts
By grouping and aggregating contract records, obtaining approved payment records, selecting the latest cumulative output value, and using the output value after outsourcing or the contract amount as the benchmark, the problem of accurate summarization and ratio calculation of contract data is solved, and the accuracy and consistency of engineering contract data are achieved.
Patent Information
- Application Number
- CN202611447069.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-09-17
- Publication Date
- 2026-11-17
AI Technical Summary
In application scenarios where a contract corresponds to multiple sub-projects and each sub-project reports its cumulative output value over a period of time, existing technologies cannot directly determine the current effective cumulative output value of each project. This leads to repeated accumulations in historical periods and inconsistencies in payment data, affecting the accurate summarization of contract data and ratio calculations.
The computer system groups and aggregates contract records, obtains approved payment records, groups them according to contract and project identifiers, selects the latest cumulative output value record, uses the output value after outsourcing or the contract amount as the calculation benchmark, calculates the completion rate and payment rate, and judges the contract status according to a preset order to form a unified data object.
It achieves accurate aggregation of contract data, reduces interference from duplicate accumulations and ineffective payments during historical periods, ensures consistency in data dependencies for ratio calculation and status assessment, and supports unified data representation for full-cycle tracking and annual statistics.
Smart Images

Figure CN123118030A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of engineering project management data processing technology, and in particular to a method, system, equipment and medium for aggregated analysis of income and expenditure in engineering contracts. Background Technology
[0002] Engineering general contracting and construction companies typically use engineering contract management systems to record business data such as contracts, output value of sub-projects, and payments received. Based on this data, they generate statistical results on contract execution for use in project management analysis.
[0003] Chinese patent application CN107016514A, entitled "A Contract Management Method and System Based on Integrated Contract Information and Related Information," discloses a contract management approach: Contracts and related information are compiled through multiple business systems. Data related to the planning, tendering, changes, payments, claims, and settlements before and after contract formation are stored in a data warehouse. The data is then linked together by contract number and aggregated into a single view. This approach centrally presents contract lifecycle information and supports access to and approval of contract management information by authorized portals.
[0004] However, in application scenarios where a contract corresponds to multiple sub-projects and each sub-project reports its cumulative output value over a period, simply aggregating related data by contract number makes it difficult to directly determine the current effective cumulative output value of each project and form an accurate summary at the contract level. Furthermore, directly summing multiple periods' cumulative records for the same project can easily lead to duplicate accumulation of historical periods. Simultaneously, payment data may include records that have not yet been approved and taken effect, and the statistical benchmarks for output value and payment may differ. Therefore, the aggregated contract data cannot directly generate consistent and comparable completion rates, payment collection rates, and risk status results. Moreover, when mixing full-cycle cumulative data with data from the current calendar year, it is difficult to simultaneously consider both full-cycle project tracking and annual statistics.
[0005] Therefore, how to deterministically aggregate scattered output value and payment records under data constraints such as contracts, projects, periods, and approval status, and form data results that can be used for subsequent status judgment and unified output based on the same calculation benchmark, is a technical problem that needs to be solved in the field of engineering contract data processing. Summary of the Invention
[0006] This invention addresses the technical problems existing in the prior art by proposing a method, system, equipment, and medium for aggregated analysis of income and expenditure in engineering contracts.
[0007] The technical solution of the present invention to solve the above-mentioned technical problems is as follows:
[0008] Firstly, a method for aggregated analysis of revenue and expenditure in engineering contracts is proposed, executed by a computer system. This method includes: acquiring undeleted contract records and a set of contract identifiers corresponding to the contract records; acquiring output value records and payment records with an approved status based on the contract identifier set; wherein the contract records include a contract amount and a nullable post-outsourcing output value field; grouping the output value records according to contract identifiers and project identifiers; sorting the output value records within each group in descending order by period and selecting the first output value record as the latest cumulative output value record for the corresponding project; summing the cumulative output values in the latest cumulative output value records for each project according to the contract identifier to obtain the cumulative output value of each contract; grouping the payment records with an approved status according to contract identifiers and summing the payment amounts to obtain the cumulative payment for each contract; for each contract, when the post-outsourcing output value field is not null, determining the post-outsourcing output value as the calculation benchmark value; when the post-outsourcing output value field is null, determining the contract amount as the calculation benchmark value; and so on. Calculate the completion rate and follow Calculate the collection rate, where, Indicates completion rate. Indicates cumulative output value. This represents the base value for calculation. Indicates the collection rate. The system represents the cumulative amount received. Following a preset judgment order, when the completion rate is greater than or equal to 100% and the cumulative amount received is less than the contract amount, the contract status is determined to be a preset overdue payment status label. If the aforementioned conditions are not met, the outsourced output value field is not empty, the cumulative amount received is less than the outsourced output value, and the cumulative output value is greater than zero, the contract status is determined to be an accounts receivable status label. If none of the aforementioned conditions are met, the contract status is determined to be a normal status label. The contract record, cumulative output value, cumulative amount received, completion rate, payment collection rate, and contract status are assembled into a unified data object and output.
[0009] Furthermore, a contract record query task is first executed to obtain the contract records and the contract identifier set. Then, a production value query task and a payment record query task with the contract identifier set as the query condition are executed in parallel. After both the production value query task and the payment record query task are completed, aggregation is performed in memory with the contract identifier as the association key.
[0010] Furthermore, the periods for each output value record corresponding to the same contract identifier and project identifier are different. The period is represented by a fixed-length year and month string, which includes a four-digit year, a connector, and a two-digit month. The month is one of 01 to 12. The output value records in each group are arranged in descending order according to the string values of the year and month strings, and the cumulative output value recorded in the first output value record after the arrangement is taken as the current cumulative output value of the corresponding project.
[0011] Furthermore, it also includes: selecting output value records from the output value records that belong to the current calendar year, summing the current period output values in the selected output value records to obtain the current year output value; selecting payment records from the payment records whose approval status is approved that have payment dates belonging to the current calendar year, summing the payment amounts in the selected payment records to obtain the current year payment.
[0012] Furthermore, engineering dimension data including contract identifiers and net output value of the project are obtained. The engineering dimension data is grouped according to the contract identifiers, and the net output value of each project corresponding to the same contract identifier is summed to obtain the outsourced output value. The outsourced output value is used to represent the actual settlementable output value baseline of the corresponding contract.
[0013] Furthermore, when the calculation benchmark value is zero or the cumulative output value is empty, the completion rate is set to an empty value; when the calculation benchmark value is zero or the cumulative receipt is zero, the payment receipt rate is set to an empty value, and the non-empty completion rate and payment receipt rate are retained to one decimal place.
[0014] Furthermore, the unified data object also includes contract number, contract name, project information, current year output value, and current year receipts. The amount field in the unified data object is converted from yuan to ten thousand yuan, and the date field is converted into a fixed-length string that includes a four-digit year, a connector, a two-digit month, a connector, and a two-digit date. A null value field is reserved for the display terminal to output a placeholder identifier.
[0015] Secondly, a system for aggregating and analyzing revenue and expenditure in engineering contracts is provided, comprising a data acquisition module, a production value aggregation module, a payment aggregation module, a ratio calculation module, a status judgment module, and a data output module. The data acquisition module acquires undeleted contract records, production value records, and payment records with an approved status. The contract records include a contract amount and a nullable subcontracted production value field. The production value aggregation module groups the production value records according to contract identifiers and project identifiers, sorts the production value records within each group in descending order by period, selects the first production value record as the latest cumulative production value record for the corresponding project, and then summarizes the cumulative production value of each project according to the contract identifier. The payment aggregation module summarizes the payment records with an approved status according to the contract identifier. The ratio calculation module calculates the ratio based on the subcontracted production value field when it is not null. The post-outsourcing output value is used as the calculation benchmark value. When the post-outsourcing output value field is empty, the contract amount is used as the calculation benchmark value. The completion rate is calculated based on the cumulative output value and the calculation benchmark value, and the collection rate is calculated based on the cumulative receipts and the calculation benchmark value. The status judgment module is used to determine the contract status as a preset overdue payment status label when the completion rate is greater than or equal to 100% and the cumulative receipts are less than the contract amount, according to a preset judgment order. When the aforementioned conditions are not met, the post-outsourcing output value field is not empty, the cumulative receipts are less than the post-outsourcing output value, and the cumulative output value is greater than zero, the contract status is determined as an accounts receivable status label. When none of the aforementioned conditions are met, the contract status is determined as a normal status label. The data output module is used to assemble the contract records, aggregated indicators, ratio results, and contract status into a unified data object and output it.
[0016] Thirdly, an electronic device is provided, including a processor and a memory, wherein the memory stores a computer program, and when the computer program is executed by the processor, the processor performs the engineering contract income and expenditure aggregation analysis method as described in any one of the first aspects.
[0017] Fourthly, a computer-readable storage medium is provided, on which a computer program is stored, wherein the computer program, when executed by a processor, implements the method for aggregate analysis of income and expenditure in engineering contracts as described in any one of the first aspects.
[0018] Compared with the prior art, the beneficial effects of the present invention are as follows:
[0019] To address the issue that existing contract data collection methods struggle to directly generate accurate cumulative contract values in multi-project, multi-period scenarios, this invention first groups output value records according to contract identifiers and project identifiers. For each project, only the latest cumulative output value record determined by the period is retained, followed by a secondary aggregation based on contract identifiers. Therefore, the calculation of cumulative contract output value uses the current cumulative status of each project, rather than all historical cumulative records for the same project, which helps reduce data discrepancies caused by duplicate accumulations during historical periods.
[0020] This invention only includes payment records with an approved status into the aggregation of cumulative payments and current year payments, and puts output value data, payment data and contract data into the same processing chain through contract identifiers. Therefore, it can reduce the interference of ineffective payments on statistical results, and make subsequent ratio calculations and status judgments based on the data set with determined status.
[0021] This invention uses the same calculation benchmark for completion rate and payment collection rate, and determines the benchmark according to the rule of prioritizing output value after outsourcing and reserving contract amount. Simultaneously, it performs the judgment of overdue payments, accounts receivable, and normal status in a predetermined order. Through this benchmark transfer and judgment order, a definite data dependency relationship can be maintained between the ratio results and the status results, and the risk of incomparable results caused by different calculation methods used for different contracts can be reduced.
[0022] This invention aggregates the cumulative value over the entire period and the value for the current natural year, enabling the same unified data object to simultaneously carry contract lifecycle tracking data and natural year statistical data, without having to use the same field to represent the two time calibers.
[0023] This invention first obtains the set of undeleted contract records and contract identifiers, then executes the output value record query and the approved payment record query in parallel, and performs grouping and association in memory after both are completed, thereby reducing the waiting time between two independent queries while maintaining clear query dependencies. By distinguishing between empty fields, zero values, and empty output values, and performing preset boundary processing on cases where the denominator is zero, the cumulative output value is empty, and the cumulative payment is zero, it can also avoid producing ratio results that have no practical meaning. Attached Figure Description
[0024] Figure 1 This is a schematic diagram of the data flow for the aggregated analysis of income and expenditure in engineering contracts of this invention;
[0025] Figure 2 This is a schematic diagram of the algorithm structure for the aggregation of income and expenditure in engineering contracts according to the present invention;
[0026] Figure 3 This is a schematic diagram illustrating the decision-making process for determining the contract status in this invention.
[0027] Figure 4 This is a schematic diagram of the field structure of the unified data object of the present invention. Detailed Implementation
[0028] The present invention will be further described below with reference to the accompanying drawings and embodiments. It should be noted that the following embodiments are used to illustrate the technical solution of the present invention and are not intended to limit the scope of protection of the present invention. Unless otherwise specified, the contract identifier corresponds to the ContractId field in the contract table used to uniquely identify the contract, and the project identifier corresponds to the EngineeringId field in the project dimension data used to uniquely identify the project. The Period is represented by a fixed-length string "yyyy-MM", where yyyy is a four-digit year and MM is a two-digit month from 01 to 12. The periods of each output value record corresponding to the same contract identifier and project identifier are different. Therefore, the result of sorting the Period in descending order by string is consistent with the result of sorting it in descending order by time by year and month.
[0029] In this implementation, contract data includes at least a contract identifier, contract number, contract name, contract amount, and a nullable field for storing the outsourced output value. Project-level data includes at least a contract identifier, project identifier, and net project output value. Output value data includes at least a contract identifier, project identifier, period, current period output value, and cumulative output value. Payment data includes at least a contract identifier, payment date, payment amount, and approval status. For each contract, project-level data is grouped according to the contract identifier, and the net project output value of each project under that contract is summed. The summation result is written to the outsourced output value field of the corresponding contract record as the baseline for calculating the actual settleable output value of the contract. One contract can correspond to multiple projects, and each project can correspond to multiple output value records for different periods.
[0030] Example 1: Method for Aggregating Income and Expenditure in Engineering Contracts
[0031] like Figure 1 As shown, in this embodiment, the data flow enters the aggregation processing flow from the contract table, output value table, and payment collection table. After output value aggregation, payment collection aggregation, ratio calculation, and status judgment, a unified data object is formed. The method is executed by a computer system and includes the following steps.
[0032] Step S1: Obtain the data to be processed. The controller's GetList method first queries all non-deleted contract records in the contract table, sorts them by contract number, and obtains the contract identifier set from the query results. After the contract record query is completed, the production value record query task and the payment record query task are initiated in parallel, using the contract identifier set as a common query condition. The production value record query task retrieves the production value records from the production value table within the contract identifier set; the payment record query task retrieves the payment records from the payment table within the contract identifier set whose approval status is approved, so that payment records that have not yet been approved and taken effect are not included in subsequent statistics. After both query tasks are completed, the processor loads the contract records, production value records, and payment records into memory and continues to execute aggregation using the contract identifier as the association key. Thus, the two queries that depend on the contract identifier set are started after the contract record query is completed, avoiding the execution of queries that depend on the set before the contract identifier set has been formed.
[0033] Step S2: Calculate the cumulative output value of the contract. First, group the obtained output value records according to the grouping key composed of the contract identifier and the project identifier. In each group, sort the output value records in descending order according to the string value of the period, and select the first output value record after sorting as the latest cumulative output value record for the corresponding project. Since the period uses a fixed-length "yyyy-MM" format, the year is fixed at four digits and the month is fixed at two digits. The year and month at the beginning of the string are arranged from high to low digits. Therefore, the period with a larger string value corresponds to the newer month. The periods of each output value record in each group are different. Therefore, the first record after sorting in descending order is the unique latest month record for the project, and its cumulative output value field is the current cumulative output value of the project.
[0034] For contracts marked as The contract, when summed up the latest cumulative output value of each project under the contract, can be expressed as:
[0035]
[0036] In the formula, Indicates contract The corresponding project set, Indicates contract Next project The latest cumulative output value, Indicates contract The cumulative output value. The above process of first taking the latest cumulative status by project and then summarizing by contract ensures that the historical cumulative output value record of the same project is not repeatedly added to the contract cumulative value. Figure 2 The algorithm structure, including the aforementioned output aggregation, is shown.
[0037] When the contract When there is no corresponding output value record, the summation of the empty set is not performed; instead, the cumulative output value field of the contract is left empty. When the contract has an output value record but the corresponding cumulative output value field is zero, the cumulative output value is left as zero. This distinction, in conjunction with the ratio boundary processing in step S5, ensures that "no output value data" and "cumulative output value is zero" maintain different semantics within the unified data object.
[0038] Step S3: Calculate the current year's output value. Filter records from all output value records that fall within the current calendar year, and sum the current period's output value for the filtered records, without summing the cumulative output value field. For contracts... The output value for this year can be expressed as:
[0039]
[0040] In the formula, Indicates contract The set of output records for the current calendar year. Representing records The current output value, Indicates contract The current year's output value. Therefore, the current year's output value and the total cumulative output value obtained in step S2 are calculated independently.
[0041] Step S4: Calculate cumulative receipts and current year receipts. Group the receipt records with the "approved" status from Step S1 according to contract identifiers, and sum the receipt amounts for each group to obtain the cumulative receipts for the corresponding contracts. Further, filter these approved receipt records for records whose receipt dates belong to the current calendar year, and sum the sum of the filtered receipt amounts to obtain the current year's receipts. For contracts... This year's receipts can be represented as:
[0042]
[0043] In the formula, Indicates contract The set of payment records within the current calendar year that are in the approved status. Representing records The amount received, Indicates contract This year's revenue.
[0044] Step S5: Calculate the completion rate and payment collection rate. For each contract, first determine the calculation benchmark value. The term "existence of output value after outsourcing" means that the "output value after outsourcing" field in the contract record is not null; the term "absence of output value after outsourcing" means that the field is empty. When the field is not null, its value is used as the calculation base value; when the field is empty, the contract amount is used as the calculation base value. A field that is not null but has a value of zero has a different meaning than a field that is empty: in the former case, the output value after outsourcing is still selected as the base, and the null ratio is output by the division-by-zero boundary rule described later; in the latter case, the contract amount is used as the base value. Indicates the cumulative output value of the contract, in This indicates the cumulative amount received under the contract and the completion rate. and the rate of receipt of payment Calculate according to the following formulas respectively:
[0045]
[0046]
[0047] When the calculation baseline value is zero or the cumulative output value is empty, the completion rate is set to an empty value; when the calculation baseline value is zero or the cumulative receipts are zero, the payment collection rate is set to an empty value. An empty value here means that no numerical ratio is output, and is not equivalent to a zero value; when the cumulative receipts are zero, the zero value of the cumulative receipts field is retained, but the payment collection rate field outputs an empty value. For non-empty completion rates and payment collection rates, the percentage values are rounded to one decimal place. By having the completion rate and payment collection rate reference the same calculation baseline value, the denominator source for the two ratios remains consistent.
[0048] Step S6: Determine the contract status. For example... Figure 3 As shown, the status judgment adopts a preset rule judgment logic with a sequential order. First, it judges whether the completion rate is greater than or equal to 100% and whether the cumulative receipts are less than the contract amount; if both are met, the contract status is determined to the preset "overdue payment" status label. Only when this condition is not met, it continues to judge whether the outsourced output value field is not empty, whether the cumulative receipts are less than the outsourced output value, and whether the cumulative output value is greater than zero; if all three are met, the contract status is determined to the "accounts receivable" status label. If neither of the above two branches is met, the contract status is determined to the "normal" status label. When the completion rate is empty, the first judgment condition is considered not met and the second judgment continues to be executed; when the cumulative output value is empty, it does not meet the condition that the cumulative output value is greater than zero. "Overdue payment" in this embodiment is a preset label output by the above aggregation rule, used to identify the data status where the completion rate has reached 100% but the cumulative receipts are still less than the contract amount, and is not calculated separately based on the payment due date. This judgment logic can be implemented in the aggregation controller as a pure function. That is, when the input completion rate, cumulative receipts, contract amount, outsourced output value and cumulative output value are the same, the output will be a definite contract status, without relying on an additional prediction model.
[0049] Step S7: Assemble and output a unified data object. For example... Figure 4 As shown, the unified data object can be in SheetRowDto format, with fields including contract number, contract name, project information, contract amount, output value after outsourcing, cumulative output value, current year's output value, cumulative receipts, current year's receipts, completion rate, payment receipt rate, and contract status. The amount field is converted from yuan to ten thousand yuan by dividing by 10000, and the date field is converted to a fixed-length string "yyyy-MM-dd", where yyyy is the four-digit year, MM is the two-digit month, and dd is the two-digit date. Nullable fields such as output value after outsourcing, completion rate, and payment receipt rate are left empty for placeholder display. Non-empty fields with a value of zero are still output as zero and are not replaced with empty values. After processing, a list of unified data objects is returned in contract number order.
[0050] Example 2: Completion Rate and Payment Collection Rate Calculation
[0051] A contract has a contract amount of 5 million yuan, the output value after outsourcing is 4 million yuan, the cumulative output value is 3 million yuan, and the cumulative receipts are 2 million yuan. Due to the existence of output value after outsourcing, a benchmark value is calculated. Take 4 million yuan. Based on the formula in Example 1, the completion rate is:
[0052]
[0053] The collection rate is:
[0054]
[0055] The contract completion rate is less than 100%, which does not meet the conditions for overdue payment. The contract involves output value after outsourcing. The cumulative payment of 2 million yuan is less than the output value after outsourcing of 4 million yuan, and the cumulative output value of 3 million yuan is greater than zero. Therefore, the contract status is determined to be accounts receivable.
[0056] Example 3: Identification of Overdue Payments
[0057] The other contract has a contract value of 5 million yuan, with an outsourced output value of 4 million yuan, a cumulative output value of 4 million yuan, and cumulative receipts of 1 million yuan. Using a base value of 4 million yuan, the completion rate is:
[0058]
[0059] If the completion rate is greater than or equal to 100%, and the cumulative amount received is 1 million yuan less than the contract amount of 5 million yuan, then the condition of overdue payment is met first, the contract status is determined to be overdue payment, and it will no longer enter the accounts receivable branch.
[0060] Example 4: Cumulative Output Value of Multiple Engineering Contracts
[0061] A contract includes Project A, Project B, and Project C. After grouping by contract identifier and project identifier, the output value records for the three projects are sorted in descending order by period. The latest cumulative output values are selected as 1 million yuan, 1.5 million yuan, and 800,000 yuan respectively. The total cumulative output value of the contract is:
[0062]
[0063] The outsourced output value of this contract is 5 million yuan, therefore the base value for calculation is 5 million yuan, and the completion rate is:
[0064]
[0065] If all cumulative output value records under a contract are summed directly without first distinguishing between projects and periods, cumulative values from different historical periods for the same project may be added to the result repeatedly. The two-level grouping process in Example 1 ensures that each project contributes only one latest period cumulative output value record.
[0066] Example 5: Engineering Contract Income and Expenditure Aggregation Analysis System
[0067] This embodiment provides an engineering contract revenue and expenditure aggregation analysis system, including a data acquisition module, an output value aggregation module, a payment aggregation module, a ratio calculation module, a status judgment module, and a data output module.
[0068] The data acquisition module executes step S1 of Example 1, first obtaining the set of undeleted contract records and contract identifiers, then simultaneously obtaining the corresponding output value records and payment records with an approved status, and outputting the three types of records associated with the contract identifiers in memory to subsequent modules. The output value aggregation module executes steps S2 and S3, grouping by contract identifier and project identifier, determining the latest cumulative output value record for each project, summarizing the cumulative output value of contracts, and calculating the output value for the current year. The payment aggregation module executes step S4, summarizing the cumulative payments and payments for the current year. The ratio calculation module executes step S5, reading the nullable outsourced output value field and contract amount from the contract records, determining the calculation benchmark value, and calculating the completion rate and payment collection rate. The status judgment module executes step S6, determining the contract status label in the order of overdue payments, accounts receivable, and normal. The data output module executes step S7, assembling the contract information, aggregation indicators, ratio results, and contract status into a unified data object and outputting it. Each module forms a sequential data stream through the aforementioned intermediate results, with the outputs of the output value aggregation module and the payment aggregation module serving as inputs to the ratio calculation module and the status judgment module.
[0069] Example 6: Electronic Equipment
[0070] This embodiment provides an electronic device, including a processor and a memory. The memory stores a computer program and storage space for storing query results and intermediate aggregation results. When the processor executes the computer program, it sequentially performs the steps described in Embodiment 1: data acquisition, output value aggregation, payment aggregation, ratio calculation, status judgment, and unified data object output. After completing the contract record query, the processor generates a contract identifier set, schedules the output value record query task and the payment record query task in parallel, and continues to execute memory aggregation after both are completed. When the processor performs formula calculations, it uses the values defined in Embodiment 1. , , , and The meaning and boundary rules are as follows. This embodiment does not limit the specific hardware models of the processor and memory.
[0071] Example 7: Computer-readable storage medium
[0072] This embodiment provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, it implements the engineering contract revenue and expenditure aggregation analysis method described in Embodiment 1, including associating three types of business data with contract identifiers, determining cumulative output value by grouping by contract and project levels, summarizing payments with an approved status, calculating completion rate and payment receipt rate, determining contract status according to a preset judgment order, and outputting a unified data object.
[0073] The above embodiments use the constraint that the periods corresponding to the same contract identifier and project identifier are different for each output value record, and use fixed-length period strings, fixed query dependency order, and explicit null value semantics to ensure that the aggregation result can be reproducibly determined. Parallel scheduling can be implemented by an asynchronous task mechanism supported by electronic devices, and its specific framework does not affect the data dependency relationship of first obtaining the set of contract identifiers, then obtaining the output value records and approved payment records in parallel, and finally performing in-memory aggregation.
Claims
1. A method for aggregating and analyzing revenue and expenditure in engineering contracts, executed by a computer system, characterized in that: include: Obtain the undeleted contract records and the corresponding set of contract identifiers. Based on the set of contract identifiers, obtain the output value records and payment records with an approved status. Each contract record includes a contract amount and a nullable post-outsourcing output value field. Group the output value records according to contract identifiers and project identifiers. Sort the output value records within each group in descending order by period and select the first output value record as the latest cumulative output value record for the corresponding project. Then, sum the cumulative output values in the latest cumulative output value records for each project according to the contract identifier to obtain the cumulative output value of each contract. Group the payment records with an approved status according to contract identifiers and sum the payment amounts to obtain the cumulative payment for each contract. For each contract, if the post-outsourcing output value field is not null, determine the post-outsourcing output value as the calculation benchmark value; if the post-outsourcing output value field is null, determine the contract amount as the calculation benchmark value. Calculate the completion rate and follow Calculate the collection rate, where, Indicates completion rate. Indicates cumulative output value. This represents the base value for calculation. Indicates the collection rate. The system represents the cumulative amount received. Following a preset judgment order, when the completion rate is greater than or equal to 100% and the cumulative amount received is less than the contract amount, the contract status is determined to be a preset overdue payment status label. If the aforementioned conditions are not met, the outsourced output value field is not empty, the cumulative amount received is less than the outsourced output value, and the cumulative output value is greater than zero, the contract status is determined to be an accounts receivable status label. If none of the aforementioned conditions are met, the contract status is determined to be a normal status label. The contract record, cumulative output value, cumulative amount received, completion rate, payment collection rate, and contract status are assembled into a unified data object and output.
2. The method for aggregated analysis of income and expenditure in engineering contracts according to claim 1, characterized in that, First, a contract record query task is executed to obtain the contract records and the contract identifier set. Then, a production value query task and a payment record query task, which use the contract identifier set as the query condition, are executed in parallel. After both the production value query task and the payment record query task are completed, aggregation is performed in memory using the contract identifier as the association key.
3. The method for aggregated analysis of income and expenditure in engineering contracts according to claim 1, characterized in that, The periods for each output value record corresponding to the same contract identifier and project identifier are different. The period is represented by a fixed-length year and month string. The year and month string includes a four-digit year, a connector, and a two-digit month. The month is one of 01 to 12. The output value records in each group are arranged in descending order according to the string values of the year and month strings, and the cumulative output value recorded in the first output value record after the arrangement is taken as the current cumulative output value of the corresponding project.
4. The method for aggregated analysis of income and expenditure in engineering contracts according to claim 1, characterized in that, Also includes: The output value records belonging to the current calendar year are selected from the output value records, and the output value of the current period in the selected output value records is summed to obtain the output value of the current year. From the approved payment records, filter the payment records whose payment dates belong to the current calendar year, and sum the payment amounts in the filtered payment records to obtain the payments for the current year.
5. The method for aggregated analysis of income and expenditure in engineering contracts according to claim 1, characterized in that, Obtain engineering dimension data including contract identifiers and net output value of the project. Group the engineering dimension data according to contract identifiers and sum the net output values of each project corresponding to the same contract identifier to obtain the outsourced output value. Use the outsourced output value to represent the actual settlementable output value baseline of the corresponding contract.
6. The method for aggregated analysis of income and expenditure in engineering contracts according to claim 1, characterized in that, When the calculation benchmark value is zero or the cumulative output value is empty, the completion rate is set to an empty value. When the calculation benchmark value is zero or the cumulative receipt is zero, the payment receipt rate is set to an empty value. The non-empty completion rate and payment receipt rate are retained to one decimal place.
7. The method for aggregated analysis of income and expenditure in engineering contracts according to claim 1, characterized in that, The unified data object also includes contract number, contract name, project information, current year output value and current year receipts. The amount field in the unified data object is converted from yuan to ten thousand yuan, and the date field is converted into a fixed-length string that includes a four-digit year, a connector, a two-digit month, a connector and a two-digit date. The null value field is reserved for the display terminal to output placeholders.
8. A system for aggregating and analyzing revenue and expenditure in engineering contracts, characterized in that, It includes a data acquisition module, an output value aggregation module, a payment aggregation module, a ratio calculation module, a status judgment module, and a data output module; the data acquisition module is used to acquire undeleted contract records, output value records, and payment records with an approval status of "approved". The contract records include the contract amount and an empty field for output value after outsourcing. The output value aggregation module is used to group the output value records according to contract identifier and project identifier, sort the output value records in each group in descending order of period, select the first output value record as the latest cumulative output value record of the corresponding project, and then summarize the cumulative output value of each project according to the contract identifier. The payment aggregation module is used to summarize payment records with an approved status according to the contract identifier; the ratio calculation module is used to calculate the completion rate based on the cumulative output value and the calculation benchmark value when the output value field after outsourcing is not empty, and to calculate the payment collection rate based on the contract amount when the output value field after outsourcing is empty; the status judgment module is used to determine the contract status as a preset overdue payment status label when the completion rate is greater than or equal to 100% and the cumulative payment is less than the contract amount, and to determine the contract status as an accounts receivable status label when the aforementioned conditions are not met, the output value field after outsourcing is not empty, the cumulative payment is less than the output value after outsourcing and the cumulative output value is greater than zero, and to determine the contract status as a normal status label when none of the aforementioned conditions are met; the data output module is used to assemble the contract records, aggregation indicators, ratio results and the contract status into a unified data object and output it.
9. An electronic device, characterized in that, It includes a processor and a memory, wherein the memory stores a computer program, and when the computer program is executed by the processor, the processor performs the engineering contract income and expenditure aggregation analysis method as described in any one of claims 1 to 7.
10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the method for aggregate analysis of income and expenditure in engineering contracts as described in any one of claims 1 to 7.
Citation Information
Patent Citations
Contract management method and system based on integration of contract information and correlated information
CN107016514A
