private void btnCreateReport_Click(object sender, EventArgs e)
{
SaleStatisticsQuery query = new SaleStatisticsQuery {
StartDate = this.dateStart,
EndDate = this.dateEnd,
SortBy = this.sortBy,
SortOrder = SortAction.Desc
};
DataTable memberStatisticsNoPage = SalesHelper.GetMemberStatisticsNoPage(query);
string s = (string.Empty + "会员") + ",订单数" + ",消费金额\r\n";
foreach (DataRow row in memberStatisticsNoPage.Rows)
{
s = s + row["UserName"].ToString();
s = s + "," + row["OrderCount"].ToString();
s = s + "," + row["SaleTotals"].ToString() + "\r\n";
}
this.Page.Response.Clear();
this.Page.Response.Buffer = false;
this.Page.Response.Charset = "GB2312";
this.Page.Response.AppendHeader("Content-Disposition", "attachment;filename=MemberRanking.csv");
this.Page.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
this.Page.Response.ContentType = "application/octet-stream";
this.Page.EnableViewState = false;
this.Page.Response.Write(s);
this.Page.Response.End();
}
private void btnCreateReport_Click(object sender, EventArgs e)
{
SaleStatisticsQuery productSale = new SaleStatisticsQuery();
productSale.StartDate = dateStart;
productSale.EndDate = dateEnd;
productSale.PageSize = pager.PageSize;
productSale.SortBy = "ProductSaleCounts";
productSale.SortOrder = SortAction.Desc;
int totalProductSales = 0;
DataTable productSalesNoPage = SubsiteSalesHelper.GetProductSalesNoPage(productSale, out totalProductSales);
string s = string.Empty + "排行,商品名称,商家编码,销售量,销售额,利润\r\n";
foreach (DataRow row in productSalesNoPage.Rows)
{
s = s + row["IDOfSaleTotals"].ToString();
s = s + "," + row["ProductName"].ToString();
s = s + "," + row["SKU"].ToString();
s = s + "," + row["ProductSaleCounts"].ToString();
s = s + "," + row["ProductSaleTotals"].ToString();
s = s + "," + row["ProductProfitsTotals"].ToString() + "\r\n";
}
Page.Response.Clear();
Page.Response.Buffer = false;
Page.Response.Charset = "GB2312";
Page.Response.AppendHeader("Content-Disposition", "attachment;filename=ProductSaleRanking.csv");
Page.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Page.Response.ContentType = "application/octet-stream";
Page.EnableViewState = false;
Page.Response.Write(s);
Page.Response.End();
}
public DataTable GetMemberStatisticsNoPage(SaleStatisticsQuery query)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(BuildMemberStatisticsQuery(query));
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return DataHelper.ConverDataReaderToDataTable(reader);
}
}
private void BindList()
{
SaleStatisticsQuery query = new SaleStatisticsQuery();
query.PageIndex = pager.PageIndex;
query.StartDate = startTime;
query.EndDate = endTime;
DbQueryResult saleOrderLineItemsStatistics = SubsiteSalesHelper.GetSaleOrderLineItemsStatistics(query);
grdOrderLineItem.DataSource = saleOrderLineItemsStatistics.Data;
grdOrderLineItem.DataBind();
pager.TotalRecords = saleOrderLineItemsStatistics.TotalRecords;
grdOrderLineItem.PageSize = query.PageSize;
}
private void BindProductSaleStatistics()
{
SaleStatisticsQuery query = new SaleStatisticsQuery();
query.PageSize = pager.PageSize;
query.PageIndex = pager.PageIndex;
query.SortBy = "BuyPercentage";
query.SortOrder = SortAction.Desc;
int totalProductSales = 0;
DataTable productVisitAndBuyStatistics = SubsiteSalesHelper.GetProductVisitAndBuyStatistics(query, out totalProductSales);
grdProductSaleStatistics.DataSource = productVisitAndBuyStatistics;
grdProductSaleStatistics.DataBind();
pager.TotalRecords = totalProductSales;
}
public DataTable GetProductSalesNoPage(SaleStatisticsQuery productSale, out int totalProductSales)
{
DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_ProductSalesNoPage_Get");
this.database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, BuildProductSaleQuery(productSale));
this.database.AddOutParameter(storedProcCommand, "TotalProductSales", DbType.Int32, 4);
DataTable table = null;
using (IDataReader reader = this.database.ExecuteReader(storedProcCommand))
{
table = DataHelper.ConverDataReaderToDataTable(reader);
}
totalProductSales = (int) this.database.GetParameterValue(storedProcCommand, "TotalProductSales");
return table;
}
private void BindList()
{
SaleStatisticsQuery query = new SaleStatisticsQuery {
PageIndex = this.pager.PageIndex,
PageSize = this.pager.PageSize,
StartDate = this.startTime,
EndDate = this.endTime
};
DbQueryResult saleOrderLineItemsStatistics = SalesHelper.GetSaleOrderLineItemsStatistics(query);
this.grdOrderLineItem.DataSource = saleOrderLineItemsStatistics.Data;
this.grdOrderLineItem.DataBind();
this.pager.TotalRecords = saleOrderLineItemsStatistics.TotalRecords;
this.grdOrderLineItem.PageSize = query.PageSize;
}
private void BindProductSaleRanking()
{
SaleStatisticsQuery productSale = new SaleStatisticsQuery();
productSale.StartDate = dateStart;
productSale.EndDate = dateEnd;
productSale.PageSize = pager.PageSize;
productSale.PageIndex = pager.PageIndex;
productSale.SortBy = "ProductSaleCounts";
productSale.SortOrder = SortAction.Desc;
int totalProductSales = 0;
DataTable productSales = SubsiteSalesHelper.GetProductSales(productSale, out totalProductSales);
grdProductSaleStatistics.DataSource = productSales;
grdProductSaleStatistics.DataBind();
pager.TotalRecords = totalProductSales;
}
private void BindMemberRanking()
{
SaleStatisticsQuery query = new SaleStatisticsQuery();
query.StartDate = dateStart;
query.EndDate = dateEnd;
query.PageSize = pager.PageSize;
query.PageIndex = pager.PageIndex;
query.SortBy = sortBy;
query.SortOrder = SortAction.Desc;
int totalProductSales = 0;
DataTable memberStatistics = SalesHelper.GetMemberStatistics(query, out totalProductSales);
grdProductSaleStatistics.DataSource = memberStatistics;
grdProductSaleStatistics.DataBind();
pager1.TotalRecords = pager.TotalRecords = totalProductSales;
}
public DataTable GetMemberStatistics(SaleStatisticsQuery query, out int totalProductSales)
{
DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_MemberStatistics_Get");
this.database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, query.PageIndex);
this.database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, query.PageSize);
this.database.AddInParameter(storedProcCommand, "IsCount", DbType.Boolean, query.IsCount);
this.database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, BuildMemberStatisticsQuery(query));
this.database.AddOutParameter(storedProcCommand, "TotalProductSales", DbType.Int32, 4);
DataTable table = null;
using (IDataReader reader = this.database.ExecuteReader(storedProcCommand))
{
table = DataHelper.ConverDataReaderToDataTable(reader);
}
totalProductSales = (int) this.database.GetParameterValue(storedProcCommand, "TotalProductSales");
return table;
}
private void BindMemberRanking()
{
SaleStatisticsQuery query = new SaleStatisticsQuery {
StartDate = this.dateStart,
EndDate = this.dateEnd,
PageSize = this.pager.PageSize,
PageIndex = this.pager.PageIndex,
SortBy = this.sortBy,
SortOrder = SortAction.Desc
};
int totalProductSales = 0;
DataTable memberStatistics = SalesHelper.GetMemberStatistics(query, out totalProductSales);
this.grdProductSaleStatistics.DataSource = memberStatistics;
this.grdProductSaleStatistics.DataBind();
this.pager1.TotalRecords = this.pager.TotalRecords = totalProductSales;
}
private void BindProductSaleRanking()
{
SaleStatisticsQuery productSale = new SaleStatisticsQuery {
StartDate = this.dateStart,
EndDate = this.dateEnd,
PageSize = this.pager.PageSize,
PageIndex = this.pager.PageIndex,
SortBy = "ProductSaleCounts",
SortOrder = SortAction.Desc
};
int totalProductSales = 0;
DataTable productSales = SalesHelper.GetProductSales(productSale, out totalProductSales);
this.grdProductSaleStatistics.DataSource = productSales;
this.grdProductSaleStatistics.DataBind();
this.pager.TotalRecords = totalProductSales;
}
private void BindProductSaleStatistics()
{
SaleStatisticsQuery query = new SaleStatisticsQuery {
PageSize = this.pager.PageSize,
PageIndex = this.pager.PageIndex,
SortBy = "BuyPercentage",
SortOrder = SortAction.Desc
};
int totalProductSales = 0;
DataTable productVisitAndBuyStatistics = SalesHelper.GetProductVisitAndBuyStatistics(query, out totalProductSales);
this.grdProductSaleStatistics.DataSource = productVisitAndBuyStatistics;
this.grdProductSaleStatistics.DataBind();
this.pager.TotalRecords = totalProductSales;
this.hidPageSize.Value = this.pager.PageSize.ToString();
this.hidPageIndex.Value = this.pager.PageIndex.ToString();
}
private void BindDistributorRanking()
{
SaleStatisticsQuery query = new SaleStatisticsQuery();
query.StartDate = dateStart;
query.EndDate = dateEnd;
query.PageSize = pager.PageSize;
query.PageIndex = pager.PageIndex;
query.SortBy = "SaleTotals";
query.SortOrder = SortAction.Desc;
int totalDistributors = 0;
OrderStatisticsInfo distributorStatistics = DistributorHelper.GetDistributorStatistics(query, out totalDistributors);
grdDistributorStatistics.DataSource = distributorStatistics.OrderTbl;
grdDistributorStatistics.DataBind();
lblTotal.Money = distributorStatistics.TotalOfSearch;
lblProfitTotal.Money = distributorStatistics.ProfitsOfSearch;
pager.TotalRecords = totalDistributors;
}
private void BindUnderlingRanking()
{
SaleStatisticsQuery query = new SaleStatisticsQuery();
query.StartDate = dateStart;
query.EndDate = dateEnd;
query.PageSize = pager.PageSize;
query.PageIndex = pager.PageIndex;
query.SortBy = sortBy;
query.SortOrder = SortAction.Desc;
int total = 0;
DataTable underlingStatistics = UnderlingHelper.GetUnderlingStatistics(query, out total);
grdProductSaleStatistics.DataSource = underlingStatistics;
grdProductSaleStatistics.DataBind();
calendarStartDate.SelectedDate = query.StartDate;
calendarEndDate.SelectedDate = query.EndDate;
pager.TotalRecords = total;
pager1.TotalRecords = total;
}
private void btnCreateReport_Click(object sender, EventArgs e)
{
SaleStatisticsQuery query = new SaleStatisticsQuery();
query.StartDate = dateStart;
query.EndDate = dateEnd;
query.PageSize = pager.PageSize;
query.SortBy = "SaleTotals";
query.SortOrder = SortAction.Desc;
DataTable orderTbl = DistributorHelper.GetDistributorStatisticsNoPage(query).OrderTbl;
string s = string.Empty + "排行,分销商名称,交易量,交易金额,利润\r\n";
foreach (DataRow row in orderTbl.Rows)
{
if (Convert.ToDecimal(row["SaleTotals"]) > 0M)
{
s = s + row["IndexId"].ToString();
}
else
{
s = s ?? "";
}
s = s + "," + row["UserName"].ToString();
s = s + "," + row["PurchaseOrderCount"].ToString();
s = s + "," + row["SaleTotals"].ToString();
s = s + "," + row["Profits"].ToString();
s = s + "\r\n";
}
Page.Response.Clear();
Page.Response.Buffer = false;
Page.Response.Charset = "GB2312";
Page.Response.AppendHeader("Content-Disposition", "attachment;filename=DistributorRanking.CSV");
Page.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Page.Response.ContentType = "application/octet-stream";
Page.EnableViewState = false;
Page.Response.Write(s);
Page.Response.End();
}
public static OrderStatisticsInfo GetDistributorStatisticsNoPage(SaleStatisticsQuery query)
{
return DistributorProvider.Instance().GetDistributorStatisticsNoPage(query);
}
public static DataTable GetUnderlingStatisticsNoPage(SaleStatisticsQuery query)
{
return UnderlingProvider.Instance().GetUnderlingStatisticsNoPage(query);
}
static string BuildMemberStatisticsQuery(SaleStatisticsQuery query)
{
if (null == query)
{
throw new ArgumentNullException("query");
}
StringBuilder builder = new StringBuilder();
builder.Append("SELECT UserId, UserName ");
if (query.StartDate.HasValue || query.EndDate.HasValue)
{
builder.AppendFormat(", ( select isnull(SUM(OrderTotal),0) from Hishop_Orders where OrderStatus != {0} AND OrderStatus != {1}", 1, 4);
if (query.StartDate.HasValue)
{
builder.AppendFormat(" and OrderDate>='{0}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
if (query.EndDate.HasValue)
{
builder.AppendFormat(" and OrderDate<='{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
builder.Append(" and userId = vw_aspnet_Members.UserId) as SaleTotals");
builder.AppendFormat(",(select Count(OrderId) from Hishop_Orders where OrderStatus != {0} AND OrderStatus != {1}", 1, 4);
if (query.StartDate.HasValue)
{
builder.AppendFormat(" and OrderDate>='{0}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
if (query.EndDate.HasValue)
{
builder.AppendFormat(" and OrderDate<='{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
builder.Append(" and userId = vw_aspnet_Members.UserId) as OrderCount ");
}
else
{
builder.Append(",ISNULL(Expenditure,0) as SaleTotals,ISNULL(OrderNumber,0) as OrderCount ");
}
builder.Append(" from vw_aspnet_Members where Expenditure > 0");
if (query.StartDate.HasValue || query.EndDate.HasValue)
{
}
if (!string.IsNullOrEmpty(query.SortBy))
{
builder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), query.SortOrder.ToString());
}
return builder.ToString();
}
public static DbQueryResult GetSaleOrderLineItemsStatisticsNoPage(SaleStatisticsQuery query)
{
return SalesProvider.Instance().GetSaleOrderLineItemsStatisticsNoPage(query);
}
public static DataTable GetUnderlingStatistics(SaleStatisticsQuery query, out int total)
{
return UnderlingProvider.Instance().GetUnderlingStatistics(query, out total);
}
public static DataTable GetProductSalesNoPage(SaleStatisticsQuery productSale, out int totalProductSales)
{
if (productSale == null)
{
totalProductSales = 0;
return null;
}
return SalesProvider.Instance().GetProductSalesNoPage(productSale, out totalProductSales);
}
public static DataTable GetProductVisitAndBuyStatisticsNoPage(SaleStatisticsQuery query, out int totalProductSales)
{
return SalesProvider.Instance().GetProductVisitAndBuyStatisticsNoPage(query, out totalProductSales);
}
public static DataTable GetMemberStatistics(SaleStatisticsQuery query, out int totalProductSales)
{
return SalesProvider.Instance().GetMemberStatistics(query, out totalProductSales);
}
public static DataTable GetMemberStatisticsNoPage(SaleStatisticsQuery query)
{
return SalesProvider.Instance().GetMemberStatisticsNoPage(query);
}
static string BuildProductSaleQuery(SaleStatisticsQuery query)
{
if (null == query)
{
throw new ArgumentNullException("query");
}
StringBuilder builder = new StringBuilder();
builder.Append("SELECT ProductId, SUM(o.Quantity) AS ProductSaleCounts, SUM(o.ItemAdjustedPrice * o.Quantity) AS ProductSaleTotals,");
builder.Append(" (SUM(o.ItemAdjustedPrice * o.Quantity) - SUM(o.CostPrice * o.ShipmentQuantity) )AS ProductProfitsTotals ");
builder.AppendFormat(" FROM Hishop_OrderItems o WHERE 0=0 ", new object[0]);
builder.AppendFormat(" AND OrderId IN (SELECT OrderId FROM Hishop_Orders WHERE OrderStatus != {0} AND OrderStatus != {1})", 1, 4);
if (query.StartDate.HasValue)
{
builder.AppendFormat(" AND OrderId IN (SELECT OrderId FROM Hishop_Orders WHERE OrderDate >= '{0}')", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
if (query.EndDate.HasValue)
{
builder.AppendFormat(" AND OrderId IN (SELECT OrderId FROM Hishop_Orders WHERE OrderDate <= '{0}')", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
builder.Append(" GROUP BY ProductId HAVING ProductId IN");
builder.Append(" (SELECT ProductId FROM Hishop_Products)");
if (!string.IsNullOrEmpty(query.SortBy))
{
builder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), query.SortOrder.ToString());
}
return builder.ToString();
}
public override DbQueryResult GetSaleOrderLineItemsStatisticsNoPage(SaleStatisticsQuery query)
{
DbQueryResult result = new DbQueryResult();
DbCommand sqlStringCommand = database.GetSqlStringCommand("SELECT * FROM vw_Hishop_SaleDetails WHERE 1=1");
if (query.StartDate.HasValue)
{
sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format(" AND OrderDate >= '{0}'", query.StartDate);
}
if (query.EndDate.HasValue)
{
sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format(" AND OrderDate <= '{0}'", query.EndDate);
}
sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format("AND OrderStatus != {0} AND OrderStatus != {1}", 1, 4);
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
}
return result;
}
public static OrderStatisticsInfo GetDistributorStatistics(SaleStatisticsQuery query, out int totalDistributors)
{
return DistributorProvider.Instance().GetDistributorStatistics(query, out totalDistributors);
}
public static DataTable GetDistributionProductSales(SaleStatisticsQuery productSale, out int totalProductSales)
{
if (productSale == null)
{
totalProductSales = 0;
return null;
}
return DistributorProvider.Instance().GetDistributionProductSales(productSale, out totalProductSales);
}
static string BuildProductVisitAndBuyStatisticsQuery(SaleStatisticsQuery query)
{
if (null == query)
{
throw new ArgumentNullException("query");
}
StringBuilder builder = new StringBuilder();
builder.Append("SELECT ProductId,(SaleCounts*100/(case when VistiCounts=0 then 1 else VistiCounts end)) as BuyPercentage");
builder.Append(" FROM Hishop_products where SaleCounts>0");
if (!string.IsNullOrEmpty(query.SortBy))
{
builder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), query.SortOrder.ToString());
}
return builder.ToString();
}