private void button1_Click(object sender, EventArgs e) { if (open_msg == "") { MessageBox.Show("상반기, 하반기 체크필요"); return; } String check = "show tables like 'hero_" + DateTime.Now.ToString("yyyy") + "_" + textBox2.Text + "_" + open_msg + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); if (!sqlc.Read()) { MessageBox.Show("이미 삭제를 하였습니다."); } else { MessageBox.Show(DateTime.Now.ToString("yyyy") + "" + open_msg + " 삭제"); String check_delete = "DROP TABLE hero_" + DateTime.Now.ToString("yyyy") + "_" + textBox2.Text + "_" + open_msg; var check_delete_c = ha.getRecord(check_delete); String coulmn_delete = "DELETE FROM hero_table_nickname WHERE tablename = '" + DateTime.Now.ToString("yyyy") + "_" + textBox2.Text + "_" + open_msg + "'"; var coulmn_delete_c = ha.getRecord(coulmn_delete); /* * var connectionInfo_pw = new Renci.SshNet.PasswordConnectionInfo("l.bsks.ac.kr", "p201606010", "pp201606010"); * var client = new SftpClient(connectionInfo_pw); * client.Connect(); * string serverPath = string.Format(@"/home/p201606010/public_html/hero_php/img/" + DateTime.Now.ToString("yyyy") + "_" + textBox2.Text + "_" + open_msg); * client.DeleteDirectory(serverPath); * client.Disconnect(); */ } }
// 미승인 그리드뷰 더블 클릭 이벤트 private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e) { if (e.RowIndex < 0) { return; } no_reader.Close(); String sql1 = "select tablename from hero_table_nickname where tablenickname like '" + comboBox1.SelectedItem.ToString() + "'"; cmd = new MySqlCommand(sql1, conn); no_reader = cmd.ExecuteReader(); while (no_reader.Read()) { String check = "update hero_" + no_reader["tablename"].ToString() + " set admin_check = 1 where user_student_no = '" + dataGridView1.Rows[e.RowIndex].Cells[0].FormattedValue.ToString() + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); String check2 = "update hero_user set user_check = 1 where user_student_no = '" + dataGridView1.Rows[e.RowIndex].Cells[0].FormattedValue.ToString() + "'"; var sqlc2 = ha.getRecord(check2); MessageBox.Show("승인 하였습니다."); } updateview(); }
public bool Student_check(string std_id) { if (search.Text == "") { return(false); } String check = "select tablename from hero_table_nickname where tablenickname like '" + comboBox1.SelectedItem.ToString() + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); while (sqlc.Read()) { String check2 = "select * from hero_" + sqlc["tablename"].ToString() + " where USER_STUDENT_NO = '" + search.Text + "'"; var reader = ha.getRecord(check2); if (reader.Read()) { return(true); } else { return(false); } } return(false); }
public void selectupdate() { String selectsql = "select * from hero_table_nickname"; hero_api ha2 = new hero_api(); var ss_sql = ha2.getRecord(selectsql); if (comboBox1.Text == "" || db_name == "" || db_nickname == "") { MessageBox.Show("테이블을 정해주세요"); return; } else { dataGridView1.Rows.Clear(); String sql1 = "select * from hero_" + db_name; cmd = new MySqlCommand(sql1, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { string[] row = { reader["USER_STUDENT_NO"].ToString(), reader["USER_NAME"].ToString(), reader["USER_BIRTH"].ToString(), reader["USER_SEX"].ToString(), reader["USER_PHONE"].ToString(), reader["USER_MAIL"].ToString(), reader["USER_ADDR"].ToString(), reader["USER_IMAGE"].ToString(), reader["USER_SYS"].ToString() }; dataGridView1.Rows.Add(row); } reader.Close(); } }
private void open_Click(object sender, EventArgs e) { DateTime dt = dateTimePicker1.Value; DateTime dt2 = dateTimePicker2.Value; int dd = int.Parse(dt.Day.ToString()); int dd2 = int.Parse(dt2.Day.ToString()); if (open_msg == "") { MessageBox.Show("상반기, 하반기 체크필요"); return; } if (dd2 < dd) { MessageBox.Show("날짜를 제대로 수정해주세요"); return; } String check = "show tables like 'hero_" + DateTime.Now.ToString("yyyy") + "_" + textBox2.Text + "_" + open_msg + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); if (!sqlc.Read()) { // putty sql버전이 낮기에 user_sys를 직접 넣어줘야함. // db이름을 고정 시켜야함 ex) hero_appbutton_2019_high/low MessageBox.Show(DateTime.Now.ToString("yyyy") + "" + textBox2.Text + "" + open_msg + " 테이블생성"); String check_create = "CREATE TABLE hero_" + DateTime.Now.ToString("yyyy") + "_" + textBox2.Text + "_" + open_msg + "(USER_STUDENT_NO varchar(30), USER_NAME varchar(30), USER_BIRTH varchar(10), USER_SEX char(1), USER_PHONE varchar(15), USER_MAIL varchar(255), USER_ADDR varchar(255), USER_IMAGE varchar(255), ADMIN_CHECK varchar(1), USER_SYS varchar(12), primary key(USER_STUDENT_NO));"; var check_create_c = ha.getRecord(check_create); string start = dateTimePicker1.Value.ToString("yyyy-MM-dd"); string end = dateTimePicker2.Value.ToString("yyyy-MM-dd"); String nickname = "insert into hero_table_nickname(tablename, tablenickname, table_start, table_bestby) values('" + DateTime.Now.ToString("yyyy") + "_" + textBox2.Text + "_" + open_msg + "', '" + nickbox.Text + "','" + start + "', '" + end + "');"; var nickname_c = ha.getRecord(nickname); /* * * 폴더 생성 구문 (권한 에러) * * var connectionInfo_pw = new Renci.SshNet.PasswordConnectionInfo("l.bsks.ac.kr", "p201606010", "pp201606010"); * var client = new SftpClient(connectionInfo_pw); * client.Connect(); * string serverPath = string.Format(@"/home/p201606010/public_html/hero_php/img/" + DateTime.Now.ToString("yyyy") + "_" + textBox2.Text + "_" + open_msg); * client.CreateDirectory(serverPath); * client.Disconnect(); * */ } else { MessageBox.Show("이미 생성을 하였습니다."); } }
private void Form8_Load(object sender, EventArgs e) { textBox1.Text = student_id; // 지각 String check = "select count(*) from hero_work where user_student_no = '" + student_id + "' and DILI_GUBUN = '2' and user_table = '" + db_name + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); if (sqlc.Read()) { textBox2.Text = sqlc[0].ToString(); } // 결근 String check2 = "select count(*) from hero_work where user_student_no = '" + student_id + "' and DILI_GUBUN = '1' and user_table = '" + db_name + "'"; var sqlc2 = ha.getRecord(check2); if (sqlc2.Read()) { textBox4.Text = sqlc2[0].ToString(); } // 조퇴 String check3 = "select count(*) from hero_work where user_student_no = '" + student_id + "' and DILI_GUBUN = '3' and user_table = '" + db_name + "'"; var sqlc3 = ha.getRecord(check3); if (sqlc3.Read()) { textBox3.Text = sqlc3[0].ToString(); } // 지각 + 조퇴 String check4 = "select count(*) from hero_work where user_student_no = '" + student_id + "' and DILI_GUBUN = '4' and user_table = '" + db_name + "'"; var sqlc4 = ha.getRecord(check4); if (sqlc4.Read()) { int plus = int.Parse(sqlc4[0].ToString()); int tb3 = int.Parse(textBox3.Text) + plus; int tb2 = int.Parse(textBox2.Text) + plus; textBox3.Text = tb3.ToString(); textBox2.Text = tb2.ToString(); } // 지각 시간 합치기 String check5 = "select SUM(DILI_TARDY) from hero_work where user_student_no = '" + student_id + "' and USER_TABLE = '" + db_name + "'"; var sqlc5 = ha.getRecord(check5); if (sqlc5.Read()) { textBox5.Text = sqlc5[0].ToString() + "시간"; } }
/************************* * * 퇴근 * ************************/ private void button3_Click(object sender, EventArgs e) { string now = yyyymmdd(); String check = "select * from hero_work where DILI_DATE = '" + now + "' and USER_STUDENT_NO='" + studentbox.Text + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); cmd = new MySqlCommand(); cmd.Connection = conn; string hourt = DateTime.Now.ToString("HH-mm-ss"); if (sqlc.Read()) { if (sqlc["DILI_TTIME"].ToString() == "") { String runhome = "update hero_work set DILI_TTIME='" + hourt + "' where DILI_DATE = '" + now + "' and USER_STUDENT_NO='" + studentbox.Text + "'"; var runhomecheck = ha.getRecord(runhome); int yyyy = int.Parse(DateTime.Now.ToString("yyyy")); int mm = int.Parse(DateTime.Now.ToString("MM")); int dd = int.Parse(DateTime.Now.ToString("dd")); // 18시가 지났는지 확인 -> 조퇴체크 DateTime nowtime = DateTime.Now; DateTime timecheck = new DateTime(yyyy, mm, dd, 18, 0, 0); int timeresult = DateTime.Compare(nowtime, timecheck); // 조퇴 체크 if (timeresult < 0) { String gubunupdate; if (sqlc["DILI_GUBUN"].ToString() == "2") { MessageBox.Show("지각도 하고 조퇴도함"); String gubunupdate2 = "update hero_work set DILI_GUBUN=4 where DILI_DATE = '" + now + "' and USER_STUDENT_NO='" + studentbox.Text + "'"; var runhomecheck3 = ha.getRecord(gubunupdate2); } else { gubunupdate = "update hero_work set DILI_GUBUN=3 where DILI_DATE = '" + now + "' and USER_STUDENT_NO='" + studentbox.Text + "'"; MessageBox.Show("18시 안지났다."); var runhomecheck2 = ha.getRecord(gubunupdate); } } MessageBox.Show("퇴근"); selectupdate(); } else { MessageBox.Show("이미 퇴근하였습니다.\n일자: " + sqlc["DILI_DATE"].ToString() + "\n학번: " + sqlc["USER_STUDENT_NO"].ToString() + "\n퇴근일자: " + sqlc["DILI_TTIME"].ToString() + "\n출근상태: " + sqlc["DILI_GUBUN"].ToString()); } } }
/************************* * * 특정학생 전날 결근체크 * (바코드를 찍었을경우) * ************************/ public void outcheck() { int yyyy = int.Parse(DateTime.Now.ToString("yyyy")); int mm = int.Parse(DateTime.Now.ToString("MM")); int dd = int.Parse(DateTime.Now.ToString("dd")) - 1; int hh = int.Parse(DateTime.Now.ToString("HH")); int mm2 = int.Parse(DateTime.Now.ToString("mm")); int ss = int.Parse(DateTime.Now.ToString("ss")); string now_m = yyyy.ToString() + "-" + mm.ToString() + "-" + dd.ToString(); String check = "select * from hero_work where DILI_DATE = '" + now_m + "' and USER_STUDENT_NO='" + studentbox.Text + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); MessageBox.Show(now_m); if (sqlc.Read()) { MessageBox.Show("이미 결근을 확인했습니다."); } else { String idcheck_sql = "select * from hero_" + db_name + " where USER_STUDENT_NO='" + studentbox.Text + "'"; cmd = new MySqlCommand(idcheck_sql, conn); reader = cmd.ExecuteReader(); if (!reader.Read()) { MessageBox.Show("없는 학생입니다"); reader.Close(); return; } else { reader.Close(); cmd = new MySqlCommand(); cmd.Connection = conn; String daycheck = GetDay(DateTime.Now); if (daycheck == "일" || daycheck == "토") { MessageBox.Show("주말"); return; } cmd.CommandText = "insert into hero_work(DILI_DATE, USER_STUDENT_NO, DILI_GUBUN, USER_TABLE) values(@a1, @a2, @a3, @a4)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@a1", now_m); cmd.Parameters.AddWithValue("@a2", studentbox.Text); cmd.Parameters.AddWithValue("@a3", 1); cmd.Parameters.AddWithValue("@a4", db_name); cmd.ExecuteNonQuery(); MessageBox.Show("전날 결근 확인"); } } }
public void login() { String idcheck_sql = "select * from hero_admin where admin_ID='" + textBox1.Text + "'"; cmd = new MySqlCommand(idcheck_sql, conn); reader = cmd.ExecuteReader(); if (!reader.Read()) { MessageBox.Show("등록되지 않은 아이디입니다."); reader.Close(); } else { if (textBox2.Text != "") { reader.Close(); String pwcheck_sql = "select admin_pw from hero_admin where admin_ID='" + textBox1.Text + "'"; hero_api ha = new hero_api(); var pwsql = ha.getRecord(pwcheck_sql); while (pwsql.Read()) { if (textBox2.Text == pwsql["admin_pw"].ToString()) { this.Opacity = 0.0; this.Enabled = false; this.ShowInTaskbar = false; Form6 f = new Form6(); f.Show(); hero_api.setadmin_id(textBox1.Text); MessageBox.Show("로그인 성공"); reg.SetValue("autologin_id", textBox1.Text); reg.SetValue("autologin_pw", textBox2.Text); reader.Close(); } else { MessageBox.Show("비밀번호가 틀렸습니다."); reader.Close(); } } reader.Close(); } else { MessageBox.Show("비밀번호를 입력해주세요"); } } }
public void show_select() { String selectsql = "select * from hero_admin"; hero_api ha2 = new hero_api(); var ss_sql = ha2.getRecord(selectsql); dataGridView1.Rows.Clear(); while (ss_sql.Read()) { string[] row = { ss_sql["admin_name"].ToString(), ss_sql["admin_id"].ToString(), ss_sql["admin_pw"].ToString(), ss_sql["admin_level"].ToString() }; dataGridView1.Rows.Add(row); } }
private void Form4_Load(object sender, EventArgs e) { textBox1.Text = DateTime.Now.ToString("yyyy"); String check = "select * from hero_table_nickname"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); listBox1.Items.Clear(); while (sqlc.Read()) { listBox1.Items.Add("DB이름: " + sqlc[0].ToString() + " 별명: " + sqlc[1].ToString()); } }
public void show_select() { String selectsql = "select * from hero_user"; hero_api ha2 = new hero_api(); var ss_sql = ha2.getRecord(selectsql); dataGridView1.Rows.Clear(); while (ss_sql.Read()) { string[] row = { ss_sql["USER_STUDENT_NO"].ToString(), ss_sql["USER_ID"].ToString(), ss_sql["USER_PW"].ToString() }; dataGridView1.Rows.Add(row); } }
public bool student_check(string std_id) { String check = "select * from hero_user where user_student_no ='" + std_id + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); if (sqlc.Read()) { return(true); } else { return(false); } }
public bool admin_check(string std_id) { String check = "select * from hero_admin where admin_id ='" + std_id + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); if (sqlc.Read()) { return(true); } else { return(false); } }
public bool sql_index_check() { String selectsql = "select USER_STUDENT_NO from hero_" + db_name + " where USER_STUDENT_NO='" + numberbox.Text + "'"; hero_api ha2 = new hero_api(); var ss_sql = ha2.getRecord(selectsql); if (ss_sql.Read()) { return(true); } else { return(false); } }
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { db_nickname = comboBox1.Text; String check = "select tablename from hero_table_nickname where tablenickname='" + comboBox1.Text + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); while (sqlc.Read()) { db_name = sqlc["tablename"].ToString(); } if (comboBox1.Text != "") { groupBox2.Enabled = true; } }
private void Form5_Load(object sender, EventArgs e) { conn.Open(); SetupDataGridView(); String check = "select tablenickname from hero_table_nickname"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); comboBox1.Items.Clear(); while (sqlc.Read()) { comboBox1.Items.Add(sqlc[0].ToString()); } button2.Enabled = false; button3.Enabled = false; }
/************************* * * 월별검색 * ************************/ private void button4_Click(object sender, EventArgs e) { dataGridView1.Rows.Clear(); string getmonth = dateTimePicker3.Value.ToString("MM"); String check = "select * from hero_work where USER_TABLE='" + db_name + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); while (sqlc.Read()) { string[] month = sqlc["DILI_DATE"].ToString().Split('-'); if (getmonth.Contains(month[1])) { string[] row = { sqlc["DILI_DATE"].ToString(), sqlc["USER_STUDENT_NO"].ToString(), sqlc["DILI_FTIME"].ToString(), sqlc["DILI_TTIME"].ToString(), sqlc["DILI_GUBUN"].ToString(), sqlc["DILI_TARDY"].ToString() }; dataGridView1.Rows.Add(row); } } }
public void updateview() { // 미승인 // dataGridView1.Rows.Clear(); String sql1 = "select tablename from hero_table_nickname where tablenickname like '" + comboBox1.SelectedItem.ToString() + "'"; cmd = new MySqlCommand(sql1, conn); if (no_reader != null) { no_reader.Close(); } no_reader = cmd.ExecuteReader(); while (no_reader.Read()) { String check = "select * from hero_" + no_reader["tablename"].ToString() + " where admin_check = 0"; hero_api ha = new hero_api(); var reader = ha.getRecord(check); while (reader.Read()) { string[] row = { reader["USER_STUDENT_NO"].ToString(), reader["USER_NAME"].ToString(), reader["USER_BIRTH"].ToString(), reader["USER_SEX"].ToString(), reader["USER_PHONE"].ToString(), reader["USER_MAIL"].ToString(), reader["USER_ADDR"].ToString(), reader["USER_IMAGE"].ToString(), reader["USER_SYS"].ToString() }; dataGridView1.Rows.Add(row); } } no_reader.Close(); // 승인 // dataGridView2.Rows.Clear(); no_reader = cmd.ExecuteReader(); while (no_reader.Read()) { String check = "select * from hero_" + no_reader["tablename"].ToString() + " where admin_check = 1"; hero_api ha = new hero_api(); var reader = ha.getRecord(check); while (reader.Read()) { string[] row = { reader["USER_STUDENT_NO"].ToString(), reader["USER_NAME"].ToString(), reader["USER_BIRTH"].ToString(), reader["USER_SEX"].ToString(), reader["USER_PHONE"].ToString(), reader["USER_MAIL"].ToString(), reader["USER_ADDR"].ToString(), reader["USER_IMAGE"].ToString(), reader["USER_SYS"].ToString() }; dataGridView2.Rows.Add(row); } } no_reader.Close(); }
private void Form3_Load(object sender, EventArgs e) { conn.Open(); SetupDataGridView(); String check = "select * from hero_table_nickname"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); comboBox1.Items.Clear(); while (sqlc.Read()) { comboBox1.Items.Add(sqlc[1].ToString()); } if (comboBox1.Text == "") { groupBox2.Enabled = false; } }
private void Form7_Load(object sender, EventArgs e) { String check_sql = "select * from hero_admin where admin_id='" + hero_api.getadmin_id() + "'"; hero_api ha = new hero_api(); var pwsql = ha.getRecord(check_sql); SetupDataGridView(); show_select(); while (pwsql.Read()) { mylevel.Text = pwsql["admin_level"].ToString(); } if (mylevel.Text != "최고관리자") { delete_b.Enabled = false; level_b.Enabled = false; MessageBox.Show("최고관리자가 아니여서 권한이 제한됩니다."); } }
private void insert_b_Click(object sender, EventArgs e) { if (MessageBox.Show("정말로 " + idbox.Text + " 관리자를 생성하시겠습니까?", "", MessageBoxButtons.YesNo) == DialogResult.Yes) { if (idbox.Text == "" || pwbox.Text == "" || comboBox1.Text == "" || namebox.Text == "") { MessageBox.Show("빈칸을 확인해주세요."); return; } String check = "insert into hero_admin(admin_id, admin_pw, admin_level, admin_name) values('" + idbox.Text + "','" + pwbox.Text + "','" + comboBox1.Text + "','" + namebox.Text + "')"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); MessageBox.Show("생성 됐습니다."); show_select(); boxreset(); } else { MessageBox.Show("취소 됐습니다."); } }
// 첫번째것이 삭제가 안됨 private void button1_Click_1(object sender, EventArgs e) { String check = "select * from hero_table_nickname"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); if (!sqlc.Read()) { MessageBox.Show("이미 삭제를 하였습니다."); } else { while (sqlc.Read()) { MessageBox.Show(sqlc[0].ToString()); String check_delete = "DROP TABLE hero_" + sqlc[0].ToString(); var check_delete_c = ha.getRecord(check_delete); String coulmn_delete = "DELETE FROM hero_table_nickname WHERE tablename = '" + sqlc[0].ToString() + "'"; var coulmn_delete_c = ha.getRecord(coulmn_delete); } } }
private void update_b_Click(object sender, EventArgs e) { if (MessageBox.Show("정말로 수정을 하시겠습니까?", "", MessageBoxButtons.YesNo) == DialogResult.Yes) { if (student_check(studentbox.Text)) { String check = "update hero_user set user_id='" + idbox.Text + "' where user_student_no='" + studentbox.Text + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); MessageBox.Show("수정 됐습니다."); show_select(); } else { MessageBox.Show("없는 학생입니다."); } } else { MessageBox.Show("취소 됐습니다."); } }
// 검색 취소 private void button3_Click(object sender, EventArgs e) { if (Student_check(search.Text)) { String check = "select tablename from hero_table_nickname where tablenickname like '" + comboBox1.SelectedItem.ToString() + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); while (sqlc.Read()) { String check2 = "update hero_" + sqlc["tablename"].ToString() + " set admin_check = 0 where USER_STUDENT_NO = '" + search.Text + "'"; var reader = ha.getRecord(check2); String check3 = "update hero_user set user_check = 0 where user_student_no = '" + search.Text + "'"; var sqlc2 = ha.getRecord(check2); MessageBox.Show(search.Text + "를 승인취소 하였습니다."); } updateview(); } else { MessageBox.Show("없는 학생입니다."); } }
private void delete_b_Click(object sender, EventArgs e) { if (MessageBox.Show("정말로 " + idbox.Text + " 관리자를 삭제하시겠습니까?", "", MessageBoxButtons.YesNo) == DialogResult.Yes) { if (admin_check(idbox.Text)) { String check = "delete from hero_admin where admin_id = '" + idbox.Text + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); MessageBox.Show("삭제 됐습니다."); show_select(); boxreset(); } else { MessageBox.Show("없는 관리자입니다"); } } else { MessageBox.Show("취소 됐습니다."); } }
private void level_b_Click(object sender, EventArgs e) { if (MessageBox.Show("정말로 권한을 수정하시겠습니까?", "", MessageBoxButtons.YesNo) == DialogResult.Yes) { if (admin_check(idbox.Text)) { String check = "update hero_admin set admin_level='" + comboBox1.Text + "' where admin_id='" + idbox.Text + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); MessageBox.Show("수정 됐습니다."); show_select(); boxreset(); } else { MessageBox.Show("없는 관리자입니다"); } } else { MessageBox.Show("취소 됐습니다."); } }
private void Button4_Click(object sender, EventArgs e) { if (textboxcheck() == 0) { if (modecheck == "insert") { // reader.Close(); String idcheck = "select USER_STUDENT_NO from hero_" + db_name + " where USER_STUDENT_NO='" + numberbox.Text + "'"; hero_api ha = new hero_api(); var idsql = ha.getRecord(idcheck); while (idsql.Read()) { MessageBox.Show("이미 있는 학번입니다."); return; } cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = "insert into hero_" + db_name + "(USER_STUDENT_NO, USER_NAME, USER_BIRTH, USER_SEX, USER_PHONE, USER_MAIL, USER_ADDR, USER_IMAGE) values(@a1, @a2, @a3, @a4, @a5, @a6, @a7, @a8)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@a1", numberbox.Text); cmd.Parameters.AddWithValue("@a2", namebox.Text); cmd.Parameters.AddWithValue("@a3", birthbox.Text); cmd.Parameters.AddWithValue("@a4", sexbox.Text); cmd.Parameters.AddWithValue("@a5", phonebox.Text); cmd.Parameters.AddWithValue("@a6", mailbox.Text); cmd.Parameters.AddWithValue("@a7", addrbox.Text); cmd.Parameters.AddWithValue("@a8", pickbox.Text); cmd.ExecuteNonQuery(); if (pictureBox1.Image == null || pickbox.Text == "") { MessageBox.Show("이미지가 없습니다."); return; } else { MessageBox.Show("저장 되었습니다."); } } else if (modecheck == "update") { if (sql_index_check() == true) { cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = "update hero_" + db_name + " set USER_STUDENT_NO=@a1, USER_NAME=@a2, USER_BIRTH=@a3, USER_SEX=@a4, USER_PHONE=@a5, USER_MAIL=@a6, USER_ADDR=@a7, USER_IMAGE=@a8 where USER_STUDENT_NO = '" + numberbox.Text + "'"; cmd.Prepare(); cmd.Parameters.AddWithValue("@a1", numberbox.Text); cmd.Parameters.AddWithValue("@a2", namebox.Text); cmd.Parameters.AddWithValue("@a3", birthbox.Text); cmd.Parameters.AddWithValue("@a4", sexbox.Text); cmd.Parameters.AddWithValue("@a5", phonebox.Text); cmd.Parameters.AddWithValue("@a6", mailbox.Text); cmd.Parameters.AddWithValue("@a7", addrbox.Text); cmd.Parameters.AddWithValue("@a8", pickbox.Text); cmd.ExecuteNonQuery(); if (pictureBox1.Image == null) { return; } MessageBox.Show("수정에 성공하였습니다."); } else { MessageBox.Show("없는 학번입니다."); } } else if (modecheck == "delete") { if (sql_index_check() == true) { String delete_sql = "delete from hero_" + db_name + " where USER_STUDENT_NO='" + numberbox.Text + "'"; hero_api ha = new hero_api(); var deletesql = ha.getRecord(delete_sql); MessageBox.Show("삭제에 성공하였습니다."); } else { MessageBox.Show("없는 학번입니다."); } textboxON(); } resetTextbox(); disablebutton(true); selectupdate(); pictureBox1.Image = null; } modecheck = "null"; }
/************************* * * 출근 * ************************/ private void button2_Click_1(object sender, EventArgs e) { int yyyy = int.Parse(DateTime.Now.ToString("yyyy")); int mm = int.Parse(DateTime.Now.ToString("MM")); int dd = int.Parse(DateTime.Now.ToString("dd")); String check = "select * from hero_work where USER_STUDENT_NO ='" + studentbox.Text + "' and DILI_DATE = '" + yyyy.ToString() + "-" + mm.ToString() + "-" + dd.ToString() + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); String admincheck = "select * from hero_" + db_name + " where USER_STUDENT_NO ='" + studentbox.Text + "' and ADMIN_CHECK = 1"; var admin_sqlc = ha.getRecord(admincheck); cmd = new MySqlCommand(); cmd.Connection = conn; if (admin_sqlc.Read()) { if (!sqlc.Read()) { cmd.CommandText = "insert into hero_work(DILI_DATE, USER_STUDENT_NO, DILI_FTIME, USER_TABLE) values(@a1, @a2, @a3, @a4)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@a1", yyyy.ToString() + "-" + mm.ToString() + "-" + dd.ToString()); cmd.Parameters.AddWithValue("@a2", studentbox.Text); cmd.Parameters.AddWithValue("@a3", DateTime.Now.ToString("HH-mm-ss")); cmd.Parameters.AddWithValue("@a4", db_name); cmd.ExecuteNonQuery(); // 9시가 지났는지 확인 -> 지각체크 DateTime nowtime = DateTime.Now; DateTime timecheck = new DateTime(yyyy, mm, dd, 9, 0, 0); int timeresult = DateTime.Compare(nowtime, timecheck); String gubunupdate; // 지각 체크 if (timeresult > 0) { TimeSpan result = nowtime - timecheck; gubunupdate = "update hero_work set DILI_gubun = '2', DILI_tardy = '" + result.ToString(@"hh\:mm\:ss") + "' where USER_STUDENT_NO='" + studentbox.Text + "' and DILI_DATE = '" + yyyy.ToString() + "-" + mm.ToString() + "-" + dd.ToString() + "'"; MessageBox.Show("9시 지났다. (지각: " + result.ToString(@"hh\:mm\:ss") + ")"); } else { gubunupdate = "update hero_work set DILI_gubun=0 where USER_STUDENT_NO='" + studentbox.Text + "'"; MessageBox.Show("9시 안지났다."); } hero_api ha2 = new hero_api(); var gubunsql = ha.getRecord(gubunupdate); outcheck(); // 전날 결근 확인 MessageBox.Show("출근완료"); selectupdate(); } else { MessageBox.Show("이미 출근하였습니다.\n일자: " + sqlc["DILI_DATE"].ToString() + "\n학번: " + sqlc["USER_STUDENT_NO"].ToString() + "\n출근일자: " + sqlc["DILI_FTIME"].ToString() + "\n출근상태: " + sqlc["DILI_GUBUN"].ToString()); } return; } else { MessageBox.Show("admin check no or not find student id"); } }
/************************* * * 날짜 결근 체크 * >> 오늘날짜 기준으로 전만 결근체크 * ************************/ private void button8_Click(object sender, EventArgs e) { DateTime dt_daycheck = dateTimePicker1.Value; DateTime dt = dateTimePicker1.Value; DateTime dt2 = dateTimePicker2.Value; int yy = int.Parse(dt.Year.ToString()); int mm = int.Parse(dt.Month.ToString()); int dd = int.Parse(dt.Day.ToString()); int dd2 = int.Parse(dt2.Day.ToString()); int ddcheck = 0, ddbox = 0; if (dd2 == dd) { ddcheck = 1; MessageBox.Show("="); } else if (dd2 > dd) { ddcheck = dd2 - dd + 1; } else { MessageBox.Show("제대로"); } listBox1.Items.Clear(); for (int i = 0; i < ddcheck; i++) { int ddfor = int.Parse(dt.Day.ToString()) + ddbox; string yymmdd = yy + "-" + mm + "-" + ddfor; DateTime answer = dt_daycheck.AddDays(ddbox); String daycheck = GetDay(answer); ddbox++; if (daycheck == "일" || daycheck == "토") { MessageBox.Show("주말 " + answer.ToString()); } else { String check = "select * from hero_" + db_name + " hero left join hero_work on hero.USER_STUDENT_NO = hero_work.USER_STUDENT_NO and DILI_DATE like '" + yymmdd + "'"; hero_api ha = new hero_api(); var sqlc = ha.getRecord(check); while (sqlc.Read()) { if (sqlc["DILI_DATE"].ToString() == "") { listBox1.Items.Add(yymmdd + " ID: " + sqlc[0].ToString()); cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = "insert into hero_work(DILI_DATE, USER_STUDENT_NO, DILI_GUBUN, USER_TABLE) values(@a1, @a2, @a3, @a4)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@a1", yymmdd); cmd.Parameters.AddWithValue("@a2", sqlc[0].ToString()); cmd.Parameters.AddWithValue("@a3", 1); cmd.Parameters.AddWithValue("@a4", db_name); cmd.ExecuteNonQuery(); } } } } }