<?php
session_start();
include "../assets/constant/config.php";



try {
   $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
   echo "Connection failed: " . $e->getMessage();
}
// Query to fetch contribution by day
$totalContributionQuery = "SELECT DATE(date) as day, SUM(amount) as total FROM contribution GROUP BY DATE(date)";
$totalContributionResult = $conn->query($totalContributionQuery);

// Query to fetch pledges by day
$totalPledgesQuery = "SELECT DATE(date) as day, SUM(total_pledges) as total FROM pledges GROUP BY DATE(date)";
$totalPledgesResult = $conn->query($totalPledgesQuery);

// Query to fetch income by day
$totalIncomeQuery = "SELECT DATE(date) as day, SUM(total_income) as total FROM income GROUP BY DATE(date)";
$totalIncomeResult = $conn->query($totalIncomeQuery);

$income = [];
while ($row = $totalContributionResult->fetch(PDO::FETCH_ASSOC)) {
    $day = $row['day'];
    $income[$day]['contribution'] = $row['total'];
}

while ($row = $totalPledgesResult->fetch(PDO::FETCH_ASSOC)) {
    $day = $row['day'];
    if (!isset($income[$day])) {
        $income[$day] = ['contribution' => 0];
    }
    $income[$day]['pledges'] = $row['total'];
}

while ($row = $totalIncomeResult->fetch(PDO::FETCH_ASSOC)) {
    $day = $row['day'];
    if (!isset($income[$day])) {
        $income[$day] = ['contribution' => 0, 'pledges' => 0];
    }
    $income[$day]['income'] = $row['total'];
}

// Convert data to JSON format for use in JavaScript
$incomeJson = json_encode($income);


?>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<?php include('include/sidebar.php'); ?>
<!-- Top Bar End -->
<?php include('include/header.php'); ?>

<!--  connect author at mdkhairnar92@gmail.com  -->
<div id="myAlert" class="alert alert-success alert-dismissible fade show" role="alert">

   <strong>Welcome!</strong> Login Successfully.
</div>
<div class="page-content-wrapper ">
   <div class="container-fluid">
      <div class="row">
         <div class="col-sm-12">
            <div class="page-title-box">
               <div class="btn-group float-right">
                  <ol class="breadcrumb hide-phone p-0 m-0">
                     <li class="breadcrumb-item active"></li>
                  </ol>
               </div>
               <marquee direction="left" behavior="alternate" scrollamount=1 >
   <h5 style="color:red"><b>Alert : Don't Sale or Publish this script with your name. However you can use it for Academic Purpose !</b></h5>
</marquee>
                <h3>I can Help you !</h3>
                <p><b>I can build dynamic and interactive websites and web application with PHP.<br>
My work offers complete solutions to your web development needs, from design and development to deployment and maintenance.<br>connect me at <a href="mailto:mayuri.infospace@gmail.com">mayuri.infospace@gmail.com</p></b>

            </div>
         </div>
         <div class="clearfix"></div>
      </div>
      <!-- end page title end breadcrumb -->
      <div class="row dashboard">
         <div class="col-lg-4">
            <div class="card bg-success">
               <div class="card-body py-4">
                  <div class="d-flex flex-row">
                     <div class="col-3 align-self-center">
                        <div class="round">
                           <i data-feather="user"></i>
                        </div>
                     </div>
                     <div class="col-9 align-self-center text-right">
                        <div class="m-l-10">
                           <h3 class="mt-0 text-white">
                              <?php
                              $stmt = $conn->prepare("SELECT count(*) as cnt_name from member WHERE delete_status='0' ");
                              $stmt->execute();
                              $record = $stmt->fetch(PDO::FETCH_ASSOC); ?>
                              <?PHP echo $record['cnt_name'];
                              $name = $record['cnt_name']; ?>
                           </h3>
                           <p class="mb-0 text-white">Total Member


                           </p>
                        </div>
                     </div>
                  </div>


               </div>
               <!--end card-body-->
            </div>
            <!--end card-->

         </div>

         <!--end col-->

         <!--end col-->
         <div class="col-lg-4">
            <div class="card bg-primary">
               <div class="card-body py-4">
                  <div class="d-flex flex-row">
                     <div class="col-3 align-self-center">
                        <div class="round">
                           <i class="fa fa-money"></i>
                        </div>
                     </div>
                     <div class="col-9 align-self-center text-right">
                        <div class="m-l-10">
                           <h3 class="mt-0 text-white">
                              <?php
                              $stmt = $conn->prepare("SELECT sum(amount) as cnt_name from contribution WHERE delete_status='0' ");
                              $stmt->execute();
                              $record = $stmt->fetch(PDO::FETCH_ASSOC); ?>
                              <?PHP echo $record['cnt_name'];
                              $name = $record['cnt_name']; ?>
                           </h3>
                           <p class="mb-0 text-white">Total Contribution


                           </p>
                        </div>
                     </div>
                  </div>


               </div>
               <!--end card-body-->
            </div>
            <!--end card-->

         </div>

         <div class="col-lg-4">
            <div class="card bg-secondary">
               <div class="card-body py-4">
                  <div class="d-flex flex-row">
                     <div class="col-3 align-self-center">
                        <div class="round">
                           <i data-feather="file"></i>
                        </div>
                     </div>
                     <div class="col-9 align-self-center text-right">
                        <div class="m-l-10">
                           <h3 class="mt-0 text-white">
                              <?php
                              $stmt = $conn->prepare("SELECT SUM(total_pledges) as cnt_pladge from pledges  ");
                              $stmt->execute();
                              $record = $stmt->fetch(PDO::FETCH_ASSOC); ?>
                              <?PHP echo $record['cnt_pladge'];
                              $name = $record['cnt_pladge']; ?>
                           </h3>
                           <p class="mb-0 text-white">Total Pledge


                           </p>
                        </div>
                     </div>
                  </div>


               </div>
               <!--end card-body-->
            </div>
            <!--end card-->

         </div>

        </div>
      <!--end row-->
     <!--google chart-->


      <!-- Google Chart for contributions -->
        <div class="row">
            <div class="col-md-6">
                <div class="card">
                    <div class="card-body">
                        <!-- Display Google Chart -->
                        <div id="linechart_material" style="width:100%; height:500px;"></div>
                       
                    </div>
                </div>
            </div>
        </div>



 <div class="row">
            <div class="col-md-6">
                <div class="card">
                    <div class="card-body">
                        <!-- Display Google Chart -->
                       
                        <div id="piechart_material" style="width: 100%; height: 500px;"></div> 
                    </div>
                </div>
            </div>
        </div>

    

    </div>
</div>


<?php include('include/footer.php'); ?>
<!--  Author Name: Mayuri K. 
 for any PHP, Codeignitor or Laravel website or software development contact me at mayuri.infospace@gmail.com  -->


<!-- Load Google Charts -->
<script>
    // Load Google Charts
    google.charts.load('current', {'packages':['line']});

    google.charts.setOnLoadCallback(drawLineChart);

    function drawLineChart() {
        var incomeData = <?php echo $incomeJson; ?>;
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Day');
        data.addColumn('number', 'Contribution');
        data.addColumn('number', 'Pledge');
        data.addColumn('number', 'Income');

        var rows = [];
        for (var day in incomeData) {
            if (incomeData.hasOwnProperty(day)) {
                var contribution = parseFloat(incomeData[day]['contribution']) || 0;
                var pledge = parseFloat(incomeData[day]['pledges']) || 0;
                var income = parseFloat(incomeData[day]['income']) || 0;
                rows.push([day, contribution, pledge, income]);
            }
        }

        data.addRows(rows);

        var options = {
            chart: {
                title: 'Income Overview',
                subtitle: 'in dollars (USD)'
            },
            width: 500,
            height: 300
        };

        var chart = new google.charts.Line(document.getElementById('linechart_material'));

        chart.draw(data, google.charts.Line.convertOptions(options));
    }// Load Google Charts
google.charts.load('current', {'packages':['line']});

google.charts.setOnLoadCallback(drawLineChart);

function drawLineChart() {
    var incomeData = <?php echo $incomeJson; ?>; // Check this JSON data in the console
    console.log(incomeData); // Check data in the console to ensure it's in the expected format

    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Day');
    data.addColumn('number', 'Contribution');
    data.addColumn('number', 'Pledge');
    data.addColumn('number', 'Income');

    var rows = [];
    for (var day in incomeData) {
        if (incomeData.hasOwnProperty(day)) {
            var contribution = parseFloat(incomeData[day]['contribution']) || 0;
            var pledge = parseFloat(incomeData[day]['pledges']) || 0;
            var income = parseFloat(incomeData[day]['income']) || 0;
            rows.push([day, contribution, pledge, income]);
        }
    }

    data.addRows(rows);

    var options = {
        chart: {
            title: 'Income Overview',
            subtitle: 'in dollars (USD)'
        },
        width: 500,
        height: 300
    };

    // Ensure the target element exists in your HTML before drawing the chart
    var chartElement = document.getElementById('linechart_material');
    if (chartElement) {
        var chart = new google.charts.Line(chartElement);
        chart.draw(data, google.charts.Line.convertOptions(options));
    } else {
        console.error('Chart element not found.');
    }
}
</script>





<!---PIE chart-->

<script>
    google.charts.load('current', {'packages':['corechart']});

    google.charts.setOnLoadCallback(drawPieChart);

    function drawPieChart() {
        var incomeData = <?php echo $incomeJson; ?>;
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Category');
        data.addColumn('number', 'Total');

        // Prepare data rows for the pie chart
        var rows = [];
        for (var category in incomeData) {
            if (incomeData.hasOwnProperty(category)) {
                var total = parseFloat(incomeData[category]['contribution']) +
                            parseFloat(incomeData[category]['pledges']) +
                            parseFloat(incomeData[category]['income']);
                rows.push([category, total]);
            }
        }

        data.addRows(rows);

        var options = {
            title: 'Income Overview by Category',
            width: '100%',
            height: 200
        };

var chart = new google.visualization.PieChart(document.getElementById('piechart_material'));
        chart.draw(data, options);
    }
</script>
