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