· 7 years ago · May 12, 2019, 06:38 PM
1import java.io.IOException;
2import java.sql.*;
3import java.text.DateFormat;
4import java.text.SimpleDateFormat;
5import java.util.Date;
6import java.util.Random;
7
8import static java.lang.Math.abs;
9
10public class DataGenerator {
11
12 Connection connection;
13
14 public DataGenerator(Connection connection) {
15 this.connection = connection;
16 }
17
18 public String[] conferenceName = {"Consumer Electronics Show", "IBM Think", "SHARE", "EmTech Digital",
19 "Google Cloud Next", "Dublin Tech Summit", "OReilly Media Artificial Intelligence", "Digital Enterprise Show",
20 "TNW Conference (The Next Web)", "Citrix Synergy", "Augmented World Expo", "IDUG Tech Conference",
21 "Gartner CIO & IT Executive Summit", "Digital Workplace Experience", "CeBIT", "IFA", "Pure//Accelerate",
22 "Strata Data Conference", "IT Arena", "World Summit AI", "IP EXPO Nordic", "Microsoft Ignite", "AWS re:Invent",
23 "Gartner IT Infrastructure"};
24
25 private Date currentDate = new Date();
26 private DateFormat dateFormatYear = new SimpleDateFormat("yyyy");
27 private DateFormat dateFormatMonth = new SimpleDateFormat("MM");
28 private int currentYear = Integer.parseInt(dateFormatYear.format(currentDate));
29 private int currentMonth = Integer.parseInt(dateFormatMonth.format(currentDate));
30 private Random random = new Random();
31
32 // generacja konferencji na pastYears and futureYears
33 int pastYears = 2;
34 int futureYears = 1;
35 // liczba konferencji w roku
36 int numberOfConferencesInYear = 24;
37 int customerNumber = 500;
38 int numberOfGenerateReservation = 1;
39
40 public void generateConferencesEventsWorkshops() throws SQLException {
41
42 int id = 0;
43
44 for (int year = currentYear - pastYears; year <= currentYear + futureYears; year++) {
45 int first = 0;
46 int last = numberOfConferencesInYear;
47
48 if (year == currentYear - pastYears) {
49 first = currentMonth * 2;
50 last = numberOfConferencesInYear;
51 }
52 if (year == currentYear + futureYears) {
53 first = 0;
54 last = currentMonth * 2;
55 }
56
57 for (int i = first; i < last; i++) {
58 id++;
59 int month = (abs(random.nextInt()) % 12) + 1;
60
61 if (year == currentYear - pastYears) {
62 month = (abs(random.nextInt()) % (12 - currentMonth)) + currentMonth;
63 }
64 if (year == currentYear + futureYears) {
65 month = (abs(random.nextInt()) % currentMonth) + 1;
66 }
67 int startDay = (abs(random.nextInt()) % 23) + 1;
68 int endDay = (startDay + abs(random.nextInt()) % 2) + 1;
69
70 CallableStatement addConference = this.connection.prepareCall("EXECUTE dbo.add_conference ?,?,?,?");
71 addConference.registerOutParameter("o_id", Types.INTEGER);
72 addConference.setString("i_name", conferenceName[i] + " " + year);
73 addConference.setString("i_start_date", month + "/" + startDay + "/" + year + " 00:00:00");
74 addConference.setString("i_end_date", month + "/" + endDay + "/" + year + " 23:59:59");
75 addConference.execute();
76
77 int conferenceId = addConference.getInt("o_id");
78 for (int k = 0; k <= (endDay - startDay); k++) {
79 CallableStatement addEvent = this.connection.prepareCall("EXECUTE dbo.add_event ?,?,?,?,?,?,?,?,?");
80
81 int startEventHour = (abs(random.nextInt()) % 4) + 8;
82 int endEventHour = (abs(random.nextInt()) % 6) + 17;
83
84 addEvent.registerOutParameter("o_id", Types.INTEGER);
85 addEvent.setNull("i_parent_id", Types.NULL);
86 addEvent.setInt("i_conference_id", conferenceId);
87 addEvent.setString("i_name", conferenceName[i] + " - Day " + (k + 1));
88 addEvent.setString("i_start_date", month + "/" + startDay + "/" + year + " " + startEventHour + ":00:00");
89 addEvent.setString("i_end_date", month + "/" + endDay + "/" + year + " " + endEventHour + ":00:00");
90 addEvent.setInt("i_attendees_limit", abs((random.nextInt()) % 3) * 50 + 150);
91 addEvent.setInt("i_price", abs((random.nextInt()) % 5) * 25 + 50);
92 addEvent.setInt("i_student_discount", abs((random.nextInt()) % 4) * 5);
93 addEvent.execute();
94
95 int startPriceTresholdYear1 = year;
96 int startPriceTresholdMonth1 = month - 7;
97 if (startPriceTresholdMonth1 <= 0) {
98 startPriceTresholdYear1 = startPriceTresholdYear1 - 1;
99 startPriceTresholdMonth1 = 12 + startPriceTresholdMonth1;
100 }
101 int endPriceTresholdYear1 = year;
102 int endPriceTresholdMonth1 = month - 5;
103 if (endPriceTresholdMonth1 <= 0) {
104 endPriceTresholdYear1 = endPriceTresholdYear1 - 1;
105 endPriceTresholdMonth1 = 12 + endPriceTresholdMonth1;
106 }
107 int startPriceTresholdYear2 = year;
108 int startPriceTresholdMonth2 = month - 5;
109 if (startPriceTresholdMonth2 <= 0) {
110 startPriceTresholdYear2 = startPriceTresholdYear2 - 1;
111 startPriceTresholdMonth2 = 12 + startPriceTresholdMonth2;
112 }
113 int endPriceTresholdYear2 = year;
114 int endPriceTresholdMonth2 = month - 3;
115 if (endPriceTresholdMonth2 <= 0) {
116 endPriceTresholdYear2 = endPriceTresholdYear2 - 1;
117 endPriceTresholdMonth2 = 12 + endPriceTresholdMonth2;
118 }
119
120 int eventId = addEvent.getInt("o_id");
121 CallableStatement addPriceTreshold1 = this.connection.prepareCall("EXECUTE dbo.add_event_price_thresholds ?,?,?,?");
122 addPriceTreshold1.setInt("i_event_id", eventId);
123 addPriceTreshold1.setInt("i_discount", (abs((random.nextInt()) % 2) + 1) * 10);
124 addPriceTreshold1.setString("i_start_date", startPriceTresholdMonth1 + "/" + startDay + "/" + startPriceTresholdYear1 + " 00:00:00");
125 addPriceTreshold1.setString("i_end_date", endPriceTresholdMonth1 + "/" + startDay + "/" + endPriceTresholdYear1 + " 00:00:00");
126 addPriceTreshold1.execute();
127
128 CallableStatement addPriceTreshold2 = this.connection.prepareCall("EXECUTE dbo.add_event_price_thresholds ?,?,?,?");
129 addPriceTreshold2.setInt("i_event_id", eventId);
130 addPriceTreshold2.setInt("i_discount", (abs((random.nextInt()) % 2) + 1) * 5);
131 addPriceTreshold2.setString("i_start_date", startPriceTresholdMonth2 + "/" + startDay + "/" + startPriceTresholdYear2 + " 00:00:00");
132 addPriceTreshold2.setString("i_end_date", endPriceTresholdMonth2 + "/" + startDay + "/" + endPriceTresholdYear2 + " 00:00:00");
133 addPriceTreshold2.execute();
134
135 int numberOfWorkshop = (abs(random.nextInt()) % 3) + 2;
136 for (int m = 1; m <= numberOfWorkshop; m++) {
137 addEvent = this.connection.prepareCall("EXECUTE dbo.add_event ?,?,?,?,?,?,?,?");
138
139 int startWorkshopHour = (abs(random.nextInt()) % 5) + startEventHour;
140 int endWorkshopHour = (abs(random.nextInt()) % 2) + startWorkshopHour + 1;
141
142 addEvent.setNull("i_conference_id", Types.NULL);
143 addEvent.setInt("i_parent_id", eventId);
144 addEvent.setString("i_name", conferenceName[i] + " - Workshop " + (m));
145 addEvent.setString("i_start_date", month + "/" + startDay + "/" + year + " " + startWorkshopHour + ":00:00");
146 addEvent.setString("i_end_date", month + "/" + endDay + "/" + year + " " + endWorkshopHour + ":00:00");
147 addEvent.setInt("i_attendees_limit", abs((random.nextInt()) % 3) * 10 + 50);
148 addEvent.setInt("i_price", abs((random.nextInt()) % 5) * 10 + 15);
149 addEvent.setInt("i_student_discount", 0);
150 addEvent.execute();
151 }
152 }
153 }
154 }
155 }
156
157 public void deleteTables() throws SQLException {
158 this.connection.prepareCall("USE reservation_system;"
159 + "DELETE FROM [dbo].[attendees];"
160 + "DELETE FROM [dbo].[attendes_on_reservations];"
161 + "DELETE FROM [dbo].[reservations];"
162 + "DELETE FROM [dbo].[transactions];"
163
164 // + "DELETE FROM [dbo].[customers];"
165 // + "DELETE FROM [dbo].[transaction_statuses];"
166 // + "DELETE FROM [dbo].[price_thresholds];"
167 // + "DELETE FROM [dbo].[events];"
168 // + "DELETE FROM [dbo].[conferences]"
169
170 + "DBCC CHECKIDENT ('[dbo].[attendees]', RESEED, 0);"
171 + "DBCC CHECKIDENT ('[dbo].[attendes_on_reservations]', RESEED, 0);"
172 + "DBCC CHECKIDENT ('[dbo].[reservations]', RESEED, 0);"
173 + "DBCC CHECKIDENT ('[dbo].[transactions]', RESEED, 0);"
174
175 // + "DBCC CHECKIDENT ('[dbo].[customers]', RESEED, 0);"
176 // + "DBCC CHECKIDENT ('[dbo].[transaction_statuses]', RESEED, 0);"
177 // + "DBCC CHECKIDENT ('[dbo].[events]', RESEED, 0);"
178 // + "DBCC CHECKIDENT ('[dbo].[conferences]', RESEED, 0);"
179 ).execute();
180 }
181
182 public void addTransactionStatus() throws SQLException {
183 this.connection.prepareCall(
184 "EXECUTE dbo.add_transaction_status @i_status = \"PENDING\"" +
185 "EXECUTE dbo.add_transaction_status @i_status = \"SUCCESS\"" +
186 "EXECUTE dbo.add_transaction_status @i_status = \"ERROR\"" +
187 "EXECUTE dbo.add_transaction_status @i_status = \"CANCELED\"").execute();
188 }
189
190 public void generateCustomer() throws SQLException {
191
192 for (int i = 0; i < customerNumber; i++) {
193
194 CallableStatement addCustomer = this.connection.prepareCall("EXECUTE dbo.add_customer ?,?,?,?");
195
196 if (random.nextInt() % 2 == 1) {
197 String name = "Company " + abs(random.nextInt() % 999);
198 addCustomer.setString("i_name", name);
199 addCustomer.setString("i_vat_id", abs(random.nextInt() % 9999999) + "1");
200 } else {
201 String name = "Person " + abs(random.nextInt() % 999);
202 addCustomer.setString("i_name", name);
203 addCustomer.setNull("i_vat_id", Types.NULL);
204 }
205 addCustomer.setString("i_phone_number", "+48 " + abs(((random.nextInt()) % 9999999)));
206 addCustomer.setString("i_e_mail", (char) (abs(random.nextInt() % 36) + 97) + "@gmail.com");
207 addCustomer.execute();
208 }
209 }
210
211 public void generateReservation() throws SQLException {
212
213 for (int i = 0; i < numberOfGenerateReservation; i++) {
214
215 CallableStatement addReservation = this.connection.prepareCall("EXECUTE dbo.add_reservation ?,?,?,?,?,?");
216 CallableStatement addAttendee = this.connection.prepareCall("EXECUTE dbo.add_attendee ?,?");
217
218 int randomCustomerId = abs(random.nextInt() % customerNumber) + 1;
219
220 addReservation.registerOutParameter("o_id2", Types.INTEGER);
221 addReservation.setInt("i_customer_id", randomCustomerId);
222 addReservation.setString("i_date", "11/11/2011 00:00:00");
223
224 Statement stmt = null;
225 stmt = connection.createStatement();
226 String sql = "SELECT id FROM events ORDER BY id DESC { LIMIT 1 }";
227 ResultSet rs = stmt.executeQuery(sql);
228
229 while (rs.next()) {
230 addReservation.setInt("i_event_id", abs(random.nextInt()) % rs.getInt("id"));
231 }
232
233 Statement stmt2 = null;
234 stmt2 = connection.createStatement();
235 String sql2 = "SELECT vat_id FROM customers" + " WHERE id=" + randomCustomerId;
236 ResultSet rs2 = stmt2.executeQuery(sql2);
237
238 while (rs2.next()) {
239 String customerVatId = rs2.getString("vat_id");
240 if (customerVatId == null) {
241 int studentOrNot = abs(random.nextInt()) % 2;
242 if (studentOrNot == 1) {
243 addReservation.setInt("i_regular_attendees_count", 0);
244 addReservation.setInt("i_student_attendees_count", 1);
245 } else {
246 addReservation.setInt("i_regular_attendees_count", 1);
247 addReservation.setInt("i_student_attendees_count", 0);
248 }
249 addReservation.execute();
250 addAttendee.registerOutParameter("o_id1", Types.INTEGER);
251 addAttendee.setString("i_name", "Name" + abs(random.nextInt() % 100));
252 addAttendee.execute();
253
254 int attendeeId = addAttendee.getInt("o_id1");
255 int reservationId = addReservation.getInt("o_id2");
256 CallableStatement addAttendeeToReservation = this.connection.prepareCall("EXECUTE dbo.add_attendee_to_reservation ?,?");
257 addAttendeeToReservation.setInt("i_reservation_id", reservationId);
258 addAttendeeToReservation.setInt("i_attendee_id", attendeeId);
259 addAttendeeToReservation.execute();
260
261 } else {
262 int numberOfRegular = ((abs(random.nextInt()) % 6) * 5);
263 int numberOfStudents = ((abs(random.nextInt()) % 11) * 5);
264
265 addReservation.setInt("i_regular_attendees_count", numberOfRegular);
266 addReservation.setInt("i_student_attendees_count", numberOfStudents);
267 addReservation.execute();
268
269 for (int j = 0; j < (numberOfRegular + numberOfStudents); j++) {
270
271 addAttendee.registerOutParameter("o_id1", Types.INTEGER);
272 addAttendee.setString("i_name", "Name" + abs(random.nextInt() % 100));
273 addAttendee.execute();
274
275 int attendeeId = addAttendee.getInt("o_id1");
276 int reservationId = addReservation.getInt("o_id2");
277 CallableStatement addAttendeeToReservation = this.connection.prepareCall("EXECUTE dbo.add_attendee_to_reservation ?,?");
278 addAttendeeToReservation.setInt("i_reservation_id", reservationId);
279 addAttendeeToReservation.setInt("i_attendee_id", attendeeId);
280 addAttendeeToReservation.execute();
281 }
282 }
283 }
284 rs.close();
285 rs2.close();
286 }
287 }
288
289 public void setTransactionStatus() throws SQLException {
290 int id = 1;
291 int statusID = 0;
292 for (int i = 0; i < 600; i++) {
293 int randomStatus = abs(random.nextInt() % 10) + 1;
294 if (randomStatus > 4) {
295 statusID = 2;
296 } else statusID = randomStatus;
297
298 CallableStatement setTransaction = this.connection.prepareCall("EXECUTE dbo.change_status_of_transaction ?,?");
299 setTransaction.setInt("i_id", id);
300 setTransaction.setInt("i_status_id", statusID);
301 setTransaction.execute();
302 id++;
303 }
304
305// Statement stmt = null;
306// stmt = connection.createStatement();
307// String sql = "UPDATE [dbo].[transactions] " +"SET status_id = 2 WHERE id=1";
308// stmt.executeUpdate(sql);
309 }
310
311 public static void main(String[] args) throws IOException, SQLException {
312
313 String connectionUrl = "jdbc:sqlserver://********;database=reservation_system;user=********;password=********;";
314
315 try (Connection connection = DriverManager.getConnection(connectionUrl)) {
316 DataGenerator dataGenerator = new DataGenerator(connection);
317 //dataGenerator.deleteTables();
318 //dataGenerator.addTransactionStatus();
319 //dataGenerator.generateConferencesEventsWorkshops();
320 //dataGenerator.generateCustomer();
321 //dataGenerator.generateReservation();
322 dataGenerator.setTransactionStatus();
323 } catch (SQLException e) {
324 e.printStackTrace();
325 }
326 }
327}