private string BuildLeaveCommentQuery(LeaveCommentQuery query)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" SELECT l.LeaveId FROM Ecshop_LeaveComments l where 0=0");
if (query.MessageStatus == MessageStatus.Replied)
{
stringBuilder.Append(" and (select Count(ReplyId) from Ecshop_LeaveCommentReplys where LeaveId=l.LeaveId) >0 ");
}
if (query.MessageStatus == MessageStatus.NoReply)
{
stringBuilder.Append(" and (select Count(ReplyId) from Ecshop_LeaveCommentReplys where LeaveId=l.LeaveId) <=0 ");
}
if (query.FeedbackType.HasValue)
{
stringBuilder.AppendFormat(" and FeedbackType = {0}", query.FeedbackType.Value);
}
if (!string.IsNullOrEmpty(query.SortBy))
{
stringBuilder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), query.SortOrder.ToString());
}
else
{
stringBuilder.Append(" ORDER BY LastDate desc");
}
return(stringBuilder.ToString());
}
private void BindList()
{
LeaveCommentQuery query = new LeaveCommentQuery();
query.PageSize = 3;
query.PageIndex = this.pager.PageIndex;
DbQueryResult leaveComments = CommentBrowser.GetLeaveComments(query);
this.rptLeaveComments.DataSource = leaveComments.Data;
this.rptLeaveComments.DataBind();
this.pager.TotalRecords = (int)(leaveComments.TotalRecords * (Convert.ToDouble(this.pager.PageSize) / 3.0));
}
private void BindList()
{
LeaveCommentQuery leaveCommentQuery = new LeaveCommentQuery();
leaveCommentQuery.PageIndex = this.pager.PageIndex;
if (!string.IsNullOrEmpty(base.Request.QueryString["MessageStatus"]))
{
leaveCommentQuery.MessageStatus = (MessageStatus)int.Parse(base.Request.QueryString["MessageStatus"]);
this.statusList.SelectedValue = leaveCommentQuery.MessageStatus;
}
DbQueryResult leaveComments = SubsiteCommentsHelper.GetLeaveComments(leaveCommentQuery);
this.leaveList.DataSource = leaveComments.Data;
this.leaveList.DataBind();
this.pager.TotalRecords = leaveComments.TotalRecords;
this.pager1.TotalRecords = leaveComments.TotalRecords;
}
public override DbQueryResult GetLeaveComments(LeaveCommentQuery query)
{
DbQueryResult result = new DbQueryResult();
DbCommand storedProcCommand = this.database.GetStoredProcCommand("sub_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, BuildLeaveCommentQuery(query));
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);
}
private void BindList()
{
LeaveCommentQuery query = new LeaveCommentQuery();
query.PageIndex = pager.PageIndex;
query.PageSize = pager.PageSize;
if (!string.IsNullOrEmpty(base.Request.QueryString["MessageStatus"]))
{
query.MessageStatus = (MessageStatus)int.Parse(base.Request.QueryString["MessageStatus"]);
statusList.SelectedValue = query.MessageStatus;
}
DbQueryResult leaveComments = NoticeHelper.GetLeaveComments(query);
leaveList.DataSource = leaveComments.Data;
leaveList.DataBind();
pager.TotalRecords = leaveComments.TotalRecords;
pager1.TotalRecords = leaveComments.TotalRecords;
}
public override DbQueryResult GetLeaveComments(LeaveCommentQuery query)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" SELECT l.LeaveId FROM distro_LeaveComments l ");
stringBuilder.AppendFormat(" WHERE IsReply = 1 AND DistributorUserId = {0}", HiContext.Current.SiteSettings.UserId.Value);
stringBuilder.Append(" ORDER BY LastDate desc");
DbQueryResult dbQueryResult = new DbQueryResult();
System.Data.Common.DbCommand storedProcCommand = this.database.GetStoredProcCommand("ss_distro_LeaveComments_Get");
this.database.AddInParameter(storedProcCommand, "PageIndex", System.Data.DbType.Int32, query.PageIndex);
this.database.AddInParameter(storedProcCommand, "PageSize", System.Data.DbType.Int32, query.PageSize);
this.database.AddInParameter(storedProcCommand, "IsCount", System.Data.DbType.Boolean, query.IsCount);
this.database.AddInParameter(storedProcCommand, "sqlPopulate", System.Data.DbType.String, stringBuilder.ToString());
this.database.AddOutParameter(storedProcCommand, "Total", System.Data.DbType.Int32, 4);
System.Data.DataSet dataSet = this.database.ExecuteDataSet(storedProcCommand);
dataSet.Relations.Add("LeaveCommentReplays", dataSet.Tables[0].Columns["LeaveId"], dataSet.Tables[1].Columns["LeaveId"], false);
dbQueryResult.Data = dataSet;
dbQueryResult.TotalRecords = (int)this.database.GetParameterValue(storedProcCommand, "Total");
return(dbQueryResult);
}
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);
}
private static string BuildLeaveCommentQuery(LeaveCommentQuery query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(" SELECT l.LeaveId FROM distro_LeaveComments l where l.DistributorUserId={0}", HiContext.Current.User.UserId);
if (query.MessageStatus == MessageStatus.Replied)
{
builder.Append(" and (select Count(ReplyId) from distro_LeaveCommentReplys where LeaveId=l.LeaveId) >0 ");
}
if (query.MessageStatus == MessageStatus.NoReply)
{
builder.Append(" and (select Count(ReplyId) from distro_LeaveCommentReplys where LeaveId=l.LeaveId) <=0 ");
}
if (!string.IsNullOrEmpty(query.SortBy))
{
builder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), query.SortOrder.ToString());
}
else
{
builder.Append(" ORDER BY LastDate desc");
}
return(builder.ToString());
}
public abstract DbQueryResult GetLeaveComments(LeaveCommentQuery query);
public static DbQueryResult GetLeaveComments(LeaveCommentQuery query)
{
return(CommentProvider.Instance().GetLeaveComments(query));
}
public static DbQueryResult GetLeaveComments(LeaveCommentQuery query)
{
return(new LeaveCommentDao().GetLeaveComments(query));
}
