public IEnumerable <ProjectPageViewModel> getProjectNotesPM(String username, int projectId, string roleName)
{
List <ProjectPageViewModel> modelList = new List <ProjectPageViewModel>();
using (SqlConnection connection = new SqlConnection(connString))
using (SqlCommand command = new SqlCommand("", connection))
{
// Query with vals
//command.CommandText = "SELECT * FROM ProjectNote WHERE projectId = @projectId ";
command.CommandText = "SELECT * FROM ProjectNote pn WHERE projectId = @projectId ORDER BY sentDate; ";
command.Parameters.AddWithValue("@projectId", projectId);
command.Parameters.AddWithValue("@userRole", roleName);
// Open connection
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Multiple rows
while (reader.Read())
{
var model = new ProjectPageViewModel();
model.ProjectNotes.ProjectNoteId = Convert.ToInt32(reader["projectNoteId"]);
model.ProjectNotes.ProjectId = Convert.ToInt32(reader["projectId"]);
model.ProjectNotes.Message = reader["message"].ToString();
model.ProjectNotes.SentDate = Convert.ToDateTime(reader["sentDate"]);
model.ProjectNotes.From = reader["from"].ToString();
model.ProjectNotes.To = reader["to"].ToString();
if (reader["completedDate"] != DBNull.Value)
{
model.ProjectNotes.CompletedDate = Convert.ToDateTime(reader["completedDate"]);
}
else
{
model.ProjectNotes.CompletedDate = DateTime.MinValue;
}
modelList.Add(model);
}
reader.Close();
connection.Close();
}
return(modelList);
}
public ProjectNotes getSelectEditProjectNotes(int projectNoteId)
{
ProjectNotes modelList = new ProjectNotes();
using (SqlConnection connection = new SqlConnection(connString))
using (SqlCommand command = new SqlCommand("", connection))
{
// Query with vals
command.CommandText = "SELECT * FROM ProjectNote WHERE projectNoteId = @projectNoteId;";
command.Parameters.AddWithValue("@projectNoteId", projectNoteId);
// Open connection
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Multiple rows
while (reader.Read())
{
var model = new ProjectPageViewModel();
modelList.ProjectNoteId = Convert.ToInt32(reader["projectNoteId"]);
modelList.ProjectId = Convert.ToInt32(reader["projectId"]);
modelList.Message = reader["message"].ToString();
modelList.SentDate = Convert.ToDateTime(reader["sentDate"]);
modelList.From = reader["from"].ToString();
modelList.To = reader["to"].ToString();
if (reader["completedDate"] != DBNull.Value)
{
modelList.CompletedDate = Convert.ToDateTime(reader["completedDate"]);
}
else
{
modelList.CompletedDate = DateTime.MinValue;
}
}
reader.Close();
connection.Close();
}
return(modelList);
}
public ProjectNotes getProjectNotesDetails(int myNoteID)
{
ProjectNotes modelList = new ProjectNotes();
using (SqlConnection connection = new SqlConnection(connString))
using (SqlCommand command = new SqlCommand("", connection))
{
try
{
// Query with vals
command.CommandText = "SELECT * FROM ProjectNote WHERE projectNoteId = @projectNoteId;";
command.Parameters.AddWithValue("@projectNoteId", myNoteID);
// Open connection
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Multiple rows
while (reader.Read())
{
var model = new ProjectPageViewModel();
modelList.ProjectId = model.ProjectNotes.ProjectNoteId = Convert.ToInt32(reader["projectNoteId"]);
modelList.Message = model.ProjectNotes.Message = reader["message"].ToString();
modelList.SentDate = model.ProjectNotes.SentDate = (DateTime)reader["sentDate"];
modelList.From = model.ProjectNotes.From = reader["from"].ToString();
modelList.To = model.ProjectNotes.To = reader["to"].ToString();
//modelList.CompletedDate = model.ProjectNotes.CompletedDate = (DateTime)reader["completedDate"];
}
reader.Close();
connection.Close();
}
catch (Exception e)
{
}
}
return(modelList);
}
public IEnumerable <ProjectPageViewModel> getTaskPM(int projectId)
{
//var model = new Task();
List <ProjectPageViewModel> modelList = new List <ProjectPageViewModel>();
using (SqlConnection connection = new SqlConnection(connString))
using (SqlCommand command = new SqlCommand("", connection))
{
// Query with vals
//command.CommandText = "SELECT * FROM Task t JOIN Project p on t.projectId = p.projectId JOIN User_Task ut on t.taskId = ut.taskId WHERE ut.uName = @username and p.projectId = @projectId ORDER BY cast(T.dueDate as datetime) asc";
//command.CommandText = "select * from Task t JOIN User_Project up ON up.projectId = t.projectId where up.projectId = @projectId";
command.CommandText = "select * from Task t INNER JOIN User_Task ut ON ut.taskId = t.taskId where projectId = @projectId";
command.Parameters.AddWithValue("@projectId", projectId);
// Open connection
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Multiple rows
while (reader.Read())
{
var model = new ProjectPageViewModel();
model.Task.TaskId = Convert.ToInt32(reader["TaskId"]);
model.Task.Title = reader["Title"].ToString();
model.Task.CompletedDate = (DateTime)reader["completionDate"];
model.User.Username = reader["uName"].ToString();
model.Task.DueDate = (DateTime)reader["dueDate"];
model.Task.Priority = reader["priority"].ToString();
model.Task.Description = reader["description"].ToString();
//model.User.Username = reader["uName"].ToString();
modelList.Add(model);
}
reader.Close();
connection.Close();
}
return(modelList);
}
public Task getTaskDetail(int taskId)
{
Task modelList = new Task();
using (SqlConnection connection = new SqlConnection(connString))
using (SqlCommand command = new SqlCommand("", connection))
{
// Query with vals
command.CommandText = "SELECT * FROM Task t JOIN User_Task ut ON t.taskId = ut.taskId WHERE t.taskId = @taskId;";
command.Parameters.AddWithValue("@taskId", taskId);
// Open connection
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Multiple rows
while (reader.Read())
{
var model = new ProjectPageViewModel();
modelList.TaskId = model.Task.TaskId = Convert.ToInt32(reader["TaskId"]);
modelList.Title = model.Task.Title = reader["Title"].ToString();
modelList.CompletedDate = model.Task.CompletedDate = (DateTime)reader["completionDate"];
modelList.StartDate = model.Task.StartDate = (DateTime)reader["startDate"];
modelList.DueDate = model.Task.DueDate = (DateTime)reader["dueDate"];
modelList.Priority = model.Task.Priority = reader["priority"].ToString();
modelList.Description = model.Task.Description = reader["description"].ToString();
modelList.User = model.Task.User = reader["uName"].ToString();
}
reader.Close();
connection.Close();
}
return(modelList);
}
public IEnumerable <ProjectPageViewModel> getDocumentsPM(String username, int projectId, string userRoleName, string uName)
{
//var model = new Task();
//List<ProjectDocumentViewModel> modelList = new List<ProjectDocumentViewModel>();
List <ProjectPageViewModel> modelList = new List <ProjectPageViewModel>();
using (SqlConnection connection = new SqlConnection(connString))
using (SqlCommand command = new SqlCommand("", connection))
{
// Query with vals
//command.CommandText = "select Document.documentId, Document.projectId, Document.fileName, Document.uploader, Document.creationTime, Document.fileExtension, DocumentRole.writeAccess, DocumentRole.roleName from Document LEFT JOIN DocumentRole ON Document.documentId = DocumentRole.documentId WHERE projectId = @projectId; ";
command.CommandText = "select DISTINCT(d.filename), d.projectId, d.fileExtension, d.uploader, d.creationTime, dr.writeAccess, d.documentId, dr.roleName from Document d JOIN DocumentRole dr ON d.documentId = dr.documentId JOIN[User] ON[User].role = dr.roleName where projectId = @projectId AND dr.writeAccess = 1;";
//command.CommandText = "select * from Document where projectId = @projectId";
command.Parameters.AddWithValue("@userRolename", userRoleName);
command.Parameters.AddWithValue("@projectId", projectId);
command.Parameters.AddWithValue("@uName", uName);
// Open connection
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Multiple rows
while (reader.Read())
{
//var model = new ProjectDocumentViewModel();
var model = new ProjectPageViewModel();
var docID = Convert.ToInt32(reader["documentId"]);
model.document.ProjectId = Convert.ToInt32(reader["projectId"]);
model.document.DocumentId = Convert.ToInt32(reader["documentId"]);
model.document.FileName = reader["fileName"].ToString();
model.document.Uploader = reader["uploader"].ToString();
model.document.CreationTime = (DateTime)reader["creationTime"];
model.document.FileExtension = reader["fileExtension"].ToString();
if (reader["roleName"] != DBNull.Value)
{
model.documentRole.RoleName = reader["roleName"].ToString();
}
else
{
model.documentRole.RoleName = "No Role";
}
if (reader["writeAccess"] != DBNull.Value)
{
model.documentRole.WriteAccess = Boolean.Parse(reader["writeAccess"].ToString());
}
else
{
model.documentRole.WriteAccess = false;
}
if (reader["roleName"].ToString() == userRoleName)
{
model.documentRole.WriteAccess = true;
}
modelList.Add(model);
}
reader.Close();
connection.Close();
}
return(modelList);
}