/// <summary>
/// 生产车间入库数量
/// </summary>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <List <ProductionOrderWorkshopCountModel> > > GetProductionOrderWorkshop(CurrentUser currentUser)
{
try
{
List <ProductionOrderWorkshopCountModel> queryData = null;//查询结果集对象
var query1 = _db.Instance.Queryable <TMMProductionOrderDetailDbModel, TMMProductionOrderMainDbModel, TBMDictionaryDbModel>(
(t1, t2, t3) => new object[]
{
JoinType.Left, t1.MainId == t2.ID,
JoinType.Left, t1.WorkshopId == t3.ID
}
).Where((t1, t2, t3) => t2.CompanyId == currentUser.CompanyID
)
.GroupBy((t1, t2, t3) => new { t2.ID, t1.WorkshopId, t3.DicValue })
.Select((t1, t2, t3) => new ProductionOrderWorkshopCountModel1 {
ID = t2.ID, WorkshopId = Convert.ToInt32(t1.WorkshopId), WorkshopName = t3.DicValue
});
var s1 = query1.ToListAsync();
var query2 = _db.Instance.Queryable <TMMWhApplyMainDbModel, TMMWhApplyDetailDbModel>(
(t1, t2) => new object[]
{
JoinType.Left, t1.ID == t2.MainId
}
).Where((t1, t2) => t1.CompanyId == currentUser.CompanyID && t1.AuditStatus == 2 && t1.DeleteFlag == false
)
.GroupBy((t1, t2) => new { t1.SourceId, t1.ID })
.Select((t1, t2) => new ProductionOrderWorkshopCountModel2 {
ID = t1.ID, SourceId = Convert.ToInt32(t1.SourceId), Number = SqlFunc.AggregateSum(t2.ApplyNum)
});
var s2 = query2.ToListAsync();
var ss2 = query2.ToSql();
var query3 = _db.Instance.Queryable <TWMProductionWhMainDbModel>().Where(t1 => t1.CompanyId == currentUser.CompanyID && t1.AuditStatus == 2 && t1.DeleteFlag == false &&
t1.WarehousingDate == Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"))
)
.GroupBy(t => t.SourceId).Select(t => new ProductionOrderWorkshopCountModel3 {
SourceId = t.SourceId, Number = SqlFunc.AggregateSum(t.Number)
});
var s3 = query3.ToListAsync();
var ss3 = query3.ToSql();
var truequery = _db.Instance.Queryable(query2, query3, JoinType.Inner, (p1, p2) => p1.ID == p2.SourceId)
.Select((p1, p2) => new ProductionOrderWorkshopCountModel2 {
ID = p1.ID, SourceId = p1.SourceId, Number = p2.Number
});
var s4 = truequery.ToListAsync();
var ss4 = truequery.ToSql();
var ts = _db.Instance.Queryable(query1, truequery, JoinType.Inner, (p1, p2) => p1.ID == p2.SourceId);
var Count = ts
.Where((p1, p2) => p1.WorkshopName != null)
.Select((p1, p2) => SqlFunc.AggregateSum(p2.Number));
var Count1 = Count.ToListAsync().Result[0];
queryData = await ts
.GroupBy((p1, p2) => p1.WorkshopName)
.Where((p1, p2) => p1.WorkshopName != null)
.Select((p1, p2) => new ProductionOrderWorkshopCountModel
{
Name = p1.WorkshopName,
Value = SqlFunc.AggregateSum(p2.Number),
NumberCount = Count1
}).ToListAsync();
return(ResponseUtil <List <ProductionOrderWorkshopCountModel> > .SuccessResult(queryData));
}
catch (Exception ex)
{
return(ResponseUtil <List <ProductionOrderWorkshopCountModel> >
.FailResult(null, $"生产车间入库数量发生异常{System.Environment.NewLine} {ex.Message}"));
}
}
/// <summary>
/// 生产一览表
/// </summary>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <ProductionOrderListModel> > GetProdutionCountList(RequestGet request, CurrentUser currentUser)
{
try
{
List <ProductionOrderCountListModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TMMProductionOrderDetailDbModel, TMMProductionOrderMainDbModel
, TBMDictionaryDbModel, TBMDictionaryDbModel, TBMCustomerFileDbModel, TBMPackageDbModel>(
(t1, t2, t3, t4, t5, t6) => new object[]
{
JoinType.Left, t1.MainId == t2.ID,
JoinType.Left, t2.ProductionType == t3.ID,
JoinType.Left, t1.WorkshopId == t4.ID,
JoinType.Left, t2.CustomerId == t5.ID,
JoinType.Left, t1.PackageId == t6.ID
}
).Where((t1, t2, t3, t4, t5, t6) => t2.CompanyId == currentUser.CompanyID)
.OrderBy((t1, t2, t3, t4, t5, t6) => t2.ID, OrderByType.Desc);
//查询条件
if (request.QueryConditions != null && request.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(request.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "orderdate" || item.FieldName.ToLower() == "customerid" || item.FieldName.ToLower() == "productionno" || item.FieldName.ToLower() == "productiontype")
{
item.FieldName = $"t2.{item.FieldName}";
continue;
}
if (item.FieldName.ToLower() == "packageid" || item.FieldName.ToLower() == "workshopid")
{
item.FieldName = $"t1.{item.FieldName}";
continue;
}
}
query.Where(conditionals);
}
//排序条件
if (request.OrderByConditions != null && request.OrderByConditions.Count > 0)
{
foreach (var item in request.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TBMDictionaryDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"t2.{item.Column} {item.Condition}");
}
}
var today = _db.Instance.Queryable <TWMProductionWhDetailDbModel, TWMProductionWhMainDbModel
, TMMWhApplyDetailDbModel>(
(t1, t2, t3) => new object[] {
JoinType.Left, t1.MainId == t2.ID,
JoinType.Left, t1.ProOrderDetailId == t3.ID
}
)
.Where((t1, t2, t3) => t2.CompanyId == currentUser.CompanyID && t2.AuditStatus == 2 && t2.DeleteFlag == false &&
t2.WarehousingDate >= Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")) &&
t2.WarehousingDate < Convert.ToDateTime(DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")))
.GroupBy((t1, t2, t3) => t3.ProOrderDetailId)
.Select((t1, t2, t3) => new { ProOrderDetailId = t3.ProOrderDetailId, ToDayNum = SqlFunc.AggregateSum(t1.ActualNum) })
.ToList().ToDictionary(p => p.ProOrderDetailId, p => p.ToDayNum);
var Total = _db.Instance.Queryable <TWMProductionWhDetailDbModel, TWMProductionWhMainDbModel
, TMMWhApplyDetailDbModel>(
(t1, t2, t3) => new object[] {
JoinType.Left, t1.MainId == t2.ID,
JoinType.Left, t1.ProOrderDetailId == t3.ID
}
)
.Where((t1, t2, t3) => t2.CompanyId == currentUser.CompanyID && t2.AuditStatus == 2 && t2.DeleteFlag == false)
.GroupBy((t1, t2, t3) => t3.ProOrderDetailId)
.Select((t1, t2, t3) => new { ProOrderDetailId = t3.ProOrderDetailId, ToDayNum = SqlFunc.AggregateSum(t1.ActualNum) })
.ToList().ToDictionary(p => p.ProOrderDetailId, p => p.ToDayNum);
ProductionOrderListModel _list = new ProductionOrderListModel();
_list.Total_Number = query.Count();
#region 统计生产数量
var PurchaseNum = query.Select((t1, t2, t3, t4, t5, t6) => SqlFunc.AggregateSum(t1.ProductionNum)).ToList();
_list.Total_Num = PurchaseNum.Count > 0 ? Convert.ToDecimal(PurchaseNum.ToList()[0]) : 0;
#endregion
if (request.IsPaging)
{
int skipNum = request.PageSize * (request.PageIndex - 1);
queryData = await query
.Select((t1, t2, t3, t4, t5, t6) => new ProductionOrderCountListModel
{
ID = t2.ID,
ProductionNo = t2.ProductionNo,
DetailID = t1.ID,
PackageName = t6.DicValue,
CustomerName = t5.CustomerName,
ProductionTypeId = t2.ProductionType,
ProductionNum = t1.ProductionNum,
WorkshopName = t4.DicValue,
ToDayNum = 0,
TotalNum = 0,
OrderDate = t2.OrderDate
}).Skip(skipNum).Take(request.PageSize).ToListAsync();
}
else
{
queryData = await query
.Select((t1, t2, t3, t4, t5, t6) => new ProductionOrderCountListModel
{
ID = t2.ID,
ProductionNo = t2.ProductionNo,
DetailID = t1.ID,
PackageName = t6.DicValue,
CustomerName = t5.CustomerName,
ProductionTypeId = t2.ProductionType,
ProductionNum = t1.ProductionNum,
WorkshopName = t4.DicValue,
ToDayNum = 0,
TotalNum = 0,
OrderDate = t2.OrderDate
})
.ToListAsync();
}
queryData.ForEach(x =>
{
if (today.ContainsKey(x.DetailID))
{
x.ToDayNum = today[x.DetailID];
_list.Total_DayNum += x.ToDayNum;
}
if (Total.ContainsKey(x.DetailID))
{
x.TotalNum = Total[x.DetailID];
_list.Total_TotalNum += x.TotalNum;
}
});
_list.List = queryData;
return(ResponseUtil <ProductionOrderListModel> .SuccessResult(_list));
}
catch (Exception ex)
{
return(ResponseUtil <ProductionOrderListModel>
.FailResult(null, $"生产一览表发生异常{System.Environment.NewLine} {ex.Message}"));
}
}
/// <summary>
/// 生产入库态势(月)
/// </summary>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <ProductionWarehousingModel> > GetProductionWarehousingMonth(CurrentUser currentUser)
{
try
{
DateTime _thisDateTime = DateTime.Now;
String y = _thisDateTime.ToString("yyyy");
String m = _thisDateTime.ToString("MM");
int day = System.Threading.Thread.CurrentThread.CurrentUICulture.Calendar.GetDaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
// var queryData = _db.Instance.Queryable<TWMProductionWhMainDbModel>()
//.Where(t => t.CompanyId == currentUser.CompanyID && t.AuditStatus == 2 && t.DeleteFlag == false);
string s1 = "";
string s2 = "";
for (int i = 0; i < 12; i++)
{
s1 += "|" + (i + 1);
var Month = (y + "-" + (i + 1));
DateTime dt1 = Convert.ToDateTime("" + y + "-" + (i + 1) + "-01");
DateTime dt2;
if ((i + 1) == 12)
{
dt2 = Convert.ToDateTime("" + (Convert.ToInt32(y) + 1) + "-01-01");
}
else
{
dt2 = Convert.ToDateTime("" + y + "-" + (i + 2) + "-01");
}
// var query = await queryData.Where(t => t.WarehousingDate >= dt1 && t.WarehousingDate < dt2
//).Select(t => SqlFunc.AggregateSum(t.Number)).ToListAsync();
// if (query.Count > 0)
// {
// s2 += "," + String.Format("{0:N2}", query[0]);
// }
// else
// {
// s2 += ", " + queryData.Where(t => t.WarehousingDate >= dt1 && t.WarehousingDate < dt2
//).Select(t => SqlFunc.AggregateSum(t.Number)).ToSql();
// }
var today = await _db.Instance.Queryable <TWMProductionWhMainDbModel>()
.Where(t => t.CompanyId == currentUser.CompanyID && t.AuditStatus == 2 && t.DeleteFlag == false &&
t.WarehousingDate >= dt1 && t.WarehousingDate < dt2
).Select(t => SqlFunc.AggregateSum(t.Number)).ToListAsync();
if (today.Count > 0)
{
s2 += "|" + String.Format("{0:N2}", today[0]);
}
else
{
s2 += "|0.00";
}
}
ProductionWarehousingModel _model = new ProductionWarehousingModel {
xAxisData = s1.Substring(1).Split('|'), SeriesData = s2.Substring(1).Split('|')
};
return(ResponseUtil <ProductionWarehousingModel> .SuccessResult(_model));
}
catch (Exception ex)
{
return(ResponseUtil <ProductionWarehousingModel>
.FailResult(null, $"生产入库态势(月) 发生异常{System.Environment.NewLine} {ex.Message}"));
}
}
/// <summary>
/// 生产入库态势(周)
/// </summary>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <ProductionWarehousingModel> > GetProductionWarehousingWeek(CurrentUser currentUser)
{
try
{
DateTime _thisDateTime = DateTime.Now;
int y = Convert.ToInt32(_thisDateTime.ToString("yyyy"));
int m = Convert.ToInt32(_thisDateTime.ToString("MM"));
//获取本月的天数
var date_count = System.Threading.Thread.CurrentThread.CurrentUICulture.Calendar.GetDaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
DateTime firstDay = _thisDateTime.AddDays(1 - _thisDateTime.Day);
int weekday = (int)firstDay.DayOfWeek == 0 ? 7 : (int)firstDay.DayOfWeek;
//本月第一周有几天
int firstWeekEndDay = 7 - (weekday - 1);
var days = date_count;
var mod = firstWeekEndDay; //本月第一周有几天
var count = 1;
var start = 1; //起始日期 1 号开始
var s1 = "";
var s2 = "";
var ss1 = "";
var ss2 = "";
var end = start + mod - 1;//截止日期
while (days >= 0)
{
//var end = start + 6;
end = end > date_count ? date_count : end;
ss1 += "第" + count + "周:" + y + "年" + m + "月" + "（" + start + "-" + end + "）;" + "\n";
s1 += ",第" + count + "周";
DateTime dt1 = Convert.ToDateTime("" + y + "-" + m + "-" + start);
DateTime dt2;
if ((end + 1) > date_count)
{
dt2 = Convert.ToDateTime("" + y + "-" + (m + 1) + "-01");
}
else
{
dt2 = Convert.ToDateTime("" + y + "-" + m + "-" + (end + 1));
}
var today = await _db.Instance.Queryable <TWMProductionWhMainDbModel>()
.Where(t => t.CompanyId == currentUser.CompanyID && t.AuditStatus == 2 && t.DeleteFlag == false &&
t.WarehousingDate >= dt1 && t.WarehousingDate < dt2
).Select(t => SqlFunc.AggregateSum(t.Number)).ToListAsync();
if (today.Count > 0)
{
s2 += "|" + String.Format("{0:N2}", today[0]);
}
else
{
s2 += "|0.00";
}
start = end + 1;
end += 7;
days -= 7;
ss2 += days + ",";
count++;
}
ProductionWarehousingModel _model = new ProductionWarehousingModel {
xAxisData = s1.Substring(1).Split(','), SeriesData = s2.Substring(1).Split('|')
};
return(ResponseUtil <ProductionWarehousingModel> .SuccessResult(_model));
}
catch (Exception ex)
{
return(ResponseUtil <ProductionWarehousingModel>
.FailResult(null, $"生产入库态势(周) 发生异常{System.Environment.NewLine} {ex.Message}"));
}
}
/// <summary>
/// 统计主播的 工时收益
/// </summary>
public static void StatisticsAnchorWorkHourIncome(DateTime startTime, DateTime endTime)
{
using (var db = sugarClient.GetSqlSugarDB(sugarClient.DbConnType.QPAnchorRecordDB))
{
try
{
db.Ado.BeginTran();
var list = db.Queryable <SysAnchorRebateEntity, SysAnchorLiveRecordEntity>((it, st) => new object[] { JoinType.Left, it.AnchorID == st.aid })
.Where((it, st) => it.IsWorkHours == 1 && st.ontime >= startTime && st.ontime < endTime && st.status == 1)
.GroupBy((it, st) => new { it.AnchorID, it.LiveTime, it.Salary, it.HourRebate, it.parentID, it.GiftAmount })
.Having((it, st) => SqlFunc.AggregateSum(st.livetime) >= it.LiveTime * 60 &&
SqlFunc.Subqueryable <TipEntity>().Where(gt => gt.sendtime >= startTime && gt.sendtime < SqlFunc.AggregateMax(st.uptime).AddMinutes(3)).Where(gt => gt.AnchorID == it.AnchorID).Sum(gt => gt.totalamount) >= it.GiftAmount)
.Select((it, st) => new SysTipIncomeDetailEntity
{
ShopID = 0,
UserID = it.parentID,
AnchorID = it.AnchorID,
StartDate = startTime,
UserRebate = it.HourRebate,
CreateTime = startTime,
TipType = TipTypeEnum.其他,
IncomeType = IncomeTypeEnum.工时,
AnchorIncome = it.Salary * (100 - it.HourRebate) / 100,
UserIncome = it.Salary * it.HourRebate / 100
}).ToList();
if (list.Count == 0)
{
Console.WriteLine("统计主播的工时收益：执行时间：" + DateTime.Now + ",统计开始时间--" + startTime + ",统计结束时间：--" + endTime + "，统计数据+" + list.Count);
return;
}
//批量插入工时收益明细
db.Insertable(list).ExecuteCommand();
List <int> anchorIDList = list.Select(it => it.AnchorID).ToList();
//更新工时记录 无效字段
db.Updateable <SysAnchorLiveRecordEntity>().SetColumns(it => new SysAnchorLiveRecordEntity {
status = 0
})
.Where(it => anchorIDList.Contains(it.aid) && it.ontime >= startTime && it.ontime < endTime)
.ExecuteCommand();
// 处理总收益报表
var incomeList = db.Queryable <SysIncomeEntity>().Where(it => it.opdate == startTime.Date).ToList();
var updateIncomeList = new List <SysIncomeEntity>(); //更新集合
var addIncomeList = new List <SysIncomeEntity>(); //新增集合
list.ForEach(it =>
{
//判读对应日期部分是否有该主播数据 有就更新
var updateModel = incomeList.Where(st => st.AnchorID == it.AnchorID).FirstOrDefault();
if (updateModel != null)//存在
{
updateModel.hour_income = it.AnchorIncome;
updateModel.agentHour_income = it.UserIncome;
updateIncomeList.Add(updateModel);
}
else
{
addIncomeList.Add(new SysIncomeEntity
{
AnchorID = it.AnchorID,
opdate = startTime.Date,
hour_income = it.AnchorIncome,
agentHour_income = it.UserIncome
});
}
});
if (addIncomeList.Count > 0)
{
db.Insertable(addIncomeList).ExecuteCommand();
}
if (updateIncomeList.Count > 0)
{
db.Updateable(updateIncomeList).UpdateColumns(it => new { it.hour_income, it.agentHour_income }).ExecuteCommand();
}
//更新代理余额
var agentBalance = list.GroupBy(s => new { s.UserID }).Select(group => new SysUser
{
Id = group.Key.UserID,
Balance = group.Sum(p => p.UserIncome),
}).ToList();
agentBalance.ForEach(it =>
{
db.Updateable <SysUser>().SetColumns(gt => new SysUser {
Balance = gt.Balance + it.Balance
}).Where(gt => gt.Id == it.Id).ExecuteCommand();
});
//更新主播余额
var anchorBalance = list.GroupBy(s => new { s.AnchorID }).Select(group => new SysAnchorInfoEntity
{
aid = group.Key.AnchorID,
agentGold = group.Sum(p => p.AnchorIncome),
}).ToList();
anchorBalance.ForEach(it =>
{
db.Updateable <SysAnchorInfoEntity>().SetColumns(gt => new SysAnchorInfoEntity {
agentGold = gt.agentGold + it.agentGold
})
.Where(gt => gt.aid == it.aid).ExecuteCommand();
});
db.Ado.CommitTran();
Console.WriteLine("统计主播的工时收益：执行时间：" + DateTime.Now + ",统计开始时间--" + startTime + ",统计结束时间：--" + endTime + "，统计数据+" + list.Count);
}
catch (Exception ex)
{
db.Ado.RollbackTran();
//统一记录日志
Console.WriteLine("按天统计工时收益异常：执行时间：" + DateTime.Now + ",统计开始时间--" + startTime + ",统计结束时间：--" + endTime + "。错误信息：" + ex.Message + "------" + ex.StackTrace);
LogHelper.WriteLogTips("按天统计工时收益异常：统计开始时间--" + startTime + ",统计结束时间：--" + endTime + "。错误信息：" + ex.Message + "------" + ex.StackTrace);
}
}
}
/// <summary>
/// 生产入库态势(天)
/// </summary>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <ProductionWarehousingModel> > GetProductionWarehousingDay(CurrentUser currentUser)
{
try
{
DateTime _thisDateTime = DateTime.Now;
String y = _thisDateTime.ToString("yyyy");
String m = _thisDateTime.ToString("MM");
int day = System.Threading.Thread.CurrentThread.CurrentUICulture.Calendar.GetDaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
var today = await _db.Instance.Queryable <TWMProductionWhMainDbModel>()
.Where(t => t.CompanyId == currentUser.CompanyID && t.AuditStatus == 2 && t.DeleteFlag == false)
.GroupBy(n => n.WarehousingDate)
.Select(n => new { n.WarehousingDate, Number = SqlFunc.AggregateSum(n.Number) })
.ToListAsync();
string s1 = "";
string s2 = "";
for (int i = 0; i < day; i++)
{
s1 += "|" + (i + 1);
int Count = today.Where(n => n.WarehousingDate == Convert.ToDateTime(y + "-" + m + "-" + (i + 1))).Count();
if (Count > 0)
{
var model = today.Where(n => n.WarehousingDate == Convert.ToDateTime(y + "-" + m + "-" + (i + 1)));
if (model != null)
{
s2 += "|" + String.Format("{0:N2}", model.Select(n => n.Number).ToList()[0]);
}
}
else
{
s2 += "|0.00";
}
}
ProductionWarehousingModel _model = new ProductionWarehousingModel {
xAxisData = s1.Substring(1).Split('|'), SeriesData = s2.Substring(1).Split('|')
};
return(ResponseUtil <ProductionWarehousingModel> .SuccessResult(_model));
}
catch (Exception ex)
{
return(ResponseUtil <ProductionWarehousingModel>
.FailResult(null, $"生产入库态势(天) 发生异常{System.Environment.NewLine} {ex.Message}"));
}
}
public static void Init()
{
var ssc = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = OrmTest.Config.ConnectionString,
DbType = SqlSugar.DbType.MySql, //必填
IsAutoCloseConnection = true
});
ssc.CodeFirst.InitTables <Student>();
var expMethods = new List <SqlFuncExternal>();
expMethods.Add(new SqlFuncExternal()
{
UniqueMethodName = "SumSugar",
MethodValue = (expInfo, dbType, expContext) =>
{
if (dbType == DbType.SqlServer)
{
return(string.Format("SUM({0})", expInfo.Args[0].MemberName));
}
else if (dbType == DbType.MySql)
{
return(string.Format("SUM({0})", expInfo.Args[0].MemberName));
}
else
{
throw new Exception("未实现");
}
}
});
ssc.CurrentConnectionConfig.ConfigureExternalServices = new ConfigureExternalServices()
{
SqlFuncServices = expMethods //set ext method
};
try
{
ssc.Insertable(new Student()
{
Age = 1, Createtime = DateTime.Now, Grade = 1, Id = 1, Name = "a", Schoolid = 1
}).ExecuteCommand();
ssc.Insertable(new Student()
{
Age = 1, Createtime = DateTime.Now, Grade = 1, Id = 1, Name = "a", Schoolid = 1
}).ExecuteCommand();
var sss12 = ssc.Queryable <Student>().GroupBy(o => o.Name).Select(o => new { Age = SqlFunc.AggregateSum(o.Age) }).ToList();
}
catch (Exception e)
{
}
}
/// <summary>
/// 从中间表里获取统计数据并按照机构级别统计
/// </summary>
/// <param name="flag"></param>
/// <param name="drugname"></param>
/// <returns></returns>
public List <StaticsViewModel> GetStaticsViewsByTable_JGJB(string flag, string drugname)
{
var DataResult = new List <StaticsViewModel>();
using (var db = _dbContext.GetIntance())
{
DataResult = db.Queryable <StatisticsDrugList>().Where(a => a.DrugName == drugname && a.flag == Convert.ToInt32(flag))
.GroupBy(a => new { a.InstitutionLevelName })
.OrderBy(a => SqlFunc.AggregateSum(a.DrugCount), OrderByType.Desc)
.Select(a => new StaticsViewModel()
{
commonname = a.InstitutionLevelName, count = SqlFunc.AggregateSum(a.DrugCount).ToString(), price = (decimal)SqlFunc.AggregateSum(a.Price)
}).ToList();
}
return(DataResult);
}
public async Task <ApiResult <Page <CmsOrderOverview> > > GetShopDailyReport(QueryOrderListRequest parm)
{
var res = new ApiResult <Page <CmsOrderOverview> >();
var role_info = await roleService.GetRoleByAdminGuid(parm.createby);
var query = Db.Queryable <CmsOrderOverview, AdminShopRel>((ds, rel) => new object[] { JoinType.Left, ds.out_shop_id == rel.out_shop_id });
if (role_info.isSystem || role_info.isAgent || role_info.isSubAdmin)
{
//管理员不需要过滤
//代理商没有该权限
//商户管理员不过滤
}
else // 门店普通员工
{
query.Where((ds, rel) => rel.admin_guid == parm.createby);
}
query.WhereIF(!string.IsNullOrEmpty(parm.out_sub_mch_id), (ds, rel) => ds.out_sub_mch_id == parm.out_sub_mch_id)
.WhereIF(!string.IsNullOrEmpty(parm.out_shop_id), (ds, rel) => ds.out_shop_id == parm.out_shop_id)
.WhereIF(parm.sub_pay_platforms.Length > 0, (ds, rel) => parm.sub_pay_platforms.Contains(ds.sub_pay_platform))
.WhereIF(parm.start_time != null, (ds, rel) => ds.business_date >= parm.start_time)
.WhereIF(parm.end_time != null, (ds, rel) => ds.business_date <= parm.end_time);
var data = await query.Select((ds, rel) => new CmsOrderOverview
{
business_date = ds.business_date,
out_shop_id = ds.out_shop_id,
success_count = SqlFunc.AggregateSum(ds.success_count), //交易笔数
success_amount = SqlFunc.AggregateSum(ds.success_amount), //交易金额
refund_create_count = SqlFunc.AggregateSum(ds.refund_create_count), //退货笔数
refund_create_amount = SqlFunc.AggregateSum(ds.refund_create_amount), //订单已退金额
discount_amount = SqlFunc.AggregateSum(ds.discount_amount), //优惠金额
poundage = SqlFunc.AggregateSum(ds.poundage), //手续费
income_amount = SqlFunc.AggregateSum(ds.income_amount), //入账金额
sub_mch_non_recharge_coupon_amount = SqlFunc.AggregateSum(ds.sub_mch_non_recharge_coupon_amount),
platform_non_recharge_coupon_amount = SqlFunc.AggregateSum(ds.platform_non_recharge_coupon_amount),
others_non_recharge_coupon_amount = SqlFunc.AggregateSum(ds.others_non_recharge_coupon_amount),
sub_mch_recharge_coupon_amount = SqlFunc.AggregateSum(ds.sub_mch_recharge_coupon_amount),
platform_recharge_coupon_amount = SqlFunc.AggregateSum(ds.platform_recharge_coupon_amount),
others_recharge_coupon_amount = SqlFunc.AggregateSum(ds.others_recharge_coupon_amount)
}).GroupBy(ds => new { ds.business_date, ds.out_shop_id }).ToPageAsync(parm.page_num, parm.page_size);
if (data.Items.Count > 0)
{
var ids = data.Items.Select(p => p.out_shop_id).ToArray();
var shopInfos = await Db.Queryable <ShopInfo>().In(p => p.out_shop_id, ids).ToListAsync();
data.Items.ForEach(shop =>
{
var shopInfo = shopInfos.FirstOrDefault(m => shop.out_shop_id == m.out_shop_id);
if (shopInfo != null)
{
shop.shop_name = shopInfo.shop_name;
shop.erp_org = shopInfo.erp_org;
}
//免充值优惠金额
shop.others_non_recharge_coupon_amount = shop.sub_mch_non_recharge_coupon_amount + shop.platform_non_recharge_coupon_amount + shop.others_non_recharge_coupon_amount;
//充值优惠金额
shop.others_recharge_coupon_amount = shop.sub_mch_recharge_coupon_amount + shop.platform_recharge_coupon_amount + shop.others_recharge_coupon_amount;
//应收金额
shop.pay_settle_amount = shop.success_amount - shop.refund_create_amount;
});
}
res.data = data;
res.statusCode = (int)ApiEnum.Status;
return(res);
}
/// <summary>
/// 礼物返点信息 分页信息
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public List <TipIncomeDetailModel> GetTipIncomeDetailPage(PageParm parm, ref int totalCount, ref TipIncomeDetailModel sumModel)
{
var res = new List <TipIncomeDetailModel>();
try
{
if (parm == null)
{
parm = new PageParm();
}
Dictionary <string, object> dic = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(parm.where))
{
dic = JsonConvert.DeserializeObject <Dictionary <string, object> >(parm.where);
}
using (var db = GetSqlSugarDB(DbConnType.QPAnchorRecordDB))
{
var query = db.Queryable <SysTipIncomeDetailEntity, SysUser, SysAnchor, TipEntity, SysShopAnchorEntity>((at, bt, ct, dt, ot) => new object[] {
JoinType.Left, at.UserID == bt.Id,
JoinType.Left, at.AnchorID == ct.id,
JoinType.Left, at.orderno == dt.orderno,
JoinType.Left, ct.id == ot.AnchorID
})
.Where((at, bt, ct, dt) => at.StartDate >= Convert.ToDateTime(dic["startTime"]) && at.StartDate < Convert.ToDateTime(dic["endTime"]).AddDays(1))
.WhereIF(dic.ContainsKey("AgentName") && !string.IsNullOrEmpty(dic["AgentName"].ToString()), (at, bt, ct, dt) => bt.Account.Contains(dic["AgentName"].ToString()))
.WhereIF(dic.ContainsKey("AnchorName") && !string.IsNullOrEmpty(dic["AnchorName"].ToString()), (at, bt, ct, dt) => ct.anchorName.Contains(dic["AnchorName"].ToString()) || ct.nickName.Contains(dic["AnchorName"].ToString()))
.WhereIF(dic.ContainsKey("Type") && Convert.ToInt32(dic["Type"].ToString()) != -1, (at, bt, ct, dt) => at.TipType == (TipTypeEnum)Convert.ToInt32(dic["Type"].ToString()))
.WhereIF(dic.ContainsKey("incomeType") && Convert.ToInt32(dic["incomeType"].ToString()) != -1, (at, bt, ct, dt) => at.IncomeType == (IncomeTypeEnum)Convert.ToInt32(dic["incomeType"].ToString()))
.WhereIF(dic.ContainsKey("ShopID") && Convert.ToInt32(dic["ShopID"]) != -1, (at, bt, ct, dt, ot) => ot.ShopID == Convert.ToInt32(dic["ShopID"]));
sumModel = query.Clone().Select((at, bt, ct, dt) => new TipIncomeDetailModel
{
AnchorIncome = SqlFunc.AggregateSum(at.AnchorIncome),
UserIncome = SqlFunc.AggregateSum(at.UserIncome),
PlatformIncome = SqlFunc.AggregateSum(at.PlatformIncome),
totalamount = SqlFunc.AggregateSum(dt.totalamount)
}).WithCache(10).First();
res = query
.Select((at, bt, ct, dt) => new TipIncomeDetailModel
{
UserName = bt.Account,
AnchorName = ct.anchorName,
AnchorNickName = ct.nickName,
UserIncome = at.UserIncome,
AnchorIncome = at.AnchorIncome,
PlatformIncome = at.PlatformIncome,
UserRebate = at.UserRebate,
PlatformRebate = at.PlatformRebate,
orderno = dt.orderno,
totalamount = dt.totalamount,
CreateTime = at.CreateTime,
Type = at.TipType,
incomeType = at.IncomeType,
}).WithCache(10)
.OrderBy(" at.CreateTime desc")
.ToPageList(parm.page, parm.limit, ref totalCount);
}
}
catch (Exception ex)
{
new LogLogic().Write(Level.Error, "礼物返点信息 分页信息", ex.Message, ex.StackTrace);
}
return(res);
}
public void Q2()
{
using (var db = GetInstance())
{
var t1 = db.Queryable <Student>().ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent]", null, t1.Key, null, "single t1 Error");
var t2 = db.Queryable <Student>().With(SqlWith.NoLock).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WITH(NOLOCK)", null, t2.Key, null, "single t2 Error");
var t3 = db.Queryable <Student>().OrderBy(it => it.Id).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] ORDER BY [ID] ASC", null, t3.Key, null, "single t3 Error");
var t4 = db.Queryable <Student>().OrderBy(it => it.Id).Take(3).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 1 AND 3", null, t4.Key, null, "single t4 Error");
var t5 = db.Queryable <Student>().OrderBy(it => it.Id).Skip(3).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 4 AND 9223372036854775807", null, t5.Key, null, "single t5 Error");
int pageIndex = 2;
int pageSize = 10;
var t6 = db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] DESC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 11 AND 20", null, t6.Key, null, "single t6 Error");
int studentCount = db.Ado.GetInt("select count(1) from Student");
var countIsSuccess = db.Queryable <Student>().Count() == studentCount;
if (!countIsSuccess)
{
throw new Exception(" single countIsSuccess Error");
}
var t7 = db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToPageList(pageIndex, pageSize, ref studentCount);
countIsSuccess = studentCount == db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize * pageIndex).Count();
if (!countIsSuccess)
{
throw new Exception("single t7 Error");
}
int studentMin = db.Ado.GetInt("select min(id) from Student");
var minIsSuccess = db.Queryable <Student>().Min(it => it.Id) == studentMin;
if (!minIsSuccess)
{
throw new Exception("single minIsSuccess Error");
}
int studentMax = db.Ado.GetInt("select max(id) from Student");
var maxIsSuccess = db.Queryable <Student>().Max(it => it.Id) == studentMax;
if (!maxIsSuccess)
{
throw new Exception("single maxIsSuccess Error");
}
int studentAvg = db.Ado.GetInt("select avg(id) from Student");
var avgIsSuccess = db.Queryable <Student>().Avg(it => it.Id) == studentAvg;
if (!maxIsSuccess)
{
throw new Exception(" single avgIsSuccess Error");
}
int studentSum = db.Ado.GetInt("select sum(id) from Student");
var sumIsSuccess = db.Queryable <Student>().Sum(it => it.Id) == studentSum;
if (!sumIsSuccess)
{
throw new Exception("single sumIsSuccess Error");
}
var t8 = db.Queryable <Student>()
.Where(it => it.Id == 1)
.WhereIF(true, it => SqlFunc.Contains(it.Name, "a"))
.OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).With(SqlWith.NoLock).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] DESC) AS RowIndex FROM [STudent] WITH(NOLOCK) WHERE ( [ID] = @Id0 ) AND ([Name] like '%'+@MethodConst1+'%') ) T WHERE RowIndex BETWEEN 11 AND 20", new List <SugarParameter>()
{
new SugarParameter("@Id0", 1), new SugarParameter("@MethodConst1", "a")
}, t8.Key, t8.Value, "single t8 Error");
var t9 = db.Queryable <Student>()
.In(1)
.Select(it => new { it.Id, it.Name, x = it.Id }).ToSql();
base.Check("SELECT [ID] AS [Id] , [Name] AS [Name] , [ID] AS [x] FROM [STudent] WHERE [Id] IN (@InPara0) ", new List <SugarParameter>()
{
new SugarParameter("@InPara0", 1)
}, t9.Key, t9.Value, "single t9 error");
var t10 = db.Queryable <Student>().Select(it => new StudentEnum()
{
Id = SqlFunc.GetSelfAndAutoFill(it.Id)
}).ToSql();
base.Check("SELECT * FROM [STudent] ", null, t10.Key, t10.Value, "single t10 error");
var t11 = db.Queryable <Student>().GroupBy("id").OrderBy("id").Select("id").ToSql();
base.Check("SELECT id FROM [STudent] GROUP BY id ORDER BY id ", null, t11.Key, t11.Value, "single t11 error");
var t12 = db.Queryable <Student>().Where(it => it.Id != null).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE ( [ID] IS NOT NULL )", null, t12.Key, t12.Value, "single t12 error");
var id = 1;
var t13 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id && s.Id == id).Max(s => s.Id) == 1).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] it WHERE ((SELECT MAX([Id]) FROM [School] WHERE (( [Id] = [it].[ID] ) AND ( [Id] = @Id0 ))) = @Const1 )",
new List <SugarParameter>()
{
new SugarParameter("@Id0", 1),
new SugarParameter("@Const1", 1)
}, t13.Key, t13.Value, "single t13 error ");
var t14 = db.Queryable <Student>()
.Where(it => it.Name == "a" && SqlFunc.HasValue(it.Name)).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE (( [Name] = @Name0 ) AND ( [Name]<>'' AND [Name] IS NOT NULL ))",
new List <SugarParameter>()
{
new SugarParameter("@Name0", "a")
}, t14.Key, t14.Value, "single t14 error ");
var t15 = db.Queryable <CapitalEntity>()
.Select(x => new
{
TGYArea = SqlFunc.AggregateSum(SqlFunc.IIF(x.FlatProp == "1", x.Areas, 0))
}).ToSql();
base.Check("SELECT SUM(( CASE WHEN ( [FlatProp] = @FlatProp0 ) THEN [Areas] ELSE @MethodConst1 END )) AS [TGYArea] FROM [RENT_CAPITAL] ", new List <SugarParameter>()
{
new SugarParameter("@FlatProp0", "1"),
new SugarParameter("@MethodConst1", 0)
}, t15.Key, t15.Value, "single t15 error");
}
}
/// <summary>
/// 获取住院或门诊的统计
/// </summary>
/// <param name="flag">1住院2门诊</param>
/// <returns></returns>
public List <StaticsViewModel> GetStaticsViews(string flag, string drugname)
{
var dataResult = new List <StaticsViewModel>();
//判断缓存
string rediskey = "";
if (flag == "1")//住院
{
rediskey = SystemManageConst.DRUGHOSKEY;
}
else
{
rediskey = SystemManageConst.DRUGCLINICKEY;
}
using (var redisdb = _redisDbContext.GetRedisIntance())
{
//删除缓存测试用
//redisdb.Del(rediskey);
dataResult = redisdb.Get <List <StaticsViewModel> >(rediskey);//从缓存里取
if (dataResult == null)
{
using (var db = _dbContext.GetIntance())
{
CheckDrugStatusEntity checkResultStatus = new CheckDrugStatusEntity()
{
CRowId = (flag == "1"?1:2), CheckResultStatus = "N", Flag = (flag == "1"?"HOS":"CLINIC"), FunctionDesc = (flag == "1" ? "'万能神药'住院信息获取" : "'万能神药'门诊信息获取")
};
bool checkStatusResultStart = UpdateResultStatusNew(checkResultStatus);
if (!checkStatusResultStart)
{
return(null);
}
if (flag == "1")//住院
{
dataResult = db.Queryable <AllPowerfulDrugEntity, YBHosPreInfoEntity>((a, c) => new object[] {
JoinType.Left, a.DrugCode == c.ItemCode
}).GroupBy(a => a.CommonName)
.Where(a => drugname.Contains(a.CommonName))
.OrderBy((a, c) => SqlFunc.AggregateSum(c.COUNT), OrderByType.Desc)
.Select((a, c) => new StaticsViewModel()
{
commonname = a.CommonName, count = SqlFunc.AggregateSum(c.COUNT).ToString(), price = SqlFunc.AggregateSum(c.COUNT * c.PRICE)
}).ToList();
}
else
{
dataResult = db.Queryable <AllPowerfulDrugEntity, YBClinicPreInfoEntity>((a, c) => new object[] {
JoinType.Left, a.DrugCode == c.ItemCode
}).GroupBy(a => a.CommonName)
.Where(a => drugname.Contains(a.CommonName))
.OrderBy((a, c) => SqlFunc.AggregateSum(c.COUNT), OrderByType.Desc)
.Select((a, c) => new StaticsViewModel()
{
commonname = a.CommonName, count = SqlFunc.AggregateSum(c.COUNT).ToString(), price = SqlFunc.AggregateSum(c.COUNT * c.PRICE)
}).ToList();
}
checkResultStatus = new CheckDrugStatusEntity()
{
CRowId = (flag == "1" ? 1 : 2), CheckResultStatus = "Y", Flag = (flag == "1" ? "HOS" : "CLINIC"), FunctionDesc = (flag == "1" ? "'万能神药'住院信息获取" : "'万能神药'门诊信息获取")
};
bool checkStatusResultEnd = UpdateResultStatusNew(checkResultStatus);
if (!checkStatusResultEnd)
{
return(null);
}
}
if (dataResult != null)//加入缓存
{
redisdb.Set(rediskey, dataResult);
redisdb.Expire(rediskey, 86400);//设置缓存时间1天
}
}
else
{
var dataResultNew = new List <StaticsViewModel>();
string drugnamenew = drugname;
//遍历缓存
foreach (StaticsViewModel item in dataResult)
{
if (item != null && drugname.Contains(item.commonname))
{
dataResultNew.Add(item);
drugnamenew = drugnamenew.Replace(item.commonname + ',', "");
}
}
if (drugnamenew.Length > 0)//从数据库中取
{
using (var db = _dbContext.GetIntance())
{
CheckDrugStatusEntity checkResultStatus = new CheckDrugStatusEntity()
{
CRowId = (flag == "1" ? 1 : 2), CheckResultStatus = "N", Flag = (flag == "1" ? "HOS" : "CLINIC"), FunctionDesc = (flag == "1" ? "'万能神药'住院信息获取" : "'万能神药'门诊信息获取")
};
bool checkStatusResultStart = UpdateResultStatusNew(checkResultStatus);
if (!checkStatusResultStart)
{
return(null);
}
if (flag == "1")//住院
{
dataResult = db.Queryable <AllPowerfulDrugEntity, YBHosPreInfoEntity>((a, c) => new object[] {
JoinType.Left, a.DrugCode == c.ItemCode
}).GroupBy(a => a.CommonName)
.Where(a => drugnamenew.Contains(a.CommonName))
.OrderBy((a, c) => SqlFunc.AggregateSum(c.COUNT), OrderByType.Desc)
.Select((a, c) => new StaticsViewModel()
{
commonname = a.CommonName, count = SqlFunc.AggregateSum(c.COUNT).ToString(), price = SqlFunc.AggregateSum(c.COUNT * c.PRICE)
}).ToList();
}
else
{
dataResult = db.Queryable <AllPowerfulDrugEntity, YBClinicPreInfoEntity>((a, c) => new object[] {
JoinType.Left, a.DrugCode == c.ItemCode
}).GroupBy(a => a.CommonName)
.Where(a => drugnamenew.Contains(a.CommonName))
.OrderBy((a, c) => SqlFunc.AggregateSum(c.COUNT), OrderByType.Desc)
.Select((a, c) => new StaticsViewModel()
{
commonname = a.CommonName, count = SqlFunc.AggregateSum(c.COUNT).ToString(), price = SqlFunc.AggregateSum(c.COUNT * c.PRICE)
}).ToList();
}
checkResultStatus = new CheckDrugStatusEntity()
{
CRowId = (flag == "1" ? 1 : 2), CheckResultStatus = "Y", Flag = (flag == "1" ? "HOS" : "CLINIC"), FunctionDesc = (flag == "1" ? "'万能神药'住院信息获取" : "'万能神药'门诊信息获取")
};
bool checkStatusResultEnd = UpdateResultStatusNew(checkResultStatus);
if (!checkStatusResultEnd)
{
return(null);
}
}
dataResult.AddRange(dataResultNew);
if (dataResult != null) //加入缓存
{
redisdb.Del(rediskey); //先删除缓存
redisdb.Set(rediskey, dataResult);
redisdb.Expire(rediskey, 86400); //设置缓存时间1天
}
}
else
{
dataResult = dataResultNew;
}
}
}
return(dataResult);
}
/// <summary>
/// 获取统计数据按照机构名称分组
/// </summary>
/// <param name="flag"></param>
/// <param name="drugname"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <StaticsViewModel> GetStaticsViews_JGMC(string flag, string drugname, int pageIndex, int pageSize, ref int totalCount)
{
var DataResult = new List <StaticsViewModel>();
using (var db = _dbContext.GetIntance())
{
if (flag == "1")//住院
{
DataResult = db.Queryable <YBHosPreInfoEntity, AllPowerfulDrugEntity, YBHosInfoEntity>((a, b, c) => new object[] {
JoinType.Left, a.ItemCode == b.DrugCode,
JoinType.Left, a.HosRegisterCode == c.HosRegisterCode
}).GroupBy((a, b, c) => new { c.InstitutionCode, c.InstitutionName })
.Where((a, b, c) => b.CommonName == drugname && c.InstitutionName != null && c.InstitutionName.Trim() != "")
.OrderBy(a => SqlFunc.AggregateSum(a.COUNT), OrderByType.Desc)
.Select((a, b, c) => new StaticsViewModel()
{
commonname = c.InstitutionName, count = SqlFunc.AggregateSum(a.COUNT).ToString(), price = SqlFunc.AggregateSum(a.COUNT * a.PRICE)
})
.ToPageList(pageIndex, pageSize, ref totalCount);
}
else
{
DataResult = db.Queryable <YBClinicPreInfoEntity, AllPowerfulDrugEntity, YBClinicInfoEntity>((a, b, c) => new object[] {
JoinType.Left, a.ItemCode == b.DrugCode,
JoinType.Left, a.ClinicRegisterCode == c.ClinicRegisterCode
}).GroupBy((a, b, c) => new { c.InstitutionCode, c.InstitutionName })
.Where((a, b, c) => b.CommonName == drugname && c.InstitutionName != null && c.InstitutionName.Trim() != "")
.OrderBy(a => SqlFunc.AggregateSum(a.COUNT), OrderByType.Desc)
.Select((a, b, c) => new StaticsViewModel()
{
commonname = c.InstitutionName, count = SqlFunc.AggregateSum(a.COUNT).ToString(), price = SqlFunc.AggregateSum(a.COUNT * a.PRICE)
})
.ToPageList(pageIndex, pageSize, ref totalCount);
}
}
return(DataResult);
}
/// <summary>
///
/// </summary>
/// <param name="requestObject"></param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <List <InventoryResultModel> > > LoadReport(RequestGet requestObject, CurrentUser currentUser)
{
try
{
//数据字典
var tBMDictionary = _db.Instance.Queryable <TBMDictionaryDbModel>().Where(p => p.CompanyId == currentUser.CompanyID).ToList().ToDictionary(p => p.ID, p => p.DicValue);
var warehouseDic = _db.Instance.Queryable <TBMWarehouseFileDbModel>().Where(t => SqlFunc.IsNull(t.DeleteFlag, false) != true &&
t.CompanyId == currentUser.CompanyID).ToList().ToDictionary(p => p.ID, p => new { Name = p.WarehouseName, Code = p.Code });
List <InventoryResultModel> result = new List <InventoryResultModel>();
//其他出入库
var otherCount = _db.Instance.Queryable <TWMOtherCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//盘亏盘盈出入库
var pCount = _db.Instance.Queryable <TWMProfitDeficitCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//销售出入库
var SaleCount = _db.Instance.Queryable <TWMSalesCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//采购出入库
var PurchaseCount = _db.Instance.Queryable <TWMPurchaseCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//生产出入库
var ProductCount = _db.Instance.Queryable <TWMProductionCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//出入库数量
var allCount = _db.Instance.UnionAll(otherCount, pCount, SaleCount, PurchaseCount, ProductCount).GroupBy(p => new { p.MaterialId, p.WarehouseId }).
Select(p => new TradeInventoryModel()
{
TradeNumber = SqlFunc.AggregateSum(p.WhNumber) - SqlFunc.AggregateSum(p.WhSendNumber),
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId
}).AS("t100");
var materialFileQuery = _db.Instance.Queryable <TBMMaterialFileDbModel>().Where(p => p.CompanyId == currentUser.CompanyID);
var ts = _db.Instance.Queryable(materialFileQuery, allCount, JoinType.Inner, (p1, p2) => p1.ID == p2.MaterialId);
#region 待出库数量
//其他待出库
var otherToOut = _db.Instance.Queryable <TWMOtherWhSendMainDbModel, TWMOtherWhSendDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNumber
});
//盘亏出库
var deficitToOut = _db.Instance.Queryable <TWMDeficitMainDbModel, TWMDeficitDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNumber
});
//销售出库
var saleToOut = _db.Instance.Queryable <TWMSalesMainDbModel, TWMSalesDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNum
});
//生产待出库
var productToOut = _db.Instance.Queryable <TWMProductionMainDbModel, TWMProductionDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNum
});
var allToOut = _db.Instance.UnionAll(otherToOut, deficitToOut, saleToOut, productToOut).AS("t101");
var tsToOut = allToOut.ToList().GroupBy(p => p.MaterialId).Select(p => new InventoryOut()
{
MaterialId = p.Key, Amount = p.Sum(m => m.Amount)
}).ToList()
.ToDictionary(p => p.MaterialId, p => new { MaterialId = p.MaterialId, Amount = p.Amount });
#endregion
string[] cQuery = { "warehouseid" };
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var QueryConditions1 = requestObject.QueryConditions.Where(p => !cQuery.Contains(p.Column.ToLower())).ToList();
if (QueryConditions1.Count() > 0)
{
var conditionals1 = SqlSugarUtil.GetConditionalModels(QueryConditions1);
foreach (ConditionalModel item in conditionals1)
{
item.FieldName = $"p1.{item.FieldName}";
}
ts.Where(conditionals1);
}
var QueryConditions2 = requestObject.QueryConditions.Where(p => cQuery.Contains(p.Column.ToLower())).FirstOrDefault();
if (QueryConditions2 != null)
{
int WarehouseId = Convert.ToInt32(QueryConditions2.Content);
ts = ts.Where((p1, p2) => p2.WarehouseId == WarehouseId);
}
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TBMMaterialFileDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
ts.OrderBy($"p1.{item.Column} {item.Condition}");
}
}
#region 最新采购/生产时间
var Purchase = _db.Instance.Queryable <TPSMPurchaseOrderDetailDbModel, TPSMPurchaseOrderMainDbModel>(
(t1, t2) => new object[] { JoinType.Left, t1.MainId == t2.ID })
.Where((t1, t2) => t2.CompanyId == currentUser.CompanyID && SqlFunc.IsNull(t2.DeleteFlag, false) != true &&
t2.AuditStatus == 2
)
.GroupBy((t1, t2) => t1.MaterialId)
.Select((t1, t2) => new { MaterialId = t1.MaterialId, OrderDate = SqlFunc.AggregateMax(t2.OrderDate) }).ToList()
.ToDictionary(p => p.MaterialId, p => new { MaterialId = p.MaterialId, OrderDate = p.OrderDate });
var Production = _db.Instance.Queryable <TMMProductionOrderDetailDbModel, TMMProductionOrderMainDbModel>(
(t1, t2) => new object[] { JoinType.Left, t1.MainId == t2.ID })
.Where((t1, t2) => t2.CompanyId == currentUser.CompanyID && SqlFunc.IsNull(t2.DeleteFlag, false) != true &&
t2.AuditStatus == 2)
.GroupBy((t1, t2) => t1.MaterialId)
.Select((t1, t2) => new { MaterialId = t1.MaterialId, OrderDate = SqlFunc.AggregateMax(t2.OrderDate) }).ToList()
.ToDictionary(p => p.MaterialId, p => new { MaterialId = p.MaterialId, OrderDate = p.OrderDate });
#endregion
int totalNum = -1;
if (requestObject.IsPaging)
{
int skipNum = requestObject.PageSize * (requestObject.PageIndex - 1);
totalNum = ts.Count();
result = await ts.Select((p1, p2) => new InventoryResultModel
{
MaterialName = p1.MaterialName,
BaseUnitId = p1.BaseUnitId,
ColorId = p1.ColorId,
MaterialCode = p1.MaterialCode,
MaterialId = p1.ID,
Spec = p1.Spec,
WarehouseAmount = p2.TradeNumber,
WarehouseUnitId = p1.WarehouseUnitId,
WarehouseRate = p1.WarehouseRate,
WarehouseId = p2.WarehouseId,
ShelfLife = p1.ShelfLife
}).Skip(skipNum).Take(requestObject.PageSize).ToListAsync();
}
else
{
result = await ts.Select((p1, p2) => new InventoryResultModel
{
MaterialName = p1.MaterialName,
BaseUnitId = p1.BaseUnitId,
ColorId = p1.ColorId,
MaterialCode = p1.MaterialCode,
MaterialId = p1.ID,
Spec = p1.Spec,
WarehouseAmount = p2.TradeNumber,
WarehouseUnitId = p1.WarehouseUnitId,
WarehouseRate = p1.WarehouseRate,
WarehouseId = p2.WarehouseId,
ShelfLife = p1.ShelfLife
}).ToListAsync();
}
var TWMPrimeCountDbList = _db.Instance.Queryable <TWMPrimeCountDbModel>().Where(p => p.CompanyId == currentUser.CompanyID).ToList();
result.ForEach(x =>
{
#region 最新生产/采购时间
string Production_PurchaseDateTime = "";
if (Production.ContainsKey(x.MaterialId))
{
Production_PurchaseDateTime += "" + Convert.ToDateTime(Production[x.MaterialId].OrderDate).ToString("yyyy-MM-dd");
}
else
{
Production_PurchaseDateTime += "无生产";
}
if (Purchase.ContainsKey(x.MaterialId))
{
Production_PurchaseDateTime += "/" + Convert.ToDateTime(Purchase[x.MaterialId].OrderDate).ToString("yyyy-MM-dd");
}
else
{
Production_PurchaseDateTime += "/无采购";
}
if (Production_PurchaseDateTime != "")
{
x.Production_PurchaseDateTime = Production_PurchaseDateTime;
}
#endregion
if (tsToOut.ContainsKey(x.MaterialId))
{
x.WarehouseAvailabilityAmount = x.WarehouseAmount - tsToOut[x.MaterialId].Amount;
}
if (x.WarehouseUnitId.HasValue)
{
if (tBMDictionary.ContainsKey(x.WarehouseUnitId.Value))
{
x.WarehouseUnitName = tBMDictionary[x.WarehouseUnitId.Value];
}
}
if (warehouseDic.ContainsKey(x.WarehouseId))
{
x.WarehouseName = warehouseDic[x.WarehouseId].Name;
x.WarehouseCode = warehouseDic[x.WarehouseId].Code;
}
if (tBMDictionary.ContainsKey(x.BaseUnitId))
{
x.BaseUnitName = tBMDictionary[x.BaseUnitId];
}
if (string.IsNullOrEmpty(x.WarehouseUnitName))
{
x.WarehouseUnitName = x.BaseUnitName;
}
if (x.WarehouseRate == null)
{
x.WarehouseRate = 1;
}
if (x.ColorId.HasValue)
{
if (tBMDictionary.ContainsKey(x.ColorId.Value))
{
x.ColorName = tBMDictionary[x.ColorId.Value];
}
}
var firstEntity = TWMPrimeCountDbList.Where(p => p.WarehouseId == x.WarehouseId && p.MaterialId == x.MaterialId).FirstOrDefault();
if (firstEntity != null)
{
x.WarehouseAmount = x.WarehouseAmount + firstEntity.PrimeNum;
x.PrimeNum = firstEntity.PrimeNum;
}
});
return(ResponseUtil <List <InventoryResultModel> > .SuccessResult(result, totalNum));
}
catch (Exception ex)
{
return(ResponseUtil <List <InventoryResultModel> > .FailResult(null, ex.Message));
}
}
public void Init()
{
var x2 = DB.Queryable <School>().Where(x => x.Id == SqlFunc.Subqueryable <School>().Where(y => y.Id == SqlFunc.Subqueryable <Student>().Where(yy => y.Id == x.Id).Select(yy => yy.Id)).Select(y => y.Id)).ToSql();
if (!x2.Key.Contains("STudent"))
{
// throw new Exception("bug2 error");
}
var UserNameOrName = "111";
var OrganizationUnitId = 0;
var RoleId = 0;
var sql = DB.Queryable <User>().//一对多的子查询
WhereIF(!string.IsNullOrWhiteSpace(UserNameOrName), t1 => t1.Name.Contains(UserNameOrName)).
Where(t1 =>
SqlFunc.Subqueryable <UserOrganizationUnit>().
Where(t2 => t2.UserId == t1.Id).
WhereIF(OrganizationUnitId > 0, t2 => t2.OrganizationUnitId == OrganizationUnitId).Any())
// Where(t1 => SqlFunc.Subqueryable<UserRole>().
//Where(t3 => t3.UserId == t1.Id).
//WhereIF(RoleId > 0, t3 => t3.RoleId == RoleId).Any())
.Select(t1 => new User {
Id = SqlFunc.GetSelfAndAutoFill(t1.Id)
}).ToSql();
var model = DB.Queryable <ClientsModel, VipAccountsModel, AccountsModel, tLogonHistoryModel, VipBenefitsModel, LevelSettingModel, JewelsModel>((a, b, c, d, e, f, g) => new object[] {
JoinType.Left, a.ClientID == b.ClientID,
JoinType.Left, a.ClientID == c.ClientID && c.TournamentID == 0,
JoinType.Left, a.ClientID == d.ClientID,
JoinType.Left, (e.MinVipCredit <= b.VipCredit && e.MaxVipCredit >= b.VipCredit) && (e.MinConsumeAmount <= b.AccumulatedConsumeAmount && e.MaxConsumeAmount >= b.AccumulatedConsumeAmount),
JoinType.Left, (c.ExperiencePoints >= f.MinExperiencePoints && c.ExperiencePoints < f.MaxExperiencePoints) || (c.ExperiencePoints > f.MaxExperiencePoints && f.UserLevel == 30),
JoinType.Left, g.ClientID == a.ClientID
})
.WhereIF(true, (a, b, c, d, e, f, g) => a.ClientID == 1)
.WhereIF(!string.IsNullOrEmpty("a"), (a, b, c, d, e, f, g) => a.NickName == "a")
.Select((a, b, c, d, e, f, g) => new
{
GoldAmount = SqlFunc.Subqueryable <ExposureModel>().Where(s => s.TournamentID == 0 && s.ClientID == a.ClientID).Sum(s => SqlFunc.IsNull(SqlFunc.AggregateSum(s.Exposure), 0)),
ClientID = a.ClientID,
NickName = a.NickName,
UserChannel = a.UserChannel,
CountryCode = d.CountryCode,
Platform = a.Platform,
Email = a.Email,
PhoneNumber = a.PhoneNumber,
RegisteredTime = a.RegisteredTime,
DiamondAmount = SqlFunc.IsNull(g.JewelCount, 0),
AccumulatedRechargeAmount = SqlFunc.IsNull(b.AccumulatedRechargeAmount, 0),
VipLevel = SqlFunc.IsNull(e.VipLevel, 0),
UserLevel = SqlFunc.IsNull(f.UserLevel, 0)
})
.With(SqlWith.NoLock)
.ToSql();
var _sql = DB.Insertable(new UserInfo
{
BrandId = -1,
UserLevel = 1
}).IgnoreColumns(m => new { m.BlockingTime, m.CreditUpdatetime }).ToSql();
var _sql2 = DB.Insertable(new UserInfo
{
BrandId = -1,
UserLevel = 1
}).IgnoreColumns(m => new { m.UserId }).ToSql();
var _sql3 = DB.Updateable(new UserInfo
{
BrandId = -1,
UserLevel = 1
}).IgnoreColumns(m => new { m.CreditUpdatetime, m.UserId }).ToSql();
DB.CodeFirst.InitTables(typeof(DataTest));
DB.Insertable(new DataTest()).ExecuteCommand();
// 初始化实体表
DB.CodeFirst.SetStringDefaultLength(255).InitTables(typeof(TestA));
var testa = new TestA();
testa.Col1 = "2333333";
testa.Col3 = "444";
DB.Saveable(testa).ExecuteCommand();
Guid newCarTypePictureId = Guid.Empty;
Guid carTypePictureId = Guid.Empty;
DB.CodeFirst.InitTables(typeof(Picture), typeof(JobPlan));
DB.Updateable <Picture>()
.UpdateColumns(p => p.Value == SqlFunc.Subqueryable <Picture>()
.Where(pp => pp.ID == newCarTypePictureId)
.Select(pp => pp.Value))
.Where(p => p.ID == carTypePictureId)
.ExecuteCommand();
DB.Updateable <Picture>()
.UpdateColumns(p => p.Value == SqlFunc.Subqueryable <Picture>()
.Select(pp => pp.Value))
.Where(p => p.ID == carTypePictureId).ExecuteCommand();
var list = new List <JobPlan>()
{
new JobPlan()
{
},
new JobPlan()
{
}
};
DB.Updateable(new JobPlan()
{
})
.WhereColumns(s => new { s.CmdNo })
.UpdateColumns(s => new
{
s.HeatNo,
s.CmdNo
}).ExecuteCommand();
DB.CodeFirst.InitTables(typeof(VMaterialInfo), typeof(TStock), typeof(TTempStock));
var GoodsList = DB.Queryable <VMaterialInfo, TStock>((vmg, ts) => new object[] {
JoinType.Left, vmg.FMICode == ts.FMICode
})
.Select((vmg, ts) => new
{
AbleQty = SqlFunc.ToInt32(ts.FQty - SqlFunc.Subqueryable <TTempStock>().Where(s => s.FMICode == vmg.FMICode && s.FK_Store == "")
.Select(s => SqlFunc.AggregateSum(s.FKCSL)))
}).ToList();
var GoodsList2 = DB.Queryable <VMaterialInfo, TStock>((vmg, ts) => new object[] {
JoinType.Left, vmg.FMICode == ts.FMICode
})
.Where((vmg, ts) => ts.FK_Store == "" && vmg.FMICode == vmg.FMICode)
.Select((vmg, ts) => new
{
PKID = vmg.PKID,
FMICode = vmg.FMICode,
FMIName = vmg.FMIName,
FGauge = vmg.FGauge,
FBIName = vmg.FBIName,
FK_FOrigin = vmg.FK_FOrigin,
FOEM = vmg.FOEM,
FSIName = vmg.FSIName,
FUIName = vmg.FUIName,
OutFQty = SqlFunc.ToInt32(ts.FQty)
,
InFQty = SqlFunc.Subqueryable <TStock>().Where(s => s.FMICode == ts.FMICode && s.FK_Store == "").Select(s => SqlFunc.ToInt32(SqlFunc.IsNull(s.FQty, 0)))
,
TempQty = SqlFunc.IsNull(SqlFunc.Subqueryable <TTempStock>().Where(s => s.FMICode == vmg.FMICode && s.FK_Store == "")
.GroupBy(s => new { s.FMICode, s.FK_Store })
.Select(s => SqlFunc.AggregateSum(SqlFunc.ToInt32(s.FKCSL))), 0)
,
AbleQty = ts.FQty - SqlFunc.Subqueryable <TTempStock>().Where(s => s.FMICode == vmg.FMICode && s.FK_Store == "")
.Select(s => SqlFunc.AggregateSum(s.FKCSL))
}).ToList();
DB.CodeFirst.InitTables <h5linkpassloginfo, logtype>();
DB.Updateable <h5linkpassloginfo>().UpdateColumns(it =>
new h5linkpassloginfo()
{
LogKeyId = SqlFunc.Subqueryable <logtype>().Where(s => s.LogKey == "openpage").Select(s => s.Id),
StrVal = "sdsdsdsd"
}).Where(it => it.Id == 1).ExecuteCommand();
}
private static void Subqueryable()
{
var db = GetInstance();
var i = 0;
var sumflat2num = db.Queryable <Student, Student>((s1, s2) =>
new object[] { JoinType.Left, s1.Id == s2.Id })
.Select((s1, s2) => new Student
{
Id = SqlFunc.IsNull(SqlFunc.AggregateSum(SqlFunc.IIF(s1.Id == 1, s1.Id, s1.Id * -1)), 0)
})
.First();
var getAll11 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Max(s => s.Id) == i).ToList();
var getAll12 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Max(s => s.Id) == 1).ToList();
var getAll7 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Any()).ToList();
var getAll9 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Count() == 1).ToList();
var getAll10 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).OrderBy(s => s.Id).Select(s => s.Id) == 1).ToList();
var getAll14 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).OrderByDesc(s => s.Id).Select(s => s.Id) == 1).ToList();
var getAll8 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Where(s => s.Name == it.Name).NotAny()).ToList();
var getAll1 = db.Queryable <Student>().Where(it => it.Id == SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Select(s => s.Id)).ToList();
var getAll2 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.Id == sc.Id
})
.Where(st => st.Id == SqlFunc.Subqueryable <School>().Where(s => s.Id == st.Id).Select(s => s.Id))
.ToList();
var getAll3 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.Id == sc.Id
})
.Select(st =>
new
{
name = st.Name,
id = SqlFunc.Subqueryable <School>().Where(s => s.Id == st.Id).Select(s => s.Id)
})
.ToList();
var getAll4 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Select(s => s.Id)
}).ToList();
var getAll5 = db.Queryable <Student>().Select(it =>
new Student
{
Name = it.Name,
Id = SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Select(s => s.Id)
}).ToList();
var getAll6 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <Student>().Where(s => s.Id == it.Id).Sum(s => (int)s.SchoolId)
}).ToList();
var getAll66 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <Student>().Where(s => s.Id == it.Id).Sum(s => s.SchoolId.Value)
}).ToList();
var getAll666 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <Student>().Where(s => s.Id == it.Id).Min(s => s.Id)
}).ToList();
}
/// <summary>
/// 获取用量统计信息
/// </summary>
/// <param name="_type">查找类型：1园区，2宿舍楼，3宿舍，5为所有园区，0为所有园区最近一年的统计，默认0</param>
/// <param name="_id">相关类型的ID</param>
/// <param name="_start">开始日期</param>
/// <param name="_end">截至日期</param>
/// <returns></returns>
public Used_datas GetUsedDatas(int _type = 0, int _id = 0, DateTime _start = default(DateTime), DateTime _end = default(DateTime))
{
var data = new Used_datas();
var list = new List <Used_data>();
var sql = Db.Queryable <T_Used>().Where(u => u.Used_model_state && u.Used_is_active);
switch (_type)
{
case 1:
// 获取园区信息
var Dorm = new Dorms();
var dorm = Dorm.FindById(_id);
if (dorm == null)
{
data.info = "找不到编号为 " + _id + " 的园区";
break;
}
data.title = "园区 " + dorm.Dorm_nickname + " 从 " + _start + " 到 " + _end + " 的统计图表";
sql = sql.Where(u => u.Used_dorm_id == _id);
break;
case 2:
// 获取宿舍楼信息
var B = new Buildings();
var b = B.FindById(_id);
if (b == null)
{
data.info = "找不到编号为 " + _id + " 的宿舍楼";
break;
}
data.title = "宿舍楼 " + b.Building_nickname + " 从 " + _start + " 到 " + _end + " 的统计图表";
sql = sql.Where(u => u.Used_building_id == _id);
break;
case 3:
// 获取宿舍信息
var R = new Rooms();
var r = R.FindById(_id);
if (r == null)
{
data.info = "找不到编号为 " + _id + " 的宿舍";
break;
}
data.title = "宿舍 " + r.Room_nickname + " 从 " + _start + " 到 " + _end + " 的统计图表";
sql = sql.Where(u => u.Used_room_id == _id);
break;
case 5:
data.title = "所有园区从 " + _start + " 到 " + _end + " 的统计图表";
break;
default:
data.title = "所有园区最近一年的统计图表";
_start = DateTime.Now.AddYears(-1);
_end = DateTime.Now;
break;
}
sql = sql.Where(u => SqlFunc.Between(u.Used_post_date, _start, _end));
list = sql.GroupBy(u => SqlFunc.Substring(u.Used_post_date, 1, 7)).Select(u => new Used_data
{
Date = SqlFunc.Substring(u.Used_post_date, 0, 7),
Cold_water_value = SqlFunc.AggregateSum(u.Used_cold_water_value),
Electric_value = SqlFunc.AggregateSum(u.Used_electric_value),
Hot_water_value = SqlFunc.AggregateSum(u.Used_hot_water_value)
}).ToList();
data.Add(list); // 加入返回列表
if (list.Count < 1)
{
data.info = "暂无相关数据";
}
return(data);
}