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