private void BindData() { OrderStatisticsQuery_UnderShop orderStatisticsQuery_UnderShop = new OrderStatisticsQuery_UnderShop(); orderStatisticsQuery_UnderShop.BeginDate = this.BeginDate; orderStatisticsQuery_UnderShop.EndDate = this.EndDate; orderStatisticsQuery_UnderShop.Top = new int?(this.Top); orderStatisticsQuery_UnderShop.PageIndex = this.pager.PageIndex; orderStatisticsQuery_UnderShop.PageSize = this.pager.PageSize; orderStatisticsQuery_UnderShop.SortOrder = SortAction.Desc; orderStatisticsQuery_UnderShop.SortBy = "SaleAmountFee"; orderStatisticsQuery_UnderShop.ShopLevel = new int?(2); orderStatisticsQuery_UnderShop.AgentId = new int?(base.GetUrlIntParam("UserId")); Globals.EntityCoding(orderStatisticsQuery_UnderShop, true); DbQueryResult orderStatisticReport_UnderShop = ShopStatisticHelper.GetOrderStatisticReport_UnderShop(orderStatisticsQuery_UnderShop); this.pager.TotalRecords = orderStatisticReport_UnderShop.TotalRecords; this.rptList.DataSource = orderStatisticReport_UnderShop.Data; this.rptList.DataBind(); System.Data.DataRow orderStatisticReportGlobalByAgentID = ShopStatisticHelper.GetOrderStatisticReportGlobalByAgentID(orderStatisticsQuery_UnderShop); this.FXOrderNumber = base.GetFieldIntValue(orderStatisticReportGlobalByAgentID, "OrderNumber"); this.BuyerNumber = base.GetFieldIntValue(orderStatisticReportGlobalByAgentID, "BuyerNumber"); this.FXSaleAmountFee = base.GetFieldDecimalValue(orderStatisticReportGlobalByAgentID, "SaleAmountFee").ToString("N2"); this.FXBuyAvgPrice = "0"; if (base.GetFieldDecimalValue(orderStatisticReportGlobalByAgentID, "BuyerNumber") > 0m) { this.FXBuyAvgPrice = System.Math.Round(base.GetFieldDecimalValue(orderStatisticReportGlobalByAgentID, "SaleAmountFee") / base.GetFieldDecimalValue(orderStatisticReportGlobalByAgentID, "BuyerNumber"), 2).ToString("N2"); } this.FXCommissionFee = base.GetFieldDecimalValue(orderStatisticReportGlobalByAgentID, "CommissionAmountFee").ToString("N2"); }
public DataRow GetOrderStatisticReportGlobalByAgentID(OrderStatisticsQuery_UnderShop query) { if (!query.BeginDate.HasValue) { query.BeginDate = new DateTime?(DateTime.Today.AddDays(-7.0)); } if (!query.EndDate.HasValue) { query.EndDate = new DateTime?(DateTime.Today); } DataTable table = new DataTable(); DbCommand sqlStringCommand = null; sqlStringCommand = this.database.GetSqlStringCommand("\r\n select SUM(OrderNumber) OrderNumber, SUM(SaleAmountFee) SaleAmountFee, SUM(BuyerNumber) BuyerNumber, AVG(BuyerAvgPrice) BuyerAvgPrice, SUM(CommissionAmountFee) CommissionAmountFee\r\n from dbo.vshop_Statistics_Distributors\r\n where AgentID >0\r\n\t and CONVERT( varchar(10), RecDate, 120) >= CONVERT( varchar(10), @BeginDate, 120)\r\n\t and CONVERT( varchar(10), RecDate, 120) <= CONVERT( varchar(10), @EndDate, 120)\r\n and AgentId = @AgentId \r\n \r\n\r\n "); this.database.AddInParameter(sqlStringCommand, "BeginDate", DbType.Date, query.BeginDate.Value); this.database.AddInParameter(sqlStringCommand, "EndDate", DbType.Date, query.EndDate.Value); this.database.AddInParameter(sqlStringCommand, "AgentId", DbType.Int32, query.AgentId); using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand)) { table = DataHelper.ConverDataReaderToDataTable(reader); } if ((table != null) && (table.Rows.Count > 0)) { return(table.Rows[0]); } return(null); }
public DbQueryResult GetOrderStatisticReport_UnderShop(OrderStatisticsQuery_UnderShop query) { StringBuilder builder = new StringBuilder(); builder.Append(" 1=1 "); if (!query.BeginDate.HasValue) { query.BeginDate = new DateTime?(DateTime.Today.AddDays(-7.0)); } if (!query.EndDate.HasValue) { query.EndDate = new DateTime?(DateTime.Today); } string table = ""; if (query.ShopLevel == 1) { table = string.Format("\r\n (\r\n select T1.*, b.RealName, b.CellPhone, b.UserName, b.UserHead , b.StoreName\r\n from \r\n (\r\n select AgentID, SUM(OrderNumber) OrderNumber, SUM(SaleAmountFee) SaleAmountFee, SUM(BuyerNumber) BuyerNumber, AVG(BuyerAvgPrice) BuyerAvgPrice, SUM(CommissionAmountFee) CommissionAmountFee\r\n from dbo.vshop_Statistics_Distributors\r\n where AgentID >0\r\n\t and CONVERT( varchar(10), RecDate, 120) >= CONVERT( varchar(10), '{0}', 120)\r\n\t and CONVERT( varchar(10), RecDate, 120) <= CONVERT( varchar(10), '{1}', 120)\r\n and AgentId in\r\n (\r\n select UserId \r\n from aspnet_Distributors\r\n where ReferralUserId= {2} and UserId <> ReferralUserId\r\n ) \r\n group by AgentID\r\n ) T1\r\n left join vw_Hishop_DistributorsMembers b on T1.AgentId= b.UserId \r\n ) P ", query.BeginDate.Value.ToString("yyyy-MM-dd"), query.EndDate.Value.ToString("yyyy-MM-dd"), query.AgentId); } else if (query.ShopLevel == 2) { table = string.Format("\r\n (\r\n select T1.*, b.RealName, b.CellPhone, b.UserName, b.UserHead , b.StoreName\r\n from \r\n (\r\n select AgentID, SUM(OrderNumber) OrderNumber, SUM(SaleAmountFee) SaleAmountFee, SUM(BuyerNumber) BuyerNumber, AVG(BuyerAvgPrice) BuyerAvgPrice, SUM(CommissionAmountFee) CommissionAmountFee\r\n from dbo.vshop_Statistics_Distributors\r\n where AgentID >0\r\n\t and CONVERT( varchar(10), RecDate, 120) >= CONVERT( varchar(10), '{0}', 120)\r\n\t and CONVERT( varchar(10), RecDate, 120) <= CONVERT( varchar(10), '{1}', 120)\r\n and AgentId in\r\n (\r\n select UserId \r\n from aspnet_Distributors\r\n where ReferralUserId in\r\n (\r\n select UserId \r\n from aspnet_Distributors\r\n where ReferralUserId= {2} and UserId <> ReferralUserId\r\n )\r\n and UserId <> ReferralUserId\r\n ) \r\n group by AgentID\r\n ) T1\r\n left join vw_Hishop_DistributorsMembers b on T1.AgentId= b.UserId \r\n ) P ", query.BeginDate.Value.ToString("yyyy-MM-dd"), query.EndDate.Value.ToString("yyyy-MM-dd"), query.AgentId); } return(DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, table, "AgentId", (builder.Length > 0) ? builder.ToString() : null, "*")); }
private void BindData() { OrderStatisticsQuery_UnderShop entity = new OrderStatisticsQuery_UnderShop { BeginDate = this.BeginDate, EndDate = this.EndDate, Top = new int?(this.Top), PageIndex = this.pager.PageIndex, PageSize = this.pager.PageSize, SortOrder = SortAction.Desc, SortBy = "SaleAmountFee", ShopLevel = 2, AgentId = new int?(base.GetUrlIntParam("UserId")) }; Globals.EntityCoding(entity, true); DbQueryResult result = ShopStatisticHelper.GetOrderStatisticReport_UnderShop(entity); this.pager.TotalRecords = result.TotalRecords; this.rptList.DataSource = result.Data; this.rptList.DataBind(); DataRow orderStatisticReportGlobalByAgentID = ShopStatisticHelper.GetOrderStatisticReportGlobalByAgentID(entity); this.FXOrderNumber = base.GetFieldIntValue(orderStatisticReportGlobalByAgentID, "OrderNumber"); this.BuyerNumber = base.GetFieldIntValue(orderStatisticReportGlobalByAgentID, "BuyerNumber"); this.FXSaleAmountFee = base.GetFieldDecimalValue(orderStatisticReportGlobalByAgentID, "SaleAmountFee").ToString("N2"); this.FXBuyAvgPrice = "0"; if (base.GetFieldDecimalValue(orderStatisticReportGlobalByAgentID, "BuyerNumber") > 0M) { this.FXBuyAvgPrice = Math.Round((decimal)(base.GetFieldDecimalValue(orderStatisticReportGlobalByAgentID, "SaleAmountFee") / base.GetFieldDecimalValue(orderStatisticReportGlobalByAgentID, "BuyerNumber")), 2).ToString("N2"); } this.FXCommissionFee = base.GetFieldDecimalValue(orderStatisticReportGlobalByAgentID, "CommissionAmountFee").ToString("N2"); }
public static DataRow GetOrderStatisticReportGlobalByAgentID(OrderStatisticsQuery_UnderShop Query) { return((new ShopStatisticDao()).GetOrderStatisticReportGlobalByAgentID(Query)); }
public static DbQueryResult GetOrderStatisticReport_UnderShop(OrderStatisticsQuery_UnderShop Query) { return((new ShopStatisticDao()).GetOrderStatisticReport_UnderShop(Query)); }