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;
}