public void populateSelectColumnsToCompose(JwSqlSelect st, String alias) {
st.selectString(alias, LOGIN);
st.selectTimestamp(alias, CREATED_UTC_TS);
st.selectDate(alias, BLOCK_BEGIN_UTC_DT);
st.selectDate(alias, BLOCK_END_UTC_DT);
st.selectDate(alias, ROUTE_BEGIN_UTC_DT);
st.selectDate(alias, ROUTE_END_UTC_DT);
st.selectInteger(alias, ERROR);
}
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 void populateSelectColumnsToCompose(JwSqlSelect st, String alias) {
st.selectInteger(alias, ID);
st.selectString(alias, OPERATOR_CARRIER_CODE);
st.selectString(alias, OPERATOR_NUMBER);
st.selectString(alias, RELATIONSHIP);
st.selectString(alias, ORIGIN_AIRPORT_CODE);
st.selectString(alias, DESTINATION_AIRPORT_CODE);
st.selectTime(alias, DEPARTURE_LOCAL_TM);
st.selectTime(alias, ARRIVAL_LOCAL_TM);
st.selectInteger(alias, DAY_OFFSET);
st.selectDate(alias, BOUNDING_START_LOCAL_DT);
st.selectDate(alias, BOUNDING_END_LOCAL_DT);
st.selectDayFrequency(alias, FREQUENCY);
st.selectString(alias, EQUIPMENT_CODE);
st.selectString(alias, SOURCE);
st.selectString(alias, ABSOLUTE_SOURCE);
st.selectBoolean(alias, CANCEL);
st.selectTimestamp(alias, CREATED_UTC_TS);
st.selectInteger(alias, SEQUENCE);
}
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;
}