Пример #1
0
 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();
 }
Пример #2
0
 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();
 }
Пример #3
0
 public DataTable GetMemberStatisticsNoPage(SaleStatisticsQuery query)
 {
     DbCommand sqlStringCommand = this.database.GetSqlStringCommand(BuildMemberStatisticsQuery(query));
     using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
     {
         return DataHelper.ConverDataReaderToDataTable(reader);
     }
 }
Пример #4
0
 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;
 }
Пример #5
0
 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;
 }
Пример #6
0
 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;
 }
Пример #7
0
 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;
 }
Пример #8
0
 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;
 }
Пример #9
0
 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;
 }
Пример #10
0
 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;
 }
Пример #11
0
 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;
 }
Пример #12
0
 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;
 }
Пример #13
0
 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;
 }
Пример #15
0
 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();
 }
Пример #17
0
 public static OrderStatisticsInfo GetDistributorStatisticsNoPage(SaleStatisticsQuery query)
 {
     return DistributorProvider.Instance().GetDistributorStatisticsNoPage(query);
 }
Пример #18
0
 public static DataTable GetUnderlingStatisticsNoPage(SaleStatisticsQuery query)
 {
     return UnderlingProvider.Instance().GetUnderlingStatisticsNoPage(query);
 }
Пример #19
0
        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();
        }
Пример #20
0
 public static DbQueryResult GetSaleOrderLineItemsStatisticsNoPage(SaleStatisticsQuery query)
 {
     return SalesProvider.Instance().GetSaleOrderLineItemsStatisticsNoPage(query);
 }
Пример #21
0
 public static DataTable GetUnderlingStatistics(SaleStatisticsQuery query, out int total)
 {
     return UnderlingProvider.Instance().GetUnderlingStatistics(query, out total);
 }
Пример #22
0
 public static DataTable GetProductSalesNoPage(SaleStatisticsQuery productSale, out int totalProductSales)
 {
     if (productSale == null)
     {
         totalProductSales = 0;
         return null;
     }
     return SalesProvider.Instance().GetProductSalesNoPage(productSale, out totalProductSales);
 }
Пример #23
0
 public static DataTable GetProductVisitAndBuyStatisticsNoPage(SaleStatisticsQuery query, out int totalProductSales)
 {
     return SalesProvider.Instance().GetProductVisitAndBuyStatisticsNoPage(query, out totalProductSales);
 }
Пример #24
0
 public static DataTable GetMemberStatistics(SaleStatisticsQuery query, out int totalProductSales)
 {
     return SalesProvider.Instance().GetMemberStatistics(query, out totalProductSales);
 }
Пример #25
0
 public static DataTable GetMemberStatisticsNoPage(SaleStatisticsQuery query)
 {
     return SalesProvider.Instance().GetMemberStatisticsNoPage(query);
 }
Пример #26
0
 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();
 }
Пример #27
0
 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;
 }
Пример #28
0
 public static OrderStatisticsInfo GetDistributorStatistics(SaleStatisticsQuery query, out int totalDistributors)
 {
     return DistributorProvider.Instance().GetDistributorStatistics(query, out totalDistributors);
 }
Пример #29
0
 public static DataTable GetDistributionProductSales(SaleStatisticsQuery productSale, out int totalProductSales)
 {
     if (productSale == null)
     {
         totalProductSales = 0;
         return null;
     }
     return DistributorProvider.Instance().GetDistributionProductSales(productSale, out totalProductSales);
 }
Пример #30
0
 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();
 }