public IList <ProductCategoryStatisticsInfo> GetProductCategoryStatisticsData(ProductStatisticsQuery query) { IList <ProductCategoryStatisticsInfo> result = new List <ProductCategoryStatisticsInfo>(); string arg = " 1 = 1 "; string format = "SELECT CategoryId,Name AS CategoryName,ISNULL((SELECT SUM(SaleQuantity) FROM Hishop_ProductDailyAccessStatistics WHERE ProductId IN(SELECT ProductId FROM Hishop_Products WHERE (MainCategoryPath LIKE CONVERT(varchar,c.CategoryId)+'|%') OR CategoryId = c.CategoryId) AND {0}),0) AS SaleCounts,ISNULL((SELECT SUM(SaleAmount) FROM Hishop_ProductDailyAccessStatistics WHERE ProductId IN(SELECT ProductId FROM Hishop_Products WHERE (MainCategoryPath LIKE CONVERT(varchar,c.CategoryId)+'|%') OR CategoryId = c.CategoryId) AND {0}),0) AS SaleAmounts FROM Hishop_Categories c WHERE Depth = 1"; DateTime dateTime; switch (query.LastConsumeTime) { case EnumConsumeTime.yesterday: dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); arg = string.Format(" DATEDIFF(dd,StatisticalDate,'{0}') = 0 ", dateTime.ToString("yyyy-MM-dd")); break; case EnumConsumeTime.inOneWeek: { dateTime = DateTime.Now; dateTime = dateTime.AddDays(-7.0); string arg3 = dateTime.ToString("yyyy-MM-dd"); dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); dateTime = dateTime.Date; arg = string.Format(" (StatisticalDate BETWEEN '{0}' AND '{1}')", arg3, dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.inTwoWeek: { dateTime = DateTime.Now; dateTime = dateTime.AddDays(-14.0); string arg4 = dateTime.ToString("yyyy-MM-dd"); dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); dateTime = dateTime.Date; arg = string.Format(" (StatisticalDate BETWEEN '{0}' AND '{1}')", arg4, dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.inOneMonth: { dateTime = DateTime.Now; dateTime = dateTime.AddMonths(-1); string arg5 = dateTime.ToString("yyyy-MM-dd"); dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); dateTime = dateTime.Date; arg = string.Format(" (StatisticalDate BETWEEN '{0}' AND '{1}')", arg5, dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.custom: if (query.CustomConsumeStartTime.HasValue && query.CustomConsumeEndTime.HasValue) { dateTime = query.CustomConsumeStartTime.Value; string arg2 = dateTime.ToString("yyyy-MM-dd"); dateTime = query.CustomConsumeEndTime.Value; arg = string.Format(" (StatisticalDate BETWEEN '{0}' AND '{1}')", arg2, dateTime.ToString("yyyy-MM-dd")); } break; } DbCommand sqlStringCommand = base.database.GetSqlStringCommand(string.Format(format, arg)); using (IDataReader objReader = base.database.ExecuteReader(sqlStringCommand)) { result = DataHelper.ReaderToList <ProductCategoryStatisticsInfo>(objReader); } return(result); }
public void ExportToExcle(object sender, EventArgs e) { string text = "ProductReport.xls"; int num = this.hidLastConsumeTime.Value.ToInt(0); if (!Enum.IsDefined(typeof(EnumConsumeTime), num)) { num = 1; } DateTime dateTime = default(DateTime); DateTime dateTime2; if (!DateTime.TryParse(this.hidStartDate.Value, out dateTime)) { dateTime2 = DateTime.Now; dateTime = dateTime2.AddDays(-1.0); } DateTime dateTime3 = default(DateTime); if (!DateTime.TryParse(this.hidEndDate.Value, out dateTime3)) { dateTime2 = DateTime.Now; dateTime3 = dateTime2.AddDays(-1.0); } EnumConsumeTime lastConsumeTime; switch (num) { case 8: if (dateTime3 < dateTime) { this.ShowMsg("错误的时间范围", false); return; } lastConsumeTime = EnumConsumeTime.custom; text = "ProductReport" + dateTime.ToString("yyyyMMdd") + "-" + dateTime3.ToString("yyyyMMdd") + ".xls"; break; case 4: { dateTime2 = DateTime.Now; dateTime2 = dateTime2.Date; dateTime3 = dateTime2.AddDays(-1.0); dateTime = dateTime3.AddDays(-30.0); lastConsumeTime = EnumConsumeTime.inOneMonth; string[] obj2 = new string[5] { "ProductReport", dateTime.ToString("yyyyMMdd"), "-", null, null }; dateTime2 = dateTime3.AddDays(-1.0); obj2[3] = dateTime2.ToString("yyyyMMdd"); obj2[4] = ".xls"; text = string.Concat(obj2); break; } case 2: { dateTime2 = DateTime.Now; dateTime2 = dateTime2.Date; dateTime3 = dateTime2.AddDays(-1.0); dateTime = dateTime3.AddDays(-7.0); lastConsumeTime = EnumConsumeTime.inOneWeek; string[] obj = new string[5] { "ProductReport", dateTime.ToString("yyyyMMdd"), "-", null, null }; dateTime2 = dateTime3.AddDays(-1.0); obj[3] = dateTime2.ToString("yyyyMMdd"); obj[4] = ".xls"; text = string.Concat(obj); break; } default: dateTime2 = DateTime.Now; dateTime2 = dateTime2.Date; dateTime3 = dateTime2.AddDays(-1.0); dateTime2 = DateTime.Now; dateTime2 = dateTime2.Date; dateTime3 = dateTime2.AddDays(-1.0); lastConsumeTime = EnumConsumeTime.yesterday; text = "ProductReport" + dateTime.ToString("yyyyMMdd") + ".xls"; break; } ProductStatisticsQuery productStatisticsQuery = new ProductStatisticsQuery(); productStatisticsQuery.CustomConsumeEndTime = dateTime3; productStatisticsQuery.CustomConsumeStartTime = dateTime; productStatisticsQuery.LastConsumeTime = lastConsumeTime; string a = this.hidOrderby.Value.ToNullString().ToLower(); string text2 = this.hidOrderAction.Value.ToNullString().ToLower(); if (text2 != "pv" && text2 != "uv" && text2 != "paymentnum" && text2 != "salequantity" && text2 != "saleamount" && text2 != "productconversionrate") { text2 = "pv"; } productStatisticsQuery.SortBy = text2; productStatisticsQuery.SortOrder = SortAction.Desc; if (a == "asc") { productStatisticsQuery.SortOrder = SortAction.Asc; } IList <ProductStatisticsInfo> productStatisticsDataNoPage = ProductStatisticsHelper.GetProductStatisticsDataNoPage(productStatisticsQuery); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">"); stringBuilder.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;\">"); stringBuilder.AppendLine("<td>商品名称</td>"); stringBuilder.AppendLine("<td>浏览量</td>"); stringBuilder.AppendLine("<td>浏览人数</td>"); stringBuilder.AppendLine("<td>付款人数</td>"); stringBuilder.AppendLine("<td>单品转化率</td>"); stringBuilder.AppendLine("<td>销售数量</td>"); stringBuilder.AppendLine("<td>销售金额</td>"); stringBuilder.AppendLine("</tr>"); foreach (ProductStatisticsInfo item in productStatisticsDataNoPage) { stringBuilder.AppendLine("<tr>"); stringBuilder.AppendLine("<td>" + item.ProductName + "</td>"); stringBuilder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + item.PV + "</td>"); stringBuilder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + item.UV + "</td>"); stringBuilder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + item.PaymentNum + "</td>"); stringBuilder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + item.ProductConversionRate.F2ToString("f2") + "%</td>"); stringBuilder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + item.SaleQuantity + "</td>"); stringBuilder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + item.SaleAmount.F2ToString("f2") + "</td>"); stringBuilder.AppendLine("</tr>"); } stringBuilder.AppendLine("</table>"); this.Page.Response.Clear(); this.Page.Response.Buffer = false; this.Page.Response.Charset = "UTF-8"; this.Page.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + text) ?? ""); this.Page.Response.ContentEncoding = Encoding.GetEncoding("UTF-8"); this.Page.Response.ContentType = "application/ms-excel"; this.Page.EnableViewState = false; this.Page.Response.Write(stringBuilder.ToString()); this.Page.Response.End(); }
public IList <ProductStatisticsInfo> GetProductStatisticsDataNoPage(ProductStatisticsQuery query) { string format = "SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber, ProductId, (SELECT ProductName FROM Hishop_Products where ProductId = p.ProductId) AS ProductName, SUM(PV) AS PV,SUM(UV) AS UV,SUM(PaymentNum) AS PaymentNum,SUM(SaleQuantity) AS SaleQuantity,SUM(SaleAmount) AS SaleAmount,(CASE WHEN SUM(UV) <=0 THEN 0 ELSE CONVERT(money,SUM(PaymentNum))/SUM(UV)*100.0 END) AS ProductConversionRate FROM Hishop_ProductDailyAccessStatistics p WHERE 1 = 1 AND ({0}) GROUP BY ProductId) AS STable"; StringBuilder stringBuilder = new StringBuilder(" 1 = 1 "); DateTime dateTime; switch (query.LastConsumeTime) { case EnumConsumeTime.yesterday: { StringBuilder stringBuilder6 = stringBuilder; dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); stringBuilder6.AppendFormat(" AND DATEDIFF(dd,StatisticalDate,'{0}') = 0 ", dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.inOneWeek: { StringBuilder stringBuilder3 = stringBuilder; dateTime = DateTime.Now; dateTime = dateTime.AddDays(-7.0); string arg2 = dateTime.ToString("yyyy-MM-dd"); dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); dateTime = dateTime.Date; stringBuilder3.AppendFormat(" AND (StatisticalDate BETWEEN '{0}' AND '{1}')", arg2, dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.inTwoWeek: { StringBuilder stringBuilder4 = stringBuilder; dateTime = DateTime.Now; dateTime = dateTime.AddDays(-14.0); string arg3 = dateTime.ToString("yyyy-MM-dd"); dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); dateTime = dateTime.Date; stringBuilder4.AppendFormat(" AND (StatisticalDate BETWEEN '{0}' AND '{1}')", arg3, dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.inOneMonth: { StringBuilder stringBuilder5 = stringBuilder; dateTime = DateTime.Now; dateTime = dateTime.AddMonths(-1); string arg4 = dateTime.ToString("yyyy-MM-dd"); dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); dateTime = dateTime.Date; stringBuilder5.AppendFormat(" AND (StatisticalDate BETWEEN '{0}' AND '{1}')", arg4, dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.custom: if (query.CustomConsumeStartTime.HasValue && query.CustomConsumeEndTime.HasValue) { StringBuilder stringBuilder2 = stringBuilder; dateTime = query.CustomConsumeStartTime.Value; string arg = dateTime.ToString("yyyy-MM-dd"); dateTime = query.CustomConsumeEndTime.Value; stringBuilder2.AppendFormat(" AND (StatisticalDate BETWEEN '{0}' AND '{1}')", arg, dateTime.ToString("yyyy-MM-dd")); } break; } SortAction sortOrder = query.SortOrder; string arg5 = "pv"; if (!string.IsNullOrEmpty(query.SortBy)) { arg5 = ((!(query.SortBy != "productconversionrate")) ? "(CASE WHEN SUM(UV) <=0 THEN 0 ELSE CONVERT(money,SUM(PaymentNum))/SUM(UV)*100 END)" : ("SUM(" + query.SortBy + ")")); } format = string.Format(format, stringBuilder, arg5, (sortOrder == SortAction.Desc) ? "DESC" : "ASC"); DbCommand sqlStringCommand = base.database.GetSqlStringCommand(format); IList <ProductStatisticsInfo> result = new List <ProductStatisticsInfo>(); using (IDataReader objReader = base.database.ExecuteReader(sqlStringCommand)) { result = DataHelper.ReaderToList <ProductStatisticsInfo>(objReader); } return(result); }
public PageModel <ProductStatisticsInfo> GetProductStatisticsData(ProductStatisticsQuery query) { string format = "SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY {2} {3}) AS RowNumber, p.ProductId, (SELECT ProductName FROM Hishop_Products where ProductId = p.ProductId) AS ProductName, SUM(PV) AS PV,SUM(UV) AS UV,SUM(PaymentNum) AS PaymentNum,SUM(SaleQuantity) AS SaleQuantity,SUM(SaleAmount) AS SaleAmount,(CASE WHEN SUM(UV) <=0 THEN 0 ELSE CONVERT(money,SUM(PaymentNum))/SUM(UV)*100.0 END) AS ProductConversionRate FROM Hishop_ProductDailyAccessStatistics p right join Hishop_Products b on b.ProductId= p.ProductId WHERE 1 = 1 AND ({0}) GROUP BY p.ProductId) AS STable WHERE {1}"; string format2 = "SELECT COUNT(*) AS Total FROM(SELECT ROW_NUMBER() OVER(ORDER BY SUM(PV) DESC) AS RowNumber FROM Hishop_ProductDailyAccessStatistics p right join Hishop_Products b on b.ProductId= p.ProductId WHERE 1 = 1 AND ({0}) GROUP BY p.ProductId) AS STable;"; string text = "RowNumber BETWEEN 1 AND " + query.PageSize; if (query.PageIndex > 1) { text = $"RowNumber BETWEEN {(query.PageIndex - 1) * query.PageSize + 1} AND {query.PageIndex * query.PageSize}"; } StringBuilder stringBuilder = new StringBuilder(" 1 = 1 "); DateTime dateTime; switch (query.LastConsumeTime) { case EnumConsumeTime.yesterday: { StringBuilder stringBuilder6 = stringBuilder; dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); stringBuilder6.AppendFormat(" AND DATEDIFF(dd,StatisticalDate,'{0}') = 0 ", dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.inOneWeek: { StringBuilder stringBuilder3 = stringBuilder; dateTime = DateTime.Now; dateTime = dateTime.AddDays(-7.0); string arg2 = dateTime.ToString("yyyy-MM-dd"); dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); dateTime = dateTime.Date; stringBuilder3.AppendFormat(" AND (StatisticalDate BETWEEN '{0}' AND '{1}')", arg2, dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.inTwoWeek: { StringBuilder stringBuilder4 = stringBuilder; dateTime = DateTime.Now; dateTime = dateTime.AddDays(-14.0); string arg3 = dateTime.ToString("yyyy-MM-dd"); dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); dateTime = dateTime.Date; stringBuilder4.AppendFormat(" AND (StatisticalDate BETWEEN '{0}' AND '{1}')", arg3, dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.inOneMonth: { StringBuilder stringBuilder5 = stringBuilder; dateTime = DateTime.Now; dateTime = dateTime.AddMonths(-1); string arg4 = dateTime.ToString("yyyy-MM-dd"); dateTime = DateTime.Now; dateTime = dateTime.AddDays(-1.0); dateTime = dateTime.Date; stringBuilder5.AppendFormat(" AND (StatisticalDate BETWEEN '{0}' AND '{1}')", arg4, dateTime.ToString("yyyy-MM-dd")); break; } case EnumConsumeTime.custom: if (query.CustomConsumeStartTime.HasValue && query.CustomConsumeEndTime.HasValue) { StringBuilder stringBuilder2 = stringBuilder; dateTime = query.CustomConsumeStartTime.Value; string arg = dateTime.ToString("yyyy-MM-dd"); dateTime = query.CustomConsumeEndTime.Value; stringBuilder2.AppendFormat(" AND (StatisticalDate BETWEEN '{0}' AND '{1}')", arg, dateTime.ToString("yyyy-MM-dd")); } break; } SortAction sortOrder = query.SortOrder; string text2 = "pv"; if (!string.IsNullOrEmpty(query.SortBy)) { text2 = ((!(query.SortBy != "productconversionrate")) ? "(CASE WHEN SUM(UV) <=0 THEN 0 ELSE CONVERT(money,SUM(PaymentNum))/SUM(UV)*100 END)" : ("SUM(" + query.SortBy + ")")); } format = string.Format(format, stringBuilder, text, text2, (sortOrder == SortAction.Desc) ? "DESC" : "ASC"); format2 = string.Format(format2, stringBuilder); DbCommand sqlStringCommand = base.database.GetSqlStringCommand(format + format2); PageModel <ProductStatisticsInfo> pageModel = new PageModel <ProductStatisticsInfo>(); using (IDataReader dataReader = base.database.ExecuteReader(sqlStringCommand)) { pageModel.Models = DataHelper.ReaderToList <ProductStatisticsInfo>(dataReader); if (dataReader.NextResult() && dataReader.Read()) { pageModel.Total = ((IDataRecord)dataReader)["Total"].ToInt(0); } } return(pageModel); }
public static IList <ProductStatisticsInfo> GetProductStatisticsDataNoPage(ProductStatisticsQuery query) { return(new ProductStatisticsDao().GetProductStatisticsDataNoPage(query)); }
public static IList <ProductCategoryStatisticsInfo> GetProductCategoryStatisticsData(ProductStatisticsQuery query) { return(new ProductStatisticsDao().GetProductCategoryStatisticsData(query)); }
public static PageModel <ProductStatisticsInfo> GetProductStatisticsData(ProductStatisticsQuery query) { return(new ProductStatisticsDao().GetProductStatisticsData(query)); }