protected void btn_import_Click(object sender, EventArgs e)
{
if (Session["yrmo"] != null)
{
usryrmo = Session["yrmo"].ToString();
}
if (usryrmo != "")
{
usryrmo = usryrmo;
}
lbl_error.Text = "";
string logFilePath = Server.MapPath("~/uploads/") + "YEB_Expat_" + usryrmo + ".xls";
if (Page.IsValid)
{
string strFilePath1 = "";
if (FileUpload1.GotFile)
{
try
{
string fn = System.IO.Path.GetFileName(FileUpload1.FilePost.FileName);
strFilePath1 = Server.MapPath("~/uploads/") + fn;
if (File.Exists(strFilePath1))
{
File.Delete(strFilePath1);
}
FileUpload1.FilePost.SaveAs(strFilePath1);
if (ImportFile(strFilePath1))
{
MultiView1.SetActiveView(view_result);
lbl_result.Text = "Import completed successfully -- " + _counter + " records imported";
}
}
catch (Exception ex)
{
if (File.Exists(logFilePath))
{
File.Delete(logFilePath);
}
//resultDiv.Visible = false;
ImportYEBData.Rollback(source, usryrmo);
lbl_error.Text = "Error - " + ex.Message;
}
finally
{
if (File.Exists(strFilePath1))
{
File.Delete(strFilePath1);
}
FileUpload1.FilePost.InputStream.Flush();
FileUpload1.FilePost.InputStream.Close();
FileUpload1.FilePost.InputStream.Dispose();
}
}
}
}
protected void btnYEBExpat_Click(object sender, EventArgs e)
{
if (Session["yrmo"] != null)
{
usryrmo = Session["yrmo"].ToString();
}
if (usryrmo != "")
{
try
{
_counter = ImportYEBData.updateYEBDetailData(usryrmo, "YEBEXPAT");
if (_counter >= 0)
{
MultiView1.SetActiveView(view_result);
lbl_result.Text = "Update completed successfully -- " + _counter + " records updated in YEBDetail";
}
}
catch (Exception ex)
{
lbl_error.Text = "Error - " + ex.Message;
}
}
}
protected void ddlYrmoList()
{
string prevYRMO;
for (int i = 0; i < 6; i++)
{
prevYRMO = ImportYEBData.getPrevYRMO(i);
ddlYrmo.Items.Add(prevYRMO);
}
ddlYrmo.Items.Add("New Yrmo");
}
Boolean ImportFile(string strFilePath1)
{
string _yrmo = ddlYrmo.SelectedItem.Text;
bool importStat = false;
ImportYEBData vobj = new ImportYEBData();
ImportYEBData.Rollback("COB" + Session["pilotind"].ToString(), _yrmo);
ImportYEBData.Rollback("COB", _yrmo);
_counter = vobj.importCobraNP_YEB(_yrmo, strFilePath1, Session["pilotind"].ToString());
ImportYEBData.insertImportStatus(_yrmo, source);
Session["taskId"] = Convert.ToInt32(Session["taskId"]) + 1;
Audit.auditUserTaskI(Session.SessionID, Session["mid"].ToString(), Session["taskId"].ToString(), "YEB", "YEB Imports", "YEB_Cobra", "YEB Detail", _yrmo, _counter);
importStat = true;
return(importStat);
}
protected void checkPastImport()
{
string _yrmo = ddlYrmo.SelectedItem.Text;
if (ImportYEBData.PastImport(source, _yrmo))
{
MultiView1.SetActiveView(view_reimport);
lbl_reimport.Text = "Imported already for year-month (YRMO): " + _yrmo;
bindResults();
}
else
{
MultiView1.SetActiveView(view_main);
//UpdateImportType();
}
}
protected void btn_importLOA_Click(object sender, EventArgs e)
{
DataTable dtLOA;
ImportYEBData iObj = new ImportYEBData();
if (Session["yrmo"] != null)
{
usryrmo = Session["yrmo"].ToString();
}
if (Session["pilotind"] != null)
{
pilotind = Session["pilotind"].ToString();
}
status = "EBALOA";
if (pilotind != "" && status != "")
{
try
{
//Delete any previously imported data for the specified YRMO and criteria
ImportYEBData.Rollback("LOA" + pilotind, usryrmo);
//retrieve the datatable for the criteria specified based on status
dtLOA = ImportYEBData.getYEBEmployeesbyStatus(pilotind, status.Substring(3, 3)).Tables[0];
if (dtLOA.Rows.Count > 0)
{
ImportYEBData.PrintLOAProgressBar();
_counter = iObj.insertYEB_ACT_LOA_Data(dtLOA, usryrmo, pilotind, src, status);
ImportYEBData.ClearProgressBar(_counter);
if (_counter > 0)
{
MultiView1.SetActiveView(view_result);
lbl_result.Text = "Import completed successfully -- " + _counter + " records imported";
}
}
}
catch (Exception ex)
{
//resultDiv.Visible = false;
//ImportYEBData iObj = new ImportYEBData();
ImportYEBData.Rollback("LOA" + pilotind, usryrmo);
lbl_error.Text = "Error - " + ex.Message;
}
}
}
//protected void checkPastImport()
//{
// string _yrmo = Session("yrmo"); //ddlYrmo.SelectedItem.Text;
// HRAImportDAL iobj = new HRAImportDAL();
// if (iobj.PastImport(source, _yrmo))
// {
// MultiView1.SetActiveView(view_reimport);
// lbl_reimport.Text = "Imported already for year-month (YRMO): " + _yrmo + "<br />Do you want to re-import the file?";
// }
// else
// {
// MultiView1.SetActiveView(view_main);
// //autoImport();
// }
//}
Boolean ImportFile(string strFilePath1)
{
string _yrmo = Session["yrmo"].ToString(); //ddlYrmo.SelectedItem.Text;
string logFilePath = Server.MapPath("~/uploads/") + "YEB_Online_" + _yrmo + ".xls";
bool importStat = false;
ImportYEBData iObj = new ImportYEBData();
HRAExcelImport tObj = new HRAExcelImport();
DataTable dtYEBOL;
DataSet ds = new DataSet(); ds.Clear();
ds = tObj.getExcelData(strFilePath1, "YEBOLTable");
dtYEBOL = ds.Tables["YEBOLTable"];
if (dtYEBOL.Rows.Count > 0)
{
ImportYEBData.Rollback(source, _yrmo);
ImportYEBData.PrintOLProgressBar();
//http://www.eggheadcafe.com/articles/20051223.asp
_counter = iObj.insertYEBOLData(dtYEBOL);
Thread.Sleep(2000);
ImportYEBData.ClearProgressBar(_counter);
//Now update the YEbDetailtable with the matches
//found in the YEBOnline table
ImportYEBData.updateYEBDetailData(_yrmo, "YEBOL");
}
if (File.Exists(logFilePath))
{
File.Delete(logFilePath);
}
importStat = true;
Session["taskId"] = Convert.ToInt32(Session["taskId"]) + 1;
Audit.auditUserTaskI(Session.SessionID, Session["mid"].ToString(), Session["taskId"].ToString(), "YEB", "ImportSourceFile", "YEB_ONLINE", "YEBOL Import", _yrmo, _counter);
return(importStat);
}
protected void btnAddrDup_Click(object sender, EventArgs e)
{
DataTable dtAddrDup;
ImportYEBData iObj = new ImportYEBData();
if (Session["yrmo"] != null)
{
usryrmo = Session["yrmo"].ToString();
}
if (Session["pilotind"] != null)
{
pilotind = Session["pilotind"].ToString();
}
if ((usryrmo == ""))
{
lbl_error.Text = "Please select YRMO (year month) value to continue ";
return;
}
if ((pilotind == ""))
{
lbl_error.Text = "Please select PilotIndicator (PI or NP) value to continue ";
return;
}
status = "EBAADDR";
if (pilotind != "" && status != "")
{
try
{
//retrieve the datatable for the criteria specified based on status
dtAddrDup = ImportYEBData.getYEBDuplicateRecs(pilotind, usryrmo, "ADDR1").Tables[0];
if (dtAddrDup.Rows.Count > 0)
{
//Delete any previously imported data for the specified YRMO and criteria
ImportYEBData.Rollback("DUPADDR" + pilotind, usryrmo);
//loop thru the datatable rows for inserting to YEB_Dup_Detail table
ImportYEBData.DisplayProgressBar();
_counter = iObj.insertYEBDupData(dtAddrDup, "ADDR1");
ImportYEBData.HideProgressBar(_counter);
if (_counter > 0)
{
MultiView1.SetActiveView(view_result);
lbl_result.Text = "Identified the Duplicate (Addr1+City) records successfully -- " + _counter + " Records identified for YEB Distribution from " + dtAddrDup.Rows.Count + " records";
}
else if
(_counter == 0)
{
MultiView1.SetActiveView(view_result);
lbl_result.Text = "No Duplicate (Addr1+City) records were identified from the YEB Detail table based on the existing data ";
}
}
else
{
MultiView1.SetActiveView(view_result);
lbl_result.Text = "No Duplicate ADDR1+City records exist in the YEBDetail table currently";
}
}
catch (Exception ex)
{
//resultDiv.Visible = false;
ImportYEBData.Rollback("DUPADDR" + pilotind, usryrmo);
lbl_error.Text = "Error - " + ex.Message;
}
}
}
Boolean ImportFile(string strFilePath1)
{
string _yrmo = Session["yrmo"].ToString(); //ddlYrmo.SelectedItem.Text;
string logFilePath = Server.MapPath("~/uploads/") + "YEB_WWRET_" + _yrmo + ".xls";
bool importStat = false;
string filterexp = "";
ImportYEBData iObj = new ImportYEBData();
HRAExcelImport tObj = new HRAExcelImport();
HRAParseData pObj = new HRAParseData();
ImportYEBData.Rollback(source + pilotind, _yrmo);
DataTable dtWWret;
DataSet ds = new DataSet(); ds.Clear();
ds = tObj.getExcelData(strFilePath1, "WWRetTable");
//testing Cigna Admin fee bill 6-8-2009
//ds = tObj.getCignaAdminFeeBillData(strFilePath1, "CignaAdminFeeTable");
//tObj.ConfirmPutnamYRMO(strFilePath1, _yrmo);
////ds.Tables.Add(tObj.ConvertRangeXLS(_filepath, dt, "SSN", 0));
///_counter = pObj.parsePutnamPartData(ds, _filepath, _source, _qy);
///end testing///
dtWWret = ds.Tables["WWRetTable"];
if (dtWWret.Rows.Count > 0)
{
if (pilotind == "PI")
{
filterexp = "(HealthStatusCode not in ('RT','VT','RFB','RFG','RFI','RFO','RFP','RFS')) AND " +
"EligGroupID <> 0 AND ((Med_TierID) not in (1,2,3,4,5,6)) AND ((Den_TierID) not in (1,2,3,4,5,6)) and ((Vis_TierID) not in (1,2,3,4,5,6)) AND " +
"((Med_OptionID <> 100) OR (Den_OptionID <>100) OR (Vis_OptionID <> 100)) AND (pilotflag='True') AND EEID >0";
//"(Med_OptionID <> '100' and Den_OptionID <>'100' and Vis_OptionID <> '100') AND (pilotflag='True') AND EEID >0";
DataRow[] foundrows = dtWWret.Select(filterexp);
_counter = foundrows.Length;
//foreach (DataRow dr in foundrows)
{
//insert the row in the YEB Detail table
iObj.insertWWRetData(foundrows, usryrmo, pilotind, source, TypeCD);
}
}
else if (pilotind == "NP")
{
filterexp = "(HealthStatusCode not in ('RT','VT','RFB','RFG','RFI','RFO','RFP','RFS')) AND " +
"EligGroupID <> 0 AND ((Med_TierID) not in (1,2,3,4,5,6)) AND ((Den_TierID) not in (1,2,3,4,5,6)) and ((Vis_TierID) not in (1,2,3,4,5,6)) AND " +
"((Med_OptionID <> 100) OR (Den_OptionID <>100) OR (Vis_OptionID <> 100)) AND (pilotflag='False') AND EEID >0";
//"(Med_OptionID <> '100' AND Den_OptionID <>'100' AND Vis_OptionID <> '100') AND (pilotflag='False') AND EEID >0";
DataRow[] foundrows = dtWWret.Select(filterexp);
_counter = foundrows.Length;
//foreach (DataRow dr in foundrows)
{
//insert the row in the YEB Detail table
iObj.insertWWRetData(foundrows, usryrmo, pilotind, source, TypeCD);
}
}
}
if (File.Exists(logFilePath))
{
File.Delete(logFilePath);
}
importStat = true;
Session["taskId"] = Convert.ToInt32(Session["taskId"]) + 1;
Audit.auditUserTaskI(Session.SessionID, Session["mid"].ToString(), Session["taskId"].ToString(), "YEB", "ImportSourceFile", "YEB_WWRET", "WW Import", usryrmo, _counter);
return(importStat);
}
protected void btn_import_Click1(object sender, EventArgs e)
{
string yrmo = ddlYrmo.SelectedItem.Text;
lbl_error.Text = "";
//lblError1.Text = "";
if (Page.IsValid)
{
if (Session["pilotind"] == null)
{
lbl_error.Text = "Please Select a Pilot indictor value";
return;
}
if (txtPrevYRMO.Visible)
{
lbl_error.Text = "Enter YRMO in format 'yyyymm'";
return;
}
if (usryrmo.Equals("") || pilotind.Equals(""))
{
lbl_error.Text = "YRMO value cannot be empty - " + usryrmo + " Pilot Indicator value cannot be empty - " + pilotind;
return;
}
lbl_error.Text = "";
string strFilePath1 = "";
if (FileUpload1.GotFile)
{
try
{
string fn = System.IO.Path.GetFileName(FileUpload1.FilePost.FileName);
strFilePath1 = Server.MapPath("~/uploads/") + fn;
if (File.Exists(strFilePath1))
{
File.Delete(strFilePath1);
}
FileUpload1.FilePost.SaveAs(strFilePath1);
if (ImportFile(strFilePath1))
{
bindResults();
//ImportYEBData.SavePrintFiles(source, yrmo);
MultiView1.SetActiveView(view_result);
lbl_result.Text = "Import completed successfully -- " + _counter + " records imported";
}
}
catch (Exception ex)
{
//resultDiv.Visible = false;
ImportYEBData.Rollback("COB" + Session["pilotind"].ToString(), yrmo);
lbl_error.Text = "Error - " + ex.Message;
}
finally
{
if (File.Exists(strFilePath1))
{
File.Delete(strFilePath1);
}
FileUpload1.FilePost.InputStream.Flush();
FileUpload1.FilePost.InputStream.Close();
FileUpload1.FilePost.InputStream.Dispose();
}
}
}
}
protected void bindResults()
{
//clearMessages();
//resultDiv.Visible = true;
string _sb1 = "", _td1 = "", _tw1 = "", _eb1 = "";
try
{
DataSet dsresult = new DataSet();
dsresult = ImportYEBData.getYEBData(ddlYrmo.SelectedItem.Text, _sb1);
foreach (DataRow dr in dsresult.Tables[0].Rows)
{
string _type = dr["Type"].ToString();
switch (_type)
{
case "Start Balance":
_sb1 = Convert.ToDecimal(dr["Amount"]).ToString("C");
break;
case "Total Deposits":
_td1 = Convert.ToDecimal(dr["Amount"]).ToString("C");
break;
case "Total Withdrawls":
_tw1 = ((-1) * Convert.ToDecimal(dr["Amount"])).ToString("C");
break;
case "End Balance":
_eb1 = Convert.ToDecimal(dr["Amount"]).ToString("C");
break;
}
}
//if (dsresult.Tables[0].Rows.Count > 0)
//{
// //Summary
// lblSBalance.Text = _sb1;
// lblDeposits.Text = _td1;
// lblWithdrawls.Text = _tw1;
// lblEnding.Text = _eb1;
// decimal _tot1 = decimal.Parse(_sb1, System.Globalization.NumberStyles.Currency)
// + decimal.Parse(_td1, System.Globalization.NumberStyles.Currency)
// + decimal.Parse(_tw1, System.Globalization.NumberStyles.Currency);
// lblTotalV.Text = _tot1.ToString("C");
// decimal _tot2 = _tot1 - decimal.Parse(_eb1, System.Globalization.NumberStyles.Currency);
// lblFinalV.Text = _tot2.ToString("C");
// if (_tot2 != 0)
// {
// Image3.Visible = true;
// }
// else
// {
// Image1.Visible = true;
// }
// //Deposits
// decimal _dep1 =
// decimal.Parse((dsresult.Tables[0].Compute("SUM([Amount])", "[importType] = 'Detail' AND ([Type] = 'DEP' OR [Type] = 'MSCC')")).ToString(), System.Globalization.NumberStyles.Currency);
// decimal _tot3 = decimal.Parse(_td1, System.Globalization.NumberStyles.Currency) - _dep1;
// lblDep11.Text = _dep1.ToString("C");
// lblDep12.Text = _td1;
// lblDeptotal.Text = _tot3.ToString("C");
// if (_tot2 != 0)
// {
// Image5.Visible = true;
// }
// else
// {
// Image4.Visible = true;
// }
// //Deposits
// decimal _withd1 =
// decimal.Parse((dsresult.Tables[0].Compute("SUM([Amount])", "[importType] = 'Detail' AND ([Type] <> 'DEP' AND [Type] <> 'MSCC')")).ToString(), System.Globalization.NumberStyles.Currency);
// decimal _tot4 = decimal.Parse(_tw1, System.Globalization.NumberStyles.Currency) + _withd1;
// lblWithd11.Text = _withd1.ToString("C");
// lblWithd12.Text = ((-1) * decimal.Parse(_tw1, System.Globalization.NumberStyles.Currency)).ToString("C");
// lblWithdtotal.Text = _tot4.ToString("C");
// if (_tot4 != 0)
// {
// Image7.Visible = true;
// }
// else
// {
// Image6.Visible = true;
// }
//}
}
catch (Exception ex)
{
//errorDiv1.Visible = true;
//lblError1.Text = ex.Message;
}
}
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);
}
