public static bool DoInsert(string CompanyCD, string ModefiedUserID) { ArrayList Commlst = new ArrayList(); string strDele = "Delete from officedba. InputPerformanceRoyalty where CompanyCD='" + CompanyCD + "'"; //先把所有当前公司的记录都删除 SqlCommand commdele = new SqlCommand(strDele); Commlst.Add(commdele); DataTable dt = GetBaseMoneyByInfo(CompanyCD); foreach (DataRow dr in dt.Rows) { InputPerformanceRoyaltyModel model = new InputPerformanceRoyaltyModel(); model.EmployeeID = dr["EmployeeID"].ToString(); model.TaskNo = dr["TaskNo"].ToString(); model.CompanyCD = CompanyCD; model.TaskFlag = dr["TaskFlag"].ToString(); model.BaseMoney = dr["BaseMoney"].ToString(); model.PerformanceMoney = dr["PerformanceMoney"].ToString(); model.Confficent = dr["Confficent"].ToString(); model.ModifiedUserID = ModefiedUserID; SqlCommand comm = Insertcomm(model); Commlst.Add(comm); } return SqlHelper.ExecuteTransWithArrayList(Commlst); }
public static DataTable GetInfo(InputPerformanceRoyaltyModel model, int pageIndex, int pageCount, string ord, ref int TotalCount) { //获取登陆用户信息 //UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; try { return InputPerformanceRoyaltyDBHelper.GetInfo(model, pageIndex, pageCount, ord, ref TotalCount); } catch (Exception) { throw; } }
/// <summary> /// 更新一条记录 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool Update(InputPerformanceRoyaltyModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //定义返回变量 bool isSucc = false; /* * 定义日志内容变量 * 增删改相关的日志,需要输出操作日志,该类型日志插入到数据库 * 其他的 如出现异常时,需要输出系统日志,该类型日志保存到日志文件 */ try { isSucc = InputPerformanceRoyaltyDBHelper.Update(model); } catch (Exception ex) { //输出日志 WriteSystemLog(userInfo, ex); } string remark; //成功时 if (isSucc) { //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } else { //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } //操作日志 LogInfoModel logModel = InitLogInfo(model.ID); //涉及关键元素 这个需要根据每个页面具体设置,本页面暂时设置为空 logModel.Element = ConstUtil.LOG_PROCESS_UPDATE; //设置操作成功标识 logModel.Remark = remark; //登陆日志 LogDBHelper.InsertLog(logModel); return isSucc; }
public static DataTable GetInfo(InputPerformanceRoyaltyModel model, int pageIndex, int pageCount, string ord, ref int TotalCount) { StringBuilder strSql = new StringBuilder(); strSql.AppendLine("select a.ID,c.EmployeeName "); strSql.AppendLine(",a.TaskNo "); strSql.AppendLine(",b.Title "); strSql.AppendLine(",case b.TaskFlag when '1' then '月考核' when '2' then '季考核' "); strSql.AppendLine(" when '3' then '半年考核' when '4' then '年考核' end as TaskFlag "); strSql.AppendLine(",b.TaskNum "); strSql.AppendLine(",Convert(varchar(10),b.StartDate,21) as StartDate "); strSql.AppendLine(",Convert(varchar(10),b.EndDate,21) as EndDate "); strSql.AppendLine(",a.BaseMoney "); strSql.AppendLine(",a.Confficent "); strSql.AppendLine(",a.PerformanceMoney "); strSql.AppendLine("from officedba. InputPerformanceRoyalty a "); strSql.AppendLine("inner join officedba. PerformanceTask b on a.CompanyCD=b.CompanyCD and a.TaskNo=b.TaskNo "); strSql.AppendLine("inner join officedba.EmployeeInfo c on c.ID=a.EmployeeID "); strSql.AppendLine("where a.CompanyCD=@CompanyCD "); //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); if (!string.IsNullOrEmpty(model.TaskFlag)) { strSql.AppendLine(" and a.TaskFlag =@TaskFlag"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskFlag", model.TaskFlag)); } if (!string.IsNullOrEmpty(model.EmployeeID)) { strSql.AppendLine(" and a.EmployeeID = @EmployeeID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", model.EmployeeID)); } comm.CommandText = strSql.ToString(); return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount); }
/// <summary> /// 保存时基本信息参数设置 /// </summary> /// <param name="comm"></param> /// <param name="model"></param> private static void SetSaveParameter(SqlCommand comm, InputPerformanceRoyaltyModel model) { if (!string.IsNullOrEmpty(model.ID) && model.ID != "0") { comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID ", model.ID));//自动生成 } comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID ", model.EmployeeID));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskNo ", model.TaskNo));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD ", model.CompanyCD));// //comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate ", model.StartDate));// //comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate ", model.EndDate));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskFlag ", model.TaskFlag));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@BaseMoney ", model.BaseMoney));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@PerformanceMoney ", model.PerformanceMoney));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confficent ", model.Confficent));// comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID ", model.ModifiedUserID));// }
/// <summary> /// 更新一条记录 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool Update(InputPerformanceRoyaltyModel model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update officedba.InputPerformanceRoyalty set "); strSql.Append("EmployeeID=@EmployeeID,"); strSql.Append("TaskNo=@TaskNo,"); strSql.Append("CompanyCD=@CompanyCD,"); strSql.Append("taskNum=@taskNum,"); strSql.Append("StartDate=@StartDate,"); strSql.Append("EndDate=@EndDate,"); strSql.Append("TaskFlag=@TaskFlag,"); strSql.Append("BaseMoney=@BaseMoney,"); strSql.Append("PerformanceMoney=@PerformanceMoney,"); strSql.Append("Confficent=@Confficent,"); strSql.Append("ModifiedUserID=@ModifiedUserID,"); strSql.Append("CreateTime=@CreateTime,"); strSql.Append("ModifiedDate=@ModifiedDate"); strSql.Append(" where ID=@ID "); SqlCommand comm = new SqlCommand(strSql.ToString()); SetSaveParameter(comm, model); return SqlHelper.ExecuteTransWithCommand(comm); }
/// <summary> /// 返回一个插入的SqlCommand /// </summary> /// <param name="model"></param> /// <returns></returns> private static SqlCommand Insertcomm(InputPerformanceRoyaltyModel model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into officedba.InputPerformanceRoyalty("); strSql.Append("EmployeeID,TaskNo,CompanyCD,TaskFlag,BaseMoney,PerformanceMoney,Confficent,ModifiedUserID,CreateTime,ModifiedDate)"); strSql.Append(" values ("); strSql.Append("@EmployeeID,@TaskNo,@CompanyCD,@TaskFlag,@BaseMoney,@PerformanceMoney,@Confficent,@ModifiedUserID,getdate(),getdate())"); strSql.Append(";set @IndexID = @@IDENTITY"); SqlCommand comm = new SqlCommand(); comm.CommandText = strSql.ToString(); SqlParameter IndexID = new SqlParameter("@IndexID", SqlDbType.Int); IndexID.Direction = ParameterDirection.Output; comm.Parameters.Add(IndexID); SetSaveParameter(comm, model); return comm; }