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