public ActionResult GetFinancialReportData() { SalesStatisticsReportQueryFilter filter = BuildQueryFilterEntity <SalesStatisticsReportQueryFilter>(); filter.C1SysNo = filter.Category.C1SysNo == 0 ? null : filter.Category.C1SysNo; filter.C2SysNo = filter.Category.C2SysNo == 0 ? null : filter.Category.C2SysNo; filter.C3SysNo = filter.Category.C3SysNo == 0 ? null : filter.Category.C3SysNo; int MerchantSysNo = UserAuthHelper.GetCurrentUser().SellerSysNo; filter.VendorSysNoList = new List <int>(); filter.VendorSysNoList.Add(MerchantSysNo); filter.SOStatusList = new List <int>(); if (filter.SOStatus.HasValue) { filter.SOStatusList.Add(filter.SOStatus.Value); } SalesStatisticsReport result = FinancialReportService.SalesStatisticsReportQuery(filter); return(AjaxGridJson(result.SalesStatisticsResult)); }
/// <summary> /// Table[0]: Result, /// Table[1]: Statistics /// </summary> /// <param name="filter"></param> /// <param name="totalCount"></param> /// <returns></returns> public static SalesStatisticsReport SalesStatisticsReportQuery(SalesStatisticsReportQueryFilter filter) { CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("FinancialReportSalesStatisticsReportQuery"); DataSet result = null; int totalCount = 0; SalesStatisticsReport adjustInfo = new SalesStatisticsReport(); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, filter, string.IsNullOrEmpty(filter.SortFields) ? "B.ProductID DESC, A.PayTypeSysNo" : filter.SortFields)) { #region Set dynamic codition for where if (filter.SOStatusList != null && filter.SOStatusList.Count > 0) { sqlBuilder.ConditionConstructor.AddInCondition <int>( QueryConditionRelationType.AND, "SOStatus", System.Data.DbType.Int32, filter.SOStatusList); } if (filter.SODateFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "rpt.OrderDate", DbType.DateTime, "@OrderDateFrom_query", QueryConditionOperatorType.MoreThanOrEqual, filter.SODateFrom.Value); } if (filter.SODateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "rpt.OrderDate", DbType.DateTime, "@SODateTo_query", QueryConditionOperatorType.LessThan, filter.SODateTo.Value); } if (filter.C1SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "rpt.C1SysNo", DbType.Int32, "@C1SysNo_query", QueryConditionOperatorType.Equal, filter.C1SysNo); } if (filter.C2SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "rpt.C2SysNo", DbType.Int32, "@C2SysNo_query", QueryConditionOperatorType.Equal, filter.C2SysNo); } if (filter.C3SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "rpt.C3SysNo", DbType.Int32, "@C3SysNo_query", QueryConditionOperatorType.Equal, filter.C3SysNo); } if (!string.IsNullOrWhiteSpace(filter.ProductID)) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "rpt.ProductID", DbType.StringFixedLength, "@ProductID_query", QueryConditionOperatorType.Equal, filter.ProductID); } if (!string.IsNullOrWhiteSpace(filter.BrandName)) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format(" (brand.BrandName_Ch = N'{0}' OR brand.BrandName_En = N'{0}') ", filter.BrandName.Replace("'", "''"))); } sqlBuilder.ConditionConstructor.AddInCondition( QueryConditionRelationType.AND, "rpt.VendorSysNo", DbType.Int32, filter.VendorSysNoList); sqlBuilder.ConditionConstructor.AddInCondition( QueryConditionRelationType.AND, "rpt.WarehouseNumber", DbType.StringFixedLength, filter.WarehouseNumberList); #endregion dataCommand.CommandText = sqlBuilder.BuildQuerySql(); result = dataCommand.ExecuteDataSet(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); } adjustInfo.SalesStatisticsResult.PageInfo = new PageInfo() { PageIndex = filter.PageIndex, PageSize = filter.PageSize, TotalCount = totalCount, SortBy = filter.SortFields }; adjustInfo.SalesStatisticsResult.ResultList = new List <SalesStatistics>(); if (null != result && result.Tables.Count > 0) { if (result.Tables.Count >= 1) { DataTable mainInfoDt = result.Tables[0]; if (mainInfoDt.Rows.Count > 0) { foreach (DataRow dr in mainInfoDt.Rows) { SalesStatistics aa = DataMapper.GetEntity <SalesStatistics>(dr); adjustInfo.SalesStatisticsResult.ResultList.Add(aa); } } } if (result.Tables.Count >= 2) { DataTable itemsInfoDt = result.Tables[1]; if (itemsInfoDt.Rows.Count > 0) { foreach (DataRow dr in itemsInfoDt.Rows) { adjustInfo.CostReportStatisticList.Add(DataMapper.GetEntity <IncomeCostReportStatistic>(dr)); } } } } return(adjustInfo); }
public FileResult AjaxFinancialExportData(string queryString) { //每页显示条数: SalesStatisticsReportQueryFilter getQueryEntity = SerializationUtility.JsonDeserialize2 <SalesStatisticsReportQueryFilter>(queryString); getQueryEntity.C1SysNo = getQueryEntity.Category.C1SysNo == 0 ? null : getQueryEntity.Category.C1SysNo; getQueryEntity.C2SysNo = getQueryEntity.Category.C2SysNo == 0 ? null : getQueryEntity.Category.C2SysNo; getQueryEntity.C3SysNo = getQueryEntity.Category.C3SysNo == 0 ? null : getQueryEntity.Category.C3SysNo; getQueryEntity.SortFields = "B.ProductID DESC"; int MerchantSysNo = UserAuthHelper.GetCurrentUser().SellerSysNo; getQueryEntity.VendorSysNoList = new List <int>(); getQueryEntity.VendorSysNoList.Add(MerchantSysNo); getQueryEntity.SOStatusList = new List <int>(); if (getQueryEntity.SOStatus.HasValue) { getQueryEntity.SOStatusList.Add(getQueryEntity.SOStatus.Value); } getQueryEntity.PageSize = 10000000; getQueryEntity.PageIndex = 0; SalesStatisticsReport result = FinancialReportService.SalesStatisticsReportQuery(getQueryEntity); List <DataTable> dataTableList = new List <DataTable>(); if (result.SalesStatisticsResult.ResultList.Count <= 0) { throw new BusinessException("无数据可导出!!!"); } DataTable newDt = ToDataTableTow(result.SalesStatisticsResult.ResultList); dataTableList.Add(newDt); List <ColumnData> columndatalist = new List <ColumnData>(); ColumnData columndata = null; columndata = new ColumnData() { FieldName = "ProductID", Width = 20, Title = "商品ID" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "ProductName", Width = 30, Title = "商品名称" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "C1Name", Width = 15, Title = "一级分类" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "C2Name", Width = 15, Title = "二级分类" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "C3Name", Width = 15, Title = "三级分类", ValueFormat = "" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "BeginDate", Width = 20, Title = "开始时间" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "EndDate", Width = 20, Title = "结束时间" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "BrandName", Width = 20, Title = "品牌" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "VendorName", Width = 20, Title = "供应商" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "StockName", Width = 20, Title = "仓库" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "BMCode", Width = 20, Title = "BM编号" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "ProductProperty1", Width = 20, Title = "属性1" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "ProductProperty2", Width = 20, Title = "属性2" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "Quantity", Width = 20, Title = "数量" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "ProductCost", Width = 20, Title = "销售成本" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "ProductPriceAmount", Width = 20, Title = "销售金额" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "PromotionDiscountAmount", Width = 20, Title = "优惠金额" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "ProductSaleAmount", Width = 20, Title = "实际销售金额" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "PayTypeName", Width = 20, Title = "支付方式" }; columndatalist.Add(columndata); columndata = new ColumnData() { FieldName = "ProductGrossMargin", Width = 20, Title = "商品毛利" }; columndatalist.Add(columndata); List <List <ColumnData> > columnList = new List <List <ColumnData> >(); columnList.Add(columndatalist); //步骤5:调用生成Excel的公共方法,返回 /*例如: * IFileExport excelExport = new ExcelFileExporter(); * byte[] excelByte = excelExport.CreateFile(dataTableList, columnList, null, out fileName, "测试导出Excel"); * return File(new MemoryStream(excelByte), "application/ms-excel", fileName); */ string fileName = ""; IFileExport excelExport = new ExcelFileExporter(); byte[] excelByte = excelExport.CreateFile(dataTableList, columnList, null, out fileName, "销售统计报表"); return(File(new MemoryStream(excelByte), "application/ms-excel", fileName)); }