<?php
    require_once('connection.class.php');
    $connection = new connection();
    $conn = $connection->getConnection();

    if (!isset($conn)) {
        return json_encode(array("Error" => "Connection couldn't be stablished"));
    }

    $stmt = "SELECT e.employee_global AS employee_global,
                    e.first_name AS first_name,
                    e.first_surname AS first_surname,
                    CONCAT_WS(' ', first_name, second_name, first_surname, second_surname) AS full_name,
                    per_info.gender AS gender,
                    per_info.birthdate AS birthdate,
                    e.company_email AS company_email,
                    e.company_email AS email,
                    e.second_company_email AS second_email,
                    e.domain_username AS username,
                    pos.name AS pos_name,
                    pos_wd.name AS pos_name_wd,
                    emp_subunit.position_id AS position_id, IFNULL(unit.name,unit_wd.name) AS unit_name,
                    unit_wd.name AS unit_name_wd, IFNULL(emp_subunit.unit_id, emp_subunit.unit_id_wd) AS unit_id,
                    emp_subunit.unit_id_wd AS unit_id_wd, IFNULL(subunit.name,subunit_wd.name) AS subunit_name,
                    subunit_wd.name AS subunit_name_wd, IFNULL(emp_subunit.subunit_id, emp_subunit.subunit_id_wd) AS subunit_id,
                    emp_subunit.subunit_id_wd AS subunit_id_wd,
                    ope_info.supervisor_id AS supervisor_id,
                    per_info.personal_email AS personal_email,
                    e.is_active AS is_active,
                    e.vhur AS vhur,

                    e.legacy_id AS legacy_id,
                    emp_subunit.position_id_wd AS position_id_wd
            FROM employee AS e
            LEFT JOIN employee_operation_info AS ope_info ON (e.employee_global = ope_info.employee_id)
            LEFT JOIN employee_personal_info AS per_info ON (e.employee_global = per_info.employee_id)
            LEFT JOIN employee_subunit AS emp_subunit ON (e.employee_global = emp_subunit.employee_id)
            LEFT JOIN posicion AS pos ON (emp_subunit.position_id = pos.id)
            LEFT JOIN posicion AS pos_wd ON (emp_subunit.position_id_wd = pos_wd.id)
            LEFT JOIN unit AS unit ON (emp_subunit.unit_id = unit.id)
            LEFT JOIN unit AS unit_wd ON (emp_subunit.unit_id_wd = unit_wd.id)
            LEFT JOIN subunit AS subunit ON (emp_subunit.subunit_id = subunit.id)
            LEFT JOIN subunit AS subunit_wd ON (emp_subunit.subunit_id_wd = subunit_wd.id)
            WHERE e.is_active = 1 ";

    /* Perform Query */
    $result = mysql_query($stmt, $conn);
    if (!$result) {
        $sError = mysql_error($conn);
       echo json_encode(array("Invalid query" => $sError));
    }

    /* Use result */
    $resp = array();
    if ($result) {
        /* Build array with the information */
        while ($row = mysql_fetch_assoc($result)) {
            $network_login = strongParseString($row['username']);
            $resp[] = array(
                'employee_global' => $row['employee_global'],
                'first_name' => strongParseString($row['first_name']),
                'first_surname' => strongParseString($row['first_surname']),
                'full_name' => strongParseString($row['full_name']),
                'gender' => strongParseString($row['gender']),
                'birthdate' => strongParseString($row['birthdate']),
                'company_email' => strongParseString($row['company_email']),
                'email' => strongParseString($row['email']),
                'second_email' => strongParseString($row['second_email']),
                'username' => $network_login,
                'pos_name' => strongParseString($row['pos_name']),
                'position_id' => $row['position_id'],
                'unit_name' => strongParseString($row['unit_name']),
                'unit_id' => $row['unit_id'],
                'subunit_name' => strongParseString($row['subunit_name']),
                'subunit_id' => $row['subunit_id'],
                'supervisor_id' => $row['supervisor_id'],
                'personal_email' => strongParseString($row['personal_email']),
                'is_active' => $row['is_active'],
                'vhur' => preg_replace("/[^0-9]/","",  $row['vhur']),
                'legacy_id' => $row['legacy_id'],
                'pos_name_wd' => strongParseString($row['pos_name_wd']),
                'position_id_wd' => $row['position_id_wd'],
                'unit_name_wd' => strongParseString($row['unit_name_wd']),
                'unit_id_wd' => $row['unit_id_wd'],
                'subunit_name_wd' => strongParseString($row['subunit_name_wd']),
                'subunit_id_wd' => $row['subunit_id_wd']
            );
        }
    }
    mysql_free_result($result);
    mysql_close($conn);
    echo json_encode($resp, JSON_HEX_AMP); /* Return the array in json format */

    function strongParseString($sString) {
        return htmlspecialchars(str_replace("\0", "", str_replace("&#x0", "", mb_convert_encoding($sString, "UTF-8"))));
    }
?>