<?php
namespace classs;
use PDO;
use Exception;
class YomyaTransaction
{
    private $con;
    public $IdYomya;

    public function __construct($con)
    {
        $this->con = $con;
    }
/**
 * إجراء معاملة مالية.
 *
 * @param string $mydate تاريخ المعاملة.
 * @param float $all_cash مجموع المبالغ المالية.
 * @param string $ml7oza وصف المعاملة.
 * @param int $sessionUser معرف المستخدم الحالي.
 * @param array $cash_f المحفظة/الحساب مدين.
 * @param array $lv3f المستوى 3 (فئة) دائن.
 * @param array $cash_t المحفظة/الحساب مدين.
 * @param array $lv3t المستوى 3 (فئة) دائن.
 * @param int|null $id_currency_codes معرف العملة (إذا كان متوفرًا).
 * @param float|null $currencyExchangeRate سعر صرف العملة (إذا كان متوفرًا).
 * @param array|null $cost_center_code_f مركز التكلفة مدين (إذا كان متوفرًا).
 * @param array|null $cost_center_code_t مركز التكلفة دائن (إذا كان متوفرًا).
 *
 * @return int|bool رقم المعاملة في حالة النجاح، أو `false` في حالة الفشل.
 */
    public function insertTransaction($mydate, $all_cash, $ml7oza, $sessionUser, $cash_f, $lv3f, $cash_t, $lv3t, $id_currency_codes = NULL, $currencyExchangeRate = NULL, $cost_center_code_f = NULL, $cost_center_code_t = NULL)
    {
        try {
            $this->con->beginTransaction();

            // تحديد العام الحالي
            $currentYear = date("Y");

            // استعلام للحصول على آخر رقم في registration_number للعام الحالي
            $sqlLastRegistrationNumber = "SELECT MAX(registration_number) AS last_registration_number
            FROM yomya
            WHERE YEAR(dateinsert) = :currentYear";

            $stmtLastRegistrationNumber = $this->con->prepare($sqlLastRegistrationNumber);
            $stmtLastRegistrationNumber->bindParam(":currentYear", $currentYear, PDO::PARAM_INT);
            $stmtLastRegistrationNumber->execute();

            $resultLastRegistrationNumber = $stmtLastRegistrationNumber->fetch(PDO::FETCH_ASSOC);
            if (empty($resultLastRegistrationNumber['last_registration_number'])) {
                $last_registration_number = 1;
            } else {
                $last_registration_number = $resultLastRegistrationNumber['last_registration_number'] + 1;
            }

            // تأمين البيانات المستلمة من المستخدم
            $mydate = htmlspecialchars(strip_tags($mydate));
            $all_cash = htmlspecialchars(strip_tags($all_cash));
            $ml7oza = htmlspecialchars(strip_tags($ml7oza));
            $sessionUser = htmlspecialchars(strip_tags($sessionUser));

            

            if ($id_currency_codes != 1) {
                $all_cash = $all_cash * $currencyExchangeRate;
            }
            
            // إدراج البيانات في جدول yomya
            $sql = "INSERT INTO yomya (registration_number, dateinsert, daen, madeen, payan, UserID_Insert) VALUES
                (:registration_number, :zdateinsert, :zdaen, :zmadeen, :payan, :zUserID_Insert)";
            $stmt = $this->con->prepare($sql);
            $stmt->execute(
                array(
                    'registration_number' => $last_registration_number,
                    'zdateinsert' => $mydate,
                    'zdaen' => $all_cash,
                    'zmadeen' => $all_cash,
                    'payan' => $ml7oza,
                    'zUserID_Insert' => $sessionUser
                )
            );
            if ($stmt->rowCount() >= 1) {
                $id_yomya = $this->con->lastInsertId();
            }
            // تأمين البيانات المستلمة من المستخدم
            $cash_f = $this->sanitizeArray($cash_f);
            $lv3f = $this->sanitizeArray($lv3f);

            $cash_t = $this->sanitizeArray($cash_t);
            $lv3t = $this->sanitizeArray($lv3t);

            // إدراج البيانات في جدول details_yomya للحسابات الدائنة
            $this->insertDetails($id_yomya, $cash_f, $lv3f, $mydate, 1, $id_currency_codes, $currencyExchangeRate, $cost_center_code_f);

            // إدراج البيانات في جدول details_yomya للحسابات المدينة
            $this->insertDetails($id_yomya, $cash_t, $lv3t, $mydate, 2, $id_currency_codes, $currencyExchangeRate, $cost_center_code_t);
            $this->IdYomya = $id_yomya;
            $this->con->commit();
            return $id_yomya; // إذا تمت جميع العمليات بنجاح
        } catch (Exception $e) {
            // يمكنك إضافة رسالة خطأ إلى سجل الأخطاء أو استخدامها بطريقة أخرى حسب احتياجاتك
            error_log("Error: " . $e->getMessage(), 0);
            $this->con->rollBack();
            return false; // في حالة حدوث خطأ
        }
    }

    public function updateTransaction($id_yomya, $mydate, $all_cash, $ml7oza, $sessionUser, $cash_f, $lv3f, $cash_t, $lv3t, $id_currency_codes = NULL, $currencyExchangeRate = NULL, $cost_center_code_f = NULL, $cost_center_code_t = NULL)
    {
        try {
            $this->con->beginTransaction();

            // تحديد العام الحالي
            $currentYear = date("Y");

            // استعلام للحصول على آخر رقم في registration_number للعام الحالي
            $sqlLastRegistrationNumber = "SELECT MAX(registration_number) AS last_registration_number
            FROM yomya
            WHERE YEAR(dateinsert) = :currentYear";

            $stmtLastRegistrationNumber = $this->con->prepare($sqlLastRegistrationNumber);
            $stmtLastRegistrationNumber->bindParam(":currentYear", $currentYear, PDO::PARAM_INT);
            $stmtLastRegistrationNumber->execute();

            $resultLastRegistrationNumber = $stmtLastRegistrationNumber->fetch(PDO::FETCH_ASSOC);
            if (empty($resultLastRegistrationNumber['last_registration_number'])) {
                $last_registration_number = 1;
            } else {
                $last_registration_number = $resultLastRegistrationNumber['last_registration_number'] + 1;
            }

            // تأمين البيانات المستلمة من المستخدم
            $mydate = htmlspecialchars(strip_tags($mydate));
            $all_cash = htmlspecialchars(strip_tags($all_cash));
            $ml7oza = htmlspecialchars(strip_tags($ml7oza));
            $sessionUser = htmlspecialchars(strip_tags($sessionUser));

            

            if ($id_currency_codes != 1) {
                $all_cash = $all_cash * $currencyExchangeRate;
            }
            
            // إدراج البيانات في جدول yomya
            $sql = "UPDATE yomya SET dateinsert = :zdateinsert, daen = :zdaen, madeen = :zmadeen, payan = :payan, UserIDEdite = :UserIDEdite WHERE id_yomya = :id_yomya";
            $stmt = $this->con->prepare($sql);
            $stmt->execute(array(
                'zdateinsert' => $mydate,
                'zdaen' => $all_cash,
                'zmadeen' => $all_cash,
                'payan' => $ml7oza,
                'UserIDEdite' => $sessionUser,
                'id_yomya' => $id_yomya
            ));
            /* if ($stmt->rowCount() >= 1) {
                $id_yomya = $this->con->lastInsertId();
            } */
            // تأمين البيانات المستلمة من المستخدم

            $deletePreviousQuery = "DELETE FROM details_yomya WHERE id_yomya = :id_yomya";
            $deletePreviousStmt = $this->con->prepare($deletePreviousQuery);
            $deletePreviousStmt->bindParam(':id_yomya', $id_yomya);
            $deletePreviousStmt->execute();

            $cash_f = $this->sanitizeArray($cash_f);
            $lv3f = $this->sanitizeArray($lv3f);

            $cash_t = $this->sanitizeArray($cash_t);
            $lv3t = $this->sanitizeArray($lv3t);

            // إدراج البيانات في جدول details_yomya للحسابات الدائنة
            $this->insertDetails($id_yomya, $cash_f, $lv3f, $mydate, 1, $id_currency_codes, $currencyExchangeRate, $cost_center_code_f);

            // إدراج البيانات في جدول details_yomya للحسابات المدينة
            $this->insertDetails($id_yomya, $cash_t, $lv3t, $mydate, 2, $id_currency_codes, $currencyExchangeRate, $cost_center_code_t);
            $this->IdYomya = $id_yomya;
            $this->con->commit();
            return $id_yomya; // إذا تمت جميع العمليات بنجاح
        } catch (Exception $e) {
            // يمكنك إضافة رسالة خطأ إلى سجل الأخطاء أو استخدامها بطريقة أخرى حسب احتياجاتك
            error_log("Error: " . $e->getMessage(), 0);
            $this->con->rollBack();
            // return false; // في حالة حدوث خطأ
            return "حدث خطأ أثناء التعديل: " . $e->getMessage();; 
        }
    }
    private function insertDetails($id_yomya, $cashArray, $lv3Array, $date, $fromOrTo, $id_currency_codes = NULL, $currencyExchangeRate = NULL, $cost_center_code = NULL)
    {
        if (!is_array($cashArray)) {
            $cashArray = [$cashArray];
            $lv3Array = [$lv3Array];
            $cost_center_code = [$cost_center_code];
        }
        foreach ($cashArray as $key => $cash) {
            if (!empty($cash)) {
                // تأمين البيانات المستلمة من المستخدم
                /* $lv5 = htmlspecialchars(strip_tags($lv5Array[$key]));
                $lv4 = htmlspecialchars(strip_tags($lv4Array[$key])); */
                if ($lv3Array[$key] != 'off') {
                    $id_lv = htmlspecialchars(strip_tags($lv3Array[$key]));
                if (!empty($cost_center_code[$key])) {
                    $id_cc_lv = $cost_center_code[$key];
                } else {
                    $id_cc_lv = 0;
                }
                $cc_lv = 0;
                $cost_code = 0;
                if (!empty($cost_center_code)) {
                    $cc_lv = getId('cost_centers', 'ID', $id_cc_lv, 'cc_lv');
                    $cost_code = getId('cost_centers', 'ID', $id_cc_lv, 'cost_center_code');
                }
                $currencyExchangeRateUSD = getId('currency','ID', 2,'currencyExchangeRate');
                if ($currencyExchangeRateUSD > 0) {
                    $currencyExchangeRateEGP = 1 / $currencyExchangeRateUSD;
                }
                
                if ($id_currency_codes != 1 ) {
                    $amountSold = $cash;
                    $cash = $cash * $currencyExchangeRate;
                } else {
                    $currencyExchangeRate = $currencyExchangeRateEGP;
                    $amountSold = NULL;
                }
                $lv = getName('level_acc', 'ID', $id_lv, 'lev_acc');
                if ($lv == 1) {
                    $lv5 = '';
                    $lv4 = '';
                    $lv3 = '';
                    $lv2 = '';
                    $lv1 = $id_lv;
                } elseif($lv == 2){
                    $lv5 = '';
                    $lv4 = '';
                    $lv3 = '';
                    $lv2 = $id_lv;
                    $lv1 = getName('level_acc', 'ID', $id_lv, 'acc_up');
                } elseif($lv == 3){
                    $lv5 = '';
                    $lv4 = '';
                    $lv3 = $id_lv;
                    $lv2 = getName('level_acc', 'ID', $id_lv, 'acc_up');
                    $lv1 = getName('level_acc', 'ID', $lv2, 'acc_up');
                } elseif($lv == 4){
                    $lv5 = '';
                    $lv4 = $id_lv;
                    $lv3 = getName('level_acc', 'ID', $id_lv, 'acc_up');
                    $lv2 = getName('level_acc', 'ID', $lv3, 'acc_up');
                    $lv1 = getName('level_acc', 'ID', $lv2, 'acc_up');
                } elseif($lv == 5){
                    $lv5 = $id_lv;
                    $lv4 = getName('level_acc', 'ID', $id_lv, 'acc_up');
                    $lv3 = getName('level_acc', 'ID', $lv4, 'acc_up');
                    $lv2 = getName('level_acc', 'ID', $lv3, 'acc_up');
                    $lv1 = getName('level_acc', 'ID', $lv2, 'acc_up');
                }

                $codeAccount = $this->getCodeAccount($lv5, $lv4, $lv3, $lv2, $lv1);

                // إدراج البيانات في جدول details_yomya
                $sql = "INSERT INTO details_yomya (id_yomya, from_or_to, cc_lv, cost_center_code, lv5, lv1, lv2, lv3, lv4, id_currency_codes, amountSold, currencyExchangeRate, cash, dateinsert) VALUES
                        (:zid_yomya, :zfrom_or_to, :cc_lv, :cost_center_code, :zlv5, :zlv1, :zlv2, :zlv3, :zlv4, :id_currency_codes, :amountSold, :currencyExchangeRate, :zcash, :zdateinsert)";

                $stmt = $this->con->prepare($sql);
                $stmt->execute(
                    array(
                        'zid_yomya' => $id_yomya,
                        'zfrom_or_to' => $fromOrTo,
                        'cc_lv' => $cc_lv,
                        'cost_center_code' => $cost_code,
                        'zlv5' => $codeAccount['lv5'],
                        'zlv1' => $codeAccount['lv1'],
                        'zlv2' => $codeAccount['lv2'],
                        'zlv3' => $codeAccount['lv3'],
                        'zlv4' => $codeAccount['lv4'],
                        'zcash' => $cash,
                        'id_currency_codes' => $id_currency_codes,
                        'amountSold' => $amountSold,
                        'currencyExchangeRate' => $currencyExchangeRate,
                        'zdateinsert' => $date
                    )
                );
                }
                
            }
        }
    }

    public function insertSanad(
        $id_sanad_type,
        $id_payment_methods,
        $mydate,
        $id_yomya,
        $name,
        $national_ID,
        $id_currency_codes,
        $amountSold,
        $currencyExchangeRate,
        $cash,
        $payan,
        $NO_check,
        $name_bank,
        $user_id_insert
    )
    {
        try {
            $this->con->beginTransaction();
            // تحديد العام الحالي
            $currentYear = date("Y");

            // استعلام للحصول على آخر رقم في registration_number للعام الحالي
            $sqlLastRegistrationNumber = "SELECT MAX(id_sanadat) AS last_id_sanadat
            FROM sanadat
            WHERE YEAR(mydate) = :currentYear";

            $stmtLastRegistrationNumber = $this->con->prepare($sqlLastRegistrationNumber);
            $stmtLastRegistrationNumber->bindParam(":currentYear", $currentYear, PDO::PARAM_INT);
            $stmtLastRegistrationNumber->execute();

            $resultLastRegistrationNumber = $stmtLastRegistrationNumber->fetch(PDO::FETCH_ASSOC);
            if (empty($resultLastRegistrationNumber['last_id_sanadat'])) {
                $last_id_sanadat = 1;
            } else {
                $last_id_sanadat = $resultLastRegistrationNumber['last_id_sanadat'] + 1;
            }
            $HotelBooking = "INSERT INTO sanadat (id_sanad_type,
                                                id_sanadat,
                                                id_payment_methods,
                                                mydate,
                                                id_yomya,
                                                name,
                                                national_ID,
                                                id_currency_codes,
                                                amountSold,
                                                currencyExchangeRate,
                                                cash,
                                                payan,
                                                NO_check,
                                                name_bank,
                                                user_id_insert) 
                                    VALUES (:id_sanad_type,
                                                :id_sanadat,
                                                :id_payment_methods,
                                                :mydate,
                                                :id_yomya,
                                                :name,
                                                :national_ID,
                                                :id_currency_codes,
                                                :amountSold,
                                                :currencyExchangeRate,
                                                :cash,
                                                :payan,
                                                :NO_check,
                                                :name_bank,
                                                :user_id_insert) ";
            $HotelBookingStmt = $this->con->prepare($HotelBooking);
            $HotelBookingStmt->bindParam(':id_sanad_type', $id_sanad_type);
            $HotelBookingStmt->bindParam(':id_sanadat', $last_id_sanadat);
            $HotelBookingStmt->bindParam(':id_payment_methods', $id_payment_methods);
            $HotelBookingStmt->bindParam(':mydate', $mydate);
            $HotelBookingStmt->bindParam(':id_yomya', $id_yomya);
            $HotelBookingStmt->bindParam(':name', $name);
            $HotelBookingStmt->bindParam(':national_ID', $national_ID);
            $HotelBookingStmt->bindParam(':id_currency_codes', $id_currency_codes);
            $HotelBookingStmt->bindParam(':amountSold', $amountSold);
            $HotelBookingStmt->bindParam(':currencyExchangeRate', $currencyExchangeRate);
            $HotelBookingStmt->bindParam(':cash', $cash);
            $HotelBookingStmt->bindParam(':payan', $payan);
            $HotelBookingStmt->bindParam(':NO_check', $NO_check);
            $HotelBookingStmt->bindParam(':name_bank', $name_bank);
            $HotelBookingStmt->bindParam(':user_id_insert', $user_id_insert);
            $HotelBookingStmt->execute();
            $hotel_bookings_id = $this->con->lastInsertId();

            $this->con->commit();
            return $hotel_bookings_id; // إذا تمت جميع العمليات بنجاح
        } catch (Exception $e) {
            // يمكنك إضافة رسالة خطأ إلى سجل الأخطاء أو استخدامها بطريقة أخرى حسب احتياجاتك
            error_log("Error: " . $e->getMessage(), 0);
            $this->con->rollBack();
            return false; // في حالة حدوث خطأ
        }
    }
    

    private function getCodeAccount($lv5, $lv4, $lv3, $lv2, $lv1)
    {

        $sql = "SELECT * FROM level_acc WHERE ID = :id";
        $stmt = $this->con->prepare($sql);

        if (!empty($lv5)) {
            $stmt->bindParam(':id', $lv5);
        } elseif (!empty($lv4)) {
            $stmt->bindParam(':id', $lv4);
        } elseif (!empty($lv3)) {
            $stmt->bindParam(':id', $lv3);
        } elseif (!empty($lv2)) {
            $stmt->bindParam(':id', $lv2);
        } else {
            $stmt->bindParam(':id', $lv1);
        }

        $stmt->execute();
        $account = $stmt->fetch(PDO::FETCH_ASSOC);

        return [
            'lv5' => $account['lv5'] ?? null,
            'lv4' => $account['lv4'] ?? null,
            'lv3' => $account['lv3'] ?? null,
            'lv2' => $account['lv2'] ?? null,
            'lv1' => $account['lv1'] ?? null
        ];
    }

    private function sanitizeArray($array)
    {
        return array_map(function ($item) {
            return htmlspecialchars(strip_tags($item));
        }, $array);
    }
}

// استخدام الصنف
/* 
$transaction = new YomyaTransaction($con);
$transaction->insertTransaction($mydate, $all_cash, $ml7oza, $sessionUser, $_POST['cash_f'], $_POST['lv5f'], $_POST['lv4f'], $_POST['lv3f'], $_POST['cash_t'], $_POST['lv5t'], $_POST['lv4t'], $_POST['lv3t']);
 */