1 package org.kuali.student.enrollment.acal;
2
3
4 import java.text.SimpleDateFormat;
5 import java.util.Calendar;
6 import java.util.Date;
7 import java.util.GregorianCalendar;
8 import java.util.HashMap;
9 import java.util.HashSet;
10 import java.util.Map;
11 import java.util.Set;
12
13 public class SqlGenerator {
14 private String userId = "admin";
15 private static final String ATP_STATE = "kuali.atp.state.Official";
16 private static final String MILESTONE_STATE = "kuali.milestone.state.Official";
17 private static final String ATPATPRELATION_STATE = "kuali.atp.atp.relation.state.active";
18
19 private Set<String> holiCals = new HashSet<String>();
20
21 public StringBuilder getSqlForAcademicCalendar (StringBuilder builder, Atp acal) {
22 if (null == builder) {
23 builder = new StringBuilder();
24 }
25
26 Calendar cal = new GregorianCalendar();
27 cal.setTime(acal.getStartDate());
28 cal.add(Calendar.YEAR, -1);
29 cal.set(Calendar.MONTH, Calendar.JANUARY);
30 cal.set(Calendar.DATE, 1);
31 Date createDate = cal.getTime();
32
33
34 getAtpInsert(builder, acal, createDate);
35
36 for (Atp holiCal : acal.getHolidayCalendars()) {
37 if (!holiCals.contains(holiCal.getId())) {
38
39 getAtpInsert(builder, holiCal, createDate);
40 }
41
42
43 getAtpAtpRelationSql(builder, acal, holiCal, "kuali.atp.atp.relation.associated", createDate);
44
45 if (!holiCals.contains(holiCal.getId())) {
46 for (Milestone holiday : holiCal.getMilestones()) {
47
48 getMilestoneInsert(builder, holiday, createDate);
49
50 getAtpMilestoneRelationSql(builder, holiCal, holiday, createDate);
51 }
52 holiCals.add(holiCal.getId());
53 }
54 }
55
56 for (Atp term : acal.getTerms()) {
57
58 getAtpInsert(builder, term, createDate);
59
60 getAtpAtpRelationSql(builder, acal, term, "kuali.atp.atp.relation.includes", createDate);
61
62 for (Milestone keydate : term.getMilestones()) {
63
64 getMilestoneInsert(builder, keydate, createDate);
65
66 getAtpMilestoneRelationSql(builder, term, keydate, createDate);
67 }
68
69 for (Atp subTerm : term.getTerms()) {
70
71 getAtpInsert(builder, subTerm, createDate);
72
73 getAtpAtpRelationSql(builder, term, subTerm, "kuali.atp.atp.relation.includes", createDate);
74
75 for (Milestone keydate : subTerm.getMilestones()) {
76
77 getMilestoneInsert(builder, keydate, createDate);
78
79 getAtpMilestoneRelationSql(builder, subTerm, keydate, createDate);
80 }
81 }
82 }
83
84 return builder;
85 }
86
87 private StringBuilder getAtpInsert(StringBuilder builder, Atp atp, Date created) {
88 StringBuilder statement = new StringBuilder();
89 statement.append("INSERT INTO KSEN_ATP ");
90 statement.append("( ID");
91 statement.append(", OBJ_ID");
92 statement.append(", ATP_TYPE");
93 statement.append(", ATP_STATE");
94 statement.append(", NAME");
95 statement.append(", DESCR_PLAIN");
96 statement.append(", DESCR_FORMATTED");
97 statement.append(", ATP_CD");
98 statement.append(", END_DT");
99 statement.append(", START_DT");
100 statement.append(", ADMIN_ORG_ID");
101 statement.append(", VER_NBR");
102 statement.append(", CREATETIME");
103 statement.append(", CREATEID");
104 statement.append(", UPDATETIME");
105 statement.append(", UPDATEID");
106 statement.append(") VALUES (");
107 statement.append(" ").append(getInput(atp.getId()));
108 statement.append(", ").append(getInput(getUUID()));
109 statement.append(", ").append(getInput(atp.getType()));
110 statement.append(", ").append(getInput(ATP_STATE));
111 statement.append(", ").append(getInput(atp.getName()));
112 statement.append(", ").append(getInput(atp.getDescriptionPlain()));
113 statement.append(", ").append(getInput(atp.getDescriptionFormatted()));
114 statement.append(", ").append(getInput(atp.getAtpCode()));
115 statement.append(", ").append(getInput(atp.getEndDate()));
116 statement.append(", ").append(getInput(atp.getStartDate()));
117 statement.append(", ").append(getInput(atp.getAdminOrgId()));
118 statement.append(", 0");
119 statement.append(", ").append(getInput(created));
120 statement.append(", ").append(getInput(userId));
121 statement.append(", ").append(getInput(created));
122 statement.append(", ").append(getInput(userId));
123 statement.append(")");
124 statement.append("\n");
125 statement.append("/\n");
126
127 if (builder == null) {
128 return statement;
129 }
130 return builder.append(statement);
131 }
132
133 private StringBuilder getMilestoneInsert(StringBuilder builder, Milestone milestone, Date created) {
134 StringBuilder statement = new StringBuilder();
135 statement.append("INSERT INTO KSEN_MSTONE ");
136 statement.append("( ID");
137 statement.append(", OBJ_ID");
138 statement.append(", MSTONE_TYPE");
139 statement.append(", MSTONE_STATE");
140 statement.append(", NAME");
141 statement.append(", DESCR_PLAIN");
142 statement.append(", DESCR_FORMATTED");
143 statement.append(", IS_ALL_DAY");
144 statement.append(", IS_INSTRCT_DAY");
145 statement.append(", IS_RELATIVE");
146 statement.append(", RELATIVE_ANCHOR_MSTONE_ID");
147 statement.append(", IS_DATE_RANGE");
148 statement.append(", START_DT");
149 statement.append(", END_DT");
150 statement.append(", VER_NBR");
151 statement.append(", CREATETIME");
152 statement.append(", CREATEID");
153 statement.append(", UPDATETIME");
154 statement.append(", UPDATEID");
155 statement.append(") VALUES (");
156 statement.append(" ").append(getInput(milestone.getId()));
157 statement.append(", ").append(getInput(getUUID()));
158 statement.append(", ").append(getInput(milestone.getType()));
159 statement.append(", ").append(getInput(MILESTONE_STATE));
160 statement.append(", ").append(getInput(milestone.getName()));
161 statement.append(", ").append(getInput(milestone.getDescriptionPlain()));
162 statement.append(", ").append(getInput(milestone.getDescriptionFormatted()));
163 statement.append(", ").append(getInput(milestone.isAllDay()));
164 statement.append(", 0");
165 statement.append(", ").append(getInput(milestone.isRelative()));
166 statement.append(", ").append(getInput(milestone.getRelativeMilestoneId()));
167 statement.append(", ").append(getInput(milestone.isDateRange()));
168 statement.append(", ").append(getInput(milestone.getStartDate()));
169 statement.append(", ").append(getInput(milestone.getEndDate()));
170 statement.append(", 0");
171 statement.append(", ").append(getInput(created));
172 statement.append(", ").append(getInput(userId));
173 statement.append(", ").append(getInput(created));
174 statement.append(", ").append(getInput(userId));
175 statement.append(")");
176 statement.append("\n");
177 statement.append("/\n");
178
179 if (builder == null) {
180 return statement;
181 }
182 return builder.append(statement);
183 }
184
185 public StringBuilder getAtpAtpRelationSql(StringBuilder builder, Atp atp, Atp relatedAtp, String relationType, Date created) {
186 StringBuilder statement = new StringBuilder();
187 statement.append("INSERT INTO KSEN_ATPATP_RELTN ");
188 statement.append("( ID");
189 statement.append(", OBJ_ID");
190 statement.append(", ATP_TYPE");
191 statement.append(", ATP_STATE");
192 statement.append(", ATP_ID");
193 statement.append(", RELATED_ATP_ID");
194 statement.append(", EFF_DT");
195 statement.append(", EXPIR_DT");
196 statement.append(", VER_NBR");
197 statement.append(", CREATETIME");
198 statement.append(", CREATEID");
199 statement.append(", UPDATETIME");
200 statement.append(", UPDATEID");
201 statement.append(") VALUES (");
202 statement.append(" ").append(getInput(atp.getId() + relatedAtp.getId() + "RELATION"));
203 statement.append(", ").append(getInput(getUUID()));
204 statement.append(", ").append(getInput(relationType));
205 statement.append(", ").append(getInput(ATPATPRELATION_STATE));
206 statement.append(", ").append(getInput(atp.getId()));
207 statement.append(", ").append(getInput(relatedAtp.getId()));
208 statement.append(", ").append(getInput(created));
209 statement.append(", ").append(getInput((Date)null));
210 statement.append(", 0");
211 statement.append(", ").append(getInput(created));
212 statement.append(", ").append(getInput(userId));
213 statement.append(", ").append(getInput(created));
214 statement.append(", ").append(getInput(userId));
215 statement.append(")");
216 statement.append("\n");
217 statement.append("/\n");
218
219 if (builder == null) {
220 return statement;
221 }
222 return builder.append(statement);
223 }
224
225 public StringBuilder getAtpMilestoneRelationSql(StringBuilder builder, Atp atp, Milestone milestone, Date created) {
226 StringBuilder statement = new StringBuilder();
227 statement.append("INSERT INTO KSEN_ATPMSTONE_RELTN ");
228 statement.append("( ID");
229 statement.append(", OBJ_ID");
230 statement.append(", VER_NBR");
231 statement.append(", CREATEID");
232 statement.append(", CREATETIME");
233 statement.append(", UPDATEID");
234 statement.append(", UPDATETIME");
235 statement.append(", ATP_ID");
236 statement.append(", MSTONE_ID");
237 statement.append(") VALUES (");
238 statement.append(" ").append(getInput(atp.getId() + milestone.getId() + "RELATION"));
239 statement.append(", ").append(getInput(getUUID()));
240 statement.append(", 0");
241 statement.append(", ").append(getInput(userId));
242 statement.append(", ").append(getInput(created));
243 statement.append(", ").append(getInput(userId));
244 statement.append(", ").append(getInput(created));
245 statement.append(", ").append(getInput(atp.getId()));
246 statement.append(", ").append(getInput(milestone.getId()));
247 statement.append(")");
248 statement.append("\n");
249 statement.append("/\n");
250
251 if (builder == null) {
252 return statement;
253 }
254 return builder.append(statement);
255 }
256
257 private String getInput(String string) {
258 if (string == null) {
259 return "NULL";
260 }
261 string = string.replaceAll("'", "''");
262 return "'"+string+"'";
263 }
264
265 private String getInput(Date date) {
266 if (date == null) {
267 return "NULL";
268 }
269 SimpleDateFormat timestamp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
270
271 return "TO_TIMESTAMP('" + timestamp.format(date) + "', 'YYYY-MM-DD HH24:MI:SS.FF')";
272 }
273
274 private int getInput(boolean value) {
275 return (value) ? 1 : 0;
276 }
277
278 private String getUUID() {
279 return java.util.UUID.randomUUID().toString();
280 }
281 }