/// <summary>
/// 历史接单量统计
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public List <MoreReportModel> GetOrderMore(HomeRequest request)
{
var where = new Where <TbWorkOrder>();
//组织机构
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = _WorkOrderLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 5);
if (SiteList.Count > 0)
{
where.And(p => p.SiteCode.In(SiteList));
}
}
if (!string.IsNullOrEmpty(request.ProcessFactoryCode))
{
where.And(p => p.ProcessFactoryCode == request.ProcessFactoryCode);
}
where.And(new WhereClip("YEAR(DistributionTime)=" + request.DayMonth.Year));
var group = new GroupByClip("month(DistributionTime)");
var select = new Field("convert(varchar,month(DistributionTime))+'月' name,SUM(WeightTotal) as y");
var orderList = Db.Context.From <TbWorkOrder>().Select(select)
.Where(where)
.GroupBy(group).ToList <MoreReportModel>();
CreatLast(orderList);
return(orderList);
}
public virtual string LambdaSelect(LambdaQueryHelper selectHelper, ref Dictionary <string, Parameter> parameters, int?pageIndex, int?pageSize, bool loadOrderby = true)
{
{
string topSql = this.Configuration.Dialect.GetTopString(Convert.ToInt32(pageIndex), Convert.ToInt32(pageSize));
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
if (!string.IsNullOrEmpty(selectHelper.DistinctString))
{
sql.Append(selectHelper.DistinctString);
sql.Append(" ");
}
sql.Append(topSql);
sql.Append(BuildSelectColumns(selectHelper.ClassMap, selectHelper.Fields, selectHelper.Joins));
sql.Append(" FROM ");
sql.Append(BuildFrom(selectHelper.ClassMap, selectHelper.Joins, selectHelper.EnabledNoLock));
sql.Append(" ");
if (!WhereClip.IsNullOrEmpty(selectHelper.WhereClip))
{
sql.Append(selectHelper.WhereClip.WhereString);
}
if (!GroupByClip.IsNullOrEmpty(selectHelper.GroupByClip))
{
sql.Append(selectHelper.GroupByClip.GroupByString);
if (!WhereClip.IsNullOrEmpty(selectHelper.HavingClip))
{
sql.Append(" HAVING ");
sql.Append(selectHelper.HavingClip.ToString());
}
}
if (loadOrderby && !OrderByClip.IsNullOrEmpty(selectHelper.OrderByClip))
{
sql.Append(selectHelper.OrderByClip.OrderByString);
sql.Append(" ");
}
if (pageIndex != null && pageSize != null && string.IsNullOrEmpty(topSql))
{
Dictionary <string, object> pageParameters = new Dictionary <string, object>();
string pageSql = this.Configuration.Dialect.GetPagingSql(sql.ToString(), Convert.ToInt32(pageIndex), Convert.ToInt32(pageSize), pageParameters);
foreach (var item in pageParameters)
{
if (!parameters.ContainsKey(item.Key))
{
parameters.Add(item.Key, new Parameter(item.Key, item.Value));
}
}
return(pageSql);
}
return(sql.ToString());
}
}
/// <summary>
/// 供应量需求量统计
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public Tuple <List <MoreReportModel>, List <MoreReportModel> > GetSupplyrMore(HomeRequest request)
{
var where = new Where <TbFactoryBatchNeedPlan>();
var where2 = new Where <TbSupplyList>();
//组织机构
if (!string.IsNullOrWhiteSpace(request.SiteCode))
{
List <string> SiteList = _WorkOrderLogic.GetCompanyWorkAreaOrSiteList(request.SiteCode, 4);
if (SiteList.Count > 0)
{
where.And(p => p.WorkAreaCode.In(SiteList));
where2.And(p => p.WorkAreaCode.In(SiteList));
}
}
if (!string.IsNullOrEmpty(request.ProcessFactoryCode))
{
where.And(p => p.ProcessFactoryCode == request.ProcessFactoryCode);
where2.And(p => p.ProcessFactoryCode == request.ProcessFactoryCode);
}
//需求量
where.And(new WhereClip("YEAR(InsertTime)=" + request.DayMonth.Year));
var group = new GroupByClip("month(InsertTime)");
var select = new Field("convert(varchar,month(InsertTime))+'月' name,SUM(BatchPlanTotal) as y");
var batchList = Db.Context.From <TbFactoryBatchNeedPlan>().Select(select)
.Where(where)
.GroupBy(group).ToList <MoreReportModel>();
CreatLast(batchList);
//供应量
where2.And(new WhereClip("YEAR(SupplyCompleteTime)=" + request.DayMonth.Year));
var group2 = new GroupByClip("month(SupplyCompleteTime)");
var select2 = new Field("convert(varchar,month(SupplyCompleteTime))+'月' name,SUM(HasSupplierTotal) as y");
var supplyList = Db.Context.From <TbSupplyList>().Select(select2)
.Where(where2)
.GroupBy(group2).ToList <MoreReportModel>();
CreatLast(supplyList);
return(new Tuple <List <MoreReportModel>, List <MoreReportModel> >(batchList, supplyList));
}
public void Append <T>(GroupByClip <T> groupby)
{
this.AppendColumnExpression(false, typeof(T), groupby.Expression);
}
/// <summary>
/// 进行分组操作
/// </summary>
/// <param name="groupBy"></param>
/// <returns></returns>
public TableRelation <T> GroupBy(GroupByClip groupBy)
{
section.GroupBy(groupBy);
return(this);
}