public bool UpdateAsync(Major model)
{
using (var conn = DapperFactory.GetConnection())
{
List <string> removeFields = new List <string>
{
"Id",
"DivisionName",
"CreateTime",
"Status",
"DivisionNameEn",
"DivisionColor",
"IsSelected",
"SchoolInfo"
};
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 [Major] set {0} where Id=@Id;", string.Join(",", fieldList));
return(conn.Execute(sql, model) > 0);
}
}
public List <Student> GetListByCourseAsync(int courseId = -1)
{
using (var conn = DapperFactory.GetConnection())
{
string sql = string.Format(@"select t.*, b.Name as MajorName, b.NameEn as MajorNameEn, c.Name as TeacherName,
c.NameEn as TeacherNameEn, d.Name as DivisionName, d.NameEn as DivisionNameEn, d.BgColor as DivisionColor,
f.Name as CampusName, f.NameEn as CampusNameEn, k.SchoolIds, k.SchoolNames from StudentCourse s
left join [STUDENT] as t on s.StudentId = t.Id
left join [Major] as b on b.Id = t.MajorId
left join [Teacher] as c on c.Id = t.TeacherId
left join [Division] as d on d.Id = t.DivisionId
left join [Campus] as f on f.Id = t.CampusId
left join (SELECT StudentId,(SELECT CONVERT(varchar(8), r.Id) +',' FROM [StudentSchool] ss LEFT JOIN [School] r ON r.Id = ss.SchoolId
WHERE StudentId=A.StudentId FOR XML PATH('')) AS SchoolIds,
(SELECT r.Name +',' FROM [StudentSchool] ss LEFT JOIN [School] r ON r.Id = ss.SchoolId
WHERE StudentId=A.StudentId FOR XML PATH('')) AS SchoolNames FROM [StudentSchool] A GROUP BY StudentId
) k ON k.StudentId = t.Id
where s.CourseId = {0} and t.Status=1
order by t.Id;", courseId);
var list = conn.Query <Student>(sql, null);
return(list != null?list.ToList() : null);
}
}
public PageModel <Banner> GetListAsync(int pageIndex, int pageSize, string title = null, int pos = 1, int type = -1)
{
using (var conn = DapperFactory.GetConnection())
{
#region generate condition
string where = "where Status=1";
if (!string.IsNullOrEmpty(title))
{
where += string.Format(" and Title like '%{0}%'", title);
}
if (pos > -1)
{
where += string.Format(" and Pos = {0}", pos);
}
if (type > -1)
{
where += string.Format(" and [Type]={0}", type);
}
#endregion
string countSql = string.Format("select count(1) from [Banner] {0};", where);
int total = conn.ExecuteScalar <int>(countSql);
if (total == 0)
{
return(new PageModel <Banner>());
}
string sql = string.Format(@"select * from ( select *, ROW_NUMBER() over (Order by Id desc) as RowNumber from [Banner] {0} ) as b where RowNumber between {1} and {2};", where, ((pageIndex - 1) * pageSize) + 1, pageIndex * pageSize);
var list = conn.Query <Banner>(sql);
return(new PageModel <Banner>
{
Total = total,
Data = list != null?list.ToList() : null
});
}
}
public PageModel <Major> GetListAsync(int pageIndex, int pageSize, int division = -1, 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);
}
if (division != -1)
{
where += string.Format(" and a.DivisionId = {0}", division);
}
#endregion
string countSql = string.Format("select count(1) from [Major] as a {0};", where);
int total = conn.ExecuteScalar <int>(countSql);
if (total == 0)
{
return(new PageModel <Major>());
}
string sql = string.Format(@"select * from (
select a.*, b.Name as DivisionName,b.NameEn as DivisionNameEn, ROW_NUMBER() over (Order by a.Id desc) as RowNumber from [Major] as a
left join [Division] as b on b.Id = a.DivisionId {0}
) as c
where RowNumber between {1} and {2};", where, ((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 PageModel <SchoolApply> GetListAsync(int pageIndex, int pageSize, string schoolName, EnumAccept accept)
{
using (var conn = DapperFactory.GetConnection())
{
#region generate condition
string where = "where 1=1 ";
if (!string.IsNullOrEmpty(schoolName))
{
where += string.Format(" and s.Name like '%{0}%'", schoolName);
}
if (accept != EnumAccept.All)
{
where += string.Format(" and a.IsAccept = {0}", (int)accept);
}
#endregion
string countSql = string.Format("select count(1) from [SchoolApply] as a left join [School] as s on s.Id = a.SchoolId {0};", where);
int total = conn.ExecuteScalar <int>(countSql);
if (total == 0)
{
return(new PageModel <SchoolApply>());
}
string sql = string.Format(@"select * from ( select a.*, m.Name as MajorName, m.NameEn as MajorNameEn,
s.Name as SchoolName, s.NameEn as SchoolNameEn, ROW_NUMBER() over (Order by a.Id desc) as RowNumber from [SchoolApply] as a
left join [School] as s on s.Id = a.SchoolId
left join [Major] m on m.Id = a.MajorId
{0} ) as b where RowNumber between {1} and {2};", where, ((pageIndex - 1) * pageSize) + 1, pageIndex * pageSize);
var list = conn.Query <SchoolApply>(sql);
return(new PageModel <SchoolApply>
{
Total = total,
Data = list != null?list.ToList() : null
});
}
}
public IEnumerable <Student> GetAllAsync()
{
using (var conn = DapperFactory.GetConnection())
{
#region generate condition
string where = "where t.Status=1";
#endregion
string sql = string.Format(@"select t.*, b.Name as MajorName, b.NameEn as MajorNameEn, c.Name as TeacherName,
c.NameEn as TeacherNameEn, d.Name as DivisionName, d.NameEn as DivisionNameEn, d.BgColor as DivisionColor,
f.Name as CampusName, f.NameEn as CampusNameEn, s.SchoolIds, s.SchoolNames from Student t
left join [Major] as b on b.Id = t.MajorId
left join [Teacher] as c on c.Id = t.TeacherId
left join [Division] as d on d.Id = t.DivisionId
left join [Campus] as f on f.Id = t.CampusId
left join (SELECT StudentId,(SELECT CONVERT(varchar(8), r.Id) +',' FROM [StudentSchool] ss LEFT JOIN [School] r ON r.Id = ss.SchoolId
WHERE StudentId=A.StudentId FOR XML PATH('')) AS SchoolIds,
(SELECT r.Name +',' FROM [StudentSchool] ss LEFT JOIN [School] r ON r.Id = ss.SchoolId
WHERE StudentId=A.StudentId FOR XML PATH('')) AS SchoolNames FROM [StudentSchool] A GROUP BY StudentId
) s ON s.StudentId = t.Id
{0} order by t.Id;", where);
return(conn.Query <Student>(sql));
}
}
