/// <summary>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExport_Click(object sender, EventArgs e)
{
TB_AllowanceApply mTB_AllowanceApply = new TB_AllowanceApply();
TB_AllowanceApplyBLL mTB_AllowanceApplyBLL = new TB_AllowanceApplyBLL();
mTB_AllowanceApply.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
mTB_AllowanceApply.Name = "";
mTB_AllowanceApply.CardNo = this.txtScanCardNO.Text.Trim();
mTB_AllowanceApply.EmployeeNo = this.txtWorkDayNo.Text.Trim();
mTB_AllowanceApply.BU = this.txtBu.Text.Trim();
DateTime dtCheckOut = DateTime.Now;
if (DateTime.TryParse(this.txtHireDate.Text.Trim(), out dtCheckOut))
{
mTB_AllowanceApply.Hire_Date = dtCheckOut;
}
dtCheckOut = DateTime.Now;
if (DateTime.TryParse(this.txtEffectiveDate.Text.Trim(), out dtCheckOut))
{
mTB_AllowanceApply.Effective_Date = dtCheckOut;
}
if (ddlEmpType.SelectedIndex > 0)
{
mTB_AllowanceApply.EmployeeTypeName = ddlEmpType.SelectedItem.Text;
}
string strFileName = mTB_AllowanceApplyBLL.Export(mTB_AllowanceApply);
this.DownLoadFile(this.Request, this.Response, "住房津申请记录.xls", File.ReadAllBytes(strFileName), 10240000);
//File.Delete(strFileName);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public int Edit(TB_AllowanceApply 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 Create(TB_AllowanceApply info, DbTransaction tran, Database db)
{
DbCommand dbCommandWrapper = null;
int intId;
string strInsertSql = @"INSERT INTO TB_AllowanceApply (EmployeeNo,Name,CardNo,Sex,Company,BU,Grade,CheckOutDate,EmployeeTypeName,BZ,CreateDate,CreateUser,SiteID,Hire_Date,Effective_Date)
VALUES(@EmployeeNo,@Name,@CardNo,@Sex,@Company,@BU,@Grade,@CheckOutDate,@EmployeeTypeName,@BZ,@CreateDate,@CreateUser,@SiteID,@Hire_Date,@Effective_Date)";
string strSelectIdSql = ";SELECT SCOPE_IDENTITY()";
try
{
dbCommandWrapper = db.GetSqlStringCommand(strInsertSql + strSelectIdSql);
#region Add parameters
db.AddInParameter(dbCommandWrapper, "@EmployeeNo", DbType.String, SetNullValue(string.IsNullOrEmpty(info.EmployeeNo), info.EmployeeNo));
db.AddInParameter(dbCommandWrapper, "@Name", DbType.String, SetNullValue(string.IsNullOrEmpty(info.Name), info.Name));
db.AddInParameter(dbCommandWrapper, "@CardNo", DbType.String, SetNullValue(string.IsNullOrEmpty(info.CardNo), info.CardNo));
db.AddInParameter(dbCommandWrapper, "@Sex", DbType.String, SetNullValue(string.IsNullOrEmpty(info.Sex), info.Sex));
db.AddInParameter(dbCommandWrapper, "@Company", DbType.String, SetNullValue(string.IsNullOrEmpty(info.Company), info.Company));
db.AddInParameter(dbCommandWrapper, "@BU", DbType.String, SetNullValue(string.IsNullOrEmpty(info.BU), info.BU));
db.AddInParameter(dbCommandWrapper, "@Grade", DbType.Int32, info.Grade);
db.AddInParameter(dbCommandWrapper, "@CheckOutDate", DbType.String, SetNullValue(string.IsNullOrEmpty(info.CheckOutDate), info.CheckOutDate));
db.AddInParameter(dbCommandWrapper, "@EmployeeTypeName", DbType.String, SetNullValue(string.IsNullOrEmpty(info.EmployeeTypeName), info.EmployeeTypeName));
db.AddInParameter(dbCommandWrapper, "@BZ", DbType.String, SetNullValue(string.IsNullOrEmpty(info.BZ), info.BZ));
db.AddInParameter(dbCommandWrapper, "@CreateDate", DbType.DateTime, info.CreateDate);
db.AddInParameter(dbCommandWrapper, "@CreateUser", DbType.String, SetNullValue(string.IsNullOrEmpty(info.CreateUser), info.CreateUser));
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, info.SiteID);
db.AddInParameter(dbCommandWrapper, "@Hire_Date", DbType.DateTime, info.Hire_Date);
db.AddInParameter(dbCommandWrapper, "@Effective_Date", DbType.DateTime, info.Effective_Date);
#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_EmployeeCheckIn"></param>
/// <returns></returns>
public string Export(TB_AllowanceApply tB_AllowanceApply)
{
DataTable dt = _mTB_AllowanceApply.GetTable(tB_AllowanceApply);
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>
private void ddlBind()
{
TB_AllowanceApply mTB_AllowanceApply = new TB_AllowanceApply();
TB_AllowanceApplyBLL mTB_AllowanceApplyBLL = new TB_AllowanceApplyBLL();
#region 宿舍区
this.ddlEmpType.DataValueField = "EmployeeTypeName";
this.ddlEmpType.DataTextField = "EmployeeTypeName";
this.ddlEmpType.DataSource = mTB_AllowanceApplyBLL.GetAllEmployeeTypes();
this.ddlEmpType.DataBind();
this.ddlEmpType.Items.Insert(0, new ListItem()
{
Value = "0", Text = "--请选择--"
});
#endregion
}
private int GetAllowanceApply(string EmployeeID)
{
TB_AllowanceApply mTB_AllowanceApply = new TB_AllowanceApply();
TB_AllowanceApplyBLL mTB_AllowanceApplyBLL = new TB_AllowanceApplyBLL();
Pager pager = new Pager();
DataTable dtSource = null;
pager.CurrentPageIndex = 1;
pager.srcOrder = " ID desc";
mTB_AllowanceApply.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
mTB_AllowanceApply.EmployeeNo = EmployeeID;
mTB_AllowanceApply.Name = "";
mTB_AllowanceApply.CardNo = "";
dtSource = mTB_AllowanceApplyBLL.GetTable(mTB_AllowanceApply, ref pager);
return(Convert.ToInt32(dtSource.Rows[0]["ID"]));
}
private void Bind(int intCurrentIndex)
{
TB_AllowanceApply mTB_AllowanceApply = new TB_AllowanceApply();
TB_AllowanceApplyBLL mTB_AllowanceApplyBLL = new TB_AllowanceApplyBLL();
Pager pager = new Pager();
pager.CurrentPageIndex = intCurrentIndex;
pager.srcOrder = " ID desc";
mTB_AllowanceApply.Name = "";
mTB_AllowanceApply.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
mTB_AllowanceApply.CardNo = this.txtScanCardNO.Text.Trim();
mTB_AllowanceApply.EmployeeNo = this.txtWorkDayNo.Text.Trim();
mTB_AllowanceApply.BU = this.txtBu.Text.Trim();
DateTime dtCheckOut = DateTime.Now;
if (DateTime.TryParse(this.txtHireDate.Text.Trim(), out dtCheckOut))
{
mTB_AllowanceApply.Hire_Date = dtCheckOut;
}
dtCheckOut = DateTime.Now;
if (DateTime.TryParse(this.txtEffectiveDate.Text.Trim(), out dtCheckOut))
{
mTB_AllowanceApply.Effective_Date = dtCheckOut;
}
if (ddlEmpType.SelectedIndex > 0)
{
mTB_AllowanceApply.EmployeeTypeName = ddlEmpType.SelectedItem.Text;
}
DataTable dt = mTB_AllowanceApplyBLL.GetTable(mTB_AllowanceApply, ref pager);
GridView1.DataSource = dt;
GridView1.DataBind();
this.Pager1.ItemCount = pager.TotalRecord;
this.Pager1.PageCount = pager.TotalPage;
this.Pager1.CurrentIndex = pager.CurrentPageIndex;
this.Pager1.PageSize = pager.PageSize;
}
/// <summary>
/// 根据ID获取对象
/// </summary>
/// <param name="intID"></param>
/// <returns></returns>
public TB_AllowanceApply Get(int intID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
TB_AllowanceApply mTB_AllowanceApply = null;
try
{
string strSQL = @"select * from TB_AllowanceApply 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_AllowanceApply = new TB_AllowanceApply()
{
Name = Convert.ToString(dt.Rows[0]["Name"]),
};
}
return(mTB_AllowanceApply);
}
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 Edit(TB_AllowanceApply info, DbTransaction tran, Database db)
{
DbCommand dbCommandWrapper = null;
string strUpdateSql = @"UPDATE TB_AllowanceApply SET Name=@Name,UpdateBy=@UpdateBy,UpdateDate=@UpdateDate WHERE ID=@ID";
try
{
dbCommandWrapper = db.GetSqlStringCommand(strUpdateSql);
#region Add parameters
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, info.ID);
db.AddInParameter(dbCommandWrapper, "@Name", DbType.String, SetNullValue(string.IsNullOrEmpty(info.Name), info.Name));
db.AddInParameter(dbCommandWrapper, "@UpdateBy", DbType.String, SetNullValue(string.IsNullOrEmpty(info.CreateUser), info.CreateUser));
db.AddInParameter(dbCommandWrapper, "@UpdateDate", DbType.DateTime, DateTime.Now);
#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;
}
}
}
protected void btnAssign_Click(object sender, EventArgs e)
{
try
{
var sInputID = this.txtScanCardNO.Text.Trim();
var sWorkDayNO = this.txtWorkDayNo.Text.Trim();
string sIdCard = string.Empty;
GetIdCardNumber(sInputID, sWorkDayNO, out sIdCard);
TB_AllowanceApplyBLL bll = new TB_AllowanceApplyBLL();
//查询人员信息
DataTable dtEmployeeInfo = new StaffingBLL().GetTableWithIDL(sWorkDayNO, sIdCard);
if (!DataTableHelper.IsEmptyDataTable(dtEmployeeInfo))
{
//检查用工类型
var sEmpType = dtEmployeeInfo.Rows[0]["EmployeeTypeName"].ToString();
if (!CanApplay(sEmpType))
{
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('此用户用工类型为：" + sEmpType + "，不能申请住房津贴')", true);
return;
}
//检查是否已经申请津贴
TB_AllowanceApply tb_AllowanceApply = new TB_AllowanceApply();
tb_AllowanceApply.EmployeeNo = dtEmployeeInfo.Rows[0]["EmployeeID"].ToString();
tb_AllowanceApply.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
Pager pager = new Pager();
pager.CurrentPageIndex = 1;
pager.srcOrder = " ID desc";
DataTable dt = bll.GetTableByID(tb_AllowanceApply, ref pager);
if (dt.Rows.Count > 0)
{
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('此用户已经申请过住房津贴，不能重复申请')", true);
return;
}
//检查是否有分配记录
DataTable dtAssignArea = new AssignRoomBLL().GetAssignDormArea(dtEmployeeInfo.Rows[0]["IDCardNumber"].ToString());
if (dtAssignArea.Rows.Count > 0)
{
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('此用户已有分配住宿记录！暂时不能申请')", true);
return;
}
//检查是否已经有CheckIn的记录
DataTable DtCheck = new AssignRoomBLL().GetAssignedData(dtEmployeeInfo.Rows[0]["IDCardNumber"].ToString(), "");
if (DtCheck != null && DtCheck.Rows.Count > 0)
{
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('此用户已有入住记录！请先退宿舍')", true);
}
else
{
TB_AllowanceApply tB_AllowanceApply = new TB_AllowanceApply();
tB_AllowanceApply.EmployeeNo = dtEmployeeInfo.Rows[0]["EmployeeID"].ToString();
tB_AllowanceApply.Name = dtEmployeeInfo.Rows[0]["ChineseName"].ToString();
tB_AllowanceApply.CardNo = sIdCard;
tB_AllowanceApply.Sex = dtEmployeeInfo.Rows[0]["Sex"].ToString();
tB_AllowanceApply.Company = "";
tB_AllowanceApply.BU = dtEmployeeInfo.Rows[0]["SegmentName"].ToString();
tB_AllowanceApply.Grade = 0;
tB_AllowanceApply.CheckOutDate = GetCheckOutDate(tB_AllowanceApply.EmployeeNo);
tB_AllowanceApply.EmployeeTypeName = dtEmployeeInfo.Rows[0]["EmployeeTypeName"].ToString();
tB_AllowanceApply.CreateUser = (base.UserInfo == null ? base.SystemAdminInfo.Account : base.UserInfo.ADAccount);
tB_AllowanceApply.CreateDate = System.DateTime.Now;
tB_AllowanceApply.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
tB_AllowanceApply.Hire_Date = Convert.ToDateTime(dtEmployeeInfo.Rows[0]["Hire_Date"]);
tB_AllowanceApply.Effective_Date = DateTime.Now.AddDays(1 - DateTime.Now.Day).AddMonths(1).AddHours(-DateTime.Now.Hour + 1).AddMinutes(-DateTime.Now.Minute).AddSeconds(-DateTime.Now.Second);
bll.ADDAllowanceApply(tB_AllowanceApply);
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('申请成功！')", true);
this.Bind(1);
if (dtEmployeeInfo.Rows[0]["Phone"].ToString() != "")
{
//string sContent = dtEmployeeInfo.Rows[0]["EmployeeID"].ToString() + "亲，以下是你被分配的宿舍信息：" + sDormAreaName + "宿舍 " + sBuildingName + "栋 " + sRoomName + "房间 " + sBedName + "床. 该宿舍的服务热线18926980019,请于3天内前往宿舍区办理入住手续，谢谢！ ";
try
{
//SendSMS(dtEmployeeInfo.Rows[0]["Phone"].ToString(), sContent);
}
catch
{
return;
}
}
}
}
else
{
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('招聘系统找不到此用户！')", true);
}
}
catch (Exception ex)
{
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('" + ex.Message + "')", true);
}
finally
{
ClearWorkIDInput();
}
}
/// <summary>
///
/// </summary>
/// <param name="tb_AllowanceApply"></param>
/// <returns></returns>
public DataTable GetTable(TB_AllowanceApply tb_AllowanceApply)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"select Company 公司
,BU 事业部
,Name 姓名
,Sex 性别
,EmployeeNo 工号
,CheckOutDate 退宿时间
,Grade 级别
,CONVERT(varchar(100), Hire_Date, 23) 入职日期
,EmployeeTypeName 用工类型
,CONVERT(varchar(100), CreateDate, 23) 申请日期
,CONVERT(varchar(100), Effective_Date, 23) 生效日期
,BZ 备注 from TB_AllowanceApply where 1=1";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND SiteID = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, tb_AllowanceApply.SiteID);
if (tb_AllowanceApply.ID > 0)
{
strBuilder.AppendLine(" AND ID <> @ID");
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, tb_AllowanceApply.ID);
}
//strBuilder.AppendLine(" AND NAME = @NAME");
//db.AddInParameter(dbCommandWrapper, "@NAME", DbType.String, TB_AllowanceApply.Name);
if (!string.IsNullOrEmpty(tb_AllowanceApply.EmployeeNo))
{
strBuilder.AppendLine(" AND EmployeeNo = @EmployeeNo");
db.AddInParameter(dbCommandWrapper, "@EmployeeNo", DbType.String, tb_AllowanceApply.EmployeeNo);
}
if (!string.IsNullOrEmpty(tb_AllowanceApply.CardNo))
{
strBuilder.AppendLine(" AND CardNo = @cardNo");
db.AddInParameter(dbCommandWrapper, "@cardNo", DbType.String, tb_AllowanceApply.CardNo);
}
if (!string.IsNullOrEmpty(tb_AllowanceApply.BU))
{
var spara = string.Format("%{0}%", tb_AllowanceApply.BU);
strBuilder.AppendLine(" AND BU like @BU");
db.AddInParameter(dbCommandWrapper, "@BU", DbType.String, spara);
}
if (tb_AllowanceApply.Hire_Date != null)
{
strBuilder.AppendLine(" AND CAST(Hire_Date as Date)=@hireDate");
var sday = tb_AllowanceApply.Hire_Date.Value.ToString("yyyy-MM-dd");
db.AddInParameter(dbCommandWrapper, "@hireDate", DbType.String, sday);
}
if (tb_AllowanceApply.Effective_Date != null)
{
strBuilder.AppendLine(" AND CAST(Effective_Date as Date)=@effDate");
var sday = tb_AllowanceApply.Effective_Date.Value.ToString("yyyy-MM-dd");
db.AddInParameter(dbCommandWrapper, "@effDate", DbType.String, sday);
}
if (!string.IsNullOrEmpty(tb_AllowanceApply.EmployeeTypeName))
{
strBuilder.AppendLine(" AND EmployeeTypeName = @empType");
db.AddInParameter(dbCommandWrapper, "@empType", DbType.String, tb_AllowanceApply.EmployeeTypeName);
}
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_AllowanceApply"></param>
/// <param name="pager"></param>
/// <returns></returns>
public DataTable GetTable(TB_AllowanceApply tb_AllowanceApply, ref Pager pager)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"select *,CONVERT(varchar(100), A.Hire_Date, 23) HireDate,CONVERT(varchar(100), A.CreateDate, 23) AllowanceDate,CONVERT(varchar(100), A.Effective_Date, 23) EffectiveDate from TB_AllowanceApply AS A ";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
#region 拼接条件
strBuilder.AppendLine(" where 1=1");
if (tb_AllowanceApply.SiteID > 0)
{
strBuilder.AppendLine(" AND A.SiteID = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, tb_AllowanceApply.SiteID);
}
if (!string.IsNullOrEmpty(tb_AllowanceApply.EmployeeNo))
{
strBuilder.AppendLine(" AND A.EmployeeNo = @EmployeeNo");
db.AddInParameter(dbCommandWrapper, "@EmployeeNo", DbType.String, tb_AllowanceApply.EmployeeNo);
}
if (!string.IsNullOrEmpty(tb_AllowanceApply.CardNo))
{
strBuilder.AppendLine(" AND A.CardNo = @cardNo");
db.AddInParameter(dbCommandWrapper, "@cardNo", DbType.String, tb_AllowanceApply.CardNo);
}
if (!string.IsNullOrEmpty(tb_AllowanceApply.BU))
{
var spara = string.Format("%{0}%", tb_AllowanceApply.BU);
strBuilder.AppendLine(" AND A.BU like @BU");
db.AddInParameter(dbCommandWrapper, "@BU", DbType.String, spara);
}
if (tb_AllowanceApply.Hire_Date != null)
{
strBuilder.AppendLine(" AND CAST(Hire_Date as Date)=@hireDate");
var sday = tb_AllowanceApply.Hire_Date.Value.ToString("yyyy-MM-dd");
db.AddInParameter(dbCommandWrapper, "@hireDate", DbType.String, sday);
}
if (tb_AllowanceApply.Effective_Date != null)
{
strBuilder.AppendLine(" AND CAST(Effective_Date as Date)=@effDate");
var sday = tb_AllowanceApply.Effective_Date.Value.ToString("yyyy-MM-dd");
db.AddInParameter(dbCommandWrapper, "@effDate", DbType.String, sday);
}
if (!string.IsNullOrEmpty(tb_AllowanceApply.EmployeeTypeName))
{
strBuilder.AppendLine(" AND A.EmployeeTypeName = @empType");
db.AddInParameter(dbCommandWrapper, "@empType", DbType.String, tb_AllowanceApply.EmployeeTypeName);
}
#endregion
if (pager != null && !pager.IsNull)
{
strSQL = pager.GetPagerSql4Count(strBuilder.ToString());
dbCommandWrapper.CommandText = strSQL;
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;
}
}
}
protected void btnAssign_Click(object sender, EventArgs e)
{
try
{
var sInputID = this.txtScanCardNO.Text.Trim();
var sWorkDayNO = this.txtWorkDayNo.Text.Trim();
string sIdCard = string.Empty;
GetIdCardNumber(sInputID, sWorkDayNO, out sIdCard);
//查询人员信息
DataTable dtEmployeeInfo = new StaffingBLL().GetTableWithIDL(sWorkDayNO, sIdCard);
TB_AllowanceApplyCancelBLL bll = new TB_AllowanceApplyCancelBLL();
if (!DataTableHelper.IsEmptyDataTable(dtEmployeeInfo))
{
//检查是否已经申请津贴
TB_AllowanceApply tb_AllowanceApply = new TB_AllowanceApply();
tb_AllowanceApply.EmployeeNo = dtEmployeeInfo.Rows[0]["EmployeeID"].ToString();
tb_AllowanceApply.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
Pager pager = new Pager();
pager.CurrentPageIndex = 1;
pager.srcOrder = " ID desc";
DataTable dt = new TB_AllowanceApplyBLL().GetTableByID(tb_AllowanceApply, ref pager);
if (dt.Rows.Count == 0)
{
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('此用户未申请过住房津贴，不能申请取消津贴')", true);
return;
}
//检查是否已经申请津贴
TB_AllowanceApplyCancel tb_AllowanceApplyCancel = new TB_AllowanceApplyCancel();
tb_AllowanceApplyCancel.EmployeeNo = dtEmployeeInfo.Rows[0]["EmployeeID"].ToString();
tb_AllowanceApplyCancel.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
DataTable dt1 = bll.GetTableByID(tb_AllowanceApplyCancel, ref pager);
if (dt1.Rows.Count > 0)
{
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('此用户已经申请过取消住房津贴，不能重复申请')", true);
return;
}
//检查是否已经有CheckIn的记录
TB_AllowanceApplyCancel tB_AllowanceApplyCancel = new TB_AllowanceApplyCancel();
tB_AllowanceApplyCancel.EmployeeNo = dtEmployeeInfo.Rows[0]["EmployeeID"].ToString();
tB_AllowanceApplyCancel.Name = dtEmployeeInfo.Rows[0]["ChineseName"].ToString();
tB_AllowanceApplyCancel.CardNo = sIdCard;
tB_AllowanceApplyCancel.Sex = dtEmployeeInfo.Rows[0]["Sex"].ToString();
tB_AllowanceApplyCancel.Company = "";
tB_AllowanceApplyCancel.BU = dtEmployeeInfo.Rows[0]["SegmentName"].ToString();
tB_AllowanceApplyCancel.Grade = 0;
tB_AllowanceApplyCancel.AlloWanceApplyID = GetAllowanceApply(tB_AllowanceApplyCancel.EmployeeNo);
tB_AllowanceApplyCancel.EmployeeTypeName = dtEmployeeInfo.Rows[0]["EmployeeTypeName"].ToString();
tB_AllowanceApplyCancel.CreateUser = (base.UserInfo == null ? base.SystemAdminInfo.Account : base.UserInfo.ADAccount);
tB_AllowanceApplyCancel.CreateDate = System.DateTime.Now;
tB_AllowanceApplyCancel.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID);
tB_AllowanceApplyCancel.Hire_Date = Convert.ToDateTime(dtEmployeeInfo.Rows[0]["Hire_Date"]);
tB_AllowanceApplyCancel.Effective_Date = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
bll.ADDAllowanceCancelApply(tB_AllowanceApplyCancel);
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('申请成功！')", true);
this.Bind(1);
if (dtEmployeeInfo.Rows[0]["Phone"].ToString() != "")
{
//string sContent = dtEmployeeInfo.Rows[0]["EmployeeID"].ToString() + "亲，以下是你被分配的宿舍信息：" + sDormAreaName + "宿舍 " + sBuildingName + "栋 " + sRoomName + "房间 " + sBedName + "床. 该宿舍的服务热线18926980019,请于3天内前往宿舍区办理入住手续，谢谢！ ";
try
{
//SendSMS(dtEmployeeInfo.Rows[0]["Phone"].ToString(), sContent);
}
catch
{
return;
}
}
}
else
{
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('招聘系统找不到此用户！')", true);
}
}
catch (Exception ex)
{
ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "msg", "alert('" + ex.Message + "')", true);
}
finally
{
ClearWorkIDInput();
}
}
public int ADDAllowanceApply(TB_AllowanceApply tb_AllowanceApply)
{
return(_mTB_AllowanceApply.Create(tb_AllowanceApply));
}
/// <summary>
/// ID查询
/// </summary>
/// <param name="mTB_DormArea"></param>
/// <param name="pager"></param>
/// <returns></returns>
public DataTable GetTableByID(TB_AllowanceApply tb_AllowanceApply, ref Pager pager)
{
return(_mTB_AllowanceApply.GetTable(tb_AllowanceApply, ref pager));
}
/// <summary>
/// 导入
/// </summary>
/// <param name="filePath"></param>
/// <returns>导入失败记录</returns>
public DataTable Import(string filePath)
{
//读取Excel内容
DataTable dt = _mExcelHelper.GetDataFromExcel(filePath);
dt.Columns.Add("BZ");
DataTable dtBU = new DataTable();
//SiteID
int intSiteID = SessionHelper.Get(HttpContext.Current, TypeManager.User) != null ?
((TB_User)SessionHelper.Get(HttpContext.Current, TypeManager.User)).SiteID :
((TB_SystemAdmin)SessionHelper.Get(HttpContext.Current, TypeManager.Admin)).SiteID;
//操作用户账号
string currentUser = 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;
//获取到整个site的所有入住人员信息
DataTable dtEmployeeCheckIn = new TB_EmployeeCheckInDAL().GetTableBySiteID(intSiteID);
DataTable dtError = dt.Clone();
StringBuilder sbBedID = new StringBuilder();//用于保存床位ID
foreach (DataRow dr in dt.Rows)
{
try
{
var sID = dr["身份证号码"] as string;
var sEmployeeNo = dr["工号"] as string;
var sCond = string.Empty;
if (string.IsNullOrEmpty(sID))
{
sCond = string.Format("EmployeeNo='{0}'", sEmployeeNo == null ? string.Empty : sEmployeeNo.Trim());
}
else
{
sCond = string.Format("CardNo='{0}'", sID == null ? string.Empty : sID.Trim());
}
DataRow[] drEmployeeCheckInArr = dtEmployeeCheckIn.Select(sCond);
if (drEmployeeCheckInArr.Length > 0)
{
dr["BZ"] = "已有入住记录";
dtError.ImportRow(dr);
}
else
{
TB_AllowanceApply tB_AllowanceApply = new TB_AllowanceApply();
tB_AllowanceApply.EmployeeNo = dr["工号"].ToString();
tB_AllowanceApply.Name = dr["姓名"].ToString();
tB_AllowanceApply.CardNo = dr["身份证号码"].ToString();
tB_AllowanceApply.Sex = dr["性别"].ToString();
tB_AllowanceApply.Company = dr["公司"].ToString();
tB_AllowanceApply.BU = dr["事业部"].ToString();
int nGrade = 0;
int.TryParse(dr["级别"].ToString(), out nGrade);
tB_AllowanceApply.Grade = nGrade;
tB_AllowanceApply.CheckOutDate = dr["退宿日期"].ToString();
tB_AllowanceApply.EmployeeTypeName = dr["用工类型"].ToString();
tB_AllowanceApply.CreateUser = currentUser;
tB_AllowanceApply.CreateDate = System.DateTime.Now;
tB_AllowanceApply.SiteID = intSiteID;
tB_AllowanceApply.Hire_Date = Convert.ToDateTime(dr["入职日期"].ToString());
ADDAllowanceApply(tB_AllowanceApply);
}
}
catch (Exception ex)
{
var smsg = ex.Message;
if (smsg.IndexOf("PRIMARY KEY", StringComparison.InvariantCultureIgnoreCase) != -1)
{
smsg = "已经有相关记录了";
}
dr["BZ"] = "申请记录失败：" + smsg;
dtError.ImportRow(dr);
}
}
return(dtError);
}
