protected void lnk_genHTHRpt_OnClick(object sender, EventArgs e) { lbl_error.Text = ""; string yrmo = ddlYrmo.SelectedItem.Text; string plancode = "P5/P5"; IPBA dobj = new IPBA(); CreateExcelRpt xlobj = new CreateExcelRpt(); DataSet ds = new DataSet(); ds.Clear(); string filename = yrmo.Substring(0, 4) + "_" + yrmo + "_HMODETSTD_P5"; string[] sheetnames = { filename }; string[][] titles = { new string[] { "" } }; string[][] colsFormat = { new string[] { "string", "string", "string", "string", "string", "string", "currency" } }; int[][] colsWidth = { new int[] { 60, 40, 65, 130, 35, 60, 65 } }; try { ds = dobj.GetHTHAnthemRpt(yrmo, plancode); xlobj.ExcelXMLRpt(ds, filename, sheetnames, titles, colsFormat, colsWidth, String.Empty); } catch (Exception ex) { lbl_error.Text = "Error in generating HTH International Report for Anthem module for YRMO - " + yrmo + "<br />" + ex.Message; } }
protected void lnk_genSumRptAll_OnClick(object sender, EventArgs e) { lbl_error.Text = ""; string yrmo = ddlYrmo.SelectedItem.Text; string plancode = ddlPlancd.SelectedItem.Value; string filename; string[] sheetnames; string[][] titles, colsFormat; int[][] colsWidth; IPBA dobj = new IPBA(); CreateExcelRpt xlobj = new CreateExcelRpt(); DataSet ds = new DataSet(); ds.Clear(); DataSet dsFinal = new DataSet(); dsFinal.Clear(); try { filename = yrmo + "_IPBA_SUM_All"; titles = new string[ddlPlancd.Items.Count - 1][]; sheetnames = new string[ddlPlancd.Items.Count - 1]; colsFormat = new string[ddlPlancd.Items.Count - 1][]; colsWidth = new int[ddlPlancd.Items.Count - 1][]; for (int i = 1; i < (ddlPlancd.Items.Count); i++) { plancode = ddlPlancd.Items[i].Value; if (plancode.Equals("P5")) { plancode = "P5/P5"; } if (plancode.Contains("P5")) { titles[i - 1] = new string[] { "HTH Billing Summary Report: " + ddlPlancd.Items[i].Text, "Year-Month: " + yrmo } } ; else { titles[i - 1] = new string[] { "Local HMO Billing Summary Report: " + ddlPlancd.Items[i].Text, "Year-Month: " + yrmo } }; sheetnames[i - 1] = "Summary_" + plancode.Substring(0, 2); colsFormat[i - 1] = new string[] { "number", "string", "string", "number_comma", "currency", "currency" }; colsWidth[i - 1] = new int[] { 40, 110, 80, 35, 75, 75 }; ds = dobj.GetSummaryRpt(yrmo, plancode); ds.Tables[0].TableName = "Table_" + plancode.Substring(0, 2); dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[i - 1].TableName = "TableF_" + plancode.Substring(0, 2); ds.Clear(); } xlobj.ExcelXMLRpt(dsFinal, filename, sheetnames, titles, colsFormat, colsWidth, String.Empty); } catch (Exception ex) { lbl_error.Text = "Error in generating HTH/HMO Billing Summary Report for YRMO - " + yrmo + "<br />" + ex.Message; } }
protected void lnk_genAdjRptAll_OnClick(object sender, EventArgs e) { lbl_error.Text = ""; string yrmo = ddlYrmo.SelectedItem.Text; string plancode = ddlPlancd.SelectedItem.Value; string filename, footernotes; string[] sheetnames; string[][] titles, colsFormat; int[][] colsWidth; IPBA dobj = new IPBA(); CreateExcelRpt xlobj = new CreateExcelRpt(); DataSet ds = new DataSet(); ds.Clear(); DataSet dsFinal = new DataSet(); dsFinal.Clear(); try { footernotes = "Exceeded maximum months adjustment that system can be applied. Additional adjustments need to be manually processed."; filename = yrmo + "_IPBA_ADJ_All"; titles = new string[ddlPlancd.Items.Count - 1][]; sheetnames = new string[ddlPlancd.Items.Count - 1]; colsFormat = new string[ddlPlancd.Items.Count - 1][]; colsWidth = new int[ddlPlancd.Items.Count - 1][]; for (int i = 1; i < (ddlPlancd.Items.Count); i++) { plancode = ddlPlancd.Items[i].Value; if (plancode.Equals("P5")) { plancode = "P5/P5"; } if (plancode.Contains("P5")) { titles[i - 1] = new string[] { "HTH Billing Adjustment Report: " + ddlPlancd.Items[i].Text, "Year-Month: " + yrmo } } ; else { titles[i - 1] = new string[] { "Local HMO Billing Adjustment Report: " + ddlPlancd.Items[i].Text, "Year-Month: " + yrmo } }; sheetnames[i - 1] = "Adjustments_" + plancode.Substring(0, 2); colsFormat[i - 1] = new string[] { "number", "string", "string", "string", "string", "number", "string", "number", "currency", "string" }; colsWidth[i - 1] = new int[] { 30, 60, 30, 60, 100, 40, 180, 40, 65, 30 }; ds = dobj.GetAdjustmentRpt(yrmo, plancode); ds.Tables[0].TableName = "Table_" + plancode.Substring(0, 2); dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[i - 1].TableName = "TableF_" + plancode.Substring(0, 2); ds.Clear(); } xlobj.ExcelXMLRpt(dsFinal, filename, sheetnames, titles, colsFormat, colsWidth, footernotes); } catch (Exception ex) { lbl_error.Text = "Error in generating HTH/HMO Billing Adjustment Report for YRMO - " + yrmo + "<br />" + ex.Message; } }
protected void lnk_genTemplateRpt_OnClick(object sender, EventArgs e) { lblErr1.Text = ""; string _PY = txtTempPlanYear.Text; bool _exists = BenhierDAL.checkRatePY(_PY); if (_exists) { string msg = "Rates for entered plan year already defined!"; string alertScript = "<script language=JavaScript> alert('" + msg + "'); <" + "/script>"; if (!ClientScript.IsClientScriptBlockRegistered("alert")) { ClientScript.RegisterClientScriptBlock(typeof(Page), "alert", alertScript); } } else { DataSet dsrptTemp1 = new DataSet(); dsrptTemp1.Clear(); dsrptTemp1 = BenhierDAL.rateTemplate(_PY); string filename = "Rates_Template_" + _PY; string[] sheetnames = { "rates" + _PY }; string[][] titles = { new string[] { "" } }; string[][] colsFormat = { new string[] { "number", "string", "string", "string", "string", "string", "string", "decimal", "decimal", "number" } }; int[][] colsWidth = { new int[] { 70, 100, 90, 70, 200, 80, 80, 75, 90, 100 } }; CreateExcelRpt xlobj = new CreateExcelRpt(); xlobj.ExcelXMLRpt(dsrptTemp1, filename, sheetnames, titles, colsFormat, colsWidth, String.Empty); } }
protected void lnk_genRateRpt_OnClick(object sender, EventArgs e) { lblErr1.Text = ""; string _filterPY = ddlFilterPY.SelectedItem.Text; string _filterPgCd = ddlFilterPgCd.SelectedItem.Text; DataSet dsrptfinal1 = new DataSet(); dsrptfinal1.Clear(); if (_filterPgCd.Equals("All")) { dsrptfinal1 = BenhierDAL.rateReport(_filterPY); } else { dsrptfinal1 = BenhierDAL.rateReport(_filterPY, _filterPgCd); } string filename = "Rates_" + _filterPY; string[] sheetnames = { "rates_" + _filterPY }; string[][] titles = { new string[] { "Rates Information for Plan year " + _filterPY } }; string[][] colsFormat = { new string[] { "number", "string", "string", "string", "string", "string", "string", "decimal", "decimal", "number" } }; int[][] colsWidth = { new int[] { 70, 100, 90, 70, 200, 80, 80, 75, 90, 100 } }; CreateExcelRpt xlobj = new CreateExcelRpt(); xlobj.ExcelXMLRpt(dsrptfinal1, filename, sheetnames, titles, colsFormat, colsWidth, String.Empty); }
protected void lnk_genBenRpt_OnClick(object sender, EventArgs e) { lblErr1.Text = ""; string _filter = ddlFilter1.SelectedItem.Text; DataSet dsrptfinal = new DataSet(); dsrptfinal.Clear(); if (_filter.Equals("All")) { dsrptfinal = BenhierDAL.benhierReport(); } else { dsrptfinal = BenhierDAL.benhierReport(_filter); } string filename = "Benefits_Hierarchy"; string[] sheetnames = { "ben_hierarchy" }; string[][] titles = { new string[] { "Benefits Hierarchy Report" } }; string[][] colsFormat = { new string[] { "string", "string", "string", "string", "string", "string", "number" } }; int[][] colsWidth = { new int[] { 100, 100, 70, 200, 80, 80, 100 } }; CreateExcelRpt xlobj = new CreateExcelRpt(); xlobj.ExcelXMLRpt(dsrptfinal, filename, sheetnames, titles, colsFormat, colsWidth, String.Empty); }
protected void lnk_genDetRpt_OnClick(object sender, EventArgs e) { lbl_error.Text = ""; string yrmo = ddlYrmo.SelectedItem.Text; string plancode = ddlPlancd.SelectedItem.Value; string filename; string[] sheetnames; string[][] titles, colsFormat; int[][] colsWidth; IPBA dobj = new IPBA(); CreateExcelRpt xlobj = new CreateExcelRpt(); DataSet dsFinal = new DataSet(); dsFinal.Clear(); try { filename = yrmo + "_IPBA_DET_" + plancode; titles = new string[1][]; sheetnames = new string[1]; colsFormat = new string[1][]; colsWidth = new int[1][]; if (plancode.Equals("P5")) { plancode = "P5/P5"; } if (plancode.Contains("P5")) { titles[0] = new string[] { "HTH Billing Detail Report: " + ddlPlancd.SelectedItem.Text, "Year-Month: " + yrmo } } ; else { titles[0] = new string[] { "Local HMO Billing Detail Report: " + ddlPlancd.SelectedItem.Text, "Year-Month: " + yrmo } }; sheetnames[0] = "Detail"; colsFormat[0] = new string[] { "string", "string", "string", "string", "string", "string", "currency" }; colsWidth[0] = new int[] { 90, 35, 60, 125, 35, 55, 65 }; dsFinal = dobj.GetDetailRpt(yrmo, plancode); xlobj.ExcelXMLRpt(dsFinal, filename, sheetnames, titles, colsFormat, colsWidth, String.Empty); } catch (Exception ex) { lbl_error.Text = "Error in generating HTH/HMO Billing Detail Report for YRMO - " + yrmo + "<br />" + ex.Message; } }
protected void lnk_genAdjRpt_OnClick(object sender, EventArgs e) { lbl_error.Text = ""; string filename, plancode, yrmo; string[] sheetnames; string[][] titles, colsFormat; int[][] colsWidth; CreateExcelRpt xlobj = new CreateExcelRpt(); DataSet dsFinal = new DataSet(); dsFinal.Clear(); DataTable tblAdj = dsFinal.Tables.Add("ManualAdj"); yrmo = ddlYrmo.SelectedItem.Text; plancode = ddlHMO.SelectedItem.Value; try { if (!(plancode.Equals("--All--"))) { filename = yrmo + "_IPBA_ManAdj_" + plancode; } else { filename = yrmo + "_IPBA_ManAdj_All"; } titles = new string[1][]; sheetnames = new string[1]; colsFormat = new string[1][]; colsWidth = new int[1][]; if (plancode.Equals("P5")) { titles[0] = new string[] { "HTH Manual Adjustments Report: P5", "Year-Month: " + yrmo } } ; else { if (!(plancode.Equals("--All--"))) { titles[0] = new string[] { "Local HMO Manual Adjustments Report: " + plancode, "Year-Month: " + yrmo } } ; else { titles[0] = new string[] { "All HTH & HMO's Manual Adjustments Report", "Year-Month: " + yrmo } }; } sheetnames[0] = "ManualAdjs"; colsFormat[0] = new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "string" }; colsWidth[0] = new int[] { 45, 35, 70, 35, 95, 95, 35, 50, 65, 80 }; for (int i = 2; i < grdvAdj.Columns.Count; i++) { tblAdj.Columns.Add(grdvAdj.Columns[i].ToString()); } foreach (GridViewRow row in grdvAdj.Rows) { DataRow dr = tblAdj.NewRow(); for (int i = 0; i < row.Cells.Count - 2; i++) { dr[i] = row.Cells[i + 2].Text; } tblAdj.Rows.Add(dr); } xlobj.ExcelXMLRpt(dsFinal, filename, sheetnames, titles, colsFormat, colsWidth, String.Empty); } catch (Exception ex) { lbl_error.Text = "Error in generating HTH/HMO Billing Summary Report for YRMO - " + yrmo + "<br />" + ex.Message; } }
protected void lnk_genRpt_OnClick(Object sender, EventArgs e) { Label1.Visible = false; errorDiv1.Visible = false; try { if ((DateTimePicker1.Value == null) || (DateTimePicker2.Value == null)) { if ((DateTimePicker1.Value == null)) { Label1.Visible = true; } if ((DateTimePicker2.Value == null)) { Label2.Visible = true; } } else { DateTime _d1 = Convert.ToDateTime(DateTimePicker1.Value); DateTime _d2 = Convert.ToDateTime(DateTimePicker2.Value); string _date1 = ""; string _date2 = ""; if (_d1.Day < 10) { _date1 = _d1.Month.ToString() + "/0" + _d1.Day.ToString() + "/" + _d1.Year.ToString(); } else { _date1 = _d1.Month.ToString() + "/" + _d1.Day.ToString() + "/" + _d1.Year.ToString(); } if (_d2.Day < 10) { _date2 = _d2.Month.ToString() + "/0" + _d2.Day.ToString() + "/" + _d2.Year.ToString(); } else { _date2 = _d2.Month.ToString() + "/" + _d2.Day.ToString() + "/" + _d2.Year.ToString(); } string filename; string[] sheetnames; string[][] titles; string[][] colsFormat; int[][] colsWidth; CreateExcelRpt xlobj = new CreateExcelRpt(); DataSet dsImFinal = new DataSet(); DataSet dsUpFinal = new DataSet(); DataSet dsExFinal = new DataSet(); dsImFinal = HRAdata.getEmployeeAuditInsert(_date1, _date2); dsUpFinal = HRAdata.getEmployeeAuditUpdate(_date1, _date2); dsExFinal = HRAdata.getEmployeeAuditException(_date1, _date2); DataSet dsTemp = new DataSet(); DataTable obNewDt1 = new DataTable(); DataTable obNewDt2 = new DataTable(); DataTable obNewDt3 = new DataTable(); if (dsImFinal.Tables.Count > 0) { DataView dv1 = new DataView(); dv1 = dsImFinal.Tables[0].DefaultView; dv1.Sort = "[Task Date] ASC,[Trigger Event] DESC,[EE#] ASC"; obNewDt1 = dv1.Table.Clone(); int idx = 0; string[] strColNames = new string[obNewDt1.Columns.Count]; foreach (DataColumn col in obNewDt1.Columns) { strColNames[idx++] = col.ColumnName; } IEnumerator viewEnumerator = dv1.GetEnumerator(); while (viewEnumerator.MoveNext()) { DataRowView drv = (DataRowView)viewEnumerator.Current; DataRow dr = obNewDt1.NewRow(); try { foreach (string strName in strColNames) { dr[strName] = drv[strName]; } } catch (Exception ex) { throw ex; } obNewDt1.Rows.Add(dr); } } obNewDt1.TableName = "tbl1"; dsTemp.Tables.Add(obNewDt1.Copy()); dsTemp.Tables[0].TableName = "tbl1"; if (dsUpFinal.Tables.Count > 0) { DataView dv2 = new DataView(); dv2 = dsUpFinal.Tables[0].DefaultView; dv2.Sort = "[Task Date] ASC,[Trigger Event] DESC,[EE#] ASC"; obNewDt2 = dv2.Table.Clone(); int idx = 0; string[] strColNames = new string[obNewDt2.Columns.Count]; foreach (DataColumn col in obNewDt2.Columns) { strColNames[idx++] = col.ColumnName; } IEnumerator viewEnumerator = dv2.GetEnumerator(); while (viewEnumerator.MoveNext()) { DataRowView drv = (DataRowView)viewEnumerator.Current; DataRow dr = obNewDt2.NewRow(); try { foreach (string strName in strColNames) { dr[strName] = drv[strName]; } } catch (Exception ex) { throw ex; } obNewDt2.Rows.Add(dr); } } obNewDt2.TableName = "tbl2"; dsTemp.Tables.Add(obNewDt2.Copy()); dsTemp.Tables[1].TableName = "tbl2"; if (dsExFinal.Tables.Count > 0) { DataView dv3 = new DataView(); dv3 = dsExFinal.Tables[0].DefaultView; dv3.Sort = "[Task Date] ASC,[Trigger Event] DESC,[EE#] ASC"; obNewDt3 = dv3.Table.Clone(); int idx = 0; string[] strColNames = new string[obNewDt3.Columns.Count]; foreach (DataColumn col in obNewDt3.Columns) { strColNames[idx++] = col.ColumnName; } IEnumerator viewEnumerator = dv3.GetEnumerator(); while (viewEnumerator.MoveNext()) { DataRowView drv = (DataRowView)viewEnumerator.Current; DataRow dr = obNewDt3.NewRow(); try { foreach (string strName in strColNames) { dr[strName] = drv[strName]; } } catch (Exception ex) { throw ex; } obNewDt3.Rows.Add(dr); } } obNewDt3.TableName = "tbl3"; dsTemp.Tables.Add(obNewDt3.Copy()); dsTemp.Tables[2].TableName = "tbl3"; titles = new string[3][]; sheetnames = new string[3]; colsFormat = new string[3][]; colsWidth = new int[3][]; if (!_date1.Equals(_date2)) { filename = "Upload_Report_" + _date1 + _date2; titles[0] = new string[] { "Audit report for inserts - " + _date1 + " to " + _date2 }; titles[1] = new string[] { "Audit report for Updates - " + _date1 + " to " + _date2 }; titles[2] = new string[] { "Audit report for Exceptions - " + _date1 + " to " + _date2 }; } else { filename = "Upload_Report_" + _date1; titles[0] = new string[] { "Audit report for inserts - " + _date1 }; titles[1] = new string[] { "Audit report for Updates - " + _date1 }; titles[2] = new string[] { "Audit report for Exceptions - " + _date1 }; } sheetnames[0] = "Inserts"; sheetnames[1] = "Updates"; sheetnames[2] = "Exceptions"; colsFormat[0] = new string[] { "number", "string", "string", "string", "string", "string", "string", "string", "string", "string" }; colsFormat[1] = new string[] { "number", "string", "string", "string", "string", "string", "string", "string", "string", "string" }; colsFormat[2] = new string[] { "number", "string", "string", "string", "string", "string", "string", "string", "string", "string" }; colsWidth[0] = new int[] { 50, 65, 140, 140, 75, 65, 50, 50, 100, 50 }; colsWidth[1] = new int[] { 50, 65, 140, 140, 75, 65, 50, 50, 100, 50 }; colsWidth[2] = new int[] { 50, 65, 140, 140, 75, 65, 50, 50, 100, 50 }; xlobj.ExcelXMLRpt(dsTemp, filename, sheetnames, titles, colsFormat, colsWidth, String.Empty); } } catch (Exception ex) { errorDiv1.Visible = true; lbl_error1.Text = "Error generating report - " + ex.Message; } }