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