public static string ExtractStmtData(AccountDetails details, string CBAConstr, string ClirecConstr, HttpContext context, string CBAType, string MsgType)
{
string uuid = Guid.NewGuid().ToString();
DataTable data = new DataTable();
try
{
var db = new DBConnector(CBAConstr, ClirecConstr);
Utils.Log("Beginging Data Extraction for (" + details.AccountCode + ") using Interface definition ID (" + details.DefinitionID + ")");
InterfaceDefinition def = db.getDefinition(details.DefinitionID);
string mappedAccts = ""; var map = new StringBuilder(); int ct = 1;
foreach (string cba in details.StmtCBAccount)
{
map.Append("'").Append(cba).Append("'");
if (ct < details.StmtCBAccount.Count)
{
map.Append(",");
}
ct++;
}
mappedAccts = map.ToString();
//string sql = def.script.Replace("startdate", getDate(details.LastLedgerDate)).
// Replace("enddate", details.Endate).Replace("acctid", mappedAccts).
// Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
Utils.Log("Connecting to transaction Database");
var sql = "";
string Startdate, Enddate;
Startdate = getDate(details.LastStmtDate);
Enddate = details.Endate.toDateTime().ToString("dd-MMM-yyyy");
switch (CBAType.CleanUp())
{
case "mysql":
sql = def.script.Replace("startdate", details.LastStmtDate.ToString("yyyy-MM-dd")).
Replace("enddate", Convert.ToDateTime(details.Endate).ToString("yyyy-MM-dd")).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode).Replace("mtype", MsgType);
Utils.Log(sql);
data = db.fetchMySQLCBA(sql);
break;
case "sqlserver":
sql = def.script.Replace("startdate", Startdate).
Replace("enddate", Enddate).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode).Replace("mtype", MsgType);
Utils.Log(sql);
data = db.getSQLServerCBA(sql);
break;
default:
sql = def.script.Replace("startdate", Startdate).
Replace("enddate", Enddate).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode).Replace("mtype", MsgType);
Utils.Log(sql);
data = db.fetchCBA(sql);
break;
}
int nofT = data.Rows.Count;
bool isData = nofT > 0;
Utils.Log(nofT + " transaction(s) fetched");
int sn = 1;
data = Utils.AddExtractionColumns(data);
foreach (DataRow row in data.Rows)
{
row["PostDate"] = row[def.postDateCol].toDateTime();
row["Valdate"] = row[def.valDateCol].toDateTime();
bool isDebit = row[def.directionCol].ToString().ToUpper().Equals("D");
row["CrDr"] = (isDebit ? "1" : "2");
row["Debits"] = (isDebit ? row[def.amountCol].toDecimal() : decimal.Zero);
row["Credits"] = (isDebit ? decimal.Zero : row[def.amountCol].toDecimal());
row["Amount"] = row["Credits"].toDecimal() + row["Debits"].toDecimal();
getUDFs(row, def);
row["Details"] = getNaration(def, row);
row["SN"] = sn++;
row["Id"] = uuid;
row["username"] = "******";
}
var balance = decimal.Zero;
if (isData)
{
Utils.Log("Attempting to Fetch Account Balance");
SqlConnection conn = new SqlConnection();
foreach (string cba in details.StmtCBAccount)
{
DataTable baldata;
switch (CBAType.CleanUp())
{
case "mysql":
sql = def.balScript.Replace("startdate", Convert.ToDateTime(Utils.getFirstDayofMonth(details.CurrentDate)).ToString("yyyy-MM-dd"))
.Replace("enddate", Convert.ToDateTime(details.Endate).ToString("yyyy-MM-dd")).Replace("acctid", cba).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
baldata = db.fetchMySQLCBA(sql);
break;
case "sqlserver":
conn.ConnectionString = CBAConstr;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 0;
sql = def.balScript.Replace("startdate", Utils.getFirstDayofMonth(details.CurrentDate))
.Replace("enddate", Enddate).Replace("acctid", cba).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
Utils.Log(sql);
cmd.CommandText = sql;
baldata = db.fetchCBA(cmd);
break;
default:
sql = def.balScript.Replace("startdate", Utils.getFirstDayofMonth(details.CurrentDate))
.Replace("enddate", Enddate).Replace("acctid", cba).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
baldata = db.fetchCBA(sql);
break;
}
if (baldata.Rows.Count > 0)
{
balance += baldata.Rows[0][def.balCol].toDecimal();
}
// Utils.Log(sql);
Utils.Log("Balance downloaded successfully and = " + balance.ToString("0.00"));
conn.Close();
}
string[] selected = new[] { "SN", "PostDate", "Valdate", "Details", "Debits", "Credits", "Amount", "CrDr", "ud1", "ud2", "ud3", "ud4", "ud5", "Id", "username" };
data = new DataView(data).ToTable(false, selected);
context.Session["TotalExempted"] = 0;
context.Session["side"] = "S";
context.Session["TotalExtraction"] = data.Rows.Count;
context.Session["ToatlCredits"] = Convert.ToDecimal(data.Compute("Sum(Credits)", ""));
context.Session["ToatlDebits"] = Convert.ToDecimal(data.Compute("Sum(Debits)", ""));
context.Session["latest"] = Convert.ToDateTime(data.Compute("Max(PostDate)", ""));
context.Session["CreditCount"] = data.Compute("count(CrDr)", "CrDr ='2' ").toInt();
context.Session["DebitCount"] = data.Compute("count(CrDr)", "CrDr ='1' ").toInt();
var fp = from row in data.AsEnumerable() where Convert.ToInt32(row["SN"]) <= 1000 orderby row["SN"] select row;
context.Session["FirstPage"] = fp.CopyToDataTable();
db.CopyDataTableToDB(data, "ExtractionTemp");
}
var ID = uuid;
return((isData) ? ID + "$" + balance.ToString("0.00") : "No Data Pulled!");
}
catch (Exception e)
{
throw new Exception("CBA Download failed for " + details.AccountName + "(" + details.AccountCode + ") because: " + e.Message);
}
finally
{
data.Clear();
data.Dispose();
}
}
public static ExctractionDetails ExtractData(AccountDetails details, string CBAConstr, string ClirecConstr, string CBAType, bool isNIP, string NIPsession)
{
string uuid = Guid.NewGuid().ToString();
DataTable data = new DataTable();
var Edetails = new ExctractionDetails();
Edetails.Data = new DataTable();
try
{
var db = new DBConnector(CBAConstr, ClirecConstr);
Utils.Log("Beginging Data Etraction for (" + details.AccountCode + ") using Interface definition ID (" + details.DefinitionID + ")");
InterfaceDefinition def = db.getDefinition(details.DefinitionID);
string mappedAccts = ""; var map = new StringBuilder(); int ct = 1;
foreach (string cba in details.CBAccount)
{
map.Append("'").Append(cba).Append("'");
if (ct < details.CBAccount.Count)
{
map.Append(",");
}
ct++;
}
mappedAccts = map.ToString();
//string sql = def.script.Replace("startdate", getDate(details.LastLedgerDate)).
// Replace("enddate", details.Endate).Replace("acctid", mappedAccts).
// Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
Utils.Log("Connecting to transaction Database");
var sql = "";
string Startdate, Enddate;
if (isNIP)
{
switch (NIPsession.toInt())
{
case 1:
Startdate = details.LastLedgerDate.ToString("dd-MMM-yy") + " 2.00.00 PM";
Enddate = details.Endate.toDateTime().ToString("dd-MMM-yy") + " 11.59.59 PM";
break;
default:
Startdate = details.LastLedgerDate.ToString("dd-MMM-yy") + " 12.00.00 AM";
Enddate = details.Endate.toDateTime().ToString("dd-MMM-yy") + " 1.59.59 PM";
break;
}
}
else
{
Startdate = getDate(details.LastLedgerDate);
Enddate = details.Endate.toDateTime().ToString("dd-MMM-yyyy");
}
switch (CBAType.CleanUp())
{
case "mysql":
sql = def.script.Replace("startdate", details.LastLedgerDate.ToString("yyyy-MM-dd")).
Replace("enddate", Convert.ToDateTime(details.Endate).ToString("yyyy-MM-dd")).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
Utils.Log(sql);
data = db.fetchMySQLCBA(sql);
break;
case "sqlserver":
sql = def.script.Replace("startdate", Startdate).
Replace("enddate", Enddate).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
Utils.Log(sql);
data = db.getSQLServerCBA(sql);
break;
default:
sql = def.script.Replace("startdate", Startdate).
Replace("enddate", Enddate).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
Utils.Log(sql);
data = db.fetchCBA(sql);
break;
}
int nofT = data.Rows.Count;
bool isData = nofT > 0;
Utils.Log(nofT + " transaction(s) fetched");
int sn = 1;
data = Utils.AddExtractionColumns(data);
foreach (DataRow row in data.Rows)
{
row["PostDate"] = row[def.postDateCol].toDateTime();
row["Valdate"] = row[def.valDateCol].toDateTime();
bool isDebit = row[def.directionCol].ToString().ToUpper().Equals("D");
row["CrDr"] = (isDebit ? "1" : "2");
row["Debits"] = (isDebit ? row[def.amountCol].toDecimal() : decimal.Zero);
row["Credits"] = (isDebit ? decimal.Zero : row[def.amountCol].toDecimal());
row["Amount"] = row["Credits"].toDecimal() + row["Debits"].toDecimal();
getUDFs(row, def);
row["Details"] = getNaration(def, row);
row["SN"] = sn++;
row["Id"] = uuid;
row["username"] = "******";
}
var balance = decimal.Zero;
if (isData)
{
try
{
Utils.Log("Computing Max Submited Date");
Edetails.LastSubmitedDate = data.Rows[0]["SubmittedOn"].ToString();
Utils.Log("Max Submitted Date =" + Edetails.LastSubmitedDate);
}
catch (Exception e)
{
Utils.Log("Failed to obtain Max submitted date because:" + e.Message);
}
if (isNIP && NIPsession.toInt() == 2)
{
}
else
{
Utils.Log("Attempting to Fetch Account Balance");
foreach (string cba in details.CBAccount)
{
DataTable baldata;
if (isNIP)
{
Enddate = Enddate.toDateTime().ToString("dd-MMM-yyyy");
}
switch (CBAType.CleanUp())
{
case "mysql":
sql = def.balScript.Replace("startdate", Convert.ToDateTime(Utils.getFirstDayofMonth(details.CurrentDate)).ToString("yyyy-MM-dd"))
.Replace("enddate", Convert.ToDateTime(details.Endate).ToString("yyyy-MM-dd")).Replace("acctid", cba).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
baldata = db.fetchMySQLCBA(sql);
break;
case "sqlserver":
sql = def.balScript.Replace("startdate", Utils.getFirstDayofMonth(details.CurrentDate))
.Replace("enddate", Enddate).Replace("acctid", cba).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
baldata = db.getSQLServerCBA(sql);
break;
default:
sql = def.balScript.Replace("startdate", Utils.getFirstDayofMonth(details.CurrentDate))
.Replace("enddate", Enddate).Replace("acctid", cba).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
baldata = db.fetchCBA(sql);
break;
}
if (baldata.Rows.Count > 0)
{
balance += baldata.Rows[0][def.balCol].toDecimal();
}
}
Utils.Log(sql);
Utils.Log("Balance downloaded successfully and = " + balance.ToString("0.00"));
}
string[] selected = new[] { "SN", "PostDate", "Valdate", "Details", "Debits", "Credits", "Amount", "CrDr", "ud1", "ud2", "ud3", "ud4", "ud5", "Id", "username" };
data = new DataView(data).ToTable(false, selected);
Edetails.TotalCredits = Convert.ToDecimal(data.Compute("Sum(Credits)", ""));
Edetails.TotalDebits = Convert.ToDecimal(data.Compute("Sum(Debits)", ""));
Edetails.Latest = Convert.ToDateTime(data.Compute("Max(PostDate)", ""));
Edetails.CreditCount = data.Compute("count(CrDr)", "CrDr ='2' ").toInt();
Edetails.DebitCount = data.Compute("count(CrDr)", "CrDr ='1' ").toInt();
Edetails.DataID = uuid;
Edetails.Balance = balance;
db.CopyDataTableToDB(data, "ExtractionTemp");
}
return(Edetails);
}
catch (Exception e)
{
throw new Exception("CBA Download failed for " + details.AccountName + "(" + details.AccountCode + ") because: " + e.Message);
}
finally
{
data.Clear();
data.Dispose();
}
}
public static ExctractionDetails ExtractAjustedEntries(AccountDetails details, string CBAConstr, string ClirecConstr, string CBAType, string submitedDateCol)
{
string uuid = Guid.NewGuid().ToString();
DataTable data = new DataTable();
var Edetails = new ExctractionDetails();
Edetails.Data = new DataTable();
try
{
var db = new DBConnector(CBAConstr, ClirecConstr);
Utils.Log("Beginging Data Etraction for (" + details.AccountCode + ") using Interface definition ID (" + details.DefinitionID + ")");
InterfaceDefinition def = db.getDefinition(details.DefinitionID);
string mappedAccts = ""; var map = new StringBuilder(); int ct = 1;
foreach (string cba in details.CBAccount)
{
map.Append("'").Append(cba).Append("'");
if (ct < details.CBAccount.Count)
{
map.Append(",");
}
ct++;
}
mappedAccts = map.ToString();
Utils.Log("Connecting to transaction Database");
var sql = "";
sql = "select lastdate from AdjustmentPosition where AccountID='" + details.AccountCode + "'";
var dt = db.getDataSet(sql);
string Startdate, Enddate, StartSubmitedDate, EndSubmited;
Startdate = Utils.getFirstDayofMonth(details.CurrentDate);
Enddate = Utils.getLastDateofMonth(details.CurrentDate);
StartSubmitedDate = dt.Rows.Count > 0? dt.Rows[0]["lastdate"].ToString(): Enddate;
EndSubmited = Utils.getLastDateofMonth(details.CurrentDate.AddMonths(1));
switch (CBAType.CleanUp())
{
case "mysql":
sql = def.script.Replace("startdate", details.LastLedgerDate.ToString("yyyy-MM-dd")).
Replace("enddate", Convert.ToDateTime(details.Endate).ToString("yyyy-MM-dd")).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
Utils.Log(sql);
data = db.fetchMySQLCBA(sql);
break;
case "sqlserver":
sql = def.script.Replace("startdate", Startdate).
Replace("enddate", Enddate).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode).Replace("StartSubDate", StartSubmitedDate).Replace("EndSubDate", EndSubmited);
Utils.Log(sql);
data = db.getSQLServerCBA(sql);
break;
default:
sql = def.script.Replace("startdate", Startdate).
Replace("enddate", Enddate).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode).Replace("StartSubDate", StartSubmitedDate).Replace("EndSubDate", EndSubmited);
Utils.Log(sql);
data = db.fetchCBA(sql);
break;
}
int nofT = data.Rows.Count;
bool isData = nofT > 0;
Utils.Log(nofT + " transaction(s) fetched");
int sn = 1;
data = Utils.AddExtractionColumns(data);
foreach (DataRow row in data.Rows)
{
row["PostDate"] = row[def.postDateCol].toDateTime();
row["Valdate"] = row[def.valDateCol].toDateTime();
bool isDebit = row[def.directionCol].ToString().ToUpper().Equals("D");
row["CrDr"] = (isDebit ? "1" : "2");
row["Debits"] = (isDebit ? row[def.amountCol].toDecimal() : decimal.Zero);
row["Credits"] = (isDebit ? decimal.Zero : row[def.amountCol].toDecimal());
row["Amount"] = row["Credits"].toDecimal() + row["Debits"].toDecimal();
getUDFs(row, def);
row["Details"] = getNaration(def, row);
row["SN"] = sn++;
row["Id"] = uuid;
row["username"] = "******";
}
var balance = decimal.Zero;
if (isData)
{
Utils.Log("Computing Max Submited Date");
Edetails.LastSubmitedDate = data.Rows[0]["SubmittedOn"].ToString();
Utils.Log("Max Submitted Date =" + Edetails.LastSubmitedDate);
string[] selected = new[] { "SN", "PostDate", "Valdate", "Details", "Debits", "Credits", "Amount", "CrDr", "ud1", "ud2", "ud3", "ud4", "ud5", "Id", "username" };
data = new DataView(data).ToTable(false, selected);
balance = details.Balance + data.Compute("sum(Credits)", "").toDecimal() - data.Compute("sum(Debits)", "").toDecimal();
Edetails.TotalCredits = Convert.ToDecimal(data.Compute("Sum(Credits)", ""));
Edetails.TotalDebits = Convert.ToDecimal(data.Compute("Sum(Debits)", ""));
Edetails.Latest = Convert.ToDateTime(data.Compute("Max(PostDate)", ""));
Edetails.CreditCount = data.Compute("count(CrDr)", "CrDr ='2' ").toInt();
Edetails.DebitCount = data.Compute("count(CrDr)", "CrDr ='1' ").toInt();
Edetails.DataID = uuid;
Edetails.Balance = balance;
db.CopyDataTableToDB(data, "ExtractionTemp");
}
return(Edetails);
}
catch (Exception e)
{
throw new Exception("CBA Download failed for " + details.AccountName + "(" + details.AccountCode + ") because: " + e.Message);
}
finally
{
data.Clear();
data.Dispose();
}
}
