public bool UpdateAsync(Course model)
{
using (var conn = DapperFactory.GetConnection())
{
List <string> removeFields = new List <string>
{
"Id",
"CreateTime",
"Status",
"IsSelected",
"IsRecommend",
"SystemName",
"SystemNameEn",
"MajorName",
"MajorNameEn"
};
var fields = model.ToFields(removeFields: removeFields);
if (fields == null || fields.Count == 0)
{
return(false);
}
var fieldList = new List <string>();
foreach (var field in fields)
{
fieldList.Add(string.Format("{0}=@{0}", field));
}
model.ModifyTime = DateTime.Now;
string sql = string.Format("update [Course] set {0} where Id=@Id;", string.Join(",", fieldList));
return(conn.Execute(sql, model) > 0);
}
}
public bool UpdateAsync(Works model)
{
using (var conn = DapperFactory.GetConnection()) {
List <string> removeFields = new List <string>
{
"Id",
"MajorName",
"CreateTime",
"Status"
};
if (String.IsNullOrEmpty(model.ImgUrl))
{
removeFields.Add("ImgUrl");
}
var fields = model.ToFields(removeFields: removeFields);
if (fields == null || fields.Count == 0)
{
return(false);
}
var fieldList = new List <string>();
foreach (var field in fields)
{
fieldList.Add(string.Format("{0}=@{0}", field));
}
model.ModifyTime = DateTime.Now;
string sql = string.Format("update [Works] set {0} where Id=@Id;", string.Join(",", fieldList));
return(conn.Execute(sql, model) > 0);
}
}
public PageModel <Log> GetListAsync(int pageIndex, int pageSize, int userId, string title)
{
using (var conn = DapperFactory.GetConnection())
{
#region generate condition
string where = "where 1=1";
if (!string.IsNullOrEmpty(title))
{
where += string.Format(" and Title like '%{0}%'", title);
}
if (userId > 0)
{
where += string.Format(" and UserId = {0}", userId);
}
#endregion
string countSql = string.Format("select count(1) from Log {0}", where);
int total = conn.ExecuteScalar <int>(countSql);
if (total == 0)
{
return(new PageModel <Log>());
}
string sql = string.Format(@"select * from ( select *, ROW_NUMBER() over (Order by Id desc) as RowNumber from Log {0} ) as b where RowNumber between {1} and {2};", where, ((pageIndex - 1) * pageSize) + 1, pageIndex * pageSize);
var list = conn.Query <Log>(sql);
return(new PageModel <Log>
{
Total = total,
Data = list != null?list.ToList() : null
});
}
}
public Works GetByIdAsync(int id)
{
using (var conn = DapperFactory.GetConnection()) {
string sql = "select * from [Works] where Id = @Id and Status=1;";
return(conn.QueryFirstOrDefault <Works>(sql, new { Id = id }));
}
}
public PageModel <Teacher> GetListAsync(int pageIndex, int pageSize, int division = -1, string name = null)
{
using (var conn = DapperFactory.GetConnection()) {
#region generate condition
string where = "where t.Status=1";
if (!string.IsNullOrEmpty(name))
{
where += string.Format(" and t.Name like '%{0}%'", name);
}
if (division != -1)
{
where += string.Format(" and t.DivisionId = {0}", division);
}
#endregion
string countSql = string.Format("select count(1) from [Teacher] as t {0};", where);
int total = conn.ExecuteScalar <int>(countSql);
if (total == 0)
{
return(new PageModel <Teacher>());
}
string sql = string.Format(@"select * from ( select t.*, d.Name as DivisionName, d.NameEn as DivisionNameEn,
d.BgColor as DivisionColor, ROW_NUMBER() over (Order by t.Id desc) as RowNumber from [Teacher] as t
left join [Division] d on d.Id = t.DivisionId {0} ) as b
where RowNumber between {1} and {2};", where, ((pageIndex - 1) * pageSize) + 1, pageIndex * pageSize);
var list = conn.Query <Teacher>(sql);
return(new PageModel <Teacher> {
Total = total,
Data = list != null?list.ToList() : null
});
}
}
public PageModel <Teacher> GetListByDivsionAsync(int divisionId, int pageIndex, int pageSize)
{
using (var conn = DapperFactory.GetConnection())
{
string countSql = string.Format("select count(1) from [Teacher] where DivisionId={0} and Status=1;", divisionId);
int total = conn.ExecuteScalar <int>(countSql);
if (total == 0)
{
return(new PageModel <Teacher>());
}
string sql = string.Format(@"select * from (select TOP (100) PERCENT t.Id,t.Name,t.NameEn,t.Avatar,s.Name as SchoolName,s.NameEn as SchoolNameEn,
m.Name as MajorName,m.NameEn as MajorNameEn,d.Name as DivisionName,d.NameEn as DivisionNameEn,d.BgColor as DivisionColor, ROW_NUMBER() over (Order by t.Id desc) as RowNumber from Teacher t
left join School s on t.SchoolId = s.Id
left join Major m on t.MajorId = m.Id
left join Division d on t.DivisionId = d.Id
where t.DivisionId = @DivisionId and t.Status = 1
order by t.Id desc) as b
where RowNumber between {0} and {1};", ((pageIndex - 1) * pageSize) + 1, pageIndex * pageSize);
var list = conn.Query <Teacher>(sql, new { DivisionId = divisionId });
PageModel <Teacher> result = new PageModel <Teacher>
{
Total = total,
Data = list != null?list.ToList() : null
};
return(result);
}
}
public bool UpdateAsync(AboutInstart model)
{
using (var conn = DapperFactory.GetConnection())
{
var fields = model.ToFields(removeFields: new List <string>
{
"PassLearning",
"CreateTime"
});
if (fields == null || fields.Count == 0)
{
return(false);
}
var fieldList = new List <string>();
foreach (var field in fields)
{
fieldList.Add(string.Format("{0}=@{0}", field));
}
model.ModifyTime = DateTime.Now;
string sql = string.Format("update [AboutInstart] set {0};", string.Join(",", fieldList));
return(conn.Execute(sql, model) > 0);
}
}
public PageModel <Major> GetListByDivsionAsync(int divisionId, int pageIndex, int pageSize)
{
using (var conn = DapperFactory.GetConnection())
{
string countSql = string.Format("select count(1) from [Major] as a where a.Status=1 and a.DivisionId={0};", divisionId);
int total = conn.ExecuteScalar <int>(countSql);
if (total == 0)
{
return(new PageModel <Major>());
}
string sql = string.Format(@"select * from (
select a.Id,a.Name,a.NameEn,a.Introduce,a.CreateTime,a.DivisionId,b.Name as DivisionName,b.NameEn as DivisionNameEn,a.ImgUrl, ROW_NUMBER() over (Order by a.Id desc) as RowNumber from [Major] as a
left join [Division] as b on b.Id = a.DivisionId where a.Status=1 and a.DivisionId={0}
) as c
where RowNumber between {1} and {2};", divisionId, ((pageIndex - 1) * pageSize) + 1, pageIndex * pageSize);
var list = conn.Query <Major>(sql);
return(new PageModel <Major>
{
Total = total,
Data = list != null?list.ToList() : null
});
}
}
public bool UpdateAsync(Student model)
{
using (var conn = DapperFactory.GetConnection()) {
var fields = model.ToFields(removeFields: new List <string>
{
"Id", "SchoolName", "SchoolNameEn", "MajorName", "MajorNameEn", "TeacherName", "TeacherNameEn",
"DivisionName", "DivisionNameEn", "IsRecommend", "SchoolLogo", "Status", "CreateTime", "CampusName", "CampusNameEn"
});
if (fields == null || fields.Count == 0)
{
return(false);
}
var fieldList = new List <string>();
foreach (var field in fields)
{
fieldList.Add(string.Format("{0}=@{0}", field));
}
model.ModifyTime = DateTime.Now;
string sql = string.Format("update [Student] set {0} where Id=@Id;", string.Join(",", fieldList));
return(conn.Execute(sql, model) > 0);
}
}
public bool DeleteAsync(int id)
{
using (var conn = DapperFactory.GetConnection()) {
string sql = "update [Works] set Status=0,ModifyTime=GETDATE() where Id=@Id;";
return(conn.Execute(sql, new { Id = id }) > 0);
}
}
public bool UpdateAsync(User model)
{
using (var conn = DapperFactory.GetConnection())
{
var fields = model.ToFields(removeFields: new List <string>
{
"Id",
"CreateTime",
"Status",
"Password"
});
if (fields == null || fields.Count == 0)
{
return(false);
}
var fieldList = new List <string>();
foreach (var field in fields)
{
fieldList.Add(string.Format("{0}=@{0}", field));
}
model.ModifyTime = DateTime.Now;
string sql = string.Format("update [User] set {0} where Id=@Id;", string.Join(",", fieldList));
return(conn.Execute(sql, model) > 0);
}
}
public PageModel <StarStudent> GetListAsync(int pageIndex, int pageSize, string name = null)
{
using (var conn = DapperFactory.GetConnection()) {
#region generate condition
string where = "where a.Status=1";
if (!string.IsNullOrEmpty(name))
{
where += string.Format(" and a.Name like '%{0}%'", name);
}
#endregion
string countSql = string.Format("select count(1) from [StarStudent] as a {0};", where);
int total = conn.ExecuteScalar <int>(countSql);
if (total == 0)
{
return(new PageModel <StarStudent>());
}
string sql = string.Format(@"select * from (
select a.*, ROW_NUMBER() over (Order by a.Id desc) as RowNumber from [StarStudent] as a {0}
) as c
where RowNumber between {1} and {2};", where, ((pageIndex - 1) * pageSize) + 1, pageIndex * pageSize);
var list = conn.Query <StarStudent>(sql);
return(new PageModel <StarStudent> {
Total = total,
Data = list != null?list.ToList() : null
});
}
}
public PageModel <Works> GetListAsync(int pageIndex, int pageSize, string name = null)
{
using (var conn = DapperFactory.GetConnection()) {
#region generate condition
string where = "where a.Status=1";
#endregion
string countSql = string.Format("select count(1) from [Works] as a {0};", where);
int total = conn.ExecuteScalar <int>(countSql);
if (total == 0)
{
return(new PageModel <Works>());
}
string sql = string.Format(@"select * from (select a.*, b.Name as MajorName, ROW_NUMBER() over (Order by a.Id desc) as RowNumber from [Works] as a
left join [Major] as b on b.Id = a.MajorId {0}) as c
where RowNumber between {1} and {2};", where, ((pageIndex - 1) * pageSize) + 1, pageIndex * pageSize);
var list = conn.Query <Works>(sql);
return(new PageModel <Works> {
Total = total,
Data = list != null?list.ToList() : null
});
}
}
public PageModel <HereMore> GetListAsync(int pageIndex, int pageSize, string name, EnumAccept accept)
{
using (var conn = DapperFactory.GetConnection())
{
#region generate condition
string where = "where 1=1 ";
if (!string.IsNullOrEmpty(name))
{
where += string.Format(" and h.Name like '%{0}%'", name);
}
if (accept != EnumAccept.All)
{
where += string.Format(" and h.IsAccept = {0}", (int)accept);
}
#endregion
string countSql = string.Format("select count(1) from [HereMore] as h {0};", where);
int total = conn.ExecuteScalar <int>(countSql);
if (total == 0)
{
return(new PageModel <HereMore>());
}
string sql = string.Format(@"select * from ( select h.*, m.Name as MajorName, m.NameEn as MajorNameEn, ROW_NUMBER() over (Order by h.Id desc) as RowNumber from [HereMore] as h
left join [Major] m on m.Id = h.MajorId {0} ) as b
where RowNumber between {1} and {2};", where, ((pageIndex - 1) * pageSize) + 1, pageIndex * pageSize);
var list = conn.Query <HereMore>(sql);
return(new PageModel <HereMore>
{
Total = total,
Data = list != null?list.ToList() : null
});
}
}
public User GetByNameAsync(string name)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = "select * from [User] where UserName = @name and Status=1;";
return(conn.QueryFirstOrDefault <User>(sql, new { name = name }));
}
}
public bool UpdatePasswordAsync(int id, string password)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = "update [User] set Password=@Password,ModifyTime=GETDATE() where Id=@Id;";
return(conn.Execute(sql, new { Id = id, Password = password }) > 0);
}
}
public IEnumerable <Course> GetAllByStudentAsync(int studentId)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = string.Format(@"select c.* from [StudentCourse] as s left join [Course] as c on s.CourseId = c.Id where c.Status=1 and s.StudentId = {0};", studentId);
return(conn.Query <Course>(sql));
}
}
public IEnumerable <Course> GetCoursesByIdAsync(int id)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = string.Format("select c.* from [TeacherCourse] as t left join [Course] as c on c.Id = t.CourseId where t.TeacherId={0};", id);
return(conn.Query <Course>(sql));;
}
}
public bool SetAcceptAsync(int id)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = "update [TeacherQuestion] set IsAccept=1,AcceptTime=GETDATE() where Id=@Id;";
return(conn.Execute(sql, new { Id = id }) > 0);
}
}
public bool DeleteImgAsync(int id)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = "delete from [StudioImg] where Id=@Id;";
return(conn.Execute(sql, new { Id = id }) > 0);
}
}
public bool InsertImgAsync(string imgUrl)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = string.Format("insert into [StudioImg] (Imgurl) values ('{0}');", imgUrl);
return(conn.Execute(sql) > 0);
}
}
public IEnumerable <StudioImg> GetImgsAsync()
{
using (var conn = DapperFactory.GetConnection())
{
string sql = string.Format(@"select * from [StudioImg];");
return(conn.Query <StudioImg>(sql));
}
}
public bool SetRecommend(int id, bool isRecommend)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = "update [Student] set IsRecommend=@IsRecommend where Id=@Id;";
return(conn.Execute(sql, new { IsRecommend = isRecommend, Id = id }) > 0);
}
}
public CourseInfo GetInfoAsync()
{
using (var conn = DapperFactory.GetConnection())
{
string sql = "select * from [CourseInfo];";
return(conn.QueryFirstOrDefault <CourseInfo>(sql));
}
}
public IEnumerable <int> GetCoursesByIdAsync(int id)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = string.Format("select CourseId from [StudentCourse] where StudentId={0};", id);
return(conn.Query <int>(sql));;
}
}
public IEnumerable <Course> GetListBySystemId(int systemId)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = string.Format(@"select a.*, s.Name as SystemName, s.NameEn as SystemNameEn from [Course] as a left join [CourseSystem] s on a.SystemId = s.Id where a.SystemId = {0} and a.Status=1;", systemId);
return(conn.Query <Course>(sql));
}
}
public bool SetShowAsync(int id, bool isShow)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = "update [Banner] set IsShow=@IsShow,ModifyTime=GETDATE() where Id=@Id;";
return(conn.Execute(sql, new { IsShow = isShow, Id = id }) > 0);
}
}
public IEnumerable <Program> GetListByTypeAsync(int type)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = string.Format("select * from [Program] where Status = 1 and Type={0};", type);
return(conn.Query <Program>(sql));;
}
}
public int GetInfoCountAsync()
{
using (var conn = DapperFactory.GetConnection())
{
string sql = "select count(1) from [CourseInfo];";
return(conn.ExecuteScalar <int>(sql));
}
}
public AboutInstart GetInfoAsync()
{
using (var conn = DapperFactory.GetConnection())
{
string sql = "select * from [AboutInstart];";
return(conn.QueryFirstOrDefault <AboutInstart>(sql));
}
}