<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;
        background-color: #1F67B1;
        color: #fff;
    }
    .table-tree th, td{
        border-left: 0;
        border-right: 0;
        margin: 0;
        padding: 3px 5px;
        border :1px solid #1F67B1;
    }
    .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: 120px;

        max-width: 330px;

        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 :1px solid #1F67B1;
    }
    .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{
      border :1px solid #1F67B1;
    }
    .footer {
      width:100%;
      float:left;
      font-size:11px;
      position:absolute;
      bottom:0px;
      left:0px;
      text-align:center !important;
      line-height:18px;
      border-bottom: 1px double #1F67B1;
      padding: 5px;
    }
    .span1{
        width: 80px !important;
        display: inline-block;
        float: left;
    }
    .span2{
        width: 80px !important;
        display: inline-block;
        float: left;
    }
    .span3{
        width: auto !important;
        display: inline-block;
        float: left;
    }
    .span5{
        width: 25%;
        display: inline-block;
        float: right;
    }
  }
</style>

<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>Trial Balance</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>End Date</b>
                            : <?php echo date('d/m/Y',strtotime($date_from)); ?></div><br>
                        </div>
                </div>

        </div>
        </div>
    <table class="table-tree">
<thead>
        <tr>
            <th class="cell-left">No.</th><th class="cell-left">Account</th>
            <th class="txt-right">Debit</th><th class="txt-right">Credit</th>
        </tr>
</thead>
<?php 
/*FinGeneralLedger::model()->deleteAll(array('condition'=>'voucher_no="TB/1001"'));
$query = "SELECT SUM(sorder.grand_total)-SUM(sorder.total_tax_amount) from crm_order sorder LEFT JOIN crm_master_file mfile ON sorder.file_id=mfile.id WHERE sorder.order_date >= '2018-01-01' AND  mfile.current_process_status IN ('D','PD','PI','I')";
$total_val = Yii::app()->db->createCommand($query)->queryScalar();
if( $total_val > 0 ){
     //Debit Entry
    $acc_data = FinSettings::getAccountData(99994);  //Uninvoiced debtors
    $LedgerDebit = new FinGeneralLedger;
    $LedgerDebit->date = date('Y-m-d');
    $LedgerDebit->account_id = $acc_data->id;
    $LedgerDebit->voucher_no = 'TB/1001';
    $LedgerDebit->debit = $total_val;
    $LedgerDebit->db_entered = $total_val;
    $LedgerDebit->isAllocated = 'Y';
    $LedgerDebit->save(false);
    //Credit Entry
    $acc_data = FinSettings::getAccountData(99912);  //Uninvoiced sales
    $LedgerCredit = new FinGeneralLedger;
    $LedgerCredit->date = date('Y-m-d');
    $LedgerCredit->account_id = $acc_data->id;
    $LedgerCredit->voucher_no = 'TB/1001';
    $LedgerCredit->credit = $total_val;
    $LedgerCredit->cr_entered = $total_val;
    $LedgerCredit->isAllocated = 'Y';
    $LedgerCredit->save(false);
}
/** End */
/*$query = "SELECT SUM(porder.stock_grand_total)-SUM(porder.total_tax_amount) from crm_purchasing_order porder LEFT JOIN crm_master_file mfile ON porder.file_id=mfile.id WHERE porder.order_date >= '2018-01-01' AND  mfile.current_process_status IN ('M_R','Q_C','P_PI','P_I')";
$total_val = Yii::app()->db->createCommand($query)->queryScalar();
if( $total_val > 0 ){
    //Debit Entry
   $acc_data = FinSettings::getAccountData(66612);  //Uninvoiced purchase
   $LedgerDebit = new FinGeneralLedger;
   $LedgerDebit->date = date('Y-m-d');
   $LedgerDebit->account_id = $acc_data->id;
   $LedgerDebit->voucher_no = 'TB/1001';
   $LedgerDebit->debit = $total_val;
   $LedgerDebit->db_entered = $total_val;
   $LedgerDebit->isAllocated = 'Y';
   $LedgerDebit->save(false);
   //Credit Entry
   $acc_data = FinSettings::getAccountData(99995);  //Uninvoiced creditor
   $LedgerCredit = new FinGeneralLedger;
   $LedgerCredit->date = date('Y-m-d');
   $LedgerCredit->account_id = $acc_data->id;
   $LedgerCredit->voucher_no = 'TB/1001';
   $LedgerCredit->credit = $total_val;
   $LedgerCredit->cr_entered = $total_val;
   $LedgerCredit->isAllocated = 'Y';
   $LedgerCredit->save(false);
} */
?>

<?php
$time=strtotime($date_from);
$year=date("Y",$time);
Yii::app()->db->createCommand("TRUNCATE TABLE trail_balance_tbl")->execute();        


Yii::app()->db->createCommand("INSERT INTO  trail_balance_tbl (
                               SELECT 
                               accounts.sort_order,accounts.opening_bal,accounts.opening_bal_type,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 ledger.isAllocated='Y' AND ledger.date<='".$date_from."' AND ( accounts.account_type='A' OR accounts.account_type='L') 
                               group by accounts.account_no 
                               order by accounts.account_no)")->execute();

Yii::app()->db->createCommand("INSERT INTO  trail_balance_tbl (
                               SELECT accounts.sort_order,accounts.opening_bal,accounts.opening_bal_type,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 ledger.isAllocated='Y' AND ledger.date>='$year-01-01' AND ledger.date<='".$date_from."' AND ( accounts.account_type='I' OR accounts.account_type='E') 
                               group by accounts.account_no 
                               order by accounts.account_no)")->execute();

Yii::app()->db->createCommand("INSERT INTO  trail_balance_tbl (select sort_order,opening_bal,opening_bal_type,account_no,description,level,account_type,0 AS debit,0 AS credit FROM crm_fin_accounts)")->execute();
$SQL="select * from  trail_balance_tbl group by account_no order by sort_order";  //make it select * from
$arr_sum_amount1 	= Yii::app()->db->createCommand($SQL)->queryAll();

$tot_debit=0;
$tot_credit=0;
$tot_bal=0;
foreach($arr_sum_amount1 as $arr_sum_amount)
{
    			$amount = "0.00";
                        $balance="0.00";
                        $credit="0.00";
                        $debit="0.00";

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

                

                if($arr_sum_amount['account_type']=="A" || $arr_sum_amount['account_type']=="E")
                {
                    if($arr_sum_amount['type']==1)
                    {
                        $credit+=$arr_sum_amount['opening_bal'];
                    }else{
                        $debit+=$arr_sum_amount['opening_bal'];
                    }
                    $balance=$debit-$credit;
                }
                else if($arr_sum_amount['account_type']=="L" || $arr_sum_amount['account_type']=="I")
                {
                     if($arr_sum_amount['type']==2)
                     {
                         $debit+=$arr_sum_amount['opening_bal'];    
                     }
                     else {
                        $credit+=$arr_sum_amount['opening_bal'];
                     }
                $balance=$credit-$debit;
                }
                
                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)
                                 {
                                $tot_credit+=abs($balance);
                                
                                 echo '<td class="txt-right l1"><b>0.00 adharsh</b></td><td class="txt-right l1"><b>'.Yii::app()->numberFormatter->formatCurrency(abs($balance),"").'</b></td></tr>';
                                 }
                                 else  if($balance>0) {
                                    $tot_debit+=$balance;
                                    echo '<td class="txt-right l1"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td><td class="txt-right l1"><b>0.00</b></td></tr>';
                                } 
                                else if($balance==0) {
                                    echo '<td>&nbsp;</td><td>&nbsp;</td>';
                                }
                        }
                        else if($arr_sum_amount['account_type']=="L" || $arr_sum_amount['account_type']=="I")
                        {
                            if($balance<0)
                            {
                            $tot_debit+=abs($balance);
                           
                             echo '<td class="txt-right l1"><b>'.Yii::app()->numberFormatter->formatCurrency(abs($balance),"").'</b></td><td class="txt-right l1"><b>0.00</b></td></tr>';
                            }
                            else if($balance>0) {
                                $tot_credit+=$balance;
                                echo '<td class="txt-right l1"><b>0.00</b></td><td class="txt-right l1"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td></tr>';
                            }
                            else if($balance==0) {
                                echo '<td>&nbsp;</td><td>&nbsp;</td>';
                            }
                        }  
                        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']=="A" || $arr_sum_amount['account_type']=="E")
                {

                                 if($balance<0)
                                 {
                                    $tot_credit+=abs($balance);
                                    echo '<td class="txt-right l2"><b>0.00</b></td><td class="txt-right l2"><b>'.Yii::app()->numberFormatter->formatCurrency(abs($balance),"").'</b></td></tr>';
                                 }
                                 else if($balance>0) {
                                    $tot_debit+=$balance;
                                    echo '<td class="txt-right l2"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td><td class="txt-right l2"><b>0.00</b></td></tr>';
                                 } 
                                 else if($balance==0) {
                                    echo '<td>&nbsp;</td><td>&nbsp;</td>';
                                }

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

                                  if($arr_sum_amount['account_no']=='999C')
                                  {
                                  $y=date('Y', strtotime($date_from));  
                                  $this_year=date('Y');
                                  }
                                      if($balance<0)
                                      {
                                        $tot_debit+=abs($balance);
                                        echo '<td class="txt-right l2"><b>'.Yii::app()->numberFormatter->formatCurrency(abs($balance),"").'</b></td><td class="txt-right l2"><b>0.00</b></td></tr>';
                                      }
                                      else if($balance>0) {
                                        $tot_credit+=$balance;
                                        echo '<td class="txt-right l2"><b>0.00</b></td><td class="txt-right l2"><b>'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</b></td></tr>';
                                    }
                                    else if($balance==0) {
                                        echo '<td>&nbsp;</td><td>&nbsp;</td>';
                                    }

                            }
                            break;
                    case 3:
                            			

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

                                    if($balance<0)
                                    {
                                        $tot_credit+=abs($balance);
                                        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">0.00</td><td class="txt-right l3">'.Yii::app()->numberFormatter->formatCurrency(abs($balance),"").'</td></tr>';
                                    }
                                    else if($balance>0) {
                                        $tot_debit+=$balance;
                                        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">'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</td><td class="txt-right l3">0.00</td></tr>';
                                    } else if($balance==0) {
                                        echo '<tr><td class="l3">'.$arr_sum_amount['account_no'].'</td><td class="level-3 l3">'.$arr_sum_amount['description'].'</td><td>&nbsp;</td><td>&nbsp;</td></tr>';
                                        }

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

                                      if($balance<0)
                                      {
                                            $tot_debit+=abs($balance);   
                                            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">'.Yii::app()->numberFormatter->formatCurrency(abs($balance),"").'</td><td class="txt-right l3">0.00</td></tr>';
                                      }
                                      else if($balance>0) {
                                            $tot_credit+=$balance;
                                            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">0.00</td><td class="txt-right l3">'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</td></tr>';
                                    } else if($balance==0) {
                                        echo '<tr><td class="l3">'.$arr_sum_amount['account_no'].'</td><td class="level-3 l3">'.$arr_sum_amount['description'].'</td><td>&nbsp;</td><td>&nbsp;</td></tr>';
                                        }


                       }
                       break;
                    case 4:
                        

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

                                    if($balance<0)
                                    {
                                        $tot_credit+=abs($balance); 
                                        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">0.00</td><td class="txt-right l4">'.Yii::app()->numberFormatter->formatCurrency(abs($balance),"").'</td></tr>';
                                    }
                                    else if($balance>0) {
                                        $tot_debit+=$balance;
                                        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">'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</td><td class="txt-right l4">0.00</td></tr>';
                                    } else if($balance==0) {
                                        echo '<tr><td class="l4">'.$arr_sum_amount['account_no'].'</td><td class="level-4 l4">'.$arr_sum_amount['description'].'</td><td>&nbsp;</td><td>&nbsp;</td></tr>';
                                    }

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

                                      if($balance<0)
                                      {
                                            $tot_debit+=abs($balance); 
                                            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">'.Yii::app()->numberFormatter->formatCurrency(abs($balance),"").'</td><td class="txt-right l4">0.00</td></tr>';
                                      }
                                      else if($balance>0) {
                                           $tot_credit+=$balance;
                                           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">0.00</td><td class="txt-right l4">'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</td></tr>';
                                      }else if($balance==0) {
                                        echo '<tr><td class="l4">'.$arr_sum_amount['account_no'].'</td><td class="level-4 l4">'.$arr_sum_amount['description'].'</td><td>&nbsp;</td><td>&nbsp;</td></tr>';
                                    }



                }
                break;
                    case 5:
                 

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

                                    if($balance<0)
                                    {
                                        $tot_credit+=abs($balance); 
                                        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">0.00</td><td class="txt-right l4">'.Yii::app()->numberFormatter->formatCurrency(abs($balance),"").'</td></tr>';
                                    }
                                    else if($balance>0) {
                                        $tot_debit+=$balance;        
                                        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">'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</td><td class="txt-right l4">0.00</td></tr>';
                                    }else if($balance==0) {
                                        echo '<tr><td class="l4">'.$arr_sum_amount['account_no'].'</td><td class="level-4 l4">'.$arr_sum_amount['description'].'</td><td>&nbsp;</td><td>&nbsp;</td></tr>';
                                    }

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

                
                                      if($balance<0)
                                      {
                                        $tot_debit+=abs($balance);   
                                        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">'.Yii::app()->numberFormatter->formatCurrency(abs($balance),"").'</td><td class="txt-right l4">0.00</td></tr>';
                                      }
                                      else if($balance>0) {
                                        $tot_credit+=$balance;      
                                        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">0.00</td><td class="txt-right l4">'.Yii::app()->numberFormatter->formatCurrency($balance,"").'</td></tr>';
                                      }else if($balance==0) {
                                        echo '<tr><td class="l4">'.$arr_sum_amount['account_no'].'</td><td class="level-4 l4">'.$arr_sum_amount['description'].'</td><td>&nbsp;</td><td>&nbsp;</td></tr>';
                                      }

                        }
                        break;
                        } 

}


                    ?>

    <?php
   // FinGeneralLedger::model()->deleteAll(array('condition'=>'voucher_no="TB/1001"'));
        

                    echo '<tr><td colspan="2"></td>
                          <td class="txt-right br-top"><b>'.Yii::app()->numberFormatter->formatCurrency($tot_debit,"").'</b></td><td class="txt-right br-top"><b>'.Yii::app()->numberFormatter->formatCurrency($tot_credit,"").'</b></td></tr></table>';

                        ?>

    </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-->