1215 lines
58 KiB
PHP
1215 lines
58 KiB
PHP
<?php
|
|
|
|
namespace App\Console\Commands;
|
|
|
|
use Illuminate\Console\Command;
|
|
use Illuminate\Support\Facades\Artisan;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Storage;
|
|
|
|
class MigrateCyclosCommand extends Command
|
|
{
|
|
protected $signature = 'migrate:cyclos {source_db? : Name of the source Cyclos database (skips prompt if provided)}';
|
|
protected $description = 'Migrates all Cyclos users, user groups, profile images accounts and transactions from the old Cyclos database to the new Laravel database ?';
|
|
|
|
public function handle()
|
|
{
|
|
// Disable job dispatching during migration to prevent ReactionCreated jobs
|
|
\Queue::fake();
|
|
|
|
// Ask for source database name (skip prompt if passed as argument)
|
|
$sourceDb = $this->argument('source_db');
|
|
if (empty($sourceDb)) {
|
|
$this->info('The source Cyclos database should be imported into MySQL and accessible from this application.');
|
|
$this->info('Hint: Use the following commands to create the database and import the dump:');
|
|
$this->info(' mysql -h 127.0.0.1 -u root -p -e "CREATE DATABASE IF NOT EXISTS cyclos_db CHARACTER SET utf8mb3;"');
|
|
$this->info(' mysql -h 127.0.0.1 -u root -p --force cyclos_db < cyclos_dump.sql');
|
|
$this->info('Note: Use --force to skip non-critical errors (e.g. Cyclos statistics views).');
|
|
$this->info('Note: Do NOT use GUI tools like Antares or TablePlus to import — they corrupt binary BLOB data (profile images).');
|
|
$sourceDb = $this->ask('Enter the name of the source Cyclos database');
|
|
}
|
|
|
|
if (empty($sourceDb)) {
|
|
$this->error('Source database name is required.');
|
|
return 1;
|
|
}
|
|
|
|
// Remove .sql extension if present
|
|
if (str_ends_with(strtolower($sourceDb), '.sql')) {
|
|
$sourceDb = substr($sourceDb, 0, -4);
|
|
$this->info("Using database name: {$sourceDb}");
|
|
}
|
|
|
|
// Verify the database exists
|
|
$databases = DB::select('SHOW DATABASES');
|
|
$databaseNames = array_map(fn($db) => $db->Database, $databases);
|
|
|
|
if (!in_array($sourceDb, $databaseNames)) {
|
|
$this->error("Database '{$sourceDb}' does not exist.");
|
|
$this->info('Available databases:');
|
|
foreach ($databaseNames as $name) {
|
|
if (!in_array($name, ['information_schema', 'mysql', 'performance_schema', 'sys'])) {
|
|
$this->line(" - {$name}");
|
|
}
|
|
}
|
|
return 1;
|
|
}
|
|
|
|
// Store in cache for use by subsequent commands during db:seed
|
|
cache()->put('cyclos_migration_source_db', $sourceDb, now()->addHours(1));
|
|
|
|
$destinationDb = env('DB_DATABASE');
|
|
|
|
$userAccountName = timebank_config('accounts.user.name');
|
|
$userLimitMin = timebank_config('accounts.user.limit_min') === null ? 'NULL' : timebank_config('accounts.user.limit_min');
|
|
$userLimitMax = timebank_config('accounts.user.limit_max') === null ? 'NULL' : timebank_config('accounts.user.limit_max');
|
|
|
|
$userProjectAccountName = timebank_config('accounts.user.name');
|
|
$userProjectLimitMin = timebank_config('accounts.user_project.limit_min') === null ? 'NULL' : timebank_config('accounts.user_project.limit_min');
|
|
$userProjectLimitMax = timebank_config('accounts.user_project.limit_max') === null ? 'NULL' : timebank_config('accounts.user_project.limit_max');
|
|
|
|
$giftAccountName = 'gift'; // Temporary name as this account will be removed after migration
|
|
$giftLimitMin = 0;
|
|
$giftLimitMax = 5 * 60;
|
|
$giftAccountName = $giftAccountName === null ? 'NULL' : "'$giftAccountName'";
|
|
$giftLimitMin = $giftLimitMin === null ? 'NULL' : $giftLimitMin;
|
|
$giftLimitMax = $giftLimitMax === null ? 'NULL' : $giftLimitMax;
|
|
|
|
$orgAccountName = timebank_config('accounts.organization.name');
|
|
$orgLimitMin = timebank_config('accounts.organization.limit_min') === null ? 'NULL' : timebank_config('accounts.organization.limit_min');
|
|
$orgLimitMax = timebank_config('accounts.organization.limit_max') === null ? 'NULL' : timebank_config('accounts.organization.limit_max');
|
|
|
|
$bankAccountName = timebank_config('accounts.bank.name');
|
|
$bankLimitMin = timebank_config('accounts.bank.limit_min') === null ? 'NULL' : timebank_config('accounts.bank.limit_min');
|
|
$bankLimitMax = timebank_config('accounts.bank.limit_max') === null ? 'NULL' : timebank_config('accounts.bank.limit_max');
|
|
|
|
$communityAccountName = timebank_config('accounts.community.name');
|
|
$communityLimitMin = timebank_config('accounts.community.limit_min') === null ? 'NULL' : timebank_config('accounts.community.limit_min');
|
|
$communityLimitMax = timebank_config('accounts.community.limit_max') === null ? 'NULL' : timebank_config('accounts.community.limit_max');
|
|
|
|
$debitAccountName = timebank_config('accounts.debit.name');
|
|
$debitLimitMin = timebank_config('accounts.debit.limit_min') === null ? 'NULL' : timebank_config('accounts.debit.limit_min');
|
|
$debitLimitMax = timebank_config('accounts.debit.limit_max') === null ? 'NULL' : timebank_config('accounts.debit.limit_max');
|
|
|
|
|
|
|
|
|
|
// MIGRATE MEMBERS
|
|
|
|
// Active Users (group_id 5)
|
|
DB::beginTransaction();
|
|
try {
|
|
$activeUsers = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.users (cyclos_id, full_name, email, email_verified_at, created_at, updated_at, name, cyclos_salt, password, limit_min, limit_max, last_login_at)
|
|
SELECT
|
|
m.id AS cyclos_id,
|
|
m.name AS full_name,
|
|
m.email AS email,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.member_activation_date)) AS email_verified_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
|
|
NOW() AS updated_at,
|
|
u.username AS name,
|
|
u.salt AS cyclos_salt,
|
|
u.password AS password,
|
|
" . $userLimitMin . " as limit_min,
|
|
" . $userLimitMax . " as limit_max,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(u.last_login)) AS last_login_at
|
|
FROM `{$sourceDb}`.`members` m
|
|
JOIN `{$sourceDb}`.`users` u ON m.id = u.id
|
|
WHERE m.group_id = 5
|
|
ON DUPLICATE KEY UPDATE
|
|
full_name = VALUES(full_name),
|
|
email = VALUES(email),
|
|
created_at = VALUES(created_at),
|
|
updated_at = NOW(),
|
|
name = VALUES(name),
|
|
cyclos_salt = VALUES(cyclos_salt),
|
|
password = VALUES(password),
|
|
last_login_at = VALUES(last_login_at);
|
|
");
|
|
DB::commit();
|
|
$this->info("Users: $activeUsers");
|
|
$this->info("Users migration completed successfully");
|
|
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Users migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
// Reset last_login_at to yesterday for active users whose last login is older than
|
|
// the inactivity threshold. This prevents the profiles:mark-inactive cron from
|
|
// immediately marking imported active users as inactive on first run.
|
|
$inactiveDays = timebank_config('profile_inactive.days_not_logged_in', 350);
|
|
$threshold = now()->subDays($inactiveDays)->toDateTimeString();
|
|
$resetUsers = DB::affectingStatement("
|
|
UPDATE {$destinationDb}.users
|
|
SET last_login_at = DATE_SUB(NOW(), INTERVAL 1 DAY)
|
|
WHERE inactive_at IS NULL
|
|
AND deleted_at IS NULL
|
|
AND (last_login_at IS NULL OR last_login_at < '{$threshold}')
|
|
");
|
|
$this->info("Reset last_login_at to yesterday for {$resetUsers} active users older than {$inactiveDays} days.");
|
|
|
|
|
|
// Inactive Users (group_id 6)
|
|
DB::beginTransaction();
|
|
try {
|
|
$inActiveUsers = DB::affectingStatement("
|
|
|
|
INSERT INTO {$destinationDb}.users (cyclos_id, full_name, email, email_verified_at, created_at, updated_at, name, cyclos_salt, password, limit_min, limit_max, last_login_at, inactive_at)
|
|
SELECT
|
|
m.id AS cyclos_id,
|
|
m.name AS full_name,
|
|
m.email AS email,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.member_activation_date)) AS email_verified_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
|
|
NOW() AS updated_at,
|
|
u.username AS name,
|
|
u.salt AS cyclos_salt,
|
|
u.password AS password,
|
|
" . $userLimitMin . " as limit_min,
|
|
" . $userLimitMax . " as limit_max,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(u.last_login)) AS last_login_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(ghl.start_date)) AS inactive_at
|
|
FROM `{$sourceDb}`.`members` m
|
|
JOIN `{$sourceDb}`.`users` u ON m.id = u.id
|
|
LEFT JOIN `{$sourceDb}`.`group_history_logs` ghl ON m.id = ghl.element_id
|
|
WHERE ghl.group_id = 6 AND ghl.end_date IS NULL AND m.group_id = 6
|
|
");
|
|
DB::commit();
|
|
$this->info("Inactive Users: $inActiveUsers");
|
|
$this->info("Inactive Users migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Inactive Users migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
//Removed Users (cyclos group_id 8):
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$removedUsers = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.users (cyclos_id, full_name, email, email_verified_at, created_at, updated_at, name, cyclos_salt, password, limit_min, limit_max, last_login_at, deleted_at)
|
|
SELECT
|
|
m.id AS cyclos_id,
|
|
CONCAT('Removed Cyclos user', m.id) AS full_name,
|
|
CONCAT(m.id, '@removed.mail') AS email,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.member_activation_date)) AS email_verified_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
|
|
NOW() AS updated_at,
|
|
CONCAT('Removed user ', m.id) AS name,
|
|
u.salt AS cyclos_salt,
|
|
u.password AS password,
|
|
0 as limit_min,
|
|
0 as limit_max,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(u.last_login)) AS last_login_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(ghl.start_date)) AS deleted_at
|
|
FROM `{$sourceDb}`.`members` m
|
|
JOIN `{$sourceDb}`.`users` u ON m.id = u.id
|
|
LEFT JOIN `{$sourceDb}`.`group_history_logs` ghl ON m.id = ghl.element_id
|
|
WHERE ghl.group_id = 8 AND ghl.end_date IS NULL AND m.group_id = 8
|
|
ON DUPLICATE KEY UPDATE
|
|
full_name = VALUES(full_name),
|
|
email = VALUES(email),
|
|
created_at = VALUES(created_at),
|
|
updated_at = NOW(),
|
|
name = VALUES(name),
|
|
cyclos_salt = VALUES(cyclos_salt),
|
|
password = VALUES(password),
|
|
last_login_at = VALUES(last_login_at),
|
|
deleted_at = VALUES(deleted_at);
|
|
");
|
|
DB::commit();
|
|
$this->info("Removed Users: $removedUsers");
|
|
$this->info("Removed Users migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Removed Users migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
//Local Bank (Level I) (Cyclos group_id 13)
|
|
DB::beginTransaction();
|
|
try {
|
|
$localBanks = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.banks (cyclos_id, name, full_name, email, email_verified_at, cyclos_salt, password, level, limit_min, limit_max, created_at, updated_at, last_login_at)
|
|
SELECT
|
|
m.id AS cyclos_id,
|
|
u.username AS name,
|
|
m.name as full_name,
|
|
m.email as email,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.member_activation_date)) AS email_verified_at,
|
|
u.salt AS cyclos_salt,
|
|
u.password AS password,
|
|
'1' as level,
|
|
" . $bankLimitMin . " as limit_min,
|
|
" . $bankLimitMax . " as limit_max,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
|
|
NOW() AS updated_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(u.last_login)) AS last_login_at
|
|
FROM `{$sourceDb}`.`members` m
|
|
JOIN `{$sourceDb}`.`users` u ON m.id = u.id
|
|
WHERE m.group_id = 13
|
|
ON DUPLICATE KEY UPDATE
|
|
name = VALUES(name),
|
|
name = VALUES(full_name),
|
|
email = VALUES(email),
|
|
email_verified_at = VALUES(email_verified_at),
|
|
created_at = VALUES(created_at),
|
|
updated_at = NOW();
|
|
");
|
|
DB::commit();
|
|
$this->info("Local Banks (Level I): $localBanks");
|
|
$this->info("Local Banks (Level I) migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Local Banks (Level I) migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
//Organizations (cyclos group_id 14)
|
|
DB::beginTransaction();
|
|
try {
|
|
$organizations = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.organizations (cyclos_id, name, full_name, email, email_verified_at, cyclos_salt, password, limit_min, limit_max, created_at, updated_at, last_login_at)
|
|
SELECT
|
|
m.id AS cyclos_id,
|
|
u.username AS name,
|
|
m.name AS full_name,
|
|
m.email AS email,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.member_activation_date)) AS email_verified_at,
|
|
u.salt AS cyclos_salt,
|
|
u.password AS password,
|
|
" . $orgLimitMin . " as limit_min,
|
|
" . $orgLimitMax . " as limit_max,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
|
|
NOW() AS updated_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(u.last_login)) AS last_login_at
|
|
FROM `{$sourceDb}`.`members` m
|
|
JOIN `{$sourceDb}`.`users` u ON m.id = u.id
|
|
WHERE m.group_id = 14
|
|
ON DUPLICATE KEY UPDATE
|
|
name = VALUES(name),
|
|
email = VALUES(email),
|
|
created_at = VALUES(created_at),
|
|
updated_at = NOW();
|
|
");
|
|
DB::commit();
|
|
$this->info("Organizations: $organizations");
|
|
$this->info("Organizations migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('User migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
// Same last_login_at reset for active organizations.
|
|
$resetOrgs = DB::affectingStatement("
|
|
UPDATE {$destinationDb}.organizations
|
|
SET last_login_at = DATE_SUB(NOW(), INTERVAL 1 DAY)
|
|
WHERE inactive_at IS NULL
|
|
AND deleted_at IS NULL
|
|
AND (last_login_at IS NULL OR last_login_at < '{$threshold}')
|
|
");
|
|
$this->info("Reset last_login_at to yesterday for {$resetOrgs} active organizations older than {$inactiveDays} days.");
|
|
|
|
|
|
//Projects to create Hours (Level II) (Cyclos group_id 15)y
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$projectsCreateHour = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.banks (cyclos_id, name, full_name, email, email_verified_at, cyclos_salt, password, level, limit_min, limit_max, created_at, updated_at, last_login_at)
|
|
SELECT
|
|
m.id AS cyclos_id,
|
|
u.username AS name,
|
|
m.name AS full_name,
|
|
m.email AS email,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.member_activation_date)) AS email_verified_at,
|
|
u.salt AS cyclos_salt,
|
|
u.password AS password,
|
|
'2' as level,
|
|
" . $bankLimitMin . " as limit_min,
|
|
" . $bankLimitMax . " as limit_max,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
|
|
NOW() AS updated_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(u.last_login)) AS last_login_at
|
|
FROM `{$sourceDb}`.`members` m
|
|
JOIN `{$sourceDb}`.`users` u ON m.id = u.id
|
|
WHERE m.group_id = 15
|
|
ON DUPLICATE KEY UPDATE
|
|
name = VALUES(name),
|
|
email = VALUES(email),
|
|
created_at = VALUES(created_at),
|
|
updated_at = NOW();
|
|
");
|
|
DB::commit();
|
|
$this->info("Projects to create Hours (Level II): $projectsCreateHour");
|
|
$this->info("Projects to create Hours (Level II) migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Projects to create Hours (Level II) migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
//TEST: Projects (Cyclos group_id 18)
|
|
DB::beginTransaction();
|
|
try {
|
|
$projectsCreateHour = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.organizations (cyclos_id, name, full_name, email, email_verified_at, cyclos_salt, password, limit_min, limit_max, created_at, updated_at, last_login_at)
|
|
SELECT
|
|
m.id AS cyclos_id,
|
|
u.username AS name,
|
|
m.name AS full_name,
|
|
m.email AS email,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.member_activation_date)) AS email_verified_at,
|
|
u.salt AS cyclos_salt,
|
|
u.password AS password,
|
|
" . $orgLimitMin . " as limit_min,
|
|
" . $orgLimitMax . " as limit_max,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
|
|
NOW() AS updated_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(u.last_login)) AS last_login_at
|
|
FROM `{$sourceDb}`.`members` m
|
|
JOIN `{$sourceDb}`.`users` u ON m.id = u.id
|
|
WHERE m.group_id = 18
|
|
ON DUPLICATE KEY UPDATE
|
|
name = VALUES(name),
|
|
email = VALUES(email),
|
|
created_at = VALUES(created_at),
|
|
updated_at = NOW();
|
|
");
|
|
DB::commit();
|
|
$this->info("TEST: Projects: $projectsCreateHour");
|
|
$this->info("TEST: Projects migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('TEST: Projects migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
//TEST: Users (Cyclos group_id 18)
|
|
DB::beginTransaction();
|
|
try {
|
|
$projectsCreateHour = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.users (cyclos_id, name, full_name, email, email_verified_at, cyclos_salt, password, limit_min, limit_max, created_at, updated_at, last_login_at)
|
|
SELECT
|
|
m.id AS cyclos_id,
|
|
u.username AS name,
|
|
m.name AS full_name,
|
|
m.email AS email,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.member_activation_date)) AS email_verified_at,
|
|
u.salt AS cyclos_salt,
|
|
u.password AS password,
|
|
" . $userLimitMin . " as limit_min,
|
|
" . $userLimitMax . " as limit_max,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
|
|
NOW() AS updated_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(u.last_login)) AS last_login_at
|
|
FROM `{$sourceDb}`.`members` m
|
|
JOIN `{$sourceDb}`.`users` u ON m.id = u.id
|
|
WHERE m.group_id = 22
|
|
ON DUPLICATE KEY UPDATE
|
|
name = VALUES(name),
|
|
email = VALUES(email),
|
|
created_at = VALUES(created_at),
|
|
updated_at = NOW();
|
|
");
|
|
DB::commit();
|
|
$this->info("TEST: Users: $projectsCreateHour");
|
|
$this->info("TEST: Users migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('TEST: Users migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
// Inactive Projects (group_id 27)
|
|
DB::beginTransaction();
|
|
try {
|
|
$inActiveUsers = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.organizations (cyclos_id, full_name, email, email_verified_at, created_at, updated_at, name, cyclos_salt, password, limit_min, limit_max, last_login_at, inactive_at)
|
|
SELECT
|
|
m.id AS cyclos_id,
|
|
m.name AS full_name,
|
|
m.email AS email,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.member_activation_date)) AS email_verified_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
|
|
NOW() AS updated_at,
|
|
u.username AS name,
|
|
u.salt AS cyclos_salt,
|
|
u.password AS password,
|
|
" . $orgLimitMin . " as limit_min,
|
|
" . $orgLimitMax . " as limit_max,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(u.last_login)) AS last_login_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(ghl.start_date)) AS inactive_at
|
|
FROM `{$sourceDb}`.`members` m
|
|
JOIN `{$sourceDb}`.`users` u ON m.id = u.id
|
|
LEFT JOIN `{$sourceDb}`.`group_history_logs` ghl ON m.id = ghl.element_id
|
|
WHERE ghl.group_id = 27 AND ghl.end_date IS NULL AND m.group_id = 27
|
|
ON DUPLICATE KEY UPDATE
|
|
name = VALUES(name),
|
|
full_name = VALUES(full_name),
|
|
email = VALUES(email),
|
|
created_at = VALUES(created_at),
|
|
updated_at = NOW(),
|
|
name = VALUES(name),
|
|
cyclos_salt = VALUES(cyclos_salt),
|
|
password = VALUES(password),
|
|
last_login_at = VALUES(last_login_at),
|
|
inactive_at = VALUES(inactive_at);
|
|
");
|
|
DB::commit();
|
|
$this->info("Inactive projects: $inActiveUsers");
|
|
$this->info("Inactive projects migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Inactive projects migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
// MIGRATE IMAGES
|
|
// Set charset to latin1 so binary blob data is not mangled by UTF-8 encoding
|
|
DB::statement("SET NAMES latin1");
|
|
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
|
|
$total_images = 0;
|
|
|
|
// Select images with the lowest order_number for each member_id.
|
|
// The image with lowest order_number will become the new Laravel profile image
|
|
$minOrderImages = DB::table($sourceDb . '.images')
|
|
->select('member_id', DB::raw('MIN(order_number) as min_order_number'))
|
|
->groupBy('member_id');
|
|
|
|
// Processing bank images
|
|
$images = DB::table($sourceDb . '.images as i')
|
|
->joinSub(clone $minOrderImages, 'min_order_images', function ($join) {
|
|
$join->on('i.member_id', '=', 'min_order_images.member_id')
|
|
->on('i.order_number', '=', 'min_order_images.min_order_number');
|
|
})
|
|
->join($sourceDb . '.members as m', 'i.member_id', '=', 'm.id')
|
|
->join($sourceDb . '.users as u', 'm.id', '=', 'u.id')
|
|
->select('i.image', 'u.id', 'i.member_id', 'i.order_number')
|
|
->whereIn('m.group_id', [13, 15]) // Banks & Projects to create Hours
|
|
->get();
|
|
foreach ($images as $image) {
|
|
$ext = $this->detectImageExtension($image->image);
|
|
$filename = 'profile-photos/bank_' . uniqid() . '.' . $ext;
|
|
Storage::disk('public')->put($filename, $image->image);
|
|
DB::table($destinationDb . '.banks')
|
|
->where('cyclos_id', $image->id)
|
|
->update(['profile_photo_path' => $filename]);
|
|
}
|
|
// Records without profile photo
|
|
DB::table($destinationDb . '.banks')
|
|
->whereNull('profile_photo_path')
|
|
->update(['profile_photo_path' => 'app-images/profile-user-default.svg']);
|
|
$total_images += $images->count();
|
|
$this->info('Banks: ' . $images->count() . ' images');
|
|
|
|
|
|
|
|
|
|
|
|
// Processing user images
|
|
$images = DB::table($sourceDb . '.images as i')
|
|
->joinSub(clone $minOrderImages, 'min_order_images', function ($join) {
|
|
$join->on('i.member_id', '=', 'min_order_images.member_id')
|
|
->on('i.order_number', '=', 'min_order_images.min_order_number');
|
|
})
|
|
->join($sourceDb . '.members as m', 'i.member_id', '=', 'm.id')
|
|
->join($sourceDb . '.users as u', 'm.id', '=', 'u.id')
|
|
->where('m.group_id', '=', 5) // Users
|
|
->select('i.image', 'u.id', 'i.member_id', 'i.order_number')
|
|
->get();
|
|
foreach ($images as $image) {
|
|
$ext = $this->detectImageExtension($image->image);
|
|
$filename = 'profile-photos/user_' . uniqid() . '.' . $ext;
|
|
Storage::disk('public')->put($filename, $image->image);
|
|
DB::table($destinationDb . '.users')
|
|
->where('cyclos_id', $image->id)
|
|
->update(['profile_photo_path' => $filename]);
|
|
}
|
|
// Records without profile photo
|
|
DB::table($destinationDb . '.users')
|
|
->whereNull('profile_photo_path')
|
|
->update(['profile_photo_path' => 'app-images/profile-user-default.svg']);
|
|
$total_images += $images->count();
|
|
$this->info('Users: ' . $images->count() . ' images');
|
|
|
|
|
|
// Processing organization images
|
|
$images = DB::table($sourceDb . '.images as i')
|
|
->joinSub(clone $minOrderImages, 'min_order_images', function ($join) {
|
|
$join->on('i.member_id', '=', 'min_order_images.member_id')
|
|
->on('i.order_number', '=', 'min_order_images.min_order_number');
|
|
})
|
|
->join($sourceDb . '.members as m', 'i.member_id', '=', 'm.id')
|
|
->join($sourceDb . '.users as u', 'm.id', '=', 'u.id')
|
|
->select('i.image', 'u.id', 'i.member_id', 'i.order_number')
|
|
->where('m.group_id', '=', 14) // Orgaizations
|
|
->get();
|
|
foreach ($images as $image) {
|
|
$ext = $this->detectImageExtension($image->image);
|
|
$filename = 'profile-photos/organization_' . uniqid() . '.' . $ext;
|
|
Storage::disk('public')->put($filename, $image->image);
|
|
DB::table($destinationDb . '.organizations')
|
|
->where('cyclos_id', $image->id)
|
|
->update(['profile_photo_path' => $filename]);
|
|
}
|
|
// Records without profile photo
|
|
DB::table($destinationDb . '.organizations')
|
|
->whereNull('profile_photo_path')
|
|
->update(['profile_photo_path' => 'app-images/profile-user-default.svg']);
|
|
$total_images += $images->count();
|
|
$this->info('Organizations: ' . $images->count() . ' images');
|
|
$this->info('Total: ' . $total_images . ' images written to disk');
|
|
|
|
DB::commit();
|
|
$this->info("Images migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Images migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
// Restore charset to utf8 for remaining queries
|
|
DB::statement("SET NAMES utf8mb4");
|
|
|
|
// MIGRATE ACCOUNTS
|
|
|
|
// Debit Account (cyclos type_id 1, id 1)
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
'{$debitAccountName}' AS name,
|
|
'App\\\\Models\\\\Bank' AS accountable_type,
|
|
1 AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $debitLimitMin . " AS limit_min,
|
|
" . $debitLimitMax . " AS limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
WHERE a.type_id = 1;
|
|
");
|
|
DB::commit();
|
|
$this->info("Debit account: $accounts");
|
|
$this->info("Debit account migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Debit account failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
// Community Account (cyclos type_id 2)
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
'{$communityAccountName}' AS name,
|
|
'App\\\\Models\\\\Bank' AS accountable_type,
|
|
1 AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $communityLimitMin . " AS limit_min,
|
|
" . $communityLimitMax . " AS limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
WHERE a.type_id = 2;
|
|
");
|
|
DB::commit();
|
|
$this->info("Community account: $accounts");
|
|
$this->info("Community account migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Community account failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
// Work account of users (cyclos type_id 5)
|
|
// Note that because of cyclos permission group changes, a Work account can also be owned by Projects, Local banks etc.
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
'{$userAccountName}' AS name,
|
|
'App\\\\Models\\\\User' AS accountable_type,
|
|
u.id AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $userLimitMin . " as limit_min,
|
|
" . $userLimitMax . " as limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
JOIN {$destinationDb}.users u ON a.member_id = u.cyclos_id
|
|
WHERE a.type_id = 5;
|
|
");
|
|
DB::commit();
|
|
$this->info("Work account: $accounts");
|
|
$this->info("Work account migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Work account migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
// Work account of organizations (cyclos type_id 5)
|
|
// Note that because of cyclos permission group changes, a Work account can also be owned by Projects, Local banks etc.
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
'{$orgAccountName}' AS name,
|
|
'App\\\\Models\\\\Organization' AS accountable_type,
|
|
u.id AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $orgLimitMin . " as limit_min,
|
|
" . $orgLimitMax . " as limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
JOIN {$destinationDb}.organizations u ON a.member_id = u.cyclos_id
|
|
WHERE a.type_id = 5;
|
|
");
|
|
DB::commit();
|
|
$this->info("Work account organizations: $accounts");
|
|
$this->info("Work account organizations migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Work account organizations migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
// Work account of banks (cyclos type_id 5)
|
|
// Note that because of cyclos permission group changes, a Work account can also be owned by Projects, Local banks etc.
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
'{$bankAccountName}' AS name,
|
|
'App\\\\Models\\\\Bank' AS accountable_type,
|
|
u.id AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $bankLimitMin . " as limit_min,
|
|
" . $bankLimitMax . " as limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
JOIN {$destinationDb}.banks u ON a.member_id = u.cyclos_id
|
|
WHERE a.type_id = 5;
|
|
");
|
|
DB::commit();
|
|
$this->info("Work account banks: $accounts");
|
|
$this->info("Work account banks migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Work banks organizations migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
// Gift account users (cyclos type_id 6)
|
|
// Note that because of cyclos permission group changes, a Gift account can also be owned by Projects, Local banks etc.
|
|
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
{$giftAccountName} AS name,
|
|
'App\\\\Models\\\\User' AS accountable_type,
|
|
u.id AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $giftLimitMin . " as limit_min,
|
|
" . $giftLimitMax . " as limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
JOIN {$destinationDb}.users u ON a.member_id = u.cyclos_id
|
|
WHERE a.type_id = 6;
|
|
");
|
|
DB::commit();
|
|
$this->info("Gift account users: $accounts");
|
|
$this->info("Gift account users migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Gift account users migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
//TODO Remove this account on each user if it contains 0 transactions!
|
|
|
|
|
|
|
|
// Gift account organizations (cyclos type_id 6)
|
|
// Note that because of cyclos permission group changes, a Gift account can also be owned by Projects, Local banks etc.
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
{$giftAccountName} AS name,
|
|
'App\\\\Models\\\\Organization' AS accountable_type,
|
|
u.id AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $orgLimitMin . " as limit_min,
|
|
" . $orgLimitMax . " as limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
JOIN {$destinationDb}.organizations u ON a.member_id = u.cyclos_id
|
|
WHERE a.type_id = 6;
|
|
");
|
|
DB::commit();
|
|
$this->info("Gift account organizations: $accounts");
|
|
$this->info("Gift account organizations migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Gift account organizations migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
// Gift account banks (cyclos type_id 6)
|
|
// Note that because of cyclos permission group changes, a Gift account can also be owned by Projects, Local banks etc.
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
{$giftAccountName} AS name,
|
|
'App\\\\Models\\\\Bank' AS accountable_type,
|
|
u.id AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $bankLimitMin . " as limit_min,
|
|
" . $bankLimitMax . " as limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
JOIN {$destinationDb}.banks u ON a.member_id = u.cyclos_id
|
|
WHERE a.type_id = 6;
|
|
");
|
|
DB::commit();
|
|
$this->info("Gift account banks: $accounts");
|
|
$this->info("Gift account banks migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Gift account banks migration failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
// Project account users (cyclos type_id 7)
|
|
// Note that because of cyclos permission group changes, a Project account can also be owned by Projects, Local banks etc.
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
'{$userProjectAccountName}' AS name,
|
|
'App\\\\Models\\\\User' AS accountable_type,
|
|
o.id AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $userProjectLimitMin . " as limit_min,
|
|
" . $userProjectLimitMax . " as limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
JOIN {$destinationDb}.users o ON a.member_id = o.cyclos_id
|
|
WHERE a.type_id = 7;
|
|
");
|
|
DB::commit();
|
|
$this->info("Project accounts users: $accounts");
|
|
$this->info("Project accounts users completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Project accounts users failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
// Project account organizations (cyclos type_id 7)
|
|
// Note that because of cyclos permission group changes, a Project account can also be owned by Projects, Local banks etc.
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
'{$orgAccountName}' AS name,
|
|
'App\\\\Models\\\\Organization' AS accountable_type,
|
|
o.id AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $orgLimitMin . " as limit_min,
|
|
" . $orgLimitMax . " as limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
JOIN {$destinationDb}.organizations o ON a.member_id = o.cyclos_id
|
|
WHERE a.type_id = 7;
|
|
");
|
|
DB::commit();
|
|
$this->info("Project accounts organizations: $accounts");
|
|
$this->info("Project accounts organizations completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Project accounts organizations failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
// Project account banks (cyclos type_id 7)
|
|
// Note that because of cyclos permission group changes, a Project account can also be owned by Projects, Local banks etc.
|
|
DB::beginTransaction();
|
|
|
|
try {
|
|
$accounts = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.accounts (name, accountable_type, accountable_id, cyclos_id, created_at, updated_at, limit_min, limit_max)
|
|
SELECT
|
|
'{$orgAccountName}' AS name,
|
|
'App\\\\Models\\\\Bank' AS accountable_type,
|
|
o.id AS accountable_id,
|
|
a.id AS cyclos_id,
|
|
a.creation_date AS created_at,
|
|
a.last_closing_date AS updated_at,
|
|
" . $bankLimitMin . " as limit_min,
|
|
" . $bankLimitMax . " as limit_max
|
|
FROM {$sourceDb}.accounts a
|
|
JOIN {$destinationDb}.banks o ON a.member_id = o.cyclos_id
|
|
WHERE a.type_id = 7;
|
|
");
|
|
DB::commit();
|
|
$this->info("Project accounts banks: $accounts");
|
|
$this->info("Project accounts banks completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Project accounts banks failed: ' . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
|
|
// MIGRATE TRANSACTIONS
|
|
DB::beginTransaction();
|
|
try {
|
|
$transactions = DB::affectingStatement("
|
|
INSERT INTO {$destinationDb}.transactions (from_account_id, to_account_id, amount, description, transaction_type_id, transaction_status_id, created_at, updated_at)
|
|
SELECT
|
|
fromAcc.id AS from_account_id,
|
|
toAcc.id AS to_account_id,
|
|
ROUND(t.amount * 60) AS amount,
|
|
t.description AS description,
|
|
t.type_id AS transaction_type_id,
|
|
1 AS transaction_status_id,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(t.date)) AS created_at,
|
|
FROM_UNIXTIME(UNIX_TIMESTAMP(t.date)) AS updated_at
|
|
FROM `{$sourceDb}`.`transfers` t
|
|
LEFT JOIN {$destinationDb}.accounts fromAcc ON t.from_account_id = fromAcc.cyclos_id
|
|
LEFT JOIN {$destinationDb}.accounts toAcc ON t.to_account_id = toAcc.cyclos_id
|
|
");
|
|
DB::commit();
|
|
$this->info("Transactions: $transactions");
|
|
$this->info("Transaction migration completed successfully");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Transaction migration failed: ' . $e->getMessage());
|
|
}
|
|
$this->info("");
|
|
|
|
|
|
|
|
// ROUNDING CORRECTIONS (post-migration, per account per year)
|
|
// Cyclos stores amounts as decimal hours; ROUND(amount * 60) per transaction
|
|
// accumulates small errors. This inserts one correction per account per calendar
|
|
// year, dated {year}-12-31 23:59:59, so each correction only covers one year's drift.
|
|
$this->info("Applying rounding corrections...");
|
|
DB::beginTransaction();
|
|
try {
|
|
$debitAccountId = DB::table("{$destinationDb}.accounts")
|
|
->where('accountable_type', 'App\\Models\\Bank')
|
|
->where('accountable_id', 1)
|
|
->where('name', $debitAccountName)
|
|
->value('id');
|
|
|
|
if (!$debitAccountId) {
|
|
$this->warn("Could not find Central Bank debit account — skipping rounding corrections.");
|
|
} else {
|
|
// Fetch per-year drift and tx counts (without cumulative balance — added separately below).
|
|
$corrections = DB::select("
|
|
SELECT
|
|
la.id AS laravel_account_id,
|
|
ca.id AS cyclos_account_id,
|
|
YEAR(t.date) AS tx_year,
|
|
COUNT(t.id) AS tx_count,
|
|
ROUND(SUM(IF(t.to_account_id = ca.id, t.amount, -t.amount)) * 60)
|
|
- SUM(ROUND(IF(t.to_account_id = ca.id, t.amount, -t.amount) * 60)) AS diff_min
|
|
FROM {$destinationDb}.accounts la
|
|
INNER JOIN {$sourceDb}.accounts ca ON la.cyclos_id = ca.id
|
|
INNER JOIN {$sourceDb}.transfers t ON t.from_account_id = ca.id OR t.to_account_id = ca.id
|
|
WHERE la.cyclos_id IS NOT NULL
|
|
GROUP BY la.id, ca.id, YEAR(t.date)
|
|
HAVING ABS(diff_min) > 0
|
|
");
|
|
|
|
// Fetch cumulative balances per (cyclos_account_id, year) using a self-join
|
|
// to avoid the correlated subquery that fails under strict GROUP BY mode.
|
|
$cumulativeBalances = DB::select("
|
|
SELECT
|
|
a_outer.id AS cyclos_account_id,
|
|
YEAR(t_outer.date) AS tx_year,
|
|
SUM(IF(t_inner.to_account_id = a_outer.id, t_inner.amount, -t_inner.amount)) AS cumulative_end_balance
|
|
FROM {$sourceDb}.accounts a_outer
|
|
INNER JOIN {$sourceDb}.transfers t_outer ON t_outer.from_account_id = a_outer.id OR t_outer.to_account_id = a_outer.id
|
|
INNER JOIN {$sourceDb}.transfers t_inner ON (t_inner.from_account_id = a_outer.id OR t_inner.to_account_id = a_outer.id)
|
|
AND YEAR(t_inner.date) <= YEAR(t_outer.date)
|
|
INNER JOIN {$destinationDb}.accounts la ON la.cyclos_id = a_outer.id
|
|
WHERE la.cyclos_id IS NOT NULL
|
|
GROUP BY a_outer.id, YEAR(t_outer.date)
|
|
");
|
|
$cumulativeBalanceMap = [];
|
|
foreach ($cumulativeBalances as $cb) {
|
|
$cumulativeBalanceMap[(int) $cb->cyclos_account_id][(int) $cb->tx_year] = (float) $cb->cumulative_end_balance;
|
|
}
|
|
|
|
// Also fetch the total drift per account (ROUND of sum vs sum of ROUNDs across all years)
|
|
// to catch any residual that doesn't distribute evenly across year boundaries.
|
|
$totalDrifts = DB::select("
|
|
SELECT
|
|
la.id AS laravel_account_id,
|
|
ROUND(SUM(IF(t.to_account_id = ca.id, t.amount, -t.amount)) * 60)
|
|
- SUM(ROUND(IF(t.to_account_id = ca.id, t.amount, -t.amount) * 60)) AS total_drift,
|
|
COUNT(t.id) AS total_tx_count,
|
|
SUM(IF(t.to_account_id = ca.id, t.amount, -t.amount)) AS total_cumulative_balance
|
|
FROM {$destinationDb}.accounts la
|
|
INNER JOIN {$sourceDb}.accounts ca ON la.cyclos_id = ca.id
|
|
INNER JOIN {$sourceDb}.transfers t ON t.from_account_id = ca.id OR t.to_account_id = ca.id
|
|
WHERE la.cyclos_id IS NOT NULL
|
|
GROUP BY la.id, ca.id
|
|
");
|
|
$totalDriftMap = [];
|
|
foreach ($totalDrifts as $td) {
|
|
$totalDriftMap[(int) $td->laravel_account_id] = [
|
|
'drift' => (int) $td->total_drift,
|
|
'txCount' => (int) $td->total_tx_count,
|
|
'balance' => (float) $td->total_cumulative_balance,
|
|
];
|
|
}
|
|
|
|
$correctionCount = 0;
|
|
$now = now()->format('Y-m-d H:i:s');
|
|
$currentYear = (int) now()->year;
|
|
$yearlyDriftSums = []; // track sum of per-year corrections per account
|
|
$currentYearTxCounts = []; // tx count per account for the current year
|
|
foreach ($corrections as $row) {
|
|
$diff = (int) $row->diff_min;
|
|
$txCount = (int) $row->tx_count;
|
|
$year = (int) $row->tx_year;
|
|
$accountId = (int) $row->laravel_account_id;
|
|
|
|
// Skip the current (final) year — its drift will be absorbed into
|
|
// the final correction below, timestamped at the actual migration datetime.
|
|
if ($year >= $currentYear) {
|
|
$currentYearTxCounts[$accountId] = $txCount;
|
|
continue;
|
|
}
|
|
|
|
$yearlyDriftSums[$accountId] = ($yearlyDriftSums[$accountId] ?? 0) + $diff;
|
|
|
|
$cyclosAccountId = (int) $row->cyclos_account_id;
|
|
$avg = $txCount > 0 ? round(abs($diff) / $txCount * 60, 1) : 0;
|
|
$endBalanceHours = number_format($cumulativeBalanceMap[$cyclosAccountId][$year] ?? 0.0, 2, ',', '');
|
|
$description = "Yearly transaction to correct rounding discrepancy between decimal values "
|
|
. "and time-based values.\n"
|
|
. "This account had {$txCount} transactions in {$year}, the average correction per "
|
|
. "transaction was {$avg} seconds. "
|
|
. "Excluding this correction, this account's balance at the end of {$year} was H {$endBalanceHours}.";
|
|
|
|
DB::table("{$destinationDb}.transactions")->insert([
|
|
'from_account_id' => $diff > 0 ? $debitAccountId : $accountId,
|
|
'to_account_id' => $diff > 0 ? $accountId : $debitAccountId,
|
|
'amount' => abs($diff),
|
|
'description' => $description,
|
|
'transaction_type_id' => 7,
|
|
'transaction_status_id' => 1,
|
|
'created_at' => "{$year}-12-31 23:59:59",
|
|
'updated_at' => "{$year}-12-31 23:59:59",
|
|
]);
|
|
$correctionCount++;
|
|
}
|
|
|
|
// Insert a final correction for any account where the sum of yearly
|
|
// corrections doesn't fully cover the total drift. This captures both
|
|
// cross-year rounding artifacts and the current (final) year's drift,
|
|
// and is always dated at the actual migration datetime.
|
|
foreach ($totalDriftMap as $accountId => $data) {
|
|
$yearlySum = $yearlyDriftSums[$accountId] ?? 0;
|
|
$residual = $data['drift'] - $yearlySum;
|
|
if ($residual === 0) {
|
|
continue;
|
|
}
|
|
$txCount = $currentYearTxCounts[$accountId] ?? 0;
|
|
$avg = $txCount > 0 ? round(abs($residual) / $txCount * 60, 1) : 0;
|
|
$endBalance = number_format($data['balance'], 2, ',', '');
|
|
$migrationDate = now()->format('F j, Y');
|
|
$finalDescription = "Final transaction to correct rounding discrepancy between decimal values "
|
|
. "and time-based values.\n"
|
|
. "This account had {$txCount} transactions in {$currentYear}, the average correction per "
|
|
. "transaction was {$avg} seconds. "
|
|
. "Excluding this correction, this account's balance at the migration date ({$migrationDate}) was H {$endBalance}.";
|
|
DB::table("{$destinationDb}.transactions")->insert([
|
|
'from_account_id' => $residual > 0 ? $debitAccountId : $accountId,
|
|
'to_account_id' => $residual > 0 ? $accountId : $debitAccountId,
|
|
'amount' => abs($residual),
|
|
'description' => $finalDescription,
|
|
'transaction_type_id' => 7,
|
|
'transaction_status_id' => 1,
|
|
'created_at' => $now,
|
|
'updated_at' => $now,
|
|
]);
|
|
$correctionCount++;
|
|
}
|
|
|
|
$this->info("Rounding corrections applied: {$correctionCount} year/account combinations adjusted.");
|
|
}
|
|
DB::commit();
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Rounding corrections failed: ' . $e->getMessage());
|
|
}
|
|
$this->info("");
|
|
|
|
|
|
|
|
|
|
|
|
// REGISTRATION OF PACKAGE MODELS
|
|
|
|
// Register laravel-love models
|
|
$exitCode = Artisan::call('love:register-reacters', ['--model' => 'App\Models\User'], new \Symfony\Component\Console\Output\ConsoleOutput());
|
|
// Optionally, check if the command was successful
|
|
if ($exitCode === 0) {
|
|
$this->info('Laravel-love Reacters registered: Users');
|
|
} else {
|
|
$this->error('Laravel-love Reacters registration failed: Users');
|
|
}
|
|
|
|
$exitCode = Artisan::call('love:register-reacters', ['--model' => 'App\Models\Organization'], new \Symfony\Component\Console\Output\ConsoleOutput());
|
|
// Optionally, check if the command was successful
|
|
if ($exitCode === 0) {
|
|
$this->info('Laravel-love Reacters registered: Organizations');
|
|
} else {
|
|
$this->error('Laravel-love Reacters registration failed: Organizations');
|
|
}
|
|
|
|
$exitCode = Artisan::call('love:register-reacters', ['--model' => 'App\Models\Bank'], new \Symfony\Component\Console\Output\ConsoleOutput());
|
|
// Optionally, check if the command was successful
|
|
if ($exitCode === 0) {
|
|
$this->info('Laravel-love Reacters registered: Banks');
|
|
} else {
|
|
$this->error('Laravel-love Reacters registration failed: Banks');
|
|
}
|
|
|
|
|
|
$exitCode = Artisan::call('love:register-reactants', ['--model' => 'App\Models\User'], new \Symfony\Component\Console\Output\ConsoleOutput());
|
|
// Optionally, check if the command was successful
|
|
if ($exitCode === 0) {
|
|
$this->info('Laravel-love Reactants registered: Users');
|
|
} else {
|
|
$this->error('Laravel-love Reactants registration failed.');
|
|
}
|
|
|
|
$exitCode = Artisan::call('love:register-reactants', ['--model' => 'App\Models\Organization'], new \Symfony\Component\Console\Output\ConsoleOutput());
|
|
// Optionally, check if the command was successful
|
|
if ($exitCode === 0) {
|
|
$this->info('Laravel-love Reactants registered: Organizations');
|
|
} else {
|
|
$this->error('Laravel-love Reactants registration failed: Organizations');
|
|
}
|
|
|
|
$exitCode = Artisan::call('love:register-reactants', ['--model' => 'App\Models\Bank'], new \Symfony\Component\Console\Output\ConsoleOutput());
|
|
// Optionally, check if the command was successful
|
|
if ($exitCode === 0) {
|
|
$this->info('Laravel-love Reactants registered: Banks');
|
|
} else {
|
|
$this->error('Laravel-love Reactants registration failed.');
|
|
}
|
|
|
|
// Add Love Reactions to each transaction.
|
|
DB::beginTransaction();
|
|
try {
|
|
// Add Love Reactions to each transaction.
|
|
Artisan::call('love:add-reactions-to-transactions');
|
|
$this->info(Artisan::output());
|
|
DB::commit();
|
|
$this->info('Love Reactions added to transactions successfully.');
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error('Failed to add Love Reactions to transactions: ' . $e->getMessage());
|
|
}
|
|
|
|
$this->warn('Do not run this migration again without refreshing the database and deleting all files in storage/app/public/profile-photo\'s');
|
|
$this->warn('You can do this with the command: bash seed.sh');
|
|
$this->info('');
|
|
}
|
|
|
|
private function detectImageExtension(string $blob): string
|
|
{
|
|
$header = substr($blob, 0, 4);
|
|
if (str_starts_with($header, "\xFF\xD8\xFF")) return 'jpg';
|
|
if (str_starts_with($header, "\x89PNG")) return 'png';
|
|
if (str_starts_with($header, 'GIF8')) return 'gif';
|
|
if (str_starts_with($header, 'RIFF')) return 'webp';
|
|
if (str_starts_with($header, "\x00\x00\x01\x00")) return 'ico';
|
|
return 'jpg'; // fallback
|
|
}
|
|
}
|