Calculate the salary to be paid based on attendance and performance information

A certain enterprise's finance department needs to calculate employee salaries and export the data to the bank. The calculation of salaries first needs to refer to the employee's absenteeism rate and job performance. The specific rules are:

Basic salary * (1- absenteeism rate + performance)

Then pay personal income tax. The calculation method for personal income tax is to subtract the tax-free portion of 2000 USD from the income salary, and the remaining portion is calculated by the excess accumulation method, that is, segmented taxation. The tax rate for each segment is shown in the following table:

Levels	Containing tax grade difference	Tax rate(%)	Tax reduction factor
1	≤500	5	0
2	500-2000	10	25
3	2000-5000	15	125
4	5000-20000	20	375
5	20000-40000	25	1375
6	>40000	30	3375

If an employee's income is 6000 USD, subtracting 2000 USD, the remaining 4000 USD is the taxable amount. According to the segmented tax calculation method, the tax amount is:

500*5%+1500*10%+2000*15%=475 USD

You can also use quick deduction to calculate:

4000*15%-125=475 USD

The following table is the employee information table for the company, called Employees.txt:

The following table shows the absenteeism rate of employees for this month, Absenteeism.txt. If an employee is not absent, there will be no record of that employee in the table:

The following table is the employee's performance report for this month, Performance.txt, and not all employees have performance record:

Please calculate the salary amount that each employee should receive and output the result as a string in the following format:

Company name

TOAM=Total Salary

COUT=number of employees

---------------------------------------

Bank account | Payable amount | Name

Bank account | Payable amount | Name

……

Create a new table sequence based on the employee table, retaining the employee name, basic salary, and bank account fields, and adding absenteeism rate, performance, and payroll fields. The value of the absenteeism rate field is obtained from the absenteeism rate table, while the performance is derived from the performance table. The salary field should be calculated according to the formula. For employees without absence or performance records, the absence or performance field will be null and automatically processed as 0 during calculation, which perfectly meets our requirements.

Copy the tax rate table into the code, record the minimum income for each level, and convert it into a table sequence for calculation.

Calculate individual income tax for each employee, deduct the tax exemption amount first, identify their income range, and then use the quick deduction method to calculate the tax amount, which will be deducted from their salary.

Then we can start piecing together the results, creating a sequence that includes the company name, the total amount of salary to be paid, the number of people to be paid to, and a separator line. Finally, loop through the previous table and add the records to the sequence one by one according to the format.

	A	B	C
1	=T("Employees.txt").keys(ID)	=T("Absenteeism.txt").keys(Employee)	=T("Performance.txt").keys(Employee)
2	Salary	TaxRate	TRF
3	0	5	0
4	500	10	25
5	2000	15	125
6	5000	20	375
7	20000	25	1375
8	40000	30	3375
9	=create(Salary,TaxRate,TRF).record([A3:C8],0)
10	=A1.new(Name,B1.find(ID).Absenteeism:Absenteeism,C1.find(ID).Performance:Performance,BasePay,AccountNo,BasePay*(1-Absenteeism+Performance):SalaryPayable )
11	>A10.run(ts=SalaryPayable-2000, tr=A9.segp@r(Salary,ts), SalaryPayable= round(SalaryPayable -(ts*tr.TaxRate/100-tr.TRF),2))
12	=a=["RAQSOFT INCORPORATION"]
13	>a|=("TOAM="/round(A10.sum(SalaryPayable),2))
14	>a|=("COUT="/A10.count())
15	>a|="---------------------------------------"
16	>A10.run(a|=(AccountNo/"|"/SalaryPayable/"|"/Name))
17	=a

https://try.esproc.com/splx?4fe

A1 reads the employee table, B1 reads the absenteeism rate table, and C1 reads the performance table.

Rows 2-8 copy the tax rate table, and in A9, generate the following table sequence:

A10 creates a new field structure table sequence based on the employee table, selects corresponding absenteeism and performance data, and calculates the pre-tax salary of each employee:

A10 loops the salary table, first calculates the taxable salary ts for each employee, then finds the corresponding tax rate level tr, calculates the tax amount for each person and deducts it, keeping the result to 2 decimal places. After execution, the salary table is as follows:

Starting from A12, output the results as needed to sequence a, and the final result is as follows: