private void import_Click(object sender, EventArgs e)
{
String tableName = databaseTables.SelectedItem.ToString(), entity = entityName.Text; // Fixes #42 : do not pass entityName.Text to the constructor
if (columnsList.Items.Count > 0)
{
// Entity name check #33
if (!XmlUtils.validInput(entityName.Text))
{
var skipEntityName = MessageBox.Show(string.Format(Resources.XML_NewTable_invalidImportEntityName_msg, entityName.Text, tableName), Resources.XMLGUI__warning, MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
switch (skipEntityName)
{
case DialogResult.Cancel:
return;
case DialogResult.OK:
entity = tableName;
break;
}
}
// Open a new SimpleXml Form as a new Thread
var newDocument = new Thread(() => Application.Run(new SimpleXml(ODBConnection.ImportTable(databaseTables.SelectedItem.ToString()), entity)));
newDocument.SetApartmentState(ApartmentState.STA); // Fixes Threads issue #21
newDocument.IsBackground = false;
newDocument.Start();
Dispose();
Close();
}
else
{
MessageBox.Show(Resources.XML_ImportTable_noColumns_msg, Resources.XMLGUI__warning, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public ActionResult AddCafeMenue(CafeMenueInsertModel cafeMenuInsertModel)
{
var jsonResult = new JsonResult();
var loginSuccessful = false;
if (Session["loggedIn"] != null || m_helper.Login(cafeMenuInsertModel.LoginDetails, Constants.ODBCString))
{
loginSuccessful = true;
var connectionString = Constants.ODBCString;
using (var dbConnection = new ODBConnection(connectionString))
using (var persister = new ODBCPersister(dbConnection))
{
var count = int.Parse(persister.ExecuteScalar(string.Format(m_helper.GetQueryValue("checkCafeteriaManager"), cafeMenuInsertModel.LoginDetails.UserName)).ToString());
if (count > 0)
{
persister.ExecuteNonQueryCmd("Cafe", string.Format(m_helper.GetQueryValue("insertCafeMenue"),
cafeMenuInsertModel.CafeMenue.CafeID,
cafeMenuInsertModel.CafeMenue.CafeMenueName));
}
else
{
var msg = "You are not previliged to complete this action";
jsonResult.Data = new { msg };
}
}
}
else
{
jsonResult.Data = new { loginSuccessful };
}
return(jsonResult);
}
private void fromDatabaseToolStripMenuItem_Click(object sender, EventArgs e)
{
// Check whether ODBConnection._Connection is open and usable
if (ODBConnection.valid)
{
try
{
new IEDatabase(ODBConnection.GetTableNames()).ShowDialog();
}
catch (SqlException)
{
MessageBox.Show(Resources.IEDatabase_connectionFail_msg, Resources.XMLGUI__fail, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
var result = new DatabaseConnection().ShowDialog();
if (result == DialogResult.OK && ODBConnection.valid)
{
fromDatabaseToolStripMenuItem_Click(sender, e);
}
else if (result == DialogResult.Cancel)
{
return;
}
}
}
private void exportToDatabase()
{
var match = true;
foreach (var column in exportColumnsList.Items) // Compare the 2 listBoxes
{
if (!importColumnsList.Items.Contains(column))
{
match = false;
}
}
if (match)
{
exportTable.TableName = databaseTables.SelectedItem.ToString();
try{
ODBConnection.ExportTable(exportTable);
MessageBox.Show(string.Format(Resources.IEDatabase_successExport_msg, exportTable.TableName), Resources.success, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}catch (Exception e) {
if (MessageBox.Show(e.Message, Resources.XMLGUI__fail, MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) == DialogResult.Retry)
{
exportToDatabase();
}
else
{
return;
}
}
Dispose();
Close();
}
else
{
MessageBox.Show(Resources.IEDatabase_columns_mismatch_msg, Resources.IEDatabase_columns_mismatch, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void databaseTables_SelectedIndexChanged(object sender, EventArgs e)
{
var tableName = databaseTables.SelectedItem.ToString();
var columnNames = ODBConnection.GetTableColumns(tableName);
entityName.Text = tableName;
columnsList.Items.Clear();
foreach (var column in columnNames)
{
columnsList.Items.Add(column);
}
}
public ActionResult AddIngredients(InventoryUpdateModel inventoryUpdateModel)
{
var jsonResult = new JsonResult();
if (Session["loggedIn"] != null || m_helper.Login(inventoryUpdateModel.LoginDetails, Constants.ODBCString))
{
var connectionString = Constants.ODBCString;
using (var dbConnection = new ODBConnection(connectionString))
using (var persister = new ODBCPersister(dbConnection))
{
var count = int.Parse(persister.ExecuteScalar(string.Format(m_helper.GetQueryValue("checkCafeteriaManagerVendorID"), inventoryUpdateModel.InventoryModel.CafeteriaVendorID)).ToString());
if (count > 0)
{
// if it exists
var itemExists = int.Parse(persister.ExecuteScalar(string.Format(m_helper.GetQueryValue("checkStoreHouseInventoryItem"), inventoryUpdateModel.InventoryModel.IngredientID)).ToString()) > 0;
if (itemExists)
{
//update updateStoreHouseInventory
persister.ExecuteNonQueryCmd("StoreHouseInventory", string.Format(m_helper.GetQueryValue("updateStoreHouseInventory"),
inventoryUpdateModel.InventoryModel.IngredientID,
inventoryUpdateModel.InventoryModel.IngredientName,
inventoryUpdateModel.InventoryModel.IngredientQuantity,
inventoryUpdateModel.InventoryModel.IngredientQuantityUnit,
"instock",
inventoryUpdateModel.InventoryModel.IngredientID));
}
else
{
persister.ExecuteNonQueryCmd("StoreHouseInventory", string.Format(m_helper.GetQueryValue("insertStoreHouseInventory"),
inventoryUpdateModel.InventoryModel.IngredientID,
inventoryUpdateModel.InventoryModel.IngredientName,
inventoryUpdateModel.InventoryModel.IngredientQuantity,
inventoryUpdateModel.InventoryModel.IngredientQuantityUnit,
"instock",
inventoryUpdateModel.InventoryModel.CafeteriaVendorID));
}
}
else
{
var msg = "You are not previliged to complete this action";
jsonResult.Data = new { msg };
}
}
}
else
{
var msg = "Unable to Login";
jsonResult.Data = new { msg };
}
return(jsonResult);
}
public ActionResult AddOrDeleteCafe(CafeUpdateModel cafeupdateModel)
{
var jsonResult = new JsonResult();
var loginSuccessful = false;
if (Session["loggedIn"] != null || m_helper.Login(cafeupdateModel.LoginDetails, Constants.ODBCString))
{
loginSuccessful = true;
var connectionString = Constants.ODBCString;
using (var dbConnection = new ODBConnection(connectionString))
using (var persister = new ODBCPersister(dbConnection))
{
var count = int.Parse(persister.ExecuteScalar(string.Format(m_helper.GetQueryValue("checkCafeteriaManager"), cafeupdateModel.CafeteriaManagerID)).ToString());
if (count > 0)
{
// if it exists
var itemExists = int.Parse(persister.ExecuteScalar(string.Format(m_helper.GetQueryValue("checkCafe"), cafeupdateModel.Cafe.CafeName)).ToString()) > 0;
if (itemExists)
{
//update updateStoreHouseInventory
persister.ExecuteNonQueryCmd("Cafe", string.Format(m_helper.GetQueryValue("updateCafe"),
cafeupdateModel.Cafe.CafeName,
cafeupdateModel.Cafe.CafeName,
cafeupdateModel.Delete ? "inactive" : "active",
cafeupdateModel.Cafe.CafeteriaVendorID,
cafeupdateModel.Delete ? cafeupdateModel.Cafe.CafeName : cafeupdateModel.NewName));
}
else
{
persister.ExecuteNonQueryCmd("Cafe", string.Format(m_helper.GetQueryValue("insertCafe"),
cafeupdateModel.Cafe.CafeName,
cafeupdateModel.Cafe.CafeName,
"active",
cafeupdateModel.Cafe.CafeteriaVendorID));
}
}
else
{
var msg = "You are not previliged to complete this action";
jsonResult.Data = new { msg };
}
}
}
else
{
jsonResult.Data = new { loginSuccessful };
}
return(jsonResult);
}
public bool Login(LoginModel loginModel, string connectionString)
{
using (var dbConnection = new ODBConnection(connectionString))
using (var persister = new ODBCPersister(dbConnection))
{
var count = int.Parse(persister.ExecuteScalar(string.Format(GetQueryValue("checkCustomerPassword"), loginModel.UserName, loginModel.Password, "active")).ToString()) +
int.Parse(persister.ExecuteScalar(string.Format(GetQueryValue("checkCafeteriaManagerPassword"), loginModel.UserName, loginModel.Password, "active")).ToString()) +
int.Parse(persister.ExecuteScalar(string.Format(GetQueryValue("checkCafeUserPassword"), loginModel.UserName, loginModel.Password, "active")).ToString());
if (count > 0)
{
return(true);
}
}
return(false);
}
public List <Dictionary <string, string> > GetODBCData(string connectionString, string queryString)
{
var data = new List <Dictionary <string, string> >();
using (var dbConnection = new ODBConnection(connectionString))
using (var dataSet = new DBDataSet(dbConnection, queryString))
{
var cols = dataSet.ColumnNames;
while (dataSet.MoveNext())
{
var dic = new Dictionary <string, string>();
foreach (var col in cols)
{
dic[col] = dataSet.GetValue(col);
}
data.Add(dic);
}
}
return(data);
}
private void toDatabaseToolStripMenuItem_Click(object sender, EventArgs e)
{
if (dataGrid.ColumnCount > 0) // Fixes #56
{
// Check whether ODBConnection._Connection is open and usable
if (ODBConnection.valid)
{
try
{
var dbTables = ODBConnection.GetTableNames();
var exportTable = dataGrid.DataSource as DataTable;
new IEDatabase(dbTables, exportTable).ShowDialog();
}
catch (SqlException)
{
MessageBox.Show(Resources.IEDatabase_connectionFail_msg, Resources.XMLGUI__fail, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
var result = new DatabaseConnection().ShowDialog();
if (result == DialogResult.OK && ODBConnection.valid)
{
toDatabaseToolStripMenuItem_Click(sender, e);
}
else if (result == DialogResult.Cancel)
{
return;
}
}
}
else
{
MessageBox.Show(Resources.XMLGUI_saveEmptyTable_fail_msg, Resources.XMLGUI__fail, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public ActionResult SignUp(SignUpModel signUpModel)
{
var connectionString = Constants.ODBCString;
var jsonResult = new JsonResult();
var signUpSuccessfull = true;
List <string> validationLogs = new List <string>();
using (var dbConnection = new ODBConnection(connectionString))
using (var persister = new ODBCPersister(dbConnection))
{
if (CheckSignUpType(signUpModel, "checkPersonID", persister).Count > 0)
{
signUpSuccessfull = false;
jsonResult.Data = new { signUpSuccessfull, validationLogs };
}
else
{
var updatePersonCommand = String.Format(m_helper.GetQueryValue("insertPersonShort"), signUpModel.UserID, signUpModel.UserID);
switch (signUpModel.Type)
{
case UserType.CAFE_USER:
validationLogs = CheckSignUpType(signUpModel, "checkCafeUser", persister);
if (validationLogs.Count == 0)
{
var updatetabelCommand = String.Format(m_helper.GetQueryValue("insertCafeUser"), signUpModel.UserID, signUpModel.NewPassword, "active", signUpModel.CafeID);
signUpSuccessfull = m_helper.SignUpStatusObject(persister, signUpModel, updatePersonCommand, "CafeUser", updatetabelCommand);
jsonResult.Data = new { signUpSuccessfull };
return(jsonResult);
}
else
{
signUpSuccessfull = false;
jsonResult.Data = new { signUpSuccessfull, validationLogs };
}
break;
case UserType.CUSTOMER:
validationLogs = CheckSignUpType(signUpModel, "checkCustomer", persister);
if (validationLogs.Count == 0)
{
var updatetabelCommand = String.Format(m_helper.GetQueryValue("insertCustomerShort"), signUpModel.UserID, signUpModel.NewPassword);
signUpSuccessfull = m_helper.SignUpStatusObject(persister, signUpModel, updatePersonCommand, "Customer", updatetabelCommand);
jsonResult.Data = new { signUpSuccessfull };
return(jsonResult);
}
else
{
signUpSuccessfull = false;
jsonResult.Data = new { signUpSuccessfull, validationLogs };
}
break;
case UserType.CafeteriaManager:
validationLogs = CheckSignUpType(signUpModel, "checkCafeteriaManager", persister);
if (validationLogs.Count == 0)
{
var updatetabelCommand = String.Format(m_helper.GetQueryValue("insertCafeteriaManager"), signUpModel.UserID, signUpModel.NewPassword, "active", signUpModel.VendorID);
signUpSuccessfull = m_helper.SignUpStatusObject(persister, signUpModel, "CafeUser", "CafeteriaManager", updatetabelCommand);
jsonResult.Data = new { signUpSuccessfull };
return(jsonResult);
}
else
{
signUpSuccessfull = false;
jsonResult.Data = new { signUpSuccessfull, validationLogs };
}
break;
}
}
}
return(jsonResult);
}
public ActionResult GetOrderDetails(LoginModel loginDetails, List <string> orderList, string cafeID, string cafeMenuID)
{
var jsonResult = new JsonResult();
var orderMap = orderList.ToDictionary(x => x.Split('-')[0], x => int.Parse(x.Split('-')[1]));
if (Session["loggedIn"] != null || m_helper.Login(loginDetails, Constants.ODBCString))
{
using (var dbConnection = new ODBConnection(Constants.ODBCString))
using (var persister = new ODBCPersister(dbConnection))
{
float sum = 0.0f;
foreach (var entry in orderMap)
{
var data = m_helper.GetODBCData(Constants.ODBCString, string.Format(m_helper.GetQueryValue("foodDetails"), entry.Key))[0];
sum += float.Parse(data["fooditemcost"]);
sum = sum * float.Parse(entry.Key + "");
}
var orderID = Guid.NewGuid();
var queryString = string.Format(m_helper.GetQueryValue("registerOrder"), orderID, "orderplaced", sum, cafeID, loginDetails.UserName);
persister.ExecuteNonQueryCmd("CustomerOrder", queryString);
foreach (var entry in orderMap)
{
queryString = string.Format(m_helper.GetQueryValue("addFoodToORder"), orderID, entry.Key, entry.Value, "orderplaced", loginDetails.UserName);
persister.ExecuteNonQueryCmd("CustomerOrder", queryString);
}
queryString = string.Format(m_helper.GetQueryValue("inventoryCheck"), orderID);
var Data = m_helper.GetODBCData(Constants.ODBCString, queryString);
foreach (var entry in Data)
{
if (float.Parse(entry["totalIngredientQuantityRequired"]) > float.Parse(entry["IngredientQuantity"]))
{
var msg = "rejected";
jsonResult.Data = msg;//updateOrderStatus
queryString = string.Format(m_helper.GetQueryValue("updateOrderStatus"), "rejected", orderID);
persister.ExecuteNonQueryCmd("CustomerOrder", queryString);
return(jsonResult);
}
}
foreach (var entry in Data)
{
var qty = float.Parse(entry["IngredientQuantity"]) - float.Parse(entry["totalIngredientQuantityRequired"]);
var status = qty > 0 ? "availabel" : "out of stock";
queryString = string.Format(m_helper.GetQueryValue("decrimentStore"), qty, entry["IngredientStatus"], entry["IngredientID"]);
persister.ExecuteNonQueryCmd("StoreHouseInventory", queryString);
}
persister.ExecuteNonQueryCmd("FoodItem", m_helper.GetQueryValue("marFoodItemNA"));
DateTime time = DateTime.Now;
int count = 60;
while (count > 0)
{
string status = "";
if ((DateTime.Now - time).Seconds < 5)
{
status = "orderplaced";
}
if ((DateTime.Now - time).Seconds < 12)
{
status = "processing";
}
if ((DateTime.Now - time).Seconds < 16)
{
status = "out for delivery ";
}
queryString = string.Format(m_helper.GetQueryValue("updateOrderStatus"), status, orderID);
persister.ExecuteNonQueryCmd("CustomerOrder", queryString);
Thread.Sleep(1000);
count--;
}
}
}
jsonResult.Data = "successful :)";
return(jsonResult);
}
public ActionResult InsertRecipe(RecipeUpdateModel recipeUpdate)
{
var jsonResult = new JsonResult();
var loginSuccessful = false;
if (Session["loggedIn"] != null || m_helper.Login(recipeUpdate.LoginDetails, Constants.ODBCString))
{
loginSuccessful = true;
var connectionString = Constants.ODBCString;
using (var dbConnection = new ODBConnection(connectionString))
using (var persister = new ODBCPersister(dbConnection))
{
var count = int.Parse(persister.ExecuteScalar(string.Format(m_helper.GetQueryValue("checkCafeUser"), recipeUpdate.LoginDetails.UserName)).ToString());
if (count > 0)
{
// if it exists
var itemExists = int.Parse(persister.ExecuteScalar(string.Format(m_helper.GetQueryValue("checkCafe"), recipeUpdate.CafeID, "active")).ToString()) > 0;
if (itemExists)
{
//update updateStoreHouseInventory
persister.ExecuteNonQueryCmd("FoodItem", string.Format(m_helper.GetQueryValue("updateFoodItem"),
recipeUpdate.Food.FoodItemID,
recipeUpdate.Food.FoodItemName,
recipeUpdate.Food.FoodItemType,
recipeUpdate.Food.FoodItemDescription,
recipeUpdate.Food.FoodItemCost,
recipeUpdate.Food.FoodItemStatus,
recipeUpdate.Food.CafeMenuID));
persister.ExecuteScalar(string.Format(m_helper.GetQueryValue("updateRecipe"), recipeUpdate.Recipe.FoodItemIngredientQuantity, recipeUpdate.Recipe.FoodItemID));
}
else
{
persister.ExecuteNonQueryCmd("FoodItem", string.Format(m_helper.GetQueryValue("insertFoodItem"),
recipeUpdate.Food.FoodItemID,
recipeUpdate.Food.FoodItemName,
recipeUpdate.Food.FoodItemType,
recipeUpdate.Food.FoodItemDescription,
recipeUpdate.Food.FoodItemCost,
recipeUpdate.Food.FoodItemStatus,
recipeUpdate.Food.CafeMenuID));
persister.ExecuteScalar(string.Format(m_helper.GetQueryValue("insertRecipe"),
recipeUpdate.Recipe.FoodItemID,
recipeUpdate.Recipe.FoodItemIngredientQuantity,
recipeUpdate.Recipe.FoodItemIngredientQuantityUnit,
recipeUpdate.Recipe.IngredientID));
}
}
else
{
var msg = "You are not previliged to complete this action";
jsonResult.Data = new { msg };
}
}
}
else
{
jsonResult.Data = new { loginSuccessful };
}
return(jsonResult);
}
private void connect_Click(object sender, EventArgs e)
{
var databaseName = String.Empty;
if (!advancedOptions)
{
ODBConnection.remote = false;
if (dbAuthType.SelectedItem.Equals("Windows Authentication"))
{
ODBConnection.connectionString = $"{dbName.Text}:{dbUser.Text}:{dbPass.Text}";
ODBConnection.winAuth = true;
}
else if (dbAuthType.SelectedItem.Equals("SQLServer Authentication"))
{
ODBConnection.connectionString = $"{dbName.Text}:{dbUser.Text}:{dbPass.Text}";
ODBConnection.winAuth = false;
}
}
else if (advancedOptions)
{
if (serverHostname.Text == "")
{
MessageBox.Show("Server address is still empty !", Resources.XMLGUI__warning, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (serverInstance.Text == "" || serverInstance.Text == "\\")
{
var result = MessageBox.Show(Resources.DatabaseConnection_emptyInstance_msg, Resources.XMLGUI__warning, MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
if (result == DialogResult.Cancel)
{
return;
}
serverInstance.Text = "";
}
ODBConnection.remote = true;
if (dbAuthType.SelectedItem.Equals("Windows Authentication"))
{
ODBConnection.winAuth = true;
ODBConnection.connectionString = $"{dbName.Text}:{dbUser.Text}:{dbPass.Text}:{serverHostname.Text}:{serverInstance}";
}
else
{
ODBConnection.winAuth = false;
ODBConnection.connectionString = $"{dbName.Text}:{dbUser.Text}:{dbPass.Text}:{serverHostname.Text}:{serverInstance}";
}
}
}
try
{
databaseName = ODBConnection.getConnection().Database;
connectionSuccess = true;
}
catch (SqlException sqle)
{
MessageBox.Show(string.Format(Resources.Connection_fail_msg, sqle.Number, sqle.Message), Resources.XMLGUI__warning, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
MessageBox.Show(string.Format(Resources.IEDatabase_successConnection_msg, databaseName), Resources.success, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
this.DialogResult = DialogResult.OK;
this.Close();
}