2.1-3 检票查询
检票查询是针对已检票的座位信息的记录总数,可以根据多条件查询(时间段,多个文本,下拉框等)检票信息,可以清楚知道当天检票情况功能如图(图2.1-3)
从界面上可以看到我们这里用到工具箱的控件有:
控件名称 | 说明 |
容器控件(框) GroupBox | 每个控件都可以根据需要的功能更改其属性,右键查看.也可以进行编辑事件-->找到一个闪电图标点击查看需要的事件,例如:查询按钮(btnSrean)是修改过按钮文本Text属性的重命名! btnSrean_Click这是单击查询事件,还有很多的事件根据项目需求找到来编辑.控件的属性有很多,更多功能实现可以找到控件例子详细学习.
|
公共控件(显示图像) PictureBox | |
公共控件(文字描写) Label | |
公共控件(按钮)Button | |
公共控件(日期)DateTimePicker | |
公共控件(文本框)TextBox | |
所有Windows窗体(dgv)DataGridView | |
公共控件(下拉框)ComboBox | |
公共控件(单选框)CheckBox |
|
检票查询功能实现:
第一步:数据库
1、 表与表关系(图2.1-3)
表1:车辆表 (VehicleList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
VehicleID | int - Identity | 主键 | 车辆ID |
VehicleNumber | char (100) |
| 车辆编号 |
VehicleBlockNumber | char (100) |
| 车牌号 |
VehicleStateID | int | 外键 | 车辆状态ID |
MotorcadeID | int | 外键 | 车队ID |
VehicleClearID | int | 外键 | 车辆明细ID |
VehicleTypeID | int | 外键 | 车型ID |
DriverID | int | 外键 | 司机ID |
DeputyDriverOneID | int | 外键 | 副司机1ID |
DeputyDriverTwoID | int | 外键 | 副司机2ID |
StationID | int | 外键 | 站点ID |
LineID | int | 外键 | 线路ID |
OpenVehicleTime | datetime |
| 发车时间 |
ASeatNumberID | int | 外键 | 座号ID |
EntireTicketNumber | char (100) |
| 全票数 |
HalfTicketNumber | char (100) |
| 半票数 |
DiscountTicketNumber | char (100) |
| 打折票数 |
FreeTicketNumber | char (100) |
| 免票数 |
FeeRateID | int | 外键 | 费率ID |
SeatNumber | char (100) |
| 座位数 |
AddSeatNumber | char (100) |
| 加座数 |
TotalSum | decimal (18, 2) |
| 总金额 |
Remarks | char (100) |
| 备注 |
ClassesLongStopID | int | 外键 | 班次长停ID |
ClassesRepeatID | int | 外键 | 班次循环ID |
ClassesNewsID | int | 外键 | 班次信息ID |
Classes | char (100) |
| 班次 |
OpenVehicleSiteID | int | 外键 | 发车站ID |
EndVehicleSiteID | int | 外键 | 终点站ID |
DeductFeeNo | bit |
| 扣费否 |
MotorTypeNumber | char (100) |
| 发动机型号 |
MotorNumber | char (100) |
| 发动机号 |
VehicleAlterTypeCase | char (100) |
| 车辆改型情况 |
WhetherCheck | char (100) |
| 是否安检 |
SafeCheckNo | bit |
| 安检否 |
表2:站点表 (StationList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 | |
StationID | int - Identity | 主键 | 站点ID | |
StationNumber | char (100) |
| 站点编号 | |
StationName | char (100) |
| 站点名称 | |
WindowID | int | 外键 | 窗口ID | |
StopNo | bit |
| 调用否 |
表3:座号表 (ASeatNumberList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
ASeatNumberID | int - Identity | 主键 | 座号ID |
ASeatNumber | char (10) |
| 座号 |
VehicleID | int | 外键 | 车辆ID |
SeatStateID | int | 外键 | 座位状态ID |
UserNo | bit |
| 使用否 |
LinkNo | bit |
| 连接否 |
表4:票号表 (TicketNumberList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
TicketNumberID | int - Identity | 主键 | 票号ID |
TicketNumber | char (100) |
| 票号 |
ASeatNumberID | int | 外键 | 座号ID |
UserNo | bit |
| 使用否 |
表5:票据表 (BillList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
BillID | int - Identity | 主键 | 票据ID |
TicketNumber | char (100) |
| 票号 |
GetOnStation | char (100) |
| 上车站 |
GetOffStation | char (100) |
| 下车站 |
RunningWaterNo | bit |
| 流水否 |
BillTypeID | int | 外键 | 票据类型ID |
BillStatusID | int | 外键 | 票据状态ID |
SellTicKetTime | datetime |
| 售票时间 |
SellTicketSatffID | int | 外键 | 售票员ID |
ASeatNumberID | int | 外键 | 座号ID |
SellTicKetYuan | decimal (18, 3) |
| 售票价 |
CheckTicketID | int | 外键 | 检票ID |
TicketNumberID | int | 外键 | 票号ID |
表6:检票表 (CheckTicketList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
CheckTicketID | int - Identity | 主键 | 检票ID |
AlreadyCheckNo | bit |
| 已检否 |
BillID | int | 外键 | 票据ID |
Remarks | char (100) |
| 备注 |
CheckTicketTime | datetime |
| 检票时间 |
CheakPicketRoomID | int | 外键 |
|
UserID | int | 外键 | 用户ID |
表7:员工表 (StaffList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
StaffID | int - Identity | 主键 | 员工ID |
StaffNumber | char (100) |
| 员工编号 |
StaffName | char (100) |
| 员工姓名 |
Sex | char (100) |
| 性别 |
IdentityCardNumber | char (100) |
| 身份证号 |
HomeLocation | char (100) |
| 家庭地址 |
Phone | char (100) |
| 电话 |
StaffTypeID | int | 外键 | 员工类型ID |
OrganizationID | int | 外键 | 机构ID |
StationID | int | 外键 | 站点ID |
Remarks | char (100) |
| 备注 |
LeaveOfficeNo | bit |
| 离职否 |
InvokingNo | bit |
| 调用否 |
Date | datetime |
| 日期 |
Photo | nvarchar (3000) |
| 相片 |
表8:票据类型表 (BillTypeList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
BillTypeID | int - Identity | 主键 | 票据类型 |
BillTypeName | char (100) |
| 票据类型MC |
TicketPrice | decimal (18, 3) |
| 票价 |
表9:用户表(OutVehicleList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
UserID | int - Identity | 主键 | 用户ID |
UserNumber | char (100) |
| 用户编码 |
StaffID | int | 外键 | 员工ID |
WorkValue | char (100) |
| 工价 |
LimitsOfAuthorityGroupID | int | 外键 | 权限组ID |
Password | char (100) |
| 密码 |
Remarks | char (100) |
| 备注 |
StopUseNo | bit |
| 停用否 |
下面开始编写数据库储存过程:
数据库对应逻辑层(BLL)和界面层(UIL)文件夹:CheckTicketManage_frmCheckTicketDemand(检票查询窗体和对应的类的存储过程)
USE [客运综合管理系统2015]
GO
/****** Object: StoredProcedure [dbo].[CheckTicketManage_frmCheckTicketDemand] Script Date: 05/29/2015 16:07:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CheckTicketManage_frmCheckTicketDemand]--[检票管理_检票查询]
@Type CHAR(200)=''
AS
BEGIN
IF @Type='frmCheckTicketDemand_Load_dgvCheckTicke'--绑定dgvCheckTicke
BEGIN
SELECT ASeatNumberList.VehicleID, ASeatNumberList.SeatStateID, LTRIM(RTRIM(BillList.TicketNumber)) AS TicketNumber, LTRIM(RTRIM(StationList_1.StationName)) AS StationNameOne,
LTRIM(RTRIM(BillList.GetOnStation)) AS GetOnStation, LTRIM(RTRIM(BillList.GetOffStation)) AS GetOffStation, LTRIM(RTRIM(BillList.SellTicKetYuan)) AS SellTicKetYuan, BillList.SellTicKetTime,
LTRIM(RTRIM(StaffList.StaffName)) AS StaffName, LTRIM(RTRIM(VehicleList.VehicleBlockNumber)) AS VehicleBlockNumber, LTRIM(RTRIM(VehicleList.Classes)) AS Classes,
LTRIM(RTRIM(StationList.StationName)) AS StationName, LTRIM(RTRIM(ASeatNumberList.ASeatNumber)) AS ASeatNumber, LTRIM(RTRIM(BillTypeList.BillTypeName)) AS BillTypeName,
UserList.UserID, CheckTicketList.AlreadyCheckNo, BillList.BillStatusID, ASeatNumberList.ASeatNumberID, BillList.BillID
FROM BillTypeList INNER JOIN
StaffList INNER JOIN
UserList ON StaffList.StaffID = UserList.StaffID INNER JOIN
BillList ON UserList.UserID = BillList.SellTicketSatffID ON BillTypeList.BillTypeID = BillList.BillTypeID INNER JOIN
TicketNumberList ON BillList.TicketNumberID = TicketNumberList.TicketNumberID INNER JOIN
ASeatNumberList INNER JOIN
VehicleList ON ASeatNumberList.VehicleID = VehicleList.VehicleID INNER JOIN
StationList ON VehicleList.StationID = StationList.StationID ON TicketNumberList.ASeatNumberID = ASeatNumberList.ASeatNumberID INNER JOIN
CheckTicketList ON BillList.BillID = CheckTicketList.BillID INNER JOIN
StationList AS StationList_1 ON CheckTicketList.CheakPicketRoomID = StationList_1.StationID
WHERE VehicleList.DeductFeeNo=0--ASeatNumberList.SeatStateID = 1 AND BillList.BillStatusID=2
ORDER BY BillList.SellTicKetTime DESC--数据按最新日期排序
END
IF @Type='frmCheckTicketDemand_Load_cboBillType'--绑定cboBillType
BEGIN
SELECT BillTypeID,RTRIM(LTRIM(BillTypeName))AS BillTypeName
FROM BillTypeList
END
END
第二步:服务端BLL(逻辑层)写方法<---调用数据库代码:
对应文件夹:CheckTicketManage_frmCheckTicketDemand.cs(检票查询类)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;//添加命名空间
using System.Data;
using System.Data.SqlClient;
namespace BLL客运综合管理系统.CheckTicketManage
{
[ServiceContract]//服务标签
public class frmCheckTicketDemand
{
DALPublic.DALMethod myDALMethod = new DALPublic.DALMethod();//实例化调用数据库存储过程
#region 绑定dgvCheckTicke
[OperationContract]//行为标签
public DataSet frmCheckTicketDemand_Load_dgvCheckTicke()//方法名
{
SqlParameter[] mySqlParameters = {
new SqlParameter("@Type", SqlDbType.Char),
};
mySqlParameters[0].Value = "frmCheckTicketDemand_Load_dgvCheckTicke";//对应数据库方法名称
DataTable dt = myDALMethod.QueryDataTable("CheckTicketManage_frmCheckTicketDemand", mySqlParameters);//对应存储过程名字
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;
}
#endregion
#region 绑定cboBillType
[OperationContract]
public DataSet frmCheckTicketDemand_Load_cboBillType()
{
SqlParameter[] mySqlParameters = {
new SqlParameter("@Type", SqlDbType.Char),
};
mySqlParameters[0].Value = "frmCheckTicketDemand_Load_cboBillType";
DataTable dt = myDALMethod.QueryDataTable("CheckTicketManage_frmCheckTicketDemand", mySqlParameters);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;
}
#endregion
}
}
第三步:客户端UIL(界面层)写调用方法<---调用BLL(逻辑层)代码:
对应文件夹:CheckTicketManage_frmCheckTicketDemand.cs(检票查询窗体)对应各种功能详解:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;//添加命名空间
namespace XX集团客运综合管理系统client.CheckTicketManage
{
public partial class frmCheckTicketDemand : Form
{
public frmCheckTicketDemand()
{
InitializeComponent();
}
BLL客运综合管理系统.CheckTicketManage.frmCheckTicketDemand.frmCheckTicketDemandClient myfrmCheckTicketDemandClient =
new BLL客运综合管理系统.CheckTicketManage.frmCheckTicketDemand.frmCheckTicketDemandClient();//实例化调用BLL方法
public delegate void ChangeFormColor(bool topmost);
public delegate void ChangeFormColor1(bool topmost1);
public event ChangeFormColor ChangeColor;
public event ChangeFormColor1 ChangeColor1;//自定义方法使用
private void frmJianPiaoChaXun_FormClosing(object sender, FormClosingEventArgs e)
{
ChangeColor1(true);
ChangeColor(true);
}
#region frmCheckTicketDemand_Load事件
DataTable dtdgvCheckTicke;//声明一个全局变量
private void frmCheckTicketDemand_Load(object sender, EventArgs e)
{
dtdgvCheckTicke=myfrmCheckTicketDemandClient.frmCheckTicketDemand_Load_dgvCheckTicke().Tables[0];
dgvCheckTicke.DataSource = dtdgvCheckTicke;//绑定dgv信息
this.dgvCheckTicke = SYS_PublicStaticClass.SetDgv(dgvCheckTicke);//显示序号
this.dgvCheckTicke.CellBorderStyle = DataGridViewCellBorderStyle.Sunken;//设置dgv三维凹陷边框
this.dgvCheckTicke.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;//设置dgv列标题居中
this.dgvCheckTicke.RowsDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;//设置dgv单元格内容居中
DataTable dtcboBillType=myfrmCheckTicketDemandClient.frmCheckTicketDemand_Load_cboBillType().Tables[0];
cboBillType.DataSource = dtcboBillType;//绑定下拉框票型
cboBillType.DisplayMember = "BillTypeName";
cboBillType.ValueMember = "BillTypeID";
cboBillType.SelectedValue = -1;//属性指定成员属性的值隐藏
string[] strTicketNumber = new string[dtdgvCheckTicke.Rows.Count];
for (int i = 0; i < dtdgvCheckTicke.Rows.Count; i++)
{
strTicketNumber[i] = dtdgvCheckTicke.Rows[i]["TicketNumber"].ToString().Trim();
}
txtTicketNumber.AutoCompleteSource = AutoCompleteSource.CustomSource;//自动完成源票号
txtTicketNumber.AutoCompleteCustomSource.AddRange(strTicketNumber);
txtTicketNumber.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
string[] strClasses = new string[dtdgvCheckTicke.Rows.Count];
for (int j = 0; j < dtdgvCheckTicke.Rows.Count; j++)
{
strClasses[j] = dtdgvCheckTicke.Rows[j]["Classes"].ToString().Trim();
}
txtClasses.AutoCompleteSource = AutoCompleteSource.CustomSource;//自动完成源班次
txtClasses.AutoCompleteCustomSource.AddRange(strClasses);
txtClasses.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
string[] strStation = new string[dtdgvCheckTicke.Rows.Count];
for (int k = 0; k < dtdgvCheckTicke.Rows.Count; k++)
{
strStation[k] = dtdgvCheckTicke.Rows[k]["StationName"].ToString().Trim();
}
txtStation.AutoCompleteSource = AutoCompleteSource.CustomSource;//自动完成源站点
txtStation.AutoCompleteCustomSource.AddRange(strStation);
txtStation.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
string[] strCheckTickeStaff = new string[dtdgvCheckTicke.Rows.Count];
for (int l = 0; l < dtdgvCheckTicke.Rows.Count; l++)
{
strCheckTickeStaff[l] = dtdgvCheckTicke.Rows[l]["StaffName"].ToString().Trim();
}
txtCheckTickeStaff.AutoCompleteSource = AutoCompleteSource.CustomSource;//自动完成源检票员
txtCheckTickeStaff.AutoCompleteCustomSource.AddRange(strCheckTickeStaff);
txtCheckTickeStaff.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
dtpSellTicketDate.Format = DateTimePickerFormat.Custom;//设置时间格式2015-04-21 10:01:00
dtpSellTicketDate.CustomFormat = "yyyy-MM-dd HH:mm:ss";
dtpSellTicketDateTwo.Format = DateTimePickerFormat.Custom;
dtpSellTicketDateTwo.CustomFormat = "yyyy-MM-dd HH:mm:ss";
int intCount = 0;
for (int i = 0; i < dgvCheckTicke.Rows.Count; i++)
{
intCount++;
}
lblJiLuZongShu.Text = intCount.ToString().Trim();//记录总数
}
#endregion
#region 多条件查询
private void btnSearch_Click(object sender, EventArgs e)
{
DataView dvCheckTicke = new DataView(dtdgvCheckTicke);//实例化
DateTime dtSellTicketDate = dtpSellTicketDate.MinDate;//获取日期最小值
DateTime dtSellTicketDateTwo = dtpSellTicketDateTwo.MaxDate;//获取日期最大值
string strtxtTicketNumber = "";
string strtxtClasses = "";
string strtxtStation = "";
string strcboBillType = "";
string strtxtCheckTickeStaff = "";
if (chKSellTicketDate.Checked==true)//判断
{
dtSellTicketDate = dtpSellTicketDate.Value;
dtSellTicketDateTwo = dtpSellTicketDateTwo.Value;
}
if (txtTicketNumber.Text != "")
{
strtxtTicketNumber = txtTicketNumber.Text.Trim();
}
if (txtClasses.Text != "")
{
strtxtClasses = txtClasses.Text.Trim();
}
if (txtStation.Text != "")
{
strtxtStation = txtStation.Text.Trim();
}
if (cboBillType.Text != "")
{
strcboBillType = cboBillType.Text.Trim();
}
if (txtCheckTickeStaff.Text != "")
{
strtxtCheckTickeStaff = txtCheckTickeStaff.Text.Trim();
}
dvCheckTicke.RowFilter = "SellTicKetTime >='" + dtSellTicketDate + "'AND SellTicKetTime <='" + dtSellTicketDateTwo +
"' AND TicketNumber LIKE '%" + strtxtTicketNumber + "%' AND Classes LIKE '%" + strtxtClasses +
"%' AND StationName LIKE '%" + strtxtStation + "%' AND BillTypeName LIKE '%" + strcboBillType +
"%' AND StaffName LIKE '%" + strtxtCheckTickeStaff + "%'";
dgvCheckTicke.DataSource = dvCheckTicke.ToTable();//绑定查询数据
int intCount = 0;
for (int i = 0; i < dgvCheckTicke.Rows.Count; i++)
{
intCount++;
}
lblJiLuZongShu.Text = intCount.ToString().Trim();//记录总数
}
#endregion
#region 清空/重置
private void btnEliminate_Click(object sender, EventArgs e)
{
txtTicketNumber.Text = "";
txtClasses.Text = "";
txtStation.Text = "";
cboBillType.Text = "";
txtCheckTickeStaff.Text = "";
chKSellTicketDate.Checked = false;
}
#endregion
}
}
以上是检票管理-检票查询功能实现步骤:数据库-->BLL-->UIL
-------实现功能待续!!!
以上仅供学习参考,禁止商业用途!!!