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