buechermarkt/BuechermarktClient/Bills.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MongoDB.Driver;
using MSOfficeUtils.Word;

namespace BuechermarktClient
{
    class Bills
    {
        public static void GenerateBills()
        {
            var ds = new DataSet();
            var tableRechnung = new DataTable("Rechnung");
            tableRechnung.Columns.Add("ID", typeof(bool));
            ds.Tables.Add(tableRechnung);

            var tablePerson = new DataTable("Person");
            tablePerson.Columns.Add("ID", typeof(string));
            tablePerson.Columns.Add("Forname", typeof(string));
            tablePerson.Columns.Add("Lastname", typeof(string));
            tablePerson.Columns.Add("EMail", typeof(string));
            tablePerson.Columns.Add("PhoneNumber", typeof(string));
            tablePerson.Columns.Add("Form", typeof(string));
            tablePerson.Columns.Add("Total", typeof(decimal));
            tablePerson.Columns.Add("Rechnung", typeof(bool));
            ds.Tables.Add(tablePerson);

            var tableBooks = new DataTable("Books");
            tableBooks.Columns.Add("Student", typeof(string));
            tableBooks.Columns.Add("Title", typeof(string));
            tableBooks.Columns.Add("LabelId", typeof(string));
            tableBooks.Columns.Add("Price", typeof(decimal));
            ds.Tables.Add(tableBooks);

            ds.Relations.Add(new DataRelation("Person", tableRechnung.Columns["ID"], tablePerson.Columns["Rechnung"]));
            ds.Relations.Add(new DataRelation("Books", tablePerson.Columns["ID"], tableBooks.Columns["Student"]));

            var types = MainWindow.BookTypeCollection.AsQueryable();
            var typesDict = new Dictionary<string, string>();
            foreach(var type in types)
            {
                typesDict.Add(type.ID.ToString(), type.Name);
            }

            var rowRechung = tableRechnung.NewRow();
            rowRechung["ID"] = true;
            tableRechnung.Rows.Add(rowRechung);

            foreach (var student in MainWindow.StudentCollection.AsQueryable().OrderBy(r => r.Form).ThenBy(r => r.Lastname))
            {
                var rowPerson = tablePerson.NewRow();
                rowPerson["ID"] = student.ID.ToString();
                rowPerson["Forname"] = student.Forname;
                rowPerson["Lastname"] = student.Lastname;
                rowPerson["EMail"] = student.EMail;
                rowPerson["PhoneNumber"] = student.PhoneNumber;
                rowPerson["Form"] = student.Form;
                rowPerson["Rechnung"] = true;
                tablePerson.Rows.Add(rowPerson);

                var total = 0m;
                var books = 0;
                foreach (var book in MainWindow.BookCollection.AsQueryable().Where(r=>r.Student == student.ID).OrderBy(r=>r.Price))
                {
                    var rowBook = tableBooks.NewRow();
                    rowBook["Student"] = rowPerson["ID"];
                    try
                    {
                        rowBook["Title"] = typesDict[book.BookType.ToString()];
                    } catch
                    {
                        rowBook["Title"] = "Unbekannt";
                    }
                    rowBook["LabelId"] = book.LabelId;
                    rowBook["Price"] = book.State == Models.BookState.BackToStudent || book.State == Models.BookState.InStock ? (object)DBNull.Value : book.Price;
                    total += !rowBook.IsNull("Price") ? rowBook.Field<decimal>("Price") : 0m;
                    tableBooks.Rows.Add(rowBook);
                    books++;
                }
                rowPerson["Total"] = total;

                if (books <= 0) rowPerson.Delete();
            }
            WordReportGeneratorOpenXml.BuildDoc(@"Vorlage.docx", "Rechnungen.docx", ds, false);
        }
    }
}