frmCKDateTotal.vb
上传用户:wj57717022
上传日期:2014-12-16
资源大小:4093k
文件大小:18k
- Public Class frmDateTotal
- 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 GroupBox2 As System.Windows.Forms.GroupBox
- Friend WithEvents Label1 As System.Windows.Forms.Label
- Friend WithEvents txtYear As System.Windows.Forms.DomainUpDown
- Friend WithEvents Label2 As System.Windows.Forms.Label
- Friend WithEvents txtMedName As System.Windows.Forms.TextBox
- Friend WithEvents Label3 As System.Windows.Forms.Label
- Friend WithEvents txtkhname As System.Windows.Forms.TextBox
- Friend WithEvents Label4 As System.Windows.Forms.Label
- Friend WithEvents txtHw As System.Windows.Forms.ComboBox
- Friend WithEvents Label5 As System.Windows.Forms.Label
- Friend WithEvents txtXsy As System.Windows.Forms.ComboBox
- Friend WithEvents Label6 As System.Windows.Forms.Label
- Friend WithEvents txtmedType As System.Windows.Forms.ComboBox
- 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 GroupBox3 As System.Windows.Forms.GroupBox
- Friend WithEvents tabtable As System.Windows.Forms.TabControl
- Friend WithEvents tbmonth As System.Windows.Forms.TabPage
- Friend WithEvents tbjd As System.Windows.Forms.TabPage
- Friend WithEvents tbyear As System.Windows.Forms.TabPage
- Friend WithEvents Panel1 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.Label6 = New System.Windows.Forms.Label
- Me.txtXsy = New System.Windows.Forms.ComboBox
- Me.Label5 = New System.Windows.Forms.Label
- Me.txtHw = New System.Windows.Forms.ComboBox
- Me.Label4 = New System.Windows.Forms.Label
- Me.txtkhname = New System.Windows.Forms.TextBox
- Me.Label3 = New System.Windows.Forms.Label
- Me.txtMedName = New System.Windows.Forms.TextBox
- Me.Label2 = New System.Windows.Forms.Label
- Me.txtYear = New System.Windows.Forms.DomainUpDown
- Me.Label1 = New System.Windows.Forms.Label
- Me.GroupBox2 = New System.Windows.Forms.GroupBox
- Me.btnPrint = New System.Windows.Forms.Button
- Me.btnQuery = New System.Windows.Forms.Button
- Me.btnQueryAll = New System.Windows.Forms.Button
- Me.GroupBox3 = New System.Windows.Forms.GroupBox
- Me.tabtable = New System.Windows.Forms.TabControl
- Me.tbmonth = New System.Windows.Forms.TabPage
- Me.tbjd = New System.Windows.Forms.TabPage
- Me.tbyear = New System.Windows.Forms.TabPage
- Me.Panel1 = New System.Windows.Forms.Panel
- Me.dgview = New System.Windows.Forms.DataGrid
- Me.GroupBox1.SuspendLayout()
- Me.GroupBox2.SuspendLayout()
- Me.GroupBox3.SuspendLayout()
- Me.tabtable.SuspendLayout()
- Me.Panel1.SuspendLayout()
- CType(Me.dgview, System.ComponentModel.ISupportInitialize).BeginInit()
- Me.SuspendLayout()
- '
- 'GroupBox1
- '
- Me.GroupBox1.Controls.Add(Me.txtmedType)
- Me.GroupBox1.Controls.Add(Me.Label6)
- Me.GroupBox1.Controls.Add(Me.txtXsy)
- Me.GroupBox1.Controls.Add(Me.Label5)
- Me.GroupBox1.Controls.Add(Me.txtHw)
- Me.GroupBox1.Controls.Add(Me.Label4)
- Me.GroupBox1.Controls.Add(Me.txtkhname)
- Me.GroupBox1.Controls.Add(Me.Label3)
- Me.GroupBox1.Controls.Add(Me.txtMedName)
- Me.GroupBox1.Controls.Add(Me.Label2)
- Me.GroupBox1.Controls.Add(Me.txtYear)
- 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(704, 96)
- Me.GroupBox1.TabIndex = 0
- Me.GroupBox1.TabStop = False
- '
- 'txtmedType
- '
- Me.txtmedType.Location = New System.Drawing.Point(432, 48)
- Me.txtmedType.Name = "txtmedType"
- Me.txtmedType.Size = New System.Drawing.Size(136, 20)
- Me.txtmedType.TabIndex = 13
- '
- 'Label6
- '
- Me.Label6.AutoSize = True
- Me.Label6.Location = New System.Drawing.Point(368, 56)
- Me.Label6.Name = "Label6"
- Me.Label6.Size = New System.Drawing.Size(66, 17)
- Me.Label6.TabIndex = 12
- Me.Label6.Text = "药品类型:"
- '
- 'txtXsy
- '
- Me.txtXsy.Location = New System.Drawing.Point(240, 48)
- Me.txtXsy.Name = "txtXsy"
- Me.txtXsy.Size = New System.Drawing.Size(104, 20)
- Me.txtXsy.TabIndex = 11
- '
- 'Label5
- '
- Me.Label5.AutoSize = True
- Me.Label5.Location = New System.Drawing.Point(184, 56)
- Me.Label5.Name = "Label5"
- Me.Label5.Size = New System.Drawing.Size(54, 17)
- Me.Label5.TabIndex = 10
- Me.Label5.Text = "销售员:"
- '
- 'txtHw
- '
- Me.txtHw.Location = New System.Drawing.Point(72, 48)
- Me.txtHw.Name = "txtHw"
- Me.txtHw.Size = New System.Drawing.Size(80, 20)
- Me.txtHw.TabIndex = 9
- '
- 'Label4
- '
- Me.Label4.AutoSize = True
- Me.Label4.Location = New System.Drawing.Point(32, 56)
- Me.Label4.Name = "Label4"
- Me.Label4.Size = New System.Drawing.Size(42, 17)
- Me.Label4.TabIndex = 8
- Me.Label4.Text = "货位:"
- '
- 'txtkhname
- '
- Me.txtkhname.Location = New System.Drawing.Point(432, 16)
- Me.txtkhname.Name = "txtkhname"
- Me.txtkhname.Size = New System.Drawing.Size(136, 21)
- Me.txtkhname.TabIndex = 7
- Me.txtkhname.Text = ""
- '
- 'Label3
- '
- Me.Label3.AutoSize = True
- Me.Label3.Location = New System.Drawing.Point(368, 24)
- Me.Label3.Name = "Label3"
- Me.Label3.Size = New System.Drawing.Size(66, 17)
- Me.Label3.TabIndex = 6
- Me.Label3.Text = "客户名称:"
- '
- 'txtMedName
- '
- Me.txtMedName.Location = New System.Drawing.Point(240, 16)
- Me.txtMedName.Name = "txtMedName"
- Me.txtMedName.TabIndex = 5
- Me.txtMedName.Text = ""
- '
- 'Label2
- '
- Me.Label2.AutoSize = True
- Me.Label2.Location = New System.Drawing.Point(176, 24)
- Me.Label2.Name = "Label2"
- Me.Label2.Size = New System.Drawing.Size(66, 17)
- Me.Label2.TabIndex = 4
- Me.Label2.Text = "药品名称:"
- '
- 'txtYear
- '
- Me.txtYear.Items.Add("2006")
- Me.txtYear.Items.Add("2007")
- Me.txtYear.Items.Add("2008")
- Me.txtYear.Items.Add("2009")
- Me.txtYear.Items.Add("2010")
- Me.txtYear.Location = New System.Drawing.Point(72, 16)
- Me.txtYear.Name = "txtYear"
- Me.txtYear.Size = New System.Drawing.Size(80, 21)
- Me.txtYear.TabIndex = 3
- '
- 'Label1
- '
- Me.Label1.AutoSize = True
- Me.Label1.Location = New System.Drawing.Point(8, 24)
- Me.Label1.Name = "Label1"
- Me.Label1.Size = New System.Drawing.Size(66, 17)
- Me.Label1.TabIndex = 0
- Me.Label1.Text = "统计年份:"
- '
- 'GroupBox2
- '
- Me.GroupBox2.Controls.Add(Me.btnPrint)
- Me.GroupBox2.Controls.Add(Me.btnQuery)
- Me.GroupBox2.Controls.Add(Me.btnQueryAll)
- Me.GroupBox2.Dock = System.Windows.Forms.DockStyle.Right
- Me.GroupBox2.Location = New System.Drawing.Point(608, 96)
- Me.GroupBox2.Name = "GroupBox2"
- Me.GroupBox2.Size = New System.Drawing.Size(96, 373)
- Me.GroupBox2.TabIndex = 1
- Me.GroupBox2.TabStop = False
- '
- 'btnPrint
- '
- Me.btnPrint.FlatStyle = System.Windows.Forms.FlatStyle.Popup
- Me.btnPrint.Location = New System.Drawing.Point(8, 64)
- Me.btnPrint.Name = "btnPrint"
- Me.btnPrint.TabIndex = 2
- Me.btnPrint.Text = "打印"
- '
- 'btnQuery
- '
- Me.btnQuery.FlatStyle = System.Windows.Forms.FlatStyle.Popup
- Me.btnQuery.Location = New System.Drawing.Point(8, 40)
- Me.btnQuery.Name = "btnQuery"
- Me.btnQuery.TabIndex = 1
- Me.btnQuery.Text = "条件查询"
- '
- 'btnQueryAll
- '
- Me.btnQueryAll.FlatStyle = System.Windows.Forms.FlatStyle.Popup
- Me.btnQueryAll.Location = New System.Drawing.Point(8, 16)
- Me.btnQueryAll.Name = "btnQueryAll"
- Me.btnQueryAll.TabIndex = 0
- Me.btnQueryAll.Text = "全部查询"
- '
- 'GroupBox3
- '
- Me.GroupBox3.Controls.Add(Me.tabtable)
- Me.GroupBox3.Dock = System.Windows.Forms.DockStyle.Top
- Me.GroupBox3.Location = New System.Drawing.Point(0, 96)
- Me.GroupBox3.Name = "GroupBox3"
- Me.GroupBox3.Size = New System.Drawing.Size(608, 48)
- Me.GroupBox3.TabIndex = 4
- Me.GroupBox3.TabStop = False
- '
- 'tabtable
- '
- Me.tabtable.Appearance = System.Windows.Forms.TabAppearance.FlatButtons
- Me.tabtable.Controls.Add(Me.tbmonth)
- Me.tabtable.Controls.Add(Me.tbjd)
- Me.tabtable.Controls.Add(Me.tbyear)
- Me.tabtable.Dock = System.Windows.Forms.DockStyle.Fill
- Me.tabtable.Location = New System.Drawing.Point(3, 17)
- Me.tabtable.Name = "tabtable"
- Me.tabtable.SelectedIndex = 0
- Me.tabtable.Size = New System.Drawing.Size(602, 28)
- Me.tabtable.TabIndex = 3
- '
- 'tbmonth
- '
- Me.tbmonth.Location = New System.Drawing.Point(4, 24)
- Me.tbmonth.Name = "tbmonth"
- Me.tbmonth.Size = New System.Drawing.Size(594, 0)
- Me.tbmonth.TabIndex = 0
- Me.tbmonth.Text = "月比较"
- '
- 'tbjd
- '
- Me.tbjd.Location = New System.Drawing.Point(4, 24)
- Me.tbjd.Name = "tbjd"
- Me.tbjd.Size = New System.Drawing.Size(680, 0)
- Me.tbjd.TabIndex = 1
- Me.tbjd.Text = "季比较"
- Me.tbjd.Visible = False
- '
- 'tbyear
- '
- Me.tbyear.Location = New System.Drawing.Point(4, 24)
- Me.tbyear.Name = "tbyear"
- Me.tbyear.Size = New System.Drawing.Size(680, 0)
- Me.tbyear.TabIndex = 2
- Me.tbyear.Text = "年比较"
- Me.tbyear.Visible = False
- '
- 'Panel1
- '
- Me.Panel1.Controls.Add(Me.dgview)
- Me.Panel1.Dock = System.Windows.Forms.DockStyle.Fill
- Me.Panel1.Location = New System.Drawing.Point(0, 144)
- Me.Panel1.Name = "Panel1"
- Me.Panel1.Size = New System.Drawing.Size(608, 325)
- Me.Panel1.TabIndex = 5
- '
- '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(608, 325)
- Me.dgview.TabIndex = 4
- '
- 'frmDateTotal
- '
- Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
- Me.ClientSize = New System.Drawing.Size(704, 469)
- Me.Controls.Add(Me.Panel1)
- Me.Controls.Add(Me.GroupBox3)
- Me.Controls.Add(Me.GroupBox2)
- Me.Controls.Add(Me.GroupBox1)
- Me.Name = "frmDateTotal"
- Me.Text = "销售期间分析"
- Me.GroupBox1.ResumeLayout(False)
- Me.GroupBox2.ResumeLayout(False)
- Me.GroupBox3.ResumeLayout(False)
- Me.tabtable.ResumeLayout(False)
- Me.Panel1.ResumeLayout(False)
- CType(Me.dgview, System.ComponentModel.ISupportInitialize).EndInit()
- Me.ResumeLayout(False)
- End Sub
- #End Region
- Public lsweb As New jxcmanage
- Private Sub frmDateTotal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
- txtYear.Text = Year(Today().ToShortDateString)
- End Sub
- Private Sub txtYear_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
- 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(CASE WHEN month(日期)='01' THEN 金额 ELSE 0.00 END) AS 一月销售金额,SUM(CASE WHEN month(日期)='02' THEN 金额 ELSE 0.00 END) AS 二月销售金额,SUM(CASE WHEN month(日期)='03' THEN 金额 ELSE 0.00 END) AS 三月销售金额,SUM(CASE WHEN month(日期)='04' THEN 金额 ELSE 0.00 END) AS 四月销售金额,SUM(CASE WHEN month(日期)='05' THEN 金额 ELSE 0.00 END) AS 五月销售金额,SUM(CASE WHEN month(日期)='06' THEN 金额 ELSE 0.00 END) AS 六月销售金额,SUM(CASE WHEN month(日期)='07' THEN 金额 ELSE 0.00 END) AS 七月销售金额,SUM(CASE WHEN month(日期)='08' THEN 金额 ELSE 0.00 END) AS 八月销售金额,SUM(CASE WHEN month(日期)='09' THEN 金额 ELSE 0.00 END) AS 九月销售金额,SUM(CASE WHEN month(日期)='10' THEN 金额 ELSE 0.00 END) AS 十月销售金额,SUM(CASE WHEN month(日期)='11' THEN 金额 ELSE 0.00 END) AS 十一月销售金额,SUM(CASE WHEN month(日期)='12' THEN 金额 ELSE 0.00 END) AS 十二月销售金额 FROM wz_ck where year(日期)='" & txtYear.Text & "' group by 药品编号,药品名称,药品规格"
- Case "季比较"
- strqueryes = "select 药品编号,药品名称,药品规格,SUM(CASE WHEN DATEPART(quarter,日期)='1' THEN 金额 ELSE 0.00 END) AS 一季度销售金额,SUM(CASE WHEN DATEPART(quarter,日期)='2' THEN 金额 ELSE 0.00 END) AS 二季度销售金额,SUM(CASE WHEN DATEPART(quarter,日期)='3' THEN 金额 ELSE 0.00 END) AS 三季度销售金额,SUM(CASE WHEN DATEPART(quarter,日期)='4' THEN 金额 ELSE 0.00 END) AS 四季度销售金额 FROM wz_ck where year(日期)='" & txtYear.Text & "' group by 药品编号,药品名称,药品规格"
- Case "年比较"
- strqueryes = "select 药品编号,药品名称,药品规格,avg(单价) as 单价,sum(数量) as 数量,sum(金额) as 全年销售金额,sum(毛利) as 全年销售毛利 from wz_ck where year(日期)=year(getdate()) group by 药品编号,药品名称,药品规格"
-
- End Select
- dslocal = lsweb.runSql(strqueryes)
- dgview.DataSource = dslocal.Tables(0).DefaultView
- End Sub
- Private Sub btnQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuery.Click
- Dim strmedname, strmedtype, strkhname, stryear, strhw, strsxy, 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
-
- stryear = " year(日期)=year(getdate()) "
- If txtHw.Text = "" Then
- strhw = ""
- Else
- strhw = " and 货位='" & txtHw.Text & "'"
- End If
- If txtXsy.Text = "" Then
- strsxy = ""
- Else
- strsxy = " and 销售员='" & txtXsy.Text & "'"
- End If
- Dim strsql As String
- strsql = stryear & strmedname & strmedtype & strkhname & strsxy & strhw
- Dim dslocal As DataSet
- Select Case tabtable.SelectedTab.Text
- Case "月比较"
- strquery = "select 药品编号,药品名称,药品规格,SUM(CASE WHEN month(日期)='01' THEN 金额 ELSE 0.00 END) AS 一月销售金额,SUM(CASE WHEN month(日期)='02' THEN 金额 ELSE 0.00 END) AS 二月销售金额,SUM(CASE WHEN month(日期)='03' THEN 金额 ELSE 0.00 END) AS 三月销售金额,SUM(CASE WHEN month(日期)='04' THEN 金额 ELSE 0.00 END) AS 四月销售金额,SUM(CASE WHEN month(日期)='05' THEN 金额 ELSE 0.00 END) AS 五月销售金额,SUM(CASE WHEN month(日期)='06' THEN 金额 ELSE 0.00 END) AS 六月销售金额,SUM(CASE WHEN month(日期)='07' THEN 金额 ELSE 0.00 END) AS 七月销售金额,SUM(CASE WHEN month(日期)='08' THEN 金额 ELSE 0.00 END) AS 八月销售金额,SUM(CASE WHEN month(日期)='09' THEN 金额 ELSE 0.00 END) AS 九月销售金额,SUM(CASE WHEN month(日期)='10' THEN 金额 ELSE 0.00 END) AS 十月销售金额,SUM(CASE WHEN month(日期)='11' THEN 金额 ELSE 0.00 END) AS 十一月销售金额,SUM(CASE WHEN month(日期)='12' THEN 金额 ELSE 0.00 END) AS 十二月销售金额 FROM wz_ck where " & strsql & " group by 药品编号,药品名称,药品规格"
- Case "季比较"
- strquery = "select 药品编号,药品名称,药品规格,SUM(CASE WHEN DATEPART(quarter,日期)='1' THEN 金额 ELSE 0.00 END) AS 一季度销售金额,SUM(CASE WHEN DATEPART(quarter,日期)='2' THEN 金额 ELSE 0.00 END) AS 二季度销售金额,SUM(CASE WHEN DATEPART(quarter,日期)='3' THEN 金额 ELSE 0.00 END) AS 三季度销售金额,SUM(CASE WHEN DATEPART(quarter,日期)='4' THEN 金额 ELSE 0.00 END) AS 四季度销售金额 FROM wz_ck where " & strsql & " group by 药品编号,药品名称,药品规格"
- Case "年比较"
- strquery = "select 药品编号,药品名称,药品规格,avg(单价) as 单价,sum(数量) as 数量,sum(金额) as 全年销售金额,sum(毛利) as 全年销售毛利 from wz_ck where " & strsql & " group by 药品编号,药品名称,药品规格"
- End Select
- dslocal = lsweb.runSql(strquery)
- dgview.DataSource = dslocal.Tables(0).DefaultView
- End Sub
- End Class