<?php

use Drupal\Core\Database\Database;
use Drupal\ek_finance\Journal;

$journal = new Journal();

$period = $params['year'] . '-' . $params['month'] . 
        '-' . cal_days_in_month(CAL_GREGORIAN,$params['month'] ,$params['year']);

$dates = $journal->getFiscalDates($params['coid'], $params['year'], $params['month']);
$to = $dates['to'];
$stop_date = $dates['stop_date'];
//remove 1 day from stop date as we generate closing amount from opening of next period
$stop_date = date('Y-m-d', strtotime($dates['stop_date'] . ' - 1 day'));

if($dates['archive'] == TRUE) {
    $from = $dates['from'];
    
    //extract data from archive tables
    $table_accounts = "ek_accounts_" . $params['year'] . "_" . $params['coid'];
    $table_journal = "ek_journal_" . $params['year'] . "_" . $params['coid'];
    $alert = t('Archive data');
} else {
    $from = $dates['fiscal_start'];
    //extract data from current tables
    $table_accounts = "ek_accounts";
    $table_journal = "ek_journal";
    $alert = t('Fiscal year'). ' : ' . $dates['fiscal_year'];
}

class EKPDF extends TCPDF {

    function Header(){
        $data = $this->getHeaderData();
        $this->company = $data['title'];
        if ($this->company->logo != '') {
               if(file_exists($this->company->logo)) {
                   $logo = \Drupal::service('file_url_generator')->generateAbsoluteString($this->company->logo);
                   $info = getimagesize($logo);
                    $h = $info[1] * 50 / $info[0];
                    $this->Image($logo, 28, 10, $h);
               }
        }
        
        $this->Ln(5);
        $this->SetFont('helvetica','',12);
        $this->Cell(100);
        $this->Cell(50,5,$this->company->name,0,1);
        $this->SetFont('helvetica','',8);
        if ($this->company->reg_number) {
            $this->Cell(100);
            $this->Cell(8,3,"(".$this->company->reg_number .")",0,1);
        }
        if ($this->company->address1) {
            $this->Cell(100);    
            $this->Cell(60,4, $this->company->address1,0,1);
        }
        if ($this->company->address2) {
             $this->Cell(100);    
            $this->Cell(60,4, $this->company->address2,0,1);
        }
        $next = '';
        if ($this->company->postcode) {
            $next .= $this->company->postcode;
        }
        if($this->company->city){
            $next .= " " . $this->company->city;
        }
        if($this->company->country){
            $next .= ", " . $this->company->country;
        }
        $this->Cell(100);
        $this->Cell(50,4, $next ,0,1);
            
        /*if ($this->company->telephone) {
            $this->Cell(100);
            $this->Cell(8,3,t("tel:"). $this->company->telephone,0,1);
        }
        if ($this->company->fax) {
            $this->Cell(100);
            $this->Cell(8,4,t("fax:"). $this->company->fax,0,1);
        }*/
    }

    //Page footer
    function Footer() {
        //Position at 1.5 cm from bottom
        $this->SetY(-15);
        //helvetica italic 8
        $this->SetFont('helvetica','I',8);
        //Page number
        $this->Cell(0,5, t('Page') . ' ' . $this->getAliasNumPage() . '/' . $this->getAliasNbPages(), 0, 1, 'R');
    }
  
}

// create new PDF document
$pdf = new EKPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

// set document information

$pdf->SetCreator($company->name);
$pdf->SetAuthor(\Drupal::currentUser()->getAccountName());
$pdf->SetTitle(t('Balance sheet') . ' ' . $company->name);
$pdf->SetSubject(t('Balance sheet'));
$pdf->SetKeywords(t('Balance sheet'));

// set default header data
$pdf->SetHeaderData('','',$company);

// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// set margins
$format_doc = ['orientation' => 'P', 'format' => 'A4', 'margin_left' => 15, 
      'margin_top' => 40, 'margin_right' => 15, 'margin_bottom' => 25, 
      'margin_header' => 5, 'margin_footer' => 10, ];
$pdf->SetMargins($format_doc['margin_left'], $format_doc['margin_top'], $format_doc['margin_right']);
$pdf->SetHeaderMargin($format_doc['margin_header']);
$pdf->SetFooterMargin($format_doc['margin_footer']);

// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, $format_doc['margin_bottom']);

// set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

$pdf->SetTitle($fileName);
$pdf->AddPage($format_doc['orientation'],$format_doc['format']);

//margin settings - to adjust output
$m1=1; $m2=5; $m3=5;
$c1=25; $c2=30; $c3=30; $c3a=10; $c3b=20;
$c4=40; $c5=40;


//DISPLAY DATA
$pdf->SetTextColor(0,128,255);
$pdf->SetDrawColor(128, 128, 128);
$pdf->SetFont('helvetica','',16);
$pdf->Cell(60,5, t("Balance Sheet") ,'B', 1);
$pdf->SetFont('helvetica','',8);
$pdf->SetTextColor(0,0,0);
$pdf->Cell(60,5, t('As of') . ": " . $period ,'0',1,'L');
$pdf->Cell(60,4, '('. $alert . ')' ,'0',1,'L');
$pdf->Ln(5);

//header
$pdf->SetFont('helvetica','B',12);
    
    $pdf->Cell($c1,5, t('account') ,'B',0);
    $pdf->Cell($c2,5, t('name') ,'B',0);
    $pdf->Cell($c3,5,'','B',0);
    $pdf->Cell($c5,5,t('Base currency') . ' ' . $params['baseCurrency'] ,'B',0,'R');
    $pdf->Cell($c4,5, t('Multi currency') ,'B',1,'R');
    
 $pdf->Ln(3);   
    
////////////////////// ASSETS /////////////////////////////

//other assets
$total_class0 = 0;
$total_class0_l = 0;

$query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
$query->fields('t', ['aid','aname']);
$query->condition('aid', $chart['other_assets'] . '%', 'like');
$query->condition('atype', 'header', '=');
$query->condition('astatus', '1', '=');
$query->condition('coid', $params['coid'], '=');
$query->orderBy('aid', 'ASC');
$data = $query->execute();
$rh = $data->fetchObject();

//head-----------------------------//
$pdf->SetFont('helvetica','B',10);
$pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,5, $rh->aid ,0,0);
    $pdf->Cell($c2,5, $rh->aname ,0,1);  
$pdf->SetTextColor(88,88,88);
//-----------------------------//    

$query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
$query->fields('t', ['aid','aname']);
$query->condition('aid', $chart['other_assets'] . '%', 'like');
$query->condition('atype', 'class', '=');
$query->condition('astatus', '1', '=');
$query->condition('coid', $params['coid'], '=');
$query->orderBy('aid', 'ASC');
$result = $query->execute();


while ($r=$result->fetchAssoc()) {

if ($params['summary'] == 0){
    //class-----------------------------//
    $pdf->SetFont('helvetica','B',8);
        $pdf->Cell($m1,5,"",0,0);
        $pdf->Cell($c1,5,$r['aid'],0,0);
        $pdf->Cell($c2,5,$r['aname'],0,1);    
    //-----------------------------//  
}
$aid = substr($r['aid'],0,2);
$total_detail=0;
$total_detail_l=0;

    $query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
    $query->fields('t', ['aid','aname']);
    $query->condition('aid', $aid . '%', 'like');
    $query->condition('atype', 'detail', '=');
    $query->condition('coid', $params['coid'], '=');
    $query->orderBy('aid', 'ASC');
    $result2 = $query->execute();

    while ($r2=$result2->fetchAssoc()) {
    
        $b = $journal->opening(
                array( 
                'aid' => $r2['aid'],
                'coid' => $params['coid'],
                'from' => $stop_date,
                'archive' => $dates['archive']
                 )
                );
    
    
    $b0=$b[0]*-1;
    $b1=$b[1]*-1;
    //details-----------------------------//
    if (($b0 != 0 || $b1 != 0) && $params['summary'] == 0){
        //display details if option is selected and different from 0
        $pdf->SetFont('helvetica','',8);
        $pdf->Cell($m1,5,"",0,0);
        $pdf->Cell($m2,5,"",0,0);
        $pdf->Cell($c1,5,$r2['aid'],0,0);
        $pdf->Cell($c2,5,$r2['aname'],0,0);
        $pdf->Cell($c3b,5,"",0,0);   
        $pdf->Cell($c4,5,number_format($b1,2),0,0,'R'); 
        $pdf->Cell($c5,5,number_format($b0,2),0,1,'R');
    }
    //-----------------------------//  
    
    $total_detail+=$b0;
    $total_detail_l+=$b1;
    }    
    
    //Toal class-----------------------------//
    $pdf->SetFont('helvetica','B',9);
    $pdf->Cell($m1,5,"",0,0);
    $pdf->Cell($c1,5, t('Total') . ' ' . $r['aid'] ,0,0);
    $pdf->Cell($c2,5, $r['aname'] ,0,0);    
    $pdf->Cell($c3,5,"",0,0);   
    $pdf->Cell($c4,5,number_format($total_detail_l,2),0,0,'R'); 
    $pdf->Cell($c5,5,number_format($total_detail,2),0,1,'R');
    //-----------------------------//    
    
    $total_class0+=$total_detail;
    $total_class0_l+=$total_detail_l;
    
    
}

    //Total head-----------------------------//
    $pdf->SetFont('helvetica','B',10);
    $pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,8, t('Total') . ' ' . $rh->aid ,'T',0);
    $pdf->Cell($c2,8, $rh->aname ,'T',0);    
    $pdf->Cell($m1,8,"",'T',0);
    $pdf->Cell($c3,8,"",'T',0);   
    $pdf->Cell($c4,8,number_format($total_class0_l,2),'T',0,'R'); 
    $pdf->Cell($c5,8,number_format($total_class0,2),'T',1,'R');
    $pdf->SetTextColor(88,88,88);
    //-----------------------------// 
    


$total_detail=0;
$total_detail_l=0;
 
 
//assets
$total_class1 = 0;
$total_class1_l = 0;

$query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
$query->fields('t', ['aid','aname']);
$query->condition('aid', $chart['assets'] . '%', 'like');
$query->condition('atype', 'header', '=');
$query->condition('astatus', '1', '=');
$query->condition('coid', $params['coid'], '=');
$query->orderBy('aid', 'ASC');
$data = $query->execute();
$rh = $data->fetchObject();

//head-----------------------------//
$pdf->SetFont('helvetica','B',10);
$pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,5, $rh->aid ,0,0);
    $pdf->Cell($c2,5, $rh->aname ,0,1);  
$pdf->SetTextColor(88,88,88);
//-----------------------------//    

$query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
$query->fields('t', ['aid','aname']);
$query->condition('aid', $chart['assets'] . '%', 'like');
$query->condition('atype', 'class', '=');
$query->condition('astatus', '1', '=');
$query->condition('coid', $params['coid'], '=');
$query->orderBy('aid', 'ASC');
$result = $query->execute();


while ($r=$result->fetchAssoc()) {

if ($params['summary'] == 0){
    //class-----------------------------//
    $pdf->SetFont('helvetica','B',8);
        $pdf->Cell($m1,5,"",0,0);
        $pdf->Cell($c1,5,$r['aid'],0,0);
        $pdf->Cell($c2,5,$r['aname'],0,1);    
    //-----------------------------//  
}
$aid = substr($r['aid'],0,2);
$total_detail=0;
$total_detail_l=0;

    $query = Database::getConnection('external_db', 'external_db')
                        ->select($table_accounts, 't');
    $query->fields('t', ['aid','aname']);
    $query->condition('aid', $aid . '%', 'like');
    $query->condition('atype', 'detail', '=');
    $query->condition('coid', $params['coid'], '=');
    $query->orderBy('aid', 'ASC');
    $result2 = $query->execute();

    while ($r2=$result2->fetchAssoc()) {
    
        $b = $journal->opening(
                array( 
                'aid' => $r2['aid'],
                'coid' => $params['coid'],
                'from' => $stop_date,
                'archive' => $dates['archive']
                 )
                );
    
    
    $b0=$b[0]*-1;
    $b1=$b[1]*-1;
    //details-----------------------------//
    if (($b0 != 0 || $b1 != 0) && $params['summary'] == 0){
        //display details if option is selected and different from 0
        $pdf->SetFont('helvetica','',8);
        $pdf->Cell($m1,5,"",0,0);
        $pdf->Cell($m2,5,"",0,0);
        $pdf->Cell($c1,5,$r2['aid'],0,0);
        $pdf->Cell($c2,5,$r2['aname'],0,0);
        $pdf->Cell($c3b,5,"",0,0);   
        $pdf->Cell($c4,5,number_format($b1,2),0,0,'R'); 
        $pdf->Cell($c5,5,number_format($b0,2),0,1,'R');
    }
    //-----------------------------//  
    
    $total_detail+=$b0;
    $total_detail_l+=$b1;
    }    
    
    //Toal class-----------------------------//
    $pdf->SetFont('helvetica','B',9);
    $pdf->Cell($m1,5,"",0,0);
    $pdf->Cell($c1,5, t('Total') . ' ' . $r['aid'] ,0,0);
    $pdf->Cell($c2,5, $r['aname'] ,0,0);    
    $pdf->Cell($c3,5,"",0,0);   
    $pdf->Cell($c4,5,number_format($total_detail_l,2),0,0,'R'); 
    $pdf->Cell($c5,5,number_format($total_detail,2),0,1,'R');
    //-----------------------------//    
    
    $total_class1+=$total_detail;
    $total_class1_l+=$total_detail_l;
    
    
}

    //Total head-----------------------------//
    $pdf->SetFont('helvetica','B',10);
    $pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,8, t('Total') . ' ' . $rh->aid ,'T',0);
    $pdf->Cell($c2,8, $rh->aname ,'T',0);    
    $pdf->Cell($m1,8,"",'T',0);
    $pdf->Cell($c3,8,"",'T',0);   
    $pdf->Cell($c4,8,number_format($total_class1_l,2),'T',0,'R'); 
    $pdf->Cell($c5,8,number_format($total_class1,2),'T',1,'R');
    $pdf->SetTextColor(88,88,88);
    //-----------------------------// 
    
    //Grand Total assets-----------------------------//
    $pdf->SetFont('helvetica','B',10);
    $pdf->SetTextColor(0,128,255);
    $pdf->Cell($c1,8, t('Total Assets') ,'L,T,B',0);
    $pdf->Cell($c2,8, '' ,'T,B',0);    
    $pdf->Cell($m1,8,"",'T,B',0);
    $pdf->Cell($c3,8,"",'T,B',0);   
    $pdf->Cell($c4,8,number_format($total_class0_l + $total_class1_l,2),'T,B',0,'R'); 
    $pdf->Cell($c5,8,number_format($total_class0 + $total_class1,2),'T,B,R',1,'R');
    $pdf->SetTextColor(88,88,88);
    //-----------------------------// 

$total_detail=0;
$total_detail_l=0;


$pdf->Ln(3);

////////////////////// LIABILITIES /////////////////////////////
//liabilities header account, row head
$total_class2 = 0;
$total_class2_l = 0;

$query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
$query->fields('t', ['aid','aname']);
$query->condition('aid', $chart['liabilities'] . '%', 'like');
$query->condition('atype', 'header', '=');
$query->condition('astatus', '1', '=');
$query->condition('coid', $params['coid'], '=');
$query->orderBy('aid', 'ASC');
$data = $query->execute();
$rh = $data->fetchObject();


//head-----------------------------//
$pdf->SetFont('helvetica','B',10);
$pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,5, $rh->aid ,0,0);
    $pdf->Cell($c2,5, $rh->aname ,0,1);
$pdf->SetTextColor(88,88,88);
//-----------------------------//    

$query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
$query->fields('t', ['aid','aname']);
$query->condition('aid', $chart['liabilities'] . '%', 'like');
$query->condition('atype', 'class', '=');
$query->condition('astatus', '1', '=');
$query->condition('coid', $params['coid'], '=');
$query->orderBy('aid', 'ASC');
$result = $query->execute();


while ($r=$result->fetchAssoc()) {

if ($params['summary'] == 0){
    //class-----------------------------//
    $pdf->SetFont('helvetica','B',8);
        $pdf->Cell($m1,5,"",0,0);
        $pdf->Cell($c1,5, $r['aid'] ,0,0);
        $pdf->Cell($c2,5, $r['aname'] ,0,1);    
    //-----------------------------//  
}
$aid = substr($r['aid'],0,2);
$total_detail=0;
$total_detail_l=0;

    $query = Database::getConnection('external_db', 'external_db')
                        ->select($table_accounts, 't');
    $query->fields('t', ['aid','aname']);
    $query->condition('aid', $aid . '%', 'like');
    $query->condition('atype', 'detail', '=');
    $query->condition('coid', $params['coid'], '=');
    $query->orderBy('aid', 'ASC');
    $result2 = $query->execute();

    while ($r2 = $result2->fetchAssoc()) {
    
        $b = $journal->opening(
                array( 
                'aid' => $r2['aid'],
                'coid'=> $params['coid'],
                'from'=> $stop_date,
                'archive' => $dates['archive']
                 )
                );
                   
    $b0=$b[0];
    $b1=$b[1];

    //details-----------------------------//
    if ( ($b[0] != 0 || $b[1] != 0 )  && $params['summary'] == 0){
        //display details if option is selected and different from 0    
        $pdf->SetFont('helvetica','',8);
        $pdf->Cell($m1,5,"",0,0);
        $pdf->Cell($m2,5,"",0,0);
        $pdf->Cell($c1,5, $r2['aid'] ,0,0);
        $pdf->Cell($c2,5, $r2['aname'] ,0,0);
        $pdf->Cell($c3b,5,"",0,0);   
        $pdf->Cell($c4,5,number_format($b1,2),0,0,'R'); 
        $pdf->Cell($c5,5,number_format($b0,2),0,1,'R');
    }
    //-----------------------------//  
    
    $total_detail+=$b[0];
    $total_detail_l+=$b[1];
    }    
    
    //Total class-----------------------------//
    $pdf->SetFont('helvetica','B',9);
    $pdf->Cell($m1,5,"",0,0);
    $pdf->Cell($c1,5, t('Total') . ' ' . $r['aid'] ,0,0);
    $pdf->Cell($c2,5, $r['aname'] ,0,0);    
    $pdf->Cell($c3,5,"",0,0);   
    $pdf->Cell($c4,5,number_format($total_detail_l,2),0,0,'R'); 
    $pdf->Cell($c5,5,number_format($total_detail,2),0,1,'R');
    //-----------------------------//    
    
    $total_class2+=$total_detail;
    $total_class2_l+=$total_detail_l;
    
    
}

    //Total head-----------------------------//
    $pdf->SetFont('helvetica','B',10);
    $pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,8, t('Total') . ' ' . $rh->aid ,'T',0);
    $pdf->Cell($c2,8, $rh->aname ,'T',0);    
    $pdf->Cell($m1,8,"",'T',0);
    $pdf->Cell($c3,8,"",'T',0);   
    $pdf->Cell($c4,8,number_format($total_class2_l,2),'T',0,'R'); 
    $pdf->Cell($c5,8,number_format($total_class2,2),'T',1,'R');
    $pdf->SetTextColor(88,88,88);
    //-----------------------------// 
    
//other liabilities header account, row head
$total_class7 = 0;
$total_class7_l = 0;

$query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
$query->fields('t', ['aid','aname']);
$query->condition('aid', $chart['other_liabilities'] . '%', 'like');
$query->condition('atype', 'header', '=');
$query->condition('astatus', '1', '=');
$query->condition('coid', $params['coid'], '=');
$query->orderBy('aid', 'ASC');
$data = $query->execute();
$rh = $data->fetchObject();


//head-----------------------------//
$pdf->SetFont('helvetica','B',10);
$pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,5, $rh->aid ,0,0);
    $pdf->Cell($c2,5, $rh->aname ,0,1);
$pdf->SetTextColor(88,88,88);
//-----------------------------//    

$query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
$query->fields('t', ['aid','aname']);
$query->condition('aid', $chart['other_liabilities'] . '%', 'like');
$query->condition('atype', 'class', '=');
$query->condition('astatus', '1', '=');
$query->condition('coid', $params['coid'], '=');
$query->orderBy('aid', 'ASC');
$result = $query->execute();
        
while ($r=$result->fetchAssoc()) {

if ($params['summary'] == 0){
    //class-----------------------------//
    $pdf->SetFont('helvetica','B',8);
        $pdf->Cell($m1,5,"",0,0);
        $pdf->Cell($c1,5, $r['aid'] ,0,0);
        $pdf->Cell($c2,5, $r['aname'] ,0,1);    
    //-----------------------------//  
}
$aid = substr($r['aid'],0,2);
$total_detail=0;
$total_detail_l=0;

    $query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
    $query->fields('t', ['aid','aname']);
    $query->condition('aid', $aid . '%', 'like');
    $query->condition('atype', 'detail', '=');
    $query->condition('coid', $params['coid'], '=');
    $query->orderBy('aid', 'ASC');
    $result2 = $query->execute();

    while ($r2=$result2->fetchAssoc()) {
    
        $b = $journal->opening(
                array( 
                'aid' => $r2['aid'],
                'coid'=> $params['coid'],
                'from'=> $stop_date,
                'archive' => $dates['archive']
                 )
                );
                   
    $b0=$b[0];
    $b1=$b[1];

    //details-----------------------------//
    if ( ($b[0] != 0 || $b[1] != 0 )  && $params['summary'] == 0){
        //display details if option is selected and different from 0    
        $pdf->SetFont('helvetica','',8);
        $pdf->Cell($m1,5,"",0,0);
        $pdf->Cell($m2,5,"",0,0);
        $pdf->Cell($c1,5, $r2['aid'] ,0,0);
        $pdf->Cell($c2,5, $r2['aname'] ,0,0);
        $pdf->Cell($c3b,5,"",0,0);   
        $pdf->Cell($c4,5,number_format($b1,2),0,0,'R'); 
        $pdf->Cell($c5,5,number_format($b0,2),0,1,'R');
    }
    //-----------------------------//  
    
    $total_detail+=$b[0];
    $total_detail_l+=$b[1];
    }    
    
    //Total class-----------------------------//
    $pdf->SetFont('helvetica','B',9);
    $pdf->Cell($m1,5,"",0,0);
    $pdf->Cell($c1,5, t('Total') . ' ' . $r['aid'] ,0,0);
    $pdf->Cell($c2,5, $r['aname'] ,0,0);    
    $pdf->Cell($c3,5,"",0,0);   
    $pdf->Cell($c4,5,number_format($total_detail_l,2),0,0,'R'); 
    $pdf->Cell($c5,5,number_format($total_detail,2),0,1,'R');
    //-----------------------------//    
    
    $total_class7+=$total_detail;
    $total_class7_l+=$total_detail_l;
    
    
}

    //Total head-----------------------------//
    $pdf->SetFont('helvetica','B',10);
    $pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,8, t('Total') . ' ' . $rh->aid ,'T',0);
    $pdf->Cell($c2,8, $rh->aname ,'T',0);    
    $pdf->Cell($m1,8,"",'T',0);
    $pdf->Cell($c3,8,"",'T',0);   
    $pdf->Cell($c4,8,number_format($total_class7_l,2),'T',0,'R'); 
    $pdf->Cell($c5,8,number_format($total_class7,2),'T',1,'R');
    $pdf->SetTextColor(88,88,88);
    //-----------------------------// 
    //
    //Grand Total liabilities-----------------------------//
    $pdf->SetFont('helvetica','B',10);
    $pdf->SetTextColor(0,128,255);
    $pdf->Cell($c1,8, t('Total Liabilities') ,'L,T,B',0);
    $pdf->Cell($c2,8, '' ,'T,B',0);    
    $pdf->Cell($m1,8,"",'T,B',0);
    $pdf->Cell($c3,8,"",'T,B',0);   
    $pdf->Cell($c4,8,number_format($total_class2_l + $total_class7_l,2),'T,B',0,'R'); 
    $pdf->Cell($c5,8,number_format($total_class2 + $total_class7,2),'T,B,R',1,'R');
    $pdf->SetTextColor(88,88,88);
    //-----------------------------// 
    
$total_detail=0;
$total_detail_l=0;


$pdf->Ln(5);

////////////////////// NET ASSETS /////////////////////////////
$net_assets = $total_class0 + $total_class1 - $total_class2 - $total_class7;
$net_assets_l = $total_class0_l + $total_class1_l - $total_class2_l- $total_class7_l;

    //-----------------------------//
    $pdf->SetFont('helvetica','B',11);
    $pdf->SetTextColor(0,128,255);
    $pdf->Cell($c1,8, t('NET ASSETS') ,'L,T,B',0);
    $pdf->Cell($c2,8,"",'T,B',0);    
    $pdf->Cell($m1,8,"",'T,B',0);

    $pdf->Cell($c3,8,"",'T,B',0);   
    $pdf->Cell($c4,8,number_format($net_assets_l,2),'T,B',0,'R'); 
    $pdf->Cell($c5,8,number_format($net_assets,2),'T,B,R',1,'R');
    $pdf->SetTextColor(88,88,88);
    //-----------------------------//

$pdf->Ln(5);
////////////////////// EQUITY /////////////////////////////
//header account
//equity ref. accounts
$equity_min = $chart['equity'] * 10000;
$equity_max = $equity_min + 9999;
$earnings_account = $equity_min + 9001;//default
$reserve_account = $equity_min + 8001;//default

$total_class3 = 0;
$total_class3_l = 0;

$query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
$query->fields('t', ['aid','aname']);
$query->condition('aid', $chart['equity'] . '%', 'like');
$query->condition('atype', 'header', '=');
$query->condition('astatus', '1', '=');
$query->condition('coid', $params['coid'], '=');
$query->orderBy('aid', 'ASC');
$data = $query->execute();
$rh = $data->fetchObject();

//head-----------------------------//
$pdf->SetFont('helvetica','B',10);
$pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,5, $rh->aid ,0,0);
    $pdf->Cell($c2,5, $rh->aname ,0,1);
$pdf->SetTextColor(88,88,88);
//-----------------------------//    

$query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
$query->fields('t', ['aid','aname']);
$query->condition('aid', $chart['equity'] . '%', 'like');
$query->condition('atype', 'class', '=');
$query->condition('astatus', '1', '=');
$query->condition('coid', $params['coid'], '=');
$query->orderBy('aid', 'ASC');
$result = $query->execute();

while ($r=$result->fetchAssoc()) {

if ($params['summary'] == 0){
    //class-----------------------------//
    $pdf->SetFont('helvetica','B',8);
        $pdf->Cell($m1,5,"",0,0);
        $pdf->Cell($c1,5, $r['aid'] ,0,0);
        $pdf->Cell($c2,5, $r['aname'] ,0,1);    
    //-----------------------------//  
}
$aid = substr($r['aid'],0,2);
$total_detail=0;
$total_detail_l=0;

    $query = Database::getConnection('external_db', 'external_db')
                    ->select($table_accounts, 't');
    $query->fields('t', ['aid','aname']);
    $query->condition('aid', $aid . '%', 'like');
    $query->condition('atype', 'detail', '=');
    $query->condition('coid', $params['coid'], '=');
    $query->orderBy('aid', 'ASC');
    $result2 = $query->execute();

    while ($r2=$result2->fetchAssoc()) {
    
    if ($r2['aid']== $earnings_account) {
    //calculate current year earnings
    $b = $journal->current_earning($params['coid'],$from,$to);
                //add other entries on the account from journal transactions
            $dt = $journal->transactions(
                array( 
                'aid' => $earnings_account,
                'type' => 'debit',
                'coid' => $params['coid'],
                'from' => $from,
                'to' => $to,
                'archive' => $dates['archive']
                 ) 
                );
    
            $ct = $journal->transactions(
                array( 
                'aid' => $earnings_account,
                'type' => 'credit',
                'coid' => $params['coid'],
                'from'=> $from,
                'to'=> $to,
                'archive' => $dates['archive']
                 )
                );    
            $b[0] = $b[0] + $ct[0]-$dt[0];
            $b[1] = $b[1] + $ct[1]-$dt[1];
    } else {
    //look up for balance
        $b = $journal->opening(
                array( 
                'aid'=> $r2['aid'],
                'coid'=> $params['coid'],
                'from'=> $stop_date,
                'archive' => $dates['archive']
                 )
                );    
    }
    $b0=$b[0];
    $b1=$b[1];   
    //details-----------------------------//
    if (( $b[0] != 0 || $b[1] != 0) && $params['summary'] == 0){
        //display details if option is selected and different from 0
        $pdf->SetFont('helvetica','',8);
        $pdf->Cell($m1,5,"",0,0);
        $pdf->Cell($m2,5,"",0,0);
        $pdf->Cell($c1,5, $r2['aid'] ,0,0);
        $pdf->Cell($c2,5, $r2['aname'] ,0,0);
        $pdf->Cell($c3b,5,"",0,0);   
        $pdf->Cell($c4,5,number_format($b1,2),0,0,'R'); 
        $pdf->Cell($c5,5,number_format($b0,2),0,1,'R');

    }
    //-----------------------------//  
  
    $total_detail+=$b0;
    $total_detail_l+=$b1;
    }    
    
    //total class-----------------------------//
    $pdf->SetFont('helvetica','B',9);
    $pdf->Cell($m1,5,"",0,0);
    $pdf->Cell($c1,5,t('Total') . ' ' .  $r['aid'] ,0,0);
    $pdf->Cell($c2,5,$r['aname'],0,0);    
    $pdf->Cell($c3,5,"",0,0);   
    $pdf->Cell($c4,5,number_format($total_detail_l,2),0,0,'R'); 
    $pdf->Cell($c5,5,number_format($total_detail,2),0,1,'R');
    //-----------------------------//    
    
    $total_class3+=$total_detail;
    $total_class3_l+=$total_detail_l;
    
    
}
$pdf->Ln(3);
    //total head-----------------------------//
    $pdf->SetFont('helvetica','B',11);
    $pdf->SetTextColor(0,128,255);
    $pdf->Cell($c1,8,t('Total') . ' ' .  $rh->aid ,'L,T,B',0);
    $pdf->Cell($c2,8,$rh->aname,'T,B',0);    
    $pdf->Cell($m1,8,"",'T,B',0);
    $pdf->Cell($c3,8,"",'T,B',0);   
    $pdf->Cell($c4,8,number_format($total_class3_l,2),'T,B',0,'R'); 
    $pdf->Cell($c5,8,number_format($total_class3,2),'T,B,R',1,'R');
    $pdf->SetTextColor(88,88,88);
    //-----------------------------// 
    

$pdf->Ln(8);
    $pdf->SetFont('helvetica','',7);
    $pdf->SetTextColor(0,0,0);
    $pdf->Cell(20,3,"printed by",0,0);
    $pdf->Cell(40,3, $userName ,0,0);
    $pdf->Cell(40,3,"on " . date('Y-m-d') ,0,0);