<!DOCTYPE HTML>
<?PHP 
require 'functions.php'; 
checkLogin(); 
$db_link = connect(); 
$rep_year = date("Y",time()); 
$rep_month = date("m",time()); 

//Make array for exporting dat
$_SESSION['rep_export'] = array(); 
$_SESSION['rep_exp_title'] = $rep_year.'-'.$rep_month.'_loans-active'; 

//Select Active Loans from LOANS
$sql_loans = "SELECT * FROM loans 
              LEFT JOIN loanstatus ON loans.loanstatus_id = loanstatus.loanstatus_id 
              LEFT JOIN customer ON loans.cust_id = customer.cust_id 
              WHERE loans.loanstatus_id = 2 
              ORDER BY loan_dateout, loans.cust_id";

$query_loans = mysqli_query($db_link, $sql_loans); 
checkSQL($db_link, $query_loans); 

//Select Active Group Loans from GROUP_LOANS
$sql_group_loans = "SELECT 
                        group_loans.*,
                        `group`.group_name,
                        (SELECT member_contact 
                         FROM group_member 
                         WHERE group_id = group_loans.group_id 
                         ORDER BY member_id LIMIT 1) as member_contact
                    FROM 
                        group_loans 
                    LEFT JOIN 
                        loanstatus ON group_loans.loanstatus_id = loanstatus.loanstatus_id 
                    LEFT JOIN 
                        `group` ON group_loans.group_id = `group`.group_id 
                    WHERE 
                        group_loans.loanstatus_id = 2 
                    ORDER BY 
                        loan_dateout, group_loans.group_id";

$query_group_loans = mysqli_query($db_link, $sql_group_loans); 
checkSQL($db_link, $query_group_loans); 
?>

<html>
    <?PHP includeHead('Active Loans',1) ?>
    <body>
        <!-- MENU -->
        <?PHP includeMenu(3); ?>
        <div id="menu_main">
            <a href="loans_search.php">Search</a>
            <a href="loans_act.php" id="item_selected">Active Loans</a>
            <a href="loans_pend.php">Pending Loans</a>
            <a href="loans_securities.php">Loan Securities</a>
        </div>
        
        <!-- CONTENT -->
        <div class="content-center">
            <table id="tb_table">
                <colgroup>
                    <col width="7.5%">
                    <col width="30%">
                    <col width="10%">
                    <col width="15%">
                    <col width="15%">
                    <col width="15%">
                    <col width="7.5%">
                </colgroup>
                <tr>
                    <form class="export" action="rep_export.php" method="post">
                        <th class="title" colspan="8">Active Loans 
                            <!-- Export Button -->
                            <input type="submit" name="export_rep" value="Export" />
                        </th>
                    </form>
                </tr>
                <tr>
                    <th>Loan No.</th>
                    <th>Customer/Group</th>
                    <th>Loan Period</th>
                    <th>Principal</th>
                    <th>Interest</th>
                    <th>Remaining</th>
                    <th>Issued on</th>
                    <th>Contact</th>
                </tr>
                
                <?PHP 
                $count = 0; 
                
                // Display individual loans
                while ($row_loans = mysqli_fetch_assoc($query_loans)){
                    $loan_balances = getLoanBalance($db_link, $row_loans['loan_id']); 
                    echo '<tr> 
                            <td><a href="loan.php?lid='.$row_loans['loan_id'].'">'.$row_loans['loan_no'].'</a></td> 
                            <td>'.$row_loans['cust_name'].' (<a href="customer.php?cust='.$row_loans['cust_id'].'">'.$row['cust_no'].')</a></td> 
                            <td>'.$row_loans['loan_period'].'</td> 
                            <td>'.number_format($loan_balances['pdue']).' '.$_SESSION['set_cur'].'</td> 
                            <td>'.number_format($loan_balances['idue']).' '.$_SESSION['set_cur'].'</td> 
                            <td>'.number_format($loan_balances['balance']).' '.$_SESSION['set_cur'].'</td> 
                            <td>'.date("d.m.Y", $row_loans['loan_dateout']).'</td> 
                            <td>'.$row_loans['cust_phone'].'</td> 
                          </tr>';
                    
                    // Export Array
                    array_push($_SESSION['rep_export'], array(
                        "Loan No." => $row_loans['loan_no'], 
                        "Customer/Group" => $row_loans['cust_name'].' ('.$row_loans['cust_no'].')('.$row_loans['cust_phone'].')', 
                        "Loan Period" => $row_loans['loan_period'], 
                        "Principal" => $loan_balances['pdue'], 
                        "Interest" => $loan_balances['idue'], 
                        "Remaining" => $loan_balances['balance'], 
                        "Issued on" => date("d.m.Y", $row_loans['loan_dateout'])
                    ));
                    
                    $count++; 
                }
                
                // Display group loans
                while ($row_group_loans = mysqli_fetch_assoc($query_group_loans)){
                    $group_loan_balances = getGroupLoanBalance($db_link, $row_group_loans['loan_id']); 
                    echo '<tr> 
                            <td><a href="group_loan.php?loan_id='.$row_group_loans['loan_id'].'">'.$row_group_loans['loan_no'].'</a></td> 
                            <td>'.$row_group_loans['group_name'].' (<a href="group.php?gid='.$row_group_loans['group_id'].'">'.$row_group_loans['group_id'].')</a></td> 
                            <td>'.$row_group_loans['loan_period'].'</td> 
                            <td>'.number_format($group_loan_balances['pdue']).' '.$_SESSION['set_cur'].'</td> 
                            <td>'.number_format($group_loan_balances['idue']).' '.$_SESSION['set_cur'].'</td> 
                            <td>'.number_format($group_loan_balances['balance']).' '.$_SESSION['set_cur'].'</td> 
                            <td>'.date("d.m.Y", $row_group_loans['loan_dateout']).'</td> 
                            <td>'.$row_group_loans['member_contact'].'</td> 
                          </tr>';
                    
                    // Export Array
                    array_push($_SESSION['rep_export'], array(
                        "Loan No." => $row_group_loans['loan_no'], 
                        "Customer/Group" => $row_group_loans['group_name'].' ('.$row_group_loans['group_id'].')('.$row_group_loans['member_contact'].')', 
                        "Loan Period" => $row_group_loans['loan_period'], 
                        "Principal" => $group_loan_balances['pdue'], 
                        "Interest" => $group_loan_balances['idue'], 
                        "Remaining" => $group_loan_balances['balance'], 
                        "Issued on" => date("d.m.Y", $row_group_loans['loan_dateout'])
                    ));
                    
                    $count++; 
                }
                ?>
                
                <tr class="balance"> 
                    <td colspan="8"> 
                        <?PHP echo $count.' active loan'; if ($count != 1) echo 's'; ?> 
                    </td> 
                </tr>
            </table>
        </div>
    </body>
</html>
