nuBuilder Forum

Code: Select all

function getQueryString() {

    return "
    
        SELECT z.id,
               z.afdeling,
               z.status,
               z.startdatum,
               z.einddatum,
               z.ritme
        FROM
          (SELECT a.contract_id AS 'id',
                  l.name AS 'afdeling' ,
                  'titularis' AS 'status',
                  date_format(a.start, '%d-%m-%Y') AS 'startdatum',
                  date_format(a.eind, '%d-%m-%Y') AS 'einddatum',
                  sum(a.tewerkstelling) AS 'ritme'
           FROM titularis a
           INNER JOIN contracten c ON c.re_id = a.contract_id
           INNER JOIN list l ON l.id = c.re_afdeling
           WHERE a.medewerker = '#id#'
           GROUP BY a.contract_id
           UNION ALL SELECT a.vv_contract_id AS 'id',
                            l.name AS 'afdeling' ,
                            'vervanging' AS 'status',
                            date_format(a.vv_start, '%d-%m-%Y') AS 'startdatum',
                            date_format(a.vv_eind, '%d-%m-%Y') AS 'einddatum',
                            sum(a.vv_vte) AS 'ritme'
           FROM vervanging a
           INNER JOIN contracten c ON c.re_id = a.vv_contract_id
           INNER JOIN list l ON l.id = c.re_afdeling
           WHERE a.vv_medewerker = '#id#'
           GROUP BY 1)z
        ORDER BY z.einddatum
";

}

$t = nuRunQuery(getQueryString());
$total = db_num_rows($t);

if ($total > 0) {

    $result = db_fetch_all_array($t);

    $headers = sprintf('<thead><tr><th>%s</th></tr></thead>', join('</th><th>', array_keys($result[0])));
    $content = '';

    foreach ($result as $result) {
        $content .= sprintf('<tr><td>%s</td></tr>', join('</td><td>', array_values($result)));
    }
    printf('<table border="1">%s%s</table>', $headers, $content);

    echo '<script type="text/javascript">';
    echo 'parent.applyIframeStyle();';
    echo '</script>';
}