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