using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using System.Threading;
namespace Bookings_And_Loans.Database
{
/// <summary>
/// This file is used to handle all data for my bookings and loans tables
/// It will load and save tables, and control validation rules
/// </summary>
class csDatabase
{
//Create the dataset and datatables
public static DataSet dbBookings = new DataSet("tables");
public static DataTable dtBookings = new DataTable("bookings");
public static DataTable dtLoans = new DataTable("loans");
public static DataTable dtArchivedBookings = new DataTable("archived bookings");
public static DataTable dtArchivedLoans = new DataTable("archived loans");
//Set the field seperator for the tables when loading from the file
static string fieldSeperator = "##";
public static void SetTableStructures()
{
//**********************************************
//*************** Bookings Table ***************
//**********************************************
//Create the structure for bookings table
//Add all required columns to the table
dtBookings.Columns.Add("Booking ID", typeof(int));
dtBookings.Columns.Add("Student Name", typeof(string));
dtBookings.Columns.Add("Facility", typeof(string));
dtBookings.Columns.Add("Date", typeof(DateTime));
dtBookings.Columns.Add("Time", typeof(string));
dtBookings.Columns.Add("Duration", typeof(string));
//Add table to database and load the file
dbBookings.Tables.Add(dtBookings);
LoadTable("bookings");
//**********************************************
//*************** Loans Table ******************
//**********************************************
//Create the structure for loans table
//Add all required columns to the table
dtLoans.Columns.Add("Loan ID", typeof(int));
dtLoans.Columns.Add("Student Name", typeof(string));
dtLoans.Columns.Add("Equipment", typeof(string));
dtLoans.Columns.Add("Date Taken", typeof(DateTime));
dtLoans.Columns.Add("Date Expected Back", typeof(DateTime));
dtLoans.Columns.Add("Overdue", typeof(bool));
//Add table to database and load the file
dbBookings.Tables.Add(dtLoans);
LoadTable("loans");
//Set the validation rules for the main/current tables
LoadValidationRules();
//**********************************************
//*********** Bookings Archive Table ***********
//**********************************************
//Create the structure for bookings archive table
dtArchivedBookings.Columns.Add("Booking ID", typeof(int));
dtArchivedBookings.Columns.Add("Student Name", typeof(string));
dtArchivedBookings.Columns.Add("Facility", typeof(string));
dtArchivedBookings.Columns.Add("Date", typeof(DateTime));
dtArchivedBookings.Columns.Add("Time", typeof(string));
dtArchivedBookings.Columns.Add("Duration", typeof(string));
dtArchivedBookings.Columns.Add("Date Archived", typeof(DateTime));
//Add table to database and load the file
dbBookings.Tables.Add(dtArchivedBookings);
LoadTable("archived bookings");
//**********************************************
//*********** Loans Archive Table ***********
//**********************************************
//Create the structure for loans archive table
dtArchivedLoans.Columns.Add("Loan ID", typeof(int));
dtArchivedLoans.Columns.Add("Student Name", typeof(string));
dtArchivedLoans.Columns.Add("Equipment", typeof(string));
dtArchivedLoans.Columns.Add("Date Taken", typeof(DateTime));
dtArchivedLoans.Columns.Add("Date Expected Back", typeof(DateTime));
dtArchivedLoans.Columns.Add("Date Archived", typeof(DateTime));
//Add table to database and load the file
dbBookings.Tables.Add(dtArchivedLoans);
LoadTable("archived loans");
}
public static void LoadTable(string tableToLoad)
{
//Set a string to the filename to load records from
string fileToLoadFrom = tableToLoad + ".txt";
if (File.Exists(fileToLoadFrom))
{
//If the file exists, start reading it and place the data into the relevant
//fields in the table
StreamReader swFileReader = new StreamReader(fileToLoadFrom);
string line = "";
while ((line = swFileReader.ReadLine()) != null)
{
DataRow rowToWriteTo = dbBookings.Tables[tableToLoad].NewRow();
string[] fields = Regex.Split(line, fieldSeperator);
for (int repeat = 0; repeat < dbBookings.Tables[tableToLoad].Columns.Count; repeat++)
{
try
{
string valueToInsert = fields[repeat];
string columnDataType = dbBookings.Tables[tableToLoad].Columns[repeat].DataType.ToString();
switch (columnDataType)
{
case "System.DatTime": rowToWriteTo[repeat] = DateTime.Parse(fields[repeat]); break;
default: rowToWriteTo[repeat] = fields[repeat]; break;
}
}
catch (Exception error) { }
}
//Add the record to the table
dbBookings.Tables[tableToLoad].Rows.Add(rowToWriteTo);
}
swFileReader.Close();
}
}
public static void SaveTable(string tableToSave)
{
//Call recursive procedure WriteFile to create the table file and save all records
WriteFile(tableToSave, 0);
}
public static void SaveAllTables()
{
//Create a new thread (Multi threading) to avoid slowing down the main application
new Thread(delegate()
{
//Loop through the database, saving each table
for (int repeat = 0; repeat < dbBookings.Tables.Count; repeat++)
{
string tableToSave = dbBookings.Tables[repeat].ToString();
//Call recursive procedure WriteFile to create the table file and save all records
WriteFile(tableToSave, 0);
}
}).Start();
}
static void WriteFile(string tableToWrite, int rowIndexToAppend)
{
//Reccursive procedure to save all records of a table to a text file
//BASE CONDITION, if the row doesn't exist, end the call
if (rowIndexToAppend >= dbBookings.Tables[tableToWrite].Rows.Count) { return; }
string lineToAppend = "";
DataRow rowToAppend = dbBookings.Tables[tableToWrite].Rows[rowIndexToAppend];
//If this is a new file to be saved, delete the old one to avoid replication of data
if (rowIndexToAppend == 0) { File.Delete(tableToWrite + ".txt"); }
//Set writer to append text to file
StreamWriter swFileWriter = File.AppendText(tableToWrite + ".txt");
//Loop through each column, adding the data to the string to be write
for (int repeat = 0; repeat < dbBookings.Tables[tableToWrite].Columns.Count; repeat++)
{
string column = dbBookings.Tables[tableToWrite].Columns[repeat].ToString();
if (repeat == 0) { lineToAppend = rowToAppend[column].ToString(); }
else { lineToAppend += fieldSeperator + rowToAppend[column].ToString(); }
}
//Write the string to the file
swFileWriter.WriteLine(lineToAppend);
swFileWriter.Flush();
swFileWriter.Close();
//Call procedure in order to process the next row
WriteFile(tableToWrite, (rowIndexToAppend + 1));
}
public static void LoadValidationRules()
{
//Set validation rules for each column in the BOOKINGS TABLE
dbBookings.Tables["bookings"].Columns["Booking ID"].Unique = true;
dbBookings.Tables["bookings"].Columns["Student Name"].MaxLength = 30;
//Set regular expressions in the captions, in order to validate on entry
dbBookings.Tables["bookings"].Columns["Booking ID"].Caption = "[0-9]*";
dbBookings.Tables["bookings"].Columns["Student Name"].Caption = ".*\\s.*";
dbBookings.Tables["bookings"].Columns["Facility"].Caption = ".*";
dbBookings.Tables["bookings"].Columns["Time"].Caption = "[0-2]?[0-9]:[0-5][0-9]";
dbBookings.Tables["bookings"].Columns["Duration"].Caption = "[0-1]?[0-9]?[0-9]";
//Set validation rules for each column in the LOANS TABLE
dbBookings.Tables["loans"].Columns["Loan ID"].Unique = true;
dbBookings.Tables["loans"].Columns["Student Name"].MaxLength = 30;
dbBookings.Tables["loans"].Columns["Loan ID"].Caption = "[0-9]*";
dbBookings.Tables["loans"].Columns["Student Name"].Caption = ".*\\s.*";
dbBookings.Tables["loans"].Columns["Equipment"].Caption = ".*";
}
public static void ClearAllTables()
{
for (int repeat = 0; repeat < dbBookings.Tables.Count; repeat++)
{
string tableToClear = dbBookings.Tables[repeat].TableName;
//Clear the table in runtime memory
Database.csDatabase.dbBookings.Tables[tableToClear].Clear();
//Delete the file as well
File.Delete(tableToClear + ".txt");
}
}
public static void ProcessOutdatedRecords()
{
//Create a new thread (Multi threading) to avoid slowing down the main application
new Thread(delegate()
{
//Go through all records in the Bookings table in a linear fashion in order to check if the record is outdated
for (int repeat = 0; repeat < dbBookings.Tables["bookings"].Rows.Count; repeat++)
{
DataRow rowToCheck = dbBookings.Tables["bookings"].Rows[repeat];
string dateOfBooking = rowToCheck["Date"].ToString().Split(' ')[0];
string timeOfBookingExpiration = DateTime.Parse(rowToCheck["Time"].ToString()).AddMinutes(Convert.ToInt32(rowToCheck["Duration"])).TimeOfDay.ToString();
string dateAndTimeOfBookingExpiration = dateOfBooking + " " + timeOfBookingExpiration;
DateTime dateTimeOfBookingExpiration = DateTime.Parse(dateAndTimeOfBookingExpiration);
DateTime today = DateTime.Now;
int compareBookingToNow = DateTime.Compare(dateTimeOfBookingExpiration, today);
//If the date and time held by the booking has passed, move the record to the archive table
if (compareBookingToNow.Equals(-1))
{ MoveRecordToArchive(rowToCheck, "bookings"); }
}
//Go through all records in the Loans table in a linear fashion in order to check if the records date indicates the equipment is overdue to be returned
for (int repeat = 0; repeat < dbBookings.Tables["loans"].Rows.Count; repeat++)
{
DataRow rowToCheck = dbBookings.Tables["loans"].Rows[repeat];
string expectedReturnDate = rowToCheck["Date Expected Back"].ToString();
DateTime dateTimeOfLoanExpiration = DateTime.Parse(expectedReturnDate);
DateTime today = DateTime.Now;
int compareExpirationToNow = DateTime.Compare(dateTimeOfLoanExpiration, today);
//If the date held by the record is of a date earlier than today, set the overdue flag as true
if (compareExpirationToNow.Equals(-1))
{ rowToCheck["Overdue"] = "True"; }
else
{ rowToCheck["Overdue"] = "False"; }
}
}).Start();
}
public static void MoveRecordToArchive(DataRow rowToArchive, string tableToArchiveFrom)
{
//Procedure to move a record from the table containing the original to the equivalent archive table
string tableToArchiveTo = "archived " + tableToArchiveFrom;
dbBookings.Tables[tableToArchiveTo].ImportRow(rowToArchive);
dbBookings.Tables[tableToArchiveFrom].Rows.Remove(rowToArchive);
}
public static void GenerateReport(DataView recordsToOutput, string outputFileName)
{
//Procedure to generate a report containing a selection of records in the format of a HTML document
if(File.Exists(outputFileName)) { File.Delete(outputFileName); }
//Set the files containing the header and footer to be included
StreamReader swReadHeaderFile = new StreamReader("ReportHeader.txt");
StreamReader swReadFooterFile = new StreamReader("ReportFooter.txt");
StreamWriter swWriteReportFile = File.AppendText(outputFileName);
string readLineFromText = "";
string tableCaption = "Report to show " + recordsToOutput.Table.TableName;
int numberOfColumns = recordsToOutput.Table.Columns.Count;
//Read the header file and write it to the report output file
while ((readLineFromText = swReadHeaderFile.ReadLine()) != null)
{
swWriteReportFile.WriteLine(readLineFromText);
}
swWriteReportFile.WriteLine("<font size=\"1\"><i>" + DateTime.Now.ToString("dddd, dd MMMM yyyy HH:mm:ss") + "</i></font>");
//If there is a filter on the dataview
if (recordsToOutput.RowFilter.Length > 0)
{
string filterApplied = recordsToOutput.RowFilter;
//Create the table which will contain the criteria for the search
swWriteReportFile.WriteLine("<table class=\"CriteriaTable\" width=\"200\" border=\"1\">");
//Create a caption for the table
swWriteReportFile.WriteLine("<CAPTION CLASS=\"RecordTable\"> <b>Search Criteria</b> </CAPTION>");
swWriteReportFile.WriteLine("<TR CLASS=\"RecordTable\">");
Regex rgxFiltersApplied = new Regex(" AND ");
string[] filtersApplied = rgxFiltersApplied.Split(filterApplied);
swWriteReportFile.WriteLine("<TR CLASS=\"RecordTable\">");
swWriteReportFile.WriteLine("<TD CLASS=\"RecordTable\">");
for (int repeat = 0; repeat < filtersApplied.Length; repeat++)
{
string filterAppliedFormatted = filtersApplied[repeat].Replace("LIKE", "=").Replace("'%", "'").Replace("%'", "'").Replace("[", "").Replace("]", "");
swWriteReportFile.WriteLine("<p>" + filterAppliedFormatted + "</p>");
}
swWriteReportFile.WriteLine("</td>");
swWriteReportFile.WriteLine("</tr>");
swWriteReportFile.WriteLine("</table>");
}
//Create the table which will contain the records
swWriteReportFile.WriteLine("<table class=\"RecordTable\" width=\"600\" border=\"1\">");
//Create a caption for the table
swWriteReportFile.WriteLine("<CAPTION CLASS=\"RecordTable\">" + tableCaption + "</CAPTION>");
swWriteReportFile.WriteLine("<TR CLASS=\"RecordTable\">");
//Create the cells in the table to display the column names
for (int a = 0; a < numberOfColumns; a++)
{
string columnToCreate = recordsToOutput.Table.Columns[a].ToString();
int spaceToAllocateColumn = 13 * columnToCreate.Length;
string lineToAppend = "<th class=\"RecordTable\" width=\"" + spaceToAllocateColumn + "\">" + columnToCreate + "</th>";
swWriteReportFile.WriteLine(lineToAppend);
}
swWriteReportFile.WriteLine("</td>");
//Create the rows in the table to display the records
for (int a = 0; a < recordsToOutput.Count; a++)
{
DataRowView recordToOutput = recordsToOutput[a];
swWriteReportFile.WriteLine("<tr>");
//Create the cells within each row to display the contents of each field in the record
for(int b=0;b< numberOfColumns;b++)
{
string cellValue;
string columnDataType = recordsToOutput.Table.Columns[b].DataType.ToString();
//If the value is a date, cut off the time element which will follow (And is not set)
switch (columnDataType)
{
case "System.DateTime":
cellValue = recordToOutput[b].ToString().Split(' ')[0];
break;
default:
cellValue = recordToOutput[b].ToString();
break;
}
string lineToAppend = "<TD CLASS=\"RecordTable\">" + cellValue + "</td>";
swWriteReportFile.WriteLine(lineToAppend);
}
swWriteReportFile.WriteLine("</tr>");
}
//Include the tag which closes the table
swWriteReportFile.WriteLine("</table>");
//Include the date and time that the report was generated
//Read the footer file and write it to the report output file
while ((readLineFromText = swReadFooterFile.ReadLine()) != null)
{
swWriteReportFile.WriteLine(readLineFromText);
}
swWriteReportFile.Close();
swReadHeaderFile.Close();
swReadFooterFile.Close();
}
}
}