public QueryResult SalesStatisticsReportExport(SalesStatisticsReportQueryFilter filter)
{
int totalCount;
var ds = ObjectFactory <IFinancialReportDA> .Instance.SalesStatisticsReportQuery(filter, out totalCount);
var dataTable = ds.Tables[0];
return(new QueryResult()
{
Data = dataTable, TotalCount = totalCount
});
}
public ActionResult FinancialReportDataText()
{
SalesStatisticsReportQueryFilter filter = SerializationUtility.JsonDeserialize2 <SalesStatisticsReportQueryFilter>(Request.Form["data"]);
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;
filter.SortFields = "B.ProductID DESC";
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);
}
var result = FinancialReportService.SalesStatisticsReportQuery(filter);
string text = "";
if (result.SalesStatisticsResult.ResultList != null && result.SalesStatisticsResult.ResultList.Count > 0)
{
string page = string.Format("{0}： --- 销售成本：{1} 销售金额：{2} 优惠金额：{3} 实际销售金额：{4} 商品毛利：{5}",
"本页小计",
result.CostReportStatisticList[0].ProductCost,
result.CostReportStatisticList[0].ProductPriceAmount,
result.CostReportStatisticList[0].PromotionDiscountAmount,
result.CostReportStatisticList[0].ProductSaleAmount,
result.CostReportStatisticList[0].ProductGrossMargin);
string Total = string.Format("{0}： --- 销售成本：{1} 销售金额：{2} 优惠金额：{3} 实际销售金额：{4} 商品毛利：{5}",
"全部总计",
result.CostReportStatisticList[1].ProductCost,
result.CostReportStatisticList[1].ProductPriceAmount,
result.CostReportStatisticList[1].PromotionDiscountAmount,
result.CostReportStatisticList[1].ProductSaleAmount,
result.CostReportStatisticList[1].ProductGrossMargin);
text = "<div class='col-md-12' style='color:red;'>" + page + "</div><div class='col-md-12' style='color:red;'>" + Total + "</div>";
}
return(Content(text));
}
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));
}
public static SalesStatisticsReport SalesStatisticsReportQuery(SalesStatisticsReportQueryFilter filter)
{
return(FinancialReportDA.SalesStatisticsReportQuery(filter));
}
/// <summary>
/// Table[0]: Result,
/// Table[1]: Statistics
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataSet SalesStatisticsReportQuery(SalesStatisticsReportQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingInfo = CreatePagingInfo(filter.PagingInfo);
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("FinancialReport.SalesStatisticsReportQuery");
DataSet result = null;
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "B.ProductID DESC, A.PayTypeSysNo"))
{
#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"));
}
return(result);
}
/// <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));
}