/// <summary> /// Populate all filter values on page load /// </summary> /// <param name="titleSearch"></param> /// <param name="pageNumber"></param> /// <returns></returns> public ActionResult Titles(TitleSearch titleSearch, int?pageNumber) { try { if (TempData["ErrorMsg"] != null) { ViewBag.ErrorMsg = TempData["ErrorMsg"]; } TitleService _title = new TitleService(); var titles = _title.GetTitles().ToPagedList(pageNumber ?? 1, 10); var searchValues = _title.GetSearchValues(); searchValues.SelectedTitleList = searchValues.SelectedTitles.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.TerritoryList = searchValues.Territories.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.LanguageList = searchValues.Languages.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.MPMList = searchValues.MPMs.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.VendorIdList = searchValues.VendorIds.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.VideoVersionList = searchValues.VideoVersions.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.RegionList = searchValues.Regions.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); Titles title = new Titles(); title.TitleSearch = searchValues; title.TitleList = titles; return(View(title)); } catch (Exception ex) { return(View("Error")); } }
public FileResult ExportTitle(TitleSearch titleSearch) { ExportReportService _export = new ExportReportService(); DownLoadFile downloadFile = _export.GenerateTitleReport(titleSearch); //string path = ConfigurationManager.AppSettings["directoryPath"]; //string path = App.Config.ReportDirectoryPath; //string downloadFile = string.Format(path, file); return(File(downloadFile.bufferByte, "application/vnd.ms-excel", downloadFile.FileName)); }
public IQueryable <Book> filterbooks(IQueryable <Book> books, string searchstring, string author, string publisher) { var titlesearch = new TitleSearch(); books = titlesearch.Result(books, searchstring); var authorsearch = new AuthorSearch(); books = authorsearch.Result(books, author); var publishersearch = new PublisherSearch(); books = publishersearch.Result(books, publisher); return(books); }
/// <summary> /// Populate all filter values on page load /// </summary> /// <returns>Filters value</returns> public TitleSearch GetSearchValues() { TitleSearch titleSearch = new TitleSearch(); var _context = new DeluxeOrderManagementEntities(); var selectedTitles = (from vid in _context.VIDs select vid.TitleName.ToString()).Distinct().OrderBy(x => x).ToList(); var titleCategory = (from ann in _context.OrderGrids select ann.Category.ToString()).Distinct().OrderBy(x => x).ToList(); var contentProviders = (from customer in _context.Customers where customer.Type == (int)Customers.ContentProvider select customer.Name.ToString() ).Distinct().ToList(); var contentDistributors = (from customer in _context.Customers where customer.Type == (int)Customers.ContentDistributor select customer.Name.ToString() ).Distinct().ToList(); var territories = (from territory in _context.Territories where territory.AppleTerritory != null select territory.AppleTerritory.ToString()).Distinct().OrderBy(x => x).ToList(); var languages = (from language in _context.Languages select language.Name.ToString()).Distinct().OrderBy(x => x).ToList(); var videoVersions = (from vid in _context.VIDs select vid.VideoVersion.ToString()).Distinct().OrderBy(x => x).ToList(); var vendorIds = (from vid in _context.VIDs where !string.IsNullOrEmpty(vid.VendorId) select vid.VendorId.ToString()).Distinct().OrderBy(x => x).ToList(); var mpm = (from vid in _context.VIDs select vid.MPM.ToString()).Distinct().OrderBy(x => x).ToList(); var regions = (from reg in _context.Regions select reg.NAME.ToString()).Distinct().OrderBy(x => x).ToList(); titleSearch.SelectedTitles = selectedTitles; titleSearch.EditTypes = titleCategory; titleSearch.ContentDistributors = contentDistributors; titleSearch.ContentProviders = contentProviders; titleSearch.Territories = territories; titleSearch.Languages = languages; titleSearch.VideoVersions = videoVersions; titleSearch.MPMs = mpm; titleSearch.VendorIds = vendorIds; titleSearch.Regions = regions; return(titleSearch); }
public ActionResult ExportTitle(TitleSearch titleSearch) { //Titles(TitleSearch titleSearch, int? pageNumber) IExportReportService _export = new ExportReportService(); int recordCount = _export.GetTitleReportRecordCount(titleSearch); if (recordCount > App.Config.MaxTitleReportDownloadRecordLimit) { titleSearch.Territory = string.IsNullOrEmpty(titleSearch.TerritoryConcat) ? titleSearch.Territory : titleSearch.TerritoryConcat.Split('|'); titleSearch.Language = string.IsNullOrEmpty(titleSearch.LanguageConcate) ? titleSearch.Language : titleSearch.LanguageConcate.Split('|'); titleSearch.Region = string.IsNullOrEmpty(titleSearch.RegionConcat) ? titleSearch.Region : titleSearch.RegionConcat.Split('|'); TempData["ErrorMsg"] = string.Format("There are {0} records that you are trying to download. Record count should not exceed {1} records.", recordCount, App.Config.MaxTitleReportDownloadRecordLimit); return(RedirectToAction("Titles", "Title", new { @titleSearch = titleSearch })); //return RedirectToAction("Index"); } DownLoadFile downloadFile = _export.GenerateTitleReport(titleSearch); return(File(downloadFile.bufferByte, "application/vnd.ms-excel", downloadFile.FileName)); }
public PagedResponse <TitleDto> Execute(TitleSearch search) { var query = _context.Titles.Include(x => x.Prices) .Include(x => x.Publisher).Include(x => x.Category) .Include(x => x.TitleAuthors).ThenInclude(x => x.Author) .DefaultFilter(search); if (search.PublisherIds.Count() > 0) { query = query.Where(x => search.PublisherIds.Contains(x.PublisherId)); } if (search.CategoryIds.Count() > 0) { query = query.Where(x => search.CategoryIds.Contains(x.CategoryId)); } if (search.AuthorIds.Count() > 0) { query = query.Where(x => x.TitleAuthors.Any(i => search.AuthorIds.Contains(i.AuthorId))); } if (!string.IsNullOrEmpty(search.Name) || !string.IsNullOrWhiteSpace(search.Name)) { query = query.Where(x => x.Name.ToLower().Contains(search.Name.ToLower())); } if (search.IssueYearFrom.HasValue) { query = query.Where(x => x.IssueYear >= search.IssueYearFrom.Value); } if (search.IssueYearTo.HasValue) { query = query.Where(x => x.IssueYear <= search.IssueYearTo.Value); } return(query.Paged <TitleDto, Domain.Title>(search, _mapper)); }
public List <TitleList> SearchTitles(TitleSearch titleSearch) { string whrSQLquery = string.Empty; string temp = string.Empty; if (!string.IsNullOrEmpty(titleSearch.StartDate) && !string.IsNullOrEmpty(titleSearch.EndDate)) { //titleSearch.ESTStartDate = Convert.ToDateTime(titleSearch.StartDate); //titleSearch.ESTEndDate = Convert.ToDateTime(titleSearch.EndDate); titleSearch.ESTStartDate = DateTime.ParseExact(titleSearch.StartDate, "MM/dd/yyyy", null); titleSearch.ESTEndDate = DateTime.ParseExact(titleSearch.EndDate, "MM/dd/yyyy", null); } if (!string.IsNullOrEmpty(titleSearch.SelectedTitle)) { temp = titleSearch.SelectedTitle; titleSearch.SelectedTitle = titleSearch.SelectedTitle.Replace("'", "\''"); whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " TitleName ='" + titleSearch.SelectedTitle + "' " : whrSQLquery + " AND TitleName = '" + titleSearch.SelectedTitle + "' "; } if (!string.IsNullOrEmpty(titleSearch.EditType)) { whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " Category ='" + titleSearch.EditType + "' " : whrSQLquery + " AND Category = '" + titleSearch.EditType + "' "; } if (!string.IsNullOrEmpty(titleSearch.Territory)) { whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " AppleTerritory ='" + titleSearch.Territory + "' " : whrSQLquery + " AND AppleTerritory = '" + titleSearch.Territory + "' "; } if (!string.IsNullOrEmpty(titleSearch.Language)) { whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " LanguageName ='" + titleSearch.Language + "' " : whrSQLquery + " AND LanguageName = '" + titleSearch.Language + "' "; } if (!string.IsNullOrEmpty(titleSearch.VideoVersion)) { whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " VideoVersion ='" + titleSearch.VideoVersion + "' " : whrSQLquery + " AND VideoVersion = '" + titleSearch.VideoVersion + "' "; } if ((titleSearch.ESTStartDate != null && !(titleSearch.ESTStartDate.Equals(DateTime.MinValue))) && (titleSearch.ESTEndDate != null && !(titleSearch.ESTEndDate.Equals(DateTime.MinValue)))) { string estStartDate = titleSearch.ESTStartDate.Date.ToString("yyyy-MM-dd"); string estEndDate = titleSearch.ESTEndDate.AddDays(1).Date.ToString("yyyy-MM-dd"); whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " (ESTStartDate >='" + estStartDate + "' AND ESTEndDate <'" + estEndDate + "') " : whrSQLquery + " AND (ESTStartDate >='" + estStartDate + "' AND ESTEndDate <'" + estEndDate + "') "; } if (!string.IsNullOrEmpty(titleSearch.MPM)) { whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " MPM ='" + titleSearch.MPM + "' " : whrSQLquery + " AND MPM = '" + titleSearch.MPM + "' "; } if (!string.IsNullOrEmpty(titleSearch.VendorId)) { whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " VendorId ='" + titleSearch.VendorId + "' " : whrSQLquery + " AND VendorId = '" + titleSearch.VendorId + "' "; } if (!string.IsNullOrEmpty(titleSearch.ComponentType)) { string componentTypes = string.Empty; if (titleSearch.ComponentType.Equals("Audio")) { componentTypes = "'AUDIO'" + "," + "'FORCED_SUBTITLES'" + "," + "'DUB_CREDIT'" + "," + "'AUDIO_DESCRIPTION'"; } else if (titleSearch.ComponentType.Equals("Sub")) { componentTypes = "'SUBTITLES'" + "," + "'CAPTIONS'"; } else if (titleSearch.ComponentType.Equals("Video")) { componentTypes = "'VIDEO'"; } whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " ComponentType IN(" + componentTypes + ")" : whrSQLquery + " AND ComponentType IN(" + componentTypes + ") "; } if (!string.IsNullOrEmpty(whrSQLquery)) { whrSQLquery = "where" + whrSQLquery; } if (!string.IsNullOrEmpty(titleSearch.SortBy)) { whrSQLquery = whrSQLquery + " ORDER BY " + " " + titleSearch.SortBy + " " + titleSearch.SortOrder; } // this if block is to resore title name which is modified for search title with ' if (!string.IsNullOrEmpty(temp)) { titleSearch.SelectedTitle = temp; } var titles = _repository.SearchTitles(whrSQLquery); return(titles); }
public ActionResult SearchTitle(TitleSearch titleSearch, int?pageNumber) { #region old code //if (!ModelState.IsValid) //{ // TitleService _title = new TitleService(); // var titles = new List<TitleList>().ToPagedList(1, 10); // var searchValues = _title.GetSearchValues(); // titleSearch.Territory = string.IsNullOrEmpty(titleSearch.TerritoryConcat) ? titleSearch.Territory : titleSearch.TerritoryConcat.Split('|'); // titleSearch.Language = string.IsNullOrEmpty(titleSearch.LanguageConcate) ? titleSearch.Language : titleSearch.LanguageConcate.Split('|'); // titleSearch.Region = string.IsNullOrEmpty(titleSearch.RegionConcat) ? titleSearch.Region : titleSearch.RegionConcat.Split('|'); // searchValues.SelectedTitleList = searchValues.SelectedTitles.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.TerritoryList = searchValues.Territories.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.LanguageList = searchValues.Languages.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.MPMList = searchValues.MPMs.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.VendorIdList = searchValues.VendorIds.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.VideoVersionList = searchValues.VideoVersions.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.RegionList = searchValues.Regions.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // Titles title = new Titles(); // title.TitleList = titles; // title.TitleSearch = titleSearch; // title.TitleSearch.ContentDistributors = searchValues.ContentDistributors; // title.TitleSearch.ContentProviders = searchValues.ContentProviders; // title.TitleSearch.SelectedTitleList = searchValues.SelectedTitleList; // title.TitleSearch.EditTypes = searchValues.EditTypes; // title.TitleSearch.TerritoryList = searchValues.TerritoryList; // title.TitleSearch.LanguageList = searchValues.LanguageList; // title.TitleSearch.VideoVersionList = searchValues.VideoVersionList; // title.TitleSearch.MPMList = searchValues.MPMList; // title.TitleSearch.VendorIdList = searchValues.VendorIdList; // title.TitleSearch.ComponentTypes = searchValues.ComponentTypes; // title.TitleSearch.SortOrderList = searchValues.SortOrderList; // title.TitleSearch.SortByList = searchValues.SortByList; // title.TitleSearch.ChannelDateRangeList = searchValues.ChannelDateRangeList; // title.TitleSearch.RegionList = searchValues.RegionList; // return View("Titles", title); //} //try //{ // TitleService _title = new TitleService(); // titleSearch.Territory = string.IsNullOrEmpty(titleSearch.TerritoryConcat) ? titleSearch.Territory : titleSearch.TerritoryConcat.Split('|'); // titleSearch.Language = string.IsNullOrEmpty(titleSearch.LanguageConcate) ? titleSearch.Language : titleSearch.LanguageConcate.Split('|'); // titleSearch.Region = string.IsNullOrEmpty(titleSearch.RegionConcat) ? titleSearch.Region : titleSearch.RegionConcat.Split('|'); // var titles = _title.SearchTitles(titleSearch).ToPagedList(pageNumber ?? 1, 10); // var searchValues = _title.GetSearchValues(); // searchValues.SelectedTitleList = searchValues.SelectedTitles.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.TerritoryList = searchValues.Territories.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.LanguageList = searchValues.Languages.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.MPMList = searchValues.MPMs.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.VendorIdList = searchValues.VendorIds.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.VideoVersionList = searchValues.VideoVersions.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // searchValues.RegionList = searchValues.Regions.Select(x => new SelectListItem() // { // Text = x, // Value = x // }).Distinct().ToList(); // Titles title = new Titles(); // title.TitleList = titles; // title.TitleSearch = titleSearch; // title.TitleSearch.ContentDistributors = searchValues.ContentDistributors; // title.TitleSearch.ContentProviders = searchValues.ContentProviders; // title.TitleSearch.SelectedTitleList = searchValues.SelectedTitleList; // title.TitleSearch.EditTypes = searchValues.EditTypes; // title.TitleSearch.TerritoryList = searchValues.TerritoryList; // title.TitleSearch.LanguageList = searchValues.LanguageList; // title.TitleSearch.VideoVersionList = searchValues.VideoVersionList; // title.TitleSearch.MPMList = searchValues.MPMList; // title.TitleSearch.VendorIdList = searchValues.VendorIdList; // title.TitleSearch.ComponentTypes = searchValues.ComponentTypes; // title.TitleSearch.SortOrderList = searchValues.SortOrderList; // title.TitleSearch.SortByList = searchValues.SortByList; // title.TitleSearch.ChannelDateRangeList = searchValues.ChannelDateRangeList; // title.TitleSearch.RegionList = searchValues.RegionList; // return View("Titles", title); //} //catch (Exception ex) //{ // return View("Error"); //} #endregion TitleService _title = new TitleService(); try { titleSearch.Territory = string.IsNullOrEmpty(titleSearch.TerritoryConcat) ? titleSearch.Territory : titleSearch.TerritoryConcat.Split('|'); titleSearch.Language = string.IsNullOrEmpty(titleSearch.LanguageConcate) ? titleSearch.Language : titleSearch.LanguageConcate.Split('|'); titleSearch.Region = string.IsNullOrEmpty(titleSearch.RegionConcat) ? titleSearch.Region : titleSearch.RegionConcat.Split('|'); var searchValues = _title.GetSearchValues(); searchValues.SelectedTitleList = searchValues.SelectedTitles.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.TerritoryList = searchValues.Territories.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.LanguageList = searchValues.Languages.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.MPMList = searchValues.MPMs.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.VendorIdList = searchValues.VendorIds.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.VideoVersionList = searchValues.VideoVersions.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.RegionList = searchValues.Regions.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); Titles title = new Titles(); title.TitleSearch = titleSearch; title.TitleSearch.ContentDistributors = searchValues.ContentDistributors; title.TitleSearch.ContentProviders = searchValues.ContentProviders; title.TitleSearch.SelectedTitleList = searchValues.SelectedTitleList; title.TitleSearch.EditTypes = searchValues.EditTypes; title.TitleSearch.TerritoryList = searchValues.TerritoryList; title.TitleSearch.LanguageList = searchValues.LanguageList; title.TitleSearch.VideoVersionList = searchValues.VideoVersionList; title.TitleSearch.MPMList = searchValues.MPMList; title.TitleSearch.VendorIdList = searchValues.VendorIdList; title.TitleSearch.ComponentTypes = searchValues.ComponentTypes; title.TitleSearch.SortOrderList = searchValues.SortOrderList; title.TitleSearch.SortByList = searchValues.SortByList; title.TitleSearch.ChannelDateRangeList = searchValues.ChannelDateRangeList; title.TitleSearch.RegionList = searchValues.RegionList; if (!ModelState.IsValid) { title.TitleList = new List <TitleList>().ToPagedList(1, 10); return(View("Titles", title)); } title.TitleList = _title.SearchTitles(titleSearch).ToPagedList(pageNumber ?? 1, 10); return(View("Titles", title)); } catch (Exception ex) { return(View("Error")); } }
/// <summary> /// Title Record count for selected filter criteria /// </summary> /// <param name="titleSearch">Filters value</param> /// <returns>Record count</returns> public int GetTitleReportRecordCount(TitleSearch titleSearch) { string whereClause = getTitleReportWhereClause(titleSearch); return(_repository.GetTitleReportRecordCount(whereClause)); }
/// <summary> /// Concatenate filter value in string /// </summary> /// <param name="titleSearch">Filters value</param> /// <returns>Filters values concatenated into a string</returns> private string getTitleReportWhereClause(TitleSearch titleSearch) { string whrSQLquery = string.Empty; string temp = string.Empty; if (!string.IsNullOrEmpty(titleSearch.StartDate) && !string.IsNullOrEmpty(titleSearch.EndDate)) { //titleSearch.ESTStartDate = DateTime.ParseExact(titleSearch.StartDate, "MM/dd/yyyy", null); //titleSearch.ESTEndDate = DateTime.ParseExact(titleSearch.EndDate, "MM/dd/yyyy", null); var channelStart = DateTime.ParseExact(titleSearch.StartDate, "MM/dd/yyyy", null); var channelEnd = DateTime.ParseExact(titleSearch.EndDate, "MM/dd/yyyy", null); if (titleSearch.ChannelDateRange.ToLower() == "est") { titleSearch.ESTStartDate = channelStart; titleSearch.ESTEndDate = channelEnd; } else if (titleSearch.ChannelDateRange.ToLower() == "poest") { titleSearch.POESTStart = channelStart; titleSearch.POESTEnd = channelEnd; } } if (!string.IsNullOrEmpty(titleSearch.SelectedTitle)) { temp = titleSearch.SelectedTitle; titleSearch.SelectedTitle = titleSearch.SelectedTitle.Replace("'", "\''"); whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " TitleName ='" + titleSearch.SelectedTitle + "' " : whrSQLquery + " AND TitleName = '" + titleSearch.SelectedTitle + "' "; } if (!string.IsNullOrEmpty(titleSearch.EditType)) { whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " Category ='" + titleSearch.EditType + "' " : whrSQLquery + " AND Category = '" + titleSearch.EditType + "' "; } if (titleSearch.Region != null && titleSearch.Region.Any()) { var concat = titleSearch.Region.Aggregate((current, next) => current + "', '" + next); concat = string.Format("{0}{1}{0}", "'", concat); whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " RegionName IN(" + concat + ") " : whrSQLquery + " AND RegionName IN(" + concat + ") "; } if (titleSearch.Territory != null && titleSearch.Territory.Any()) { var concatterritory = titleSearch.Territory.Aggregate((current, next) => current + "', '" + next); concatterritory = string.Format("{0}{1}{0}", "'", concatterritory); whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " AppleTerritory IN(" + concatterritory + ") " : whrSQLquery + " AND AppleTerritory IN(" + concatterritory + ") "; } if (titleSearch.Language != null && titleSearch.Language.Any()) { var concateLanguage = titleSearch.Language.Aggregate((current, next) => current + "', '" + next); concateLanguage = string.Format("{0}{1}{0}", "'", concateLanguage); whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " LanguageName IN(" + concateLanguage + ") " : whrSQLquery + " AND LanguageName IN(" + concateLanguage + ") "; } if (!string.IsNullOrEmpty(titleSearch.VideoVersion)) { whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " VideoVersion ='" + titleSearch.VideoVersion + "' " : whrSQLquery + " AND VideoVersion = '" + titleSearch.VideoVersion + "' "; } if ((titleSearch.ESTStartDate != null && !(titleSearch.ESTStartDate.Equals(DateTime.MinValue))) && (titleSearch.ESTEndDate != null && !(titleSearch.ESTEndDate.Equals(DateTime.MinValue)))) { string estStartDate = titleSearch.ESTStartDate.Date.ToString("yyyy-MM-dd"); string estEndDate = titleSearch.ESTEndDate.AddDays(1).Date.ToString("yyyy-MM-dd"); whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " (ESTStartDate >='" + estStartDate + "' AND ESTStartDate <'" + estEndDate + "') " : whrSQLquery + " AND (ESTStartDate >='" + estStartDate + "' AND ESTStartDate <'" + estEndDate + "') "; } if ((titleSearch.POESTStart != null && !(titleSearch.POESTStart.Equals(DateTime.MinValue))) && (titleSearch.POESTEnd != null && !(titleSearch.POESTEnd.Equals(DateTime.MinValue)))) { string poeststart = titleSearch.POESTStart.Date.ToString("yyyy-MM-dd"); string poestend = titleSearch.POESTEnd.AddDays(1).Date.ToString("yyyy-MM-dd"); whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " (POESTStartDate >='" + poeststart + "' AND POESTStartDate <'" + poestend + "') " : whrSQLquery + " AND (POESTStartDate >='" + poeststart + "' AND POESTStartDate <'" + poestend + "') "; } if (!string.IsNullOrEmpty(titleSearch.MPM)) { whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " MPM ='" + titleSearch.MPM + "' " : whrSQLquery + " AND MPM = '" + titleSearch.MPM + "' "; } if (!string.IsNullOrEmpty(titleSearch.VendorId)) { whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " VendorId ='" + titleSearch.VendorId + "' " : whrSQLquery + " AND VendorId = '" + titleSearch.VendorId + "' "; } if (!string.IsNullOrEmpty(titleSearch.ComponentType)) { string componentTypes = string.Empty; if (titleSearch.ComponentType.Equals("Audio")) { componentTypes = "'AUDIO'" + "," + "'FORCED_SUBTITLES'" + "," + "'DUB_CREDIT'" + "," + "'AUDIO_DESCRIPTION'"; } else if (titleSearch.ComponentType.Equals("Sub")) { componentTypes = "'SUBTITLES'" + "," + "'CAPTIONS'"; } else if (titleSearch.ComponentType.Equals("Video")) { componentTypes = "'VIDEO'"; } whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " ComponentType IN(" + componentTypes + ")" : whrSQLquery + " AND ComponentType IN(" + componentTypes + ") "; } if (!string.IsNullOrEmpty(whrSQLquery)) { whrSQLquery = "where" + whrSQLquery; } if (!string.IsNullOrEmpty(titleSearch.SortBy)) { whrSQLquery = whrSQLquery + " ORDER BY " + " " + titleSearch.SortBy + " " + titleSearch.SortOrder; } // this if block is to resore title name which is modified for search title with ' if (!string.IsNullOrEmpty(temp)) { titleSearch.SelectedTitle = temp; } return(whrSQLquery); }
/// <summary> /// Export Title Report in .xlsx for selected filter criteria /// </summary> /// <param name="reportSearch">Filters values</param> /// <returns>File name and Byte stream</returns> public DownLoadFile GenerateTitleReport(TitleSearch titleSearch) { string whrSQLquery = this.getTitleReportWhereClause(titleSearch); #region Moved to GetTitleWhereClause() //string temp = string.Empty; //if (!string.IsNullOrEmpty(titleSearch.StartDate) && !string.IsNullOrEmpty(titleSearch.EndDate)) //{ // //titleSearch.ESTStartDate = Convert.ToDateTime(titleSearch.StartDate); // //titleSearch.ESTEndDate = Convert.ToDateTime(titleSearch.EndDate); // titleSearch.ESTStartDate = DateTime.ParseExact(titleSearch.StartDate, "MM/dd/yyyy", null); // titleSearch.ESTEndDate = DateTime.ParseExact(titleSearch.EndDate, "MM/dd/yyyy", null); //} //if (!string.IsNullOrEmpty(titleSearch.SelectedTitle)) //{ // temp = titleSearch.SelectedTitle; // titleSearch.SelectedTitle = titleSearch.SelectedTitle.Replace("'", "\''"); // whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " TitleName ='" + titleSearch.SelectedTitle + "' " : whrSQLquery + " AND TitleName = '" + titleSearch.SelectedTitle + "' "; //} //if (!string.IsNullOrEmpty(titleSearch.EditType)) //{ // whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " Category ='" + titleSearch.EditType + "' " : whrSQLquery + " AND Category = '" + titleSearch.EditType + "' "; //} //if (!string.IsNullOrEmpty(titleSearch.Territory)) //{ // whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " AppleTerritory ='" + titleSearch.Territory + "' " : whrSQLquery + " AND AppleTerritory = '" + titleSearch.Territory + "' "; //} //if (!string.IsNullOrEmpty(titleSearch.Language)) //{ // whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " LanguageName ='" + titleSearch.Language + "' " : whrSQLquery + " AND LanguageName = '" + titleSearch.Language + "' "; //} //if (!string.IsNullOrEmpty(titleSearch.VideoVersion)) //{ // whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " VideoVersion ='" + titleSearch.VideoVersion + "' " : whrSQLquery + " AND VideoVersion = '" + titleSearch.VideoVersion + "' "; //} //if ((titleSearch.ESTStartDate != null && !(titleSearch.ESTStartDate.Equals(DateTime.MinValue))) && (titleSearch.ESTEndDate != null && !(titleSearch.ESTEndDate.Equals(DateTime.MinValue)))) //{ // string estStartDate = titleSearch.ESTStartDate.Date.ToString("yyyy-MM-dd"); // string estEndDate = titleSearch.ESTEndDate.AddDays(1).Date.ToString("yyyy-MM-dd"); // whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " (ESTStartDate >='" + estStartDate + "' AND ESTEndDate <'" + estEndDate + "') " : whrSQLquery + " AND (ESTStartDate >='" + estStartDate + "' AND ESTEndDate <'" + estEndDate + "') "; //} //if (!string.IsNullOrEmpty(titleSearch.MPM)) //{ // whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " MPM ='" + titleSearch.MPM + "' " : whrSQLquery + " AND MPM = '" + titleSearch.MPM + "' "; //} //if (!string.IsNullOrEmpty(titleSearch.VendorId)) //{ // whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " VendorId ='" + titleSearch.VendorId + "' " : whrSQLquery + " AND VendorId = '" + titleSearch.VendorId + "' "; //} //if (!string.IsNullOrEmpty(titleSearch.ComponentType)) //{ // string componentTypes = string.Empty; // if (titleSearch.ComponentType.Equals("Audio")) // { // componentTypes = "'AUDIO'" + "," + "'FORCED_SUBTITLES'" + "," + "'DUB_CREDIT'" + "," + "'AUDIO_DESCRIPTION'"; // } // else if (titleSearch.ComponentType.Equals("Sub")) // { // componentTypes = "'SUBTITLES'" + "," + "'CAPTIONS'"; // } // else if (titleSearch.ComponentType.Equals("Video")) // { // componentTypes = "'VIDEO'"; // } // whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " ComponentType IN(" + componentTypes + ")" : whrSQLquery + " AND ComponentType IN(" + componentTypes + ") "; //} //if (!string.IsNullOrEmpty(whrSQLquery)) //{ // whrSQLquery = "where" + whrSQLquery; //} //if (!string.IsNullOrEmpty(titleSearch.SortBy)) //{ // whrSQLquery = whrSQLquery + " ORDER BY " + " " + titleSearch.SortBy + " " + titleSearch.SortOrder; //} //// this if block is to resore title name which is modified for search title with ' //if (!string.IsNullOrEmpty(temp)) //{ // titleSearch.SelectedTitle = temp; //} #endregion //string TitleReportSourceFile = ConfigurationManager.AppSettings["TitleReportSourceFile"]; string TitleReportSourceFile = App.Config.TitleReportExportFilePath; //string TitleReportCopyFile = ConfigurationManager.AppSettings["TitleReportCopyFile"]; string TitleReportCopyFile = App.Config.TitleReportExportCopyFilePath; string downloadedFileName = "Title Report" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + ".xlsx"; //string path = ConfigurationManager.AppSettings["directoryPath"]; string path = App.Config.ReportDirectoryPath; string downloadFile = string.Format(path, downloadedFileName); File.Copy(TitleReportSourceFile, TitleReportCopyFile, true); _repository.GenerateTitleReport(whrSQLquery); File.Copy(TitleReportSourceFile, downloadFile, true); if (File.Exists(TitleReportSourceFile)) { File.Delete(TitleReportSourceFile); } File.Copy(TitleReportCopyFile, TitleReportSourceFile, true); if (File.Exists(TitleReportCopyFile)) { File.Delete(TitleReportCopyFile); } DownLoadFile download = DownloadFile(downloadFile, downloadedFileName); return(download); }
public ActionResult SearchTitle(TitleSearch titleSearch, int?pageNumber) { if (!ModelState.IsValid) { TitleService _title = new TitleService(); var titles = new List <TitleList>().ToPagedList(1, 10); var searchValues = _title.GetSearchValues(); searchValues.SelectedTitleList = searchValues.SelectedTitles.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.TerritoryList = searchValues.Territories.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.LanguageList = searchValues.Languages.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.MPMList = searchValues.MPMs.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.VendorIdList = searchValues.VendorIds.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.VideoVersionList = searchValues.VideoVersions.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); Titles title = new Titles(); title.TitleList = titles; title.TitleSearch = titleSearch; title.TitleSearch.ContentDistributors = searchValues.ContentDistributors; title.TitleSearch.ContentProviders = searchValues.ContentProviders; title.TitleSearch.SelectedTitleList = searchValues.SelectedTitleList; title.TitleSearch.EditTypes = searchValues.EditTypes; title.TitleSearch.TerritoryList = searchValues.TerritoryList; title.TitleSearch.LanguageList = searchValues.LanguageList; title.TitleSearch.VideoVersionList = searchValues.VideoVersionList; title.TitleSearch.MPMList = searchValues.MPMList; title.TitleSearch.VendorIdList = searchValues.VendorIdList; title.TitleSearch.ComponentTypes = searchValues.ComponentTypes; title.TitleSearch.SortOrderList = searchValues.SortOrderList; title.TitleSearch.SortByList = searchValues.SortByList; return(View("Titles", title)); } try { TitleService _title = new TitleService(); var titles = _title.SearchTitles(titleSearch).ToPagedList(pageNumber ?? 1, 10); var searchValues = _title.GetSearchValues(); searchValues.SelectedTitleList = searchValues.SelectedTitles.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.TerritoryList = searchValues.Territories.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.LanguageList = searchValues.Languages.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.MPMList = searchValues.MPMs.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.VendorIdList = searchValues.VendorIds.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); searchValues.VideoVersionList = searchValues.VideoVersions.Select(x => new SelectListItem() { Text = x, Value = x }).Distinct().ToList(); Titles title = new Titles(); title.TitleList = titles; title.TitleSearch = titleSearch; title.TitleSearch.ContentDistributors = searchValues.ContentDistributors; title.TitleSearch.ContentProviders = searchValues.ContentProviders; title.TitleSearch.SelectedTitleList = searchValues.SelectedTitleList; title.TitleSearch.EditTypes = searchValues.EditTypes; title.TitleSearch.TerritoryList = searchValues.TerritoryList; title.TitleSearch.LanguageList = searchValues.LanguageList; title.TitleSearch.VideoVersionList = searchValues.VideoVersionList; title.TitleSearch.MPMList = searchValues.MPMList; title.TitleSearch.VendorIdList = searchValues.VendorIdList; title.TitleSearch.ComponentTypes = searchValues.ComponentTypes; title.TitleSearch.SortOrderList = searchValues.SortOrderList; title.TitleSearch.SortByList = searchValues.SortByList; return(View("Titles", title)); } catch (Exception ex) { return(View("Error")); } }
public ActionResult Index(TitleSearch model) { return(RedirectToAction("Titles", new { term = model.TitleName, maxResults = model.MaxResults })); }
public IActionResult Get([FromQuery] TitleSearch search, [FromServices] IGetTitles query) { return(Ok(_executor.ExecuteQuery(query, search))); }
private void Process_Click(object sender, RoutedEventArgs e) { Output.Items.Clear(); query = null; queryIndex = 0; if (ListMode.IsChecked == true) { TitleSearch.Clear(); //Output.Items.Add("Searching with filters..."); if (OwnerDrop.SelectedIndex != 0) { query += "owner = '" + OwnerDrop.Text.ToLower() + "'"; queryIndex++; } //Output.Items.Add(OwnerDrop.Text); if (GenreDrop.SelectedIndex != 0) { if (queryIndex > 0) { query += " AND "; } query += "genre = '" + GenreDrop.Text.ToLower() + "'"; queryIndex++; } //Output.Items.Add(GenreDrop.Text); if (RatedDrop.SelectedIndex != 0) { if (queryIndex > 0) { query += " AND "; } query += "mpaa = '" + RatedDrop.Text.ToLower() + "'"; queryIndex++; } //Output.Items.Add(RatedDrop.Text); if (OwnRateDrop.SelectedIndex != 0) { if (queryIndex > 0) { query += " AND "; } query += "ownrate = '" + OwnRateDrop.Text.ToLower() + "'"; queryIndex++; } // Output.Items.Add(OwnRateDrop.Text); if (RottenToDrop.SelectedIndex != 0) { if (queryIndex > 0) { query += " AND "; } query += "rottentomatoes = '" + RottenToDrop.Text.ToLower() + "'"; queryIndex++; } //Output.Items.Add(RottenToDrop.Text); } else if (SearchMode.IsChecked == true) { OwnerDrop.SelectedIndex = 0; GenreDrop.SelectedIndex = 0; RatedDrop.SelectedIndex = 0; OwnRateDrop.SelectedIndex = 0; RottenToDrop.SelectedIndex = 0; //Output.Items.Add("Searching for " + TitleSearch.Text); query = "title = '" + TitleSearch.Text.ToLower() + "'"; queryIndex++; //straight to SQL stuff } if (queryIndex == 0) { beginQuery = "SELECT * FROM moviedata;"; } else { beginQuery = "SELECT * FROM moviedata WHERE " + query + ";"; } //Put code here to query MySqlConnection connect = new MySqlConnection("SERVER=localhost; user id=root; password=root; database=movies"); MySqlCommand cmd = new MySqlCommand(beginQuery); cmd.CommandType = CommandType.Text; cmd.Connection = connect; connect.Open(); try { MySqlDataReader dr; dr = cmd.ExecuteReader(); while (dr.Read()) { this.Output.Items.Add(new MyItem { Title = dr.GetString(0), Year = dr.GetString(1), Owner = dr.GetString(2), Genre = dr.GetString(3), MPAA_Rating = dr.GetString(4), Owner_Rating = dr.GetString(5), RT = dr.GetString(6) }); } dr.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (connect.State == ConnectionState.Open) { connect.Close(); } } //Output.Items.Add(beginQuery); }
private void ListMode_Checked(object sender, RoutedEventArgs e) { TitleSearch.Clear(); ListMode.IsChecked = true; }