001: package org.airtodo.db;
002:
003: import java.sql.PreparedStatement;
004: import java.sql.ResultSet;
005: import java.sql.SQLException;
006: import java.util.HashSet;
007: import java.util.Map;
008: import java.util.Set;
009: import java.util.Vector;
010:
011: import org.air.framework.core.Registry;
012: import org.air.framework.core.Utils;
013: import org.air.framework.db.Db;
014: import org.air.framework.db.DbSelectUtil;
015: import org.air.framework.db.ISqlSelect;
016: import org.air.framework.db.Ps;
017: import org.air.framework.records.Attachments;
018: import org.air.framework.records.Common;
019: import org.airtodo.records.Customers;
020: import org.airtodo.records.Employees;
021: import org.airtodo.records.Initiators;
022: import org.airtodo.records.Modules;
023: import org.airtodo.records.Notes;
024: import org.airtodo.records.Projects;
025: import org.airtodo.records.TaskTypes;
026: import org.airtodo.records.Tasks;
027:
028: public class DbUtils {
029: //
030: // createDatabase
031: //
032: public static void prepareDatabase() throws SQLException {
033: Ps.getPs("script_product_initialize_database").execute();
034: createViewCustomers();
035: createViewEmployees();
036: createViewModules();
037: createViewInitializers();
038: createViewTaskTypes();
039: createViewProjects();
040: createViewTasks();
041: createViewNotes();
042: createViewProjectRelations();
043: createViewModulesRelations();
044: createViewTasksRelations();
045: createViewNotesRelations();
046: }
047:
048: // ******************* //
049: // *** S E L E C T *** //
050: // ******************* //
051: //
052: // selectNotesByMasterId
053: //
054: public static Vector<Map> selectNotesByMasterId(final long masterId)
055: throws SQLException {
056: return DbSelectUtil.select(new ISqlSelect() {
057: public PreparedStatement getPreparedStatement()
058: throws SQLException {
059: PreparedStatement ps = Ps
060: .getPs("select_notes_by_master_id");
061: ps.setString(1, Utils
062: .getColumnIdentifier(Notes.MASTER_ID));
063: ps.setLong(2, masterId);
064: return ps;
065: }
066:
067: public void fillMap(ResultSet rs, Map map)
068: throws SQLException {
069: Map mapNote = (Map) rs.getObject(6);
070: map.putAll(mapNote);
071: Map mapInitiator = (Map) rs.getObject(7);
072: if (mapInitiator != null)
073: map
074: .putAll(convertEmployeesMapToInitiatorsMap(mapInitiator));
075: }
076: });
077: }
078:
079: //
080: // selectModulesByProjectId
081: //
082: public static Vector<Map> selectModulesByProjectId(
083: final long masterId) throws SQLException {
084: return DbSelectUtil.select(new ISqlSelect() {
085: public PreparedStatement getPreparedStatement()
086: throws SQLException {
087: PreparedStatement ps = Ps
088: .getPs("select_modules_by_project_id");
089: ps.setLong(1, masterId);
090: return ps;
091: }
092:
093: public void fillMap(ResultSet rs, Map map)
094: throws SQLException {
095: map.putAll((Map) rs.getObject(5));
096: map.putAll((Map) rs.getObject(6));
097: map.putAll((Map) rs.getObject(7));
098: }
099: });
100: }
101:
102: //
103: // selectTasksByFilter
104: //
105: public static Vector<Map> selectTasksByFilter() throws SQLException {
106: return DbSelectUtil.select(new ISqlSelect() {
107: public PreparedStatement getPreparedStatement()
108: throws SQLException {
109: PreparedStatement ps = Ps
110: .getPs("select_tasks_by_filter");
111: //
112: Long customerId0 = Registry.getFilterId().get(
113: Customers.class);
114: long customerId = customerId0 == null ? -1
115: : customerId0;
116: ps.setLong(1, customerId);
117: //
118: Long projectId0 = Registry.getFilterId().get(
119: Projects.class);
120: long projectId = projectId0 == null ? -1 : projectId0;
121: ps.setLong(2, projectId);
122: //
123: Long employeeId0 = Registry.getFilterId().get(
124: Employees.class);
125: long employeeId = employeeId0 == null ? -1
126: : employeeId0;
127: ps.setLong(3, employeeId);
128: //
129: Long initiatorId0 = Registry.getFilterId().get(
130: Initiators.class);
131: long initiatorId = initiatorId0 == null ? -1
132: : initiatorId0;
133: ps.setLong(4, initiatorId);
134: //
135: Long taskTypeId0 = Registry.getFilterId().get(
136: TaskTypes.class);
137: long taskTypeId = taskTypeId0 == null ? -1
138: : taskTypeId0;
139: ps.setLong(5, taskTypeId);
140: //
141: Long moduleId0 = Registry.getFilterId().get(
142: Modules.class);
143: long moduleId = moduleId0 == null ? -1 : moduleId0;
144: ps.setLong(6, moduleId);
145: //
146: return ps;
147: }
148:
149: public void fillMap(ResultSet rs, Map map)
150: throws SQLException {
151: map.putAll((Map) rs.getObject(5));
152: Map mapProject = (Map) rs.getObject(6);
153: if (mapProject != null)
154: map.putAll(mapProject);
155: Map mapCustomer = (Map) rs.getObject(7);
156: if (mapCustomer != null)
157: map.putAll(mapCustomer);
158: Map mapTaskType = (Map) rs.getObject(8);
159: if (mapTaskType != null)
160: map.putAll(mapTaskType);
161: Map mapEmployee = (Map) rs.getObject(9);
162: if (mapEmployee != null)
163: map.putAll(mapEmployee);
164: Map mapInitiator = (Map) rs.getObject(10);
165: if (mapInitiator != null)
166: map
167: .putAll(convertEmployeesMapToInitiatorsMap(mapInitiator));
168: Map mapModule = (Map) rs.getObject(11);
169: if (mapModule != null)
170: map.putAll(mapModule);
171: }
172: });
173: }
174:
175: public static Map convertEmployeesMapToInitiatorsMap(Map map) {
176: return Utils.convertMapToMap(Employees.class, Initiators.class,
177: map);
178: }
179:
180: //
181: // selectProjectsByFilter
182: //
183: public static Vector<Map> selectProjectsByFilter()
184: throws SQLException {
185: return DbSelectUtil.select(new ISqlSelect() {
186: public PreparedStatement getPreparedStatement()
187: throws SQLException {
188: PreparedStatement ps = Ps
189: .getPs("select_projects_by_filter");
190: //
191: Long customerId0 = Registry.getFilterId().get(
192: Customers.class);
193: long customerId = customerId0 == null ? -1
194: : customerId0;
195: ps.setLong(1, customerId);
196: //
197: Long projectId0 = Registry.getFilterId().get(
198: Projects.class);
199: long projectId = projectId0 == null ? -1 : projectId0;
200: ps.setLong(2, projectId);
201: //
202: return ps;
203: }
204:
205: public void fillMap(ResultSet rs, Map map)
206: throws SQLException {
207: Map mapProject = (Map) rs.getObject(5);
208: map.putAll((Map) mapProject);
209: Map mapCustomer = (Map) rs.getObject(6);
210: if (mapCustomer != null)
211: map.putAll(mapCustomer);
212: }
213: });
214: }
215:
216: //
217: // selectCustomer
218: //
219: public static Vector<Map> selectCustomer() throws SQLException {
220: return DbSelectUtil.select(new ISqlSelect() {
221: public PreparedStatement getPreparedStatement()
222: throws SQLException {
223: PreparedStatement ps = Ps
224: .getPs("select_document_header");
225: ps.setString(1, Customers.class.getSimpleName());
226: return ps;
227: }
228:
229: public void fillMap(ResultSet rs, Map map)
230: throws SQLException {
231: map.putAll((Map) rs.getObject(5));
232: }
233: });
234: }
235:
236: //
237: // selectEmployee
238: //
239: public static Vector<Map> selectEmployee() throws SQLException {
240: return DbSelectUtil.select(new ISqlSelect() {
241: public PreparedStatement getPreparedStatement()
242: throws SQLException {
243: PreparedStatement ps = Ps
244: .getPs("select_document_header");
245: ps.setString(1, Employees.class.getSimpleName());
246: return ps;
247: }
248:
249: public void fillMap(ResultSet rs, Map map)
250: throws SQLException {
251: map.putAll((Map) rs.getObject(5));
252: }
253: });
254: }
255:
256: //
257: // selectItem
258: //
259: public static Vector<Map> selectItem() throws SQLException {
260: return DbSelectUtil.select(new ISqlSelect() {
261: public PreparedStatement getPreparedStatement()
262: throws SQLException {
263: PreparedStatement ps = Ps
264: .getPs("select_document_header");
265: ps.setString(1, TaskTypes.class.getSimpleName());
266: return ps;
267: }
268:
269: public void fillMap(ResultSet rs, Map map)
270: throws SQLException {
271: map.putAll((Map) rs.getObject(5));
272: }
273: });
274: }
275:
276: private static Set<Map> deleteSlaves(Set<Map> maps,
277: Enum slaveIdInMaster) throws SQLException {
278: PreparedStatement ps = Ps.getPs("delete_from_lookup_I");
279: ps.setString(2, Utils.getColumnIdentifier(slaveIdInMaster));
280: //
281: Set deletedSet = new HashSet();
282: for (Map map : maps) {
283: ps.setLong(1, (Long) map.get(Utils
284: .getColumnIdentifier(Common.ID)));
285: if (ps.executeUpdate() == 1)
286: deletedSet.add(map);
287: }
288: return deletedSet;
289: }
290:
291: public static Set<Map> deleteCustomers(Set<Map> maps)
292: throws SQLException {
293: return deleteSlaves(maps, Projects.CUSTOMER_ID);
294: }
295:
296: public static Set<Map> deleteEmployees(Set<Map> maps)
297: throws SQLException {
298: PreparedStatement ps = Ps.getPs("delete_from_lookup_II");
299: ps.setString(2, Utils.getColumnIdentifier(Tasks.EMPLOYEE_ID));
300: ps.setString(3, Utils.getColumnIdentifier(Tasks.INITIATOR_ID));
301: //
302: Set deletedSet = new HashSet();
303: for (Map map : maps) {
304: ps.setLong(1, (Long) map.get(Utils
305: .getColumnIdentifier(Common.ID)));
306: if (ps.executeUpdate() == 1)
307: deletedSet.add(map);
308: }
309: return deletedSet;
310: }
311:
312: public static Set<Map> deleteTaskTypes(Set<Map> maps)
313: throws SQLException {
314: return deleteSlaves(maps, Tasks.TASK_TYPE_ID);
315: }
316:
317: public static Set<Map> deleteProjects(Set<Map> maps)
318: throws SQLException {
319: PreparedStatement ps0 = Ps.getPs("delete_from_lookup_I");
320: ps0.setString(2, Utils
321: .getColumnIdentifier(Projects.CUSTOMER_ID));
322: //
323: PreparedStatement ps1 = Ps
324: .getPs("delete.document_rows_by_header_id");
325: ps1.setString(1, Utils.getColumnIdentifier(Tasks.PROJECT_ID));
326: //
327: Set deletedSet = new HashSet();
328: for (Map map : maps) {
329: ps0.setLong(1, (Long) map.get(Utils
330: .getColumnIdentifier(Common.ID)));
331: if (ps0.executeUpdate() == 1) {
332: deletedSet.add(map);
333: ps1.setLong(2, (Long) map.get(Utils
334: .getColumnIdentifier(Common.ID)));
335: ps1.executeUpdate();
336: }
337: }
338: return deletedSet;
339: }
340:
341: //
342: // selectLinksByMasterId
343: //
344: private static Vector<Map> selectLinksByMasterId(
345: final long masterId, final Enum enumMasterId)
346: throws SQLException {
347: return DbSelectUtil.select(new ISqlSelect() {
348: public PreparedStatement getPreparedStatement()
349: throws SQLException {
350: PreparedStatement ps = Ps
351: .getPs("select_links_by_master_id");
352: ps
353: .setString(1, Utils
354: .getColumnIdentifier(enumMasterId));
355: ps.setString(2, enumMasterId.getDeclaringClass()
356: .getSimpleName());
357: ps.setLong(3, masterId);
358: return ps;
359: }
360:
361: public void fillMap(ResultSet rs, Map map)
362: throws SQLException {
363: map.putAll((Map) rs.getObject(5));
364: }
365: });
366: }
367:
368: //
369: // selectAttachmentsByMasterId
370: //
371: public static Vector<Map> selectAttachmentsByMasterId(long masterId)
372: throws SQLException {
373: return selectLinksByMasterId(masterId, Attachments.MASTER_ID);
374: }
375:
376: //////////////////////////////////////////////////////////////////////////////////////////////////////////////
377: public static void createViewCustomers() throws SQLException {
378: String sql = Ps.getString("create_view_customers");
379: sql = String.format(sql, Customers.class.getSimpleName());
380: Db.getInstance().getStatement().executeUpdate(sql);
381: }
382:
383: public static void createViewEmployees() throws SQLException {
384: String sql = Ps.getString("create_view_employees");
385: sql = String.format(sql, Employees.class.getSimpleName());
386: Db.getInstance().getStatement().executeUpdate(sql);
387: }
388:
389: public static void createViewModules() throws SQLException {
390: String sql = Ps.getString("create_view_modules");
391: sql = String.format(sql, Utils
392: .getColumnIdentifier(Modules.PROJECT_ID), Modules.class
393: .getSimpleName());
394: Db.getInstance().getStatement().executeUpdate(sql);
395: }
396:
397: public static void createViewInitializers() throws SQLException {
398: String sql = Ps.getString("create_view_initializers");
399: sql = String.format(sql, Employees.class.getSimpleName());
400: Db.getInstance().getStatement().executeUpdate(sql);
401: }
402:
403: public static void createViewTaskTypes() throws SQLException {
404: String sql = Ps.getString("create_view_task_types");
405: sql = String.format(sql, TaskTypes.class.getSimpleName());
406: Db.getInstance().getStatement().executeUpdate(sql);
407: }
408:
409: public static void createViewProjects() throws SQLException {
410: String sql = Ps.getString("create_view_projects");
411: sql = String.format(sql, Utils
412: .getColumnIdentifier(Projects.CUSTOMER_ID),
413: Projects.class.getSimpleName());
414: Db.getInstance().getStatement().executeUpdate(sql);
415: }
416:
417: public static void createViewNotes() throws SQLException {
418: String sql = Ps.getString("create_view_notes");
419: sql = String.format(sql, Utils
420: .getColumnIdentifier(Notes.INITIATOR_ID), Notes.class
421: .getSimpleName());
422: Db.getInstance().getStatement().executeUpdate(sql);
423: }
424:
425: public static void createViewTasks() throws SQLException {
426: String sql = Ps.getString("create_view_tasks");
427: sql = String.format(sql, //
428: Utils.getColumnIdentifier(Tasks.PROJECT_ID), //
429: Utils.getColumnIdentifier(Tasks.TASK_TYPE_ID), //
430: Utils.getColumnIdentifier(Tasks.EMPLOYEE_ID), //
431: Utils.getColumnIdentifier(Tasks.INITIATOR_ID), //
432: Utils.getColumnIdentifier(Tasks.MODULE_ID), //
433: Tasks.class.getSimpleName());
434: Db.getInstance().getStatement().executeUpdate(sql);
435: }
436:
437: public static void createViewProjectRelations() throws SQLException {
438: PreparedStatement ps = Ps
439: .getPs("create_view_projects_relations");
440: ps.executeUpdate();
441: }
442:
443: public static void createViewModulesRelations() throws SQLException {
444: PreparedStatement ps = Ps
445: .getPs("create_view_modules_relations");
446: ps.executeUpdate();
447: }
448:
449: public static void createViewTasksRelations() throws SQLException {
450: PreparedStatement ps = Ps.getPs("create_view_tasks_relations");
451: ps.executeUpdate();
452: }
453:
454: public static void createViewNotesRelations() throws SQLException {
455: PreparedStatement ps = Ps.getPs("create_view_notes_relations");
456: ps.executeUpdate();
457: }
458: }
|