frmMedRkTotals.vb
上传用户:wj57717022
上传日期:2014-12-16
资源大小:4093k
文件大小:18k
- Public Class frmMedRkTotals
- Inherits System.Windows.Forms.Form
- #Region " Windows 窗体设计器生成的代码 "
- Public Sub New()
- MyBase.New()
- '该调用是 Windows 窗体设计器所必需的。
- InitializeComponent()
- '在 InitializeComponent() 调用之后添加任何初始化
- End Sub
- '窗体重写 dispose 以清理组件列表。
- Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
- If disposing Then
- If Not (components Is Nothing) Then
- components.Dispose()
- End If
- End If
- MyBase.Dispose(disposing)
- End Sub
- 'Windows 窗体设计器所必需的
- Private components As System.ComponentModel.IContainer
- '注意: 以下过程是 Windows 窗体设计器所必需的
- '可以使用 Windows 窗体设计器修改此过程。
- '不要使用代码编辑器修改它。
- Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
- Friend WithEvents Label1 As System.Windows.Forms.Label
- Friend WithEvents Label2 As System.Windows.Forms.Label
- Friend WithEvents Label3 As System.Windows.Forms.Label
- Friend WithEvents txtmedname As System.Windows.Forms.TextBox
- Friend WithEvents Label4 As System.Windows.Forms.Label
- Friend WithEvents Label5 As System.Windows.Forms.Label
- Friend WithEvents txtkhname As System.Windows.Forms.TextBox
- Friend WithEvents btnqueryall As System.Windows.Forms.Button
- Friend WithEvents btnQuery As System.Windows.Forms.Button
- Friend WithEvents btnPrint As System.Windows.Forms.Button
- Friend WithEvents txtmedtype As System.Windows.Forms.ComboBox
- Friend WithEvents txtbegintime As System.Windows.Forms.DateTimePicker
- Friend WithEvents txtendtime As System.Windows.Forms.DateTimePicker
- Friend WithEvents Panel1 As System.Windows.Forms.Panel
- Friend WithEvents Panel2 As System.Windows.Forms.Panel
- Friend WithEvents GroupBox2 As System.Windows.Forms.GroupBox
- Friend WithEvents tabtable As System.Windows.Forms.TabControl
- Friend WithEvents tbmed As System.Windows.Forms.TabPage
- Friend WithEvents tbtype As System.Windows.Forms.TabPage
- Friend WithEvents tbkh As System.Windows.Forms.TabPage
- Friend WithEvents tbywy As System.Windows.Forms.TabPage
- Friend WithEvents Panel3 As System.Windows.Forms.Panel
- Friend WithEvents dgview As System.Windows.Forms.DataGrid
- <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
- Me.GroupBox1 = New System.Windows.Forms.GroupBox
- Me.txtmedtype = New System.Windows.Forms.ComboBox
- Me.txtkhname = New System.Windows.Forms.TextBox
- Me.Label5 = New System.Windows.Forms.Label
- Me.Label4 = New System.Windows.Forms.Label
- Me.txtmedname = New System.Windows.Forms.TextBox
- Me.Label3 = New System.Windows.Forms.Label
- Me.txtendtime = New System.Windows.Forms.DateTimePicker
- Me.txtbegintime = New System.Windows.Forms.DateTimePicker
- Me.Label2 = New System.Windows.Forms.Label
- Me.Label1 = New System.Windows.Forms.Label
- Me.btnPrint = New System.Windows.Forms.Button
- Me.btnQuery = New System.Windows.Forms.Button
- Me.btnqueryall = New System.Windows.Forms.Button
- Me.Panel1 = New System.Windows.Forms.Panel
- Me.Panel2 = New System.Windows.Forms.Panel
- Me.GroupBox2 = New System.Windows.Forms.GroupBox
- Me.tabtable = New System.Windows.Forms.TabControl
- Me.tbmed = New System.Windows.Forms.TabPage
- Me.tbtype = New System.Windows.Forms.TabPage
- Me.tbkh = New System.Windows.Forms.TabPage
- Me.tbywy = New System.Windows.Forms.TabPage
- Me.Panel3 = New System.Windows.Forms.Panel
- Me.dgview = New System.Windows.Forms.DataGrid
- Me.GroupBox1.SuspendLayout()
- Me.Panel1.SuspendLayout()
- Me.Panel2.SuspendLayout()
- Me.GroupBox2.SuspendLayout()
- Me.tabtable.SuspendLayout()
- Me.Panel3.SuspendLayout()
- CType(Me.dgview, System.ComponentModel.ISupportInitialize).BeginInit()
- Me.SuspendLayout()
- '
- 'GroupBox1
- '
- Me.GroupBox1.Controls.Add(Me.txtmedtype)
- Me.GroupBox1.Controls.Add(Me.txtkhname)
- Me.GroupBox1.Controls.Add(Me.Label5)
- Me.GroupBox1.Controls.Add(Me.Label4)
- Me.GroupBox1.Controls.Add(Me.txtmedname)
- Me.GroupBox1.Controls.Add(Me.Label3)
- Me.GroupBox1.Controls.Add(Me.txtendtime)
- Me.GroupBox1.Controls.Add(Me.txtbegintime)
- Me.GroupBox1.Controls.Add(Me.Label2)
- Me.GroupBox1.Controls.Add(Me.Label1)
- Me.GroupBox1.Dock = System.Windows.Forms.DockStyle.Top
- Me.GroupBox1.Location = New System.Drawing.Point(0, 0)
- Me.GroupBox1.Name = "GroupBox1"
- Me.GroupBox1.Size = New System.Drawing.Size(694, 112)
- Me.GroupBox1.TabIndex = 0
- Me.GroupBox1.TabStop = False
- '
- 'txtmedtype
- '
- Me.txtmedtype.Location = New System.Drawing.Point(544, 64)
- Me.txtmedtype.Name = "txtmedtype"
- Me.txtmedtype.Size = New System.Drawing.Size(136, 23)
- Me.txtmedtype.TabIndex = 10
- '
- 'txtkhname
- '
- Me.txtkhname.Location = New System.Drawing.Point(85, 62)
- Me.txtkhname.Name = "txtkhname"
- Me.txtkhname.Size = New System.Drawing.Size(363, 25)
- Me.txtkhname.TabIndex = 9
- Me.txtkhname.Text = ""
- '
- 'Label5
- '
- Me.Label5.AutoSize = True
- Me.Label5.Location = New System.Drawing.Point(8, 72)
- Me.Label5.Name = "Label5"
- Me.Label5.Size = New System.Drawing.Size(75, 21)
- Me.Label5.TabIndex = 8
- Me.Label5.Text = "客户名称:"
- '
- 'Label4
- '
- Me.Label4.AutoSize = True
- Me.Label4.Location = New System.Drawing.Point(472, 72)
- Me.Label4.Name = "Label4"
- Me.Label4.Size = New System.Drawing.Size(75, 21)
- Me.Label4.TabIndex = 6
- Me.Label4.Text = "药品类别:"
- '
- 'txtmedname
- '
- Me.txtmedname.Location = New System.Drawing.Point(544, 21)
- Me.txtmedname.Name = "txtmedname"
- Me.txtmedname.Size = New System.Drawing.Size(136, 25)
- Me.txtmedname.TabIndex = 5
- Me.txtmedname.Text = ""
- '
- 'Label3
- '
- Me.Label3.AutoSize = True
- Me.Label3.Location = New System.Drawing.Point(469, 31)
- Me.Label3.Name = "Label3"
- Me.Label3.Size = New System.Drawing.Size(75, 21)
- Me.Label3.TabIndex = 4
- Me.Label3.Text = "药品名称:"
- '
- 'txtendtime
- '
- Me.txtendtime.Location = New System.Drawing.Point(312, 21)
- Me.txtendtime.Name = "txtendtime"
- Me.txtendtime.Size = New System.Drawing.Size(136, 25)
- Me.txtendtime.TabIndex = 3
- Me.txtendtime.Value = New Date(2006, 2, 23, 0, 0, 0, 0)
- '
- 'txtbegintime
- '
- Me.txtbegintime.Location = New System.Drawing.Point(85, 21)
- Me.txtbegintime.Name = "txtbegintime"
- Me.txtbegintime.Size = New System.Drawing.Size(136, 25)
- Me.txtbegintime.TabIndex = 2
- Me.txtbegintime.Value = New Date(2006, 2, 23, 0, 0, 0, 0)
- '
- 'Label2
- '
- Me.Label2.AutoSize = True
- Me.Label2.Location = New System.Drawing.Point(235, 31)
- Me.Label2.Name = "Label2"
- Me.Label2.Size = New System.Drawing.Size(75, 21)
- Me.Label2.TabIndex = 1
- Me.Label2.Text = "结束日期:"
- '
- 'Label1
- '
- Me.Label1.AutoSize = True
- Me.Label1.Location = New System.Drawing.Point(8, 31)
- Me.Label1.Name = "Label1"
- Me.Label1.Size = New System.Drawing.Size(75, 21)
- Me.Label1.TabIndex = 0
- Me.Label1.Text = "开始日期:"
- '
- 'btnPrint
- '
- Me.btnPrint.Dock = System.Windows.Forms.DockStyle.Top
- Me.btnPrint.FlatStyle = System.Windows.Forms.FlatStyle.Popup
- Me.btnPrint.Location = New System.Drawing.Point(0, 66)
- Me.btnPrint.Name = "btnPrint"
- Me.btnPrint.Size = New System.Drawing.Size(96, 30)
- Me.btnPrint.TabIndex = 2
- Me.btnPrint.Text = "打印"
- '
- 'btnQuery
- '
- Me.btnQuery.Dock = System.Windows.Forms.DockStyle.Top
- Me.btnQuery.FlatStyle = System.Windows.Forms.FlatStyle.Popup
- Me.btnQuery.Location = New System.Drawing.Point(0, 31)
- Me.btnQuery.Name = "btnQuery"
- Me.btnQuery.Size = New System.Drawing.Size(96, 35)
- Me.btnQuery.TabIndex = 1
- Me.btnQuery.Text = "条件查询"
- '
- 'btnqueryall
- '
- Me.btnqueryall.Dock = System.Windows.Forms.DockStyle.Top
- Me.btnqueryall.FlatStyle = System.Windows.Forms.FlatStyle.Popup
- Me.btnqueryall.Location = New System.Drawing.Point(0, 0)
- Me.btnqueryall.Name = "btnqueryall"
- Me.btnqueryall.Size = New System.Drawing.Size(96, 31)
- Me.btnqueryall.TabIndex = 0
- Me.btnqueryall.Text = "全部查询"
- '
- 'Panel1
- '
- Me.Panel1.Controls.Add(Me.btnPrint)
- Me.Panel1.Controls.Add(Me.btnQuery)
- Me.Panel1.Controls.Add(Me.btnqueryall)
- Me.Panel1.Dock = System.Windows.Forms.DockStyle.Right
- Me.Panel1.Location = New System.Drawing.Point(598, 112)
- Me.Panel1.Name = "Panel1"
- Me.Panel1.Size = New System.Drawing.Size(96, 398)
- Me.Panel1.TabIndex = 4
- '
- 'Panel2
- '
- Me.Panel2.Controls.Add(Me.GroupBox2)
- Me.Panel2.Dock = System.Windows.Forms.DockStyle.Top
- Me.Panel2.Location = New System.Drawing.Point(0, 112)
- Me.Panel2.Name = "Panel2"
- Me.Panel2.Size = New System.Drawing.Size(598, 63)
- Me.Panel2.TabIndex = 5
- '
- 'GroupBox2
- '
- Me.GroupBox2.Controls.Add(Me.tabtable)
- Me.GroupBox2.Dock = System.Windows.Forms.DockStyle.Fill
- Me.GroupBox2.Location = New System.Drawing.Point(0, 0)
- Me.GroupBox2.Name = "GroupBox2"
- Me.GroupBox2.Size = New System.Drawing.Size(598, 63)
- Me.GroupBox2.TabIndex = 0
- Me.GroupBox2.TabStop = False
- '
- 'tabtable
- '
- Me.tabtable.Appearance = System.Windows.Forms.TabAppearance.FlatButtons
- Me.tabtable.Controls.Add(Me.tbmed)
- Me.tabtable.Controls.Add(Me.tbtype)
- Me.tabtable.Controls.Add(Me.tbkh)
- Me.tabtable.Controls.Add(Me.tbywy)
- Me.tabtable.Dock = System.Windows.Forms.DockStyle.Fill
- Me.tabtable.Location = New System.Drawing.Point(3, 21)
- Me.tabtable.Name = "tabtable"
- Me.tabtable.SelectedIndex = 0
- Me.tabtable.Size = New System.Drawing.Size(592, 39)
- Me.tabtable.TabIndex = 5
- '
- 'tbmed
- '
- Me.tbmed.Location = New System.Drawing.Point(4, 27)
- Me.tbmed.Name = "tbmed"
- Me.tbmed.Size = New System.Drawing.Size(584, 8)
- Me.tbmed.TabIndex = 0
- Me.tbmed.Text = "按药品品种"
- '
- 'tbtype
- '
- Me.tbtype.Location = New System.Drawing.Point(4, 27)
- Me.tbtype.Name = "tbtype"
- Me.tbtype.Size = New System.Drawing.Size(827, 6)
- Me.tbtype.TabIndex = 1
- Me.tbtype.Text = "按药品类别"
- Me.tbtype.Visible = False
- '
- 'tbkh
- '
- Me.tbkh.Location = New System.Drawing.Point(4, 27)
- Me.tbkh.Name = "tbkh"
- Me.tbkh.Size = New System.Drawing.Size(827, 6)
- Me.tbkh.TabIndex = 2
- Me.tbkh.Text = "按供应商"
- Me.tbkh.Visible = False
- '
- 'tbywy
- '
- Me.tbywy.Location = New System.Drawing.Point(4, 27)
- Me.tbywy.Name = "tbywy"
- Me.tbywy.Size = New System.Drawing.Size(827, 6)
- Me.tbywy.TabIndex = 3
- Me.tbywy.Text = "按业务员"
- Me.tbywy.Visible = False
- '
- 'Panel3
- '
- Me.Panel3.Controls.Add(Me.dgview)
- Me.Panel3.Dock = System.Windows.Forms.DockStyle.Fill
- Me.Panel3.Location = New System.Drawing.Point(0, 175)
- Me.Panel3.Name = "Panel3"
- Me.Panel3.Size = New System.Drawing.Size(598, 335)
- Me.Panel3.TabIndex = 6
- '
- 'dgview
- '
- Me.dgview.DataMember = ""
- Me.dgview.Dock = System.Windows.Forms.DockStyle.Fill
- Me.dgview.HeaderForeColor = System.Drawing.SystemColors.ControlText
- Me.dgview.Location = New System.Drawing.Point(0, 0)
- Me.dgview.Name = "dgview"
- Me.dgview.Size = New System.Drawing.Size(598, 335)
- Me.dgview.TabIndex = 3
- '
- 'frmMedRkTotals
- '
- Me.AutoScaleBaseSize = New System.Drawing.Size(8, 18)
- Me.ClientSize = New System.Drawing.Size(694, 510)
- Me.Controls.Add(Me.Panel3)
- Me.Controls.Add(Me.Panel2)
- Me.Controls.Add(Me.Panel1)
- Me.Controls.Add(Me.GroupBox1)
- Me.Name = "frmMedRkTotals"
- Me.Text = "药品入库统计"
- Me.WindowState = System.Windows.Forms.FormWindowState.Maximized
- Me.GroupBox1.ResumeLayout(False)
- Me.Panel1.ResumeLayout(False)
- Me.Panel2.ResumeLayout(False)
- Me.GroupBox2.ResumeLayout(False)
- Me.tabtable.ResumeLayout(False)
- Me.Panel3.ResumeLayout(False)
- CType(Me.dgview, System.ComponentModel.ISupportInitialize).EndInit()
- Me.ResumeLayout(False)
- End Sub
- #End Region
- Public lsweb As New jxcmanage
- Private Sub frmMedRkTotals_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
- txtbegintime.Value = Today.ToString
- txtendtime.Value = Today.ToString
- End Sub
- Private Sub btnQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuery.Click
- Dim strmedname, strmedtype, strkhname, strquery As String
- If txtmedname.Text = "" Then
- strmedname = ""
- Else
- strmedname = " and 药品名称='" & txtmedname.Text & "'"
- End If
- If txtmedtype.Text = "" Then
- strmedtype = ""
- Else
- strmedtype = " and substring(药品编号,1,1)='" & txtmedtype.Text.Substring(0, 1) & "'"
- End If
- If txtkhname.Text = "" Then
- strkhname = ""
- Else
- strkhname = " and 客户名称='" & txtkhname.Text & "'"
- End If
- Dim strsql As String
- strsql = " where 日期 between '" & txtbegintime.Value & "' and '" & txtendtime.Value & "'" & strmedname & strmedtype & strkhname
- Select Case tabtable.SelectedTab.Text
- Case "按药品品种"
- strquery = "select 药品编号,药品名称,药品规格,单位,包装,sum(数量) as 数量,avg(进价) as 进价,sum(数量*进价) as 金额,批号,产地,有效期,avg(批发价) as 批发价,avg(零售价) as 零售价 from wz_rk" & strsql & " group by 药品编号,药品名称,药品规格,单位,包装,批号,产地,有效期 "
- Case "按药品类别"
- strquery = "select SUM(CASE WHEN substring(药品编号,1,1)='P' THEN 金额 ELSE 0.00 END) AS 片剂,SUM(CASE WHEN substring(药品编号,1,1)='Z' THEN 金额 ELSE 0.00 END) AS 针剂,SUM(CASE WHEN substring(药品编号,1,1)='F' THEN 金额 ELSE 0.00 END) AS 粉剂,SUM(CASE WHEN substring(药品编号,1,1)='K' THEN 金额 ELSE 0.00 END) AS 口服,SUM(CASE WHEN substring(药品编号,1,1)='W' THEN 金额 ELSE 0.00 END) AS 外用,SUM(CASE WHEN substring(药品编号,1,1)='Q' THEN 金额 ELSE 0.00 END) AS 器械,SUM(CASE WHEN substring(药品编号,1,1)='B' THEN 金额 ELSE 0.00 END) AS 保健,SUM(金额) AS 合计 FROM wz_rk"
- Case "按供应商"
- strquery = "select 客户编号,客户名称,药品编号,药品名称,药品规格,包装,单位,sum(数量) as 数量,avg(进价) as 进价,sum(进价*数量) 金额,产地,批号,有效期,avg(批发价) as 批发价,avg(零售价) as 零售价 from wz_rk" & strsql & " group by 客户编号,客户名称,药品编号,药品名称,药品规格,包装,单位,产地,批号,有效期"
- Case "按业务员"
- strquery = "select 操作员 as 业务员,药品编号,药品名称,药品规格,包装,单位,sum(数量) as 数量,avg(进价) as 进价,sum(进价*数量) 金额,产地,批号,有效期,avg(批发价) as 批发价,avg(零售价) as 零售价 from wz_rk" & strsql & " group by 操作员,药品编号,药品名称,药品规格,包装,单位,产地,批号,有效期"
- End Select
- lsweb.runSql(strquery)
- Dim dslocal As DataSet
- dslocal = lsweb.runSql(strquery)
- dgview.DataSource = dslocal.Tables(0).DefaultView
- End Sub
- Private Sub btnqueryall_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnqueryall.Click
- Dim strqueryes As String
- Dim dslocal As DataSet
- Select Case tabtable.SelectedTab.Text
- Case "按药品品种"
- strqueryes = "select 药品编号,药品名称,药品规格,单位,包装,sum(数量) as 数量,avg(进价) as 进价,sum(数量*进价) as 金额,批号,产地,有效期,avg(批发价) as 批发价,avg(零售价) as 零售价 from wz_rk group by 药品编号,药品名称,药品规格,单位,包装,批号,产地,有效期 "
- Case "按药品类别"
- strqueryes = "select SUM(CASE WHEN substring(药品编号,1,1)='P' THEN 金额 ELSE 0.00 END) AS 片剂,SUM(CASE WHEN substring(药品编号,1,1)='Z' THEN 金额 ELSE 0.00 END) AS 针剂,SUM(CASE WHEN substring(药品编号,1,1)='F' THEN 金额 ELSE 0.00 END) AS 粉剂,SUM(CASE WHEN substring(药品编号,1,1)='K' THEN 金额 ELSE 0.00 END) AS 口服,SUM(CASE WHEN substring(药品编号,1,1)='W' THEN 金额 ELSE 0.00 END) AS 外用,SUM(CASE WHEN substring(药品编号,1,1)='Q' THEN 金额 ELSE 0.00 END) AS 器械,SUM(CASE WHEN substring(药品编号,1,1)='B' THEN 金额 ELSE 0.00 END) AS 保健,SUM(金额) AS 合计 FROM wz_rk"
- Case "按供应商"
- strqueryes = "select 客户编号,客户名称,药品编号,药品名称,药品规格,包装,单位,sum(数量) as 数量,avg(进价) as 进价,sum(进价*数量) 金额,产地,批号,有效期,avg(批发价) as 批发价,avg(零售价) as 零售价 from wz_rk group by 客户编号,客户名称,药品编号,药品名称,药品规格,包装,单位,产地,批号,有效期"
- Case "按业务员"
- strqueryes = "select 操作员 as 业务员,药品编号,药品名称,药品规格,包装,单位,sum(数量) as 数量,avg(进价) as 进价,sum(进价*数量) 金额,产地,批号,有效期,avg(批发价) as 批发价,avg(零售价) as 零售价 from wz_rk group by 操作员,药品编号,药品名称,药品规格,包装,单位,产地,批号,有效期"
- End Select
- dslocal = lsweb.runSql(strqueryes)
- dgview.DataSource = dslocal.Tables(0).DefaultView
- End Sub
- End Class