<?php
include 'database.php';
// _____________________________________________________
//                   --insert data--
// _____________________________________________________


// insert single report
function insert_report($aid,$tableName)
{
    $conn = connect_database();
    $id = insert_report_info($tableName);
    $data1 = get_data_trends($tableName, $conn);
    insert_trends($id, $aid, $data1, $conn);
    $data2 = get_data_inclination($tableName, $conn);
    insert_inclination($id, $aid, $data2, $conn);
    $data3 = get_data_ratings($tableName, $conn);
    insert_ratings($id, $aid, $data3, $conn);
    
    return null;
}

// insert single report info
function insert_report_info($tableName)
{
    $conn = connect_database();
    $data = get_total_rows($tableName);
    $sql = "UPDATE `reportInfo` SET `rows` ='" . $data['total'] . "' WHERE `report_tablename`='" . $tableName . "'";
    $conn->query($sql);
    $sql = "SELECT id FROM `reportInfo` WHERE`report_tablename`='" . $tableName . "'";
    $result = $conn->query($sql);
    $row = mysqli_fetch_all($result, MYSQLI_ASSOC);

    return $row[0]['id'];
}


// insert bulk report
function insert_bulk($id)
{
    $data = get_bulk_report_insert($id);
    foreach ($data as $key => $assembly) {
        $rid = insert_bulk_report_info($assembly['assembly_id'], $assembly['Assembly_Name'], $assembly['total']);
        insert_bulk_report($assembly['assembly_id'], $rid);
    }
}

// insert bulk report one at a time
function insert_bulk_report($id, $rid)
{
    $conn = connect_database();
    $tableName = 'bulkReport';
    $data1 = get_bulk_data_trends($id, $tableName, $conn);
    insert_trends($rid, $data1, $conn);
    $data2 = get_bulk_data_inclination($id, $tableName, $conn);
    insert_inclination($rid, $data2, $conn);
    $data3 = get_bulk_data_ratings($id, $tableName, $conn);
    insert_ratings($rid, $data3, $conn);
    return null;
}

// insert bulk report info
function insert_bulk_report_info($id, $assembly_name, $rows)
{
    $conn = connect_database();
    $tablename = '___bulk___import___';
    $sql = "INSERT INTO `reportInfo`(`assembly_id`, `report_title`, `report_tablename`, `rows`) VALUES (" . $id . ",'" . $assembly_name . "','" . $tablename . "'," . $rows . ")";
    $conn->query($sql);
    $sql = "SELECT id FROM `reportInfo` WHERE `assembly_id` =" . $id;
    $result = $conn->query($sql);
    $row = mysqli_fetch_all($result, MYSQLI_ASSOC);

    return $row[0]['id'];
}


// trends data insert 
function insert_trends($id, $aid, $data, $conn)
{
    $arr = ['caste', 'occupation', 'age_group', 'education'];
    $arr2 = ['CasteGroups', 'Occupation', 'AgeGroup', 'Education'];
    foreach ($arr as $key => $col) {
        $sql = "INSERT INTO `trends`
        (`assembly_id`, `report_id`, `" . $col . "`, `INC`, `BJP`, `AAP`, `Others`, `adjusted_INC`, `adjusted_BJP`, `adjusted_AAP`, `adjusted_Others`) VALUES 
        ";
        $flag = true;
        foreach ($data[$key] as $inputdata => $row) {
            $temp = $row[$arr2[$key]];
            $val = "('" . $aid . "','" . $id . "','" . $temp . "','" . $row['INC'] . "','" . $row['BJP'] . "','" . $row['AAP'] . "','" . $row['Others'] . "','" . $row['INC'] . "','" . $row['BJP'] . "','" . $row['AAP'] . "','" . $row['Others'] . "')";
            if ($flag)
                $sql = $sql . $val;
            else
                $sql = $sql . ', ' . $val;

            $flag = false;
        }

        $conn->query($sql);
    }
}

// inclination data insert 
function insert_inclination($id, $aid, $data, $conn)
{
    $conn = connect_database();
    $arr = ['INC', 'BJP', 'AAP', 'Others'];
    foreach ($arr as $party) {
        $sql = "INSERT INTO `inclination`(`assembly_id`, `report_id`, `party`, `voting_percentage`, `adjusted_voting_percentage`) 
    VALUES ('" . $aid . "','" . $id . "','" . $party . "','" . $data[$party] . "','" . $data[$party] . "');";
        $conn->query($sql);
    }
}

// ratings data insert 
function insert_ratings($id, $aid, $data, $conn)
{
    $arr = ['Q1', 'Q2', 'Q3'];
    foreach ($arr as $key => $col) {
        $sql = "INSERT INTO `ratings`
        (`assembly_id`, `report_id`, `question`, `rating`, `total_votes`, `total_percentage`, `adjusted_total_votes`, `adjusted_total_percentage`) VALUES 
        ";
        $flag = true;
        foreach ($data[$key] as $inputdata => $row) {
            $val = "('" . $aid . "','" . $id . "','" . $col . "','" . $row[$col] . "','" . $row['total'] . "','" . $row['totalPercentage'] . "','" . $row['total'] . "','" . $row['totalPercentage'] . "')";
            if ($flag)
                $sql = $sql  . $val;
            else
                $sql = $sql . ', ' . $val;

            $flag = false;
        }
        $conn->query($sql);
    }
}

// _____________________________________________________
//                   --update data--
// _____________________________________________________


// update values trends -> caste data
function update_trends_caste_values($newData)
{
    $conn = connect_database();
    $id = $_GET['id'];
    // $oldData = get_caste_data($newData['caste'], $id);
    // $inc = $oldData['adjusted_INC'] + $newData['INC'];
    // $bjp = $oldData['adjusted_BJP'] + $newData['BJP'];
    // $aap = $oldData['adjusted_AAP'] + $newData['AAP'];
    // $others = $oldData['adjusted_Others'] + $newData['Others'];
    $inc = $newData['adjusted_INC'];
    $bjp = $newData['adjusted_BJP'];
    $aap = $newData['adjusted_AAP'];
    $others = $newData['adjusted_Others'];
    $status = $newData['status'];
    if ($inc < 0 || $bjp < 0 || $aap < 0 || $others < 0 || $inc > 100 || $bjp > 100 || $aap > 100 || $others > 100) {
        // echo "<script>alert('Total Percentage should be between 0 to 100.')</script>";
        // echo "<script>event.preventDefault();</script>";
    } else {
        $sql = "UPDATE `trends` SET `adjusted_INC`= " . $inc . ",`adjusted_BJP`= " . $bjp . ",`adjusted_AAP`= " . $aap . ",`adjusted_Others`= " . $others . ",`active_status`= " . $status . " WHERE caste = '" . $newData['caste'] . "' AND report_id=" . $id;
        $conn->query($sql);

        // echo '<script>alert("Value Updated");</script>';
    }
}

// update values inclination data
function update_inclination_values($newData)
{
    $conn = connect_database();
    $id = $_GET['id'];
    $oldData = get_inclination_data($id);
    if ($oldData[0]['vote_per'] + $newData['INC'] < 0 || $oldData[0]['vote_per']  + $newData['INC'] > 100 || $oldData[1]['vote_per']  + $newData['BJP'] < 0 || $oldData[1]['vote_per']  + $newData['BJP'] > 100 || $oldData[2]['vote_per'] + $newData['AAP'] < 0 || $oldData[2]['vote_per'] + $newData['AAP'] > 100 || $oldData[3]['vote_per'] + $newData['Others'] < 0 || $oldData[3]['vote_per'] + $newData['Others'] > 100) {
        echo "<script>alert('Total Percentage should be between 0 to 100.')</script>";
        echo "<script>event.preventDefault();</script>";
    } else {
        foreach ($oldData as $party) {
            $sql = "UPDATE `inclination` SET `adjusted_voting_percentage`= " . $party['vote_per'] + $newData[$party['party']] . " WHERE party = '" . $party['party'] . "' AND report_id=" . $id;
            $conn->query($sql);
        }

        echo '<script>alert("Value Updated");</script>';
    }
}

// _____________________________________________________
//                   --get data--
// _____________________________________________________


// get total rows
function get_total_rows($tableName)
{
    $conn = connect_database();
    $sql = "SELECT COUNT(*) as total FROM " . $tableName;
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    }
    return $data[0];
}

// get assembly id
function get_assembly_id($id)
{
    $conn = connect_database();
    $sql = "SELECT assembly_id as id FROM reportInfo WHERE id = " . $id;
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    }
    return $data[0]['id'];
}

// get assembly details
function get_assembly_detail($id)
{
    $conn = connect_database();
    $sql = "SELECT assembly_id as id,report_title as title FROM reportInfo WHERE id = " . $id;
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    }
    return $data[0];
}

// get bulk report data for inserting in database
function get_bulk_report_insert($id)
{
    $conn = connect_database();
    $sql = "SELECT t1.`assembly_id` as assembly_id, t1.assembly as Assembly_Name, COUNT(*) as total 
    FROM `bulkReport` t1 
    LEFT JOIN `reportInfo` t2 ON t2.`assembly_id` = t1.`assembly_id` 
    ";

    if ($id === -1) {
        $sql = $sql . "WHERE t2.`assembly_id` IS NULL
        GROUP BY t1.assembly_id;";
    } else {
        $sql = $sql . "WHERE t2.`assembly_id` IS NULL AND t1.assembly_id = " . $id . "
        GROUP BY t1.assembly_id;";
    }
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    } else {
        echo "All data is being imported";
    }

    return $data;
}


// get caste data for caste value update
function get_caste_data($caste, $id)
{
    $conn = connect_database();
    $sql = "SELECT adjusted_INC, adjusted_BJP, adjusted_AAP, adjusted_Others from trends WHERE caste = '" . $caste . "' AND report_id=" . $id;
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    }

    return $data[0];
}

// get inclination data for inclination value update
function get_inclination_data($id)
{
    $conn = connect_database();
    $sql = "SELECT adjusted_voting_percentage as vote_per, party from inclination WHERE  report_id=" . $id;
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    }

    return $data;
}


// get trends data for inserting in database (single)
function get_data_trends($tableName, $db)
{
    if (empty($db)) {
        $msg = "Database is empty";
    } elseif (empty($tableName)) {
        $msg = "Table Name is empty";
    } else {
        $arr = ['CasteGroups', 'Occupation', 'Age', 'Education'];
        $sendData = [];
        foreach ($arr as $field) {

            if ($field === 'CasteGroups' || $field === 'Occupation' || $field === 'Education') {
                $query = '
                SELECT
                s.`' . $field . '`,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 = "कोंग्रेस (INC)" AND s.`' . $field . '` = t.`' . $field . '`) * 100.0/ COUNT(*), 1) as INC,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 = "भाजपा (BJP)" AND s.`' . $field . '` = t.`' . $field . '`) * 100.0/ COUNT(*), 1) as BJP,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 = "आप (AAP)" AND s.`' . $field . '` = t.`' . $field . '`) * 100.0/ COUNT(*), 1) as AAP,
                ROUND(((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 != "कोंग्रेस (INC)" AND Q4 != "भाजपा (BJP)" AND Q4 != "आप (AAP)" AND s.`' . $field . '` = t.`' . $field . '`)) * 100.0/ COUNT(*), 1) as Others
                FROM `' . $tableName . '` s 
                GROUP BY s.`' . $field . '`
                ORDER BY COUNT(s.`' . $field . '`) DESC
                ;
                ';
            } elseif ($field === 'Age') {
                $query = '
                SELECT
                CASE
                WHEN s.`' . $field . '` between 18 and 22 THEN "18-22"
                WHEN s.`' . $field . '` between 23 and 30 THEN "23-30"
                WHEN s.`' . $field . '` between 31 and 45 THEN "31-45"
                WHEN s.`' . $field . '` between 46 and 60 THEN "46-60"
                WHEN s.`' . $field . '` between 61 and 99 THEN "61-99"
                END as AgeGroup,
                COUNT(s.`' . $field . '`) as totalNumberOfParticipaton,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 = "कोंग्रेस (INC)" AND (t.`' . $field . '` BETWEEN LEFT(AgeGroup, 2) AND RIGHT(AgeGroup, 2) ) ) * 100.00 / COUNT(*), 1) as INC,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 = "भाजपा (BJP)" AND (t.`' . $field . '` BETWEEN LEFT(AgeGroup, 2) AND RIGHT(AgeGroup, 2) ) ) * 100.00 / COUNT(*), 1) as BJP,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 = "आप (AAP)" AND (t.`' . $field . '` BETWEEN LEFT(AgeGroup, 2) AND RIGHT(AgeGroup, 2) ) ) * 100.00 / COUNT(*), 1) as AAP,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 != "कोंग्रेस (INC)" AND Q4 != "भाजपा (BJP)" AND Q4 != "आप (AAP)" AND (t.`' . $field . '` BETWEEN LEFT(AgeGroup, 2) AND RIGHT(AgeGroup, 2) ) ) * 100.00 / COUNT(*), 1) as Others
                FROM `' . $tableName . '` s 
                GROUP BY AgeGroup
                ORDER BY AgeGroup
                ;
                ';
            }
            $result = $db->query($query);
            if ($result == true) {
                if ($result && $result->num_rows > 0) {
                    $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                    $msg = $row;
                } else {
                    $msg = "No Data Found";
                }
            } else {
                $msg = mysqli_error($db);
            }
            array_push($sendData, $msg);
        }
    }
    return $sendData;
}

// get inclination data for inserting in database (single)
function get_data_inclination($tableName, $db)
{
    $db = connect_database();
    if (empty($db)) {
        $msg = "Database is empty";
    } elseif (empty($tableName)) {
        $msg = "Table Name is empty";
    } else {
        // MAX(s.assembly_id) as assembly_id,
        $query = '
        SELECT
        ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 = "कोंग्रेस (INC)") * 100.0/ COUNT(*), 1) as INC,
        ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 = "भाजपा (BJP)") * 100.0/ COUNT(*), 1) as BJP,
        ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 = "आप (AAP)") * 100.0/ COUNT(*), 1) as AAP,
        ROUND(((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE Q4 != "कोंग्रेस (INC)" AND Q4 != "भाजपा (BJP)" AND Q4 != "आप (AAP)")) * 100.0/ COUNT(*), 1) as Others
        FROM `' . $tableName . '` s 
        ;
        ';
        // 
        $result = $db->query($query);
        if ($result == true) {
            if ($result && $result->num_rows > 0) {
                $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                $msg = $row;
            } else {
                $msg = "No Data Found";
            }
        } else {
            $msg = mysqli_error($db);
        }
    }
    
    return $msg[0];
}

// get ratings data for inserting in database (single)
function get_data_ratings($tableName, $db)
{
    if (empty($db)) {
        $msg = "Database is empty";
    } elseif (empty($tableName)) {
        $msg = "Table Name is empty";
    } else {
        $arr = ['Q1', 'Q2', 'Q3'];
        $sendData = [];
        foreach ($arr as $field) {
            $query = '
            SELECT s.`' . $field . '`, ABS(LEFT(RIGHT(s.`' . $field . '`,3),2)) as orderBy, 
            ROUND(((COUNT(*) *100.00)/(SELECT COUNT(*) FROM `' . $tableName . '`)), 1) as totalPercentage,
            (SELECT COUNT(`' . $field . '`) FROM `' . $tableName . '`t WHERE t.`' . $field . '` = s.`' . $field . '`) as total
            FROM `' . $tableName . '` s 
            GROUP BY s.`' . $field . '` 
            ORDER BY orderBy DESC
            ;
            ';
            
            $result = $db->query($query);
            if ($result == true) {
                if ($result && $result->num_rows > 0) {
                    $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                    $msg = $row;
                } else {
                    $msg = "No Data Found";
                }
            } else {
                $msg = mysqli_error($db);
            }
            array_push($sendData, $msg);
        }
    }
    return $sendData;
}


// get trends data for inserting in database (bulk)
function get_bulk_data_trends($id, $tableName, $db)
{
    if (empty($db)) {
        $msg = "Database is empty";
    } elseif (empty($tableName)) {
        $msg = "Table Name is empty";
    } else {
        $arr = ['CasteGroups', 'Occupation', 'Age', 'Education'];
        $sendData = [];
        foreach ($arr as $field) {

            if ($field === 'CasteGroups' || $field === 'Occupation' || $field === 'Education') {
                $query = '
                SELECT
                s.`' . $field . '`, MAX(s.assembly_id) as assembly_id,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.`assembly_id` AND Q4 = "कोंग्रेस (INC)" AND s.`' . $field . '` = t.`' . $field . '`) * 100.0/ COUNT(*), 1) as INC,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.`assembly_id` AND Q4 = "भाजपा (BJP)" AND s.`' . $field . '` = t.`' . $field . '`) * 100.0/ COUNT(*), 1) as BJP,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.`assembly_id` AND Q4 = "आप (AAP)" AND s.`' . $field . '` = t.`' . $field . '`) * 100.0/ COUNT(*), 1) as AAP,
                ROUND(((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.`assembly_id` AND Q4 != "कोंग्रेस (INC)" AND Q4 != "भाजपा (BJP)" AND Q4 != "आप (AAP)" AND s.`' . $field . '` = t.`' . $field . '`)) * 100.0/ COUNT(*), 1) as Others
                FROM `' . $tableName . '` s 
                WHERE s.assembly_id = ' . $id . '
                GROUP BY s.`' . $field . '`
                ORDER BY COUNT(s.`' . $field . '`) DESC
                ;
                ';
            } elseif ($field === 'Age') {
                $query = '
                SELECT
                MAX(s.assembly_id) as assembly_id,
                CASE
                WHEN s.`' . $field . '` between 18 and 22 THEN "18-22"
                WHEN s.`' . $field . '` between 23 and 30 THEN "23-30"
                WHEN s.`' . $field . '` between 31 and 45 THEN "31-45"
                WHEN s.`' . $field . '` between 46 and 60 THEN "46-60"
                WHEN s.`' . $field . '` between 61 and 99 THEN "61-99"
                END as AgeGroup,
                COUNT(s.`' . $field . '`) as totalNumberOfParticipaton,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.`assembly_id` AND Q4 = "कोंग्रेस (INC)" AND (t.`' . $field . '` BETWEEN LEFT(AgeGroup, 2) AND RIGHT(AgeGroup, 2) ) ) * 100.00 / COUNT(*), 1) as INC,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.`assembly_id` AND Q4 = "भाजपा (BJP)" AND (t.`' . $field . '` BETWEEN LEFT(AgeGroup, 2) AND RIGHT(AgeGroup, 2) ) ) * 100.00 / COUNT(*), 1) as BJP,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.`assembly_id` AND Q4 = "आप (AAP)" AND (t.`' . $field . '` BETWEEN LEFT(AgeGroup, 2) AND RIGHT(AgeGroup, 2) ) ) * 100.00 / COUNT(*), 1) as AAP,
                ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.`assembly_id` AND Q4 != "कोंग्रेस (INC)" AND Q4 != "भाजपा (BJP)" AND Q4 != "आप (AAP)" AND (t.`' . $field . '` BETWEEN LEFT(AgeGroup, 2) AND RIGHT(AgeGroup, 2) ) ) * 100.00 / COUNT(*), 1) as Others
                FROM `' . $tableName . '` s 
                GROUP BY AgeGroup
                ORDER BY AgeGroup
                ;
                ';
            }
            $result = $db->query($query);
            if ($result == true) {
                if ($result && $result->num_rows > 0) {
                    $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                    $msg = $row;
                } else {
                    $msg = "No Data Found";
                }
            } else {
                $msg = mysqli_error($db);
            }
            array_push($sendData, $msg);
        }
    }
    return $sendData;
}

// get inclination data for inserting in database (bulk)
function get_bulk_data_inclination($id, $tableName, $db)
{
    if (empty($db)) {
        $msg = "Database is empty";
    } elseif (empty($tableName)) {
        $msg = "Table Name is empty";
    } else {
        $query = '
        SELECT
        MAX(s.assembly_id) as assembly_id,
        ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.assembly_id AND Q4 = "कोंग्रेस (INC)") * 100.0/ COUNT(*), 1) as INC,
        ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.assembly_id AND  Q4 = "भाजपा (BJP)") * 100.0/ COUNT(*), 1) as BJP,
        ROUND((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.assembly_id AND  Q4 = "आप (AAP)") * 100.0/ COUNT(*), 1) as AAP,
        ROUND(((SELECT COUNT(*) FROM `' . $tableName . '` t WHERE s.assembly_id = t.assembly_id AND  Q4 != "कोंग्रेस (INC)" AND Q4 != "भाजपा (BJP)" AND Q4 != "आप (AAP)")) * 100.0/ COUNT(*), 1) as Others
        FROM `' . $tableName . '` s 
        WHERE s.assembly_id = ' . $id . '
        ;
        ';
        $result = $db->query($query);
        if ($result == true) {
            if ($result && $result->num_rows > 0) {
                $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                $msg = $row;
            } else {
                $msg = "No Data Found";
            }
        } else {
            $msg = mysqli_error($db);
        }
    }
    return $msg[0];
}

// get ratings data for inserting in database (bulk)
function get_bulk_data_ratings($id, $tableName, $db)
{
    if (empty($db)) {
        $msg = "Database is empty";
    } elseif (empty($tableName)) {
        $msg = "Table Name is empty";
    } else {
        $arr = ['Q1', 'Q2', 'Q3'];
        $sendData = [];
        foreach ($arr as $field) {
            $query = '
            SELECT s.`' . $field . '`, MAX(s.assembly_id) as assembly_id, ABS(LEFT(RIGHT(s.`' . $field . '`,3),2)) as orderBy, 
            ROUND(((COUNT(*) *100.00)/(SELECT COUNT(*) FROM `' . $tableName . '`t WHERE s.assembly_id = t.assembly_id)), 1) as totalPercentage,
            (SELECT COUNT(`' . $field . '`) FROM `' . $tableName . '`t WHERE s.assembly_id = t.assembly_id AND t.`' . $field . '` = s.`' . $field . '`) as total
            FROM `' . $tableName . '` s 
            WHERE s.assembly_id = ' . $id . '
            GROUP BY s.`' . $field . '` 
            ORDER BY orderBy DESC
            ;
            ';
            $result = $db->query($query);
            if ($result == true) {
                if ($result && $result->num_rows > 0) {
                    $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                    $msg = $row;
                } else {
                    $msg = "No Data Found";
                }
            } else {
                $msg = mysqli_error($db);
            }
            array_push($sendData, $msg);
        }
    }
    return $sendData;
}

// _____________________________________________________
//                     --fetch data--
// _____________________________________________________


// fetch all added report info to display on manage report page
function fetch_reports()
{
    $conn = connect_database();
    $sql = "SELECT * FROM reportInfo;";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    } else {
        // echo "No results";
    }

    return $data;
}

// fetch all report available for bulk insert/import
function fetch_bulk_report()
{
    $conn = connect_database();
    $sql = "SELECT t1.`assembly_id` as assembly_id, t1.assembly as Assembly_Name, COUNT(*) as total 
    FROM `bulkReport` t1 
    LEFT JOIN `reportInfo` t2 ON t2.`assembly_id` = t1.`assembly_id` 
    WHERE t2.`assembly_id` IS NULL 
    GROUP BY t1.assembly_id;";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    } else {
        echo "All data is being imported";
    }

    return $data;
}

// fetch all user data to display on manage user page
function fetch_Users()
{
    $conn = connect_database();
    $sql = "SELECT * FROM User;";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    } else {
        // echo "No results";
    }

    return $data;
}

// fetch all table name in database (to filter out available report info data later for add report)
function fetch_available_reports()
{
    $conn = connect_database();
    $sql = "SHOW TABLES";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result)) {
            $data = $row;
        }
    } else {
        // echo "No results";
    }

    return $data;
}

// fetch already added reports in database (to filter out available report info data later for add report)
function fetch_already_added_table()
{
    $conn = connect_database();
    $sql = "SELECT `report_tablename` FROM `reportInfo` WHERE `report_tablename` != '___bulk___import___';";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result)) {
            $data = array_merge(...$row);
        }
    } else {
        // echo "No results";
    }

    return $data;
}

// fetch report info data for edit report
function fetch_report_info($id)
{
    $db = connect_database();
    if (empty($db)) {
        $msg = "Database is empty";
    } else {
        $query = '
            SELECT * 
            FROM `reportInfo` 
            WHERE `id` = ' . $id . ';
            ;
            ';
        $result = $db->query($query);
        if ($result == true) {
            if ($result && $result->num_rows > 0) {
                $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                $msg = $row;
            } else {
                $msg = "No Data Found";
            }
        } else {
            $msg = mysqli_error($db);
        }
    }

    return $msg;
}

// fetch trends data for view report
function fetch_trends_data($id)
{
    $db = connect_database();
    if (empty($db)) {
        $msg = "Database is empty";
    } else {
        $arr = ['caste', 'occupation', 'age_group', 'education'];
        $sendData = [];
        foreach ($arr as $field) {
            $query = '
            SELECT `adjusted_INC`+`adjusted_BJP`+`adjusted_AAP`+`adjusted_Others` as sum,active_status as status, `' . $field . '`, adjusted_INC as INC, adjusted_BJP as BJP, adjusted_AAP as AAP, adjusted_Others as Others 
            FROM `trends` 
            WHERE `report_id` = ' . $id . ' AND `' . $field . '` != "" AND `' . $field . '` IS NOT NULL ;
            ;
            ';
            $result = $db->query($query);
            if ($result == true) {
                if ($result && $result->num_rows > 0) {
                    $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                    $msg = $row;
                } else {
                    $msg = "No Data Found";
                }
            } else {
                $msg = mysqli_error($db);
            }
            array_push($sendData, $msg);
        }
    }
    // print("<pre>".print_r($sendData,true)."</pre>");
    return $sendData;
}

// fetch printable trends data for view report
function fetch_printable_trends_data($id)
{
    $db = connect_database();
    if (empty($db)) {
        $msg = "Database is empty";
    } else {
        $arr = ['caste', 'occupation', 'age_group', 'education'];
        $sendData = [];
        foreach ($arr as $field) {
            $query = '
            SELECT `' . $field . '`, adjusted_INC as INC, adjusted_BJP as BJP, adjusted_AAP as AAP, adjusted_Others as Others 
            FROM `trends` 
            WHERE `active_status` = 1 AND `report_id` = ' . $id . ' AND `' . $field . '` != "" AND `' . $field . '` IS NOT NULL ;
            ;
            ';
            $result = $db->query($query);
            if ($result == true) {
                if ($result && $result->num_rows > 0) {
                    $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                    $msg = $row;
                } else {
                    $msg = "No Data Found";
                }
            } else {
                $msg = mysqli_error($db);
            }
            array_push($sendData, $msg);
        }
    }
    // print_r($sendData);
    return $sendData;
}

// fetch caste data for value adjustment
function fetch_caste()
{
    $id = $_GET['id'];
    $conn = connect_database();
    $sql = "SELECT caste FROM trends WHERE caste IS NOT NULL AND report_id = " . $id;
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    } else {
        // echo "No results";
    }

    return $data;
}

// fetch occupation data for value adjustment
function fetch_occupation()
{
    $id = $_GET['id'];
    $conn = connect_database();
    $sql = "SELECT occupation FROM trends WHERE occupation IS NOT NULL AND report_id = " . $id;
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    } else {
        // echo "No results";
    }

    return $data;
}

// fetch age group data for value adjustment
function fetch_age_group()
{
    $id = $_GET['id'];
    $conn = connect_database();
    $sql = "SELECT age_group FROM trends WHERE age_group IS NOT NULL AND report_id = " . $id;
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    } else {
        // echo "No results";
    }

    return $data;
}

// fetch education data for value adjustment
function fetch_education()
{
    $id = $_GET['id'];
    $conn = connect_database();
    $sql = "SELECT education FROM trends WHERE education IS NOT NULL AND report_id = " . $id;
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    } else {
        // echo "No results";
    }

    return $data;
}

// fetch past result data for view report
function fetch_past_result($id)
{
    $aid = get_assembly_id($id);
    $data = [];
    $conn = connect_database();
    $sql = "SELECT `election_type` FROM `past_results` WHERE `assembly_id` = ".$aid." GROUP BY `election_type`";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $electionType = $row;
        }
        foreach ($electionType as $key => $value) {
           $data[$value['election_type']] = [];
    
           $sql = "SELECT `year` FROM `past_results` WHERE `assembly_id` = ".$aid." AND `election_type` = '".$value['election_type']."' GROUP BY `year` ORDER BY `year` DESC";
            $result = $conn->query($sql);
            if ($result && $result->num_rows > 0) {
                while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
                    $electionYear = $row;
                }
                foreach ($electionYear as $key1 => $year) {
                    $data[$value['election_type']][$year['year']] = [];
             
                    $sql = "SELECT `name`, `party`, `votes`,`percent` FROM `past_results` WHERE `assembly_id` = ".$aid." AND `election_type` = '".$value['election_type']."' AND `year`= '".$year['year']."' ORDER BY `votes` DESC;";
                    $result = $conn->query($sql);
                     if ($result && $result && $result->num_rows > 0) {
                         while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
                             $electionData = $row;
                         }
                         foreach ($electionData as $key2 => $rowData) {
                           array_push($data[$value['election_type']][$year['year']], $rowData);
                        }
                     } else {
                         // echo "No results";
                     }
                     
                     
                }
            } else {
                // echo "No results";
            }
    
            
        }
    } else {
        // echo "No results";
    }

    

    return $data;
}

function fetch_cast_dynamics($id)
{
    $conn = connect_database();
    $aid = get_assembly_id($id);
    $sql = "SELECT `caste`, CAST(voters AS int) as voters, `voter%` FROM `caste_dynamics` WHERE `assembly_id` = ".$aid." ORDER BY `voters` DESC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    } else {
       $data = [];
    }
    return $data;
}




// _____________________________________________________
//                    --graph data--
// _____________________________________________________

function fetch_colors()
{
    $db = connect_database();
    $id = (int)$_GET['id'];
    if (empty($db)) {
        $msg = "Database connection error";
    } else {
        $query = 'SELECT color FROM `party_colours` ORDER BY id ;';
        $result = $db->query($query);
        if ($result == true) {
            if ($result && $result->num_rows > 0) {
                $row = $result->fetch_all();
                $msg = '[';
                foreach ($row as $data) {
                    $msg = $msg . '"' . $data[0] . '"' . ",";
                }
                $msg = rtrim($msg, ",") . ']';
                $msg = "'" . $msg . "'";
            } else {
                $msg = "No Data Found";
            }
        } else {
            $msg = mysqli_error($db);
        }
    }
    return $msg;
}
function fetch_data_chart()
{
    $db = connect_database();
    $id = (int)$_GET['id'];
    if (empty($db)) {
        $msg = "Database connection error";
    } else {
        $query = '
            SELECT
            (SELECT adjusted_voting_percentage FROM `inclination` t WHERE party = "INC" AND t.report_id = ' . $id . ') as INC,
            (SELECT adjusted_voting_percentage FROM `inclination` t WHERE party = "BJP" AND t.report_id = ' . $id . ') as BJP,
            (SELECT adjusted_voting_percentage FROM `inclination` t WHERE party = "AAP" AND t.report_id = ' . $id . ') as AAP,
            (SELECT adjusted_voting_percentage FROM `inclination` t WHERE party = "Others" AND t.report_id = ' . $id . ') as Others
            FROM `inclination` s
            WHERE s.report_id = ' . $id . '
            LIMIT 1
            ;
            ';
        $result = $db->query($query);
        if ($result == true) {
            if ($result && $result->num_rows > 0) {
                $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
                $msg = $row;
                // $data = convertChartData($msg);
                return $msg;
            } else {
                $msg = "No Data Found";
            }
        } else {
            $msg = mysqli_error($db);
        }
    }
    return $msg;
}

// function convertChartData($data)
// {
//     $cols = '[
//         {"id":"","label":"Party","pattern":"","type":"string"},
//         {"id":"","label":"Votes","pattern":"","type":"number"}
//       ]';
//     $str = '';
//     foreach ($data as $key => $value) {
//         if ($str === '')
//             $str = '{"c":[{"v":"' . $key . '","f":null},{"v":' . $value . ',"f":null}]}';
//         else
//             $str = $str . ',' . '{"c":[{"v":"' . $key . '","f":null},{"v":' . $value . ',"f":null}]}';
//     }
//     $rows = '[' . $str . ']';
//     $formatedData = '{
//         "cols": ' . $cols . ',
//         "rows": ' . $rows . '
//     }';
//     return $formatedData;
// }

function fetch_data_bar_chart($col, $color)
{
    $db = connect_database();
    $id = $_GET['id'];
    if (empty($db)) {
        $msg = "Database connection error";
    } else {
        $query = 'SELECT rating, ABS(LEFT(RIGHT(s.rating,3),2)) as orderBy,
        adjusted_total_votes as total, adjusted_total_percentage as total_per
        FROM `ratings` s 
        WHERE `question` = "' . $col . '" AND report_id =  ' . $id . '
        ORDER BY ABS(LEFT(RIGHT(s.rating,3),2)) DESC;
        ';
        $result = $db->query($query);
        if ($result == true) {
            if ($result && $result->num_rows > 0) {
                $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                // $msg = convertBarChartData($row, $color, $col);
                $msg = $row;
            } else {
                $msg = "No Data Found";
            }
        } else {
            $msg = mysqli_error($db);
        }
    }
    return $msg;
}

function convertBarChartData($d, $color, $col)
{
    $role = json_encode('{ "role": "style" }, { "role": "Density" } ');
    $data = '["Element", "Total Percentage", ' . json_decode($role) . ']';
    foreach ($d as $key => $value) {
        $arr = ',["' . $value['rating'] . '", ' . $value['total_per'] . ', "' . $color . '", "' . $value['total'] . '"]';
        $data = $data . $arr;
    }

    return '[' . $data . ']';
}

function fetch_party_color()
{
    $conn = connect_database();
    $sql = "SELECT *
    FROM `party_colours`";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = mysqli_fetch_all($result, MYSQLI_ASSOC)) {
            $data = $row;
        }
    } else {
        echo "All data is being imported";
    }
    return $data;
}