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