public DataSet GetDetail(long id, long userid, bool searchMyJoinItem)
{
//return new SqlQuickBuild(@"select
// a.*,
// (select count(1) from ActivityJoin where ActivityId=a.ActivityId and IsFeed=1) JoinCount
// from Activity a
// where a.ActivityId=@id and a.IsChecked=2;
// select * from ActivityFee b where b.ActivityId=@id;
// select ItemName,JoinItemQuestionExtId from JoinItemQuestionExt c where c.MainID=@id and c.MainType=1")
// .AddParams("@id", SqlDbType.BigInt, id)
// .Query();
var sql = new SqlQuickBuild();
StringBuilder sb = new StringBuilder(@"select
a.*,
(select count(1) from ActivityJoin where ActivityId = a.ActivityId and IsFeed = 1) JoinCount
from Activity a
where a.ActivityId = @id and a.IsChecked = 2;
select * from ActivityFee b where b.ActivityId = @id;
select ItemName, JoinItemQuestionExtId from JoinItemQuestionExt c where c.MainID = @id and c.MainType = 1;");
if (searchMyJoinItem)
{
sb.Append(@"select b.*,a.JoinCount from ActivityJoin a
left join ActivityFee b on b.ActivityFeeId=a.ActivityFeeId
where a.JoinUserID = @userid and a.ActivityId = @id and a.IsFeed=1;");
sql.AddParams("@userid", SqlDbType.BigInt, userid);
}
sql.AddParams("@id", SqlDbType.BigInt, id);
sql.Cmd = sb.ToString();
return(sql.Query());
}
public DataSet SearchQuestion(int startIndex, int endIndex, string keywords)
{
StringBuilder sb = new StringBuilder();
var sql = new SqlQuickBuild();
sb.Append(@" --计算总量
select count(1) from Question a
left join UserBase b on b.UserID=a.UserID
where a.IsDelete=0 and a.IsChecked=2 and (a.Title like @key or a.Body like @key or b.UserName like @key);
select * from (
select ROW_NUMBER() over(order by a.createtime desc) rid,
b.UserName,
isnull(b.HeadUrl,'/Content/img/head_default.gif') HeadUrl,
(select count(1) from UserLike ul where ul.LikeTargetId=a.QuestionId and ul.LikeType=1 and ul.IsDelete=0) LikeCount,
(select count(1) from Comment aa where aa.MainID=a.QuestionId and aa.IsDelete=0) CommentCount,
(select count(1) from [Master] _master where _master.UserID=a.UserID and _master.IsDelete=0) IsMaster,--是否管理员
a.*,
c.VIP,c.VIPExpiryTime,c.TotalScore,
(select EnumDesc from BBSEnum where c.LevelName=BBSEnumId and EnumType=3) LevelName,
(select EnumDesc from BBSEnum where c.OnlyLevelName=BBSEnumId and EnumType=4) OnlyLevelName,
c.HeadNameShowType,c.UserV
from Question a
left join UserBase b on a.UserID=b.UserID
left join UserExt c on c.UserID=b.UserID
where a.IsDelete=0 and a.IsChecked=2 and (a.Title like @key or a.Body like @key or b.UserName like @key) and b.IsDelete=0) T where T.rid BETWEEN @startindex and @endindex");
sql.AddParams("@key", SqlDbType.VarChar, "%{0}%".FormatWith(keywords));
sql.Cmd = sb.ToString();
return
(sql.AddParams("@startindex", SqlDbType.Int, startIndex)
.AddParams("@endindex", SqlDbType.Int, endIndex)
.Query());
}
public DataSet GetLikesByUserID(long userID, int type, int startindex = 0, int endindex = 0)
{
StringBuilder sb = new StringBuilder();
string pageSql = string.Empty;
string pageSqlEnd = string.Empty;
SqlQuickBuild sqlExe = new SqlQuickBuild();
if (startindex > 0)
{
pageSql = " select * from ( select ROW_NUMBER() over(order by a.LikeTime desc) rid,";
pageSqlEnd = " ) T where T.rid BETWEEN @startindex and @endindex;";
sqlExe.AddParams("@startindex", SqlDbType.Int, startindex)
.AddParams("@endindex", SqlDbType.Int, endindex);
}
if (type == 1)
{
sb.Append(@"
select count(1) from UserLike a
left join Question b on a.LikeTargetID=b.QuestionId
where a.UserID=@userid and a.IsDelete=0 and a.LikeType=1 and b.IsDelete=0;
{0}
b.QuestionId,b.Title BeLikedName,a.LikeType from UserLike a
left join Question b on a.LikeTargetID=b.QuestionId
where a.UserID=@userid and a.IsDelete=0 and a.LikeType=1 and b.IsDelete=0
{1}");
}
else if (type == 2)
{
sb.Append(@"
select count(1) from UserLike a
left join Article b on a.LikeTargetID=b.ArticleId
where a.UserID=@userid and a.IsDelete=0 and a.LikeType=2 and b.IsDelete=0;
{0}
b.ArticleId,b.Title BeLikedName,a.LikeType from UserLike a
left join Article b on a.LikeTargetID=b.ArticleId
where a.UserID=@userid and a.IsDelete=0 and a.LikeType=2 and b.IsDelete=0
{1}");
}
else if (type == 3)
{
sb.Append(@"
select count(1) from UserLike a
left join UserBase b on b.UserID=a.LikeTargetID
where a.UserID=@userid and a.IsDelete=0 and a.LikeType=3 and b.IsDelete=0;
{0}
b.UserName BeLikedName,b.UserID BeLikedUserID,a.LikeType from UserLike a
left join UserBase b on a.LikeTargetID=b.UserID
where a.UserID=@userid and a.IsDelete=0 and a.LikeType=3 and b.IsDelete=0
{1}");
}
sqlExe.Cmd = sb.ToString().FormatWith(pageSql, pageSqlEnd);
sqlExe.AddParams("@userid", SqlDbType.BigInt, userID);
return(sqlExe.Query());
}
/// <summary>
/// 分页显示内容
/// </summary>
/// <param name="startIndex">开始码</param>
/// <param name="endIndex">结束码</param>
/// <returns></returns>
public DataSet SearchByRows(int startIndex, int endIndex, string key)
{
StringBuilder sb = new StringBuilder();
var sql = new SqlQuickBuild();
if (key.IsNotNullOrEmpty())
{
sb.Append(@"
select count(*) from ZhaoPin a
left join UserBase b on b.UserID=a.Publisher
where a.IsDelete=0 and b.IsDelete=0 and (a.Gangwei like @key or a.Cname like @key or a.JobRequire like @key or b.UserName like @key);
select * from (SELECT ROW_NUMBER() OVER(ORDER BY ZhaoPinID desc ) as rowid,
a.*,
--查询有效状态排序
(case when a.ValidTime>GETDATE() then 1 else 0 end) Flag,
b.UserName,
c.VIP,c.VIPExpiryTime,c.TotalScore,
(select EnumDesc from BBSEnum where c.LevelName=BBSEnumId and EnumType=3) LevelName,
(select EnumDesc from BBSEnum where c.OnlyLevelName=BBSEnumId and EnumType=4) OnlyLevelName,
c.HeadNameShowType,c.UserV
FROM ZhaoPin a
left join UserBase b on b.UserID=a.Publisher
left join UserExt c on c.UserID=b.UserID
where a.IsDelete=0 and b.IsDelete=0 and (a.Gangwei like @key or a.Cname like @key or a.JobRequire like @key or b.UserName like @key))
t where t.rowid between @startindex and @endindex order by T.Flag desc,T.CreateTime desc;");
sql.AddParams("@key", SqlDbType.VarChar, "%{0}%".FormatWith(key));
}
else
{
sb.Append(@"select count(*) from ZhaoPin a where a.IsDelete=0;
select * from (SELECT ROW_NUMBER() OVER(ORDER BY ZhaoPinID desc ) as rowid,
a.*,
(case when a.ValidTime>GETDATE() then 1 else 0 end) Flag,
b.UserName,
c.VIP,c.VIPExpiryTime,c.TotalScore,
(select EnumDesc from BBSEnum where c.LevelName=BBSEnumId and EnumType=3) LevelName,
(select EnumDesc from BBSEnum where c.OnlyLevelName=BBSEnumId and EnumType=4) OnlyLevelName,
c.HeadNameShowType,c.UserV
FROM ZhaoPin a
left join UserBase b on b.UserID=a.Publisher
left join UserExt c on c.UserID=a.Publisher
where a.IsDelete=0 and b.IsDelete=0)
t where t.rowid between @startindex and @endindex order by T.Flag desc,T.CreateTime desc;");
}
sql.Cmd = sb.ToString();
return(sql.AddParams("@startindex", SqlDbType.Int, startIndex)
.AddParams("@endindex", SqlDbType.Int, endIndex)
.Query());
}
public DataSet GetCommentListByUserid(long userID, int mainType, int startIndex, int endIndex, long currentLoginUserID, bool currentLoginUserIsMaster)
{
bool withoutAnonymous = false;//查询时是否排除 匿名的
bool isNotMe = userID != currentLoginUserID;
string filed = string.Empty;
string conditionJoin = string.Empty;
if (isNotMe)
{
withoutAnonymous = currentLoginUserIsMaster ? false : true;
filed = " ISNULL(feeanswer.FeeAnswerLogId,0) IsFeeAnswer ,";
conditionJoin = " left join FeeAnswerLog as feeanswer on feeanswer.AnswerId=a.CommentId and feeanswer.UserID=@currentUid";
}
string anonymous = withoutAnonymous ? " and a.IsAnonymous=0 " : string.Empty;
var sqlquick = new SqlQuickBuild(@"
select count(1) from Comment a
left join Question b on b.QuestionId=a.MainId
where a.CommentUserId=@userid and a.MainType=@mainType and a.IsDelete=0 and b.IsDelete=0;
select * from (
select row_Number() over(order by a.CreateTime desc) rid,a.CommentContent,a.CreateTime,b.Title QuestionTitle,
b.QuestionId,a.IsHideOrFeeToSee,
{0}
(select count(1) from Comment _a where _a.ReplyTopCommentId=a.CommentId and _a.MainType=@mainType) ReplyCount,--回答人数
(select count(1) from Prise _p where _p.TargetID=a.CommentId and _p.[TYpe]=2) PrisedCount--点赞人数
from Comment a
left join Question b on a.MainId=b.QuestionId
{1}
where a.CommentUserId=@userid and a.IsDelete=0 and a.MainType=@mainType and b.IsDelete=0 {2}
) T where T.rid BETWEEN @startindex and @endindex;
select HeadUrl as CommentHeadUrl from UserBase where UserID=@userid and IsDelete=0;"
.FormatWith(filed, conditionJoin, anonymous)
);
sqlquick.AddParams("@userid", SqlDbType.BigInt, userID)
.AddParams("@mainType", SqlDbType.Int, mainType)
.AddParams("@startindex", SqlDbType.Int, startIndex)
.AddParams("@endindex", SqlDbType.Int, endIndex);
if (isNotMe)
{
sqlquick.AddParams("@currentUid", SqlDbType.BigInt, currentLoginUserID);
}
return(sqlquick.Query());
}
public DataSet GetAllActivits(int startIndex, int endIndex, string key)
{
StringBuilder sb = new StringBuilder();
var sql = new SqlQuickBuild();
if (key.IsNotNullOrEmpty())
{
sb.Append(@"
select count(1) from Activity where IsDelete=0 and IsChecked=2 and IsDelete=0 and (Title like @key or UserName like @key);
select * from (
select ROW_NUMBER() over(order by ActivityCreateTIme desc)rid,
--a.ActivityId,a.Title,a.ActivityIMG,a.BeginTime,a.[Address]
a.*
,b.FeeType,b.Fee from Activity a
left join (select * from (select ROW_NUMBER() over(partition by activityid order by activityFeeid desc)rid,* from ActivityFee)T where T.rid=1) b on b.ActivityId=a.ActivityId
where a.IsDelete=0 and a.IsChecked=2
and (a.Title like @key or a.UserName like @key)
) T where T.rid BETWEEN @si and @ei");
sql.AddParams("@key", SqlDbType.VarChar, "%{0}%".FormatWith(key));
}
else
{
sb.Append(@"
select count(1) from Activity a
left join UserBase b on b.UserID=a.UserID
where a.IsDelete=0 and a.IsChecked=2;
select * from (
select ROW_NUMBER() over(order by ActivityCreateTIme desc)rid,
--a.ActivityId,a.Title,a.ActivityIMG,a.BeginTime,a.[Address]
a.*
,b.FeeType,b.Fee from Activity a
left join (select * from (select ROW_NUMBER() over(partition by activityid order by activityFeeid desc)rid,* from ActivityFee)T where T.rid=1) b on b.ActivityId=a.ActivityId
where a.IsDelete=0 and a.IsChecked=2
) T where T.rid BETWEEN @si and @ei");
}
sql.Cmd = sb.ToString();
return(sql.AddParams("@si", SqlDbType.Int, startIndex)
.AddParams("@ei", SqlDbType.Int, endIndex)
.Query());
}
public DataTable GetAllTags(TagsSortTypeEnum tagsSortTypeEnum)
{
var sql = new SqlQuickBuild();
string command = @"
select
ISNULL(b.ItemCount,0) ItemCount
,a.* from Tag a
{1} join (
select _b.TagId,count(*) ItemCount from MenuBelongTag _b
left join Question qq on qq.QuestionId=_b.MainId and _b.MainType=1
left join Article aa on aa.ArticleId=_b.MainId and _b.MainType=2
where (qq.IsDelete=0 or aa.IsDelete=0) {0}
group by _b.TagId
) b on a.TagId=b.TagId
where a.IsDelete=0 order by a.CreateTime desc;" ;
string conditionJoin = " left ";
string conditionWhere = string.Empty;
switch (tagsSortTypeEnum)
{
case TagsSortTypeEnum.Sort_Hot:
// conditionJoin = @"
//left join MenuBelongTag b on b.TagId=a.TagId and b.MainType=1
//left join Question c on c.QuestionId=b.MainId";
// conditionWhere = " and c.IsRemen=1 and c.IsDelete=0 ";
conditionJoin = " right ";
conditionWhere = @" and (qq.IsRemen=1)";
break;
case TagsSortTypeEnum.Sort_JingHua:
// conditionJoin = @"left join MenuBelongTag b on b.TagId=a.TagId and b.MainType=1
//left join Question c on c.QuestionId=b.MainId";
// conditionWhere = " and c.IsJinghua=1 and c.IsDelete=0 ";
conditionJoin = " right ";
conditionWhere = @" and (qq.IsJinghua=1)";
break;
case TagsSortTypeEnum.Sort_30:
// conditionJoin = @"left join MenuBelongTag b on b.TagId=a.TagId and b.MainType=1
//left join Question c on c.QuestionId=b.MainId";
DateTime start = DateTime.Now.AddDays(-DateTime.Now.Day + 1).Date;
// conditionWhere = " and c.IsDelete=0 and c.CreateTime between @startTime and getdate()";
conditionJoin = " right ";
conditionWhere = " and (qq.CreateTime between @startTime and getdate() or aa.CreateTime between @startTime and getdate())";
sql.AddParams("@startTime", SqlDbType.DateTime, start);
break;
}
sql.Cmd = command.FormatWith(conditionWhere, conditionJoin);
return(sql.GetTable());
}
public DataSet GetShareCoinList(long userID)
{
SqlQuickBuild sql = new SqlQuickBuild();
StringBuilder sb = new StringBuilder();
sb.Append(@"
select count(1) from ShareRegistLog where ShareUserID=@uid;
select a.EnumDesc ShareCoin,a.BBSEnumId BBSID,a.SortIndex ShareCount,ISNULL(b.ScoreCoinLogId,0) IsPickCoin from BBSEnum a
left join ScoreCoinLog b on b.CoinSource=22 and b.CreateUser=a.BBSEnumId and b.UserID=@uid
where a.EnumType=5 ;");
sql.AddParams("@uid", SqlDbType.BigInt, userID);
sql.Cmd = sb.ToString();
return(sql.Query());
}
public DataSet GetGiftDetail(long id, long uesrid, int joinType, bool searchMyJoinItem)
{
var sql = new SqlQuickBuild();
StringBuilder sb = new StringBuilder();
sb.Append(@"
select (select count(1) from UserGift ug where ug.GiftID=a.GiftID) AllBuyCount,
--,(select count(1) from UserGift ug2 where ug2.GiftID=a.GiftID and ug2.BuyUserID=@userid) CurrentUserBuyCount,
a.*
from Gift a where a.GiftID=@id;
select * from GiftFee b where b.GiftID=@id;
select ItemName,JoinItemQuestionExtId from JoinItemQuestionExt c where c.MainID=@id and c.MainType=@maintype;");
if (searchMyJoinItem)
{
sb.Append(@"select b.*,a.BuyCount from UserGift a
left join GiftFee b on b.GiftFeeId = a.GiftFeeId
where a.BuyUserID = @userid and a.IsPay = 1 and a.GiftID = @id");
}
sql.AddParams("@id", SqlDbType.BigInt, id)
.AddParams("@userid", SqlDbType.BigInt, uesrid)
.AddParams("@maintype", SqlDbType.Int, joinType);
sql.Cmd = sb.ToString();
return(sql.Query());
}
public DataSet GetQuestionList(int id, int startIndex, int endIndex, string keyWords = null, long tagId = 0)
{
StringBuilder sb = new StringBuilder();
var sql = new SqlQuickBuild();
if (keyWords.IsNotNullOrEmpty() || tagId > 0)
{
string baseSql = @"
--计算总量
select count(1) from Question a
left join UserBase b on b.UserID=a.UserID
{0}
{1};
select * from (
select ROW_NUMBER() over(order by a.createtime desc) rid,
b.UserName,
isnull(b.HeadUrl,'/Content/img/head_default.gif') HeadUrl,
(select count(1) from UserLike ul where ul.LikeTargetId=a.QuestionId and ul.LikeType=1 and ul.IsDelete=0) LikeCount,
(select count(1) from Comment aa where aa.MainID=a.QuestionId and aa.MainType=1 and aa.CommentOrReplyType=1 and aa.IsDelete=0) CommentCount,
(select count(*) from Prise where [Type]=1 and TargetID=a.QuestionId) PriseCount,
(select max(CreateTime) from Comment where mainid=a.questionid and MainType=1) LastCommentTime,
(select count(1) from [Master] _master where _master.UserID=a.UserID and _master.IsDelete=0) IsMaster,--是否管理员
a.*,
c.VIP,c.VIPExpiryTime,c.TotalScore,
(select EnumDesc from BBSEnum where c.LevelName=BBSEnumId and EnumType=3) LevelName,
(select EnumDesc from BBSEnum where c.OnlyLevelName=BBSEnumId and EnumType=4) OnlyLevelName,
c.HeadNameShowType,c.UserV
from Question a
left join UserBase b on a.UserID=b.UserID
left join UserExt c on c.UserID=b.UserID
{0}
{1}
) T where T.rid BETWEEN @startindex and @endindex";
if (tagId > 0)
{
//搜标签
sb.Append(baseSql.FormatWith(@"
left join MenuBelongTag mbt on mbt.MainId=a.QuestionId and mbt.MainType=1
left join Tag tag on tag.TagId=mbt.TagId",
" where tag.TagId=@tagid and b.IsDelete=0 and a.IsDelete=0 "));
sql.AddParams("@tagid", SqlDbType.BigInt, tagId);
}
else if (keyWords.IsNotNullOrEmpty())
{
//搜关键词
sb.Append(baseSql.FormatWith(string.Empty, " where a.TopicID=@id and a.IsDelete=0 and a.IsChecked=2 and b.IsDelete=0 and (a.Title like @key or b.UserName like @key)"));
sql.AddParams("@id", SqlDbType.Int, id);
sql.AddParams("@key", SqlDbType.VarChar, "%{0}%".FormatWith(keyWords));
}
}
else
{
sb.Append(@"
--选择置顶贴
select a.*,b.UserName,
isnull(b.HeadUrl,'/Content/img/head_default.gif') HeadUrl,
(select count(1) from UserLike ul where ul.LikeTargetId=a.QuestionId and ul.LikeType=1 and ul.IsDelete=0) LikeCount,
(select count(1) from Comment aa where aa.MainID=a.QuestionId and aa.MainType=1 and aa.CommentOrReplyType=1 and aa.IsDelete=0) CommentCount,
c.VIP,c.VIPExpiryTime,c.TotalScore,
(select count(*) from Prise where [Type]=1 and TargetID=a.QuestionId) PriseCount,
(select max(CreateTime) from Comment where mainid=a.questionid and MainType=1) LastCommentTime,
(select count(1) from [Master] _master where _master.UserID=a.UserID and _master.IsDelete=0) IsMaster,--是否管理员
(select EnumDesc from BBSEnum where c.LevelName=BBSEnumId and EnumType=3) LevelName,
(select EnumDesc from BBSEnum where c.OnlyLevelName=BBSEnumId and EnumType=4) OnlyLevelName,
c.HeadNameShowType,c.UserV
from Question a
left join UserBase b on a.UserID=b.UserID
left join UserExt c on c.UserID=b.UserID
where a.TopicID=@id and a.IsTop=1 and a.IsDelete=0 and b.IsDelete=0 ;
--计算总量
select count(1) from Question where TopicID=@id and IsDelete=0 and IsChecked=2;
select * from (
select ROW_NUMBER() over(order by a.createtime desc) rid,
b.UserName,
isnull(b.HeadUrl,'/Content/img/head_default.gif') HeadUrl,
(select count(1) from UserLike ul where ul.LikeTargetId=a.QuestionId and ul.LikeType=1 and ul.IsDelete=0) LikeCount,
(select count(1) from Comment aa where aa.MainID=a.QuestionId and aa.MainType=1 and aa.CommentOrReplyType=1 and aa.IsDelete=0) CommentCount,
(select count(*) from Prise where [Type]=1 and TargetID=a.QuestionId) PriseCount,
(select max(CreateTime) from Comment where mainid=a.questionid and MainType=1) LastCommentTime,
(select count(1) from [Master] _master where _master.UserID=a.UserID and _master.IsDelete=0) IsMaster,--是否管理员
a.*,
c.VIP,c.VIPExpiryTime,c.TotalScore,
--(select EnumDesc from BBSEnum where c.LevelName=BBSEnumId and EnumType=3) LevelName,
d.EnumDesc LevelName ,
d.Url LevelNameUrls,
--(select EnumDesc from BBSEnum where c.OnlyLevelName=BBSEnumId and EnumType=4) OnlyLevelName,
e.EnumDesc OnlyLevelName ,
c.HeadNameShowType,c.UserV
from Question a
left join UserBase b on a.UserID=b.UserID
left join UserExt c on c.UserID=b.UserID
left join BBSEnum d on c.LevelName = d.BBSENumId and d.EnumType = @LevelName
left join BBSEnum e on c.OnlyLevelName = e.BBSENumId and e.EnumType = @OnlyLevelName
where a.TopicID=@id and a.IsTop=0 and a.IsDelete=0 and a.IsChecked=2 and b.IsDelete=0) T where T.rid BETWEEN @startindex and @endindex");
sql.AddParams("@id", SqlDbType.BigInt, id);
}
sql.Cmd = sb.ToString();
return(sql.AddParams("@startindex", SqlDbType.Int, startIndex)
.AddParams("@endindex", SqlDbType.Int, endIndex)
.AddParams("@LevelName", SqlDbType.Int, BBSEnumType.LevelName.GetHashCode())
.AddParams("@OnlyLevelName", SqlDbType.Int, BBSEnumType.OnlyLevelName.GetHashCode())
.Query());
}
/// <summary>
/// 根据类型获取所有兑换数据
/// </summary>
/// <param name="startIndex"></param>
/// <param name="endIndex"></param>
/// <param name="type">礼物类型(1-礼物 2-数据分析 3-百晓堂课程)</param>
/// <param name="key">关键词搜索</param>
/// <param name="sortConfig">排序规则</param>
/// <returns></returns>
public DataSet GetALLGifts(int startIndex, int endIndex, int type, string key, string sortConfig)
{
StringBuilder sb = new StringBuilder();
var sql = new SqlQuickBuild();
StringBuilder orderBy = new StringBuilder();
string orderType = sortConfig.IsNotNullOrEmpty() && sortConfig.Contains("7") ? "asc" : "desc";
//排序规则
if (sortConfig.IsNotNullOrEmpty())
{
var sorts = sortConfig.Split('+');
sorts.ForEach(item =>
{
switch (item)
{
//case "1": item.AppendFormat(" a.IsTop {0},", orderType); break;
//case "2": item.AppendFormat(" a.IsRemen {0},", orderType); break;
//case "3": item.AppendFormat(" a.IsJinghua {0},", orderType); break;
case "4": orderBy.AppendFormat(" a.PVCount {0},", orderType); break;
case "5": orderBy.AppendFormat(" a.GiftCreateTime {0},", orderType); break;
}
});
orderBy.Remove(orderBy.Length - 1, 1);
}
else
{
orderBy.Append(" a.GiftCreateTime {0} ".FormatWith(orderType));
}
if (key.IsNotNullOrEmpty())
{
sb.Append(@" select count(1) from Gift a
left join UserBase b on b.UserID=a.GiftCreateUserID
where a.GType=@GType and a.IsDelete=0 and b.IsDelete=0
and (a.GiftName like @key or b.UserName like @key);
select * from (
select row_number() over(order by a.GiftCreateTime Desc)rid,
(select count(1) from UserGift ug where ug.GiftID=a.GiftID) BuyCount,
c.Fee,c.FeeType,
a.* from Gift a
left join UserBase b on b.UserID=a.GiftCreateUserID
left join (select * from (select ROW_NUMBER() over(partition by GiftID order by GiftFeeId desc)rid,* from GiftFee)T where T.rid=1) c on c.GiftID=a.GiftID
where a.GType=@GType and a.IsDelete=0 and b.IsDelete=0
and (a.GiftName like @key or b.UserName like @key)
) T where T.rid between @startindex and @endindex;");
sql.AddParams("@key", SqlDbType.VarChar, "%{0}%".FormatWith(key));
}
else
{
sb.Append(@"
select count(1) from Gift where GType=@GType and IsDelete=0;
select * from (
select row_number() over(order by {0})rid,
(select count(1) from UserGift ug where ug.GiftID=a.GiftID) BuyCount,
b.Fee,b.FeeType,
a.* from Gift a
left join (select * from (select ROW_NUMBER() over(partition by GiftID order by GiftFeeId desc)rid,* from GiftFee)T where T.rid=1) b on b.GiftID=a.GiftID
where a.GType=@GType and IsDelete=0) T where T.rid between @startindex and @endindex;".FormatWith(orderBy.ToString()));
}
sql.Cmd = sb.ToString();
return(sql.AddParams("@startindex", SqlDbType.Int, startIndex)
.AddParams("@endindex", SqlDbType.Int, endIndex)
.AddParams("@GType", SqlDbType.Int, type)
.Query());
}
public DataSet GetAllArticles(int startIndex, int endIndex, string keyWord = null, long tagId = 0)
{
//在提高客户满意度上面，我们需要注意什么
//在团队工作方面，哪些原则是我们需要着重去考虑的
//在软件质量方面，哪些点需要考虑
//项目管理方面，哪些点需要考虑
StringBuilder sb = new StringBuilder();
var sql = new SqlQuickBuild();
if (keyWord.IsNotNullOrEmpty() || tagId > 0)
{
string baseSql = @"select count(1) from Article a
left join UserBase b on b.UserID=a.UserID
{0}{1};
select * from (select
ROW_NUMBER() over(order by a.CreateTime desc) rid,
b.UserName,b.HeadUrl,
( select count(1) from UserLike ul where ul.LikeTargetID=a.ArticleId and ul.LikeType=2 and ul.IsDelete=0) LikeCount,
(select count(1) from Comment aa where aa.MainID=a.ArticleId and aa.MainType=2 and aa.CommentOrReplyType=1 and aa.IsDelete=0) CommentCount,
(select count(*) from Prise where [Type]=3 and TargetID=a.ArticleId) PriseCount,
(select max(CreateTime) from Comment where mainid=a.ArticleId and MainType=2) LastCommentTime,
a.*,
c.VIP,c.VIPExpiryTime,c.TotalScore,
(select EnumDesc from BBSEnum where c.LevelName=BBSEnumId and EnumType=3) LevelName,
(select EnumDesc from BBSEnum where c.OnlyLevelName=BBSEnumId and EnumType=4) OnlyLevelName,
c.HeadNameShowType,c.UserV
from Article a
left join UserBase b on a.UserID=b.UserID
left join UserExt c on c.UserID=b.UserID
{0}{1}
)T where T.rid BETWEEN @startindex and @endindex ;";
if (tagId > 0)
{
sb.Append(baseSql.FormatWith(@"
left join MenuBelongTag mbt on mbt.MainId=a.ArticleId and mbt.MainType=2
left join Tag tag on tag.TagId=mbt.TagId",
" where tag.TagId=@tagid and b.IsDelete=0 and a.IsDelete=0 "));
sql.AddParams("@tagid", SqlDbType.BigInt, tagId);
}
else if (keyWord.IsNotNullOrEmpty())
{
//搜关键词
sb.Append(baseSql.FormatWith(string.Empty, " where a.IsDelete=0 and a.IsChecked=2 and b.IsDelete=0 and (a.Title like @key or a.Body like @key or b.UserName like @key) "));
sql.AddParams("@key", SqlDbType.VarChar, "%{0}%".FormatWith(keyWord));
}
}
else
{
sb.Append(@"
select count(1) from Article where IsDelete=0 and IsChecked=2;
select * from (select
ROW_NUMBER() over(order by a.CreateTime desc) rid,
b.UserName,b.HeadUrl,
( select count(1) from UserLike ul where ul.LikeTargetID=a.ArticleId and ul.LikeType=2 and ul.IsDelete=0) LikeCount,
(select count(1) from Comment aa where aa.MainID=a.ArticleId and aa.MainType=2 and aa.CommentOrReplyType=1 and aa.IsDelete=0) CommentCount,
(select count(*) from Prise where [Type]=3 and TargetID=a.ArticleId) PriseCount,
(select max(CreateTime) from Comment where mainid=a.ArticleId and MainType=2) LastCommentTime,
a.*,
c.VIP,c.VIPExpiryTime,c.TotalScore,
(select EnumDesc from BBSEnum where c.LevelName=BBSEnumId and EnumType=3) LevelName,
(select EnumDesc from BBSEnum where c.OnlyLevelName=BBSEnumId and EnumType=4) OnlyLevelName,
c.HeadNameShowType,c.UserV
from Article a
left join UserBase b on a.UserID=b.UserID
left join UserExt c on c.UserID=b.UserID
where a.IsDelete=0 and a.IsChecked=2 and b.IsDelete=0
)T where T.rid BETWEEN @startindex and @endindex ;");
}
sql.Cmd = sb.ToString();
return(sql.AddParams("@startindex", SqlDbType.Int, startIndex)
.AddParams("@endindex", SqlDbType.Int, endIndex)
.Query());
}