private void ResourceManagement_Load(object sender, EventArgs e)
{
using (var context = new Session1Entities())
{
#region Populating the Type of Resources Combo nox
typeBox.Items.Add("No Filter");
HashSet <string> type = new HashSet <string>();
var getTypes = (from x in context.Resource_Type
select x.resTypeName);
foreach (var types in getTypes)
{
type.Add(types);
}
typeBox.Items.AddRange(type.ToArray());
#endregion
#region Populating the skills from DB into the Skill's Combo box
skilBox.Items.Add("No Filter");
HashSet <string> skill = new HashSet <string>();
var getSkills = (from x in context.Skills
select x.skillName);
foreach (var skills in getSkills)
{
skill.Add(skills);
}
skilBox.Items.AddRange(skill.ToArray());
#endregion
}
//Initial loading of DGV with no fields/filters selected
GridRefresh();
}
private void ResouceManagement_Load(object sender, EventArgs e)
{
using (var db = new Session1Entities())
{
var query = db.Resources.OrderBy(x => x.remainingQuantity).ToList();
dataGridView1.DataSource = CDT(query);
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.Columns["ID"].Visible = false;
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
if (dr.Cells["Available Quantity"].Value.ToString() == "Not Available")
{
dr.DefaultCellStyle.BackColor = Color.Red;
}
}
//Filter not working.
var query2 = db.Resource_Type.ToList();
foreach (var item in query2)
{
type.Items.Add(item.resTypeName);
}
var query3 = db.Skills.ToList();
foreach (var item in query3)
{
Skill.Items.Add(item.skillName);
}
}
}
private void DelBtn_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow dr in dataGridView1.SelectedRows)
{
using (var db = new Session1Entities())
{
var ID = int.Parse(dr.Cells["ID"].Value.ToString());
var query = db.Resources.Where(x => x.resId == ID).FirstOrDefault();
db.Resources.Remove(query);
var query2 = db.Resource_Allocation.Where(x => x.resIdFK == ID).ToList();
foreach (var item in query2)
{
db.Resource_Allocation.Remove(item);
}
try {
db.SaveChanges();
MessageBox.Show("Deleted Successfully!");
var q = db.Resources.ToList();
dataGridView1.DataSource = CDT(q);
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.Columns["ID"].Visible = false;
foreach (DataGridViewRow datar in dataGridView1.Rows)
{
if (datar.Cells["Available Quantity"].ToString() == "Not Available")
{
datar.DefaultCellStyle.BackColor = Color.Red;
}
}
}
catch (Exception es) { MessageBox.Show(es.ToString()); }
}
}
}
private void UpBtn_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count == 1)
{
foreach (DataGridViewRow dr in dataGridView1.SelectedRows)
{
var ID = int.Parse(dr.Cells["ID"].Value.ToString());
Update_Resource update_Resource = new Update_Resource(ID);
update_Resource.Show();
this.Hide();
using (var db = new Session1Entities())
{
var q = db.Resources.ToList();
dataGridView1.DataSource = CDT(q);
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.Columns["ID"].Visible = false;
foreach (DataGridViewRow datar in dataGridView1.Rows)
{
if (datar.Cells["Available Quantity"].Value.ToString() == "Not Available")
{
datar.DefaultCellStyle.BackColor = Color.Red;
}
}
}
}
}
}
private void AddResource_Load(object sender, EventArgs e)
{
using (var context = new Session1Entities())
{
#region Populating the Type of Resources Combo Box
var getType = (from x in context.Resource_Type
select x.resTypeName);
HashSet <string> resouceType = new HashSet <string>();
foreach (var item in getType)
{
resouceType.Add(item);
}
typeBox.Items.AddRange(resouceType.ToArray());
#endregion
#region Populating Skill Allocation Checklist Box
var getSkills = (from x in context.Skills
select x.skillName);
var skills = new HashSet <string>();
foreach (var item in getSkills)
{
skills.Add(item);
}
allocationBox.Items.AddRange(skills.ToArray());
#endregion
}
}
private void CABtn_Click(object sender, EventArgs e)
{
using (var db = new Session1Entities())
{
User user = new User();
user.userName = UName.Text;
if (UID.TextLength < 8)
{
MessageBox.Show("User ID must have a minimum of 8 characters!");
return;
}
else
{
var query2 = db.Users.Where(x => x.userId == UID.Text).FirstOrDefault();
if (query2 != null)
{
MessageBox.Show("User ID already exists!");
}
else
{
user.userId = UID.Text;
}
}
//Checking password
if (Pass.TextLength == 0)
{
MessageBox.Show("Password must have a value!");
return;
}
if (PassC.TextLength == 0)
{
MessageBox.Show("Confirm Password must have a value!");
return;
}
if (Pass.Text != PassC.Text)
{
MessageBox.Show("Passwords do not match!");
}
else
{
user.userPw = Pass.Text;
}
var ID = UType.SelectedIndex + 1;
var query = db.User_Type.Where(x => x.userTypeId == ID).FirstOrDefault();
user.userTypeIdFK = query.userTypeId;
try
{
db.Users.Add(user);
db.SaveChanges();
MessageBox.Show("Account Created Successfully!");
this.Hide();
Form1 form = new Form1();
form.ShowDialog();
}catch (Exception es)
{
MessageBox.Show(es.ToString());
}
}
}
private void loginBtn_Click(object sender, EventArgs e)
{
using (var context = new Session1Entities())
{
//Checks for empty User ID field
if (userBox.Text.Trim() == "")
{
MessageBox.Show("User ID field is empty!", "Empty Fields", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
//Checks for empty Password field
else if (passwordBox.Text.Trim() == "")
{
MessageBox.Show("Password field is empty!", "Empty Fields", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
else
{
var getUser = (from x in context.Users
where x.userId.Equals(userBox.Text.Trim())
join y in context.User_Type on x.userTypeIdFK equals y.userTypeId
select new { Password = x.userPw, Name = x.userName, UserType = y.userTypeName, ID = x.userId }).FirstOrDefault();
//Checks if User account exist in DB. Else prompts error message
if (!getUser.ID.Any())
{
MessageBox.Show("User does not exist!", "Invalid Account", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
//Check if entered ID has password matching with what is keyed into the password field
else if (passwordBox.Text.Trim() != getUser.Password)
{
MessageBox.Show("Password is incorrect!", "Password Wrong", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
//Checks for User type as application only let Resource Managers in
else if (getUser.UserType != "Manager")
{
MessageBox.Show("Sorry, your account is not an account of a Resource Manager!", "Account invalid", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
//Allow login if all check passes
else
{
MessageBox.Show($"Welcome {getUser.Name}!", "Welcome!", MessageBoxButtons.OK,
MessageBoxIcon.Information);
this.Hide();
(new ResourceManagement()).ShowDialog();
this.Close();
}
}
}
}
private void createBtn_Click(object sender, EventArgs e)
{
using (var context = new Session1Entities())
{
//Checks if User ID is at least 8 characters long, else error message
if (userIdBox.TextLength < 8)
{
MessageBox.Show("ID needs to be at least 8 characters");
}
//Checks if password fields are the same, else error message
else if (passwordBox.Text != rePasswordBox.Text)
{
MessageBox.Show("Passwords must be the same!");
}
//Checks if User's type is selected, else error message
else if (typeUserBox.SelectedItem == null)
{
MessageBox.Show("Please choose user type!");
}
else
{
//Check if User ID exist in the Database. If ID exist, prompt user an error message.
//If not, allow creation of new account
var checkUserID = (from x in context.Users
where x.userId == userIdBox.Text
select x).FirstOrDefault();
if (checkUserID == null)
{
var getUserTypeID = (from x in context.User_Type
where x.userTypeName == typeUserBox.SelectedItem.ToString()
select x.userTypeId).First();
context.Users.Add(new User()
{
userName = userNameBox.Text,
userId = userIdBox.Text,
userPw = passwordBox.Text,
userTypeIdFK = getUserTypeID
});
context.SaveChanges();
this.Hide();
(new Main()).ShowDialog();
this.Close();
}
else
{
MessageBox.Show("User ID already exist! Please choose another ID!", "Used ID",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
}
private void UpdateBtn_Click(object sender, EventArgs e)
{
using (var db = new Session1Entities())
{
var query = db.Resources.Where(x => x.resId == IDs).FirstOrDefault();
query.remainingQuantity = (int)quantity.Value;
if (skills.Visible == false)
{
try
{
db.SaveChanges();
MessageBox.Show("Successful!");
this.Hide();
ResouceManagement resouceManagement = new ResouceManagement();
resouceManagement.Show();
}
catch (Exception ES)
{
MessageBox.Show(ES.ToString());
}
}
else
{
var query2 = db.Resource_Allocation.Where(x => x.resIdFK == IDs).ToList();
foreach (var item in query2)
{
db.Resource_Allocation.Remove(item);
}
for (int i = 0; i < skills.CheckedItems.Count; i++)
{
Resource_Allocation resource_Allocation = new Resource_Allocation();
resource_Allocation.resIdFK = IDs;
resource_Allocation.skillIdFK = i + 1;
db.Resource_Allocation.Add(resource_Allocation);
}
try
{
db.SaveChanges();
MessageBox.Show("Successful!");
this.Hide();
ResouceManagement resouceManagement = new ResouceManagement();
resouceManagement.Show();
}
catch (Exception es)
{
MessageBox.Show(es.ToString());
}
}
}
}
private void CreateNewAccount_Load(object sender, EventArgs e)
{
using (var db = new Session1Entities())
{
var query = db.User_Type.ToList();
if (query != null)
{
foreach (var item in query)
{
UType.Items.Add(item.userTypeName);
}
UType.SelectedIndex = 0;
}
}
}
private void Create_Load(object sender, EventArgs e)
{
//When form is loaded, add User type into the combo box named: typeUserBox
HashSet <string> vs = new HashSet <string>();
using (var context = new Session1Entities())
{
var getType = (from x in context.User_Type
select x.userTypeName);
foreach (var item in getType)
{
vs.Add(item);
}
typeUserBox.Items.AddRange(vs.ToArray());
}
}
private void LoginBtn_Click(object sender, EventArgs e)
{
using (var db = new Session1Entities())
{
var query = db.Users.Where(x => x.userId == UID.Text && x.userPw == Pass.Text).FirstOrDefault();
if (query != null)
{
this.Hide();
ResouceManagement resouceManagement = new ResouceManagement();
resouceManagement.ShowDialog();
}
else
{
MessageBox.Show("Invalid User!");
}
}
}
private void AddResource_Load(object sender, EventArgs e)
{
using (var db = new Session1Entities())
{
var query = db.Resource_Type.ToList();
foreach (var item in query)
{
type.Items.Add(item.resTypeName);
}
var query2 = db.Skills.ToList();
foreach (var item in query2)
{
skills.Items.Add(item.skillName);
}
skills.Visible = false;
}
}
/// <summary>
/// This method is called when the delete button is triggered
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void deleteBtn_Click(object sender, EventArgs e)
{
//Checks if they is a selected row, else prompts user to select a row to delete
if (dataGridView1.CurrentRow == null)
{
MessageBox.Show("Please select a resource to delete!", "No resource selected", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
//Runs when VadCheck at If clause is passed
else
{
var getResourceID = Convert.ToInt32(dataGridView1.CurrentRow.Cells[5].Value);
using (var context = new Session1Entities())
{
var getResourceDelete = (from x in context.Resources
where x.resId == getResourceID
select x).First();
var getAllocationDelete = (from x in context.Resource_Allocation
where x.resIdFK == getResourceID
select x);
context.Resources.Remove(getResourceDelete);
foreach (var item in getAllocationDelete)
{
context.Resource_Allocation.Remove(item);
}
context.SaveChanges();
var getRowToDelete = dataGridView1.CurrentRow.Index;
dataGridView1.Rows.RemoveAt(getRowToDelete);
}
}
}
//Directs User to Update Resource page - 1.6
private void updateBtn_Click(object sender, EventArgs e)
{
//Check if there is a selected row. Else prompts user to select a resource to update
if (dataGridView1.CurrentRow == null)
{
MessageBox.Show("Please select a resource to update!", "No resource selected",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//Runs and direct user with parsing correct resource information to update selected resource
else
{
using (var context = new Session1Entities())
{
var resName = dataGridView1.CurrentRow.Cells[0].Value.ToString();
var getID = (from x in context.Resources
where x.resName == resName
select x.resId).First();
this.Hide();
(new Update(getID)).ShowDialog();
this.Close();
}
}
}
void filter()
{
using (var db = new Session1Entities())
{
var q = db.Resources.ToList();
if (string.IsNullOrEmpty(type.Text) == false)
{
var IDss = type.SelectedIndex + 1;
q = q.Where(x => x.resTypeIdFK == IDss).ToList();
}
if (string.IsNullOrEmpty(Skill.Text) == false)
{
List <Resource> resources = new List <Resource>();
var query = db.Resource_Allocation.Where(x => x.Skill.skillName == Skill.Text).ToList();
foreach (var item in query)
{
var res = q.Where(x => x.resId == item.resIdFK).FirstOrDefault();
if (res != null)
{
resources.Add(res);
}
}
q = resources;
}
dataGridView1.DataSource = CDT(q);
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.Columns["ID"].Visible = false;
foreach (DataGridViewRow datar in dataGridView1.Rows)
{
if (datar.Cells["Available Quantity"].Value.ToString() == "Not Available")
{
datar.DefaultCellStyle.BackColor = Color.Red;
}
}
}
}
private void Update_Resource_Load(object sender, EventArgs e)
{
using (var db = new Session1Entities())
{
var query = db.Resources.Where(x => x.resId == IDs).FirstOrDefault();
LblName.Text = query.resName;
typeLbl.Text = query.Resource_Type.resTypeName;
quantity.Value = query.remainingQuantity;
if (quantity.Value == 0)
{
skills.Visible = false;
}
var query2 = db.Skills.ToList();
foreach (var item in query2)
{
skills.Items.Add(item.skillName);
}
var query3 = db.Resource_Allocation.Where(x => x.resIdFK == IDs);
foreach (var item in query3)
{
skills.SetItemChecked(item.Skill.skillId, true);
}
}
}
private void AddBtn_Click(object sender, EventArgs e)
{
using (var db = new Session1Entities())
{
Resource resource = new Resource();
var query = db.Resources.Where(x => x.resName == name.Text).FirstOrDefault();
if (query != null)
{
MessageBox.Show("Resource Name is already used!");
return;
}
else
{
resource.resName = name.Text;
resource.resTypeIdFK = type.SelectedIndex + 1;
resource.remainingQuantity = (int)quantity.Value;
try
{
db.Resources.Add(resource);
if (skills.CheckedItems.Count > 0)
{
foreach (var item in skills.CheckedItems)
{
var name = item.ToString();
var val = db.Skills.Where(x => x.skillName == name).FirstOrDefault();
var ID = resource.resId;
Resource_Allocation resource_ = new Resource_Allocation();
resource_.resIdFK = ID;
resource_.skillIdFK = val.skillId;
RA.Add(resource_);
}
try
{
if (RA.Count == 0)
{
var ID = resource.resId;
Resource_Allocation resource_ = new Resource_Allocation();
resource_.resIdFK = ID;
db.Resource_Allocation.Add(resource_);
}
foreach (var item in RA)
{
db.Resource_Allocation.Add(item);
}
db.SaveChanges();
MessageBox.Show("Created Successfully!");
this.Hide();
}
catch (Exception es)
{
MessageBox.Show(es.ToString());
}
}
else
{
var ID = resource.resId;
Resource_Allocation resource_ = new Resource_Allocation();
resource_.resIdFK = ID;
db.Resource_Allocation.Add(resource_);
try
{
db.SaveChanges();
}catch (Exception es)
{
MessageBox.Show(es.ToString());
}
MessageBox.Show("Created Successfully!");
this.Hide();
}
}
catch (Exception es)
{
MessageBox.Show(es.ToString());
}
}
}
}
/// <summary>
/// This method is called when the Add button is triggered
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void addBtn_Click(object sender, EventArgs e)
{
using (var context = new Session1Entities())
{
var checkResourceExistence = (from x in context.Resources
where x.resName == resourceNameBox.Text
select x).FirstOrDefault();
var getNewID = (from x in context.Resources
orderby x.resId descending
select x.resId).First() + 1;
//Check if intended new resource has a record in the database
if (checkResourceExistence != null)
{
MessageBox.Show("Resource already exist in Database!", "Duplicate Resource", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
else
{
//Check if the Type of Resource and and quantity is selected and keyed in properly
if (typeBox.SelectedItem == null || quantityBox.Text == null)
{
MessageBox.Show("Please check your entries again!", "Empty Field(s)", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
//Check if there is any allocation if quantity is more than 0. Resource must have an allocation if quantity is more than 0
else if (Int32.Parse(quantityBox.Text) > 0 && allocationBox.CheckedItems.Count == 0)
{
MessageBox.Show("Resource must have at least 1 allocated skill!", "Allocation of resource not set", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
//Check if user keyed in an invalid integer
else if (Int32.Parse(quantityBox.Text) < 0)
{
MessageBox.Show("Resource amount cannot be negative!", "Invalid Amount", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
//Check if there are any allocation if the quantity is set to 0
else if (Int32.Parse(quantityBox.Text) == 0 && allocationBox.CheckedItems.Count > 0)
{
MessageBox.Show("Resource cannot be allocated if amount is 0", "Unable to allocate resource",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
//If quantity is 0, add everything keyed in except for skill allocation to DB
if (Int32.Parse(quantityBox.Text) == 0)
{
var getTypeID = (from x in context.Resource_Type
where x.resTypeName == typeBox.SelectedItem.ToString()
select x.resTypeId).First();
context.Resources.Add(new Resource()
{
resId = getNewID,
resName = resourceNameBox.Text,
remainingQuantity = Int32.Parse(quantityBox.Text),
resTypeIdFK = getTypeID
});
}
//If quantity more 0, add everything keyed in to DB
else if (Int32.Parse(quantityBox.Text) > 0)
{
var getTypeID = (from x in context.Resource_Type
where x.resTypeName == typeBox.SelectedItem.ToString()
select x.resTypeId).First();
context.Resources.Add(new Resource()
{
resId = getNewID,
resName = resourceNameBox.Text,
remainingQuantity = Int32.Parse(quantityBox.Text),
resTypeIdFK = getTypeID
});
if (list.Count() != 0)
{
foreach (var item in list)
{
var getSkillID = (from x in context.Skills
where x.skillName == item
select x.skillId).First();
context.Resource_Allocation.Add(new Resource_Allocation()
{
resIdFK = getNewID, skillIdFK = getSkillID
});
}
}
}
context.SaveChanges();
resourceNameBox.Text = "";
typeBox.SelectedItem = null;
quantityBox.Text = "";
allocationBox.SelectedItems.Clear();
list.Clear();
}
}
}
}
/// <summary>
/// This event is triggered when the update button is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnUpdate_Click(object sender, EventArgs e)
{
using (var context = new Session1Entities())
{
//Check if quantity is empty
if (txtQuantityBox.Text == null)
{
MessageBox.Show("Please check your entries again!", "Empty Field(s)", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
//Check if quantity is more than 0 but no allocation of skill is made
else if (Int32.Parse(txtQuantityBox.Text) > 0 && clbAllocation.CheckedItems.Count == 0)
{
MessageBox.Show("Resource must have at least 1 allocated skill!", "Allocation of resource not set", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
//Check if quantity is invalid as in negative / less than 0
else if (Int32.Parse(txtQuantityBox.Text) < 0)
{
MessageBox.Show("Resource amount cannot be negative!", "Invalid Amount", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
else
{
//Check if quantity is 0 but there is allocation(s) of skill
if (Int32.Parse(txtQuantityBox.Text) == 0 && clbAllocation.CheckedItems.Count > 0)
{
MessageBox.Show("Resource cannot be allocated if amount is 0", "Unable to allocate resource",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
//If quantity is 0 and no allocation checks out, update amount to DB
else if (Int32.Parse(txtQuantityBox.Text) == 0 && clbAllocation.CheckedItems.Count == 0)
{
var updateResource = (from x in context.Resources
where x.resId == _resID
select x).First();
updateResource.remainingQuantity = Int32.Parse(txtQuantityBox.Text);
}
//Else update everything into DB
else if (Int32.Parse(txtQuantityBox.Text) > 0)
{
var updateResource = (from x in context.Resources
where x.resId == _resID
select x).First();
updateResource.remainingQuantity = Int32.Parse(txtQuantityBox.Text);
if (_list.Count() != 0)
{
foreach (var item in _list)
{
var getSkillID = (from x in context.Skills
where x.skillName == item
select x.skillId).First();
context.Resource_Allocation.Add(new Resource_Allocation()
{
resIdFK = _resID, skillIdFK = getSkillID
});
}
}
}
context.SaveChanges();
this.Hide();
(new ResourceManagement()).ShowDialog();
this.Close();
}
}
}
/// <summary>
/// To load Datagridview components
/// </summary>
private void GridRefresh()
{
dataGridView1.ColumnCount = 6;
dataGridView1.Columns[0].Name = "Name";
dataGridView1.Columns[1].Name = "Type";
dataGridView1.Columns[2].Name = "No. of Skills";
dataGridView1.Columns[3].Name = "Allocated Skill(s)";
dataGridView1.Columns[4].Name = "Available Quantity";
dataGridView1.Columns[5].Name = "Resource ID";
dataGridView1.Columns[5].Visible = false;
using (var context = new Session1Entities())
{
//Loads DGV for no filters
if (typeBox.SelectedItem != null && skilBox.SelectedItem != null && skilBox.SelectedItem.ToString() != "No Filter" && typeBox.SelectedItem.ToString() != "No Filter")
{
var getTypeID = (from x in context.Resource_Type
where x.resTypeName == typeBox.SelectedItem.ToString()
select x.resTypeId).First();
var getSkillID = (from x in context.Skills
where x.skillName == skilBox.SelectedItem.ToString()
select x.skillId).First();
var getResources = (from x in context.Resources
join y in context.Resource_Allocation on x.resId equals y.resIdFK
where y.skillIdFK == getSkillID && x.resTypeIdFK == getTypeID
orderby x.remainingQuantity descending
select x).ToList();
foreach (var resources in getResources)
{
List <string> rows = new List <string>()
{
resources.resName, resources.Resource_Type.resTypeName, resources.Resource_Allocation.Count().ToString()
};
string skillsAllocated = "";
if (resources.Resource_Allocation.Where(x => x.resIdFK == resources.resId).Select(x => x).FirstOrDefault() == null)
{
skillsAllocated += "NIL";
}
else
{
foreach (var skills in resources.Resource_Allocation)
{
if (skillsAllocated != "")
{
skillsAllocated += ", " + skills.Skill.skillName;
}
else if (skillsAllocated == "")
{
skillsAllocated += skills.Skill.skillName;
}
}
}
rows.Add(skillsAllocated);
var checkQuantity = (from x in context.Resources
where x.resId == resources.resId
select x.remainingQuantity).FirstOrDefault();
if (checkQuantity == 0)
{
rows.Add("Not Available");
}
else if (checkQuantity <= 5 && checkQuantity >= 1)
{
rows.Add("Low Stock");
}
else
{
rows.Add("Sufficient");
}
rows.Add(resources.resId.ToString());
dataGridView1.Rows.Add(rows.ToArray());
}
}
//Loads DGV when Type of Resource filter is selected
else if (typeBox.SelectedItem != null && typeBox.SelectedItem.ToString() != "No Filter")
{
var getTypeID = (from x in context.Resource_Type
where x.resTypeName == typeBox.SelectedItem.ToString()
select x.resTypeId).First();
var getResources = (from x in context.Resources
where x.resTypeIdFK == getTypeID
orderby x.remainingQuantity descending
select x).ToList();
foreach (var resources in getResources)
{
List <string> rows = new List <string>()
{
resources.resName, resources.Resource_Type.resTypeName, resources.Resource_Allocation.Count().ToString()
};
string skillsAllocated = "";
if (resources.Resource_Allocation.Where(x => x.resIdFK == resources.resId).Select(x => x).FirstOrDefault() == null)
{
skillsAllocated += "NIL";
}
else
{
foreach (var skills in resources.Resource_Allocation)
{
if (skillsAllocated != "")
{
skillsAllocated += ", " + skills.Skill.skillName;
}
else if (skillsAllocated == "")
{
skillsAllocated += skills.Skill.skillName;
}
}
}
rows.Add(skillsAllocated);
var checkQuantity = (from x in context.Resources
where x.resId == resources.resId
select x.remainingQuantity).FirstOrDefault();
if (checkQuantity == 0)
{
rows.Add("Not Available");
}
else if (checkQuantity <= 5 && checkQuantity >= 1)
{
rows.Add("Low Stock");
}
else
{
rows.Add("Sufficient");
}
rows.Add(resources.resId.ToString());
dataGridView1.Rows.Add(rows.ToArray());
}
}
//Loads DGV when Skill filter is selected
else if (skilBox.SelectedItem != null && skilBox.SelectedItem.ToString() != "No Filter")
{
var getSkillID = (from x in context.Skills
where x.skillName == skilBox.SelectedItem.ToString()
select x.skillId).First();
var getResources = (from x in context.Resources
join y in context.Resource_Allocation on x.resId equals y.resIdFK
where y.skillIdFK == getSkillID
orderby x.remainingQuantity descending
select x).ToList();
foreach (var resources in getResources)
{
List <string> rows = new List <string>()
{
resources.resName, resources.Resource_Type.resTypeName, resources.Resource_Allocation.Count().ToString()
};
string skillsAllocated = "";
if (resources.Resource_Allocation.Where(x => x.resIdFK == resources.resId).Select(x => x).FirstOrDefault() == null)
{
skillsAllocated += "NIL";
}
else
{
foreach (var skills in resources.Resource_Allocation)
{
if (skillsAllocated != "")
{
skillsAllocated += ", " + skills.Skill.skillName;
}
else if (skillsAllocated == "")
{
skillsAllocated += skills.Skill.skillName;
}
}
}
rows.Add(skillsAllocated);
var checkQuantity = (from x in context.Resources
where x.resId == resources.resId
select x.remainingQuantity).FirstOrDefault();
if (checkQuantity == 0)
{
rows.Add("Not Available");
}
else if (checkQuantity <= 5 && checkQuantity >= 1)
{
rows.Add("Low Stock");
}
else
{
rows.Add("Sufficient");
}
rows.Add(resources.resId.ToString());
dataGridView1.Rows.Add(rows.ToArray());
}
}
//Loads DGV when Type of Resource and Skill filters are selected
else
{
var getResources = (from x in context.Resources
orderby x.remainingQuantity descending
select x).ToList();
foreach (var resources in getResources)
{
List <string> rows = new List <string>()
{
resources.resName, resources.Resource_Type.resTypeName, resources.Resource_Allocation.Count().ToString()
};
string skillsAllocated = "";
if (resources.Resource_Allocation.Where(x => x.resIdFK == resources.resId).Select(x => x).FirstOrDefault() == null)
{
skillsAllocated += "NIL";
}
else
{
foreach (var skills in resources.Resource_Allocation)
{
if (skillsAllocated != "")
{
skillsAllocated += ", " + skills.Skill.skillName;
}
else if (skillsAllocated == "")
{
skillsAllocated += skills.Skill.skillName;
}
}
}
rows.Add(skillsAllocated);
var checkQuantity = (from x in context.Resources
where x.resId == resources.resId
select x.remainingQuantity).FirstOrDefault();
if (checkQuantity == 0)
{
rows.Add("Not Available");
}
else if (checkQuantity <= 5 && checkQuantity >= 1)
{
rows.Add("Low Stock");
}
else
{
rows.Add("Sufficient");
}
rows.Add(resources.resId.ToString());
dataGridView1.Rows.Add(rows.ToArray());
}
}
}
//Runs through every row to check for "Not Available", then color those row pertaining that value with a red background
foreach (DataGridViewRow rows in dataGridView1.Rows)
{
if (rows.Cells[4].Value.ToString() == "Not Available")
{
rows.DefaultCellStyle.BackColor = Color.Red;
}
}
}
private void Update_Load(object sender, EventArgs e)
{
using (var context = new Session1Entities())
{
#region Populating Allocation Checklist Box
var getSkills = (from x in context.Skills
select x.skillName);
var skills = new HashSet <string>();
foreach (var item in getSkills)
{
skills.Add(item);
}
clbAllocation.Items.AddRange(skills.ToArray());
#endregion
#region Initialising label text for selected resources
var getResourceName = (from x in context.Resources
where x.resId == _resID
select x.resName).First();
lblResourceName.Text = getResourceName;
var getResourceType = (from x in context.Resources
where x.resId == _resID
select x.Resource_Type.resTypeName).First();
lblResourceType.Text = getResourceType;
var getResourceAmount = (from x in context.Resources
where x.resId == _resID
select x.remainingQuantity).First();
txtQuantityBox.Text = getResourceAmount.ToString();
#endregion
#region Auto check skills that were previously allocated to for selected resource
var checkAllocatedSkills = (from x in context.Resource_Allocation
where x.resIdFK == _resID
select x).FirstOrDefault();
if (checkAllocatedSkills != null)
{
var getAllocatedSkills = (from x in context.Resource_Allocation
where x.resIdFK == _resID
select x.Skill.skillName);
foreach (var item in getAllocatedSkills)
{
_list.Add(item);
clbAllocation.SetItemChecked(clbAllocation.Items.IndexOf(item), true);
}
var getAllocatedSkillsToDelete = (from x in context.Resource_Allocation
where x.resIdFK == _resID
select x).ToList();
foreach (var item in getAllocatedSkillsToDelete)
{
context.Resource_Allocation.Remove(item);
context.SaveChanges();
}
}
#endregion
}
}
DataTable CDT(List <Resource> resources)
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("Type");
dt.Columns.Add("No of Skills");
dt.Columns.Add("Allocated Skill(s)");
dt.Columns.Add("Available Quantity");
using (var db = new Session1Entities())
{
foreach (var item in resources)
{
DataRow dr = dt.NewRow();
var vals = item.resId;
var val = db.Resources.Where(x => x.resId == vals).FirstOrDefault();
dr["ID"] = val.resId;
dr["Name"] = val.resName;
dr["Type"] = val.Resource_Type.resTypeName;
if (val.remainingQuantity > 5)
{
dr["Available Quantity"] = "Sufficiet";
}
else if (val.remainingQuantity >= 1 && val.remainingQuantity <= 5)
{
dr["Available Quantity"] = "Low Stock";
}
if (val.remainingQuantity == 0)
{
dr["Available Quantity"] = "Not Available";
}
int count = 0;
string skills = "Nil";
var q = db.Resource_Allocation.Where(x => x.resIdFK == vals);
if (q.FirstOrDefault() != null)
{
foreach (var items in q.ToList())
{
if (skills == "Nil")
{
skills = items.Skill.skillName;
count += 1;
}
else
{
skills += ", " + items.Skill.skillName.ToString();
count += 1;
}
}
}
dr["Allocated Skill(s)"] = skills;
dr["No of Skills"] = count;
dt.Rows.Add(dr);
}
return(dt);
}
}