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 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 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);
}
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 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<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<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<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 List<BasRoomModel> GetRoomInfo(BasRoomModel mRoom, ObjectControls oCtrl)
{
string sql = @"SELECT A.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
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=" + mRoom.RoomId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomNo) && oCtrl.Append(ref sql, " AND A.ROOM_NO=" + SQL(mRoom.RoomNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomStatus) && oCtrl.Append(ref sql, " AND A.STATUS=" + SQL(mRoom.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomStatusGroup) && oCtrl.Append(ref sql, " AND A.STATUS IN (" + mRoom.StatusGroup+")"));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomType) && oCtrl.Append(ref sql, " AND A.ROOM_TYPE=" + SQL(mRoom.RoomType)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByFloorId) && oCtrl.Append(ref sql, " AND B.FLOOR_ID=" + mRoom.FloorInfo.FloorId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByFloorName) && oCtrl.Append(ref sql, " AND B.FLOOR_NAME=" + SQL(mRoom.FloorInfo.FloorName)));
sql += " ORDER BY ROOM_NO";
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.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;
}
/// <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 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 UpdateOrderInfo(OrderInfoModel mOrder, ObjectControls oCtrl)
{
string sql = " UPDATE ORDER_INFO SET CREATE_DATE=CREATE_DATE";
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetName) && oCtrl.Append(ref sql, ",NAME=" + SQL(mOrder.Name)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetIdCard) && oCtrl.Append(ref sql, ",ID_CARD=" + SQL(mOrder.IdCardNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetPhone) && oCtrl.Append(ref sql, ",PHONE=" + SQL(mOrder.Phone)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetStartDate) && oCtrl.Append(ref sql, ",START_DATE=" + SQL(mOrder.CommonInfo.StartDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetEndDate) && oCtrl.Append(ref sql, ",END_DATE=" + SQL(mOrder.CommonInfo.EndDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetKeepDate) && oCtrl.Append(ref sql, ",KEEP_DATE=" + SQL(mOrder.KeepDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetRoomId) && oCtrl.Append(ref sql, ",ROOM_ID=" + SQL(mOrder.RoomInfo.RoomId)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetRoomRate) && oCtrl.Append(ref sql, ",ROOM_RATE=" + SQL(mOrder.OrderRoomRate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetNotice) && oCtrl.Append(ref sql, ",NOTICE=" + SQL(mOrder.Notice)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetOrderStatus) && oCtrl.Append(ref sql, ",STATUS=" + SQL(mOrder.Status)));
sql += ",UPDATE_DATE=" + SQL(GetDBTime);
sql += ",UPDATE_USERID=" + SQL(mOrder.CommonInfo.UpdateUserId);
sql += " WHERE ORDER_ID=" + mOrder.OrderId;
return ExcuteNonQuery(sql);
}
public List<OrderInfoModel> GetOrderInfo(OrderInfoModel mOrder, ObjectControls oCtrl)
{
string sql = @"SELECT D.ORDER_ID,D.NAME,D.PHONE,D.ID_CARD,D.ROOM_RATE AS ORDER_RATE,D.STATUS AS ORDER_STATUS,D.START_DATE,D.END_DATE,D.KEEP_DATE,D.NOTICE,
D.CREATE_DATE,D.CREATE_USERID,D.UPDATE_DATE,D.UPDATE_USERID,
A.ROOM_ID, A.ROOM_NO, A.ROOM_TYPE,C.CODE_DESC AS ROOM_TYPE_DESC, A.ROOM_RATES, A.STATUS AS ROOM_STATUS,
A.ROOM_PHONE,B.FLOOR_ID, B.FLOOR_NAME, B.STATUS AS FLOOR_STATUS
FROM ORDER_INFO D INNER JOIN
BAS_ROOM_INFO AS A ON D.ROOM_ID=A.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=" + mOrder.RoomInfo.RoomId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomNo) && oCtrl.Append(ref sql, " AND A.ROOM_NO=" + SQL(mOrder.RoomInfo.RoomNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomStatus) && oCtrl.Append(ref sql, " AND A.STATUS=" + SQL(mOrder.RoomInfo.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomStatusGroup) && oCtrl.Append(ref sql, " AND A.STATUS IN (" + mOrder.RoomInfo.StatusGroup + ")"));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomType) && oCtrl.Append(ref sql, " AND A.ROOM_TYPE=" + SQL(mOrder.RoomInfo.RoomType)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByFloorId) && oCtrl.Append(ref sql, " AND B.FLOOR_ID=" + mOrder.RoomInfo.FloorInfo.FloorId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByFloorName) && oCtrl.Append(ref sql, " AND B.FLOOR_NAME=" + SQL(mOrder.RoomInfo.FloorInfo.FloorName)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStartDateBetween) && oCtrl.Append(ref sql, " AND D.START_DATE BETWEEN " + SQL(mOrder.CommonInfo.StartDate) + " AND " + SQL(mOrder.CommonInfo.EndDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByEndDateBetween) && oCtrl.Append(ref sql, " AND D.END_DATE BETWEEN " + SQL(mOrder.CommonInfo.StartDate) + " AND " + SQL(mOrder.CommonInfo.EndDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByKeepDateEnable) && oCtrl.Append(ref sql, " AND D.KEEP_DATE >= " + SQL(mOrder.KeepDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByKeepDateDisable) && oCtrl.Append(ref sql, " AND D.KEEP_DATE < " + SQL(mOrder.KeepDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByOrderStatus) && oCtrl.Append(ref sql, " AND D.STATUS=" + SQL(mOrder.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByIdCard) && oCtrl.Append(ref sql, " AND D.ID_CARD=" + SQL(mOrder.IdCardNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByName) && oCtrl.Append(ref sql, " AND D.NAME=" + SQL(mOrder.Name)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByPhone) && oCtrl.Append(ref sql, " AND D.PHONE=" + SQL(mOrder.Phone)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByNPR) && oCtrl.Append(ref sql, " AND (D.NAME LIKE " + SQLL(mOrder.Name) + " OR D.PHONE LIKE " + SQLL(mOrder.Name) + " OR A.ROOM_NO LIKE " + SQLL(mOrder.Name)+")"));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByOrderId) && oCtrl.Append(ref sql, " AND D.ORDER_ID=" + mOrder.OrderId));
sql += " ORDER BY D.CREATE_DATE DESC";
sdr = ExecuteReader(sql);
List<OrderInfoModel> listOrderInfo = new List<OrderInfoModel>();
using (sdr)
{
while (sdr.Read())
{
OrderInfoModel mOrderInfo = new OrderInfoModel();
mOrderInfo.OrderId = ToInt32(sdr["ORDER_ID"]);
mOrderInfo.Name = ToString(sdr["NAME"]);
mOrderInfo.Phone = ToString(sdr["PHONE"]);
mOrderInfo.IdCardNo = ToString(sdr["ID_CARD"]);
mOrderInfo.OrderRoomRate = ToDouble(sdr["ORDER_RATE"]);
mOrderInfo.CommonInfo = new CommonModel();
mOrderInfo.CommonInfo.StartDate = ToDateTime(sdr["START_DATE"]);
mOrderInfo.CommonInfo.EndDate = ToDateTime(sdr["END_DATE"]);
mOrderInfo.KeepDate = ToDateTime(sdr["KEEP_DATE"]);
mOrderInfo.Notice = ToString(sdr["NOTICE"]);
mOrderInfo.Status = ToChar(sdr["ORDER_STATUS"]);
mOrderInfo.CommonInfo.CreateDate = ToDateTime(sdr["CREATE_DATE"]);
mOrderInfo.CommonInfo.CreateUserId = ToInt32(sdr["CREATE_USERID"]);
mOrderInfo.CommonInfo.UpdateDate = ToDateTime(sdr["UPDATE_DATE"]);
mOrderInfo.CommonInfo.UpdateUserId = ToInt32(sdr["UPDATE_USERID"]);
mOrderInfo.RoomInfo.RoomId = ToInt32(sdr["ROOM_ID"]);
mOrderInfo.RoomInfo.RoomNo = ToString(sdr["ROOM_NO"]);
mOrderInfo.RoomInfo.RoomPhone = ToString(sdr["ROOM_PHONE"]);
mOrderInfo.RoomInfo.RoomRate = ToDouble(sdr["ROOM_RATES"]);
mOrderInfo.RoomInfo.RoomType = ToChar(sdr["ROOM_TYPE"]);
mOrderInfo.RoomInfo.RoomTypeDesc = ToString(sdr["ROOM_TYPE_DESC"]);
mOrderInfo.RoomInfo.Status = ToChar(sdr["ROOM_STATUS"]);
mOrderInfo.RoomInfo.FloorInfo = new BasFloorModel();
mOrderInfo.RoomInfo.FloorInfo.FloorId = ToInt32(sdr["FLOOR_ID"]);
mOrderInfo.RoomInfo.FloorInfo.FloorName = ToString(sdr["FLOOR_NAME"]);
mOrderInfo.RoomInfo.FloorInfo.Status = ToChar(sdr["FLOOR_STATUS"]);
listOrderInfo.Add(mOrderInfo);
}
}
return listOrderInfo;
}
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);
}
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;
}
}
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;
}
}
public int UpdateConsumeDetail(ConsumeDetailModel mConsume, ObjectControls oCtrl)
{
string sql = " UPDATE CONSUME_DETAIL SET CREATE_DATE=CREATE_DATE";
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetStayId) && oCtrl.Append(ref sql, ",STAY_ID=" + SQL(mConsume.StayId)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetGoodsId) && oCtrl.Append(ref sql, ",GOODS_ID=" + SQL(mConsume.GoodsId)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetNumber) && oCtrl.Append(ref sql, ",NUMBER=" + SQL(mConsume.Number)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetUnitPrice) && oCtrl.Append(ref sql, ",UNIT_PRICE=" + SQL(mConsume.UnitPrice)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetTotal) && oCtrl.Append(ref sql, ",TOTAL=" + SQL(mConsume.Total)));
sql += " ,UPDATE_DATE=" + SQL(GetDBTime) + ",UPDATE_USERID=" + SQL(mConsume.CommonInfo.UpdateUserId);
return ExcuteNonQuery(sql);
}
public int UpdateHandOver(HandOverModel mHandOver, ObjectControls oCtrl)
{
string sql = "UPDATE HAND_OVER_INFO SET CREATE_DATE=CREATE_DATE";
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetCUserId) && oCtrl.Append(ref sql, " ,C_USER_ID=" + mHandOver.NextUserInfo.UserId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetNUserId) && oCtrl.Append(ref sql, " ,N_USER_ID=" + mHandOver.NextUserInfo.UserId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetHandInMoney) && oCtrl.Append(ref sql, " ,HAND_IN_MONEY=" + SQL(mHandOver.HandInMoney)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetHandOverMoney) && oCtrl.Append(ref sql, " ,HAND_OVER_MONEY=" + SQL(mHandOver.HandOverMoney)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetPaidMoney) && oCtrl.Append(ref sql, " ,CURRENT_PAID=" + SQL(mHandOver.CurrentPaidMoney)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetDeposit) && oCtrl.Append(ref sql, " ,CURRENT_DEPOSIT=" + SQL(mHandOver.CurrentDeposit)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetStartDate) && oCtrl.Append(ref sql, " ,START_DATE=" + SQL(mHandOver.CommonInfo.StartDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetEndDate) && oCtrl.Append(ref sql, " ,END_DATE=" + SQL(mHandOver.CommonInfo.EndDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetCurrentIncome) && oCtrl.Append(ref sql, " ,FROM_LAST_MONEY=" + SQL(mHandOver.FromLastMoney)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.SetToNextMoney) && oCtrl.Append(ref sql, " ,TO_NEXT_MONEY=" +SQL(mHandOver.ToNextMoney)));
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);
}
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;
}
}
public DataSet GetOrderInfoDs(OrderInfoModel mOrder, ObjectControls oCtrl)
{
string sql = @"SELECT D.ORDER_ID,D.NAME,D.PHONE,D.ID_CARD,D.ROOM_RATE AS ORDER_RATE,D.STATUS AS ORDER_STATUS,D.START_DATE,D.END_DATE,D.KEEP_DATE,D.NOTICE,
D.CREATE_DATE,D.CREATE_USERID,D.UPDATE_DATE,D.UPDATE_USERID,
A.ROOM_ID, A.ROOM_NO, A.ROOM_TYPE,C.CODE_DESC AS ROOM_TYPE_DESC, A.ROOM_RATES, A.STATUS AS ROOM_STATUS,
A.ROOM_PHONE,B.FLOOR_ID, B.FLOOR_NAME, B.STATUS AS FLOOR_STATUS
FROM ORDER_INFO D INNER JOIN
BAS_ROOM_INFO AS A ON D.ROOM_ID=A.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=" + mOrder.RoomInfo.RoomId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomNo) && oCtrl.Append(ref sql, " AND A.ROOM_NO=" + SQL(mOrder.RoomInfo.RoomNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomStatus) && oCtrl.Append(ref sql, " AND A.STATUS=" + SQL(mOrder.RoomInfo.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomStatusGroup) && oCtrl.Append(ref sql, " AND A.STATUS IN (" + mOrder.RoomInfo.StatusGroup + ")"));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomType) && oCtrl.Append(ref sql, " AND A.ROOM_TYPE=" + SQL(mOrder.RoomInfo.RoomType)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByFloorId) && oCtrl.Append(ref sql, " AND B.FLOOR_ID=" + mOrder.RoomInfo.FloorInfo.FloorId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByFloorName) && oCtrl.Append(ref sql, " AND B.FLOOR_NAME=" + SQL(mOrder.RoomInfo.FloorInfo.FloorName)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStartDateBetween) && oCtrl.Append(ref sql, " AND D.START_DATE BETWEEN " + SQL(mOrder.CommonInfo.StartDate) + " AND " + SQL(mOrder.CommonInfo.EndDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByEndDateBetween) && oCtrl.Append(ref sql, " AND D.END_DATE BETWEEN " + SQL(mOrder.CommonInfo.StartDate) + " AND " + SQL(mOrder.CommonInfo.EndDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByKeepDateEnable) && oCtrl.Append(ref sql, " AND D.KEEP_DATE >= " + SQL(mOrder.KeepDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByKeepDateDisable) && oCtrl.Append(ref sql, " AND D.KEEP_DATE < " + SQL(mOrder.KeepDate)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByOrderStatus) && oCtrl.Append(ref sql, " AND D.STATUS=" + SQL(mOrder.Status)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByIdCard) && oCtrl.Append(ref sql, " AND D.ID_CARD=" + SQL(mOrder.IdCardNo)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByName) && oCtrl.Append(ref sql, " AND D.NAME=" + SQL(mOrder.Name)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByPhone) && oCtrl.Append(ref sql, " AND D.PHONE=" + SQL(mOrder.Phone)));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByNPR) && oCtrl.Append(ref sql, " AND (D.NAME LIKE " + SQLL(mOrder.Name) + " OR D.PHONE LIKE " + SQLL(mOrder.Name) + " OR A.ROOM_NO LIKE " + SQLL(mOrder.Name) + ")"));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByOrderId) && oCtrl.Append(ref sql, " AND D.ORDER_ID=" + mOrder.OrderId));
sql += " ORDER BY D.CREATE_DATE DESC";
return ExecuteDataSet(sql);
}