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

Here is a short PHP script (relying on Sendinblue API and PhpSpreadsheet) to query your account's locations (company) and extract appointment data for previous month (using syncToken parameter to fetch each page of results):

<?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' => '[email protected]');
$sendSmtpEmail['to'] = array(
    array('email' => $targetEmail)
);
$sendSmtpEmail['cc'] = array(
    array('email' => '[email protected]', 'name' => 'John Doe')
);
$sendSmtpEmail['replyTo'] = array('email' => '[email protected]', '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;
}
?>

Composer settings:

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

0 Votes