public EBO_Statement_Detail_Update(bool inTestMode, string inCredGrp = "")
{
AMC_Functions.DetermineDSNFile oDSN = new AMC_Functions.DetermineDSNFile();
connectionString = inTestMode ? oDSN.getDSNFile("jerrodr", "Training DB5", true) : oDSN.getDSNFile("jerrodr", "DB5", true);
connectionString_RO = inTestMode ? oDSN.getDSNFile("jerrodr", "Training DB5", false) : oDSN.getDSNFile("jerrodr", "DB5", false);
using (OdbcConnection con = new OdbcConnection(connectionString_RO))
{
con.Open();
QueryData(inCredGrp, con, inTestMode);
AddInDTHeaders();
EvaluateData(con, inTestMode);
ExportDataIfApplicable(inCredGrp);
}
InsertIntoDataBase(inCredGrp);
}
/// <summary>
/// Query for all of the data
/// </summary>
/// <param name="inCredGrp"></param>
private void QueryData(string inCredGrp, OdbcConnection inCN, bool inTestMode)
{
string selectSQL;
DateTime queryDate = DateTime.Today.AddDays(DaysToLookBack * -1);
if (inTestMode)
{
if (inCredGrp == "")
{
selectSQL = $@"SELECT amanumber, amdnumber, ambalance, amcnumber, acctwin_A.wdtext[3] as 'TotalCharges', acctwin_A.wdtext[4] as 'TotalAdjust', acctwin_A.wdtext[5] as 'TotalIns', acctwin_A.wdtext[6] as 'TotalPat',
'' as 'TotalPayments',
'' as 'TotalInsPayments',
'' as 'TotalAdjusts', acctwin_A.wdtext[1] as 'ADATA:AA1', acctwin_A.wdtext[2] as 'ADATA:AA2', acctwin_A.wdtext[3] as 'ADATA:AA3', acctwin_A.wdtext[4] as 'ADATA:AA4', acctwin_A.wdtext[5] as 'ADATA:AA5', acctwin_A.wdtext[6] as 'ADATA:AA6', acctwin_A.wdtext[7] as 'ADATA:AA7', acctwin_A.wdtext[8] as 'ADATA:AA8', acctwin_A.wdtext[9] as 'ADATA:AA9', acctwin_A.wdtext[10] as 'ADATA:AA10', acctwin_A.wdtext[11] as 'ADATA:AA11', acctwin_A.wdtext[12] as 'ADATA:AA12', acctwin_A.wdtext[13] as 'ADATA:AA13', acctwin_A.wdtext[14] as 'ADATA:AA14', acctwin_A.wdtext[15] as 'ADATA:AA15', acctwin_A.wdtext[16] as 'ADATA:AA16'
FROM PUB.acctmstr qA
LEFT JOIN PUB.windata acctwin_A on acctwin_A.wdtype = 'A' and acctwin_A.wdcode = 'A' and acctwin_A.wdnumber = amanumber and acctwin_A.wdagency = amagency
JOIN PUB.credgrpd on PUB.credgrpd.gdcnumber = amcnumber
WHERE ambalance > 0 and acctwin_A.wdtext[3] != '' and gdgnumber = '{StatementGroup}' WITH (NOLOCK)";
Console.WriteLine("Querying for all data to update for Statement Data..");
}
else
{
// otherwise query for the specific cred group
selectSQL = $@"SELECT amanumber, amdnumber, ambalance, amcnumber, acctwin_A.wdtext[3] as 'TotalCharges', acctwin_A.wdtext[4] as 'TotalAdjust', acctwin_A.wdtext[5] as 'TotalIns', acctwin_A.wdtext[6] as 'TotalPat',
'' as 'TotalPayments',
'' as 'TotalInsPayments',
'' as 'TotalAdjusts', acctwin_A.wdtext[1] as 'ADATA:AA1', acctwin_A.wdtext[2] as 'ADATA:AA2', acctwin_A.wdtext[3] as 'ADATA:AA3', acctwin_A.wdtext[4] as 'ADATA:AA4', acctwin_A.wdtext[5] as 'ADATA:AA5', acctwin_A.wdtext[6] as 'ADATA:AA6', acctwin_A.wdtext[7] as 'ADATA:AA7', acctwin_A.wdtext[8] as 'ADATA:AA8', acctwin_A.wdtext[9] as 'ADATA:AA9', acctwin_A.wdtext[10] as 'ADATA:AA10', acctwin_A.wdtext[11] as 'ADATA:AA11', acctwin_A.wdtext[12] as 'ADATA:AA12', acctwin_A.wdtext[13] as 'ADATA:AA13', acctwin_A.wdtext[14] as 'ADATA:AA14', acctwin_A.wdtext[15] as 'ADATA:AA15', acctwin_A.wdtext[16] as 'ADATA:AA16'
FROM PUB.acctmstr qA
LEFT JOIN PUB.windata acctwin_A on acctwin_A.wdtype = 'A' and acctwin_A.wdcode = 'A' and acctwin_A.wdnumber = amanumber and acctwin_A.wdagency = amagency
JOIN PUB.credgrpd on PUB.credgrpd.gdcnumber = amcnumber
WHERE ambalance > 0 and gdgnumber = '{inCredGrp}' WITH (NOLOCK)";
Console.WriteLine($"Querying for all data for {inCredGrp} to update for Statement Data for..");
}
using (OdbcCommand SelectCMD = new OdbcCommand(selectSQL, inCN))
using (OdbcDataAdapter adapter = new OdbcDataAdapter(SelectCMD))
{
adapter.Fill(dt_allData);
}
Console.WriteLine("Querying for the transactions for LIVE currently to evaluate for testing.");
// now, since we're testing, need to change over to the live connection and get the transactions for today, so we can get an accurate export of the data that needs to be updated
AMC_Functions.DetermineDSNFile oDSN = new AMC_Functions.DetermineDSNFile();
string connectionString = oDSN.getDSNFile("jerrodr", "DB5", false);
//(select sum(baamount) from PUB.tranmstr JOIN PUB.balances on PUB.balances.baserial = tmtserial JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber WHERE amdnumber = qA.amdnumber and tmrcptcode NOT IN ('I', 'Y') and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'C') as 'TotalPAyments'
//(select sum(baamount) from PUB.tranmstr JOIN PUB.balances on PUB.balances.baserial = tmtserial JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber WHERE amdnumber = qA.amdnumber and tmrcptcode IN ('I', 'Y') and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'C') as 'TotalInsPayments'
//(select sum(baamount) from PUB.tranmstr JOIN PUB.balances on PUB.balances.baserial = tmtserial JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber WHERE amdnumber = qA.amdnumber and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'A') as 'TotalAdjusts'
using (OdbcConnection ConLive = new OdbcConnection(connectionString))
{
string select_TotalPay = $@"SELECT sum(baamount) as 'TotalPayments'
from PUB.tranmstr
JOIN PUB.balances on PUB.balances.baserial = tmtserial
JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber
WHERE amdnumber = ? and tmrcptcode NOT IN ('I', 'Y') and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'C'";
string select_TotalIns = $@"SELECT sum(baamount) as 'TotalInsPayments'
from PUB.tranmstr
JOIN PUB.balances on PUB.balances.baserial = tmtserial
JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber
WHERE amdnumber = ? and tmrcptcode IN ('I', 'Y') and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'C'";
string select_TotalAdj = $@"SELECT sum(baamount) as 'TotalAdjusts'
from PUB.tranmstr
JOIN PUB.balances on PUB.balances.baserial = tmtserial
JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber
WHERE amdnumber = ? and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'A'";
ConLive.Open();
foreach (DataRow dr in dt_allData.Rows)
{
// total payments
using (OdbcCommand SelectCMD = new OdbcCommand(select_TotalPay, ConLive))
{
SelectCMD.Parameters.Add("@dbtr", OdbcType.VarChar).Value = dr["amdnumber"].ToString();
using (OdbcDataReader Reader = SelectCMD.ExecuteReader())
{
while (Reader.Read())
{
dr["TotalPayments"] = Reader["TotalPayments"].ToString();
}
}
}
// total insurnace payments
using (OdbcCommand SelectCMD = new OdbcCommand(select_TotalIns, ConLive))
{
SelectCMD.Parameters.Add("@dbtr", OdbcType.VarChar).Value = dr["amdnumber"].ToString();
using (OdbcDataReader Reader = SelectCMD.ExecuteReader())
{
while (Reader.Read())
{
dr["TotalInsPayments"] = Reader["TotalInsPayments"].ToString();
}
}
}
// total adjustments
using (OdbcCommand SelectCMD = new OdbcCommand(select_TotalAdj, ConLive))
{
SelectCMD.Parameters.Add("@dbtr", OdbcType.VarChar).Value = dr["amdnumber"].ToString();
using (OdbcDataReader Reader = SelectCMD.ExecuteReader())
{
while (Reader.Read())
{
dr["TotalAdjusts"] = Reader["TotalAdjusts"].ToString();
}
}
}
}
}
}
else
{
if (inCredGrp == "")
{
selectSQL = $@"SELECT amanumber, ambalance, amcnumber, acctwin_A.wdtext[3] as 'TotalCharges', acctwin_A.wdtext[4] as 'TotalAdjust', acctwin_A.wdtext[5] as 'TotalIns', acctwin_A.wdtext[6] as 'TotalPat',
(select sum(baamount) from PUB.tranmstr JOIN PUB.balances on PUB.balances.baserial = tmtserial JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber WHERE amdnumber = qA.amdnumber and tmrcptcode NOT IN ('I', 'Y') and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'C') as 'TotalPayments',
(select sum(baamount) from PUB.tranmstr JOIN PUB.balances on PUB.balances.baserial = tmtserial JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber WHERE amdnumber = qA.amdnumber and tmrcptcode IN ('I', 'Y') and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'C') as 'TotalInsPayments',
(select sum(baamount) from PUB.tranmstr JOIN PUB.balances on PUB.balances.baserial = tmtserial JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber WHERE amdnumber = qA.amdnumber and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'A') as 'TotalAdjusts', acctwin_A.wdtext[1] as 'ADATA:AA1', acctwin_A.wdtext[2] as 'ADATA:AA2', acctwin_A.wdtext[3] as 'ADATA:AA3', acctwin_A.wdtext[4] as 'ADATA:AA4', acctwin_A.wdtext[5] as 'ADATA:AA5', acctwin_A.wdtext[6] as 'ADATA:AA6', acctwin_A.wdtext[7] as 'ADATA:AA7', acctwin_A.wdtext[8] as 'ADATA:AA8', acctwin_A.wdtext[9] as 'ADATA:AA9', acctwin_A.wdtext[10] as 'ADATA:AA10', acctwin_A.wdtext[11] as 'ADATA:AA11', acctwin_A.wdtext[12] as 'ADATA:AA12', acctwin_A.wdtext[13] as 'ADATA:AA13', acctwin_A.wdtext[14] as 'ADATA:AA14', acctwin_A.wdtext[15] as 'ADATA:AA15', acctwin_A.wdtext[16] as 'ADATA:AA16'
FROM PUB.acctmstr qA
LEFT JOIN PUB.windata acctwin_A on acctwin_A.wdtype = 'A' and acctwin_A.wdcode = 'A' and acctwin_A.wdnumber = amanumber and acctwin_A.wdagency = amagency
JOIN PUB.credgrpd on PUB.credgrpd.gdcnumber = amcnumber
WHERE ambalance > 0 and acctwin_A.wdtext[3] != '' and gdgnumber = '{StatementGroup}' WITH (NOLOCK)";
Console.WriteLine("Querying for all data to update for Statement Data..");
}
else
{
// otherwise query for the specific cred group
selectSQL = $@"SELECT amanumber, ambalance, amcnumber, acctwin_A.wdtext[3] as 'TotalCharges', acctwin_A.wdtext[4] as 'TotalAdjust', acctwin_A.wdtext[5] as 'TotalIns', acctwin_A.wdtext[6] as 'TotalPat',
(select sum(baamount) from PUB.tranmstr JOIN PUB.balances on PUB.balances.baserial = tmtserial JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber WHERE amdnumber = qA.amdnumber and tmrcptcode NOT IN ('I', 'Y') and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'C') as 'TotalPayments',
(select sum(baamount) from PUB.tranmstr JOIN PUB.balances on PUB.balances.baserial = tmtserial JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber WHERE amdnumber = qA.amdnumber and tmrcptcode IN ('I', 'Y') and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'C') as 'TotalInsPayments',
(select sum(baamount) from PUB.tranmstr JOIN PUB.balances on PUB.balances.baserial = tmtserial JOIN PUB.acctmstr on PUB.acctmstr.amanumber = tmanumber WHERE amdnumber = qA.amdnumber and tmtrandate >= '{queryDate.ToString("yyyy-MM-dd")}' and tmtrancode = 'A') as 'TotalAdjusts', acctwin_A.wdtext[1] as 'ADATA:AA1', acctwin_A.wdtext[2] as 'ADATA:AA2', acctwin_A.wdtext[3] as 'ADATA:AA3', acctwin_A.wdtext[4] as 'ADATA:AA4', acctwin_A.wdtext[5] as 'ADATA:AA5', acctwin_A.wdtext[6] as 'ADATA:AA6', acctwin_A.wdtext[7] as 'ADATA:AA7', acctwin_A.wdtext[8] as 'ADATA:AA8', acctwin_A.wdtext[9] as 'ADATA:AA9', acctwin_A.wdtext[10] as 'ADATA:AA10', acctwin_A.wdtext[11] as 'ADATA:AA11', acctwin_A.wdtext[12] as 'ADATA:AA12', acctwin_A.wdtext[13] as 'ADATA:AA13', acctwin_A.wdtext[14] as 'ADATA:AA14', acctwin_A.wdtext[15] as 'ADATA:AA15', acctwin_A.wdtext[16] as 'ADATA:AA16'
FROM PUB.acctmstr qA
LEFT JOIN PUB.windata acctwin_A on acctwin_A.wdtype = 'A' and acctwin_A.wdcode = 'A' and acctwin_A.wdnumber = amanumber and acctwin_A.wdagency = amagency
JOIN PUB.credgrpd on PUB.credgrpd.gdcnumber = amcnumber
WHERE ambalance > 0 and gdgnumber = '{inCredGrp}' WITH (NOLOCK)";
Console.WriteLine($"Querying for all data for {inCredGrp} to update for Statement Data for..");
}
using (OdbcCommand SelectCMD = new OdbcCommand(selectSQL, inCN))
using (OdbcDataAdapter adapter = new OdbcDataAdapter(SelectCMD))
{
adapter.Fill(dt_allData);
}
}
}
