/// <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);
}
}