public Boolean EmployeeRemove(int employeeId)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
string dateFormat = DateTime.Now.ToString("yyyy-MM-dd");
sqlQuery = "UPDATE employee SET end_date = '" + dateFormat + "' WHERE employee_id = " + employeeId;
MySqlCommand command = connection.CreateCommand();
command.CommandText = sqlQuery;
connection.Open();
int response = command.ExecuteNonQuery();
if (response > 0)
{
return true;
}
else
return false;
}
catch (Exception ex)
{
return false;
}
finally
{
connection.Close();
}
}
public Boolean PriceChange(int productId, float price, String effectiveDate, String office, int officeId)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
List<int> storeList = new List<int>();
storeList = getChildStoreIds(officeId, office);
if (storeList.Count > 0)
{
sqlQuery = "UPDATE store_product SET effective_price=" + price + ", effective_date = '" + effectiveDate + "' WHERE store_id IN (" + store(storeList) + ") AND product_id = " + productId;
MySqlCommand command = connection.CreateCommand();
command.CommandText = sqlQuery;
int response = command.ExecuteNonQuery();
if (response > 0)
{
return true;
}
}
return false;
}
catch (Exception ex)
{
return false;
}
finally
{
connection.Close();
}
}
public Boolean EmployeeAdd(string name, string ssn, string address, string gender, string jobTitle, float salary, string worksFor, int officeId, string startDate, string username, string password)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
string dateFormat = DateTime.Now.ToString("yyyy-MM-dd");
sqlQuery = "INSERT INTO employee (name, ssn, address, gender, job_title, salary, works_for, office_id, start_date, username, password) VALUES ('" + name + "', '" +
ssn + "', '" + address + "', '" + gender + "', '" + jobTitle + "', " + salary + ", '" + worksFor + "', " + officeId + ", '" + startDate + "', '" + username + "', '" +
password + "')";
MySqlCommand command = connection.CreateCommand();
command.CommandText = sqlQuery;
connection.Open();
int response = command.ExecuteNonQuery();
if (response > 0)
{
return true;
}
else
return false;
}
catch (Exception ex)
{
return false;
}
finally
{
connection.Close();
}
}
public String PriceChange(int productId, String office, int officeId)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
List<int> storeList = new List<int>();
storeList = getChildStoreIds(officeId, office);
sqlQuery = "SELECT * FROM store JOIN (SELECT * FROM store_product WHERE product_id=" + productId + " AND store_id IN (" +
store(storeList) + ")) sp WHERE store.store_id=sp.store_id";
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
return "123";
}
catch (Exception ex)
{
return new JavaScriptSerializer().Serialize(ex.Message);
}
finally
{
connection.Close();
}
}
public float Payroll(string office, int officeId)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
List<int> storeList = new List<int>();
storeList = getChildStoreIds(officeId, office);
sqlQuery = "SELECT sum(salary) as sum FROM employee WHERE works_for='Retail' AND office_id IN(" + store(storeList) + ")";
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
float count = 0;
while (sdr.Read())
{
count = float.Parse(sdr["sum"].ToString());
}
return count;
}
catch (Exception ex)
{
return 0;
}
finally
{
connection.Close();
}
}
public Boolean RemoveItem(int productId, int officeId, String office)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
List<int> storeList = new List<int>();
storeList = getChildStoreIds(officeId, office);
String stores = store(storeList);
sqlQuery = "UPDATE store_product SET discontinue = true WHERE product_id=" + productId + " AND store_id IN (" + store(storeList) + ")";
MySqlCommand command = connection.CreateCommand();
command.CommandText = sqlQuery;
int response = command.ExecuteNonQuery();
if (response > 0)
{
return true;
}
else
return false;
}
catch (Exception ex)
{
return false;
}
finally
{
connection.Close();
}
}
public Boolean ItemAdd(String productName, String productDescription, float price, int productId, String office, int officeId)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
List<int> storeList = new List<int>();
storeList = getChildStoreIds(officeId, office);
//store(storeList);
if (storeList.Count > 0)
{
if (productId == 0)
{
sqlQuery = "INSERT INTO product (product_id, product_name, product_description) VALUES (NULL, '" + productName + "', '" + productDescription + "')";
MySqlCommand newCommand = connection.CreateCommand();
newCommand.CommandText = sqlQuery;
newCommand.ExecuteNonQuery();
}
foreach (int eachStore in storeList)
{
sqlQuery = "SELECT * FROM store_product WHERE product_id=" + productId + " AND store_id=" + eachStore;
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
if (!sdr.HasRows)
{
sdr.Close();
sqlQuery = "INSERT INTO store_product (store_id, product_id, price) VALUES (" + eachStore + ", " + productId + ", " + price + ")";
MySqlCommand newCommand = connection.CreateCommand();
newCommand.CommandText = sqlQuery;
newCommand.ExecuteNonQuery();
}
sdr.Close();
}
return true;
}
return false;
}
catch (Exception ex)
{
return false;
//return new JavaScriptSerializer().Serialize(ex.Message); ;
}
finally
{
connection.Close();
}
}
public string Sale(int storeId, int productId, int quantity, int employeeId)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
DateTime saleDate = DateTime.Now;
sqlQuery = "SELECT * FROM store_product WHERE product_id = " + productId + " AND store_id = " + storeId;
connection.Open();
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
if (sdr.Read())
{
currentQuantity = Int32.Parse(sdr["quantity"].ToString());
if (quantity > currentQuantity)
{
return "Insufficient Quantity";
}
if ((Boolean)sdr["discontinue"])
{
return "Product Discontinued";
}
}
sdr.Close();
sqlQuery = "INSERT INTO sales (store_id, product_id, quantity, sale_date, employee_id) VALUES (" + storeId + ", " + productId + ", " + quantity + ",'" + DateTime.Now.ToString("yyyy-MM-dd") + "'," + employeeId + ")";
MySqlCommand newCommand = new MySqlCommand(sqlQuery, connection);
int response = newCommand.ExecuteNonQuery();
if (response > 0)
{
int newQuantity = currentQuantity - quantity;
sqlQuery = "UPDATE store_product SET quantity = " + newQuantity + " WHERE store_id=" + storeId + " AND product_id=" + productId;
MySqlCommand newNewCommand = new MySqlCommand(sqlQuery, connection);
newNewCommand.ExecuteNonQuery();
return "Success";
}
return "Failure";
}
catch (Exception ex)
{
//return new JavaScriptSerializer().Serialize(ex.Message);
return ex.Message;
}
finally
{
connection.Close();
}
}
public string ListItems(int productId, string office, int officeId)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
List<int> storeList = new List<int>();
storeList = getChildStoreIds(officeId, office);
string sqlQuery = "SELECT * FROM product WHERE product_id = " + productId;
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
string productName = "";
string productDescription = "";
while (sdr.Read())
{
productName = sdr["product_name"].ToString();
productDescription = sdr["product_description"].ToString();
}
sdr.Close();
sqlQuery = "SELECT * FROM store_product WHERE product_id =" + productId + " AND store_id IN(" + store(storeList) + ")";
MySqlCommand newCommand = new MySqlCommand(sqlQuery, connection);
sdr = newCommand.ExecuteReader();
if (sdr.HasRows)
{
var result = new List<inventory>();
while (sdr.Read())
{
result.Add(new inventory { productId = Int32.Parse(sdr["product_id"].ToString()), productName = productName, productDescription = productDescription,
price = float.Parse(sdr["price"].ToString()), quantity = Int32.Parse(sdr["quantity"].ToString()), storeId=Int32.Parse(sdr["store_id"].ToString()), effectiveDate=sdr["effective_date"].ToString(),
effectivePrice=float.Parse(sdr["effective_price"].ToString()), discontinue=bool.Parse(sdr["discontinue"].ToString())});
}
sdr.Close();
return new JavaScriptSerializer().Serialize(result);
}
}
catch (Exception ex)
{
return new JavaScriptSerializer().Serialize(ex.Message);
}
return "Hello World";
}
public Boolean InventoryAdd(int storeId, int productId, int quantity)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
sqlQuery = "SELECT quantity FROM store_product WHERE store_id=" + storeId + " AND product_id=" + productId;
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
if (sdr.Read())
{
int newQuantity = quantity + Int32.Parse(sdr["quantity"].ToString());
sqlQuery = "UPDATE store_product SET quantity = " + newQuantity + " WHERE store_id=" + storeId + " AND product_id=" + productId;
}
else
{
sqlQuery = "INSERT INTO store_product (store_id, product_id, quantity) VALUES (" + storeId + "," + productId + "," + quantity + ")";
}
sdr.Close();
MySqlCommand newCommand = connection.CreateCommand();
newCommand.CommandText = sqlQuery;
int response = newCommand.ExecuteNonQuery();
if (response > 0)
{
return true;
}
else
return false;
}
catch (Exception ex)
{
//var result = new { result = "error", message = "LoginFailed" };
//return new JavaScriptSerializer().Serialize(ex.Message);
return false;
}
finally
{
connection.Close();
}
}
public string Login(string userName, string password)
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
int userRole = 0;
try
{
connection.Open();
string sqlQuery = "SELECT * FROM employee WHERE username = '******' and password = '******'";
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
if(sdr.HasRows)
{
if (sdr.Read())
{
var result = new { employeeId = sdr["employee_id"].ToString(), worksFor=sdr["works_for"].ToString(), officeId=sdr["office_id"].ToString()};
return new JavaScriptSerializer().Serialize(result);
}
}
}
catch (Exception ex)
{
var result = new { result="connection failed"};
return new JavaScriptSerializer().Serialize(result);
}
if (userRole != 0)
{
var result = new { result = "success", UserRole = userRole};
return new JavaScriptSerializer().Serialize(result);
}
else
{
var result = new { result = "error", message = "LoginFailed"};
return new JavaScriptSerializer().Serialize(result);
}
return "Hello World";
}
public String EmployeeInfo(string office, int officeId)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
List<int> storeList = new List<int>();
storeList = getChildStoreIds(officeId, office);
int storeInHierarchy = 0;
List<int> newStoreList = new List<int>();
foreach (int stores in storeList)
{
if (stores == officeId)
{
storeInHierarchy = 1;
}
else
{
newStoreList.Add(stores);
}
}
if (storeInHierarchy == 1)
{
return "Not in the same Hierarchy";
}
if (newStoreList.Count > 0)
{
sqlQuery = "SELECT * FROM employee WHERE works_for='Retail' AND office_id IN(" + store(newStoreList) + ")";
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
var result = new List<Employee>();
while (sdr.Read())
{
result.Add(new Employee
{
name = sdr["name"].ToString(),
ssn = sdr["ssn"].ToString(),
address = sdr["address"].ToString(),
gender = sdr["gender"].ToString(),
jobTitle = sdr["job_title"].ToString(),
salary = float.Parse(sdr["salary"].ToString()),
works_for = sdr["works_for"].ToString(),
officeId = int.Parse(sdr["office_id"].ToString()),
// startDate = sdr["start_date"].ToString(),
// endDate = sdr["end_date"].ToString(),
username = sdr["username"].ToString()
});
}
sdr.Close();
return new JavaScriptSerializer().Serialize(result);
}
else
{
return null;
}
}
catch (Exception ex)
{
return new JavaScriptSerializer().Serialize(ex.Message);
}
finally
{
connection.Close();
}
}
public String SalesInfo(string office, int officeId)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
List<int> storeList = new List<int>();
storeList = getChildStoreIds(officeId, office);
int storeInHierarchy = 0;
List<int> newStoreList = new List<int>();
foreach (int stores in storeList)
{
if (stores == officeId)
{
storeInHierarchy = 1;
}
else
{
newStoreList.Add(stores);
}
}
if (storeInHierarchy == 1)
{
return "Not in the same Hierarchy";
}
if (newStoreList.Count > 0)
{
sqlQuery = "SELECT * FROM sales WHERE store_id IN(" + store(newStoreList) + ")";
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
var result = new List<Sales>();
while (sdr.Read())
{
result.Add(new Sales
{
storeId = int.Parse(sdr["store_id"].ToString()),
quantity = int.Parse(sdr["quantity"].ToString()),
salesDate = sdr["sale_date"].ToString(),
employeeId = int.Parse(sdr["employee_id"].ToString()),
});
}
sdr.Close();
return new JavaScriptSerializer().Serialize(result);
}
else
{
return null;
}
}
catch (Exception ex)
{
return new JavaScriptSerializer().Serialize(ex.Message);
}
finally
{
connection.Close();
}
}
public String TransferToStore(int productId, int quantity, int fromStoreId, int toStoreId, float price)
{
try
{
int currentQuantityFromStore = 0;
int currentQuantityToStore = 0;
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
sqlQuery = "SELECT quantity FROM store_product WHERE product_id = " + productId + " AND store_id = " + fromStoreId;
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
if (sdr.Read())
{
currentQuantityFromStore = Int32.Parse(sdr["quantity"].ToString());
if (quantity > currentQuantityFromStore)
{
return "234";
}
}
sdr.Close();
sqlQuery = "SELECT quantity FROM store_product WHERE product_id = " + productId + " AND store_id = " + toStoreId;
MySqlCommand fromCommand = new MySqlCommand(sqlQuery, connection);
sdr = fromCommand.ExecuteReader();
if(sdr.Read())
{
currentQuantityToStore = Int32.Parse(sdr["quantity"].ToString());
}
sdr.Close();
sqlQuery = "UPDATE store_product SET quantity=" + (currentQuantityFromStore - quantity) + " WHERE store_id=" + fromStoreId + " AND product_id=" + productId;
MySqlCommand newCommand = new MySqlCommand(sqlQuery, connection);
newCommand.ExecuteNonQuery();
sqlQuery = "SELECT * FROM store_product WHERE store_id=" + toStoreId + " AND product_id=" + productId;
MySqlCommand checkCommand = new MySqlCommand(sqlQuery, connection);
sdr = checkCommand.ExecuteReader();
if(sdr.HasRows)
{
sqlQuery = "UPDATE store_product SET quantity=" + (currentQuantityToStore + quantity) + " WHERE store_id=" + toStoreId + " AND product_id=" + productId;
}
else
{
sqlQuery = "INSERT INTO store_product (store_id, product_id, quantity, price) VALUES (" + toStoreId + ", " + productId + ", " + quantity + ", " + price + ")";
}
sdr.Close();
MySqlCommand newNewCommand = new MySqlCommand(sqlQuery, connection);
newNewCommand.ExecuteNonQuery();
return "123";
}
catch (Exception ex)
{
return new JavaScriptSerializer().Serialize(ex.Message);
//return false;
}
finally
{
connection.Close();
}
}