File "DatabaseBackupController.php"
Full Path: /home/trinadezambia/public_html/admin_panel/app/Http/Controllers/DatabaseBackupController.php
File size: 39.99 KB
MIME-type: text/x-php
Charset: utf-8
<?php
namespace App\Http\Controllers;
use App\Models\CertificateTemplate;
use App\Models\ClassGroup;
use App\Models\ExpenseCategory;
use App\Models\Faq;
use App\Models\FeesType;
use App\Models\File as ModelsFile;
use App\Models\FormField;
use App\Models\Grade;
use App\Models\Holiday;
use App\Models\Mediums;
use App\Models\PaymentTransaction;
use App\Models\Role;
use App\Models\School;
use App\Models\SchoolSetting;
use App\Models\Section;
use App\Models\Semester;
use App\Models\SessionYear;
use App\Models\Shift;
use App\Models\Slider;
use App\Models\Staff;
use App\Models\Stream;
use App\Models\Students;
use App\Models\Subscription;
use App\Models\SystemSetting;
use App\Models\User;
use App\Repositories\DatabaseBackup\DatabaseBackupInterface;
use App\Services\BootstrapTableService;
use App\Services\ResponseService;
use App\Services\SubscriptionService;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\Auth as FacadesAuth;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Str;
use Throwable;
use ZipArchive;
class DatabaseBackupController extends Controller
{
//
private DatabaseBackupInterface $databaseBackup;
private SubscriptionService $subscriptionService;
public function __construct(DatabaseBackupInterface $databaseBackup, SubscriptionService $subscriptionService)
{
$this->databaseBackup = $databaseBackup;
$this->subscriptionService = $subscriptionService;
}
public function index()
{
if ((Auth::user()->hasRole(['Super Admin', 'School Admin']) || Auth::user()->hasPermissionTo('database-backup'))) {
$schoolId = Auth::user()->hasRole('School Admin') ? Auth::user()->school_id : Auth::user()->id;
return view('database-backup.index', compact('schoolId'));
} else {
return redirect()->route('home')->with('error', "You Don't have enough permissions");
}
}
public function store()
{
ResponseService::noPermissionThenSendJson('database-backup');
if(env('DEMO_MODE') == true) {
ResponseService::errorResponse('Database backup is not allowed in demo mode');
}
$user_Id = Auth::user()->hasRole('School Admin') ? Auth::user()->school_id : Auth::user()->id;
$current_version = SystemSetting::where('name', 'system_version')->first()['data'];
if (Auth::user()->hasRole('School Admin')) {
// Database backup
$allTables = DB::select('SHOW TABLES');
$tableNames = array_map('current', $allTables);
$expectedTables = ['addons', 'attachments', 'categories', 'chats', 'failed_jobs', 'features', 'feature_sections', 'feature_section_lists', 'guidances', 'languages', 'messages', 'migrations', 'packages', 'package_features', 'password_resets', 'personal_access_tokens', 'staff_support_schools', 'system_settings', 'user_status_for_next_cycles', 'database_backups'];
$subscription = $this->subscriptionService->active_subscription(Auth::user()->school_id);
$allTables = array_diff($tableNames, $expectedTables);
$tableNames = array_values($allTables);
$staff_ids = Staff::whereHas('user', function ($q) use ($user_Id) {
$q->where('school_id', $user_Id);
})->pluck('id')->toArray();
$students = Students::where('school_id', $user_Id)->withTrashed()->get();
$student_ids = $students->pluck('user_id')->toArray();
$guardian_ids = $students->pluck('guardian_id')->toArray();
$roles_id = Role::where('school_id', Auth::user()->school_id)->pluck('id')->toArray();
$guardian_ids = array_values(array_unique($guardian_ids));
// Tables requiring additional conditions
// dd($subscription->school->admin_id);
$tablesWithAdditionalConditions = [
'users' => function ($query) use ($user_Id, $guardian_ids, $subscription) {
$query->where(function ($q) use ($user_Id, $guardian_ids) {
$q->where('school_id', $user_Id)
->orWhereIn('id', $guardian_ids);
});
// ->where(function ($q) use ($subscription) {
// $q->WhereNot('id', $subscription->school->admin_id)
// ->where('id', Auth::user()->id);
// });
},
'fees_advance' => function ($query) use ($guardian_ids) {
$query->whereIn('parent_id', $guardian_ids);
},
'staffs' => function ($query) use ($staff_ids) {
$query->whereIn('id', $staff_ids);
},
'staff_salaries' => function ($query) use ($staff_ids) {
$query->whereIn('staff_id', $staff_ids);
},
'model_has_roles' => function ($query) use ($roles_id) {
$query->whereIn('role_id', $roles_id);
},
'role_has_permissions' => function ($query) use ($roles_id) {
$query->whereIn('role_id', $roles_id);
},
'subscription_features' => function ($query) use ($subscription) {
$query->where('subscription_id', $subscription->id);
},
// Add more specific tables here
];
$backupData = '';
// dd($tableNames);
foreach ($tableNames as $table) {
// Get table creation SQL
// $createTableSQL = DB::select("SHOW CREATE TABLE `$table`");
// $backupData .= $createTableSQL[0]->{'Create Table'} . ";\n\n";
// ==========================================================
$createTableSQL = DB::select("SHOW CREATE TABLE `$table`");
$createTable = $createTableSQL[0]->{'Create Table'};
// Replace 'CREATE TABLE' with 'CREATE TABLE IF NOT EXISTS'
$createTableWithIfNotExists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $createTable);
// Add the modified SQL to the backup
$backupData .= $createTableWithIfNotExists . ";\n\n";
// ==========================================================
// Create query builder
$query = DB::table($table);
// Check if 'school_id' column exists
$hasSchoolIdColumn = Schema::hasColumn($table, 'school_id');
// Apply conditions
if ($hasSchoolIdColumn) {
if (!array_key_exists($table, $tablesWithAdditionalConditions)) {
$query->where('school_id', $user_Id);
} else {
// Apply specific conditions
$tablesWithAdditionalConditions[$table]($query);
}
} else {
// Apply specific conditions if necessary
if (array_key_exists($table, $tablesWithAdditionalConditions)) {
$tablesWithAdditionalConditions[$table]($query);
}
}
// Fetch rows and build SQL for inserts
$rows = $query->get();
foreach ($rows as $row) {
// Convert row to array
$rowArray = (array) $row;
// For class_subjects table, skip virtual_semester_id
if ($table === 'class_subjects') {
unset($rowArray['virtual_semester_id']);
}
$values = array_map(function ($value) use ($table) {
if (is_null($value)) {
return 'NULL';
}
// Special handling for settings tables that contain HTML content or email templates
if (in_array($table, ['school_settings', 'system_settings'])) {
// Check if it's an email template or contains HTML
if (
strpos($value, '<') !== false || strpos($value, '>') !== false ||
strpos($value, '{') !== false || strpos($value, '}') !== false
) {
// Properly escape HTML content and template variables
$value = str_replace('\\', '\\\\', $value); // Escape backslashes first
$value = str_replace("'", "\\'", $value); // Escape single quotes
$value = str_replace('"', '\\"', $value); // Escape double quotes
return "'" . $value . "'";
}
}
// For all other tables/content
$value = str_replace("'", "''", $value); // Escape single quotes
$value = str_replace("\\", "\\\\", $value); // Escape backslashes
return "'" . $value . "'";
}, $rowArray);
// Get column names for INSERT statement
if ($table === 'class_subjects') {
$columns = array_keys($rowArray);
$backupData .= "INSERT INTO `$table` (`" . implode('`, `', $columns) . "`) VALUES (" . implode(', ', $values) . ");\n";
} else {
$backupData .= "INSERT INTO `$table` VALUES (" . implode(', ', $values) . ");\n";
}
}
$backupData .= "\n";
}
// Define the path
$path = 'public/database-backup/schools/' . $user_Id; // Change to store in the 'sql' directory
// $path = storage_path('public/database-backup/schools/' . $user_Id);
$timestamp = Carbon::now()->format('Y-m-d');
$file_name = "database_backup_{$user_Id}_{$timestamp}";
$filePath = $path . "/database_backup_{$user_Id}_{$timestamp}.sql";
// check storage public in id folder exist
if (!Storage::exists('public/' . $user_Id)) {
Storage::makeDirectory('public/' . $user_Id);
}
// Create the directory if it doesn't exist
if (!Storage::exists($path)) {
Storage::makeDirectory($path);
}
// Save the backup to the file
Storage::put($filePath, $backupData);
// End database backup
// ==========================================================
$zip = new ZipArchive;
$backup_type = 'schools';
$public_folder_path = storage_path('app/public/' . $user_Id);
$backup_folder_path = storage_path('app/public/database-backup/' . $backup_type . '/' . $user_Id);
$zip_file_path = $backup_folder_path . '/' . $file_name . '-(V-' . $current_version . ').zip';
// dd(File::isDirectory($backup_folder_path));
// $mainFolder = Auth::user()->id; // Main folder name
// $mainFolderPath = storage_path('app/public/database-backup/super-admin/' . $mainFolder); // Path to the main folder
if ($zip->open($zip_file_path, ZipArchive::CREATE) === TRUE) {
if (File::isDirectory($backup_folder_path)) {
// Add main folder to the zip
$zip_media_folder = 'media';
// create folder for media files
$zip->addEmptyDir($zip_media_folder);
// create database-backup folder and add .sql file
$relativeSqlFile = 'database-backup/database_backup_' . $user_Id . '_' . $timestamp . '.sql';
$zip->addFile(storage_path('app/' . $filePath), $relativeSqlFile);
// dd($relativeSqlFile);
// // Get all subdirectories inside the main folder
$subfolders = File::directories($public_folder_path);
foreach ($subfolders as $subfolderPath) {
// Get the relative path of the subfolder
$relativeSubfolder = $zip_media_folder . '/' . str_replace(storage_path('app/public/'), '', $subfolderPath);
// Add the subfolder to the zip
$zip->addEmptyDir($relativeSubfolder);
// Get all files inside the current subfolder
$files = File::files($subfolderPath);
foreach ($files as $file) {
// Get the relative path of the file
$relativeFile = $zip_media_folder . '/' . str_replace(storage_path('app/public/'), '', $file);
// Add the file to the zip with its relative path
$zip->addFile($file, $relativeFile);
}
}
// // Add files in the main folder
// $mainFolderFiles = File::files($mainFolderPath);
// foreach ($mainFolderFiles as $file) {
// // Get the relative path of the file
// $relativeFile = str_replace(storage_path('app/public/'), '', $file);
// // Add the file to the zip
// $zip->addFile($file, $relativeFile);
// }
// Close the archive
$zip->close();
// Create the directory if it doesn't exist
if (!Storage::exists($path)) {
Storage::makeDirectory($path);
}
}
// Delete the .sql file after zipping
Storage::delete($filePath);
$data = [
'name' => $file_name
];
// $this->databaseBackup->create($data);
$file_name = "database_backup_{$user_Id}_" . Carbon::now()->format('Y-m-d') . '-(V-' . $current_version . ').zip';
// dd($file_name);
$zipFileDownload = 'database-backup/schools/' . $user_Id . '/' . $file_name;
$download_url = url(Storage::url($zipFileDownload));
ResponseService::successResponse('Backup completed successfully', $download_url);
} else {
ResponseService::logErrorResponse("DatabaseBackup Controller -> Store Method");
ResponseService::errorResponse();
}
} else if (Auth::user()->hasRole('Super Admin')) {
// Set default connection for Super Admin
DB::setDefaultConnection('mysql');
// Get all tables
$allTables = DB::select('SHOW TABLES');
$tableNames = array_map('current', $allTables);
$backupData = '';
$expectedTables = ['addons', 'failed_jobs', 'features', 'feature_sections', 'feature_section_lists', 'guidances', 'languages', 'migrations', 'packages', 'package_features', 'password_resets', 'personal_access_tokens', 'staff_support_schools', 'system_settings'];
$allTables = array_diff($tableNames, $expectedTables);
$tableNames = array_values($allTables);
// Loop through each table and generate backup data
foreach ($tableNames as $table) {
$createTableSQL = DB::select("SHOW CREATE TABLE `$table`");
$createTable = $createTableSQL[0]->{'Create Table'};
$createTableWithIfNotExists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $createTable);
$backupData .= $createTableWithIfNotExists . ";\n\n";
$query = DB::table($table);
$rows = $query->get();
foreach ($rows as $row) {
$values = array_map(function ($value) {
return is_null($value) ? 'NULL' : "'" . addslashes($value) . "'";
}, (array) $row);
$backupData .= "INSERT INTO `$table` VALUES (" . implode(', ', $values) . ");\n";
}
$backupData .= "\n";
}
// Define the path
$path = 'public/database-backup/super-admin/' . $user_Id; // Change to store in the 'sql' directory
$timestamp = Carbon::now()->format('Y-m-d');
$file_name = "database_backup_{$user_Id}_{$timestamp}";
$filePath = $path . "/database_backup_{$user_Id}_{$timestamp}.sql";
// check storage public in id folder exist
if (!Storage::exists('public/' . $user_Id)) {
Storage::makeDirectory('public/' . $user_Id);
}
// Create the directory if it doesn't exist
if (!Storage::exists($path)) {
Storage::makeDirectory($path);
}
// Save the backup to the file
Storage::put($filePath, $backupData);
// End database backup
// ==========================================================
$zip = new ZipArchive;
$backup_type = 'super-admin';
$public_folder_path = storage_path('app/public/' . $user_Id);
$backup_folder_path = storage_path('app/public/database-backup/' . $backup_type . '/' . $user_Id);
$zip_file_path = $backup_folder_path . '/' . $file_name . '-(V-' . $current_version . ').zip';
// dd(File::isDirectory($backup_folder_path));
// $mainFolder = Auth::user()->id; // Main folder name
// $mainFolderPath = storage_path('app/public/database-backup/super-admin/' . $mainFolder); // Path to the main folder
if ($zip->open($zip_file_path, ZipArchive::CREATE) === TRUE) {
if (File::isDirectory($backup_folder_path)) {
// Add main folder to the zip
$zip_media_folder = 'media';
// create folder for media files
$zip->addEmptyDir($zip_media_folder);
// create database-backup folder and add .sql file
$relativeSqlFile = 'database-backup/database_backup_' . $user_Id . '_' . $timestamp . '.sql';
$zip->addFile(storage_path('app/' . $filePath), $relativeSqlFile);
// dd($relativeSqlFile);
// // Get all subdirectories inside the main folder
$subfolders = File::directories($public_folder_path);
foreach ($subfolders as $subfolderPath) {
// Get the relative path of the subfolder
$relativeSubfolder = $zip_media_folder . '/' . str_replace(storage_path('app/public/'), '', $subfolderPath);
// Add the subfolder to the zip
$zip->addEmptyDir($relativeSubfolder);
// Get all files inside the current subfolder
$files = File::files($subfolderPath);
foreach ($files as $file) {
// Get the relative path of the file
$relativeFile = $zip_media_folder . '/' . str_replace(storage_path('app/public/'), '', $file);
// Add the file to the zip with its relative path
$zip->addFile($file, $relativeFile);
}
}
// // Add files in the main folder
// $mainFolderFiles = File::files($mainFolderPath);
// foreach ($mainFolderFiles as $file) {
// // Get the relative path of the file
// $relativeFile = str_replace(storage_path('app/public/'), '', $file);
// // Add the file to the zip
// $zip->addFile($file, $relativeFile);
// }
// Close the archive
$zip->close();
// Create the directory if it doesn't exist
if (!Storage::exists($path)) {
Storage::makeDirectory($path);
}
}
// Delete the .sql file after zipping
Storage::delete($filePath);
$data = [
'name' => $file_name
];
// $this->databaseBackup->create($data);
$file_name = "database_backup_{$user_Id}_" . Carbon::now()->format('Y-m-d') . '-(V-' . $current_version . ').zip';
$zipFileDownload = 'database-backup/super-admin/' . $user_Id . '/' . $file_name;
$download_url = url(Storage::url($zipFileDownload));
ResponseService::successResponse('Backup completed successfully', $download_url);
} else {
ResponseService::logErrorResponse("DatabaseBackup Controller -> Store Method");
ResponseService::errorResponse();
}
}
}
public function show()
{
ResponseService::noPermissionThenRedirect('database-backup');
if(env('DEMO_MODE') == true) {
ResponseService::errorResponse('Database backup is not allowed in demo mode');
}
$offset = request('offset', 0);
$limit = request('limit', 10);
$sort = request('sort', 'id');
$order = request('order', 'DESC');
$search = request('search');
$sql = $this->databaseBackup->builder()
->where(function ($query) use ($search) {
$query->when($search, function ($query) use ($search) {
$query->where(function ($query) use ($search) {
$query->where('id', 'LIKE', "%$search%")->orwhere('title', 'LIKE', "%$search%")->orwhere('description', 'LIKE', "%$search%")->orwhere('date', 'LIKE', "%$search%");
});
});
});
$total = $sql->count();
if ($offset >= $total && $total > 0) {
$lastPage = floor(($total - 1) / $limit) * $limit; // calculate last page offset
$offset = $lastPage;
}
$sql->orderBy($sort, $order)->skip($offset)->take($limit);
$res = $sql->get();
$bulkData = array();
$bulkData['total'] = $total;
$rows = array();
$no = 1;
foreach ($res as $row) {
$operate = BootstrapTableService::button('fa fa-refresh', '#', ['restore-database', 'btn-gradient-info'], ['title' => trans("restore"), 'data-id' => $row->id]);
$operate .= BootstrapTableService::deleteButton(url('database-backup', $row->id));
$tempRow = $row->toArray();
$tempRow['no'] = $no++;
$tempRow['operate'] = $operate;
$rows[] = $tempRow;
}
$bulkData['rows'] = $rows;
return response()->json($bulkData);
}
public function destroy($id)
{
ResponseService::noPermissionThenSendJson('database-backup');
if(env('DEMO_MODE') == true) {
ResponseService::errorResponse('Database backup is not allowed in demo mode');
}
try {
$databaseBackup = $this->databaseBackup->findById($id);
$sql_file = 'database-backup/' . Auth::user()->school_id . '/' . $databaseBackup->name . '.sql';
$zip_file = 'database-backup/' . Auth::user()->school_id . '/' . $databaseBackup->name . '.zip';
if (Storage::disk('public')->exists($sql_file)) {
Storage::disk('public')->delete($sql_file);
}
if (Storage::disk('public')->exists($zip_file)) {
Storage::disk('public')->delete($zip_file);
}
$this->databaseBackup->deleteById($id);
ResponseService::successResponse('Data Deleted Successfully');
} catch (Throwable $e) {
ResponseService::logErrorResponse($e, "DatabaseBackup Controller -> Destroy Method");
ResponseService::errorResponse();
}
}
public function restore(Request $request)
{
if(env('DEMO_MODE') == true) {
ResponseService::errorResponse('Database backup is not allowed in demo mode');
}
$validator = Validator::make($request->all(), [
'zip_file' => 'required|mimes:zip',
]);
if ($validator->fails()) {
ResponseService::errorResponse($validator->errors()->first());
}
try {
$authId = Auth::user()->hasRole('School Admin') ? Auth::user()->school_id : Auth::user()->id;
if (Auth::user()->hasRole('School Admin')) {
// File UnZip
$zip = new ZipArchive;
$backup_type = 'schools';
$public_folder_path = storage_path('app/public/' . $authId);
$backup_folder_path = storage_path('app/public/database-backup/' . $backup_type . '/' . $authId);
// Ensure the school ID folder exists
if (!file_exists($public_folder_path)) {
File::makeDirectory($public_folder_path, 0755, true);
}
// Old Data Delete from public folder
$mainOldfolders = File::directories($public_folder_path);
// dd("School Admin",$mainOldfolders);
foreach ($mainOldfolders as $subfolder) {
foreach (File::files($subfolder) as $file) {
File::delete($file);
}
}
// Un-Zip and move data
if ($zip->open($request->zip_file) === TRUE) {
$zip->extractTo($backup_folder_path);
$mainfolders = File::directories($backup_folder_path);
foreach ($mainfolders as $subfolder) {
// Check if the folder doesn't contain the specific 'database-backup' path
if (!Str::contains($subfolder, $authId . "/database-backup")) {
if (File::isDirectory($subfolder . '/' . $authId)) {
// Get the list of subfolders
$mainSubfolders = File::directories($subfolder . '/' . $authId);
// Iterate through each subfolder
foreach ($mainSubfolders as $folder) {
$folderName = basename($folder);
// Build the destination path for the folder
$destinationPath = $public_folder_path . "/" . $folderName;
File::move($folder, $destinationPath);
}
}
}
}
$zip->close();
}
// Read Backup .sql File
$sql_file_path = "";
foreach (File::directories($backup_folder_path) as $subfolders) {
foreach (File::files($subfolders) as $subfolders) {
$sql_file_path = pathinfo($subfolders)['dirname'] . "/" . pathinfo($subfolders)['basename'];
}
}
try {
DB::beginTransaction();
DB::statement("SET FOREIGN_KEY_CHECKS = 0");
$tables = DB::connection()->getDoctrineSchemaManager()->listTableNames();
$expectedTables = ['addons', 'attachments', 'categories', 'chats', 'failed_jobs', 'features', 'feature_sections', 'feature_section_lists', 'guidances', 'languages', 'messages', 'migrations', 'packages', 'package_features', 'password_resets', 'personal_access_tokens', 'staff_support_schools', 'system_settings', 'user_status_for_next_cycles', 'database_backups'];
$allTables = array_diff($tables, $expectedTables);
$tableNames = array_values($allTables);
// dd($tableNames);
foreach ($tableNames as $table) {
DB::table($table)->truncate();
\Log::info("Table {$table} truncated.");
}
DB::statement("SET FOREIGN_KEY_CHECKS = 1");
DB::commit();
} catch (\Exception $e) {
DB::rollBack();
\Log::error('Truncation error: ' . $e->getMessage());
}
// Check if the SQL file exists and proceed with execution
if (File::exists($sql_file_path)) {
// Read the contents of the SQL file
$sql = File::get($sql_file_path);
$queries = array_filter(
explode(";\n", $sql),
function ($query) {
return !empty(trim($query));
}
);
try {
// Begin transaction
DB::beginTransaction();
DB::statement("SET FOREIGN_KEY_CHECKS = 0");
foreach ($queries as $query) {
if (!empty(trim($query))) {
DB::unprepared(trim($query));
\Log::info("Executed query: {$query}");
}
}
DB::statement("SET FOREIGN_KEY_CHECKS = 1");
DB::commit();
} catch (\Exception $e) {
DB::rollBack();
\Log::error("Transaction failed: " . $e->getMessage());
}
} else {
\Log::warning('SQL file not found: ' . $sql_file_path);
}
// Delete backup folders and .sql files after backup
$deleteBackupFolders = File::directories($backup_folder_path);
foreach ($deleteBackupFolders as $subfolder) {
if (Str::contains($subfolder, $authId . "/database-backup")) {
// Delete all .sql files in the subfolder
foreach (File::files($subfolder) as $subpath) {
if (pathinfo($subpath)['extension'] === 'sql') {
File::delete($subpath);
}
}
// Delete the subfolder after its contents have been deleted
File::deleteDirectory($subfolder);
}
}
// add school current version migration
$schools = School::withTrashed()->where('id', Auth::user()->school_id)->get();
foreach ($schools as $key => $school) {
Config::set('database.connections.school.database', $school->database_name);
DB::purge('school');
DB::connection('school')->reconnect();
DB::setDefaultConnection('school');
Artisan::call('migrate', [
'--database' => 'school',
'--path' => 'database/migrations/schools',
'--force' => true,
]);
}
} else if (Auth::user()->hasRole('Super Admin')) {
// File UnZip
$zip = new ZipArchive;
$backup_type = 'super-admin';
$public_folder_path = storage_path('app/public/' . $authId);
$backup_folder_path = storage_path('app/public/database-backup/' . $backup_type . '/' . $authId);
// Ensure the school ID folder exists
if (!file_exists($public_folder_path)) {
File::makeDirectory($public_folder_path, 0755, true);
}
// Old Data Delete from public folder
$mainOldfolders = File::directories($public_folder_path);
// dd("School Admin",$mainOldfolders);
foreach ($mainOldfolders as $subfolder) {
foreach (File::files($subfolder) as $file) {
File::delete($file);
}
}
// Un-Zip and move data
if ($zip->open($request->zip_file) === TRUE) {
$zip->extractTo($backup_folder_path);
$mainfolders = File::directories($backup_folder_path);
foreach ($mainfolders as $subfolder) {
// Check if the folder doesn't contain the specific 'database-backup' path
if (!Str::contains($subfolder, $authId . "/database-backup")) {
if (File::isDirectory($subfolder . '/' . $authId)) {
// Get the list of subfolders
$mainSubfolders = File::directories($subfolder . '/' . $authId);
// Iterate through each subfolder
foreach ($mainSubfolders as $folder) {
$folderName = basename($folder);
// Build the destination path for the folder
$destinationPath = $public_folder_path . "/" . $folderName;
File::move($folder, $destinationPath);
}
}
}
}
$zip->close();
}
// Read Backup .sql File
$sql_file_path = "";
foreach (File::directories($backup_folder_path) as $subfolders) {
foreach (File::files($subfolders) as $subfolders) {
$sql_file_path = pathinfo($subfolders)['dirname'] . "/" . pathinfo($subfolders)['basename'];
}
}
try {
// Start the transaction
DB::beginTransaction();
// Disable foreign key checks
DB::statement("SET FOREIGN_KEY_CHECKS = 0");
// Get all table names
$tables = DB::connection()->getDoctrineSchemaManager()->listTableNames();
$expectedTables = ['addons', 'attachments', 'categories', 'chats', 'failed_jobs', 'features', 'feature_sections', 'feature_section_lists', 'guidances', 'languages', 'messages', 'migrations', 'packages', 'package_features', 'password_resets', 'personal_access_tokens', 'staff_support_schools', 'system_settings', 'user_status_for_next_cycles', 'database_backups'];
$allTables = array_diff($tables, $expectedTables);
$tableNames = array_values($allTables);
// Loop through each table and truncate
foreach ($tableNames as $table) {
// You can use `DB::table($table)->truncate()` or use raw SQL for truncation
DB::table($table)->truncate();
\Log::info("Table {$table} truncated.");
}
// Re-enable foreign key checks
DB::statement("SET FOREIGN_KEY_CHECKS = 1");
// Commit the transaction
DB::commit();
} catch (\Exception $e) {
// Rollback the transaction if an error occurs
DB::rollBack();
\Log::error('Truncation error: ' . $e->getMessage());
}
// Check if the SQL file exists and proceed with execution
if (File::exists($sql_file_path)) {
// Read the contents of the SQL file
$sql = File::get($sql_file_path);
$queries = array_filter(
explode(";\n", $sql),
function ($query) {
return !empty(trim($query));
}
);
try {
// Begin transaction
DB::beginTransaction();
DB::statement("SET FOREIGN_KEY_CHECKS = 0");
foreach ($queries as $query) {
if (!empty(trim($query))) {
DB::unprepared(trim($query));
\Log::info("Executed query: {$query}");
}
}
DB::statement("SET FOREIGN_KEY_CHECKS = 1");
DB::commit();
} catch (\Exception $e) {
DB::rollBack();
\Log::error("Transaction failed: " . $e->getMessage());
}
} else {
\Log::warning('SQL file not found: ' . $sql_file_path);
}
// Delete backup folders and .sql files after backup
$deleteBackupFolders = File::directories($backup_folder_path);
foreach ($deleteBackupFolders as $subfolder) {
if (Str::contains($subfolder, $authId . "/database-backup")) {
// Delete all .sql files in the subfolder
foreach (File::files($subfolder) as $subpath) {
if (pathinfo($subpath)['extension'] === 'sql') {
File::delete($subpath);
}
}
// Delete the subfolder after its contents have been deleted
File::deleteDirectory($subfolder);
}
}
}
ResponseService::successResponse('Data Restore Successfully');
} catch (\Throwable $th) {
DB::rollBack();
ResponseService::logErrorResponse($th, "DatabaseBackup Controller -> Restore Method");
ResponseService::errorResponse();
}
}
public function getTableNameFromQuery($query)
{
if (preg_match('/CREATE TABLE `?(\w+)`?/', $query, $matches)) {
return $matches[1];
}
return null;
}
public function deleteOldRecords($subscription)
{
$schoolId = $subscription->school_id;
if ($subscription) {
$guardian_ids = Students::where('school_id', $schoolId)->pluck('guardian_id')->toArray();
Mediums::where('school_id', $schoolId)->delete();
User::where('school_id', $schoolId)->whereNot('id', Auth::user()->id)->whereNot('id', $subscription->school->admin_id)->delete();
User::whereIn('id', $guardian_ids)->delete();
CertificateTemplate::where('school_id', $schoolId)->delete();
ClassGroup::where('school_id', $schoolId)->delete();
ExpenseCategory::where('school_id', $schoolId)->delete();
Faq::where('school_id', $schoolId)->delete();
FeesType::where('school_id', $schoolId)->delete();
ModelsFile::where('school_id', $schoolId)->delete();
FormField::where('school_id', $schoolId)->delete();
SessionYear::where('school_id', $schoolId)->delete();
Grade::where('school_id', $schoolId)->delete();
Holiday::where('school_id', $schoolId)->delete();
SchoolSetting::where('school_id', $schoolId)->delete();
Section::where('school_id', $schoolId)->delete();
Semester::where('school_id', $schoolId)->delete();
Shift::where('school_id', $schoolId)->delete();
Slider::where('school_id', $schoolId)->delete();
Stream::where('school_id', $schoolId)->delete();
Subscription::where('school_id', $schoolId)->delete();
PaymentTransaction::where('school_id', $schoolId)->delete();
}
}
}