客户订单管理系统功能简介： 某公司为企业客户提供食品、蔬菜、肉制品等商品采购配送服务。为此公司需要保存企业客户的基本信息，其基本信息包括企业名称、客户简称、企业法人、企业地址、邮政编码、开户银行、账号以及企业客户的联系人，联系人信息包括联系
某公司为企业客户提供食品、蔬菜、肉制品等商品采购配送服务。为此公司需要保存企业客户的基本信息，其基本信息包括企业名称、客户简称、企业法人、企业地址、邮政编码、开户银行、账号以及企业客户的联系人，联系人信息包括联系人姓名、联系人电话、手机、电子信箱。 一个企业客户可以有多个联系人。
公司为企业客户提供的商品信息描述包括商品名称、包装方式、计量单位、商品产地、商品保质期、商品特征描述、售价。
企业客户中的联系人根据企业的实际需求和公司提供的商品信息下订单，订单信息包括订单日期、要求到货日期时间、送货地址、特殊说明、下订单人以及订购的商品详细信息，订购商品详细信息包括商品名称、购买数量、单价、金额。 一个订单中可以有多种商品。
公司为了能够保质、保量、按时地位客户服务，要求客户在要求到货日期的前一天下订单且每天只能下一个订单。每天早上公司根据客户订单确定采购的商品和总量形成采购单，采购员根据采购单采购，采购回来后保存采购回来的商品数量、计量单位及采购价格。
详细设计要求：
1）设计E-R图。
2）根据E-R图进行数据库的逻辑设计和物理设计
3）在数据库上建库、基本表以及依据数据库设计方案建立的视图
4）创建系统总体功能菜单
5）实现对商品信息、客户信息、订单信息、采购信息的管理（包括数据的插入、删除和修改功能）
6）实现各种基本表中数据的查询功能（执行模糊查询）
7）实现按商品分类查询和统计。
8）设计使用该系统的用户有三种：系统管理员、采购员、客服。
采购员能够根据执行采购单生成功能、根据采购结果实现对采购回来的商品的维护功能。客服在接到客户的需求后能够生成客户订单、查询客户信息、维护商品信息。系统管理员能够执行系统全部功能。
9）将所开发出的功能分配给三种不同的用户，各类用户在登录系统时需要输入帐号、密码，并进入自己的功能菜单，进行相应的操作。
<无>
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim name As String = TextBox1.Text
Dim password As String = TextBox2.Text
If password = "123456" Then
If name = "purchaser" Then
Form2.Button1.Enabled = False
Form2.Button2.Enabled = False
Form2.Button3.Enabled = False
Me.Hide()
Form2.Show()
ElseIf name = "waiter" Then
Form2.Button5.Enabled = False
Me.Hide()
Form2.Show()
ElseIf name = "admin" Then
Form2.Show()
Me.Hide()
Else
MsgBox("不存在此用户名")
End If
Else
MsgBox("密码错误")
End If
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Form2.Button1.Enabled = True
Form2.Button2.Enabled = True
Form2.Button3.Enabled = True
Form2.Button5.Enabled = True
End Sub
End Class
Public Class Form2
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Hide()
Form3.Show()
End Sub
Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.Hide()
Form4.Show()
End Sub
Private Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Hide()
Form5.Show()
End Sub
Private Sub Button5_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button5.Click
Me.Hide()
Form7.Show()
End Sub
End Class
Imports System.Data.SqlClient
Public Class Form3
Dim connstr As String = "data source=localhost;initial catalog=Database project design;integrated security=SSPI"
Dim conn As SqlConnection = New SqlConnection(connstr)
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
fresh()
fresh1()
End Sub
Sub fresh()
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 企业客户信息表", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView1.DataSource = data
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If MsgBox("您确认要更改此企业信息吗？（若您需要更改企业名称，请在下方输入信息后进行更改）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
Try
Dim data As DataTable
data = DataGridView1.DataSource
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 企业客户信息表", conn)
Dim cmdb As SqlCommandBuilder = New SqlCommandBuilder(myadpt)
Dim changedData As DataTable = data.GetChanges()
If Not changedData Is Nothing Then
myadpt.Update(changedData)
data.AcceptChanges()
End If
MsgBox("修改企业信息成功！")
fresh()
fresh1()
Catch ex As Exception
MsgBox("暂时无法在信息框内修改企业名称！请您在下方修改企业名称!")
End Try
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
fresh()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 企业客户信息表 where 企业名称='" & Trim(TextBox1.Text) & "'", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView1.DataSource = data
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
If MsgBox("您确认要删除此企业吗？（将会删除此企业所有联系人及所有订单）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("delete from 企业客户信息表 where 企业名称='" & Trim(TextBox2.Text) & "' ", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView1.DataSource = data
Dim myadpt1 As SqlDataAdapter = New SqlDataAdapter("delete from 客户订购信息表 where 企业名称='" & Trim(TextBox2.Text) & "' ", conn)
Dim data1 As DataTable = New DataTable
myadpt1.Fill(data1)
DataGridView1.DataSource = data1
fresh()
fresh1()
Catch ex As Exception
MsgBox("不存在该企业名称！")
End Try
End If
fresh()
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
Dim str() As String = {TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text}
Dim mycmd As String = "insert into 联系人信息表(联系人姓名,电话号,手机号,企业名称,邮箱) values('"
For i% = 0 To 3
mycmd = mycmd & Trim(str(i)) & "','"
Next
mycmd = mycmd & str(4) & "')"
Dim conn As SqlConnection = New SqlConnection(connstr)
conn.Open()
Dim cmd As SqlCommand = New SqlCommand(mycmd, conn)
Try
If cmd.ExecuteNonQuery = 1 Then
MsgBox("添加联系人成功")
Else
MsgBox("添加联系人失败")
End If
conn.Close()
fresh1()
Catch ex As Exception
MsgBox("添加了重复的联系人姓名！或是 不存在该企业名称！")
End Try
End Sub
Sub fresh1()
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 联系人信息表,企业客户信息表 where 联系人信息表.企业名称=企业客户信息表.企业名称", conn)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView2.DataSource = mydt
Catch ex As Exception
MsgBox("不存在该企业名称！")
End Try
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
If MsgBox("您确认要更改此联系人姓名吗？请与客户确认是否更换联系人！（将会更改订单中所有相应的联系人姓名！）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
Dim data As DataTable
data = DataGridView2.DataSource
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 联系人信息表", conn)
Dim cmdb As SqlCommandBuilder = New SqlCommandBuilder(myadpt)
Dim changedData As DataTable = data.GetChanges()
Try
If Not changedData Is Nothing Then
myadpt.Update(changedData)
data.AcceptChanges()
End If
MsgBox("修改联系人信息成功！")
fresh1()
Catch ex As Exception
MsgBox("添加的联系人姓名重复！")
End Try
End If
End Sub
Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
fresh1()
End Sub
Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
If MsgBox("您确认要删除此联系人吗？请与企业客户联系确认！（将会删除该联系人下的所有订单信息！）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("delete from 联系人信息表 where 联系人姓名='" & Trim(TextBox8.Text) & "' ", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView2.DataSource = data
fresh1()
Catch ex As Exception
MsgBox("不存在该联系人姓名！")
End Try
End If
fresh1()
End Sub
Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
Me.Hide()
Form2.Show()
End Sub
Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click
If MsgBox("您确认要修改此企业名称吗？请与企业客户联系确认！（将会修改该联系人信息和订单信息中的企业名称！）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
Dim enterprisename As String = Trim(TextBox10.Text)
Dim name1 As String = Trim(TextBox9.Text)
Try
Dim cmd1 As SqlCommand = New SqlCommand("update 企业客户信息表 set 企业名称 = '" & name1 & "'where 企业名称='" & enterprisename & "'", conn)
conn.Open()
Try
If cmd1.ExecuteNonQuery = 1 Then
Dim cmd2 As SqlCommand = New SqlCommand("update 客户订购信息表 set 企业名称 = '" & name1 & "'where 企业名称='" & enterprisename & "'", conn)
cmd2.ExecuteNonQuery()
MsgBox("修改企业名称成功")
fresh()
Else
MsgBox("修改企业名称失败")
fresh()
End If
Catch ex As DuplicateNameException
MsgBox("已存在该企业名称")
End Try
conn.Close()
Catch ex As Exception
MsgBox("修改企业名称失败,要修改的企业名称不存在")
End Try
End If
End Sub
Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 联系人信息表 where 企业名称= '" & Trim(TextBox11.Text) & "'", conn)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView2.DataSource = mydt
Catch ex As Exception
MsgBox("不存在该企业名称！")
End Try
End Sub
End Class
Imports System.Data.SqlClient
Public Class Form4
Dim connstr As String = "data source=localhost;initial catalog=Database project design;integrated security=SSPI"
Dim conn As SqlConnection = New SqlConnection(connstr)
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
fresh()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If MsgBox("您确认要更改此商品信息吗？（若您更改商品名称，所有订购商品和采购商品的名称都将进行更改！）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
Dim data As DataTable
data = DataGridView1.DataSource
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表", conn)
Dim cmdb As SqlCommandBuilder = New SqlCommandBuilder(myadpt)
Dim changedData As DataTable = data.GetChanges()
Try
If Not changedData Is Nothing Then
myadpt.Update(changedData)
data.AcceptChanges()
End If
MsgBox("修改商品信息成功！")
fresh()
Catch ex As DuplicateNameException
MsgBox("添加的商品名称重复！")
Catch ex As Exception
MsgBox("修改商品名称失败！")
End Try
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
fresh()
End Sub
Sub fresh()
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView1.DataSource = data
End Sub
Private Sub DateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 where 出产日期 between '" & Trim(Format(DateTimePicker1.Value, "yyyy-MM-dd").ToString()) & "' and '" & Trim(Format(DateTimePicker2.Value, "yyyy-MM-dd").ToString()) & "'", conn)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView1.DataSource = mydt
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 where 商品产地= '" & Trim(ComboBox1.Text) & "' ", conn)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView1.DataSource = mydt
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 where 商品单价 between '" & Val(TextBox1.Text) & "' and '" & Val(TextBox2.Text) & "' ", conn)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView1.DataSource = mydt
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 where 商品名称= '" & Trim(TextBox3.Text) & "' ", conn)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView1.DataSource = mydt
End Sub
Private Sub DateTimePicker2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker2.ValueChanged
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 where 出产日期 between '" & Trim(Format(DateTimePicker1.Value, "yyyy-MM-dd").ToString()) & "' and '" & Trim(Format(DateTimePicker2.Value, "yyyy-MM-dd").ToString()) & "'", conn)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView1.DataSource = mydt
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
If MsgBox("您确认要删除此商品吗？（将会删除订单中所有相应的订购商品！）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("delete from 商品信息表 where 商品名称='" & Trim(TextBox4.Text) & "' ", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView1.DataSource = data
fresh()
Catch ex As Exception
MsgBox("不存在该商品名称！")
End Try
End If
fresh()
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 order by 商品单价 DESC ", conn)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView1.DataSource = mydt
End Sub
Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
Me.Hide()
Form2.Show()
End Sub
Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
Dim BeforeGoodsName As String = Trim(TextBox5.Text)
Dim AfterGoodsName As String = Trim(TextBox6.Text)
Try
Dim cmd1 As SqlCommand = New SqlCommand("update 商品信息表 set 商品名称 = '" & AfterGoodsName & "'where 商品名称='" & BeforeGoodsName & "'", conn)
conn.Open()
Try
If cmd1.ExecuteNonQuery = 1 Then
Dim cmd2 As SqlCommand = New SqlCommand("update 所有订购商品信息表 set 商品名称 = '" & AfterGoodsName & "'where 商品名称='" & BeforeGoodsName & "'", conn)
cmd2.ExecuteNonQuery()
MsgBox("修改商品名称成功")
fresh()
Else
MsgBox("修改商品名称失败")
fresh()
End If
Catch ex As DuplicateNameException
MsgBox("已有此商品名称")
End Try
conn.Close()
Catch ex As Exception
MsgBox("修改商品名称失败")
End Try
End Sub
Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
daochu(DataGridView1, "商品信息")
End Sub
Public Function daochu(ByVal x As DataGridView, ByVal namestr As String) As Boolean '导出到Excel函数
Try
If x.Rows.Count <= 0 Then '判断记录数,如果没有记录就退出
MessageBox.Show("没有记录可以导出", "没有可以导出的项目", MessageBoxButtons.OK, MessageBoxIcon.Information)
Return False
Else '如果有记录就导出到Excel
Dim xx As Object : Dim yy As Object
xx = CreateObject("Excel.Application") '创建Excel对象
yy = xx.workbooks.add()
Dim i As Integer, u As Integer = 0, v As Integer = 0 '定义循环变量,行列变量
For i = 1 To x.Columns.Count '把表头写入Excel
yy.worksheets(1).cells(1, i) = x.Columns(i - 1).HeaderCell.Value
Next
Dim str(x.Rows.Count - 1, x.Columns.Count - 1) '定义一个二维数组
For u = 1 To x.Rows.Count '行循环
For v = 1 To x.Columns.Count '列循环
If x.Item(v - 1, u - 1).ToString <> "System.Guid" Then
str(u - 1, v - 1) = x.Item(v - 1, u - 1).Value
Else
str(u - 1, v - 1) = x.Item(v - 1, u - 1).Value.ToString
End If
Next
Next
yy.worksheets(1).range("A2").Resize(x.Rows.Count, x.Columns.Count).Value = str '把数组一起写入Excel
yy.worksheets(1).Cells.EntireColumn.AutoFit() '自动调整Excel列
yy.worksheets(1).name = namestr '表标题写入作为Excel工作表名称
xx.visible = True '设置Excel可见
yy = Nothing '销毁组建释放资源
xx = Nothing '销毁组建释放资源
End If
Return True
Catch ex As Exception '错误处理
MessageBox.Show(Err.Description.ToString, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error) '出错提示
Return False
End Try
End Function
End Class
Imports System.Data.SqlClient
Public Class Form5
Dim connstr As String = "data source=localhost;initial catalog=Database project design;integrated security=SSPI"
Dim conn As SqlConnection = New SqlConnection(connstr)
Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
fresh1()
fresh2()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim data As DataTable
data = DataGridView1.DataSource
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 客户订购信息表 ", conn)
Dim cmdb As SqlCommandBuilder = New SqlCommandBuilder(myadpt)
Dim changedData As DataTable = data.GetChanges()
Try
If Not changedData Is Nothing Then
myadpt.Update(changedData)
data.AcceptChanges()
End If
fresh1()
MsgBox("修改订单信息成功")
Catch ex As Exception
MsgBox("修改订单信息失败，请检查是否存在该企业名称和联系人名称！")
fresh1()
End Try
End Sub
Sub fresh1()
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 客户订购信息表,企业客户信息表,联系人信息表 where 客户订购信息表.企业名称 = 企业客户信息表.企业名称 and 客户订购信息表.联系人姓名 = 联系人信息表.联系人姓名 ", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView1.DataSource = data
Catch ex As Exception
MsgBox("企业名称不存在！ 或是 联系人姓名不存在！")
End Try
End Sub
Sub fresh2()
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 所有订购商品信息表,客户订购信息表,商品信息表 where 所有订购商品信息表.订单号=客户订购信息表.订单号 and 所有订购商品信息表.商品名称=商品信息表.商品名称", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView2.DataSource = data
Catch ex As Exception
MsgBox("订单号不存在！ 或是 商品名称不存在！")
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim str() As String = {TextBox1.Text, TextBox2.Text, Format(DateAdd("d", 1, DateTimePicker1.Value), "yyyy-MM-dd").ToString(),
Format(DateTimePicker2.Value, "yyyy-MM-dd-hh:mm:ss").ToString(), TextBox3.Text, TextBox4.Text, TextBox12.Text}
Dim mycmd As String = "insert into 客户订购信息表(企业名称,订单号,下单日期,到货日期,送货地址,特殊说明,联系人姓名) values('"
For i% = 0 To 5
mycmd = mycmd & Trim(str(i)) & "','"
Next
mycmd = mycmd & str(6) & "')"
Dim conn As SqlConnection = New SqlConnection(connstr)
conn.Open()
Dim cmd As SqlCommand = New SqlCommand(mycmd, conn)
Try
If cmd.ExecuteNonQuery = 1 Then
MsgBox("添加订单成功")
Else
MsgBox("添加订单失败")
End If
conn.Close()
fresh1()
Catch ex As Exception
MsgBox("企业客户不存在！或是 联系人不存在！或是 添加了重复的客户名称和订单号或联系人！")
End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
If MsgBox("您确认要删除此订单吗？请与客户沟通后确认其是否删除订单？（将会删除订单下所有订购商品）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("delete from 客户订购信息表 where 订单号='" & Trim(TextBox5.Text) & "'", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView1.DataSource = data
MsgBox("删除订单成功！")
fresh1()
Catch ex As Exception
MsgBox("删除订单失败！")
End Try
End If
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Try
If MsgBox("点击‘是’选择直接删除该企业客户（包括其所有联系人和订单信息)，点击‘否’选择仅删除该企业下所有订单", MsgBoxStyle.YesNoCancel) = MsgBoxResult.No Then
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("delete from 客户订购信息表 where 企业名称='" & Trim(TextBox6.Text) & "'", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView1.DataSource = data
MsgBox("删除企业成功")
fresh1()
ElseIf MsgBox("点击‘是’选择直接删除该企业客户（包括其所有联系人和订单信息)，点击‘否’选择仅删除该企业下所有订单", MsgBoxStyle.YesNoCancel) = MsgBoxResult.Yes Then
Dim myadpt2 As SqlDataAdapter = New SqlDataAdapter("delete from 客户订购信息表 where 企业名称='" & Trim(TextBox6.Text) & "'", conn)
Dim data2 As DataTable = New DataTable
myadpt2.Fill(data2)
DataGridView1.DataSource = data2
Dim myadpt1 As SqlDataAdapter = New SqlDataAdapter("delete from 企业客户信息表 where 企业名称='" & Trim(TextBox6.Text) & "'", conn)
Dim data1 As DataTable = New DataTable
myadpt1.Fill(data1)
DataGridView1.DataSource = data1
MsgBox("删除企业成功")
fresh1()
Else
fresh1()
End If
Catch ex As Exception
MsgBox("删除企业失败")
End Try
End Sub
Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
fresh1()
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
fresh2()
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
Dim data As DataTable
data = DataGridView2.DataSource
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 所有订购商品信息表 ", conn)
Dim cmdb As SqlCommandBuilder = New SqlCommandBuilder(myadpt)
Dim changedData As DataTable = data.GetChanges()
If Not changedData Is Nothing Then
myadpt.Update(changedData)
data.AcceptChanges()
End If
fresh2()
MsgBox("修改订单商品信息成功")
Catch ex As Exception
MsgBox("修改订单商品信息失败，请查看是否存在该订单号或商品名称！")
fresh1()
End Try
End Sub
Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
Dim a, b, c As Integer
a = Val(Trim(TextBox8.Text))
b = Val(Trim(TextBox9.Text))
c = Val(Trim(TextBox10.Text))
Try
Dim mycmd As String = "insert into 所有订购商品信息表(商品名称,订购数量,商品单价,订购全额,订单号)values('" & Trim(TextBox7.Text) & "','" & a & "','" & b & "','" & c & "','" & Trim(TextBox11.Text) & "')"
Dim conn As SqlConnection = New SqlConnection(connstr)
conn.Open()
Dim cmd As SqlCommand = New SqlCommand(mycmd, conn)
If cmd.ExecuteNonQuery = 1 Then
MsgBox("添加订单商品成功")
Else
MsgBox("添加订单商品失败")
End If
conn.Close()
fresh2()
Catch ex As Exception
MsgBox("添加了重复的商品！ 或 添加了不存在的商品！或是 添加了不存在的订单号！")
End Try
End Sub
Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
Me.Hide()
Form2.Show()
End Sub
Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 客户订购信息表 where 企业名称 = '" & Trim(TextBox13.Text) & "'", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView1.DataSource = data
Catch ex As Exception
MsgBox("企业名称不存在！")
End Try
End Sub
Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
Try
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 所有订购商品信息表 where 订单号 = '" & Trim(TextBox14.Text) & "'", conn)
Dim data As DataTable = New DataTable
myadpt.Fill(data)
DataGridView2.DataSource = data
Catch ex As Exception
MsgBox("订单号不存在！")
End Try
End Sub
End Class
Imports System.Data.SqlClient
Public Class Form7
Dim connstr As String = "data source=localhost;initial catalog=Database project design;integrated security=SSPI"
Dim conn As SqlConnection = New SqlConnection(connstr)
Sub fresh()
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 采购信息表,商品信息表 where 采购信息表.商品名称=商品信息表.商品名称", connstr)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView2.DataSource = mydt
End Sub
Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
fresh1()
fresh()
End Sub
Sub fresh1()
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select 所有订购商品信息表.商品名称,SUM(所有订购商品信息表.订购数量) from 客户订购信息表,所有订购商品信息表 where 客户订购信息表.订单号=所有订购商品信息表.订单号 GROUP BY 所有订购商品信息表.商品名称", connstr)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView1.DataSource = mydt
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim num As Integer = Val(TextBox2.Text) * Val(TextBox4.Text)
Dim cmd As String = "insert into 采购信息表(商品名称,采购数量,计量单位,采购价格,采购日期,总价) values('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" & Trim(TextBox3.Text) & "','" & Trim(TextBox4.Text) & "','" & Trim(Format(DateTimePicker1.Value, "yyyy-MM-dd").ToString()) & "','" & num & "')"
Dim conn As SqlConnection = New SqlConnection(connstr)
conn.Open()
Dim mycmd As SqlCommand = New SqlCommand(cmd, conn)
Try
If mycmd.ExecuteNonQuery = 1 Then
fresh()
MsgBox("添加采购回执信息成功！")
Else
MsgBox("添加采购回执信息失败！")
End If
Catch ex As Exception
MsgBox("添加采购回执信息失败！")
End Try
conn.Close()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
fresh1()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
daochu(DataGridView1, "采购单" & Trim(Format(DateTimePicker1.Value, "yyyy-MM-dd").ToString()))
End Sub
Public Function daochu(ByVal x As DataGridView, ByVal namestr As String) As Boolean '导出到Excel函数
Try
If x.Rows.Count <= 0 Then '判断记录数,如果没有记录就退出
MessageBox.Show("没有记录可以导出", "没有可以导出的项目", MessageBoxButtons.OK, MessageBoxIcon.Information)
Return False
Else '如果有记录就导出到Excel
Dim xx As Object : Dim yy As Object
xx = CreateObject("Excel.Application") '创建Excel对象
yy = xx.workbooks.add()
Dim i As Integer, u As Integer = 0, v As Integer = 0 '定义循环变量,行列变量
For i = 1 To x.Columns.Count '把表头写入Excel
yy.worksheets(1).cells(1, i) = x.Columns(i - 1).HeaderCell.Value
Next
Dim str(x.Rows.Count - 1, x.Columns.Count - 1) '定义一个二维数组
For u = 1 To x.Rows.Count '行循环
For v = 1 To x.Columns.Count '列循环
If x.Item(v - 1, u - 1).ToString <> "System.Guid" Then
str(u - 1, v - 1) = x.Item(v - 1, u - 1).Value
Else
str(u - 1, v - 1) = x.Item(v - 1, u - 1).Value.ToString
End If
Next
Next
yy.worksheets(1).range("A2").Resize(x.Rows.Count, x.Columns.Count).Value = str '把数组一起写入Excel
yy.worksheets(1).Cells.EntireColumn.AutoFit() '自动调整Excel列
yy.worksheets(1).name = namestr '表标题写入作为Excel工作表名称
xx.visible = True '设置Excel可见
yy = Nothing '销毁组建释放资源
xx = Nothing '销毁组建释放资源
End If
Return True
Catch ex As Exception '错误处理
MessageBox.Show(Err.Description.ToString, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error) '出错提示
Return False
End Try
End Function
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Me.Hide()
Form2.Show()
End Sub
Private Sub DateTimePicker2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker2.ValueChanged
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select 所有订购商品信息表.商品名称,SUM(所有订购商品信息表.订购数量) from 客户订购信息表,所有订购商品信息表 where 下单日期='" & Trim(Format(DateAdd("d", -1, DateTimePicker2.Value), "yyyy-MM-dd").ToString()) & "' AND 客户订购信息表.订单号=所有订购商品信息表.订单号 GROUP BY 所有订购商品信息表.商品名称", connstr)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView1.DataSource = mydt
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
fresh()
End Sub
Private Sub DateTimePicker3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker3.ValueChanged
Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 采购信息表 where 采购日期='" & Trim(Format(DateAdd("d", -1, DateTimePicker3.Value))) & "'", connstr)
Dim mydt As DataTable = New DataTable
myadpt.Fill(mydt)
DataGridView2.DataSource = mydt
End Sub
End Class
