Petelai project experience small set (3) ___ summarize the amount from multiple tables to one table

This project is mainly used by all departments and agents of petelai. The following function of this system is to collect agent fees. The expense statistics are stored in the expense pool (traditionally, the agent fee is called the expense pool) data table. The agent can purchase items based on the amount in the expense pool, the money in the fee pool comes from the service appraisal form filled in by the agent each time.

Let's take a look at the structure of this part of the data table:

The following storage process summarizes the cost of materials and other expenses and summarizes the storage process to the billing pool information table. This code is a bit long and adds various judgments:

 

-- ===================================================== ====== -- Author: ma zhaojuan -- create Date: 15:32:16 -- Description: the statistical material fee and other expenses are collected to the expense pool -- ============================ ===================== create procedure [DBO]. [proc_wxjd_committers] -- add the parameters for the stored procedure here @ wxjdid int, -- service identification ticket ID @ dlsid int -- Agent idasdeclare @ err1 int, -- declare the variable, the transaction uses @ err2 int, -- declares the variable, and the transaction uses @ glf decimal, -- Management Fee @ GSF decimal, -- Working Hours fee @ XJ decimal, -- subtotal @ totalmoney decimal, -- total amount @ count int, -- Integer Data @ ljglf decimal, -- Part Management Fee @ ljgsf decimal, -- Part time fee @ qtfy decimal, -- other fee @ wxjdtotalmoney decimal -- total amount beginbegin transaction -- enable transaction select @ COUNT = 0; -- assign a value to the variable -- calculate the other fees in the following example: Select @ COUNT = count (*) from t_dls_wxjd_cost_qtfy where [email protected] If @ count> 0 -- determine whether other fees have been written to other fees, next, assign in select @ XJ = case sum (XJ) when null then 0 else sum (XJ) end from t_dls_wxjd_cost_qtfy where [email protected]; endelsebegin select @ XJ = 0; end -- select @ COUNT = 0; select @ COUNT = count (*) from t_dls_wxjd_cost_clf where FID [email protected] If @ count> 0 -- determines whether the material fee table has been written to the material fee. The following value is begin select @ glf = case sum (glf) for the management fee and working hours fee) when null then 0 else sum (glf) End, @ GSF = case sum (GSF) when null then 0 else sum (GSF) end from t_dls_wxjd_cost_clf where FID [email protected] endelsebegin select @ glf = 0; select @ GSF = 0; end -- assign the following value to the total amount: Select @ totalmoney = @ XJ + @ glf + @ GSF; -- print @ totalmoney; -- The following will write the amount calculated from other fees and materials into the billing pool select @ COUNT = 0; select @ COUNT = count (*) from t_feeexist where dlsid = @ dlsid; if @ count> 0 -- determines whether the fee pool table has been written into the agent fee. If the table has been written into the agent fee, you need to add the statistical fees to the agent. Begin select @ ljglf = ljglf, @ ljgsf = ljgsf, @ qtfy = qtfy, @ wxjdtotalmoney = balance from t_feeexist where dlsid = @ dlsid; select @ [email protected] [email protected]; select @ [email protected] [email protected]; select @ [email protected] [email protected]; select @ [email protected] [email protected]; update t_feeexist set [email protected], [email protected], [email protected], [email protected] Where dlsid = @ dlsid; endelse -- add the new record begininsert into t_feeexist (dlsid, ljglf, ljgsf, qtfy, wxjdtotalmoney) values (@ dlsid, @ glf, @ GSF, @ XJ, @ totalmoney ); endset @ [email protected] @ error -- Update whether the repair table has been submitted to the billing pool Field update t_dls_wxjd set isfyc = 'is 'where [email protected] Set @ [email protected] @ Error -- determine whether an error occurs if (@ err1 = 0 and @ err2 = 0) commit transaction -- submit the transaction elserollback transaction -- transaction rollback end

The statistical fee is briefly introduced here. The key point of the record here is how to summarize the data of each table and determine the value in the stored procedure!