请问如何在以下问题中生成薪酬结构 [英] How do I generate salary structure in below problem please

问题描述

我的sql表中有以下数据;

I have the following data in my sql table;

rank   grade_level   maximum_step   salary_base   salary_increment
A          15             1             5000.00          0.00
B          14             3             4500.00          205.00
C          13             7             3500.00          185.00

我想写一个vb.net代码读取每一行并返回以下结果：

I want to write a vb.net code to read each line and return result below:

rank    grade_level     step_1    step_2    step_3 ........ step_n
A          15           5000.00
B          14           4500.00   4705.00   4910.00 
C          13           3500.00   3685.00   3870.00   4055.00 .......

，其中n = maximum_step字段中的最高值，在上面的示例中为7



我尝试过：

, where n=highest value in maximum_step field which is 7 in the above example

What I have tried:

Private Sub btnRun_Click(sender As Object, e As EventArgs) Handles btnRun.Click
        Dim str As String
        Dim i As Integer
        str = "select rankCode, gl,maxStep,sBase,sInc,rBase,rInc from ifmis_rank "
        Dim com As New SqlCommand(str, cn)
        Dim rd As SqlDataReader
        lstVAudit.Items.Clear()
        cn.Open()
        With com
            rd = .ExecuteReader(CommandBehavior.CloseConnection)
            With rd
                While .Read
                    lst = New ListViewItem
lst.Text = CType(.Item(0), String)
                    lst.SubItems.Add(CType(.Item(1), String))
for i = 1 to ctype(.item(2),integer)
 lst.SubItems.Add(CType(.Item(3), Decimal))
next 1        
                    lstVAudit.Items.Add(lst)
                End While
            End With
        End With
    End Sub

推荐答案

如果我没有正确理解你，你必须通过添加 salary_increment 乘以 n salary_base >直到 n 达到 maximum_step 。



假设你的数据存储在 DataTable 对象中......

If i undestand you correctly, you have to calculate salary_base by adding salary_increment multiplied by n till n reaches maximum_step.

Assuming your data is stored in a DataTable object...

'your code here till
rd = .ExecuteReader(CommandBehavior.CloseConnection)
'then
Dim dt As DataTable = New DataTable()
dt.Load(rd)

'get max of maximum_step
Dim maxstep = dt.AsEnumerable().Max(Function(x) x.Field(Of Integer)("maximum_step")) 

'create final DataTable
Dim finaldt As DataTable = New DataTable()
finaldt.Columns.Add(New DataColumn("rank", Type.GetType("System.String")))
finaldt.Columns.Add(New DataColumn("grade_level", Type.GetType("System.Int32")))
'add "step" columns
For i As Integer = 1 To maxstep
	finaldt.Columns.Add(New DataColumn("step_" & i, Type.GetType("System.Int32")))
Next

For Each r As DataRow In dt.Rows
	Dim destRow As DataRow = finaldt.NewRow()
	maxstep = r.Field(Of Integer)("maximum_step")
	destRow("rank") = r.Field(Of String)("rank")
	destRow("grade_level") = r.Field(Of Integer)("grade_level")
	For i As Integer = 1 To maxstep
		destRow("step_" & i) = r.Field(Of Double)("salary_base") + (r.Field(Of Double)("salary_increment") * i)
	Next
	finaldt.Rows.Add(destRow)
Next

结果（ finaldt 内容）：

Result (finaldt content):

rank grade_level step_1 step_2 step_3 step_4 step_5 step_6 step_7
A    15          5000   null   null   null   null   null   null 
B    14          4705   4910   5115   null   null   null   null 
C    13          3685   3870   4055   4240   4425   4610   4795

以同样的方式创建 ListViewItem [ ^ ]。





.NET 2.解决方案：

In the same manner you can create ListViewItem[^].


.NET 2. solution:

'get max  of maximum_step
Dim sortedRows = dt.Select("maximum_step>=0", "maximum_step DESC")
Dim maxstep As Integer = sortedRows.GetValue(0)("maximum_step")

'create final DataTable
Dim finaldt As DataTable = New DataTable()
finaldt.Columns.Add(New DataColumn("rank", Type.GetType("System.String")))
finaldt.Columns.Add(New DataColumn("grade_level", Type.GetType("System.Int32")))
'add "step" columns
For i As Integer = 1 To maxstep
    finaldt.Columns.Add(New DataColumn("step_" & i, Type.GetType("System.Int32")))
Next

For Each r As DataRow In dt.Rows
    Dim destRow As DataRow = finaldt.NewRow()
    maxstep = r("maximum_step")
    destRow("rank") = r("rank")
    destRow("grade_level") = r("grade_level")
    For i As Integer = 1 To maxstep
        destRow("step_" & i) = r("salary_base") + (r("salary_increment") * i)
    Next
    finaldt.Rows.Add(destRow)
Next

这篇关于请问如何在以下问题中生成薪酬结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！