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