//***************************************************
//학생 생성 result 1 이상 성공 0 실패
//***************************************************
public int CreateStudent(StudentAdminViewModel Data, string Admin)
{
string sql = "insert into SASU_STD values" +
"(@num,@resno,@name,@sex,@dept,@phone,@email,@password,now(),'A',@admin); ";
int result = 0;
try
{
connectionOpen();
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@num", Data.StudentNumber);
cmd.Parameters.AddWithValue("@resno", Data.StudentResNumber);
cmd.Parameters.AddWithValue("@name", Data.StudentName);
cmd.Parameters.AddWithValue("@sex", Data.StudentSex);
cmd.Parameters.AddWithValue("@dept", Data.StudentDept);
cmd.Parameters.AddWithValue("@phone", Data.StudentPhone);
cmd.Parameters.AddWithValue("@email", Data.StudentEmail);
cmd.Parameters.AddWithValue("@password", SHA512(Data.StudentResNumber1));
cmd.Parameters.AddWithValue("@admin", Admin);
cmd.ExecuteNonQuery();
result = 1;
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
result = 0;
}
finally
{
cmd = null;
conn.Close();
}
return(result);
}
//public SnackbarMessageQueue(TimeSpan messageDuration)
//*********************************************
//엑셀파일 읽기
//*********************************************
private void ExcelRead(string FilePath)
{
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(FilePath, 0, true, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, true, false, Missing.Value, false, false, false);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
string str;
int rCnt = 0; // 열 갯수
int cCnt = 0; // 행 갯수
Excel.Range range = worksheet.UsedRange;
System.Array myvalues = (System.Array)range.Cells.Value2;
//string[] strArray = ConvertToStringArray(myvalues);
myViewModel.Clear();
for (rCnt = 2; rCnt <= range.Rows.Count; rCnt++)
{
StudentAdminViewModel Data = new StudentAdminViewModel
{
StudentCode = "A",
StudentNumber = (range.Cells[rCnt, 1] as Excel.Range).Value2.ToString(),
StudentResNumber1 = (range.Cells[rCnt, 2] as Excel.Range).Value2.ToString(),
StudentResNumber2 = (range.Cells[rCnt, 3] as Excel.Range).Value2.ToString(),
StudentName = (range.Cells[rCnt, 4] as Excel.Range).Value2.ToString(),
StudentSex = (range.Cells[rCnt, 5] as Excel.Range).Value2.ToString(),
StudentDept = (range.Cells[rCnt, 6] as Excel.Range).Value2.ToString(),
StudentPhone = (range.Cells[rCnt, 7] as Excel.Range).Value2.ToString(),
StudentEmail = (range.Cells[rCnt, 8] as Excel.Range).Value2.ToString(),
StudentPassword = (range.Cells[rCnt, 2] as Excel.Range).Value2.ToString()
};
myViewModel.Insert(myViewModel.Count, Data);
}
notifier.ShowSuccess("엑셀파일을 성공적으로 불러왔습니다");
}
//***************************************************
//학생 조회 (검색)
//***************************************************
public void SearchStudent(BindingList <StudentAdminViewModel> myViewModel, string StuNo)
{
if (StuNo.Length < 3)
{
return;
}
String sql = "Select * from sasu_std where stu_stuNo like '%" + StuNo + "%'";
try
{
connectionOpen();
cmd = new MySqlCommand(sql, conn);
MySqlDataReader reader = cmd.ExecuteReader();
myViewModel.Clear();
while (reader.Read())
{
//stu_stuno, stu_resno, stu_name, stu_sex, stu_dept, stu_phone, stu_email, stu_password, datasys1, datasys2, datasys3
string ResNo = reader["stu_resno"].ToString();
StudentAdminViewModel Data = new StudentAdminViewModel
{
StudentCode = "S",
StudentNumber = reader["stu_stuno"].ToString(),
StudentResNumber1 = ResNo.Substring(0, 6),
StudentResNumber2 = ResNo.Substring(6),
StudentEmail = reader["stu_email"].ToString(),
StudentName = reader["stu_name"].ToString(),
StudentPassword = reader["stu_password"].ToString(),
StudentPhone = reader["stu_phone"].ToString(),
StudentSex = reader["stu_sex"].ToString(),
StudentDept = reader["stu_dept"].ToString(),
};
myViewModel.Insert(myViewModel.Count, Data);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
cmd = null;
conn.Close();
}
}
//***************************************************
//학생 업데이트 result 1 이상 성공 0 실패
//***************************************************
public int UpdateStudent(StudentAdminViewModel Data, string admin)
{
int result = 0;
string sql = "update SASU_STD set " +
"stu_resno = @num, " +
"stu_name = @name, " +
"stu_resno = @resno " +
"stu_sex = @sex, " +
"stu_dept = @dept, " +
"stu_phone = @phone " +
"stu_email =@email ," +
"stu_password = @password " +
"datasys1 = now(), " +
"datasys2= 'U', " +
"datasys3 = @admin " +
"where stu_stuno = @num";
try
{
connectionOpen();
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@num", Data.StudentNumber);
cmd.Parameters.AddWithValue("@resno", Data.StudentResNumber);
cmd.Parameters.AddWithValue("@name", Data.StudentName);
cmd.Parameters.AddWithValue("@sex", Data.StudentSex);
cmd.Parameters.AddWithValue("@dept", Data.StudentDept);
cmd.Parameters.AddWithValue("@phone", Data.StudentPhone);
cmd.Parameters.AddWithValue("@email", Data.StudentEmail);
cmd.Parameters.AddWithValue("@password", SHA512(Data.StudentPassword));
cmd.Parameters.AddWithValue("@admin", admin);
cmd.ExecuteNonQuery();
result = 1;
}catch (Exception e)
{
Console.WriteLine(e.ToString());
result = 0;
}
finally
{
cmd = null;
conn.Close();
}
return(result);
}
//***************************************************
//학생 삭제 result 1 이상 성공 0 실패
//***************************************************
public int DeleteStudent(StudentAdminViewModel Data)
{
int result = 0;
string sql = "delete from SASU_STD WHERE stu_stuno =@num;";
try
{
connectionOpen();
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@num", Data.StudentNumber);
cmd.ExecuteNonQuery();
result = 1;
}catch (Exception e)
{
Console.WriteLine(e.ToString());
result = 0;
}
finally
{
cmd = null;
conn.Close();
}
return(result);
}
//*********************************************
//확인 버튼 클릭
//*********************************************
private void CheckButton_Click(object sender, RoutedEventArgs e)
{
DefaultState();
//입력
StudentAdminViewModel data = (StudentAdminViewModel)StudentInfo.DataContext;
if (Btn_state == 0)
{
StudentAdminViewModel temp = new StudentAdminViewModel
{
StudentCode = "A",
StudentNumber = data.StudentNumber,
StudentName = data.StudentName,
StudentResNumber1 = data.StudentResNumber1,
StudentResNumber2 = data.StudentResNumber2,
StudentSex = data.StudentSex,
StudentDept = data.StudentDept,
StudentPassword = data.StudentPassword,
StudentEmail = data.StudentEmail,
StudentPhone = data.StudentPhone,
};
myViewModel.Insert(myViewModel.Count, temp);
}
//업데이트
if (Btn_state == 1)
{
if (myViewModel[DG1.SelectedIndex].StudentCode == "A")
{
}
else
{
int cnt = 0;
if (TempData[0] != data.StudentNumber)
{
cnt++;
}
if (TempData[1] == data.StudentName)
{
cnt++;
}
if (TempData[2] == data.StudentPhone)
{
cnt++;
}
if (TempData[3] == data.StudentPassword)
{
cnt++;
}
if (TempData[4] == data.StudentDept)
{
cnt++;
}
if (TempData[5] == data.StudentSex)
{
cnt++;
}
if (TempData[6] == data.StudentEmail)
{
cnt++;
}
if (TempData[7] == data.StudentResNumber1)
{
cnt++;
}
if (TempData[8] == data.StudentResNumber2)
{
cnt++;
}
if (cnt > 0)
{
data.StudentCode = "U";
}
}
}
if (Btn_state == 2)
{
IList items = DG1.SelectedItems;
if (items.Count > 1)
{
List <int> indexArray = new List <int>();
foreach (StudentAdminViewModel item in items)
{
for (int i = 0; i < myViewModel.Count; i++)
{
if (myViewModel[i].StudentNumber == item.StudentNumber)
{
if (item.StudentCode == "A")
{
indexArray.Add(i);
}
else if (item.StudentCode == "D")
{
myViewModel[i].StudentCode = myViewModel[i].StudentDivision;
}
else
{
myViewModel[i].StudentDivision = myViewModel[i].StudentCode;
myViewModel[i].StudentCode = "D";
}
}
}
}
for (int i = indexArray.Count - 1; i >= 0; i--)
{
myViewModel.RemoveAt(indexArray[i]);
}
}
else if (items.Count == 1)
{
StudentAdminViewModel DeleteData = myViewModel[DG1.SelectedIndex];
if (DeleteData.StudentCode == "A")
{
myViewModel.Remove(DeleteData);
}
else if (DeleteData.StudentCode == "D")
{
DeleteData.StudentCode = DeleteData.StudentDivision;
}
else
{
DeleteData.StudentDivision = DeleteData.StudentCode;
DeleteData.StudentCode = "D";
}
}
else
{
MessageBox.Show("선택이 안되었네요");
}
}
resetText();
}
