|
1 package sellwin.db;
2
3 import sellwin.domain.*;
4 import sellwin.utils.*;
5
6 import java.util.*;
7 import java.sql.*;
8
9
13
14
19 public class Admin extends DBType {
20
21 private static final String  [] drops =
22 { "drop table delete_info",
23 "drop table state_tax",
24 "drop table role_permission",
25 "drop table user_role",
26 "drop table user_group_member",
27 "drop table user_group",
28 "drop table sellwin_order",
29 "drop table quote_line",
30 "drop table quote",
31 "drop table attendee",
32 "drop table activity",
33 "drop table lead",
34 "drop table campaign",
35 "drop table customer_inventory",
36 "drop table product",
37 "drop table customer",
38 "drop table sales_person",
39 "drop table sales_person_role",
40 "drop table forecast",
41 "drop table contact",
42 "drop table opportunity",
43 "drop table address"
44 };
45
46 private static final String [] creates = {
47 "CREATE TABLE delete_info (" +
48 "pk bigint(15) NOT NULL PRIMARY KEY, "+
49 "class_name VARCHAR(25) NOT NULL)",
50
51 "CREATE TABLE state_tax (" +
52 "state_code VARCHAR(2) NOT NULL PRIMARY KEY,"+
53 "state_full_name VARCHAR(30),"+
54 "tax_pct decimal(6,5) NOT NULL,"+
55 "modified_by VARCHAR(25) NOT NULL,"+
56 "modified_date DATE NOT NULL)",
57
58 "CREATE TABLE address ("+
59 "pk bigint(15) NOT NULL PRIMARY KEY,"+
60 "first_name VARCHAR(25),"+
61 "last_name VARCHAR(25),"+
62 "middle_initial VARCHAR(20),"+
63 "address_line1 VARCHAR(80),"+
64 "address_line2 VARCHAR(80),"+
65 "title VARCHAR(7),"+
66 "phone VARCHAR(25),"+
67 "fax VARCHAR(25),"+
68 "pager VARCHAR(25),"+
69 "cell VARCHAR(25),"+
70 "email VARCHAR(80),"+
71 "city VARCHAR(25),"+
72 "state VARCHAR(25),"+
73 "country VARCHAR(25),"+
74 "zip VARCHAR(5),"+
75 "zip4 VARCHAR(4), "+
76 "modified_by VARCHAR(25) NOT NULL,"+
77 "modified_date DATE NOT NULL)",
78
79 "CREATE TABLE sales_person ("+
80 "pk bigint(15) NOT NULL PRIMARY KEY,"+
81 "id VARCHAR(25) NOT NULL UNIQUE,"+
82 "password VARCHAR(25) NOT NULL,"+
83 "address_pk bigint(15) NOT NULL,"+
84 "modified_by VARCHAR(25) NOT NULL,"+
85 "modified_date DATE NOT NULL,"+
86 "sync_date DATE,"+
87 "CONSTRAINT SPFK1 FOREIGN KEY (address_pk) REFERENCES address (pk))",
88
89 "CREATE TABLE customer ( "+
90 "pk bigint(15) NOT NULL PRIMARY KEY, "+
91 "name VARCHAR(25) NOT NULL UNIQUE, "+
92 "ship_address_pk bigint(15) NOT NULL, "+
93 "bill_address_pk bigint(15) NOT NULL, "+
94 "annual_sales INTEGER NOT NULL, "+
95 "industry VARCHAR(25), "+
96 "channel VARCHAR(25), "+
97 "contact_pk bigint(15) NOT NULL, "+
98 "modified_by VARCHAR(25) NOT NULL, "+
99 "modified_date DATE NOT NULL, "+
100 "CONSTRAINT CUSTFK1 FOREIGN KEY (ship_address_pk) REFERENCES address (pk), "+
101 "CONSTRAINT CUSTFK2 FOREIGN KEY (bill_address_pk) REFERENCES address (pk), "+
102 "CONSTRAINT CUSTFK3 FOREIGN KEY (contact_pk) REFERENCES address (pk))",
103
104 "CREATE TABLE product (" +
105 "pk bigint(15) NOT NULL PRIMARY KEY," +
106 "product_group VARCHAR(25) NOT NULL," +
107 "line VARCHAR(25) NOT NULL," +
108 "name VARCHAR(25) NOT NULL," +
109 "model_no VARCHAR(25) NOT NULL," +
110 "cost DECIMAL(12,2) NOT NULL," +
111 "price DECIMAL(12,2) NOT NULL," +
112 "description VARCHAR(128) NOT NULL," +
113 "modified_by VARCHAR(25) NOT NULL," +
114 "modified_date DATE NOT NULL)",
115
116 "CREATE TABLE customer_inventory ( "+
117 "pk bigint(15) NOT NULL PRIMARY KEY, "+
118 "customer_pk bigint(15) NOT NULL, "+
119 "product_pk bigint(15) NOT NULL, "+
120 "cnt INTEGER NOT NULL, "+
121 "modified_by VARCHAR(25) NOT NULL, "+
122 "modified_date DATE NOT NULL, "+
123 "CONSTRAINT CIFK1 FOREIGN KEY (customer_pk) REFERENCES customer (pk), "+
124 "CONSTRAINT CIFK2 FOREIGN KEY (product_pk) REFERENCES product (pk))",
125
126 "CREATE TABLE campaign (" +
127 "pk bigint(15) NOT NULL PRIMARY KEY," +
128 "name VARCHAR(25) NOT NULL," +
129 "type VARCHAR(25) NOT NULL," +
130 "product_group VARCHAR(25) NOT NULL," +
131 "product_line VARCHAR(25) NOT NULL," +
132 "lead_count INTEGER NOT NULL," +
133 "modified_by VARCHAR(25) NOT NULL," +
134 "modified_date DATE NOT NULL)",
135
136 "CREATE TABLE lead (" +
137 "pk bigint(15) NOT NULL PRIMARY KEY," +
138 "campaign_pk bigint(15) NOT NULL UNIQUE," +
139 "address_pk bigint(15) NOT NULL," +
140 "company VARCHAR(25) NOT NULL," +
141 "interest_level VARCHAR(25) NOT NULL," +
142 "contacted_by_pk bigint(15) NOT NULL," +
143 "contact_date DATE NOT NULL," +
144 "converted_opp CHAR NOT NULL," +
145 "modified_by VARCHAR(25) NOT NULL," +
146 "modified_date DATE NOT NULL," +
147 "CONSTRAINT LEADFK1 FOREIGN KEY (campaign_pk) REFERENCES campaign (pk)," +
148 "CONSTRAINT LEADFK2 FOREIGN KEY (address_pk) REFERENCES address (pk)," +
149 "CONSTRAINT LEADFK3 FOREIGN KEY (contacted_by_pk) REFERENCES sales_person (pk))",
150
151 "CREATE TABLE opportunity ("+
152 "pk bigint(15) NOT NULL PRIMARY KEY,"+
153 "name VARCHAR(80) NOT NULL,"+
154 "dollar_value INTEGER NOT NULL,"+
155 "probability VARCHAR(25) NOT NULL,"+
156 "stage VARCHAR(25) NOT NULL,"+
157 "lead_source VARCHAR(25) NOT NULL,"+
158 "lead_type VARCHAR(25) NOT NULL,"+
159 "description VARCHAR(125) NOT NULL,"+
160 "group_name VARCHAR(25) NOT NULL,"+
161 "prime_sp_pk bigint(15),"+
162 "cust_pk bigint(15),"+
163 "close_date DATE NOT NULL,"+
164 "lead_pk bigint(15),"+
165 "modified_by VARCHAR(25) NOT NULL,"+
166 "modified_date DATE NOT NULL)",
167
168 "CREATE TABLE forecast ("+
169 "pk bigint(15) NOT NULL PRIMARY KEY,"+
170 "opp_pk bigint(15) NOT NULL,"+
171 "name VARCHAR(25) NOT NULL ,"+
172 "close_date DATE NOT NULL,"+
173 "submitted CHAR NOT NULL,"+
174 "submitted_date DATE NOT NULL,"+
175 "submitted_by VARCHAR(25) NOT NULL,"+
176 "revenue INTEGER NOT NULL,"+
177 "profit INTEGER NOT NULL,"+
178 "margin DECIMAL(3,2) NOT NULL,"+
179 "scenario VARCHAR(25) NOT NULL,"+
180 "amount_jan INTEGER NOT NULL,"+
181 "amount_feb INTEGER NOT NULL,"+
182 "amount_mar INTEGER NOT NULL,"+
183 "q1 INTEGER NOT NULL,"+
184 "amount_apr INTEGER NOT NULL,"+
185 "amount_may INTEGER NOT NULL,"+
186 "amount_jun INTEGER NOT NULL,"+
187 "q2 INTEGER NOT NULL,"+
188 "amount_jul INTEGER NOT NULL,"+
189 "amount_aug INTEGER NOT NULL,"+
190 "amount_sep INTEGER NOT NULL,"+
191 "q3 INTEGER NOT NULL,"+
192 "amount_oct INTEGER NOT NULL,"+
193 "amount_nov INTEGER NOT NULL,"+
194 "amount_dec INTEGER NOT NULL,"+
195 "q4 INTEGER NOT NULL,"+
196 "modified_by VARCHAR(25) NOT NULL,"+
197 "modified_date DATE NOT NULL,"+
198 "CONSTRAINT FORFK1 FOREIGN KEY (opp_pk) REFERENCES opportunity (pk))",
199
200 "CREATE TABLE contact ( "+
201 "pk bigint(15) NOT NULL PRIMARY KEY, "+
202 "opp_pk bigint(15) NOT NULL, "+
203 "address_pk bigint(15) NOT NULL, "+
204 "modified_by VARCHAR(25) NOT NULL, "+
205 "modified_date DATE NOT NULL, "+
206 "CONSTRAINT CONTFK1 FOREIGN KEY (opp_pk) REFERENCES opportunity (pk), "+
207 "CONSTRAINT CONTFK2 FOREIGN KEY (address_pk) REFERENCES address (pk))",
208
209 "CREATE TABLE activity ( "+
210 "pk bigint(15) NOT NULL PRIMARY KEY, "+
211 "opp_pk bigint(15) NOT NULL, "+
212 "subject VARCHAR(45) NOT NULL, "+
213 "type VARCHAR(25) NOT NULL, "+
214 "place VARCHAR(120) NOT NULL, "+
215 "message VARCHAR(255) NOT NULL, "+
216 "start_date DATE NOT NULL, "+
217 "duration int NOT NULL, "+
218 "group_alarm CHAR NOT NULL, "+
219 "group_alarm_ack CHAR NOT NULL, "+
220 "alarm CHAR NOT NULL, "+
221 "alarm_ack CHAR NOT NULL, "+
222 "modified_by VARCHAR(25) NOT NULL, "+
223 "modified_date DATE NOT NULL, "+
224 "CONSTRAINT ACTFK1 FOREIGN KEY (opp_pk) REFERENCES opportunity (pk))",
225
226 "CREATE TABLE attendee ( "+
227 "pk bigint(15) NOT NULL PRIMARY KEY, "+
228 "activity_pk bigint(15) NOT NULL, "+
229 "person_pk bigint(15) NOT NULL, "+
230 "modified_by VARCHAR(25) NOT NULL, "+
231 "modified_date DATE NOT NULL, "+
232 "CONSTRAINT ATTFK1 FOREIGN KEY (activity_pk) REFERENCES activity (pk), "+
233 "CONSTRAINT ATTFK2 FOREIGN KEY (person_pk) REFERENCES sales_person (pk))",
234
235 "CREATE TABLE quote ("+
236 "pk bigint(15) NOT NULL PRIMARY KEY,"+
237 "opp_pk bigint(15) NOT NULL,"+
238 "name VARCHAR(25) NOT NULL,"+
239 "modified_by VARCHAR(25) NOT NULL,"+
240 "modified_date DATE NOT NULL,"+
241 "CONSTRAINT QUOFK1 FOREIGN KEY (opp_pk) REFERENCES opportunity (pk))",
242
243 "CREATE TABLE sellwin_order ( "+
244 "pk bigint(15) NOT NULL PRIMARY KEY, "+
245 "opp_pk bigint(15) NOT NULL, "+
246 "order_number bigint(15) NOT NULL, "+
247 "quote_pk bigint(15) NOT NULL, "+
248 "ship_address_pk bigint(15) NOT NULL, "+
249 "bill_address_pk bigint(15) NOT NULL, "+
250 "status VARCHAR(25) NOT NULL, "+
251 "payment_form VARCHAR(25) NOT NULL, "+
252 "sub_total decimal(10,2) NOT NULL, "+
253 "tax decimal(10,2) NOT NULL, "+
254 "shipping decimal(10,2) NOT NULL, "+
255 "total decimal(10,2) NOT NULL, "+
256 "modified_by VARCHAR(25) NOT NULL, "+
257 "modified_date DATE NOT NULL, "+
258 "CONSTRAINT ORDFK1 FOREIGN KEY (opp_pk) REFERENCES opportunity (pk), "+
259 "CONSTRAINT ORDFK2 FOREIGN KEY (quote_pk) REFERENCES quote (pk), "+
260 "CONSTRAINT ORDFK3 FOREIGN KEY (ship_address_pk) REFERENCES address (pk), "+
261 "CONSTRAINT ORDFK4 FOREIGN KEY (bill_address_pk) REFERENCES address (pk))",
262
263 "CREATE TABLE quote_line ("+
264 "pk bigint(15) NOT NULL PRIMARY KEY,"+
265 "quote_pk bigint(15) NOT NULL,"+
266 "product_pk bigint(15) NOT NULL,"+
267 "product_name VARCHAR(25) NOT NULL,"+
268 "product_model_no VARCHAR(25) NOT NULL,"+
269 "quantity INTEGER NOT NULL,"+
270 "cost DECIMAL(12,2) NOT NULL,"+
271 "unit_price DECIMAL(12,2) NOT NULL,"+
272 "total_price DECIMAL(12,2) NOT NULL,"+
273 "product_desc VARCHAR(128) NOT NULL,"+
274 "modified_by VARCHAR(25) NOT NULL,"+
275 "modified_date DATE NOT NULL,"+
276 "CONSTRAINT QULFK1 FOREIGN KEY (quote_pk) REFERENCES quote (pk),"+
277 "CONSTRAINT QULFK2 FOREIGN KEY (product_pk) REFERENCES product (pk))",
278
279 "CREATE TABLE user_group ("+
280 "pk bigint(15) NOT NULL PRIMARY KEY,"+
281 "name VARCHAR(25) NOT NULL,"+
282 "modified_by VARCHAR(25) NOT NULL,"+
283 "modified_date DATE NOT NULL)",
284
285 "CREATE TABLE user_group_member ( "+
286 "pk bigint(15) NOT NULL PRIMARY KEY, "+
287 "user_group_pk bigint(15) NOT NULL, "+
288 "user_pk bigint(15) NOT NULL, "+
289 "modified_by VARCHAR(25) NOT NULL, "+
290 "modified_date DATE NOT NULL, "+
291 "CONSTRAINT UGFK1 FOREIGN KEY (user_group_pk) REFERENCES user_group (pk), "+
292 "CONSTRAINT UGFK2 FOREIGN KEY (user_pk) REFERENCES sales_person (pk))",
293
294 "CREATE TABLE user_role ("+
295 "pk bigint(15) NOT NULL PRIMARY KEY,"+
296 "name VARCHAR(128) NOT NULL UNIQUE,"+
297 "modified_by VARCHAR(25) NOT NULL,"+
298 "modified_date DATE NOT NULL)",
299
300 "CREATE TABLE sales_person_role ( "+
301 "pk bigint(15) NOT NULL PRIMARY KEY, "+
302 "sp_pk bigint(15) NOT NULL, "+
303 "user_role_pk bigint(15) NOT NULL, "+
304 "modified_by VARCHAR(25) NOT NULL, "+
305 "modified_date DATE NOT NULL)",
306
307 "CREATE TABLE role_permission ( "+
308 "pk bigint(15) NOT NULL PRIMARY KEY, "+
309 "user_role_pk bigint(15) NOT NULL, "+
310 "name VARCHAR(128) NOT NULL, "+
311 "r CHAR NOT NULL, "+
312 "w CHAR NOT NULL, "+
313 "modified_by VARCHAR(25) NOT NULL, "+
314 "modified_date DATE NOT NULL, "+
315 "CONSTRAINT rpfk1 FOREIGN KEY (user_role_pk) REFERENCES user_role (pk))"
316
317
318 };
319
320 private final static String [] create_roles = {
321
322 "INSERT INTO sellwin.user_role (pk, name, modified_by, modified_date) VALUES (1, 'Sales Rep', 'SYSTEM', CURRENT_DATE)",
323
324 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (1, 1, 'Forecast', 'Y', 'Y', 'SYSTEM', CURRENT_DATE)",
325
326 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (2, 1, 'Quote', 'Y', 'Y', 'SYSTEM', CURRENT_DATE)",
327
328 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (3, 1, 'Activity', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
329
330 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (4, 1, 'Basic Admin', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
331
332 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (5, 1, 'User Admin', 'N', 'N', 'SYSTEM', CURRENT_DATE) ",
333
334 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (6, 1, 'Order', 'Y', 'Y', 'SYSTEM', CURRENT_DATE)",
335
336 "INSERT INTO user_role (pk, name, modified_by, modified_date) VALUES (2, 'Sales Mgr', 'SYSTEM', CURRENT_DATE) ",
337
338 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (7, 2, 'Forecast', 'N', 'N', 'SYSTEM', CURRENT_DATE) ",
339
340 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (8, 2, 'Quote', 'Y', 'N', 'SYSTEM', CURRENT_DATE)",
341
342 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (9, 2, 'Activity', 'Y', 'N', 'SYSTEM', CURRENT_DATE) ",
343
344 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (10, 2, 'Basic Admin', 'Y', 'N', 'SYSTEM', CURRENT_DATE) ",
345
346 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (11, 2, 'User Admin', 'N', 'N', 'SYSTEM', CURRENT_DATE) ",
347
348 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (12, 2, 'Order', 'Y', 'N', 'SYSTEM', CURRENT_DATE) ",
349
350 "INSERT INTO user_role (pk, name, modified_by, modified_date) VALUES (3, 'Sales Support', 'SYSTEM', CURRENT_DATE) ",
351
352 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (13, 3, 'Forecast', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
353
354 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (14, 3, 'Quote', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
355
356 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (15, 3, 'Activity', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
357
358 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (16, 3, 'Basic Admin', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
359
360 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (17, 3, 'User Admin', 'Y', 'N', 'SYSTEM', CURRENT_DATE) ",
361
362 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (18, 3, 'Order', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
363
364 "INSERT INTO user_role (pk, name, modified_by, modified_date) VALUES (4, 'System Admin', 'SYSTEM', CURRENT_DATE) ",
365
366 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (19, 4, 'Forecast', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
367
368 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (20, 4, 'Quote', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
369
370 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (21, 4, 'Activity', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
371
372 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (22, 4, 'Basic Admin', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
373
374 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (23, 4, 'User Admin', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
375
376 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (24, 4, 'Order', 'Y', 'Y', 'SYSTEM', CURRENT_DATE) ",
377
378 "INSERT INTO user_role (pk, name, modified_by, modified_date) VALUES (5, 'Marketing Rep', 'SYSTEM', CURRENT_DATE) ",
379
380 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (25, 5, 'Forecast', 'Y', 'N', 'SYSTEM', CURRENT_DATE) ",
381
382 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (26, 5, 'Quote', 'Y', 'N', 'SYSTEM', CURRENT_DATE) ",
383
384 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (27, 5, 'Activity', 'Y', 'N', 'SYSTEM', CURRENT_DATE) ",
385
386 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (28, 5, 'Basic Admin', 'Y', 'N', 'SYSTEM', CURRENT_DATE) ",
387
388 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (29, 5, 'User Admin', 'N', 'N', 'SYSTEM', CURRENT_DATE) ",
389
390 "INSERT INTO sellwin.role_permission (pk, user_role_pk, name, r, w, modified_by, modified_date) VALUES (30, 5, 'Order', 'Y', 'N', 'SYSTEM', CURRENT_DATE) "
391 };
392
393 private final static String [] state_tax = {
394 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('ALABAMA','AL', 0.04,'SYSTEM', CURRENT_DATE) ",
395 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('ALASKA','AK', 0.00,'SYSTEM', CURRENT_DATE) ",
396 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('ARIZONA','AZ', 0.056,'SYSTEM', CURRENT_DATE) ",
397 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('ARKANSAS','AR', 0.05125,'SYSTEM', CURRENT_DATE) ",
398 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('CALIFORNIA','CA', 0.06,'SYSTEM', CURRENT_DATE) ",
399 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('COLORADO','CO', 0.029,'SYSTEM', CURRENT_DATE) ",
400 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('CONNECTICUT','CT', 0.06,'SYSTEM', CURRENT_DATE) ",
401 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('DELAWARE','DE', 0.00,'SYSTEM', CURRENT_DATE) ",
402 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('DISTRICT OF COLUMBIA','DC', 0.0575,'SYSTEM', CURRENT_DATE) ",
403 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('FLORIDA','FL', 0.06,'SYSTEM', CURRENT_DATE) ",
404 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('GEORGIA','GA', 0.04,'SYSTEM', CURRENT_DATE) ",
405 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('HAWAII','HI', 0.04,'SYSTEM', CURRENT_DATE) ",
406 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('IDAHO','ID', 0.05,'SYSTEM', CURRENT_DATE) ",
407 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('ILLINOIS','IL', 0.0625,'SYSTEM', CURRENT_DATE) ",
408 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('INDIANA','IN', 0.05,'SYSTEM', CURRENT_DATE) ",
409 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('IOWA','IA', 0.05,'SYSTEM', CURRENT_DATE) ",
410 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('KANSAS','KS', 0.049,'SYSTEM', CURRENT_DATE) ",
411 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('KENTUCKY','KY', 0.06,'SYSTEM', CURRENT_DATE) ",
412 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('LOUISIANA','LA', 0.04,'SYSTEM', CURRENT_DATE) ",
413 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('MAINE','ME', 0.05,'SYSTEM', CURRENT_DATE) ",
414 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('MARYLAND','MD', 0.05,'SYSTEM', CURRENT_DATE) ",
415 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('MASSACHUSETTS','MA', 0.05,'SYSTEM', CURRENT_DATE) ",
416 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('MICHIGAN','MI', 0.06,'SYSTEM', CURRENT_DATE) ",
417 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('MINNESOTA','MN', 0.065,'SYSTEM', CURRENT_DATE) ",
418 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('MISSISSIPPI','MS', 0.07,'SYSTEM', CURRENT_DATE) ",
419 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('MISSOURI','MO', 0.04225,'SYSTEM', CURRENT_DATE) ",
420 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('MONTANA','MT', 0.00,'SYSTEM', CURRENT_DATE) ",
421 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('NEBRASKA','NE', 0.05,'SYSTEM', CURRENT_DATE) ",
422 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('NEVADA','NV', 0.0425,'SYSTEM', CURRENT_DATE) ",
423 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('NEW HAMPSHIRE','NH', 0.00,'SYSTEM', CURRENT_DATE)",
424 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('NEW JERSEY','NJ', 0.06,'SYSTEM', CURRENT_DATE) ",
425 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('NEW MEXICO','NM', 0.05,'SYSTEM', CURRENT_DATE) ",
426 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('NEW YORK','NY', 0.04,'SYSTEM', CURRENT_DATE) ",
427 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('NORTH CAROLINA','NC', 0.045,'SYSTEM', CURRENT_DATE) ",
428 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('NORTH DAKOTA','ND', 0.05,'SYSTEM', CURRENT_DATE) ",
429 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('OHIO','OH', 0.05,'SYSTEM', CURRENT_DATE) ",
430 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('OKLAHOMA','OK', 0.045,'SYSTEM', CURRENT_DATE) ",
431 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('OREGON','OR', 0.00,'SYSTEM', CURRENT_DATE) ",
432 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('PENNSYLVANIA','PA', 0.06,'SYSTEM', CURRENT_DATE) ",
433 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('RHODE ISLAND','RI', 0.07,'SYSTEM', CURRENT_DATE) ",
434 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('SOUTH CAROLINA','SC', 0.05,'SYSTEM', CURRENT_DATE) ",
435 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('SOUTH DAKOTA','SD', 0.04,'SYSTEM', CURRENT_DATE) ",
436 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('TENNESSEE','TN', 0.06,'SYSTEM', CURRENT_DATE) ",
437 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('TEXAS','TX', 0.0625,'SYSTEM', CURRENT_DATE) ",
438 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('UTAH','UT', 0.0475,'SYSTEM', CURRENT_DATE) ",
439 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('VERMONT','VT', 0.05,'SYSTEM', CURRENT_DATE) ",
440 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('VIRGINIA','VA', 0.035,'SYSTEM', CURRENT_DATE) ",
441 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('WASHINGTON','WA', 0.065,'SYSTEM', CURRENT_DATE) ",
442 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('WEST VIRGINIA','WV', 0.06,'SYSTEM', CURRENT_DATE) ",
443 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('WISCONSIN','WI', 0.05,'SYSTEM', CURRENT_DATE) ",
444 "insert into state_tax (state_full_name, state_code, tax_pct, modified_by, modified_date) values ('WYOMING','WY', 0.04,'SYSTEM', CURRENT_DATE) ",
445 };
446
447 private final static String [] office_products = {
448 "insert into sellwin.product values (1, 'seating', 'Aerial', 'Size A - Aerial', 'a-1', 911.78, 888.55, 'Aerial Size A Chair', 'SYSTEM', current_date) ",
449 "insert into sellwin.product values (2, 'seating', 'Aerial', 'Size B - Aerial', 'a-2', 888.42, 777.55, 'Aerial Size B Chair', 'SYSTEM', current_date) ",
450 "insert into sellwin.product values (3, 'seating', 'Aerial', 'Size C - Aerial', 'a-3', 712.42, 727.82, 'Aerial Size C Chair', 'SYSTEM', current_date) ",
451 "insert into sellwin.product values (4, 'seating', 'Equity', 'Size A - Equity', 'ea-1', 345.42, 123.82, 'Equity Size A Chair', 'SYSTEM', current_date) ",
452 "insert into sellwin.product values (5, 'seating', 'Equity', 'Size B - Equity', 'eb-1', 145.42, 123.82, 'Equity Size B Chair', 'SYSTEM', current_date) ",
453 "insert into sellwin.product values (6, 'seating', 'Equity', 'Size C - Equity', 'ec-1', 45.42, 23.82, 'Equity Size C Chair', 'SYSTEM', current_date) ",
454 "insert into sellwin.product values (7, 'systems furniture', 'Act', 'Size A - Act', 'ac-1', 100.42, 75.82, 'Act Size A Chair', 'SYSTEM', current_date) ",
455 "insert into sellwin.product values (8, 'systems furniture', 'Act', 'Size B - Act', 'ab-8', 999.42, 787.82, 'Act Size B ', 'SYSTEM', current_date) ",
456 "insert into sellwin.product values (9, 'systems furniture', 'Act', 'Size B - Surface laminate - Act', 'ac-9', 199.42, 188.82, 'Act Surface Lam', 'SYSTEM', current_date) ",
457 "insert into sellwin.product values (10, 'systems furniture', 'R System', 'Size A - R System', 'sc-9', 101.42, 77.82, 'R System Size A', 'SYSTEM', current_date) ",
458 "insert into sellwin.product values (11, 'systems furniture', 'R System', 'Size B - R System', 'sc-9', 101.42, 77.82, 'R System Size B', 'SYSTEM', current_date) ",
459 "insert into sellwin.product values (12, 'systems furniture', 'R System', 'Size C - R System', 'sc-9', 101.42, 77.82, 'R System Size C', 'SYSTEM', current_date) ",
460 "insert into sellwin.product values (13, 'freestanding furniture', 'Eanes', 'Storage units', 'e-19', 652.42, 399.82, 'Eanes Stor Units', 'SYSTEM', current_date) ",
461 "insert into sellwin.product values (14, 'freestanding furniture', 'Eanes', 'Desks', 'e-29', 183.42, 99.82, 'Eanes Stor Units', 'SYSTEM', current_date) ",
462 "insert into sellwin.product values (15, 'freestanding furniture', 'Eanes', 'Conference table', 'e-39', 222.42, 100.82, 'Eanes Conf Table', 'SYSTEM', current_date) ",
463 "insert into sellwin.product values (16, 'freestanding furniture', 'Eanes', 'Side Table', 'e-49', 333.42, 111.82, 'Eanes Side Table', 'SYSTEM', current_date) ",
464 "insert into sellwin.product values (17, 'freestanding furniture', 'Burdine', 'Table', 'bur-1', 721.42, 100.82, 'Burdine Table', 'SYSTEM', current_date) ",
465 "insert into sellwin.product values (18, 'freestanding furniture', 'Burdine', 'Coffee Table', 'bur-2', 200.42, 100.02, 'Burdine Coff Table', 'SYSTEM', current_date) ",
466 "insert into sellwin.product values (19, 'freestanding furniture', 'Burdine', 'Desk', 'bur-3', 1721.42, 1100.82, 'Burdine Dsk', 'SYSTEM', current_date) ",
467 "insert into sellwin.product values (20, 'freestanding furniture', 'Burdine', 'Conf Table', 'bur-4', 721.42, 100.82, 'Burdine Table', 'SYSTEM', current_date) ",
468 "insert into sellwin.product values (21, 'accessories', 'Harry Miller', 'Keyboard', 'mil-5', 21.42, 10.82, 'Keyboard', 'SYSTEM', current_date) ",
469 "insert into sellwin.product values (22, 'accessories', 'Harry Miller', 'Shelves', 'mil-6', 31.42, 30.82, 'Shelves', 'SYSTEM', current_date) ",
470 "insert into sellwin.product values (23, 'accessories', 'Harry Miller', 'Paper Tray', 'mil-7', 41.42, 20.82, 'Paper Tray', 'SYSTEM', current_date) ",
471 "insert into sellwin.product values (24, 'accessories', 'Harry Miller', 'CPU Holder', 'mil-8', 51.42, 19.82, 'CPU Holder', 'SYSTEM', current_date) "
472 };
473
474 private final static String [] test_data = {
475 "INSERT INTO sellwin.address VALUES (1, 'Jeff', 'Roberts', 'A', '111 Spring Road', ' ', 'Mr.', '512-111-2222', ' ', ' ', ' ', 'test1998@yahoo.com', 'Dallas', 'TX', 'USA', '78732', ' ', 'SYSTEM', current_date) ",
476 "INSERT INTO sellwin.address VALUES (12345, 'Jeff', 'McCormick', 'X', 'Ocean blvd ', ' ', 'Mr.', '512-111-2222', ' ', ' ', ' ', 'test1998@yahoo.com', 'Austin', 'TX', 'USA', '78732', ' ', 'SYSTEM', current_date) ",
477 "INSERT INTO sellwin.sales_person VALUES (1, 'trial', 'trial', 1, 'SYSTEM', current_date, current_date) ",
478 "insert into sellwin.campaign values (1, 'campaign1', 'camptype1', 'group1', 'line1', 8, 'SYSTEM', current_date) ",
479 "insert into sellwin.lead values (1, 1, 12345, 'company1', 'interest1', 1, current_date, 'Y', 'SYSTEM', current_date) ",
480 "insert into sellwin.user_group values (1, 'usergroup1', 'SYSTEM', current_date) ",
481 "insert into sellwin.user_group_member values (1, 1, 1, 'SYSTEM', current_date) ",
482 "insert into sellwin.sales_person_role values (1, 1, 4, 'SYSTEM', current_date) "
483 };
484
485 private Connection con;
486
487 public Admin() {
488 }
489
490
494 public Admin(int dbType) {
495 DB_TYPE = dbType;
496 }
497
498
504 public Admin(Connection con) {
505 this.con = con;
506 }
507
508
513 public Connection getConnection() {
514 return this.con;
515 }
516
517
522 public final void setConnection(Connection con)
523 throws SQLException {
524
525 this.con = con;
526 }
527
528
529
534 public final void dropAllTables()
535 throws SQLException {
536
537 Statement stmt = null;
538
539 try {
540
541 stmt = con.createStatement();
542
543 StringBuffer query = null;
544 int updatedRows;
545
546 LogWrite.write("about to drop " + drops.length);
547 for (int i=0;i<drops.length;i++) {
548 query = new StringBuffer ();
549 query.append(drops[i]);
550 LogWrite.write(query.toString());
551 if (Prefs.DEBUG) LogWrite.write(query.toString());
552 try {
553 updatedRows = stmt.executeUpdate(query.toString());
554 } catch (SQLException e) {
555 LogWrite.write(e.getMessage());
556 }
557 }
558 } catch (SQLException e) {
559 throw e;
560 } finally {
561 try { if (stmt != null) stmt.close();
562 } catch (SQLException x) { }
563 }
564 }
565
566
571 public final void createAllTables()
572 throws SQLException {
573
574 Statement stmt = null;
575
576 try {
577
578 stmt = con.createStatement();
579
580 StringBuffer query = null;
581 int updatedRows;
582
583 LogWrite.write("about to create " + creates.length);
584 for (int i=0;i<creates.length;i++) {
585 query = new StringBuffer ();
586 query.append(creates[i]);
587 LogWrite.write(query.toString());
588 if (Prefs.DEBUG) LogWrite.write(query.toString());
589 try {
590 updatedRows = stmt.executeUpdate(query.toString());
591 } catch (SQLException e) {
592 LogWrite.write(e.getMessage());
593 }
594 }
595 } catch (SQLException e) {
596 throw e;
597 } finally {
598 try { if (stmt != null) stmt.close();
599 } catch (SQLException x) { }
600 }
601 }
602
603
604
609 public final boolean testTable() {
610 Statement stmt = null;
611 ResultSet rs = null;
612 String query = "select count(*) from sales_person";
613
614 try {
615 stmt = con.createStatement();
616 rs = stmt.executeQuery(query);
617 return true;
618 } catch (SQLException e) {
619 LogWrite.write(e);
620 return false;
621 } catch (Exception x) {
622 LogWrite.write(x);
623 return false;
624 } finally {
625 try {
626 if (rs != null) rs.close();
627 } catch (SQLException y) { }
628 try {
629 if (stmt != null) stmt.close();
630 } catch (SQLException z) { }
631 }
632 }
633
634 public final int testTableData() {
635 Statement stmt = null;
636 ResultSet rs = null;
637 String query = "select count(*) rupert from sales_person";
638
639 try {
640 stmt = con.createStatement();
641 rs = stmt.executeQuery(query);
642 rs.next();
643 int ct = rs.getInt("rupert");
644 return ct;
645 } catch (SQLException e) {
646 e.printStackTrace();
647 LogWrite.write(e);
648 } catch (Exception x) {
649 LogWrite.write(x);
650 } finally {
651 try {
652 if (rs != null) rs.close();
653 } catch (SQLException y) { }
654 try {
655 if (stmt != null) stmt.close();
656 } catch (SQLException z) { }
657 }
658
659 return 0;
660 }
661
662 public final void loadTestData()
663 throws SQLException {
664
665 Statement stmt = null;
666
667 try {
668
669 stmt = con.createStatement();
670
671 StringBuffer query = null;
672 int updatedRows;
673
674 LogWrite.write("creating " + create_roles.length + " test roles");
675 for (int i=0;i<create_roles.length;i++) {
676 query = new StringBuffer ();
677 query.append(create_roles[i]);
678 LogWrite.write(query.toString());
679 try {
680 updatedRows = stmt.executeUpdate(query.toString());
681 } catch (SQLException e) {
682 LogWrite.write(e.getMessage());
683 }
684 }
685 LogWrite.write("creating " + state_tax.length + " test state tax");
686 for (int i=0;i<state_tax.length;i++) {
687 query = new StringBuffer ();
688 query.append(state_tax[i]);
689 LogWrite.write(query.toString());
690 try {
691 updatedRows = stmt.executeUpdate(query.toString());
692 } catch (SQLException e) {
693 LogWrite.write(e.getMessage());
694 }
695 }
696 LogWrite.write("creating " + test_data.length + " test data");
697 for (int i=0;i<test_data.length;i++) {
698 query = new StringBuffer ();
699 query.append(test_data[i]);
700 LogWrite.write(query.toString());
701 try {
702 updatedRows = stmt.executeUpdate(query.toString());
703 } catch (SQLException e) {
704 LogWrite.write(e.getMessage());
705 }
706 }
707 LogWrite.write("creating " + office_products.length + " test products");
708 for (int i=0;i<office_products.length;i++) {
709 query = new StringBuffer ();
710 query.append(office_products[i]);
711 LogWrite.write(query.toString());
712 try {
713 updatedRows = stmt.executeUpdate(query.toString());
714 } catch (SQLException e) {
715 LogWrite.write(e.getMessage());
716 }
717 }
718 } catch (SQLException e) {
719 throw e;
720 } finally {
721 try { if (stmt != null) stmt.close();
722 } catch (SQLException x) { }
723 }
724 }
725
726 }
727 |
Popular Tags |
Java API By Example, From Geeks To Geeks.