// Reset Bid Model
private lw_Bids_Model Reset_ClientContact_Mod(lw_Bids_Model bMod)
{
DateTime? nullDate = null;
// reset the model
bMod.ID = 0;
bMod.BidID = 0;
bMod.ClientLocID = "";
bMod.BidAmtRetail = 0;
bMod.BidAmtCost = 0;
bMod.Area = 0;
bMod.BidSent = nullDate;
bMod.Description = "";
bMod.BidWon = "";
bMod.Narrative = "";
bMod.AttnName = "";
bMod.Narrative2 = "";
bMod.TwoColumns = "";
bMod.descBidAmtRetail = "";
bMod.descBidAmtCost = "";
bMod.showLocName = "";
bMod.AccNum = 0;
bMod.ClientName = "";
// return the model
return bMod;
}
// Method: Get List by Bid Sent Date
public List<lw_Bids_Model> Get_Bids_ByBidSentDate_List(string strDate)
{
// building the connection string
// get the provider, activeStatus database name, and path
connectionString = PSWkr.G_SQLDatabaseConnectionString;
string strMsg = "";
bool result = false;
DateTime DT;
DateTime? nullDate = null;
// get the compare date
result = DateTime.TryParse(strDate, out DT);
// create needed objects
SqlConnection connection;
// building sql command, ContactID is the Key
string sqlStatement = "SELECT ID, BidID, ClientLocID, BidAmtRetail, BidAmtCost, Area, BidSent, " +
"Description, BidWon, Narrative, AttnName, Narrative2, TwoColumns, descBidAmtRetail, " +
"descBidAmtCost, showLocName, AccNum, ClientName " +
"FROM lw_Bids " +
"WHERE BidSent>=@BidSent " +
"ORDER BY BidSent";
// create List
List<lw_Bids_Model> bMod_List = new List<lw_Bids_Model>();
try
{
connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand(sqlStatement, connection);
command.Parameters.AddWithValue("@BidSent", DT);
SqlDataReader reader = command.ExecuteReader();
// read table, populate model
while (reader.Read())
{
lw_Bids_Model bMod = new lw_Bids_Model();
bMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
bMod.BidID = (reader[1] != DBNull.Value) ? (Int64)reader[1] : 0;
bMod.ClientLocID = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
bMod.BidAmtRetail = (reader[3] != DBNull.Value) ? (decimal)reader[3] : 0;
bMod.BidAmtCost = (reader[4] != DBNull.Value) ? (decimal)reader[4] : 0;
bMod.Area = (reader[5] != DBNull.Value) ? (Int64)reader[5] : 0;
//
result = DateTime.TryParse(reader[6].ToString(), out DT);
bMod.BidSent = (result) ? DT : nullDate;
//
bMod.Description = (reader[7] != DBNull.Value) ? (string)reader[7] : "";
bMod.BidWon = (reader[8] != DBNull.Value) ? (string)reader[8] : "";
bMod.Narrative = (reader[9] != DBNull.Value) ? (string)reader[9] : "";
bMod.AttnName = (reader[10] != DBNull.Value) ? (string)reader[10] : "";
bMod.Narrative2 = (reader[11] != DBNull.Value) ? Convert.ToString(reader[11]) : "";
bMod.TwoColumns = (reader[12] != DBNull.Value) ? (string)reader[12] : "";
bMod.descBidAmtRetail = (reader[13] != DBNull.Value) ? (string)reader[13] : "";
bMod.descBidAmtCost = (reader[14] != DBNull.Value) ? (string)reader[14] : "";
bMod.showLocName = (reader[15] != DBNull.Value) ? (string)reader[15] : "";
bMod.AccNum = (reader[16] != DBNull.Value) ? (Int64)reader[16] : 0;
bMod.ClientName = (reader[17] != DBNull.Value) ? (string)reader[17] : "";
// add to List
bMod_List.Add(bMod);
}
// close reader, close connection
reader.Close();
connection.Close();
strMsg = "BidsList";
}
catch (Exception errMsg)
{
strMsg = errMsg.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Get_Bids_ByBidSentDate_List", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// return List
return bMod_List;
}
// ADD
public string Add_Bids_Rec(lw_Bids_Model bMod)
{
// 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_Bids (BidID, ClientLocID, BidAmtRetail, BidAmtCost, Area, BidSent, Description, " +
"BidWon, Narrative, AttnName, Narrative2, TwoColumns, descBidAmtRetail, descBidAmtCost, " +
"showLocName, AccNum, ClientName) " +
"VALUES (@BidID, @ClientLocID, @BidAmtRetail, @BidAmtCost, @Area, @BidSent, @Description, " +
"@BidWon, @Narrative, @AttnName, @Narrative2, @TwoColumns, @descBidAmtRetail, @descBidAmtCost, " +
"@showLocName, @AccNum, @ClientName)";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
connection.Open();
// Adding parameters for the Insert Command
command.Parameters.AddWithValue("@BidID", bMod.BidID);
command.Parameters.AddWithValue("@ClientLocID", bMod.ClientLocID);
command.Parameters.AddWithValue("@BidAmtRetail", bMod.BidAmtRetail);
command.Parameters.AddWithValue("@BidAmtCost", bMod.BidAmtCost);
command.Parameters.AddWithValue("@Area", bMod.Area);
command.Parameters.AddWithValue("@BidSent", bMod.BidSent);
command.Parameters.AddWithValue("@Description", bMod.Description);
command.Parameters.AddWithValue("@BidWon", bMod.BidWon);
command.Parameters.AddWithValue("@Narrative", bMod.Narrative);
command.Parameters.AddWithValue("@AttnName", bMod.AttnName);
command.Parameters.AddWithValue("@Narrative2", bMod.Narrative2);
command.Parameters.AddWithValue("@TwoColumns", bMod.TwoColumns);
command.Parameters.AddWithValue("@descBidAmtRetail", bMod.descBidAmtRetail);
command.Parameters.AddWithValue("@descBidAmtCost", bMod.descBidAmtCost);
command.Parameters.AddWithValue("@showLocName", bMod.showLocName);
command.Parameters.AddWithValue("@AccNum", bMod.AccNum);
command.Parameters.AddWithValue("@ClientName", bMod.ClientName);
command.ExecuteNonQuery();
strMsg = "Record was added.";
// update Application Value: BidsID by 1
UpdateNext_BidsID();
}
catch (Exception e)
{
strMsg = e.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Add_Bids_Rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
connection.Close();
return strMsg;
}
// Method: update record
public string Update_Bid_rec(lw_Bids_Model bMod)
{
// 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_Bids " +
"SET BidID=@BidID, " +
"ClientLocID=@ClientLocID, " +
"BidAmtRetail=@BidAmtRetail, " +
"BidAmtCost=@BidAmtCost, " +
"Area=@Area, " +
"BidSent=@BidSent, " +
"Description=@Description, " +
"BidWon=@BidWon, " +
"Narrative=@Narrative, " +
"AttnName=@AttnName, " +
"Narrative2=@Narrative2, " +
"TwoColumns=@TwoColumns, " +
"descBidAmtRetail=@descBidAmtRetail, " +
"descBidAmtCost=@descBidAmtCost " +
"showLocName=@showLocName, " +
"AccNum=@AccNum, " +
"ClientName=@ClientName " +
"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("@BidID", bMod.BidID);
command.Parameters.AddWithValue("@ClientLocID", bMod.ClientLocID);
command.Parameters.AddWithValue("@BidAmtRetail", bMod.BidAmtRetail);
command.Parameters.AddWithValue("@BidAmtCost", bMod.BidAmtCost);
command.Parameters.AddWithValue("@Area", bMod.Area);
command.Parameters.AddWithValue("@BidSent", bMod.BidSent);
command.Parameters.AddWithValue("@Description", bMod.Description);
command.Parameters.AddWithValue("@BidWon", bMod.BidWon);
command.Parameters.AddWithValue("@Narrative", bMod.Narrative);
command.Parameters.AddWithValue("@AttnName", bMod.AttnName);
command.Parameters.AddWithValue("@Narrative2", bMod.Narrative2);
command.Parameters.AddWithValue("@TwoColumns", bMod.TwoColumns);
command.Parameters.AddWithValue("@descBidAmtRetail", bMod.descBidAmtRetail);
command.Parameters.AddWithValue("@descBidAmtCost", bMod.descBidAmtCost);
command.Parameters.AddWithValue("@showLocName", bMod.showLocName);
command.Parameters.AddWithValue("@AccNum", bMod.AccNum);
command.Parameters.AddWithValue("@ClientName", bMod.ClientName);
//
command.Parameters.AddWithValue("@ID", bMod.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_Bids_Model Get_Specific_Bids_Record(int recID)
{
string strMsg = "";
bool result = false;
DateTime DT;
DateTime? nullDate = null;
// get the connection string
connectionString = PSWkr.G_SQLDatabaseConnectionString;
// create connection object
SqlConnection connection = new SqlConnection(connectionString);
// building sql command
string sqlStatement = "SELECT ID, BidID, ClientLocID, BidAmtRetail, BidAmtCost, Area, BidSent, " +
"Description, BidWon, Narrative, AttnName, Narrative2, TwoColumns, descBidAmtRetail, " +
"descBidAmtCost, showLocName, AccNum, ClientName " +
"FROM lw_Bids " +
"WHERE ID=@ID";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
// Create object base on Bids Model (bMod)
lw_Bids_Model bMod = new lw_Bids_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())
{
bMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
bMod.BidID = (reader[1] != DBNull.Value) ? (Int64)reader[1] : 0;
bMod.ClientLocID = (reader[2] != DBNull.Value) ? (string)reader[2] : "";
bMod.BidAmtRetail = (reader[3] != DBNull.Value) ? (decimal)reader[3] : 0;
bMod.BidAmtCost = (reader[4] != DBNull.Value) ? (decimal)reader[4] : 0;
bMod.Area = (reader[5] != DBNull.Value) ? (Int64)reader[5] : 0;
//
result = DateTime.TryParse(reader[6].ToString(), out DT);
bMod.BidSent = (result) ? DT : nullDate;
//
bMod.Description = (reader[7] != DBNull.Value) ? (string)reader[7] : "";
bMod.BidWon = (reader[8] != DBNull.Value) ? (string)reader[8] : "";
bMod.Narrative = (reader[9] != DBNull.Value) ? (string)reader[9] : "";
bMod.AttnName = (reader[10] != DBNull.Value) ? (string)reader[10] : "";
bMod.Narrative2 = (reader[11] != DBNull.Value) ? Convert.ToString(reader[11]) : "";
bMod.TwoColumns = (reader[12] != DBNull.Value) ? (string)reader[12] : "";
bMod.descBidAmtRetail = (reader[13] != DBNull.Value) ? (string)reader[13] : "";
bMod.descBidAmtCost = (reader[14] != DBNull.Value) ? (string)reader[14] : "";
bMod.showLocName = (reader[15] != DBNull.Value) ? (string)reader[15] : "";
bMod.AccNum = (reader[16] != DBNull.Value) ? (Int64)reader[16] : 0;
bMod.ClientName = (reader[17] != DBNull.Value) ? (string)reader[17] : "";
}
// the close
reader.Close();
}
catch (Exception e)
{
strMsg = e.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Get_Specific_Bids_Record", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// the close
connection.Close();
// return the Model
return bMod;
}
// Async Add Method
public void Add_Bids_Async(lw_Bids_Model bMod)
{
// async thread
Task.Run(() =>
{
// add bids record
lw_Bids_Worker BWkr = new lw_Bids_Worker();
return BWkr.Add_Bids_Rec(bMod);
})
.ContinueWith(task => AddResult = task.Result, context);
}
// Aysnc Update Method
public void Update_Bids_Async(lw_Bids_Model bMod)
{
// aysnc thread
Task.Run(() =>
{
lw_Bids_Worker BWkr = new lw_Bids_Worker();
return BWkr.Update_Bid_rec(bMod);
})
.ContinueWith(task => UpdateResult = task.Result, context);
}
// ADD Enable - Blank
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_Bids_Model();
// set the UI
ResetDisplayFields();
ADDButtonConfiguration();
}
// load the Model
// load model is used on an ADD
private lw_Bids_Model Load_Model()
{
lw_Bids_Model bMod = new lw_Bids_Model();
bool result = false;
Int64 intNum = 0;
decimal decNum = 0;
DateTime DT;
DateTime? nullDate = null;
// formatting for Richtext
TextRange tr = new TextRange(rtbNarrative.Document.ContentStart, rtbNarrative.Document.ContentEnd);
bMod.ID = 0;
//
result = Int64.TryParse(lblBidID.Content.ToString(), out intNum);
bMod.BidID = (result) ? intNum : 0;
//
bMod.ClientLocID = txtClientLocID.Text;
//
result = decimal.TryParse(txtBidAmtRetail.Text, out decNum);
bMod.BidAmtRetail = (result) ? decNum : 0;
//
result = decimal.TryParse(txtBidAmtCost.Text, out decNum);
bMod.BidAmtCost = (result) ? decNum : 0;
//
result = Int64.TryParse(txtArea.Text, out intNum);
bMod.Area = (result) ? intNum : 0;
//
result = DateTime.TryParse(dtpBidSent.Text, out DT);
bMod.BidSent = (result) ? DT : nullDate;
//
bMod.Description = txtDescription.Text;
bMod.BidWon = cboBidWon.Text;
bMod.AttnName = txtAttnName.Text;
bMod.descBidAmtRetail = txtdescBidAmtRetail.Text;
bMod.descBidAmtCost = txtdescBidAmtCost.Text;
// richtext formatting save to db
// tr contains reference to rtbNarrative
// saved as string in bMod.Narrative
using (MemoryStream ms = new MemoryStream())
{
tr.Save(ms, DataFormats.Rtf);
bMod.Narrative = Encoding.ASCII.GetString(ms.ToArray());
}
// returne the model
return bMod;
}