/// <summary>
///提交考试记录失败更新操作表状态
/// </summary>
/// <param name="id">考试操作表id</param>
/// <param name="failMessage">提交失败文字说明</param>
/// <param name="sendTimes">考试记录累积处理次数</param>
internal void UpdateExamSendTimes(int id, string failMessage, int sendTimes)
{
string sqlStr = string.Empty;
if (sendTimes < 2)
{
if (sendTimes == 0)
{
sqlStr = string.Format(@"UPDATE Reform_ExamRecords SET ResultMsg='{0}',SendTimes=1,SendTime='{1}' WHERE Id={2}", failMessage, DateTime.Now.ToString(), id);
}
else
{
sqlStr = string.Format(@"UPDATE Reform_ExamRecords SET ResultMsg='{0}',SendTimes=SendTimes+1,UpdateTime='{1}' WHERE Id={2}", failMessage, DateTime.Now.ToString(), id);
}
}
else
{
sqlStr = string.Format(@"UPDATE Reform_ExamRecords SET ResultMsg='{0}',Status=2,SendTimes=SendTimes+1,UpdateTime='{1}' WHERE Id={2}", failMessage, DateTime.Now.ToString(), id);
}
try
{
SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.Text, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog("提交考试记录失败更新操作表状态时出现异常：" + ex.Message + ";sql=" + sqlStr, "Exam");
}
}
/// <summary>
/// 获取考试记录操作表中所有的项目编号
/// </summary>
/// <returns></returns>
internal DataTable GetExamProjectCode()
{
DataTable dt = null;
string sqlStr = "SELECT ProjectCode FROM Reform_ExamRecords GROUP BY ProjectCode ";
try
{
dt = SqlHelper.ExecuteTable(SqlHelper.connString, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog("获取考试记录操作表中所有的项目编号时出现异常：" + ex.Message + ";sql=" + sqlStr, "Exam");
}
return(dt);
}
/// <summary>
/// 获取考试记录操作表中未处理的记录
/// </summary>
/// <param name="projectCode">项目编号</param>
/// <returns></returns>
internal DataTable GetExamRecords(string projectCode)
{
DataTable dt = null;
string sqlStr = string.Format(@"SELECT TOP 100 Id, ProjectCode, StudentNo, Score, ExamTime, ExamName, Status, SendTimes,ClassId FROM Reform_ExamRecords WHERE ProjectCode='{0}' AND Status=0", projectCode);
try
{
dt = SqlHelper.ExecuteTable(SqlHelper.connString, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog("获取考试记录操作表中未处理的记录时出现异常：" + ex.Message + ";sql=" + sqlStr, "Exam");
}
return(dt);
}
/// <summary>
/// 获取该项目下还未处理的支付记录
/// </summary>
/// <param name="projectCode">项目编号</param>
/// <returns></returns>
internal DataTable GetPayRecords(string projectCode)
{
DataTable dt = null;
string sqlStr = string.Format(@"SELECT TOP 100 Id, ProjectCode, StudentNo, PayAmount, PayTime, PayDesc,SendTimes,ClassId FROM Reform_PayRecords WHERE ProjectCode='{0}' AND Status=0", projectCode);
try
{
dt = SqlHelper.ExecuteTable(SqlHelper.connString, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog("获取该项目下还未处理的支付记录时出现异常：" + ex.Message + ";sql=" + sqlStr, "Pay");
}
return(dt);
}
/// <summary>
/// 根据项目编号获取项目id
/// </summary>
/// <param name="projectCode"></param>
/// <returns></returns>
private int GetCourseIdByProjectCode(string projectCode)
{
int courseId = 0;
string sqlStr = string.Format(@"SELECT TOP 1 CourseId FROM Res_Course WHERE ProjectCode='{0}' ORDER BY CourseId DESC", projectCode);
try
{
courseId = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connString, CommandType.Text, sqlStr));
}
catch (Exception ex)
{
CommLog.WriteExceptionLog(" 根据项目编号获取项目id时出现异常：" + ex.Message + ";Sql=" + sqlStr, "SignUp");
}
return(courseId);
}
/// <summary>
/// 添加/修改学员信息成功后更新操作表的状态
/// </summary>
/// <param name="id">操作表id</param>
/// <param name="studentNo">学号</param>
internal void UpdateStudentStatus(int id, string studentNo, string code, string message)
{
if (id > 0 && !string.IsNullOrEmpty(studentNo))
{
string sqlStr = string.Format(@"UPDATE Reform_Students SET StudentNo='{0}',Status=1,SendTimes=SendTimes+1,ResultCode='{1}',ResultMsg='{2}',UpdateTime='{3}' WHERE Id={4}", studentNo, code, message, DateTime.Now.ToString(), id);
try
{
SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.Text, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog("添加或者修改学员信息成功后更新操作表的状态时出现异常：" + ex.Message + ";sql=" + sqlStr, "AddModify");
}
}
}
/// <summary>
/// 根据学号获取学员id
/// </summary>
/// <param name="studentNo">学号</param>
/// <returns></returns>
private int GetUserIdByStudentNo(string studentNo)
{
int userId = 0;
string sqlStr = string.Format(@"SELECT TOP 1 UserId FROM Sys_Users WHERE StudentNo='{0}' ORDER BY UserId DESC ", studentNo);
try
{
userId = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connString, CommandType.Text, sqlStr));
}
catch (Exception ex)
{
CommLog.WriteExceptionLog(" 根据学号获取学员id时出现异常：" + ex.Message + ";Sql=" + sqlStr, "SignUp");
}
return(userId);
}
/// <summary>
/// 学员报名成功修改操作表状态
/// </summary>
/// <param name="id">操作表id</param>
/// <param name="code">接口返回结果码</param>
/// <param name="message">接口返回结果说明文档</param>
/// <param name="signUpStatus">审核状态：0审核中 1审核通过 2审核失败</param>
internal void UpdateSignUpStatus(int id, string code, string message, string signUpStatus)
{
if (id > 0 && !string.IsNullOrEmpty(signUpStatus))
{
string sqlStr = string.Format(@"UPDATE Reform_SignUpProject SET Status=1,ResultCode='{0}',ResultMsg='{1}',SendTimes=SendTimes+1,UpdateTime='{2}',SignUpStatus={3} WHERE Id={4}", code, message, DateTime.Now.ToString(), signUpStatus, id);
try
{
SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.Text, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog(" 学员报名成功修改操作表状态时出现异常：" + ex.Message + ";Sql=" + sqlStr, "SignUp");
}
}
}
/// <summary>
/// 添加学员成功后向报名操作表中插入一条未处理的记录
/// </summary>
/// <param name="projectCode">项目编号</param>
/// <param name="studentNo">学号</param>
/// <param name="url">接口地址</param>
internal void InsertIntoReformSignUp(string projectCode, string studentNo, string url)
{
if (!string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(studentNo) && !string.IsNullOrEmpty(url))
{
string sqlStr = string.Format(@"INSERT INTO Reform_SignUpProject(StudentNo, ProjectCode, Url, Status, SendTimes, CreateTime, SignUpStatus) VALUES('{0}','{1}','{2}',{3},{4},'{5}',{6})", studentNo, projectCode, url, 0, 0, DateTime.Now.ToString(), 0);
try
{
SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.Text, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog("添加学员成功后向报名操作表中插入一条未处理的记录时出现异常：" + ex.Message + ";sql=" + sqlStr, "AddModify");
}
}
}
/// <summary>
/// 更新支付记录中学号
/// </summary>
/// <param name="userId">用户id</param>
/// <param name="studentNo">学号</param>
/// <param name="projectCode">项目编号</param>
internal void UpdatePayStudentNo(int userId, string studentNo, string projectCode)
{
if (userId > 0 && !string.IsNullOrEmpty(studentNo) && !string.IsNullOrEmpty(projectCode))
{
string sqlStr = string.Format(@" UPDATE Reform_PayRecords SET StudentNo='{0}' WHERE ProjectCode='{1}' AND UserId={2} AND StudentNo IS NULL", studentNo, projectCode, userId);
try
{
SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.Text, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog("添加学员成功后更新支付操作表中学号时出现异常：" + ex.Message + ";sql=" + sqlStr, "AddModify");
}
}
}
/// <summary>
/// 更新学员表中的学号
/// </summary>
/// <param name="id">用户id</param>
/// <param name="studentNo">学号</param>
internal void UpdateStudentNo(int id, string studentNo)
{
if (id > 0 && !string.IsNullOrEmpty(studentNo))
{
string sqlStr = string.Format(@" UPDATE Sys_Users SET StudentNo='{0}' WHERE UserId={1} ", studentNo, id);
try
{
SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.Text, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog("添加学员成功后更新学员表学号时出现异常：" + ex.Message + ";sql=" + sqlStr, "AddModify");
}
}
}
/// <summary>
/// 获取学员报名操作表中还未处理的记录
/// </summary>
/// <returns></returns>
internal DataTable GetSignUpInfo()
{
DataTable dt = null;
string sqlStr = "SELECT TOP 100 * FROM Reform_SignUpProject WHERE Status=0 ORDER BY Id DESC ";
try
{
dt = SqlHelper.ExecuteTable(SqlHelper.connString, sqlStr);
}
catch (Exception ex)
{
dt = null;
CommLog.WriteExceptionLog(" 获取学员报名操作表中还未处理的记录时出现异常：" + ex.Message + ";Sql=" + sqlStr, "SignUp");
}
return(dt);
}
/// <summary>
/// 获取添加/修改学员操作表中还未处理的记录
/// </summary>
/// <returns></returns>
internal DataTable GetStudentInfo()
{
DataTable dt = null;
try
{
string sqlStr = "SELECT TOP 100 * FROM Reform_Students WHERE Status=0 ORDER BY Id DESC ";
dt = SqlHelper.ExecuteTable(SqlHelper.connString, sqlStr);
}
catch (Exception ex)
{
dt = null;
CommLog.WriteExceptionLog("获取添加/修改学员操作表中还未处理的记录时出现异常：" + ex.Message, "AddModify");
}
return(dt);
}
/// <summary>
/// 报名审核通过修改ResCourseEnter表的审核状态
/// </summary>
/// <param name="projectCode"></param>
/// <param name="studentNo"></param>
internal void UpdateCourseEnterStatus(string projectCode, string studentNo)
{
if (!string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(studentNo))
{
int courseId = GetCourseIdByProjectCode(projectCode);
int userId = GetUserIdByStudentNo(studentNo);
if (userId > 0 && courseId > 0)
{
string sqlStr = string.Format(@"UPDATE Res_CourseEnter SET SignUpStatus=1 WHERE CourseId={0} AND UserId={1}", courseId, userId);
try
{
SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.Text, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog(" 报名审核通过修改ResCourseEnter表的审核状态时出现异常：" + ex.Message + ";Sql=" + sqlStr, "SignUp");
}
}
}
}
/// <summary>
/// 调用接口推送数据
/// </summary>
/// <param name="url">接口地址</param>
/// <param name="projectCode">项目编号</param>
/// <param name="encryption">接口参数加密串</param>
/// <returns></returns>
public string PostData(string url, string projectCode, string encryption)
{
string result = string.Empty;
string postString = string.Empty;
try
{
System.Net.WebClient WebClientObj = new System.Net.WebClient();
WebClientObj.Headers.Add("Content-Type", "application/x-www-form-urlencoded");
postString = "projectCode=" + HttpUtility.UrlEncode(projectCode, UTF8Encoding.UTF8) + "&encryption=" + HttpUtility.UrlEncode(encryption, UTF8Encoding.UTF8);
byte[] postData = Encoding.UTF8.GetBytes(postString);
byte[] responseData = WebClientObj.UploadData(url, "POST", postData);
result = Encoding.UTF8.GetString(responseData);
}
catch (Exception ex)
{
result = string.Empty;
CommLog.WriteExceptionLog("调用接口数据时出现异常：" + ex.Message + "; Url=" + url + ";projectCode=" + projectCode + ";encryption=" + encryption, "Main");
}
return(result);
}
/// <summary>
/// 报名项目失败后修改sendtimes
/// </summary>
/// <param name="id">操作表id</param>
/// <param name="sendTimes">接口调用累计次数</param>
/// <param name="code">接口返回结果码</param>
/// <param name="message">接口返回结果文字说明</param>
internal void UpdateSignUpSendTimes(int id, int sendTimes, string code, string message)
{
if (id > 0)
{
string sqlStr = string.Empty;
if (sendTimes < 2)
{
sqlStr = string.Format(@"UPDATE Reform_SignUpProject SET SendTimes=SendTimes+1,ResultCode='{0}',ResultMsg='{1}',UpdateTime='{2}' WHERE Id={3}", code, message, DateTime.Now.ToString(), id);
}
else
{
sqlStr = string.Format(@"UPDATE Reform_SignUpProject SET Status=2,SendTimes=SendTimes+1,ResultCode='{0}',ResultMsg='{1}',UpdateTime='{2}' WHERE Id={3}", code, message, DateTime.Now.ToString(), id);
}
try
{
SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.Text, sqlStr);
}
catch (Exception ex)
{
CommLog.WriteExceptionLog("学员报名项目失败时，修改提交次数时出现异常：" + ex.Message + ";sql=" + sqlStr, "SignUp");
}
}
}
