public void SyncCardHistory() { using (var context = new VnrHrmDataContext()) { IUnitOfWork unitOfWork = new UnitOfWork(context); string syncType = SyncDataType.Sync_Card_History.ToString(); var listSyncInfo = unitOfWork.CreateQueryable<Cat_Sync>(d => d.Code == syncType && (!d.IsFromInner.HasValue || !d.IsFromInner.Value)).Select(d => new { d.ID, d.Code, d.SyncName, d.ServerName, d.UserName, d.PassWord, d.DatabaseName, d.InnerTable, d.OuterTable, d.IsFromInner }).ToList(); var listSyncID = listSyncInfo.Select(d => d.ID).ToArray(); var listSyncItemInfo = unitOfWork.CreateQueryable<Cat_SyncItem>(d => d.SyncID.HasValue && listSyncID.Contains(d.SyncID.Value)).Select(d => new { d.IsExcluded, d.InnerField, d.OuterField, d.AllowNull, d.AllowDuplicate, d.DuplicateGroup, d.FilterValues, d.SyncID }).ToList(); foreach (var syncInfo in listSyncInfo) { string connectionString = DbClientHelper.BuildSqlConnectionString(syncInfo.ServerName, syncInfo.DatabaseName, syncInfo.UserName, syncInfo.PassWord); var syncItems = listSyncItemInfo.Where(d => d.SyncID == syncInfo.ID); SqlConnection connection = new SqlConnection(connectionString); using (DbCommander commander = new DbCommander(connection)) { } } } }
/// <summary> /// 英译汉联想查询(单个单词) /// </summary> /// <param name="word">单词</param> /// <param name="firstChar">第一个字母</param> /// <param name="querySize">最大查询联想结果数量</param> /// <returns></returns> public static IEnumerable <DictRecord> DictE2cExtendQueryWithSingleWords(string word, string firstChar, int querySize) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Material_Dict_ExtendQueryBySingleWord", CommandType.StoredProcedure)) { cmd.AddInputParameters("Word, WordLike, FirstChar, Size", word, string.Format("{0}%", word), string.Format("{0}%", firstChar), querySize); List <DictRecord> resultData = new List <DictRecord>(0); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { resultData.Add(new DictRecord { Word = (string)reader["Word"], PhonicsEn = (string)reader["PhonicsEn"], PhonicsUs = (string)reader["PhonicsUs"], AudioEn = (string)reader["AudioEn"], AudioUs = (string)reader["AudioUs"], Explication = (byte[])reader["Explication"], Example = (byte[])reader["Example"], TransBase = (byte[])reader["TransBase"] }); } reader.Close(); } return(resultData); } }
/// <summary> /// 汉译英词典查询 /// </summary> /// <param name="word"></param> /// <returns></returns> public static DictRecord QueryC2EDict(string word) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Material_Dict_QueryC2E", CommandType.StoredProcedure)) { cmd.AddInputParameters("Word", word); DictRecord resultData = null; using (IDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { resultData = new DictRecord { Word = (string)reader["Word"], TransBase = (byte[])reader["Translation"], AudioEn = "", AudioUs = "", PhonicsEn = "", PhonicsUs = "", Example = new byte[0], Explication = new byte[0] }; } reader.Close(); } return(resultData); } }
/// <summary> /// 获取指定的帖子跟帖信息分页列表 /// </summary> /// <param name="topicId"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public static IEnumerable <PostInfo> GetTopicReplyList(int topicId, int pageIndex, int pageSize) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetTopicPostList", CommandType.StoredProcedure)) { cmd.AddInputParameters("PostId, PageIndex, PageSize", topicId, pageIndex, pageSize); List <PostInfo> resultData = new List <PostInfo>(0); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { resultData.Add(new PostInfo { TopicId = (int)reader["TopicId"], PostId = (int)reader["PostId"], UserId = (int)reader["UserId"], ExpChanged = (int)reader["ExpChanged"], VirtualCoinChanged = (int)reader["VirtualCoinChanged"], FavouredCount = (int)reader["FavouredCount"], CreateDate = (DateTime)reader["CreateDate"], Content = (string)reader["Content"], IsBestReply = (bool)reader["IsBestReply"], SetBestDate = (DateTime)reader["SetBestDate"], ReplyForUserId = (int)reader["ReplyForUserId"] }); } reader.Close(); } return(resultData); } }
/// <summary> /// 获取指定用户的所有课时信息列表 /// </summary> /// <param name="schoolId"></param> /// <param name="userId"></param> /// <returns></returns> public static IEnumerable <LessonInfo> GetUserLessions(int schoolId, int userId) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_School_GetUserLessons", CommandType.StoredProcedure)) { cmd.AddInputParameters("SchoolId", schoolId); List <LessonInfo> resultData = new List <LessonInfo>(0); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { resultData.Add(new LessonInfo { CourseId = (int)reader["CourseId"], LessonId = (int)reader["LessonId"], ClassId = (int)reader["ClassId"], LessonDate = (DateTime)reader["LessonDate"], LessonStartTime = (DateTime)reader["LessonStartTime"], LessonEndTime = (DateTime)reader["LessonEndTime"], CreateDate = (DateTime)reader["CreateDate"], ClassName = (string)reader["ClassName"] }); } reader.Close(); } return(resultData); } }
/// <summary> /// 根据群编号或者快速加入码获取群组信息 /// </summary> /// <param name="groupId">群组编号</param> /// <param name="quickJoinCode">快速加入码</param> /// <returns>群组信息</returns> public static GroupInfo GetGroupInfo(int groupId, string quickJoinCode) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Social_GetGroupDetail", CommandType.StoredProcedure)) { cmd.AddInputParameters("GroupId, QuickJoinCode", groupId, quickJoinCode); GroupInfo resultData = null; using (IDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { resultData = new GroupInfo { GroupId = (int)reader["GroupId"], GroupName = (string)reader["GroupName"], GroupIcon = (string)reader["GroupIcon"], Comment = (string)reader["Comment"], MaxMemberCount = (int)reader["MaxMemberCount"], MemberCount = (int)reader["MemberCount"], GroupType = (int)reader["GroupType"], CreatorId = (int)reader["CreatorId"], InterestCode = (string)reader["InterestCode"], QuickJoinCode = (string)reader["QuickJoinCode"], CreateDate = (DateTime)reader["CreateDate"] }; } reader.Close(); } return(resultData); } }
/// <summary> /// 获取指定学校的课程信息列表 /// </summary> /// <param name="schoolId"></param> /// <returns></returns> public static IEnumerable <CourseBase> GetSchoolCourses(int schoolId) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_School_GetCourses", CommandType.StoredProcedure)) { cmd.AddInputParameters("SchoolId", schoolId); List <CourseBase> resultData = new List <CourseBase>(0); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { resultData.Add(new CourseBase { CourseId = (int)reader["CourseId"], CategoryId = (int)reader["CategoryId"], SchoolId = (int)reader["SchoolId"], CourseName = (string)reader["CourseName"], CourseCode = (string)reader["CourseCode"], CourseIcon = (string)reader["CourseIcon"], Intro = (string)reader["Intro"], Comment = (string)reader["Comment"], PriceName = (string)reader["PriceName"], SalePriceName = (string)reader["SalePriceName"], Sort = (int)reader["Sort"], IsEnabled = (bool)reader["IsEnabled"], CreateDate = (DateTime)reader["CreateDate"] }); } reader.Close(); } return(resultData); } }
/// <summary> /// 获取指定的学校信息 /// </summary> /// <param name="schoolId">学校编号</param> /// <returns>学校信息</returns> public static SchoolBase GetSchoolBaseInfo(int schoolId) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_School_GetDetail", CommandType.StoredProcedure)) { cmd.AddInputParameters("SchoolId", schoolId); SchoolBase resultData = null; using (IDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { resultData = new SchoolBase { SchoolId = (int)reader["SchoolId"], FullName = (string)reader["FullName"], ShortName = (string)reader["ShortName"], EnglishName = (string)reader["EnglishName"], LargerLogo = (string)reader["LargerLogo"], SmallerLogo = (string)reader["SmallerLogo"], SceneryLogo = (string)reader["SceneryLogo"], AreaCity = (string)reader["AreaCity"], AreaAddress = (string)reader["AreaAddress"], Contacter = (string)reader["Contacter"], Telphone = (string)reader["Telphone"], Email = (string)reader["Email"], WebSite = (string)reader["WebSite"], Introduce = (string)reader["Introduce"], CreateDate = (DateTime)reader["CreateDate"] }; } reader.Close(); } return(resultData); } }
/// <summary> /// 获取用户与学校相关的信息 /// </summary> /// <param name="userId">目标用户编号</param> /// <returns>用户与学校相关的信息</returns> public static UserWithSchool GetUserWithSchool(int userId) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Users_GetUserWithSchool", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId", userId); UserWithSchool resultData = null; using (IDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { resultData = new UserWithSchool { UserId = (int)reader["UserId"], SchoolId = (int)reader["UserId"], IsTrial = (bool)reader["IsTrial"], TrialedSource = (string)reader["TrialedSource"], IsActivated = (bool)reader["IsActivated"], ActivatedDate = (DateTime)reader["ActivatedDate"], IsExpired = (bool)reader["IsExpired"], ExpiredDate = (DateTime)reader["ExpiredDate"], StudNo = (string)reader["StudNo"], ClassInfo = (string)reader["ClassInfo"], Comment = (string)reader["Comment"], SyncForBbs = (bool)reader["SyncForBbs"], Status = (int)reader["Status"] }; } reader.Close(); } return(resultData); } }
/// <summary> /// 获取用户验证信息 /// </summary> /// <param name="userName">用户名</param> /// <param name="userAccess">用户对应的登录权限</param> /// <returns>用户验证信息</returns> public static UserVaildInfo GetUserVaildInfo(string userName, UserAccess userAccess) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Users_GetUserVaildInfo", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserName, UserAccess", userName, userAccess); UserVaildInfo resultData = null; using (IDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { resultData = new UserVaildInfo { UserId = (int)reader["UserId"], UserName = (string)reader["UserName"], Password = (string)reader["Password"], PasswordSalt = (string)reader["PasswordSalt"], IsApproved = (bool)reader["IsApproved"], IsLocked = (bool)reader["IsLocked"], LastLockedoutDate = (DateTime)reader["LastLockedoutDate"], LoginCount = (int)reader["LoginCount"], LastLoginDate = (DateTime)reader["LastLoginDate"], UserSite = (int)reader["UserSite"], UserRole = (int)reader["UserRole"], UserAccess = (UserAccess)(int)reader["UserAccess"], LastLoginDeviceId = (int)reader["LastLoginDeviceId"] }; } reader.Close(); } return(resultData); } }
/// <summary> /// 获取指定的论坛版块详情 /// </summary> /// <param name="forumId"></param> /// <param name="schoolId"></param> /// <returns></returns> public static ForumInfo GetForumInfoById(int forumId, int schoolId) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetForumById", CommandType.StoredProcedure)) { cmd.AddInputParameters("ForumId, SchoolId", forumId, schoolId); ForumInfo resultData = null; using (IDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { resultData = new ForumInfo { ForumId = (int)reader["ForumId"], ForumName = (string)reader["ForumName"], ForumIcon = (string)reader["ForumIcon"], AllowPost = (int)reader["AllowPost"], AllowPostType = (int)reader["AllowPostType"], LinkType = (int)reader["LinkType"] }; } reader.Close(); } return(resultData); } }
/// <summary> /// 获取指定的帖子回复信息 /// </summary> /// <param name="postId"></param> /// <returns></returns> public static PostInfo GetPostInfoById(int postId) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetPostById", CommandType.StoredProcedure)) { cmd.AddInputParameters("PostId", postId); PostInfo resultData = null; using (IDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { resultData = new PostInfo { TopicId = (int)reader["TopicId"], PostId = (int)reader["PostId"], UserId = (int)reader["UserId"], ExpChanged = (int)reader["ExpChanged"], VirtualCoinChanged = (int)reader["VirtualCoinChanged"], FavouredCount = (int)reader["FavouredCount"], CreateDate = (DateTime)reader["CreateDate"], Content = (string)reader["Content"], IsBestReply = (bool)reader["IsBestReply"], SetBestDate = (DateTime)reader["SetBestDate"], ReplyForUserId = (int)reader["ReplyForUserId"] }; } reader.Close(); } return(resultData); } }
/// <summary> /// 获取更新的论坛版块信息列表 /// </summary> /// <param name="userId"></param> /// <param name="deviceId"></param> /// <param name="schoolId"></param> /// <returns></returns> public static IEnumerable <ForumInfo> GetUpdatedForumsList(int userId, int deviceId, int schoolId) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_GetForumUpdated", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, DeviceId, SchoolId, Date", userId, deviceId, schoolId, DateTime.Now); List <ForumInfo> resultData = new List <ForumInfo>(0); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { resultData.Add(new ForumInfo { ForumId = (int)reader["ForumId"], ForumName = (string)reader["ForumName"], ForumIcon = (string)reader["ForumIcon"], AllowPost = (int)reader["AllowPost"], AllowPostType = (int)reader["AllowPostType"], LinkType = (int)reader["LinkType"] }); } reader.Close(); } return(resultData); } }
/// <summary> /// 汉译英联想查询 /// </summary> /// <param name="words">分词得到的关键词组</param> /// <param name="querySize">最大查询联想结果数量</param> /// <returns></returns> public static IEnumerable <DictRecord> DictC2eExtendQuery(IEnumerable <string> words, int querySize) { List <string> pNameList = new List <string> { "Size" }; List <object> pValueList = new List <object> { querySize }; List <string> sqlWhere = new List <string>(0); int idx = 0; foreach (var word in words) { string p = string.Format("@E{0}", idx), p1 = string.Format("@L{0}", idx); sqlWhere.Add(string.Format(" Word = {0} ", p)); pNameList.Add(p); pValueList.Add(word); sqlWhere.Add(string.Format(" Word LIKE {0} ", p1)); pNameList.Add(p1); pValueList.Add(string.Format("%{0}%", word)); idx++; } string querySql = @"SET ROWCOUNT @Size SELECT Word , Translation FROM Material_Dict_CE WHERE {0} ORDER BY Word"; querySql = string.Format(querySql, string.Join(" OR ", sqlWhere)); using (DbCommander cmd = new DbCommander(DbConn.ReadDb, querySql)) { cmd.AddInputParameters(string.Join(",", pNameList), pValueList.ToArray()); List <DictRecord> resultData = new List <DictRecord>(0); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { resultData.Add(new DictRecord { Word = (string)reader["Word"], TransBase = (byte[])reader["Translation"], AudioEn = "", AudioUs = "", PhonicsEn = "", PhonicsUs = "", Example = new byte[0], Explication = new byte[0] }); } reader.Close(); } return(resultData); } }
/// <summary> /// 更新跟帖内容 /// </summary> /// <param name="postInfo"></param> public static void UpdatePostContent(PostFullInfo postInfo) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_UpdateReplyContent", CommandType.StoredProcedure)) { cmd.AddInputParameters("PostId, Content", postInfo.PostId, postInfo.Content); cmd.ExecuteNonQuery(); } }
/// <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="userId">用户编号</param> /// <param name="targetId">目标数据编号</param> /// <param name="t">类型:1-帖子,2-回复</param> public static void CreateUserFavoured(int userId, int targetId, byte t) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_CreateUserFavoured", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, DataId, T, Date", userId, targetId, t, DateTime.Now); cmd.ExecuteNonQuery(); } }
/// <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="topicId"></param> public static void UpdateTopicViewCount(int topicId) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_AddTopicViewCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("TopicId", topicId); cmd.ExecuteNonQuery(); } }
/// <summary> /// 更新帖子内容等 /// </summary> /// <param name="topic"></param> public static void UpdateTopicContent(TopicFullInfo topic) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_UpdateTopicContent", CommandType.StoredProcedure)) { cmd.AddInputParameters("TopicId, Content, Icon, Voice", topic.TopicId, topic.Content, topic.Icon, topic.Voice); cmd.ExecuteNonQuery(); } }
/// <summary> /// 将指定的用户设定为非锁定状态 /// </summary> /// <param name="userId"></param> public static void SetUsesUnLocked(int userId) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UnLockUserAccount", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId", userId); cmd.ExecuteNonQuery(); } }
/// <summary> /// 更改学校试用用户账号状态信息 /// </summary> /// <param name="userWithSchool">用户相关信息</param> /// <param name="action">更改状态:0-激活,1-过期</param> public static void SchoolTrialUserStatusChanged(UserWithSchool userWithSchool, int action = 0) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_SchoolTrialUserStatusChanged", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, IsActivated, ActivatedDate, IsExpired, ExpiredDate, Action", userWithSchool.UserId, userWithSchool.IsActivated, userWithSchool.ActivatedDate, userWithSchool.IsExpired, userWithSchool.ExpiredDate, action); cmd.ExecuteNonQuery(); } }
/// <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> /// <param name="coinChanged">虚拟币账户余额变化数量</param> public static void UserCoinChanged(int userId, int coinChanged) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UserVirtualCoinChanged", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, CoinChanged", userId, coinChanged); cmd.ExecuteNonQuery(); } }
/// <summary> /// 更新用户登录成功相关信息 /// </summary> /// <param name="uExtInfo"></param> public static void UpdateUserWhenSignIn(UserExtInfo uExtInfo) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UpdateUserWhenSignIn", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, LastLoginDeviceId, LastLoginAPPChannel, LastLoginAccountChannel, LastLoginDate", uExtInfo.UserId, uExtInfo.LastLoginDeviceId, uExtInfo.LastLoginAPPChannel, uExtInfo.LastLoginAccountChannel, DateTime.Now); cmd.ExecuteNonQuery(); } }
/// <summary> /// 更新用户头像、背景图片信息 /// </summary> /// <param name="userId">用户编号</param> /// <param name="userIcon">用户图标路径</param> /// <param name="iconType">更新类型:0-头像, 1-个人信息背景图片</param> public static void UpdateUserIcon(int userId, string userIcon, byte iconType = 0) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UpdateUserIcon", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, IconUrl, Datetime, T", userId, userIcon, DateTime.Now, iconType); cmd.ExecuteNonQuery(); } }
/// <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="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> /// 指定用户,指定日期内进行签到。返回签到状态: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="device"></param> public static void SetDeviceToken(UserDevice device) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Support_DeviceSetToken", CommandType.StoredProcedure)) { cmd.AddInputParameters("DeviceId, Token", device.DeviceId, device.Token); cmd.ExecuteNonQuery(); } }
/// <summary> /// 获取指定学校、指定角色类型的用户编号列表 /// </summary> /// <param name="schoolId"></param> /// <param name="userRole"></param> /// <returns></returns> public static IEnumerable <int> GetSchoolUserIds(int schoolId, int userRole) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_School_GetCsUsers", CommandType.StoredProcedure)) { cmd.AddInputParameters("SchoolId, UserRole", schoolId, userRole); return(cmd.ExecuteDataTable().AsEnumerable().Select(dr => dr.Field <int>("UserId"))); } }
/// <summary> /// Compute Payroll New /// </summary> /// <param name="ProfileID"></param> /// <param name="CutOffDuration"></param> /// <param name="Sys_AsynTaskID"></param> public void ComputePayroll_Progress(ComputePayrollDataModel TotalData, List<Hre_ProfileEntity> ProfileID, Att_CutOffDurationEntity CutOffDuration, Guid Sys_AsynTaskID, int totalProfile, TraceLogManager FileLog, bool ComputeOrderNumber, bool Settlement = false) { using (var context = new VnrHrmDataContext()) { var unitOfWork = new UnitOfWork(context); DateTime DatetimeStart = new DateTime(); #region Init Repo var repoSys_AsynTask = new CustomBaseRepository<Sys_AsynTask>(unitOfWork); var repoHre_profile = new CustomBaseRepository<Hre_ProfileEntity>(unitOfWork); var repoCat_Element = new CustomBaseRepository<Cat_ElementEntity>(unitOfWork); var repoSal_BasicSalary = new CustomBaseRepository<Sal_BasicSalaryEntity>(unitOfWork); //var repoPayrollTable = new CustomBaseRepository<Sal_PayrollTable>(unitOfWork); //var repoPayrollTableItem = new CustomBaseRepository<Sal_PayrollTableItem>(unitOfWork); var repoCat_OvertimeType = new CustomBaseRepository<Cat_OvertimeTypeEntity>(unitOfWork); var repoCat_LeaveDayType = new CustomBaseRepository<Cat_LeaveDayTypeEntity>(unitOfWork); var repoCat_UsualAllowanceEntity = new CustomBaseRepository<Cat_UsualAllowanceEntity>(unitOfWork); var repoCat_GradePayroll = new CustomBaseRepository<Cat_GradePayrollEntity>(unitOfWork); var repoCat_UnusualAllowanceCfg = new CustomBaseRepository<Cat_UnusualAllowanceCfgEntity>(unitOfWork); var repoSal_PayrollTable = new CustomBaseRepository<Sal_PayrollTable>(unitOfWork); #endregion //Các biến xử dụng chung Sys_AsynTask asynTask = new Sys_AsynTask(); //[SCV] list lưu tiền khấu nhân viên chưa đủ thâm niên của shop Dictionary<Guid, ValueCount> listTmpDeduction = new Dictionary<Guid, ValueCount>(); List<Sal_PayrollTableItem> listPayrollTableItem = new List<Sal_PayrollTableItem>(); List<Sal_PayrollTable> listPayrollTable = new List<Sal_PayrollTable>(); //Get asynTask asynTask = repoSys_AsynTask.FindBy(m => m.ID == Sys_AsynTaskID).FirstOrDefault(); //Order By theo ngày vào làm để tính trường hợp nhân viên không đủ thâm niên của dự án SCV ProfileID = ProfileID.OrderBy(m => m.DateHire).ToList(); ParallelOptions parallelOptions = new ParallelOptions(); #region Duyệt Profile //Parallel.For(0, ProfileID.Count, parallelOptions, d => //{ //}); for (int i = 0; i < ProfileID.Count; i++) { TraceLogItemInfo logItem = new TraceLogItemInfo(); DatetimeStart = DateTime.Now; var profileItem = ProfileID[i]; bool isCancled = false; //Biến tổng lưu tất cả các value của Formula List<ElementFormula> listElementFormula = new List<ElementFormula>(); //Lấy các phần tử tính lương nằm trong Grade của nhân viên Sal_GradeEntity Grade = FindGradePayrollByProfileAndMonthYear(TotalData.listGrade, profileItem.ID, CutOffDuration.DateStart, CutOffDuration.DateEnd); List<Cat_ElementEntity> listElement = new List<Cat_ElementEntity>(); if (Grade.GradePayrollID != null && Grade.GradePayrollID != Guid.Empty) { listElement = new List<Cat_ElementEntity>(TotalData.listElement_All.Where(m => (m.GradePayrollID != null && (Guid)m.GradePayrollID == Grade.GradePayrollID) || m.IsApplyGradeAll == true)); } //loại bỏ nhân viên có ngày vào làm sau ngày chốt lương Cat_GradePayrollEntity CatGrade = TotalData.listCat_GradePayroll.Where(m => m.ID == Grade.GradePayrollID).FirstOrDefault(); if (CatGrade != null && CatGrade.SalaryTimeTypeClose != null) { DateTime DateClose = new DateTime(); if (CatGrade.SalaryTimeTypeClose == SalaryTimeTypeClose.E_CURRENTMONTH.ToString()) { DateClose = new DateTime(CutOffDuration.MonthYear.Year, CutOffDuration.MonthYear.Month, CatGrade.SalaryDayClose != null ? (int)CatGrade.SalaryDayClose > 30 && CutOffDuration.MonthYear.Month % 2 == 0 ? 30 : (int)CatGrade.SalaryDayClose : 1); } else if (CatGrade.SalaryTimeTypeClose == SalaryTimeTypeClose.E_LASTMONTH.ToString()) { DateClose = new DateTime(CutOffDuration.MonthYear.Year, CutOffDuration.MonthYear.Month, CatGrade.SalaryDayClose != null ? (int)CatGrade.SalaryDayClose > 30 && CutOffDuration.MonthYear.Month % 2 == 0 ? 30 : (int)CatGrade.SalaryDayClose : 1).AddMonths(-1); } if (profileItem.DateHire != null && (DateTime)profileItem.DateHire > DateClose) { isCancled = true; FileLog.WriteLog("NV có ngày vào làm sau ngày chốt lương của tháng", "", ""); } } if (!isCancled) { #region tạo mới PayrollTable Sal_PayrollTable PayrollTable_Model = new Sal_PayrollTable(); if (Settlement == true) { if (repoSal_PayrollTable.FindBy(m => m.CutOffDurationID == CutOffDuration.ID && m.ProfileID == profileItem.ID).FirstOrDefault() != null) { Att_CutOffDurationEntity CutOff = TotalData.listCutOffDuration.Where(m => m.MonthYear >= CutOffDuration.MonthYear).OrderBy(m => m.MonthYear).FirstOrDefault(); PayrollTable_Model = new Sal_PayrollTable() { ID = Guid.NewGuid(), ProfileID = profileItem.ID, CutOffDurationID = CutOff.ID, MonthYear = CutOff.MonthYear, OrgStructureID = profileItem.OrgStructureID, PositionID = profileItem.PositionID, JobTitleID = profileItem.JobTitleID, EmployeeTypeID = profileItem.EmpTypeID, PayrollGroupID = profileItem.PayrollGroupID, CostCentreID = profileItem.CostCentreID, IncomeBeforeTax = 0, IsCash = profileItem.IsCash, DependantCount = 0, IncomeTaxable = 0, AmountPaidPITCom = 0, AmountPaidPITEmp = 0, IncomeNET = 0, BankID = profileItem.BankID, AccountNo = profileItem.AccountNo, Status = FileLog.GetFullPath(FileLog.FileName).Replace(".XML", "_" + DateTime.Now.ToString("yyyyMMdd") + ".XML"), }; } else { PayrollTable_Model = new Sal_PayrollTable() { ID = Guid.NewGuid(), ProfileID = profileItem.ID, CutOffDurationID = CutOffDuration.ID, MonthYear = CutOffDuration.MonthYear, OrgStructureID = profileItem.OrgStructureID, PositionID = profileItem.PositionID, JobTitleID = profileItem.JobTitleID, EmployeeTypeID = profileItem.EmpTypeID, PayrollGroupID = profileItem.PayrollGroupID, CostCentreID = profileItem.CostCentreID, IncomeBeforeTax = 0, DependantCount = 0, IncomeTaxable = 0, AmountPaidPITCom = 0, AmountPaidPITEmp = 0, IncomeNET = 0, BankID = profileItem.BankID, AccountNo = profileItem.AccountNo, Status = FileLog.GetFullPath(FileLog.FileName).Replace(".XML", "_" + DateTime.Now.ToString("yyyyMMdd") + ".XML"), }; } } else { PayrollTable_Model = new Sal_PayrollTable() { ID = Guid.NewGuid(), ProfileID = profileItem.ID, CutOffDurationID = CutOffDuration.ID, MonthYear = CutOffDuration.MonthYear, OrgStructureID = profileItem.OrgStructureID, PositionID = profileItem.PositionID, JobTitleID = profileItem.JobTitleID, EmployeeTypeID = profileItem.EmpTypeID, PayrollGroupID = profileItem.PayrollGroupID, CostCentreID = profileItem.CostCentreID, IncomeBeforeTax = 0, DependantCount = 0, IncomeTaxable = 0, AmountPaidPITCom = 0, AmountPaidPITEmp = 0, IncomeNET = 0, BankID = profileItem.BankID, AccountNo = profileItem.AccountNo, //Status = FileLog.GetFullPath(FileLog.FileName).Replace(".XML", "_" + DateTime.Now.ToString("yyyyMMdd") + ".XML"), }; } lock (listPayrollTable) { listPayrollTable.Add(PayrollTable_Model); } #endregion //bắt lỗi tính công thức có giá trị nào null hay không try { //DatetimeStart = DateTime.Now; listElementFormula = ParseElementFormula(listElementFormula, listElement, TotalData, profileItem, CutOffDuration, listTmpDeduction, ComputeOrderNumber, FileLog); //FileLog.WriteLog("", "Compute Formula", Common.ComputeTime(DatetimeStart, DateTime.Now)); listElement = listElement.OrderBy(m => m.OrderNumber).ToList();//sắp xếp lại //Duyệt qua các phần tử tính lương foreach (var elementItem in listElement) { Sal_PayrollTableItem tableItem = new Sal_PayrollTableItem(); tableItem.ID = Guid.NewGuid(); tableItem.PayrollTableID = PayrollTable_Model.ID; tableItem.Name = elementItem.ElementName; tableItem.Code = elementItem.ElementCode; tableItem.MonthYear = CutOffDuration.MonthYear; tableItem.IsDecrypt = false; tableItem.IsAddToHourlyRate = false; tableItem.IsChargePIT = false; tableItem.OrderNo = elementItem.OrderNumber != null ? (int)elementItem.OrderNumber : 0; tableItem.ElementType = elementItem.TabType; tableItem.ValueType = elementItem.Type; tableItem.IsShow = elementItem.Invisible != null ? !elementItem.Invisible : true; if (elementItem.IsBold == true) { tableItem.Description4 += "E_BOLD,"; } tableItem.Description4 += elementItem.ElementLevel + "," + elementItem.Type; var ElementResult = listElementFormula.Where(m => m.VariableName.Trim() == elementItem.ElementCode.Trim()); if (ElementResult != null) { tableItem.Value = ElementResult.LastOrDefault().Value.ToString(); tableItem.Description1 = ElementResult.LastOrDefault().ErrorMessage; } else { tableItem.Value = "0"; tableItem.Description1 = "Không Tìm Thấy Phần Tử !"; } lock (listPayrollTableItem) { listPayrollTableItem.Add(tableItem); } } logItem.Summary += profileItem.CodeEmp + "-" + profileItem.ProfileName + "-" + Common.ComputeTime(DatetimeStart, DateTime.Now) + "-" + listElement.Count.ToString() + "Element Formula"; logItem.Source = "Sucess"; FileLog.WriteLog(logItem); } catch (Exception ex) { logItem.Summary += profileItem.CodeEmp + "-" + profileItem.ProfileName + "-" + Common.ComputeTime(DatetimeStart, DateTime.Now) + "-" + listElement.Count.ToString() + " Element Formula"; logItem.Description = ex.Message; logItem.Source = "Error"; FileLog.WriteLog(logItem); //FileLog.WriteLog("Error", "Error Compute Profle " + ProfileID[i].CodeEmp + "-" + ProfileID[i].ProfileName, Common.ComputeTime(DatetimeStart, DateTime.Now)); Sal_PayrollTableItem tableItem = new Sal_PayrollTableItem(); tableItem.ID = Guid.NewGuid(); tableItem.PayrollTableID = PayrollTable_Model.ID; tableItem.Name = "Lỗi, không thể tính được phần tử " + ex.Message; tableItem.Code = "Error"; tableItem.MonthYear = CutOffDuration.MonthYear; tableItem.IsDecrypt = false; tableItem.IsAddToHourlyRate = false; tableItem.IsChargePIT = false; tableItem.OrderNo = 0; tableItem.ElementType = "Payroll"; tableItem.ValueType = "Double"; tableItem.Description4 = "Double"; tableItem.Value = "0"; tableItem.Description1 = ex.Message; lock (listPayrollTableItem) { listPayrollTableItem.Add(tableItem); } } } } #endregion asynTask = repoSys_AsynTask.FindBy(m => m.ID == Sys_AsynTaskID).FirstOrDefault(); asynTask.PercentComplete += ((double)ProfileID.Count / (double)totalProfile); asynTask.Description = FileLog.GetFullPath(FileLog.FileName).Replace(".XML", "_" + DateTime.Now.ToString("yyyyMMdd") + ".XML"); asynTask.TimeEnd = DateTime.Now; if (asynTask.PercentComplete >= 1) { asynTask.Status = AsynTaskStatus.Done.ToString(); asynTask.PercentComplete = 1D; } //if (dataErrorCode == DataErrorCode.Locked) //{ // asynTask.PercentComplete = 1D; // asynTask.Description = "Dữ Liệu Tính Lương Đã Bị Khóa"; // FileLog.WriteLog("Dữ liệu tính lương bị khóa", "--------------------", "--------------------"); //} DatetimeStart = DateTime.Now; var connection = context.Database.Connection.GetAdoConnection(); using (DbCommander commander = new DbCommander(connection)) { if (connection.IsSqlConnection()) { var bulkCopyHelper = new SqlBulkCopyHelper(connection.ConnectionString); var dtPayrollTable = commander.GetSchema("Columns", new string[] { null, null, typeof(Sal_PayrollTable).Name }); var dtPayrollTableItem = commander.GetSchema("Columns", new string[] { null, null, typeof(Sal_PayrollTableItem).Name }); var payrollTableFields = dtPayrollTable.Rows.OfType<System.Data.DataRow>().Select(d => d["Column_Name"].GetString()).ToArray(); var payrollTableItemFields = dtPayrollTableItem.Rows.OfType<System.Data.DataRow>().Select(d => d["Column_Name"].GetString()).ToArray(); bulkCopyHelper.WriteToServer(listPayrollTable, typeof(Sal_PayrollTable).Name, payrollTableFields); foreach (var listPayrollTableItemBySize in listPayrollTableItem.Chunk(2000)) { try { bulkCopyHelper.WriteToServer(listPayrollTableItemBySize.ToList(), typeof(Sal_PayrollTableItem).Name, payrollTableItemFields); } catch (Exception ex) { FileLog.WriteLog(ex.Message, "", ""); } } } else { var dtPayrollTable = commander.GetSchema("Columns", new string[] { null, typeof(Sal_PayrollTable).Name }); var dtPayrollTableItem = commander.GetSchema("Columns", new string[] { null, typeof(Sal_PayrollTableItem).Name }); var payrollTableFields = dtPayrollTable.Rows.OfType<System.Data.DataRow>().Select(d => d["Name"].GetString()).ToArray(); var payrollTableItemFields = dtPayrollTableItem.Rows.OfType<System.Data.DataRow>().Select(d => d["Name"].GetString()).ToArray(); commander.InsertList(typeof(Sal_PayrollTable).Name, listPayrollTable, payrollTableFields); foreach (var listPayrollTableItemBySize in listPayrollTableItem.Chunk(2000)) { commander.InsertList(typeof(Sal_PayrollTableItem).Name, listPayrollTableItemBySize.ToList(), payrollTableItemFields); } } } FileLog.WriteLog("", "", "Save Change 100 Profile-" + Common.ComputeTime(DatetimeStart, DateTime.Now)); //repoPayrollTableItem.Add(listPayrollTableItem); //repoPayrollTable.Add(listPayrollTable); unitOfWork.SaveChanges(); } }
public void ComputeCommission_Progress(ComputePayrollDataModel TotalData, List<Hre_ProfileEntity> ProfileID, Att_CutOffDurationEntity CutOffDuration, Guid Sys_AsynTaskID, int totalProfile, string methodPayroll, Guid CutOffDuration2ID) { using (var context = new VnrHrmDataContext()) { TraceLogManager FileLog = new TraceLogManager(); var unitOfWork = new UnitOfWork(context); #region Init Repo var repoSys_AsynTask = new CustomBaseRepository<Sys_AsynTask>(unitOfWork); var repoHre_profile = new CustomBaseRepository<Hre_ProfileEntity>(unitOfWork); var repoCat_Element = new CustomBaseRepository<Cat_ElementEntity>(unitOfWork); var repoSal_BasicSalary = new CustomBaseRepository<Sal_BasicSalaryEntity>(unitOfWork); //var repoPayrollTable = new CustomBaseRepository<Sal_PayrollTable>(unitOfWork); //var repoPayrollTableItem = new CustomBaseRepository<Sal_PayrollTableItem>(unitOfWork); var repoCat_OvertimeType = new CustomBaseRepository<Cat_OvertimeTypeEntity>(unitOfWork); var repoCat_LeaveDayType = new CustomBaseRepository<Cat_LeaveDayTypeEntity>(unitOfWork); var repoCat_UsualAllowanceEntity = new CustomBaseRepository<Cat_UsualAllowanceEntity>(unitOfWork); var repoCat_GradePayroll = new CustomBaseRepository<Cat_GradePayrollEntity>(unitOfWork); var repoCat_UnusualAllowanceCfg = new CustomBaseRepository<Cat_UnusualAllowanceCfgEntity>(unitOfWork); var repoSal_PayrollTable = new CustomBaseRepository<Sal_PayrollTable>(unitOfWork); #endregion //Các biến xử dụng chung Sys_AsynTask asynTask = new Sys_AsynTask(); //[SCV] list lưu tiền khấu nhân viên chưa đủ thâm niên của shop Dictionary<Guid, ValueCount> listTmpDeduction = new Dictionary<Guid, ValueCount>(); List<Sal_PayCommissionItem> listPayrollTableItem = new List<Sal_PayCommissionItem>(); List<Sal_PayCommission> listPayrollTable = new List<Sal_PayCommission>(); //Get asynTask asynTask = repoSys_AsynTask.FindBy(m => m.ID == Sys_AsynTaskID).FirstOrDefault(); //Order By theo ngày vào làm để tính trường hợp nhân viên không đủ thâm niên của dự án SCV ProfileID = ProfileID.OrderBy(m => m.DateHire).ToList(); ParallelOptions parallelOptions = new ParallelOptions(); #region Duyệt Profile Parallel.For(0, ProfileID.Count, parallelOptions, d => { var profileItem = ProfileID[d]; bool isCancled = false; //Biến tổng lưu tất cả các value của Formula List<ElementFormula> listElementFormula = new List<ElementFormula>(); //Lấy các phần tử tính lương nằm trong Grade của nhân viên Sal_GradeEntity Grade = PayrollServices.FindGradePayrollByProfileAndMonthYear(TotalData.listGrade, profileItem.ID, CutOffDuration.DateStart, CutOffDuration.DateEnd); //lấy phần tử tính lương theo grade List<Cat_ElementEntity> listElement = TotalData.listElement_All.Where(m => m.GradePayrollID != null && (Guid)m.GradePayrollID == Grade.GradePayrollID && m.MethodPayroll != null && m.MethodPayroll == methodPayroll).ToList(); if (!isCancled) { #region tạo mới PayrollTable Sal_PayCommission PayrollTable_Model = new Sal_PayCommission() { ID = Guid.NewGuid(), ProfileID = profileItem.ID, CutoffDurationID = CutOffDuration.ID, MonthYear = CutOffDuration.MonthYear, CutoffDuration2ID = CutOffDuration2ID, //OrgStructureID = profileItem.OrgStructureID, //PositionID = profileItem.PositionID, //JobTitleID = profileItem.JobTitleID, //EmployeeTypeID = profileItem.EmpTypeID, //PayrollGroupID = profileItem.PayrollGroupID, //CostCentreID = profileItem.CostCentreID, //IncomeBeforeTax = 0, //DependantCount = 0, //IncomeTaxable = 0, //AmountPaidPITCom = 0, //AmountPaidPITEmp = 0, //IncomeNET = 0, //BankID = profileItem.BankID, //AccountNo = profileItem.AccountNo, }; lock (listPayrollTable) { listPayrollTable.Add(PayrollTable_Model); } #endregion //bắt lỗi tính công thức có giá trị nào null hay không try { listElementFormula = PayrollServices.ParseElementFormula(listElementFormula, listElement, TotalData, profileItem, CutOffDuration, listTmpDeduction,false, FileLog); listElement = listElement.OrderBy(m => m.OrderNumber).ToList();//sắp xếp lại //Duyệt qua các phần tử tính lương foreach (var elementItem in listElement) { Sal_PayCommissionItem tableItem = new Sal_PayCommissionItem(); tableItem.ID = Guid.NewGuid(); tableItem.PayCommissionID = PayrollTable_Model.ID; tableItem.Name = elementItem.ElementName; tableItem.Code = elementItem.ElementCode; tableItem.OrderNo = elementItem.OrderNumber != null ? (int)elementItem.OrderNumber : 0; tableItem.ElementType = elementItem.TabType; tableItem.ValueType = elementItem.Type; tableItem.IsShow = elementItem.Invisible != null ? !elementItem.Invisible : true; if (elementItem.IsBold == true) { tableItem.Description += "E_BOLD,"; } tableItem.Description += elementItem.ElementLevel + "," + elementItem.Type; var ElementResult = listElementFormula.Where(m => m.VariableName.Trim() == elementItem.ElementCode.Trim()).FirstOrDefault(); if (ElementResult != null) { tableItem.Value = ElementResult.Value.ToString(); tableItem.Description = ElementResult.ErrorMessage; } else { tableItem.Value = "0"; tableItem.Description = "Không Tìm Thấy Phần Tử !"; } lock (listPayrollTableItem) { listPayrollTableItem.Add(tableItem); } } } catch (Exception ex) { Sal_PayCommissionItem tableItem = new Sal_PayCommissionItem(); tableItem.ID = Guid.NewGuid(); tableItem.PayCommissionID = PayrollTable_Model.ID; tableItem.Name = "Lỗi, không thể tính được phần tử " + ex.Message; tableItem.Code = "Error"; tableItem.OrderNo = 0; tableItem.ElementType = "Payroll"; tableItem.ValueType = "Double"; tableItem.Value = "0"; tableItem.Description = ex.Message; lock (listPayrollTableItem) { listPayrollTableItem.Add(tableItem); } } } }); #endregion asynTask = repoSys_AsynTask.FindBy(m => m.ID == Sys_AsynTaskID).FirstOrDefault(); asynTask.PercentComplete += ((double)ProfileID.Count / (double)totalProfile); asynTask.Description = FileLog.GetFullPath(FileLog.FileName); asynTask.TimeEnd = DateTime.Now; if (asynTask.PercentComplete >= 1) { asynTask.Status = AsynTaskStatus.Done.ToString(); asynTask.PercentComplete = 1D; } var connection = context.Database.Connection.GetAdoConnection(); using (DbCommander commander = new DbCommander(connection)) { if (connection.IsSqlConnection()) { var bulkCopyHelper = new SqlBulkCopyHelper(connection.ConnectionString); var dtPayrollTable = commander.GetSchema("Columns", new string[] { null, null, typeof(Sal_PayCommission).Name }); var dtPayrollTableItem = commander.GetSchema("Columns", new string[] { null, null, typeof(Sal_PayCommissionItem).Name }); var payrollTableFields = dtPayrollTable.Rows.OfType<System.Data.DataRow>().Select(d => d["Column_Name"].GetString()).ToArray(); var payrollTableItemFields = dtPayrollTableItem.Rows.OfType<System.Data.DataRow>().Select(d => d["Column_Name"].GetString()).ToArray(); bulkCopyHelper.WriteToServer(listPayrollTable, typeof(Sal_PayCommission).Name, payrollTableFields); foreach (var listPayrollTableItemBySize in listPayrollTableItem.Chunk(2000)) { bulkCopyHelper.WriteToServer(listPayrollTableItemBySize.ToList(), typeof(Sal_PayCommissionItem).Name, payrollTableItemFields); } } else { var dtPayrollTable = commander.GetSchema("Columns", new string[] { null, typeof(Sal_PayCommission).Name }); var dtPayrollTableItem = commander.GetSchema("Columns", new string[] { null, typeof(Sal_PayCommissionItem).Name }); var payrollTableFields = dtPayrollTable.Rows.OfType<System.Data.DataRow>().Select(d => d["Name"].GetString()).ToArray(); var payrollTableItemFields = dtPayrollTableItem.Rows.OfType<System.Data.DataRow>().Select(d => d["Name"].GetString()).ToArray(); commander.InsertList(typeof(Sal_PayCommission).Name, listPayrollTable, payrollTableFields); foreach (var listPayrollTableItemBySize in listPayrollTableItem.Chunk(2000)) { commander.InsertList(typeof(Sal_PayCommissionItem).Name, listPayrollTableItemBySize.ToList(), payrollTableItemFields); } } } //repoPayrollTableItem.Add(listPayrollTableItem); //repoPayrollTable.Add(listPayrollTable); unitOfWork.SaveChanges(); } }
public string ExecuteStringCommand(string strCommand) { using (var context = new VnrHrmDataContext()) { var connection = context.Database.Connection; using (DbCommander commander = new DbCommander(connection)) { try { commander.ExecuteNonQuery(strCommand); } catch (Exception e) { if (!e.ToString().Contains("There is already an object named") && !e.ToString().Contains("name is already used by an existing object")) return e.ToString(); } } } return string.Empty; }
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; } }
public bool SaveInterviewResult() { bool result = true; if (ImportObjects.ContainsKey(UserID)) { using (var context = new VnrHrmDataContext()) { IUnitOfWork unitOfWork = new UnitOfWork(context); var dtImportObject = ImportObjects[UserID]; var dtInterview = new DataTable("Rec_Interview"); dtInterview.Columns.Add("ID", typeof(Guid)); dtInterview.Columns.Add("CandidateID", typeof(Guid)); dtInterview.Columns.Add("JobVacancyID", typeof(Guid)); dtInterview.Columns.Add("LanguageID", typeof(Guid)); dtInterview.Columns.Add("GroupConditionID", typeof(Guid)); dtInterview.Columns.Add("RecruitmentHistoryID", typeof(Guid)); dtInterview.Columns.Add("Score", typeof(double)); dtInterview.Columns.Add("Score1", typeof(double)); dtInterview.Columns.Add("Score2", typeof(double)); dtInterview.Columns.Add("ResultInterview", typeof(string)); dtInterview.Columns.Add("LevelInterview", typeof(int)); var dtCandidate = new DataTable("Rec_Candidate"); dtCandidate.Columns.Add("ID", typeof(Guid)); dtCandidate.Columns.Add("Status", typeof(string)); var dtHisstorry = new DataTable("Rec_RecruitmentHistory"); dtHisstorry.Columns.Add("ID", typeof(Guid)); dtHisstorry.Columns.Add("Status", typeof(string)); foreach (DataRow importRow in dtImportObject.Rows) { var candidateStatus = EnumDropDown.CandidateStatus.E_PASS; var candidateID = importRow["CandidateID"]; object groupResult = null; if (!importRow.IsNull(FieldMappings["GroupResult1"])) { groupResult = importRow[FieldMappings["GroupResult1"]]; DataRow interviewRow1 = dtInterview.NewRow(); dtInterview.Rows.Add(interviewRow1); interviewRow1.SetField("ID", Guid.NewGuid()); var interviewID = importRow["InterviewID1"]; if (interviewID != null && interviewID.IsTypeOf(typeof(Guid))) { if (interviewID.GetString() != Guid.Empty.ToString()) { interviewRow1.SetField("ID", interviewID); interviewRow1.AcceptChanges();//update mode } } interviewRow1.SetField("CandidateID", candidateID); interviewRow1.SetField("GroupConditionID", importRow["GroupConditionID1"]); interviewRow1.SetField("RecruitmentHistoryID", importRow["RecruitmentHistoryID"]); interviewRow1.SetField("Score", importRow[FieldMappings["Group1Score1"]]); interviewRow1.SetField("Score1", importRow[FieldMappings["Group1Score2"]]); interviewRow1.SetField("Score2", importRow[FieldMappings["Group1Score3"]]); interviewRow1.SetField("ResultInterview", groupResult); interviewRow1.SetField("LevelInterview", 1); if (!importRow.IsNull("JobVacancyID")) { interviewRow1.SetField("JobVacancyID", importRow["JobVacancyID"]); } if (!importRow.IsNull("LanguageID")) { interviewRow1.SetField("LanguageID", importRow["LanguageID"]); } if (groupResult.GetString() == EnumDropDown.CandidateStatus.E_FAIL.ToString()) { candidateStatus = EnumDropDown.CandidateStatus.E_FAIL; } } if (!importRow.IsNull(FieldMappings["GroupResult2"])) { groupResult = importRow[FieldMappings["GroupResult2"]]; DataRow interviewRow2 = dtInterview.NewRow(); dtInterview.Rows.Add(interviewRow2); interviewRow2.SetField("ID", Guid.NewGuid()); var interviewID = importRow["InterviewID2"]; if (interviewID != null && interviewID.IsTypeOf(typeof(Guid))) { if (interviewID.GetString() != Guid.Empty.ToString()) { interviewRow2.SetField("ID", interviewID); interviewRow2.AcceptChanges();//update mode } } interviewRow2.SetField("CandidateID", candidateID); interviewRow2.SetField("GroupConditionID", importRow["GroupConditionID2"]); interviewRow2.SetField("RecruitmentHistoryID", importRow["RecruitmentHistoryID"]); interviewRow2.SetField("Score", importRow[FieldMappings["Group2Score1"]]); interviewRow2.SetField("Score1", importRow[FieldMappings["Group2Score2"]]); interviewRow2.SetField("Score2", importRow[FieldMappings["Group2Score3"]]); interviewRow2.SetField("ResultInterview", groupResult); interviewRow2.SetField("LevelInterview", 2); if (!importRow.IsNull("JobVacancyID")) { interviewRow2.SetField("JobVacancyID", importRow["JobVacancyID"]); } if (!importRow.IsNull("LanguageID")) { interviewRow2.SetField("LanguageID", importRow["LanguageID"]); } if (groupResult.GetString() == EnumDropDown.CandidateStatus.E_FAIL.ToString()) { candidateStatus = EnumDropDown.CandidateStatus.E_FAIL; } } if (!importRow.IsNull(FieldMappings["GroupResult3"])) { groupResult = importRow[FieldMappings["GroupResult3"]]; DataRow interviewRow3 = dtInterview.NewRow(); dtInterview.Rows.Add(interviewRow3); interviewRow3.SetField("ID", Guid.NewGuid()); var interviewID = importRow["InterviewID3"]; if (interviewID != null && interviewID.IsTypeOf(typeof(Guid))) { if (interviewID.GetString() != Guid.Empty.ToString()) { interviewRow3.SetField("ID", interviewID); interviewRow3.AcceptChanges();//update mode } } interviewRow3.SetField("CandidateID", candidateID); interviewRow3.SetField("GroupConditionID", importRow["GroupConditionID3"]); interviewRow3.SetField("RecruitmentHistoryID", importRow["RecruitmentHistoryID"]); interviewRow3.SetField("Score", importRow[FieldMappings["Group3Score1"]]); interviewRow3.SetField("Score1", importRow[FieldMappings["Group3Score2"]]); interviewRow3.SetField("Score2", importRow[FieldMappings["Group3Score3"]]); interviewRow3.SetField("ResultInterview", groupResult); interviewRow3.SetField("LevelInterview", 3); if (!importRow.IsNull("JobVacancyID")) { interviewRow3.SetField("JobVacancyID", importRow["JobVacancyID"]); } if (!importRow.IsNull("LanguageID")) { interviewRow3.SetField("LanguageID", importRow["LanguageID"]); } if (groupResult.GetString() == EnumDropDown.CandidateStatus.E_FAIL.ToString()) { candidateStatus = EnumDropDown.CandidateStatus.E_FAIL; } } if (!importRow.IsNull(FieldMappings["GroupResult4"])) { groupResult = importRow[FieldMappings["GroupResult4"]]; DataRow interviewRow4 = dtInterview.NewRow(); dtInterview.Rows.Add(interviewRow4); interviewRow4.SetField("ID", Guid.NewGuid()); var interviewID = importRow["InterviewID4"]; if (interviewID != null && interviewID.IsTypeOf(typeof(Guid))) { if (interviewID.GetString() != Guid.Empty.ToString()) { interviewRow4.SetField("ID", interviewID); interviewRow4.AcceptChanges();//update mode } } interviewRow4.SetField("CandidateID", candidateID); interviewRow4.SetField("GroupConditionID", importRow["GroupConditionID4"]); interviewRow4.SetField("RecruitmentHistoryID", importRow["RecruitmentHistoryID"]); interviewRow4.SetField("Score", importRow[FieldMappings["Group4Score1"]]); interviewRow4.SetField("Score1", importRow[FieldMappings["Group4Score2"]]); interviewRow4.SetField("Score2", importRow[FieldMappings["Group4Score3"]]); interviewRow4.SetField("ResultInterview", groupResult); interviewRow4.SetField("LevelInterview", 4); if (!importRow.IsNull("JobVacancyID")) { interviewRow4.SetField("JobVacancyID", importRow["JobVacancyID"]); } if (!importRow.IsNull("LanguageID")) { interviewRow4.SetField("LanguageID", importRow["LanguageID"]); } if (groupResult.GetString() == EnumDropDown.CandidateStatus.E_FAIL.ToString()) { candidateStatus = EnumDropDown.CandidateStatus.E_FAIL; } } if (!importRow.IsNull(FieldMappings["GroupResult5"])) { groupResult = importRow[FieldMappings["GroupResult5"]]; DataRow interviewRow5 = dtInterview.NewRow(); dtInterview.Rows.Add(interviewRow5); interviewRow5.SetField("ID", Guid.NewGuid()); var interviewID = importRow["InterviewID5"]; if (interviewID != null && interviewID.IsTypeOf(typeof(Guid))) { if (interviewID.GetString() != Guid.Empty.ToString()) { interviewRow5.SetField("ID", interviewID); interviewRow5.AcceptChanges();//update mode } } interviewRow5.SetField("CandidateID", candidateID); interviewRow5.SetField("GroupConditionID", importRow["GroupConditionID5"]); interviewRow5.SetField("RecruitmentHistoryID", importRow["RecruitmentHistoryID"]); interviewRow5.SetField("Score", importRow[FieldMappings["Group5Score1"]]); interviewRow5.SetField("Score1", importRow[FieldMappings["Group5Score2"]]); interviewRow5.SetField("Score2", importRow[FieldMappings["Group5Score3"]]); interviewRow5.SetField("ResultInterview", groupResult); interviewRow5.SetField("LevelInterview", 5); if (!importRow.IsNull("JobVacancyID")) { interviewRow5.SetField("JobVacancyID", importRow["JobVacancyID"]); } if (!importRow.IsNull("LanguageID")) { interviewRow5.SetField("LanguageID", importRow["LanguageID"]); } if (groupResult.GetString() == EnumDropDown.CandidateStatus.E_FAIL.ToString()) { candidateStatus = EnumDropDown.CandidateStatus.E_FAIL; } } if (!importRow.IsNull(FieldMappings["GroupResult6"])) { groupResult = importRow[FieldMappings["GroupResult6"]]; DataRow interviewRow6 = dtInterview.NewRow(); dtInterview.Rows.Add(interviewRow6); interviewRow6.SetField("ID", Guid.NewGuid()); var interviewID = importRow["InterviewID6"]; if (interviewID != null && interviewID.IsTypeOf(typeof(Guid))) { if (interviewID.GetString() != Guid.Empty.ToString()) { interviewRow6.SetField("ID", interviewID); interviewRow6.AcceptChanges();//update mode } } interviewRow6.SetField("CandidateID", candidateID); interviewRow6.SetField("GroupConditionID", importRow["GroupConditionID6"]); interviewRow6.SetField("RecruitmentHistoryID", importRow["RecruitmentHistoryID"]); interviewRow6.SetField("Score", importRow[FieldMappings["Group6Score1"]]); interviewRow6.SetField("Score1", importRow[FieldMappings["Group6Score2"]]); interviewRow6.SetField("Score2", importRow[FieldMappings["Group6Score3"]]); interviewRow6.SetField("ResultInterview", groupResult); interviewRow6.SetField("LevelInterview", 6); if (!importRow.IsNull("JobVacancyID")) { interviewRow6.SetField("JobVacancyID", importRow["JobVacancyID"]); } if (!importRow.IsNull("LanguageID")) { interviewRow6.SetField("LanguageID", importRow["LanguageID"]); } if (groupResult.GetString() == EnumDropDown.CandidateStatus.E_FAIL.ToString()) { candidateStatus = EnumDropDown.CandidateStatus.E_FAIL; } } if (!importRow.IsNull(FieldMappings["GroupResult7"])) { groupResult = importRow[FieldMappings["GroupResult7"]]; DataRow interviewRow7 = dtInterview.NewRow(); dtInterview.Rows.Add(interviewRow7); interviewRow7.SetField("ID", Guid.NewGuid()); var interviewID = importRow["InterviewID7"]; if (interviewID != null && interviewID.IsTypeOf(typeof(Guid))) { if (interviewID.GetString() != Guid.Empty.ToString()) { interviewRow7.SetField("ID", interviewID); interviewRow7.AcceptChanges();//update mode } } interviewRow7.SetField("CandidateID", candidateID); interviewRow7.SetField("GroupConditionID", importRow["GroupConditionID7"]); interviewRow7.SetField("RecruitmentHistoryID", importRow["RecruitmentHistoryID"]); interviewRow7.SetField("Score", importRow[FieldMappings["Group7Score1"]]); interviewRow7.SetField("Score1", importRow[FieldMappings["Group7Score2"]]); interviewRow7.SetField("Score2", importRow[FieldMappings["Group7Score3"]]); interviewRow7.SetField("ResultInterview", groupResult); interviewRow7.SetField("LevelInterview", 7); if (!importRow.IsNull("JobVacancyID")) { interviewRow7.SetField("JobVacancyID", importRow["JobVacancyID"]); } if (!importRow.IsNull("LanguageID")) { interviewRow7.SetField("LanguageID", importRow["LanguageID"]); } if (groupResult.GetString() == EnumDropDown.CandidateStatus.E_FAIL.ToString()) { candidateStatus = EnumDropDown.CandidateStatus.E_FAIL; } } if (groupResult != null) { DataRow candidateRow = dtCandidate.NewRow(); dtCandidate.Rows.Add(candidateRow); candidateRow.AcceptChanges();//Chuyển qua update candidateRow.SetField("ID", candidateID); candidateRow.SetField("Status", candidateStatus.ToString()); var historyID = importRow["RecruitmentHistoryID"]; if (historyID != null && historyID.IsTypeOf(typeof(Guid))) { if (historyID.GetString() != Guid.Empty.ToString()) { DataRow hisstorryRow = dtHisstorry.NewRow(); dtHisstorry.Rows.Add(hisstorryRow); hisstorryRow.AcceptChanges();//Chuyển qua update hisstorryRow.SetField("ID", historyID); hisstorryRow.SetField("Status", candidateStatus.ToString()); } } } } using (DbCommander commander = new DbCommander(context.Database.Connection)) { var transaction = commander.BeginTransaction(); try { var resultCount = commander.ExecuteBatch(transaction, dtInterview); resultCount = commander.UpdateTable(transaction, dtCandidate); resultCount = commander.UpdateTable(transaction, dtHisstorry); transaction.Commit(); } catch (Exception) { result = false; transaction.Rollback(); throw; } } } } if (result) { if (ImportObjects.ContainsKey(UserID)) { ImportObjects[UserID] = null; ImportObjects.Remove(UserID); } if (InvalidObjects.ContainsKey(UserID)) { InvalidObjects[UserID] = null; InvalidObjects.Remove(UserID); } } return result; }