private void BindStatistics(StatisticsInfo statistics) { ManagerHelper.GetCurrentManager(); if (statistics.OrderNumbWaitConsignment > 0) { this.ltrWaitSendOrdersNumber.NavigateUrl = "javascript:ShowSecondMenuLeft('微订单','sales/manageorder.aspx','" + Globals.ApplicationPath + "/Admin/sales/ManageOrder.aspx?orderStatus=2')"; this.ltrWaitSendOrdersNumber.Text = statistics.OrderNumbWaitConsignment.ToString() + "条"; } else { this.ltrWaitSendOrdersNumber.Text = "<font style=\"color:#2d2d2d\">0条</font>"; } if (statistics.GroupBuyNumWaitRefund > 0) { this.WaitForRefund.NavigateUrl = "javascript:ShowSecondMenuLeft('微营销','promotion/groupbuys.aspx','" + Globals.ApplicationPath + "/Admin/promotion/groupbuys.aspx?state=5')"; this.WaitForRefund.Text = statistics.GroupBuyNumWaitRefund.ToString() + "个"; } else { this.WaitForRefund.Text = "<font style=\"color:#2d2d2d\">0个</font>"; } this.lblTodayOrderAmout.Text = (statistics.OrderPriceToday > 0M) ? ("¥" + Globals.FormatMoney(statistics.OrderPriceToday)) : string.Empty; this.ltrTodayAddMemberNumber.Text = (statistics.UserNewAddToday > 0) ? statistics.UserNewAddToday.ToString() : string.Empty; this.lblTodayFinishOrder.Text = (statistics.TodayFinishOrder > 0) ? statistics.TodayFinishOrder.ToString() : string.Empty; this.lblYesterdayFinishOrder.Text = (statistics.YesterdayFinishOrder > 0) ? statistics.YesterdayFinishOrder.ToString() : string.Empty; this.lblOrderPriceYesterDay.Text = (statistics.OrderPriceYesterday > 0M) ? ("¥" + statistics.OrderPriceYesterday.ToString("F2")) : string.Empty; this.lblUserNewAddYesterToday.Text = (statistics.UserNewAddYesterToday > 0) ? (statistics.UserNewAddYesterToday.ToString() + "位") : string.Empty; this.lblTotalMembers.Text = (statistics.TotalMembers > 0) ? (statistics.TotalMembers.ToString() + "位") : string.Empty; this.lblTotalProducts.Text = (statistics.TotalProducts > 0) ? (statistics.TotalProducts.ToString() + "条") : string.Empty; this.lblOrderPriceMonth.Text = (statistics.OrderPriceMonth > 0M) ? ("¥" + statistics.OrderPriceMonth.ToString("F2")) : string.Empty; }
private void BindBusinessInformation(StatisticsInfo statisticsInfo) { ltrWaitSendOrdersNumber.Text = statisticsInfo.OrderNumbWaitConsignment.ToString(); hpkZiXun.Text = statisticsInfo.ProductConsultations.ToString(); hpkMessages.Text = statisticsInfo.Messages.ToString(); hpkLiuYan.Text = statisticsInfo.LeaveComments.ToString(); lblTodayOrderAmout.Text = Globals.FormatMoney(statisticsInfo.OrderPriceToday); lblTodaySalesProfile.Text = Globals.FormatMoney(statisticsInfo.OrderProfitToday); ltrTodayAddMemberNumber.Text = statisticsInfo.UserNewAddToday.ToString(); lblMembersBalanceTotal.Text = Globals.FormatMoney(statisticsInfo.Balance); lblProductCountTotal.Text = (statisticsInfo.ProductAlert > 0) ? ("<a href='" + Globals.ApplicationPath + "/Shopadmin/product/MyProductAlert.aspx'>" + statisticsInfo.ProductAlert.ToString() + "</a>") : "0"; ltrWaitSendPurchaseOrdersNumber.Text = statisticsInfo.PurchaseOrderNumbWaitConsignment.ToString(); hpkLiuYan.NavigateUrl = Globals.ApplicationPath + "/Shopadmin/comment/ManageMyLeaveComments.aspx?MessageStatus=3"; hpkZiXun.NavigateUrl = Globals.ApplicationPath + "/Shopadmin/comment/MyProductConsultations.aspx"; hpkMessages.NavigateUrl = Globals.ApplicationPath + "/Shopadmin/comment/MyReceivedMessages.aspx?IsRead=0"; }
public static StatisticsInfo PopulateStatistics(IDataRecord reader) { if (null == reader) { return null; } StatisticsInfo info = new StatisticsInfo(); info.OrderNumbWaitConsignment = (int) reader["orderNumbWaitConsignment"]; info.ApplyRequestWaitDispose = (int) reader["ApplyRequestWaitDispose"]; info.ProductNumStokWarning = (int) reader["ProductNumStokWarning"]; info.PurchaseOrderNumbWaitConsignment = (int) reader["purchaseOrderNumbWaitConsignment"]; info.LeaveComments = (int) reader["LeaveComments"]; info.ProductConsultations = (int) reader["ProductConsultations"]; info.Messages = (int) reader["Messages"]; info.OrderNumbToday = (int) reader["OrderNumbToday"]; info.OrderPriceToday = (decimal) reader["OrderPriceToday"]; info.OrderProfitToday = (decimal) reader["OrderProfitToday"]; info.UserNewAddToday = (int) reader["UserNewAddToday"]; info.DistroButorsNewAddToday = (int) reader["AgentNewAddToday"]; info.UserNumbBirthdayToday = (int) reader["userNumbBirthdayToday"]; info.OrderNumbYesterday = (int) reader["OrderNumbYesterday"]; info.OrderPriceYesterday = (decimal) reader["OrderPriceYesterday"]; info.OrderProfitYesterday = (decimal) reader["OrderProfitYesterday"]; info.UserNumb = (int) reader["UserNumb"]; info.DistroButorsNumb = (int) reader["AgentNumb"]; info.Balance = (decimal) reader["memberBalance"]; info.BalanceDrawRequested = (decimal) reader["BalanceDrawRequested"]; info.ProductNumbOnSale = (int) reader["ProductNumbOnSale"]; info.ProductNumbInStock = (int) reader["ProductNumbInStock"]; info.ProductAlert = (int) reader["ProductAlert"]; if (reader["authorizeProductCount"] != DBNull.Value) { info.AuthorizeProductCount = (int) reader["authorizeProductCount"]; } if (reader["arealdyPaidNum"] != DBNull.Value) { info.AlreadyPaidOrdersNum = (int) reader["arealdyPaidNum"]; } if (reader["arealdyPaidTotal"] != DBNull.Value) { info.AreadyPaidOrdersAmount = (decimal) reader["arealdyPaidTotal"]; } return info; }
public override StatisticsInfo GetStatistics() { DbCommand sqlStringCommand = database.GetSqlStringCommand("SELECT " + string.Format(" (SELECT COUNT(OrderId) FROM distro_Orders WHERE OrderStatus={0} AND DistributorUserId={1}) AS orderNumbWaitConsignment,", 2, HiContext.Current.User.UserId) + string.Format(" (SELECT COUNT(*) FROM distro_BalanceDrawRequest WHERE DistributorUserId={0} ) AS applyRequestWaitDispose,", HiContext.Current.User.UserId) + string.Format(" (SELECT COUNT(PurchaseOrderId) FROM Hishop_PurchaseOrders WHERE PurchaseStatus={0} AND DistributorId={1}) AS purchaseOrderNumbWaitConsignment, ", 1, HiContext.Current.User.UserId) + " 0 as productNumStokWarning," + string.Format("(select Count(LeaveId) from distro_LeaveComments l where (select count(replyId) from distro_LeaveCommentReplys where leaveId =l.leaveId)=0 and DistributorUserId={0} ) as leaveComments,", HiContext.Current.User.UserId) + string.Format("(select Count(ConsultationId) from distro_ProductConsultations where ReplyUserId is null AND DistributorUserId={0}) as productConsultations,", HiContext.Current.User.UserId) + string.Format("(select Count(ReceiveMessageId) from distro_ReceivedMessages where IsRead=0 AND Addressee='{0}' ) as messages,", HiContext.Current.User.Username) + string.Format(" (select count(orderId) from distro_orders where (OrderStatus=2 or OrderStatus=3 or OrderStatus=5) and DistributorUserId={0} ", HiContext.Current.User.UserId) + " and OrderDate>='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "') as orderNumbToday," + string.Format(" isnull((select sum(OrderTotal)-isnull(sum(RefundAmount),0) from distro_orders where (OrderStatus=2 or OrderStatus=3 or OrderStatus=5) AND DistributorUserId={0} ", HiContext.Current.User.UserId) + " and OrderDate>='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "'),0) as orderPriceToday," + string.Format(" isnull((select sum(orderProfit) from distro_orders where (OrderStatus=2 or OrderStatus=3 or OrderStatus=5) AND DistributorUserId={0} ", HiContext.Current.User.UserId) + " and OrderDate>='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "'),0) as orderProfitToday," + string.Format(" (select count(*) from vw_distro_Members where ParentUserId={0} and CreateDate>='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "' ) as userNewAddToday,", HiContext.Current.User.UserId) + " 0 as agentNewAddToday, 0 as userNumbBirthdayToday ," + string.Format(" (select count(orderId) from distro_orders where (OrderStatus=2 or OrderStatus=3 or OrderStatus=5) AND DistributorUserId={0} ", HiContext.Current.User.UserId) + " and OrderDate>='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date.AddDays(-1.0)) + "' and OrderDate<='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "') as orderNumbYesterday," + string.Format(" isnull((select sum(orderTotal) from distro_orders where (OrderStatus=2 or OrderStatus=3 or OrderStatus=5) AND DistributorUserId={0} ", HiContext.Current.User.UserId) + " and OrderDate>='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date.AddDays(-1.0)) + "' and OrderDate<='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "'),0) as orderPriceYesterday," + string.Format(" isnull((select sum(orderProfit) from distro_orders where ( OrderStatus=2 or OrderStatus=3 or OrderStatus=5) AND DistributorUserId={0} ", HiContext.Current.User.UserId) + " and OrderDate>='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date.AddDays(-1.0)) + "' and OrderDate<='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "'),0) as orderProfitYesterday," + string.Format(" (select count(*) from vw_distro_Members where ParentUserId={0}) as userNumb,", HiContext.Current.User.UserId) + " 0 as agentNumb," + string.Format(" isnull((select sum(Balance) from vw_distro_Members WHERE ParentUserId={0}),0) as memberBalance,", HiContext.Current.User.UserId) + " 0.00 as BalanceDrawRequested," + string.Format(" (SELECT COUNT(PurchaseOrderId) FROM Hishop_PurchaseOrders WHERE PurchaseStatus = 1 AND DistributorId={0}) AS purchaseOrderNumbWaitConsignment,", HiContext.Current.User.UserId) + string.Format(" (select count(productId) from distro_Products where SaleStatus={0} and DistributorUserId={1}) as productNumbOnSale,", 1, HiContext.Current.User.UserId) + string.Format(" (select count(productId) from distro_Products where SaleStatus={0} and DistributorUserId={1}) as productNumbInStock,", 1, HiContext.Current.User.UserId) + string.Format(" (select count(productId) from distro_Products where DistributorUserId={0}) as authorizeProductCount,", HiContext.Current.User.UserId) + string.Format(" (select count(orderId) from distro_orders where (OrderStatus=2 or OrderStatus=3 or OrderStatus=5) AND DistributorUserId={0} ) as arealdyPaidNum,", HiContext.Current.User.UserId) + string.Format(" (select sum(OrderTotal) from distro_orders where (OrderStatus=2 or OrderStatus=3 or OrderStatus=5) AND DistributorUserId={0} ) as arealdyPaidTotal,", HiContext.Current.User.UserId) + string.Format(" (select count(*) from distro_Products where DistributorUserId={0} and ProductId in (select productId from Hishop_SKUs where Stock<=AlertStock group by productId)) as ProductAlert", HiContext.Current.User.UserId)); StatisticsInfo info = new StatisticsInfo(); using (IDataReader reader = database.ExecuteReader(sqlStringCommand)) { if (reader.Read()) { info = DataMapper.PopulateStatistics(reader); } } return info; }
public StatisticsInfo GetStatistics() { DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT (SELECT COUNT(OrderId) FROM Hishop_Orders WHERE OrderStatus = 2 OR (OrderStatus = 1 AND Gateway = 'hishop.plugins.payment.podrequest')) AS orderNumbWaitConsignment, (select count(GroupBuyId) from Hishop_GroupBuy where Status = 5) as groupBuyNumWaitRefund, isnull((select sum(OrderTotal)-isnull(sum(RefundAmount),0) from hishop_orders where (OrderStatus<>1 AND OrderStatus<>4 AND OrderStatus<>9) and OrderDate>='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "'),0) as orderPriceToday, isnull((select sum(OrderProfit) from Hishop_Orders where (OrderStatus<>1 AND OrderStatus<>4 AND OrderStatus<>9) and OrderDate>='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "'),0) as orderProfitToday, (select count(*) from aspnet_Members where CreateDate>='" + DataHelper.GetSafeDateTimeFormat(DateTime.Now.Date) + "' ) as userNewAddToday,(select count(*) from Hishop_Orders where datediff(dd,getdate(),OrderDate)=0 and (OrderStatus<>1 AND OrderStatus<>4 AND OrderStatus<>9)) as todayFinishOrder,(select count(*) from Hishop_Orders where datediff(dd,getdate()-1,OrderDate)=0 and (OrderStatus<>1 AND OrderStatus<>4 AND OrderStatus<>9)) as yesterdayFinishOrder, isnull((select sum(OrderTotal)-isnull(sum(RefundAmount),0) from hishop_orders where (OrderStatus<>1 AND OrderStatus<>4 AND OrderStatus<>9) and datediff(dd,getdate()-1,OrderDate)=0),0) as orderPriceYesterDay,(select count(*) from aspnet_Members where datediff(dd,getdate()-1,CreateDate)=0) as userNewAddYesterToday,(select count(*) from aspnet_Members) as TotalMembers,(select count(*) from Hishop_Products where SaleStatus!=0) as TotalProducts,(select count(*) from aspnet_Members where datediff(dd,getdate(),VipCardDate)=0 and VipCardNumber IS NOT NULL) as TodayVipCardNumber,(select count(*) from aspnet_Members where datediff(dd,getdate()-1,VipCardDate)=0 and VipCardNumber IS NOT NULL) as YesterTodayVipCardNumber, isnull((select sum(OrderTotal)-isnull(sum(RefundAmount),0) from hishop_orders where (OrderStatus<>1 AND OrderStatus<>4 AND OrderStatus<>9) and datediff(dd,OrderDate,getdate())<=30),0) as orderPriceMonth"); StatisticsInfo info = new StatisticsInfo(); using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand)) { if (reader.Read()) { info.OrderNumbWaitConsignment = (int) reader["orderNumbWaitConsignment"]; info.OrderPriceToday = (decimal) reader["orderProfitToday"]; info.OrderProfitToday = (decimal) reader["orderPriceToday"]; info.UserNewAddToday = (int) reader["userNewAddToday"]; info.TodayFinishOrder = (int) reader["todayFinishOrder"]; info.YesterdayFinishOrder = (int) reader["yesterdayFinishOrder"]; info.UserNewAddYesterToday = (int) reader["userNewAddYesterToday"]; info.TotalMembers = (int) reader["TotalMembers"]; info.TotalProducts = (int) reader["TotalProducts"]; info.TodayVipCardNumber = (int) reader["TodayVipCardNumber"]; info.YesterTodayVipCardNumber = (int) reader["YesterTodayVipCardNumber"]; info.OrderPriceMonth = (decimal) reader["OrderPriceMonth"]; info.GroupBuyNumWaitRefund = (int) reader["groupBuyNumWaitRefund"]; info.OrderPriceYesterday = (decimal) reader["orderPriceYesterDay"]; } } return info; }