function create_subquery($id, $year, $alias = null)
{
require SERVER_ROOT . '/php/dist/constant.php';
require_once SERVER_ROOT . '/php/dist/extension.php';
require_once SERVER_ROOT . '/module/definition/module.php';
//Initialize.
$where_realistic_all = '';
if (!$alias) {
$alias = $id;
}
//End: Initialize.
//Test whether subqueries are needed ($hasSubfield).
if (count(idMultiStrToArr($id)) === 1) {
//If there's only 1 field to select (i.e. no subqueries needed).
$def_obj = definitionCreate($id, $year);
$thisQuery_obj = clone $def_obj;
$id_arr = $thisQuery_obj->sql->id_arr;
if (count($id_arr) > 1) {
//If there was only 1 field to select, but that field is actually made up of multiple fields (e.g. temp_diff).
$hasSubfield = true;
} else {
//If there was only 1 field to select and it IS NOT actually made up of multiple fields.
$hasSubfield = false;
}
} else {
//If there are multiple fields to select (i.e. subqueries needed).
$hasSubfield = true;
$thisQuery_obj->sql->field = $id;
$id_arr = idMultiStrToArr($id);
}
//End: Test whether subqueries are needed.
//Generating subqueries (if necessary).
if (!$hasSubfield) {
//If subqueries are NOT needed.
$from = $thisQuery_obj->sql->from;
$db = $thisQuery_obj->sql->db;
if (!$thisQuery_obj->sql->fromPseudoTable) {
$thisQuery_obj->sql->from = "{$db}.{$from} {$from} ";
} else {
$thisQuery_obj->sql->from = "( \n {$from} \n ) sub ";
}
} else {
//If subqueries are needed.
$from_sub = '';
$where = "where 1 = 1 ";
foreach ($id_arr as $key => $value) {
//Loop thru each subfield.
$subquery_obj = definitionCreate($value, $year);
$id = $subquery_obj->sql->id;
$where_realistic = $subquery_obj->sql->where_realistic;
$sub_alias = 'sub' . ($key + 1);
if ($subquery_obj->sql->nullToZero) {
//If it's a null-to-zero field.
$where_realistic = str_replace($id, "isNull({$id}, 0)", $where_realistic);
} else {
$where_realistic = str_replace($alias, $id, $where_realistic);
}
if ($where_realistic) {
if ($where_realistic_all !== '') {
$where_realistic_all .= " \n" . "and ";
}
$where_realistic_all .= "( {$where_realistic} )";
}
$join = $subquery_obj->sql->join;
if ($from_sub !== '') {
$join_type = "{$join} join";
$join_field = "on sub1.{$const->field_heat} = {$sub_alias}.{$const->field_heat} \n" . "and sub1.{$const->field_year} = {$sub_alias}.{$const->field_year} ";
$join_field = indent($join_field, 2);
} else {
$join_type = '';
$join_field = '';
}
$obj = create_subquery($value, $year);
$query = $obj->query;
$from_sub .= "{$join_type} ( \n" . indent($query, 1) . " \n" . ") {$sub_alias} \n" . $join_field;
}
$thisQuery_obj->sql->from = $from_sub;
}
//End: Generating subqueries (if necessary).
//Getting necessary variables ready.
$field = $thisQuery_obj->sql->field;
$id = $thisQuery_obj->sql->id;
$from = $thisQuery_obj->sql->from;
$db = $thisQuery_obj->sql->db;
$where_local = $thisQuery_obj->sql->where_local;
$where_realistic = $thisQuery_obj->sql->where_realistic;
if ($thisQuery_obj->sql->nullToZero) {
//If it's a null-to-zero field.
$where_realistic = str_replace($id, "isNull({$id}, 0)", $where_realistic);
} else {
$where_realistic = str_replace($alias, $id, $where_realistic);
}
if ($thisQuery_obj->sql->select_distinct) {
$select = 'select distinct';
} else {
$select = 'select';
}
$where = "where {$const->field_year} >= '{$year}'";
//End: Getting necessary variables ready.
//Create local where clause.
if ($where_local) {
$where_local = "and ( \n" . indent($where_local, 1) . " \n" . ")";
$where = $where . " \n" . indent($where_local, 1);
}
//End: Create local where clause.
//Create realistic where clause. This will be part of the where clause in the parent query (not this query).
if ($where_realistic_all) {
$where_realistic_all = "and ( \n" . indent($where_realistic_all, 1) . " \n" . ")";
$where = $where . " \n" . indent($where_realistic_all, 1);
}
//End: Create realistic where clause.
//Create this query.
$query = "{$select} {$field} as {$alias}, {$const->field_heat}, {$const->field_year} \n" . "from {$from} \n" . $where;
//End: Create this query.
//Handle output.
$output = new stdClass();
$output->query = $query;
$output->where_realistic = $where_realistic;
return $output;
//End: Handle output.
}
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;
}
