File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090029/Project SME/db.php

Back
<?php /** * CMTC Tech Solution - Database Connection * Database: smart_dine_cloud_db * Driver: PDO (Prepared Statements enabled) / if (session_status() === PHP_SESSION_NONE) { session_start(); } // Load .env file if it exists if (file_exists(__DIR__ . '/.env')) { $lines = file(__DIR__ . '/.env', FILE_IGNORE_NEW_LINES \| FILE_SKIP_EMPTY_LINES); foreach ($lines as $line) { $line = trim($line); if (empty($line) \|\| strpos($line, '#') === 0) { continue; } if (strpos($line, '=') !== false) { list($name, $value) = explode('=', $line, 2); $name = trim($name); $value = trim($value); // Remove optional surrounding quotes $value = preg_replace('/^[\'"]\|[\'"]$/', '', $value); if (!array_key_exists($name, $_SERVER) && !array_key_exists($name, $_ENV)) { putenv("{$name}={$value}"); $_ENV[$name] = $value; $_SERVER[$name] = $value; } } } } $db_host = getenv('DB_HOST') ?: 'localhost'; $db_port = getenv('DB_PORT') ?: '3306'; / $db_user = getenv('DB_USER') !== false ? getenv('DB_USER') : 'root'; $db_pass = getenv('DB_PASS') !== false ? getenv('DB_PASS') : ''; $db_name = getenv('DB_NAME') ?: 'smart_dine_cloud_db'; / $db_user = getenv('DB_USER') !== false ? getenv('DB_USER') : 'sme_os'; $db_pass = getenv('DB_PASS') !== false ? getenv('DB_PASS') : '@smetech69'; $db_name = getenv('DB_NAME') ?: 'sme_os'; $db_debug = filter_var(getenv('DB_DEBUG'), FILTER_VALIDATE_BOOLEAN); try { $pdo = new PDO( "mysql:host={$db_host};port={$db_port};dbname={$db_name};charset=utf8mb4", $db_user, $db_pass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ] ); // Explicitly enforce UTF-8 connection encoding to resolve scrambled Thai text in local environments $pdo->exec("SET NAMES utf8mb4"); // 1. plans Table $pdo->exec("CREATE TABLE IF NOT EXISTS `plans` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(100) NOT NULL, `price` DECIMAL(10, 2) NOT NULL, `limit_tables` INT NOT NULL, `description` TEXT NOT NULL ) ENGINE=InnoDB;"); // 2. tenants Table $pdo->exec("CREATE TABLE IF NOT EXISTS `tenants` ( `store_id` INT AUTO_INCREMENT PRIMARY KEY, `store_name` VARCHAR(255) NOT NULL, `address` TEXT, `category` VARCHAR(100), `plan_id` INT, `status` VARCHAR(50) DEFAULT 'active', `promo_banner` VARCHAR(255), `policy_text` TEXT, `custom_logo_url` VARCHAR(255) ) ENGINE=InnoDB;"); try { $pdo->exec("ALTER TABLE `tenants` ADD COLUMN `store_banner_url` VARCHAR(255) DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `tenants` ADD COLUMN `phone` VARCHAR(50) DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `tenants` ADD COLUMN `line_id` VARCHAR(100) DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `tenants` ADD COLUMN `trial_ends_at` DATETIME DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `shops` ADD COLUMN `trial_ends_at` DATETIME DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("UPDATE `tenants` SET `trial_ends_at` = DATE_ADD(NOW(), INTERVAL CASE WHEN plan_id = 3 THEN 7 ELSE 14 END DAY) WHERE `trial_ends_at` IS NULL AND `status` != 'active'"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `menus` ADD COLUMN `description` TEXT DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `menus` ADD COLUMN `spice_options` VARCHAR(255) DEFAULT 'เผ็ดปกติ,ไม่เผ็ด,เผ็ดน้อย,เผ็ดมาก'"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `menus` ADD COLUMN `unit` VARCHAR(50) DEFAULT 'จาน'"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `order_items` ADD COLUMN `note` VARCHAR(255) DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `order_items` ADD COLUMN `spice_level` VARCHAR(50) DEFAULT NULL"); } catch (PDOException $e) {} // 3. users Table $pdo->exec("CREATE TABLE IF NOT EXISTS `users` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `username` VARCHAR(150) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `role` ENUM('super_admin', 'store_admin') NOT NULL, `store_id` INT DEFAULT NULL, FOREIGN KEY (`store_id`) REFERENCES `tenants`(`store_id`) ON DELETE CASCADE ) ENGINE=InnoDB;"); // Add foreign key constraint to tenants plan_id if not present try { $pdo->exec("ALTER TABLE `tenants` ADD CONSTRAINT `fk_tenants_plan` FOREIGN KEY (`plan_id`) REFERENCES `plans`(`id`) ON DELETE SET NULL"); } catch (PDOException $e) { // FK probably already exists } // 4. menus Table $pdo->exec("CREATE TABLE IF NOT EXISTS `menus` ( `menu_id` INT AUTO_INCREMENT PRIMARY KEY, `store_id` INT NOT NULL, `name` VARCHAR(255) NOT NULL, `price` DECIMAL(10, 2) NOT NULL, `category` VARCHAR(100) NOT NULL, `image_url` VARCHAR(255), `is_available` TINYINT(1) DEFAULT 1, `sort_order` INT DEFAULT 0, FOREIGN KEY (`store_id`) REFERENCES `tenants`(`store_id`) ON DELETE CASCADE ) ENGINE=InnoDB;"); try { $pdo->exec("ALTER TABLE `menus` ADD COLUMN `sort_order` INT DEFAULT 0"); } catch (PDOException $e) {} // 4.1. categories Table $pdo->exec("CREATE TABLE IF NOT EXISTS `categories` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `store_id` INT NOT NULL, `name` VARCHAR(100) NOT NULL, `sort_order` INT DEFAULT 0, `is_active` TINYINT(1) DEFAULT 1, FOREIGN KEY (`store_id`) REFERENCES `tenants`(`store_id`) ON DELETE CASCADE ) ENGINE=InnoDB;"); // Auto-seed categories from existing menus if missing try { $pdo->exec("INSERT INTO `categories` (store_id, name, sort_order, is_active) SELECT DISTINCT m.store_id, m.category, 0, 1 FROM `menus` m WHERE m.category IS NOT NULL AND TRIM(m.category) != '' AND NOT EXISTS ( SELECT 1 FROM `categories` c WHERE c.store_id = m.store_id AND c.name = m.category )"); } catch (PDOException $e) {} // 5. tables_qr Table $pdo->exec("CREATE TABLE IF NOT EXISTS `tables_qr` ( `table_id` INT AUTO_INCREMENT PRIMARY KEY, `store_id` INT NOT NULL, `table_number` VARCHAR(50) NOT NULL, `qr_code_url` VARCHAR(255) NOT NULL, `qr_token` VARCHAR(64) DEFAULT NULL, FOREIGN KEY (`store_id`) REFERENCES `tenants`(`store_id`) ON DELETE CASCADE ) ENGINE=InnoDB;"); try { $pdo->exec("ALTER TABLE `tables_qr` ADD COLUMN `qr_token` VARCHAR(64) DEFAULT NULL"); } catch (PDOException $e) {} // Static QR + Guest Authenticator: track table occupancy state and current guest name try { $pdo->exec("ALTER TABLE `tables_qr` ADD COLUMN `table_status` ENUM('available','occupied') NOT NULL DEFAULT 'available'"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `tables_qr` ADD COLUMN `table_guest_name` VARCHAR(100) DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `tables_qr` ADD COLUMN `session_token` VARCHAR(64) DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `orders` ADD COLUMN `payment_method` VARCHAR(50) DEFAULT 'cash'"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `orders` ADD COLUMN `is_paid` TINYINT(1) DEFAULT 0"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `orders` ADD COLUMN `slip_url` VARCHAR(255) DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `orders` ADD COLUMN `voucher` VARCHAR(50) DEFAULT NULL"); } catch (PDOException $e) {} try { $pdo->exec("ALTER TABLE `orders` MODIFY COLUMN `voucher` VARCHAR(50) NULL"); } catch (PDOException $e) {} // Create orders table $pdo->exec("CREATE TABLE IF NOT EXISTS `orders` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `store_id` INT NOT NULL, `table_number` VARCHAR(50) NOT NULL, `status` ENUM('pending', 'preparing', 'ready', 'served', 'completed', 'cancelled') DEFAULT 'pending', `payment_method` VARCHAR(50) DEFAULT 'cash', `is_paid` TINYINT(1) DEFAULT 0, `slip_url` VARCHAR(255) DEFAULT NULL, `voucher` VARCHAR(50) DEFAULT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`store_id`) REFERENCES `tenants`(`store_id`) ON DELETE CASCADE ) ENGINE=InnoDB;"); // Create order_items table $pdo->exec("CREATE TABLE IF NOT EXISTS `order_items` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `menu_id` INT DEFAULT NULL, `quantity` INT NOT NULL DEFAULT 1, `price` DECIMAL(10, 2) NOT NULL, FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE, FOREIGN KEY (`menu_id`) REFERENCES `menus`(`menu_id`) ON DELETE SET NULL ) ENGINE=InnoDB;"); // 6. payments Table $pdo->exec("CREATE TABLE IF NOT EXISTS `payments` ( `payment_id` INT AUTO_INCREMENT PRIMARY KEY, `store_id` INT NOT NULL, `plan_id` INT NOT NULL, `amount` DECIMAL(10, 2) NOT NULL, `slip_url` VARCHAR(255) DEFAULT NULL, `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending', `note` TEXT DEFAULT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`store_id`) REFERENCES `tenants`(`store_id`) ON DELETE CASCADE, FOREIGN KEY (`plan_id`) REFERENCES `plans`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB;"); // Seed default plans if empty $checkPlans = $pdo->query("SELECT COUNT() FROM `plans`")->fetchColumn(); if ($checkPlans == 0) { $pdo->exec("INSERT INTO `plans` (id, name, price, limit_tables, description) VALUES (1, 'Starter', 490.00, 10, 'เหมาะสำหรับร้านเริ่มต้นขนาดเล็ก พร้อมระบบจัดการโต๊ะและคิวพื้นฐาน'), (2, 'Standard', 990.00, 30, 'เหมาะสำหรับร้านขนาดกลาง บริหารจัดการระบบอาหารและคิวได้คล่องตัวขึ้น'), (3, 'Premium', 1590.00, 100, 'ร้านค้าขนาดใหญ่ บริหารโต๊ะ บุฟเฟ่ต์ และเครื่องมือการตลาดครบครัน')"); } // Seed default super admin user if empty $checkSuperAdmin = $pdo->query("SELECT COUNT() FROM `users` WHERE `role` = 'super_admin'")->fetchColumn(); if ($checkSuperAdmin == 0) { $hashed_admin_pass = password_hash('admin1234', PASSWORD_DEFAULT); $stmt_sa = $pdo->prepare("INSERT INTO `users` (username, password, role, store_id) VALUES ('admin', :password, 'super_admin', NULL)"); $stmt_sa->execute([':password' => $hashed_admin_pass]); } // Seed default demo tenant and store admin if empty $checkTenants = $pdo->query("SELECT COUNT() FROM `tenants`")->fetchColumn(); if ($checkTenants == 0) { $pdo->exec("INSERT INTO `tenants` (store_id, store_name, address, category, plan_id, status, promo_banner, policy_text, custom_logo_url) VALUES (1, 'ร้านกะเพรา CMTC Tech', 'วิทยาลัยเทคนิคเชียงใหม่ อาคาร 3 ชั้น 1', 'อาหารตามสั่ง', 2, 'active', 'แถมฟรีไข่ดาวทุกเมนูเมื่อสั่งผ่านเว็บ!', 'การยกเลิกออเดอร์ต้องแจ้งพนักงานภายใน 3 นาทีหลังการสั่งซื้อ', 'https://upload.wikimedia.org/wikipedia/th/thumb/b/b5/Vocational_Education_Commission_logo.svg/200px-Vocational_Education_Commission_logo.svg.png')"); $hashed_store_pass = password_hash('store1234', PASSWORD_DEFAULT); $stmt_st = $pdo->prepare("INSERT INTO `users` (username, password, role, store_id) VALUES ('store1', :password, 'store_admin', 1)"); $stmt_st->execute([':password' => $hashed_store_pass]); // Seed default menus for tenant 1 $pdo->exec("INSERT INTO `menus` (store_id, name, price, category, image_url, is_available) VALUES (1, 'ข้าวกะเพราหมูสับไข่ดาว (สูตรดั้งเดิม)', 50.00, 'อาหารจานเดียว', 'https://images.unsplash.com/photo-1512058564366-18510be2db19?w=600&auto=format&fit=crop&q=60', 1), (1, 'ชาไทยเย็นสูตรเข้มข้น CMTC', 30.00, 'เครื่องดื่ม', 'https://images.unsplash.com/photo-1544787219-7f47ccb76574?w=600&auto=format&fit=crop&q=60', 1)"); // Seed default tables for tenant 1 — static QR URLs (no token in URL; physical stickers never need reprinting) $pdo->exec("INSERT INTO `tables_qr` (store_id, table_number, qr_code_url, table_status) VALUES (1, 'Table 1', 'menu.php?store_id=1&table=1', 'available'), (1, 'Table 2', 'menu.php?store_id=1&table=2', 'available')"); } } catch (PDOException $e) { error_log("Database Connection Error: " . $e->getMessage()); // Display detailed error in debug mode $error_details = ''; if ($db_debug) { $error_details = "<div style='margin-top: 15px; text-align: left; background: #333; color: #fff; padding: 15px; font-family: monospace; font-size: 13px; border-radius: 4px;'> <strong>Debug Exception Details:</strong><br>" . htmlspecialchars($e->getMessage()) . "<br><br>" . "<strong>Connection Parameters:</strong><br>" . "Host: " . htmlspecialchars($db_host) . "<br>" . "Port: " . htmlspecialchars($db_port) . "<br>" . "User: " . htmlspecialchars($db_user) . "<br>" . "Database: " . htmlspecialchars($db_name) . " </div>"; } die("<div style='font-family: \"Sarabun\", \"Inter\", sans-serif; text-align: center; margin-top: 80px; padding: 25px; border: 2px solid #BD2130; background-color: #FFF0F0; max-width: 600px; margin-left: auto; margin-right: auto;'> <h2 style='color: #BD2130; margin-bottom: 10px;'>ระบบขัดข้องทางเทคนิค (Database Connection Error)</h2> <p>ไม่สามารถเชื่อมต่อฐานข้อมูลได้ กรุณาตรวจสอบสถานะและพารามิเตอร์การเชื่อมต่อ</p> <hr style='border: 0; border-top: 1px solid #BD2130; margin: 15px 0;'> <small style='color: #666;'>ความเสียหายของโครงสร้าง: ปฏิเสธการเชื่อมต่อจากฐานข้อมูลหลัก</small> {$error_details} </div>"); } /** * Send External Notification (LINE Notify / Webhook Log) for Payment Submissions / if (!function_exists('send_payment_notification')) { function send_payment_notification($store_name, $plan_name, $amount) { $line_token = getenv('LINE_NOTIFY_TOKEN') ?: ''; $message = "\n🔔 มีการชำระเงินใหม่!\nร้าน: {$store_name}\nแพ็กเกจ: {$plan_name}\nจำนวน: " . number_format($amount, 2) . " บาท\nกรุณาเข้าตรวจสอบในระบบ Super Admin"; if (!empty($line_token)) { @$ch = curl_init('https://notify-api.line.me/api/notify'); if ($ch) { curl_setopt($ch, CURLOPT_HTTPHEADER, ["Authorization: Bearer {$line_token}"]); curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query(['message' => $message])); curl_setopt($ch, CURLOPT_TIMEOUT, 5); curl_exec($ch); curl_close($ch); } } $log_entry = date('Y-m-d H:i:s') . " \| PAYMENT_ALERT \| Store: {$store_name} \| Plan: {$plan_name} \| Amount: {$amount} THB\n"; @file_put_contents(__DIR__ . '/payment_notifications.log', $log_entry, FILE_APPEND); } } /* * CSRF Protection Helper Functions / if (!function_exists('generate_csrf_token')) { function generate_csrf_token() { if (empty($_SESSION['csrf_token'])) { $_SESSION['csrf_token'] = bin2hex(random_bytes(32)); } return $_SESSION['csrf_token']; } } if (!function_exists('verify_csrf_token')) { function verify_csrf_token($token) { if (empty($_SESSION['csrf_token']) \|\| empty($token)) { return false; } return hash_equals($_SESSION['csrf_token'], $token); } } /* * XSS Sanitation Helper / if (!function_exists('e')) { function e($string) { return htmlspecialchars($string ?? '', ENT_QUOTES, 'UTF-8'); } } /* * High-Performance Image Resizing and Compression Utility using PHP GD Library * * - Resizes images exceeding 1200px width down to 1200px maintaining original aspect ratio. * - Preserves transparency/alpha channel for PNG, WebP, GIF formats. * - Compresses JPEG/WEBP to 80% quality keeping file sizes between 300KB-800KB. * - Cleans up GD memory resources via imagedestroy(). * - Gracefully falls back to move_uploaded_file() or copy() if GD is unavailable or encounters unsupported mime types. * * @param string $source_path Uploaded temporary or source image path * @param string $dest_path Target save path for optimized image * @param int $max_width Maximum width allowed in pixels (default 1200) * @param int $quality Compression quality percentage (default 80) * @return bool True on success, false on error / if (!function_exists('processAndCompressImage')) { function processAndCompressImage($source_path, $dest_path, $max_width = 1200, $quality = 80) { if (!file_exists($source_path) \|\| !is_readable($source_path)) { return false; } // Ensure destination folder exists and is writable $dest_dir = dirname($dest_path); if (!is_dir($dest_dir)) { @mkdir($dest_dir, 0777, true); } // Fallback if GD library extension is not loaded if (!extension_loaded('gd') \|\| !function_exists('imagecreatetruecolor')) { if (is_uploaded_file($source_path)) { return @move_uploaded_file($source_path, $dest_path); } return @copy($source_path, $dest_path); } $info = @getimagesize($source_path); if (!$info) { if (is_uploaded_file($source_path)) { return @move_uploaded_file($source_path, $dest_path); } return @copy($source_path, $dest_path); } $mime = $info['mime'] ?? ''; $orig_width = $info[0] ?? 0; $orig_height = $info[1] ?? 0; if ($orig_width <= 0 \|\| $orig_height <= 0) { if (is_uploaded_file($source_path)) { return @move_uploaded_file($source_path, $dest_path); } return @copy($source_path, $dest_path); } // Create GD resource based on original image mime type $src_img = null; switch ($mime) { case 'image/jpeg': case 'image/pjpeg': $src_img = @imagecreatefromjpeg($source_path); break; case 'image/png': $src_img = @imagecreatefrompng($source_path); break; case 'image/webp': $src_img = function_exists('imagecreatefromwebp') ? @imagecreatefromwebp($source_path) : null; break; case 'image/gif': $src_img = @imagecreatefromgif($source_path); break; case 'image/bmp': case 'image/x-ms-bmp': $src_img = function_exists('imagecreatefrombmp') ? @imagecreatefrombmp($source_path) : null; break; } // Fallback if image GD creation fails if (!$src_img) { if (is_uploaded_file($source_path)) { return @move_uploaded_file($source_path, $dest_path); } return @copy($source_path, $dest_path); } // Calculate aspect ratio preserving dimensions if ($orig_width > $max_width) { $new_width = $max_width; $new_height = (int)round($orig_height ($max_width / $orig_width)); } else { $new_width = $orig_width; $new_height = $orig_height; } // Create new image canvas $dst_img = @imagecreatetruecolor($new_width, $new_height); if (!$dst_img) { @imagedestroy($src_img); if (is_uploaded_file($source_path)) { return @move_uploaded_file($source_path, $dest_path); } return @copy($source_path, $dest_path); } // Preserve transparency / alpha channel for PNG, WebP, GIF if ($mime === 'image/png' \|\| $mime === 'image/webp' \|\| $mime === 'image/gif') { @imagealphablending($dst_img, false); @imagesavealpha($dst_img, true); $transparent = @imagecolorallocatealpha($dst_img, 255, 255, 255, 127); @imagefilledrectangle($dst_img, 0, 0, $new_width, $new_height, $transparent); } // Resample original image onto newly dimensioned canvas @imagecopyresampled($dst_img, $src_img, 0, 0, 0, 0, $new_width, $new_height, $orig_width, $orig_height); // Save image file according to target extension $dest_ext = strtolower(pathinfo($dest_path, PATHINFO_EXTENSION)); $saved = false; if ($dest_ext === 'png') { $png_quality = (int)round((100 - $quality) / 10); if ($png_quality < 0) $png_quality = 0; if ($png_quality > 9) $png_quality = 9; $saved = @imagepng($dst_img, $dest_path, $png_quality); } elseif ($dest_ext === 'webp' && function_exists('imagewebp')) { $saved = @imagewebp($dst_img, $dest_path, $quality); } else { $saved = @imagejpeg($dst_img, $dest_path, $quality); } // Clean up temporary GD memory resources @imagedestroy($src_img); @imagedestroy($dst_img); // Fallback if saving failed if (!$saved \|\| !file_exists($dest_path)) { if (is_uploaded_file($source_path)) { return @move_uploaded_file($source_path, $dest_path); } return @copy($source_path, $dest_path); } return true; } } ?>

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