/// <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("操作异常,请稍后重试"); } }