private void btnCreateReport_Click(object sender, EventArgs e) { UserOrderQuery userOrder = new UserOrderQuery(); userOrder.UserName = userName; userOrder.ShipTo = shipTo; userOrder.StartDate = dateStart; userOrder.EndDate = dateEnd; userOrder.OrderId = orderId; userOrder.PageSize = pager.PageSize; userOrder.PageIndex = pager.PageIndex; userOrder.SortBy = grdUserOrderStatistics.SortOrderBy; if (grdUserOrderStatistics.SortOrder.ToLower() == "desc") { userOrder.SortOrder = SortAction.Desc; } OrderStatisticsInfo userOrdersNoPage = SubsiteSalesHelper.GetUserOrdersNoPage(userOrder); StringBuilder builder = new StringBuilder(); builder.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">"); builder.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;\">"); builder.AppendLine("<td>订单号</td>"); builder.AppendLine("<td>下单时间</td>"); builder.AppendLine("<td>总订单金额</td>"); builder.AppendLine("<td>用户名</td>"); builder.AppendLine("<td>收货人</td>"); builder.AppendLine("<td>利润</td>"); builder.AppendLine("</tr>"); foreach (DataRow row in userOrdersNoPage.OrderTbl.Rows) { builder.AppendLine("<tr>"); builder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + row["OrderId"].ToString() + "</td>"); builder.AppendLine("<td>" + row["OrderDate"].ToString() + "</td>"); builder.AppendLine("<td>" + row["Total"].ToString() + "</td>"); builder.AppendLine("<td>" + row["UserName"].ToString() + "</td>"); builder.AppendLine("<td>" + row["ShipTo"].ToString() + "</td>"); builder.AppendLine("<td>" + row["Profits"].ToString() + "</td>"); builder.AppendLine("</tr>"); } builder.AppendLine("<tr>"); builder.AppendLine("<td>当前查询结果共计," + userOrdersNoPage.TotalCount + "</td>"); builder.AppendLine("<td>订单金额共计," + userOrdersNoPage.TotalOfSearch + "</td>"); builder.AppendLine("<td>订单毛利润共计," + userOrdersNoPage.ProfitsOfSearch + "</td>"); builder.AppendLine("<td></td>"); builder.AppendLine("</tr>"); builder.AppendLine("</table>"); Page.Response.Clear(); Page.Response.Buffer = false; Page.Response.Charset = "utf-8"; Page.Response.AppendHeader("Content-Disposition", "attachment;filename=UserOrderStatistics.xls"); Page.Response.ContentEncoding = Encoding.GetEncoding("utf-8"); Page.Response.ContentType = "application/ms-excel"; Page.EnableViewState = false; Page.Response.Write(builder.ToString()); Page.Response.End(); }
private void btnCreateReport_Click(object sender, EventArgs e) { UserOrderQuery query2 = new UserOrderQuery(); query2.UserName = userName; query2.StartDate = dateStart; query2.EndDate = dateEnd; query2.PageIndex = pager.PageIndex; query2.SortBy = grdPurchaseOrderStatistics.SortOrderBy; UserOrderQuery order = query2; if (grdPurchaseOrderStatistics.SortOrder.ToLower() == "desc") { order.SortOrder = SortAction.Desc; } OrderStatisticsInfo purchaseOrdersNoPage = DistributorHelper.GetPurchaseOrdersNoPage(order); StringBuilder builder = new StringBuilder(); builder.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">"); builder.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;\">"); builder.AppendLine("<td>采购单号</td>"); builder.AppendLine("<td>订单号</td>"); builder.AppendLine("<td>下单时间</td>"); builder.AppendLine("<td>分销商名称</td>"); builder.AppendLine("<td>采购单金额</td>"); builder.AppendLine("<td>利润</td>"); builder.AppendLine("</tr>"); foreach (DataRow row in purchaseOrdersNoPage.OrderTbl.Rows) { builder.AppendLine("<tr>"); builder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + row["PurchaseOrderId"].ToString() + "</td>"); builder.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + row["OrderId"].ToString() + "</td>"); builder.AppendLine("<td>" + row["PurchaseDate"].ToString() + "</td>"); builder.AppendLine("<td>" + row["Distributorname"].ToString() + "</td>"); builder.AppendLine("<td>" + row["PurchaseTotal"].ToString() + "</td>"); builder.AppendLine("<td>" + row["PurchaseProfit"].ToString() + "</td>"); builder.AppendLine("</tr>"); } builder.AppendLine("<tr>"); builder.AppendLine("<td>当前查询结果共计," + purchaseOrdersNoPage.TotalCount + "</td>"); builder.AppendLine("<td>采购单金额共计," + purchaseOrdersNoPage.TotalOfSearch + "</td>"); builder.AppendLine("<td>采购单毛利润共计," + purchaseOrdersNoPage.ProfitsOfSearch + "</td>"); builder.AppendLine("<td></td>"); builder.AppendLine("</tr>"); builder.AppendLine("</table>"); Page.Response.Clear(); Page.Response.Buffer = false; Page.Response.Charset = "UTF-8"; Page.Response.AppendHeader("Content-Disposition", "attachment;filename=PurchaseOrderStatistics.xls"); Page.Response.ContentEncoding = Encoding.UTF8; Page.Response.ContentType = "application/ms-excel"; Page.EnableViewState = false; Page.Response.Write(builder.ToString()); Page.Response.End(); }
private void BindUserOrderStatistics() { UserOrderQuery userOrder = new UserOrderQuery(); userOrder.UserName = userName; userOrder.ShipTo = shipTo; userOrder.StartDate = dateStart; userOrder.EndDate = dateEnd; userOrder.OrderId = orderId; userOrder.PageSize = pager.PageSize; userOrder.PageIndex = pager.PageIndex; userOrder.SortBy = ""; OrderStatisticsInfo userOrders = SalesHelper.GetUserOrders(userOrder); rp_orderStatistics.DataSource = userOrders.OrderTbl; rp_orderStatistics.DataBind(); pager.TotalRecords = userOrders.TotalCount; lblPageCount.Text = string.Format("当前页共计<span style=\"color:red;\">{0}</span>个 <span style=\"padding-left:10px;\">订单金额共计</span><span style=\"color:red;\">{1}</span>元 <span style=\"padding-left:10px;\">订单毛利润共计</span><span style=\"color:red;\">{2}</span>元 ", userOrders.OrderTbl.Rows.Count, Globals.FormatMoney(userOrders.TotalOfPage), Globals.FormatMoney(userOrders.ProfitsOfPage)); lblSearchCount.Text = string.Format("当前查询结果共计<span style=\"color:red;\">{0}</span>个 <span style=\"padding-left:10px;\">订单金额共计</span><span style=\"color:red;\">{1}</span>元 <span style=\"padding-left:10px;\">订单毛利润共计</span><span style=\"color:red;\">{2}</span>元 ", userOrders.TotalCount, Globals.FormatMoney(userOrders.TotalOfSearch), Globals.FormatMoney(userOrders.ProfitsOfSearch)); }
private void BindPurchaseOrderStatistics() { UserOrderQuery order = new UserOrderQuery(); order.UserName = userName; order.StartDate = dateStart; order.EndDate = dateEnd; order.PageSize = 10; order.PageIndex = pager.PageIndex; order.SortBy = grdPurchaseOrderStatistics.SortOrderBy; if (grdPurchaseOrderStatistics.SortOrder.ToLower() == "desc") { order.SortOrder = SortAction.Desc; } OrderStatisticsInfo purchaseOrders = DistributorHelper.GetPurchaseOrders(order); grdPurchaseOrderStatistics.DataSource = purchaseOrders.OrderTbl; grdPurchaseOrderStatistics.DataBind(); pager.TotalRecords = purchaseOrders.TotalCount; lblPageCount.Text = string.Format("当前页共计<span style=\"color:red;\">{0}</span>个 <span style=\"padding-left:10px;\">采购单金额共计</span><span style=\"color:red;\">{1}</span>元 <span style=\"padding-left:10px;\">采购单毛利润共计</span><span style=\"color:red;\">{2}</span>元 ", purchaseOrders.OrderTbl.Rows.Count, Globals.FormatMoney(purchaseOrders.TotalOfPage), Globals.FormatMoney(purchaseOrders.ProfitsOfPage)); lblSearchCount.Text = string.Format("当前查询结果共计<span style=\"color:red;\">{0}</span>个 <span style=\"padding-left:10px;\">采购单金额共计</span><span style=\"color:red;\">{1}</span>元 <span style=\"padding-left:10px;\">采购单毛利润共计</span><span style=\"color:red;\">{2}</span>元 ", purchaseOrders.TotalCount, Globals.FormatMoney(purchaseOrders.TotalOfSearch), Globals.FormatMoney(purchaseOrders.ProfitsOfSearch)); }
public static OrderStatisticsInfo GetUserOrdersNoPage(UserOrderQuery userOrder) { return SalesProvider.Instance().GetUserOrdersNoPage(userOrder); }
public static OrderStatisticsInfo GetPurchaseOrdersNoPage(UserOrderQuery order) { return DistributorProvider.Instance().GetPurchaseOrdersNoPage(order); }
static string BuildUserOrderQuery(UserOrderQuery query) { if (null == query) { throw new ArgumentNullException("query"); } StringBuilder builder = new StringBuilder(); builder.AppendFormat("SELECT OrderId FROM Hishop_Orders WHERE OrderStatus != {0} AND OrderStatus != {1}", 1, 4); if (!string.IsNullOrEmpty(query.OrderId)) { builder.AppendFormat(" AND OrderId = '{0}'", DataHelper.CleanSearchString(query.OrderId)); return builder.ToString(); } if (!string.IsNullOrEmpty(query.UserName)) { builder.AppendFormat(" AND UserName LIKE '%{0}%'", DataHelper.CleanSearchString(query.UserName)); } if (!string.IsNullOrEmpty(query.ShipTo)) { builder.AppendFormat(" AND ShipTo LIKE '%{0}%'", DataHelper.CleanSearchString(query.ShipTo)); } 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)); } if (!string.IsNullOrEmpty(query.SortBy)) { builder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), query.SortOrder.ToString()); } return builder.ToString(); }
public override OrderStatisticsInfo GetUserOrdersNoPage(UserOrderQuery userOrder) { OrderStatisticsInfo info = new OrderStatisticsInfo(); DbCommand storedProcCommand = database.GetStoredProcCommand("cp_OrderStatisticsNoPage_Get"); database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, BuildUserOrderQuery(userOrder)); database.AddOutParameter(storedProcCommand, "TotalUserOrders", DbType.Int32, 4); using (IDataReader reader = database.ExecuteReader(storedProcCommand)) { info.OrderTbl = DataHelper.ConverDataReaderToDataTable(reader); if (reader.NextResult()) { reader.Read(); if (reader["OrderTotal"] != DBNull.Value) { info.TotalOfSearch += (decimal)reader["OrderTotal"]; } if (reader["Profits"] != DBNull.Value) { info.ProfitsOfSearch += (decimal)reader["Profits"]; } } } info.TotalCount = (int)database.GetParameterValue(storedProcCommand, "TotaluserOrders"); return info; }
public override OrderStatisticsInfo GetUserOrders(UserOrderQuery userOrder) { OrderStatisticsInfo info = new OrderStatisticsInfo(); DbCommand storedProcCommand = database.GetStoredProcCommand("sub_OrderStatistics_Get"); database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, userOrder.PageIndex); database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, userOrder.PageSize); database.AddInParameter(storedProcCommand, "IsCount", DbType.Boolean, userOrder.IsCount); database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, BuildUserOrderQuery(userOrder)); database.AddOutParameter(storedProcCommand, "TotalUserOrders", DbType.Int32, 4); database.AddInParameter(storedProcCommand, "DistributorUserId", DbType.Int32, HiContext.Current.User.UserId); using (IDataReader reader = database.ExecuteReader(storedProcCommand)) { info.OrderTbl = DataHelper.ConverDataReaderToDataTable(reader); if (reader.NextResult()) { reader.Read(); if (reader["OrderTotal"] != DBNull.Value) { info.TotalOfPage += (decimal)reader["OrderTotal"]; } if (reader["Profits"] != DBNull.Value) { info.ProfitsOfPage += (decimal)reader["Profits"]; } } if (reader.NextResult()) { reader.Read(); if (reader["OrderTotal"] != DBNull.Value) { info.TotalOfSearch += (decimal)reader["OrderTotal"]; } if (reader["Profits"] != DBNull.Value) { info.ProfitsOfSearch += (decimal)reader["Profits"]; } } } info.TotalCount = (int)database.GetParameterValue(storedProcCommand, "TotaluserOrders"); return info; }
public abstract OrderStatisticsInfo GetPurchaseOrdersNoPage(UserOrderQuery order);
public abstract OrderStatisticsInfo GetUserOrdersNoPage(UserOrderQuery userOrder);
public static OrderStatisticsInfo GetUserOrders(UserOrderQuery userOrder) { return SubsiteSalesProvider.Instance().GetUserOrders(userOrder); }