exec("CREATE TABLE IF NOT EXISTS customers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(160) NOT NULL, branch_name VARCHAR(160) NOT NULL, phone VARCHAR(40) DEFAULT NULL, status VARCHAR(20) NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"); $pdo->exec("CREATE TABLE IF NOT EXISTS products ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(80) NOT NULL UNIQUE, name VARCHAR(160) NOT NULL, category VARCHAR(20) NOT NULL, unit VARCHAR(40) NOT NULL, stock_qty DECIMAL(12,2) NOT NULL DEFAULT 0, cost_price DECIMAL(12,2) NOT NULL DEFAULT 0, sale_price DECIMAL(12,2) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"); $pdo->exec("CREATE TABLE IF NOT EXISTS customer_prices ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, special_price DECIMAL(12,2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_customer_product (customer_id, product_id), CONSTRAINT fk_customer_prices_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE, CONSTRAINT fk_customer_prices_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"); $pdo->exec("CREATE TABLE IF NOT EXISTS sales_orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_number VARCHAR(40) NOT NULL UNIQUE, customer_id INT UNSIGNED NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'confirmed', subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, expected_profit DECIMAL(12,2) NOT NULL DEFAULT 0, notes TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_sales_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"); $pdo->exec("CREATE TABLE IF NOT EXISTS sales_order_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sales_order_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, qty DECIMAL(12,2) NOT NULL, unit_price DECIMAL(12,2) NOT NULL, line_total DECIMAL(12,2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_sales_items_order FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id) ON DELETE CASCADE, CONSTRAINT fk_sales_items_product FOREIGN KEY (product_id) REFERENCES products(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"); $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_movements ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED NOT NULL, movement_type VARCHAR(30) NOT NULL, qty_change DECIMAL(12,2) NOT NULL, stock_before DECIMAL(12,2) NOT NULL DEFAULT 0, stock_after DECIMAL(12,2) NOT NULL DEFAULT 0, reference_code VARCHAR(80) DEFAULT NULL, notes VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_inventory_product_created (product_id, created_at), CONSTRAINT fk_inventory_movements_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"); seed_demo_data($pdo); $booted = true; } function seed_demo_data(PDO $pdo): void { $customerCount = (int) $pdo->query("SELECT COUNT(*) FROM customers")->fetchColumn(); if ($customerCount === 0) { $stmt = $pdo->prepare('INSERT INTO customers (name, branch_name, phone, status) VALUES (?, ?, ?, ?)'); $rows = [ ['شركة السهول التجارية', 'فرع الرياض', '+966500000101', 'active'], ['مؤسسة الواجهة الذكية', 'فرع جدة', '+966500000202', 'active'], ['شركة المدى للتوزيع', 'فرع الدمام', '+966500000303', 'active'], ]; foreach ($rows as $row) { $stmt->execute($row); } } $productCount = (int) $pdo->query("SELECT COUNT(*) FROM products")->fetchColumn(); if ($productCount === 0) { $stmt = $pdo->prepare('INSERT INTO products (sku, name, category, unit, stock_qty, cost_price, sale_price) VALUES (?, ?, ?, ?, ?, ?, ?)'); $rows = [ ['FG-101', 'منظف أرضيات 5 لتر', 'finished', 'كرتون', 84, 18.00, 24.00], ['FG-102', 'معقم يد 500 مل', 'finished', 'كرتون', 52, 12.50, 17.00], ['FG-103', 'صابون سائل 1 لتر', 'finished', 'كرتون', 38, 10.00, 14.50], ['RM-201', 'مادة فعالة A', 'raw', 'برميل', 120, 7.50, 0.00], ['RM-202', 'عبوات فارغة 500 مل', 'raw', 'رتبة', 360, 1.20, 0.00], ]; foreach ($rows as $row) { $stmt->execute($row); } } $priceCount = (int) $pdo->query("SELECT COUNT(*) FROM customer_prices")->fetchColumn(); if ($priceCount === 0) { $customerIds = $pdo->query('SELECT id, name FROM customers ORDER BY id')->fetchAll(); $productIds = $pdo->query("SELECT id, sku FROM products WHERE category = 'finished' ORDER BY id")->fetchAll(); $customerMap = []; foreach ($customerIds as $row) { $customerMap[$row['name']] = (int) $row['id']; } $productMap = []; foreach ($productIds as $row) { $productMap[$row['sku']] = (int) $row['id']; } $stmt = $pdo->prepare('INSERT INTO customer_prices (customer_id, product_id, special_price) VALUES (?, ?, ?)'); $rows = [ [$customerMap['شركة السهول التجارية'], $productMap['FG-101'], 22.50], [$customerMap['شركة السهول التجارية'], $productMap['FG-102'], 15.90], [$customerMap['مؤسسة الواجهة الذكية'], $productMap['FG-102'], 16.10], [$customerMap['مؤسسة الواجهة الذكية'], $productMap['FG-103'], 13.60], [$customerMap['شركة المدى للتوزيع'], $productMap['FG-101'], 23.20], [$customerMap['شركة المدى للتوزيع'], $productMap['FG-103'], 13.80], ]; foreach ($rows as $row) { $stmt->execute($row); } } } function csrf_token(): string { if (empty($_SESSION['csrf_token'])) { $_SESSION['csrf_token'] = bin2hex(random_bytes(32)); } return $_SESSION['csrf_token']; } function verify_csrf(?string $token): bool { return is_string($token) && isset($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], $token); } function flash(string $type, string $message): void { $_SESSION['flash'] = ['type' => $type, 'message' => $message]; } function consume_flash(): ?array { if (!isset($_SESSION['flash'])) { return null; } $flash = $_SESSION['flash']; unset($_SESSION['flash']); return $flash; } function project_name(): string { return trim((string) ($_SERVER['PROJECT_NAME'] ?? 'NexusERP')) ?: 'NexusERP'; } function render_header(string $pageTitle, string $active = 'dashboard'): void { $projectDescription = (string) ($_SERVER['PROJECT_DESCRIPTION'] ?? 'ERP dashboard and sales operations workspace'); $projectImageUrl = (string) ($_SERVER['PROJECT_IMAGE_URL'] ?? ''); $fullTitle = $pageTitle . ' | ' . project_name(); $nav = [ 'dashboard' => ['href' => 'index.php', 'label' => 'لوحة التحكم'], 'customers' => ['href' => 'customers.php', 'label' => 'العملاء'], 'products' => ['href' => 'products.php', 'label' => 'المخزون'], 'sales' => ['href' => 'sales.php', 'label' => 'أوامر البيع'], ]; $cssVersion = file_exists(__DIR__ . '/assets/css/custom.css') ? (string) filemtime(__DIR__ . '/assets/css/custom.css') : (string) time(); echo ''; echo ''; echo ''; echo ''; echo ''; echo '' . h($fullTitle) . ''; echo ''; echo ''; echo ''; echo ''; echo ''; if ($projectImageUrl !== '') { echo ''; echo ''; } echo ''; echo ''; echo ''; echo ''; echo '
'; echo '
'; echo '
'; echo '
'; echo '
'; echo '
NE
'; echo '
'; echo '
Single-company ERP
'; echo '
' . h(project_name()) . '
'; echo '
'; echo '
'; echo '
'; echo 'Admin Demo'; echo 'Health'; echo '
'; echo '
'; echo ''; echo '
'; echo '
'; echo '
'; echo '
'; } function render_footer(): void { $flash = consume_flash(); $jsVersion = file_exists(__DIR__ . '/assets/js/main.js') ? (string) filemtime(__DIR__ . '/assets/js/main.js') : (string) time(); if ($flash) { echo '
'; echo '
'; } echo '
'; echo '
'; echo ''; echo '
'; echo ''; echo ''; echo ''; } function fetch_dashboard_metrics(): array { $pdo = db(); return [ 'customers' => (int) $pdo->query('SELECT COUNT(*) FROM customers')->fetchColumn(), 'products' => (int) $pdo->query('SELECT COUNT(*) FROM products')->fetchColumn(), 'finished_stock' => (float) $pdo->query("SELECT COALESCE(SUM(stock_qty),0) FROM products WHERE category = 'finished'")->fetchColumn(), 'raw_stock' => (float) $pdo->query("SELECT COALESCE(SUM(stock_qty),0) FROM products WHERE category = 'raw'")->fetchColumn(), 'low_stock_products' => (int) $pdo->query("SELECT COUNT(*) FROM products WHERE stock_qty <= 40")->fetchColumn(), 'orders_today' => (int) $pdo->query('SELECT COUNT(*) FROM sales_orders WHERE DATE(created_at) = CURDATE()')->fetchColumn(), 'sales_today' => (float) $pdo->query('SELECT COALESCE(SUM(subtotal),0) FROM sales_orders WHERE DATE(created_at) = CURDATE()')->fetchColumn(), 'profit_today' => (float) $pdo->query('SELECT COALESCE(SUM(expected_profit),0) FROM sales_orders WHERE DATE(created_at) = CURDATE()')->fetchColumn(), ]; } function fetch_sales_trend(): array { $pdo = db(); $stmt = $pdo->query("SELECT DATE(created_at) AS order_date, COUNT(*) AS order_count, COALESCE(SUM(subtotal),0) AS revenue FROM sales_orders WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) GROUP BY DATE(created_at) ORDER BY order_date ASC"); $rows = $stmt->fetchAll(); $indexed = []; foreach ($rows as $row) { $indexed[$row['order_date']] = $row; } $trend = []; for ($i = 6; $i >= 0; $i--) { $date = date('Y-m-d', strtotime('-' . $i . ' days')); $trend[] = [ 'date' => $date, 'label' => date('d M', strtotime($date)), 'order_count' => isset($indexed[$date]) ? (int) $indexed[$date]['order_count'] : 0, 'revenue' => isset($indexed[$date]) ? (float) $indexed[$date]['revenue'] : 0.0, ]; } return $trend; } function fetch_recent_orders(int $limit = 8): array { $pdo = db(); $stmt = $pdo->prepare("SELECT so.id, so.order_number, so.status, so.subtotal, so.expected_profit, so.created_at, c.name AS customer_name, c.branch_name FROM sales_orders so INNER JOIN customers c ON c.id = so.customer_id ORDER BY so.created_at DESC, so.id DESC LIMIT :limit"); $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(); } function fetch_products(): array { $pdo = db(); return $pdo->query('SELECT * FROM products ORDER BY category ASC, name ASC')->fetchAll(); } function fetch_product_by_id(int $productId): ?array { $pdo = db(); $stmt = $pdo->prepare('SELECT * FROM products WHERE id = ? LIMIT 1'); $stmt->execute([$productId]); $product = $stmt->fetch(); return $product ?: null; } function fetch_inventory_movements(int $limit = 12): array { $pdo = db(); $stmt = $pdo->prepare("SELECT im.*, p.sku, p.name AS product_name, p.unit FROM inventory_movements im INNER JOIN products p ON p.id = im.product_id ORDER BY im.created_at DESC, im.id DESC LIMIT :limit"); $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(); } function fetch_product_options(): array { $pdo = db(); $stmt = $pdo->query('SELECT id, sku, name, category, unit, stock_qty FROM products ORDER BY name ASC'); return $stmt->fetchAll(); } function fetch_customers_with_catalog(): array { $pdo = db(); $customers = $pdo->query('SELECT * FROM customers ORDER BY created_at DESC, id DESC')->fetchAll(); $stmt = $pdo->query("SELECT cp.customer_id, p.sku, p.name, p.unit, cp.special_price FROM customer_prices cp INNER JOIN products p ON p.id = cp.product_id ORDER BY p.name ASC"); $catalogRows = $stmt->fetchAll(); $catalog = []; foreach ($catalogRows as $row) { $catalog[(int) $row['customer_id']][] = $row; } foreach ($customers as &$customer) { $customer['catalog'] = $catalog[(int) $customer['id']] ?? []; } unset($customer); return $customers; } function fetch_finished_products_for_pricing(): array { $pdo = db(); $stmt = $pdo->query("SELECT id, sku, name, unit, stock_qty, sale_price FROM products WHERE category = 'finished' ORDER BY name ASC"); return $stmt->fetchAll(); } function normalize_product_category(string $category): ?string { $category = trim($category); return in_array($category, ['finished', 'raw'], true) ? $category : null; } function create_product(array $input): array { $sku = strtoupper(trim((string) ($input['sku'] ?? ''))); $name = trim((string) ($input['name'] ?? '')); $category = normalize_product_category((string) ($input['category'] ?? '')); $unit = trim((string) ($input['unit'] ?? '')); $stockQty = round((float) ($input['stock_qty'] ?? 0), 2); $costPrice = round((float) ($input['cost_price'] ?? 0), 2); $salePrice = round((float) ($input['sale_price'] ?? 0), 2); if ($sku === '' || $name === '' || $unit === '' || $category === null) { return ['success' => false, 'message' => 'SKU واسم الصنف والفئة ووحدة القياس مطلوبة.']; } if ($stockQty < 0 || $costPrice < 0 || $salePrice < 0) { return ['success' => false, 'message' => 'قيم المخزون والأسعار يجب أن تكون صفرًا أو أكبر.']; } $pdo = db(); try { $pdo->beginTransaction(); $stmt = $pdo->prepare('INSERT INTO products (sku, name, category, unit, stock_qty, cost_price, sale_price) VALUES (?, ?, ?, ?, ?, ?, ?)'); $stmt->execute([$sku, $name, $category, $unit, $stockQty, $costPrice, $salePrice]); $productId = (int) $pdo->lastInsertId(); if ($stockQty > 0) { record_inventory_movement($pdo, $productId, 'opening', $stockQty, 0.0, $stockQty, $sku, 'رصيد افتتاحي عند إنشاء الصنف'); } $pdo->commit(); return ['success' => true, 'message' => 'تم إنشاء الصنف بنجاح.', 'product_id' => $productId]; } catch (PDOException $e) { if ($pdo->inTransaction()) { $pdo->rollBack(); } if ((string) $e->getCode() === '23000') { return ['success' => false, 'message' => 'SKU مستخدم من قبل. اختر رمزًا مختلفًا.']; } return ['success' => false, 'message' => 'تعذر إنشاء الصنف حالياً.']; } catch (Throwable $e) { if ($pdo->inTransaction()) { $pdo->rollBack(); } return ['success' => false, 'message' => 'تعذر إنشاء الصنف حالياً.']; } } function update_product(int $productId, array $input): array { $product = fetch_product_by_id($productId); if (!$product) { return ['success' => false, 'message' => 'الصنف المطلوب غير موجود.']; } $sku = strtoupper(trim((string) ($input['sku'] ?? ''))); $name = trim((string) ($input['name'] ?? '')); $category = normalize_product_category((string) ($input['category'] ?? '')); $unit = trim((string) ($input['unit'] ?? '')); $costPrice = round((float) ($input['cost_price'] ?? 0), 2); $salePrice = round((float) ($input['sale_price'] ?? 0), 2); if ($sku === '' || $name === '' || $unit === '' || $category === null) { return ['success' => false, 'message' => 'SKU واسم الصنف والفئة ووحدة القياس مطلوبة.']; } if ($costPrice < 0 || $salePrice < 0) { return ['success' => false, 'message' => 'الأسعار يجب أن تكون صفرًا أو أكبر.']; } $pdo = db(); try { $stmt = $pdo->prepare('UPDATE products SET sku = ?, name = ?, category = ?, unit = ?, cost_price = ?, sale_price = ? WHERE id = ?'); $stmt->execute([$sku, $name, $category, $unit, $costPrice, $salePrice, $productId]); return ['success' => true, 'message' => 'تم تحديث بيانات الصنف بنجاح.']; } catch (PDOException $e) { if ((string) $e->getCode() === '23000') { return ['success' => false, 'message' => 'SKU مستخدم من قبل. اختر رمزًا مختلفًا.']; } return ['success' => false, 'message' => 'تعذر تحديث الصنف حالياً.']; } } function record_inventory_movement(PDO $pdo, int $productId, string $movementType, float $qtyChange, float $stockBefore, float $stockAfter, ?string $referenceCode = null, ?string $notes = null): void { $stmt = $pdo->prepare('INSERT INTO inventory_movements (product_id, movement_type, qty_change, stock_before, stock_after, reference_code, notes) VALUES (?, ?, ?, ?, ?, ?, ?)'); $stmt->execute([$productId, $movementType, round($qtyChange, 2), round($stockBefore, 2), round($stockAfter, 2), $referenceCode, $notes]); } function adjust_product_stock(int $productId, string $direction, float $qty, string $reason, string $notes): array { $direction = trim($direction); $reason = trim($reason); $notes = trim($notes); if (!in_array($direction, ['add', 'subtract'], true) || $qty <= 0) { return ['success' => false, 'message' => 'أدخل عملية ضبط صحيحة وكمية أكبر من صفر.']; } $product = fetch_product_by_id($productId); if (!$product) { return ['success' => false, 'message' => 'الصنف المطلوب غير موجود.']; } $pdo = db(); try { $pdo->beginTransaction(); $lockStmt = $pdo->prepare('SELECT id, sku, stock_qty FROM products WHERE id = ? FOR UPDATE'); $lockStmt->execute([$productId]); $locked = $lockStmt->fetch(); if (!$locked) { throw new RuntimeException('Product not found during lock.'); } $stockBefore = (float) $locked['stock_qty']; $delta = $direction === 'add' ? $qty : -$qty; $stockAfter = round($stockBefore + $delta, 2); if ($stockAfter < 0) { if ($pdo->inTransaction()) { $pdo->rollBack(); } return ['success' => false, 'message' => 'لا يمكن خصم كمية أكبر من المخزون الحالي.']; } $updateStmt = $pdo->prepare('UPDATE products SET stock_qty = ? WHERE id = ?'); $updateStmt->execute([$stockAfter, $productId]); $movementType = $direction === 'add' ? 'adjustment_in' : 'adjustment_out'; $movementNotes = $reason !== '' ? $reason : 'ضبط يدوي'; if ($notes !== '') { $movementNotes .= ' — ' . $notes; } record_inventory_movement($pdo, $productId, $movementType, $delta, $stockBefore, $stockAfter, $locked['sku'], $movementNotes); $pdo->commit(); return ['success' => true, 'message' => 'تم تحديث المخزون للصنف بنجاح.']; } catch (Throwable $e) { if ($pdo->inTransaction()) { $pdo->rollBack(); } return ['success' => false, 'message' => 'تعذر ضبط المخزون حالياً.']; } } function create_customer_with_prices(string $name, string $branchName, string $phone, array $selectedProducts, array $priceInputs): array { $name = trim($name); $branchName = trim($branchName); $phone = trim($phone); if ($name === '' || $branchName === '') { return ['success' => false, 'message' => 'اسم العميل واسم الفرع مطلوبان.']; } $productIds = []; foreach ($selectedProducts as $productId) { $id = (int) $productId; if ($id > 0) { $productIds[$id] = $id; } } if (!$productIds) { return ['success' => false, 'message' => 'اختر صنفاً واحداً على الأقل وحدد له سعراً خاصاً.']; } $pricingRows = []; foreach ($productIds as $productId) { $rawPrice = isset($priceInputs[$productId]) ? trim((string) $priceInputs[$productId]) : ''; if ($rawPrice === '' || !is_numeric($rawPrice)) { return ['success' => false, 'message' => 'أدخل سعراً صحيحاً لكل صنف تم اختياره.']; } $price = round((float) $rawPrice, 2); if ($price <= 0) { return ['success' => false, 'message' => 'السعر الخاص يجب أن يكون أكبر من صفر.']; } $pricingRows[] = ['product_id' => $productId, 'special_price' => $price]; } $pdo = db(); try { $pdo->beginTransaction(); $customerStmt = $pdo->prepare('INSERT INTO customers (name, branch_name, phone, status) VALUES (?, ?, ?, ?)'); $customerStmt->execute([$name, $branchName, $phone !== '' ? $phone : null, 'active']); $customerId = (int) $pdo->lastInsertId(); $priceStmt = $pdo->prepare('INSERT INTO customer_prices (customer_id, product_id, special_price) VALUES (?, ?, ?)'); foreach ($pricingRows as $row) { $priceStmt->execute([$customerId, $row['product_id'], $row['special_price']]); } $pdo->commit(); return [ 'success' => true, 'message' => 'تمت إضافة العميل وربط الأسعار الخاصة به بنجاح.', 'customer_id' => $customerId, ]; } catch (Throwable $e) { if ($pdo->inTransaction()) { $pdo->rollBack(); } return ['success' => false, 'message' => 'تعذر حفظ العميل حالياً.']; } } function fetch_sales_form_catalog(): array { $pdo = db(); $stmt = $pdo->query("SELECT c.id AS customer_id, c.name AS customer_name, c.branch_name, p.id AS product_id, p.sku, p.name AS product_name, p.unit, p.stock_qty, p.cost_price, p.sale_price, cp.special_price FROM customer_prices cp INNER JOIN customers c ON c.id = cp.customer_id INNER JOIN products p ON p.id = cp.product_id ORDER BY c.name ASC, p.name ASC"); $catalog = []; foreach ($stmt->fetchAll() as $row) { $customerId = (int) $row['customer_id']; if (!isset($catalog[$customerId])) { $catalog[$customerId] = [ 'customer_id' => $customerId, 'customer_name' => $row['customer_name'], 'branch_name' => $row['branch_name'], 'items' => [], ]; } $catalog[$customerId]['items'][] = [ 'product_id' => (int) $row['product_id'], 'sku' => $row['sku'], 'product_name' => $row['product_name'], 'unit' => $row['unit'], 'stock_qty' => (float) $row['stock_qty'], 'cost_price' => (float) $row['cost_price'], 'sale_price' => (float) $row['sale_price'], 'special_price' => (float) $row['special_price'], ]; } return $catalog; } function format_money(float $value): string { return number_format($value, 2) . ' ر.س'; } function format_qty(float $value): string { return rtrim(rtrim(number_format($value, 2, '.', ''), '0'), '.'); } function next_order_number(PDO $pdo): string { $count = (int) $pdo->query('SELECT COUNT(*) FROM sales_orders WHERE DATE(created_at) = CURDATE()')->fetchColumn(); return 'SO-' . date('Ymd') . '-' . str_pad((string) ($count + 1), 3, '0', STR_PAD_LEFT); } function create_sales_order(int $customerId, int $productId, float $qty, string $notes): array { $pdo = db(); if ($qty <= 0) { return ['success' => false, 'message' => 'الكمية يجب أن تكون أكبر من صفر.']; } $stmt = $pdo->prepare("SELECT c.id AS customer_id, c.name AS customer_name, c.branch_name, p.id AS product_id, p.name AS product_name, p.sku, p.unit, p.stock_qty, p.cost_price, cp.special_price FROM customer_prices cp INNER JOIN customers c ON c.id = cp.customer_id INNER JOIN products p ON p.id = cp.product_id WHERE c.id = :customer_id AND p.id = :product_id LIMIT 1"); $stmt->execute([ ':customer_id' => $customerId, ':product_id' => $productId, ]); $row = $stmt->fetch(); if (!$row) { return ['success' => false, 'message' => 'هذا الصنف غير متاح لهذا العميل أو لا يوجد سعر خاص له.']; } $unitPrice = (float) $row['special_price']; $lineTotal = round($qty * $unitPrice, 2); $expectedProfit = round($lineTotal - ($qty * (float) $row['cost_price']), 2); $orderNumber = next_order_number($pdo); try { $pdo->beginTransaction(); $stockLock = $pdo->prepare('SELECT stock_qty FROM products WHERE id = ? FOR UPDATE'); $stockLock->execute([$productId]); $stockRow = $stockLock->fetch(); if (!$stockRow) { throw new RuntimeException('الصنف غير موجود أثناء الحفظ.'); } $stockBefore = (float) $stockRow['stock_qty']; if ($qty > $stockBefore) { if ($pdo->inTransaction()) { $pdo->rollBack(); } return ['success' => false, 'message' => 'الكمية المطلوبة أكبر من المخزون المتاح حالياً.']; } $stockAfter = round($stockBefore - $qty, 2); $orderStmt = $pdo->prepare('INSERT INTO sales_orders (order_number, customer_id, status, subtotal, expected_profit, notes) VALUES (?, ?, ?, ?, ?, ?)'); $orderStmt->execute([$orderNumber, $customerId, 'confirmed', $lineTotal, $expectedProfit, $notes !== '' ? $notes : null]); $orderId = (int) $pdo->lastInsertId(); $itemStmt = $pdo->prepare('INSERT INTO sales_order_items (sales_order_id, product_id, qty, unit_price, line_total) VALUES (?, ?, ?, ?, ?)'); $itemStmt->execute([$orderId, $productId, $qty, $unitPrice, $lineTotal]); $stockStmt = $pdo->prepare('UPDATE products SET stock_qty = ? WHERE id = ?'); $stockStmt->execute([$stockAfter, $productId]); record_inventory_movement( $pdo, $productId, 'sale', -$qty, $stockBefore, $stockAfter, $orderNumber, 'خصم تلقائي من أمر البيع ' . $orderNumber ); $pdo->commit(); return [ 'success' => true, 'message' => 'تم إنشاء أمر البيع ' . $orderNumber . ' وتحديث المخزون تلقائياً.', 'order_id' => $orderId, 'order_number' => $orderNumber, ]; } catch (Throwable $e) { if ($pdo->inTransaction()) { $pdo->rollBack(); } return ['success' => false, 'message' => 'حدث خطأ أثناء حفظ أمر البيع.']; } } function fetch_order_detail(int $orderId): ?array { $pdo = db(); $stmt = $pdo->prepare("SELECT so.*, c.name AS customer_name, c.branch_name, c.phone FROM sales_orders so INNER JOIN customers c ON c.id = so.customer_id WHERE so.id = :id LIMIT 1"); $stmt->execute([':id' => $orderId]); $order = $stmt->fetch(); if (!$order) { return null; } $itemStmt = $pdo->prepare("SELECT soi.qty, soi.unit_price, soi.line_total, p.name AS product_name, p.sku, p.unit, p.cost_price FROM sales_order_items soi INNER JOIN products p ON p.id = soi.product_id WHERE soi.sales_order_id = :id"); $itemStmt->execute([':id' => $orderId]); $items = $itemStmt->fetchAll(); return ['order' => $order, 'items' => $items]; }