//******************************************************************************************* //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(); } }