Archive2 Controller Documentation

File: /controllers/archive2Controller.php

Purpose: Advanced database archiving and backup operations with selective table migration

Last Updated: December 20, 2024

Total Functions: 12+

Lines of Code: ~584

---

📋 Overview

The Archive2 Controller is an enhanced version of the archiving system that provides sophisticated database backup and restoration capabilities. It handles:

• Advanced database structure copying
• Selective table data migration
• Account tree cleanup during archiving
• Intelligent table relationship handling
• Date-based data filtering
• Category-based product filtering
• Check status management
• Database engine conversion (MyISAM/InnoDB)
• Multi-database management
• User session management for database switching

Primary Functions

✅ Create new database with selective data migration
✅ Empty database tables with settings preservation
✅ Copy database structure while maintaining relationships
✅ Clean up account tree references for non-migrated data
✅ Date-based data filtering during archiving
✅ Category-specific product migration
✅ Database engine conversion
✅ Database listing and switching
✅ Transaction-safe operations
✅ Advanced SQL file processing

Related Controllers

• archiveController.php - Basic archiving operations
• backupController.php - Database backup utilities
• programsettingsController.php - System configuration

---

🗄️ Database Tables

Core Management Tables

Table Name	Purpose	Key Columns
newdbname	Database registry	newdbnameId, dbname
usergroup	User group management	usergroupid, startpage
accountstree	Chart of accounts	id, parent, customName

### Data Tables (Selective Migration)

Table Name	Purpose	Key Columns
product	Product master	productId, productCatId
productcat	Product categories	productCatId, productCatParent
client	Customer data	clientid, clientarea
supplier	Supplier data	supplierid
employee	Employee records	employeeid
bank	Banking information	bankid
assets	Asset management	assetid, assetscatid

### Transaction Tables (Date Filtered)

Table Name	Purpose	Key Columns
savedaily	Daily cash transactions	savedailydate
clientdebtchange	Customer debt history	clientdebtchangedate
supplierdebtchange	Supplier debt history	supplierdebtchangedate
salaryreport	Salary payments	salaryreportdate
employeepersonnel	Employee records	employeepersonneldate

### System Tables (Always Migrated)

Table Name	Purpose	Key Columns
billproperty	Bill properties	billpropertyid
programsettings	System settings	programsettingsid
user	System users	userid
usergroup	User groups	usergroupid
store	Store/warehouse data	storeid
save	Cash registers	saveid

---

🔑 Key Functions

1. emptying() - Advanced Table Clearing

Location: Line 123

Purpose: Intelligently empty database tables with preservation options

Function Signature:

function emptying()

Process Flow:

1. Determine empty type from POST data

2. Build table exclusion list for settings preservation

3. Execute TRUNCATE commands via RedBean

4. Preserve critical system configuration

Empty Types:

• emptyType = 0 - Empty all tables
• emptyType = 1 - Keep settings tables only

Preserved Tables:

$settingsTables = array("programsettings", "billproperty", "billname", 
                       "billsettings", "properties", "relusergroupproperties", 
                       "user", "usergroup", "menuurl");

---

2. createNewDB() - Advanced Database Creation

Location: Line 147

Purpose: Create new database with intelligent data migration

Function Signature:

function createNewDB()

Process Flow:

1. Parse selected migration options

2. Build stable tables array based on selections

3. Create new database with timestamped name

4. Copy structure and migrate selected data

5. Clean up account tree references

6. Apply date filters and special options

7. Register new database in system

Migration Options:

• Products with categories and units
• Clients with areas and debt history
• Suppliers with debt tracking
• Employees with attendance and salary
• Banking and financial data
• Assets and maintenance records
• Production and task orders

Key Features:

// Timestamped database names
$newName = $newName . '_' . date("dmY") . '_' . date("his");

// Intelligent table grouping
$stableTablesSorted = array(
    'products' => array("product", "productcat", "productunit", ...),
    'client' => array("client", "clientarea", "typeclient", ...),
    'supplier' => array("supplier", "typesupplier", ...)
);

---

3. copyNewDBStructureFromOldOneAndFillReqTables() - Database Replication

Location: Line 436

Purpose: Advanced database structure copying with selective data migration

Function Signature:

function copyNewDBStructureFromOldOneAndFillReqTables($hostname, $username, $password, 
                                                     $sourceDatabase, $destinationDatabase, 
                                                     $stableTables)

Process Flow:

1. Establish source and destination connections

2. Create destination database if needed

3. Iterate through all source tables

4. Copy table structure using LIKE syntax

5. Migrate data for selected tables only

6. Clean account tree for non-migrated entities

7. Apply special business rules and filters

Account Tree Cleanup:

// Remove tree references for non-migrated tables
if (!in_array($tableName, $stableTables)) {
    $destinationConnection->query("DELETE FROM accountstree 
                                  WHERE id IN (SELECT $colName FROM $sourceDatabase.$tableName)");
}

---

4. Special Data Processing Functions

Cash Register Reset

if ($archiveOptionForCheck == 1) {
    $destinationConnection->query("update save set savecurrentvalue = 0");
}

Completed Checks Cleanup

if ($archiveOptionForCheck == 1) {
    $destinationConnection->query("DELETE FROM datedchecked WHERE done = 1");
}

Date-Based Filtering

$destinationConnection->query("DELETE FROM savedaily 
                              WHERE date(savedailydate) < '" . $from . "'");
$destinationConnection->query("DELETE FROM clientdebtchange 
                              WHERE date(clientdebtchangedate) < '" . $from . "'");

Category-Based Product Filtering

if ($catOptionForCheck == 1) {
    // Clean category IDs and remove unwanted products
    $destinationConnection->query("DELETE FROM productcat 
                                  WHERE productCatId NOT IN ($caiIdsToBeTransfered)");
    $destinationConnection->query("DELETE FROM product 
                                  WHERE productCatId NOT IN (SELECT productCatId FROM productcat)");
}

---

5. loadDatabases() - Database Registry

Location: Line 355

Purpose: Load available archived databases for selection

Function Signature:

function loadDatabases()

Returns: Array of database objects for dropdown population

---

6. restoreDB() - Database Switching

Location: Line 363

Purpose: Switch active database session to selected archive

Function Signature:

function restoreDB()

Process Flow:

1. Validate selected database ID

2. Load database record from registry

3. Update session variable

4. Redirect to user's start page

---

7. alterDB() - Engine Conversion

Location: Line 385

Purpose: Convert database tables between MyISAM and InnoDB engines

Function Signature:

function alterDB()

Supported Tables:

$tablesArray = array("buyandruternbill", "buybill", "sellbill", 
                    "product", "storedetail", "storemovement", 
                    "supplierdebtchange", "movementmanage");

Engine Types:

• engineType = 1 - Convert to MyISAM
• engineType = 2 - Convert to InnoDB

---

🔄 Workflows

Workflow 1: Advanced Database Creation

┌─────────────────────────────────────────────────────────────┐

START: Select Migration Options

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Parse Migration Selections

- Product categories and related tables

- Client/supplier data with history

- Employee records and attendance

- Banking and financial accounts

- Assets and maintenance data

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Build Stable Tables Array

- Start with core system tables

- Add selected module tables

- Remove duplicates and validate

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Create Destination Database

- Generate timestamped name

- Create database structure

- Establish connections

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

4Copy Structure and Data

FOR EACH source table:

│

→ Copy table structure (CREATE TABLE LIKE)

│

├─→ IF table in stable list:

│ │ └─→ Copy all data (INSERT INTO SELECT) │

│

│ └─→ ELSE: Create empty table │

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

5Clean Account Tree References

- Find tables with tree columns

- Remove account tree entries for non-migrated data

- Maintain accounting integrity

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

6Apply Business Rules

- Reset cash register values if requested

- Remove completed checks if requested

- Apply date filters to transaction tables

- Filter products by categories if requested

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

7Register and Activate

- Register new database in system

- Update file registry

- Destroy session to force re-login

└─────────────────────────────────────────────────────────────┘

---

Workflow 2: Intelligent Table Emptying

┌─────────────────────────────────────────────────────────────┐

START: Select Empty Options

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Determine Empty Type

- emptyType = 0: Empty all tables

- emptyType = 1: Preserve settings tables

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Build Table List

IF preserving settings:

→ Get all tables

│ └─→ Exclude settings tables │

ELSE:

│ └─→ Get all tables │

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Execute TRUNCATE Operations

FOR EACH table in list:

│ └─→ Execute: TRUNCATE TABLE tablename │

└─────────────────────────────────────────────────────────────┘

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty)	Default view	Show archive options form
`do=emptydata`	Show form	Display table emptying interface
`do=emptying`	`emptying()`	Execute table emptying operation
`do=archive`	Show form	Display advanced archive creation form
`do=newDB`	`createNewDB()`	Create new archived database
`do=changDB` or `do=show`	`loadDatabases()`	Show database selection interface
`do=restoreDB`	`restoreDB()`	Switch to selected database
`do=dbType`	Show form	Display engine conversion options
`do=alterdb`	`alterDB()`	Convert database engine type

### Required Parameters by Action

Database Creation (do=newDB):

• dbName - New database name
• choosedItem[] - Array of data to migrate
• archiveOptionForCheck - Reset cash registers flag
• from - Date filter start (optional)
• catOptionForCheck - Category filter flag
• caiIdsToBeTransfered - Category IDs to migrate

Table Emptying (do=emptying):

• emptyType - 0 (all) or 1 (preserve settings)

Database Restore (do=restoreDB):

• newdbnameid - Database ID to restore

Engine Conversion (do=alterdb):

• type - 1 (MyISAM) or 2 (InnoDB)

---

🧮 Advanced Features

Category-Based Product Migration

// Parse and validate category IDs
$caiIdsToBeTransfered = $_POST['caiIdsToBeTransfered'];
$caiIdsToBeTransfered = preg_replace('/[^0-9,]/', '', $caiIdsToBeTransfered);
$caiIdsToBeTransfered = array_filter(explode(',', $caiIdsToBeTransfered));
$caiIdsToBeTransfered = implode(',', $caiIdsToBeTransfered);

// Remove unwanted categories and related data
$destinationConnection->query("DELETE FROM productcat 
                              WHERE productCatId NOT IN ($caiIdsToBeTransfered) 
                              AND productCatParent NOT IN ($caiIdsToBeTransfered)");

Account Tree Integrity Maintenance

// Find all tables with tree references
$tablesInTreeSql = "SELECT c.table_name, c.column_name
                   FROM information_schema.columns c
                   WHERE c.column_name LIKE 'tree%' 
                   AND c.column_name != 'treeType'";

// Clean up tree references for non-migrated data
foreach ($tablesInTree as $value) {
    if (!in_array($tableName, $stableTables)) {
        $destinationConnection->query("DELETE FROM accountstree 
                                      WHERE id IN (SELECT $colName FROM $sourceDatabase.$tableName)");
    }
}

Transaction-Safe Operations

mysql_query("START TRANSACTION");
mysql_query("BEGIN");
try {
    // Perform operations
    mysql_query("COMMIT");
} catch (Exception $exc) {
    mysql_query("ROLLBACK");
    echo $exc->getTraceAsString();
}

---

🔒 Security & Validation

Input Sanitization

// Category ID validation
$caiIdsToBeTransfered = preg_replace('/[^0-9,]/', '', $caiIdsToBeTransfered);
$caiIdsToBeTransfered = array_filter(explode(',', $caiIdsToBeTransfered));

Database Connection Security

• Uses ConnectionProperty class for credentials
• Separate connections for source and destination
• Proper error handling and cleanup
• Transaction rollback on failures

Permission Checks

• All operations require authentication
• Session-based user validation
• Administrative operation restrictions

---

📊 Performance Considerations

Optimization Strategies

1. Batch Operations: Copy data in bulk using INSERT INTO SELECT

2. Index Preservation: Table structure copying maintains indexes

3. Connection Management: Separate connections prevent blocking

4. Memory Efficiency: Process tables individually to avoid memory issues

Known Limitations

• Large databases may require extended execution time
• Memory usage scales with table sizes
• No progress indication for long operations
• Limited rollback options once committed

---

🐛 Common Issues & Troubleshooting

1. Database Creation Failures

Issue: New database creation fails

Cause: Insufficient privileges or name conflicts

Debug:

-- Check database privileges
SHOW GRANTS FOR CURRENT_USER;

-- Check existing databases
SHOW DATABASES LIKE 'pattern%';

2. Account Tree Inconsistencies

Issue: Broken account references after migration

Cause: Missing tree cleanup for non-migrated entities

Fix: Run account tree validation queries and manual cleanup

3. Category Filter Issues

Issue: Products disappear unexpectedly

Cause: Parent category relationships not preserved

Solution: Include all parent categories in migration list

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• archiveController.md - Basic archive operations
• Database Schema Documentation - Table relationships and constraints

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

Archive2 Documentation

Archive2 Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Core Management Tables

🔑 Key Functions

1. emptying() - Advanced Table Clearing

2. createNewDB() - Advanced Database Creation

3. copyNewDBStructureFromOldOneAndFillReqTables() - Database Replication

4. Special Data Processing Functions

Cash Register Reset

Completed Checks Cleanup

Date-Based Filtering

Category-Based Product Filtering

5. loadDatabases() - Database Registry

6. restoreDB() - Database Switching

7. alterDB() - Engine Conversion

🔄 Workflows

Workflow 1: Advanced Database Creation

Workflow 2: Intelligent Table Emptying

🌐 URL Routes & Actions

🧮 Advanced Features

Category-Based Product Migration

Account Tree Integrity Maintenance

Transaction-Safe Operations

🔒 Security & Validation

Input Sanitization

Database Connection Security

Permission Checks

📊 Performance Considerations

Optimization Strategies

Known Limitations

🐛 Common Issues & Troubleshooting

1. Database Creation Failures

2. Account Tree Inconsistencies

3. Category Filter Issues

📚 Related Documentation