/// <summary>
/// 获取文件路径
/// </summary>
/// <param name="classId"></param>
/// <param name="classIndex"></param>
/// <returns></returns>
public static List <vw_ClassListJob> ClassListJob(string classid, int classindex)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_ClassListJob "; //表或者视图
orderby = "id"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append("select * from vw_ClassListJob where ");
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(classid))//班级ID
{
sb.Append(" and classid = @ClassID");
}
if (classindex != 0)//班级行号
{
sb.Append(" and classindex = @ClassIndex ");
}
sb.Append(" order by id ");
var parameters = new DynamicParameters();
parameters.Add("@classid", classid);
parameters.Add("@classindex", classindex);
return(MsSqlMapperHepler.SqlWithParams <vw_ClassListJob>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 根据班级ID获取学员考勤信息
/// </summary>
/// <param name="classid"></param>
/// <returns></returns>
public static List <vw_AttendanceRecord> GetAttendanceRecordByClassID(string classid, int ClassIndex)
{
string strsql = "select * from vw_AttendanceRecord where ClassID = '" + classid + "' and ClassIndex = " + ClassIndex;
List <vw_AttendanceRecord> ret = new List <vw_AttendanceRecord>();
ret = MsSqlMapperHepler.SqlWithParams <vw_AttendanceRecord>(strsql, null, DBKeys.PRX);
return(ret);
}
/// <summary>
/// 获取启用课程下拉
/// </summary>
/// <returns></returns>
public static List <CommonEntity> GetCourseIL()
{
List <CommonEntity> ret;
string sql = "select id,CourseName as name from Course";
ret = MsSqlMapperHepler.SqlWithParams <CommonEntity>(sql.ToString(), null, DBKeys.PRX);
return(ret);
}
/// <summary>
/// 跳转查询账号信息
/// </summary>
/// <param name="apid"></param>
/// <returns></returns>
public static List <SYSAccount> GetAccounts_Update(string ACC_Account)
{
String sql = "select * from SYS_Account where ACC_Account = @ACC_Account ";
var dynamic = new DynamicParameters();
dynamic.Add("@ACC_Account", ACC_Account);
return(MsSqlMapperHepler.SqlWithParams <SYSAccount>(sql, dynamic, DBKeys.PRX));
}
/// <summary>
/// 根据预约ID获取更近记录
/// </summary>
/// <param name="apid"></param>
/// <returns></returns>
public static List <FollowRecord> GetFollowListByAPID(string apid)
{
string sql = @" select * from FollowRecord where APID = @APID order by id desc";
var parameters = new DynamicParameters();
parameters.Add("@APID", apid);
return(MsSqlMapperHepler.SqlWithParams <FollowRecord>(sql, parameters, DBKeys.PRX));
}
/// <summary>
/// 获取获取试听，判断是否有重复试听记录
/// </summary>
/// <param name="apid"></param>
/// <returns></returns>
public static List <vw_Enroll> GetEnrollPrintByApidAndClassid(string apid, string classid)
{
String sql = "select * from vw_Enroll where APID = @APID and ClassID = @ClassID";
var dynamic = new DynamicParameters();
dynamic.Add("@APID", apid);
dynamic.Add("@ClassID", classid);
return(MsSqlMapperHepler.SqlWithParams <vw_Enroll>(sql, dynamic, DBKeys.PRX));
}
/// <summary>
/// 获取班级字典信息,不包括试听
/// </summary>
/// <param name="dicTypeID"></param>
/// <returns>用于下拉的绑定项目</returns>
public static List <CommonEntity> GetClassesItemList()
{
StringBuilder sb = new StringBuilder();
sb.Append(" select ID,ClassName name from Classes");
sb.Append(" WHERE TeachTypeID <> 1 and StateID = 1");
var parameters = new DynamicParameters();
return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX));
}
public static List <vw_Menu> GetMenuList(string loginid)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT *,'{0}' + URL as logurl ", preurl);
sb.AppendFormat(" FROM [vw_StaffMenu] a WITH(NOLOCK)");
sb.AppendFormat(" where ACC_Account = '{0}'", loginid);
sb.AppendFormat(" ORDER BY ParentId,OrderIndex");
return(MsSqlMapperHepler.SqlWithParams <vw_Menu>(sb.ToString(), null, DBKeys.PRX));
}
/// <summary>
/// 获取优惠下拉
/// </summary>
/// <returns></returns>
public static List <Discount> GetDiscountItems()
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * ");
sb.Append(" FROM [Discount] a WITH(NOLOCK)");
sb.Append(" WHERE a.StateID <> 2");
sb.Append(" ORDER BY CreateTime desc");
return(MsSqlMapperHepler.SqlWithParams <Discount>(sb.ToString(), null, DBKeys.PRX));
}
/// <summary>
/// 获取字典列表
/// </summary>
/// <param name="dicTypeID"></param>
/// <returns>用于下拉的绑定项目</returns>
public static List <string> GetSYS_SystemRoleList_ROLE_Id(int ROLE_Id)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ROLE_Name as name FROM SYS_SystemRole");
sb.Append(" WHERE ROLE_Id = @ROLE_Id");
var parameters = new DynamicParameters();
parameters.Add("@ROLE_Id", ROLE_Id);
return(MsSqlMapperHepler.SqlWithParams <string>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取字典列表
/// </summary>
/// <param name="dicTypeID"></param>
/// <returns>用于下拉的绑定项目</returns>
public static List <SYS_Role> GetSYS_SystemRoleList(int ROLE_OrderIndex)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ROLE_Id as id,ROLE_Name as name FROM SYS_SystemRole");
sb.Append(" WHERE ROLE_OrderIndex = @ROLE_OrderIndex");
var parameters = new DynamicParameters();
parameters.Add("@ROLE_OrderIndex", ROLE_OrderIndex);
return(MsSqlMapperHepler.SqlWithParams <SYS_Role>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 甲方获取所报班级的名称
/// </summary>
/// <param name="dicTypeID"></param>
/// <returns>用于下拉的绑定项目</returns>
public static List <vw_Enroll> Getvw_EnrollList(string StudentID)
{
StringBuilder sb = new StringBuilder();
sb.Append("select ID,ClassName as name,ClassHour,UsedHour from vw_Enroll");
sb.Append(" where TeachTypeID<>1 and StudentID=@StudentID");
var parameters = new DynamicParameters();
parameters.Add("@StudentID", StudentID);
return(MsSqlMapperHepler.SqlWithParams <vw_Enroll>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 新增,返回的是主键
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
// public static bool AddClassList(ClassList Clas,Date date,Classes Clss,Weekday weekday)
//{
// var number = 0;//因为事务查询数据库会直接卡死
// bool ret = false;
// DBRepository db = new DBRepository(DBKeys.PRX);
// try
// {
// db.BeginTransaction();//事务开始
// var lenthed = date.End_Date.Subtract(date.Start_Date).Days; //取日期之间的差距天数
// for (int i = 0; i <= lenthed; i++)
// {
// var Date = date.Start_Date.AddDays(i);
// var week = Convert.ToInt32(Date.DayOfWeek); //这个时间是星期几
// int Monday=-1;
// int Tuesday = -1;
// int Wednesday = -1;
// int Thursday = -1;
// int Friday = -1;
// int Saturday = -1;
// int Sunday = -1;
// if (!string.IsNullOrWhiteSpace(weekday.Monday))
// {
// Monday = week.ToString().IndexOf(weekday.Monday); //判断是否包含在星期里面
// }
// if (!string.IsNullOrWhiteSpace(weekday.Tuesday))
// {
// Tuesday = week.ToString().IndexOf(weekday.Tuesday); //判断是否包含在星期里面
// }
// if (!string.IsNullOrWhiteSpace(weekday.Wednesday))
// {
// Wednesday = week.ToString().IndexOf(weekday.Wednesday); //判断是否包含在星期里面
// }
// if (!string.IsNullOrWhiteSpace(weekday.Thursday))
// {
// Thursday = week.ToString().IndexOf(weekday.Thursday); //判断是否包含在星期里面
// }
// if (!string.IsNullOrWhiteSpace(weekday.Friday))
// {
// Friday = week.ToString().IndexOf(weekday.Friday); //判断是否包含在星期里面
// }
// if (!string.IsNullOrWhiteSpace(weekday.Saturday))
// {
// Saturday = week.ToString().IndexOf(weekday.Saturday); //判断是否包含在星期里面
// }
// if (!string.IsNullOrWhiteSpace(weekday.Sunday))
// {
// Sunday = week.ToString().IndexOf(weekday.Sunday); //判断是否包含在星期里面
// }
// if (Monday > -1 || Tuesday > -1 || Wednesday > -1 || Thursday > -1 || Friday > -1 || Saturday > -1 || Sunday > -1)
// {
// if (number==0)
// {
// number = Getnumber(Clas.ClassID);//取行号
// }
// number = number + 1;
// Clas.weekday = week;//星期几
// Clas.ClassDate = Date;//上课日期
// Clas.ClassIndex = number; //班次序号，也就是班级生成的集体上课记录
// db.Insert<ClassList>(Clas); //增加排课表数据
// // MsSqlMapperHepler.Insert<ClassList>(Clas, DBKeys.PRX); //增加排课表数据
// 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 = number;//班次序号，也就是班级生成的集体上课记录
// attend.AttendanceTypeID = 1;//上课状态,默认为1，未考勤
// attend.AttendanceWayID = 3;//AttendanceWayID默认3，教师操作的考勤。
// for (int j = 0; j < Enroll.Count(); j++)
// {
// attend.StudentID = Enroll[j].StudentID;
// db.Insert<AttendanceRecord>(attend);//增加上课记录表数据
// //MsSqlMapperHepler.Insert<AttendanceRecord>(attend, DBKeys.PRX); //增加上课记录表数据
// }
// }
// }
// if (UpdateClasses(Clas.ClassID, Clss.TotalLesson,db) > 0)//反写回Classes班级维护表的总课时字段
// {
// db.Commit(); //事务提交
// ret = true;//新增成功
// }
// db.Dispose(); //资源释放
// }
// catch (Exception ex)
// {
// db.Rollback();
// db.Dispose();//资源释放
// throw new Exception(ex.Message + "。" + ex.InnerException.Message);
// }
// return ret;
//}
/// <summary>
/// 获取Enroll表数据
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
//public static Enroll GetEnrollByID(string ClassID)
//{
// return MsSqlMapperHepler.GetOne<Enroll>(ClassID, DBKeys.PRX);
//}
public static List <Enroll> GetEnrollByID(string ClassID)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from Enroll ");
sb.Append(" where ClassID=@ClassID ");
var parameters = new DynamicParameters();
parameters.Add("@ClassID", ClassID);
return(MsSqlMapperHepler.SqlWithParams <Enroll>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取学生字典列表
/// </summary>
/// <param name="dicTypeID"></param>
/// <returns>用于下拉的绑定项目</returns>
public static List <CommonEntity> GetStudentsList()
{
StringBuilder sb = new StringBuilder();
sb.Append("select id,name from Students");
sb.Append(" WHERE 1=@one");
var parameters = new DynamicParameters();
var one = 1;
parameters.Add("@one", one);
return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取分校字典列表
/// </summary>
/// <param name="dicTypeID"></param>
/// <returns>用于分校下拉的绑定项目</returns>
public static List <CommonEntity> Get_SYS_Company_List()
{
StringBuilder sb = new StringBuilder();
sb.Append(" select COMP_Code as ID,COMP_Name as name from SYS_Company ");
sb.Append(" WHERE 1 = @StateID");
var parameters = new DynamicParameters();
var StateID = 1;
parameters.Add("@StateID", StateID);
return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取课程字典列表
/// </summary>
/// <param name="dicTypeID"></param>
/// <returns>用于下拉的绑定项目</returns>
public static List <CommonEntity> GetCourseList()
{
StringBuilder sb = new StringBuilder();
sb.Append(" select ID,CourseName name from Course");
sb.Append(" WHERE StateID <> @StateID");
var parameters = new DynamicParameters();
var StateID = 2;
parameters.Add("@StateID", StateID);
return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <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="dicTypeID"></param>
/// <returns>班级用于下拉的绑定项目</returns>
public static List <CommonEntity> GetClassesList(int CourseID, string ClassID)
{
StringBuilder sb = new StringBuilder();
sb.Append("select distinct cl.id+','+CAST(cll.ClassIndex as nvarchar(10)) as id,cl.ClassName+'('+convert(varchar(100), cll.ClassDate ,23)+')' as Name");
sb.Append(" from Classes cl inner join ClassList cll");
sb.Append(" on cl.ID=cll.ClassID ");
sb.Append(" where cl.StateID <> 4 and cl.CourseID = @CourseID and cl.id <> @ClassID");
var parameters = new DynamicParameters();
parameters.Add("@CourseID", CourseID);
parameters.Add("@ClassID", ClassID);
return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 根据角色的权限来获取相应的留言信息
/// </summary>
/// <param name="roleid"></param>
/// <returns></returns>
public static List <vw_Message> GetMessageList(List <string> roleids)
{
StringBuilder where = new StringBuilder();
foreach (string rid in roleids)
{
where.AppendFormat("or ToRoles like '%{0}%'", rid);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"select top 100 * from vw_Message where 1=1 {0} {1}", where.ToString(), "order by CreateTime desc");
List <vw_Message> ret = MsSqlMapperHepler.SqlWithParams <vw_Message>(sql.ToString(), null, DBKeys.PRX);
return(ret);
}
/// <summary>
/// 获取字典列表
/// </summary>
/// <param name="dicTypeID"></param>
/// <returns>因为学员状态有重新的名称，所以单独写一个下拉的绑定</returns>
public static List <CommonEntity> GetDictionary_StudentSource_List(int DicTypeID)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT DicItemID id,DicItemName name");
sb.Append(" FROM DictionaryItem a WITH(NOLOCK)");
sb.Append(" INNER JOIN DictionaryType b WITH(NOLOCK) ON a.DicTypeID = b.DicTypeID");
sb.Append(" WHERE b.DicTypeID = @DicTypeID and a.recordState <> '2' AND a.DicItemID < > 0 AND a.DicItemID <> 1");
sb.Append(" ORDER BY Sort");
var parameters = new DynamicParameters();
parameters.Add("@DicTypeID", DicTypeID);
return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
///乙方获取所报班级的名称
/// </summary>
/// <param name="dicTypeID"></param>
/// <returns>用于下拉的绑定项目</returns>
public static List <CommonEntity> GetClasses(string StudentID)
{
StringBuilder sb = new StringBuilder();
sb.Append("select ClassID as ID,ClassName+'-已报'as Name from vw_Enroll");
sb.Append(" where StudentID=@StudentID");
sb.Append(" union ");
sb.Append(" select ID,ClassName as Name from vw_Classes");
sb.Append(" where StateID<>4 and ID not in ");
sb.Append(" ( select ClassID from vw_Enroll ");
sb.Append(" where StudentID=@StudentID ) ");
var parameters = new DynamicParameters();
parameters.Add("@StudentID", StudentID);
return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取分校字典列表
/// </summary>
/// <param name="dicTypeID"></param>
/// <returns>用于分校下拉的绑定项目</returns>
public static List <CommonEntity> Get_SYS_Company_COMP_Code(string COMP_Code)
{
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(COMP_Code) || COMP_Code == "1")
{
sb.Append("SELECT '' AS ID,'--请选择--' AS name UNION ALL select COMP_Code as ID,COMP_Name as name from SYS_Company ");
sb.Append(" WHERE 1 = 1 ");
}
else
{
sb.Append("select COMP_Code as ID,COMP_Name as name from SYS_Company ");
sb.Append(" WHERE COMP_Code = @COMP_Code");
}
var parameters = new DynamicParameters();
parameters.Add("@COMP_Code", COMP_Code);
return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取对学生评价
/// </summary>
/// <param name="classId"></param>
/// <param name="classIndex"></param>
/// <returns></returns>
public static List <vw_StudentEvaluate> getStudentEvaluate(String classId, int classIndex)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_StudentEvaluate "; //表或者视图
orderby = "StudentID"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append("select * from vw_StudentEvaluate where ");
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(classId))//按钮中文名称
{
sb.Append(" and ClassID = @ClassID");
}
if (classIndex != 0)//按钮中文名称
{
sb.Append(" and ClassIndex = @ClassIndex ");
}
sb.Append(" order by StudentID ");
//if (!string.IsNullOrWhiteSpace(search.BTN_Name_En))//城市
// sb.AppendFormat(" and BTN_Name_En like '%{0}%' ", search.BTN_Name_En);
var parameters = new DynamicParameters();
parameters.Add("@ClassID", classId);
parameters.Add("@ClassIndex", classIndex);
return(MsSqlMapperHepler.SqlWithParams <vw_StudentEvaluate>(sb.ToString(), parameters, DBKeys.PRX));
// var list = CommonPage<vw_StudentEvaluate>.GetPageList(
//out allcount, table, fields: fields, where: where.Trim(),
//orderby: orderby, pageindex: search.CurrentPage, pagesize: search.PageSize, connect: DBKeys.PRX);
// return new PagedList<vw_StudentEvaluate>(list, search.CurrentPage, search.PageSize, allcount);
}
/// <summary>
/// 获取学生的考勤情况
/// </summary>
/// <param name="classId"></param>
/// <param name="classIndex"></param>
/// <returns></returns>
public static List <AttendanceRecord> getStudentCheck(String classId, int classIndex)
{
/**
* string sql = @" select * from FollowRecord where APID = @APID";
* var parameters = new DynamicParameters();
* parameters.Add("@APID", apid);
*
* */
StringBuilder sb = new StringBuilder();//构建where条件
//
//sb.Append("select a.*,c.Name as Name,c.BindPhone as Phone ,");
//sb.Append(" (b.ClassHour-b.UsedHour) as LeftHour from AttendanceRecord a, Enroll b, Students c ");
//sb.Append(" where a.StudentID=b.StudentID and b.StudentID = c.ID ");
//
sb.Append(@" select a.[ID]
,b.[StudentID]
,a.[ClassID]
,a.[ClassIndex]
,a.[ClockTime]
,a.[AttendanceTypeID]
,a.[AttendanceWayID]
,a.[Evaluate]
,a.[Remark]
,a.[CreateTime]
,a.[CreatorId]
,a.[OutStatus]
,a.[UpdateTime]
,a.[UpdatorId],
c.Name as Name,
c.BindPhone as Phone,
(b.ClassHour-b.UsedHour) as LeftHour
FROM Enroll b
INNER JOIN ClassList e ON b.ClassID =e.ClassID
inner join AttendanceRecord a on a.StudentID=b.StudentID AND a.ClassID=b.ClassID AND a.ClassIndex = e.ClassIndex
LEFT join Students c on b.StudentID = c.ID
WHERE 1=1 and b.StateID <> 6 and b.StateID <> 5 "); //去掉了冻结和完结的人数
if (!string.IsNullOrWhiteSpace(classId)) //按钮中文名称
{
sb.Append(" and b.ClassID = @ClassID ");
}
if (classIndex != 0)//按钮中文名称
{
sb.Append(" and e.ClassIndex = @ClassIndex ");
}
//if (!string.IsNullOrWhiteSpace(search.BTN_Name_En))//城市
// sb.AppendFormat(" and BTN_Name_En like '%{0}%' ", search.BTN_Name_En);
var parameters = new DynamicParameters();
parameters.Add("@ClassID", classId);
parameters.Add("@ClassIndex", classIndex);
return(MsSqlMapperHepler.SqlWithParams <AttendanceRecord>(sb.ToString(), parameters, DBKeys.PRX));
// var list = CommonPage<vw_StudentEvaluate>.GetPageList(
//out allcount, table, fields: fields, where: where.Trim(),
//orderby: orderby, pageindex: search.CurrentPage, pagesize: search.PageSize, connect: DBKeys.PRX);
// return new PagedList<vw_StudentEvaluate>(list, search.CurrentPage, search.PageSize, allcount);
}
/// <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="enid"></param>
/// <returns></returns>
public static List <TransferRecord> GetHoursLogByENID(string enid)
{
string strsql = "select a.*,dbo.getDicNameByID(23,a.TypeID) AS TypeName from [TransferRecord] a where a.ENID = '" + enid + "'";
return(MsSqlMapperHepler.SqlWithParams <TransferRecord>(strsql, null, DBKeys.PRX));
}
/// <summary>
/// 获取所有在职的教师
/// </summary>
/// <returns></returns>
public static List <Teachers> getOnWorkTeachers()
{
string strsql = "select * from Teachers where LeaveDate is null";
return(MsSqlMapperHepler.SqlWithParams <Teachers>(strsql, null, DBKeys.PRX));
}