private bool ExecuteGivenQuery(IConfiguration configuration, generateReportTotalListsViewModel viewmodel) { try { string query = composeQuery(viewmodel); List <KeyValuePair <string, string> > parameters = new List <KeyValuePair <string, string> >(); DataSet results = ExecuteQuery(configuration, query, parameters); //dgvShowResults.AutoGenerateColumns = true; //dgvShowResults.DataSource = results; // dataset if (results != null) { foreach (DataTable table in results.Tables) { //dgvShowResults.DataSource = table; } } else { // MessageBox.Show("Er ging iets fout bij het opvragen van de gegevens. Mocht u apart alle gegevens van een tabel willen opvragen, zonder relatie, probeer dit dan stuk voor stuk"); } //dgvShowResults.DataMember = "Results"; return(true); } catch (Exception) { return(false); } }
private string addWhenExceptionRBSelected(generateReportTotalListsViewModel viewmodel) { DateTime dtFromInput = viewmodel.specifiersToAdd.vanafDateTimeInput; DateTime dtTillInput = viewmodel.specifiersToAdd.totDateTimeInput; string dateFilter = ""; //¯\_(ツ)_/¯ Add Absence to Database / Class Diagram ¯\_(ツ)_/¯ if (dtFromInput == null && dtTillInput != null) { /*dateFilter = "(`dienst`.eindMoment > " + dtTillInput.ToString() + ")";*/ } else if (dtTillInput == null && dtFromInput != null) { //dateFilter = "(`dienst`.startMoment < " + dtTillInput.ToString() + ")"; } else if (dtFromInput < dtTillInput) { //dateFilter = "((`dienst`.startMoment > " + dtFromInput.ToString() + ") AND (`dienst`.eindMoment < " + dtTillInput.ToString() + "))"; } else if (dtFromInput > dtTillInput) { //dateFilter = "((`dienst`.startMoment > " + dtFromInput.ToString() + ") OR (`dienst`.eindMoment < " + dtTillInput.ToString() + "))"; } else if (dtFromInput == null && dtTillInput == null) { //dateFilter = ""; } return(dateFilter); }
private string addWhenDateRBSelected(generateReportTotalListsViewModel viewmodel) { DateTime dtFromInput = viewmodel.specifiersToAdd.vanafDateTimeInput; DateTime dtTillInput = viewmodel.specifiersToAdd.totDateTimeInput; string dateFilter = ""; if (dtFromInput == null && dtTillInput != null) { dateFilter = "(`dienst`.eindMoment > " + dtTillInput.ToString() + ")"; } else if (dtTillInput == null && dtFromInput != null) { dateFilter = "(`dienst`.startMoment < " + dtTillInput.ToString() + ")"; } else if (dtFromInput < dtTillInput) { dateFilter = "((`dienst`.startMoment > " + dtFromInput.ToString() + ") AND (`dienst`.eindMoment < " + dtTillInput.ToString() + "))"; } else if (dtFromInput > dtTillInput) { dateFilter = "((`dienst`.startMoment > " + dtFromInput.ToString() + ") OR (`dienst`.eindMoment < " + dtTillInput.ToString() + "))"; } else if (dtFromInput == null && dtTillInput == null) { dateFilter = ""; } return(dateFilter); }
public string leftJoinString(generateReportTotalListsViewModel viewmodel, string table) { string leftJoinString = ""; switch (table) { case "`betaling`": leftJoinString = leftJoinStringBetaling(viewmodel, table); break; case "`certificaat-lid-combo`": leftJoinString = leftJoinStringCertificaatLidCombo(viewmodel, table); break; case "`leden`": leftJoinString = leftJoinStringLeden(viewmodel, table); break; case "`lid-dienst-combo`": leftJoinString = leftJoinStringLidDienstCombo(viewmodel, table); break; case "`nietbeschikbaar`": leftJoinString = leftJoinStringNietBeschikbaar(viewmodel, table); break; case "`schema`": leftJoinString = leftJoinStringSchema(viewmodel, table); break; case "`schema-dienst-combo`": leftJoinString = leftJoinStringSchemaDienstCombo(viewmodel, table); break; case "`vereniging`": leftJoinString = leftJoinStringVereniging(viewmodel, table); break; default: leftJoinString = "(" + table + ")"; break; } return(leftJoinString); }
public IActionResult SelectColumnsByTable(generateReportTotalListsViewModel reportInfo, string foo) { fillForSelectedTable(foo); reportInfo.columns = (List <string>)ViewData["columns"]; reportInfo.selectedTable = foo; return(RedirectToAction("Index", reportInfo)); }
public string composeQuery(generateReportTotalListsViewModel viewmodel) { string composedQuery = "SELECT " + usedColumnsString(viewmodel) + " FROM " + usedTablesString(viewmodel); if (stringHasValue(combinedSpecificationsString(viewmodel))) { composedQuery = composedQuery + " WHERE " + combinedSpecificationsString(viewmodel); } return(composedQuery); }
//ʕ•́ᴥ•̀ʔっ Add specific stuff ʕ•́ᴥ•̀ʔっ private string addWhenNameRBSelected(generateReportTotalListsViewModel viewmodel) { string nameContains = ""; if (viewmodel.specifiersToAdd.bevatTextInput != null) { nameContains = "((`leden`.naam LIKE '%" + viewmodel.specifiersToAdd.bevatTextInput + "%') OR (`leden`.achternaam LIKE '%" + viewmodel.specifiersToAdd.bevatTextInput + "%'))"; } return(nameContains); }
public IActionResult addSpecifier(generateReportTotalListsViewModel reportInfo, string specifierRadios) { string rb = specifierRadios; if (specifierRadios == null) { rb = "none"; } addThisSpecifier(reportInfo, rb); return(RedirectToAction("Index", reportInfo)); }
private string addWhenGroupRBSelected(generateReportTotalListsViewModel viewmodel) { string groupContains = ""; if (viewmodel.specifiersToAdd.bevatTextInput != null) { //¯\_(ツ)_/¯ ADD GROUPS TO DATABASE + CLASS DIAGRAM ¯\_(ツ)_/¯ //groupContains = "((`leden`.naam LIKE '%" + TBContains.Text + "%') OR (`leden`.achternaam LIKE '%" + TBContains.Text + "%'))"; } return(groupContains); }
public IActionResult Index(generateReportTotalListsViewModel viewmodel) { if (viewmodel.specifiersToAdd.Equals(null)) { viewmodel = new generateReportTotalListsViewModel { }; } fillTablesList(); fillMembersList(); return(View(viewmodel)); }
public string combinedSpecificationsString(generateReportTotalListsViewModel viewmodel) { string combinedSpecificationsString = ""; combinedSpecificationsString = combinedSpecificationsString + viewmodel.NameSQLSpecifier; combinedSpecificationsString = addAndBetweenStringsWhenLastStringIsNotEmpty(combinedSpecificationsString, viewmodel.ShiftDateSQLSpecifier); combinedSpecificationsString = addAndBetweenStringsWhenLastStringIsNotEmpty(combinedSpecificationsString, viewmodel.AgeSQLSpecifier); combinedSpecificationsString = addAndBetweenStringsWhenLastStringIsNotEmpty(combinedSpecificationsString, viewmodel.AbsenceSQLSpecifier); combinedSpecificationsString = addAndBetweenStringsWhenLastStringIsNotEmpty(combinedSpecificationsString, viewmodel.GroupSQLSpecifier); return(combinedSpecificationsString); }
public string leftJoinStringSchema(generateReportTotalListsViewModel viewmodel, string table) { string leftJoinString = "(" + table; if (viewmodel.usedTables.Contains("vereniging")) { leftJoinString = leftJoinString + "LEFT JOIN vereniging v_s ON v_s.ID = `schema`.verenigingID"; } leftJoinString = leftJoinString + ")"; return(leftJoinString); }
public string leftJoinStringNietBeschikbaar(generateReportTotalListsViewModel viewmodel, string table) { string leftJoinString = "(" + table; if (viewmodel.usedTables.Contains("leden")) { leftJoinString = leftJoinString + "LEFT JOIN leden l_nb ON l_nb.ID = nietbeschikbaar.lidID"; } leftJoinString = leftJoinString + ")"; return(leftJoinString); }
public string leftJoinStringVereniging(generateReportTotalListsViewModel viewmodel, string table) { string leftJoinString = "(" + table; if (viewmodel.usedTables.Contains("adres")) { leftJoinString = leftJoinString + "LEFT JOIN adres a_v ON a_v.ID = `vereniging`.adresID"; } if (viewmodel.usedTables.Contains("schema")) { leftJoinString = leftJoinString + "LEFT JOIN schema a_v ON a_v.ID = `vereniging`.schemaID"; } leftJoinString = leftJoinString + ")"; return(leftJoinString); }
public string leftJoinStringSchemaDienstCombo(generateReportTotalListsViewModel viewmodel, string table) { string leftJoinString = "(" + table; if (viewmodel.usedTables.Contains("schema")) { leftJoinString = leftJoinString + "LEFT JOIN leden s_sdc ON s_sdc.ID = `schema-dienst-combo`.schemaID"; } if (viewmodel.usedTables.Contains("dienst")) { leftJoinString = leftJoinString + "LEFT JOIN dienst s_sdc ON s_sdc.ID = `schema-dienst-combo`.dienstID"; } leftJoinString = leftJoinString + ")"; return(leftJoinString); }
public string leftJoinStringLidDienstCombo(generateReportTotalListsViewModel viewmodel, string table) { string leftJoinString = "(" + table; if (viewmodel.usedTables.Contains("leden")) { leftJoinString = leftJoinString + "LEFT JOIN leden l_ldc ON l_ldc.ID = `lid-dienst-combo`.lidID"; } if (viewmodel.usedTables.Contains("dienst")) { leftJoinString = leftJoinString + "LEFT JOIN dienst d_ldc ON d_ldc.ID = `lid-dienst-combo`.dienstID"; } leftJoinString = leftJoinString + ")"; return(leftJoinString); }
public string leftJoinStringCertificaatLidCombo(generateReportTotalListsViewModel viewmodel, string table) { string leftJoinString = "(" + table; if (viewmodel.usedTables.Contains("leden")) { leftJoinString = leftJoinString + "LEFT JOIN leden l_clb ON l_clb.ID = `certificaat-lid-combo`.lidID"; } if (viewmodel.usedTables.Contains("leden")) { leftJoinString = leftJoinString + "LEFT JOIN certificaat c_clc ON c_clc.ID = `certificaat-lid-combo`.certificaatID"; } leftJoinString = leftJoinString + ")"; return(leftJoinString); }
public string leftJoinStringLeden(generateReportTotalListsViewModel viewmodel, string table) { string leftJoinString = "(" + table; if (viewmodel.usedTables.Contains("adres")) { leftJoinString = leftJoinString + "LEFT JOIN adres a_l ON a_l.ID = leden.adresID"; } if (viewmodel.usedTables.Contains("vereniging")) { leftJoinString = leftJoinString + "LEFT JOIN vereniging v_l ON v_l.ID = leden.bondsnummer"; } leftJoinString = leftJoinString + ")"; return(leftJoinString); }
public string usedColumnsString(generateReportTotalListsViewModel viewmodel) { string usedColumnsString = ""; int count = viewmodel.usedColumns.Count; int columnsUsed = 1; foreach (string column in viewmodel.usedColumns) { usedColumnsString = usedColumnsString + "" + column + ""; if (columnsUsed < count) { usedColumnsString = usedColumnsString + ", "; } columnsUsed++; } return(usedColumnsString); }
private string addWhenAgeRBSelected(generateReportTotalListsViewModel viewmodel) { string fromInput = viewmodel.specifiersToAdd.vanafTextInput; string tillInput = viewmodel.specifiersToAdd.totTextInput; string ageFilter = ""; bool successFrom = int.TryParse(fromInput, out int fromAge); bool successTill = int.TryParse(tillInput, out int tillAge); DateTime Today = DateTime.Now; DateTime BirthdayFrom; DateTime BirthdayTill; if (successFrom == false && successTill == true) { BirthdayTill = Today.AddYears((tillAge) * -1); ageFilter = "(`leden`.geboortedatum < " + BirthdayTill.ToString() + ")"; } else if (successTill == false && successFrom == true) { BirthdayFrom = Today.AddYears((fromAge + 1) * -1); ageFilter = "(`leden`.geboortedatum > " + BirthdayFrom.ToString() + ")"; } else if (successFrom == false && successTill == false) { ageFilter = ""; } else if (fromAge > tillAge) { BirthdayFrom = Today.AddYears((fromAge + 1) * -1); BirthdayTill = Today.AddYears((tillAge) * -1); ageFilter = "((`leden`.geboortedatum < " + BirthdayTill.ToString() + ") OR (`leden`.geboortedatum > " + BirthdayFrom.ToString() + "))"; } else if (fromAge < tillAge) { BirthdayFrom = Today.AddYears((fromAge + 1) * -1); BirthdayTill = Today.AddYears((tillAge) * -1); ageFilter = "((`leden`.geboortedatum < " + BirthdayTill.ToString() + ") AND (`leden`.geboortedatum > " + BirthdayFrom.ToString() + "))"; } else if (fromAge == tillAge) { BirthdayFrom = Today.AddYears((fromAge + 1) * -1); BirthdayTill = Today.AddYears((tillAge) * -1); ageFilter = "((`leden`.geboortedatum < " + BirthdayTill.ToString() + ") AND (`leden`.geboortedatum > " + BirthdayFrom.ToString() + "))"; } return(ageFilter); }
private void addThisSpecifier(generateReportTotalListsViewModel viewmodel, string rbSelected) { switch (rbSelected) { case "name": viewmodel.NameSQLSpecifier = viewmodel.NameSQLSpecifier + addWhenNameRBSelected(viewmodel); break; case "date": viewmodel.ShiftDateSQLSpecifier = viewmodel.ShiftDateSQLSpecifier + addWhenDateRBSelected(viewmodel); break; case "age": viewmodel.AgeSQLSpecifier = viewmodel.AgeSQLSpecifier + addWhenAgeRBSelected(viewmodel); break; case "exceptionAbsence": viewmodel.AbsenceSQLSpecifier = viewmodel.AbsenceSQLSpecifier + addWhenExceptionRBSelected(viewmodel); break; case "group": viewmodel.GroupSQLSpecifier = viewmodel.GroupSQLSpecifier + addWhenGroupRBSelected(viewmodel); break; case "none": addWhenNoRBSelected(); break; default: addWhenNoRBSelected(); break; } }
public string usedTablesString(generateReportTotalListsViewModel viewmodel) { string usedTablesString = ""; int count = viewmodel.usedTables.Count; int tablesUsed = 1; foreach (string table in viewmodel.usedTables) { string toUse = "`" + table + "`"; usedTablesString = usedTablesString + leftJoinString(viewmodel, toUse); if (tablesUsed < count) { usedTablesString = usedTablesString + ", "; } tablesUsed++; } return(usedTablesString); }
public string leftJoinStringBetaling(generateReportTotalListsViewModel viewmodel, string table) { string leftJoinString = "(" + table; if (viewmodel.usedTables.Contains("leden") || viewmodel.usedTables.Contains("dienst") || viewmodel.usedTables.Contains("lid-dienst-combo")) { leftJoinString = leftJoinString + "LEFT JOIN `lid-dienst-combo` ldc_b ON ldc_b.ID = betaling.Lid_Dienst_ID"; if (viewmodel.usedTables.Contains("leden")) { leftJoinString = leftJoinString + "LEFT JOIN leden l_b ON l_b.ID = ldc_b.lidID"; } if (viewmodel.usedTables.Contains("dienst")) { leftJoinString = leftJoinString + "LEFT JOIN dienst d_b ON d_b.ID = ldc_b.dienstID"; } } leftJoinString = leftJoinString + ")"; return(leftJoinString); }
public IActionResult addSelectedColumns(generateReportTotalListsViewModel viewmodel) { viewmodel.usedColumns = new List <string>(); List <string> allChecked = new List <string> { "ID" }; foreach (string column in allChecked) { string usedTable = viewmodel.selectedTable; string toUse = "`" + usedTable + "`." + column; if (!viewmodel.usedColumns.Contains(toUse)) { viewmodel.usedColumns.Add(toUse); } } if (viewmodel.selectedTable != null) { viewmodel.usedTables.Add(viewmodel.selectedTable); } return(RedirectToAction("Index", viewmodel)); }
public IActionResult ExecuteConstructedQuery(IConfiguration configuration, generateReportTotalListsViewModel viewmodel) { //ExecuteGivenQuery(configuration,viewmodel); return(View(viewmodel)); }