public List <ValuePair> getTables()
{
DBInterface db;
db = new PostgreSQLDBInterface(new Settings().warehousedb);
var pairs = db.getValuePair("id", "value", " select table_name as ID, table_name as Value FROM INFORMATION_SCHEMA.TABLES where table_type = 'BASE TABLE' and table_schema = 'public'");
var pairs2 = db.getValuePair("viewname", "viewname", "SELECT oid::regclass::text as viewname FROM pg_class WHERE relkind = 'm'");
pairs.AddRange(pairs2);
return(pairs);
}
public List <ValuePair> getViewColumns(string tablename)
{
DBInterface db;
db = new PostgreSQLDBInterface(new Settings().warehousedb);
var pairs = db.getValuePair("attname", "attname", "select a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod), a.attnotnull from pg_attribute a" +
" join pg_class t on a.attrelid = t.oid join pg_namespace s on t.relnamespace = s.oid" +
" where a.attnum > 0 and not a.attisdropped" +
" and t.relname = '" + tablename + "' and s.nspname = 'public'" +
" order by a.attnum; ");
return(pairs);
}
private void sch_int_rates(DataTable dt, ReportDocument report)
{
dt.AddTableColumns(new string[] { "TYPE_OF_ACCOUNT", "0-30", "31-60", "61-90", "91-180", "181-360", "OVER360" });
var sql = "select distinct a.\"CONDITION_CODE\", a.\"DESCRIPTION\" as Type_of_Account,b.\"CR_INT_RATE\" as interest_rate, b.\"GROUP_CY_DATE\", TRIM(both 'N D' from(substring(b.\"GROUP_CY_DATE\" FROM 5 FOR 9))) as effective_date from \"ACCT_GEN_CONDITION\" a join \"GROUP_CREDIT_INT\" b on \n" +
"(a.\"CONDITION_CODE\"=TRIM(both 'N' from substring(b.\"GROUP_CY_DATE\",'..')))";
var db = new PostgreSQLDBInterface(new Settings().sourcedb);
var data = db.getData(sql);
data.AddTableColumns(new string[] { "AgeGroup" });
foreach (DataRow row in data.Rows)
{
var datestr = row["effective_date"].ToString();
var efdate = DateTime.ParseExact(datestr, "yyyyMMdd", new CultureInfo("en-US"));
row["AgeGroup"] = efdate.getAgeGroup();
}
var grps = data.AsEnumerable().GroupBy(g => g["Type_of_Account"]).Select(grp => grp.ToList());
foreach (var grp in grps)
{
var row = dt.NewRow();
row["TYPE_OF_ACCOUNT"] = grp[0]["Type_of_Account"].ToString();
foreach (DataRow grprow in grp)
{
row[grprow["AgeGroup"].ToString()] = grprow["interest_rate"].ToString() + "%";
}
dt.Rows.Add(row);
}
}
public DataTable FilteredData(string dataset, List <DataSetFilter> filters)
{
var querybuild = new StringBuilder();
querybuild.Append("select ");
var included = filters.Where(f => f.IsIncluded).ToList();
if (included.Count > 0)
{
var includecolums = new string[included.Count];
for (int i = 0; i < included.Count; i++)
{
includecolums[i] = "\"" + included[i].ColumnName + "\"";
}
querybuild.Append(string.Join(",", includecolums));
}
else
{
querybuild.Append("*");
}
querybuild.Append(" from ").Append("\"" + dataset + "\"");
var withfilters = filters.Where(f => !f.ColumnValue.CleanUp().isNull()).ToList();
if (withfilters.Count > 0)
{
querybuild.Append(" where ");
var filtercolums = new string[withfilters.Count];
for (int i = 0; i < withfilters.Count; i++)
{
if (withfilters[i].Operator.CleanUp().Contains("like"))
{
filtercolums[i] = "\" " + withfilters[i].ColumnName + "\" " + withfilters[i].Operator + " '%" + withfilters[i].ColumnValue + "%' ";
}
else
{
filtercolums[i] = " \"" + withfilters[i].ColumnName + "\" " + withfilters[i].Operator + " '" + withfilters[i].ColumnValue + "' ";
}
}
querybuild.Append(string.Join("and", filtercolums));
}
var sql = querybuild.ToString();
DBInterface db = new PostgreSQLDBInterface(new Settings().warehousedb);
var dt = db.getData(sql);
var columns = included.Count > 0 ? included : filters;
foreach (DataSetFilter column in columns)
{
if (!column.DisplayName.isNull()) //testing if display name is set and effecting appropraitely
{
dt.Columns[column.ColumnName].ColumnName = column.DisplayName.Replace(" ", "_");
}
}
return(dt);
}
public void DeleteDataSet(string datasetname)
{
var db = new PostgreSQLDBInterface(new Settings().warehousedb);
db.Execute(" drop materialized view \"" + datasetname + "\"");
}
public string LaunchStatic(string id)
{
string result;
try
{
ReportDocument report;
DataTable dt; string sql = ""; DBInterface db;
switch (id.ToLower())
{
default:
result = "failed";
break;
case "sect_analysis":
report = new crmbase1();
dt = new DataTable();
sec_analysis(dt, report);
LoadCBNReturnsReport(dt, report, id);
result = "success";
break;
case "due_bal":
report = new due_bal221();
sql = "select distinct '' as \"Code\", \"ACCOUNT_TITLE_1\" as \"NAME_OF_BANK\", CAST(COALESCE(NULLIF(\"WORKING_BALANCE\",''),'0') as decimal) as \"Amount\" from \"ACCOUNT\" where \"CATEGORY\" >= '5000' and \"CATEGORY\" <='5999'";
db = new PostgreSQLDBInterface(new Settings().sourcedb);
dt = db.getData(sql);
LoadCBNReturnsReport(dt, report, id);
result = "success";
break;
case "ml_lend_model":
report = new SLMLL_711();
dt = new DataTable();
ml_lend_model(dt, report);
LoadCBNReturnsReport(dt, report, id);
result = "success";
break;
case "sch_int_rates":
report = new ITR764();
dt = new DataTable();
sch_int_rates(dt, report);
LoadCBNReturnsReport(dt, report, id);
result = "success";
break;
case "pnlacct_montly":
report = new pnl1000();
dt = new DataTable();
pnlacct_montly(dt, report);
LoadCBNReturnsReport(dt, report, id);
result = "success";
break;
case "mem_items":
report = new mem001();
dt = new DataTable();
mem_items(dt, report);
LoadCBNReturnsReport(dt, report, id);
result = "success";
break;
}
return(result);
}
catch (Exception d)
{
return("failed");
}
}
public string CreateDataSet()
{
try
{
var data = Request.Form["datasetdata"];
var moduleid = Request.Form["module"].toInt();
var datasetobject = JsonConvert.DeserializeObject <List <DatasetObject> >(data);
bool isduplicateTable = datasetobject.GroupBy(d => d.TableName).Count() < datasetobject.Count;
if (isduplicateTable)
{
return("1");
}
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
int tcounter = 0;
int ccounter = 0;
string[] tags = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };
foreach (DatasetObject tbitem in datasetobject)
{
if (tcounter == 0) //handling the first table
{
sb2.Append(" from \"" + tbitem.TableName + "\" " + tags[0]);
}
else
{
var pretag = tags[tcounter - 1]; //setting previous table values
var pretable = datasetobject[tcounter - 1];
sb2.Append(" inner join \"" + tbitem.TableName + "\" " + tags[tcounter] + " on (" + pretag + ".\"" + pretable.NxtTable + "\"=" + tags[tcounter] + ".\"" + tbitem.PreTable + "\") ");
}
ccounter = 0;
if (tbitem.Columns.isNull())
{
sb.Append(tags[tcounter] + ".*");
}
else
{
foreach (string column in tbitem.Columns)
{
ccounter++;
sb.Append(tags[tcounter] + ".\"" + column + "\"");
if (ccounter < tbitem.Columns.Length)
{
sb.Append(",");
}
}
}
tcounter++;
if (tcounter < datasetobject.Count)
{
sb.Append(",");
}
}
string cols = sb.ToString();
string tabs = sb2.ToString();
var db = new InlaksBIContext();
var script = "CREATE MATERIALIZED VIEW public." + datasetobject[0].DataSetName + " AS Select ";
var dataset = new DataSetDetail();
dataset.Script = script + cols + tabs;
dataset.DataSetName = datasetobject[0].DataSetName;
DBInterface dbinterface = new PostgreSQLDBInterface(new Settings().warehousedb);
int resp = dbinterface.Execute(dataset.Script);
var sql = "select * from \"" + dataset.DataSetName + "\" LIMIT 1";
var dt = dbinterface.getData(sql);
dataset.Module = db.Modules.Where(m => m.ModuleID == moduleid).FirstOrDefault();
db.DataSets.Add(dataset);
db.SaveChanges();
if (dt.Rows.Count > 0)
{
return("0");
}
else
{
return("DataSet Created sucessfully but has no data");
}
}
catch (Exception e)
{
return(e.Message);
}
}
private void sec_analysis(DataTable dt, ReportDocument report)
{
var myDataColumn = new DataColumn
{
DataType = Type.GetType("System.String"),
ColumnName = "SECTOR"
};
dt.Columns.Add(myDataColumn);
myDataColumn = new DataColumn
{
DataType = typeof(Int32),
ColumnName = "NUMBER_OF_LOANS"
};
dt.Columns.Add(myDataColumn);
myDataColumn = new DataColumn
{
DataType = Type.GetType("System.Decimal"),
ColumnName = "AMOUNT"
};
dt.Columns.Add(myDataColumn);
myDataColumn = new DataColumn
{
DataType = Type.GetType("System.Decimal"),
ColumnName = "PERCENTAGE"
};
dt.Columns.Add(myDataColumn);
var sql = "select a.*,b.* from \"AA_LOANS_IL\" a inner join \"SECTOR\" b on (a.\"Sector\"=b.\"@ID\")";
var db = new PostgreSQLDBInterface(new Settings().sourcedb);
var data = db.getData(sql);
//dt.Columns.Add(myDataColumn);
myDataColumn = new DataColumn
{
DataType = Type.GetType("System.Decimal"),
ColumnName = "CONVERTED_AMOUNT"
};
data.Columns.Add(myDataColumn);
data = data.AsEnumerable().Select(t =>
{
t["CONVERTED_AMOUNT"] = t["Amount"].ToString().isNull()?0: t["Amount"].toDecimal();
return(t);
}).CopyToDataTable();
var total = data.AsEnumerable().Sum(t => t["CONVERTED_AMOUNT"].toDecimal());
var sectgroup = data.AsEnumerable().GroupBy(r => r["SECTOR"]).Select(grp => grp.ToList());
foreach (var grp in sectgroup)
{
var row = dt.NewRow();
row["SECTOR"] = grp[0]["SHORT_NAME"].ToString();
row["NUMBER_OF_LOANS"] = grp.Count;
var amount = grp.Sum(t => t["CONVERTED_AMOUNT"].toDecimal());
row["AMOUNT"] = amount;
row["PERCENTAGE"] = (amount / total) * 100;
dt.Rows.Add(row);
}
}
private void ml_lend_model(DataTable dt, ReportDocument report)
{
var myDataColumn = new DataColumn
{
DataType = Type.GetType("System.String"),
ColumnName = "S/N"
};
dt.Columns.Add(myDataColumn);
myDataColumn = new DataColumn
{
DataType = Type.GetType("System.String"),
ColumnName = "Lending_Model"
};
dt.Columns.Add(myDataColumn);
myDataColumn = new DataColumn
{
DataType = typeof(Int32),
ColumnName = "Number"
};
dt.Columns.Add(myDataColumn);
myDataColumn = new DataColumn
{
DataType = Type.GetType("System.Decimal"),
ColumnName = "Amount_N'000"
};
dt.Columns.Add(myDataColumn);
myDataColumn = new DataColumn
{
DataType = Type.GetType("System.Decimal"),
ColumnName = "%"
};
dt.Columns.Add(myDataColumn);
var sql = "SELECT b.\"DESCRIPTION\" as Lending_Model, CAST(COALESCE(NULLIF(a.\"Amount\",''),'0') as decimal) as \"Amount\" FROM \"AA_LOANS_IL\" a inner join \"CATEGORY\" b on (a.\"Category\"=b.\"@ID\");";
var db = new PostgreSQLDBInterface(new Settings().sourcedb);
var data = db.getData(sql);
var total = data.AsEnumerable().Sum(t => t["Amount"].toDecimal());
var sectgroup = data.AsEnumerable().GroupBy(r => r["Lending_Model"]).Select(grp => grp.ToList());
int i = 1;
foreach (var grp in sectgroup)
{
var row = dt.NewRow();
row["S/N"] = i++;
row["Lending_Model"] = grp[0]["Lending_Model"].ToString();
row["Number"] = grp.Count;
var amount = grp.Sum(t => t["Amount"].toDecimal());
row["Amount_N'000"] = amount;
row["%"] = (amount / total) * 100;
dt.Rows.Add(row);
}
}
