/// <summary>
/// 编辑
/// </summary>
/// <param name="tb_Bed"></param>
/// <returns></returns>
public int Edit(TB_Bed tb_Bed)
{
DataTable dt = null;
//查询宿舍区域名称是否存在
dt = _mTB_BedDAL.GetTable(tb_Bed);
if (dt != null && dt.Rows.Count > 0)
{
_errMessage = "名称重复！";
}
else
{
//更新操作
if (tb_Bed.ID > 0)
{
_mTB_BedDAL.Edit(tb_Bed);
}
//添加操作
else
{
tb_Bed.ID = _mTB_BedDAL.Create(tb_Bed);
}
}
return(tb_Bed.ID);
}
/// <summary>
/// GridView绑定
/// </summary>
/// <param name="intCurrentIndex"></param>
private void Bind(int intCurrentIndex)
{
TB_Bed mTB_Bed = new TB_Bed();
BedBLL mBedBLL = new BedBLL();
Pager pager = new Pager();
pager.CurrentPageIndex = intCurrentIndex;
pager.srcOrder = " ID desc";
mTB_Bed.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
mTB_Bed.DormAreaID = Convert.ToInt32(this.ddlDormArea.SelectedValue);
mTB_Bed.BuildingID = Convert.ToInt32(Request.Form[this.ddlBuildingName.UniqueID.ToString()]);
mTB_Bed.UnitID = Convert.ToInt32(Request.Form[this.ddlUnit.UniqueID.ToString()]);
mTB_Bed.FloorID = Convert.ToInt32(Request.Form[this.ddlFloor.UniqueID.ToString()]);
mTB_Bed.RoomID = Convert.ToInt32(Request.Form[this.ddlRoom.UniqueID.ToString()]);
mTB_Bed.Status = (int)TypeManager.BedStatus.Free;
//mTB_Bed.RoomType = Convert.ToInt32(this.ddlRoomType.SelectedValue);
//mTB_Bed.RoomSexType = this.ddlRoomSexType.SelectedValue;
mTB_Bed.Name = this.txtBed.Text.Trim();
GridView1.DataSource = mBedBLL.GetTableByEnableStatus(mTB_Bed, ref pager);
GridView1.DataBind();
this.Pager1.ItemCount = pager.TotalRecord;
this.Pager1.PageCount = pager.TotalPage;
this.Pager1.CurrentIndex = pager.CurrentPageIndex;
this.Pager1.PageSize = pager.PageSize;
}
/// <summary>
/// 初始化页面数据
/// </summary>
private void InitPage()
{
string strID = Request.QueryString["id"];
int intID;
TB_Bed mTB_Bed = null;
BedBLL mBedBLL = new BedBLL();
if (Int32.TryParse(strID, out intID))
{
mTB_Bed = mBedBLL.Get(intID);
this.txtBed.Text = mTB_Bed.Name;
this.txtKeyCount.Text = mTB_Bed.KeyCount.ToString();
this.ddlDormArea.SelectedValue = mTB_Bed.DormAreaID.ToString();
this.BindDDLBuilding(mTB_Bed.DormAreaID);
this.ddlBuilding.SelectedValue = mTB_Bed.BuildingID.ToString();
this.BindDDLUnit(mTB_Bed.BuildingID);
this.ddlUnit.SelectedValue = mTB_Bed.UnitID.ToString();
//this.ddlRoomType.SelectedValue = mTB_Room.RoomType.ToString();
//this.ddlRoomSexType.SelectedValue = mTB_Room.RoomSexType.ToString();
this.BindDDLFloor(mTB_Bed.UnitID);
this.ddlFloor.SelectedValue = mTB_Bed.FloorID.ToString();
//this.BindDDLRoom(mTB_Bed.FloorID);
this.BindDDLRoomByBuildingID(mTB_Bed.BuildingID);
this.ddlRoom.SelectedValue = mTB_Bed.RoomID.ToString();
}
}
public string EditBed(TB_Bed tb_Bed)
{
BedBLL mBedBLL = new BedBLL();
mBedBLL.ErrMessage = string.Empty;
mBedBLL.Edit(tb_Bed);
return(mBedBLL.ErrMessage);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public int Edit(TB_Bed info)
{
try
{
Database db = DBO.CreateDatabase();
return(this.Edit(info, (DbTransaction)null, db));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 事务更新
/// </summary>
/// <param name="info"></param>
/// <param name="tran"></param>
/// <param name="db"></param>
/// <returns></returns>
public int Edit(TB_Bed info, DbTransaction tran, Database db)
{
DbCommand dbCommandWrapper = null;
string strUpdateSql = @"UPDATE TB_Bed SET DormAreaID=@DormAreaID
,BuildingID=@BuildingID
,UnitID=@UnitID
,FloorID=@FloorID
,RoomID=@RoomID
,Name=@Name
,UpdateBy=@UpdateBy
,UpdateDate=@UpdateDate
,KeyCount=@KeyCount
WHERE ID=@ID";
try
{
dbCommandWrapper = db.GetSqlStringCommand(strUpdateSql);
#region Add parameters
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, info.ID);
db.AddInParameter(dbCommandWrapper, "@DormAreaID", DbType.Int32, info.DormAreaID);
db.AddInParameter(dbCommandWrapper, "@BuildingID", DbType.Int32, info.BuildingID);
db.AddInParameter(dbCommandWrapper, "@UnitID", DbType.Int32, info.UnitID);
db.AddInParameter(dbCommandWrapper, "@FloorID", DbType.Int32, info.FloorID);
db.AddInParameter(dbCommandWrapper, "@RoomID", DbType.Int32, info.RoomID);
db.AddInParameter(dbCommandWrapper, "@Name", DbType.String, info.Name);
//db.AddInParameter(dbCommandWrapper, "@RoomSexType", DbType.String, info.RoomSexType);
//db.AddInParameter(dbCommandWrapper, "@RoomType", DbType.Int32, info.RoomType);
db.AddInParameter(dbCommandWrapper, "@UpdateBy", DbType.String, SetNullValue(string.IsNullOrEmpty(info.UpdateBy), info.UpdateBy));
db.AddInParameter(dbCommandWrapper, "@UpdateDate", DbType.DateTime, DateTime.Now);
db.AddInParameter(dbCommandWrapper, "@KeyCount", DbType.Int32, info.KeyCount);
#endregion
if (tran == null)
{
return(db.ExecuteNonQuery(dbCommandWrapper));
}
else
{
return(db.ExecuteNonQuery(dbCommandWrapper, tran));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 事务添加
/// </summary>
/// <param name="info"></param>
/// <param name="tran"></param>
/// <param name="db"></param>
/// <returns></returns>
public int Create(TB_Bed info, DbTransaction tran, Database db)
{
DbCommand dbCommandWrapper = null;
int intId;
string strInsertSql = @"INSERT INTO TB_Bed (SiteID,DormAreaID,BuildingID,UnitID,FloorID,RoomID,Name,[Status],Creator,KeyCount)
VALUES(@SiteID,@DormAreaID,@BuildingID,@UnitID,@FloorID,@RoomID,@Name,@Status,@Creator,@KeyCount)";
string strSelectIdSql = ";SELECT SCOPE_IDENTITY()";
try
{
dbCommandWrapper = db.GetSqlStringCommand(strInsertSql + strSelectIdSql);
#region Add parameters
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, info.SiteID);
db.AddInParameter(dbCommandWrapper, "@DormAreaID", DbType.Int32, info.DormAreaID);
db.AddInParameter(dbCommandWrapper, "@BuildingID", DbType.Int32, info.BuildingID);
db.AddInParameter(dbCommandWrapper, "@UnitID", DbType.Int32, info.UnitID);
db.AddInParameter(dbCommandWrapper, "@FloorID", DbType.Int32, info.FloorID);
db.AddInParameter(dbCommandWrapper, "@RoomID", DbType.Int32, info.RoomID);
db.AddInParameter(dbCommandWrapper, "@Name", DbType.String, info.Name);
db.AddInParameter(dbCommandWrapper, "@Status", DbType.Int32, info.Status);
//db.AddInParameter(dbCommandWrapper, "@RoomSexType", DbType.String, info.RoomSexType);
//db.AddInParameter(dbCommandWrapper, "@RoomType", DbType.Int32, info.RoomType);
db.AddInParameter(dbCommandWrapper, "@Creator", DbType.String, SetNullValue(string.IsNullOrEmpty(info.Creator), info.Creator));
db.AddInParameter(dbCommandWrapper, "@KeyCount", DbType.Int32, info.KeyCount);
#endregion
if (tran == null)
{
intId = Convert.ToInt32(db.ExecuteScalar(dbCommandWrapper));
}
else
{
intId = Convert.ToInt32(db.ExecuteScalar(dbCommandWrapper, tran));
}
return(intId);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 根据ID获取对象
/// </summary>
/// <param name="intID"></param>
/// <returns></returns>
public TB_Bed Get(int intID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
TB_Bed mTB_Bed = null;
try
{
string strSQL = @"select * from TB_Bed where 1=1";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND ID = @ID");
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, intID);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
mTB_Bed = new TB_Bed()
{
DormAreaID = Convert.ToInt32(dt.Rows[0][TB_Bed.col_DormAreaID]),
BuildingID = Convert.ToInt32(dt.Rows[0][TB_Bed.col_BuildingID]),
UnitID = Convert.ToInt32(dt.Rows[0][TB_Bed.col_UnitID]),
FloorID = Convert.ToInt32(dt.Rows[0][TB_Bed.col_FloorID]),
RoomID = Convert.ToInt32(dt.Rows[0][TB_Bed.col_RoomID]),
//RoomSexType = Convert.ToString(dt.Rows[0][TB_Room.col_RoomSexType]),
//RoomType = Convert.ToInt32(dt.Rows[0][TB_Room.col_RoomType]),
Name = Convert.ToString(dt.Rows[0][TB_Bed.col_Name]),
KeyCount = Convert.ToInt32(dt.Rows[0][TB_Bed.col_KeyCount]),
};
}
return(mTB_Bed);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public DataTable GetTable(TB_Bed info)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"select id,[Status] from TB_Bed where 1=1";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
if (info.ID > 0)
{
strBuilder.AppendLine(" AND ID <> @ID");
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, info.ID);
}
strBuilder.AppendLine(" AND SiteID = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, info.SiteID);
if (!string.IsNullOrEmpty(info.Name))
{
strBuilder.AppendLine(" AND NAME = @NAME");
db.AddInParameter(dbCommandWrapper, "@NAME", DbType.String, info.Name);
}
if (info.DormAreaID > 0)
{
strBuilder.AppendLine(" AND DormAreaID = @DormAreaID");
db.AddInParameter(dbCommandWrapper, "@DormAreaID", DbType.Int32, info.DormAreaID);
}
if (info.BuildingID > 0)
{
strBuilder.AppendLine(" AND BuildingID = @BuildingID");
db.AddInParameter(dbCommandWrapper, "@BuildingID", DbType.Int32, info.BuildingID);
}
if (info.UnitID > 0)
{
strBuilder.AppendLine(" AND UnitID = @UnitID");
db.AddInParameter(dbCommandWrapper, "@UnitID", DbType.Int32, info.UnitID);
}
if (info.FloorID > 0)
{
strBuilder.AppendLine(" AND FloorID = @FloorID");
db.AddInParameter(dbCommandWrapper, "@FloorID", DbType.Int32, info.FloorID);
}
if (info.RoomID > 0)
{
strBuilder.AppendLine(" AND RoomID = @RoomID");
db.AddInParameter(dbCommandWrapper, "@RoomID", DbType.Int32, info.RoomID);
}
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取分页数据集合
/// </summary>
/// <param name="info"></param>
/// <param name="pager"></param>
/// <returns></returns>
public DataTable GetTableByEnableStatus(TB_Bed info, ref Pager pager)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
StringBuilder strBuilder = new StringBuilder(@"
select A.[ID]
,A.[RoomID]
,A.[Name]
,A.[Creator]
,A.[CreateDate]
,A.[UpdateBy]
,A.[UpdateDate]
,A.[SiteID]
,A.[DormAreaID]
,A.[BuildingID]
,A.[UnitID]
,A.[FloorID]
,A.[Status]
,A.[KeyCount]
,case
when A.[IsEnable] is null then '已启用'
when A.[IsEnable]='已禁用' then '已禁用'
when A.[IsEnable]='已启用' then '已启用'
else '已启用' end as IsEnable
,B.Name as DormAreaName
,C.name as BuildingName
,D.Name As UnitName
,E.Name As FloorName
,F.Name As RoomName
,F.RoomSexType
,GG.Name AS RoomTypeName
from [TB_Bed] as A
left join TB_dormarea As B
on A.DormAreaID=B.ID
left join TB_building as C
on a.buildingid=c.id
left join TB_Unit AS D
on a.UnitID=D.ID
left join TB_Floor as E
on a.FloorID=E.ID
left join TB_Room AS F
on a.RoomID=F.ID
LEFT JOIN TB_RoomType AS GG
ON F.RoomType=GG.ID ");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
#region 拼接条件
if (null != SessionHelper.Get(HttpContext.Current, TypeManager.User))
{
strBuilder.AppendLine(@"inner join [TB_UserConnectDormArea] AS G
on B.ID=G.[DormAreaID]
where 1=1");
strBuilder.AppendLine(" AND G.[UserID] = @UserID");
db.AddInParameter(dbCommandWrapper, "@UserID", DbType.Int32, ((TB_User)SessionHelper.Get(HttpContext.Current, TypeManager.User)).ID);
}
else
{
strBuilder.AppendLine(" where 1=1 ");
}
//过滤掉禁用的房间
strBuilder.AppendLine(" And(A.IsEnable <> '已禁用' OR A.IsEnable is NULL)");
if (info.ID > 0)
{
strBuilder.AppendLine(" AND A.ID = @ID");
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, info.ID);
}
if (info.SiteID > 0)
{
strBuilder.AppendLine(" AND A.SiteID = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, info.SiteID);
}
if (!string.IsNullOrEmpty(info.Name))
{
strBuilder.AppendLine(" AND A.NAME LIKE @NAME");
db.AddInParameter(dbCommandWrapper, "@NAME", DbType.String, "%" + info.Name + "%");
}
if (info.DormAreaID > 0)
{
strBuilder.AppendLine(" AND A.DormAreaID = @DormAreaID");
db.AddInParameter(dbCommandWrapper, "@DormAreaID", DbType.Int32, info.DormAreaID);
}
if (info.BuildingID > 0)
{
strBuilder.AppendLine(" AND A.BuildingID = @BuildingID");
db.AddInParameter(dbCommandWrapper, "@BuildingID", DbType.Int32, info.BuildingID);
}
if (info.UnitID > 0)
{
strBuilder.AppendLine(" AND A.UnitID = @UnitID");
db.AddInParameter(dbCommandWrapper, "@UnitID", DbType.Int32, info.UnitID);
}
if (info.FloorID > 0)
{
strBuilder.AppendLine(" AND A.FloorID = @FloorID");
db.AddInParameter(dbCommandWrapper, "@FloorID", DbType.Int32, info.FloorID);
}
if (info.RoomType > 0)
{
strBuilder.AppendLine(" AND F.RoomType = @RoomType");
db.AddInParameter(dbCommandWrapper, "@RoomType", DbType.Int32, info.RoomType);
}
if (!String.IsNullOrEmpty(info.RoomSexType))
{
strBuilder.AppendLine(" AND F.RoomSexType = @RoomSexType");
db.AddInParameter(dbCommandWrapper, "@RoomSexType", DbType.String, info.RoomSexType);
}
if (info.RoomID > 0)
{
strBuilder.AppendLine(" AND A.RoomID = @RoomID");
db.AddInParameter(dbCommandWrapper, "@RoomID", DbType.Int32, info.RoomID);
}
if (info.Status > 0)
{
strBuilder.AppendLine(" AND A.Status = @Status");
db.AddInParameter(dbCommandWrapper, "@Status", DbType.Int32, info.Status);
}
#endregion
if (pager != null && !pager.IsNull)
{
dbCommandWrapper.CommandText = pager.GetPagerSql4Count(strBuilder.ToString());
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
pager.TotalRecord = Convert.ToInt32(dt.Rows[0][0]);
dbCommandWrapper.CommandText = pager.GetPagerSql4Data(strBuilder.ToString(), DataBaseTypeEnum.sqlserver);
}
else
{
dbCommandWrapper.CommandText = strBuilder.ToString();
}
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="tb_Bed"></param>
/// <param name="pager"></param>
/// <returns></returns>
public DataTable GetTableByEnableStatus(TB_Bed tb_Bed, ref Pager pager)
{
return(_mTB_BedDAL.GetTableByEnableStatus(tb_Bed, ref pager));
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="tb_Bed"></param>
/// <param name="pager"></param>
/// <returns></returns>
public DataTable GetTable(TB_Bed tb_Bed, ref Pager pager)
{
return(_mTB_BedDAL.GetTable(tb_Bed, ref pager));
}
/// <summary>
/// 员工换房
/// </summary>
/// <param name="intID">需要换房的员工ID</param>
/// <param name="intBedID">新换的床位ID</param>
public void ChangeRoom(int intID, int intBedID)
{
TB_EmployeeCheckIn mTB_EmployeeCheckIn = null;
TB_ChangeRoomRecord mTB_ChangeRoomRecord = null;
string operatorUser = SessionHelper.Get(HttpContext.Current, TypeManager.User) != null
? ((TB_User)SessionHelper.Get(HttpContext.Current, TypeManager.User)).ADAccount
: ((TB_SystemAdmin)SessionHelper.Get(HttpContext.Current, TypeManager.Admin)).Account;
int intOldBedID = 0;
TB_Bed mTB_Bed = null;
//启用事务
_db = DBO.CreateDatabase();
_connection = _db.CreateConnection();
_connection.Open();
_tran = _connection.BeginTransaction();
try
{
mTB_EmployeeCheckIn = this.ConvertTableToList(_mTB_EmployeeCheckInDAL.Get(intID)).FirstOrDefault();
mTB_Bed = _mTB_BedDAL.Get(intBedID);
intOldBedID = mTB_EmployeeCheckIn.BedID;
TB_Bed mOldTB_Bed = _mTB_BedDAL.Get(intOldBedID);
//更新床位状态为入住
_mTB_BedDAL.Update(intBedID, _tran, _db, TypeManager.BedStatus.Busy);
//更新床位状态为空闲
_mTB_BedDAL.Update(intOldBedID, _tran, _db, TypeManager.BedStatus.Free);
//添加换房记录
mTB_ChangeRoomRecord = new TB_ChangeRoomRecord();
mTB_ChangeRoomRecord.BU = mTB_EmployeeCheckIn.BU;
mTB_ChangeRoomRecord.BUID = mTB_EmployeeCheckIn.BUID; //new add buid 2015-02-07
mTB_ChangeRoomRecord.CardNo = mTB_EmployeeCheckIn.CardNo;
mTB_ChangeRoomRecord.ChangeRoomDate = DateTime.Now;
mTB_ChangeRoomRecord.CheckInDate = mTB_EmployeeCheckIn.CheckInDate;
mTB_ChangeRoomRecord.Company = mTB_EmployeeCheckIn.Company;
mTB_ChangeRoomRecord.Creator = operatorUser;
mTB_ChangeRoomRecord.EmployeeNo = mTB_EmployeeCheckIn.EmployeeNo;
mTB_ChangeRoomRecord.IsSmoking = mTB_EmployeeCheckIn.IsSmoking;
mTB_ChangeRoomRecord.Name = mTB_EmployeeCheckIn.Name;
mTB_ChangeRoomRecord.NewBedID = intBedID;
mTB_ChangeRoomRecord.OldBedID = mTB_EmployeeCheckIn.BedID;
mTB_ChangeRoomRecord.Province = mTB_EmployeeCheckIn.Province;
mTB_ChangeRoomRecord.Sex = mTB_EmployeeCheckIn.Sex;
mTB_ChangeRoomRecord.SiteID = mTB_EmployeeCheckIn.SiteID;
mTB_ChangeRoomRecord.Telephone = mTB_EmployeeCheckIn.Telephone;
mTB_ChangeRoomRecord.EmployeeTypeName = mTB_EmployeeCheckIn.EmployeeTypeName;
_mTB_ChangeRoomRecordDAL.Create(mTB_ChangeRoomRecord, _tran, _db);
if (mTB_Bed.BuildingID != mOldTB_Bed.BuildingID)//不是一栋的增加退宿记录
{
//增加退宿记录
AddCheckOut(intID);
}
//更新入住人员信息
mTB_EmployeeCheckIn.BedID = intBedID;
mTB_EmployeeCheckIn.RoomID = mTB_Bed.RoomID;
mTB_EmployeeCheckIn.UpdateBy = operatorUser;
mTB_EmployeeCheckIn.CheckInDate = DateTime.Now;
_mTB_EmployeeCheckInDAL.Edit(mTB_EmployeeCheckIn, _tran, _db);
//提交事务
_tran.Commit();
}
catch (Exception ex)
{
//回滚事务
_tran.Rollback();
throw ex;
}
finally
{
//关闭连接
_connection.Close();
}
}
