Ejemplo n.º 1
0
        private async void chkEnableDatagrid_Click(object sender, RoutedEventArgs e)
        {
            if (chkEnableDatagrid.IsChecked == true)
            {
                await Task.Run(() => this.Dispatcher.Invoke(() => lblOutput.Content = "Data word geladen..."));

                await Task.Run(() => this.Dispatcher.Invoke(() => ConnectDatabase.FillDataGrid(dtgStudent)));

                lblOutput.Content = "Data succesvol geladen.";
            }
            else
            {
                MessageBoxResult result = MessageBox.Show("Waarschuwing, uw gewijzigde gegevens zullen niet opgeslaan worden. Bent u zeker?", "Opgelet", MessageBoxButton.YesNo, MessageBoxImage.Warning);
                if (result == MessageBoxResult.Yes)
                {
                    dtgStudent.ItemsSource = null;
                    dtgStudent.Items.Clear();
                    lblOutput.Content = "";
                }
                else
                {
                    chkEnableDatagrid.IsChecked = true;
                }
            }
        }
        public static void AddTableInBookmark(string sql, Table table, BookmarksNavigator navigator, string bookmark, TextBodyPart txtbodyPart)
        {
            //Voer sql query uit en steek deze in datatable
            DataTable result = ConnectDatabase.getTable(sql);

            //Vul de table met data van de datatable
            List <string> years = new List <string>();

            for (int i = 0; i < result.Rows.Count; i++)
            {
                if (!years.Contains(result.Rows[i]["jaar"].ToString()))
                {
                    years.Add(result.Rows[i]["jaar"].ToString());
                }
            }
            years.Sort();

            List <string> groups = new List <string>();

            for (int i = 0; i < result.Rows.Count; i++)
            {
                if (!groups.Contains(result.Rows[i][0].ToString()))
                {
                    groups.Add(result.Rows[i][0].ToString());
                }
            }
            //groups.Sort(); sorteer modules
            table.ResetCells(groups.Count + 1, years.Count + 1);
            table.Rows[0].Cells[0].AddParagraph().AppendText(bookmark);
            for (int i = 0; i < years.Count; i++)
            {
                table.Rows[0].Cells[i + 1].AddParagraph().AppendText(years[i]);
            }
            for (int i = 1; i <= groups.Count; i++)
            {
                table.Rows[i].Cells[0].AddParagraph().AppendText(groups[i - 1]);
            }

            for (int i = 1; i <= groups.Count; i++)
            {
                List <TableInput> tableInputByGroup = GetTableInputsByGroup(result, groups[i - 1], years);
                for (int j = 1; j <= tableInputByGroup.Count; j++)
                {
                    table.Rows[i].Cells[j].AddParagraph().AppendText(tableInputByGroup[j - 1].Value.ToString());
                }
            }
            table.AutoFit(AutoFitBehaviorType.AutoFitToContents);

            navigator.MoveToBookmark(bookmark);

            txtbodyPart.BodyItems.Add(table);
            navigator.ReplaceBookmarkContent(txtbodyPart);
        }
Ejemplo n.º 3
0
        private void btnUploadData_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();

            dlg.Filter = "CSV files (*.csv)|*.csv";

            if (dlg.ShowDialog() == true)
            {
                string fileName;
                fileName = dlg.FileName;
                ConnectDatabase.UploadCSV(fileName, dtgStudent);
            }
        }
Ejemplo n.º 4
0
        private void btnSubmit_Click(object sender, RoutedEventArgs e)
        {
            if (dtgStudent.Items.Count == 0)
            {
                MessageBoxResult result = MessageBox.Show("U gaat nu alle items van de databank verwijderen doordat er geen items in het datagrid staan. Bent u zeker om dit te doen?", "Opgelet", MessageBoxButton.YesNo, MessageBoxImage.Warning);

                if (result == MessageBoxResult.No)
                {
                    return;
                }
            }
            chkEditCells.IsChecked = false;
            btnSubmit.IsEnabled    = false;
            dtgStudent.IsReadOnly  = true;
            ConnectDatabase.UpdateDatabase(dtgStudent);
        }
        public static void AddTableInBookmarkWithoutGroups(string sql, Table table, BookmarksNavigator navigator, string bookmark, TextBodyPart txtbodyPart)
        {
            //Voer sql query uit en steek deze in datatable
            DataTable result = ConnectDatabase.getTable(sql);

            //Vul de table met data van de datatable
            List <string> years = new List <string>();

            for (int i = 0; i < result.Rows.Count; i++)
            {
                if (!years.Contains(result.Rows[i]["jaar"].ToString()))
                {
                    years.Add(result.Rows[i]["jaar"].ToString());
                }
            }
            years.Sort();



            table.ResetCells(2, years.Count + 1);
            table.Rows[0].Cells[0].AddParagraph().AppendText(bookmark);
            for (int i = 0; i < years.Count; i++)
            {
                table.Rows[0].Cells[i + 1].AddParagraph().AppendText(years[i]);
            }

            for (int i = 1; i <= years.Count; i++)
            {
                table.Rows[1].Cells[i].AddParagraph().AppendText(result.Rows[i - 1]["value"].ToString());
            }

            table.AutoFit(AutoFitBehaviorType.AutoFitToContents);

            navigator.MoveToBookmark(bookmark);

            txtbodyPart.BodyItems.Add(table);
            navigator.ReplaceBookmarkContent(txtbodyPart);
        }
Ejemplo n.º 6
0
 public MainWindow()
 {
     InitializeComponent();
     ConnectDatabase.CreateDatabaseIfNotExists();
 }
        public static void Create()
        {
            try
            {
                //Laad de template in
                Document testdoc = new Document();
                testdoc.LoadFromFile(AppDomain.CurrentDomain.BaseDirectory + "\\Template.docx");
                //Maak table
                Section section = testdoc.Sections[0];
                Table   table   = section.Tables[0] as Table;
                //Table table = new Table(testdoc);
                table.ApplyStyle(DefaultTableStyle.MediumShading1Accent1);

                BookmarksNavigator navigator = new BookmarksNavigator(testdoc);

                TextBodyPart part = new TextBodyPart(testdoc);

                /*Aantal studenten /module/sesmter */

                //Deze tabel klopt
                //Aantal studenten / module /semster1
                AddTableInBookmark("select Module, count(Geslacht) as value, CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where MONTH([Module begindatum]) < 7  group by module ,CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) HAVING YEAR([Module begindatum]) >= (Year(GETDATE())-5) order by  YEAR([Module begindatum]), semester", table, navigator, "TotaalAantalStudentenSem1", part);
                //Aantal studenten / module / semester2
                AddTableInBookmark("select Module, count(Geslacht) as value, CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where MONTH([Module begindatum]) > 7  group by module , CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) HAVING YEAR([Module begindatum]) >= (Year(GETDATE())-5) order by  YEAR([Module begindatum]), semester", table, navigator, "TotaalAantalStudentenSem2", part);


                /*Geslaagde mensen / module per semester */
                //tabellen kloppen
                //Semester 1
                AddTableInBookmark("select Module, COUNT([Module attest]) as value , CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where[Module attest] = 'Geslaagd' and MONTH([Module begindatum]) < 7 group by module , CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) order by  YEAR([Module begindatum]), semester", table, navigator, "MensenGeslaagdSem1", part);

                //Geslaagde mensen / module per semester
                //semester 2
                AddTableInBookmark("select Module, COUNT([Module attest]) as value , CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where[Module attest] = 'Geslaagd' and MONTH([Module begindatum]) > 7 group by module , CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) order by  YEAR([Module begindatum]), semester", table, navigator, "MensenGeslaagdSem2", part);



                //Aantal afgestudeerden /semester

                //semester 1
                AddTableInBookmarkWithoutGroups("select COUNT(Stamnummer) as value, CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where Module = 'Module Toegepaste verpleegkunde (40 weken)' and[Module attest] = 'Geslaagd' and MONTH([Module begindatum]) < 7 group by  CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum])", table, navigator, "AantalAfgestudeerdeStudentenSem1", part);

                //semester 2
                AddTableInBookmarkWithoutGroups("select COUNT(Stamnummer) as value, CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where Module = 'Module Toegepaste verpleegkunde (40 weken)' and[Module attest] = 'Geslaagd' and MONTH([Module begindatum]) > 7 group by  CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum])", table, navigator, "AantalAfgestudeerdeStudentenSem2", part);


                //RedenStoppen
                //Klopt
                //semester 1
                AddTableInBookmark("select[Reden stoppen] ,count([Reden stoppen]) as value,CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester , YEAR([Module begindatum]) as jaar from tblStudentGegevens where [Reden stoppen] != '' and MONTH([Module begindatum]) < 7 group by [Reden stoppen],CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) order by  YEAR([Module begindatum]) ASC, semester ASC ", table, navigator, "RedenStoppenSem1", part);

                //semester 2
                AddTableInBookmark("select[Reden stoppen] ,count([Reden stoppen]) as value,CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester , YEAR([Module begindatum]) as jaar from tblStudentGegevens where [Reden stoppen] != '' and MONTH([Module begindatum]) > 7 group by [Reden stoppen],CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) order by  YEAR([Module begindatum]) ASC, semester ASC ", table, navigator, "RedenStoppenSem2", part);


                //school leren kennen
                //Klopt
                //semester 1
                AddTableInBookmark("select coalesce(nullif([School leren kennen],''), 'onbekend') as [school leren kennen], count([School leren kennen]) as value, CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where Module = 'Module Initiatie verpleegkunde (20 weken)' and MONTH([Module begindatum]) < 7 group by [School leren kennen], CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) HAVING YEAR([Module begindatum]) >= (Year(GETDATE()) - 5) order by  YEAR([Module begindatum]) ASC, semester ASC", table, navigator, "SchoolLerenKennenSem1", part);

                //semester 2
                AddTableInBookmark("select coalesce(nullif([School leren kennen],''), 'onbekend') as [school leren kennen], count([School leren kennen]) as value, CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where Module = 'Module Initiatie verpleegkunde (20 weken)' and MONTH([Module begindatum]) > 7 group by [School leren kennen], CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) HAVING YEAR([Module begindatum]) >= (Year(GETDATE()) - 5) order by  YEAR([Module begindatum]) ASC, semester ASC", table, navigator, "SchoolLerenKennenSem2", part);


                ////Aantal modules dat hernomen worden per module per semester*
                ////semester 1

                //// AddTableInBookmark("select Module, count(Module) as value ,CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where [Verleende studiebewijzen 1ste zit] = '' and[Reden stoppen] = '' and[Klas vorig schooljaar] = '' and MONTH([Module begindatum]) < 7 group by Module,  CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) HAVING YEAR([Module begindatum]) >= (Year(GETDATE()) - 5) order by  YEAR([Module begindatum]) ASC, semester ASC", table, navigator, "ModuleHernemenSem1", part);

                ////semester 2
                //if (ConnectDatabase.ExecuteQuery("create or alter view StudentenMetEenVorigeKlas as select Module as module,[Module attest] as moduleAttest, REPLACE([Klas vorig schooljaar], ' ', '') as VorigeKlas  ,Klas as Klas,[Module begindatum] as begindatum from tblStudentGegevens where[Klas vorig schooljaar]  != ''"))
                //{
                //    if (ConnectDatabase.ExecuteQuery())
                //        AddTableInBookmark("select Module, count(Module) as value ,CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where [Verleende studiebewijzen 1ste zit] = '' and[Reden stoppen] = '' and[Klas vorig schooljaar] = '' and MONTH([Module begindatum]) > 7 group by Module,  CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) HAVING YEAR([Module begindatum]) >= (Year(GETDATE()) - 5) order by  YEAR([Module begindatum]) ASC, semester ASC", table, navigator, "ModuleHernemenSem2", part);
                //}



                /******************/

                //Gemiddelde duur aantal semesters van de opleiding / student PER MOdule per afstudeerjaar (andere functie nodig)
                if (ConnectDatabase.ExecuteQuery("create or alter view GeslaagdeStudenten as select Stamnummer, Module from tblStudentGegevens where Module = 'Module Toegepaste verpleegkunde (40 weken)' and[Module attest] = 'Geslaagd'"))
                {
                    if (ConnectDatabase.ExecuteQuery("create or alter view AantalModulesPerAfgestudeerdeStudent as select count(tblStudentGegevens.Module) as aantalModules, GeslaagdeStudenten.Stamnummer from tblStudentGegevens INNER JOIN GeslaagdeStudenten ON tblStudentGegevens.Stamnummer = GeslaagdeStudenten.Stamnummer group by   GeslaagdeStudenten.Stamnummer"))
                    {
                        AddTableInBookmarkWithoutGroups("select ROUND(AVG(CAST(AantalModulesPerAfgestudeerdeStudent.aantalModules as float)),2)  as value, YEAR([Module begindatum]) as jaar from tblStudentGegevens INNER JOIN AantalModulesPerAfgestudeerdeStudent ON tblStudentGegevens.Stamnummer = AantalModulesPerAfgestudeerdeStudent.Stamnummer Group by YEAR([Module begindatum]) HAVING YEAR([Module begindatum]) >= (Year(GETDATE()) - 5)", table, navigator, "GemiddeldeDuur", part);
                    }
                }


                //Percentage meisjes jongens
                if (ConnectDatabase.ExecuteQuery("create or alter view TotaalAantalStudentenPerSemester as select Count(Stamnummer) as 'value' , CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens group  by CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) HAVING YEAR([Module begindatum]) >= (Year(GETDATE()) - 5) "))
                {
                    if (ConnectDatabase.ExecuteQuery("create or alter view TotaalAantalVrouwenPerSemester as select Count(Stamnummer) as 'value', CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where Geslacht = 'V' group  by CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) HAVING YEAR([Module begindatum]) >= (Year(GETDATE()) - 5) "))
                    {
                        if (ConnectDatabase.ExecuteQuery("create or alter view TotaalAantalMannenPerSemester as select Count(Stamnummer) as 'value' , Geslacht, CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END AS semester, YEAR([Module begindatum]) as jaar from tblStudentGegevens where Geslacht = 'M' group  by Geslacht, CASE WHEN MONTH([Module begindatum]) < 7 THEN 1 ELSE 2 END , YEAR([Module begindatum]) HAVING YEAR([Module begindatum]) >= (Year(GETDATE()) - 5)"))
                        {
                            //Mannen
                            AddTableInBookmarkWithoutGroups("select CAST(ROUND(CAST(TotaalAantalMannenPerSemester.value as float) / CAST(TotaalAantalStudentenPerSemester.value as float) * 100,2) AS nvarchar(50)) +'%' as value , TotaalAantalStudentenPerSemester.semester, TotaalAantalStudentenPerSemester.jaar from TotaalAantalStudentenPerSemester join TotaalAantalMannenPerSemester on(TotaalAantalMannenPerSemester.jaar = TotaalAantalStudentenPerSemester.jaar and TotaalAantalMannenPerSemester.semester = TotaalAantalStudentenPerSemester.semester) where TotaalAantalStudentenPerSemester.jaar >= (Year(GETDATE()) - 5) and TotaalAantalMannenPerSemester.semester = 1 ", table, navigator, "VerhoudingManSem1", part);
                            AddTableInBookmarkWithoutGroups("select CAST(ROUND(CAST(TotaalAantalMannenPerSemester.value as float) / CAST(TotaalAantalStudentenPerSemester.value as float) * 100,2) AS nvarchar(50)) +'%' as value , TotaalAantalStudentenPerSemester.semester, TotaalAantalStudentenPerSemester.jaar from TotaalAantalStudentenPerSemester join TotaalAantalMannenPerSemester on(TotaalAantalMannenPerSemester.jaar = TotaalAantalStudentenPerSemester.jaar and TotaalAantalMannenPerSemester.semester = TotaalAantalStudentenPerSemester.semester) where TotaalAantalStudentenPerSemester.jaar >= (Year(GETDATE()) - 5) and TotaalAantalMannenPerSemester.semester = 2 ", table, navigator, "VerhoudingManSem2", part);

                            //Vrouwen
                            AddTableInBookmarkWithoutGroups(" select CAST(ROUND(CAST(TotaalAantalVrouwenPerSemester.value as float) / CAST(TotaalAantalStudentenPerSemester.value as float) * 100,2) AS nvarchar(50)) +'%' as value , TotaalAantalStudentenPerSemester.semester, TotaalAantalStudentenPerSemester.jaar from TotaalAantalStudentenPerSemester join TotaalAantalVrouwenPerSemester on(TotaalAantalVrouwenPerSemester.jaar = TotaalAantalStudentenPerSemester.jaar and TotaalAantalVrouwenPerSemester.semester = TotaalAantalStudentenPerSemester.semester) where TotaalAantalStudentenPerSemester.jaar >= (Year(GETDATE()) - 5) and TotaalAantalVrouwenPerSemester.semester = 1", table, navigator, "VerhoudingVrouwSem1", part);
                            AddTableInBookmarkWithoutGroups(" select CAST(ROUND(CAST(TotaalAantalVrouwenPerSemester.value as float) / CAST(TotaalAantalStudentenPerSemester.value as float) * 100,2) AS nvarchar(50)) +'%' as value , TotaalAantalStudentenPerSemester.semester, TotaalAantalStudentenPerSemester.jaar from TotaalAantalStudentenPerSemester join TotaalAantalVrouwenPerSemester on(TotaalAantalVrouwenPerSemester.jaar = TotaalAantalStudentenPerSemester.jaar and TotaalAantalVrouwenPerSemester.semester = TotaalAantalStudentenPerSemester.semester) where TotaalAantalStudentenPerSemester.jaar >= (Year(GETDATE()) - 5) and TotaalAantalVrouwenPerSemester.semester = 2", table, navigator, "VerhoudingVrouwSem2", part);
                        }
                    }
                }



                testdoc.SaveToFile(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\output.docx", FileFormat.Docx2013);
                System.Diagnostics.Process.Start(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\output.docx");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Er is een onverwachte fout opgetreden bij het exporten van de data: " + ex, "Fout", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }