/// <summary> /// 导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnExport_Click(object sender, EventArgs e) { TB_AllowanceApplyCancel mTB_AllowanceApplyCancel = new TB_AllowanceApplyCancel(); TB_AllowanceApplyCancelBLL mTB_AllowanceApplyCancelBLL = new TB_AllowanceApplyCancelBLL(); mTB_AllowanceApplyCancel.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID); mTB_AllowanceApplyCancel.Name = ""; mTB_AllowanceApplyCancel.CardNo = this.txtScanCardNO.Text.Trim(); mTB_AllowanceApplyCancel.EmployeeNo = this.txtWorkDayNo.Text.Trim(); mTB_AllowanceApplyCancel.BU = this.txtBu.Text.Trim(); DateTime dtCheckOut = DateTime.Now; if (DateTime.TryParse(this.txtHireDate.Text.Trim(), out dtCheckOut)) { mTB_AllowanceApplyCancel.Hire_Date = dtCheckOut; } dtCheckOut = DateTime.Now; if (DateTime.TryParse(this.txtEffectiveDate.Text.Trim(), out dtCheckOut)) { mTB_AllowanceApplyCancel.Effective_Date = dtCheckOut; } if (ddlEmpType.SelectedIndex > 0) { mTB_AllowanceApplyCancel.EmployeeTypeName = ddlEmpType.SelectedItem.Text; } string strFileName = mTB_AllowanceApplyCancelBLL.Export(mTB_AllowanceApplyCancel); this.DownLoadFile(this.Request, this.Response, "住房津贴申请取消记录.xls", File.ReadAllBytes(strFileName), 10240000); //File.Delete(strFileName); }
/// <summary> /// 添加 /// </summary> /// <param name="info"></param> /// <returns></returns> public int Create(TB_AllowanceApplyCancel info) { try { Database db = DBO.CreateDatabase(); return(this.Create(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_AllowanceApplyCancel info, DbTransaction tran, Database db) { DbCommand dbCommandWrapper = null; int intId; string strInsertSql = @"INSERT INTO TB_AllowanceApplyCancel (EmployeeNo,Name,CardNo,Sex,Company,BU,Grade,AlloWanceApplyID,EmployeeTypeName,BZ,CreateDate,CreateUser,SiteID,Hire_Date,Effective_Date) VALUES(@EmployeeNo,@Name,@CardNo,@Sex,@Company,@BU,@Grade,@AlloWanceApplyID,@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, "@AlloWanceApplyID", DbType.Int32, info.AlloWanceApplyID); 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_AllowanceApplyCancel tB_AllowanceApply) { DataTable dt = _mTB_AllowanceApplyCancel.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_AllowanceApplyCancel mTB_AllowanceApplyCancel = new TB_AllowanceApplyCancel(); TB_AllowanceApplyCancelBLL mTB_AllowanceApplyCancelBLL = new TB_AllowanceApplyCancelBLL(); this.ddlEmpType.DataValueField = "EmployeeTypeName"; this.ddlEmpType.DataTextField = "EmployeeTypeName"; this.ddlEmpType.DataSource = mTB_AllowanceApplyCancelBLL.GetAllEmployeeTypes(); this.ddlEmpType.DataBind(); this.ddlEmpType.Items.Insert(0, new ListItem() { Value = "0", Text = "--请选择--" }); }
private void Bind(int intCurrentIndex) { TB_AllowanceApplyCancel mTB_AllowanceApplyCancel = new TB_AllowanceApplyCancel(); TB_AllowanceApplyCancelBLL mTB_AllowanceApplyCancelBLL = new TB_AllowanceApplyCancelBLL(); Pager pager = new Pager(); pager.CurrentPageIndex = intCurrentIndex; pager.srcOrder = " ID desc"; mTB_AllowanceApplyCancel.Name = ""; mTB_AllowanceApplyCancel.SiteID = (base.UserInfo == null ? base.SystemAdminInfo.SiteID : base.UserInfo.SiteID); mTB_AllowanceApplyCancel.CardNo = this.txtScanCardNO.Text.Trim(); mTB_AllowanceApplyCancel.EmployeeNo = this.txtWorkDayNo.Text.Trim(); mTB_AllowanceApplyCancel.BU = this.txtBu.Text.Trim(); DateTime dtCheckOut = DateTime.Now; if (DateTime.TryParse(this.txtHireDate.Text.Trim(), out dtCheckOut)) { mTB_AllowanceApplyCancel.Hire_Date = dtCheckOut; } dtCheckOut = DateTime.Now; if (DateTime.TryParse(this.txtEffectiveDate.Text.Trim(), out dtCheckOut)) { mTB_AllowanceApplyCancel.Effective_Date = dtCheckOut; } if (ddlEmpType.SelectedIndex > 0) { mTB_AllowanceApplyCancel.EmployeeTypeName = ddlEmpType.SelectedItem.Text; } DataTable dt = mTB_AllowanceApplyCancelBLL.GetTable(mTB_AllowanceApplyCancel, 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_AllowanceApplyCancel Get(int intID) { DataTable dt = null; DbCommand dbCommandWrapper = null; TB_AllowanceApplyCancel mTB_AllowanceApplyCancel = null; try { string strSQL = @"select * from TB_AllowanceApplyCancel 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_AllowanceApplyCancel = new TB_AllowanceApplyCancel() { Name = Convert.ToString(dt.Rows[0]["Name"]), }; } return(mTB_AllowanceApplyCancel); } 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_AllowanceApplyCancel info, DbTransaction tran, Database db) { DbCommand dbCommandWrapper = null; string strUpdateSql = @"UPDATE TB_AllowanceApplyCancel 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; } } }
/// <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; TB_AllowanceApplyBLL mTB_AllowanceApplyBLL = new TB_AllowanceApplyBLL(); DataTable dtError = dt.Clone(); StringBuilder sbBedID = new StringBuilder();//用于保存床位ID foreach (DataRow dr in dt.Rows) { try { DataTable dtAllowanceApply = mTB_AllowanceApplyBLL.GetTableByEmployeeNo(dr["工号"].ToString()); if (dtAllowanceApply.Rows.Count == 0) { dr["BZ"] = "未申请津贴"; dtError.ImportRow(dr); } else { TB_AllowanceApplyCancel tB_AllowanceApplyCancel = new TB_AllowanceApplyCancel(); tB_AllowanceApplyCancel.EmployeeNo = dr["工号"].ToString(); tB_AllowanceApplyCancel.Name = dr["姓名"].ToString(); tB_AllowanceApplyCancel.CardNo = dr["身份证号码"].ToString(); tB_AllowanceApplyCancel.Sex = dr["性别"].ToString(); tB_AllowanceApplyCancel.Company = dr["公司"].ToString(); tB_AllowanceApplyCancel.BU = dr["事业部"].ToString(); tB_AllowanceApplyCancel.Grade = Convert.ToInt32(dr["级别"].ToString()); tB_AllowanceApplyCancel.AlloWanceApplyID = Convert.ToInt32(dtAllowanceApply.Rows[0]["ID"]); tB_AllowanceApplyCancel.EmployeeTypeName = dr["姓名"].ToString(); tB_AllowanceApplyCancel.CreateUser = currentUser; tB_AllowanceApplyCancel.CreateDate = System.DateTime.Now; tB_AllowanceApplyCancel.SiteID = intSiteID; var sdtHired = dr["入职日期"] as string; if (!string.IsNullOrEmpty(sdtHired)) { tB_AllowanceApplyCancel.Hire_Date = Convert.ToDateTime(sdtHired); } ADDAllowanceCancelApply(tB_AllowanceApplyCancel); } } catch (Exception ex) { var smsg = ex.Message; if (smsg.IndexOf("PRIMARY KEY", StringComparison.InvariantCultureIgnoreCase) != -1) { smsg = "已经有相关记录了"; } dr["BZ"] = "取消津贴失败:" + smsg; dtError.ImportRow(dr); } } return(dtError); }
public int ADDAllowanceCancelApply(TB_AllowanceApplyCancel tb_AllowanceApplyCancel) { return(_mTB_AllowanceApplyCancel.Create(tb_AllowanceApplyCancel)); }
/// <summary> /// ID查询 /// </summary> /// <param name="mTB_DormArea"></param> /// <param name="pager"></param> /// <returns></returns> public DataTable GetTableByID(TB_AllowanceApplyCancel tb_AllowanceApply, ref Pager pager) { return(_mTB_AllowanceApplyCancel.GetTable(tb_AllowanceApply, ref pager)); }
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(); } }
/// <summary> /// /// </summary> /// <param name="tb_allowanceApplyCancel"></param> /// <returns></returns> public DataTable GetTable(TB_AllowanceApplyCancel tb_allowanceApplyCancel) { DataTable dt = null; DbCommand dbCommandWrapper = null; try { string strSQL = @"select Company 公司 ,BU 事业部 ,Name 姓名 ,Sex 性别 ,EmployeeNo 工号 ,CONVERT(varchar(100), CreateDate, 23) 申请时间 ,Grade 级别 ,CONVERT(varchar(100), Hire_Date, 23) 入职日期 ,EmployeeTypeName 用工类型 ,CONVERT(varchar(100), Effective_Date, 23) 生效日期 ,BZ 备注 from TB_AllowanceApplyCancel 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_allowanceApplyCancel.SiteID); if (tb_allowanceApplyCancel.ID > 0) { strBuilder.AppendLine(" AND ID <> @ID"); db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, tb_allowanceApplyCancel.ID); } if (!string.IsNullOrEmpty(tb_allowanceApplyCancel.EmployeeNo)) { strBuilder.AppendLine(" AND EmployeeNo = @EmployeeNo"); db.AddInParameter(dbCommandWrapper, "@EmployeeNo", DbType.String, tb_allowanceApplyCancel.EmployeeNo); } if (!string.IsNullOrEmpty(tb_allowanceApplyCancel.CardNo)) { strBuilder.AppendLine(" AND CardNo = @cardNo"); db.AddInParameter(dbCommandWrapper, "@cardNo", DbType.String, tb_allowanceApplyCancel.CardNo); } if (!string.IsNullOrEmpty(tb_allowanceApplyCancel.BU)) { var spara = string.Format("%{0}%", tb_allowanceApplyCancel.BU); strBuilder.AppendLine(" AND BU like @BU"); db.AddInParameter(dbCommandWrapper, "@BU", DbType.String, spara); } if (tb_allowanceApplyCancel.Hire_Date != null) { strBuilder.AppendLine(" AND CAST(Hire_Date as Date)=@hireDate"); var sday = tb_allowanceApplyCancel.Hire_Date.Value.ToString("yyyy-MM-dd"); db.AddInParameter(dbCommandWrapper, "@hireDate", DbType.String, sday); } if (tb_allowanceApplyCancel.Effective_Date != null) { strBuilder.AppendLine(" AND CAST(Effective_Date as Date)=@effDate"); var sday = tb_allowanceApplyCancel.Effective_Date.Value.ToString("yyyy-MM-dd"); db.AddInParameter(dbCommandWrapper, "@effDate", DbType.String, sday); } if (!string.IsNullOrEmpty(tb_allowanceApplyCancel.EmployeeTypeName)) { strBuilder.AppendLine(" AND EmployeeTypeName = @empType"); db.AddInParameter(dbCommandWrapper, "@empType", DbType.String, tb_allowanceApplyCancel.EmployeeTypeName); } //strBuilder.AppendLine(" AND NAME = @NAME"); //db.AddInParameter(dbCommandWrapper, "@NAME", DbType.String, TB_AllowanceApplyCancel.Name); 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_allowanceApplyCancel"></param> /// <param name="pager"></param> /// <returns></returns> public DataTable GetTable(TB_AllowanceApplyCancel tb_allowanceApplyCancel, ref Pager pager) { DataTable dt = null; DbCommand dbCommandWrapper = null; try { string strSQL = @"select a.*,CONVERT(varchar(100), A.Hire_Date, 23) HireDate,CONVERT(varchar(100), A.CreateDate, 23) AllowanceCancelDate,CONVERT(varchar(100), B.CreateDate, 23) AllowanceApplyDate,CONVERT(varchar(100), A.Effective_Date, 23) EffectiveDate from TB_AllowanceApplyCancel AS A left join TB_AllowanceApply as B on A.AlloWanceApplyID=B.ID "; 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_allowanceApplyCancel.SiteID > 0) { strBuilder.AppendLine(" AND A.SiteID = @SiteID"); db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, tb_allowanceApplyCancel.SiteID); } if (!string.IsNullOrEmpty(tb_allowanceApplyCancel.EmployeeNo)) { strBuilder.AppendLine(" AND A.EmployeeNo = @EmployeeNo"); db.AddInParameter(dbCommandWrapper, "@EmployeeNo", DbType.String, tb_allowanceApplyCancel.EmployeeNo); } if (!string.IsNullOrEmpty(tb_allowanceApplyCancel.CardNo)) { strBuilder.AppendLine(" AND A.CardNo = @cardNo"); db.AddInParameter(dbCommandWrapper, "@cardNo", DbType.String, tb_allowanceApplyCancel.CardNo); } if (!string.IsNullOrEmpty(tb_allowanceApplyCancel.BU)) { var spara = string.Format("%{0}%", tb_allowanceApplyCancel.BU); strBuilder.AppendLine(" AND A.BU like @BU"); db.AddInParameter(dbCommandWrapper, "@BU", DbType.String, spara); } if (tb_allowanceApplyCancel.Hire_Date != null) { strBuilder.AppendLine(" AND CAST(A.Hire_Date as Date)=@hireDate"); var sday = tb_allowanceApplyCancel.Hire_Date.Value.ToString("yyyy-MM-dd"); db.AddInParameter(dbCommandWrapper, "@hireDate", DbType.String, sday); } if (tb_allowanceApplyCancel.Effective_Date != null) { strBuilder.AppendLine(" AND CAST(A.Effective_Date as Date)=@effDate"); var sday = tb_allowanceApplyCancel.Effective_Date.Value.ToString("yyyy-MM-dd"); db.AddInParameter(dbCommandWrapper, "@effDate", DbType.String, sday); } if (!string.IsNullOrEmpty(tb_allowanceApplyCancel.EmployeeTypeName)) { strBuilder.AppendLine(" AND A.EmployeeTypeName = @empType"); db.AddInParameter(dbCommandWrapper, "@empType", DbType.String, tb_allowanceApplyCancel.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; } } }