public static ArrayList GetAllSavedAppraisal(string userid, int page)
{
SqlConnection myconn = null;
staffappraisal stf = 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 SaveAppraisal sv, StaffInfo s where sv.UserID=s.UserID and sv.userid=@uid and AppraisalQuestionID=@page and AppraisalDate >= (select StartDate from SystemDate) and AppraisalDate <= (select EndDate from SystemDate) order by s.Name, AppraisalQuestionid";
comm.CommandText = "select * from SaveAppraisal sv where sv.UserID=@uid and sv.userid=@uid and AppraisalQuestionID=@page and AppraisalDate >= (select StartDate from SystemDate) and AppraisalDate <= (select EndDate from SystemDate)";
comm.Parameters.AddWithValue("@uid", userid);
comm.Parameters.AddWithValue("@page", page);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string uid = dr["UserID"].ToString();
int questionid = Convert.ToInt32(dr["AppraisalQuestionID"]);
string appstaff = dr["AppraisalStaffUserID"].ToString();
double appresult = Convert.ToDouble(dr["AppraisalResult"]);
string appremarks = dr["AppraisalRemarks"].ToString();
DateTime date = (DateTime)dr["AppraisalDate"];
DateTime systemend = (DateTime)dr["SystemEndDate"];
stf = new staffappraisal(uid, questionid, appstaff, appresult, appremarks, date, systemend);
listofstaff.Add(stf);
}
dr.Close();
}
catch (SqlException)
{
return listofstaff;
}
finally
{
myconn.Close();
}
return listofstaff;
}
/*public static int CheckStaffHasCompletedAppraisal(string uid, DateTime endDate)
{
SqlConnection myconn = null;
int count = 0;
try
{
myconn = new SqlConnection();
SqlCommand comm = new SqlCommand();
myconn.ConnectionString = connectionString;
myconn.Open();
comm.Connection = myconn;
comm.CommandText = "select COUNT(AppraisalStaffUserID) as TOTAL from StaffAppraisal where UserID=@uid AND SystemEndDate =@SystemEndDate";
comm.Parameters.AddWithValue("@UserID", uid);
comm.Parameters.AddWithValue("@SystemEndDate", endDate);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
count = Convert.ToInt32(dr["TOTAL"]);
}
dr.Close();
}
catch (SqlException)
{
return count;
}
finally
{
myconn.Close();
}
return count;
} */
public static ArrayList GetDistinctNameUidCompletedAppraisal(string function)
{
SqlConnection myconn = null;
staffappraisal stfapp = null;
ArrayList listofCompleted = new ArrayList();
try
{
myconn = new SqlConnection();
SqlCommand comm = new SqlCommand();
myconn.ConnectionString = connectionString;
myconn.Open();
comm.Connection = myconn;
comm.CommandText = "select UserID,Functions from StaffInfo where Functions=@funct and UserID NOT IN (select UserID from StaffAppraisal where AppraisalDate >= (select StartDate from SystemDate) and AppraisalDate <= (select EndDate from SystemDate)) order by Name";
comm.Parameters.AddWithValue("@funct", function);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string uid = dr["UserID"].ToString();
string funct = dr["Functions"].ToString();
stfapp = new staffappraisal(uid, 0, funct, 0.0, "", DateTime.Today, DateTime.Today);
listofCompleted.Add(stfapp);
}
dr.Close();
}
catch (SqlException)
{
return listofCompleted;
}
finally
{
myconn.Close();
}
return listofCompleted;
}
protected bool SavedAppraisalValues()
{
bool result = false;
try
{
int qid = SubmitAppraisalGrid.PageIndex + 1;
ArrayList saveAppraisal = new ArrayList();
string uid = Session["UserID"].ToString();
DateTime systemend = (DateTime)Session["EndTime"];
DateTime todaydate = DateTime.Today;
for (int i = 0; i < SubmitAppraisalGrid.Rows.Count; i++)
{
GridView gve = (GridView)SubmitAppraisalGrid.Rows[i].FindControl("StaffAppraisalGrid"); // for grid
if (gve != null)
{
for (int j = 0; j < gve.Rows.Count; j++)
{
RadioButtonList bblist = (RadioButtonList)gve.Rows[j].Cells[2].FindControl("RadioList");
TextBox tbx = (TextBox)gve.Rows[j].Cells[3].FindControl("RemarksTbx");
ArrayList staffList = (ArrayList)Session["AllStaff"];
string staff = staffList[j].ToString();
string rate = bblist.SelectedValue;
double rate2;
if (rate.Equals("N/A"))
{
rate2 = 1.1;
}
else
{
rate2 = Convert.ToDouble(rate);
}
string remarks = tbx.Text;
staffappraisal stfapp = new staffappraisal(uid, qid, staff, Convert.ToDouble(rate2), remarks, todaydate, systemend);
saveAppraisal.Add(stfapp);
}
}
int count = 0;
}
//check if there is exist and save in database
bool exist = dbmanager.CheckAppraisalSave(uid, qid, systemend);
if (exist == true)
{
dbmanager.DeleteAppraisalSaved(uid, qid, systemend);
}
dbmanager.InsertSavedData(saveAppraisal);
result = true;
}
catch
{
result = false;
}
return result;
}
public static staffappraisal GetAllRemarksViaUserNameNoFunction(string userid, string userappraisal, int qid, DateTime enddate)
{
SqlConnection myconn = null;
staffappraisal stf = null;
try
{
myconn = new SqlConnection();
SqlCommand comm = new SqlCommand();
myconn.ConnectionString = connectionString;
myconn.Open();
comm.Connection = myconn;
comm.CommandText = "select * from StaffAppraisal s,StaffInfo p where s.AppraisalStaffUserID=p.UserID and s.AppraisalQuestionID=@qid and s.AppraisalStaffUserID=@uid and s.UserID=@userappraisal and s.AppraisalRemarks!=''" +
" and SystemEndDate=@enddate";
comm.Parameters.AddWithValue("@uid", userid);
comm.Parameters.AddWithValue("@userappraisal", userappraisal);
comm.Parameters.AddWithValue("@qid", qid);
comm.Parameters.AddWithValue("@enddate", enddate);
SqlDataReader dr = comm.ExecuteReader();
if (dr.Read())
{
string uid = dr["UserID"].ToString();
int questionid = Convert.ToInt32(dr["AppraisalQuestionID"]);
string appstaff = dr["AppraisalStaffUserID"].ToString();
double appresult = Convert.ToDouble(dr["AppraisalResult"]);
string appremarks = dr["AppraisalRemarks"].ToString();
DateTime date = (DateTime)dr["AppraisalDate"];
DateTime systemend = (DateTime)dr["SystemEndDate"];
stf = new staffappraisal(uid, questionid, appstaff, appresult, appremarks, date, systemend);
}
dr.Close();
}
catch (SqlException)
{
return stf;
}
finally
{
myconn.Close();
}
return stf;
}
protected bool FindAppraisalValues()
{
int count = 0;
int index = 0;
bool result = false;
ArrayList list = FindValues(this.Page);
ArrayList listofqn = (ArrayList)Session["AllQuestion"];
ArrayList listofstf = (ArrayList)Session["AllStaff"];
ArrayList listofallappraisal = new ArrayList();
ArrayList listofunans = new ArrayList();
int countpage = 1;
foreach (Question qn in listofqn)
{
if (countpage == SubmitAppraisalGrid.PageIndex + 1)
{
for (int i = 0; i < list.Count; )
{
string rate = list[i].ToString();
int num = (i / 2) + 1;
if (rate == "0.0")
{
count++;
}
if (rate == "N/A")
{
rate = "1.1";
}
string remarks = list[i + 1].ToString().Trim();
string staff = listofstf[index].ToString();
string uid = Session["UserID"].ToString();
DateTime todaydate = DateTime.Today;
DateTime systemend = (DateTime)Session["EndTime"];
staffappraisal stfapp = new staffappraisal(uid, qn.QuestionID, staff, Convert.ToDouble(rate), remarks, todaydate, systemend);
if (Session["AllAppraisal"] != null)
{
ArrayList listtodelete = new ArrayList();
listofallappraisal = (ArrayList)Session["AllAppraisal"];
foreach (staffappraisal allstaf in listofallappraisal)
{
if (stfapp.Questionid == allstaf.Questionid)
{
if (stfapp.Appstaffuid == allstaf.Appstaffuid)
{
listtodelete.Add(allstaf);
//remove past data to prevent stack
}
}
}
//delete all past data
foreach (staffappraisal deletestf in listtodelete)
{
listofallappraisal.Remove(deletestf);
}
listofallappraisal.Add(stfapp);
}
else
{
listofallappraisal.Add(stfapp);
}
i++;
i++;
index++;
}
}
countpage++;
}
if (count == 0)
{
result = true;
Session["AllAppraisal"] = listofallappraisal;
ViewState["answers" + SubmitAppraisalGrid.PageIndex + 1] = list;
}
return result;
}