private void CreateExcel()
{
if (dlgSave.ShowDialog() == DialogResult.OK)
{
btnPrint.Enabled = false;
try
{
using (var reportService = ReportServiceClient.CreateInstance())
{
try
{
DateTime dDate = new DateTime((cmbYear.SelectedItem as IdValue).Id, (cmbMonth.SelectedItem as IdValue).Id, 1);
var idVal = (cbUserList.SelectedItem as IdValue);
var reportData = reportService.MonthlyReport(SqlDataHandler.GetConnectionString(), dDate, rdCompleted.Checked, idVal != null ? idVal.Id : (int?)null);
File.WriteAllBytes(dlgSave.FileName, reportData);
Process.Start(dlgSave.FileName);
}
catch (Exception ex)
{
Controller.HandleError(ex);
Controller.ShowMessage(ex.GetType().ToString());
}
}
}
finally
{
btnPrint.Enabled = true;
}
}
}
private void CreateReport()
{
if (dlgSave.ShowDialog() != DialogResult.OK)
{
return;
}
byte[] reportData = null;
try
{
var building = cmbBuilding.SelectedItem as Building;
using (var reportService = ReportServiceClient.CreateInstance())
{
reportData = reportService.InsuranceSchedule(SqlDataHandler.GetConnectionString(), building.ID);
if (reportData != null)
{
File.WriteAllBytes(dlgSave.FileName, reportData);
Process.Start(dlgSave.FileName);
}
else
{
MessageBox.Show("No data for report.");
}
}
}
catch (Exception exp)
{
Controller.HandleError(exp);
}
}
private void LoadRequisitions()
{
this.Cursor = Cursors.WaitCursor;
var building = cmbBuilding.SelectedItem as Building;
try
{
_Data = new List <PastelMaintenanceTransaction>();
using (var reportService = ReportServiceClient.CreateInstance())
{
var items = reportService.MissingMaintenanceRecordsGet(SqlDataHandler.GetConnectionString(), building.ID);
if (items == null || items.Length == 0)
{
Controller.ShowMessage("No transactions found", "Warning");
}
else
{
_Data = items.ToList();
BindDataGrid();
}
}
}
catch (Exception e)
{
var settings = dataContext.tblSettings.FirstOrDefault();
Controller.HandleError("Error loading requisitions, please confirm the ODBC setup for \n" + building.DataPath + "\n as well as \n" + settings.trust + "\n" +
e.Message);
}
finally
{
this.Cursor = Cursors.Default;
}
}
private void button1_Click(object sender, EventArgs e)
{
if (dlgSave.ShowDialog() == DialogResult.OK)
{
this.Cursor = Cursors.WaitCursor;
button1.Enabled = false;
try
{
using (var reportService = ReportServiceClient.CreateInstance())
{
DateTime dtFrom = new DateTime((cmbFromYear.SelectedItem as IdValue).Id, (cmbFromMonth.SelectedItem as IdValue).Id, 1);
DateTime dtTo = new DateTime((cmbToYear.SelectedItem as IdValue).Id, (cmbToMonth.SelectedItem as IdValue).Id, 1);
if (dtFrom > dtTo)
{
Controller.HandleError("Invalid date range", "Supplier Report");
return;
}
var building = cmbBuilding.SelectedItem as Building;
var reportData = reportService.SupplierReport(SqlDataHandler.GetConnectionString(), dtFrom, dtTo, building == null || building.ID == 0 ? (int?)null : building.ID, _SelectedSupplier == null ? (int?)null : _SelectedSupplier.id);
if (reportData == null)
{
Controller.HandleError("No data found", "Supplier Report");
return;
}
File.WriteAllBytes(dlgSave.FileName, reportData);
Process.Start(dlgSave.FileName);
}
}
finally
{
this.Cursor = Cursors.Default;
button1.Enabled = true;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (dlgSave.ShowDialog() == DialogResult.OK)
{
this.Cursor = Cursors.WaitCursor;
button1.Enabled = false;
try
{
using (var reportService = ReportServiceClient.CreateInstance())
{
DateTime startDate = new DateTime((cmbFromYear.SelectedItem as IdValue).Id, (cmbFromMonth.SelectedItem as IdValue).Id, 1);
DateTime endDate = new DateTime((cmbToYear.SelectedItem as IdValue).Id, (cmbToMonth.SelectedItem as IdValue).Id, 1).AddMonths(1).AddSeconds(-1);
MaintenanceReportType repType;
if (rbDetailed.Checked)
{
repType = MaintenanceReportType.DetailedReport;
}
else if (rbDetailWithDocs.Checked)
{
repType = MaintenanceReportType.DetailedReportWithSupportingDocuments;
}
else
{
repType = MaintenanceReportType.SummaryReport;
}
var building = cmbBuilding.SelectedItem as Building;
var reportData = reportService.MaintenanceReport(SqlDataHandler.GetConnectionString(), repType, startDate, endDate, building.ID, building.Name, (cmbBuilding.SelectedItem as Building).DataPath);
if (reportData == null)
{
Controller.HandleError("No data found for " + startDate.ToString("MMM yyyy") + " - " + endDate.ToString("MMM yyyy"), "Maintenance Report");
return;
}
if (repType == MaintenanceReportType.DetailedReportWithSupportingDocuments)
{
byte[] combinedReport = null;
using (var dataContext = SqlDataHandler.GetDataContext())
{
var documentIds = (from m in dataContext.MaintenanceSet
from d in m.MaintenanceDocuments
where m.BuildingMaintenanceConfiguration.BuildingId == building.ID &&
m.Requisition.trnDate >= startDate && m.Requisition.trnDate <= endDate
orderby m.DateLogged
select d.id).ToList();
var reqDocIds = (from m in dataContext.MaintenanceSet
from d in m.Requisition.Documents
where m.BuildingMaintenanceConfiguration.BuildingId == building.ID &&
m.Requisition.trnDate >= startDate && m.Requisition.trnDate <= endDate
orderby m.DateLogged
select d.id).ToList();
using (MemoryStream ms = new MemoryStream())
{
using (Document doc = new Document())
{
using (PdfCopy copy = new PdfCopy(doc, ms))
{
doc.Open();
AddPdfDocument(copy, reportData);
foreach (var documentId in reqDocIds)
{
var document = dataContext.RequisitionDocumentSet.Where(a => a.id == documentId).Select(a => a.FileData).Single();
AddPdfDocument(copy, document);
}
foreach (var documentId in documentIds)
{
var document = dataContext.MaintenanceDocumentSet.Where(a => a.id == documentId).Select(a => a.FileData).Single();
AddPdfDocument(copy, document);
}
}
}
combinedReport = ms.ToArray();
}
}
File.WriteAllBytes(dlgSave.FileName, combinedReport);
}
else
{
File.WriteAllBytes(dlgSave.FileName, reportData);
}
Process.Start(dlgSave.FileName);
}
}
finally
{
this.Cursor = Cursors.Default;
button1.Enabled = true;
}
}
}
private void btnMaintenance_Click(object sender, EventArgs e)
{
button1.Enabled = false;
try
{
var reportFileName = Path.GetTempFileName();
using (var reportService = ReportServiceClient.CreateInstance())
{
DateTime startDate = new DateTime((cmbYear.SelectedItem as IdValue).Id, (cmbMonth.SelectedItem as IdValue).Id, 1);
DateTime endDate = startDate.AddMonths(1).AddDays(-1);
MaintenanceReportType repType;
if (rbDetailed.Checked)
{
repType = MaintenanceReportType.DetailedReport;
}
else if (rbDetailWithDocs.Checked)
{
repType = MaintenanceReportType.DetailedReportWithSupportingDocuments;
}
else
{
repType = MaintenanceReportType.SummaryReport;
}
var building = cmbBuilding.SelectedItem as Building;
var reportData = reportService.MaintenanceReport(SqlDataHandler.GetConnectionString(), repType, startDate, endDate, building.ID, building.Name, (cmbBuilding.SelectedItem as Building).DataPath);
if (reportData == null)
{
Controller.HandleError("No data found for " + startDate.ToString("MMM yyyy") + " - " + endDate.ToString("MMM yyyy"), "Maintenance Report");
return;
}
if (repType == MaintenanceReportType.DetailedReportWithSupportingDocuments)
{
byte[] combinedReport = null;
using (var dataContext = SqlDataHandler.GetDataContext())
{
var documentIds = (from m in dataContext.MaintenanceSet
from d in m.MaintenanceDocuments
where m.BuildingMaintenanceConfiguration.BuildingId == building.ID &&
m.Requisition.trnDate >= startDate && m.Requisition.trnDate <= endDate
orderby m.DateLogged
select d.id).ToList();
var reqDocIds = (from m in dataContext.MaintenanceSet
from d in m.Requisition.Documents
where m.BuildingMaintenanceConfiguration.BuildingId == building.ID &&
m.Requisition.trnDate >= startDate && m.Requisition.trnDate <= endDate
orderby m.DateLogged
select d.id).ToList();
using (MemoryStream ms = new MemoryStream())
{
using (Document doc = new Document())
{
using (PdfCopy copy = new PdfCopy(doc, ms))
{
doc.Open();
AddPdfDocument(copy, reportData);
foreach (var documentId in reqDocIds)
{
var document = dataContext.RequisitionDocumentSet.Where(a => a.id == documentId).Select(a => a.FileData).Single();
AddPdfDocument(copy, document);
}
foreach (var documentId in documentIds)
{
var document = dataContext.MaintenanceDocumentSet.Where(a => a.id == documentId).Select(a => a.FileData).Single();
AddPdfDocument(copy, document);
}
}
}
combinedReport = ms.ToArray();
}
}
File.WriteAllBytes(reportFileName, combinedReport);
}
else
{
File.WriteAllBytes(reportFileName, reportData);
}
var fileDate = DateTime.Now;
var totalPages = GetTotalPages(reportFileName);
_TableOfContents.Insert(0, new TableOfContentForPdfRecord()
{
Path = reportFileName,
File = Path.GetFileName(reportFileName),
Description = GetDescriptionList().Where(a => a.StartsWith("Maintenance")).FirstOrDefault(),
Position = -1,
Pages = totalPages,
FileDate = fileDate,
IsTempFile = true,
IncludeInTOC = true
});
RefreshTOC();
}
}
finally
{
this.Cursor = Cursors.Default;
button1.Enabled = true;
}
}
private void button1_Click(object sender, EventArgs e)
{
if (dlgSave.ShowDialog() == DialogResult.OK)
{
button1.Enabled = false;
try
{
lbProgress.Text = "Starting";
var buildingList = _Buildings.Where(a => a.ID > 0).ToList();
if ((cmbBuilding.SelectedItem as Building).ID != 0)
{
buildingList = _Buildings.Where(a => a.ID == (cmbBuilding.SelectedItem as Building).ID).ToList();
}
lbStatus.Items.Clear();
int errorCount = 0;
DateTime dDate = new DateTime((cmbYear.SelectedItem as IdValue).Id, (cmbMonth.SelectedItem as IdValue).Id, 1);
List <ExcelLineItem> compiledList = new List <ExcelLineItem>();
int buildingNum = 1;
using (var reportService = ReportServiceClient.CreateInstance())
{
foreach (var building in buildingList)
{
lbProgress.Text = "Processing " + buildingNum.ToString() + "/" + buildingList.Count().ToString() + " => " + building.Name;
buildingNum++;
Application.DoEvents();
try
{
var items = reportService.RunDebitOrderForBuilding(SqlDataHandler.GetConnectionString(), building.ID, dDate, cbShowBreakdown.Checked);
if (items != null && items.Length > 0)
{
compiledList.AddRange(items.Select(a => new ExcelLineItem(a, building.Abbr, building.Name)).ToList());
}
lbStatus.Items.Insert(0, building.Name + " => " + items.Length.ToString() + " records");
}
catch (Exception ex)
{
lbStatus.Items.Insert(0, "ERROR => " + building.Name + " " + ex.Message);
errorCount++;
}
Application.DoEvents();
}
lbProgress.Text = "Compiling Excel File " + compiledList.Count().ToString() + " records total";
Application.DoEvents();
if (errorCount > 0)
{
Controller.HandleError("Warning " + errorCount.ToString() + " buildings that could not be processed.", "Warning");
}
byte[] reportData = null;
reportData = ExportDebitOrder(compiledList, cbShowBreakdown.Checked);
File.WriteAllBytes(dlgSave.FileName, reportData);
Process.Start(dlgSave.FileName);
lbProgress.Text = "Completed " + compiledList.Count().ToString() + " records total";
Application.DoEvents();
}
}
finally
{
button1.Enabled = true;
}
}
}
public List <Transaction> LoadTransactions(Building building, Customer customer, DateTime transDate, out double totalDue, out String trnMsg)
{
trnMsg = "";
PeriodItem startPeriod;
PeriodItem endPeriod;
try
{
using (var reportService = ReportServiceClient.CreateInstance())
{
var result = reportService.CustomerStatementParameterLookup(SqlDataHandler.GetConnectionString(), building.ID, customer.accNumber, transDate, 3);
startPeriod = result.OrderBy(a => a.Start).First();
endPeriod = result.OrderBy(a => a.Start).Last();
}
totalDue = 0;
List <Transaction> trans = new List <Transaction>();
List <Transaction> optrans = new List <Transaction>();
totalDue = 0;
DateTime trnDate = startPeriod.Start.Value;
DateTime dt1 = new DateTime(transDate.Year, transDate.Month, 1);
var minDate = dt1.AddMonths(-3);
var opBal = startPeriod.OpeningBalance;
Transaction optran = new Transaction
{
AccAmt = startPeriod.OpeningBalance,
Description = "Balance Brought Forward",
Reference = "",
TrnAmt = startPeriod.OpeningBalance,
TrnDate = trnDate,
IsOpeningBalance = true
};
var transactions = Controller.pastel.GetTransactions(building.DataPath, startPeriod.PeriodNumber, endPeriod.PeriodNumber, customer.accNumber).OrderBy(c => c.Date).ToList();
double subtractAmount = 0;
foreach (Trns trn in transactions)
{
Transaction tran = new Transaction
{
Description = trn.Description,
Reference = trn.Reference,
TrnAmt = double.Parse(trn.Amount),
TrnDate = DateTime.Parse(trn.Date)
};
subtractAmount += double.Parse(trn.Amount);
trans.Add(tran);
}
optran.TrnAmt = opBal;
optran.AccAmt = optran.TrnAmt;
optrans.Add(optran);
foreach (Transaction tran in trans)
{
opBal += tran.TrnAmt;
tran.AccAmt = opBal;
optrans.Add(tran);
}
totalDue = opBal;
return(optrans);
}
catch (Exception e)
{
ShowDebug(e.Message);
totalDue = 0;
throw e;
}
}
private byte[] CreateReport(int requisitionBatchId, string combinedFilePath)
{
bool processedOk = true;
byte[] reportData = null;
try
{
using (var reportService = ReportServiceClient.CreateInstance())
{
reportData = reportService.RequisitionBatchReport(SqlDataHandler.GetConnectionString(), requisitionBatchId);
if (reportData != null)
{
using (FileStream ms = new FileStream(combinedFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
using (Document doc = new Document())
{
using (PdfCopy copy = new PdfCopy(doc, ms))
{
doc.Open();
AddPdfDocument(copy, reportData, "Generated Report");
using (var context = SqlDataHandler.GetDataContext())
{
foreach (var requisitionId in context.tblRequisitions.Where(a => a.RequisitionBatchId == requisitionBatchId).OrderBy(a => a.trnDate).Select(a => a.id).ToList())
{
foreach (var invoice in context.RequisitionDocumentSet.Where(a => a.RequisitionId == requisitionId && a.IsInvoice == true).Select(a => a).ToList())
{
if (IsValidPdf(invoice.FileData))
{
try
{
AddPdfDocument(copy, invoice.FileData, invoice.FileName);
}
catch (Exception ex)
{
processedOk = false;
Controller.HandleError("Unable to attach invoice " + invoice.FileData);
}
}
else
{
processedOk = false;
Controller.HandleError("Unable to attach invoice " + invoice.FileData + " pdf is invalid");
}
Application.DoEvents();
}
}
}
//write output file
ms.Flush();
}
}
}
}
}
}
catch (Exception exp)
{
processedOk = false;
Controller.HandleError(exp);
}
if (processedOk == false)
{
return(null);
}
return(reportData);
}