公司里用的统计程序及简单报表的实现
这是一个用于公司内部打印短信发送每日报表和每月报表的程序，只是用于记录一下
里面有些如sqlclient类的用法，打印的实现，月历控件的使用都是我在公司写程序时常用的，放到这上面以供以后查阅
当然在这里是不会写数据库结构和账号什么的，写出这个来，就玩大了，老板非得叫我赔到死才算完。
.net是个好东东，很好用
最后提一下，对于打印文档的设置使用了drawing
这个东东极好用，银行的单据打印用这个都能轻松实现，drawstring(text,x,y)想打在什么地方都行，当然再加上drawline等适当的应用，做出漂亮的报表就好用多了，那个什么水晶报表的东东，可以直接不用了，这个可能麻烦点，可做出的东东是全听自己的，这也是大多数程序员最想做的事情吧。嘿嘿。不多说了，下面是代码，自己看吧
——————————————————————————————————————————————
' 命名空间用了sqlclient类
Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
' 用于定义端口数组和选择的日期变量
Dim ports(8) As String
Dim firstdate, enddate As String
#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 TextBox1 As System.Windows.Forms.TextBox
Friend WithEvents MonthCalendar1 As System.Windows.Forms.MonthCalendar
Friend WithEvents PrintDocument1 As System.Drawing.Printing.PrintDocument
Friend WithEvents Button1 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.TextBox1 = New System.Windows.Forms.TextBox
Me.MonthCalendar1 = New System.Windows.Forms.MonthCalendar
Me.PrintDocument1 = New System.Drawing.Printing.PrintDocument
Me.Button1 = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'TextBox1
'
Me.TextBox1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
Me.TextBox1.Location = New System.Drawing.Point(272, 0)
Me.TextBox1.Multiline = True
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.ReadOnly = True
Me.TextBox1.ScrollBars = System.Windows.Forms.ScrollBars.Both
Me.TextBox1.Size = New System.Drawing.Size(232, 392)
Me.TextBox1.TabIndex = 0
Me.TextBox1.Text = ""
'
'MonthCalendar1
'
Me.MonthCalendar1.Location = New System.Drawing.Point(0, 0)
Me.MonthCalendar1.MaxSelectionCount = 31
Me.MonthCalendar1.Name = "MonthCalendar1"
Me.MonthCalendar1.TabIndex = 1
'
'PrintDocument1
'
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(72, 200)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(96, 16)
Me.Button1.TabIndex = 2
Me.Button1.Text = "Button1"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
Me.ClientSize = New System.Drawing.Size(600, 397)
Me.Controls.Add(Me.Button1)
Me.Controls.Add(Me.MonthCalendar1)
Me.Controls.Add(Me.TextBox1)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)
End Sub
#End Region
'用于得到1690的从端口string数组
Private Sub getport()
Dim LTsmsconstr As String
Dim LTcon As SqlConnection
Dim comstr As String
Dim ltcom As SqlCommand
Dim i As Integer
LTsmsconstr = "uid=sa;pwd=xxxxx;database=sc;server=xxx.xxx.xxx.xxx"
LTcon = New SqlConnection(LTsmsconstr)
LTcon.Open()
comstr = "select FAC_ID from CO_FAC_INFO"
ltcom = New SqlCommand(comstr, LTcon)
Dim LTreader1 As SqlDataReader
LTreader1 = ltcom.ExecuteReader
While LTreader1.Read
i = i + 1
ports(i) = LTreader1(0)
'TextBox1.Text = TextBox1.Text + ports(i)
End While
LTcon.Close()
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
getport()
End Sub
'调用统计函数得到统计结果，并在textbox1中显出来
Private Sub showend()
Dim LT, YD, TOL, i As Integer
For i = 0 To 8
LT = LTS(firstdate, enddate, ports(i))
YD = YDS(firstdate, enddate, ports(i))
TOL = LT + YD
TextBox1.Text = TextBox1.Text & "---------------------------------------------" & vbCrLf & " 1690" & ports(i) & "端口统计：" & vbCrLf & "---------------------------------------------" & vbCrLf & "联通发送量：" & LT & vbCrLf & "移动发送量：" & YD & vbCrLf & "端口发送总量：" & TOL & vbCrLf & vbCrLf
Next
End Sub
' 用于统计联通发送数据
Private Function LTS(ByVal firstdate As String, ByVal enddate As String, ByVal port As String) As Integer
Dim LTsmsconstr As String
LTsmsconstr = "uid=sa;pwd=xxxx;database=sc;server=xxx.xxx.xxx.xxx"
Dim LTcon As SqlConnection
LTcon = New SqlConnection(LTsmsconstr)
LTcon.Open()
Dim LTtol As SqlCommand
Dim tol As String
LTcon = New SqlConnection(LTsmsconstr)
LTcon.Open()
tol = "select count(*) from MESSENG_SEND_LT_BACK where attime>='" & firstdate & "'"
'If firstdate <> enddate Then
tol = tol & " and attime<='" & enddate & "'"
'End If
If port <> "" Then
tol = tol & " and srcid=1690" & port
End If
'MsgBox(tol)
Try
LTtol = New SqlCommand(tol, LTcon)
Return CType(LTtol.ExecuteScalar, Integer)
LTcon.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Function
' 用于统计移动发送数据
Private Function YDS(ByVal firstdate As String, ByVal enddate As String, ByVal port As String) As Integer
Dim LTsmsconstr As String
LTsmsconstr = "uid=sa;pwd=xxxxx;database=sc;server=xxx.xxx.xxx.xxx"
Dim LTcon As SqlConnection
LTcon = New SqlConnection(LTsmsconstr)
LTcon.Open()
Dim LTtol As SqlCommand
Dim tol As String
LTcon = New SqlConnection(LTsmsconstr)
LTcon.Open()
tol = "select count(*) from MESSENG_SEND_YD_BACK where attime>='" & firstdate & "'"
'If firstdate <> enddate Then
tol = tol & " and attime<='" & enddate & "'"
'End If
If port <> "" Then
tol = tol & " and srcid=01690" & port
End If
Try
LTtol = New SqlCommand(tol, LTcon)
Return CType(LTtol.ExecuteScalar, Integer)
LTcon.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Function
' 由于时间字段是采用的字符型，所以这里定义了一个将时间取得后进行格式化的函数，主要功能是给日期里小于10的数字前面加0，并只取日期部份，去掉时间部份。
Function formatdate(ByVal date1 As String) As String
Dim year1, month1, day1 As String
If Not IsDate(date1) Then
Return "2001-10-22"
Exit Function
End If
year1 = DatePart("yyyy", date1)
month1 = DatePart("m", date1)
If month1 < 10 Then
month1 = "0" + month1
End If
day1 = DatePart("d", date1)
If day1 < 10 Then
day1 = "0" + day1
End If
Return year1 + "-" + month1 + "-" + day1
End Function
' 月历控件的时间改变事件
Private Sub MonthCalendar1_DateChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar1.DateChanged
firstdate = formatdate(CStr(MonthCalendar1.SelectionStart))
enddate = formatdate(CStr(MonthCalendar1.SelectionEnd))
TextBox1.Text = "从" & firstdate & " 00点" & vbCrLf & "到" & enddate & " 00点" & vbCrLf & vbCrLf
showend()
End Sub
' 设置要打印的文档内容
Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
e.Graphics.DrawString(TextBox1.Text, New Font("system", 8), Brushes.Black, 20, 10)
End Sub
' 点击打印
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
PrintDocument1.Print()
End Sub
End Class
浙公网安备 33010602011771号