gerrit.onap Code Review - portal/sdk.git/blob - ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/base/IdNameSql.java

1 /*

2 * ============LICENSE_START==========================================

3 * ONAP Portal SDK

4 * ===================================================================

6 * ===================================================================

7 *

8 * Unless otherwise specified, all software contained herein is licensed

9 * under the Apache License, Version 2.0 (the "License");

10 * you may not use this software except in compliance with the License.

11 * You may obtain a copy of the License at

12 *

13 * http://www.apache.org/licenses/LICENSE-2.0

14 *

15 * Unless required by applicable law or agreed to in writing, software

16 * distributed under the License is distributed on an "AS IS" BASIS,

17 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

18 * See the License for the specific language governing permissions and

19 * limitations under the License.

20 *

21 * Unless otherwise specified, all documentation contained herein is licensed

22 * under the Creative Commons License, Attribution 4.0 Intl. (the "License");

23 * you may not use this documentation except in compliance with the License.

24 * You may obtain a copy of the License at

25 *

26 * https://creativecommons.org/licenses/by/4.0/

27 *

28 * Unless required by applicable law or agreed to in writing, documentation

29 * distributed under the License is distributed on an "AS IS" BASIS,

30 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

31 * See the License for the specific language governing permissions and

32 * limitations under the License.

33 *

34 * ============LICENSE_END============================================

35 *

36 *

37 */

38 /* ===========================================================================================

39 * This class is part of <I>RAPTOR (Rapid Application Programming Tool for OLAP Reporting)</I>

40 * Raptor : This tool is used to generate different kinds of reports with lot of utilities

41 * ===========================================================================================

42 *

43 * -------------------------------------------------------------------------------------------

44 * IdNameSql.java - This class is used to generate form field items when sql is provided.

45 * -------------------------------------------------------------------------------------------

46 *

47 * Created By : Stan Pishamanov

48 * Modified By: Sundar Ramalingam

49 *

50 * Changes

51 * -------

52 * 08-Jun-2009 : Version 8.3 (RS); Rownum references is avoided for reports connnecting to Daytona

53 * Database.

54 *

55 */

56 package org.onap.portalsdk.analytics.model.base;

57

58 import java.util.HashMap;

59

60 import org.onap.portalsdk.analytics.error.RaptorException;

61 import org.onap.portalsdk.analytics.system.ConnectionUtils;

62 import org.onap.portalsdk.analytics.system.Globals;

63 import org.onap.portalsdk.analytics.util.AppConstants;

64 import org.onap.portalsdk.analytics.util.DataSet;

65 import org.onap.portalsdk.analytics.util.Utils;

66 import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;

67

68 public class IdNameSql extends IdNameList {

69

70 private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(IdNameSql.class);

71

72 protected int dataSize = -1;

73

74 protected int dataSizeUsedInPopup = -1;

75

76 private String sql = null;

77

78 private String oldSql = null;

79

80 private String defaultSQL = null;

81

82 private String sqlNoOrderBy = null;

83

84 public IdNameSql(int pageNo, String sql, String defaultSQL) {

85 this(sql,defaultSQL);

86 this.pageNo = pageNo;

87 } // IdNameSql

88

89 public IdNameSql(String sql) {

90 this();

91 setSql(sql);

92 } // IdNameSql

93

94 public IdNameSql(String sql, String defaultSQL) {

95 this();

96 setDefaultSQL(defaultSQL);

97 setSql(sql);

98 } // IdNameSql

99

100 protected IdNameSql() {

101 super();

102 } // IdNameSql

103

104 public boolean canUseSearchString() {

105 return true;

106 }

107

108 public String getSql() {

109 return sql;

110 }

111

112 public String getOldSql() {

113 return oldSql;

114 }

115

116 public String getBaseSQL() {

117 return "SELECT id FROM (" + sql + ") xid";

118 }

119

120 public String getBaseWholeSQL() {

121 return "SELECT id, name FROM (" + sql + ") xid";

122 }

123

124 public String getBaseWholeReadonlySQL() {

125 return "SELECT id, name, ff_readonly FROM (" + sql + ") xid";

126 }

127

128 public String getBaseSQLForPDFExcel(boolean multiParam) {

129 if(!multiParam)

130 return "SELECT id, name FROM (" + sql + ") xid where id = '[VALUE]'";

131 else

132 return "SELECT id, name FROM (" + sql + ") xid where id in [VALUE]";

133

134 }

135

136 // public String getSqlNoOrderBy() { return sqlNoOrderBy; }

137

138 protected void setSql(String sql) {

139 this.sql = sql;

140 }

141

142 public void setOldSql(String oldSql) {

143 this.oldSql = oldSql;

144 }

145

146 protected void setSqlNoOrderBy(String sql) {

147 this.sqlNoOrderBy = sql;

148 }

149

150 public int getDataSize() {

151 return dataSize;

152 } // getDataSize

153

154 public int getDataSizeUsedinPopup() {

155 return dataSizeUsedInPopup;

156 } // getDataSizeUsedinPopup

157

158 public void setDataSizeUsedinPopup(int dataSizePop) {

159 this.dataSizeUsedInPopup = dataSizePop;

160 } // getDataSizeUsedinPopup

161

162 public void clearData() {

163 removeAllElements();

164 } // clearData

165

166 /* public void loadData(String pageNo, String searchString, String dbInfo, String userId) throws RaptorException {

167 // setSql(searchString);

168 loadUserData(pageNo, searchString, dbInfo,userId);

169 } // loadData

170 */

171

172 public void loadUserData(String pageNo, String searchString, String dbInfo,String userId) throws RaptorException {

173 int iPageNo = 0;

174

175 if (pageNo != null)

176 try {

177 iPageNo = Integer.parseInt(pageNo);

178 } catch (NumberFormatException e) {

179 }

180

181 loadUserData(iPageNo, searchString, dbInfo,userId);

182 } // loadData

183

184 public void loadUserData(int pageNo, String searchString, String dbInfo, String userId) throws RaptorException {

185 if(userId!=null) {

186 String sql = Utils.replaceInString(getSql(), "[LOGGED_USERID]", userId);

187 //String defaultSQL = "";

188 if(defaultSQL!=null && (defaultSQL.trim().toLowerCase().startsWith("select")) ) {

189 defaultSQL = Utils.replaceInString(getDefaultSQL(), "[LOGGED_USERID]", userId);

190 setDefaultSQL(defaultSQL);

191 }

192 setSql(sql);

193

194 }

195 loadData(searchString,pageNo, dbInfo);

196 }

197

198 public void loadData(String searchString, int pageNo, String dbInfo) throws RaptorException {

199

200 //boolean dataAlreadyLoaded = (this.pageNo == pageNo);

201

202 //if (dataAlreadyLoaded)

203 // return;

204

205 this.pageNo = pageNo;

206

207 performLoadData(searchString, dbInfo);

208 } // loadData

209

210 protected void performLoadData(String searchString, String dbInfo) throws RaptorException {

211 long currentTime = System.currentTimeMillis();

212 int startRow = 0;

213 int endRow = dataSize;

214 String readOnlyInSql = "ff_readonly";

215 String dbType = Globals.getDBType();

216 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {

217 try {

218 org.onap.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.onap.portalsdk.analytics.util.RemDbInfo();

219 dbType = remDbInfo.getDBType(dbInfo);

220 } catch (Exception ex) {

221 throw new RaptorException(ex);

222 }

223 }

224 if (pageNo >= 0) {

225 startRow = pageNo * pageSize;

226 endRow = startRow + pageSize;

227 } // if

228 DataSet ds = null;

229 DataSet dsDefault = null;

230 StringBuffer query = new StringBuffer("");

231 StringBuffer queryPop = new StringBuffer("");

232 String sql = getSql();

233 boolean avail_ReadOnly = (sql.toLowerCase().indexOf(readOnlyInSql)!=-1);

234

235 if (dbType.equals("DAYTONA") && getSql().trim().toUpperCase().startsWith("SELECT")) {

236 query.append(getSql());

237 } else {

238 if(avail_ReadOnly) // need to add readonlyinsql

239 if(!(Globals.isMySQL() && dbType.equals(AppConstants.MYSQL)))

240 query.append("SELECT rownum, id, name, " + readOnlyInSql +" FROM ("+ Globals.getReportSqlForFormfield() +", " + readOnlyInSql + " FROM (" + sql

241 + ") x "+ Globals.getReportSqlForFormfieldSuffix());

242 else

243 query.append("SELECT id, name, " + readOnlyInSql +" FROM ("+ Globals.getReportSqlForFormfield() +", " + readOnlyInSql + " FROM (" + sql

244 + ") x "+ Globals.getReportSqlForFormfieldSuffix());

245 else

246 query.append(Globals.getReportSqlForFormfieldPrefix()+ Globals.getReportSqlForFormfield() +" FROM (" + sql

247 + ") x " + Globals.getReportSqlForFormfieldSuffix());

248 if(pageNo!= -2 && (dbType.equals(AppConstants.ORACLE)) ) {

249 query.append(" WHERE rownum <= " + ((dataSize < 0) ? (endRow + 1) : endRow));

250 } else if(pageNo!=2 && (dbType.equals(AppConstants.POSTGRESQL))) {

251 query.append(" LIMIT " + ((dataSize < 0) ? (endRow + 1) : endRow));

252

253 } else if(pageNo!=2 && (dbType.equals(AppConstants.MYSQL))) {

254 query.append(" LIMIT " + startRow); //((dataSize < 0) ? (endRow + 1) : endRow)

255

256 }

257 if(searchString!=null && searchString.length()>0 && !searchString.equals("%")) {

258 if(pageNo == -2){

259 query.append(" WHERE ");

260 }

261 else query.append(" and ");

262 query.append("name like '"+ searchString +"'");

263 }

264 if(dbType.equals(AppConstants.POSTGRESQL)) {

265 query.append(") xx OFFSET " + startRow);

266 } else if(dbType.equals(AppConstants.MYSQL)) {

267 query.append(" ," + ((dataSize < 0) ? (endRow + 1) : endRow) +") xx");

268 } else if(dbType.equals(AppConstants.ORACLE))

269 query.append(") xx WHERE rownum>" + startRow);

270 }

271 String defaultQuery ="";

272 boolean readOnly = true;

273 ds = ConnectionUtils.getDataSet(query.toString(), dbInfo);

274

275 // if ( (dbInfo!=null) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {

276 // Globals.getRDbUtils().setDBPrefix(dbInfo);

277 // ds = RemDbUtils.executeQuery(query);

278 // }

279 // else

280 // ds = DbUtils.executeQuery(query);

281 clearData();

282 if (dbType.equals("DAYTONA") && (getDefaultSQL()!=null && getDefaultSQL().trim().toUpperCase().startsWith("SELECT"))) {

283 defaultQuery = getDefaultSQL();

284 } else if (getDefaultSQL()!=null && getDefaultSQL().length()>10 && getDefaultSQL().substring(0,10).toLowerCase().startsWith("select")) {

285 defaultQuery = Globals.getReportSqlForFormfieldPrefix()+ Globals.getReportSqlForFormfield() +" FROM (" + getDefaultSQL()

286 + ") x "

287 + ") xx ";

288 logger.debug(EELFLoggerDelegate.debugLogger, ("Default Query " +defaultQuery));

289 }

290 HashMap defaultMap = new HashMap();

291 if(!isNull(defaultQuery)) {

292 dsDefault = ConnectionUtils.getDataSet(defaultQuery, dbInfo);

293 if(dsDefault!=null && dsDefault.getRowCount()>0) {

294 for (int i = 0; i < dsDefault.getRowCount(); i++) {

295 //addValue(dsDefault.getString(i, 0), dsDefault.getString(i, 1), true);

296 defaultMap.put(dsDefault.getString(i, "id"), dsDefault.getString(i, "name"));

297 }

298 }

299 }

300

301 for (int i = 0; i < ((pageNo!=-2)?Math.min(ds.getRowCount(), pageSize):ds.getRowCount()); i++) {

302 //if(getCount()==0)

303 // addValue(ds.getString(i, 0), ds.getString(i, 1));

304 if(i==0 && avail_ReadOnly)

305 readOnly = ds.getString(i, "ff_readonly").toUpperCase().startsWith("Y")||ds.getString(i, "ff_readonly").toUpperCase().startsWith("T");

306 if(getCount()>=0) {//&& !((IdNameValue)getValue(0)).getId().equals(ds.getString(i, 0)))

307 if(defaultMap.get(ds.getString(i, "id")) == null)

308 if(avail_ReadOnly)

309 addValue(ds.getString(i, "id"), ds.getString(i, "name"), false, readOnly);

310 else

311 addValue(ds.getString(i, "id"), ds.getString(i, "name"), false);

312 else

313 if(avail_ReadOnly)

314 addValue(ds.getString(i, "id"), ds.getString(i, "name"), true, readOnly);

315 else

316 addValue(ds.getString(i, "id"), ds.getString(i, "name"), true);

317 }

318 }

319

320 if (!(dbType.equals("DAYTONA"))) {

321 if (ds.getRowCount() <= pageSize) {

322 if(dsDefault!=null && dsDefault.getRowCount()>0)

323 dataSize = ds.getRowCount()+1;

324 else

325 dataSize = ds.getRowCount();

326

327 //System.out.println("IDNAME SQL COUNT");*/

328 if(searchString!=null && searchString.length()>0 && !searchString.equals("%")) {

329 queryPop = new StringBuffer("");

330 queryPop.append("SELECT count(*) num_rows FROM ("+ Globals.getReportSqlForFormfield() +", name FROM (" + sql

331 + ") x ");

332 if(searchString!=null && searchString.length()>0 && !searchString.equals("%"))

333 queryPop.append(" where name like '"+ searchString +"'");

334 queryPop.append(") xx ");

335

336 ds = ConnectionUtils.getDataSet(queryPop.toString(), dbInfo);

337 try {

338 dataSizeUsedInPopup = Integer.parseInt(ds.getString(0, 0));

339 } catch (NumberFormatException e) {

340 }

341 } else if(dataSizeUsedInPopup == -3) {

342 queryPop = new StringBuffer("");

343 //System.out.println("IDNAME SQL COUNT");

344 //queryPop.append("SELECT count(*) num_rows FROM ("+query.toString()+") x");

345 queryPop.append("SELECT count(*) num_rows FROM ("+ Globals.getReportSqlForFormfield() +", name FROM (" + sql

346 + ") x ");

347 queryPop.append(") xx ");

348

349 ds = ConnectionUtils.getDataSet(queryPop.toString(), dbInfo);

350 // if ( (dbInfo!=null) &&

351 // (!dbInfo.equals(AppConstants.DB_LOCAL))) {

352 // Globals.getRDbUtils().setDBPrefix(dbInfo);

353 // ds = RemDbUtils.executeQuery(query);

354 // }

355 // else

356 // ds = DbUtils.executeQuery(query);

357 //

358 try {

359 dataSizeUsedInPopup = Integer.parseInt(ds.getString(0, 0));

360 } catch (NumberFormatException e) {

361 }

362

363 }

364

365 } else {

366 //pageNo = 0;

367 if(pageNo!= -2) {

368 queryPop = new StringBuffer("");

369 //System.out.println("IDNAME SQL COUNT");

370 //queryPop.append("SELECT count(*) num_rows FROM ("+query.toString()+") x");

371 queryPop.append("SELECT count(*) num_rows FROM ("+ Globals.getReportSqlForFormfield() +" FROM (" + sql

372 + ") x ");

373 queryPop.append(") xx ");

374

375 ds = ConnectionUtils.getDataSet(queryPop.toString(), dbInfo);

376 // if ( (dbInfo!=null) &&

377 // (!dbInfo.equals(AppConstants.DB_LOCAL))) {

378 // Globals.getRDbUtils().setDBPrefix(dbInfo);

379 // ds = RemDbUtils.executeQuery(query);

380 // }

381 // else

382 // ds = DbUtils.executeQuery(query);

383 //

384 try {

385 dataSize = Integer.parseInt(ds.getString(0, 0));

386 dataSizeUsedInPopup = Integer.parseInt(ds.getString(0, 0));

387 } catch (NumberFormatException e) {

388 }

389 }

390 } // else

391 } // dataSize < 0

392 long totalTime = System.currentTimeMillis() - currentTime;

393 logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] ------->Time Taken to the above formfield Query (+ count Query if any) --- " + totalTime));

394 } // performLoadData

395

396

397 public String getDefaultSQL() {

398

399 return defaultSQL;

400 }

401

402

403 public void setDefaultSQL(String defaultSQL) {

404

405 this.defaultSQL = defaultSQL;

406 }

407

408 public void setSQL(String sql_)

409 {

410 this.sql = sql_;

411 }

412

413 public static boolean isNull(String a) {

414 if ((a == null) || (a.length() == 0) || a.equalsIgnoreCase("null"))

415 return true;

416 else

417 return false;

418 }

419 } // IdNameSql