<?php
session_start();
include 'include/config.php';

if(!isset($_SESSION['id'])){
 echo "<script> window.location.href = 'index.php' </script>";
  }
?>

<?php  include 'include/head.php'; ?>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/2.4.1/css/buttons.dataTables.min.css">

<style>
    /* Add custom styling here */
    body {
      font-family: Arial, sans-serif;
    }
    #example {
      width: 100%;
      border-collapse: collapse;
      margin-top: 20px;
    }
    #example th, #example td {
      border: 1px solid #ddd;
      padding: 8px;
      text-align: left;
    }
    #example th {
      background-color: #f2f2f2;
    }
    #example tbody tr:nth-child(even) {
      background-color: #f2f2f2;
    }
  </style>
                    
                      <!-- Start Content-->
                      <div class="container-fluid">
                        
                        <!-- start page title -->
                        <div class="row">
                            <div class="col-12">
                                <div class="page-title-box">
                                    <div class="page-title-right">
                                        <ol class="breadcrumb m-0">
                                            <li class="breadcrumb-item"><a href="dashboard.php">Home</a></li>
                                            <li class="breadcrumb-item active">Statement</li>
                                        </ol>
                                    </div>
                                    <h4 class="page-title"> Statement</h4>
                                </div>
                            </div>
                        </div>
                         <!--end page title -->
                         
                         <div class="row">
        <div class="card">
            <div class="card-body">
                <!-- Form start -->
                <form method="post" action="">
                    <!-- Dropdown and Input field -->
                    <div class="row">
                        <!--<div class="col-lg-4">-->
                        <!--    <div class="form-group">-->
                        <!--        <label for="exampleFormControlSelect1">Select Spa</label>-->
                        <!--        <select name="spa" class="form-control" id="exampleFormControlSelect1">-->
                        <!--            <option>Select..</option>-->
                        <!--            <?php while($data = mysqli_fetch_assoc($select_spa)){?>-->
                        <!--            <option value="<?php echo $data['unique_id'];?>"><?php echo $data['unique_id'] . ' = ' .$data['name'];?></option>-->
                        <!--            <?php }?>-->
                                    
                        <!--        </select>-->
                        <!--    </div>-->
                        <!--</div>-->
                        <div class="col-lg-3">
                            <div class="form-group">
                                <label for="userid">Type Of Statement</label>
                                <select name="statement" class="form-control" id="userid">
                                    <option value="">Select..</option>
                                    <option value="cd_st">CD Statement</option>
                                    <option value="share_st">Share Money Statement</option>
                                    <option value="loan_st">Loan Statement</option>
                                    
                                </select>
                            </div>
                        </div>
                       
                       <div class="col-lg-3">
                           <div class="form-group">
                               <label for="schedule">User ID/Ac No /Phone No</label>
                               <input type="text"  name="userid" class="form-control" id="schedule">
                           </div>
                       </div>
                       
                       <div class="col-lg-3">
                           <div class="form-group">
                               <label for="schedule">To</label>
                               <input type="date"  name="to_date" class="form-control" id="schedule">
                           </div>
                       </div>
                       
                       <div class="col-lg-3">
                           <div class="form-group">
                               <label for="schedule">From</label>
                               <input type="date"  name="from_date" class="form-control" id="schedule">
                           </div>
                       </div>
                        <div class="col-lg-4" style="margin-top:20px;">
                         <button type="submit" name="submittt" class="btn btn-primary">Submit</button>
                         </div>
                    </div>
                    <!-- End of Dropdown and Input field -->
                    <!-- Submit button -->
                   
                </form>
                
                <?php 
if(isset($_POST['submittt'])){
     $statement = $_POST['statement'];
     $userid = $_POST['userid'];
     $to_date = $_POST['to_date'];
     $from_date = $_POST['from_date'];
     
     $sqll = mysqli_fetch_assoc(mysqli_query($conn,"SELECT * FROM `user` where `id`='$userid' OR `a_c_number`='$userid' OR `phone`='$userid'"));
                     $iddd = $sqll['id'];

    switch($statement){
        case 'cd_st':
           $to_date_formatted = str_replace('-', '/', $to_date);
            $from_date_formatted = str_replace('-', '/', $from_date);
           $select_cd = mysqli_query($conn, "SELECT * FROM `cd` WHERE `userid`='$iddd' AND `cd_date` BETWEEN '$to_date_formatted' AND '$from_date_formatted'");
            break;
        case 'share_st':
           $to_date_formatted = str_replace('-', '/', $to_date);
            $from_date_formatted = str_replace('-', '/', $from_date);

            $select_share = mysqli_query($conn, "SELECT * FROM `share_money` WHERE `userid`='$iddd' AND `share_money_date` BETWEEN '$to_date_formatted' AND '$from_date_formatted'");
            break;
        case 'loan_st':
            $to_date_formatted = str_replace('-', '/', $to_date);
            $from_date_formatted = str_replace('-', '/', $from_date);
            $select_loan = mysqli_query($conn, "SELECT * FROM `loan_desc` WHERE `userid`='$iddd' AND `date` BETWEEN '$to_date_formatted' AND '$from_date_formatted'");
            break;
    }
}
?>
                <!-- Form end -->
            </div>
        </div>
</div>
                        
   <div style=" width:300px; margin-top:20px;">
    <a href="export_statment.php?iddd=<?php echo $iddd ?>&to_date=<?php echo $to_date_formatted ?>&from_date=<?php echo $from_date_formatted ?>&statement=<?php echo $statement ?>" class="btn btn-info" target="_blank">Export</a>
</div>
                        <div class="row">
                            <div class="col-12">
                                <div class="card">
                                    <div class="card-body">
                                        
                                        <div class="table-responsive">
                                            <div class="tab-pane show active" id="basic-datatable-preview">
                                               <?php if(isset($select_cd) && mysqli_num_rows($select_cd) > 0) { ?>
                            <!-- CD Statement Table -->
                            <table id="example" class="table table-striped dt-responsive nowrap w-100">
                                <!-- Table header -->
                                <thead>
                                    <tr>
                                        <th>Sr. No.</th>
                                        <th>User ID</th>
                                        <th>User Name</th>
                                        <th>CD Amount</th>
                                        <th>CD Interest</th>
                                        <th>Date</th>
                                        <th>Month</th>
                                        
                                    </tr>
                                </thead>
                                <!-- Table body -->
                                <tbody>
                                    <?php
                                    $i = 1;
                                    while ($row = mysqli_fetch_assoc($select_cd)) {
                                        // Fetch user details
                                        $user_detail = mysqli_fetch_assoc(mysqli_query($conn, "SELECT * FROM `user` WHERE `id`='$row[userid]'"));
                                        $total_cd_amount += $row['cd_amount'];
                                        $total_cd_interest += $row['cd_Interest'];
                                    ?>
                                    <tr>
                                        <td><?= $i; ?></td>
                                        <td><?= $user_detail['id'];?></td>
                                        <td><?= $user_detail['first_name']. ' ' .$user_detail['last_name'] ?></td>
                                        <td><?= $row['cd_amount'] ?></td>
                                        <td><?= $row['cd_Interest'] ?></td>
                                        <td><?= $row['cd_date'] ?></td>
                                        <td><?= $row['cd_month'] ?></td>
                                        
                                        
                                    </tr>
                                    <?php
                                    $i++;
                                    } 
                                    ?>
                                </tbody>
                                
                            </table>  
                            
                               <h5>Total CD Amount: <?= $total_cd_amount ?></h5>
                               <h5>Total CD Interest: <?= $total_cd_interest ?></h5>
                        <?php } ?>
                                               
                                                <?php if(isset($select_share) && mysqli_num_rows($select_share) > 0) { ?>
                                                <table id="example" class="table table-striped dt-responsive nowrap w-100">
                                                    <thead>
                                                        <tr>
                                                            <th>Sr. No.</th>
                                                            <th>User ID</th>
                                                            <th>User Name</th>
                                                             <th>Share Money Amount</th>
                                                             <th>Share Money Interest</th>
                                                             <th>Date</th>
                                                             <th>Month</th>
                                                            
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                    <?php
                                                    // $sql = "SELECT * FROM `user`";
                                                  //  $res = mysqli_query($conn, $sql);
                                                    $i = 1;
                                                    while ($row = mysqli_fetch_assoc($select_share)) {
                                                        $iddd = $row['userid'];
                                                        $select_user_detail = mysqli_fetch_assoc(mysqli_query($conn,"select * from `user` where `id`='$iddd'"));
                                                        
                                                        $total_share_amount += $row['share_money_amount'];
                                                        $total_share_interest += $row['share_money_Interest'];
                                                    ?>
                                                        <tr>
                                                            <td><?= $i; ?></td>
                                                            <td><?= $select_user_detail['id'];?></td>
                                                            <td><?= $select_user_detail['first_name']. ' ' .$select_user_detail['last_name'] ?></td>
                                                            <td><?= $row['share_money_amount'] ?></td>
                                                            <td><?= $row['share_money_Interest'] ?></td>
                                                            <td><?= $row['share_money_date'] ?></td>
                                                            <td><?= $row['month_name'] ?></td>
                                                            
                                                            
                                                           
                                                         </tr>
                                                        <?php
                                                       $i++;  } 
                                                        ?>
                                                    </tbody>
                                                </table> 
                                                <h5>Total Share Money Amount: <?= $total_share_amount ?></h5>
                                                <h5>Total Share Money Interest: <?= $total_share_interest ?></h5>
                                                 <?php } ?>
                                                 
                                                 <?php if(isset($select_loan) && mysqli_num_rows($select_loan) > 0) { ?>
                                                 
                                                 <table id="example" class="table table-striped dt-responsive nowrap w-100">
                                                    <thead>
                                                        <tr>
                                                            <th>Sr. No.</th>
                                                            <th>User ID</th>
                                                            <th>Name</th>
                                                             <th>Loan Amount</th>
                                                             <th>Month Amount</th>
                                                             <th>Monthly Intrest</th>
                                                             <th>Monthly Penalty</th>
                                                             <th>CD Deduction Amount:</th>
                                                             <th>Total Deduct Amount:</th>
                                                             <th>Loan Returan:</th>
                                                             <th>Available Amount:</th>
                                                             <th>Loan Date:</th>
                                                             <!--<th>Loan Month:</th>-->
                                                            
                                                             
                                                              
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                    <?php
                                                    // $sql = "SELECT * FROM `loan` ORDER BY ID DESC";
                                                    $res = mysqli_query($conn, $sql);
                                                    $i = 1;
                                                    while ($row = mysqli_fetch_assoc($select_loan)) {
                                                    ?>
                                                        <tr>
                                                            <td><?= $i; ?></td>
                                                            <td><?= $row['userid'] ?></td>
                                                            <?php
                                                                 $select_name = mysqli_fetch_assoc(mysqli_query($conn,"SELECT * FROM `user` WHERE `id`='$row[userid]'"));
                                                             ?>
                                                            <td><?= $select_name['first_name']. ' ' .$select_name['last_name'] ?></td>
                                                            <td><?= $row['available_balance'] ?></td>
                                                            <td><?= $row['month_payment'] ?></td>
                                                            <td><?= $row['monthly_Interest'] ?></td>
                                                            <td><?= $row['monthly_Penalty'] ?></td>
                                                            <td><?= $row['cd_deduction'] ?></td>
                                                            <td><?= $row['total_deduct_amount'] ?></td>
                                                            
                                                           
                                                            <td><?= $row['loan_return'] ?></td>
                                                            <td><?= $row['available_balance'] ?></td>
                                                            <td><?= $row['date'] ?></td>
                                                            <!--<td><?= $row['month_name'] ?></td>-->
                                                           
                                                            
                                                           
                                                         </tr>
                                                        <?php
                                                       $i++;  } 
                                                        ?>
                                                    </tbody>
                                                </table> 
                                                <?php } ?>
                                            </div> <!-- end preview-->
                                        
                                            
                                        </div> <!-- end tab-content-->

                                    </div> <!-- end card body-->
                                </div> <!-- end card -->
                            </div><!-- end col-->
                        </div> <!-- end row-->
                    
                    
                    </div> <!-- container -->


<?php  
include 'include/footer.php'; 
?>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.7.0.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/2.4.1/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.html5.min.js"></script>

<script>
$(document).ready(function() {
    $('#example').DataTable( {
        dom: 'Bfrtip',
        buttons: [
            {
                extend: 'pdfHtml5',
                download: 'open'
            }
        ]
    } );
} );
</script>
