<?php
   $date = new DateTime("now", new DateTimeZone('Africa/Nairobi') );
   $timenow=$date->format('Y-m-d H:i:s');
   $timenow2=$date->format('Y-m-d');
   $month=$date->format('F');
   //$finaldate='Today: '.$date
   
   $y=intval($_GET['Year']);
   if($y=='' || $y==null)
   {
   $yr=$date->format('Y');
   }else
   {
     $yr=$y;          
   }
   
   //include('feeincome.php');
   ?>
<div class="content-wrapper">
   <!-- Content Header (Page header) -->
   <div class="content-header">
      <div class="container-fluid">
         <div class="row mb-2">
            <div class="col-sm-6">
               <h5 class="m-0">Income Per Class</h5>
            </div>
            <!-- /.col -->
            <div class="col-sm-6">
               <ol class="breadcrumb float-sm-right">
                  <li class="breadcrumb-item">Dashboard</li>
                  <li class="breadcrumb-item ">Reports</li>
                  <li class="breadcrumb-item active">Income Per Item</li>
               </ol>
            </div>
            <!-- /.col -->
         </div>
         <!-- /.row -->
      </div>
      <!-- /.container-fluid -->
   </div>
   <section class="content">
      <div class="container-fluid">
         <div class="row">
            <div class="col-md-2 col-sm-12 col-12">
               <div class="info-box">
                  <div class="info-box-content">
                     <div class="form-group">
                        <select id="Tyr" class="form-control custom-select" required>
                           <option selected value="">Year</option>
                           <option value="2022">2022</option>
                           </option>
                           <option value="2023">2023</option>
                           <option value="2024">2024</option>
                           <option value="2025">2025</option>
                           <option value="2026">2026</option>
                           <option value="2027">2027</option>
                           <option value="2028">2028</option>
                           <option value="2029">2029</option>
                           <option value="2030">2030</option>
                           <option value="2031">2031</option>
                           <option value="2032">2032</option>
                           <option value="2033">2033</option>
                           <option value="2034">2034</option>
                           <option value="2035">2035</option>
                           <option value="2036">2036</option>
                           <option value="2037">2037</option>
                           <option value="2038">2038</option>
                           <option value="2039">2039</option>
                           <option value="2040">2040</option>
                        </select>
                     </div>
                  </div>
                  <!-- /.info-box-content -->
               </div>
               <!-- /.info-box -->
            </div>
            <div class="col-md-2 col-sm-12 col-12">
               <div class="info-box">
                  <div class="info-box-content">
                     <div class="form-group">
                        <select id="Ttrm" class="form-control custom-select" required>
                           <option value="">Term</option>
                           <option value="Term One">Term One</option>
                           <option value="Term Two">Term Two</option>
                           <option value="Term Three">Term Three</option>
                        </select>
                     </div>
                  </div>
                  <!-- /.info-box-content -->
               </div>
               <!-- /.info-box -->
            </div>
            <div class="col-md-2 col-sm-12 col-12">
               <div class="info-box">
                  <div class="info-box-content">
                     <div class="form-group">
                        <select id="TCat" class="form-control custom-select" required>
                           <option value="">Class ...</option>
                           <option value="BABY CLASS">Baby Class</option>
                           <option value="PP1">PP1</option>
                           <option value="PP2">PP2</option>
                           <option value="GRADE 1">Grade 1</option>
                           <option value="GRADE 2">Grade 2</option>
                           <option value="GRADE 3">Grade 3</option>
                           <option value="GRADE 4">Grade 4</option>
                           <option value="GRADE 5">Grade 5</option>
                           <option value="GRADE 6">Grade 6</option>
                           <option value="GRADE 7">Grade 7</option>
                           <option value="GRADE 8">Grade 8</option>
                           <option value="GRADE 9">Grade 9</option>
                           <option value="GRADE 8">Class 8</option>
                        </select>
                     </div>
                  </div>
                  <!--info-box-content-->
               </div>
               <!-- /.info-box -->
            </div>
            <div class="col-md-2 col-sm-12 col-12">
               <div class="info-box">
                  <div class="info-box-content">
                     <div class="form-group">
                        <button class="btn btn-success btn-sm" title="Click to Filter"><i class="fa fa-refresh" id="btnfilter"></i></button>
                        <label id="labelcatmsg"></label>
                     </div>
                  </div>
                  <!-- /.info-box-content -->
               </div>
               <!-- /.info-box -->
            </div>
         </div>
      </div>
   </section>
   <section class="content" style="text-decoration:none;font-weight:normal;font-family:Times;font-size:12px">
      <div class="row">
         <div class="container">
            <ul class="nav nav-tabs">
               <li class="active"><a data-toggle="tab" href="#home" class="btn btn-prmary btn-sm">General Data <i class="fa fa-list"></i></a></li> 
               <li >|</li>
               <li><a data-toggle="tab" href="#menu1" class="btn btn-prmary btn-sm">Transport <i class="fa fa-bus"></i></a></li>
            </ul>
            <div class="tab-content">
               <div id="home" class="tab-pane  active">
                  <div class="row">
                     <div class="col-md-6">
                        <div class="card">
                           <div class="card-body">
                              <div class="tab-content">
                                 <div class="post">
                                    <div class="user-block table-respo" >
                                       <?php
                                          $catt=$_SESSION['Caat'];
                                          $yr=$_SESSION['Yeer'];
                                          $trm=$_SESSION['Trrm'];
                                          $grd=strtoupper($_SESSION['Grrd']);
                                          ?>
                                       <div class="table-responsive" id="divincomeanysis">
                                          <h6 class="text-danger" style="font-weight:bold;font-size:18px;font-family:'Arial Black', 'Arial Bold', Gadget, sans-serif;">
                                          <u><?php echo  $catt.'-'.$trm.' '.$yr;   ?></u> </h5>
                                          <label class="text-danger"><em>Records for SELECTED classes only</em></label>
                                          <table class="table table-bordered table-dark table-sm " id="incomecattable" style="font-size:13px;font-family:'Arial', Gadget, sans-serif;">
                                             <thead>
                                                <tr class="alert-warning">
                                                   <th>#</th>
                                                   <th>Item</th>
                                                   <th>Total Income (KES)</th>
                                                </tr>
                                             </thead>
                                             <tbody>
                                                <?php
                                                   $sql = "select feestatement.*,(SELECT SUM(TrFee) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as tran,(SELECT SUM(TuFee) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as tu,
                                                   (SELECT SUM(BoFee) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as bo,(SELECT SUM(AdFee) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as adm,
                                                   (SELECT SUM(MoFee) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as mof,(SELECT SUM(UnFee) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as unfe,
                                                   (SELECT SUM(BookFee) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as bofe,(SELECT SUM(DiFee) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as dife,
                                                   (SELECT SUM(PeFee) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as pefe,(SELECT SUM(Dev) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as dev,
                                                   (SELECT SUM(Tablet) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as tab from feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt'";
                                                   $result = $con->query($sql);
                                                   while($row = $result->fetch_assoc()) 
                                                   {
                                                   $tr=$row["tran"];  
                                                   $tu=$row["tu"]; 
                                                   $bo=$row["bo"];
                                                   $ad=$row["adm"];
                                                   $mo=$row["mof"];
                                                   $unf=$row["unfe"];
                                                   $dife=$row["dife"];
                                                   $bofe=$row["bofe"];
                                                   $dev=$row["dev"];
                                                   $pefe=$row["pefe"];
                                                   $tab=$row["tab"];
                                                   }
                                                   
                                                   $sqll = "select TourPayment.*,(SELECT SUM(Amount) FROM TourPayment where Year='$yr' AND Term='$trm' AND Grad='$catt') as tpay from TourPayment where Year='$yr' AND Term='$trm' AND Grad='$catt'";
                                                   $result = $con->query($sqll);
                                                   while($row = $result->fetch_assoc()) 
                                                   {
                                                     $tpay=$row["tpay"]; 
                                                   }
                                                   
                                                   $sqll1 = "select Gradpayment.*,(SELECT SUM(Amount) FROM Gradpayment where Year='$yr' AND Term='$trm' AND Grad='$catt') as gpay from Gradpayment where Year='$yr' AND Term='$trm' AND Grad='$catt'";
                                                   $result = $con->query($sqll1);
                                                   while($row = $result->fetch_assoc()) 
                                                   {
                                                     $gpay=$row["gpay"]; 
                                                   }
                                                   $tot=$tr+$tu+$bo+$ad+$mo+$unf+$tab+$pefe+$dev+$bofe+$dife+$tpay+$gpay;
                                                   ?>	
                                                <tr>
                                                   <td>1</td>
                                                   <td>Tution Fee</td>
                                                   <td><strong class="text-success"><?php echo number_format($tu); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>2</td>
                                                   <td>Admission Fee</td>
                                                   <td><strong class="text-success"><?php echo number_format($ad); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>3</td>
                                                   <td>Motivation Fee</td>
                                                   <td><strong class="text-success"><?php echo number_format($mo); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>4</td>
                                                   <td>Uniform Fee</td>
                                                   <td><strong class="text-success"><?php echo number_format($unf); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>5</td>
                                                   <td>Transport Fee (SMDEC)</td>
                                                   <td><strong class="text-success"><?php echo number_format($tr); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>6</td>
                                                   <td>School Diaries</td>
                                                   <td><strong class="text-success"><?php echo number_format($dife); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>7</td>
                                                   <td>Text Books</td>
                                                   <td><strong class="text-success"><?php echo number_format($bofe); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>8</td>
                                                   <td>P. Expenses</td>
                                                   <td><strong class="text-success"><?php echo number_format($pefe); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>9</td>
                                                   <td>Boarding Fee</td>
                                                   <td><strong class="text-success"><?php echo number_format($bo); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>10</td>
                                                   <td>Development Fee</td>
                                                   <td><strong class="text-success"><?php echo number_format($dev); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>11</td>
                                                   <td>Graduation Fee</td>
                                                   <td><strong class="text-success"><?php echo number_format($gpay); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>12</td>
                                                   <td>Tablet</td>
                                                   <td><strong class="text-success"><?php echo number_format($tab); ?></strong></td>
                                                </tr>
                                                <tr>
                                                   <td>13</td>
                                                   <td>Tour Payment </td>
                                                   <td><strong class="text-success"><?php echo number_format($tpay); ?></strong></td>
                                                </tr>
                                             </tbody>
                                             <tfoot>
                                                <tr class="bg-white">
                                                   <td colspan="2"><strong class="text-danger">TOTAL (KES)</strong></td>
                                                   <td><label><strong class="text-danger"><?php echo number_format($tot); ?></strong></label></td>
                                                </tr>
                                             </tfoot>
                                          </table>
                                          <hr />
                                          <button class="btn btn-success btn-sm pull-right" id="downincomeper">Download <i class="fa fa-cloud-download"></i></button>
                                       </div>
                                    </div>
                                 </div>
                              </div>
                           </div>
                        </div>
                     </div>
                     <div class="col-md-6">
                        <div class="card">
                           <div class="card-body">
                              <div class="tab-content">
                                 <h4><strong>Termly Income for all the classes</strong></h4>
                                 <label class="text-danger"><em>Excludes: Farm, Bus and any other income not listed on the table</em></label>
                                 <table class="table table-bordered table-dark table-sm " id="" style="font-size:13px;font-family:'Arial', Gadget, sans-serif;">
                                    <thead>
                                    </thead>
                                    <tbody>
                                       <?php
                                          $sql = "select feestatement.*,(SELECT SUM(TrFee) FROM feestatement where Year='$yr' AND Term='$trm') as tran,(SELECT SUM(TuFee) FROM feestatement where Year='$yr' AND Term='$trm') as tu,
                                          (SELECT SUM(BoFee) FROM feestatement where Year='$yr' AND Term='$trm') as bo,(SELECT SUM(AdFee) FROM feestatement where Year='$yr' AND Term='$trm') as adm,
                                          (SELECT SUM(MoFee) FROM feestatement where Year='$yr' AND Term='$trm') as mof,(SELECT SUM(UnFee) FROM feestatement where Year='$yr' AND Term='$trm') as unfe,
                                          (SELECT SUM(BookFee) FROM feestatement where Year='$yr' AND Term='$trm') as bofe,(SELECT SUM(DiFee) FROM feestatement where Year='$yr' AND Term='$trm') as dife,
                                          (SELECT SUM(PeFee) FROM feestatement where Year='$yr' AND Term='$trm') as pefe,(SELECT SUM(Dev) FROM feestatement where Year='$yr' AND Term='$trm') as dev,
                                          (SELECT SUM(Tablet) FROM feestatement where Year='$yr' AND Term='$trm') as tab from feestatement where Year='$yr' AND Term='$trm'";
                                          $result = $con->query($sql);
                                          while($row = $result->fetch_assoc()) 
                                          {
                                          $tr=$row["tran"];  
                                          $tu=$row["tu"]; 
                                          $bo=$row["bo"];
                                          $ad=$row["adm"];
                                          $mo=$row["mof"];
                                          $unf=$row["unfe"];
                                          $dife=$row["dife"];
                                          $bofe=$row["bofe"];
                                          $dev=$row["dev"];
                                          $pefe=$row["pefe"];
                                          $tab=$row["tab"];
                                          }
                                          
                                          $sqll = "select TourPayment.*,(SELECT SUM(Amount) FROM TourPayment where Year='$yr' AND Term='$trm') as tpay from TourPayment where Year='$yr' AND Term='$trm'";
                                          $result = $con->query($sqll);
                                          while($row = $result->fetch_assoc()) 
                                          {
                                            $tpay=$row["tpay"];  
                                          }
                                          
                                          $sqll1 = "select Gradpayment.*,(SELECT SUM(Amount) FROM Gradpayment where Year='$yr' AND Term='$trm') as gpay from Gradpayment where Year='$yr' AND Term='$trm'";
                                          $result = $con->query($sqll1);
                                          while($row = $result->fetch_assoc()) 
                                          {
                                            $gpay=$row["gpay"]; 
                                          }
                                          
                                          $tot=$tr+$tu+$bo+$ad+$mo+$unf+$tab+$pefe+$dev+$bofe+$dife+$tpay+$gpay;
                                          ?>	
                                    </tbody>
                                    <tfoot>
                                       <tr class="bg-white">
                                          <td colspan="2"><strong class="text-danger">TOTAL (KES)</strong></td>
                                          <td><label><strong class="text-danger"><?php echo number_format($tot); ?></strong></label></td>
                                       </tr>
                                    </tfoot>
                                 </table>
                              </div>
                           </div>
                        </div>
                     </div>
                  </div>
               </div>
               <div id="menu1" class="tab-pane fade">
                  <h5>Transport Fee: <u><?php echo  $catt.'-'.$trm.' '.$yr;   ?></u></h5>
                  <label class="text-danger">ONLY Learners with Transport Fee will be displayed</label>
                  <div class="col-md-8">
                     <div class="card">
                        <div class="card-body">
                         <div id="divtranspo">
                         <table class="table table-hover table-sm table-dark" id="tabletrans" style="font-size:12px">
                             <thead>
                                 <tr>
                                     <th>#</th>
                                     <th>Learner</th>
                                     <th>Grade</th>
                                     <th>Transport Fee</th>
                                     <th>Paid</th>
                                     <th>Balance</th>
                                 </tr>
                             </thead>
                             <tbody>
                                <?php
                                $sql = "select Fees2.*, pupils.Names as nm, pupils.AdmNo as adm, (SELECT SUM(TrFee) FROM feestatement where Year='$yr' AND Term='$trm' AND pid=pupils.count) as paid from Fees2 
                                left join pupils ON pupils.count=Fees2.pid left join feestatement ON feestatement.pid=pupils.count where Fees2.Year='$yr' AND Fees2.Term='$trm' AND Fees2.Grad='$catt' AND Fees2.TranF !='0' GROUP BY pupils.count";
                                $result = $con->query($sql);
                                $cnt=1;
                                while($row = $result->fetch_assoc()) 
                                {
                                 $pd=$row["paid"];
                                 $tf=$row["TranF"];
                                 if($pd=='')
                                 {
                                     $pd=0;
                                 }
                                 $bal=$tf-$pd;
                                ?>
                                 <tr>
                                     <td><?php echo $cnt; ?></td>
                                     <td><?php echo $row["nm"].'('.$row["adm"].')'; ?></td>
                                     <td><?php echo $row["Grad"]; ?></td>
                                     <td><?php echo $tf; ?></td>
                                     <td class="bg-success"><strong><?php echo $pd; ?></strong></td>
                                     <td class="bg-warning"><strong><?php echo $bal; ?></strong></td>
                                 </tr>
                                 <?php
                                 $cnt++;
                                }
                                ?>
                             </tbody>
                             <tfoot>
                                 <?php
                                 $sql = "select Fees2.*,(SELECT SUM(TrFee) FROM feestatement where Year='$yr' AND Term='$trm' AND Grad='$catt') as paid,
                                 (SELECT SUM(TranF) FROM Fees2 where Year='$yr' AND Term='$trm' AND Grad='$catt') as tfee from Fees2 where Fees2.Year='$yr' AND Fees2.Term='$trm' AND Fees2.Grad='$catt'";
                                $result = $con->query($sql);
                                while($row = $result->fetch_assoc()) 
                                {
                                 $tpd=$row["paid"];
                                 $tra=$row["tfee"];
                                }
                                 $bal=$tra-$tpd;
                                 ?>
                                 <tr class="bg-secondary">
                                 <td></td>
                                 <td colspan="">TOTAL (KES)</td>
                                 <td></td>
                                 <td><strong><?php echo number_format($tra); ?></strong></td>
                                 <td class="bg-success"><strong><?php echo number_format($tpd); ?></strong></td>
                                 <td class="bg-danger"><strong><?php echo number_format($bal); ?></strong></td>
                                 </tr>
                             </tfoot>
                         </table>
                         
                          <button class="btn btn-success btn-sm pull-right" id="downincomeper2">Download <i class="fa fa-cloud-download"></i></button>
                         </div>
                        </div>
                     </div>
                  </div>
               </div>
            </div>
         </div>
      </div>
   </section>
</div>