<?php
session_start();
include_once("../../../../../lib/konfig.php");
include_once("../../../../../lib/function.php");
$MySQLi = new MySQLi($strDBHostName, $strDBUsername, $strDBPassword, $strDBName);
if ($_SESSION['tambah-lpbj'] == "TRUE") {
    $_SESSION['tambah-lpbj'] = "FALSE";
    $_SESSION['input_newItem'] = "FALSE";
    $strEmail = $_SESSION['strEmail'];
    $query = "WITH RECURSIVE ApprovalHierarchy AS 
			( 
				SELECT ID, FullName, Division, Email, Level, ApprovalLine, Level AS Input_Level, 1 AS depth 
				FROM employee 
				WHERE Email = '" . $strEmail . "' 
				UNION ALL 
			
				SELECT t.ID, t.FullName, t.Division, t.Email, t.Level, t.ApprovalLine, a.Input_Level, a.depth + 1 
				FROM employee t 
				JOIN ApprovalHierarchy a ON t.ID = a.ApprovalLine 
				WHERE a.depth < 4 
			) 
			SELECT 
				MAX(CASE WHEN h.Level = 1 THEN h.ID END) AS ID_direktur, 
				MAX(CASE WHEN h.Level = 1 THEN h.FullName END) AS nama_direktur, 
				MAX(CASE WHEN h.Level = 1 THEN h.Email END) AS email_direktur, 
				MAX(CASE WHEN h.Level = 2 THEN h.ID END) AS ID_manager, 
				MAX(CASE WHEN h.Level = 2 THEN h.FullName END) AS nama_manager, 
				MAX(CASE WHEN h.Level = 2 THEN h.Email END) AS email_manager, 
				MAX(CASE WHEN h.Level = 3 THEN h.ID END) AS ID_supervisor, 
				MAX(CASE WHEN h.Level = 3 THEN h.FullName END) AS nama_supervisor, 
				MAX(CASE WHEN h.Level = 3 THEN h.Email END) AS email_supervisor, 
				MAX(CASE WHEN h.Level = 4 THEN h.ID END) AS ID_staff, 
				MAX(CASE WHEN h.Level = 4 THEN h.FullName END) AS nama_staff, 
				MAX(CASE WHEN h.Level = 4 THEN h.Email END) AS email_staff, 
				MAX(CASE WHEN h.Email= '" . $strEmail . "' THEN h.Division END) AS Divisi, 
				MAX(h.Input_Level) AS Input_Level 
			FROM ApprovalHierarchy h;"; //die($query);
    $result = $MySQLi->query($query);
    if ($result->num_rows > 0) {
        $row = $result->fetch_array();
        $strDivisi = $row['Divisi'];
        switch ($row['Input_Level']) {
            case 2:
                $strMailDIR = $row['email_direktur'];
                $strMailMGR = $row['email_manager'];
                $strMailSPV = $row['email_manager'];
                $strFullNameDIR = $row['nama_direktur'];
                $strFullNameMGR = $row['nama_manager'];
                $strFullNameSPV = $row['nama_manager'];
                $strFullNameREQ = $row['nama_manager'];
                break;
            case 3:
                $strMailDIR = $row['email_direktur'];
                $strFullNameDIR = $row['nama_direktur'];
                if ($row['email_manager'] == "") {
                    $strMailMGR = $row['email_direktur'];
                    $strMailSPV = $row['email_supervisor'];
                    $strFullNameMGR = $row['nama_direktur'];
                    $strFullNameSPV = $row['nama_supervisor'];
                    $strFullNameREQ = $row['nama_supervisor'];
                } else {
                    $strMailMGR = $row['email_manager'];
                    $strMailSPV = $row['email_supervisor'];
                    $strFullNameMGR = $row['nama_manager'];
                    $strFullNameSPV = $row['nama_supervisor'];
                    $strFullNameREQ = $row['nama_supervisor'];
                }
                break;
            case 4:
                $strMailDIR = $row['email_direktur'];
                $strFullNameDIR = $row['nama_direktur'];
                if ($row['email_supervisor'] == "") {
                    if ($row['email_manager'] == "") {
                        $strMailMGR = $row['email_direktur'];
                        $strMailSPV = $row['email_direktur'];
                        $strFullNameMGR = $row['nama_direktur'];
                        $strFullNameSPV = $row['nama_direktur'];
                    } else {
                        $strMailMGR = $row['email_manager'];
                        $strMailSPV = $row['email_manager'];
                        $strFullNameMGR = $row['nama_manager'];
                        $strFullNameSPV = $row['nama_manager'];
                    }
                } else {
                    if ($row['email_manager'] == "") {
                        $strMailMGR = $row['email_direktur'];
                        $strMailSPV = $row['email_supervisor'];
                        $strFullNameMGR = $row['nama_direktur'];
                        $strFullNameSPV = $row['nama_supervisor'];
                    } else {
                        $strMailMGR = $row['email_manager'];
                        $strMailSPV = $row['email_supervisor'];
                        $strFullNameMGR = $row['nama_manager'];
                        $strFullNameSPV = $row['nama_supervisor'];
                    }
                }
                $strFullNameREQ = $row['nama_staff'];
                break;
        }
    } else {
        echo '<script type="text/javascript">';
        echo 'alert(\'ERROR : USER EMAIL NOT FOUND \');';
        echo 'location.href = \'' . $strNmDomain . 'home/index.php?menu=tambah-lpbj\';';
        echo '</script>';
    }
    $strKdLPBJ = MD5(date('Y-m-d H:i:s') . $strEmail);
    //$strKdLPBJ = date('Y-m-d H:i:s') . $strEmail;
    $query = "INSERT INTO tpc_h_lpbj 
			SET strKdLPBJ='" . $strKdLPBJ . "', 
				employee_Email='" . $strEmail . "', 
				strFullNameREQ='" . $strFullNameREQ . "', 
				strMailDIR='" . $strMailDIR . "', 
				strFullNameDIR='" . $strFullNameDIR . "', 
				strMailMGR='" . $strMailMGR . "', 
				strFullNameMGR='" . $strFullNameMGR . "', 
				strMailSPV='" . $strMailSPV . "', 
				strFullNameSPV='" . $strFullNameSPV . "', 
				strNmDivisiRequestor='" . $strDivisi . "', 
				iLevelREQ='" . $row['Input_Level'] . "', 
				dtCreate=NOW(), 
				usrCreate='" . $strEmail . "'";
    $MySQLi->query($query);
    if ($MySQLi->affected_rows == 1) {
        echo '<script type="text/javascript">';
        echo 'location.href = \'http://' . $strNmDomain . '/home/index2.php?menu=tambah-lpbj&lpbj=' . $strKdLPBJ . '\';';
        echo '</script>';
    }
}
