<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
require "racs.php";
require "vtshowsBBB.php";

if (session_status() == PHP_SESSION_NONE) {
    session_start();
}

if (!isset($_SESSION['titles']) || !isset($_SESSION['positions']) || !isset($_SESSION['usernames'])) {
    header("Location: index.php");
    exit();
}

if (isset($_REQUEST['file_submit'])) {
    $csvf = $_FILES['files']['tmp_name'];
    update_invoices(0, $csvf);
}

function update_invoices($acl, $fl)
{
    set_time_limit(0);
    $conn = mysqli_connect("localhost", "primarysales", "S5L7lF01EQZvQrGK", "primarysales_diana");

    if (!$conn) {
        die("Database connection failed: " . mysqli_connect_error());
    }

    // Helper: safely get CSV cell value with default
    function get_csv_value($data, $index, $default = '')
    {
        return isset($data[$index]) ? trim($data[$index]) : $default;
    }

    $unos = 0;

    if (strlen($fl) > 2) {
        printf("%s<br>", $fl);

        if (($handle = fopen($fl, "r")) !== FALSE) {
            $rows = 0;

            while (($data = fgetcsv($handle, 5000, ",")) !== FALSE) {
                $rows++;

                if (strlen(get_csv_value($data, 2)) > 0 && strlen(get_csv_value($data, 6)) > 0 && $rows >= 0 && get_csv_value($data, 2) !== 'Totals') {
                    
                    // --- Safe Date Conversion ---
                    $arr = explode("/", get_csv_value($data, 3));
                    $dates = count($arr) === 3 ? $arr[2] . "-" . $arr[1] . "-" . $arr[0] : date('Y-m-d');

                    // --- Sanitize Strings ---
                    $agent = mysqli_real_escape_string($conn, get_csv_value($data, 6));
                    $invoice = mysqli_real_escape_string($conn, get_csv_value($data, 2));
                    $companycode = mysqli_real_escape_string($conn, get_csv_value($data, 26));
                    $invoiceNumber = $companycode . '-' . $invoice;
                    $voucherr = mysqli_real_escape_string($conn, get_csv_value($data, 16));
                    $username = mysqli_real_escape_string($conn, 'test user');

                    // --- Safe Numeric Conversions ---
                    $values = (float)((get_csv_value($data, 12, 0)) + (get_csv_value($data, 13, 0)) - (get_csv_value($data, 10, 0)));
                    $netvalues = (float)get_csv_value($data, 13, 0);
                    $disrates = (float)((get_csv_value($data, 11, 0) / 100) * -1);
                    $disvall = (float)get_csv_value($data, 12, 0) * -1;
                    $vatrate = ($values != 0 && strlen(get_csv_value($data, 10)) > 0) ? (float)get_csv_value($data, 10) / $values : 0;

                    $itemcode = mysqli_real_escape_string($conn, get_csv_value($data, 18));
                    $inlistp = (float)get_csv_value($data, 27, 0);
                    $um = (int)get_csv_value($data, 28, 1);
                    $pqty = (int)get_csv_value($data, 21, 0);
                    $listprice = (float)get_csv_value($data, 29, 0);
                    $mrprice = (is_numeric(get_csv_value($data, 30, 0)) && get_csv_value($data, 30) !== '') ? (float)get_csv_value($data, 30) : 0;

                    if ($um == 0) $um = 1; // Prevent division by zero

                    $bxprice = $inlistp * $um;
                    $bqty = $pqty / $um;
                    $calculated_value = $listprice * $um;
                    $uncode = $itemcode . $calculated_value;

                    // --- Agent Lookup ---
                    $sqlst = "SELECT zone, region, rsm_name, executive_name, rep_name, agent_name, agent_type, territory_name, zone_order, sub_region, chanelname, range_type, arm_name, sm_id, rm_id, asm_name 
                              FROM agentsmrd WHERE sap_code='$agent'";
                    $aqry = mysqli_query($conn, $sqlst);
                    if (mysqli_num_rows($aqry) == 0) {
                        printf("<span style='color:orange'>Agent not found: %s</span><br>", $agent);
                        continue;
                    }
                    $rsta = mysqli_fetch_row($aqry);

                    // --- Item Lookup ---
                    $rsqlst = "SELECT DISTINCT item_name, item_ranges, sub_ranges, (packets_per_box * pcs_per_packet) AS total_pcs, packet_weight, location, itemcode,item_name_mrt 
                               FROM itemsmrd WHERE sap_itcode = '$itemcode'";
                    $rqry = mysqli_query($conn, $rsqlst);
                    if (mysqli_num_rows($rqry) == 0) {
                        printf("<span style='color:orange'>Item not found: %s</span><br>", get_csv_value($data, 18));
                        continue;
                    }

                    $trst = mysqli_fetch_row($rqry);
                    if (!$trst) continue;

                    list($itmnemee, $itemranges, $subranges, $packetsperbox, $packetweights, $storeloc, $itemcoded,$itemgroups) = $trst;
                    $marginrates = 0;

                    // --- Safe Defaults for Missing Columns ---
                    $stores_names = '';
                    $date_charts = $dates;

                    // --- Build Insert Query ---
                    $vsqlst = "INSERT INTO rc_invoices (
                        dates, sale_types, invoice_types, invoice_number, voucher_number, naration, 
                        agent_names, agent_types, zone_orders, zones, regions, chanell, sub_regions, 
                        rsm_names, executive_names, territory_names, range_types, arm_names, sm_id, 
                        rm_id, asm_names, rep_names, item_names, item_ranges, sub_ranges, packets_per_box, 
                        packet_weights, box_price, box_qty, discount_values, grand_values, net_values, 
                        user_names, discount_rates, margin_rates, paymode, itemcodes, itemgroups, vat_rates, 
                        box_mrp, payments, user_dates, stores_names, date_charts
                    ) VALUES (
                        '$dates','Primary invoice','Primary Invoice','$invoiceNumber','$voucherr','',
                        '{$rsta[5]}','{$rsta[6]}','{$rsta[8]}','{$rsta[0]}','{$rsta[1]}','{$rsta[10]}','{$rsta[9]}',
                        '{$rsta[2]}','{$rsta[3]}','{$rsta[7]}','{$rsta[11]}','{$rsta[12]}','{$rsta[13]}','{$rsta[14]}',
                        '{$rsta[15]}','{$rsta[4]}','$itmnemee','$itemranges','$subranges',$packetsperbox,$packetweights,
                        $bxprice,$bqty,$disvall,$values,$netvalues,'$username',$disrates,$marginrates,'',
                        '$itemcoded','$itemgroups',$vatrate,$mrprice,'NOT DELIVERD',now(),'$stores_names','$date_charts'
                    )";

                    $insResult = mysqli_query($conn, $vsqlst);
                    if (!$insResult) {
                        echo "<span style='color:red'>Insert Error: " . mysqli_error($conn) . "</span><br>";
                        continue;
                    }

                    $unos++;
                }
            }
            fclose($handle);
        }

        echo "<div class='cmp'>✅ Upload complete<br>Total invoices uploaded: <b>$unos</b></div>";
    }
}


?>
<html>
<head>
    <title>Invoice Upload</title>
    <link rel="stylesheet" type="text/css" href="vtxstyles.css">
    <link rel="stylesheet" type="text/css" href="rdsty.css">
</head>
<body style="font-family:calibri;font-size:small">
<script src="datesps.js"></script>
<div class='sds'><?php newdash(1); ?></div>
<div class='mnf'>
    <b>Import Order Invoices form from Tally system<br></b>
    The file type should be CSV. Export in detailed view with codes<br><br>
    <form enctype='multipart/form-data' method='POST' action='prupload.php?actp=0'>
        <label>Select the file:</label><br>
        <input type='file' id='files' name='files'><br><br>
        <input type='submit' name='file_submit' value='Go'><br>
    </form>
</div>
</body>
</html>
