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