protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
//获取数据
PerformanceTemplateModel searchModel = new PerformanceTemplateModel();
searchModel.TypeID = selSearchPerformanceType .Value ;
searchModel.Title = inpSearchTitle.Value .Trim ();
//设置查询条件
//要素名称
/// searchModel.ElemName = context.Request.QueryString["ElemName"];
//启用状态
/// searchModel.UsedStatus = context.Request.QueryString["UsedStatus"];
//查询数据
DataTable dt = PerformanceTemplateBus.SearchFlowInfo(searchModel);
//导出标题
string headerTitle = "模板主题|考核类型|创建人|创建日期";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "Title|TypeName|CreaterName|CreateDate";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "考核模板设置");
}
/// <summary>
/// 获取模板信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable SearchCheckElemInfo(PerformanceTemplateModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" TemplateNo ");
searchSql.AppendLine(" ,ISNULL(Title, '') AS Title ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.PerformanceTemplate ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" CompanyCD = @CompanyCD ");
searchSql.AppendLine(" and UsedStatus = @UsedStatus ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD ));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", "1"));
//l
//启用状态
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 根据模板编号获取模板信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetPerformanceElemInfo(PerformanceTemplateModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("select a.ID,a.TemplateNo as TemplateNo,ISNULL(a.Title,'') as Title,ISNULL(c.TypeName,'') as TypeName,ISNULL(a.Description,'') as Description,a.UsedStatus,b.ElemID,b.ElemOrder,b.Rate,ISNULL(d.ElemName,'') as ElemName,a.typeID,isnull(e.ElemName,'') as ParentName,e.ID as ParentID,CONVERT(VARCHAR(10),a.CreateDate,21) AS CreateDate from officedba.PerformanceTemplate a right outer join officedba.PerformanceTemplateElem b on b.CompanyCD=a.CompanyCD and a.TemplateNo=b.TemplateNo left outer join officedba.PerformanceType c on c.CompanyCD=a.CompanyCD and a.TypeID=c.ID left outer join officedba.PerformanceElem d on d.CompanyCD=a.CompanyCD and b.ElemID=d.ID left outer join officedba.PerformanceElem e on e.CompanyCD=a.CompanyCD and d.ParentElemNo=e.ID where a.CompanyCD = @CompanyCD and a.TemplateNo=@TemplateNo order by e.ID");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TemplateNo", model.TemplateNo));
//l
//启用状态
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 检索（根据模板的名称和模板ID为条件）
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable SearchTemplateInfo(PerformanceTemplateModel model)
{
//ISNULL(TypeName, '') AS TypeName
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("select a.ID,ISNULL(a.TemplateNo,'') AS TemplateNo,ISNULL(a.Title,'') AS Title,ISNULL(c.TypeName,'') AS TypeName,ISNULL(a.Description,'') as Description,ISNULL(a.UsedStatus,'') as UsedStatus,ISNULL(b.EmployeeName,'') as CreaterName,ISNULL( CONVERT(VARCHAR(10), a.CreateDate,21),'') as CreateDate, isnull( Convert(varchar(100),a.ModifiedDate,23),'') AS ModifiedDate from officedba.PerformanceTemplate a left outer join officedba.EmployeeInfo b on b.CompanyCD=a.CompanyCD and a.Creator=b.ID left outer join officedba.PerformanceType c on c.CompanyCD=a.CompanyCD and a.TypeID=c.ID where a.CompanyCD = @CompanyCD ");
if (!string.IsNullOrEmpty(model.TypeID))
{
searchSql.AppendLine("and a.TypeID=@TypeID");
}
if (!string.IsNullOrEmpty(model.Title))
{
searchSql.AppendLine("and a.title like @title");
}
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
if (!string.IsNullOrEmpty(model.TypeID))
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", model.TypeID));
}
if (!string.IsNullOrEmpty(model.Title))
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@title", "%" + model.Title + "%"));
}
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//l
//启用状态
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 插入一条模板信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertPerformenceTemplate(PerformanceTemplateModel model)
{
#region 插入SQL拼写
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine("INSERT INTO officedba. PerformanceTemplate ");
insertSql.AppendLine(" (CompanyCD ");
insertSql.AppendLine(" ,TemplateNo ");
insertSql.AppendLine(" ,Title ");
insertSql.AppendLine(" ,TypeID ");
insertSql.AppendLine(" ,Description ");
insertSql.AppendLine(" ,UsedStatus ");
insertSql.AppendLine(" ,Creator ");
insertSql.AppendLine(" ,CreateDate) ");
insertSql.AppendLine(" VALUES ");
insertSql.AppendLine(" (@CompanyCD ");
insertSql.AppendLine(" ,@TemplateNo ");
insertSql.AppendLine(" ,@Title ");
insertSql.AppendLine(" ,@TypeID ");
insertSql.AppendLine(" ,@Description ");
insertSql.AppendLine(" ,@UsedStatus ");
insertSql.AppendLine(" ,@Creator ");
insertSql.AppendLine(" ,getdate() ) ");
// insertSql.AppendLine(" set @ElemID= @@IDENTITY ");
#endregion
//定义插入基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = insertSql.ToString();
//设置保存的参数
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("@TypeID", model.TypeID)); //启用状态
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Description", model.Description)); //更新用户ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus)); //更新用户ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator)); //更新用户ID
//添加返回参数
// comm.Parameters.Add(SqlHelper.GetOutputParameter("@ElemID", SqlDbType.Int));
//执行插入操作
bool isSucc = SqlHelper.ExecuteTransWithCommand(comm);
if (isSucc)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 删除指标信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool DeletePerformenceTemElem(PerformanceTemplateModel model)
{
#region 插入SQL拼写
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine("delete from officedba.PerformanceTemplateElem where CompanyCD=@CompanyCD and TemplateNo=@TemplateNo ");
// insertSql.AppendLine(" set @ElemID= @@IDENTITY ");
#endregion
//定义插入基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = insertSql.ToString();
//设置保存的参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TemplateNo", model.TemplateNo)); //类型名称
//添加返回参数
// comm.Parameters.Add(SqlHelper.GetOutputParameter("@ElemID", SqlDbType.Int));
//执行插入操作
bool isSucc = SqlHelper.ExecuteTransWithCommand(comm);
if (isSucc)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 更新指标库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool UpdatePerformenceTemplate(PerformanceTemplateModel model)
{
#region 插入SQL拼写
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine("update officedba.PerformanceTemplate set Title=@Title,TypeID=@TypeID,Description=@Description,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID,UsedStatus=@UsedStatus where CompanyCD=@CompanyCD and TemplateNo=@TemplateNo");
// insertSql.AppendLine(" set @ElemID= @@IDENTITY ");
#endregion
//定义插入基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = insertSql.ToString();
//设置保存的参数
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("@TypeID", model.TypeID)); //启用状态
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Description", model.Description)); //更新用户ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus)); //更新用户ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID)); //更新用户ID
//添加返回参数
// comm.Parameters.Add(SqlHelper.GetOutputParameter("@ElemID", SqlDbType.Int));
//执行插入操作
bool isSucc = SqlHelper.ExecuteTransWithCommand(comm);
if (isSucc)
{
return true;
}
else
{
return false;
}
}
public static DataTable SearchRectCheckElemInfo(PerformanceTemplateModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询
return PerformanceTemplateEmpDBHelper.SearchCheckElemInfo(model);
}
public static bool InsertPerformenceTemplate(IList<PerformanceTemplateElemModel> modeList, PerformanceTemplateModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
bool isSucc = false;
//操作日志
LogInfoModel logModel = InitLogInfo(modeList[0].ModifiedUserID);
//更新
if (ConstUtil.EDIT_FLAG_UPDATE.Equals(modeList[0].EditFlag))
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//执行更新操作
// isSucc = PerformanceTemplateEmpDBHelper.UpdatePerformenceTempElm(modeList, templateList);
if (PerformanceTemplateDBHelper.UpdatePerformenceTemplate(model))
{
if (PerformanceTemplateDBHelper.DeletePerformenceTemElem(model))
{
if (PerformanceTemplateDBHelper.InsertPerformenceTempElm(modeList))
{
isSucc = true;
}
else
{
isSucc = false;
}
}
else
{
isSucc = false;
}
}
else
{
isSucc = false;
}
logModel.ObjectID = modeList[0].ModifiedUserID;
}
catch (Exception ex)
{
//输出系统日志
WriteSystemLog(userInfo, ex);
}
}
//插入
else
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//执行插入操作
if (PerformanceTemplateDBHelper.InsertPerformenceTemplate(model))
{
if (PerformanceTemplateDBHelper.InsertPerformenceTempElm(modeList))
{
isSucc = true;
}
else
{
isSucc = false;
}
}
else
{
isSucc = false;
}
logModel.ObjectID = modeList[0].ModifiedUserID;
}
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;
}