/// <summary>
/// 单条添加用户角色信息
/// </summary>
/// <param name="usersInfo"></param>
/// <returns></returns>
public int Add(UsersInfo usersInfo)
{
//实例化用户表
Users users = new Users();
users.UserName = usersInfo.UserName;
users.Password = usersInfo.Password;
var result = UsersDB.Insert(users);
//如果result为true
if (result)
{
SqlSugarClient sqlSugarClient = Educationcontext.GetClient();
var db = sqlSugarClient.SqlQueryable <Users>("select Id from Users order by Id ").Max(s => s.Id);
//实例化用户角色表
UserandRole userandRole = new UserandRole();
userandRole.UsersId = db;
var num = usersInfo.RoleName.Substring(0, usersInfo.RoleName.LastIndexOf(',')).Split(',');
var usersdb = 0;
foreach (var item in num)
{
userandRole.RolesId = int.Parse(item);
usersdb += sqlSugarClient.Insertable <UserandRole>(userandRole).ExecuteCommand();
}
if (usersdb == num.Length)
{
return(1);
}
}
return(0);
}
/// <summary>
/// 显示全部题型
/// </summary>
public List <TextType> GetTextType()
{
SqlSugarClient sugarClient = Educationcontext.GetClient();
List <TextType> textTypes = sugarClient.SqlQueryable <TextType>("select * from TextType where Enable = 1").ToList();
return(textTypes);
}
/// <summary>
/// 单条添加用户角色数据
/// </summary>
/// <param name="jurisdictionInfo"></param>
/// <returns></returns>
public int Add(JurisdictionInfo jurisdictionInfo)
{
//实例化角色表
Role role = new Role();
role.RoleName = jurisdictionInfo.RoleName;
var result = RoleDB.Insert(role);
//如果为true
if (result)
{
SqlSugarClient sqlSugarClient = Educationcontext.GetClient();
//排序查询最大值
var db = sqlSugarClient.SqlQueryable <Role>("select Id from Role order by Id").Max(s => s.Id);
RoleanJurisdiction roleanJurisdiction = new RoleanJurisdiction();
roleanJurisdiction.RolesId = db;
var num = jurisdictionInfo.Name.Substring(0, jurisdictionInfo.Name.LastIndexOf(',')).Split(',');
var roledb = 0;
foreach (var item in num)
{
roleanJurisdiction.JurisdictionId = int.Parse(item);
roledb += sqlSugarClient.Insertable <RoleanJurisdiction>(roleanJurisdiction).ExecuteCommand();
}
if (roledb == num.Length)
{
return(1);
}
}
return(0);
}
/// <summary>
/// 单条添加题
/// </summary>
/// <param name="questionBank"></param>
/// <returns></returns>
public int ADD(QuestionBankinherit questionBankinherit)
{
try
{
//添加到题库
QuestionBank questionBank = new QuestionBank();
questionBank.Subject = questionBankinherit.Subject;
questionBank.Answer = questionBankinherit.Answer;
questionBank.Photo = questionBankinherit.Photo;
questionBank.TypeOfExam = questionBankinherit.TypeOfExam;
questionBank.Enable = questionBankinherit.Enable;
SqlSugarClient sugarClient = Educationcontext.GetClient();
var resultquestionBank = QuestionBankDB.Insert(questionBank);
//获取最后一个Id
questionBank = sugarClient.SqlQueryable <QuestionBank>("select id from QuestionBank order by id DESC limit 1").First();
var resultQuestionBankId = questionBank.Id;
//添加到选项
Option option = new Option();
option.QuestionBankId = resultQuestionBankId;
option.AnswerA = questionBankinherit.AnswerA;
option.AnswerB = questionBankinherit.AnswerB;
option.AnswerC = questionBankinherit.AnswerC;
option.AnswerD = questionBankinherit.AnswerD;
option.AnswerE = questionBankinherit.AnswerE;
var resultoption = OptionDB.Insert(option);
return(1);
}
catch (Exception ex)
{
//return ex.Message;
return(0);
}
}
/// <summary>
/// 根据id返填题
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public QuestionBankinherit UpdateById(int id)
{
SqlSugarClient sugarClient = Educationcontext.GetClient();
QuestionBankinherit questionBankinherit = sugarClient.SqlQueryable <QuestionBankinherit>("select q.*,o.AnswerA,o.AnswerB,o.AnswerC,o.AnswerD,o.AnswerE,t.ExamType from QuestionBank q join `Option` o on q.Id = o.QuestionBankId join TextType t on q.TypeOfExam = t.ID where q.Id = " + id).First();
return(questionBankinherit);
}
/// <summary>
/// 修改题库的题
/// </summary>
/// <param name="questionBank"></param>
/// <returns></returns>
public int Update(QuestionBankinherit questionBankinherit)
{
SqlSugarClient sugarClient = Educationcontext.GetClient();
//修改题库
QuestionBank questionBank = new QuestionBank();
questionBank.Subject = questionBankinherit.Subject;
questionBank.Answer = questionBankinherit.Answer;
questionBank.Photo = questionBankinherit.Photo;
questionBank.TypeOfExam = questionBankinherit.TypeOfExam;
questionBank.Enable = questionBankinherit.Enable;
questionBank.Id = questionBankinherit.Id;
var questionBankId = questionBankinherit.Id;
var resultquestionBank = sugarClient.Updateable <QuestionBank>(questionBank).ExecuteCommand();
if (resultquestionBank != 0)
{
//修改选项
Option option = sugarClient.SqlQueryable <Option>("select * from `Option` where QuestionBankId = " + questionBankinherit.Id).First();
option.AnswerA = questionBankinherit.AnswerA;
option.AnswerB = questionBankinherit.AnswerB;
option.AnswerC = questionBankinherit.AnswerC;
option.AnswerD = questionBankinherit.AnswerD;
option.AnswerE = questionBankinherit.AnswerE;
var resultoption = sugarClient.Updateable <Option>(option).ExecuteCommand();
if (resultoption != 0)
{
return(1);
}
}
return(0);
}
/// <summary>
/// 获取角色信息
/// </summary>
/// <returns></returns>
public List <Role> GetRoles()
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
var result = db.SqlQueryable <Role>("select * from Role");
return(result.ToList());;
}
}
/// <summary>
/// 获取角色和权限信息
/// </summary>
/// <returns></returns>
public List <JurisdictionInfo> GetJurisdictionInfos()
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
var result = db.SqlQueryable <JurisdictionInfo>("SELECT a.Id,a.RoleName,b.`Name`,b.`level`,b.Url,c.RolesId,c.JurisdictionId FROM role a,jurisdiction b,roleanjurisdiction c WHERE a.Id=c.RolesId AND b.Id=c.JurisdictionId");
return(result.ToList());
}
}
/// <summary>
/// 修改说明信息
/// </summary>
/// <param name="explain"></param>
/// <returns></returns>
public int UpdExplain(Explain explain)
{
using (SqlSugarClient sqlsc = Educationcontext.GetClient())
{
var result = sqlsc.Updateable(explain).ExecuteCommand();
return(result);
}
}
/// <summary>
/// 获取今天用户是不是考试过了
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool GetTypeByUid(string id)
{
using (SqlSugarClient sqlsc = Educationcontext.GetClient())
{
ExaminationOrPractice result = sqlsc.Queryable <ExaminationOrPractice>().First(s => s.UId == id);
return(result.Flag);
}
}
/// <summary>
/// 获取所有说明信息
/// </summary>
/// <returns></returns>
public IList <Explain> GetExplainList()
{
using (SqlSugarClient sqlsc = Educationcontext.GetClient())
{
var explainList = sqlsc.Queryable <Explain>().ToList();
return(explainList as IList <Explain>);
}
}
/// <summary>
/// 获取说明信息根据Id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Explain GetExplainById(int id)
{
using (SqlSugarClient sqlsc = Educationcontext.GetClient())
{
var result = sqlsc.Queryable <Explain>().First(s => s.Id == id);
return(result);
}
}
/// <summary>
/// 添加说明信息
/// </summary>
/// <param name="explain"></param>
/// <returns></returns>
public long AddExplain(Explain explain)
{
using (SqlSugarClient sqlsc = Educationcontext.GetClient())
{
var result = sqlsc.Insertable(explain).ExecuteReturnIdentity();
return(result);
}
}
public int Add(Jurisdiction jurisdiction)
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
var result = db.Insertable <Jurisdiction>(jurisdiction).ExecuteReturnIdentity();
return(result);
}
}
/// <summary>
/// 显示考生信息
/// </summary>
/// <returns></returns>
public List <Candidateinherit> GetCandidates()
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
var CandList = db.SqlQueryable <Candidateinherit>("select a.*,c.`Name` as CompanyName,b.`Name` as TestRoomName, d.`Name` as ExamRoomName from candidate a,testroom b, examroom c ,company d where a.TestRoomID=b.Id and a.CompanyID=d.Id and a.ExamRoomID=c.Id");
return(CandList.ToList());
}
}
/// <summary>
/// 单条数据添加考生
/// </summary>
/// <param name="candidate"></param>
/// <returns></returns>
public int ADD(Candidate candidate)
{
using (SqlSugarClient sugarClient = Educationcontext.GetClient())
{
var result = sugarClient.Insertable <Candidate>(candidate).ExecuteCommand();
return(result);
}
}
/// <summary>
/// 获取单位
/// </summary>
/// <returns></returns>
public List <ExamRoom> GetExamRooms()
{
using (SqlSugarClient sugarClient = Educationcontext.GetClient())
{
var result = sugarClient.SqlQueryable <ExamRoom>("select * from ExamRoom").ToList();
return(result);
}
}
/// <summary>
/// 获取单位
/// </summary>
/// <returns></returns>
public List <Company> GetCompanies()
{
using (SqlSugarClient sugarClient = Educationcontext.GetClient())
{
var result = sugarClient.SqlQueryable <Company>("select * from Company").ToList();
return(result);
}
}
/// <summary>
/// 显示多少题型 禁用不显示
/// </summary>
/// <returns></returns>
public List <TextType> GetTextTypeList()
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
var texttypelist = db.Ado.SqlQuery <TextType>("select * from texttype where `Enable`=1");
return(texttypelist);
}
}
/// <summary>
/// 反填信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public UsersInfo GetUsersInfoById(int id)
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
var result = db.SqlQueryable <UsersInfo>("select a.Id,a.UserName,a.`Password`,b.RoleName,c.UsersId,c.RolesId from Users a,Role b,UserandRole c where a.Id=" + id).First();
return(result);
}
}
/// <summary>
/// 显示题类型显示题数
/// </summary>
/// <returns></returns>
public List <TextTypeNuminherit> TextTypeNums()
{
using (SqlSugarClient sugarClient = Educationcontext.GetClient())
{
List <TextTypeNuminherit> textTypeNums = sugarClient.SqlQueryable <TextTypeNuminherit>("select t.*,p.ExamType from TextTypeNum t join texttype p on t.TextTypeId = p.id").ToList();
return(textTypeNums);
}
}
/// <summary>
/// 编辑修改信息
/// </summary>
/// <param name="usersInfo"></param>
/// <returns></returns>
public int EditById(UsersInfo usersInfo)
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
var result = db.Updateable <UsersInfo>(usersInfo).ExecuteCommand();
return(result);
}
}
/// <summary>
/// 获取用户角色信息
/// </summary>
/// <returns></returns>
public List <UsersInfo> GetUsersList()
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
//三表联查
var result = db.SqlQueryable <UsersInfo>("select a.Id,a.UserName,a.`Password`,b.RoleName,c.UsersId,c.RolesId from Users a,Role b,UserandRole c where a.Id=c.UsersId and b.Id=c.RolesId");
return(result.ToList());
}
}
/// <summary>
/// 根据返填需要修改的考生
/// </summary>
/// <param name="candidate"></param>
/// <returns></returns>
public Candidateinherit UpdateById(int candidateId)
{
SqlSugarClient sugarClient = Educationcontext.GetClient();
Candidateinherit candidateinherit = sugarClient.Queryable <Candidate, TestRoom, ExamRoom, Company, TestTime>((CD, TR, ER, CP, TT) => CD.CompanyID == CP.Id && CD.ExamRoomID == ER.Id && CD.TestRoomID == TR.ID && CD.ExamNumber == TT.ExamNumberId).Select((CD, TR, ER, CP, TT) => new Candidateinherit {
Certificates = CD.Certificates, CompanyName = CP.Name, DocumentType = CD.DocumentType, Enable = CD.Enable, ExamNumber = CD.ExamNumber, ExamRoomName = ER.Name, Field = CD.Field, ID = CD.ID, LongExam = TT.LongExam, Name = CD.Name, Photo = CD.Photo, ProgressOfAnswer = TT.ProgressOfAnswer, RemainderLength = TT.RemainderLength, SeatNumber = CD.SeatNumber, Sex = CD.Sex, TestRoomName = TR.Name, TimeUsed = TT.TimeUsed
}).Where(m => m.ID == candidateId).First();
return(candidateinherit);
}
/// <summary>
/// 获取权限信息
/// </summary>
/// <returns></returns>
public List <Jurisdiction> GetJurisdictions()
{
using (SqlSugarClient db = Educationcontext.GetClient())
{
var result = db.SqlQueryable <Jurisdiction>("select * from Jurisdiction where Level=0");
var list = result.ToList();
return(list);
}
}
/// <summary>
/// 根据类型查找题库
/// </summary>
/// <param name="TypeOfExamId"></param>
/// <returns></returns>
public List <QuestionBankinherit> GetQuestionBanksByTypeOfExam(int TypeOfExamId)
{
SqlSugarClient sugarClient = Educationcontext.GetClient();
List <QuestionBankinherit> questionBankinherits = sugarClient.Queryable <QuestionBank, Option, TextType>((QB, Op, TT) => QB.Id == Op.QuestionBankId && QB.Id == Op.QuestionBankId).Where((QB, Op, TT) => QB.TypeOfExam == TypeOfExamId).Select((QB, Op, TT) => new QuestionBankinherit {
Answer = QB.Answer, AnswerA = Op.AnswerA, AnswerB = Op.AnswerB, AnswerC = Op.AnswerC, AnswerD = Op.AnswerD, AnswerE = Op.AnswerE, Subject = QB.Subject, Enable = QB.Enable, Photo = QB.Photo, ExamType = TT.ExamType
}).ToList();
return(questionBankinherits);
}
/// <summary>
/// 修改题量
/// </summary>
/// <returns></returns>
public int UpdateTextTypeNum(TextTypeNum textTypeNum)
{
using (SqlSugarClient sugarClient = Educationcontext.GetClient())
{
var result = sugarClient.Updateable <TextTypeNum>(textTypeNum).ExecuteCommand();
if (result == 1)
{
return(1);
}
return(0);
}
}
/// <summary>
/// 查询全部题库
/// </summary>
/// <returns></returns>
public List <QuestionBankinherit> GetQuestionBanks()
{
SqlSugarClient sugarClient = Educationcontext.GetClient();
List <QuestionBankinherit> questionBankinherits = sugarClient.Queryable <QuestionBank, Option, TextType>((QB, Op, TT) => new object[] {
JoinType.Inner, QB.Id == Op.QuestionBankId,
JoinType.Inner, QB.TypeOfExam == TT.ID
})
.Select((QB, Op, TT) => new QuestionBankinherit {
Answer = QB.Answer, AnswerA = Op.AnswerA, AnswerB = Op.AnswerB, AnswerC = Op.AnswerC, AnswerD = Op.AnswerD, AnswerE = Op.AnswerE, Subject = QB.Subject, Enable = QB.Enable, ExamType = TT.ExamType, Photo = QB.Photo, Id = QB.Id
}).ToList();
return(questionBankinherits);
}
/// <summary>
/// 根据准考证号显示考生信息
/// </summary>
/// <param name="examNumber"></param>
/// <returns></returns>
public Candidateinherit GetCandidatesByExamNumber(string examNumber)
{
Candidateinherit candidate = null;
try
{
SqlSugarClient sugarClient = Educationcontext.GetClient();
//var candidate = sugarClient.Queryable<Candidate>().First(s=>s.ExamNumber==examNumber);
candidate = sugarClient.SqlQueryable <Candidateinherit>(" select * from ( select a.*,c.`Name` as CompanyName,b.`Name` as TestRoomName, d.`Name` as ExamRoomName from candidate a,testroom b, examroom c ,company d where a.TestRoomID=b.Id and a.CompanyID=d.Id and a.ExamRoomID=c.Id) info where ExamNumber =" + examNumber).Single();
return(candidate);
}
catch (Exception)
{
return(candidate);
}
}
/// <summary>
/// 获取后台登陆人的信息
/// </summary>
/// <param name="name"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public Users GetUsersByNameAndPwd(string name, string pwd)
{
Users user = null;
try
{
using (SqlSugarClient sqlsc = Educationcontext.GetClient())
{
user = sqlsc.Queryable <Users>().First(s => (s.UserName == name) && (s.Password == pwd));
return(user);
}
}
catch (Exception)
{
return(user);
}
}
