public IList <LectureTypeItem> FindAll()
{
StringBuilder builder = new StringBuilder();
builder.Append("select * from LectureType");
return(AdoTemplate.QueryWithRowMapper <LectureTypeItem>(CommandType.Text, builder.ToString(), new LectureTypeMapper()));
}
public IList <LectureCategoryItem> FindLectureTypeExist()
{
StringBuilder builder = new StringBuilder();
builder.Append("select * from LectureCategory where id in (select distinct lectureCategoryId from LectureType)");
return(AdoTemplate.QueryWithRowMapper <LectureCategoryItem>(CommandType.Text, builder.ToString(), new LectureCategoryMapper()));
}
/// <summary>
/// 查询分页数据
/// </summary>
/// <typeparam name="TResult">分页数据类型</typeparam>
/// <typeparam name="TFilter">过滤器类型</typeparam>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页尺寸</param>
/// <param name="querySql">查询Sql语句</param>
/// <param name="countSql">统计Sql语句</param>
/// <param name="orderBy">排序字段</param>
/// <param name="filter">过滤器</param>
/// <returns></returns>
List <TResult> GetPagedData <TResult, TFilter>(int pageIndex, int pageSize, out int totalCount, string countSql, string querySql, IQueryFilter filter)
where TResult : class, new()
where TFilter : IQueryFilter, new()
{
List <TResult> list = new List <TResult>();
IDbParameters pms;
string where = (filter ?? new TFilter()).ToSqlWhere(out pms);
querySql += where;
countSql += where;
pageSize = pageSize < 1 ? 20 : pageSize;
totalCount = GetDataCount(countSql, pms);
int totalPages = (int)Math.Ceiling(totalCount * 1.0 / pageSize);
totalPages = totalPages < 1 ? 1 : totalPages;
pageIndex = pageIndex < 1 ? 1 : (pageIndex > totalPages ? totalPages : pageIndex);
string sql = string.Format("select * from ({0}) as sr where sr.\"rownum\" BETWEEN {1} and {2}", querySql, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
list = AdoTemplate.QueryWithRowMapper <TResult>(CommandType.Text, sql, new BaseRowMapper <TResult>(), pms).ToList();
return(list);
}
public List <MatingCount> GetMatingCount(string sheepId, int?count, DateTime?startDate, DateTime?endDate, int pageIndex, int pageSize, out int totalCount)
{
List <MatingCount> list = new List <MatingCount>();
StringBuilder sb = new StringBuilder();
StringBuilder sbCount = new StringBuilder();
IDbParameters pms = AdoTemplate.CreateDbParameters();
sb.Append("SELECT \"row_number\" () OVER (ORDER BY \"SerialNumber\") \"rownum\",\"SerialNumber\",\"BreedName\",\"SheepfoldName\",\"Status\",COUNT (\"MaleId\") AS \"Count\" FROM(SELECT s.\"SerialNumber\",M .\"MaleId\",sf.\"Name\" AS \"SheepfoldName\",b.\"Name\" AS \"BreedName\",s.\"Status\"FROM \"T_Sheep\" s JOIN \"T_Mating\" M ON M .\"FemaleId\" = s.\"Id\" JOIN \"T_Sheepfold\" sf ON sf.\"Id\" = s.\"SheepfoldId\" JOIN \"T_Breed\" b ON b.\"Id\" = s.\"BreedId\" WHERE s.\"Gender\" =:gender");
sbCount.Append("SELECT \"count\" (\"SerialNumber\") FROM ( SELECT \"SerialNumber\" FROM ( SELECT s.\"SerialNumber\" FROM \"T_Sheep\" s JOIN \"T_Mating\" M ON M .\"FemaleId\" = s.\"Id\" JOIN \"T_Sheepfold\" sf ON sf.\"Id\" = s.\"SheepfoldId\" JOIN \"T_Breed\" b ON b.\"Id\" = s.\"BreedId\" WHERE s.\"Gender\" =:gender and M .\"IsDel\" = FALSE");
pms.AddWithValue("gender", (int)GenderEnum.Female);
if (startDate != null || endDate != null)
{
sb.Append(" and M .\"IsDel\" = FALSE and m.\"MatingDate\">=:startDate and m.\"MatingDate\"<=:endDate ");
sbCount.Append(" and m.\"MatingDate\">=:startDate and m.\"MatingDate\"<=:endDate ");
pms.AddWithValue("startDate", startDate);
pms.AddWithValue("endDate", endDate);
}
if (!string.IsNullOrEmpty(sheepId))
{
sb.Append(" and s.\"Id\"=:sheepId ");
sbCount.Append(" and s.\"Id\"=:sheepId ");
pms.AddWithValue("sheepId", sheepId);
}
sb.Append(")T GROUP BY \"SerialNumber\",\"BreedName\",\"SheepfoldName\",\"Status\" ");
sbCount.Append(" )T GROUP BY \"SerialNumber\"");
if (count != null)
{
sb.Append(" HAVING COUNT (\"MaleId\") =:cnt ");
sbCount.Append(" HAVING COUNT (\"SerialNumber\") =:cnt ");
pms.AddWithValue("cnt", count);
}
sb.Append("ORDER BY \"Count\" DESC ");
sbCount.Append(" ) C");
pageSize = pageSize < 1 ? 20 : pageSize;
totalCount = GetDataCount(sbCount.ToString(), pms);
int totalPages = (int)Math.Ceiling(totalCount * 1.0 / pageSize);
totalPages = totalPages < 1 ? 1 : totalPages;
pageIndex = pageIndex < 1 ? 1 : (pageIndex > totalPages ? totalPages : pageIndex);
string sql = string.Format("select * from ({0}) as sr where sr.\"rownum\" BETWEEN {1} and {2}", sb.ToString(), (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
list = AdoTemplate.QueryWithRowMapper <MatingCount>(CommandType.Text, sql, new BaseRowMapper <MatingCount>(), pms).ToList();
return(list);
//string sql = "SELECT \"row_number\" () OVER (ORDER BY \"SerialNumber\") \"rownum\",\"SerialNumber\", \"BreedName\", \"SheepfoldName\", \"Status\", COUNT (\"MaleId\") AS \"Count\" FROM ( SELECT s.\"SerialNumber\", M .\"MaleId\", sf.\"Name\" AS \"SheepfoldName\", b.\"Name\" AS \"BreedName\", s.\"Status\" FROM \"T_Sheep\" s LEFT JOIN \"T_Mating\" M ON M .\"FemaleId\" = s.\"Id\" AND M .\"IsDel\" = FALSE JOIN \"T_Sheepfold\" sf ON sf.\"Id\" = s.\"SheepfoldId\" JOIN \"T_Breed\" b ON b.\"Id\" = s.\"BreedId\" WHERE s.\"Gender\" = 1 ) T GROUP BY \"SerialNumber\", \"BreedName\", \"SheepfoldName\", \"Status\" ORDER BY \"Count\" DESC";
//string countSql = "SELECT \"count\" (\"SerialNumber\") FROM ( SELECT \"SerialNumber\" FROM ( SELECT s.\"SerialNumber\" FROM \"T_Sheep\" s LEFT JOIN \"T_Mating\" M ON M .\"FemaleId\" = s.\"Id\" AND M .\"IsDel\" = FALSE JOIN \"T_Sheepfold\" sf ON sf.\"Id\" = s.\"SheepfoldId\" JOIN \"T_Breed\" b ON b.\"Id\" = s.\"BreedId\" WHERE s.\"Gender\" = 1 ) T GROUP BY \"SerialNumber\" ) C";
//return GetPagedData<MatingCount, MatingCountFilter>(pageIndex, pageSize, out totalCount, countSql, sql, null);
}
public List <AssistMating> GetAssistMating(string sheepId, int depth)
{
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("sheepId", sheepId);
pms.AddWithValue("generation", depth);
return(AdoTemplate.QueryWithRowMapper <AssistMating>(CommandType.StoredProcedure, "AssistMating", new BaseRowMapper <AssistMating>(), pms).ToList());
}
//List<TResult> GetData<TResult, TFilter, TRowMapper>(string sql, IQueryFilter filter)
// where TResult : class,new()
// where TFilter : IQueryFilter, new()
// where TRowMapper : IRowMapper<TResult>, new()
//{
// List<TResult> list = new List<TResult>();
// IDbParameters pms;
// sql += (filter ?? new TFilter()).ToSqlWhere(out pms);
// list = AdoTemplate.QueryWithRowMapper<TResult>(CommandType.Text, sql, new TRowMapper(), pms).ToList();
// return list;
//}
/// <summary>
/// 绑定查询
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="sql"></param>
/// <param name="pms"></param>
/// <returns></returns>
List <TResult> GetData <TResult>(string sql, IDbParameters pms)
where TResult : class, new()
{
List <TResult> list = new List <TResult>();
list = AdoTemplate.QueryWithRowMapper <TResult>(CommandType.Text, sql, new BaseRowMapper <TResult>(), pms).ToList();
return(list);
}
public IList <LectureDetailItem> FindAll()
{
StringBuilder builder = new StringBuilder();
builder.Append("select a.*, isnull(b.name ,N'구분없음') lectureTypeName ,c.name lectureCategoryName, isnull(d.name,N'구분없음') sectionName from LectureDetail a left outer join LectureType b on a.lectureTypeId=b.id ");
builder.Append("inner join LectureCategory c on a.lectureCategoryId = c.id ");
builder.Append("left outer join Section d on a.sectionId = d.id order by lectureCategoryId, lectureTypeId, sectionId");
return(AdoTemplate.QueryWithRowMapper <LectureDetailItem>(CommandType.Text, builder.ToString(), new LectureDetailMapper()));
}
public IList <LectureTypeItem> FindByCategoryId(int id)
{
StringBuilder builder = new StringBuilder();
builder.Append("select * from LectureType where lectureCategoryId = @Id");
IDbParameters param = CreateDbParameters();
param.AddWithValue("Id", id).DbType = DbType.Int32;
return(AdoTemplate.QueryWithRowMapper <LectureTypeItem>(CommandType.Text, builder.ToString(), new LectureTypeMapper(), param));
}
public IList <SectionRequirementItem> FindByLectureTypeId(int lectureTypeId)
{
StringBuilder builder = new StringBuilder();
builder.Append("select *from SectionRequirement where lectureTypeId = @Id");
IDbParameters param = CreateDbParameters();
param.AddWithValue("Id", lectureTypeId).DbType = DbType.Int32;
return(AdoTemplate.QueryWithRowMapper <SectionRequirementItem>(CommandType.Text, builder.ToString(), new SectionRequirementMapper(), param));
}
public IList <LectureTypeRequirementItem> FindByAccountId(int accountId)
{
StringBuilder builder = new StringBuilder();
builder.Append("select a.*, b.name lectureTypeName ,c.name lectureCategoryName, c.id lectureCategoryId from lectureTypeRequirement a inner join lectureType b on a.lectureTypeId=b.id ");
builder.Append("inner join lectureCategory c on b.lectureCategoryId = c.id where accountId=@AccountId");
IDbParameters param = CreateDbParameters();
param.AddWithValue("AccountId", accountId).DbType = DbType.Int32;
return(AdoTemplate.QueryWithRowMapper <LectureTypeRequirementItem>(CommandType.Text, builder.ToString(), new LectureTypeRequirementMapper(), param));
}
public IList <LectureDetailItem> FindByAccountId(int accountId)
{
StringBuilder builder = new StringBuilder();
builder.Append("select a.*, isnull(b.name ,N'구분없음') lectureTypeName ,c.name lectureCategoryName, isnull(d.name ,N'구분없음') sectionName from LectureDetail a left outer join LectureType b on a.lectureTypeId=b.id ");
builder.Append("inner join LectureCategory c on a.lectureCategoryId = c.id ");
builder.Append("left outer join Section d on a.sectionId = d.id where accountId=@AccountId order by lectureCategoryId, lectureTypeId, sectionId");
IDbParameters param = CreateDbParameters();
param.AddWithValue("AccountId", accountId).DbType = DbType.Int32;
return(AdoTemplate.QueryWithRowMapper <LectureDetailItem>(CommandType.Text, builder.ToString(), new LectureDetailMapper(), param));
}
/// <summary>
/// sql语句中包含聚合语句，where不能放在最后
/// 仅支持一个参数的情况下
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <typeparam name="TFilter"></typeparam>
/// <param name="sql"></param>
/// <param name="filter"></param>
/// <returns></returns>
List <TResult> GetDataWithFormate <TResult, TFilter>(string sql, IQueryFilter filter)
where TResult : class, new()
where TFilter : IQueryFilter, new()
{
List <TResult> list = new List <TResult>();
IDbParameters pms;
sql = string.Format(sql, (filter ?? new TFilter()).ToSqlWhere(out pms));
list = AdoTemplate.QueryWithRowMapper <TResult>(CommandType.Text, sql, new BaseRowMapper <TResult>(), pms).ToList();
return(list);
}
public List <FamilyTree> GetFamilyTree(string sheepId, int depth)
{
//string sql = "select * FROM getfamilytree(:sheepId,:depth);";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("sheepId", sheepId);
//pms.AddWithValue("depth", depth);
// return GetData<FamilyTree>(sql, pms);
//存储过程调用方式
pms.AddWithValue("len", depth);
return(AdoTemplate.QueryWithRowMapper <FamilyTree>(CommandType.StoredProcedure, "getfamilytree", new BaseRowMapper <FamilyTree>(), pms).ToList());
}
public AccountItem FindByEmail(string email)
{
string query = "select * from account where email=@Email";
IDbParameters param = CreateDbParameters();
param.AddWithValue("Email", email).DbType = DbType.String;
IList <AccountItem> list = AdoTemplate.QueryWithRowMapper <AccountItem>(CommandType.Text, query, new AccountMapper(), param);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
public AirportCollection GetAllAirports()
{
AirportCollection results = new AirportCollection();
IList list = AdoTemplate.QueryWithRowMapper(CommandType.Text,
"SELECT * FROM airport", airportMapper);
//TODO - add support to queries for supplying own collection implementation?
// Why not using IResultSetExtrator ?
foreach (Airport airport in list)
{
results.Add(airport);
}
return(results);
}
public LectureCategoryRequirementItem FindById(int id)
{
string query = " select a.*,b.name lectureCategoryName from lectureCategoryRequirement a inner join lectureCategory b on a.lectureCategoryId=b.id where a.id = @Id";
IDbParameters param = CreateDbParameters();
param.AddWithValue("Id", id);
IList <LectureCategoryRequirementItem> list = AdoTemplate.QueryWithRowMapper <LectureCategoryRequirementItem>(CommandType.Text, query, new LectureCategoryRequirementMapper(), param);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
public TestObject FindByName(string name)
{
IList toList = AdoTemplate.QueryWithRowMapper(CommandType.Text,
"select TestObjectNo, Age, Name from TestObjects where Name='" + name + "'",
new TestObjectRowMapper());
if (toList.Count > 0)
{
return((TestObject)toList[0]);
}
else
{
return(null);
}
}
public LectureTypeRequirementItem FindByAccountAndTypeId(int accountId, int type)
{
string query = "select a.*, b.name lectureTypeName ,c.name lectureCategoryName, c.id lectureCategoryId from lectureTypeRequirement a inner join lectureType b on a.lectureTypeId=b.id inner join lectureCategory c on b.lectureCategoryId = c.id where a.accountId = @AccountId and a.lectureTypeId = @Type";
IDbParameters param = CreateDbParameters();
param.AddWithValue("AccountId", accountId);
param.AddWithValue("Type", type);
IList <LectureTypeRequirementItem> list = AdoTemplate.QueryWithRowMapper <LectureTypeRequirementItem>(CommandType.Text, query, new LectureTypeRequirementMapper(), param);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
public LectureTypeRequirementItem FindById(int id)
{
StringBuilder builder = new StringBuilder();
builder.Append("select a.*, b.name lectureTypeName ,c.name lectureCategoryName, c.id lectureCategoryId from lectureTypeRequirement a inner join lectureType b on a.lectureTypeId=b.id inner join lectureCategory c on b.lectureCategoryId = c.id where a.id = @Id");
IDbParameters param = CreateDbParameters();
param.AddWithValue("Id", id).DbType = DbType.Int32;
IList <LectureTypeRequirementItem> list = AdoTemplate.QueryWithRowMapper <LectureTypeRequirementItem>(CommandType.Text, builder.ToString(), new LectureTypeRequirementMapper(), param);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
public SectionRequirementItem FindById(int id)
{
StringBuilder builder = new StringBuilder();
builder.Append("select *from SectionRequirement where id = @Id");
IDbParameters param = CreateDbParameters();
param.AddWithValue("Id", id).DbType = DbType.Int32;
IList <SectionRequirementItem> list = AdoTemplate.QueryWithRowMapper <SectionRequirementItem>(CommandType.Text, builder.ToString(), new SectionRequirementMapper(), param);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
/// <summary>
/// sql语句中包含聚合语句，where不能放在最后
/// 仅支持一个参数的情况下
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <typeparam name="TFilter"></typeparam>
/// <param name="rowsCount"></param>
/// <param name="querySql"></param>
/// <param name="filter"></param>
/// <returns></returns>
List <TResult> GetRuledRowsDataWithFormate <TResult, TFilter>(int rowsCount, string querySql, IQueryFilter filter)
where TResult : class, new()
where TFilter : IQueryFilter, new()
{
List <TResult> list = new List <TResult>();
IDbParameters pms;
querySql = string.Format(querySql, (filter ?? new TFilter()).ToSqlWhere(out pms));
string sql = string.Format("select * from ({0}) as sr where sr.\"rownum\"<={1}", querySql, rowsCount);
list = AdoTemplate.QueryWithRowMapper <TResult>(CommandType.Text, sql, new BaseRowMapper <TResult>(), pms).ToList();
return(list);
}
public LectureDetailItem FindByAccountAndTitle(int accountId, string title)
{
StringBuilder builder = new StringBuilder();
builder.Append("select a.*, isnull(b.name ,N'구분없음') lectureTypeName ,c.name lectureCategoryName, isnull(d.name,N'구분없음') sectionName from LectureDetail a left outer join LectureType b on a.lectureTypeId=b.id ");
builder.Append("inner join LectureCategory c on a.lectureCategoryId = c.id ");
builder.Append("left outer join Section d on a.sectionId = d.id where a.accountId = @AccountId and a.Name = @Name order by lectureCategoryId, lectureTypeId, sectionId");
IDbParameters param = CreateDbParameters();
param.AddWithValue("AccountId", accountId).DbType = DbType.Int32;
param.AddWithValue("Name", title);
IList <LectureDetailItem> list = AdoTemplate.QueryWithRowMapper <LectureDetailItem>(CommandType.Text, builder.ToString(), new LectureDetailMapper(), param);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
public List <MatingCount> GetMatingCount(string sheepId, int?count, DateTime?startDate, DateTime?endDate, int rowsCount)
{
//string sql = "SELECT \"row_number\" () OVER (ORDER BY \"SerialNumber\") \"rownum\",\"SerialNumber\", \"BreedName\", \"SheepfoldName\", \"Status\", COUNT (\"MaleId\") AS \"Count\" FROM ( SELECT s.\"SerialNumber\", M .\"MaleId\", sf.\"Name\" AS \"SheepfoldName\", b.\"Name\" AS \"BreedName\", s.\"Status\" FROM \"T_Sheep\" s LEFT JOIN \"T_Mating\" M ON M .\"FemaleId\" = s.\"Id\" AND M .\"IsDel\" = FALSE JOIN \"T_Sheepfold\" sf ON sf.\"Id\" = s.\"SheepfoldId\" JOIN \"T_Breed\" b ON b.\"Id\" = s.\"BreedId\" WHERE s.\"Gender\" = 1 ) T GROUP BY \"SerialNumber\", \"BreedName\", \"SheepfoldName\", \"Status\" ORDER BY \"Count\" DESC";
//return GetRuledRowsData<MatingCount, MatingCountFilter>(rowsCount, sql, filter);
List <MatingCount> list = new List <MatingCount>();
StringBuilder sb = new StringBuilder();
IDbParameters pms = AdoTemplate.CreateDbParameters();
sb.Append("SELECT \"row_number\" () OVER (ORDER BY \"SerialNumber\") \"rownum\",\"SerialNumber\",\"BreedName\",\"SheepfoldName\",\"Status\",COUNT (\"MaleId\") AS \"Count\" FROM(SELECT s.\"SerialNumber\",M .\"MaleId\",sf.\"Name\" AS \"SheepfoldName\",b.\"Name\" AS \"BreedName\",s.\"Status\"FROM \"T_Sheep\" s JOIN \"T_Mating\" M ON M .\"FemaleId\" = s.\"Id\" JOIN \"T_Sheepfold\" sf ON sf.\"Id\" = s.\"SheepfoldId\" JOIN \"T_Breed\" b ON b.\"Id\" = s.\"BreedId\" WHERE s.\"Gender\" =:gender");
pms.AddWithValue("gender", (int)GenderEnum.Female);
if (startDate != null || endDate != null)
{
sb.Append(" and M .\"IsDel\" = FALSE and m.\"MatingDate\">=:startDate and m.\"MatingDate\"<=:endDate ");
pms.AddWithValue("startDate", startDate);
pms.AddWithValue("endDate", endDate);
}
if (!string.IsNullOrEmpty(sheepId))
{
sb.Append(" and s.\"Id\"=:sheepId ");
pms.AddWithValue("sheepId", sheepId);
}
sb.Append(")T GROUP BY \"SerialNumber\",\"BreedName\",\"SheepfoldName\",\"Status\" ");
if (count != null)
{
sb.Append("HAVING COUNT (\"MaleId\") =:cnt ");
pms.AddWithValue("cnt", count);
}
sb.Append("ORDER BY \"Count\" DESC ");
string sql = string.Format("select * from ({0}) as sr where sr.\"rownum\"<={1}", sb.ToString(), rowsCount);
list = AdoTemplate.QueryWithRowMapper <MatingCount>(CommandType.Text, sql, new BaseRowMapper <MatingCount>(), pms).ToList();
return(list);
}
public IList getAll()
{
return(AdoTemplate.QueryWithRowMapper(CommandType.Text, @"SELECT * FROM tb_user", new Tb_userRowMapper()));
}
public IList GetAllAircraft()
{
return(AdoTemplate.QueryWithRowMapper(CommandType.Text, AllAircraft, aircraftMapper));
}
/// <summary>
/// Gets the customers.
/// </summary>
/// <returns>A list of customers</returns>
public virtual IList GetCustomers()
{
return(AdoTemplate.QueryWithRowMapper(CommandType.Text, cmdText,
new CustomerRowMapper()));
}
public IList FindAll()
{
return(AdoTemplate.QueryWithRowMapper(CommandType.Text,
"select TestObjectNo, Age, Name from TestObjects",
new TestObjectRowMapper()));
}
public IEnumerable <Trade> GetAllTrades()
{
const string selectAllTrades = "select ConfirmationNumber, Symbol, Quantity, ExecutionPrice, BuyRequest, OrderType, Error, ErrorMessage from Trade";
return(_adoTemplate.QueryWithRowMapper(CommandType.Text, selectAllTrades, _tradeRowMapper));
}
public IList <T> ExecuteQueryWithRowMapper(string command, IDbParameters parameters)
{
return(AdoTemplate.QueryWithRowMapper(
CommandType.Text, command, GetRowMapper(), parameters).ToList <T>());
}