/// <summary> /// 指定用户,指定日期内进行签到。返回签到状态:0-重复签到,1-签到成功 /// </summary> /// <param name="log"></param> /// <returns>返回签到状态:0-重复签到,1-签到成功</returns> public static int UserCheckInToday(UserCheckedInLog log) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UserCheckedIn", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, AppChannel, Date", log.UserId, log.AppChannel, log.CreateDate); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 获取指定的帖子跟帖总数 /// </summary> /// <param name="topicId"></param> /// <returns></returns> public static int GetTopicReplyCount(int topicId) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetTopicPostCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("TopicId", topicId); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 获取指定用户、指定个人信息类型的更新次数 /// </summary> /// <param name="log">日志内容(用于指定用户编号和更新的个人信息类型)</param> /// <returns>曾经更新的次数</returns> public static int UserUpdateSelfInfoCount(UserInfoUpdateLog log) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Logs_GetUserInfoUpdatesCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, InfoType", log.UserId, log.InfoType); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 查询指定用户对指定的目标数据“点赞”次数 /// </summary> /// <param name="userId">用户编号</param> /// <param name="targetId">目标数据编号</param> /// <param name="t">类型:1-帖子,2-回复</param> /// <returns></returns> public static int UserFavouredCount(int userId, int targetId, byte t) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetUserFavouredCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, TargetId, T", userId, targetId, t); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 获取词、句、新闻信息总数 /// </summary> /// <param name="target"></param> /// <returns></returns> public static int GetWsnContentCount(int target) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Material_Wsn_GetNewsCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("Target", target); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 检测用户名是否已存在 /// </summary> /// <param name="userName">待检测的用户名</param> /// <param name="userAccess">用户对应的登录权限</param> /// <returns>用户名是否已存在</returns> public static bool UserNameIsExists(string userName, UserAccess userAccess) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Users_UserNameCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserName, UserAccess", userName, userAccess); return(Convert.ToInt32(cmd.ExecuteScalar()) > 0); } }
/// <summary> /// 获取指定用户回复过的帖子总数 /// </summary> /// <param name="userId"></param> /// <returns></returns> public static int GetUserRepliedTopicCount(int userId) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetUserRepliedTopicCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId", userId); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 获取和指定的目标用户之间是否已存在单向“关注”关系记录 /// </summary> /// <param name="uFollowed">关注关系数据</param> /// <returns></returns> public static bool BeFollowedWithTargetUser(UserFollowed uFollowed) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Social_BeFollowedWithTargetUser", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, FollowedUserId", uFollowed.UserId, uFollowed.FollowedUserId); return(Convert.ToBoolean(cmd.ExecuteScalar())); } }
/// <summary> /// 获取指定用户,指定日期内签到次数 /// </summary> /// <param name="log"></param> /// <returns></returns> public static int UserCheckedInCountWithDay(UserCheckedInLog log) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Users_GetUserCheckedInCountWithDay", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, Date", log.UserId, log.CreateDate); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 改变和指定用户的单向“关注”关系,改变结果:成功或失败 /// </summary> /// <param name="uFollowed">关注关系数据</param> /// <param name="action">改变类型:1-增加,0-移除</param> /// <returns>改变结果:成功或失败</returns> public static bool ChangedFollowedWithTargetUser(UserFollowed uFollowed, byte action) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Social_ChangedFollowedWithTargetUser", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, FollowedUserId, CreateDate, Action", uFollowed.UserId, uFollowed.FollowedUserId, uFollowed.CreateDate, action); return(Convert.ToBoolean(cmd.ExecuteScalar())); } }
/// <summary> /// 用户退出群组 /// </summary> /// <param name="userId"></param> /// <param name="groupId"></param> /// <returns></returns> public static int UserQuitFromGroup(int userId, int groupId) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Social_QuitFromGroup", CommandType.StoredProcedure)) { cmd.AddInputParameters("GroupId, UserId", groupId, userId); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 用户加入群组,返回状态码:0-目标群组已满员,1-加入成功, 2-用户已是群组成员 /// </summary> /// <param name="groupMember">用户与群组关系数据信息</param> /// <returns>状态码:0-目标群组已满员,1-加入成功, 2-用户已是群组成员</returns> public static int UserJoinToGroup(GroupMember groupMember) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Social_JoinToGroup", CommandType.StoredProcedure)) { cmd.AddInputParameters("GroupId, UserId, IsCreator, CreateDate", groupMember.GroupId, groupMember.UserId, groupMember.IsCreator, groupMember.CreateDate); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 获取符合查询条件的帖子总数 /// </summary> /// <param name="condition"></param> /// <returns></returns> public static int GetTopicCount(TopicQueryConditions condition) { Tuple <string, string, List <string>, List <object> > tuples = CreateSqlPartionByCondition(condition); string querySql = string.Format("SELECT COUNT(0) FROM Forum_Topics WHERE {0}", tuples.Item1); using (DbCommander cmd = new DbCommander(DbConn.ReadDb, querySql)) { cmd.AddInputParameters(string.Join(",", tuples.Item3), tuples.Item4.ToArray()); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 创建帖子 /// </summary> /// <param name="topic"></param> public static void CreateTopic(TopicFullInfo topic) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_CreateTopic", CommandType.StoredProcedure)) { cmd.AddInputParameters( "ForumId, SchoolId, UserId, AttachContent, Title, Content, Intro, Icon, Voice, Reward, IsQuestion, IsAllowReply, Status, CreateDate", topic.ForumId, topic.SchoolId, topic.UserId, topic.AttachContent, topic.Title, topic.Content, topic.Intro, topic.Icon, topic.Voice, topic.Reward, topic.IsQuestion, topic.IsAllowReply, topic.Status, topic.CreateDate ); topic.TopicId = Convert.ToInt32(cmd.ExecuteScalar()); } }
/// <summary> /// 注册用户设备信息 /// </summary> /// <param name="device"></param> public static void RegisterDevice(UserDevice device) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Support_DeviceCreate", CommandType.StoredProcedure)) { cmd.AddInputParameters( "IMSI, IMEI, MSID, Platform, OS, Code, Token, ChannelCode, CreateDate", device.IMSI, device.IMEI, device.MSID, device.Platform, device.OS, device.Code, device.Token, device.ChannelCode, device.CreateDate ); device.DeviceId = Convert.ToInt32(cmd.ExecuteScalar()); } }
/// <summary> /// 提交报名信息 /// </summary> /// <param name="apply"></param> public static void SubmitApply(ApplyInfo apply) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Extend_SubmitUserApply", CommandType.StoredProcedure)) { cmd.AddInputParameters( "UserId, UserName, Telphone, AreaCity, SchoolId, CourseId, SourceAd, CreateDate, Comment", apply.UserId, apply.UserName, apply.Telphone, apply.AreaCity, apply.SchoolId, apply.CourseId, apply.SourceAd, apply.CreateDate, apply.Comment ); apply.Id = Convert.ToInt32(cmd.ExecuteScalar()); } }
/// <summary> /// 创建跟帖信息 /// </summary> /// <param name="postInfo"></param> public static void CreateReply(PostFullInfo postInfo) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_CreateTopicReply", CommandType.StoredProcedure)) { cmd.AddInputParameters( "TopicId, ForumId, SchoolId, UserId, Content, ReplyForUserId, Status, CreateDate", postInfo.TopicId, postInfo.ForumId, postInfo.SchoolId, postInfo.UserId, postInfo.Content, postInfo.ReplyForUserId, postInfo.Status, postInfo.CreateDate ); postInfo.PostId = Convert.ToInt32(cmd.ExecuteScalar()); } }
/// <summary> /// 创建群组 /// </summary> /// <param name="group"></param> /// <param name="site"></param> public static void CreateGroup(GroupInfo group, int site) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Social_CreateGroup", CommandType.StoredProcedure)) { cmd.AddInputParameters( "GroupName, GroupIcon, Comment, MaxMemberCount, MemberCount, GroupType, CreatorId, InterestCode, QuickJoinCode, CreateDate, IsCreator, SchoolId, ClassId", group.GroupName, group.GroupIcon, group.Comment, group.MaxMemberCount, group.MemberCount, group.GroupType, group.CreatorId, group.InterestCode, group.QuickJoinCode, group.CreateDate, true, site, 0 ); group.GroupId = Convert.ToInt32(cmd.ExecuteScalar()); } }
/// <summary> /// 查询符合条件的群组信息总数 /// </summary> /// <param name="keyword"></param> /// <param name="interest"></param> /// <param name="onlyLess"></param> /// <param name="userId"></param> /// <param name="site"></param> /// <returns></returns> public static int GroupFindCount(string keyword, string interest, bool onlyLess, int userId, int site) { Tuple <string, string, List <string>, List <object> > sqlPart = CreateGroupFindSqlPart(keyword, interest, onlyLess, site); string querySql = string.Format("SELECT COUNT(0) FROM Chat_Groups G {0} WHERE NOT EXISTS( SELECT 1 FROM Chat_GroupMembers WHERE UserId = @UserId AND GroupId = G.GroupId ) {1}", sqlPart.Item1, sqlPart.Item2); using (DbCommander cmd = new DbCommander(DbConn.ReadDb, querySql, CommandType.Text)) { sqlPart.Item3.Add("UserId"); sqlPart.Item4.Add(userId); cmd.AddInputParameters(string.Join(",", sqlPart.Item3), sqlPart.Item4.ToArray()); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
public DataTable ExecuteCommand(Guid userID, string commandText, int pageIndex, int pageSize, out int totalRow) { using (var context = new VnrHrmDataContext()) { DataTable result = new DataTable("Sys_SQLCommanderModel"); var connection = context.Database.Connection; using (DbCommander commander = new DbCommander(connection)) { if (pageIndex >= 0 && pageSize > 0) { var fromIndex = commandText.ToLower().IndexOf("from"); var countCommand = commandText.Substring(fromIndex); countCommand = "Select Count(*) " + countCommand; var rowCount = commander.ExecuteScalar(countCommand); totalRow = rowCount.TryGetValue<int>(); int startRow = pageIndex * pageSize; commander.Fill(result, startRow, pageSize, commandText); } else { commander.Fill(result, commandText); totalRow = result.Rows.Count; } if ((result.Columns.Contains(Constant.GrossAmount) && result.Columns[Constant.GrossAmount].DataType == typeof(string)) || (result.Columns.Contains(Constant.Amount) && result.Columns[Constant.Amount].DataType == typeof(string))) { foreach (DataRow row in result.Rows) { try { if (result.Columns.Contains(Constant.Amount) && result.Columns[Constant.Amount].DataType == typeof(string)) { if (!string.IsNullOrWhiteSpace(row[Constant.Amount].GetString())) { var grossAmount = row[Constant.Amount].GetString(); row[Constant.Amount] = grossAmount.Decrypt(); } } if (result.Columns.Contains(Constant.GrossAmount) && result.Columns[Constant.GrossAmount].DataType == typeof(string)) { if (!string.IsNullOrWhiteSpace(row[Constant.GrossAmount].GetString())) { var grossAmount = row[Constant.GrossAmount].GetString(); row[Constant.GrossAmount] = grossAmount.Decrypt(); } } } catch (Exception) { } } } } return result; } }
/// <summary> /// 创建用户记录 /// </summary> /// <param name="uAccount">用户账号信息</param> /// <param name="uInfo">用户个人信息</param> /// <param name="extInfo">用户扩展信息</param> /// <param name="userCoords">用户当前位置坐标信息</param> /// <param name="userOptions">用户个人应用设置信息</param> /// <param name="userInSchool">学校用户信息</param> public static void CreateUser(UserAccount uAccount, UserInfo uInfo, UserExtInfo extInfo, UserCoords userCoords, UserOptions userOptions, UserWithSchool userInSchool) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UserAccountCreate", CommandType.StoredProcedure)) { cmd.AddInputParameters( @"UserId, UserName, Password, PasswordFormat, PasswordSalt, LoginCount, CreateDate, NickName, RealName, BackIcon, HeadIcon, Signature, Comment, Gender, Birthday, IDCardNo, Area, Address, PostCode, Industry, Company, JobPosition, Interests, WebSite, Email, Mobile, Telphone, QQ, MSN, OtherInfo, UserSite, UserRole, UserAccess, CreateDeviceId, CreateAPPChannel, CreateAccountChannel, Longitude, Latitudes, RemindPrivateMessage, RemindGroupMessage, RemindBeFollowed, RemindTopicBeReply, SchoolId, IsTrial, TrialedSource, IsActivated, IsExpired, StudNo, ClassInfo, SchoolComment, SyncForBbs, Status", uAccount.UserId, uAccount.UserName, uAccount.Password, uAccount.PasswordFormat, uAccount.PasswordSalt, uAccount.LoginCount, uAccount.CreateDate, uInfo.NickName, uInfo.RealName, uInfo.BackIcon, uInfo.HeadIcon, uInfo.Signature, uInfo.Comment, uInfo.Gender, uInfo.Birthday, uInfo.IDCardNo, uInfo.Area, uInfo.Address, uInfo.PostCode, uInfo.Industry, uInfo.Company, uInfo.JobPosition, uInfo.Interests, uInfo.WebSite, uInfo.Email, uInfo.Mobile, uInfo.Telphone, uInfo.QQ, uInfo.MSN, uInfo.OtherInfo, extInfo.UserSite, extInfo.UserRole, extInfo.UserAccess, extInfo.CreateDeviceId, extInfo.CreateAPPChannel, extInfo.CreateAccountChannel, userCoords.Longitude, userCoords.Latitudes, userOptions.RemindPrivateMessage, userOptions.RemindGroupMessage, userOptions.RemindBeFollowed, userOptions.RemindTopicBeReply, userInSchool.SchoolId, userInSchool.IsTrial, userInSchool.TrialedSource, userInSchool.IsActivated, userInSchool.IsExpired, userInSchool.StudNo, userInSchool.ClassInfo, userInSchool.Comment, userInSchool.SyncForBbs, userInSchool.Status ); uAccount.UserId = Convert.ToInt32(cmd.ExecuteScalar()); } }
public DataTable ExecuteCommand(Guid userID, string commandText, int pageIndex, int pageSize, out int totalRow) { using (var context = new VnrHrmDataContext()) { DataTable result = new DataTable("Sys_SQLCommanderModel"); var connection = context.Database.Connection; using (DbCommander commander = new DbCommander(connection)) { if (pageIndex >= 0 && pageSize > 0) { var fromIndex = commandText.ToLower().IndexOf("from"); var countCommand = commandText.Substring(fromIndex); countCommand = "Select Count(*) " + countCommand; var rowCount = commander.ExecuteScalar(countCommand); totalRow = rowCount.TryGetValue <int>(); int startRow = pageIndex * pageSize; commander.Fill(result, startRow, pageSize, commandText); } else { commander.Fill(result, commandText); totalRow = result.Rows.Count; } if ((result.Columns.Contains(Constant.GrossAmount) && result.Columns[Constant.GrossAmount].DataType == typeof(string)) || (result.Columns.Contains(Constant.Amount) && result.Columns[Constant.Amount].DataType == typeof(string))) { foreach (DataRow row in result.Rows) { try { if (result.Columns.Contains(Constant.Amount) && result.Columns[Constant.Amount].DataType == typeof(string)) { if (!string.IsNullOrWhiteSpace(row[Constant.Amount].GetString())) { var grossAmount = row[Constant.Amount].GetString(); row[Constant.Amount] = grossAmount.Decrypt(); } } if (result.Columns.Contains(Constant.GrossAmount) && result.Columns[Constant.GrossAmount].DataType == typeof(string)) { if (!string.IsNullOrWhiteSpace(row[Constant.GrossAmount].GetString())) { var grossAmount = row[Constant.GrossAmount].GetString(); row[Constant.GrossAmount] = grossAmount.Decrypt(); } } } catch (Exception) { } } } } return(result); } }