public string AddTaskName(int task_id, int i)
{
string query = "";
string taskname = "";
switch (i)
{
case 1:
query = "SELECT name FROM MainCategory WHERE id = '" + task_id + "'";
break;
case 2:
query = "SELECT name FROM MiddleCategory WHERE id = '" + task_id + "'";
break;
case 3:
query = "SELECT name FROM SubCategory WHERE id = '" + task_id + "'";
break;
}
MySqlDataReader rdr = DailyTaskDBManager.GetInstace().Select(query);
while (rdr.Read())
{
taskname = (string)rdr["name"];
}
rdr.Close();
return(taskname);
}
private void comboBoxMiddleCateMod_SelectedIndexChanged(object sender, EventArgs e)
{
comboBoxSubCateMod.Items.Clear();
string query = "SELECT id, Mid_maincategory_id FROM MiddleCategory WHERE name = '" + comboBoxMiddleCateMod.Text + "'";
MySqlDataReader rdr = DailyTaskDBManager.GetInstace().Select(query);
int main_id = 0;
int middle_id = 0;
while (rdr.Read())
{
main_id = (int)rdr["Mid_maincategory_id"];
middle_id = (int)rdr["id"];
}
rdr.Close();
string query_sub = "SELECT name FROM SubCategory WHERE maincategory_id = '" + main_id + "' AND middlecategory_id = '" + middle_id + "'";
MySqlDataReader rdr_m = DailyTaskDBManager.GetInstace().Select(query_sub);
while (rdr_m.Read())
{
string subcategory = (string)rdr_m["name"];
comboBoxSubCateMod.Items.Add(subcategory);
}
rdr_m.Close();
}
public void initWork()
{
string query_main = "SELECT name FROM MainCategory";
MySqlDataReader rdr_m = DailyTaskDBManager.GetInstace().Select(query_main);
while (rdr_m.Read())
{
string maincategory = (string)rdr_m["name"];
comboBoxMainCateMod.Items.Add(maincategory);
}
rdr_m.Close();
}
public void initWork()
{
/*
* string query = "SELECT MainCategory.name, MiddleCategory.name FROM MainCategory " +
* "join MiddleCategory ON MainCategory.id = MiddleCategory.Mid_maincategory_id;";
*/
string query_main = "SELECT name FROM MainCategory";
MySqlDataReader rdr_m = DailyTaskDBManager.GetInstace().Select(query_main);
while (rdr_m.Read())
{
string maincategory = (string)rdr_m["name"];
comboBoxMainCate.Items.Add(maincategory);
}
rdr_m.Close();
}
public void ModWork()
{
FrmDailyAdd form2 = new FrmDailyAdd();
int indexnum = Convert.ToInt32(form1.listViewWorkList.FocusedItem.Text);
string maincategory = comboBoxMainCateMod.Text;
string middlecategory = comboBoxMiddleCateMod.Text;
string subcategory = comboBoxSubCateMod.Text;
int main_id = 0;
int middle_id = 0;
int sub_id = 0;
string query_id = "SELECT id, maincategory_id, middlecategory_id FROM SubCategory WHERE name = '" + comboBoxSubCateMod.Text + "'";
MySqlDataReader rdr = DailyTaskDBManager.GetInstace().Select(query_id);
while (rdr.Read())
{
main_id = (int)rdr["maincategory_id"];
middle_id = (int)rdr["middlecategory_id"];
sub_id = (int)rdr["id"];
}
rdr.Close();
string query = "UPDATE Task SET Task_maincategory_id = @main_id, Task_middlecategory_id = " +
"@middle_id, Task_subcategory_id = @sub_id WHERE id='" + indexnum + "'";
if (maincategory == "대분류" || middlecategory == "중분류" || subcategory == "소분류")
{
MessageBox.Show("항목을 수정해 주세요");
}
else
{
DailyTaskDBManager.GetInstace().Update(query, main_id, middle_id, sub_id);
this.Close();
}
}
public List <WorkCategory> SearchLoad()
{
List <WorkCategory> worklist = new List <WorkCategory>();
string search_date = dateTimePickerSearchWork.Value.ToString("yyyy-MM-dd");
string keyword = textBoxInputKeyword.Text;
string query = "";
if (keyword == "")
{
query = "SELECT * FROM Task WHERE date LIKE'%" + search_date + "%'";
}
else
{
query = "SELECT * FROM Task WHERE date LIKE'%" + search_date + "%'" +
"AND Task_maincategory_id IN (SELECT id FROM MainCategory WHERE name LIKE '%" + keyword + "%') " +
"OR Task_middlecategory_id IN (SELECT id FROM MiddleCategory WHERE name LIKE '%" + keyword + "%') " +
"OR Task_subcategory_id IN (SELECT id FROM SubCategory WHERE name LIKE '%" + keyword + "%') ";
}
MySqlDataReader rdr = DailyTaskDBManager.GetInstace().Select(query);
while (rdr.Read())
{
WorkCategory workcategory = new WorkCategory();
workcategory.id = (int)rdr["id"];
workcategory.maindcategory_id = (int)rdr["Task_maincategory_id"];
workcategory.middlecategory_id = (int)rdr["Task_middlecategory_id"];
workcategory.subcategory_id = (int)rdr["Task_subcategory_id"];
workcategory.start_time = (string)rdr["taskstarttime"];
workcategory.end_time = (string)rdr["taskendtime"];
workcategory.day = (DateTime)rdr["date"];
worklist.Add(workcategory);
}
rdr.Close();
return(worklist);
}
private void buttonWorkDel_Click(object sender, EventArgs e)
{
FrmDailyAdd form2 = new FrmDailyAdd();
int indexnum = 0;
if (listViewWorkList.SelectedIndices.Count == 0)
{
MessageBox.Show("항목을 선택해 주세요");
}
else
{
if (MessageBox.Show("선택하신 업무가 삭제됩니다", "업무 삭제", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
indexnum = Convert.ToInt32(listViewWorkList.FocusedItem.Text);
string query = "DELETE FROM Task WHERE id = '" + indexnum + "'";
DailyTaskDBManager.GetInstace().DBquery(query);
DelList(indexnum);
}
else
{
MessageBox.Show("삭제를 취소하셨습니다.");
}
}
}
public List <WorkCategory> LoadWork()
{
List <WorkCategory> worklist = new List <WorkCategory>();
string query = "SELECT * FROM Task";
MySqlDataReader rdr = DailyTaskDBManager.GetInstace().Select(query);
while (rdr.Read())
{
WorkCategory workcategory = new WorkCategory();
workcategory.id = (int)rdr["id"];
workcategory.maindcategory_id = (int)rdr["Task_maincategory_id"];
workcategory.middlecategory_id = (int)rdr["Task_middlecategory_id"];
workcategory.subcategory_id = (int)rdr["Task_subcategory_id"];
workcategory.start_time = (string)rdr["taskstarttime"];
workcategory.end_time = (string)rdr["taskendtime"];
workcategory.day = (DateTime)rdr["date"];
//workcategory.day = (string)rdr["date"];
worklist.Add(workcategory);
}
rdr.Close();
return(worklist);
}
public void AddWork()
{
var day = dateTimePickerInsertWork.Value.ToString("yyyy-MM-dd");
var start_time = dateTimePickerStartTime.Text;
var end_time = dateTimePickerEndTime.Text;
var maincategory = comboBoxMainCate.Text;
var middlecategory = comboBoxMiddleCate.Text;
var subcategory = comboBoxSubCate.Text;
int main_id = 0;
int middle_id = 0;
int sub_id = 0;
string query_id = "SELECT id, maincategory_id, middlecategory_id FROM SubCategory WHERE name = '" + comboBoxSubCate.Text + "'";
MySqlDataReader rdr = DailyTaskDBManager.GetInstace().Select(query_id);
while (rdr.Read())
{
main_id = (int)rdr["maincategory_id"];
middle_id = (int)rdr["middlecategory_id"];
sub_id = (int)rdr["id"];
}
rdr.Close();
string query = "INSERT INTO Task(Task_maincategory_id, Task_middlecategory_id, Task_subcategory_id, taskstarttime, taskendtime, date) " +
"VALUES('" + main_id + "', '" + middle_id + "','" + sub_id + "','" + start_time + "','" + end_time + "','" + day + "')";
if (maincategory == "대분류" || middlecategory == "중분류" || subcategory == "소분류")//세가지 모두 선택해야 저장
{
MessageBox.Show("모든 항목을 선택하세요");
}
else
{
TimeOverlap(query);
}
}
public void TimeOverlap(string query)
{
int start_hour = dateTimePickerStartTime.Value.Hour;
int end_hour = dateTimePickerEndTime.Value.Hour;
int start_minute = dateTimePickerStartTime.Value.Minute;
int end_minute = dateTimePickerEndTime.Value.Minute;
List <WorkCategory> worklist = LoadWork();
int i = 0;
int j = 0;
while (i < worklist.Count)//listview에 삽입
{
WorkCategory workcategory = new WorkCategory();
workcategory = worklist[i];
DateTime work_start_time = Convert.ToDateTime(workcategory.start_time);
DateTime work_end_time = Convert.ToDateTime(workcategory.end_time);
//등록 시간이 시작 시간과 종료시간 사이일때
if (start_hour > work_start_time.Hour && start_hour < work_end_time.Hour)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
else if (end_hour > work_start_time.Hour && end_hour < work_end_time.Hour)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
else if (start_hour >= work_start_time.Hour && start_hour < work_end_time.Hour && start_minute > work_start_time.Minute)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
else if (start_hour > work_start_time.Hour && start_hour <= work_end_time.Hour && start_minute < work_end_time.Minute)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
else if (end_hour >= work_start_time.Hour && end_hour < work_end_time.Hour && end_minute > work_start_time.Minute)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
else if (end_hour > work_start_time.Hour && end_hour <= work_end_time.Hour && end_minute < work_end_time.Minute)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
else if (start_hour == work_start_time.Hour && start_minute >= work_start_time.Minute && start_hour != work_end_time.Hour)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
else if (start_hour == work_end_time.Hour && start_minute <= work_end_time.Minute && start_hour != work_start_time.Hour)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
else if (start_hour == work_start_time.Hour && start_hour == work_end_time.Hour && start_minute >= work_start_time.Minute && start_minute <= work_end_time.Minute)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
else if (start_hour == work_start_time.Hour && start_hour == work_end_time.Hour && end_hour == work_end_time.Hour && end_minute <= work_end_time.Minute)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
else if (end_hour == work_start_time.Hour && end_hour == work_end_time.Hour && end_hour == work_end_time.Hour && end_minute >= work_start_time.Minute && end_minute <= work_end_time.Minute)
{
MessageBox.Show("다른 업무가 있습니다! 등록 할 수 없습니다.");
j = 1;
this.Close();
break;
}
i++;
}
if (j == 1)
{
this.Close();
}
else
{
DailyTaskDBManager.GetInstace().DBquery(query);
this.Close();
}
}