<?php
$fromdate= str_replace('/', '-',$fromdate);
$from_date= date("Y-m-d", strtotime($fromdate));
$todate= str_replace('/', '-',$todate);
$end_date= date("Y-m-d", strtotime($todate));

$current_date = date('Y-m-d');
$model_lead= new Lead();
$count_lead = $model_lead->with('file')->count(array('condition'=>'t.isActive="Y" AND (enq_date >= "'.$from_date.'" AND enq_date <= "'.$end_date.'")'));
$model_quote= new Quotes();
$count_quote = $model_quote->with('file')->count(array('condition'=>'t.isActive="Y" AND (qtn_start_date >= "'.$from_date.'" AND qtn_start_date <= "'.$end_date.'")') );
$model_order=new Order();
$count_order = $model_order->with('file')->count(array('condition'=>'t.isActive="Y" AND (order_date >= "'.$from_date.'" AND order_date <= "'.$end_date.'")') );
$model_invoice=new Invoice();
$count_invoice = $model_invoice->with('file')->count(array('condition'=>'t.isActive="Y" AND (invoice_date >= "'.$from_date.'" AND invoice_date <= "'.$end_date.'")'));
/*$SQL = "SELECT IFNULL(SUM(invoice_amount - credit_note_amount),0) AS invoice_amount FROM `view_invoice_creditnote_details` WHERE (transaction_date BETWEEN '".$from_date."' AND '".$end_date."') OR transaction_date IS NULL";
//$SQL = 'SELECT SUM(`exchange_rate` *`grand_total` ) FROM `erp_invoice` WHERE `invoice_date` >= "'.$from_date.'" AND invoice_date <= "'.$end_date.'"';
//echo $SQL;
$today_invoice_value = Yii::app()->db->createCommand($SQL)->queryScalar();	*/

$SQL = "SELECT SUM(exchange_rate * grand_total) AS invoice_amount FROM `erp_invoice` AS invoice WHERE isActive='Y' AND invoice_date >='".$from_date."' AND invoice_date<='".$end_date."'";
$arr_today_invoice_value = Yii::app()->db->createCommand($SQL)->queryRow();


$SQL = "SELECT SUM(exchange_rate * credited_amount) AS credit_note_amount FROM `erp_creditnote` AS CN WHERE CN.isActive='Y' AND credited_date >='".$from_date."' AND credited_date<='".$end_date."'";
$arr_credit_note_value = Yii::app()->db->createCommand($SQL)->queryRow();

$today_invoice_value = $arr_today_invoice_value['invoice_amount'] - $arr_credit_note_value['credit_note_amount']; 

$SQL = 'SELECT SUM(total_voucher_amount) FROM `erp_finance_voucher_head` WHERE (voucher_type=2 OR voucher_type=13) AND voucher_date >= "'.$from_date.'" AND voucher_date <= "'.$end_date.'" AND is_posted="Y"';
$today_collection=Yii::app()->db->createCommand($SQL)->queryScalar();
$SQL = 'SELECT SUM(total_voucher_amount) FROM `erp_finance_voucher_head` WHERE (voucher_type=1) AND voucher_date >= "'.$from_date.'" AND voucher_date <= "'.$end_date.'" AND is_posted="Y"';
$today_payment=Yii::app()->db->createCommand($SQL)->queryScalar();
$SQL = 'SELECT SUM(amount) FROM `erp_finance_voucher_head` H INNER JOIN erp_finance_voucher_items V ON V.header_id=H.id WHERE V.ledger_id=20 AND H.voucher_date <= "'.$end_date.'" AND H.is_posted="Y"';
$account_payable=Yii::app()->db->createCommand($SQL)->queryScalar();
$SQL = 'SELECT SUM(amount) FROM `erp_finance_voucher_head` H INNER JOIN erp_finance_voucher_items V ON V.header_id=H.id WHERE V.ledger_id=11 AND H.voucher_date <= "'.$end_date.'" AND H.is_posted="Y"';
$account_receivable=Yii::app()->db->createCommand($SQL)->queryScalar();

//purchase order
$SQL = 'SELECT SUM(stock_grand_total * exchange_rate) FROM `erp_purchasing_order` WHERE  order_date >= "'.$from_date.'" AND order_date <= "'.$end_date.'"';
//echo $SQL;
$today_purchase_order=Yii::app()->db->createCommand($SQL)->queryScalar();

//purchase invoice
$SQL = 'SELECT SUM(grand_total * exchange_rate) FROM `erp_purchasinginvoice` WHERE  invoice_date >= "'.$from_date.'" AND invoice_date <= "'.$end_date.'"';
$today_purchase_invoice=Yii::app()->db->createCommand($SQL)->queryScalar();

//purchase debit note
$SQL = 'SELECT SUM(debited_amount * exchange_rate) FROM `erp_purchasing_debit_note` WHERE  debited_date >= "'.$from_date.'" AND debited_date <= "'.$end_date.'"';
$today_purchase_debit_note=Yii::app()->db->createCommand($SQL)->queryScalar();


$total_purchased_amount = $today_purchase_invoice - $today_purchase_debit_note;



?>
<div class="clearfix"></div>
<br>
<!--a href="javascript:void(0)"  onclick="getTotalsBySalesLeads();">
       <div class="col-lg-3 col-sm-6">
           <section class="panel">
               <div class="symbol bg-terques">
                   <i class="fa fa-user"></i>
               </div>
               <div class="value">
                   <h3 class="count" id="lead_count"><?php echo $count_lead;?></h3>
                   <p>Total Leads
               </div>
           </section>
       </div>
   </a-->
   <a href="javascript:void(0)"  onclick="getTotalsBySalesQuotes();">
       <div class="col-lg-3 col-sm-6">
           <section class="panel">
               <div class="symbol bg-red">
                   <i class="fa fa-tags"></i>
               </div>
               <div class="value">
                   <h3 class="count" id="quote_count"><?php echo $count_quote;?></h3>
                   <p>Total Quotes</p>
               </div>
           </section>
       </div>
   </a>
   <a href="#20" onclick="getTotalsBySalesOrders();">
       <div class="col-lg-3 col-sm-6">
           <section class="panel">
               <div class="symbol bg-yellow">
                   <i class="fa fa-shopping-cart"></i>
               </div>
               <div class="value">
                   <h3 class="count" id="order_count"><?php echo $count_order;?></h3>
                   <p>Total Orders</p>
               </div>
           </section>
       </div>
   </a>
   <a href="#20" onclick="getTotalsBySalesInvoices();">
       <div class="col-lg-3 col-sm-6">
           <section class="panel">
               <div class="symbol bg-pista">
                   <i class="fa fa-bar-chart-o"></i>
               </div>
               <div class="value">
                   <h3 class="count" id="invoice_count"><?php echo $count_invoice;?></h3>
                   <p>Total Invoices</p>
               </div>
           </section>
       </div>
   </a>
   
    <a href="#20" onclick="getTotalsByLeadOwner();">
        <div class="col-lg-3 col-sm-6">
            <section class="panel">
                <div class="symbol bg-purple">
                    <i class="fa fa-shopping-cart"></i>
                </div>
                <div class="value">
                    <h3 class="count" id="invoice_value"><?php echo Yii::app()->numberFormatter->formatCurrency($today_invoice_value,"");?></h3>
                    <p>Total Sales</p>
                </div>
            </section>
        </div>
    </a>
    
     <a href="#20" onclick="getTotalsByReceipt();">
        <div class="col-lg-3 col-sm-6">
            <section class="panel">
                <div class="symbol"  style="background:#990000;">
                    <i class="fa fa-money"></i>
                </div>
                <div class="value">
                    <h3 class="count" id="invoice_value"><?php echo Yii::app()->numberFormatter->formatCurrency($today_collection,"");?></h3>
                    <p>Total Collection</p>
                </div>
            </section>
        </div>
    </a>
    
     <a href="#20" onclick="getTotalsByPayment();">
        <div class="col-lg-3 col-sm-6">
            <section class="panel">
                <div class="symbol" style="background:#00004d;">
                    <i class="fa fa-exchange"></i>
                </div>
                <div class="value">
                    <h3 class="count" id="invoice_value"><?php echo Yii::app()->numberFormatter->formatCurrency($today_payment,"");?></h3>
                    <p>Total Payment</p>
                </div>
            </section>
        </div>
    </a>
     <a href="#20" onclick="getTotalsByAccountPayables();">
        <div class="col-lg-3 col-sm-6">
            <section class="panel">
                <div class="symbol" style="background:#00ffcc;">
                    <i class="fa fa-exchange"></i>
                </div>
                <div class="value">
                    <h3 class="count" id="invoice_value"><?php echo Yii::app()->numberFormatter->formatCurrency(-1*$account_payable,"");?></h3>
                    <p>Account Payables</p>
                </div>
            </section>
        </div>
    </a>
    
    <a href="#20" onclick="getTotalsByAccountReceivables();">
        <div class="col-lg-3 col-sm-6">
            <section class="panel">
                <div class="symbol" style="background:#999966;">
                    <i class="fa fa-exchange"></i>
                </div>
                <div class="value">
                    <h3 class="count" id="invoice_value"><?php echo Yii::app()->numberFormatter->formatCurrency($account_receivable,"");?></h3>
                    <p>Account Receivables</p>
                </div>
            </section>
        </div>
    </a>
    
    <a href="#20" onclick="getTotalsByPurcahseOrders();">
        <div class="col-lg-3 col-sm-6">
            <section class="panel">
                <div class="symbol" style="background:#a472c5;">
                    <i class="fa fa-book"></i>
                </div>
                <div class="value">
                    <h3 class="count" id="invoice_value"><?php echo Yii::app()->numberFormatter->formatCurrency($today_purchase_order,"");?></h3>
                    <p>Purchase Order</p>
                </div>
            </section>
        </div>
    </a>
    
     <a href="#20" onclick="getTotalsByPurchaseInvoices();">
        <div class="col-lg-3 col-sm-6">
            <section class="panel">
                <div class="symbol" style="background:#990000;">
                    <i class="fa fa-list"></i>
                </div>
                <div class="value">
                    <h3 class="count" id="invoice_value"><?php echo Yii::app()->numberFormatter->formatCurrency($total_purchased_amount,"");?></h3>
                    <p>Purchase Invoice</p>
                </div>
            </section>
        </div>
    </a>