private void Populate_DailyTasks() { List <int> layerIds = JournalTask.GetAllIdInLayer(layer, entryType); List <int> validIds = new List <int>(); string command = "select count(*) from currenttasks " + "where id = @id " + "and datecompleted is not null"; foreach (int layerId in layerIds) { List <int> taskIds = JournalTask.GetAllCurrentTasksId(layerId); int contentCounter = 0; foreach (int taskId in taskIds) { SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int) { Value = taskId } }; if (db.GenericQueryAction(command, parameter).AsEnumerable().ToList()[0].Field <int>(0) > 0) { contentCounter++; } } if (contentCounter != 0) { validIds.Add(layerId); } } command = "select " + "a.id, " + "case " + "when a.taskisimportant = 1 " + "then '*' " + "else '' end as [I], " + "case " + "when a.tasktype = 0 then 'TASK' " + "when a.tasktype = 1 then 'EVENT' " + "when a.tasktype = 2 then 'NOTES' " + "else 'CLOSED' end as [Type], " + "case when a.iscompleted = 1 then 'Completed' " + "else 'Incomplete' end as [Status], " + "a.description as [Description], " + "sum(case when b.datecompleted is not null and b.id is not null then 1 " + "else 0 end) as [Contents], " + "format(a.datecompleted, 'dd/MM/yyyy, hh:mm:ss tt') as [Date Completed], " + "format(a.dateadded, 'dd/MM/yyyy, hh:mm:ss tt') as [Date Added], " + "format(a.datechanged, 'dd/MM/yyyy, hh:mm:ss tt') as [Date Changed] " + "from currenttasks as a " + "left join currenttasks as b " + "on a.id = b.previouslayerid " + "where a.layerid = @layerid " + "and a.id in ({0}) " + "and a.description like @filter " + "and a.previouslayerid = case " + "when @layerid = 0 " + "then a.previouslayerid " + "else @currentid end " + "group by a.id, " + "case when a.iscompleted = 1 then 'Completed' " + "else 'Incomplete' end, " + "a.description, " + "case " + "when a.tasktype = 0 then 'TASK' " + "when a.tasktype = 1 then 'EVENT' " + "when a.tasktype = 2 then 'NOTES' " + "else 'CLOSED' end, " + "case " + "when a.taskisimportant = 1 " + "then '*' " + "else '' end, " + "a.description, " + "format(a.datecompleted, 'dd/MM/yyyy, hh:mm:ss tt'), " + "format(a.dateadded, 'dd/MM/yyyy, hh:mm:ss tt'), " + "format(a.datechanged, 'dd/MM/yyyy, hh:mm:ss tt')"; command = String.Format(command, String.Join(",", validIds.Count > 0 ? validIds : new List <int>() { -1 })); SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@layerid", SqlDbType.Int) { Value = layer }, new SqlParameter("@currentId", SqlDbType.Int) { Value = currentId }, new SqlParameter("@filter", SqlDbType.NVarChar) { Value = '%' + txt_historySearch.Text + '%' } }; dataGrid_content.Columns["btn_undo"].Width = 70; dataGrid_content.DataSource = db.GenericQueryAction(command, parameters); dataGrid_content.Columns["id"].Visible = false; dataGrid_content.Columns["id"].Width = 1; dataGrid_content.Columns["Status"].Width = 70; dataGrid_content.Columns["I"].Width = 30; dataGrid_content.Columns["I"].Visible = Properties.Settings.Default.DailyTaskIsImportant; dataGrid_content.Columns["Type"].Width = 60; dataGrid_content.Columns["Type"].Visible = Properties.Settings.Default.DailyTaskType; dataGrid_content.Columns["Description"].Width = 400; dataGrid_content.Columns["Description"].DefaultCellStyle.WrapMode = DataGridViewTriState.True; dataGrid_content.Columns["Contents"].Width = 70; dataGrid_content.Columns["Date Completed"].Width = 150; dataGrid_content.Columns["Date Added"].Width = 150; dataGrid_content.Columns["Date Added"].Visible = Properties.Settings.Default.DailyDateAdded; dataGrid_content.Columns["Date Changed"].Width = 150; dataGrid_content.Columns["Date Changed"].Visible = Properties.Settings.Default.DailyDateChanged; }