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 _pilotind, string _filename, string yrmo)
{
HRAExcelReport xlObj = new HRAExcelReport();
DataSet ds = new DataSet(); ds.Clear();
DataSet dsTemp = new DataSet(); dsTemp.Clear();
string[] sheetnames, titles, YRMOs;
string[][] colsFormat, colsName;
int[][] colsWidth;
string _content = "";
switch (_source)
{
case "HOME_YEB_RET":
//Home Mailing List Non Pilots
sheetnames = new string[] { "Home_YEB_RET" + _pilotind + "_Detail" };
titles = new string[] { yrmo + "_Home_YEB_RET" + _pilotind + " Date Report Run : " + DateTime.Now.ToString() };
colsName = new string[][] { new string[] { "YRMO", "PilotInd", "SourceCd", "TypeCd", "Name", "SSNO", "Addr1", "Addr2", "City", "State", "Zipcode", "EENO", "YebOnline", "ExpatFlag" } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "number", "string", "string" } };
colsWidth = new int[][] { new int[] { 55, 140, 70, 60, 190, 65, 170, 60, 110, 40, 60, 60, 40, 40 } };
dsTemp = ImportYEBData.ProcessReportData("HOME_YEB_RET", yrmo, _pilotind); dsTemp.Tables[0].TableName = "DetailTable";
if (dsTemp.Tables[0].Rows.Count != 0)
{
DataRow row;
row = dsTemp.Tables[0].NewRow();
row["Pilotind"] = "** Total Number of Records:";
row["Sourcecd"] = dsTemp.Tables[0].Compute("count([yrmo])", "yrmo = '" + yrmo + "'");
dsTemp.Tables[0].Rows.Add(row);
}
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "DetailTableF"; dsTemp.Clear();
_content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
_content = LetterGen.replaceIllegalXMLCharacters(_content);
break;
case "HOME_YEB_NONRET":
//Home Mailing List Non Pilots
sheetnames = new string[] { "Home_YEB_NONRET" + _pilotind + "_Detail" };
titles = new string[] { yrmo + "_Home_YEB_NONRET" + _pilotind + " Date Report Run : " + DateTime.Now.ToString() };
colsName = new string[][] { new string[] { "YRMO", "PilotInd", "SourceCd", "TypeCd", "Name", "SSNO", "Addr1", "Addr2", "City", "State", "Zipcode", "EENO", "YebOnline", "ExpatFlag" } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "number", "string", "string" } };
colsWidth = new int[][] { new int[] { 55, 140, 70, 60, 190, 65, 170, 60, 110, 40, 60, 60, 40, 40 } };
dsTemp = ImportYEBData.ProcessReportData("HOME_YEB_NONRET", yrmo, _pilotind); dsTemp.Tables[0].TableName = "DetailTable";
if (dsTemp.Tables[0].Rows.Count != 0)
{
DataRow row;
row = dsTemp.Tables[0].NewRow();
row["Pilotind"] = "** Total Number of Records:";
row["Sourcecd"] = dsTemp.Tables[0].Compute("count([yrmo])", "yrmo = '" + yrmo + "'");
dsTemp.Tables[0].Rows.Add(row);
}
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "DetailTableF"; dsTemp.Clear();
_content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
_content = LetterGen.replaceIllegalXMLCharacters(_content);
break;
case "HOME_SAR_ONLY":
sheetnames = new string[] { "HOME_SAROnly_" + _pilotind + "Detail" };
titles = new string[] { yrmo + "_HOME_SAROnly_" + _pilotind + " Date Report Run : " + DateTime.Now.ToString() };
colsName = new string[][] { new string[] { "YRMO", "PilotInd", "SourceCd", "TypeCd", "Name", "SSNO", "Addr1", "Addr2", "City", "State", "Zipcode", "EENO", "YebOnline", "ExpatFlag" } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "number", "string", "string" } };
colsWidth = new int[][] { new int[] { 55, 140, 70, 60, 190, 65, 170, 60, 110, 40, 60, 60, 40, 40 } };
dsTemp = ImportYEBData.ProcessReportData("HOME_SAR_ONLY", yrmo, _pilotind); dsTemp.Tables[0].TableName = "DetailTable";
if (dsTemp.Tables[0].Rows.Count != 0)
{
DataRow row;
row = dsTemp.Tables[0].NewRow();
row["Pilotind"] = "** Total Number of Records:";
row["Sourcecd"] = dsTemp.Tables[0].Compute("count([yrmo])", "yrmo = '" + yrmo + "'");
dsTemp.Tables[0].Rows.Add(row);
}
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "DetailTableF"; dsTemp.Clear();
_content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
_content = LetterGen.replaceIllegalXMLCharacters(_content);
break;
case "Active_YEB_Group1":
sheetnames = new string[] { "Active_YEB_Group1_" + _pilotind + "Detail" };
titles = new string[] { yrmo + "_Active_YEB_Group1_" + _pilotind + " Date Report Run : " + DateTime.Now.ToString() };
colsName = new string[][] { new string[] { "YRMO", "PilotInd", "SourceCd", "TypeCd", "Name", "SSNO", "Addr1", "Addr2", "City", "State", "Zipcode", "EENO", "YebOnline", "ExpatFlag" } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "number", "string", "string" } };
colsWidth = new int[][] { new int[] { 55, 140, 70, 60, 190, 65, 170, 60, 110, 40, 60, 60, 40, 40 } };
dsTemp = ImportYEBData.ProcessReportData("Active_YEB_Group1", yrmo, _pilotind); dsTemp.Tables[0].TableName = "DetailTable";
if (dsTemp.Tables[0].Rows.Count != 0)
{
DataRow row;
row = dsTemp.Tables[0].NewRow();
row["Pilotind"] = "** Total Number of Records:";
row["Sourcecd"] = dsTemp.Tables[0].Compute("count([yrmo])", "yrmo = '" + yrmo + "'");
dsTemp.Tables[0].Rows.Add(row);
}
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "DetailTableF"; dsTemp.Clear(); dsTemp.Dispose();
_content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
_content = LetterGen.replaceIllegalXMLCharacters(_content);
break;
case "Active_YEB_Group2":
sheetnames = new string[] { "Active_YEB_Group2_" + _pilotind + "Detail" };
titles = new string[] { yrmo + "_Active_YEB_Group2_" + _pilotind + " Date Report Run : " + DateTime.Now.ToString() };
colsName = new string[][] { new string[] { "YRMO", "PilotInd", "SourceCd", "TypeCd", "Name", "SSNO", "Addr1", "Addr2", "City", "State", "Zipcode", "EENO", "YebOnline", "ExpatFlag" } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "number", "string", "string" } };
colsWidth = new int[][] { new int[] { 55, 140, 70, 60, 190, 65, 170, 60, 110, 40, 60, 60, 40, 40 } };
dsTemp = ImportYEBData.ProcessReportData("Active_YEB_Group2", yrmo, _pilotind); dsTemp.Tables[0].TableName = "DetailTable";
if (dsTemp.Tables[0].Rows.Count != 0)
{
DataRow row;
row = dsTemp.Tables[0].NewRow();
row["Pilotind"] = "** Total Number of Records:";
row["SourceCd"] = dsTemp.Tables[0].Compute("count([yrmo])", "yrmo = '" + yrmo + "'");
dsTemp.Tables[0].Rows.Add(row);
}
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "DetailTableF"; dsTemp.Clear(); dsTemp.Dispose();
_content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
_content = LetterGen.replaceIllegalXMLCharacters(_content);
break;
case "Active_SAROnly":
sheetnames = new string[] { "Active_SAROnly_" + _pilotind + "Detail" };
titles = new string[] { yrmo + "_Active_SARonly_" + _pilotind + " Date Report Run : " + DateTime.Now.ToString() };
colsName = new string[][] { new string[] { "YRMO", "PilotInd", "SourceCd", "TypeCd", "Name", "SSNO", "Addr1", "Addr2", "City", "State", "Zipcode", "EENO", "YebOnline", "ExpatFlag" } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "number", "string", "string" } };
colsWidth = new int[][] { new int[] { 55, 140, 70, 60, 190, 65, 170, 60, 110, 40, 60, 60, 40, 40 } };
dsTemp = ImportYEBData.ProcessReportData("Active_SAROnly", yrmo, _pilotind); dsTemp.Tables[0].TableName = "DetailTable";
if (dsTemp.Tables[0].Rows.Count != 0)
{
DataRow row;
row = dsTemp.Tables[0].NewRow();
row["Pilotind"] = "** Total Number of Records:";
row["Sourcecd"] = dsTemp.Tables[0].Compute("count([yrmo])", "yrmo = '" + yrmo + "'");
dsTemp.Tables[0].Rows.Add(row);
}
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "DetailTableF"; dsTemp.Clear();
_content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
_content = LetterGen.replaceIllegalXMLCharacters(_content);
break;
case "Active_PROnly":
sheetnames = new string[] { "Active_YEB_" + _pilotind + "PRonly" + "Detail" };
titles = new string[] { yrmo + "_Active_YEB_" + _pilotind + "PRonly" + " Date Report Run : " + DateTime.Now.ToString() };
colsName = new string[][] { new string[] { "YRMO", "PilotInd", "SourceCd", "TypeCd", "Name", "SSNO", "Addr1", "Addr2", "City", "State", "Zipcode", "EENO", "YebOnline", "ExpatFlag" } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "number", "string", "string" } };
colsWidth = new int[][] { new int[] { 55, 140, 70, 60, 190, 65, 170, 60, 110, 40, 60, 60, 40, 40 } };
dsTemp = ImportYEBData.ProcessReportData("Active_PROnly", yrmo, _pilotind); dsTemp.Tables[0].TableName = "DetailTable";
if (dsTemp.Tables[0].Rows.Count != 0)
{
DataRow row;
row = dsTemp.Tables[0].NewRow();
row["Pilotind"] = "** Total Number of Records:";
row["Sourcecd"] = dsTemp.Tables[0].Compute("count([yrmo])", "yrmo = '" + yrmo + "'");
dsTemp.Tables[0].Rows.Add(row);
}
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "DetailTableF"; dsTemp.Clear();
_content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
_content = LetterGen.replaceIllegalXMLCharacters(_content);
break;
case "DUP_SSN":
sheetnames = new string[] { "DUPLICATES_SSN_ONLY_" + _pilotind + "Detail" };
titles = new string[] { yrmo + "_SSNDuplicates_YEB_" + _pilotind + " Date Report Run : " + DateTime.Now.ToString() };
colsName = new string[][] { new string[] { "Rectype", "YRMO", "PilotInd", "SourceCd", "TypeCd", "Name", "SSNO", "Addr1", "Addr2", "City", "State", "Zipcode", "EENO" } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "number" } };
colsWidth = new int[][] { new int[] { 65, 55, 140, 70, 60, 190, 65, 170, 60, 110, 40, 60, 60 } };
dsTemp = ImportYEBData.ProcessReportData("DUP_SSN", yrmo, _pilotind); dsTemp.Tables[0].TableName = "DetailTable";
if (dsTemp.Tables[0].Rows.Count != 0)
{
DataRow row;
row = dsTemp.Tables[0].NewRow();
row["Pilotind"] = "** Total Number of Records:";
row["Sourcecd"] = dsTemp.Tables[0].Compute("count([yrmo])", "yrmo = '" + yrmo + "'");
dsTemp.Tables[0].Rows.Add(row);
}
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "DetailTableF"; dsTemp.Clear();
_content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
_content = LetterGen.replaceIllegalXMLCharacters(_content);
break;
case "DUP_ADDR1":
sheetnames = new string[] { "DUPLICATES_ADDR1_ONLY_" + _pilotind + "Detail" };
titles = new string[] { yrmo + "_ADDR1Duplicates_YEB_" + _pilotind + " Date Report Run : " + DateTime.Now.ToString() };
colsName = new string[][] { new string[] { "Rectype", "YRMO", "PilotInd", "SourceCd", "TypeCd", "Name", "SSNO", "Addr1", "Addr2", "City", "State", "Zipcode", "EENO" } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "number" } };
colsWidth = new int[][] { new int[] { 65, 55, 140, 70, 60, 190, 65, 170, 60, 110, 40, 60, 60 } };
dsTemp = ImportYEBData.ProcessReportData("DUP_ADDR1", yrmo, _pilotind); dsTemp.Tables[0].TableName = "DetailTable";
if (dsTemp.Tables[0].Rows.Count != 0)
{
DataRow row;
row = dsTemp.Tables[0].NewRow();
row["Pilotind"] = "** Total Number of Records:";
row["Sourcecd"] = dsTemp.Tables[0].Compute("count([yrmo])", "yrmo = '" + yrmo + "'");
dsTemp.Tables[0].Rows.Add(row);
}
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "DetailTableF"; dsTemp.Clear();
_content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
_content = LetterGen.replaceIllegalXMLCharacters(_content);
break;
}
return(_content);
}