public JsonResult RemoveUser(string userName)
{
MethodStatus status = new MethodStatus();
//using (SqlConnection connection = new SqlConnection("ConnectionStringHere"))
//{
// using (SqlCommand command = new SqlCommand())
// {
// command.Connection = connection; // <== lacking
// command.CommandType = CommandType.Text;
// command.CommandText = "INSERT into tbl_staff (staffName, userID, idDepartment) VALUES (@staffName, @userID, @idDepart)";
// command.Parameters.AddWithValue("@staffName", name);
// command.Parameters.AddWithValue("@userID", userId);
// command.Parameters.AddWithValue("@idDepart", idDepart);
// try
// {
// connection.Open();
// int recordsAffected = command.ExecuteNonQuery();
// }
// catch (SqlException)
// {
// // error here
// }
// finally
// {
// connection.Close();
// }
// }
//}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public JsonResult AddIssue(string locationName, int issuedType)
{
MethodStatus status = new MethodStatus();
Random random = new Random();
int randomNumber = random.Next(0, 100);
Configuration config = new Configuration();
List <InventoryItem> InventoryItems = new List <InventoryItem>();
SqlConnection conn = new SqlConnection(config.ConnectionString());
try
{
conn.Open();
string sql = "INSERT INTO Issuedto(IssuedToDescription,IssuedToTypeID) VALUES(@param1,@param2)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@param1", SqlDbType.VarChar, 50).Value = locationName;
cmd.Parameters.Add("@param2", SqlDbType.Int).Value = issuedType;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.InnerException.ToString();
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public JsonResult AddFromCheckout(ConsumerManager.Consumer consumer)
{
if (ModelState.IsValid)
{
var consCompstatus = new MethodStatus <ConsumerCompanyManager.ConsumerCompany>();
if (!consumer.ConsumerCompanyName.IsNullOrEmpty())
{
consCompstatus =
CreateConsumerCompany(new ConsumerCompanyModel
{
CompanyName = consumer.ConsumerCompanyName.Trim()
});
var consComp = consCompstatus.Data;
if (!consCompstatus.IsError)
{
consumer.ConsumerCompanyId = consComp.ConsumerCompanyId;
}
}
//if (!consCompstatus.IsError)
//{
// var updateConsumer = _consumerManager.Create(consumer);
// if (!updateConsumer.IsError && !updateConsumer.Message.Contains("error"))
// {
// consumer = updateConsumer.Data;
// }
// else
// {
// updateConsumer.HandleFrontEndError();
// }
//}
}
return(Json(new { Consumer = consumer }, JsonRequestBehavior.AllowGet));
}
public JsonResult AddNewItem(string ItemName, int ItemQuantity, int ItemReorder, string ItemDescription)
{
MethodStatus status = new MethodStatus();
List <InventoryItem> InventoryItems = new List <InventoryItem>();
SqlConnection conn = new SqlConnection("SERVER=173.88.245.82,1433;Database=Inventory;USER ID=Capstone;PASSWORD=abc123");
try
{
conn.Open();
string sql = "INSERT INTO Inventory(IsOnKSUInventory,ItemName,InventoryDescription,InventoryQuantity,InventoryReorderQuantity,InventoryTypeID,InventoryLocationID) VALUES(@param1,@param2,@param3,@param4,@param5,@param6,@param7)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@param1", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@param2", SqlDbType.VarChar, 50).Value = ItemName;
cmd.Parameters.Add("@param3", SqlDbType.VarChar, 50).Value = ItemDescription;
cmd.Parameters.Add("@param4", SqlDbType.Int).Value = ItemQuantity;
cmd.Parameters.Add("@param5", SqlDbType.Int).Value = ItemReorder;
cmd.Parameters.Add("@param6", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@param7", SqlDbType.Int).Value = 1;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.InnerException.ToString();
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public JsonResult AddNewLocation(string locationName)
{
MethodStatus status = new MethodStatus();
Random random = new Random();
int randomNumber = random.Next(0, 100);
List <InventoryItem> InventoryItems = new List <InventoryItem>();
SqlConnection conn = new SqlConnection("SERVER=173.88.245.82,1433;Database=Inventory;USER ID=Capstone;PASSWORD=abc123");
try
{
conn.Open();
string sql = "INSERT INTO InventoryLocation(InventoryLocationDescription) VALUES(@param1)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@param1", SqlDbType.VarChar, 50).Value = locationName;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.InnerException.ToString();
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public JsonResult UpdateQuantities(string ItemName, int ItemQuantity)
{
MethodStatus status = new MethodStatus();
SqlConnection conn = new SqlConnection("SERVER=173.88.245.82,1433;Database=Inventory;USER ID=Capstone;PASSWORD=abc123");
try
{
conn.Open();
string query = "UPDATE Inventory SET InventoryQuantity=" + ItemQuantity + " WHERE ItemName=" + ItemName;
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "UPDATE Inventory SET InventoryQuantity = @quantity WHERE ItemName = @name";
command.Parameters.AddWithValue("@quantity", ItemQuantity);
command.Parameters.AddWithValue("@name", ItemName);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.InnerException.ToString();
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public JsonResult UpdateQuantities(string ItemName, int ItemQuantity)
{
MethodStatus status = new MethodStatus();
Configuration config = new Configuration();
SqlConnection conn = new SqlConnection(config.ConnectionString());
try
{
conn.Open();
string query = "UPDATE Inventory SET InventoryQuantity=" + ItemQuantity + " WHERE ItemName=" + ItemName;
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "UPDATE Inventory SET InventoryQuantity = @quantity WHERE ItemName = @name";
command.Parameters.AddWithValue("@quantity", ItemQuantity);
command.Parameters.AddWithValue("@name", ItemName);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.Message;
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
/// <summary>
/// Constructor
/// </summary>
public RestResponse(MethodStatus methodStatus, string rawResponse,
HttpStatusCode statusCode, string statusDescription)
{
Status = methodStatus;
RawResponse = rawResponse;
StatusCode = statusCode;
StatusDescription = statusDescription;
}
public JsonResult LoadLocationData(string locationName)
{
MethodStatus status = new MethodStatus();
status.success = true;
return(Json(status, JsonRequestBehavior.DenyGet));
}
public static void UpdateMethodLog(string token, string methodName, MethodStatus status, long?timeTaken = null, string error = null)
{
try
{
if (IsServer)
{
FbConnection connection = GetConnection();
Open();
using (FbCommand insertData = connection.CreateCommand())
{
// ReSharper disable once ConvertIfStatementToConditionalTernaryExpression
if (timeTaken != null)
{
insertData.CommandText = "insert into MethodLogs values (@token, @methodName, @status, @time, @dt, @edt";
}
else
{
insertData.CommandText = "insert into MethodLogs values (@token, @methodName, @status, NULL, @dt, @edt";
}
if (!string.IsNullOrEmpty(error))
{
insertData.CommandText += ", @err)";
}
else
{
insertData.CommandText += ", NULL)";
}
insertData.Parameters.Clear();
insertData.Parameters.Add("@token", FbDbType.VarChar, 50).Value = token;
insertData.Parameters.Add("@methodName", FbDbType.VarChar, 200).Value = methodName;
insertData.Parameters.Add("@status", FbDbType.Integer).Value = status;
if (timeTaken != null)
{
insertData.Parameters.Add("@time", FbDbType.BigInt).Value = timeTaken.Value;
}
insertData.Parameters.Add("@dt", FbDbType.TimeStamp).Value = DateTime.Now;
insertData.Parameters.Add("@edt", FbDbType.TimeStamp).Value = DateTime.Now;
if (!string.IsNullOrEmpty(error))
{
insertData.Parameters.Add("@err", FbDbType.Text).Value = error;
}
insertData.ExecuteNonQuery();
}
}
}
catch (Exception)
{
//eat exceptions of firebird. TODO
}
}
public ActionResult Add(ConsumerManager.Consumer consumer)
{
ViewBag.ActionLinks = new List <NavigationAction>();
ViewBag.Navigation = new List <NavigationAction>();
var updateConsumer = new ClientResponse <ConsumerManager.Consumer> {
Data = consumer
};
var consCompstatus = new MethodStatus <ConsumerCompanyManager.ConsumerCompany>();
if (!consumer.ConsumerCompanyName.IsNullOrEmpty())
{
consCompstatus =
CreateConsumerCompany(new ConsumerCompanyModel
{
CompanyName = consumer.ConsumerCompanyName.Trim()
});
var consComp = consCompstatus.Data;
if (!consCompstatus.IsError)
{
consumer.ConsumerCompanyId = consComp.ConsumerCompanyId;
}
else
{
updateConsumer.ReturnCode = ClientReturnCode.Failed;
updateConsumer.Message = consCompstatus.Message;
}
}
else
{
updateConsumer.ReturnCode = ClientReturnCode.Failed;
updateConsumer.Message = "Company Name is required!";
}
if (updateConsumer.ReturnCode == ClientReturnCode.Failed)
{
return(View(consumer));
}
//var status = _consumerManager.Create(consumer);
var status = new MethodStatus <ConsumerManager.Consumer>();
if (!status.IsError)
{
ViewBag.Consumer = status.Data;
ViewBag.Result = "Consumer Added Successfully.";
return(RedirectToAction("Edit", new { id = status.Data.ConsumerId = 1 }));
//return RedirectToAction("Edit");
}
return(View(status.Data));
}
public JsonResult SendMail(string reportLocation)
{
var mail = new MailReport();
MethodStatus status = new MethodStatus();
if (string.IsNullOrWhiteSpace(reportLocation))
{
status.success = false;
}
else
{
status.success = mail.SendReport(reportLocation);
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public ActionResult Edit(ConsumerManager.Consumer consumer)
{
var updateConsumer = new MethodStatus <ConsumerManager.Consumer> {
Data = consumer
};
if (consumer.ConsumerCompanyId <= 0)
{
if (!consumer.ConsumerCompanyName.IsNullOrEmpty())
{
var response =
CreateConsumerCompany(new ConsumerCompanyModel
{
CompanyName = consumer.ConsumerCompanyName.Trim()
});
if (!response.IsError)
{
consumer.ConsumerCompanyId = response.Data.ConsumerCompanyId;
}
else
{
updateConsumer.IsError = true;
updateConsumer.Message = response.Message;
ModelState.AddModelError("Consumer Company Name", response.Message);
}
}
else
{
updateConsumer.IsError = true;
updateConsumer.Message = "Could not save consumer at this time, contact administration.";
ModelState.AddModelError("Consumer Company Name", "Company is required.");
}
if (updateConsumer.IsError)
{
return(View(consumer));
}
}
if (ModelState.IsValid)
{
//updateConsumer = _consumerManager.Update(consumer);
}
return(View(consumer));
}
public JsonResult RemoveItem(string ItemName)
{
MethodStatus status = new MethodStatus();
Configuration config = new Configuration();
SqlConnection conn = new SqlConnection(config.ConnectionString());
try
{
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "SELECT InventoryID FROM Inventory Where ItemName = @itemname";
cmd.Parameters.AddWithValue("@itemname", ItemName);
string str = Convert.ToString(cmd.ExecuteScalar());
int locationid = Int32.Parse(str);
int hasinventory = 0;
cmd.CommandText = "SELECT COUNT (InventoryID) FROM Issued WHERE InventoryID = @param1";
cmd.Parameters.AddWithValue("@param1", locationid);
string str2 = Convert.ToString(cmd.ExecuteScalar());
hasinventory = Int32.Parse(str2);
if (hasinventory == 0)
{
cmd.CommandText = "DELETE FROM Inventory WHERE ItemName = @word";
cmd.Parameters.AddWithValue("@word", ItemName);
cmd.ExecuteNonQuery();
}
else
{
status.success = false;
status.Message = "Cannot remove items that have been issued";
}
}
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.Message;
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public JsonResult RemoveItem(string ItemName)
{
MethodStatus status = new MethodStatus();
SqlConnection conn = new SqlConnection("SERVER=173.88.245.82,1433;Database=Inventory;USER ID=Capstone;PASSWORD=abc123");
try
{
conn.Open();
using (SqlCommand command = new SqlCommand("DELETE FROM Inventory WHERE ItemName='" + ItemName + "'", conn))
{
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.InnerException.ToString();
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public static void UpdateMethodLog(string token, string methodName, MethodStatus status, long?timeTaken = null,
string error = null)
{
IFbService client = null;
var myChannelFactory = GetClient();
try
{
client = myChannelFactory.CreateChannel();
client.UpdateMethodLog(token, methodName, status, timeTaken,
error + " - From " + Environment.MachineName);
// ReSharper disable once SuspiciousTypeConversion.Global
((ICommunicationObject)client).Close();
}
catch
{
if (client != null)
{
// ReSharper disable once SuspiciousTypeConversion.Global
((ICommunicationObject)client).Abort();
}
}
}
public JsonResult RemoveLocation(string locationName)
{
MethodStatus status = new MethodStatus();
SqlConnection conn = new SqlConnection("SERVER=173.88.245.82,1433;Database=Inventory;USER ID=Capstone;PASSWORD=abc123");
try
{
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "DELETE FROM InventoryLocation WHERE InventoryLocationDescription = @word";
cmd.Parameters.AddWithValue("@word", locationName);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.InnerException.ToString();
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
private MethodStatus <ConsumerCompanyManager.ConsumerCompany> CreateConsumerCompany(ConsumerCompanyModel model)
{
var newCompany = new MethodStatus <ConsumerCompanyManager.ConsumerCompany>
{
IsError = true,
Message = "Company name already exists, please add a different name to continue."
};
var manager = new ConsumerCompanyManager();
var companyId = FrontEndSession.Instance.CompanyId.GetValueOrDefault(0);
//var foundName = manager.Search(new ConsumerCompanyManager.ConsumerCompanySearch
//{
// CompanyId = companyId,
// CompanyName = model.CompanyName
//});
//if (foundName.ReturnCode != ClientReturnCode.Failed)
//{
// if (foundName.TotalRecords == 0)
// {
// newCompany = manager.Update(new ConsumerCompanyManager.ConsumerCompany
// {
// SiteId = FrontEndSession.Instance.SiteId.GetValueOrDefault(0),
// CompanyId = companyId,
// CompanyName = model.CompanyName
// });
// }
// else
// {
// newCompany = manager.Read(foundName.Data[0].ConsumerCompanyId);
// }
// return newCompany;
//}
return(newCompany);
}
public JsonResult IssueItem(string issuedTo, int quantity, string itemName)
{
MethodStatus status = new MethodStatus();
Configuration config = new Configuration();
int QuantityUpdate = quantity;
SqlConnection conn = new SqlConnection(config.ConnectionString());
try
{
conn.Open();
int inventoryId = -1;
int issuedToId = -1;
bool IssuedAlreadyExists = false;
string query = "SELECT * FROM Inventory"; // get all items
using (SqlCommand command = new SqlCommand(query, conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string ItemName = reader.GetString(reader.GetOrdinal("ItemName"));
int ItemID = (int)reader.GetInt64(reader.GetOrdinal("InventoryID"));
if (ItemName == itemName)
{
inventoryId = ItemID; // get item id for issued to
}
}
}
}
query = "SELECT * FROM IssuedTo"; // get all issuedtolocations
using (SqlCommand command = new SqlCommand(query, conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string description = reader.GetString(reader.GetOrdinal("IssuedToDescription"));
int locationId = (int)reader.GetInt64(reader.GetOrdinal("IssuedToID"));
if (description == issuedTo)
{
issuedToId = locationId; // get location id for issued to
}
}
}
}
query = "SELECT * FROM Issued"; // check if row already exists that matches itemID & issuedToID
using (SqlCommand command = new SqlCommand(query, conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int issuedId = (int)reader.GetInt64(reader.GetOrdinal("IssuedToID"));
int itemID = (int)reader.GetInt64(reader.GetOrdinal("InventoryID"));
if (itemID == inventoryId && issuedId == issuedToId)
{
IssuedAlreadyExists = true; // record with this item/location already exists
}
}
}
}
if (!IssuedAlreadyExists) // location/item combination do not exist in the DB, so add a new row with them
{
string insertString = "INSERT INTO Issued (IssuedQuantity,IssuedDate,InventoryID,IssuedToID) VALUES (@val1, @val2, @val3, @val4)";
using (SqlCommand comm = new SqlCommand(insertString, conn))
{
comm.CommandText = insertString;
comm.Parameters.AddWithValue("@val1", quantity);
comm.Parameters.AddWithValue("@val2", DateTime.Now.ToShortDateString());
comm.Parameters.AddWithValue("@val3", inventoryId);
comm.Parameters.AddWithValue("@val4", issuedToId);
comm.ExecuteNonQuery();
}
}
else // find current quantity, add new issued quantity to it, update table accordingly
{
int oldQuantity = -1;
int newQuantity = -1;
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Issued WHERE InventoryID =" + inventoryId + "AND IssuedToID =" + issuedToId, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int ItemID = (int)reader.GetInt64(reader.GetOrdinal("InventoryID"));
int locationId = (int)reader.GetInt64(reader.GetOrdinal("IssuedToID"));
if (ItemID == inventoryId && locationId == issuedToId)
{
oldQuantity = (int)reader.GetInt32(reader.GetOrdinal("IssuedQuantity"));
newQuantity = oldQuantity + quantity;
}
}
}
}
using (SqlCommand cmd = new SqlCommand("UPDATE Issued SET IssuedQuantity=@quantity" + " WHERE InventoryID=@ItemId AND IssuedToID=@LocationID", conn))
{
cmd.Parameters.AddWithValue("@quantity", newQuantity);
cmd.Parameters.AddWithValue("@ItemId", inventoryId);
cmd.Parameters.AddWithValue("@LocationID", issuedToId);
int rows = cmd.ExecuteNonQuery();
}
}
int originalQuantity = -1;
int updatedQuantity = -1;
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Inventory WHERE InventoryID =" + inventoryId, conn)) // Get Old quantity and calculate new quantity
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int ItemID = (int)reader.GetInt64(reader.GetOrdinal("InventoryID"));
if (ItemID == inventoryId)
{
originalQuantity = (int)reader.GetInt32(reader.GetOrdinal("InventoryQuantity"));
updatedQuantity = originalQuantity - quantity;
}
}
}
}
using (SqlCommand cmd = new SqlCommand("UPDATE Inventory SET InventoryQuantity=@quantity" + " WHERE InventoryID=@ItemId", conn))
{
cmd.Parameters.AddWithValue("@ItemId", inventoryId);
cmd.Parameters.AddWithValue("@quantity", updatedQuantity);
int rows = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.Message = ex.Message;
}
conn.Close();
return(Json(status, JsonRequestBehavior.DenyGet));
}
public JsonResult DeissueItem(string itemName, int oldIssuedQuantity, string locationName)
{
MethodStatus status = new MethodStatus();
// increment inventory table quantity by quantity of item to deissue
Configuration config = new Configuration();
SqlConnection conn = new SqlConnection(config.ConnectionString());
int ItemQuantity = 0;
int oldQuantity = 0;
int itemId = -1;
int issuedToID = -1;
//decrement issued table quantity by numberToReturn
conn.Open();
//get old quantity
try
{
string query = "SELECT * FROM Inventory WHERE ItemName=[" + itemName + "]"; // Get All Locations
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "SELECT * FROM Inventory WHERE ItemName=@itemName";
command.Parameters.AddWithValue("@itemName", itemName);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
oldQuantity = reader.GetInt32(reader.GetOrdinal("InventoryQuantity"));
itemId = (int)reader.GetInt64(reader.GetOrdinal("InventoryID"));
}
}
}
}
catch (Exception ex)
{
status.success = false;
status.Message = "FAIL";
return(Json(status, JsonRequestBehavior.DenyGet));
}
ItemQuantity = oldQuantity + oldIssuedQuantity;
//set inventory table quantity to new quantity
try
{
string query = "UPDATE Inventory SET InventoryQuantity=" + ItemQuantity + " WHERE ItemName=" + itemName;
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "UPDATE Inventory SET InventoryQuantity = @quantity WHERE ItemName = @name";
command.Parameters.AddWithValue("@quantity", ItemQuantity);
command.Parameters.AddWithValue("@name", itemName);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.InnerException.ToString();
}
// remove row from issued table
try
{
int IssuedToID = 0;
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT IssuedToID FROM IssuedTo WHERE IssuedToDescription = @description";
cmd.Parameters.AddWithValue("@description", locationName);
string str = Convert.ToString(cmd.ExecuteScalar());
IssuedToID = Int32.Parse(str);
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "DELETE FROM Issued WHERE IssuedToId = @location AND InventoryID=@item";
cmd.Parameters.AddWithValue("@location", IssuedToID);
cmd.Parameters.AddWithValue("@item", itemId);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.success = false;
status.Message = "failed to remove row from issued table.";
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public JsonResult UsedItem(string itemName, int numberToReturn, int oldIssuedQuantity, string location)
{
MethodStatus status = new MethodStatus();
Configuration config = new Configuration();
SqlConnection conn = new SqlConnection(config.ConnectionString());
int ItemQuantity = oldIssuedQuantity - numberToReturn;
int oldQuantity = 0;
int itemId = -1;
int issuedToID = -1;
//decrement issued table quantity by numberToReturn
ItemQuantity = oldIssuedQuantity - numberToReturn;
conn.Open();
//get old quantity
try
{
string query = "SELECT * FROM Inventory WHERE ItemName=[" + itemName + "]"; // Get All Locations
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "SELECT * FROM Inventory WHERE ItemName=@itemName";
command.Parameters.AddWithValue("@itemName", itemName);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
oldQuantity = reader.GetInt32(reader.GetOrdinal("InventoryQuantity"));
itemId = (int)reader.GetInt64(reader.GetOrdinal("InventoryID"));
}
}
}
}
catch (Exception ex)
{
status.success = false;
status.Message = "FAIL";
return(Json(status, JsonRequestBehavior.DenyGet));
}
try // get IssuedToID
{
string query = "SELECT IssuedToID FROM IssuedTo WHERE IssuedToDescription=" + location;
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "SELECT IssuedToID FROM IssuedTo WHERE IssuedToDescription=@location";
command.Parameters.AddWithValue("@location", location);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
issuedToID = (int)reader.GetInt64(reader.GetOrdinal("IssuedToID"));
}
}
}
}
catch (Exception ex)
{
status.success = false;
status.Message = "FAIL";
return(Json(status, JsonRequestBehavior.DenyGet));
}
try // set new inventory quantity
{
string query = "UPDATE Issued SET IssuedQuantity=" + ItemQuantity + " WHERE InventoryID=" + itemId + " AND IssuedToID=" + issuedToID;
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "UPDATE Issued SET IssuedQuantity = @quantity WHERE InventoryID = @item AND IssuedToID= @location";
command.Parameters.AddWithValue("@quantity", ItemQuantity);
command.Parameters.AddWithValue("@item", itemId);
command.Parameters.AddWithValue("@location", issuedToID);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.success = false;
status.Message = "failed yo";
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public JsonResult UpdateIssuedQuantity(string itemName, int oldIssuedQuantity, int numberTaken, string location)
{
MethodStatus status = new MethodStatus();
Configuration config = new Configuration();
SqlConnection conn = new SqlConnection(config.ConnectionString());
int ItemQuantity = 0;
int oldQuantity = 0;
int itemId = -1;
int issuedToID = -1;
//get old quantity
try
{
conn.Open();
string query = "SELECT * FROM Inventory WHERE ItemName=[" + itemName + "]"; // Get All Locations
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "SELECT * FROM Inventory WHERE ItemName=@itemName";
command.Parameters.AddWithValue("@itemName", itemName);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
oldQuantity = reader.GetInt32(reader.GetOrdinal("InventoryQuantity"));
itemId = (int)reader.GetInt64(reader.GetOrdinal("InventoryID"));
}
}
}
}
catch (Exception ex)
{
status.success = false;
status.Message = "FAIL";
return(Json(status, JsonRequestBehavior.DenyGet));
}
ItemQuantity = oldQuantity - numberTaken; // check if update will take inventory under 0
if (ItemQuantity < 0)
{
status.success = false;
status.Message = "Cannot take more than currently in inventory";
return(Json(status, JsonRequestBehavior.DenyGet));
}
//set inventory table quantity to new quantity
try
{
string query = "UPDATE Inventory SET InventoryQuantity=" + ItemQuantity + " WHERE ItemName=" + itemName;
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "UPDATE Inventory SET InventoryQuantity = @quantity WHERE ItemName = @name";
command.Parameters.AddWithValue("@quantity", ItemQuantity);
command.Parameters.AddWithValue("@name", itemName);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.InnerException.ToString();
}
ItemQuantity = oldIssuedQuantity + numberTaken;
//increment issued table quantity by numberTaken
try // get IssuedToID
{
string query = "SELECT IssuedToID FROM IssuedTo WHERE IssuedToDescription=" + location;
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "SELECT IssuedToID FROM IssuedTo WHERE IssuedToDescription=@location";
command.Parameters.AddWithValue("@location", location);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
issuedToID = (int)reader.GetInt64(reader.GetOrdinal("IssuedToID"));
}
}
}
}
catch (Exception ex)
{
status.success = false;
status.Message = "FAIL";
return(Json(status, JsonRequestBehavior.DenyGet));
}
try // set new inventory quantity
{
string query = "UPDATE Issued SET IssuedQuantity=" + ItemQuantity + " WHERE InventoryID=" + itemId + " AND IssuedToID=" + issuedToID;
using (SqlCommand command = new SqlCommand(query, conn))
{
command.CommandText = "UPDATE Issued SET IssuedQuantity = @quantity WHERE InventoryID = @item AND IssuedToID= @location";
command.Parameters.AddWithValue("@quantity", ItemQuantity);
command.Parameters.AddWithValue("@item", itemId);
command.Parameters.AddWithValue("@location", issuedToID);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.InnerException.ToString();
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
public JsonResult AddNewItem(string ItemName, int ItemQuantity, int ItemReorder, string ItemDescription, string ItemLocation, string ItemType, string GunSerial = "")
{
MethodStatus status = new MethodStatus();
int location = -1;
List <Location> Locations = new List <Location>();
Configuration config = new Configuration();
SqlConnection conn1 = new SqlConnection(config.ConnectionString());
try
{
conn1.Open();
string query1 = "SELECT InventoryLocationID FROM InventoryLocation WHERE InventoryLocationDescription=@location";
using (SqlCommand command = new SqlCommand(query1, conn1))
{
command.CommandText = "SELECT InventoryLocationID FROM InventoryLocation WHERE InventoryLocationDescription=@location";
command.Parameters.AddWithValue("@location", ItemLocation);
string str = Convert.ToString(command.ExecuteScalar());
location = Int32.Parse(str);
Locations.Add(new Location(ItemLocation, location));
}
}
catch (Exception ex)
{
status.success = false;
status.Message = ex.Message;
}
foreach (var LOCATION in Locations)
{
if (LOCATION.LocationName == ItemLocation)
{
location = LOCATION.LocationID;
}
}
List <InventoryItem> InventoryItems = new List <InventoryItem>();
SqlConnection conn = new SqlConnection(config.ConnectionString());
try
{
conn.Open();
if (ItemType == "Guns ")
{
string sql = "INSERT INTO Inventory(IsOnKSUInventory,ItemName,InventoryDescription,InventoryQuantity,InventoryReorderQuantity,InventoryTypeID,InventoryLocationID,GunSerial) VALUES(@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@param1", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@param2", SqlDbType.VarChar, 50).Value = ItemName;
cmd.Parameters.Add("@param3", SqlDbType.VarChar, 50).Value = ItemDescription;
cmd.Parameters.Add("@param4", SqlDbType.Int).Value = ItemQuantity;
cmd.Parameters.Add("@param5", SqlDbType.Int).Value = ItemReorder;
cmd.Parameters.Add("@param6", SqlDbType.Int).Value = 3;
cmd.Parameters.Add("@param7", SqlDbType.Int).Value = location;
cmd.Parameters.Add("@param8", SqlDbType.VarChar, 50).Value = GunSerial;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
else
{
string sql = "INSERT INTO Inventory(IsOnKSUInventory,ItemName,InventoryDescription,InventoryQuantity,InventoryReorderQuantity,InventoryTypeID,InventoryLocationID) VALUES(@param1,@param2,@param3,@param4,@param5,@param6,@param7)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@param1", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@param2", SqlDbType.VarChar, 50).Value = ItemName;
cmd.Parameters.Add("@param3", SqlDbType.VarChar, 50).Value = ItemDescription;
cmd.Parameters.Add("@param4", SqlDbType.Int).Value = ItemQuantity;
cmd.Parameters.Add("@param5", SqlDbType.Int).Value = ItemReorder;
cmd.Parameters.Add("@param6", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@param7", SqlDbType.Int).Value = location;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status.success = false;
//status.Message = ex.InnerException.ToString();
}
return(Json(status, JsonRequestBehavior.DenyGet));
}
/// <summary>
/// updates a new method log, in an append only manner
/// </summary>
/// <param name="token"></param>
/// <param name="methodName"></param>
/// <param name="status"></param>
/// <param name="timeTaken"></param>
/// <param name="error"></param>
public void UpdateMethodLog(string token, string methodName, MethodStatus status, long?timeTaken = null, string error = null)
{
Task.Factory.StartNew(() => FbHelper.UpdateMethodLog(token, methodName, status, timeTaken, error)).ConfigureAwait(false);
}
public new MethodStatus <Theme> Create(Theme theme)
{
var status = new MethodStatus <Theme>();
try
{
var themeOptionManagerList = new List <ESM_THEME_OPTION>();
var companyId = FrontEndSession.Instance.CompanyId.GetValueOrDefault(0);
var siteId = FrontEndSession.Instance.SiteId.GetValueOrDefault(0);
var sessionThemeId = 1;
var companyThemeId = 1;
var esmTheme = new ESM_THEME
{
GROUP_NAME = "Created Themes",
IS_DEFAULT = false,
IS_SYSTEM = false,
DATE_CREATED = DateTime.Now,
COMPANY_ID = 1,
SITE_ID = 1,
THEME_STRUCTURE_ID = theme.ThemeStructureId,
HREF = theme.Href,
NAME = theme.Name,
STYLESHEET = theme.StyleSheet,
PRIMARY_COLOR = theme.PrimaryColor,
SECONDARY_COLOR = theme.SecondaryColor,
TERTIARY_COLOR = theme.TertiaryColor
};
foreach (var themeStyle in theme.ThemeStyles)
{
var esmThemeStyle = new ESM_THEME_STYLE
{
THEME_ID = esmTheme.THEME_ID,
THEME_GROUP_ID = themeStyle.ThemeGroupId,
DISPLAY_NAME = themeStyle.DisplayName,
NAME = themeStyle.Name,
SELECTOR = themeStyle.Selector,
ROLE = themeStyle.Role,
IS_PRIMARY = themeStyle.IsPrimary,
IS_SECONDARY = themeStyle.IsSecondary,
IS_TERTIARY = themeStyle.IsTertiary
};
if (themeStyle.DisplayName.Equals("Body Background Image") && !themeStyle.Value.Equals("none"))
{
//var backgroundImageValue = themeStyle.Value.Replace("url(", string.Empty).Replace(")", string.Empty);
var backgroundImageUrl = "";
//var backgroundImageUrl = GetBackgroundImageUrl(backgroundImageValue);
esmThemeStyle.VALUE = string.IsNullOrEmpty(backgroundImageUrl)
? "none"
: $"url({backgroundImageUrl})";
}
else
{
esmThemeStyle.VALUE = themeStyle.Value;
}
if (themeStyle.ThemeOptions == null)
{
continue;
}
foreach (var themeOption in themeStyle.ThemeOptions)
{
themeOptionManagerList.Add(new ESM_THEME_OPTION
{
THEME_STYLE_ID = esmThemeStyle.THEME_STYLE_ID,
VALUE = themeOption.Value,
TEXT = themeOption.Text,
IS_SELECTED = themeOption.IsSelected
});
}
}
theme.IsDeletable = companyThemeId != theme.Id && sessionThemeId != theme.Id && !theme.IsSystem;
status.Data = theme;
}
catch (Exception err)
{
status.ErrorType = ErrorType.Unhandled;
status.IsError = true;
status.MessageSeverity = MessageSeverity.Error;
}
return(status);
}
public new MethodStatus <Theme> Update(Theme theme)
{
var status = new MethodStatus <Theme>();
try
{
//var themeManager = new ArgosyManager<ESM_THEME>();
//var themeOptionManager = new ArgosyManager<ESM_THEME_OPTION>();
//var themeStyleManager = new ArgosyManager<ESM_THEME_STYLE>();
var companyId = FrontEndSession.Instance.CompanyId.GetValueOrDefault(0);
var sessionThemeId = 1;
var companyThemeId = 1;
//themeManager.Update(t =>
//{
// t.HREF = theme.Href;
// t.NAME = theme.Name;
// t.PRIMARY_COLOR = theme.PrimaryColor;
// t.SECONDARY_COLOR = theme.SecondaryColor;
// t.TERTIARY_COLOR = theme.TertiaryColor;
// t.STYLESHEET = theme.StyleSheet;
//}, t => t.THEME_ID == theme.Id);
//themeManager.SaveChanges();
foreach (var themeStyle in theme.ThemeStyles)
{
//themeStyleManager.Update(ts =>
//{
// ts.DISPLAY_NAME = themeStyle.DisplayName;
// ts.NAME = themeStyle.Name;
// ts.VALUE = themeStyle.Value;
// ts.SELECTOR = themeStyle.Selector;
// ts.ROLE = themeStyle.Role;
// ts.IS_PRIMARY = themeStyle.IsPrimary;
// ts.IS_SECONDARY = themeStyle.IsSecondary;
// ts.IS_TERTIARY = themeStyle.IsTertiary;
//}, ts => ts.THEME_STYLE_ID == themeStyle.ThemeStyleId);
//themeStyleManager.SaveChanges();
if (themeStyle.ThemeOptions == null)
{
continue;
}
foreach (var themeOption in themeStyle.ThemeOptions)
{
//themeOptionManager.Update(to =>
//{
// to.VALUE = themeOption.Value;
// to.TEXT = themeOption.Text;
// to.IS_SELECTED = themeOption.IsSelected;
//}, to => to.THEME_OPTION_ID == themeOption.ThemeOptionId);
//themeOptionManager.SaveChanges();
}
}
theme.IsDeletable = companyThemeId != theme.Id && sessionThemeId != theme.Id && !theme.IsSystem;
status.Data = theme;
}
catch (Exception err)
{
//status.ErrorCode = Cerqa.Common.V5.ErrorHandling.ErrorHandlingRepositoryFactory.Instance.HandleError(err, "Update " + TypeName + " Exception", theme);
status.ErrorType = ErrorType.Unhandled;
status.IsError = true;
status.MessageSeverity = MessageSeverity.Error;
status.Message = "There was an error updating your " + "nesto" + ". Please contact support.";
}
return(status);
}
public new MethodStatus <Theme> Delete(Theme theme)
{
var status = new MethodStatus <Theme>();
var company = new ESM_COMPANY()
{
COMPANY_ID = 1, THEME_ID = 1
};
if (!theme.IsDeletable)
{
if (company != null && company.THEME_ID == theme.Id)
{
status.Message = "~{msgStandardThemesCanNotBeDeleted}~";
status.IsError = true;
return(status);
}
if (theme.IsSystem)
{
status.Message = "~{msgStandardThemesCanNotBeDeleted}~";
status.IsError = true;
return(status);
}
if (int.Parse(FrontEndSession.Instance.ThemeId) == theme.Id)
{
status.Message = "~{msgSessionThemeCanNotBeDeleted}~";
status.IsError = true;
return(status);
}
}
try
{
//var themeManager = new ArgosyManager<ESM_THEME>();
//var themeStyleManager = new ArgosyManager<ESM_THEME_STYLE>();
//var themeOptionManager = new ArgosyManager<ESM_THEME_OPTION>();
foreach (var themeStyle in theme.ThemeStyles)
{
if (themeStyle.ThemeOptions != null)
{
foreach (var themeOption in themeStyle.ThemeOptions)
{
//var esmThemeOption = themeOptionManager.FirstOrDefault(to => to.THEME_OPTION_ID == themeOption.ThemeOptionId);
//themeOptionManager.Delete(esmThemeOption);
//themeOptionManager.SaveChanges();
}
}
//var esmThemeStyle = themeStyleManager.FirstOrDefault(ts => ts.THEME_STYLE_ID == themeStyle.ThemeStyleId);
//themeStyleManager.Delete(esmThemeStyle);
//themeStyleManager.SaveChanges();
}
var esmTheme = 1;
//themeManager.Delete(esmTheme);
//themeManager.SaveChanges();
status.Data = theme;
status.IsError = false;
status.Message = "~{msgThemeDeleted}~";
}
catch (Exception err)
{
//status.ErrorCode = Cerqa.Common.V5.ErrorHandling.ErrorHandlingRepositoryFactory.Instance.HandleError(err, "Delete " + TypeName + " Exception", theme);
status.ErrorType = ErrorType.Unhandled;
status.IsError = true;
status.MessageSeverity = MessageSeverity.Error;
status.Message = "There was an error deleting your " + "Nesto" + ". Please contact support.";
}
return(status);
}
/// <summary>
/// Returns the correct view based upon the status.
/// </summary>
protected ActionResult View(MethodStatus status, object model)
{
if (status == MethodStatus.NotAuthorized)
return new ControllerResultCode(HttpResultCodeOption.NotAuthorized403);
else if (status != MethodStatus.Pass)
return new ControllerResultCode(HttpResultCodeOption.NotFound404);
return View(model);
}
/// <summary>
/// Redirects to an action.
/// </summary>
protected ActionResult RedirectToAction(MethodStatus status, string actionName, string controllerName)
{
if (status == MethodStatus.NotAuthorized)
return new ControllerResultCode(HttpResultCodeOption.NotAuthorized403);
else if (status != MethodStatus.Pass)
return new ControllerResultCode(HttpResultCodeOption.NotFound404);
return RedirectToAction(actionName, controllerName);
}
/// <summary>
/// updates a new method log, in an append only manner
/// </summary>
/// <param name="token"></param>
/// <param name="methodName"></param>
/// <param name="status"></param>
/// <param name="timeTaken"></param>
/// <param name="error"></param>
public void UpdateMethodLog(string token, string methodName, MethodStatus status, long? timeTaken = null, string error = null)
{
Task.Factory.StartNew(() => FbHelper.UpdateMethodLog(token, methodName, status, timeTaken, error)).ConfigureAwait(false);
}
/// <summary>
/// Update method status in local DB via FbService
/// </summary>
/// <param name="token">unique token for method call</param>
/// <param name="methodName">Method Name</param>
/// <param name="status">Status</param>
/// <param name="timeTaken">Time taken</param>
/// <param name="error">Error if any</param>
private static void UpdateMethodLog(string token, string methodName, MethodStatus status, long? timeTaken = null, string error = null)
{
if (EventCount > 100 || EventCount == -1)
{
EventCount = 0;
if (RunResultUpdated != null)
{
RunResultUpdated(null, null);
}
}
EventCount++;
ServiceClient.UpdateMethodLog(token, methodName, status, timeTaken, error);
}
public static void UpdateMethodLog(string token, string methodName, MethodStatus status, long? timeTaken = null, string error = null)
{
try
{
if (IsServer)
{
FbConnection connection = GetConnection();
Open();
using (FbCommand insertData = connection.CreateCommand())
{
// ReSharper disable once ConvertIfStatementToConditionalTernaryExpression
if (timeTaken != null)
insertData.CommandText = "insert into MethodLogs values (@token, @methodName, @status, @time, @dt, @edt";
else
insertData.CommandText = "insert into MethodLogs values (@token, @methodName, @status, NULL, @dt, @edt";
if (!string.IsNullOrEmpty(error))
insertData.CommandText += ", @err)";
else
insertData.CommandText += ", NULL)";
insertData.Parameters.Clear();
insertData.Parameters.Add("@token", FbDbType.VarChar, 50).Value = token;
insertData.Parameters.Add("@methodName", FbDbType.VarChar, 200).Value = methodName;
insertData.Parameters.Add("@status", FbDbType.Integer).Value = status;
if (timeTaken != null)
{
insertData.Parameters.Add("@time", FbDbType.BigInt).Value = timeTaken.Value;
}
insertData.Parameters.Add("@dt", FbDbType.TimeStamp).Value = DateTime.Now;
insertData.Parameters.Add("@edt", FbDbType.TimeStamp).Value = DateTime.Now;
if (!string.IsNullOrEmpty(error))
{
insertData.Parameters.Add("@err", FbDbType.Text).Value = error;
}
insertData.ExecuteNonQuery();
}
}
}
catch (Exception)
{
//eat exceptions of firebird. TODO
}
}
