<?php
session_start();
include_once("../../../../../lib/konfig.php");
include_once("../../../../../lib/function.php");
$MySQLi = new MySQLi($strDBHostName, $strDBUsername, $strDBPassword, $strDBName);

if (isset($_POST['lpbj'])) {
    $MySQLi->begin_transaction(); // Mulai transaksi
    try {
        $query = "SELECT * 
				  FROM tpc_h_lpbj  
				  WHERE stsAktif=2 AND 
						strKdLPBJ='" . $MySQLi->real_escape_string($_POST['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 . ",stsUrgent = " . $_POST['stsUrgent'] . " 
					  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/index.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/index.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
    }
}
