private void CreateHeader()
{
Databasehandler dbh = new Databasehandler();
// Vul hier de laaste sql statement in
SqlCommand sql = new SqlCommand("");
dbh.OpenConnectionToDB();
SqlDataAdapter adapt = new SqlDataAdapter(sql);
adapt.Fill(dbh.table);
dbh.CloseConnectionToDB();
section.AddParagraph(dbh.table.Rows[0].ItemArray[0].ToString());
section.AddParagraph(dbh.table.Rows[1].ItemArray[0].ToString());
section.AddParagraph("");
section.AddParagraph("-----------------------------------------------------------------------------------------------------");
section.AddParagraph("");
}
private void FillPDF()
{
Databasehandler dbh = new Databasehandler();
SqlCommand GetData = new SqlCommand
(@" SELECT [RegDienst].[RegDienst_Naam] as Dienst,
CONCAT ([Leden].[Roepnaam], ' ' , [Leden].[Achternaam] ) AS Naam,
CONCAT ([RegDienst].[RegDienst_BeginDatum], ' ' , [RegDienst].[RegDienst_BeginUur], ':' , [RegDienst].[RegDienst_BeginMinuut]) As Begintijd,
CONCAT ([RegDienst].[RegDienst_EindDatum], ' ' , [RegDienst].[RegDienst_EindUur], ':' , [RegDienst].[RegDienst_EindMinuut]) as Eindtijd,
[Leden].[BondsNr],[Leden].[Telefoon] as Telefoonnummer
FROM [GenBB_Regels]
INNER JOIN [RegDienst]
ON [GenBB_Regels].[RegDienst_ID] = [RegDienst].[RegDienst_ID]
INNER JOIN [Leden]
ON [GenBB_Regels].[Leden_ID] = [Leden].[Leden_ID]", dbh.GetCon()
);
SqlCommand GetJob = new SqlCommand
(
@" SELECT DISTINCT [RegDienst].RegDienst_Naam FROM[GenBB_Regels]
INNER JOIN[RegDienst]
ON[GenBB_Regels].[RegDienst_ID] = [RegDienst].[RegDienst_ID] ", dbh.GetCon()
);
DataTable Jobs = new DataTable();
// Connect to the database and fill the datatables
dbh.OpenConnectionToDB();
SqlDataAdapter adapt = new SqlDataAdapter(GetData);
adapt.Fill(dbh.table);
adapt = new SqlDataAdapter(GetJob);
adapt.Fill(Jobs);
dbh.CloseConnectionToDB();
section = document.AddSection();
CreateHeader();
int count = 0;
foreach (DataRow DataRow in Jobs.Rows)
{
// Get a job from the datatable
string Job = DataRow.Table.Rows[count].ItemArray[0].ToString();
// Create the table
Table table = CreateTable(Job + "-dienst");
// Loop trough every datarow
foreach (DataRow dtRow in dbh.table.Rows)
{
// Add the row to the table if the job is the same
if (dtRow.ItemArray[0].ToString() == Job)
{
Row row = table.AddRow();
for (int i = 0; i < table.Columns.Count; i++)
{
int x = i + 1;
row.Cells[i].AddParagraph(dtRow.ItemArray[x].ToString());
}
}
}
count++;
// Create spaces in between tables
section.AddParagraph();
section.AddParagraph();
}
}