Exemplo n.º 1
0
function queryCreate($m_axes, $filter = null)
{
    require SERVER_ROOT . '/php/dist/constant.php';
    require SERVER_ROOT . '/php/dist/extension.php';
    require SERVER_ROOT . '/module/definition/module.php';
    require SERVER_ROOT . '/module/query_create/dist/create_subquery.php';
    $y_axis = $m_axes->y_axis;
    $x_axis = $m_axes->x_axis;
    $filter_main = $m_axes->filter_main;
    $year_min = $filter_main->year_min;
    //Y-axis stuff.
    $y_id = paramToId($y_axis->param);
    $y_def = definitionCreate($y_id, $year_min);
    $y_sql = $y_def->sql;
    $y_min = $y_axis->min;
    $y_max = $y_axis->max;
    if ($y_sql->nullToZero) {
        $y_sql->y_field = 'isNull(y, 0) as y';
    } else {
        $y_sql->y_field = 'y';
    }
    $y_sql->join_field = "on {$const->table}.{$const->field_heat} = subY.{$const->field_heat} \n" . "and {$const->table}.{$const->field_year} = subY.{$const->field_year} ";
    $y_sql->join_field = indent($y_sql->join_field, 2);
    $subquery = create_subquery($y_id, $year_min, 'y');
    $query_y = $subquery->query;
    $where_realistic_y = $subquery->where_realistic;
    $query_y = indent($query_y, 2);
    //End: Y-axis stuff.
    //X-axis stuff.
    $x_id = paramToId($x_axis->param);
    $x_def = definitionCreate($x_id, $year_min);
    $x_sql = $x_def->sql;
    $x_min = $x_axis->min;
    $x_max = $x_axis->max;
    if ($x_sql->nullToZero) {
        $x_sql->x_field = 'isNull(x, 0) as x';
    } else {
        $x_sql->x_field = 'x';
    }
    $x_sql->join_field = "on {$const->table}.{$const->field_heat} = subX.{$const->field_heat} \n" . "and {$const->table}.{$const->field_year} = subX.{$const->field_year} ";
    $x_sql->join_field = indent($x_sql->join_field, 2);
    $subquery = create_subquery($x_id, $year_min, 'x');
    $query_x = $subquery->query;
    $where_realistic_x = $subquery->where_realistic;
    $query_x = indent($query_x, 2);
    //End: X-axis stuff.
    //Main filter stuff.
    $date_min = $filter_main->date_min;
    $date_max = $filter_main->date_max;
    $tap_grade = $filter_main->tap_grade;
    //End: Main filter stuff.
    //Main where clause.
    $where = "where {$const->table}.{$const->field_year} = '{$year_min}'";
    if ($date_min || $date_max) {
        if (!$date_max) {
            $where .= " \n" . "  and {$const->table}.{$const->field_tapDate} >= '{$date_min}'";
        } else {
            if (!$date_min) {
                $where .= " \n" . "  and {$const->table}.{$const->field_tapDate} <= '{$date_max} 23:59:59'";
            } else {
                $where .= " \n" . "  and {$const->table}.{$const->field_tapDate} between '{$date_min}' and '{$date_max} 23:59:59'";
            }
        }
    }
    if ($tap_grade) {
        $where .= " \n" . "  and {$const->table}.{$const->field_tapGrade} like '{$tap_grade}'";
    }
    if ($y_min || $y_max) {
        if (!$y_max) {
            $where .= " \n" . "  and y >= {$y_min}";
        } else {
            if (!$y_min) {
                $where .= " \n" . "  and y <= {$y_max}";
            } else {
                $where .= " \n" . "  and y between {$y_min} and {$y_max}";
            }
        }
    }
    if ($x_min || $x_max) {
        if (!$x_max) {
            $where .= " \n" . "  and x >= {$x_min}";
        } else {
            if (!$x_min) {
                $where .= " \n" . "  and x <= {$x_max}";
            } else {
                $where .= " \n" . "  and x between {$x_min} and {$x_max}";
            }
        }
    }
    if ($where_realistic_y) {
        $where_realistic_y = "and ( \n" . indent($where_realistic_y, 1) . " \n" . ")";
        $where = $where . " \n" . indent($where_realistic_y, 1);
    }
    if ($where_realistic_x) {
        $where_realistic_x = "and ( \n" . indent($where_realistic_x, 1) . " \n" . ")";
        $where = $where . " \n" . indent($where_realistic_x, 1);
    }
    $where = str_replace($y_id, 'y', $where);
    $where = str_replace($x_id, 'x', $where);
    //End: Main where clause.
    //Main subquery.
    $round_factor = $x_axis->round_factor;
    if ($round_factor) {
        $x_field_round = "{$round_factor} * round(x / {$round_factor}, 0) as x_round";
    } else {
        $x_field_round = "null as x_round";
    }
    $query = "select {$x_sql->x_field}, {$y_sql->y_field}, {$const->table}.{$const->field_heat} as heat, {$const->table}.{$const->field_tapDate} as tap_date, {$x_field_round} \n" . "from {$const->db}.{$const->table} {$const->table} \n" . "{$x_sql->join} join ( \n" . "{$query_x} \n" . ") subX \n" . "{$x_sql->join_field} \n" . "{$y_sql->join} join ( \n" . "{$query_y} \n" . ") subY \n" . "{$y_sql->join_field} \n" . $where;
    //End: Main subquery.
    //Main query.
    $round_factor = $x_axis->round_factor;
    if ($round_factor) {
        $y_field_avg = "avg(y) over(partition by x_round) as y_avg";
        $y_field_stdev = "stdev(y) over(partition by x_round) as y_stdev";
        $y_field_count = "count(y) over(partition by x_round) as y_count";
    } else {
        $y_field_avg = "null as y_avg";
        $y_field_stdev = "null as y_stdev";
        $y_field_count = "null as y_count";
    }
    $query = "select x, y, heat, tap_date, x_round, {$y_field_avg}, {$y_field_stdev}, {$y_field_count} \n" . "from ( \n" . indent($query, 2) . " \n" . ") sub";
    //End: Main query.
    return $query;
}
<?php

require_once SERVER_ROOT . '/php/dist/extension.php';
require_once SERVER_ROOT . '/module/definition/module.php';
require_once SERVER_ROOT . '/module/form_elem/module.php';
$param_array = json_decode($_POST["param_array"]);
$area = json_decode($_POST["area"]);
$id = paramToId($param_array);
$def = definitionCreate($id);
$filter_type = $def->info->filter_type;
switch ($area) {
    case 'axis':
        switch ($filter_type) {
            case 'none':
                $html = '';
                break;
            default:
                $html = "<div class=\"min-wrap col-xs-6 noPad-xs\"> \n" . formElemCreate("axis_min", "min") . "</div> \n" . "<div class=\"max-wrap col-xs-6 noPad-xs\"> \n" . formElemCreate("axis_max", "max") . "</div>";
                break;
        }
        break;
    case 'filters':
        switch ($filter_type) {
            case 'none':
                $html = '';
                break;
            default:
                $html = formElemCreate("filter_operator", "operator");
                break;
        }
        break;