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