public static string GetBalReportContent(string _source, string _filename, string yrmo, DataTable dsAuditSum1, DataTable dsAuditSum2, DataTable dsSum)
{
string[] sheetnames, titles;
string[][] colsFormat;
int[][] colsWidth;
string _content = "";
HRAExcelReport xlObj = new HRAExcelReport();
switch (_source)
{
case "BalSumAudit":
DataSet ds1 = new DataSet(); ds1.Clear();
DataSet ds2 = new DataSet(); ds2.Clear();
sheetnames = new string[] { "VWA_BALSUM" };
titles = new string[] { "Balance Audit Report for YRMO : " + yrmo };
string[][] subtitles = { new string[] { "Bank Statement Summary", "Remittance Information Summary" } };
colsFormat = new string[][] { new string[] { "string", "number", "currency", "currency" }, new string[] { "string", "number", "string", "currency", "currency" } };
colsWidth = new int[][] { new int[] { 85, 85, 85, 85, 85 } };
dsAuditSum1.TableName = "BalAuditSum1"; ds1.Tables.Add(dsAuditSum1.Copy());
dsAuditSum2.TableName = "BalAuditSum2"; ds2.Tables.Add(dsAuditSum2.Copy());
_content = xlObj.ExcelXMLRpt(ds1, ds2, _filename, sheetnames, titles, subtitles, colsFormat, colsWidth);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "BalSum":
string sheetname = "VWA_BAL_XSUM";
titles = new string[] { "Balance Summary Report for YRMO : " + yrmo };
colsFormat = new string[][] { new string[] { "string", "currency", "string" } };
colsWidth = new int[][] { new int[] { 150, 90, 100 } };
_content = VWAExcelReport.ExcelXMLRpt(dsSum, _filename, sheetname, titles, colsFormat, colsWidth);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
}
return(_content);
}
public static string GetReportContent(string _source, string _filename, string yrmo)
{
DataSet ds = new DataSet();
DataSet dsTemp = new DataSet();
string[] sheetnames;
string[][] colsFormat, titles;
int[][] colsWidth;
string _content = "";
DataRow rowNew;
switch (_source)
{
case "RemitDisp":
sheetnames = new string[] { "Remit_Discrepancy" };
titles = new string[][] { new string[] { "VWA Remittance Discrepancy Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "number", "string", "currency", "currency", "currency", "currency", "string" } };
colsWidth = new int[][] { new int[] { 65, 140, 65, 65, 65, 65, 65, 65, 65, 65, 50 } };
ds = VWAImportDAL.GetRemitDiscData(yrmo);
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "RemitInput":
sheetnames = new string[] { "Remit_Summary", "Remit_Detail" };
titles = new string[][] { new string[] { "VWA Remittance Summary Report for YRMO : " + yrmo }, new string[] { "VWA Remittance Detail Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "string", "currency", "currency", "currency", "percent", "string" }, new string[] { "string", "string", "string", "string", "number", "string", "currency", "currency", "currency", "currency", "string" } };
colsWidth = new int[][] { new int[] { 120, 65, 65, 65, 65, 40 }, new int[] { 80, 130, 65, 65, 65, 65, 65, 65, 65, 65, 40 } };
dsTemp = GetRemitSumData(yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = GetRemitDetData(yrmo); dsTemp.Tables[0].TableName = "DetailTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, "! VWA Fees(VWA) plus Due Client(acme) does not equals Paid VWA(Total)");
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "BankDisp":
ds = GetBankValidData(yrmo);
_content = VWAExcelReport.BankDispExcelRpt(ds, yrmo);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranMis":
sheetnames = new string[] { "VWA_DELETED" };
titles = new string[][] { new string[] { "Missing Transactions Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "string", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency" } };
colsWidth = new int[][] { new int[] { 50, 80, 45, 80, 40, 150, 65, 130, 65, 130, 45, 65, 65, 65, 65, 65, 65, 65, 65 } };
ds = VWA_ExportDAL.GetTranOutputData("TranMis", yrmo);
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranClient":
sheetnames = new string[] { "VWA_CLIENT_SUM", "VWA_CLIENT_DET" };
titles = new string[][] { new string[] { "Change in Client Summary Report for YRMO : " + yrmo }, new string[] { "Change in Client Detail Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "number" }, new string[] { "number", "string", "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency" } };
colsWidth = new int[][] { new int[] { 50, 80, 50, 80, 45 }, new int[] { 50, 80, 50, 80, 45, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 65, 65, 65, 65 } };
dsTemp = VWA_ExportDAL.GetTranOutputData("TranClientSUM", yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranClientDET", yrmo); dsTemp.Tables[0].TableName = "DetailTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranGrp":
sheetnames = new string[] { "VWA_GROUP_SUM", "VWA_GROUP_DET" };
titles = new string[][] { new string[] { "Change in Group Summary Report for YRMO : " + yrmo }, new string[] { "Change in Group Detail Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "number" }, new string[] { "number", "string", "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency" } };
colsWidth = new int[][] { new int[] { 40, 80, 40, 80, 45 }, new int[] { 40, 80, 40, 80, 50, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 65, 65, 65, 65 } };
dsTemp = VWA_ExportDAL.GetTranOutputData("TranGrpSUM", yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranGrpDET", yrmo); dsTemp.Tables[0].TableName = "DetailTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranStat":
sheetnames = new string[] { "VWA_STATUS_SUM", "VWA_STATUS_OPEN", "VWA_STATUS_CLOSED" };
titles = new string[][] { new string[] { "Open, Closed, Pended Counts Summary Report for YRMO : " + yrmo }, new string[] { "New Cases Opened Detail Report for YRMO : " + yrmo }, new string[] { "New Cases Closed Detail Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "number", "number", "number" }, new string[] { "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency" }, new string[] { "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency" } };
colsWidth = new int[][] { new int[] { 50, 80, 45, 80, 50, 50, 50 }, new int[] { 50, 80, 45, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 65, 65, 65, 65 }, new int[] { 50, 80, 45, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 65, 65, 65, 65 } };
dsTemp = VWA_ExportDAL.GetTranOutputData("TranStatSUM", yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
rowNew = dsTemp.Tables[0].NewRow();
rowNew["Group Name"] = "TOTAL:";
rowNew["Open Counter"] = dsTemp.Tables[0].Compute("SUM([Open Counter])", String.Empty);
rowNew["Closed Counter"] = dsTemp.Tables[0].Compute("SUM([Closed Counter])", String.Empty);
rowNew["Pending Counter"] = dsTemp.Tables[0].Compute("SUM([Pending Counter])", String.Empty);
dsTemp.Tables[0].Rows.Add(rowNew);
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranStatOpenDET", yrmo); dsTemp.Tables[0].TableName = "Detail1Table";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "Detail1TableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranStatCloseDET", yrmo); dsTemp.Tables[0].TableName = "Detail2Table";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "Detail2TableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranACCTG":
sheetnames = new string[] { "VWA_ACCTG_SUM", "VWA_ACCTG_DET" };
titles = new string[][] { new string[] { "Financial Activity Summary Report for YRMO : " + yrmo }, new string[] { "Financial Activity Detail Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "number", "currency", "currency", "currency" }, new string[] { "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency", "currency", "currency", "currency", "string" } };
colsWidth = new int[][] { new int[] { 50, 80, 45, 80, 45, 65, 65, 65 }, new int[] { 50, 80, 45, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 70, 70, 70, 70, 70, 70, 70, 150 } };
dsTemp = VWA_ExportDAL.GetTranOutputData("TranACCTGSUM", yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
rowNew = dsTemp.Tables[0].NewRow();
rowNew["Group Name"] = "TOTAL:";
rowNew["Counter"] = dsTemp.Tables[0].Compute("SUM([Counter])", String.Empty);
rowNew["Recovery Amount"] = dsTemp.Tables[0].Compute("SUM([Recovery Amount])", String.Empty);
rowNew["Total Fees"] = dsTemp.Tables[0].Compute("SUM([Total Fees])", String.Empty);
rowNew["Net Amount"] = dsTemp.Tables[0].Compute("SUM([Net Amount])", String.Empty);
dsTemp.Tables[0].Rows.Add(rowNew);
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranACCTGDET", yrmo); dsTemp.Tables[0].TableName = "DetailTable";
rowNew = dsTemp.Tables[0].NewRow();
rowNew["Recovery Date"] = "TOTAL:";
rowNew["Recovered Amount Current"] = dsTemp.Tables[0].Compute("SUM([Recovered Amount Current])", String.Empty);
rowNew["TotFees Current"] = dsTemp.Tables[0].Compute("SUM([TotFees Current])", String.Empty);
rowNew["Net Amount Current"] = dsTemp.Tables[0].Compute("SUM([Net Amount Current])", String.Empty);
rowNew["Benefit Amount Total"] = dsTemp.Tables[0].Compute("SUM([Benefit Amount Total])", String.Empty);
rowNew["Recovered Amount Total"] = dsTemp.Tables[0].Compute("SUM([Recovered Amount Total])", String.Empty);
rowNew["TotFees Total"] = dsTemp.Tables[0].Compute("SUM([TotFees Total])", String.Empty);
rowNew["Net Amount Total"] = dsTemp.Tables[0].Compute("SUM([Net Amount Total])", String.Empty);
dsTemp.Tables[0].Rows.Add(rowNew);
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranAging":
if (yrmo == null || yrmo.Equals(String.Empty))
{
yrmo = "0";
}
sheetnames = new string[] { "VWA_AGING_SUM", "VWA_AGING_DET" };
titles = new string[][] { new string[] { "Aging Summary Report on " + DateTime.Today.ToString("MM/dd/yyyy") + " with aging days - " + yrmo }, new string[] { "Aging Detail Report on " + DateTime.Today.ToString("MM/dd/yyyy") + " with aging days - " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "number" }, new string[] { "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency", "number", "string" } };
colsWidth = new int[][] { new int[] { 50, 80, 45, 80, 50 }, new int[] { 50, 80, 45, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 65, 65, 65, 65, 45, 75 } };
dsTemp = VWA_ExportDAL.GetTranOutputData("TranAgingSUM", yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranAgingDET", yrmo); dsTemp.Tables[0].TableName = "DetailTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranStatCtr":
sheetnames = new string[] { "VWA_STATUSCTR_SUM" };
titles = new string[][] { new string[] { "Status Counter Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "string", "string", "number" } };
colsWidth = new int[][] { new int[] { 40, 200, 50 } };
ds = VWA_ExportDAL.GetTranOutputData("TranStatCtr", yrmo);
rowNew = ds.Tables[0].NewRow();
rowNew["Status Description"] = "TOTAL:";
rowNew["Counter"] = ds.Tables[0].Compute("SUM([Counter])", String.Empty);
ds.Tables[0].Rows.Add(rowNew);
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "CaseInfo":
//yrmo is Contract number in this case
ds = GetCaseData(yrmo);
_content = VWAExcelReport.CaseExcelRpt(ds, yrmo);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
}
return(_content);
}