stagfe2/app/Http/Controllers/GsheetController.php

<?php

namespace App\Http\Controllers;

use Exception;
use Google\Service\Sheets\ValueRange;
use Google_Client;
use Illuminate\Console\Command;
use Revolution\Google\Sheets\Facades\Sheets;
use Revolution\Google\Sheets\Sheets as SheetsSheets;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class GsheetController extends Controller
{
    

    public function testGsheet(){
        $sql = "
        SELECT client.name_first, client.name_last,
       (client.name_first || ' ' || client.name_last) as client_name,
       (mcp_pro.name_first || ' ' || mcp_pro.name_last) as mcp_pro_name,
       (rmm_pro.name_first || ' ' || rmm_pro.name_last) as rmm_pro_name,
       client.uid as client_uid, 
       client.dob,
       client.is_enrolled_in_rm,
       client.most_recent_completed_mcp_note_date,
       client.most_recent_completed_mcp_note_id,
       mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
       mrmnote.id as most_recent_completed_mcp_note_id_cm,
       mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
       client.cell_number,
       client.is_assigned_cellular_bp_device,
       client.is_assigned_cellular_weight_scale_device,
       care_month.uid as care_month_uid,
       care_month.id as care_month_id,
       care_month.start_date,
       care_month.rm_total_time_in_seconds_by_mcp,
       care_month.rm_total_time_in_seconds_by_rmm_pro,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_anyone_interacted_with_client_about_rm,
       care_month.has_mcp_interacted_with_client_about_rm,
       care_month.rm_num_measurements_not_stamped_by_mcp,
       care_month.rm_num_measurements_not_stamped_by_non_hcp,
       care_month.rm_num_measurements_not_stamped_by_rmm,
       care_month.rm_num_measurements_not_stamped_by_rme,
       care_month.mcp_pro_id as care_month_mcp_pro_id,
       care_month.rmm_pro_id as care_month_rmm_pro_id,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number,
       client.most_recent_cellular_bp_dbp_mm_hg,
       client.most_recent_cellular_bp_sbp_mm_hg,
       client.most_recent_cellular_bp_measurement_at,      
       client.most_recent_cellular_weight_value,
       client.most_recent_cellular_weight_measurement_at
FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
     left join note mrmnote on mrmnote.id = (
        select max(n.id) from note n 
        where 
              n.client_id = client.id AND 
              n.is_cancelled = FALSE AND
              (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
              n.effective_dateest::date >= care_month.start_date::date AND 
              n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
     )
    left join pro mcp_pro on care_month.mcp_pro_id = mcp_pro.id
    left join pro rmm_pro on care_month.rmm_pro_id = rmm_pro.id
        ";
        $sqlParams = [];

        $values = $this->getValues($sql, $sqlParams);

        $spreadsheetId = '1jRCkukeSfZufY8gpKSGYgv9cgeDbA1ZYP4pKJhyPP_o';
        $sheetName = 'Sheet1';
        return $this->exportToGsheet($sql, $sqlParams, $spreadsheetId, $sheetName);

    }


    private function exportToGsheet($sql, $sqlParams, $spreadsheetId, $sheetName){
       
        $client = $this->getApiClient();
        $service = new \Google\Service\Sheets($client);

        
        $values = $this->getValues($sql, $sqlParams);

        $range = $sheetName.'!A1:V1';
        $body = new ValueRange([
            'values' => $values
        ]);
        $params = [
           'valueInputOption' => 'RAW'
        ];

        $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);

        return $result;
    }


    function getApiClient(){
      
        $KEY_FILE_LOCATION = storage_path('stag-gsheets-d9ead2f78b4b.json');

        $client = new Google_Client();
        $client->setApplicationName("My Rooster Admin");
        $client->setAuthConfig($KEY_FILE_LOCATION);
        
        $client->setScopes([\Google\Service\Sheets::DRIVE, \Google\Service\Sheets::SPREADSHEETS]);

        return $client;

    }


    private function getValues($sql, $sqlParams){

        $values = [];

        $raws = DB::select($sql, $sqlParams);

        if(!count($raws)){
            throw new \Exception("No data");
        }

        $firstRaw = $raws[0];

        $headers = [];
        foreach($firstRaw as $k => $val) {
            $headers[] = $k;
        }
        $values[] = $headers;

        
        foreach($raws as $raw){
            $rawStr = '';
            foreach($headers as $header ){
                $rawStr = $rawStr.$raw->$header.'~~';
            }
            $values[] = explode('~~', $rawStr);
        }
        
       
        return $values;
    }

}