//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 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 Search(long provinceId, long areaId, int current, int rowCount, string searchPhrase, FormCollection fc) { var user = ResSettings.SettingsInSession.User; //---------------------------------------------------------- var t = APDBDef.ResUser; var c = APDBDef.ResCompany; APSqlOrderPhrase order = null; APSqlWherePhrase where = t.Removed == false; // 取排序 var co = GridOrder.GetSortDef(fc); if (co != null) { switch (co.Id) { case "UserName": order = new APSqlOrderPhrase(t.UserName, co.Order); break; case "RealName": order = new APSqlOrderPhrase(t.RealName, co.Order); break; case "CompanyName": order = new APSqlOrderPhrase(c.CompanyName, co.Order); break; case "RoleName": order = new APSqlOrderPhrase(APDBDef.ResRole.RoleId, co.Order); break; case "Gender": order = new APSqlOrderPhrase(t.GenderPKID, co.Order); break; case "Email": order = new APSqlOrderPhrase(t.Email, co.Order); break; case "RegisterTime": order = new APSqlOrderPhrase(t.RegisterTime, co.Order); break; case "LoginCount": order = new APSqlOrderPhrase(t.LoginCount, co.Order); break; case "Actived": order = new APSqlOrderPhrase(t.Actived, co.Order); break; } } // 默认排序 // if (order == null) order = new APSqlOrderPhrase(t.RegisterTime, APSqlOrderAccording.Desc); // 按用户名或真实姓名过滤 if (searchPhrase != null) { searchPhrase = searchPhrase.Trim(); if (searchPhrase != "") { where &= (t.UserName.Match(searchPhrase) | t.RealName.Match(searchPhrase)); } } if (provinceId > 0) { where &= t.ProvinceId == provinceId; } if (areaId > 0) { where &= t.AreaId == areaId; } // 用户数据范围 if (user.ProvinceId > 0) { where &= t.ProvinceId == user.ProvinceId; } if (user.AreaId > 0) { where &= t.AreaId == user.AreaId; } if (user.CompanyId > 0) { where &= t.CompanyId == user.CompanyId; } int total; var list = APBplDef.ResUserBpl.TolerantSearch(out total, current, rowCount, where, order); //---------------------------------------------------------- if (Request.IsAjaxRequest()) { return(Json( new { rows = from res in list select new { id = res.UserId, res.UserName, res.RealName, res.CompanyName, res.UserType, res.Gender, res.Email, RegisterTime = res.RegisterTime.ToString("yyyy-MM-dd"), res.LoginCount, Actived = res.Actived ? "有效" : "无效" }, current = current, rowCount = rowCount, total = total })); } else { return(View(list)); } }
/// 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()); } }
/// <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> /// 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()); } }
public ActionResult Search(long activeId, long themeId, long provinceId, long areaId, long deliveryStatus, // long stateId, long maxScore, long minScore, long isPass, int current, int rowCount, string searchPhrase, FormCollection fc) { var user = ResSettings.SettingsInSession.User; //---------------------------------------------------------- var t = APDBDef.CroResource; var u = APDBDef.ResUser; APSqlOrderPhrase order = null; APSqlWherePhrase where = t.StatePKID != 10359 & t.ActiveId == ThisApp.CurrentActiveId; //TODO 只取2018 // 取排序 var co = GridOrder.GetSortDef(fc); if (co != null) { switch (co.Id) { case "Title": order = new APSqlOrderPhrase(t.Title, co.Order); break; case "Author": order = new APSqlOrderPhrase(u.RealName, co.Order); break; case "CreatedTime": order = new APSqlOrderPhrase(t.CreatedTime, co.Order); break; // case "State": order = new APSqlOrderPhrase(t.StatePKID, co.Order); break; case "Score": order = new APSqlOrderPhrase(t.Score, co.Order); break; case "WinLevel": order = new APSqlOrderPhrase(t.WinLevelPKID, co.Order); break; } } // 按作品标题,内容,作者等过滤 if (searchPhrase != null) { searchPhrase = searchPhrase.Trim(); if (searchPhrase != "") { where &= t.Title.Match(searchPhrase) | t.Author.Match(searchPhrase) | t.Description.Match(searchPhrase) | t.AuthorCompany.Match(searchPhrase); } } // 按照主题过滤 if (themeId > 0) { where &= t.ThemeId == themeId; } // 用户数据范围或搜索 if (user.ProvinceId > 0 || provinceId > 0) { where &= t.ProvinceId == (provinceId > 0 ? provinceId : user.ProvinceId); } if (user.AreaId > 0 || areaId > 0) { where &= t.AreaId == (areaId > 0 ? areaId : user.AreaId); } if (maxScore > 0) { where &= t.Score <= maxScore; } if (minScore > 0) { where &= t.Score >= minScore; } // 按项目数据过滤 if (activeId > 0) { where &= t.ActiveId == activeId; } // 按照报送状态过滤 if (deliveryStatus > -1) { where &= t.DeliveryStatus == deliveryStatus; } //if (stateId > 0) // where &= t.StatePKID == stateId; if (isPass > 0) { APDBDef.EvalResultTableDef er = APDBDef.EvalResult; var subQuery = APQuery.select(er.ResourceId).from(er).where (er.EvalType == EvalGroupHelper.FirstTrial); where &= t.CrosourceId.In(subQuery); } int total; var list = APBplDef.CroResourceBpl.TolerantSearch(out total, current, rowCount, where, order); if (Request.IsAjaxRequest()) { return(Json( new { rows = from cro in list select new { id = cro.CrosourceId, cro.Title, cro.Author, CreatedTime = cro.CreatedTime.ToString("yyyy-MM-dd"), cro.Province, cro.Area, //cro.State, cro.StatePKID, cro.Score, cro.WinLevel, cro.WinLevelPKID, cro.DeliveryStatusName, cro.DeliveryStatus, cro.Theme }, current = current, rowCount = rowCount, total = total })); } else { return(View(list)); } }
public ActionResult Category(int current, int rowCount, string searchPhrase, FormCollection fc) { //---------------------------------------------------------- var t = APDBDef.CroResource; var u = APDBDef.ResUser; APSqlOrderPhrase order = null; List <APSqlWherePhrase> conds = new List <APSqlWherePhrase>() { t.StatePKID != CroResourceHelper.StateDelete }; // 取排序 var co = GridOrder.GetSortDef(fc); if (co != null) { switch (co.Id) { case "Title": order = new APSqlOrderPhrase(t.Title, co.Order); break; case "Author": order = new APSqlOrderPhrase(u.RealName, co.Order); break; case "CreatedTime": order = new APSqlOrderPhrase(t.CreatedTime, co.Order); break; case "State": order = new APSqlOrderPhrase(t.StatePKID, co.Order); break; } } // 取过滤条件 foreach (string cond in fc.Keys) { switch (cond) { case "State": conds.Add(t.StatePKID == Int64.Parse(fc[cond])); break; } } // 按关键字过滤 if (searchPhrase != null) { searchPhrase = searchPhrase.Trim(); if (searchPhrase != "") { conds.Add(t.Title.Match(searchPhrase)); } } int total; var list = APBplDef.CroResourceBpl.TolerantSearch(out total, current, rowCount, new APSqlConditionAndPhrase(conds), order); //---------------------------------------------------------- if (Request.IsAjaxRequest()) { return(Json( new { rows = from cro in list select new { //id = cro.CrosourceId, //cro.Title, //cro.Author, //cro.MediumType, //CreatedTime = cro.CreatedTime.ToString("yyyy-MM-dd"), //cro.State, //cro.StatePKID, //cro.EliteScore, //cro.ViewCount, //cro.DownCount, //cro.FavoriteCount, //cro.CommentCount, //cro.StarTotal }, current = current, rowCount = rowCount, total = total })); } else { return(View(list)); } }