<?php

/**
 * Smart Report Maker
 * Version 9.0.0
 * Author : Webuccino
 * All copyrights are preserved to Webuccinorecords_per_page
 * URL : http://mysqlreports.com/
 */
defined('DIRECTACESS') or die("Error 301: Access denied!");

class StandardEngine extends Engine {

    public function __construct($reports_directory) {
        $this->intialize_needed_sessions();
        if (substr(trim($reports_directory), -1) == '/') {
            $this->_reports_directory = $reports_directory;
        } else {
            $this->_reports_directory = $reports_directory . "/";
        }
        if ($_SESSION[Session_report_Settings_key]["layout"] == 'Mobile') {
            $_SESSION[Session_report_Settings_key]["is_mobile"] = true;
        }

        $this->set_connection_name($_SESSION[Session_report_Settings_key]["connection_name"]);
        $this->set_security_rules($_SESSION[Session_report_Settings_key]["access_rule"], $_SESSION[Session_report_Settings_key]["access_groups"]);
        $this->set_file_name($_SESSION[Session_report_Settings_key]["file_name"]);
        $this->set_category($_SESSION[Session_report_Settings_key]["category_id"]);
        $this->set_date_created($_SESSION[Session_report_Settings_key]["date_created"]);
        $this->set_language($_SESSION[Session_report_Settings_key]["language"]);

        $this->set_fields($_SESSION[Session_report_Settings_key]["fields"]);
        $this->set_fields2($_SESSION[Session_report_Settings_key]["fields2"]);
        $this->set_records_per_page((int) $_SESSION[Session_report_Settings_key]["records_per_page"]);
        $this->set_layout($_SESSION[Session_report_Settings_key]["layout"]);
        $this->set_style_name($_SESSION[Session_report_Settings_key]["style_name"]);
        $this->set_title($_SESSION[Session_report_Settings_key]['title']);
        $this->set_header($_SESSION[Session_report_Settings_key]["header"]);
        $this->set_footer($_SESSION[Session_report_Settings_key]["footer"]);
        if (isset($_SESSION[Session_report_Settings_key]["filters_grouping"]))
            $this->set_filters_grouping($_SESSION[Session_report_Settings_key]["filters_grouping"]);
        $_SESSION[Session_report_Settings_key]["sub_totals"] = isset($_SESSION[Session_report_Settings_key]["sub_totals"]) ? $_SESSION[Session_report_Settings_key]["sub_totals"] : array();
        $this->set_sub_totals($_SESSION[Session_report_Settings_key]["sub_totals"]);
        $_SESSION[Session_report_Settings_key]["subtotals_enabled"] = isset($_SESSION[Session_report_Settings_key]["subtotals_enabled"]) ? $_SESSION[Session_report_Settings_key]["subtotals_enabled"] : "";
        $this->set_sub_totals_enabled($_SESSION[Session_report_Settings_key]["subtotals_enabled"]);



        $_SESSION[Session_report_Settings_key]["is_mobile"] = isset($_SESSION[Session_report_Settings_key]["is_mobile"]) ? $_SESSION[Session_report_Settings_key]["is_mobile"] : false;
        $this->set_is_mobile($_SESSION[Session_report_Settings_key]["is_mobile"]);
        $this->set_cells($_SESSION[Session_report_Settings_key]["cells"]);
        $_SESSION[Session_report_Settings_key]["conditional_formating"] = isset($_SESSION[Session_report_Settings_key]["conditional_formating"]) ? $_SESSION[Session_report_Settings_key]["conditional_formating"] : array();
        $this->set_conditional_formating($_SESSION[Session_report_Settings_key]["conditional_formating"]);
        $this->set_labels($_SESSION[Session_report_Settings_key]["labels"]);
        $this->set_group_by($_SESSION[Session_report_Settings_key]["group_by"]);
        $this->set_datasource($_SESSION[Session_report_Settings_key]["datasource"]);
        $this->set_sort_by($_SESSION[Session_report_Settings_key]["sort_by"]);
        if (strtolower($_SESSION[Session_report_Settings_key]["datasource"]) == "sql") {
            if(isset($_SESSION[Session_report_Settings_key]["sql"])){
            $this->set_sql($_SESSION[Session_report_Settings_key]["sql"]);
            }
            $this->set_chkSearch("no");
        } else {
            $this->set_table($_SESSION[Session_report_Settings_key]["table"]);
            //$_SESSION[Session_report_Settings_key]["tables_filters"] = isset($_SESSION[Session_report_Settings_key]["tables_filters"])?$_SESSION[Session_report_Settings_key]["tables_filters"]:array();
            $_SESSION[Session_report_Settings_key]["tables_filters"] = isset($_SESSION[Session_report_Settings_key]["tables_filters"]) ? $_SESSION[Session_report_Settings_key]["tables_filters"] : array();
            $this->set_tables_filters($_SESSION[Session_report_Settings_key]["tables_filters"]);
            $_SESSION[Session_report_Settings_key]["relationships"] = isset($_SESSION[Session_report_Settings_key]["relationships"]) ? $_SESSION[Session_report_Settings_key]["relationships"] : array();
            $this->set_relationships($_SESSION[Session_report_Settings_key]["relationships"]);
            $this->set_chkSearch($_SESSION[Session_report_Settings_key]["chkSearch"]);
            $_SESSION[Session_report_Settings_key]["affected_column"] = isset($_SESSION[Session_report_Settings_key]["affected_column"]) ? $_SESSION[Session_report_Settings_key]["affected_column"] : "";
            $this->set_Statestical_affected_column($_SESSION[Session_report_Settings_key]["affected_column"]);
            $_SESSION[Session_report_Settings_key]["function"] = isset($_SESSION[Session_report_Settings_key]["function"]) ? $_SESSION[Session_report_Settings_key]["function"] : "";
            $this->set_Statestical_function($_SESSION[Session_report_Settings_key]["function"]);
            $_SESSION[Session_report_Settings_key]["groupby_column"] = isset($_SESSION[Session_report_Settings_key]["groupby_column"]) ? $_SESSION[Session_report_Settings_key]["groupby_column"] : "";
            $this->set_Statestical__groupby_column($_SESSION[Session_report_Settings_key]["groupby_column"]);
        }
    }

    /**
     * This function validate the request and make sure it's sent by an admin who is connected to the database
     * @return boolean if the request is sent by a connected admin
     * @throws Exception
     */
    protected function validate_request($srm_dashboard) {

        /*
         * ===============================================================================
         *               Check a connection was made. 
         * ===============================================================================
         */
        if (!isset($_SESSION[Session_wizard_key]["validate_key"])) {
            srm_dashboard::log("Invalid request as no connection detected.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
            throw new Exception('Invalid request as no connection detected.');
            return false;
        }

        /*
         * ===================================================================================================================
         *               Check that selected fields $_SESSION[Session_report_Settings_key]["fields"] are not empty 
         * ==================================================================================================================
         */

        if (!isset($_SESSION[Session_report_Settings_key]["fields"]) ||
                empty($_SESSION[Session_report_Settings_key]["fields"])
        ) {
            srm_dashboard::log("It seems that no fields have been selected in your options. Please return to the columns step and ensure that you select fields for your report. Then, remember to click 'Next' to save your options.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
            throw new Exception("It seems that no fields have been selected in your options. Please return to the columns step and ensure that you select fields for your report. Then, remember to click 'Next' to save your options.");
            return false;
        }


        /*
         * ===================================================================================================================
         *               check that if Table data source that selected columns and relasionships belongs to selected tables. 
         * ==================================================================================================================
         */


        if (isset($_SESSION[Session_report_Settings_key]["datasource"]) && $_SESSION[Session_report_Settings_key]["datasource"] == "table") {
            if ($_SESSION[Session_report_Settings_key]["connection_name"] != default_connection_name) {
                $parameters = $srm_dashboard->get_connection_details(trim($_SESSION[Session_report_Settings_key]["connection_name"]));
            } else {
                $parameters = $srm_dashboard->get_default_connection_details();
            }
            $DB_HOST = isset($parameters["server"]) ? $parameters["server"] : "";
            $DB_USER = isset($parameters["user"]) ? $parameters["user"] : "";
            $DB_PASSWORD = isset($parameters["password"]) ? $parameters["password"] : "";
            $DB_NAME = isset($parameters["db"]) ? $parameters["db"] : "";
            $DB_CHARSET = isset($parameters["charset"]) ? $parameters["charset"] : "";
            $DB_COLLATION = isset($parameters["collation"]) ? $parameters["collation"] : "";
            $DB_PORT = isset($parameters["port"]) ? $parameters["port"] : "";
            $dbHandler = new DatabaseHandler($DB_HOST, $DB_USER, $DB_PASSWORD, $DB_NAME);
            if ($dbHandler) {
                $query = "select ";
                foreach ($_SESSION[Session_report_Settings_key]["fields"] as $column) {
                    if (count($_SESSION[Session_report_Settings_key]["table"]) == 1) {
                        $query .= "`$column` ,";
                    } else {
                        $column_details = explode(".", $column);
                        $query .= "`" . $column_details[0] . "`.`" . $column_details[1] . "` ,";
                    }
                }
                $query = substr_replace($query, '', -1);
                $query .= " from";
                foreach ($_SESSION[Session_report_Settings_key]["table"] as $table) {
                    $query .= " `$table` ,";
                }
                $query = substr_replace($query, '', -1);
                if (isset($_SESSION[Session_report_Settings_key]["relationships"]) && !empty($_SESSION[Session_report_Settings_key]["relationships"])) {
                    $query .= " where ";
                    foreach ($_SESSION[Session_report_Settings_key]["relationships"] as $relasion) {
                        $query .= " $relasion And";
                    }
                    $query .= ")";
                    $query = str_replace("And)", "", $query);
                }
                $results = $dbHandler->query($query);

                if ($results === false) {
                    srm_dashboard::log("One or more of the selected coulmns dosn't exist in selected table", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", $query, json_encode($_SESSION[Session_report_Settings_key]));
                    throw new Exception("It appears that one or more of the columns you have chosen do not exist in the selected table. To ensure the accuracy of your selection, please revisit the columns step to validate your choices. After confirming the column selection, be sure to click Next to save your updated preferences.");
                    return false;
                }
            } else {
                srm_dashboard::log("Can't establish a connection with the database of the report.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                throw new Exception("Can't establish a connection with the datasource of the report.");
                return false;
            }
        }

        /*
         * ===================================================================================================================
         *               check that if there is filters that it belongs to selected tables. 
         * ==================================================================================================================
         */


        if (isset($_SESSION[Session_report_Settings_key]["tables_filters"]) && !empty($_SESSION[Session_report_Settings_key]["tables_filters"])) {

            foreach ($_SESSION[Session_report_Settings_key]["tables_filters"] as $filter) {
                $filter_details = explode(".", $filter["sql"]);
                $filter_table = trim(str_replace("`", "", $filter_details[0]));
                if (!in_array($filter_table, $_SESSION[Session_report_Settings_key]["table"])) {
                    srm_dashboard::log("Table $filter_table  is used for filtration in the data filters step. However, it appears that this table is not currently selected. Kindly return to the filters step and remove any filters that rely on this table. ", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                    throw new Exception("Table $filter_table  is used for filtration in the data filters step. However, it appears that this table is not currently selected. Kindly return to the filters step and remove any filters that rely on this table. ");
                    return false;
                }
            }
        }

        /*
         * ===================================================================================================================
         *               check that if data source is sql Then: 
         *                - Selected Tables are empty , relationships empty, filters empty
         *                - Sql query can't be empty
         *                - Selected Fields belongs to sql query.
         * ==================================================================================================================
         */

        if (isset($_SESSION[Session_report_Settings_key]["datasource"]) && $_SESSION[Session_report_Settings_key]["datasource"] == "sql") {
            if (!isset($_SESSION[Session_report_Settings_key]["sql"]) || empty($_SESSION[Session_report_Settings_key]["sql"])) {
                srm_dashboard::log("The report's data source is set to a query, but no SQL query has been defined. Please return to the data source step, click 'Next', and ensure that you enter a valid SQL query. Then, click 'Next' again to save your selection.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                throw new Exception("The report's data source is set to a query, but no SQL query has been defined. Please return to the data source step, click 'Next', and ensure that you enter a valid SQL query. Then, click 'Next' again to save your selection.");
                return false;
            } elseif (isset($_SESSION[Session_report_Settings_key]["table"]) && !empty($_SESSION[Session_report_Settings_key]["table"])) {
                srm_dashboard::log("The report's data source is set as a query, but there is a selected table in your preferences. Please return to the data source step, validate your choices, and then ensure to click 'Next' to save your input.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                throw new Exception("The report's data source is set as a query, but there is a selected table in your preferences. Please return to the data source step, validate your choices, and then ensure to click 'Next' to save your input.");
                return false;
            } elseif (isset($_SESSION[Session_report_Settings_key]["relationships"]) && !empty($_SESSION[Session_report_Settings_key]["relationships"])) {
                srm_dashboard::log("The report's data source is set as a query, but there are table relationships defined in your preferences. Please return to the data source step, validate your choices, and then ensure to click 'Next' to save your input.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                throw new Exception("The report's data source is set as a query, but there are table relationships defined in your preferences. Please return to the data source step, validate your choices, and then ensure to click 'Next' to save your input.");
                return false;
            } elseif (isset($_SESSION[Session_report_Settings_key]["tables_filters"]) && !empty($_SESSION[Session_report_Settings_key]["tables_filters"])) {
                srm_dashboard::log("The report's data source is set as a query, but there are table filters defined in your preferences. Please return to the data source step, validate your choices, and then ensure to click 'Next' to save your input.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                throw new Exception("The report's data source is set as a query, but there are table filters defined in your preferences. Please return to the data source step, validate your choices, and then ensure to click 'Next' to save your input.");
                return false;
            } else {

                if ($_SESSION[Session_report_Settings_key]["connection_name"] != default_connection_name) {
                    $parameters = $srm_dashboard->get_connection_details(trim($_SESSION[Session_report_Settings_key]["connection_name"]));
                } else {
                    $parameters = $srm_dashboard->get_default_connection_details();
                }
                $DB_HOST = isset($parameters["server"]) ? $parameters["server"] : "";
                $DB_USER = isset($parameters["user"]) ? $parameters["user"] : "";
                $DB_PASSWORD = isset($parameters["password"]) ? $parameters["password"] : "";
                $DB_NAME = isset($parameters["db"]) ? $parameters["db"] : "";
                $DB_CHARSET = isset($parameters["charset"]) ? $parameters["charset"] : "";
                $DB_COLLATION = isset($parameters["collation"]) ? $parameters["collation"] : "";
                $DB_PORT = isset($parameters["port"]) ? $parameters["port"] : "";
                $dbHandler = new DatabaseHandler($DB_HOST, $DB_USER, $DB_PASSWORD, $DB_NAME);
                if ($dbHandler) {
                    $query = $_SESSION[Session_report_Settings_key]["sql"];
                    $results = $dbHandler->query($query,"ASSOC");

                    if ($results === false) {
                        srm_dashboard::log("The report's data source is set as a query, but the used SQL query is not valid. Please return to the data source step, validate your query, and then ensure to click 'Next' to save your input.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", $query, json_encode($_SESSION[Session_report_Settings_key]));
                        throw new Exception("The report's data source is set as a query, but the used SQL query is not valid. Please return to the data source step, validate your choices, and then ensure to click 'Next' to save your input.");
                        return false;
                    } else {
                        foreach ($_SESSION[Session_report_Settings_key]["fields"] as $selected_column) {
                            if (!in_array(strtolower($selected_column), array_map("strtolower",array_keys($results[0])))) {
                                srm_dashboard::log("The report's data source is set as a query, but there are one or more columns not belonging to the SQL query. Please return to the columns step, validate your selection, and then ensure to click 'Next' to save your input.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__,"", log_error, "report_builder_engine", $query, json_encode(array_keys($results["0"])).json_encode($_SESSION[Session_report_Settings_key]));
                                throw new Exception("The report's data source is set as a query, but there are one or more columns not belonging to the SQL query. Please return to the columns step, validate your selection, and then ensure to click 'Next' to save your input.");
                                return false;
                            }
                        }
                    }
                } else {
                    srm_dashboard::log("Can't establish a connection with the database of the report.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                    throw new Exception("Can't establish a connection with the datasource of the report.");
                    return false;
                }
            }
        }


        /*
         * ===================================================================================================================
         *               check for Group By columns that: 
         *                - Group by columns belongs to selected fields
         *                - Group by column count matches formats of Group by column
         *                - If Table part exists in Group By Column then it exists in Selected Table. 
         * ==================================================================================================================
         */

        if (isset($_SESSION[Session_report_Settings_key]["group_by"]) && !empty($_SESSION[Session_report_Settings_key]["group_by"])) {
            foreach ($_SESSION[Session_report_Settings_key]["group_by"] as $group_by) {
                if (!in_array(strtolower($group_by), array_map("strtolower",$_SESSION[Session_report_Settings_key]["fields"]))) {
                    srm_dashboard::log("Return to the grouping step and verify that the chosen group-by columns exists in the selected fields.", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                    throw new Exception("Return to the grouping step and verify that the chosen group-by columns exists in the selected fields. Then, Please click 'Next' to save your selection. ");
                    return false;
                    
                }
            }


            if ((count($_SESSION[Session_report_Settings_key]["table"]) > 1 && !strstr($_SESSION[Session_report_Settings_key]["group_by"][0], ".")) ||
                    (count($_SESSION[Session_report_Settings_key]["table"]) == 1 && strstr($_SESSION[Session_report_Settings_key]["group_by"][0], "."))) {
                srm_dashboard::log("Return to the grouping step and verify that the chosen group-by columns exists in the selected tables. ", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                throw new Exception("Return to the grouping step and verify that the chosen group-by columns exists in the selected tables. ");
                return false;
            }
        }



        /*
         * ===================================================================================================================
         *               check for Sub totals array that: 
         *                - Subtotal Group by and Affected columns belongs to selected fields
         *                - Group by column count matches formats of Group by column
         *                - If Table part exists in Group By Column or affected columns then it exists in Selected Table. 
         * ==================================================================================================================
         */
        if (isset($_SESSION[Session_report_Settings_key]["sub_totals"]) && !empty($_SESSION[Session_report_Settings_key]["sub_totals"])) {
            if ((!in_array(strtolower($_SESSION[Session_report_Settings_key]["sub_totals"]["group_by"]), array_map("strtolower",$_SESSION[Session_report_Settings_key]["fields"]))) ||
                    !empty(array_diff(array_map("strtolower",$_SESSION[Session_report_Settings_key]["sub_totals"]["affected_columns"]), array_map("strtolower",$_SESSION[Session_report_Settings_key]["fields"])))) {
                srm_dashboard::log("Return to the Subtotal step and verify that the chosen group-by column exists in the selected columns. ", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                throw new Exception("Return to the Subtotal step and verify that the chosen group-by column exists in the selected columns, then please make sure to click 'Next' to save your input");
                return false;
            }


            if ((count($_SESSION[Session_report_Settings_key]["table"]) > 1 && !strstr($_SESSION[Session_report_Settings_key]["sub_totals"]["group_by"], ".")) ||
                    (count($_SESSION[Session_report_Settings_key]["table"]) == 1 && strstr($_SESSION[Session_report_Settings_key]["sub_totals"]["group_by"], "."))) {
                srm_dashboard::log("Return to the Subtotal step and verify that the chosen group-by column exists in the selected tables. ", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                throw new Exception("Return to the Subtotal step and verify that the chosen group-by column exists in the selected tables. ");
                return false;
            }
        }

        /*
         * ===================================================================================================================
         *               Check for cells array that: 
         *                - CELLS KEYS  belongs to selected fields
         *                - count of cells = count of selected fields
         *               
         * ==================================================================================================================
         */
        if (isset($_SESSION[Session_report_Settings_key]["cells"]) && !empty($_SESSION[Session_report_Settings_key]["cells"])) {
            if (count($_SESSION[Session_report_Settings_key]["cells"]) != count($_SESSION[Session_report_Settings_key]["fields"]))  {
                srm_dashboard::log("Return to the cells step and verify that it matches the selected columns. ", __FILE__ . "  lINE NO:" . __LINE__, __METHOD__, "", log_error, "report_builder_engine", "", json_encode($_SESSION[Session_report_Settings_key]));
                throw new Exception("Return to the cells step and verify that it matches the selected columns. Then, please click 'Next' to save your selection.");
                return false;
            }
        }

        return true;
    }

    private function intialize_needed_sessions() {
        $array_keys = array("access_groups", "fields", "fields2", "sub_totals", "cells", "conditional_formating", "group_by", "labels", "tables_filters", "table", "sort_by");
        foreach ($array_keys as $key) {
            if (!isset($_SESSION[Session_report_Settings_key][$key])) {
                $_SESSION[Session_report_Settings_key][$key] = array();
            }
        }
    }

}

?>