/// <summary>
/// Get a list of persons
/// </summary>
/// <param name="mot">The MovieObjectType.</param>
/// <param name="name">The name.</param>
/// <returns></returns>
public List<Person> GetPersonList(MovieObjectType mot, string name, string sortExpression, DataSortDirection sortDirection)
{
List<Person> list = new List<Person>();
//PersonCollection list = new PersonCollection();
StringBuilder str = new StringBuilder();
str.Append("SELECT p.*, ");
str.Append(" (");
str.Append(" SELECT CAST(COUNT(pkid) AS int)");
str.Append(" FROM tbl_movies_to_persons");
str.Append(" WHERE person_pkid = p.pkid");
str.Append(" AND as_actor = 1");
str.Append(" ) AS actorQuantity,");
str.Append(" (");
str.Append(" SELECT CAST(COUNT(pkid) AS int)");
str.Append(" FROM tbl_movies_to_persons");
str.Append(" WHERE person_pkid = p.pkid");
str.Append(" AND as_director = 1");
str.Append(" ) AS directorQuantity,");
str.Append(" (");
str.Append(" SELECT CAST(COUNT(pkid) AS int)");
str.Append(" FROM tbl_movies_to_persons");
str.Append(" WHERE person_pkid = p.pkid");
str.Append(" AND as_producer = 1");
str.Append(" ) AS producerQuantity, ");
str.Append(" (");
str.Append(" SELECT CAST(COUNT(pkid) AS int)");
str.Append(" FROM tbl_movies_to_persons");
str.Append(" WHERE person_pkid = p.pkid");
str.Append(" AND as_musician = 1");
str.Append(" ) AS musicianQuantity, ");
str.Append(" (");
str.Append(" SELECT CAST(COUNT(pkid) AS int)");
str.Append(" FROM tbl_movies_to_persons");
str.Append(" WHERE person_pkid = p.pkid");
str.Append(" AND as_cameraman = 1");
str.Append(" ) AS cameramanQuantity, ");
str.Append(" (");
str.Append(" SELECT CAST(COUNT(pkid) AS int)");
str.Append(" FROM tbl_movies_to_persons");
str.Append(" WHERE person_pkid = p.pkid");
str.Append(" AND as_cutter = 1");
str.Append(" ) AS cutterQuantity, ");
str.Append("(");
str.Append(" SELECT CAST(COUNT(pkid) AS int)");
str.Append(" FROM tbl_movies_to_persons");
str.Append(" WHERE person_pkid = p.pkid");
str.Append(" AND as_writer = 1");
str.Append(" ) AS writerQuantity, ");
str.Append("(");
str.Append(" SELECT CAST(COUNT(DISTINCT movie_pkid) AS int) ");
str.Append(" FROM tbl_movies_to_persons ");
str.Append(" WHERE person_pkid = p.pkid ");
str.Append(" AND ( as_director = 1 OR as_producer = 1 OR as_actor = 1 OR as_musician = 1 OR as_cameraman = 1 OR as_cutter = 1 OR as_writer = 1 )");
str.Append(") AS movieQuantity ");
str.Append(" FROM tbl_persons AS p");
switch(mot) {
case MovieObjectType.Genre:
break;
case MovieObjectType.Actor:
str.Append(" WHERE p.is_actor = 1");
break;
case MovieObjectType.Director:
str.Append(" WHERE p.is_director = 1");
break;
case MovieObjectType.Producer:
str.Append(" WHERE p.is_producer = 1");
break;
case MovieObjectType.Musician:
str.Append(" WHERE p.is_musician = 1");
break;
case MovieObjectType.Cameraman:
str.Append(" WHERE p.is_cameraman = 1");
break;
case MovieObjectType.Cutter:
str.Append(" WHERE p.is_cutter = 1");
break;
case MovieObjectType.Writer:
str.Append(" WHERE p.is_writer = 1");
break;
case MovieObjectType.All:
str.Append(" WHERE ( NOT (p.pkid IS NULL) )");
break;
}
if(name != null
&& name.Trim() != "") {
if(this._cfg.ProviderType == ProviderType.SQLite) {
str.Append(" AND ( ");
str.Append(" ( LOWER(p.firstname) LIKE LOWER('{" + name + "}') AND LOWER(p.lastname) LIKE LOWER('{" + name + "}') ) ");
str.Append(" OR ( LOWER(p.firstname) + ' ' + LOWER(p.lastname) LIKE LOWER('{" + name + "}') ) ");
str.Append(" OR ( LOWER(p.lastname) + ' ' + LOWER(p.firstname) LIKE LOWER('{" + name + "}') ) ");
str.Append(" ) ");
}
else {
str.Append(" AND ( ");
str.Append(" ( LOWER(p.firstname) LIKE LOWER('%" + name + "%') AND LOWER(p.lastname) LIKE LOWER('%" + name + "%') ) ");
str.Append(" OR ( LOWER(p.firstname) + ' ' + LOWER(p.lastname) LIKE LOWER('%" + name + "%') ) ");
str.Append(" OR ( LOWER(p.lastname) + ' ' + LOWER(p.firstname) LIKE LOWER('%" + name + "%') ) ");
str.Append(" ) ");
}
}
// add sort expression
if(!string.IsNullOrEmpty(sortExpression)) {
switch(sortExpression) {
case "MovieQuantity":
str.Append("\r\n");
str.AppendFormat(
SqlResources.GetPersonList_SortOrder,
"movieQuantity",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
str.Append("\r\n");
str.AppendFormat(
", p.firstname {0}, p.lastname {0}",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
break;
case "MovieQuantityAsActor":
str.Append("\r\n");
str.AppendFormat(
SqlResources.GetPersonList_SortOrder,
"actorQuantity",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
str.Append("\r\n");
str.AppendFormat(
", p.firstname {0}, p.lastname {0}",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
break;
case "MovieQuantityAsDirector":
str.Append("\r\n");
str.AppendFormat(
SqlResources.GetPersonList_SortOrder,
"directorQuantity",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
str.Append("\r\n");
str.AppendFormat(
", p.firstname {0}, p.lastname {0}",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
break;
case "MovieQuantityAsProducer":
str.Append("\r\n");
str.AppendFormat(
SqlResources.GetPersonList_SortOrder,
"producerQuantity",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
str.Append("\r\n");
str.AppendFormat(
", p.firstname {0}, p.lastname {0}",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
break;
case "MovieQuantityAsMusician":
str.Append("\r\n");
str.AppendFormat(
SqlResources.GetPersonList_SortOrder,
"musicianQuantity",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
str.Append("\r\n");
str.AppendFormat(
", p.firstname {0}, p.lastname {0}",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
break;
case "MovieQuantityAsCameraman":
str.Append("\r\n");
str.AppendFormat(
SqlResources.GetPersonList_SortOrder,
"cameramanQuantity",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
str.Append("\r\n");
str.AppendFormat(
", p.firstname {0}, p.lastname {0}",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
break;
case "MovieQuantityAsCutter":
str.Append("\r\n");
str.AppendFormat(
SqlResources.GetPersonList_SortOrder,
"cutterQuantity",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
str.Append("\r\n");
str.AppendFormat(
", p.firstname {0}, p.lastname {0}",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
break;
case "MovieQuantityAsWriter":
str.Append("\r\n");
str.AppendFormat(
SqlResources.GetPersonList_SortOrder,
"writerQuantity",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
str.Append("\r\n");
str.AppendFormat(
", p.firstname {0}, p.lastname {0}",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
break;
case "Fullname":
default:
str.Append("\r\n");
str.AppendFormat(
SqlResources.GetPersonList_SortOrder,
"p.firstname",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
str.AppendFormat(
", p.lastname {0}",
( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
);
break;
}
}
else {
str.Append(" ORDER BY p.firstname ASC, p.lastname ASC");
}
if(this._cfg.ProviderType == ProviderType.SQLite) {
str = str.Replace(" = 1", " = 'True'");
}
// TODO: Optimize for SQLite
// new way
using(DAL dal = new DAL(this._cfg)) {
IDbCommand cmd = dal.CreateCommand();
cmd.CommandText = str.ToString();
dal.OpenConnection();
using(IDataReader reader = dal.ExecuteQueryForDataReader(cmd)) {
while(reader.Read()) {
list.Add(
new Person(
reader.GetSafeValue<Guid>("pkid").ToString(),
reader.GetSafeValue<string>("firstname"),
reader.GetSafeValue<string>("lastname"),
reader.GetSafeValue<bool>("is_actor"),
reader.GetSafeValue<bool>("is_director"),
reader.GetSafeValue<bool>("is_producer"),
( reader["is_cameraman"] == DBNull.Value ? false : reader.GetSafeValue<bool>("is_cameraman") ),
( reader["is_cutter"] == DBNull.Value ? false : reader.GetSafeValue<bool>("is_cutter") ),
( reader["is_musician"] == DBNull.Value ? false : reader.GetSafeValue<bool>("is_musician") ),
( reader["is_writer"] == DBNull.Value ? false : reader.GetSafeValue<bool>("is_writer") )
) {
MovieQuantityAsActor = reader["actorQuantity"].ToString().ToInt32(),
MovieQuantityAsDirector = reader["directorQuantity"].ToString().ToInt32(),
MovieQuantityAsProducer = reader["producerQuantity"].ToString().ToInt32(),
MovieQuantityAsCameraman = reader["cameramanQuantity"].ToString().ToInt32(),
MovieQuantityAsCutter = reader["cutterQuantity"].ToString().ToInt32(),
MovieQuantityAsMusician = reader["musicianQuantity"].ToString().ToInt32(),
MovieQuantityAsWriter = reader["writerQuantity"].ToString().ToInt32(),
MovieQuantity = reader["movieQuantity"].ToString().ToInt32()
}
);
}
}
}
return list;
}
/// <summary>
/// Get a list of movies
/// </summary>
/// <param name="filter">The filter.</param>
/// <param name="value">The value.</param>
/// <param name="sortExpression">The sort expression.</param>
/// <param name="sortDirection">The sort direction.</param>
/// <returns></returns>
public List<Movie> GetMovieList(
FilterType filter,
string value,
string sortExpression,
DataSortDirection sortDirection)
{
return this.GetMovieList(filter, value, 0, false, sortExpression, sortDirection);
}
/// <summary>
/// Get a list of persons
/// </summary>
/// <param name="mot">The MovieObjectType.</param>
/// <param name="sortExpression">The sort expression.</param>
/// <param name="sortDirection">The sort direction.</param>
/// <returns></returns>
public List<Person> GetPersonList(MovieObjectType mot, string sortExpression, DataSortDirection sortDirection)
{
return this.GetPersonList(mot, "", sortExpression, sortDirection);
}
/// <summary>
/// Get a list of movies
/// </summary>
/// <param name="sortExpression">The sort expression.</param>
/// <param name="sortDirection">The sort direction.</param>
/// <returns></returns>
public List<Movie> GetMovieList(string sortExpression, DataSortDirection sortDirection)
{
return this.GetMovieList(FilterType.NoFilter, "", 0, false, sortExpression, sortDirection);
}
