public static DataTable GetTaskListReportPrincipal(TaskModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
SqlCommand comm = new SqlCommand();
#region SQL语句拼写
string whereStr = GetReportWhere(model, ref comm);
StringBuilder SelectAimListSqlString = new StringBuilder();
if (model.Remark == "PIC")
{
SelectAimListSqlString.Append("SELECT count(*) as Num , dbo.getEmployeeName(Principal) as PrincipalName ,Principal ");
SelectAimListSqlString.Append(" FROM officedba.Task ");
SelectAimListSqlString.Append(whereStr);
SelectAimListSqlString.Append(" GROUP BY Principal ");
}
else
{
SelectAimListSqlString.Append("SELECT * ,dbo.getEmployeeName(Principal) as PrincipalName,dbo.getDeptNameByID(DeptID) as DeptName ");
SelectAimListSqlString.Append(" FROM officedba.Task ");
SelectAimListSqlString.Append(whereStr);
}
#endregion
//定义更新基本信息的命令
comm.CommandText = SelectAimListSqlString.ToString();
DataTable dt = new DataTable();
// 执行查找操作
dt = SqlHelper.ExecuteSearch(comm);
return dt;
}
public static DataTable SelectTaskList(TaskModel model, string orderby)
{
SqlCommand comm = GetWhereString(model, orderby);
return SqlHelper.ExecuteSearch(comm);
}
public static DataTable GetTaskListReportStatus(TaskModel model)
{
DataTable dtTaskList = new DataTable();
try
{
//执行更新操作
dtTaskList = TaskListDBHelper.GetTaskListReportStatus(model);
}
catch
{
//输出日志
}
return dtTaskList;
}
public static DataTable GetTaskList(TaskModel model, string orderby)
{
DataTable dtTaskList = new DataTable();
try
{
//执行更新操作
dtTaskList = TaskListDBHelper.SelectTaskList(model,orderby);
}
catch
{
//输出日志
}
return dtTaskList;
}
public static bool CheckTask(TaskModel model)
{
bool isSucc = false;
try
{
//执行更新操作
isSucc = TaskInfoDBHelper.CheckTask(model);
}
catch
{
//输出日志
}
return isSucc;
}
public static bool UpdateTaskInfo(TaskModel model)
{
bool isSucc = false;
try
{
//执行更新操作
isSucc = TaskInfoDBHelper.UpdateTaskInfo(model);
}
catch
{
//输出日志
}
return isSucc;
}
public static int InsertTaskInfo(TaskModel model)
{
int isSucc = 0;
try
{
//执行更新操作
isSucc = TaskInfoDBHelper.AddTaskInfo(model);
}
catch
{
//输出日志
}
return isSucc;
}
private static SqlCommand GetWhereString(TaskModel model, string orderby)
{
SqlCommand comm = new SqlCommand();
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
StringBuilder sb = new StringBuilder(" select *,officedba.getEmployeeNameByID(Principal) as PrincipalName from officedba.Task ");
comm.Parameters.AddWithValue("@Principal", SqlDbType.Int);
if (model.TaskType == "1")
{
sb.Append(" where CompanyCD = '" + userInfo.CompanyCD + "' and ( (Joins like @Joins or Principal= @Principal ) and status = '2' ) ");
}
if (model.TaskType == "2")
{
sb.Append(" where TaskType = '1' and CompanyCD = '" + userInfo.CompanyCD + "' ");
}
if (model.TaskType == "3")
{
sb.Append(" where TaskType = '2' and CompanyCD = '" + userInfo.CompanyCD + "' ");
}
if (model.TaskTypeID > 0)
{
sb.Append(" and TaskTypeID=@TaskTypeID ");
comm.Parameters.AddWithValue("@TaskTypeID", SqlDbType.Int);
comm.Parameters["@TaskTypeID"].Value = model.TaskTypeID;
}
if (!string.IsNullOrEmpty(model.TaskNo) && model.TaskNo.Trim() != "")
{
sb.Append(" and TaskNo like @TaskNo ");
comm.Parameters.AddWithValue("@TaskNo", SqlDbType.VarChar);
comm.Parameters["@TaskNo"].Value = "%" + model.TaskNo.Trim() + "%";
}
if (!string.IsNullOrEmpty(model.Title))
{
sb.Append(" and Title like @Title ");
comm.Parameters.AddWithValue("@Title", SqlDbType.VarChar);
comm.Parameters["@Title"].Value = "%" + model.Title.Trim() + "%";
}
if (!string.IsNullOrEmpty(model.Critical))
{
sb.Append(" and Critical=@Critical ");
comm.Parameters.AddWithValue("@Critical", SqlDbType.VarChar);
comm.Parameters["@Critical"].Value = model.Critical;
}
if (!string.IsNullOrEmpty(model.Important))
{
sb.Append(" and Important=@Important ");
comm.Parameters.AddWithValue("@Important", SqlDbType.VarChar);
comm.Parameters["@Important"].Value = model.Important;
}
if (!string.IsNullOrEmpty(model.Priority))
{
sb.Append(" and Priority=@Priority ");
comm.Parameters.AddWithValue("@Priority", SqlDbType.VarChar);
comm.Parameters["@Priority"].Value = model.Priority;
}
if (!string.IsNullOrEmpty(model.Status))
{
sb.Append(" and Status=@Status ");
comm.Parameters.AddWithValue("@Status", SqlDbType.VarChar);
comm.Parameters["@Status"].Value = model.Status;
}
if (model.Principal > 0)
{
sb.Append(" and ( charindex('," + userInfo.EmployeeID + ",' , ','+CanViewUser+',')>0 OR Sendor = @Sendor OR charindex('," + userInfo.EmployeeName + ",' , ','+Joins+',')>0 OR Principal= " + userInfo.EmployeeID + " OR Creator=" + userInfo.EmployeeID + " OR CanViewUser='' OR CanViewUser is null) and Principal=@Principal ");
comm.Parameters["@Principal"].Value = model.Principal;
comm.Parameters.AddWithValue("@Sendor", SqlDbType.Int);
try { comm.Parameters["@Sendor"].Value = Convert.ToInt32(userInfo.EmployeeID); }
catch { comm.Parameters["@Sendor"].Value = 0; }
comm.Parameters.AddWithValue("@Creator", SqlDbType.Int);
try { comm.Parameters["@Creator"].Value = Convert.ToInt32(userInfo.EmployeeID); }
catch { comm.Parameters["@Creator"].Value = 0; }
comm.Parameters.AddWithValue("@Joins", SqlDbType.VarChar);
comm.Parameters["@Joins"].Value = "%" + model.Joins + "%";
}
else
{
sb.Append(" and ( charindex('," + userInfo.EmployeeID + ",' , ','+CanViewUser+',')>0 OR Sendor = @Sendor OR charindex('," + userInfo.EmployeeName + ",' , ','+Joins+',')>0 OR Principal= " + userInfo.EmployeeID + " OR Creator=" + userInfo.EmployeeID + " OR CanViewUser='' OR CanViewUser is null) ");
comm.Parameters["@Principal"].Value = userInfo.EmployeeID;
comm.Parameters.AddWithValue("@Sendor", SqlDbType.Int);
try { comm.Parameters["@Sendor"].Value = Convert.ToInt32(userInfo.EmployeeID); }
catch { comm.Parameters["@Sendor"].Value = 0; }
comm.Parameters.AddWithValue("@Creator", SqlDbType.Int);
try { comm.Parameters["@Creator"].Value = Convert.ToInt32(userInfo.EmployeeID); }
catch { comm.Parameters["@Creator"].Value = 0; }
comm.Parameters.AddWithValue("@Joins", SqlDbType.VarChar);
comm.Parameters["@Joins"].Value = "%" + model.Joins + "%";
}
sb.Append(" " + orderby + " ");
comm.CommandText = sb.ToString();
return comm;
}
public static string GetReportWhere(TaskModel model, ref SqlCommand com)
{
SqlCommand comm = new SqlCommand();
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
StringBuilder sb = new StringBuilder();
comm.Parameters.AddWithValue("@Principal", SqlDbType.Int);
comm.Parameters["@Principal"].Value = model.Principal;
sb.Append(" where CompanyCD = '" + userInfo.CompanyCD + "' ");
if (model.TaskTypeID > 0)
{
sb.Append(" and TaskTypeID=@TaskTypeID ");
comm.Parameters.AddWithValue("@TaskTypeID", SqlDbType.Int);
comm.Parameters["@TaskTypeID"].Value = model.TaskTypeID;
}
if (!string.IsNullOrEmpty(model.TaskNo) && model.TaskNo.Trim() != "")
{
sb.Append(" and TaskNo like @TaskNo ");
comm.Parameters.AddWithValue("@TaskNo", SqlDbType.VarChar);
comm.Parameters["@TaskNo"].Value = "%" + model.TaskNo.Trim() + "%";
}
if (!string.IsNullOrEmpty(model.Title))
{
sb.Append(" and Title like @Title ");
comm.Parameters.AddWithValue("@Title", SqlDbType.VarChar);
comm.Parameters["@Title"].Value = "%" + model.Title.Trim() + "%";
}
if (!string.IsNullOrEmpty(model.Critical))
{
sb.Append(" and Critical=@Critical ");
comm.Parameters.AddWithValue("@Critical", SqlDbType.VarChar);
comm.Parameters["@Critical"].Value = model.Critical;
}
if (!string.IsNullOrEmpty(model.Important))
{
sb.Append(" and Important=@Important ");
comm.Parameters.AddWithValue("@Important", SqlDbType.VarChar);
comm.Parameters["@Important"].Value = model.Important;
}
if (!string.IsNullOrEmpty(model.Priority))
{
sb.Append(" and Priority=@Priority ");
comm.Parameters.AddWithValue("@Priority", SqlDbType.VarChar);
comm.Parameters["@Priority"].Value = model.Priority;
}
if (!string.IsNullOrEmpty(model.Status))
{
sb.Append(" and Status=@Status ");
comm.Parameters.AddWithValue("@Status", SqlDbType.VarChar);
comm.Parameters["@Status"].Value = model.Status;
}
if (model.SendDate + "" != "")
{
sb.Append(" and CompleteDate>= @SendDate ");
comm.Parameters.AddWithValue("@SendDate", SqlDbType.DateTime);
comm.Parameters["@SendDate"].Value = model.SendDate;
}
if (model.ReportDate + "" != "")
{
sb.Append(" and CompleteDate<= @ReportDate ");
comm.Parameters.AddWithValue("@ReportDate", SqlDbType.DateTime);
comm.Parameters["@ReportDate"].Value = model.ReportDate.AddDays(1);
}
if (model.CheckUserID > 0)
{
if (model.CheckNote == "1")
{
sb.Append(" AND ");
sb.Append(" DeptID = @SearchID ");
}
else
{
sb.Append(" AND ");
sb.Append(" Principal=@SearchID ");
}
comm.Parameters.AddWithValue("@SearchID", SqlDbType.Int);
comm.Parameters["@SearchID"].Value = model.CheckUserID;
}
if (model.Principal > 0)
{
sb.Append(" and ( charindex('," + userInfo.EmployeeID + ",' , ','+CanViewUser+',')>0 OR Sendor = @Sendor OR charindex('," + userInfo.EmployeeID + ",' , ','+Joins+',')>0 OR Principal= " + userInfo.EmployeeID + " OR Creator=" + userInfo.EmployeeID + " OR CanViewUser='' OR CanViewUser is null) and Principal=@Principal ");
comm.Parameters.AddWithValue("@Sendor", SqlDbType.Int);
try { comm.Parameters["@Sendor"].Value = Convert.ToInt32(model.Joins); }
catch { comm.Parameters["@Sendor"].Value = 0; }
comm.Parameters.AddWithValue("@Creator", SqlDbType.Int);
try { comm.Parameters["@Creator"].Value = Convert.ToInt32(model.Joins); }
catch { comm.Parameters["@Creator"].Value = 0; }
comm.Parameters.AddWithValue("@Joins", SqlDbType.VarChar);
comm.Parameters["@Joins"].Value = "%" + model.Joins + "%";
}
else
{
sb.Append(" and ( charindex('," + userInfo.EmployeeID + ",' , ','+CanViewUser+',')>0 OR Sendor = @Sendor OR charindex('," + userInfo.EmployeeID + ",' , ','+Joins+',')>0 OR Principal= " + userInfo.EmployeeID + " OR Creator=" + userInfo.EmployeeID + " OR CanViewUser='' OR CanViewUser is null) ");
comm.Parameters.AddWithValue("@Sendor", SqlDbType.Int);
try { comm.Parameters["@Sendor"].Value = Convert.ToInt32(model.Joins); }
catch { comm.Parameters["@Sendor"].Value = 0; }
comm.Parameters.AddWithValue("@Creator", SqlDbType.Int);
try { comm.Parameters["@Creator"].Value = Convert.ToInt32(model.Joins); }
catch { comm.Parameters["@Creator"].Value = 0; }
comm.Parameters.AddWithValue("@Joins", SqlDbType.VarChar);
comm.Parameters["@Joins"].Value = "%" + model.Joins + "%";
}
com = comm;
return sb.ToString();
}