/// <summary>
/// Populate all dropdown value on VID page and render view
/// </summary>
/// <returns>VID Search</returns>
public VIDSearch GetSearchValue()
{
var _context = new DeluxeOrderManagementEntities();
var titleName = (from vid in _context.VIDs
where !string.IsNullOrEmpty(vid.TitleName)
select vid.TitleName
).Distinct().OrderBy(x => x).ToList();
var vendorId = (from vid in _context.VIDs
where !string.IsNullOrEmpty(vid.VendorId)
select vid.VendorId
).Distinct().OrderBy(x => x).ToList();
var videoVersion = (from vid in _context.VIDs
where !string.IsNullOrEmpty(vid.VideoVersion)
select vid.VideoVersion
).Distinct().OrderBy(x => x).ToList();
var vidSatus = (from vid in _context.VIDs
where !string.IsNullOrEmpty(vid.VIDStatus)
select vid.VIDStatus
).Distinct().OrderBy(x => x).ToList();
VIDSearch vidSeach = new VIDSearch();
vidSeach.TitleList = titleName.Select(x => new KeyValue {
Text = x, Value = x
}).ToList();
vidSeach.VendorIdList = vendorId.Select(x => new KeyValue {
Text = x, Value = x
}).ToList();
vidSeach.VideoVersionList = videoVersion.Select(x => new KeyValue {
Text = x, Value = x
}).ToList();
vidSeach.ListVidStatus = vidSatus.Select(x => new KeyValue {
Text = x, Value = x
}).ToList();
return(vidSeach);
}
/// <summary>
/// Export VID to .xlsx file
/// </summary>
/// <param name="vidSearch">VidSearch contains the filter value to export VID. If vidSearch is null then all VID data get exported</param>
/// <returns>FileResult which help to download file in browser</returns>
public ActionResult Export(VIDSearch vidSearch)
{
var vid = new VIDMgt();
var vidSearchValues = _service.GetSearchValue();
vid.VIDSearch = vidSearchValues;
DownLoadFile downloadFile = _export.ExportVidReport(vidSearch);
var vidModel = _service.GetDropDownValue();
vid.VID = vidModel;
vid.VIDSearch = vidSearch;
//return View("Vid", vid);
string path = App.Config.ReportDirectoryPath;
return(File(downloadFile.bufferByte, "application/vnd.ms-excel", downloadFile.FileName));
}
/// <summary>
/// Export VID Report in .xlsx for selected filter criteria
/// </summary>
/// <param name="reportSearch">Filters values</param>
/// <returns>File name and Byte stream</returns>
public DownLoadFile ExportVidReport(VIDSearch vidSearch)
{
string whrSQLquery = string.Empty;
if (vidSearch.TitleName != null && vidSearch.TitleName.Any())
{
var titleNames = vidSearch.TitleName.Select(x => x.Replace("'", "\''")).ToList();
string concattitle = titleNames.Aggregate((current, next) => current + "', '" + next);
concattitle = string.Format("{0}{1}{0}", "'", concattitle);
whrSQLquery += string.IsNullOrEmpty(whrSQLquery) ? string.Empty : " AND ";
whrSQLquery += " TitleName IN (" + concattitle + ")";
//whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " T.WBTerritory ='" + orderSearch.Territory + "' " : whrSQLquery + " AND T.WBTerritory = '" + orderSearch.Territory + "' ";
}
if (vidSearch.VideoVersion != null && vidSearch.VideoVersion.Any())
{
string videoVersion = vidSearch.VideoVersion.Aggregate((current, next) => current + "', '" + next);
videoVersion = string.Format("{0}{1}{0}", "'", videoVersion);
whrSQLquery += string.IsNullOrEmpty(whrSQLquery) ? string.Empty : " AND ";
whrSQLquery += " VideoVersion IN (" + videoVersion + ")";
//whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " T.WBTerritory ='" + orderSearch.Territory + "' " : whrSQLquery + " AND T.WBTerritory = '" + orderSearch.Territory + "' ";
}
if (vidSearch.VendorId != null && vidSearch.VendorId.Any())
{
string vendorId = vidSearch.VendorId.Aggregate((current, next) => current + "', '" + next);
vendorId = string.Format("{0}{1}{0}", "'", vendorId);
whrSQLquery += string.IsNullOrEmpty(whrSQLquery) ? string.Empty : " AND ";
whrSQLquery += " VendorId IN (" + vendorId + ")";
//whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " T.WBTerritory ='" + orderSearch.Territory + "' " : whrSQLquery + " AND T.WBTerritory = '" + orderSearch.Territory + "' ";
}
if (!string.IsNullOrEmpty(vidSearch.VidStatus))
{
whrSQLquery = string.IsNullOrEmpty(whrSQLquery) ? " VIDStatus ='" + vidSearch.VidStatus + "' " : whrSQLquery + " AND VIDStatus = '" + vidSearch.VidStatus + "' ";
}
if (!string.IsNullOrEmpty(whrSQLquery))
{
whrSQLquery = "where" + whrSQLquery;
}
string VidReportSourceFile = App.Config.VidReportExportFilePath;
string VidReportCopyFile = App.Config.VidReportExportCopyFilePath;
string downloadedFileName = "Vid Report" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + ".xlsx";
string path = App.Config.ReportDirectoryPath;
string downloadFile = string.Format(path, downloadedFileName);
File.Copy(VidReportSourceFile, VidReportCopyFile, true);
_repository.ExportVidReport(whrSQLquery);
File.Copy(VidReportSourceFile, downloadFile, true);
if (File.Exists(VidReportSourceFile))
{
File.Delete(VidReportSourceFile);
}
File.Copy(VidReportCopyFile, VidReportSourceFile, true);
if (File.Exists(VidReportCopyFile))
{
File.Delete(VidReportCopyFile);
}
DownLoadFile download = DownloadFile(downloadFile, downloadedFileName);
return(download);
}
