// select box data refresh.
public void selectBoxDataRefresh(ListBox selectBox, string[] dateStr)
{
int dayItem = (int)selectBox.Items[0];
selectBox.Items.Clear();
selectBox.Items.Insert(0, dayItem);
dbConnect.Open();
SQLiteCommand command = new SQLiteCommand(QueryList.listBoxRefreshSQL(dateStr), dbConnect);
SQLiteDataReader reader = command.ExecuteReader();
int moreCount = 0;
for (int count = 1; reader.Read(); count = count + 1)
{
if (count >= 4)
{
moreCount = moreCount + 1;
}
else
{
selectBox.Items.Insert(count, reader["text"].ToString());
}
}
if (moreCount > 0)
{
selectBox.Items.Insert(4, "(...more " + moreCount + ")");
}
reader.Close();
dbConnect.Close();
}
// EVENT.
private void Button_today_OK_Click(object sender, EventArgs e)
{
int length;
string sql;
length = Encoding.Default.GetBytes(textBox_today_text.Text).Length;
if (!(length <= 20 && length > 0))
{
MessageBox.Show("Invalid input.\nPlease select the correct date.");
return;
}
try {
sql = QueryList.overlapCheckSQL_Today(nUD_t_hour.Value, nUD_t_minute.Value);
if (!overlapCheck(sql))
{
MessageBox.Show("Duplicate alarm time."); return;
}
// insert
sql = QueryList.insertSQL_today(nUD_t_hour.Value, nUD_t_minute.Value, textBox_today_text.Text);
queryActive(sql);
} catch (Exception exc) {
MessageBox.Show("Error : " + exc.Message);
if (dbConnect.State.ToString() == "Open")
{
dbConnect.Close();
}
}
}
// database current day calendar import.
public void calendarListRefresh()
{
dbConnect.Open();
listView_Schedule.Items.Clear();
dbCommand = new SQLiteCommand(QueryList.listviewRefreshSQL(selectYear, selectMonth, selectDay), dbConnect);
SQLiteDataReader reader = dbCommand.ExecuteReader();
while (reader.Read())
{
bool active = (bool)reader["active"];
listView_Schedule.Items.Add(new ListViewItem(new string[] {
((int)reader["sethour"]).ToString("00") + " : " + ((int)reader["setminute"]).ToString("00"),
reader["text"].ToString(),
active ? "Y" : "N"
}));
}
reader.Close();
dbConnect.Close();
button_modifySch.Enabled = false;
button_deleteSch.Enabled = false;
dataview.refreshData();
}
private void deleteMessage()
{
if (MessageBox.Show($"Are you sure you want to delete the data?", "", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
for (int count = listView_allDatalist.Items.Count - 1; count >= 0; count = count - 1)
{
if (listView_allDatalist.Items[count].Selected == true)
{
string[] date_temp = listView_allDatalist.Items[count].Text.ToString().Split('-');
string[] alarm_temp = listView_allDatalist.Items[count].SubItems[1].Text.ToString().Split(':');
int[] date = new int[3]; date[0] = int.Parse(date_temp[0]);
date[1] = int.Parse(date_temp[1]);
date[2] = int.Parse(date_temp[2]);
int[] alarm = new int[2]; alarm[0] = int.Parse(alarm_temp[0]);
alarm[1] = int.Parse(alarm_temp[1]);
connect.Open();
SQLiteCommand command = new SQLiteCommand(QueryList.deleteDateSQL(date[0], date[1], date[2], alarm), connect);
command.ExecuteNonQuery();
connect.Close();
}
}
refreshData();
cmain.changeCalendar();
cmain.calendarListRefresh();
cmain.refreshAlarm();
}
}
public void refreshData()
{
listView_allDatalist.Items.Clear();
groupBox_mode.Text = "Unselected";
connect.Open();
SQLiteCommand command = new SQLiteCommand(QueryList.allDataSQL(), connect);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
bool active = (bool)reader["active"];
string[] items = new string[] {
reader["year"] + "-" + reader["month"] + "-" + reader["day"],
reader["sethour"] + ":" + reader["setminute"],
reader["text"].ToString(),
active ? "O" : "X"
};
listView_allDatalist.Items.Add(new ListViewItem(items));
}
reader.Close();
connect.Close();
button_modify.Enabled = false;
button_delete.Enabled = false;
buttonClickDisableChanged();
}
// select "Modift mode"
public void modifyMode()
{
string sql;
string[] date;
date = new string[3];
date = past_day.ToString("yyyy-M-d").Split('-');
sql = QueryList.overlapMultiCheckSQL(date, past_h, past_m);
connect.Open();
SQLiteCommand command = new SQLiteCommand(sql, connect);
SQLiteDataReader reader = command.ExecuteReader();
if (!reader.Read())
{
MessageBox.Show("Can't find past data.");
reader.Close(); connect.Close();
return;
}
reader.Close(); connect.Close();
// single alarm mode.
if (checkBox_isMulti.Checked == false)
{
sql = QueryList.updateSQL(date, numericUpDown_hour.Value, numericUpDown_minute.Value, textBox_text.Text, checkBox_alarm.Checked, (int)past_h, (int)past_m);
connect.Open();
command = new SQLiteCommand(sql, connect);
command.ExecuteNonQuery();
connect.Close();
}
// multi alarm mode.
else
{
DateTime temp_checkDay = new DateTime(dateTimePicker_start.Value.Ticks);
int dayCount = (DateTime.Parse(dateTimePicker_end.Value.ToString("yyyy-M-d")) - DateTime.Parse(dateTimePicker_start.Value.ToString("yyyy-M-d"))).Days;
for (int count = 0; count <= dayCount; count++, temp_checkDay = temp_checkDay.AddDays(1))
{
date = new string[3];
date = temp_checkDay.ToString("yyyy-M-d").Split('-');
sql = QueryList.updateMultiSQL2(date, numericUpDown_hour.Value, numericUpDown_minute.Value, textBox_text.Text, checkBox_alarm.Checked, past_h, past_m);
connect.Open();
command = new SQLiteCommand(sql, connect);
command.ExecuteNonQuery();
connect.Close();
}
}
// refresh data
refreshData();
cmain.changeCalendar();
cmain.calendarListRefresh();
cmain.refreshAlarm();
}
private void deleteDBdata_today(int index)
{
string[] splitstr = new string[2];
int[] datetemp = new int[2];
splitstr = listView_todayList.Items[index].Text.Split(':');
datetemp[0] = int.Parse(splitstr[0]);
datetemp[1] = int.Parse(splitstr[1]);
dbConnect2.Open();
dbCommand2 = new SQLiteCommand(QueryList.deleteDateSQL_today(datetemp), dbConnect2);
dbCommand2.ExecuteNonQuery();
dbConnect2.Close();
}
// delete select database.
private void deleteDBdata(int index)
{
string[] splitstr = new string[2];
int[] datetemp = new int[2];
splitstr = listView_Schedule.Items[index].Text.Split(':');
datetemp[0] = int.Parse(splitstr[0]);
datetemp[1] = int.Parse(splitstr[1]);
// MessageBox.Show(listView_Schedule.SelectedItems[0].SubItems[1].ToString());
dbConnect.Open();
dbCommand = new SQLiteCommand(QueryList.deleteDateSQL(selectYear, selectMonth, selectDay, datetemp), dbConnect);
dbCommand.ExecuteNonQuery();
dbConnect.Close();
}
// time refresh.
public void nextAlarmReadyRefresh()
{
try {
isAlarmExist = false;
sql = QueryList.nextAlarmImport(DateTime.Now.ToString("yyyy"), DateTime.Now.ToString("MM"), DateTime.Now.ToString("dd"));
dbconnect.Open();
dbcommand = new SQLiteCommand(sql, dbconnect);
reader = dbcommand.ExecuteReader();
while (reader.Read())
{
alarm = new DateTime();
if ((bool)reader["active"] == true)
{
alarm = alarm.AddYears((int)reader["year"] - 1).
AddMonths((int)reader["month"] - 1).
AddDays((int)reader["day"] - 1).
AddHours((int)reader["sethour"]).
AddMinutes((int)reader["setminute"]);
if (alarm < DateTime.Now)
{
continue;
}
isAlarmExist = true;
alarm_text = reader["text"].ToString();
break;
}
}
if (!isAlarmExist)
{
alarm = new DateTime();
alarm = alarm.AddYears(9997);
alarm_text = "alarm disable.";
}
reader.Close();
dbconnect.Close();
todayAlarmChecked();
} catch (Exception exc) { MessageBox.Show(exc.Message); }
}
// database(today) current day calendar import.
public void todayAlarmListRefresh()
{
dbConnect2.Open();
listView_todayList.Items.Clear();
dbCommand2 = new SQLiteCommand(QueryList.listviewTodayRefreshSQL(), dbConnect2);
SQLiteDataReader reader = dbCommand2.ExecuteReader();
while (reader.Read())
{
listView_todayList.Items.Add(new ListViewItem(new string[] {
((int)reader["sethour"]).ToString("00") + " : " + ((int)reader["setminute"]).ToString("00"),
reader["text"].ToString()
}));
}
reader.Close();
dbConnect2.Close();
button_today_modify.Enabled = false;
button_today_delete.Enabled = false;
}
// button Event.
private void button_ok_Click(object sender, EventArgs e)
{
int length;
string sql;
length = Encoding.Default.GetBytes(textBox_calendarText.Text).Length;
if (length <= 20 && length > 0)
{
try {
sql = QueryList.overlapCheckSQL(dateStr);
// add schedule mode.
if (!modifyMode)
{
// overlap alarm check.
if (!overlapCheck(sql, false))
{
return;
}
// insert data. (normal)
if (!checkBox_multiMode.Checked)
{
sql = QueryList.insertSQL(dateStr, numericUpDown_setHour.Value, numericUpDown_setMinute.Value, textBox_calendarText.Text, checkBox_checkAlarm.Checked);
queryActive(sql);
}
// insert data. (multi)
else
{
DateTime temp_nextday = new DateTime(startDateTemp.Ticks);
TimeSpan temp = DateTime.Parse(dateTimePicker_end.Value.ToString("yyyy-MM-dd")) - DateTime.Parse(startDateTemp.ToString("yyyy-MM-dd"));
int dayTemp = temp.Days;
bool oncemessage = true;
if (dayTemp > 0)
{
for (int count = 0; count <= dayTemp; count++, temp_nextday = temp_nextday.AddDays(1))
{
// DB Check
string[] curDateStr = temp_nextday.ToString("yyyy-MM-dd").Split('-');
sql = QueryList.overlapMultiCheckSQL(curDateStr, numericUpDown_setHour.Value, numericUpDown_setMinute.Value);
dbConnect.Open();
SQLiteCommand command = new SQLiteCommand(sql, dbConnect);
SQLiteDataReader reader = command.ExecuteReader();
// data is already exist.
if (reader.Read())
{
reader.Close();
dbConnect.Close();
if (oncemessage)
{
MessageBox.Show("Existing data was maintained due to overlapping schedules.");
}
oncemessage = false;
}
// data is not already exist.
else
{
reader.Close();
dbConnect.Close();
sql = QueryList.insertSQL(curDateStr, numericUpDown_setHour.Value, numericUpDown_setMinute.Value, textBox_calendarText.Text, checkBox_checkAlarm.Checked);
dbConnect.Open();
command = new SQLiteCommand(sql, dbConnect);
command.ExecuteNonQuery();
dbConnect.Close();
}
}
calendar.changeCalendar();
calendar.calendarListRefresh();
calendar.refreshAlarm();
Close();
return;
}
else
{
MessageBox.Show("Invalid input.\nPlease select the correct date."); return;
}
}
}
// modify schedule mode. (normal)
else
{
// overlap alarm check.
if (!overlapCheck(sql, true))
{
return;
}
// update data. (normal)
if (!checkBox_multiMode.Checked)
{
sql = QueryList.updateSQL(dateStr, numericUpDown_setHour.Value, numericUpDown_setMinute.Value, textBox_calendarText.Text, checkBox_checkAlarm.Checked, original_hour, original_minute);
queryActive(sql);
}
// update data. (multi)
else
{
DateTime temp_nextday = new DateTime(startDateTemp.Ticks);
TimeSpan temp = DateTime.Parse(dateTimePicker_end.Value.ToString("yyyy-MM-dd")) - DateTime.Parse(startDateTemp.ToString("yyyy-MM-dd"));
int dayTemp = temp.Days;
if (dayTemp > 0)
{
for (int count = 0; count <= dayTemp; count++, temp_nextday = temp_nextday.AddDays(1))
{
// DB Check
string[] curDateStr = temp_nextday.ToString("yyyy-MM-dd").Split('-');
sql = QueryList.overlapMultiCheckSQL(curDateStr, numericUpDown_setHour.Value, numericUpDown_setMinute.Value);
dbConnect.Open();
SQLiteCommand command = new SQLiteCommand(sql, dbConnect);
SQLiteDataReader reader = command.ExecuteReader();
// data is already exist.
if (reader.Read())
{
reader.Close();
dbConnect.Close();
sql = QueryList.updateMultiSQL(curDateStr, numericUpDown_setHour.Value, numericUpDown_setMinute.Value, textBox_calendarText.Text, checkBox_checkAlarm.Checked);
dbConnect.Open();
command = new SQLiteCommand(sql, dbConnect);
command.ExecuteNonQuery();
dbConnect.Close();
}
// data is not already exist.
else
{
reader.Close(); dbConnect.Close();
}
}
}
calendar.changeCalendar();
calendar.calendarListRefresh();
calendar.refreshAlarm();
Close();
return;
}
}
}
catch (Exception exc) {
MessageBox.Show("Error : " + exc.Message);
if (dbConnect.State.ToString() == "Open")
{
dbConnect.Close();
}
}
}
else if (length <= 0)
{
MessageBox.Show("You didn't enter anything!");
}
else
{
MessageBox.Show("Character size must be no larger than 20.");
}
}
// today alarm check.
public void todayAlarmChecked()
{
DateTime today = new DateTime();
DateTime current = DateTime.Now;
bool findToday = false;
try {
sql = QueryList.listviewTodayRefreshSQL();
dbconnect2.Open();
dbcommand = new SQLiteCommand(sql, dbconnect2);
reader = dbcommand.ExecuteReader();
// Today alarm confirm.
while (reader.Read())
{
today = new DateTime();
today = today.AddYears(current.Year - 1).AddMonths(current.Month - 1).AddDays(current.Day - 1).
AddHours((int)reader["sethour"]).AddMinutes((int)reader["setminute"]);
if (today <= current)
{
continue;
}
else if (today > current)
{
findToday = true;
if (today >= alarm)
{
break;
}
else if (today < alarm)
{
alarm = today;
alarm_text = reader["text"].ToString();
break;
}
}
}
reader.Close();
// Next day confirm.
if (!findToday)
{
reader = dbcommand.ExecuteReader();
if (reader.Read())
{
today = new DateTime();
today = today.AddYears(current.Year - 1).AddMonths(current.Month - 1).AddDays(current.Day).
AddHours((int)reader["sethour"]).AddMinutes((int)reader["setminute"]);
if (today <= current)
{
}
else if (today > current)
{
if (today >= alarm)
{
}
else if (today < alarm)
{
alarm = today;
alarm_text = reader["text"].ToString();
}
}
}
reader.Close();
}
dbconnect2.Close();
MessageBox.Show("alarm : " + alarm.Year + "." + alarm.Month + "." + alarm.Day + " " + alarm.Hour + ":" + alarm.Minute);
} catch (Exception exc) { MessageBox.Show(exc.Message); }
}
// main method.
public CalendarMain()
{
InitializeComponent();
alarm_onCheck = true;
gbox = new ListBox[42];
addForm = new DataAddForm(label_DateTemp, this, false);
addForm_today = new TodayDataAddForm(this);
// Database setting.
dbConnect = new SQLiteConnection("Data Source=" + path + dbFileName + ";Version=3;");
dbConnect2 = new SQLiteConnection("Data Source=" + path + dbFileName2 + ";Version=3;");
addForm.setDbConnect(dbConnect);
addForm_today.setDbConnect(dbConnect2);
if (!File.Exists(path + dbFileName))
{
dbCommand = new SQLiteCommand(QueryList.createTableSQL(), dbConnect);
Directory.CreateDirectory(path);
SQLiteConnection.CreateFile(path + dbFileName);
MessageBox.Show("Created new calendar db.");
dbConnect.Open();
dbCommand.ExecuteNonQuery();
dbConnect.Close();
}
if (!File.Exists(path + dbFileName2))
{
dbCommand2 = new SQLiteCommand(QueryList.createTableSQL_today(), dbConnect2);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
SQLiteConnection.CreateFile(path + dbFileName2);
MessageBox.Show("Created new today alarm db.");
dbConnect2.Open();
dbCommand2.ExecuteNonQuery();
dbConnect2.Close();
}
tManager = new ThreadManager(label_Time, dbConnect, dbConnect2); // Thread setting.
dataview = new DataView(dbConnect, this); // DataView setting.
// Panel setting.
for (int count = 0; count < gbox.Length; count++)
{
gbox[count] = new ListBox();
}
// Current date setting.
selectDay = int.Parse(DateTime.Now.ToString("dd"));
selectYear = int.Parse(DateTime.Now.ToString("yyyy"));
selectMonth = int.Parse(DateTime.Now.ToString("MM"));
for (int row = 1, count = 0; row <= 6; row++)
{
for (int col = 0; col < 7; col++)
{
panel_MonthList.Controls.Add(gbox[count], col, row); count++;
}
}
label_DateTemp.Text = selectYear.ToString() + "." + selectMonth.ToString() + "." + selectDay.ToString();
label_YearMonth.Text = selectYear.ToString() + "." + selectMonth.ToString("00");
changeCalendar();
todayAlarmListRefresh();
}
// calendar diary set Method.
private void settingCalendar()
{
int maxDays = int.Parse(DateTime.DaysInMonth(selectYear, selectMonth).ToString());
int blankCount, tempCt;
DateTime dOfMonth = new DateTime();
dOfMonth = dOfMonth.AddYears(selectYear - 1).AddMonths(selectMonth - 1);
switch (dOfMonth.DayOfWeek.ToString())
{
case "Sunday": blankCount = 7; break;
case "Monday": blankCount = 1; break;
case "Tuesday": blankCount = 2; break;
case "Wednesday": blankCount = 3; break;
case "Thursday": blankCount = 4; break;
case "Friday": blankCount = 5; break;
default: blankCount = 6; break;
}
tempCt = blankCount - 1;
dbConnect.Open();
for (int row = 1, boxCount = 0, dayCount = 1; row <= 6; row = row + 1)
{
for (int col = 0; col < 7; col = col + 1)
{
if (blankCount > 0 || dayCount > maxDays)
{
gbox[boxCount].BackColor = System.Drawing.SystemColors.InactiveCaptionText;
gbox[boxCount].TabStop = false;
blankCount = blankCount - 1;
}
else
{
string[] dateStr = new string[3];
// Calender Panel color setting.
if (DateTime.Now.ToString("yyyy-MM") == (selectYear.ToString() + "-" + selectMonth.ToString("00")) && dayCount == int.Parse(DateTime.Now.ToString("dd")))
{
gbox[dayCount + tempCt].BackColor = System.Drawing.Color.FromArgb(192, 255, 255);
}
else if (dOfMonth.DayOfWeek.ToString() == "Sunday")
{
gbox[boxCount].BackColor = System.Drawing.Color.FromArgb(255, 192, 192);
}
else if (dOfMonth.DayOfWeek.ToString() == "Saturday")
{
gbox[boxCount].BackColor = System.Drawing.Color.FromArgb(192, 192, 255);
}
else
{
gbox[boxCount].BackColor = System.Drawing.SystemColors.Window;
}
// database setting.
dateStr = dOfMonth.ToString("yyyy-MM-dd").Split('-');
dateStr[1] = int.Parse(dateStr[1]).ToString();
dateStr[2] = int.Parse(dateStr[2]).ToString();
dbCommand = new SQLiteCommand(QueryList.listBoxRefreshSQL(dateStr), dbConnect);
SQLiteDataReader reader = dbCommand.ExecuteReader();
gbox[boxCount].Items.Insert(0, dayCount);
int moreCount = 0;
for (int count = 1; reader.Read(); count = count + 1)
{
if (count >= 4)
{
moreCount = moreCount + 1;
}
else
{
gbox[boxCount].Items.Insert(count, reader["text"].ToString());
}
}
if (moreCount > 0)
{
gbox[boxCount].Items.Insert(4, "(...more " + moreCount + ")");
}
reader.Close();
gbox[boxCount].TabStop = true;
dayCount = dayCount + 1;
dOfMonth = dOfMonth.AddDays(1);
}
gbox[boxCount].Enabled = false;
boxCount = boxCount + 1;
}
}
dbConnect.Close();
gbox_index = selectDay + tempCt;
gbox[gbox_index].BackColor = System.Drawing.Color.FromArgb(255, 255, 192);
//MessageBox.Show("Refresh Database");
dataview.refreshData();
}
/*** Method List. ***/
// select "Add mode"
public void addMode()
{
string sql;
string[] date;
SQLiteCommand command;
date = new string[3];
date = (this.dateTimePicker_start.Value.ToString("yyyy-M-d")).Split('-');
// duplicate check.
sql = QueryList.overlapMultiCheckSQL(date, numericUpDown_hour.Value, numericUpDown_minute.Value);
connect.Open();
command = new SQLiteCommand(sql, connect);
SQLiteDataReader reader = command.ExecuteReader();
if (reader.Read())
{
MessageBox.Show("Duplicate alarm time.");
reader.Close(); connect.Close(); return;
}
reader.Close();
connect.Close();
/* normal mode */
if (this.checkBox_isMulti.Checked == false)
{
// input data.
sql = QueryList.insertSQL(date, numericUpDown_hour.Value, numericUpDown_minute.Value, textBox_text.Text, checkBox_alarm.Checked);
connect.Open();
command = new SQLiteCommand(sql, connect);
command.ExecuteNonQuery();
connect.Close();
}
/* multi mode */
else
{
DateTime temp_nextday = new DateTime(dateTimePicker_start.Value.Ticks);
TimeSpan temp = DateTime.Parse(dateTimePicker_end.Value.ToString("yyyy-M-d")) - DateTime.Parse(dateTimePicker_start.Value.ToString("yyyy-M-d"));
int dayTemp = temp.Days;
bool oncemessage = true;
// input data (multi).
for (int count = 0; count <= dayTemp; count++, temp_nextday = temp_nextday.AddDays(1))
{
date = new string[3];
date = temp_nextday.ToString("yyyy-M-d").Split('-');
sql = QueryList.overlapMultiCheckSQL(date, numericUpDown_hour.Value, numericUpDown_minute.Value);
connect.Open();
command = new SQLiteCommand(sql, connect);
reader = command.ExecuteReader();
// data is already exist.
if (reader.Read())
{
reader.Close(); connect.Close();
if (oncemessage)
{
MessageBox.Show("Existing data was maintained due to overlapping schedules."); oncemessage = false;
}
}
// data is not already exist.
else
{
reader.Close(); connect.Close();
sql = QueryList.insertSQL(date, numericUpDown_hour.Value, numericUpDown_minute.Value, textBox_text.Text, checkBox_alarm.Checked);
connect.Open(); command = new SQLiteCommand(sql, connect);
command.ExecuteNonQuery();
connect.Close();
}
}
}
// refresh data
refreshData();
cmain.changeCalendar();
cmain.calendarListRefresh();
cmain.refreshAlarm();
}
