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