/// <summary>
/// Concatenate filter value in string
/// </summary>
/// <param name="reportSearch">Filters value</param>
/// <returns>Filters values concatenated into a string</returns>
private string getAnnouncementAnalysisReportWhereClause(ReportSearch reportSearch)
{
string whereClause = string.Empty;
if (!string.IsNullOrEmpty(reportSearch.VideoVersion))
{
whereClause = whereClause + " AND A.VideoVersion= '" + reportSearch.VideoVersion + "'";
}
if (!string.IsNullOrEmpty(reportSearch.LocalEdit))
{
if (string.IsNullOrEmpty(reportSearch.LocalEdit))
{
whereClause = whereClause + " AND A.LocalEdit ='Yes' AND A.LocalEdit ='No' ";
}
if (reportSearch.LocalEdit.Equals("Yes"))
{
whereClause = whereClause + " AND A.LocalEdit ='Yes' ";
}
if (reportSearch.LocalEdit.Equals("No"))
{
whereClause = whereClause + "AND A.LocalEdit ='No' ";
}
}
if ((reportSearch.CreatedStartDate != null) && !(reportSearch.CreatedStartDate.Equals(DateTime.MinValue)) && (reportSearch.CreatedEndDate != null && !(reportSearch.CreatedEndDate.Equals(DateTime.MinValue))))
{
string endDate = reportSearch.CreatedEndDate.AddDays(1).Date.ToString("MM-dd-yyyy");
whereClause = whereClause + " AND A.FirstAnnouncedDate >= '" + reportSearch.CreatedStartDate.ToString("MM-dd-yyyy") + "' and A.FirstAnnouncedDate < '" + endDate + "' ";
}
return(whereClause);
}
public List <PASSENGER_EX> QueryPassenger(ReportSearch search)
{
string sql = @"SELECT SUM(CUST_NUMBER) AS count,CONVERT(varchar(10),Dates,120) Dates
FROM (SELECT CUST_NUMBER, DATEADD(day, DATEDIFF(day, 0, TRANS_DATE), 0) AS Dates
FROM ORDER_HEAD where (PAY_STATUS=1 or PAY_STATUS=2) ";
//if (search.store_id > 0)
//{
// sql += " and store_id= " + search.store_id;
//}
if (!string.IsNullOrEmpty(search.STORE))
{
sql += " and store_id= " + search.STORE;
}
if (!string.IsNullOrEmpty(search.BEGIN_DATE))
{
sql += " and trans_date>= '" + search.BEGIN_DATE + "'";
}
if (!string.IsNullOrEmpty(search.BEGIN_DATE))
{
sql += " and trans_date<='" + search.END_DATE + "'";
}
sql += " and CUST_NUMBER is not null) AS MM GROUP BY Dates";
return(base.Query <PASSENGER_EX>(sql, new { }));
}
/// <summary>
/// Exports report in .xlsx file
/// </summary>
/// <param name="reportSearch">reportSearch contains which report to export and it filters values</param>
/// <remark>This method export Announcement analysis report,Finance report,Cancel avail report,Date change report</remark>>
/// <returns>FileResult which help to download report in browser</returns>
public ActionResult Reports1(ReportSearch reportSearch)
{
string path = string.Empty;
string downloadFile = string.Empty;
DownLoadFile downLoadFile = null;
IExportReportService exportService = new ExportReportService();
if (reportSearch.ReportTitleID.Equals(1))
{
int recordCount = exportService.GetAnnouncementAnalysisReportRecordCount(reportSearch);
if (recordCount > App.Config.MaxAnnouncementAnalysisDownloadRecordLimit)
{
TempData["ErrorMsg"] = string.Format("There are {0} records that you are trying to download. Record count should not exceed {1} records.", recordCount, App.Config.MaxAnnouncementAnalysisDownloadRecordLimit);
return(RedirectToAction("Reports", "Report", new { @reportSearch = reportSearch }));
}
downLoadFile = exportService.GenerateAnnouncementAnalysisReport(reportSearch);
}
else if (reportSearch.ReportTitleID.Equals(2))
{
downLoadFile = exportService.GenerateCancelAvailsReport(reportSearch);
}
else if (reportSearch.ReportTitleID.Equals(3))
{
downLoadFile = exportService.GenerateFinanceReport(reportSearch);
}
else if (reportSearch.ReportTitleID.Equals(4))
{
downLoadFile = exportService.GenerateAnnouncementChangeReport(reportSearch);
}
return(File(downLoadFile.bufferByte, "application/vnd.ms-excel", downLoadFile.FileName));
}
/// <summary>
/// Export Cancel Avails Report in .xlsx for selected filter criteria
/// </summary>
/// <param name="reportSearch">Filters values</param>
/// <returns>File name and Byte stream</returns>
public DownLoadFile GenerateCancelAvailsReport(ReportSearch reportSearch)
{
//string cancelAvailsSourceFile = ConfigurationManager.AppSettings["cancelAvailsSourceFile"];
string cancelAvailsSourceFile = App.Config.CancelReportExportFilePath;
//string cancelAvailsCopyFile = ConfigurationManager.AppSettings["cancelAvailsCopyFile"];
string cancelAvailsCopyFile = App.Config.CancelReportExportCopyFilePath;
string downloadedFileName = "Cancel Avails 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(cancelAvailsSourceFile, cancelAvailsCopyFile, true);
_repository.ExportCancelAvailsReport(reportSearch);
File.Copy(cancelAvailsSourceFile, downloadFile, true);
if (File.Exists(cancelAvailsSourceFile))
{
File.Delete(cancelAvailsSourceFile);
}
File.Copy(cancelAvailsCopyFile, cancelAvailsSourceFile, true);
if (File.Exists(cancelAvailsCopyFile))
{
File.Delete(cancelAvailsCopyFile);
}
DownLoadFile download = DownloadFile(downloadFile, downloadedFileName);
return(download);
}
/// <summary>
/// Export Announcement Analysis Report in .xlsx for selected filter criteria
/// </summary>
/// <param name="reportSearch">Filters values</param>
/// <returns>File name and Bytestream</returns>
public DownLoadFile GenerateAnnouncementAnalysisReport(ReportSearch reportSearch)
{
//string annoucementSourceFile = ConfigurationManager.AppSettings["annoucementSourceFile"];
string annoucementSourceFile = App.Config.AnnouncementReportExportFilePath;
//string annoucementCopyFile = ConfigurationManager.AppSettings["annoucementCopyFile"];
string annoucementCopyFile = App.Config.AnnouncementReportExportCopyFilePath;
string downloadedFileName = "Annoucement Analysis 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(annoucementSourceFile, annoucementCopyFile, true);
//_repository.ExportAnnouncementAnalysisReport(reportSearch);
string whereClause = this.getAnnouncementAnalysisReportWhereClause(reportSearch);
_repository.ExportAnnouncementAnalysisReport(whereClause);
File.Copy(annoucementSourceFile, downloadFile, true);
if (File.Exists(annoucementSourceFile))
{
File.Delete(annoucementSourceFile);
}
File.Copy(annoucementCopyFile, annoucementSourceFile, true);
if (File.Exists(annoucementCopyFile))
{
File.Delete(annoucementCopyFile);
}
DownLoadFile download = DownloadFile(downloadFile, downloadedFileName);
return(download);
}
public FileResult Reports1(ReportSearch reportSearch)
{
string path = string.Empty;
string downloadFile = string.Empty;
DownLoadFile downLoadFile = null;
ExportReportService exportService = new ExportReportService();
if (reportSearch.ReportTitleID.Equals(1))
{
downLoadFile = exportService.GenerateAnnouncementAnalysisReport(reportSearch);
}
else if (reportSearch.ReportTitleID.Equals(2))
{
downLoadFile = exportService.GenerateCancelAvailsReport(reportSearch);
}
else if (reportSearch.ReportTitleID.Equals(3))
{
downLoadFile = exportService.GenerateFinanceReport(reportSearch);
}
else if (reportSearch.ReportTitleID.Equals(4))
{
downLoadFile = exportService.GenerateAnnouncementChangeReport(reportSearch);
}
//file = TempData["fileName"].ToString();
//path = ConfigurationManager.AppSettings["directoryPath"];
//path = App.Config.ReportDirectoryPath;
//string fullPath = string.Format(path, file);
return(File(downLoadFile.bufferByte, "application/vnd.ms-excel", downLoadFile.FileName));
//TempData["fileName"] = file;
//return RedirectToAction("Reports");
}
/// <summary>
/// Populates report values
/// </summary>
/// <returns>ReportSearch</returns>
public ReportSearch GetSearchValues()
{
ReportSearch reportsSearch = _repository.GetSearchValue();
reportsSearch.AnnouncementProcessedDate.ForEach(x =>
x.Date = ((x.AnnouncemntDate.ToString("dd MMM yyyy")) + " " + (!string.IsNullOrEmpty(x.AnnouncementFileName)? "(" + x.AnnouncementFileName + ")" : string.Empty) + " " + string.Format("{0:hh:mm tt}", x.AnnouncemntDate)));
return(reportsSearch);
}
// GET: Reports
public ActionResult InvoiceAging(ReportSearch searchModel)
{
var allQuotes = quoteService.FilterIPQuotes().ToList();
searchModel.lstQuotes =
allQuotes
.Where(q => q.quote_date_created >= searchModel.startDate && q.quote_date_created <= searchModel.endDate && q.type == searchModel.Type);
searchModel.ReportVisible = true;
return(View(searchModel));
}
public ActionResult InvoiceAging()
{
ReportSearch searchModel = new ReportSearch();
searchModel.startDate = DateTime.MinValue;
searchModel.endDate = DateTime.MinValue;
searchModel.ReportVisible = false;
searchModel.lstQuotes = new List <IPQuotesModel>();
searchModel.Type = 0;
return(View(searchModel));
}
public ActionResult PaymentHistory()
{
ReportSearch searchModel = new ReportSearch();
searchModel.startDate = DateTime.MinValue;
searchModel.endDate = DateTime.MinValue;
searchModel.ReportVisible = false;
searchModel.lstQuotes = new List <IPQuotesModel>();
searchModel.lstPayments = new List <IPPaymentsModel>();
searchModel.Type = 2;
return(View(searchModel));
}
private void ReportSearch_MouseDown(object sender, System.Windows.Input.MouseButtonEventArgs e)
{
ReportSearch reportSearch = new ReportSearch();
reportSearch.Owner = mainWindow;
reportSearch.WindowStartupLocation = WindowStartupLocation.CenterOwner;
reportSearch.Top = 200;
reportSearch.ShowDialog();
grid_onlyPlan.Visibility = Visibility.Visible;
grid_onlyWorkdone.Visibility = Visibility.Hidden;
grid_both.Visibility = Visibility.Hidden;
}
public ActionResult Reports(ReportSearch reportSearch)
{
ReportsModel reportModel = new ReportsModel();
ExportReportService _report = new ExportReportService();
//reportModel.Name = Session["Name"].ToString();
var searchValues = _report.GetSearchValues();
reportModel.ReportSearch.ContentDistributors = searchValues.ContentDistributors;
reportModel.ReportSearch.ContentProviders = searchValues.ContentProviders;
reportModel.ReportSearch.LocalEdits = searchValues.LocalEdits;
reportModel.ReportSearch.AnnouncementProcessedDate = searchValues.AnnouncementProcessedDate;
return(View(reportModel));
}
public ActionResult PaymentHistory(ReportSearch searchModel)
{
if (searchModel.startDate.Equals(DateTime.MinValue) || searchModel.endDate.Equals(DateTime.MinValue))
{
return(View(searchModel));
}
else
{
var allQuotes = quoteService.FilterIPQuotes().ToList();
searchModel.lstQuotes =
allQuotes
.Where(q => q.quote_date_created >= searchModel.startDate && q.quote_date_created <= searchModel.endDate && q.type == searchModel.Type);
searchModel.ReportVisible = true;
return(View(searchModel));
}
}
public ReportSearch GetSearchValue()
{
ReportSearch reportsSearch = new ReportSearch();
var _context = new DeluxeOrderManagementEntities();
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 localEdits = new List <string>()
{
"", "Yes", "No"
};
var announcementProcessedDate = (from job in _context.JOBS
join jobItems in _context.Jobs_Items
on job.Id equals jobItems.JobId
where job.JobType == FtpConfigurationType.Announcement.ToString() &&
job.Status == true
orderby job.Id descending
select new AnnouncementProcessed()
{
AnnouncemntDate = jobItems.StartDate.Value,
JobId = jobItems.JobId.ToString(),
AnnouncementFileName = job.FileName
}
).ToList().GroupBy(x => x.JobId);
var announcementProcessed = announcementProcessedDate.Select(x => new AnnouncementProcessed
{
AnnouncementFileName = x.Select(m => m.AnnouncementFileName).FirstOrDefault(),
JobId = x.Select(m => m.JobId).FirstOrDefault(),
AnnouncemntDate = x.Select(m => m.AnnouncemntDate).FirstOrDefault(),
}).ToList();
reportsSearch.LocalEdits = localEdits;
reportsSearch.ContentProviders = contentProviders;
reportsSearch.ContentDistributors = contentDistributors;
reportsSearch.AnnouncementProcessedDate = announcementProcessed;
return(reportsSearch);
}
public void ExportAnnouncementAnalysisReport(ReportSearch reportSearch)
{
GetConnection();
cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandText = "usp_ExportAnnoucementAnalysisReport";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
whereClause = string.Empty;
if (!string.IsNullOrEmpty(reportSearch.VideoVersion))
{
whereClause = whereClause + " AND A.VideoVersion= '" + reportSearch.VideoVersion + "'";
}
if (!string.IsNullOrEmpty(reportSearch.LocalEdit))
{
if (string.IsNullOrEmpty(reportSearch.LocalEdit))
{
whereClause = whereClause + " AND A.LocalEdit ='Yes' AND A.LocalEdit ='No' ";
}
if (reportSearch.LocalEdit.Equals("Yes"))
{
whereClause = whereClause + " AND A.LocalEdit ='Yes' ";
}
if (reportSearch.LocalEdit.Equals("No"))
{
whereClause = whereClause + "AND A.LocalEdit ='No' ";
}
}
if ((reportSearch.CreatedStartDate != null) && !(reportSearch.CreatedStartDate.Equals(DateTime.MinValue)) && (reportSearch.CreatedEndDate != null && !(reportSearch.CreatedEndDate.Equals(DateTime.MinValue))))
{
string endDate = reportSearch.CreatedEndDate.AddDays(1).Date.ToString("MM-dd-yyyy");
whereClause = whereClause + " AND A.FirstAnnouncedDate >= '" + reportSearch.CreatedStartDate.ToString("MM-dd-yyyy") + "' and A.FirstAnnouncedDate<= '" + endDate + "' ";
}
//if ((reportSearch.CreatedEndDate != null && !(reportSearch.CreatedEndDate.Equals(DateTime.MinValue))))
//{
// reportSearch.CreatedEndDate = reportSearch.CreatedEndDate.AddDays(1);
// whereClause = whereClause + "AND A.FirstAnnouncedDate >= '" + reportSearch.CreatedStartDate.ToString("MM-dd-yyyy") + "' and A.FirstAnnouncedDate<= '" + reportSearch.CreatedEndDate.ToString("MM-dd-yyyy") + "' " ;
//}
cmd.Parameters.Add("@whereClause", SqlDbType.VarChar).Value = whereClause;
cmd.ExecuteNonQuery();
}
public void ExportAnnouncementChangeReport(ReportSearch reportSearch)
{
GetConnection();
cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandText = "usp_ExportAnnouncementChangeReport";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
JobId = 0;
if (!string.IsNullOrEmpty(reportSearch.JobId))
{
JobId = Convert.ToInt32(reportSearch.JobId);
}
cmd.Parameters.Add("@JobId", SqlDbType.Int).Value = JobId;
cmd.ExecuteNonQuery();
CloseConnection();
}
public void ExportFinanceReport(ReportSearch reportSearch)
{
GetConnection();
cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandText = "usp_ExportFinanceReport";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
whereClause = string.Empty;
if (((reportSearch.ImportStartDate != null && !(reportSearch.ImportStartDate.Equals(DateTime.MinValue))) && ((reportSearch.ImportEndDate) != null) || !(reportSearch.ImportEndDate.Equals(DateTime.MinValue))))
{
string endDate = reportSearch.ImportEndDate.AddDays(1).Date.ToString("MM-dd-yyyy");
whereClause = " and VQ.ImportedDate >= '" + reportSearch.ImportStartDate.ToString("MM-dd-yyyy") + "' and VQ.ImportedDate <= '" + endDate + "' ";
}
cmd.Parameters.Add("@whereClause", SqlDbType.VarChar).Value = whereClause;
cmd.ExecuteNonQuery();
CloseConnection();
}
public void ExportCancelAvailsReport(ReportSearch reportSearch)
{
GetConnection();
cmd = new SqlCommand();
DataSet dataset = new DataSet();
cmd.Connection = connect;
cmd.CommandText = "usp_ExportCancelAvailsReport";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
whereClause = string.Empty;
if ((reportSearch.CreatedStartDate != null && !(reportSearch.CreatedStartDate.Equals(DateTime.MinValue)) && (reportSearch.CreatedEndDate) != null && !(reportSearch.CreatedEndDate.Equals(DateTime.MinValue))))
{
string endDate = reportSearch.CreatedEndDate.AddDays(1).Date.ToString("MM-dd-yyyy");
whereClause = " and AG.CancellationDate >= '" + reportSearch.CreatedStartDate.ToString("MM-dd-yyyy") + "' and AG.CancellationDate<= '" + endDate + "' ";
}
cmd.Parameters.Add("@whereClause", SqlDbType.VarChar).Value = whereClause;
cmd.ExecuteNonQuery();
CloseConnection();
}
public IEnumerable <Supply> GetAllSuppliesBetweenTheSpecifiedDatesForAParticularSupplier(ReportSearch searchDates, string supplierId)
{
return(_reportService.GetAllSuppliesBetweenTheSpecifiedDatesForAParticularSupplier(searchDates.FromDate, searchDates.ToDate, supplierId));
}
/// <summary>
/// Announcement Analysis Report Record Count for selected filter criteria
/// </summary>
/// <param name="reportSearch">Filters value</param>
/// <returns>Record count</returns>
public int GetAnnouncementAnalysisReportRecordCount(ReportSearch reportSearch)
{
string whereClause = getAnnouncementAnalysisReportWhereClause(reportSearch);
return(_repository.GetAnnouncementAnalysisReportRecordCount(whereClause));
}
public IEnumerable <Transaction> GetAllTransactionsBetweenTheSpecifiedDates(ReportSearch search)
{
return(_reportService.GetAllTransactionsBetweenTheSpecifiedDates(search.FromDate, search.ToDate));
}
public IEnumerable <Supply> GetAllSuppliesBetweenTheSpecifiedDates(ReportSearch searchDates)
{
return(_reportService.GetAllSuppliesBetweenTheSpecifiedDates(searchDates.FromDate, searchDates.ToDate));
}
public IEnumerable <AccountTransactionActivity> GetAllAccountTransactionsBetweenTheSpecifiedDates(ReportSearch searchDates)
{
return(_reportService.GetAllAccountTransactionsBetweenTheSpecifiedDates(searchDates.FromDate, searchDates.ToDate, searchDates.BranchId, searchDates.SupplierId));
}
