thetool/application/WarehouseStocktake/WarehouseStocktakeController.php

<?php

class WarehouseStocktakeController extends TTCrud {
    protected string $headerTitle = 'Inventur';
    protected string $createText = 'Inventur erstellen';
    protected bool $reopenOnCreate = false;

    protected array $columns = [
        ['key' => 'stocktakeNumber', 'text' => 'Inventur-Nr.', 'required' => false,
         'modal' => false,
         'table' => ['priority' => 10]],
        ['key' => 'title', 'text' => 'Titel', 'required' => true,
         'modal' => ['type' => 'text'],
         'table' => ['priority' => 9]],
        ['key' => 'warehouseLocationId', 'text' => 'Lagerort', 'required' => true,
         'modal' => ['type' => 'select', 'items' => []],
         'table' => ['priority' => 8, 'filter' => 'select']],
        ['key' => 'status', 'text' => 'Status', 'required' => false,
         'modal' => false,
         'table' => ['priority' => 7, 'filter' => 'iconSelect', 'filterOptions' => [
             ['value' => 'planned', 'text' => 'Geplant', 'icon' => 'fas fa-calendar text-secondary'],
             ['value' => 'in_progress', 'text' => 'In Bearbeitung', 'icon' => 'fas fa-cog text-primary'],
             ['value' => 'completed', 'text' => 'Abgeschlossen', 'icon' => 'fas fa-check-circle text-success'],
             ['value' => 'cancelled', 'text' => 'Abgebrochen', 'icon' => 'fas fa-times-circle text-danger'],
         ]]],
        ['key' => 'progress', 'text' => 'Fortschritt', 'required' => false,
         'modal' => false,
         'table' => ['priority' => 6, 'sortable' => false, 'filter' => false]],
        ['key' => 'startedAt', 'text' => 'Gestartet', 'required' => false,
         'modal' => false,
         'table' => ['priority' => 5, 'filter' => false]],
        ['key' => 'description', 'text' => 'Beschreibung', 'required' => false,
         'modal' => ['type' => 'textarea'],
         'table' => false],
        ['key' => 'actions', 'text' => 'Aktionen', 'required' => false,
         'modal' => false,
         'table' => ['filter' => false, 'sortable' => false, 'class' => 'text-center']],
    ];

    protected array $additionalActions = [
        ['key' => 'startStocktake', 'title' => 'Inventur starten', 'class' => 'fas fa-play text-success'],
        ['key' => 'viewProgress', 'title' => 'Fortschritt anzeigen', 'class' => 'fas fa-chart-line text-primary'],
        ['key' => 'completeStocktake', 'title' => 'Inventur abschließen', 'class' => 'fas fa-check text-success'],
        ['key' => 'applyToStock', 'title' => 'Auf Lager anwenden', 'class' => 'fas fa-boxes text-warning'],
        ['key' => 'exportReport', 'title' => 'Excel Export', 'class' => 'fas fa-download text-secondary'],
        ['key' => 'openHistory', 'title' => 'Historie', 'class' => 'fas fa-history text-secondary'],
    ];

    protected array $additionalJSVariables = [];

    protected array $statusOptions = [
        ['value' => 'planned', 'text' => 'Geplant', 'icon' => 'fas fa-calendar text-secondary', 'color' => 'secondary'],
        ['value' => 'in_progress', 'text' => 'In Bearbeitung', 'icon' => 'fas fa-cog text-primary', 'color' => 'primary'],
        ['value' => 'completed', 'text' => 'Abgeschlossen', 'icon' => 'fas fa-check-circle text-success', 'color' => 'success'],
        ['value' => 'cancelled', 'text' => 'Abgebrochen', 'icon' => 'fas fa-times-circle text-danger', 'color' => 'danger'],
    ];

    protected array $permissionCheck = ['WarehouseUser'];

    protected array $infoMessages = [
        'create' => 'Inventur wurde erstellt',
        'update' => 'Inventur wurde aktualisiert',
        'delete' => 'Inventur wurde gelöscht',
        'noChanges' => 'Keine Änderungen',
    ];

    public function prepareCrudConfig() {
        // Populate locations dropdown
        $locations = array_map(function($location) {
            return ['value' => $location->id, 'text' => $location->title];
        }, WarehouseLocationModel::getAll());

        foreach ($this->columns as &$col) {
            if ($col['key'] === 'warehouseLocationId') {
                $col['modal']['items'] = $locations;
                $col['table']['filterOptions'] = $locations;
            }
        }

        $this->additionalJSVariables['STATUS_ITEMS'] = $this->statusOptions;
    }

    protected function beforeCreate(): bool {
        // Set default values
        $this->postData['status'] = 'planned';
        $this->postData['totalItems'] = 0;
        $this->postData['totalScannedItems'] = 0;
        return true;
    }

    protected function afterCreate($postData) {
        // Generate stocktake number
        $stocktake = WarehouseStocktakeModel::get($postData['id']);
        if ($stocktake) {
            $stocktakeNumber = WarehouseStocktakeModel::generateStocktakeNumber();
            $db = FronkDB::singleton();
            $db->query("UPDATE WarehouseStocktake SET stocktakeNumber = '{$stocktakeNumber}' WHERE id = {$stocktake->id}");

            // Log creation
            WarehouseStocktakeLogModel::log($stocktake->id, 'created', null, ['title' => $stocktake->title]);
        }
    }

    protected function beforeUpdate($postData): bool {
        (new WarehouseHistoryController)->create($postData, $this->mod);
        return true;
    }

    protected function customRowsHandler($rows) {
        return array_map(fn($row) => $this->formatRow((array)$row), $rows);
    }

    protected function formatRow($row) {
        // Keep raw status for frontend conditional logic (don't modify 'status' - table needs raw value for filter)
        $row['rawStatus'] = $row['status'];

        // Don't modify warehouseLocationId - table uses items to display the text
        // Don't modify status - table uses filterOptions to display

        // Format progress (no filter on this column)
        $row['progress'] = "<span class='badge bg-info'>{$row['totalScannedItems']} Artikel gescannt</span>";

        // Format startedAt (no filter on this column)
        if ($row['startedAt']) {
            $row['startedAt'] = date('d.m.Y H:i', $row['startedAt']);
        } else {
            $row['startedAt'] = '-';
        }

        return $row;
    }

    /**
     * Start a stocktake - changes status to in_progress
     */
    protected function startStocktakeAction() {
        $id = intval($this->postData['id'] ?? 0);
        if (!$id) {
            self::returnJson(['success' => false, 'message' => 'Keine Inventur-ID angegeben']);
            return;
        }

        $stocktake = WarehouseStocktakeModel::get($id);
        if (!$stocktake) {
            self::returnJson(['success' => false, 'message' => 'Inventur nicht gefunden']);
            return;
        }

        if ($stocktake->status !== 'planned') {
            self::returnJson(['success' => false, 'message' => 'Inventur kann nur im Status "Geplant" gestartet werden']);
            return;
        }

        $db = FronkDB::singleton();
        $db->query("UPDATE WarehouseStocktake SET
                    status = 'in_progress',
                    startedAt = " . time() . ",
                    startedBy = {$this->user->id}
                    WHERE id = {$id}");

        WarehouseStocktakeLogModel::log($id, 'started', null, ['startedBy' => $this->user->name]);

        self::returnJson(['success' => true, 'message' => 'Inventur wurde gestartet']);
    }

    /**
     * Complete a stocktake - changes status to completed
     */
    protected function completeStocktakeAction() {
        $id = intval($this->postData['id'] ?? 0);
        if (!$id) {
            self::returnJson(['success' => false, 'message' => 'Keine Inventur-ID angegeben']);
            return;
        }

        $stocktake = WarehouseStocktakeModel::get($id);
        if (!$stocktake) {
            self::returnJson(['success' => false, 'message' => 'Inventur nicht gefunden']);
            return;
        }

        if ($stocktake->status !== 'in_progress') {
            self::returnJson(['success' => false, 'message' => 'Inventur kann nur im Status "In Bearbeitung" abgeschlossen werden']);
            return;
        }

        $db = FronkDB::singleton();
        $db->query("UPDATE WarehouseStocktake SET
                    status = 'completed',
                    completedAt = " . time() . ",
                    completedBy = {$this->user->id}
                    WHERE id = {$id}");

        WarehouseStocktakeLogModel::log($id, 'completed', null, ['completedBy' => $this->user->name]);

        self::returnJson(['success' => true, 'message' => 'Inventur wurde abgeschlossen']);
    }

    /**
     * Get progress data for live updates
     */
    protected function getProgressAction() {
        $id = intval($this->request->id);
        if (!$id) {
            self::returnJson(['success' => false, 'message' => 'Keine Inventur-ID angegeben']);
            return;
        }

        $stocktake = WarehouseStocktakeModel::get($id);
        if (!$stocktake) {
            self::returnJson(['success' => false, 'message' => 'Inventur nicht gefunden']);
            return;
        }

        // Get items via direct SQL to avoid any ORM issues
        $db = FronkDB::singleton();
        $result = $db->query("SELECT si.*, a.articleNumber, a.title as articleTitle, a.cheapestPurchasePrice, w.name as scannedByName,
                              CASE WHEN si.overwrittenById IS NOT NULL THEN 1 ELSE 0 END as isOverwritten
                              FROM WarehouseStocktakeItem si
                              LEFT JOIN WarehouseArticle a ON si.articleId = a.id
                              LEFT JOIN Worker w ON si.scannedBy = w.id
                              WHERE si.stocktakeId = {$id}
                              ORDER BY si.`create` DESC");

        $formattedItems = [];
        $totalValue = 0;
        $totalQuantity = 0;
        while ($row = $result->fetch_assoc()) {
            $unitPrice = $row['cheapestPurchasePrice'] ? (float)$row['cheapestPurchasePrice'] : 0;
            $quantity = (float)$row['countedQuantity'];
            $lineTotal = $unitPrice * $quantity;
            $isOverwritten = (bool)$row['isOverwritten'];

            // Only count non-overwritten items in totals
            if (!$isOverwritten) {
                $totalValue += $lineTotal;
                $totalQuantity += $quantity;
            }

            $formattedItems[] = [
                'id' => (int)$row['id'],
                'articleId' => (int)$row['articleId'],
                'articleNumber' => $row['articleNumber'] ?? '',
                'articleTitle' => $row['articleTitle'] ?? 'Unbekannt',
                'countedQuantity' => $quantity,
                'unitPrice' => $unitPrice,
                'lineTotal' => $lineTotal,
                'rack' => $row['rack'],
                'shelf' => $row['shelf'],
                'note' => $row['note'],
                'scannedAt' => $row['scannedAt'] ? date('d.m.Y H:i:s', $row['scannedAt']) : null,
                'scannedBy' => $row['scannedByName'],
                'isOverwritten' => $isOverwritten,
            ];
        }

        $location = $stocktake->getLocation();

        self::returnJson([
            'success' => true,
            'stocktake' => [
                'id' => $stocktake->id,
                'stocktakeNumber' => $stocktake->stocktakeNumber,
                'title' => $stocktake->title,
                'status' => $stocktake->status,
                'locationName' => $location ? $location->title : 'Unbekannt',
                'totalScannedItems' => $stocktake->totalScannedItems,
                'startedAt' => $stocktake->startedAt ? date('d.m.Y H:i', $stocktake->startedAt) : null,
            ],
            'items' => $formattedItems,
            'summary' => [
                'totalValue' => $totalValue,
                'totalQuantity' => $totalQuantity,
            ],
        ]);
    }

    /**
     * Apply stocktake results to actual warehouse stock
     */
    protected function applyToStockAction() {
        $id = intval($this->postData['id'] ?? 0);
        if (!$id) {
            self::returnJson(['success' => false, 'message' => 'Keine Inventur-ID angegeben']);
            return;
        }

        $stocktake = WarehouseStocktakeModel::get($id);
        if (!$stocktake) {
            self::returnJson(['success' => false, 'message' => 'Inventur nicht gefunden']);
            return;
        }

        if ($stocktake->status !== 'completed') {
            self::returnJson(['success' => false, 'message' => 'Inventur muss abgeschlossen sein, um die Bestände anzupassen']);
            return;
        }

        $db = FronkDB::singleton();
        $items = WarehouseStocktakeItemModel::getAll(['stocktakeId' => $id]);
        $appliedCount = 0;
        $createdCount = 0;

        foreach ($items as $item) {
            // Check if a WarehouseItem already exists for this article at this location
            $existingItems = WarehouseItemModel::getAll([
                'articleId' => $item->articleId,
                'warehouseLocationId' => $stocktake->warehouseLocationId
            ]);

            if (count($existingItems) > 0) {
                // Update existing item
                $existingItem = $existingItems[0];
                $oldQuantity = $existingItem->quantity;

                $db->query("UPDATE WarehouseItem SET
                            quantity = {$item->countedQuantity},
                            rack = " . ($item->rack ? "'{$db->escape($item->rack)}'" : "NULL") . ",
                            shelf = " . ($item->shelf ? "'{$db->escape($item->shelf)}'" : "NULL") . "
                            WHERE id = {$existingItem->id}");

                // Log history
                (new WarehouseHistoryController)->create([
                    'id' => $existingItem->id,
                    'quantity' => $item->countedQuantity,
                    'rack' => $item->rack,
                    'shelf' => $item->shelf,
                ], 'WarehouseItem');

                $appliedCount++;
            } else {
                // Create new WarehouseItem
                $db->query("INSERT INTO WarehouseItem (articleId, warehouseLocationId, quantity, rack, shelf, createBy, `create`)
                            VALUES ({$item->articleId}, {$stocktake->warehouseLocationId}, {$item->countedQuantity},
                            " . ($item->rack ? "'{$db->escape($item->rack)}'" : "NULL") . ",
                            " . ($item->shelf ? "'{$db->escape($item->shelf)}'" : "NULL") . ",
                            {$this->user->id}, " . time() . ")");

                $createdCount++;
            }
        }

        WarehouseStocktakeLogModel::log($id, 'applied_to_stock', null, [
            'appliedCount' => $appliedCount,
            'createdCount' => $createdCount,
            'appliedBy' => $this->user->name
        ]);

        self::returnJson([
            'success' => true,
            'message' => "Bestände angepasst: {$appliedCount} aktualisiert, {$createdCount} neu erstellt"
        ]);
    }

    /**
     * Export stocktake report to Excel
     */
    protected function exportReportAction() {
        $id = intval($this->request->id);
        if (!$id) {
            self::returnJson(['success' => false, 'message' => 'Keine Inventur-ID angegeben']);
            return;
        }

        $stocktake = WarehouseStocktakeModel::get($id);
        if (!$stocktake) {
            self::returnJson(['success' => false, 'message' => 'Inventur nicht gefunden']);
            return;
        }

        // Get items via direct SQL to include price and overwritten status
        $db = FronkDB::singleton();
        $result = $db->query("SELECT si.*, a.articleNumber, a.title as articleTitle, a.cheapestPurchasePrice, w.name as scannedByName
                              FROM WarehouseStocktakeItem si
                              LEFT JOIN WarehouseArticle a ON si.articleId = a.id
                              LEFT JOIN Worker w ON si.scannedBy = w.id
                              WHERE si.stocktakeId = {$id}
                              ORDER BY si.`create` ASC");

        $rows = [];
        $totalSum = 0;

        while ($row = $result->fetch_assoc()) {
            $unitPrice = $row['cheapestPurchasePrice'] ? (float)$row['cheapestPurchasePrice'] : 0;
            $quantity = (float)$row['countedQuantity'];
            $lineTotal = $unitPrice * $quantity;
            $isOverwritten = !empty($row['overwrittenById']);

            // Skip overwritten items in calculation but show them
            if (!$isOverwritten) {
                $totalSum += $lineTotal;
            }

            $rows[] = [
                'Artikel Titel' => $row['articleTitle'] ?? 'Unbekannt',
                'Artikel Nummer' => $row['articleNumber'] ?? '',
                'Einzelpreis' => number_format($unitPrice, 2, ',', '.') . ' €',
                'Anzahl' => $quantity,
                'Gesamtsumme' => number_format($lineTotal, 2, ',', '.') . ' €',
                'Gescannt am' => $row['scannedAt'] ? date('d.m.Y H:i', $row['scannedAt']) : '',
                'Gescannt von' => $row['scannedByName'] ?? '',
                'Status' => $isOverwritten ? 'Überschrieben' : '',
            ];
        }

        // Add summary row
        $rows[] = [
            'Artikel Titel' => '',
            'Artikel Nummer' => '',
            'Einzelpreis' => '',
            'Anzahl' => 'SUMME:',
            'Gesamtsumme' => number_format($totalSum, 2, ',', '.') . ' €',
            'Gescannt am' => '',
            'Gescannt von' => '',
            'Status' => '',
        ];

        $filename = "Inventur_{$stocktake->stocktakeNumber}_" . date('Y-m-d') . ".csv";
        $csv = Helper::arrayToCsv($rows);

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        echo "\xEF\xBB\xBF"; // UTF-8 BOM
        echo $csv;
        exit;
    }

    /**
     * Get history for a stocktake
     */
    protected function getHistoryAction() {
        $this->prepareCrudConfig();
        self::returnJson((new WarehouseHistoryController)->getHistory($this->request->id, $this->mod, $this->columns));
    }

    /**
     * Get logs for a stocktake
     */
    protected function getLogsAction() {
        $id = intval($this->request->id);
        if (!$id) {
            self::returnJson(['success' => false, 'message' => 'Keine Inventur-ID angegeben']);
            return;
        }

        $logs = WarehouseStocktakeLogModel::getLogsForStocktake($id);
        $formattedLogs = [];

        foreach ($logs as $log) {
            $user = UserModel::get($log->userId);
            $formattedLogs[] = [
                'id' => $log->id,
                'action' => $log->action,
                'details' => $log->details ? json_decode($log->details, true) : null,
                'userName' => $user ? $user->name : 'Unbekannt',
                'create' => date('d.m.Y H:i:s', $log->create),
            ];
        }

        self::returnJson(['success' => true, 'logs' => $formattedLogs]);
    }
}