PHP | How to extract appointment data into an XLSX file and send it via email

<?php 
require 'vendor/autoload.php';

ini_set("memory_limit", "128M");

if (sizeof($argv) < 2) {
    echo "Usage: php report.php RECIPIENT\n";
    die();
}

$targetEmail = $argv[1];
$server = "app.agendize.com";
$apiKey = "";
$token = "";

$startDate = date("Y-m-d", strtotime('first day of last month'));
$endDate = date("Y-m-d", strtotime('last day of last month'));


/* Excel spreadsheet */

use PhpOffice\PhpSpreadsheet\Spreadsheet; 
use PhpOffice\PhpSpreadsheet\Writer\Xlsx; 

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet(); 
$spreadsheet->getActiveSheet()->getStyle('A1:P1')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('00C8C8C8');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);

$spreadsheet->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);

$spreadsheet->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);

$spreadsheet->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);

$data_from_db = array();
$i = 0;

$data_from_db[0]=array("Location", "City", "Staff", "Date", "Status", "Service", "Service external ID", "Client", "Client email address", "Phone number", "Widget form field value #1");

// Fetching list of companies/locations for the account
$listCompanies = file_get_contents("https://$server/api/2.1/scheduling/companies?apiKey=$apiKey&token=$token");
$companys = json_decode($listCompanies);

foreach ($companys->items as $company) {
    // Fetch appointments for each location
    $url = "https://$server/api/2.1/scheduling/companies/$company->id/appointments?startDate=$startDate&endDate=$endDate&levelDetail=full&apiKey=$apiKey&token=$token";
    $listRdv = file_get_contents($url);
    $rdvs = json_decode($listRdv);

    // Browsing each page's results (syncToken)
    while ($rdvs->items) {
        foreach($rdvs->items as $rdv){
            $data_from_db[$i][] = $company->name;
            $data_from_db[$i][] = $company->address->city;
            $data_from_db[$i][] = $rdv->staff->firstName." ".$rdv->staff->lastName;
            $data_from_db[$i][] = $rdv->start->dateTime;
            $data_from_db[$i][] = $rdv->status;

            $data_from_db[$i][] = $rdv->service->name;
            $data_from_db[$i][] = $rdv->service->externalId;
            $data_from_db[$i][] = $rdv->client->firstName . " " . substr($rdv->client->lastName, 0, 1);
            $data_from_db[$i][] = substr($rdv->client->email, 0, 1) . "..." . substr($rdv->client->email, strpos($rdv->client->email,'@')-1);
            $data_from_db[$i][] = substr($rdv->client->phone, 0, 6) . "...";

            $data_from_db[$i][] = $rdv->form[0]->value;

            $i++;
        }

        // Next page (ie: next syncToken)
        $listRdv = file_get_contents($url . "&syncToken=" . $rdvs->nextSyncToken);
        $rdvs = json_decode($listRdv);
    }
}

//set value row
for ($i=0; $i<count($data_from_db); $i++) {
    //set value for indi cell
    $row = $data_from_db[$i];

    //writing cell index start at 1 not 0
    $j = 1;

    foreach ($row as $x => $x_value) {
        $sheet->setCellValueByColumnAndRow($j, $i+1, $x_value);
        $j = $j + 1;
    }
}

// Write an .xlsx file  
$writer = new Xlsx($spreadsheet); 

// Save .xlsx file to the files directory 
$targetFileName = "AA_" . $startDate . "_" . $endDate . ".xlsx";
$writer->save($targetFileName); 


/* Email */

$config = SendinBlue\Client\Configuration::getDefaultConfiguration()->setApiKey('api-key', '');

$apiInstance = new SendinBlue\Client\Api\TransactionalEmailsApi(
    new GuzzleHttp\Client(),
    $config
);
$sendSmtpEmail = new \SendinBlue\Client\Model\SendSmtpEmail();
$sendSmtpEmail['subject'] = 'Stats (' . $startDate . ' - ' . $endDate . ')';
$sendSmtpEmail['htmlContent'] = '<html><body><p>Hello,</p><p>Here are the statistics up-to-date.</p></body></html>';
$sendSmtpEmail['sender'] = array('name' => 'Agendize', 'email' => 'noreply@agendize.com');
$sendSmtpEmail['to'] = array(
    array('email' => $targetEmail)
);
$sendSmtpEmail['cc'] = array(
    array('email' => 'youremailaddress@yourdomainname.com', 'name' => 'John Doe')
);
$sendSmtpEmail['replyTo'] = array('email' => 'youremailaddress@yourdomainname.com', 'name' => 'John Doe');

$content = chunk_split(base64_encode(file_get_contents($targetFileName)));
$attachment_item = array(
    'name' => $targetFileName,
    'content' => $content
);
$attachment_list = array($attachment_item);
$sendSmtpEmail['attachment'] = $attachment_list;
$sendSmtpEmail['tags'] = array('your-tag');

unlink($targetFileName);

try {
    $company = $apiInstance->sendTransacEmail($sendSmtpEmail);
} catch (Exception $e) {
    echo 'Exception when calling TransactionalEmailsApi->sendTransacEmail: ', $e->getMessage(), PHP_EOL;
}
?>

{
    "name": "script/xlsx",
    "require": {
        "sendinblue/api-v3-sdk": "^7.4",
        "phpoffice/phpspreadsheet": "^1.17"
    }
}