// ADD
private void buttonADD_Click(object sender, RoutedEventArgs e)
{
lw_WorkOrder_Model woMod = new lw_WorkOrder_Model();
string strMsg = "";
// load the model
woMod = (lw_WorkOrder_Model)DataContext;
// regular add (non-async)
strMsg = WOWkr.Add_WorkOrder_Rec(woMod);
sender = strMsg;
// reset display fields
// and initialize buttons
ResetDisplayFields();
InitialButtonConfiguration();
// update event
if (OnWorkOrder_ADD != null) OnWorkOrder_ADD(sender, new RoutedEventArgs());
}
// Aysnc Update Method
public void Update_WorkOrders_Async(lw_WorkOrder_Model woMod)
{
// aysnc thread
Task.Run(() =>
{
// update work order record
return WOWkr.Update_WorkOrder_rec(woMod);
})
.ContinueWith(task => UpdateResult = task.Result, context);
}
// Async Add Method
public void Add_WorkOrders_Async(lw_WorkOrder_Model woMod)
{
// async thread
Task.Run(() =>
{
// add WorkOrders record
return WOWkr.Add_WorkOrder_Rec(woMod);
})
.ContinueWith(task => AddResult = task.Result, context);
}
// Method: Select Client List by LIKE Client Name
public List<lw_WorkOrder_Model> Get_WorkerOrders_byLIKEClientName_List(string _clName)
{
// 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;
// create needed objects
SqlConnection connection;
// building sql command, ContactID is the Key
string sqlStatement = "SELECT ID, LocationID, WOID, PurchaseOrder, StartDate, FinishDate, " +
"ROWName, ROWNum, State, Description, JobType, PIC, ContractPrice_cost, " +
"ContractPrice_retail, RateID, LocID, expWorker, expEquip, expChem, InvoicedNoTax, " +
"perDiemCost, TankTotal, linenum, AccNum, ClientName " +
"FROM lw_WorkOrder " +
"WHERE ClientName LIKE @ClientName " +
"ORDER BY ClientName";
// create List
List<lw_WorkOrder_Model> woMod_List = new List<lw_WorkOrder_Model>();
try
{
connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand(sqlStatement, connection);
// insert command
command.Parameters.AddWithValue("@ClientName", _clName + '%');
SqlDataReader reader = command.ExecuteReader();
// read table, populate model
while (reader.Read())
{
lw_WorkOrder_Model woMod = new lw_WorkOrder_Model();
woMod.ID = (reader[0] != DBNull.Value) ? Convert.ToInt64(reader[0]) : 0;
woMod.LocationID = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
woMod.WOID = (reader[2] != DBNull.Value) ? Convert.ToInt64(reader[2]) : 0;
woMod.PurchaseOrder = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
// StartDate and FinishDate
result = DateTime.TryParse(reader[4].ToString(), out DT);
woMod.StartDate = (result) ? DT : nullDate;
result = DateTime.TryParse(reader[5].ToString(), out DT);
woMod.FinishDate = (result) ? DT : nullDate;
//
woMod.ROWName = (reader[6] != DBNull.Value) ? (string)reader[6] : "";
woMod.ROWNum = (reader[7] != DBNull.Value) ? (string)reader[7] : "";
woMod.State = (reader[8] != DBNull.Value) ? (string)reader[8] : "";
woMod.Description = (reader[9] != DBNull.Value) ? (string)reader[9] : "";
woMod.JobType = (reader[10] != DBNull.Value) ? (string)reader[10] : "";
woMod.PIC = (reader[11] != DBNull.Value) ? (string)reader[11] : "";
woMod.ContractPrice_cost = (reader[12] != DBNull.Value) ? (decimal)reader[12] : 0;
woMod.ContractPrice_retail = (reader[13] != DBNull.Value) ? (decimal)reader[13] : 0;
woMod.RateID = (reader[14] != DBNull.Value) ? Convert.ToInt64(reader[14]) : 0;
woMod.LocID = (reader[15] != DBNull.Value) ? Convert.ToInt64(reader[15]) : 0;
woMod.expWorker = (reader[16] != DBNull.Value) ? Convert.ToDouble(reader[16]) : 0;
woMod.expEquip = (reader[17] != DBNull.Value) ? Convert.ToDouble(reader[17]) : 0;
woMod.expChem = (reader[18] != DBNull.Value) ? Convert.ToDouble(reader[18]) : 0;
woMod.InvoicedNoTax = (reader[19] != DBNull.Value) ? Convert.ToDouble(reader[19]) : 0;
woMod.perDiemCost = (reader[20] != DBNull.Value) ? Convert.ToDouble(reader[20]) : 0;
woMod.TankTotal = (reader[21] != DBNull.Value) ? Convert.ToDouble(reader[21]) : 0;
woMod.linenum = (reader[22] != DBNull.Value) ? (string)reader[22] : "";
woMod.AccNum = (reader[23] != DBNull.Value) ? Convert.ToInt64(reader[23]) : 0;
woMod.ClientName = (reader[24] != DBNull.Value) ? (string)reader[24] : "";
// add to List
woMod_List.Add(woMod);
}
// close reader, close connection
reader.Close();
connection.Close();
strMsg = "done";
}
catch (Exception errMsg)
{
strMsg = errMsg.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Method: Get_WorkerOrders_byLIKEClientName_List", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
// return List
return woMod_List;
}
// Reset Bid Model
public lw_WorkOrder_Model Reset_Bids_Mod(lw_WorkOrder_Model woMod)
{
DateTime? nullDate = null;
// reset the model
woMod.ID = 0;
woMod.LocationID = "";
woMod.WOID = 0;
woMod.PurchaseOrder = "";
woMod.StartDate = nullDate;
woMod.FinishDate = nullDate;
woMod.ROWName = "";
woMod.ROWNum = "";
woMod.State = "";
woMod.Description = "";
woMod.JobType = "";
woMod.PIC = "";
woMod.ContractPrice_cost = 0;
woMod.ContractPrice_retail = 0;
woMod.RateID = 0;
woMod.LocID = 0;
woMod.expWorker = 0;
woMod.expEquip = 0;
woMod.expChem = 0;
woMod.InvoicedNoTax = 0;
woMod.perDiemCost = 0;
woMod.TankTotal = 0;
woMod.linenum = "";
woMod.AccNum = 0;
woMod.ClientName = "";
// return the model
return woMod;
}
// Method: update record
public string Update_WorkOrder_rec(lw_WorkOrder_Model woMod)
{
// 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_WorkOrder " +
"SET LocationID=@LocationID, " +
"WOID=@WOID, " +
"PurchaseOrder=@PurchaseOrder, " +
"StartDate=@StartDate, " +
"FinishDate=@FinishDate, " +
"ROWName=@ROWName, " +
"ROWNum=@ROWNum, " +
"State=@State, " +
"Description=@Description, " +
"JobType=@JobType, " +
"PIC=@PIC, " +
"ContractPrice_cost=@ContractPrice_cost, " +
"ContractPrice_retail=@ContractPrice_retail, " +
"RateID=@RateID, " +
"LocID=@LocID, " +
"expWorker=@expWorker, " +
"expEquip=@expEquip, " +
"expChem=@expChem, " +
"InvoicedNoTax=@InvoicedNoTax, " +
"perDiemCost=@perDiemCost, " +
"TankTotal=@TankTotal, " +
"linenum=@linenum, " +
"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("@LocationID", woMod.LocationID);
command.Parameters.AddWithValue("@WOID", woMod.WOID);
command.Parameters.AddWithValue("@PurchaseOrder", woMod.PurchaseOrder);
// date handling
if (woMod.StartDate != null)
{
command.Parameters.AddWithValue("@StartDate", woMod.StartDate);
}
else
{
command.Parameters.AddWithValue("@StartDate", DBNull.Value);
}
if (woMod.FinishDate != null)
{
command.Parameters.AddWithValue("@FinishDate", woMod.FinishDate);
}
else
{
command.Parameters.AddWithValue("@FinishDate", DBNull.Value);
}
command.Parameters.AddWithValue("@ROWName", woMod.ROWName);
command.Parameters.AddWithValue("@ROWNum", woMod.ROWNum);
command.Parameters.AddWithValue("@State", woMod.State);
command.Parameters.AddWithValue("@Description", woMod.Description);
command.Parameters.AddWithValue("@JobType", woMod.JobType);
command.Parameters.AddWithValue("@PIC", woMod.PIC);
command.Parameters.AddWithValue("@ContractPrice_cost", woMod.ContractPrice_cost);
command.Parameters.AddWithValue("@ContractPrice_retail", woMod.ContractPrice_retail);
command.Parameters.AddWithValue("@RateID", woMod.RateID);
command.Parameters.AddWithValue("@LocID", woMod.LocID);
command.Parameters.AddWithValue("@expWorker", woMod.expWorker);
command.Parameters.AddWithValue("@expEquip", woMod.expEquip);
command.Parameters.AddWithValue("@expChem", woMod.expChem);
command.Parameters.AddWithValue("@InvoicedNoTax", woMod.InvoicedNoTax);
command.Parameters.AddWithValue("@perDiemCost", woMod.perDiemCost);
command.Parameters.AddWithValue("@TankTotal", woMod.TankTotal);
command.Parameters.AddWithValue("@linenum", woMod.linenum);
command.Parameters.AddWithValue("@AccNum", woMod.AccNum);
command.Parameters.AddWithValue("@ClientName", woMod.ClientName);
// // ID is key
command.Parameters.AddWithValue("@ID", woMod.ID); // must be in the order of the sqlstatement
command.ExecuteNonQuery();
strMsg = "Record was updated.";
}
catch (Exception errMsg)
{
strMsg = errMsg.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "Method: Update_WorkOrder_rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
connection.Close();
return strMsg;
}
// ADD
public string Add_WorkOrder_Rec(lw_WorkOrder_Model woMod)
{
// Method: Create new record
// update the database
string strMsg = "";
DBNull dbNll = DBNull.Value;
// get the connection string
connectionString = PSWkr.G_SQLDatabaseConnectionString;
// create connection object
SqlConnection connection = new SqlConnection(connectionString);
// building sql command
string sqlStatement = "INSERT INTO lw_WorkOrder (LocationID, WOID, PurchaseOrder, StartDate, FinishDate, " +
"ROWName, ROWNum, State, Description, JobType, PIC, ContractPrice_cost, ContractPrice_retail, RateID, LocID, " +
"expWorker, expEquip, expChem, InvoicedNoTax, perDiemCost, TankTotal, linenum, AccNum, ClientName) " +
"VALUES (@LocationID, @WOID, @PurchaseOrder, @StartDate, @FinishDate, " +
"@ROWName, @ROWNum, @State, @Description, @JobType, @PIC, @ContractPrice_cost, @ContractPrice_retail, @RateID, @LocID, " +
"@expWorker, @expEquip, @expChem, @InvoicedNoTax, @perDiemCost, @TankTotal, @linenum, @AccNum, @ClientName)";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
try
{
connection.Open();
// Adding parameters for the Insert Command
command.Parameters.AddWithValue("@LocationID", woMod.LocationID);
command.Parameters.AddWithValue("@WOID", woMod.WOID);
command.Parameters.AddWithValue("@PurchaseOrder", woMod.PurchaseOrder);
// date handling
if (woMod.StartDate != null)
{
command.Parameters.AddWithValue("@StartDate", woMod.StartDate);
}
else
{
command.Parameters.AddWithValue("@StartDate", DBNull.Value);
}
if (woMod.FinishDate != null)
{
command.Parameters.AddWithValue("@FinishDate", woMod.FinishDate);
}
else
{
command.Parameters.AddWithValue("@FinishDate", DBNull.Value);
}
command.Parameters.AddWithValue("@ROWName", woMod.ROWName);
command.Parameters.AddWithValue("@ROWNum", woMod.ROWNum);
command.Parameters.AddWithValue("@State", woMod.State);
command.Parameters.AddWithValue("@Description", woMod.Description);
command.Parameters.AddWithValue("@JobType", woMod.JobType);
command.Parameters.AddWithValue("@PIC", woMod.PIC);
command.Parameters.AddWithValue("@ContractPrice_cost", woMod.ContractPrice_cost);
command.Parameters.AddWithValue("@ContractPrice_retail", woMod.ContractPrice_retail);
command.Parameters.AddWithValue("@RateID", woMod.RateID);
command.Parameters.AddWithValue("@LocID", woMod.LocID);
command.Parameters.AddWithValue("@expWorker", woMod.expWorker);
command.Parameters.AddWithValue("@expEquip", woMod.expEquip);
command.Parameters.AddWithValue("@expChem", woMod.expChem);
command.Parameters.AddWithValue("@InvoicedNoTax", woMod.InvoicedNoTax);
command.Parameters.AddWithValue("@perDiemCost", woMod.perDiemCost);
command.Parameters.AddWithValue("@TankTotal", woMod.TankTotal);
command.Parameters.AddWithValue("@linenum", woMod.linenum);
command.Parameters.AddWithValue("@AccNum", woMod.AccNum);
command.Parameters.AddWithValue("@ClientName", woMod.ClientName);
command.ExecuteNonQuery();
strMsg = "Record was added.";
// update the next available Work Order Number by 1
UpdateNext_WONum();
}
catch (Exception e)
{
strMsg = e.Message.ToString();
System.Windows.MessageBox.Show(strMsg, "SQL:Record Add Error", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Exclamation);
}
connection.Close();
return strMsg;
}
// Method: get record data based on id
public lw_WorkOrder_Model Get_Specific_WorkOrder_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, LocationID, WOID, PurchaseOrder, StartDate, FinishDate, " +
"ROWName, ROWNum, State, Description, JobType, PIC, ContractPrice_cost, " +
"ContractPrice_retail, RateID, LocID, expWorker, expEquip, expChem, InvoicedNoTax, " +
"perDiemCost, TankTotal, linenum, AccNum, ClientName " +
"FROM lw_WorkOrder " +
"WHERE WOID=@WOID";
// SqlCommand
SqlCommand command = new SqlCommand(sqlStatement, connection);
// Create object base on WorkOrder Model (woMod)
lw_WorkOrder_Model woMod = new lw_WorkOrder_Model();
try
{
// open the connection
connection.Open();
command.Parameters.AddWithValue("@WOID", recID);
// execute the reader
SqlDataReader reader = command.ExecuteReader();
// populate the invoice list
if (reader.Read())
{
woMod.ID = (reader[0] != DBNull.Value) ? Convert.ToInt64(reader[0]) : 0;
woMod.LocationID = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
woMod.WOID = (reader[2] != DBNull.Value) ? Convert.ToInt64(reader[2]) : 0;
woMod.PurchaseOrder = (reader[3] != DBNull.Value) ? (string)reader[3] : "";
// StartDate and FinishDate
result = DateTime.TryParse(reader[4].ToString(), out DT);
woMod.StartDate = (result) ? DT : nullDate;
result = DateTime.TryParse(reader[5].ToString(), out DT);
woMod.FinishDate = (result) ? DT : nullDate;
//
woMod.ROWName = (reader[6] != DBNull.Value) ? (string)reader[6] : "";
woMod.ROWNum = (reader[7] != DBNull.Value) ? (string)reader[7] : "";
woMod.State = (reader[8] != DBNull.Value) ? (string)reader[8] : "";
woMod.Description = (reader[9] != DBNull.Value) ? (string)reader[9] : "";
woMod.JobType = (reader[10] != DBNull.Value) ? (string)reader[10] : "";
woMod.PIC = (reader[11] != DBNull.Value) ? (string)reader[11] : "";
woMod.ContractPrice_cost = (reader[12] != DBNull.Value) ? (decimal)reader[12] : 0;
woMod.ContractPrice_retail = (reader[13] != DBNull.Value) ? (decimal)reader[13] : 0;
woMod.RateID = (reader[14] != DBNull.Value) ? Convert.ToInt64(reader[14]) : 0;
woMod.LocID = (reader[15] != DBNull.Value) ? Convert.ToInt64(reader[15]) : 0;
woMod.expWorker = (reader[16] != DBNull.Value) ? Convert.ToDouble(reader[16]) : 0;
woMod.expEquip = (reader[17] != DBNull.Value) ? Convert.ToDouble(reader[17]) : 0;
woMod.expChem = (reader[18] != DBNull.Value) ? Convert.ToDouble(reader[18]) : 0;
woMod.InvoicedNoTax = (reader[19] != DBNull.Value) ? Convert.ToDouble(reader[19]) : 0;
woMod.perDiemCost = (reader[20] != DBNull.Value) ? Convert.ToDouble(reader[20]) : 0;
woMod.TankTotal = (reader[21] != DBNull.Value) ? Convert.ToDouble(reader[21]) : 0;
woMod.linenum = (reader[22] != DBNull.Value) ? (string)reader[22] : "";
woMod.AccNum = (reader[23] != DBNull.Value) ? Convert.ToInt64(reader[23]) : 0;
woMod.ClientName = (reader[24] != DBNull.Value) ? (string)reader[24] : "";
}
// the close
reader.Close();
}
catch (Exception e)
{
strMsg = e.Message.ToString();
}
// the close
connection.Close();
// return the Model
return woMod;
}
// UPDATE
private void buttonUpdate_Click(object sender, RoutedEventArgs e)
{
lw_WorkOrder_Model woMod = new lw_WorkOrder_Model();
string strMsg = "";
// load the model
woMod = (lw_WorkOrder_Model)DataContext;
// update work order
strMsg = WOWkr.Update_WorkOrder_rec(woMod);
sender = strMsg;
// update event
if (OnWorkOrder_UPDATE != null) OnWorkOrder_UPDATE(sender, new RoutedEventArgs());
}
// ADD Enable using Blank
private void buttonADD_Enable_Blank_Click(object sender, RoutedEventArgs e)
{
Int64 iNWoNum = 0;
// 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 lw_WorkOrder_Model();
// reset display fields and change
// buttons to Add configuration
ResetDisplayFields();
ADDButtonConfiguration();
sender = "ADD Enabled based on blank display fields.";
// get next WO number
iNWoNum = GetNext_WorkOrder_Number();
txtWOID.Text = iNWoNum.ToString();
// Add Enable Event
if (OnWorkOrder_ADDEnabel != null) OnWorkOrder_ADDEnabel(sender, new RoutedEventArgs());
}
// ADD Enable with copy
private void buttonADD_Enable_Copy_Click(object sender, RoutedEventArgs e)
{
lw_WorkOrder_Model woMod = new lw_WorkOrder_Model();
Int64 iNWoNum = 0;
// change to ADD configuration
ADDButtonConfiguration();
sender = "ADD Enabled based on copying display fields.";
lblID.Content = "";
// get next WO number
woMod = (lw_WorkOrder_Model)DataContext;
iNWoNum = GetNext_WorkOrder_Number();
// insert new WO number
woMod.WOID = iNWoNum;
txtWOID.Text = iNWoNum.ToString();
// move everything back to dataContext
DataContext = (lw_WorkOrder_Model)woMod;
// Add Enable Event
if (OnWorkOrder_ADDEnabel != null) OnWorkOrder_ADDEnabel(sender, new RoutedEventArgs());
}
