1 package fr.ifremer.quadrige3.synchro.intercept.data;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 import com.google.common.base.Joiner;
27 import com.google.common.collect.ImmutableList;
28 import com.google.common.collect.ImmutableSet;
29 import com.google.common.collect.Lists;
30 import com.google.common.collect.Maps;
31 import com.google.common.eventbus.Subscribe;
32 import fr.ifremer.common.synchro.dao.Daos;
33 import fr.ifremer.common.synchro.dao.SynchroTableDao;
34 import fr.ifremer.common.synchro.intercept.SynchroInterceptorBase;
35 import fr.ifremer.common.synchro.intercept.SynchroOperationRepository;
36 import fr.ifremer.common.synchro.meta.SynchroTableMetadata;
37 import fr.ifremer.common.synchro.meta.event.CreateQueryEvent;
38 import fr.ifremer.common.synchro.meta.event.LoadTableEvent;
39 import fr.ifremer.common.synchro.query.SynchroQueryBuilder;
40 import fr.ifremer.common.synchro.query.SynchroQueryName;
41 import fr.ifremer.common.synchro.query.SynchroQueryOperator;
42 import fr.ifremer.quadrige3.core.config.QuadrigeConfiguration;
43 import fr.ifremer.quadrige3.core.dao.administration.user.PrivilegeCode;
44 import fr.ifremer.quadrige3.core.dao.referential.ObjectTypeCode;
45 import fr.ifremer.quadrige3.core.dao.system.synchronization.SynchronizationStatus;
46 import fr.ifremer.quadrige3.synchro.meta.DatabaseColumns;
47 import fr.ifremer.quadrige3.synchro.meta.data.DataSynchroTables;
48 import fr.ifremer.quadrige3.synchro.service.SynchroDirection;
49 import fr.ifremer.quadrige3.synchro.service.data.DataSynchroDatabaseConfiguration;
50 import fr.ifremer.quadrige3.synchro.vo.SynchroDateOperatorVO;
51 import org.apache.commons.collections4.CollectionUtils;
52 import org.apache.commons.collections4.MapUtils;
53 import org.apache.commons.lang3.StringUtils;
54 import org.hibernate.LockMode;
55
56 import java.io.IOException;
57 import java.sql.PreparedStatement;
58 import java.sql.ResultSet;
59 import java.sql.SQLException;
60 import java.util.*;
61
62
63
64
65
66
67
68 public class SurveyInterceptor extends AbstractDataInterceptor {
69
70
71 private static final String UNIQUE_CONSTRAINT_NAME = "NATURAL_IDC";
72
73 private static final List<String> UNIQUE_CONSTRAINT_COLUMNS = ImmutableList.of(
74 DatabaseColumns.SURVEY_DT.name(),
75 DatabaseColumns.SURVEY_TIME.name(),
76 DatabaseColumns.MON_LOC_ID.name());
77
78 private int columnSurveyIdIndex = -1;
79 private boolean enableIntegrityConstraints = true;
80 private boolean forceDuplication = false;
81 private PreparedStatement selectTargetProgCdsStatement;
82 private PreparedStatement selectSourceProgCdsStatement;
83
84
85
86
87
88
89 public SurveyInterceptor() {
90 super(DataSynchroTables.SURVEY.name());
91 setEnableOnWrite(true);
92 }
93
94
95 @Override
96 public void init(DataSynchroDatabaseConfiguration config) {
97 super.init(config);
98 enableIntegrityConstraints = config.getDirection() == SynchroDirection.EXPORT_TEMP2SERVER
99 || config.getDirection() == SynchroDirection.IMPORT_TEMP2LOCAL;
100 forceDuplication = config.isForceDuplication();
101
102 setEnableCheckUniqueConstraint(config.getDirection() == SynchroDirection.EXPORT_TEMP2SERVER
103 || config.getDirection() == SynchroDirection.IMPORT_FILE2LOCAL);
104 }
105
106
107 @Override
108 public SynchroInterceptorBase clone() {
109 SurveyInterceptor newBean = (SurveyInterceptor) super.clone();
110 newBean.columnSurveyIdIndex = this.columnSurveyIdIndex;
111 newBean.enableIntegrityConstraints = this.enableIntegrityConstraints;
112 newBean.forceDuplication = this.forceDuplication;
113 newBean.selectSourceProgCdsStatement = selectSourceProgCdsStatement;
114 return newBean;
115 }
116
117
118 @Override
119 protected void doClose() throws IOException {
120 super.doClose();
121
122
123 Daos.closeSilently(selectTargetProgCdsStatement);
124 selectTargetProgCdsStatement = null;
125
126 Daos.closeSilently(selectSourceProgCdsStatement);
127 selectSourceProgCdsStatement = null;
128
129 }
130
131
132
133
134
135
136
137
138
139 @Subscribe
140 public void handleLoadTable(LoadTableEvent e) {
141 SynchroTableMetadata table = e.table;
142 SynchroDirection direction = getConfig().getDirection();
143
144 columnSurveyIdIndex = e.table.getSelectColumnIndex(DatabaseColumns.SURVEY_ID.name());
145
146 table.setRoot(true);
147
148
149 if (direction == SynchroDirection.EXPORT_TEMP2SERVER) {
150
151
152
153
154
155
156 table.setLockOnUpdate(LockMode.UPGRADE_NOWAIT);
157 }
158
159
160 if (direction == SynchroDirection.IMPORT_FILE2LOCAL) {
161
162
163 table.addUniqueConstraint(UNIQUE_CONSTRAINT_NAME, UNIQUE_CONSTRAINT_COLUMNS,
164
165 forceDuplication ? SynchroTableMetadata.DuplicateKeyStrategy.DUPLICATE : SynchroTableMetadata.DuplicateKeyStrategy.REJECT);
166 }
167 }
168
169
170
171
172
173
174
175
176
177 @Subscribe
178 public void handleQuery(CreateQueryEvent e) {
179 SynchroDirection direction = getConfig().getDirection();
180
181 switch (e.queryName) {
182
183 case count:
184 case countFromUpdateDate:
185 case select:
186 case selectFromUpdateDate:
187 case selectMaxUpdateDate:
188 if (direction == SynchroDirection.IMPORT_SERVER2TEMP
189 || direction == SynchroDirection.IMPORT_NO_TEMP) {
190
191 e.sql = addRestrictionOnImport(e.source, e.queryName, e.sql);
192
193
194 e.sql = addDebugRestriction(e.sql, DatabaseColumns.SURVEY_ID.name());
195 }
196
197 else if (direction == SynchroDirection.IMPORT_TEMP2LOCAL) {
198
199 e.sql = addRestrictionOnImportFromTempDB(e.source, e.queryName, e.sql);
200
201
202 e.sql = addDebugRestriction(e.sql, DatabaseColumns.SURVEY_ID.name());
203 }
204
205 else if (direction == SynchroDirection.EXPORT_LOCAL2TEMP) {
206
207 e.sql = addRestrictionOnExport(e.source, e.queryName, e.sql);
208
209
210 e.sql = addDebugRestriction(e.sql, getConfig().getColumnRemoteId());
211 }
212
213 else if (direction == SynchroDirection.IMPORT_FILE2LOCAL) {
214
215 e.sql = addRestrictionOnImportFromFile(e.source, e.queryName, e.sql);
216
217
218 e.sql = addDebugRestriction(e.sql, getConfig().getColumnRemoteId());
219 }
220
221 else if (direction == SynchroDirection.EXPORT_LOCAL2FILE) {
222
223 e.sql = addRestrictionOnExportToFile(e.source, e.queryName, e.sql);
224
225
226 e.sql = addDebugRestriction(e.sql, getConfig().getColumnRemoteId());
227 }
228 break;
229
230 default:
231 break;
232 }
233 }
234
235
236 @Override
237 protected void doOnWrite(Object[] data,
238 List<Object> pk,
239 SynchroTableDao sourceDao,
240 SynchroTableDao targetDao,
241 SynchroOperationRepository buffer,
242 boolean insert) {
243
244
245 if (buffer == null) {
246 return;
247 }
248
249 Object surveyId = data[columnSurveyIdIndex];
250
251 if (surveyId != null) {
252 buffer.addChildToUpdateFromManyColumns(DataSynchroTables.QUALIFICATION_HISTORY.name(),
253 ImmutableSet.of(DatabaseColumns.OBJECT_TYPE_CD.name(), DatabaseColumns.QUAL_HIST_ELEMENT_ID.name()),
254 Lists.newArrayList(ObjectTypeCode.SURVEY.value(), Long.parseLong(surveyId.toString())));
255 buffer.addChildToUpdateFromManyColumns(DataSynchroTables.VALIDATION_HISTORY.name(),
256 ImmutableSet.of(DatabaseColumns.OBJECT_TYPE_CD.name(), DatabaseColumns.VALID_HIST_ELEMENT_ID.name()),
257 Lists.newArrayList(ObjectTypeCode.SURVEY.value(), Long.parseLong(surveyId.toString())));
258 }
259 }
260
261 @Override
262 public void doOnCheckUniqueConstraint(Object[] data,
263 List<Object> pk,
264 SynchroTableDao sourceDao,
265 SynchroTableDao targetDao,
266 Map<String, List<Object>> duplicatedKeys) throws SQLException {
267
268
269
270
271
272
273 if (MapUtils.isEmpty(duplicatedKeys) || !duplicatedKeys.containsKey(UNIQUE_CONSTRAINT_NAME)) {
274 return;
275 }
276
277
278 List<Object> potentialDuplicateKey = duplicatedKeys.remove(UNIQUE_CONSTRAINT_NAME);
279
280
281 Object remoteIdToCheck = potentialDuplicateKey.iterator().next();
282
283
284
285
286
287
288
289
290
291 if (selectTargetProgCdsStatement == null || selectTargetProgCdsStatement.isClosed()) {
292 selectTargetProgCdsStatement = targetDao.getPreparedStatement(createSelectProgCdsBySimilarSurveySql());
293 }
294 selectTargetProgCdsStatement.setObject(1, remoteIdToCheck);
295 ResultSet rs = selectTargetProgCdsStatement.executeQuery();
296 Map<String, Integer> targetSurveyIdsByProgCd = Maps.newHashMap();
297 while (rs.next()) {
298 targetSurveyIdsByProgCd.put(rs.getString(1), Integer.parseInt(rs.getObject(2).toString()));
299 }
300 rs.close();
301
302
303 if (selectSourceProgCdsStatement == null || selectSourceProgCdsStatement.isClosed()) {
304 selectSourceProgCdsStatement = sourceDao.getPreparedStatement(createSelectProgCdsBySurveyIdSql());
305 }
306 Object localId = Integer.parseInt(pk.iterator().next().toString());
307 selectSourceProgCdsStatement.setObject(1, localId);
308 rs = selectSourceProgCdsStatement.executeQuery();
309 List<String> sourceProgCds = Lists.newArrayList();
310 while (rs.next()) {
311 sourceProgCds.add(rs.getString(1));
312 }
313 rs.close();
314
315 Collection<String> sameProgCds = CollectionUtils.intersection(targetSurveyIdsByProgCd.keySet(), sourceProgCds);
316 if (CollectionUtils.isNotEmpty(sameProgCds)) {
317
318 Integer duplicateKey = targetSurveyIdsByProgCd.get(sameProgCds.iterator().next());
319 duplicatedKeys.put(UNIQUE_CONSTRAINT_NAME, ImmutableList.of(duplicateKey));
320 }
321 }
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338 protected String addRestrictionOnImport(SynchroTableMetadata table, SynchroQueryName queryName, String sql) {
339
340 DataSynchroDatabaseConfiguration databaseConfiguration = getConfig();
341 boolean enableUpdateDateFilter = SynchroQueryName.withUpdateDate(queryName);
342
343 SynchroQueryBuilder queryBuilder = SynchroQueryBuilder.newBuilder(queryName, sql);
344
345
346
347 if (queryName == SynchroQueryName.count
348 || queryName == SynchroQueryName.countFromUpdateDate) {
349 queryBuilder.replaceColumn("count(*)", "count(distinct t.survey_id)");
350 } else {
351 queryBuilder.setColumnDistinct(true);
352
353 String updateDtColumn = DatabaseColumns.UPDATE_DT.name();
354 if (!queryBuilder.constainsColumn(updateDtColumn)
355 && queryBuilder.constainsColumn("t." + updateDtColumn)) {
356 updateDtColumn = "t." + updateDtColumn;
357 }
358
359 if (queryBuilder.constainsColumn(updateDtColumn)) {
360
361 queryBuilder.replaceColumn(updateDtColumn,
362 String.format("GREATEST(t.%s, (select max(%s) from sampling_operation where survey_id = t.survey_id)) AS %s",
363 DatabaseColumns.UPDATE_DT.name(),
364 DatabaseColumns.UPDATE_DT.name(),
365 DatabaseColumns.UPDATE_DT.name()));
366 }
367 }
368
369
370 String programJoinFilter = createProgramCodesFilter("INNER JOIN SURVEY_PROG sp ON sp.survey_id=t.survey_id AND sp.prog_cd IN (%s)");
371 if (StringUtils.isNotBlank(programJoinFilter)) {
372 queryBuilder.addJoin(programJoinFilter);
373 } else {
374 queryBuilder.addJoin("INNER JOIN SURVEY_PROG sp ON sp.survey_id=t.survey_id");
375 }
376
377
378
379 queryBuilder.addJoin("INNER JOIN QUSER u on u.QUSER_ID=:userId");
380 queryBuilder.addJoin("LEFT OUTER JOIN PROG_QUSER_PROG_PRIV up ON up.prog_cd=sp.prog_cd AND up.quser_id=u.QUSER_ID");
381 queryBuilder.addJoin("LEFT OUTER JOIN PROG_DEP_PROG_PRIV dp ON dp.PROG_CD=sp.prog_cd AND dp.DEP_ID=u.DEP_ID");
382
383
384 queryBuilder.addJoin("LEFT OUTER JOIN SAMPLING_OPERATION so ON so.survey_id=t.survey_id");
385
386
387 if (enableUpdateDateFilter) {
388
389 queryBuilder.addWhere(SynchroQueryOperator.OR, "t." + DatabaseColumns.UPDATE_DT.name(), ">", ":updateDate");
390
391 queryBuilder.addWhere(SynchroQueryOperator.OR, "so." + DatabaseColumns.UPDATE_DT.name(), ">", ":updateDate");
392 }
393
394
395 String pkFilter = createPkFilter(table);
396 if (StringUtils.isNotBlank(pkFilter)) {
397
398 queryBuilder.addWhere(SynchroQueryOperator.AND, pkFilter);
399 }
400
401
402 else if (databaseConfiguration.getDataStartDate() != null
403 && databaseConfiguration.getDataEndDate() != null) {
404 queryBuilder.addWhere(SynchroQueryOperator.AND, String.format(
405 "t.%s >= :startDate AND t.%s <= :endDate", DatabaseColumns.SURVEY_DT.name(), DatabaseColumns.SURVEY_DT.name()));
406 }
407
408
409 queryBuilder.addWhere(
410 SynchroQueryOperator.AND, String.format(
411 "(up.QUSER_ID is not null or dp.DEP_ID is not null or (select count(*) from QUSER_PRIVILEGE qp where qp.PRIVILEGE_CD='%s' and qp.QUSER_ID=:userId) = 1)",
412 PrivilegeCode.REFERENTIAL_ADMINISTRATOR.getValue()
413 )
414 );
415
416 return queryBuilder.build();
417 }
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432 protected String addRestrictionOnImportFromTempDB(SynchroTableMetadata table, SynchroQueryName queryName, String sql) {
433
434 String pkFilter = createPkFilter(table);
435 if (StringUtils.isBlank(pkFilter)) {
436 return sql;
437 }
438
439
440 SynchroQueryBuilder queryBuilder = SynchroQueryBuilder.newBuilder(queryName, sql);
441 queryBuilder.addWhere(SynchroQueryOperator.AND, pkFilter);
442 return queryBuilder.build();
443
444 }
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459 protected String addRestrictionOnImportFromFile(SynchroTableMetadata table, SynchroQueryName queryName, String sql) {
460
461 SynchroQueryBuilder queryBuilder = SynchroQueryBuilder.newBuilder(queryName, sql);
462
463
464
465 if (queryName == SynchroQueryName.count
466 || queryName == SynchroQueryName.countFromUpdateDate) {
467 queryBuilder.replaceColumn("count(*)", "count(distinct t.survey_id)");
468 } else {
469 queryBuilder.setColumnDistinct(true);
470 }
471
472
473 String pkFilter = createPkFilter(table);
474 if (StringUtils.isNotBlank(pkFilter)) {
475
476 queryBuilder.addWhere(SynchroQueryOperator.AND, pkFilter);
477 }
478
479 return queryBuilder.build();
480 }
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495 protected String addRestrictionOnExport(SynchroTableMetadata table, SynchroQueryName queryName, String sql) {
496
497 SynchroQueryBuilder queryBuilder = SynchroQueryBuilder.newBuilder(queryName, sql);
498
499
500
501 if (queryName == SynchroQueryName.count
502 || queryName == SynchroQueryName.countFromUpdateDate) {
503 queryBuilder.replaceColumn("count(*)", "count(distinct t.survey_id)");
504 } else {
505 queryBuilder.setColumnDistinct(true);
506 }
507
508
509 String programJoinFilter = createProgramCodesFilter("INNER JOIN SURVEY_PROG sp ON sp.survey_id=t.survey_id AND sp.prog_cd IN (%s)");
510 if (StringUtils.isNotBlank(programJoinFilter)) {
511 queryBuilder.addJoin(programJoinFilter);
512 } else {
513 queryBuilder.addJoin("INNER JOIN SURVEY_PROG sp ON sp.survey_id=t.survey_id");
514 }
515
516
517
518 queryBuilder.addJoin("INNER JOIN QUSER u on u.QUSER_ID=:userId");
519 queryBuilder.addJoin("LEFT OUTER JOIN PROG_QUSER_PROG_PRIV up ON up.prog_cd=sp.prog_cd AND up.quser_id=u.QUSER_ID");
520 queryBuilder.addJoin("LEFT OUTER JOIN PROG_DEP_PROG_PRIV dp ON dp.PROG_CD=sp.prog_cd AND dp.DEP_ID=u.DEP_ID");
521
522
523
524 String whereClause = String.format("(%s='%s') OR (%s='%s' AND %s IS NOT NULL)",
525 DatabaseColumns.SYNCHRONIZATION_STATUS.name(), SynchronizationStatus.READY_TO_SYNCHRONIZE.getValue(),
526 DatabaseColumns.SYNCHRONIZATION_STATUS.name(), SynchronizationStatus.SYNCHRONIZED_WITH_FILE.getValue(),
527 DatabaseColumns.SURVEY_VALID_DT);
528 queryBuilder.addWhere(SynchroQueryOperator.AND, whereClause);
529
530
531 queryBuilder.addWhere(
532 SynchroQueryOperator.AND, String.format(
533 "(up.QUSER_ID is not null or dp.DEP_ID is not null or (select count(*) from QUSER_PRIVILEGE qp where qp.PRIVILEGE_CD='%s' and qp.QUSER_ID=:userId) = 1)",
534 PrivilegeCode.REFERENTIAL_ADMINISTRATOR.getValue()
535 )
536 );
537
538 return queryBuilder.build();
539 }
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554 protected String addRestrictionOnExportToFile(SynchroTableMetadata table, SynchroQueryName queryName, String sql) {
555
556 SynchroQueryBuilder queryBuilder = SynchroQueryBuilder.newBuilder(queryName, sql);
557
558
559
560 if (queryName == SynchroQueryName.count
561 || queryName == SynchroQueryName.countFromUpdateDate) {
562 queryBuilder.replaceColumn("count(*)", "count(distinct t.survey_id)");
563 } else {
564 queryBuilder.setColumnDistinct(true);
565 }
566
567
568 String programJoinFilter = createProgramCodesFilter("INNER JOIN SURVEY_PROG sp ON sp.survey_id=t.survey_id AND sp.prog_cd IN (%s)");
569 if (StringUtils.isNotBlank(programJoinFilter)) {
570 queryBuilder.addJoin(programJoinFilter);
571 }
572
573
574 queryBuilder.addWhere(SynchroQueryOperator.AND,
575 String.format("%s is not null", DatabaseColumns.SURVEY_CONTROL_DT.name()));
576
577
578 boolean hasFilterDirtyOnly = getConfig().isDirtyOnly();
579 if (hasFilterDirtyOnly) {
580 queryBuilder.addWhere(SynchroQueryOperator.AND,
581 String.format("%s IN ('%s', '%s')",
582 DatabaseColumns.SYNCHRONIZATION_STATUS.name(),
583 SynchronizationStatus.DIRTY.getValue(),
584 SynchronizationStatus.READY_TO_SYNCHRONIZE.getValue()));
585 }
586
587
588 else {
589 queryBuilder.addWhere(SynchroQueryOperator.AND,
590 String.format("%s <> '%s'", DatabaseColumns.SYNCHRONIZATION_STATUS.name(), SynchronizationStatus.DELETED.getValue()));
591 }
592
593
594 String dateFilter = createDateFilter();
595 if (StringUtils.isNotBlank(dateFilter)) {
596
597 queryBuilder.addWhere(SynchroQueryOperator.AND, dateFilter);
598 }
599
600 return queryBuilder.build();
601 }
602
603
604
605
606
607
608
609
610
611
612 protected String createProgramCodesFilter(String stringToFormat) {
613 Set<String> programCodes = getConfig().getProgramCodes();
614 if (CollectionUtils.isEmpty(programCodes)) {
615 return null;
616 }
617 return String.format(stringToFormat, "'" + Joiner.on("','").join(programCodes) + "'");
618 }
619
620
621
622
623
624
625
626
627
628
629 protected String createPkFilter(SynchroTableMetadata table) {
630 if (getConfig().getPkIncludes() == null
631 || getConfig().getPkIncludes().isEmpty()) {
632 return null;
633 }
634
635 if (table.getPkNames().size() != 1) {
636 return null;
637 }
638
639 String pkName = table.getPkNames().iterator().next();
640
641 List<String> pkStrs = Lists.newArrayList(getConfig()
642 .getPkIncludes()
643 .get(table.getName().toUpperCase()));
644
645 if (CollectionUtils.isEmpty(pkStrs)) {
646
647
648 return "1=2";
649 }
650
651
652 List<String> inList = new ArrayList<>();
653
654 for (List<String> pkStrsChunk: Lists.partition(pkStrs, 1000)) {
655 inList.add(Joiner.on(',').join(pkStrsChunk));
656 }
657
658 if (inList.size() == 1) {
659
660
661 return String.format("t.%s IN (%s)", pkName, inList.get(0));
662
663 } else {
664
665
666 StringJoiner joiner = new StringJoiner(" OR ", "(", ")");
667 for (String in : inList) {
668 joiner.add(String.format("t.%s IN (%s)", pkName, in));
669 }
670 return joiner.toString();
671 }
672
673 }
674
675
676
677
678
679
680
681
682 protected String createDateFilter() {
683 DataSynchroDatabaseConfiguration config = getConfig();
684 SynchroDateOperatorVO operator = config.getDateOperator();
685 Date startDate = config.getDataStartDate();
686
687 if (operator == null || startDate == null) {
688 return null;
689 }
690
691 if (operator == SynchroDateOperatorVO.BETWEEN) {
692 Date endDate = config.getDataEndDate();
693 if (endDate == null) {
694 return null;
695 }
696
697 return String.format("t.%s >= :startDate AND t.%s <= :endDate",
698 DatabaseColumns.SURVEY_DT.name(),
699 DatabaseColumns.SURVEY_DT.name());
700 }
701
702 String operationStr;
703 switch (operator) {
704 case EQUALS:
705 operationStr = "=";
706 break;
707 case BEFORE:
708 operationStr = "<";
709 break;
710 case BEFORE_OR_EQUALS:
711 operationStr = "<=";
712 break;
713 case AFTER:
714 operationStr = ">";
715 break;
716 case AFTER_OR_EQUALS:
717 operationStr = ">=";
718 break;
719 default:
720 return null;
721 }
722
723 return String.format("t.%s %s :startDate",
724 DatabaseColumns.SURVEY_DT.name(),
725 operationStr);
726 }
727
728
729
730
731
732
733
734
735
736
737
738
739 protected String addDebugRestriction(String sql, String surveyFilteredColumnName) {
740
741 String idIncludes = QuadrigeConfiguration.getInstance().getApplicationConfig().getOption("quadrige3.synchro.import.survey.includes");
742 if (StringUtils.isBlank(idIncludes)) {
743 return sql;
744 }
745
746 SynchroQueryBuilder queryBuilder = SynchroQueryBuilder.newBuilder(sql);
747
748
749 queryBuilder.addWhere(SynchroQueryOperator.AND, String.format("t.%s in (%s)",
750 surveyFilteredColumnName,
751 idIncludes));
752
753 return queryBuilder.build();
754 }
755
756 protected String createSelectProgCdsBySurveyIdSql() {
757 return "select distinct sp.PROG_CD"
758 + " from SURVEY_PROG sp"
759 + " inner join SURVEY t on t.SURVEY_ID=sp.SURVEY_ID"
760 + " where t.SURVEY_ID=?";
761 }
762
763 protected String createSelectProgCdsBySimilarSurveySql() {
764
765
766
767
768 return "select distinct sp.PROG_CD, sp.SURVEY_ID"
769 + " from SURVEY t1,"
770 + " SURVEY t2"
771 + " inner join SURVEY_PROG sp on t2.SURVEY_ID=sp.SURVEY_ID"
772 + " where t1.SURVEY_ID=?"
773 + " AND t1.SURVEY_DT=t2.SURVEY_DT"
774 + " AND t1.MON_LOC_ID=t2.MON_LOC_ID"
775 + " AND ((t1.SURVEY_TIME IS NULL AND t2.SURVEY_TIME IS NULL) OR (t1.SURVEY_TIME=t2.SURVEY_TIME))";
776 }
777 }