// Method: Get List
public List<lwdom_Rates_Model> Get_Rates_List()
{
// 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, chemcode is the Key
string sqlStatement = "SELECT ID, RateID, Category, Code, RateYear, Description, " +
"RetailRate, CostRate, ActiveStatus " +
"FROM lwdom_Rates " +
"ORDER BY RateYear, Category";
// create List
List<lwdom_Rates_Model> lwrMod_List = new List<lwdom_Rates_Model>();
try
{
connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand(sqlStatement, connection);
SqlDataReader reader = command.ExecuteReader();
// read table, populate model
while (reader.Read())
{
lwdom_Rates_Model lwrMod = new lwdom_Rates_Model();
lwrMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
lwrMod.RateID = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
lwrMod.Category = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
lwrMod.Code = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
lwrMod.RateYear = (reader[4] != DBNull.Value) ? (int)reader[4] : 0;
lwrMod.Description = (reader[5] != DBNull.Value) ? (string)reader[5] : "";
lwrMod.RetailRate = (reader[6] != DBNull.Value) ? (decimal)reader[6] : 0;
lwrMod.CostRate = (reader[7] != DBNull.Value) ? (decimal)reader[7] : 0;
lwrMod.ActiveStatus = (reader[8] != DBNull.Value) ? (string)reader[8] : "";
// add Equipment to List
lwrMod_List.Add(lwrMod);
}
// close reader, close connection
reader.Close();
connection.Close();
strMsg = "List Complete.";
}
catch (Exception errMsg)
{
strMsg = errMsg.Message.ToString();
}
// return List
return lwrMod_List;
}
// Method: get record data based on id
public lwdom_Rates_Model Get_SpecificRates_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, RateID, Category, Code, RateYear, Description, " +
"RetailRate, CostRate, ActiveStatus " +
"FROM lwdom_Rates " +
"WHERE ID=@ID";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
// Create object base on LW Rates Model (lwrMod)
lwdom_Rates_Model lwrMod = new lwdom_Rates_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())
{
lwrMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
lwrMod.RateID = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
lwrMod.Category = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
lwrMod.Code = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
lwrMod.RateYear = (reader[4] != DBNull.Value) ? (int)reader[4] : 0;
lwrMod.Description = (reader[5] != DBNull.Value) ? (string)reader[5] : "";
lwrMod.RetailRate = (reader[6] != DBNull.Value) ? (decimal)reader[6] : 0;
lwrMod.CostRate = (reader[7] != DBNull.Value) ? (decimal)reader[7] : 0;
lwrMod.ActiveStatus = (reader[8] != DBNull.Value) ? (string)reader[8] : "";
}
// the close
reader.Close();
}
catch (Exception e)
{
strMsg = e.Message.ToString();
}
// the close
connection.Close();
// return the Model
return lwrMod;
}
// Reset lwdom_Rates Model
private lwdom_Rates_Model Reset_Rates_Model(lwdom_Rates_Model lwrMod)
{
// reset the model
lwrMod.ID = 0;
lwrMod.RateID = "";
lwrMod.Category = "";
lwrMod.Code = "";
lwrMod.RateYear = 0;
lwrMod.Description = "";
lwrMod.RetailRate = 0;
lwrMod.CostRate = 0;
lwrMod.ActiveStatus = "";
// return the model
return lwrMod;
}
// ADD
public string Add_Rates_Rec(lwdom_Rates_Model lwrMod)
{
// 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 lwdom_Rates (RateID, Category, Code, RateYear, " +
"Description, RetailRate, CostRate, ActiveStatus) " +
"VALUES (@RateID, @Category, @Code, @RateYear, " +
"@Description, @RetailRate, @CostRate, @ActiveStatus)";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
connection.Open();
// Adding parameters for the Insert Command
command.Parameters.AddWithValue("@RateID", lwrMod.RateID);
command.Parameters.AddWithValue("@Category", lwrMod.Category);
command.Parameters.AddWithValue("@Code", lwrMod.Code);
command.Parameters.AddWithValue("@RateYear", lwrMod.RateYear);
command.Parameters.AddWithValue("@Description", lwrMod.Description);
command.Parameters.AddWithValue("@RetailRate", lwrMod.RetailRate);
command.Parameters.AddWithValue("@CostRate", lwrMod.CostRate);
command.Parameters.AddWithValue("@ActiveStatus", lwrMod.ActiveStatus);
//lwdcMod.activeStatus = true;
//command.Parameters.AddWithValue("@activeStatus", lwdcMod.activeStatus);
command.ExecuteNonQuery();
strMsg = "Record was added.";
}
catch (Exception e)
{
strMsg = e.Message.ToString();
}
connection.Close();
return strMsg;
}
// Method: update record
public string Update_Rates_rec(lwdom_Rates_Model lwrMod)
{
// Method: update selected Hours Master recore
// 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 lwdom_Rates " +
"SET RateID=@RateID, " +
"Category=@Category, " +
"Code=@Code, " +
"RateYear=@RateYear, " +
"Description=@Description, " +
"RetailRate=@RetailRate, " +
"CostRate=@CostRate, " +
"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("@RateID", lwrMod.RateID);
command.Parameters.AddWithValue("@Category", lwrMod.Category);
command.Parameters.AddWithValue("@Code", lwrMod.Code);
command.Parameters.AddWithValue("@RateYear", lwrMod.RateYear);
command.Parameters.AddWithValue("@Description", lwrMod.Description);
command.Parameters.AddWithValue("@RetailRate", lwrMod.RetailRate);
command.Parameters.AddWithValue("@CostRate", lwrMod.CostRate);
command.Parameters.AddWithValue("@ActiveStatus", lwrMod.ActiveStatus);
//
command.Parameters.AddWithValue("@ID", lwrMod.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;
}
/// <summary>
/// Create a copy of this instance
/// </summary>
/// <param name="original"></param>
/// <returns></returns>
public lwdom_Rates_Model Copy()
{
// create the model copy
lwdom_Rates_Model modelCopy = new lwdom_Rates_Model();
// copy the data
modelCopy.ID = ID;
modelCopy.RateID = RateID;
modelCopy.Category = Category;
modelCopy.Code = Code;
modelCopy.RateYear = RateYear;
modelCopy.Description = Description;
modelCopy.RetailRate = RetailRate;
modelCopy.CostRate = CostRate;
modelCopy.ActiveStatus = ActiveStatus;
// return the copy
return modelCopy;
}
public void Update_Rates_Async(lwdom_Rates_Model rMod)
{
// Async thread
Task.Run(() =>
{
return RWkr.Update_Rates_rec(rMod);
})
.ContinueWith(task => UpdateResult = task.Result, context);
}
public void Add_Rate_Async(lwdom_Rates_Model rMod)
{
// Aysnc thread
Task.Run(() =>
{
return RWkr.Add_Rates_Rec(rMod);
})
.ContinueWith(task => AddResult = task.Result, context);
}
// ADD Record
private void buttonADD_Click(object sender, RoutedEventArgs e)
{
lwdom_Rates_Model rMod = new lwdom_Rates_Model();
// load the model
rMod = (lwdom_Rates_Model)this.DataContext;
// async ADD
viewModel.Add_Rate_Async(rMod);
// reset fields
ResetDisplayFields();
// use inital button configuration
InitialButtonConfiguration();
}
// ADD Enable - Blank display fields
private void buttonADD_EnableBlank_Click(object sender, RoutedEventArgs e)
{
// set the data context
// 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 lwdom_Rates_Model();
ResetDisplayFields();
ADDButtonConfiguration();
}