public static bool InsertStaffInformation(staffinfo staf)
{
bool result = false;
SqlConnection myconn = null;
int rowsAffected = 0;
try
{
myconn = new SqlConnection();
SqlCommand comm = new SqlCommand();
myconn.ConnectionString = connectionString;
myconn.Open();
comm.Connection = myconn;
comm.CommandText = "insert into StaffInfo" +
"(Name,Designation,Section,Functions,UserID,Role) values" +
"(@name,@designation,@section,@funct,@uid,@role)";
comm.Parameters.AddWithValue("@name", staf.Name);
comm.Parameters.AddWithValue("@designation", staf.Designation);
comm.Parameters.AddWithValue("@section", staf.Section);
comm.Parameters.AddWithValue("@funct", staf.Function);
comm.Parameters.AddWithValue("@uid", staf.Uid);
comm.Parameters.AddWithValue("@role", staf.Role);
rowsAffected = comm.ExecuteNonQuery();
if (rowsAffected > 0)
{
result = true;
}
}
catch (SqlException)
{
}
finally
{
myconn.Close();
}
return result;
}
public static bool UpdateUserInformation(staffinfo stf)
{
bool updateresult = false;
int rowsAffected = 0;
SqlConnection myconn = null;
try
{
myconn = new SqlConnection();
SqlCommand comm = new SqlCommand();
myconn.ConnectionString = connectionString;
myconn.Open();
comm.Connection = myconn;
comm.CommandText = "Update StaffInfo set Section=@sect, Functions=@funct, Role=@role where UserID=@uid";
comm.Parameters.AddWithValue("@uid", stf.Uid);
comm.Parameters.AddWithValue("@sect", stf.Section);
comm.Parameters.AddWithValue("@funct", stf.Function);
comm.Parameters.AddWithValue("@role", stf.Role);
rowsAffected = comm.ExecuteNonQuery();
if (rowsAffected > 0)
{
updateresult = true;
}
}
catch
{
}
finally
{
myconn.Close();
}
return updateresult;
}
public static ArrayList GetAllUserIDViaLikeNameSection(string name, ArrayList sec)
{
ArrayList listofstaff = new ArrayList();
SqlConnection myconn = null;
staffinfo staff = null;
try
{
foreach (string sect in sec)
{
myconn = new SqlConnection();
SqlCommand comm = new SqlCommand();
myconn.ConnectionString = connectionString;
myconn.Open();
comm.Connection = myconn;
comm.CommandText = "select * from StaffInfo where Name LIKE '%" + name + "%' and Section=@sec";
comm.Parameters.AddWithValue("@sec", sect);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string staffname = dr["Name"].ToString();
string designation = dr["Designation"].ToString();
string section = dr["Section"].ToString();
string function = dr["Functions"].ToString();
string userid = dr["UserID"].ToString();
string role = dr["Role"].ToString();
staff = new staffinfo(staffname, designation, section, function, userid, role);
listofstaff.Add(staff);
}
dr.Close();
}
}
catch (SqlException)
{
return listofstaff;
}
finally
{
myconn.Close();
}
return listofstaff;
}
public static staffinfo GetStaffDetailsViaUid(string uid)
{
SqlConnection myconn = null;
staffinfo staff = null;
try
{
myconn = new SqlConnection();
SqlCommand comm = new SqlCommand();
myconn.ConnectionString = connectionString;
myconn.Open();
comm.Connection = myconn;
comm.CommandText = "select * from StaffInfo where UserID=@uid";
comm.Parameters.AddWithValue("@uid", uid);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string staffname = dr["Name"].ToString();
string designation = dr["Designation"].ToString();
string section = dr["Section"].ToString();
string function = dr["Functions"].ToString();
string userid = dr["UserID"].ToString();
string role = dr["Role"].ToString();
staff = new staffinfo(staffname, designation, section, function, userid, role);
}
dr.Close();
}
catch (SqlException)
{
return staff;
}
finally
{
myconn.Close();
}
return staff;
}
public static ArrayList GetAllStaffDetailsByFunctionSection(string function, string section)
{
SqlConnection myconn = null;
ArrayList listofStaff = new ArrayList();
try
{
myconn = new SqlConnection();
SqlCommand comm = new SqlCommand();
myconn.ConnectionString = connectionString;
myconn.Open();
comm.Connection = myconn;
comm.CommandText = "select * from StaffInfo where Section = @section and Functions = @function;";
comm.Parameters.AddWithValue("@function", function);
comm.Parameters.AddWithValue("@section", section);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
//string staffID = dr["UserID"].ToString();
////Question q2 = new Question(qID, question, include);
//listofStaff.Add(staffID);
string staffname = dr["Name"].ToString();
string designation = dr["Designation"].ToString();
string sect = dr["Section"].ToString();
string funct = dr["Functions"].ToString();
string uid = dr["UserID"].ToString();
string role = dr["Role"].ToString();
staffinfo staff = new staffinfo(staffname, designation, sect, funct, uid, role);
listofStaff.Add(staff);
}
dr.Close();
}
catch (SqlException)
{
return listofStaff;
}
finally
{
myconn.Close();
}
return listofStaff;
}
//Manage User Module
public static ArrayList GetAllStaffDetails()
{
SqlConnection myconn = null;
staffinfo staff = null;
ArrayList listofstaff = new ArrayList();
try
{
myconn = new SqlConnection();
SqlCommand comm = new SqlCommand();
myconn.ConnectionString = connectionString;
myconn.Open();
comm.Connection = myconn;
comm.CommandText = "select * from StaffInfo order by Name";
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string staffname = dr["Name"].ToString();
string designation = dr["Designation"].ToString();
string section = dr["Section"].ToString();
string function = dr["Functions"].ToString();
string uid = dr["UserID"].ToString();
string role = dr["Role"].ToString();
staff = new staffinfo(staffname, designation, section, function, uid, role);
listofstaff.Add(staff);
}
dr.Close();
}
catch (SqlException)
{
return listofstaff;
}
finally
{
myconn.Close();
}
return listofstaff;
}
protected void UpdateBtn_Click(object sender, EventArgs e)
{
try
{
#region update fields
try
{
bool chkresult = true;
int count = 1;
string sect = "";
ArrayList listofSectionItem = new ArrayList();
for (int i = 0; i < listSection.Items.Count; i++)
{
if (listSection.Items[i].Selected == true)
{
listofSectionItem.Add(listSection.Items[i].Text);
}
}
foreach (string s in listofSectionItem)
{
if (count == listofSectionItem.Count)
{
sect += s;
}
else
{
sect += s + ",";
}
count++;
}
if (listofSectionItem.Count == 0)
{
MessageBoxShowWithOutRedirect("Please select at least one section.");
chkresult = false;
}
if (chkresult == true)
{
if (sect.Contains("ALL"))
{
sect = "ALL";
}
string name = lblStaffName.Text;
string designation = tbxStaffDesignation.Text;
string function = ddlFunction.Text;
string role = ddlRole.Text;
string uid = lblUid.Text;
staffinfo stf = new staffinfo(name, designation, sect, function, uid, role);
bool result = dbmanager.UpdateUserInformation(stf);
{
if (result == true)
{
MessageBoxShow("Updated successfully.");
}
else
{
MessageBoxShow("Fail to update.");
}
}
}
}
catch (Exception ex)
{
MessageBoxShow(ex.Message);
}
#endregion
}
catch (Exception ex)
{
MessageBoxShow(ex.Message);
}
}
protected void NextBtn_Click(object sender, EventArgs e)
{
ArrayList listofSectionItems = new ArrayList();
bool chkresult = true;
string name = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(NameTbx.Text.Trim());
string uid = UserIdTbx.Text.ToLower().Trim();
string sect = "";
int count = 1;
foreach (ListItem listItem in listSection.Items)
{
if (listItem.Selected == true)
{
listofSectionItems.Add(listItem.Text);
}
}
foreach (string s in listofSectionItems)
{
if (count == listofSectionItems.Count)
{
sect += s;
}
else
{
sect += s + ",";
}
count++;
}
if (chkresult == true)
{
lblValidatorSection.Text = "";
string[] design = designationTbx.Text.Trim().Split('/');
string designation = "";
if (design.LongLength > 0)
{
int counter = 0;
foreach (string de in design)
{
if (de != "")
{
if (counter > 0)
{
designation += "/" + CultureInfo.CurrentCulture.TextInfo.ToTitleCase(de.Trim());
}
else
{
designation += CultureInfo.CurrentCulture.TextInfo.ToTitleCase(de.Trim());
}
counter++;
}
}
}
else
{
designation = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(designationTbx.Text.Trim());
}
string funct = ddlFunction.Text;
string role = ddlRole.Text;
staffinfo stf = null;
if (name != "" && uid != "" && sect != "")
{
bool chk = dbmanager.CheckUserIDExist(uid);
if (sect.Contains("ALL"))
{
sect = "ALL";
}
if (chk == false)
{
stf = new staffinfo(name, designation, sect, funct, uid, role);
Session["Staff"] = stf;
mainView.ActiveViewIndex = 1;
InformationLbl.Text = "Please preview what you have selected. If the information is incorrect, click 'Back' to re-select.";
string summary = "";
summary += "<table><tr><td><b>New system user information<b></td></tr></table>";
summary += "<table>";
summary += "<tr><td>Name:</td><td>" + name + "</td></tr>";
summary += "<tr><td>Designation:</td><td>" + designation + "</td></tr>";
summary += "<tr><td>Section:</td><td>" + sect.Replace(';', ',') + "</td></tr>";
summary += "<tr><td>Function:</td><td>" + funct + "</td></tr>";
summary += "<tr><td>User ID:</td><td>" + uid + "</td></tr>";
summary += "<tr><td>Role:</td><td>" + role + "</td></tr>";
summary += "</table>";
SummaryLbl.Text = summary;
}
else
{
lblValidatorUserId.ForeColor = System.Drawing.Color.Red;
lblValidatorUserId.Text = "Id already exist. Please enter another id.";
}
}
}
}
private ArrayList ReadListOfDataFromCSVStaffInfo(DataTable listofStaffInformation)
{
ArrayList listofStaff = new ArrayList();
try
{
int emptyvalue = 0;
int invalidrelation = 0;
int invaliddataformat = 0;
StringBuilder SqlQuery = new StringBuilder();
SqlQuery.Append("");
if (listofStaffInformation.Rows.Count != 0)
{
foreach (DataRow dr in listofStaffInformation.Rows)
{
if (dr["UserID"].ToString() != "")
{
string designationstring = "";
bool checksec = false;
string staffname = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(dr["Name"].ToString().Trim());
string designation = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(dr["Designation"].ToString().Trim());
string[] designarray = designation.Split('/');
if (designarray.LongLength > 0)
{
int counter = 0;
foreach (string de in designarray)
{
if (de != "")
{
if (counter > 0)
{
designationstring += "/" + CultureInfo.CurrentCulture.TextInfo.ToTitleCase(de.Trim());
}
else
{
designationstring += CultureInfo.CurrentCulture.TextInfo.ToTitleCase(de.Trim());
}
counter++;
}
}
}
else
{
designationstring = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(designation);
}
string section = dr["Section"].ToString().ToUpper().Trim();
string sectionstring = "";
string[] secinfo = section.Split(',');
string[] secarray = RemoveDuplicates(secinfo);
int countstring = 0;
foreach (string se in secarray)
{
if (se != "")
{
checksec = dbmanager.CheckSectionExist(se.Trim());
if (checksec == true)
{
if (countstring > 0)
{
sectionstring += "," + se.Trim();
}
else
{
sectionstring += se.Trim();
}
countstring++;
}
if (checksec == false)
{
break;
}
}
}
string function = dr["Functions"].ToString().Trim();
bool checkfunc = dbmanager.CheckFunctionExist(function);
string staffuid = dr["UserID"].ToString().ToLower().Trim();
string role = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(dr["Role"].ToString().Trim());
bool checkrole = dbmanager.CheckRoleExist(role);
if (staffname != "" && staffuid != "" && checksec == true && checkfunc == true && checkrole == true)
{
if (sectionstring.Contains("ALL"))
{
sectionstring = "ALL";
}
staffinfo staffinfo = new staffinfo(staffname, designationstring, sectionstring, function, staffuid, role);
listofStaff.Add(staffinfo);
}
else if (checksec == false || checkfunc == false || checkrole == false)
{
invalidrelation++;
}
if (staffname.Length > 100 || staffuid.Length > 30)
{
invaliddataformat++;
}
}
else
{
emptyvalue++;
}
}
if (invalidrelation > 0)
{
listofStaff.Clear();
MessageBoxShow(invalidrelation + " record(s) found with invalid data. might need relation data from other tables.");
}
if (invaliddataformat > 0)
{
listofStaff.Clear();
MessageBoxShow(invaliddataformat + " record(s) found with invalid data format.");
}
if (emptyvalue > 0)
{
listofStaff.Clear();
MessageBoxShow(emptyvalue + " record(s) found with empty data field.");
}
}
else
{
StaffErrorMsgLbl.ForeColor = System.Drawing.Color.Red;
StaffErrorMsgLbl.Text = "Fail to read file.";
}
}
catch
{
StaffErrorMsgLbl.ForeColor = System.Drawing.Color.Red;
StaffErrorMsgLbl.Text = "Invalid data format found.";
}
return listofStaff;
}