<!-- Page Heading -->
                <div class="row">
                    <div class="col-lg-12">
                        <h1 class="page-header">
                           Dashboard 
                        
                        </h1>
                    </div>
                </div>

<div class="panel panel-green"><!--Initiative-->
<div class="panel-heading"><h3 class="panel-title">Table 1: Expectation Vs Actual income | by Term</h3></div>
<div class="panel-body">

  <form method='post'>
  <div class='col-lg-2'>
<select class='form-control' name='schoolyear' required>
  <option value=''>Year</option>
<?php
include ("connect.php");
include ("../gentools/expected.php");
$result = $dbh->query("select * from scrap where type='ya'");
$row = $result->fetchObject();
$count = $result->rowCount();



if($count>0){
do{
echo "
<option value='".$row->item."'>".$row->item2."</option>";
}while($row = $result->fetchObject());
}else{echo "<option value=''>Contact the IT department</option>";

}
?>
</select>
  </div>
  <div class='col-lg-2'>
    <input type='submit' name='submit500' class='btn btn-sm btn-success' value='Filter'>
  </form>
</div>
<div class='col-lg-4'>
<?php echo "Showing 20".$schoolyear. " | <a href='".$systemdir."/gentools/comparison.php'>View by class</a>"; ?>
</div>
</form>
<table class="table table-bordered table-hover table-striped sortable">
<thead>
  <tr>
    <th></th>
    <th>Expected</th>
    <th>Income</th>
  <th>Variance</th>
  <th>%</th>
  </tr>
</thead>
<tbody>
  <?php
  
//below: expenditure per term, first table

//below:variance
if ($row_exp_t1->t1_totalexpected>'0'){
$incterm1=$row_inc_t1->t1_totalincome;
$expterm1=$row_exp_t1->t1_totalexpected;
$varit1=$incterm1-$expterm1;
$varit1percent=($incterm1/$expterm1)*100;
}
else
{
$incterm1=0;
$expterm1=0;
$varit1=0;
$varit1percent=0;
}
if ($row_exp_t2->t2_totalexpected>'0'){
$incterm2=$row_inc_t2->t2_totalincome;
$expterm2=$row_exp_t2->t2_totalexpected;
$varit2=$incterm2-$expterm2;
$varit2percent=($incterm2/$expterm2)*100;
}
else
{
$incterm2=0;
$expterm2=0;
$varit2=0;
$varit2percent=0;
}
if ($row_exp_t3->t3_totalexpected>'0'){
$incterm3=$row_inc_t3->t3_totalincome;
$expterm3=$row_exp_t3->t3_totalexpected;
$varit3=$incterm3-$expterm3;
$varit3percent=($incterm3/$expterm3)*100;
}
else
{
$incterm3=0;
$expterm3=0;
$varit3=0;
$varit3percent=0;
}
?>
  <tr>
  <td>Term 1</td>
  <td><?php echo number_format($expterm1); ?></td>
  <td><?php echo number_format($incterm1); ?></td>
  <td><?php echo number_format($varit1); ?></td>
  <td><?php echo number_format($varit1percent,0)."%"; ?></td>
  </tr>
  <tr>
  <td>Term 2</td>
  <td><?php echo number_format($expterm2); ?></td>
  <td><?php echo number_format($incterm2); ?></td>
  <td><?php echo number_format($varit2); ?></td>
  <td><?php echo number_format($varit2percent,0)."%"; ?></td>

  </tr>
  <tr>
  <td>Term 3</td>
   <td><?php echo number_format($expterm3); ?></td>
   <td><?php echo number_format($incterm3); ?></td>
  <td><?php echo number_format($varit3); ?></td>
  <td><?php echo number_format($varit3percent,0)."%"; ?></td>
  </tr>
  
  </tbody>
</table>
</div>
</div>
<?php
//Primary requirements
echo "
<div class='panel panel-yellow'>
<div class='panel-heading'>
<h3 class='panel-title'>Table 2: Primary requirements</h3>
</div>
<div class='panel-body'>
<form method='post'>
<div class='col-lg-2'>
<select name='pri_schoolyear' class='form-control'>";

$cm=date("Y");
$cm2=date("y");

echo "<option value=".$cm2.">".$cm."</option>
<option value='17'>2017</option>
<option value='18'>2018</option>
<option value='19'>2019</option>
<option value='20'>2020</option>
<option value='16'>2016</option>
</select>
</div>

<div class='col-lg-2'>
<select name='pri_term' class='form-control'>
<option value=''>Term</option>
<option value='t1'>Term 1</option>
<option value='t2'>Term 2</option>
<option value='t3'>Term 3</option>
</select>
</div>
<div class='col-lg-2'>
<input type='submit' class='btn btn-sm btn-primary' name='pri_filter' value='Filter'>
</div> 
</form>";

include("connect.php");

if(isset($_POST["pri_filter"])){
$pri_schoolyear=$_POST["pri_schoolyear"];
$pri_term=$_POST["pri_term"];

$pri_yeartotake="schoolyear='$pri_schoolyear'";
$pri_termtotake=$pri_term;
}else{
    $cy=date("y");
$pri_yeartotake="schoolyear='$cy'";


$cm = date("m");
if ($cm>='1' and $cm<='4'){$pri_termtotake="t1";}
elseif ($cm>='5' and $cm<='8'){$pri_termtotake="t2";}
elseif ($cm>='9' and $cm<='12'){$pri_termtotake="t3";}

}

$pri_range="((cid>='10000')&&(cid<='49999')) and";  

// Expected
$result_own=$dbh->query("select sum(ownfee) from $pri_termtotake where $pri_range $pri_yeartotake and active='1'");
$row_own=$result_own->fetch();

$result_cont=$dbh->query("select sum(contribution) from $pri_termtotake where $pri_range $pri_yeartotake and active='1'");
$row_cont=$result_cont->fetch();

$result_mockfa=$dbh->query("select sum(mockfa) from $pri_termtotake where $pri_range $pri_yeartotake and active='1'");
$row_mockfa=$result_mockfa->fetch();

$result_uni=$dbh->query("select sum(uniform) from $pri_termtotake where $pri_range $pri_yeartotake and active='1'");
$row_uni=$result_uni->fetch();

$result_o3=$dbh->query("select sum(other3) from $pri_termtotake where $pri_range $pri_yeartotake and active='1'");
$row_o3=$result_o3->fetch();

$result_reg=$dbh->query("select sum(registration) from $pri_termtotake where $pri_range $pri_yeartotake and active='1'");
$row_reg=$result_reg->fetch();

$result_adm=$dbh->query("select sum(admission) from $pri_termtotake where $pri_range $pri_yeartotake and active='1'");
$row_adm=$result_adm->fetch();

$exp_total=$row_own[0]+$row_cont[0]+$row_mockfa[0]+$row_uni[0]+$row_o3[0]+$row_reg[0]+$row_adm[0];
// Expected

// Collected
$result_ownp=$dbh->query("select sum(p_fees) from received where $pri_range $pri_yeartotake");
$row_ownp=$result_ownp->fetch();

$result_contp=$dbh->query("select sum(p_contribution) from received where $pri_range $pri_yeartotake");
$row_contp=$result_contp->fetch();

$result_mockfap=$dbh->query("select sum(p_mockfa) from received where $pri_range $pri_yeartotake");
$row_mockfap=$result_mockfap->fetch();

$result_unip=$dbh->query("select sum(p_uniform) from received where $pri_range $pri_yeartotake");
$row_unip=$result_unip->fetch();

$result_o3p=$dbh->query("select sum(p_other3) from received where $pri_range $pri_yeartotake");
$row_o3p=$result_o3p->fetch();

$result_regp=$dbh->query("select sum(p_registration) from received where $pri_range $pri_yeartotake");
$row_regp=$result_regp->fetch();

$result_admp=$dbh->query("select sum(p_admission) from received where $pri_range $pri_yeartotake");
$row_admp=$result_admp->fetch();

$coll_total=$row_ownp[0]+$row_contp[0]+$row_mockfap[0]+$row_unip[0]+$row_o3p[0]+$row_regp[0]+$row_admp[0];
// Collected

// achievement
if($row_own[0]>0){$ach_own=($row_ownp[0]/$row_own[0])*100;}else{$ach_own=0;}
if($row_cont[0]>0){$ach_cont=($row_contp[0]/$row_cont[0])*100;}else{$ach_cont=0;}
if($row_mockfa[0]>0){$ach_mockfa=($row_mockfap[0]/$row_mockfa[0])*100;}else{$ach_mockfa=0;}
if($row_uni[0]>0){$ach_uni=($row_unip[0]/$row_uni[0])*100;}else{$ach_uni=0;}
if($row_o3[0]>0){$ach_o3=($row_o3p[0]/$row_o3[0])*100;}else{$ach_o3=0;}
if($row_reg[0]>0){$ach_reg=($row_regp[0]/$row_reg[0])*100;}else{$ach_reg=0;}
if($row_adm[0]>0){$ach_adm=($row_admp[0]/$row_adm[0])*100;}else{$ach_adm=0;}
if($exp_total>0){$ach_tot=($coll_total/$exp_total)*100;}else{$ach_tot=0;}
// achievement
echo "
<table class='table table-bordered table-hover table-striped sortable'>
<tr>
<th>Item</th>
<th>Expected</th>
<th>Collected</th>
<th>Achievement (%)</th>
</tr>
<tr>
<td>School fees</td>
<td>".number_format($row_own[0])."</td>
<td>".number_format($row_ownp[0])."</td>
<td>".number_format($ach_own,2)."</td>
</tr>
<tr>
<td>Contribution</td>
<td>".number_format($row_cont[0])."</td>
<td>".number_format($row_contp[0])."</td>
<td>".number_format($ach_cont,2)."</td>
</tr>
<tr>
<td>Mock/Facilitation</td>
<td>".number_format($row_mockfa[0])."</td>
<td>".number_format($row_mockfap[0])."</td>
<td>".number_format($ach_mockfa,2)."</td>
</tr>
<tr>
<td>Uniform</td>
<td>".number_format($row_uni[0])."</td>
<td>".number_format($row_unip[0])."</td>
<td>".number_format($ach_uni,2)."</td>
</tr>
<tr>
<td>Admission</td>
<td>".number_format($row_adm[0])."</td>
<td>".number_format($row_admp[0])."</td>
<td>".number_format($ach_adm[0],2)."</td>
</tr>
<tr>
<td>Registration</td>
<td>".number_format($row_reg[0])."</td>
<td>".number_format($row_regp[0])."</td>
<td>".number_format($ach_reg,2)."</td>
</tr>
<tr>
<td>Other 3</td>
<td>".number_format($row_o3[0])."</td>
<td>".number_format($row_o3p[0])."</td>
<td>".number_format($ach_o3,2)."</td>
</tr>
<tr>
<td><b>Total</b></td>
<td><b>".number_format($exp_total)."</b></td>
<td><b>".number_format($coll_total)."</b></td>
<td><b>".number_format($ach_tot,2)."</b></td>
</tr>
</table>

</div>
</div>";
//Primary requirements
//Secondary requirements
echo "
<div class='panel panel-info'>
<div class='panel-heading'>
<h3 class='panel-title'>Table 3: Secondary requirements</h3>
</div>
<div class='panel-body'>
<form method='post'>
<div class='col-lg-2'>
<select name='sec_schoolyear' class='form-control'>";

$cm=date("Y");
$cm2=date("y");

echo "<option value=".$cm2.">".$cm."</option>
<option value='17'>2017</option>
<option value='18'>2018</option>
<option value='19'>2019</option>
<option value='20'>2020</option>
<option value='16'>2016</option>
</select>
</div>

<div class='col-lg-2'>
<select name='sec_term' class='form-control'>
<option value=''>Term</option>
<option value='t1'>Term 1</option>
<option value='t2'>Term 2</option>
<option value='t3'>Term 3</option>
</select>
</div>
<div class='col-lg-2'>
<input type='submit' class='btn btn-sm btn-primary' name='sec_filter' value='Filter'>
</div> 
</form>";

include("connect.php");

if(isset($_POST["sec_filter"])){
$sec_schoolyear=$_POST["sec_schoolyear"];
$sec_term=$_POST["sec_term"];

$sec_yeartotake="schoolyear='$sec_schoolyear'";
$sec_termtotake=$sec_term;
}else{
    $cy=date("y");
$sec_yeartotake="schoolyear='$cy'";


$cm = date("m");
if ($cm>='1' and $cm<='4'){$sec_termtotake="t1";}
elseif ($cm>='5' and $cm<='8'){$sec_termtotake="t2";}
elseif ($cm>='9' and $cm<='12'){$sec_termtotake="t3";}
}

$sec_range="cid>='50000' and";  

// Expected
$result_own=$dbh->query("select sum(ownfee) from $sec_termtotake where $sec_range $sec_yeartotake and active='1'");
$row_own=$result_own->fetch();

$result_cont=$dbh->query("select sum(contribution) from $sec_termtotake where $sec_range $sec_yeartotake and active='1'");
$row_cont=$result_cont->fetch();

$result_mockfa=$dbh->query("select sum(mockfa) from $sec_termtotake where $sec_range $sec_yeartotake and active='1'");
$row_mockfa=$result_mockfa->fetch();

$result_uni=$dbh->query("select sum(uniform) from $sec_termtotake where $sec_range $sec_yeartotake and active='1'");
$row_uni=$result_uni->fetch();

$result_o3=$dbh->query("select sum(other3) from $sec_termtotake where $sec_range $sec_yeartotake and active='1'");
$row_o3=$result_o3->fetch();

$result_reg=$dbh->query("select sum(registration) from $sec_termtotake where $sec_range $sec_yeartotake and active='1'");
$row_reg=$result_reg->fetch();

$result_adm=$dbh->query("select sum(admission) from $sec_termtotake where $sec_range $sec_yeartotake and active='1'");
$row_adm=$result_adm->fetch();

$exp_total=$row_own[0]+$row_cont[0]+$row_mockfa[0]+$row_uni[0]+$row_o3[0]+$row_reg[0]+$row_adm[0];
// Expected

// Collected
$result_ownp=$dbh->query("select sum(p_fees) from received where $sec_range $sec_yeartotake");
$row_ownp=$result_ownp->fetch();

$result_contp=$dbh->query("select sum(p_contribution) from received where $sec_range $sec_yeartotake");
$row_contp=$result_contp->fetch();

$result_mockfap=$dbh->query("select sum(p_mockfa) from received where $sec_range $sec_yeartotake");
$row_mockfap=$result_mockfap->fetch();

$result_unip=$dbh->query("select sum(p_uniform) from received where $sec_range $sec_yeartotake");
$row_unip=$result_unip->fetch();

$result_o3p=$dbh->query("select sum(p_other3) from received where $sec_range $sec_yeartotake");
$row_o3p=$result_o3p->fetch();

$result_regp=$dbh->query("select sum(p_registration) from received where $sec_range $sec_yeartotake");
$row_regp=$result_regp->fetch();

$result_admp=$dbh->query("select sum(p_admission) from received where $sec_range $sec_yeartotake");
$row_admp=$result_admp->fetch();

$coll_total=$row_ownp[0]+$row_contp[0]+$row_mockfap[0]+$row_unip[0]+$row_o3p[0];
// Collected

// achievement
if($row_own[0]>0){$ach_own=($row_ownp[0]/$row_own[0])*100;}else{$ach_own=0;}
if($row_cont[0]>0){$ach_cont=($row_contp[0]/$row_cont[0])*100;}else{$ach_cont=0;}
if($row_mockfa[0]>0){$ach_mockfa=($row_mockfap[0]/$row_mockfa[0])*100;}else{$ach_mockfa=0;}
if($row_uni[0]>0){$ach_uni=($row_unip[0]/$row_uni[0])*100;}else{$ach_uni=0;}
if($row_o3[0]>0){$ach_o3=($row_o3p[0]/$row_o3[0])*100;}else{$ach_o3=0;}
if($row_reg[0]>0){$ach_reg=($row_regp[0]/$row_reg[0])*100;}else{$ach_reg=0;}
if($row_adm[0]>0){$ach_adm=($row_admp[0]/$row_adm[0])*100;}else{$ach_adm=0;}
if($exp_total>0){$ach_tot=($coll_total/$exp_total)*100;}else{$ach_tot=0;}
// achievement
echo "
<table class='table table-bordered table-hover table-striped sortable'>
<tr>
<th>Item</th>
<th>Expected</th>
<th>Collected</th>
<th>Achievement (%)</th>
</tr>
<tr>
<td>School fees</td>
<td>".number_format($row_own[0])."</td>
<td>".number_format($row_ownp[0])."</td>
<td>".number_format($ach_own,2)."</td>
</tr>
<tr>
<td>Contribution</td>
<td>".number_format($row_cont[0])."</td>
<td>".number_format($row_contp[0])."</td>
<td>".number_format($ach_cont,2)."</td>
</tr>
<tr>
<td>Mock/Facilitation</td>
<td>".number_format($row_mockfa[0])."</td>
<td>".number_format($row_mockfap[0])."</td>
<td>".number_format($ach_mockfa,2)."</td>
</tr>
<tr>
<td>Uniform</td>
<td>".number_format($row_uni[0])."</td>
<td>".number_format($row_unip[0])."</td>
<td>".number_format($ach_uni,2)."</td>
</tr>
<tr>
<td>Admission</td>
<td>".number_format($row_adm[0])."</td>
<td>".number_format($row_admp[0])."</td>
<td>".number_format($ach_adm  ,2)."</td>
</tr>
<tr>
<td>Registration</td>
<td>".number_format($row_reg[0])."</td>
<td>".number_format($row_regp[0])."</td>
<td>".number_format($ach_reg,2)."</td>
</tr>
<tr>
<td>Other 3</td>
<td>".number_format($row_o3[0])."</td>
<td>".number_format($row_o3p[0])."</td>
<td>".number_format($ach_o3,2)."</td>
</tr>
<tr>
<td><b>Total</b></td>
<td><b>".number_format($exp_total)."</b></td>
<td><b>".number_format($coll_total)."</b></td>
<td><b>".number_format($ach_tot,2)."</b></td>
</tr>
</table>

</div>
</div>";
//Secondary requirements
?>

<div class="panel panel-primary"><!--Initiative-->
<div class="panel-heading"><h3 class="panel-title">Table 4: Expectation vs Actual income | by section</h3></div>
<div class="panel-body">
<?php 
include ("connect.php");
include ("../gentools/expected2.php");?>
  <form method='post'>
  <div class='col-lg-2'>
<select class='form-control' name='schoolyear' required>
  <option value=''>Year</option>
<?php
$result = $dbh->query("select * from scrap where type='ya'");
$row = $result->fetchObject();
$count = $result->rowCount();
if($count>0){
do{
echo "
<option value='".$row->item."'>".$row->item2."</option>";
}while($row = $result->fetchObject());
}else{echo "<option value=''>Contact the IT department</option>";}
?>
</select>
  </div>

<div class='col-lg-2'>
<select class='form-control' name='term' required>
  <option value=''>Term</option>
<?php
$resultts = $dbh->query("select * from scrap where type='ts'");
$rowts = $resultts->fetchObject();
$countts = $resultts->rowCount();
if($countts>0){
do{
echo "
<option value='".$rowts->item."'>".$rowts->item2."</option>
";
}while($rowts = $resultts->fetchObject());
echo"</select>";
}else{echo "<option value=''>Contact the IT department</option>";}


?></div>
<div class='col-lg-2'><input type='submit' name='submit600' class='btn btn-sm btn-success' value='Filter'></div>


<div class='col-lg-4'>Now showing: <?php echo "Term ".$tid." 20".$selectyear; ?> </div>

</form>
  
  



 <table class="table table-bordered table-hover table-striped sortable">
<thead>
  <tr>
    <th></th>
    <th>Expected</th>
    <th>Income</th>
  <th>Variance</th>
  <th>%</th>
  </tr>
</thead>
<tbody>
  <?php


?>
  <tr>
  <td>Primary</td>
  <td><?php echo number_format($sum_pri_expected); ?></td>
  <td><?php echo number_format($sum_pri_income); ?></td>
  <td><?php echo number_format($pri_variance); ?></td>
  <td><?php echo $pri_variance_p; ?></td>
  </tr>
  <tr>
  <td>Secondary</td>
  <td><?php echo number_format($sum_sec_expected); ?></td>
  <td><?php echo number_format($sum_sec_income); ?></td>
  <td><?php echo number_format($sec_variance); ?></td>
  <td><?php echo number_format($sec_variance_p); ?></td>
  </tr>
  <tr>
  <td>Total</td>
  <td><?php echo number_format($all_expected); ?></td>
  <td><?php echo number_format($all_income); ?></td>
  <td><?php echo number_format($all_variance); ?></td>
  <td><?php echo number_format($all_variance_p); ?></td>
  </tr>
  
  </tbody>
</table>
</div>
</div>

<div class="panel panel-green"><!--Initiative-->
<div class="panel-heading"><h3 class="panel-title">Table 5: Income vs Expenditure | 6 months recap</h3></div>
<div class="panel-body">
  
<table class="table table-bordered table-hover table-striped sortable">
<thead>
  <tr>
    <th></th>
    <th>Gross Revenue</th>
    <th>Total Expenses</th>
  <th>Net Income</th>
  </tr>
</thead>
<tbody>
  <?php
  $var_cm=$rowcmincome[0]-$rowcmexpenses[0];

  $var_cm1=$rowcm1income[0]-$rowcm1expenses[0];

  $var_cm2=$rowcm2income[0]-$rowcm2expenses[0];

  $var_cm3=$rowcm3income[0]-$rowcm3expenses[0];

  $var_cm4=$rowcm4income[0]-$rowcm4expenses[0];

  $var_cm5=$rowcm5income[0]-$rowcm5expenses[0];


  //$var_cm3_p=($rowcm3expenses[0]/$rowcm3income[0])*100;

?>

  <tr>
  <td><?php echo $cmfull;?></td>
  <td><?php echo number_format($rowcmincome[0]);?></td>
  <td><?php echo "<a href='monthexpense.php?month=".$cm."'>".number_format($rowcmexpenses[0])."</a>";?></td>
  <td><?php echo number_format($var_cm);?></td>

  </tr>
    <tr>
  <td><?php echo $cm1full;?></td>
  <td><?php echo number_format($rowcm1income[0]);?></td>
  <td><?php echo "<a href='monthexpense.php?month=".$cm1."'>".number_format($rowcm1expenses[0])."</a>"; ?></td>
  <td><?php echo number_format($var_cm1);?></td>

  </tr>

  <tr>
  <td><?php echo $cm2full;?></td>
  <td><?php echo number_format($rowcm2income[0]);?></td>
  <td><?php echo "<a href='monthexpense.php?month=".$cm2."'>".number_format($rowcm2expenses[0])."</a>"; ?></td>
  <td><?php echo number_format($var_cm2);?></td>

  </tr>
  <tr>
  <td><?php echo $cm3full;?></td>
  <td><?php echo number_format($rowcm3income[0]);?></td>
  <td><?php echo "<a href='monthexpense.php?month=".$cm3."'>".number_format($rowcm3expenses[0])."</a>"; ?></td>
  <td><?php echo number_format($var_cm3);?></td>

  </tr>
  <tr>
  <td><?php echo $cm4full;?></td>
  <td><?php echo number_format($rowcm4income[0]);?></td>
  <td><?php echo "<a href='monthexpense.php?month=".$cm4."'>".number_format($rowcm4expenses[0])."</a>"; ?></td>
  <td><?php echo number_format($var_cm4);?></td>

  </tr>
  <tr>
  <td><?php echo $cm5full;?></td>
  <td><?php echo number_format($rowcm5income[0]);?></td>
  <td><?php echo "<a href='monthexpense.php?month=".$cm5."'>".number_format($rowcm5expenses[0])."</a>"; ?></td>
  <td><?php echo number_format($var_cm5);?></td>
  </tr>

 
  </tbody>
</table>
</div>
</div>
</div>
                <!-- /.row -->

                   
              