public bool IncrementPackingSlipSuffix() { /*I used the command "GRANT UPDATE ON [WTF_App].[dbo].[co] TO WTFApp" to grant the update priveleges * "REVOKE UPDATE ON [WTF_App].[dbo].[co] TO WTFApp" is the SQL to remove the privelege. * Note that I needed to make a new query in order for a successful execution; each time I tried to use an existing Query, it failed */ SqlCommand objSQLCommand; string strSQL; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("UpdatePackingSlipPrintSequence", new string[] { CustomerOrderNumber.Trim() }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = objSL8_WTF_DataBaseSettings.SQLConnection.CreateCommand(); objSQLCommand.CommandText = strSQL; if (objSQLCommand.ExecuteNonQuery() == 1)//appropriate row was affected { return(true); } else { return(false); } }
//public static SortableSearchableBindingList<StockLocation> StockLocations //{ // get // { // SortableSearchableBindingList<StockLocation> objStockLocationList = new SortableSearchableBindingList<StockLocation>(); // StockLocation objStockLocation; // SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); // DataTable objDataTable = new DataTable(); // SqlDataAdapter objDataAdapter; // SqlCommand objSQLCommand; // string strSQL; // strSQL = QueryDefinitions.GetQuery("SelectStockRoomLocations"); // objSL8_WTF_DataBaseSettings.SQLConnection.Open(); // objSQLCommand = new SqlCommand(strSQL, objSL8_WTF_DataBaseSettings.SQLConnection); // objDataAdapter = new SqlDataAdapter(objSQLCommand); // objDataAdapter.Fill(objDataTable); // foreach (DataRow objRow in objDataTable.Rows) // { // objStockLocation = new StockLocation(); // objStockLocation.Location = objRow["loc"].ToString(); // objStockLocation.LocationDescription = objRow["description"].ToString(); // objStockLocation.LocationType = objRow["loc_type"].ToString()[0]; // objStockLocationList.Add(objStockLocation); // } // return objStockLocationList; // } //} //public static BindingList<ComboBoxItem> StockLocations //{ // get // { // string strSQL; // SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); // SqlCommand objSQLCommand; // SqlDataAdapter objDataAdapter; // DataTable objDataTable; // BindingList<ComboBoxItem> objBindingList; // objBindingList = new BindingList<ComboBoxItem>(); // objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); // strSQL = QueryDefinitions.GetQuery("SelectStockRoomLocations"); // objSL8_WTF_DataBaseSettings.SQLConnection.Open(); // objSQLCommand = new SqlCommand(strSQL, objSL8_WTF_DataBaseSettings.SQLConnection); // objDataAdapter = new SqlDataAdapter(objSQLCommand); // objDataTable = new DataTable(); // objDataAdapter.Fill(objDataTable); // if (objDataTable.Rows.Count > 0) // { // objBindingList = new BindingList<ComboBoxItem>(); // //objBindingList.Add(new ComboBoxItem { Text = "--- Select Carrier ---", Value = "0" }); // for (int intCounter = 0; intCounter < objDataTable.Rows.Count; intCounter++) // objBindingList.Add(new ComboBoxItem { Value = objDataTable.Rows[intCounter][0].ToString(), Text = objDataTable.Rows[intCounter][0].ToString() }); // return objBindingList; // } // else // return objBindingList; // } //} public bool Insert() { /*I used the command "GRANT INSERT ON [WTF_App].[dbo].[jobtran] TO WTFApp" to grant the insert priveleges * "REVOKE INSERT ON [WTF_App].[dbo].[jobtran] TO WTFApp" is the SQL to remove the privelege. * Note that I needed to make a new query in order for a successful execution; each time I tried to use an existing Query, it failed */ SortableSearchableBindingList <Operation> objOperationList = new SortableSearchableBindingList <Operation>(); SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); DataTable objDataTable = new DataTable(); SqlCommand objSQLCommand; string strSQL; int intCompleteOperation, intCloseJob; intCompleteOperation = CompleteOperation ? 1 : 0; intCloseJob = CloseJob ? 1 : 0; strSQL = QueryDefinitions.GetQuery("InsertIntoJobTran", new string[] { JobNumber, Suffix.ToString(), TransactionType.ToString().ToUpper(), DateTime.Now.ToString(), QtyComplete.ToString(), Operation.ToString(), QtyMoved.ToString(), Warehouse, Location.ToString(), TransactionClass.ToString().ToUpper(), Operation.WorkCenter, UserCode, intCompleteOperation.ToString(), intCloseJob.ToString(), NextOperation.ToString() }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = objSL8_WTF_DataBaseSettings.SQLConnection.CreateCommand(); objSQLCommand.CommandText = strSQL; if (objSQLCommand.ExecuteNonQuery() == 1) //the appropriate record was affected { return(true); } else { return(false); } }
public static SortableSearchableBindingList<VendorAddress> Search(string strVendorNumber, int intMaxRecordCount = 100, string strSearchBy = "vend_num")//Retrieves a list of customers that match the customer number { SortableSearchableBindingList<VendorAddress> objVendorAddressList; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; DataTable objDataTable = new DataTable(); SqlDataAdapter objDataAdapter; SqlCommand objSQLCommand; VendorAddress objVendorAddress; string strSQL; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectTopNVendorAddresses", new string[] { intMaxRecordCount.ToString(), strSearchBy, strVendorNumber, strSearchBy }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = new SqlCommand(strSQL, objSL8_WTF_DataBaseSettings.SQLConnection); objDataAdapter = new SqlDataAdapter(objSQLCommand); objDataAdapter.Fill(objDataTable); objVendorAddressList = new SortableSearchableBindingList<VendorAddress>(); if (objDataTable.Rows.Count > 0) { foreach (DataRow objRow in objDataTable.Rows) { objVendorAddress = new VendorAddress(); objVendorAddress.VendorNumber = objRow["vend_num"].ToString(); objVendorAddress.Name = objRow["Name"].ToString(); objVendorAddress.AddressLine1 = objRow["Addr##1"].ToString(); objVendorAddress.AddressLine2 = objRow["Addr##2"].ToString(); objVendorAddress.AddressLine3 = objRow["Addr##3"].ToString(); objVendorAddress.AddressLine4 = objRow["Addr##4"].ToString(); objVendorAddress.City = objRow["City"].ToString(); objVendorAddress.State = objRow["State"].ToString(); objVendorAddress.ZipCode = objRow["ZIP"].ToString(); objVendorAddress.Country = objRow["country"].ToString(); objVendorAddressList.Add(objVendorAddress); } } return objVendorAddressList; }
public static SortableSearchableBindingList <CustomerAddress> Find(string strCustomerNumber)//Retrieves a list of addresses of the specified customer { SortableSearchableBindingList <CustomerAddress> objCustomerAddressList; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; DataTable objDataTable = new DataTable(); SqlDataAdapter objDataAdapter; SqlCommand objSQLCommand; CustomerAddress objCustomerAddress; string strSQL; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectCustomerAddresses", new string[] { strCustomerNumber }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = new SqlCommand(strSQL, objSL8_WTF_DataBaseSettings.SQLConnection); objDataAdapter = new SqlDataAdapter(objSQLCommand); objDataAdapter.Fill(objDataTable); objCustomerAddressList = new SortableSearchableBindingList <CustomerAddress>(); if (objDataTable.Rows.Count > 0) { foreach (DataRow objRow in objDataTable.Rows) { objCustomerAddress = new CustomerAddress(); objCustomerAddress.CustomerNumber = objRow["cust_num"].ToString(); objCustomerAddress.Name = objRow["Name"].ToString(); objCustomerAddress.AddressLine1 = objRow["Addr##1"].ToString(); objCustomerAddress.AddressLine2 = objRow["Addr##2"].ToString(); objCustomerAddress.AddressLine3 = objRow["Addr##3"].ToString(); objCustomerAddress.AddressLine4 = objRow["Addr##4"].ToString(); objCustomerAddress.City = objRow["City"].ToString(); objCustomerAddress.State = objRow["State"].ToString(); objCustomerAddress.ZipCode = objRow["ZIP"].ToString(); objCustomerAddress.Country = objRow["country"].ToString(); objCustomerAddressList.Add(objCustomerAddress); } } return(objCustomerAddressList); }
//Retrieves a list of customers that match the customer number public static SortableSearchableBindingList<VendorAddress> Search(string strVendorNumber, int intMaxRecordCount = 100, string strSearchBy = "vend_num") { SortableSearchableBindingList<VendorAddress> objVendorAddressList; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; DataTable objDataTable = new DataTable(); SqlDataAdapter objDataAdapter; SqlCommand objSQLCommand; VendorAddress objVendorAddress; string strSQL; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectTopNVendorAddresses", new string[] { intMaxRecordCount.ToString(), strSearchBy, strVendorNumber, strSearchBy }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = new SqlCommand(strSQL, objSL8_WTF_DataBaseSettings.SQLConnection); objDataAdapter = new SqlDataAdapter(objSQLCommand); objDataAdapter.Fill(objDataTable); objVendorAddressList = new SortableSearchableBindingList<VendorAddress>(); if (objDataTable.Rows.Count > 0) { foreach (DataRow objRow in objDataTable.Rows) { objVendorAddress = new VendorAddress(); objVendorAddress.VendorNumber = objRow["vend_num"].ToString(); objVendorAddress.Name = objRow["Name"].ToString(); objVendorAddress.AddressLine1 = objRow["Addr##1"].ToString(); objVendorAddress.AddressLine2 = objRow["Addr##2"].ToString(); objVendorAddress.AddressLine3 = objRow["Addr##3"].ToString(); objVendorAddress.AddressLine4 = objRow["Addr##4"].ToString(); objVendorAddress.City = objRow["City"].ToString(); objVendorAddress.State = objRow["State"].ToString(); objVendorAddress.ZipCode = objRow["ZIP"].ToString(); objVendorAddress.Country = objRow["country"].ToString(); objVendorAddressList.Add(objVendorAddress); } } return objVendorAddressList; }
//Retrieves a list of addresses of the specified customer public static SortableSearchableBindingList<CustomerAddress> Find(string strCustomerNumber) { SortableSearchableBindingList<CustomerAddress> objCustomerAddressList; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; DataTable objDataTable = new DataTable(); SqlDataAdapter objDataAdapter; SqlCommand objSQLCommand; CustomerAddress objCustomerAddress; string strSQL; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectCustomerAddresses", new string[] { strCustomerNumber }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = new SqlCommand(strSQL, objSL8_WTF_DataBaseSettings.SQLConnection); objDataAdapter = new SqlDataAdapter(objSQLCommand); objDataAdapter.Fill(objDataTable); objCustomerAddressList = new SortableSearchableBindingList<CustomerAddress>(); if (objDataTable.Rows.Count > 0) { foreach (DataRow objRow in objDataTable.Rows) { objCustomerAddress = new CustomerAddress(); objCustomerAddress.CustomerNumber = objRow["cust_num"].ToString(); objCustomerAddress.Name = objRow["Name"].ToString(); objCustomerAddress.AddressLine1 = objRow["Addr##1"].ToString(); objCustomerAddress.AddressLine2 = objRow["Addr##2"].ToString(); objCustomerAddress.AddressLine3 = objRow["Addr##3"].ToString(); objCustomerAddress.AddressLine4 = objRow["Addr##4"].ToString(); objCustomerAddress.City = objRow["City"].ToString(); objCustomerAddress.State = objRow["State"].ToString(); objCustomerAddress.ZipCode = objRow["ZIP"].ToString(); objCustomerAddress.Country = objRow["country"].ToString(); objCustomerAddressList.Add(objCustomerAddress); } } return objCustomerAddressList; }
public Order(string strOrderNumber) { SqlCommand objSQLCommand; string strSQL; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; SqlDataReader objDataReader; DateTime dtmTemp; double dblTemp; int intTemp; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectTopNOrders", new string[] { "1", "co.co_num", strOrderNumber.Trim(), "co.co_num" }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = objSL8_WTF_DataBaseSettings.SQLConnection.CreateCommand(); objSQLCommand.CommandText = strSQL; objDataReader = objSQLCommand.ExecuteReader(); if (objDataReader.HasRows) { objDataReader.Read(); CustomerOrderNumber = objDataReader["co_num"].ToString(); IsBlanketOrder = objDataReader["type"].ToString().ToUpper().Trim().Equals("B") ? true : false;//Types are either B-Blanket, R-Regular or E-? CustomerPO = objDataReader["cust_po"].ToString(); CustomerNumber = objDataReader["cust_num"].ToString(); CustomerName = objDataReader["name"].ToString(); OrderDate = DateTime.TryParse(objDataReader["order_date"].ToString(), out dtmTemp) ? dtmTemp : DateTime.MinValue; RowPointer = objDataReader["RowPointer"].ToString(); PackingSlipPrintSequence = double.TryParse(objDataReader["decifld1"].ToString(), out dblTemp) ? (int)dblTemp : 0; SelectedAddressNo = int.TryParse(objDataReader["cust_seq"].ToString(), out intTemp) ? intTemp : 0; //Notes = this.GetInternalNotes(); } else { throw new Exception("Order does not exist!"); } }
private static List <Note> GetNotesListByGUID(string strGUID) { SqlCommand objSQLCommand; string strSQL; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; SqlDataReader objDataReader; Note objNote; List <Note> objNoteList; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectNotesByGUID", new string[] { strGUID }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = objSL8_WTF_DataBaseSettings.SQLConnection.CreateCommand(); objSQLCommand.CommandText = strSQL; objDataReader = objSQLCommand.ExecuteReader(); objNoteList = new List <Note>(); if (objDataReader.HasRows) { while (objDataReader.Read()) { objNote = new Note(); objNote.Description = objDataReader["Description"].ToString(); objNote.Notes = objDataReader["Note"].ToString(); objNote.IsSpecificNote = objDataReader["IsSpecificNote"].ToString().Equals("0") ? false : true; objNote.TableName = objDataReader["TableName"].ToString(); objNote.RefRowPointer = objDataReader["RefRowPointer"].ToString(); objNote.IsInternalNote = objDataReader["IsInternalNote"].ToString().Equals("0") ? false : true; objNoteList.Add(objNote); } } return(objNoteList); }
public Order(string strOrderNumber) { SqlCommand objSQLCommand; string strSQL; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; SqlDataReader objDataReader; DateTime dtmTemp; double dblTemp; int intTemp; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectTopNOrders", new string[] { "1", "co.co_num", strOrderNumber.Trim(), "co.co_num" }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = objSL8_WTF_DataBaseSettings.SQLConnection.CreateCommand(); objSQLCommand.CommandText = strSQL; objDataReader = objSQLCommand.ExecuteReader(); if (objDataReader.HasRows) { objDataReader.Read(); CustomerOrderNumber = objDataReader["co_num"].ToString(); IsBlanketOrder = objDataReader["type"].ToString().ToUpper().Trim().Equals("B") ? true : false;//Types are either B-Blanket, R-Regular or E-? CustomerPO = objDataReader["cust_po"].ToString(); CustomerNumber = objDataReader["cust_num"].ToString(); CustomerName = objDataReader["name"].ToString(); OrderDate = DateTime.TryParse(objDataReader["order_date"].ToString(), out dtmTemp) ? dtmTemp : DateTime.MinValue; RowPointer = objDataReader["RowPointer"].ToString(); PackingSlipPrintSequence = double.TryParse(objDataReader["decifld1"].ToString(), out dblTemp) ? (int)dblTemp : 0; SelectedAddressNo = int.TryParse(objDataReader["cust_seq"].ToString(), out intTemp) ? intTemp : 0; //Notes = this.GetInternalNotes(); } else throw new Exception("Order does not exist!"); }
public PurchasedItemList() : base() { SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; SqlCommand objCmd; SqlDataAdapter objAdapter; DataSet objDataSet, objLoadDataSet; TimePhasedItem objPurchasedItem; RepletionDepletionItem objRepletionDepletionItem; int intTemp; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objCmd = new SqlCommand("Rpt_PurchaseRequirementsSp", objSL8_WTF_DataBaseSettings.SQLConnection); objCmd.CommandTimeout = 60;//via the vpn I kept getting a timeout; this fixed it. objCmd.CommandType = CommandType.StoredProcedure; //objCmd.Parameters.Add(new SqlParameter("@ItemStarting", "WIQ.3045-4 1018")); //objCmd.Parameters.Add(new SqlParameter("@ItemEnding", "WIQ.3045-4 1018")); objCmd.Parameters.Add(new SqlParameter("@ShowDepl", 1)); objCmd.Parameters.Add(new SqlParameter("@ShowRepl", 1)); objCmd.Parameters.Add(new SqlParameter("@TimePhaseDetail", 1)); objCmd.Parameters.Add(new SqlParameter("@JobStatus", "FR"));//can make this "FR" to show Firm AND Released Jobs objCmd.Parameters.Add(new SqlParameter("@POStatus", "O")); objCmd.Parameters.Add(new SqlParameter("@Source", "P")); objAdapter = new SqlDataAdapter(objCmd); objDataSet = new DataSet(); objAdapter.Fill(objDataSet); objCmd = new SqlCommand("CLM_ResourceGroupScheduleSp", objSL8_WTF_DataBaseSettings.SQLConnection); objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add(new SqlParameter("@StartDate", "1/1/1753")); objCmd.Parameters.Add(new SqlParameter("@EndDate", "12/31/9999")); objCmd.Parameters.Add(new SqlParameter("@AltNum", 0)); objAdapter = new SqlDataAdapter(objCmd); objLoadDataSet = new DataSet(); objAdapter.Fill(objLoadDataSet); objSL8_WTF_DataBaseSettings.SQLConnection.Close(); var objGroupedItems = objDataSet.Tables[0].AsEnumerable() .GroupBy(f => new { ItemID = f.Field <string>("item") }) .Select(g => new { ItemID = g.Key.ItemID, ItemDescription = g.First().Field <string>("item_description"), UnitOfMeasure = g.First().Field <string>("u_m"), QtyOnHand = g.First().Field <decimal>("on_hand"), QtyOnOrder = g.First().Field <decimal>("on_order"), QtyAllocated = g.First().Field <decimal>("alloc_mfg"), NetQtyRequired = g.First().Field <decimal>("net_req"), VendorNumber = g.First().Field <string>("vend1"), VendorName = g.First().Field <string>("vendname1"), RepletionsAndDepletions = g }); foreach (var objAnonymousPurchasedItem in objGroupedItems) { objPurchasedItem = new TimePhasedItem { ItemID = objAnonymousPurchasedItem.ItemID, ItemDescription = objAnonymousPurchasedItem.ItemDescription, UnitOfMeasure = objAnonymousPurchasedItem.UnitOfMeasure, QtyOnHand = objAnonymousPurchasedItem.QtyOnHand, QtyOnOrder = objAnonymousPurchasedItem.QtyOnOrder, QtyAllocated = objAnonymousPurchasedItem.QtyAllocated, NetQtyRequired = objAnonymousPurchasedItem.NetQtyRequired, VendorNumber = objAnonymousPurchasedItem.VendorNumber, VendorName = objAnonymousPurchasedItem.VendorName }; objPurchasedItem.RepletionsAndDepletions = new SortableBindingList <RepletionDepletionItem>(); foreach (var objAnonymousRepletionDepletion in objAnonymousPurchasedItem.RepletionsAndDepletions) { objRepletionDepletionItem = new RepletionDepletionItem { DueDate = objAnonymousRepletionDepletion.Field <DateTime>("sub10_due_date"), ProjectedOnHand = objAnonymousRepletionDepletion.Field <decimal>("sub10_on_hand"), OutstandingRequirement = objAnonymousRepletionDepletion.Field <decimal>("sub10_reqmt"), OutstandingReciept = objAnonymousRepletionDepletion.Field <decimal>("sub10_receipt"), Status = objAnonymousRepletionDepletion.Field <string>("sub10_s"), ReferenceData = objAnonymousRepletionDepletion.Field <string>("sub10_reference"), VendorName = objAnonymousRepletionDepletion.Field <string>("sub10_name") }; if (!string.IsNullOrEmpty(objRepletionDepletionItem.Status) && objRepletionDepletionItem.Status.Equals("R"))//it is a released job, so get the workcenter { var objArray = objRepletionDepletionItem.ReferenceData.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); objRepletionDepletionItem.Job = objArray[1].Substring(0, objArray[1].IndexOf('-')); objRepletionDepletionItem.Operation = int.TryParse(objArray[2], out intTemp) ? intTemp : 0; objRepletionDepletionItem.Suffix = int.TryParse(objArray[1].Substring(objArray[1].IndexOf('-') + 1, objArray[1].Length - objArray[1].IndexOf('-') - 1), out intTemp) ? intTemp : 0; var query = objLoadDataSet.Tables[0].AsEnumerable() .Where(w => !string.IsNullOrEmpty(w.Field <string>("DerJob")) && w.Field <string>("DerJob").Equals(objRepletionDepletionItem.Job)) .Where(w => !w.IsNull("DerOperNum") && w.Field <int>("DerOperNum") == objRepletionDepletionItem.Operation) .Where(w => !w.IsNull("DerSuffix") && w.Field <Int16>("DerSuffix") == objRepletionDepletionItem.Suffix) .FirstOrDefault(); //I kept getting unhandled null exceptions when executing the below query. turns out that there were nulls in the stored procedure and I needed to check for them; see altered query above. //var query = objLoadDataSet.Tables[0].AsEnumerable() // .Where(w => w.Field<string>("DerJob").Equals(objRepletionDepletionItem.Job) && // w.Field<int>("DerOperNum") == objRepletionDepletionItem.Operation && // w.Field<Int16>("DerSuffix") == objRepletionDepletionItem.Suffix) // .FirstOrDefault(); if (query != null) { objRepletionDepletionItem.WorkCenter = query.Field <string>("DerWC"); objRepletionDepletionItem.ItemID = query.Field <string>("PARTID"); } //Console.WriteLine(); } objPurchasedItem.RepletionsAndDepletions.Add(objRepletionDepletionItem); } this.Add(objPurchasedItem); } }
public TimePhasedItemList(bool SetDueDateByProjectedSchedule) : base() { SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; SqlCommand objCmd; SqlDataAdapter objAdapter; DataSet objDataSet, objLoadDataSet; TimePhasedItem objTimePhasedInventoryItem; RepletionDepletionItem objRepletionDepletionItem; SL8DbDataContext objDB; int intTemp; objDB = new SL8DbDataContext(); objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objCmd = new SqlCommand("CLM_ResourceGroupScheduleSp", objSL8_WTF_DataBaseSettings.SQLConnection); objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add(new SqlParameter("@StartDate", "1/1/1753")); objCmd.Parameters.Add(new SqlParameter("@EndDate", "12/31/9999")); objCmd.Parameters.Add(new SqlParameter("@AltNum", 0)); objAdapter = new SqlDataAdapter(objCmd); objLoadDataSet = new DataSet(); objAdapter.Fill(objLoadDataSet); objCmd = new SqlCommand("SupDemBuildSp", objSL8_WTF_DataBaseSettings.SQLConnection); objCmd.CommandType = CommandType.StoredProcedure; foreach (var objItem in objDB.PurchasedItems) { objTimePhasedInventoryItem = new TimePhasedItem(); objTimePhasedInventoryItem.ItemID = objItem.ItemID; objTimePhasedInventoryItem.ItemDescription = objItem.Description; objTimePhasedInventoryItem.UnitOfMeasure = objItem.UnitOfMeasure; objCmd.Parameters.Clear(); objCmd.Parameters.Add(new SqlParameter("@Item", objTimePhasedInventoryItem.ItemID)); objCmd.Parameters.Add(new SqlParameter("@Whse", "MAIN")); objCmd.Parameters.Add(new SqlParameter("@StartingDate", DateTime.Now.AddYears(-1).ToShortDateString())); objCmd.Parameters.Add(new SqlParameter("@Filter", DBNull.Value));//This was the only way I could get null passed as a parameter to the stored proc objAdapter = new SqlDataAdapter(objCmd); objDataSet = new DataSet(); objAdapter.Fill(objDataSet); objTimePhasedInventoryItem.RepletionsAndDepletions = new SortableBindingList <RepletionDepletionItem>(); foreach (var objAnonymousRepletionDepletion in objDataSet.Tables[0].AsEnumerable()) { objRepletionDepletionItem = new RepletionDepletionItem { DueDate = objAnonymousRepletionDepletion.Field <DateTime?>("DisplayDate") ?? DateTime.MinValue,//This was how I stopped the null errors from the null dates in the db ProjectedOnHand = objAnonymousRepletionDepletion.Field <decimal>("QtyOnHand"), Qty = objAnonymousRepletionDepletion.Field <decimal>("Qty"), Status = objAnonymousRepletionDepletion.Field <string>("RcptRqmt"), ReferenceData = objAnonymousRepletionDepletion.Field <string>("Reference"), ItemID = objAnonymousRepletionDepletion.Field <string>("Parent"), Job = objAnonymousRepletionDepletion.Field <string>("RefNum") }; if (objRepletionDepletionItem.Job.ToUpper().Trim().Equals("INIT")) { objTimePhasedInventoryItem.QtyOnHand = objRepletionDepletionItem.ProjectedOnHand; objRepletionDepletionItem.DueDate = DateTime.Now; } else if (!string.IsNullOrEmpty(objRepletionDepletionItem.Status) && objRepletionDepletionItem.Status.Equals("Q"))//it is a job, so get the workcenter { var objArray = objRepletionDepletionItem.ReferenceData.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); objRepletionDepletionItem.Job = objArray[1].Substring(0, objArray[1].IndexOf('-')); objRepletionDepletionItem.Operation = int.TryParse(objArray[2], out intTemp) ? intTemp : 0; objRepletionDepletionItem.Suffix = int.TryParse(objArray[1].Substring(objArray[1].IndexOf('-') + 1, objArray[1].Length - objArray[1].IndexOf('-') - 1), out intTemp) ? intTemp : 0; var query = objLoadDataSet.Tables[0].AsEnumerable() .Where(w => !string.IsNullOrEmpty(w.Field <string>("DerJob")) && w.Field <string>("DerJob").Equals(objRepletionDepletionItem.Job)) .Where(w => !w.IsNull("DerOperNum") && w.Field <int>("DerOperNum") == objRepletionDepletionItem.Operation) .Where(w => !w.IsNull("DerSuffix") && w.Field <Int16>("DerSuffix") == objRepletionDepletionItem.Suffix) .OrderBy(w => w.Field <DateTime>("STARTDATE")) //sort the load so I get the earliest date... .FirstOrDefault(); //I kept getting unhandled null exceptions when executing the below query. turns out that there were nulls in the stored procedure and I needed to check for them; see altered query above. //var query = objLoadDataSet.Tables[0].AsEnumerable() // .Where(w => w.Field<string>("DerJob").Equals(objRepletionDepletionItem.Job) && // w.Field<int>("DerOperNum") == objRepletionDepletionItem.Operation && // w.Field<Int16>("DerSuffix") == objRepletionDepletionItem.Suffix) // .FirstOrDefault(); if (query != null) { objRepletionDepletionItem.WorkCenter = query.Field <string>("DerWC"); //objRepletionDepletionItem.ItemID = query.Field<string>("PARTID"); if (SetDueDateByProjectedSchedule) { objRepletionDepletionItem.DueDate = query.Field <DateTime?>("STARTDATE") ?? DateTime.MinValue; //set the DueDate to the earliest operation start date... } } } objTimePhasedInventoryItem.RepletionsAndDepletions.Add(objRepletionDepletionItem); } this.Add(objTimePhasedInventoryItem); } objSL8_WTF_DataBaseSettings.SQLConnection.Close(); }
private void LoadCustomerAddresses() { SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; DataTable objDataTable = new DataTable(); SqlDataAdapter objDataAdapter; SqlCommand objSQLCommand; CustomerAddress objCustomerAddress; string strSQL; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectCustomerAddresses", new string[] { mintCustomerNumber.ToString() }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = new SqlCommand(strSQL, objSL8_WTF_DataBaseSettings.SQLConnection); objDataAdapter = new SqlDataAdapter(objSQLCommand); objDataAdapter.Fill(objDataTable); mobjCustomerAddressList = new BindingList<CustomerAddress>(); foreach (DataRow objRow in objDataTable.Rows) { objCustomerAddress = new CustomerAddress(); objCustomerAddress.Name = objRow["Name"].ToString(); objCustomerAddress.AddressLine1 = objRow["Addr##1"].ToString(); objCustomerAddress.AddressLine2 = objRow["Addr##2"].ToString(); objCustomerAddress.AddressLine3 = objRow["Addr##3"].ToString(); objCustomerAddress.AddressLine4 = objRow["Addr##4"].ToString(); objCustomerAddress.City = objRow["City"].ToString(); objCustomerAddress.State = objRow["State"].ToString(); objCustomerAddress.ZipCode = objRow["ZIP"].ToString(); objCustomerAddress.Country = objRow["Country"].ToString(); mobjCustomerAddressList.Add(objCustomerAddress); } mintSelectedAddressIndex = -1; if (objDataTable.Rows.Count > 0) { SelectCustomerAddress(0); grpAddress.Enabled = (mobjSelectedLabel.AddressLineCount > 0); } else { MessageBox.Show("No addresses found for customer!", "No Addresses Found", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); lblAddrCt.Text = ""; grpAddress.Enabled = false; } }
public Item(string strItemNo) { SqlCommand objSQLCommand; string strSQL; double dblTemp; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; SqlDataReader objDataReader; Match objMFGMatch, objDashIntMatch; objMFGMatch = Regex.Match(strItemNo, @"-MFG\Z", RegexOptions.IgnoreCase); objDashIntMatch = Regex.Match(strItemNo, @"-[0-9]\Z", RegexOptions.IgnoreCase); objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectTopNItems", new string[] { "1", "item.item", strItemNo, "item.item" }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = objSL8_WTF_DataBaseSettings.SQLConnection.CreateCommand(); objSQLCommand.CommandText = strSQL; objDataReader = objSQLCommand.ExecuteReader(); if (objDataReader.HasRows) { objDataReader.Read(); ItemID = objDataReader["Item"].ToString(); Description = objDataReader["Description"].ToString(); Revision = objDataReader["revision"].ToString(); QtyOnHand = double.TryParse(objDataReader["qty_on_hand"].ToString(), out dblTemp) ? dblTemp : 0.0; //CustomerItem = objDataReader["drawing_nbr"].ToString(); CustomerItem = objDataReader["charfld1"].ToString(); //IsMFG = ItemID.ToUpper().EndsWith("MFG"); IsSubItem = objMFGMatch.Success || objDashIntMatch.Success;//Implemented so isMFG functionality could be utilized for -1, -2, etc items SetAvgUnitWeight(); try { MaxUnitWeightHistoryCount = Settings.GetMaxItemPieceUnitWeightHistoryCount(); } catch { throw new Exception("Unable to get ItemPieceWeightHistoryCount from the Settings Table."); } } else { throw new Exception("Item does not exist!"); } //SL8WebServiceSettings objSL8WebServiceSettings; //DataSet objDataSet; //ValidateItemNo(strItemNo); //objSL8WebServiceSettings = new SL8WebServiceSettings(); //objDataSet = objSL8WebServiceSettings.WebService.LoadDataSet(objSL8WebServiceSettings.SessionToken, "SL.SLItems", "Item, Description", "Item LIKE '" + strItemNo + "'", "Item", string.Empty, 1); //if (objDataSet.Tables[0].Rows.Count > 0) //{ // ItemID = objDataSet.Tables[0].Rows[0]["Item"].ToString(); // Description = objDataSet.Tables[0].Rows[0]["Description"].ToString(); // IsMFG = ItemID.ToUpper().EndsWith("MFG"); // SetAvgUnitWeight(); // MaxUnitWeightHistoryCount = 5; //} //else // throw new Exception("Item does not exist!"); }
public bool IncrementPackingSlipSuffix() { /*I used the command "GRANT UPDATE ON [WTF_App].[dbo].[co] TO WTFApp" to grant the update priveleges * "REVOKE UPDATE ON [WTF_App].[dbo].[co] TO WTFApp" is the SQL to remove the privelege. * Note that I needed to make a new query in order for a successful execution; each time I tried to use an existing Query, it failed */ SqlCommand objSQLCommand; string strSQL; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("UpdatePackingSlipPrintSequence", new string[] { CustomerOrderNumber.Trim() }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = objSL8_WTF_DataBaseSettings.SQLConnection.CreateCommand(); objSQLCommand.CommandText = strSQL; if (objSQLCommand.ExecuteNonQuery() == 1)//appropriate row was affected return true; else return false; }
public Item(string strItemNo) { SqlCommand objSQLCommand; string strSQL; double dblTemp; SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; SqlDataReader objDataReader; Match objMFGMatch, objDashIntMatch; objMFGMatch = Regex.Match(strItemNo, @"-MFG\Z", RegexOptions.IgnoreCase); objDashIntMatch = Regex.Match(strItemNo, @"-[0-9]\Z", RegexOptions.IgnoreCase); objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectTopNItems", new string[] { "1", "item.item", strItemNo, "item.item" }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = objSL8_WTF_DataBaseSettings.SQLConnection.CreateCommand(); objSQLCommand.CommandText = strSQL; objDataReader = objSQLCommand.ExecuteReader(); if (objDataReader.HasRows) { objDataReader.Read(); ItemID = objDataReader["Item"].ToString(); Description = objDataReader["Description"].ToString(); Revision = objDataReader["revision"].ToString(); QtyOnHand = double.TryParse(objDataReader["qty_on_hand"].ToString(), out dblTemp) ? dblTemp : 0.0; //CustomerItem = objDataReader["drawing_nbr"].ToString(); CustomerItem = objDataReader["charfld1"].ToString(); //IsMFG = ItemID.ToUpper().EndsWith("MFG"); IsSubItem = objMFGMatch.Success || objDashIntMatch.Success;//Implemented so isMFG functionality could be utilized for -1, -2, etc items SetAvgUnitWeight(); try { MaxUnitWeightHistoryCount = Settings.GetMaxItemPieceUnitWeightHistoryCount(); } catch { throw new Exception("Unable to get ItemPieceWeightHistoryCount from the Settings Table."); } } else throw new Exception("Item does not exist!"); //SL8WebServiceSettings objSL8WebServiceSettings; //DataSet objDataSet; //ValidateItemNo(strItemNo); //objSL8WebServiceSettings = new SL8WebServiceSettings(); //objDataSet = objSL8WebServiceSettings.WebService.LoadDataSet(objSL8WebServiceSettings.SessionToken, "SL.SLItems", "Item, Description", "Item LIKE '" + strItemNo + "'", "Item", string.Empty, 1); //if (objDataSet.Tables[0].Rows.Count > 0) //{ // ItemID = objDataSet.Tables[0].Rows[0]["Item"].ToString(); // Description = objDataSet.Tables[0].Rows[0]["Description"].ToString(); // IsMFG = ItemID.ToUpper().EndsWith("MFG"); // SetAvgUnitWeight(); // MaxUnitWeightHistoryCount = 5; //} //else // throw new Exception("Item does not exist!"); }
private void GetItems() { SL8_WTF_DataBaseSettings objSL8_WTF_DataBaseSettings; DataTable objDataTable = new DataTable(); SqlDataAdapter objDataAdapter; SqlCommand objSQLCommand; Item objItem; string strSQL; double dblTemp; Match objMFGMatch, objDashIntMatch; Cursor.Current = Cursors.WaitCursor; dgvItems.Visible = false; objSL8_WTF_DataBaseSettings = new SL8_WTF_DataBaseSettings(); strSQL = QueryDefinitions.GetQuery("SelectTopNItems", new string[] { MINTMAXRECORDCOUNT.ToString(), ((ComboBoxItem)cboSortSearch.SelectedItem).Value, txtItem.Text, ((ComboBoxItem)cboSortSearch.SelectedItem).Value }); objSL8_WTF_DataBaseSettings.SQLConnection.Open(); objSQLCommand = new SqlCommand(strSQL, objSL8_WTF_DataBaseSettings.SQLConnection); objDataAdapter = new SqlDataAdapter(objSQLCommand); objDataAdapter.Fill(objDataTable); mobjItemList = new SortableSearchableBindingList<Item>(); if (objDataTable.Rows.Count > 0) { foreach (DataRow objRow in objDataTable.Rows) { objItem = new Item(); objItem.ItemID = objRow["Item"].ToString(); objItem.Description = objRow["Description"].ToString(); objItem.Revision = objRow["revision"].ToString(); objItem.QtyOnHand = double.TryParse(objRow["qty_on_hand"].ToString(), out dblTemp) ? dblTemp : 0.0; objMFGMatch = Regex.Match(objItem.ItemID, @"-MFG\Z", RegexOptions.IgnoreCase); objDashIntMatch = Regex.Match(objItem.ItemID, @"-[0-9]\Z", RegexOptions.IgnoreCase); //objItem.IsMFG = objRow["Item"].ToString().ToUpper().EndsWith("MFG"); objItem.IsSubItem = objMFGMatch.Success || objDashIntMatch.Success;//Implemented so isMFG functionality could be utilized for -1, -2, etc items mobjItemList.Add(objItem); } } else { MessageBox.Show(((ComboBoxItem)cboSortSearch.SelectedItem).Text + " " + txtItem.Text + " not found!", "No Item Found", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); } Cursor.Current = Cursors.Default; dgvItems.Visible = true; dgvItems.DataSource = mobjItemList; dgvItems.Refresh(); }