protected void btnExport_Click(object sender, EventArgs e)
{
TB_ChangeRoomRecord mTB_ChangeRoomRecord = new TB_ChangeRoomRecord();
ChangeRoomRecordBLL mChangeRoomRecordBLL = new ChangeRoomRecordBLL();
mTB_ChangeRoomRecord.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
mTB_ChangeRoomRecord.CardNo = this.txtScanCardNO.Text.Trim();
mTB_ChangeRoomRecord.EmployeeNo = this.txtWorkDayNo.Text.Trim();
mTB_ChangeRoomRecord.BU = GetSelectedBu();
DateTime dtVal = DateTime.Now;
if (DateTime.TryParse(this.txtStartDay.Text.Trim(), out dtVal))
{
mTB_ChangeRoomRecord.ChangeRoomDateBegin = dtVal;
}
if (DateTime.TryParse(this.txtEndDay.Text.Trim(), out dtVal))
{
mTB_ChangeRoomRecord.ChangeRoomDateEnd = dtVal;
}
string strFileName = mChangeRoomRecordBLL.Export(mTB_ChangeRoomRecord);
this.DownLoadFile(this.Request, this.Response, "换房记录.xls", File.ReadAllBytes(strFileName), 10240000);
//File.Delete(strFileName);
}
private void Bind(int intCurrentIndex)
{
TB_ChangeRoomRecord mTB_ChangeRoomRecord = new TB_ChangeRoomRecord();
ChangeRoomRecordBLL mChangeRoomRecordBLL = new ChangeRoomRecordBLL();
Pager pager = new Pager();
DataTable dtSource = null;
pager.CurrentPageIndex = intCurrentIndex;
pager.srcOrder = " ID desc";
mTB_ChangeRoomRecord.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
mTB_ChangeRoomRecord.CardNo = this.txtScanCardNO.Text.Trim();
mTB_ChangeRoomRecord.EmployeeNo = this.txtWorkDayNo.Text.Trim();
mTB_ChangeRoomRecord.BU = GetSelectedBu();
DateTime dtVal = DateTime.Now;
if (DateTime.TryParse(this.txtStartDay.Text.Trim(), out dtVal))
{
mTB_ChangeRoomRecord.ChangeRoomDateBegin = dtVal;
}
if (DateTime.TryParse(this.txtEndDay.Text.Trim(), out dtVal))
{
mTB_ChangeRoomRecord.ChangeRoomDateEnd = dtVal;
}
dtSource = mChangeRoomRecordBLL.GetPagerData(mTB_ChangeRoomRecord, ref pager);
GridView1.DataSource = dtSource;
GridView1.DataBind();
this.Pager1.ItemCount = pager.TotalRecord;
this.Pager1.PageCount = pager.TotalPage;
this.Pager1.CurrentIndex = pager.CurrentPageIndex;
this.Pager1.PageSize = pager.PageSize;
}
/// <summary>
/// 添加
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public int Create(TB_ChangeRoomRecord info)
{
try
{
Database db = DBO.CreateDatabase();
return(this.Create(info, (DbTransaction)null, db));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 导出
/// </summary>
/// <param name="tb_ChangeRoomRecord"></param>
/// <returns></returns>
public string Export(TB_ChangeRoomRecord tb_ChangeRoomRecord)
{
DataTable dt = _mTB_ChangeRoomRecordDAL.GetTable(tb_ChangeRoomRecord);
dt.Columns.Remove("原楼层");
dt.Columns.Remove("原单元");
dt.Columns.Remove("新楼层");
dt.Columns.Remove("新单元");
string strFilePath = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("..\\..\\"), "Report");
if (!Directory.Exists(strFilePath))
{
Directory.CreateDirectory(strFilePath);
}
string strFileName = Path.Combine(strFilePath, DateTime.Now.ToString("yyMMddHHmmssms_") + "换房记录.xls");
_mExcelHelper.RenderToExcel(dt, strFileName);
return strFileName;
}
/// <summary>
/// 获取到分页数据
/// </summary>
/// <param name="tb_ChangeRoomRecord"></param>
/// <param name="pager"></param>
/// <returns></returns>
public DataTable GetPagerData(TB_ChangeRoomRecord tb_ChangeRoomRecord, ref Pager pager)
{
return _mTB_ChangeRoomRecordDAL.GetTable(tb_ChangeRoomRecord, ref pager);
}
/// <summary>
/// 事务添加,已经添加BUID
/// </summary>
/// <param name="info"></param>
/// <param name="tran"></param>
/// <param name="db"></param>
/// <returns></returns>
public int Create(TB_ChangeRoomRecord info, DbTransaction tran, Database db)
{
DbCommand dbCommandWrapper = null;
int intId;
string strInsertSql = @"INSERT INTO [TB_ChangeRoomRecord]
([SiteID]
,[EmployeeNo]
,[Name]
,[Sex]
,[BUID]
,[BU]
,[Company]
,[CardNo]
,[Telephone]
,[Province]
,[IsSmoking]
,[CheckInDate]
,[ChangeRoomDate]
,[Creator]
,[OldRoomID]
,[OldBedID]
,[NewRoomID]
,[NewBedID]
,EmployeeTypeName)
VALUES
(@SiteID
,@EmployeeNo
,@Name
,@Sex
,@BUID
,@BU
,@Company
,@CardNo
,@Telephone
,@Province
,@IsSmoking
,@CheckInDate
,@ChangeRoomDate
,@Creator
,@OldRoomID
,@OldBedID
,@NewRoomID
,@NewBedID
,@EmployeeTypeName) ";
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, "@EmployeeNo", DbType.String, info.EmployeeNo);
db.AddInParameter(dbCommandWrapper, "@Name", DbType.String, info.Name);
db.AddInParameter(dbCommandWrapper, "@Sex", DbType.Int32, info.Sex);
db.AddInParameter(dbCommandWrapper, "@BUID", DbType.Int32, info.BUID);
db.AddInParameter(dbCommandWrapper, "@BU", DbType.String, info.BU);
db.AddInParameter(dbCommandWrapper, "@Company", DbType.String, info.Company);
db.AddInParameter(dbCommandWrapper, "@CardNo", DbType.String, info.CardNo);
db.AddInParameter(dbCommandWrapper, "@Telephone", DbType.String, info.Telephone);
db.AddInParameter(dbCommandWrapper, "@Province", DbType.String, info.Province);
db.AddInParameter(dbCommandWrapper, "@IsSmoking", DbType.Boolean, info.IsSmoking);
db.AddInParameter(dbCommandWrapper, "@CheckInDate", DbType.DateTime, info.CheckInDate);
db.AddInParameter(dbCommandWrapper, "@ChangeRoomDate", DbType.DateTime, info.ChangeRoomDate);
db.AddInParameter(dbCommandWrapper, "@Creator", DbType.String, info.Creator);
db.AddInParameter(dbCommandWrapper, "@OldRoomID", DbType.Int32, info.OldRoomID);
db.AddInParameter(dbCommandWrapper, "@OldBedID", DbType.Int32, info.OldBedID);
db.AddInParameter(dbCommandWrapper, "@NewRoomID", DbType.Int32, info.NewRoomID);
db.AddInParameter(dbCommandWrapper, "@NewBedID", DbType.Int32, info.NewBedID);
db.AddInParameter(dbCommandWrapper, "@EmployeeTypeName", DbType.String, info.EmployeeTypeName);
#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>
/// 获取到所有的换房记录
/// </summary>
/// <param name="tb_ChangeRoomRecord"></param>
/// <returns></returns>
public DataTable GetTable(TB_ChangeRoomRecord tb_ChangeRoomRecord)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
//TODO 2018-02-07
//由于 EHR.[Segment] 的ID 与 DormManage.[TB_BU]根本不对应
//因此，ON A.BUID=N.ID 是不会成立的
//现在事业部直接取 TB_ChangeRoomRecord.[BU]
StringBuilder strBuilder = new StringBuilder(@"
SELECT A.[EmployeeNo] as '工号'
,A.[Name] as '姓名'
,A.[BU] AS '事业部'
,A.[Company] as '公司'
,A.[CardNo] as '身份证号码'
,A.[Telephone] as '电话号码'
,Convert(varchar(12),A.[CheckInDate],111) as '入住日期'
,Convert(varchar(12),A.[ChangeRoomDate],111) as '换房日期'
,G.Name As '原宿舍区'
,F.Name As '原楼栋'
,E.Name As '原单元'
,D.Name As '原楼层'
,C.Name AS '原房间号'
,B.Name AS '原床位号'
,OldRT.Name as '原房间类型'
,M.Name As '新宿舍区'
,L.Name As '新楼栋'
,K.Name As '新单元'
,J.Name As '新楼层'
,I.Name AS '新房间号'
,H.Name AS '新床位号'
,RT.Name as '新房间类型'
FROM [TB_ChangeRoomRecord] AS A
LEFT JOIN [TB_Bed] AS B
ON A.[OldBedID]=B.ID
LEFT JOIN [TB_Room] AS C
ON B.[RoomID]=C.ID
LEFT JOIN [TB_RoomType] AS OldRT
ON C.RoomType=OldRT.ID
LEFT JOIN [TB_Floor] AS D
ON B.FloorID=D.ID
LEFT JOIN [TB_Unit] AS E
ON B.UnitID=E.ID
LEFT JOIN [TB_Building] AS F
on B.BuildingID=F.ID
LEFT JOIN [TB_DormArea] AS G
ON B.DormAreaID=G.ID
LEFT JOIN [TB_Bed] AS H
ON A.[NewBedID]=H.ID
LEFT JOIN [TB_Room] AS I
ON H.[RoomID]=I.ID
LEFT JOIN [TB_RoomType] AS RT
ON I.RoomType=RT.ID
LEFT JOIN [TB_Floor] AS J
ON H.FloorID=J.ID
LEFT JOIN [TB_Unit] AS K
ON H.UnitID=K.ID
LEFT JOIN [TB_Building] AS L
on H.BuildingID=L.ID
LEFT JOIN [TB_DormArea] AS M
ON H.DormAreaID=M.ID
LEFT JOIN TB_BU AS N
ON A.BUID=N.ID");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
#region 拼接条件
if (null != SessionHelper.Get(HttpContext.Current, TypeManager.User))
{
strBuilder.AppendLine(@" LEFT JOIN [TB_UserConnectDormArea] AS O
on G.ID=O.[DormAreaID]
where 1=1");
strBuilder.AppendLine(" AND O.[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.[SiteID] = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, tb_ChangeRoomRecord.SiteID);
if (!string.IsNullOrEmpty(tb_ChangeRoomRecord.EmployeeNo))
{
strBuilder.AppendLine(" AND A.[EmployeeNo] = @EmployeeNo");
db.AddInParameter(dbCommandWrapper, "@EmployeeNo", DbType.String, tb_ChangeRoomRecord.EmployeeNo);
}
if (!string.IsNullOrEmpty(tb_ChangeRoomRecord.CardNo))
{
strBuilder.AppendLine(" AND A.[CardNo] = @CardNo");
db.AddInParameter(dbCommandWrapper, "@CardNo", DbType.String, tb_ChangeRoomRecord.CardNo);
}
if (tb_ChangeRoomRecord.ChangeRoomDateBegin != default(DateTime))
{
strBuilder.AppendLine(" AND CAST(ChangeRoomDate as Date) between @ChangeRoomDateBegin and @ChangeRoomDateEnd");
db.AddInParameter(dbCommandWrapper, "@ChangeRoomDateBegin", DbType.String, tb_ChangeRoomRecord.ChangeRoomDateBegin.ToString("yyyy/MM/dd"));
db.AddInParameter(dbCommandWrapper, "@ChangeRoomDateEnd", DbType.String, tb_ChangeRoomRecord.ChangeRoomDateEnd.ToString("yyyy/MM/dd"));
}
if (!string.IsNullOrEmpty(tb_ChangeRoomRecord.BU))
{
var spara = string.Format("%{0}%", tb_ChangeRoomRecord.BU);
strBuilder.AppendLine(" AND A.BU like @BU");
db.AddInParameter(dbCommandWrapper, "@BU", DbType.String, spara);
}
#endregion
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取到换房记录分页数据,已经添加BUID
/// </summary>
/// <param name="tb_ChangeRoomRecord"></param>
/// <param name="pager"></param>
/// <returns></returns>
public DataTable GetTable(TB_ChangeRoomRecord tb_ChangeRoomRecord, ref Pager pager)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
//TODO 2018-02-07
//由于 EHR.[Segment] 的ID 与 DormManage.[TB_BU]根本不对应
//因此，ON A.BUID=N.ID 是不会成立的
//现在事业部直接取 TB_ChangeRoomRecord.[BU]
StringBuilder strBuilder = new StringBuilder(@"SELECT A.[ID]
,A.[EmployeeNo]
,A.[Sex]
,A.[BU] AS BU
,A.[Company]
,A.EmployeeTypeName
, A.[CardNo]
,A.[Telephone]
,A.[Province]
,Convert(varchar(12),A.[CheckInDate],111) as CheckInDate
,Convert(varchar(12),A.[ChangeRoomDate],111) as ChangeRoomDate
,A.[Name]
,A.[SiteID]
,B.Name+'->'+'<span style=color:red>'+H.Name+'</span>' AS BedName
,C.Name+'->'+'<span style=color:red>'+I.Name+'</span>' AS RoomName
,OldRoomType.Name+'->'+'<span style=color:red>'+NewRoomType.Name+'</span>' AS RoomType
,D.Name+'->'+'<span style=color:red>'+J.Name+'</span>' As FloorName
,E.Name+'->'+'<span style=color:red>'+K.Name+'</span>' As UnitName
,F.Name+'->'+'<span style=color:red>'+L.Name+'</span>' As BuildingName
,G.Name+'->'+'<span style=color:red>'+M.Name+'</span>' As DormAreaName
FROM [TB_ChangeRoomRecord] AS A
LEFT JOIN [TB_Bed] AS B
ON A.[OldBedID]=B.ID
LEFT JOIN [TB_Room] AS C
ON B.[RoomID]=C.ID
LEFT JOIN [TB_Floor] AS D
ON B.FloorID=D.ID
LEFT JOIN [TB_Unit] AS E
ON B.UnitID=E.ID
LEFT JOIN [TB_Building] AS F
on B.BuildingID=F.ID
LEFT JOIN [TB_DormArea] AS G
ON B.DormAreaID=G.ID
LEFT JOIN [TB_Bed] AS H
ON A.[NewBedID]=H.ID
LEFT JOIN [TB_Room] AS I
ON H.[RoomID]=I.ID
LEFT JOIN [TB_Floor] AS J
ON H.FloorID=J.ID
LEFT JOIN [TB_Unit] AS K
ON H.UnitID=K.ID
LEFT JOIN [TB_Building] AS L
on H.BuildingID=L.ID
LEFT JOIN [TB_DormArea] AS M
ON H.DormAreaID=M.ID
LEFT JOIN TB_BU AS N
ON A.BUID=N.ID
LEFT JOIN TB_RoomType AS OldRoomType
ON C.RoomType=OldRoomType.ID
LEFT JOIN TB_RoomType AS NewRoomType
ON I.RoomType=NewRoomType.ID ");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
#region 拼接条件
if (null != SessionHelper.Get(HttpContext.Current, TypeManager.User))
{
strBuilder.AppendLine(@" LEFT JOIN [TB_UserConnectDormArea] AS O
on G.ID=O.[DormAreaID]
where 1=1");
strBuilder.AppendLine(" AND O.[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.[SiteID] = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, tb_ChangeRoomRecord.SiteID);
if (!string.IsNullOrEmpty(tb_ChangeRoomRecord.EmployeeNo))
{
strBuilder.AppendLine(" AND A.[EmployeeNo] = @EmployeeNo");
db.AddInParameter(dbCommandWrapper, "@EmployeeNo", DbType.String, tb_ChangeRoomRecord.EmployeeNo);
}
if (!string.IsNullOrEmpty(tb_ChangeRoomRecord.CardNo))
{
strBuilder.AppendLine(" AND A.[CardNo] = @CardNo");
db.AddInParameter(dbCommandWrapper, "@CardNo", DbType.String, tb_ChangeRoomRecord.CardNo);
}
if (tb_ChangeRoomRecord.ChangeRoomDateBegin != default(DateTime))
{
strBuilder.AppendLine(" AND CAST(ChangeRoomDate as Date) between @ChangeRoomDateBegin and @ChangeRoomDateEnd");
db.AddInParameter(dbCommandWrapper, "@ChangeRoomDateBegin", DbType.String, tb_ChangeRoomRecord.ChangeRoomDateBegin.ToString("yyyy/MM/dd"));
db.AddInParameter(dbCommandWrapper, "@ChangeRoomDateEnd", DbType.String, tb_ChangeRoomRecord.ChangeRoomDateEnd.ToString("yyyy/MM/dd"));
}
if (!string.IsNullOrEmpty(tb_ChangeRoomRecord.BU))
{
var spara = string.Format("%{0}%", tb_ChangeRoomRecord.BU);
strBuilder.AppendLine(" AND A.BU like @BU");
db.AddInParameter(dbCommandWrapper, "@BU", DbType.String, spara);
}
#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="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();
}
}
