/** * 获取时间限定sql语句。。。该函数还会顺北将参数设置到 params列表中。。 要求是表别名必须是 t !!!!! * * @param aqm * @param thisYearDateResult * @param flightDateFieldName 飞行日期 e3为 lcl_dpt_dt e4为 flight_date * @param aiplanTypeFieldName 机型 e3为plane_model e4为aircraft_type * @return * @throws Exception */ public static String limitSqlString( AirlineQueryModel aqm, List<Map<String, Object>> thisYearDateResult, Map<String, String> fieldName, List<Object> params) throws Exception { // 1 为时间段 2 为时间点。 String dateType = thisYearDateResult.get(0).get("dateType").toString(); StringBuffer result = new StringBuffer(" on t.client_id = '" + aqm.getClientId() + "'"); // StringBuffer result = new StringBuffer(" on 1 = 1 "); if (StringUtils.equals(dateType, "1")) { result.append(" and ("); for (Map<String, Object> m : thisYearDateResult) { String beginDt = m.get("beginDate").toString(); String endDt = m.get("endDate").toString(); result.append( " t." + fieldName.get("flightDate") + " between to_date(?, 'yyyy/MM/dd') and to_date(?, 'yyyy/MM/dd') or"); params.add(beginDt); params.add(endDt); } result.append(" 1=2)"); } else if (StringUtils.equals(dateType, "2")) { String dt = thisYearDateResult.get(0).get("searchDate").toString(); String[] dtL = dt.split(","); result.append( " and to_char(t." + fieldName.get("flightDate") + ",'yyyy/MM/dd') in (" + Stringer.formatStr(Statics.gson.toJson(dtL)) + ")"); } if (aqm.getWeeks().length() > 2) { result.append( " and t." + fieldName.get("week") + " in (" + Stringer.formatStr(aqm.getWeeks()) + ")"); } if (aqm.getCompany().length() > 2) { result.append( " and t." + fieldName.get("company") + " in (" + Stringer.formatStr(aqm.getCompany()) + ")"); } if (aqm.getAirplaneType().length() > 2) { result.append( " and t." + fieldName.get("airlineType") + " in (" + Stringer.formatStr(aqm.getAirplaneType()) + ")"); } return result.toString(); }
/** * @param aqm * @param thisYearDateResult * @param segs * @param fc 指标 表的配置信息 * @param tableField 指标表对应父表的 一些字段。 * @param result * @throws Exception * @throws IllegalAccessException * @throws InvocationTargetException */ protected void dealTotal( AirlineQueryModel aqm, List<Map<String, Object>> thisYearDateResult, Map<String, Object> segs, BaseFlagConfig fc, Map<String, String> tableField, List<Map<String, Object>> result) throws Exception, IllegalAccessException, InvocationTargetException { // 遍历航线。如: 成都-北京首都-成都 CTU-PEK_PEK-CTU Set<String> keys = segs.keySet(); for (String k : keys) { params.clear(); String airlineLst = (String) segs.get(k); String airlines = Statics.gson.toJson(airlineLst.split("_")); // 获取父表名字 e0301_flt 或者 E0401_AIRLINE String tableName = tableField.get("tableName"); // 年份 、航线字节码,航线中文翻译。 StringBuffer sql = new StringBuffer( "select '" + thisYearDateResult.get(0).get("year").toString() + "' YEAR,"); sql.append(" '" + airlineLst + "' AIRLINE,"); sql.append(" '" + k + "' AIRLINEREF,"); sql.append("c.DT,"); // sum string (" sum(c.A) A,"); sql.append(fieldJoint("c", true, true, fc)); // 开始拼接子表。 sql.append(" from (select "); // 日期维度 字段DT。 sql.append(switchDateTypeToSqlString(thisYearDateResult, tableField.get("flightDate"))); // 拼接指标表字段。.(" tableAlias.fieldName Alias,"); sql.append(fieldJoint(fc.tableAlias, false, false, fc)); // 连接指标表和父表 sql.append(" from " + tableName + " t inner join " + fc.tableName + " " + fc.tableAlias); // 条件 子句拼接。 // 日期、周、公司、机型限定。 sql.append(limitSqlString(aqm, thisYearDateResult, tableField, params)); // 经停航线 if (tableName.equals(someE3TableField.get("tableName"))) { // 限定航线 air_line 限定air_seg_nbr为0. sql.append( " and t.air_line in (" + Stringer.formatStr(airlines) + ") and t.air_seg_nbr = 0"); } // 直达航线。 else { // 限定经停标识。 sql.append( " and t.airline_stop_flag in ('" + aqm.getIncludeGoto() + "','" + aqm.getIncludeStop() + "')"); // 连接离港和到达地两个字段 (离港-到达地) 限定, sql.append( " and t.dpt_airpt_cd||'-'||t.arr_airpt_cd in (" + Stringer.formatStr(airlines) + ")"); } sql.append(" and t.id = " + fc.tableAlias + "." + fc.foreignFieldName); // 年份(已被日期限定。)、航线(已被限定)、时间维度分组。 sql.append(" ) c group by c.DT"); List<Map<String, Object>> listMap = super.executeSql(sql.toString(), params); result.addAll(listMap); // 非全年需要小计一次。 String dimensionality = thisYearDateResult.get(0).get("dimensionality").toString(); if (!dimensionality.contains("全年")) { subTotal(fc, result, listMap, "DT"); } } }