private void comboBox_year_SelectedIndexChanged(object sender, EventArgs e) { chart1.Series.Clear(); string year = comboBox_year.SelectedItem.ToString(); int month = 1; string mm; for (int i = 1; i < 13; i++) { if (i >= 1 && i < 10) { mm = "0" + i.ToString(); } else { mm = i.ToString(); } string query = "select subject.name subname, qq.a, qq.b from subject, (select count(*) a, register.subject_id b from register join subject on subject.id = register.subject_id where to_char(register.day, 'yyyymm')='" + comboBox_year.Text + mm + "' group by register.subject_id) qq where subject.id = qq.b"; OracleDataReader rdr2 = DBManager.GetInstance().ExecuteReader(query); if (rdr2.HasRows) { while (rdr2.Read()) { string subname = rdr2["subname"].ToString(); if (chart1.Series.IsUniqueName(subname) is false) { chart1.Series.Add(rdr2["subname"].ToString()); } else { chart1.Series["subname"].Points.AddXY(mm, Convert.ToInt32(rdr2["a"])); } } } } /* * while (rdr2.Read()) * { * int mm = Convert.ToInt32(rdr2["mm"]); * string name = rdr2["sub_name"].ToString() + "-" + rdr2["id"].ToString(); * * while (true) * { * if (chart1.Series.IsUniqueName(name) is false) * { * for (month = 1; month < 13; month++) * { * if (mm == month) * { * chart1.Series[name].Points.AddXY(month, Convert.ToInt32(rdr2["stu_num"])); * } * else * { * chart1.Series[name].Points.AddXY(month, 0); * } * } * break; * } * else * { * chart1.Series.Add(name); * chart1.Series[name].ChartType = SeriesChartType.Line; * } * } * }*/ }
private void button_Search_Click(object sender, EventArgs e) { string x; try { x = comboBox_Search.SelectedItem.ToString(); } catch (Exception exception) { MessageBox.Show("검색키워드를 선택해주십시오.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } switch (x) { case "과목번호": string id = textBox_Search.Text.ToString(); string query = "SELECT * FROM SUBJECT WHERE id = '" + id + "' ORDER BY ID ASC"; OracleDataReader rdr = DBManager.GetInstance().ExecuteReader(query); dataGridView1.DataSource = null; DataTable dt = new DataTable(); dt.Load(rdr); dataGridView1.DataSource = dt; break; case "과목명": string name = textBox_Search.Text.ToString(); query = "SELECT * FROM SUBJECT WHERE name like '" + name + "%' ORDER BY ID ASC"; OracleDataReader rdr_name = DBManager.GetInstance().ExecuteReader(query); dataGridView1.DataSource = null; dt = new DataTable(); dt.Load(rdr_name); dataGridView1.DataSource = dt; break; case "수강료": string tuition = textBox_Search.Text.ToString(); query = "SELECT * FROM SUBJECT WHERE tuition = '" + tuition + "' ORDER BY ID ASC"; OracleDataReader rdr_tuition = DBManager.GetInstance().ExecuteReader(query); dataGridView1.DataSource = null; dt = new DataTable(); dt.Load(rdr_tuition); dataGridView1.DataSource = dt; break; case "강사번호": string teacher_id = textBox_Search.Text.ToString(); query = "SELECT * FROM SUBJECT WHERE teacher_id = '" + teacher_id + "' ORDER BY ID ASC"; OracleDataReader rdr_teacher_id = DBManager.GetInstance().ExecuteReader(query); dataGridView1.DataSource = null; dt = new DataTable(); dt.Load(rdr_teacher_id); dataGridView1.DataSource = dt; break; case "월": try { foreach (DataGridViewRow item in dataGridView1.SelectedRows) { int sub_id = Convert.ToInt32(item.Cells[0].Value.ToString()); } } catch (Exception exception) { MessageBox.Show("과목을 선택해주십시오.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } string day = textBox_Search.Text.ToString(); query = "SELECT * FROM lecture WHERE to_char(day, 'mm') = '" + day + "' ORDER BY subject_id, id ASC"; OracleDataReader rdr_day = DBManager.GetInstance().ExecuteReader(query); dataGridView2.DataSource = null; dt = new DataTable(); dt.Load(rdr_day); dataGridView2.DataSource = dt; break; default: MessageBox.Show("데이터가 없습니다", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); break; } }
static bool update = true; //업데이트 조건 최종 true 일 때 업데이트 private void button_Save_Click(object sender, EventArgs e) { string key; string name; string tuition; string teacher_id; int start_time; int end_time; string room_num; DialogResult result = MessageBox.Show("수정하시겠습니까?", "알림", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == DialogResult.Yes) { for (int i = 0; i < dataGridView1.Rows.Count; i++) { key = dataGridView1.Rows[i].Cells[0].Value.ToString(); name = dataGridView1.Rows[i].Cells[1].Value.ToString(); tuition = dataGridView1.Rows[i].Cells[2].Value.ToString(); teacher_id = dataGridView1.Rows[i].Cells[3].Value.ToString(); string query = "UPDATE SUBJECT set name = '" + name + "' tuition = '" + Convert.ToInt32(tuition) + "', teacher_id = '" + Convert.ToInt32(teacher_id) + "' where id = " + Convert.ToInt32(key) + ""; int rdr2 = DBManager.GetInstance().ExecuteNon(query); } for (int i = 0; i < dataGridView2.Rows.Count; i++) { //데이터 그리드뷰 모든 데이터 int sub_id = Convert.ToInt32(dataGridView2.Rows[i].Cells[0].Value.ToString()); int id = Convert.ToInt32(dataGridView2.Rows[i].Cells[1].Value.ToString()); start_time = Convert.ToInt32(DateParse_time(dataGridView2.Rows[i].Cells[2].Value.ToString())); end_time = Convert.ToInt32(DateParse_time(dataGridView2.Rows[i].Cells[3].Value.ToString())); room_num = dataGridView2.Rows[i].Cells[4].Value.ToString(); string supply = dataGridView2.Rows[i].Cells[5].Value.ToString(); //날짜 비교를 위한 변수 string week_ = DateParse_day(dataGridView2.Rows[i].Cells[2].Value.ToString()); //시작시간, 끝시간, 날짜, 방번호, 과목번호를 받아온다. 단, 수정하려는 데이터의 강의는 제외한다. string query = "SELECT to_char(DAY, 'HH24mi') AS DAY_TIME, to_char(END_DAY, 'HH24mi') AS END_DAY_TIME, to_char(DAY, 'yyyy-MM-dd') AS DAY_WEEK, ROOM_NUM, subject_id FROM LECTURE where subject_id not in (" + sub_id + ")"; OracleDataReader rdr2 = DBManager.GetInstance().ExecuteReader(query); while (rdr2.Read()) { int time_Day = Convert.ToInt32(rdr2["DAY_TIME"]); int time_End_Day = Convert.ToInt32(rdr2["END_DAY_TIME"]); string day_week = rdr2["DAY_WEEK"].ToString(); string Room_num_ = rdr2["ROOM_NUM"].ToString(); int date_sub_id = Convert.ToInt32(rdr2["subject_id"]); if (week_.Equals(day_week)) { if ((time_Day > end_time && time_Day > start_time) || (time_End_Day < end_time) && (time_End_Day < start_time)) { if (Room_num_.Equals(room_num)) { update = true; } } else { update = false; MessageBox.Show("이미 강의가 있는 시간입니다. 시간표를 확인하십시오", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); break; } } } if (update == true) { query = "update lecture set day = to_date('" + DateParse(dataGridView2.Rows[i].Cells[2].Value.ToString()) + "', 'yyyy-mm-dd hh24:mi'), end_day = to_date('" + DateParse(dataGridView2.Rows[i].Cells[3].Value.ToString()) + "', 'yyyy-mm-dd hh24:mi'), room_num = '" + room_num + "' where subject_id = " + sub_id + " AND id = " + id + ""; int rdr3 = DBManager.GetInstance().ExecuteNon(query); } } } }