<?php

class RecordCounter {
    private $conn;
    private $columnPrefix;
    private $countRange;

    public function __construct($conn,$condX,$tblX) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
    }

    public function countRecords($prefix,$range,$airport) {
        $bCounts = [];
        $cCounts = [];

        foreach($airport as $key => $value)
        {
            foreach ($range as $value2) 
            {
                $bCounts[] = 'count(case when `Q5` = '.$key.' AND `' . $prefix . $value2 . '` != "99999" AND `' . $prefix . $value2 . '` != "98" then record end) as ' . $prefix . 'b' . $value2 . $key;
                $cCounts[] = 'count(case when `Q5` = '.$key.' AND `' . $prefix . $value2 . '` = "1"  then record end) as ' . $prefix . 'c' . $value2 . $key;
            }
        }

        $bSql = implode(', ', $bCounts);
        $cSql = implode(', ', $cCounts);

        $sql = "SELECT $bSql, $cSql, record FROM $this->tblX WHERE $this->condX";
        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_array($result);

        $data = [];

        foreach($range as $value2)
        {
            $data2 = array();
            foreach ($airport as $key => $value) 
            {
                $bCount = $ALL[$prefix . 'b' . $value2 . $key];
                $cCount = $ALL[$prefix . 'c' . $value2 . $key];
                
                $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100, 1) : 0;

                $data2[] = [
                    'bCount' => $bCount,
                    'cCount' => $cCount,
                    'percentage' => $percentage,
                ];
            }
            array_push($data,$data2);
        }
        return $data;
    }
}

class RecordCounter2 {
    private $conn;
    private $columnPrefix;
    private $countRange;

    public function __construct($conn,$condX,$tblX) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
    }

    public function countRecords2($prefix,$range,$airport) {
        $bCounts = [];
        $cCounts = [];

        foreach($airport as $key => $value)
        {
            foreach ($range as $value2) 
            {
                $bCounts[] = 'count(case when `Q5` = '.$key.' AND `' . $prefix . '` != "99999" AND `' . $prefix . '` != "98" then record end) as ' . $prefix . 'b' . $value2 . $key;
                $cCounts[] = 'count(case when `Q5` = '.$key.' AND `' . $prefix . '` = "'.$value2.'"  then record end) as ' . $prefix . 'c' . $value2 . $key;
            }
        }

        $bSql = implode(', ', $bCounts);
        $cSql = implode(', ', $cCounts);

        $sql = "SELECT $bSql, $cSql, record FROM $this->tblX WHERE $this->condX";
        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_array($result);

        $data = [];

        foreach($range as $value2)
        {
            $data2 = array();
            foreach ($airport as $key => $value) 
            {
                $bCount = $ALL[$prefix . 'b' . $value2 . $key];
                $cCount = $ALL[$prefix . 'c' . $value2 . $key];
                
                $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100, 1) : 0;

                $data2[] = [
                    'bCount' => $bCount,
                    'cCount' => $cCount,
                    'percentage' => $percentage,
                ];
            }
            array_push($data,$data2);
        }
        return $data;
    }
}

class ProfileOverall {
    private $conn;
    private $columnPrefix;
    private $countRange;

    public function __construct($conn,$tblX,$Q29a) {
        $this->conn = $conn;
        $this->tblX = $tblX;
        $this->Q29a = '(' . implode(',', $Q29a) . ')';
    }

    public function profileOverallCount($prefix,$range)
    {
        $bCounts = [];
        $cCounts = [];

        foreach ($range as $value2) 
        {
            $bCounts[] = 'count(case when ' . $prefix . ' != "99999" AND `' . $prefix . '` != "98" then record end) as ' . $prefix . 'b' . $value2 ;
            $cCounts[] = 'count(case when ' . $prefix . ' = '. $value2 .'  then record end) as ' . $prefix . 'c' . $value2 ;
        }

        $bSql = implode(', ', $bCounts);
        $cSql = implode(', ', $cCounts);

        $sql = "SELECT $bSql, $cSql, record FROM $this->tblX WHERE `Q29a` IN $this->Q29a";
        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_array($result);

        $data = [];

        foreach($range as $value2)
        {
            $bCount = $ALL[$prefix . 'b' . $value2 ];
            $cCount = $ALL[$prefix . 'c' . $value2 ];
            
            $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100, 1) : 0;

            $data[] = [
                'bCount' => $bCount,
                'cCount' => $cCount,
                'percentage' => $percentage,
            ];
        }
        return $data;
    }
}


class ProfileAirport {
    private $conn;
    private $columnPrefix;
    private $countRange;

    public function __construct($conn,$tblX,$Q29a,$condX) {
        $this->conn = $conn;
        $this->tblX = $tblX;
        $this->Q29a = '(' . implode(',', $Q29a) . ')';
        $this->condX = $condX;
    }

    public function profileAirportCount($prefix,$range,$airport)
    {
        $bCounts = [];
        $cCounts = [];

        foreach($airport as $key => $value)
        {
            foreach ($range as $value2) 
            {
                $bCounts[] = 'count(case when `Q5` = '.$key.' AND ' . $prefix . ' != "99999" AND `' . $prefix . '` != "98" then record end) as ' . $prefix . 'b' . $value2 . $key ;
                $cCounts[] = 'count(case when `Q5` = '.$key.' AND ' . $prefix . ' = '. $value2 .'  then record end) as ' . $prefix . 'c' . $value2 . $key ;
            }
        }
        

        $bSql = implode(', ', $bCounts);
        $cSql = implode(', ', $cCounts);

        $sql = "SELECT $bSql, $cSql, record FROM $this->tblX WHERE `Q29a` IN $this->Q29a AND $this->condX";
        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_array($result);

        $data = [];

        foreach($range as $value2)
        {
            $data2 = array();
            foreach ($airport as $key => $value) 
            {
                $bCount = $ALL[$prefix . 'b' . $value2 . $key];
                $cCount = $ALL[$prefix . 'c' . $value2 . $key];
                
                $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100, 1) : 0;

                $data2[] = [
                    'bCount' => $bCount,
                    'cCount' => $cCount,
                    'percentage' => $percentage,
                ];
            }
            array_push($data,$data2);
        }
        return $data;
    }
}




?>