PHP query::count_qy_results方法代码示例

     }
 }
 //Prepare output
 $template_site->assign_vars(array('PG_TITLE' => $wpbi_dialog['page']['queries']['title'], 'PG_DESCRIPTION' => $wpbi_dialog['page']['queries']['description'], 'QY_NEW_CONNECTION' => $wpbi_dialog['field']['queries']['connection'], 'QY_NEW_NAME' => $wpbi_dialog['field']['queries']['name'], 'P_QY_NAME' => $wpbi_settings['parameter']['qy_name'], 'V_QY_NAME' => $_POST[$wpbi_settings['parameter']['qy_name']], 'QY_NEW_STMT' => $wpbi_dialog['field']['queries']['statement'], 'P_QY_STMT' => $wpbi_settings['parameter']['qy_statement'], 'V_QY_STMT' => $_POST[$wpbi_settings['parameter']['qy_statement']], 'QY_NEW_DB_OPTIONS' => $select_options, 'P_QY_DB' => $wpbi_settings['parameter']['qy_db'], 'P_QY_ACTION' => $wpbi_settings['parameter']['action'], 'V_ADD_ACTION' => $wpbi_settings['value']['add'], 'V_TEST_ACTION' => $wpbi_settings['value']['test'], 'LBL_BTN_ADD' => $wpbi_dialog['label']['button']['add'], 'LBL_BTN_TEST' => $wpbi_dialog['label']['button']['test']));
 //output page header and query form
 $template_site->pparse('header');
 $template_site->pparse('queries-new');
 //Output new query test result (if any)
 echo $test_output;
 //*** Query Manager ***
 //To be display only when the user is not testin a new query
 if ($_POST[$wpbi_settings['parameter']['action']] != 'test') {
     //Get saved queries
     $qy_queries = "\r\n\t\tSELECT `QUERY_ID`, `QUERY_NAME`, CONCAT(`DB_NAME`,'(',`DB_USER`,'@',`DB_HOST`,')') \"DATABASE\" , `QUERY_STATEMENT` \r\n\t\tFROM " . $wpbi_sql['tname']['queries'] . ", " . $wpbi_sql['tname']['databases'] . "\r\n\t\tWHERE `DATABASE_ID` = `DB_ID` order by `QUERY_NAME` asc";
     $query = new query($qy_queries, $wpdb, $wpbi_sql['tname']['vars']);
     $total_rows = $wpdb->get_results($query->count_qy_results(), 'ARRAY_N');
     $total_rows = intval($total_rows[0][0]);
     //Page navigation
     $pagination = new pagination();
     $pagination->set_pg_interval($wpbi_settings['parameter']['page-interval']);
     $pagination->set_pg_parameter('pg');
     $pagination->set_current_page(isset($_GET[$pagination->pg_parameter]) ? abs((int) $_GET[$pagination->pg_parameter]) : 1);
     $pagination->set_rows($total_rows);
     $pagination->set_css_class('widefat post fixed');
     $pagination->set_css_style('widefat-pagination.css');
     $pagination->set_pagination_tpl_path($wpbi_url['tpl']);
     $pagination->set_pagination_stats($wpbi_dialog['table']['pagination']['stats']);
     $pagination->set_paginate_links(paginate_links(array('base' => esc_url(add_query_arg($pagination->pg_parameter, '%#%')), 'format' => '', 'prev_text' => __($wpbi_dialog['table']['pagination']['prev_text']), 'next_text' => __($wpbi_dialog['table']['pagination']['next_text']), 'total' => ceil($pagination->rows / $pagination->pg_interval), 'current' => $pagination->current_page)));
     $pagination->initialize();
     echo $pagination->get_html();
     //END page navigation

function get_html_4_chart($id)
{
    global $wpdb, $qy_table_databases, $qy_table_queries, $wpbi_sql, $wpbi_settings, $wpbi_url, $wpbi_dialog, $lng, $language, $template_site;
    $template_site->set_filenames(array('chart' => $wpbi_url['tpl']['root-path'] . $wpbi_url['tpl']['nvd3chart']));
    $chart_id = $id;
    //id or key
    $search_by_key = false;
    if (!is_numeric($chart_id)) {
        if (strlen($chart_id) == 32 && strpos($chart_id, ' ') === false) {
            $search_by_key = true;
        } else {
            return '<p>Could not create table. Wrong ID provided.</p>';
        }
    }
    $test_output = '';
    //Store the test output
    //Get chart metadata
    $selected_charts = $chart_id;
    $vo_chart = new vo_chart(NULL);
    $vo_chart->set_chart_id($selected_charts);
    $vo_chart->set_chart_key($selected_charts);
    $dao_chart = new dao_chart($wpdb, $wpbi_sql['tname']['charts']);
    $vo_chart = $search_by_key ? $dao_chart->select_by_key($vo_chart) : $dao_chart->select($vo_chart);
    $vo_chart = $vo_chart[0];
    //Get cols metadata
    $vo_ch_cols = new vo_ch_cols();
    $vo_ch_cols->set_ch_id($vo_chart->chart_id);
    $dao_ch_cols = new dao_ch_cols($wpdb, $wpbi_sql['tname']['chart-cols']);
    $vo_ch_cols = $dao_ch_cols->select($vo_ch_cols);
    //Get selected query
    $dao_query = new dao_query($wpdb, $wpbi_sql['tname']['queries']);
    $tgt_query = new vo_query($vo_chart->query_id, NULL, NULL, NULL, NULL);
    $vo_query = $dao_query->select($tgt_query);
    $vo_query = $vo_query[0];
    //Select associated db
    $dao_database = new dao_database($wpdb, $wpbi_sql['tname']['databases']);
    $tgt_database = new vo_database($vo_query->db_id, NULL, NULL, NULL, NULL);
    $vo_database = $dao_database->select($tgt_database);
    $vo_database = $vo_database[0];
    //Create wpdb object and execute the query
    $my_test_db = new wpdb($vo_database->user, $vo_database->pass, $vo_database->name, $vo_database->host);
    $query = new query($vo_query->statement, $wpdb, $wpbi_sql['tname']['vars']);
    $total_rows = $my_test_db->get_results($query->count_qy_results(), 'ARRAY_N');
    if (count($total_rows) == 0) {
        return "<div style='padding:40px;'><h1 style='color: red;'>The query returned no data</h1>" . "<p>Please check the DB connectivity or your SQL query syntax</p></div>";
    }
    $total_rows = intval($total_rows[0][0]);
    //get query resultset
    $my_test_rows = $my_test_db->get_results($query->stmt, 'ARRAY_N');
    //get columns
    $x_label_cols = array();
    $color_cols = array();
    $tx_label_cols = array();
    $values_cols = array();
    $istime_cols = array();
    $stacked_label_cols = array();
    $stacked_label_cols_color = array();
    $col_idx = 0;
    foreach ($vo_ch_cols as $vo_ch_col) {
        array_push($tx_label_cols, $vo_ch_col->col_label);
        array_push($color_cols, $vo_ch_col->col_color);
        if ($vo_ch_col->is_label) {
            array_push($x_label_cols, $col_idx);
        }
        if ($vo_ch_col->is_value) {
            array_push($values_cols, $col_idx);
            array_push($stacked_label_cols, $vo_ch_col->col_label);
            array_push($stacked_label_cols_color, $vo_ch_col->col_color);
        }
        if ($vo_ch_col->is_time) {
            array_push($istime_cols, $col_idx);
        }
        $col_idx++;
    }
    //Create chart
    $wpbi_chart = new chart($my_test_rows);
    $wpbi_chart->set_name($vo_chart->chart_name);
    $wpbi_chart->set_tooltip($vo_chart->chart_tooltip);
    $wpbi_chart->set_width($vo_chart->chart_width . ($vo_chart->chart_width_percent ? '%' : ''));
    $wpbi_chart->set_height($vo_chart->chart_height . ($vo_chart->chart_height_percent ? '%' : ''));
    $wpbi_chart->set_bg_colour($vo_chart->chart_bg_color);
    $wpbi_chart->set_title($vo_chart->chart_title);
    $wpbi_chart->set_title_color($vo_chart->chart_title_color);
    $wpbi_chart->set_title_size($vo_chart->chart_title_size);
    $wpbi_chart->set_type($vo_chart->chart_type);
    $wpbi_chart->set_x_axis_step_percent($vo_chart->chart_x_grid_lines);
    $wpbi_chart->set_y_axis_step_percent($vo_chart->chart_y_grid_lines);
    $wpbi_chart->set_time_format($vo_chart->chart_time_format);
    //Get values, labels, colors
    $label_color = array();
    $stacked_label_color = array();
    $row_idx = 0;
    $label_tmp_x = "";
    foreach ($my_test_rows as $my_test_row) {
        for ($col_idx = 0; $col_idx < sizeof($my_test_row); $col_idx++) {
            //Colors
            $label_color[$tx_label_cols[$col_idx]] = $color_cols[$col_idx];
            if (in_array($col_idx, $values_cols)) {
                $stacked_label_color[$tx_label_cols[$col_idx]] = $color_cols[$col_idx];
            }
//.........这里部分代码省略.........