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