-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsales_report.jsp
198 lines (183 loc) · 6.32 KB
/
sales_report.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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>
<body style="background-color:#F0F8FF;">
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="java.io.*,java.util.Date.*,java.util.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*"%>
<%
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://group31.cwwhhyxvfxql.us-east-2.rds.amazonaws.com:3306/group31","Yuchen_jin","Jinyuchen2016*");
Statement st = con.createStatement();
java.util.Date today = Calendar.getInstance().getTime();
int id = Integer.parseInt(request.getParameter("theid"));//(Integer)session.getAttribute("id");
out.print("Sales Repoet Generated at:" + today + "<br>"+"By Admin #" + id + "<br><br><hr>");
int total = 0;
//total earning
String str = "SELECT SUM(item_earning) AS total_earning FROM Item_Record";
ResultSet rs=st.executeQuery(str);
out.print("<table style=\"margin: 0px auto;\">");
out.print("<tr>");
out.print("<td>");
out.print("Total earning($)");
out.print("</td>");
out.print("</tr>");
while(rs.next()){
out.print("<tr>");
out.print("<td>");
total = rs.getInt("total_earning");
out.print(total);
out.print("</td>");
out.print("</tr>");
}
out.print("</table>");
out.print("<br>");
//earning per item/type/user
//String str2 = "SELECT item_id, item_earning FROM Item_Record";
String str2 = "SELECT Item.iname, Item_Record.item_earning FROM Item_Record RIGHT JOIN Item ON Item.item_id = Item_Record.item_id ORDER BY Item.iname";
rs=st.executeQuery(str2);
out.print("<table style=\"margin: 0px auto;\">");
out.print("<tr>");
out.print("<td>");
out.print("Item");
out.print("</td>");
out.print("<td>");
out.print("Earning ($)");
out.print("</td>");
out.print("</tr>");
while(rs.next()){
out.print("<tr>");
out.print("<td>");
out.print(rs.getString("iname"));
out.print("</td>");
out.print("<td>");
int earning = rs.getInt("item_earning");
out.print(earning);
out.print("</td>");
out.print("</tr>");
}
out.print("</table>");
out.print("<br>");
String str2_2 = "SELECT scname, SUM(item_earning) as earning FROM (SubCatagory LEFT JOIN Has_SubCatagory ON Has_SubCatagory.sid = SubCatagory.sid) LEFT JOIN Item_Record ON Item_Record.item_id = Has_SubCatagory.item_id GROUP BY scname";
//"SELECT scname, SUM(item_earning) AS earning FROM (Has_SubCatagory LEFT JOIN Item_Record ON Has_SubCatagory.item_id = Item_Record.item_id) LEFT JOIN SubCatagory ON SubCatagory.sid = Has_SubCatagory.sid GROUP BY scname ";
//"SELECT Has_SubCatagory.sid, SUM(Item_Record.item_earning) AS earning FROM Has_SubCatagory LEFT JOIN Item_Record ON Has_SubCatagory.item_id = Item_Record.item_id GROUP BY Has_SubCatagory.sid ";
rs=st.executeQuery(str2_2);
out.print("<table style=\"margin: 0px auto;\">");
out.print("<tr>");
out.print("<td>");
out.print("Item Type");
out.print("</td>");
out.print("<td>");
out.print("Earning ($)");
out.print("</td>");
out.print("</tr>");
while(rs.next()){
out.print("<tr>");
out.print("<td>");
out.print(rs.getString("scname"));
out.print("</td>");
out.print("<td>");
int earning = rs.getInt("earning");
out.print(earning);
out.print("</td>");
out.print("</tr>");
}
out.print("</table>");
out.print("<br>");
String str2_3 = "SELECT edaccount_id, SUM(user_earning) AS earning FROM User_Record GROUP BY edaccount_id";
rs=st.executeQuery(str2_3);
out.print("<table style=\"margin: 0px auto;\">");
out.print("<tr>");
out.print("<td>");
out.print("User");
out.print("</td>");
out.print("<td>");
out.print("Earning($)");
out.print("</td>");
out.print("</tr>");
while(rs.next()){
out.print("<tr>");
out.print("<td>");
out.print(rs.getString("edaccount_id"));
out.print("</td>");
out.print("<td>");
int earning = rs.getInt("earning");
out.print(earning);
out.print("</td>");
out.print("</tr>");
}
out.print("</table>");
out.print("<br>");
//best selling item
String str3 = "SELECT iname FROM Item WHERE item_id = (SELECT item_id FROM (SELECT item_id, MAX(item_earning) FROM Item_Record) AS T)";
rs=st.executeQuery(str3);
out.print("<table style=\"margin: 0px auto;\">");
out.print("<tr>");
out.print("<td>");
out.print("Best Selling Item");
out.print("</td>");
out.print("</tr>");
while(rs.next()){
out.print("<tr>");
out.print("<td>");
out.print(rs.getString("iname"));
out.print("</td>");
out.print("</tr>");
}
out.print("</table>");
out.print("<br>");
//best buyer
String str4 = "SELECT eaccount_id , MAX(sum) AS amount FROM (SELECT eaccount_id,SUM(amount) AS sum FROM Bid WHERE is_winning = true) AS T";
rs=st.executeQuery(str4);
out.print("<table style=\"margin: 0px auto;\">");
out.print("<tr>");
out.print("<td>");
out.print("Best Buyer ID");
out.print("</td>");
out.print("<td>");
out.print("Amount");
out.print("</td>");
out.print("</tr>");
while(rs.next()){
out.print("<tr>");
out.print("<td>");
out.print(rs.getString("eaccount_id"));
out.print("</td>");
out.print("<td>");
int earning = rs.getInt("amount");
out.print(earning);
out.print("</td>");
out.print("</tr>");
}
out.print("</table>");
out.print("<br>");
String gen = "INSERT INTO Generate(sdate,adaccount_id)" + "VALUE(?,?)";
String sale = "INSERT INTO Sales_Report(date,total_earning)" + "VALUE(?,?)";
PreparedStatement ps = con.prepareStatement(sale);
ps.setTimestamp(1, new java.sql.Timestamp(today.getTime()));
//out.print(total);
ps.setInt(2, total);
ps.executeUpdate();
ps = con.prepareStatement(gen);
ps.setTimestamp(1, new java.sql.Timestamp(today.getTime()));
ps.setInt(2, id);
ps.executeUpdate();
con.close();
}catch (Exception ex) {
out.print("Error occured");
}
%>
</body>
</html>