/// <summary>
/// 获取列表
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static Classes GetClassesList()
{
string strsql = "select id from Classes order by CreateTime desc ";
var parameters = new DynamicParameters();
return(MsSqlMapperHepler.SqlWithParamsSingle <Classes>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取主键类型是整数的签名对象
/// </summary>
/// <param name="id"></param>
/// <param name="typeid"></param>
/// <returns></returns>
public static SignImage GetIntKeySign(int id, int typeid)
{
string strsql = "select top 1 * from SignImage where IntKey = '" + id + "' and TypeID = " + typeid + " order by CreateTime desc";
SignImage si = MsSqlMapperHepler.SqlWithParamsSingle <SignImage>(strsql, null, DBKeys.PRX);
return(si);
}
/// <summary>
/// 获取转让协议未签名时获取的数据
/// </summary>
/// <param name="id"></param>
/// <param name="typeid"></param>
/// <returns></returns>
public static vw_Transfer GetIntKeySign_stare(int id)
{
string strsql = "select top 1 * from vw_Transfer where id = '" + id + "' order by CreateTime desc";
vw_Transfer si = MsSqlMapperHepler.SqlWithParamsSingle <vw_Transfer>(strsql, null, DBKeys.PRX);
return(si);
}
/// <summary>
/// 根据绑定的账号获取一名学员信息
/// </summary>
/// <param name="AccountID"></param>
/// <returns></returns>
public static Students GetStudentByAccountID(string AccountID)
{
string strsql = "select TOP 1 * from Students where BindAccount = @AccountID ";
var parameters = new DynamicParameters();
parameters.Add("@AccountID", AccountID);
return(MsSqlMapperHepler.SqlWithParamsSingle <Students>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static Students GetStudentsOne(string Mosaic)
{
string strsql = "select top 1 * from Students WHERE ID like @Mosaic + '%' order by CreateTime desc ";
var parameters = new DynamicParameters();
parameters.Add("@Mosaic", Mosaic);
return(MsSqlMapperHepler.SqlWithParamsSingle <Students>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 根据TimePeriod的ID获取开始时间
/// </summary>
/// <param name="perid"></param>
/// <returns></returns>
public static string GetStartTimePeriodByid(int perid)
{
string ret = "";
string strsql = "select DicItemName from DictionaryItem where DicTypeID = 8 and [DicItemID] = " + perid;
ret = " " + MsSqlMapperHepler.SqlWithParamsSingle <string>(strsql, null, DBKeys.PRX).Substring(0, 5);
return(ret);
}
/// <summary>
/// 获取本次上课信息
/// </summary>
/// <param name="classid"></param>
/// <param name="classindex"></param>
/// <returns></returns>
public static vw_ClassAttendanceList GetOneClassAttendanceList(string classid, int classindex)
{
string strsql = "select * from vw_ClassAttendanceList where ClassID = '" + classid + "' and ClassIndex = " + classindex;
vw_ClassAttendanceList ret = new vw_ClassAttendanceList();
ret = MsSqlMapperHepler.SqlWithParamsSingle <vw_ClassAttendanceList>(strsql, null, DBKeys.PRX);
return(ret);
}
/// <summary>
/// 获取SYS_Account表最大的主键
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static int Max_ACC_Id()
{
string strsql = " select isnull(max(ACC_Id),1)as ID from SYS_Account";
var parameters = new DynamicParameters();
parameters.Add("@id");
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 根据预约单号ID的主键 获取列表vw_Appointment
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static vw_Appointment Getvw_AppointmentList(string ID)
{
string strsql = "select top 1 * from vw_Appointment WITH(NOLOCK) where ID=@ID ";
var parameters = new DynamicParameters();
parameters.Add("@ID", ID);
return(MsSqlMapperHepler.SqlWithParamsSingle <vw_Appointment>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 判断是否已经有申请数据
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static int Refund(string StudentID, string EnrollID)
{
string strsql = "select count(ID) from Refund where StudentID=@StudentID and EnrollID=@EnrollID and StateID=1 ";
var parameters = new DynamicParameters();
parameters.Add("@EnrollID", EnrollID);
parameters.Add("@StudentID", StudentID);
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 新增判断手机号+姓名是唯一
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static string BindPhone_insert(string BindPhone, string name)
{
string strsql = "select id from Students WITH(NOLOCK) where BindPhone=@BindPhone and Name = @Name";
var parameters = new DynamicParameters();
parameters.Add("@BindPhone", BindPhone);
parameters.Add("@Name", name);
return(MsSqlMapperHepler.SqlWithParamsSingle <string>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取classlist对象
/// </summary>
/// <param name="classid"></param>
/// <param name="classindex"></param>
/// <returns></returns>
public static ClassList GetOneByid(string classid, int classindex)
{
string strsql = "select * from ClassList where ClassID =@ClassID and ClassIndex = @ClassIndex";
var parameters = new DynamicParameters();
parameters.Add("@ClassID", classid);
parameters.Add("@ClassIndex", classindex);
return(MsSqlMapperHepler.SqlWithParamsSingle <ClassList>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 修改判断手机号是唯一
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static int BindPhone_update(string id, string BindPhone)
{
string strsql = "select id from Students where id <> @id and BindPhone = @BindPhone";
var parameters = new DynamicParameters();
parameters.Add("@id", id);
parameters.Add("@BindPhone", BindPhone);
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 跟进学员ID和班级ID查看报名，防止重复报名
/// </summary>
/// <param name="studentID"></param>
/// <param name="classId"></param>
/// <returns></returns>
public static int getEnrollByStuidCalssid(string studentID, string classId)
{
String sql = "select count(*) from Enroll where StudentID = @StudentID and ClassID = @ClassID ";
var dynamic = new DynamicParameters();
dynamic.Add("@StudentID", studentID);
dynamic.Add("@ClassID", classId);
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(sql, dynamic, DBKeys.PRX));
}
public static Enroll getEnrollByStudentClass(string studentID, string classId, DBRepository db)
{
String sql = "select * from Enroll WITH(NOLOCK) where StudentID = @StudentID and ClassID = @ClassID ";
var dynamic = new DynamicParameters();
dynamic.Add("@StudentID", studentID);
dynamic.Add("@ClassID", classId);
return(MsSqlMapperHepler.SqlWithParamsSingle <Enroll>(sql, dynamic, DBKeys.PRX));
}
//<summary>
//获取ClassList行数
//</summary>
//<param name="Stockid"></param>
//<returns></returns>
public static int Getnumber(int AR_AccountId)
{
StringBuilder sb = new StringBuilder();
sb.Append("select count(AR_ID) from SYS_AccountRole ");
sb.Append(" where AR_AccountId=@AR_AccountId ");
var parameters = new DynamicParameters();
parameters.Add("@AR_AccountId", AR_AccountId);
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 停课一周
/// </summary>
/// <param name="classid"></param>
/// <param name="classindex"></param>
/// <returns></returns>
public static DateTime StopClass(string classid, int classindex)
{
string strsql1 = "select max(ClassDate) from [ClassList] where [ClassID] = '" + classid + "'";
DateTime lastClassTime = MsSqlMapperHepler.SqlWithParamsSingle <DateTime>(strsql1, null, DBKeys.PRX);//获取最后一次课的时间
ClassList cl = GetOneByid(classid, classindex);
cl.ClassDate = lastClassTime.AddDays(7);//延后一周
MsSqlMapperHepler.Update(cl, DBKeys.PRX);
return(cl.ClassDate);
}
//<summary>
//获取Teachers表的分校CODE
//</summary>
//<param name="Stockid"></param>
//<returns></returns>
public static string GetTeachers_ComCode(string MobilePhone)
{
StringBuilder sb = new StringBuilder();
sb.Append("select ComCode from Teachers ");
sb.Append(" where MobilePhone=@MobilePhone ");
var parameters = new DynamicParameters();
parameters.Add("@MobilePhone", MobilePhone);
return(MsSqlMapperHepler.SqlWithParamsSingle <string>(sb.ToString(), parameters, DBKeys.PRX));
}
//<summary>
//获取ClassList行数
//</summary>
//<param name="Stockid"></param>
//<returns></returns>
public static int Getnumber(string ClassID)
{
StringBuilder sb = new StringBuilder();
sb.Append("select count(ClassID) from ClassList WITH(NOLOCK) ");
sb.Append(" where ClassID=@ClassID ");
var parameters = new DynamicParameters();
parameters.Add("@ClassID", ClassID);
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(sb.ToString(), parameters, DBKeys.PRX));
}
//<summary>
//获取dictionaryItem行数
//</summary>
//<param name="Stockid"></param>
//<returns></returns>
public static int Getnumber(int DicTypeID)
{
StringBuilder sb = new StringBuilder();
sb.Append("select count(DicTypeID) from dictionaryItem ");
sb.Append(" where DicTypeID=@DicTypeID ");
var parameters = new DynamicParameters();
parameters.Add("@DicTypeID", DicTypeID);
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 根据学生ID，classID,classIndex 获取学生单条考勤记录
/// </summary>
/// <param name="studentID"></param>
/// <param name="classId"></param>
/// <param name="classIndex"></param>
/// <returns></returns>
public static AttendanceRecord GetAttendanceRecordByStudentClass(string studentID, string classId, int classIndex)
{
String sql = "select * from AttendanceRecord WITH(NOLOCK) where StudentID = @StudentID and ClassID = @ClassID and ClassIndex = @ClassIndex";
var dynamic = new DynamicParameters();
dynamic.Add("@StudentID", studentID);
dynamic.Add("@ClassID", classId);
dynamic.Add("@ClassIndex", classIndex);
return(MsSqlMapperHepler.SqlWithParamsSingle <AttendanceRecord>(sql, dynamic, DBKeys.PRX));
}
//<summary>
//判断ClassList表是否已经存在相对应的数据，有就不能再新增了
//</summary>
//<param name="Stockid"></param>
//<returns></returns>
public static int GetClassList_num(string ClassID, string TimePeriod, DateTime ClassDate)
{
StringBuilder sb = new StringBuilder();
sb.Append(" select COUNT(Classid) from ClassList ");
sb.Append(" where Classid=@Classid and TimePeriod=@TimePeriod ");
sb.Append(" and ClassDate = @ClassDate");
//sb.Append(" and weekday = @weekday");
var parameters = new DynamicParameters();
parameters.Add("@ClassID", ClassID);
parameters.Add("@TimePeriod", TimePeriod);
parameters.Add("@ClassDate", ClassDate);
// parameters.Add("@weekday", weekday);
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 根据登录ID获取教师信息
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public static Teachers GetTeachByID(string ID)
{
string strsql = "select * from Teachers where ID = '" + ID + "'";
return(MsSqlMapperHepler.SqlWithParamsSingle <Teachers>(strsql, null, DBKeys.PRX));
}
/// <summary>
/// 根据登录手机号码获取教师信息
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public static Teachers GetTeachByMobilePhone(String loginid)
{
string strsql = "select * from Teachers where MobilePhone = '" + loginid + "'";
return(MsSqlMapperHepler.SqlWithParamsSingle <Teachers>(strsql, null, DBKeys.PRX));
}
/// <summary>
/// 获取待审的记录条数
/// </summary>
/// <returns></returns>
public static int GetEnrollAuditListCount()
{
string strsql = "select count(*) from EnrollAudit where StateID = 2";
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(strsql, null, DBKeys.PRX));
}
public static Course getCourseById(int id)
{
return(MsSqlMapperHepler.SqlWithParamsSingle <Course>("select *,dbo.getDicNameByID(17,StateID) as StateName ,dbo.getDicNameByID(17,TypeID) as TypeName from Course where ID = " + id, null, DBKeys.PRX));
}
/// <summary>
/// 根据ACC_Account获取SYS_Account表信息
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public static SYSAccount GetSYS_AccountByID(string ACC_Account)
{
string strsql = "select * from SYS_Account where ACC_Account = '" + ACC_Account + "'";
return(MsSqlMapperHepler.SqlWithParamsSingle <SYSAccount>(strsql, null, DBKeys.PRX));
}