protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
//从请求中获取排序列
string orderString = hidOrderBy.Value.Trim();
//排序：默认为升序
string orderBy = "asc";
//要排序的字段，如果为空，默认为"RectApplyNo"
string orderByCol = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "TemplateNo";
//降序时如果设置为降序
if (orderString.EndsWith("_d"))
{
//排序：降序
orderBy = "desc";
}
//从请求中获取当前页
//int pageIndex = Convert.ToInt32(txtToPage.Value);
////从请求中获取每页显示记录数
//int pageCount = Convert.ToInt32(txtShowPageCount.Value);
////跳过记录数
//int skipRecord = (pageIndex - 1) * pageCount;
RectCheckTemplateModel searchModel = new RectCheckTemplateModel();
//设置查询条件
//考核编号
searchModel.TemplateNo = txtTemplateNo.Value.Trim();
//主题
searchModel.Title = txtTitle.Value.Trim();
//岗位
searchModel.QuarterID = Request.Form["ddlQuarter"].ToString();
if (searchModel.Title != null)
{
int bbb = searchModel.Title.IndexOf('%');///过滤字符串
if (bbb != -1)
{
searchModel.Title = searchModel.Title.Replace('%', ' ');
}
}
string ord = orderByCol + " " + orderBy;
int TotalCount = 0;
//查询数据
DataTable dtTemp = new DataTable();
if (!string.IsNullOrEmpty(txtToPage.Value))
{
dtTemp = RectCheckTemplateBus.SearchTemplateCSInfo(searchModel, 1, 10000, ord, ref TotalCount);
}
string[,] ht = {
{ "模板编号", "TemplateNo"},
{ "主题", "Title"},
{ "岗位", "QuarterName" }
};
ExportExcel(dtTemp, ht, "", "面试评测模板列表");
}
public static DataTable SearchTemplateCSInfo(RectCheckTemplateModel model, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询
return RectCheckTemplateDBHelper.SearchTemplateCSInfo(model, pageIndex, pageCount, ord, ref TotalCount);
}
/// <summary>
/// 编辑面试评测模板信息
/// </summary>
/// <param name="model">保存信息</param>
/// <returns></returns>
public static bool SaveRectCheckTemplateInfo(RectCheckTemplateModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
//设置最后修改者
model.ModifiedUserID = userInfo.UserID;
//定义返回变量
bool isSucc = false;
//操作日志
LogInfoModel logModel = InitLogInfo(model.TemplateNo);
//更新
if (ConstUtil.EDIT_FLAG_UPDATE.Equals(model.EditFlag))
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//执行更新操作
isSucc = RectCheckTemplateDBHelper.UpdateRectTemplateInfo(model);
}
catch (Exception ex)
{
//输出系统日志
WriteSystemLog(userInfo, ex);
}
}
//插入
else
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//执行插入操作
isSucc = RectCheckTemplateDBHelper.InsertRectTemplateInfo(model);
}
catch (Exception ex)
{
//输出系统日志
WriteSystemLog(userInfo, ex);
}
}
//更新成功时
if (isSucc)
{
//设置操作成功标识
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
//更新不成功
else
{
//设置操作成功标识
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
}
//登陆日志
LogDBHelper.InsertLog(logModel);
return isSucc;
}
/// <summary>
/// 查询面试评测模板信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchRectCheckTemplateInfo(RectCheckTemplateModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询
return RectCheckTemplateDBHelper.SearchTemplateInfo(model);
}
/// <summary>
/// 保存时基本信息参数设置
/// </summary>
/// <param name="comm">命令</param>
/// <param name="model">人员信息</param>
private static void SetSaveParameter(SqlCommand comm, RectCheckTemplateModel model)
{
//设置参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));//企业代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TemplateNo", model.TemplateNo));//评测模板编号
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title));//主题
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));//岗位ID（对应岗位表ID）
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark));//备注
comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus));//启用状态
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));//更新用户ID
}
/// <summary>
/// 更新面试评测模板以及相关信息
/// </summary>
/// <param name="model">面试评测模板信息</param>
/// <returns></returns>
public static bool UpdateRectTemplateInfo(RectCheckTemplateModel model)
{
#region 更新SQL拼写
StringBuilder updateSql = new StringBuilder();
updateSql.AppendLine(" UPDATE officedba.RectCheckTemplate ");
updateSql.AppendLine(" SET Title = @Title ");
updateSql.AppendLine(" ,QuarterID = @QuarterID ");
updateSql.AppendLine(" ,Remark = @Remark ");
updateSql.AppendLine(" ,UsedStatus = @UsedStatus ");
updateSql.AppendLine(" ,ModifiedDate = getdate() ");
updateSql.AppendLine(" ,ModifiedUserID = @ModifiedUserID ");
updateSql.AppendLine(" WHERE ");
updateSql.AppendLine(" CompanyCD = @CompanyCD ");
updateSql.AppendLine(" AND TemplateNo = @TemplateNo ");
#endregion
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = updateSql.ToString();
//设置保存的参数
SetSaveParameter(comm, model);
//定义更新列表
ArrayList lstUpdate = new ArrayList();
//添加基本信息更新命令
lstUpdate.Add(comm);
//登陆或者更新要素信息
EditElemInfo(lstUpdate, model.ElemList, model.TemplateNo, model.CompanyCD);
//执行更新操作并返回更新结果
return SqlHelper.ExecuteTransWithArrayList(lstUpdate);
}
/// <summary>
/// 添加面试评测模板
/// </summary>
/// <param name="model">面试评测模板信息</param>
/// <returns></returns>
public static bool InsertRectTemplateInfo(RectCheckTemplateModel model)
{
#region 插入SQL拼写
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine(" INSERT INTO ");
insertSql.AppendLine(" officedba.RectCheckTemplate ");
insertSql.AppendLine(" (CompanyCD ");
insertSql.AppendLine(" ,TemplateNo ");
insertSql.AppendLine(" ,Title ");
insertSql.AppendLine(" ,QuarterID ");
insertSql.AppendLine(" ,Remark ");
insertSql.AppendLine(" ,UsedStatus ");
insertSql.AppendLine(" ,ModifiedDate ");
insertSql.AppendLine(" ,ModifiedUserID) ");
insertSql.AppendLine(" VALUES ");
insertSql.AppendLine(" (@CompanyCD ");
insertSql.AppendLine(" ,@TemplateNo ");
insertSql.AppendLine(" ,@Title ");
insertSql.AppendLine(" ,@QuarterID ");
insertSql.AppendLine(" ,@Remark ");
insertSql.AppendLine(" ,@UsedStatus ");
insertSql.AppendLine(" ,getdate() ");
insertSql.AppendLine(" ,@ModifiedUserID) ");
#endregion
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = insertSql.ToString();
//设置保存的参数
SetSaveParameter(comm, model);
//定义更新列表
ArrayList lstInsert = new ArrayList();
//添加基本信息更新命令
lstInsert.Add(comm);
//登陆或者更新要素信息
EditElemInfo(lstInsert, model.ElemList, model.TemplateNo, model.CompanyCD);
//执行更新操作并返回更新结果
return SqlHelper.ExecuteTransWithArrayList(lstInsert);
}
public static DataTable SearchTemplateCSInfo(RectCheckTemplateModel model, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID ");
searchSql.AppendLine(" ,A.TemplateNo ");
searchSql.AppendLine(" ,ISNULL(A.Title, '') AS Title ");
searchSql.AppendLine(" ,ISNULL(B.QuarterName, '') AS QuarterName ,isnull( Convert(varchar(100),A.ModifiedDate,23),'') AS ModifiedDate");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.RectCheckTemplate A ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter B ");
searchSql.AppendLine(" ON A.QuarterID = B.ID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//模板编号
if (!string.IsNullOrEmpty(model.TemplateNo))
{
searchSql.AppendLine(" AND A.TemplateNo LIKE '%' + @TemplateNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TemplateNo", model.TemplateNo));
}
//主题
if (!string.IsNullOrEmpty(model.Title))
{
searchSql.AppendLine(" AND A.Title LIKE '%' + @Title + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title));
}
//岗位
if (!string.IsNullOrEmpty(model.QuarterID))
{
searchSql.AppendLine(" AND A.QuarterID = @QuarterID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount); //执行查询
}
