1 package org.paneris.jal.controller;
2
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5 import java.sql.Statement;
6 import java.util.Enumeration;
7 import java.util.Vector;
8
9 import org.paneris.jal.model.DBConnectionManager;
10 import org.paneris.jal.model.DDField;
11 import org.paneris.jal.model.DDRecord;
12 import org.paneris.jal.model.Log;
13 import org.paneris.jal.model.RecordSet;
14 import org.paneris.user.model.User;
15 import org.paneris.util.ExtendedHash;
16 import org.paneris.util.UniqueVector;
17 import org.webmacro.Template;
18 import org.webmacro.servlet.HandlerException;
19 import org.webmacro.servlet.PanerisPage;
20 import org.webmacro.servlet.WebContext;
21
22
23
24
25 public class Admin extends PanerisPage {
26
27 private static final long serialVersionUID = 1L;
28 private DBConnectionManager connMgr = DBConnectionManager.getInstance();
29 private Connection conn;
30 private static Log log = new Log("Admin", "Admin Log");
31 private static final boolean debug = false;
32
33
34
35
36
37
38
39
40
41 public Template handle(WebContext context) throws HandlerException {
42
43 String sqlString = "";
44 String action = null;
45 String db = null;
46 String table = null;
47 String id = null;
48 Integer idInteger;
49 String templateName = null;
50 String orderby = null;
51 String ascdesc = null;
52 String resultsPerPage = null;
53 String returnURL = null;
54 String selectedid = null;
55 String selectExpression = null;
56 boolean list = false;
57
58
59
60
61 action = context.getForm("action");
62 if (action == null) action = "tables";
63 db = context.getForm("db");
64 if (db == null)
65 throw new HandlerException("No datasource specified.");
66 else {
67 conn = connMgr.getConnection("Admin", db);
68 if (conn == null)
69 throw new HandlerException("Can't get connection: " + db);
70 }
71 table = context.getForm("table");
72 id = context.getForm("id");
73 orderby = context.getForm("orderby");
74 if (orderby == null) orderby = "id";
75 ascdesc = context.getForm("ascdesc");
76 if (ascdesc == null)
77 ascdesc = "desc";
78 else {
79 if (ascdesc.equals("desc"))
80 ascdesc = "DESC";
81 else
82 ascdesc = "";
83 }
84 resultsPerPage = context.getForm("resultsperpage");
85 returnURL = context.getForm("returnURL");
86 selectedid = context.getForm("selectedid");
87 if (selectedid == null) selectedid = "0";
88 selectExpression = context.getForm("selectexpression");
89 if (selectExpression == null) selectExpression = "";
90 if (context.getForm("list") != null)
91 list = true;
92
93
94
95
96 try {
97
98
99
100 templateName = checkPermissions(context, table, id, action);
101 if (templateName == null) {
102 templateName = context.getForm("wmtemplate");
103 if (action.equals("tables")) {
104 if (templateName == null)
105 templateName = "jal/view/AdministrationTables1.wm";
106 Statement s = conn.createStatement();
107 Vector rt = new Vector();
108 sqlString =
109 "SELECT * FROM datadictionarytables ORDER BY type, displayorder";
110 ResultSet rs = s.executeQuery(sqlString);
111 while (rs.next()) {
112 DDRecord r = new DDRecord(db, "datadictionarytables", rs);
113 rt.addElement(r);
114 }
115 context.put("results", rt);
116 }
117
118
119
120
121 else if (action.equals("select")) {
122 if (templateName == null && context.getForm("edit") != null)
123 templateName = "jal/view/AdministrationEditList1.wm";
124 if (templateName == null)
125 templateName = "jal/view/AdministrationList1.wm";
126
127
128
129 RecordSet set = null;
130 DDRecord tableRS = new DDRecord(db, table);
131 tableRS.setFromForm(context);
132
133 if (resultsPerPage == null)
134 resultsPerPage =
135 tableRS.getMetaData().getResultsPerPage().toString();
136 if (resultsPerPage.equals(""))
137 resultsPerPage = "0";
138
139 if (list == true || context.getForm("edit") != null) {
140 sqlString =
141 buildQuery(
142 context,
143 tableRS,
144 table,
145 orderby,
146 ascdesc,
147 db,
148 selectExpression);
149 set =
150 RecordSet.getInstance(
151 context,
152 db,
153 table,
154 sqlString,
155 new Integer(resultsPerPage),
156 "Admin" + "." + table);
157 set.getState().put("ddtable", tableRS);
158 set.getState().put("resultsPerPage", resultsPerPage);
159 set.getState().put("ascdesc", ascdesc);
160 set.getState().put("orderby", orderby);
161 }
162 else {
163 set = RecordSet.getInstance(context, "Admin" + "." + table);
164 if (set != null) {
165 tableRS = (DDRecord)set.getState().get("ddtable");
166 resultsPerPage = (String)set.getState().get("resultsPerPage");
167 ascdesc = (String)set.getState().get("ascdesc");
168 orderby = (String)set.getState().get("orderby");
169 }
170 }
171 if (set != null) {
172 context.put("navigation", set);
173 context.put("results", set.getRows());
174 }
175 context.put(
176 "orderbydropdown",
177 getAllFieldsDropDown(tableRS, orderby));
178 context.put("ddtable", tableRS);
179
180 }
181
182
183
184 else if (action.equals("briefselect")) {
185 if (templateName == null)
186 templateName = "jal/view/select2.wm";
187 DDRecord tableRS = new DDRecord(db, table);
188 tableRS.setFromForm(context);
189
190
191 int minDO = Integer.MAX_VALUE;
192 DDField thisfield = null;
193 String reltable = "";
194 String relfield = "";
195 boolean found = false;
196
197
198 for (Enumeration en = tableRS.elements();
199 en.hasMoreElements();
200 ) {
201 DDField field = (DDField)en.nextElement();
202 if (field.getMetaData().getSelectDisplay()
203 && field.getMetaData().getDisplayOrder() < minDO) {
204 minDO = field.getMetaData().getDisplayOrder();
205
206
207
208 thisfield = field;
209 reltable = field.getMetaData().getRelationshipTable();
210 relfield = field.getMetaData().getRelationshipField();
211 found = true;
212 }
213 }
214
215
216 Vector rt = new Vector();
217 if (found) {
218 sqlString =
219 "SELECT id, "
220 + relfield
221 + " FROM "
222 + reltable
223 + " ORDER BY "
224 + relfield;
225 if (debug)
226 log.debug(sqlString);
227 Statement s = conn.createStatement();
228 ResultSet rs = s.executeQuery(sqlString);
229 while (rs.next()) {
230 ExtendedHash temp = new ExtendedHash();
231 temp.put("id", rs.getString(1).trim());
232 temp.put("value", rs.getString(2).trim());
233 rt.addElement(temp);
234 }
235 context.put("found", "true");
236
237 }
238 context.put("select", rt);
239 context.put("ddfield", thisfield);
240 context.put("selecttable", reltable);
241 context.put("selectfield", relfield);
242 }
243
244
245
246 else if (action.equals("display")) {
247 if (templateName == null)
248 templateName = "jal/view/AdministrationDisplay1.wm";
249 if (id != null)
250 idInteger = new Integer(id);
251 else
252 throw new HandlerException("No id specified.");
253 DDRecord ddTable = new DDRecord(db, table, idInteger);
254 context.put("ddtable", ddTable);
255 }
256
257
258
259 else if (action.equals("edit")) {
260
261 if (id != null)
262 idInteger = new Integer(id);
263 else
264 throw new HandlerException("No id specified.");
265 DDRecord ddTable = new DDRecord(db, table, idInteger);
266 if (templateName == null) {
267 if (table.equals("pagecontents"))
268 templateName = "jal/view/AdministrationEditPageContents1.wm";
269 else
270 templateName = "jal/view/AdministrationEdit1.wm";
271 }
272 if (id.equals("0")) {
273 ddTable.setFromForm(context);
274 ddTable.setFromDefaults();
275 }
276 else {
277 Vector links = ddTable.getLinksToThis(false, true);
278 context.put("links", links);
279 }
280 context.put("ddtable", ddTable);
281 }
282
283
284
285 else if (action.equals("update")) {
286 if (templateName == null)
287 templateName = "jal/view/AdministrationUpdate1.wm";
288 if (id != null)
289 idInteger = new Integer(id);
290 else
291 throw new HandlerException("No id specified.");
292
293
294
295 DDRecord ddTable = new DDRecord(db, table, idInteger);
296
297
298 if (context.getForm("delete_button") != null) {
299 Vector links = ddTable.getLinksToThis(true);
300 if (links.isEmpty()) {
301 ddTable.delete();
302 }
303 else {
304 context.put("notDeleted", links);
305 context.put("error", "Update Failed");
306 }
307
308 }
309 else {
310 ddTable.setFromForm(context);
311 if (context.getForm("duplicate_button") != null)
312 ddTable.setFieldValue("id", "0");
313 Vector problems = ddTable.getProblems();
314 if (problems.isEmpty()) {
315 ddTable.write();
316
317 if (!ddTable.getFieldValue("id").equals(id)) {
318 RecordSet set =
319 RecordSet.getInstance(context, "Admin" + "." + table);
320 if (set != null) {
321 set.addElement(new Integer(ddTable.getFieldValue("id")));
322 }
323 }
324 }
325 else {
326 context.put("problems", problems);
327 context.put("error", "Update Failed");
328 }
329 }
330 context.put("ddtable", ddTable);
331 }
332
333
334
335
336 else if (
337 action.equals("multipleupdate")
338 && context.getForm("add_button") == null) {
339 if (templateName == null)
340 templateName = "jal/view/AdministrationMultipleUpdate1.wm";
341
342
343
344 Vector results = new Vector();
345 boolean anyProbs = false;
346 String[] postfixList = context.getFormList("postfix");
347 DDRecord[] recordList = new DDRecord[postfixList.length];
348
349
350 for (int i = 0; i < postfixList.length; i++) {
351
352 DDRecord ddTable;
353
354 String postfix = postfixList[i];
355 boolean newRec = (postfix.lastIndexOf("New") != -1);
356 if (newRec)
357 ddTable = new DDRecord(db, table, new Integer("0"));
358 else
359 ddTable = new DDRecord(db, table, new Integer(postfix));
360 ExtendedHash record = new ExtendedHash();
361 boolean prob = false;
362
363
364 if (context.getForm("delete" + postfix) != null) {
365 if (!newRec) {
366 Vector links = ddTable.getLinksToThis(true);
367 if (!links.isEmpty()) {
368 record.put("notDeleted", links);
369 prob = true;
370 }
371 }
372
373 }
374 else {
375 ddTable.setFromForm(context, postfix);
376 Vector problems = ddTable.getProblems();
377 if (!problems.isEmpty()) {
378 record.put("problems", problems);
379 prob = true;
380 }
381 }
382
383 if (prob) {
384 anyProbs = true;
385 record.put("ddtable", ddTable);
386 record.put("postfix", postfix);
387 results.addElement(record);
388 }
389
390 recordList[i] = ddTable;
391 }
392
393
394 if (anyProbs) {
395 context.put(
396 "error",
397 "There were errors. No changes have been made");
398 context.put("results", results);
399
400
401 }
402 else {
403 for (int i = 0; i < recordList.length; i++) {
404 if (context.getForm("delete" + postfixList[i]) != null) {
405 if (postfixList[i].lastIndexOf("New") == -1)
406
407 recordList[i].delete();
408 }
409 else
410 recordList[i].write();
411 }
412 }
413 context.put("ddtable", new DDRecord(db, table));
414
415 }
416
417
418
419 else if (
420 action.equals("multipleupdate")
421 && context.getForm("add_button") != null) {
422 String currtemplate = context.getForm("currtemplate");
423 if (currtemplate != null)
424 templateName = currtemplate;
425 else
426 templateName = "jal/view/AdministrationEditList1.wm";
427
428 Vector results = new Vector();
429 String[] postfixList = context.getFormList("postfix");
430
431 context.put("ddtable", new DDRecord(db, table));
432
433
434
435
436
437 String highest = "";
438 String current = "";
439
440 if (postfixList != null) {
441 for (int i = 0; i < postfixList.length; i++) {
442 String postfix = postfixList[i];
443 DDRecord ddTable = new DDRecord(db, table);
444 ddTable.setFromForm(context, postfix);
445 ddTable.put("postfix", postfix);
446
447 int startOfNewID = postfix.lastIndexOf("New");
448 if (startOfNewID != -1) {
449 current = postfix.substring(startOfNewID + 3);
450 if (current.compareTo(highest) > 0)
451 highest = current;
452 }
453 results.addElement(ddTable);
454 }
455 }
456
457 if (highest.equals(""))
458 highest = "0";
459 int newID = new Integer(highest).intValue() + 1;
460 DDRecord ddTable = new DDRecord(db, table);
461 ddTable.setFromDefaults();
462 ddTable.setFieldValue("id", "0");
463 ddTable.put("postfix", "New" + newID);
464 results.addElement(ddTable);
465
466 context.put("results", results);
467 }
468 }
469
470
471
472 }
473 catch (Exception e) {
474 context.put("sqlstring", sqlString);
475 log.exception(e);
476 throw (HandlerException)new HandlerException(e.toString()).initCause(e);
477 }
478 finally {
479 connMgr.freeConnection(db, conn);
480 }
481
482
483
484
485
486
487 context.put("db", db);
488 context.put("table", table);
489 context.put("id", id);
490 context.put("wmtemplate", templateName);
491 context.put("orderby", orderby);
492 context.put("ascdesc", ascdesc);
493 context.put("resultsperpage", resultsPerPage);
494 context.put("returnURL", returnURL);
495 context.put("selectedid", selectedid);
496 context.put("list", new Boolean(list));
497 context.put("selectexpression", selectExpression);
498 context.put("display", context.getForm("display"));
499
500
501 try {
502 return (Template)context.getBroker().get("template", templateName);
503 }
504 catch (Exception e) {
505 throw new HandlerException(
506 "Problem loading template " + templateName + " : " + e.toString());
507 }
508 }
509
510
511
512
513
514
515
516
517
518
519 private Vector getAllFieldsDropDown(DDRecord tableRS, String orderby)
520 throws Exception {
521 Vector orderByDropDown = new Vector();
522 for (Enumeration en = tableRS.elements(); en.hasMoreElements();) {
523 DDField field = (DDField)en.nextElement();
524 ExtendedHash row = new ExtendedHash();
525 row.put("value", field.getMetaData().getFieldName());
526 row.put("description", field.getMetaData().getDisplayName());
527 row.put(
528 "selected",
529 checkSelected(orderby, field.getMetaData().getFieldName()));
530 orderByDropDown.addElement(row);
531 }
532 return orderByDropDown;
533 }
534
535 private String checkSelected(String a, String b) {
536 if ((a != null) && (b != null) && a.equals(b))
537 return "selected";
538 else
539 return "";
540 }
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558 public String buildQuery(
559 WebContext context,
560 DDRecord tableRS,
561 String table,
562 String orderby,
563 String ascdesc,
564 String db,
565 String selectExpression)
566 throws Exception {
567
568 UniqueVector fromVector = new UniqueVector();
569 UniqueVector whereVector = new UniqueVector();
570 fromVector.addUniqueElement(table);
571 String orderString = "";
572
573
574
575
576 for (Enumeration en = tableRS.elements(); en.hasMoreElements();) {
577 DDField field = (DDField)en.nextElement();
578 String currentSetting = context.getForm(field.getMetaData().getFieldName());
579 if ((currentSetting != null) && (currentSetting.equals("0")))
580 currentSetting = null;
581 if ((currentSetting != null) && (currentSetting.equals("All")))
582 currentSetting = null;
583 if ((currentSetting != null) && (currentSetting.equals("None")))
584 currentSetting = null;
585 if ((currentSetting != null) && (currentSetting.equals("")))
586 currentSetting = null;
587
588
589
590 if (currentSetting != null) {
591
592 if (field.getMetaData().getRelationshipTable() != "") {
593 try {
594 Integer.parseInt(currentSetting);
595 whereVector.addUniqueElement(
596 new String(
597 field.getMetaData().getFieldName() + "=" + currentSetting));
598
599 }
600 catch (NumberFormatException e) {
601 fromVector.addUniqueElement(
602 new String(field.getMetaData().getRelationshipTable()));
603 whereVector.addUniqueElement(
604 new String(
605 field.getMetaData().getRelationshipTable()
606 + ".id = "
607 + table
608 + "."
609 + field.getMetaData().getFieldName()));
610 whereVector.addUniqueElement(
611 new String(
612 field.getMetaData().getRelationshipTable()
613 + "."
614 + field.getMetaData().getRelationshipField()
615 + " = '"
616 + currentSetting
617 + "'"));
618 }
619 }
620 else {
621 if (!currentSetting.equals("")) {
622 if (connMgr.getDatabaseEngineType(db)
623 == DBConnectionManager.ACCESS) {
624 whereVector.addUniqueElement(
625 new String(
626 field.getMetaData().getFieldName()
627 + " LIKE '%"
628 + currentSetting
629 + "%'"));
630 }
631 else {
632 whereVector.addUniqueElement(
633 new String(
634 field.getMetaData().getFieldName()
635 + " ~* '"
636 + currentSetting
637 + "'"));
638 }
639 }
640 }
641 }
642
643 if (selectExpression != null && !selectExpression.equals("")) {
644 if (debug)
645 log.debug("SelectExpression:" + selectExpression + ":");
646 whereVector.addUniqueElement(selectExpression);
647 }
648
649
650
651
652 if (orderby.equals(field.getMetaData().getFieldName())) {
653 if (field.getMetaData().getRelationshipTable() != "") {
654 fromVector.addUniqueElement(
655 new String(field.getMetaData().getRelationshipTable()));
656 orderString =
657 " ORDER BY "
658 + field.getMetaData().getRelationshipTable()
659 + "."
660 + field.getMetaData().getRelationshipField();
661 whereVector.addUniqueElement(
662 new String(
663 field.getMetaData().getRelationshipTable()
664 + ".id = "
665 + table
666 + "."
667 + field.getMetaData().getFieldName()));
668 }
669 else {
670 orderString =
671 " ORDER BY " + table + "." + field.getMetaData().getFieldName();
672 }
673 }
674 }
675
676
677
678 String sqlString = "SELECT " + table + ".* FROM " + fromVector.join(", ");
679
680 if (whereVector.isEmpty()) {
681 whereVector.addUniqueElement("id>0");
682 }
683 sqlString += " WHERE " + whereVector.join(" AND ");
684 sqlString += orderString + " " + ascdesc;
685 if (debug)
686 log.debug("sqlString: " + sqlString);
687 return sqlString;
688 }
689
690 private String checkPermissions(
691 WebContext context,
692 String table,
693 String id,
694 String action)
695 throws Exception {
696
697 String templateName = null;
698
699
700 templateName =
701 User.checkLoggedIn(
702 "controller",
703 this.getClass().getName(),
704 context,
705 " access the Database Administration pages.");
706 if (templateName != null)
707 return templateName;
708
709
710 if (action.equals("tables"))
711 templateName =
712 User.checkLoggedIn(
713 "controller",
714 "AdminTables",
715 context,
716 " access the Database Administration pages.");
717 if (templateName != null)
718 return templateName;
719
720 if (action.equals("select"))
721 templateName =
722 User.checkLoggedIn(
723 "controller",
724 "AdminSelect",
725 context,
726 " access the Database Administration pages.");
727 if (templateName != null)
728 return templateName;
729
730 if (action.equals("briefselect"))
731 templateName =
732 User.checkLoggedIn(
733 "controller",
734 "AdminBriefSelect",
735 context,
736 " access the Database Administration pages.");
737 if (templateName != null)
738 return templateName;
739
740 if (action.equals("display"))
741 templateName =
742 User.checkLoggedIn(
743 "controller",
744 "AdminDisplay",
745 context,
746 " access the Database Administration pages.");
747 if (templateName != null)
748 return templateName;
749
750 if (action.equals("edit"))
751 templateName =
752 User.checkLoggedIn(
753 "controller",
754 "AdminEdit",
755 context,
756 " access the Database Administration pages.");
757 if (templateName != null)
758 return templateName;
759
760 if (action.equals("update"))
761 templateName =
762 User.checkLoggedIn(
763 "controller",
764 "AdminUpdate",
765 context,
766 " access the Database Administration pages.");
767 if (templateName != null)
768 return templateName;
769
770 if (action.equals("multipleupdate"))
771 templateName =
772 User.checkLoggedIn(
773 "controller",
774 "AdminMultipleUpdate",
775 context,
776 " access the Database Administration pages.");
777 if (templateName != null)
778 return templateName;
779
780
781 if (table == null)
782 return null;
783 templateName =
784 User.checkLoggedIn("table", table, context, " access this data.");
785 if (templateName != null)
786 return templateName;
787
788
789 if (action.equals("edit")
790 || action.equals("update")
791 || action.equals("multipleupdate"))
792 templateName =
793 User.checkLoggedIn("tableedit", table, context, " edit this data.");
794 if (templateName != null)
795 return templateName;
796
797
798 if (id == null)
799 return null;
800 templateName =
801 User.checkLoggedIn(table, id, context, " access the This Record.");
802 if (templateName != null)
803 return templateName;
804
805 return null;
806 }
807 }