Приложение г Листинг программного модуля (обязательное)
//реализация методов просмотра
public List View() {
try {
stmt = null;
ResultSet rs = null;
list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Properties ConnectionProps = new Properties();
ConnectionProps.setProperty("useUnicode", "TRUE");
ConnectionProps.setProperty("characterEncoding", "cp866");
String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";
Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"
+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"
+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"
+ " ON speciality.`ID_spec` =personal.`ID_spec`"
+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state`;");
// rs.next();
while (rs.next()) {
list.add(rs.getString("FIO"));
list.add(rs.getString("age"));
list.add(rs.getString("sex"));
list.add(rs.getString("skill"));
list.add(rs.getString("spec"));
list.add(rs.getString("otdel"));
list.add(rs.getString("boss"));
list.add(rs.getString("state"));
list.add(rs.getString("zarpl"));
}
//int rowsEffected = stmt.executeUpdate("DELETE FROM mvalues WHERE `IDМатериальнойЦенности` = '"+delName+"' ");
//int rowsEffected = stmt.executeUpdate("INSERT INTO mvalues VALUES "
// + "('"+Integer.parseInt(s1)+"','"+s2+"','"+s3
// +"','"+Integer.parseInt(s5)+"','"+s4+"'"//**c2-c3
// + ",'"+Integer.parseInt(s6)+"','"+Integer.parseInt(s7)+"')");
// rs = stmt.executeQuery("SELECT * FROM `mvalues` INNER JOIN `building` "
// + "ON `mvalues`.`mesto` = `building`.`IDbuilding` INNER JOIN `matotvetstvenniy`"
// + "ON `mvalues`.`otv` = `matotvetstvenniy`.`IDsotr`;");
//
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: "+ cE.toString());
}
return list;
}
@Override
public List View1() {
try {
stmt = null;
ResultSet rs = null;
list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Properties ConnectionProps = new Properties();
ConnectionProps.setProperty("useUnicode", "TRUE");
ConnectionProps.setProperty("characterEncoding", "cp866");
String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";
Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"
+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"
+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"
+ " ON speciality.`ID_spec` =personal.`ID_spec`"
+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state` WHERE `statement`.`ID_state`='1';");
// rs.next();
while (rs.next()) {
list.add(rs.getString("FIO"));
list.add(rs.getString("age"));
list.add(rs.getString("sex"));
list.add(rs.getString("skill"));
list.add(rs.getString("spec"));
list.add(rs.getString("otdel"));
list.add(rs.getString("boss"));
list.add(rs.getString("state"));
list.add(rs.getString("zarpl"));
}
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: "+ cE.toString());
}
return list;
}
@Override
public List View2() {
try {
stmt = null;
ResultSet rs = null;
list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Properties ConnectionProps = new Properties();
ConnectionProps.setProperty("useUnicode", "TRUE");
ConnectionProps.setProperty("characterEncoding", "cp866");
String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";
Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"
+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"
+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"
+ " ON speciality.`ID_spec` =personal.`ID_spec`"
+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state` WHERE `statement`.`ID_state`='2';");
//rs.next();
while (rs.next()) {
list.add(rs.getString("FIO"));
list.add(rs.getString("age"));
list.add(rs.getString("sex"));
list.add(rs.getString("skill"));
list.add(rs.getString("spec"));
list.add(rs.getString("otdel"));
list.add(rs.getString("boss"));
list.add(rs.getString("state"));
list.add(rs.getString("zarpl"));
}
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: "+ cE.toString());
}
return list;
}
@Override
public int Remove(String fio) {
try {
stmt = null;
ResultSet rs = null;
list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Properties ConnectionProps = new Properties();
ConnectionProps.setProperty("useUnicode", "TRUE");
ConnectionProps.setProperty("characterEncoding", "cp866");
String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";
Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);
stmt = con.createStatement();
int rowsEffected = stmt.executeUpdate("DELETE FROM personal WHERE `FIO` = '"+fio+"' ");
minus++;
if(rowsEffected!=0)return 1;
else return 0;
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
return 0;
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: "+ cE.toString());
return 0;
}
}
@Override
public List View3() {
try {
stmt = null;
ResultSet rs = null;
list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Properties ConnectionProps = new Properties();
ConnectionProps.setProperty("useUnicode", "TRUE");
ConnectionProps.setProperty("characterEncoding", "cp866");
String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";
Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"
+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"
+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"
+ " ON speciality.`ID_spec` =personal.`ID_spec`"
+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state` WHERE `statement`.`ID_state`='3';");
//rs.next();
while (rs.next()) {
list.add(rs.getString("FIO"));
list.add(rs.getString("age"));
list.add(rs.getString("sex"));
list.add(rs.getString("skill"));
list.add(rs.getString("spec"));
list.add(rs.getString("otdel"));
list.add(rs.getString("boss"));
list.add(rs.getString("state"));
list.add(rs.getString("zarpl"));
}
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: "+ cE.toString());
}
return list;
}
//реализация методов Add() – добавление, Remove() – удаление, Edit() – изменение
public int Add(String id, String fio, String age, String sex, String id_spec, String skill, String id_state, String zp) {
try {
stmt = null;
ResultSet rs = null;
list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Properties ConnectionProps = new Properties();
ConnectionProps.setProperty("useUnicode", "TRUE");
ConnectionProps.setProperty("characterEncoding", "cp866");
String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";
Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);
stmt = con.createStatement();
int rowsEffected = stmt.executeUpdate("INSERT INTO personal VALUES "
+ "('"+Integer.parseInt(id)+"','"+fio+"','"+Integer.parseInt(age)
+"','"+sex+"','"+Integer.parseInt(id_spec)+"'"
+ ",'"+Integer.parseInt(skill)+"','"+Integer.parseInt(id_state)+"','"+Integer.parseInt(zp)+"')");
plus++;
if(rowsEffected!=0) {return 1;}
else return 0;
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
return 0;
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: "+ cE.toString());
return 0;
}
catch (NumberFormatException e8) {
System.out.println("NumberFormatException: "+ e8.toString());
return 0;
}
public int Remove(String fio) {
try {
stmt = null;
ResultSet rs = null;
list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Properties ConnectionProps = new Properties();
ConnectionProps.setProperty("useUnicode", "TRUE");
ConnectionProps.setProperty("characterEncoding", "cp866");
String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";
Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);
stmt = con.createStatement();
int rowsEffected = stmt.executeUpdate("DELETE FROM personal WHERE `FIO` = '"+fio+"' ");
minus++;
if(rowsEffected!=0)return 1;
else return 0;
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
return 0;
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: "+ cE.toString());
return 0;
}
}
public int Edit(String id, String name, String val) {
try {
stmt = null;
ResultSet rs = null;
list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Properties ConnectionProps = new Properties();
ConnectionProps.setProperty("useUnicode", "TRUE");
ConnectionProps.setProperty("characterEncoding", "cp866");
String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";
Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);
stmt = con.createStatement();
int rowsEffected = stmt.executeUpdate("UPDATE personal SET "+name+"= '"+val+"' WHERE id='"+id+"'");
if(rowsEffected!=0) {return 1;}
else return 0;
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
return 0;
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: "+ cE.toString());
return 0;
}
catch (NumberFormatException e8) {
System.out.println("NumberFormatException: "+ e8.toString());
return 0;
}
}
// Реализация статистики
public double Stat(double i) {
if(i==1){
return plus;
}
if (i==2)
{return minus;}
if (i==3)
{return (plus+1)/(minus+1);}
else {
return 11;}
}
@Override
public List BestZp(String zp) {
try {
stmt = null;
ResultSet rs = null;
list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Properties ConnectionProps = new Properties();
ConnectionProps.setProperty("useUnicode", "TRUE");
ConnectionProps.setProperty("characterEncoding", "cp866");
String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";
Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"
+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"
+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"
+ " ON speciality.`ID_spec` =personal.`ID_spec`"
+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state` "
+ "WHERE `personal`.`zarpl`>='"+zp+"';");
// rs.next();
while (rs.next()) {
list.add(rs.getString("FIO"));
list.add(rs.getString("age"));
list.add(rs.getString("sex"));
list.add(rs.getString("skill"));
list.add(rs.getString("spec"));
list.add(rs.getString("otdel"));
list.add(rs.getString("boss"));
list.add(rs.getString("state"));
list.add(rs.getString("zarpl"));
}
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: "+ cE.toString());
}
return list;
}
@Override
public List Skill(String skil) {
try {
stmt = null;
ResultSet rs = null;
list = new ArrayList();
Class.forName("com.mysql.jdbc.Driver");
Properties ConnectionProps = new Properties();
ConnectionProps.setProperty("useUnicode", "TRUE");
ConnectionProps.setProperty("characterEncoding", "cp866");
String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";
Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"
+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"
+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"
+ " ON speciality.`ID_spec` =personal.`ID_spec`"
+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state` "
+ "WHERE `personal`.`skill`>'"+skil+"';");
// rs.next();
while (rs.next()) {
list.add(rs.getString("FIO"));
list.add(rs.getString("age"));
list.add(rs.getString("sex"));
list.add(rs.getString("skill"));
list.add(rs.getString("spec"));
list.add(rs.getString("otdel"));
list.add(rs.getString("boss"));
list.add(rs.getString("state"));
list.add(rs.getString("zarpl"));
}
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: "+ cE.toString());
}
return list;
}
}
// файлы оформления CSS
Design.css
body {
font-style: italic;
background-image: url("hd2.jpg")
}
h1{
color:whitesmoke;
text-decoration: blink;
}
Tables.css
body {
font-style: italic;
background-image: url("koala.jpg")
}
h1{
color:whitesmoke;
text-decoration: blink;
}
a:hover {
text-decoration: blink;
color:red;
}
table {
border: 4px double #333;
border-collapse: separate;
width: 100%;
border-spacing: 7px 11px;
}
td {
background: #fc0;
padding: 5px;
border: 1px solid #a52a2a;
}
Stat.css
body {
font-style: italic;
background-image: url("money.jpg")
}
h1{
color:gray;
text-decoration: blink;
}
a:hover {
text-decoration: blink;
color:red;
}
table {
border: 4px double #333;
border-collapse: separate;
width: 100%;
border-spacing: 7px 11px;
}
td {
background: #fc0;
padding: 5px;
border: 1px solid #a52a2a;
}
- Содержание
- Введение
- 1 Описание предметной области системы организации работы отдела кадров
- 2 Постановка задачи на проектирование системы автоматизации работы отдела кадров
- 3 Модели представления системы автоматизированной работы отдела кадров
- 4 Информационная модель системы автоматизированной работы отдела кадров
- 5 Описание алгоритмов реализующих бизнес-логику серверной части проектируемой системы
- 6 Описание руководства пользователя
- 7 Результаты тестирования разработанной системы и оценка выполнения задач
- Заключение
- Список использованных источников
- Приложение г Листинг программного модуля (обязательное)