frmgztj.vb

一个实际的商业项目

        Next
    End Sub

    Private Sub frmGztj_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cnData.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:database password=power;;Data Source= " & Application.StartupPath & "\gongzi.mdb;Persist Security Info=False"

        cmdSQL.Connection = cnData
        Dim dr As OleDbDataReader



        Dim dc As DataColumn

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "工号"
        dt.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "部门"
        dt.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "姓名"
        dt.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Single")
        dc.ColumnName = "应该出勤"
        dt.Columns.Add(dc)


        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Single")
        dc.ColumnName = "实际出勤"
        dt.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Single")
        dc.ColumnName = "基本工资"
        dc.DefaultValue = 0
        dt.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Single")
        dc.ColumnName = "计件工资"
        dc.DefaultValue = 0
        dt.Columns.Add(dc)

        cnData.Open()

        cmdSQL.CommandText = "select Yzsz from Gzxm where Xmmc ='应发工资'"
        douYfgz = CType(cmdSQL.ExecuteScalar(), Decimal)



        cmdSQL.CommandText = "select Xmmc,Yzsz from Gzxm where Type ='SQ'"
        dr = cmdSQL.ExecuteReader(CommandBehavior.CloseConnection)
        Do While dr.Read
            dc = New DataColumn
            dc.DataType = System.Type.GetType("System.Decimal")
            dc.ColumnName = Trim(dr.GetString(0))
            dc.DefaultValue = dr.GetDecimal(1)
            dt.Columns.Add(dc)
        Loop
        dr.Close()

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Decimal")
        dc.ColumnName = "应发工资"
        dt.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Decimal")
        dc.ColumnName = "税前小计"
        dt.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Decimal")
        dc.ColumnName = "个人所得税"
        dt.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Decimal")
        dc.ColumnName = "三金"
        dt.Columns.Add(dc)

        cmdSQL.CommandText = "select Xmmc,Yzsz from Gzxm where Type ='SH'"
        cnData.Open()
        dr = cmdSQL.ExecuteReader(CommandBehavior.CloseConnection)
        Do While dr.Read
            dc = New DataColumn
            dc.DataType = System.Type.GetType("System.Decimal")
            dc.ColumnName = Trim(dr.GetString(0))
            dc.DefaultValue = dr.GetDecimal(1)
            dt.Columns.Add(dc)
        Loop
        dr.Close()

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Decimal")
        dc.ColumnName = "实发"
        dt.Columns.Add(dc)

        DataGrid1.DataSource = dt

        txtYear.Text = Now.Year.ToString
        cbMonth.SelectedIndex = Now.Month - 1
    End Sub

    Private Sub btnTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTable.Click
        If txtDay.Text = "" Then
            MessageBox.Show("请输入应该出勤天数！")
            Exit Sub
        End If
        If Not IsNumeric(txtDay.Text) Then
            MessageBox.Show("出勤天数必须是数字！")
            Exit Sub
        End If
        If Val(txtDay.Text) <= 0 Or Val(txtDay.Text) > 31 Then
            MessageBox.Show("出勤天数不合法！")
            Exit Sub
        End If

        Dim rd As OleDbDataReader
        Dim dr As DataRow
        Dim dr2 As DataRow
        Dim dr3 As DataRow
        Dim strRq As String = txtYear.Text & cbMonth.SelectedItem.ToString

        Dim strRq1 As String
        Dim strRq2 As String = txtYear.Text & cbMonth.SelectedItem.ToString & "25"

        Dim douSum As Decimal
        Dim dou As Decimal

        If cbMonth.SelectedItem.ToString = "01" Then
            strRq1 = (CInt(txtYear.Text) - 1).ToString & "12" & "26"
        Else
            strRq1 = txtYear.Text & cbMonth.Items.Item(cbMonth.SelectedIndex - 1).ToString & "26"
        End If
        'MessageBox.Show(strRq1 & " " & strRq2)

        Dim dtTemp As DataTable
        dtTemp = New DataTable("Temp")
        Dim dc As DataColumn

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "Pf"
        dtTemp.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "Gx"
        dtTemp.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Single")
        dc.ColumnName = "Sl"
        dtTemp.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Single")
        dc.ColumnName = "Sl1"
        dtTemp.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Single")
        dc.ColumnName = "Sl2"
        dtTemp.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Single")
        dc.ColumnName = "Sl3"
        dtTemp.Columns.Add(dc)



        Dim dtTemp2 As DataTable
        dtTemp2 = New DataTable("Temp2")

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "Gx"
        dtTemp2.Columns.Add(dc)

        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.Single")
        dc.ColumnName = "Sl"
        dtTemp2.Columns.Add(dc)



        dt.Clear()

        cmdSQL.CommandText = "select Gh,Bmmc,Xm,Jbgz from Ryzl"
        cnData.Open()
        rd = cmdSQL.ExecuteReader()
        Do While rd.Read
            dr = dt.NewRow
            dr("工号") = Trim(rd.GetString(0))
            dr("部门") = Trim(rd.GetString(1))
            dr("姓名") = Trim(rd.GetString(2))
            dr("应该出勤") = Val(txtDay.Text)
            dr("实际出勤") = Val(txtDay.Text)
            dr("基本工资") = rd.GetDecimal(3)
            'dr("三金") = -(rd.GetDecimal(4) + rd.GetDecimal(5) + rd.GetDecimal(6))
            dt.Rows.Add(dr)
        Loop
        rd.Close()
        For Each dr In dt.Rows
            douSum = 0
            dtTemp.Clear()
            dtTemp2.Clear()
            'MessageBox.Show(dr("工号").ToString)
            'cmdSQL.CommandText = "SELECT Pf, Gx, sum(Sl) as total, sum(Sl1) as total1, sum(Sl2) as total2, sum(Sl3) as total3 " & _
            '                     "FROM Wg " & _
            '                     "WHERE Rq LIKE '" & strRq & "*' AND Gh = '" & dr("工号").ToString & "' " & _
            '                     "GROUP BY Pf,Gx"


            strstr = "select pf, gx, sum(sl) as total, sum(sl1) as total1, sum(sl2) as total2, sum(sl3) as total3 " & _
                                 "from wg " & _
                                 "where gh = '" & Trim(dr("工号").ToString) & "' " & _
                                 "and (rq between '" & strRq1 & "' and '" & strRq2 & "') " & _
                                 "group by pf, gx"
            'MessageBox.Show(strstr)

            cmdSQL.CommandText = strstr
            'cmdSQL.CommandText = "SELECT Pf ,Gx, sum(Sl) as total, sum(Sl1) as total1, sum(Sl2) as total2, sum(Sl3) as total3 from wg where (gh = '00001') and (rq like '200509%')  group by pf,gx "


            rd = cmdSQL.ExecuteReader()
            'MessageBox.Show("11111111111111111")

            Do While rd.Read
                'MessageBox.Show("11111111111111")
                dr2 = dtTemp.NewRow


                dr2("Pf") = Trim(rd.GetString(0))
                dr2("Gx") = Trim(rd.GetString(1))

                dr2("Sl") = rd.GetDouble(2)
                dr2("Sl1") = rd.GetDouble(3)
                dr2("Sl2") = rd.GetDouble(4)
                dr2("Sl3") = rd.GetDouble(5)
                dtTemp.Rows.Add(dr2)
            Loop
            rd.Close()

            For Each dr2 In dtTemp.Rows
                dou = 0
                cmdSQL.CommandText = "select Jg, Fei1, Fei2, Fei3 from Cpgx " & _
                                     "where Pf = '" & dr2("Pf").ToString & "' and Gx = '" & dr2("Gx").ToString & "'"

                rd = cmdSQL.ExecuteReader()
                If rd.Read Then
                    dou = rd.GetDecimal(0) * CType(dr2("Sl"), Decimal) - rd.GetDecimal(1) * CType(dr2("Sl1"), Decimal) - rd.GetDecimal(2) * CType(dr2("Sl2"), Decimal) - rd.GetDecimal(3) * CType(dr2("Sl3"), Decimal)
                End If

                rd.Close()
                douSum += dou
                'dou = CType(cmdSQL.ExecuteScalar, Decimal)
                'douSum += dou * CType(dr2("Sl"), Decimal)
            Next



            cmdSQL.CommandText = "select gx, sum(sl) as total from jswg " & _
                                 "where gh = '" & Trim(dr("工号").ToString) & "' " & _
                                 "and (rq between '" & strRq1 & "' and '" & strRq2 & "') " & _
                                 "group by gx"
            rd = cmdSQL.ExecuteReader
            Do While rd.Read
                dr3 = dtTemp2.NewRow
                dr3("Gx") = Trim(rd.GetString(0))
                dr3("Sl") = rd.GetDouble(1)
                dtTemp2.Rows.Add(dr3)
            Loop
            rd.Close()
            For Each dr3 In dtTemp2.Rows
                dou = 0

                cmdSQL.CommandText = "select Jg from JsGx where GxMc = '" & dr3("Gx").ToString & "'"
                rd = cmdSQL.ExecuteReader()

                If rd.Read Then
                    dou = rd.GetDecimal(0) * CType(dr3("Sl"), Decimal)
                End If
                rd.Close()
                douSum += dou
            Next

            dr("计件工资") = DotTwo(douSum)
        Next


        AddAll()
        cnData.Close()

    End Sub

    Private Sub btnRe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRe.Click
        If DialogResult.Yes = MessageBox.Show("确实需要重新计算吗？", "系统信息", MessageBoxButtons.YesNo, MessageBoxIcon.Question) Then
            cnData.Open()
            AddAll()
            cnData.Close()
        End If
        
    End Sub

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        Exports2Excel2(DataGrid1)
    End Sub

    Private Sub frmGztj_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        Dim pProcess As System.Diagnostics.Process

        For Each pProcess In System.Diagnostics.Process.GetProcesses()
            If pProcess.ProcessName.ToString() = "Excel" Then pProcess.Kill()
        Next
    End Sub
End Class