Exemplo n.º 1
0
        /// <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"));
            }
        }
Exemplo n.º 2
0
        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));
        }
Exemplo n.º 3
0
        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);
        }
Exemplo n.º 4
0
        /// <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);
        }
Exemplo n.º 5
0
        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));
        }
Exemplo n.º 6
0
        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));
        }
Exemplo n.º 7
0
        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);
        }
Exemplo n.º 8
0
        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);
        }
Exemplo n.º 12
0
 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"));
     }
 }
Exemplo n.º 13
0
 public ActionResult Index(TitleSearch model)
 {
     return(RedirectToAction("Titles", new { term = model.TitleName, maxResults = model.MaxResults }));
 }
Exemplo n.º 14
0
 public IActionResult Get([FromQuery] TitleSearch search, [FromServices] IGetTitles query)
 {
     return(Ok(_executor.ExecuteQuery(query, search)));
 }
Exemplo n.º 15
0
        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);
        }
Exemplo n.º 16
0
 private void ListMode_Checked(object sender, RoutedEventArgs e)
 {
     TitleSearch.Clear();
     ListMode.IsChecked = true;
 }