public WeeklyReport(int projectID, int archivedMode) { this.ArchivedMode = archivedMode; this.ProjectID = projectID; InitializeComponent(); if (this.ArchivedMode == 1) { IEnumerable <Button> collection = MainWRGrid.Children.OfType <Button>(); foreach (var button in collection) { button.Background = new SolidColorBrush(Colors.GhostWhite); } } //Now gather the project data into the weekly report string weeklyReportText = ""; string currentType = ""; using ( SqlConnection conn = new SqlConnection( "Server=(LocalDB)\\MSSQLLocalDB;Database=Project_Notes;Integrated Security = true")) { conn.Open(); //insert log, the creation_date is added by default string sql = String.Format(@" DECLARE @project_title NVARCHAR(50); DECLARE @PROJECT_NUMBER NVARCHAR(50); DECLARE @NOTE_TITLE NVARCHAR(50); DECLARE @NOTE_CONTENT NVARCHAR(400); DECLARE @TASK_TITLE NVARCHAR(50); DECLARE @TASK_DESCRIPTION NVARCHAR(400); DECLARE @CREATION_DATE DATE; DECLARE @LOG_NOTE NVARCHAR(400); DECLARE @project_id int; DECLARE @task_id int; DECLARE @sqlCommand varchar(1000) DECLARE @FetchStatus int DECLARE project_cursor CURSOR FOR SELECT ID,PROJECT_TITLE,PROJECT_NUMBER FROM PROJECT WHERE ID = @projectId; OPEN project_cursor FETCH NEXT FROM project_cursor INTO @project_id, @project_title, @project_number WHILE @@FETCH_STATUS = 0 BEGIN SELECT 'PROJECT',@PROJECT_TITLE AS 'PROJECT_TITLE',@PROJECT_NUMBER AS 'PROJECT_NUMBER' SELECT 'NOTE',NOTE_TITLE,NOTE_CONTENT FROM NOTES WHERE PROJECT_ID = @PROJECT_ID DECLARE TASK_cursor CURSOR FOR SELECT ID,TASK_TITLE,TASK_DESCRIPTION,CREATION_DATE FROM TASKS WHERE PROJECT_ID = @PROJECT_ID; OPEN TASK_CURSOR FETCH NEXT FROM TASK_cursor INTO @TASK_ID,@TASK_TITLE,@TASK_DESCRIPTION,@CREATION_DATE WHILE @@FETCH_STATUS = 0 BEGIN SELECT 'TASK',@TASK_TITLE AS 'TASK_TITLE',@TASK_DESCRIPTION AS 'TASK_DESCRIPTION' WHERE @CREATION_DATE > (SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)); SELECT 'LOG',LOG_NOTE FROM [Project_Notes].[dbo].[LOGS] WHERE Task_Id = @TASK_ID AND CREATION_DATE > (SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)) ORDER BY CREATION_DATE DESC; FETCH NEXT FROM TASK_cursor INTO @TASK_ID,@TASK_TITLE,@TASK_DESCRIPTION,@CREATION_DATE END CLOSE TASK_CURSOR DEALLOCATE TASK_CURSOR FETCH NEXT FROM project_cursor INTO @project_id, @project_title, @project_number END CLOSE project_cursor; DEALLOCATE project_cursor; "); using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add("@projectId", this.ProjectID); SqlDataReader reader = cmd.ExecuteReader(); do { while (reader.Read()) { object[] colVals = new object[4]; reader.GetValues(colVals); for (int i = 0; i < 4; i++) { if (colVals[i] != null) { colVals[i] = colVals[i].ToString().Replace("\r\n", ""); if (i == 0) { if (colVals[i].ToString() == "PROJECT") { weeklyReportText += "\nProject: "; currentType = "PROJECT"; } else if (colVals[i].ToString() == "NOTE") { weeklyReportText += "Note: "; currentType = "NOTE"; } else if (colVals[i].ToString() == "TASK") { weeklyReportText += "\n\tTask: "; currentType = "TASK"; } else if (colVals[i].ToString() == "LOG") { weeklyReportText += "\n\t\t\tLog: "; currentType = "LOG"; } } else { if (i == 2) { weeklyReportText += "\n\t"; if (currentType == "NOTE" || currentType == "TASK") { weeklyReportText += "\t"; } } if (i == 1) { weeklyReportText += ""; } if (currentType == "PROJECT") { } else if (currentType == "NOTE") { if (i == 1) { weeklyReportText += "Title: "; } else if (i == 2) { weeklyReportText += "Description: "; } } else if (currentType == "TASK") { if (i == 2) { weeklyReportText += "Description: "; } } else if (currentType == "LOG") { } if (!(currentType == "PROJECT" && i == 2)) { weeklyReportText += colVals[i].ToString().Replace("\r\n\r\n", ""); } else { int p = 5; } } } } } }while (reader.NextResult()); } } WeeklyReportTextbox.AppendText(weeklyReportText); this.Width = SystemParameters.WorkArea.Width; this.Height = SystemParameters.WorkArea.Height; }
private void Calendar_OnSelectedDatesChanged(object sender, SelectionChangedEventArgs e) { WeeklyReportTextbox.Document.Blocks.Clear(); var calendar = sender as Calendar; DateTime startDate = calendar.SelectedDate.Value; int sn = calendar.SelectedDates.Count; DateTime endDate = startDate.AddDays(sn - 1); string weeklyReportText = ""; string currentType = ""; using ( SqlConnection conn = new SqlConnection( "Server=(LocalDB)\\MSSQLLocalDB;Database=Project_Notes;Integrated Security = true")) { conn.Open(); //insert log, the creation_date is added by default string sql = String.Format(@" DECLARE @project_title NVARCHAR(50); DECLARE @PROJECT_NUMBER NVARCHAR(50); DECLARE @NOTE_TITLE NVARCHAR(50); DECLARE @NOTE_CONTENT NVARCHAR(400); DECLARE @TASK_TITLE NVARCHAR(50); DECLARE @TASK_DESCRIPTION NVARCHAR(400); DECLARE @CREATION_DATE DATE; DECLARE @LOG_NOTE NVARCHAR(400); DECLARE @project_id int; DECLARE @task_id int; DECLARE @sqlCommand varchar(1000) DECLARE @FetchStatus int DECLARE project_cursor CURSOR FOR SELECT ID,PROJECT_TITLE,PROJECT_NUMBER FROM PROJECT WHERE ID = @projectId; OPEN project_cursor FETCH NEXT FROM project_cursor INTO @project_id, @project_title, @project_number WHILE @@FETCH_STATUS = 0 BEGIN SELECT 'PROJECT',@PROJECT_TITLE AS 'PROJECT_TITLE',@PROJECT_NUMBER AS 'PROJECT_NUMBER' SELECT 'NOTE',NOTE_TITLE,NOTE_CONTENT FROM NOTES WHERE PROJECT_ID = @PROJECT_ID DECLARE TASK_cursor CURSOR FOR SELECT ID,TASK_TITLE,TASK_DESCRIPTION,CREATION_DATE FROM TASKS WHERE PROJECT_ID = @PROJECT_ID; OPEN TASK_CURSOR FETCH NEXT FROM TASK_cursor INTO @TASK_ID,@TASK_TITLE,@TASK_DESCRIPTION,@CREATION_DATE WHILE @@FETCH_STATUS = 0 BEGIN SELECT 'TASK',@TASK_TITLE AS 'TASK_TITLE',@TASK_DESCRIPTION AS 'TASK_DESCRIPTION' WHERE @CREATION_DATE > (SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)); SELECT 'LOG',LOG_NOTE FROM [Project_Notes].[dbo].[LOGS] WHERE Task_Id = @TASK_ID AND CREATION_DATE > @startDate and CREATION_DATE < @endDate ORDER BY CREATION_DATE DESC; FETCH NEXT FROM TASK_cursor INTO @TASK_ID,@TASK_TITLE,@TASK_DESCRIPTION,@CREATION_DATE END CLOSE TASK_CURSOR DEALLOCATE TASK_CURSOR FETCH NEXT FROM project_cursor INTO @project_id, @project_title, @project_number END CLOSE project_cursor; DEALLOCATE project_cursor; "); using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add("@projectId", this.ProjectID); cmd.Parameters.Add("@startDate", startDate.ToString("yyyy-MM-dd hh:mm:ss")); cmd.Parameters.Add("@endDate", endDate.ToString("yyyy-MM-dd hh:mm:ss")); SqlDataReader reader = cmd.ExecuteReader(); do { while (reader.Read()) { object[] colVals = new object[4]; reader.GetValues(colVals); for (int i = 0; i < 4; i++) { if (colVals[i] != null) { colVals[i] = colVals[i].ToString().Replace("\r\n", ""); if (i == 0) { if (colVals[i].ToString() == "PROJECT") { weeklyReportText += "\nProject: "; currentType = "PROJECT"; } else if (colVals[i].ToString() == "NOTE") { weeklyReportText += "Note: "; currentType = "NOTE"; } else if (colVals[i].ToString() == "TASK") { weeklyReportText += "\n\tTask: "; currentType = "TASK"; } else if (colVals[i].ToString() == "LOG") { weeklyReportText += "\n\t\t\tLog: "; currentType = "LOG"; } } else { if (i == 2) { weeklyReportText += "\n\t"; if (currentType == "NOTE" || currentType == "TASK") { weeklyReportText += "\t"; } } if (i == 1) { weeklyReportText += ""; } if (currentType == "PROJECT") { } else if (currentType == "NOTE") { if (i == 1) { weeklyReportText += "Title: "; } else if (i == 2) { weeklyReportText += "Description: "; } } else if (currentType == "TASK") { if (i == 2) { weeklyReportText += "Description: "; } } else if (currentType == "LOG") { } if (!(currentType == "PROJECT" && i == 2)) { weeklyReportText += colVals[i].ToString().Replace("\r\n\r\n", ""); } else { int p = 5; } } } } } } while (reader.NextResult()); } } WeeklyReportTextbox.AppendText(weeklyReportText); }