<div class="panel">
    <div class="panel-heading panel-theme clearfix report-head">
        Settlement Expense List
        <div class="pull-right">
            <div class="filter_details">

                <?php if ($from_date != '') { ?>
                    <div class="filter-item">
                        <label>From Date :</label>
                        <?php echo $from_date; ?>
                    </div>
                <?php } ?>

                <?php if ($to_date != '') { ?>
                    <div class="filter-item">
                        <label>To Date :</label>
                        <?php echo $to_date; ?>
                    </div>
                <?php } ?>

            </div>
        </div>
    </div>

    <div class="panel-body">
        <div class="table-responsive">
            <table id="tbl_purchase_order_table"
            class="table table-fixed table-bordered table-primary dataTable html_table">
            <thead>
                <tr>
                    <th>S.No</th>
                    <th>User</th>
                    <th>Last Dispersed Date</th>
                    <th style="text-align:right;">Total Amount Dispersed</th>
                    <th style="text-align:right;">Unsettled Expense</th>
                    <th style="text-align:right;">Balance in Hand</th>
                </tr>
            </thead>

            <tbody>
                <?php
                $condition = '1=1';

                // if ($from_date != '') {
                //     $from_date = str_replace('/', '-', $from_date);
                //     $from_date = date('Y-m-d', strtotime($from_date));
                //     $condition .= ' AND t.settlement_date >= "' . $from_date . '"';
                // }

                // if ($to_date != '') {
                //     $to_date = str_replace('/', '-', $to_date);
                //     $to_date = date('Y-m-d', strtotime($to_date));
                //     $condition .= ' AND t.settlement_date <= "' . $to_date . '"';
                // }

                $criteria = new CDbCriteria();
                $criteria->select = "
                t.user_id,
                t.expense_details_id,
                SUM(t.amount_dispersed) AS amount_dispersed,
                SUM(t.unsettled_expenses) AS unsettled_expenses,
                SUM(t.amount_returned) AS amount_returned,
                MAX(
                CASE 
                WHEN t.amount_dispersed != 0 
                THEN t.settlement_date 
                ELSE NULL 
                END
                ) AS settlement_date
                ";
                $criteria->condition = $condition;
                $criteria->group = 't.user_id';
                $criteria->order = 't.user_id ASC';

                $model_settlement = ExpenseSettlement::model()->findAll($criteria);

                $total_amount_dispersed   = 0;
                $total_unsettled_expenses = 0;
                $total_balance            = 0;

                if (!empty($model_settlement)) {

                    foreach ($model_settlement as $i => $model) {


                     $user_id = $model->user_id;

                     $records = ExpenseDetails::model()->findAll(
                        'created_by=:uid AND isActive=:active',
                        array(':uid' => $user_id, ':active' => 'N')
                    );

                     $unsettled_expenses = 0;

                     foreach ($records as $r) {
                        $unsettled_expenses += $r->amount;
                    }

                    $lastSettlement = ExpenseSettlement::model()->find(array(
                        'condition' => 'user_id = :uid',
                        'params'    => array(':uid' => $user_id),
                        'order'     => 'id DESC'
                    ));

                    $balance = ($lastSettlement !== null) ? $lastSettlement->new_balance : 0;
                    if($lastSettlement->amount_returned == '0.00'){
                        
                      $amount_dispersed = $balance;
                    }
                    else{
                         $amount_dispersed = ($balance > 0) ? ($balance - $unsettled_expenses) : $unsettled_expenses;
                         
                    }

                        // $total_amount_dispersed_val   = (float)$model->amount_dispersed;
                        // $total_amount_returned_val    = (float)$model->amount_returned;
                       // $amount_dispersed             =  $total_amount_dispersed_val - $total_amount_returned_val;

                        //$unsettled_expenses = (float)$model->unsettled_expenses;
                    $balance_in_hand    = $amount_dispersed - $unsettled_expenses;

                    $total_amount_dispersed   += $amount_dispersed;
                    $total_unsettled_expenses += $unsettled_expenses;
                    $total_balance            += $balance_in_hand;

                    $user_details = User::model()->findByPk($user_id);
                    $username     = $user_details->first_name . ' ' . $user_details->last_name;
                    ?>
                    <tr>
                        <td><?php echo $i + 1; ?></td>
                        <td><?php echo $username; ?></td>
                        <td>
                            <?php
                            echo !empty($model->settlement_date)
                            ? date('d/m/Y', strtotime($model->settlement_date))
                            : '-';
                            ?>
                        </td>
                        <td style="text-align:right;">
                            <?php echo number_format($amount_dispersed, 2); ?>
                        </td>
                        <td style="text-align:right;">
                            <?php echo number_format($unsettled_expenses, 2); ?>
                        </td>
                        <td style="text-align:right;">
                            <?php echo number_format($balance_in_hand, 2); ?>
                        </td>
                    </tr>
                    <?php
                }
                ?>

                <tr style="font-weight:bold; background:#f5f5f5;">
                    <td colspan="3" style="text-align:right;">Grand Total</td>
                    <td style="text-align:right;">
                        <?php echo number_format($total_amount_dispersed, 2); ?>
                    </td>
                    <td style="text-align:right;">
                        <?php echo number_format($total_unsettled_expenses, 2); ?>
                    </td>
                    <td style="text-align:right;">
                        <?php echo number_format($total_balance, 2); ?>
                    </td>
                </tr>

            <?php } else { ?>

                <tr>
                    <td colspan="6" style="text-align:center;">No Data Found</td>
                </tr>

            <?php } ?>
        </tbody>
    </table>
</div>
</div>
</div>
