stagfe2/app/Console/Commands/clientsToGsheet.php

<?php

namespace App\Console\Commands;

use App\Models\BDTDevice;
use App\Models\Client;
use App\Models\SimpleProduct;
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;

class clientsToGsheet extends Command
{
  
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'clients:export-to-gsheet';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Export records to gsheet';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $this->pushSheet();
        return 0;
    }


    function pushSheet(){

        $spreadsheetId = config('app.googleSpreadsheetId');
        $googleSpreadsheetProductsSheetName = config('app.googleSpreadsheetProductsSheetName'); 
       
        $client = $this->getApiClient();
        $service = new \Google\Service\Sheets($client);

        
        $values = $this->getValues();


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

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

        $this->info(json_encode($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(){

        $values = [];

        $values[] = [
            'Chart Number',
			'Name',
			'Issued Yet?',
			'Client',
			'Used?',
			'MCP',
			'Issue Date',
			'Last Meas. Date',
			'Last Meas. Days Ago',
			'Days with Remote Meas. this Month',
			'Days with Remote Meas. last Month',
			'Client Engagement Status Category',
			'Admin Engagement Assessment Status',
			'Mcp Engagement Assessment Status',
			'Default Na Engagement Assessment Status'
        ];

        Client::whereNull('shadow_pro_id')->chunk(function($clients) use ($values){
            foreach($clients as $patient){
                $patientStr =($patient->chart_number).'~~'.
                ($patient->displayName()).'~~'.
                (@$patient->mcp ? $patient->mcp->displayName() : '--').'~~'.
                ($patient->mailing_address_state).'~~'.
                ($patient->getPrimaryCoverage()?$patient->getPrimaryCoverage()->insuranceDisplayName():'-').'~~'.
                (friendly_date($patient->most_recent_completed_mcp_note_date) ).'~~'.
                ($patient->nextMcpAppointment ? friendly_date_time($patient->nextMcpAppointment->raw_date.' '.$patient->nextMcpAppointment->raw_start_time) : '-').'~~'.
                (friendlier_date_time($patient->most_recent_cellular_bp_measurement_at) ).'~~'.
                (friendlier_date_time($patient->most_recent_cellular_weight_measurement_at) ).'~~'.
                (friendly_date_time($patient->created_at, false)).'~~'.
                ($patient->getMcpAssignedOn()).'~~'.
                (count($patient->activeNotes)).'~~'.
                ($patient->client_engagement_status_category == 'DUMMY'? 'Test Record': $patient->client_engagement_status_category).'~~'.
                ($patient->temparatureGunDeliveryStatus()).'~~'.
                ($patient->pulseOximeterDeliveryStatus()).'~~'.
                ($patient->hasBPDevice()?'DELIVERED':'').'~~'.
                ($patient->hasWeightScaleDevice()?'DELIVERED':'').'~~';
                $values[] = explode('~~', $patientStr);
            }
        });
       
        return $values;
    }

    
}