#############################################
#############################################
##########     Zeitserie       ##############
#############################################
#############################################

timeseries_timeseriespoint=SELECT DISTINCT \
        tsp.FEATUREID KEY, \
        tsp.NAME VALUE \
    FROM MEDIAN.TIMESERIESPOINT tsp, \
         MEDIAN.MEASUREMENT mmt \
    WHERE tsp.FEATUREID =  mmt.FEATUREID AND \
          mmt.SOURCEID = ? \
    order by tsp.name

timeseries_parameter=SELECT DISTINCT \
        p.PARAMETERID KEY, \
        p.GERMANNAME VALUE \
    from MEDIAN.PARAMETER p \
    where p.PARAMETERID in \
          (select distinct \
              ts.PARAMETERID \
           from MEDIAN.TIMESERIES ts \
           where ts.TIMESERIESID in \
               (select distinct \
                   tsv.TIMESERIESID \
                from MEDIAN.TIMESERIESVALUE tsv \
                where tsv.MEASUREMENTID in \
                    (select m.MEASUREMENTID \
                     from MEDIAN.MEASUREMENT m, \
                          MEDIAN.TIMESERIESPOINT tsp \
                     where m.FEATUREID = tsp.FEATUREID and \
                           tsp.FEATUREID IN ( ? )\
                    )\
               )\
          ) \
    ORDER BY p.GERMANNAME

timeseries_depth_height=SELECT DISTINCT \
        m.MEASUREMENTID KEY, \
        m.ZLOCATION VALUE \
    from MEDIAN.MEASUREMENT m \
    where m.MEASUREMENTID in \
          (SELECT DISTINCT \
              t_v.MEASUREMENTID \
          from MEDIAN.TIMESERIESVALUE t_v \
          where t_v.TIMESERIESID in \
              (SELECT DISTINCT \
                  t.TIMESERIESID \
              from MEDIAN.TIMESERIES t \
              where t.PARAMETERID in \
                  (SELECT DISTINCT \
                      p.PARAMETERID \
                  from MEDIAN.PARAMETER p \
                  where m.FEATUREID IN ( ? ) and \
                        p.PARAMETERID IN (?)\
                  )\
              )\
          )\
    ORDER BY m.ZLOCATION DESC

timeseries_interval=select min(tv.TIMEVALUE) MIN, \
        max(tv.TIMEVALUE) MAX \
    from MEDIAN.TIMESERIES t , \
         MEDIAN.TIMESERIESVALUE tv \
    where tv.TIMESERIESID = t.TIMESERIESID AND \
          t.PARAMETERID IN ( ? ) AND \
          tv.MEASUREMENTID IN ( ? )
          
timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, \
        tv.DATAVALUE YORDINATE, \
        t.PARAMETERID GROUP1, \
        tv.MEASUREMENTID GROUP2, \
        tv.TIMESERIESID GROUP3 \
    FROM MEDIAN.TIMESERIESVALUE tv, \
         MEDIAN.TIMESERIES t \
    WHERE tv.TIMESERIESID = t.TIMESERIESID AND \
          t.PARAMETERID IN ( ? ) AND \
          tv.MEASUREMENTID IN ( ? ) AND \
          tv.TIMEVALUE > TO_DATE ('?', 'YYYY.MM.DD HH24:MI:SS') AND \
          tv.TIMEVALUE < TO_DATE ('?', 'YYYY.MM.DD HH24:MI:SS') \
    ORDER BY tv.MEASUREMENTID , \
             tv.TIMESERIESID , \
             t.PARAMETERID , \
             tv.TIMEVALUE

#############################################
#############################################
##########   Zeitserie Mesh    ##############
#############################################
#############################################

timeseries_mesh = SELECT OBJECTID KEY, \
        m.NAME VALUE \
    FROM MEDIAN.MESH m \
    order by m.NAME
timeseries_meshpoint = SELECT FEATUREID KEY, \
        SHAPE VALUE \
    FROM MEDIAN.MESHPOINT mp , \
         MEDIAN.MESH m \
    WHERE m.OBJECTID = ? AND \
          mp.MESHID = m.MESHID AND \
          KPOSITION = 1 AND \
          rownum < 10

timeseries_meshpoint_depth = select mp.FEATUREID KEY, \
        -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE \
    from MEDIAN.MESHLAYER ml, \
         MEDIAN.MESHPOINT mp \
    where ml.KPOSITION = mp.KPOSITION and \
          ml.MESHID = mp.MESHID and \
          mp.FEATUREID in \
    ( select FEATUREID \
      from MEDIAN.MESHPOINT mp, \
           MEDIAN.MESH m \
      where m.OBJECTID = ? AND \
            mp.MESHID = m.MESHID  AND \
            IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
            JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
    order by ml.UPPERZLOCATION desc

timeseries_mesh_parameter=SELECT distinct \
        p.PARAMETERID KEY , \
        p.GERMANNAME VALUE \
    from MEDIAN.PARAMETER p, \
         MEDIAN.MESHSCALARVALUE msc, \
         MEDIAN.MESH m \
    where m.OBJECTID = ? AND \
          msc.PARTID = m.PARTIDMIN AND \
          msc.PARAMETERID = p.PARAMETERID \
    order by p.GERMANNAME
timeseries_mesh_interval=select /*+ parallel(TIMEVALUE,5) */ \
        min(TIMEVALUE) MIN, \
        max(TIMEVALUE) MAX \
    from MEDIAN.MESHSCALARVALUE msc , \
         MEDIAN.MESH m \
    where m.OBJECTID = ? AND \
          msc.PARTID >= m.PARTIDMIN AND \
          msc.PARTID <= m.PARTIDMAX

timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ \
        msv.TIMEVALUE XORDINATE, \
        msv.DATAVALUE YORDINATE, \
        msv.PARAMETERID GROUP1, \
        msv.FEATUREID GROUP2, \
        mp.FEATUREID GROUP3 \
    from MEDIAN.MESHSCALARVALUE msv , \
         MEDIAN.MESHPOINT mp, \
         MEDIAN.MESH m \
    where (m.OBJECTID = ? AND \
           msv.PARTID >= m.PARTIDMIN AND \
           msv.PARTID <= m.PARTIDMAX ) AND \
          msv.FEATUREID in ( ? ) AND \
          msv.PARAMETERID in ( ? ) AND \
          mp.FEATUREID = ? AND \
          msv.TIMEVALUE >= to_date('?', 'YYYY.MM.DD HH24:MI:SS') AND \
          msv.TIMEVALUE <= to_date('?', 'YYYY.MM.DD HH24:MI:SS') \
    order by msv.FEATUREID, \
             msv.PARAMETERID, \
             msv.TIMEVALUE

#############################################
#############################################
##########   Vertikalprofil    ##############
#############################################
#############################################

verticalprofile_point=SELECT DISTINCT \
           tsp.FEATUREID KEY, \
           tsp.NAME VALUE \
    FROM MEDIAN.TIMESERIESPOINT tsp, \
           MEDIAN.MEASUREMENT mmt \
    WHERE tsp.FEATUREID =  mmt.FEATUREID AND \
           mmt.SOURCEID = ? \
    order by tsp.name

verticalprofile_parameter=SELECT DISTINCT \
           p.PARAMETERID KEY, \
           p.GERMANNAME VALUE \
    from MEDIAN.PARAMETER p, \
           MEDIAN.TIMESERIES ts, \
           MEDIAN.TIMESERIESVALUE tsv, \
           MEDIAN.MEASUREMENT m \
    where ts.PARAMETERID = p.PARAMETERID and \
          ts.TIMESERIESID = tsv.TIMESERIESID and \
          m.MEASUREMENTID = tsv.MEASUREMENTID and \
          m.FEATUREID = ? \
    ORDER BY p.GERMANNAME

verticalprofile_date=select distinct \
           tsv.TIMEVALUE KEY, \
           tsv.TIMEVALUE VALUE \
    from MEDIAN.TIMESERIES ts , \
         MEDIAN.TIMESERIESVALUE tsv, \
         MEDIAN.MEASUREMENT m \
    where ts.TIMESERIESID = tsv.TIMESERIESID and \
          m.MEASUREMENTID = tsv.MEASUREMENTID and \
          m.FEATUREID = ? and \
          ts.PARAMETERID IN ( ? ) \
    order by tsv.TIMEVALUE 

verticalprofile_chart_data= SELECT m.ZLOCATION XORDINATE, \
           tsv.DATAVALUE YORDINATE, \
           ts.PARAMETERID GROUP1, \
           tsv.TIMEVALUE GROUP2, \
           1 GROUP3 \
    from MEDIAN.TIMESERIES ts, \
         MEDIAN.TIMESERIESVALUE tsv, \
         MEDIAN.MEASUREMENT m \
    where ts.TIMESERIESID = tsv.TIMESERIESID and \
          m.MEASUREMENTID = tsv.MEASUREMENTID and \
          m.FEATUREID = ? and \
          ts.PARAMETERID IN ( ? ) AND \
          tsv.TIMEVALUE IN (?) \
    ORDER BY tsv.TIMEVALUE , \
             tsv.TIMESERIESID , \
             ts.PARAMETERID , \
             tsv.TIMEVALUE
             
#############################################
#############################################
########## Vertikalprofil Mesh ##############
#############################################
#############################################

verticalprofile_mesh = SELECT OBJECTID KEY, \
        m.NAME VALUE \
    FROM MEDIAN.MESH m \
    order by m.NAME
    
verticalprofile_mesh_point = SELECT FEATUREID KEY, \
        SHAPE VALUE \
    FROM MEDIAN.MESHPOINT mp , \
         MEDIAN.MESH m \
    WHERE m.OBJECTID = ? AND \
          m.MESHID = mp.MESHID AND \
          mp.KPOSITION = 1 AND \
          rownum < 10
          
verticalprofile_mesh_parameter=SELECT distinct \
        p.PARAMETERID KEY , \
        p.GERMANNAME VALUE \
    from MEDIAN.PARAMETER p, \
         MEDIAN.MESHSCALARVALUE msc, \
         MEDIAN.MESH m  \
    where m.OBJECTID = ? AND \
          m.PARTIDMIN = msc.PARTID AND \
          msc.PARAMETERID = p.PARAMETERID \
    order by p.GERMANNAME
          
verticalprofile_mesh_date= select  distinct \
        msv.TIMEVALUE KEY, \
        msv.TIMEVALUE VALUE \
    from MEDIAN.MESHSCALARVALUE msv , \
         MEDIAN.MESH m \
    where m.OBJECTID = ?  AND \
          msv.PARTID >= m.PARTIDMIN AND \
          msv.PARTID <= m.PARTIDMAX \
    order by msv.TIMEVALUE

verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \
           msv.DATAVALUE YORDINATE, \
           msv.PARAMETERID GROUP1, \
           msv.TIMEVALUE GROUP2, \
           1 GROUP3 \
    from MEDIAN.MESHLAYER ml, \
         MEDIAN.MESHPOINT mp, \
         MEDIAN.MESH m, \
         MEDIAN.MESHSCALARVALUE msv \
    where msv.FEATUREID = mp. FEATUREID AND \
          ml.KPOSITION = mp.KPOSITION and \
          ml.MESHID = mp.MESHID and \
          m.MESHID = mp.MESHID AND \
          m.PARTIDMIN <= msv.PARTID AND \
          m.PARTIDMAX >= msv.PARTID AND \
          msv.PARAMETERID in (?) AND \
          msv.TIMEVALUE in (?) AND \
          m.OBJECTID = ? AND \
          mp.FEATUREID in \
            ( select FEATUREID \
              from MEDIAN.MESHPOINT mp, \
                   MEDIAN.MESH m \
              where m.OBJECTID = ? AND \
                    mp.MESHID = m.MESHID  AND \
                    IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
                    JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
   order by msv.PARAMETERID, \
            msv.TIMEVALUE, \
            ml.UPPERZLOCATION
            
#############################################
#############################################
##### Vertikalprofil InstantaneousPoint #####
#############################################
#############################################

verticalprofile_instantaneouspoint_series = SELECT S.SERIESID KEY, \
        S.DESCRIPTION VALUE \
    FROM MEDIAN.SERIES S

verticalprofile_instantaneouspoint_point = SELECT IP.FEATUREID KEY, \
        IP.SHAPE VALUE \
    FROM MEDIAN.INSTANTANEOUSPOINT IP \
    WHERE IP.POINTSPEC = 4 AND \
          IP.SERIESID = ?

verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
        P.PARAMETERID KEY , \
        P.GERMANNAME VALUE \
    FROM MEDIAN.PARAMETER P, \
         MEDIAN.INSTANTANEOUSPOINT IP, \
         MEDIAN.MEASUREMENT M, \
         MEDIAN.MEASUREDSCALARVALUE MSV \
    WHERE IP.FEATUREID = M.FEATUREID AND \
          M.MEASUREMENTID = MSV.MEASUREMENTID AND \
          MSV.PARAMETERID = P.PARAMETERID AND \
          IP.FEATUREID = ? \
    ORDER BY P.GERMANNAME

verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \
        MSV.DATAVALUE  YORDINATE, \
        MSV.PARAMETERID GROUP1, \
        IP.TIMEVALUE GROUP2, \
        1 GROUP3 \
    FROM MEDIAN.INSTANTANEOUSPOINT IP, \
         MEDIAN.MEASUREMENT M, \
         MEDIAN.MEASUREDSCALARVALUE MSV \
    WHERE IP.FEATUREID = M.FEATUREID AND \
          M.MEASUREMENTID = MSV.MEASUREMENTID AND \
          IP.FEATUREID = ? AND \
          MSV.PARAMETERID in (?) \
    ORDER BY IP.TIMEVALUE, \
          MSV.PARAMETERID, \
          M.ZLOCATION