Exemplo n.º 1
0
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.
}
Exemplo n.º 2
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;
}
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "select c.child_name_id, c.html_class \n" . "from data_chart.param_input_child c \n" . "where c.option_value = '" . $name_id . "' \n" . "order by c.order_num \n";
if (!$conn->query($sql)) {
    $error_arr[] = $conn->error;
}
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
    $child_name_id = $row["child_name_id"];
    $html_class = $row["html_class"];
    $html .= formElemCreate($child_name_id, $html_class);
}
$id_second_part = idFromFirstOptions($html);
$id = idWrapAdd("{$id_first_part} {$id_second_part}");
$def = definitionCreate($id);
$filter_type = $def->info->filter_type;
if (count($error_arr) === 0) {
    $conn->commit();
    $status = "success";
} else {
    $conn->rollback();
    $status = "failure";
}
$output = new stdClass();
$output->html = $html;
$output->status = $status;
$output->errorArr = $error_arr;
// $output->debugSQL = $sql;
$output->id = $id;
$output->filter_type = $filter_type;
Exemplo n.º 4
0
function categoryHTML($area)
{
    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';
    require_once SERVER_ROOT . '/module/category/dist/id_from_first_options.php';
    $html_class = "m-category";
    switch ($area) {
        case 'y-axis':
            $html_class .= " areatype-axis";
            break;
        case 'x-axis':
            $html_class .= " areatype-axis";
            break;
        case 'filters':
            $html_class .= " areatype-filters";
            break;
        default:
            break;
    }
    $category_html = formElemCreate("category", null, $area);
    $id = idFromFirstOptions($category_html);
    $id = idWrapAdd($id);
    $def = definitionCreate($id);
    $filter_type = $def->info->filter_type;
    $filter_wrap_html_class = 'filter-wrap row';
    if ($filter_type === 'none') {
        $filter_wrap_html_class .= ' hidden';
    }
    ob_start();
    //Function continues...
    ?>

		
		<div class="<?php 
    echo $html_class;
    ?>
">
		
			<div class="category-wrap">
				<?php 
    echo $category_html;
    ?>
			</div>
	

			<div class="<?php 
    echo $filter_wrap_html_class;
    ?>
">
				<div class="operator-wrap col-xs-4 halfPad-xs">
					<?php 
    echo formElemCreate("filter_operator", "operator");
    ?>
				</div>

				<div class="col-xs-8 noPad-xs">
					<div class="equal-wrap col-xs-6 halfPad-xs hidden">
						<?php 
    echo formElemCreate("filter_min", "equal");
    ?>
					</div>

					<div class="min-wrap col-xs-6 halfPad-xs hidden">
						<?php 
    echo formElemCreate("filter_min", "min");
    ?>
					</div>

					<div class="max-wrap col-xs-6 halfPad-xs hidden">
						<?php 
    echo formElemCreate("filter_max", "max");
    ?>
					</div>
				</div>
			</div>

		</div>
		  

<?php 
    //...function continues.
    $html = ob_get_clean();
    return $html;
}