computeform.vb

这是一个固定资产管理系统的源代码

'文件名：ComputeForm.vb
Imports System.Data.SqlClient
Public Class ComputeForm
    Private Sub 折旧核算表BindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 折旧核算表BindingNavigatorSaveItem.Click
        Me.Validate()
        Me.折旧核算表BindingSource.EndEdit()
        Me.折旧核算表TableAdapter.Update(Me.MyAssetsDataSet.折旧核算表)
    End Sub
    Private Sub ComputeForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        For i As Integer = 2006 To 2100 Step 1
            Me.折旧年份ToolStripComboBox.Items.Add(i.ToString())
        Next
        For i As Integer = 1 To 12 Step 1
            Me.折旧月份ToolStripComboBox.Items.Add(i.ToString())
        Next
        'TODO: 这行代码将数据加载到表“MyAssetsDataSet.折旧核算表”中。您可以根据需要移动或移除它。
        Me.折旧核算表TableAdapter.Fill(Me.MyAssetsDataSet.折旧核算表)
    End Sub
    Private Sub 导入数据ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 导入数据ToolStripButton.Click
        If (Me.折旧年份ToolStripComboBox.Text = "" Or Me.折旧月份ToolStripComboBox.Text = "") Then
            Return
        End If
        If (MessageBox.Show("导入数据意味着本月的折旧数据将被删除，是否继续？", "信息提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.No) Then
            Return
        End If
        Dim MySQLConnectionString As String = My.Settings.MyAssetsConnectionString
        Dim MyConnection As New SqlConnection(MySQLConnectionString)
        MyConnection.Open()
        Dim MySQL As String = "DELETE FROM 折旧核算表;"
        MySQL += "INSERT INTO 折旧核算表 (使用部门,类别,资产编号,名称,资产原值,累计折旧,折旧方法,折旧月数,已提月数,月度折旧额,预计净残值) SELECT 使用部门,类别,资产编号,名称,资产原值,累计折旧,折旧方法,折旧月数,已提月数,月度折旧额,预计净残值 FROM 固定资产明细 WHERE (已提月数<折旧月数) AND (资产编号 NOT IN (SELECT 资产编号 FROM 减少固定资产));"
        MySQL += "UPDATE 折旧核算表 SET 折旧年份=" + Me.折旧年份ToolStripComboBox.Text + ",折旧月份=" + Me.折旧月份ToolStripComboBox.Text
        Dim MyCommand As SqlCommand = MyConnection.CreateCommand()
        MyCommand.CommandText = MySQL
        MyCommand.ExecuteNonQuery()
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
        Me.折旧核算表TableAdapter.Fill(Me.MyAssetsDataSet.折旧核算表)
    End Sub
    Private Function GetNewID() As String
        '自动计算自编号
        Dim MySQLConnectionString As String = My.Settings.MyAssetsConnectionString
        Dim MyConnection As New SqlConnection(MySQLConnectionString)
        MyConnection.Open()
        Dim MyCommand As SqlCommand = MyConnection.CreateCommand()
        MyCommand.CommandText = "Select max(自编号) 最大编号 From 计提累计折旧"
        Dim MyResult As Object = MyCommand.ExecuteScalar()
        Dim MyID As System.Int64 = 1
        If (Not (MyResult Is System.DBNull.Value)) Then
            Dim MyMaxID As String = MyResult.ToString().Trim()
            MyMaxID = MyMaxID.Substring(2, MyMaxID.Length - 2)
            MyID = Convert.ToInt64(MyMaxID) + 1
        End If
        Dim MyLength As Integer = MyID.ToString().Length
        Dim MyNewID As String = ""
        Select Case (MyLength)
            Case 1
                    MyNewID = "ZJ0000000" + MyID.ToString()
            Case 2
                    MyNewID = "ZJ000000" + MyID.ToString()
            Case 3
                    MyNewID = "ZJ00000" + MyID.ToString()
            Case 4
                    MyNewID = "ZJ0000" + MyID.ToString()
            Case 5
                    MyNewID = "ZJ000" + MyID.ToString()
            Case 6
                    MyNewID = "ZJ00" + MyID.ToString()
            Case 7
                MyNewID = "ZJ0" + MyID.ToString()
        End Select
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
        Return MyNewID
    End Function
    Private Sub 累计折旧ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 累计折旧ToolStripButton.Click
        If (Me.MyAssetsDataSet.折旧核算表.Rows.Count < 1) Then
            Return
        End If
        If (MessageBox.Show("累计折旧意味着本月的折旧数据将入帐并且无法再更改，是否继续？", "信息提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.No) Then
            Return
        End If
        Dim MySQLConnectionString As String = My.Settings.MyAssetsConnectionString
        Dim MyConnection As New SqlConnection(MySQLConnectionString)
        MyConnection.Open()
        Dim MyCount As Integer = 0
        Dim MyCommand As SqlCommand
        Dim MySQL As String
        For Each MyRow As DataRow In Me.MyAssetsDataSet.折旧核算表.Rows
            Dim My资产编号 As String = MyRow(2).ToString()
            Dim My折旧年份 As String = MyRow(11).ToString()
            Dim My折旧月份 As String = MyRow(12).ToString()
            MySQL = "Select COUNT(*) From 计提累计折旧 Where 折旧年份=" + My折旧年份 + " AND 折旧月份=" + My折旧月份 + " AND 资产编号='" + My资产编号 + "'"
            MyCommand = New SqlCommand(MySQL, MyConnection)
            MyCount = MyCommand.ExecuteScalar()
            If (MyCount > 0) Then
                Continue For
            End If
            MySQL = "INSERT INTO 计提累计折旧([自编号],[资产编号],[折旧方法],[月度折旧额],[折旧年份] ,[折旧月份], [说明]) VALUES (@自编号,@资产编号,@折旧方法,@月度折旧额,@折旧年份 ,@折旧月份,@说明)"
            MyCommand = MyConnection.CreateCommand()
            MyCommand.CommandText = MySQL
            MyCommand.Parameters.Add(New SqlParameter("@自编号", SqlDbType.VarChar))
            MyCommand.Parameters.Add(New SqlParameter("@资产编号", SqlDbType.VarChar))
            MyCommand.Parameters.Add(New SqlParameter("@折旧方法", SqlDbType.VarChar))
            MyCommand.Parameters.Add(New SqlParameter("@月度折旧额", SqlDbType.Float))
            MyCommand.Parameters.Add(New SqlParameter("@折旧年份", SqlDbType.Int))
            MyCommand.Parameters.Add(New SqlParameter("@折旧月份", SqlDbType.Int))
            MyCommand.Parameters.Add(New SqlParameter("@说明", SqlDbType.VarChar))
            MyCommand.Parameters("@自编号").Value = Me.GetNewID()
            MyCommand.Parameters("@资产编号").Value = MyRow(2)
            MyCommand.Parameters("@折旧方法").Value = MyRow(6)
            MyCommand.Parameters("@月度折旧额").Value = MyRow(9)
            MyCommand.Parameters("@折旧年份").Value = MyRow(11)
            MyCommand.Parameters("@折旧月份").Value = MyRow(12)
            MyCommand.Parameters("@说明").Value = MyRow(13)
            MyCommand.ExecuteNonQuery()
            MyCommand.CommandText = "Update 固定资产明细 Set 累计折旧=累计折旧+" + MyRow(9).ToString() + ",已提月数=已提月数+1 WHERE 资产编号='" + My资产编号 + "'"
            MyCommand.ExecuteNonQuery()
        Next
        MySQL = "DELETE FROM 折旧核算表"
        MyCommand = New SqlCommand(MySQL, MyConnection)
        MyCommand.ExecuteScalar()
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
        Me.折旧核算表TableAdapter.Fill(Me.MyAssetsDataSet.折旧核算表)
    End Sub
End Class