<?php

// Make a GET request to the API
$url = 'http://117.247.179.120:9099/external/api/allDbstock';
$response = file_get_contents($url);

// Check if the request was successful
if ($response !== false) {
    $apiData = json_decode($response, true);

    // Connect to the MySQL database
    $host = 'localhost';
    $username = 'vishnum1_mmvkcuser';
    $password = 'J4Jasmine@123';
    $database = 'vishnum1_materials_requirement_managementfull';

    $conn = new mysqli($host, $username, $password, $database);

    // Check the database connection
    if ($conn->connect_error) {
        $error = "Connection failed: " . $conn->connect_error;
        // Write the error to the file
        file_put_contents('hi.txt', $error);
        die($error);
    }

 $truncateQuery = "TRUNCATE TABLE sapdatafetch";
            $conn->query($truncateQuery);

            $insertQuery = "INSERT INTO sapdatafetch (sap_code, sub, main_category, sub_category,description, closing_stock) VALUES (?, ?, ?, ?, ?, ?)";
            $stmt = $conn->prepare($insertQuery);
    

        if ($stmt !== false) {
            foreach ($apiData['data'] as $data) {
                $sapcode = $data['SAP_CODE'];
                $sbu = $data['SBU'];
                $maincategory = $data['MAIN_CATEGORY'];
                $sbucategory = $data['SUB_CATEGORY'];
                $description = $data['MATERIAL_DESCRIPTION'];
                $closingstock = $data['CLOSING_STOCK'];

                if (!$stmt->bind_param("sssssd", $sapcode, $sbu, $maincategory, $sbucategory,$description, $closingstock)) {
                    $error = "Parameter binding failed: " . $stmt->error;
                    // Write the error to the file
                    file_put_contents('hi.txt', $error);
                    die($error);
                }

                if (!$stmt->execute()) {
                    $error = "Statement execution failed: " . $stmt->error;
                    // Write the error to the file
                    file_put_contents('hi.txt', $error);
                    die($error);
                }
            }

            $stmt->close();

          
            echo "Data inserted successfully.";
        } else {
            echo "No valid table found for insert.";
        }
    
    

    // Close the database connection
    $conn->close();
} else {
    $error = error_get_last()['message'];
    // Write the error to the file
    file_put_contents('hi.txt', $error);
    echo "Failed to retrieve data from the API.";
}
?>