public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().Select <object>("*").ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Student>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Select(it => it.SchoolId).ToList();
var sum2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Sum((st, sc) => sc.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var count = db.Queryable <Student>().Count(it => it.Id > 0);
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var joinSql = db.Queryable("student", "s").OrderBy("id").Select("id,name").ToPageList(1, 2);
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var getSqlList = db.Queryable <Student>().AS("(select * from student) t").ToList();
var getUnionAllList = db.UnionAll(db.Queryable <Student>().Where(it => it.Id == 1), db.Queryable <Student>().Where(it => it.Id == 2)).ToList();
var getUnionAllList2 = db.UnionAll(db.Queryable <Student>(), db.Queryable <Student>()).ToList();
var test1 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Where(st => st.CreateTime > SqlFunc.GetDate()).Select((st, sc) => SqlFunc.ToInt64(sc.Id)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
var test3 = db.Queryable <DataTestInfo2>().Select(it => it.Bool1).ToSql();
var test4 = db.Queryable <DataTestInfo2>().Select(it => new { b = it.Bool1 }).ToSql();
DateTime?result = DateTime.Now;
var test5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToList();
}
/// <summary>
/// 获取项目燃尽图数据
/// </summary>
/// <param name="projectId"></param>
/// <returns></returns>
public List <Dictionary <string, object> > getProjectBurndownChart(int projectId)
{
var project = Db.Queryable <Project>().Where(u => u.Id == projectId).Single();
var startDate = project.StartTime;
var endDate = project.EndTime;
var tempDate = project.StartTime;
var result = new List <Dictionary <string, object> >();
var remain = 100.0;
while (tempDate <= endDate && tempDate <= DateTime.Now)
{
// 得出项目周期内 每天的燃尽数据
var query = Db.Queryable <Task, TaskRecord>((t, tr) => new object[]
{
JoinType.Left, t.Id == tr.TaskId
}).Where((t, tr) => t.ParentId == 0 && SqlFunc.DateIsSame(tempDate, tr.CreateTime)).Sum((t, tr) => tr.Percent * t.Weight * 0.01);
remain -= query;
var dict = new Dictionary <string, object>
{
{ "date", tempDate.ToString("yyyy-MM-dd") },
{ "value", remain }
};
result.Add(dict);
tempDate = tempDate.AddDays(1);
}
return(result);
}
/// <summary>
/// 获取项目动态 提交情况
/// </summary>
/// <param name="projectId"></param>
/// <returns></returns>
public Dictionary <string, object> getTaskRecordByProjectId(QueryTaskRecordRequest request)
{
var dateQuery = Db.Queryable <TaskRecord, Task, Project>((tr, t, p) => new object[]
{
JoinType.Left, tr.TaskId == t.Id, JoinType.Left, t.ProjectId == p.Id
}).Where((tr, t, p) => t.ProjectId == request.projectOrTaskId && tr.Status == 1);
if (!string.IsNullOrEmpty(request.startTime) && !string.IsNullOrEmpty(request.endTime))
{
dateQuery = dateQuery.Where((tr, t, p) =>
SqlFunc.Between(tr.CreateTime, request.startTime, request.endTime));
}
var dateList = dateQuery.GroupBy((tr, t, p) => SqlFunc.DateValue(tr.CreateTime, DateType.Year) + "-" +
SqlFunc.DateValue(tr.CreateTime, DateType.Month) + "-" +
SqlFunc.DateValue(tr.CreateTime, DateType.Day))
.OrderBy((tr, t, p) => tr.CreateTime)
.Select((tr, t, p) => tr.CreateTime).ToList();
var dict = new Dictionary <string, object>();
foreach (var date in dateList)
{
var taskRecordList = Db.Queryable <TaskRecord>().Where(u => SqlFunc.DateIsSame(date, u.CreateTime))
.ToList();
dict.Add(date.ToString("yyyy-MM-dd"), taskRecordList);
}
return(dict);
}
private void TakeSnapshot()
{
using (SqlSugarClient dbClient = new DBClient().GetClient())
{
dbClient.Ado.UseTran(() => { });
List <string> accounts = null;
dbClient.Ado.UseTran(() =>
{
accounts = dbClient.Queryable <table_商户账号>().Select(it => it.商户ID).ToList();
});
if (accounts == null)
{
return;
}
DateTime current = DateTime.Now;
DateTime dateTime = new DateTime(current.Year, current.Month, current.Day, 23, 59, 0);
foreach (string id in accounts)
{
dbClient.Ado.UseTran(() =>
{
var balance = dbClient.Queryable <table_商户账号>().Where(it => it.商户ID == id).Select(it => it.提款余额).First();
var reverse = dbClient.Queryable <table_商户明细提款>()
.Where(it => it.类型 == "冲正" && SqlFunc.DateIsSame(it.时间创建, current) && it.商户ID == id)
.Sum(it => it.交易金额);
var deposit = dbClient.Queryable <table_商户明细充值>()
.Where(it => it.充值类型 == "充值提款余额" && it.状态 == "成功" && SqlFunc.DateIsSame(it.时间创建, current) && it.商户ID == int.Parse(id))
.Sum(it => it.充值金额);
var withdraw = dbClient.Queryable <table_商户明细提款>()
.Where(it => it.类型 == "提款" && it.状态 == "成功" && SqlFunc.DateIsSame(it.时间创建, current) && it.商户ID == id)
.Sum(it => it.交易金额);
var tableId = dbClient.Queryable <table_snapshot>()
.Where(it => it.MerchantID == id)
.OrderBy(it => it.Id, OrderByType.Desc)
.Select(it => it.Id).First();
if (tableId.HasValue)
{
dbClient.Updateable <table_snapshot>()
.SetColumns(it => it.Balance == balance.Value)
.SetColumns(it => it.Diff == balance.Value + it.Reverse + it.Deposit - it.Withdraw)
.Where(it => it.Id == tableId.Value).ExecuteCommand();
}
table_snapshot snapshot = new table_snapshot()
{
MerchantID = id,
Reverse = reverse.HasValue ? reverse.Value : 0,
Deposit = deposit.HasValue ? deposit.Value : 0,
Withdraw = withdraw.HasValue ? withdraw.Value : 0,
CreateTime = DateTime.Now
};
dbClient.Insertable(snapshot).ExecuteCommand();
});
}
}
}
public static void Easy()
{
var db = GetInstance();
var getAll = db.Queryable <Student>().ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Sum(it => it.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
}
/// <summary>
/// 查询今日待办事项，销售统计
/// </summary>
/// <returns></returns>
public Task <ApiResult <BackLogReport> > GetBackLogReport()
{
var res = new ApiResult <BackLogReport>()
{
statusCode = (int)ApiEnum.Error
};
try
{
DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00");
string startWeek = Utils.GetMondayDate().ToShortDateString();
string endWeek = Utils.GetSundayDate().AddDays(1).ToShortDateString();
//昨天销售额
var yesterDayTime = Convert.ToDateTime(DateTime.Now.ToShortDateString() + " 00:00:00");
var yesterDay = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.DateIsSame(m.AddDate, yesterDayTime)).Sum(m => m.RealMoney);
//上周销售额
var yesterWeekTimeStart = Utils.GetMondayDate().AddDays(-7).ToShortDateString();
var yesterWeekTimeEnd = Utils.GetMondayDate().AddDays(-1).ToShortDateString();
var yesterWeek = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.Between(m.AddDate, yesterWeekTimeStart, yesterWeekTimeEnd)).Sum(m => m.RealMoney);
//上个月销售额
var yesterMonthTime = DateTime.Now.AddMonths(-1);
var yesterMonth = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.DateIsSame(m.AddDate, yesterMonthTime, DateType.Month)).Sum(m => m.RealMoney);
var model = new BackLogReport
{
//今日返货数量
ReturnCount = ErpReturnOrderDb.Count(m => SqlFunc.DateIsSame(m.AddDate, dayTime)),
//今日退货数量
BackCount = ErpBackGoodsDb.Count(m => SqlFunc.DateIsSame(m.AddDate, dayTime)),
//库存报警
StockPoliceCount = ErpGoodsSkuDb.Count(m => m.StockSum < 10),
//今日加入会员
JoinUserCount = ErpShopUserDb.Count(m => SqlFunc.DateIsSame(m.RegDate, dayTime)),
//今日销售金额
DaySaleMoney = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.DateIsSame(m.AddDate, dayTime)).Sum(m => m.RealMoney),
//本周销售金额
WeekSaleMoney = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.Between(m.AddDate, Utils.GetMondayDate(), Utils.GetMondayDate().AddDays(7))).Sum(m => m.RealMoney),
//本月销售金额
MonthSaleMoney = Db.Queryable <ErpSaleOrder>().Where(m => SqlFunc.DateIsSame(m.AddDate, dayTime, DateType.Month)).Sum(m => m.RealMoney)
};
//日同比 同比增长率=（本年的指标值-去年同期的值）÷去年同期的值*100%
model.DayOnYear = yesterDay == 0?0:Convert.ToDouble((model.DaySaleMoney - yesterDay) / yesterDay * 100);
//周同比
model.WeekOnYear = yesterWeek == 0 ? 0 : Convert.ToDouble((model.WeekSaleMoney - yesterWeek) / yesterWeek * 100);
//月同比
model.MonthOnYear = yesterMonth == 0 ? 0 : Convert.ToDouble((model.MonthSaleMoney - yesterMonth) / yesterMonth * 100);
res.data = model;
res.statusCode = (int)ApiEnum.Status;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
}
return(Task.Run(() => res));
}
public static void Init()
{
SqlSugarClient db = new SqlSugarClient(
new ConnectionConfig()
{
ConnectionString = Config.ConnectionString,
DbType = DbType.MySql, //设置数据库类型
IsAutoCloseConnection = true, //自动释放数据务，如果存在事务，在事务结束后释放
InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息
});
db.Aop.OnError = (exp) =>//SQL报错
{
string sql = exp.Sql;
//exp.sql 这样可以拿到错误SQL
};
//db.DbMaintenance.CreateDatabase();
db.Deleteable <Order>().ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = DateTime.Now.Date.AddDays(-1), Name = "1a", Price = 1, CustomId = 1
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = DateTime.Now.Date.AddDays(-1).AddHours(23), Name = "1a", Price = 1, CustomId = 1
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = DateTime.Now.Date.AddDays(1), Name = "1a", Price = 1, CustomId = 1
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = DateTime.Now.Date.AddDays(2), Name = "1a", Price = 1, CustomId = 1
}).ExecuteCommand();
var s = DateTime.Now.Date.AddMilliseconds(-1);
var list = db.Queryable <Order>().Where(it => SqlFunc.DateIsSame(it.CreateTime, s)).ToList();
var s2 = DateTime.Now.Date.AddDays(-1);
var lists = db.Queryable <Order>().Where(it => SqlFunc.DateIsSame(it.CreateTime, s2)).ToSql();
Console.ReadKey();
}
/// <summary>
/// 查询营业额
/// </summary>
/// <returns></returns>
public Task <ApiResult <DayTurnover> > GetTurnover(PageParm parm, AppSearchParm searchParm)
{
var res = new ApiResult <DayTurnover>();
try
{
DateTime now = DateTime.Now;
DateTime d1 = new DateTime(now.Year, now.Month, 1);
DateTime d2 = d1.AddMonths(1);
DateTime dayTime = Convert.ToDateTime(now.AddDays(1).ToShortDateString() + " 00:00:00");
//订单数
var orderSum = Db.Queryable <ErpSaleOrder>()
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.btime),
m => SqlFunc.Between(m.AddDate, Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)))
.WhereIF(parm.types == 1, m => SqlFunc.DateIsSame(m.AddDate, dayTime))
.WhereIF(parm.types == 2, m => SqlFunc.Between(m.AddDate, d1, d2))
.Count();
//订单金额
var orderMoney = Db.Queryable <ErpSaleOrder>()
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.btime),
m => SqlFunc.Between(m.AddDate, Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)))
.WhereIF(parm.types == 1, m => SqlFunc.DateIsSame(m.AddDate, dayTime))
.WhereIF(parm.types == 2, m => SqlFunc.Between(m.AddDate, d1, d2))
.Sum(m => m.RealMoney);
//查询退单金额
var backMoney = Db.Queryable <ErpBackGoods>()
.Where(m => m.Status == 1)
.WhereIF(!string.IsNullOrEmpty(parm.guid), m => m.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.btime) && !string.IsNullOrEmpty(searchParm.btime),
m => SqlFunc.Between(m.AddDate, Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)))
.WhereIF(parm.types == 1, m => SqlFunc.DateIsSame(m.AddDate, dayTime))
.WhereIF(parm.types == 2, m => SqlFunc.Between(m.AddDate, d1, d2))
.Sum(m => m.BackMoney);
res.data = new DayTurnover()
{
OrderSum = orderSum,
Money = orderMoney - backMoney
};
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
private void DateIsSameByType()
{
var x2 = DateTime.Now;
Expression <Func <Student, bool> > exp = it => SqlFunc.DateIsSame(x2, x2, DateType.Millisecond);
OracleExpressionContext expContext = new OracleExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, " (ROUND(TO_NUMBER(:MethodConst0 - :MethodConst1) * 86400000) = 0) ", new List <SugarParameter>()
{
new SugarParameter(":MethodConst0", x2), new SugarParameter(":MethodConst1", x2)
}, "DateIsSameByType error");
}
private void DateIsSameByDay()
{
var x2 = DateTime.Now;
Expression <Func <Student, bool> > exp = it => SqlFunc.DateIsSame(x2, x2);
SqliteExpressionContext expContext = new SqliteExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, " Cast((JulianDay(@MethodConst0) - JulianDay(@MethodConst1)) *1 As INTEGER)=0 ", new List <SugarParameter>()
{
new SugarParameter("@MethodConst0", x2), new SugarParameter("@MethodConst1", x2)
}, "DateIsSameDay error");
}
private void DateIsSameByType()
{
var x2 = DateTime.Now;
Expression <Func <Student, bool> > exp = it => SqlFunc.DateIsSame(x2, x2, DateType.Millisecond);
MySqlExpressionContext expContext = new MySqlExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, " (TIMESTAMPDIFF(Millisecond,@MethodConst0,@MethodConst1)=0) ", new List <SugarParameter>()
{
new SugarParameter("@MethodConst0", x2), new SugarParameter("@MethodConst1", x2)
}, "DateIsSameByType error");
}
private void DateIsSameByDay()
{
var x2 = DateTime.Now;
Expression <Func <Student, bool> > exp = it => SqlFunc.DateIsSame(x2, x2);
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(DATEDIFF(day,@MethodConst0,@MethodConst1)=0) ", new List <SugarParameter>()
{
new SugarParameter("@MethodConst0", x2), new SugarParameter("@MethodConst1", x2)
}, "DateIsSameDay error");
}
/// <summary>
/// 添加一条数据
/// </summary>
/// <returns></returns>
public async Task <ApiResult <string> > AddAsync(ErpPurchase parm)
{
var res = new ApiResult <string>()
{
data = "1", statusCode = 200
};
try
{
//查询今天又多少条采购单，并生成订单号
var dayCount = ErpPurchaseDb.Count(m => SqlFunc.DateIsSame(m.AddDate, DateTime.Now));
parm.Guid = Guid.NewGuid().ToString();
parm.Number = Utils.PurchaseNumber(1001 + dayCount);
//分析商品并保存
var list = new List <ErpPurchaseGoods>();
if (!string.IsNullOrEmpty(parm.GoodsList))
{
list = JsonConvert.DeserializeObject <List <ErpPurchaseGoods> > (parm.GoodsList);
for (int i = 0; i < list.Count; i++)
{
var item = list[i];
if (string.IsNullOrEmpty(item.Number) && string.IsNullOrEmpty(item.Name))
{
list.Remove(item);
}
else
{
item.Guid = Guid.NewGuid().ToString();
item.PurchaseGuid = parm.Guid;
parm.Money += item.Quantity * item.Price;
}
}
}
Db.Ado.BeginTran();
Db.Insertable(list).ExecuteCommand();
Db.Insertable(parm).ExecuteCommand();
Db.Ado.CommitTran();
}
catch (Exception ex)
{
Db.Ado.RollbackTran();
res.statusCode = (int)ApiEnum.Error;
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
}
return(await Task.Run(() => res));
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().ToList();
var getTop2 = db.Queryable <Student>().Take(2).ToList();//TOP2
var getLike = db.Queryable <Student>().Where(it => it.Name.Contains("a")).ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 2).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Sum(it => it.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var asCount = db.Queryable <object>().AS("student").Count();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var unionAll = db.UnionAll <Student>(db.Queryable <Student>(), db.Queryable <Student>());
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
}
/// <summary>
/// 获得一条信息
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public async Task <ApiResult <ErpSkuLossOrder> > GetByGuidAsync(string parm)
{
var model = ErpSkuLossOrderDb.GetSingle(m => m.Guid == parm);
if (model == null)
{
var dayCount = ErpSkuLossOrderDb.Count(m => SqlFunc.DateIsSame(m.AddDate, DateTime.Now));
model = new ErpSkuLossOrder()
{
Number = "SL-" + DateTime.Now.ToString("yyyyMMdd") + "-" + (1001 + dayCount)
};
}
var res = new ApiResult <ErpSkuLossOrder>
{
statusCode = 200,
data = model ?? new ErpSkuLossOrder()
{
}
};
return(await Task.Run(() => res));
}
/// <summary>
/// 添加一条数据
/// </summary>
/// <returns></returns>
public async Task <ApiResult <string> > AddAsync(ErpBackGoods parm)
{
var res = new ApiResult <string>()
{
data = "1", statusCode = (int)ApiEnum.Error
};
try
{
DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00");
parm.Guid = Guid.NewGuid().ToString();
//查询今天退货数量
var dayCount = ErpBackGoodsDb.Count(m => SqlFunc.DateIsSame(m.AddDate, dayTime));
parm.Number = "BO-" + DateTime.Now.ToString("yyyyMMdd") + "-" + (1001 + dayCount);
//根据条形码查询唯一编号
var goodSku = ErpGoodsSkuDb.GetSingle(m => m.Code == parm.GoodsGuid);
if (goodSku != null)
{
parm.GoodsGuid = goodSku.Guid;
}
else
{
res.message = "商品不存在~";
return(await Task.Run(() => res));
}
//判断退货商品，金额是否大于订单金额， 以及商品数量，是否大于订单出售数量
var orderModel = ErpSaleOrderDb.GetSingle(m => m.Number == parm.OrderNumber);
if (orderModel == null)
{
res.message = "订单不存在~";
return(await Task.Run(() => res));
}
if (parm.BackMoney > orderModel.RealMoney)
{
res.message = "退货金额不能大于订单金额~";
return(await Task.Run(() => res));
}
//判断是否存在
var isExt = ErpBackGoodsDb.IsAny(m => m.ShopGuid == parm.ShopGuid && m.GoodsGuid == parm.GoodsGuid && m.OrderNumber == parm.OrderNumber);
if (isExt)
{
res.message = "该退货信息已存在~";
return(await Task.Run(() => res));
}
//根据订单查询商品数量是否满足
var orderGoodsModel = ErpSaleOrderGoodsDb.GetSingle(m => m.OrderNumber == parm.OrderNumber && m.GoodsGuid == parm.GoodsGuid && m.ShopGuid == parm.ShopGuid);
if (orderGoodsModel == null)
{
res.message = "该商品在该订单号中不存在~";
return(await Task.Run(() => res));
}
if (orderGoodsModel.Counts < parm.BackCount)
{
res.message = "退货商品数量不能大于订单购买数量~";
return(await Task.Run(() => res));
}
var result = Db.Ado.UseTran(() =>
{
//修改加盟商条形码里面的库存 退货=加盟商库存增加
Db.Updateable <ErpShopSku>()
.UpdateColumns(m => new ErpShopSku()
{
Stock = m.Stock + parm.BackCount, Sale = m.Sale - parm.BackCount
})
.Where(m => m.ShopGuid == parm.ShopGuid && m.SkuGuid == goodSku.Guid)
.ExecuteCommand();
//根据商品编号，修改平台的销售数量
Db.Updateable <ErpGoodsSku>().UpdateColumns(m => new ErpGoodsSku()
{
SaleSum = m.SaleSum - parm.BackCount
})
.Where(m => m.Guid == goodSku.Guid).ExecuteCommand();
//修改订单-商品详情表中的退货数量 增加
orderGoodsModel.BackCounts = orderGoodsModel.BackCounts + parm.BackCount;
Db.Updateable(orderGoodsModel).ExecuteCommand();
//增加一条退货信息
Db.Insertable(parm).ExecuteCommand();
});
res.statusCode = (int)ApiEnum.Status;
if (!result.IsSuccess)
{
res.statusCode = (int)ApiEnum.Error;
res.message = result.ErrorMessage;
}
}
catch (Exception ex)
{
res.statusCode = (int)ApiEnum.Error;
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
}
return(await Task.Run(() => res));
}
/// <summary>
/// 查询库存剩余数量和销售数量
/// 可根据店铺查询，日期，品牌
/// </summary>
/// <returns></returns>
public Task <ApiResult <Page <StockSaleNum> > > GetStockNumByShopAsync(PageParm parm, AppSearchParm searchParm)
{
var res = new ApiResult <Page <StockSaleNum> >();
try
{
var query = Db.Queryable <ErpShopSku, ErpGoodsSku>((t1, t2) => new object[] { JoinType.Left, t1.SkuGuid == t2.Guid })
.Where((t1, t2) => t1.ShopGuid == parm.guid)
.WhereIF(!string.IsNullOrEmpty(searchParm.brand), (t1, t2) => t2.BrankGuid == searchParm.brand)
.OrderByIF(parm.orderType == 1, (t1, t2) => t1.Sale, OrderByType.Desc)
.OrderByIF(parm.orderType == 2, (t1, t2) => t1.Stock, OrderByType.Desc)
.Select((t1, t2) => new StockSaleNum()
{
Guid = t2.Guid,
Code = t2.Code,
Brand = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.BrankGuid).Select(g => g.Name),
Style = SqlFunc.Subqueryable <SysCode>().Where(g => g.Guid == t2.StyleGuid).Select(g => g.Name),
Stock = t1.Stock,
returnSum = SqlFunc.Subqueryable <ErpReturnGoods>().Where(g => g.GoodsGuid == t1.SkuGuid && g.ShopGuid == parm.guid).Sum(g => g.ReturnCount)
}).ToPage(parm.page, parm.limit);
//根据日期查询
var guidList = query.Items.Select(m => m.Guid).ToList();
if (parm.types == 0)
{
//所有
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid);
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
if (parm.types == 1)
{
DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00");
//本日
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid &&
SqlFunc.DateIsSame(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), dayTime));
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
if (parm.types == 2)
{
//本月
DateTime now = DateTime.Now;
DateTime d1 = new DateTime(now.Year, now.Month, 1);
DateTime d2 = d1.AddMonths(1);
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid &&
SqlFunc.Between(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), d1, d2));
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
if (parm.types == 3)
{
//自定义时间
var dayList = ErpSaleOrderGoodsDb.GetList(m => guidList.Contains(m.GoodsGuid) && m.ShopGuid == parm.guid &&
SqlFunc.Between(SqlFunc.Subqueryable <ErpSaleOrder>().Where(g => g.Number == m.OrderNumber).Select(g => g.AddDate), Convert.ToDateTime(searchParm.btime), Convert.ToDateTime(searchParm.etime)));
foreach (var item in query.Items)
{
item.Sale = dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.Counts) - dayList.Where(m => m.GoodsGuid == item.Guid).Sum(m => m.BackCounts);
}
}
res.data = query;
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().Select <object>("*").ToList();
var getAll2 = db.Queryable <Student>().Select(it => it.Name.Substring(0, 4)).ToList();
var getAll22 = db.Queryable <Student>().ToDataTable();
var getAll222 = db.Queryable <Student>().ToJson();
var getAll22222 = db.Queryable <Student>().ToArray();
var getAll2222 = db.Queryable <Student>().OrderBy(it => it.Name.Length).ToJson();
var getAll3 = db.Queryable <Student>().OrderBy(it => new { it.Id, it.Name }).GroupBy(it => new { it.Id, it.Name }).Select <object>("id").ToList();
var getRandomList = db.Queryable <Student>().OrderBy(it => SqlFunc.GetRandom()).ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Student>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Select(it => it.SchoolId).ToList();
var sum2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Sum((st, sc) => sc.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var count = db.Queryable <Student>().Count(it => it.Id > 0);
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var joinSql = db.Queryable("student", "s").OrderBy("id").Select("id,name").ToPageList(1, 2);
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var getSqlList = db.Queryable <Student>().AS("(select * from student) t").ToList();
var getUnionAllList = db.UnionAll(db.Queryable <Student>().Where(it => it.Id == 1), db.Queryable <Student>().Where(it => it.Id == 2)).ToList();
var getUnionAllList2 = db.UnionAll(db.Queryable <Student>(), db.Queryable <Student>()).ToList();
var getUnionAllList3 = db.UnionAll(db.Queryable <Student>()
.Select(it => new Student {
Id = SqlFunc.ToInt32(1), Name = SqlFunc.ToString("2"), SchoolId = Convert.ToInt32(3)
})
, db.Queryable <Student>()
.Select(it => new Student {
Id = SqlFunc.ToInt32(11), Name = SqlFunc.ToString("22"), SchoolId = Convert.ToInt32(33)
}))
.Select(it => new Student()
{
Id = SqlFunc.ToInt32(111), Name = SqlFunc.ToString("222")
}).ToList();
var test1 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Where(st => st.CreateTime > SqlFunc.GetDate()).Select((st, sc) => SqlFunc.ToInt64(sc.Id)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
var test3 = db.Queryable <DataTestInfo2>().Select(it => it.Bool1).ToSql();
var test4 = db.Queryable <DataTestInfo2>().Select(it => new { b = it.Bool1 }).ToSql();
DateTime?result = DateTime.Now;
var test5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToList();
var test6 = db.Queryable <DataTestInfo2>().Where(it => SqlFunc.HasValue(it.Bool2) == true && SqlFunc.HasValue(it.Bool2) == true).ToList();
var test7 = db.Queryable <DataTestInfo2>().Where(it => SqlFunc.HasValue(it.Bool1) && SqlFunc.HasValue(it.Bool1)).ToList();
var test8 = db.Queryable <Student>().Where(it => SqlFunc.HasValue(it.SchoolId) && SqlFunc.HasValue(it.SchoolId)).ToList();
bool?b = false;
var test9 = db.Queryable <DataTestInfo2>().Where(it => it.Bool1 == b).ToList();
var test10 = db.Queryable <Student>(db.Queryable <Student>().Select(it => new Student()
{
Name = it.Name.Substring(0, 1)
})).GroupBy(it => it.Name).ToList();;
var test11 = db.Queryable <Student>().Distinct().ToList();
var test12 = db.Queryable <Student>().Distinct().Select(it => new Student {
Name = it.Name
}).ToList();
var test13 = db.Queryable <Student>().Where(it => DateTime.Parse("2014-1-1") == DateTime.Now).Where(it => Boolean.Parse("true") == true).ToList();
var test14 = db.Queryable <DataTestInfo2>().Where(it => Convert.ToBoolean(it.Bool1)).ToList();
var test15 = db.Queryable <DataTestInfo2>().Where(it => it.Bool2.Value && it.Bool1).ToList();
var test16 = db.Queryable <DataTestInfo2>().Where(it => !it.Bool2.Value && !it.Bool1).ToList();
var test17 = db.Queryable <DataTestInfo2>().Where(it => it.Bool1 && it.Bool1).ToList();
var test18 = db.Queryable <Student>().Where(it => it.SchoolId.HasValue && it.SchoolId.HasValue).ToList();
var test19 = db.Queryable <Student>().Where(it => it.SchoolId.HasValue && it.SchoolId.HasValue && it.SchoolId.HasValue).ToList();
var test20 = db.Queryable <Student>().Where(it => it.SchoolId.HasValue && SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var test21 = db.Queryable <Student>().Where(it => !it.SchoolId.HasValue && it.Name == "").ToList();
var test22 = db.Queryable <Student>().Where(it => !it.SchoolId.HasValue && it.SchoolId.HasValue).ToList();
var test23 = db.Queryable <Student>().Where(it => !(it.Id == 1) && it.Name == "").ToList();
var test24 = db.Queryable <Student>().Where(it => string.IsNullOrEmpty("a")).Where(it => string.IsNullOrEmpty(it.Name)).ToList();
var test25 = db.Queryable <Student>().Where(it => SqlFunc.IIF(it.Id == 0, 1, 2) == 1).ToList();
var test26 = db.Queryable <Student>().Where(it => (it.Name == null?2:3) == 1)
.ToList();
var test27 = db.Queryable <Student>().Select(x => new {
name = x.Name == null?"1":"2"
}).ToList();
var test28 = db.Queryable <Student>().Select(x => new Student {
Name = x.Name == null ? "1" : "2"
}).ToList();
var test29 = db.Queryable <Student>().Where(it => it.Id % 1 == 0).ToList();
var test30 = db.Queryable <Student>().Select(x => new Student
{
Name = x.Name ?? "a"
}).ToList();
var test31 = db.Queryable <Student>().Where(it => (it.Name ?? "a") == "a").ToList();
var test32 = db.Queryable <Student>().Where(it => it.Name == null ? true : false).ToList();
var test33 = db.Queryable <Student>().Where(it => SqlFunc.IIF(it.Name == null, true, false)).ToList();
var test34 = db.Queryable <Student>().Where(it => SqlFunc.IIF(it.Name == null || 1 == 1, true, false)).ToList();
var test35 = db.Queryable <Student>().Where(it => it.Id == 1 && SqlFunc.IF(it.Id == 1).Return(true).End(false)).ToList();
var test36 = db.Queryable <Student>().Where(it => it.Id == 1 && it.SchoolId.HasValue).ToList();
var test37 = db.Queryable <Student>().Where(it => it.Id == 1 && SqlFunc.IIF(it.Id == 1, true, false)).ToList();
var test38 = db.Queryable <Student>().Where(it => it.Id == 1 && SqlFunc.IIF(it.Id == 1, true, false) == true).ToList();
var test39 = db.Queryable <Student>().Where(it => it.Id == 1 && (it.Id == 1?true:false)).ToList();
var test40 = db.Queryable <Student>().Where(it => it.Id == 1 && Convert.ToBoolean("true")).ToList();
var test41 = db.Queryable <Student>().Where(it => it.Id == ((it.Id == 1?2:3) == 2?1:2)).ToList();
var test42 = db.Queryable <Student>().Where(it => new int[] { 1, 2, 3 }.Contains(1)).ToList();
var test43 = db.Queryable <Student>().Where(it => new int[] { 1, 2, 3 }.Contains(it.Id)).ToList();
var test44 = db.Queryable <Student>().Select(it => new {
x = SqlFunc.Subqueryable <DataTestInfo>().Where(x => false).Sum(x => x.Decimal1)
}).ToList();
decimal?p = null;
var test45 = db.Queryable <DataTestInfo>().Select(it => new {
x = p
}).ToList();
var test46 = db.Queryable <Student>().Where(it => it.CreateTime > SqlFunc.ToDate(DateTime.Now.Date)).ToList();
var test47 = db.Queryable <Student>().Where(it => string.IsNullOrEmpty(it.Name) == true).ToList();
var test48 = db.Queryable <Student>().Where(it => it.CreateTime != null).Where(it => SqlFunc.ToDate(it.CreateTime).Date == DateTime.Now.Date).ToList();
var test49 = db.Queryable <Student>().Where(it => it.CreateTime != null).Where(it => SqlFunc.ToDate(it.CreateTime).Year == DateTime.Now.Year).ToList();
var test50 = db.Queryable <Student>().Where(it => it.CreateTime != null).Where(it => SqlFunc.ToDate(it.CreateTime).Year == SqlFunc.GetDate().Year).ToList();
var test51 = db.Queryable <Student>().Select(it => new { x = SqlFunc.ToDate(it.CreateTime).Year + "-" }).ToList();
var test52 = db.Queryable <Student>().Select(it => SqlFunc.IsNull(it.CreateTime, SqlFunc.GetDate())).ToList();
var test53 = db.Queryable <Student>().Select(it => SqlFunc.IsNull(it.CreateTime, SqlFunc.GetDate())).First();
var test54 = db.Queryable <Student>().Where(it => it.CreateTime == test52.First().Value).ToList();
var test55 = db.Queryable <Student>().Select(it => new {
isAny = SqlFunc.Subqueryable <School>().Any()?1:2
}).ToList();
var test56 = db.Queryable <Student>().Select(it => new {
isAny = SqlFunc.Subqueryable <Student>().Any(),
isAny2 = SqlFunc.Subqueryable <Student>().Where(s => false).Any()
}).ToList();
var totalPage = 0;
var total = 0;
db.Queryable <Student>().ToPageList(1, 2, ref total, ref totalPage);
}
/// <summary>
/// 获取任务燃尽图数据
/// </summary>
/// <param name="projectId"></param>
/// <returns></returns>
public List <Dictionary <string, object> > getTaskBurndownChart(int taskId)
{
var task = Db.Queryable <Task>().Where(u => u.Id == taskId).Single();
// 获取所有子任务
var subTaskIdList = Db.Queryable <Task>().Where(u => u.CascadeId.Contains(task.CascadeId) &&
SqlFunc.Subqueryable <Task>().Where(t => t.ParentId == u.Id).Count() == 0).Select(u => u.Id).ToList();
var startDate = task.StartTime;
var endDate = task.EndTime;
var tempDate = task.StartTime;
var result = new List <Dictionary <string, object> >();
var remain = 100.0;
while (tempDate <= endDate && tempDate <= DateTime.Now)
{
// 得出项目周期内 每天的燃尽数据
var query = Db.Queryable <TaskRecord, Task>((tr, t) => new object[]
{
JoinType.Left, tr.TaskId == t.Id
}).Where((tr, t) => subTaskIdList.Contains(tr.TaskId) && tr.Status == 1 && SqlFunc.DateIsSame(tempDate, tr.CreateTime))
.Sum((tr, t) => t.Weight * tr.Percent * 0.01);
remain -= query;
var dict = new Dictionary <string, object>
{
{ "date", tempDate.ToString("yyyy-MM-dd") },
{ "value", remain }
};
result.Add(dict);
tempDate = tempDate.AddDays(1);
}
return(result);
}
/// <summary>
/// 首页工作台 项目提交情况统计
/// </summary>
/// <returns></returns>
public List <Dictionary <string, object> > getSubmissionStatus()
{
var sugarQueryableList = Db.Queryable <Project>();
// 如果是管理员 才查看所有项目 否则查询自己负责或者自己提交的项目
var roleId = Db.Queryable <UserRole>().Where(u => u.UserId == user.UserId).Select(u => u.RoleId).First();
if (roleId != 1)
{
sugarQueryableList = sugarQueryableList.Where(u => u.ChargeUserId == user.UserId || u.SubmitterId == user.UserId);
}
var projectList = sugarQueryableList.ToList();
var result = new List <Dictionary <string, object> >();
foreach (var project in projectList)
{
var dict = new Dictionary <string, object>();
var list = new List <double>();
// 统计当前星期的提交情况
for (var i = 0; i < 7; i++)
{
var time = DateTime.Now.AddDays(i - Convert.ToInt16(DateTime.Now.DayOfWeek) + 1);
var taskRecordList = Db.Queryable <TaskRecord, Task, Project>((tr, t, p) => new object[]
{ JoinType.Left, tr.TaskId == t.Id, JoinType.Left, t.ProjectId == p.Id }
).Where((tr, t, p) => SqlFunc.DateIsSame(tr.CreateTime, time) && p.Id == project.Id)
.Select((tr, t, p) => new{ percent = tr.Percent, weight = t.Weight }).ToList();
var ratio = 0.0;
foreach (var taskRecord in taskRecordList)
{
ratio += taskRecord.percent * taskRecord.weight * 0.01;
}
list.Add(ratio);
}
dict.Add("list", list);
dict.Add("name", project.Name);
result.Add(dict);
}
return(result);
}
/// <summary>
/// 添加一条记录
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public async Task <ApiResult <string> > AddAsync(ErpSaleOrder parm, string goodsJson)
{
var res = new ApiResult <string>()
{
data = "1", statusCode = (int)ApiEnum.Error
};
try
{
DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00");
parm.Guid = Guid.NewGuid().ToString();
//判断销售订单字符串是否为空
if (string.IsNullOrEmpty(goodsJson))
{
res.message = "销售订单里面的商品不能为空~";
return(await Task.Run(() => res));
}
//判断用户信息存在，如果存在需要根据用户查询编号
ErpShopUser userModel = null;
if (!string.IsNullOrEmpty(parm.UserGuid))
{
//判断是否存在
userModel = ErpShopUserDb.GetSingle(m => m.Mobile == parm.UserGuid);
if (userModel == null)
{
res.message = "会员不存在~";
return(await Task.Run(() => res));
}
parm.UserGuid = userModel.Guid;
}
var isStockSuccess = true;
//解析字符串转换成List对象
var roGoodsList = JsonConvert.DeserializeObject <List <ErpSaleOrderGoods> >(goodsJson);
//验证销售商品的数量是否大于库存数量
foreach (var item in roGoodsList.GroupBy(m => m.GoodsGuid).Select(m => new ErpSaleOrderGoods {
GoodsGuid = m.Key, Counts = m.Sum(g => g.Counts)
}).ToList())
{
var shopStockSum = Db.Queryable <ErpShopSku>()
.Where(m => m.ShopGuid == parm.ShopGuid && m.SkuGuid == item.GoodsGuid).First();
if (shopStockSum.Stock < item.Counts)
{
isStockSuccess = false;
}
}
if (!isStockSuccess)
{
res.message = "商品库存数量";
return(await Task.Run(() => res));
}
//获得商品的所有id
var goodIds = roGoodsList.Select(m => m.GoodsGuid).ToList();
//根据商品获得列表
var goodList = ErpGoodsSkuDb.GetList(m => goodIds.Contains(m.Guid));
//根据店铺获得加盟商的库存信息
var shopGoodsSkuList = ErpShopSkuDb.GetList(m => m.ShopGuid == parm.ShopGuid && goodIds.Contains(m.SkuGuid));
//满减变量
var fullJson = new List <ShopActivity>();
//根据活动编号，查询活动详情，处理对应金额
ErpShopActivity activityModel = null;
if (!string.IsNullOrEmpty(parm.ActivityName))
{
//查询活动
activityModel = ErpShopActivityDb.GetSingle(m => m.Guid == parm.ActivityName && m.Enable && !m.IsDel);
if (activityModel != null)
{
parm.ActivityGuid = parm.ActivityName;
parm.ActivityName = Utils.GetActivityMethod(activityModel.Method);
parm.ActivityTypes = Utils.GetActivityTypes(activityModel.Method);
if (!string.IsNullOrEmpty(activityModel.FullBack))
{
//满减======序列号满减对象
fullJson = JsonConvert.DeserializeObject <List <ShopActivity> >(activityModel.FullBack);
}
}
}
//根据活动算好订单金额，如果有活动，满减或者打折，最终金额会根据活动而变
var roIndex = 0; //定义一个循环的标记
foreach (var item in roGoodsList)
{
roIndex++;
var skuItem = goodList.Find(m => m.Guid == item.GoodsGuid);
//增加到GoodsSku的销售数量
goodList.Find(m => m.Guid == item.GoodsGuid).SaleSum += item.Counts;
//修改加盟商条形码表中的库存减少， 销售数量增加
var shopSkuModel = shopGoodsSkuList.Find(m => m.SkuGuid == skuItem.Guid);
shopGoodsSkuList.Find(m => m.SkuGuid == item.GoodsGuid).Stock = shopSkuModel.Stock - item.Counts;
//修改加盟商条形码表中的销售数量 增加
shopGoodsSkuList.Find(m => m.SkuGuid == item.GoodsGuid).Sale = shopSkuModel.Sale + item.Counts;
//获得商品原价*购买商品的数量
parm.Money += Convert.ToDecimal(skuItem.SalePrice) * item.Counts;
//如果没有活动直接结算金额
if (activityModel == null)
{
if (parm.SaleType == 1)
{
parm.RealMoney += Convert.ToInt32(skuItem.SalePrice) * item.Counts;
item.Money = Convert.ToInt32(skuItem.SalePrice);
}
else
{
item.Money = parm.RealMoney;
}
}
else
{
//统计非品牌活动，到下面去处理这个值
if (activityModel.Types != 2 && parm.SaleType == 1)
{
parm.RealMoney += Convert.ToInt32(skuItem.SalePrice) * item.Counts;
}
}
//整除销售计算价格，残次品价格是前端传过来的
if (activityModel != null && parm.SaleType == 1)
{
//按品牌——这里面只处理打折的，并且是按品牌的
if (activityModel.Method == 1 && activityModel.Types == 2)
{
if (skuItem.BrankGuid == activityModel.BrandGuid)
{
//品牌打折
var tempMoney = Convert.ToDecimal(skuItem.SalePrice) * item.Counts;
tempMoney = tempMoney * (Convert.ToDecimal(activityModel.CountNum) / 100);
parm.RealMoney += tempMoney;
item.Money = parm.RealMoney;
}
else
{
//不是该品牌部打折
parm.RealMoney += Convert.ToDecimal(skuItem.SalePrice) * item.Counts;
item.Money = parm.RealMoney;
}
}
//按品牌——满减
else if (activityModel.Method == 2 && activityModel.Types == 2)
{
//查询该品牌价格是否满足满减需要
var brankMoney = goodList.Where(m => m.BrankGuid == activityModel.BrandGuid).Sum(m => Convert.ToInt32(m.SalePrice) * item.Counts);
var brankCount = goodList.Count(m => m.BrankGuid == activityModel.BrandGuid);
//是否满足品牌打折的要求
var isBrankOk = false;
//如果满足打折要求，满减值
var fullMoney = 0;
//循环判断符合满减对象
foreach (var fullItem in fullJson.OrderByDescending(m => m.fullbegin).ToList())
{
if (brankMoney >= fullItem.fullbegin)
{
isBrankOk = true;
fullMoney = fullItem.fullend;
break;
}
}
//满足打折需求——根据品牌计算单个商品的价格 品牌平均分配价格
if (skuItem.BrankGuid == activityModel.BrandGuid && isBrankOk)
{
//单个商品的金额减满减值/品牌个数=订单详情商品的金额
item.Money = Convert.ToDecimal(skuItem.SalePrice) * item.Counts - Convert.ToDecimal(fullMoney / brankCount);
parm.RealMoney += item.Money;
}
else
{
item.Money = Convert.ToDecimal(skuItem.SalePrice) * item.Counts;
parm.RealMoney += item.Money;
}
}
//按品牌——买一增一
else if (activityModel.Method == 3 && activityModel.Types == 2)
{
//按品牌买一增一
if (skuItem.BrankGuid == activityModel.BrandGuid)
{
if (roIndex == 1)
{
item.Money = Convert.ToDecimal(skuItem.SalePrice) * item.Counts;
parm.RealMoney += item.Money;
}
else
{
item.Money = 0;
}
}
else
{
//不是该品牌，不参与活动 既然后是品牌买一赠一活动，实际上不应该出现非品牌的商品出现
item.Money = Convert.ToDecimal(skuItem.SalePrice) * item.Counts;
parm.RealMoney += item.Money;
}
}
//按全部店铺——打折
else if (activityModel.Method == 1 && activityModel.Types != 2)
{
//全部商铺，也就是所有金额
var zhVal = Convert.ToDecimal(activityModel.CountNum) / 100;
item.Money = (Convert.ToDecimal(skuItem.SalePrice) * item.Counts) * zhVal;
}
//按全部店铺——满减
else if (activityModel.Method == 2 && activityModel.Types != 2)
{
var allMoney = goodList.Sum(m => Convert.ToInt32(m.SalePrice) * item.Counts);
var allCount = goodList.Count();
//是否满足品牌打折的要求
var isBrankOk = false;
//如果满足打折要求，满减值
var fullMoney = 0;
//循环判断符合满减对象
foreach (var fullItem in fullJson.OrderByDescending(m => m.fullbegin).ToList())
{
if (allMoney >= fullItem.fullbegin)
{
isBrankOk = true;
fullMoney = fullItem.fullend;
break;
}
}
if (isBrankOk)
{
item.Money = Convert.ToDecimal(skuItem.SalePrice) * item.Counts - Convert.ToDecimal(fullMoney / allCount);
}
}
//按全部店铺——买一赠一
else if (activityModel.Method == 3 && activityModel.Types != 2)
{
item.Money = roIndex == 1 ? Convert.ToDecimal(skuItem.SalePrice) * item.Counts : 0;
}
}
}
//有活动，并且是正常销售的情况下 非按品牌
if (activityModel != null && parm.SaleType == 1 && activityModel.Types != 2)
{
//====打折/满减
if (activityModel.Method == 1)
{
//全部商铺，也就是所有金额
var zhVal = Convert.ToDecimal(activityModel.CountNum) / 100;
parm.RealMoney = Convert.ToDecimal(parm.RealMoney * zhVal);
}
else if (activityModel.Method == 2)
{
//循环判断符合满减对象
foreach (var item in fullJson.OrderByDescending(m => m.fullbegin).ToList())
{
if (parm.RealMoney >= item.fullbegin)
{
parm.RealMoney = parm.RealMoney - item.fullend;
break;
}
}
}
else if (activityModel.Method == 3)
{
//买一增一 循环购买商品，只读取第一件，第二个是赠品
for (int i = 0; i < roGoodsList.Count; i++)
{
if (i == 0)
{
parm.RealMoney = Convert.ToDecimal(goodList.Find(m => m.Guid == roGoodsList[i].GoodsGuid).SalePrice);
//给商品明细，第一条商品价格，第二价格为0元，不需要处理
roGoodsList[i].Money = parm.RealMoney;
break;
}
}
}
}
//查询今天销售数量
var dayCount = ErpSaleOrderDb.Count(m => SqlFunc.DateIsSame(m.AddDate, dayTime));
parm.Number = "SO-" + DateTime.Now.ToString("yyyyMMdd") + "-" + (1001 + dayCount);
res.data = parm.Number;
for (int i = 0; i < roGoodsList.Count; i++)
{
var item = roGoodsList[i];
item.OrderNumber = parm.Number;
item.Guid = Guid.NewGuid().ToString();
item.ShopGuid = parm.ShopGuid;
}
var result = Db.Ado.UseTran(() =>
{
//添加订单
Db.Insertable(parm).ExecuteCommand();
//添加订单商品
Db.Insertable(roGoodsList).ExecuteCommand();
//根据商品修改商品的销售数量
Db.Updateable(goodList).ExecuteCommand();
//修改加盟商商品条形码的销售数量
Db.Updateable(shopGoodsSkuList).ExecuteCommand();
if (userModel != null)
{
//根据实付金额，计算积分值
userModel.Points = Convert.ToInt32(parm.RealMoney / 10);
//构建积分变动记录
var pointLogModel = new ErpUserPointLog()
{
Guid = Guid.NewGuid().ToString(),
UserGuid = userModel.Guid,
OperateGuid = parm.Guid,
Types = 0,
Point = userModel.Points,
Summary = "[增加]-销售商品"
};
Db.Insertable(pointLogModel).ExecuteCommand();
//修改用户积分
Db.Updateable <ErpShopUser>().UpdateColumns(m => m.Points == m.Points + userModel.Points).Where(m => m.Guid == userModel.Guid).ExecuteCommand();
}
});
res.statusCode = (int)ApiEnum.Status;
if (!result.IsSuccess)
{
res.statusCode = (int)ApiEnum.Error;
res.message = result.ErrorMessage;
}
}
catch (Exception ex)
{
res.statusCode = (int)ApiEnum.Error;
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
}
return(await Task.Run(() => res));
}
/// <summary>
/// 添加一条数据
/// </summary>
/// <returns></returns>
public async Task <ApiResult <string> > AddAsync(ErpReturnOrder parm, string goodsJson)
{
var res = new ApiResult <string>()
{
data = "1", statusCode = (int)ApiEnum.Error
};
try
{
parm.Guid = Guid.NewGuid().ToString();
//判断返货订单字符串是否为空
if (string.IsNullOrEmpty(goodsJson))
{
res.message = "返货订单里面的商品不能为空~";
return(await Task.Run(() => res));
}
var isStockSuccess = true;
//解析字符串转换成List对象
var roGoodsList = JsonConvert.DeserializeObject <List <ErpReturnGoods> >(goodsJson);
var guidArray = roGoodsList.Select(m => m.GoodsGuid).ToList();
//根据返货的商品，查询平台的条形码
var goodsSkuList = ErpGoodsSkuDb.GetList(m => guidArray.Contains(m.Guid));
//根据返货的商品，查询加盟商的条形码
var shopsSkuList = ErpShopSkuDb.GetList(m => guidArray.Contains(m.SkuGuid) && m.ShopGuid == parm.ShopGuid);
//验证返货商品的数量是否大于库存数量
foreach (var item in roGoodsList.GroupBy(m => m.GoodsGuid).Select(m => new ErpReturnGoods {
GoodsGuid = m.Key, ReturnCount = m.Sum(g => g.ReturnCount)
}).ToList())
{
var shopStockSum = shopsSkuList.Find(m => m.SkuGuid == item.GoodsGuid);
if (shopStockSum.Stock < item.ReturnCount)
{
isStockSuccess = false;
}
//加盟商条形码表，减少返货的库存
shopsSkuList.Find(m => m.SkuGuid == item.GoodsGuid).Stock = shopStockSum.Stock - item.ReturnCount;
//平台条形码表，增加返货的库存
var goodsStock = goodsSkuList.Find(m => m.Guid == item.GoodsGuid);
goodsSkuList.Find(m => m.Guid == item.GoodsGuid).StockSum = goodsStock.StockSum + item.ReturnCount;
}
if (!isStockSuccess)
{
res.message = "返货的商品数量大于库存数量";
return(await Task.Run(() => res));
}
foreach (var item in roGoodsList)
{
item.OrderGuid = parm.Guid;
item.Guid = Guid.NewGuid().ToString();
item.ShopGuid = parm.ShopGuid;
}
parm.GoodsSum = roGoodsList.Sum(m => m.ReturnCount);
//查询今天返货数量
DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00");
var dayCount = ErpReturnOrderDb.Count(m => SqlFunc.DateIsSame(m.AddDate, dayTime));
parm.Number = "RO-" + DateTime.Now.ToString("yyyyMMdd") + "-" + (1001 + dayCount);
var result = Db.Ado.UseTran(() =>
{
//添加订单
Db.Insertable(parm).ExecuteCommand();
//添加订单商品
Db.Insertable(roGoodsList).ExecuteCommand();
//修改平台库存
Db.Updateable(goodsSkuList).ExecuteCommand();
//修改加盟商库存
Db.Updateable(shopsSkuList).ExecuteCommand();
});
res.statusCode = (int)ApiEnum.Status;
if (!result.IsSuccess)
{
res.statusCode = (int)ApiEnum.Error;
res.message = result.ErrorMessage;
}
}
catch (Exception ex)
{
res.statusCode = (int)ApiEnum.Error;
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
}
return(await Task.Run(() => res));
}
public async Task <ApiResult> GetRightData(string spell, int globalSiteId)
{
var monthArticle = await _articleService.GetArtcileByConditionAsync((ca, cc) => ca.Audit == true && SqlFunc.DateIsSame(DateTime.Now, ca.CreateTime, DateType.Month), 1, 10);
var currentSpellArticle = await _articleService.GetArtcileByConditionAsync((ca, cc) => ca.Audit == true && cc.EnTitle.Trim().Equals(spell), 1, 10);
var allChildColumn = (await GetColumnAsync(globalSiteId)).Where(d => d.ParentId != 0).ToList();
return(new ApiResult(data: new { monthArticle = monthArticle.Items, currentSpellArticle = currentSpellArticle.Items, allChildColumn }));
}
/// <summary>
/// 添加一条数据
/// </summary>
/// <returns></returns>
public async Task <ApiResult <string> > AddAsync(ErpBackGoods parm)
{
var res = new ApiResult <string>()
{
data = "1", statusCode = (int)ApiEnum.Error
};
try
{
DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00");
parm.Guid = Guid.NewGuid().ToString();
//查询今天退货数量
var dayCount = ErpBackGoodsDb.Count(m => SqlFunc.DateIsSame(m.AddDate, dayTime));
parm.Number = "BO-" + DateTime.Now.ToString("yyyyMMdd") + "-" + (1001 + dayCount);
//根据条形码查询唯一编号
var goodSku = ErpGoodsSkuDb.GetSingle(m => m.Code == parm.GoodsGuid);
if (goodSku != null)
{
parm.GoodsGuid = goodSku.Guid;
}
else
{
res.message = "商品不存在~";
return(await Task.Run(() => res));
}
//判断退货商品，金额是否大于订单金额， 以及商品数量，是否大于订单出售数量
var orderModel = ErpSaleOrderDb.GetSingle(m => m.Number == parm.OrderNumber);
if (orderModel == null)
{
res.message = "订单不存在~";
return(await Task.Run(() => res));
}
//跨月不允许退货
if (orderModel.AddDate.Month != DateTime.Now.Month)
{
res.message = "跨月不允许退货~";
return(await Task.Run(() => res));
}
//查询活动，是否买一赠一活动，如果是，需要单独处理下
var activityModel = new ErpShopActivity();
if (!string.IsNullOrEmpty(orderModel.ActivityGuid))
{
activityModel = ErpShopActivityDb.GetSingle(m => m.Guid == orderModel.ActivityGuid);
}
//if (parm.BackMoney>orderModel.RealMoney)
//{
// res.message = "退货金额不能大于订单金额~";
// return await Task.Run(() => res);
//}
//判断是否存在
var egbCount = ErpBackGoodsDb.Count(m => m.ShopGuid == parm.ShopGuid && m.GoodsGuid == parm.GoodsGuid && m.OrderNumber == parm.OrderNumber);
if (egbCount > 0)
{
//买一增一，最多可以退货2次
if (activityModel != null && activityModel.Method == 3)
{
if (egbCount > 1)
{
res.message = "该退货信息已存在~";
return(await Task.Run(() => res));
}
}
else
{
res.message = "该退货信息已存在~";
return(await Task.Run(() => res));
}
}
//根据订单查询商品数量是否满足
var orderGoodsModel = new ErpSaleOrderGoods();
if (activityModel != null && activityModel.Method == 3)
{
//如果是买一赠一，第一次差=0的，第二次差！=0的
if (egbCount == 0)
{
orderGoodsModel = ErpSaleOrderGoodsDb.GetSingle(m => m.OrderNumber == parm.OrderNumber && m.GoodsGuid == parm.GoodsGuid && m.ShopGuid == parm.ShopGuid && m.Money == 0);
}
else
{
orderGoodsModel = ErpSaleOrderGoodsDb.GetSingle(m => m.OrderNumber == parm.OrderNumber && m.GoodsGuid == parm.GoodsGuid && m.ShopGuid == parm.ShopGuid && m.Money != 0);
}
}
else
{
orderGoodsModel = ErpSaleOrderGoodsDb.GetSingle(m => m.OrderNumber == parm.OrderNumber && m.GoodsGuid == parm.GoodsGuid && m.ShopGuid == parm.ShopGuid);
}
if (orderGoodsModel == null)
{
res.message = "该商品在该订单号中不存在~";
return(await Task.Run(() => res));
}
if (orderGoodsModel.Counts < parm.BackCount)
{
res.message = "退货商品数量不能大于订单购买数量~";
return(await Task.Run(() => res));
}
//获取订单销售的金额 客户端不需要手动输入， 注意需要判断下是否买一赠一活动
parm.BackMoney = orderGoodsModel.Money;
//构建积分变动记录
var result = Db.Ado.UseTran(() =>
{
//修改加盟商条形码里面的库存 退货=加盟商库存增加
Db.Updateable <ErpShopSku>()
.UpdateColumns(m => new ErpShopSku()
{
Stock = m.Stock + parm.BackCount, Sale = m.Sale - parm.BackCount
})
.Where(m => m.ShopGuid == parm.ShopGuid && m.SkuGuid == goodSku.Guid)
.ExecuteCommand();
//根据商品编号，修改平台的销售数量
Db.Updateable <ErpGoodsSku>().UpdateColumns(m => new ErpGoodsSku()
{
SaleSum = m.SaleSum - parm.BackCount
})
.Where(m => m.Guid == goodSku.Guid).ExecuteCommand();
//修改订单-商品详情表中的退货数量 增加
orderGoodsModel.BackCounts = orderGoodsModel.BackCounts + parm.BackCount;
Db.Updateable(orderGoodsModel).ExecuteCommand();
//增加一条退货信息
Db.Insertable(parm).ExecuteCommand();
//判断是否存在用户信息
if (!string.IsNullOrEmpty(orderModel.UserGuid))
{
var pointLogModel = new ErpUserPointLog()
{
Guid = Guid.NewGuid().ToString(),
UserGuid = orderModel.UserGuid,
OperateGuid = parm.Guid,
Types = 1,
Point = orderGoodsModel.Money != 0?Convert.ToInt32((orderGoodsModel.Money / orderGoodsModel.Counts) / 10):0,
Summary = "[减少]-退货积分变更"
};
Db.Insertable(pointLogModel).ExecuteCommand();
//用户积分减少
Db.Updateable <ErpShopUser>().UpdateColumns(m => m.Points == m.Points - pointLogModel.Point)
.Where(m => m.Guid == orderModel.UserGuid).ExecuteCommand();
}
});
res.statusCode = (int)ApiEnum.Status;
if (!result.IsSuccess)
{
res.statusCode = (int)ApiEnum.Error;
res.message = result.ErrorMessage;
}
}
catch (Exception ex)
{
res.statusCode = (int)ApiEnum.Error;
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
}
return(await Task.Run(() => res));
}
/// <summary>
/// 添加一条记录
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public async Task <ApiResult <string> > AddAsync(ErpSaleOrder parm, string goodsJson)
{
var res = new ApiResult <string>()
{
data = "1", statusCode = (int)ApiEnum.Error
};
try
{
DateTime dayTime = Convert.ToDateTime(DateTime.Now.AddDays(1).ToShortDateString() + " 00:00:00");
parm.Guid = Guid.NewGuid().ToString();
//判断销售订单字符串是否为空
if (string.IsNullOrEmpty(goodsJson))
{
res.message = "销售订单里面的商品不能为空~";
return(await Task.Run(() => res));
}
//判断用户信息存在，如果存在需要根据用户查询编号
ErpShopUser userModel = null;
if (!string.IsNullOrEmpty(parm.UserGuid))
{
//判断是否存在
userModel = ErpShopUserDb.GetSingle(m => m.Mobile == parm.UserGuid);
if (userModel == null)
{
res.message = "会员不存在~";
return(await Task.Run(() => res));
}
parm.UserGuid = userModel.Guid;
}
var isStockSuccess = true;
//解析字符串转换成List对象
var roGoodsList = JsonConvert.DeserializeObject <List <ErpSaleOrderGoods> >(goodsJson);
//验证销售商品的数量是否大于库存数量
foreach (var item in roGoodsList.GroupBy(m => m.GoodsGuid).Select(m => new ErpSaleOrderGoods {
GoodsGuid = m.Key, Counts = m.Sum(g => g.Counts)
}).ToList())
{
var shopStockSum = Db.Queryable <ErpShopSku>()
.Where(m => m.ShopGuid == parm.ShopGuid && m.SkuGuid == item.GoodsGuid).First();
if (shopStockSum.Stock < item.Counts)
{
isStockSuccess = false;
}
}
if (!isStockSuccess)
{
res.message = "商品库存数量";
return(await Task.Run(() => res));
}
//根据活动编号，查询活动详情，处理对应金额
ErpShopActivity activityModel = null;
if (!string.IsNullOrEmpty(parm.ActivityName))
{
//查询活动
activityModel = ErpShopActivityDb.GetById(parm.ActivityName);
if (activityModel != null)
{
parm.ActivityGuid = parm.ActivityName;
parm.ActivityName = activityModel.Method == 1 ? "打折" : "满减";
parm.ActivityTypes = activityModel.Method == 1 ? Convert.ToByte(2) : Convert.ToByte(3);
}
}
//根据活动算好订单金额，如果有活动，满减或者打折，最终金额会根据活动而变
//获得商品的所有id
var goodIds = roGoodsList.Select(m => m.GoodsGuid).ToList();
//根据商品获得列表
var goodList = ErpGoodsSkuDb.GetList(m => goodIds.Contains(m.Guid));
//根据店铺获得加盟商的库存信息
var shopGoodsSkuList = ErpShopSkuDb.GetList(m => m.ShopGuid == parm.ShopGuid && goodIds.Contains(m.SkuGuid));
foreach (var item in goodList)
{
foreach (var roitem in roGoodsList)
{
if (roitem.GoodsGuid == item.Guid)
{
//修改商品的销售数量
item.SaleSum += roitem.Counts;
//修改加盟商条形码表中的库存减少， 销售数量增加
var shopSkuModel = shopGoodsSkuList.Find(m => m.SkuGuid == roitem.GoodsGuid);
shopGoodsSkuList.Find(m => m.SkuGuid == roitem.GoodsGuid).Stock = shopSkuModel.Stock - roitem.Counts;
//修改加盟商条形码表中的销售数量 增加
shopGoodsSkuList.Find(m => m.SkuGuid == roitem.GoodsGuid).Sale = shopSkuModel.Sale + roitem.Counts;
//获得商品原价*购买商品的数量
parm.Money += Convert.ToDecimal(item.SalePrice) * roitem.Counts;
//整除销售计算价格，残次品价格是前端传过来的
if (parm.SaleType == 1)
{
//这里面只处理打折的，并且是按品牌的
if (activityModel != null && activityModel.Method == 1 && activityModel.Types == 2)
{
if (item.BrankGuid == activityModel.BrandGuid)
{
//品牌打折
var tempMoney = Convert.ToDecimal(item.SalePrice) * roitem.Counts;
tempMoney = tempMoney * (Convert.ToDecimal(activityModel.CountNum) / 100);
parm.RealMoney += tempMoney;
}
else
{
//不是该品牌部打折
parm.RealMoney += Convert.ToDecimal(item.SalePrice) * roitem.Counts;
}
}
//else if (activityModel != null && activityModel.Method == 2 && activityModel.Types == 2) {
// //品牌满减
// if (item.BrankGuid == activityModel.BrandGuid)
// {
// }
//}
else
{
parm.RealMoney += Convert.ToDecimal(item.SalePrice) * roitem.Counts;
}
}
}
}
}
if (activityModel != null && parm.SaleType == 1)
{
//====打折/满减
if (activityModel.Method == 1)
{
//打折 实收金额=实收金额*（折扣值/100）
if (activityModel.Types == 0)
{
//全部商铺，也就是所有金额
var zhVal = Convert.ToDecimal(activityModel.CountNum) / 100;
parm.RealMoney = Convert.ToDecimal(parm.RealMoney * zhVal);
}
}
else
{
//满减======序列号满减对象
var fullJson = JsonConvert.DeserializeObject <List <ShopActivity> >(activityModel.FullBack);
//循环判断符合满减对象
foreach (var item in fullJson.OrderByDescending(m => m.fullbegin).ToList())
{
if (parm.RealMoney > item.fullbegin)
{
parm.RealMoney = parm.RealMoney - item.fullend;
break;
}
}
}
}
//根据实付金额，计算积分值
if (userModel != null)
{
userModel.Points = Convert.ToInt32(parm.RealMoney / 10);
}
//查询今天销售数量
var dayCount = ErpSaleOrderDb.Count(m => SqlFunc.DateIsSame(m.AddDate, dayTime));
parm.Number = "SO-" + DateTime.Now.ToString("yyyyMMdd") + "-" + (1001 + dayCount);
res.data = parm.Number;
foreach (var item in roGoodsList)
{
item.OrderNumber = parm.Number;
item.Guid = Guid.NewGuid().ToString();
item.ShopGuid = parm.ShopGuid;
}
var result = Db.Ado.UseTran(() =>
{
//添加订单
Db.Insertable(parm).ExecuteCommand();
//添加订单商品
Db.Insertable(roGoodsList).ExecuteCommand();
//根据商品修改商品的销售数量
Db.Updateable(goodList).ExecuteCommand();
//修改加盟商商品条形码的销售数量
Db.Updateable(shopGoodsSkuList).ExecuteCommand();
if (userModel != null)
{
//修改用户积分
Db.Updateable <ErpShopUser>().UpdateColumns(m => m.Points == m.Points + userModel.Points).Where(m => m.Guid == userModel.Guid).ExecuteCommand();
}
});
res.statusCode = (int)ApiEnum.Status;
if (!result.IsSuccess)
{
res.statusCode = (int)ApiEnum.Error;
res.message = result.ErrorMessage;
}
}
catch (Exception ex)
{
res.statusCode = (int)ApiEnum.Error;
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
}
return(await Task.Run(() => res));
}
/// <summary>
/// 打印记录查询
/// </summary>
/// <param name="model"></param>
/// <param name="page"></param>
/// <returns></returns>
public ReturnValue <List <packageInfo> > GetPrintInfo(PintInfoModel model, Page page)
{
var packageDao = SqlSugarDB.Instance <packageInfo>();
List <packageInfo> packageList = packageDao.Query()
.WhereIF(!string.IsNullOrEmpty(model.material), p => p.mNo.Equals(model.material))
.WhereIF(!string.IsNullOrEmpty(model.status), p => p.STATE == model.status.ObjToInt())
.WhereIF(!string.IsNullOrEmpty(model.seriesNo), p => p.seriesNo.Equals(model.seriesNo))
.WhereIF(!string.IsNullOrEmpty(model.oldMatNo), p => p.newNo.Equals(model.oldMatNo))
.WhereIF(!string.IsNullOrEmpty(model.isPackage), p => p.packageType == model.isPackage.ObjToInt())
.WhereIF(!string.IsNullOrEmpty(model.spackageDate), p => p.packageDate >= Convert.ToDateTime(model.spackageDate))
.WhereIF(!string.IsNullOrEmpty(model.epackageDate), p => p.packageDate <= Convert.ToDateTime(model.epackageDate))
.WhereIF(!string.IsNullOrEmpty(model.printDate), p => SqlFunc.DateIsSame(p.timestamps, Convert.ToDateTime(model.printDate)))
.WhereIF(!string.IsNullOrEmpty(UserBLL.userInfo.WorkLine), p => p.workShop == UserBLL.userInfo.WorkLine)
.OrderBy(p => p.id, OrderByType.Desc).ToPageList(page.pageIndex, page.pageSize, ref page.totalCount)
.ToList();
return(new ReturnValue <List <packageInfo> >().Success(packageList));
}
/// <summary>
/// 获取当天最大箱号
/// </summary>
/// <param name="wksplit"></param>
/// <param name="siloNum">料仓号编号</param>
/// <param name="PorcessNum">工艺号</param>
/// <returns></returns>
public ReturnValue <string> GetSNInfo(string wksplit, string siloNum = "", string PorcessNum = "")
{
ReturnValue <string> rv = new ReturnValue <string>();
var packageDao = SqlSugarDB.Instance <packageInfo>();
var packageModel = packageDao.Query().Where(p => p.wksplit.Equals(wksplit) && SqlFunc.DateIsSame(p.timestamps, DateTime.Now)).OrderBy(p => p.id, OrderByType.Desc).First();
string date = DateTime.Now.ToString("yyMMdd");
if (packageModel != null)
{
string snCode = rv.Success(packageModel.seriesNo.Substring(packageModel.seriesNo.Length - 3)).Value;
return(rv.Success(UserBLL.userInfo.SeriesHead + siloNum + (!string.IsNullOrEmpty(PorcessNum) ? PorcessNum : "00") + date + (Convert.ToInt32(snCode) + 1).ToString().PadLeft(3, '0')));
}
else
{
return(rv.Success(UserBLL.userInfo.SeriesHead + siloNum + (!string.IsNullOrEmpty(PorcessNum) ? PorcessNum : "00") + date + "001"));
}
}