<?php
	 $DB =& DbHelper(); // Create instance of the database helper class by DbHelper() (for main database) or DbHelper("<dbname>") (for linked databases) where <dbname> is database variable name
 ?>
 <div class="card">
	 <div class="card-header"><h3 class="card-title">Monthly Report</h3></div>
	 <div class="card-body p-0">
 <?php
 $sql = "SELECT abscount.Date,  hostel.Hostel_Name, floor.Floor, Count(DISTINCT admission.id) AS Strength, Sum(DISTINCT abscount.Absent) AS Absent, Count(DISTINCT admission.id) - Sum(DISTINCT abscount.Absent) AS Present  FROM admission INNER JOIN hostel ON hostel.id = admission.Hostel  INNER JOIN floor ON floor.id = admission.Floor  INNER JOIN abscount ON hostel.id = abscount.Hostel AND floor.id = abscount.Floor  WHERE (abscount.Date) BETWEEN (abscount.Date = 2019-11-01 AND LAST_DAY(abscount.Date) GROUP BY abscount.Date, hostel.Hostel_Name, floor.Floor ORDER BY abscount.Floor";
 echo $DB->ExecuteHtml($sql, ["fieldcaption" => TRUE, "tablename" => ["hostel", "floor", "abscount"]]); // Execute a SQL and show as HTML table
 ?>
	 </div>
 </div>
 //SELECT FROM table_name WHERE DATE_FIELD between '2019-01-01' and LAST_DAY('2019-02-01')

