public JwList<AcDomesticNominatedRoute> getAllByStatusCreatedUtcTsOnOrAfter(
AcDomesticNominatedRouteStatusEnum status, JwTimestamp createdUtcTs) {
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, STATUS, status != null ? status.getCode() : null);
if (createdUtcTs != null)
st.where().isGreaterThanOrEqualTo(ALIAS, CREATED_UTC_TS, createdUtcTs);
return composeList(st);
}
public boolean isModuleAssigned(Integer moduleConfigId) {
JwSqlSelect st = createSelect();
st.selectCount();
st.from(TABLE, ALIAS);
st.where().or();
st.where().isEqual(ALIAS, MY_GLOBAL_MODULE_CONFIG_ID, moduleConfigId);
st.where().isEqual(ALIAS, MY_STATION_MODULE_CONFIG_ID, moduleConfigId);
int count = composeCount(st);
return count > 0;
}
public JwList<AcUser> getAllActiveCargoWorkersByRole() {
AcUserRoleDb ur = null;
JwSqlSelect st = getSelect();
st.from(ur.TABLE, ur.ALIAS);
st.where().isEqual(ur.ALIAS, ur.ROLE, AcRoleEnum.CARGO_WORKER.getCode());
st.where().isEqualColumn(ur.ALIAS, ur.LOGIN, ALIAS, LOGIN);
st.where().isTrue(ALIAS, ACTIVE);
return composeList(st);
}
public JwList<AcUser> getAllActiveCargoWorkersByRoleForAirports(JwList<String> airportCodes) {
AcUserAirportDb ua = null;
AcUserRoleDb ur = null;
JwSqlSelect st = getSelect();
st.from(ua.TABLE, ua.ALIAS);
st.from(ur.TABLE, ur.ALIAS);
st.where().isEqual(ur.ALIAS, ur.ROLE, AcRoleEnum.CARGO_WORKER.getCode());
st.where().isEqualColumn(ur.ALIAS, ur.LOGIN, ua.ALIAS, ua.LOGIN);
st.where().isIn(ua.ALIAS, ua.AIRPORT_CODE, airportCodes.toStringArray());
st.where().isEqualColumn(ua.ALIAS, ua.LOGIN, ALIAS, LOGIN);
st.where().isTrue(ALIAS, ACTIVE);
return composeList(st);
}
public JwList<AcUspsDomesticClaim> getAllBySubmissionId(Integer submissionId) {
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, SUBMISSION_ID, submissionId);
return composeList(st);
}
private JwSqlSelect getFlightNumberExistsSelect(String flightNumber) {
JwSqlSelect st = createSelect();
st.selectInteger(AcDomesticNominatedRouteLegDb.ALIAS, AcDomesticNominatedRouteLegDb.ID);
st.from(AcDomesticNominatedRouteLegDb.TABLE, AcDomesticNominatedRouteLegDb.ALIAS);
st.where()
.isEqualColumn(
AcDomesticNominatedRouteLegDb.ALIAS,
AcDomesticNominatedRouteLegDb.DOMESTIC_NOMINATED_ROUTE_ID,
ALIAS,
ID);
st.where()
.isEqual(
AcDomesticNominatedRouteLegDb.ALIAS,
AcDomesticNominatedRouteLegDb.FLIGHT_NUMBER,
AcFlightUtility.normalizeFlightNumber(flightNumber));
return st;
}
public JwList<AcUser> getAllActiveCargoWorkersByRoleGroupForAirport(String airportCode) {
AcUserAirportDb ua = null;
AcUserRoleDb ur = null;
AcRoleGroupRoleDb rgr = null;
JwSqlSelect st = getSelect();
st.from(ua.TABLE, ua.ALIAS);
st.from(ur.TABLE, ur.ALIAS);
st.from(rgr.TABLE, rgr.ALIAS);
st.where().isEqual(rgr.ALIAS, rgr.ROLE, AcRoleEnum.CARGO_WORKER.getCode());
st.where().isEqualColumn(rgr.ALIAS, rgr.ROLE_GROUP_CODE, ur.ALIAS, ur.ROLE);
st.where().isEqualColumn(ur.ALIAS, ur.LOGIN, ua.ALIAS, ua.LOGIN);
st.where().isEqual(ua.ALIAS, ua.AIRPORT_CODE, airportCode);
st.where().isEqualColumn(ua.ALIAS, ua.LOGIN, ALIAS, LOGIN);
st.where().isTrue(ALIAS, ACTIVE);
return composeList(st);
}
public AcGpsReport getNewByOutputChannelId(Integer outputChannelId) {
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, STATUS, AcGpsReportStatusEnum.NEW.getCode());
st.where().isEqual(ALIAS, OUTPUT_CHANNEL_ID, outputChannelId);
st.orderBy(ALIAS, CREATED_UTC_TS);
st.orderBy(ALIAS, ID);
return composeFirst(st);
}
public JwList<AcGpsReport> getAllCompleteAndCreatedOnOrBefore(JwTimestamp utcTs) {
String[] codes = {
AcGpsReportStatusEnum.COMPLETE.getCode(), AcGpsReportStatusEnum.ERROR.getCode()
};
JwSqlSelect st = getSelect();
st.where().isIn(ALIAS, STATUS, codes);
st.where().isLessThanOrEqualTo(ALIAS, CREATED_UTC_TS, utcTs);
return composeList(st);
}
public JwList<AcGlobalAuditLog> getAllById(Collection<Integer> ids) {
JwList<AcGlobalAuditLog> v = new JwList<AcGlobalAuditLog>();
for (Integer id : ids) {
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, ID, id);
v.addAll(composeList(st));
}
return v;
}
public JwList<AcTravelPlanConfirmation> getAllContainingItem(Integer itemId) {
AcTravelPlanConfirmationItemDb iDb = null;
JwSqlSelect st = getSelect();
st.from(iDb.TABLE, iDb.ALIAS);
st.where().isEqual(iDb.ALIAS, iDb.ITEM_ID, itemId);
st.where().isEqualColumn(iDb.ALIAS, iDb.TRAVEL_PLAN_CONFIRMATION_ID, ALIAS, ID);
return composeList(st);
}
public JwList<AcUploadDownloadFile> getAllById(Collection<Integer> ids) {
JwList<AcUploadDownloadFile> v = new JwList<AcUploadDownloadFile>();
for (Integer id : ids) {
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, ID, id);
v.addAll(composeList(st));
}
return v;
}
@Override
public JwList<AcGpsReport> getAllByStatusOutputChannelId(
AcGpsReportStatusEnum status, Integer outputChannelId) {
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, STATUS, status != null ? status.getCode() : null);
st.where().isEqual(ALIAS, OUTPUT_CHANNEL_ID, outputChannelId);
st.orderBy(ALIAS, CREATED_UTC_TS);
st.orderBy(ALIAS, ID);
return composeList(st);
}
public AcGpsReport getNotCompleteByOutputChannelId(Integer outputChannelId) {
String[] codes = {
AcGpsReportStatusEnum.COMPLETE.getCode(), AcGpsReportStatusEnum.ERROR.getCode()
};
JwSqlSelect st = getSelect();
st.where().isNotIn(ALIAS, STATUS, codes);
st.where().isEqual(ALIAS, OUTPUT_CHANNEL_ID, outputChannelId);
st.orderBy(ALIAS, CREATED_UTC_TS);
st.orderBy(ALIAS, ID);
return composeFirst(st);
}
public JwList<AcDomesticNominatedRoute> getAllByNormalizedBeginUtcDtBetweenCreatedOnOrAfter(
JwDate startUtcDt,
JwDate endUtcDt,
String originAirport,
String destinationAirport,
String flightNumber,
JwTimestamp createdUtcTs,
JwList<String> statuses) {
if (statuses != null && statuses.isEmpty()) return new JwList<AcDomesticNominatedRoute>();
JwSqlSelect st = getSelect();
st.where().isGreaterThanOrEqualTo(ALIAS, NORMALIZED_BEGIN_UTC_DT, startUtcDt);
st.where().isLessThanOrEqualTo(ALIAS, NORMALIZED_BEGIN_UTC_DT, endUtcDt);
if (JwUtility.hasValue(originAirport))
st.where().isEqual(ALIAS, ORIGIN_AIRPORT_CODE, originAirport);
if (JwUtility.hasValue(destinationAirport))
st.where().isEqual(ALIAS, DESTINATION_AIRPORT_CODE, destinationAirport);
if (JwUtility.hasValue(flightNumber))
st.where().exists(getFlightNumberExistsSelect(flightNumber));
if (createdUtcTs != null)
st.where().isGreaterThanOrEqualTo(ALIAS, CREATED_UTC_TS, createdUtcTs);
if (statuses != null) st.where().isInStrings(ALIAS, STATUS, statuses);
st.orderBy(ALIAS, NORMALIZED_BEGIN_UTC_DT);
st.orderBy(ALIAS, NORMALIZED_END_UTC_DT);
return composeList(st);
}
public JwList<AcDomesticNominatedRouteSummaryVo> groupSummaryByCreatedUtcTsOnOrAfter(
JwTimestamp startUtcTs) {
JwList<AcDomesticNominatedRouteSummaryVo> v = new JwList<AcDomesticNominatedRouteSummaryVo>();
JwSqlSelect st = createSelect();
st.selectDate(ALIAS, NORMALIZED_BEGIN_UTC_DT);
st.selectDate(ALIAS, NORMALIZED_END_UTC_DT);
st.selectMinimumTimestamp(ALIAS, CREATED_UTC_TS);
st.selectCount();
st.from(TABLE, ALIAS);
st.where().isGreaterThanOrEqualTo(ALIAS, CREATED_UTC_TS, startUtcTs);
st.where().isEqual(ALIAS, STATUS, AcDomesticNominatedRouteStatusEnum.NEW.getCode());
st.groupBy(ALIAS, NORMALIZED_BEGIN_UTC_DT);
st.groupBy(ALIAS, NORMALIZED_END_UTC_DT);
AcDbUtility.processResults(
getAccess(), st, getReadNominatedRouteSummaryVoListResultSetProcessor(v));
return v;
}
public JwList<AcDomesticCandidateRouteSummaryVo> groupSummaryByNormalizedBeginUtcDtBetween(
JwDate startUtcDt, JwDate endUtcDt) {
// select c.normalizedBeginUtcDt, c.normalizedEndUtcDt, count(distinct c.id),
// group_concat(distinct c.status)
// from domesticNominatedRoute c
// where c.normalizedBeginUtcDt >= startUtcDt and c.normalizedBeginUtcDt <= endUtcDt
// group by c.normalizedBeginUtcDt, c.normalizedEndUtcDt
JwList<AcDomesticCandidateRouteSummaryVo> v = new JwList<AcDomesticCandidateRouteSummaryVo>();
JwSqlSelect st = createSelect();
st.selectDate(ALIAS, NORMALIZED_BEGIN_UTC_DT);
st.selectDate(ALIAS, NORMALIZED_END_UTC_DT);
st.selectCountDistinct(ALIAS, ID);
st.selectGroupConcatDistinct(ALIAS, STATUS);
st.from(TABLE, ALIAS);
st.where().isGreaterThanOrEqualTo(ALIAS, NORMALIZED_BEGIN_UTC_DT, startUtcDt);
st.where().isLessThanOrEqualTo(ALIAS, NORMALIZED_BEGIN_UTC_DT, endUtcDt);
st.groupBy(ALIAS, NORMALIZED_BEGIN_UTC_DT);
st.groupBy(ALIAS, NORMALIZED_END_UTC_DT);
AcDbUtility.processResults(getAccess(), st, getReadSummaryVoListResultSetProcessor(v));
return v;
}
private JwSqlSelect getUserRoleGroupsSelect(String isRoleGroup, String groupRoleRoles) {
AcUserRoleDb ur = null;
AcRoleGroupRoleDb rgr = null;
JwSqlSimpleExprColumn c =
new JwSqlSimpleExprColumn(_access.getAdaptor(), rgr.ALIAS, rgr.ROLE_GROUP_CODE);
JwSqlBooleanPrimaryIs isRoleGroupExpr = new JwSqlBooleanPrimaryIs(c, true);
JwSqlSelect st = createSelect();
st.selectString(ALIAS, LOGIN);
st.selectString(ALIAS, FIRST_NAME);
st.selectString(ALIAS, LAST_NAME);
st.selectString(ur.ALIAS, ur.ROLE);
st.addExpression(isRoleGroupExpr, isRoleGroup);
st.selectGroupConcat(rgr.ALIAS, rgr.ROLE, null, "1", null, groupRoleRoles);
st.from(TABLE, ALIAS);
st.from(ur.TABLE, ur.ALIAS);
st.fromLeftOuterJoin(null, ur.ALIAS, rgr.TABLE, rgr.ALIAS, ur.ROLE, rgr.ROLE_GROUP_CODE);
st.where().isTrue(ALIAS, ACTIVE);
st.where().isEqualColumn(ALIAS, LOGIN, ur.ALIAS, ur.LOGIN);
st.groupBy(1, 2, 3, 4, 5);
return st;
}
public JwList<AcUser> getAllStartingWithMatchingActive(String s, Boolean active) {
JwSqlSelect st = getSelect();
if (JwUtility.hasValue(s)) st.where().startsWith(ALIAS, LOGIN, s);
if (active != null) st.where().isEqual(ALIAS, ACTIVE, active);
return composeList(st);
}
public JwList<AcScheduledFlightUpload> getAllByOriginAirportCode(String originAirportCode) {
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, ORIGIN_AIRPORT_CODE, originAirportCode);
return composeList(st);
}
public AcDomesticCandidateRouteTmp getDomesticCandidateRouteTmp(String login) {
if (isPrimaryKeyNull(login)) return null;
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, LOGIN, login);
return composeFirst(st);
}
public void verifyNoName(AcMobileProfile e) {
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, NAME, e.getName());
AcMobileProfile o = composeFirst(st);
if (o != null) JwErrorUtility.throwNewUniqueError("AcMobileProfile", "Name", e.getName());
}
public JwList<AcGlobalAuditLog> getAllByType(Integer type) {
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, TYPE, type);
return composeList(st);
}
public JwList<AcScheduledFlightUpload> getAllByDestinationAirportCode(
String destinationAirportCode) {
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, DESTINATION_AIRPORT_CODE, destinationAirportCode);
return composeList(st);
}
public AcScheduledFlightUpload getScheduledFlightUpload(Integer id) {
if (isPrimaryKeyNull(id)) return null;
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, ID, id);
return composeFirst(st);
}
public AcMobileProfile getMobileProfile(String name) {
if (isPrimaryKeyNull(name)) return null;
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, NAME, name);
return composeFirst(st);
}
public AcGlobalAuditLog getGlobalAuditLog(Integer id) {
if (isPrimaryKeyNull(id)) return null;
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, ID, id);
return composeFirst(st);
}
public JwList<AcMobileProfile> getAllWhere(String whereClause, Integer rowLimit) {
JwSqlSelect st = getSelect();
if (JwUtility.hasValue(whereClause)) st.where().setCondition(whereClause);
return composeList(st, rowLimit);
}
public JwList<AcDomesticCandidateRouteTmp> getAllWhere(String whereClause, Integer rowLimit) {
JwSqlSelect st = getSelect();
if (JwUtility.hasValue(whereClause)) st.where().setCondition(whereClause);
return composeList(st, rowLimit);
}
public AcUploadDownloadFile getUploadDownloadFile(Integer id) {
if (isPrimaryKeyNull(id)) return null;
JwSqlSelect st = getSelect();
st.where().isEqual(ALIAS, ID, id);
return composeFirst(st);
}