public void ExportToExcel(HttpContext context) { BalanceStatisticsQuery dataQuery = this.GetDataQuery(context); IList <SupplierSettlementModel> balanceStatisticsExportData = BalanceHelper.GetBalanceStatisticsExportData(dataQuery); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("<table border='1'>"); stringBuilder.Append("<thead><tr>"); stringBuilder.Append("<th>供应商</th>"); stringBuilder.Append("<th>联系电话</th>"); stringBuilder.Append("<th>可提现金额</th>"); stringBuilder.Append("<th>已冻结提现</th>"); stringBuilder.Append("<th>已提现总额</th>"); stringBuilder.Append("</tr></thead>"); StringBuilder stringBuilder2 = new StringBuilder(); foreach (SupplierSettlementModel item in balanceStatisticsExportData) { stringBuilder2.Append("<tr>"); stringBuilder2.Append(ExcelHelper.GetXLSFieldsTD(item.SupplierName, true)); stringBuilder2.Append(ExcelHelper.GetXLSFieldsTD(item.Tel, false)); stringBuilder2.Append(ExcelHelper.GetXLSFieldsTD(item.CanDrawRequestBalance.F2ToString("f2"), false)); stringBuilder2.Append(ExcelHelper.GetXLSFieldsTD(item.FrozenBalance.F2ToString("f2"), false)); stringBuilder2.Append(ExcelHelper.GetXLSFieldsTD(item.BalanceOut.F2ToString("f2"), false)); stringBuilder2.Append("</tr>"); } stringBuilder.AppendFormat("<tbody>{0}</tbody></table>", stringBuilder2.ToString()); StringWriter stringWriter = new StringWriter(); stringWriter.Write(stringBuilder); DownloadHelper.DownloadFile(context.Response, stringWriter.GetStringBuilder(), "SupplierSettlement" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls"); stringWriter.Close(); context.Response.End(); }
private BalanceStatisticsQuery GetDataQuery(HttpContext context) { BalanceStatisticsQuery balanceStatisticsQuery = new BalanceStatisticsQuery(); balanceStatisticsQuery.Key = base.GetParameter(context, "Key", true); balanceStatisticsQuery.PageIndex = base.CurrentPageIndex; balanceStatisticsQuery.PageSize = base.CurrentPageSize; return(balanceStatisticsQuery); }
public void GetList(HttpContext context) { BalanceStatisticsQuery 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 DataGridViewModel <Dictionary <string, object> > GetDataList(BalanceStatisticsQuery query) { DataGridViewModel <Dictionary <string, object> > dataGridViewModel = new DataGridViewModel <Dictionary <string, object> >(); if (query != null) { DbQueryResult balanceStatisticsList = BalanceHelper.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(BalanceStatisticsQuery query) { DbQueryResult dbQueryResult = new DbQueryResult(); StringBuilder stringBuilder = new StringBuilder(); StringBuilder stringBuilder2 = new StringBuilder(); stringBuilder.AppendFormat("select top {0} SupplierId,SupplierName,Tel,Balance,", query.PageSize); stringBuilder.Append("(select isNull(SUM(amount),0) from Hishop_SupplierBalanceDrawRequest r where r.SupplierId=s.SupplierId and IsPass is null) as BalanceFozen,"); stringBuilder.Append("(select isnull(SUM(amount),0) from Hishop_SupplierBalanceDrawRequest r where r.SupplierId=s.SupplierId and IsPass=1) as BalanceOut"); stringBuilder.Append(" from dbo.Hishop_Supplier s where 0=0"); if (!string.IsNullOrEmpty(query.Key)) { stringBuilder2.AppendFormat(" and SupplierName like '%{0}%'", query.Key); } if (query.PageIndex == 1) { stringBuilder.AppendFormat("{0} ORDER BY SupplierId DESC", stringBuilder2); } else { stringBuilder.AppendFormat(" and SupplierId < (select min(SupplierId) from (select top {0} SupplierId from Hishop_Supplier where 0=0 {1} ORDER BY SupplierId DESC ) as tbltemp) {1} ORDER BY SupplierId DESC", (query.PageIndex - 1) * query.PageSize, stringBuilder2); } if (query.IsCount) { stringBuilder.AppendFormat(";select count(1) as Total from Hishop_Supplier where 0=0 {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 IList <SupplierSettlementModel> GetBalanceStatisticsExportData(BalanceStatisticsQuery query) { IList <SupplierSettlementModel> result = new List <SupplierSettlementModel>(); StringBuilder stringBuilder = new StringBuilder(); StringBuilder stringBuilder2 = new StringBuilder(); stringBuilder.Append("SELECT SupplierId,SupplierName,Tel,Balance,"); stringBuilder.Append("(SELECT ISNULL(SUM(amount),0) FROM Hishop_SupplierBalanceDrawRequest r WHERE r.SupplierId=s.SupplierId AND IsPass IS null) AS BalanceFozen,"); stringBuilder.Append("(SELECT ISNULL(SUM(amount),0) FROM Hishop_SupplierBalanceDrawRequest r WHERE r.SupplierId=s.SupplierId AND IsPass=1) AS BalanceOut"); stringBuilder.Append(" FROM dbo.Hishop_Supplier s WHERE 0=0"); if (!string.IsNullOrEmpty(query.Key)) { stringBuilder2.AppendFormat(" AND SupplierName like '%{0}%'", query.Key); } stringBuilder.AppendFormat("{0} ORDER BY SupplierId DESC", stringBuilder2); DbCommand sqlStringCommand = base.database.GetSqlStringCommand(stringBuilder.ToString()); using (IDataReader objReader = base.database.ExecuteReader(sqlStringCommand)) { result = DataHelper.ReaderToList <SupplierSettlementModel>(objReader); } return(result); }
public static IList <SupplierSettlementModel> GetBalanceStatisticsExportData(BalanceStatisticsQuery query) { return(new BalanceDao().GetBalanceStatisticsExportData(query)); }
public static DbQueryResult GetBalanceStatisticsList(BalanceStatisticsQuery query) { return(new BalanceDao().GetBalanceStatisticsList(query)); }