<?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/index2.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 'location.href = \'' . $strNmDomain . '/home/index2.php?menu=tambah-lpbj&lpbj=' . $strKdLPBJ . '\';';
        echo '</script>';
    }
}


// INPUT DETAIL LPBJ ========================================================================

//if (isset($_POST['+'])) {

if (isset($_FILES['strNmFile']) && $_FILES['strNmFile']['error'] == UPLOAD_ERR_OK) {
    $errors = [];
    $allowed_types = ['image/jpeg', 'image/jpg', 'image/png', 'image/bmp'];
    $file_type = $_FILES['strNmFile']['type'];
    $file_size = $_FILES['strNmFile']['size'];
    $file_tmp = $_FILES['strNmFile']['tmp_name'];
    $file_ext = strtolower(pathinfo($_FILES['strNmFile']['name'], PATHINFO_EXTENSION));

    if (!in_array($file_type, $allowed_types))
        $errors[] = "Only JPG, JPEG, PNG, dan BMP format that allowed.";
    if (empty($errors)) {
        $strNmFile = MD5(uniqid() . date("Y-m-d H:i:s")) . "." . $file_ext;
        $destination = '../../../assets/images/lpbj_pic/' . $strNmFile;
        $thumb_destination = '../../../assets/images/lpbj_pic/thumb_' . $strNmFile;
        $strNmFile = "strNmFile='" . $strNmFile . "', ";
        // echo $strNmFile;
        // die();
        $quality = 90;
        while (true) {
            compressImage($file_tmp, $destination, $quality);
            if (filesize($destination) <= 200000 || $quality <= 10)
                break;
            $quality -= 10;
        }
        createThumbnail($destination, $thumb_destination, 200);
    } else {
        foreach ($errors as $error)
            echo $error . "<br>";
        die("<a href='../../../index2.php?menu=tambah-lpbj&lpbj=" . $_POST['strKdLPBJ'] . "'>back</a>");
    }
} else {
    $strNmFile = "";
}
if (!isset($_POST['dept_ID']) || empty($_POST['dept_ID'])) {
    alertpopup("Pastikan departement sudah ada isinya, jika tidak ada pilihannya /kosong maka kontak IT untuk melengkapi table tpc_dept", "", "../../../index2.php?menu=tambah-lpbj&lpbj=" . $_POST['strKdLPBJ']);
    die();
}
$strEmail = $_POST['Email'];
if ($_POST['strUnit'] == "")
    $strUnit = "PCS";
else $strUnit = $_POST['strUnit'];

if (strpos($_POST['strDeskripsi'], '_') !== false) {
    list($ID, $strDeskripsi) = explode("_", $_POST['strDeskripsi']);
} else {
    $strDeskripsi = $_POST['strDeskripsi'];
}
$query = "SELECT * 
			FROM item 
			WHERE strDeskripsi='" . $strDeskripsi . "' AND 
				  (stsAktif BETWEEN 1 AND 2)
			";
$result = $MySQLi->query($query);
if ($result->num_rows == 0) {
    $strKdItem = MD5(date('Y-m-d H:i:s') . $_POST['strDeskripsi']);
    $query = "INSERT INTO item 
				SET strKdItem='" . $strKdItem . "', 
					strDeskripsi='" . $_POST['strDeskripsi'] . "', 
					strUnit='" . $strUnit . "', 
					dtCreate=NOW(),
					usrCreate='" . $strEmail . "'
				";
    $MySQLi->query($query);
    if ($MySQLi->affected_rows == 1)
        $_SESSION['input_newItem'] = "TRUE";

    $query = "SELECT * 
				FROM item 
				WHERE strKdItem='" . $strKdItem . "' AND 
					  stsAktif=2
				";
    $result = $MySQLi->query($query);
}
$row = $result->fetch_array();
$item_ID = $row['ID'];
//	die($_POST['iLevelREQ']);
if ($_POST['iLevelREQ'] == 3) {
    $stsACC = "stsACCSPV=1, 
				 strKeteranganSPV= 'SPV : OK. " . $_POST['strKeterangan'] . "', 
				";
} elseif ($_POST['iLevelREQ'] == 2) {
    $stsACC = "stsACCSPV=1, 
				 stsACCMGR=1, 
				 strKeteranganSPV= 'SPV : OK. " . $_POST['strKeterangan'] . "', 
				 strKeteranganMGR= 'MGR : OK. " . $_POST['strKeterangan'] . "',  
				";
} else $stsACC = "";

$query = "INSERT INTO tpc_d_lpbj 
			SET item_ID='" . $item_ID . "', 
				item_strUnit='" . $strUnit . "', 
				" . $strNmFile . " 
				dept_ID='" . $_POST['dept_ID'] . "', 
				h_lpbj_ID='" . $_POST['h_lpbj_ID'] . "', 
				iJumlah='" . $_POST['iJumlah'] . "', 
				iJumlahStok='" . $_POST['iJumlahStok'] . "', 
				" . $stsACC . "
				strKeteranganREQ='REQ : " . $_POST['strKeterangan'] . "', 
				dtDibutuhkan='" . $_POST['dtDibutuhkan'] . "', 
				dtCreate=NOW(), 
				usrCreate='" . $_POST['Email'] . "'
 			";
//die($query);
$MySQLi->query($query);
if ($MySQLi->affected_rows == 1) {
    $strKdLPBJ = $_POST['strKdLPBJ'];
    echo '<script type="text/javascript">';
    echo 'location.href = \'http://' . $strNmDomain . '/home/index2.php?menu=tambah-lpbj&lpbj=' . $strKdLPBJ . '\';';
    echo '</script>';
}
//}

/*
// DELETE DETAIL LPBJ ========================================================================
if (isset($_GET['data'])) {
    $query = "UPDATE tpc_d_lpbj 
			SET stsAktif=0, 
				dtUpdate=NOW(), 
				usrUpdate='" . $_SESSION['strEmailREQ'] . "'
			WHERE ID='" . $_GET['data'] . "'
			";
    $MySQLi->query($query);
    if ($MySQLi->affected_rows == 1) {
        $strBuffer = str_replace('"', '`', str_replace("'", "`", $query));
        $query = "INSERT INTO tpc_log 
				SET strPCHostname='" . gethostbyaddr(getIPv4()) . "', 
					strIPv4='" . getIPv4() . "', 
					strPort='" . $_SERVER['REMOTE_PORT'] . "', 
					strTable='tpc_d_lpbj', 
					strQuery='" . $strBuffer . "', 
					strKeterangan='" . $_SESSION['strEmail'] . " Berhasil menghapus detail LPBJ : " . $_SESSION['strKdLPBJ'] . "', 
					dtCreate=NOW(), 
					usrCreate='" . $_SESSION['strEmail'] . "' 
				";
        $MySQLi->query($query);
        echo '<script type="text/javascript">';
        echo 'alert(\'berhasil dihapus \');';
        echo 'location.href = \'http://' . $strNmDomain . '/home/index2.php?menu=tambah-lpbj&lpbj=' . $_SESSION['strKdLPBJ'] . '\';';
        echo '</script>';
    }
}

// tekan submit ========================================== 
if (isset($_GET['lpbj'])) {
    $MySQLi->begin_transaction(); // Mulai transaksi
    try {
        $query = "SELECT * 
				  FROM tpc_h_lpbj  
				  WHERE stsAktif=2 AND 
						strKdLPBJ='" . $MySQLi->real_escape_string($_GET['lpbj']) . "'";
        $result = $MySQLi->query($query);

        if ($result->num_rows == 1) {
            $row = $result->fetch_array();
            $strEmail = $row['employee_Email'];
            $strOldKdLPBJ = $row['strKdLPBJ'];
            $h_lpbj_ID = $row['ID'];
            $strFullNameREQ = $row['strFullNameREQ'];
            $strNmDivisiREQ = $row['strNmDivisiRequestor'];
            $query_h_tambahan = "";
            $query_d_tambahan = "";
            $where = "";
            $stsLevel = "";

            switch ($row['iLevelREQ']) {
                case 2:
                    $where = "Email='" . $row['strMailDIR'] . "' AND FullName='" . $row['strFullNameDIR'] . "'";
                    $query_h_tambahan = ", dtSubmitMGR=NOW(), dtSubmitSPV=NOW() ";
                    $query_d_tambahan = ", stsACCSPV=1, stsACCMGR=1 ";
                    break;
                case 3:
                    $where = "Email='" . $row['strMailMGR'] . "' AND FullName='" . $row['strFullNameMGR'] . "'";
                    $query_h_tambahan = ", dtSubmitSPV=NOW() ";
                    $query_d_tambahan = ", stsACCSPV=1 ";
                    break;
                case 4:
                    $where = "Email='" . $row['strMailSPV'] . "' AND FullName='" . $row['strFullNameSPV'] . "'";
                    break;
            }

            $query = "SELECT FullName, Email, Level 
					  FROM employee 
					  WHERE " . $where;
            $result = $MySQLi->query($query);
            $row = $result->fetch_array();
            switch ($row['Level']) {
                case 1:
                    $stsLevel = "DIR";
                    break;
                case 2:
                    $stsLevel = "MGR";
                    break;
                case 3:
                    $stsLevel = "SPV";
                    break;
            }

            $strFullName = $row['FullName'];
            $strMail = $row['Email'];

            $query = "SELECT strKdLoc
					  FROM employee 
					  WHERE Email='" . $strEmail . "'";
            $result = $MySQLi->query($query);
            $row = $result->fetch_array();
            $strKdLoc = $row['strKdLoc'];

            // Mengunci baris yang dipilih untuk mencegah pembacaan bersamaan
            $query = "SELECT ID, 
							 CAST(SUBSTRING(strKdLPBJ, 12, 2) AS UNSIGNED) AS iNoUrut 
					  FROM tpc_h_lpbj 
					  WHERE strKdLPBJ LIKE '" . $strKdLoc . "-" . date('Ym') . "%' AND 
							stsAktif=1 
					  ORDER BY ID DESC 
					  LIMIT 1 
					  FOR UPDATE";

            $result = $MySQLi->query($query);

            if ($result->num_rows > 0) {
                $row = $result->fetch_array();
                $iNoUrut = $row['iNoUrut'] + 1;
            } else $iNoUrut = 1;

            if ($iNoUrut < 10)
                $strKdLPBJ = $strKdLoc . "-" . date('Ymd') . "0" . $iNoUrut;
            else $strKdLPBJ = $strKdLoc . "-" . date('Ymd') . $iNoUrut;

            //die("session=".$_GET['stsUrgent']." dan ".$stsUrgent);

            $query = "UPDATE tpc_h_lpbj 
					  SET strKdLPBJ='" . $strKdLPBJ . "', 
						  stsAktif=1 
						  " . $query_h_tambahan . " 
					  WHERE strKdLPBJ='" . $strOldKdLPBJ . "' AND 
							stsAktif=2";

            $MySQLi->query($query);
            if ($MySQLi->affected_rows == 1) {
                $strBuffer = str_replace('"', '`', str_replace("'", "`", $query));
                $query = "INSERT INTO tpc_log 
						  SET strPCHostname='" . gethostbyaddr(getIPv4()) . "', 
							  strIPv4='" . getIPv4() . "', 
							  strPort='" . $_SERVER['REMOTE_PORT'] . "', 
							  strTable='tpc_h_lpbj', 
							  strQuery='" . $strBuffer . "', 
							  strKeterangan='" . $strEmail . " membuat LPBJ baru dengan nomor : " . $strKdLPBJ . "', 
							  dtCreate=NOW(), 
							  usrCreate='" . $strEmail . "'";
                $MySQLi->query($query);

                if ($MySQLi->affected_rows == 1) {
                    $query = "UPDATE tpc_d_lpbj SET stsAktif=1 " . $query_d_tambahan . " WHERE stsAktif=2 AND h_lpbj_ID='" . $h_lpbj_ID . "'";
                    $MySQLi->query($query);

                    if ($MySQLi->affected_rows > 0) {
                        if ($_SESSION['input_newItem'] == "TRUE") {
                            $query = "UPDATE item 
									  SET stsAktif=1 
									  WHERE stsAktif=2 AND 
											ID IN (SELECT item_ID FROM tpc_d_lpbj WHERE stsAktif=1 AND h_lpbj_ID='" . $h_lpbj_ID . "')";
                            $MySQLi->query($query);

                            if ($MySQLi->affected_rows > 0) {
                                $strBuffer = str_replace('"', '`', str_replace("'", "`", $query));
                                $query = "INSERT INTO tpc_log 
										  SET strPCHostname='" . gethostbyaddr(getIPv4()) . "', 
											  strIPv4='" . getIPv4() . "', 
											  strPort='" . $_SERVER['REMOTE_PORT'] . "', 
											  strTable='item', 
											  strQuery='" . $strBuffer . "', 
											  strKeterangan='" . $strEmail . " membuat Item baru pada : " . $strKdLPBJ . "', 
											  dtCreate=NOW(), 
											  usrCreate='" . $strEmail . "'";
                                $MySQLi->query($query);
                            }
                        }

                        $query = "DELETE FROM item WHERE stsAktif > 1 AND usrCreate = '" . $strEmail . "'";
                        $MySQLi->query($query);
                        $query = "DELETE FROM tpc_d_lpbj WHERE stsAKtif > 2 AND usrCreate = '" . $strEmail . "'";
                        $MySQLi->query($query);
                        $query = "DELETE FROM tpc_h_lpbj WHERE stsAKtif > 2 AND usrCreate = '" . $strEmail . "'";
                        $MySQLi->query($query);
                    }
                }

                $strHyperlink = "http://" . $strNmDomain . "/home/index2.php?menu=persetujuan-lpbj&session_data=" . $strMail . "&lpbj=" . $strKdLPBJ . "&level=" . $stsLevel;
                $strBody = " <html>
								<body>Dear " . $strFullName . ",<br><br>
									" . $strEmail . " is requesting LPBJ, click link below to review the items :<br>
									<a href='" . $strHyperlink . "'>click me</a><br><br>
									Happy " . date('l', strtotime(date('Y-m-d'))) . "<br><br>
									Best Regards,<br>
									" . $strFullNameREQ . "<br>
									" . $strNmDivisiREQ . "
								</body>
							 </html>";
                $strSubject = "[LPBJ] Approval Request for LPBJ No :" . $strKdLPBJ;
                // die($strHyperlink); 
                $strMailTo = [$strMail . "#" . $strFullName];
                if (kirim_email($strMailTo, "", "", $strSubject, $strBody) && filter_var($strMail, FILTER_VALIDATE_EMAIL)) {
                    echo '<script type="text/javascript">';
                    echo 'alert(\'Request telah diemailkan ke atasan anda\');';
                    echo 'location.href = \'http://' . $strNmDomain . '/home/index2.php\';';
                    echo '</script>';
                }
            }

            $MySQLi->commit(); // Komit transaksi jika semua query berhasil
        }
    } catch (Exception $e) {
        $MySQLi->rollback(); // Rollback transaksi jika ada error
        throw $e; // Lempar kembali error untuk penanganan lebih lanjut
    }
}

*/