using framework_business; using framework_library; using System; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Data; using System.IO; using System.Linq; using System.Reflection; using System.Text; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class controls_module_debtorsReports : System.Web.UI.UserControl { #region methods /// /// Create a Debtors Age Report /// /// private DataTable CreateDebtorsReport(DateTime ageDate, bool onlyDebitBalance) { DataTable result = new DataTable(); try { /* CVH 2016-06-29 Modified Result sp to calculate ageing for given date. No longer using pal_Ageing, don't need to run sp_DebtorsAgeReport first */ //create age Analysis //DataTable PracticeHeaderReport = xData.CreateDebtorsAgeReport(DateTime.Now, 4); //if (PracticeHeaderReport.Rows.Count > 0) //{ //fetch Age Report Result //CVH 2017-12-05 Change filter option from 4 to 1 (merged 2018-01-23 from TSP) int filter = 1; if (onlyDebitBalance) filter = 2; DataTable DebtorsResult = xData.CreateDebtorsAgeReportResult(ageDate, filter, false); result = DebtorsResult; //} } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } return result; } /// /// Create a Debtors Assisted Report /// /// private DataTable CreateAssistedReport() { DataTable result = new DataTable(); try { //create age Analysis DataTable PracticeHeaderReport = xData.CreateDebtorsAgeReport(DateTime.Now, 4); if (PracticeHeaderReport.Rows.Count > 0) { //fetch Age Report Result DataTable DebtorsResult = xData.CreateDebtorsAssistedReportResult(); /* CVH 2016-09-06 Don't add totals here. Totals are added in item data bound for grid, and in export function for excel */ result = DebtorsResult; } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } return result; } /// /// Create Assisted Report Summary /// /// private DataTable CreateAssistedReportSummary() { DataTable result = new DataTable(); try { //create age Analysis DataTable PracticeHeaderReport = xData.CreateDebtorsAgeReport(DateTime.Now, 4); if (PracticeHeaderReport.Rows.Count > 0) { List list = new List(); DataTable DebtorsResult = xData.GetTypedTableByProc("recId", typeof(oAccount), "sp_DebtorsAssistedReportSummary", list); result = DebtorsResult; } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } return result; } /// /// Export Debtors age report to excel /// private void DebtorsAgeReportToExcel() { try { /* CVH 2016-06-30 Running report for a specific date, set file name to selected date instead of current date */ DateTime ageDate = utils.formatStringToDate(txtFromDate.Value); //create Excel string fileName = "DebtorsAgeReport_" + ageDate.ToString("yyyy-MM-dd-") + System.DateTime.Now.ToString("hh-mm-ss") + ".xlsx"; /* CVH 2016-06-27 Format Excel report */ DataTable dt = CreateDebtorsReport(ageDate, false); dt.TableName = "Debtors Report"; /* CVH 2016-06-28 Add totals to spreadsheet, no longer being added to grid table, added in footer on item data bound */ //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalPatLiable = rows.Select(r => utils.DecimalParse(r["LiableP"])).Sum(); decimal totalmedLiable = rows.Select(r => utils.DecimalParse(r["LiableS"])).Sum(); decimal totalUnallocated = rows.Select(r => utils.DecimalParse(r["Unallocated"])).Sum(); decimal totalInvoiced = rows.Select(r => utils.DecimalParse(r["Invoiced"])).Sum(); decimal totalPaid = rows.Select(r => utils.DecimalParse(r["Paid"])).Sum(); decimal totalBalance = rows.Select(r => utils.DecimalParse(r["Due"])).Sum(); decimal totalCurrent = rows.Select(r => utils.DecimalParse(r["Current"])).Sum(); decimal total30Days = rows.Select(r => utils.DecimalParse(r["d30"])).Sum(); decimal total60Days = rows.Select(r => utils.DecimalParse(r["d60"])).Sum(); decimal total90Days = rows.Select(r => utils.DecimalParse(r["d90"])).Sum(); decimal total120Days = rows.Select(r => Math.Abs(utils.DecimalParse(r["d120"]))).Sum(); //decimal total150Days = rows.Select(r => Math.Abs(utils.DecimalParse(r["d150"]))).Sum(); //decimal total180Days = rows.Select(r => Math.Abs(utils.DecimalParse(r["d180+"]))).Sum(); int TotalPatients = rows.Select(r => utils.DecimalParse(r["PatientNum"])).Count(); //create total row DataRow totalsRow = dt.NewRow(); totalsRow["Name"] = "Patients: " + TotalPatients; totalsRow["MedicalAid"] = "Totals:"; //totalsRow["LiableP"] = utils.returnFormattedDecimal(Convert.ToString(totalPatLiable)); totalsRow["LiableP"] = totalPatLiable; totalsRow["LiableS"] = totalmedLiable; totalsRow["Unallocated"] = totalUnallocated; totalsRow["Invoiced"] = totalInvoiced; totalsRow["Paid"] = totalPaid; totalsRow["Due"] = totalBalance; totalsRow["Current"] = totalCurrent; totalsRow["d30"] = total30Days; totalsRow["d60"] = total60Days; totalsRow["d90"] = total90Days; totalsRow["d120"] = total120Days; //totalsRow["d150"] = total150Days; //totalsRow["d180+"] = total180Days; //add total row dt.Rows.Add(totalsRow); foreach (DataColumn col in dt.Columns) { if (col.ColumnName == "Current") col.ColumnName = "0-30Days"; else if (col.ColumnName == "d30") col.ColumnName = "30-60Days"; else if (col.ColumnName == "d60") col.ColumnName = "60-90Days"; else if (col.ColumnName == "d90") col.ColumnName = "90-120Days"; else if (col.ColumnName == "d120") col.ColumnName = "120+Days"; } string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, true); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Debtors Age Summary Report To Excel /// private void DebtorsAgeSummaryReportToExcel() { try { /* CVH 2016-06-30 Running report for a specific date, set file name to selected date instead of current date */ DateTime ageDate = utils.formatStringToDate(txtFromDate.Value); //create Excel string fileName = "DebtorsAgeSummaryReport_" + ageDate.ToString("yyyy-MM-dd-") + System.DateTime.Now.ToString("hh-mm-ss") + ".xlsx"; /* CVH 2016-06-27 Format Excel report */ DataTable dt = CreateDebtorsReport(ageDate, false); dt.TableName = "Debtors Summary Report"; /* CVH 2016-06-28 Add totals to spreadsheet, no longer being added to grid table, added in footer on item data bound */ //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalInvoiced = rows.Select(r => utils.DecimalParse(r["Invoiced"])).Sum(); decimal totalPaid = rows.Select(r => utils.DecimalParse(r["Paid"])).Sum(); decimal totalBalance = rows.Select(r => utils.DecimalParse(r["Due"])).Sum(); decimal totalCurrent = rows.Select(r => utils.DecimalParse(r["Current"])).Sum(); decimal total30Days = rows.Select(r => utils.DecimalParse(r["d30"])).Sum(); decimal total60Days = rows.Select(r => utils.DecimalParse(r["d60"])).Sum(); decimal total90Days = rows.Select(r => utils.DecimalParse(r["d90"])).Sum(); decimal total120Days = rows.Select(r => utils.DecimalParse(r["d120"])).Sum(); int TotalPatients = rows.Select(r => utils.DecimalParse(r["PatientNum"])).Count(); //create total row DataRow totalsRow = dt.NewRow(); totalsRow["Surname"] = "Patients: " + TotalPatients; totalsRow["Name"] = "Totals:"; totalsRow["Invoiced"] = totalInvoiced; totalsRow["Paid"] = totalPaid; totalsRow["Due"] = totalBalance; totalsRow["Current"] = totalCurrent; totalsRow["d30"] = total30Days; totalsRow["d60"] = total60Days; totalsRow["d90"] = total90Days; totalsRow["d120"] = total120Days; //add total row dt.Rows.Add(totalsRow); for (int i = dt.Columns.Count - 1; i >= 0; i--) { if (dt.Columns[i].ColumnName != "PatientNum" && dt.Columns[i].ColumnName != "Surname" && dt.Columns[i].ColumnName != "Name" && dt.Columns[i].ColumnName != "Invoiced" && dt.Columns[i].ColumnName != "Paid" && dt.Columns[i].ColumnName != "Due" && dt.Columns[i].ColumnName != "Current" && dt.Columns[i].ColumnName != "d30" && dt.Columns[i].ColumnName != "d60" && dt.Columns[i].ColumnName != "d90" && dt.Columns[i].ColumnName != "d120") dt.Columns.RemoveAt(i); else if (dt.Columns[i].ColumnName == "Current") dt.Columns[i].ColumnName = "0-30Days"; else if (dt.Columns[i].ColumnName == "d30") dt.Columns[i].ColumnName = "30-60Days"; else if (dt.Columns[i].ColumnName == "d60") dt.Columns[i].ColumnName = "60-90Days"; else if (dt.Columns[i].ColumnName == "d90") dt.Columns[i].ColumnName = "90-120Days"; else if (dt.Columns[i].ColumnName == "d120") dt.Columns[i].ColumnName = "120+Days"; } /* CVH 2016-06-28 Find image to include at top of worksheet */ string imagePath = ""; foreach (oGallery gallery in xData.GetTypedByCriteriaSpecific("recId", typeof(oGallery), "title", "Debtors Age Summary Report")) { foreach (oPictureType pType in xData.GetTypedByCriteriaSpecific("recId", typeof(oPictureType), "recId", gallery.pictureTypeId.ToString())) { imagePath = Server.MapPath("~/upload/gallery/image/" + pType.type + "/" + gallery.fileName); } } string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, true, imagePath, "A2"); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Bind Debtors age report to repeater /// private void DebtorsAgeReportToGrid() { try { DateTime ageDate = utils.formatStringToDate(txtFromDate.Value); //bind Grid rptDebtorsAgeReport.DataSource = CreateDebtorsReport(ageDate, false); rptDebtorsAgeReport.DataBind(); SetVisibleRepeater(rptDebtorsAgeReport.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Debtors Age Summary Report To Grid /// private void DebtorsAgeSummaryReportToGrid() { try { DateTime ageDate = utils.formatStringToDate(txtFromDate.Value); //bind Grid rptDebtorsAgeSummaryReport.DataSource = CreateDebtorsReport(ageDate, false); rptDebtorsAgeSummaryReport.DataBind(); SetVisibleRepeater(rptDebtorsAgeSummaryReport.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Month End Statement Run To Grid /// private void MonthEndStatementRunToGrid() { try { //bind Grid rptMonthEndStatementRun.DataSource = CreateDebtorsReport(System.DateTime.Now, true); rptMonthEndStatementRun.DataBind(); SetVisibleRepeater(rptMonthEndStatementRun.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Bind Invoice Listings Report to Repeater /// private void BindInvoiceListings() { try { //bind Grid rptInvoicingListings.DataSource = GetInvoiceListings(); rptInvoicingListings.DataBind(); SetVisibleRepeater(rptInvoicingListings.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Bind Receipt Listings Report to Repeater /// private void BindReceiptListings() { try { //bind Grid rptReceiptListings.DataSource = GetReceiptListings(); rptReceiptListings.DataBind(); SetVisibleRepeater(rptReceiptListings.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Bind Medical Aid Summary Report to Repeater /// private void BindMedicalAidSummary() { try { //bind Grid rptMedicalAidSummary.DataSource = GetMedicalAidSummaryReport(); rptMedicalAidSummary.DataBind(); SetVisibleRepeater(rptMedicalAidSummary.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Build Remittance filter string /// /// private void BuildRemittanceFilter(out string filterEraStatus, out string filterProcessStatus) { filterEraStatus = ""; filterProcessStatus = ""; try { int[] indicesEraStatus = lstFilterEraStatus.GetSelectedIndices(); for (int i = 0; i < indicesEraStatus.Count(); i++) { string val = lstFilterEraStatus.Items[indicesEraStatus[i]].Value.Replace("'", "''"); if (val == "(blank)") val = ""; if (filterEraStatus == String.Empty) filterEraStatus = "'" + val + "'"; else filterEraStatus += ", " + "'" + val + "'"; } int[] indicesProcessStatus = lstFilterProcessStatus.GetSelectedIndices(); for (int i = 0; i < indicesProcessStatus.Count(); i++) { string val = lstFilterProcessStatus.Items[indicesProcessStatus[i]].Value.Replace("'", "''"); if (val == "(blank)") val = ""; if (filterProcessStatus == String.Empty) filterProcessStatus = "'" + val + "'"; else filterProcessStatus += ", " + "'" + val + "'"; } } catch (Exception ex) { exception.HandleException("surface:", MethodBase.GetCurrentMethod().Name, ex, Session["user"]); Response.Redirect("/error", false); } } /// /// Bind Remittance filter boxes /// private void BindRemittanceFilters() { try { string eraStatus = ""; foreach (int index in lstFilterEraStatus.GetSelectedIndices()) { if (eraStatus == "") eraStatus = lstFilterEraStatus.Items[index].Value; else eraStatus += "|" + lstFilterEraStatus.Items[index].Value; } DataTable dt = GetRemittanceListings("", ""); //bind era status filter dt.DefaultView.Sort = "EraStatus ASC"; DataTable dtFilterEraStatus = dt.DefaultView.ToTable(true, "EraStatus"); //replace spaces with   foreach (DataRow row in dtFilterEraStatus.Rows) { if (row[0].ToString().Trim() == String.Empty) row[0] = "(blank)"; } lstFilterEraStatus.DataSource = dtFilterEraStatus; lstFilterEraStatus.DataTextField = "EraStatus"; lstFilterEraStatus.DataValueField = "EraStatus"; lstFilterEraStatus.DataBind(); foreach (string sel in eraStatus.Split('|')) { if (lstFilterEraStatus.Items.FindByValue(sel) != null) lstFilterEraStatus.Items.FindByValue(sel).Selected = true; } string processStatus = ""; foreach (int index in lstFilterProcessStatus.GetSelectedIndices()) { if (processStatus == "") processStatus = lstFilterProcessStatus.Items[index].Value; else processStatus += "|" + lstFilterProcessStatus.Items[index].Value; } //bind process status filter dt.DefaultView.Sort = "ProcessedStatus ASC"; DataTable dtFilterProcessStatus = dt.DefaultView.ToTable(true, "ProcessedStatus"); //replace spaces with   foreach (DataRow row in dtFilterProcessStatus.Rows) { if (row[0].ToString().Trim() == String.Empty) row[0] = "(blank)"; } lstFilterProcessStatus.DataSource = dtFilterProcessStatus; lstFilterProcessStatus.DataTextField = "ProcessedStatus"; lstFilterProcessStatus.DataValueField = "ProcessedStatus"; lstFilterProcessStatus.DataBind(); foreach (string sel in processStatus.Split('|')) { if (lstFilterProcessStatus.Items.FindByValue(sel) != null) lstFilterProcessStatus.Items.FindByValue(sel).Selected = true; } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Bind Remittance Advice Listings Report to Repeater /// private void BindRemittanceListings() { try { BindRemittanceFilters(); string filterEraStatus = ""; string filterProcessStatus = ""; BuildRemittanceFilter(out filterEraStatus, out filterProcessStatus); //bind Grid rptRemittanceListings.DataSource = GetRemittanceListings(filterEraStatus, filterProcessStatus); rptRemittanceListings.DataBind(); SetVisibleRepeater(rptRemittanceListings.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Bind Recon Report to Repeater /// private void BindRecon() { try { //bind Grid rptRecon.DataSource = GetRecon(); rptRecon.DataBind(); SetVisibleRepeater(rptRecon.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Bind Debit Note Listings Report to Repeater /// private void BindDebitNoteListings() { try { //bind Grid rptDebitListings.DataSource = GetDebitListings(); rptDebitListings.DataBind(); SetVisibleRepeater(rptDebitListings.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Bind Credit Note Listings Report to Repeater /// private void BindCreditNoteListings() { try { //bind Grid rptCreditListings.DataSource = GetCreditListings(); rptCreditListings.DataBind(); SetVisibleRepeater(rptCreditListings.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Bind Financial Transaction Summary /// private void BindFinancialTransactionSummary() { try { DataTable dtIN = GetInvoiceListings(); rptInvoiceTransactions.DataSource = dtIN; rptInvoiceTransactions.DataBind(); DataTable dtPM = GetReceiptListings(); rptReceiptTransactions.DataSource = dtPM; rptReceiptTransactions.DataBind(); DataTable dtDN = GetDebitListings(); rptDebitTransactions.DataSource = dtDN; rptDebitTransactions.DataBind(); DataTable dtCN = GetCreditListings(); rptCreditTransactions.DataSource = dtCN; rptCreditTransactions.DataBind(); SetVisibleRepeater(pnlFinancialTransactionSummary.ID); var rowsIN = dtIN.Rows.Cast(); var rowsPM = dtPM.Rows.Cast(); var rowsDN = dtDN.Rows.Cast(); var rowsCN = dtCN.Rows.Cast(); //get totals decimal totalVAT = rowsIN.Select(r => utils.DecimalParse(r["VatAmount"])).Sum() + rowsDN.Select(r => utils.DecimalParse(r["VatAmount"])).Sum() + rowsCN.Select(r => utils.DecimalParse(r["VatAmount"])).Sum(); decimal totalAmount = rowsIN.Select(r => utils.DecimalParse(r["TotalBilling"])).Sum() - rowsPM.Select(r => utils.DecimalParse(r["TotalReceiptedAmount"])).Sum() + rowsDN.Select(r => utils.DecimalParse(r["TotalDebitNoteAmount"])).Sum() + rowsCN.Select(r => utils.DecimalParse(r["TotalCreditNoteAmount"])).Sum(); lblTransVAT.Text = "R " + utils.returnFormattedDecimal(totalVAT.ToString()); ; lblTransTotal.Text = "R " + utils.returnFormattedDecimal(totalAmount.ToString()); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Get Invoice Listings Report Data /// /// DataTable private DataTable GetInvoiceListings() { DataTable dtInvoiceListing = new DataTable(); DateTime dateFrom = utils.formatStringToDate(txtFromDate.Value); DateTime dateTo = utils.formatStringToDate(txtToDate.Value); List storedProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "dateFrom"; param.paramObject = dateFrom; storedProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "dateTo"; param2.paramObject = dateTo; storedProcParams.Add(param2); dtInvoiceListing = xData.GetTypedTableByProc("InvoiceNumber", typeof(oInvoiceListing), "sp_InvoiceListingByDate", storedProcParams, "v_"); return dtInvoiceListing; } /// /// Get Medical Aid Summary Report Data /// /// DataTable private DataTable GetMedicalAidSummaryReport() { DataTable dtMedicalAidSummary = new DataTable(); DateTime dateFrom = utils.formatStringToDate(txtFromDate.Value); DateTime dateTo = utils.formatStringToDate(txtToDate.Value); List storedProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "startDate"; param.paramObject = dateFrom; storedProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "endDate"; param2.paramObject = dateTo; storedProcParams.Add(param2); dtMedicalAidSummary = xData.GetTypedTableByProc("recId", typeof(oAccount), "sp_GetMedicalAidInvoices", storedProcParams); return dtMedicalAidSummary; } /// /// Get Receipt Listings Report Data /// /// DataTable private DataTable GetReceiptListings() { DataTable dtReceiptListing = new DataTable(); DateTime dateFrom = utils.formatStringToDate(txtFromDate.Value); DateTime dateTo = utils.formatStringToDate(txtToDate.Value); List storedProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "dateFrom"; param.paramObject = dateFrom; storedProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "dateTo"; param2.paramObject = dateTo; storedProcParams.Add(param2); dtReceiptListing = xData.GetTypedTableByProc("ReceiptNumber", typeof(oReceiptListing), "sp_ReceiptListingByDate", storedProcParams, "v_"); return dtReceiptListing; } /// /// Get Remittance Advice Listings Report Data /// /// DataTable private DataTable GetRemittanceListings(string eraStatusFilter, string processStatusFilter) { DataTable dtRemittanceListing = new DataTable(); DateTime dateFrom = utils.formatStringToDate(txtFromDate.Value); DateTime dateTo = utils.formatStringToDate(txtToDate.Value); List storedProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "dateFrom"; param.paramObject = dateFrom; storedProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "dateTo"; param2.paramObject = dateTo; storedProcParams.Add(param2); oDynamicParam param3 = new oDynamicParam(); param3.paramDisplayName = "EraStatusFilter"; param3.paramObject = eraStatusFilter; storedProcParams.Add(param3); oDynamicParam param4 = new oDynamicParam(); param4.paramDisplayName = "ProcessedStatusFilter"; param4.paramObject = processStatusFilter; storedProcParams.Add(param4); dtRemittanceListing = xData.GetTypedTableByProc("ReceiptNumber", typeof(ovRemittanceListing), "sp_RemittanceListingByDate", storedProcParams, "v_"); return dtRemittanceListing; } /// /// Get Recon Report Data /// /// DataTable private DataTable GetRecon() { DataTable dtRecon = new DataTable(); dtRecon = xData.GetTypedTableByProc("FiscalPeriod", typeof(oAccountRecon), "sp_ReconByFiscalPeriod", new List()); return dtRecon; } /// /// Get Debit Notes Listings Report Data /// /// DataTable private DataTable GetDebitListings() { DataTable dtDebitListing = new DataTable(); DateTime dateFrom = utils.formatStringToDate(txtFromDate.Value); DateTime dateTo = utils.formatStringToDate(txtToDate.Value); List storedProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "dateFrom"; param.paramObject = dateFrom; storedProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "dateTo"; param2.paramObject = dateTo; storedProcParams.Add(param2); dtDebitListing = xData.GetTypedTableByProc("DebitNoteNumber", typeof(oDebitListing), "sp_DebitListingByDate", storedProcParams, "v_"); return dtDebitListing; } /// /// Get Credit Notes Listings Report Data /// /// DataTable private DataTable GetCreditListings() { DataTable dtCreditListing = new DataTable(); DateTime dateFrom = utils.formatStringToDate(txtFromDate.Value); DateTime dateTo = utils.formatStringToDate(txtToDate.Value); List storedProcParams = new List(); oDynamicParam param = new oDynamicParam(); param.paramDisplayName = "dateFrom"; param.paramObject = dateFrom; storedProcParams.Add(param); oDynamicParam param2 = new oDynamicParam(); param2.paramDisplayName = "dateTo"; param2.paramObject = dateTo; storedProcParams.Add(param2); dtCreditListing = xData.GetTypedTableByProc("CreditNoteNumber", typeof(oCreditListing), "sp_CreditListingByDate", storedProcParams, "v_"); return dtCreditListing; } /// /// Set Repeater visibility /// /// private void SetVisibleRepeater(string repeaterName) { try { rptCreditListings.Visible = repeaterName == "rptCreditListings"; rptDebitListings.Visible = repeaterName == "rptDebitListings"; rptDebtorsAgeReport.Visible = repeaterName == "rptDebtorsAgeReport"; rptDebtorsAgeSummaryReport.Visible = repeaterName == "rptDebtorsAgeSummaryReport"; rptMonthEndStatementRun.Visible = repeaterName == "rptMonthEndStatementRun"; rptDebtorsAssistedReport.Visible = repeaterName == "rptDebtorsAssistedReport"; rptDebtorsAssistedReportSummary.Visible = repeaterName == "rptDebtorsAssistedReportSummary"; rptInvoicingListings.Visible = repeaterName == "rptInvoicingListings"; rptReceiptListings.Visible = repeaterName == "rptReceiptListings"; rptMedicalAidSummary.Visible = repeaterName == "rptMedicalAidSummary"; rptRemittanceListings.Visible = repeaterName == "rptRemittanceListings"; rptRecon.Visible = repeaterName == "rptRecon"; pnlFinancialTransactionSummary.Visible = repeaterName == "pnlFinancialTransactionSummary"; lnkExportToExcel.Visible = repeaterName != "" && repeaterName != "rptMonthEndStatementRun"; lnkEmailStatements.Visible = repeaterName == "rptMonthEndStatementRun"; upReports.Update(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Export Receipt Listings to Excel /// private void ExportReceiptListings() { try { //create Excel string fileName = "ListingsReceipts_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"; /* CVH 2016-06-27 Format Excel report */ DataTable dt = GetReceiptListings(); dt.TableName = "Listings - Receipts"; /* CVH 2016-06-27 Add totals */ //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalTotalReceiptedAmount = rows.Select(r => utils.DecimalParse(r["TotalReceiptedAmount"])).Sum(); decimal totalAmountPaidToAssistantFee = rows.Select(r => utils.DecimalParse(r["AmountPaidToAssistantFee"])).Sum(); //create total row DataRow totalsRow = dt.NewRow(); totalsRow["PaymentMethod"] = "Totals:"; totalsRow["TotalReceiptedAmount"] = totalTotalReceiptedAmount; totalsRow["AmountPaidToAssistantFee"] = totalAmountPaidToAssistantFee; dt.Rows.Add(totalsRow); string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, true); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Export Financial Transaction Summary /// private void ExportFinancialTransactionSummary() { try { //create Excel string fileName = "FinancialTransactionSummary_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"; DataTable dtInvoices = GetInvoiceListings(); DataTable dtReceipts = GetReceiptListings(); for (int i = 0; i < dtReceipts.Columns.Count - 1; i++) { if (dtReceipts.Columns[i].ColumnName == "Invoices") dtReceipts.Columns[i].SetOrdinal(i - 2); else if (dtReceipts.Columns[i].ColumnName == "TotalReceiptedAmount") dtReceipts.Columns[i].SetOrdinal(i + 2); else dtReceipts.Columns[i].SetOrdinal(i); } DataTable dtDebitnotes = GetDebitListings(); DataTable dtCreditNotes = GetCreditListings(); //totals //invoices var rowsIN = dtInvoices.Rows.Cast(); decimal totalIN = rowsIN.Select(r => utils.DecimalParse(r["TotalBilling"])).Sum(); decimal vatIN = rowsIN.Select(r => utils.DecimalParse(r["VatAmount"])).Sum(); DataRow totalsRowIN = dtInvoices.NewRow(); totalsRowIN[dtInvoices.Columns.Count - 5] = "Totals:"; totalsRowIN[dtInvoices.Columns.Count - 2] = vatIN; totalsRowIN[dtInvoices.Columns.Count - 1] = totalIN; dtInvoices.Rows.Add(totalsRowIN); //receipts var rowsPM = dtReceipts.Rows.Cast(); decimal totalPM = rowsPM.Select(r => utils.DecimalParse(r["TotalReceiptedAmount"])).Sum(); decimal totalAss = rowsPM.Select(r => utils.DecimalParse(r["AmountPaidToAssistantFee"])).Sum(); DataRow totalsRowPM = dtReceipts.NewRow(); totalsRowPM[dtReceipts.Columns.Count - 3] = "Totals:"; totalsRowPM[dtReceipts.Columns.Count - 2] = totalAss; totalsRowPM[dtReceipts.Columns.Count - 1] = totalPM; dtReceipts.Rows.Add(totalsRowPM); //debit notes var rowsDN = dtDebitnotes.Rows.Cast(); decimal totalDN = rowsDN.Select(r => utils.DecimalParse(r["TotalDebitNoteAmount"])).Sum(); decimal vatDN = rowsDN.Select(r => utils.DecimalParse(r["VatAmount"])).Sum(); DataRow totalsRowDN = dtDebitnotes.NewRow(); totalsRowDN[dtDebitnotes.Columns.Count - 4] = "Totals:"; totalsRowDN[dtDebitnotes.Columns.Count - 2] = vatDN; totalsRowDN[dtDebitnotes.Columns.Count - 1] = totalDN; dtDebitnotes.Rows.Add(totalsRowDN); //credit notes var rowsCN = dtCreditNotes.Rows.Cast(); decimal totalCN = rowsCN.Select(r => utils.DecimalParse(r["TotalCreditNoteAmount"])).Sum(); decimal vatCN = rowsCN.Select(r => utils.DecimalParse(r["VatAmount"])).Sum(); DataRow totalsRowCN = dtCreditNotes.NewRow(); totalsRowCN[dtCreditNotes.Columns.Count - 4] = "Totals:"; totalsRowCN[dtCreditNotes.Columns.Count - 2] = vatCN; totalsRowCN[dtCreditNotes.Columns.Count - 1] = totalCN; dtCreditNotes.Rows.Add(totalsRowCN); string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTablesToExcelGLOB1FinancialSummary(dtInvoices, dtReceipts, dtDebitnotes, dtCreditNotes, vatIN + vatDN + vatCN, totalIN - totalPM + totalDN + totalCN, excelFile); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Export Remittance Listings to Excel /// private void ExportRemittanceListings() { try { //create Excel string fileName = "ListingsRemittanceAdvices_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"; string filterEraStatus = ""; string filterProcessStatus = ""; BuildRemittanceFilter(out filterEraStatus, out filterProcessStatus); /* CVH 2016-06-27 Format Excel report */ DataTable dt = GetRemittanceListings(filterEraStatus, filterProcessStatus); dt.TableName = "Listings - Remittance Advices"; /* CVH 2016-06-27 Add totals */ //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalClaimAmount = rows.Select(r => utils.DecimalParse(r["ClaimAmount"])).Sum(); decimal totalPaidAmount = rows.Select(r => utils.DecimalParse(r["PaidAmount"])).Sum(); //create total row DataRow totalsRow = dt.NewRow(); totalsRow["MedicalAid"] = "Totals:"; totalsRow["ClaimAmount"] = totalClaimAmount; totalsRow["PaidAmount"] = totalPaidAmount; dt.Rows.Add(totalsRow); string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, true); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Export Recon to Excel /// private void ExportRecon() { try { //create Excel string fileName = "Recon_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"; /* CVH 2016-06-27 Format Excel report */ DataTable dt = GetRecon(); dt.TableName = "Recon"; /* CVH 2016-06-27 Add totals */ //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalInvoices = rows.Select(r => utils.DecimalParse(r["Invoices"])).Sum(); decimal totalReceipts = rows.Select(r => utils.DecimalParse(r["Receipts"])).Sum(); decimal totalCreditNotes = rows.Select(r => utils.DecimalParse(r["CreditNotes"])).Sum(); decimal totalDebitNotes = rows.Select(r => utils.DecimalParse(r["DebitNotes"])).Sum(); //create total row DataRow totalsRow = dt.NewRow(); totalsRow["CalendarDate"] = "Totals:"; totalsRow["Invoices"] = totalInvoices; totalsRow["Receipts"] = totalReceipts; totalsRow["CreditNotes"] = totalCreditNotes; totalsRow["DebitNotes"] = totalDebitNotes; dt.Rows.Add(totalsRow); string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, true); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Export Credit Note Listings to Excel /// private void ExportCreditNoteListings() { try { //create Excel string fileName = "ListingsCreditNotes_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"; /* CVH 2016-06-27 Format Excel report */ DataTable dt = GetCreditListings(); dt.TableName = "Listings - Credit Notes"; /* CVH 2016-06-27 Add totals */ //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalNettAmount = rows.Select(r => utils.DecimalParse(r["NettAmount"])).Sum(); decimal totalVatAmount = rows.Select(r => utils.DecimalParse(r["VatAmount"])).Sum(); decimal totalTotalCreditNoteAmount = rows.Select(r => utils.DecimalParse(r["TotalCreditNoteAmount"])).Sum(); //create total row DataRow totalsRow = dt.NewRow(); totalsRow["MedicalAid"] = "Totals:"; totalsRow["NettAmount"] = totalNettAmount; totalsRow["VatAmount"] = totalVatAmount; totalsRow["TotalCreditNoteAmount"] = totalTotalCreditNoteAmount; dt.Rows.Add(totalsRow); string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, true); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Export Debit Note Listings to Excel /// private void ExportDebitNoteListings() { try { //create Excel string fileName = "ListingsDebitNotes_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"; /* CVH 2016-06-27 Format Excel report */ DataTable dt = GetDebitListings(); dt.TableName = "Listings - Debit Notes"; /* CVH 2016-06-27 Add totals */ //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalNettAmount = rows.Select(r => utils.DecimalParse(r["NettAmount"])).Sum(); decimal totalVatAmount = rows.Select(r => utils.DecimalParse(r["VatAmount"])).Sum(); decimal totalTotalDebitNoteAmount = rows.Select(r => utils.DecimalParse(r["TotalDebitNoteAmount"])).Sum(); //create total row DataRow totalsRow = dt.NewRow(); totalsRow["MedicalAid"] = "Totals:"; totalsRow["NettAmount"] = totalNettAmount; totalsRow["VatAmount"] = totalVatAmount; totalsRow["TotalDebitNoteAmount"] = totalTotalDebitNoteAmount; dt.Rows.Add(totalsRow); string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, true); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Export Invoice Listings to Excel /// private void ExportInvoiceListings() { try { //create Excel string fileName = "ListingsInvoices_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"; /* CVH 2016-06-27 Format Excel report */ DataTable dt = GetInvoiceListings(); dt.TableName = "Listings - Invoices"; /* CVH 2016-06-27 Add totals */ //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalNettAmount = rows.Select(r => utils.DecimalParse(r["NettAmount"])).Sum(); decimal totalAssistantFee = rows.Select(r => utils.DecimalParse(r["AssistantFee"])).Sum(); decimal totalVatAmount = rows.Select(r => utils.DecimalParse(r["VatAmount"])).Sum(); decimal totalTotalBilling = rows.Select(r => utils.DecimalParse(r["TotalBilling"])).Sum(); //create total row DataRow totalsRow = dt.NewRow(); totalsRow["MedicalAid"] = "Totals:"; totalsRow["NettAmount"] = totalNettAmount; totalsRow["AssistantFee"] = totalAssistantFee; totalsRow["VatAmount"] = totalVatAmount; totalsRow["TotalBilling"] = totalTotalBilling; dt.Rows.Add(totalsRow); string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, true); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Export Medical Aid Summary to Excel /// private void ExportMedicalAidSummary() { try { //create Excel string fileName = "MedicalAidSummary_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"; DataTable dt = GetMedicalAidSummaryReport(); dt.TableName = "Medical Aid Summary"; string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, false); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Bind Debtors Assisted Report to Repeater /// private void DebtorsAssistedReportToGrid() { try { rptDebtorsAssistedReport.DataSource = CreateAssistedReport(); rptDebtorsAssistedReport.DataBind(); SetVisibleRepeater(rptDebtorsAssistedReport.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } private void DebtorsAssistedReportSummaryToGrid() { try { rptDebtorsAssistedReportSummary.DataSource = CreateAssistedReportSummary(); rptDebtorsAssistedReportSummary.DataBind(); SetVisibleRepeater(rptDebtorsAssistedReportSummary.ID); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Export Debtors Assistant Report to Excel /// private void DebtorsAssistedReportToExcel() { try { //create Excel string fileName = "DebtorsAssistantReport_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"; /* CVH 2016-06-27 Format Excel report */ DataTable dt = CreateAssistedReport(); dt.TableName = "Assistant Report"; //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalBalance = rows.Select(r => utils.DecimalParse(r["Due"])).Sum(); decimal totalDueAssistantIncl = rows.Select(r => utils.DecimalParse(r["DueToAssistantIncl"])).Sum(); decimal totalVAT = rows.Select(r => utils.DecimalParse(r["VAT"])).Sum(); decimal totalDueAssistantExcl = rows.Select(r => utils.DecimalParse(r["DueToAssistantExcl"])).Sum(); decimal totalPayAssistant = rows.Select(r => utils.DecimalParse(r["PayableToAssistant"])).Sum(); int TotalPatients = rows.Select(r => Math.Abs(utils.DecimalParse(r["PatientNum"]))).Count(); //create total row DataRow totalsRow = dt.NewRow(); totalsRow["MainMember"] = "Patients: " + TotalPatients + " Totals:"; totalsRow["Due"] = totalBalance; totalsRow["DueToAssistantIncl"] = totalDueAssistantIncl; totalsRow["VAT"] = totalVAT; totalsRow["DueToAssistantExcl"] = totalDueAssistantExcl; totalsRow["PayableToAssistant"] = totalPayAssistant; //add total row dt.Rows.Add(totalsRow); if (dt.Columns["DueToAssistantIncl"] != null) dt.Columns["DueToAssistantIncl"].ColumnName = "DueToAssistant(Incl.VAT)"; if (dt.Columns["DueToAssistantExcl"] != null) dt.Columns["DueToAssistantExcl"].ColumnName = "DueToAssistant(Excl.VAT)"; if (dt.Columns["VAT"] != null) dt.Columns["VAT"].ColumnName = "VAT Amount"; string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, true); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } private void DebtorsAssistedSummaryReportToExcel() { try { //create Excel string fileName = "DebtorsAssistantSummaryReport_" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"; /* CVH 2016-06-27 Format Excel report */ DataTable dt = CreateAssistedReportSummary(); dt.TableName = "Assistant Summary Report"; //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalDueAssistantIncl = rows.Select(r => utils.DecimalParse(r["DueToAssistantIncl"])).Sum(); decimal totalVAT = rows.Select(r => utils.DecimalParse(r["VAT"])).Sum(); decimal totalDueAssistantExcl = rows.Select(r => utils.DecimalParse(r["DueToAssistantExcl"])).Sum(); decimal totalPayAssistant = rows.Select(r => utils.DecimalParse(r["PayableToAssistant"])).Sum(); decimal totalPaidYTD = rows.Select(r => utils.DecimalParse(r["PaidYTD"])).Sum(); decimal totalPaidYTDPrev = rows.Select(r => utils.DecimalParse(r["PaidYTDPrev"])).Sum(); //create total row DataRow totalsRow = dt.NewRow(); totalsRow["Assistant_Specialist"] = "Totals: "; totalsRow["DueToAssistantIncl"] = totalDueAssistantIncl; totalsRow["VAT"] = totalVAT; totalsRow["DueToAssistantExcl"] = totalDueAssistantExcl; totalsRow["PayableToAssistant"] = totalPayAssistant; totalsRow["PaidYTD"] = totalPaidYTD; totalsRow["PaidYTDPrev"] = totalPaidYTDPrev; //add total row dt.Rows.Add(totalsRow); dt.Columns["Assistant_Specialist"].ColumnName = "Assistant / Specialist"; if (dt.Columns["DueToAssistantIncl"] != null) dt.Columns["DueToAssistantIncl"].ColumnName = "DueToAssistant(Incl.VAT)"; if (dt.Columns["DueToAssistantExcl"] != null) dt.Columns["DueToAssistantExcl"].ColumnName = "DueToAssistant(Excl.VAT)"; if (dt.Columns["VAT"] != null) dt.Columns["VAT"].ColumnName = "VAT Amount"; if (dt.Columns["PaidYTD"] != null) dt.Columns["PaidYTD"].ColumnName = "Paid YTD " + System.DateTime.Now.Year; if (dt.Columns["PaidYTDPrev"] != null) dt.Columns["PaidYTDPrev"].ColumnName = "Paid YTD " + System.DateTime.Now.AddYears(-1).Year; string excelFile = Server.MapPath("~/upload/documents/" + fileName); utils.ExportDataTabletoExcelFormatted(dt, excelFile, true, true); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(excelFile); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Generate and export Statement History /// private void CreateStatementHistory() { try { string fileName = "StatementReport_" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx"; string path = Server.MapPath("~/upload/documents/"); utils.validateFolder(path); DataTable result = new DataTable(); //Default the columns result.Columns.Add("DateCreated", typeof(DateTime)); result.Columns.Add("PatientNumber"); result.Columns.Add("PatientName"); result.Columns.Add("PatientSurname"); result.Columns.Add("CurrentBalance"); result.Columns.Add("MedicalAid"); result.Columns.Add("MedicalAidNo"); FileInfo[] Files = new DirectoryInfo(HttpContext.Current.Server.MapPath("~") + "\\upload\\documents\\").GetFiles("Statement_*"); //Enumerate the items in the File list foreach (FileInfo Item in Files) { string patNum = Item.Name.Substring(Item.Name.IndexOf("_") + 1, 4); //get patient decimal bal = 0; string patName = String.Empty; string patSurname = String.Empty; string MedAid = String.Empty; string MedNumber = String.Empty; ArrayList lines = xData.GetTypedByCriteriaSpecific("recId", typeof(oAccount), "patientNumber", patNum, "DateOfService,sequence"); foreach (oAccount line in lines) { bal += line.amount; if (line.procedureType == "TI") { patName = line.patientName; patSurname = line.patientSurname; MedAid = line.medAidName; MedNumber = line.medAidNumber; } } //Add data row to the file data result.Rows.Add(Item.LastWriteTime, patNum, patName, patSurname, utils.returnFormattedDecimal(Convert.ToString(bal)), MedAid, MedNumber); } /* CVH 2016-06-27 Format Excel report */ result.TableName = "Statement History"; utils.ExportDataTabletoExcelFormatted(result, path + fileName, true, true); Response.Clear(); //Set the appropriate ContentType. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); Response.TransmitFile(path + fileName); Response.Flush(); Response.SuppressContent = true; ApplicationInstance.CompleteRequest(); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } #endregion #region events /// /// Page Load /// /// /// protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { } else { ScriptManager.RegisterStartupScript(Page, Page.GetType(), "setLoadOptions", "if(typeof(setLoadOptions) == \"function\"){window.onload = setLoadOptions()};", true); } } /// /// Create /// /// /// protected void btnDebtorsAgeReport_Click(object sender, EventArgs e) { DebtorsAgeReportToExcel(); } /// /// Export to Excel /// /// /// protected void lnkDebtorsAgeReportToGrid_Click(object sender, EventArgs e) { DebtorsAgeReportToGrid(); } /// /// Assisted Report to Grid /// /// /// protected void lnkDebtorsAssistedReportToGrid_Click(object sender, EventArgs e) { DebtorsAssistedReportToGrid(); } /// /// Asssited Reprot to File /// /// /// protected void lnkDebtorsAssistedReportToFile_Click(object sender, EventArgs e) { DebtorsAssistedReportToExcel(); } /// /// Item Data Bound Event /// /// /// protected void rptDebtorsAgeReport_ItemDataBound(object sender, RepeaterItemEventArgs e) { try { /* CVH 2016-06-27 Moved totals into footer */ //Label lblMedicalAid = (Label)e.Item.FindControl("lblMedicalAid"); //if (lblMedicalAid != null && lblMedicalAid.Text.ToLower().Contains("total")) //{ // Label lblPatientName = (Label)e.Item.FindControl("lblPatientName"); // Label lblPatientSurname = (Label)e.Item.FindControl("lblPatientSurname"); // //Label LiableP = (Label)e.Item.FindControl("LiableP"); // //Label LiableS = (Label)e.Item.FindControl("LiableS"); // //Label Unallocated = (Label)e.Item.FindControl("Unallocated"); // Label Invoiced = (Label)e.Item.FindControl("Invoiced"); // Label Paid = (Label)e.Item.FindControl("Paid"); // Label Due = (Label)e.Item.FindControl("Due"); // Label Current = (Label)e.Item.FindControl("Current"); // Label d30 = (Label)e.Item.FindControl("d30"); // Label d60 = (Label)e.Item.FindControl("d60"); // Label d90 = (Label)e.Item.FindControl("d90"); // //Label d120 = (Label)e.Item.FindControl("d120"); // Label lblDateInvoiced = (Label)e.Item.FindControl("lblDateInvoiced"); // Label lblDatePaid = (Label)e.Item.FindControl("lblDatePaid"); // lblMedicalAid.Font.Bold = true; // lblPatientName.Font.Bold = true; // lblPatientSurname.Font.Bold = true; // //LiableP.Font.Bold = true; // //LiableS.Font.Bold = true; // //Unallocated.Font.Bold = true; // Invoiced.Font.Bold = true; // Paid.Font.Bold = true; // Due.Font.Bold = true; // Current.Font.Bold = true; // d30.Font.Bold = true; // d60.Font.Bold = true; // d90.Font.Bold = true; // //d120.Font.Bold = true; // lblDateInvoiced.Font.Bold = true; // lblDatePaid.Font.Bold = true; //} if (e.Item.ItemType == ListItemType.Footer) { DateTime ageDate = utils.formatStringToDate(txtFromDate.Value); //CVH 2017-12-05 Change filter option from 4 to 1 (merged 2018-01-23 from TSP) //fetch Age Report Result DataTable DebtorsResult = xData.CreateDebtorsAgeReportResult(ageDate, 1, false); //cast rows var rows = DebtorsResult.Rows.Cast(); //get totals //decimal totalUnallocated = rows.Select(r => utils.DecimalParse(r["Unallocated"])).Sum(); decimal totalInvoiced = rows.Select(r => utils.DecimalParse(r["Invoiced"])).Sum(); decimal totalPaid = rows.Select(r => utils.DecimalParse(r["Paid"])).Sum(); decimal totalBalance = rows.Select(r => utils.DecimalParse(r["Due"])).Sum(); decimal totalCurrent = rows.Select(r => utils.DecimalParse(r["Current"])).Sum(); decimal total30Days = rows.Select(r => utils.DecimalParse(r["d30"])).Sum(); decimal total60Days = rows.Select(r => utils.DecimalParse(r["d60"])).Sum(); decimal total90Days = rows.Select(r => utils.DecimalParse(r["d90"])).Sum(); decimal total120Days = rows.Select(r => utils.DecimalParse(r["d120"])).Sum(); int TotalPatients = rows.Select(r => utils.DecimalParse(r["PatientNum"])).Count(); Label lblFooterPatients = (Label)e.Item.FindControl("lblFooterPatients"); Label lblFooterInvoiced = (Label)e.Item.FindControl("lblFooterInvoiced"); Label lblFooterPaid = (Label)e.Item.FindControl("lblFooterPaid"); Label lblFooterDue = (Label)e.Item.FindControl("lblFooterDue"); Label lblFooterCurrent = (Label)e.Item.FindControl("lblFooterCurrent"); Label lblFooter30 = (Label)e.Item.FindControl("lblFooter30"); Label lblFooter60 = (Label)e.Item.FindControl("lblFooter60"); Label lblFooter90 = (Label)e.Item.FindControl("lblFooter90"); Label lblFooter120 = (Label)e.Item.FindControl("lblFooter120"); if (lblFooterPatients != null) lblFooterPatients.Text = "Patients: " + utils.returnFormattedDecimal(TotalPatients.ToString()); if (lblFooterInvoiced != null) lblFooterInvoiced.Text = utils.returnFormattedDecimal(totalInvoiced.ToString()); if (lblFooterPaid != null) lblFooterPaid.Text = utils.returnFormattedDecimal(totalPaid.ToString()); if (lblFooterDue != null) lblFooterDue.Text = utils.returnFormattedDecimal(totalBalance.ToString()); if (lblFooterCurrent != null) lblFooterCurrent.Text = utils.returnFormattedDecimal(totalCurrent.ToString()); if (lblFooter30 != null) lblFooter30.Text = utils.returnFormattedDecimal(total30Days.ToString()); if (lblFooter60 != null) lblFooter60.Text = utils.returnFormattedDecimal(total60Days.ToString()); if (lblFooter90 != null) lblFooter90.Text = utils.returnFormattedDecimal(total90Days.ToString()); if (lblFooter120 != null) lblFooter120.Text = utils.returnFormattedDecimal(total120Days.ToString()); } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Debtors Age Summary Report Item Data Bound /// /// /// protected void rptDebtorsAgeSummaryReport_ItemDataBound(object sender, RepeaterItemEventArgs e) { try { if (e.Item.ItemType == ListItemType.Footer) { DateTime ageDate = utils.formatStringToDate(txtFromDate.Value); //CVH 2017-12-05 Change filter option from 4 to 1 //fetch Age Report Result DataTable DebtorsResult = xData.CreateDebtorsAgeReportResult(ageDate, 1, false); //cast rows var rows = DebtorsResult.Rows.Cast(); //get totals decimal totalInvoiced = rows.Select(r => utils.DecimalParse(r["Invoiced"])).Sum(); decimal totalPaid = rows.Select(r => utils.DecimalParse(r["Paid"])).Sum(); decimal totalBalance = rows.Select(r => utils.DecimalParse(r["Due"])).Sum(); decimal totalCurrent = rows.Select(r => utils.DecimalParse(r["Current"])).Sum(); decimal total30Days = rows.Select(r => utils.DecimalParse(r["d30"])).Sum(); decimal total60Days = rows.Select(r => utils.DecimalParse(r["d60"])).Sum(); decimal total90Days = rows.Select(r => utils.DecimalParse(r["d90"])).Sum(); decimal total120Days = rows.Select(r => utils.DecimalParse(r["d120"])).Sum(); int TotalPatients = rows.Select(r => utils.DecimalParse(r["PatientNum"])).Count(); Label lblFooterPatients = (Label)e.Item.FindControl("lblFooterPatients"); Label lblFooterInvoiced = (Label)e.Item.FindControl("lblFooterInvoiced"); Label lblFooterPaid = (Label)e.Item.FindControl("lblFooterPaid"); Label lblFooterDue = (Label)e.Item.FindControl("lblFooterDue"); Label lblFooterCurrent = (Label)e.Item.FindControl("lblFooterCurrent"); Label lblFooter30 = (Label)e.Item.FindControl("lblFooter30"); Label lblFooter60 = (Label)e.Item.FindControl("lblFooter60"); Label lblFooter90 = (Label)e.Item.FindControl("lblFooter90"); Label lblFooter120 = (Label)e.Item.FindControl("lblFooter120"); if (lblFooterPatients != null) lblFooterPatients.Text = "Patients: " + utils.returnFormattedDecimal(TotalPatients.ToString()); if (lblFooterInvoiced != null) lblFooterInvoiced.Text = utils.returnFormattedDecimal(totalInvoiced.ToString()); if (lblFooterPaid != null) lblFooterPaid.Text = utils.returnFormattedDecimal(totalPaid.ToString()); if (lblFooterDue != null) lblFooterDue.Text = utils.returnFormattedDecimal(totalBalance.ToString()); if (lblFooterCurrent != null) lblFooterCurrent.Text = utils.returnFormattedDecimal(totalCurrent.ToString()); if (lblFooter30 != null) lblFooter30.Text = utils.returnFormattedDecimal(total30Days.ToString()); if (lblFooter60 != null) lblFooter60.Text = utils.returnFormattedDecimal(total60Days.ToString()); if (lblFooter90 != null) lblFooter90.Text = utils.returnFormattedDecimal(total90Days.ToString()); if (lblFooter120 != null) lblFooter120.Text = utils.returnFormattedDecimal(total120Days.ToString()); } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Month End Statement Run Item Data Bound /// /// /// protected void rptMonthEndStatementRun_ItemDataBound(object sender, RepeaterItemEventArgs e) { try { if (e.Item.ItemType == ListItemType.Footer) { //fetch Age Report Result DataTable DebtorsResult = xData.CreateDebtorsAgeReportResult(System.DateTime.Now, 2); //cast rows var rows = DebtorsResult.Rows.Cast(); //get totals decimal totalInvoiced = rows.Select(r => utils.DecimalParse(r["Invoiced"])).Sum(); decimal totalPaid = rows.Select(r => utils.DecimalParse(r["Paid"])).Sum(); decimal totalBalance = rows.Select(r => utils.DecimalParse(r["Due"])).Sum(); decimal totalCurrent = rows.Select(r => utils.DecimalParse(r["Current"])).Sum(); decimal total30Days = rows.Select(r => utils.DecimalParse(r["d30"])).Sum(); decimal total60Days = rows.Select(r => utils.DecimalParse(r["d60"])).Sum(); decimal total90Days = rows.Select(r => utils.DecimalParse(r["d90"])).Sum(); decimal total120Days = rows.Select(r => utils.DecimalParse(r["d120"])).Sum(); int TotalPatients = rows.Select(r => utils.DecimalParse(r["PatientNum"])).Count(); Label lblFooterPatients = (Label)e.Item.FindControl("lblFooterPatients"); Label lblFooterInvoiced = (Label)e.Item.FindControl("lblFooterInvoiced"); Label lblFooterPaid = (Label)e.Item.FindControl("lblFooterPaid"); Label lblFooterDue = (Label)e.Item.FindControl("lblFooterDue"); Label lblFooterCurrent = (Label)e.Item.FindControl("lblFooterCurrent"); Label lblFooter30 = (Label)e.Item.FindControl("lblFooter30"); Label lblFooter60 = (Label)e.Item.FindControl("lblFooter60"); Label lblFooter90 = (Label)e.Item.FindControl("lblFooter90"); Label lblFooter120 = (Label)e.Item.FindControl("lblFooter120"); if (lblFooterPatients != null) lblFooterPatients.Text = "Patients: " + utils.returnFormattedDecimal(TotalPatients.ToString()); if (lblFooterInvoiced != null) lblFooterInvoiced.Text = utils.returnFormattedDecimal(totalInvoiced.ToString()); if (lblFooterPaid != null) lblFooterPaid.Text = utils.returnFormattedDecimal(totalPaid.ToString()); if (lblFooterDue != null) lblFooterDue.Text = utils.returnFormattedDecimal(totalBalance.ToString()); if (lblFooterCurrent != null) lblFooterCurrent.Text = utils.returnFormattedDecimal(totalCurrent.ToString()); if (lblFooter30 != null) lblFooter30.Text = utils.returnFormattedDecimal(total30Days.ToString()); if (lblFooter60 != null) lblFooter60.Text = utils.returnFormattedDecimal(total60Days.ToString()); if (lblFooter90 != null) lblFooter90.Text = utils.returnFormattedDecimal(total90Days.ToString()); if (lblFooter120 != null) lblFooter120.Text = utils.returnFormattedDecimal(total120Days.ToString()); } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Debtors Assisted Report Item Data Bound /// /// /// protected void rptDebtorsAssistedReport_ItemDataBound(object sender, RepeaterItemEventArgs e) { try { if (e.Item.ItemType == ListItemType.Footer) { //fetch Age Report Result DataTable dt = xData.CreateDebtorsAssistedReportResult(); //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalDueAssistantIncl = rows.Select(r => utils.DecimalParse(r["DueToAssistantIncl"])).Sum(); decimal totalVAT = rows.Select(r => utils.DecimalParse(r["VAT"])).Sum(); decimal totalDueAssistantExcl = rows.Select(r => utils.DecimalParse(r["DueToAssistantExcl"])).Sum(); decimal totalPayAssistant = rows.Select(r => utils.DecimalParse(r["PayableToAssistant"])).Sum(); Label lblFooterAssTotals = (Label)e.Item.FindControl("lblFooterAssTotals"); Label lblFooterDueAssistantIncl = (Label)e.Item.FindControl("lblFooterDueAssistantIncl"); Label lblFooterVAT = (Label)e.Item.FindControl("lblFooterVAT"); Label lblFooterDueAssistantExcl = (Label)e.Item.FindControl("lblFooterDueAssistantExcl"); Label lblFooterPayAssistant = (Label)e.Item.FindControl("lblFooterPayAssistant"); if (lblFooterAssTotals != null) lblFooterAssTotals.Text = "Totals:"; if (lblFooterDueAssistantIncl != null) lblFooterDueAssistantIncl.Text = utils.returnFormattedDecimal(totalDueAssistantIncl.ToString()); if (lblFooterVAT != null) lblFooterVAT.Text = utils.returnFormattedDecimal(totalVAT.ToString()); if (lblFooterDueAssistantExcl != null) lblFooterDueAssistantExcl.Text = utils.returnFormattedDecimal(totalDueAssistantExcl.ToString()); if (lblFooterPayAssistant != null) lblFooterPayAssistant.Text = utils.returnFormattedDecimal(totalPayAssistant.ToString()); } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Debtors Assisted Report Summary Item Data Bound /// /// /// protected void rptDebtorsAssistedReportSummary_ItemDataBound(object sender, RepeaterItemEventArgs e) { try { if (e.Item.ItemType == ListItemType.Footer) { //fetch Age Report Result DataTable dt = CreateAssistedReportSummary(); //cast rows var rows = dt.Rows.Cast(); //get totals decimal totalDueAssistantIncl = rows.Select(r => utils.DecimalParse(r["DueToAssistantIncl"])).Sum(); decimal totalVAT = rows.Select(r => utils.DecimalParse(r["VAT"])).Sum(); decimal totalDueAssistantExcl = rows.Select(r => utils.DecimalParse(r["DueToAssistantExcl"])).Sum(); decimal totalPayAssistant = rows.Select(r => utils.DecimalParse(r["PayableToAssistant"])).Sum(); decimal totalPaidYTD = rows.Select(r => utils.DecimalParse(r["PaidYTD"])).Sum(); decimal totalPaidYTDPrev = rows.Select(r => utils.DecimalParse(r["PaidYTDPrev"])).Sum(); Label lblFooterDueAssistantIncl = (Label)e.Item.FindControl("lblFooterDueAssistantIncl"); Label lblFooterVAT = (Label)e.Item.FindControl("lblFooterVAT"); Label lblFooterDueAssistantExcl = (Label)e.Item.FindControl("lblFooterDueAssistantExcl"); Label lblFooterPayAssistant = (Label)e.Item.FindControl("lblFooterPayAssistant"); Label lblFooterPaidYTD = (Label)e.Item.FindControl("lblFooterAssistantPaidYTD"); Label lblFooterPaidYTDPrev = (Label)e.Item.FindControl("lblFooterAssistantPaidYTDPrev"); if (lblFooterDueAssistantIncl != null) lblFooterDueAssistantIncl.Text = utils.returnFormattedDecimal(totalDueAssistantIncl.ToString()); if (lblFooterVAT != null) lblFooterVAT.Text = utils.returnFormattedDecimal(totalVAT.ToString()); if (lblFooterDueAssistantExcl != null) lblFooterDueAssistantExcl.Text = utils.returnFormattedDecimal(totalDueAssistantExcl.ToString()); if (lblFooterPayAssistant != null) lblFooterPayAssistant.Text = utils.returnFormattedDecimal(totalPayAssistant.ToString()); if (lblFooterPaidYTD != null) lblFooterPaidYTD.Text = utils.returnFormattedDecimal(totalPaidYTD.ToString()); if (lblFooterPaidYTDPrev != null) lblFooterPaidYTDPrev.Text = utils.returnFormattedDecimal(totalPaidYTDPrev.ToString()); } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } protected void rptInvoiceTransactions_ItemDataBound(object sender, RepeaterItemEventArgs e) { try { if (e.Item.ItemType == ListItemType.Footer) { DataTable dt = GetInvoiceListings(); var rows = dt.Rows.Cast(); //get totals decimal totalVAT = rows.Select(r => utils.DecimalParse(r["VatAmount"])).Sum(); decimal totalAmount = rows.Select(r => utils.DecimalParse(r["TotalBilling"])).Sum(); Label lblFooterVAT = (Label)e.Item.FindControl("lblFooterVAT"); Label lblFooterAmount = (Label)e.Item.FindControl("lblFooterAmount"); if (lblFooterVAT != null) lblFooterVAT.Text = utils.returnFormattedDecimal(totalVAT.ToString()); if (lblFooterAmount != null) lblFooterAmount.Text = utils.returnFormattedDecimal(totalAmount.ToString()); } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } protected void rptReceiptTransactions_ItemDataBound(object sender, RepeaterItemEventArgs e) { try { if (e.Item.ItemType == ListItemType.Footer) { DataTable dt = GetReceiptListings(); var rows = dt.Rows.Cast(); //get totals decimal totalAmount = rows.Select(r => utils.DecimalParse(r["TotalReceiptedAmount"])).Sum(); Label lblFooterAmount = (Label)e.Item.FindControl("lblFooterAmount"); if (lblFooterAmount != null) lblFooterAmount.Text = utils.returnFormattedDecimal(totalAmount.ToString()); } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } protected void rptDebitTransactions_ItemDataBound(object sender, RepeaterItemEventArgs e) { try { if (e.Item.ItemType == ListItemType.Footer) { DataTable dt = GetDebitListings(); var rows = dt.Rows.Cast(); //get totals decimal totalVAT = rows.Select(r => utils.DecimalParse(r["VatAmount"])).Sum(); decimal totalAmount = rows.Select(r => utils.DecimalParse(r["TotalDebitNoteAmount"])).Sum(); Label lblFooterVAT = (Label)e.Item.FindControl("lblFooterVAT"); Label lblFooterAmount = (Label)e.Item.FindControl("lblFooterAmount"); if (lblFooterVAT != null) lblFooterVAT.Text = utils.returnFormattedDecimal(totalVAT.ToString()); if (lblFooterAmount != null) lblFooterAmount.Text = utils.returnFormattedDecimal(totalAmount.ToString()); } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } protected void rptCreditTransactions_ItemDataBound(object sender, RepeaterItemEventArgs e) { try { if (e.Item.ItemType == ListItemType.Footer) { DataTable dt = GetCreditListings(); var rows = dt.Rows.Cast(); //get totals decimal totalVAT = rows.Select(r => utils.DecimalParse(r["VatAmount"])).Sum(); decimal totalAmount = rows.Select(r => utils.DecimalParse(r["TotalCreditNoteAmount"])).Sum(); Label lblFooterVAT = (Label)e.Item.FindControl("lblFooterVAT"); Label lblFooterAmount = (Label)e.Item.FindControl("lblFooterAmount"); if (lblFooterVAT != null) lblFooterVAT.Text = utils.returnFormattedDecimal(totalVAT.ToString()); if (lblFooterAmount != null) lblFooterAmount.Text = utils.returnFormattedDecimal(totalAmount.ToString()); } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } protected void rptDebtorsAssistedReportSummary_ItemCreated(object sender, RepeaterItemEventArgs e) { try { if (e.Item.ItemType == ListItemType.Header) { Label AssistedPaidYTDPrevHdr = (Label)e.Item.FindControl("AssistedPaidYTDPrevHdr"); Label AssistedPaidYTDHdr = (Label)e.Item.FindControl("AssistedPaidYTDHdr"); if (AssistedPaidYTDHdr != null) AssistedPaidYTDHdr.Text += " " + System.DateTime.Now.Year; if (AssistedPaidYTDPrevHdr != null) AssistedPaidYTDPrevHdr.Text += " " + System.DateTime.Now.AddYears(-1).Year; } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Changed the checked status of the repeater items /// /// /// protected void chkAll_CheckedChanged(object sender, EventArgs e) { CheckBox chkAll = (CheckBox)sender; if (chkAll != null) { foreach (RepeaterItem item in rptMonthEndStatementRun.Items) { CheckBox chk = (CheckBox)item.FindControl("chkSelected"); if (chk != null) chk.Checked = chkAll.Checked; } } } /// /// Download a temporary Statement history from the file system /// /// /// protected void lnkStatementHistory_Click(object sender, EventArgs e) { CreateStatementHistory(); } protected void ddReportType_SelectedIndexChanged(object sender, EventArgs e) { try { lblResultSelection.Text = ""; pnlResultSelection.Visible = false; if (ddReportType.SelectedValue == "Listings - Invoices" || ddReportType.SelectedValue == "Listings - Debit Notes" || ddReportType.SelectedValue == "Listings - Credit Notes" || ddReportType.SelectedValue == "Listings - Receipts" || ddReportType.SelectedValue == "Financial Transaction Summary" || ddReportType.SelectedValue == "Medical Aid Summary Report") { /* CVH 2016-06-27 Only set default date on first load, need to keep selected dates on selection changed also when switching from age report where only from date is visible also when from date is current date */ if (txtFromDate.Value == "" || txtToDate.Value == "" || txtFromDate.Value == System.DateTime.Now.ToString("dd/MM/yyyy") || (txtFromDate.Visible && !txtToDate.Visible)) { //txtFromDate.Value = DateTime.Now.AddMonths(-3).ToString("dd/MM/yyyy"); //txtToDate.Value = DateTime.Now.ToString("dd/MM/yyyy"); if (ddReportType.SelectedValue == "Financial Transaction Summary") { txtFromDate.Value = DateTime.Now.ToString("dd/MM/yyyy"); txtToDate.Value = DateTime.Now.ToString("dd/MM/yyyy"); } else { txtFromDate.Value = "01/" + DateTime.Now.AddMonths(-1).ToString("MM/yyyy"); txtToDate.Value = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-01")).AddDays(-1).ToString("dd/MM/yyyy"); } } divFromDate.Visible = true; divToDate.Visible = true; divRemittanceFilters.Visible = false; } else if (ddReportType.SelectedValue == "Listings - Remittance Advices") { if (txtFromDate.Value == "" || txtToDate.Value == "" || txtFromDate.Value == System.DateTime.Now.ToString("dd/MM/yyyy") || (txtFromDate.Visible && !txtToDate.Visible)) { txtFromDate.Value = System.DateTime.Now.AddMonths(-1).ToString("dd/MM/yyyy"); txtToDate.Value = System.DateTime.Now.ToString("dd/MM/yyyy"); } divFromDate.Visible = true; divToDate.Visible = true; BindRemittanceFilters(); divRemittanceFilters.Visible = true; } else if (ddReportType.SelectedValue == "Debtors Age Report" || ddReportType.SelectedValue == "Debtors Age Summary Report") { txtFromDate.Value = DateTime.Now.ToString("dd/MM/yyyy"); divFromDate.Visible = true; txtToDate.Value = DateTime.Now.ToString("dd/MM/yyyy"); divToDate.Visible = false; divRemittanceFilters.Visible = false; } else { divFromDate.Visible = false; divToDate.Visible = false; divRemittanceFilters.Visible = false; } btnGenerate.Visible = ddReportType.SelectedValue != "Statement History"; SetVisibleRepeater(""); lnkExportToExcel.Visible = ddReportType.SelectedValue == "Statement History"; } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } protected void btnGenerate_Click(object sender, EventArgs e) { try { switch (ddReportType.SelectedValue) { case "Debtors Age Report": DebtorsAgeReportToGrid(); break; case "Debtors Age Summary Report": DebtorsAgeSummaryReportToGrid(); break; case "Month End Statement Run": MonthEndStatementRunToGrid(); break; case "Assistant Report": DebtorsAssistedReportToGrid(); break; case "Assistant Summary Report": DebtorsAssistedReportSummaryToGrid(); break; case "Listings - Invoices": BindInvoiceListings(); break; case "Listings - Debit Notes": BindDebitNoteListings(); break; case "Listings - Credit Notes": BindCreditNoteListings(); break; case "Listings - Receipts": BindReceiptListings(); break; case "Listings - Remittance Advices": BindRemittanceListings(); break; case "Recon": BindRecon(); break; case "Financial Transaction Summary": BindFinancialTransactionSummary(); break; case "Medical Aid Summary Report": BindMedicalAidSummary(); break; default: break; } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } protected void lnkExportToExcel_Click(object sender, EventArgs e) { try { switch (ddReportType.SelectedValue) { case "Debtors Age Report": DebtorsAgeReportToExcel(); break; case "Debtors Age Summary Report": DebtorsAgeSummaryReportToExcel(); break; case "Assistant Report": DebtorsAssistedReportToExcel(); break; case "Assistant Summary Report": DebtorsAssistedSummaryReportToExcel(); break; case "Statement History": CreateStatementHistory(); break; case "Listings - Invoices": ExportInvoiceListings(); break; case "Listings - Debit Notes": ExportDebitNoteListings(); break; case "Listings - Credit Notes": ExportCreditNoteListings(); break; case "Listings - Receipts": ExportReceiptListings(); break; case "Listings - Remittance Advices": ExportRemittanceListings(); break; case "Recon": ExportRecon(); break; case "Financial Transaction Summary": ExportFinancialTransactionSummary(); break; case "Medical Aid Summary Report": ExportMedicalAidSummary(); break; default: break; } } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } /// /// Email Statements /// /// /// protected void lnkEmailStatements_Click(object sender, EventArgs e) { try { lblEmailModalResult.Text = ""; pnlEmailModalResult.Visible = false; ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "myBulkStatementModal", "$('#modEmailStatements').modal();", true); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } protected void btnModalEmailStatements_Click(object sender, EventArgs e) { try { if (!utils.verifySession("user")) { Response.Redirect("/home", false); return; } if (ddReportType.SelectedValue == "Month End Statement Run") { string emailFailed = ""; //email statements to all selected accounts foreach (RepeaterItem item in rptMonthEndStatementRun.Items) { CheckBox chk = (CheckBox)item.FindControl("chkSelected"); if (chk != null && chk.Checked) { //find account Label lblPatientNumber = (Label)item.FindControl("lblPatientNumber"); if (lblPatientNumber != null) { oAccount mainAccount = new oAccount(); foreach (oAccount acc in xData.GetTypedByCriteriaSpecific("recId", typeof(oAccount), "patientNumber", lblPatientNumber.Text, "recId DESC")) { mainAccount = acc; break; } if (mainAccount.recId == 0) throw new Exception("Account for patient number " + lblPatientNumber.Text + " could not be found."); //CVH 2016-11-17 Find Notes Surface Field Name on Patients grid to link statement note to string noteSurfaceFieldName = ""; foreach (oSurfaceItem surfaceItem in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceItem), "recId", mainAccount.surfaceItemId.ToString())) { foreach (oSurfaceField noteField in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceField), "surfaceId,surfaceFieldTypeId,isActive", surfaceItem.surfaceId + "," + pNums.FieldType.Note.GetHashCode() + ",1")) { noteSurfaceFieldName = noteField.surfaceFieldName; } } int userId = ((oUser)Session["user"]).recId; string additionalEmailMessage = txtEmailBodyMessage.InnerText; int bbfDays = 0; bool showCorrections = false; int maxLines = 15; bool fromZeroBalance = true; bool validAccountEmail = false; bool validPracticeEmail = false; //not used, only emailing patient bool validMedicalAidEmail = false; //not used, only emailing patient bool success = xDebtors.EmailAccountStatement(mainAccount, ConfigurationManager.AppSettings["from"], ConfigurationManager.AppSettings["bcc"], ConfigurationManager.AppSettings["admin"], ConfigurationManager.AppSettings["WebAddy"], additionalEmailMessage, bbfDays, showCorrections, maxLines, fromZeroBalance, userId, System.DateTime.Now,false, System.DateTime.Now, System.DateTime.Now, "Month End Account Update", true, false, false, out validAccountEmail, out validPracticeEmail, out validMedicalAidEmail, noteSurfaceFieldName); if (!success || !validAccountEmail) { if (emailFailed == "") emailFailed = mainAccount.patientNumber + " - " + mainAccount.surname; else emailFailed += ", " + mainAccount.patientNumber + " - " + mainAccount.surname; } } } } if (emailFailed != "") { lblEmailModalResult.Text = "One or more emails could not be sent. Sending failed for the following accounts: " + emailFailed; pnlEmailModalResult.Visible = true; } else { lblEmailModalResult.Text = "The statements have been emailed."; pnlEmailModalResult.Visible = true; } } ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "myBulkStatementModalKeep", "$('#modEmailStatements').modal();", true); } catch (Exception ex) { exception.HandleException("debtors Reports:", MethodBase.GetCurrentMethod().Name, ex, 0); Response.Redirect("/error", false); } } #endregion }