private string addMeetingSQL()
{
clsFormat objFormat = new clsFormat();
if (JobID == "" || JobID == null)
{
JobID = "null";
}
/* Todo - update this SQL statement to fit in with new logic within system */
string strSQL = "Insert into Review(Date,DesiredOutcome,Barriers,Note,Job_ID, Staff_ID, Meeting_ID, Funding_ID, Consumer_ID, TimeIn, TimeOut)";
strSQL = strSQL + " Select " + objFormat.formatDate(dtDate.Text) + ",'" + objFormat.removeApostrophe(txtOutcome.Text) + "','" + objFormat.removeApostrophe(txtBarriers.Text) + "',";
strSQL = strSQL + " '" + objFormat.removeApostrophe(txtDetails.Text) + "'," + JobID + ", Staff.ID, Meeting.ID, Consumer.Funding_ID," + ID;
strSQL = strSQL + ", '" + objFormat.convertTime_24hr(dtTimeIn.Value) + "', '" + objFormat.convertTime_24hr(dtTimeOut.Value) + "'";
strSQL = strSQL + " FROM Staff, Consumer, Meeting ";
// strSQL = strSQL + " Join ConsumerStaff on Staff.ID = ConsumerStaff.Staff_ID";
// strSQL = strSQL + " Join Consumer on ConsumerStaff.Consumer_ID = Consumer.ID";
// strSQL = strSQL + " , Meeting";
strSQL = strSQL + " WHERE Staff.FirstName || ' ' || Staff.LastName = '" + Staff + "'";
strSQL = strSQL + " AND Meeting.Description = '" + cmbMeeting.Text + "'";
strSQL = strSQL + " AND Consumer.ID = " + ID;
return(strSQL);
}
private string updateMeetingSQL()
{
clsFormat objFormat = new clsFormat();
if (JobID == "" || JobID == null)
{
JobID = "null";
}
string SQL = "Update Review";
SQL = SQL + " Set Date = " + objFormat.formatDate(dtDate.Text) + ", DesiredOutcome = '" + objFormat.removeApostrophe(txtOutcome.Text) + "'";
SQL = SQL + ", Barriers ='" + objFormat.removeApostrophe(txtBarriers.Text) + "'";
SQL = SQL + ", Note ='" + objFormat.removeApostrophe(txtDetails.Text) + "'";
SQL = SQL + ",Job_ID = " + JobID + ", Staff_ID = " + StaffID + ", Meeting_ID ='" + MeetingID + "'";
SQL = SQL + ",TimeIn= '" + objFormat.convertTime_24hr(dtTimeIn.Value) + "', TimeOut = '" + objFormat.convertTime_24hr(dtTimeOut.Value) + "'";
SQL = SQL + " Where Review.ID = " + ReviewID;
return(SQL);
}
private string updateAdmin_SQL()
{
clsFormat objFormat = new clsFormat();
string SQL = "Update Administrative";
SQL = SQL + " Set Date = " + objFormat.formatDate(dtDateAdmin.Text);
SQL = SQL + " , Description = '" + objFormat.removeApostrophe(txtDescription.Text) + "'";
SQL = SQL + " , StartTime = '" + objFormat.convertTime_24hr(dtTimeIn.Value) + "'";
SQL = SQL + " , EndTime = '" + objFormat.convertTime_24hr(dtTimeOut.Value) + "'";
SQL = SQL + " Where Administrative.ID = " + AdminID;
return(SQL);
}
private string addAdmin_SQL()
{
clsFormat objFormat = new clsFormat();
string SQL = "Insert Into Administrative(Date,Description,StartTime,EndTime, staff_ID)";
SQL = SQL + " Select " + objFormat.formatDate(dtDateAdmin.Text);
SQL = SQL + " ,'" + objFormat.removeApostrophe(txtDescription.Text) + "'";
SQL = SQL + " ,'" + objFormat.convertTime_24hr(dtTimeIn.Value) + "'";
SQL = SQL + " ,'" + objFormat.convertTime_24hr(dtTimeOut.Value) + "'";
SQL = SQL + " , Staff.ID";
SQL = SQL + " From Staff";
SQL = SQL + " Where FirstName || ' ' || LastName = '" + StaffID + "'";
return(SQL);
}
private void checkNewJob()
{
if ((txtEmployer.Text != Employer || txtJob.Text != Job) && txtJob.Text != "")
{
clsDB objDB = new clsDB(strDB);
clsFormat objFormat = new clsFormat();
string strInsert = "Insert Into Job(Title, Description, Employer, Address,City, Zip,PlacementDate,Consumer_ID)";
string strValues = " Values('" + txtJob.Text + "','" + objFormat.removeApostrophe(txtDescription.Text) + "','" + objFormat.removeApostrophe(txtEmployer.Text) + "','" + txtAddress.Text + "'";
strValues = strValues + ",'" + txtCity.Text + "','" + objFormat.formatZip(txtZip.Text) + "'," + objFormat.formatDate(txtPlacement.Text) + "," + ID + ")";
objDB.assignJob(strInsert + strValues);
// logic to email Kristen.
clsEmail objEmail = new clsEmail();
objEmail.MailInfo("*****@*****.**", txtConsumer.Text);
}
}