<?php

include_once('mysqli_array.inc.php');

$host = 'localhost';
$user = 'root';
$password = '';
$db = 'the_core_gt';

$connection_wbs = array('server' => 'gtdb02.transactelgroup.com',
    'user' => 'TheCoreWS',
    'password' => 'U53r7h3C0r3W5',
    'database' => 'db_thecore');



$query_wbs = "SELECT vfn.val_value AS 'first', IFNULL(vsn.val_value,'') AS 'last', CASE vgn.val_value WHEN 1 THEN 'female' WHEN 2 THEN 'male' ELSE '' END AS 'gender',
                            vbt.val_value AS 'birthday',
                            vem.val_value AS 'email',
                            p.pos_id AS 'position_id', IFNULL(su.uni_id, u.uni_id) AS 'superunit_id', TRIM(vwl.val_value) AS 'NickName', TRIM(vwl.val_value) AS 'network_login',
                            u.uni_id AS 'unit_id',
                            vsp.val_value AS 'supervisor',
                            p.pos_name AS 'position', IFNULL(su.uni_name, u.uni_name) AS 'superunit',
                            u.uni_name AS 'unit', CONCAT(IFNULL(vfn.val_value,''), ' ', IFNULL(vln.val_value,''), ' ', IFNULL(vsn.val_value,''), ' ', IFNULL(vsl.val_value,'')) AS 'full_name',
                            vpem.val_value AS 'personal_email', IFNULL(vis.val_value,0) AS 'Is_sup',
                            vst.val_value AS 'Status',
                            c.cit_name AS 'city',
                            e.emp_id AS 'VHUR',
                            vhd.val_value AS 'hire_date',
                            vgi.val_value AS 'generic_id',
                            vdm.val_value AS 'domain_id', CASE vdm.val_value WHEN 3 THEN 'SV' WHEN 2 THEN 'TRTEL' WHEN 1 THEN 'TI' ELSE '' END AS 'domain',
                            vaem.val_value AS 'account_email'
                    FROM emp_employees e
                    LEFT JOIN val_values vfn ON e.emp_id = vfn.val_emp_id AND vfn.val_pro_id = 3 AND vfn.val_active = 1
                    LEFT JOIN val_values vln ON e.emp_id = vln.val_emp_id AND vln.val_pro_id = 4 AND vln.val_active = 1
                    LEFT JOIN val_values vsn ON e.emp_id = vsn.val_emp_id AND vsn.val_pro_id = 5 AND vsn.val_active = 1
                    LEFT JOIN val_values vsl ON e.emp_id = vsl.val_emp_id AND vsl.val_pro_id = 6 AND vsl.val_active = 1
                    LEFT JOIN val_values vgn ON e.emp_id = vgn.val_emp_id AND vgn.val_pro_id = 31 AND vgn.val_active = 1
                    LEFT JOIN val_values vbt ON e.emp_id = vbt.val_emp_id AND vbt.val_pro_id = 29 AND vbt.val_active = 1
                    LEFT JOIN val_values vem ON e.emp_id = vem.val_emp_id AND vem.val_pro_id = 9 AND vem.val_active = 1
                    LEFT JOIN val_values vps ON e.emp_id = vps.val_emp_id AND vps.val_pro_id = 20 AND vps.val_active = 1
                    LEFT JOIN val_values vun ON e.emp_id = vun.val_emp_id AND vun.val_pro_id = 18 AND vun.val_active = 1
                    LEFT JOIN val_values vwl ON e.emp_id = vwl.val_emp_id AND vwl.val_pro_id = 10 AND vwl.val_active = 1
                    LEFT JOIN val_values vsp ON e.emp_id = vsp.val_emp_id AND vsp.val_pro_id = 21 AND vsp.val_active = 1
                    LEFT JOIN val_values vpem ON e.emp_id = vpem.val_emp_id AND vpem.val_pro_id = 25 AND vpem.val_active = 1
                    LEFT JOIN val_values vis ON e.emp_id = vis.val_emp_id AND vis.val_pro_id = 48 AND vis.val_active = 1
                    LEFT JOIN val_values vst ON e.emp_id = vst.val_emp_id AND vst.val_pro_id = 14 AND vst.val_active = 1
                    LEFT JOIN val_values vhd ON e.emp_id = vhd.val_emp_id AND vhd.val_pro_id = 15 AND vhd.val_active = 1
                    LEFT JOIN val_values vgi ON e.emp_id = vgi.val_emp_id AND vgi.val_pro_id = 84 AND vgi.val_active = 1
                    LEFT JOIN val_values vdm ON e.emp_id = vdm.val_emp_id AND vdm.val_pro_id = 88 AND vdm.val_active = 1
                    LEFT JOIN val_values vaem ON e.emp_id = vaem.val_emp_id AND vaem.val_pro_id = 46 AND vaem.val_active = 1
                    LEFT JOIN val_values vct ON e.emp_id = vct.val_emp_id AND vct.val_pro_id = 27 AND vct.val_active = 1
                    LEFT OUTER JOIN cit_cities c ON vct.val_value = c.CIT_ID
                    LEFT OUTER JOIN uni_units u ON u.uni_id= vun.val_value
                    LEFT OUTER JOIN uni_units su ON u.uni_parent_uni_id = su.uni_id
                    LEFT OUTER JOIN pos_positions p ON p.pos_id = vps.val_value
                    WHERE e.emp_active = 1
                    GROUP BY e.emp_id";


$result_wbs = mysqli_array($query_wbs, null, 2, $connection_wbs);
echo json_encode($result_wbs);
?>