// 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(); }