/// <summary>
/// 阿里促销商品
/// </summary>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <param name="title"></param>
/// <returns></returns>
public PagedResult <AliGoodsEntity> AliGoodsPagedList(int page, int limit, string title)
{
try
{
using (KeepDbContext context = new KeepDbContext())
{
var total = new SqlParameter("@total", SqlDbType.Int);
total.Direction = ParameterDirection.Output;
var pageParam = new SqlParameter("@page", SqlDbType.Int);
pageParam.Value = page;
var limitParam = new SqlParameter("@limit", SqlDbType.Int);
limitParam.Value = limit;
var titleParam = new SqlParameter("@title", SqlDbType.VarChar);
titleParam.Value = title;
var parm = new SqlParameter[] { pageParam, limitParam, titleParam, total };
string sql = @"EXEC [dbo].[SP_QueryAliGoodsList] @page,@limit,@title,@total OUTPUT";
List <AliGoodsEntity> list = context.Database.SqlQuery <AliGoodsEntity>(sql, parm).ToList();
return(new PagedResult <AliGoodsEntity>(page, limit, (int)total.Value, list));
}
}
catch (Exception ex)
{
return(new PagedResult <AliGoodsEntity>(1, ex.Message));
}
}
private static void AddNotes(KeepDbContext context)
{
context.Notes.Add(new Note {
CategoryId = 1, ReminderId = 1, NoteTitle = "Technology", NoteContent = "ASP.NET Core", NoteStatus = "Started", CreatedBy = "John"
});
context.SaveChanges();
}
public static void PopulateTestData(KeepDbContext context)
{
AddUsers(context);
AddCategories(context);
AddReminders(context);
AddNotes(context);
}
private static void AddReminders(KeepDbContext context)
{
context.Reminders.Add(new Reminder {
ReminderName = "Email", ReminderDescription = "Email reminder", ReminderType = "notification", CreatedBy = "John"
});
context.SaveChanges();
}
private static void AddUsers(KeepDbContext context)
{
context.Users.Add(new User {
UserId = "John", UserName = "******", Password = "******", Contact = "9812345670"
});
context.SaveChanges();
}
private static void AddCategories(KeepDbContext context)
{
context.Categories.Add(new Category {
CategoryName = "Testing", CategoryDescription = "Unit Testing", CategoryCreatedBy = "John"
});
context.SaveChanges();
}
public DatabaseFixture()
{
var options = new DbContextOptionsBuilder <KeepDbContext>()
.UseInMemoryDatabase(databaseName: "NoteDB")
.Options;
//Initializing DbContext with InMemory
context = new KeepDbContext(options);
// Insert seed data into the database using one instance of the context
SeedData.PopulateTestData(context);
}
/// <summary>
/// 周期列表
/// </summary>
/// <returns></returns>
public ListResult <CycleEntity> CycleList()
{
try
{
KeepDbContext context = new KeepDbContext();
var list = context.Cycle.ToList();
return(new ListResult <CycleEntity>(list));
}
catch (Exception ex)
{
ComLogger.ErrorTag("CycleList", ex.Message);
return(new ListResult <CycleEntity>(1, ex.Message));
}
}
/// <summary>
/// 新增记录
/// </summary>
/// <param name="params"></param>
/// <returns></returns>
public ServiceResult CreateBill(BillEntity @params)
{
try
{
using (KeepDbContext context = new KeepDbContext()) {
@params.Id = Guid.NewGuid();
@params.CreateDate = DateTime.Now;
context.Bill.Add(@params);
context.SaveChanges();
return(new ServiceResult());
}
}
catch (Exception ex)
{
ComLogger.ErrorTag("CreateBill", ex.InnerException.StackTrace);
return(new ServiceResult(1, ex.Message));
}
}
/// <summary>
/// 获取周期分类图标数据
/// </summary>
/// <param name="Cycle"></param>
/// <returns></returns>
public ListResult <ChartsEntity> PieChartsList(Guid Cycle)
{
try
{
using (KeepDbContext context = new KeepDbContext())
{
string sql = string.Format(@"select sum(price) as Price,Category from bill
where Cycle=@Cycle
group by Category");
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@Cycle", Cycle));
List <ChartsEntity> result = context.Database.SqlQuery <ChartsEntity>(sql, param.ToArray()).ToList();
return(new ListResult <ChartsEntity>(result));
}
}
catch (Exception ex)
{
return(new ListResult <ChartsEntity>(1, ex.Message));
}
}
/// <summary>
/// 记录分页列表
/// </summary>
/// <param name="cycle"></param>
/// <param name="category"></param>
/// <param name="mode"></param>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <returns></returns>
public PagedResult <BillView> BillPagedList(Guid?cycle, string category, string mode, int page, int limit)
{
try
{
using (KeepDbContext context = new KeepDbContext())
{
var total = new SqlParameter("@total", SqlDbType.Int);
total.Direction = ParameterDirection.Output;
var cycleParam = new SqlParameter("@cycle", SqlDbType.UniqueIdentifier);
cycleParam.Value = cycle;
if (cycle == null)
{
cycleParam.Value = DBNull.Value;
}
var categoryParam = new SqlParameter("@category", SqlDbType.NVarChar);
categoryParam.Value = category;
if (category == null)
{
categoryParam.Value = DBNull.Value;
}
var modeParam = new SqlParameter("@mode", SqlDbType.NVarChar);
modeParam.Value = mode;
if (mode == null)
{
modeParam.Value = DBNull.Value;
}
var pageParam = new SqlParameter("@page", SqlDbType.Int);
pageParam.Value = page;
var limitParam = new SqlParameter("@limit", SqlDbType.Int);
limitParam.Value = limit;
var parm = new SqlParameter[] { cycleParam, categoryParam, modeParam, pageParam, limitParam, total };
string sql = @"EXEC [dbo].[SP_QueryBill] @cycle,@category,@mode,@page,@limit,@total OUTPUT";
List <BillView> list = context.Database.SqlQuery <BillView>(sql, parm).ToList();
return(new PagedResult <BillView>(page, limit, (int)total.Value, list));
}
}
catch (Exception ex)
{
return(new PagedResult <BillView>(1, ex.Message));
}
}
/// <summary>
/// 最近6月趋势图
/// </summary>
/// <returns></returns>
public ListResult <ChartsEntity> LineChartsList()
{
try
{
using (KeepDbContext context = new KeepDbContext())
{
string sql = string.Format(@"select top 6 sum(price) as Price,BillName from bill
inner join Cycle
on Cycle.Id=bill.Cycle
group by billname
order by Price
");
List <ChartsEntity> result = context.Database.SqlQuery <ChartsEntity>(sql).ToList();
return(new ListResult <ChartsEntity>(result));
}
}
catch (Exception ex)
{
return(new ListResult <ChartsEntity>(1, ex.Message));
}
}
/// <summary>
/// 删除记录
/// </summary>
/// <param name="params"></param>
/// <returns></returns>
public ServiceResult DeleteBill(BillEntity @params)
{
try
{
using (KeepDbContext context = new KeepDbContext())
{
BillEntity entity = context.Bill.SingleOrDefault(x => x.Id == @params.Id);
if (entity == null)
{
return(new ServiceResult(1, "entity is null"));
}
context.Bill.Remove(entity);
context.SaveChanges();
return(new ServiceResult());
}
}
catch (Exception ex)
{
ComLogger.ErrorTag("DeleteBill", ex.InnerException.StackTrace);
return(new ServiceResult(1, ex.Message));
}
}
public UsersController(KeepDbContext context)
{
_context = context;
}
public LabelsController(KeepDbContext context)
{
_context = context;
}
public ItemsController(KeepDbContext context)
{
_context = context;
}
public void Dispose()
{
context = null;
}
public ListsController(KeepDbContext context)
{
_context = context;
}
