完整的一张成绩一览帐票
环境：
vs2005+ActiveReportsNet2
本节代码下载：https://files.cnblogs.com/batoosai/SimpleExample10.rar
学习了前面的一些基础，我们来实际开发一张帐票吧。
1，帐票式样
2，帐票说明：
1,抽出条件：
1-1班级：必選,多選
1-2科目：必選, 多選
2,用紙サイズ： A4 横
3,改頁条件：班级
4,印刷順序：行：生徒出席番号升排序
列：科目順位
4,如果某学生某subject没有成绩的话，也要显示该数据（只是把格子留空）
分析下帐票，我们应该用3个子模版（如下图，其中Sub3不需要取数，老师手动填）
3,好，我们项目一般的架构如下：
在我们的示例中，方便起见，就用简单的3层架构吧，当中的web service去掉。创建Project如图：
1）表示层：Form,ARTemplate
2）业务逻辑层:BR
3）数据层：DA
4，数据库如下：
具体数据表的数据：
1）Class
|
Class |
|||
|
ClassID |
ClassName |
TeacherName |
DisplayOrder |
|
1 |
A班 |
Tony Gong |
1 |
|
2 |
B班 |
Tony Wang |
2 |
|
3 |
C班 |
Tony Li |
3 |
2)Student
|
Student |
|||
|
StudentID |
ClassID |
No |
Name |
|
11 |
1 |
1 |
Tony |
|
12 |
1 |
2 |
Zhu |
|
13 |
1 |
3 |
Li |
|
14 |
1 |
4 |
Zhang |
|
15 |
1 |
5 |
Zha |
|
16 |
1 |
6 |
Sun |
|
21 |
2 |
1 |
Wang |
|
22 |
2 |
2 |
Gong |
|
23 |
2 |
3 |
Tian |
|
24 |
2 |
4 |
Su |
|
25 |
2 |
5 |
Xiao |
|
31 |
3 |
1 |
Xu |
|
32 |
3 |
2 |
Liu |
|
33 |
3 |
3 |
Tom |
|
34 |
3 |
4 |
Mary |
3)Subject
|
Subject |
||
|
SubjectID |
SubjectName |
DisplayOrder |
|
1 |
古典 |
1 |
|
2 |
現代文 |
2 |
|
3 |
語文 |
3 |
4)Score
|
Score |
||||
|
ScoreID |
StudentID |
SubjectID |
Score |
GradeOrder |
|
1 |
11 |
1 |
70 |
11 |
|
2 |
11 |
2 |
75 |
23 |
|
3 |
11 |
3 |
55 |
1 |
|
4 |
12 |
1 |
6 |
15 |
|
5 |
12 |
2 |
77 |
6 |
|
6 |
13 |
1 |
8 |
6 |
|
7 |
13 |
2 |
77 |
7 |
|
8 |
13 |
3 |
66 |
8 |
|
9 |
14 |
1 |
55 |
9 |
|
10 |
14 |
3 |
44 |
10 |
|
11 |
15 |
2 |
32 |
11 |
|
12 |
16 |
3 |
100 |
12 |
|
13 |
21 |
1 |
11 |
13 |
|
14 |
21 |
2 |
14 |
14 |
|
15 |
21 |
3 |
15 |
1 |
|
16 |
22 |
1 |
16 |
2 |
|
17 |
22 |
2 |
76 |
3 |
|
18 |
22 |
3 |
18 |
4 |
|
19 |
23 |
1 |
99 |
5 |
|
20 |
23 |
3 |
89 |
6 |
|
21 |
24 |
1 |
21 |
7 |
|
22 |
25 |
1 |
44 |
8 |
|
23 |
31 |
1 |
23 |
9 |
|
24 |
31 |
2 |
24 |
10 |
|
25 |
32 |
1 |
25 |
1 |
|
26 |
32 |
3 |
26 |
2 |
|
27 |
33 |
1 |
27 |
3 |
|
28 |
34 |
1 |
28 |
4 |
|
29 |
34 |
2 |
29 |
5 |
|
30 |
34 |
3 |
30 |
6 |
5,ok,开工，先add 如下文件
其中Form中
frmScore让用户选择抽出条件
frmShowAR用来显示report
6，在form上放2个多选的条件控件，Class和Subject。
实际项目中，这2个控件的内容应该取数据表里的值。我这里方便起见，直接把值写死在控件上。
btnPreview的click事件如下：
If Me.listClass.SelectedIndex = -1 OrElse Me.listSubject.SelectedIndex = -1 Then
Return
End If
'Get the Parameter
Dim classID As String = ""
Dim subjectID As String = ""
For index As Int32 = 0 To Me.listClass.SelectedIndices.Count - 1
classID &= Me.listClass.SelectedIndices(index) + 1 & ","
Next
For index As Int32 = 0 To Me.listSubject.SelectedIndices.Count - 1
subjectID &= Me.listSubject.SelectedIndices(index) + 1 & ","
Next
classID = classID.TrimEnd(Convert.ToChar(","))
subjectID = subjectID.TrimEnd(Convert.ToChar(","))
'Send parameters to the BR and get the dataset.
Dim br As New BR.brScore()
Dim finalDS As DataSet = br.GetData(classID, subjectID)
'Send dataset to the form
Dim frm As New frmShowAR(finalDS)
frm.Show()
End Sub
7，br层，由于业务太简单了，代码很简单。
Try
' 創建DA
Dim objDA As New DA.daScore()
Dim ds As DataSet = objDA.GetData(classID, subjectID)
' 返回値
Return ds
Catch ex As Exception
' 抛出異常
Throw ex
End Try
End Function
8， DA层，根据参数执行sql语句，返回结果Dataset
DA的任务，是根据参数，作select语句，得出2张table，放到ds中return。
假设Class选了”B,C”，Subject选了”古典，现代文”
其中table1（用于Sub1和主模版）数据应该如下：(9条记录)
|
Query1 |
|||||||
|
Class.ClassID |
ClassName |
TeacherName |
DisplayOrder |
StudentID |
Student.ClassID |
No |
Name |
|
2 |
B班 |
Tony Wang |
2 |
21 |
2 |
1 |
Wang |
|
2 |
B班 |
Tony Wang |
2 |
22 |
2 |
2 |
Gong |
|
2 |
B班 |
Tony Wang |
2 |
23 |
2 |
3 |
Tian |
|
2 |
B班 |
Tony Wang |
2 |
24 |
2 |
4 |
Su |
|
2 |
B班 |
Tony Wang |
2 |
25 |
2 |
5 |
Xiao |
|
3 |
C班 |
Tony Li |
3 |
31 |
3 |
1 |
Xu |
|
3 |
C班 |
Tony Li |
3 |
32 |
3 |
2 |
Liu |
|
3 |
C班 |
Tony Li |
3 |
33 |
3 |
3 |
Tom |
|
3 |
C班 |
Tony Li |
3 |
34 |
3 |
4 |
Mary |
table2（用于Sub2）数据应该这样做：
9条生徒 * 2门科目,然后left join Score的内容，最终得到18条记录（假如某人某科没成绩的话，该字段也会显示出来，只是留空而已）。
PS：由于Access的Left join 俺实在是搞不懂，没法做left join，所以只能把Score的纪录填满，然后用普通查询。
再PS：Access的参数化查询我也搞不太清楚，所以代码中直接把”@classid”等替换成字符串了。
最后的PS：假如是mssql的话，sub2的查询语句大概如下：
Select * from student,class
cross join Subject
left join score
on score.subjectID=Subject.SubjectID
and score.StudentID=student.StudentID
Where s.ClassID=c.ClassID
And c.ClassID in (@ClassID)
Sub3就不需要查寻了。
9，frmShowAR接收到数据源，并显示帐票
Sub New(ByVal ds As DataSet)
Me.finalDS = ds
' 此调用是 Windows 窗体设计器所必需的。
InitializeComponent()
' 在 InitializeComponent() 调用之后添加任何初始化。
End Sub
Private finalDS As DataSet
Private Sub frmShowAR_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim rpt As New ARTemplate.rptScore
rpt.DataSource = finalDS
rpt.DataMember = finalDS.Tables(0).TableName
'A4 Landscape
rpt.PageSettings.PaperKind = Printing.PaperKind.A4
rpt.PageSettings.Orientation = DataDynamics.ActiveReports.Document.PageOrientation.Landscape
rpt.Run()
Me.Viewer1.Document = rpt.Document
End Sub
End Class
10，主模版和子模版主要属性及代码（完整的请看下载的代码）
1），主模版
GroupHeader1：DataFied=”ClassID”
NewPage=”Before”
txtClassNo： DataField=”ClassName”
txtTeacher： DataField=”TeacherName”
代码：
Report_start中布局代码
'<--------Step (1)---------->
'Set contols' width
'me.lblClass.Width=xxx
'
'
'<--------Step (1)---------->
'<--------Step (2)---------->
'Arrange Controls.
Me.lblYear.Left = 0.5
Me.lblYear.Top = 0.5
Me.txtClassNo.Left = Me.lblYear.Left
Me.txtClassNo.Top = Me.lblYear.Top + Me.lblYear.Height
Me.Label3.Left = Me.txtClassNo.Left + Me.txtClassNo.Width * 2
Me.Label3.Top = Me.txtClassNo.Top
Me.txtTeacher.Left = Me.Label3.Left + Me.Label3.Width
Me.txtTeacher.Top = Me.Label3.Top
'<--------Step (2)---------->
'<--------Step (3)---------->
Dim sub1 As New sub1
Me.subReport1.Width = sub1.ReportWidth
Dim sub2 As New sub2
With CType(Me.DataSource, DataSet)
Me.subReport2.Width = sub2.ReportWidth * CInt(.Tables(1).Rows.Count / .Tables(0).Rows.Count)
End With
Dim sub3 As New sub3
Me.subReport3.Width = sub3.reportWidth
'<--------Step (3)---------->
'<--------Step (4)---------->
Me.subReport1.Left = Me.txtClassNo.Left
Me.subReport1.Top = Me.txtClassNo.Top + Me.txtClassNo.Height
Me.subReport2.Left = Me.subReport1.Left + Me.subReport1.Width
Me.subReport2.Top = Me.subReport1.Top
Me.subReport3.Left = Me.subReport2.Left + Me.subReport2.Width
Me.subReport3.Top = Me.subReport1.Top
'<--------Step (4)---------->
'<--------Step (5)---------->
'Set all sections & PrintWidth
Me.PageHeader.Height = Me.TextBox2.Height
Me.Detail.Height = 0
'<--------Step (5)---------->
'<--------Step (6)---------->
'Set PrintWidth
Dim realWidth As Single = Me.subReport3.Left + Me.subReport3.Width
Dim defaultWidth As Single
If PageSettings.Orientation = PageOrientation.Portrait Then
defaultWidth = PageSettings.PaperWidth - PageSettings.Margins.Left - PageSettings.Margins.Right
Else
defaultWidth = PageSettings.PaperHeight - PageSettings.Margins.Left - PageSettings.Margins.Right
End If
If realWidth > defaultWidth Then
Me.PrintWidth = realWidth
Else
Me.PrintWidth = defaultWidth
End If
'<--------Step (6)---------->
End Sub
Detail_Format里的生成子模版的代码：
'subReport1
Dim sub1 As New sub1
Me.subReport1.Report = sub1
'為子報表設置数据源
Dim dvStudent As DataView = New DataView(CType(Me.DataSource, DataSet).Tables(0), "ClassID='" & Me.Fields("ClassID").Value.ToString & "'", "", DataViewRowState.CurrentRows)
Me.subReport1.Report.DataSource = dvStudent
'subReport2
Dim sub2 As New sub2
Me.subReport2.Report = sub2
'為子報表設置数据源
Dim dvScore As DataView = New DataView(CType(Me.DataSource, DataSet).Tables(1), "ClassID='" & Me.Fields("ClassID").Value.ToString & "'", "", DataViewRowState.CurrentRows)
With CType(Me.subReport2.Report, sub2)
.DataSource = dvScore
'Set subReport's ColumnCount
.RealColumnCount = CInt(dvScore.Count / dvStudent.Count)
End With
'subReport3
Dim sub3 As New sub3
Me.subReport3.Report = sub3
Me.subReport3.Report.DataSource = dvStudent
End Sub
2），Sub1
txtID：DataField=”No”
txtName：DataField=”Name”
3），Sub2
GroupHeader1：DataField=” SubjectID”
NewPage=”Before”
lblSubject：DataField=”SubjectName”
txtScore：Datafield=”Score”
txtOrder：Datafield=”gradeOrder”
4），Sub3
11，最终效果，比较粗糙，大家自己改进吧。
