public void UpdateLot(Lots lot)
{
InventorySprocs sproc = new InventorySprocs();
//Build customProp Ids and Values
string propValsId = lot.weightId + "," + lot.widthId + "," + lot.heightId + "," + lot.volumeId + "," + lot.nextInvDateId + "," + lot.lastInvDateId;
string propVals = lot.weight + "," + lot.width + "," + lot.height + "," + lot.volume + "," + lot.nextInvDate + "," + lot.lastInvDate;
sproc.UpdateLot(lot, propValsId, propVals);
}
public string AddLot(Lots lot)
{
InventorySprocs sproc = new InventorySprocs();
//Build customProp Ids and Values
string propValsId = "1,2,3,4,5,6";
string propVals = lot.weight + "," + lot.width + "," + lot.height + "," + lot.volume + "," + lot.nextInvDate + "," + lot.lastInvDate;
return sproc.InsertLot(lot.serialNo,lot.price.ToString(),
lot.purchaseDate, lot.expirationDate, propValsId, propVals, lot.notes);
}
public List<Lots> GetInventoryReport(string lotId)
{
StoredProcedure sproc = new StoredProcedure();
MySqlDataReader rdr = null;
var list = new List<KeyValuePair<string, string>>();
double val;
//Add Parameters
list.Add(new KeyValuePair<string, string>("@lotID", lotId));
MySqlCommand cmd = sproc.Command("INV_GetInventoryReport", list, null, false);
rdr = cmd.ExecuteReader();
List<Lots> results = new List<Lots>();
// iterate through results, printing each to console
while (rdr.Read())
{
Lots lot = new Lots();
lot.Id = Convert.ToInt32(rdr["id"]);
lot.serialNo = Convert.ToString(rdr["serial_no"]);
lot.brand = Convert.ToString(rdr["brand"]);
lot.type = Convert.ToString(rdr["type"]);
lot.typeId = Convert.ToString(rdr["typeID"]);
lot.location = Convert.ToString(rdr["location"]);
lot.locationId = Convert.ToString(rdr["locationID"]);
lot.price = Double.TryParse(Convert.ToString(rdr["price"]), out val) ? val : Double.NaN;
lot.weightId = Convert.ToString(rdr["weight_id"]);
lot.weight = Double.TryParse(Convert.ToString(rdr["weight"]), out val) ? val : Double.NaN;
lot.widthId = Convert.ToString(rdr["width_id"]);
lot.width = Double.TryParse(Convert.ToString(rdr["width"]), out val) ? val : Double.NaN;
lot.heightId = Convert.ToString(rdr["height_id"]);
lot.height = Double.TryParse(Convert.ToString(rdr["height"]), out val) ? val : Double.NaN;
lot.volumeId = Convert.ToString(rdr["volume_id"]);
lot.volume = Double.TryParse(Convert.ToString(rdr["volume"]), out val) ? val : Double.NaN;
lot.purchaseDate = Convert.ToString(rdr["purchase_date"]);
lot.expirationDate = Convert.ToString(rdr["expiration_date"]);
lot.nextInvDateId = Convert.ToString(rdr["last_inv_date_id"]);
lot.nextInvDate = Convert.ToString(rdr["last_inv_date"]);
lot.lastInvDateId = Convert.ToString(rdr["next_inv_date_id"]);
lot.lastInvDate = Convert.ToString(rdr["next_inv_date"]);
lot.notes = Convert.ToString(rdr["notes"]);
results.Add(lot);
}
return results;
}
/// <summary>
/// Update values of a specific lot
/// </summary>
/// <param name="lot"></param>
/// <param name="propValsId"></param>
/// <param name="propVals"></param>
public void UpdateLot(Lots lot, string propValsId, string propVals)
{
try
{
StoredProcedure sproc = new StoredProcedure();
MySqlDataReader rdr = null;
var list = new List<KeyValuePair<string, string>>();
//Add Parameters
list.Add(new KeyValuePair<string, string>("@lotID", Convert.ToString(lot.Id)));
list.Add(new KeyValuePair<string, string>("@serialNo", lot.serialNo));
list.Add(new KeyValuePair<string, string>("@uomID", "1"));
list.Add(new KeyValuePair<string, string>("@quantity", "1"));
list.Add(new KeyValuePair<string, string>("@price", Convert.ToString(lot.price)));
list.Add(new KeyValuePair<string, string>("@dateAdded", lot.purchaseDate));
list.Add(new KeyValuePair<string, string>("@expirationDate", lot.expirationDate));
list.Add(new KeyValuePair<string, string>("@userName", "RSW"));
list.Add(new KeyValuePair<string, string>("@matTypeID", lot.typeId));
list.Add(new KeyValuePair<string, string>("@propValsID", propValsId));
list.Add(new KeyValuePair<string, string>("@propVals", propVals));
list.Add(new KeyValuePair<string, string>("@userNotes", String.IsNullOrEmpty(lot.notes)?"":lot.notes));
MySqlCommand cmd = sproc.Command("INV_UpdateLot", list, null, false);
// execute the command
cmd.ExecuteNonQuery();
//Update Lot Location in case there was a change
if (!String.IsNullOrEmpty(lot.locationId))
{
MoveLot(lot, "RSW");
}
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// Return a list of Serial Numbers based on a search parameter
/// </summary>
/// <param name="serialNo"></param>
/// <returns></returns>
public List<Lots> SearchSerialNo(String serialNo)
{
StoredProcedure sproc = new StoredProcedure();
MySqlDataReader rdr = null;
var list = new List<KeyValuePair<string, string>>();
//Add Parameters
serialNo = String.IsNullOrEmpty(serialNo) ? null : serialNo+"*"; //Need to concatenate a wild card for full text serach
list.Add(new KeyValuePair<string, string>("@serialSearchTerm",serialNo));
MySqlCommand cmd = sproc.Command("INV_SearchSerialNo", list, null, false);
rdr = cmd.ExecuteReader();
List<Lots> results = new List<Lots>();
// iterate through results, printing each to console
while (rdr.Read())
{
Lots lot = new Lots();
lot.Id = Convert.ToInt32(rdr["id"]);
lot.serialNo = rdr["serial_no"].ToString();
results.Add(lot);
}
return results;
}
/// <summary>
/// Procedure for moving lots to track lot movement history
/// </summary>
/// <param name="lot"></param>
/// <param name="userName"></param>
public void MoveLot(Lots lot, string userName)
{
try
{
StoredProcedure sproc = new StoredProcedure();
MySqlDataReader rdr = null;
var list = new List<KeyValuePair<string, string>>();
//Add Parameters
list.Add(new KeyValuePair<string, string>("@lotIDs", Convert.ToString(lot.Id)));
list.Add(new KeyValuePair<string, string>("@newValue", lot.locationId));
list.Add(new KeyValuePair<string, string>("@user", userName));
MySqlCommand cmd = sproc.Command("INV_UpdateLotLocation", list, null, false);
// execute the command
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw (ex);
}
}