protected void createMessagesTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("Found in", typeof(string));
dt.Columns.Add("Time", typeof(string));
dt.Columns.Add("Type", typeof(string));
dt.Columns.Add("Creator", typeof(string));
string id = "", title = "", type = "", creator = "", time = "";
string searchString = txtSearch.Text.Replace("'", "''");
connect.Open();
SqlCommand cmd = connect.CreateCommand();
cmd.CommandText = "select count(*) from entries where entry_text like '%" + searchString + "%' and entry_isDeleted = 0 and entry_isApproved = 1 and entry_isDenied = 0 ";
int count = Convert.ToInt32(cmd.ExecuteScalar());
for (int i = 1; i <= count; i++)
{
//Get the topic ID:
cmd.CommandText = "select [topicId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY topicId ASC), * FROM [Entries] where entry_text like '%" + searchString + "%' and entry_isDeleted = 0 and entry_isApproved = 1 and entry_isDenied = 0) as t where rowNum = '" + i + "'";
string new_id = cmd.ExecuteScalar().ToString();
if (!new_id.Equals(id))
{
id = new_id;
//Check if the topic of the selected message is deleted or not:
cmd.CommandText = "select topic_isDeleted from Topics where topicId = '" + id + "' ";
int isDeleted = Convert.ToInt32(cmd.ExecuteScalar());
if (isDeleted == 0)//0: False, meaning that the topic is not deleted
{
//Get type:
cmd.CommandText = "select [topic_time] from Topics where topicId = '" + id + "' ";
time = cmd.ExecuteScalar().ToString();
//Get title:
cmd.CommandText = "select [topic_title] from Topics where topicId = '" + id + "' ";
title = cmd.ExecuteScalar().ToString();
//Get type:
cmd.CommandText = "select [topic_type] from Topics where topicId = '" + id + "' ";
type = cmd.ExecuteScalar().ToString();
//Get creator's ID:
cmd.CommandText = "select [topic_createdBy] from Topics where topicId = '" + id + "' ";
string creatorId = cmd.ExecuteScalar().ToString();
//Get creator's name:
cmd.CommandText = "select user_firstname from users where userId = '" + creatorId + "' ";
creator = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_lastname from users where userId = '" + creatorId + "' ";
creator = creator + " " + cmd.ExecuteScalar().ToString();
dt.Rows.Add(title, "Message text", Layouts.getTimeFormat(time), type, creator);
}
}
}
connect.Close();
grdResults.DataSource = dt;
grdResults.DataBind();
grdResults.Visible = true;
rebindValues();
}
protected void createTable(int count)
{
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(string));
dt.Columns.Add("Time", typeof(string));
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("Type", typeof(string));
dt.Columns.Add("Creator", typeof(string));
string id = "", title = "", type = "", creator = "", time = "";
connect.Open();
SqlCommand cmd = connect.CreateCommand();
for (int i = 1; i <= count; i++)
{
//Get the topic ID:
cmd.CommandText = "select [topicId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY topicId ASC), * FROM [Topics] where topic_isApproved = 1 and topic_isDenied = 0 and topic_isDeleted = 0) as t where rowNum = '" + i + "'";
id = cmd.ExecuteScalar().ToString();
//Get type:
cmd.CommandText = "select [topic_time] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY topicId ASC), * FROM [Topics] where topic_isApproved = 1 and topic_isDenied = 0 and topic_isDeleted = 0) as t where rowNum = '" + i + "'";
time = cmd.ExecuteScalar().ToString();
//Get title:
cmd.CommandText = "select [topic_title] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY topicId ASC), * FROM [Topics] where topic_isApproved = 1 and topic_isDenied = 0 and topic_isDeleted = 0) as t where rowNum = '" + i + "'";
title = cmd.ExecuteScalar().ToString();
//Get type:
cmd.CommandText = "select [topic_type] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY topicId ASC), * FROM [Topics] where topic_isApproved = 1 and topic_isDenied = 0 and topic_isDeleted = 0) as t where rowNum = '" + i + "'";
type = cmd.ExecuteScalar().ToString();
//Get creator's ID:
cmd.CommandText = "select [topic_createdBy] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY topicId ASC), * FROM [Topics] where topic_isApproved = 1 and topic_isDenied = 0 and topic_isDeleted = 0) as t where rowNum = '" + i + "'";
string creatorId = cmd.ExecuteScalar().ToString();
//Get creator's name:
cmd.CommandText = "select user_firstname from users where userId = '" + creatorId + "' ";
creator = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_lastname from users where userId = '" + creatorId + "' ";
creator = creator + " " + cmd.ExecuteScalar().ToString();
if (!type.Equals("Consultation"))
{
dt.Rows.Add(id, Layouts.getTimeFormat(time), title, type, creator);
}
}
connect.Close();
grdTopics.DataSource = dt;
grdTopics.DataBind();
//grdTopics.AutoGenerateColumns = true;
if (grdTopics.Rows.Count > 0)
{
//Hide the header called "ID":
grdTopics.HeaderRow.Cells[1].Visible = false;
//Hide IDs column and content which are located in column index 1:
for (int i = 0; i < grdTopics.Rows.Count; i++)
{
grdTopics.Rows[i].Cells[1].Visible = false;
}
rebindValues();
}
}
protected void createUsersTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("Found in", typeof(string));
dt.Columns.Add("Time", typeof(string));
dt.Columns.Add("Type", typeof(string));
dt.Columns.Add("Creator", typeof(string));
string id = "", title = "", type = "", creator = "", time = "";
string searchString = txtSearch.Text.Replace("'", "''");
connect.Open();
SqlCommand cmd = connect.CreateCommand();
cmd.CommandText = "select count(*) from users where (user_firstname+ ' ' +user_lastname) like '%" + searchString + "%' ";
int totalUsers = Convert.ToInt32(cmd.ExecuteScalar());
for (int i = 1; i <= totalUsers; i++)
{
cmd.CommandText = "select [userId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY userId ASC), * FROM [Users] where (user_firstname+ ' ' +user_lastname) like '%" + searchString + "%' ) as t where rowNum = '" + i + "'";
string temp_userId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select count(*) from topics where topic_createdBy = '" + temp_userId + "' and topic_isApproved = 1 and topic_isDenied = 0 and topic_isDeleted = 0";
int totalTopicsForTempUser = Convert.ToInt32(cmd.ExecuteScalar());
for (int j = 1; j <= totalTopicsForTempUser; j++)
{
//Get the topic ID:
cmd.CommandText = "select [topicId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY topicId ASC), * FROM [Topics] where topic_createdBy = '" + temp_userId + "' and topic_isApproved = 1 and topic_isDenied = 0 and topic_isDeleted = 0) as t where rowNum = '" + j + "'";
id = cmd.ExecuteScalar().ToString();
//Get type:
cmd.CommandText = "select [topic_time] from Topics where topicId = '" + id + "' ";
time = cmd.ExecuteScalar().ToString();
//Get title:
cmd.CommandText = "select [topic_title] from Topics where topicId = '" + id + "' ";
title = cmd.ExecuteScalar().ToString();
//Get type:
cmd.CommandText = "select [topic_type] from Topics where topicId = '" + id + "' ";
type = cmd.ExecuteScalar().ToString();
//Get creator's ID:
cmd.CommandText = "select [topic_createdBy] from Topics where topicId = '" + id + "' ";
string creatorId = cmd.ExecuteScalar().ToString();
//Get creator's name:
cmd.CommandText = "select user_firstname from users where userId = '" + creatorId + "' ";
creator = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_lastname from users where userId = '" + creatorId + "' ";
creator = creator + " " + cmd.ExecuteScalar().ToString();
dt.Rows.Add(title, "Creator name", Layouts.getTimeFormat(time), type, creator);
}
}
connect.Close();
grdResults.DataSource = dt;
grdResults.DataBind();
grdResults.Visible = true;
rebindValues();
}
protected string getTopicInformation()
{
string info = "";
connect.Open();
SqlCommand cmd = connect.CreateCommand();
cmd.CommandText = "select topic_title from Topics where topicId = '" + topicId + "' ";
string title = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select topic_type from Topics where topicId = '" + topicId + "' ";
string type = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select topic_time from Topics where topicId = '" + topicId + "' ";
string time = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select topic_description from Topics where topicId = '" + topicId + "' ";
string description = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select topic_createdBy from Topics where topicId = '" + topicId + "' ";
string creatorId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_firstname from users where userId = '" + creatorId + "' ";
string creator = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_lastname from users where userId = '" + creatorId + "' ";
creator = creator + " " + cmd.ExecuteScalar().ToString();
//Now check if the topic has images:
cmd.CommandText = "select topic_hasImage from Topics where topicId = '" + topicId + "' ";
int hasImage = Convert.ToInt32(cmd.ExecuteScalar());
string imagesHTML = "";
if (hasImage == 1)
{
cmd.CommandText = "select count(*) from ImagesForTopics where topicId = '" + topicId + "' ";
int totalImages = Convert.ToInt32(cmd.ExecuteScalar());
for (int i = 1; i <= totalImages; i++)
{
cmd.CommandText = "select [imageId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY imageId ASC), * FROM [ImagesForTopics] where topicId = '" + topicId + "') as t where rowNum = '" + i + "'";
string imageId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select image_name from Images where imageId = '" + imageId + "' ";
string image_name = cmd.ExecuteScalar().ToString();
imagesHTML = imagesHTML + "<img src='../../images/" + image_name + "'></img> <br /><br />";
}
}
info = "Creator: " + creator + "<br />" +
"Type: " + type + "<br />" +
"Title: " + title + "<br />" +
"Time: " + Layouts.getTimeFormat(time) + "<br />" +
"Description: \"" + description + "\"<br />" +
imagesHTML;
connect.Close();
return(info);
}
protected void createTable()
{
int countProjects = getTotalProjects();
if (countProjects == 0)
{
lblMessage.Visible = true;
}
else if (countProjects > 0)
{
lblMessage.Visible = false;
DataTable dt = new DataTable();
dt.Columns.Add("Project Name", typeof(string));
dt.Columns.Add("Created by", typeof(string));
dt.Columns.Add("Created time", typeof(string));
dt.Columns.Add("Creator ID", typeof(string));
string id = "", project_name = "", createdBy = "", createdOn = "", creatorId = "";
connect.Open();
SqlCommand cmd = connect.CreateCommand();
cmd.CommandText = "select userId from Users where loginId = '" + loginId + "' ";
string userId = cmd.ExecuteScalar().ToString();
for (int i = 1; i <= countProjects; i++)
{
//Get the project ID:
cmd.CommandText = "select [projectId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY projectId ASC), * FROM [UsersForProjects] where userId = '" + userId + "' ) as t where rowNum = '" + i + "'";
id = cmd.ExecuteScalar().ToString();
//Check if the project is deleted:
cmd.CommandText = "select project_isDeleted from Projects where projectId = '" + id + "' ";
int project_isDeleted = Convert.ToInt32(cmd.ExecuteScalar());
if (project_isDeleted == 0)
{
//Get project name:
cmd.CommandText = "select project_name from Projects where projectId = '" + id + "' ";
project_name = cmd.ExecuteScalar().ToString();
//Get the project's creator User ID:
cmd.CommandText = "select project_createdBy from Projects where projectId = '" + id + "' ";
creatorId = cmd.ExecuteScalar().ToString();
//Convert the User ID to a name:
cmd.CommandText = "select (user_firstname + ' ' + user_lastname) from Users where userId = '" + creatorId + "' ";
createdBy = cmd.ExecuteScalar().ToString();
//Get project creation date:
cmd.CommandText = "select project_createdDate from Projects where projectId = '" + id + "' ";
createdOn = cmd.ExecuteScalar().ToString();
dt.Rows.Add(project_name, createdBy, Layouts.getTimeFormat(createdOn), creatorId);
//Creator ID is not needed here, but it's used to uniquely identify the names in the system in case we have duplicate names.
}
}
connect.Close();
grdProjects.DataSource = dt;
grdProjects.DataBind();
rebindValues();
}
}
protected void createTimePeriodTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("Found in", typeof(string));
dt.Columns.Add("Time", typeof(string));
dt.Columns.Add("Type", typeof(string));
dt.Columns.Add("Creator", typeof(string));
string id = "", title = "", type = "", creator = "", time = "";
string searchString = txtSearch.Text.Replace("'", "''");
int count = 0;
connect.Open();
SqlCommand cmd = connect.CreateCommand();
DateTime start_time = calFrom.SelectedDate, end_time = calTo.SelectedDate;
if (!start_time.ToString().Equals("1/1/0001 12:00:00 AM") && !end_time.ToString().Equals("1/1/0001 12:00:00 AM"))
{
cmd.CommandText = "select count(*) from topics where topic_time >= '" + start_time + "' and topic_time <= '" + end_time + "' and topic_isDeleted = 0 and topic_isDenied = 0 and topic_isApproved = 1 ";
count = Convert.ToInt32(cmd.ExecuteScalar());
}
for (int i = 1; i <= count; i++)
{
//Get the topic ID:
cmd.CommandText = "select [topicId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY topicId ASC), * FROM [Topics] where topic_time >= '" + calFrom.SelectedDate + "' and topic_time <= '" + calTo.SelectedDate + "' and topic_isApproved = 1 and topic_isDenied = 0 and topic_isDeleted = 0) as t where rowNum = '" + i + "'";
id = cmd.ExecuteScalar().ToString();
//Get type:
cmd.CommandText = "select [topic_time] from Topics where topicId = '" + id + "' ";
time = cmd.ExecuteScalar().ToString();
//Get title:
cmd.CommandText = "select [topic_title] from Topics where topicId = '" + id + "' ";
title = cmd.ExecuteScalar().ToString();
//Get type:
cmd.CommandText = "select [topic_type] from Topics where topicId = '" + id + "' ";
type = cmd.ExecuteScalar().ToString();
//Get creator's ID:
cmd.CommandText = "select [topic_createdBy] from Topics where topicId = '" + id + "' ";
string creatorId = cmd.ExecuteScalar().ToString();
//Get creator's name:
cmd.CommandText = "select user_firstname from users where userId = '" + creatorId + "' ";
creator = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_lastname from users where userId = '" + creatorId + "' ";
creator = creator + " " + cmd.ExecuteScalar().ToString();
dt.Rows.Add(title, "Time period", Layouts.getTimeFormat(time), type, creator);
}
connect.Close();
grdResults.DataSource = dt;
grdResults.DataBind();
grdResults.Visible = true;
rebindValues();
}
protected void rebindValues()
{
if (grdProjects.Rows.Count > 0)
{
//Hide the header called "User ID":
grdProjects.HeaderRow.Cells[3].Visible = false;
//Hide IDs column and content which are located in column index 3:
for (int i = 0; i < grdProjects.Rows.Count; i++)
{
grdProjects.Rows[i].Cells[3].Visible = false;
}
}
connect.Open();
try
{
SqlCommand cmd = connect.CreateCommand();
string project_name = "", createdBy = "", createdOn = "", creatorId = "";
for (int row = 0; row < grdProjects.Rows.Count; row++)
{
//Set links to review a user:
project_name = grdProjects.Rows[row].Cells[0].Text;
createdBy = grdProjects.Rows[row].Cells[1].Text;
createdOn = grdProjects.Rows[row].Cells[2].Text;
creatorId = grdProjects.Rows[row].Cells[3].Text;
//Get the Project ID:
cmd.CommandText = "select [projectId] from [Projects] where project_name like '" + project_name + "' and " +
"project_createdDate = '" + Layouts.getOriginalTimeFormat(createdOn) + "' and project_createdBy = '" + creatorId + "' ";
string id = cmd.ExecuteScalar().ToString();
//string linkToReviewUser = "******" + id;
HyperLink projectLink = new HyperLink();
HyperLink userLink = new HyperLink();
HyperLink dateLink = new HyperLink();
projectLink.Text = project_name + " ";
userLink.Text = createdBy + " ";
dateLink.Text = Layouts.getTimeFormat(createdOn) + " ";
projectLink.NavigateUrl = "ViewProject.aspx?id=" + id;
userLink.NavigateUrl = "Profile.aspx?id=" + creatorId;
dateLink.NavigateUrl = "ViewProject.aspx?id=" + id;
grdProjects.Rows[row].Cells[0].Controls.Add(projectLink);
grdProjects.Rows[row].Cells[1].Controls.Add(userLink);
grdProjects.Rows[row].Cells[2].Controls.Add(dateLink);
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.ToString());
}
connect.Close();
}
protected void createTable(int count)
{
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(string));
dt.Columns.Add("Complain time", typeof(string));
dt.Columns.Add("From user", typeof(string));
dt.Columns.Add("Creator ID", typeof(string));
string id = "", time = "", from_user = "";
connect.Open();
SqlCommand cmd = connect.CreateCommand();
cmd.CommandType = CommandType.Text;
for (int i = 1; i <= count; i++)
{
//Get the complain's ID:
cmd.CommandText = "select [complainId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY complainId ASC), * FROM [Complains] ) as t where rowNum = '" + i + "'";
id = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select complain_time from Complains where complainId = '" + id + "' ";
time = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select complain_fromUser from Complains where complainId = '" + id + "' ";
string reporter_userId = cmd.ExecuteScalar().ToString();
//Get reporter's name:
cmd.CommandText = "select (user_firstname + ' ' + user_lastname) from users where userId = '" + reporter_userId + "' ";
from_user = cmd.ExecuteScalar().ToString();
//Get the entryId:
cmd.CommandText = "select entryId from Complains where complainId = '" + id + "' ";
string entryId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select entry_isDeleted from Entries where entryId = '" + entryId + "' ";
int entry_isDeleted = Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandText = "select userId from Entries where entryId = '" + entryId + "' ";
string creatorId = cmd.ExecuteScalar().ToString();
//If the complaint was not deleted, add it to the table:
if (entry_isDeleted == 0)
{
dt.Rows.Add(id, Layouts.getTimeFormat(time), from_user, creatorId);
}
}
connect.Close();
grdComplains.DataSource = dt;
grdComplains.DataBind();
rebindValues();
}
protected void showMessageInformation()
{
connect.Open();
SqlCommand cmd = connect.CreateCommand();
//Check if the ID exists in the database:
cmd.CommandText = "select count(*) from [Entries] where [entryId] = '" + messageId + "' ";
int countMessage = Convert.ToInt32(cmd.ExecuteScalar());
if (countMessage > 0)//if ID exists, countMessage = 1
{
//Get topic ID:
cmd.CommandText = "select topicId from [Entries] where [entryId] = '" + messageId + "' ";
string topicId = cmd.ExecuteScalar().ToString();
//Get topic title:
cmd.CommandText = "select topic_title from [Topics] where [topicId] = '" + topicId + "' ";
string topic_title = cmd.ExecuteScalar().ToString();
//Get entry userId:
cmd.CommandText = "select userId from [Entries] where [entryId] = '" + messageId + "' ";
string userId = cmd.ExecuteScalar().ToString();
//Get user full name:
cmd.CommandText = "select user_firstname from users where userId = '" + userId + "' ";
string creator = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_lastname from users where userId = '" + userId + "' ";
creator = creator + " " + cmd.ExecuteScalar().ToString();
//Get user's email:
cmd.CommandText = "select user_email from users where userId = '" + userId + "' ";
string email = cmd.ExecuteScalar().ToString();
//Get entry_time:
cmd.CommandText = "select entry_time from [Entries] where [entryId] = '" + messageId + "' ";
string entry_time = cmd.ExecuteScalar().ToString();
//Get entry_text:
cmd.CommandText = "select entry_text from [Entries] where [entryId] = '" + messageId + "' ";
string entry_text = cmd.ExecuteScalar().ToString();
//Get entry_isDeleted:
cmd.CommandText = "select entry_isDeleted from [Entries] where [entryId] = '" + messageId + "' ";
int int_entry_isDeleted = Convert.ToInt32(cmd.ExecuteScalar());
string entry_isDeleted = "";
if (int_entry_isDeleted == 0)
{
entry_isDeleted = "Topic has not been deleted.";
}
else
{
entry_isDeleted = "Topic has been deleted.";
}
//Get entry_isApproved:
cmd.CommandText = "select entry_isApproved from [Entries] where [entryId] = '" + messageId + "' ";
int int_entry_isApproved = Convert.ToInt32(cmd.ExecuteScalar());
string entry_isApproved;
if (int_entry_isApproved == 0)
{
entry_isApproved = "Message has not been approved.";
}
else
{
entry_isApproved = "Message has been approved.";
}
//Get entry_isDenied:
cmd.CommandText = "select entry_isDenied from [Entries] where [entryId] = '" + messageId + "' ";
int int_entry_isDenied = Convert.ToInt32(cmd.ExecuteScalar());
string entry_isDenied;
if (int_entry_isDenied == 0)
{
entry_isDenied = "Message has not been approved.";
}
else
{
entry_isDenied = "Message has been approved.";
}
//Get "Yes" or "No" for entry_hasImage:
cmd.CommandText = "select entry_hasImage from [Entries] where [entryId] = '" + messageId + "' ";
int entry_hasImage = Convert.ToInt32(cmd.ExecuteScalar());
string str_entry_hasImage = "";
if (entry_hasImage == 0)
{
str_entry_hasImage = "Message does not have an image.";
}
else
{
str_entry_hasImage = "Message has an image.";
}
//Create an informative message containing all information for the selected user:
string imagesHTML = "";
if (entry_hasImage == 1)
{
cmd.CommandText = "select count(*) from ImagesForEntries where entryId = '" + messageId + "' ";
int totalImages = Convert.ToInt32(cmd.ExecuteScalar());
for (int i = 1; i <= totalImages; i++)
{
cmd.CommandText = "select [imageId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY imageId ASC), * FROM [ImagesForEntries] where entryId = '" + messageId + "') as t where rowNum = '" + i + "'";
string imageId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select image_name from Images where imageId = '" + imageId + "' ";
string image_name = cmd.ExecuteScalar().ToString();
imagesHTML = imagesHTML + "<img src='../../images/" + image_name + "'></img> <br />";
}
}
lblMessageInformation.Text = "<table style=\"width:100%;\">" +
"<tr><td>Creator: </td><td>" + creator + "</td></tr>" +
"<tr><td>Topic related:</td><td>" + topic_title + "</td></tr>" +
"<tr><td>Message time: </td><td>" + Layouts.getTimeFormat(entry_time) + "</td></tr>" +
"<tr><td>Deleted?: </td><td>" + entry_isDeleted + "</td></tr>" +
"<tr><td>Has image?: </td><td>" + str_entry_hasImage + "</td></tr>" +
"<tr><td>Approved?: </td><td>" + entry_isApproved + "</td></tr>" +
"<tr><td>Denied?: </td><td>" + entry_isDenied + "</td></tr>" +
"<tr><td>Message: </td><td> <div style=\"background: #DCCDCA; padding-left:5px; padding-right:5px; \"> " + entry_text +
imagesHTML + "</div></td></tr>" +
"</table>";
lblMessageInformation.Visible = true;
//Copy values to globals:
g_topic_isApproved = int_entry_isApproved; g_topic_isDenied = int_entry_isDenied;
g_creator = creator; g_topic_title = topic_title; g_email = email; g_entry_text = entry_text;
}
else
{
addSession();
Response.Redirect("ApproveMessages");
}
connect.Close();
}
protected void printInfo()
{
SqlCommand cmd = connect.CreateCommand();
connect.Open();
cmd.CommandText = "select project_name from Projects where projectId = '" + projectId + "' ";
string project_name = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select project_description from Projects where projectId = '" + projectId + "' ";
string project_description = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select count(*) from UsersForProjects where projectId = '" + projectId + "' ";
int totalUsersForProject = Convert.ToInt32(cmd.ExecuteScalar());
List <string> names = new List <string>();
for (int i = 1; i <= totalUsersForProject; i++)
{
cmd.CommandText = "select userId from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY userId ASC), * FROM usersForProjects where projectId = '" + projectId + "' ) as t where rowNum = '" + i + "' ";
string userId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select (user_firstname + ' ' + user_lastname) from Users where userId = '" + userId + "' ";
string name = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select loginId from Users where userId = '" + userId + "' ";
string loginId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select roleId from Logins where loginId = '" + loginId + "' ";
int int_roleId = Convert.ToInt32(cmd.ExecuteScalar());
if (int_roleId == 2)
{
name = "Project Master: " + name + "";
}
else
{
name = "Developer: " + name + "";
}
names.Add(name);
}
string projectHeader = "<h1>" + project_name + "</h1></br>" + project_description + "</br></br>";
foreach (string n in names)
{
projectHeader += n + "</br>";
}
projectHeader += "</br></br></br>";
string userStoriesTable =
"<h3>User Stories</h3>" +
"<table border='1' style='width:100%;' >" +
"<tr><th>Unique user story ID</th>" + "<th>As a \"type of user\"</th>" + "<th>I want to \"some goal\"</th>" + "<th>so that \"reason\"</th>" + "<th>Date introduced</th>" + "<th>Date considered for implementation</th>" + "<th>Developer responsible for</th>" + "<th>Current Status</th></tr>";
string sprintTasksTable =
"<h3>Sprint Tasks</h3>" +
"<table border='1' style='width:100%;' >" +
"<tr><th>Unique sprint task ID</th>" + "<th>Unique user story ID</th>" + "<th>Task description</th>" + "<th>Date introduced</th>" + "<th>Date considered for implementation</th>" + "<th>Date completed</th>" + "<th>Developer responsible for</th>" + "<th>Current Status</th></tr>";
string testCasesTables =
"<h3>Test Cases</h3>";
cmd.CommandText = "select count(*) from UserStories where projectId = '" + projectId + "' ";
int totalUserStories = Convert.ToInt32(cmd.ExecuteScalar());
int totalSprintTasks = 0;
int totalTestCases = 0;
//int sprintTaskCounter = 0;
for (int i = 1; i <= totalUserStories; i++)
{
cmd.CommandText = "select userStoryId from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY CAST(userStory_uniqueId AS float) ASC), * FROM UserStories where projectId = '" + projectId + "' ) as t where rowNum = '" + i + "' ";
string userStoryId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select userStory_uniqueId from UserStories where userStoryId = '" + userStoryId + "' ";
string userStory_uniqueId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select userStory_asARole from UserStories where userStoryId = '" + userStoryId + "' ";
string userStory_asARole = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select userStory_iWantTo from UserStories where userStoryId = '" + userStoryId + "' ";
string userStory_iWantTo = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select userStory_soThat from UserStories where userStoryId = '" + userStoryId + "' ";
string userStory_soThat = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select userStory_dateIntroduced from UserStories where userStoryId = '" + userStoryId + "' ";
string userStory_dateIntroduced = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select userStory_dateConsideredForImplementation from UserStories where userStoryId = '" + userStoryId + "' ";
string userStory_dateConsideredForImplementation = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select userStory_currentStatus from UserStories where userStoryId = '" + userStoryId + "' ";
string userStory_currentStatus = cmd.ExecuteScalar().ToString();
//Now get the list of developers:
cmd.CommandText = "select count(*) from UsersForUserStories where userStoryId = '" + userStoryId + "' ";
int totalUsersForUserStory = Convert.ToInt32(cmd.ExecuteScalar());
string usersForUserStories = "";
for (int j = 1; j <= totalUsersForUserStory; j++)
{
cmd.CommandText = "select userId from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY usersForUserStoriesId ASC), * FROM UsersForUserStories where userStoryId = '" + userStoryId + "' ) as t where rowNum = '" + j + "' ";
string temp_userId = cmd.ExecuteScalar().ToString();
//Translate the user ID to a user name:
cmd.CommandText = "select (user_firstname + ' ' + user_lastname) from Users where userId = '" + temp_userId + "' ";
string temp_name = cmd.ExecuteScalar().ToString();
if (j == 1)
{
usersForUserStories = temp_name;
}
else
{
usersForUserStories = usersForUserStories + ",</br>" + temp_name;
}
}
userStoriesTable +=
"<tr><td>" + userStory_uniqueId + "</td><td>" + userStory_asARole + "</td><td>" + userStory_iWantTo + "</td><td>" + userStory_soThat + "</td><td>" + Layouts.getTimeFormat(userStory_dateIntroduced) + "</td><td>" + Layouts.getTimeFormat(userStory_dateConsideredForImplementation) + "</td><td>" + usersForUserStories + "</td><td>" + userStory_currentStatus + "</td></tr>";
cmd.CommandText = "select count(*) from SprintTasks where userStoryId = '" + userStoryId + "' ";
int temp_totalSprintTasks = Convert.ToInt32(cmd.ExecuteScalar());
totalSprintTasks += temp_totalSprintTasks;
for (int j = 1; j <= temp_totalSprintTasks; j++)
{
//sprintTaskCounter++;
cmd.CommandText = "select sprintTaskId from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY CAST(sprintTask_uniqueId AS float) ASC), * FROM SprintTasks where userStoryId = '" + userStoryId + "' ) as t where rowNum = '" + j + "' ";
string sprintTaskId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select sprintTask_uniqueId from SprintTasks where sprintTaskId = '" + sprintTaskId + "' ";
string sprintTask_uniqueId = cmd.ExecuteScalar().ToString();
//userStory_uniqueId
cmd.CommandText = "select sprintTask_taskDescription from SprintTasks where sprintTaskId = '" + sprintTaskId + "' ";
string sprintTask_taskDescription = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select sprintTask_dateIntroduced from SprintTasks where sprintTaskId = '" + sprintTaskId + "' ";
string sprintTask_dateIntroduced = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select sprintTask_dateConsideredForImplementation from SprintTasks where sprintTaskId = '" + sprintTaskId + "' ";
string sprintTask_dateConsideredForImplementation = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select count(sprintTask_dateCompleted) from SprintTasks where sprintTaskId = '" + sprintTaskId + "' ";
int isCompleted = Convert.ToInt32(cmd.ExecuteScalar());
string sprintTask_dateCompleted = "";
if (isCompleted > 0)
{
cmd.CommandText = "select sprintTask_dateCompleted from SprintTasks where sprintTaskId = '" + sprintTaskId + "' ";
sprintTask_dateCompleted = cmd.ExecuteScalar().ToString();
sprintTask_dateCompleted = Layouts.getTimeFormat(sprintTask_dateCompleted);
}
else
{
sprintTask_dateCompleted = "Not completed";
}
//Now get the list of developers:
cmd.CommandText = "select count(*) from UsersForSprintTasks where sprintTaskId = '" + sprintTaskId + "' ";
int totalUsersForSprintTask = Convert.ToInt32(cmd.ExecuteScalar());
string usersForSprintTasks = "";
for (int k = 1; k <= totalUsersForSprintTask; k++)
{
cmd.CommandText = "select userId from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY usersForSprintTasksId ASC), * FROM UsersForSprintTasks where sprintTaskId = '" + sprintTaskId + "' ) as t where rowNum = '" + k + "' ";
string temp_userId = cmd.ExecuteScalar().ToString();
//Translate the user ID to a user name:
cmd.CommandText = "select (user_firstname + ' ' + user_lastname) from Users where userId = '" + temp_userId + "' ";
string temp_name = cmd.ExecuteScalar().ToString();
if (j == 1)
{
usersForSprintTasks = temp_name;
}
else
{
usersForSprintTasks = usersForSprintTasks + ",</br>" + temp_name;
}
}
cmd.CommandText = "select sprintTask_currentStatus from SprintTasks where sprintTaskId = '" + sprintTaskId + "' ";
string sprintTask_currentStatus = cmd.ExecuteScalar().ToString();
sprintTasksTable +=
"<tr><td>" + sprintTask_uniqueId + "</td><td>" + userStory_uniqueId + "</td><td>" + sprintTask_taskDescription + "</td><td>" + Layouts.getTimeFormat(sprintTask_dateIntroduced) + "</td><td>" + Layouts.getTimeFormat(sprintTask_dateConsideredForImplementation) + "</td><td>" + sprintTask_dateCompleted + "</td><td>" + usersForSprintTasks + "</td><td>" + sprintTask_currentStatus + "</td></tr>";
string testCaseTable =
"<h3>Test case for (user story ID: " + userStory_uniqueId + ", sprint task ID: " + sprintTask_uniqueId + ") </h3>" +
"<table border='1' style='width:100%;' >" +
"<tr><th>Unique test case ID</th>" + "<th>Unique user story ID</th>" + "<th>Sprint task ID</th>" + "<th>Test case scenario</th>" + "<th>Input Parameters</th>" + "<th>Expected output</th>" + "<th>Current Status</th></tr>";
cmd.CommandText = "select count(*) from TestCases where sprintTaskId = '" + sprintTaskId + "' ";
int temp_totalTestCases = Convert.ToInt32(cmd.ExecuteScalar());
totalTestCases += temp_totalTestCases;
for (int m = 1; m <= temp_totalTestCases; m++)
{
cmd.CommandText = "select testCaseId from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY CAST(testCase_uniqueId AS float) ASC), * FROM TestCases where sprintTaskId = '" + sprintTaskId + "' ) as t where rowNum = '" + m + "' ";
string testCaseId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select testCase_uniqueId from TestCases where testCaseId = '" + testCaseId + "' ";
string testCase_uniqueId = cmd.ExecuteScalar().ToString();
//userStory_uniqueId
//sprintTask_uniqueId
cmd.CommandText = "select testCase_testCaseScenario from TestCases where testCaseId = '" + testCaseId + "' ";
string testCase_testCaseScenario = cmd.ExecuteScalar().ToString();
//Get the input parameters:
cmd.CommandText = "select count(*) from Parameters where testCaseId = '" + testCaseId + "' ";
int totalParameters = Convert.ToInt32(cmd.ExecuteScalar());
string inputParameters = "";
for (int n = 1; n <= totalParameters; n++)
{
cmd.CommandText = "select parameter_name from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY parameterId ASC), * FROM Parameters where testCaseId = '" + testCaseId + "' ) as t where rowNum = '" + n + "' ";
string parameter = cmd.ExecuteScalar().ToString();
if (n == 1)
{
inputParameters = parameter;
}
else
{
inputParameters = inputParameters + ",</br>" + parameter;
}
}
cmd.CommandText = "select testCase_expectedOutput from TestCases where testCaseId = '" + testCaseId + "' ";
string testCase_expectedOutput = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select testCase_currentStatus from TestCases where testCaseId = '" + testCaseId + "' ";
string testCase_currentStatus = cmd.ExecuteScalar().ToString();
testCaseTable +=
"<tr><td>" + testCase_uniqueId + "</td><td>" + userStory_uniqueId + "</td><td>" + sprintTask_uniqueId + "</td><td>" + testCase_testCaseScenario + "</td><td>" + inputParameters + "</td><td>" + testCase_expectedOutput + "</td><td>" + testCase_currentStatus + "</td></tr>";
}
testCaseTable = (temp_totalTestCases > 0) ? testCaseTable + "</table></br>" : "";
testCasesTables += testCaseTable;
}
}
userStoriesTable = (totalUserStories > 0) ? userStoriesTable + "</table></br>" : "";
sprintTasksTable = (totalSprintTasks > 0) ? sprintTasksTable + "</table></br>" : "";
testCasesTables = (totalTestCases > 0) ? testCasesTables + "</br>" : "";
connect.Close();
string result =
projectHeader +
userStoriesTable +
sprintTasksTable +
testCasesTables;
lblContent.Text = result;
}
protected void createTable(int count)
{
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(string));
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("Time Created", typeof(string));
dt.Columns.Add("Current Participants", typeof(string));
string id = "", title = "", type = "", time = "";
connect.Open();
SqlCommand cmd = connect.CreateCommand();
//Get the current user ID:
cmd.CommandText = "select userId from users where loginId = '" + loginId + "' ";
string userId = cmd.ExecuteScalar().ToString();
for (int i = 1; i <= count; i++)
{
//Get the topic ID:
cmd.CommandText = "select [topicId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY usersForTopicsId ASC), * FROM [UsersForTopics] where isApproved = 1 and userId = '" + userId + "' ) as t where rowNum = '" + i + "'";
id = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select topic_isTerminated from topics where topicId = '" + id + "' ";
int isTerminated = Convert.ToInt32(cmd.ExecuteScalar());
if (isTerminated == 0)// 0 = false. Meaning that the topic is not terminated; therefore, show it in the list of my topics:
{
//Get type:
cmd.CommandText = "select [topic_time] from topics where topicId = '" + id + "' ";
time = cmd.ExecuteScalar().ToString();
//Get title:
cmd.CommandText = "select [topic_title] from topics where topicId = '" + id + "' ";
title = cmd.ExecuteScalar().ToString();
//Get type:
cmd.CommandText = "select [topic_type] from topics where topicId = '" + id + "' ";
type = cmd.ExecuteScalar().ToString();
//Get creator's ID:
cmd.CommandText = "select [topic_createdBy] from topics where topicId = '" + id + "' ";
string creatorId = cmd.ExecuteScalar().ToString();
//dt.Rows.Add(id, title, Layouts.getTimeFormat(time), participantLink);
dt.Rows.Add(id, title, Layouts.getTimeFormat(time));
}
}
grdTopics.DataSource = dt;
grdTopics.DataBind();
//Hide the header called "ID":
grdTopics.HeaderRow.Cells[1].Visible = false;
//Hide IDs column and content which are located in column index 1:
for (int i = 0; i < grdTopics.Rows.Count; i++)
{
grdTopics.Rows[i].Cells[1].Visible = false;
}
for (int row = 0; row < grdTopics.Rows.Count; row++)
{
id = grdTopics.Rows[row].Cells[1].Text;
//Get total approved participants for a topic:
cmd.CommandText = "select count(*) from UsersForTopics where topicId = '" + id + "' and isApproved = '1' ";
int totalApprovedParticipants = Convert.ToInt32(cmd.ExecuteScalar());
for (int j = 1; j <= totalApprovedParticipants; j++)
{
HyperLink participantLink = new HyperLink();
cmd.CommandText = "select [userId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY UsersForTopicsId ASC), * FROM [UsersForTopics] where topicId = '" + id + "' and isApproved = '1') as t where rowNum = '" + j + "'";
string participantId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_firstname from Users where userId = '" + participantId + "' ";
string participant_name = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_lastname from Users where userId = '" + participantId + "' ";
participant_name = participant_name + " " + cmd.ExecuteScalar().ToString();
participantLink.Text = participant_name + " ";
participantLink.NavigateUrl = "Profile.aspx?id=" + participantId;
grdTopics.Rows[row].Cells[4].Controls.Add(participantLink);
if (totalApprovedParticipants > 1)
{
HyperLink temp = new HyperLink();
temp.Text = "<br/>";
grdTopics.Rows[row].Cells[4].Controls.Add(temp);
}
}
if (totalApprovedParticipants == 0)
{
grdTopics.Rows[row].Cells[4].Text = "There are no participants";
}
}
connect.Close();
}
protected void showTopicInformation()
{
connect.Open();
SqlCommand cmd = connect.CreateCommand();
//Check if the ID exists in the database:
cmd.CommandText = "select count(*) from topics where topicId = '" + topicId + "' ";
int countTopic = Convert.ToInt32(cmd.ExecuteScalar());
if (countTopic > 0)//if ID exists, countTopic = 1
{
//Get topic_createdBy:
cmd.CommandText = "select topic_createdBy from [Topics] where [topicId] = '" + topicId + "' ";
string topic_createdBy = cmd.ExecuteScalar().ToString();
//Get creator's email:
cmd.CommandText = "select user_email from users where userId = '" + topic_createdBy + "' ";
string email = cmd.ExecuteScalar().ToString();
//Get creator's fullname:
cmd.CommandText = "select user_firstname from users where userId = '" + topic_createdBy + "' ";
string creator = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_lastname from users where userId = '" + topic_createdBy + "' ";
creator = creator + " " + cmd.ExecuteScalar().ToString();
//Get topic_type:
cmd.CommandText = "select topic_type from [Topics] where [topicId] = '" + topicId + "' ";
string topic_type = cmd.ExecuteScalar().ToString();
//Get topic_title:
cmd.CommandText = "select topic_title from [Topics] where [topicId] = '" + topicId + "' ";
string topic_title = cmd.ExecuteScalar().ToString();
//Get topic_time:
cmd.CommandText = "select topic_time from [Topics] where [topicId] = '" + topicId + "' ";
string topic_time = cmd.ExecuteScalar().ToString();
//Get topic_description:
cmd.CommandText = "select topic_description from [Topics] where [topicId] = '" + topicId + "' ";
string topic_description = cmd.ExecuteScalar().ToString();
//Get "Yes" or "No" for topic_hasImage:
cmd.CommandText = "select topic_hasImage from [Topics] where [topicId] = '" + topicId + "' ";
int topic_hasImage = Convert.ToInt32(cmd.ExecuteScalar());
string str_topic_hasImage = "";
if (topic_hasImage == 0)
{
str_topic_hasImage = "Topic does not have an image.";
}
else
{
str_topic_hasImage = "Topic has an image.";
}
//Get topic_isDeleted ?:
cmd.CommandText = "select topic_isDeleted from [Topics] where [topicId] = '" + topicId + "' ";
int int_topic_isDeleted = Convert.ToInt32(cmd.ExecuteScalar());
string topic_isDeleted = "";
if (int_topic_isDeleted == 0)
{
topic_isDeleted = "Topic has not been deleted.";
}
else
{
topic_isDeleted = "Topic has been deleted.";
}
//Get topic_isApproved ?:
cmd.CommandText = "select topic_isApproved from [Topics] where [topicId] = '" + topicId + "' ";
int int_topic_isApproved = Convert.ToInt32(cmd.ExecuteScalar());
string topic_isApproved;
if (int_topic_isApproved == 0)
{
topic_isApproved = "Topic has not been approved.";
}
else
{
topic_isApproved = "Topic has been approved.";
}
//Get topic_isDenied ?:
cmd.CommandText = "select topic_isDenied from [Topics] where [topicId] = '" + topicId + "' ";
int int_topic_isDenied = Convert.ToInt32(cmd.ExecuteScalar());
string topic_isDenied;
if (int_topic_isDenied == 0)
{
topic_isDenied = "Topic has not been denied.";
}
else
{
topic_isDenied = "Topic has been denied.";
}
//Get topic_isTerminated ?:
cmd.CommandText = "select topic_isTerminated from [Topics] where [topicId] = '" + topicId + "' ";
int int_topic_isTerminated = Convert.ToInt32(cmd.ExecuteScalar());
string topic_isTerminated = "";
if (int_topic_isTerminated == 0)
{
topic_isTerminated = "Topic has not been terminated.";
}
else
{
topic_isTerminated = "Topic has been terminated.";
}
//Get tags:
string tagNames = "";
cmd.CommandText = "select count(*) from TagsForTopics where topicId = '" + topicId + "' ";
int totalTags = Convert.ToInt32(cmd.ExecuteScalar());
if (totalTags == 0)
{
tagNames = "There are no tags for the selected topic";
}
for (int i = 1; i <= totalTags; i++)
{
cmd.CommandText = "select [tagId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY tagId ASC), * FROM [TagsForTopics] where topicId = '" + topicId + "') as t where rowNum = '" + i + "'";
string tagId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select tag_name from Tags where tagId = '" + tagId + "' ";
if (totalTags == 1)
{
tagNames = cmd.ExecuteScalar().ToString();
}
else if (totalTags > 1)
{
if (i == 0)
{
tagNames = cmd.ExecuteScalar().ToString();
}
else
{
tagNames = tagNames + ", " + cmd.ExecuteScalar().ToString();
}
}
}
//Create an informative message containing all information for the selected user:
string imagesHTML = "";
if (topic_hasImage == 1)
{
cmd.CommandText = "select count(*) from ImagesForTopics where topicId = '" + topicId + "' ";
int totalImages = Convert.ToInt32(cmd.ExecuteScalar());
for (int i = 1; i <= totalImages; i++)
{
cmd.CommandText = "select [imageId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY imageId ASC), * FROM [ImagesForTopics] where topicId = '" + topicId + "') as t where rowNum = '" + i + "'";
string imageId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select image_name from Images where imageId = '" + imageId + "' ";
string image_name = cmd.ExecuteScalar().ToString();
imagesHTML = imagesHTML + "<img src='../../images/" + image_name + "'></img> <br /> <br/>";
}
}
lblTopicInformation.Text =
"<table>" +
"<tr><td>Creator: </td><td>" + creator + "</td></tr>" +
"<tr><td>Type: </td><td>" + topic_type + "</td></tr>" +
"<tr><td>Title: </td><td>" + topic_title + "</td></tr>" +
"<tr><td>Time: </td><td>" + Layouts.getTimeFormat(topic_time) + "</td></tr>" +
"<tr><td>Has image?: </td><td>" + str_topic_hasImage + "</td></tr>" +
"<tr><td>Deleted?: </td><td>" + topic_isDeleted + "</td></tr>" +
"<tr><td>Approved?: </td><td>" + topic_isApproved + "</td></tr>" +
"<tr><td>Denied?: </td><td>" + topic_isDenied + "</td></tr>" +
"<tr><td>Terminated?: </td><td>" + topic_isTerminated + "</td></tr>" +
"<tr><td>Tags: </td><td>" + tagNames + "</td></tr>" +
"<tr><td>Description: </td><td> <div style=\"background: #DCCDCA; padding-left:5px; padding-right:5px; \"> " + topic_description +
imagesHTML + "</div></td></tr>" +
"</table>";
lblTopicInformation.Visible = true;
//Copy values to globals:
g_topic_isApproved = int_topic_isApproved; g_topic_isDenied = int_topic_isDenied;
g_creator = creator; g_topic_title = topic_title; g_email = email;
}
else
{
addSession();
Response.Redirect("ApproveTopics");
}
connect.Close();
}
protected bool correctInput()
{
bool correct = true;
if (string.IsNullOrWhiteSpace(txtTaskDescription.Text))
{
correct = false;
lblTaskDescriptionError.Text = "Invalid input: Please type something for \"Task description\" .";
lblTaskDescriptionError.Visible = true;
}
//Check the start date of the project:
SqlCommand cmd = connect.CreateCommand();
connect.Open();
cmd.CommandText = "select project_startedDate from Projects where projectId = '" + g_projectId + "' ";
DateTime project_startedDate = DateTime.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "select userStory_dateIntroduced from UserStories where userStoryId = '" + g_userStoryId + "' ";
DateTime userStory_dateIntroduced = DateTime.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "select userStory_dateConsideredForImplementation from UserStories where userStoryId = '" + g_userStoryId + "' ";
DateTime userStory_dateConsideredForImplementation = DateTime.Parse(cmd.ExecuteScalar().ToString());
connect.Close();
int differenceInDaysFromNow = (calDateIntroduced.SelectedDate - DateTime.Now).Days;
int differenceFromProjectStartDateToDateIntroduced = (calDateIntroduced.SelectedDate - project_startedDate).Days;
int differenceFromProjectStartDateToDateConsidered = (calDateConsidered.SelectedDate - project_startedDate).Days;
int differenceFromUserStoryDateConsideredToSprintTaskDateConsidered = (calDateConsidered.SelectedDate - userStory_dateConsideredForImplementation).Days;
int differenceFromUserStoryDateIntroducedDateToSprintTaskDateIntroduced = (calDateIntroduced.SelectedDate - userStory_dateIntroduced).Days;
if (differenceFromProjectStartDateToDateConsidered < 0)
{
correct = false;
lblDateConsideredError.Visible = true;
lblDateConsideredError.Text = "Invalid input: Please select a date starting after the project's start date (" + Layouts.getTimeFormat(project_startedDate.ToString()) + ").";
}
if (differenceFromProjectStartDateToDateIntroduced < 0)
{
correct = false;
lblDateConsideredError.Visible = true;
lblDateConsideredError.Text = "Please wait until the project's start date (" + Layouts.getTimeFormat(project_startedDate.ToString()) + ").";
}
if (differenceFromProjectStartDateToDateIntroduced > 0 && differenceFromProjectStartDateToDateConsidered > 0)
{
if (differenceFromUserStoryDateConsideredToSprintTaskDateConsidered < 0)
{
correct = false;
lblDateConsideredError.Visible = true;
lblDateConsideredError.Text = "Invalid input: Please select a date starting after the user story's considered date (" + Layouts.getTimeFormat(userStory_dateConsideredForImplementation.ToString()) + ").";
}
if (differenceFromUserStoryDateIntroducedDateToSprintTaskDateIntroduced < 0)
{
correct = false;
lblDateConsideredError.Visible = true;
lblDateConsideredError.Text = "Please wait until the user story's start date (" + Layouts.getTimeFormat(userStory_dateIntroduced.ToString()) + ").";
}
if (differenceFromUserStoryDateConsideredToSprintTaskDateConsidered > 0 && differenceFromUserStoryDateIntroducedDateToSprintTaskDateIntroduced > 0)
{
if (differenceInDaysFromNow < 0)
{
correct = false;
lblDateIntroducedError.Visible = true;
lblDateIntroducedError.Text = "Invalid input: Please select a date starting from now.";
}
differenceInDaysFromNow = (calDateConsidered.SelectedDate - DateTime.Now).Days;
if (differenceInDaysFromNow < 0)
{
correct = false;
lblDateConsideredError.Visible = true;
lblDateConsideredError.Text = "Invalid input: Please select a date starting from now.";
}
}
}
if (drpCurrentStatus.SelectedIndex == 0)
{
correct = false;
lblCurrentStatusError.Visible = true;
lblCurrentStatusError.Text = "Invalid input: Please select a status for this sprint task.";
}
if (usersToAdd.Count == 0)
{
correct = false;
lblDeveloperResponsibleError.Visible = true;
lblDeveloperResponsibleError.Text = "Invalid input: Please add at least one developer.";
}
return(correct);
}
protected void showTopicInformation()
{
connect.Open();
SqlCommand cmd = connect.CreateCommand();
//Check if the ID exists in the database:
cmd.CommandText = "select count(*) from topics where topicId = '" + topicId + "' ";
int countTopic = Convert.ToInt32(cmd.ExecuteScalar());
if (countTopic > 0)//if ID exists, countTopic = 1
{
//Get topic_createdBy:
cmd.CommandText = "select topic_createdBy from [Topics] where [topicId] = '" + topicId + "' ";
string topic_createdBy = cmd.ExecuteScalar().ToString();
//Get creator's email:
cmd.CommandText = "select user_email from users where userId = '" + topic_createdBy + "' ";
string email = cmd.ExecuteScalar().ToString();
//Get creator's fullname:
cmd.CommandText = "select user_firstname from users where userId = '" + topic_createdBy + "' ";
string creator = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_lastname from users where userId = '" + topic_createdBy + "' ";
creator = creator + " " + cmd.ExecuteScalar().ToString();
//Get topic_type:
cmd.CommandText = "select topic_type from [Topics] where [topicId] = '" + topicId + "' ";
string topic_type = cmd.ExecuteScalar().ToString();
//Get topic_title:
cmd.CommandText = "select topic_title from [Topics] where [topicId] = '" + topicId + "' ";
string topic_title = cmd.ExecuteScalar().ToString();
//Get topic_time:
cmd.CommandText = "select topic_time from [Topics] where [topicId] = '" + topicId + "' ";
string topic_time = cmd.ExecuteScalar().ToString();
//Get topic_description:
cmd.CommandText = "select topic_description from [Topics] where [topicId] = '" + topicId + "' ";
string topic_description = cmd.ExecuteScalar().ToString();
//Get "Yes" or "No" for topic_hasImage:
cmd.CommandText = "select topic_hasImage from [Topics] where [topicId] = '" + topicId + "' ";
int topic_hasImage = Convert.ToInt32(cmd.ExecuteScalar());
//Get topic_isDeleted ?:
cmd.CommandText = "select topic_isDeleted from [Topics] where [topicId] = '" + topicId + "' ";
int int_topic_isDeleted = Convert.ToInt32(cmd.ExecuteScalar());
//Get topic_isApproved ?:
cmd.CommandText = "select topic_isApproved from [Topics] where [topicId] = '" + topicId + "' ";
int int_topic_isApproved = Convert.ToInt32(cmd.ExecuteScalar());
//Get topic_isDenied ?:
cmd.CommandText = "select topic_isDenied from [Topics] where [topicId] = '" + topicId + "' ";
int int_topic_isDenied = Convert.ToInt32(cmd.ExecuteScalar());
//Get topic_isTerminated ?:
cmd.CommandText = "select topic_isTerminated from [Topics] where [topicId] = '" + topicId + "' ";
int int_topic_isTerminated = Convert.ToInt32(cmd.ExecuteScalar());
//Get tags:
string tagNames = "";
cmd.CommandText = "select count(*) from TagsForTopics where topicId = '" + topicId + "' ";
int totalTags = Convert.ToInt32(cmd.ExecuteScalar());
if (totalTags == 0)
{
tagNames = "There are no tags for the selected topic";
}
for (int i = 1; i <= totalTags; i++)
{
cmd.CommandText = "select [tagId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY tagId ASC), * FROM [TagsForTopics] where topicId = '" + topicId + "') as t where rowNum = '" + i + "'";
string tagId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select tag_name from Tags where tagId = '" + tagId + "' ";
if (totalTags == 1)
{
tagNames = cmd.ExecuteScalar().ToString();
}
else if (totalTags > 1)
{
if (i == 0)
{
tagNames = cmd.ExecuteScalar().ToString();
}
else
{
tagNames = tagNames + ", " + cmd.ExecuteScalar().ToString();
}
}
}
//Create an informative message containing all information for the selected user:
string imagesHTML = "";
if (topic_hasImage == 1)
{
cmd.CommandText = "select count(*) from ImagesForTopics where topicId = '" + topicId + "' ";
int totalImages = Convert.ToInt32(cmd.ExecuteScalar());
for (int i = 1; i <= totalImages; i++)
{
cmd.CommandText = "select [imageId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY imageId ASC), * FROM [ImagesForTopics] where topicId = '" + topicId + "') as t where rowNum = '" + i + "'";
string imageId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select image_name from Images where imageId = '" + imageId + "' ";
string image_name = cmd.ExecuteScalar().ToString();
imagesHTML = imagesHTML + "<img src='../../images/" + image_name + "'></img> <br /><br />";
}
}
lblTopicInformation.Text =
" Title: " + topic_title + "<br />" +
"Created by: " + creator + "<br />" +
" Time: " + Layouts.getTimeFormat(topic_time) + "<br />";
//list of members
cmd.CommandText = "select count(*) from UsersForTopics where topicId = '" + topicId + "' and isApproved = 1";
int totalMembers = Convert.ToInt32(cmd.ExecuteScalar());
string members = "";
if (totalMembers > 0)
{
members = "________________________________________________________<br/> List of current members: <br />";
}
for (int i = 1; i <= totalMembers; i++)
{
cmd.CommandText = "select [userId] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY usersForTopicsId ASC), * FROM [UsersForTopics] where topicId = '" + topicId + "' and isApproved = 1) as t where rowNum = '" + i + "'";
string tempUserId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select [joined_time] from(SELECT rowNum = ROW_NUMBER() OVER(ORDER BY usersForTopicsId ASC), * FROM [UsersForTopics] where topicId = '" + topicId + "' and isApproved = 1) as t where rowNum = '" + i + "'";
string joined_time = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_firstname from Users where userId = '" + tempUserId + "' ";
string temp_name = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select user_lastname from Users where userId = '" + tempUserId + "' ";
temp_name = temp_name + " " + cmd.ExecuteScalar().ToString();
temp_name = "<a href=\"Profile?id=" + tempUserId + "\">" + temp_name + "</a>";
if (!string.IsNullOrWhiteSpace(joined_time))
{
joined_time = Layouts.getTimeFormat(joined_time);
}
members += " (" + temp_name + ") Joined on: " + joined_time + "<br/>";
}
lblTopicInformation.Text += members;
//Short profile of requester
//"Description: \"" + topic_description + "\"<br />" +
//"Tags: \"" + tagNames + "\"<br />" +
//imagesHTML;
lblTopicInformation.Visible = true;
//Copy values to globals:
g_topic_isApproved = int_topic_isApproved; g_topic_isDenied = int_topic_isDenied;
g_creator = creator; g_topic_title = topic_title; g_email = email;
}
else
{
addSession();
Response.Redirect("ApproveJoinTopics");
}
connect.Close();
}
protected bool correctInput()
{
bool correct = true;
if (drpAsRole.SelectedIndex == 0)
{
correct = false;
lblAsRoleError.Text = "Invalid input: Please select a role.";
lblAsRoleError.Visible = true;
}
if (string.IsNullOrEmpty(txtIWantTo.Text))
{
correct = false;
lblIWantToError.Visible = true;
lblIWantToError.Text = "Invalid input: Please type something for \"I want to...\" ";
}
if (string.IsNullOrWhiteSpace(txtSoThat.Text))
{
correct = false;
lblSoThatError.Visible = true;
lblSoThatError.Text = "Invalid input: Please type something for \"So that ...\" ";
}
//Check the start date of the project:
SqlCommand cmd = connect.CreateCommand();
connect.Open();
cmd.CommandText = "select project_startedDate from Projects where projectId = '" + g_projectId + "' ";
DateTime project_startedDate = DateTime.Parse(cmd.ExecuteScalar().ToString());
connect.Close();
int differenceInDays = (calDateIntroduced.SelectedDate - DateTime.Now).Days;
int differenceFromProjectStartDateToDateIntroduced = (calDateIntroduced.SelectedDate - project_startedDate).Days;
int differenceFromProjectStartDateToDateConsidered = (calDateConsidered.SelectedDate - project_startedDate).Days;
if (differenceFromProjectStartDateToDateConsidered < 0)
{
correct = false;
lblDateConsideredError.Visible = true;
lblDateConsideredError.Text = "Invalid input: Please select a date starting after the project's start date (" + Layouts.getTimeFormat(project_startedDate.ToString()) + ").";
}
if (differenceFromProjectStartDateToDateIntroduced < 0)
{
correct = false;
lblDateConsideredError.Visible = true;
lblDateConsideredError.Text = "Please wait until the project's start date (" + Layouts.getTimeFormat(project_startedDate.ToString()) + ").";
}
if (differenceFromProjectStartDateToDateIntroduced > 0 && differenceFromProjectStartDateToDateConsidered > 0)
{
if (differenceInDays < 0)
{
correct = false;
lblDateIntroducedError.Visible = true;
lblDateIntroducedError.Text = "Invalid input: Please select a date starting from now.";
}
differenceInDays = (calDateConsidered.SelectedDate - DateTime.Now).Days;
if (differenceInDays < 0)
{
correct = false;
lblDateConsideredError.Visible = true;
lblDateConsideredError.Text = "Invalid input: Please select a date starting from now.";
}
}
if (drpCurrentStatus.SelectedIndex == 0)
{
correct = false;
lblCurrentStatusError.Visible = true;
lblCurrentStatusError.Text = "Invalid input: Please select a status for this user story.";
}
if (usersToAdd.Count == 0)
{
correct = false;
lblDeveloperResponsibleError.Visible = true;
lblDeveloperResponsibleError.Text = "Invalid input: Please add at least one developer.";
}
return(correct);
}
protected void showTopicInformation()
{
connect.Open();
SqlCommand cmd = connect.CreateCommand();
//Check if the ID exists in the database:
cmd.CommandText = "select count(*) from Complains where complainId = '" + complainId.Replace("'", "''") + "' ";
int countComplain = Convert.ToInt32(cmd.ExecuteScalar());
if (countComplain > 0)//if ID exists, countComplain = 1
{
cmd.CommandText = "select entryId from Complains where complainId = '" + complainId + "' ";
string complain_entryId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select complain_reason from Complains where complainId = '" + complainId + "' ";
string complain_reason = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select complain_FromUser from Complains where complainId = '" + complainId + "' ";
string complain_fromUser = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select complain_time from Complains where complainId = '" + complainId + "' ";
string complain_time = cmd.ExecuteScalar().ToString();
//Get reporter's fullname:
cmd.CommandText = "select (user_firstname + ' ' + user_lastname) from users where userId = '" + complain_fromUser + "' ";
string reporter_name = cmd.ExecuteScalar().ToString();
//Get topics title as a link:
cmd.CommandText = "select topicId from Entries where entryId = '" + complain_entryId + "' ";
string topicId = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select topic_title from Topics where topicId = '" + topicId + "' ";
string temp_topic_title = cmd.ExecuteScalar().ToString();
string topic_title = "<a href=\"ViewTopic.aspx?id=" + topicId + "\">" + temp_topic_title + "</a>";
//Get the original message text:
cmd.CommandText = "select entry_text from Entries where entryId = '" + complain_entryId + "' ";
string entry_text = cmd.ExecuteScalar().ToString();
//Get the creator of the message:
cmd.CommandText = "select userId from Entries where entryId = '" + complain_entryId + "' ";
string creatorId = cmd.ExecuteScalar().ToString();
//Get the creator's name:
cmd.CommandText = "select (user_firstname + ' ' + user_lastname) from Users where userId = '" + creatorId + "' ";
string creator_name = cmd.ExecuteScalar().ToString();
//Get the creator's name:
cmd.CommandText = "select user_email from Users where userId = '" + creatorId + "' ";
string creator_email = cmd.ExecuteScalar().ToString();
lblComplainInformation.Text =
"<table>" +
"<tr><td>Reporter: </td><td>" + reporter_name + "</td></tr>" +
"<tr><td>About message: </td><td>" + entry_text + "</td></tr>" +
"<tr><td>In the topic: </td><td>" + topic_title + "</td></tr>" +
"<tr><td>Complain time: </td><td>" + Layouts.getTimeFormat(complain_time) + "</td></tr>" +
"<tr><td>Reason: </td><td> <div style=\"background: #DCCDCA; padding-left:5px; padding-right:5px; \"> " + complain_reason +
"</table>";
lblComplainInformation.Visible = true;
//Copy values to globals:
g_complain_entryId = complain_entryId;
g_complain_reason = complain_reason;
g_complain_fromUser = complain_fromUser;
g_complain_time = complain_time;
g_reporter_name = reporter_name;
g_topicId = topicId;
g_topic_title = temp_topic_title;
g_entry_text = entry_text;
g_creatorId = creatorId;
g_creator_name = creator_name;
g_email = creator_email;
}
else
{
addSession();
Response.Redirect("ApproveComplains");
}
connect.Close();
}