Beispiel #1
0
        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);
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        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);
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        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);
        }
Beispiel #6
0
        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);
        }