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); }