/// <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); }