# sql-chart-builder/3.0.3/functions.php

SQL Chart Builder, version 3.0.3. 1,333 lines.

- Page: https://pluginprobe.com/plugins/sql-chart-builder/3.0.3/code/functions.php
- Raw: https://pluginprobe.com/plugins/sql-chart-builder/3.0.3/raw/functions.php
- Modified: 2026-09-12T08:54:46+00:00

Line numbers below start at 1. Link to a line or a range by appending a fragment to the
page URL, for example `https://pluginprobe.com/plugins/sql-chart-builder/3.0.3/code/functions.php#L10-L20`.

```php
<?php
if (!defined('ABSPATH')) {
    die;
}
//postinstall function
function guaven_sqlcharts_load_defaults()
{
    if (get_option("guaven_sqlcharts_already_installed_2") === false) {
        update_option("guaven_sqlcharts_already_installed_2", "1");
        guaven_sqlcharts_install_first_data();

    }
}

function guaven_sqlcharts_install_first_data()
{
    require_once(dirname(__FILE__) . "/initial_data.php");
    gvn_chart_sample_nonxml_data();
}


function guaven_sqlcharts_recommended(){
    if(!class_exists('WooCommerce'))return;
    $uid=(int)get_current_user_id();
    if(isset($_GET["gvnsql_dismiss_recommendation"])){
        update_option('gvnsql_dismiss_recommendation_'.$uid,1);
        return;
    }
    if(get_option('gvnsql_dismiss_recommendation_'.$uid)!='')return;
    return '<table class="gf-alert gf-alert-info" style="margin-top:20px">
            <tbody><tr><td style="width: auto;vertical-align: top;padding: 20px;">
            <h2>WooCommerce Search Engine – INSTANT, RELEVANT AND SMART Search Box</h2>
            <h3>Turn your website search into Smart Search which find products by price, SKU, attributes, meta data, categorys, tags etc. </h3>
            <p>“WooCommerce Search Engine” is a very powerful and easy to use WooCommerce Search Plugin which turns a simple search box of your WooCommerce Store to the powerful multifunctional magic box which helps you to sell more products. The plugin UI is compatible with ALL THEMES.</p>
            <a target="_blank" style="border:0px solid #6200ee;border-radius:0px;color:white;font-weight:bold;background: #6200ee;" class="button button-secondary"
            href="https://codecanyon.net/item/woocommerce-search-box/15685698">Get the Search Box </a>
            </td><td style="position:relative">
            <a href="'.admin_url().'/edit.php?post_type=gvn_schart&gvnsql_dismiss_recommendation=1'.'" style="position: absolute;right: 0;top: -15px;right: -10px;">{svg}
            </a>
            <img src="'.plugin_dir_url( __FILE__ ) . 'asset/img/recommended1.jpg" style="max-width: 430px;"></td></tr>
            </tbody></table>';
}



function guaven_sqlcharts_my_admin_notice()
{
    global $post;


    if(
        (!empty($_SERVER["REQUEST_URI"]) and strpos(sanitize_text_field(wp_unslash($_SERVER["REQUEST_URI"])),'post_type=gvn_schart')!==false)
        or
        (!empty($post) and $post->post_type == 'gvn_schart')
     ){
        echo str_replace('{svg}','<svg version="1.1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="11" height="14" viewBox="0 0 11 14">
            <path d="M10.141 10.328q0 0.312-0.219 0.531l-1.062 1.062q-0.219 0.219-0.531 0.219t-0.531-0.219l-2.297-2.297-2.297 2.297q-0.219 0.219-0.531 0.219t-0.531-0.219l-1.062-1.062q-0.219-0.219-0.219-0.531t0.219-0.531l2.297-2.297-2.297-2.297q-0.219-0.219-0.219-0.531t0.219-0.531l1.062-1.062q0.219-0.219 0.531-0.219t0.531 0.219l2.297 2.297 2.297-2.297q0.219-0.219 0.531-0.219t0.531 0.219l1.062 1.062q0.219 0.219 0.219 0.531t-0.219 0.531l-2.297 2.297 2.297 2.297q0.219 0.219 0.219 0.531z"></path>
            </svg>',wp_kses_post(guaven_sqlcharts_recommended(),[]));
     }



    if (!empty($post) and $post->post_type == 'gvn_schart'):
        if (!current_user_can('manage_options')) {
            echo '<br><br>
  <div class="updated gf-alert gf-alert-danger">Only administrators can manage this page</div>';
            die();
        }
        echo '<div class="updated gf-alert gf-alert-info">';
        if (empty($_GET["post"]) && strpos(sanitize_text_field(wp_unslash($_SERVER["REQUEST_URI"])), "post-new") === false) {
            $gf_message = __(
                'Use <b>Add new</b> button above to create a new SQL report. And click on any existing rule names below to manage them.',
                'guaven_sqlcharts'
            );
        } else {
            $gf_message = __(
                '1. Give any name to your report.<br>
                2. Pick a chart type, build your SQL query with the visual builder (or type it — autocomplete will help), map the X/Y columns and press Publish/Update.<br>
                3. After update, you will see the shortcode of this chart at the bottom of the page. You can use that shortcode anywhere in your website: in pages, posts, widgets, etc.',
                'guaven_sqlcharts'
            );
        }

        echo '<div style="float:left;max-width:calc(100% - 345px)">';
        echo wp_kses_post( $gf_message );
        echo '</div>';

        echo '<div style="float: right;
    margin-top: 0px;
    padding-top: 0px;"><a target="_blank" style="text-align:center;border:0px solid #6200ee;border-radius:0px;color:white;font-weight:bold;background: #6200ee;"
    class="button button-secondary" href="https://guaven.com/contact/solution-request/">Get Premium Support </a>
    <span style="line-height: 30px;padding: 0 5px;">OR</span>
    <a target="_blank" style="text-align:center;border:0px solid #26b286;border-radius:0px;color:white;font-weight:bold;background: #26b286;"
    class="button button-secondary" href="https://guaven.com/service/small-thankyou-premium-support-service/">Make a Small Donation</a>
    </div> </div>';
    endif;
}
add_action('admin_notices', 'guaven_sqlcharts_my_admin_notice');

function guaven_sqlcharts_onboarding_notice(){
    if((function_exists('wp_doing_ajax') and wp_doing_ajax()) or get_option('guaven_sqlcharts_onboarding_notice_dismissed') == 1)
        return;

    printf('
        <div class="guaven-sqlcharts-notice notice notice-success is-dismissible" data-notice="onboarding_notice">
            <p>Welcome aboard on MySQL Charts!</p>
        </div>'
    );
}
add_action('admin_notices', 'guaven_sqlcharts_onboarding_notice');

function guaven_sqlcharts_onboarding_notice_dismissed(){
    check_ajax_referer('notice_dismissed', 'nonce');

    if(empty($_POST['type']))return;
    switch ($_POST['type']){
        case 'onboarding_notice':
            update_option('guaven_sqlcharts_onboarding_notice_dismissed', 1);
            break;
        }
}
add_action('wp_ajax_guaven_sqlcharts_onboarding_notice_dismissed', 'guaven_sqlcharts_onboarding_notice_dismissed');

function guaven_sqlcharts_enqueue_chart()
{
    wp_enqueue_script('guaven_sqlcharts_chartjs', plugins_url('asset/chart.umd.min.js', __FILE__),array('jquery'),GVNSQLCHARTS_VERSION,false);
    wp_enqueue_script('guaven_sqlcharts_datepicker', plugins_url('asset/datepicker.min.js', __FILE__),array('jquery'),GVNSQLCHARTS_VERSION,false);
    wp_enqueue_script('guaven_sqlcharts_front', plugins_url('asset/front.js', __FILE__),array('jquery','guaven_sqlcharts_chartjs','guaven_sqlcharts_datepicker'),GVNSQLCHARTS_VERSION,false);
    wp_localize_script('guaven_sqlcharts_front', 'guaven_sqlcharts_notice_dismissed', array(
        'action' => 'guaven_sqlcharts_onboarding_notice_dismissed',
        'nonce' => wp_create_nonce('notice_dismissed')
    ));

}
add_action('wp_enqueue_scripts', 'guaven_sqlcharts_enqueue_chart');
add_action('admin_enqueue_scripts', 'guaven_sqlcharts_enqueue_chart');

function guaven_sqlcharts_enqueue_main_style()
{
    wp_enqueue_style('guaven_sqlcharts_main_style', plugins_url('asset/guaven_sqlcharts.css', __FILE__),array(),GVNSQLCHARTS_VERSION);
}
add_action('wp_enqueue_scripts', 'guaven_sqlcharts_enqueue_main_style');
add_action('admin_enqueue_scripts', 'guaven_sqlcharts_enqueue_main_style');

// admin-only assets: SQL builder, autocomplete, new metabox UI
function guaven_sqlcharts_admin_assets($hook)
{
    if (!in_array($hook, array('post.php', 'post-new.php'))) return;
    $screen = function_exists('get_current_screen') ? get_current_screen() : null;
    if (empty($screen->post_type) or $screen->post_type != 'gvn_schart') return;
    if (!current_user_can('manage_options')) return;

    wp_enqueue_style('guaven_sqlcharts_admin_style', plugins_url('asset/admin.css', __FILE__), array(), GVNSQLCHARTS_VERSION);
    wp_enqueue_script('guaven_sqlcharts_admin', plugins_url('asset/admin.js', __FILE__), array('jquery'), GVNSQLCHARTS_VERSION, true);

    global $wpdb;
    $tables = $wpdb->get_col('SHOW TABLES');
    if (!is_array($tables)) $tables = array();
    wp_localize_script('guaven_sqlcharts_admin', 'gvnSqlBuilder', array(
        'ajaxurl' => admin_url('admin-ajax.php'),
        'nonce'   => wp_create_nonce('gvnsql_schema'),
        'tables'  => array_values($tables),
        'prefix'  => $wpdb->prefix,
    ));
}
add_action('admin_enqueue_scripts', 'guaven_sqlcharts_admin_assets');

// returns column names of one table for the live SQL builder/autocomplete
function guaven_sqlcharts_ajax_columns()
{
    check_ajax_referer('gvnsql_schema', 'nonce');
    if (!current_user_can('manage_options')) wp_send_json_error('forbidden', 403);
    global $wpdb;
    $table = isset($_POST['table']) ? sanitize_text_field(wp_unslash($_POST['table'])) : '';
    $tables = $wpdb->get_col('SHOW TABLES');
    if (!is_array($tables) or !in_array($table, $tables, true)) wp_send_json_error('unknown table', 400);
    $cols = $wpdb->get_results('SHOW COLUMNS FROM `' . str_replace('`', '', $table) . '`');
    $out = array();
    if (is_array($cols)) {
        foreach ($cols as $col) {
            $out[] = array('name' => $col->Field, 'type' => $col->Type);
        }
    }
    wp_send_json_success($out);
}
add_action('wp_ajax_gvnsql_get_columns', 'guaven_sqlcharts_ajax_columns');


function guaven_sqlcharts_isJson($string)
{
    json_decode($string);
    return (json_last_error() == JSON_ERROR_NONE);
}

add_action('init', 'guaven_sqlcharts_register_post');
function guaven_sqlcharts_register_post()
{
    register_post_type('gvn_schart', array(
        'labels' => array(
            'name' => __('My SQL Charts','guaven_sqlcharts'),
            'singular_name' => __('SQL Chart','guaven_sqlcharts'),
            'menu_name' => __('My SQL Charts','guaven_sqlcharts'),
            'add_new' => __('Add Chart','guaven_sqlcharts'),
            'add_new_item' => __('Add New Chart','guaven_sqlcharts'),
            'edit_item' => __('Edit Chart','guaven_sqlcharts'),
            'new_item' => __('New Chart','guaven_sqlcharts'),
            'view_item' => __('View Chart','guaven_sqlcharts'),
            'view_items' => __('View Charts','guaven_sqlcharts'),
            'search_items' => __('Search Charts','guaven_sqlcharts'),
            'not_found' => __('No charts found','guaven_sqlcharts'),
            'not_found_in_trash' => __('No charts found in Trash','guaven_sqlcharts'),
            'all_items' => __('All Charts','guaven_sqlcharts'),
            'archives' => __('Chart Archives','guaven_sqlcharts'),
            'attributes' => __('Chart Attributes','guaven_sqlcharts'),
            'insert_into_item' => __('Insert into chart','guaven_sqlcharts'),
            'uploaded_to_this_item' => __('Uploaded to this chart','guaven_sqlcharts'),
            'filter_items_list' => __('Filter charts list','guaven_sqlcharts'),
            'items_list_navigation' => __('Charts list navigation','guaven_sqlcharts'),
            'items_list' => __('Charts list','guaven_sqlcharts'),
            'item_published' => __('Chart published.','guaven_sqlcharts'),
            'item_published_privately' => __('Chart published privately.','guaven_sqlcharts'),
            'item_reverted_to_draft' => __('Chart reverted to draft.','guaven_sqlcharts'),
            'item_scheduled' => __('Chart scheduled.','guaven_sqlcharts'),
            'item_updated' => __('Chart updated.','guaven_sqlcharts'),
        ),

        'public' => true,
        'menu_icon' => 'dashicons-chart-pie',
        'supports' => array(
            'title',
            'postmeta'
        ),
        'register_meta_box_cb' => 'guaven_sqlcharts_metabox_area'
    ));

    guaven_sqlcharts_load_defaults();
}

// "Add title" placeholder on the chart edit screen
add_filter('enter_title_here', function ($title, $post) {
    if (!empty($post) and $post->post_type == 'gvn_schart') return __('Chart name', 'guaven_sqlcharts');
    return $title;
}, 10, 2);

add_action('admin_footer', 'guaven_sqlcharts_admin_front');


function guaven_sqlcharts_admin_front()
{
    global $post;
    if (!empty($post) and $post->post_type == 'gvn_schart') {
?>
<style type="text/css">#normal-sortables{display: none}</style>
  <?php
    }
}

// metabox for editor
function guaven_sqlcharts_metabox_area()
{
    add_meta_box('guaven_sqlcharts_metabox', 'Chart Builder', 'guaven_sqlcharts_metabox', 'gvn_schart', 'advanced', 'default');
}

function guaven_sqlcharts_metabox()
{
    require_once(dirname(__FILE__) . "/admin_metabox.php");
}

/**
 * Catalog of all supported chart types: label, group, per-type usage guide and example query.
 * Used by the admin UI to render the type cards and the contextual guides.
 */
function guaven_sqlcharts_type_catalog()
{
    global $wpdb;
    $p = $wpdb->posts;
    $u = $wpdb->users;
    return array(
        'pie_l' => array(
            'label' => 'Pie',
            'group' => 'Circular',
            'guide' => 'Best for showing how a total splits into a few parts (shares/percentages). Use one query that returns a label column (X) and a numeric value column (Y). Keep it under ~8 slices for readability.',
            'example_sql' => "select count(*) postcount, SUBSTR(post_date,1,4) yearnum from $p group by yearnum order by yearnum asc limit 10",
            'example_x' => 'yearnum', 'example_y' => 'postcount',
        ),
        'donut_l' => array(
            'label' => 'Doughnut',
            'group' => 'Circular',
            'guide' => 'Same as Pie but with a hole in the middle — slightly easier to compare slice sizes. One query: label column (X) + numeric column (Y).',
            'example_sql' => "select count(*) postcount, post_type from $p group by post_type order by postcount desc limit 8",
            'example_x' => 'post_type', 'example_y' => 'postcount',
        ),
        'polar_l' => array(
            'label' => 'Polar Area',
            'group' => 'Circular',
            'guide' => 'Like a pie, but every slice has the same angle and the value controls the radius. Good for cyclic data (months, weekdays). One query: label (X) + numeric value (Y).',
            'example_sql' => "select count(*) postcount, SUBSTRING(post_date,6,2) monthnum from $p group by monthnum order by monthnum",
            'example_x' => 'monthnum', 'example_y' => 'postcount',
        ),
        'radar_l' => array(
            'label' => 'Radar',
            'group' => 'Circular',
            'guide' => 'Compares one or more series across several categories arranged in a circle. Great for profiles/ratings. Use one query per series, separated with ";".',
            'example_sql' => "select count(*) postcount, SUBSTRING(post_date,6,2) monthnum from $p where post_type=\"post\" group by monthnum order by monthnum",
            'example_x' => 'monthnum', 'example_y' => 'postcount',
        ),
        'line_l' => array(
            'label' => 'Line',
            'group' => 'Line',
            'guide' => 'The classic choice for trends over time (per day/month/year). X should be an ordered value like a date. Add more queries separated with ";" for comparison lines.',
            'example_sql' => "select count(*) postcount, SUBSTRING(post_date,1,7) monthandyear from $p group by monthandyear order by monthandyear",
            'example_x' => 'monthandyear', 'example_y' => 'postcount',
        ),
        'area_l' => array(
            'label' => 'Area',
            'group' => 'Line',
            'guide' => 'A line chart with the region under the line filled — emphasizes volume/magnitude of a trend. Works well with 2 queries (";" separated) to compare filled regions.',
            'example_sql' => "select count(*) postcount, SUBSTRING(post_date,1,7) monthandyear from $p where post_type=\"post\" group by monthandyear order by monthandyear;\nselect count(*) postcount, SUBSTRING(post_date,1,7) monthandyear from $p where post_type!=\"post\" group by monthandyear order by monthandyear",
            'example_x' => 'monthandyear', 'example_y' => 'Posts;Other content',
        ),
        'steppedline_l' => array(
            'label' => 'Stepped Line',
            'group' => 'Line',
            'guide' => 'Line chart that moves in steps instead of slopes — perfect for values that change at discrete moments (prices, stock level, settings history).',
            'example_sql' => "select count(*) postcount, SUBSTRING(post_date,1,4) yearnum from $p group by yearnum order by yearnum",
            'example_x' => 'yearnum', 'example_y' => 'postcount',
        ),
        'bar_l' => array(
            'label' => 'Bar',
            'group' => 'Bar',
            'guide' => 'Compares values across categories with vertical bars. One query: category (X) + numeric value (Y). Multiple ";" separated queries become grouped/stacked bars.',
            'example_sql' => "select count(*) postcount, SUBSTRING(post_date,1,7) monthandyear from $p group by monthandyear order by monthandyear",
            'example_x' => 'monthandyear', 'example_y' => 'postcount',
        ),
        'horizontalbar_l' => array(
            'label' => 'Horizontal Bar',
            'group' => 'Bar',
            'guide' => 'Bar chart rotated 90° — the best pick when category names are long (user names, product titles).',
            'example_sql' => "select count(*) as postcount, b.display_name as dname from $p a inner join $u b ON a.post_author=b.ID where a.post_status=\"publish\" group by a.post_author order by postcount desc limit 10",
            'example_x' => 'dname', 'example_y' => 'postcount',
        ),
        'stackedbar_l' => array(
            'label' => 'Stacked Bar',
            'group' => 'Bar',
            'guide' => 'Shows how each category total is composed of parts. Use 2+ queries separated with ";" — each query becomes one segment color of the stack.',
            'example_sql' => "select count(*) postcount, SUBSTRING(post_date,1,4) yearnum from $p where post_type=\"post\" group by yearnum order by yearnum;\nselect count(*) postcount, SUBSTRING(post_date,1,4) yearnum from $p where post_type!=\"post\" group by yearnum order by yearnum",
            'example_x' => 'yearnum', 'example_y' => 'Posts;Other content',
        ),
        'scatter_l' => array(
            'label' => 'Scatter',
            'group' => 'Other',
            'guide' => 'Plots points on numeric X/Y axes to reveal correlation between two numbers. Both X and Y columns must be numeric (e.g. comment_count vs menu_order).',
            'example_sql' => "select comment_count ccount, ID from $p where post_status=\"publish\" order by ID limit 100",
            'example_x' => 'ID', 'example_y' => 'ccount',
        ),
    );
}

/**
 * Maps deprecated Google-Chart era type slugs to their Chart.js equivalents.
 * Old charts keep working without any manual migration; when the post is re-saved
 * from the new UI the normalized value is stored automatically.
 */
function guaven_sqlcharts_normalize_type($type)
{
    $map = array(
        'pie'    => 'pie_l',
        '3dpie'  => 'pie_l',
        'column' => 'bar_l',
        'bar'    => 'horizontalbar_l',
        'area'   => 'area_l',
    );
    return isset($map[$type]) ? $map[$type] : $type;
}

function guaven_gutenberg_wrapper($atts){
    if(isset($atts['sqlcharts_inserted_script'])){
        global $sqlcharts_inserted_script;
        $sqlcharts_inserted_script = $atts['sqlcharts_inserted_script'];
    }

    $post = get_post($atts['chart_id']);
    if( ! isset($atts['chart_id']) or !isset($post) or $post->post_type != 'gvn_schart'){

        return "Invalid id";
    }

    return guaven_sqlcharts_local_shortcode(array('id' => $atts['chart_id'])); // temporary explicit value
}
function guaven_register_gutenberg_blocks()
{
    wp_register_script(
		'gvn_gutenberg_charts',
		plugins_url( 'asset/guaven_gutenberg_charts.js', __FILE__ ),
		array( 'wp-blocks', 'wp-i18n', 'wp-element', 'wp-server-side-render' ),
		GVNSQLCHARTS_VERSION.'_'.filemtime( plugin_dir_path( __FILE__ ) . 'asset/guaven_gutenberg_charts.js'),
        false
    );
    wp_localize_script('gvn_gutenberg_charts', 'guaven', array(
        'description' => 'Add My SQL Chart to your post',
    ));

	register_block_type( 'guaven-sqlcharts/gvn-chart-gutenberg', array(
        'editor_script' => 'gvn_gutenberg_charts',
        'render_callback' => 'guaven_gutenberg_wrapper',
        'attributes' => array(
            'chart_id' => array(
                'type' => 'string',
                'default' => null
            ),
            'sqlcharts_inserted_script' => array(
                'type' => 'number',
                'default' => null
            )
        )
    ));
}
add_action('init', 'guaven_register_gutenberg_blocks');



function guaven_sqlcharts_save_metabox_area($post_id, $post)
{
    if (!isset($_POST['meta_box_nonce_field']) or !wp_verify_nonce($_POST['meta_box_nonce_field'], 'meta_box_nonce_action')) {
        return $post->ID;
    }
    $fields = array(
        "guaven_sqlcharts_chartheight",
        "guaven_sqlcharts_chartwidth",
        "guaven_sqlcharts_graphtype",
        "guaven_sqlcharts_xarg_s",
        "guaven_sqlcharts_xarg_l",
        "guaven_sqlcharts_yarg_s",
        "guaven_sqlcharts_yarg_l",
        "guaven_sqlcharts_tablepart",
        "guaven_sqlcharts_variables",
        "guaven_sqlcharts_formpartrole",
        "guaven_sqlcharts_formpartbutton",
        "guaven_sqlcharts_dbhost",
        "guaven_sqlcharts_dblogin",
        "guaven_sqlcharts_dbname",
        "guaven_sqlcharts_colors",
        "guaven_sqlcharts_begin_with_0_x",
        "guaven_sqlcharts_begin_with_0_y",
        "guaven_sqlcharts_round_y_values",
        "guaven_sqlcharts_legend_position",
        "guaven_sqlcharts_nostacked",
        "guaven_sqlcharts_forcetooltips",
        "guaven_sqlcharts_timeaxis"
    );
    foreach ($fields as $key => $value) {
        if(isset($_POST[$value]))$newval=esc_attr($_POST[$value]);
        else $newval='';

        update_post_meta($post->ID, $value, $newval);
    }
    if(!empty($_POST["guaven_sqlcharts_dbpass"])){
        $encpass=guaven_sqlcharts_encrypt_decrypt('encrypt',$_POST["guaven_sqlcharts_dbpass"]);
        update_post_meta($post->ID, 'guaven_sqlcharts_dbpass', ['encrypted',$encpass]);
    }
    // Store the SQL as typed. Do not HTML-encode it and do not rewrite quotes:
    // the editor escapes it on output and the front end decodes entities before running it.
    $sql_code = isset($_POST['guaven_sqlcharts_code']) ? wp_check_invalid_utf8(wp_unslash($_POST['guaven_sqlcharts_code'])) : '';
    update_post_meta($post->ID, 'guaven_sqlcharts_code', $sql_code);
    // Flag that this chart stores raw SQL. Charts without the flag were saved by
    // versions before 3.0.1, which HTML-encoded the query, and still need decoding.
    update_post_meta($post->ID, 'guaven_sqlcharts_code_raw', 1);
}

// Returns the stored SQL query exactly as the user typed it.
function guaven_sqlcharts_get_code($post_id)
{
    $sql = get_post_meta($post_id, 'guaven_sqlcharts_code', true);
    if (get_post_meta($post_id, 'guaven_sqlcharts_code_raw', true) != 1) {
        $sql = html_entity_decode($sql, ENT_QUOTES, 'UTF-8');
    }
    return $sql;
}
add_action('save_post', 'guaven_sqlcharts_save_metabox_area', 1, 2);
// save the custom fields



function gvn_chart_check_sql_query($sql)
{
    // case-insensitive, word-boundary check: only read-only SELECT queries are allowed
    $pattern = '/\b(delete|update|insert|replace|drop|truncate|alter|create|rename|grant|revoke|call|handler|load\s+data|load_file|outfile|dumpfile)\b/i';
    return preg_match($pattern, $sql) ? 1 : 0;
}

function guaven_get_labels_and_values($id, $fvs)
{
    $values   = array();
    $labels   = array();
    $xarg_s   = get_post_meta($id, 'guaven_sqlcharts_xarg_s', true);
    $yarg_s   = get_post_meta($id, 'guaven_sqlcharts_yarg_s', true);
    // labels are saved through esc_attr, so "&" is stored as "&amp;"; decode before splitting on ";"
    // or the entity's own ";" would be taken as a series separator
    $xarg_l   = html_entity_decode((string) get_post_meta($id, 'guaven_sqlcharts_xarg_l', true), ENT_QUOTES, 'UTF-8');
    $yarg_l   = html_entity_decode((string) get_post_meta($id, 'guaven_sqlcharts_yarg_l', true), ENT_QUOTES, 'UTF-8');
    foreach ($fvs as $key => $value) {
        $values[$value->$xarg_s] = $value->$yarg_s;
        $labels[$value->$xarg_s] = '"' . $value->$xarg_s . '"';
    }
    return array(
        $labels,
        $values,
        explode(";", $yarg_l),
        explode(";", $xarg_l)
    );
}

function guaven_sqlcharts_print_chart_js($print_data)
{
    extract($print_data);
    $tip_g = guaven_sqlcharts_normalize_type($tip_g);

    switch ($tip_g) {
        case 'line_l':
            guaven_sqlcharts_linedata($title, $labels, $values, $ylabel, 'false', $pid);
            break;
        case 'area_l':
            guaven_sqlcharts_linedata($title, $labels, $values, $ylabel, 'true', $pid);
            break;
        case 'steppedline_l':
            guaven_sqlcharts_linedata($title, $labels, $values, $ylabel, 'false', $pid, 'line', true);
            break;
        case 'radar_l':
            guaven_sqlcharts_linedata($title, $labels, $values, $ylabel, 'radarfill', $pid, 'radar');
            break;
        case 'pie_l':
            guaven_sqlcharts_piedata($title, $labels, $values, $ylabel, $pid);
            break;
        case 'donut_l':
            guaven_sqlcharts_piedata($title, $labels, $values, $ylabel, $pid, 'doughnut');
            break;
        case 'polar_l':
            guaven_sqlcharts_piedata($title, $labels, $values, $ylabel, $pid, 'polarArea');
            break;
        case 'bar_l':
            guaven_sqlcharts_bardata($title, $labels, $values, $ylabel, 'bar', $pid);
            break;
        case 'horizontalbar_l':
            guaven_sqlcharts_bardata($title, $labels, $values, $ylabel, 'horizontalBar', $pid);
            break;
        case 'stackedbar_l':
            guaven_sqlcharts_bardata($title, $labels, $values, $ylabel, 'stackedBar', $pid);
            break;
        case 'scatter_l':
            guaven_sqlcharts_scatterdata($title, $labels, $values, $ylabel, $pid);
            break;
        case 'custom':
            guaven_sqlcharts_custom($title, $labels, $values, $ylabel, $pid);
            break;
    }
}

function guaven_sqlcharts_custom($title, $labels, $values, $ylabel, $pid){
    do_action('guaven_sqlcharts_custom',$title, $labels, $values, $ylabel, $pid);
}

function gvn_chart_put_variables($sql,$pid){
  $sql_initial=$sql;


  $default_tag_keys=['{current_user_id}','{current_user_login}','{current_user_email}','{current_user_display_name}'];
  if(is_user_logged_in(  )){
    $currentuser=wp_get_current_user();
    $default_tag_values=[$currentuser->ID,$currentuser->user_login,$currentuser->user_email,$currentuser->user_display_name];
  }
  else {
    $default_tag_values='';
  }
  $sql_initial=str_replace($default_tag_keys,$default_tag_values,$sql_initial);

  $variables_raw=get_post_meta($pid,'guaven_sqlcharts_variables',true);
  $variables_arr=explode("|",$variables_raw);
  foreach($variables_arr as $varfield){
    $varfield_arr=explode("~",$varfield);
    if (count($varfield_arr)<3) continue;
    $varfield_arr=array_map("trim",$varfield_arr);
    if (!empty($_GET[$varfield_arr[0]])) {
      // User-supplied input: no () bypass allowed — sanitize strictly
      $varreplacement = sanitize_text_field(wp_unslash($_GET[$varfield_arr[0]]));
      if (is_numeric($varreplacement)) {
        $varreplacement = $varreplacement + 0;
      } else {
        $varreplacement = '"' . esc_sql($varreplacement) . '"';
      }
    } else {
      // Admin-configured default value: allow () for SQL functions (e.g. NOW())
      $varreplacement = $varfield_arr[1];
      if (!is_numeric($varreplacement) && strpos($varreplacement,'()')===false) {
        $varreplacement = '"' . esc_sql($varreplacement) . '"';
      }
    }

    $sql_initial=str_replace('{'.$varfield_arr[0].'}',$varreplacement,$sql_initial);
  }
  return $sql_initial;
}

function gvn_chart_top_form($atts){
  if (get_post_meta($atts["id"],'guaven_sqlcharts_formpartrole',true)!='' and !is_user_logged_in()) return;
  $topform='';$dateexists=false;
  $variables_raw=get_post_meta($atts['id'],'guaven_sqlcharts_variables',true);
  $variables_raw=explode("|",$variables_raw);
  foreach ($variables_raw as $vrow){
    $vrow_arr=explode("~",$vrow);
    $vrow_arr=array_map("trim",$vrow_arr);
    if (empty($vrow_arr[3])) continue;
    $gvalue=!empty($_GET[$vrow_arr[0]])?esc_attr(urldecode($_GET[$vrow_arr[0]])):'';
    $dvalue=(strpos($vrow_arr[1],'()')===false)?esc_attr($vrow_arr[1]):'';
    if ($vrow_arr[3]=='date') {
      $dateexists=true;
      $topform.= '<span class="gvn-filter-field"><label>'.$vrow_arr[2].'</label> <input class="gws_datepicker" autocomplete="off" type="text"
      value="'.$gvalue.'"
      data-toggle="datepicker" name="'.$vrow_arr[0].'" placeholder="'.$dvalue.'"></span>
      ';}
    else {
      $topform.= '<span class="gvn-filter-field"><label>'.$vrow_arr[2].'</label> <input autocomplete="off"
      type="'.$vrow_arr[3].'"
      value="'.$gvalue.'"    name="'.$vrow_arr[0].'" placeholder="'.$dvalue.'"></span>
      ';
    }
  }
  if (!empty($topform)) {
    $allowed_html = array(
        'form' => array(
            'method' => array(),
            'action' => array(),
            'class' => array()
        ),
        'input' => array(
            'type' => array(),
            'value' => array(),
            'name' => array(),
            'class' => array(),
            'data-toggle'=>array(),
            'placeholder'=>array(),
            'autocomplete'=>array(),
            'style'=>[]
        ),
        'span' => array('class' => array()),
        'label' => array(),
    );

    $submit_button_value = get_post_meta($atts['id'], 'guaven_sqlcharts_formpartbutton', true) != ''
        ? esc_attr(get_post_meta($atts['id'], 'guaven_sqlcharts_formpartbutton', true))
        : 'OK';

    $topform = '<form method="get" action="" class="guaven_sqlcharts_form">' . $topform . '
        <input type="submit" value="' . $submit_button_value . '"></form>';

    echo wp_kses($topform, $allowed_html);
  }
}


function guaven_sqlcharts_encrypt_decrypt($action, $string)
{
    $output = false;
    $encrypt_method = "AES-256-CBC";
    $secret_key = 'GWSCHARTPL2022.2016.';
    $secret_iv = 'GWSCHARTPL2016.2022';
    $key = hash('sha256', $secret_key);
    $iv = substr(hash('sha256', $secret_iv), 0, 16);
    if ( $action == 'encrypt' ) {
        $output = openssl_encrypt($string, $encrypt_method, $key, 0, $iv);
        $output = base64_encode($output);
    } else if( $action == 'decrypt' ) {
        $output = openssl_decrypt(base64_decode($string), $encrypt_method, $key, 0, $iv);
    }
    return $output;
}

function guaven_sqlcharts_local_shortcode($atts) {
    if(empty($atts['id']))return 'ID is missing.';
    $atts['id']=intval($atts['id']);
    $remote_host=get_post_meta($atts['id'], 'guaven_sqlcharts_dbhost', true);
    if ($remote_host!=''){
      $remote_db=get_post_meta($atts['id'], 'guaven_sqlcharts_dbname', true);
      $remote_login=get_post_meta($atts['id'], 'guaven_sqlcharts_dblogin', true);
      $remote_pass=get_post_meta($atts['id'], 'guaven_sqlcharts_dbpass', true);
      if(is_array($remote_pass)){
        $remote_pass=guaven_sqlcharts_encrypt_decrypt('decrypt',$remote_pass[1]);
      }
      $wpdb=new wpdb($remote_login,$remote_pass,$remote_db,$remote_host);
    }
    else {
      global $wpdb;
    }

    $GLOBALS["guaven_sqlcharts_atts"]=$atts;

    $sql           = guaven_sqlcharts_get_code($atts['id']);
    if(empty($sql))return 'SQL query is missing.';
    $sql=gvn_chart_put_variables($sql,$atts['id']);


    $sql=apply_filters('guaven_sqlcharts_rendered_sql',$sql,$atts);

    $blacklister_f = gvn_chart_check_sql_query($sql);
    if ($blacklister_f == 1)return 'You given SQL code contains forbidden commands. Remember that you should only use SELECT queries';
    $tip_g = guaven_sqlcharts_normalize_type(get_post_meta($atts['id'], 'guaven_sqlcharts_graphtype', true));

    // {arg1}..{arg19} come from shortcode attributes: [gvn_schart_2 id="1" arg1="41"].
    // Substituted directly (not via wpdb::prepare) so the same tag may appear any number of times,
    // e.g. in every query of a ";"-separated comparison chart. Numbers are inserted as-is, anything
    // else is escaped and quoted; a tag already wrapped in quotes ('{arg1}') is not double-quoted.
    for($i=1;$i<20;$i++){
        $tag = '{arg'.$i.'}';
        if (strpos($sql, $tag) === false) continue;
        $replacearg = !empty($atts['arg'.$i]) ? $atts['arg'.$i] : 0;
        if (is_numeric($replacearg)) $replacearg = $replacearg + 0;
        else $replacearg = "'" . esc_sql($replacearg) . "'";
        $sql = str_replace(array("'".$tag."'", '"'.$tag.'"', $tag), $replacearg, $sql);
    }

    $sql_split         = explode(';', $sql);
    $labels_and_values = array();
    $labels = $values = $ylabel = $xlabel = array();
    $post_g            = get_post($atts['id']);

    global $sqlcharts_inserted_script;
    ob_start();
    for ($i = 0; $i < count($sql_split); $i++) {
        if (!empty($sql_split[$i])) {

            $fvs = $wpdb->get_results($sql_split[$i]);
            if (strpos($_SERVER["REQUEST_URI"],'wp-admin')!==false){
              $wpdb->show_errors();
              ob_start();
              $wpdb->print_error();
              $printerror = ob_get_clean();
              if ($printerror != '' and strpos($printerror, "[]") === false){
                  ob_end_clean();
                  return $printerror;
              }
              elseif (empty($fvs)){
                  ob_end_clean();
                  return 'Your SQL returnes empty data, please recheck your SQL query above';
              }
            }

            if (empty($sqlcharts_inserted_script))
                $sqlcharts_inserted_script = 1;
                $labels_and_values[$i] = guaven_get_labels_and_values($atts['id'], $fvs);
                $labels[$i]            = $labels_and_values[$i][0];
                $values[$i]            = $labels_and_values[$i][1];
                $ylabel[$i]            = !empty($labels_and_values[$i][2][$i]) ? $labels_and_values[$i][2][$i] : '';
                $xlabel[$i]            = !empty($labels_and_values[$i][3][$i]) ? $labels_and_values[$i][3][$i] : '';
        }
    }

    gvn_chart_top_form($atts);

    // shortcode width/height attributes override the saved defaults
    $chart_w = !empty($atts['width'])  ? $atts['width']  : get_post_meta($atts['id'], 'guaven_sqlcharts_chartwidth', true);
    $chart_h = !empty($atts['height']) ? $atts['height'] : get_post_meta($atts['id'], 'guaven_sqlcharts_chartheight', true);
    $wrap_style = '';
    if ($chart_w != '') $wrap_style .= 'max-width:' . (int) $chart_w . 'px;';
    if ($chart_h != '') $wrap_style .= 'height:' . (int) $chart_h . 'px;';
    ?>
    <div class="gvn-chartwrap"<?php echo $wrap_style != '' ? ' style="' . esc_attr($wrap_style) . '"' : ''; ?>>
    <canvas
        id="ct-chart_<?php echo esc_attr($sqlcharts_inserted_script); ?>"
        class="guaven_chart_canvas"
    ></canvas>
    </div>

     <script type="text/javascript" class="gvn_charts_script" async>
     var ctx = jQuery("#ct-chart_<?php
    echo esc_attr($sqlcharts_inserted_script);
    ?>");

    <?php
        $print_data=apply_filters('guaven_sqlcharts_pre_print_vars',['tip_g'=>$tip_g, 'title'=>$post_g->post_title,
        'labels'=>$labels, 'values'=>$values, 'ylabel'=>$ylabel, 'pid'=>$atts['id']]);
        guaven_sqlcharts_print_chart_js($print_data);
    ?>
    </script>

    <?php
    if (!empty($atts["table"])) guaven_sqlcharts_tablepart($post_g->post_title, $labels, $values, $ylabel,$xlabel);
    $sqlcharts_inserted_script++;
    $ret=ob_get_clean();
    $ret=apply_filters( 'guaven_sqlcharts_final_output', $ret, $atts );
    return $ret;
}

add_shortcode('gvn_schart_2', 'guaven_sqlcharts_local_shortcode');

// legacy alias: old Google-Chart era posts produced [gvn_schart id=".."] shortcodes
if (!shortcode_exists('gvn_schart')) {
    add_shortcode('gvn_schart', 'guaven_sqlcharts_local_shortcode');
}

// [gvn_schart_2_cached id="1" expire="3600" arg1=".."] – same as gvn_schart_2 but the output is kept in a
// transient. All other attributes (argN, width, height, table, params) are passed through, and each
// distinct set of attributes gets its own cache entry. Append ?force_sql_cache_reload to the URL to bypass.
add_shortcode("gvn_schart_2_cached",function($atts){
	if(empty($atts["id"]))return;
    $atts["id"]=intval($atts["id"]);
    $is_logged_in=is_user_logged_in()?'':'_guest';
    $expire=!empty($atts["expire"])?intval($atts["expire"]):3600;
    $inner_atts=$atts;
    unset($inner_atts['expire']);
    $key='cached_sql_charts_'.$atts["id"].$is_logged_in;
    if (count($inner_atts) > 1) $key .= '_'.md5(serialize($inner_atts));
	$cached=get_transient($key);
	if(!empty($cached) and !isset($_GET["force_sql_cache_reload"])  )return $cached;
	$tobecached=guaven_sqlcharts_local_shortcode($inner_atts);
	set_transient($key, $tobecached,$expire);
	return $tobecached;
});

// fixed, colorblind-friendly default palette (Tableau 10) used when no custom colors are set
function guaven_sqlcharts_default_palette(){
    return apply_filters('guaven_sqlcharts_default_palette', array(
        '#4E79A7', '#F28E2B', '#E15759', '#76B7B2', '#59A14F',
        '#EDC948', '#B07AA1', '#FF9DA7', '#9C755F', '#BAB0AC'
    ));
}

function guaven_sqlcharts_colors($index, $pid = null){
  if(!isset($pid)) {
    global $post;
    $pid = $post->ID;
  }
  $colors=get_post_meta($pid,'guaven_sqlcharts_colors',true);
  $colors=explode(",",$colors);
  if (!empty($colors[$index])) return $colors[$index];
  $palette = guaven_sqlcharts_default_palette();
  return $palette[$index % count($palette)];
}

// outputs 'maintainAspectRatio:false,' when an explicit height is set, so the
// chart fills its sized wrapper instead of keeping the default aspect ratio
function guaven_sqlcharts_mar($pid){
    $atts = isset($GLOBALS["guaven_sqlcharts_atts"]) ? $GLOBALS["guaven_sqlcharts_atts"] : array();
    $h = !empty($atts['height']) ? $atts['height'] : get_post_meta($pid, 'guaven_sqlcharts_chartheight', true);
    return $h != '' ? 'maintainAspectRatio: false,' : '';
}

// outputs 'showAllTooltips: true,' when "Value labels" is checked; the values are drawn by the
// gvnShowAllValues plugin in asset/front.js (works for every chart type)
function guaven_sqlcharts_value_labels($pid){
    return get_post_meta($pid, 'guaven_sqlcharts_forcetooltips', true) != '' ? 'showAllTooltips: true,' : '';
}

// Chart.js scale title block built from the "X axis label" / "Y axis label" fields.
// $which is 'x' or 'y' (the *field* to use, not the scale). The Y label is only used as an axis
// title for single-series charts; with several ";"-separated series the legend names them instead.
function guaven_sqlcharts_axis_title($pid, $which){
    $key  = $which == 'x' ? 'guaven_sqlcharts_xarg_l' : 'guaven_sqlcharts_yarg_l';
    $text = trim(html_entity_decode((string) get_post_meta($pid, $key, true), ENT_QUOTES, 'UTF-8'));
    if ($text === '' or ($which == 'y' and strpos($text, ';') !== false)) return '';
    return 'title: {display: true, text: ' . wp_json_encode($text) . '},';
}

// dataset label as a safe JS string literal (labels saved before 3.0.1 may hold HTML entities)
function guaven_sqlcharts_js_label($label){
    return wp_json_encode(html_entity_decode((string) $label, ENT_QUOTES, 'UTF-8'));
}

// Parses an X value for the "time axis" option. Accepts YYYY, YYYY-MM, YYYY-MM-DD, optionally followed
// by HH:MM or HH:MM:SS. Returns a UTC timestamp in milliseconds, or false when the value is not a date.
function guaven_sqlcharts_parse_date($str){
    $str = trim((string) $str);
    if (!preg_match('/^(\d{4})(?:-(\d{1,2})(?:-(\d{1,2})(?:[ T](\d{1,2}):(\d{2})(?::(\d{2}))?)?)?)?$/', $str, $m)) return false;
    $y = (int) $m[1]; $mo = isset($m[2]) ? (int) $m[2] : 1; $d = isset($m[3]) ? (int) $m[3] : 1;
    $h = isset($m[4]) ? (int) $m[4] : 0; $mi = isset($m[5]) ? (int) $m[5] : 0; $sec = isset($m[6]) ? (int) $m[6] : 0;
    if (!checkdate($mo, $d, $y) or $h > 23 or $mi > 59 or $sec > 59) return false;
    return gmmktime($h, $mi, $sec, $mo, $d, $y) * 1000;
}

// "Scale X axis by date/time" option. Returns, per dataset, a list of "{x:<ms>,y:<value>}" JS point
// literals when the option is on and every X value is a date; false otherwise (normal category axis).
function guaven_sqlcharts_time_axis_points($pid, $values){
    if (get_post_meta($pid, 'guaven_sqlcharts_timeaxis', true) != 1) return false;
    $out = array();
    $has_point = false;
    foreach ($values as $key_ak => $series) {
        $out[$key_ak] = array();
        foreach ($series as $x => $y) {
            $ts = guaven_sqlcharts_parse_date($x);
            if ($ts === false) return false;
            $out[$key_ak][] = '{x:' . $ts . ',y:' . (is_numeric($y) ? $y + 0 : 'null') . '}';
            $has_point = true;
        }
    }
    return $has_point ? $out : false;
}

// X scale options for time-axis mode; gvnSqlChartsTimeTick (asset/front.js) formats the ticks as dates
function guaven_sqlcharts_time_axis_scale(){
    return "type: 'linear', offset: true, ticks: {callback: gvnSqlChartsTimeTick, maxRotation: 45},";
}
// extra entry for the Chart.js "plugins" object in time-axis mode (tooltip title shown as a date)
function guaven_sqlcharts_time_axis_plugins($time_points){
    return $time_points !== false ? 'tooltip: {callbacks: {title: gvnSqlChartsTimeTooltipTitle}}' : '';
}

function guaven_sqlcharts_bardata($title, $labels, $values, $ylabel, $type = 'bar', $pid = null)
{
    $horizontal = ($type == 'horizontalBar');
    $forcestack = ($type == 'stackedBar');
    $stacked = ($forcestack or get_post_meta($pid, 'guaven_sqlcharts_nostacked', true) != 1) ? 'true' : 'false';
    $time_points = $horizontal ? false : guaven_sqlcharts_time_axis_points($pid, $values);
?>
    var data = {
    <?php if ($time_points === false) { ?>labels: [<?php guaven_sqlcharts_merge_labeldata($labels);?>],<?php } ?>
    datasets: [
    <?php
    $values_new=guaven_sqlcharts_key_normalizer($values,$labels,$ylabel)[0];
    $i=-1;
    foreach ($values_new as $key_ak=>$value_ak) {
    $i++;
    $points = $time_points !== false ? $time_points[$key_ak] : $values_new[$key_ak];
?>
        {
            <?php
            if(!empty($GLOBALS["guaven_sqlcharts_atts"]["params"])){
                //passing chartJS params via the shortcode
                echo wp_kses($GLOBALS["guaven_sqlcharts_atts"]["params"],[]);
            }
            ?>
            label: <?php echo guaven_sqlcharts_js_label($ylabel[$key_ak]); ?>,
            backgroundColor: [
                <?php
        echo wp_kses(guaven_sqlcharts_colorgenerator(count($points), 0, 0, guaven_sqlcharts_colors($i, $pid)),[]);
?>
            ],
            borderColor: [
                <?php
        echo  wp_kses(guaven_sqlcharts_colorgenerator(count($points), 0, 0.2, guaven_sqlcharts_colors($i, $pid)),[]);
?>
            ],
            borderWidth: 1,
            <?php if ($time_points !== false) echo 'maxBarThickness: 48,'; ?>
            data: [<?php
        echo  wp_kses(implode(",", $points),[]);
?>],
        },
        <?php
    }
?>
    ]
};
var options={
    responsive: true,
    <?php echo wp_kses(guaven_sqlcharts_mar($pid),[]); ?>
    <?php echo wp_kses(guaven_sqlcharts_value_labels($pid),[]); ?>
    <?php if ($horizontal) echo "indexAxis: 'y',"; ?>
    scales: {
        x: {
            <?php if ($time_points !== false) echo guaven_sqlcharts_time_axis_scale(); ?>
            <?php echo guaven_sqlcharts_axis_title($pid, $horizontal ? 'y' : 'x'); ?>
            stacked: <?php echo esc_js($stacked); ?>,
            beginAtZero: <?php echo (get_post_meta($pid, 'guaven_sqlcharts_begin_with_0_x', true) == 1) ? 'true':'false'; ?>
        },
        y: {
            <?php echo guaven_sqlcharts_axis_title($pid, $horizontal ? 'x' : 'y'); ?>
            stacked: <?php echo esc_js($stacked); ?>,
            beginAtZero: <?php echo (get_post_meta($pid, 'guaven_sqlcharts_begin_with_0_y', true) == 1) ? 'true':'false'; ?>,
            ticks: {
                <?php if(get_post_meta($pid, 'guaven_sqlcharts_round_y_values', true) == 1) echo 'precision: 0,'; ?>
            }
        }
    }
    <?php
    guaven_sqlcharts_maybe_additional_parameters($pid, guaven_sqlcharts_time_axis_plugins($time_points));
    ?>
};
var myBarChart = new Chart(ctx, {
    type: 'bar',
    data: data,
    options: options
});
    <?php
}

function guaven_sqlcharts_merge_labeldata($labels){
    if(count($labels)==1){echo wp_kses(implode(",",$labels[0]),[]);return;}
    $merged=[];
    foreach($labels as $label){
        $merged=array_merge($merged,$label);
    }
    echo  wp_kses(implode(",",array_unique($merged)),[]);
}

function guaven_sqlcharts_linedata($title, $labels, $values, $ylabel, $type = 'false', $pid = null, $charttype = 'line', $stepped = false)
{
    $time_points = ($charttype == 'radar') ? false : guaven_sqlcharts_time_axis_points($pid, $values);
?>
var data = {
    <?php if ($time_points === false) { ?>labels: [<?php guaven_sqlcharts_merge_labeldata($labels);?>],<?php } ?>
    datasets: [
    <?php
    $values_new=guaven_sqlcharts_key_normalizer($values,$labels,$ylabel)[0];
    $dataset_count=count($values_new);
    $i=-1;
    foreach ($values_new as $key_ak=>$value_ak) {
        $i++;
        $points = $time_points !== false ? $time_points[$key_ak] : $values_new[$key_ak];
        if ($type == 'radarfill') $fill = "'origin'";
        elseif ($type == 'false') $fill = 'false';
        else $fill = ($i == 0 and $dataset_count > 1) ? '"+1"' : '"origin"';
?>
        {
            <?php
            if(!empty($GLOBALS["guaven_sqlcharts_atts"]["params"])){
                //passing chartJS params via the shortcode
                echo wp_kses($GLOBALS["guaven_sqlcharts_atts"]["params"],[]);
            }
            ?>
            label: <?php echo guaven_sqlcharts_js_label($ylabel[$key_ak]); ?>,
            fill: <?php echo wp_kses($fill,[]);
?>,
            tension: 0.1,
            <?php if ($stepped) echo 'stepped: true,'; ?>
            backgroundColor:  <?php
        echo  wp_kses_post(guaven_sqlcharts_colorgenerator(1, 1, 0.2, guaven_sqlcharts_colors($i, $pid)));
?>
            borderColor:  <?php
        echo  wp_kses_post(guaven_sqlcharts_colorgenerator(1, 1, 0.2, guaven_sqlcharts_colors($i, $pid)));
?>
            pointBorderColor: <?php
        echo  wp_kses_post(guaven_sqlcharts_colorgenerator(1, 1, 0.2, guaven_sqlcharts_colors($i, $pid)));
?>
            pointHoverBackgroundColor: <?php
        echo  wp_kses_post(guaven_sqlcharts_colorgenerator(1, 1, 0.2, guaven_sqlcharts_colors($i, $pid)));
?>
            pointHoverBorderColor:  <?php
        echo  wp_kses_post(guaven_sqlcharts_colorgenerator(1, 1, 0.2, guaven_sqlcharts_colors($i, $pid)));
?>
            data: [<?php
        echo  wp_kses_post(implode(",", $points));
?>],
            spanGaps: false,
        },
        <?php
    }
?>
    ]
};
var myLineChart = new Chart(ctx, {
    type: '<?php echo esc_attr($charttype); ?>',
    data: data,
   options: {
        responsive: true,
        <?php echo wp_kses(guaven_sqlcharts_mar($pid),[]); ?>
        <?php echo wp_kses(guaven_sqlcharts_value_labels($pid),[]); ?>
        <?php if ($charttype == 'radar') { ?>
        scales: {
            r: {
                beginAtZero: <?php echo (get_post_meta($pid, 'guaven_sqlcharts_begin_with_0_y', true) == 1) ? 'true':'false'; ?>
            }
        }
        <?php } else { ?>
        scales: {
            x: {
                display: true,
                <?php if ($time_points !== false) echo guaven_sqlcharts_time_axis_scale(); ?>
                <?php echo guaven_sqlcharts_axis_title($pid, 'x'); ?>
                beginAtZero: <?php echo (get_post_meta($pid, 'guaven_sqlcharts_begin_with_0_x', true) == 1) ? 'true':'false'; ?>
            },
            y: {
                <?php echo guaven_sqlcharts_axis_title($pid, 'y'); ?>
                beginAtZero: <?php echo (get_post_meta($pid, 'guaven_sqlcharts_begin_with_0_y', true) == 1) ? 'true':'false'; ?>,
                ticks: {
                   <?php if(get_post_meta($pid, 'guaven_sqlcharts_round_y_values', true) == 1) echo 'precision: 0,'; ?>
                }
            }
        }
        <?php } ?>
        <?php
        guaven_sqlcharts_maybe_additional_parameters($pid, guaven_sqlcharts_time_axis_plugins($time_points));
        ?>

    }
});
    <?php
}

function guaven_sqlcharts_scatterdata($title, $labels, $values, $ylabel, $pid = null)
{
?>
var data = {
    datasets: [
    <?php
    $i=-1;
    foreach ($values as $key_ak=>$value_ak) {
        $i++;
        $points=array();
        foreach ($value_ak as $xval=>$yval) {
            $x = is_numeric($xval) ? $xval : '"'.esc_js($xval).'"';
            $y = is_numeric($yval) ? $yval : '"'.esc_js($yval).'"';
            $points[] = '{x:'.$x.',y:'.$y.'}';
        }
?>
        {
            <?php
            if(!empty($GLOBALS["guaven_sqlcharts_atts"]["params"])){
                //passing chartJS params via the shortcode
                echo wp_kses($GLOBALS["guaven_sqlcharts_atts"]["params"],[]);
            }
            ?>
            label: <?php echo guaven_sqlcharts_js_label(isset($ylabel[$key_ak])?$ylabel[$key_ak]:''); ?>,
            backgroundColor:  <?php
        echo  wp_kses_post(guaven_sqlcharts_colorgenerator(1, 1, 0.2, guaven_sqlcharts_colors($i, $pid)));
?>
            borderColor:  <?php
        echo  wp_kses_post(guaven_sqlcharts_colorgenerator(1, 1, 0.2, guaven_sqlcharts_colors($i, $pid)));
?>
            data: [<?php echo wp_kses(implode(",", $points),[]); ?>],
        },
        <?php
    }
?>
    ]
};
var myScatterChart = new Chart(ctx, {
    type: 'scatter',
    data: data,
    options: {
        responsive: true,
        <?php echo wp_kses(guaven_sqlcharts_mar($pid),[]); ?>
        <?php echo wp_kses(guaven_sqlcharts_value_labels($pid),[]); ?>
        scales: {
            x: {
                <?php echo guaven_sqlcharts_axis_title($pid, 'x'); ?>
                beginAtZero: <?php echo (get_post_meta($pid, 'guaven_sqlcharts_begin_with_0_x', true) == 1) ? 'true':'false'; ?>
            },
            y: {
                <?php echo guaven_sqlcharts_axis_title($pid, 'y'); ?>
                beginAtZero: <?php echo (get_post_meta($pid, 'guaven_sqlcharts_begin_with_0_y', true) == 1) ? 'true':'false'; ?>,
                ticks: {
                    <?php if(get_post_meta($pid, 'guaven_sqlcharts_round_y_values', true) == 1) echo 'precision: 0,'; ?>
                }
            }
        }
        <?php
        guaven_sqlcharts_maybe_additional_parameters($pid);
        ?>
    }
});
    <?php
}


function guaven_sqlcharts_maybe_additional_parameters($pid, $extra_plugins = ''){
    if(function_exists('guaven_sqlcharts_maybe_additional_parameters_custom')){
        wp_kses(guaven_sqlcharts_maybe_additional_parameters_custom($pid),[]);
        return;
    }
    $guaven_sqlcharts_legend_position=get_post_meta($pid, 'guaven_sqlcharts_legend_position', true);
    if(in_array($guaven_sqlcharts_legend_position,['top','bottom','left','right'])){
        $display='true';$position=$guaven_sqlcharts_legend_position;
    }
    else {
        $display='false';$position='top';
    }
    echo wp_kses( ",plugins: {legend: {display: ".$display.",position:'".$position."'}".($extra_plugins !== '' ? ','.$extra_plugins : '')."}",[]);
}




function guaven_sqlcharts_piedata($title, $labels, $values, $ylabel, $pid, $type = 'pie')
{
?>
    var options={
        <?php echo wp_kses(guaven_sqlcharts_value_labels($pid),[]); ?>
        responsive: true
        <?php echo get_post_meta($pid,'guaven_sqlcharts_chartheight',true)!=''||!empty($GLOBALS["guaven_sqlcharts_atts"]['height'])?',maintainAspectRatio: false':''; ?>
        <?php
        guaven_sqlcharts_maybe_additional_parameters($pid);
        ?>
    };
    var data = {
    labels: [ <?php  guaven_sqlcharts_merge_labeldata($labels);?>],
    datasets: [
    <?php
    for ($i = 0; $i < count($values); $i++) {
?>
        {
            <?php
            if(!empty($GLOBALS["guaven_sqlcharts_atts"]["params"])){
                //passing chartJS params via the shortcode
                echo wp_kses($GLOBALS["guaven_sqlcharts_atts"]["params"],[]);
            }
            ?>
            data: [<?php
        echo wp_kses(implode(",", $values[$i]),[]);
?>],
            backgroundColor: [
                <?php
                $ii=0;
                foreach($values[$i] as $vci=>$valuecolor){
                    echo wp_kses_post(guaven_sqlcharts_colorgenerator(1, 0, -0.1, guaven_sqlcharts_colors($ii, $pid)));
                    $ii++;
                }
?>
            ],
            hoverBackgroundColor: [
               <?php
               $ii=0;
               foreach($values[$i] as $vci=>$valuecolor){
                echo wp_kses_post(guaven_sqlcharts_colorgenerator(1, 0, 0.2, guaven_sqlcharts_colors($ii, $pid)));
                $ii++;
            }
?>
            ]
        },
<?php
    }
?>
        ]
};
var myPieChart = new Chart(ctx,{
    type: '<?php
    echo esc_attr($type);
?>',
    data: data,
    options: options
});
    <?php
}




function guaven_sqlcharts_colorgenerator($count, $indic, $darkness = 0, $initcolor = '255,0,0')
{
  if (strpos($initcolor,'#')===0) {
    $split = str_split(substr($initcolor,1), 2);
    $r = hexdec($split[0]);
    $g = hexdec($split[1]);
    $b = hexdec($split[2]);
    $ret='';
    for ($i = 0; $i < $count; $i++) {
        $ret .= "'rgba(" . $r . ", " . $g . ", " . $b . ",".($darkness + 0.8 - $indic * $i * 0.8 / ($count)).")',
        ";
      }
      return $ret;
  }
    $initial_colors = array(
        'linebg' => 'red',
        'linebr' => 'yellow',
        'linebc' => 'green',
        'linehbg' => 'white',
        'linehbc' => 'black'
    );
    if (!empty($initial_colors[$count]))
        return '"' . $initial_colors[$count] . '",
        ';
    $ret = '';
    for ($i = 0; $i < $count; $i++) {
        $ret .= "'rgba(" . $initcolor . "," . ($darkness + 0.8 - $indic * $i * 0.8 / ($count)) . ")',
        ";
    }
    return $ret;
}

function guaven_sqlcharts_tablepart($title, $labels, $values, $ylabel,$xlabel){
  $tabledata='';
  $fcol=[];$scol=[];
  $empty_cell=apply_filters( 'guaven_sqlcharts_table_empty_cell','<td></td>');
  $tablein='';
  foreach($values as $row=>$valuerow){
    foreach ($valuerow as $key => $value) {
      $putval=$labels[$row][$key]??'';
      $fcol[$key]='<td>'.str_replace('"',"",$putval).'</td>';
      $scol[$key][$row]='<td>'.$value.'</td>';
    }
    foreach($scol as $scolkey=>$scolvalue){
        for($i=0;$i<count($values);$i++){
            //echo $i;
            if(!isset($scolvalue[$i]))$scol[$scolkey][$i]=$empty_cell;;
        }
        ksort($scol[$scolkey]);
      }
  }

  foreach($fcol as $key=>$value){
    $tablein.='<tr>'.$value.implode(" ",$scol[$key]).'</tr>'.PHP_EOL;
  }
  $tabledata.='<div class="gvn-tablewrap"><table class="gvn-table"><tr><th>'.$xlabel[0].'</th><th>'.implode("</th><th>",$ylabel).'</th></tr>
  '.$tablein.'</table></div><br>';

  echo wp_kses_post($tabledata);

}

function guaven_sqlcharts_graphtype($post){
    if (strpos(get_post_meta($post->ID, 'guaven_sqlcharts_graphtype', true), "_l") !== false)
        $postfix = '_2';
    else $postfix = '';
    return $postfix;
}

add_filter('the_content',function($content){
    if(!is_singular('gvn_schart'))return $content;
    global $post;
    $postfix=guaven_sqlcharts_graphtype($post);
    return '[gvn_schart'.$postfix.' id="'.$post->ID.'"'.
    (get_post_meta($post->ID,'guaven_sqlcharts_tablepart',true)!=''?' table="1"':'')
    .']';
});

function guaven_sqlcharts_key_normalizer($values,$labels,$ylabel){
    $normalize_keys=[];
    $empty_value=apply_filters( 'guaven_sqlcharts_table_empty_value','');
    foreach ($values as $key_ak=>$value_ak) {
        $normalize_keys=array_merge($normalize_keys,array_keys($values[$key_ak]));
    }
    $values_normalized=[];$labels_normalized=[];$ylabel_normalized=[];
    foreach($normalize_keys as $normalized_key){
        foreach ($values as $key_ak=>$value_ak) {
            $values_normalized[$key_ak][$normalized_key]=isset( $values[$key_ak][$normalized_key])? $values[$key_ak][$normalized_key]:"'".$empty_value."'";
            $labels_normalized[$key_ak][$normalized_key]=isset( $labels[$key_ak][$normalized_key])? $labels[$key_ak][$normalized_key]:"''";
            $ylabel_normalized[$key_ak][$normalized_key]=isset( $ylabel[$key_ak][$normalized_key])? $ylabel[$key_ak][$normalized_key]:"";
        }
    }
    return [$values_normalized,$labels_normalized,$ylabel_normalized];
}


add_filter('guaven_sqlcharts_table_empty_cell',function($str){return '<td>#</td>';});
add_filter('guaven_sqlcharts_table_empty_value',function($str){return 'N/A';});

```