public opdrachtPage(Page page, Job job = null)
{
this.previousPage = page;
InitializeComponent();
this.selectedItems = new List<JobObject>();
this.dbhandler = singletonDbHandler.getInstance();
this.brandSelection.ItemsSource = this.dbhandler.getBrands();
if (job != null) {
this.opdracht = job;
this.jobList = new ObservableCollection<JobObject>(this.opdracht.JobObjects);
this.jobTasksViewer.ItemsSource = this.jobList;
this.setPageElements();
this.updateJobViewer();
} else {
this.opdracht = new Job();
this.jobList = new ObservableCollection<JobObject>(this.opdracht.JobObjects);
this.jobTasksViewer.ItemsSource = this.jobList;
this.opdracht.setPrize(helperFunctions.milieukosten);
}
this.environmentCosts.Content = helperFunctions.prizeToString(helperFunctions.milieukosten);
this.subTotalPrize.Content = helperFunctions.prizeToString(helperFunctions.calculateAmountExcl(this.opdracht.JobObjects));
int totalExcl = helperFunctions.exclVAT(this.opdracht.Amount) + helperFunctions.milieukosten;
this.totalExclBtw.Content = helperFunctions.prizeToString(totalExcl);
int totalIncl = this.opdracht.Amount + helperFunctions.inclVAT(helperFunctions.milieukosten);
this.totalPrizeIncl.Content = helperFunctions.prizeToString(totalIncl);
this.btwPrize.Content = helperFunctions.prizeToString(totalIncl - totalExcl);
}
public bonViewer(Page page, Job job)
{
this.previousPage = page;
this.job = job;
this.dbhandler = singletonDbHandler.getInstance();
InitializeComponent();
string path = "E:\\Docs\\VDIJssel\\WPF-PriceChecker\\PriceCheckerWPF\\bin\\Debug\\internal\\bonnen\\" + job.Id + ".pdf";
Uri uri;
Uri.TryCreate(path, UriKind.Absolute, out uri);
if (uri != null){
this.bonWebBrowser.Source = uri;
} else {
throw new IOException("File doesn't exist");
}
}
private void continueButton_Click(object sender, RoutedEventArgs e)
{
this.opdracht.Name = this.nameInput.Text;
this.opdracht.Date = this.dateInput.Text;
string telnr = this.phoneInput.Text;
this.opdracht.Notes = this.notesBox.Text;
this.opdracht.PlateNumber = this.plateInput.Text;
if (this.distanceInput.Text == "") {
errorDist.Visibility = Visibility.Visible;
return;
}
int dist;
if (int.TryParse(this.distanceInput.Text, out dist)) {
this.opdracht.Distance = dist;
} else {
errorDist.Visibility = Visibility.Visible;
return;
}
if (this.nameInput.Text == "") {
errorNaam.Visibility = Visibility.Visible;
return;
}
int x;
if (telnr == "") {
errorPhone.Visibility = Visibility.Visible;
return;
}
if (telnr.Length > 10) {
telnr = telnr.Replace("-", "");
}
if (telnr.Length != 10 || int.TryParse(telnr, out x) == false) {
validPhone.Visibility = Visibility.Visible;
return;
}
this.opdracht.Phone = telnr;
this.opdracht.JobObjects = this.jobList;
this.opdracht.setPrize(helperFunctions.calculateAmountIncl(this.jobList));
this.opdracht.Brand = brandSelection.Text;
this.opdracht = this.dbhandler.insertJob(this.opdracht);
Printer printer = new Printer(this.opdracht);
printer.createPDF();
_mainWindow.SetPage(new bonViewer(this, this.opdracht));
}
private void updatePlate(Job job, IDbTransaction transaction)
{
IDbCommand command = this.databaseFactory.CreateCommand();
command.CommandText = String.Format(@"UPDATE {0} SET
name = @name, phone = @phone, brand = @brand WHERE platenumber = @plate",
dbConstants.plateInfoTable);
command.Parameters.Add(this.createParameter("@plate", job.PlateNumber));
command.Parameters.Add(this.createParameter("@name", job.Name));
command.Parameters.Add(this.createParameter("@phone", job.Phone));
command.Parameters.Add(this.createParameter("@brand", this.getBrandIdFromString(job.Brand, transaction)));
command.CommandType = CommandType.Text;
command.Connection = transaction.Connection;
try {
command.ExecuteNonQuery();
} catch (Exception e) {
throw new Exception("[updatePlate] " + e);
}
}
private void insertPlate(Job job, IDbTransaction transaction)
{
IDbCommand command = this.databaseFactory.CreateCommand();
command.CommandText = String.Format(@"INSERT INTO {0} (platenumber, name, phone, brand)
VALUES (@plate, @name, @phone, @brand)", dbConstants.plateInfoTable);
command.Parameters.Add(this.createParameter("@plate", job.PlateNumber));
command.Parameters.Add(this.createParameter("@name", job.Name));
command.Parameters.Add(this.createParameter("@phone", job.Phone));
command.Parameters.Add(this.createParameter("@brand", this.getBrandIdFromString(job.Brand, transaction)));
command.CommandType = CommandType.Text;
command.Connection = transaction.Connection;
try {
command.ExecuteNonQuery();
} catch (Exception e) {
throw new Exception("[insertPlate] " + e);
}
}
private void removeProductsFromJob(Job job, IDbTransaction transaction)
{
IDbCommand command = this.databaseFactory.CreateCommand();
command.CommandText = String.Format("DELETE FROM {0} WHERE job_id = " + job.Id,
dbConstants.jobLinkProdTable);
command.CommandType = CommandType.Text;
command.Connection = transaction.Connection;
try {
command.ExecuteNonQuery();
} catch (Exception e) {
throw new Exception("[insertJobProduct] " + e);
}
}
private void insertJobProducts(Job job, IDbTransaction transaction)
{
try {
removeProductsFromJob(job, transaction);
} catch (Exception e) {
throw new Exception("" + e);
}
foreach (JobObject prod in job.JobObjects) {
IDbCommand command = this.databaseFactory.CreateCommand();
command.CommandText = String.Format(@"
INSERT INTO {0} (job_id, product_id, amount)
VALUES (@job, @prod, @amount)", dbConstants.jobLinkProdTable);
command.Parameters.Add(this.createParameter("@job", job.Id));
command.Parameters.Add(this.createParameter("@prod", prod.Product.Id));
command.Parameters.Add(this.createParameter("@amount", prod.Multiplier));
command.CommandType = CommandType.Text;
command.Connection = transaction.Connection;
try {
command.ExecuteNonQuery();
} catch (Exception e) {
throw new Exception("[insertJobProduct] " + e);
}
}
}
private int checkPlate(Job job, IDbTransaction transaction)
{
IDbCommand command = this.databaseFactory.CreateCommand();
command.CommandText = String.Format(@"SELECT * FROM {0} WHERE platenumber = @plate",
dbConstants.plateInfoTable);
command.Parameters.Add(this.createParameter("@plate", job.PlateNumber));
command.CommandType = CommandType.Text;
command.Connection = transaction.Connection;
try {
using (IDataReader reader = command.ExecuteReader()) {
if (reader.Read()) {
string name = reader.GetString(reader.GetOrdinal("name"));
string phone = reader.GetString(reader.GetOrdinal("phone"));
if (name == job.Name && phone == job.Phone) {
return 1;
} else {
return 0;
}
}
reader.Close();
}
} catch (Exception e) {
throw new Exception("[checkPlate] " + e);
}
return -1;
}
public void updateJob(Job job)
{
IDbTransaction transaction;
IDbConnection databaseConnection = this.databaseFactory.CreateConnection();
databaseConnection.ConnectionString = this.connection.ConnectionString;
databaseConnection.Open();
transaction = databaseConnection.BeginTransaction();
IDbCommand command = this.databaseFactory.CreateCommand();
command.CommandText = String.Format(@"UPDATE {0} SET
plate_number = @plate,
notes = @notes,
date = @date,
distance = @distance WHERE id = " + job.Id, dbConstants.jobTable);
command.Parameters.Add(this.createParameter("@plate", job.PlateNumber));
command.Parameters.Add(this.createParameter("@notes", job.Notes));
command.Parameters.Add(this.createParameter("@date", job.Date));
command.Parameters.Add(this.createParameter("@distance", job.Distance));
command.CommandType = CommandType.Text;
command.Connection = transaction.Connection;
try {
int plateExists = this.checkPlate(job, transaction);
if (plateExists == 0) {
this.updatePlate(job, transaction);
} else if (plateExists == -1) {
//Something might be wrong...
this.insertPlate(job, transaction);
}
this.insertJobProducts(job, transaction);
transaction.Commit();
} catch (Exception e) {
transaction.Rollback();
throw new Exception(String.Format("[insertJob] Job could not be updated. Error: {0}", e));
} finally {
databaseConnection.Dispose();
}
}
public Job insertJob(Job job)
{
IDbTransaction transaction;
IDbConnection databaseConnection = this.databaseFactory.CreateConnection();
databaseConnection.ConnectionString = this.connection.ConnectionString;
databaseConnection.Open();
transaction = databaseConnection.BeginTransaction();
IDbCommand command = this.databaseFactory.CreateCommand();
command.CommandText = String.Format(@"INSERT INTO {0} (plate_number, notes, date, distance)
VALUES (@plate, @notes, @date, @distance); SELECT LAST_INSERT_ID();",
dbConstants.jobTable);
command.Parameters.Add(this.createParameter("@plate", job.PlateNumber));
command.Parameters.Add(this.createParameter("@notes", job.Notes));
command.Parameters.Add(this.createParameter("@date", job.Date));
command.Parameters.Add(this.createParameter("@distance", job.Distance));
command.CommandType = CommandType.Text;
command.Connection = transaction.Connection;
try {
int plateExists = this.checkPlate(job, transaction);
if (plateExists == 0) {
this.updatePlate(job, transaction);
} else if (plateExists == -1) {
this.insertPlate(job, transaction);
}
using (IDataReader reader = command.ExecuteReader()) {
if (reader.Read()) {
job.Id = reader.GetInt32(0);
} else {
throw new Exception("No Id Acquired");
}
reader.Close();
this.insertJobProducts(job, transaction);
transaction.Commit();
return job;
}
} catch (Exception e) {
transaction.Rollback();
throw new Exception(String.Format("[insertJob] Job could not be inserted. Error: {0}", e));
} finally {
databaseConnection.Dispose();
}
}
public IList<Job> getJobsWithPlateNumber(string plate)
{
IDbTransaction transaction;
IDbConnection databaseConnection = this.databaseFactory.CreateConnection();
databaseConnection.ConnectionString = this.connection.ConnectionString;
databaseConnection.Open();
transaction = databaseConnection.BeginTransaction();
IDbCommand command = this.databaseFactory.CreateCommand();
command.CommandText = String.Format(@"
SELECT {0}.id, {0}.plate_number, {0}.notes, {0}.date, {1}.name, {1}.phone, {0}.distance
FROM {0} JOIN {1} ON {0}.plate_number = {1}.platenumber
WHERE plate_number LIKE '%" + plate + "%'", dbConstants.jobTable, dbConstants.plateInfoTable);
command.CommandType = CommandType.Text;
command.Connection = transaction.Connection;
IList<Job> result = new List<Job>();
try {
using (IDataReader reader = command.ExecuteReader()) {
while (reader.Read()) {
Job job = new Job();
job.PlateNumber = reader.GetString(reader.GetOrdinal("plate_number"));
job.Notes = reader.GetString(reader.GetOrdinal("notes"));
job.Date = reader.GetString(reader.GetOrdinal("date"));
job.Name = reader.GetString(reader.GetOrdinal("name"));
job.Phone = reader.GetString(reader.GetOrdinal("phone"));
job.Id = reader.GetInt32(reader.GetOrdinal("id"));
job.Distance = reader.GetInt32(reader.GetOrdinal("distance"));
result.Add(job);
}
reader.Close();
}
} catch (Exception e) {
throw new Exception("No data acquired. Error message: " + e);
}
foreach (Job job in result) {
job.JobObjects = (List<JobObject>)this.getProductsOnJob(job.Id, transaction);
job.setPrize(helperFunctions.calculateAmountIncl(job.JobObjects));
}
return result;
}
