494 lines
20 KiB
PHP
494 lines
20 KiB
PHP
<?php
|
|
|
|
namespace App\Console\Commands;
|
|
|
|
use App\Models\Bank;
|
|
use App\Models\Locations\Location;
|
|
use App\Models\Organization;
|
|
use App\Models\User;
|
|
use Illuminate\Console\Command;
|
|
use Illuminate\Support\Facades\Artisan;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
class MigrateCyclosProfilesCommand extends Command
|
|
{
|
|
/**
|
|
* The name and signature of the console command.
|
|
*
|
|
* @var string
|
|
*/
|
|
protected $signature = 'migrate:cyclos-profiles {source_db? : Name of the source Cyclos database (skips prompt if provided)}';
|
|
protected $description = 'Migrates the Cyclos profile contents from the old Cyclos database to the new Laravel database';
|
|
|
|
public function handle()
|
|
{
|
|
// Use argument if provided, otherwise fall back to cache (set by migrate:cyclos during db:seed)
|
|
$sourceDb = $this->argument('source_db') ?: cache()->get('cyclos_migration_source_db');
|
|
|
|
if (empty($sourceDb)) {
|
|
// If not in cache, ask for it
|
|
$this->info('The source Cyclos database should be imported into MySQL and accessible from this application.');
|
|
$this->info('Hint: Place the database dump in the app root and import with: mysql -u root -p < cyclos_dump.sql');
|
|
$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;
|
|
}
|
|
} else {
|
|
$this->info("Using source database from previous step: {$sourceDb}");
|
|
}
|
|
|
|
$destinationDb = env('DB_DATABASE');
|
|
|
|
// Migrate phone field
|
|
$tables = ['users', 'organizations', 'banks'];
|
|
|
|
foreach ($tables as $tableName) {
|
|
DB::beginTransaction();
|
|
try {
|
|
$affectedRows = DB::affectingStatement("
|
|
UPDATE `{$destinationDb}`.`{$tableName}` dest
|
|
JOIN (
|
|
SELECT
|
|
c.member_id,
|
|
LEFT(c.string_value, 20) AS phone -- Truncate to 20 characters
|
|
FROM `{$sourceDb}`.`custom_field_values` c
|
|
WHERE c.field_id = 7
|
|
) src ON dest.cyclos_id = src.member_id
|
|
SET dest.phone = src.phone
|
|
");
|
|
DB::commit();
|
|
$this->info(ucfirst($tableName) . " phone field updated for $affectedRows records");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error(ucfirst($tableName) . " phone field migration failed: " . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
// Migrate locations
|
|
|
|
$countryCodeMap = [
|
|
860 => 2, // BE
|
|
861 => 7, // PT
|
|
862 => 1, // NL
|
|
863 => 10, // country not set / other country → "Location not specified"
|
|
];
|
|
$cityCodeMap = [
|
|
864 => 188, // Amsterdam
|
|
865 => 200, // Haarlem
|
|
866 => 316, // Leiden
|
|
867 => 305, // The Hague
|
|
868 => 300, // Delft
|
|
869 => 331, // Rotterdam
|
|
870 => 272, // Utrecht
|
|
881 => 345, // Brussels
|
|
];
|
|
|
|
$updatedRecordsCount = 0;
|
|
|
|
DB::beginTransaction();
|
|
try {
|
|
// Wrap the migration calls in a function that returns the count of updated records
|
|
$updatedRecordsCount += $this->migrateLocationData('User', $destinationDb, $sourceDb, $countryCodeMap, $cityCodeMap);
|
|
$updatedRecordsCount += $this->migrateLocationData('Organization', $destinationDb, $sourceDb, $countryCodeMap, $cityCodeMap);
|
|
$updatedRecordsCount += $this->migrateLocationData('Bank', $destinationDb, $sourceDb, $countryCodeMap, $cityCodeMap);
|
|
|
|
DB::commit();
|
|
// Output the total number of records updated
|
|
$this->info("Location fields migration updated for: " . $updatedRecordsCount);
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error("Location fields migration failed: " . $e->getMessage());
|
|
}
|
|
|
|
|
|
|
|
// Migrate user about field
|
|
$tables = ['users'];
|
|
foreach ($tables as $tableName) {
|
|
DB::beginTransaction();
|
|
try {
|
|
$affectedRows = DB::affectingStatement("
|
|
UPDATE `{$destinationDb}`.`{$tableName}` dest
|
|
JOIN (
|
|
SELECT
|
|
c.member_id,
|
|
c.string_value AS about
|
|
FROM `{$sourceDb}`.`custom_field_values` c
|
|
WHERE c.field_id = 17
|
|
) src ON dest.cyclos_id = src.member_id
|
|
SET dest.about = src.about
|
|
");
|
|
DB::commit();
|
|
$this->info(ucfirst($tableName) . " about field updated for $affectedRows records");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error(ucfirst($tableName) . " about field migration failed: " . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
|
|
// Migrate motivation field
|
|
$tables = ['users', 'organizations', 'banks'];
|
|
foreach ($tables as $tableName) {
|
|
DB::beginTransaction();
|
|
try {
|
|
$affectedRows = DB::affectingStatement("
|
|
UPDATE `{$destinationDb}`.`{$tableName}` dest
|
|
JOIN (
|
|
SELECT
|
|
c.member_id,
|
|
c.string_value AS motivation
|
|
FROM `{$sourceDb}`.`custom_field_values` c
|
|
WHERE c.field_id = 35
|
|
) src ON dest.cyclos_id = src.member_id
|
|
SET dest.motivation = src.motivation
|
|
");
|
|
DB::commit();
|
|
$this->info(ucfirst($tableName) . " motivation field updated for $affectedRows records");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error(ucfirst($tableName) . " motivation field migration failed: " . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
// Migrate website field
|
|
$tables = ['users', 'organizations', 'banks'];
|
|
foreach ($tables as $tableName) {
|
|
DB::beginTransaction();
|
|
try {
|
|
$affectedRows = DB::affectingStatement("
|
|
UPDATE `{$destinationDb}`.`{$tableName}` dest
|
|
JOIN (
|
|
SELECT
|
|
c.member_id,
|
|
c.string_value AS website
|
|
FROM `{$sourceDb}`.`custom_field_values` c
|
|
WHERE c.field_id = 10
|
|
) src ON dest.cyclos_id = src.member_id
|
|
SET dest.website = src.website
|
|
");
|
|
DB::commit();
|
|
$this->info(ucfirst($tableName) . " website field updated for $affectedRows records");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error(ucfirst($tableName) . " website field migration failed: " . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
|
|
|
|
// Migrate birthday field
|
|
$tables = ['users'];
|
|
foreach ($tables as $tableName) {
|
|
DB::beginTransaction();
|
|
try {
|
|
$affectedRows = DB::affectingStatement("
|
|
UPDATE `{$destinationDb}`.`{$tableName}` dest
|
|
JOIN (
|
|
SELECT
|
|
c.member_id,
|
|
STR_TO_DATE(REPLACE(c.string_value, '/', '-'), '%d-%m-%Y') AS birthday
|
|
FROM `{$sourceDb}`.`custom_field_values` c
|
|
WHERE c.field_id = 1
|
|
) src ON dest.cyclos_id = src.member_id
|
|
SET dest.date_of_birth = src.birthday
|
|
");
|
|
DB::commit();
|
|
$this->info(ucfirst($tableName) . " birthday field updated for $affectedRows records");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error(ucfirst($tableName) . " birthday field migration failed: " . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
// Migrate General Newsletter field to message_settings table
|
|
DB::beginTransaction();
|
|
try {
|
|
// Get all newsletter preferences from Cyclos
|
|
$newsletterPrefs = DB::table("{$sourceDb}.custom_field_values")
|
|
->where('field_id', 28)
|
|
->get(['member_id', 'possible_value_id']);
|
|
|
|
$totalUpdated = 0;
|
|
$tables = [
|
|
'users' => User::class,
|
|
'organizations' => Organization::class,
|
|
'banks' => Bank::class
|
|
];
|
|
|
|
foreach ($tables as $tableName => $modelClass) {
|
|
foreach ($newsletterPrefs as $pref) {
|
|
$entity = DB::table($tableName)
|
|
->where('cyclos_id', $pref->member_id)
|
|
->first();
|
|
|
|
if ($entity) {
|
|
$model = $modelClass::find($entity->id);
|
|
if ($model) {
|
|
// Convert: 790 (No) → 0, 791 (Yes) → 1, null → 1
|
|
$value = $pref->possible_value_id == 790 ? 0 : 1;
|
|
|
|
// Update or create message settings
|
|
$model->message_settings()->updateOrCreate(
|
|
['message_settingable_id' => $model->id, 'message_settingable_type' => $modelClass],
|
|
['general_newsletter' => $value]
|
|
);
|
|
$totalUpdated++;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
DB::commit();
|
|
$this->info("General newsletter field migrated to message_settings for {$totalUpdated} records");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error("General newsletter field migration failed: " . $e->getMessage());
|
|
}
|
|
|
|
// Migrate Local Newsletter field to message_settings table
|
|
DB::beginTransaction();
|
|
try {
|
|
// Get all newsletter preferences from Cyclos
|
|
$newsletterPrefs = DB::table("{$sourceDb}.custom_field_values")
|
|
->where('field_id', 29)
|
|
->get(['member_id', 'possible_value_id']);
|
|
|
|
$totalUpdated = 0;
|
|
$tables = [
|
|
'users' => User::class,
|
|
'organizations' => Organization::class,
|
|
'banks' => Bank::class
|
|
];
|
|
|
|
foreach ($tables as $tableName => $modelClass) {
|
|
foreach ($newsletterPrefs as $pref) {
|
|
$entity = DB::table($tableName)
|
|
->where('cyclos_id', $pref->member_id)
|
|
->first();
|
|
|
|
if ($entity) {
|
|
$model = $modelClass::find($entity->id);
|
|
if ($model) {
|
|
// Convert: 792 (No) → 0, 793 (Yes) → 1, null → 1
|
|
$value = $pref->possible_value_id == 792 ? 0 : 1;
|
|
|
|
// Update or create message settings
|
|
$model->message_settings()->updateOrCreate(
|
|
['message_settingable_id' => $model->id, 'message_settingable_type' => $modelClass],
|
|
['local_newsletter' => $value]
|
|
);
|
|
$totalUpdated++;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
DB::commit();
|
|
$this->info("Local newsletter field migrated to message_settings for {$totalUpdated} records");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error("Local newsletter field migration failed: " . $e->getMessage());
|
|
}
|
|
|
|
|
|
// Migrate Cyclos skills from refined database
|
|
$tables = ['users', 'organizations'];
|
|
foreach ($tables as $tableName) {
|
|
DB::beginTransaction();
|
|
try {
|
|
$affectedRows = DB::affectingStatement("
|
|
UPDATE `{$destinationDb}`.`{$tableName}` dest
|
|
JOIN (
|
|
SELECT
|
|
c.member_id,
|
|
CASE
|
|
WHEN CHAR_LENGTH(c.string_value) > 495
|
|
THEN CONCAT(LEFT(c.string_value, 495), ' ...')
|
|
ELSE c.string_value
|
|
END AS cyclos_skills
|
|
FROM `{$sourceDb}`.`custom_field_values` c
|
|
WHERE c.field_id = 13
|
|
) src ON dest.cyclos_id = src.member_id
|
|
SET dest.cyclos_skills = src.cyclos_skills
|
|
");
|
|
DB::commit();
|
|
$this->info(ucfirst($tableName) . " skills field updated for $affectedRows records");
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
$this->error(ucfirst($tableName) . " skills field migration failed: " . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
// Strip all HTML tags from imported tables
|
|
foreach ($tables as $tableName) {
|
|
$records = DB::table($tableName)->select('id', 'cyclos_skills')->whereNotNull('cyclos_skills')->get();
|
|
foreach ($records as $record) {
|
|
$cleaned = strip_tags($record->cyclos_skills);
|
|
if ($cleaned !== $record->cyclos_skills) {
|
|
DB::table($tableName)->where('id', $record->id)->update(['cyclos_skills' => $cleaned]);
|
|
}
|
|
}
|
|
$records = DB::table($tableName)->select('id', 'about')->whereNotNull('about')->get();
|
|
foreach ($records as $record) {
|
|
$cleaned = strip_tags($record->about);
|
|
if ($cleaned !== $record->about) {
|
|
DB::table($tableName)->where('id', $record->id)->update(['about' => $cleaned]);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
// Set suspicious robot members to inactive
|
|
|
|
// 1755
|
|
// 1768
|
|
// 1776
|
|
// 1777
|
|
|
|
// Check if user.about is null, if true, copy skill tags where length > 50 to user.about
|
|
// if user.about <> null, copy skill tags where length > 50 to about_short or update this field
|
|
|
|
protected function migrateLocationData($modelClass, $destinationDb, $sourceDb, $countryCodeMap, $cityCodeMap)
|
|
{
|
|
$fullyQualifiedModelClass = "App\\Models\\" . $modelClass;
|
|
|
|
$cyclos_countries = DB::table("{$sourceDb}.custom_field_values")
|
|
->where('field_id', 36)
|
|
->get(['possible_value_id', 'member_id']);
|
|
$cyclos_cities = DB::table("{$sourceDb}.custom_field_values")
|
|
->where('field_id', 38)
|
|
->get(['possible_value_id', 'member_id']);
|
|
|
|
$remappedCountries = $cyclos_countries->mapWithKeys(function ($item) use ($countryCodeMap) {
|
|
return [$item->member_id => $countryCodeMap[$item->possible_value_id] ?? null];
|
|
});
|
|
$remappedCities = $cyclos_cities->mapWithKeys(function ($item) use ($cityCodeMap) {
|
|
return [$item->member_id => $cityCodeMap[$item->possible_value_id] ?? null];
|
|
});
|
|
|
|
$recordUpdateCount = 0;
|
|
$syncedDataCount = 0;
|
|
|
|
foreach ($remappedCountries as $memberId => $countryId) {
|
|
$cityId = $remappedCities[$memberId] ?? null;
|
|
if ($countryId !== null || $cityId !== null) {
|
|
$entity = DB::table("{$destinationDb}." . strtolower($modelClass) . "s")
|
|
->where('cyclos_id', $memberId)
|
|
->first();
|
|
|
|
if ($entity) {
|
|
$entityModel = $fullyQualifiedModelClass::find($entity->id);
|
|
if ($entityModel) {
|
|
$location = new Location();
|
|
$location->name = 'Default location';
|
|
$location->country_id = $countryId;
|
|
$location->city_id = $cityId;
|
|
$entityModel->locations()->save($location);
|
|
$recordUpdateCount++;
|
|
|
|
// Sync all missing location data (divisions, etc.)
|
|
try {
|
|
$synced = $location->syncAllLocationData();
|
|
if (!empty($synced)) {
|
|
$syncedDataCount++;
|
|
$this->info(" → Synced data for {$modelClass} ID {$entity->id}: " . implode(', ', $synced));
|
|
}
|
|
} catch (\Exception $e) {
|
|
$this->warn(" → Failed to sync location data for {$modelClass} ID {$entity->id}: " . $e->getMessage());
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
$this->info("{$modelClass}: {$recordUpdateCount} locations created, {$syncedDataCount} had additional data synced");
|
|
return $recordUpdateCount;
|
|
|
|
}
|
|
|
|
|
|
|
|
/**
|
|
* Tinker script to clean 'about' field containing only a single double quote
|
|
*
|
|
* Run this in Laravel Tinker:
|
|
* php artisan tinker
|
|
* Then paste this code
|
|
*/
|
|
protected function cleanAboutField()
|
|
{
|
|
echo "Starting cleanup of 'about' fields containing only double quotes...\n\n";
|
|
$models = [
|
|
'App\Models\User' => 'Users',
|
|
'App\Models\Organization' => 'Organizations',
|
|
'App\Models\Bank' => 'Banks',
|
|
'App\Models\Admin' => 'Admins'
|
|
];
|
|
$totalUpdated = 0;
|
|
foreach ($models as $modelClass => $tableName) {
|
|
echo "Processing {$tableName}...\n";
|
|
|
|
// Check if the model class exists
|
|
if (!class_exists($modelClass)) {
|
|
echo " - Model {$modelClass} not found, skipping\n";
|
|
continue;
|
|
}
|
|
|
|
try {
|
|
// Find records where about field contains only a double quote
|
|
$records = $modelClass::where('about', '"')->get();
|
|
|
|
echo " - Found {$records->count()} records with about = '\"'\n";
|
|
|
|
if ($records->count() > 0) {
|
|
// Update records to set about to null
|
|
$updated = $modelClass::where('about', '"')->update(['about' => null]);
|
|
|
|
echo " - Updated {$updated} records\n";
|
|
$totalUpdated += $updated;
|
|
}
|
|
} catch (\Exception $e) {
|
|
echo " - Error processing {$tableName}: " . $e->getMessage() . "\n";
|
|
}
|
|
|
|
echo "\n";
|
|
}
|
|
echo "Cleanup completed!\n";
|
|
echo "Total records updated: {$totalUpdated}\n";
|
|
echo "\nTo verify the cleanup, you can run:\n";
|
|
foreach ($models as $modelClass => $tableName) {
|
|
if (class_exists($modelClass)) {
|
|
echo "{$modelClass}::where('about', '\"')->count(); // Should return 0\n";
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
}
|