2.4-3 废票查询
废票查询是基于售票管理-废票模块的基础的一个功能,记录着由于各种原因所造成废票的废票记录明细信息,可以根据多条件查询相关废票,也可以更详细知道售票信息,功能如下图2.4-3
从界面上可以看到我们这里用到工具箱的控件有:
控件名称 | 说明 |
容器控件(框) GroupBox | 每个控件都可以根据需要的功能更改其属性,右键查看.也可以进行编辑事件-->找到一个闪电图标点击查看需要的事件,例如:查询按钮(btnSrean)是修改过按钮文本Text属性的重命名! btnSrean_Click这是单击查询事件,还有很多的事件根据项目需求找到来编辑.
|
公共控件(单选框)CheckBox | |
公共控件(文字描写) Label | |
公共控件(按钮)Button | |
公共控件(日期)DateTimePicker | |
公共控件(文本框)TextBox | |
所有Windows窗体(dgv)DataGridView | |
公共控件(下拉框)ComboBox |
退票查询功能实现:
第一步:数据库
1、 表与表关系(图2.4-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:座号表 (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:票据类型表 (BillTypeList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
BillTypeID | int - Identity | 主键 | 票据类型 |
BillTypeName | char (100) |
| 票据类型MC |
TicketPrice | decimal (18, 3) |
| 票价 |
表7:出车表(OutVehicleList)
主要包含如下字段信息:(说明)
列名 | 数据类型 | 主键/外键 | 说明 |
AbolishTicketID | int - Identity | 主键 | 废票ID |
AbolishTicketCause | char (100) |
| 废票原因 |
BillID | int | 外键 | 票据ID |
SatffID | int | 外键 | 员工ID |
AbandonTicketTime | datetime |
| 废票时间 |
表8:员工表 (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) |
| 相片 |
下面开始编写数据库储存过程:
数据库对应逻辑层(BLL)和界面层(UIL)文件夹: LeadDemand_frmAbolishTicketSearch(废票查询窗体和对应的类的存储过程)
USE [客运综合管理系统2015]
GO
/****** Object: StoredProcedure [dbo].[LeadDemand_frmAbolishTicketSearch] Script Date: 06/02/2015 15:17:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[LeadDemand_frmAbolishTicketSearch]--[领导查询-废票查询]
@Type CHAR(200)=''
AS
BEGIN
IF @Type='frmAbolishTicketSearch_Load_cboAbolishTicketStaff'--绑定cboAbolishTicketStaff
BEGIN
SELECT StaffID, LTRIM(RTRIM(StaffName))AS StaffName
FROM StaffList
END
IF @Type='frmAbolishTicketSearch_Load_dgvAbolishTicket'--绑定dgvAbolishTicket
BEGIN
SELECT ASeatNumberList.ASeatNumberID, VehicleList.VehicleID,LTRIM(RTRIM(VehicleList.VehicleBlockNumber))AS VehicleBlockNumber,
LTRIM(RTRIM(ASeatNumberList.ASeatNumber))AS ASeatNumber, LTRIM(RTRIM(BillList.GetOffStation))AS GetOffStation,
LTRIM(RTRIM(BillList.GetOnStation))AS GetOnStation, LTRIM(RTRIM(BillTypeList.BillTypeName))AS BillTypeName,
LTRIM(RTRIM(BillList.SellTicKetYuan))AS SellTicKetYuan, BillList.SellTicKetTime, LTRIM(RTRIM(StaffList.StaffName))AS StaffName,
LTRIM(RTRIM(VehicleList.Classes))AS Classes, VehicleList.OpenVehicleTime, StationList.StationID AS OpenVehicleSiteID,
LTRIM(RTRIM(StationList.StationName)) AS OpenVehicleSite, StationList_1.StationID AS EndVehicleSiteID, LTRIM(RTRIM(StationList_1.StationName)) AS EndVehicleSite,
LTRIM(RTRIM(TicketNumberList.TicketNumber))AS TicketNumber, AbolishTicketList.BillID,
StaffList_1.StaffID, LTRIM(RTRIM(StaffList_1.StaffName)) AS StaffNameOne, AbolishTicketList.AbandonTicketTime,
LTRIM(RTRIM(AbolishTicketList.AbolishTicketCause))AS AbolishTicketCause, TicketNumberList.TicketNumberID
FROM ASeatNumberList INNER JOIN
VehicleList ON ASeatNumberList.VehicleID = VehicleList.VehicleID INNER JOIN
BillList ON ASeatNumberList.ASeatNumberID = BillList.ASeatNumberID INNER JOIN
BillTypeList ON BillList.BillTypeID = BillTypeList.BillTypeID INNER JOIN
StaffList ON BillList.SellTicketSatffID = StaffList.StaffID INNER JOIN
StationList ON VehicleList.OpenVehicleSiteID = StationList.StationID INNER JOIN
StationList AS StationList_1 ON VehicleList.EndVehicleSiteID = StationList_1.StationID INNER JOIN
AbolishTicketList ON BillList.BillID = AbolishTicketList.BillID INNER JOIN
StaffList AS StaffList_1 ON AbolishTicketList.SatffID = StaffList_1.StaffID INNER JOIN
TicketNumberList ON BillList.TicketNumberID = TicketNumberList.TicketNumberID
ORDER BY AbolishTicketList.AbandonTicketTime DESC--数据按最新日期排序
END
END
第二步:服务端BLL(逻辑层)写方法<---调用数据库代码:
对应文件夹: LeadDemand_frmAbolishTicketSearch.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 frmAbolishTicketSearch
{
DALPublic.DALMethod myDALMethod = new DALPublic.DALMethod();
#region 绑定cboAbolishTicketStaff方法
[OperationContract]//添加行为标签
public DataSet frmAbolishTicketSearch_Load_cboAbolishTicketStaff()//自定义方法名称
{
SqlParameter[] mySqlParameters = {
new SqlParameter("@Type", SqlDbType.Char),
};
mySqlParameters[0].Value = "frmAbolishTicketSearch_Load_cboAbolishTicketStaff";//对应数据库名称
DataTable dt = myDALMethod.QueryDataTable("LeadDemand_frmAbolishTicketSearch", mySqlParameters);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;//返回值
}
#endregion
#region 绑定dgvAbolishTicket方法
[OperationContract]//添加行为标签
public DataSet frmAbolishTicketSearch_Load_dgvAbolishTicket()//自定义方法名称
{
SqlParameter[] mySqlParameters = {
new SqlParameter("@Type", SqlDbType.Char),
};
mySqlParameters[0].Value = "frmAbolishTicketSearch_Load_dgvAbolishTicket";//对应数据库名称
DataTable dt = myDALMethod.QueryDataTable("LeadDemand_frmAbolishTicketSearch", mySqlParameters);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;//返回值
}
#endregion
}
}
第三步:客户端UIL(界面层)写调用方法<---调用BLL(逻辑层)代码:
对应文件夹: LeadDemand_frmAbolishTicketSearch.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 frmAbolishTicketSearch : Form
{
public frmAbolishTicketSearch()
{
InitializeComponent();
}
public delegate void ChangeFormColor(bool topmost);//自定义方法调用
public delegate void ChangeFormColor1(bool topmost1);//自定义方法调用
public event ChangeFormColor ChangeColor;//自定义方法调用
public event ChangeFormColor1 ChangeColor1;//自定义方法调用窗体
BLL客运综合管理系统.LeadDemand.frmAbolishTicketSearch.frmAbolishTicketSearchClient myfrmAbolishTicketSearchClient =
new BLL客运综合管理系统.LeadDemand.frmAbolishTicketSearch.frmAbolishTicketSearchClient();//实例化调用BLL方法
private void frmFeiPiaoGuanLi_FormClosing(object sender, FormClosingEventArgs e)
{
ChangeColor1(true);//开关
ChangeColor(true);
}
#region frmAbolishTicketSearch_Load事件功能
DataTable dtdgvAbolishTicket;//声明一个全局变量
private void frmAbolishTicketSearch_Load(object sender, EventArgs e)
{
DataTable dtcboAbolishTicketStaff=myfrmAbolishTicketSearchClient.frmAbolishTicketSearch_Load_cboAbolishTicketStaff().Tables[0];//绑定数据
cboAbolishTicketStaff.DataSource = dtcboAbolishTicketStaff;//下拉框绑定数据方法
cboAbolishTicketStaff.DisplayMember = "StaffName";
cboAbolishTicketStaff.ValueMember = "StaffID";
cboAbolishTicketStaff.SelectedValue = -1;
dtdgvAbolishTicket=myfrmAbolishTicketSearchClient.frmAbolishTicketSearch_Load_dgvAbolishTicket().Tables[0];
dgvReturnATicketRecord.DataSource = dtdgvAbolishTicket;//DGV绑定数据
this.dgvReturnATicketRecord = SYS_PublicStaticClass.SetDgv(dgvReturnATicketRecord);//设置dgv序号
this.dgvReturnATicketRecord.CellBorderStyle = DataGridViewCellBorderStyle.Sunken;//设置dgv三维凹陷边框
this.dgvReturnATicketRecord.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;//设置dgv列标题居中
this.dgvReturnATicketRecord.RowsDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;//设置dgv单元格内容居中
dtpAbolishTicketDate.Format = DateTimePickerFormat.Custom;//设置时间格式2015-04-20 10:46:00
dtpAbolishTicketDate.CustomFormat = "yyyy-MM-dd HH:mm:ss";
dtpAbolishTicketDateOne.Format = DateTimePickerFormat.Custom;
dtpAbolishTicketDateOne.CustomFormat = "yyyy-MM-dd HH:mm:ss";
string[] strtxtTicketNumber = new string[dtdgvAbolishTicket.Rows.Count];
for (int i = 0; i < dtdgvAbolishTicket.Rows.Count; i++)
{
strtxtTicketNumber[i] = dtdgvAbolishTicket.Rows[i]["TicketNumber"].ToString().Trim();
}
txtTicketNumber.AutoCompleteSource = AutoCompleteSource.CustomSource;//设置票号自动完成源方法
txtTicketNumber.AutoCompleteCustomSource.AddRange(strtxtTicketNumber);
txtTicketNumber.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
}
#endregion
#region 多条件查询功能:btnSearch_Click单击事件
private void btnSearch_Click(object sender, EventArgs e)
{
DataView dvAbolishTicket = new DataView(dtdgvAbolishTicket);//实例化调用数据
DateTime dtAbolishTicketDate = dtpAbolishTicketDate.MinDate;//获取日期最小值
DateTime dtAbolishTicketDateOne = dtpAbolishTicketDateOne.MaxDate;//获取日期最大值
string strTicketNumber = "";
string strAbolishTicketStaff = "";//声明一个变量并赋值
if (chkAbolishTicketDate.Checked == true)//判断单选框是否选择
{
dtAbolishTicketDate = dtpAbolishTicketDate.Value;//获取日期文本值
dtAbolishTicketDateOne = dtpAbolishTicketDateOne.Value;//获取日期文本值
}
if (txtTicketNumber.Text != "")//判断文本框是否为空
{
strTicketNumber = txtTicketNumber.Text.ToString().Trim();//获取文本值
}
if (cboAbolishTicketStaff.Text != "")//判断下拉框是否为空
{
strAbolishTicketStaff = cboAbolishTicketStaff.Text.ToString().Trim();//获取值
}
dvAbolishTicket.RowFilter = "AbandonTicketTime>='" + dtAbolishTicketDate + "'AND AbandonTicketTime<='" + dtAbolishTicketDateOne +
"'AND TicketNumber LIKE'%" + strTicketNumber + "%'AND StaffName LIKE'%" + strAbolishTicketStaff + "%'";
dgvReturnATicketRecord.DataSource = dvAbolishTicket.ToTable();//返回数据查询信息
}
#endregion
}
}
以上是领导查询-废票查询功能实现步骤:数据库-->BLL-->UIL
----实现功能待续!!!