public List <FeedbackForm> GetAllFeedBackAvailable(string AdminNo)
{
// Step 2 : declare a list to hold collection of customer's timeDeposit
// DataSet instance and dataTable instance
List <FeedbackForm> tdList = new List <FeedbackForm>();
DataSet ds = new DataSet();
DataTable tdData = new DataTable();
//
// Step 3 :Create SQLcommand to select all columns from TDMaster by parameterised customer id
// where TD is not matured yet
StringBuilder sqlStr = new StringBuilder();
sqlStr.AppendLine("select r.tripid, triptitle, location, CONVERT(VARCHAR(10), tripstart, 103) + '-' + CONVERT(VARCHAR(10), tripend, 103) AS [TIMERANGE] from trip r ");
sqlStr.AppendLine("inner join interview i on r.tripid = i.tripid ");
sqlStr.AppendLine("where tripend < GETDATE() and studentchoice = 'Accepted' and AdminNo = @paraAdminNo and (FeedbackDone ='' or FeedbackDone IS NULL) ");
// Step 4 :Instantiate SqlConnection instance and SqlDataAdapter instance
SqlConnection myConn = new SqlConnection(DBConnect);
SqlDataAdapter da = new SqlDataAdapter(sqlStr.ToString(), myConn);
da.SelectCommand.Parameters.AddWithValue("paraAdminNo", AdminNo);
// Step 5 :add value to parameter
// Step 6: fill dataset
da.Fill(ds, "TableTD");
// Step 7: Iterate the rows from TableTD above to create a collection of TD
// for this particular customer
int rec_cnt = ds.Tables["TableTD"].Rows.Count;
if (rec_cnt > 0)
{
foreach (DataRow row in ds.Tables["TableTD"].Rows)
{
FeedbackForm myTD = new FeedbackForm();
// Step 8 Set attribute of timeDeposit instance for each row of record in TableTD
myTD.TripId = Convert.ToInt32(row["TripId"]);
myTD.location = row["location"].ToString();
myTD.TimeRange = row["TIMERANGE"].ToString();
myTD.TripTitle = row["triptitle"].ToString();
// Step 9: Add each timeDeposit instance to array list
tdList.Add(myTD);
}
}
else
{
tdList = null;
}
return(tdList);
}
public FeedbackForm GetFeedbackSelected(string TripId, string AdminNo)
{
// Step 2 : declare a list to hold collection of customer's timeDeposit
// DataSet instance and dataTable instance
FeedbackForm td = new FeedbackForm();
DataSet ds = new DataSet();
DataTable tdData = new DataTable();
//
// Step 3 :Create SQLcommand to select all columns from TDMaster by parameterised customer id
// where TD is not matured yet
StringBuilder sqlStr = new StringBuilder();
sqlStr.AppendLine("select location, StudentName from interview i ");
sqlStr.AppendLine("inner join trip t on i.tripid = t.tripid ");
sqlStr.AppendLine("inner join student s on i.AdminNo= s.AdminNo ");
sqlStr.AppendLine("where i.tripid = @paraTripId and i.AdminNo = @paraAdminNo");
// Step 4 :Instantiate SqlConnection instance and SqlDataAdapter instance
SqlConnection myConn = new SqlConnection(DBConnect);
SqlDataAdapter da = new SqlDataAdapter(sqlStr.ToString(), myConn);
// Step 5 :add value to parameter
da.SelectCommand.Parameters.AddWithValue("paraTripId", TripId);
da.SelectCommand.Parameters.AddWithValue("paraAdminNo", AdminNo);
// Step 6: fill dataset
da.Fill(ds, "TableTD");
// Step 7: Iterate the rows from TableTD above to create a collection of TD
// for this particular customer
int rec_cnt = ds.Tables["TableTD"].Rows.Count;
FeedbackForm myTD = new FeedbackForm();
if (rec_cnt > 0)
{
// Step 8 Set attribute of timeDeposit instance for the record in TableTD
// DataRow is set to Rows[0] because only one row is returned
//
DataRow row = ds.Tables["TableTD"].Rows[0];
myTD.location = row["location"].ToString();
myTD.StudentName = row["StudentName"].ToString();
}
else
{
myTD = null;
}
return(myTD);
}
public FeedbackForm GetSpecificTrip(int tripId)
{
// Step 2 : declare a list to hold collection of customer's timeDeposit
// DataSet instance and dataTable instance
FeedbackForm td = new FeedbackForm();
DataSet ds = new DataSet();
DataTable tdData = new DataTable();
//
// Step 3 :Create SQLcommand to select all columns from TDMaster by parameterised customer id
// where TD is not matured yet
StringBuilder sqlStr = new StringBuilder();
sqlStr.AppendLine("SELECT * From trip");
sqlStr.AppendLine("where TripId = @paratripId");
// Step 4 :Instantiate SqlConnection instance and SqlDataAdapter instance
SqlConnection myConn = new SqlConnection(DBConnect);
SqlDataAdapter da = new SqlDataAdapter(sqlStr.ToString(), myConn);
// Step 5 :add value to parameter
da.SelectCommand.Parameters.AddWithValue("paratripId", tripId);
// Step 6: fill dataset
da.Fill(ds, "TableTD");
// Step 7: Iterate the rows from TableTD above to create a collection of TD
// for this particular customer
int rec_cnt = ds.Tables["TableTD"].Rows.Count;
FeedbackForm myTD = new FeedbackForm();
if (rec_cnt > 0)
{
// Step 8 Set attribute of timeDeposit instance for the record in TableTD
// DataRow is set to Rows[0] because only one row is returned
//
DataRow row = ds.Tables["TableTD"].Rows[0];
myTD.TripTitle = row["TRIPTITLE"].ToString();
myTD.TripId = Convert.ToInt32(row["TripId"]);
myTD.Country = row["location"].ToString();
}
else
{
myTD = null;
}
return(myTD);
}
public List <FeedbackForm> GetOwnFeedBack(string AdminNo)
{
// Step 2 : declare a list to hold collection of customer's timeDeposit
// DataSet instance and dataTable instance
List <FeedbackForm> tdList = new List <FeedbackForm>();
DataSet ds = new DataSet();
DataTable tdData = new DataTable();
//
// Step 3 :Create SQLcommand to select all columns from TDMaster by parameterised customer id
// where TD is not matured yet
StringBuilder sqlStr = new StringBuilder();
sqlStr.AppendLine("SELECT * From FeedBack");
sqlStr.AppendLine("where AdminNo = @paraAdminNo");
// Step 4 :Instantiate SqlConnection instance and SqlDataAdapter instance
SqlConnection myConn = new SqlConnection(DBConnect);
SqlDataAdapter da = new SqlDataAdapter(sqlStr.ToString(), myConn);
// Step 5 :add value to parameter
da.SelectCommand.Parameters.AddWithValue("paraAdminNo", AdminNo);
// Step 6: fill dataset
da.Fill(ds, "TableTD");
// Step 7: Iterate the rows from TableTD above to create a collection of TD
// for this particular customer
int rec_cnt = ds.Tables["TableTD"].Rows.Count;
if (rec_cnt > 0)
{
foreach (DataRow row in ds.Tables["TableTD"].Rows)
{
FeedbackForm myTD = new FeedbackForm();
// Step 8 Set attribute of timeDeposit instance for each row of record in TableTD
myTD.Affordability = row["Affordability"].ToString();
myTD.Enjoyment = row["Enjoyment"].ToString();
myTD.Freedom = row["Freedom"].ToString();
myTD.ReviewPros = row["ReviewPros"].ToString();
myTD.ReviewCons = row["ReviewCons"].ToString();
myTD.ReviewImprovement = row["ReviewImprovement"].ToString();
myTD.AdminNo = row["AdminNo"].ToString();
myTD.TripId = Convert.ToInt32(row["TripId"]);
myTD.Country = row["Country"].ToString();
myTD.StudentName = row["StudentName"].ToString();
myTD.FeedBackId = Convert.ToInt16(row["FeedBackId"]);
// Step 9: Add each timeDeposit instance to array list
tdList.Add(myTD);
}
}
else
{
tdList = null;
}
return(tdList);
}
public List <FeedbackForm> GetFilteredFeedBacks(string Country, string Affordability, string Freedom, string DateStart, string DateEnd)
{
// Step 2 : declare a list to hold collection of customer's timeDeposit
// DataSet instance and dataTable instance
List <FeedbackForm> tdList = new List <FeedbackForm>();
DataSet ds = new DataSet();
DataTable tdData = new DataTable();
//
// Step 3 :Create SQLcommand to select all columns from TDMaster by parameterised customer id
// where TD is not matured yet
StringBuilder sqlStr = new StringBuilder();
sqlStr.AppendLine("SELECT * From FeedBack");
sqlStr.AppendLine("WHERE Country = @paraCountry and Affordability = @paraAffordability and Freedom = @paraFreedom and DateCreated BETWEEN @paraDateStart and @paraDateEnd");
// Step 4 :Instantiate SqlConnection instance and SqlDataAdapter instance
SqlConnection myConn = new SqlConnection(DBConnect);
SqlDataAdapter da = new SqlDataAdapter(sqlStr.ToString(), myConn);
// Step 5 :add value to parameter
da.SelectCommand.Parameters.AddWithValue("paraCountry", Country);
da.SelectCommand.Parameters.AddWithValue("paraAffordability", Affordability);
da.SelectCommand.Parameters.AddWithValue("paraFreedom", Freedom);
da.SelectCommand.Parameters.AddWithValue("paraDateStart", DateStart);
da.SelectCommand.Parameters.AddWithValue("paraDateEnd", DateEnd);
// Step 6: fill dataset
da.Fill(ds, "TableTD");
// Step 7: Iterate the rows from TableTD above to create a collection of TD
// for this particular customer
int rec_cnt = ds.Tables["TableTD"].Rows.Count;
FeedbackForm myTD = new FeedbackForm();
if (rec_cnt > 0)
{
foreach (DataRow row in ds.Tables["TableTD"].Rows)
{
// Step 8 Set attribute of timeDeposit instance for the record in TableTD
// DataRow is set to Rows[0] because only one row is returned
myTD.Affordability = row["Affordability"].ToString();
myTD.Enjoyment = row["Enjoyment"].ToString();
myTD.Freedom = row["Freedom"].ToString();
myTD.ReviewPros = row["ReviewPros"].ToString();
myTD.ReviewCons = row["ReviewCons"].ToString();
myTD.ReviewImprovement = row["ReviewImprovement"].ToString();
myTD.AdminNo = row["AdminNo"].ToString();
myTD.TripId = Convert.ToInt32(row["TripId"]);
myTD.Country = row["Country"].ToString();
myTD.StudentName = row["StudentName"].ToString();
tdList.Add(myTD);
}
}
else
{
myTD = null;
}
return(tdList);
}