public ActionResult Contact([Bind(Include = "Mid,FirstName,LastName,email,Phone,Messege")] Messeges messeges)
{
if (string.IsNullOrEmpty(Request["firstname"]) || string.IsNullOrEmpty(Request["lastname"]))
{
ModelState.AddModelError("Firstname", "Both Firstname and Lastname fields are required");
}
if (string.IsNullOrEmpty(Request["email"]))
{
ModelState.AddModelError("Email", "The Email field is required");
}
if (string.IsNullOrEmpty(Request["phone"]))
{
ModelState.AddModelError("Phone", "Phone Number is required");
}
if (string.IsNullOrEmpty(Request["messege"]))
{
ModelState.AddModelError("Messege", "The Message field is required");
}
if (ModelState.IsValid)
{
db.Messeges.Add(messeges);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(messeges));
}
} //end AddShippingOrder
public static bool UpdateShippingOrder(ShippingOrder shippingOrder, ShippingOrder origShippingOrder, SqlConnection myConnection)
{
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_UpdateShippingOrder", myConnection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@purchaseOrderID", shippingOrder.PurchaseOrderId);
mySqlCommand.Parameters.AddWithValue("@userID", shippingOrder.UserId ?? Convert.DBNull); //could be null
mySqlCommand.Parameters.AddWithValue("@picked", shippingOrder.Picked ? 1 : 0);
mySqlCommand.Parameters.AddWithValue("@shipDate", (shippingOrder.ShipDate) ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@shippingTermID", shippingOrder.ShippingTermId);
mySqlCommand.Parameters.AddWithValue("@shipToName", shippingOrder.ShipToName ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@shipToAddress", shippingOrder.ShipToAddress ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@shipToCity", shippingOrder.ShipToCity ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@shipToState", shippingOrder.ShipToState ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@shipToZip", shippingOrder.ShipToZip ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@orig_ShippingOrderID", origShippingOrder.ID);
mySqlCommand.Parameters.AddWithValue("@orig_PurchaseOrderID", origShippingOrder.PurchaseOrderId);
mySqlCommand.Parameters.AddWithValue("@orig_UserID", origShippingOrder.UserId ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@orig_Picked", origShippingOrder.Picked ? 1 : 0);
mySqlCommand.Parameters.AddWithValue("@orig_ShipDate", (origShippingOrder.ShipDate) ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@orig_ShippingTermID", origShippingOrder.ShippingTermId);
mySqlCommand.Parameters.AddWithValue("@orig_ShipToName", origShippingOrder.ShipToName ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@orig_ShipToAddress", origShippingOrder.ShipToAddress ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@orig_ShipToCity", origShippingOrder.ShipToCity ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@orig_ShipToState", origShippingOrder.ShipToState ?? Convert.DBNull);
mySqlCommand.Parameters.AddWithValue("@orig_ShipToZip", origShippingOrder.ShipToZip ?? Convert.DBNull);
myConnection.Open();
if (mySqlCommand.ExecuteNonQuery() == 1)
{
return(true);
}
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(false);
} // end UpdateShippingOrder(...)
public static List <CLSPackDetails> FetchCLSPackDetails(ShippingOrder order, SqlConnection myConnection)
{
List <CLSPackDetails> reportLines = new List <CLSPackDetails>();
SqlConnection conn = myConnection;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("proc_GetCLSPackDetails", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ShippingOrderId", order.ID);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var reportLine = new CLSPackDetails(reader.GetInt32(0))
{
ShippingTermId = reader.GetInt32(1),
ShippingVendorId = reader.GetInt32(2),
ShippingDescription = reader.GetString(3),
ShippingVendorName = reader.IsDBNull(4) ? "" : reader.GetString(4),
ShippingTermDescription = reader.GetString(5),
ProductId = reader.GetInt32(6),
ProductName = reader.GetString(7),
Quantity = reader.GetInt32(8),
ShipDate = reader.GetDateTime(9),
ShipToName = reader.IsDBNull(10) ? "" : reader.GetString(10),
ShipToAddress = reader.IsDBNull(11) ? "" : reader.GetString(11),
ShipToCity = reader.IsDBNull(12) ? "" : reader.GetString(12),
ShipToState = reader.IsDBNull(13) ? "" : reader.GetString(13),
ShipToZip = reader.IsDBNull(14) ? "" : reader.GetString(14)
};
reportLines.Add(reportLine);
}
}
reader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
conn.Close();
}
return(reportLines);
}// End FetchCLSPackDetails(.)
} // end ReactivateVendor(..)
public static bool DeleteVendor(Vendor vendor, SqlConnection myConnection)
{
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
SqlCommand mySqlCommand = new SqlCommand("proc_DeleteVendor", myConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.AddWithValue("@VendorID", vendor.Id);
myConnection.Open();
if (mySqlCommand.ExecuteNonQuery() == 1)
{
return(true);
}
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(false);
} // end DeleteVendor(..)
public ActionResult Send(string Message)
{
var PublisherId = User.Identity.GetUserId();
var ResearcherId = (string)Session["UserId"];
// var JobId = (int)Session["JobId"];
var messag = db.Messeges.Where(a => a.PublisherId == PublisherId &&
a.ResearcherId == ResearcherId || //&& a.job.Id == job_id
a.PublisherId == ResearcherId && a.ResearcherId == PublisherId //&& a.job.Id == job_id
);
var m = new Messeges();
m.PublisherId = PublisherId;
m.ResearcherId = ResearcherId;
m.MessegeTime = DateTime.Now;
m.Messege = Message;
m.IdJob = 1;
db.Messeges.Add(m);
db.SaveChanges();
// Console.WriteLine("Length: {0}");
return(View(messag.ToList()));
// return View();
}
}// end GetAllShippingVendors
public static List <ShippingVendor> GetVendorsByActive(Boolean activeState, SqlConnection myConnection)
{
var shippingVendorList = new List <ShippingVendor>();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetShippingVendorsByActive", myConnection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@Active", activeState ? 1 : 0);
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
var shippingVendor = new ShippingVendor(mySqlReader.GetInt32(0))
{
Name = mySqlReader.GetString(1),
Address = mySqlReader.GetString(2),
City = mySqlReader.GetString(3),
State = mySqlReader.GetString(4),
Country = mySqlReader.GetString(5),
Zip = mySqlReader.GetString(6),
Phone = mySqlReader.GetString(7),
Contact = mySqlReader.GetString(8),
ContactEmail = mySqlReader.GetString(9),
Active = mySqlReader.GetBoolean(10)
};
//Add item to list
shippingVendorList.Add(shippingVendor);
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(shippingVendorList);
}//End of FetchTermsByActive(..)
public Flexlist(ref string myQuery)
{
SqlConnection myConnection = GetInventoryDbConnection();
Dictionary <string, string> row = null;
try
{
myConnection.Open();
SqlCommand mySqlCommand = new SqlCommand(myQuery, myConnection);
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
//check to see if any rows were returned, if so, process them
if (mySqlDataReader.HasRows)
{
//get fieldcount for the result of this query
dynamic fieldCount = mySqlDataReader.FieldCount;
fields = new string[fieldCount];
mySqlDataReader.Read();
for (int i = 0; i <= fieldCount - 1; i++)
{
//add the current field name and value of current row
fields[i] = mySqlDataReader.GetName(i);
}
do
{
row = new Dictionary <string, string>();
for (int i = 0; i <= fieldCount - 1; i++)
{
//add the current field name and value to the current row
row.Add(fields[i], (mySqlDataReader.IsDBNull(i) ? "NULL" : mySqlDataReader.GetValue(i).ToString()));
}
rows.Add(row);
row = null;
} while (mySqlDataReader.Read());
}
mySqlDataReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
} //end Public FlexList
public ActionResult DeleteConfirmed(int id)
{
Messeges messeges = db.Messeges.Find(id);
db.Messeges.Remove(messeges);
db.SaveChanges();
return(RedirectToAction("Index"));
}
} // end GetAllVendors(.)
public static List <Vendor> GetAllVendorsByActive(Boolean active, SqlConnection myConnection)
{
List <Vendor> VendorList = new List <Vendor>();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
SqlCommand mySqlCommand = new SqlCommand("proc_GetVendorsByActive", myConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.AddWithValue("Active", active ? 1 : 0);
myConnection.Open();
SqlDataReader mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
Vendor vendor;
while (mySqlReader.Read())
{
vendor = new Vendor(mySqlReader.GetInt32(0));
vendor.Name = mySqlReader.GetString(1);
vendor.Address = mySqlReader.GetString(2);
vendor.City = mySqlReader.GetString(3);
vendor.State = mySqlReader.GetString(4);
vendor.Country = mySqlReader.GetString(5);
vendor.Zip = mySqlReader.GetString(6);
vendor.Phone = mySqlReader.GetString(7);
vendor.Contact = mySqlReader.GetString(8);
vendor.ContactEmail = mySqlReader.GetString(9);
vendor.Active = (Boolean)mySqlReader.GetSqlBoolean(10);
VendorList.Add(vendor);
vendor = null;
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(VendorList);
} // end GetAllVendorsByActive(..)
}// end AddShippingVendor
public static bool UpdateShippingVendor(ShippingVendor shippingVendor, ShippingVendor origShippingVendor, SqlConnection myConnection)
{
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_UpdateShippingVendor", myConnection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@name", shippingVendor.Name);
mySqlCommand.Parameters.AddWithValue("@address", shippingVendor.Address);
mySqlCommand.Parameters.AddWithValue("@city", shippingVendor.City);
mySqlCommand.Parameters.AddWithValue("@state", shippingVendor.State);
mySqlCommand.Parameters.AddWithValue("@country", shippingVendor.Country);
mySqlCommand.Parameters.AddWithValue("@zip", shippingVendor.Zip);
mySqlCommand.Parameters.AddWithValue("@phone", shippingVendor.Phone);
mySqlCommand.Parameters.AddWithValue("@contact", shippingVendor.Contact);
mySqlCommand.Parameters.AddWithValue("@contactEmail", shippingVendor.ContactEmail);
mySqlCommand.Parameters.AddWithValue("@orig_ShippingVendorID", origShippingVendor.Id);
mySqlCommand.Parameters.AddWithValue("@orig_Name", origShippingVendor.Name);
mySqlCommand.Parameters.AddWithValue("@orig_Address", origShippingVendor.Address);
mySqlCommand.Parameters.AddWithValue("@orig_City", origShippingVendor.City);
mySqlCommand.Parameters.AddWithValue("@orig_State", origShippingVendor.State);
mySqlCommand.Parameters.AddWithValue("@orig_Country", origShippingVendor.Country);
mySqlCommand.Parameters.AddWithValue("@orig_Zip", origShippingVendor.Zip);
mySqlCommand.Parameters.AddWithValue("@orig_Phone", origShippingVendor.Phone);
mySqlCommand.Parameters.AddWithValue("@orig_Contact", origShippingVendor.Contact);
mySqlCommand.Parameters.AddWithValue("@orig_ContactEmail", origShippingVendor.ContactEmail);
myConnection.Open();
if (mySqlCommand.ExecuteNonQuery() == 1)
{
return(true);
}
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(false);
}// end UpdateShippingVendor
}// end UpdateShippingVendor
public static ShippingVendor GetShippingVendorById(int id, SqlConnection myConnection)
{
var shippingVendor = new ShippingVendor();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetShippingVendorByID", myConnection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@shippingVendorID", id);
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
shippingVendor = new ShippingVendor(mySqlReader.GetInt32(0))
{
Name = mySqlReader.GetString(1),
Address = mySqlReader.GetString(2),
City = mySqlReader.GetString(3),
State = mySqlReader.GetString(4),
Country = mySqlReader.GetString(5),
Zip = mySqlReader.GetString(6),
Phone = mySqlReader.GetString(7),
Contact = mySqlReader.GetString(8),
ContactEmail = mySqlReader.GetString(9),
Active = mySqlReader.GetBoolean(10)
};
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(shippingVendor);
}// end GetShippingVendorById
public static List <VendorSourceItem> GetVendorSourceItemsByProduct(int productId, SqlConnection myConnection)
{
var vendorSourceItemList = new List <VendorSourceItem>();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetVendorSourceItemsByProduct", myConnection);
mySqlCommand.Parameters.AddWithValue("@productID", productId);
mySqlCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
var vendorSrcItem = new VendorSourceItem(mySqlReader.GetInt32(0), mySqlReader.GetInt32(1))
{
UnitCost = (Decimal)mySqlReader.GetSqlMoney(2),
MinQtyToOrder = mySqlReader.GetInt32(3),
ItemsPerCase = mySqlReader.GetInt32(4),
Name = mySqlReader.GetString(5),
Active = true
};
//Add item to list
vendorSourceItemList.Add(vendorSrcItem);
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(vendorSourceItemList);
}
public ActionResult Edit([Bind(Include = "Mid,FirstName,LastName,email,Phone,Messege,IsReleased")] Messeges messeges)
{
if (ModelState.IsValid)
{
db.Entry(messeges).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(messeges));
}
} //end UnpickShippingOrderLineItem(..)
public static List <ShippingOrderLineItem> GetAllShippingOrderLineItems(SqlConnection myConnection)
{
var shippingOrderLineItemList = new List <ShippingOrderLineItem>();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetAllShippingOrderLineItems", myConnection)
{
CommandType = CommandType.StoredProcedure
};
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
// shippingOrderId and productId added in construction below
var shippingLineItem = new ShippingOrderLineItem(mySqlReader.GetInt32(0), mySqlReader.GetInt32(1))
{
ProductName = mySqlReader.GetString(2),
Quantity = mySqlReader.GetInt32(3),
ProductLocation = mySqlReader[4] as string,
IsPicked = mySqlReader.GetBoolean(5)
};
shippingOrderLineItemList.Add(shippingLineItem);
} //end while
} // end if
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(shippingOrderLineItemList);
} //end GetAllShippingOrderLineItems(.)
} // end AddVendorObject(..)
public static Vendor GetVendor(int vendorId, SqlConnection myConnection) //untested - 2/6/14 AJW
{
Vendor vendor = new Vendor();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
SqlCommand mySqlCommand = new SqlCommand("proc_GetVendor", myConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.AddWithValue("@VendorID", vendorId);
myConnection.Open();
SqlDataReader mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
vendor = new Vendor(mySqlReader.GetInt32(0));
vendor.Name = mySqlReader.GetString(1);
vendor.Address = mySqlReader.GetString(2);
vendor.City = mySqlReader.GetString(3);
vendor.State = mySqlReader.GetString(4);
vendor.Country = mySqlReader.GetString(5);
vendor.Zip = mySqlReader.GetString(6);
vendor.Phone = mySqlReader.GetString(7);
vendor.Contact = mySqlReader.GetString(8);
vendor.ContactEmail = mySqlReader.GetString(9);
vendor.Active = (Boolean)mySqlReader.GetSqlBoolean(10);
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(vendor);
} // end GetVendor(..)
public static VendorOrderLineItem Get(VendorOrder vendorOrder, Product product, SqlConnection myConnection)
{
VendorOrderLineItem vendorOrderLineItem = null;
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetVendorOrderLineItem", myConnection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@VendorOrderID", vendorOrder.Id);
mySqlCommand.Parameters.AddWithValue("@ProductID", product.Id);
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
vendorOrderLineItem = new VendorOrderLineItem(mySqlReader.GetInt32(0), mySqlReader.GetInt32(1))
{
QtyOrdered = mySqlReader.GetInt32(2),
QtyReceived = mySqlReader.GetInt32(3),
QtyDamaged = mySqlReader.GetInt32(4)
};
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(vendorOrderLineItem);
}
}//end GetShippingTermsById
public static List <ShippingTerm> GetAllShippingTerms(SqlConnection myConnection)
{
var shippingTermList = new List <ShippingTerm>();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetAllShippingTerms", myConnection)
{
CommandType = CommandType.StoredProcedure
};
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
var shippingTerm = new ShippingTerm(mySqlReader.GetInt32(0))
{
ShippingVendorId = mySqlReader.GetInt32(1),
Description = mySqlReader.GetString(2),
ShippingVendorName = mySqlReader.GetString(3),
Active = mySqlReader.GetBoolean(4)
};
//Add item to list
shippingTermList.Add(shippingTerm);
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(shippingTermList);
}//end GetAllShippingTerms
public static VendorSourceItem GetVendorSourceItem(int vendorSrcItemId, SqlConnection myConnection)
{
var vendorSrcItem = new VendorSourceItem();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetVendorSourceItem", myConnection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@productID", vendorSrcItem.ProductID);
mySqlCommand.Parameters.AddWithValue("@vendorID", vendorSrcItem.VendorID);
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
vendorSrcItem = new VendorSourceItem(mySqlReader.GetInt32(0), mySqlReader.GetInt32(1))
{
UnitCost = (Decimal)mySqlReader.GetSqlMoney(2),
MinQtyToOrder = mySqlReader.GetInt32(3),
ItemsPerCase = mySqlReader.GetInt32(4),
Active = (Boolean)mySqlReader.GetSqlBoolean(5)
};
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(vendorSrcItem);
}
}//End of deactivateProduct(..)
public static Boolean DeleteProduct(Product product, SqlConnection connection)
{
//?? Null-coalescing operator.
//If the connection is null a new connection will be returned.
SqlConnection conn = connection ?? GetInventoryDbConnection();
try
{
//Establishes the connection.
conn.Open();
//Creates the command object, passing the SP and connection object.
SqlCommand sqlCmd = new SqlCommand("proc_DeleteProduct", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@ProductID", product.Id);
sqlCmd.Parameters.AddWithValue("@OnHand", product.reserved);
sqlCmd.Parameters.AddWithValue("@Available", product.available);
sqlCmd.Parameters.AddWithValue("@Description", product.description);
sqlCmd.Parameters.AddWithValue("@Location", product.location);
sqlCmd.Parameters.AddWithValue("@UnitPrice", product.unitPrice);
sqlCmd.Parameters.AddWithValue("@ShortDesc", product.Name);
sqlCmd.Parameters.AddWithValue("@ReorderThreshold", product._reorderThreshold);
sqlCmd.Parameters.AddWithValue("@ReorderAmount", product._reorderAmount);
sqlCmd.Parameters.AddWithValue("@OnOrder", product._onOrder);
sqlCmd.Parameters.AddWithValue("@ShippingDimensions", product._shippingDemensions);
sqlCmd.Parameters.AddWithValue("@ShippingWeight", product._shippingWeight);
sqlCmd.Parameters.AddWithValue("@Active", product.Active);
//If the procedure returns 1 set to true, if 0 remain false.
if (sqlCmd.ExecuteNonQuery() == 1)
{
return(true);
}
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
conn.Close();
}
return(false);
}//End of deleteProduct(..)
public ActionResult Create([Bind(Include = "Mid,FirstName,LastName,email,Phone,Messege,IsReleased")] Messeges messeges)
{
if (ModelState.IsValid)
{
db.Messeges.Add(messeges);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(messeges));
}
public static VendorOrder GetVendorOrderByVendorAndDate(int vendorId, SqlConnection connection)
{
VendorOrder newVendorOrder;
connection = connection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetVendorOrderByVendorAndDate", connection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@VendorId", vendorId);
connection.Open();
var myReader = mySqlCommand.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
newVendorOrder = new VendorOrder(myReader.GetInt32(0), myReader.GetInt32(1))
{
DateOrdered = (DateTime)myReader.GetSqlDateTime(2),
NumberOfShipments = myReader.GetInt32(3),
Finalized = myReader.GetBoolean(4),
Active = myReader.GetBoolean(5)
};
return(newVendorOrder);
}
;
}
myReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
connection.Close();
}
throw new ApplicationException("Vendor Order from vendor: "
+ vendorId + "not found");
}
}// end UpdateShippingTerms
public static ShippingTerm GetShippingTermsById(int id, SqlConnection myConnection)
{
var shippingTerm = new ShippingTerm();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetShippingTermsByID", myConnection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@shippingTermID", id);
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
shippingTerm = new ShippingTerm(mySqlReader.GetInt32(0))
{
ShippingVendorId = mySqlReader.GetInt32(1),
Description = mySqlReader.GetString(2),
ShippingVendorName = mySqlReader.GetString(3),
Active = mySqlReader.GetBoolean(10)
};
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(shippingTerm);
}//end GetShippingTermsById
public static List <RoleControl> GetControlsForRole(int roleID)
{
List <RoleControl> roleControls = new List <RoleControl>();
SqlConnection conn = GetInventoryDbConnection();
try
{
conn.Open();
SqlCommand sqlCmd = new SqlCommand("proc_GetControlsForRole", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@RoleID", roleID);
SqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var roleControl = new RoleControl()
{
RoleID = reader.GetInt32(reader.GetOrdinal("RoleID")),
FormName = reader.GetString(reader.GetOrdinal("Form")),
Name = reader.GetString(reader.GetOrdinal("Control")),
Visible = reader.GetBoolean(reader.GetOrdinal("Visible")),
Disabled = reader.GetBoolean(reader.GetOrdinal("Disabled")),
};
roleControls.Add(roleControl);
}
}
reader.Close();
}
#region Exceptions
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
conn.Close();
}
#endregion
return(roleControls);
}
public static List <VendorOrderLineItem> GetExceptionItems(VendorOrder vendorOrder, SqlConnection myConnection)
{
var exceptionItemsList = new List <VendorOrderLineItem>();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetExceptionItems", myConnection)
{
CommandType = CommandType.StoredProcedure
};
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
var vendorOrderLineItem = new VendorOrderLineItem(mySqlReader.GetInt32(0), mySqlReader.GetInt32(1))
{
QtyOrdered = mySqlReader.GetInt32(2),
QtyReceived = mySqlReader.GetInt32(3),
QtyDamaged = mySqlReader.GetInt32(4)
};
exceptionItemsList.Add(vendorOrderLineItem);
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(exceptionItemsList);
}
} // end GetAllVendorsByActive(..)
public static Boolean UpdateVendor(Vendor vendor, Vendor origVendor, SqlConnection myConnection)
{
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
SqlCommand mySqlCommand = new SqlCommand("proc_UpdateVendor", myConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.AddWithValue("@Name", vendor.Name);
mySqlCommand.Parameters.AddWithValue("@Address", vendor.Address);
mySqlCommand.Parameters.AddWithValue("@City", vendor.City);
mySqlCommand.Parameters.AddWithValue("@State", vendor.State);
mySqlCommand.Parameters.AddWithValue("@Country", vendor.Contact);
mySqlCommand.Parameters.AddWithValue("@Zip", vendor.Zip);
mySqlCommand.Parameters.AddWithValue("@Phone", vendor.Phone);
mySqlCommand.Parameters.AddWithValue("@Contact", vendor.Contact);
mySqlCommand.Parameters.AddWithValue("@ContactEmail", vendor.ContactEmail);
mySqlCommand.Parameters.AddWithValue("@original_VendorID", origVendor.Id);
mySqlCommand.Parameters.AddWithValue("@original_Name", origVendor.Name);
mySqlCommand.Parameters.AddWithValue("@original_Address", origVendor.Address);
mySqlCommand.Parameters.AddWithValue("@original_City", origVendor.City);
mySqlCommand.Parameters.AddWithValue("@original_State", origVendor.State);
mySqlCommand.Parameters.AddWithValue("@original_Country", origVendor.Country);
mySqlCommand.Parameters.AddWithValue("@original_Zip", origVendor.Zip);
mySqlCommand.Parameters.AddWithValue("@original_Phone", origVendor.Phone);
mySqlCommand.Parameters.AddWithValue("@original_Contact", origVendor.Contact);
mySqlCommand.Parameters.AddWithValue("@original_ContactEmail", origVendor.ContactEmail);
myConnection.Open();
if (mySqlCommand.ExecuteNonQuery() == 1)
{
return(true);
}
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(false);
} // end UpdateVendor(...)
public static List <Employee> GetEmployeesByActive(Boolean active)
{
List <Employee> employees = new List <Employee>();
SqlConnection conn = GetInventoryDbConnection();
try
{
conn.Open();
SqlCommand sqlCmd = new SqlCommand("proc_GetEmployeesByActive", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@Active", active ? 1 : 0);
SqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var employee = new Employee(reader.GetInt32(reader.GetOrdinal("UserID")))
{
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
PhoneNumber = reader.GetString(reader.GetOrdinal("PhoneNumber")),
RoleID = reader.GetInt32(reader.GetOrdinal("RoleID")),
Active = reader.GetBoolean(reader.GetOrdinal("Active"))
};
employees.Add(employee);
}
}
reader.Close();
}
#region Exceptions
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
conn.Close();
}
#endregion
return(employees);
}
public static Dictionary <int, State> GetAllStates(SqlConnection connection)
{
Dictionary <int, State> states = new Dictionary <int, State>();
SqlConnection conn = connection ?? GetInventoryDbConnection();
try
{
conn.Open();
SqlCommand sqlCmd = new SqlCommand("proc_GetAllStates", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var state = new State(reader.GetInt32(0))
{
StateCode = reader.GetString(reader.GetOrdinal("StateCode")),
StateName = reader.GetString(reader.GetOrdinal("StateName")),
FirstZipCode = reader.GetInt32(reader.GetOrdinal("FirstZipCode")),
LastZipCode = reader.GetInt32(reader.GetOrdinal("LastZipCode"))
};
states.Add(state.Id, state);
}
}
reader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
conn.Close();
}
return(states);
}
} //end GetShippingOrderLineItemsById(..)
public static ShippingOrderLineItem GetShippingOrderLineItemById(int productId, int shippingOrderID, SqlConnection myConnection)
{
var shippingLineItem = new ShippingOrderLineItem();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetShippingOrderLineItem", myConnection);
mySqlCommand.Parameters.AddWithValue("@productID", productId);
mySqlCommand.Parameters.AddWithValue("@shippingOrderID", shippingOrderID);
mySqlCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
SqlDataReader mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
shippingLineItem = new ShippingOrderLineItem(mySqlReader.GetInt32(0), mySqlReader.GetInt32(1))
{
ProductName = mySqlReader.GetString(2),
Quantity = mySqlReader.GetInt32(3),
ProductLocation = mySqlReader[4] as string,
IsPicked = mySqlReader.GetBoolean(5)
};
}
} // End If
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(shippingLineItem);
}
} // end GetRoles()
public static List <Role> GetRolesByActive(Boolean active)
{
List <Role> roles = new List <Role>();
SqlConnection conn = GetInventoryDbConnection();
try
{
conn.Open();
SqlCommand sqlCmd = new SqlCommand("proc_GetRolesByActive", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@Active", active ? 1 : 0);
SqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var role = new Role(reader.GetInt32(0))
{
Name = reader.GetString(reader.GetOrdinal("Name")),
Description = reader.GetString(reader.GetOrdinal("Description")),
Active = reader.GetBoolean(reader.GetOrdinal("Active")),
};
roles.Add(role);
}
}
reader.Close();
}
#region Exceptions
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
conn.Close();
}
#endregion
return(roles);
}
public static List <VendorOrder> GetAll(SqlConnection connection)
{
List <VendorOrder> allOrders = new List <VendorOrder>();
connection = connection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetAllVendorOrders", connection)
{
CommandType = CommandType.StoredProcedure
};
connection.Open();
var myReader = mySqlCommand.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
var newVendorOrder = new VendorOrder(myReader.GetInt32(0), myReader.GetInt32(1))
{
DateOrdered = (DateTime)myReader.GetSqlDateTime(2),
NumberOfShipments = myReader.GetInt32(3),
Finalized = myReader.GetBoolean(4),
Active = myReader.GetBoolean(5)
};
allOrders.Add(newVendorOrder);
}
;
}
myReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
connection.Close();
}
return(allOrders);
}
