public override DbQueryResult GetBalanceDetails(BalanceDetailQuery query)
{
if (null == query)
{
return new DbQueryResult();
}
DbQueryResult result = new DbQueryResult();
StringBuilder builder = new StringBuilder();
string str = BuildBalanceDetailsQuery(query);
builder.AppendFormat("SELECT TOP {0} *", query.PageSize);
builder.Append(" FROM Hishop_BalanceDetails B where 0=0 ");
if (query.PageIndex == 1)
{
builder.AppendFormat("{0} ORDER BY JournalNumber DESC", str);
}
else
{
builder.AppendFormat(" and JournalNumber < (select min(JournalNumber) from (select top {0} JournalNumber from Hishop_BalanceDetails where 0=0 {1} ORDER BY JournalNumber DESC ) as tbltemp) {1} ORDER BY JournalNumber DESC", (query.PageIndex - 1) * query.PageSize, str);
}
if (query.IsCount)
{
builder.AppendFormat(";select count(JournalNumber) as Total from Hishop_BalanceDetails where 0=0 {0}", str);
}
DbCommand sqlStringCommand = database.GetSqlStringCommand(builder.ToString());
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
if (query.IsCount && reader.NextResult())
{
reader.Read();
result.TotalRecords = reader.GetInt32(0);
}
}
return result;
}
private void btnCreateReport_Click(object sender, EventArgs e)
{
BalanceDetailQuery query = new BalanceDetailQuery();
query.UserName = userName;
query.FromDate = dateStart;
query.ToDate = dateEnd;
query.SortBy = "TradeDate";
query.SortOrder = SortAction.Desc;
query.TradeType = TradeTypes.DrawRequest;
DbQueryResult balanceDetailsNoPage = MemberHelper.GetBalanceDetailsNoPage(query);
string s = string.Empty + "用户名,交易时间,业务摘要,转出金额,当前余额\r\n";
foreach (DataRow row in ((DataTable)balanceDetailsNoPage.Data).Rows)
{
s = s + row["UserName"];
s = s + "," + row["TradeDate"];
s = s + ",提现";
s = s + "," + row["Expenses"];
object obj2 = s;
s = string.Concat(new object[] { obj2, ",", row["Balance"], "\r\n" });
}
Page.Response.Clear();
Page.Response.Buffer = false;
Page.Response.Charset = "GB2312";
Page.Response.AppendHeader("Content-Disposition", "attachment;filename=BalanceDetailsStatistics.csv");
Page.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Page.Response.ContentType = "application/octet-stream";
Page.EnableViewState = false;
Page.Response.Write(s);
Page.Response.End();
}
public void GetBalanceDrawRequest()
{
BalanceDetailQuery query = new BalanceDetailQuery();
query.UserName = userName;
query.FromDate = dateStart;
query.ToDate = dateEnd;
query.PageIndex = pager.PageIndex;
query.TradeType = TradeTypes.DrawRequest;
DbQueryResult balanceDetails = MemberHelper.GetBalanceDetails(query);
grdBalanceDrawRequest.DataSource = balanceDetails.Data;
grdBalanceDrawRequest.DataBind();
pager1.TotalRecords = pager.TotalRecords = balanceDetails.TotalRecords;
}
private void GetBalanceDetails()
{
BalanceDetailQuery query = new BalanceDetailQuery();
query.FromDate = dataStart;
query.ToDate = dataEnd;
query.PageIndex = pager.PageIndex;
query.PageSize = pager.PageSize;
DbQueryResult myBalanceDetails = SubsiteStoreHelper.GetMyBalanceDetails(query);
grdBalanceDetails.DataSource = myBalanceDetails.Data;
grdBalanceDetails.DataBind();
pager.TotalRecords = myBalanceDetails.TotalRecords;
pager1.TotalRecords = myBalanceDetails.TotalRecords;
}
private void GetBalanceDetails()
{
BalanceDetailQuery query = new BalanceDetailQuery();
query.FromDate = dataStart;
query.ToDate = dataEnd;
query.UserId = new int?(userId);
query.PageIndex = pager.PageIndex;
query.PageSize = pager.PageSize;
query.TradeType = tradeType;
DbQueryResult distributorBalanceDetails = DistributorHelper.GetDistributorBalanceDetails(query);
grdBalanceDetails.DataSource = distributorBalanceDetails.Data;
grdBalanceDetails.DataBind();
pager.TotalRecords = distributorBalanceDetails.TotalRecords;
pager1.TotalRecords = distributorBalanceDetails.TotalRecords;
}
private void BindBalanceDetails()
{
BalanceDetailQuery query = new BalanceDetailQuery();
query.FromDate = dataStart;
query.ToDate = dataEnd;
query.TradeType = (TradeTypes)typeId;
query.PageIndex = pager.PageIndex;
query.UserId = new int?(userId);
query.PageSize = pager.PageSize;
DbQueryResult balanceDetails = UnderlingHelper.GetBalanceDetails(query);
grdBalanceDetails.DataSource = balanceDetails.Data;
grdBalanceDetails.DataBind();
pager.TotalRecords = balanceDetails.TotalRecords;
pager1.TotalRecords = balanceDetails.TotalRecords;
}
BalanceDetailQuery GetBalanceDetailQuery()
{
BalanceDetailQuery query = new BalanceDetailQuery();
query.UserId = new int?(HiContext.Current.User.UserId);
if (!string.IsNullOrEmpty(this.Page.Request.QueryString["dataStart"]))
{
query.FromDate = new DateTime?(Convert.ToDateTime(this.Page.Server.UrlDecode(this.Page.Request.QueryString["dataStart"])));
}
if (!string.IsNullOrEmpty(this.Page.Request.QueryString["dataEnd"]))
{
query.ToDate = new DateTime?(Convert.ToDateTime(this.Page.Server.UrlDecode(this.Page.Request.QueryString["dataEnd"])));
}
if (!string.IsNullOrEmpty(this.Page.Request.QueryString["tradeType"]))
{
query.TradeType = (TradeTypes) Convert.ToInt32(this.Page.Server.UrlDecode(this.Page.Request.QueryString["tradeType"]));
}
query.PageIndex = this.pager.PageIndex;
query.PageSize = this.pager.PageSize;
return query;
}
public abstract DbQueryResult GetMyBalanceDetails(BalanceDetailQuery query);
private void GetBalanceDetails()
{
BalanceDetailQuery query = new BalanceDetailQuery();
query.FromDate = dateStart;
query.ToDate = dateEnd;
query.PageIndex = pager.PageIndex;
query.PageSize = pager.PageSize;
query.SortBy = "TradeDate";
query.SortOrder = SortAction.Desc;
query.UserName = userName;
DbQueryResult balanceDetails = MemberHelper.GetBalanceDetails(query);
grdBalanceDetails.DataSource = balanceDetails.Data;
grdBalanceDetails.DataBind();
pager1.TotalRecords = pager.TotalRecords = balanceDetails.TotalRecords;
}
static string BuildBalanceDetailsQuery(BalanceDetailQuery query)
{
StringBuilder builder = new StringBuilder();
if (query.UserId.HasValue)
{
builder.AppendFormat(" AND UserId = {0}", query.UserId.Value);
}
if (!string.IsNullOrEmpty(query.UserName))
{
builder.AppendFormat(" AND UserId IN (SELECT UserId FROM vw_aspnet_Members WHERE UserName='******')", DataHelper.CleanSearchString(query.UserName));
}
if (query.FromDate.HasValue)
{
builder.AppendFormat(" AND TradeDate >= '{0}'", DataHelper.GetSafeDateTimeFormat(query.FromDate.Value));
}
if (query.ToDate.HasValue)
{
builder.AppendFormat(" AND TradeDate <= '{0}'", DataHelper.GetSafeDateTimeFormat(query.ToDate.Value));
}
if (query.TradeType != TradeTypes.NotSet)
{
builder.AppendFormat(" AND TradeType = {0}", (int)query.TradeType);
}
return builder.ToString();
}
static string BuildBalanceDetailsQuery(BalanceDetailQuery query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(" AND DistributorUserId = {0}", HiContext.Current.User.UserId);
if (query.UserId.HasValue)
{
builder.AppendFormat(" AND UserId = {0}", query.UserId.Value);
}
if (!string.IsNullOrEmpty(query.UserName))
{
builder.AppendFormat(" AND UserName='******'", DataHelper.CleanSearchString(query.UserName));
}
if (query.FromDate.HasValue)
{
builder.AppendFormat(" AND TradeDate >= '{0}'", DataHelper.GetSafeDateTimeFormat(query.FromDate.Value));
}
if (query.ToDate.HasValue)
{
builder.AppendFormat(" AND TradeDate <= '{0}'", DataHelper.GetSafeDateTimeFormat(query.ToDate.Value));
}
if (query.TradeType != TradeTypes.NotSet)
{
builder.AppendFormat(" AND TradeType = {0}", (int)query.TradeType);
}
return builder.ToString();
}
public static DbQueryResult GetBalanceDetails(BalanceDetailQuery query)
{
return PersonalProvider.Instance().GetBalanceDetails(query);
}
public abstract DbQueryResult GetDistributorBalanceDetails(BalanceDetailQuery query);
static string BuildBalanceDetailsQuery(BalanceDetailQuery query)
{
IUser user = HiContext.Current.User;
StringBuilder builder = new StringBuilder();
builder.AppendFormat(" AND UserId = {0}", user.UserId);
if (query.FromDate.HasValue)
{
builder.AppendFormat(" AND TradeDate >= '{0}'", DataHelper.GetSafeDateTimeFormat(query.FromDate.Value));
}
if (query.ToDate.HasValue)
{
builder.AppendFormat(" AND TradeDate <= '{0}'", DataHelper.GetSafeDateTimeFormat(query.ToDate.Value));
}
return builder.ToString();
}
public static DbQueryResult GetDistributorBalanceDetails(BalanceDetailQuery query)
{
return DistributorProvider.Instance().GetDistributorBalanceDetails(query);
}
static string BuildBalanceDetailsQuery(BalanceDetailQuery query)
{
StringBuilder builder = new StringBuilder();
if (query.UserId.HasValue)
{
builder.AppendFormat(" AND UserId = {0}", query.UserId.Value);
}
if (query.TradeType != TradeTypes.NotSet)
{
builder.AppendFormat(" AND TradeType = {0}", (int)query.TradeType);
}
if (query.FromDate.HasValue)
{
builder.AppendFormat(" AND TradeDate >= '{0}'", DataHelper.GetSafeDateTimeFormat(query.FromDate.Value));
}
if (query.ToDate.HasValue)
{
builder.AppendFormat(" AND TradeDate <= '{0}'", DataHelper.GetSafeDateTimeFormat(query.ToDate.Value));
}
return builder.ToString();
}
public static DbQueryResult GetBalanceDetailsNoPage(BalanceDetailQuery query)
{
return MemberProvider.Instance().GetBalanceDetailsNoPage(query);
}
public override DbQueryResult GetBalanceDetails(BalanceDetailQuery query)
{
if (null == query)
{
return null;
}
DbQueryResult result = new DbQueryResult();
StringBuilder builder = new StringBuilder();
string str = BuildUnderlingBalanceDetailsQuery(query);
builder.AppendFormat("SELECT TOP {0} * FROM distro_BalanceDetails B WHERE 0=0", query.PageSize);
if (query.PageIndex == 1)
{
builder.AppendFormat(" {0} ORDER BY JournalNumber DESC;", str);
}
else
{
builder.AppendFormat(" AND JournalNumber < (SELECT MIN(JournalNumber) FROM (SELECT TOP {0} JournalNumber FROM distro_BalanceDetails WHERE 0=0 {1} ORDER BY JournalNumber DESC ) AS T) {1} ORDER BY JournalNumber DESC;", (query.PageIndex - 1) * query.PageSize, str);
}
if (query.IsCount)
{
builder.AppendFormat(" SELECT COUNT(JournalNumber) AS Total from distro_BalanceDetails WHERE 0=0 {0}", str);
}
DbCommand sqlStringCommand = database.GetSqlStringCommand(builder.ToString());
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
if (query.IsCount && reader.NextResult())
{
reader.Read();
result.TotalRecords = reader.GetInt32(0);
}
}
return result;
}
public static DbQueryResult GetBalanceDetails(BalanceDetailQuery query)
{
return UnderlingProvider.Instance().GetBalanceDetails(query);
}
public static DbQueryResult GetMyBalanceDetails(BalanceDetailQuery query)
{
return SubsiteStoreProvider.Instance().GetMyBalanceDetails(query);
}
public abstract DbQueryResult GetBalanceDetailsNoPage(BalanceDetailQuery query);
public override DbQueryResult GetBalanceDetailsNoPage(BalanceDetailQuery query)
{
if (null == query)
{
return new DbQueryResult();
}
DbQueryResult result = new DbQueryResult();
StringBuilder builder = new StringBuilder();
string str = BuildBalanceDetailsQuery(query);
builder.Append("SELECT * FROM Hishop_BalanceDetails WHERE 0=0 ");
builder.AppendFormat("{0} ORDER BY JournalNumber DESC", str);
if (query.IsCount)
{
builder.AppendFormat(";select count(JournalNumber) as Total from Hishop_BalanceDetails where 0=0 {0}", str);
}
DbCommand sqlStringCommand = database.GetSqlStringCommand(builder.ToString());
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
if (query.IsCount && reader.NextResult())
{
reader.Read();
result.TotalRecords = reader.GetInt32(0);
}
}
return result;
}