private void btnCreateReport_Click(object sender, EventArgs e)
{
SaleStatisticsQuery productSale = new SaleStatisticsQuery();
productSale.StartDate = this.dateStart;
productSale.EndDate = this.dateEnd;
productSale.PageSize = this.pager.PageSize;
productSale.SortBy = "ProductSaleCounts";
productSale.SortOrder = SortAction.Desc;
int totalProductSales = 0;
DataTable distributionProductSalesNoPage = DistributorHelper.GetDistributionProductSalesNoPage(productSale, out totalProductSales);
string s = ((string.Empty + "排行" + ",商品名称") + ",商家编码" + ",销售量") + ",销售额" + ",利润\r\n";
foreach (DataRow row in distributionProductSalesNoPage.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";
}
this.Page.Response.Clear();
this.Page.Response.Buffer = false;
this.Page.Response.Charset = "GB2312";
this.Page.Response.AppendHeader("Content-Disposition", "attachment;filename=ProductSaleRanking.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 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} AND OrderStatus != {2})", 1, 4, 9);
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());
}
private void btnCreateReport_Click(object sender, EventArgs e)
{
SaleStatisticsQuery query = new SaleStatisticsQuery();
query.StartDate = dateStart;
query.EndDate = dateEnd;
query.SortBy = sortBy;
query.SortOrder = SortAction.Desc;
DataTable underlingStatisticsNoPage = UnderlingHelper.GetUnderlingStatisticsNoPage(query);
string s = string.Empty + "会员,订单数,消费金额\r\n";
foreach (DataRow row in underlingStatisticsNoPage.Rows)
{
s = s + row["UserName"].ToString();
s = s + "," + row["OrderCount"].ToString();
s = s + "," + row["SaleTotals"].ToString() + "\r\n";
}
Page.Response.Clear();
Page.Response.Buffer = false;
Page.Response.Charset = "GB2312";
Page.Response.AppendHeader("Content-Disposition", "attachment;filename=MemberRanking.csv");
Page.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Page.Response.ContentType = "application/octet-stream";
Page.EnableViewState = false;
Page.Response.Write(s);
Page.Response.End();
}
private void btnCreateReport_Click(object sender, System.EventArgs e)
{
SaleStatisticsQuery productSale = new SaleStatisticsQuery
{
StartDate = this.dateStart,
EndDate = this.dateEnd,
PageSize = this.pager.PageSize,
SortBy = "ProductSaleCounts",
SortOrder = SortAction.Desc
};
int totalProductSales = 0;
System.Data.DataTable productSalesNoPage = SalesHelper.GetProductSalesNoPage(productSale, out totalProductSales);
string s = string.Empty + "排行,商品名称,商家编码,销售量,销售额,利润\r\n";
foreach (System.Data.DataRow row in productSalesNoPage.Rows)
{
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";
}
this.Page.Response.Clear();
this.Page.Response.Buffer = false;
this.Page.Response.Charset = "GB2312";
this.Page.Response.AppendHeader("Content-Disposition", "attachment;filename=ProductSaleRanking.csv");
this.Page.Response.ContentEncoding = System.Text.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, System.EventArgs e)
{
SaleStatisticsQuery query = new SaleStatisticsQuery
{
StartDate = this.dateStart,
EndDate = this.dateEnd,
SortBy = this.sortBy,
SortOrder = SortAction.Desc
};
System.Data.DataTable memberStatisticsNoPage = SalesHelper.GetMemberStatisticsNoPage(query);
string s = string.Empty + "会员,订单数,消费金额\r\n";
foreach (System.Data.DataRow row in memberStatisticsNoPage.Rows)
{
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 = System.Text.Encoding.GetEncoding("GB2312");
this.Page.Response.ContentType = "application/octet-stream";
this.Page.EnableViewState = false;
this.Page.Response.Write(s);
this.Page.Response.End();
}
public DataTable GetProductVisitAndBuyStatisticsNoPage(SaleStatisticsQuery query, out int totalProductSales)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(" ");
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT ProductName,VistiCounts,SaleCounts as BuyCount ,(SaleCounts/(case when VistiCounts=0 then 1 else VistiCounts end))*100 as BuyPercentage ");
stringBuilder.Append("FROM Ecshop_Products WHERE SaleCounts>0 ORDER BY BuyPercentage DESC;");
stringBuilder.Append("SELECT COUNT(*) as TotalProductSales FROM Ecshop_Products WHERE SaleCounts>0;");
sqlStringCommand.CommandText = stringBuilder.ToString();
DataTable result = null;
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
while (dataReader.Read())
{
result = DataHelper.ConverDataReaderToDataTable(dataReader);
}
if (dataReader.NextResult() && dataReader.Read())
{
totalProductSales = (int)dataReader["TotalProductSales"];
}
else
{
totalProductSales = 0;
}
}
return(result);
}
/// <summary>
/// 导出Excel数据
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable GetProductVisitAndDt(SaleStatisticsQuery query)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(" ");
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("select * from [vw_Ecshop_SaleStoreDetails] where 1=1 ");
if (query.StartDate.HasValue)
{
stringBuilder.AppendFormat(" AND orderDate >= '{0}'", query.StartDate.Value);
}
if (query.EndDate.HasValue)
{
stringBuilder.AppendFormat(" AND orderDate <= '{0}'", query.EndDate.Value.AddDays(1.0));
}
if (!string.IsNullOrEmpty(query.ProductName))
{
stringBuilder.AppendFormat(" AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(query.ProductName));
}
if (query.SupplierId.HasValue && query.SupplierId != 0)
{
stringBuilder.AppendFormat(" AND SupplierId = {0}", query.SupplierId);
}
sqlStringCommand.CommandText = stringBuilder.ToString();
DataTable result = null;
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
while (dataReader.Read())
{
result = DataHelper.ConverDataReaderToDataTable(dataReader);
}
}
return(result);
}
public override DataTable GetUnderlingStatisticsNoPage(SaleStatisticsQuery query)
{
DbCommand sqlStringCommand = database.GetSqlStringCommand(BuildUnderlingStatisticsQuery(query));
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
return(DataHelper.ConverDataReaderToDataTable(reader));
}
}
public static DataTable GetDistributionProductSales(SaleStatisticsQuery productSale, out int totalProductSales)
{
if (productSale == null)
{
totalProductSales = 0;
return(null);
}
return(DistributorProvider.Instance().GetDistributionProductSales(productSale, out totalProductSales));
}
public static DataTable GetProductSalesNoPage(SaleStatisticsQuery productSale, out int totalProductSales)
{
if (productSale == null)
{
totalProductSales = 0;
return(null);
}
return(SalesProvider.Instance().GetProductSalesNoPage(productSale, out totalProductSales));
}
public DataTable GetMemberStatisticsNoPage(SaleStatisticsQuery query)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(BuildMemberStatisticsQuery(query));
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(DataHelper.ConverDataReaderToDataTable(reader));
}
}
public static DataTable GetProductSales(SaleStatisticsQuery productSale, out int totalProductSales)
{
if (productSale == null)
{
totalProductSales = 0;
return(null);
}
return(new SaleStatisticDao().GetProductSales(productSale, out totalProductSales));
}
public System.Data.DataTable GetMemberStatisticsNoPage(SaleStatisticsQuery query)
{
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(SaleStatisticDao.BuildMemberStatisticsQuery(query));
System.Data.DataTable result = null;
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = DataHelper.ConverDataReaderToDataTable(dataReader);
}
return(result);
}
private void btnCreateReport_Click(object sender, System.EventArgs e)
{
SaleStatisticsQuery saleStatisticsQuery = new SaleStatisticsQuery();
saleStatisticsQuery.StartDate = this.dateStart;
saleStatisticsQuery.EndDate = this.dateEnd;
saleStatisticsQuery.PageSize = this.pager.PageSize;
saleStatisticsQuery.SortBy = "ProductSaleCounts";
saleStatisticsQuery.SortOrder = SortAction.Desc;
saleStatisticsQuery.SupplierId = this.ddlSupplier.SelectedValue.HasValue ? this.ddlSupplier.SelectedValue : 0;
int num = 0;
System.Data.DataTable productSalesNoPage = SalesHelper.GetProductSalesNoPage(saleStatisticsQuery, out num);
string text = string.Empty;
text += "排行";
text += ",商品名称";
text += ",英文名称";
text += ",供应商";
text += ",商家编码";
text += ",销售量";
text += ",销售额";
text += ",利润\r\n";
foreach (System.Data.DataRow dataRow in productSalesNoPage.Rows)
{
text += dataRow["IDOfSaleTotals"].ToString();
text = text + "," + dataRow["ProductName"].ToString();
text = text + "," + dataRow["EnglishName"].ToString();
text = text + "," + dataRow["SupplierName"].ToString();
text = text + "," + dataRow["SKU"].ToString();
text = text + "," + dataRow["ProductSaleCounts"].ToString();
text = text + "," + dataRow["ProductSaleTotals"].ToString();
text = text + "," + dataRow["ProductProfitsTotals"].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=ProductSaleRanking.csv");
if (!(this.calendarStartDate.SelectedDate.HasValue && this.calendarEndDate.SelectedDate.HasValue))
{
this.Page.Response.AppendHeader("Content-Disposition", "attachment;filename=商品销售排行.csv");
}
else
{
this.Page.Response.AppendHeader("Content-Disposition", "attachment;filename=商品销售排行_" + this.dateStart.Value.ToString("yyyyMMdd") + "-" + this.dateEnd.Value.ToString("yyyyMMdd") + ".csv");
}
this.Page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
this.Page.Response.ContentType = "application/octet-stream";
this.Page.EnableViewState = false;
this.Page.Response.Write(text);
this.Page.Response.End();
}
public System.Data.DataTable GetProductSalesNoPage(SaleStatisticsQuery productSale, out int totalProductSales)
{
System.Data.Common.DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_ProductSalesNoPage_Get");
this.database.AddInParameter(storedProcCommand, "sqlPopulate", System.Data.DbType.String, SaleStatisticDao.BuildProductSaleQuery(productSale));
this.database.AddOutParameter(storedProcCommand, "TotalProductSales", System.Data.DbType.Int32, 4);
System.Data.DataTable result = null;
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(storedProcCommand))
{
result = DataHelper.ConverDataReaderToDataTable(dataReader);
}
totalProductSales = (int)this.database.GetParameterValue(storedProcCommand, "TotalProductSales");
return(result);
}
private static string BuildProductSaleQuery(SaleStatisticsQuery query)
{
if (null == query)
{
throw new ArgumentNullException("query");
}
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT ProductId, SUM(o.Quantity) AS ProductSaleCounts, SUM(o.ItemAdjustedPrice * o.Quantity) AS ProductSaleTotals,");
stringBuilder.Append(" (SUM(o.ItemAdjustedPrice * o.Quantity) - SUM(o.CostPrice * o.ShipmentQuantity) )AS ProductProfitsTotals,isnull(s.SupplierName,'') SupplierName");
stringBuilder.AppendFormat(" FROM Ecshop_OrderItems o left join Ecshop_Supplier s on s.SupplierId = o.SupplierId WHERE 0=0 ", new object[0]);
//stringBuilder.AppendFormat(" AND o.OrderId IN (SELECT OrderId FROM Ecshop_Orders WHERE OrderStatus != {0} AND OrderStatus != {1} AND OrderStatus != {2})", 1, 4, 9);
stringBuilder.AppendFormat(" AND o.OrderId IN (SELECT OrderId FROM Ecshop_Orders WHERE OrderStatus not in ({0}) )", "1, 4, 9,98,99");
if (query.StartDate.HasValue)
{
stringBuilder.AppendFormat(" AND o.OrderId IN (SELECT OrderId FROM Ecshop_Orders WHERE OrderDate >= '{0}')", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
if (query.EndDate.HasValue)
{
stringBuilder.AppendFormat(" AND o.OrderId IN (SELECT OrderId FROM Ecshop_Orders WHERE OrderDate <= '{0}')", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
if (query.SupplierId.HasValue && query.SupplierId.Value != 0)
{
stringBuilder.AppendFormat(" AND o.SupplierId = {0}", query.SupplierId.Value);
}
stringBuilder.Append(" GROUP BY ProductId,s.SupplierName HAVING ProductId IN");
stringBuilder.Append(" (SELECT ProductId FROM Ecshop_Products ");
if (query.ProductName != "")
{
stringBuilder.Append(" where ProductName like '%" + query.ProductName + "%' ");
}
if (query.ProductCode != "")
{
if (query.ProductName != "")
{
stringBuilder.Append(" and ProductCode like '%" + query.ProductCode + "%' ");
}
else
{
stringBuilder.Append(" where ProductCode like '%" + query.ProductCode + "%' ");
}
}
stringBuilder.Append(" ) ");
if (!string.IsNullOrEmpty(query.SortBy))
{
stringBuilder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), query.SortOrder.ToString());
}
return(stringBuilder.ToString());
}
private void BindList()
{
SaleStatisticsQuery query = new SaleStatisticsQuery();
query.PageIndex = this.pager.PageIndex;
query.StartDate = this.startTime;
query.EndDate = this.endTime;
DbQueryResult saleOrderLineItemsStatistics = SubsiteSalesHelper.GetSaleOrderLineItemsStatistics(query);
this.grdOrderLineItem.DataSource = saleOrderLineItemsStatistics.Data;
this.grdOrderLineItem.DataBind();
this.pager.TotalRecords = saleOrderLineItemsStatistics.TotalRecords;
this.grdOrderLineItem.PageSize = query.PageSize;
}
public static System.Data.DataTable GetProductSalesNoPage(SaleStatisticsQuery productSale, out int totalProductSales)
{
System.Data.DataTable result;
if (productSale == null)
{
totalProductSales = 0;
result = null;
}
else
{
result = new SaleStatisticDao().GetProductSalesNoPage(productSale, out totalProductSales);
}
return(result);
}
public static System.Data.DataTable GetProductSales(SaleStatisticsQuery productSale, out int totalProductSales)
{
System.Data.DataTable result;
if (productSale == null)
{
totalProductSales = 0;
result = null;
}
else
{
result = SalesProvider.Instance().GetProductSales(productSale, out totalProductSales);
}
return(result);
}
private void btnCreateReport_Click(object sender, System.EventArgs e)
{
ManagerHelper.CheckPrivilege(Privilege.ProductSaleAsImportSourceExcel);
SaleStatisticsQuery saleStatisticsQuery = new SaleStatisticsQuery();
saleStatisticsQuery.StartDate = this.dateStart;
saleStatisticsQuery.EndDate = this.dateEnd;
saleStatisticsQuery.PageSize = this.pager.PageSize;
saleStatisticsQuery.SortBy = "b.ImportSourceId";
saleStatisticsQuery.SortOrder = SortAction.Asc;
int num = 0;
System.Data.DataTable productSalesNoPage = SalesHelper.GetProductSaleAsImportSourceNoPage(saleStatisticsQuery, out num);
string text = string.Empty;
text += "原产地";
//text += ",排行";
text += ",商品名称";
text += ",商家编码";
text += ",销售量";
text += ",销售额";
text += ",利润\r\n";
foreach (System.Data.DataRow dataRow in productSalesNoPage.Rows)
{
text += dataRow["CnArea"].ToString();
//text = text + "," + dataRow["IDOfSaleTotals"].ToString();
text = text + "," + dataRow["ProductName"].ToString();
text = text + "," + dataRow["SKU"].ToString();
text = text + "," + dataRow["ProductSaleCounts"].ToString();
text = text + "," + dataRow["ProductSaleTotals"].ToString();
text = text + "," + dataRow["ProductProfitsTotals"].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=ProductSaleAsImportSource.csv");
if (!(this.calendarStartDate.SelectedDate.HasValue && this.calendarEndDate.SelectedDate.HasValue))
{
this.Page.Response.AppendHeader("Content-Disposition", "attachment;filename=按产地汇总商品销售.csv");
}
else
{
this.Page.Response.AppendHeader("Content-Disposition", "attachment;filename=按产地汇总商品销售_" + this.dateStart.Value.ToString("yyyyMMdd") + "-" + this.dateEnd.Value.ToString("yyyyMMdd") + ".csv");
}
this.Page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
this.Page.Response.ContentType = "application/octet-stream";
this.Page.EnableViewState = false;
this.Page.Response.Write(text);
this.Page.Response.End();
}
private void BindProductSaleStatistics()
{
SaleStatisticsQuery saleStatisticsQuery = new SaleStatisticsQuery();
saleStatisticsQuery.PageSize = this.pager.PageSize;
saleStatisticsQuery.PageIndex = this.pager.PageIndex;
saleStatisticsQuery.SortBy = "BuyPercentage";
saleStatisticsQuery.SortOrder = SortAction.Desc;
int totalRecords = 0;
System.Data.DataTable productVisitAndBuyStatistics = SubsiteSalesHelper.GetProductVisitAndBuyStatistics(saleStatisticsQuery, out totalRecords);
this.grdProductSaleStatistics.DataSource = productVisitAndBuyStatistics;
this.grdProductSaleStatistics.DataBind();
this.pager.TotalRecords = totalRecords;
}
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 static DataTable GetProductSalesNoPage(SaleStatisticsQuery productSale, out int totalProductSales)
{
DataTable productSalesNoPage;
if (productSale != null)
{
productSalesNoPage = (new SaleStatisticDao()).GetProductSalesNoPage(productSale, out totalProductSales);
}
else
{
totalProductSales = 0;
productSalesNoPage = null;
}
return(productSalesNoPage);
}
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);
}
public System.Data.DataTable GetMemberStatistics(SaleStatisticsQuery query, out int totalProductSales)
{
System.Data.Common.DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_MemberStatistics_Get");
this.database.AddInParameter(storedProcCommand, "PageIndex", System.Data.DbType.Int32, query.PageIndex);
this.database.AddInParameter(storedProcCommand, "PageSize", System.Data.DbType.Int32, query.PageSize);
this.database.AddInParameter(storedProcCommand, "IsCount", System.Data.DbType.Boolean, query.IsCount);
this.database.AddInParameter(storedProcCommand, "sqlPopulate", System.Data.DbType.String, SaleStatisticDao.BuildMemberStatisticsQuery(query));
this.database.AddOutParameter(storedProcCommand, "TotalProductSales", System.Data.DbType.Int32, 4);
System.Data.DataTable result = null;
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(storedProcCommand))
{
result = DataHelper.ConverDataReaderToDataTable(dataReader);
}
totalProductSales = (int)this.database.GetParameterValue(storedProcCommand, "TotalProductSales");
return(result);
}
private static string BuildProductVisitAndBuyStatisticsQuery(SaleStatisticsQuery query)
{
if (null == query)
{
throw new ArgumentNullException("query");
}
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT ProductId,(SaleCounts*100/(case when VistiCounts=0 then 1 else VistiCounts end)) as BuyPercentage");
stringBuilder.Append(" FROM Ecshop_products where SaleCounts>0");
if (!string.IsNullOrEmpty(query.SortBy))
{
stringBuilder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), query.SortOrder.ToString());
}
return(stringBuilder.ToString());
}
private static string BuildMemberStatisticsQuery(SaleStatisticsQuery query)
{
if (query == null)
{
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} AND OrderStatus != {2}", 1, 4, 9);
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 = aspnet_Members.UserId) as SaleTotals");
builder.AppendFormat(",(select Count(OrderId) from Hishop_Orders where OrderStatus != {0} AND OrderStatus != {1} AND OrderStatus != {2}", 1, 4, 9);
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 = aspnet_Members.UserId) as OrderCount ");
}
else
{
builder.Append(",ISNULL(Expenditure,0) as SaleTotals,ISNULL(OrderNumber,0) as OrderCount ");
}
builder.Append(" from aspnet_Members where Expenditure > 0");
if (!query.StartDate.HasValue)
{
bool 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());
}
private void BindMemberRanking()
{
SaleStatisticsQuery query = new SaleStatisticsQuery();
query.StartDate = this.dateStart;
query.EndDate = this.dateEnd;
query.PageSize = this.pager.PageSize;
query.PageIndex = this.pager.PageIndex;
query.SortBy = this.sortBy;
query.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();
productSale.StartDate = this.dateStart;
productSale.EndDate = this.dateEnd;
productSale.PageSize = this.pager.PageSize;
productSale.PageIndex = this.pager.PageIndex;
productSale.SortBy = "ProductSaleCounts";
productSale.SortOrder = SortAction.Desc;
int totalProductSales = 0;
DataTable distributionProductSales = DistributorHelper.GetDistributionProductSales(productSale, out totalProductSales);
this.grdProductSaleStatistics.DataSource = distributionProductSales;
this.grdProductSaleStatistics.DataBind();
this.pager.TotalRecords = totalProductSales;
}
private void BindProductSaleRanking()
{
SaleStatisticsQuery saleStatisticsQuery = new SaleStatisticsQuery();
saleStatisticsQuery.StartDate = this.dateStart;
saleStatisticsQuery.EndDate = this.dateEnd;
saleStatisticsQuery.PageSize = this.pager.PageSize;
saleStatisticsQuery.PageIndex = this.pager.PageIndex;
saleStatisticsQuery.SortBy = "ProductSaleCounts";
saleStatisticsQuery.SortOrder = SortAction.Desc;
int totalRecords = 0;
System.Data.DataTable productSales = SalesHelper.GetProductSales(saleStatisticsQuery, out totalRecords);
this.grdProductSaleStatistics.DataSource = productSales;
this.grdProductSaleStatistics.DataBind();
this.pager.TotalRecords = totalRecords;
}
