forked from halnigalni/DB_Team3_Phase4
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathManagerAccount.jsp
143 lines (119 loc) · 4.37 KB
/
ManagerAccount.jsp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<%@ page language="java"
import="java.text.*, java.sql.*, java.util.ArrayList"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR" />
<title>KNU MOVIE_TEAM3</title>
</head>
<form action = "middle.jsp" method = "POST">
<body
style="margin-left: 200px; margin-right: 200px; text-align: center">
<%
String serverIP = "localhost";
String strSID = "orcl";
String portNum = "1521";
String user = "university";
String pass = "comp322";
String url = "jdbc:oracle:thin:@"+serverIP+":"+portNum+":"+strSID;
String sql="";
String id = (String)session.getAttribute("manager_id"); // 사람id 받아옴.
String name="";
ArrayList<Integer> titleidlist = new ArrayList<Integer>();
ArrayList<String>titlelist = new ArrayList<String>();
ArrayList<String> score = new ArrayList<String>();
String avgscore="";
Connection conn = null;
Statement stmt=null;
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection(url,user,pass);
try {
conn.setAutoCommit(false);
stmt = conn.createStatement();
sql = "SELECT name FROM ACCOUNT WHERE Account_id = '"+id+"'";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next())
{
name = rs.getString("name");
}
rs.close();
} catch(SQLException ex)
{
//System.err.println("sql error = "+ex.getMessage());
System.exit(1);
}
%>
<div style="text-align: center;">
<h2>
관리자:
<%=name%></h2>
</div>
<div>
<h4>내가 올린 영상물</h4>
<div style="border: solid; border-color: black;">
<%
try {
conn.setAutoCommit(false);
stmt = conn.createStatement();
sql = "SELECT title_id, title FROM ACCOUNT, MOVIE WHERE admin_id = Account_id AND Account_id='" + id + "'";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next())
{
titleidlist.add(rs.getInt(1));
titlelist.add(rs.getString(2));
}
}catch(SQLException ex)
{
System.err.println("sql error = "+ex.getMessage());
System.exit(1);
}
//관리자가 올린 영상물들의 title_id 를 titlelist에 담음
//titlelist 에 있는 애들의 title_id, title, 평점을 보여줌
// System.out.println("titlelist size:"+titlelist.size());
for(int i =0; i<titleidlist.size();i++)
{
try {
conn.setAutoCommit(false);
stmt = conn.createStatement();
sql = "select Mt_id, AVG(Score) from rating where Mt_id = "+titleidlist.get(i)+" group by Mt_id";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next())
{
String rate = rs.getString(2);
if(rate.length()>4)
{
rate = rate.substring(0,3);
score.add(rate);
}
else
{
score.add(rate);
}
}
else{
score.add("0");
}
}catch(SQLException ex)
{
System.err.println("sql error = "+ex.getMessage());
System.exit(1);
}
}
for(int i =0; i<titleidlist.size();i++)
{
out.println("<br> <input type=\"radio\" name = \"id\" value = \"" + titleidlist.get(i) + "\">"+ titlelist.get(i) +" : "+score.get(i)+ "</input>");
}
session.setAttribute("manager_id",id);
%>
</div>
</div>
<div>
<input type="submit" value="관리">
<input type="button" value="새로운영상등록" onclick="location.href='addmovie.jsp'">
<input type="button" value="로그아웃" onclick="location.href='Login.jsp'">
</div>
</form>
</body>
</html>