public List<BasGoodsModel> GetGoodsInfo(BasGoodsModel mGoods, ObjectControls oCtrl)
{
string sql = @"SELECT GOODS_ID, GOODS_NAME, GOODS_UNIT, PRICE, TYPE, STATUS
FROM BAS_GOODS_INFO WHERE 1=1 ";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByGoodsId) && oCtrl.Append(ref sql, " AND GOODS_ID=" + mGoods.GoodsId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByGoodsName) && oCtrl.Append(ref sql, " AND GOODS_NAME=" + SQL(mGoods.GoodsName)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByGoodsType) && oCtrl.Append(ref sql, " AND TYPE=" + SQL(mGoods.Type)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByGoodsStatus) && oCtrl.Append(ref sql, " AND STATUS=" + SQL(mGoods.Status)));
sdr = ExecuteReader(sql);
List<BasGoodsModel> listGoodsInfo = new List<BasGoodsModel>();
using (sdr)
{
while (sdr.Read())
{
BasGoodsModel mGoodsInfo = new BasGoodsModel();
mGoodsInfo.GoodsId = ToInt32(sdr["GOODS_ID"]);
mGoodsInfo.GoodsName = ToString(sdr["GOODS_NAME"]);
mGoodsInfo.GoodsUnit = ToString(sdr["GOODS_UNIT"]);
mGoodsInfo.Price = ToDouble(sdr["PRICE"]);
mGoodsInfo.Type = ToChar(sdr["TYPE"]);
mGoodsInfo.Status = ToChar(sdr["STATUS"]);
listGoodsInfo.Add(mGoodsInfo);
}
}
return listGoodsInfo;
}
public List<SysParameterModel> GetSysParameter(SysParameterModel mParameter, ObjectControls oCtrl)
{
string sql = "SELECT A.PARAM_ID,A.PARAM_NO,A.PARAM_NAME,A.PARAM_DESC,A.VALUE1,A.VALUE2,A.VALUE3 FROM SYS_PARAMETER A WHERE 1=1";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByParameterId) && oCtrl.Append(ref sql, " AND A.PARAM_ID=" + mParameter.ParamId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByParameterNo) && oCtrl.Append(ref sql, " AND A.PARAM_NO=" + mParameter.ParamNo));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByParameterName) && oCtrl.Append(ref sql, " AND A.PARAM_NAME=" + mParameter.ParamName));
sdr = ExecuteReader(sql);
List<SysParameterModel> listParameter = new List<SysParameterModel>();
using (sdr)
{
while (sdr.Read())
{
SysParameterModel mSysParameterInfo = new SysParameterModel();
mSysParameterInfo.ParamId = ToInt32(sdr["PARAM_ID"]);
mSysParameterInfo.ParamNo = ToString(sdr["PARAM_NO"]);
mSysParameterInfo.ParamName = ToString(sdr["PARAM_NAME"]);
mSysParameterInfo.ParamDesc = ToString(sdr["PARAM_DESC"]);
mSysParameterInfo.Value1 = ToString(sdr["VALUE1"]);
mSysParameterInfo.Value2 = ToString(sdr["VALUE2"]);
mSysParameterInfo.Value3 = ToString(sdr["VALUE3"]);
listParameter.Add(mSysParameterInfo);
}
}
return listParameter;
}
public int UpdateParameter(SysParameterModel mParameter, ObjectControls oCtrl)
{
string sql = "UPDATE SYS_PARAMETER SET PARAM_NO=PARAM_NO ";
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetValue1) && oCtrl.Append(ref sql, ",VALUE1=" + SQL(mParameter.Value1)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetValue2) && oCtrl.Append(ref sql, ",VALUE2=" + SQL(mParameter.Value2)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetValue3) && oCtrl.Append(ref sql, ",VALUE3=" + SQL(mParameter.Value3)));
sql += " WHERE PARAM_ID=" + mParameter.ParamId;
return ExcuteNonQuery(sql);
}
public int UpdateGoods(BasGoodsModel mGoods, ObjectControls oCtrl)
{
string sql = "UPDATE BAS_GOODS_INFO SET TYPE=TYPE";
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetGoodsName) && oCtrl.Append(ref sql, ",GOODS_NAME=" + SQL(mGoods.GoodsName)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetUnit) && oCtrl.Append(ref sql, ",GOODS_UNIT=" + SQL(mGoods.GoodsUnit)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetUnitPrice) && oCtrl.Append(ref sql, ",PRICE=" + SQL(mGoods.Price)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetGoodsStaus) && oCtrl.Append(ref sql, ",STATUS=" + SQL(mGoods.Status)));
sql += " WHERE GOODS_ID=" + mGoods.GoodsId;
return ExcuteNonQuery(sql);
}
/// <summary>
/// 绑定系统代码与描述.用于ComboBox的显示与选取.
/// </summary>
/// <param name="cboTarget"></param>
/// <param name="sTableName"></param>
/// <param name="sColumnName"></param>
public void BindCodeInfo(ComboBox cboTarget, string sTableName, string sColumnName)
{
SysLookUpCodeInfo bSysLookupCode = new SysLookUpCodeInfo();
SysLookupCodeModel mSysLookupCode = new SysLookupCodeModel();
mSysLookupCode.TableName = sTableName;
mSysLookupCode.ColumnName = sColumnName;
ObjectControls oLookCode = new ObjectControls();
oLookCode.Add(MCtrl.ByColumnName);
oLookCode.Add(MCtrl.ByTableName);
cboTarget.DataSource = bSysLookupCode.GetSysLookUpCode(mSysLookupCode, oLookCode);
cboTarget.DisplayMember = "CodeDesc";
cboTarget.ValueMember = "CodeNo";
}
public List<ConsumeDetailModel> GetConsumeList(ConsumeDetailModel mConsume, ObjectControls oCtrl)
{
string sql = @"SELECT CONSUME_ID,A.STAY_ID, A.GOODS_ID, A.CREATE_DATE, A.CREATE_USERID, A.UPDATE_DATE,
A.UPDATE_USERID, A.UNIT_PRICE, A.NUMBER, A.TOTAL,
B.GOODS_NAME, B.GOODS_UNIT, B.PRICE , B.TYPE, B.STATUS,
D.ROOM_NO,E.USER_NAME
FROM CONSUME_DETAIL AS A INNER JOIN
CUSTOMER_STAY_INFO AS C ON A.STAY_ID = C.STAY_ID INNER JOIN
BAS_GOODS_INFO AS B ON A.GOODS_ID = B.GOODS_ID
INNER JOIN
BAS_ROOM_INFO AS D ON C.ROOM_ID = D.ROOM_ID
INNER JOIN
SYS_USER_INFO AS E ON A.CREATE_USERID = E.USER_ID";
sql += " WHERE 1=1";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayIdGroup) && oCtrl.Append(ref sql, " AND A.STAY_ID IN (" + mConsume.StayIdGroup + ")"));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayId) && oCtrl.Append(ref sql, " AND A.STAY_ID=" + mConsume.StayId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByGoodsId) && oCtrl.Append(ref sql, " AND A.GOODS_ID=" + mConsume.GoodsId));
sql += " ORDER BY A.CREATE_DATE";
sdr = ExecuteReader(sql);
List<ConsumeDetailModel> listConsumDetail = new List<ConsumeDetailModel>();
using (sdr)
{
while (sdr.Read())
{
ConsumeDetailModel mConsumeInfo = new ConsumeDetailModel();
mConsumeInfo.ConsumeId = ToInt32(sdr["CONSUME_ID"]);
mConsumeInfo.StayId = ToInt32(sdr["STAY_ID"]);
mConsumeInfo.GoodsId = ToInt32(sdr["GOODS_ID"]);
mConsumeInfo.CommonInfo = new CommonModel();
mConsumeInfo.CommonInfo.CreateDate = ToDateTime(sdr["CREATE_DATE"]);
mConsumeInfo.CommonInfo.CreateUserId = ToInt32(sdr["CREATE_USERID"]);
mConsumeInfo.CommonInfo.UpdateDate = ToDateTime(sdr["UPDATE_DATE"]);
mConsumeInfo.CommonInfo.UpdateUserId = ToInt32(sdr["UPDATE_USERID"]);
mConsumeInfo.UnitPrice = ToDouble(sdr["UNIT_PRICE"]);
mConsumeInfo.Number = ToDouble(sdr["NUMBER"]);
mConsumeInfo.Total = ToDouble(sdr["TOTAL"]);
mConsumeInfo.GoodsInfo = new BasGoodsModel();
mConsumeInfo.GoodsInfo.GoodsName = ToString(sdr["GOODS_NAME"]);
mConsumeInfo.GoodsInfo.GoodsUnit = ToString(sdr["GOODS_UNIT"]);
mConsumeInfo.GoodsInfo.Price = ToDouble(sdr["PRICE"]);
mConsumeInfo.GoodsInfo.Type = ToChar(sdr["TYPE"]);
mConsumeInfo.GoodsInfo.Status = ToChar(sdr["STATUS"]);
mConsumeInfo.RoomNo = ToString(sdr["ROOM_NO"]);
mConsumeInfo.CommonInfo.CreateUserName = ToString(sdr["USER_NAME"]);
listConsumDetail.Add(mConsumeInfo);
}
}
return listConsumDetail;
}
public List<BasRoomModel> GetTeamRoomList(CustomerStayModel mCustomerStay, ObjectControls oCtrl)
{
string sql = @"SELECT A.ROOM_ID,D.MAIN_ROOM_ID, A.ROOM_NO, A.ROOM_TYPE,C.CODE_DESC AS ROOM_TYPE_DESC, A.ROOM_RATES, A.STATUS,
A.CREATE_DATE, A.CREATE_USERID, A.ROOM_PHONE,A.ROOM_NOTICE, A.UPDATE_DATE,A.UPDATE_USERID,
B.FLOOR_ID, B.FLOOR_NAME, B.STATUS AS FLOOR_STATUS
FROM BAS_ROOM_INFO AS A INNER JOIN
CUSTOMER_STAY_INFO AS D ON A.ROOM_ID=D.ROOM_ID INNER JOIN
BAS_FLOOR_INFO AS B ON A.FLOOR_ID = B.FLOOR_ID
INNER JOIN SYS_LOOKUP_CODE AS C ON A.ROOM_TYPE=C.CODE_NO AND C.TABLE_NAME='BAS_ROOM_INFO'
AND C.COLUMN_NAME='ROOM_TYPE' ";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomId) && oCtrl.Append(ref sql, " AND A.ROOM_ID=" + mCustomerStay.RoomId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByMainRoomId) && oCtrl.Append(ref sql, " AND D.MAIN_ROOM_ID=" + mCustomerStay.MainRoomId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayStatus) && oCtrl.Append(ref sql, " AND D.STATUS=" + SQL(mCustomerStay.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomNo) && oCtrl.Append(ref sql, " AND A.ROOM_NO=" + SQL(mCustomerStay.RoomInfo.RoomNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomStatus) && oCtrl.Append(ref sql, " AND A.STATUS=" + SQL(mCustomerStay.RoomInfo.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomStatusGroup) && oCtrl.Append(ref sql, " AND A.STATUS IN (" + mCustomerStay.RoomInfo.StatusGroup + ")"));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomType) && oCtrl.Append(ref sql, " AND A.ROOM_TYPE=" + SQL(mCustomerStay.RoomInfo.RoomType)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByFloorId) && oCtrl.Append(ref sql, " AND B.FLOOR_ID=" + mCustomerStay.RoomInfo.FloorInfo.FloorId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByFloorName) && oCtrl.Append(ref sql, " AND B.FLOOR_NAME=" + SQL(mCustomerStay.RoomInfo.FloorInfo.FloorName)));
sdr = ExecuteReader(sql);
List<BasRoomModel> listRoomInfo = new List<BasRoomModel>();
using (sdr)
{
while (sdr.Read())
{
BasRoomModel mBasRoomInfo = new BasRoomModel();
mBasRoomInfo.RoomId = ToInt32(sdr["ROOM_ID"]);
mBasRoomInfo.MainRoomId = ToInt32(sdr["MAIN_ROOM_ID"]);
mBasRoomInfo.RoomNo = ToString(sdr["ROOM_NO"]);
mBasRoomInfo.RoomPhone = ToString(sdr["ROOM_PHONE"]);
mBasRoomInfo.RoomRate = ToDouble(sdr["ROOM_RATES"]);
mBasRoomInfo.RoomType = ToChar(sdr["ROOM_TYPE"]);
mBasRoomInfo.RoomTypeDesc = ToString(sdr["ROOM_TYPE_DESC"]);
mBasRoomInfo.RoomNotice = ToString(sdr["ROOM_NOTICE"]);
mBasRoomInfo.Status = ToChar(sdr["STATUS"]);
mBasRoomInfo.CommonInfo = new CommonModel();
mBasRoomInfo.CommonInfo.CreateDate = ToDateTime(sdr["CREATE_DATE"]);
mBasRoomInfo.CommonInfo.CreateUserId = ToInt32(sdr["CREATE_USERID"]);
mBasRoomInfo.CommonInfo.UpdateDate = ToDateTime(sdr["UPDATE_DATE"]);
mBasRoomInfo.CommonInfo.UpdateUserId = ToInt32(sdr["UPDATE_USERID"]);
mBasRoomInfo.FloorInfo = new BasFloorModel();
mBasRoomInfo.FloorInfo.FloorId = ToInt32(sdr["FLOOR_ID"]);
mBasRoomInfo.FloorInfo.FloorName = ToString(sdr["FLOOR_NAME"]);
mBasRoomInfo.FloorInfo.Status = ToChar(sdr["FLOOR_STATUS"]);
listRoomInfo.Add(mBasRoomInfo);
}
}
return listRoomInfo;
}
public PrintModel GetPrintConfig(PrintModel mPrint, ObjectControls oCtrl)
{
string sql = @"SELECT A.PRINT_ID, A.PRINT_NAME, A.PRINT_NO,A.START_X,A.START_Y,A.TABLE_ALIGN,
A.LAND_SCAPE,B.PRINT_DETAIL_ID,
B.CONTENT_DESC, B.FONT_SIZE, B.FONT_COLOR,
B.FONT_BOLD, B.FONT_UNDER_LINE, B.ALIGN, B.SITE_X, B.SITE_Y, B.BIND_VALUE, B.VALUE_TYPE,
B.CREATE_DATE, B.CREATE_USERID
FROM PRINT_HEADER AS A INNER JOIN
PRINT_DETAIL AS B ON A.PRINT_ID = B.PRINT_ID";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByPrintNo) && oCtrl.Append(ref sql, " AND A.PRINT_NO=" + SQL(mPrint.PrintNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByPrintId) && oCtrl.Append(ref sql, " AND A.PRINT_ID=" + SQL(mPrint.PrintId)));
sdr = ExecuteReader(sql);
using (sdr)
{
PrintModel mPrintNew = new PrintModel();
mPrintNew.PrintDetailList = new List<PrintDetailModel>();
while (sdr.Read())
{
mPrintNew.PrintId = ToInt32(sdr["PRINT_ID"]);
mPrintNew.PrintNo = ToString(sdr["PRINT_NO"]);
mPrintNew.PrintName = ToString(sdr["PRINT_NAME"]);
mPrintNew.StartX = ToInt32(sdr["START_X"]);
mPrintNew.StartY = ToInt32(sdr["START_Y"]);
mPrintNew.TableAlign = ToInt32(sdr["TABLE_ALIGN"]);
mPrintNew.LandScape = ToInt32(sdr["LAND_SCAPE"]);
PrintDetailModel mPrintDetail = new PrintDetailModel();
mPrintDetail.PrintDetailId = ToInt32(sdr["PRINT_DETAIL_ID"]);
mPrintDetail.ContentDesc = ToString(sdr["CONTENT_DESC"]);
mPrintDetail.FontSize = ToInt32(sdr["FONT_SIZE"]);
mPrintDetail.sFontColor = ToString(sdr["FONT_COLOR"]);
mPrintDetail.nBold = ToInt32(sdr["FONT_BOLD"]);
mPrintDetail.nUnderLine = ToInt32(sdr["FONT_UNDER_LINE"]);
mPrintDetail.Align = ToInt32(sdr["ALIGN"]);
mPrintDetail.SiteX = ToInt32(sdr["SITE_X"]);
mPrintDetail.SiteY = ToInt32(sdr["SITE_Y"]);
mPrintDetail.BindValue = ToString(sdr["BIND_VALUE"]);
mPrintDetail.ValueType = ToChar(sdr["VALUE_TYPE"]);
mPrintDetail.CommonInfo.CreateDate = ToDateTime(sdr["CREATE_DATE"]);
mPrintDetail.CommonInfo.CreateUserId = ToInt32(sdr["CREATE_USERID"]);
mPrintNew.PrintDetailList.Add(mPrintDetail);
}
return mPrintNew;
}
}
public List<CustomerModel> GetCustomerInfo(CustomerModel mCustomer, ObjectControls oCtrl)
{
string sql = @"SELECT CUSTOMER_ID, ID_CARD, NAME, SEX, NATION, BIRTHDAY, ADDRESS, COMPANY,
PHONE, PICTURE, TYPE, CREATE_DATE, CREATE_USERID, UPDATE_DATE,
UPDATE_USERID
FROM CUSTOMER_INFO WHERE 1=1";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCustomerId) && oCtrl.Append(ref sql, " AND CUSTOMER_ID=" + mCustomer.CustomerId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByName) && oCtrl.Append(ref sql, " AND NAME=" + SQL(mCustomer.Name)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByIdCard)&& oCtrl.Append( ref sql, " AND ID_CARD=" + SQL(mCustomer.IdCardNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.BySex)&& oCtrl.Append( ref sql, " AND SEX=" + SQL(mCustomer.Sex)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByPhone)&& oCtrl.Append(ref sql, " AND PHONE=" + SQL(mCustomer.Phone)));
//oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayId)&& oCtrl.Append(ref sql, " AND A.STAY_ID=" + mCustomer.StayId));
sdr = ExecuteReader(sql);
List<CustomerModel> listCustomer = new List<CustomerModel>();
using (sdr)
{
while (sdr.Read())
{
CustomerModel mCustomerInfo = new CustomerModel();
mCustomerInfo.CustomerId = ToInt32(sdr["CUSTOMER_ID"]);
// mCustomerInfo.CustomerStayHisInfo.StayId = ToInt32(sdr["STAY_ID"]);
mCustomerInfo.IdCardNo = ToString(sdr["ID_CARD"]);
mCustomerInfo.Name = ToString(sdr["NAME"]);
mCustomerInfo.Sex = ToString(sdr["SEX"]);
mCustomerInfo.Birthday = ToDateTime(sdr["BIRTHDAY"]);
mCustomerInfo.Address = ToString(sdr["ADDRESS"]);
mCustomerInfo.Company = ToString(sdr["COMPANY"]);
mCustomerInfo.Nation = ToString(sdr["NATION"]);
mCustomerInfo.Phone = ToString(sdr["PHONE"]);
mCustomerInfo.Picture = ToString(sdr["PICTURE"]);
mCustomerInfo.Type = ToChar(sdr["TYPE"]);
mCustomerInfo.CommonInfo = new CommonModel();
mCustomerInfo.CommonInfo.CreateDate = ToDateTime(sdr["CREATE_DATE"]);
mCustomerInfo.CommonInfo.CreateUserId = ToInt32(sdr["CREATE_USERID"]);
mCustomerInfo.CommonInfo.UpdateDate = ToDateTime(sdr["UPDATE_DATE"]);
mCustomerInfo.CommonInfo.UpdateUserId = ToInt32(sdr["UPDATE_USERID"]);
listCustomer.Add(mCustomerInfo);
}
}
return listCustomer;
}
public List<HandOverModel> GetHandOverList(HandOverModel mHandOver,ObjectControls oCtrl)
{
string sql = @"SELECT A.HAND_OVER_ID, A.C_USER_ID, A.N_USER_ID, A.START_DATE, A.END_DATE,
A.FROM_LAST_MONEY, A.HAND_OVER_MONEY, A.HAND_IN_MONEY,
A.CURRENT_DEPOSIT, A.CURRENT_PAID,A.TO_NEXT_MONEY, A.CREATE_DATE,
A.CREATE_USERID,B.USER_NAME C_USER_NAME,B.USER_NO C_USER_NO,C.USER_NAME N_USER_NAME,
C.USER_NO N_USER_NO FROM HAND_OVER_INFO A,SYS_USER_INFO B,
SYS_USER_INFO C WHERE A.C_USER_ID=B.USER_ID AND A.N_USER_ID=C.USER_ID";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByHandOverId) && oCtrl.Append(ref sql, " AND A.HAND_OVER_ID=" + mHandOver.HandOverId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCUserId) && oCtrl.Append(ref sql, " AND A.C_USER_ID=" + mHandOver.CurrentUserInfo.UserId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByNUserId) && oCtrl.Append(ref sql, " AND A.N_USER_ID=" + mHandOver.NextUserInfo.UserId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCreateDate) && oCtrl.Append(ref sql, " AND A.CREATE_DATE BETWEEN " + SQL(mHandOver.CommonInfo.StartDate) + " AND " + SQL(mHandOver.CommonInfo.EndDate)));
sdr = ExecuteReader(sql);
using (sdr)
{
List<HandOverModel> listHandOver = new List<HandOverModel>();
while (sdr.Read())
{
HandOverModel mHandOverNew = new HandOverModel();
mHandOverNew.HandOverId = ToInt32(sdr["HAND_OVER_ID"]);
mHandOverNew.CurrentUserInfo.UserId = ToInt32(sdr["C_USER_ID"]);
mHandOverNew.CurrentUserInfo.UserName = ToString(sdr["C_USER_NAME"]);
mHandOverNew.CurrentUserInfo.UserNo = ToString(sdr["C_USER_NO"]);
mHandOverNew.NextUserInfo.UserId = ToInt32(sdr["N_USER_ID"]);
mHandOverNew.NextUserInfo.UserName = ToString(sdr["N_USER_NAME"]);
mHandOverNew.NextUserInfo.UserNo = ToString(sdr["N_USER_NO"]);
mHandOverNew.CommonInfo.StartDate = ToDateTime(sdr["START_DATE"]);
mHandOverNew.CommonInfo.EndDate = ToDateTime(sdr["END_DATE"]);
mHandOverNew.CommonInfo.CreateDate = ToDateTime(sdr["CREATE_DATE"]);
mHandOverNew.CommonInfo.CreateUserId = ToInt32(sdr["CREATE_USERID"]);
mHandOverNew.HandOverMoney = ToDouble(sdr["HAND_OVER_MONEY"]);
mHandOverNew.HandInMoney = ToDouble(sdr["HAND_IN_MONEY"]);
mHandOverNew.CurrentPaidMoney = ToDouble(sdr["CURRENT_PAID"]);
mHandOverNew.CurrentDeposit = ToDouble(sdr["CURRENT_DEPOSIT"]);
mHandOverNew.FromLastMoney = ToDouble(sdr["FROM_LAST_MONEY"]);
mHandOverNew.ToNextMoney = ToDouble(sdr["TO_NEXT_MONEY"]);
listHandOver.Add(mHandOverNew);
}
return listHandOver;
}
}
public List<StayRateModel> GetStayRate(StayRateModel mStayRate, ObjectControls oCtrl)
{
string sql = "SELECT A.ID,A.STAY_RATE,A.DAYS FROM STAY_RATE A WHERE 1=1 ";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ById) && oCtrl.Append(ref sql, " AND A.ID=" + SQL(mStayRate.Id)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCreateDate) && oCtrl.Append(ref sql, " AND A.DAYS=" + SQL(mStayRate.Days)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStartDateBetween) && oCtrl.Append(ref sql, " AND A.DAYS BETWEEN " + SQL(mStayRate.CommonInfo.StartDate) + " AND " + SQL(mStayRate.CommonInfo.EndDate)));
sql += " ORDER BY A.DAYS";
sdr=ExecuteReader(sql);
using (sdr)
{
List<StayRateModel> listStayRate = new List<StayRateModel>();
while (sdr.Read())
{
StayRateModel mStayRateNew = new StayRateModel();
mStayRateNew.Id = ToInt32(sdr["ID"]);
mStayRateNew.StayRate = ToDouble(sdr["STAY_RATE"]);
mStayRateNew.Days = ToDateTime(sdr["DAYS"]);
listStayRate.Add(mStayRateNew);
}
return listStayRate;
}
}
public List<SysLookupCodeModel> GetSysLookUpCode(SysLookupCodeModel mSysLookupCode,ObjectControls oCtrl)
{
string sql = "SELECT A.CODE_ID,A.CODE_NO,A.TABLE_NAME,A.COLUMN_NAME,A.CODE_DESC FROM SYS_LOOKUP_CODE A WHERE 1=1";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCodeId) && oCtrl.Append(ref sql, " AND A.CODE_ID=" +mSysLookupCode.CodeId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCodeNo) && oCtrl.Append(ref sql, " AND A.CODE_NO=" + SQL(mSysLookupCode.CodeNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByTableName) && oCtrl.Append(ref sql, " AND A.TABLE_NAME=" + SQL(mSysLookupCode.TableName)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByColumnName) && oCtrl.Append(ref sql, " AND A.COLUMN_NAME=" + SQL(mSysLookupCode.ColumnName)));
sdr = ExecuteReader(sql);
List<SysLookupCodeModel> listLookupCode = new List<SysLookupCodeModel>();
using (sdr)
{
while (sdr.Read())
{
SysLookupCodeModel mCode = new SysLookupCodeModel();
mCode.CodeId = ToInt32(sdr["CODE_ID"]);
mCode.CodeNo = ToString(sdr["CODE_NO"]);
mCode.TableName = ToString(sdr["TABLE_NAME"]);
mCode.ColumnName = ToString(sdr["COLUMN_NAME"]);
mCode.CodeDesc = ToString(sdr["CODE_DESC"]);
listLookupCode.Add(mCode);
}
}
return listLookupCode;
}
public int UpdateGoods(BasGoodsModel mGoods, ObjectControls oCtrl)
{
return dGoodsDao.UpdateGoods(mGoods,oCtrl);
}
/// <summary>
/// 换房逻辑,换房只能从一个已入住房间换到状态为可用的房间.
/// </summary>
/// <param name="mCustomerStay">入住信息</param>
/// <param name="mNewRoomInfo">新房信息</param>
/// <param name="dNewRoomRate">新房费用</param>
/// <param name="mUserInfo">系统用户信息</param>
/// <param name="listSysParameter">系统参数</param>
public void ChangeRoom(CustomerStayModel mCustomerStay, BasRoomModel mNewRoomInfo,double dNewRoomRate, SysUserInfoModel mUserInfo, List<SysParameterModel> listSysParameter)
{
try
{
#region 将之前房间费用(光房间费用)加入到消费清单中.
ConsumeDetailModel mConsume = new ConsumeDetailModel();
BasGoodsModel mGoods = new BasGoodsModel();
mGoods.Type = 'R';
List<BasGoodsModel> listGoods = bGoods.GetGoodsInfo(mGoods, new ObjectControls(MCtrl.ByGoodsType));
if (Cmn.CheckEOF(listGoods))
{
mConsume.GoodsId = listGoods[0].GoodsId;
}
else
{
throw new Exception("未定义类型的R的商品,该类型为房间费.");
}
mConsume.StayId = mCustomerStay.StayId;
mConsume.UnitPrice = mCustomerStay.RoomRate;
RoomStayType eRst = mCustomerStay.RoomStayType == 'D' ? RoomStayType.Day : RoomStayType.Hour;
DateTime dtNow = Cmn.DateBaseDate;
mConsume.Number = GetCustomerStayDays(mCustomerStay.CommonInfo.StartDate, dtNow,dtNow, eRst, listSysParameter);
mConsume.Total = GetTotalRates(mCustomerStay, null, listSysParameter, dtNow, 0.0);
mConsume.CommonInfo = new CommonModel();
mConsume.CommonInfo.CreateDate = mCustomerStay.CommonInfo.StartDate;
mConsume.CommonInfo.CreateUserId = mUserInfo.UserId;
mConsume.CommonInfo.UpdateUserId = mUserInfo.UserId;
bConsume.InsertConsumeDetail(mConsume);
#endregion
#region 是否团队房间
if (mCustomerStay.MainRoomId == mCustomerStay.RoomId)
{
if (Cmn.Confirm(string.Format("{0}为主房间,变更后{1}将成为主房间,是否继续?", mCustomerStay.RoomInfo.RoomNo, mNewRoomInfo.RoomNo)))
{
#region 将团队房间的MainRoomId变为新ID
List<BasRoomModel> listTeamRoom = GetTeamRoomListByRoomId(mCustomerStay.RoomInfo, 'I');
if (Cmn.CheckEOF(listTeamRoom))
{
ObjectControls oCtrl = new ObjectControls();
oCtrl.Add(MCtrl.SetMainRoomId);
foreach (BasRoomModel mRoom in listTeamRoom)
{
CustomerStayModel mCs = new CustomerStayModel();
mCs.RoomId = mRoom.RoomId;
mCs.Status = 'I';
ObjectControls oc = new ObjectControls();
oc.Add(MCtrl.ByStayStatus);
oc.Add(MCtrl.ByRoomId);
mCs = bCustomerStay.GetCustomerStayInfo(mCs, oc);
if (mCs != null)
{
mCs.MainRoomId = mNewRoomInfo.RoomId;
bCustomerStay.UpdateCustomerStay(mCs, oCtrl);
}
}
}
#endregion
}
}
#endregion
#region 将原来房间置为清理,将新房间设置为入住
ObjectControls oCtrlMain = new ObjectControls();
oCtrlMain.Add(MCtrl.SetRoomId);
oCtrlMain.Add(MCtrl.SetStartDate);
oCtrlMain.Add(MCtrl.SetHours);
oCtrlMain.Add(MCtrl.SetRoomRate);
mCustomerStay.RoomId = mNewRoomInfo.RoomId;
mCustomerStay.CommonInfo.StartDate = Cmn.DateBaseDate;
double dDays = GetCustomerStayDays(mCustomerStay.CommonInfo.StartDate, mCustomerStay.CommonInfo.EndDate,mCustomerStay.CommonInfo.StartDate, eRst, listSysParameter);
mCustomerStay.Hours = eRst == RoomStayType.Day ? Convert.ToInt32(dDays * 24) : Convert.ToInt32(dDays);
mCustomerStay.RoomRate = dNewRoomRate;
bCustomerStay.UpdateCustomerStay(mCustomerStay, oCtrlMain);
mNewRoomInfo.Status = mCustomerStay.RoomInfo.Status;
UpdateRoomStatusByRoomId(mNewRoomInfo, mUserInfo);
mCustomerStay.RoomInfo.Status = 'C';
UpdateRoomStatusByRoomId(mCustomerStay.RoomInfo, mUserInfo);
#endregion
}
catch (Exception err)
{
throw err;
}
}
public int UpdateConsumeDetail(ConsumeDetailModel mConsume, ObjectControls oCtrl)
{
return dConsumeDetail.UpdateConsumeDetail(mConsume, oCtrl);
}
/// <summary>
/// 根据一个RoomId获取团队所有房间
/// </summary>
/// <param name="mRoom"></param>
/// <returns></returns>
public List<BasRoomModel> GetTeamRoomListByRoomId(BasRoomModel mRoom,char cStayStatus)
{
CustomerStayModel mCustomerStay = new CustomerStayModel();
mCustomerStay.RoomId = mRoom.RoomId;
mCustomerStay.Status = cStayStatus;
ObjectControls oCtrl = new ObjectControls();
oCtrl.Add(MCtrl.ByStayStatus);
oCtrl.Add(MCtrl.ByRoomId);
CustomerStayModel mC = bCustomerStay.GetCustomerStayInfo(mCustomerStay, oCtrl);
if (mC.MainRoomId > 0)
{
oCtrl.Reset();
oCtrl.Add(MCtrl.ByStayStatus);
oCtrl.Add(MCtrl.ByMainRoomId);
return bRoom.GetTeamRoomList(mC, oCtrl);
}
else
{
return new List<BasRoomModel>();
}
}
public List<CustomerStayHisModel> GetStayHis(CustomerStayHisModel mStayHis, ObjectControls oCtrl)
{
string sql = "SELECT * FROM CUSTOMER_STAY_HIS A WHERE 1=1";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCustomerId) && oCtrl.Append(ref sql, " AND A.CUSTOMER_ID=" + mStayHis.CustomerId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayId) && oCtrl.Append(ref sql, " AND A.STAY_ID=" + mStayHis.StayId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayType) && oCtrl.Append(ref sql, " AND A.STAY_TYPE=" + mStayHis.StayType));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByHisStatus) && oCtrl.Append(ref sql, " AND A.HIS_STATUS=" + mStayHis.HisStatus));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByHisId) && oCtrl.Append(ref sql, " AND A.HIS_ID=" + mStayHis.HisId));
sdr = ExecuteReader(sql);
using (sdr)
{
List<CustomerStayHisModel> listStayHis = new List<CustomerStayHisModel>();
while (sdr.Read())
{
CustomerStayHisModel mStayHisNew = new CustomerStayHisModel();
mStayHisNew.HisId = ToInt32(sdr["HIS_ID"]);
mStayHisNew.StayId =ToInt32(sdr["STAY_ID"]);
mStayHisNew.CustomerId = ToInt32(sdr["CUSTOMER_ID"]);
mStayHisNew.HisStatus = ToChar(sdr["HIS_STATUS"]);
mStayHisNew.StayType = ToChar(sdr["STAY_TYPE"]);
mStayHisNew.CommonInfo = new CommonModel();
mStayHisNew.CommonInfo.CreateDate = ToDateTime(sdr["CREATE_DATE"]);
mStayHisNew.CommonInfo.CreateUserId = ToInt32(sdr["CREATE_USERID"]);
mStayHisNew.CommonInfo.UpdateDate = ToDateTime(sdr["UPDATE_DATE"]);
mStayHisNew.CommonInfo.UpdateUserId = ToInt32(sdr["UPDATE_USERID"]);
listStayHis.Add(mStayHisNew);
}
return listStayHis;
}
}
/// <summary>
/// 获取打印的设置信息
/// </summary>
/// <param name="mPrint"></param>
/// <param name="oCtrl"></param>
/// <returns></returns>
public PrintModel GetPrintModel(PrintModel mPrint, ObjectControls oCtrl)
{
return dPrint.GetPrintConfig(mPrint, oCtrl);
//return mPrint;
}
public CustomerStayModel GetCustomerStayInfo(CustomerStayModel mCustomerStay, ObjectControls oCtrl)
{
string sql = @"SELECT A.STAY_ID,A.STAY_NO, A.ROOM_ID, A.MAIN_ROOM_ID, A.ROOM_STAY_TYPE,
A.START_DATE, A.END_DATE,A.HOURS, A.DEPOSIT, A.ROOM_RATES AS CURRENT_RATE,A.PAID_MONEY, A.PAY_TYPE,A.TOTAL_MONEY, A.STATUS,
A.NOTICE, A.CREATE_DATE, A.CREATE_USERID,F.USER_NAME AS CREATE_USER_NAME,G.USER_NAME AS UPDATE_USER_NAME, A.UPDATE_DATE,
A.UPDATE_USERID, B.CUSTOMER_ID, B.ID_CARD, B.NAME, B.SEX, B.NATION,
B.BIRTHDAY, B.ADDRESS, B.COMPANY, B.PHONE, B.PICTURE, B.TYPE, E.STAY_TYPE,E.HIS_STATUS,E.HIS_ID,
E.START_TIME,E.END_TIME,
C.ROOM_NO, C.ROOM_TYPE, C.ROOM_RATES AS DEFAULT_RATE, C.ROOM_NOTICE,
C.ROOM_PHONE, C.STATUS AS ROOM_STATUS, C.FLOOR_ID,D.FLOOR_NAME
FROM CUSTOMER_STAY_INFO AS A INNER JOIN
CUSTOMER_STAY_HIS E ON A.STAY_ID =E.STAY_ID";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayType) && oCtrl.Append(ref sql, " AND E.STAY_TYPE=" + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.StayType)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByHisStatus) && oCtrl.Append(ref sql, " AND E.HIS_STATUS=" + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.HisStatus)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByHisStatusNotEqual) && oCtrl.Append(ref sql, " AND E.HIS_STATUS !=" + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.HisStatus)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByHisStartTime) && oCtrl.Append(ref sql, " AND E.START_TIME BETWEEN " + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.CommonInfo.StartDate) + " AND " + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.CommonInfo.EndDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByHisEndTime) && oCtrl.Append(ref sql, " AND E.END_TIME BETWEEN " + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.CommonInfo.StartDate) + " AND " + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.CommonInfo.EndDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByHisStartOrEndTime) && oCtrl.Append(ref sql, " AND (E.START_TIME BETWEEN " + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.CommonInfo.StartDate)
+ " AND " + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.CommonInfo.EndDate)
+ " OR E.END_TIME BETWEEN " + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.CommonInfo.StartDate)
+ " AND " + SQL(mCustomerStay.CustomerList[0].CustomerStayHisInfo.CommonInfo.EndDate) + ")"));
sql += @" INNER JOIN
CUSTOMER_INFO AS B ON E.CUSTOMER_ID = B.CUSTOMER_ID ";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByName) && oCtrl.Append(ref sql, " AND B.NAME=" + SQL(mCustomerStay.CustomerList[0].Name)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByPhone) && oCtrl.Append(ref sql, " AND B.PHONE=" + SQL(mCustomerStay.CustomerList[0].Phone)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCustomerType) && oCtrl.Append(ref sql, " AND B.TYPE=" + SQL(mCustomerStay.CustomerList[0].Type)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCustomerId) && oCtrl.Append(ref sql, " AND B.CUSTOMER_ID=" + mCustomerStay.CustomerList[0].CustomerId));
sql+=@" INNER JOIN
BAS_ROOM_INFO AS C ON A.ROOM_ID = C.ROOM_ID INNER JOIN
SYS_USER_INFO AS F ON A.CREATE_USERID = F.USER_ID INNER JOIN
SYS_USER_INFO AS G ON A.UPDATE_USERID = G.USER_ID INNER JOIN
BAS_FLOOR_INFO AS D ON C.FLOOR_ID = D.FLOOR_ID";
sql += " WHERE 1=1";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomId) && oCtrl.Append(ref sql, " AND A.ROOM_ID=" + mCustomerStay.RoomId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByFloorId) && oCtrl.Append(ref sql, " AND D.FLOOR_ID=" + mCustomerStay.RoomInfo.FloorInfo.FloorId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomNo) && oCtrl.Append(ref sql, " AND C.ROOM_NO=" + SQL(mCustomerStay.RoomInfo.RoomNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStartDate) && oCtrl.Append(ref sql, " AND A.START_DATE=" + SQL(mCustomerStay.CommonInfo.StartDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByEndDate)&& oCtrl.Append(ref sql, " AND A.END_DATE=" + SQL(mCustomerStay.CommonInfo.EndDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayStatus) && oCtrl.Append(ref sql, " AND A.STATUS=" + SQL(mCustomerStay.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomStayType) && oCtrl.Append(ref sql, " AND A.ROOM_STAY_TYPE=" + mCustomerStay.RoomStayType));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayNo) && oCtrl.Append(ref sql, " AND A.STAY_NO=" + mCustomerStay.StayNo));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayId) && oCtrl.Append(ref sql, " AND A.STAY_ID=" + mCustomerStay.StayId));
sdr = ExecuteReader(sql);
using (sdr)
{
if (!sdr.HasRows)
{
return null;
}
CustomerStayModel mCustomerStayInfo = new CustomerStayModel();
mCustomerStayInfo.CustomerList = new List<CustomerModel>();
while(sdr.Read())
{
mCustomerStayInfo.CommonInfo = new CommonModel();
mCustomerStayInfo.StayNo = ToString(sdr["STAY_NO"]);
mCustomerStayInfo.StayId = ToInt32(sdr["STAY_ID"]);
mCustomerStayInfo.RoomId = ToInt32(sdr["ROOM_ID"]);
mCustomerStayInfo.MainRoomId = ToInt32(sdr["MAIN_ROOM_ID"]);
mCustomerStayInfo.CommonInfo.StartDate = ToDateTime(sdr["START_DATE"]);
mCustomerStayInfo.CommonInfo.EndDate = ToDateTime(sdr["END_DATE"]);
mCustomerStayInfo.Deposit = ToDouble(sdr["DEPOSIT"]);
mCustomerStayInfo.RoomRate = ToInt32(sdr["CURRENT_RATE"]);
mCustomerStayInfo.Total = ToDouble(sdr["TOTAL_MONEY"]);
mCustomerStayInfo.PayType = ToChar(sdr["PAY_TYPE"]);
mCustomerStayInfo.Status = ToChar(sdr["STATUS"]);
mCustomerStayInfo.Notice = ToString(sdr["NOTICE"]);
mCustomerStayInfo.Hours = ToInt32(sdr["HOURS"]);
mCustomerStayInfo.RoomStayType = ToChar(sdr["ROOM_STAY_TYPE"]);
mCustomerStayInfo.PaidMoney = ToDouble(sdr["PAID_MONEY"]);
mCustomerStayInfo.CommonInfo.CreateDate = ToDateTime(sdr["CREATE_DATE"]);
mCustomerStayInfo.CommonInfo.CreateUserId = ToInt32(sdr["CREATE_USERID"]);
mCustomerStayInfo.CommonInfo.CreateUserName = ToString(sdr["CREATE_USER_NAME"]);
mCustomerStayInfo.CommonInfo.UpdateDate = ToDateTime(sdr["UPDATE_DATE"]);
mCustomerStayInfo.CommonInfo.UpdateUserId = ToInt32(sdr["UPDATE_USERID"]);
mCustomerStayInfo.CommonInfo.UpateUserName = ToString(sdr["UPDATE_USER_NAME"]);
CustomerModel mCustomerInfo = new CustomerModel();
mCustomerInfo.CustomerId = ToInt32(sdr["CUSTOMER_ID"]);
mCustomerInfo.IdCardNo = ToString(sdr["ID_CARD"]);
mCustomerInfo.Name = ToString(sdr["NAME"]);
mCustomerInfo.Nation = ToString(sdr["NATION"]);
mCustomerInfo.Phone = ToString(sdr["PHONE"]);
mCustomerInfo.Picture = ToString(sdr["PICTURE"]);
mCustomerInfo.Type = ToChar(sdr["TYPE"]);
mCustomerInfo.Sex = ToString(sdr["SEX"]);
mCustomerInfo.Birthday = ToDateTime(sdr["BIRTHDAY"]);
mCustomerInfo.Address = ToString(sdr["ADDRESS"]);
mCustomerInfo.Company = ToString(sdr["COMPANY"]);
mCustomerInfo.CustomerStayHisInfo = new CustomerStayHisModel();
mCustomerInfo.CustomerStayHisInfo.HisId = ToInt32(sdr["HIS_ID"]);
mCustomerInfo.CustomerStayHisInfo.StayId = ToInt32(sdr["STAY_ID"]);
mCustomerInfo.CustomerStayHisInfo.CustomerId = ToInt32(sdr["CUSTOMER_ID"]);
mCustomerInfo.CustomerStayHisInfo.StayType = ToChar(sdr["STAY_TYPE"]);
mCustomerInfo.CustomerStayHisInfo.HisStatus = ToChar(sdr["HIS_STATUS"]);
mCustomerInfo.CustomerStayHisInfo.CommonInfo.StartDate = ToDateTime(sdr["START_TIME"]);
mCustomerInfo.CustomerStayHisInfo.CommonInfo.EndDate = ToDateTime(sdr["END_TIME"]);
mCustomerInfo.CommonInfo = new CommonModel();
mCustomerStayInfo.CustomerList.Add(mCustomerInfo);
mCustomerStayInfo.RoomInfo = new BasRoomModel();
mCustomerStayInfo.RoomInfo.RoomId = ToInt32(sdr["ROOM_ID"]);
mCustomerStayInfo.RoomInfo.RoomNo = ToString(sdr["ROOM_NO"]);
mCustomerStayInfo.RoomInfo.RoomType = ToChar(sdr["ROOM_TYPE"]);
mCustomerStayInfo.RoomInfo.RoomRate = ToDouble(sdr["DEFAULT_RATE"]);
mCustomerStayInfo.RoomInfo.RoomPhone = ToString(sdr["ROOM_PHONE"]);
mCustomerStayInfo.RoomInfo.Status = ToChar(sdr["ROOM_STATUS"]);
mCustomerStayInfo.RoomInfo.RoomNotice = ToString(sdr["ROOM_NOTICE"]);
mCustomerStayInfo.RoomInfo.FloorInfo = new BasFloorModel();
mCustomerStayInfo.RoomInfo.FloorInfo.FloorId = ToInt32(sdr["FLOOR_ID"]);
mCustomerStayInfo.RoomInfo.FloorInfo.FloorName = ToString(sdr["FLOOR_NAME"]);
}
return mCustomerStayInfo;
}
}
/// <summary>
/// 更新用户信息
/// </summary>
/// <param name="mCustomerNew">新的信息</param>
/// <param name="mCustomerOld">以前旧信息</param>
public void UpdateExsitCustomerInfo(CustomerModel mCustomerNew,CustomerModel mCustomerOld)
{
ObjectControls oCtrl = new ObjectControls();
if (!string.IsNullOrEmpty(mCustomerNew.Name))
{
mCustomerOld.Name = mCustomerNew.Name;
oCtrl.Add(MCtrl.SetName);
}
if (!string.IsNullOrEmpty(mCustomerNew.IdCardNo))
{
mCustomerOld.IdCardNo = mCustomerNew.IdCardNo;
oCtrl.Add(MCtrl.SetIdCard);
}
if (!string.IsNullOrEmpty(mCustomerNew.Address))
{
mCustomerOld.Address = mCustomerNew.Address;
oCtrl.Add(MCtrl.SetAddress);
}
if (mCustomerNew.Birthday != DateTime.MinValue)
{
mCustomerOld.Birthday = mCustomerNew.Birthday;
oCtrl.Add(MCtrl.SetBirthday);
}
if (!string.IsNullOrEmpty(mCustomerNew.Company))
{
mCustomerOld.Company = mCustomerNew.Company;
oCtrl.Add(MCtrl.SetCompanyName);
}
if (!string.IsNullOrEmpty(mCustomerNew.Nation))
{
mCustomerOld.Nation = mCustomerNew.Nation;
oCtrl.Add(MCtrl.SetNation);
}
if (!string.IsNullOrEmpty(mCustomerNew.Phone))
{
mCustomerOld.Phone = mCustomerNew.Phone;
oCtrl.Add(MCtrl.SetPhone);
}
if (!string.IsNullOrEmpty(mCustomerNew.Picture))
{
mCustomerOld.Picture = mCustomerNew.Picture;
oCtrl.Add(MCtrl.SetPicture);
}
if (!string.IsNullOrEmpty(mCustomerNew.Sex))
{
mCustomerOld.Sex = mCustomerNew.Sex;
oCtrl.Add(MCtrl.SetSex);
}
if (!mCustomerNew.Type.Equals(mCustomerOld.Type))
{
mCustomerOld.Type = mCustomerNew.Type;
oCtrl.Add(MCtrl.SetCustomerType);
}
mCustomerNew.CustomerId = mCustomerOld.CustomerId;
mCustomerOld.CommonInfo.UpdateUserId = mCustomerNew.CommonInfo.UpdateUserId;
bCustomer.UpdateCustomer(mCustomerOld, oCtrl);
}
/// <summary>
/// 更新团体房间的MainId,用于将团体房变为散客房
/// </summary>
/// <param name="mCustomerStay"></param>
/// <returns></returns>
public bool UpdateCustomerStayMainRoomId(CustomerStayModel mCustomerStay)
{
try
{
ObjectControls oCtrl = new ObjectControls();
oCtrl.Add(MCtrl.ByStayStatus);
oCtrl.Add(MCtrl.ByMainRoomId);
//获取该房所在的团体所有房间
List<BasRoomModel> listTeamRoom = bRoom.GetTeamRoomList(mCustomerStay, oCtrl);
//是否有其他房间可以作为主房间
var query = listTeamRoom.Where(c => c.RoomId != mCustomerStay.RoomId);
if (query.Count() > 0)
{
if (Cmn.Confirm(string.Format("房间{0}是主房间,变为散客房之后,团队的主房间将变为{1},继续?", mCustomerStay.RoomInfo.RoomNo, query.First().RoomNo)))
{
List<CustomerStayModel> listTeamCustomer = new List<CustomerStayModel>();
listTeamCustomer = GetStayInRoomInfo(listTeamRoom, 'I',"M", false);
int nMainRoomId = query.First().RoomId;
foreach (CustomerStayModel mcs in listTeamCustomer)
{
if (mcs.RoomId == mCustomerStay.RoomId)
{
mcs.MainRoomId = -1; ;
}
else
{
mcs.MainRoomId = nMainRoomId;
}
bCustomerStay.UpdateCustomerStay(mcs, new ObjectControls(MCtrl.SetMainRoomId));
}
return true;
}
}
else
{
throw new Exception("无主房间可供接替,无法变更为散客房!");
}
return false;
}
catch (Exception err)
{
throw err;
}
}
/// <summary>
/// 根据编号表名列名,获取对应的描述
/// </summary>
/// <param name="sTableName"></param>
/// <param name="sColumn"></param>
/// <param name="sCodeNo"></param>
/// <returns></returns>
public string ToLookupCodeDesc(string sTableName, string sColumn, string sCodeNo)
{
SysLookupCodeModel mCode = new SysLookupCodeModel();
mCode.TableName = sTableName;
mCode.ColumnName = sColumn;
mCode.CodeNo = sCodeNo;
ObjectControls oCtrl = new ObjectControls();
oCtrl.Add(MCtrl.ByCodeNo);
oCtrl.Add(MCtrl.ByColumnName);
oCtrl.Add(MCtrl.ByTableName);
List<SysLookupCodeModel> listLookUpCode = bLookUpCode.GetSysLookUpCode(mCode, oCtrl);
if (Cmn.CheckEOF(listLookUpCode))
{
return listLookUpCode[0].CodeDesc;
}
return string.Empty;
}
public int UpdateCustomer(CustomerModel mCustomer, ObjectControls oCtrl)
{
string sql = "UPDATE CUSTOMER_INFO SET CREATE_DATE = CREATE_DATE";
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetName) && oCtrl.Append(ref sql, ",NAME=" + SQL(mCustomer.Name)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetNation) && oCtrl.Append(ref sql, ",NATION=" + SQL(mCustomer.Nation)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetIdCard) && oCtrl.Append(ref sql, ",ID_CARD=" + SQL(mCustomer.IdCardNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetSex) && oCtrl.Append(ref sql, ",SEX=" + SQL(mCustomer.Sex)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetBirthday) && oCtrl.Append(ref sql, ",BIRTHDAY=" + SQL(mCustomer.Birthday)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetCompanyName) && oCtrl.Append( ref sql, ",COMPANY=" + SQL(mCustomer.Company)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetAddress) && oCtrl.Append(ref sql, ",ADDRESS=" + SQL(mCustomer.Address)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetCustomerType) && oCtrl.Append( ref sql, ",ADDRESS=" + SQL(mCustomer.Type)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetPicture) && oCtrl.Append(ref sql, ",PICTURE=" + SQL(mCustomer.Picture)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetPhone) && oCtrl.Append(ref sql, ",PHONE=" + SQL(mCustomer.Phone)));
sql += ",UPDATE_DATE=" + SQL(GetDBTime);
sql += ",UPDATE_USERID=" + SQL(mCustomer.CommonInfo.UpdateUserId);
sql += " WHERE CUSTOMER_ID=" + mCustomer.CustomerId;
return ExcuteNonQuery(sql);
}
public int UpdateCustomerStay(CustomerStayModel mCustomerStay, ObjectControls oCtrl)
{
string sql = "UPDATE CUSTOMER_STAY_INFO SET CREATE_DATE=CREATE_DATE";
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetStayNo) && oCtrl.Append(ref sql, ",STAY_NO=" + SQL(mCustomerStay.StayNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetRoomId) && oCtrl.Append(ref sql, ",ROOM_ID=" + SQL(mCustomerStay.RoomId)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetMainRoomId) && oCtrl.Append(ref sql, ",MAIN_ROOM_ID=" + SQL(mCustomerStay.MainRoomId)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetHours) && oCtrl.Append(ref sql, ",HOURS=" + SQL(mCustomerStay.Hours)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetRoomStayType) && oCtrl.Append(ref sql, ",ROOM_STAY_TYPE=" + SQL(mCustomerStay.RoomStayType)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetStartDate) && oCtrl.Append(ref sql, ",START_DATE=" + SQL(mCustomerStay.CommonInfo.StartDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetEndDate) && oCtrl.Append(ref sql, ",END_DATE=" + SQL(mCustomerStay.CommonInfo.EndDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetDeposit) && oCtrl.Append(ref sql, ",DEPOSIT=" + SQL(mCustomerStay.Deposit)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetRoomRate) && oCtrl.Append(ref sql, ",ROOM_RATES=" + SQL(mCustomerStay.RoomRate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetTotal) && oCtrl.Append(ref sql, ",TOTAL_MONEY=" + SQL(mCustomerStay.Total)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetPayType) && oCtrl.Append(ref sql, ",PAY_TYPE=" + SQL(mCustomerStay.PayType)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetCustomerStayStatus) && oCtrl.Append(ref sql, ",STATUS=" + SQL(mCustomerStay.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetPaidMoney) && oCtrl.Append(ref sql, ",PAID_MONEY=" + SQL(mCustomerStay.PaidMoney)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetNotice) && oCtrl.Append(ref sql, ",NOTICE=" + SQL(mCustomerStay.Notice)));
sql += ",UPDATE_DATE=" + SQL(GetDBTime);
sql += ",UPDATE_USERID=" + SQL(mCustomerStay.CommonInfo.UpdateUserId);
sql += " WHERE STAY_ID=" + mCustomerStay.StayId;
return ExcuteNonQuery(sql);
}
/// <summary>
/// 修改房间信息
/// </summary>
/// <param name="mRoomInfo"></param>
/// <param name="oCtrl"></param>
/// <returns></returns>
public int UpdateRoomInfo(BasRoomModel mRoomInfo, ObjectControls oCtrl)
{
try
{
string sql = "UPDATE BAS_ROOM_INFO SET CREATE_DATE=CREATE_DATE";
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetRoomNo) && oCtrl.Append(ref sql, ",ROOM_NO=" + SQL(mRoomInfo.RoomNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetRoomStatus) && oCtrl.Append(ref sql, ",STATUS=" + SQL(mRoomInfo.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetRoomRate) && oCtrl.Append(ref sql, ",ROOM_RATES=" + SQL(mRoomInfo.RoomRate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetPhone) && oCtrl.Append(ref sql, ",ROOM_PHONE=" + SQL(mRoomInfo.RoomPhone)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetRoomType) && oCtrl.Append(ref sql, ",ROOM_TYPE=" + SQL(mRoomInfo.RoomType)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetFloorId) && oCtrl.Append(ref sql, ",FLOOR_ID=" + SQL(mRoomInfo.FloorInfo.FloorId)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetNotice) && oCtrl.Append(ref sql, ",ROOM_NOTICE=" + SQL(mRoomInfo.RoomNotice)));
sql += ",UPDATE_DATE=" + SQL(GetDBTime);
sql += ",UPDATE_USERID=" + SQL(mRoomInfo.CommonInfo.UpdateUserId);
sql += " WHERE ROOM_ID=" + mRoomInfo.RoomId;
return ExcuteNonQuery(sql);
}
catch (Exception err)
{
throw err;
}
}
public int UpdateStayHis(CustomerStayHisModel mHis, ObjectControls oCtrl)
{
string sql = "UPDATE CUSTOMER_STAY_HIS SET CREATE_DATE=CREATE_DATE";
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetCustomerId) && oCtrl.Append(ref sql, ",CUSTOMER_ID=" + SQL(mHis.CustomerId)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetHisType) && oCtrl.Append(ref sql, ",STAY_TYPE=" + SQL(mHis.StayType)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetHisStatus) && oCtrl.Append(ref sql, ",HIS_STATUS=" + SQL(mHis.HisStatus)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetEndDate) && oCtrl.Append(ref sql, ",END_TIME=" + SQL(mHis.CommonInfo.EndDate)));
sql += ",UPDATE_DATE=" + SQL(GetDBTime);
sql += ",UPDATE_USERID=" + SQL(mHis.CommonInfo.UpdateUserId);
sql += " WHERE HIS_ID=" + mHis.HisId;
//STAY_ID=" + mHis.StayId;
//sql += " AND CUSTOMER_ID=" + mHis.CustomerId;
return ExcuteNonQuery(sql);
}
/// <summary>
/// 获取消费明细
/// </summary>
/// <param name="mConsume"></param>
/// <param name="oCtrl"></param>
/// <returns></returns>
public List<ConsumeDetailModel> GetConsumeList(ConsumeDetailModel mConsume, ObjectControls oCtrl)
{
return dConsumeDetail.GetConsumeList(mConsume, oCtrl);
}
/// <summary>
/// 获取商品信息
/// </summary>
/// <param name="mGoods"></param>
/// <param name="oCtrl"></param>
/// <returns></returns>
public List<BasGoodsModel> GetGoodsInfo(BasGoodsModel mGoods, ObjectControls oCtrl)
{
return dGoodsDao.GetGoodsInfo(mGoods, oCtrl);
}
public HandOverModel GetHandOverStayInfo(CustomerStayModel mCustomerStay, ObjectControls oCtrl)
{
string sql = "SELECT ";
if (mCustomerStay.Status == 'I')
{
sql += "ISNULL(SUM(A.DEPOSIT),0) MONEY";
}
else
{
sql += "ISNULL(SUM(A.PAID_MONEY),0) MONEY";
}
sql += " FROM CUSTOMER_STAY_INFO A WHERE 1=1";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayStatus) && oCtrl.Append(ref sql, " AND A.STATUS=" + SQL(mCustomerStay.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCreateDate) && oCtrl.Append(ref sql, " AND A.CREATE_DATE >" + SQL(mCustomerStay.CommonInfo.CreateDate)));
sdr = ExecuteReader(sql);
using (sdr)
{
HandOverModel mHandOver = new HandOverModel();
while (sdr.Read())
{
if (mCustomerStay.Status == 'I')
{
mHandOver.CurrentDeposit = ToDouble(sdr["MONEY"]);
}
else
{
mHandOver.CurrentPaidMoney = ToDouble(sdr["MONEY"]);
}
}
return mHandOver;
}
}
/// <summary>
/// 开房以及修改房间信息逻辑.支持房间多人以及团体开房.支持房间人数变更以及宾客信息变更.
/// </summary>
/// <param name="listCustomerStayInfo"></param>
/// <param name="listCustomerInfo"></param>
/// <param name="eRmType"></param>
/// <returns></returns>
public bool OpendOrUpdateRoom(List<CustomerStayModel> listCustomerStayInfo, List<CustomerModel> listCustomerInfo, RoomLogicType eRmType)
{
try
{
if (listCustomerStayInfo[0].RoomStayType == 'D' && listCustomerStayInfo[0].Hours == 0)
{
throw new Exception("非钟点房,预住天数不能为零.");
}
if (eRmType == RoomLogicType.OpenRoom)
{
#region 开单
//如团体开房,则每个房间新增一个StayId
foreach (CustomerStayModel mCustomerStay in listCustomerStayInfo)
{
int nStayId = bCustomerStay.InsertCustomerStay(mCustomerStay);
BasRoomModel mRoom = new BasRoomModel();
mRoom.RoomId = mCustomerStay.RoomId;
mRoom.Status = mCustomerStay.RoomInfo.Status;
mRoom.CommonInfo = mCustomerStay.CommonInfo;
UpdateRoomStatusByRoomId(mRoom, new SysUserInfoModel(mCustomerStay.CommonInfo.UpdateUserId));
//每个房间可能多个入住人员
foreach (CustomerModel mCustomer in listCustomerInfo)
{
//检查宾客信息是否存在,存在则更新较新的信息.姓名和证件号不更新
List<CustomerModel> listCustomerOld = GetExistCustomerInfo(mCustomer);
int nCustomerId = 0;
if (Cmn.CheckEOF(listCustomerOld))
{
UpdateExsitCustomerInfo(mCustomer, listCustomerOld[0]);
nCustomerId = listCustomerOld[0].CustomerId;
}
else
{
nCustomerId = bCustomer.InsertCustomer(mCustomer);
}
CustomerStayHisModel mHis = new CustomerStayHisModel();
mHis.StayId = nStayId;
mHis.CustomerId = nCustomerId;
mHis.StayType = mCustomer.CustomerStayHisInfo.StayType;
mHis.HisStatus = 'E';
mHis.CommonInfo = new CommonModel();
mHis.CommonInfo.StartDate = mCustomerStay.CommonInfo.StartDate;
mHis.CommonInfo.EndDate = mCustomerStay.CommonInfo.EndDate;
mHis.CommonInfo.CreateDate = mCustomerStay.CommonInfo.CreateDate;
mHis.CommonInfo.CreateUserId = mCustomerStay.CommonInfo.CreateUserId;
bCustomerStay.InsertCustomerStayHis(mHis);
}
}
#endregion
}
else
{
#region 修改房间信息
foreach (CustomerStayModel mCustomerStay in listCustomerStayInfo)
{
ObjectControls oCtrl = new ObjectControls();
oCtrl.Add(MCtrl.SetDeposit);
oCtrl.Add(MCtrl.SetMainRoomId);
oCtrl.Add(MCtrl.SetNotice);
oCtrl.Add(MCtrl.SetPayType);
oCtrl.Add(MCtrl.SetRoomId);
oCtrl.Add(MCtrl.SetRoomRate);
oCtrl.Add(MCtrl.SetCustomerStayStatus);
oCtrl.Add(MCtrl.SetRoomStayType);
oCtrl.Add(MCtrl.SetEndDate);
oCtrl.Add(MCtrl.SetHours);
//修改入住信息
bCustomerStay.UpdateCustomerStay(mCustomerStay, oCtrl);
foreach (CustomerModel mCustomer in listCustomerInfo)
{
//检查宾客信息是否存在,存在则更新较新的信息.姓名和证件号不更新
List<CustomerModel> listCustomerOld = GetExistCustomerInfo(mCustomer);
int nCustomerId = 0;
if (Cmn.CheckEOF(listCustomerOld))
{
mCustomer.CommonInfo.UpdateUserId = mCustomerStay.CommonInfo.UpdateUserId;
UpdateExsitCustomerInfo(mCustomer, listCustomerOld[0]);
nCustomerId = listCustomerOld[0].CustomerId;
}
else
{
nCustomerId = bCustomer.InsertCustomer(mCustomer);
}
CustomerStayHisModel mHis = new CustomerStayHisModel();
mHis.StayId = mCustomerStay.StayId;
mHis.CustomerId = nCustomerId;
oCtrl.Reset();
oCtrl.Add(MCtrl.ByStayId);
oCtrl.Add(MCtrl.ByCustomerId);
List<CustomerStayHisModel> listStayHis = bCustomerStay.GetStayHis(mHis, oCtrl);
if (Cmn.CheckEOF(listStayHis))
{
mHis.HisId = listStayHis[0].HisId;
mHis.HisStatus = mCustomer.CustomerStayHisInfo.HisStatus;
mHis.StayType = mCustomer.CustomerStayHisInfo.StayType;
mHis.CommonInfo = new CommonModel();
mHis.CommonInfo.UpdateUserId = mCustomerStay.CommonInfo.UpdateUserId;
oCtrl.Reset();
oCtrl.Add(MCtrl.SetHisStatus);
oCtrl.Add(MCtrl.SetHisType);
//如果用户选择将房间内某一宾客离店,则该宾客的离店时间为现在.开始时间不变
if (mHis.HisStatus == 'O')
{
mHis.CommonInfo.EndDate = Cmn.DateBaseDate;
oCtrl.Add(MCtrl.SetEndDate);
}
bCustomerStay.UpdateStayHis(mHis, oCtrl);
}
else
{
mHis.StayType = mCustomer.CustomerStayHisInfo.StayType;
mHis.HisStatus = mCustomer.CustomerStayHisInfo.HisStatus;
mHis.CommonInfo = new CommonModel();
mHis.CommonInfo.CreateUserId = mCustomerStay.CommonInfo.UpdateUserId;
mHis.CommonInfo.UpdateUserId = mCustomerStay.CommonInfo.UpdateUserId;
//如宾客为新增加到该房间内的.则他的入住时间从现在开始,到该房间的预住结束时间为止.
mHis.CommonInfo.StartDate = Cmn.DateBaseDate;
mHis.CommonInfo.EndDate = mCustomerStay.CommonInfo.EndDate;
bCustomerStay.InsertCustomerStayHis(mHis);
}
}
}
#endregion
}
return true;
}
catch (Exception err)
{
throw err;
}
}