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