/// <summary>
/// Check if the query need to have a join to the season table.
/// </summary>
/// <param name="qb"></param>
private void NeedSeasonJoin(ref QueryBuilder qb)
{
if (!seasonJoinExists)
{
qb.InnerJoin("season", "s2");
qb.On("s2.serie_id = s1.id");
this.seasonJoinExists = true;
}
}
/// <summary>
/// Build a season query from given data in the window fields.
/// </summary>
/// <returns></returns>
private string CreateSeasonQuery()
{
QueryBuilder qb = new QueryBuilder(Entity.Season.TABLE)
.Select(Repository.Season.Instance.GetSelectQueryForLabeledList(), "sa")
.InnerJoin("serie", "se")
.On("sa.serie_id = se.id");
if (this.genres != null && this.genres.Count > 0)
{
foreach (Entity.Genre genre in this.genres)
{
string id = genre.Id.ToString();
qb.InnerJoin("serie_genre", "sg" + id);
qb.On("sg" + id + ".serie_id = se.id");
qb.AndWhere("sg" + id + ".genre_id = :genreId" + id);
qb.SetParam("genreId" + id, genre.Id);
}
}
if (!String.IsNullOrEmpty((string)this.combo_Studio.SelectedValue))
{
qb.AndWhere("sa.studio_id = :studioId");
qb.SetParam("studioId", this.studios[this.combo_Studio.SelectedIndex].Id);
}
// Seasonal
if (this.combo_Seasonal.SelectedValue != null &&
!String.IsNullOrEmpty((string)this.combo_Seasonal.SelectedValue.ToString()))
{
qb.AndWhere("sa.seasonal = :seasonalIndex");
qb.SetParam("seasonalIndex", this.combo_Seasonal.SelectedIndex);
}
// Year
if (!String.IsNullOrEmpty((string)this.text_Year.Text))
{
qb.AndWhere("sa.year = :year");
qb.SetParam("year", (string)this.text_Year.Text);
}
// Type
if (this.ComboBox_Type.SelectedValue != null &&
!String.IsNullOrEmpty((string)this.ComboBox_Type.SelectedValue.ToString()))
{
qb.AndWhere("sa.type = :typeIndex");
qb.SetParam("typeIndex", this.ComboBox_Type.SelectedIndex);
}
// Source
if (this.ComboBox_Source.SelectedValue != null &&
!String.IsNullOrEmpty((string)this.ComboBox_Source.SelectedValue.ToString()))
{
qb.AndWhere("sa.source = :sourceIndex");
qb.SetParam("sourceIndex", this.ComboBox_Source.SelectedIndex);
}
// WHEREs must be after JOIN statments
qb.Where("1");
if (!String.IsNullOrWhiteSpace(this.textbox_Title.ActualText))
{
qb.AndWhere("sa.title LIKE '%" + this.textbox_Title.ActualText + "%'");
}
if (!String.IsNullOrWhiteSpace(this.text_Seasons.Text))
{
qb.AndWhere("seasons_count " + this.combo_SeasonsOperator.Text + " :seasons");
qb.SetParam("seasons", this.text_Seasons.Number);
}
if (!String.IsNullOrWhiteSpace(this.text_Episodes.Text))
{
qb.AndWhere("episodes_total " + this.combo_EpisodesOperator.Text + " :episodes");
qb.SetParam("episodes", this.text_Episodes.Number);
}
if (this.StatusId != (int)Entity.DefaultStatus.All)
{
qb.AndWhere("sa.status_id = :status");
qb.SetParam("status", this.StatusId);
}
qb.OrderBy("sa." + Settings.Default.TileOrderBy, Settings.Default.TileOrderByDirection, true);
return(qb.Query);
}
