/// <summary>
/// 查询订阅结果集
/// </summary>
/// <param name="userID">用户id</param>
/// <param name="subID">末尾订阅ID</param>
/// <param name="strWhere">查询条件 用于查询是否订阅条件</param>
/// <param name="pageSize">页面大小</param>
/// <param name="pageIndex">起始页 默认为1</param>
/// <param name="type">YES 已订阅 NO 未订阅</param>
/// <returns></returns>
public OperationResult <List <SubscriptionEntity> > Subscription_GetList(int userID, int subID, int pageSize, string strWhere, int pageIndex, SubQueryType type)
{
try
{
List <SubscriptionEntity> entitys = new List <SubscriptionEntity>();
SqlParameter[] prms =
{
new SqlParameter("@UserID", userID),
new SqlParameter("@SubID", subID),
new SqlParameter("@StrWhere", strWhere),
new SqlParameter("@PageIndex", pageIndex),
new SqlParameter("@PageSize", pageSize),
new SqlParameter("@QueryType", (int)type)
};
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_Subscription_GetList", prms).Tables[0];
entitys = ConvertDataTable <SubscriptionEntity> .ConvertToList(table);
return(new OperationResult <List <SubscriptionEntity> >(OperationResultType.Success, "订阅列表获取完成！", entitys));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <List <SubscriptionEntity> >(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>
/// 添加回复
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public OperationResult <AddReplyCommonEntity> ArticleReply_Insert(EventItemReplyEntity entity)
{
try
{
AddReplyCommonEntity acEntity = new AddReplyCommonEntity();
ReplyCommonEntity rcEntity = new ReplyCommonEntity();
using (SqlConnection con = new SqlConnection(SQlHelper.MyConnectStr))
{
SqlTransaction trans = null;
con.Open();
trans = con.BeginTransaction();
DateTime? nowDate = entity.ReplyTime == null ? DateTime.Now : entity.ReplyTime;
SqlParameter[] prms =
{
new SqlParameter("@ReplyID", entity.ReplyID),
new SqlParameter("@CommentID", entity.CommentID),
new SqlParameter("@UserID", entity.UserID),
new SqlParameter("@IsAnonymous", entity.IsAnonymous),
new SqlParameter("@ReplyParentID", entity.ReplyParentID),
new SqlParameter("@ReplyContent", entity.ReplyContent),
new SqlParameter("@ReplyTime", nowDate),
new SqlParameter("@CheckTypeID", entity.CheckTypeID),
new SqlParameter("@ViewStateID", entity.ViewStateID)
};
SqlParameter[] commPrms =
{
new SqlParameter("@CommentID", entity.CommentID),
new SqlParameter("@UserID", entity.UserID),
};
prms[0].Direction = ParameterDirection.Output;
int state = SQlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "M_EventItemReply_Insert", prms);
trans.Commit();
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_ReplyCommon_Get", commPrms).Tables[0];
if (table != null && table.Rows.Count != 0)
{
rcEntity = ConvertDataTable <ReplyCommonEntity> .ConvertToList(table)[0];
}
acEntity.ReplyID = prms[0].Value.ToInt();
acEntity.ReplyTime = nowDate;
acEntity.ReviewLikeCount = rcEntity.ReviewLikeCount;
acEntity.IsReviewLike = rcEntity.IsReviewLike;
acEntity.ReplyCount = rcEntity.ReplyCount;
if (state > 0)
{
return(new OperationResult <AddReplyCommonEntity>(OperationResultType.Success, "回复完成！", acEntity));
}
else
{
return(new OperationResult <AddReplyCommonEntity>(OperationResultType.NoChanged, "评论已被删除,无法回复！", acEntity));
}
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <AddReplyCommonEntity>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>
/// 获取单条订阅明细
/// </summary>
/// <param name="userID"></param>
/// <param name="subID"></param>
/// <returns></returns>
public OperationResult <SubscriptionEntity> Subscription_Get(int userID, int subID)
{
try
{
string sql = string.Empty;
sql += " SELECT s.SubID,s.SubName,s.PinYin,s.[Description],s.SubShortName,fp.PicturePath ,fp.Domain,s.CreateTime ";
sql += " ,(SELECT CAST(COUNT(1) AS BIT) FROM [dbo].[M_User_Subscription] WHERE USERID={0} AND SubID =s.SubID) AS IsAlready ";
sql += " FROM M_Subscription AS s ";
sql += " LEFT JOIN [dbo].[M_V_Picture] AS fp ON s.PictureID= fp.PictureID ";
sql += " WHERE s.SubID={1} ";
sql = string.Format(sql, userID, subID);
List <SubscriptionEntity> entitys = new List <SubscriptionEntity>();
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, sql).Tables[0];
entitys = ConvertDataTable <SubscriptionEntity> .ConvertToList(table);
if (entitys != null && entitys.Count == 1)
{
return(new OperationResult <SubscriptionEntity>(OperationResultType.Success, "订阅信息获取完成！", entitys[0]));
}
else
{
return(new OperationResult <SubscriptionEntity>(OperationResultType.NoChanged, "请求的订阅号不存在！"));
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <SubscriptionEntity>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>获取符合条件在有效时间内的一个活动对象</summary>
/// <param name="ActTypeId">活动类型id 1新人活动/2短期活动/3常规活动/4邀请活动/5系统赠送 </param>
/// <param name="ActUser">面向用户 首次投资用户=1，非首投用户=2 ，每标首投用户=3，每标最大投资用户=4，所有投资用户=5，续投用户=6 , 特殊复投用户=7</param>
/// <param name="ActState">活动状态 0默认(未上线) 1进行中(上线) 2结束(下线) 3停止</param>
/// <param name="RewTypeID">RewTypeID 默认0不限制 奖励类型id 1现金/2抵扣券/3加息券</param>
/// <returns>hx_ActivityTable 符合条件的一个活动对象</returns>
protected hx_ActivityTable GetActTableInfo(int ActTypeId, string targetPlatform, int ActUser = 0, int ActState = 1, int RewTypeID = 0)
{
hx_ActivityTable hat = null; //new hx_ActivityTable();
string key = "Act" + ActTypeId.ToString() + ActUser.ToString() + ActState.ToString() + RewTypeID.ToString();
StringBuilder sql = new StringBuilder();
if (HttpRuntime.Cache[key] == null)
{
sql.AppendFormat("select top 1 * from hx_ActivityTable where ActTypeId = {0} and ActState = {1} and ActTypeId != {2} and ActStarttime < '{3}' and ActEndtime > '{3}'", ActTypeId, ActState, 5, DateTime.Now);
//var query = ef.hx_ActivityTable.Where(p => p.ActTypeId == ActTypeId && p.ActState == ActState && p.ActTypeId != 5 && p.ActStarttime < DateTime.Now && p.ActEndtime > DateTime.Now);
//if (ActUser != 0)
//{
// query = query.Where(p => p.ActUser == ActUser);
//}
if (ActUser != 0)
{
sql.AppendFormat(" and ActUser = {0} ", ActUser);
}
//if (RewTypeID != 0)
//{
// query = query.Where(p => p.RewTypeID == RewTypeID);
//}
if (RewTypeID != 0)
{
sql.AppendFormat(" and RewTypeID = {0} ", RewTypeID);
}
if (!string.IsNullOrWhiteSpace(targetPlatform) && targetPlatform != EnumCommon.E_hx_ActivityTable.E_ActTargetPlatform.all)
{
string tp = targetPlatform.Replace("0", "_");
sql.AppendFormat(" and (ActTargetPlatform is null or ActTargetPlatform like '{0}') ", tp);
//query = query.AsQueryable().Where(p => p.ActTargetPlatform == null || SqlMethods.Like(p.ActTargetPlatform, targetPlatform.Replace("0", "_")));
//query = query.Where(" ActTargetPlatform like @atp ", new ObjectParameter("atp", tp));
}
sql.Append(" order by ActID desc ");
DataTable dt = DBUtility.DbHelperSQL.Query(sql.ToString()).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
hat = ConvertDataTable <hx_ActivityTable> .ConvertToList(dt)[0];
}
//hat = query.OrderByDescending(p => p.ActID).FirstOrDefault();
if (hat != null)
{//放入缓存
HttpRuntime.Cache.Add(key, hat, null, DateTime.Now.AddMinutes(30), System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.High, null);
}
}
else
{
hat = (hx_ActivityTable)HttpRuntime.Cache[key];
}
return(hat);
}
/// <summary>
/// 评论点赞插入
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public OperationResult <ReplyCommonEntity> CommentsLike_Insert(CommentLikeEntity entity)
{
try
{
ReplyCommonEntity rcEntity = new ReplyCommonEntity();
using (SqlConnection con = new SqlConnection(SQlHelper.MyConnectStr))
{
SqlTransaction trans = null;
con.Open();
trans = con.BeginTransaction();
SqlParameter[] prms =
{
new SqlParameter("@CommentID", entity.CommentID),
new SqlParameter("@UserID", entity.UserID),
new SqlParameter("@LikeCnt", entity.LikeCnt),
new SqlParameter("@LikeTime", entity.LikeTime == null ? DateTime.Now :entity.LikeTime)
};
SqlParameter[] commPrms =
{
new SqlParameter("@CommentID", entity.CommentID),
new SqlParameter("@UserID", entity.UserID)
};
int state = SQlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "M_Comments_Like_Insert", prms);
trans.Commit();
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_ReplyCommon_Get", commPrms).Tables[0];
if (table != null && table.Rows.Count != 0)
{
rcEntity = ConvertDataTable <ReplyCommonEntity> .ConvertToList(table)[0];
}
if (state > 0)
{
return(new OperationResult <ReplyCommonEntity>(OperationResultType.Success, "评论点赞成功！", rcEntity));
}
else
{
return(new OperationResult <ReplyCommonEntity>(OperationResultType.NoChanged, "您已点过赞了！", rcEntity));
}
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <ReplyCommonEntity>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>
/// 获取与我先关的 数据信息
/// </summary>
/// <param name="userId">用户ID</param>
/// <param name="messageID">消息ID</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">页面大小</param>
/// <returns></returns>
public OperationResult <List <RelateEntity> > Relate_GetList(int userId, int messageID, int pageIndex, int pageSize)
{
try
{
List <RelateEntity> entitys = new List <RelateEntity>();
SqlParameter[] parms =
{
new SqlParameter("@UserID", userId),
new SqlParameter("@MessageID", messageID),
new SqlParameter("@PageIndex", pageIndex),
new SqlParameter("@PageSize", pageSize)
};
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_Relate_GetList", parms).Tables[0];
entitys = ConvertDataTable <RelateEntity> .ConvertToList(table);
return(new OperationResult <List <RelateEntity> >(OperationResultType.Success, "与我相关数据获取成功！", entitys));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <List <RelateEntity> >(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>
/// 回复主体列表
/// </summary>
/// <param name="reviewsId">评论Id</param>
/// <param name="replyId">回复Id</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页面大小</param>
/// <returns></returns>
public OperationResult <List <EventItemReplyEntity> > ArticleReply_GetList(int reviewsId, int replyId, int pageIndex, int pageSize)
{
try
{
List <EventItemReplyEntity> entitys = new List <EventItemReplyEntity>();
SqlParameter[] parms =
{
new SqlParameter("@CommentID", reviewsId),
new SqlParameter("@ReplyID", replyId),
new SqlParameter("@PageIndex", pageIndex),
new SqlParameter("@PageSize", pageSize)
};
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_EventItemReply_GetList", parms).Tables[0];
entitys = ConvertDataTable <EventItemReplyEntity> .ConvertToList(table);
return(new OperationResult <List <EventItemReplyEntity> >(OperationResultType.Success, "回复列表获取完成！", entitys));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <List <EventItemReplyEntity> >(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>
/// 投资统计接口
/// </summary>
/// <param name="t"></param>
/// <param name="token"></param>
/// <param name="startdate"></param>
/// <param name="enddate"></param>
/// <param name="page"></param>
/// <param name="pagesize"></param>
/// <returns></returns>
public string Tongji_Invest(HttpRequestBase Request, string t = "1", string token = "", string startdate = "", string enddate = "", int page = 1, int pagesize = 10)
{
try
{
if (CheckTimeOut(t))
{
return("{\"code\":1008,\"msg\":\"请求失效\"}");
}
if (string.IsNullOrEmpty(token))
{
return("{\"code\":1008,\"msg\":\"参数token错误\"}");
}
string localToken = Utils.MD5(Utils.MD5(t) + _client_secret); //EncryptHelper.GetMd5Str32(EncryptHelper.GetMd5Str32(t, "x") + _client_secret, "x");
if (localToken != token)
{
return("{\"code\":1008,\"msg\":\"请求被篡改\"}");
}
//var retData = from item in ef.hx_member_table
// join channel in ef.hx_Channel
// on item.channel_invitedcode equals channel.Invitedcode
// join invest in ef.hx_Bid_records
// on item.registerid equals invest.investor_registerid
// where channel.ChannelName == "xicai"
// select new
// {
// username = item.username,
// invest_time = invest.invest_time,
// targetid = invest.targetid,
// investor_registerid = invest.investor_registerid,
// borrower_registerid = invest.borrower_registerid,
// bid_records_id = invest.bid_records_id,
// investment_amount = invest.investment_amount,
// mobile = item.mobile
// };
StringBuilder sql = new StringBuilder(@"
select
row_number() over (order by c.registration_time asc) as RowNumber,
'' url,
0 commision,
e.targetid pid,
e.invest_time [datetime],
[money] Earnings,
bid_records_id id,
investment_amount [money],
c.mobile phone,
f.annual_interest_rate rate,
case f.unit_day when 1 then ISNULL(life_of_loan,0)*30 else life_of_loan end life_cycle,
c.username username
from hx_member_table c
join hx_Channel d
on c.channel_invitedcode = d.Invitedcode
join hx_Bid_records e
on c.registerid = e.investor_registerid
join hx_borrowing_target f
on e.targetid = f.targetid
left join
(select sum(interestpayment) [money],
investor_registerid,
borrower_registerid,
targetid
from hx_income_statement
group by investor_registerid,borrower_registerid,targetid) g
on e.investor_registerid = g.investor_registerid and e.borrower_registerid = g.borrower_registerid and e.targetid = g.targetid
where d.ChannelName = 'xicai'" );
StringBuilder sqlCount = new StringBuilder(@"
select count(*)
from hx_member_table c
join hx_Channel d
on c.channel_invitedcode = d.Invitedcode
join hx_Bid_records e
on c.registerid = e.investor_registerid
join hx_borrowing_target f
on e.targetid = f.targetid
left join
(select sum(interestpayment) [money],
investor_registerid,
borrower_registerid,
targetid
from hx_income_statement
group by investor_registerid,borrower_registerid,targetid) g
on e.investor_registerid = g.investor_registerid and e.borrower_registerid = g.borrower_registerid and e.targetid = g.targetid
where d.ChannelName = 'xicai'" );
if (!string.IsNullOrWhiteSpace(startdate))
{
DateTime sTime = DateTime.Parse(startdate);
//retData = from item in retData
// where item.invest_time >= sTime
// select item;
sql.Append(" and e.invest_time >=");
sql.Append(sTime.ToString("yyyy-MM-dd"));
sqlCount.Append(" and e.invest_time >=");
sqlCount.Append(sTime.ToString("yyyy-MM-dd"));
}
if (!string.IsNullOrWhiteSpace(enddate))
{
DateTime eTime = DateTime.Parse(enddate);
//retData = from item in retData
// where item.invest_time <= eTime
// select item;
sql.Append(" and e.invest_time <=");
sql.Append(eTime.ToString("yyyy-MM-dd"));
sqlCount.Append(" and e.invest_time <=");
sqlCount.Append(eTime.ToString("yyyy-MM-dd"));
}
//var resData = from item in retData
// join target in ef.hx_borrowing_target
// on item.targetid equals target.targetid
// join earning in
// (from item in ef.hx_income_statement
// group item by new
// {
// investor = item.investor_registerid,
// borrower = item.borrower_registerid,
// targetID = item.targetid
// } into g
// select new
// {
// investor = g.Key.investor,
// borrower = g.Key.borrower,
// targetID = g.Key.targetID,
// money = g.Sum(c => c.interestpayment ?? 0)
// })
// on new { investor = item.investor_registerid, borrower = item.borrower_registerid, targetID = item.targetid }
// equals new { investor = earning.investor, borrower = earning.borrower, targetID = earning.targetID }
// select new
// {
// datetime = item.invest_time,
// Earnings = earning.money,
// id = item.bid_records_id,
// money = item.investment_amount,
// phone = item.mobile,
// pid = item.targetid,
// rate = target.annual_interest_rate,
// term = target.unit_day == 1 ? (target.life_of_loan ?? 0) * 30 : (target.life_of_loan ?? 0),
// username = item.username
// };
if (page <= 0)
{
page = 1;
}
if (pagesize > 50)
{
pagesize = 50;
}
if (pagesize <= 0)
{
pagesize = 1;
}
DataTable dtCount = DbHelperSQL.GET_DataTable_List(sqlCount.ToString());
string filnalSql = "select top " + pagesize + @" * from (" + sql.ToString() + ")as temp where RowNumber > " + (pagesize * (page - 1));
DataTable dtRecord = DbHelperSQL.GET_DataTable_List(filnalSql);
//var totalCount = resData.Count();
//var cc = resData.OrderBy(c => c.datetime).Skip((page - 1) * pagesize).Take(pagesize);
//var res = (from item in cc.ToList()
// select new tj_data
// {
// url = Request.Url.GetLeftPart(UriPartial.Authority) + "/invest_borrow_" + item.pid + ".html",
// commision = 0,
// pid = (item.pid ?? 0).ToString(),
// datetime = (Convert.ToDateTime(item.datetime).ToString("yyyy-MM-dd HH:mm:ss")),
// Earnings = item.Earnings,
// id = item.id.ToString(),
// money = item.money ?? 0,
// phone = item.phone,
// rate = item.rate ?? 0,
// life_cycle = item.term,
// username = item.username
// }).ToList();
List <tj_data> res = new List <tj_data>();
res = ConvertDataTable <tj_data> .ConvertToList(dtRecord);
int totalCount = Convert.ToInt32(dtCount.Rows[0][0]);
string r = "{\"list\":" + JsonConvert.SerializeObject(res, Formatting.Indented, timeFormat) + ",\"total\":" + totalCount + ",\"code\":0}";
return(r);
}
catch (Exception ex)
{
LogInfo.WriteLog("投资统计接口，异常消息：" + ex.Message + ",Trace:" + ex.StackTrace + ex.InnerException.StackTrace);
return("操作异常，请稍后重试");
}
}
