Extract reusable code to a class, and separate out the command line application

[fulldecent]

This repo works great, thank you! I have tested it in our production environment and it's good to go live. With changes.

As current, this repo is a full application. You must only use it as-is and configure using the provided config file.

To use it in our environment, it was necessary for us to instead connect our existing configuration management and also make other changes. I am prepared to contribute those changes back here.

There are a few different types of changes here. So I thought it best to ask if you generally like these ideas/changes before sending in a large PR.

Changes

• Separate out the code to perform sync (new kimaiSync) from the SingleCommandApplication
  • Support generic output methods (which are also compatible to Symphony OutputInterface)
• Separate out configuration management, to allow any configuration approach
• Allow table name prefix/suffix with new $databaseTableFormat parameter
• Write a thin Symphony wrapper using this separated code to work just as the current code works

Proposed approach

• Maintainer briefly reviews my code below
• I finish work and send a full pull request
• Maintainer merges PR
• Maintainer increments package.json version
• Maintainer publishes package to Packagist

Draft code (works in production, but I can improve further when making a PR)

First add PSR autoloader to package.json.

src/Sync.php

If I will proceed here, I will further edit this into a class.

// Convert from API response like '2022-01-14T13:45:47+00:00' to database native format
// 
// @param string $fieldName Field name to be converted
// @param string|null $date Date string to be converted
// @param string $databaseDatetimeFormat Database datetime format
// @return array [fieldName, convertedValue]
function dateConverter(string $fieldName, ?string $date, string $databaseDatetimeFormat): array
{
    $converted = null;
    if ($date !== null) {
        $tmp = new DateTimeImmutable($date);
        $converted = $tmp->format($databaseDatetimeFormat);
    }
    return [$fieldName, $converted];
}

// Fetch data from Kimai API
//
// @param Client $client Guzzle client
// @param string $endpoint API endpoint to fetch
// @return array|false Array of data or false on error
function doGet(Client $client, string $endpoint): array|false
{
    $response = $client->get($endpoint);
    if ($response->getStatusCode() === 404) {
        return false;
    }
    return json_decode($response->getBody()->getContents(), true);
}

/**
 * Sync data from Kimai API to local database
 * 
 * @param callable $writeLn callable Output one line of text with some status updates
 * @param callable $writeLnSuccess callable Output one line of text with some status updates, indicated successful part
 * @param callable $writeLnError callable Output one line of text with some status updates, indicated error part
 * @param bool $onlyTimesheets Only sync timesheets (for hourly cronjob)
 * @param DateTimeImmutable $modifiedSince Only timesheets that were modified after this date will be synced, by default latest 24 hours
 * 
 * Throws Exception on inescaple errors
 */
function kimaiSync(
    callable $writeLn,
    callable $writeLnSuccess,
    callable $writeLnError,
    bool $onlyTimesheets = false,
    DateTimeImmutable $modifiedSince = new DateTimeImmutable('-24 hours'),
    string $kimaiApiUrl,
    string $kimaiApiToken,
    string $databaseConnection,
    string $databaseUser,
    string $databasePassword,
    string $databaseTableFormat = '`%s`',
    string $databaseColumnFormat = '`%s`',
    string $databaseDatetimeFormat = 'Y-m-d H:i:s',
    string $proxyUrl = ''
) {
    // Setup database connection
    $connection = new PDO($databaseConnection, $databaseUser, $databasePassword);

    // Setup HTTP client
    $clientOptions = [
        'base_uri' => $kimaiApiUrl,
        'verify' => false,
        'headers' => ['Authorization' => 'Bearer ' . $kimaiApiToken],
    ];
    if (defined('PROXY_URL') && !empty($proxyUrl)) {
        $clientOptions['proxy'] = $proxyUrl;
    }
    $client = new Client($clientOptions);

    // Reusable function to sync one endpoint
    $syncEndpoint = function ($title, $settings) use ($connection, $client, $writeLn, $writeLnSuccess, $writeLnError, $databaseTableFormat, $databaseColumnFormat): void {
        $apiEntities = [];
        $existingEntities = []; // mapping local id to kimai id in local database
        $localColumns = []; // column names on local side to prepare SQL statements

        // fetch the API result
        $results = doGet($client, $settings['endpoint']);
        if ($results === false) {
            $writeLnError(sprintf('Failed to sync data for endpoint: %s', $settings['endpoint']));
        }

        // prepare the array of all entities for the local database by mapping columns
        foreach ($results as $entity) {
            $newEntity = [];
            foreach ($settings['mapping'] as $kimaiField => $localField) {
                $key = $localField;
                $value = $entity[$kimaiField];
                // some values need to be converted to local format (eg. datetime)
                if (is_callable($localField)) {
                    $tmp = call_user_func($localField, $entity, $kimaiField);
                    $key = $tmp[0];
                    $value = $tmp[1];
                }
                $newEntity[$key] = $value;
            }
            if (count($localColumns) === 0) {
                $localColumns = array_keys($newEntity);
            }
            $apiEntities[$entity['id']] = $newEntity;
        }

        unset($results);

        if (count($apiEntities) === 0) {
            $writeLnSuccess('No data found to sync: ' . $title);
            return;
        }

        // convert all column names using configured format (e.g. prefix/suffix)
        $localColumns = array_map(function ($columnName) use ($databaseColumnFormat) {
            return sprintf($databaseColumnFormat, $columnName);
        }, $localColumns);

        // convert table name using configured format (e.g. prefix/suffix)
        $tableName = sprintf($databaseTableFormat, $settings['table']);

        // fetch all existing entries to decide if we update or insert
        $sql = sprintf('SELECT id, kimai_id FROM %s WHERE kimai_id IN (%s)', $tableName, implode(',', array_keys($apiEntities)));
        $stmt = $connection->prepare($sql);
        try {
            if ($stmt->execute() === false) {
                $writeLnError($sql);
            }
        } catch (Exception $ex) {
            $writeLnError($sql . PHP_EOL . $ex->getMessage());
        }
        $existing = $stmt->fetchAll();
        foreach ($existing as $existingValues) {
            $existingEntities[$existingValues['kimai_id']] = $existingValues['id'];
        }

        // prepare the insert statement
        $columnsReplacer = [];
        for ($i = 0; $i < count($localColumns); $i++) {
            $columnsReplacer[] = '?';
        }
        $sqlInsert = sprintf('INSERT INTO %s (%s) VALUES (%s)', $tableName, implode(',', $localColumns), implode(',', $columnsReplacer));
        $stmtInsert = $connection->prepare($sqlInsert);

        // prepare the update statement
        $columnsReplacer = [];
        foreach ($localColumns as $localField) {
            $columnsReplacer[] = $localField . ' = ?';
        }
        $sqlUpdate = sprintf('UPDATE %s SET %s WHERE id = ?', $tableName, implode(',', $columnsReplacer));
        $stmtUpdate = $connection->prepare($sqlUpdate);

        foreach ($apiEntities as $kimaiId => $values) {
            if (array_key_exists($kimaiId, $existingEntities)) {
                $values[] = $existingEntities[$kimaiId];
                if ($stmtUpdate->execute(array_values($values)) === false) {
                    $writeLnError(sprintf('Failed updating "%s" for ID "%s" with: %s', $tableName, $existingEntities[$kimaiId], $stmtUpdate->errorInfo()[2]));
                }
            } else {
                if ($stmtInsert->execute(array_values($values)) === false) {
                    $writeLnError(sprintf('Failed inserting into "%s" with: %s', $tableName, $stmtInsert->errorInfo()[2]));
                }
            }
        }
        $writeLnSuccess('Synced ' . $title . ': ' . count($apiEntities));
    };

    $syncConfig = [
        'Customer' => [
            'table' => 'customer',
            'endpoint' => 'customers',
            'mapping' => [
                'id' => 'kimai_id',
                'name' => 'name',
                'number' => 'number',
            ],
        ],
        'Projects' => [
            'table' => 'project',
            'endpoint' => 'projects',
            'mapping' => [
                'id' => 'kimai_id',
                'customer' => 'customer',
                'name' => 'name',
                'start' => function ($entity, $fieldName) use ($databaseDatetimeFormat) {
                    return dateConverter('start', $entity[$fieldName], $databaseDatetimeFormat);
                },
                'end' => function ($entity, $fieldName) use ($databaseDatetimeFormat) {
                    return dateConverter('end', $entity[$fieldName], $databaseDatetimeFormat);
                },
            ],
        ],
        'Activities' => [
            'table' => 'activity',
            'endpoint' => 'activities',
            'mapping' => [
                'id' => 'kimai_id',
                'project' => 'project',
                'name' => 'name',
            ],
        ],
        'Users' => [
            'table' => 'user',
            'endpoint' => 'users',
            'mapping' => [
                'id' => 'kimai_id',
                'alias' => 'alias',
                'username' => 'username',
            ],
        ],
        'Teams' => [
            'table' => 'team',
            'endpoint' => 'teams',
            'mapping' => [
                'id' => 'kimai_id',
                'name' => 'name',
            ],
        ],
    ];

    if ($onlyTimesheets) {
        $syncConfig = [];
    }

    $syncConfig['Timesheets'] = [
        'table' => 'timesheet',
        'endpoint' => 'timesheets?user=all&modified_after=' . $modifiedSince->format('Y-m-d\TH:i:s') . '&size=' . PHP_INT_MAX,
        'mapping' => [
            'id' => 'kimai_id',
            'activity' => 'activity',
            'project' => 'project',
            'user' => 'user',
            'begin' => function ($entity, $fieldName) use ($databaseDatetimeFormat) {
                return dateConverter('begin', $entity[$fieldName], $databaseDatetimeFormat);
            },
            'end' => function ($entity, $fieldName) use ($databaseDatetimeFormat) {
                return dateConverter('end', $entity[$fieldName], $databaseDatetimeFormat);
            },
            'duration' => 'duration',
            'description' => function ($entity, $fieldName) {
                $value = $entity[$fieldName];
                if ($value !== null && mb_strlen($value) > 200) {
                    $value = mb_substr($value, 0, 200);
                }
                return ['description', $value];
            },
            'rate' => 'rate',
            'internalRate' => 'internalRate',
            'billable' => function ($entity, $fieldName) {
                $value = 1;
                if (!$entity[$fieldName]) {
                    $value = 0;
                }
                return ['billable', $value];
            },
        ],
    ];

    foreach ($syncConfig as $title => $settings)
    {
        $syncEndpoint($title, $settings);
    }

    if ($onlyTimesheets) {
        return;
    }

    // SPECIAL HANDLING FOR TEAMS
    $teamTable = sprintf($databaseTableFormat, 'team');
    $stmt = $connection->prepare(sprintf('SELECT id, kimai_id FROM %s', $teamTable));
    $stmt->execute();
    $teams = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $teamProjects = [];
    $teamUsers = [];
    $deleteIds = [];

    $writeLn('Syncing teams, user and project links ...');
    $totalCount = count($teams);
    $doneCount = 0;
    // $progress = new ProgressBar($output, count($teams)); // not available in this context

    foreach ($teams as $team) {
        $kimaiTeamId = $team['kimai_id'];
        $teamId = $team['id'];

        try {
            $team = doGet($client, 'teams/' . $kimaiTeamId);
        } catch (ClientException $ex) {
            if ($ex->getResponse()->getStatusCode() === 404) {
                $deleteIds[] = $teamId;
                continue;
            }
        }

        foreach ($team['members'] as $member) {
            $teamUsers[$kimaiTeamId][] = $member['user']['id'];
        }
        foreach ($team['projects'] as $project) {
            $teamProjects[$kimaiTeamId][] = $project['id'];
        }

        usleep(500); // be polite and do not overstress remote Server/API
        $doneCount++;
        // $progress->advance(); // not available in this context
        $writeLn(sprintf('Syncing teams, user and project links ... %s/%s', $doneCount, $totalCount));
    }
    // $progress->finish(); // not available in this context

    foreach ($deleteIds as $deleteId) {
        // make sure table is always empty before inserting the relations between user and team
        $stmt = $connection->prepare('DELETE FROM team WHERE id = ' . $deleteId);
        $stmt->execute();
    }

    // make sure table is always empty before inserting the relations between user and team
    $teamUserTable = sprintf($databaseTableFormat, 'team_user');
    $stmt = $connection->prepare(sprintf('DELETE FROM %s', $teamUserTable));
    $stmt->execute();

    $stmt = $connection->prepare(sprintf('INSERT INTO %s (team_kimai_id, user_kimai_id) VALUES (?, ?)', $teamUserTable));
    foreach ($teamUsers as $kimaiTeamId => $kimaiUserIds) {
        foreach ($kimaiUserIds as $kimaiUserId) {
            if ($stmt->execute([$kimaiTeamId, $kimaiUserId]) === false) {
                $writeLnError(sprintf('Failed inserting into "team_user" with: %s', $stmt->errorInfo()[2]));
            }
        }
    }

    // make sure table is always empty before inserting the relations between project and team
    $teamProjectTable = sprintf($databaseTableFormat, 'team_project');
    $stmt = $connection->prepare(sprintf('TRUNCATE %s', $teamProjectTable));
    $stmt->execute();

    $stmt = $connection->prepare(sprintf('INSERT INTO %s (team_kimai_id, project_kimai_id) VALUES (?, ?)', $teamProjectTable));
    foreach ($teamProjects as $kimaiTeamId => $kimaiProjectIds) {
        foreach ($kimaiProjectIds as $kimaiProjectId) {
            if ($stmt->execute([$kimaiTeamId, $kimaiProjectId]) === false) {
                $writeLnError(sprintf('Failed inserting into "team_project" with: %s', $stmt->errorInfo()[2]));
            }
        }
    }
}

Updates to README

This example shows how to use the above class inside your own production system.

// Call function connected from pmt-slim configuration
kimaiSync(
    writeLn: function(string $line) {
        echo $line . PHP_EOL;
    },
    writeLnSuccess: function(string $line) {
        echo '✅ ' . $line . PHP_EOL;
    },
    writeLnError: function(string $line) {
        echo '❌ ' . $line . PHP_EOL;
    },
    onlyTimesheets: false,
    modifiedSince: new DateTimeImmutable('-24 hours'),
    kimaiApiUrl: $_ENV['KIMAI_API_URL'],
    kimaiApiToken: $_ENV['KIMAI_API_TOKEN'],
    databaseConnection: $_ENV['DB_DSN'],
    databaseUser: $_ENV['DB_USER'],
    databasePassword: $_ENV['DB_PASS'],
    databaseTableFormat: $_ENV['KIMAI_DB_TABLE_FORMAT']
);

`bin/KimaiSyncSymphonyCommand.php

Then here is the Symphony wrapper around the reusable code above which uses the config file.

// Command application wrapper for the kimaiSync function using config from configuration.local.php or configuration.php
(new SingleCommandApplication())
    ->setName('Sync Kimai data via API')
    ->setVersion('1.0')
    ->addOption('timesheets', null, InputOption::VALUE_NONE, 'Only sync timesheets (for hourly cronjob)')
    ->addOption('modified', null, InputOption::VALUE_REQUIRED, 'Only timesheets that were modified after this date will be synced, by default latest 24 hours. Format: 2022-01-14 13:45:47')
    ->setCode(function (InputInterface $input, OutputInterface $output): int {

        $io = new SymfonyStyle($input, $output);

        $modifiedSince = $input->getOption('modified');
        if ($modifiedSince === null) {
            $modifiedSince = new DateTimeImmutable('-24 hours');
        } else {
            try {
                $modifiedSince = new DateTimeImmutable($modifiedSince);
            } catch (Exception $ex) {
                $io->error('Invalid "since" date given, please check your format.');
                return 1;
            }
        }

        kimaiSync(
            writeLn: function(string $line) use ($io) {
                $io->writeln($line);
            },
            writeLnSuccess: function(string $line) use ($io) {
                $io->success($line);
            },
            writeLnError: function(string $line) use ($io) {
                $io->error($line);
            },
            onlyTimesheets: $input->getOption('timesheets'),
            modifiedSince: $modifiedSince,
            kimaiApiUrl: KIMAI_API_URL,
            kimaiApiToken: KIMAI_API_TOKEN,
            databaseConnection: DATABASE_CONNECTION,
            databaseUser: DATABASE_USER,
            databasePassword: DATABASE_PASSWORD,
            databaseTableFormat: DATABASE_COLUMN,
            databaseColumnFormat: DATABASE_COLUMN,
            databaseDatetimeFormat: DATABASE_DATETIME_FORMAT
        );
        return 0;
    })
    ->run();

config/

Move configuration to this folder. This is best practice established by https://github.com/php-pds/skeleton

Update .gitignore.

Load the .local config, never the example config. Rename example config with .example. Do not read .example from the code. Update README to instruct people to copy example config to config before using tool. This approach is shown as best practice, identified at https://github.com/vlucas/phpdotenv

And add additional line for DATABASE_TABLE

Future work, not included here

• Separate out database instantiation, just accept a PDO object