Разработка программного средства автоматизации обслуживания заявок пользователей локальной вычислительной сети филиала РГУ нефти и газа имени И.М. Губкина в г. Оренбурге

while(result.next())

{

ArrayList<String> arRow = new ArrayList<String>(colCount);

arRow.add(result.getString(1));

arRow.add(result.getString(2));

selAllRoomTypes.add(arRow);

}

pst.close();

connect.close();

}

catch(SQLException e)

{

throw e;

}

return selAllRoomTypes;

}

}

//Модуль доступа к таблице пользователей БД DAOUsers.java

package avk;

import java.sql.*;

import java.util.*;

import java.util.Date;

public class DAOusers {

private Connection connect = null;

private PreparedStatement pst = null;

//private Statement st = null;

//private String sql;

private ResultSet result = null;

private ResultSetMetaData resMet;

public DAOusers() throws ClassNotFoundException, SQLException

{

this.connect = getConnection();

}

public Connection getConnection() throws ClassNotFoundException, SQLException {

try

{

Class.forName("org.postgresql.Driver");

}

catch(ClassNotFoundException e)

{

//return null;

throw e;

}

String url, user, password;

url = "jdbc:postgresql://127.0.0.1/zit";

user = "postgres";

password = "xmmGQAKq";

try

{

return DriverManager.getConnection(url, user, password);

}

catch(SQLException e)

{

throw e;

}

}

public ArrayList<String> getUserDate(String login, String pas) throws SQLException

{

String sql = "SELECT \"id\",\"login\", \"pas\",\"Surname\",\"Name\",\"FName\",\"shortName\",\"typeUser\" FROM public.\"Users\" WHERE \"Users\".\"login\" =? AND \"Users\".pas = ?;";

ArrayList ar = null;

try

{

pst = connect.prepareStatement(sql);

pst.setString(1, login);

pst.setString(2, pas);

result = pst.executeQuery();

if(result.next())

{

ar = new ArrayList(8);

ar.add(result.getString(1));

ar.add(result.getString(2));

ar.add(result.getString(3));

ar.add(result.getString(4));

ar.add(result.getString(5));

ar.add(result.getString(6));

ar.add(result.getString(7));

Integer i;

ar.add((i = result.getInt(8)).toString());

}

else return null;

if(result.wasNull())return null;

pst.close();

connect.close();

}

catch(SQLException e)

{

return null;

//throw e;

}

return ar;

}

public ArrayList<ArrayList<String>> selAllUserDate() throws SQLException

{

String sql = "SELECT \"id\", \"shortName\", \"typeUser\" FROM public.\"Users\";";

ArrayList<ArrayList<String>> selAllUser = null;

try

{

pst = connect.prepareStatement(sql);

result = pst.executeQuery();

resMet = result.getMetaData();

int colCount = resMet.getColumnCount();

selAllUser = new ArrayList<ArrayList<String>>();

while(result.next())

{

ArrayList<String> arRow = new ArrayList<String>(colCount);

arRow.add(result.getString(1));

arRow.add(result.getString(2));

arRow.add(result.getString(3));

selAllUser.add(arRow);

}

pst.close();

connect.close();

}

catch(SQLException e)

{

throw e;

}

return selAllUser;

}

public boolean admAccept(String flag, String id)

{

String sql = "UPDATE \"Users\" SET \"typeUser\"=? WHERE id = ?;";

try

{

pst = connect.prepareStatement(sql);

pst.setInt(1, Integer.parseInt(flag));

pst.setInt(2, Integer.parseInt(id));

pst.executeUpdate();

pst.close();

connect.close();

}

catch(SQLException e)

{

return false;

}

return true;

}

public boolean InsUserDate(String login, String pas, String Surname, String Name, String FName) throws SQLException

{

int typeUser = 0;

String shortName = Surname+" "+Name.substring(0, 1) + "."+FName.substring(0, 1) + ".";

String sql = "INSERT INTO \"Users\"(\"login\", \"pas\",\"Surname\", \"Name\", \"FName\", \"shortName\", \"typeUser\")VALUES (?, ?, ?, ?, ?, ?, ?);";

try

{

pst = connect.prepareStatement(sql);

pst.setString(1, login);

pst.setString(2, pas);

pst.setString(3, Surname);

pst.setString(4, Name);

pst.setString(5, FName);

pst.setString(6, shortName);

pst.setInt(7, typeUser);

pst.executeUpdate();

pst.close();

connect.close();

}

catch(SQLException e)

{

//return false;

throw e;

}

return true;

}

}

// Модуль, открывающий доступ к странице редактирования прав пользователей AdmAccept.java

package avk;

import java.io.IOException;

import java.sql.SQLException;

import javax.servlet.ServletException;

import javax.servlet.http.*;

public class AdmAccept extends HttpServlet {

protected void doGet(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

String flag, id;

id = req.getParameter("id");

flag = req.getParameter("flag");

try

{

DAOusers dao = new DAOusers();

if(dao.admAccept(flag.equals("0") ? "1" : "0", id))resp.sendRedirect("admUser.jsp");

else resp.sendRedirect("error.html?errorAdd");

}

catch(SQLException e)

{

resp.sendRedirect("error.html?errorDB");

}

catch(ClassNotFoundException e)

{

resp.sendRedirect("error.html?errorCNotF");

}

}

}

//Модуль создания интерфейса страницы редактирования прав пользователей admUser.jsp

<?xml version="1.0" encoding="utf8" ?>

<%@page import="java.sql.SQLException"%>

<%@page import="java.util.ArrayList"%>

<%@page import="avk.DAOusers"%>

<%@ page language="java" contentType="text/html; charset=utf8"

pageEncoding="utf8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf8" />

<title>Заявки</title>

</head>

<body>

<div style="text-align: center;"><big><big><big><big><big><span

style="font-weight: bold;">Управление пользователями(<%= session.getAttribute("shortName") %>)<br><br>

</span></big></big></big></big></big>

<table style="text-align: left; width: 340px; height: 60px;"

align="center" border="1" cellpadding="2"

cellspacing="2">

<tbody>

<tr>

<td style="width: 149px; text-align: right;"><span

style="font-weight: bold;">Пользователь</span></td>

<td style="width: 171px; text-align: center;"><span

style="font-weight: bold;">Администратор</span></td>

</tr>

<%

ArrayList<ArrayList<String>> arSel=null;

HttpSession ses = request.getSession(false);

String atr;

if(ses==null) {%>

<big><big><big><big><big>ses==null</big></big></big></big>

<jsp:forward page="info2.html"/>

<%;} else

{

atr = (String)ses.getAttribute("typeUser");

if(atr.equals("1"))

{

try

{

DAOusers dao = new DAOusers();

arSel = dao.selAllUserDate();

}

catch(SQLException e)

{%>

<jsp:forward page="error.html"/>

<%;}

String str = null;

for(ArrayList<String> ar : arSel)

{ str = ar.get(2).equals("0") ? "НЕТ" : "ДА";%>

<tr>

<td style="width: 149px; text-align: right;"><%= ar.get(1) %>

</td>

<td

style="width: 171px; text-align: center; font-weight: bold;"><big><big><a

name="Users" href="AdmAccept?id=<%= ar.get(0) %>&flag=<%=ar.get(2)%>"> <%= str%>

</a></big></big></td>

</tr>

<%;}

if(arSel.isEmpty())

{

%>

<tr align="center">

<td colspan="2" rowspan="1"><span

style="font-weight: bold;">Пользователи отсутствуют</span></td>

</tr>

<%;}} if(atr.equals("0")){%>

<jsp:forward page="info2.html"/>

<% ;}}%>

</tbody>

</table>

<big><big><big><big>

<br>

<a href="menu.html" name="menu">Главная</a></span></big></big></big></big></div>

</div>

</body>

</html>

//Модуль доступа к таблице заявок на мультимедиа оборудование БД //DAORequest.java

package avk;

import java.sql.*;

import java.util.*;

import java.util.Date;

public class DAORequest implements IDAORequest {

private Connection connect = null;

private PreparedStatement pst = null;

//private Statement st = null;

//private String sql;

private ResultSet result = null;

private ResultSetMetaData resMet;

public DAORequest() throws ClassNotFoundException, SQLException

{

this.connect = getConnection();

}

public Connection getConnection() throws ClassNotFoundException, SQLException {

try

{

Class.forName("org.postgresql.Driver");

}

catch(ClassNotFoundException e)

{

//return null;

throw e;

}

String url, user, password;

url = "jdbc:postgresql://127.0.0.1/request";

user = "postgres";

password = "xmmGQAKq";

try

{

return DriverManager.getConnection(url, user, password);

}

catch(SQLException e)

{

throw e;

}

}

public boolean addRequest(String forName, String name, String tarqetDate, int tarqetTimeH, int tarqetTimeM, int tarqetAud, String targetMulti) throws SQLException {

String sql = "INSERT INTO \"requests\"(\"forName\", \"name\", \"dateTime\", \"tarqetAud\", \"targetMulti\", "+

"\"requestAccepted\", \"requestDoned\", \"dateTimeRegistration\")VALUES(?, ?, ?, ?, ?, ?, ?, ?);";

try

{

pst = connect.prepareStatement(sql);

pst.setString(1, forName);

pst.setString(2, name);

pst.setTimestamp(3, getTimestamp(tarqetDate,((Integer)tarqetTimeH).toString(),((Integer)tarqetTimeM).toString()));

pst.setInt(4, tarqetAud);

pst.setString(5, targetMulti);

pst.setBoolean(6, false);

pst.setBoolean(7, false);

Timestamp timestamp = new Timestamp((new Date()).getTime());

pst.setTimestamp(8, timestamp);

pst.executeUpdate();

}

catch(SQLException e)

{

throw e;

}

/*finally

{

try{

pst.close();

connect.close();

}

catch(SQLException e)

{

throw e;

}

}*/

return true;

}

public ArrayList<ArrayList<String>> todayRequest() throws SQLException {

return callSel(0, 0, 0, 0, 0, 1);

}

public ArrayList<ArrayList<String>> tomorrowRequest() throws SQLException {

return callSel(0, 0, 1, 0, 0, 2);

}

public ArrayList<ArrayList<String>> endWeekRequest() throws SQLException {

GregorianCalendar cal = new GregorianCalendar();

int curentDayOfWeek = cal.get(GregorianCalendar.DAY_OF_WEEK);

if (curentDayOfWeek == 1) curentDayOfWeek = 8;

int shift = 8 - curentDayOfWeek + 1;

return callSel(0, 0, 0, 0, 0, shift);

}

public ArrayList<ArrayList<String>> weekendRequest() throws SQLException {

GregorianCalendar cal = new GregorianCalendar();

int curentDayOfWeek = cal.get(GregorianCalendar.DAY_OF_WEEK);

if (curentDayOfWeek == 1) curentDayOfWeek = 8;

int shift = 8 - curentDayOfWeek + 1;

return callSel(0, 0, shift-2, 0, 0, shift);

}

public ArrayList<ArrayList<String>> timeIntervalRequest() throws SQLException {

return callSel(0, 0, 1, 0, 0, 2);

}

public boolean acceptingRequest(String id, String bool){

String sql = "UPDATE requests SET \"requestAccepted\"=? WHERE \"idRequest\"=?;";

try

{

pst = connect.prepareStatement(sql);

pst.setBoolean(1, new Boolean(bool));

pst.setInt(2, Integer.parseInt(id));

pst.executeUpdate();

pst.close();

connect.close();

}

catch(SQLException e)

{

return false;

}

return true;

}

public boolean donedRequest(String id, String bool){

String sql = "UPDATE requests SET \"requestDoned\"=? WHERE \"idRequest\"=?;";

try

{

pst = connect.prepareStatement(sql);

pst.setBoolean(1, new Boolean(bool));

pst.setInt(2, Integer.parseInt(id));

pst.executeUpdate();

pst.close();

connect.close();

}

catch(SQLException e)

{

return false;

}

return true;

}

public ArrayList<String> getUserDate(String login, String pas) throws SQLException

{

String sql = "SELECT \"id\", \"fName\", \"lName\", \"mName\", \"shortName\", \"login\", \"pas\", \"typeUser\" FROM public.\"user\" WHERE \"user\".\"login\" =? AND \"user\".pas = ?;";

ArrayList ar = null;

try

{

pst = connect.prepareStatement(sql);

pst.setString(1, login);

pst.setString(2, pas);

result = pst.executeQuery();

if(result.next())

{

ar = new ArrayList(8);

ar.add(result.getString(1));

ar.add(result.getString(2));

ar.add(result.getString(3));

ar.add(result.getString(4));

ar.add(result.getString(5));

ar.add(result.getString(6));

ar.add(result.getString(7));

Integer i;

ar.add((i = result.getInt(8)).toString());

}

else return null;

if(result.wasNull())return null;

pst.close();

connect.close();

}

catch(SQLException e)

{

return null;

//throw e;

}

return ar;

}

public ArrayList<ArrayList<String>> selAllUserDate() throws SQLException

{

String sql = "SELECT \"id\", \"shortName\", \"typeUser\" FROM public.\"user\";";

ArrayList<ArrayList<String>> selAllUser = null;

try

{

pst = connect.prepareStatement(sql);

result = pst.executeQuery();

resMet = result.getMetaData();

int colCount = resMet.getColumnCount();

selAllUser = new ArrayList<ArrayList<String>>();

while(result.next())

{

ArrayList<String> arRow = new ArrayList<String>(colCount);

arRow.add(result.getString(1));

arRow.add(result.getString(2));

arRow.add(result.getString(3));

selAllUser.add(arRow);

}

pst.close();

connect.close();

}

catch(SQLException e)

{

throw e;

}

return selAllUser;

}

public boolean admAccept(String flag, String id)

{

String sql = "UPDATE \"user\" SET \"typeUser\"=? WHERE id = ?;";

try

{

pst = connect.prepareStatement(sql);

pst.setInt(1, Integer.parseInt(flag));

pst.setInt(2, Integer.parseInt(id));

pst.executeUpdate();

pst.close();

connect.close();

}

catch(SQLException e)

{

return false;

}

return true;

}

public boolean InsUserDate(String login, String pas, String fName, String lName, String mName) throws SQLException

{

int typeUser = 0;

String shortName = lName+" "+fName.substring(0, 1) + "."+mName.substring(0, 1) + ".";

String sql = "INSERT INTO \"user\"(\"fName\", \"lName\", \"mName\", \"shortName\", \"login\", \"pas\", \"typeUser\")VALUES (?, ?, ?, ?, ?, ?, ?);";

try

{

pst = connect.prepareStatement(sql);

pst.setString(1, fName);

pst.setString(2, lName);

pst.setString(3, mName);

pst.setString(4, shortName);

pst.setString(5, login);

pst.setString(6, pas);

pst.setInt(7, typeUser);

pst.executeUpdate();

pst.close();

connect.close();

}

catch(SQLException e)

{

//return false;

throw e;

}

return true;

}

private ArrayList<ArrayList<String>> commonRequest(String sqlSuffics) throws SQLException

{

String sql = "SELECT \"idRequest\", \"forName\", \"name\", \"dateTime\","+

"\"tarqetAud\", \"targetMulti\", \"requestAccepted\","+

"\"requestDoned\", \"dateTimeRegistration\" FROM public.requests "+sqlSuffics +" ORDER BY \"dateTime\";";

pst = connect.prepareStatement(sql);

result = pst.executeQuery();

resMet = result.getMetaData();

int colCount = resMet.getColumnCount();

GregorianCalendar cal = new GregorianCalendar(Locale.getDefault());

Formatter frm;

ArrayList<ArrayList<String>> selectRequest = new ArrayList<ArrayList<String>>();

for(int i = 1; i <= colCount; i++)

while(result.next())

{

ArrayList<String> arRow = new ArrayList<String>(colCount);

for(int j = 1; j <= colCount; j++)

{

arRow.add(((Integer)result.getInt(1)).toString());

arRow.add(result.getString(2));

arRow.add(result.getString(3));

cal.setTimeInMillis(result.getTimestamp(4).getTime());

frm = new Formatter();

frm.format("%td.%tm.%tY %tk:%tM", cal,cal,cal,cal,cal);

arRow.add(frm.toString());

frm.close();

arRow.add(((Integer)result.getInt(5)).toString());

arRow.add(result.getString(6));

arRow.add(result.getBoolean(7) ? "ДА" : "НЕТ");

arRow.add(result.getBoolean(8) ? "ДА" : "НЕТ");

cal.setTimeInMillis(result.getTimestamp(9).getTime());

frm = new Formatter();

frm.format("%td.%tm.%tY %tk:%tM:%tS", cal,cal,cal,cal,cal,cal);

arRow.add(frm.toString());

frm.close();

}

selectRequest.add(arRow);

}

pst.close();

connect.close();

return selectRequest;

}

private String getTime(int shiftYear, int shiftMonth, int shiftDay)

{

GregorianCalendar cal = new GregorianCalendar();

cal.set(GregorianCalendar.HOUR_OF_DAY, 0);

cal.set(GregorianCalendar.DAY_OF_MONTH, cal.get(GregorianCalendar.DAY_OF_MONTH)+shiftDay);

cal.set(GregorianCalendar.MONTH, cal.get(GregorianCalendar.MONTH)+shiftMonth);

cal.set(GregorianCalendar.YEAR, cal.get(GregorianCalendar.YEAR)+shiftYear);

Formatter frm = new Formatter();

frm.format("%td.%tm.%tY", cal,cal,cal);

return frm.toString();

}

private String getSqlSuffics(String date, String date1)

{

return

"WHERE requests.\"dateTime\" >= \'"+date+"\' AND requests.\"dateTime\" <= \'"+date1+"\'";

}

private ArrayList<ArrayList<String>> callSel(int shiftYear, int shiftMonth, int shiftDay,int shiftYear1, int shiftMonth1, int shiftDay1) throws SQLException

{

try

{

return commonRequest(getSqlSuffics(getTime(shiftYear, shiftMonth, shiftDay), getTime(shiftYear1, shiftMonth1, shiftDay1)));

}

catch(SQLException e)

{

throw e;

}

}

public Timestamp getTimestamp(String date, String h, String m)

{

int year, month, dayOfMonth, hours = Integer.valueOf(h), minutes = Integer.valueOf(m);

StringTokenizer strTok = new StringTokenizer(date, ".");

dayOfMonth = Integer.valueOf(strTok.nextToken());

month = Integer.valueOf(strTok.nextToken());

year = Integer.valueOf(strTok.nextToken());

GregorianCalendar cal = new GregorianCalendar(year, month-1, dayOfMonth, hours, minutes);

Timestamp timeStamp = new Timestamp(cal.getTime().getTime());

return timeStamp;

}

}

//Модуль выборки заявок по определенным периодам SelRequest.java

import java.io.*;

import java.sql.SQLException;

import java.util.*;

import javax.servlet.ServletException;

import javax.servlet.http.*;

public class SelRequests extends HttpServlet {

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//String sel1 = req.getParameter("sel");

if(secureTested())

{

/*HttpSession session = req.getSession();

String sel = null;

if(session.getAttribute("sel") == null)

{

sel = req.getParameter("sel");

session.setAttribute("sel", sel);

}else sel = (String)session.getAttribute("sel");*/

String sel = req.getParameter("sel");

/*if(sel == null)*/

resp.setCharacterEncoding("utf-8");

resp.setContentType("text/html");

PrintWriter out = resp.getWriter();

ArrayList<ArrayList<String>> arSel = null;

String info = null;

try

{

DAORequest dao = new DAORequest();

switch(Integer.parseInt(sel))

{

case 0: {arSel=dao.todayRequest(); info = "На сегодня"; break;}

case 1: {arSel=dao.tomorrowRequest(); info = "На завтра"; break;}

case 2: {arSel=dao.endWeekRequest(); info = "До конца недели"; break;}

case 3: {arSel=dao.weekendRequest(); info = "На выходные"; break;}

}

}

catch(SQLException e)

{

}

catch(ClassNotFoundException e)

{

}

out.println("<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");

out.println("<html>");

out.println("<head>");

out.println("<meta content=\"text/html; charset=utf-8\"");

out.println("http-equiv=\"content-type\">");

out.println("<title>Заявки</title>");

out.println("</head>");

out.println("<body>");

out.println("<div style=\"text-align: center; font-weight: bold;\"><big><big><big><big>Заявки " + info);

out.println("<br>");

out.println("<br>");

out.println("</big></big></big></big>");

out.println("<div style=\"text-align: center;\"></div>");

out.println("<table style=\"text-align: left; width: 100%;\" align=\"center\"");

out.println("border=\"1\" cellpadding=\"2\" cellspacing=\"2\">");

out.println("<tbody>");

out.println("<tr>");

out.println("<td style=\"text-align: center; width: 102px;\"><big><big><small><small>Запросил(а)</small></small><br>");

out.println("</big></big></td>");

out.println("<td style=\"text-align: center; width: 101px;\"><big><big><small><small>Оформил(а)</small></small></big></big></td>");

out.println("<td style=\"text-align: center; width: 88px;\">Время<br>");

out.println("выполнения</td>");

out.println("<td style=\"text-align: center; width: 21px;\">№<br>");

out.println("ауд.</td>");

out.println("<td style=\"text-align: center; width: 311px;\">Средство");

out.println("мультимедиа</td>");

out.println("<td style=\"text-align: center; width: 50px;\">Приня-<br>");

out.println("та<br>");

out.println("</td>");

out.println("<td style=\"text-align: center; width: 46px;\">Выпол-<br>");

out.println("нена");

out.println("</td>");

out.println("<td style=\"text-align: center; width: 87px;\">Время");

out.println("регистрации</td>");

out.println("</tr>");

for(ArrayList<String> ar : arSel)

{

out.println("<tr>");//Boolean bol = new

out.println("<td style=\"text-align: center; width: 102px;\"><small>"+ar.get(1)+"</small></td>");

out.println("<td style=\"text-align: center; width: 101px;\"><small>"+ar.get(2)+"</small></td>");

out.println("<td style=\"text-align: center; width: 88px;\"><small>"+ar.get(3)+"</small></td>");

out.println("<td style=\"text-align: center; width: 21px;\"><small>"+ar.get(4)+"</small></td>");

out.println("<td style=\"text-align: center; width: 311px;\"><small>"+ar.get(5)+"</small></td>");

out.println("<td style=\"text-align: center; width: 50px;\"><big><big>");

String strForA = "?sel=%s&id=%s&flag=%s&field=%s";

Formatter frm = new Formatter();

frm.format(strForA, sel, ar.get(0), ar.get(6).equals("ДА") ? "true":"false","0");

HttpSession sesion;

sesion = req.getSession(true);

String admUser = (String)sesion.getAttribute("typeUser");

boolean bol = (admUser.equals("1") ? true : false);

String h = frm.toString();

frm = new Formatter();

frm.format(strForA, sel, ar.get(0), ar.get(7).equals("ДА") ? "true":"false","1");

String h1 = frm.toString();

if(bol)

{

out.println("<a href=\"DoneingAccepting"+h+"\" name=\"submitRequest\">"+ar.get(6)+"</a></big></big></td>");

out.println("<td style=\"text-align: center; width: 46px;\"><big><big><a");

out.println("href=\"DoneingAccepting"+h1+"\" name=\"showRequest\">"+ar.get(7)+"</a></big></big></td>");

}

else

{

out.println(ar.get(6)+"</big></big></td>");

out.println("<td style=\"text-align: center; width: 46px;\"><big><big>"+ar.get(7)+"</big></big></td>");

}

out.println("<td style=\"text-align: center; width: 87px;\"><small>"+ar.get(8)+"</small></td>");

out.println("</tr>");

}

out.println("<tr align=\"center\">");

out.println("<td colspan=\"8\" rowspan=\"1\"><big><big><big><big><a");

out.println("href=\"showRequest.html\" name=\"index\">Посмотреть еще</a></big></big></big></big></td>");

out.println(" </tr>");

out.println("</tbody></table></div></body></html>");

}

//resp.sendRedirect("/");

}

private boolean secureTested()

{

return true;

}

}

Размещено на Allbest.ru