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;
}