//Process index change in drop-down list
protected void DropDownListUserFilter_SelectedIndexChanged(object sender, EventArgs e)
{
TechInventoryDataContext db = new TechInventoryDataContext();
string strFilter = DropDownListUserFilter.SelectedValue;
if (strFilter == "DeptID")
{
var query = from dept in db.Depts
select new
{
DeptName = dept.DeptName,
DeptID = dept.DeptID
};
FillDDL(this, query, strFilter);
}
else if (strFilter == "AreaID")
{
var query = from area in db.Areas
select new
{
AreaName = area.AreaName,
AreaID = area.AreaID
};
FillDDL(this, query, strFilter);
}
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
//Add filter by user, type, room
//Add columns: Check date, uvuinvid, otherid, MakeModel
TechInventoryDataContext db = new TechInventoryDataContext();
int index = Convert.ToInt32(e.CommandArgument);
GridViewRow row = GridView1.Rows[index];
string uvuInvID = Convert.ToString(row.Cells[1].Text);
ButtonField b = new ButtonField();
DateTime now = DateTime.Now;
string date = now.ToShortDateString();
var query = (from equip in db.Equipments
where equip.UVUInvID == uvuInvID
select equip).Single();
query.InvCheck = now;
string test = "test";
try
{
// Save the changes.
db.SubmitChanges();
}
// Detect concurrency conflicts.
catch (InRowChangingEventException)
{
// Resolve conflicts.
//db.ChangeConflicts.ResolveAll();
}
GridView1.DataBind();
}
//Process index change in drop-down list
protected void DropDownListEquipFilter_SelectedIndexChanged(object sender, EventArgs e)
{
//Invoke new SQL connection
TechInventoryDataContext db = new TechInventoryDataContext();
string strFilter = DropDownListEquipFilter.SelectedValue;
string strSort = DropDownListSort.SelectedValue;
if (strFilter == "DeptID")
{
var query = from dept in db.Depts
select new
{
DeptName = dept.DeptName,
DeptID = dept.DeptID
};
FillDDL(this, query, strFilter);
}
else if (strFilter == "ModelID")
{
var query = from mod in db.Models
select new
{
ModelName = mod.ModelName,
ModelID = mod.ModelID
};
FillDDL(this, query, strFilter);
}
else if (strFilter == "UserUVID")
{
var query = from usr in db.Users
select new
{
UserName = usr.UserLName + ", " + usr.UserFName,
UserUVID = usr.UserUVID
};
FillDDL(this, query, strFilter);
}
else if (strFilter == "Room")
{
var query = from rm in db.Equipments
group rm by rm.Room into g
select new
{
Room = g.Key
};
FillDDL(this, query, strFilter);
}
else if (strFilter == "EquipTypeID")
{
var query = from etype in db.EquipTypes
select new
{
EquipTypeName = etype.EquipTypeName,
EquipTypeID = etype.EquipTypeID
};
FillDDL(this, query, strFilter);
}
}
//Generate queries based on values in the drop-down lists and send to gridview data source
private void ReturnResults(object sender)
{
//Invoke new SQL connection
TechInventoryDataContext db = new TechInventoryDataContext();
string strVal1 = DropDownListDateRange.SelectedValue;
//Past Due Query
if (strVal1 == "PastDue")
{
DateTime expiryDate = DateTime.Now.AddYears(-3);
var queryGrid = from equip in db.Equipments
join mod in db.Models on equip.ModelID equals mod.ModelID
join mfg in db.Mfgs on mod.MfgID equals mfg.MfgID
join dept in db.Depts on equip.DeptID equals dept.DeptID
join etype in db.EquipTypes on equip.EquipTypeID equals etype.EquipTypeID
join area in db.Areas on equip.AreaID equals area.AreaID
join u in db.Users on equip.UserUVID equals u.UserUVID
where equip.UserPrimaryComp == true
where u.FullTime == true
where equip.PurchDate <= expiryDate
select new
{
AreaName = area.AreaName,
BldgID = equip.BldgID,
Department = equip.DeptID,
Room = equip.Room,
UVUInvID = equip.UVUInvID,
Date = equip.PurchDate,
Make = mfg.MfgName + " " + mod.ModelName,
Type = etype.EquipTypeName,
SerialNumber = equip.SerialNum,
Location = area.AreaName + " - " + equip.BldgID + " - " + dept.DeptName + " - " + equip.Room,
PrimaryUser = u.UserLName + ", " + u.UserFName,
IsPrimary = equip.UserPrimaryComp,
FullTime = u.FullTime
};
FillGrid(this, queryGrid);
};
//Equipment coming due in 3 months query
if (strVal1 == "3")
{
DateTime expiryDate = DateTime.Now.AddMonths(-33);
DateTime expiryLimit = DateTime.Now.AddMonths(-36);
var queryGrid = from equip in db.Equipments
join mod in db.Models on equip.ModelID equals mod.ModelID
join mfg in db.Mfgs on mod.MfgID equals mfg.MfgID
join dept in db.Depts on equip.DeptID equals dept.DeptID
join etype in db.EquipTypes on equip.EquipTypeID equals etype.EquipTypeID
join area in db.Areas on equip.AreaID equals area.AreaID
join u in db.Users on equip.UserUVID equals u.UserUVID
where equip.UserPrimaryComp == true
where u.FullTime == true
where (equip.PurchDate <= expiryDate) && (equip.PurchDate >= expiryLimit)
select new
{
AreaName = area.AreaName,
BldgID = equip.BldgID,
Department = equip.DeptID,
Room = equip.Room,
UVUInvID = equip.UVUInvID,
Date = equip.PurchDate,
Make = mfg.MfgName + " " + mod.ModelName,
Type = etype.EquipTypeName,
SerialNumber = equip.SerialNum,
Location = area.AreaName + " - " + equip.BldgID + " - " + dept.DeptName + " - " + equip.Room,
PrimaryUser = u.UserLName + ", " + u.UserFName,
};
FillGrid(this, queryGrid);
}
//Equipment coming due in 6 months query
if (strVal1 == "6")
{
DateTime expiryDate = DateTime.Now.AddMonths(-30);
DateTime expiryLimit = DateTime.Now.AddMonths(-36);
var queryGrid = from equip in db.Equipments
join mod in db.Models on equip.ModelID equals mod.ModelID
join mfg in db.Mfgs on mod.MfgID equals mfg.MfgID
join dept in db.Depts on equip.DeptID equals dept.DeptID
join etype in db.EquipTypes on equip.EquipTypeID equals etype.EquipTypeID
join area in db.Areas on equip.AreaID equals area.AreaID
join u in db.Users on equip.UserUVID equals u.UserUVID
where equip.UserPrimaryComp == true
where u.FullTime == true
where (equip.PurchDate <= expiryDate) && (equip.PurchDate >= expiryLimit)
select new
{
AreaName = area.AreaName,
BldgID = equip.BldgID,
Department = equip.DeptID,
Room = equip.Room,
UVUInvID = equip.UVUInvID,
Date = equip.PurchDate,
Make = mfg.MfgName + " " + mod.ModelName,
Type = etype.EquipTypeName,
SerialNumber = equip.SerialNum,
Location = area.AreaName + " - " + equip.BldgID + " - " + dept.DeptName + " - " + equip.Room,
PrimaryUser = u.UserLName + ", " + u.UserFName,
};
FillGrid(this, queryGrid);
}
//Equipment coming due in 12 months query
if (strVal1 == "12")
{
DateTime expiryDate = DateTime.Now.AddMonths(-24);
DateTime expiryLimit = DateTime.Now.AddMonths(-36);
var queryGrid = from equip in db.Equipments
join mod in db.Models on equip.ModelID equals mod.ModelID
join mfg in db.Mfgs on mod.MfgID equals mfg.MfgID
join dept in db.Depts on equip.DeptID equals dept.DeptID
join etype in db.EquipTypes on equip.EquipTypeID equals etype.EquipTypeID
join area in db.Areas on equip.AreaID equals area.AreaID
join u in db.Users on equip.UserUVID equals u.UserUVID
where equip.UserPrimaryComp == true
where u.FullTime == true
where (equip.PurchDate <= expiryDate) && (equip.PurchDate >= expiryLimit)
select new
{
AreaName = area.AreaName,
BldgID = equip.BldgID,
Department = equip.DeptID,
Room = equip.Room,
UVUInvID = equip.UVUInvID,
Date = equip.PurchDate,
Make = mfg.MfgName + " " + mod.ModelName,
Type = etype.EquipTypeName,
SerialNumber = equip.SerialNum,
Location = area.AreaName + " - " + equip.BldgID + " - " + dept.DeptName + " - " + equip.Room,
PrimaryUser = u.UserLName + ", " + u.UserFName,
};
FillGrid(this, queryGrid);
}
}
protected void BtnSubmit_Click(object sender, EventArgs e)
{
TechInventoryDataContext db = new TechInventoryDataContext();
ReturnResults(sender);
}
//Method for handling the button click event on the row.
//Gets the UVUInvID from the selected row and uses that as the filter for the SQL update query
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
string eventString = e.CommandName.ToString();
if (e.CommandName == "InventoryCheck")
{
//Instantiate new instance of SQL database connection
TechInventoryDataContext db = new TechInventoryDataContext();
//Convert command argument from button to string
string uvuInvID = Convert.ToString(e.CommandArgument);
ButtonField b = new ButtonField();
//Get current datetime value and convert to string
DateTime now = DateTime.Now;
string date = now.ToShortDateString();
//Linq query for a single UVUInventory record
var query = (from equip in db.Equipments
where equip.UVUInvID == uvuInvID
select equip).Single();
//Set the InvCheck value for the queried record to current datetime
query.InvCheck = now;
//Try/Catch to submit changes to the database
try
{
// Save the changes.
db.SubmitChanges();
}
// Detect concurrency conflicts.
catch (InRowChangingEventException)
{
// Resolve conflicts.
//db.ChangeConflicts.ResolveAll();
}
//Rebind data in gridview. Updated datetime value should be visible in grid.
GridView1.DataBind();
ViewState.Clear();
}
}
private void ReturnResults(object sender)
{
TechInventoryDataContext db = new TechInventoryDataContext();
string strVal1 = DropDownListUserFilter.SelectedValue;
string strVal2 = DropDownListUserValue.SelectedValue;
if (strVal1 == "All")
{
var queryGrid = from user in db.Users
join dept in db.Depts on user.DeptID equals dept.DeptID
join area in db.Areas on user.AreaID equals area.AreaID
orderby user.UserLName
select new
{
LastName = user.UserLName,
FirstName = user.UserFName,
PhoneExt = user.PhoneExt,
HomePhone = user.HomePhone,
CellPhone = user.CellPhone,
Birthday = user.Bday,
email = user.Email,
AreaName = area.AreaName,
Department = user.DeptID,
};
FillGrid(this, queryGrid);
};
if (strVal1 == "DeptID")
{
var queryGrid = from user in db.Users
join dept in db.Depts on user.DeptID equals dept.DeptID
join area in db.Areas on user.AreaID equals area.AreaID
where user.DeptID.ToString() == strVal2
orderby user.UserLName
select new
{
LastName = user.UserLName,
FirstName = user.UserFName,
PhoneExt = user.PhoneExt,
HomePhone = user.HomePhone,
CellPhone = user.CellPhone,
Birthday = user.Bday,
email = user.Email,
AreaName = area.AreaName,
Department = user.DeptID,
};
FillGrid(this, queryGrid);
}
if (strVal1 == "AreaID")
{
var queryGrid = from user in db.Users
join dept in db.Depts on user.DeptID equals dept.DeptID
join area in db.Areas on user.AreaID equals area.AreaID
where user.AreaID.ToString() == strVal2
orderby user.UserLName
select new
{
LastName = user.UserLName,
FirstName = user.UserFName,
PhoneExt = user.PhoneExt,
HomePhone = user.HomePhone,
CellPhone = user.CellPhone,
Birthday = user.Bday,
email = user.Email,
AreaName = area.AreaName,
Department = user.DeptID,
};
FillGrid(this, queryGrid);
}
}
private void ReturnResults(object sender)
{
TechInventoryDataContext db = new TechInventoryDataContext();
string strVal1 = DropDownListEquipFilter.SelectedValue;
string strVal2 = DropDownListEquipValue.SelectedValue;
if (strVal1 == "All")
{
var queryGrid = from equip in db.Equipments
join mod in db.Models on equip.ModelID equals mod.ModelID
join mfg in db.Mfgs on mod.MfgID equals mfg.MfgID
join dept in db.Depts on equip.DeptID equals dept.DeptID
join etype in db.EquipTypes on equip.EquipTypeID equals etype.EquipTypeID
join area in db.Areas on equip.AreaID equals area.AreaID
join u in db.Users on equip.UserUVID equals u.UserUVID
select new
{
AreaName = area.AreaName,
BldgID = equip.BldgID,
Department = equip.DeptID,
Room = equip.Room,
UVUInvID = equip.UVUInvID,
UserUVID = equip.UserUVID,
Type = etype.EquipTypeName,
Mfg = mfg.MfgName,
Model = mod.ModelName,
UserName = u.UserLName + ", " + u.UserFName,
PrimaryComputer = equip.UserPrimaryComp
};
FillGrid(this, queryGrid);
};
if (strVal1 == "DeptID")
{
var queryGrid = from equip in db.Equipments
join mod in db.Models on equip.ModelID equals mod.ModelID
join mfg in db.Mfgs on mod.MfgID equals mfg.MfgID
join dept in db.Depts on equip.DeptID equals dept.DeptID
join etype in db.EquipTypes on equip.EquipTypeID equals etype.EquipTypeID
join area in db.Areas on equip.AreaID equals area.AreaID
join u in db.Users on equip.UserUVID equals u.UserUVID
where equip.DeptID.ToString() == strVal2
select new
{
AreaName = area.AreaName,
BldgID = equip.BldgID,
Department = equip.DeptID,
Room = equip.Room,
UVUInvID = equip.UVUInvID,
UserUVID = equip.UserUVID,
Type = etype.EquipTypeName,
Mfg = mfg.MfgName,
Model = mod.ModelName,
UserName = u.UserLName + ", " + u.UserFName,
PrimaryComputer = equip.UserPrimaryComp
};
FillGrid(this, queryGrid);
}
if (strVal1 == "UserUVID")
{
var queryGrid = from equip in db.Equipments
join mod in db.Models on equip.ModelID equals mod.ModelID
join mfg in db.Mfgs on mod.MfgID equals mfg.MfgID
join dept in db.Depts on equip.DeptID equals dept.DeptID
join etype in db.EquipTypes on equip.EquipTypeID equals etype.EquipTypeID
join area in db.Areas on equip.AreaID equals area.AreaID
join u in db.Users on equip.UserUVID equals u.UserUVID
where equip.UserUVID.ToString() == strVal2
select new
{
AreaName = area.AreaName,
BldgID = equip.BldgID,
Department = equip.DeptID,
Room = equip.Room,
UVUInvID = equip.UVUInvID,
UserUVID = equip.UserUVID,
Type = etype.EquipTypeName,
Mfg = mfg.MfgName,
Model = mod.ModelName,
UserName = u.UserLName + ", " + u.UserFName,
PrimaryComputer = equip.UserPrimaryComp
};
FillGrid(this, queryGrid);
}
if (strVal1 == "ModelID")
{
var queryGrid = from equip in db.Equipments
join mod in db.Models on equip.ModelID equals mod.ModelID
join mfg in db.Mfgs on mod.MfgID equals mfg.MfgID
join dept in db.Depts on equip.DeptID equals dept.DeptID
join etype in db.EquipTypes on equip.EquipTypeID equals etype.EquipTypeID
join area in db.Areas on equip.AreaID equals area.AreaID
join u in db.Users on equip.UserUVID equals u.UserUVID
where equip.ModelID.ToString() == strVal2
select new
{
AreaName = area.AreaName,
BldgID = equip.BldgID,
Department = equip.DeptID,
Room = equip.Room,
UVUInvID = equip.UVUInvID,
UserUVID = equip.UserUVID,
Type = etype.EquipTypeName,
Mfg = mfg.MfgName,
Model = mod.ModelName,
UserName = u.UserLName + ", " + u.UserFName,
PrimaryComputer = equip.UserPrimaryComp
};
FillGrid(this, queryGrid);
}
if (strVal1 == "EquipTypeID")
{
var queryGrid = from equip in db.Equipments
join mod in db.Models on equip.ModelID equals mod.ModelID
join mfg in db.Mfgs on mod.MfgID equals mfg.MfgID
join dept in db.Depts on equip.DeptID equals dept.DeptID
join etype in db.EquipTypes on equip.EquipTypeID equals etype.EquipTypeID
join area in db.Areas on equip.AreaID equals area.AreaID
join u in db.Users on equip.UserUVID equals u.UserUVID
where equip.EquipTypeID.ToString() == strVal2
select new
{
AreaName = area.AreaName,
BldgID = equip.BldgID,
Department = equip.DeptID,
Room = equip.Room,
UVUInvID = equip.UVUInvID,
UserUVID = equip.UserUVID,
Type = etype.EquipTypeName,
Mfg = mfg.MfgName,
Model = mod.ModelName,
UserName = u.UserLName + ", " + u.UserFName,
PrimaryComputer = equip.UserPrimaryComp
};
FillGrid(this, queryGrid);
}
if (strVal1 == "Room")
{
var queryGrid = from equip in db.Equipments
join mod in db.Models on equip.ModelID equals mod.ModelID
join mfg in db.Mfgs on mod.MfgID equals mfg.MfgID
join dept in db.Depts on equip.DeptID equals dept.DeptID
join etype in db.EquipTypes on equip.EquipTypeID equals etype.EquipTypeID
join area in db.Areas on equip.AreaID equals area.AreaID
join u in db.Users on equip.UserUVID equals u.UserUVID
where equip.Room == strVal2
select new
{
AreaName = area.AreaName,
BldgID = equip.BldgID,
Department = equip.DeptID,
Room = equip.Room,
UVUInvID = equip.UVUInvID,
UserUVID = equip.UserUVID,
Type = etype.EquipTypeName,
Mfg = mfg.MfgName,
Model = mod.ModelName,
UserName = u.UserLName + ", " + u.UserFName,
PrimaryComputer = equip.UserPrimaryComp
};
FillGrid(this, queryGrid);
}
}
