protected void btn_reconAgn_Click(object sender, EventArgs e) { string yrmo = ddlYrmo.SelectedItem.Text.ToString(); lbl_error.Text = ""; try { ReconDAL.pastReconcileDelete(yrmo, "DOM"); //if (RptsImported()) //Removed this if condition as it is not applicable to Dom Recon //R.A 10/30/2009 { ReconDAL.DomesticReconcile(yrmo, "ANTH_ACT", "GRS", "ACT"); ReconDAL.DomesticReconcile(yrmo, "ANTH_RET", "RET%", "RET"); ReconDAL.DomesticReconcile(yrmo, "ANTH_COB", "ADP", "COB"); ShowResult(); auditRecon(yrmo); } } catch (Exception ex) { ReconDAL.pastReconcileDelete(yrmo, "DOM"); lbl_error.Text = "Error in re-reconciliation.<br />" + ex.Message; } }
public static string CheckReconOrder(string yrmo, string source) { string latestYRMO = ReconDAL.GetLatestYRMO(source); string _yrmo = yrmo.Insert(4, "/"); string _latestYRMO = latestYRMO.Insert(4, "/"); string _monthList = ""; string _prevyrmo = null; int _mDiff = Convert.ToDateTime(_yrmo).Month - Convert.ToDateTime(_latestYRMO).Month; int _YearDiff = Convert.ToDateTime(_yrmo).Year - Convert.ToDateTime(_latestYRMO).Year; int _months = Math.Abs(_mDiff + (12 * _YearDiff)); if (_months > 1) { string _pramYrmo = yrmo; for (int i = 0; i < (_months - 1); i++) { _prevyrmo = prevYRMO(_pramYrmo); _monthList = _monthList + _prevyrmo + ", "; _pramYrmo = _prevyrmo; } _monthList = _monthList.Remove(_monthList.LastIndexOf(','), 2); } return(_monthList); }
protected void bindResult(string _src) { DataSet ds = new DataSet(); DataSet ds1 = new DataSet(); DataSet ds2 = new DataSet(); string yrmo = ddlYrmo.SelectedItem.Text; switch (_src) { case "ACT": ds = ReconDAL.GetDomReconData(yrmo, "ACT"); grdvActResult.DataSource = ds; grdvActResult.DataBind(); break; case "RET": ds = ReconDAL.GetDomReconData(yrmo, "RET"); grdvRetResult.DataSource = ds; grdvRetResult.DataBind(); break; case "COB": ds = ReconDAL.GetDomReconData(yrmo, "COB"); grdvCobResult.DataSource = ds; grdvCobResult.DataBind(); break; } }
protected void btn_genRpt_Click(object sender, EventArgs e) { string yrmo = ddlYrmo.SelectedItem.Text; DataSet ds = new DataSet(); DataSet dsFinal = new DataSet(); ds.Clear(); dsFinal.Clear(); string[][] cols = { new string[] { "YRMO", "EBA Count", "EBA Amount", "Anthem Count", "Anthem Amount", "EBA Count Variance", "EBA vs Anthem % Count Variance", "Threshold", "Threshold Level" }, new string[] { "YRMO", "Source", "EBA Count", "Anthem Count" } }; string[][] colsFormat = { new string[] { "string", "number", "decimal", "number", "decimal", "number", "decimal", "decimal", "string" }, new string[] { "string", "string", "number", "number" } }; string[] sheetnames = { "EAP", "EAP_Count_Details" }; string[] titles = { "EAP Billing Reconciliation for YRMO - " + yrmo, "EAP Billing Headcounts Detail for YRMO - " + yrmo }; try { ds = ReconDAL.GetEAPReconData(yrmo); ds.Tables[0].TableName = "eapTable"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[0].TableName = "eapTableF"; ds.Clear(); ds = ReconDAL.GetEAPReconDetails(yrmo); ds.Tables[0].TableName = "detTable"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[1].TableName = "detTableF"; ds.Clear(); ExcelReport.ExcelXMLRpt(dsFinal, "EAPRecon_" + yrmo, sheetnames, titles, cols, colsFormat); } catch (Exception ex) { MultiView1.SetActiveView(view_main); lbl_error.Text = "Error in generating excel report" + ex.Message; } }
private void SortGridView(string sortExpression, string direction, string source) { // You can cache the DataTable for improving performance string yrmo = ddlYrmo.SelectedItem.Text; DataTable dt = ReconDAL.GetDomReconData(yrmo, source).Tables[0]; DataView dv = new DataView(dt); dv.Sort = sortExpression + direction; switch (source) { case "ACT": grdvActResult.DataSource = dv; grdvActResult.DataBind(); break; case "RET": grdvRetResult.DataSource = dv; grdvRetResult.DataBind(); break; case "COB": grdvCobResult.DataSource = dv; grdvCobResult.DataBind(); break; } }
protected void bindResult() { DataSet ds = new DataSet(); string yrmo = ddlYrmo.SelectedItem.Text; ds = ReconDAL.GetIntlReconData(yrmo); grdvResult.DataSource = ds; grdvResult.DataBind(); }
protected void bindgrdvUnmatchedDFRF() { string yrmo = ddlYrmo.SelectedItem.Text; DataSet ds = new DataSet(); ds = ReconDAL.getRFDFUM(yrmo); if (ds.Tables[0].Rows.Count > 0) { grdvUnmatchedDFRF.DataSource = ds; grdvUnmatchedDFRF.DataBind(); } }
//RFDF Recon //public static void matchCF(string _yrmo) //{ // List<int> rid = new List<int>(); // rid = ReconDAL.matchCF(_yrmo); // if (rid.Count != 0) // { // ReconDAL.updatematchCF(rid); // } //} //public static void amountMatch(string _yrmo) //{ // DataSet ds = new DataSet(); // ds = ReconDAL.amountMatch(_yrmo); // insertReconData(ds, _yrmo); //} //public static void CF(string _yrmo) //{ // DataSet ds = new DataSet(); // ds = ReconDAL.updateCFFinal(_yrmo); // insertReconCF(ds, _yrmo); //} //private static void insertReconData(DataSet ds, string _yrmo) //{ // string _claimid; // decimal _clmamt, _rfdfamt, _var; // foreach (DataRow row in ds.Tables["reconTemp"].Rows) // { // _claimid = row[0].ToString(); // _clmamt = decimal.Parse(row[1].ToString(), System.Globalization.NumberStyles.Currency); // _rfdfamt = decimal.Parse(row[2].ToString(), System.Globalization.NumberStyles.Currency); // _var = _clmamt - _rfdfamt; // ReconDAL.insertRecon(_yrmo, _claimid, _clmamt, _rfdfamt, _var); // } // ReconDAL.updateTablesCF(_yrmo); //} //private static void insertReconCF(DataSet ds, string _yrmo) //{ // string _claimid; // decimal _fclmamt, _frfdfamt, _var; // foreach (DataRow row in ds.Tables["CFTemp"].Rows) // { // _claimid = row[0].ToString(); // _fclmamt = decimal.Parse(row[1].ToString(), System.Globalization.NumberStyles.Currency); // _frfdfamt = decimal.Parse(row[2].ToString(), System.Globalization.NumberStyles.Currency); // _var = _fclmamt - _frfdfamt; // if (_var == 0) // { // ReconDAL.updateReconCF(_yrmo, _claimid, _fclmamt, _frfdfamt, _var); // } // } //} //public static void matchDFCl(string _yrmo) //{ // List<int> rid = new List<int>(); // rid = ReconDAL.matchCF(_yrmo); // if (rid.Count != 0) // { // ReconDAL.updatematchCF(rid); // } // ReconDAL.calcTotalAmt(_yrmo, rid); //} //RX Recon public static void anthemRXrecon(string _yrmo) { decimal _frdamt = 0; decimal _anbtamt = 0; _frdamt = ReconDAL.getFRDData(_yrmo); if (_frdamt.Equals(-1)) { throw (new Exception("No data from FRD. FRD may not have processed the Anthem bank Reconciliation.<br/>Contact FRD.")); } _anbtamt = ReconDAL.getAnthemRX(_yrmo); ReconDAL.insertRXRecon(_yrmo, _frdamt, _anbtamt); }
private void SortGridView(string sortExpression, string direction) { // You can cache the DataTable for improving performance string yrmo = ddlYrmo.SelectedItem.Text; DataTable dt = ReconDAL.GetEAPReconData(yrmo).Tables[0]; DataView dv = new DataView(dt); dv.Sort = sortExpression + direction; grdvResult.DataSource = dv; grdvResult.DataBind(); }
protected void bindgrdvmisMatched() { string yrmo = ddlYrmo.SelectedItem.Text; DataSet ds = new DataSet(); ds = ReconDAL.getMatchedDCNAmnt(yrmo, "UnMatched"); if (ds.Tables[0].Rows.Count > 0) { grdvmisMatched.DataSource = ds; grdvmisMatched.DataBind(); } }
protected void bindHTH() { string yrmo = ddlYrmo.SelectedItem.Text; DataSet ds1 = null; ds1 = ReconDAL.getUnmatch(yrmo); if (ds1.Tables["HTHDATA"].Rows.Count > 0) { hthDiv.Visible = true; detailDiv.Visible = true; grdvHTH.DataSource = ds1.Tables["HTHDATA"]; grdvHTH.DataBind(); } }
protected void bindANTH() { string yrmo = ddlYrmo.SelectedItem.Text; DataSet ds1 = new DataSet(); ds1 = ReconDAL.getUnmatch(yrmo); if (ds1.Tables["ANTHDATA"].Rows.Count > 0) { anthdiv.Visible = true; detailDiv.Visible = true; grdvAnth.DataSource = ds1.Tables["ANTHDATA"]; grdvAnth.DataBind(); } }
protected void btn_genRpt_Click(object sender, EventArgs e) { string yrmo = ddlYrmo.SelectedItem.Text; DataSet ds = new DataSet(); DataSet dsFinal = new DataSet(); dsFinal.Clear(); string[][] cols = { new string[] { "Reconciliation Type", "EBA Count", "EBA Amount", "Anthem Count", "Anthem Amount", "EBA Count Variance" }, new string[] { "YRMO", "Anthem Group Suffix", "Anthem Covg. Code", "EBA Count", "EBA Amount", "Anthem Count", "Anthem Amount", "EBA Count Variance", "EBA vs Anthem % Count Variance", "Threshold", "Threshold Level"}, new string[] { "YRMO", "Anthem Group Suffix", "Anthem Covg. Code", "EBA Count", "EBA Amount", "Anthem Count", "Anthem Amount", "EBA Count Variance", "EBA vs Anthem % Count Variance", "Threshold", "Threshold Level"}, new string[] { "YRMO", "Anthem Group Suffix", "Anthem Covg. Code", "EBA Count", "EBA Amount", "Anthem Count", "Anthem Amount", "EBA Count Variance", "EBA vs Anthem % Count Variance", "Threshold", "Threshold Level"} }; string[][] colsFormat = { new string[] { "string", "number", "decimal", "number", "decimal", "number" }, new string[] { "string", "string", "string", "number", "decimal", "number", "decimal", "number", "decimal", "decimal", "string"}, new string[] { "string", "string", "string", "number", "decimal", "number", "decimal", "number", "decimal", "decimal", "string"}, new string[] { "string", "string", "string", "number", "decimal", "number", "decimal", "number", "decimal", "decimal", "string"} }; string[] sheetnames = { "Summary", "Active", "Retiree", "Cobra" }; string[] titles = { "Domestic Billing Reconciliation Summary for YRMO - " + yrmo, "Active Billing Reconciliation for YRMO - " + yrmo, "Retiree Billing Reconciliation for YRMO - " + yrmo, "Cobra Billing Reconciliation for YRMO - " + yrmo }; try { ds = ReconDAL.GetDomSummary(yrmo); ds.Tables[0].TableName = "sumTable"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[0].TableName = "sumTableF"; ds.Clear(); ds = ReconDAL.GetDomReconData(yrmo, "ACT"); ds.Tables[0].TableName = "actTable"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[1].TableName = "actTableF"; ds.Clear(); ds = ReconDAL.GetDomReconData(yrmo, "RET"); ds.Tables[0].TableName = "retTable"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[2].TableName = "retTableF"; ds.Clear(); ds = ReconDAL.GetDomReconData(yrmo, "COB"); ds.Tables[0].TableName = "cobTable"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[3].TableName = "cobTableF"; ds.Clear(); ExcelReport.ExcelXMLRpt(dsFinal, "DomesticRecon_" + yrmo, sheetnames, titles, cols, colsFormat); } catch (Exception ex) { MultiView1.SetActiveView(view_main); lbl_error.Text = "Error in generating excel report" + ex.Message; } }
protected void bindResult() { DataSet ds = new DataSet(); string yrmo = ddlYrmo.SelectedItem.Text; ds = ReconDAL.GetEAPReconData(yrmo); if (ds.Tables[0].Rows.Count > 0) { resultDiv.Visible = true; grdvResult.DataSource = ds; grdvResult.DataBind(); } else { resultDiv.Visible = false; } }
protected void checkPastRecon() { string _yrmo = ddlYrmo.SelectedItem.Text.ToString(); resultDiv.Visible = false; lbl_error.Text = ""; if (ReconDAL.pastReconcile(_yrmo, "DOM")) { MultiView1.SetActiveView(view_reconAgn); lbl_reconAgn.Text = "Reconciled already for year-month (YRMO): " + _yrmo + ".<br /> Do you want to re-reconcile?"; } else { lbl_error.Text = ""; MultiView1.SetActiveView(view_main); } }
protected void btn_reimport_Click(object sender, EventArgs e) { string yrmo = ddlYrmo.SelectedItem.Text; lbl_error.Text = ""; try { ImportDAL iObj = new ImportDAL(); iObj.Rollback("HTH", yrmo); ReconDAL.pastReconcileDelete(yrmo, "INTL"); MultiView1.SetActiveView(view_main); } catch (Exception ex) { lbl_error1.Text = "Error in re-importing file.<br />" + ex.Message; } }
private void SortGridView(string sortExpression, string direction, string source) { // You can cache the DataTable for improving performance string yrmo = ddlYrmo.SelectedItem.Text; switch (source) { case "INTL": DataTable dt = ReconDAL.GetIntlReconData(yrmo).Tables[0]; DataView dv = new DataView(dt); dv.Sort = sortExpression + direction; grdvResult.DataSource = dv; grdvResult.DataBind(); break; case "INTL_HTH": DataSet ds1 = new DataSet(); ds1 = ReconDAL.getUnmatch(yrmo); if (ds1.Tables["HTHDATA"].Rows.Count > 0) { DataTable dt1 = ds1.Tables["HTHDATA"]; DataView dv1 = new DataView(dt1); dv1.Sort = sortExpression + direction; grdvHTH.DataSource = dv1; grdvHTH.DataBind(); } break; case "INTL_ANTH": DataSet ds2 = new DataSet(); ds2 = ReconDAL.getUnmatch(yrmo); if (ds2.Tables["ANTHDATA"].Rows.Count > 0) { DataTable dt2 = ds2.Tables["ANTHDATA"]; DataView dv2 = new DataView(dt2); dv2.Sort = sortExpression + direction; grdvAnth.DataSource = dv2; grdvAnth.DataBind(); } break; } }
protected void btn_reconAgn_Click(object sender, EventArgs e) { string yrmo = ddlYrmo.SelectedItem.Text.ToString(); lbl_error.Text = ""; try { ReconDAL.pastReconcileDelete(yrmo, "INTL"); if (RptsImported()) { ReconDAL.IntlReconcile(yrmo); ShowResult(); auditRecon(yrmo); } } catch (Exception ex) { ReconDAL.pastReconcileDelete(yrmo, "INTL"); lbl_error.Text = "Error in re-reconciliation.<br />" + ex.Message; } }
protected void btn_genRpt_Click(object sender, EventArgs e) { string yrmo = ddlYrmo.SelectedItem.Text; DataSet ds = new DataSet(); ds.Clear(); string[][] cols = { new string[] { "YRMO", "Anthem Group Suffix", "Anthem Covg. Code", "EBA Count", "EBA Amount", "Anthem Count", "Anthem Amount", "EBA Count Variance", "EBA vs Anthem % Count Variance", "Threshold", "Threshold Level" } }; string[][] colsFormat = { new string[] { "string", "string", "string", "number", "decimal", "number", "decimal", "number", "decimal", "decimal", "string" } }; string[] sheetnames = { "Intl_Bill_Recon" }; string[] titles = { "International Billing Reconciliation for YRMO - " + yrmo }; try { ds = ReconDAL.GetIntlReconData(yrmo); ExcelReport.ExcelXMLRpt(ds, "InternationalRecon_" + yrmo, sheetnames, titles, cols, colsFormat); } catch (Exception ex) { MultiView1.SetActiveView(view_main); lbl_error.Text = "Error in generating excel report" + ex.Message; } }
protected void btn_reconcile_Click(object sender, EventArgs e) { string yrmo = ddlYrmo.SelectedItem.Text; if (Page.IsValid) { lbl_error.Text = ""; try { if (RptsImported()) { ReconDAL.insertEAPRecon(yrmo); ShowResult(); auditRecon(yrmo); } } catch (Exception ex) { ReconDAL.pastReconcileDelete(yrmo, "EAP"); lbl_error.Text = "Error in reconciling<br />" + ex.Message; } } }
protected static DataSet DFRecords(string yrmo) { DataSet dsMatched1 = ReconDAL.getReconM(yrmo); DataSet dfDups = getDFDups(yrmo); DataSet dsUnMatched1 = ReconDAL.getClaimsUM(yrmo); DataRow rowNew; DataSet dsDFTotal = new DataSet(); DataTable tempTable1, newTable1; tempTable1 = dsMatched1.Tables[0]; newTable1 = dsDFTotal.Tables.Add("newTable1"); DataColumn col; col = new DataColumn("Type"); newTable1.Columns.Add(col); col = new DataColumn("RecordCount"); newTable1.Columns.Add(col); col = new DataColumn("DupCount"); newTable1.Columns.Add(col); col = new DataColumn("TotalCount"); newTable1.Columns.Add(col); int _grandTotal = 0; rowNew = newTable1.NewRow(); rowNew["Type"] = "Anthem DF Records Summary"; rowNew["RecordCount"] = ""; rowNew["DupCount"] = ""; rowNew["TotalCount"] = ""; newTable1.Rows.Add(rowNew); rowNew = newTable1.NewRow(); rowNew["Type"] = "Matched without Dups"; rowNew["RecordCount"] = ""; rowNew["DupCount"] = ""; _grandTotal = dsMatched1.Tables[0].Rows.Count - getDupsCountDFinMatched(yrmo, " > 1"); rowNew["TotalCount"] = _grandTotal; newTable1.Rows.Add(rowNew); List <int> _dupCnt = new List <int>(); _dupCnt = getRecordCountDFRFDup(yrmo); foreach (int x in _dupCnt) { int _temp = getDupsCountDFinMatched(yrmo, " = " + x); if (_temp != 0) { rowNew = newTable1.NewRow(); rowNew["Type"] = "Dups on Matched Report"; rowNew["RecordCount"] = x; rowNew["DupCount"] = _temp; rowNew["TotalCount"] = (x * _temp); _grandTotal = _grandTotal + (x * _temp); newTable1.Rows.Add(rowNew); } } rowNew = newTable1.NewRow(); rowNew["Type"] = "UnMatched"; rowNew["RecordCount"] = ""; rowNew["DupCount"] = ""; rowNew["TotalCount"] = dsUnMatched1.Tables[0].Rows.Count; _grandTotal = _grandTotal + dsUnMatched1.Tables[0].Rows.Count; newTable1.Rows.Add(rowNew); rowNew = newTable1.NewRow(); rowNew["Type"] = "Grand Total:"; rowNew["RecordCount"] = ""; rowNew["DupCount"] = ""; rowNew["TotalCount"] = _grandTotal; newTable1.Rows.Add(rowNew); rowNew = newTable1.NewRow(); rowNew["Type"] = ""; rowNew["RecordCount"] = ""; rowNew["DupCount"] = ""; rowNew["TotalCount"] = ""; newTable1.Rows.Add(rowNew); return(dsDFTotal); }
private void SortGridView(string sortExpression, string direction, string source) { ClaimsRecon repObj = new ClaimsRecon(); DataTable dt; DataView dv; string yrmo = ddlYrmo.SelectedItem.Text; switch (source) { case "recon": dt = repObj.DF_DFRFClaimsRecon(yrmo).Tables[0]; dv = new DataView(dt); dv.Sort = sortExpression + direction; grdvResult.DataSource = dv; grdvResult.DataBind(); break; case "DFAging": //dt = ReconDAL.getDFAgingRpt(yrmo).Tables[0]; dt = repObj.DFnoRFAging(yrmo).Tables[0]; dv = new DataView(dt); dv.Sort = sortExpression + direction; grdvDFAging.DataSource = dv; grdvDFAging.DataBind(); break; case "DFRFAging": //dt = ReconDAL.getDFAgingRpt(yrmo).Tables[0]; dt = repObj.DFRFAging(yrmo).Tables[0]; dv = new DataView(dt); dv.Sort = sortExpression + direction; grdvDFRFAging.DataSource = dv; grdvDFRFAging.DataBind(); break; case "DFRFmismatchAging": //dt = ReconDAL.getDFAgingRpt(yrmo).Tables[0]; dt = repObj.getMismatchCF(yrmo).Tables[0]; dv = new DataView(dt); dv.Sort = sortExpression + direction; grdvDFRFmismatchAging.DataSource = dv; grdvDFRFmismatchAging.DataBind(); break; case "recon_matched": dt = ReconDAL.getMatchedDCNAmnt(yrmo, "Matched").Tables[0]; dv = new DataView(dt); dv.Sort = sortExpression + direction; grdvMatched.DataSource = dv; grdvMatched.DataBind(); break; case "recon_mismatched": dt = ReconDAL.getMatchedDCNAmnt(yrmo, "UnMatched").Tables[0]; dv = new DataView(dt); dv.Sort = sortExpression + direction; grdvMatched.DataSource = dv; grdvMatched.DataBind(); break; case "recon_unmatchDFRF": dt = ReconDAL.getRFDFUM(yrmo).Tables[0]; dv = new DataView(dt); dv.Sort = sortExpression + direction; grdvUnmatchedDFRF.DataSource = dv; grdvUnmatchedDFRF.DataBind(); break; case "recon_unmatchAnth": dt = ReconDAL.getClaimsUM(yrmo).Tables[0]; dv = new DataView(dt); dv.Sort = sortExpression + direction; grdvUnmatchedAnth.DataSource = dv; grdvUnmatchedAnth.DataBind(); break; } }
protected void lnkAdjMatched_OnClick(object sender, EventArgs e) { lblErrRep.Text = ""; ClaimsRecon repObj3 = new ClaimsRecon(); DataSet ds = new DataSet(); DataSet dsFinal = new DataSet(); dsFinal.Clear(); string yrmo = repObj3.latestReconYrmo(); string filename = "DFRF_matched_mismatched_Details_" + yrmo; string[][] cols = { new string[] { "YRMO", "DCN", "Anthem Claims Amount", "DFRF Amount", "Variance" }, new string[] { "YRMO", "DCN", "Anthem Claims Amount", "DFRF Amount", "Variance" }, new string[] { "YRMO", "DCN", "Subscriber ID", "Paid Date", "DFRF Amount" }, new string[] { "YRMO", "DCN", "Subscriber ID", "Paid Date", "Anthem Claims Amount" }, new string[] { "Type", "Record Count", "Dups Count", "Total Count" } }; string[][] colsFormat = { new string[] { "string", "string", "decimal", "decimal", "decimal" }, new string[] { "string", "string", "decimal", "decimal", "decimal" }, new string[] { "string", "string", "string", "string", "decimal" }, new string[] { "string", "string", "string", "string", "decimal" }, new string[] { "string", "string", "string", "string" } }; string[] sheetnames = { "Matched_Records", "Mismatched_Records", "DFRF_with_no_matching_Anthem", "Anthem_with_no_matching_DFRF", "Records_Summary" }; string[] titles = { "DFRF Reconciliation Matched Records by DCN and Amount for " + yrmo, "DFRF Reconciliation Mismatched Records by Amount for " + yrmo, "DCN Records in DF/RF not in Anthem Claims for " + yrmo, "DCN records in Anthem Claims not in DF/RF for " + yrmo, "Summary of records from Anthem DF Report & DFRF Report for " + yrmo }; try { ds = ReconDAL.getMatchedDCNAmnt(yrmo, "Matched"); ds.Tables[0].TableName = "matchedTable"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[0].TableName = "matchedTableF"; ds.Clear(); ds = ReconDAL.getMatchedDCNAmnt(yrmo, "UnMatched"); ds.Tables[0].TableName = "mismatchedTable"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[1].TableName = "mismatchedTableF"; ds.Clear(); ds = ReconDAL.getRFDFUM(yrmo); ds.Tables[0].TableName = "unmatch_DFRFTable"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[2].TableName = "unmatch_DFRFTableF"; ds.Clear(); ds = ReconDAL.getClaimsUM(yrmo); ds.Tables[0].TableName = "unmatch_AnthTable"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[3].TableName = "unmatch_AnthTableF"; ds.Clear(); ds = RecordCount.SummaryRecordsFinal(yrmo); ds.Tables[0].TableName = "summRecords"; dsFinal.Tables.Add(ds.Tables[0].Copy()); dsFinal.Tables[4].TableName = "summRecordsF"; ds.Clear(); ExcelReport.ExcelXMLRpt(dsFinal, filename, sheetnames, titles, cols, colsFormat); } catch (Exception ex) { lblErrRep.Text = "Error in generating excel report" + ex.Message; } }
static void TestPostgresql() { // string processingDateTimeString = DateTime.Now.AddDays(-1).ToString(Constants.ProcessingDateFormat); string processingDateTimeString = "20-11-15"; //var connString = "Host=127.0.0.1;Port=5432;Username=postgres;Password=postgres;Database=postgres;"; var connString = "Server=127.0.0.1;Port=5439;User Id=postgres;Database=postgres;"; // Server=XX.XX.XX.XX;Port=5433;User Id=XXXX;Password=XXXX;Database=XXXX;Pooling=true; // MaxPoolSize=100;ConnectionLifeTime=15;Timeout=45;CommandTimeout=30;ApplicationName=ManagedAppServer; // SearchPath=XXXXXX; Console.WriteLine(processingDateTimeString); using (var conn = new NpgsqlConnection(connString)) { try { conn.Open(); // // Insert some data // using (var cmd = new NpgsqlCommand()) // { // cmd.Connection = conn; // cmd.CommandText = "INSERT INTO data (some_field) VALUES (@p)"; // cmd.Parameters.AddWithValue("p", "Hello world"); // cmd.ExecuteNonQuery(); // } // Retrieve all rows var sql1 = "SELECT * FROM trx2_details_src8;"; var sql2 = "select cardno as cardno, authcode as authcode, trx_date as trx_date, report_date as report_date " + "from trx2_details_src8;"; var sql3 = "select reqref as reqref from trial;"; using (var cmd = new NpgsqlCommand(sql3, conn)) using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { // using sql2 query.. //Console.WriteLine(reader["cardno"] + ", " + reader["authcode"] + ", " + reader["trx_date"]); Console.WriteLine(reader["reqref"]); } } IEnumerable <TX_CSC_FINANCIAL> CRecords = ReconDAL.GetCRecords(processingDateTimeString, Constants.CONST_AGENTID_ONLINE, Constants.CONST_TXN_TYPE_CD_TOPUP); // if (CRecords != null && CRecords.Any()) // { // foreach(var c in CRecords) // { // Console.WriteLine(c.CSC_APP_NO + ", " + c.PROCESSING_DATE.ToString()); // Console.WriteLine(c.PURSE_TXN_CTR); // } // } // delegate with lambda.. // meaning take a IEnumerable<TX_CSC_FINANCIAL> for input, and return a // IEnumerable<TX_CSC_FINANCIAL>.. Func <IEnumerable <TX_CSC_FINANCIAL>, IEnumerable <TX_CSC_FINANCIAL> > func = cRec => { var enumerable = new List <TX_CSC_FINANCIAL>(); foreach (var c in cRec) { enumerable.Add(c); } return(enumerable); }; // delegate with lambda.. Func <IEnumerable <TX_CSC_FINANCIAL>, IEnumerable <TX_CSC_FINANCIAL> > func2 = cRec => { var enumerable = cRec.Where(c => c.PURSE_TXN_CTR > 2000); return(enumerable); }; var j = func2(CRecords); // calls the delegate func if (j != null && j.Any()) { foreach (var c in j) { Console.WriteLine(c.CSC_APP_NO + ", " + c.TXN_DATETIME.ToString()); Console.WriteLine(c.PURSE_TXN_CTR); } Console.WriteLine("Count = {0}", j.Count()); } } catch (Exception e) { Console.WriteLine(e.ToString()); } } }