public List <GuestRecord> GetRoomTypeALL()
{
List <GuestRecord> rList = new List <GuestRecord>();
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT a.GuestId,a.GuestName,a.IdentityID,a.ResideDate,a.Deposit,c.ResideName,a.LeaveDate,a.TotalMoney,b.RoomID,d.TypeName,e.RoomStateName,c.ResideId");
sb.AppendLine("FROM GuestRecord a INNER JOIN Room b ON a.RoomID=b.RoomID");
sb.AppendLine("INNER JOIN ResideState c ON a.ResideID=c.ResideId");
sb.AppendLine("INNER JOIN RoomType d ON b.RoomTypeID=d.TypeID");
sb.AppendLine("INNER JOIN RoomState e ON b.RoomStateID=e.RoomStateID");
try
{
DBHelper.connection.Open();
SqlCommand cmd = new SqlCommand(sb.ToString(), DBHelper.connection);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
GuestRecord Record = new GuestRecord();
string LeaveDate = dr["LeaveDate"].ToString();
if (!string.IsNullOrEmpty(LeaveDate))
{
Record.LeaveDate = DateTime.Parse(LeaveDate);
}
Record.Deposit = double.Parse(dr["Deposit"].ToString());
Record.GuestId = Convert.ToInt32(dr["GuestId"].ToString());
Record.GuestName = dr["GuestName"].ToString();
Record.IdentityId = dr["IdentityId"].ToString();
Record.ResideDate = DateTime.Parse(dr["ResideDate"].ToString());
Record.ResideId = Convert.ToInt32(dr["ResideId"].ToString());
Record.RoomId = Convert.ToInt32(dr["RoomId"].ToString());
string TotalMoney = dr["TotalMoney"].ToString();
if (!string.IsNullOrEmpty(TotalMoney))
{
Record.TotalMoney = double.Parse(TotalMoney);
}
Record.ResideName = dr["ResideName"].ToString();
Record.RoomStateName = dr["RoomStateName"].ToString();
Record.TypeName = dr["TypeName"].ToString();
rList.Add(Record);
}
dr.Close();
}
catch (Exception)
{
MessageBox.Show("加载房间类型异常!");
}
finally
{
DBHelper.connection.Close();
}
return(rList);
}
/// <summary>
/// 登记订单
/// </summary>
private void Register()
{
if (NonEmpty())
{
string name = txtName.Text.Trim();
string cardNo = txtNum.Text.Trim();
string phone = txtPhone.Text.Trim();
//日期格式化
string arrivalDateStr = dtpArrivalDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
//性别
bool sex = true;
if (rboMale.Checked == true)
{
sex = true;
}
else
{
sex = false;
}
//为单个房间登记入住判断（不讲）
if (roomId == 0)
{
roomId = Convert.ToInt32(cbxRoom.Text.Trim());
}
//押金
double money = Convert.ToDouble(txtMoney.Text.Trim());
//客户信息对象初始化
GuestInfo info = new GuestInfo()
{
Name = name, CardNo = cardNo, Phone = phone, Sex = sex
};
// 客户信息ID
string guestId = string.Empty;
if (checkComm.CheckExist(cardNo))
{
guestId = checkComm.UpdateGuestInfo(info);
}
else
{
guestId = checkComm.AddGuestInfo(info);
}
//订单对象初始化
GuestRecord guest = new GuestRecord()
{
GuestID = Convert.ToInt32(guestId), Deposit = money, ArrivalDateStr = arrivalDateStr, RoomId = roomId
};
checkComm.AddOrder(guest);
MessageBox.Show("登记成功");
Initialize();
}
}
/// <summary>
/// 加载信息
/// </summary>
private void LoadInfo()
{
GuestRecord guest = guestInfoComm.SLoadOrder(RoomId);
txtName.Text = guest.Name;
txtId.Text = guest.CardNo;
txtPhone.Text = guest.Phone;
txtSex.Text = guest.SexStr;
txtDeposit.Text = guest.Deposit.ToString();
txtArrivalDate.Text = guest.ArrivalDate.ToString();
string gid = guest.ID.ToString();
CheckOutInfo outInfo = guestInfoComm.CheckOutInfo(gid);
txtRoomTotal.Text = outInfo.RoomTotal.ToString();
txtFoodTotal.Text = outInfo.FoodTotal.ToString();
txtMoney.Text = outInfo.TotalAmount.ToString();
}
/// <summary>
/// 根据房间编号获取状态为1的入住记录
/// </summary>
private void GetGuestInfoWifhFjbh()
{
try
{
guestrecord = Bll_Guset.GetRecordInfo(fjbh);
lb_khxm.Text = guestrecord.khxm;
lb_sfzmhm.Text = guestrecord.sfzmhm;
lb_rzrq.Text = guestrecord.rzrq.ToString();
}
catch (MySqlException ex)
{
MessageBox.Show("数据库异常：" + ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("其它异常：" + ex.Message);
}
}
/// <summary>
/// 根据房间编号获取入住信息
/// </summary>
/// <param name="fjbh"></param>
/// <returns></returns>
public static GuestRecord GetRecordInfo(string fjbh)
{
GuestRecord guestrecord = new GuestRecord();
string sql = "select fjbh,khxm,sfzmhm,rzrq from guestrecord where fjbh=@fjbh and rzzt='1'";
MySqlParameter[] para =
{
new MySqlParameter("@fjbh", fjbh)
};
MySqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text, para);
reader.Read();
guestrecord.fjbh = reader["fjbh"].ToString();
guestrecord.khxm = reader["khxm"].ToString();
guestrecord.sfzmhm = reader["sfzmhm"].ToString();
guestrecord.rzrq = Convert.ToDateTime(reader["rzrq"].ToString());
return(guestrecord);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="g"></param>
/// <returns></returns>
public static int InsertGuestRecord(GuestRecord g)
{
string sql = SqlHelper.Dbowner + ".usp_insertGuestRecord";
SqlParameter[] pars = new SqlParameter[] {
new SqlParameter("@identityID", SqlDbType.VarChar),
new SqlParameter("@guestName", SqlDbType.VarChar),
new SqlParameter("@roomID", SqlDbType.Int),
new SqlParameter("@deposit", SqlDbType.Decimal),
new SqlParameter("@ResideDate", SqlDbType.DateTime),
};
pars[0].Value = g.IdentityID;
pars[1].Value = g.GuestName;
pars[2].Value = g.RoomID;
pars[3].Value = g.Deposit;
pars[4].Value = g.ResideDate;
return(SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, sql, pars));
}
/// <summary>
/// 添加
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
GuestRecord gu = new GuestRecord();
gu.IdentityID = txtidentityID.Text.Trim();
gu.GuestName = txtguestName.Text.Trim();
gu.RoomID = Convert.ToInt32(cmbroomName.SelectedValue);
gu.Deposit = Convert.ToDecimal(txtdeposit.Text.Trim());
gu.ResideDate = Convert.ToDateTime(dtpResideDate.Text);
int num = GuestRecordManager.InsertGuestRecord(gu);
if (num > 0)
{
MessageBox.Show("添加成功！");
}
else
{
MessageBox.Show("添加失败！");
}
}
/// <summary>
/// 入住登记
/// </summary>
/// <param name="guest"></param>
/// <returns></returns>
public static int AddGuest(GuestRecord guest)
{
string sql = "insert into guestrecord(lsh,khxm,khxb,sfzmhm,lxdh,fjbh,rzzt,rzrq,rzts,rzyj,ldrq) "
+ "values(@rzbh,@GuestName,@Gender,@IdentityId,@Phone,@RoomId,@ResideId,@ResideDate,@TradeNo,@Deposit,@LeaveDate);"
+ "update room set fjzt=@RoomStateId where fjbh=@RoomId";
MySqlParameter[] para =
{
new MySqlParameter("@rzbh", guest.lsh),
new MySqlParameter("@GuestName", guest.khxm),
new MySqlParameter("@Gender", guest.xb == "男"?0:1),
new MySqlParameter("@IdentityId", guest.sfzmhm),
new MySqlParameter("@Phone", guest.lxdh),
new MySqlParameter("@RoomId", guest.fjbh),
new MySqlParameter("@ResideId", guest.rzzt),
new MySqlParameter("@ResideDate", guest.rzrq),
new MySqlParameter("@Deposit", guest.rzyj),
new MySqlParameter("@RoomStateId", 1),
new MySqlParameter("@TradeNo", guest.rzts),
new MySqlParameter("@LeaveDate", guest.tfrq)
};
return(SqlHelper.ExecuteNonQuery(sql, CommandType.Text, para));
}
private ObservableCollection <GuestRecord> ConvertGuestRecords(List <Guest> guests)
{
guests = guests.OrderByDescending(g => g.TimeIn).ToList();
ObservableCollection <GuestRecord> guestRecords = new ObservableCollection <GuestRecord>();
foreach (var guest in guests)
{
GuestRecord guestRecord = new GuestRecord()
{
FirstName = guest.FirstName,
LastName = guest.LastName,
Purpose = guest.Purpose,
ContactPerson = guest.ContactPerson,
IdNumber = guest.IdNumber,
TimeIn = guest.TimeIn.ToString("HHmm"),
TimeOut = guest.TimeOut.HasValue ? guest.TimeOut.Value.ToString("HHmm") : string.Empty,
ID = guest.ID
};
guestRecords.Add(guestRecord);
}
return(guestRecords);
}
private void btn_ok_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txt_khxm.Text.Trim()))
{
toolTip1.Show("客户姓名不能为空!", this.txt_khxm, 1000);
txt_khxm.Focus();
return;
}
if (string.IsNullOrEmpty(txt_sfzmhm.Text.Trim()))
{
toolTip1.Show("身份证号不能为空!", this.txt_sfzmhm, 1000);
txt_sfzmhm.Focus();
return;
}
if (string.IsNullOrEmpty(txt_rzts.Text.Trim()))
{
toolTip1.Show("入住天数不能为空!", this.txt_rzts, 1000);
txt_rzts.Focus();
return;
}
if (string.IsNullOrEmpty(txt_lxdh.Text.Trim()))
{
toolTip1.Show("联系电话不能为空!", this.txt_lxdh, 1000);
txt_lxdh.Focus();
return;
}
if (this.cb_rzfj.SelectedIndex == 0)
{
toolTip1.Show("请选择入住房间类型", this.cb_rzfj, 1000);
cb_rzfj.Focus();
return;
}
GuestRecord guest = new GuestRecord();
guest.lsh = lshCreate.GetLsh();
guest.khxm = this.txt_khxm.Text.Trim();
guest.sfzmhm = this.txt_sfzmhm.Text.Trim();
guest.lxdh = this.txt_lxdh.Text.Trim();
guest.xb = this.cb_khxb.Text;
guest.rzyj = this.txt_rzyj.Text.Trim();
guest.fjbh = Bll_Room.GetFreeRoomIdWithFjlx(cb_rzfj.SelectedValue.ToString()).ToString();
guest.rzrq = this.dtp_rzsj.Value;
guest.rzts = this.txt_rzts.Text.Trim();
guest.tfrq = guest.rzrq.AddDays(Convert.ToInt32(guest.rzts));
guest.rzzt = "1";
try
{
if (Bll_Guset.AddGuest(guest) > 0)
{
MessageBox.Show("入住办理成功!房间号：" + guest.fjbh, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (MySqlException ex)
{
MessageBox.Show("数据库异常：" + ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("其它异常：" + ex.Message);
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="g"></param>
/// <returns></returns>
public static int InsertGuestRecord(GuestRecord g)
{
return(GuestRecordService.InsertGuestRecord(g));
}
/// <summary>
/// 入住登记
/// </summary>
/// <param name="guest"></param>
/// <returns></returns>
public static int AddGuest(GuestRecord guest)
{
return(Dal_Guest.AddGuest(guest));
}
