🔍 Running security validation on staged changes... Log file: /root/liveserver2024/data/www/official-en-aia/.claude/skills/safe-commit/scripts/../logs/validate_20260202_110540.log AI Validation: enabled (codex) ✓ Found staged changes Checking for sensitive data patterns... - API keys/tokens/secrets... OK - Absolute file paths... OK - Private domains/IPs... OK - .env variable values... OK - Database credentials... OK ✓ Regex-based security checks passed! 🤖 Running AI-powered validation... - AI sensitive data scan (codex)... OpenAI Codex v0.84.0 (research preview) -------- workdir: /root/liveserver2024/data/www/official-en-aia model: gpt-5.2 provider: openai approval: never sandbox: read-only reasoning effort: medium reasoning summaries: auto session id: 019c1e07-a462-7810-a4d7-a8626300a190 -------- user You are a security auditor reviewing a git diff for sensitive information before commit. Analyze this git diff and check for: 1. API keys, tokens, secrets, passwords (real values, not placeholders) 2. Absolute file paths that reveal server infrastructure (e.g., /root/..., /home/user/..., /var/www/...) 3. Internal/private domain names or IP addresses 4. Database credentials or connection strings with passwords 5. Personal information (emails, phone numbers, addresses) 6. Cloud credentials (AWS keys, GCP keys, Azure keys) IMPORTANT: Documentation examples with placeholder paths like '/home/username/...' are OK. Only flag REAL sensitive data that would be a security risk if committed. Respond with EXACTLY one of these formats: - If SAFE: 'SAFE: No sensitive data found' - If ISSUES: 'ISSUES: [brief description of what was found]' Do NOT output anything else. Be concise. Here is the git diff to analyze: diff --git a/laravel/app/Console/Commands/RefreshKpiCache.php b/laravel/app/Console/Commands/RefreshKpiCache.php new file mode 100644 index 0000000..28dc522 --- /dev/null +++ b/laravel/app/Console/Commands/RefreshKpiCache.php @@ -0,0 +1,56 @@ +info('Refreshing KPI data from Google Sheets...'); + + // Clear existing cache first + $kpiService->clearCache(); + $this->line('Cache cleared.'); + + // Fetch fresh data + $data = $kpiService->getKpiData(); + + if ($data['updated_at']) { + $this->info('KPI data refreshed successfully!'); + $this->table( + ['Metric', 'Value'], + collect($data['data'])->map(function ($value, $key) { + return [ + str_replace('_', ' ', ucwords($key, '_')), + number_format($value), + ]; + })->toArray() + ); + $this->line("Updated at: {$data['updated_at']}"); + return Command::SUCCESS; + } + + $this->warn('Failed to fetch fresh data, using default values.'); + return Command::FAILURE; + } +} diff --git a/laravel/app/Http/Controllers/HomeController.php b/laravel/app/Http/Controllers/HomeController.php index 127af62..d018f85 100644 --- a/laravel/app/Http/Controllers/HomeController.php +++ b/laravel/app/Http/Controllers/HomeController.php @@ -4,11 +4,16 @@ use App\Models\Post; use App\Http\Resources\PostResource; +use App\Services\KpiService; use Illuminate\Http\Request; use Inertia\Inertia; class HomeController extends Controller { + public function __construct( + private KpiService $kpiService + ) {} + public function index() { // 查詢 landing-event 分類的文章（限制2筆） @@ -28,9 +33,13 @@ public function index() ->limit(5) ->get(); + // 取得 KPI 數據（含緩存） + $kpiData = $this->kpiService->getFormattedKpiData(); + return Inertia::render('home', [ 'landingEvents' => PostResource::collection($landingEvents)->resolve(), 'lastests' => PostResource::collection($lastests)->resolve(), + 'kpiData' => $kpiData, ]); } } diff --git a/laravel/app/Services/KpiService.php b/laravel/app/Services/KpiService.php new file mode 100644 index 0000000..1ccd8be --- /dev/null +++ b/laravel/app/Services/KpiService.php @@ -0,0 +1,200 @@ + 6, + 'programs' => 129, + 'technical_study_cases' => 661, + 'trainee_companies' => 2157, + 'trainee' => 11496, + 'industry_user_cases' => 714, + 'lectures' => 431, + 'industry_involved' => 15, + ]; + + /** + * Get KPI data with caching + * + * @return array{data: array, updated_at: string|null} + */ + public function getKpiData(): array + { + return Cache::remember(self::CACHE_KEY, self::CACHE_TTL_HOURS * 60 * 60, function () { + return $this->fetchKpiData(); + }); + } + + /** + * Clear KPI cache + * + * @return void + */ + public function clearCache(): void + { + Cache::forget(self::CACHE_KEY); + } + + /** + * Fetch KPI data from Google Sheets TSV + * + * @return array{data: array, updated_at: string|null} + */ + private function fetchKpiData(): array + { + try { + $response = Http::timeout(10)->get(self::GOOGLE_SHEETS_URL); + + if (!$response->successful()) { + Log::warning('[KpiService] Failed to fetch Google Sheets data', [ + 'status' => $response->status(), + ]); + return $this->getDefaultData(); + } + + $tsvContent = $response->body(); + $parsedData = $this->parseTsvContent($tsvContent); + + return [ + 'data' => $parsedData, + 'updated_at' => now()->format('Y/m/d H:i'), + ]; + } catch (\Exception $e) { + Log::error('[KpiService] Exception while fetching KPI data', [ + 'error' => $e->getMessage(), + ]); + return $this->getDefaultData(); + } + } + + /** + * Parse TSV content into key-value array + * TSV format: Key\tValue (first row is header) + * + * @param string $content + * @return array + */ + private function parseTsvContent(string $content): array + { + $lines = explode("\r\n", $content); + + // If \r\n doesn't work, try \n + if (count($lines) <= 1) { + $lines = explode("\n", $content); + } + + $result = self::DEFAULT_KPI; + + // Skip header row (index 0) + for ($i = 1; $i < count($lines); $i++) { + $line = trim($lines[$i]); + if (empty($line)) { + continue; + } + + $parts = explode("\t", $line); + if (count($parts) >= 2) { + $key = $this->normalizeKey($parts[0]); + $value = $this->parseNumericValue($parts[1]); + + if ($key && isset($result[$key])) { + $result[$key] = $value; + } + } + } + + return $result; + } + + /** + * Normalize key from TSV to match our expected keys + * "Annual Conferences" -> "annual_conferences" + * + * @param string $key + * @return string + */ + private function normalizeKey(string $key): string + { + $key = strtolower(trim($key)); + $key = str_replace(' ', '_', $key); + + // Remove "_count" suffix if present (from original PHP) + $key = preg_replace('/_count$/', '', $key); + + return $key; + } + + /** + * Parse numeric value, removing commas and converting to integer + * + * @param string $value + * @return int + */ + private function parseNumericValue(string $value): int + { + $value = str_replace(',', '', trim($value)); + return (int) $value; + } + + /** + * Get default data when fetch fails + * + * @return array{data: array, updated_at: string|null} + */ + private function getDefaultData(): array + { + return [ + 'data' => self::DEFAULT_KPI, + 'updated_at' => null, + ]; + } + + /** + * Format KPI data for frontend display + * Returns data with formatted numbers (e.g., 11496 -> "11,496") + * + * @return array + */ + public function getFormattedKpiData(): array + { + $kpiData = $this->getKpiData(); + + $formatted = []; + foreach ($kpiData['data'] as $key => $value) { + $formatted[$key] = [ + 'value' => $value, + 'formatted' => number_format($value), + ]; + } + + return [ + 'kpi' => $formatted, + 'updated_at' => $kpiData['updated_at'], + ]; + } +} diff --git a/laravel/resources/js/components/aia/our-impact.tsx b/laravel/resources/js/components/aia/our-impact.tsx index b7a2eaf..d923844 100644 --- a/laravel/resources/js/components/aia/our-impact.tsx +++ b/laravel/resources/js/components/aia/our-impact.tsx @@ -1,6 +1,6 @@ /** * OurImpact Component - React Wrapper for Vendor JS - * + * * @vendor-dependency @vendor-web/js/components/ourImpact.js * @cleanup-status ⚠️ Pending vendor update - needs cleanup function */ @@ -8,11 +8,47 @@ import React, { useRef, useEffect } from 'react'; import { OurImpact } from '@vendor-web/js/components/ourImpact.js'; +interface KpiItem { + value: number; + formatted: string; +} + +interface KpiData { + kpi: { + annual_conferences: KpiItem; + programs: KpiItem; + technical_study_cases: KpiItem; + trainee_companies: KpiItem; + trainee: KpiItem; + industry_user_cases: KpiItem; + lectures: KpiItem; + industry_involved: KpiItem; + }; + updated_at: string | null; +} + interface OurImpactProps { dataEnd?: boolean; + kpiData?: KpiData; } -export default function Impact({ dataEnd = false }: OurImpactProps) { +// Default KPI values (fallback) +const DEFAULT_KPI: KpiData = { + kpi: { + annual_conferences: { value: 6, formatted: '6' }, + programs: { value: 129, formatted: '129' }, + technical_study_cases: { value: 661, formatted: '661' }, + trainee_companies: { value: 2157, formatted: '2,157' }, + trainee: { value: 11496, formatted: '11,496' }, + industry_user_cases: { value: 714, formatted: '714' }, + lectures: { value: 431, formatted: '431' }, + industry_involved: { value: 15, formatted: '15' }, + }, + updated_at: null, +}; + +export default function Impact({ dataEnd = false, kpiData = DEFAULT_KPI }: OurImpactProps) { + const kpi = kpiData?.kpi ?? DEFAULT_KPI.kpi; const ourImpactRef = useRef(null); useEffect(() => { @@ -52,7 +88,7 @@ export default function Impact({ dataEnd = false }: OurImpactProps) { alt="" /> - 6 + {kpi.annual_conferences.formatted} Annual Conferences
  • - 129 + {kpi.programs.formatted} Programs in Management and Engineering @@ -81,7 +117,7 @@ export default function Impact({ dataEnd = false }: OurImpactProps) { alt="" /> - 661 + {kpi.technical_study_cases.formatted} Technical Study Cases
  • - 2,157 + {kpi.trainee_companies.formatted} Trainee Companies
    • @@ -109,7 +145,7 @@ export default function Impact({ dataEnd = false }: OurImpactProps) { alt="" /> - 11,496 + {kpi.trainee.formatted} Trainee
  • - 714 + {kpi.industry_user_cases.formatted} Industry User Cases
  • - 431 + {kpi.lectures.formatted} Lectures
  • - 15 + {kpi.industry_involved.formatted} Industry Involved
    • diff --git a/laravel/resources/js/pages/home.tsx b/laravel/resources/js/pages/home.tsx index 949f15c..9799b4a 100644 --- a/laravel/resources/js/pages/home.tsx +++ b/laravel/resources/js/pages/home.tsx @@ -34,13 +34,33 @@ type Post = { }>; }; +type KpiItem = { + value: number; + formatted: string; +}; + +type KpiData = { + kpi: { + annual_conferences: KpiItem; + programs: KpiItem; + technical_study_cases: KpiItem; + trainee_companies: KpiItem; + trainee: KpiItem; + industry_user_cases: KpiItem; + lectures: KpiItem; + industry_involved: KpiItem; + }; + updated_at: string | null; +}; + type HomeProps = { landingEvents: Post[]; lastests: Post[]; + kpiData: KpiData; }; export default function Home( - { landingEvents, lastests }: HomeProps + { landingEvents, lastests, kpiData }: HomeProps ) { // console.log('@@@@@@@@@', landingEvents); React.useEffect(() => { @@ -65,7 +85,7 @@ export default function Home( landingEvents={landingEvents} />
    - +
    comment(Inspiring::quote()); })->purpose('Display an inspiring quote'); + +/* +|-------------------------------------------------------------------------- +| KPI Data Refresh Schedule +|-------------------------------------------------------------------------- +| +| Refresh KPI data from Google Sheets daily at 6:00 AM (Taipei timezone). +| This ensures the data is fresh when users visit the site during work hours. +| +*/ +Schedule::command('kpi:refresh') + ->dailyAt('06:00') + ->timezone('Asia/Taipei') + ->withoutOverlapping() + ->onOneServer() + ->appendOutputTo(storage_path('logs/kpi-refresh.log')); mcp: playwright starting mcp: context7 starting mcp: context7 ready mcp: playwright ready mcp startup: ready: context7, playwright thinking SAFE: No sensitive data found. codex SAFE: No sensitive data found 2026-02-02T11:05:54.749553Z ERROR codex_core::codex: needs_follow_up: false tokens used 17,548 OK AI: SAFE: No sensitive data found ✓ All security checks passed! Staged files: - laravel/app/Console/Commands/RefreshKpiCache.php - laravel/app/Http/Controllers/HomeController.php - laravel/app/Services/KpiService.php - laravel/resources/js/components/aia/our-impact.tsx - laravel/resources/js/pages/home.tsx - laravel/routes/console.php