public static DataSet GetReportContentDS(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 = "";
DataSet results = new DataSet();
XmlDocument doc = new XmlDocument();
switch (_source)
{
case "HOME_YEB":
//Home Mailing List Non Pilots
sheetnames = new string[] { "Home_YEB_" + _pilotind + "_Detail" };
titles = new string[] { yrmo + "_Home_YEB_" + _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", 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);
//doc.LoadXml(_content);
//Stream str;
//doc.Save(HttpContext.Current.Server.MapPath("~/uploads/") + "data.xml");
//results.ReadXml(HttpContext.Current.Server.MapPath("~/uploads/") + "data.xml");
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);
//XmlSanitizedString xmlsanit = new XmlSanitizedString(_content);
//doc.LoadXml(xmlsanit.ToString());
//doc.Save(HttpContext.Current.Server.MapPath("~/uploads/") + "data.xml");
//results.ReadXml(HttpContext.Current.Server.MapPath("~/uploads/") + "data.xml");
break;
}
return(ds);
}
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);
}