// generate publication count group by Year, the years scope would be from 'from' to 'to'
public static int[] fetchPublicationCounts(DateTime from, DateTime to)
{
MySqlConnection conn = GetConnection();
return(PublicationAdapter.fetchPublicationCounts(conn, from, to));
}
// return publication detailed information accoring to the publication id
public Publication completePublicationDetails(Publication p)
{
MySqlConnection conn = GetConnection();
return(PublicationAdapter.completePublicationDetails(conn, p));
}
// return publication list according to the researcher data (id)
public static List <Publication> fetchBasicPublicationDetails(Researcher r)
{
MySqlConnection conn = GetConnection();
return(PublicationAdapter.fetchBasicPublicationDetails(conn, r));
}
// generate the publication count group by year
public static List <PublicationYearCount> genPublicationYearData(Researcher r)
{
MySqlConnection conn = GetConnection();
return(PublicationAdapter.genPublicationYearData(conn, r));
}
// return the year list of the researcher's publication
public static List <int> fetchPublicationYearList(Researcher r)
{
MySqlConnection conn = GetConnection();
return(PublicationAdapter.fetchPublicationYearList(conn, r));
}
// by using the researcher id, get the researcher details
public static Researcher fetchFullResearcherDetails(MySqlConnection conn, int id)
{
Researcher researcher = new Researcher();
Student rStudent = new Student();
Staff rStaff = new Staff();
String type = "";
MySqlDataReader rdr = null;
//MySqlDataReader rdr2 = null;
try
{
conn.Open();
// This part will generate the supersision count of the researcher
MySqlCommand cmd2 = new MySqlCommand("select count(*) as supervisioncount " +
"from researcher " +
"where supervisor_id=?supervisor_id", conn);
cmd2.Parameters.AddWithValue("supervisor_id", id);
rdr = cmd2.ExecuteReader();
while (rdr.Read())
{
researcher.Supervisions = rdr.GetInt32(0);
}
rdr.Close();
// This part will generate the researcher detailed information
MySqlCommand cmd = new MySqlCommand("select given_name, family_name, title, unit, campus, email, photo,level, current_start, utas_start, " +
"timestampdiff(day, DATE_FORMAT(utas_start,'%Y-%m-%d'),DATE_FORMAT(SYSDATE(), '%Y-%m-%d')) as days, " +
"type, IFNULL(degree, '') " +
"from researcher " +
"where id=?id", conn);
cmd.Parameters.AddWithValue("id", id);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
researcher.GivenName = rdr.GetString(0);
researcher.FamilyName = rdr.GetString(1);
researcher.Title = rdr.GetString(2);
researcher.Unit = rdr.GetString(3);
researcher.Campus = rdr.GetString(4);
researcher.Email = rdr.GetString(5);
researcher.Photo = rdr.GetString(6);
//Check whether 'Level' is null
if (!rdr.IsDBNull(7))
{
researcher.Level = rdr.GetString(7);
}
else
{
researcher.Level = "";
}
researcher.CurrentJobStart = rdr.GetDateTime(8);
researcher.EarliestStart = rdr.GetDateTime(9);
float years = (float)rdr.GetInt32(10) / 365;
int dayInt = (int)(years * 100);
years = (float)dayInt / 100;
researcher.Tenure = years;
type = rdr.GetString(11);
// if the researche type equals 'Student', generate student object
if (type == "Student")
{
rStudent.GivenName = researcher.GivenName;
rStudent.FamilyName = researcher.FamilyName;
rStudent.Title = researcher.Title;
rStudent.Unit = researcher.Unit;
rStudent.Campus = researcher.Campus;
rStudent.Email = researcher.Email;
rStudent.Photo = researcher.Photo;
rStudent.Level = researcher.Level;
rStudent.CurrentJobStart = researcher.CurrentJobStart;
rStudent.EarliestStart = researcher.EarliestStart;
rStudent.Tenure = researcher.Tenure;
rStudent.Supervisions = researcher.Supervisions;
rStudent.Degree = rdr.GetString(12);
}
// if the researche type equals 'Student', generate staff object
else
{
rStaff.GivenName = researcher.GivenName;
rStaff.FamilyName = researcher.FamilyName;
rStaff.Title = researcher.Title;
rStaff.Unit = researcher.Unit;
rStaff.Campus = researcher.Campus;
rStaff.Email = researcher.Email;
rStaff.Photo = researcher.Photo;
rStaff.Level = researcher.Level;
rStaff.CurrentJobStart = researcher.CurrentJobStart;
rStaff.EarliestStart = researcher.EarliestStart;
rStaff.Tenure = researcher.Tenure;
rStaff.Supervisions = researcher.Supervisions;
}
}
}
catch (MySqlException e)
{
ERDAdapter.ReportError("loading training sessions", e);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
// return student object
if (type == "Student")
{
//get the publication count
rStudent.PublicationsCount = PublicationAdapter.fetchPublicationCounts(conn, id);
return(rStudent);
}
// return staff object
else
{
//get the publication count
rStaff.PublicationsCount = PublicationAdapter.fetchPublicationCounts(conn, id);
rStaff.ThreeYearAverage = calThreeYearAveragePublications(conn, id);
// according to the level of staff, calculate the performance data
switch (rStaff.Level)
{
case "A": rStaff.Performance = (float)(rStaff.ThreeYearAverage / 0.5);
break;
case "B":
rStaff.Performance = rStaff.ThreeYearAverage;
break;
case "C":
rStaff.Performance = (float)(rStaff.ThreeYearAverage / 2);
break;
case "D":
rStaff.Performance = (float)(rStaff.ThreeYearAverage / 3.2);
break;
case "E":
rStaff.Performance = (float)(rStaff.ThreeYearAverage / 4);
break;
}
return(rStaff);
}
}