/// <summary>
/// 通过category_id得到Category
/// </summary>
/// <param name="catId"></param>
/// <returns></returns>
public static Category GetCategoryBycatId(long catId)
{
using (var db = new SQLiteDb())
{
return(db.Categories.FirstOrDefault(x => x.category_id == catId));
}
}
/// <summary>
/// 获取所有分类
/// </summary>
/// <returns></returns>
public static List <Category> GetAllCategories()
{
using (var db = new SQLiteDb())
{
return(db.Categories.ToList());
}
}
/// <summary>
/// 通过id查找dm
/// </summary>
/// <param name="dmId"></param>
/// <returns></returns>
public static Dm GetDmById(long dmId)
{
using (var db = new SQLiteDb())
{
return(db.Dms.FirstOrDefault(x => x.advertisement_id == dmId));
}
}
/// <summary>
/// 通过advertisement_id查找ad
/// </summary>
/// <param name="adId"></param>
/// <returns></returns>
public static Ad GetAdById(long adId)
{
using (var db = new SQLiteDb())
{
return(db.Ads.FirstOrDefault(x => x.advertisement_id == adId));
}
}
/// <summary>
/// 插入多条dm
/// </summary>
/// <param name="listDm"></param>
/// <returns></returns>
public static int InsertDms(List <Dm> listDm)
{
using (var db = new SQLiteDb())
{
db.Dms.AddRange(listDm);
return(db.SaveChanges());
}
}
/// <summary>
/// 插入多条ad
/// </summary>
/// <param name="listAd"></param>
/// <returns></returns>
public static int InsertAds(List <Ad> listAd)
{
using (var db = new SQLiteDb())
{
db.Ads.AddRange(listAd);
int count = db.SaveChanges();
return(count);
}
}
/// <summary>
/// 插入一条ad
/// </summary>
/// <param name="ad"></param>
/// <returns></returns>
public static int InsertAd(Ad ad)
{
using (var db = new SQLiteDb())
{
db.Ads.Add(ad);
int count = db.SaveChanges();
return(count);
}
}
/// <summary>
/// 更新dm
/// </summary>
/// <param name="dm"></param>
/// <returns></returns>
public static int UpdateDm(Dm dm)
{
using (var db = new SQLiteDb())
{
db.Dms.Add(dm);
db.Entry <Dm>(dm).State = System.Data.Entity.EntityState.Modified;
return(db.SaveChanges());
}
}
/// <summary>
/// 插入一条dm
/// </summary>
/// <param name="dm"></param>
/// <returns></returns>
public static int InsertDm(Dm dm)
{
using (var db = new SQLiteDb())
{
db.Dms.Add(dm);
int count = db.SaveChanges();
return(count);
}
}
/// <summary>
/// 更新ad
/// </summary>
/// <param name="ad"></param>
/// <returns></returns>
public static int UpdateAd(Ad ad)
{
using (var db = new SQLiteDb())
{
db.Ads.Add(ad);
db.Entry <Ad>(ad).State = System.Data.Entity.EntityState.Modified;
int count = db.SaveChanges();
return(count);
}
}
/// <summary>
/// 插入一条Category
/// </summary>
/// <param name="category"></param>
/// <returns></returns>
public static int InsertCategory(Category category)
{
using (SQLiteDb db = new SQLiteDb())
{
//db.Entry(category);
db.Categories.Add(category);
//db.Set<Category>().Add(category);
int count = db.SaveChanges();
return(count);
}
}
//public static Dm DeleteDmById(long dmId)
//{
// using (var db = new SQLiteDb())
// {
// return db.Dms.Remove(db.Dms.FirstOrDefault(x => x.advertisement_id == dmId));
// }
//}
//public static Dm DeleteDmById(Dm dm)
//{
// using (var db = new SQLiteDb())
// {
// return db.Dms.Remove(dm);
// }
//}
/// <summary>
/// 删除过期的dm
/// </summary>
/// <returns></returns>
public static void DeleteDmByOutDate()
{
using (var db = new SQLiteDb())
{
List <Dm> list = GetDmsByOutDate();
foreach (Dm dm in list)
{
db.Dms.Attach(dm);
db.Dms.Remove(dm);
}
db.SaveChanges();
}
}
/// <summary>
/// 删除过期的ad
/// </summary>
/// <returns></returns>
public static void DeleteAdByOutDate()
{
using (var db = new SQLiteDb())
{
List <Ad> list = GetAdsByOutDate();
foreach (Ad ad in list)
{
db.Ads.Attach(ad);
db.Ads.Remove(ad);
}
//db.Ads.RemoveRange(list);
db.SaveChanges();
}
}
/// <summary>
/// 查找过期的dm
/// </summary>
/// <returns></returns>
public static List <Dm> GetDmsByOutDate()
{
try
{
using (var db = new SQLiteDb())
{
return(db.Dms.ToList().Where(x => x.puton_time > DateTime.Now || DateTime.Now > x.putoff_time).ToList());
}
}
catch (Exception ex)
{
log.Error("GetDmsByOutDate error:", ex);
return(new List <Dm>());
}
}
/// <summary>
/// 根据category_id查找有效时间范围内的dm
/// </summary>
/// <param name="catId"></param>
/// <returns></returns>
public static List <Dm> GetDmsBycatId(int catId)
{
try
{
using (var db = new SQLiteDb())
{
return(db.Dms.Where(x => x.puton_time <= DateTime.Now && DateTime.Now <= x.putoff_time && x.category_id == catId).OrderByDescending(x => x.advertisement_id).ToList());
}
}
catch (Exception ex)
{
log.Error("GetDmsBycatId error:", ex);
return(new List <Dm>());
}
}
/// <summary>
/// 查找有效时间范围内的dm
/// </summary>
/// <returns></returns>
public static List <Dm> GetDms()
{
try
{
DeleteDmByOutDate(); //先删除过期Dm
using (var db = new SQLiteDb())
{
return(db.Dms.ToList().Where(x => x.puton_time <= DateTime.Now && DateTime.Now <= x.putoff_time).OrderBy(x => x.advertisement_id).ToList());
}
}
catch (Exception ex)
{
log.Error("GetDms error:", ex);
return(new List <Dm>());
}
}
/// <summary>
/// 查找有效时间范围内的ad
/// </summary>
/// <returns></returns>
public static List <Ad> GetAds()
{
try
{
DeleteAdByOutDate(); //先删除过期广告
using (var db = new SQLiteDb())
{
var ads = db.Ads.ToList().Where(x => x.puton_time <= DateTime.Now && DateTime.Now <= x.putoff_time).OrderBy(x => x.advertisement_id).ToList();
return(ads);
}
}
catch (Exception ex)
{
log.Error("GetAds error:", ex);
return(new List <Ad>());
}
}