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.
}
<?php
$area_id = $param[1]->value;
$area_name = $param[1]->text;
$elem_id = strtoupper($param[2]->value);
$elem_name = $param[2]->text;
$title = "Yield: {$area_name} {$elem_name}";
$id = idWrapAdd("{$category} {$area_id} {$elem_id}");
$unit = '%';
switch ($area_id) {
case 'bop':
$field = '( ' . idWrapAdd("chem chem_diff ILAD BLAD {$elem_id}") . ' ) / ' . '( ' . idWrapAdd("bop_ladle_add bop_ladle_{$elem_id} total_cntd") . ' / 5000.0 )';
$id_arr = idMultiStrToArr($field);
break;
default:
break;
}
