2.4 领导查询(模块)
针对老的售票系统查询条件单一,结果体现不完整,不能真实反应日趋复杂的站务工作等现象,本系统提供了综合查询功能,可按多种条件对售票、退票、废票等数据进行组合查询,以此方便各级人员及时掌握站场的经营情况。
2.4-1 售票查询
售票查询是基于售票管理-售票功能的一个查询信息,根据售票情况会记录售票的详细信息,同时记录当天售票情况方便查找当天销售情况.功能如下图2.4-1
从界面上可以看到我们这里用到工具箱的控件有:
控件名称 | 说明 |
容器控件(框)GroupBox | 每个控件都可以根据需要的功能更改其属性,右键查看.也可以进行编辑事件-->找到一个闪电图标点击查看需要的事件,例如:查询按钮(btnSrean)是修改过按钮文本Text属性的重命名! btnSrean_Click这是单击查询事件,还有很多的事件根据项目需求找到来编辑.
|
公共控件(单选框)CheckBox | |
公共控件(文字描写)Label | |
公共控件(按钮)Button | |
公共控件(日期)DateTimePicker | |
公共控件(文本框)TextBox | |
所有Windows窗体(dgv)DataGridView | |
公共控件(下拉框)ComboBox | |
公共控件(显示图像)PictureBox |
|
售票查询功能实现:
第一步:数据库
1、 表与表关系(图2.3-4)
从上面可以知道用到的表有:
表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:车型表 (VehicleTypeList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
VehicleTypeID | int - Identity | 主键 | 车型ID |
VehicleTypeNumber | char (100) |
| 车型编号 |
VehicleTypeName | char (100) |
| 车型MC |
Load | char (100) |
| 载重 |
表4:座号表 (ASeatNumberList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
ASeatNumberID | int - Identity | 主键 | 座号ID |
ASeatNumber | char (10) |
| 座号 |
VehicleID | int | 外键 | 车辆ID |
SeatStateID | int | 外键 | 座位状态ID |
UserNo | bit |
| 使用否 |
LinkNo | bit |
| 连接否 |
表5:票号表 (TicketNumberList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
TicketNumberID | int - Identity | 主键 | 票号ID |
TicketNumber | char (100) |
| 票号 |
ASeatNumberID | int | 外键 | 座号ID |
UserNo | bit |
| 使用否 |
表6:票据表 (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 |
表7:票据类型表 (BillTypeList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
BillTypeID | int - Identity | 主键 | 票据类型 |
BillTypeName | char (100) |
| 票据类型MC |
TicketPrice | decimal (18, 3) |
| 票价 |
下面开始编写数据库储存过程:
数据库对应逻辑层(BLL)和界面层(UIL)文件夹:LeadDemand_frmSellTicketSearch(售票查询窗体和对应的类的存储过程)
USE [客运综合管理系统2015]
GO
/****** Object: StoredProcedure [dbo].[LeadDemand_frmSellTicketSearch] Script Date: 06/01/2015 11:08:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[LeadDemand_frmSellTicketSearch]--[领导查询-售票查询]
@Type CHAR(200)=''
AS
BEGIN
IF @Type='frmSellTicketSearch_Load_cboTicketType'--绑定cboTicketType
BEGIN
SELECT BillTypeID,LTRIM(RTRIM(BillTypeName))AS BillTypeName
FROM BillTypeList
END
IF @Type='frmSellTicketSearch_Load_cboTicketStatus'--绑定cboTicketStatus
BEGIN
SELECT BillStatusID,LTRIM(RTRIM(BillStatus))AS BillStatus
FROM BillStatusList
END
IF @Type='frmSellTicketSearch_Load_cboVehicleType'--绑定cboVehicleType
BEGIN
SELECT VehicleTypeID,LTRIM(RTRIM(VehicleTypeName))AS VehicleTypeName
FROM VehicleTypeList
END
IF @Type='frmSellTicketSearch_Load_dgvSellTicketSearch'--绑定dgvSellTicketSearch
BEGIN
SELECT VehicleList.VehicleID, LTRIM(RTRIM(VehicleList.Classes)) AS Classes, LTRIM(RTRIM(VehicleTypeList.VehicleTypeName)) AS VehicleTypeName,
LTRIM(RTRIM(StationList.StationName))AS StationName, LTRIM(RTRIM(StationList_1.StationName)) AS StationNameOne,
LTRIM(RTRIM(ASeatNumberList.ASeatNumber)) AS ASeatNumber, LTRIM(RTRIM(BillTypeList.BillTypeName))AS BillTypeName,
LTRIM(RTRIM(BillList.TicketNumber)) AS TicketNumber, LTRIM(RTRIM(BillList.GetOnStation)) AS GetOnStation, LTRIM(RTRIM(BillList.GetOffStation))AS GetOffStation, BillList.BillID,
ASeatNumberList.ASeatNumberID, LTRIM(RTRIM(StaffList.StaffName)) AS StaffName, BillTypeList.BillTypeID, ASeatNumberList.SeatStateID, BillList.SellTicKetTime
FROM BillList INNER JOIN
TicketNumberList ON BillList.TicketNumberID = TicketNumberList.TicketNumberID INNER JOIN
ASeatNumberList INNER JOIN
VehicleList INNER JOIN
VehicleTypeList ON VehicleList.VehicleTypeID = VehicleTypeList.VehicleTypeID INNER JOIN
StationList ON VehicleList.StationID = StationList.StationID INNER JOIN
StationList AS StationList_1 ON VehicleList.EndVehicleSiteID = StationList_1.StationID ON ASeatNumberList.VehicleID = VehicleList.VehicleID ON
TicketNumberList.ASeatNumberID = ASeatNumberList.ASeatNumberID INNER JOIN
StaffList ON BillList.SellTicketSatffID = StaffList.StaffID INNER JOIN
BillTypeList ON BillList.BillTypeID = BillTypeList.BillTypeID
WHERE BillList.BillStatusID=3
ORDER BY BillList.SellTicKetTime DESC--数据按日期最新排序
END
END
第二步:服务端BLL(逻辑层)写方法<---调用数据库代码:
对应文件夹: LeadDemand_frmSellTicketSearch.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客运综合管理系统.LeadDemand
{
[ServiceContract] //添加服务标签
public class frmSellTicketSearch
{
DALPublic.DALMethod myDALMethod = new DALPublic.DALMethod();//实例化调用数据库方法
#region 绑定票型cboTicketType方法
[OperationContract] //添加行为标签
public DataSet frmSellTicketSearch_Load_cboTicketType()//添加自定义方法名称
{
SqlParameter[] mySqlParameters = {
new SqlParameter("@Type", SqlDbType.Char),
};
mySqlParameters[0].Value = "frmSellTicketSearch_Load_cboTicketType";//方法名称对应数据库方法名称
DataTable dt = myDALMethod.QueryDataTable("LeadDemand_frmSellTicketSearch", mySqlParameters);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds; //返回值
}
#endregion
#region 绑定票状态cboTicketStatus方法
[OperationContract]
public DataSet frmSellTicketSearch_Load_cboTicketStatus()
{
SqlParameter[] mySqlParameters = {
new SqlParameter("@Type", SqlDbType.Char),
};
mySqlParameters[0].Value = "frmSellTicketSearch_Load_cboTicketStatus";
DataTable dt = myDALMethod.QueryDataTable("LeadDemand_frmSellTicketSearch", mySqlParameters);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;
}
#endregion
#region 绑定车型cboVehicleType方法
[OperationContract]
public DataSet frmSellTicketSearch_Load_cboVehicleType()
{
SqlParameter[] mySqlParameters = {
new SqlParameter("@Type", SqlDbType.Char),
};
mySqlParameters[0].Value = "frmSellTicketSearch_Load_cboVehicleType";
DataTable dt = myDALMethod.QueryDataTable("LeadDemand_frmSellTicketSearch", mySqlParameters);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;
}
#endregion
#region 绑定dgvSellTicketSearch方法
[OperationContract]
public DataSet frmSellTicketSearch_Load_dgvSellTicketSearch()
{
SqlParameter[] mySqlParameters = {
new SqlParameter("@Type", SqlDbType.Char),
};
mySqlParameters[0].Value = "frmSellTicketSearch_Load_dgvSellTicketSearch";
DataTable dt = myDALMethod.QueryDataTable("LeadDemand_frmSellTicketSearch", mySqlParameters);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;
}
#endregion
}
}
第三步:客户端UIL(界面层)写调用方法<---调用BLL(逻辑层)代码:
对应文件夹:LeadDemand_frmSellTicketSearch.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.LeadDemand
{
public partial class frmSellTicketSearch : Form
{
public frmSellTicketSearch()
{
InitializeComponent();
}
BLL客运综合管理系统.LeadDemand.frmSellTicketSearch.frmSellTicketSearchClient myfrmSellTicketSearchClient =
new BLL客运综合管理系统.LeadDemand.frmSellTicketSearch.frmSellTicketSearchClient();//实例化调用BLL方法
public delegate void ChangeFormColor(bool topmost); //自定义方法使用
public delegate void ChangeFormColor1(bool topmost1); //自定义方法使用
public event ChangeFormColor ChangeColor; //自定义方法使用
public event ChangeFormColor1 ChangeColor1;//自定义方法使用
private void frmShouPiaoChaXun_FormClosing(object sender, FormClosingEventArgs e)
{
ChangeColor1(true);
ChangeColor(true);
}
#region frmSellTicketSearch_Load事件功能
DataTable dtdgvSellTicketSearch;//声明一个全局变量
private void frmSellTicketSearch_Load(object sender, EventArgs e)
{
DataTable dtcboVehicleType=myfrmSellTicketSearchClient.frmSellTicketSearch_Load_cboVehicleType().Tables[0]; //绑定数据下拉框调用
cboVehicleType.DataSource = dtcboVehicleType;//绑定下拉框车型
cboVehicleType.DisplayMember = "VehicleTypeName";
cboVehicleType.ValueMember = "VehicleTypeID";
cboVehicleType.SelectedValue = -1;
DataTable dtcboTicketType=myfrmSellTicketSearchClient.frmSellTicketSearch_Load_cboTicketType().Tables[0];
cboTicketType.DataSource = dtcboTicketType;//绑定下拉框票型
cboTicketType.DisplayMember = "BillTypeName";
cboTicketType.ValueMember = "BillTypeID";
cboTicketType.SelectedValue = -1;
dtdgvSellTicketSearch=myfrmSellTicketSearchClient.frmSellTicketSearch_Load_dgvSellTicketSearch().Tables[0];
dgvSellTicketSearch.DataSource = dtdgvSellTicketSearch;//绑定dgv信息
this.dgvSellTicketSearch = SYS_PublicStaticClass.SetDgv(dgvSellTicketSearch);//dgv序号
this.dgvSellTicketSearch.CellBorderStyle = DataGridViewCellBorderStyle.Sunken;//设置dgv三维凹陷边框
this.dgvSellTicketSearch.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;//设置dgv列标题居中
this.dgvSellTicketSearch.RowsDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;//设置dgv单元格内容居中
dtpSellTicketDate.Format = DateTimePickerFormat.Custom;//设置时间格式2015-04-19 21:46:00
dtpSellTicketDate.CustomFormat = "yyyy-MM-dd HH:mm:ss";
dtpSellTicketDateOne.Format = DateTimePickerFormat.Custom;
dtpSellTicketDateOne.CustomFormat = "yyyy-MM-dd HH:mm:ss";
string[] strtxtTicketNumber=new string[dtdgvSellTicketSearch.Rows.Count];
for (int i = 0; i < dtdgvSellTicketSearch.Rows.Count; i++)
{
strtxtTicketNumber[i] = dtdgvSellTicketSearch.Rows[i]["TicketNumber"].ToString().Trim();
}
txtTicketNumber.AutoCompleteSource = AutoCompleteSource.CustomSource;//设置票号自动完成源方法
txtTicketNumber.AutoCompleteCustomSource.AddRange(strtxtTicketNumber);
txtTicketNumber.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
string[] strtxtStation = new string[dtdgvSellTicketSearch.Rows.Count];
for (int j = 0; j < dtdgvSellTicketSearch.Rows.Count; j++)
{
strtxtStation[j] = dtdgvSellTicketSearch.Rows[j]["StationName"].ToString().Trim();
}
txtStation.AutoCompleteSource = AutoCompleteSource.CustomSource;//设置站点自动完成源方法
txtStation.AutoCompleteCustomSource.AddRange(strtxtStation);
txtStation.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
string[] strtxtClasses = new string[dtdgvSellTicketSearch.Rows.Count];
for (int k = 0; k < dtdgvSellTicketSearch.Rows.Count; k++)
{
strtxtClasses[k] = dtdgvSellTicketSearch.Rows[k]["Classes"].ToString().Trim();
}
txtClasses.AutoCompleteSource = AutoCompleteSource.CustomSource;//设置班次自动完成源方法
txtClasses.AutoCompleteCustomSource.AddRange(strtxtClasses);
txtClasses.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
int one = 0;
for (int i = 0; i < dgvSellTicketSearch.Rows.Count; i++)
{
one++;
}
label2.Text = Convert.ToString(one);//记录文本当前数量
SheZhiCheXingYanSe();//调用自定义方法
}
#endregion
#region 多条件查询功能:btnSearch_Click单击事件
private void btnSearch_Click(object sender, EventArgs e)
{
DataView dvdgvSellTicketSearch = new DataView(dtdgvSellTicketSearch);//实例化调用数据
DateTime dtSellTicketDate = dtpSellTicketDate.MinDate;//获取日期最小值
DateTime dtSellTicketDateOne = dtpSellTicketDateOne.MaxDate;//获取日期最大值
string strTicketNumber = "";
string strStation = "";
string strVehicleType = "";
string strTicketType = "";
string strClasses = "";//声明变量并赋值
if (chkSellTicketDate.Checked == true) //判断条件
{
dtSellTicketDate = dtpSellTicketDate.Value; //获取日期值
dtSellTicketDateOne = dtpSellTicketDateOne.Value;
}
if (txtTicketNumber.Text != "")//判断文本是否为空
{
strTicketNumber = txtTicketNumber.Text.ToString().Trim();//获取值
}
if (txtStation.Text != "")//判断文本是否为空
{
strStation = txtStation.Text.ToString().Trim();//获取值
}
if (cboVehicleType.Text != "")//判断文本是否为空
{
strVehicleType = cboVehicleType.Text.ToString().Trim();//获取值
}
if (cboTicketType.Text != "")//判断文本是否为空
{
strTicketType = cboTicketType.Text.ToString().Trim();//获取值
}
if (txtClasses.Text != "")//判断文本是否为空
{
strClasses = txtClasses.Text.ToString().Trim();//获取值
}
dvdgvSellTicketSearch.RowFilter = "SellTicKetTime >='" + dtSellTicketDate + "' AND SellTicKetTime<='" + dtSellTicketDateOne +
"' AND TicketNumber LIKE'%" + strTicketNumber +"%'AND StationName LIKE'%" + strStation + "%'AND VehicleTypeName LIKE'%" +
strVehicleType + "%'AND BillTypeName LIKE'%" + strTicketType + "%'AND Classes LIKE'%" + strClasses + "%'";//获取查询方法
dgvSellTicketSearch.DataSource = dvdgvSellTicketSearch.ToTable();//返回数据查询信息
int one = 0;
for (int i = 0; i < dgvSellTicketSearch.Rows.Count; i++)
{
one++;
}
label2.Text = Convert.ToString(one);//记录文本当前数量
SheZhiCheXingYanSe();//调用自定义方法
}
#endregion
#region 自定义方法功能
void SheZhiCheXingYanSe()//自定义方法根据车型显示不同单元格颜色
{
for (int i = 0; i < dgvSellTicketSearch.Rows.Count; i++)//循环获取值
{
string strCheXing = dgvSellTicketSearch.Rows[i].Cells["车型"].Value.ToString().Trim();//获取值
if (strCheXing =="大型车")//判断条件
{
dgvSellTicketSearch.Rows[i].DefaultCellStyle.BackColor = Color.MediumAquamarine;
}
else
{
if (strCheXing == "中型车")
{
dgvSellTicketSearch.Rows[i].DefaultCellStyle.BackColor = Color.OrangeRed;
}
else
{
dgvSellTicketSearch.Rows[i].DefaultCellStyle.BackColor = Color.MediumOrchid;
}
}
}
}
#endregion
}
}
以上是领导查询-售票查询功能实现步骤:数据库-->BLL-->UIL
-------实现功能待续!!!