public static bool getLeader(string name, out TourLeader leader)
{
bool exist = false;
leader = null; //initial
string connectionString = "Server=LAPTOP-4CBSFNAO;" + "Database=FSTA;" + "Integrated Security=true";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
string cmdtext = @"SELECT * FROM TourLeader
LEFT JOIN FullTimeLead ON TourLeader.Id = FullTimeLead.LeadId
LEFT JOIN PartTimeLead ON TourLeader.Id = PartTimeLead.LeadId
WHERE TourLeader.Name = '" + name + "'";
SqlCommand cmd = new SqlCommand(cmdtext, conn);
SqlDataReader sdr = cmd.ExecuteReader();
//Definitely only returns one tour leader, assuming leader name is unique
if (!sdr.HasRows)
{
return(exist);
}
else
{
while (sdr.Read())
{
if ((string)sdr["Role"] == "PT")
{
PartTimeLead partTimeLead = new PartTimeLead();
partTimeLead.Id = (int)sdr["Id"];
partTimeLead.Name = (string)sdr["Name"];
partTimeLead.Email = (string)sdr["Email"];
partTimeLead.Phone = (int)sdr["Phone"];
partTimeLead.Role = (string)sdr["Role"];
partTimeLead.Salary = (double)sdr["Salary"];
leader = partTimeLead;
}
else
{
FullTimeLead fullTimeLead = new FullTimeLead();
fullTimeLead.Id = (int)sdr["Id"];
fullTimeLead.Name = (string)sdr["Name"];
fullTimeLead.Email = (string)sdr["Email"];
fullTimeLead.Phone = (int)sdr["Phone"];
fullTimeLead.Role = (string)sdr["Role"];
fullTimeLead.Ranking = (string)sdr["Ranking"];
leader = fullTimeLead;
}
}
}
}
exist = true;
return(exist);
}
public static List <TourLeader> getAvailableLeaders(string DesCode)
{
List <TourLeader> leaders = new List <TourLeader>();
string connectionString = "Server=LAPTOP-4CBSFNAO;" + "Database=FSTA;" + "Integrated Security=true";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
string cmdtext = @"SELECT * FROM TourLeader
FULL OUTER JOIN FullTimeLead ON TourLeader.Id = FullTimeLead.LeadId
FULL OUTER JOIN PartTimeLead ON TourLeader.Id = PartTimeLead.LeadId
FULL OUTER JOIN DestinationOpted ON PartTimeLead.LeadId = DestinationOpted.PartTimeId
WHERE ROLE = 'FT' OR Destination = '" + DesCode + "'";
SqlCommand cmd = new SqlCommand(cmdtext, conn);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
if ((string)sdr["Role"] == "PT")
{
PartTimeLead partTimeLead = new PartTimeLead();
partTimeLead.Id = (int)sdr["Id"];
partTimeLead.LeadId = (int)sdr["Id"];
partTimeLead.Name = (string)sdr["Name"];
partTimeLead.Email = (string)sdr["Email"];
partTimeLead.Phone = (int)sdr["Phone"];
partTimeLead.Role = (string)sdr["Role"];
partTimeLead.Salary = (double)sdr["Salary"];
leaders.Add(partTimeLead);
}
else
{
FullTimeLead fullTimeLead = new FullTimeLead();
fullTimeLead.Id = (int)sdr["Id"];
fullTimeLead.LeadId = (int)sdr["Id"];
fullTimeLead.Name = (string)sdr["Name"];
fullTimeLead.Email = (string)sdr["Email"];
fullTimeLead.Phone = (int)sdr["Phone"];
fullTimeLead.Role = (string)sdr["Role"];
fullTimeLead.Ranking = (string)sdr["Ranking"];
leaders.Add(fullTimeLead);
}
}
}
return(leaders);
}
