private String getSQL(String strDate,String endDate){
String sql = "select t.arlncd,t.fleetcd,sum(t.flightnum) flightnum,sum(t.incountry) incountry,sum(t.international) international,"
+" sum(t.transport) transport,sum(t.freight) freight,sum(t.pilotnum) pilotnum,sum(t.capnum) capnum,sum(t.capflytime) capflytime,"
+" sum(t.fonum) fonum,sum(t.foflytime) foflytime,sum(t.groupnum) groupnum,sum(t.mttime) mttime,sum(t.motime) motime,"
+" sum(t.segtime) segtime,sum(t.bttime) bttime,sum(t.botime) botime"
+" from (select substr(fd.arlncd, 1, 3) arlncd,fd.fleetcd fleetcd,0 flightNum,0 incountry,0 international,case when fd.flighttype = 'B/W' or fd.flighttype = 'C/B' or"
+" fd.flighttype = 'F/H' or fd.flighttype = 'W/Z' or fd.flighttype = 'Z/P' or fd.flighttype = 'Z/X' then fd.flytime else 0 end transport,"
+" case when fd.flighttype = 'H/G' or fd.flighttype = 'H/Y' then fd.flytime else 0 end freight,0 pilotNum,0 capNum,0 capFlyTime,"
+" 0 foNum,0 foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,0 btTime,0 boTime from pilotduty pd left join flyduty fd on pd.flyduty_id = fd.id"
+" where fd.flighttype in ('H/G', 'H/Y', 'B/W', 'C/B', 'F/H', 'W/Z', 'Z/P', 'Z/X') and fd.strdt between to_date('"+strDate+"','YYYY-MM-DD') and to_date('"+endDate+"','YYYY-MM-DD')"
+" union all select substr(fd.arlncd, 1, 3) arlncd, fd.fleetcd fleetcd,0 flightNum,0 incountry,0 international,"
+" 0 transport,0 freight,0 pilotNum,0 capNum,sum(decode(pd.identify, 'CAP', pd.flytime, 0)) capFlyTime,0 foNum,"
+" sum(decode(pd.identify, 'F/O', pd.flytime, 'S/O', pd.flytime, 0)) foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,0 btTime,0 boTime"
+" from flyduty fd left join pilotduty pd on pd.flyduty_id = fd.id where (pd.identify = 'CAP' or pd.identify = 'F/O' or pd.identify = 'S/O')"
+" and fd.arlncd is not null and fd.fleetcd is not null and fd.strdt between to_date('"+strDate+"','YYYY-MM-DD') and to_date('"+endDate+"','YYYY-MM-DD')"
+" group by fd.arlncd, fd.fleetcd having fd.arlncd is not null"
+" union all"
+" select p.arlcd,p.fleetcd,0 flightNum,0 incountry,0 international,0 transport,0 freight,count(*) pilotNum,0 capNum,0 capFlyTime,0 foNum,"
+" 0 foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,0 btTime,0 boTime from (select substr(f.arlncd, 1, 3) arlcd, f.fleetcd"
+" from flyduty f where f.arlncd is not null and f.fleetcd is not null group by f.arlncd, f.fleetcd) p left join (select"
+" substr(registration_base_code,-4,3) arlcd,pt.fleet from post pt"
+" left join subranktype s on pt.subranktype_id = s.id left join person pn on pt.person_id = pn.id where type = '注册岗位'"
+" and s.fd_ind = '飞行员' and pn.deleted = 0 and pt.effdt <= to_date('"+endDate+"', 'YYYY-MM-DD') and (pt.expdt is null or pt.expdt>to_date('"+strDate+"', 'YYYY-MM-DD'))"
+" and pt.fleet is not null and pn.registration_base_code is not null) t on t.arlcd = p.arlcd"
+" where t.fleet like '%' || p.fleetcd || '%' group by p.arlcd, p.fleetcd"
+" union all"
+" select p.arlcd, p.fleetcd,0 flightNum,0 incountry,0 international,0 transport,0 freight,0 pilotNum,count(*) capNum,0 capFlyTime,"
+" 0 foNum,0 foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,0 btTime,0 boTime from (select substr(f.arlncd, 1, 3) arlcd, f.fleetcd"
+" from flyduty f where f.arlncd is not null and f.fleetcd is not null group by f.arlncd, f.fleetcd) p left join (select"
+" substr(registration_base_code,-4,3) arlcd,pt.fleet from post pt left join subranktype s on pt.subranktype_id = s.id"
+" left join person pn on pt.person_id = pn.id where pt.type = '注册岗位' and s.rank_cd = 'CAP' or s.rank_cd = 'CAPT' and pn.deleted = 0"
+" and pt.effdt <= to_date('"+endDate+"', 'YYYY-MM-DD') and (pt.expdt is null or pt.expdt > to_date('"+strDate+"', 'YYYY-MM-DD'))"
+" and pt.fleet is not null) t on t.arlcd = p.arlcd where t.fleet like '%' || p.fleetcd || '%' group by p.arlcd, p.fleetcd"
+" union all"
+" select p.arlcd,p.fleetcd,0 flightNum,0 incountry,0 international,0 transport,0 freight,0 pilotNum,0 capNum,0 capFlyTime,"
+" count(*) foNum,0 foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,0 btTime,0 boTime from (select substr(f.arlncd, 1, 3) arlcd, f.fleetcd fleetcd"
+" from flyduty f where f.arlncd is not null and f.fleetcd is not null group by f.arlncd, f.fleetcd) p left join (select"
+" substr(registration_base_code,-4,3) arlcd,pt.fleet from post pt left join subranktype s on pt.subranktype_id = s.id"
+" left join person pn on pt.person_id = pn.id where pt.type = '注册岗位' and s.rank_cd = 'FO' and pn.deleted = 0"
+" and pt.effdt <= to_date('"+endDate+"', 'YYYY-MM-DD') and (pt.expdt is null or pt.expdt > to_date('"+strDate+"', 'YYYY-MM-DD')) and pt.fleet is not null) t on t.arlcd = p.arlcd"
+" where t.fleet like '%' || p.fleetcd || '%' group by p.arlcd, p.fleetcd"
+" union all"
+" select tp.orgunitcoding arlncd,tp.fleetcd fleetcd,0 flightNum,0 incountry,0 international,0 transport,0 freight,0 pilotNum,0 capNum,0 capFlyTime,"
+" 0 foNum,0 foFlyTime,count(*) groupnum,sum(tf.teachertime) mtTime,sum(tf.observerstime) moTime,0 segTime,0 btTime,0 boTime"
+" from trainingplan tp left join trainingproject tg on tg.id = tp.trainingproject_id left join trainingsubject ts on ts.id = tg.trainingsubject_id"
+" left join trainingpersoninfo tf on tf.trainingplan_id = tp.id where ts.subjecttype = '模拟机训练'"
+" and tf.startdate_str between '"+strDate+"' and '"+endDate+"' group by tp.orgunitcoding, tp.fleetcd"
+" union all"
+" select tp.orgunitcoding arlncd,tp.fleetcd fleetcd,0 flightNum,0 incountry,0 international,0 transport,0 freight,0 pilotNum,0 capNum,0 capFlyTime,"
+" 0 foNum,0 foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,sum(tf.teachertime) btTime,sum(tf.observerstime) boTime from trainingplan tp"
+" left join trainingproject tg on tg.id = tp.trainingproject_id left join trainingpersoninfo tf on tf.trainingplan_id = tp.id"
+" where tg.isplacetraining = '是' and tf.startdate_str between '"+strDate+"' and '"+endDate+"' group by tp.orgunitcoding, tp.fleetcd"
+" union all"
+" select fd.fleetcd fleetcd,fd.arlncd arlncd,0 flightNum,0 incountry,0 international,0 transport,0 freight,0 pilotNum,0 capNum,"
+" 0 capFlyTime,0 foNum,0 foFlyTime,0 groupnum,0 mtTime,0 moTime,pd.segtimes segTime,0 btTime,0 boTime from pilotduty pd"
+" left join flyduty fd on pd.flyduty_id = fd.id where fd.flighttype = 'K/L' and fd.strdt between to_date('"+strDate+"', 'YYYY-MM-DD') and to_date('"+endDate+"', 'YYYY-MM-DD')"
+" ) t group by t.arlncd,fleetcd";
return sql.toUpperCase();
}