/// Return a list for admin UI list.
/// </summary>
/// <param name="total"></param>
/// <param name="current"></param>
/// <param name="rowCount"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public static List <CroResource> TolerantSearch(out int total, int current, int rowCount, APSqlWherePhrase where, APSqlOrderPhrase order)
{
var t = APDBDef.CroResource;
var u = APDBDef.ResUser;
var query = APQuery
.select(t.CrosourceId, t.Title, t.CreatedTime, t.StatePKID, t.ThemeId,
t.ProvinceId, t.AreaId, t.WinLevelPKID, t.Score, t.DeliveryStatus,
t.Author)
.from(t, u.JoinInner(t.Creator == u.UserId))
.where (where);
//.order_by(t.CrosourceId.Desc)
if (order != null)
{
query.order_by_add(order);
}
else
{
query.order_by_add(t.CrosourceId.Desc);
}
query.primary(t.CrosourceId)
.skip((current - 1) * rowCount)
.take(rowCount);
using (APDBDef db = new APDBDef())
{
total = db.ExecuteSizeOfSelect(query);
return(db.Query(query, t.TolerantMap).ToList());
}
}
public List <CroResourceMedal> MyMedals(long id, out int total, int take, int skip = 0)
{
var cr = APDBDef.CroResource;
var m = APDBDef.CroResourceMedal;
var f = APDBDef.Files;
var currentActive = ResSettings.SettingsInSession.Actives[0];
var userid = id;
var query = APQuery.select(m.ResourceMedalId, m.CreateDate,
cr.CrosourceId.As("CrosourceId"), cr.Title, cr.Author, cr.WinLevelPKID,
f.FilePath)
.from(m,
cr.JoinInner(m.CrosourceId == cr.CrosourceId),
f.JoinInner(f.FileId == m.FileId))
.where (cr.Creator == userid & m.ActiveId == currentActive.ActiveId & cr.WinLevelPKID > 0)
.order_by(cr.CreatedTime.Desc)
.primary(m.ResourceMedalId)
.take(take)
.skip(skip);
total = db.ExecuteSizeOfSelect(query);
return(db.Query(query, r => new CroResourceMedal
{
ResourceMedalId = m.ResourceMedalId.GetValue(r),
CrosourceId = m.CrosourceId.GetValue(r, "CrosourceId"),
Title = cr.Title.GetValue(r),
Author = cr.Author.GetValue(r),
FilePath = f.FilePath.GetValue(r),
ActiveName = currentActive.ActiveName,
CreateDate = m.CreateDate.GetValue(r),
WinLevel = CroResourceHelper.DictWinLevel[cr.WinLevelPKID.GetValue(r)]
}).ToList());
}
/// <summary>
/// Return a list for admin UI list.
/// </summary>
/// <param name="total"></param>
/// <param name="current"></param>
/// <param name="rowCount"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public static List <CroResource> TolerantSearch(out int total, int current, int rowCount, APSqlWherePhrase where, APSqlOrderPhrase order)
{
var t = APDBDef.CroResource;
var u = APDBDef.ResUser;
var query = APQuery
.select(t.CrosourceId, t.Title, u.RealName.As("Author") //TODO:t.MediumTypePKID
, t.CreatedTime, t.StatePKID)
.from(t, u.JoinInner(t.Creator == u.UserId))
.where (where)
.primary(t.CrosourceId)
.skip((current - 1) * rowCount)
.take(rowCount);
if (order != null)
{
query.order_by(order);
}
using (APDBDef db = new APDBDef())
{
total = db.ExecuteSizeOfSelect(query);
return(db.Query(query, t.TolerantMap).ToList());
}
}
private void CheckCurrent()
{
if (!HttpContext.Current.Request.IsAuthenticated)
{
throw new NotSupportedException(
"本系统的 SessionSettings 不支持非登录用户采样，请确保你书写的代码段不存在非登录用户访问的漏洞！");
}
if (_user == null)
{
var u = APDBDef.ResUser;
var r = APDBDef.ResRole;
var ur = APDBDef.ResUserRole;
var ra = APDBDef.ResRoleApprove;
using (APDBDef db = new APDBDef())
{
_user = db.ResUserDal.ConditionQuery(u.UserName == HttpContext.Current.User.Identity.Name, null, null, null).FirstOrDefault();
if (_user != null)
{
_role = APQuery.select(r.Asterisk)
.from(r, ur.JoinInner(r.RoleId == ur.RoleId))
.where (ur.UserId == _user.UserId)
.query(db, r.Map).FirstOrDefault();
if (_role != null)
{
_approves = APQuery.select(ra.ApproveId).from(ra).where (ra.RoleId == _role.RoleId)
.query(db, reader => { return(ra.ApproveId.GetValue(reader)); }).ToArray();
}
}
}
}
}
//public List<ResActiveUser> HomeActiveUserList(out int total, int take, int skip = 0)
//{
// var t = APDBDef.ResUser;
// var t1 = APDBDef.ResFavorite;
// var query = APQuery.select(t.UserId, t.RealName, t.GenderPKID, t.PhotoPath, t1.UserId.Count().As("ViewCount"))
// .from(t, t1.JoinInner(t.UserId == t1.UserId))
// .where(t.Actived == true)
// .group_by(t.UserId, t.RealName, t.GenderPKID, t.PhotoPath)
// .order_by(new APSqlOrderPhrase(t1.UserId.Count(), APSqlOrderAccording.Desc))
// .primary(t.UserId)
// .take(take);
// if (skip != -1)
// {
// query.skip(skip);
// total = db.ExecuteSizeOfSelect(query);
// }
// else
// {
// total = 0;
// }
// return db.Query(query, reader =>
// {
// return new ResActiveUser()
// {
// UserId = t.UserId.GetValue(reader),
// RealName = t.RealName.GetValue(reader),
// GenderPKID = t.GenderPKID.GetValue(reader),
// PhotoPath = t.PhotoPath.GetValue(reader),
// ViewCount = reader.GetInt32(reader.GetOrdinal("ViewCount")),
// };
// }).ToList();
//}
#endregion
#region [ 活动公告短查询 ]
public List <CroBulletin> HomeCroBulltinList(APSqlOrderPhrase order, out int total, int take, int skip = 0)
{
var t = APDBDef.CroBulletin;
var query = APQuery.select(t.BulletinId, t.Title, t.Content, t.CreatedTime)
.from(t)
.order_by(order)
.primary(t.BulletinId)
.take(take);
if (skip != -1)
{
query.skip(skip);
total = db.ExecuteSizeOfSelect(query);
}
else
{
total = 0;
}
return(db.Query(query, reader =>
{
return new CroBulletin()
{
BulletinId = t.BulletinId.GetValue(reader),
Title = t.Title.GetValue(reader),
Content = t.Content.GetValue(reader),
CreatedTime = t.CreatedTime.GetValue(reader),
};
}).ToList());
}
public static List <ResCompany> GetTree(string path, bool isShowRoot = true)
{
var t = APDBDef.ResCompany;
var query = APQuery.select(t.CompanyId, t.ParentId, t.CompanyName, t.Path)
.from(t)
.order_by(t.ParentId.Asc);
if (!string.IsNullOrEmpty(path))
{
query = query.where_and(t.Path.Match(path));
}
var list = query.query(new APDBDef(), reader =>
{
return(new ResCompany()
{
CompanyId = reader.GetInt64(0),
ParentId = reader.GetInt64(1),
CompanyName = reader.GetString(2),
Path = reader.GetString(3),
});
}).ToList();
ResCompany root = new ResCompany()
{
CompanyId = 0, ParentId = 0, CompanyName = "上海市", Path = ""
};
if (list.Count > 0 && isShowRoot)
{
root = list.FirstOrDefault();
}
Dictionary <long, ResCompany> dict = new Dictionary <long, ResCompany>()
{
{ 0, root }
};
foreach (var item in list)
{
if (dict.ContainsKey(item.ParentId))
{
var node = dict[item.ParentId];
if (node.Children == null)
{
node.Children = new List <ResCompany>();
}
node.Children.Add(item);
}
dict[item.CompanyId] = item;
}
return(root.Children);
}
public static void SetLastLoginTime(string username)
{
var t = APDBDef.ResUser;
var query = APQuery.update(t)
.set(t.LastLoginTime, DateTime.Now)
.set(t.LoginCount, APSqlThroughExpr.Expr("LoginCount + 1"))
.where (t.UserName == username);
using (var db = new APDBDef())
{
db.ExecuteNonQuery(query);
}
}
/// <summary>
/// Build group query command.
/// </summary>
/// <returns>The command.</returns>
public virtual APSqlSelectCommand BuildGroupQuery()
{
List <APSqlSelectPhrase> select = _source.GetGroupSelectPhrases();
List <APSqlFromPhrase> from = _source.GetPrimeFormPhrases();
List <APSqlWherePhrase> where = _source.GetPrimeWherePhrases();
List <APSqlExprPhrase> group = _source.GetGroupPhrases();
List <APSqlOrderPhrase> order = new List <APSqlOrderPhrase>();
foreach (APRptGroupDef def in _def.Groups)
{
APRptColumn column = _source.AllColumns[def.ColumnId];
// DateTime must grouping by datetime function.
if (column.FilterType == APRptFilterType.DateTime)
{
APSqlExpr expr = new APSqlDateGroupExpr(column.SelectExpr, def.DateGroupMode);
select.Add(expr);
group.Add(expr);
}
else
{
column.AddToQuerySelectPhrases(select);
column.AddToQueryGroupPhrases(group);
}
column.AddToQueryFromPhrases(from);
column.AddToQueryWherePhrases(where);
order.Add(column.GetQueryOrderByPhrase(def.According));
}
select.Add(APSqlAsteriskExpr.Expr.Count().As(COUNT_ALIAS));
// Build query
var query = APQuery
.select(select)
.from(from)
.where (where);
if (group.Count > 0)
{
query
.group_by(group)
.order_by(order);
}
return(query);
}
/// <summary>
/// Return a list for admin UI list.
/// </summary>
/// <param name="total"></param>
/// <param name="current"></param>
/// <param name="rowCount"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public static List <ResUser> TolerantSearch(out int total, int current, int rowCount, APSqlWherePhrase where, APSqlOrderPhrase order)
{
var t = APDBDef.ResUser;
var c = APDBDef.ResCompany;
//var r = APDBDef.ResRole;
//var ur = APDBDef.ResUserRole;
var query = APQuery
.select(t.UserId, t.UserName, t.RealName, t.GenderPKID, t.Email, t.RegisterTime, t.LoginCount, t.Actived, t.UserTypePKID,
t.CompanyId, t.ProvinceId, t.AreaId,
c.CompanyName //,r.RoleName
)
.from(t,
c.JoinLeft(t.CompanyId == c.CompanyId)
)
.where (where)
.primary(t.UserId)
.skip((current - 1) * rowCount)
.take(rowCount);
if (order != null)
{
query.order_by(order);
}
using (APDBDef db = new APDBDef())
{
total = db.ExecuteSizeOfSelect(query);
return(db.Query(query, reader =>
{
return new ResUser()
{
UserId = t.UserId.GetValue(reader),
UserTypePKID = t.UserTypePKID.GetValue(reader),
UserName = t.UserName.GetValue(reader),
RealName = t.RealName.GetValue(reader),
GenderPKID = t.GenderPKID.GetValue(reader),
Email = t.Email.GetValue(reader),
RegisterTime = t.RegisterTime.GetValue(reader),
LoginCount = t.LoginCount.GetValue(reader),
Actived = t.Actived.GetValue(reader),
CompanyName = c.CompanyName.GetValue(reader),
ProvinceId = t.ProvinceId.GetValue(reader),
AreaId = t.AreaId.GetValue(reader)
};
}).ToList());
}
}
/// <summary>
/// 根据 PickList 的 InnerKey 获得所有子项
/// </summary>
/// <param name="innerKey"></param>
/// <returns></returns>
public static List <ResPickListItem> GetByPickListInnerKey(string innerKey)
{
var query = APQuery
.select(APDBDef.ResPickListItem.Asterisk)
.from(
APDBDef.ResPickListItem,
APDBDef.ResPickList.Join(APSqlJoinType.Inner, APDBDef.Res_PickList_Item)
)
.where (APDBDef.ResPickList.InnerKey == innerKey);
using (APDBDef db = new APDBDef())
{
return(APDBDef.ResPickListItem.MapList(db.ExecuteReader(query)));
}
}
/// <summary>
/// Build maxtrix query command.
/// </summary>
/// <returns>The command.</returns>
public virtual APSqlSelectCommand BuildMatrixQuery()
{
List <APSqlSelectPhrase> select = _source.GetPrimeSelectPhrases();
List <APSqlFromPhrase> from = _source.GetPrimeFormPhrases();
List <APSqlWherePhrase> where = _source.GetPrimeWherePhrases();
List <APSqlOrderPhrase> order = new List <APSqlOrderPhrase>();
foreach (APRptGroupDef def in _def.Groups)
{
APRptColumn column = _source.AllColumns[def.ColumnId];
column.AddToQueryFromPhrases(from);
column.AddToQueryWherePhrases(where);
order.Add(column.GetQueryOrderByPhrase(def.According));
}
foreach (APRptReferDef def in _def.Refers)
{
APRptColumn column = _source.AllColumns[def.ColumnId];
column.AddToQuerySelectPhrases(select);
column.AddToQueryFromPhrases(from);
column.AddToQueryWherePhrases(where);
}
foreach (APRptOrderDef def in _def.Orders)
{
APRptColumn column = _source.AllColumns[def.ColumnId];
order.Add(column.GetQueryOrderByPhrase(def.According));
}
// Build query
var query = APQuery
.select(select)
.from(from)
.where (where);
return(query);
}
public ActionResult Search(int current, int rowCount, string searchPhrase)
{
var a = APDBDef.Active;
var query = APQuery
.select(a.ActiveId, a.ActiveName, a.Description, a.UploadStartDate, a.UploadEndDate, a.EvalStartDate,
a.EvalEndDate, a.FirstEvalStartDate, a.FirstEvalEndDate, a.DeliveryEvalStartDate, a.DeliveryEvalEndDate)
.from(a);
if (!string.IsNullOrEmpty(searchPhrase))
{
query.where (a.ActiveName.Match(searchPhrase));
}
query.primary(a.ActiveId)
.skip((current - 1) * rowCount)
.take(rowCount);
var total = db.ExecuteSizeOfSelect(query);
var actives = db.Query(query, a.TolerantMap).ToList();
var list = (from ac in actives
select new
{
id = ac.ActiveId,
name = ac.ActiveName,
description = ac.Description,
start = ac.UploadStartDate.ToString("yyyy-MM-dd"),
end = ac.UploadEndDate.ToString("yyyy-MM-dd"),
evalStart = ac.EvalStartDate.ToString("yyyy-MM-dd"),
evalEnd = ac.EvalEndDate.ToString("yyyy-MM-dd"),
firstEvalStart = ac.FirstEvalStartDate.ToString("yyyy-MM-dd"),
firstEvalEnd = ac.FirstEvalEndDate.ToString("yyyy-MM-dd"),
deliveryEvalStartDate = ac.DeliveryEvalStartDate.ToString("yyyy-MM-dd"),
deliveryEvalEndDate = ac.DeliveryEvalEndDate.ToString("yyyy-MM-dd"),
}).ToList();
return(Json(new
{
rows = list,
current,
rowCount,
total
}));
}
public ActionResult MultiWinLevel(string ids, long levelId)
{
ThrowNotAjax();
if (Request.IsAjaxRequest() && !string.IsNullOrEmpty(ids))
{
var cr = APDBDef.CroResource;
var array = ids.Split(',');
var idArray = new long[array.Length];
for (int i = 0; i < array.Length; i++)
{
idArray[i] = Convert.ToInt64(array[i]);
}
APQuery.update(cr).set(cr.WinLevelPKID, levelId).where (cr.CrosourceId.In(idArray)).execute(db);
return(Json(new { cmd = "Processed", msg = "批量奖项设置完成。" }));
}
return(IsNotAjax());
}
public List <CroMyResource> MyCroResource(long id, out int total, int take, int skip = 0)
{
var t = APDBDef.CroResource;
var userid = id;
var query = APQuery.select(t.CrosourceId, t.Title, t.ProvinceId, t.AreaId,
t.Author, t.AuthorPhone, t.AuthorEmail, t.AuthorCompany, //t.CoverPath, t.FileExtName,
t.Description, t.CreatedTime, t.AuditOpinion, t.StatePKID, t.ActiveId)
.from(t)
.where (t.Creator == userid & t.StatePKID == CroResourceHelper.StateAllow)
.order_by(t.CreatedTime.Desc)
.primary(t.CrosourceId)
.take(take)
.skip(skip);
total = db.ExecuteSizeOfSelect(query);
return(db.Query(query, reader =>
{
var des = t.Description.GetValue(reader);
if (des.Length > 100)
{
des = des.Substring(0, 100);
}
return new CroMyResource()
{
CrosourceId = t.CrosourceId.GetValue(reader),
Title = t.Title.GetValue(reader),
Author = t.Author.GetValue(reader),
Description = des,
OccurTime = t.CreatedTime.GetValue(reader),
StatePKID = t.StatePKID.GetValue(reader),
AuditOpinion = t.AuditOpinion.GetValue(reader),
IsCurrentActive = t.ActiveId.GetValue(reader) == ThisApp.CurrentActiveId,
AuthorPhone = t.AuthorPhone.GetValue(reader),
AuthorEmail = t.AuthorEmail.GetValue(reader),
AuthorCompany = t.AuthorCompany.GetValue(reader),
ProvinceId = t.ProvinceId.GetValue(reader),
AreaId = t.AreaId.GetValue(reader)
};
}).ToList());
}
public ActionResult MultiApprove(string ids, long state)
{
if (Request.IsAjaxRequest() && !string.IsNullOrEmpty(ids))
{
var cr = APDBDef.CroResource;
// var state = value ? CroResourceHelper.StateAllow : CroResourceHelper.StateDeny;
var array = ids.Split(',');
var idArray = new long[array.Length];
for (int i = 0; i < array.Length; i++)
{
idArray[i] = Convert.ToInt64(array[i]);
}
APQuery.update(cr).set(cr.StatePKID, state).where (cr.CrosourceId.In(idArray)).execute(db);
return(Json(new { cmd = "Processed", value = state, msg = "批量审核完成。" }));
}
return(IsNotAjax());
}
public static void Sync(long roleId, List <long> approveIds)
{
var t = APDBDef.ResRoleApprove;
using (APDBDef db = new APDBDef())
{
var existIds = APQuery.select(t.ApproveId)
.from(t)
.where (t.RoleId == roleId).query(db, reader =>
{
return(reader.GetInt64(0));
}).ToList();
db.BeginTrans();
try
{
foreach (var id in approveIds)
{
if (existIds.Contains(id))
{
existIds.Remove(id);
}
else
{
db.ResRoleApproveDal.Insert(new ResRoleApprove(0, roleId, id));
}
}
if (existIds.Count > 0)
{
db.ResRoleApproveDal.ConditionDelete(t.RoleId == roleId & t.ApproveId.In(existIds.ToArray()));
}
db.Commit();
}
catch
{
db.Rollback();
}
}
}
/// get complex resource object
/// </summary>
/// <param name="db">db</param>
/// <param name="resourceId">resourceId</param>
/// <returns>CroResource</returns>
public static CroResource GetResource(APDBDef db, long resourceId)
{
var cr = APDBDef.CroResource;
var f = APDBDef.Files;
var query = APQuery.select(cr.Asterisk, f.Asterisk)
.from(cr,
f.JoinInner(cr.AttachmentId == f.FileId)
)
.where (cr.CrosourceId == resourceId);
CroResource model = null;
return(query.query(db, r =>
{
model = new CroResource();
cr.Fullup(r, model, false);
model.AttachmentName = f.FileName.GetValue(r);
model.AttachmentPath = f.FilePath.GetValue(r);
return model;
}).FirstOrDefault());
}
public static List <ResCompany> GetParentTree()
{
var t = APDBDef.ResCompany;
var query = APQuery.select(t.CompanyId, t.ParentId, t.CompanyName, t.Path)
.from(t)
.where (t.ParentId == 0)
.order_by(t.ParentId.Asc);
var list = query.query(new APDBDef(), reader =>
{
return(new ResCompany()
{
CompanyId = reader.GetInt64(0),
ParentId = reader.GetInt64(1),
CompanyName = reader.GetString(2),
Path = reader.GetString(3),
});
}).ToList();
return(list);
}
public List <CroBulletin> CroBulltinList(APSqlOrderPhrase order, out int total, int take, int skip = -1)
{
var t = APDBDef.CroBulletin;
var query = APQuery.select(t.BulletinId, t.Title, t.Content, t.CreatedTime)
.from(t)
.order_by(new APSqlOrderPhrase(t.CreatedTime, APSqlOrderAccording.Desc))
.primary(t.BulletinId)
.take(take);
if (skip != -1)
{
query.skip(skip);
total = db.ExecuteSizeOfSelect(query);
}
else
{
total = 0;
}
return(db.Query(query, reader =>
{
var des = t.Title.GetValue(reader);
if (des.Length > 100)
{
des = des.Substring(0, 100);
}
return new CroBulletin()
{
BulletinId = t.BulletinId.GetValue(reader),
Title = t.Title.GetValue(reader),
Content = t.Content.GetValue(reader),
CreatedTime = t.CreatedTime.GetValue(reader),
};
}).ToList());
}
public ActionResult EvalProgress(long id, int current, int rowCount, string searchPhrase)
{
var ege = APDBDef.EvalGroupExpert;
var egr = APDBDef.EvalGroupResource;
var g = APDBDef.EvalGroup;
var u = APDBDef.ResUser;
var er = APDBDef.EvalResult;
var groupResourceCount = APBplDef.EvalGroupResourceBpl.ConditionQueryCount(egr.GroupId == id);
var result = APQuery.select(er.ExpertId, u.UserName, u.RealName, g.GroupId, g.GroupName, er.ResultId.Count().As("evalCount"))
.from(g,
ege.JoinInner(ege.GroupId == g.GroupId),
u.JoinInner(ege.ExpertId == u.UserId),
er.JoinLeft(er.GroupId == g.GroupId & er.ExpertId == u.UserId))
.group_by(er.ExpertId, u.UserName, u.RealName, g.GroupId, g.GroupName)
.where (g.GroupId == id)
.skip(rowCount * (current - 1))
.take(rowCount)
.query(db, r => new EvalProgress
{
ExpertId = er.ExpertId.GetValue(r),
Expert = u.RealName.GetValue(r),
GorupId = g.GroupId.GetValue(r),
GroupName = g.GroupName.GetValue(r),
Percent = Math.Round(((double)er.ResultId.GetValue(r, "evalCount") / (groupResourceCount == 0 ? 1 : groupResourceCount)) * 100, 1)
}).ToList();
return(Json(new
{
rows = result,
current,
rowCount,
total = result.Count
}));
}
public ActionResult FirstTrailEvalResultList(long activeId, long groupid, long expertId, int current, int rowCount, string searchPhrase)
{
var user = ResSettings.SettingsInSession.User;
var a = APDBDef.Active;
var g = APDBDef.EvalGroup;
var u = APDBDef.ResUser;
var cr = APDBDef.CroResource;
var er = APDBDef.EvalResult;
var der = APDBDef.DeliveryRecord;
var isAdmin = user.UserTypePKID == ResUserHelper.Admin || user.UserTypePKID == ResUserHelper.ProvinceAdmin;
var isExpert = user.UserTypePKID == ResUserHelper.Export;
APSqlSelectCommand query = null;
query = APQuery.select(er.ResultId, er.ExpertId, er.GroupId, er.AccessDate, er.Score, er.Comment,
cr.CrosourceId, cr.Title, cr.Score.As("AverageScore"), u.UserName, u.UserId,
g.GroupName, g.GroupId, a.ActiveName, a.ActiveId, der.RecordId)
.from(er,
cr.JoinInner(cr.CrosourceId == er.ResourceId),
u.JoinInner(u.UserId == er.ExpertId),
g.JoinInner(er.GroupId == g.GroupId),
a.JoinInner(a.ActiveId == cr.ActiveId),
der.JoinLeft(der.ResourceId == cr.CrosourceId)
);
if (activeId > 0)
{
query = query.where_and(cr.ActiveId == activeId);
}
if (groupid > 0)
{
query = query.where_and(er.GroupId == groupid);
}
if (user.ProvinceId > 0)
{
query.where_and(cr.ProvinceId == user.ProvinceId);
}
if (user.AreaId > 0)
{
query.where_and(cr.AreaId == user.AreaId);
}
//过滤条件
//模糊搜索
if (!string.IsNullOrEmpty(searchPhrase))
{
//query=query.
}
//query.primary(cr.CrosourceId)
// .skip(rowCount * (current - 1))
// .take(rowCount);
//获得查询的总数量
//var total = db.ExecuteSizeOfSelect(query);
//查询结果集
var result = query.query(db, r =>
{
return(new
{
id = er.ResultId.GetValue(r),
sourceId = cr.CrosourceId.GetValue(r),
title = cr.Title.GetValue(r),
date = er.AccessDate.GetValue(r),
expert = u.UserName.GetValue(r),
expertId = u.UserId.GetValue(r),
averageScore = cr.Score.GetValue(r, "AverageScore"),
score = er.Score.GetValue(r),
group = g.GroupName.GetValue(r),
groupId = g.GroupId.GetValue(r),
active = a.ActiveName.GetValue(r),
activeId = a.ActiveId.GetValue(r),
comment = er.Comment.GetValue(r),
isDelivery = der.RecordId.GetValue(r) > 0
});
}).ToList();
var total = result.Count;
if (result.Count > 0)
{
result = result.Skip(rowCount * (current - 1)).Take(rowCount).ToList();
}
return(Json(new
{
rows = result,
current,
rowCount,
total
}));
}
public ActionResult IndicationList(int current, int rowCount, string searchPhrase)
{
ThrowNotAjax();
var i = APDBDef.Indication;
var a = APDBDef.Active;
var query = APQuery.select(i.IndicationId, i.IndicationName, i.Description, i.Score,
i.LevelPKID, i.TypePKID, i.ActiveId, a.ActiveName
)
.from(i, a.JoinInner(a.ActiveId == i.ActiveId))
.primary(i.IndicationId)
.skip((current - 1) * rowCount)
.take(rowCount);
//.where(i.IndicationStatus==IndicationKeys.EnabelStatus);
//过滤条件
//模糊搜索
if (!string.IsNullOrEmpty(searchPhrase))
{
searchPhrase = searchPhrase.Trim();
query.where_and(i.IndicationName.Match(searchPhrase) | i.Description.Match(searchPhrase));
}
//排序条件表达式
//if (sort != null)
//{
// switch (sort.ID)
// {
// //case "userName": query.order_by(sort.OrderBy(u.UserName)); break;
// //case "realName": query.order_by(sort.OrderBy(u.RealName)); break;
// //case "userType": query.order_by(sort.OrderBy(u.UserType)); break;
// }
//}
//获得查询的总数量
var total = db.ExecuteSizeOfSelect(query);
//查询结果集
var result = query.query(db, r =>
{
return(new
{
id = i.IndicationId.GetValue(r),
name = i.IndicationName.GetValue(r),
description = i.Description.GetValue(r),
level = IndicationHelper.Level.GetName(i.LevelPKID.GetValue(r)),
type = IndicationHelper.Type.GetName(i.TypePKID.GetValue(r)),
activeName = a.ActiveName.GetValue(r),
activeId = i.ActiveId.GetValue(r),
score = i.Score.GetValue(r)
});
}).ToList();
return(Json(new
{
rows = result,
current,
rowCount,
total
}));
}
public ActionResult ResList(long id, long activeId, long deliveryStatus,
long provinceId, long areaId, int current,
int rowCount, string searchPhrase
)
{
ThrowNotAjax();
var user = ResSettings.SettingsInSession.User;
if (provinceId == 0)
{
provinceId = user.ProvinceId;
}
var r = APDBDef.CroResource;
var egr = APDBDef.EvalGroupResource;
var query = APQuery.select(r.CrosourceId, r.Title, r.AuthorCompany, r.Author, egr.GroupResourceId)
.from(r,
egr.JoinLeft(r.CrosourceId == egr.ResourceId & egr.GroupId == id)
)
.where_and(r.StatePKID != CroResourceHelper.StateDelete & r.ActiveId == ThisApp.CurrentActiveId)
.primary(r.CrosourceId)
.skip((current - 1) * rowCount)
.take(rowCount);
//过滤条件
//模糊搜索用户名、实名进行
if (!string.IsNullOrEmpty(searchPhrase))
{
searchPhrase = searchPhrase.Trim();
query.where_and(r.Author.Match(searchPhrase) | r.Description.Match(searchPhrase) | r.Title.Match(searchPhrase));
}
// 按项目，年级，学科,省市，地区，单位数据过滤
areaId = areaId <= 0 ? user.AreaId : areaId;
provinceId = provinceId <= 0 ? user.ProvinceId : provinceId;
if (activeId > 0)
{
query.where_and(r.ActiveId == activeId);
}
if (provinceId > 0)
{
query.where_and(r.ProvinceId == provinceId);
}
if (areaId > 0)
{
query.where_and(r.AreaId == areaId);
}
if (deliveryStatus > CroResourceHelper.AllDelivery)
{
query.where_and(r.DeliveryStatus == deliveryStatus);
}
//排序条件表达式
//if (sort != null)
//{
// switch (sort.ID)
// {
// case "realName": query.order_by(sort.OrderBy(up.RealName)); break;
// case "target": query.order_by(sort.OrderBy(d.DeclareTargetPKID)); break;
// case "subject": query.order_by(sort.OrderBy(d.DeclareSubjectPKID)); break;
// case "stage": query.order_by(sort.OrderBy(d.DeclareStagePKID)); break;
// case "groupCount": query.order_by(sort.OrderBy(e.GroupCount)); break;
// }
//}
//获得查询的总数量
var total = db.ExecuteSizeOfSelect(query);
//查询结果集
var result = query.query(db, rd =>
{
return(new
{
id = egr.GroupResourceId.GetValue(rd),
resId = r.CrosourceId.GetValue(rd),
title = r.Title.GetValue(rd),
company = r.AuthorCompany.GetValue(rd),
author = r.Author.GetValue(rd),
isSelect = egr.GroupResourceId.GetValue(rd) > 0,
});
}).ToList();
return(Json(new
{
rows = result,
current,
rowCount,
total
}));
}
public ActionResult Search(int activeId, int current, int rowCount, string searchPhrase, FormCollection fc)
{
var user = ResSettings.SettingsInSession.User;
var eg = APDBDef.EvalGroup;
var a = APDBDef.Active;
var query = APQuery
.select(eg.GroupId, eg.GroupName, eg.LevelPKID, eg.StartDate, eg.EndDate, a.ActiveId, a.ActiveName) //t.MediumTypePKID,
.from(eg, a.JoinInner(eg.ActiveId == a.ActiveId))
.where (eg.GroupType == EvalGroupHelper.LastTrial);
if (!string.IsNullOrEmpty(searchPhrase))
{
query.where (eg.GroupName.Match(searchPhrase));
}
if (user.ProvinceId > 0)
{
query.where_and(eg.ProvinceId == user.ProvinceId);
}
if (user.AreaId > 0)
{
query.where_and(eg.AreaId == user.AreaId);
}
if (user.CompanyId > 0)
{
query.where_and(eg.CompanyId == user.CompanyId);
}
if (activeId > 0)
{
query.where_and(eg.ActiveId == activeId);
}
query.primary(eg.GroupId)
.skip((current - 1) * rowCount)
.take(rowCount);
var total = db.ExecuteSizeOfSelect(query);
var egs = query.query(db, r =>
new EvalGroup
{
GroupId = eg.GroupId.GetValue(r),
GroupName = eg.GroupName.GetValue(r),
LevelPKID = eg.LevelPKID.GetValue(r),
ActiveId = a.ActiveId.GetValue(r),
ActiveName = a.ActiveName.GetValue(r),
StartDate = eg.StartDate.GetValue(r),
EndDate = eg.EndDate.GetValue(r)
}).ToList();
var list = (from c in egs
select new
{
id = c.GroupId,
gropupName = c.GroupName,
level = c.Level,
activeId = c.ActiveId,
activeName = c.ActiveName,
start = c.StartDate.ToString("yyyy-MM-dd"),
end = c.EndDate.ToString("yyyy-MM-dd")
}).ToList();
return(Json(new
{
rows = list,
current,
rowCount,
total
}));
}
public ActionResult ExpList(int current, int rowCount, string searchPhrase, int id)
{
ThrowNotAjax();
var user = ResSettings.SettingsInSession.User;
var u = APDBDef.ResUser;
var ege = APDBDef.EvalGroupExpert;
var query = APQuery.select(u.UserId, u.RealName, u.UserName, ege.GroupExpertId)
.from(
u,
ege.JoinLeft(u.UserId == ege.ExpertId & ege.GroupId == id)
)
.where (u.UserTypePKID == ResUserHelper.Export)
.primary(u.UserId)
.skip((current - 1) * rowCount)
.take(rowCount);
//过滤条件
//模糊搜索用户名、实名进行
if (!string.IsNullOrEmpty(searchPhrase))
{
searchPhrase = searchPhrase.Trim();
query.where_and(u.RealName.Match(searchPhrase) | u.UserName.Match(searchPhrase));
}
//TODO: 角色数据范围过滤
if (user.ProvinceId > 0)
{
query.where_and(u.ProvinceId == user.ProvinceId);
}
if (user.AreaId > 0)
{
query.where_and(u.AreaId == user.AreaId);
}
//if (user.AreaId > 0 && user.ProvinceId != ResCompanyHelper.Shanghai) //如果非上海，其他市区id都是areaId
// query.where_and(u.AreaId == user.AreaId);
//排序条件表达式
//if (sort != null)
//{
// switch (sort.ID)
// {
// case "realName": query.order_by(sort.OrderBy(up.RealName)); break;
// case "target": query.order_by(sort.OrderBy(d.DeclareTargetPKID)); break;
// case "subject": query.order_by(sort.OrderBy(d.DeclareSubjectPKID)); break;
// case "stage": query.order_by(sort.OrderBy(d.DeclareStagePKID)); break;
// case "groupCount": query.order_by(sort.OrderBy(e.GroupCount)); break;
// }
//}
//获得查询的总数量
var total = db.ExecuteSizeOfSelect(query);
//查询结果集
var result = query.query(db, rd =>
{
return(new
{
id = ege.GroupExpertId.GetValue(rd),
expId = u.UserId.GetValue(rd),
realName = u.RealName.GetValue(rd),
userName = u.UserName.GetValue(rd),
isSelect = ege.GroupExpertId.GetValue(rd) > 0
});
}).ToList();
return(Json(new
{
rows = result,
current,
rowCount,
total
}));
}
public ActionResult Search(int current, int rowCount, string searchPhrase, FormCollection fc)
{
var user = ResSettings.SettingsInSession.User;
var expertId = ResSettings.SettingsInSession.UserId;
var query = APQuery.select(r.CrosourceId, r.Title, r.Author, r.AuthorCompany, eg.GroupType,
eg.GroupName, eg.GroupId.As("groupId"), er.ResultId, er.Score, er.ResultId.As("resultId"))
.from(egr,
r.JoinInner(egr.ResourceId == r.CrosourceId),
eg.JoinInner(eg.GroupId == egr.GroupId),
ege.JoinInner(eg.GroupId == ege.GroupId),
u.JoinInner(ege.ExpertId == u.UserId & ege.ExpertId == expertId),
er.JoinLeft(er.ResourceId == r.CrosourceId & er.GroupId == eg.GroupId & er.ExpertId == expertId)
);
if (!string.IsNullOrEmpty(searchPhrase))
{
query.where (eg.GroupName.Match(searchPhrase)
| r.Title.Match(searchPhrase)
| r.AuthorCompany.Match(searchPhrase)
| r.Author.Match(searchPhrase)
);
}
//query.primary(r.CrosourceI)
// .skip((current - 1) * rowCount)
// .take(rowCount);
//var total = db.ExecuteSizeOfSelect(query);
var results = query.query(db, rd =>
{
return(new
{
id = er.ResultId.GetValue(rd, "resultId"),
resId = r.CrosourceId.GetValue(rd),
title = r.Title.GetValue(rd),
author = r.Author.GetValue(rd),
company = r.AuthorCompany.GetValue(rd),
groupName = eg.GroupName.GetValue(rd),
groupId = eg.GroupId.GetValue(rd, "groupId"),
score = er.Score.GetValue(rd),
isEval = er.ResultId.GetValue(rd) > 0,
//evalType=eg.GroupType.GetValue(rd), // 初审 或 最终评审
isFirstTrail = eg.GroupType.GetValue(rd) == EvalGroupHelper.FirstTrial
});
}).ToList();
var total = results.Count();
results = results.Skip((current - 1) * rowCount).Take(rowCount).ToList();
return(Json(new
{
rows = results,
current,
rowCount,
total
}));
}
private List <ExportAllScoreViewModel> GetExpertScoreViewModels(long expertId)
{
var result = APQuery.select(r.CrosourceId, r.Title, r.Author, r.AuthorCompany, eg.GroupName,
er.ResultId, er.Score, er.Comment, eri.Score.As("detailScore"), idi.IndicationName)
.from(
egr,
r.JoinInner(egr.ResourceId == r.CrosourceId),
eg.JoinInner(eg.GroupId == egr.GroupId),
er.JoinLeft(er.ResourceId == r.CrosourceId & er.ExpertId == expertId),
eri.JoinLeft(er.ResultId == eri.ResultId),
idi.JoinLeft(idi.IndicationId == eri.IndicationId)
)
.where (egr.GroupId.In(APQuery.select(ege.GroupId).from(ege).where (ege.ExpertId == expertId)))
.query(db, rd =>
{
return(new
{
id = r.CrosourceId.GetValue(rd),
title = r.Title.GetValue(rd),
Author = r.Author.GetValue(rd),
Compnay = r.AuthorCompany.GetValue(rd),
Score = er.Score.GetValue(rd),
IndicationScore = eri.Score.GetValue(rd, "detailScore"),
indicationName = idi.IndicationName.GetValue(rd),
comment = er.Comment.GetValue(rd),
IsEval = er.ResultId.GetValue(rd) > 0,
groupName = eg.GroupName.GetValue(rd)
});
}).ToList();
var models = new List <ExportAllScoreViewModel>();
var index = 1;
foreach (var item in result)
{
if (!models.Exists(x => x.Id == item.id))
{
models.Add(new ExportAllScoreViewModel
{
Id = item.id,
ResourceName = item.title,
Author = item.Author,
AuthorCompany = item.Compnay,
Comment = item.comment,
Score = item.Score,
Score1 = item.IndicationScore,
IsEval = item.IsEval,
GroupName = item.groupName
});
}
else
{
var model = models.Find(x => x.Id == item.id);
var dynamicColumnCount = typeof(ExportAllScoreViewModel)
.GetProperties().ToList()
.FindAll(x => x.Name.IndexOf("Score") == 0)
.Count() - 1;
for (int i = 2; i <= dynamicColumnCount; i++)
{
if (index == i || index % dynamicColumnCount == i)
{
model.GetType().GetProperty("Score" + i).SetValue(model, item.IndicationScore);
}
if (index == dynamicColumnCount || index % dynamicColumnCount == 0)
{
model.GetType().GetProperty("Score" + dynamicColumnCount).SetValue(model, item.IndicationScore);
}
}
}
index++;
}
return(models);
}
/// <summary>
/// Creates a record or updates an existing record.
/// </summary>
/// <param name='operations'>
/// The operations group for this extension method.
/// </param>
/// <param name='entity'>
/// The record to be passed to the system.
/// </param>
/// <param name='select'>
/// The fields of the entity to be returned from the system.
/// </param>
/// <param name='filter'>
/// The conditions that determine which records should be selected from the
/// system.
/// </param>
/// <param name='expand'>
/// The linked and detail entities that should be expanded.
/// </param>
/// <param name='custom'>
/// The fields that are not defined in the contract of the endpoint to be
/// returned from the system.
/// </param>
public static object PutEntity(this IAPQueryOperations operations, APQuery entity, string select = default(string), string filter = default(string), string expand = default(string), string custom = default(string))
{
return(Task.Factory.StartNew(s => ((IAPQueryOperations)s).PutEntityAsync(entity, select, filter, expand, custom), operations, CancellationToken.None, TaskCreationOptions.None, TaskScheduler.Default).Unwrap().GetAwaiter().GetResult());
}
/// <summary>
/// Creates a record or updates an existing record.
/// </summary>
/// <param name='operations'>
/// The operations group for this extension method.
/// </param>
/// <param name='entity'>
/// The record to be passed to the system.
/// </param>
/// <param name='select'>
/// The fields of the entity to be returned from the system.
/// </param>
/// <param name='filter'>
/// The conditions that determine which records should be selected from the
/// system.
/// </param>
/// <param name='expand'>
/// The linked and detail entities that should be expanded.
/// </param>
/// <param name='custom'>
/// The fields that are not defined in the contract of the endpoint to be
/// returned from the system.
/// </param>
/// <param name='cancellationToken'>
/// The cancellation token.
/// </param>
public static async Task <object> PutEntityAsync(this IAPQueryOperations operations, APQuery entity, string select = default(string), string filter = default(string), string expand = default(string), string custom = default(string), CancellationToken cancellationToken = default(CancellationToken))
{
using (var _result = await operations.PutEntityWithHttpMessagesAsync(entity, select, filter, expand, custom, null, cancellationToken).ConfigureAwait(false))
{
return(_result.Body);
}
}
/// <summary>
/// Build a APQuery.
/// </summary>
/// <param name="additionCondition">Addition condition.</param>
/// <param name="additionOrders">Addition orders</param>
/// <param name="fuzzySearchString">Fuzzy search string.</param>
/// <returns>The APQuery.</returns>
public virtual APSqlSelectCommand BuildQuery(APSqlWherePhrase additionCondition = null,
IEnumerable <APSqlOrderPhrase> additionOrders = null,
string fuzzySearchString = null)
{
List <APSqlSelectPhrase> select = _source.GetPrimeSelectPhrases();
List <APSqlFromPhrase> from = _source.GetPrimeFormPhrases();
List <APSqlWherePhrase> where = _source.GetPrimeWherePhrases();
// Base build.
foreach (APRptReferDef refer in _def.Refers)
{
APRptColumn column = _source.AllColumns[refer.ColumnId];
column.AddToQuerySelectPhrases(select);
column.AddToQueryFromPhrases(from);
column.AddToQueryWherePhrases(where);
}
var query = APQuery
.select(select)
.from(from)
.primary(_source.GetPrimaryExpr());
// Order build.
List <APSqlOrderPhrase> orderby = new List <APSqlOrderPhrase>();
if (_def.Orders.Count > 0)
{
foreach (APRptOrderDef order in _def.Orders)
{
orderby.Add(_source.AllColumns[order.ColumnId].GetQueryOrderByPhrase(order.According));
}
}
if (additionOrders != null)
{
foreach (APSqlOrderPhrase order in additionOrders)
{
orderby.Add(order);
}
}
query.order_by(orderby);
// Filter build.
if (_def.Condition.Filters.Count > 0)
{
where.Add(new APRptConditionBuilder(_def.Condition, _source.AllColumns).BuildCondition());
}
// Additions condition.
if (additionCondition != null)
{
where.Add(additionCondition);
}
// Fuzzy search condition.
APSqlWherePhrase fuzzyWhere;
if (!String.IsNullOrEmpty(fuzzySearchString) && (fuzzyWhere = _source.GetFuzzySearchPhrase(fuzzySearchString)) != null)
{
where.Add(fuzzyWhere);
}
query.where (where);
return(query);
}