frmselllist.vb

VB.NET开发的进销存系统,数据库SQLSERVER

            frm.ShowDialog()
            Dim result() As String = frm.SetInfo().Split("|")
            Me.txbDepot.Text = result(0)
            iMaxCount = CInt(result(1))
        Catch ex As Exception

        End Try
    End Sub
    Private Function ValidateNew() As Boolean
        If Me.txbClientId.Text.Trim.Length = 0 Or Me.txbClientName.Text.Trim.Length = 0 Then
            Return False
        End If
        If Me.txbDepot.Text.Trim.Length = 0 Then
            Return False
        End If
        If Me.txbGoodsId.Text.Trim.Length = 0 Or Me.txbGoodsName.Text.Trim.Length = 0 Then
            Return False
        End If
        If Me.txbGoodsCount.Text.Trim.Length = 0 Or Me.txbPrice.Text.Trim.Length = 0 Then
            Return False
        End If
        Dim i As Integer = CInt(Me.txbGoodsCount.Text.Trim)
        If i > Me.iMaxCount Then
            Return False
        End If
        Return True

    End Function
    Private Sub InitTable()
        '新建表
        dtSellList = New DataTable
        Dim dc As DataColumn
        '新建 DataColumn, 设置 DataType, ColumnName 并加入 DataTable
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "货号"
        dtSellList.Columns.Add(dc)
        '新建 DataColumn, 设置 DataType, ColumnName 并加入 DataTable
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "品名"
        dtSellList.Columns.Add(dc)
        '新建 DataColumn, 设置 DataType, ColumnName 并加入 DataTable
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "数量"
        dtSellList.Columns.Add(dc)
        '新建 DataColumn, 设置 DataType, ColumnName 并加入 DataTable
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "单位"
        dtSellList.Columns.Add(dc)
        '新建 DataColumn, 设置 DataType, ColumnName 并加入 DataTable
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "单价"
        dtSellList.Columns.Add(dc)
        '新建 DataColumn, 设置 DataType, ColumnName 并加入 DataTable
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "仓库"
        dtSellList.Columns.Add(dc)
        '新建 DataColumn, 设置 DataType, ColumnName 并加入 DataTable
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "税价合计"
        dtSellList.Columns.Add(dc)
        '新建 DataColumn, 设置 DataType, ColumnName 并加入 DataTable
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "税率"
        dtSellList.Columns.Add(dc)
        '新建 DataColumn, 设置 DataType, ColumnName 并加入 DataTable
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "税额"
        dtSellList.Columns.Add(dc)
        '新建 DataColumn, 设置 DataType, ColumnName 并加入 DataTable
        dc = New DataColumn
        dc.DataType = System.Type.GetType("System.String")
        dc.ColumnName = "不含税价"
        dtSellList.Columns.Add(dc)
    End Sub
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        If Me.ValidateNew() = False Then
            MessageBox.Show("数据错误")
            Exit Sub
        End If
        Dim dr As DataRow = Me.dtSellList.NewRow
        dr("货号") = Me.txbGoodsId.Text.Trim
        dr("品名") = Me.txbGoodsName.Text.Trim()
        dr("数量") = Me.txbGoodsCount.Text.Trim
        dr("单位") = Me.txbGoodsUnit.Text.Trim
        dr("单价") = Me.txbPrice.Text.Trim
        dr("仓库") = Me.txbDepot.Text.Trim
        Try

            Dim total As Integer = CInt(Me.txbPrice.Text.Trim) * CInt(Me.txbGoodsCount.Text.Trim)
            Dim rate As Integer = CInt(Me.lblRateValue.Text.Trim)
            dr("税价合计") = total.ToString
            dr("税率") = Me.lblRateValue.Text.Trim
            dr("税额") = (total - total / (1 + rate / 100)).ToString("f")
            dr("不含税价") = (total / (1 + rate / 100)).ToString("f")
            Me.dtSellList.Rows.Add(dr)

            SetTaxForAdd(CInt(Me.txbPrice.Text.Trim) * CInt(Me.txbGoodsCount.Text.Trim))
        Catch ex As Exception
            MessageBox.Show("数据格式不正确")
        End Try
        Clear()
    End Sub

    Private Sub ToolBar1_ButtonClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) Handles ToolBar1.ButtonClick
        If e.Button Is Me.tbbSave Then
            If Me.dtSellList.Rows.Count = 0 Then
                Exit Sub
            End If
            Dim db As DataBase = New DataBase

            ' 获取单据ID
            Dim dvTemp As DataView = db.RunSelectSQL("select max(编号) as 编号 from 销售单历史")
            Dim sId As String = "0000000001"
            If dvTemp.Table.Rows(0).IsNull(0) = False Then
                sId = (CType(dvTemp.Table.Rows(0)("编号"), Integer) + 1).ToString
            End If
            Dim count As Integer
            For count = sId.Trim.Length To 9
                sId = "0" + sId

            Next
            ' 删除已有数据
            db.RunDelOrInsSQL("delete from 销售单明细 where 销售单号='" + sId + "'")
            db.RunDelOrInsSQL("delete from 销售单 where 编号='" + sId + "'")
            ' 写入销售单
            Dim strSQL As String = "insert into 销售单(编号,客户编号,销售日期, 制单人,业务员,"
            strSQL += "税价合计,不含税价,税额,保管员) values('" + sId + "','"
            strSQL += Me.txbClientId.Text.Trim + "','" + Me.txbOrderDate.Text + "','" + Me.txtListMaker.Text.Trim + "','"
            strSQL += CType(Me.cbbWorker.SelectedItem("姓名"), String).Trim() + "'," + Me.txbTotal.Text.Trim() + "," + Me.txbWithoutTax.Text.Trim
            strSQL += "," + Me.txbTax.Text + ",'sys')"
            db.RunDelOrInsSQL(strSQL)

            ' 写入明细
            Dim sId1 As String = "0000000000"
            dvTemp = db.RunSelectSQL("select max(编号) as 编号  from 销售单明细历史")
            If dvTemp.Table.Rows(0).IsNull(0) <> True Then
                sId1 = dvTemp.Table.Rows(0)(0)
            End If

            Dim i As Integer
            For i = 0 To dtSellList.Rows.Count - 1
                sId1 = (CInt(sId1) + 1).ToString
                For count = sId1.Trim.Length To 9
                    sId1 = "0" + sId1
                Next
                strSQL = "insert into 销售单明细(编号,销售单号,货号,销售数量,销售价,仓库,"
                strSQL += "税价合计,税率,不含税价,税额) values('" + sId1 + "','"
                strSQL += sId + "','" + dtSellList.Rows(i)("货号") + "','"
                strSQL += dtSellList.Rows(i)("数量") + "','" + dtSellList.Rows(i)("单价") + "','"
                strSQL += dtSellList.Rows(i)("仓库") + "','" + dtSellList.Rows(i)("税额") + "','"
                strSQL += dtSellList.Rows(i)("税率") + "','" + dtSellList.Rows(i)("不含税价") + "','"
                strSQL += dtSellList.Rows(i)("税额") + "')"
                db.RunDelOrInsSQL(strSQL)
            Next
            db.Dispose()
        End If
        If e.Button Is Me.tbbConfirm Then
            If MessageBox.Show("是否记帐确认销售单", "提示", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                Dim db As DataBase = New DataBase
                Try
                    db.RunDelOrInsSQL("exec sf_销售单")
                    db.Dispose()
                    Me.txbClientId.Text = ""
                    Me.txbClientName.Text = ""
                    Me.txbGoodsCount.Text = ""
                    Me.txbGoodsName.Text = ""
                    Me.txbGoodsId.Text = ""
                    Me.txbGoodsUnit.Text = ""
                    Me.txbPrice.Text = ""
                    Me.txbTax.Text = "0"
                    Me.txbTotal.Text = "0"
                    Me.txbWithoutTax.Text = "0"
                    Me.dtSellList.Clear()
                Catch ex As Exception
                    MessageBox.Show(ex.ToString()) '("提交销售单出错")
                End Try
            End If
        End If
    End Sub
    Private Sub SetTaxForAdd(ByVal total As Integer)
        Try
            Dim iAll As Integer = total + CInt(Me.txbTotal.Text.Trim())
            Me.txbTotal.Text = iAll.ToString
            Me.txbTax.Text = (iAll - iAll / (1 + CInt(Me.lblRateValue.Text.Trim()) / 100)).ToString("f")
            Me.txbWithoutTax.Text = (iAll / (1 + CInt(Me.lblRateValue.Text.Trim()) / 100)).ToString("f")
        Catch ex As Exception
            MessageBox.Show("数据格式不正确")
        End Try
    End Sub
    Private Sub Clear()
        'Me.txbGoodsName.Text = ""
        'Me.txbGoodsUnit.Text = ""
        'Me.txbGoodsId.Text = ""
        Me.txbPrice.Text = ""
        Me.txbGoodsCount.Text = ""
        Me.txbDepot.Text = ""
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            Dim i, total As Integer
            Dim s As String = "0"
            Dim rate As Integer = CInt(Me.lblRateValue.Text.Trim)
            If Me.dgdOrderList.CurrentRowIndex <> -1 Then
                Dim iRow As Integer = Me.dgdOrderList.CurrentRowIndex
                Me.dtSellList.Rows(iRow)("货号") = Me.txbGoodsId.Text.Trim
                Me.dtSellList.Rows(iRow)("单价") = Me.txbPrice.Text.Trim
                Me.dtSellList.Rows(iRow)("数量") = Me.txbGoodsCount.Text.Trim
                Me.dtSellList.Rows(iRow)("单位") = Me.txbGoodsUnit.Text.Trim
                Me.dtSellList.Rows(iRow)("品名") = Me.txbGoodsName.Text.Trim
                Me.dtSellList.Rows(iRow)("单价") = Me.txbPrice.Text.Trim
                Me.dtSellList.Rows(iRow)("仓库") = Me.txbDepot.Text.Trim
                total = CInt(Me.txbPrice.Text.Trim) * CInt(Me.txbGoodsCount.Text.Trim)
                Me.dtSellList.Rows(iRow)("税率") = Me.lblRateValue.Text.Trim

                Me.dtSellList.Rows(iRow)("税额") = (total - total / (1 + rate / 100)).ToString("f")
                Me.dtSellList.Rows(iRow)("不含税价") = (total / (1 + rate / 100)).ToString("f")
                Me.dtSellList.Rows(iRow)("税价合计") = total.ToString
            End If
            SetTaxForDelOrUpgrade()
        Catch ex As Exception
            MessageBox.Show("数据错误")
        End Try

        Clear()
    End Sub
    Private Sub txbClientId_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If Me.txbClientId.Text.Trim.Length > 0 Then
            Dim db As DataBase = New DataBase
            Dim dv As DataView = db.RunSelectSQL("select 名称 from 客户清单 where 客户编号 = '" + Me.txbClientId.Text.Trim + "'")
            If dv.Table.Rows.Count > 0 Then
                Me.txbClientName.Text = dv.Table.Rows(0)("名称")
            Else
                Me.txbClientId.Text = ""
                Me.txbClientName.Text = ""
            End If
            db.Dispose()
        End If
    End Sub

    Private Sub txbGoodsId_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txbGoodsId.Leave
        If Me.txbGoodsId.Text.Trim.Length > 0 Then
            Dim db As DataBase = New DataBase
            Dim dv As DataView = db.RunSelectSQL("select 品名,单位 from 商品清单 where 货号 = '" + Me.txbGoodsId.Text.Trim + "'")
            If dv.Table.Rows.Count > 0 Then
                Me.txbGoodsName.Text = dv.Table.Rows(0)("品名")
                Me.txbGoodsUnit.Text = dv.Table.Rows(0)("单位")
                Me.txbGoodsId.Text = dv.Table.Rows(0)("单位")
                Me.txbGoodsUnit.Text = dv.Table.Rows(0)("单位")
            Else
                Me.txbGoodsId.Text = ""
                Me.txbGoodsName.Text = ""
                Me.txbGoodsUnit.Text = ""
            End If
            db.Dispose()
        End If
    End Sub

    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
        Try
            '获得DataGrid当前被选中的行号
            Dim iRow = dgdOrderList.CurrentRowIndex
            '弹出对话框让用户确认要删除记录
            '如果用户确认要删除记录
            If MessageBox.Show("你确认要删除此条记录吗？", "Confirm", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                '从商品清单表删除当前被选中记录()
                dtSellList.Rows(iRow).Delete()
                SetTaxForDelOrUpgrade()
            Else
                Exit Sub
            End If
        Catch ex As Exception
            MessageBox.Show("删除数据出错")
        End Try
    End Sub
    Private Sub SetTaxForDelOrUpgrade()

        Dim total As Integer = 0
        Dim i As Integer
        Dim rate As Integer = CInt(Me.lblRateValue.Text.Trim)
        Dim s As String
        For i = 0 To Me.dtSellList.Rows.Count - 1
            s = Me.dtSellList.Rows(i)("税价合计")
            total = total + CInt(s.Trim)
        Next
        Me.txbTax.Text = (total - total / (1 + rate / 100)).ToString("f")
        Me.txbTotal.Text = total.ToString
        Me.txbWithoutTax.Text = (total / (1 + rate / 100)).ToString("f")
    End Sub

    Private Sub dgdOrderList_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgdOrderList.MouseDown
        Dim hti As DataGrid.HitTestInfo = Me.dgdOrderList.HitTest(New Point(e.X, e.Y))
        If hti.Type = DataGrid.HitTestType.Cell Then
            Dim iRow As Integer = Me.dgdOrderList.CurrentRowIndex
            Me.txbPrice.Text = Me.dtSellList.Rows(iRow)("单价")
            Me.txbGoodsCount.Text = Me.dtSellList.Rows(iRow)("数量")
            Me.txbGoodsId.Text = dtSellList.Rows(iRow)("货号")
            Me.txbGoodsName.Text = dtSellList.Rows(iRow)("品名")
            Me.txbDepot.Text = dtSellList.Rows(iRow)("仓库")

        End If
    End Sub
End Class