<?php
session_start();
require_once 'db.php';
if (!isset($_SESSION['co_id'])) {
    echo json_encode(["status" => "error", "message" => "Unauthorized"]);
    exit;
}

//$coid  = $_SESSION['co_id'];
$user_id   = $_SESSION['user_id'];

// Check if the form is submitted
if ($_SERVER["REQUEST_METHOD"] === "POST") {

    // Function to sanitize input
    function sanitize_input($data) {
        return htmlspecialchars(trim($data), ENT_QUOTES, 'UTF-8');
    }

    // Sanitize single-value form inputs
    $field1 = sanitize_input($_POST["field1"] ?? '');
    $field2 = sanitize_input($_POST["field2"] ?? '');
    $field3 = sanitize_input($_POST["field3"] ?? '');
    
    $field4 = sanitize_input($_POST["field4"] ?? '');
    $field5 = sanitize_input($_POST["field5"] ?? '');
    $user_location = sanitize_input($_POST["whm"] ?? '');
    $store_location = sanitize_input($_POST["store"] ?? '');
    $coid = sanitize_input($_POST["comp_id"] ?? '');
    $driver = sanitize_input($_POST["driver"] ?? '');
    $supplier = sanitize_input($_POST["supplier"] ?? '');
    $cash_acc = sanitize_input($_POST["cash_acc"] ?? '');
    $storex = sanitize_input($_POST["storex"] ?? '');
    $customerx = sanitize_input($_POST["customerx"] ?? '');
    $loadout = sanitize_input($_POST["loadout"] ?? '');

    // Sanitize array inputs (column values)
    $column1 = array_map('sanitize_input', $_POST["column1"] ?? []);
    $column2 = array_map('sanitize_input', $_POST["column2"] ?? []);
    $column7 = array_map('sanitize_input', $_POST["column7"] ?? []);
    $column8 = array_map('sanitize_input', $_POST["column8"] ?? []);
    $column9 = array_map('sanitize_input', $_POST["column9"] ?? []);
    
    $col33 = array_map('sanitize_input', $_POST["column33"] ?? []);
    $col44 = array_map('sanitize_input', $_POST["column44"] ?? []);
    $col55 = array_map('sanitize_input', $_POST["column55"] ?? []);
    $col66 = array_map('sanitize_input', $_POST["column66"] ?? []);
    $col77 = array_map('sanitize_input', $_POST["column77"] ?? []);
    $col100 = array_map('sanitize_input', $_POST["column100"] ?? []);
    $col1110 = array_map('sanitize_input', $_POST["column1110"] ?? []);
    
    // Sanitize array inputs ACCOUNTS
    $accs = array_map('sanitize_input', $_POST["accs"] ?? []);
    $amt = array_map('sanitize_input', $_POST["amt"] ?? []);
    
    $exp = array_map('sanitize_input', $_POST["exp"] ?? []);
    $desc = array_map('sanitize_input', $_POST["desc"] ?? []);
    $amt2 = array_map('sanitize_input', $_POST["amt2"] ?? []);
    
    $cust = array_map('sanitize_input', $_POST["cust"] ?? []);
    $amt3 = array_map('sanitize_input', $_POST["amt3"] ?? []);
    
    

    // Constants
    $copy1 = "O";
    $copy2 = "D";
    $sub_date = date("Y-m-d H:i:s");
    $status = "Pending";
    
    //require_once "db.php"; // Ensure this initializes $pdo

    // Ensure column1 and column2 have the same length
    if (!is_array($column1) || !is_array($column2) || count($column1) !== count($column2)) {
        die("Invalid input data. Please check your form submission.");
    }
    
    if ($field4==='DEL') {

    try {
        

        // Begin transaction
        $pdo->beginTransaction();

        // Prepare the INSERT queries
        $stmt = $pdo->prepare("INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date,  t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt2 = $pdo->prepare("INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            
        $stockStmt = $pdo->prepare("
        INSERT INTO stock_balance (co_id, item_id, warehouse_id, qty, last_update)
        VALUES (?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE qty = qty - VALUES(qty), last_update = NOW() ");

        // Loop through items and check before inserting
       // Insert records into the database
       
               $rowCount = count($column1);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                    $field1, $coid, $user_location, $field2, $copy1, $field4, $field3, 
                    $field3, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $user_location, $field2, $copy2, $field4, $field3, 
                    $user_location, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
                
                $stockStmt->execute([
                                        $coid,
                                        $column1[$i],     // item id
                                        $user_location,   // warehouse
                                        $column2[$i]      // quantity
                                    ]);
        }


       
        

        // Execute another commission-based transaction
       // Update quantities
        $stmt3 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_credit=t.t_qty*p.p_sale_px, t.t_comm=t.t_qty*p.sale_comm, t.t_margin=t.t_qty*p.margin,
            t.t_sale_vat=t.t_qty*p.sale_vat, t.t_prch_px=t.t_qty*p.p_prch_px, t.t_prch_vat=t.t_qty*p.inp_vat, t.t_sku=t.t_qty*p.sku,
            t.admin_fee=t.t_qty*p.admin_fee
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='O' AND t.co_id=?
        ");
        $stmt3->execute([$field2, $field4, $coid]);
        
        $stmt4 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_debit=t.t_qty*p.p_sale_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='D' AND t.co_id=?
        ");
        $stmt4->execute([$field2, $field4, $coid]);

        // Commit transaction
        $pdo->commit();

        //echo "Data inserted successfully.";
        echo json_encode(["status" => "success"]);

    } catch (PDOException $e) {
        $pdo->rollBack(); // Rollback in case of failure
        error_log("Database error: " . $e->getMessage()); // Log error
        echo "An error occurred while processing your request.";
    }
    }
    
if ($field4 === 'GR') {

    try {

        $pdo->beginTransaction();

        $stmt = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date,  t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt2 = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
        $stockStmt = $pdo->prepare("
        INSERT INTO stock_balance (co_id, item_id, warehouse_id, qty, last_update)
        VALUES (?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty), last_update = NOW() ");


        $rowCount = count($column1);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                    $field1, $coid, $user_location, $field2, $copy1, $field4, $field3, 
                    $field3, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $user_location, $field2, $copy2, $field4, $field3, 
                    $user_location, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
                
                $stockStmt->execute([
                                        $coid,
                                        $column1[$i],     // item id
                                        $user_location,   // warehouse
                                        $column2[$i]      // quantity
                                    ]);
        }
        
               // Update quantities
        $stmt3 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_debit=t.t_qty*p.p_sale_px, t.t_comm=t.t_qty*p.sale_comm, t.t_margin=t.t_qty*p.margin,
            t.t_sale_vat=t.t_qty*p.sale_vat, t.t_prch_px=t.t_qty*p.p_prch_px, t.t_prch_vat=t.t_qty*p.inp_vat, t.t_sku=t.t_qty*p.sku,
            t.admin_fee=t.t_qty*p.admin_fee
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='O' AND t.co_id=?
        ");
        $stmt3->execute([$field2, $field4, $coid]);
        
        $stmt4 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_credit=t.t_qty*p.p_sale_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='D' AND t.co_id=?
        ");
        $stmt4->execute([$field2, $field4, $coid]);

        $pdo->commit();

        echo "Data inserted successfully.";

    } catch (PDOException $e) {

        $pdo->rollBack();
        error_log("Database error: " . $e->getMessage());
        echo "An error occurred while processing your request.";
    }
}

        if ($field4 === 'LO') {

    try {

        $pdo->beginTransaction();

        $stmt = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date,  t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt2 = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
        $stockStmt = $pdo->prepare("
        INSERT INTO stock_balance (co_id, item_id, warehouse_id, qty, last_update)
        VALUES (?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty), last_update = NOW() ");


        $rowCount = count($column1);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                $field1, $coid, $supplier, $field2, $copy1, $field4, $user_location, 
                    $driver, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $supplier, $field2, $copy2, $field4, $user_location,
                    $supplier, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
                
                $stockStmt->execute([
                                        $coid,
                                        $column1[$i],     // item id
                                        $user_location,   // warehouse
                                        $column2[$i]      // quantity
                                    ]);
        }
        
               // Update quantities
         $stmt3 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_credit=t.t_qty*p.p_prch_px, t.t_prch_px=t.t_qty*p.p_prch_px, t.t_prch_vat=t.t_qty*p.inp_vat, t.t_sku=t.t_qty*p.sku,
            t.prch_comm=t.t_qty*p.prch_comm
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='O' AND t.co_id=?
        ");
        $stmt3->execute([$field2, $field4, $coid]);
        
        $stmt4 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_debit=t.t_qty*p.p_prch_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='D' AND t.co_id=?
        ");
        $stmt4->execute([$field2, $field4, $coid]);

        $pdo->commit();

        echo "Data inserted successfully.";

    } catch (PDOException $e) {

        $pdo->rollBack();
        error_log("Database error: " . $e->getMessage());
        echo "An error occurred while processing your request.";
    }
}


        if ($field4 === 'PRCH') {

    try {

        $pdo->beginTransaction();

        $stmt = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date,  t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt2 = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
        $stockStmt = $pdo->prepare("
        INSERT INTO stock_balance (co_id, item_id, warehouse_id, qty, last_update)
        VALUES (?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty), last_update = NOW() ");


        $rowCount = count($column1);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                $field1, $coid, $supplier, $field2, $copy1, $field4, $user_location, 
                    $driver, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $supplier, $field2, $copy2, $field4, $user_location,
                    $supplier, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
                
                $stockStmt->execute([
                                        $coid,
                                        $column1[$i],     // item id
                                        $user_location,   // warehouse
                                        $column2[$i]      // quantity
                                    ]);
        }
        
               // Update quantities
         $stmt3 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_credit=t.t_qty*p.p_prch_px, t.t_prch_px=t.t_qty*p.p_prch_px, t.t_prch_vat=t.t_qty*p.inp_vat, t.t_sku=t.t_qty*p.sku,
            t.prch_comm=t.t_qty*p.prch_comm
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='O' AND t.co_id=?
        ");
        $stmt3->execute([$field2, $field4, $coid]);
        
        $stmt4 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_debit=t.t_qty*p.p_prch_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='D' AND t.co_id=?
        ");
        $stmt4->execute([$field2, $field4, $coid]);

        $pdo->commit();

        echo "Data inserted successfully.";

    } catch (PDOException $e) {

        $pdo->rollBack();
        error_log("Database error: " . $e->getMessage());
        echo "An error occurred while processing your request.";
    }
}

        if ($field4 === 'RET PRCH') {

    try {

        $pdo->beginTransaction();

        $stmt = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date,  t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt2 = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
        $stockStmt = $pdo->prepare("
        INSERT INTO stock_balance (co_id, item_id, warehouse_id, qty, last_update)
        VALUES (?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE qty = qty - VALUES(qty), last_update = NOW() ");


        $rowCount = count($column1);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                $field1, $coid, $user_location, $field2, $copy1, $field4, $supplier, 
                    $driver, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $user_location, $field2, $copy2, $field4, $supplier,
                    $user_location, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
                
                $stockStmt->execute([
                                        $coid,
                                        $column1[$i],     // item id
                                        $user_location,   // warehouse
                                        $column2[$i]      // quantity
                                    ]);
        }
        
               // Update quantities
         $stmt3 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_credit=t.t_qty*p.p_sale_px, t.t_prch_px=t.t_qty*p.p_prch_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='O' AND t.co_id=?
        ");
        $stmt3->execute([$field2, $field4, $coid]);
        
        $stmt4 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_debit=t.t_qty*p.p_prch_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='D' AND t.co_id=?
        ");
        $stmt4->execute([$field2, $field4, $coid]);

        $pdo->commit();

        echo "Data inserted successfully.";

    } catch (PDOException $e) {

        $pdo->rollBack();
        error_log("Database error: " . $e->getMessage());
        echo "An error occurred while processing your request.";
    }
}
    
    
        if ($field4==='STFR') {

    try {
        // Begin transaction
        $pdo->beginTransaction();

        // Prepare the INSERT queries
         $stmt = $pdo->prepare("INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date,  t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt2 = $pdo->prepare("INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            
        $stockStmt = $pdo->prepare("
        INSERT INTO stock_balance (co_id, item_id, warehouse_id, qty, last_update)
        VALUES (?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE qty = qty - VALUES(qty), last_update = NOW() ");

        // Loop through items and check before inserting
       // Insert records into the database
       
               $rowCount = count($column1);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                    $field1, $coid, $user_location, $field2, $copy1, $field4, $storex, 
                    $driver, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $user_location, $field2, $copy2, $field4, $storex, 
                    $user_location, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
                
                $stockStmt->execute([
                                        $coid,
                                        $column1[$i],     // item id
                                        $user_location,   // warehouse
                                        $column2[$i]      // quantity
                                    ]);
        }


       
        

        // Execute another commission-based transaction
       $stmt3 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_credit=t.t_qty*p.p_prch_px, t.t_prch_px=t.t_qty*p.p_prch_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='O' AND t.co_id=?
        ");
        $stmt3->execute([$field2, $field4, $coid]);
        
        $stmt4 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_debit=t.t_qty*p.p_prch_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='D' AND t.co_id=?
        ");
        $stmt4->execute([$field2, $field4, $coid]);

        // Commit transaction
        $pdo->commit();

        echo "Data inserted successfully.";

    } catch (PDOException $e) {
        $pdo->rollBack(); // Rollback in case of failure
        error_log("Database error: " . $e->getMessage()); // Log error
        echo "An error occurred while processing your request.";
    }
    }
    
    if ($field4 === 'STK INV') {

    try {
        

        // Begin transaction
        $pdo->beginTransaction();

        // Prepare the INSERT queries
        $stmt = $pdo->prepare("INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date,  t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt2 = $pdo->prepare("INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            
           $rowCount = count($column1);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                    $field1, $coid, $field3, $field2, $copy1, $field4, $customerx, 
                    $customerx, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $field3, $field2, $copy2, $field4, $customerx, 
                    $field3, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
                
                
        }
        // Execute another commission-based transaction
       // Update quantities
        $stmt3 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_credit=t.t_qty*p.p_sale_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='O' AND t.co_id=?
        ");
        $stmt3->execute([$field2, $field4, $coid]);
        
        $stmt4 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_debit=t.t_qty*p.p_sale_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='D' AND t.co_id=?
        ");
        $stmt4->execute([$field2, $field4, $coid]);

        // Commit transaction
        $pdo->commit();

        //echo "Data inserted successfully.";
        echo json_encode(["status" => "success"]);

    } catch (PDOException $e) {
        $pdo->rollBack(); // Rollback in case of failure
        error_log("Database error: " . $e->getMessage()); // Log error
        echo "An error occurred while processing your request.";
    }
    }
    
    if ($field4 === 'INV RET') {

    try {
        

        // Begin transaction
        $pdo->beginTransaction();

        // Prepare the INSERT queries
        $stmt = $pdo->prepare("INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date,  t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt2 = $pdo->prepare("INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            
           $rowCount = count($column1);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                    $field1, $coid, $user_location, $field2, $copy1, $field4, $field3, 
                    $user_location, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $user_location, $field2, $copy2, $field4, $field3, 
                    $customerx, $column1[$i], $column2[$i],  
                    $status, $sub_date, $field5
                ]);
                
                
        }
        // Execute another commission-based transaction
       // Update quantities
        $stmt3 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_credit=t.t_qty*p.p_sale_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='O' AND t.co_id=?
        ");
        $stmt3->execute([$field2, $field4, $coid]);
        
        $stmt4 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_debit=t.t_qty*p.p_sale_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='D' AND t.co_id=?
        ");
        $stmt4->execute([$field2, $field4, $coid]);

        // Commit transaction
        $pdo->commit();

        //echo "Data inserted successfully.";
        echo json_encode(["status" => "success"]);

    } catch (PDOException $e) {
        $pdo->rollBack(); // Rollback in case of failure
        error_log("Database error: " . $e->getMessage()); // Log error
        echo "An error occurred while processing your request.";
    }
    }
    
    
    
            if ($field4 === 'REC') {

    try {

        $pdo->beginTransaction();

        $stmt = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_credit, t_status, t_sub_date,  t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt2 = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_debit, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            
        $rowCount = count($accs);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                $field1, $coid, $accs[$i], $field2, $copy1, $field4, $field3, 
                    $accs[$i], $amt[$i],   
                    $status, $sub_date, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $accs[$i], $field2, $copy2, $field4, $field3,
                    $field3, $amt[$i],   
                    $status, $sub_date, $field5
                ]);
                
     
        }


        $pdo->commit();

        echo "Data inserted successfully.";

    } catch (PDOException $e) {

        $pdo->rollBack();
        error_log("Database error: " . $e->getMessage());
        echo "An error occurred while processing your request.";
    }
}


         if ($field4 === 'INV PMT') {
        
            try {
        
                $pdo->beginTransaction();
        
                $stmt = $pdo->prepare("
                INSERT INTO transactions 
                    (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_credit, t_status, t_sub_date,  t_entry_by) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
                $stmt2 = $pdo->prepare("
                INSERT INTO transactions 
                    (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_debit, t_status, t_sub_date, t_entry_by) 
                    VALUES (?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    
                $rowCount = count($accs);
        
                for ($i = 0; $i < $rowCount; $i++) {
                        $stmt->execute([
                        $field1, $coid, $accs[$i], $field2, $copy1, $field4, $field3, 
                            $accs[$i], $amt[$i],   
                            $status, $sub_date, $field5
                        ]);
             
        
             
                        $stmt2->execute([
                            $field1,$coid, $accs[$i], $field2, $copy2, $field4, $field3,
                            $customerx, $amt[$i],   
                            $status, $sub_date, $field5
                        ]);
                        
             
                }
        
        
                $pdo->commit();
        
                echo "Data inserted successfully.";
        
            } catch (PDOException $e) {
        
                $pdo->rollBack();
                error_log("Database error: " . $e->getMessage());
                echo "An error occurred while processing your request.";
            }
        }
        

        if ($field4 === 'CTFR' || $field4 === 'BNK') {
        
            try {
        
                $pdo->beginTransaction();
        
                $stmt = $pdo->prepare("
                INSERT INTO transactions 
                    (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_credit, t_status, t_sub_date,  t_entry_by) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
                $stmt2 = $pdo->prepare("
                INSERT INTO transactions 
                    (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_debit, t_status, t_sub_date, t_entry_by) 
                    VALUES (?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    
                $rowCount = count($accs);
        
                for ($i = 0; $i < $rowCount; $i++) {
                        $stmt->execute([
                        $field1, $coid, $cash_acc, $field2, $copy1, $field4, $accs[$i], 
                            $accs[$i], $amt[$i],   
                            $status, $sub_date, $field5
                        ]);
             
        
             
                        $stmt2->execute([
                            $field1,$coid, $cash_acc, $field2, $copy2, $field4, $accs[$i],
                            $cash_acc, $amt[$i],   
                            $status, $sub_date, $field5
                        ]);
                        
             
                }
        
        
                $pdo->commit();
        
                echo "Data inserted successfully.";
        
            } catch (PDOException $e) {
        
                $pdo->rollBack();
                error_log("Database error: " . $e->getMessage());
                echo "An error occurred while processing your request.";
            }
        }
        
         if ($field4 === 'EXP') {
        
            try {
        
                $pdo->beginTransaction();
        
                $stmt = $pdo->prepare("
                INSERT INTO transactions 
                    (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_credit, t_status, t_sub_date, t_narration, t_entry_by) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
                $stmt2 = $pdo->prepare("
                INSERT INTO transactions 
                    (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_debit, t_status, t_sub_date, t_narration, t_entry_by) 
                    VALUES (?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    
                $rowCount = count($exp);
        
                for ($i = 0; $i < $rowCount; $i++) {
                        $stmt->execute([
                        $field1, $coid, $cash_acc, $field2, $copy1, $field4, $exp[$i], 
                            $exp[$i], $amt2[$i],   
                            $status, $sub_date, $desc[$i], $field5
                        ]);
             
        
             
                        $stmt2->execute([
                            $field1,$coid, $cash_acc, $field2, $copy2, $field4, $exp[$i],
                            $cash_acc, $amt2[$i],   
                            $status, $sub_date, $desc[$i], $field5
                        ]);
                        
             
                }
                
                 // Update status
                    $stmt3 = $pdo->prepare("
                    UPDATE transactions t
                    SET t.t_status = 'Confirmed'
                    WHERE 
                      t.t_doc_no = ?
                      AND t.t_type = 'exp'
                      AND t.co_id = ?
                      AND t.t_copy='O'
                      AND EXISTS (
                        SELECT 1
                        FROM permissions p
                        LEFT JOIN accounts u ON t.t_cr = u.id
                        WHERE 
                          p.co_id = t.co_id
                          AND p.user_id = ?
                          AND p.pm_acc_lvl IN (1,2,6,7)
                          AND (
                            (t.t_cr = p.pm_acc_id AND p.pm_acc_id <> '')
                            OR
                            (u.acc_categ = p.pm_acc_categ AND p.pm_acc_id = '')
                          )
                      );
                    ");
                    $stmt3->execute([$field2, $coid, $user_id]);
                    
                    $stmt4 = $pdo->prepare("
                    UPDATE transactions t
                    SET t.t_status = 'Confirmed'
                    WHERE 
                      t.t_doc_no = ?
                      AND t.t_type = 'exp'
                      AND t.co_id = ?
                      AND t.t_copy='D'
                      AND EXISTS (
                        SELECT 1
                        FROM permissions p
                        LEFT JOIN accounts u ON t.t_ct_det = u.id
                        WHERE 
                          p.co_id = t.co_id
                          AND p.user_id = ?
                          AND p.pm_acc_lvl IN (1,2,6,7)
                          AND (
                            (t.t_cr = p.pm_acc_id AND p.pm_acc_id <> '')
                            OR
                            (u.acc_categ = p.pm_acc_categ AND p.pm_acc_id = '')
                          )
                      );
                    ");
                    $stmt4->execute([$field2, $coid, $user_id]);
                    
                 
                $pdo->commit();
        
                echo "Data inserted successfully.";
        
            } catch (PDOException $e) {
        
                $pdo->rollBack();
                error_log("Database error: " . $e->getMessage());
                echo "An error occurred while processing your request.";
            }
        }
        
        if ($field4 === 'CS INV') {

    try {

        $pdo->beginTransaction();

        $stmt = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_credit, t_status, t_sub_date,  t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt2 = $pdo->prepare("
        INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_debit, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            
        $rowCount = count($cust);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                $field1, $coid, $field3, $field2, $copy1, $field4, $cust[$i], 
                    $cust[$i], $amt3[$i],   
                    $status, $sub_date, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $field3, $field2, $copy2, $field4, $cust[$i],
                    $field3, $amt3[$i],   
                    $status, $sub_date, $field5
                ]);
                
     
        }


        $pdo->commit();

        echo "Data inserted successfully.";

    } catch (PDOException $e) {

        $pdo->rollBack();
        error_log("Database error: " . $e->getMessage());
        echo "An error occurred while processing your request.";
    }
}


}
?>
