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); }