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