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));
}
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 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);
}
}
private void pbtnSearch_Click(object sender, EventArgs e)
{
try
{
CustomerStayModel mCustomerStay = new CustomerStayModel();
mCustomerStay.CommonInfo.StartDate = GetDateTimePickValue(dtpStart, cboHourS);
mCustomerStay.CommonInfo.EndDate = GetDateTimePickValue(dtpEnd, cboHourE);
mCustomerStay.Status = 'O';
ObjectControls oCtrl = new ObjectControls();
mCustomerStay.CustomerList = new List <CustomerModel>();
CustomerModel mc = new CustomerModel();
mc.CustomerStayHisInfo = new CustomerStayHisModel();
//mc.CustomerStayHisInfo.StayType = 'M';
mc.CustomerStayHisInfo.HisStatus = 'O';//取离店宾客入住历史记录.
mCustomerStay.CustomerList.Add(mc);
oCtrl.Add(MCtrl.ByStayStatus);
oCtrl.Add(MCtrl.ByHisStatus);
oCtrl.Add(MCtrl.ByUpdateDate);
listCustomerStay = bCustomerStay.GetCustomerStayList(mCustomerStay, oCtrl);
BindDgv(listCustomerStay);
}
catch (Exception err)
{
cmn.Show(err.Message);
}
}
/// <summary>
/// 修改用户信息
/// </summary>
/// <param name="mUser"></param>
/// <param name="oCtrl"></param>
/// <returns></returns>
public int UpdateUserInfo(SysUserInfoModel mUser, ObjectControls oCtrl)
{
SYS_USER_INFO sui = new SYS_USER_INFO();
var query = Dc.SYS_USER_INFO.Where(c => c.USER_ID == mUser.UserId);
if (query.Count() > 0)
{
sui = query.First();
if (oCtrl.Exsit(MCtrl.SetPsw))
{
sui.PASSWORD = mUser.NewPsw;
}
if (oCtrl.Exsit(MCtrl.SetUserName))
{
sui.USER_NAME = mUser.UserName;
}
if (oCtrl.Exsit(MCtrl.SetUserNo))
{
sui.USER_NO = mUser.UserNo;
}
if (oCtrl.Exsit(MCtrl.SetRoleId))
{
sui.ROLE_ID = mUser.RoleInfo.RoleId;
}
if (oCtrl.Exsit(MCtrl.SetSkinId))
{
sui.SKIN_ID = mUser.SkinId;
}
sui.UPDATE_DATE = Cmn.DateBaseDate;
sui.UPDATE_USERID = mUser.UserId;
Dc.SubmitChanges();
return(sui.USER_ID);
}
return(-1);
}
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);
}
/**
* Updates flags to verify if the object can be considered landed or not and manages when can a new object be spawned
*/
private void ObjectLanded()
{
// If a reference to an object exists, if this object if considered landed and if the game is not considered lost
if (currentObject && currentObject.Landed() && !gameOver.gameOverFlag)
{
// Frees the reference holder of the 'current object'
currentObject = null;
// Calls for a new object to be spawned in 2 seconds
spawnArea.SpawnNewObject(2f);
// Adds a point to the current score
score++;
// If the current score exceeds the highest score registered for this session
if (score > highestScore)
{
// Saves current score in highest
highestScore = score;
}
// When the player reaches a score dividable by 5
if (score % 5 == 0)
{
// Creates a new paper plane in the scene
eventSpawnPoint.SpawnPaperPlane();
}
}
}
/// <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>
/// 退房逻辑
/// </summary>
/// <param name="listCustomerStayInfo"></param>
/// <returns></returns>
public bool CheckOutRoom(List <CustomerStayModel> listCustomerStayInfo)
{
try
{
ObjectControls oCtrl = new ObjectControls();
foreach (CustomerStayModel mCustomerStay in listCustomerStayInfo)
{
#region 修改房间状态
mCustomerStay.RoomInfo.Status = 'C';
UpdateRoomStatusByRoomId(mCustomerStay.RoomInfo, new SysUserInfoModel(mCustomerStay.CommonInfo.UpdateUserId));
#endregion
#region 更新入住信息
oCtrl.Reset();
oCtrl.Add(MCtrl.SetCustomerStayStatus);
oCtrl.Add(MCtrl.SetDeposit);
oCtrl.Add(MCtrl.SetEndDate);
oCtrl.Add(MCtrl.SetHours);
oCtrl.Add(MCtrl.SetNotice);
oCtrl.Add(MCtrl.SetPayType);
oCtrl.Add(MCtrl.SetPaidMoney);
oCtrl.Add(MCtrl.SetStayNo);
oCtrl.Add(MCtrl.SetTotal);
bCustomerStay.UpdateCustomerStay(mCustomerStay, oCtrl);
//退房时将stayhis的入住状态改为离店模式，以区分每个宾客的状态。有时候宾馆房间某一宾客会提前离开，所以需要设置每个宾客的状态。
//如提前离开需修改宾客登记，右击宾客详细列表，在菜单中进行离店操作。
//如要遇到宾客信息登记错误，需要删除，则启用DELETE键删除即可。
if (Cmn.CheckEOF(mCustomerStay.CustomerList))
{
foreach (CustomerModel mcustomer in mCustomerStay.CustomerList)
{
CustomerStayHisModel mcshis = mcustomer.CustomerStayHisInfo;
//整个房间退房时,将HIS状态不为O的人改为O:OFF 离店.离店时间为现在.如果该宾客以及事先离店,则不更新.
if (mcshis.HisStatus != 'O')
{
oCtrl.Reset();
mcshis.HisStatus = 'O';
mcshis.CommonInfo.EndDate = Cmn.DateBaseDate;
mcshis.CommonInfo.UpdateUserId = mCustomerStay.CommonInfo.UpdateUserId;//更新人员为操作员ID
oCtrl.Add(MCtrl.SetEndDate);
oCtrl.Add(MCtrl.SetHisStatus);
bCustomerStay.UpdateStayHis(mcshis, oCtrl);
}
}
}
#endregion
}
return(true);
}
catch (Exception err)
{
throw err;
}
}
public List <JFModel> GetPhoneList(JFModel mJf, DateTime dtmNow)
{
ObjectControls oCtrl = new ObjectControls();
oCtrl.Add(MCtrl.ByPhone);
oCtrl.Add(MCtrl.ByStartDate);
mJf.PhoneNoGroup = Cmn.RemoveLastDou(mJf.PhoneNoGroup);
return(bPhone.GetPhoneDetail(mJf, oCtrl, dtmNow));
}
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));
}
/// <summary>
/// 仅获取宾客入住信息
/// </summary>
/// <param name="mRoom"></param>
/// <param name="cStayStatus"></param>
/// <returns></returns>
public CustomerStayModel GetCustomerStayInfoByRoom(BasRoomModel mRoom, char cStayStatus)
{
CustomerStayModel mStayInfo = new CustomerStayModel();
ObjectControls oCtrl = new ObjectControls();
mStayInfo.RoomId = mRoom.RoomId;
mStayInfo.Status = cStayStatus;
oCtrl.Add(MCtrl.ByRoomId);
oCtrl.Add(MCtrl.ByStayStatus);
return(bCustomerStay.GetCustomerStayInfo(mStayInfo, oCtrl));
}
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 <SysLookupCodeModel> GetCodeDesc(string sTable, string sColumn)
{
SysLookupCodeModel mSysLookupCode = new SysLookupCodeModel();
mSysLookupCode.TableName = sTable;
mSysLookupCode.ColumnName = sColumn;
ObjectControls oLookCode = new ObjectControls();
oLookCode.Add(MCtrl.ByColumnName);
oLookCode.Add(MCtrl.ByTableName);
return(bLookUpCode.GetSysLookUpCode(mSysLookupCode, oLookCode));
}
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 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);
}
/// <summary>
/// 获取在宾客信息清单
/// </summary>
/// <param name="listRoomInfo"></param>
/// <param name="cStayStatus">该入住单是否在住</param>
/// <param name="sStayType">宾客类型,主客Or从客</param>
/// <param name="HisStayStatus">宾客是否退房</param>
/// <param name="IsCountConsume">是否统计消费</param>
/// <returns></returns>
public List <CustomerStayModel> GetStayInRoomInfo(List <BasRoomModel> listRoomInfo, char cStayStatus, string sStayType, bool IsCountConsume)
{
List <CustomerStayModel> listCustomerStayInfo = new List <CustomerStayModel>();
CustomerStayModel mStayInfo = new CustomerStayModel();
ObjectControls oCtrl = new ObjectControls();
foreach (BasRoomModel mRoom in listRoomInfo)
{
mStayInfo.RoomInfo.RoomIdGroup += Cmn.MakeGroup(mRoom.RoomId.ToString());
}
mStayInfo.RoomInfo.RoomIdGroup = Cmn.RemoveLastDou(mStayInfo.RoomInfo.RoomIdGroup);
mStayInfo.Status = cStayStatus;
mStayInfo.CustomerList = new List <CustomerModel>();
CustomerModel mc = new CustomerModel();
mc.CustomerStayHisInfo = new CustomerStayHisModel();
if (!string.IsNullOrEmpty(sStayType))
{
mc.CustomerStayHisInfo.StayType = Cmn.ToChar(sStayType);
oCtrl.Add(MCtrl.ByStayType);
}
mc.CustomerStayHisInfo.HisStatus = cStayStatus == 'I' ? 'E' : 'O';
mStayInfo.CustomerList.Add(mc);
if (Cmn.CheckEOF(listRoomInfo))
{
oCtrl.Add(MCtrl.ByRoomIdGroup);
}
oCtrl.Add(MCtrl.ByStayStatus);
oCtrl.Add(MCtrl.ByHisStatus);
listCustomerStayInfo = bCustomerStay.GetCustomerStayList(mStayInfo, oCtrl);
if (Cmn.CheckEOF(listCustomerStayInfo) && IsCountConsume)
{
foreach (CustomerStayModel mCustomerStay in listCustomerStayInfo)
{
List <ConsumeDetailModel> listGetConsumeDetail = new List <ConsumeDetailModel>();
ConsumeDetailModel mConsume = new ConsumeDetailModel();
mConsume.StayId = mCustomerStay.StayId;
oCtrl.Reset();
oCtrl.Add(MCtrl.ByStayId);
listGetConsumeDetail = bConsume.GetConsumeList(mConsume, oCtrl);
if (Cmn.CheckEOF(listGetConsumeDetail))
{
mCustomerStay.ConSumeDetailList = listGetConsumeDetail;
}
}
}
return(listCustomerStayInfo);
}
/// <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";
}
/// <summary>
/// 获取房间有效的预定
/// </summary>
/// <param name="mRoom"></param>
/// <returns></returns>
public List <OrderInfoModel> GetRoomOrder(BasRoomModel mRoom)
{
OrderInfoModel mOrder = new OrderInfoModel();
mOrder.RoomInfo = mRoom;
mOrder.KeepDate = Cmn.DateBaseDate;
mOrder.Status = 'E';
ObjectControls oCtrl = new ObjectControls();
oCtrl.Reset();
oCtrl.Add(MCtrl.ByRoomId);
oCtrl.Add(MCtrl.ByKeepDateEnable);
oCtrl.Add(MCtrl.ByOrderStatus);
return(bOrder.GetOrderInfo(mOrder, oCtrl));
}
/// <summary>
/// By用户证件以及姓名获取用户信息
/// </summary>
/// <param name="mCustomer"></param>
/// <returns></returns>
public List <CustomerModel> GetExistCustomerInfo(CustomerModel mCustomer)
{
ObjectControls oCtrl = new ObjectControls();
if (mCustomer.CustomerId > 0)
{
oCtrl.Add(MCtrl.ByCustomerId);
}
else
{
oCtrl.Add(MCtrl.ByIdCard);
oCtrl.Add(MCtrl.ByName);
}
return(bCustomer.GetCustomerInfo(mCustomer, oCtrl));
}
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 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 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));
}
/// <summary>
/// 获取交接班信息
/// </summary>
/// <returns></returns>
public HandOverModel GetHandOverInfo()
{
try
{
HandOverInfo bHandOver = new HandOverInfo();
int nHandOverId = bHandOver.GetHandOverLatestId();
ObjectControls oCtrl = new ObjectControls();
List <HandOverModel> listHandOver = new List <HandOverModel>();
HandOverModel mHandOver = new HandOverModel();
CustomerStayModel mCustomerStay = new CustomerStayModel();
if (nHandOverId > 0)
{
mHandOver.HandOverId = nHandOverId;
listHandOver = bHandOver.GetHandOverList(mHandOver, new ObjectControls(MCtrl.ByHandOverId));
if (Cmn.CheckEOF(listHandOver))
{
mCustomerStay.CommonInfo.CreateDate = listHandOver[0].CommonInfo.EndDate;
mHandOver.CommonInfo.StartDate = listHandOver[0].CommonInfo.EndDate;
mHandOver.FromLastMoney = listHandOver[0].ToNextMoney;
}
}
else
{
mCustomerStay.CommonInfo.CreateDate = DateTime.Parse("2005-01-01");
mHandOver.CommonInfo.StartDate = mCustomerStay.CommonInfo.CreateDate;
mHandOver.FromLastMoney = 0.0;
}
oCtrl.Add(MCtrl.ByCreateDate);
mCustomerStay.Status = 'I';
oCtrl.Add(MCtrl.ByStayStatus);
mHandOver.CurrentDeposit = bCustomerStay.GetHandOverStayInfo(mCustomerStay, oCtrl).CurrentDeposit;
mCustomerStay.Status = 'O';
mHandOver.CurrentPaidMoney = bCustomerStay.GetHandOverStayInfo(mCustomerStay, oCtrl).CurrentPaidMoney;
mHandOver.CommonInfo.EndDate = Cmn.DateBaseDate;
mHandOver.HandInMoney = 0;
mHandOver.HandOverMoney = mHandOver.FromLastMoney + mHandOver.CurrentDeposit + mHandOver.CurrentPaidMoney;
mHandOver.ToNextMoney = mHandOver.HandOverMoney;
return(mHandOver);
}
catch (Exception err)
{
throw err;
}
}
/// <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;
}
}
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 <CallModel> GetCallList(CallModel mCall, ObjectControls oCtrl)
{
string sql = @"SELECT A.LIST_ID, A.ROOM_ID, A.PHONE, A.STAY_ID, A.FREE_ID, A.CAT_ID,
A.START_TIME, A.END_TIME, A.CREATE_DATE, A.CREATE_USERID, B.CAT_DESC,
B.CAT_RATE, C.ROOM_NO
FROM CALL_LIST AS A INNER JOIN
PHONE_CAT AS B ON A.CAT_ID = B.CAT_ID INNER JOIN
BAS_ROOM_INFO AS C ON A.ROOM_ID = C.ROOM_ID";
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByPhone) && oCtrl.Append(ref sql, " AND A.PHONE=" + mCall.RoomInfo.RoomPhone));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByStayId) && oCtrl.Append(ref sql, " AND A.STAY_ID=" + mCall.StayId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByFreeId) && oCtrl.Append(ref sql, " AND A.FREE_ID=" + mCall.FreeId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByCatId) && oCtrl.Append(ref sql, " AND A.CAT_ID=" + mCall.PhoneCatInfo.CatId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomId) && oCtrl.Append(ref sql, " AND A.ROOM_ID=" + mCall.RoomInfo.RoomId));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByRoomNo) && oCtrl.Append(ref sql, " AND C.ROOM_NO=" + mCall.RoomInfo.RoomNo));
oCtrl.Helper(oCtrl.Exsit(MCtrl.ByListId) && oCtrl.Append(ref sql, " AND A.LIST_ID=" + mCall.ListId));
sdr = ExecuteReader(sql);
using (sdr)
{
List <CallModel> listCall = new List <CallModel>();
while (sdr.Read())
{
CallModel mCallNew = new CallModel();
mCallNew.ListId = ToInt32(sdr["LIST_ID"]);
mCallNew.RoomInfo.RoomId = ToInt32(sdr["ROOM_ID"]);
mCallNew.RoomInfo.RoomNo = ToString(sdr["ROOM_NO"]);
mCallNew.RoomInfo.RoomPhone = ToString(sdr["PHONE"]);
mCallNew.StayId = ToInt32(sdr["STAY_ID"]);
mCallNew.FreeId = ToInt32(sdr["FREE_ID"]);
mCallNew.PhoneCatInfo.CatId = ToInt32(sdr["CAT_ID"]);
mCallNew.PhoneCatInfo.CatDesc = ToString(sdr["CAT_DESC"]);
mCallNew.PhoneCatInfo.CatRate = ToDouble(sdr["CAT_RATE"]);
mCallNew.CommonInfo.StartDate = ToDateTime(sdr["START_TIME"]);
mCallNew.CommonInfo.EndDate = ToDateTime(sdr["END_TIME"]);
mCallNew.CommonInfo.CreateDate = ToDateTime(sdr["CREATE_DATE"]);
mCallNew.CommonInfo.CreateUserId = ToInt32(sdr["CREATE_USERID"]);
listCall.Add(mCallNew);
}
return(listCall);
}
}
/// <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);
}
