public static List <TourLeader> GetAvailableTourLeaders(int tid, int pid)
{
List <TourLeader> tl_list = new List <TourLeader>();
DBConnect db = new DBConnect();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"select * from TourLeaders order by Name";
DataTable tbl = db.GetData(cmd);
if (tbl != null)
{
foreach (DataRow r in tbl.Rows)
{
TourLeader tl = new TourLeader();
tl.TourleaderId = Convert.ToInt32(r[0].ToString());
tl.Name = Convert.ToString(r[1].ToString());
if (CheckLeader(tid, tl.TourleaderId))
{
if (CheckPt(tl.TourleaderId))
{
if (CheckPtDestination(tl.TourleaderId, pid))
{
tl_list.Add(tl);
}
}
else
{
tl_list.Add(tl);
}
}
}
}
return(tl_list);
}
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 bool CheckPt(int tl_id)
{
TourLeader tl = new TourLeader();
SqlConnection con = new SqlConnection();
DBConnect db = new DBConnect();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"select count(*) from TourLeaders t, ParttimeLeaders pt where t.TourLeaderId = pt.TourLeaderId and t.TourLeaderId = " + tl_id;
db.SetData(cmd);
DataTable tbl = db.GetData(cmd);
if (Convert.ToInt32(tbl.Rows[0][0]) > 0)
{
return(true);
}
return(false);
}
public virtual List <TourLeader> GetTourLeads()
{
var leads = new List <TourLeader>();
var db = new DBConnect();
var dtLeads = db.GetData(new SqlCommand("SELECT * FROM TourLeaders"));
for (int i = 0; i < dtLeads.Rows.Count; i++)
{
var lead = new TourLeader();
var row = dtLeads.Rows[i];
lead.TourleaderId = Convert.ToInt32(row["TourLeaderId"]);
lead.Name = row["Name"].ToString();
lead.ContactNumber = row["ContactNumber"].ToString();
lead.Email = row["Email"].ToString();
leads.Add(lead);
}
return(leads);
}
