# 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
- [x] Create new database with selective data migration
- [x] Empty database tables with settings preservation
- [x] Copy database structure while maintaining relationships
- [x] Clean up account tree references for non-migrated data
- [x] Date-based data filtering during archiving
- [x] Category-specific product migration
- [x] Database engine conversion
- [x] Database listing and switching
- [x] Transaction-safe operations
- [x] Advanced SQL file processing

### Related Controllers
- [archiveController.php](archiveController.md) - Basic archiving operations
- [backupController.php](backupController.md) - Database backup utilities
- [programsettingsController.php](programsettingsController.md) - 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**:
```php
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**:
```php
$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**:
```php
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**:
```php
// 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**:
```php
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**:
```php
// 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**
```php
if ($archiveOptionForCheck == 1) {
    $destinationConnection->query("update save set savecurrentvalue = 0");
}
```

#### **Completed Checks Cleanup**
```php
if ($archiveOptionForCheck == 1) {
    $destinationConnection->query("DELETE FROM datedchecked WHERE done = 1");
}
```

#### **Date-Based Filtering**
```php
$destinationConnection->query("DELETE FROM savedaily 
                              WHERE date(savedailydate) < '" . $from . "'");
$destinationConnection->query("DELETE FROM clientdebtchange 
                              WHERE date(clientdebtchangedate) < '" . $from . "'");
```

#### **Category-Based Product Filtering**
```php
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**:
```php
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**:
```php
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**:
```php
function alterDB()
```

**Supported Tables**:
```php
$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             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Migration Selections                             │
│     - Product categories and related tables                │
│     - Client/supplier data with history                    │
│     - Employee records and attendance                      │
│     - Banking and financial accounts                       │
│     - Assets and maintenance data                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Stable Tables Array                              │
│     - Start with core system tables                        │
│     - Add selected module tables                           │
│     - Remove duplicates and validate                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Create Destination Database                             │
│     - Generate timestamped name                            │
│     - Create database structure                            │
│     - Establish connections                                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Copy 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                         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Clean Account Tree References                          │
│     - Find tables with tree columns                        │
│     - Remove account tree entries for non-migrated data    │
│     - Maintain accounting integrity                        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Apply 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           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  7. Register 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                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Determine Empty Type                                    │
│     - emptyType = 0: Empty all tables                      │
│     - emptyType = 1: Preserve settings tables              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Table List                                        │
│     IF preserving settings:                                 │
│       ├─→ Get all tables                                   │
│       └─→ Exclude settings tables                          │
│     ELSE:                                                   │
│       └─→ Get all tables                                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Execute 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
```php
// 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
```php
// 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
```php
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
```php
// 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**:
```sql
-- 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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [archiveController.md](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