定位——条件查询
定位实现预约单据的条件查询功能。【满足所有条件】就是条件的累加,所有条件都满足后才查询出来。【满足任一条件】就是只要只要满足其中任意一个条件就会查询出来。
从定位界面上可以看到控件:
控件 | 说明 |
单选框(RadioButton) | 控件可以在工具箱直接拖动至窗体,拖至窗体后右击属性可以修改控件的样式和各种属性,还可以编辑事件。 |
文本框(TextBox) | |
日期控件(DateTimePicker) | |
按钮(Button) |
1、数据库功能实现
第一步:数据库
1、表和关系
表1、预约单表(PW_BespeakBillList)
列名 | 数据类型 | 主键/外键 | 说明 |
BespeakBillID | int - Identity | 主键 | 预约单ID |
BespeakOddNumBer | nchar (20) | 预约单号 | |
CarNewsID | int | 外键 | 车辆信息表,车辆信息ID |
BespeakTime | datetime | 预约时间 | |
AttributeMinuteID_BespeakWay | int | 外键 | 属性明细表,属性明细ID_预约方式 |
BespeakMileage | decimal (18, 2) | 预约里程 | |
BespeakStatus | nchar (20) | 预约状态 | |
Gross | decimal (18, 2) | 总计金额 | |
FailCause | nchar (100) | 失败原因 | |
StaffID_HearPersons | int | 外键 | 员工档案表,员工ID_受理人 |
StaffID_Receiver | int | 外键 | 员工档案表,员工ID_接待人 |
LastTimeInTheFactory | nchar (20) | 上次进厂时间 | |
IfBespeakSucceed | bit | 预约成功否 | |
StaffID_AlterationPerson | nchar (20) | 外键 | 员工档案表,员工ID_变更人 |
BookingCarDeliveryTime | nchar (20) | 预约交车时间 | |
BespeakWarnTime | nchar (20) | 预约提醒时间 | |
ServiceOddNumber | nchar (20) | 外键 | 维修单表,维修单号 |
NewBespeakOddlNumber | nchar (20) | 新预约单号 | |
OldBespeakOddNumber | nchar (20) | 旧预约单号 | |
AlterationTime | nchar (20) | 变更时间 | |
AlterationCauses | nchar (100) | 变更原因 | |
ClientDescribe | nchar (100) | 客户描述 | |
Remarks | nchar (100) | 备注 | |
IfResourceRelease | bit | 资源释放否 |
表2、车辆信息表(BM_CarNewsList)
列名 | 数据类型 | 主键/外键 | 说明 |
CarNewsID | int - Identity | 主键 | 车辆信息ID |
CarOwnerNewsID | int | 外键 | 车主信息ID |
RecordNumber | nchar (20) |
| 档案号 |
LicensePlateNumber | nchar (20) |
| 车牌号 |
CarModelsCode | nchar (20) |
| 车型代码 |
VINCode | nchar (20) |
| VIN码 |
MotorModel | nchar (20) |
| 发动机型号 |
Transmissiontype | nchar (20) |
| 变速器型式 |
MotorNumber | nchar (20) |
| 发动机号 |
TransmissionNumber | nchar (20) |
| 变速箱号码 |
KeyNumber | nchar (20) |
| 钥匙号 |
ShiftWay | nchar (20) |
| 换挡方式 |
CarModelsYearFund | nchar (20) |
| 车型年款 |
Displacement | nchar (20) |
| 排量 |
EquipmentCode | nchar (20) |
| 装备代码 |
BodyworkColour | nchar (10) |
| 车身颜色 |
LeaveFactoryDate | datetime |
| 出厂日期 |
FuelKind | nchar (20) |
| 燃料种类 |
BuyCarDate | datetime |
| 购车日期 |
BuyCarMileage | decimal (18, 2) |
| 购车里程 |
Purpose | nchar (20) |
| 用途 |
SellUnit | nchar (20) |
| 销售单位 |
CarBrand | nchar (20) |
| 车辆品牌 |
CarModelsSimpleCode | nchar (20) |
| 车型简码 |
IfInWarrantyperiod | bit |
| 是否在保修期内 |
UserManage | bit |
| 用户管理 |
IfEffective | bit |
| 有效否 |
表3、车主信息表(BM_CarOwnerNewsList)
列名 | 数据类型 | 说明 | 说明 |
CarOwnerNewsID | int - Identity | 主键 | 车主信息ID |
CarOwnerCode | nchar (20) |
| 车主代码 |
CarOwnerName | nchar (20) |
| 车主名称 |
AttributeMinuteID_ClientTypeOne | int | 外键 | 属性明细表,属性明细ID_客户类型一 |
AttributeMinuteID_ClientTypeTwo | int | 外键 | 属性明细表,属性明细ID_客户类型二 |
AttributeMinuteID_Sex | int | 外键 | 属性明细表,属性明细ID_性别 |
Site | nchar (100) |
| 地址 |
MobilePhone | nchar (20) |
| 手机号码 |
HousePhone | nchar (20) |
| 住宅电话 |
OfficePhone | nchar (20) |
| 办公电话 |
AddressPostcode | nchar (20) |
| 住址邮编 |
TheGenusCountiesAndCities | nchar (50) |
| 所属县市 |
WorkUnit | nchar (50) |
| 工作单位 |
Job | nchar (50) |
| 职位 |
AttributeMinuteID_MaritalStatus | int | 外键 | 属性明细表,属性明细ID_婚姻状况 |
IDCard | nchar (30) |
| 身份证 |
Birthday | datetime |
| 生日 |
Hobby | nchar (100) |
| 爱好 |
ChangeSite | nchar (100) |
| 变跟地址 |
Postcode | nchar (20) |
| 邮编 |
Nationality | nchar (50) |
| 国籍 |
| nchar (50) |
| |
FacilitateTheReturnTime | datetime |
| 方便回访时间 |
CarOwnerPicture | nchar (3000) |
| 车主照片 |
Remarks | nchar (50) |
| 备注 |
IfEffective | bit |
| 有效否 |
功能实现
1、条件查询数据——“确定”按钮点击事件。
第一步:数据库存储过程。
IF(@TYPE='FRM_YuYueGuanLi_Load_SelectYuYue')
BEGIN
SELECT PW_BespeakBillList.BespeakBillID, PW_BespeakBillList.BespeakOddNumBer, PW_BespeakBillList.CarNewsID, PW_BespeakBillList.BespeakTime,
PW_BespeakBillList.AttributeMinuteID_BespeakWay, PW_BespeakBillList.BespeakMileage, PW_BespeakBillList.BespeakStatus, PW_BespeakBillList.Gross, PW_BespeakBillList.FailCause,
PW_BespeakBillList.StaffID_HearPersons, PW_BespeakBillList.StaffID_Receiver, PW_BespeakBillList.ApprovalTime, PW_BespeakBillList.LastTimeInTheFactory,
PW_BespeakBillList.IfBespeakSucceed, PW_BespeakBillList.BookingCarDeliveryTime, PW_BespeakBillList.BespeakWarnTime, PW_BespeakBillList.PredictAOGTime,
PW_BespeakBillList.ServiceOddNumber, PW_BespeakBillList.NewBespeakOddlNumber, PW_BespeakBillList.OldBespeakOddNumber, PW_BespeakBillList.NeedOddNumber,
PW_BespeakBillList.AlterationTime, PW_BespeakBillList.AlterationCauses, PW_BespeakBillList.ClientDescribe, PW_BespeakBillList.Remarks, PW_BespeakBillList.IfResourceRelease,
BM_CarNewsList_1.CarOwnerNewsID, BM_CarNewsList_1.BuyCarDate, BM_CarNewsList_1.LicensePlateNumber, BM_CarNewsList_1.VINCode, BM_CarNewsList_1.CarModelsCode,
BM_CarOwnerNewsList.CarOwnerCode, BM_CarOwnerNewsList.MobilePhone, BM_CarNewsList_1.BodyworkColour, AttributeMinuteList.AttributeMinuteName AS BespeakWay,
BM_StaffRecordList_1.StaffName AS Receiver, BM_StaffRecordList.StaffName AS HearPersons, PW_BespeakBillList.ApprovalPerson, PW_BespeakBillList.AlterationPerson,
PW_BespeakBillList.IfApproval, BM_CarOwnerNewsList.CarOwnerName
FROM AttributeMinuteList INNER JOIN
BM_StaffRecordList INNER JOIN
PW_BespeakBillList INNER JOIN
BM_CarNewsList AS BM_CarNewsList_1 ON PW_BespeakBillList.CarNewsID = BM_CarNewsList_1.CarNewsID INNER JOIN
BM_CarOwnerNewsList ON BM_CarNewsList_1.CarOwnerNewsID = BM_CarOwnerNewsList.CarOwnerNewsID ON BM_StaffRecordList.StaffID = PW_BespeakBillList.StaffID_HearPersons INNER JOIN
BM_StaffRecordList AS BM_StaffRecordList_1 ON PW_BespeakBillList.StaffID_Receiver = BM_StaffRecordList_1.StaffID ON
AttributeMinuteList.AttributeMinuteID = PW_BespeakBillList.AttributeMinuteID_BespeakWay
WHERE (PW_BespeakBillList.IfEffective = 1 and PW_BespeakBillList.IfResourceRelease=0)
END
第二步:逻辑层(BLL)
//查询预约信息
[OperationContract]
public DataSet FRM_YuYueGuanLi_Load_SelectYuYue()
{
SqlParameter[] mySqlParameters =
{
new SqlParameter("@TYPE",SqlDbType.Char),
};
mySqlParameters[0].Value = "FRM_YuYueGuanLi_Load_SelectYuYue";
DataTable dt=myDALMethod.QueryDataTable("预约管理_FRM_YuYueGuanLi", mySqlParameters);
DataSet ds=new DataSet();
ds.Tables.Add(dt);
return ds; //返回数据集
}
第三步:界面层(UIL),定位界面。
BLL海马汽车销售系统.预约管理.FRM_YuYueGuanLi.FRM_YuYueGuanLiClient myFRM_YuYueGuanLiClient =
new BLL海马汽车销售系统.预约管理.FRM_YuYueGuanLi.FRM_YuYueGuanLiClient();
public static DataTable dtYuYueGuanLiDanJu; //公共静态表
public static bool blnKuaiGuan; //公共静态变量
private void btnConFirm_Click(object sender, EventArgs e)
{
//给自定义表赋值
DataTable dt = myFRM_YuYueGuanLiClient.FRM_YuYueGuanLi_Load_SelectYuYue().Tables[0];
DataView dv = new DataView(dt);//过滤表
string str = "";//定义字符串
string strDanYi = "";
if (tdbContentAll.Checked)//选中满足所有条件
{
if (txtWorkOddNember.Text == "")//判断预约单号为不为空
{
//如果为空就跳出这个事件
MessageBox.Show("单号不能为空!");
return;
}
else
{
//不为空就进行条件的字符串拼接
str = " BespeakTime >='" + dtpStartEntranceTime.Value.ToShortDateString() + " 00:00:00.000" +
"' and BespeakTime <='" + dtpEndEntranceTime.Value.ToShortDateString() + " 23:59:59.999" + "' and";
str += " BespeakOddNumBer = '" + txtWorkOddNember.Text.Trim() + "' and";
}
if (str.Length > 1)//判断字符串的长度是否大于1
{
//如果大于1就截断字符串的后三位
str = str.Remove(str.Length - 3);
}
dv.RowFilter = str;//根据条件字符串过滤
dtYuYueGuanLiDanJu = dv.ToTable();//将dv转换成表并赋值
}
else if (rdbContentSingle.Checked)//选中满足任一条件
{
if (rdbContentSingle.Checked)
{
//条件的字符串拼接
str += " BespeakTime >='" + dtpStartEntranceTime.Value.ToShortDateString() + " 00:00:00.000" + "' and BespeakTime <='" + dtpEndEntranceTime.Value.ToShortDateString() + " 23:59:59.999" + "' and";
}
if (str.Length > 1)
{
str = str.Remove(str.Length - 3);
}
dv.RowFilter = str;
dtYuYueGuanLiDanJu = dv.ToTable();
if (rdbContentSingle.Checked)
{
//条件的字符串拼接
strDanYi += " BespeakOddNumBer = '" + txtWorkOddNember.Text.Trim() + "' and";
}
if (strDanYi.Length > 1)
{
strDanYi = strDanYi.Remove(strDanYi.Length - 3);
}
dv.RowFilter = strDanYi;
DataTable dtDanHao = new DataTable();//实例化表
dtDanHao = dv.ToTable();
for (int i = 0; i < dtDanHao.Rows.Count; i++)//循环表
{
DataRow row = dtYuYueGuanLiDanJu.NewRow();//表dtYuYueGuanLiDanJu添加一行
row.ItemArray = dtDanHao.Rows[i].ItemArray;//获取行数据
dtYuYueGuanLiDanJu.Rows.Add(row);//把行数据添加进表dtYuYueGuanLiDanJu
}
}
blnKuaiGuan = true; //静态变量的值为“true”
this.Close();//关闭窗体
}
第三步:界面层(UIL),主界面,“定位”按钮点击事件。
private void btnLocation_Click(object sender, EventArgs e)
{
//实例化窗体
FRM_YuYueGuanLi_DingWei myFRM_YuYueGuanLi_DingWei = new FRM_YuYueGuanLi_DingWei();
myFRM_YuYueGuanLi_DingWei.ShowDialog();//显示窗体
if (FRM_YuYueGuanLi_DingWei.blnKuaiGuan == true) //如果静态变量的值为“true”
{
dgvBespeak.DataSource = FRM_YuYueGuanLi_DingWei.dtYuYueGuanLiDanJu;//把表绑定到DataGridView
}
}
以上仅供参考学习,禁止用于商业用途!!!