ExcelToMapController.php

This handles the storing and retrieving JSON map data, converting CSV and KML data to JSON format, converting JSON data to CSV, interacting with Google Sheets, and generating KML data from JSON data.

storeMap

public function storeMap(Request $request)
{
    // Retrieve JSON data from the request
    $jsonData = $request->input('jsonData');
    // Decode JSON data into an associative array
    $jsonData = json_decode($jsonData, true);
    // Define the directory where the map data will be stored
    $directory = 'Map';
    // Create a file path based on the USER_ID from the JSON data
    $fileMap = $directory . '/' . $jsonData['USER_ID'] . '.json';
    // Convert the associative array back to a pretty-printed JSON string
    $prettyPrintedJson = json_encode($jsonData, JSON_PRETTY_PRINT);
    // Open the file in write mode
    $file = fopen($fileMap, 'w');
    // Check if the file was successfully opened
    if ($file) {
        // Write the pretty-printed JSON data to the file
        fwrite($file, $prettyPrintedJson);
        // Close the file
        fclose($file);
    }
    // Prepare a success response
    $response = [
        'SUCCESS' => 1,
    ];
    // Return the response
    return $response;
}

To explain what this storeMap method does:

  1. Retrieve JSON Data:

    • Get the JSON data from the incoming HTTP request.

  2. Decode JSON:

    • Decode the received JSON data into an associative array.

  3. Define Directory and File Path:

    • Specify the directory ('Map') where map data will be stored.

    • Create a file path based on the 'USER_ID' from the JSON data.

  4. Convert to Pretty-Printed JSON:

    • Convert the associative array back to a pretty-printed JSON string.

  5. Open File for Writing:

    • Open a file in write mode using the determined file path.

  6. Write to File:

    • If the file is successfully opened, write the pretty-printed JSON data to the file.

  7. Close File:

    • Close the file after writing.

  8. Prepare Success Response:

    • Create a success response array indicating the operation was successful.

  9. Return Response:

    • Return the success response array as the HTTP response.

getMap

public function getMap(Request $request)
{
    // Retrieve the user ID from the request
    $userId = $request->input('userId');
    // Define the directory where the map data is stored
    $directory = 'Map';
    // Create a file path based on the user ID
    $fileMap = $directory . '/' . $userId . '.json';
    // Read the content of the JSON file
    $jsonContent = file_get_contents($fileMap);
    // Decode the JSON content into an associative array
    $jsonData = json_decode($jsonContent, true);
    // Prepare a response with success status and map data
    $response = [
        'SUCCESS' => 1,
        'DATA' => $jsonData['DATA'] // Assuming there is a 'DATA' key in the stored JSON
    ];
    // Return the response
    return $response;
}

To explain what this getMap method does:

  1. Retrieve User ID:

    • Extract the user ID from the incoming HTTP request.

  2. Define Directory and File Path:

    • Specify the directory ('Map') where map data is stored.

    • Create a file path based on the retrieved user ID.

  3. Read JSON Content:

    • Read the content of the JSON file using the determined file path.

  4. Decode JSON:

    • Decode the JSON content into an associative array.

  5. Prepare Response:

    • Create a response array with a success status ('SUCCESS' => 1).

    • Include the map data under the 'DATA' key from the decoded JSON.

  6. Return Response:

    • Return the response array as the HTTP response.

convertCsvToJson

public function convertCsvToJson(Request $request)
{
    // Read the content of the CSV file from the request
    $csvContent = file_get_contents($request->file('file'));
    // Split the CSV content into an array of rows
    $csvRows = str_getcsv($csvContent, "\n");
    // Extract headers from the first row and remove it from the rows array
    $headers = str_getcsv(array_shift($csvRows));
    // Initialize an empty array to store the JSON data
    $jsonData = array();
    // Iterate through each CSV row and convert it to associative array
    foreach ($csvRows as $csvRow) {
        $rowValues = str_getcsv($csvRow);
        $data = array();
        // Iterate through each value in the row and associate it with the corresponding header
        foreach ($rowValues as $key => $value) {
            if (isset($headers[$key])) {
                $data[$headers[$key]] = $value;
            }
        }
        // Create a 'WKT' (Well-Known Text) field based on 'LONGITUDE' and 'LATITUDE'
        $longitude = $data['LONGITUDE'];
        $latitude = $data['LATITUDE'];
        $data['WKT'] = "POINT (" . $longitude . " " . $latitude . ")";
        // Add the processed row data to the JSON data array
        $jsonData[] = $data;
    }
    // Retrieve the title from the request
    $title = $request->title;
    // Prepare a response with success status, title, and converted JSON data
    $response = [
        'SUCCESS' => 1,
        'TITLE' => $title,
        'DATA' => $jsonData,
    ];
    // Return the response
    return $response;
}

To explain what this convertCsvToJson method does:

  1. Read CSV Content:

    • Read the content of the CSV file from the incoming HTTP request.

  2. Split CSV into Rows:

    • Split the CSV content into an array of rows.

  3. Extract Headers:

    • Extract headers from the first row and remove it from the rows array.

  4. Initialize JSON Data Array:

    • Create an empty array to store the converted JSON data.

  5. Iterate Through CSV Rows:

    • For each CSV row, convert it into an associative array using headers.

  6. Create 'WKT' Field:

    • Create a 'WKT' (Well-Known Text) field based on 'LONGITUDE' and 'LATITUDE'.

  7. Retrieve Title:

    • Get the title from the incoming HTTP request.

  8. Prepare Response:

    • Create a response array with a success status ('SUCCESS' => 1).

    • Include the title and the converted JSON data under the 'DATA' key.

  9. Return Response:

    • Return the response array as the HTTP response.

convertGoogleSheetToJson

public function convertGoogleSheetToJson(Request $request)
{
    // Retrieve the Google Sheet link from the request
    $googleSheetLink = $request->input('googleSheetLink');
    // Extract the Google Sheet ID from the link using a regular expression
    preg_match('/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/', $googleSheetLink, $matches);
    // Check if the Google Sheet ID was successfully extracted
    if (!isset($matches[1])) {
        // If not, prepare an error response and return it
        $response = [
            'SUCCESS' => 0,
        ];
        return $response;
    } else {
        // Extract the Google Sheet ID from the matches
        $googleSheetId = $matches[1];
        // Create a URL to download the Google Sheet as a CSV file
        $googleSheetUrl = "https://docs.google.com/spreadsheets/d/" . $googleSheetId . "/export?format=csv";
        // Retrieve the CSV data from the Google Sheet URL
        $data = file_get_contents($googleSheetUrl);
        // Parse the CSV data into an array
        $csv = array_map('str_getcsv', explode("\n", $data));
        // Extract headers from the first row and remove it from the rows array
        $headers = array_shift($csv);
        // Initialize an empty array to store the JSON data
        $jsonData = array();
        // Iterate through each CSV row and convert it to an associative array
        foreach ($csv as $row) {
            $data = array();
            // Iterate through each value in the row and associate it with the corresponding header
            foreach ($row as $key => $value) {
                if (isset($headers[$key])) {
                    $data[$headers[$key]] = $value;
                }
            }
            // Create a 'WKT' (Well-Known Text) field based on 'LONGITUDE' and 'LATITUDE'
            $longitude = $data['LONGITUDE'];
            $latitude = $data['LATITUDE'];
            $data['WKT'] = "POINT (" . $longitude . " " . $latitude . ")";
            // Add the processed row data to the JSON data array
            $jsonData[] = $data;
        }
        // Retrieve the title from the request
        $title = $request->input('title');
        // Prepare a response with success status, title, and converted JSON data
        $response = [
            'SUCCESS' => 1,
            'TITLE' => $title,
            'DATA' => $jsonData,
        ];
        // Return the response
        return $response;
    }
}

To explain what this convertGoogleSheetToJson method does:

  1. Retrieve Google Sheet Link:

    • Extract the Google Sheet link from the incoming HTTP request.

  2. Extract Google Sheet ID:

    • Use a regular expression to extract the Google Sheet ID from the link.

  3. Check Google Sheet ID:

    • Check if the Google Sheet ID was successfully extracted.

  4. Create CSV Download URL:

    • If successful, create a URL to download the Google Sheet as a CSV file.

  5. Retrieve CSV Data:

    • Retrieve the CSV data from the generated Google Sheet download URL.

  6. Parse CSV Data:

    • Parse the CSV data into an array of rows.

  7. Extract Headers:

    • Extract headers from the first row and remove it from the rows array.

  8. Initialize JSON Data Array:

    • Create an empty array to store the converted JSON data.

  9. Iterate Through CSV Rows:

    • For each CSV row, convert it into an associative array using headers.

  10. Create 'WKT' Field:

    • Create a 'WKT' (Well-Known Text) field based on 'LONGITUDE' and 'LATITUDE'.

  11. Retrieve Title:

    • Get the title from the incoming HTTP request.

  12. Prepare Response:

    • Create a response array with a success status ('SUCCESS' => 1).

    • Include the title and the converted JSON data under the 'DATA' key.

  13. Return Response:

    • Return the response array as the HTTP response.

convertKmlToJson

public function convertKmlToJson(Request $request)
{
    // Read the content of the KML file from the request
    $kmlContent = file_get_contents($request->file('file'));
    // Create a SimpleXMLElement object from the KML content
    $XML = new SimpleXMLElement($kmlContent);
    // Initialize an empty array to store the JSON data
    $jsonData = [];
    // Initialize a counter for generating unique IDs
    $counter = 1;
    // Iterate through each Placemark in the KML Document
    foreach ($XML->Document->Placemark as $placemark) {
        // Extract coordinates from the Point element
        $coordinates = explode(',', (string) $placemark->Point->coordinates);
        // Create an associative array representing a place with an ID, name, latitude, and longitude
        $place = [
            'ID' => (string) $counter,
            'NAME' => (string) $placemark->name,
            'LATITUDE' => $coordinates[1],
            'LONGITUDE' => $coordinates[0],
        ];
        // Create a 'WKT' (Well-Known Text) field based on 'LONGITUDE' and 'LATITUDE'
        $place['WKT'] = "POINT (" . $place['LONGITUDE'] . " " . $place['LATITUDE'] . ")";
        // Add the processed place data to the JSON data array
        $jsonData[] = $place;
        // Increment the counter for the next ID
        $counter++;
    }
    // Retrieve the title from the request
    $title = $request->title;
    // Prepare a response with success status, title, and converted JSON data
    $response = [
        'SUCCESS' => 1,
        'TITLE' => $title,
        'DATA' => $jsonData,
    ];
    // Return the response
    return $response;
}

To explain what this convertKmlToJson method does:

  1. Read KML Content:

    • Read the content of the KML file from the incoming HTTP request.

  2. Create SimpleXMLElement Object:

    • Create a SimpleXMLElement object from the KML content.

  3. Initialize JSON Data Array:

    • Create an empty array to store the converted JSON data.

  4. Initialize ID Counter:

    • Initialize a counter for generating unique IDs.

  5. Iterate Through KML Placemarks:

    • For each Placemark in the KML Document, extract relevant data.

  6. Extract Coordinates:

    • Extract coordinates from the Point element in the Placemark.

  7. Create Associative Array for Place:

    • Create an associative array representing a place with ID, name, latitude, and longitude.

  8. Create 'WKT' Field:

    • Create a 'WKT' (Well-Known Text) field based on 'LONGITUDE' and 'LATITUDE'.

  9. Add Place Data to JSON Array:

    • Add the processed place data to the JSON data array.

  10. Retrieve Title:

    • Get the title from the incoming HTTP request.

  11. Prepare Response:

    • Create a response array with a success status ('SUCCESS' => 1).

    • Include the title and the converted JSON data under the 'DATA' key.

  12. Return Response:

    • Return the response array as the HTTP response.

convertJsonToCsv

public function convertJsonToCsv(Request $request)
{
    // Retrieve JSON data from the request
    $jsonData = $request->input('jsonData');
    // Decode JSON data into an associative array
    $jsonData = json_decode($jsonData, true);
    // Initialize an array to store unique column names
    $uniqueColumns = [];
    // Iterate through each item in the JSON data to identify unique column names
    foreach ($jsonData as $item) {
        foreach (array_keys($item) as $key) {
            if (!in_array($key, $uniqueColumns)) {
                $uniqueColumns[] = $key;
            }
        }
    }
    // Initialize an array to store formatted data
    $formattedData = [];
    // Iterate through each item in the JSON data to create a new structure with unique columns
    foreach ($jsonData as $item) {
        $newItem = [];
        foreach ($uniqueColumns as $column) {
            // Assign the value for the current column or an empty string if not present
            $newItem[$column] = $item[$column] ?? '';
        }
        $formattedData[] = $newItem;
    }
    // Initialize an empty string to store the resulting CSV content
    $csv = "";
    // If there is formatted data, create the header row in CSV
    if (!empty($formattedData)) {
        $csv .= implode(',', $uniqueColumns) . "\n";
    }
    // Iterate through each formatted row and append it to the CSV string
    foreach ($formattedData as $row) {
        $csv .= '"' . implode('","', $row) . "\"\n";
    }
    // Prepare a response with success status and the generated CSV data
    $response = [
        'SUCCESS' => 1,
        'DATA' => $csv,
    ];
    // Return the response
    return $response;
}

To explain what this convertJsonToCsv method does:

  1. Retrieve JSON Data:

    • Get the JSON data from the incoming HTTP request.

  2. Decode JSON:

    • Decode the received JSON data into an associative array.

  3. Initialize Unique Columns Array:

    • Create an array to store unique column names.

  4. Identify Unique Columns:

    • Iterate through each item in the JSON data to identify unique column names.

  5. Initialize Formatted Data Array:

    • Create an array to store formatted data.

  6. Create Formatted Data Structure:

    • Iterate through each item in the JSON data to create a new structure with unique columns.

  7. Initialize CSV String:

    • Create an empty string to store the resulting CSV content.

  8. Create Header Row in CSV:

    • If there is formatted data, create the header row in CSV.

  9. Iterate Through Formatted Rows:

    • Iterate through each formatted row and append it to the CSV string.

  10. Prepare Response:

    • Create a response array with a success status ('SUCCESS' => 1).

    • Include the generated CSV data under the 'DATA' key.

  11. Return Response:

    • Return the response array as the HTTP response.

convertJsonToKml

public function convertJsonToKml(Request $request)
{
    // Retrieve JSON data from the request
    $jsonData = $request->input('jsonData');
    // Decode JSON data into an associative array
    $jsonData = json_decode($jsonData, true);
    // Initialize a string with the KML header
    $kmlData = '<?xml version="1.0" encoding="UTF-8"?>' . PHP_EOL;
    $kmlData .= '<kml xmlns="http://www.opengis.net/kml/2.2">' . PHP_EOL;
    $kmlData .= '<Document>' . PHP_EOL;
    // Iterate through each item in the JSON data
    foreach ($jsonData as $item) {
        $latitude = "";
        $longitude = "";
        // Define a pattern to extract latitude and longitude from 'WKT' (Well-Known Text)
        $pattern = '/\((-?\d+\.\d+) (-?\d+\.\d+)\)/';
        // Check if the 'WKT' matches the pattern
        if (preg_match($pattern, $item['WKT'], $matches)) {
            // Assign latitude and longitude values
            $latitude = $matches[1];
            $longitude = $matches[2];
        }
        // Build the KML structure for each item
        $kmlData .= '<Placemark>' . PHP_EOL;
        $kmlData .= '<name>' . $item['NAME'] . '</name>' . PHP_EOL;
        $kmlData .= '<Point>' . PHP_EOL;
        $kmlData .= '<coordinates>' . $longitude . ',' . $latitude . '</coordinates>' . PHP_EOL;
        $kmlData .= '</Point>' . PHP_EOL;
        $kmlData .= '</Placemark>' . PHP_EOL;
    }
    // Close the KML Document and KML tags
    $kmlData .= '</Document>' . PHP_EOL;
    $kmlData .= '</kml>';
    // Prepare a response with success status and the generated KML data
    $response = [
        'SUCCESS' => 1,
        'DATA' => $kmlData,
    ];
    // Return the response
    return $response;
}

To explain what this convertJsonToKml method does:

  1. Retrieve JSON Data:

    • Get the JSON data from the incoming HTTP request.

  2. Decode JSON:

    • Decode the received JSON data into an associative array.

  3. Initialize KML Data String:

    • Create a string with the KML header.

  4. Iterate Through JSON Items:

    • Iterate through each item in the JSON data.

  5. Extract Latitude and Longitude:

    • Define a pattern to extract latitude and longitude from 'WKT' (Well-Known Text).

    • Check if the 'WKT' matches the pattern.

  6. Build KML Structure for Each Item:

    • Build the KML structure for each item, including name, point coordinates, etc.

  7. Close KML Tags:

    • Close the KML Document and KML tags.

  8. Prepare Response:

    • Create a response array with a success status ('SUCCESS' => 1).

    • Include the generated KML data under the 'DATA' key.

  9. Return Response:

    • Return the response array as the HTTP response.

authenticateGoogleAccountForGoogleSheet

public function authenticateGoogleAccountForGoogleSheet()
{
    // Create a new instance of the Google_Client class
    $client = new Google_Client();
    // Set the application name for identification
    $client->setApplicationName('Google Sheets');
    // Set the scopes for the requested permissions (in this case, access to spreadsheets)
    $client->setScopes(['https://www.googleapis.com/auth/spreadsheets']);
    // Set the access type to 'offline' for obtaining a refresh token
    $client->setAccessType('offline');
    // Check if the application is running on a live server
    $LIVE_SERVER = getenv('LIVE_SERVER');
    // Configure the client based on the server environment
    if ($LIVE_SERVER == 1) {
        // If on a live server, set the authentication configuration using the live server credentials
        $client->setAuthConfig(storage_path('google-sheets-credentials-LIVE.json'));
    } else if ($LIVE_SERVER == 0) {
        // If on a local server, set the authentication configuration using the local server credentials
        $client->setAuthConfig(storage_path('google-sheets-credentials-LOCAL.json'));
    }
    // Generate an authentication URL for the user to grant access
    $authUrl = $client->createAuthUrl();
    // Prepare a response with success status and the generated authentication URL
    $response = [
        'SUCCESS' => 1,
        'AUTH_URL' => filter_var($authUrl, FILTER_SANITIZE_URL),
    ];
    return $response;
}

To explain what this authenticateGoogleAccountForGoogleSheet method does:

  1. Create Google_Client Instance:

    • Instantiate a new instance of the Google_Client class.

  2. Set Application Name:

    • Set the application name for identification to 'Google Sheets'.

  3. Set Scopes:

    • Define the scopes for the requested permissions (in this case, access to spreadsheets).

  4. Set Access Type:

    • Set the access type to 'offline' for obtaining a refresh token.

  5. Check Server Environment:

    • Check if the application is running on a live server.

  6. Configure Client:

    • Configure the client based on the server environment.

      • If on a live server, set the authentication configuration using live server credentials.

      • If on a local server, set the authentication configuration using local server credentials.

  7. Generate Authentication URL:

    • Generate an authentication URL for the user to grant access.

  8. Prepare Response:

    • Create a response array with a success status ('SUCCESS' => 1).

    • Include the generated authentication URL under the 'AUTH_URL' key.

  9. Return Response:

    • Return the response array as the HTTP response.

convertJsonToGoogleSheet

public function convertJsonToGoogleSheet(Request $request)
{
    // Create a new instance of the Google_Client class
    $client = new Google_Client();
    // Set the application name for identification
    $client->setApplicationName('Google Sheets');
    // Set the scopes for the requested permissions (in this case, access to spreadsheets)
    $client->setScopes(['https://www.googleapis.com/auth/spreadsheets']);
    // Set the access type to 'offline' for obtaining a refresh token
    $client->setAccessType('offline');
    // Check if the application is running on a live server
    $LIVE_SERVER = getenv('LIVE_SERVER');
    // Configure the client based on the server environment
    if ($LIVE_SERVER == 1) {
        // If on a live server, set the authentication configuration using the live server credentials
        $client->setAuthConfig(storage_path('google-sheets-credentials-LIVE.json'));
    } else if ($LIVE_SERVER == 0) {
        // If on a local server, set the authentication configuration using the local server credentials
        $client->setAuthConfig(storage_path('google-sheets-credentials-LOCAL.json'));
    }
    // Obtain an access token using the authorization code from the request
    $accessToken = $client->fetchAccessTokenWithAuthCode($request->input('code'));
    // Set the obtained access token to the client
    $client->setAccessToken($accessToken);
    // Create a new instance of the Google Sheets service
    $service = new \Google\Service\Sheets($client);
    // Retrieve the title for the new spreadsheet from the request
    $title = $request->input('title');
    // Create a new spreadsheet with the specified title
    $spreadsheet = new \Google\Service\Sheets\Spreadsheet();
    $spreadsheet->setProperties(new \Google\Service\Sheets\SpreadsheetProperties(['title' => $title]));
    $spreadsheet = $service->spreadsheets->create($spreadsheet);
    // Retrieve the ID of the created spreadsheet
    $spreadsheetId = $spreadsheet->getSpreadsheetId();
    // Decode JSON data from the request
    $jsonData = json_decode($request->input('jsonData'), true);
    // Initialize an array to store unique column names
    $rows = [];
    foreach ($jsonData as $obj) {
        foreach ($obj as $key => $value) {
            // Collect unique column names
            if (!in_array($key, $rows)) {
                $rows[] = $key;
            }
        }
    }
    // Count the number of columns in the sheet
    $sheetColumn = count($rows);
    // Initialize a 2D array with the header row
    $rows = [$rows];
    // Populate the rows array with data from the JSON
    foreach ($jsonData as $json) {
        $rowData = [];
        foreach ($rows[0] as $key) {
            // Fill in the data for each column or an empty string if not present
            if (array_key_exists($key, $json)) {
                $rowData[] = $json[$key];
            } else {
                $rowData[] = '';
            }
        }
        $rows[] = $rowData;
    }
    // Define the range for the update operation
    $range = 'Sheet1!A1:' . chr(ord('A') + $sheetColumn - 1);
    // Create a ValueRange object with the data
    $values = new \Google\Service\Sheets\ValueRange([
        'values' => $rows
    ]);
    // Update the values in the spreadsheet
    $service->spreadsheets_values->update($spreadsheetId, $range, $values, [
        'valueInputOption' => 'USER_ENTERED'
    ]);
    // Get the URL of the created spreadsheet
    $link = $spreadsheet->getSpreadsheetUrl();
    // Prepare a response with success status and the link to the created Google Sheet
    $response = [
        'SUCCESS' => 1,
        'LINK' => $link,
    ];
    // Return the response
    return $response;
}

To explain what this convertJsonToGoogleSheet method does:

  1. Create Google_Client Instance:

    • Instantiate a new instance of the Google_Client class.

  2. Set Application Name:

    • Set the application name for identification to 'Google Sheets'.

  3. Set Scopes:

    • Define the scopes for the requested permissions (in this case, access to spreadsheets).

  4. Set Access Type:

    • Set the access type to 'offline' for obtaining a refresh token.

  5. Check Server Environment:

    • Check if the application is running on a live server.

  6. Configure Client:

    • Configure the client based on the server environment.

      • If on a live server, set the authentication configuration using live server credentials.

      • If on a local server, set the authentication configuration using local server credentials.

  7. Obtain Access Token:

    • Obtain an access token using the authorization code from the request.

  8. Set Access Token:

    • Set the obtained access token to the client.

  9. Create Google Sheets Service Instance:

    • Create a new instance of the Google Sheets service.

  10. Retrieve Title:

    • Retrieve the title for the new spreadsheet from the request.

  11. Create Spreadsheet:

    • Create a new spreadsheet with the specified title.

  12. Retrieve Spreadsheet ID:

    • Retrieve the ID of the created spreadsheet.

  13. Decode JSON Data:

    • Decode JSON data from the request.

  14. Initialize Rows Array:

    • Initialize an array to store unique column names.

  15. Count Number of Columns:

    • Count the number of columns in the sheet.

  16. Initialize 2D Array with Header Row:

    • Initialize a 2D array with the header row.

  17. Populate Rows Array with Data:

    • Populate the rows array with data from the JSON.

  18. Define Range for Update Operation:

    • Define the range for the update operation in the spreadsheet.

  19. Create ValueRange Object:

    • Create a ValueRange object with the data.

  20. Update Values in Spreadsheet:

    • Update the values in the spreadsheet.

  21. Get Spreadsheet URL:

    • Get the URL of the created spreadsheet.

  22. Prepare Response:

    • Create a response array with a success status ('SUCCESS' => 1).

    • Include the link to the created Google Sheet under the 'LINK' key.

  23. Return Response:

    • Return the response array as the HTTP response.

PreviousContactController.phpNextForgotPasswordController.php

Last updated