<style type="text/css">
@media print {
    @page {
    margin-top: 1.2cm;
    margin-bottom: 3cm;
  }
       *{

        box-sizing: border-box;

    }
    .table-tree{
        width:100%;
        text-align:left;
        padding-left:10px !important;
        font-size: 12px;
        border-collapse: collapse;
        border-spacing: 0;
    }
    .table-tree th{
        padding-top:5px;
        padding-bottom:5px;
        border-top:1px solid #000;
        border-bottom:1px solid #000;
    }
    .table-tree th, td{
        border-left: 0;
        border-right: 0;
        margin: 0;
        padding: 3px 5px;
    }
    .ze{background: #CCC}
    .box-pad,.box-pad *{
        -moz-box-sizing: border-box;
        -webkit-box-sizing: border-box;
        box-sizing: border-box;
    }
    .tre td{
        border:none;
    }
    .level-1{
        padding-left:5px !important;
    }
    .level-2{
        padding-left: 20px !important;

    }
    .level-3{
        padding-left: 35px !important;
    }
    .level-4{
        padding-left: 55px !important;
    }
    .l1{
        font-size: 12px !important;
    }
    .l2{
        font-size: 11px !important;
        font-style: italic !important;
    }
    .l3{
        font-size: 12px !important;
    }

    .l4{
        font-size: 11px !important;
        font-style: italic;
    }
    .txt-right{
        text-align: right !important;
    }

    .tittle{

        width: 100%;

        margin:10px 0;

        float: left;

        text-align: center;



    }

    .tittle h6{

        border-bottom:2px solid #000;

        width: 150px;

        max-width: 400px;

        font-size: 16px;

        margin: 10px auto;

    }

    .date{
        width: 100%;
        float: left;
        font-size: 11px;
        padding-left: 5px;
        font-weight: 600;
        text-align: left;
        padding-bottom: 3px;
    }

    .date span{

        width: 200px;

        display: inline-block;

    }
    .clear{
        clear: both;
    }
    .cell-left{
      text-align: left !important;
    }
    .cell-right{
      text-align: right !important;
    }
    .br-top{
      border-top: 1px solid #000 !important;
      border-bottom: 3px double #000 !important;
    }
    .space{
      width: 100%;
      float: left;
      clear: both;
      height: 5px;
    }
    .col-6{
      width: 50%;
    }
    .col-9{
      width: 75%
    }
    .col-3{
      width:25%;
    }
    .f-l{
      float: left;
    }
    .f-r{
      float: right;
    }
    .txt-right{
      text-align: right;
    }
    .txt-left{
      text-align: left;
    }
    .x-space{
      box-sizing: border-box;
    }
    .bg-w{
      background: #FFF !important;
    }
    .footer {
      width:100%;
      float:left;
      font-size:11px;
      position:absolute;
      bottom:0px;
      left:0px;
      text-align:center !important;
      line-height:18px;
      border-top: 1px dotted #000;
      padding: 5px;
    }
    .span1{
        width: 80px !important;
        display: inline-block;
        float: left;
    }
     .span5{
        width: 25%;
        display: inline-block;
        float: right;
    }
    .span2{
        width: 80px !important;
        display: inline-block;
        float: left;
    }
    .span3{
        width: auto !important;
        display: inline-block;
        float: left;
    }
    .pro{
        background: #ccffdf;
    }
    .loss{
        background: #ffb4b4;
    }
    .cy{
        background: #cdcdcd;
    }
    .py{
        background: #ececec;
    }
    .to{
        background: #f1f1f1;
    }
}   
</style>
<?php
$prev_from=strtotime("$date_from -1 year");
$prev_from=date('Y-m-d', $prev_from);
$prev_to=strtotime("$date_to -1 year");
$prev_to=date('Y-m-d', $prev_to);
?>

<div id="finance-account-trialbal">
 <div class="container">
            <div class="heading tittle">
                    <div><h2><?php echo Yii::app()->user->master_company_name;?></h2></div>
            </div>
	</div>
        <div class="tittle"><h6>Profit and Loss</h6></div>
        <div class="container-fluid">
        <div class="col-md-12">
                <div class="col-md-9"></div>
                <div class="col-md-3">
                                <div class="date">
                            <div class="span5">
                                <b>Date From</b>
                            : <?php echo date('d/m/Y',strtotime($date_from)); ?></div><br>
                            <div class="span5">
                                <b>Date To</b>
                            : <?php echo date("d/m/Y", strtotime($date_to)); ?></div>
                        </div>
                </div>

        </div>
        </div>
    <table class="table-tree">
<thead>
        <tr>

            <th class="cell-left">Account No.</th><th class="cell-left">Description</th><th class="txt-right cy">Current Year</th><th class="txt-right py">Previous Year</th>

        </tr>
</thead>





         <?php
$SQL1="CREATE OR REPLACE VIEW trial_balance AS (SELECT accounts.sort_order,accounts.opening_bal,accounts.account_no,accounts.description,accounts.level,accounts.account_type,sum(ledger.debit) AS debit,sum(ledger.credit) AS credit FROM `crm_fin_general_ledger` as ledger left join `crm_fin_accounts` as accounts on ledger.account_id=accounts.id WHERE accounts.account_type='I' AND ledger.isAllocated='Y' AND ledger.date>='".$date_from."' AND ledger.date<='".$date_to."' group by accounts.account_no order by accounts.account_no) UNION (select sort_order,opening_bal,account_no,description,level,account_type,0 AS debit,0 AS credit FROM crm_fin_accounts where account_type='I')" ;
Yii::app()->db->createCommand($SQL1)->execute();
$SQL="select * from trial_balance group by account_no order by sort_order";
$SQL3="CREATE OR REPLACE VIEW trial_balance1 AS (SELECT accounts.sort_order,accounts.opening_bal,accounts.account_no,accounts.description,accounts.level,accounts.account_type,sum(ledger.debit) AS debit,sum(ledger.credit) AS credit FROM `crm_fin_general_ledger` as ledger left join `crm_fin_accounts` as accounts on ledger.account_id=accounts.id WHERE accounts.account_type='I' AND ledger.isAllocated='Y' AND ledger.date>='".$prev_from."' AND ledger.date<='".$prev_to."' group by accounts.account_no order by accounts.account_no) UNION (select sort_order,opening_bal,account_no,description,level,account_type,0 AS debit,0 AS credit FROM crm_fin_accounts where account_type='I')" ;
Yii::app()->db->createCommand($SQL3)->execute();
$SQL11="select * from trial_balance group by account_no order by sort_order";

                $arr_sum_amount1 	= Yii::app()->db->createCommand($SQL11)->queryAll();
$SQL21="select * from trial_balance1 group by account_no order by sort_order";

                $arr_sum_amount11 	= Yii::app()->db->createCommand($SQL21)->queryAll();


$tot_debit=0;
$tot_debit1=0;
$tot_credit=0;
$tot_credit1=0;
foreach($arr_sum_amount1 as $i=>$arr_sum_amount)
{
    			$amount = "0.00";
                        $balance="0.00";
                        $balance1="0.00";
                        $credit="0.00";
                        $debit="0.00";
                        $credit1="0.00";
                        $debit1="0.00";
    			if(isset($arr_sum_amount['credit']) && $arr_sum_amount['credit']!="")
                        {
                            
    				$credit+= $arr_sum_amount['credit'];
                                $credit1+= $arr_sum_amount11[$i]['credit'];
                               
                        }
                        if(isset($arr_sum_amount['debit']) && $arr_sum_amount['debit']!="")
                        { 
                                $debit+= $arr_sum_amount['debit'];
                                $debit1+= $arr_sum_amount11[$i]['debit'];
                        
                        }
    		

                
 if($arr_sum_amount['account_type']=="L" || $arr_sum_amount['account_type']=="I")
                {
                    if($arr_sum_amount['account_no']==2124 || $arr_sum_amount['account_no']==2127)
                    {
                    $debit+=$arr_sum_amount['opening_bal'];    
                    }
                    else {
                $credit+=$arr_sum_amount['opening_bal'];
                $credit1+=$arr_sum_amount['opening_bal'];
                    }
                $balance=$credit-$debit;
                $balance1=$credit1-$debit1;
                }
                
                switch($arr_sum_amount['level'])
                {
                    case 1:
                     	
             echo '<tr class="l1"><td class="l1"><b>'.$arr_sum_amount['account_no'].'</b></td><td class="level-1 l1"><b>'.$arr_sum_amount['description'].'</b></td>';
                 if($arr_sum_amount['account_type']=="L" || $arr_sum_amount['account_type']=="I")
                {
                           
                             $tot_credit+=$balance;
                             $tot_credit1+=$balance1;
                            echo '<td class="txt-right l1 cy"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td><td class="txt-right l1 py"><b>'.Yii::app()->numberFormatter->formatCurrency($balance1,"").'</b></td></tr>';
                        
                }  
                break;
                    case 2:

                       echo '<tr  class="l2"><td class="l2"><b>'.$arr_sum_amount['account_no'].'</b></td><td class="level-2 l2"><b>'.$arr_sum_amount['description'].'</b></td>';
                  if($arr_sum_amount['account_type']=="L" || $arr_sum_amount['account_type']=="I")
                {
                          
                                    $tot_credit+=$balance;
                                    $tot_credit1+=$balance1; 
                                   
                            echo '<td class="txt-right l2 cy"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td><td class="txt-right l2 py"><b>'.Yii::app()->numberFormatter->formatCurrency($balance1,"").'</b></td></tr>';
                                 

                            }
                            break;
                    case 3:
       if($arr_sum_amount['account_type']=="L" || $arr_sum_amount['account_type']=="I")
                {

                                    
                                  $tot_credit+=$balance;
                                  $tot_credit1+=$balance1; 
                                  echo '<tr class="l3"><td class="l3">'.$arr_sum_amount['account_no'].'</td><td class="level-3 l3">'.$arr_sum_amount['description'].'</td>';
                            echo '<td class="txt-right l3 cy">'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</td><td class="txt-right l3 py">'.Yii::app()->numberFormatter->formatCurrency($balance1,"").'</td></tr>';
                                 
                       }
                       break;
                    case 4:
                        
                        if($arr_sum_amount['account_type']=="L" || $arr_sum_amount['account_type']=="I")
                {

                                  
                                           $tot_credit+=$balance;
                                           $tot_credit1+=$balance1; 
                                           echo '<tr  class="l4"><td class="l4">'.$arr_sum_amount['account_no'].'</td><td class="level-4 l4">'.$arr_sum_amount['description'].'</td>';
                               echo '<td class="txt-right l4 cy">'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</td><td class="txt-right l4 py">'.Yii::app()->numberFormatter->formatCurrency($balance1,"").'</td></tr>';
                                      

                }
                break;
                    case 5:

                      if($arr_sum_amount['account_type']=="L" || $arr_sum_amount['account_type']=="I")
                {

                                     $tot_credit+=$balance;    
                                     $tot_credit1+=$balance1;    
                                     echo '<tr  class="l4"><td class="l4">'.$arr_sum_amount['account_no'].'</td><td class="level-4 l4">'.$arr_sum_amount['description'].'</td>';
                               echo '<td class="txt-right l4 cy">'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</td><td class="txt-right l4 py">'.Yii::app()->numberFormatter->formatCurrency($balance1,"").'</td></tr>';
                     
                }
                break;
                } 

}

                    ?>

    <?php

                    echo '<tr><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td class="py"></td><td class="py"></td></tr>
                        <tr class="to"><td></td><td><b>Total</b></td><td class="txt-right br-top py"><b>'.Yii::app()->numberFormatter->formatCurrency($tot_credit,"").'</b></td><td class="txt-right br-top py"><b>'.Yii::app()->numberFormatter->formatCurrency($tot_credit1,"").'</b></td></tr>'
                            . '<tr><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td class="py"></td><td class="py"></td></tr>';
                    ?>

                    <?php
                    
$SQL1="CREATE OR REPLACE VIEW trial_balance AS (SELECT accounts.sort_order,accounts.opening_bal,accounts.account_no,accounts.description,accounts.level,accounts.account_type,sum(ledger.debit) AS debit,sum(ledger.credit) AS credit FROM `crm_fin_general_ledger` as ledger left join `crm_fin_accounts` as accounts on ledger.account_id=accounts.id WHERE accounts.account_type='E' AND ledger.isAllocated='Y' AND ledger.date>='".$date_from."' AND ledger.date<='".$date_to."' group by accounts.account_no order by accounts.account_no) UNION (select sort_order,opening_bal,account_no,description,level,account_type,0 AS debit,0 AS credit FROM crm_fin_accounts where account_type='E')" ;
Yii::app()->db->createCommand($SQL1)->execute();
$SQL2="CREATE OR REPLACE VIEW trial_balance1 AS (SELECT accounts.sort_order,accounts.opening_bal,accounts.account_no,accounts.description,accounts.level,accounts.account_type,sum(ledger.debit) AS debit,sum(ledger.credit) AS credit FROM `crm_fin_general_ledger` as ledger left join `crm_fin_accounts` as accounts on ledger.account_id=accounts.id WHERE accounts.account_type='E' AND ledger.isAllocated='Y' AND ledger.date>='".$prev_from."' AND ledger.date<='".$prev_to."' group by accounts.account_no order by accounts.account_no) UNION (select sort_order,opening_bal,account_no,description,level,account_type,0 AS debit,0 AS credit FROM crm_fin_accounts where account_type='E')" ;
Yii::app()->db->createCommand($SQL2)->execute();
$SQL="select * from trial_balance group by account_no order by sort_order";
$SQL3="select * from trial_balance1 group by account_no order by sort_order";

                $arr_sum_amount1 	= Yii::app()->db->createCommand($SQL)->queryAll();
                $arr_sum_amount3 	= Yii::app()->db->createCommand($SQL3)->queryAll();


$tot_bal=0;
foreach($arr_sum_amount1 as $i=>$arr_sum_amount)
{
    			$amount = "0.00";
                        $balance="0.00";
                         $balance1="0.00";
                        $credit="0.00";
                         $credit1="0.00";
                        $debit="0.00";
                        $debit1="0.00";

    			if(isset($arr_sum_amount['credit']) && $arr_sum_amount['credit']!="")
                        {
    				$credit+= $arr_sum_amount['credit'];
                                $credit1+= $arr_sum_amount3[$i]['credit'];
                               
                        }
                        if(isset($arr_sum_amount['debit']) && $arr_sum_amount['debit']!="")
                        { 
                                $debit+= $arr_sum_amount['debit'];
                                $debit1+= $arr_sum_amount3[$i]['debit'];
                        
                        }

                if($arr_sum_amount['account_type']=="A" || $arr_sum_amount['account_type']=="E")
                {
                    if($arr_sum_amount['account_no']!=4215)
                    {
                $debit+=$arr_sum_amount['opening_bal'];
                $debit1+= $arr_sum_amount['opening_bal'];
                $balance1=$debit1-$credit1;
                $balance=$debit-$credit;
                    }
                }
                else if($arr_sum_amount['account_type']=="L" || $arr_sum_amount['account_type']=="I")
                {
                    if($arr_sum_amount['account_no']==2124 || $arr_sum_amount['account_no']==2127)
                    {
                    $debit+=$arr_sum_amount['opening_bal'];    
                    }
                    else {
                $credit+=$arr_sum_amount['opening_bal'];
                $credit1+=$arr_sum_amount['opening_bal'];
                    }
                $balance=$credit-$debit;
                }
                if($arr_sum_amount['account_no']==4215)
                {
//                    $credit+= $arr_sum_amount['credit'];
//                    $credit1+= $arr_sum_amount3[$i]['credit'];
//                    $debit+= $arr_sum_amount['debit'];
//                    $credit1+= $arr_sum_amount3[$i]['debit'];
                $depreciation=$debit-$credit; 
                $depreciation1=$debit1-$credit1; 
                }
                else {
                switch($arr_sum_amount['level'])
                {
                    case 1:
                     	
             echo '<tr class="l1"><td class="l1"><b>'.$arr_sum_amount['account_no'].'</b></td><td class="level-1 l1"><b>'.$arr_sum_amount['description'].'</b></td>';
                        if($arr_sum_amount['account_type']=="A" || $arr_sum_amount['account_type']=="E")
                {
                              if($balance!=0 || $balance1!=0) {
                                $tot_debit+=$balance;
                                $tot_debit1+=$balance1;
                            
                           echo '<td class="txt-right l1 cy"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td><td class="txt-right l1 py"><b>'.Yii::app()->numberFormatter->formatCurrency($balance1,"").'</b></td></tr>';
                           }
                        }
                 
                break;
                    case 2:
                        	
//                                 }
//    			$balance=$debit-$credit;
                       echo '<tr  class="l2"><td class="l2"><b>'.$arr_sum_amount['account_no'].'</b></td><td class="level-2 l2"><b>'.$arr_sum_amount['description'].'</b></td>';
                             if($arr_sum_amount['account_type']=="A" || $arr_sum_amount['account_type']=="E")
                {
 if($balance!=0 || $balance1!=0) {
                                $tot_debit+=$balance;
                                 $tot_debit1+=$balance1;
                           echo '<td class="txt-right l1 cy"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td><td class="txt-right l1 py"><b>'.Yii::app()->numberFormatter->formatCurrency($balance1,"").'</b></td></tr>';
                           }
                        }
                            break;
                    case 3:
                            			
//                                 }
//    			$balance=$debit-$credit;
        
                            if($arr_sum_amount['account_type']=="A" || $arr_sum_amount['account_type']=="E")
                {

                                    if($balance!=0 || $balance1!=0)
                                    {
                                $tot_debit+=$balance;
                                $tot_debit1+=$balance1;
                                echo '<tr class="l3"><td class="l3">'.$arr_sum_amount['account_no'].'</td><td class="level-3 l3">'.$arr_sum_amount['description'].'</td>';
                           
                           echo '<td class="txt-right l1 cy"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td><td class="txt-right l1 py"><b>'.Yii::app()->numberFormatter->formatCurrency($balance1,"").'</b></td></tr>';
                                    }
                        }

                       break;
                    case 4:
                        
                           			
//                                 }
//    			$balance=$debit-$credit;
                            if($arr_sum_amount['account_type']=="A" || $arr_sum_amount['account_type']=="E")
                {
 if($balance!=0 || $balance1!=0) {
                                        $tot_debit+=$balance;
                                        $tot_debit1+=$balance1;
                                        echo '<tr  class="l4"><td class="l4">'.$arr_sum_amount['account_no'].'</td><td class="level-4 l4">'.$arr_sum_amount['description'].'</td>';
                            echo '<td class="txt-right l1 cy"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td><td class="txt-right l1 py"><b>'.Yii::app()->numberFormatter->formatCurrency($balance1,"").'</b></td></tr>';
                        }
                }
          
                break;
                    case 5:

                            if($arr_sum_amount['account_type']=="A" || $arr_sum_amount['account_type']=="E")
                {

                                     if($balance!=0) {
                                $tot_debit+=$balance;   
                                $tot_debit1+=$balance1;   
                                echo '<tr  class="l4"><td class="l4">'.$arr_sum_amount['account_no'].'</td><td class="level-4 l4">'.$arr_sum_amount['description'].'</td>';
                           echo '<td class="txt-right l1 cy"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td><td class="txt-right l1 py"><b>'.Yii::app()->numberFormatter->formatCurrency($balance1,"").'</b></td></tr>';
                        }
                }
                break;
                } 
                }
}


                    ?>

    <?php

                    echo '<tr><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td class="py"></td><td class="py"></td></tr>
                        <tr class="to"><td></td><td><b>Total(Before Depreciation)</b></td><td class="txt-right br-top cy"><b>'.Yii::app()->numberFormatter->formatCurrency($tot_debit,"").'</b></td><td class="txt-right br-top py"><b>'.Yii::app()->numberFormatter->formatCurrency($tot_debit1,"").'</b></td></tr>';
                    
                    echo '<tr><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td class="py"></td><td class="py"></td></tr>
                        <tr class="to"><td></td><td><b>Depreciation</b></td><td class="txt-right br-top cy"><b>'.Yii::app()->numberFormatter->formatCurrency($depreciation,"").'</b></td><td class="txt-right br-top py"><b>'.Yii::app()->numberFormatter->formatCurrency($depreciation1,"").'</b></td></tr>';
                   
                    echo '<tr><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td class="py"></td><td class="py"></td></tr>
                        <tr class="to"><td></td><td><b>Total(After Depreciation)</b></td><td class="txt-right br-top cy"><b>'.Yii::app()->numberFormatter->formatCurrency($tot_debit+$depreciation,"").'</b></td><td class="txt-right br-top py"><b>'.Yii::app()->numberFormatter->formatCurrency($tot_debit1+$depreciation1,"").'</b></td></tr>';    
                    $profit=$tot_debit+$depreciation-$tot_credit;
                    $profit1=$tot_debit1+$depreciation1-$tot_credit1;
                    if($profit1>0)
                    {
                    $profit1=-$profit1;    
                    }
                    else {
                    $profit1=abs($profit1);    
                    }
                    if($profit>0)
                    {
                      echo '<tr><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td><td></td></tr>
                        <tr class="loss"><td></td><td><b>Loss</b></td><td class="txt-right br-top"><b>'.Yii::app()->numberFormatter->formatCurrency($profit,"").'</b></td><td class="txt-right br-top"><b>'.Yii::app()->numberFormatter->formatCurrency($profit1,"").'</b></td></tr>';
                    }
                    else if($profit<0)
                    {
                      echo '<tr><td></td><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td><td></td></tr>
                        <tr class="pro"><td></td><td><b>Profit</b></td><td class="txt-right br-top"><b>'.Yii::app()->numberFormatter->formatCurrency(abs($profit),"").'</b></td><td class="txt-right br-top"><b>'.Yii::app()->numberFormatter->formatCurrency($profit1,"").'</b></td></tr>';   
                    }
                        ?>

    </table>

                    </div>

        <!--mpdf
        <htmlpagefooter name="myfooter">
            <div style="border-top: 1px dotted #000000; font-size: 12px; text-align: center; padding-top: 10px; ">
                <div style="text-align:left;width:50%;float:left">Printed on <?php
                  date_default_timezone_set("Asia/Dubai");
                 echo date('d/m/Y')." ".date("h:i:sa"); ?> </div>
                <div style="text-align:right;width:50%;float:left">Page {PAGENO} of {nb} </div>
            </div>
        </htmlpagefooter>
        <sethtmlpagefooter name="myfooter" page="all" value="on" />
        mpdf-->