public static IList <St_user> GetAdminUsers()
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_user>(us => us.RoleId != 0));
}
}
public static IList <St_notice> GetLatestNotices(int limit, int noticeType)
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_notice>(fs => fs.Where(ct => ct.NoticeType == noticeType).Limit(limit).OrderByDescending(ct => ct.Id)));
}
}
/// <summary>
/// 首页获取推荐库存
/// </summary>
/// <param name="limit"></param>
/// <returns></returns>
public static IList <St_good> GetRecommendStocks(int limit)
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_good>(cat => cat.Limit(limit).Where(ct => ct.Status > 0 && ct.Recommended == true).OrderByDescending(ct => ct.Id)));
}
}
/// <summary>
/// 获取最新库存的所有分类
/// </summary>
/// <param name="latestIds"></param>
/// <returns></returns>
public static IList <St_cat> GetlatestStocksCats(IList <int> latestIds)
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_cat>(cat => Sql.In(cat.Id, latestIds)));
}
}
/// <summary>
/// 后台获取库存
/// </summary>
/// <param name="memberid"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="search"></param>
/// <returns></returns>
public static IList <St_good> GetStocks(int?memberid, int start, int limit, string search)
{
using (var db = DBService.OpenDb())
{
if (!string.IsNullOrEmpty(search))
{
if (memberid > 0)
{
return(db.Select <St_good>(fs => fs.Limit(start, limit).Where(ct => ct.MemberId == memberid && ct.Name.Contains(search)).OrderByDescending(ct => ct.Id)));
}
else
{
return(db.Select <St_good>(fs => fs.Limit(start, limit).Where(ct => ct.Name.Contains(search)).OrderByDescending(ct => ct.Id)));
}
}
else if (memberid > 0)
{
return(db.Select <St_good>(fs => fs.Limit(start, limit).Where(ct => ct.MemberId == memberid).OrderByDescending(ct => ct.Id)));
}
else
{
return(db.Select <St_good>(fs => fs.Limit(start, limit).OrderByDescending(ct => ct.Id)));
}
}
}
/// <summary>
/// 前台获取品牌库存分页数据
/// </summary>
/// <param name="catid"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="search"></param>
/// <returns></returns>
public static IList <St_good> GetBrandPagedStocks(int?catid, int start, int limit, string search)
{
using (var db = DBService.OpenDb())
{
var catp = "," + catid + ",";
if (catid > 0)
{
var ccat = db.SingleOrDefault <St_cat>("Id={0}", catid);
if (ccat != null && ccat.Id > 0)
{
catp = ccat.Path;
}
}
if (!string.IsNullOrEmpty(search))
{
if (catid > 0)
{
return(db.Select <St_good>(fs => fs.Limit(start, limit).Where(ct => ct.IsBrandGoods == true && ct.CatPath.StartsWith(catp) && ct.Status > 0 && ct.Name.Contains(search)).OrderByDescending(ct => ct.Id)));
}
else
{
return(db.Select <St_good>(fs => fs.Limit(start, limit).Where(ct => ct.IsBrandGoods == true && ct.Status > 0 && ct.Name.Contains(search)).OrderByDescending(ct => ct.Id)));
}
}
else if (catid > 0)
{
return(db.Select <St_good>(fs => fs.Limit(start, limit).Where(ct => ct.IsBrandGoods == true && ct.CatPath.StartsWith(catp) && ct.Status > 0).OrderByDescending(ct => ct.Id)));
}
else
{
return(db.Select <St_good>(fs => fs.Limit(start, limit).Where(ct => ct.IsBrandGoods == true && ct.Status > 0).OrderByDescending(ct => ct.Id)));
}
}
}
/// <summary>
/// 后台获取所有库存分类
/// </summary>
/// <returns></returns>
public static IList <St_cat> GetStockCats()
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_cat>(cat => cat.OrderBy(ct => ct.Path)));
}
}
public static TEntity GetEntity <TEntity>(int id) where TEntity : IHasId <int>, new()
{
using (var db = DBService.OpenDb())
{
return(db.SingleOrDefault <TEntity>("Id={0}", id));
}
}
public static IList <TEntity> GetList <TEntity>() where TEntity : IHasId <int>, new()
{
using (var db = DBService.OpenDb())
{
return(db.Select <TEntity>(te => te.OrderByDescending(ee => ee.Id)));
}
}
public static St_role GetRole(int id)
{
using (var db = DBService.OpenDb())
{
return(db.Single <St_role>("Id={0}", id));
}
}
public static string GetRoleName(string roleId)
{
int rid;
if (string.IsNullOrEmpty(roleId) || !int.TryParse(roleId, out rid))
{
return("普通用户");
}
else if (rid > 0)
{
using (var db = DBService.OpenDb())
{
var role = db.Single <St_role>("Id={0}", rid);
if (role != null && role.Id > 0)
{
return(role.Name);
}
else
{
return("管理员");
}
}
}
else if (rid == -1)
{
return("超级管理员");
}
else
{
return("普通用户");
}
}
public static IList <St_role> GetRoles()
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_role>());
}
}
public static string GetUserPerms(int id)
{
var perms = "";
if (id > 0)
{
using (var db = DBService.OpenDb())
{
var u = db.Single <St_user>("Id={0}", id);
if (u != null && u.Id > 0)
{
var rid = u.RoleId.HasValue ? u.RoleId.Value : 0;
if (rid > 0)
{
var r = db.Single <St_role>("Id={0}", rid);
if (r != null && r.Id > 0)
{
perms = r.Perms;
}
}
}
}
}
return(perms);
}
public static St_user GetUser(int id)
{
using (var db = DBService.OpenDb())
{
return(db.Single <St_user>("Id={0}", id));
}
}
/// <summary>
/// 前台获取库存求购分页数据
/// </summary>
/// <param name="catid"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="search"></param>
/// <returns></returns>
public static IList <St_good> GetBuyPagedStocks(int?catid, int start, int limit, string search)
{
using (var db = DBService.OpenDb())
{
var catp = "," + catid + ",";
if (catid > 0)
{
var ccat = db.SingleOrDefault <St_cat>("Id={0}", catid);
if (ccat != null && ccat.Id > 0)
{
catp = ccat.Path;
}
}
var query = db.CreateExpression <St_good>().Where(s => s.Status > 0 && s.GoodsType == 1);
if (!string.IsNullOrEmpty(search))
{
query = query.Where(s => s.Name.Contains(search));
}
if (catid > 0)
{
query = query.Where(s => s.CatPath.StartsWith(catp));
}
query = query.OrderByDescending(s => s.Id).Limit(start, limit);
return(db.Select <St_good>(query));
}
}
public static IList <St_good> GetLatestStocks()
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_good>(cc => cc.Limit(5).OrderByDescending(fs => fs.Id)));
}
}
/// <summary>
/// 前台获取品牌库存个数
/// </summary>
/// <param name="catid"></param>
/// <param name="search"></param>
/// <returns></returns>
public static int GetBrandTotalStockCount(int?catid, string search)
{
using (var db = DBService.OpenDb())
{
var catp = "," + catid + ",";
if (catid > 0)
{
var ccat = db.SingleOrDefault <St_cat>("Id={0}", catid);
if (ccat != null && ccat.Id > 0)
{
catp = ccat.Path;
}
}
if (!string.IsNullOrEmpty(search))
{
if (catid > 0)
{
return((int)db.Count <St_good>(fs => fs.IsBrandGoods == true && fs.CatPath.StartsWith(catp) && fs.Status > 0 && fs.Name.Contains(search)));
}
else
{
return((int)db.Count <St_good>(fs => fs.IsBrandGoods == true && fs.Status > 0 && fs.Name.Contains(search)));
}
}
else if (catid > 0)
{
return((int)db.Count <St_good>(fs => fs.IsBrandGoods == true && fs.CatPath.StartsWith(catp) && fs.Status > 0));
}
else
{
return((int)db.Count <St_good>(fs => fs.IsBrandGoods == true && fs.Status > 0));
}
}
}
public static long[] GetStockMonthlyCount()
{
var res = new long[12];
var mcs = new List <MonthlyCount>();
var yeardate = new DateTime(DateTime.Now.Year, 1, 1, 0, 0, 0);
using (var db = DBService.OpenDb())
{
mcs = db.SqlList <MonthlyCount>("SELECT month(AddTime) as Month,count(Id) as Count FROM st_goods where AddTime>='" + yeardate + "' group by month(AddTime);");
}
if (mcs != null && mcs.Count > 0)
{
for (var i = 0; i < 12; i++)
{
foreach (var mc in mcs)
{
if (mc.Month == i + 1)
{
res[i] = mc.Count;
}
}
}
}
return(res);
}
/// <summary>
/// 获取库存的其他图片
/// </summary>
/// <param name="stockid"></param>
/// <returns></returns>
public static IList <St_pic> GetStockPics(int stockid)
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_pic>(pic => pic.PicType == 5 && pic.RelateId == stockid));
}
}
public static Settings GetSettingsObject()
{
Settings sts = new Settings();
Type ststype = sts.GetType();
using (var db = DBService.OpenDb())
{
var settings = db.Select <St_setting>();
var dicts = new Dictionary <string, string>();
if (settings != null && settings.Count > 0)
{
foreach (var sett in settings)
{
dicts.Add(sett.Name, sett.Val);
}
}
//var dicts = db.Dictionary<string, string>("select Name,Val from St_setting");
if (dicts != null && dicts.Count > 0)
{
PropertyInfo property = null;
foreach (var dict in dicts)
{
property = ststype.GetProperty(dict.Key);
if (property != null)
{
property.SetValue(sts, Convert.ChangeType(dict.Value, property.PropertyType), null);
}
}
}
}
return(sts);
}
/// <summary>
/// 获取前两层分类
/// </summary>
/// <returns></returns>
public static IList <St_cat> GetTopLevelStockCats()
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_cat>(cat => cat.Where(ct => ct.Level < 2).OrderBy(ct => ct.Id)));
}
}
public static void SaveSettings(IDictionary <string, string> settings)
{
if (settings != null && settings.Count > 0)
{
using (var db = DBService.OpenDb())
{
var stttes = db.Select <St_setting>();
var sts = new Dictionary <string, string>();
if (stttes != null && stttes.Count > 0)
{
foreach (var sett in stttes)
{
sts.Add(sett.Name, sett.Val);
}
}
//var sts = db.Dictionary<string, string>("select Name,Val from St_setting");
foreach (var setting in settings)
{
if (sts.ContainsKey(setting.Key))
{
db.Update <St_setting>(new { Val = setting.Value }, st => st.Name == setting.Key);
}
else
{
db.Insert <St_setting>(new St_setting {
Name = setting.Key, Val = setting.Value
});
}
}
}
}
}
public static int GetMemberTotalStockCount(int memberid, string search)
{
using (var db = DBService.OpenDb())
{
if (!string.IsNullOrEmpty(search))
{
if (memberid > 0)
{
return((int)db.Count <St_good>(fs => fs.MemberId == memberid && fs.Name.Contains(search)));
}
else
{
return(0);
}
}
else if (memberid > 0)
{
return((int)db.Count <St_good>(fs => fs.MemberId == memberid));
}
else
{
return(0);
}
}
}
/// <summary>
/// 前台获取库存求购个数
/// </summary>
/// <param name="catid"></param>
/// <param name="search"></param>
/// <returns></returns>
public static int GetBuyTotalStockCount(int?catid, string search)
{
using (var db = DBService.OpenDb())
{
var catp = "," + catid + ",";
if (catid > 0)
{
var ccat = db.SingleOrDefault <St_cat>("Id={0}", catid);
if (ccat != null && ccat.Id > 0)
{
catp = ccat.Path;
}
}
var query = db.CreateExpression <St_good>().Where(s => s.Status > 0 && s.GoodsType == 1);
if (!string.IsNullOrEmpty(search))
{
query = query.Where(s => s.Name.Contains(search));
}
if (catid > 0)
{
query = query.Where(s => s.CatPath.StartsWith(catp));
}
return((int)db.Count <St_good>(query));
}
}
/// <summary>
/// 前台获取热门库存求购
/// </summary>
/// <param name="limit"></param>
/// <returns></returns>
public static IList <St_good> GetBuyHotStocks(int limit)
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_good>(cat => cat.Limit(limit).Where(ct => ct.Status > 0 && ct.GoodsType == 1).OrderByDescending(ct => ct.ViewCount).OrderByDescending(ct => ct.Id)));
}
}
public static IList <St_user> GetUsers()
{
using (var db = DBService.OpenDb())
{
return(db.Select <St_user>());
}
}
public static void UpdateSubCatPath(int parentid, string parentoldpath, string parentnewpath)
{
if (parentid > 0 && !string.IsNullOrEmpty(parentoldpath) && !string.IsNullOrEmpty(parentnewpath))
{
using (var db = DBService.OpenDb())
{
var subs = db.Select <St_cat>(cc => cc.Path.StartsWith(parentoldpath));
if (subs != null && subs.Count > 0)
{
foreach (var sub in subs)
{
if (sub.Id != parentid)
{
int lv = 0;
string newpath = parentnewpath + sub.Id.ToString() + ",";
if (sub.Path.IndexOf(',') > -1)
{
newpath = parentnewpath + sub.Path.Substring(parentoldpath.Length);
var pa = newpath.Split(',');
if (pa.Length > 3)
{
lv = pa.Length - 3;
}
}
db.Update <St_cat>(new { Path = newpath, Level = lv }, cc => cc.Id == sub.Id);
}
}
}
}
}
}
public static void UpdateStockPics(int stockid, IList <int> picids)
{
if (stockid > 0 && picids != null && picids.Count > 0)
{
using (var db = DBService.OpenDb())
{
db.Update <St_pic>(new { RelateId = stockid }, cc => cc.PicType == 5 && cc.AddTime > DateTime.Now.AddHours(-6) && Sql.In(cc.Id, picids));
}
}
}
public static void UpdateStockCatPath(int catid, string catpath)
{
if (catid > 0 && !string.IsNullOrEmpty(catpath))
{
using (var db = DBService.OpenDb())
{
db.Update <St_good>(new { CatPath = catpath }, cc => cc.CatId == catid);
}
}
}
public static void UpdateViewCount(int stockid)
{
if (stockid > 0)
{
using (var db = DBService.OpenDb())
{
db.Update <St_good>("ViewCount=ViewCount+1", "Id=" + stockid);
}
}
}