|
1 package smile.stored;
2
3 import java.util.*;
4 import java.sql.*;
5
6
16 public class s_insertMappingCode2 extends StoredProcedure implements ProcedureInterface {
17
18 String  mappingCode;
20
21 String subMapOf;
22
23 String pubName;
24
25 String section;
26
27 String sectionName;
28
29 String sectionDesc;
30
31 String sectionType;
32
33 String image;
34
35 String sigDir;
36
37 String keywords;
38
39 String include;
40
41 String promo;
42
43 String rank;
44
45 String url;
46
47 String displayOnIndex;
48
49 String sectionOld;
50
51 String sectionNameOld;
52
53 String sectionDescOld;
54
55 String userID;
56
57
60 public s_insertMappingCode2() {
61 }
62
63
71 public void init(HashMap initData, Connection con) {
72 super.init(initData, con);
74 mappingCode = utils.getString(data, "MAPPINGCODE", "");
76 if (mappingCode.trim().length() == 0) {
77 mappingCode = "0";
78 }
79 subMapOf = utils.getString(data, "SUBMAPOF", "");
80 if (subMapOf.trim().length() == 0) {
81 subMapOf = "0";
82 }
83 pubName = utils.getString(data, "PUBNAME", "");
84 section = utils.getString(data, "SECTION", "");
85 sectionName = utils.getString(data, "SECTIONNAME", "");
86 sectionDesc = utils.getString(data, "SECTIONDESC", "");
87 sectionType = utils.getString(data, "SECTIONTYPE", "");
88 image = utils.getString(data, "IMAGE", "");
89 sigDir = utils.getString(data, "SIGDIR", "");
90 keywords = utils.getString(data, "KEYWORDS", "");
91 include = utils.getString(data, "INCLUDE", "");
92 promo = utils.getString(data, "PROMO", "");
93 rank = utils.getString(data, "RANK", "");
94 url = utils.getString(data, "URL", "");
95 displayOnIndex = (String ) data.get("DISPLAYONINDEX");
96 userID = utils.getString(data, "USERID", "0");
97 }
98
99
105 public void checkParams() throws SQLException {
106 if (mappingCode.equals("") || subMapOf.equals("") || pubName.equals("") || section.equals("") || sectionName.equals("") || sectionDesc.equals("")
107 || sectionType.equals("")) {
108 throw new SQLException("ERROR: REQUIRED FIELDS WERE NOT SENT.");
109 }
110
111 execStatementToRS("SELECT * FROM tblpublications WHERE pubName = '" + pubName + "'");
112 if (!psResult.next()) {
113 throw new SQLException("ERROR: PUBLICATION DOES NOT EXIST.");
114 }
115 close();
116
117
122 }
123
124
130 public void checkAction() throws SQLException {
131 execStatementToRS("SELECT * FROM tblsections WHERE mappingCode = '" + mappingCode + "'");
132 if (Integer.parseInt(mappingCode) > 0 && psResult.next()) {
133 action = "update";
134 } else if (Integer.parseInt(mappingCode) == 0) {
135 action = "insert";
136 } else {
137 throw new SQLException("ERROR: MAPPINGCODE DOES NOT EXIST.");
138 }
139 close();
140 }
141
142
148 public void executeAction() throws SQLException {
149 if (action.equals("update")) {
150 StringBuffer v_sectionOld = new StringBuffer ();
151 v_sectionOld.append("SELECT section from tblsections WHERE mappingCode = '" + mappingCode + "'");
152 try {
153 sectionOld = execStatementToValue(v_sectionOld.toString());
154 } catch (SQLException e) {
155 sectionOld = section;
156 }
157
158 StringBuffer v_sectionNameOld = new StringBuffer ();
159 v_sectionNameOld.append("SELECT sectionName from tblsections WHERE mappingCode = '" + mappingCode + "'");
160 try {
161 sectionNameOld = execStatementToValue(v_sectionNameOld.toString());
162 } catch (SQLException e) {
163 sectionNameOld = sectionName;
164 }
165
166 StringBuffer v_sectionDescOld = new StringBuffer ();
167 v_sectionDescOld.append("SELECT sectionDesc from tblsections WHERE mappingCode = '" + mappingCode + "'");
168 try {
169 sectionDescOld = execStatementToValue(v_sectionDescOld.toString());
170 } catch (SQLException e) {
171 sectionDescOld = sectionDesc;
172 }
173
174 StringBuffer v_sb = new StringBuffer ();
175 v_sb.append("UPDATE tblsections").append(" SET section = '" + section + "',").append(" sectionName = '" + sectionName + "',").append(
176 " sectionDesc = '" + sectionDesc + "',").append(" subMapOf = '" + subMapOf + "',").append(" image = '" + image + "',").append(
177 " sigDir = '" + sigDir + "',").append(" sectionType = '" + sectionType + "',").append(" keywords = '" + keywords + "',").append(
178 " include = '" + include + "',").append(" promo = '" + promo + "',").append(" rank = '" + rank + "',").append(" url = '" + url + "',")
179 .append(" displayOnIndex = '" + displayOnIndex + "',").append(" sectionUpdateDate = '" + sdf.format(new java.util.Date ()) + "'").append(
180 " WHERE mappingCode = '" + mappingCode + "'");
181 execStatement(v_sb.toString());
182
183 if (!(section.equals(sectionOld))) {
185 StringBuffer v_sb2 = new StringBuffer ();
186 v_sb2.append("UPDATE tblarticles").append(" SET section = '" + section + "' ").append(" WHERE section = '" + sectionOld + "' ").append(
187 " AND pubName='" + pubName + "'");
188 execStatement(v_sb2.toString());
189
190 StringBuffer v_sb5 = new StringBuffer ();
191 v_sb5.append("UPDATE tblarticleorder").append(" SET section = '" + section + "' ").append(" WHERE mappingCode = '" + mappingCode + "' ")
192 .append(" AND pubName='" + pubName + "'");
193 execStatement(v_sb5.toString());
194
195 }
196
197 if (!(sectionName.equals(sectionNameOld))) {
199 StringBuffer v_sb3 = new StringBuffer ();
200 v_sb3.append("UPDATE tblarticlepreload").append(" SET virtualFolder = '" + pubName + '/' + sectionName + "' ,").append(
201 " fullLink= CONCAT(left(fullLink,instr(fullLink,'" + sectionNameOld + "')-1),'" + sectionName
202 + "', right(fullLink,length(fullLink)-instr(fullLink,'" + sectionNameOld + "')-length('" + sectionNameOld + "')+1)) ").append(
203 " WHERE fullLink LIKE '%/" + sectionNameOld + "/%' ").append(" AND fullLink LIKE '%/" + pubName + "/%' ");
204 execStatement(v_sb3.toString());
205
206 StringBuffer v_sb4 = new StringBuffer ();
207 v_sb4.append("UPDATE tblsections ").append(
208 " SET sectionName = CONCAT(left(sectionName,instr(sectionName,'" + sectionNameOld + "')-1),'" + sectionName
209 + "', right(sectionName,length(sectionName)-instr(sectionName,'" + sectionNameOld + "')-length('" + sectionNameOld + "')+1)) ")
210 .append(" WHERE sectionName LIKE '%/" + sectionNameOld + "/%' ").append(" OR sectionName LIKE '" + sectionNameOld + "/%' ").append(
211 " AND pubName='" + pubName + "'");
212 execStatement(v_sb4.toString());
213 }
214
215 if (!(sectionDesc.equals(sectionDescOld))) {
217 StringBuffer v_sb4 = new StringBuffer ();
218 v_sb4.append("UPDATE tblarticlepreload").append(" SET sectionDesc = '" + sectionDesc + "' ").append(
219 " WHERE sectionDesc = '" + sectionDescOld + "'");
220 execStatement(v_sb4.toString());
221 }
222
223 } else if (action.equals("insert")) {
224 StringBuffer v_sb = new StringBuffer ();
225 v_sb
226 .append("INSERT INTO tblsections ")
227 .append(
228 "(pubName, section, sectionDesc, sectionName, image, orderFile, sigDir, subMapOf, sectionType, keywords, include, promo, rank, url, displayOnIndex, sectionUpdateDate)")
229 .append(
230 " VALUES ('" + pubName + "', '" + section + "', '" + sectionDesc + "', '" + sectionName + "', '" + image + "', '', '" + sigDir
231 + "', '" + subMapOf + "', '" + sectionType + "', '" + keywords + "', '" + include + "', '" + promo + "', '" + rank + "', '"
232 + url + "', '" + displayOnIndex + "','" + sdf.format(new java.util.Date ()) + "')");
233 execStatement(v_sb.toString());
234
235 StringBuffer v_ins = new StringBuffer ();
237 v_ins.append("INSERT INTO tblpermusersection (mappingCode,userID,Manager) ").append(
238 " SELECT mappingCode, '" + userID + "' as userID, '1' as Director FROM tblsections").append(
239 " WHERE pubname='" + pubName + "' and section='" + section + "' ");
240 execStatement(v_ins.toString());
241
242 StringBuffer v_ins2 = new StringBuffer ();
245 v_ins2.append("INSERT INTO tblpermusersection (mappingCode,userID,Manager) ").append("select distinct S.mappingCode, PUG1.userId, 0 as Manager ")
246 .append("from tblpermusergroup AS PUG1, tblpermusergroup AS PUG2, tblsections as S ").append("where PUG2.userID='" + userID + "' ").append(
247 "and PUG1.groupID = PUG2.groupID ").append("and PUG1.userID<>'" + userID + "' ").append(
248 "and pubname='" + pubName + "' and section='" + section + "' ");
249 execStatement(v_ins2.toString());
250 }
251 execStatementToRS(queryResultOk);
252 }
253 }
254 |
Popular Tags |
Java API By Example, From Geeks To Geeks.