public void CrearEstudiante(EstudianteVM estudianteVM)
{
estudiante.Nombres = estudianteVM.Estudiante.Nombres;
estudiante.ApellidoP = estudianteVM.Estudiante.ApellidoP;
estudiante.ApellidoM = estudianteVM.Estudiante.ApellidoM;
estudiante.FechaNacimiento = estudianteVM.Estudiante.FechaNacimiento;
estudiante.Genero = estudianteVM.Estudiante.Genero;
estudiante.RH = estudianteVM.Estudiante.RH;
estudiante.TipoSangre = estudianteVM.Estudiante.TipoSangre;
estudiante.Estado = estudianteVM.Estudiante.Estado;
historicoDocumento.PersonaId = estudiante.PersonaId;
historicoDocumento.FechaExpedicion = estudianteVM.HistoricoDocumentoPersona.FechaExpedicion;
historicoDocumento.TipoDocumento = estudianteVM.HistoricoDocumentoPersona.TipoDocumento;
historicoDocumento.Numero = estudianteVM.HistoricoDocumentoPersona.Numero;
historicoTelefono.Estado = estudianteVM.HistoricoDocumentoPersona.Estado;
historicoTelefono.PersonaId = estudiante.PersonaId;
historicoTelefono.TiposTelefono = estudianteVM.HistoricoTelefonoPersona.TiposTelefono;
historicoTelefono.Numero = estudianteVM.HistoricoTelefonoPersona.Numero;
historicoTelefono.Estado = estudianteVM.HistoricoTelefonoPersona.Estado;
personaDireccion.PersonaId = estudiante.PersonaId;
personaDireccion.Direccion = estudianteVM.HistoricoPersonaDireccion.Direccion;
personaDireccion.Estado = estudianteVM.HistoricoPersonaDireccion.Estado;
dBRepository.Add(estudiante);
dBRepository.Add(historicoDocumento);
dBRepository.Add(historicoTelefono);
dBRepository.Add(personaDireccion);
dBRepository.Commit();
}
/// <summary>
/// 添加新的教师
/// </summary>
/// <returns></returns>
public static string AddTeachers(Teachers teacher, SYSAccount sys)
{
DBRepository db = new DBRepository(DBKeys.PRX);
string ret = "0";
try
{
db.BeginTransaction();//事务开始
db.Insert <Teachers>(teacher);
// MsSqlMapperHepler.Insert<Teachers>(teacher, DBKeys.PRX);
db.Insert <SYSAccount>(sys);
db.Commit(); //事务提交
db.Dispose(); //资源释放
ret = "1"; //新增成功
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();//资源释放
throw new Exception(ex.Message + "。" + ex.InnerException.Message);
}
return(ret);
}
/// <summary>
/// 添加新的教师
/// </summary>
/// <returns></returns>
public static string AddSYS_SystemRole(SYSAccountRole sys)
{
DBRepository db = new DBRepository(DBKeys.PRX);
db.BeginTransaction();//事务开始
string ret = "0";
try
{
var number = Getnumber(sys.AR_AccountId);//判断是否存在
if (number > 0)
{
db.Execute("Delete From SYS_AccountRole where AR_AccountId = " + sys.AR_AccountId);
}
for (int i = 0; i < sys.AR_SystemRoleIdS.Length; i++)
{
sys.AR_SystemRoleId = int.Parse(sys.AR_SystemRoleIdS[i]);
db.Insert <SYSAccountRole>(sys);
}
db.Commit(); //事务提交
ret = "1"; //新增成功
db.Dispose(); //资源释放
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message);
}
return(ret);
}
/// <summary>
/// 新增,返回的是主键
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static string AddStudent(Students Stu, SYSAccount sys, SYSAccountRole sysR)
{
string ret;
DBRepository db = new DBRepository(DBKeys.PRX);
db.BeginTransaction();//事务开始
try
{
ret = db.Insert <Students>(Stu); //添加学生表
int max = db.Insert <SYSAccount>(sys); //添加用户表
sysR.AR_AccountId = max;
//sysR.AR_AccountId = max.ContainsValue(0);
db.Insert <SYSAccountRole>(sysR); //添加权限表
db.Commit(); //事务提交
db.Dispose(); //资源释放
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message + "。" + ex.InnerException.Message);
}
return(ret);
}
/// <summary>
/// 保存转班记录
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool SaveClass_transfe(Enroll en, ClassesTrans ct)
{
bool ret = false;
DBRepository db = new DBRepository(DBKeys.PRX);
try
{
db.BeginTransaction();//事务开始
Enroll en_old = db.GetById <Enroll>(en.ID);
string oldclassid = en_old.ClassID;//原来的班级号
en_old.ClassID = en.ClassID;
en_old.UpdateTime = en.UpdateTime;
en_old.UpdatorId = en.UpdatorId;
db.Update(en_old); //把报名表中的classid改成新的
string strsql = "delete from AttendanceRecord where AttendanceTypeID = 1 and StudentID = '" + en_old.StudentID + "' and ClassID = '" + oldclassid + "'";
db.Execute(strsql); //删除未考勤多余的学员课程记录
if (AddClassesTrans(ct, db) > 0)
{
db.Commit(); //事务提交
db.Dispose(); //资源释放
ClassListData.RefreshClassList(en.ClassID, en.UpdatorId); //刷新排课
ret = true; //新增成功
}
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();//资源释放
throw new Exception(ex.Message + "。" + ex.InnerException.Message);
}
return(ret);
}
/// <summary>
/// 批量新增报名审核记录
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static bool AddEnrollAuditList(List <EnrollAudit> list)
{
bool ret = false;
DBRepository db = new DBRepository(DBKeys.PRX);
db.BeginTransaction();
try
{
foreach (var obj in list)
{
db.Insert <EnrollAudit>(obj);
Appointment ap = db.GetById <Appointment>(obj.APID);
ap.ApStateID = 5;//待审核
db.Update <Appointment>(ap);
}
ret = true;
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message);
}
return(ret);
}
/// <summary>
/// 保存学生的考勤
/// </summary>
/// <param name="cls"></param>
/// <returns></returns>
public static bool saveStudentEvalute(List <vw_StudentEvaluate> cls)
{
bool ret = false;
DBRepository db = new DBRepository(DBKeys.PRX);
try {
db.BeginTransaction();//事务开始
foreach (vw_StudentEvaluate value in cls)
{
if (string.IsNullOrWhiteSpace(value.Evaluate))
{
continue;
}
AttendanceRecord btnto = GetAttendanceRecordByID(value.ID);//获取对象
btnto.Evaluate = value.Evaluate;
//MsSqlMapperHepler.Update(btnto, DBKeys.PRX);
db.Update(btnto);
}
db.Commit(); //事务提交
db.Dispose(); //资源释放
ret = true; //新增成功
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message + "。" + ex.InnerException.Message);
}
return(ret);
}
/// <summary>
/// 调整报名课时
/// </summary>
/// <param name="enid"></param>
/// <returns></returns>
public static bool AjustmentEnroll(string ENID, int ClassHour, decimal Price, string userid)
{
DBRepository db = new DBRepository(DBKeys.PRX);
try
{
db.BeginTransaction();
Enroll en = db.GetById <Enroll>(ENID);
if (en == null)
{
return(false);
}
//增加调整课时日志
TransferRecord tr = new TransferRecord();//添加日志记录
tr.StudentID = en.StudentID;
tr.BeforeHours = en.ClassHour - en.UsedHour;
tr.AfterHours = en.ClassHour - en.UsedHour + +decimal.Parse(ClassHour.ToString());
tr.TypeID = 8;//调整报名课时
tr.CreateTime = DateTime.Now;
tr.CreatorId = userid;
tr.ENID = en.ID;
tr.ClassID = en.ClassID;
db.Insert(tr);
//增加资金日志
FundsFlow fundsflow = new FundsFlow();//添加日志记录
fundsflow.TypeID = 5;
fundsflow.Amount = Price;
fundsflow.KeyID = ENID;
fundsflow.CreatorId = userid;
fundsflow.CreateTime = DateTime.Now;
fundsflow.StateID = 0;
db.Insert(fundsflow);
//增加报名费用
en.Price = en.Price + Price; //金额
en.Paid = en.Paid + Price;
en.ClassHour = en.ClassHour + ClassHour; //课时
en.UpdateTime = DateTime.Now;
en.UpdatorId = userid;
db.Update(en);
db.Commit();
db.Dispose();
return(true);
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message);
}
}
/// <summary>
/// 转让课程，乙方没有报过名
/// </summary>
/// <param name="a"></param>
/// <param name="b"></param>
/// <param name="loginid"></param>
/// <returns></returns>
public static bool TransferAudit2(DataProvider.Entities.Enroll a, DataProvider.Entities.Enroll b, string loginid, Transfer rb)
{
bool ret = false;
DBRepository db = new DBRepository(DBKeys.PRX);
db.BeginTransaction();
try
{
DataProvider.Entities.Enroll j = EnrollData.GetEnrollByID(a.ID); //甲方原始报名记录
TransferRecord tr1 = new TransferRecord(); //甲方的转移记录
tr1.StudentID = j.StudentID;
tr1.BeforeHours = j.ClassHour - j.UsedHour;
tr1.AfterHours = a.ClassHour - a.UsedHour;
tr1.TypeID = 1;//转让产生的
tr1.CreateTime = DateTime.Now;
tr1.CreatorId = loginid;
tr1.Remark = "转班：原报名号:" + a.ID + " 原班级号：" + a.ClassID + " 转至班级：" + b.ClassID;
db.Insert(tr1);
db.Update(a);//扣除课时
TransferRecord tr2 = new TransferRecord();//乙方的转移记录
tr2.StudentID = b.StudentID;
tr2.BeforeHours = 0;
tr2.AfterHours = b.ClassHour - b.UsedHour;
tr2.TypeID = 1;//转让产生的
tr2.CreateTime = DateTime.Now;
tr2.CreatorId = loginid;
tr2.Remark = "转班：来自报名号:" + a.ID + " 来自班级号：" + a.ClassID + " 转至班级：" + b.ClassID;
db.Insert(tr2);
db.Insert(b);
rb.StateID = 2;
rb.ApprovedBy = loginid;
rb.ApprovedTime = DateTime.Now;
db.Update(rb);
ret = true;
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message);
}
return(ret);
}
/// <summary>
/// 刷新排课记录，主要用于补漏
/// </summary>
public static bool RefreshClassList(string classid, string loginid)
{
bool ret = false;
DBRepository db = new DBRepository(DBKeys.PRX);
string strsql = "SELECT * FROM dbo.Enroll WHERE ClassID = '" + classid + "'";
List <Enroll> enlist = MsSqlMapperHepler.SqlWithParams <Enroll>(strsql, null, DBKeys.PRX);//找到未报名记录
Classes cl = db.GetById <Classes>(classid);
db.BeginTransaction();//事务开始
try
{
foreach (var en in enlist)
{
if (cl.StateID.Value == 2 || cl.StateID.Value == 3)//如果班级状态是已排课，或已上课，生成课程表AttendanceRecord
{
List <vw_ClassAttendanceList> clist = new List <vw_ClassAttendanceList>();
clist = db.Query <vw_ClassAttendanceList>("select * from vw_ClassAttendanceList where ClassID = '" + cl.ID + "'", null).ToList();
//int aa = Convert.ToInt32(en.ClassHour) < clist.Count() ? Convert.ToInt32(en.ClassHour) : clist.Count();//取较小数做循环
int aa = clist.Count();//循环全部
for (int i = 0; i < aa; i++)
{
AttendanceRecord attendold = db.Query <AttendanceRecord>("select * from AttendanceRecord where StudentID = '" + en.StudentID + "' and ClassID = '" + en.ClassID + "' and ClassIndex = " + (i + 1), null).FirstOrDefault();
AttendanceRecord attend = new AttendanceRecord();
attend.CreateTime = DateTime.Now; //创建时间
attend.CreatorId = loginid; //创建人
attend.ClassID = en.ClassID; //班级编号
attend.ClassIndex = i + 1; //班次序号，也就是班级生成的集体上课记录
attend.AttendanceTypeID = 1; //上课状态,默认为1，未考勤
attend.StudentID = en.StudentID; //学员号
if (attendold == null)
{
db.Insert <AttendanceRecord>(attend);//增加上课记录表数据
}
}
}
//string delstr = "delete from AttendanceRecord where "
}
db.Commit();
db.Dispose();
ret = true;
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message);
}
return(ret);
}
/// <summary>
/// 批量新增报名记录
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static int AddList(List <Enroll> list)
{
int ret = 0;//初始
foreach (var obj in list)
{
int haveenroll = EnrollData.getEnrollByStuidCalssid(obj.StudentID, obj.ClassID);
if (haveenroll > 0)
{
ret = -1;//重复报名
return(ret);
}
}
DBRepository db = new DBRepository(DBKeys.PRX);
db.BeginTransaction();
try
{
foreach (var obj in list)
{
obj.ID = CommonData.DPGetTableMaxId("EN", "ID", "Enroll", 8, db);//走同一个事务
db.Insert <Enroll>(obj);
Appointment ap = db.GetById <Appointment>(obj.APID);
ap.ApStateID = 3;//已报名
db.Update <Appointment>(ap);
FundsFlow fl = new FundsFlow(); //资金流水
fl.TypeID = 1; //类型1报名
fl.Amount = obj.Paid;
fl.KeyID = obj.ID;
fl.CreateTime = DateTime.Now;
fl.CreatorId = obj.CreatorId;
db.Insert(fl);
//Classes ca = ClassesData.GetClassesByID(obj.ClassID);
//ca.PresentEnroll = ca.PresentEnroll + 1;
//MsSqlMapperHepler.Update<Classes>(ca, DBKeys.PRX);//报名数加1
}
ret = 1;//成功
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message);
}
return(ret);
}
public void CrearDocente(DocenteVM docenteVM)
{
docente.Nombres = docenteVM.Docente.Nombres;
docente.ApellidoP = docenteVM.Docente.ApellidoP;
docente.ApellidoM = docenteVM.Docente.ApellidoM;
docente.FechaNacimiento = docenteVM.Docente.FechaNacimiento;
docente.Genero = docenteVM.Docente.Genero;
docente.RH = docenteVM.Docente.RH;
docente.TipoSangre = docenteVM.Docente.TipoSangre;
docente.Estado = docenteVM.Docente.Estado;
docente.EspecialidadId = docenteVM.Docente.EspecialidadId;
historicoDocumento.PersonaId = docente.PersonaId;
historicoDocumento.FechaExpedicion = docenteVM.HistoricoDocumentoPersona.FechaExpedicion;
historicoDocumento.TipoDocumento = docenteVM.HistoricoDocumentoPersona.TipoDocumento;
historicoDocumento.Numero = docenteVM.HistoricoDocumentoPersona.Numero;
historicoTelefono.Estado = docenteVM.HistoricoDocumentoPersona.Estado;
historicoTelefono.PersonaId = docente.PersonaId;
historicoTelefono.TiposTelefono = docenteVM.HistoricoTelefonoPersona.TiposTelefono;
historicoTelefono.Numero = docenteVM.HistoricoTelefonoPersona.Numero;
historicoTelefono.Estado = docenteVM.HistoricoTelefonoPersona.Estado;
personaDireccion.PersonaId = docente.PersonaId;
personaDireccion.Direccion = docenteVM.HistoricoPersonaDireccion.Direccion;
personaDireccion.Estado = docenteVM.HistoricoPersonaDireccion.Estado;
dBRepository.Add(docente);
dBRepository.Add(historicoDocumento);
dBRepository.Add(historicoTelefono);
dBRepository.Add(personaDireccion);
dBRepository.Commit();
}
/// <summary>
/// 完结报名，清空课时
/// </summary>
/// <param name="enid"></param>
/// <returns></returns>
public static bool FinishEnroll(string enid, string loginid)
{
if (string.IsNullOrEmpty(enid))
{
return(false);
}
DBRepository db = new DBRepository(DBKeys.PRX);
try
{
db.BeginTransaction();
Enroll en = db.GetById <Enroll>(enid);
if (en == null)
{
return(false);
}
TransferRecord tr = new TransferRecord();//添加日志记录
tr.StudentID = en.StudentID;
tr.BeforeHours = en.ClassHour - en.UsedHour;
tr.AfterHours = 0; //清空剩余课时
tr.TypeID = 7; //ERP点完成按钮
tr.CreateTime = DateTime.Now;
tr.CreatorId = loginid;
tr.ENID = en.ID;
tr.ClassID = en.ClassID;
db.Insert(tr);
en.StateID = 6; //状态6是已完成报名，需要清空剩余课时
en.UsedHour = en.ClassHour; //清空课时
en.UpdateTime = DateTime.Now;
en.UpdatorId = loginid;
db.Update(en);
db.Commit();
db.Dispose();
return(true);
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message);
}
}
/// <summary>
/// 更新 教师信息
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool UpdateTeacher(Teachers btn)
{
DBRepository db = new DBRepository(DBKeys.PRX);
bool ret = false;
try
{
Teachers btnto = TeacherData.GetTeachByID(btn.ID);//获取对象
btnto.IDNumber = btn.IDNumber;
btnto.sex = btn.sex;
btnto.name = btn.name;
btnto.MobilePhone = btn.MobilePhone;
btnto.LeaveDate = btn.LeaveDate;
btnto.EntryDate = btn.EntryDate;
btnto.WeChat = btn.WeChat;
btnto.Remark = btn.Remark;
btnto.Email = btn.Email;
btnto.ContactAddress = btn.ContactAddress;
btnto.ComCode = btn.ComCode;
db.BeginTransaction();//事务开始
db.Update <Teachers>(btnto);
SYSAccount sysa = TeacherData.GetSYS_AccountByID(btnto.BindAccountID);//获取对象
sysa.ACC_ComCode = btn.ComCode;
db.Update <SYSAccount>(sysa); //修改权限表
db.Commit(); //事务提交
db.Dispose(); //资源释放
ret = true; //修改成功
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();//资源释放
throw new Exception(ex.Message + "。" + ex.InnerException.Message);
}
return(ret);
}
/// <summary>
/// 手动调整剩余课时
/// </summary>
/// <param name="ENID"></param>
/// <param name="AdjustNum"></param>
/// <returns></returns>
public static bool AdjustLeftHour(string ENID, int AdjustNum, string loginid)
{
bool ret = false;
DBRepository db = new DBRepository(DBKeys.PRX);
try
{
db.BeginTransaction();
Enroll en = db.GetById <Enroll>(ENID);
TransferRecord tr = new TransferRecord();//添加日志记录
tr.StudentID = en.StudentID;
tr.BeforeHours = en.ClassHour - en.UsedHour;
tr.AfterHours = en.ClassHour - en.UsedHour + +decimal.Parse(AdjustNum.ToString());
tr.TypeID = 3;//自定义调整课时
tr.CreateTime = DateTime.Now;
tr.CreatorId = loginid;
tr.ENID = en.ID;
tr.ClassID = en.ClassID;
db.Insert(tr);
en.UsedHour = en.UsedHour - decimal.Parse(AdjustNum.ToString());//调整剩余课时
en.UpdateTime = DateTime.Now;
en.UpdatorId = loginid;
db.Update(en);
db.Commit();
db.Dispose();
ret = true;
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message);
}
return(ret);
}
/// <summary>
/// 修改班级表并修改考勤数据
/// </summary>
/// <param name="Clas"></param>
/// <returns></returns>
public static bool Update_Classes(Classes Clas)
{
DBRepository db = new DBRepository(DBKeys.PRX);
db.BeginTransaction(); //事务开始
Classes Classes = ClassesData.GetClassesByID(Clas.ID); //根据ID获取数据,然后对比
try
{
if (ClassesData.Update_DB(Clas, db)) //最后修改班级数据,注意时间类型，而且需要在前台把所有的值
{
ClassesData.Update_TeacherID_DB(Clas.TeacherID, Clas.Teacher2ID, Clas.ID, db); //修改班级信息
db.Commit(); //事务提交
db.Dispose(); //资源释放
}
return(true);
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();//资源释放
throw new Exception(ex.Message + "。" + ex.InnerException.Message);
}
}
/// <summary>
/// 审核不通过
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static int AuditNotThrough(EnrollAudit erau, int AppointmentApStateID)
{
int ret = 0;
DBRepository db = new DBRepository(DBKeys.PRX);
db.BeginTransaction();//事务开始
try
{
UpdateEnrollAudit(erau.APID, erau.StateID, erau.UpdateTime, erau.UpdatorId, db);
ret = UpdateAppointment(erau.APID, AppointmentApStateID, erau.UpdateTime, erau.UpdatorId, db); //最后修改Appointment状态为3，3为已报名
db.Commit(); //事务提交
db.Dispose(); //资源释放
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message + "。" + ex.InnerException.Message);
}
return(ret);
}
/// <summary>
/// 新增,返回的是主键
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool AddClassList(ClassList Clas, Date date, Classes Clss, Weekday weekday)
{
bool ret = false;
DBRepository db = new DBRepository(DBKeys.PRX); //事务
int curriculum = Clss.TotalLesson; //总的新增记录条数
DateTime thisdate = date.Start_Date; //循环到的日期
try
{
ArrayList al = new ArrayList();
if (!string.IsNullOrWhiteSpace(weekday.Monday))//判断有选择星期
{
al.Add(2);
}
if (!string.IsNullOrWhiteSpace(weekday.Tuesday))//判断有选择星期
{
al.Add(3);
}
if (!string.IsNullOrWhiteSpace(weekday.Wednesday))//判断有选择星期
{
al.Add(4);
}
if (!string.IsNullOrWhiteSpace(weekday.Thursday))//判断有选择星期
{
al.Add(5);
}
if (!string.IsNullOrWhiteSpace(weekday.Friday))//判断有选择星期
{
al.Add(6);
}
if (!string.IsNullOrWhiteSpace(weekday.Saturday))//判断有选择星期
{
al.Add(7);
}
if (!string.IsNullOrWhiteSpace(weekday.Sunday))//判断有选择星期
{
al.Add(1);
}
if (Convert.ToInt32(thisdate.DayOfWeek) > int.Parse(al[0].ToString())) //如果当前星期大于要循环的第一个星期
{
thisdate = GetWeekFirstDaySun(thisdate).AddDays(7); //起始设为下一周第一天
}
else
{
thisdate = GetWeekFirstDaySun(thisdate); //起始设为当前周第一天
}
db.BeginTransaction(); //事务开始
for (int i = 1; i <= curriculum;) //根据总课时和开始时间来生成课程
{
foreach (int theweek in al) //循环星期
{
if (i <= curriculum) //如果课时还未生成完则继续
{
ClassList CL = new ClassList();
CL.ClassID = Clas.ClassID;
CL.ClassIndex = i;
CL.ClassDate = DateTime.Parse(thisdate.AddDays(theweek - 1).ToShortDateString() + GetStartTimePeriodByid(Clas.TimePeriod.Value));
CL.TimePeriod = Clas.TimePeriod;
CL.StateID = 1;
CL.TeacherID = Clas.TeacherID;
CL.Teacher2ID = Clas.Teacher2ID;
CL.RoomID = Clas.RoomID;
CL.CreateTIme = DateTime.Now;
CL.CreatorId = Clas.CreatorId;
CL.weekday = theweek;
db.Insert(CL);
List <Enroll> Enroll = GetEnrollByID(Clas.ClassID); //获取Enroll报名表的数据
AttendanceRecord attend = new AttendanceRecord();
attend.CreateTime = DateTime.Now; //创建时间
attend.CreatorId = Clas.CreatorId; //创建人
attend.ClassID = Clas.ClassID; //班级编号
attend.ClassIndex = i; //班次序号，也就是班级生成的集体上课记录
attend.AttendanceTypeID = 1; //上课状态,默认为1，未考勤
for (int j = 0; j < Enroll.Count(); j++)
{
attend.StudentID = Enroll[j].StudentID;
if (Enroll[j].ClassHour - Enroll[j].UsedHour >= i) //如果剩余课时还够
{
db.Insert <AttendanceRecord>(attend); //增加上课记录表数据
}
}
i = i + 1;
}
}
thisdate = thisdate.AddDays(7); //循环到下一周
}
if (UpdateClasses(Clas.ClassID, Clss.TotalLesson, db) > 0) //反写回Classes班级维护表的总课时字段
{
db.Commit(); //事务提交
db.Dispose(); //资源释放
ret = true; //新增成功
}
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();//资源释放
throw new Exception(ex.Message + "。" + ex.InnerException.Message);
}
return(ret);
}
/// <summary>
/// 升班的操作
/// </summary>
/// <param name="oldclassid"></param>
/// <param name="newclassid"></param>
/// <param name="ja"></param>
/// <param name="operateid"></param>
/// <returns></returns>
public static bool UpClass(string oldclassid, string newclassid, JArray ja, string operateid)
{
bool ret = false;
DBRepository db = new DBRepository(DBKeys.PRX);
db.BeginTransaction();
try
{
foreach (var item in ja)
{
string enid = ((JObject)item)["enid"].ToString(); //报名记录id
decimal newclasshour = decimal.Parse(((JObject)item)["newclasshour"].ToString()); //转换后的课时
decimal upprice = decimal.Parse(((JObject)item)["upprice"].ToString());; //升班差价
Enroll en = db.GetById <Enroll>(enid); //获取学员的报名记录
TransferRecord tf = new TransferRecord(); //转移记录
tf.StudentID = en.StudentID;
tf.TypeID = 2; //升班
tf.BeforeHours = en.ClassHour - en.UsedHour;
tf.AfterHours = newclasshour;
tf.CreateTime = DateTime.Now;
tf.CreatorId = operateid;
tf.Remark = "升班：原报名号:" + enid + " 原班级号：" + oldclassid + " 新班级：" + newclassid;
db.Insert(tf);
//生成新的报名记录
Enroll upen = new Enroll();
upen.ID = CommonData.DPGetTableMaxId("EN", "ID", "Enroll", 8, db);
upen.APID = "";
upen.StudentID = en.StudentID;
upen.ClassID = newclassid;
upen.ClassHour = newclasshour;
upen.UsedHour = 0;//改变所消耗课时
upen.Price = 0;
upen.Paid = 0;
upen.CreatorId = operateid;
upen.CreateTime = DateTime.Now;
upen.StateID = 1;//无需审核
upen.UpPrice = upprice;
db.Insert(upen);
FundsFlow fl = new FundsFlow(); //资金流水
fl.TypeID = 4; //类型为升班
fl.Amount = upprice;
fl.KeyID = upen.ID;
fl.CreateTime = DateTime.Now;
fl.CreatorId = operateid;
db.Insert(fl);
Classes ca = db.GetById <Classes>(oldclassid);
ca.StateID = 4;//原来班级状态
db.Update(ca);
//原课时清零
en.UsedHour = en.ClassHour;
db.Update(en);
}
ret = true;
db.Commit();
db.Dispose();
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();//资源释放
throw new Exception(ex.Message);
}
return(ret);
}
/// <summary>
/// 审核通过
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static int Audited(EnrollAudit erau)
{
int ret = 0;
DBRepository db = new DBRepository(DBKeys.PRX);
db.BeginTransaction();//事务开始
try
{
UpdateEnrollAudit(erau.APID, erau.StateID, erau.UpdateTime, erau.UpdatorId, db);
string sql = "select * from EnrollAudit where APID=@APID";
var parameters = new DynamicParameters();
parameters.Add("@APID", erau.APID);
List <EnrollAudit> List = db.Query <EnrollAudit>(sql, parameters).ToList();
Enroll er = new Enroll();
foreach (var en in List)
{
er.ID = CommonData.DPGetTableMaxId("EN", "ID", "Enroll", 8, db);
er.APID = en.APID;
er.ApprovedBy = en.ApprovedBy;
er.ApprovedRemark = en.ApprovedRemark;
er.ApprovedTime = en.ApprovedTime;
er.ClassHour = en.ClassHour;
er.ClassID = en.ClassID;
er.CreateTime = en.CreateTime;
er.CreatorId = en.CreatorId;
er.DiscountID = en.DiscountID;
er.DiscountPrice = en.DiscountPrice;
er.Paid = en.Paid;
er.Price = en.Price;
er.Remark = en.Remark;
er.StateID = en.StateID;
er.StudentID = en.StudentID;
er.UpdateTime = en.UpdateTime;
er.UpdatorId = en.UpdatorId;
er.UsedHour = en.UsedHour;
er.CollectionRec = en.CollectionRec;
db.Insert <Enroll>(er); //复制 EnrollAudit表数据到报名表
FundsFlow fl = new FundsFlow(); //资金流水
fl.TypeID = 1; //类型1报名
fl.Amount = er.Paid;
fl.KeyID = er.ID;
fl.CreateTime = DateTime.Now;
fl.CreatorId = er.CreatorId;
db.Insert(fl);
//如果所报名的班级意见排课了，需要重新生成排课记录
Classes cl = db.GetById <Classes>(er.ClassID);
if (cl.StateID.Value == 2 || cl.StateID.Value == 3)//如果班级状态是已排课，或已上课，生成课程表AttendanceRecord
{
List <vw_ClassAttendanceList> clist = new List <vw_ClassAttendanceList>();
clist = db.Query <vw_ClassAttendanceList>("select * from vw_ClassAttendanceList where ClassID = '" + cl.ID + "'", null).ToList();
int aa = Convert.ToInt32(er.ClassHour) < clist.Count() ? Convert.ToInt32(er.ClassHour) : clist.Count();//取较小数做循环
for (int i = 0; i < aa; i++)
{
AttendanceRecord attend = new AttendanceRecord();
attend.CreateTime = DateTime.Now; //创建时间
attend.CreatorId = en.UpdatorId; //创建人
attend.ClassID = en.ClassID; //班级编号
attend.ClassIndex = i + 1; //班次序号，也就是班级生成的集体上课记录
attend.AttendanceTypeID = 1; //上课状态,默认为1，未考勤
attend.StudentID = en.StudentID; //学员号
db.Insert <AttendanceRecord>(attend); //增加上课记录表数据
}
}
}
ret = UpdateAppointment(erau.APID, erau.StateID, erau.UpdateTime, erau.UpdatorId, db); //最后修改Appointment状态为3，3为已报名
db.Commit(); //事务提交
db.Dispose(); //资源释放
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message + "。" + ex.InnerException.Message);
}
return(ret);
}
/// <summary>
/// 处理原始考勤记录
/// </summary>
public static void DealAttendanceOriginal(string operatorid)
{
string strsql = "select * from AttendanceOriginal where RecogniseTime is null";
List <AttendanceOriginal> aolist = MsSqlMapperHepler.SqlWithParams <AttendanceOriginal>(strsql, null, DBKeys.PRX);//获取所有未识别的数据
DBRepository db = new DBRepository(DBKeys.PRX);
try
{
db.BeginTransaction();
foreach (AttendanceOriginal ao in aolist)//循环处理
{
//智能匹配到这个学员报名过的，时间最接近的班
StringBuilder str = new StringBuilder();
str.AppendFormat(@"SELECT TOP 1 * from ClassList
WHERE ABS(DATEDIFF(Hour,ClassDate,'{0}')) < 4
AND ClassID IN (SELECT ClassID FROM Enroll WHERE StudentID = {1})
ORDER BY ABS(DATEDIFF(Hour,ClassDate,'{0}'))", ao.workDates, ao.UserID);
ClassList cl = db.Query <ClassList>(str.ToString()).FirstOrDefault(); //找到唯一班次
if (cl != null) //打卡正确，找到班次
{
//是否之前有考勤，有过考勤记录则不处理
string strar = "select top 1 * from AttendanceRecord where ClassID = '" + cl.ClassID + "' and ClassIndex = " + cl.ClassIndex + " and StudentID = '" + ao.UserID + "'";
AttendanceRecord ar = db.Query <AttendanceRecord>(strar).FirstOrDefault();
if (ar == null)//不存在就新增
{
ar = new AttendanceRecord();
ar.StudentID = ao.UserID;
ar.ClassID = cl.ClassID;
ar.ClassIndex = cl.ClassIndex;
ar.ClockTime = null;
ar.AttendanceTypeID = 1;//未考勤
ar.AttendanceWayID = null;
ar.CreateTime = DateTime.Now;
ar.CreatorId = operatorid;
int arid = db.Insert(ar);
ar = db.GetById <AttendanceRecord>(arid);
}
if (ar.AttendanceTypeID == 1) //还没考勤过
{
ar.ClockTime = ao.workDates;
ar.AttendanceTypeID = 2; //正常
ar.AttendanceWayID = 2; //设备考勤
ar.UpdateTime = DateTime.Now;
ar.UpdatorId = operatorid;
string stren = "select * from Enroll where StudentID = '" + ao.UserID + "' and ClassID = '" + cl.ClassID + "'";
Enroll en = db.Query <Enroll>(stren).FirstOrDefault();// 找到报名记录
if (en == null)
{
ao.Recognise = "无效";
ao.Remark = "没有报名记录";
ao.RecogniseTime = DateTime.Now;
db.Update(ao);
return;
//throw new Exception("没有报名记录");
}
else //扣掉学时
{
//-----添加课时变化日志记录 begin
TransferRecord tr = new TransferRecord(); //添加课时变化日志记录
tr.StudentID = en.StudentID;
tr.BeforeHours = en.ClassHour - en.UsedHour;
tr.AfterHours = en.ClassHour - en.UsedHour - 1;
tr.TypeID = 4;//考勤机自动识别
tr.CreateTime = DateTime.Now;
tr.CreatorId = operatorid;
tr.ENID = en.ID;
tr.ClassID = en.ClassID;
tr.ClassIndex = ar.ClassIndex;
db.Insert(tr);
//-----添加课时变化日志记录 end
en.UsedHour = en.UsedHour + 1;
en.UpdateTime = DateTime.Now;
en.UpdatorId = operatorid;
db.Update(en);
if (cl.StateID < 3) //班级状态如果没上课，设置成上课
{
cl.StateID = 3;
db.Update(cl);
}
ao.Recognise = "有效";
ao.Remark = "更新考勤记录，识别成功,当前剩余课时：" + (en.ClassHour - en.UsedHour).ToString();
ao.Classid = cl.ClassID;
ao.ClassIndex = cl.ClassIndex;
ao.RecogniseTime = DateTime.Now;
db.Update(ao);
}
ar.Remark = ao.Remark;
db.Update(ar);
}
else
{
ao.Recognise = "无效";
ao.Remark = "重复的考勤";
ao.RecogniseTime = DateTime.Now;
ao.Classid = cl.ClassID;
ao.ClassIndex = cl.ClassIndex;
db.Update(ao);
}
}
else//没找到对应的班次
{
ao.Recognise = "无效";
ao.Remark = "未找到对应的班次";
ao.RecogniseTime = DateTime.Now;
db.Update(ao);
}
}
db.Commit();
db.Dispose();
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();
throw new Exception(ex.Message);
}
}
/// <summary>
/// 保存考勤记录，正常考勤扣除课时
/// </summary>
/// <param name="ar"></param>
/// <param name="userid"></param>
/// <returns></returns>
public static bool saveStudentAttendance(List <AttendanceRecord> ar, string userid)
{
bool ret = false;
DBRepository db = new DBRepository(DBKeys.PRX);
try{
db.BeginTransaction();//事务开始
foreach (AttendanceRecord value in ar)
{
if (value == null)
{
continue;
}
AttendanceRecord btnto = AttendaceData.GetAttendanceRecordByStudentClass(value.StudentID, value.ClassID, value.ClassIndex); //获取对象
if (value.ClockTime != null) //正常打卡扣除课时
{
Enroll enroll = db.Query <Enroll>("select * from Enroll where StudentID = '" + value.StudentID + "' and ClassID = '" + value.ClassID + "'").FirstOrDefault();
if (enroll != null)
{
//-----添加课时变化日志记录 begin
TransferRecord tr = new TransferRecord();//添加课时变化日志记录
tr.StudentID = enroll.StudentID;
tr.BeforeHours = enroll.ClassHour - enroll.UsedHour;
tr.AfterHours = enroll.ClassHour - enroll.UsedHour - 1;
tr.TypeID = 5;//ERP考勤操作
tr.CreateTime = DateTime.Now;
tr.CreatorId = userid;
tr.ENID = enroll.ID;
tr.ClassID = enroll.ClassID;
db.Insert(tr);
//-----添加课时变化日志记录 end
enroll.UsedHour = enroll.UsedHour + 1;
db.Update(enroll);
Students s = StudentData.GetStudentsByID(value.StudentID); //获取学员
if (s.StateID != null && (s.StateID.Value == 1 || s.StateID.Value == 3)) //冻结和未读状态下
{
s.StateID = 2; //改成在读
db.Update <Students>(s);
}
ClassList cl = db.Query <ClassList>("select * from ClassList where ClassID = '" + value.ClassID + "' and ClassIndex = " + value.ClassIndex).FirstOrDefault();
cl.StateID = 2;//课时状态变成已上
db.Update <ClassList>(cl);
}
else
{
throw new Exception("获取报名记录错误");
}
//插入考勤记录---------------------------------
if (btnto == null)
{
btnto = new AttendanceRecord();
btnto.OutStatus = 0;
btnto.ClockTime = value.ClockTime;
btnto.AttendanceTypeID = 2;//考勤正常
btnto.CreateTime = DateTime.Now;
btnto.CreatorId = userid;
btnto.AttendanceWayID = 3;//工作人员操作
btnto.ClassID = value.ClassID;
btnto.ClassIndex = value.ClassIndex;
btnto.StudentID = value.StudentID;
btnto.Remark = "更新考勤记录，识别成功,当前剩余课时：" + (enroll.ClassHour - enroll.UsedHour).ToString();
db.Insert(btnto);//新增考勤
}
else//如果之前有考勤记录了，且未打卡则更新打卡
{
if (btnto.ClockTime == null)//未打卡
{
btnto.OutStatus = 0;
btnto.ClockTime = value.ClockTime;
btnto.AttendanceTypeID = 2;//考勤正常
btnto.UpdateTime = DateTime.Now;
btnto.UpdatorId = userid;
btnto.AttendanceWayID = 3;//工作人员操作
btnto.ClassID = value.ClassID;
btnto.ClassIndex = value.ClassIndex;
btnto.StudentID = value.StudentID;
btnto.Remark = "更新考勤记录，识别成功,当前剩余课时：" + (enroll.ClassHour - enroll.UsedHour).ToString();
db.Update(btnto);//更新
}
else
{
throw new Exception("已有考勤记录！");
}
}
}
if (value.ClockTime == null)//未打卡
{
Enroll enroll = db.Query <Enroll>("select * from Enroll where StudentID = '" + value.StudentID + "' and ClassID = '" + value.ClassID + "'").FirstOrDefault();
if (enroll != null && value.AttendanceTypeID == 3)//缺勤状态还是要扣课时，同时更新报名记录的小号课时
{
//-----添加课时变化日志记录 begin
TransferRecord tr = new TransferRecord();//添加课时变化日志记录
tr.StudentID = enroll.StudentID;
tr.BeforeHours = enroll.ClassHour - enroll.UsedHour;
tr.AfterHours = enroll.ClassHour - enroll.UsedHour - 1;
tr.TypeID = 5;//ERP考勤操作
tr.CreateTime = DateTime.Now;
tr.CreatorId = userid;
tr.ENID = enroll.ID;
tr.ClassID = enroll.ClassID;
db.Insert(tr);
//-----添加课时变化日志记录 end
enroll.UsedHour = enroll.UsedHour + 1;
db.Update(enroll);
ClassList cl = db.Query <ClassList>("select * from ClassList where ClassID = '" + value.ClassID + "' and ClassIndex = " + value.ClassIndex).FirstOrDefault();
cl.StateID = 2;//课时状态变成已上
db.Update <ClassList>(cl);
}
//新增考勤记录--------------------------------------------------------------
if (btnto == null)
{
btnto = new AttendanceRecord();
btnto.AttendanceTypeID = value.AttendanceTypeID;
btnto.CreateTime = DateTime.Now;
btnto.CreatorId = userid;
btnto.AttendanceWayID = 3;//工作人员操作
btnto.ClassID = value.ClassID;
btnto.ClassIndex = value.ClassIndex;
btnto.StudentID = value.StudentID;
btnto.Remark = "更新考勤记录，识别成功,当前剩余课时：" + (enroll.ClassHour - enroll.UsedHour).ToString();
db.Insert(btnto);//新增考勤
}
else//如果之前有考勤记录了，且未打卡则更新打卡
{
if (btnto.ClockTime == null)//已打卡，不允许改成请假
{
btnto.AttendanceTypeID = value.AttendanceTypeID;
btnto.UpdateTime = DateTime.Now;
btnto.UpdatorId = userid;
btnto.AttendanceWayID = 3;//工作人员操作
btnto.ClassID = value.ClassID;
btnto.ClassIndex = value.ClassIndex;
btnto.StudentID = value.StudentID;
btnto.Remark = "更新考勤记录，识别成功,当前剩余课时：" + (enroll.ClassHour - enroll.UsedHour).ToString();
db.Update(btnto);//更新
}
else
{
throw new Exception("已有考勤记录！");
}
}
}
}
db.Commit(); //事务提交
db.Dispose(); //资源释放
ret = true; //新增成功
}
catch (Exception ex)
{
db.Rollback();
db.Dispose();//资源释放
throw new Exception(ex.Message + "。" + ex.InnerException.Message);
}
return(ret);
}
