/// <summary> /// Builds the SQL code corresponding to the specified filter and /// paging options for a list of acts. /// </summary> /// <param name="filter">The acts filter.</param> /// <returns> /// SQL code for both page and total. /// </returns> /// <exception cref="ArgumentNullException">filter</exception> public Tuple <string, string> BuildActSearch(ActFilter filter) { if (filter == null) { throw new ArgumentNullException(nameof(filter)); } StringBuilder sbPage = new StringBuilder(); StringBuilder sbTot = new StringBuilder(); // fields sbPage.AppendLine("SELECT DISTINCT") .Append(ETPS("act", "id", "bookId", "typeId", "subtypeId", "familyId", "companyId", "placeId", "label")) .AppendLine(",") .Append(ETPS("book", "location", "description", "startYear", "endYear", "file")) .AppendLine(",") .Append(ETP("archive", "id")).Append(" AS archiveId").AppendLine(",") .Append(ETP("archive", "name")).Append(" AS archiveName").AppendLine(",") .Append(ETP("actType", "name")).Append(" AS typeName").AppendLine(",") .Append(ETP("actSubtype", "name")).Append(" AS subtypeName").AppendLine(",") .Append(ETP("family", "name")).Append(" AS familyName").AppendLine(",") .Append(ETP("company", "name")).Append(" AS companyName").AppendLine(",") .Append(ETP("place", "name")).Append(" AS placeName").AppendLine(); sbTot.Append("SELECT COUNT(DISTINCT ") .Append(ETP("act", "id")) .AppendLine(")"); // from ... join ... sbPage.Append("FROM ").AppendLine(ET("act")); AppendJoins(sbPage); sbTot.Append("FROM ").AppendLine(ET("act")); AppendJoins(filter, sbTot); // where if (!filter.IsEmpty()) { string whereSql = BuildWhereSql(filter); sbPage.Append(whereSql); sbTot.Append(whereSql); } // order by (for page only) sbPage.Append("ORDER BY ") .Append(ETP("book", "startYear")) .Append(',').Append(ET("companyName")) .Append(',').Append(ET("familyName")) .Append(',').Append(ETP("act", "label")) .Append(',').AppendLine(ETP("act", "id")); // paging (for page only) AppendPaging(filter, sbPage); return(Tuple.Create(sbPage.ToString(), sbTot.ToString())); }
private void AppendJoins(ActFilter filter, StringBuilder sb) { if (filter.ArchiveId != 0) { sb.AppendLine("INNER JOIN book ON act.bookId=book.id") .AppendLine("INNER JOIN archive ON book.archiveId=archive.id"); } else if (filter.BookYearMin != 0 || filter.BookYearMax != 0) { sb.AppendLine("INNER JOIN book ON act.bookId=book.id"); } }
private string BuildWhereSql(ActFilter filter) { StringBuilder sb = new StringBuilder(); // archive if (filter.ArchiveId != 0) { sb.Append(ETP("archive", "id")).Append('=').Append(filter.ArchiveId); } // book if (filter.BookId != 0) { if (sb.Length > 0) { sb.Append(" AND "); } sb.Append(ETP("act", "bookId")).Append('=').Append(filter.BookId); } if (filter.BookYearMin != 0) { if (sb.Length > 0) { sb.Append(" AND "); } sb.Append(ETP("book", "startYear")).Append(">=").Append(filter.BookYearMin); } if (filter.BookYearMax != 0) { if (sb.Length > 0) { sb.Append(" AND "); } sb.Append(ETP("book", "endYear")).Append("<=").Append(filter.BookYearMax); } // act if (!string.IsNullOrEmpty(filter.Description)) { if (sb.Length > 0) { sb.Append(" AND "); } sb.Append(ETP("act", "descriptionx")) .Append(" LIKE '%") .Append(SqlHelper.SqlEncode(filter.Description)) .Append("%'"); } if (filter.ActTypeId != 0) { if (sb.Length > 0) { sb.Append(" AND "); } sb.Append(ETP("act", "typeId")).Append('=').Append(filter.ActTypeId); } if (filter.FamilyId != 0) { if (sb.Length > 0) { sb.Append(" AND "); } sb.Append(ETP("act", "familyId")).Append('=').Append(filter.FamilyId); } if (filter.CompanyId != 0) { if (sb.Length > 0) { sb.Append(" AND "); } sb.Append(ETP("act", "companyId")).Append('=').Append(filter.CompanyId); } if (filter.PlaceId != 0) { if (sb.Length > 0) { sb.Append(" AND "); } sb.Append(ETP("act", "placeId")).Append('=').Append(filter.PlaceId); } if (!string.IsNullOrEmpty(filter.Label)) { if (sb.Length > 0) { sb.Append(" AND "); } sb.Append(ETP("act", "labelx")) .Append(" LIKE '%") .Append(SqlHelper.SqlEncode(filter.Label)) .Append("%'"); } // categories if (filter.CategoryIds?.Count > 0) { if (sb.Length > 0) { sb.Append(" AND "); } AppendActInSql(filter.CategoryIds, "actCategory", "categoryId", sb); } // professions if (filter.ProfessionIds?.Count > 0) { if (sb.Length > 0) { sb.Append(" AND "); } AppendActInSql(filter.ProfessionIds, "actProfession", "professionId", sb); } // partners if (filter.PartnerIds?.Count > 0) { if (sb.Length > 0) { sb.Append(" AND "); } AppendActInSql(filter.PartnerIds, "actPartner", "partnerId", sb); } sb.Insert(0, "WHERE" + Environment.NewLine); sb.AppendLine(); return(sb.ToString()); }
public DataPage <ActInfo> GetActs(ActFilter filter) { if (filter == null) { throw new ArgumentNullException(nameof(filter)); } EnsureConnected(); var t = _queryBuilder.BuildActSearch(filter); List <ActInfo> acts = new List <ActInfo>(); int total; using (IDbCommand cmdPage = _connection.CreateCommand()) using (IDbCommand cmdTot = _connection.CreateCommand()) { // total cmdTot.CommandText = t.Item2; object result = cmdTot.ExecuteScalar(); total = result != DBNull.Value && result != null? Convert.ToInt32(result) : 0; // page if (total > 0) { cmdPage.CommandText = t.Item1; using var reader = cmdPage.ExecuteReader(); while (reader.Read()) { acts.Add(new ActInfo { Id = GetReaderInt(reader, "id"), TypeId = GetReaderInt(reader, "typeId"), TypeName = GetReaderString(reader, "typeName"), SubtypeId = GetReaderInt(reader, "subtypeId"), SubtypeName = GetReaderString(reader, "subtypeName"), FamilyId = GetReaderInt(reader, "familyId"), FamilyName = GetReaderString(reader, "familyName"), CompanyId = GetReaderInt(reader, "companyId"), CompanyName = GetReaderString(reader, "companyName"), PlaceId = GetReaderInt(reader, "placeId"), PlaceName = GetReaderString(reader, "placeName"), Label = GetReaderString(reader, "label"), ArchiveId = GetReaderInt(reader, "archiveId"), ArchiveName = GetReaderString(reader, "archiveName"), BookId = GetReaderInt(reader, "bookId"), BookLocation = GetReaderString(reader, "location"), BookDescription = GetReaderString(reader, "description"), BookStartYear = GetReaderShort(reader, "startYear"), BookEndYear = GetReaderShort(reader, "endYear"), BookFile = GetReaderString(reader, "file") }); } } } _connection.Close(); return(new DataPage <ActInfo>( filter.PageNumber, filter.PageSize, total, acts)); }