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);
}
}
}
