public void UpdateCustomerInfo(CustomerDetailsUpdatableField field, string OldValue, string NewValue, long CustomerNumber, long JobID) { if (OldValue == NewValue) { return; } if (File.Exists(ServerClientViewController.dbFilePath)) { // INSERT a record about the change to the CUSTUPDATE table (look for an existing record first, if there is one, update it) using (var connection = new SqliteConnection("Data Source=" + ServerClientViewController.dbFilePath)) { connection.Open(); var cmd = connection.CreateCommand(); cmd.CommandText = "SELECT * FROM CustUpdate WHERE Cust_OID = ? AND Field = \"" + field.ToString() + "\" "; cmd.Parameters.Add("@CustomerID", DbType.Int32).Value = CustomerNumber; var reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Close(); cmd.Parameters.Clear(); // UPDATE existing record in CUSTUPDATE cmd.CommandText = "UPDATE CustUpdate SET NewValue = ? WHERE Cust_OID = ? AND Field = \"" + field.ToString() + "\" "; cmd.Parameters.Add("@NewValue", DbType.String).Value = NewValue; cmd.Parameters.Add("@CustomerID", DbType.Int32).Value = CustomerNumber; cmd.ExecuteNonQuery(); _tabs._scView.Log(String.Format("UPDATE statement: {0}", cmd.CommandText)); foreach (SqliteParameter param in cmd.Parameters) { if (_tabs._scView != null && param.Value != null) { _tabs._scView.Log(String.Format("Param: {0} = {1}", param.ParameterName, param.Value.ToString())); } } } else { reader.Close(); cmd.Parameters.Clear(); // INSERT new record into CUSTUPDATE cmd.CommandText = "INSERT INTO CustUpdate (Empl_OID, Job_OID, FIELD, OldValue, NewValue, Cust_OID) VALUES (?, ?, \"" + field.ToString() + "\", ?, ?, ?)"; cmd.Parameters.Add("@EmployeeID", DbType.Int32).Value = MyConstants.EmployeeID; cmd.Parameters.Add("@JobID", DbType.Int32).Value = JobID; cmd.Parameters.Add("@OldValue", DbType.String).Value = OldValue; cmd.Parameters.Add("@NewValue", DbType.String).Value = NewValue; cmd.Parameters.Add("@CustomerID", DbType.Int32).Value = CustomerNumber; cmd.ExecuteNonQuery(); _tabs._scView.Log(String.Format("UPDATE statement: {0}", cmd.CommandText)); foreach (SqliteParameter param in cmd.Parameters) { if (_tabs._scView != null) { if (param.Value != null) { _tabs._scView.Log(String.Format("Param: {0} = {1}", param.ParameterName, param.Value.ToString())); } else { _tabs._scView.Log(String.Format("Param: {0} = {1}", param.ParameterName, DBNull.Value.ToString())); } } } } // UPDATE the field(s) in WCLIENT table // depends a lot on the field being updated cmd.Parameters.Clear(); switch (field) { case CustomerDetailsUpdatableField.CompanyName: { cmd.CommandText = "UPDATE Wclient SET wComName = ? WHERE Cusnum = ?"; cmd.Parameters.Add("@CompanyName", DbType.String).Value = NewValue; foreach (Customer c in this._tabs._jobRunTable.Customers) { if (c.CustomerNumber == CustomerNumber) { c.isCompany = !String.IsNullOrEmpty(NewValue); } } foreach (Customer c in this._tabs._jobRunTable.UserAddedCustomers) { if (c.CustomerNumber == CustomerNumber) { c.isCompany = !String.IsNullOrEmpty(NewValue); } } break; } case CustomerDetailsUpdatableField.FirstName: { cmd.CommandText = "UPDATE Wclient SET Wconame = ? WHERE Cusnum = ?"; cmd.Parameters.Add("@FirstName", DbType.String).Value = NewValue; break; } case CustomerDetailsUpdatableField.LastName: { cmd.CommandText = "UPDATE Wclient SET Wcsname = ? WHERE Cusnum = ?"; cmd.Parameters.Add("@LastName", DbType.String).Value = NewValue; break; } case CustomerDetailsUpdatableField.Address: { cmd.CommandText = "UPDATE Wclient SET Wcadd1 = ? WHERE Cusnum = ?"; cmd.Parameters.Add("@Address", DbType.String).Value = NewValue; break; } case CustomerDetailsUpdatableField.Suburb: { cmd.CommandText = "UPDATE Wclient SET Wcadd2 = ? WHERE Cusnum = ?"; cmd.Parameters.Add("@Suburb", DbType.String).Value = NewValue; break; } case CustomerDetailsUpdatableField.Phone: { string newval = ""; for (int i = 0; i < NewValue.Length; i++) { char c = NewValue[i]; if (char.IsDigit(c)) { newval += c; } } cmd.CommandText = "UPDATE Wclient SET Wcacde = ?, Wcphone = ? WHERE Cusnum = ?"; if (newval.Length > 2) { cmd.Parameters.Add("@AreaCode", DbType.String).Value = newval.Substring(0, 2); cmd.Parameters.Add("@PhoneNumber", DbType.String).Value = newval.Substring(2); } else { cmd.Parameters.Add("@AreaCode", DbType.String).Value = ""; cmd.Parameters.Add("@PhoneNumber", DbType.String).Value = newval; } break; } case CustomerDetailsUpdatableField.MobilePhone: { string newval = ""; for (int i = 0; i < NewValue.Length; i++) { char c = NewValue[i]; if (char.IsDigit(c)) { newval += c; } } cmd.CommandText = "UPDATE Wclient SET Mobpre = ?, Mobile = ? WHERE Cusnum = ?"; if (newval.Length > 2) { cmd.Parameters.Add("@MobilePrefix", DbType.String).Value = newval.Substring(0, 2); cmd.Parameters.Add("@MobileNumber", DbType.String).Value = newval.Substring(2); } else { cmd.Parameters.Add("@MobilePrefix", DbType.String).Value = ""; cmd.Parameters.Add("@MobileNumber", DbType.String).Value = newval; } break; } case CustomerDetailsUpdatableField.FallbackContact: { cmd.CommandText = "UPDATE Wclient SET Wcsoname = ?, Wcssname = ? WHERE Cusnum = ?"; int i = NewValue.LastIndexOf(' '); if (i != -1) { cmd.Parameters.Add("@FirstName", DbType.String).Value = NewValue.Substring(0, i); cmd.Parameters.Add("@LastName", DbType.String).Value = NewValue.Substring(i); } else { cmd.Parameters.Add("@FirstName", DbType.String).Value = NewValue; cmd.Parameters.Add("@LastName", DbType.String).Value = ""; } break; } case CustomerDetailsUpdatableField.FallbackPhone: { string newval = ""; for (int i = 0; i < NewValue.Length; i++) { char c = NewValue[i]; if (char.IsDigit(c)) { newval += c; } } cmd.CommandText = "UPDATE Wclient SET Wccoacde = ?, Wccophone = ? WHERE Cusnum = ?"; if (newval.Length > 2) { cmd.Parameters.Add("@AreaCode", DbType.String).Value = newval.Substring(0, 2); cmd.Parameters.Add("@PhoneNumber", DbType.String).Value = newval.Substring(2); } else { cmd.Parameters.Add("@AreaCode", DbType.String).Value = ""; cmd.Parameters.Add("@PhoneNumber", DbType.String).Value = newval; } break; } case CustomerDetailsUpdatableField.SpecialComments: { cmd.CommandText = "UPDATE WSALES SET SPECIALINSTRUCT=? WHERE UnitNum=? AND CusNum=?"; cmd.Parameters.Add("@InstructionsText", DbType.String).Value = NewValue; cmd.Parameters.Add("@UnitNumber", DbType.Int64).Value = (this._tabs._jobRunTable.CurrentJob.HasParent()) ? this._tabs._jobRunTable.FindParentJob(this._tabs._jobRunTable.CurrentJob).UnitNumber : this._tabs._jobRunTable.CurrentJob.UnitNumber; break; } case CustomerDetailsUpdatableField.AttentionReason: { cmd.CommandText = "UPDATE PL_RECOR SET ATTENTION_REASON=? WHERE BookNum=? AND CusNum=?"; cmd.Parameters.Add("@AttentionText", DbType.String).Value = NewValue; cmd.Parameters.Add("@Job_ID", DbType.Int64).Value = (this._tabs._jobRunTable.CurrentJob.HasParent()) ? this._tabs._jobRunTable.FindParentJob(this._tabs._jobRunTable.CurrentJob).JobBookingNumber : this._tabs._jobRunTable.CurrentJob.JobBookingNumber; break; } case CustomerDetailsUpdatableField.TubingUpgradeDone: { cmd.CommandText = "UPDATE Wclient SET tu_Done = ? WHERE Cusnum = ?"; cmd.Parameters.Add("@tubingDone", DbType.String).Value = NewValue; break; } case CustomerDetailsUpdatableField.Email: { cmd.CommandText = "UPDATE Wclient SET EmailAd = ? WHERE Cusnum = ?"; cmd.Parameters.Add("@EmailAddress", DbType.String).Value = NewValue; break; } } cmd.Parameters.Add("@CustomerID", DbType.Int32).Value = CustomerNumber; if (field != CustomerDetailsUpdatableField.JobPriceTotal) { cmd.ExecuteNonQuery(); _tabs._scView.Log(String.Format("UPDATE statement: {0}", cmd.CommandText)); foreach (SqliteParameter param in cmd.Parameters) { _tabs._scView.Log(String.Format("Param: {0} = {1}", param.ParameterName, param.Value.ToString())); } } } } else { _tabs._scView.Log("UpdateCustomerInfo: ERROR: Database file not found: " + ServerClientViewController.dbFilePath + "\n WHAT?"); } }
void HandleEditingDidEnd(object sender, EventArgs e) { JobRunTable jrt = _tabs._jobRunTable; if (jrt.CurrentCustomer != null) { if (jrt.Customers.IndexOf(jrt.CurrentCustomer) != -1) { jrt.CurrentJob = jrt.MainJobList[jrt.Customers.IndexOf(jrt.CurrentCustomer)]; } else { jrt.CurrentJob = jrt.UserCreatedJobs[jrt.UserAddedCustomers.IndexOf(jrt.CurrentCustomer)]; } CustomerDetailsUpdatableField field = CustomerDetailsUpdatableField.None; if (sender is UITextField) { field = (CustomerDetailsUpdatableField)Convert.ToInt32((sender as UITextField).Tag); } else if (sender is UITextView) { field = (CustomerDetailsUpdatableField)Convert.ToInt32((sender as UIView).Tag); } string OldValue; switch (field) { case CustomerDetailsUpdatableField.FirstName: OldValue = jrt.CurrentCustomer.FirstName; jrt.CurrentCustomer.FirstName = cFirstName.Text; UpdateCustomerInfo(field, OldValue, jrt.CurrentCustomer.FirstName, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); _tabs._scView.Log(String.Format("Database updated for Customer #{0} : Set First Name to {1}", jrt.CurrentCustomer.CustomerNumber, cFirstName.Text)); break; case CustomerDetailsUpdatableField.LastName: OldValue = jrt.CurrentCustomer.LastName; jrt.CurrentCustomer.LastName = cLastName.Text; UpdateCustomerInfo(field, OldValue, jrt.CurrentCustomer.LastName, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); _tabs._scView.Log(String.Format("Database updated for Customer #{0} : Set Last Name to {1}", jrt.CurrentCustomer.CustomerNumber, cLastName.Text)); break; case CustomerDetailsUpdatableField.Address: OldValue = jrt.CurrentCustomer.Address; jrt.CurrentCustomer.Address = cAddress.Text; UpdateCustomerInfo(field, OldValue, jrt.CurrentCustomer.Address, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); _tabs._scView.Log(String.Format("Database updated for Customer #{0} : Set Address to {1}", jrt.CurrentCustomer.CustomerNumber, cAddress.Text)); break; case CustomerDetailsUpdatableField.Suburb: OldValue = jrt.CurrentCustomer.Suburb; jrt.CurrentCustomer.Suburb = cSuburb.Text; UpdateCustomerInfo(field, OldValue, jrt.CurrentCustomer.Suburb, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); _tabs._scView.Log(String.Format("Database updated for Customer #{0} : Set Suburb to {1}", jrt.CurrentCustomer.CustomerNumber, cSuburb.Text)); break; case CustomerDetailsUpdatableField.Phone: OldValue = jrt.CurrentCustomer.PhoneNumber; string newPhoneValue = String.Empty; for (int i = 0; i < cPhoneNumber.Text.Length; i++) { char c = cPhoneNumber.Text[i]; if (char.IsDigit(c) || char.IsWhiteSpace(c)) { newPhoneValue += c; } } jrt.CurrentCustomer.PhoneNumber = newPhoneValue; cPhoneNumber.Text = newPhoneValue; UpdateCustomerInfo(field, OldValue, jrt.CurrentCustomer.PhoneNumber, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); _tabs._scView.Log(String.Format("Database updated for Customer #{0} : Set Phone Number to {1}", jrt.CurrentCustomer.CustomerNumber, cPhoneNumber.Text)); break; case CustomerDetailsUpdatableField.MobilePhone: OldValue = jrt.CurrentCustomer.MobileNumber; string newMobileValue = String.Empty; for (int i = 0; i < cMobileNumber.Text.Length; i++) { char c = cMobileNumber.Text[i]; if (char.IsDigit(c) || char.IsWhiteSpace(c)) { newMobileValue += c; } } jrt.CurrentCustomer.MobileNumber = newMobileValue; cMobileNumber.Text = newMobileValue; UpdateCustomerInfo(field, OldValue, jrt.CurrentCustomer.MobileNumber, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); _tabs._scView.Log(String.Format("Database updated for Customer #{0} : Set Mobile Phone Number to {1}", jrt.CurrentCustomer.CustomerNumber, cMobileNumber.Text)); break; case CustomerDetailsUpdatableField.FallbackContact: OldValue = jrt.CurrentCustomer.FallbackContact; jrt.CurrentCustomer.FallbackContact = cFallbackContact.Text; UpdateCustomerInfo(field, OldValue, jrt.CurrentCustomer.FallbackContact, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); _tabs._scView.Log(String.Format("Database updated for Customer #{0} : Set Fallback Contact to {1}", jrt.CurrentCustomer.CustomerNumber, cFallbackContact.Text)); break; case CustomerDetailsUpdatableField.FallbackPhone: OldValue = jrt.CurrentCustomer.FallbackPhoneNumber; string newFallbackPhoneValue = String.Empty; for (int i = 0; i < cFallbackPhone.Text.Length; i++) { char c = cFallbackPhone.Text[i]; if (char.IsDigit(c) || char.IsWhiteSpace(c)) { newFallbackPhoneValue += c; } } jrt.CurrentCustomer.FallbackPhoneNumber = newFallbackPhoneValue; cFallbackPhone.Text = newFallbackPhoneValue; UpdateCustomerInfo(field, OldValue, jrt.CurrentCustomer.FallbackPhoneNumber, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); _tabs._scView.Log(String.Format("Database updated for Customer #{0} : Set Fallback Phone to {1}", jrt.CurrentCustomer.CustomerNumber, cFallbackPhone.Text)); break; case CustomerDetailsUpdatableField.CompanyName: OldValue = jrt.CurrentCustomer.CompanyName; jrt.CurrentCustomer.CompanyName = cCompanyName.Text; UpdateCustomerInfo(field, OldValue, jrt.CurrentCustomer.CompanyName, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); _tabs._scView.Log(String.Format("Database updated for Customer #{0} : Set Company Name to {1}", jrt.CurrentCustomer.CustomerNumber, cCompanyName.Text)); break; case CustomerDetailsUpdatableField.SpecialComments: OldValue = (jrt.CurrentJob.HasParent()) ? jrt.FindParentJob(jrt.CurrentJob).JobSpecialInstructions : jrt.CurrentJob.JobSpecialInstructions; if (jrt.CurrentJob.HasParent()) { jrt.FindParentJob(jrt.CurrentJob).JobSpecialInstructions = cSpecialInstructions.Text; UpdateCustomerInfo(field, OldValue, this.cSpecialInstructions.Text, jrt.CurrentCustomer.CustomerNumber, jrt.FindParentJob(jrt.CurrentJob).JobBookingNumber); } else { jrt.CurrentJob.JobSpecialInstructions = cSpecialInstructions.Text; UpdateCustomerInfo(field, OldValue, this.cSpecialInstructions.Text, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); } break; case CustomerDetailsUpdatableField.AttentionReason: OldValue = (jrt.CurrentJob.HasParent()) ? jrt.FindParentJob(jrt.CurrentJob).AttentionReason : jrt.CurrentJob.AttentionReason; if (jrt.CurrentJob.HasParent()) { jrt.FindParentJob(jrt.CurrentJob).AttentionReason = cAttentionReason.Text; UpdateCustomerInfo(field, OldValue, this.cAttentionReason.Text, jrt.CurrentCustomer.CustomerNumber, jrt.FindParentJob(jrt.CurrentJob).JobBookingNumber); } else { jrt.CurrentJob.AttentionReason = cAttentionReason.Text; UpdateCustomerInfo(field, OldValue, this.cAttentionReason.Text, jrt.CurrentCustomer.CustomerNumber, jrt.CurrentJob.JobBookingNumber); } break; default: if (_tabs._scView != null) { _tabs._scView.Log(String.Format("HandleEditingDidEnd: Attempt to update unknown field: Tag {0}", field)); } break; } // jrt.CurrentJob = null; if (jrt.HighlightedMode) { jrt.HighlightedMode = false; } if (jrt.LastSelectedRowPath != null) { jrt.TableView.ReloadRows(new NSIndexPath[] { jrt.LastSelectedRowPath }, UITableViewRowAnimation.Automatic); for (int i = 0; i < jrt.TableView.NumberOfRowsInSection(0); i++) { if (jrt.Customers[i].HighLighted) { jrt.TableView.ReloadRows(new NSIndexPath[] { NSIndexPath.FromRowSection(i, 0) }, UITableViewRowAnimation.Automatic); } } if (jrt.TableView.NumberOfRowsInSection(1) > 0) { for (int i = 0; i < jrt.TableView.NumberOfRowsInSection(1); i++) { if (jrt.UserAddedCustomers[i].HighLighted) { jrt.TableView.ReloadRows(new NSIndexPath[] { NSIndexPath.FromRowSection(i, 1) }, UITableViewRowAnimation.Automatic); } } } // jrt.TableView.CellAt (jrt.LastSelectedRowPath).Selected = true ; jrt.TableView.SelectRow(jrt.LastSelectedRowPath, true, UITableViewScrollPosition.None); } } }