/// <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));
}
