frmmingxi.vb

一个实际的商业项目

            MessageBox.Show("没有数据!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub frmMingXi_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
        txtYear.Text = Now.Year.ToString
        cbMonth.SelectedIndex = Now.Month - 1

        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.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.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.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)

    End Sub

    Private Sub btnTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTable.Click
        Dim rd As OleDbDataReader
        Dim dr 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 drTemp As DataRow

        Dim money As Decimal
        Dim moneySum 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


        dt.Clear()


        Dim dtGh As DataTable = New DataTable("Gh")
        Dim dc As DataColumn

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

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

        cnData.Open()
        cmdSQL.CommandText = "select Gh, Xm from Ryzl order by Gh"
        rd = cmdSQL.ExecuteReader()
        Do While rd.Read
            dr = dtGh.NewRow
            dr("Gh") = rd.GetString(0)
            dr("Xm") = rd.GetString(1)
            dtGh.Rows.Add(dr)
        Loop
        rd.Close()

        For Each dr In dtGh.Rows
            moneySum = 0

            'drTemp = dt.NewRow
            'drTemp("工号") = dr("Gh")
            'drTemp("姓名") = dr("Xm")
            cmdSQL.CommandText = "select Wg.Rq, Wg.Pf, Wg.Gx, Cpgx.Jg, Wg.Sl, Wg.Sl1, Wg.Sl2, Wg.Sl3, Wg.Bz, Cpgx.Fei1, Cpgx.Fei2, Cpgx.Fei3 " & _
                                 "from Wg inner join Cpgx on (Wg.Pf = Cpgx.Pf and Wg.Gx = Cpgx.Gx) " & _
                                 "where Wg.Gh = '" & dr("Gh").ToString & "' and (Wg.Rq between '" & strRq1 & "' and '" & strRq2 & "') " & _
                                 "order by Wg.Rq"
            rd = cmdSQL.ExecuteReader


            Do While rd.Read
                money = 0
                drTemp = dt.NewRow
                drTemp("工号") = dr("Gh")
                drTemp("姓名") = dr("Xm")
                drTemp("完工日期") = rd.GetString(0)
                drTemp("产品名称") = rd.GetString(1)
                drTemp("工序") = rd.GetString(2)
                drTemp("单价") = rd.GetDecimal(3)
                drTemp("完工数") = rd.GetInt32(4)
                drTemp("不合格(料)") = rd.GetInt32(5)
                drTemp("不合格(责)") = rd.GetInt32(6)
                drTemp("不合格(回用)") = rd.GetInt32(7)
                money = rd.GetDecimal(3) * rd.GetInt32(4) - rd.GetInt32(5) * rd.GetDecimal(9) - rd.GetInt32(6) * rd.GetDecimal(10) - rd.GetInt32(7) * rd.GetDecimal(11)
                drTemp("完成定额") = DotTwo(money)
                moneySum += money
                drTemp("备注") = rd.GetString(8)
                dt.Rows.Add(drTemp)
            Loop
            rd.Close()

            cmdSQL.CommandText = "select JsWg.Rq, JsWg.Gx, JsGx.Jg, JsWg.Sl, JsWg.Bz " & _
                                 "from JsWg inner join JsGx on JsWg.Gx = JsGx.GxMc " & _
                                 "where JsWg.Gh = '" & dr("Gh").ToString & "' and (JsWg.Rq between '" & strRq1 & "' and '" & strRq2 & "') " & _
                                 "order by JsWg.Rq"
            rd = cmdSQL.ExecuteReader
            Do While rd.Read
                money = 0
                drTemp = dt.NewRow
                drTemp("工号") = dr("Gh")
                drTemp("姓名") = dr("Xm")
                drTemp("完工日期") = rd.GetString(0)
                drTemp("产品名称") = "<计时>"
                drTemp("工序") = rd.GetString(1)
                drTemp("单价") = rd.GetDecimal(2)
                drTemp("完工数") = rd.GetDouble(3)
                drTemp("不合格(料)") = ""
                drTemp("不合格(责)") = ""
                drTemp("不合格(回用)") = ""
                money = CType(rd.GetDecimal(2) * rd.GetDouble(3), Decimal)
                drTemp("完成定额") = DotTwo(money)
                moneySum += money
                drTemp("备注") = rd.GetString(4)
                dt.Rows.Add(drTemp)
            Loop
            rd.Close()

            If moneySum <> 0 Then
                drTemp = dt.NewRow
                drTemp("工号") = ""
                drTemp("姓名") = "合计"
                drTemp("完工日期") = ""
                drTemp("产品名称") = ""
                drTemp("工序") = ""
                drTemp("单价") = ""
                drTemp("完工数") = ""
                drTemp("不合格(料)") = ""
                drTemp("不合格(责)") = ""
                drTemp("不合格(回用)") = ""
                drTemp("完成定额") = DotTwo(moneySum)
                drTemp("备注") = ""
                dt.Rows.Add(drTemp)
            End If



        Next

        cnData.Close()
        DataGrid1.DataSource = dt
        DataGrid1.ReadOnly = True
        DataGrid1.AllowSorting = False
    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 frmMingXi_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