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;
}
void BindList()
{
HelpQuery helpQuery = this.GetHelpQuery();
DbQueryResult helpList = new DbQueryResult();
helpList = CommentBrowser.GetHelpList(helpQuery);
this.rptHelps.DataSource = helpList.Data;
this.rptHelps.DataBind();
this.pager.TotalRecords = helpList.TotalRecords;
}
public override DbQueryResult GetLeaveComments(LeaveCommentQuery query)
{
StringBuilder builder = new StringBuilder();
builder.Append(" SELECT l.LeaveId FROM Hishop_LeaveComments l ");
builder.Append(" WHERE IsReply = 1 ");
builder.Append(" ORDER BY LastDate desc");
DbQueryResult result = new DbQueryResult();
DbCommand storedProcCommand = this.database.GetStoredProcCommand("ss_LeaveComments_Get");
this.database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, query.PageIndex);
this.database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, query.PageSize);
this.database.AddInParameter(storedProcCommand, "IsCount", DbType.Boolean, query.IsCount);
this.database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, builder.ToString());
this.database.AddOutParameter(storedProcCommand, "Total", DbType.Int32, 4);
DataSet set = this.database.ExecuteDataSet(storedProcCommand);
set.Relations.Add("LeaveCommentReplays", set.Tables[0].Columns["LeaveId"], set.Tables[1].Columns["LeaveId"], false);
result.Data = set;
result.TotalRecords = (int) this.database.GetParameterValue(storedProcCommand, "Total");
return result;
}
public override DbQueryResult GetOrderGifts(OrderGiftQuery query)
{
DbQueryResult result = new DbQueryResult();
StringBuilder builder = new StringBuilder();
builder.AppendFormat("select top {0} * from Hishop_OrderGifts where OrderId=@OrderId", query.PageSize);
if (query.PageIndex == 1)
{
builder.Append(" ORDER BY GiftId ASC");
}
else
{
builder.AppendFormat(" and GiftId > (select max(GiftId) from (select top {0} GiftId from Hishop_OrderGifts where 0=0 and OrderId=@OrderId ORDER BY GiftId ASC ) as tbltemp) ORDER BY GiftId ASC", (query.PageIndex - 1) * query.PageSize);
}
if (query.IsCount)
{
builder.AppendFormat(";select count(GiftId) as Total from Hishop_OrderGifts where OrderId=@OrderId", new object[0]);
}
DbCommand sqlStringCommand = database.GetSqlStringCommand(builder.ToString());
database.AddInParameter(sqlStringCommand, "OrderId", DbType.String, query.OrderId);
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 override DbQueryResult GetSaleTargets()
{
DbQueryResult result = new DbQueryResult();
string query = string.Empty;
query = string.Format("select (select Count(OrderId) from Hishop_orders) as OrderNumb ,ISNULL((select sum(OrderTotal) from hishop_orders),0) as OrderPrice, (select COUNT(*) from vw_aspnet_Members) as UserNumb, (select count(*) from vw_aspnet_Members where UserID in (select userid from Hishop_orders)) as UserOrderedNumb, ISNULL((select sum(VistiCounts) from Hishop_products),0) as ProductVisitNumb ", new object[0]);
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
}
return result;
}
public override DbQueryResult GetSaleOrderLineItemsStatisticsNoPage(SaleStatisticsQuery query)
{
DbQueryResult result = new DbQueryResult();
DbCommand sqlStringCommand = database.GetSqlStringCommand("SELECT * FROM vw_Hishop_SaleDetails WHERE 1=1");
if (query.StartDate.HasValue)
{
sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format(" AND OrderDate >= '{0}'", query.StartDate);
}
if (query.EndDate.HasValue)
{
sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format(" AND OrderDate <= '{0}'", query.EndDate);
}
sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format("AND OrderStatus != {0} AND OrderStatus != {1}", 1, 4);
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
}
return result;
}
public override DbQueryResult GetPrintTasks(Pagination query)
{
DbQueryResult result = new DbQueryResult();
database = DatabaseFactory.CreateDatabase();
DbCommand storedProcCommand = database.GetStoredProcCommand("sp_PrintTasks_Get");
database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, query.PageIndex);
database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, query.PageSize);
database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, "SELECT taskId FROM Hishop_PrintTask WHERE 1 = 1 ORDER BY taskId DESC");
using (IDataReader reader = database.ExecuteReader(storedProcCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
if (reader.NextResult())
{
reader.Read();
result.TotalRecords = reader.GetInt32(0);
}
}
return result;
}
public override DbQueryResult GetSendedMessagesForReceivedMessage(long receiveMessageId)
{
DataTable table;
DbQueryResult result = new DbQueryResult();
DbCommand sqlStringCommand = database.GetSqlStringCommand("SELECT * from Hishop_SendedMessages WHERE ReceiveMessageId=@ReceiveMessageId ORDER BY PublishDate DESC");
database.AddInParameter(sqlStringCommand, "ReceiveMessageId", DbType.Int64, receiveMessageId);
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
table = DataHelper.ConverDataReaderToDataTable(reader);
}
result.Data = table;
result.TotalRecords = table.Rows.Count;
return result;
}
public override DbQueryResult GetFavorites(int userId, string tags, Pagination page)
{
DbQueryResult result = new DbQueryResult();
DbCommand storedProcCommand = database.GetStoredProcCommand("ac_Member_Favorites_Get");
database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, page.PageIndex);
database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, page.PageSize);
database.AddInParameter(storedProcCommand, "IsCount", DbType.Boolean, page.IsCount);
Member user = HiContext.Current.User as Member;
database.AddInParameter(storedProcCommand, "GradeId", DbType.Int32, user.GradeId);
database.AddInParameter(storedProcCommand, "SqlPopulate", DbType.String, BuildFavoriteQuery(userId, tags));
database.AddOutParameter(storedProcCommand, "TotalFavorites", DbType.Int32, 4);
using (IDataReader reader = database.ExecuteReader(storedProcCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
if (page.IsCount && reader.NextResult())
{
reader.Read();
result.TotalRecords = reader.GetInt32(0);
}
}
return result;
}
public static DbQueryResult PagingByTopsort(int pageIndex, int pageSize, string sortBy, SortAction sortOrder, bool isCount, string table, string pk, string filter, string selectFields)
{
if (string.IsNullOrEmpty(table))
{
return null;
}
if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(pk))
{
return null;
}
if (string.IsNullOrEmpty(selectFields))
{
selectFields = "*";
}
string query = BuildTopQuery(pageIndex, pageSize, sortBy, sortOrder, isCount, table, pk, filter, selectFields);
DbQueryResult result = new DbQueryResult();
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
result.Data = ConverDataReaderToDataTable(reader);
if (isCount && reader.NextResult())
{
reader.Read();
result.TotalRecords = reader.GetInt32(0);
}
}
return result;
}
public static DbQueryResult PagingByRownumber(int pageIndex, int pageSize, string sortBy, SortAction sortOrder, bool isCount, string table, string pk, string filter, string selectFields, int partitionSize)
{
if (string.IsNullOrEmpty(table))
{
return null;
}
if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(pk))
{
return null;
}
if (string.IsNullOrEmpty(selectFields))
{
selectFields = "*";
}
string query = BuildRownumberQuery(sortBy, sortOrder, isCount, table, pk, filter, selectFields, partitionSize);
int num = ((pageIndex - 1) * pageSize) + 1;
int num2 = (num + pageSize) - 1;
DbQueryResult result = new DbQueryResult();
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
database.AddInParameter(sqlStringCommand, "StartNumber", DbType.Int32, num);
database.AddInParameter(sqlStringCommand, "EndNumber", DbType.Int32, num2);
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
result.Data = ConverDataReaderToDataTable(reader);
if ((isCount && (partitionSize == 0)) && reader.NextResult())
{
reader.Read();
result.TotalRecords = reader.GetInt32(0);
}
}
return result;
}
public override DbQueryResult GetDistributorBalanceDrawRequests(BalanceDrawRequestQuery query)
{
if (null == query)
{
return new DbQueryResult();
}
DbQueryResult result = new DbQueryResult();
StringBuilder builder = new StringBuilder();
string str = BuildBalanceDrawRequestQuery(query);
builder.AppendFormat("select top {0} *", query.PageSize);
builder.Append(" from Hishop_DistributorBalanceDrawRequest B where 0=0 ");
if (query.PageIndex == 1)
{
builder.AppendFormat("{0} ORDER BY RequestTime DESC", str);
}
else
{
builder.AppendFormat(" and RequestTime < (select min(RequestTime) from (select top {0} RequestTime from Hishop_DistributorBalanceDrawRequest where 0=0 {1} ORDER BY RequestTime DESC ) as tbltemp) {1} ORDER BY RequestTime DESC", (query.PageIndex - 1) * query.PageSize, str);
}
if (query.IsCount)
{
builder.AppendFormat(";select count(*) as Total from Hishop_DistributorBalanceDrawRequest 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 override DbQueryResult GetDistributorBalance(DistributorQuery query)
{
if (null == query)
{
return new DbQueryResult();
}
DbQueryResult result = new DbQueryResult();
StringBuilder builder = new StringBuilder();
string str = string.Empty;
if (!string.IsNullOrEmpty(query.Username))
{
str = string.Format("AND UserName LIKE '%{0}%'", DataHelper.CleanSearchString(query.Username));
}
if (!string.IsNullOrEmpty(query.RealName))
{
str = str + string.Format(" AND RealName LIKE '%{0}%'", DataHelper.CleanSearchString(query.RealName));
}
builder.AppendFormat("SELECT TOP {0} *", query.PageSize);
builder.Append(" FROM vw_aspnet_Distributors WHERE IsApproved = 1");
if (query.PageIndex == 1)
{
builder.AppendFormat(" {0} ORDER BY CreateDate DESC", str);
}
else
{
builder.AppendFormat(" AND CreateDate < (select min(CreateDate) FROM (SELECT TOP {0} CreateDate FROM vw_aspnet_Distributors WHERE IsApproved = 1 {1} ORDER BY CreateDate DESC ) AS tbltemp) {1} ORDER BY CreateDate DESC", (query.PageIndex - 1) * query.PageSize, str);
}
if (query.IsCount)
{
builder.AppendFormat(" ;SELECT COUNT(CreateDate) AS Total FROM vw_aspnet_Distributors 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 DbQueryResult GetSaleTargets()
{
DbQueryResult result = new DbQueryResult();
string query = string.Empty;
query = string.Format("select (select Count(OrderId) from Hishop_orders WHERE OrderStatus != {0} AND OrderStatus != {1} AND OrderStatus != {2}) as OrderNumb,", 1, 4, 9) + string.Format("(select isnull(sum(OrderTotal),0) - isnull(sum(RefundAmount),0) from hishop_orders WHERE OrderStatus != {0} AND OrderStatus != {1} AND OrderStatus != {2}) as OrderPrice, ", 1, 4, 9) + " (select COUNT(*) from aspnet_Members) as UserNumb, (select count(*) from aspnet_Members where UserID in (select userid from Hishop_orders)) as UserOrderedNumb, ISNULL((select sum(VistiCounts) from Hishop_products),0) as ProductVisitNumb ";
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
}
return result;
}
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 override DbQueryResult GetConsultationProducts(ProductConsultationAndReplyQuery consultationQuery)
{
DbQueryResult result = new DbQueryResult();
DbCommand storedProcCommand = database.GetStoredProcCommand("cp_ProductConsultation_Get");
database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, consultationQuery.PageIndex);
database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, consultationQuery.PageSize);
database.AddInParameter(storedProcCommand, "IsCount", DbType.Boolean, consultationQuery.IsCount);
if (consultationQuery.CategoryId.HasValue)
{
database.AddInParameter(storedProcCommand, "CategoryId", DbType.Int32, consultationQuery.CategoryId.Value);
}
database.AddInParameter(storedProcCommand, "SqlPopulate", DbType.String, BuildProductConsultationQuery(consultationQuery));
using (IDataReader reader = database.ExecuteReader(storedProcCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
if (consultationQuery.IsCount && reader.NextResult())
{
reader.Read();
result.TotalRecords = reader.GetInt32(0);
}
}
return result;
}
public override DbQueryResult GetLeaveComments(LeaveCommentQuery query)
{
DbQueryResult result = new DbQueryResult();
DbCommand storedProcCommand = database.GetStoredProcCommand("cp_LeaveComments_Get");
database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, query.PageIndex);
database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, query.PageSize);
database.AddInParameter(storedProcCommand, "IsCount", DbType.Boolean, query.IsCount);
database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, BuildLeaveCommentQuery(query));
database.AddOutParameter(storedProcCommand, "Total", DbType.Int32, 4);
DataSet set = database.ExecuteDataSet(storedProcCommand);
set.Relations.Add("LeaveCommentReplays", set.Tables[0].Columns["LeaveId"], set.Tables[1].Columns["LeaveId"], false);
result.Data = set;
result.TotalRecords = (int)database.GetParameterValue(storedProcCommand, "Total");
return result;
}
public override DbQueryResult GetSaleTargets()
{
DbQueryResult result = new DbQueryResult();
string query = string.Empty;
query = string.Format("select " + string.Format(" (select Count(OrderId) from distro_orders where DistributorUserId={0}) as OrderNumb ,", HiContext.Current.User.UserId) + string.Format("ISNULL((select sum(OrderTotal) from distro_orders where DistributorUserId={0}),0) as OrderPrice, ", HiContext.Current.User.UserId) + string.Format(" (select COUNT(*) from vw_distro_Members where ParentUserId={0}) as UserNumb, ", HiContext.Current.User.UserId) + string.Format(" (select count(*) from vw_distro_Members where UserID in (select userid from distro_orders where DistributorUserId={0})) as UserOrderedNumb, ", HiContext.Current.User.UserId) + string.Format(" ISNULL((select sum(VistiCounts) from distro_products where DistributorUserId={0}),0) as ProductVisitNumb ", HiContext.Current.User.UserId), new object[0]);
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
using (IDataReader reader = database.ExecuteReader(sqlStringCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
}
return result;
}
public override DbQueryResult GetOrders(OrderQuery query)
{
DbCommand storedProcCommand = database.GetStoredProcCommand("cp_Orders_Get");
database.AddInParameter(storedProcCommand, "PageIndex", DbType.Int32, query.PageIndex);
database.AddInParameter(storedProcCommand, "PageSize", DbType.Int32, query.PageSize);
database.AddInParameter(storedProcCommand, "IsCount", DbType.Boolean, query.IsCount);
database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, BuildOrdersQuery(query));
database.AddOutParameter(storedProcCommand, "TotalOrders", DbType.Int32, 4);
DbQueryResult result = new DbQueryResult();
using (IDataReader reader = database.ExecuteReader(storedProcCommand))
{
result.Data = DataHelper.ConverDataReaderToDataTable(reader);
}
result.TotalRecords = (int)database.GetParameterValue(storedProcCommand, "TotalOrders");
return result;
}
public override DbQueryResult GetBalanceDrawRequestsNoPage(BalanceDrawRequestQuery query)
{
if (null == query)
{
return new DbQueryResult();
}
DbQueryResult result = new DbQueryResult();
StringBuilder builder = new StringBuilder();
string str = BuildBalanceDrawRequestQuery(query);
builder.Append("select *");
builder.Append(" from Hishop_BalanceDrawRequest B where 0=0 ");
builder.AppendFormat("{0} ORDER BY RequestTime DESC", str);
if (query.IsCount)
{
builder.AppendFormat(";select count(*) as Total from Hishop_BalanceDrawRequest 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;
}