<?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('M');
    //$finaldate='Today: '.$date
   
    $y=intval($_GET['Year']);
    if($y=='' || $y==null)
    {
        $yr=$date->format('Y');
    }else
    {
         $yr=$y;
    }
    
   include('expe.php');
    $sql=mysqli_query($con,"UPDATE Expenditure SET January='$janexpe',February='$febexpe', March='$marexpe',April='$aprexpense', May='$mayexpe', June='$junexpe', July='$julyexpe', August='$augexpe', September='$sepexpe', October='$octexpe', November='$novexpe', December='$decexpe' WHERE Year='$yr' AND Item='Other Expenses'");
    if($sql)
    {
        
    }else{
       
    }
      
 include('sala.php');
    //UniformUpdate
    $sql=mysqli_query($con,"UPDATE Expenditure SET January='$jansala',February='$febsala', March='$marsala',April='$aprsala', May='$maysala', June='$junsala', July='$julysala', August='$augsala', September='$sepsala', October='$octsala', November='$novsala', December='$decsala' WHERE Year='$yr' AND Item='Salaries'");
    if($sql)
    {
        
    }else{
       
    }
    
     include('nhif.php');
     
    $sql=mysqli_query($con,"UPDATE Expenditure SET January='$jannhif',February='$febnhif', March='$marnhif',April='$aprnhif', May='$maynhif', June='$junnhif', July='$julynhif', August='$augnhif', September='$sepnhif', October='$octnhif', November='$novnhif', December='$decnhif' WHERE Year='$yr' AND Item='NHIF'");
    if($sql)
    {
    }else{
    }
    
    include('nssf.php');
    $sql=mysqli_query($con,"UPDATE Expenditure SET January='$jannssf',February='$febnssf', March='$marnssf',April='$aprnssf', May='$maynssf', June='$junnssf', July='$julynssf', August='$augnssf', September='$sepnssf', October='$octnssf', November='$novnssf', December='$decnssf' WHERE Year='$yr' AND Item='nssf'");
    if($sql)
    {
        
    }else{
       
    }
    
    include('pro.php');
    $sql=mysqli_query($con,"UPDATE Expenditure SET January='$janpro',February='$febpro', March='$marpro',April='$aprpro', May='$maypro', June='$junpro', July='$julypro', August='$augpro', September='$seppro', October='$octpro', November='$novpro', December='$decpro' WHERE Year='$yr' AND Item='Projects'");
    if($sql)
    {
        
    }else{
    }
    
    //include('farmincome.php');
    
     //$sql2=mysqli_query($con,"UPDATE income SET January='$janfam',February='$febfam', March='$marfam',April='$aprfam'  WHERE Year='$yr' AND Item='Farm Income'");
    //if($sql2)
    //{}
     $queryy=mysqli_query($con,"DELETE FROM impreincome");
?>
<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">Expenses Reports</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">Expenditure</li>
            </ol>
          </div><!-- /.col -->
        </div><!-- /.row -->
      </div><!-- /.container-fluid -->
    </div>
    
    <section class="content">
      <div class="container-fluid">
        <div class="row">
       
          
          <div class="col-md-5 col-sm-12 col-12">
            <div class="info-box">
              <span class="info-box-icon bg-success"><i class="far fa-filter"></i></span>
              
              <div class="info-box-content">
                <div class="form-group">
                <label for="inputStatus">Year <span class="requireds">*</span>
                </label>
                 <select id="yearfilter" class="form-control custom-select" required>
                <option selected disabled value="<?php echo $yr; ?>"><?php echo $yr; ?></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>
         </div>
    </section>
          
    
    <section class="content" style="text-decoration:none;font-weight:normal;font-family:Times;font-size:12px">
        
         <div class="row">
         <div class="col-md-12">   
         <div class="card">
              
           <div class="card-header p-2">
                <ul class="nav nav-pills">
                    
                    
                   <li class="nav-item"><a class="nav-link active" href="#divimprest" data-toggle="tab">Expenses Via Imprest</a></li>
                   <li class="nav-item"><a class="nav-link " href="#divfee" data-toggle="tab">Other Expenses</a></li>
                  
                </ul>
             </div><!-- /.card-header -->  
             <div class="card-body">
                <div class="tab-content">
                    
            <div class="tab-pane" id="divfee">
                 <h5>Other Expenses</h5>
             <div class="post">
             <div class="user-block table-respo" > 
            
             <div class="table-responsive" id="divincome">
                 <table class="table table-bordered" id="idmonthincome" >
                     <thead>
                         <tr class="alert-success">
                             <th>#</th>
                             <th>Item</th>
                             <th>January</th>
                             <th>February</th>
                             <th>March</th>
                             <th>April</th>
                             <th>May</th>
                             <th>June</th>
                             <th>July</th>
                             <th>August</th>
                             <th>September</th>
                             <th>October</th>
                             <th>November</th>
                             <th>December</th>
                         </tr>
                     </thead>
                     <tbody>
                        <?php
                        //include('../include/config.php');
                        $sql = "select * from Expenditure where Year='$yr'";
                        $result = $con->query($sql);
                        if ($result->num_rows > 0) 
                        {
                            $cnt='1';
                            while($row = $result->fetch_assoc()) 
                            {
                            ?>	
                            <tr>
                        <td><?php echo $cnt; ?></td>
                        <td style="font-size:14px;color:brown"><strong><?php echo $row["Item"]; ?></strong></td>
                        <td><strong><?php echo $row["January"]; ?></strong></td>
                        <td><strong><?php echo $row["February"]; ?></strong></td>
                        <td><strong><?php echo $row["March"]; ?></strong></td>
                        <td><strong><?php echo $row["April"]; ?></strong></td>
                        <td><strong><?php echo $row["May"]; ?></strong></td>
                        <td><strong><?php echo $row["June"]; ?></strong></td>
                        <td><strong><?php echo $row["July"]; ?></strong></td>
                        <td><strong><?php echo $row["August"]; ?></strong></td>
                        <td><strong><?php echo $row["September"]; ?></strong></td>
                        <td><strong><?php echo $row["October"]; ?></strong></td>
                        <td><strong><?php echo $row["November"]; ?></strong></td>
                        <td><strong><?php echo $row["December"]; ?></td>
                            </tr>
                            <?php
                            $cnt=$cnt+1;
                            }
                        }
                            ?>
                     </tbody>
                 </table> 
              </div>
              </div>
              </div>
              </div>
              
              <div class="tab-pane active" id="divimprest">
                  <div class="col-md-12"> 
                      <h5>Expenses Via Imprest</h5>
                      
                 <div class="table-responsive" id="divimp">
                 <table class="table table-bordered" id="exeimprest" >
                     <thead>
                         <tr class="alert-success">
                                 <th>#</th>
                                 <th>Item</th>
                                 <th>January</th>
                                 <th>February</th>
                                 <th>March</th>
                                 <th>April</th>
                                 <th>May</th>
                                 <th>June</th>
                                 <th>July</th>
                                 <th>August</th>
                                 <th>September</th>
                                 <th>October</th>
                                 <th>November</th>
                                 <th>December</th>
                         </tr>
                     </thead>
                     <tbody>
                        <?php
                        
                          $query=mysqli_query($con,"INSERT INTO impreincome(Item) SELECT DISTINCT Imprest2.Cate FROM Imprest2 WHERE Year='$yr'");
                        
                        
                        $sql = "select * from Imprest2 where Year='$yr'";
                        $result = $con->query($sql);
                        while($row = $result->fetch_assoc()) 
                        {
                           $itemu=$row["Cate"]; 
                           
                        
                            
                         $result = mysqli_query($con,"SELECT sum(Amount) FROM Imprest2 where Cate ='$itemu' AND Year='$yr' AND Month='June'");
                            while($row = $result->fetch_assoc()) 
                            {
                            $ama=$row['sum(Amount)'];
                            }   
                           
                           $query=mysqli_query($con,"UPDATE impreincome SET June ='$ama' WHERE Item='$itemu'");
                         
                          }
                       
                        
                       $sql = "select DISTINCT impreincome.Item as itemm, expenses.Item as ite, (select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='January') as ja,(select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='February') as fe,(select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='March') as mar,(select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='April') as ap,(select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='May') as ma, (select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='June') as ju,(select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='July') as jul,(select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='August') as aug,(select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='September') as sep,(select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='October') as oct,(select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='November') as nov,(select sum(Amount) from Imprest2 where Cate=impreincome.Item AND Imprest2.Month='December') as de FROM impreincome left join expenses ON expenses.Count=impreincome.Item left join Imprest2 ON Imprest2.Cate=impreincome.Item ORDER BY ite ASC";
                        $result = $con->query($sql);
                        if ($result->num_rows > 0) 
                        {
                            $cnt='1';
                            while($row = $result->fetch_assoc()) 
                            {
                            ?>	
                            <tr>
                            <td><?php echo $cnt; ?></td>
                            <td><strong><?php echo $row["ite"]; ?></strong></td>
                            <td><strong><?php echo $row["ja"]; ?></strong></td>
                            <td><strong><?php echo $row["fe"]; ?></strong></td>
                            <td><strong><?php echo $row["mar"]; ?></strong></td>
                            <td><strong><?php echo $row["ap"]; ?></strong></td>
                            <td><strong><?php echo $row["ma"]; ?></strong></td>
                            <td><strong><?php echo $row["ju"]; ?></strong></td>
                            <td><strong><?php echo $row["jul"]; ?></strong></td>
                            <td><strong><?php echo $row["aug"]; ?></strong></td>
                            <td><strong><?php echo $row["sep"]; ?></strong></td>
                            <td><strong><?php echo $row["oct"]; ?></strong></td>
                            <td><strong><?php echo $row["nov"]; ?></strong></td>
                            <td><strong><?php echo $row["de"]; ?></strong></td>
                            </tr>
                            
                            <?php
                            $cnt=$cnt+1;
                            }
                        }
                            ?>
                     </tbody>
                     <tfoot>
         <?php
                $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='January'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as jantot from Imprest2 where Year='$yr' AND Month='January'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $jant=$row["jantot"]; 
                }
                }else
                {
                   $jant='0'; 
                }
                
            
                $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='February'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as febtot from Imprest2 where Year='$yr' AND Month='February'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $febt=$row["febtot"]; 
                }
                }else
                {
                   $febt='0'; 
                }
                
                 $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='March'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as martot from Imprest2 where Year='$yr' AND Month='March'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $mart=$row["martot"]; 
                }
                }else
                {
                   $mart='0'; 
                }
                
                
                $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='April'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as aprtot from Imprest2 where Year='$yr' AND Month='April'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $aprt=$row["aprtot"]; 
                }
                }else
                {
                $aprt='0'; 
                }
                
                 $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='May'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as maytot from Imprest2 where Year='$yr' AND Month='May'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $mayto=$row["maytot"]; 
                }
                }else
                {
                $mayto='0'; 
                }
                
                
                 $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='June'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as junetot from Imprest2 where Year='$yr' AND Month='June'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $juto=$row["junetot"]; 
                }
                }else
                {
                $juto='0'; 
                }
                
                
                $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='July'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as jultot from Imprest2 where Year='$yr' AND Month='July'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $julto=$row["jultot"]; 
                }
                }else
                {
                $julto='0'; 
                }
                
                $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='August'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as augtot from Imprest2 where Year='$yr' AND Month='August'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $augto=$row["augtot"]; 
                }
                }else
                {
                $augto='0'; 
                }
                
                $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='September'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as septot from Imprest2 where Year='$yr' AND Month='September'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $septo=$row["septot"]; 
                }
                }else
                {
                $septo='0'; 
                }
                
                
                 $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='October'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as octtot from Imprest2 where Year='$yr' AND Month='October'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $octto=$row["octtot"]; 
                }
                }else
                {
                $octto='0'; 
                }
                
                
                  $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='November'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as novtot from Imprest2 where Year='$yr' AND Month='November'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $novto=$row["novtot"]; 
                }
                }else
                {
                $novto='0'; 
                }
                
                
                $sql=mysqli_query($con,"SELECT * from Imprest2 where Year='$yr' AND Month='December'");
                $num=mysqli_fetch_array($sql);
                if($num>0)
                {
                $sqll = "select SUM(Amount) as dectot from Imprest2 where Year='$yr' AND Month='December'";
                $result = $con->query($sqll);
                while($row = $result->fetch_assoc()) 
                {
                $decto=$row["dectot"]; 
                }
                }else
                {
                $decto='0'; 
                }
         ?>
                         <tr class="bg-secondary text-white">
                             <td colspan="2"><strong>Total (KES)</strong> </td>
                             <td><strong><?php echo  $jant; ?></strong></td>
                             <td><strong><?php echo  $febt; ?></strong></td>
                             <td><strong><?php echo  $mart; ?></strong></td>
                             <td><strong><?php echo  $aprt; ?></strong></td>
                             <td><strong><?php echo  $mayto; ?></strong></td>
                             <td><strong><?php echo  $juto; ?></strong></td>
                             <td><strong><?php echo  $julto; ?></strong></td>
                             <td><strong><?php echo  $augto; ?></strong></td>
                             <td><strong><?php echo  $septo; ?></strong></td>
                             <td><strong><?php echo  $octto; ?></strong></td>
                             <td><strong><?php echo  $novto; ?></strong></td>
                             <td><strong><?php echo  $decto; ?></strong></td>
                         </tr>
                     </tfoot>
                 </table> 
              </div>
                  </div>
             </div>
              </div>
              
            </div>
          </div>
          </div>
          </div>
    </section>
</div>

