<?php
class NPS {
    private $conn;
    private $columnPrefix;
    private $countRange;

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

    public function npsCalculate($prefix,$monthX) {
        $baseCounts = [];
        $promoterCounts = [];
        $passiveCounts = [];
        $detractorCounts = [];

        $baseCounts[] = 'count(case when '.$prefix.' IN (1,2,3,4,5,9) then sys_RespNum end) as ' . $prefix . 'base';
        $promoterCounts[] = 'count(case when '.$prefix.' IN (4,5) then sys_RespNum end) as ' . $prefix . 'promoter';
        $passiveCounts[] = 'count(case when '.$prefix.' IN (3,9) then sys_RespNum end) as ' . $prefix . 'passive';
        $detractorCounts[] = 'count(case when '.$prefix.' IN (1,2) then sys_RespNum end) as ' . $prefix . 'detractor';

        $baseSql = implode(', ', $baseCounts);
        $promoterSql = implode(', ', $promoterCounts);
        $passiveSql = implode(', ', $passiveCounts);
        $detractorSql = implode(', ', $detractorCounts);

        $sql = "SELECT $baseSql, $promoterSql, $passiveSql, $detractorSql FROM m1 WHERE `Month` IN ($monthX) AND $this->condX";
        
        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_array($result);

        $data = [];

        $baseCount = $ALL[$prefix . 'base'];
        $baseCount2 = $ALL[$prefix . 'base'];
		if($baseCount==0) { $baseCount=1; } 
        $promoterCount = $ALL[$prefix . 'promoter'];
        $passiveCount = $ALL[$prefix . 'passive'];
        $detractorCount = $ALL[$prefix . 'detractor'];

        $promoterPercentage = ($promoterCount/$baseCount)*100;
        $passivePercentage = ($passiveCount/$baseCount)*100;
        $detractorPercentage = ($detractorCount/$baseCount)*100;
        $npsPercentage = $promoterPercentage - $detractorPercentage;

        $promoterPercentage2 = round($promoterPercentage);
        $passivePercentage2 = round($passivePercentage);
        $detractorPercentage2 = round($detractorPercentage);
        $npsPercentage2 = round($npsPercentage);

        array_push($data,$baseCount2,$promoterPercentage2,$passivePercentage2,$detractorPercentage2,$npsPercentage2);

        return $data;
    }
}

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

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

    public function npsCalculate2($prefix, $range, $brand, $monthX) {
        $baseCounts = [];
        $promoterCounts = [];
        $passiveCounts = [];
        $detractorCounts = [];

        foreach ($range as $value) {
            foreach ($brand as $value2) {
                $additional = '1=1';
                // if($prefix=="Q20a")
                // {
                //     if($value=="10")
                //     {
                //         $additional = 'Q15 = "1" OR Q16= "1"';
                //     }
                // } 
                $baseCounts[] = 'COUNT(CASE WHEN '.$additional.' AND ' . $prefix . $value . '_' . $value2 . ' IN (1,2,3,4,5,9) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'base';
                $promoterCounts[] = 'COUNT(CASE WHEN '.$additional.' AND ' . $prefix . $value . '_' . $value2 . ' IN (4, 5) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'promoter';
                $passiveCounts[] = 'COUNT(CASE WHEN '.$additional.' AND ' . $prefix . $value . '_' . $value2 . ' IN (3,9) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'passive';
                $detractorCounts[] = 'COUNT(CASE WHEN '.$additional.' AND ' . $prefix . $value . '_' . $value2 . ' IN (1, 2) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'detractor';
            }
        }
    
        $baseSql = implode(', ', $baseCounts);
        $promoterSql = implode(', ', $promoterCounts);
        $passiveSql = implode(', ', $passiveCounts);
        $detractorSql = implode(', ', $detractorCounts);

        $sql = "SELECT $baseSql, $promoterSql, $passiveSql, $detractorSql FROM m1 WHERE `Month` IN ($monthX) AND $this->condX ";
        $result = mysqli_query($this->conn, $sql);
        if (!$result) {
            die('SQL Error: ' . mysqli_error($this->conn));
        }
    
        $ALL = mysqli_fetch_assoc($result);
        $data = [];
    
        foreach ($range as $value) {
            $data2 = [];
            foreach ($brand as $value2) {
                $baseKey = $prefix . '_' . $value . '_' . $value2 . 'base';
                $promoterKey = $prefix . '_' . $value . '_' . $value2 . 'promoter';
                $passiveKey = $prefix . '_' . $value . '_' . $value2 . 'passive';
                $detractorKey = $prefix . '_' . $value . '_' . $value2 . 'detractor';
    
                $baseCount = $ALL[$baseKey] ;
                $baseCount2 = $ALL[$baseKey] ;
				if($baseCount==0){$baseCount=1;}
                $promoterCount = $ALL[$promoterKey] ;
                $passiveCount = $ALL[$passiveKey] ;
                $detractorCount = $ALL[$detractorKey] ;
    
                $promoterPercentage =  ($promoterCount / $baseCount) * 100 ;
                $passivePercentage =   ($passiveCount / $baseCount) * 100 ;
                $detractorPercentage =  ($detractorCount / $baseCount) * 100 ;
                $npsPercentage = $promoterPercentage - $detractorPercentage;

                $data2[] = [
                    'base' => $baseCount2,
                    'promoter' => round($promoterPercentage),
                    'passive' => round($passivePercentage),
                    'detractor' => round($detractorPercentage),
                    'nps' => round($npsPercentage),
                ];
            }
            $data[] = $data2;
        }
        return $data;
    }    
}


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

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

    public function npsCalculate3($prefix, $range, $brand, $monthX) {
        $baseCounts = [];
        $promoterCounts = [];
        $passiveCounts = [];
        $detractorCounts = [];

        foreach ($range as $value) {
            foreach ($brand as $value2) {
                $additional = '1=1';
                $baseCounts[] = 'COUNT(CASE WHEN '.$additional.' AND ' . $prefix . $value . '_' . $value2 . ' IN (1,2,3,4,5,9)  THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'base';
                $promoterCounts[] = 'COUNT(CASE WHEN '.$additional.' AND ' . $prefix . $value . '_' . $value2 . ' IN (4,5) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'promoter';
                $passiveCounts[] = 'COUNT(CASE WHEN '.$additional.' AND ' . $prefix . $value . '_' . $value2 . ' IN (3,9) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'passive';
                $detractorCounts[] = 'COUNT(CASE WHEN '.$additional.' AND ' . $prefix . $value . '_' . $value2 . ' IN (1,2) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'detractor';
            }
        }
    
        $baseSql = implode(', ', $baseCounts);
        $promoterSql = implode(', ', $promoterCounts);
        $passiveSql = implode(', ', $passiveCounts);
        $detractorSql = implode(', ', $detractorCounts);
        //echo $baseSql;
        $sql = "SELECT $baseSql, $promoterSql, $passiveSql, $detractorSql FROM m1 WHERE `Month` IN ($monthX) AND $this->condX";

        $result = mysqli_query($this->conn, $sql);
        if (!$result) {
            die('SQL Error: ' . mysqli_error($this->conn));
        }
    
        $ALL = mysqli_fetch_assoc($result);
        $data = [];
    
        foreach ($range as $value) {
            $data2 = [];
            foreach ($brand as $value2) {
                $baseKey = $prefix . '_' . $value . '_' . $value2 . 'base';
                $promoterKey = $prefix . '_' . $value . '_' . $value2 . 'promoter';
                $passiveKey = $prefix . '_' . $value . '_' . $value2 . 'passive';
                $detractorKey = $prefix . '_' . $value . '_' . $value2 . 'detractor';
    
                $baseCount = $ALL[$baseKey] ;
                $baseCount2 = $ALL[$baseKey] ;
				if($baseCount==0){$baseCount=1;}
                $promoterCount = $ALL[$promoterKey] ;
                $passiveCount = $ALL[$passiveKey] ;
                $detractorCount = $ALL[$detractorKey] ;
    
                $promoterPercentage =  ($promoterCount / $baseCount) * 100 ;
                $passivePercentage =   ($passiveCount / $baseCount) * 100 ;
                $detractorPercentage =  ($detractorCount / $baseCount) * 100 ;
                $npsPercentage = $promoterPercentage - $detractorPercentage;

                $data2[] = [
                    'base' => $baseCount2,
                    'promoter' => round($promoterPercentage),
                    'passive' => round($passivePercentage),
                    'detractor' => round($detractorPercentage),
                    'nps' => round($npsPercentage),
                ];
            }
            $data[] = $data2;
        }
        return $data;
    }    
}

class NPS4 {
    private $conn;
    private $condX;

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

    public function npsCalculate4($prefix, $monthX) {
        $resultData = [];

        foreach ($monthX as $month) {
            $baseCounts = [];
            $promoterCounts = [];
            $passiveCounts = [];
            $detractorCounts = [];

            $baseCounts[] = 'count(case when ' . $prefix . ' IN (1,2,3,4,5,9) then sys_RespNum end) as ' . $prefix . 'base';
            $promoterCounts[] = 'count(case when ' . $prefix . ' IN (4,5) then sys_RespNum end) as ' . $prefix . 'promoter';
            $passiveCounts[] = 'count(case when ' . $prefix . ' IN (3,9) then sys_RespNum end) as ' . $prefix . 'passive';
            $detractorCounts[] = 'count(case when ' . $prefix . ' IN (1,2) then sys_RespNum end) as ' . $prefix . 'detractor';

            $baseSql = implode(', ', $baseCounts);
            $promoterSql = implode(', ', $promoterCounts);
            $passiveSql = implode(', ', $passiveCounts);
            $detractorSql = implode(', ', $detractorCounts);

            $sql = "SELECT $baseSql, $promoterSql, $passiveSql, $detractorSql FROM `m1` WHERE `Month` = $month AND $this->condX";
           
            $result = mysqli_query($this->conn, $sql);
            $ALL = mysqli_fetch_array($result);

            $data = [];

            $baseCount = $ALL[$prefix . 'base'];
            $baseCount2 = $ALL[$prefix . 'base'];
            if ($baseCount == 0) {
                $baseCount = 1;
            }

            $promoterCount = $ALL[$prefix . 'promoter'];
            $passiveCount = $ALL[$prefix . 'passive'];
            $detractorCount = $ALL[$prefix . 'detractor'];

            $promoterPercentage = ($promoterCount / $baseCount) * 100;
            $passivePercentage = ($passiveCount / $baseCount) * 100;
            $detractorPercentage = ($detractorCount / $baseCount) * 100;
            $npsPercentage = $promoterPercentage - $detractorPercentage;

            $data['month'] = $month;
            $data['baseCount'] = $baseCount2;
            $data['promoterPercentage'] = round($promoterPercentage);
            $data['passivePercentage'] = round($passivePercentage);
            $data['detractorPercentage'] = round($detractorPercentage);
            $data['npsPercentage'] = round($npsPercentage);

            $resultData[] = $data;
        }

        return $resultData;
    }
}

class NPS5 {
    private $conn;
    private $condX;

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

    public function npsCalculate5($prefix, $range, $brand, $monthX) {
        $resultData = [];

        foreach ($monthX as $month) {
            $baseCounts = [];
            $promoterCounts = [];
            $passiveCounts = [];
            $detractorCounts = [];

            foreach ($range as $value) {

                foreach ($brand as $value2) {
                    $additional = '1=1';
                    
                    $baseCounts[] = 'COUNT(CASE WHEN ' . $additional . ' AND ' . $prefix . $value . '_' . $value2 . ' IN (1,2,3,4,5,9) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'base';
                    $promoterCounts[] = 'COUNT(CASE WHEN ' . $additional . ' AND ' . $prefix . $value . '_' . $value2 . ' IN (4, 5) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'promoter';
                    $passiveCounts[] = 'COUNT(CASE WHEN ' . $additional . ' AND ' . $prefix . $value . '_' . $value2 . ' IN (3,9) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'passive';
                    $detractorCounts[] = 'COUNT(CASE WHEN ' . $additional . ' AND ' . $prefix . $value . '_' . $value2 . ' IN (1,2) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'detractor';
                }
            }

            $baseSql = implode(', ', $baseCounts);
            $promoterSql = implode(', ', $promoterCounts);
            $passiveSql = implode(', ', $passiveCounts);
            $detractorSql = implode(', ', $detractorCounts);

            $sql = "SELECT $baseSql, $promoterSql, $passiveSql, $detractorSql FROM `m1` WHERE `Month` = $month AND $this->condX";
            $result = mysqli_query($this->conn, $sql);
            if (!$result) {
                die('SQL Error: ' . mysqli_error($this->conn));
            }

            $ALL = mysqli_fetch_assoc($result);
            
            foreach ($range as $value) {
                $data2 = [];
                foreach ($brand as $value2) {
                    $baseKey = $prefix . '_' . $value . '_' . $value2 . 'base';
                    $promoterKey = $prefix . '_' . $value . '_' . $value2 . 'promoter';
                    $passiveKey = $prefix . '_' . $value . '_' . $value2 . 'passive';
                    $detractorKey = $prefix . '_' . $value . '_' . $value2 . 'detractor';

                    $baseCount = $ALL[$baseKey];
                    $baseCount2 = $ALL[$baseKey];
                    if ($baseCount == 0) {
                        $baseCount = 1;
                    }

                    $promoterCount = $ALL[$promoterKey];
                    $passiveCount = $ALL[$passiveKey];
                    $detractorCount = $ALL[$detractorKey];

                    $promoterPercentage = ($promoterCount / $baseCount) * 100;
                    $passivePercentage = ($passiveCount / $baseCount) * 100;
                    $detractorPercentage = ($detractorCount / $baseCount) * 100;
                    $npsPercentage = $promoterPercentage - $detractorPercentage;

                    $data2[] = [
                        'month' => $month,
                        'baseCount' => $baseCount2,
                        'promoterPercentage' => round($promoterPercentage),
                        'passivePercentage' => round($passivePercentage),
                        'detractorPercentage' => round($detractorPercentage),
                        'npsPercentage' => round($npsPercentage),
                    ];
                }
            }

            $resultData[] = $data2;
        }

        return $resultData;
    }
}

class NPS6 {
    private $conn;
    private $condX;

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

    public function npsCalculate6($prefix, $range, $brand, $monthX) {
        $resultData = [];

        foreach ($monthX as $month) {
            $XXX = [];
            $baseCounts = [];
            $promoterCounts = [];
            $passiveCounts = [];
            $detractorCounts = [];

            foreach ($range as $value) {
                foreach ($brand as $value2) {
                    $additional = '1=1';
                    
                    $baseCounts[] = 'COUNT(CASE WHEN ' . $additional . ' AND ' . $prefix . $value . '_' . $value2 . ' IN (1,2,3,4,5,9) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'base';
                    $promoterCounts[] = 'COUNT(CASE WHEN ' . $additional . ' AND ' . $prefix . $value . '_' . $value2 . ' IN (4, 5) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'promoter';
                    $passiveCounts[] = 'COUNT(CASE WHEN ' . $additional . ' AND ' . $prefix . $value . '_' . $value2 . ' IN (3,9) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'passive';
                    $detractorCounts[] = 'COUNT(CASE WHEN ' . $additional . ' AND ' . $prefix . $value . '_' . $value2 . ' IN (1,2) THEN sys_RespNum END) AS ' . $prefix . '_' . $value . '_' . $value2 . 'detractor';
                }
            }

            $baseSql = implode(', ', $baseCounts);
            $promoterSql = implode(', ', $promoterCounts);
            $passiveSql = implode(', ', $passiveCounts);
            $detractorSql = implode(', ', $detractorCounts);

            $sql = "SELECT $baseSql, $promoterSql, $passiveSql, $detractorSql FROM `m1` WHERE `Month` = $month AND $this->condX";
            $result = mysqli_query($this->conn, $sql);
            if (!$result) {
                die('SQL Error: ' . mysqli_error($this->conn));
            }

            $ALL = mysqli_fetch_assoc($result);
            
            foreach ($brand as $value2) {
                $data2 = [];
                foreach ($range as $value) {
                    $baseKey = $prefix . '_' . $value . '_' . $value2 . 'base';
                    $promoterKey = $prefix . '_' . $value . '_' . $value2 . 'promoter';
                    $passiveKey = $prefix . '_' . $value . '_' . $value2 . 'passive';
                    $detractorKey = $prefix . '_' . $value . '_' . $value2 . 'detractor';

                    $baseCount = $ALL[$baseKey];
                    $baseCount2 = $ALL[$baseKey];
                    if ($baseCount == 0) {
                        $baseCount = 1;
                    }

                    $promoterCount = $ALL[$promoterKey];
                    $passiveCount = $ALL[$passiveKey];
                    $detractorCount = $ALL[$detractorKey];

                    $promoterPercentage = ($promoterCount / $baseCount) * 100;
                    $passivePercentage = ($passiveCount / $baseCount) * 100;
                    $detractorPercentage = ($detractorCount / $baseCount) * 100;
                    $npsPercentage = $promoterPercentage - $detractorPercentage;

                    $data2[] = [
                        'month' => $month,
                        'baseCount' => $baseCount2,
                        'promoterPercentage' => round($promoterPercentage),
                        'passivePercentage' => round($passivePercentage),
                        'detractorPercentage' => round($detractorPercentage),
                        'npsPercentage' => round($npsPercentage),
                    ];
                }
                array_push($XXX,$data2);
            }
            $resultData[] = $XXX;
        }

        return $resultData;
    }
}

?>