<?php 
$from_date= str_replace('/', '-',$from_date);
$from_date= date("Y-m-d", strtotime($from_date));
$to_date= str_replace('/', '-',$to_date);
$to_date= date("Y-m-d", strtotime($to_date));
//$current_date="2021-02-23";

$SQL = 'SELECT SUM(`exchange_rate` *`grand_total` ) FROM `erp_invoice` WHERE `invoice_date` >= "'.$from_date.'" AND invoice_date <= "'.$to_date.'"';
//echo $SQL;
$today_invoice_value = Yii::app()->db->createCommand($SQL)->queryScalar();	

/*$SQL = 'SELECT lead_owner_id,CONCAT(first_name," ",last_name) AS lead_owner_name,COUNT(INV.id) AS invoice_count,SUM(`exchange_rate` *`grand_total` ) AS invoice_amount FROM `erp_invoice` INV 
INNER JOIN '.MasCompany::MainDB.'.erp_user AS U ON lead_owner_id=U.id 
WHERE invoice_date >= "'.$from_date.'" AND invoice_date <= "'.$to_date.'" GROUP BY lead_owner_id';*/

/*$SQL = "SELECT COUNT(transaction_id) AS invoice_count,lead_owner_id,lead_owner_name,IFNULL(SUM(invoice_amount - credit_note_amount),0) AS invoice_amount FROM `view_invoice_creditnote_details` WHERE (transaction_date BETWEEN '".$from_date."' AND '".$to_date."') OR transaction_date IS NULL GROUP BY lead_owner_id ORDER BY invoice_amount DESC";

$arr_today_invoice_value = Yii::app()->db->createCommand($SQL)->queryAll();*/	

$SQL = "SELECT COUNT(invoice.id) AS invoice_count,lead_owner_id,SUM(exchange_rate * grand_total) AS invoice_amount,CONCAT(user.first_name,' ',user.last_name) AS lead_owner_name  FROM `erp_invoice` AS invoice LEFT JOIN ".MasCompany::MainDB.".erp_user as user ON user.id=lead_owner_id WHERE invoice.isActive='Y' AND invoice_date >='".$from_date."' AND invoice_date<='".$to_date."' GROUP BY lead_owner_id";
$arr_today_invoice_value = Yii::app()->db->createCommand($SQL)->queryAll();

?>
<div class="popup_inner_element">
        <table class="table table-hover table-bordered personal-task">
            <thead>
                <tr>
                    <th>S.No</th>
                    <th>Lead Owner</th> 
                    <th>Invoice Count</th> 
                    <th>CN Count</th> 
                    <th>Invoice Amount</th>
                    <th>CN Amount</th> 
                    <th>Total</th> 
                </tr>
            </thead>
            <tbody>
            
            <?php
            
            
            if(!empty($arr_today_invoice_value)){
                $total_invoice = 0;
                $total_invoice_count = 0;
                $total_credit_note_count = 0;
                $total_invoice_credit_value=0;
                $total_credit_note_value=0;
                foreach ($arr_today_invoice_value as $i=>$value) {
                    
                    $model_credit_note=new Creditnote();
                    //$count_credit_note = $model_credit_note->with('invoice')->count(array('condition'=>'t.isActive="Y" AND invoice.lead_owner_id='.$value["lead_owner_id"].' AND (credited_date >= "'.$from_date.'" AND credited_date <= "'.$to_date.'")'));
                    $SQL = "SELECT COUNT(CN.id) AS count_credit_note,SUM(INV.exchange_rate * credited_amount) AS credit_note_amount FROM `erp_creditnote` AS CN INNER JOIN erp_invoice AS INV ON INV.id=invoice_id WHERE CN.isActive='Y' AND INV.lead_owner_id=".$value['lead_owner_id']." AND credited_date >='".$from_date."' AND credited_date<='".$to_date."'";
                    //echo $SQL;
                    $arr_credit_note_value = Yii::app()->db->createCommand($SQL)->queryRow();
                    
                    //echo "<pre>".print_r($arr_credit_note_value);
                    
                    //echo $arr_credit_note_value['credit_note_amount']."<br>";
                    
                    $invoice_value = $value['invoice_amount'] - $arr_credit_note_value['credit_note_amount'];
                    ?>
                        <tr>
                            <td><?php echo ($i+1); ?></td>
                            <td><?php echo $value['lead_owner_name']; ?></td>                            
                            <td class="text-right"><?php echo $value['invoice_count']; ?></td>                          
                            <td class="text-right"><?php echo $arr_credit_note_value['count_credit_note']; ?></td>   
                            <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency($value['invoice_amount'],""); ?></td>                      
                            <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency($arr_credit_note_value['credit_note_amount'],""); ?></td>                      
                            <td class="text-right"><?php echo Yii::app()->numberFormatter->formatCurrency($invoice_value,""); ?></td>
                        </tr>
                    <?php
                    
                    $total_invoice_count+=$value['invoice_count'];
                    $total_invoice+=$value['invoice_amount'];
                    $total_invoice_credit_value+=$invoice_value;
                    $total_credit_note_count+=$arr_credit_note_value['count_credit_note'];
                    $total_credit_note_value+=$arr_credit_note_value['credit_note_amount'];
                    $i++;   
                }
                ?>
                    <tr>
                        <td class="text-right" colspan="2"><b>Total</b></td>
                        <td class="text-right"><b><?php echo $total_invoice_count;?></b></td>
                        <td class="text-right"><b><?php echo $total_credit_note_count;?></b></td>
                        <td class="text-right"><b>AED <?php echo Yii::app()->numberFormatter->formatCurrency($total_invoice,''); ?></b></td>
                        <td class="text-right"><b>AED <?php echo Yii::app()->numberFormatter->formatCurrency($total_credit_note_value,''); ?></b></td>
                        <td class="text-right"><b>AED <?php echo Yii::app()->numberFormatter->formatCurrency($total_invoice_credit_value,''); ?></b></td>
                    </tr>
                <?php
            }
            else{
                ?>
                    <tr>
                        <td colspan="4" class="text-center">No data found</td>
                    </tr>
                <?php
            }
            
            ?>
            </tbody>
        </table>
    </div>