private void Populate_Habits() { string command = "select id, " + "description as [Description], " + "isvisible as [Show] " + "from habit " + "where description like @filter"; SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("@filter", SqlDbType.NVarChar) { Value = '%' + txt_collectionSearch.Text + '%' } }; dataGrid_content.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders; dataGrid_content.DataSource = db.GenericQueryAction(command, parameter); dataGrid_content.RowHeadersVisible = false; dataGrid_content.Columns[0].Width = 1; dataGrid_content.Columns[0].Visible = false; dataGrid_content.Columns["description"].Width = 353; dataGrid_content.Columns["Description"].DefaultCellStyle.WrapMode = DataGridViewTriState.True; dataGrid_content.Columns["show"].Width = 55; }
public static List <int> GetAllIdInLayer(int _layer, JournalTask.EntryType _mode) { List <int> idInLayer = new List <int>(); DBTools db = new DBTools(Properties.Settings.Default.ConnectionString); string command = ""; if (_mode == JournalTask.EntryType.daily) { command = "select id from currenttasks where layerid = @layerid"; } if (_mode == JournalTask.EntryType.monthly) { command = "select id from monthlytasks where layerid = @layerid"; } if (_mode == JournalTask.EntryType.future) { command = "select id from futuretasks where layerid = @layerid"; } SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("layerid", SqlDbType.Int) { Value = _layer } }; foreach (DataRow row in db.GenericQueryAction(command, parameter).AsEnumerable().ToList()) { idInLayer.Add(row.Field <int>("id")); } return(idInLayer); }
public static List <int> GetFutureTaskIds(int _id) { DBTools db = new DBTools(Properties.Settings.Default.ConnectionString); List <int> returnList = new List <int>(); string command = "select id " + "from futuretasks " + "where previouslayerid = @id"; SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int) { Value = _id } }; DataTable dataTable = db.GenericQueryAction(command, parameters); foreach (DataRow dataRow in dataTable.AsEnumerable().ToList()) { returnList.Add(dataRow.Field <int>("id")); } return(returnList); }
public HabitDescription(JournalTask.EntryMode _entryMode, int _id) { InitializeComponent(); id = _id; entryMode = _entryMode; db = new DBTools(Properties.Settings.Default.ConnectionString); if (entryMode == JournalTask.EntryMode.edit) { this.Text = "Edit Habit"; // Query the category name string command = "select description, " + "isvisible " + "from habit " + "where id = @id"; SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int) { Value = id } }; DataTable dataTable = db.GenericQueryAction(command, parameters); DataRow dataRow = dataTable.AsEnumerable().ToList()[0]; // Set the textbox to the category name txt_currentTaskDescription.Text = dataRow.Field <string>("description"); chk_taskIsVisible.Checked = dataRow.Field <bool>("isvisible"); } }
private void Populate_Contents(int _id, int _layer) { string command = "select " + "a.id, " + "a.notedescription as [Description], " + "count(b.id) as [Contents], " + "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 notes as a " + "left join notes as b " + "on a.id = b.previouslayerid " + "where a.layerid = @layerid " + "and a.notedescription like @filter " + "and a.previouslayerid = @id " + "group by a.id, a.notedescription, " + "format(a.dateadded, 'dd/MM/yyyy, hh:mm:ss tt'), " + "format(a.datechanged, 'dd/MM/yyyy, hh:mm:ss tt') "; SqlParameter[] paramters = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int) { Value = _id }, new SqlParameter("@layerid", SqlDbType.Int) { Value = _layer }, new SqlParameter("@filter", SqlDbType.NVarChar) { Value = '%' + txt_collectionSearch.Text + '%' } }; dataGrid_content.DataSource = db.GenericQueryAction(command, paramters); dataGrid_content.RowHeadersVisible = false; // format grid dataGrid_content.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders; dataGrid_content.Columns[0].Visible = false; dataGrid_content.Columns[0].Width = 1; dataGrid_content.Columns["Description"].Width = 410; dataGrid_content.Columns["Description"].DefaultCellStyle.WrapMode = DataGridViewTriState.True; dataGrid_content.Columns["Contents"].Width = 70; dataGrid_content.Columns["Date Added"].Width = 150; dataGrid_content.Columns["Date Added"].Visible = Properties.Settings.Default.NotesDateAdded; dataGrid_content.Columns["Date Changed"].Width = 150; dataGrid_content.Columns["Date Changed"].Visible = Properties.Settings.Default.NotesDateChanged;; }
public CurrentTaskDescription(JournalTask.EntryMode _entryMode, int _id, int _layer, JournalTask.EntryType _entryType = JournalTask.EntryType.none) { InitializeComponent(); // initialize db db = new DBTools(Properties.Settings.Default.ConnectionString); // store ids id = _id; layer = _layer; // store mode mode = _entryMode; entryType = _entryType; cmb_taskType.SelectedIndex = 0; // Edit Mode if (mode == JournalTask.EntryMode.edit) { this.Text = "Edit Daily Task"; // Query the category name string command = "select description, " + "taskisimportant," + "tasktype " + "from currenttasks " + "where id = @id"; SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int) { Value = id } }; DataTable dataTable = db.GenericQueryAction(command, parameters); DataRow dataRow = dataTable.AsEnumerable().ToList()[0]; // Set the textbox to the category name txt_currentTaskDescription.Text = dataRow.Field <string>("description"); cmb_taskType.SelectedIndex = dataRow.Field <int>("tasktype"); chk_taskIsImportant.Checked = dataRow.Field <bool>("taskisimportant"); } }
public static int GetFutureLayer(int _id) { DBTools db = new DBTools(Properties.Settings.Default.ConnectionString); string queryCommand = "select layerid " + "from futuretasks " + "where id = @id"; SqlParameter[] queryParameter = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int) { Value = _id } }; DataTable dataTable = db.GenericQueryAction(queryCommand, queryParameter); DataRow dataRow = dataTable.AsEnumerable().ToList()[0]; return(dataRow.Field <int>("layerid")); }
public NotesDescription(JournalTask.EntryMode _entryMode, int _id, int _layer) { InitializeComponent(); // initialize db db = new DBTools(Properties.Settings.Default.ConnectionString); // store ids id = _id; layer = _layer; // store mode mode = _entryMode; // Edit Mode if (mode == JournalTask.EntryMode.edit) { this.Text = "Edit Notes"; // Query the category name string command = "select noteDescription " + "from notes " + "where id = @id"; SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int) { Value = id } }; DataTable dataTable = db.GenericQueryAction(command, parameters); DataRow dataRow = dataTable.AsEnumerable().ToList()[0]; // Set the textbox to the category name txt_notes.Text = dataRow.Field <string>("notedescription"); } }
public static int GetPreviousLayerId(int _id, JournalTask.EntryType _entryType) { DBTools db = new DBTools(Properties.Settings.Default.ConnectionString); string command = ""; if (_entryType == JournalTask.EntryType.daily) { command = "select previouslayerid from currenttasks " + "where id = @id"; } if (_entryType == JournalTask.EntryType.monthly) { command = "select previouslayerid from monthlytasks " + "where id = @id"; } if (_entryType == JournalTask.EntryType.future) { command = "select previouslayerid from futuretaks " + "where id = @id"; } SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("id", SqlDbType.Int) { Value = _id } }; DataTable dataTable = db.GenericQueryAction(command, parameter); DataRow row = dataTable.AsEnumerable().ToList()[0]; return(row.Field <int>("previouslayerid")); }
private void Populate_Tracker() { string command = "select id, " + "description as [Description] " + "from habit " + "where isvisible = 1"; SqlParameter[] parameter = new SqlParameter[] { }; dataGrid_tracker.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders; DataTable trackerTable = db.GenericQueryAction(command, parameter); int daysInMonth = DateTime.DaysInMonth(dateTimePicker1.Value.Year, dateTimePicker1.Value.Month) + 1; for (int i = 1; i < daysInMonth; i++) { trackerTable.Columns.Add(new DataColumn(i.ToString(), typeof(bool))); } if (dataGrid_tracker.DataSource != null) { dataGrid_tracker.Columns["Description"].Frozen = false; } dataGrid_tracker.DataSource = trackerTable; dataGrid_tracker.RowHeadersVisible = false; dataGrid_tracker.Columns["id"].Visible = false; dataGrid_tracker.Columns["Description"].Width = 200; dataGrid_tracker.Columns["Description"].DefaultCellStyle.WrapMode = DataGridViewTriState.True; for (int i = 1; i < daysInMonth; i++) { dataGrid_tracker.Columns[i.ToString()].Width = 25; } CheckAllHabits(); dataGrid_tracker.Columns["Description"].Frozen = true; }
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; }
private void Populate_Contents(int _id, int _layer) { string command = "select " + "a.id, " + "a.iscompleted as [Status], " + "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], " + "a.description as [Description], " + "sum(case when b.datecompleted is null and b.id is not null then 1 " + "else 0 end) as [Contents], " + "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 monthlytasks as a " + "left join monthlytasks as b " + "on a.id = b.previouslayerid " + "where a.layerid = @layerid " + "and a.description like @filter " + "and a.previouslayerid = @id " + "and a.datecompleted is null " + "group by a.id, " + "a.iscompleted, " + "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.dateadded, 'dd/MM/yyyy, hh:mm:ss tt'), " + "format(a.datechanged, 'dd/MM/yyyy, hh:mm:ss tt') "; SqlParameter[] paramters = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int) { Value = _id }, new SqlParameter("@layerid", SqlDbType.Int) { Value = _layer }, new SqlParameter("@filter", SqlDbType.NVarChar) { Value = '%' + txt_collectionSearch.Text + '%' } }; dataGrid_content.DataSource = db.GenericQueryAction(command, paramters); dataGrid_content.RowHeadersVisible = false; // format grid dataGrid_content.Columns[0].Visible = false; dataGrid_content.Columns[0].Width = 1; dataGrid_content.Columns["Status"].Width = 50; dataGrid_content.Columns["I"].Width = 30; dataGrid_content.Columns["I"].Visible = Properties.Settings.Default.MonthlyTaskIsImportant; dataGrid_content.Columns["Type"].Width = 60; dataGrid_content.Columns["Type"].Visible = Properties.Settings.Default.MonthlyTaskType; dataGrid_content.Columns["Description"].Width = 360; dataGrid_content.Columns["Description"].DefaultCellStyle.WrapMode = DataGridViewTriState.True; dataGrid_content.Columns["Contents"].Width = 70; dataGrid_content.Columns["Date Added"].Width = 150; dataGrid_content.Columns["Date Added"].Visible = Properties.Settings.Default.MonthlyDateAdded; dataGrid_content.Columns["Date Changed"].Width = 150; dataGrid_content.Columns["Date Changed"].Visible = Properties.Settings.Default.MonthlyDateChanged; }