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