public DbQueryResult GetBalanceDetails4Report(BalanceDetailSupplierQuery query)
{
try
{
if (query == null)
{
return(new DbQueryResult());
}
DbQueryResult dbQueryResult = new DbQueryResult();
StringBuilder stringBuilder = new StringBuilder();
string arg = this.BuildBalanceDetailsQuery(query);
stringBuilder.Append("SELECT *, isnull (Income, 0) + isnull (Expenses, 0) Amount ,CASE WHEN TradeType = 1 THEN '提现' ELSE '商品交易' END TradeTypeText ");
stringBuilder.Append(" FROM Hishop_SupplierBalanceDetails B where 0=0 ");
stringBuilder.AppendFormat("{0} ORDER BY JournalNumber DESC", arg);
DbCommand sqlStringCommand = base.database.GetSqlStringCommand(stringBuilder.ToString());
using (IDataReader reader = base.database.ExecuteReader(sqlStringCommand))
{
dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(reader);
}
return(dbQueryResult);
}
catch (Exception)
{
return(new DbQueryResult());
}
}
private string BuildBalanceDetailsQuery(BalanceDetailSupplierQuery query)
{
StringBuilder stringBuilder = new StringBuilder();
if (query.SupplierId.HasValue && query.SupplierId.Value > 0)
{
stringBuilder.AppendFormat(" AND SupplierId = {0}", query.SupplierId.Value);
}
if (!string.IsNullOrEmpty(query.Key))
{
stringBuilder.AppendFormat(" AND UserName like '%{0}%'", DataHelper.CleanSearchString(query.Key));
}
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}", query.TradeType);
}
return(stringBuilder.ToString());
}
private void ExportToExcelBalancedetail(HttpContext context)
{
BalanceDetailSupplierQuery balanceDetailSupplierQuery = this.GetBalanceDetailSupplierQuery(context);
DbQueryResult balanceDetails4Report = BalanceHelper.GetBalanceDetails4Report(balanceDetailSupplierQuery);
string empty = string.Empty;
empty += "时间";
empty += ",类型";
empty += ",订单号";
empty += ",收入";
empty += ",支出";
empty += ",账户余额";
empty += ",备注\r\n";
foreach (DataRow row in balanceDetails4Report.Data.Rows)
{
empty += row["TradeDate"];
empty = empty + "," + row["TradeTypeText"];
empty = empty + "," + row["OrderId"];
empty = empty + "," + row["Income"];
empty = empty + "," + row["Expenses"];
empty = empty + "," + row["Balance"];
empty = empty + "," + row["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/ms-excel";
context.Response.Write(empty);
context.Response.End();
}
public void GetList(HttpContext context)
{
BalanceDetailSupplierQuery 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 void GetlistBalanceDetail(HttpContext context)
{
BalanceDetailSupplierQuery balanceDetailSupplierQuery = this.GetBalanceDetailSupplierQuery(context);
DataGridViewModel <Dictionary <string, object> > balanceDetails = this.GetBalanceDetails(balanceDetailSupplierQuery);
string s = base.SerializeObjectToJson(balanceDetails);
context.Response.Write(s);
context.Response.End();
}
public void ExportExcel(HttpContext context)
{
BalanceDetailSupplierQuery dataQuery = this.GetDataQuery(context);
SupplierInfo supplierById = SupplierHelper.GetSupplierById(dataQuery.SupplierId.Value);
DbQueryResult balanceDetails4Report = BalanceHelper.GetBalanceDetails4Report(dataQuery);
StringBuilder stringBuilder = new StringBuilder(300);
stringBuilder.Append("供应商");
stringBuilder.Append(",时间");
stringBuilder.Append(",提现帐户");
stringBuilder.Append(",类型");
stringBuilder.Append(",订单号");
stringBuilder.Append(",收入");
stringBuilder.Append(",支出");
stringBuilder.Append(",账户余额");
stringBuilder.Append(",备注\r\n");
foreach (DataRow row in balanceDetails4Report.Data.Rows)
{
int requestId = row["OrderId"].ToInt(0);
SupplierBalanceDrawRequestInfo balanceDrawRequestInfo = BalanceHelper.GetBalanceDrawRequestInfo(requestId);
stringBuilder.Append(supplierById.SupplierName);
stringBuilder.Append("," + row["TradeDate"]);
if (balanceDrawRequestInfo != null)
{
string text = "";
text = ((!balanceDrawRequestInfo.IsWeixin) ? ((!balanceDrawRequestInfo.IsAlipay) ? $"提现到银行卡(开户银行:{balanceDrawRequestInfo.BankName}，银行开户名:{balanceDrawRequestInfo.AccountName}，银行卡帐号:{balanceDrawRequestInfo.MerchantCode})" : ("提现到支付宝(支付宝帐号:" + balanceDrawRequestInfo.AlipayCode + "，支付宝姓名:" + balanceDrawRequestInfo.AlipayRealName + ")")) : "提现到微信");
stringBuilder.Append("," + text);
}
else
{
stringBuilder.Append(",");
}
stringBuilder.Append("," + row["TradeTypeText"]);
SupplierTradeTypes supplierTradeTypes = (SupplierTradeTypes)row["TradeType"].ToInt(0);
if (supplierTradeTypes == SupplierTradeTypes.OrderBalance)
{
stringBuilder.Append("," + row["OrderId"]);
}
else
{
stringBuilder.Append(",");
}
stringBuilder.Append("," + row["Income"]);
stringBuilder.Append("," + row["Expenses"]);
stringBuilder.Append("," + row["Balance"]);
stringBuilder.Append("," + row["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();
}
private BalanceDetailSupplierQuery GetDataQuery(HttpContext context)
{
BalanceDetailSupplierQuery balanceDetailSupplierQuery = new BalanceDetailSupplierQuery();
balanceDetailSupplierQuery.SupplierId = base.GetIntParam(context, "SupplierId", false);
balanceDetailSupplierQuery.FromDate = base.GetDateTimeParam(context, "FromDate");
balanceDetailSupplierQuery.ToDate = base.GetDateTimeParam(context, "ToDate");
balanceDetailSupplierQuery.TradeType = base.GetIntParam(context, "TradeType", false).Value;
balanceDetailSupplierQuery.PageIndex = base.CurrentPageIndex;
balanceDetailSupplierQuery.PageSize = base.CurrentPageSize;
return(balanceDetailSupplierQuery);
}
private DataGridViewModel <Dictionary <string, object> > GetBalanceDetails(BalanceDetailSupplierQuery query)
{
DataGridViewModel <Dictionary <string, object> > dataGridViewModel = new DataGridViewModel <Dictionary <string, object> >();
string empty = string.Empty;
if (query != null)
{
DbQueryResult balanceDetails = BalanceHelper.GetBalanceDetails(query);
List <Dictionary <string, object> > list = DataHelper.DataTableToDictionary(balanceDetails.Data);
foreach (Dictionary <string, object> item in list)
{
SupplierBalanceDetailInfo supplierBalanceDetailInfo = item.ToObject <SupplierBalanceDetailInfo>();
if (supplierBalanceDetailInfo.TradeType == SupplierTradeTypes.DrawRequest)
{
item.Add("TradeTypeStr", "提现");
}
else
{
item.Add("TradeTypeStr", "商品交易");
}
if (string.IsNullOrEmpty(supplierBalanceDetailInfo.OrderId))
{
item.Add("OrderIdStr", "--");
}
else
{
item.Add("OrderIdStr", supplierBalanceDetailInfo.OrderId);
}
if (supplierBalanceDetailInfo.Income == decimal.Zero)
{
item.Add("IncomeStr", "--");
}
else
{
item.Add("IncomeStr", supplierBalanceDetailInfo.Income.F2ToString("f2"));
}
if (supplierBalanceDetailInfo.Expenses == decimal.Zero)
{
item.Add("ExpensesStr", "--");
}
else
{
item.Add("ExpensesStr", supplierBalanceDetailInfo.Expenses.F2ToString("f2"));
}
}
dataGridViewModel.rows = list;
dataGridViewModel.total = balanceDetails.TotalRecords;
}
return(dataGridViewModel);
}
private DataGridViewModel <Dictionary <string, object> > GetDataList(BalanceDetailSupplierQuery query)
{
DataGridViewModel <Dictionary <string, object> > dataGridViewModel = new DataGridViewModel <Dictionary <string, object> >();
if (query != null)
{
DbQueryResult balanceDetails = BalanceHelper.GetBalanceDetails(query);
dataGridViewModel.rows = new List <Dictionary <string, object> >();
dataGridViewModel.total = balanceDetails.TotalRecords;
foreach (DataRow row in balanceDetails.Data.Rows)
{
Dictionary <string, object> item = DataHelper.DataRowToDictionary(row);
dataGridViewModel.rows.Add(item);
}
}
return(dataGridViewModel);
}
public DbQueryResult GetBalanceDetails(BalanceDetailSupplierQuery query)
{
try
{
if (query == null)
{
return(new DbQueryResult());
}
DbQueryResult dbQueryResult = new DbQueryResult();
StringBuilder stringBuilder = new StringBuilder();
string text = this.BuildBalanceDetailsQuery(query);
stringBuilder.AppendFormat("SELECT TOP {0} *, isnull (Income, 0) + isnull (Expenses, 0) Amount ,CASE WHEN TradeType = 1 THEN '提现' ELSE '商品交易' END TradeTypeText ", query.PageSize);
stringBuilder.Append(" FROM Hishop_SupplierBalanceDetails B where 0=0 ");
if (query.PageIndex == 1)
{
stringBuilder.AppendFormat("{0} ORDER BY JournalNumber DESC", text);
}
else
{
stringBuilder.AppendFormat(" and JournalNumber < (select min(JournalNumber) from (select top {0} JournalNumber from Hishop_SupplierBalanceDetails where 0=0 {1} ORDER BY JournalNumber DESC ) as tbltemp) {1} ORDER BY JournalNumber DESC", (query.PageIndex - 1) * query.PageSize, text);
}
if (query.IsCount)
{
stringBuilder.AppendFormat(";select count(JournalNumber) as Total from Hishop_SupplierBalanceDetails where 0=0 {0}", text);
}
DbCommand sqlStringCommand = base.database.GetSqlStringCommand(stringBuilder.ToString());
using (IDataReader dataReader = base.database.ExecuteReader(sqlStringCommand))
{
dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(dataReader);
if (query.IsCount && dataReader.NextResult())
{
dataReader.Read();
dbQueryResult.TotalRecords = dataReader.GetInt32(0);
}
}
return(dbQueryResult);
}
catch (Exception)
{
return(new DbQueryResult());
}
}
public static DbQueryResult GetBalanceDetails4Report(BalanceDetailSupplierQuery query)
{
return(new BalanceDao().GetBalanceDetails4Report(query));
}
private BalanceDetailSupplierQuery GetBalanceDetailSupplierQuery(HttpContext context)
{
int num = 1;
int num2 = 10;
string empty = string.Empty;
DateTime?fromDate = null;
DateTime?toDate = null;
int tradeType = 0;
empty = context.Request["DateStart"];
if (!string.IsNullOrWhiteSpace(empty))
{
try
{
fromDate = DateTime.Parse(empty);
}
catch
{
fromDate = null;
}
}
empty = context.Request["DateEnd"];
if (!string.IsNullOrWhiteSpace(empty))
{
try
{
toDate = DateTime.Parse(empty);
}
catch
{
toDate = null;
}
}
empty = context.Request["TradeType"];
if (!string.IsNullOrWhiteSpace(empty))
{
try
{
tradeType = int.Parse(empty);
}
catch
{
tradeType = 0;
}
}
num = base.GetIntParam(context, "page", false).Value;
if (num < 1)
{
num = 1;
}
num2 = base.GetIntParam(context, "rows", false).Value;
if (num2 < 1)
{
num2 = 10;
}
BalanceDetailSupplierQuery balanceDetailSupplierQuery = new BalanceDetailSupplierQuery();
balanceDetailSupplierQuery.SupplierId = this.CurrentManager.StoreId;
balanceDetailSupplierQuery.FromDate = fromDate;
balanceDetailSupplierQuery.ToDate = toDate;
balanceDetailSupplierQuery.TradeType = tradeType;
balanceDetailSupplierQuery.PageSize = num2;
balanceDetailSupplierQuery.PageIndex = num;
return(balanceDetailSupplierQuery);
}
