public void OLEInsertNewJob(DataJob dj)
{
//int newJobId = 0;
List <DataJob> listDataJob = new List <DataJob>();
using (OleDbConnection conn = new OleDbConnection(Constants.ConnectionString))
{
conn.Open();
using (OleDbCommand comm = new OleDbCommand())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("INSERT INTO Job (EmployeeId, AgencyId, JobDetails, StartJob, Endjob, Hours, Rate, PaymentReceivedDate, TotalPaymentReceived) ");
sb.AppendLine("VALUES (@EmployeeId, @AgencyId, @JobDetails, @StartJob, @EndJob, @Hours, @Rate, @PaymentReceivedDate, @TotalPaymentReceived) ");
comm.CommandText = sb.ToString();
comm.Connection = conn;
comm.Parameters.Clear();
comm.Parameters.AddWithValue("@EmployeeId", dj.EmployeeId);
comm.Parameters.AddWithValue("@AgencyId", dj.AgencyId);
comm.Parameters.AddWithValue("@JobDetails", dj.JobDetails);
comm.Parameters.AddWithValue("@StartJob", dj.StartJob);
comm.Parameters.AddWithValue("@Endjob", dj.EndJob);
comm.Parameters.AddWithValue("@Hours", dj.Hours);
comm.Parameters.AddWithValue("@Rate", dj.Rate);
comm.Parameters.AddWithValue("@PaymentReceivedDate", dj.EmployeeId);
comm.Parameters.AddWithValue("@TotalPaymentReceived", dj.EmployeeId);
comm.ExecuteScalar();
return;
}
}
}
public void OLEUpdateExistingJob(DataJob dj)
{
List <DataJob> listDataJob = new List <DataJob>();
using (OleDbConnection conn = new OleDbConnection(Constants.ConnectionString))
{
conn.Open();
using (OleDbCommand comm = new OleDbCommand())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("UPDATE Job SET AgencyId = @AgencyId, JobDetails = @JobDetails, StartJob = @StartJob, ");
sb.AppendLine("Endjob = @EndJob, PaymentReceived = @PaymentReceived, TotalPayment = @TotalPayment, ");
sb.AppendLine("WebLink = @WebLink, MediaLink = @MediaLink ");
sb.AppendLine("WHERE JobId = @JobId");
comm.CommandText = sb.ToString();
comm.Connection = conn;
comm.Parameters.Clear();
comm.Parameters.AddWithValue("@JobId", dj.JobId);
comm.Parameters.AddWithValue("@AgencyId", dj.AgencyId);
comm.Parameters.AddWithValue("@JobDetails", dj.JobDetails);
comm.Parameters.AddWithValue("@StartJob", dj.StartJob);
comm.Parameters.AddWithValue("@Endjob", dj.EndJob);
comm.Parameters.AddWithValue("@PaymentReceived", dj.PaymentReceived);
comm.Parameters.AddWithValue("@TotalPayment", dj.TotalPayment);
comm.Parameters.AddWithValue("@WebLink", dj.WebLink);
comm.Parameters.AddWithValue("@MediaLink", dj.MediaLink);
comm.ExecuteScalar();
return;
}
}
}
public void OLEInsertNewJob(DataJob dj)
{
//int newJobId = 0;
List <DataJob> listDataJob = new List <DataJob>();
using (OleDbConnection conn = new OleDbConnection(Constants.ConnectionString))
{
conn.Open();
using (OleDbCommand comm = new OleDbCommand())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("INSERT INTO Job (EmployeeId, AgencyId, JobDetails, StartJob, Endjob, PaymentReceived, TotalPayment, JobLink, MediaLink) ");
sb.AppendLine("VALUES (@EmployeeId, @AgencyId, @JobDetails, @StartJob, @EndJob, @PaymentReceived, @TotalPayment, @JobLink, @MediaLink) ");
comm.CommandText = sb.ToString();
comm.Connection = conn;
comm.Parameters.Clear();
comm.Parameters.AddWithValue("@EmployeeId", dj.EmployeeId);
comm.Parameters.AddWithValue("@AgencyId", dj.AgencyId);
comm.Parameters.AddWithValue("@JobDetails", dj.JobDetails);
comm.Parameters.AddWithValue("@StartJob", Convert.ToDateTime(dj.StartJob).ToString());
comm.Parameters.AddWithValue("@Endjob", Convert.ToDateTime(dj.EndJob).ToString());
comm.Parameters.AddWithValue("@PaymentReceived", dj.PaymentReceived);
comm.Parameters.AddWithValue("@TotalPayment", dj.TotalPayment);
comm.Parameters.AddWithValue("@WebLink", dj.WebLink);
comm.Parameters.AddWithValue("@MediaLink", dj.MediaLink);
comm.ExecuteNonQuery();
return;
}
}
}
private void buttonAdd_Click(object sender, EventArgs e)
{
DataJob dj = new DataJob();
int newFileId = 0;
using (Sql sql = new Sql())
{
// Do validation first so we know we've got everything
dj.EmployeeId = Constants.UserId;
dj.AgencyId = Convert.ToInt32(comboBoxAgency.SelectedValue);
dj.JobDetails = textBoxjobDetails.Text;
dj.StartJob = Convert.ToDateTime(dateTimePickerStartJob.Value);
dj.EndJob = Convert.ToDateTime(dateTimePickerEndJob.Value);
dj.Hours = Convert.ToDecimal(numericUpDownHours.Value);
dj.Rate = Convert.ToDecimal(textBoxRate.Text);
dj.PaymentReceivedDate = Convert.ToDateTime(dateTimePickerPaymentReceivedDate.Value);
dj.TotalPaymentReceived = Convert.ToDecimal(textBoxTotalPayment.Text);
//Insert Job to DB and dgv.
// Function determined by the type of db we are connecting to
if (Constants.DBMS == "MSSQL")
{
newFileId = sql.SQLInsertNewJob(dj);
}
else
{
sql.OLEInsertNewJob(dj);
}
// Add to dgv - Or could just do a refresh of the dgv from the db??????????
var index = dataGridViewJobs.Rows.Add();
dataGridViewJobs.Rows[index].Cells["JobId"].Value = dj.JobId;
dataGridViewJobs.Rows[index].Cells["EmployeeId"].Value = dj.EmployeeId;
dataGridViewJobs.Rows[index].Cells["AgencyId"].Value = dj.AgencyId;
dataGridViewJobs.Rows[index].Cells["JobDetails"].Value = dj.JobDetails;
dataGridViewJobs.Rows[index].Cells["StartJob"].Value = dj.StartJob;
dataGridViewJobs.Rows[index].Cells["EndJob"].Value = dj.EndJob;
dataGridViewJobs.Rows[index].Cells["Hours"].Value = dj.Hours;
dataGridViewJobs.Rows[index].Cells["Rate"].Value = dj.Rate;
dataGridViewJobs.Rows[index].Cells["PaymentReceivedDate"].Value = dj.PaymentReceivedDate;
dataGridViewJobs.Rows[index].Cells["TotalPaymentReceived"].Value = dj.TotalPaymentReceived;
}
}
public int SQLInsertNewJob(DataJob dj)
{
int newJobId = 0;
List <DataJob> listDataJob = new List <DataJob>();
using (SqlConnection conn = new SqlConnection(Constants.ConnectionString))
{
conn.Open();
using (SqlCommand comm = new SqlCommand())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("INSERT INTO Job (EmployeeId, AgencyId, JobDetails, StartJob, Endjob, PaymentReceived, TotalPayment, WebLink, MediaLink) ");
sb.AppendLine("OUTPUT Inserted.JobId ");
sb.AppendLine("VALUES (@EmployeeId, @AgencyId, @JobDetails, @StartJob, @EndJob, @PaymentReceived, @TotalPayment, @WebLink, @MediaLink) ");
comm.CommandText = sb.ToString();
comm.Connection = conn;
comm.Parameters.Clear();
comm.Parameters.AddWithValue("@EmployeeId", dj.EmployeeId);
comm.Parameters.AddWithValue("@AgencyId", dj.AgencyId);
comm.Parameters.AddWithValue("@JobDetails", dj.JobDetails);
comm.Parameters.AddWithValue("@StartJob", dj.StartJob);
comm.Parameters.AddWithValue("@Endjob", dj.EndJob);
comm.Parameters.AddWithValue("@PaymentReceived", dj.PaymentReceived);
comm.Parameters.AddWithValue("@TotalPayment", dj.TotalPayment);
comm.Parameters.AddWithValue("@WebLink", dj.WebLink);
comm.Parameters.AddWithValue("@MediaLink", dj.MediaLink);
newJobId = Convert.ToInt32(comm.ExecuteScalar());
return(newJobId);
}
}
}
private void buttonSave_Click(object sender, EventArgs e)
{
DataJob dj = new DataJob();
int newFileId = 0;
int existingJobId = Convert.ToInt32(textBoxJobId.Text);
if (textBoxjobDetails.Text == "")
{
MessageBox.Show("No job details entered.", "Information",
MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
return;
}
if (textBoxTotalPayment.Text == "0")
{
MessageBox.Show("No payment details entered.", "Information",
MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
return;
}
using (Sql sql = new Sql())
{
// Do validation first so we know we've got everything
dj.EmployeeId = Constants.UserId;
dj.AgencyId = Convert.ToInt32(comboBoxAgency.SelectedValue);
dj.JobDetails = textBoxjobDetails.Text;
dj.StartJob = Convert.ToDateTime(dateTimePickerStartJob.Value);
dj.EndJob = Convert.ToDateTime(dateTimePickerEndJob.Value);
dj.PaymentReceived = Convert.ToBoolean(checkBox1.Checked);
dj.TotalPayment = Convert.ToDecimal(textBoxTotalPayment.Text);
dj.WebLink = textBoxWebLink.Text;
dj.MediaLink = textBoxMediaLink.Text;
// Function determined by the type of db we are connecting to
if (Constants.DBMS == "MSSQL")
{
if (sql.SQLCheckJobExists(existingJobId))
{
// must be update
dj.JobId = existingJobId;
sql.SQLUpdateExistingJob(dj);
}
else
{
//Insert Job to DB and dgv.
newFileId = sql.SQLInsertNewJob(dj);
}
}
else
{
if (sql.OLECheckJobExists(existingJobId))
{
// must be update
dj.JobId = existingJobId;
sql.OLEUpdateExistingJob(dj);
}
else
{
//Insert Job to DB and dgv.
sql.OLEInsertNewJob(dj);
}
}
// Add to dgv - Or could just do a refresh of the dgv from the db??????????
jobRefresh();
// Clear down the insert window & Set the default Agency Type
comboBoxAgency.SelectedValue = Convert.ToInt32(ConfigurationManager.AppSettings["DefaultAgencyId"]);
textBoxjobDetails.Text = "";
dateTimePickerStartJob.Value = DateTime.Now;
dateTimePickerEndJob.Value = DateTime.Now;
checkBox1.Checked = Convert.ToBoolean(0); // Cannot rename as pops open the save as dialog ?????
textBoxTotalPayment.Text = "0";
textBoxJobId.Text = "0";
textBoxWebLink.Text = "";
textBoxMediaLink.Text = "";
// Update the finance info
UpdateFinances();
}
}
public List <DataJob> SQLGetJobs()
{
// Assign Empty Values
List <DataJob> listDataJob = new List <DataJob>();
using (SqlConnection conn = new SqlConnection(Constants.ConnectionString))
{
conn.Open();
using (SqlCommand comm = new SqlCommand())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT j.JobId, j.EmployeeId, e.FirstName, e.LastName, j.AgencyId, a.AgencyName, j.JobDetails, ");
sb.AppendLine("j.StartJob, j.EndJob, j.hours, j.Rate, j.PaymentReceivedDate, j.TotalPaymentReceived ");
sb.AppendLine("FROM Job j INNER JOIN ");
sb.AppendLine("Employee e ON e.EmployeeId = j.EmployeeId INNER JOIN ");
sb.AppendLine("Agency a ON a.AgencyId = j.AgencyId");
sb.AppendLine("ORDER BY j.StartJob DESC");
comm.CommandText = sb.ToString();
comm.Connection = conn;
//comm.Parameters.Clear();
//comm.Parameters.AddWithValue("@RegNumber", regNumber);
using (SqlDataReader reader = comm.ExecuteReader())
{
sb.Clear();
while (reader.Read())
{
DataJob dj = new DataJob();
// Assign our Output Variables
dj.JobId = Convert.ToInt32(reader["JobId"]);
dj.EmployeeId = Convert.ToInt32(reader["EmployeeId"]);
dj.EmployeeFirstName = reader["FirstName"].ToString();
dj.EmployeeLastName = reader["LastName"].ToString();
dj.AgencyId = Convert.ToInt32(reader["AgencyId"]);
dj.AgencyName = reader["AgencyName"].ToString();
dj.JobDetails = reader["JobDetails"].ToString();
int ordinal = reader.GetOrdinal("StartJob");
dj.StartJob = reader.IsDBNull(ordinal) ? (DateTime?)null : Convert.ToDateTime(reader["StartJob"]);
ordinal = reader.GetOrdinal("EndJob");
dj.EndJob = reader.IsDBNull(ordinal) ? (DateTime?)null : Convert.ToDateTime(reader["EndJob"]);
dj.Hours = Convert.ToInt32(reader["Hours"]);
dj.Rate = Convert.ToDecimal(reader["Rate"]);
ordinal = reader.GetOrdinal("PaymentReceivedDate");
dj.EndJob = reader.IsDBNull(ordinal) ? (DateTime?)null : Convert.ToDateTime(reader["PaymentReceivedDate"]);
//ordinal = reader.GetOrdinal("TotalPaymentReceived");
//dj.TotalPaymentReceived = reader.IsDBNull(ordinal) ? (DateTime?)null : Convert.ToDecimal(reader["TotalPaymentReceived"]);
// Add to the DataRecord
listDataJob.Add(dj);
}
}
return(listDataJob);
}
}
}
public List <DataJob> OLEGetJobs(int EditJobId)
{
// Assign Empty Values
List <DataJob> listDataJob = new List <DataJob>();
using (OleDbConnection conn = new OleDbConnection(Constants.ConnectionString))
{
conn.Open();
using (OleDbCommand comm = new OleDbCommand())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT Job.JobId, Job.EmployeeId, Job.AgencyId, Agency.AgencyName, Job.JobDetails, Job.StartJob, Job.EndJob, ");
sb.AppendLine("Job.TotalPayment, Job.PaymentReceived, Employee.FirstName, Employee.LastName, Job.WebLink, Job.MediaLink ");
sb.AppendLine("FROM (Job LEFT JOIN Agency ON Job.AgencyId = Agency.AgencyId) ");
sb.AppendLine("INNER JOIN Employee ON Job.EmployeeId = Employee.EmployeeId ");
if (EditJobId > 0)
{
sb.AppendLine("WHERE Job.JobId = @EditJobId ");
}
sb.AppendLine("ORDER BY Job.EndJob DESC;");
comm.CommandText = sb.ToString();
comm.Connection = conn;
comm.Parameters.Clear();
if (EditJobId > 0)
{
comm.Parameters.AddWithValue("EditJobId", EditJobId);
}
using (OleDbDataReader reader = comm.ExecuteReader())
{
sb.Clear();
while (reader.Read())
{
DataJob dj = new DataJob();
// Assign our Output Variables
dj.JobId = Convert.ToInt32(reader["JobId"]);
dj.EmployeeId = Convert.ToInt32(reader["EmployeeId"]);
dj.EmployeeFirstName = reader["FirstName"].ToString();
dj.EmployeeLastName = reader["LastName"].ToString();
dj.AgencyId = Convert.ToInt32(reader["AgencyId"]);
dj.AgencyName = reader["AgencyName"].ToString();
dj.JobDetails = reader["JobDetails"].ToString();
int ordinal = reader.GetOrdinal("StartJob");
dj.StartJob = reader.IsDBNull(ordinal) ? (DateTime?)null : Convert.ToDateTime(reader["StartJob"]);
ordinal = reader.GetOrdinal("EndJob");
dj.EndJob = reader.IsDBNull(ordinal) ? (DateTime?)null : Convert.ToDateTime(reader["EndJob"]);
dj.TotalPayment = Convert.ToDecimal(reader["TotalPayment"]);
dj.PaymentReceived = Convert.ToBoolean(reader["PaymentReceived"]);
dj.WebLink = reader["WebLink"].ToString();
dj.MediaLink = reader["MediaLink"].ToString();
// Add to the DataRecord
listDataJob.Add(dj);
}
}
return(listDataJob);
}
}
}