1 package net.sumaris.core.extraction.dao.trip.survivalTest;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 import com.google.common.base.Preconditions;
26 import net.sumaris.core.extraction.dao.trip.rdb.ExtractionRdbTripDaoImpl;
27 import net.sumaris.core.extraction.dao.technical.XMLQuery;
28 import net.sumaris.core.extraction.vo.ExtractionFilterVO;
29 import net.sumaris.core.extraction.vo.trip.ExtractionTripFilterVO;
30 import net.sumaris.core.extraction.vo.trip.rdb.ExtractionRdbTripContextVO;
31 import net.sumaris.core.extraction.vo.trip.survivalTest.ExtractionSurvivalTestContextVO;
32 import net.sumaris.core.extraction.vo.trip.survivalTest.ExtractionSurvivalTestVersion;
33 import net.sumaris.core.model.referential.pmfm.PmfmEnum;
34 import org.slf4j.Logger;
35 import org.slf4j.LoggerFactory;
36 import org.springframework.context.annotation.Lazy;
37 import org.springframework.stereotype.Repository;
38
39
40
41
42 @Repository("extractionSurvivalTestDao")
43 @Lazy
44 public class ExtractionSurvivalTestDaoImpl<C extends ExtractionSurvivalTestContextVO> extends ExtractionRdbTripDaoImpl<C> implements ExtractionSurvivalTestDao {
45
46 private static final Logger log = LoggerFactory.getLogger(ExtractionSurvivalTestDaoImpl.class);
47
48 private static final String XML_QUERY_ST_PATH = "survivalTest/v%s/%s";
49
50 public static final String ST_SHEET_NAME = "ST";
51 public static final String RL_SHEET_NAME = "RL";
52
53 private static final String ST_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + ST_SHEET_NAME + "_%s";
54 private static final String RL_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + RL_SHEET_NAME + "_%s";
55
56 private String version = ExtractionSurvivalTestVersion.VERSION_1_0.getLabel();
57
58 @Override
59 public C execute(ExtractionFilterVO filter) {
60 String sheetName = filter != null ? filter.getSheetName() : null;
61
62
63 C context = super.execute(filter);
64
65
66 context.setFormatName(SURVIVAL_TEST_FORMAT);
67 context.setFormatVersion(version);
68 context.setSurvivalTestTableName(String.format(ST_TABLE_NAME_PATTERN, context.getId()));
69 context.setReleaseTableName(String.format(RL_TABLE_NAME_PATTERN, context.getId()));
70
71
72 if (sheetName != null && context.hasSheet(sheetName)) return context;
73
74
75 long rowCount = createSurvivalTestTable(context);
76 if (rowCount == 0) return context;
77 if (sheetName != null && context.hasSheet(sheetName)) return context;
78
79
80 rowCount = createReleaseTable(context);
81 if (rowCount == 0) return context;
82 if (sheetName != null && context.hasSheet(sheetName)) return context;
83
84 return context;
85 }
86
87
88
89
90 @Override
91 protected XMLQuery createTripQuery(C context) {
92 XMLQuery xmlQuery = super.createTripQuery(context);
93
94
95 xmlQuery.injectQuery(getXMLQueryURL(context, "injectionTripTable"));
96
97
98 xmlQuery.bind("mainMetierPmfmId", String.valueOf(PmfmEnum.MAIN_METIER.getId()));
99 xmlQuery.bind("conveyorBeltPmfmId", String.valueOf(PmfmEnum.CONVEYOR_BELT.getId()));
100 xmlQuery.bind("nbFishermenPmfmId", String.valueOf(PmfmEnum.NB_FISHERMEN.getId()));
101 xmlQuery.bind("nbSamplingOperPmfmId", String.valueOf(PmfmEnum.NB_SAMPLING_OPERATION.getId()));
102 xmlQuery.bind("randomSamplingOperPmfmId", String.valueOf(PmfmEnum.RANDOM_SAMPLING_OPERATION.getId()));
103
104 return xmlQuery;
105 }
106
107 @Override
108 protected XMLQuery createStationQuery(C context) {
109 XMLQuery xmlQuery = super.createStationQuery(context);
110
111
112 xmlQuery.injectQuery(getXMLQueryURL(context, "injectionStationTable"));
113
114
115 xmlQuery.bind("substrateTypePmfmId", String.valueOf(PmfmEnum.SUBSTRATE_TYPE.getId()));
116 xmlQuery.bind("bottomTempPmfmId", String.valueOf(PmfmEnum.BOTTOM_TEMP_C.getId()));
117 xmlQuery.bind("seaStatePmfmId", String.valueOf(PmfmEnum.SEA_STATE.getId()));
118 xmlQuery.bind("survivalSamplingTypePmfmId", String.valueOf(PmfmEnum.SURVIVAL_SAMPLING_TYPE.getId()));
119
120 xmlQuery.bind("landingWeightPmfmId", String.valueOf(PmfmEnum.LANDING_WEIGHT.getId()));
121 xmlQuery.bind("sandStonesWeightRangePmfmId", String.valueOf(PmfmEnum.SAND_STONES_WEIGHT_RANGE.getId()));
122 xmlQuery.bind("benthosWeightRangePmfmId", String.valueOf(PmfmEnum.BENTHOS_WEIGHT_RANGE.getId()));
123 xmlQuery.bind("onDeckDateTimePmfmId", String.valueOf(PmfmEnum.ON_DECK_DATE_TIME.getId()));
124 xmlQuery.bind("sortingDateTimePmfmId", String.valueOf(PmfmEnum.SORTING_START_DATE_TIME.getId()));
125 xmlQuery.bind("sortingEndDateTimePmfmId", String.valueOf(PmfmEnum.SORTING_END_DATE_TIME.getId()));
126
127 return xmlQuery;
128 }
129
130 @Override
131 protected XMLQuery createSpeciesLengthQuery(C context) {
132 XMLQuery xmlQuery = super.createSpeciesLengthQuery(context);
133
134
135 xmlQuery.injectQuery(getXMLQueryURL(context, "injectionSpeciesLengthTable"));
136
137
138 xmlQuery.bind("isDeadPmfmId", String.valueOf(PmfmEnum.IS_DEAD.getId()));
139 xmlQuery.bind("tagIdPmfmId", String.valueOf(PmfmEnum.TAG_ID.getId()));
140 xmlQuery.bind("discardReasonPmfmId", String.valueOf(PmfmEnum.DISCARD_REASON.getId()));
141
142 return xmlQuery;
143 }
144
145 @Override
146 protected Class<? extends ExtractionRdbTripContextVO> getContextClass() {
147 return ExtractionSurvivalTestContextVO.class;
148 }
149
150 protected String getQueryFullName(C context, String queryName) {
151 Preconditions.checkNotNull(context);
152 Preconditions.checkNotNull(context.getFormatVersion());
153
154 String versionStr = version.replaceAll("[.]", "_");
155 switch (queryName) {
156 case "injectionTripTable":
157 case "injectionStationTable":
158 case "injectionSpeciesLengthTable":
159 case "createSurvivalTestTable":
160 case "createReleaseTable":
161 return String.format(XML_QUERY_ST_PATH, versionStr, queryName);
162 default:
163 return super.getQueryFullName(context, queryName);
164 }
165 }
166
167 protected long createSurvivalTestTable(C context) {
168
169 log.debug(String.format("Extraction #%s > Creating survival tests table...", context.getId()));
170 XMLQuery xmlQuery = createXMLQuery(context, "createSurvivalTestTable");
171 xmlQuery.bind("stationTableName", context.getStationTableName());
172 xmlQuery.bind("survivalTestTableName", context.getSurvivalTestTableName());
173
174
175 execute(xmlQuery);
176 long count = countFrom(context.getSurvivalTestTableName());
177
178
179 if (count > 0) {
180 count -= cleanRow(context.getSurvivalTestTableName(), context.getFilter(), ST_SHEET_NAME);
181 }
182
183
184 if (count > 0) {
185 context.addTableName(context.getSurvivalTestTableName(),
186 ST_SHEET_NAME,
187 xmlQuery.getHiddenColumnNames(),
188 xmlQuery.hasDistinctOption());
189 log.debug(String.format("Extraction #%s > Survival test table: %s rows inserted", context.getId(), count));
190 }
191 return count;
192 }
193
194 protected long createReleaseTable(C context) {
195
196 log.debug(String.format("Extraction #%s > Creating releases table...", context.getId()));
197 XMLQuery xmlQuery = createXMLQuery(context, "createReleaseTable");
198 xmlQuery.bind("stationTableName", context.getStationTableName());
199 xmlQuery.bind("releaseTableName", context.getReleaseTableName());
200
201
202 execute(xmlQuery);
203 long count = countFrom(context.getReleaseTableName());
204
205
206 if (count > 0) {
207 count -= cleanRow(context.getReleaseTableName(), context.getFilter(), RL_SHEET_NAME);
208 }
209
210
211 if (count > 0) {
212 context.addTableName(context.getReleaseTableName(),
213 RL_SHEET_NAME,
214 xmlQuery.getHiddenColumnNames(),
215 xmlQuery.hasDistinctOption());
216 log.debug(String.format("Extraction #%s > Release table: %s rows inserted", context.getId(), count));
217 }
218 return count;
219 }
220
221 }