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); }