public void DataBindToPerType()
{
PerformanceTypeModel searchModel = new PerformanceTypeModel();
DataTable dtData = PerformanceQueryBus.SearchPerTypeInfo(searchModel);
this.ddlPerType.DataSource = dtData;
ddlPerType.DataTextField = "TypeName";
ddlPerType.DataValueField = "ID";
ddlPerType.DataBind();
ddlPerType.Items.Add(new System.Web.UI.WebControls.ListItem("--请选择--", "0"));
ddlPerType.SelectedValue = "0";
}
/// <summary>
/// 保存时基本信息参数设置
/// </summary>
/// <param name="comm">命令</param>
/// <param name="model">考核类型信息</param>
private static void SetSaveParameter(SqlCommand comm, PerformanceTypeModel model)
{
//设置参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeName", model.TypeName)); //类型名称
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateUser", model.CreateUser.ToString ())); //创建人
comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus)); //启用状态
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID)); //更新用户ID
if (ConstUtil.EDIT_FLAG_UPDATE.Equals(model.EditFlag))
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID)); //考核类型编号
}
}
public static bool InsertPerformanceType(PerformanceTypeModel model)
{
#region 插入SQL拼写
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine("INSERT INTO officedba.PerformanceType ");
insertSql.AppendLine(" (CompanyCD ");
insertSql.AppendLine(" ,TypeName ");
insertSql.AppendLine(" ,CreateUser ");
insertSql.AppendLine(" ,CreateDate ");
insertSql.AppendLine(" ,UsedStatus ");
insertSql.AppendLine(" ,ModifiedDate ");
insertSql.AppendLine(" ,ModifiedUserID) ");
insertSql.AppendLine(" VALUES ");
insertSql.AppendLine(" (@CompanyCD ");
insertSql.AppendLine(" ,@TypeName ");
insertSql.AppendLine(" ,@CreateUser ");
insertSql.AppendLine(" ,getdate() ");
insertSql.AppendLine(" ,@UsedStatus ");
insertSql.AppendLine(" ,getdate() ");
insertSql.AppendLine(" ,@ModifiedUserID) ");
insertSql.AppendLine(" set @ElemID= @@IDENTITY ");
#endregion
//定义插入基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = insertSql.ToString();
//设置保存的参数
SetSaveParameter(comm, model);
//添加返回参数
comm.Parameters.Add(SqlHelper.GetOutputParameter("@ElemID", SqlDbType.Int));
//执行插入操作
bool isSucc = SqlHelper.ExecuteTransWithCommand(comm);
//设置ID
model.ID = comm.Parameters["@ElemID"].Value.ToString();
//返回更新结果
return isSucc;
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
PerformanceTypeModel searchModel = new PerformanceTypeModel();
//设置查询条件
//要素名称
searchModel.TypeName = this.txtSearchElemName.Value;
//启用状态
searchModel.UsedStatus = this.sltSearchUsedStatus.Value;
searchModel.CompanyCD = companyCD;
//查询数据
//DataTable dtData = PerformanceTypeBus.SearchRectCheckElemInfo(searchModel);
////导出标题
//string headerTitle = "类型名称|启用状态";
//string[] header = headerTitle.Split('|');
////导出标题所对应的列字段名称
//string columnFiled = "TypeName|UsedStatusName";
//string[] field = columnFiled.Split('|');
//XBase.Common.OutputToExecl.ExportToTable(this.Page, dtData, header, field, "考核类型设置");
DataTable dt = PerformanceTypeBus.SearchRectCheckElemInfo(searchModel);
//导出标题
string headerTitle = "类型名称|启用状态";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "TypeName|UsedStatusName";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "考核类型设置");
}
public static DataTable SearchPerTypeInfo(PerformanceTypeModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" ID ");
searchSql.AppendLine(" ,ISNULL(TypeName, '') AS TypeName ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.PerformanceType ");
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);
}
public static bool SaveRectCheckElemInfo(PerformanceTypeModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
//设置最后修改者
model.ModifiedUserID = userInfo.UserID;
//设置创建者的编号
model.CreateUser = userInfo.EmployeeID;
//定义返回变量
bool isSucc = false;
//操作日志
LogInfoModel logModel = InitLogInfo(model.ID);
//更新
if (ConstUtil.EDIT_FLAG_UPDATE.Equals(model.EditFlag))
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//执行更新操作
isSucc = PerformanceTypeDBhelper.UpdatePerformanceType(model);
}
catch (Exception ex)
{
//输出系统日志
WriteSystemLog(userInfo, ex);
}
}
//插入
else
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//执行插入操作
isSucc = PerformanceTypeDBhelper.InsertPerformanceType(model);
logModel.ObjectID = model.ID;
}
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 SearchRectCheckElemInfo(PerformanceTypeModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询
return PerformanceTypeDBhelper.SearchCheckElemInfo(model);
}
public static DataTable SearchPerTypeInfo(PerformanceTypeModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询
return PerformanceQueryDBHelper.SearchPerTypeInfo (model);
}
public static bool UpdatePerformanceType(PerformanceTypeModel model)
{
StringBuilder updateSql = new StringBuilder();
updateSql.AppendLine(" UPDATE officedba.PerformanceType ");
updateSql.AppendLine(" SET CompanyCD = @CompanyCD ");
updateSql.AppendLine(" ,TypeName = @TypeName ");
updateSql.AppendLine(" ,UsedStatus = @UsedStatus ");
updateSql.AppendLine(" ,ModifiedDate = getdate() ");
updateSql.AppendLine(" ,ModifiedUserID = @ModifiedUserID ");
updateSql.AppendLine(" WHERE ");
updateSql.AppendLine(" ID = @ID ");
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = updateSql.ToString();
//设置保存的参数
SetSaveParameter(comm, model);
//执行插入操作并返回更新结果
return SqlHelper.ExecuteTransWithCommand(comm);
}
public static DataTable SearchCheckElemInfo(PerformanceTypeModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" ID ");
searchSql.AppendLine(" ,ISNULL(TypeName, '') AS TypeName ");
searchSql.AppendLine(" ,CASE UsedStatus ");
searchSql.AppendLine(" WHEN '0' THEN '停用' ");
searchSql.AppendLine(" WHEN '1' THEN '启用' ");
searchSql.AppendLine(" WHEN NULL THEN '' ");
searchSql.AppendLine(" ELSE '' ");
searchSql.AppendLine(" END AS UsedStatusName ,isnull( Convert(varchar(100),ModifiedDate,23),'') AS ModifiedDate ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.PerformanceType ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" CompanyCD = @CompanyCD ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//l
if (!string.IsNullOrEmpty(model.TypeName ))
{
searchSql.AppendLine(" AND TypeName LIKE @TypeName ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeName", "%"+model.TypeName+"%" ));
}
//启用状态
if (!string.IsNullOrEmpty(model.UsedStatus))
{
searchSql.AppendLine(" AND UsedStatus = @UsedStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus));
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}