public async Task <IEnumerable <Interviewee> > GetExamAttendedintervieweesBySessionIdAsync(long interviewSessionId)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
var query = "SELECT * FROM dbo.IntervieweeView WHERE InterviewSessionId = @InterviewSessionId AND AttendedExam = @AttendedExam " +
"ORDER BY TotalMarksObtained DESC";
var result = await db.QueryAsync <Interviewee>(query, new
{
InterviewSessionId = interviewSessionId,
AttendedExam = 1
});
return(result);
}
}
catch (Exception)
{
throw;
}
}
public async Task <int> DeleteAsync(long sessionwiseJobId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string questionQuery = "UPDATE dbo.SessionwiseJobs SET deleted = @delete WHERE SessionwiseJobId = @SessionwiseJobId";
var result = await db.ExecuteAsync(questionQuery,
new
{
delete = 1,
SessionwiseJobId = sessionwiseJobId
});
Console.WriteLine(result);
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <bool> CheckJobExistsInSessionAsync(long sessionId, int jobTitleId, long examSetId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string query = "SELECT * FROM dbo.SessionwiseJobs WHERE InterviewSessionId = @sessionId " +
" AND JobTitleId = @jobTitleId AND ExamSetId = @examSetId";
var result = await db.QueryAsync <SessionwiseJob>(query,
new
{
sessionId = sessionId,
jobTitleId = jobTitleId,
examSetId = examSetId
});
bool exists = result.Any();
Console.WriteLine(exists);
return(exists);
}
catch (Exception)
{
throw;
}
}
}
/// <summary>
/// 取得数据表（包括客户的简称）
/// </summary>
/// <param name="userInfo">用户</param>
/// <returns>数据表</returns>
public DataTable GetDataTable(UserInfo userInfo)
{
DataTable dataTable = new DataTable(LinkManTable.TableName);
using (IDbProvider dbProvider = DbFactoryProvider.GetProvider(BusinessDbType))
{
try
{
dbProvider.Open(BusinessDbConnection);
// 取得列表
LinkManManager manager = new LinkManManager(dbProvider, userInfo);
dataTable = manager.GetDataTable();
dataTable.TableName = LinkManTable.TableName;
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbProvider.Close();
}
}
return(dataTable);
}
/// <summary>
/// 根据主键数组获取数据
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="ids">主鍵</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByIds(UserInfo userInfo, string[] ids)
{
DataTable dataTable = new DataTable(LinkManTable.TableName);
using (IDbProvider dbProvider = DbFactoryProvider.GetProvider(BusinessDbType))
{
try
{
dbProvider.Open(BusinessDbConnection);
LinkManManager manager = new LinkManManager(dbProvider, userInfo);
dataTable = manager.GetDT(LinkManTable.FieldId, ids, LinkManTable.FieldSortCode);
dataTable.TableName = LinkManTable.TableName;
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbProvider.Close();
}
}
return(dataTable);
}
public async Task <int> DeleteAsync(long UserId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string questionQuery = "UPDATE dbo.Users SET deleted = @delete WHERE UserId = @UserId";
var result = await db.ExecuteAsync(questionQuery,
new
{
delete = 1,
UserId = UserId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <IEnumerable <SetQuestion> > GetQuestionsBySetIdAsync(long examSetId)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
string questionQuery = "SELECT * FROM dbo.QuestionsForSetView " +
"WHERE ExamSetId = @examSetId " +
"ORDER BY QuestionTypeId, QuestionCategoryId,QuestionComplexityId";
var result = await db.QueryAsync <SetQuestion>(questionQuery, new
{
examSetId = examSetId
});
return(result);
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 得到客户-主联系人信息
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="id">主键</param>
/// <returns>数据表</returns>
public DataTable GetCustomerContactInfo(UserInfo userInfo, string id)
{
DataTable dataTable = new DataTable();
using (IDbProvider dbProvider = DbFactoryProvider.GetProvider(BusinessDbType))
{
try
{
dbProvider.Open(BusinessDbConnection);
// 取得列表
CustomerManager manager = new CustomerManager(dbProvider, userInfo);
dataTable = manager.GetCustomerContactInfo(id);
dataTable.TableName = CustomerTable.TableName;
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbProvider.Close();
}
}
return(dataTable);
}
/// <summary>
/// 根据分类获取数据
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="category">分类</param>
/// <returns>数据表</returns>
public DataTable GetDTByCategory(UserInfo userInfo, string category = "")
{
DataTable dataTable = new DataTable(CustomerTable.TableName);
using (IDbProvider dbProvider = DbFactoryProvider.GetProvider(BusinessDbType))
{
try
{
dbProvider.Open(BusinessDbConnection);
// 根据条件获取数据
CustomerManager manager = new CustomerManager(dbProvider, userInfo);
dataTable = manager.GetDTByCategory(category);
dataTable.TableName = CustomerTable.TableName;
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbProvider.Close();
}
}
return(dataTable);
}
public async Task <IEnumerable <ExamSet> > GetExamSetsByJobTitleAsync(long jobTitleId)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
string examSetsQuery = "SELECT * FROM ExamSets where JobTitleId = @jobTitleId AND Deleted = @deleted";
var result = await db.QueryAsync <ExamSet>(examSetsQuery,
new
{
jobTitleId = jobTitleId,
deleted = 0
});
return(result);
}
}
catch (Exception)
{
throw;
}
}
public async Task <int> DeleteAsync(int jobTitleId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string deleteQuery = "UPDATE dbo.JobTitles SET deleted = @delete WHERE jobTitleId = @jobTitleId";
var result = await db.ExecuteAsync(deleteQuery,
new
{
delete = 1,
jobTitleId = jobTitleId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public bool ArchRelation(string userId, string workFlowId, string workTaskId, string workFlowInstanceId, string workTaskInstanceId, OperParameter operParam)
{
switch (operParam.OperRelation)
{
case 1: //本部门领导
var orgEntity = new PiOrganizeManager(DbFactoryProvider.GetProvider(SystemInfo.RDIFrameworkDbType, SystemInfo.RDIFrameworkDbConectionString)).GetEntity(operParam.OperContent);
var uEntity = new PiUserManager(DbFactoryProvider.GetProvider(SystemInfo.RDIFrameworkDbType, SystemInfo.RDIFrameworkDbConectionString)).GetEntity(orgEntity.ManagerId);
operParam.OperContent = uEntity.Id;
operParam.OperContenText = uEntity.UserName;
AssignUser(userId, workFlowId, workTaskId, workFlowInstanceId, workTaskInstanceId, operParam);
break;
case 2: //无此情况
break;
case 3: //上级部门,一个部门只有一个上级部门
//ToDo...
break;
case 4: //下级部门
//ToDo...
break;
case 5: //上级部门领导
//ToDo...
break;
case 6: //下级部门领导
//ToDo...
break;
}
return(true);
}
public static void ProcessRDIWriteDbWithLock(UserInfo userInfo, ParameterUtil parameter, object locker, ProcessFunWithLockDelegate fun)
{
if (SystemInfo.IsAuthorized(userInfo))
{
int milliStart = Begin(parameter.UserInfo, parameter.CurrentMethod);
bool getOnLine = false;
lock (locker)
{
using (IDbProvider dbProvider = DbFactoryProvider.GetProvider(GetDbType(DbType.RDIDbWrite)))
{
try
{
dbProvider.Open(GetDbConnection(DbType.RDIDbWrite));
getOnLine = fun(dbProvider, getOnLine);
AddLog(dbProvider, parameter);
}
catch (Exception ex)
{
CiExceptionManager.LogException(dbProvider, parameter.UserInfo, ex);
throw;
}
finally
{
dbProvider.Close();
}
}
}
End(milliStart, parameter.CurrentMethod, getOnLine);
}
}
private static void ProcessDbProvider(ParameterUtil sup, ProcessFunDelegate fun, DbType dbType, bool inTransaction)
{
using (IDbProvider dbProvider = DbFactoryProvider.GetProvider(GetDbType(dbType)))
{
try
{
dbProvider.Open(GetDbConnection(dbType));
if (inTransaction)
{
dbProvider.BeginTransaction();
}
fun(dbProvider);
AddLog(dbProvider, sup);
if (inTransaction)
{
dbProvider.CommitTransaction();
}
}
catch (Exception ex)
{
if (inTransaction)
{
dbProvider.RollbackTransaction();
}
CiExceptionManager.LogException(dbProvider, sup.UserInfo, ex);
throw;
}
finally
{
dbProvider.Close();
}
}
}
public async Task <int> DeleteOptionByQuestionIdAsync(long questionId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
Console.WriteLine("delete called");
await db.OpenAsync();
string questionQuery = "UPDATE dbo.ObjectiveQuestionOptions SET deleted = @delete WHERE QuestionId = @QuestionId";
var result = await db.ExecuteAsync(questionQuery,
new
{
delete = 1,
QuestionId = questionId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public static async Task <Interviewee> GetIntervieweeDetailAsync(string emailaddress, string contactnumber)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
string questionQuery = "SELECT * FROM dbo.InterviewSessionCandidate_view " +
"WHERE EmailAddress = @emailaddress " +
"AND ContactNumber = @contactNumber";
var result = await db.QueryAsync <Interviewee>(questionQuery,
new
{
emailaddress = emailaddress,
contactNumber = contactnumber
});
return(result.FirstOrDefault());
}
}
catch (Exception)
{
throw;
}
}
public async Task <IEnumerable <QuestionBanks> > SearchQuestionAsync(QuestionSearch model)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
string questionQuery = "SELECT * FROM dbo.QuestionBankView WHERE " +
"(@QuestionTypeId IS NULL OR QuestionTypeId = @QuestionTypeId) " +
"AND (@QuestionCategoryId IS NULL OR QuestionCategoryId = @QuestionCategoryId) " +
"AND (@QuestionComplexityId IS NULL OR QuestionComplexityId = @QuestionComplexityId) " +
"AND (Question LIKE @Question)";
var result = await db.QueryAsync <QuestionBanks>(questionQuery,
new
{
QuestionTypeId = model.QuestionTypeId == 0 ? (int?)null : (int?)model.QuestionTypeId,
QuestionCategoryId = model.QuestionCategoryId == 0 ? (int?)null : (int?)model.QuestionCategoryId,
QuestionComplexityId = model.QuestionComplexityId == 0 ? (int?)null : (int?)model.QuestionComplexityId,
Question = "%" + model.Question + "%"
});
Console.WriteLine(questionQuery);
return(result);
}
}
catch (Exception)
{
throw;
}
}
public static async Task <int> MarkAttendedExamAsync(long intervieweeId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string questionQuery = "update Interviewees set AttendedExam = @Attended WHERE IntervieweeId = @IntervieweeId";
var result = await db.ExecuteAsync(questionQuery,
new
{
Attended = 1,
IntervieweeId = intervieweeId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <int> UpdatePasswordAsync(Password model)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string questionQuery = "UPDATE dbo.Users SET " +
"Password = @Password," +
"PasswordChanged = @PasswordChanged " +
"WHERE EmailAddress = @EmailAddress";
var result = await db.ExecuteAsync(questionQuery,
new
{
Password = model.NewPassword,
PasswordChanged = 1,
EmailAddress = model.EmailAddress
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
/// <summary>
/// 取得数据表
/// </summary>
/// <param name="userInfo">用户</param>
/// <returns>数据表</returns>
public DataTable GetDataTable(UserInfo userInfo)
{
DataTable dataTable = new DataTable(IncomingCallTable.TableName);
using (IDbProvider dbProvider = DbFactoryProvider.GetProvider(BusinessDbType))
{
try
{
dbProvider.Open(BusinessDbConnection);
// 取得列表
IncomingCallManager manager = new IncomingCallManager(dbProvider, userInfo);
dataTable = manager.GetDT(new KeyValuePair <string, object>(IncomingCallTable.FieldDeleteMark, 0), IncomingCallTable.FieldSortCode);
dataTable.TableName = IncomingCallTable.TableName;
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbProvider.Close();
}
}
return(dataTable);
}
public static async Task <string> GetUserPasswordAsync(string emailAddress)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
db.Open();
string questionQuery = "SELECT password FROM dbo.Users " +
"WHERE EmailAddress = @EmailAddress";
var result = await db.QueryAsync <string>(questionQuery,
new
{
EmailAddress = emailAddress
});
return(result.FirstOrDefault());
}
catch (Exception)
{
throw;
}
}
}
public async Task <int> UpdateAsync(InterviewSession model)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string optionQuery = "UPDATE dbo.InterviewSessions SET " +
"Title = @Title," +
"SessionStartDate = @StartDate," +
"SessionEndDate = @EndDate," +
"AuditTs = @AuditTs " +
"WHERE InterviewSessionId = @IntervierwSessionId";
var result = await db.ExecuteAsync(optionQuery,
new
{
Title = model.Title,
StartDate = model.SessionStartDate,
EndDate = model.SessionEndDate,
AuditTs = DateTime.Now,
IntervierwSessionId = model.InterviewSessionId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="userId">用户主键</param>
/// <param name="searchValue">查询关键字</param>
/// <param name="recordCount">记录数</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示记录条数</param>
/// <param name="sortExpression">排序字段</param>
/// <param name="sortDire">排序方向</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByPage(UserInfo userInfo, string userId, string searchValue, out int recordCount, int pageIndex = 1, int pageSize = 20, string sortExpression = null, string sortDire = null)
{
DataTable dataTable = new DataTable(LinkManTable.TableName);
using (IDbProvider dbProvider = DbFactoryProvider.GetProvider(BusinessDbType))
{
try
{
dbProvider.Open(BusinessDbConnection);
// 取得列表
LinkManManager manager = new LinkManManager(dbProvider, userInfo);
dataTable = manager.GetDataTableByPage(userId, searchValue, out recordCount, pageIndex, pageSize, sortExpression, sortDire);
dataTable.TableName = LinkManTable.TableName;
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbProvider.Close();
}
}
return(dataTable);
}
public async Task <IEnumerable <ResultSummary> > ResultSummaryAsync(ResultSummary model)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
string questionQuery = "SELECT * FROM dbo.InterviewResultSummaryView WHERE " +
"(@IntervieweeId IS NULL OR IntervieweeId = @IntervieweeId) " +
"AND (@InterviewSessionId IS NULL OR InterviewSessionId = @InterviewSessionId) " +
"AND (EmailAddress LIKE @EmailAddress) " +
"AND (IntervieweeName LIKE @IntervieweeName)" +
" ORDER BY MarksObtained DESC";
var result = await db.QueryAsync <ResultSummary>(questionQuery,
new
{
IntervieweeId = model.IntervieweeId == 0 ? (int?)null : (int?)model.IntervieweeId,
InterviewSessionId = model.InterviewSessionId == 0 ? (int?)null : (int?)model.InterviewSessionId,
EmailAddress = "%" + model.EmailAddress + "%",
IntervieweeName = "%" + model.IntervieweeName + "%"
});
Console.WriteLine(questionQuery);
return(result);
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 根据条件获取数据
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="parameters">参数</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByValues(UserInfo userInfo, List <KeyValuePair <string, object> > parameters)
{
DataTable dataTable = new DataTable(LinkManTable.TableName);
using (IDbProvider dbProvider = DbFactoryProvider.GetProvider(BusinessDbType))
{
try
{
dbProvider.Open(BusinessDbConnection);
// 根据条件获取数据
LinkManManager manager = new LinkManManager(dbProvider, userInfo);
dataTable = manager.GetDT(parameters);
dataTable.TableName = LinkManTable.TableName;
}
catch (Exception ex)
{
CiExceptionManager.LogException(dbProvider, userInfo, ex);
throw ex;
}
finally
{
dbProvider.Close();
}
}
return(dataTable);
}
public async Task <int> AddInterviewSessionAsync(InterviewSession model)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
Console.WriteLine(model.CreatedBy);
await db.OpenAsync();
string optionQuery = "INSERT INTO dbo.InterviewSessions VALUES" +
" (@Title, @StartDate,@EndDate,@CreatedBy,@AuditTs,@Deleted)";
var result = await db.ExecuteAsync(optionQuery,
new
{
Title = model.Title,
StartDate = model.SessionStartDate,
EndDate = model.SessionEndDate,
CreatedBy = model.CreatedBy,
AuditTs = DateTime.Now,
Deleted = 0
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
public async Task <IEnumerable <SessionwiseJob> > GetJobsBySessionIdAsync(long sessionId)
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
try
{
await db.OpenAsync();
string query = "SELECT * FROM SessionJobView WHERE InterviewSessionId = @sessionId";
var result = await db.QueryAsync <SessionwiseJob>(query,
new
{
sessionId = sessionId
});
return(result);
}
catch (Exception)
{
throw;
}
}
}
/// <summary>
/// 新增数据
/// </summary>
/// <param name="userInfo">使用者</param>
/// <param name="entity">实体</param>
/// <param name="statusMessage">返回状态信息</param>
/// <returns>受影响的行数</returns>
public int Add(UserInfo userInfo, CiItemDetailsEntity entity, out string statusMessage)
{
int returnValue = 0;
statusMessage = string.Empty;
if (this.GetDTByValues(userInfo, new string[] { CiItemDetailsTable.FieldItemId, CiItemDetailsTable.FieldItemName, CiItemDetailsTable.FieldDeleteMark }, new string[] { entity.ItemId.ToString(), entity.ItemName, "0" }).Rows.Count > 0)
{
returnValue = 0;
statusMessage = "已存在相同的明细项！";
}
else
{
using (IDbProvider dbProvider = DbFactoryProvider.GetProvider(SystemInfo.RDIFrameworkDbType))
{
try
{
dbProvider.Open(RDIFrameworkDbConection);
var manager = new CiItemDetailsManager(dbProvider, userInfo);
returnValue = !string.IsNullOrEmpty(manager.AddEntity(entity)) ?1:0;
statusMessage = "成功新增数据！";
}
catch (Exception ex)
{
CiExceptionManager.LogException(dbProvider, userInfo, ex);
throw ex;
}
finally
{
dbProvider.Close();
}
}
}
return(returnValue);
}
public async Task <bool> IntervieweeValidationAsync(string emailaddress, string contactnumber)
{
try
{
var dbfactory = DbFactoryProvider.GetFactory();
using (var db = (SqlConnection)dbfactory.GetConnection())
{
await db.OpenAsync();
var query = "SELECT * FROM dbo.InterviewSessionCandidate_view WHERE EmailAddress = @EmailAddress" +
"AND ContactNumber = @ContactNumber";
var result = await db.GetAsync <Interviewee>(query, new
{
EmailAddress = emailaddress,
ContactNumber = contactnumber
});
if (result != null)
{
return(true);
}
else
{
return(false);
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 构造函数
/// </summary>
public PiUserManager()
{
if (base.dbProvider == null)
{
base.dbProvider = DbFactoryProvider.GetProvider(SystemInfo.RDIFrameworkDbType, SystemInfo.RDIFrameworkDbConection);
}
base.CurrentTableName = PiUserTable.TableName;
}
