public void ExportExcel(HttpContext context) { StoreBalanceDetailQuery dataQuery = this.GetDataQuery(context); StoresInfo storeById = DepotHelper.GetStoreById(dataQuery.StoreId.Value); IList <StoreBalanceDetailInfo> balanceDetails4Report = StoreBalanceHelper.GetBalanceDetails4Report(dataQuery); StringBuilder stringBuilder = new StringBuilder(300); stringBuilder.Append("门店"); stringBuilder.Append(",时间"); stringBuilder.Append(",类型"); stringBuilder.Append(",订单号"); stringBuilder.Append(",收入"); stringBuilder.Append(",支出"); stringBuilder.Append(",账户余额"); stringBuilder.Append(",备注\r\n"); foreach (StoreBalanceDetailInfo item in balanceDetails4Report) { stringBuilder.Append(storeById.StoreName); stringBuilder.Append("," + item.TradeDate); stringBuilder.Append("," + item.TradeTypeText); stringBuilder.Append("," + item.TradeNo); stringBuilder.Append("," + item.Income); stringBuilder.Append("," + item.Expenses); stringBuilder.Append("," + item.Balance); stringBuilder.Append("," + item.Remark + "\r\n"); } context.Response.Clear(); context.Response.Buffer = false; context.Response.Charset = "GB2312"; context.Response.AppendHeader("Content-Disposition", "attachment;filename=BalanceDetail.csv"); context.Response.ContentEncoding = Encoding.GetEncoding("GB2312"); context.Response.ContentType = "application/octet-stream"; context.Response.Write(stringBuilder.ToString()); context.Response.End(); }
public void GetList(HttpContext context) { StoreBalanceDetailQuery dataQuery = this.GetDataQuery(context); PageModel <StoreBalanceDetailInfo> balanceDetails = StoreBalanceHelper.GetBalanceDetails(dataQuery); string s = base.SerializeObjectToJson(new { Result = new { total = balanceDetails.Total, rows = from b in balanceDetails.Models select new { Income = (b.Income.HasValue ? b.Income.Value.F2ToString("f2").ToDecimal(0) : decimal.Zero), Expenses = (b.Expenses.HasValue ? b.Expenses.Value.F2ToString("f2").ToDecimal(0) : decimal.Zero), TradeType = b.TradeType, TradeTypeText = b.TradeTypeText, TradeDate = b.TradeDate.ToString("yyyy-MM-dd HH:mm"), Balance = b.Balance, TradeNo = b.TradeNo, UserName = ((new ManagerDao().FindManagerByStoreId(b.StoreId, SystemRoles.StoreAdmin) != null) ? new ManagerDao().FindManagerByStoreId(b.StoreId, SystemRoles.StoreAdmin).UserName : "") } } }); context.Response.Write(s); context.Response.End(); }
private string BuildBalanceDetailsQuery(StoreBalanceDetailQuery query) { StringBuilder stringBuilder = new StringBuilder(); if (query.StoreId.HasValue && query.StoreId.Value > 0) { stringBuilder.AppendFormat(" AND StoreId = {0}", query.StoreId.Value); } if (!string.IsNullOrEmpty(query.StoreName)) { stringBuilder.AppendFormat(" AND StoreId IN(SELECT StoreId FROM Hishop_Stores WHERE StoreName LIKE '%{0}%')", DataHelper.CleanSearchString(query.StoreName)); } if (query.FromDate.HasValue) { stringBuilder.AppendFormat(" AND TradeDate >= '{0}'", DataHelper.GetSafeDateTimeFormat(query.FromDate.Value)); } if (query.ToDate.HasValue) { stringBuilder.AppendFormat(" AND TradeDate <= '{0}'", DataHelper.GetSafeDateTimeFormat(query.ToDate.Value.AddDays(1.0))); } if (query.TradeType != 0) { stringBuilder.AppendFormat(" AND TradeType = {0}", (int)query.TradeType); } return(stringBuilder.ToString()); }
public IList <StoreBalanceDetailInfo> GetBalanceDetails4Report(StoreBalanceDetailQuery query) { IList <StoreBalanceDetailInfo> result = new List <StoreBalanceDetailInfo>(); try { if (query == null) { return(result); } DbQueryResult dbQueryResult = new DbQueryResult(); StringBuilder stringBuilder = new StringBuilder(); string arg = this.BuildBalanceDetailsQuery(query); stringBuilder.Append("SELECT * "); stringBuilder.Append(" FROM Hishop_StoreBalanceDetails B WHERE 0 = 0 "); stringBuilder.AppendFormat("{0} ORDER BY JournalNumber DESC", arg); DbCommand sqlStringCommand = base.database.GetSqlStringCommand(stringBuilder.ToString()); using (IDataReader objReader = base.database.ExecuteReader(sqlStringCommand)) { result = DataHelper.ReaderToList <StoreBalanceDetailInfo>(objReader); } return(result); } catch (Exception) { return(result); } }
private StoreBalanceDetailQuery GetDataQuery(HttpContext context) { StoreBalanceDetailQuery storeBalanceDetailQuery = new StoreBalanceDetailQuery(); storeBalanceDetailQuery.StoreName = base.GetParameter(context, "Key", true); storeBalanceDetailQuery.PageIndex = base.CurrentPageIndex; storeBalanceDetailQuery.PageSize = base.CurrentPageSize; return(storeBalanceDetailQuery); }
public void GetList(HttpContext context) { StoreBalanceDetailQuery dataQuery = this.GetDataQuery(context); DataGridViewModel <Dictionary <string, object> > dataList = this.GetDataList(dataQuery); string s = base.SerializeObjectToJson(dataList); context.Response.Write(s); context.Response.End(); }
private StoreBalanceDetailQuery GetDataQuery(HttpContext context) { StoreBalanceDetailQuery storeBalanceDetailQuery = new StoreBalanceDetailQuery(); storeBalanceDetailQuery.StoreId = base.GetIntParam(context, "StoreId", false); storeBalanceDetailQuery.FromDate = base.GetDateTimeParam(context, "FromDate"); storeBalanceDetailQuery.ToDate = base.GetDateTimeParam(context, "ToDate"); storeBalanceDetailQuery.TradeType = (StoreTradeTypes)base.GetIntParam(context, "TradeType", false).Value; storeBalanceDetailQuery.PageIndex = base.CurrentPageIndex; storeBalanceDetailQuery.PageSize = base.CurrentPageSize; return(storeBalanceDetailQuery); }
public PageModel <StoreBalanceDetailInfo> GetBalanceDetails(StoreBalanceDetailQuery query) { PageModel <StoreBalanceDetailInfo> pageModel = new PageModel <StoreBalanceDetailInfo>(); pageModel.Total = 0; pageModel.Models = new List <StoreBalanceDetailInfo>(); try { if (query == null) { return(pageModel); } DbQueryResult dbQueryResult = new DbQueryResult(); StringBuilder stringBuilder = new StringBuilder(); string text = this.BuildBalanceDetailsQuery(query); stringBuilder.AppendFormat("SELECT TOP {0} * ", query.PageSize); stringBuilder.Append(" FROM Hishop_StoreBalanceDetails B WHERE 1 = 1"); if (query.PageIndex == 1) { stringBuilder.AppendFormat("{0} ORDER BY TradeDate DESC", text); } else { stringBuilder.AppendFormat(" AND TradeDate < (SELECT MIN(TradeDate) FROM (SELECT TOP {0} JournalNumber FROM Hishop_StoreBalanceDetails WHERE 1 = 1 {1} ORDER BY TradeDate DESC ) as tbltemp) {1} ORDER BY JournalNumber DESC", (query.PageIndex - 1) * query.PageSize, text); } if (query.IsCount) { stringBuilder.AppendFormat(";SELECT COUNT(TradeDate) AS Total FROM Hishop_StoreBalanceDetails WHERE 1 = 1 {0}", text); } DbCommand sqlStringCommand = base.database.GetSqlStringCommand(stringBuilder.ToString()); using (IDataReader dataReader = base.database.ExecuteReader(sqlStringCommand)) { pageModel.Models = DataHelper.ReaderToList <StoreBalanceDetailInfo>(dataReader); if (query.IsCount && dataReader.NextResult()) { dataReader.Read(); pageModel.Total = dataReader.GetInt32(0); } } return(pageModel); } catch (Exception) { return(pageModel); } }
private DataGridViewModel <Dictionary <string, object> > GetDataList(StoreBalanceDetailQuery query) { DataGridViewModel <Dictionary <string, object> > dataGridViewModel = new DataGridViewModel <Dictionary <string, object> >(); if (query != null) { DbQueryResult balanceStatisticsList = StoreBalanceHelper.GetBalanceStatisticsList(query); dataGridViewModel.rows = new List <Dictionary <string, object> >(); dataGridViewModel.total = balanceStatisticsList.TotalRecords; foreach (DataRow row in balanceStatisticsList.Data.Rows) { Dictionary <string, object> dictionary = DataHelper.DataRowToDictionary(row); dictionary.Add("AvailableBalance", dictionary["Balance"].ToDecimal(0) - dictionary["BalanceFozen"].ToDecimal(0)); dataGridViewModel.rows.Add(dictionary); } } return(dataGridViewModel); }
public DbQueryResult GetBalanceStatisticsList(StoreBalanceDetailQuery query) { DbQueryResult dbQueryResult = new DbQueryResult(); StringBuilder stringBuilder = new StringBuilder(); StringBuilder stringBuilder2 = new StringBuilder(); stringBuilder.AppendFormat("SELECT TOP {0} StoreId,StoreName,Tel,Balance,", query.PageSize); stringBuilder.Append("(SELECT ISNULL(SUM(amount),0) FROM Hishop_StoreBalanceDrawRequest r WHERE r.StoreId = s.StoreId AND IsPass IS NULL) as BalanceFozen,"); stringBuilder.Append("(SELECT ISNULL(SUM(amount),0) FROM Hishop_StoreBalanceDrawRequest r WHERE r.StoreId = s.StoreId AND IsPass= 1) as BalanceOut"); stringBuilder.Append(" FROM dbo.Hishop_Stores s WHERE 1 = 1"); if (!string.IsNullOrEmpty(query.StoreName)) { stringBuilder2.AppendFormat(" AND StoreName LIKE '%{0}%'", query.StoreName); } if (query.PageIndex == 1) { stringBuilder.AppendFormat("{0} ORDER BY StoreId DESC", stringBuilder2); } else { stringBuilder.AppendFormat(" AND StoreId < (SELECT MIN(StoreId) FROM (SELECT TOP {0} StoreId FROM Hishop_Stores WHERE 1 = 1 {1} ORDER BY StoreId DESC ) AS tbltemp) {1} ORDER BY StoreId DESC", (query.PageIndex - 1) * query.PageSize, stringBuilder2); } if (query.IsCount) { stringBuilder.AppendFormat(";SELECT COUNT(1) AS Total FROM Hishop_Stores WHERE 1 = 1 {0}", stringBuilder2); } DbCommand sqlStringCommand = base.database.GetSqlStringCommand(stringBuilder.ToString()); using (IDataReader dataReader = base.database.ExecuteReader(sqlStringCommand)) { DataTable dataTable2 = dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(dataReader); if (query.IsCount && dataReader.NextResult()) { dataReader.Read(); dbQueryResult.TotalRecords = dataReader.GetInt32(0); } } return(dbQueryResult); }
public static IList <StoreBalanceDetailInfo> GetBalanceDetails4Report(StoreBalanceDetailQuery query) { return(new StoreBalanceDao().GetBalanceDetails4Report(query)); }
public static DbQueryResult GetBalanceStatisticsList(StoreBalanceDetailQuery query) { return(new StoreBalanceDao().GetBalanceStatisticsList(query)); }
public static PageModel <StoreBalanceDetailInfo> GetBalanceDetails(StoreBalanceDetailQuery query) { return(new StoreBalanceDao().GetBalanceDetails(query)); }