// Reset ClientContact Model
private lw_ClientContacts_Model Reset_ClientContact_Mod(lw_ClientContacts_Model ccMod)
{
// reset the model
ccMod.ID = 0;
ccMod.AccNum = 0;
ccMod.LocationID = "";
ccMod.ContactTitle = "";
ccMod.ContactFN = "";
ccMod.ContactLN = "";
ccMod.OffPhone = "";
ccMod.OffPhoneExt = "";
ccMod.CellPhone = "";
ccMod.FaxNum = "";
ccMod.OthPhone = "";
ccMod.Email = "";
ccMod.Comments = "";
ccMod.LocID = 0;
ccMod.ActiveStatus = "";
// return the model
return ccMod;
}
// Method: Get List
public List<lw_ClientContacts_Model> Get_ClientContacts_byLIKELastName_List(string strStatus, string strLN)
{
// building the connection string
// get the provider, activeStatus database name, and path
connectionString = PSWkr.G_SQLDatabaseConnectionString;
string strMsg = "";
// create needed objects
SqlConnection connection;
// building sql command, ContactID is the Key
string sqlStatement = "SELECT ID, AccNum, LocationID, ContactTitle, ContactFN, ContactLN, " +
"OffPhone, OffPhoneExt, CellPhone, FaxNum, OthPhone, Email, Comments, LocID, ActiveStatus " +
"FROM lw_ClientContacts " +
// "WHERE ActiveStatus=@ActiveStatus AND ContactLN LIKE @ContactLN " +
"WHERE ContactLN LIKE @ContactLN " +
"ORDER BY ContactLN, ContactFN";
// create List
List<lw_ClientContacts_Model> ccMod_List = new List<lw_ClientContacts_Model>();
try
{
connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand(sqlStatement, connection);
// add command
// command.Parameters.AddWithValue("@ActiveStatus", strStatus);
command.Parameters.AddWithValue("@ContactLN", strLN + '%');
SqlDataReader reader = command.ExecuteReader();
// read table, populate model
while (reader.Read())
{
lw_ClientContacts_Model ccMod = new lw_ClientContacts_Model();
ccMod.ID = (reader[0] != DBNull.Value) ? Convert.ToInt64(reader[0]) : 0;
ccMod.AccNum = (reader[1] != DBNull.Value) ? Convert.ToInt64(reader[1]) : 0;
ccMod.LocationID = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
ccMod.ContactTitle = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
ccMod.ContactFN = (reader[4] != DBNull.Value) ? (string)reader[4] : "";
ccMod.ContactLN = (reader[5] != DBNull.Value) ? (string)reader[5] : "";
ccMod.OffPhone = (reader[6] != DBNull.Value) ? (string)reader[6] : "";
ccMod.OffPhoneExt = (reader[7] != DBNull.Value) ? (string)reader[7] : "";
ccMod.CellPhone = (reader[8] != DBNull.Value) ? (string)reader[8] : "";
ccMod.FaxNum = (reader[9] != DBNull.Value) ? (string)reader[9] : "";
ccMod.OthPhone = (reader[10] != DBNull.Value) ? (string)reader[10] : "";
ccMod.Email = (reader[11] != DBNull.Value) ? (string)reader[11] : "";
ccMod.Comments = (reader[12] != DBNull.Value) ? (string)reader[12] : "";
ccMod.LocID = (reader[13] != DBNull.Value) ? Convert.ToInt64(reader[13]) : 0;
ccMod.ActiveStatus = (reader[14] != DBNull.Value) ? (string)reader[14] : "";
// add Equipment to List
ccMod_List.Add(ccMod);
}
// close reader, close connection
reader.Close();
connection.Close();
strMsg = "done";
}
catch (Exception errMsg)
{
strMsg = errMsg.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Method: Get_ClientContacts_byLIKELastName_List", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// return List
return ccMod_List;
}
// ADD
public string Add_ClientContact_Rec(lw_ClientContacts_Model ccMod)
{
// Method: Create new record
// update the database
string strMsg = "";
// get the connection string
connectionString = PSWkr.G_SQLDatabaseConnectionString;
// create connection object
SqlConnection connection = new SqlConnection(connectionString);
// building sql command
string sqlStatement = "INSERT INTO lw_ClientContacts (AccNum, LocationID, ContactTitle, ContactFN, ContactLN, " +
"OffPhone, OffPhoneExt, CellPhone, FaxNum, OthPhone, Email, Comments, LocID, ActiveStatus) " +
"VALUES (@AccNum, @LocationID, @ContactTitle, @ContactFN, @ContactLN, " +
"@OffPhone, @OffPhoneExt, @CellPhone, @FaxNum, @OthPhone, @Email, @Comments, @LocID, @ActiveStatus)";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
connection.Open();
// Adding parameters for the Insert Command
command.Parameters.AddWithValue("@AccNum", ccMod.AccNum);
command.Parameters.AddWithValue("@LocationID", ccMod.LocationID);
command.Parameters.AddWithValue("@ContactTitle", ccMod.ContactTitle);
command.Parameters.AddWithValue("@ContactFN", ccMod.ContactFN);
command.Parameters.AddWithValue("@ContactLN", ccMod.ContactLN);
command.Parameters.AddWithValue("@OffPhone", ccMod.OffPhone);
command.Parameters.AddWithValue("@OffPhoneExt", ccMod.OffPhoneExt);
command.Parameters.AddWithValue("@CellPhone", ccMod.CellPhone);
command.Parameters.AddWithValue("@FaxNum", ccMod.FaxNum);
command.Parameters.AddWithValue("@OthPhone", ccMod.OthPhone);
command.Parameters.AddWithValue("@Email", ccMod.Email);
command.Parameters.AddWithValue("@Comments", ccMod.Comments);
command.Parameters.AddWithValue("@LocID", ccMod.LocID);
command.Parameters.AddWithValue("@ActiveStatus", ccMod.ActiveStatus);
command.ExecuteNonQuery();
strMsg = "Record was added.";
}
catch (Exception e)
{
strMsg = e.Message.ToString();
}
connection.Close();
return strMsg;
}
// Method: update record
public string Update_ClientContact_rec(lw_ClientContacts_Model ccMod)
{
// Method: update selected Client record
// update the database
string strMsg = "";
// get the connection string
connectionString = PSWkr.G_SQLDatabaseConnectionString;
// create connection object
SqlConnection connection = new SqlConnection(connectionString);
// building sql command
string sqlStatement = "UPDATE lw_ClientContacts " +
"SET AccNum=@AccNum, " +
"LocationID=@LocationID, " +
"ContactTitle=@ContactTitle, " +
"ContactFN=@ContactFN, " +
"ContactLN=@ContactLN, " +
"OffPhone=@OffPhone, " +
"OffPhoneExt=@OffPhoneExt, " +
"CellPhone=@CellPhone, " +
"FaxNum=@FaxNum, " +
"OthPhone=@OthPhone, " +
"Email=@Email, " +
"Comments=@Comments, " +
"LocID=@LocID, " +
"ActiveStatus=@ActiveStatus " +
"WHERE ID=@ID";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
// update the database
connection.Open();
// use of command.parameters... prevents sql injection
command.Parameters.AddWithValue("@AccNum", ccMod.AccNum);
command.Parameters.AddWithValue("@LocationID", ccMod.LocationID);
command.Parameters.AddWithValue("@ContactTitle", ccMod.ContactTitle);
command.Parameters.AddWithValue("@ContactFN", ccMod.ContactFN);
command.Parameters.AddWithValue("@ContactLN", ccMod.ContactLN);
command.Parameters.AddWithValue("@OffPhone", ccMod.OffPhone);
command.Parameters.AddWithValue("@OffPhoneExt", ccMod.OffPhoneExt);
command.Parameters.AddWithValue("@CellPhone", ccMod.CellPhone);
command.Parameters.AddWithValue("@FaxNum", ccMod.FaxNum);
command.Parameters.AddWithValue("@OthPhone", ccMod.OthPhone);
command.Parameters.AddWithValue("@Email", ccMod.Email);
command.Parameters.AddWithValue("@Comments", ccMod.Comments);
command.Parameters.AddWithValue("@LocID", ccMod.LocID);
command.Parameters.AddWithValue("@ActiveStatus", ccMod.ActiveStatus);
//
command.Parameters.AddWithValue("@ID", ccMod.ID); // must be in the order of the sqlstatement
command.ExecuteNonQuery();
strMsg = "Record was updated.";
}
catch (Exception e)
{
strMsg = e.Message.ToString();
}
connection.Close();
return strMsg;
}
// Method: get record data based on id
public lw_ClientContacts_Model Get_SpecificClientContact_Record(int recID)
{
string strMsg = "";
// get the connection string
connectionString = PSWkr.G_SQLDatabaseConnectionString;
// create connection object
SqlConnection connection = new SqlConnection(connectionString);
// building sql command
string sqlStatement = "SELECT ID, AccNum, LocationID, ContactTitle, ContactFN, ContactLN, " +
"OffPhone, OffPhoneExt, CellPhone, FaxNum, OthPhone, Email, Comments, LocID, ActiveStatus " +
"FROM lw_ClientContacts " +
"WHERE ID=@ID";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
// Create object base on Equip Model (eMod)
lw_ClientContacts_Model ccMod = new lw_ClientContacts_Model();
try
{
// open the connection
connection.Open();
command.Parameters.AddWithValue("@ID", recID);
// execute the reader
SqlDataReader reader = command.ExecuteReader();
// populate the invoice list
if (reader.Read())
{
ccMod.ID = (reader[0] != DBNull.Value) ? (int)reader[0] : 0;
ccMod.AccNum = (reader[1] != DBNull.Value) ? (int)reader[1] : 0;
ccMod.LocationID = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
ccMod.ContactTitle = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
ccMod.ContactFN = (reader[4] != DBNull.Value) ? (string)reader[4] : "";
ccMod.ContactLN = (reader[5] != DBNull.Value) ? (string)reader[5] : "";
ccMod.OffPhone = (reader[6] != DBNull.Value) ? (string)reader[6] : "";
ccMod.OffPhoneExt = (reader[7] != DBNull.Value) ? (string)reader[7] : "";
ccMod.CellPhone = (reader[8] != DBNull.Value) ? (string)reader[8] : "";
ccMod.FaxNum = (reader[9] != DBNull.Value) ? (string)reader[9] : "";
ccMod.OthPhone = (reader[10] != DBNull.Value) ? (string)reader[10] : "";
ccMod.Email = (reader[11] != DBNull.Value) ? (string)reader[11] : "";
ccMod.Comments = (reader[12] != DBNull.Value) ? (string)reader[12] : "";
ccMod.LocID = (reader[13] != DBNull.Value) ? (int)reader[13] : 0;
ccMod.ActiveStatus = (reader[14] != DBNull.Value) ? (string)reader[14] : "";
}
// the close
reader.Close();
}
catch (Exception e)
{
strMsg = e.Message.ToString();
}
// the close
connection.Close();
// return the Model
return ccMod;
}
public void Update_Contact_Async(lw_ClientContacts_Model ccMod)
{
// Async thread
Task.Run(() =>
{
return CCWkr.Update_ClientContact_rec(ccMod);
})
.ContinueWith(task => UpdateResult = task.Result, context);
}
public void Add_Contact_Async(lw_ClientContacts_Model ccMod)
{
// Aysnc thread
Task.Run(() =>
{
return CCWkr.Add_ClientContact_Rec(ccMod);
})
.ContinueWith(task => AddResult = task.Result, context);
}
// ADD Client Contact record
private void buttonADD_Click(object sender, RoutedEventArgs e)
{
lw_ClientContacts_Model ccMod = new lw_ClientContacts_Model();
// populate model
ccMod = (lw_ClientContacts_Model)DataContext;
// async ADD
ccVM.Add_Contact_Async(ccMod);
// reset and initialize button configuration
ResetDisplayFields();
InitialButtonConfiguration();
}
// Enable Add no copy
private void buttonADD_Enable_Blank_Click(object sender, RoutedEventArgs e)
{
// Enable Add Button has been clicked, so release any data
// context reference
DataContext = null;
// Create a new model object and bind
// it to the dataContext
DataContext = new lw_ClientContacts_Model();
// set the UI
ResetDisplayFields();
ADDButtonConfiguration();
}
