File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090028/Shop/app/Models/Settlement.php

Back
<?php /** * Settlement Model * Maps to `settlements` table * Handles seller payout cycles, commission deductions, and transfer records / require_once __DIR__ . '/../Core/Model.php'; class Settlement extends Model { protected string $table = 'settlements'; public function getAdminSettlements(array $filters = [], int $limit = 20, int $offset = 0): array { [$whereSql, $params] = $this->buildFilters($filters); $sql = "SELECT se., s.store_name, s.store_slug, s.phone as store_phone, CONCAT(u.first_name, ' ', u.last_name) as owner_name, u.username as owner_username, u.email as owner_email FROM `settlements` se JOIN `stores` s ON se.store_id = s.id JOIN `users` u ON s.user_id = u.id WHERE {$whereSql} ORDER BY se.created_at DESC LIMIT :limit OFFSET :offset"; $stmt = $this->db->prepare($sql); foreach ($params as $k => $v) { $stmt->bindValue($k, $v); } $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->bindValue(':offset', $offset, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(); } public function countAdminSettlements(array $filters = []): int { [$whereSql, $params] = $this->buildFilters($filters); $sql = "SELECT COUNT() FROM `settlements` se JOIN `stores` s ON se.store_id = s.id JOIN `users` u ON s.user_id = u.id WHERE {$whereSql}"; $stmt = $this->db->prepare($sql); foreach ($params as $k => $v) { $stmt->bindValue($k, $v); } $stmt->execute(); return (int)$stmt->fetchColumn(); } private function buildFilters(array $filters): array { $conditions = ['1=1']; $params = []; if (!empty($filters['status']) && $filters['status'] !== 'all') { $conditions[] = "se.payment_status = :status"; $params[':status'] = $filters['status']; } if (!empty($filters['store_id'])) { $conditions[] = "se.store_id = :store_id"; $params[':store_id'] = (int)$filters['store_id']; } if (!empty($filters['q'])) { $conditions[] = "(s.store_name LIKE :q OR s.store_slug LIKE :q OR u.username LIKE :q OR se.payment_reference LIKE :q)"; $params[':q'] = '%' . trim($filters['q']) . '%'; } if (!empty($filters['date_from'])) { $conditions[] = "se.created_at >= :date_from"; $params[':date_from'] = $filters['date_from'] . ' 00:00:00'; } if (!empty($filters['date_to'])) { $conditions[] = "se.created_at <= :date_to"; $params[':date_to'] = $filters['date_to'] . ' 23:59:59'; } return [implode(' AND ', $conditions), $params]; } public function findWithStore(int $id): ?array { $sql = "SELECT se., s.store_name, s.store_slug, s.phone as store_phone, s.address as store_address, CONCAT(u.first_name, ' ', u.last_name) as owner_name, u.username as owner_username, u.email as owner_email FROM `settlements` se JOIN `stores` s ON se.store_id = s.id JOIN `users` u ON s.user_id = u.id WHERE se.id = :id LIMIT 1"; return $this->fetchOne($sql, [':id' => $id]); } public function getSettlementStats(): array { $sql = "SELECT COUNT() as total, SUM(CASE WHEN payment_status = 'pending' THEN 1 ELSE 0 END) as pending_count, SUM(CASE WHEN payment_status = 'pending' THEN seller_receives ELSE 0 END) as pending_amount, SUM(CASE WHEN payment_status = 'processing' THEN 1 ELSE 0 END) as processing_count, SUM(CASE WHEN payment_status = 'completed' THEN 1 ELSE 0 END) as completed_count, SUM(CASE WHEN payment_status = 'completed' THEN seller_receives ELSE 0 END) as completed_amount, SUM(total_sales) as total_sales, SUM(commission_deduction) as total_commission, SUM(seller_receives) as total_seller_receives FROM `settlements`"; return $this->fetchOne($sql) ?: []; } public function process(int $id, string $status, ?string $paymentReference, ?string $note): bool { $data = [ 'payment_status' => $status, 'updated_at' => date('Y-m-d H:i:s') ]; if ($paymentReference !== null) { $data['payment_reference'] = $paymentReference; } if ($note !== null) { $data['note'] = $note; } if ($status === 'completed') { $data['transfer_date'] = date('Y-m-d H:i:s'); } return $this->update($id, $data); } /* * Calculate completed unsettled order totals per store */ public function getUnsettledStoreBalances(): array { $sql = "SELECT s.id as store_id, s.store_name, s.store_slug, CONCAT(u.first_name, ' ', u.last_name) as owner_name, u.username as owner_username, COUNT(o.id) as completed_orders, SUM(o.total_amount) as gross_sales, SUM(o.commission_amount) as total_commission, SUM(o.net_revenue) as net_receivable FROM `stores` s JOIN `users` u ON s.user_id = u.id JOIN `orders` o ON o.store_id = s.id WHERE o.order_status = 'completed' AND o.deleted_at IS NULL GROUP BY s.id ORDER BY net_receivable DESC"; return $this->fetchAll($sql); } }

| ver. 1.4 | Github | . | PHP 7.4.33 | Generation time: 0.24 | proxy | phpinfo | Settings