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));
}
