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