/// <summary>
/// 查询所有
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pairs"></param>
/// <param name="pagination"></param>
/// <returns></returns>
public IEnumerable <T> QryRei <T>(Dictionary <string, object> pairs, Pagination pagination)
{
WhereBuilder builder = new WhereBuilder();
builder.FromSql = "tbReimbursement";
builder.AddWhereAndParameter(pairs, "ID");
builder.AddWhereAndParameter(pairs, "Department");
builder.AddWhereAndParameter(pairs, "Applicant");
return(SortAndPage <T>(builder, pagination));
}
/// <summary>
/// 查询真实姓名
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ids"></param>
/// <returns></returns>
public IEnumerable <T> QryRealName <T>(Dictionary <string, object> paras)
{
string sql = "SELECT AccountName,RealName FROM tbUser";
WhereBuilder builder = new WhereBuilder();
if (paras != null)
{
builder.AddWhereAndParameter(paras, "names", "AccountName", "IN");
builder.AddWhereAndParameter(paras, "DepartmentId");
}
return(QueryList <T>(sql, builder));
}
/// <summary>
/// 查询成员
/// </summary>
/// <returns></returns>
public IEnumerable <T> GetTeams <T>(Dictionary <string, object> paras)
{
string sql = "SELECT * FROM tbTeam";
WhereBuilder builder = new WhereBuilder();
if (paras != null)
{
builder.AddWhereAndParameter(paras, "projectId");
builder.AddWhereAndParameter(paras, "taskId");
}
return(QueryList <T>(sql, builder));
}
/// <summary>
/// 根据用户查询任务
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pagination"></param>
/// <param name="paras"></param>
/// <returns></returns>
public IEnumerable <T> QryTask <T>(Pagination pagination, Dictionary <string, object> paras)
{
if (pagination.sidx == null || pagination.sidx == "")
{
pagination.sidx = "ProjectId";
}
bool boo = pagination.sidx.IndexOf("ProjectId", StringComparison.OrdinalIgnoreCase) >= 0;
if (!boo)
{
pagination.sidx = "ProjectId," + pagination.sidx;
}
WhereBuilder builder = new WhereBuilder();
builder.FromSql = "fun_userTask(@userName)";
builder.AddParameter("userName", paras["userName"]);
builder.AddWhereAndParameter(paras, "ProjectId");
int tag = int.Parse(paras["qryTag"].ToString());
if (tag == 1)
{
builder.AddWhere(" CreateBy = @userName");
}
if (tag == 2)
{
builder.AddWhere(" FinishBy = @userName");
}
return(SortAndPage <T>(builder, pagination));
}
/// <summary>
/// 查询选单
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="paras"></param>
/// <returns></returns>
public IEnumerable <T> MenuGetList <T>(Dictionary <string, object> paras)
{
string sql = "SELECT * FROM tbMenu";
if (paras != null)
{
WhereBuilder builder = new WhereBuilder();
builder.AddWhereAndParameter(paras, "id");
builder.AddWhereAndParameter(paras, "noState", "state", "!=");
if (builder.Wheres.Count > 0)
{
sql += " WHERE " + String.Join(" and ", builder.Wheres);
}
}
sql += " ORDER BY sortvalue DESC";
return(QueryList <T>(sql, paras));
}
/// <summary>
/// 获得公用数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="paras"></param>
/// <returns></returns>
public IEnumerable <T> LibraryGetList <T>(Dictionary <string, object> paras)
{
string sql = "SELECT * FROM tbLibrary";
if (paras != null)
{
WhereBuilder builder = new WhereBuilder();
builder.AddWhereAndParameter(paras, "id");
builder.AddWhereAndParameter(paras, "tag");
if (builder.Wheres.Count > 0)
{
sql += " WHERE " + String.Join(" and ", builder.Wheres);
}
sql += " ORDER BY sort DESC";
}
return(QueryList <T>(sql, paras));
}
/// <summary>
/// 获取成员在各项目中工时详情
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ProName">项目名称</param>
/// <returns></returns>
public IEnumerable <T> GetProjectHoursByPerson <T>(Dictionary <string, object> paras)
{
string sql = "SELECT PID ProjectId, PName TaskId, RealName Person ,Person PersonName ,SUM(Consume) Hour ,MAX(FinishTime) FinishTime FROM v_hour_statistics";
if (paras != null)
{
WhereBuilder builder = new WhereBuilder();
builder.AddWhereAndParameter(paras, "PerName", "Person");
builder.AddWhereAndParameter(paras, "StartTime", "FinishTime", ">=");
builder.AddWhereAndParameter(paras, "EndTime", "FinishTime", "<=");
if (builder.Wheres.Count > 0)
{
sql += " WHERE " + String.Join(" AND ", builder.Wheres);
}
}
sql += " GROUP BY PName, RealName,Person, PID";
return(QueryList <T>(sql, paras));
}
/// <summary>
/// 获取对应项目中成员的任务工时详情
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="paras"></param>
/// <returns></returns>
public IEnumerable <T> GetTaskByPerAndPro <T>(Dictionary <string, object> paras)
{
string sql = "SELECT PName ProjectId, TName TaskId, RealName Person,Person PersonName,Consume Hour,FinishTime FinishTime from v_hour_statistics ";
if (paras != null)
{
WhereBuilder builder = new WhereBuilder();
builder.AddWhereAndParameter(paras, "ProName", "PID");
builder.AddWhereAndParameter(paras, "PerName", "Person");
builder.AddWhereAndParameter(paras, "StartTime", "FinishTime", ">=");
builder.AddWhereAndParameter(paras, "EndTime", "FinishTime", "<=");
if (builder.Wheres.Count > 0)
{
sql += " WHERE " + String.Join(" AND ", builder.Wheres);
}
}
sql += " AND TID is not null";
return(QueryList <T>(sql, paras));
}
/// <summary>
/// 查询用户列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="paras"></param>
/// <param name="iCount"></param>
/// <returns></returns>
public IEnumerable <T> QryUsers <T>(Dictionary <string, object> paras, out int iCount)
{
iCount = 0;
WhereBuilder builder = new WhereBuilder();
builder.FromSql = "v_user_list";
GridData grid = new GridData()
{
PageIndex = Convert.ToInt32(paras["pi"]),
PageSize = Convert.ToInt32(paras["pageSize"]),
SortField = paras["sort"].ToString(),
SortDirection = paras["order"].ToString()
};
builder.AddWhereAndParameter(paras, "userid", "AccountName", "LIKE", "'%'+@userid+'%'");
builder.AddWhereAndParameter(paras, "username", "RealName", "LIKE", "'%'+@username+'%'");
builder.AddWhereAndParameter(paras, "IsAble");
builder.AddWhereAndParameter(paras, "IfChangePwd");
builder.AddWhereAndParameter(paras, "RoleID");
builder.AddWhereAndParameter(paras, "adddatestart", "CreateTime", ">");
builder.AddWhereAndParameter(paras, "adddateend", "CreateTime", "<");
return(SortAndPage <T>(builder, grid, out iCount));
}
