//*******************************************************************************************
//adding new submission
protected void Button_Create_Submission_Click(object sender, EventArgs e)
{
try
{
ContentPlaceHolder placeholder = (ContentPlaceHolder)Page.Master.FindControl("ContentPlaceHolder1");
//ListView notes = (ListView)placeholder.FindControl("ListView_Consultant_Status");
//string statusMessage = "message not changed";
string recruiter, client,
method, city, state, jobTitle, duration,
vendor, vendorContact,
clientSubmission, remarks,
submissionStatus;
DateTime date;
Decimal rate;
bool active;
int sLNO, consultantId, recruiterID = 0, clientID, vendorID, vendorContactID;
//get values entered by user
using (var db = new ModelContext())
{
sLNO = db.Submissions.Max(c => c.Slno) + 1;
}
consultantId = Int32.Parse(ConsultantId.Value);
recruiter = cb_Recruiter.Text;
client = cb_Client.Text;
rate = Decimal.Parse(tb_Rate.Text);
method = tb_Method.Text;
city = tb_City.Text;
state = cb_State.SelectedValue;
jobTitle = tb_Job_Title.Text;
duration = tb_Duration.Text;
vendor = cb_Vendor.Text;
vendorContact = cb_Vendor_Contact.Text;
clientSubmission = tb_Client_Submission.Text;
remarks = tb_Remarks.Text;
submissionStatus = cb_Submission_Status.Text;
date = DateTime.Parse(tb_Date.Text);
active = true;
//dtbs connect
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/joshh/OneDrive/Documents/Database2.accdb";
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
//get all SLNO numbers
ArrayList sLNONums = new ArrayList();
string selectSLNONumbersQuery = "SELECT [SLNO] FROM Submissions;";
OleDbCommand readCmd = new OleDbCommand(selectSLNONumbersQuery, con);
OleDbDataReader reader = readCmd.ExecuteReader();
while (reader.Read())
{
sLNONums.Add(reader["SLNO"].ToString());
}
reader.Close();
//check if SLNO already taken
if (sLNONums.Contains(sLNO))
{
throw new ArgumentException("SLNO number already used for different submission.");
}
//check recruiter name exists and get recruiter id
List <string> recruiterNames = new List <string>();
ArrayList recruiters = new ArrayList();
string selectRecruiterNamesQuery = "SELECT [ID], [Recruiter Name] FROM [ASR-RECRUITERS];";
readCmd = new OleDbCommand(selectRecruiterNamesQuery, con);
reader = readCmd.ExecuteReader();
while (reader.Read())
{
recruiterNames.Add(reader["Recruiter Name"].ToString());
recruiters.Add(new Recruiter(reader["ID"].ToString(), reader["Recruiter Name"].ToString()));
}
reader.Close();
//check if recruiter name already taken
if (!recruiterNames.Contains(recruiter))
{
throw new ArgumentException("Recruiter name does not exist. First click add new Recruiter.");
}
//get the rec id #
foreach (Recruiter r in recruiters)
{
if (recruiter.Equals(r.name))
{
recruiterID = Int32.Parse(r.id);
continue;
}
}
using (ModelContext db = new ModelContext())
{
//get client id
var clientDb = db.Clients.FirstOrDefault(c => c.ClientName == client);
if (clientDb == null)
{
clientDb = new Models.Clients {
ClientName = client
};
db.Clients.Add(clientDb);
db.SaveChanges();
LoggingUtility.appendToLog(String.Format("new client added '{0}'", client));
}
clientID = clientDb.Id;
//get vendor id
var vendorDb = db.Vendors.FirstOrDefault(c => c.VendorCompanyName == vendor);
if (vendorDb == null)
{
vendorDb = new Models.Vendors {
VendorCompanyName = vendor
};
db.Vendors.Add(vendorDb);
db.SaveChanges();
LoggingUtility.appendToLog(String.Format("new vendor added '{0}'", vendor));
}
vendorID = vendorDb.Id;
//get vendor contact id
var vendorContactDb = db.VendorContact.FirstOrDefault(c => c.RecruiterName == vendorContact);
if (vendorContactDb == null)
{
vendorContactDb = new Models.VendorContact {
RecruiterName = vendorContact
};
db.VendorContact.Add(vendorContactDb);
db.SaveChanges();
LoggingUtility.appendToLog(String.Format("new vendor contact added '{0}'", vendorContact));
}
vendorContactID = vendorContactDb.Id;
//create new submission
var newSubmission = new Models.Submissions
{
Slno = sLNO,
AsrRecruiterId = recruiterID,
Date = date,
ConsultantId = consultantId,
JobTitle = jobTitle,
City = city,
State = state,
Duration = duration,
VendorId = vendorID,
VendorContactId = vendorContactID,
ClientId = clientID,
RatePerHour = rate,
Method = method,
ClientSubmission = clientSubmission,
Remarks = remarks,
SubmissionStatus = submissionStatus,
Active = active
};
db.Submissions.Add(newSubmission);
db.SaveChanges();
LoggingUtility.appendToLog(String.Format("new submission successfully created with SLNO # {0} to client {1}", newSubmission.Slno, newSubmission.Client));
}
con.Close();
//showMessage(String.Format("New submission #{0} to {1} successfully created.", sLNO, client));
//refresh page
Response.Redirect($"~/Pages/ViewAllSubmissions.aspx?Id={ConsultantId.Value}");
}
catch (Exception ex)
{
//statusMessage = ex.Message;
//showMessage(ex.Message);
//LoggingUtility.appendToLog(ex.Message);
//LoggingUtility.appendToLog(ex.StackTrace);
handleException(ex, sender.ToString());
}
LoggingUtility.writeMessage();
//Response.Write("<script>alert('" + statusMessage + "')</script>");
}
private void updateSubmission(int slnoNum)
{
using (ModelContext db = new ModelContext())
{
var submissionDb = db.Submissions.FirstOrDefault(c => c.Slno == slnoNum);
if (submissionDb == null)
{
throw new ArgumentException(String.Format("Submission for SLNO #{0} cannot be found", slnoNum));
}
//get fields
string recruiter, client, rateString, method, city, state, jobTitle, duration, vendor, vendorContact, clientSubmission, remarks, status, dateString;
bool active;
recruiter = ComboBox_Recruiter.Text;
client = ComboBox_Client.Text;
rateString = TextBox_Rate.Text;
method = TextBox_Method.Text;
city = TextBox_City.Text;
state = ComboBox_State.Text;
jobTitle = TextBox_JobTitle.Text;
duration = TextBox_Duration.Text;
vendor = ComboBox_Vendor.Text;
vendorContact = ComboBox_VendorContact.Text;
clientSubmission = TextBox_ClientSubmission.Text;
remarks = TextBox_Remarks.Text;
status = ComboBox_SubmissionStatus.Text;
dateString = Edit_TextBox_Date.Text;
active = CheckBox_Active.Checked;
//check fields
FieldValidator.checkFields(recruiter, client, rateString, method, city, state, jobTitle, duration, vendor, vendorContact, clientSubmission, remarks, status, dateString);
int rate = Int32.Parse(rateString);
DateTime date = DateTime.Parse(dateString);
//process submission changes
//check if recruiter was changed, and then if recruiter doesn't exist, throw exception
if (submissionDb.AsrRecruiterId == null || !db.AsrRecruiters.FirstOrDefault(c => c.Id == submissionDb.AsrRecruiterId).RecruiterName.Equals(recruiter))
{
var recruiterDb = db.AsrRecruiters.FirstOrDefault(c => c.RecruiterName.Equals(recruiter));
if (recruiterDb == null)
{
throw new ArgumentException(String.Format("Recruiter Name '{0}' does not exist in database. Go to 'Add New Recruiter'", recruiter));
}
else
{
submissionDb.AsrRecruiterId = recruiterDb.Id;
}
}
//if client/vendor/vendorContact changed, and doesn't exist, first add to db
if (submissionDb.ClientId == null || !db.Clients.FirstOrDefault(c => c.Id == submissionDb.ClientId).ClientName.Equals(client))
{
var clientDb = db.Clients.FirstOrDefault(c => c.ClientName.Equals(client));
if (clientDb == null)
{
clientDb = new Models.Clients {
ClientName = client
};
db.Clients.Add(clientDb);
db.SaveChanges();
LoggingUtility.appendToLog(String.Format("new client added '{0}'", client));
}
submissionDb.ClientId = clientDb.Id;
}
if (submissionDb.VendorId == null || !db.Vendors.FirstOrDefault(c => c.Id == submissionDb.VendorId).VendorCompanyName.Equals(vendor))
{
var vendorDb = db.Vendors.FirstOrDefault(c => c.VendorCompanyName.Equals(vendor));
if (vendorDb == null)
{
vendorDb = new Models.Vendors {
VendorCompanyName = vendor
};
db.Vendors.Add(vendorDb);
db.SaveChanges();
LoggingUtility.appendToLog(String.Format("new vendor added '{0}'", vendor));
}
submissionDb.VendorId = vendorDb.Id;
}
if (submissionDb.VendorContactId == null || !db.VendorContact.FirstOrDefault(c => c.Id == submissionDb.VendorContactId).RecruiterName.Equals(vendorContact))
{
var vendorContactDb = db.VendorContact.FirstOrDefault(c => c.RecruiterName.Equals(vendorContact));
if (vendorContactDb == null)
{
vendorContactDb = new Models.VendorContact {
RecruiterName = vendorContact
};
db.VendorContact.Add(vendorContactDb);
db.SaveChanges();
LoggingUtility.appendToLog($"new vendor contact added '{vendorContact}'");
}
submissionDb.VendorContactId = vendorContactDb.Id;
}
var stateDb = db.UsStates.FirstOrDefault(c => c.Initials.Equals(state) || c.Ucname.Equals(state) || c.Lcname.Equals(state));
submissionDb.State = stateDb.Initials;
submissionDb.RatePerHour = rate;
submissionDb.Method = method;
submissionDb.City = city;
submissionDb.JobTitle = jobTitle;
submissionDb.Duration = duration;
submissionDb.ClientSubmission = clientSubmission;
submissionDb.Remarks = remarks;
submissionDb.SubmissionStatus = status;
submissionDb.Date = date;
submissionDb.Active = active;
db.SaveChanges();
LoggingUtility.writeMessage();
}
}
