<?php

use Drupal\Core\Database\Database;
use Drupal\ek_admin\Access\AccessCheck;
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'];



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, 25, 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->telephone,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('Profit and Loss') . ' ' . $company->name);
$pdf->SetSubject(t('Profit and Loss'));
$pdf->SetKeywords(t('Profit Loss'));

// 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
$m0 = 1; $m1=5; $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->SetFont('helvetica','',16);
    $pdf->Cell(60,5, t("Profit & loss") ,'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);
$pdf->SetLineStyle(['width' => 0.1, 'cap' => 'square', 'join' => 'miter', 'dash' => 0, 'color' => [128, 128, 128]]);
//header
$pdf->SetFont('helvetica','',10);
    
    $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(1);
////////////////////// REVENUE /////////////////////////////
//header account
$q = "SELECT aid,aname FROM {$table_accounts} "
        . "WHERE (aid like :aid1 or aid like :aid2) "
        . "AND atype=:type AND astatus=:status AND coid=:coid order by aid";
$a = array( ':aid1' => $chart['income'] . '%',
            ':aid2' => $chart['other_income'] . '%',
            ':type' => 'header',
            ':status' => 1,
            ':coid' => $params['coid']);
$rh = Database::getConnection('external_db', 'external_db')
        ->query($q,$a)->fetchObject();


//-----------------------------//
$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);
//-----------------------------//    


$q = "SELECT aid,aname FROM {$table_accounts} "
        . "WHERE (aid like :aid1 or aid like :aid2) "
        . "AND atype=:type AND astatus=:status AND coid=:coid order by aid";
$a = array(
            ':aid1' => $chart['income'] . '%',
            ':aid2' => $chart['other_income'] . '%',
            ':type' => 'class',
            ':status' => 1, 
            ':coid' => $params['coid']
        );
$result = Database::getConnection('external_db', 'external_db')
        ->query($q,$a);


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

if($params['summary'] == 0){
    //-----------------------------//
    $pdf->SetFont('helvetica','',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;
$q = "SELECT aid,aname FROM {$table_accounts} "
        . "WHERE aid like :aid AND atype=:type AND coid=:coid order by aid";
$a = array(':aid'=> $aid . '%' ,':type' => 'detail', ':coid' => $params['coid']);
$result2 = Database::getConnection('external_db', 'external_db')
        ->query($q,$a);

    while ($r2=$result2->fetchAssoc()) {
    
        $d = $journal->transactions(
                array( 
                'aid' => $r2['aid'],
                'type' => 'debit',
                'coid' => $params['coid'] ,
                'from' => $from ,
                'to' => $to,
                'archive' => $dates['archive']
                 )
                );
    
        $c = $journal->transactions(
                array( 
                'aid' => $r2['aid'],
                'type' => 'credit',
                'coid' => $params['coid'],
                'from' => $from,
                'to' => $to,
                'archive' => $dates['archive']
                 )
                );    
    $b = $c[0]-$d[0];
    $b_exc = $c[1]-$d[1];
    if ($params['summary'] == 0){
        //detail-----------------------------//
        $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($b_exc,2),0,0,'R'); 
        $pdf->Cell($c5,5,number_format($b,2),0,1,'R');
        //-----------------------------//  
    }
    $total_detail+=$b_exc;
    $total_detail_l+=$b;
    }    
    
    //Sub total-----------------------------//
    $pdf->SetFont('helvetica','',9);
    $pdf->Cell($m0,5,"",'T',0);
    $pdf->Cell($c1,5, (STRING) t("Total") . ' ' .  $r->aid ,'T',0);
    $pdf->Cell($c2,5, $r->aname ,'T',0);    
    $pdf->Cell($c3,5,"",'T',0);   
    $pdf->Cell($c4,5,number_format($total_detail,2),'T',0,'R'); 
    $pdf->Cell($c5,5,number_format($total_detail_l,2),'T',1,'R');
    //-----------------------------//    
    
    $total_class4+=$total_detail;
    $total_class4_l+=$total_detail_l;
    
    
}

    //Head total-----------------------------//
    $pdf->SetFont('helvetica','',10);
    $pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,8,(STRING) t("TOTAL") . ' ' . $rh->aid ,'T,B',0);
    $pdf->Cell($c2,8, $rh->aname,'T,B',0);    
    $pdf->Cell($m0,8, "" ,'T,B',0);
    $pdf->Cell($c3,8, "" ,'T,B',0);   
    $pdf->Cell($c4,8,number_format($total_class4,2),'T,B',0,'R'); 
    $pdf->Cell($c5,8,number_format($total_class4_l,2),'T,B',1,'R');
    $pdf->SetTextColor(88,88,88);
    //-----------------------------// 

$total_detail=0;
$total_detail_l=0;
$total_class=0;
$total_class_l=0;

$pdf->Ln(3);

////////////////////// COS /////////////////////////////
//header account
$q = "SELECT aid,aname FROM {$table_accounts} "
        . "WHERE aid like :aid AND atype=:type AND astatus=:status AND coid=:coid order by aid";
$a = array(
            ':aid' => $chart['cos'] . '%', 
            ':type' => 'header',
            ':status' => 1, 
            ':coid' => $params['coid']);
$rh = Database::getConnection('external_db', 'external_db')
        ->query($q,$a)
        ->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);
//-----------------------------//  

$q = "SELECT aid,aname FROM {$table_accounts} "
        . "WHERE aid like :aid AND atype=:type AND astatus=:status AND coid=:coid order by aid";
$a = array(
            ':aid' => $chart['cos'] . '%', 
            ':type' => 'class', 
            ':status' => 1, 
            ':coid' => $params['coid']);

$result = Database::getConnection('external_db', 'external_db')
        ->query($q,$a);


while ($r=$result->fetchAssoc()) {
 if ($params['summary'] == 0){
    //-----------------------------//
    $pdf->SetFont('helvetica','',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;
$q = "SELECT aid,aname FROM {$table_accounts} "
        . "WHERE aid like :aid AND atype=:type AND coid=:coid order by aid";
$a = array(':aid' => $aid . '%', ':type' => 'detail', ':coid' => $params['coid']);
$result2 = Database::getConnection('external_db', 'external_db')
        ->query($q,$a);

    while ($r2 = $result2->fetchAssoc()) {
    
        $d = $journal->transactions(
                array( 
                'aid' => $r2['aid'],
                'type'=> 'debit',
                'coid' => $params['coid'] ,
                'from' => $from ,
                'to' => $to,
                'archive' => $dates['archive']
                 )
                );

        $c = $journal->transactions(
                array( 
                'aid' => $r2['aid'],
                'type' => 'credit',
                'coid' => $params['coid'],
                'from' => $from,
                'to' => $to,
                'archive' => $dates['archive']
                 )
                );    

    $b = $c[0]-$d[0];
    $b_exc = $c[1]-$d[1];
    if ($params['summary'] == 0){
    //Detail-----------------------------//
        $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($b_exc,2),0,0,'R'); 
        $pdf->Cell($c5,5,number_format($b,2),0,1,'R');
    //-----------------------------//  
    }
    
    $total_detail+=$b_exc;
    $total_detail_l+=$b;
    
    
    }    
    
    //Sub total-----------------------------//
    $pdf->SetFont('helvetica','',9);
    $pdf->Cell($m0,5,"",'T',0);
    $pdf->Cell($c1,5, (STRING) t("Total") . ' ' .  $r['aid'] ,'T',0);
    $pdf->Cell($c2,5, $r['aname'] ,'T',0);    
    $pdf->Cell($c3,5, "" ,'T',0);   
    $pdf->Cell($c4,5,number_format($total_detail,2),'T',0,'R'); 
    $pdf->Cell($c5,5,number_format($total_detail_l,2),'T',1,'R');
    //-----------------------------//    
    
    $total_class5+=$total_detail;
    $total_class5_l+=$total_detail_l;
    
    
}

    //Head total-----------------------------//
    $pdf->SetFont('helvetica','',10);
    $pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,8,(STRING) t("TOTAL") . ' ' . $rh->aid ,'T,B',0);
    $pdf->Cell($c2,8, $rh->aname ,'T,B',0);    
    $pdf->Cell($m0,8,"",'T,B',0);
    $pdf->Cell($c3,8, "" ,'T,B',0);   
    $pdf->Cell($c4,8,number_format($total_class5,2),'T,B',0,'R'); 
    $pdf->Cell($c5,8,number_format($total_class5_l,2),'T,B',1,'R');
    $pdf->SetTextColor(88,88,88);
    //-----------------------------// 
    

$total_detail=0;
$total_detail_l=0;
$total_class=0;
$total_class_l=0;

$pdf->Ln(2);

////////////////////// CHARGES /////////////////////////////
//header account
$q = "SELECT aid,aname FROM {$table_accounts} "
        . "WHERE (aid like :aid1 or aid like :aid2) "
        . "AND atype=:type AND astatus=:status AND coid=:coid order by aid";
$a = array(
            ':aid1' => $chart['expenses'] . '%',
            ':aid2' => $chart['other_expenses'] . '%',
            ':type' => 'header', 
            ':status' => 1,
            ':coid' => $params['coid']);

$rh =  Database::getConnection('external_db', 'external_db')
        ->query($q,$a)->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);
//-----------------------------//    


$q = "SELECT aid,aname FROM {$table_accounts} "
        . "WHERE (aid like :aid1 or aid like :aid2) "
        . "AND atype=:type AND astatus=:status AND coid=:coid order by aid";
$a = array(
            ':aid1' => $chart['expenses'] . '%',
            ':aid2' => $chart['other_expenses'] . '%', 
            ':type' => 'class', 
            ':status' => 1, 
            ':coid' => $params['coid']);

$result = Database::getConnection('external_db', 'external_db')
        ->query($q,$a);


while ($r = $result->fetchAssoc()) {
 if ($params['summary'] == 0){
    //-----------------------------//
    $pdf->SetFont('helvetica','',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;
$q = "SELECT aid,aname FROM {$table_accounts} "
        . "WHERE aid like :aid AND atype=:type AND coid=:coid order by aid";
$a = array(':aid'=> $aid . '%',':type'=>'detail',':coid'=>$params['coid']);

$result2 = Database::getConnection('external_db', 'external_db')
        ->query($q,$a);

    while ($r2=$result2->fetchAssoc()) {
    
        $d = $journal->transactions(
                array( 
                'aid' => $r2['aid'],
                'type' => 'debit',
                'coid' => $params['coid'] ,
                'from' => $from ,
                'to' => $to,
                'archive' => $dates['archive']
                 )
                );
    
        $c = $journal->transactions(
                array( 
                'aid' => $r2['aid'],
                'type' => 'credit',
                'coid' => $params['coid'],
                'from' => $from,
                'to'=> $to,
                'archive' => $dates['archive']
                 )
                );    
    $b = $c[0]-$d[0];
    $b_exc = $c[1]-$d[1];
    if ($params['summary'] == 0){
        //Detail-----------------------------//
        $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($b_exc,2),0,0,'R'); 
        $pdf->Cell($c5,5,number_format($b,2),0,1,'R');
        //-----------------------------//  
    }

    $total_detail+=$b_exc;
    $total_detail_l+=$b;
    }    
    
    //Sub total-----------------------------//
    $pdf->SetFont('helvetica','',9);
    $pdf->Cell($m0,5,"",'T',0);
    $pdf->Cell($c1,5, (STRING)t("Total") . ' ' . $r['aid'] ,'T',0);
    $pdf->Cell($c2,5, $r['aname'] ,'T',0);    

    $pdf->Cell($c3,5, "" ,'T',0);   
    $pdf->Cell($c4,5,number_format($total_detail,2),'T',0,'R'); 
    $pdf->Cell($c5,5,number_format($total_detail_l,2),'T',1,'R');
    //-----------------------------//    
   
    $total_class6+=$total_detail;
    $total_class6_l+=$total_detail_l;
    
    
}

    //Head total-----------------------------//
    $pdf->SetFont('helvetica','',10);
    $pdf->SetTextColor(0,61,121);
    $pdf->Cell($c1,8,(STRING)t("TOTAL") . ' ' . $rh->aid ,'T,B',0);
    $pdf->Cell($c2,8, $rh->aname ,'T,B',0);    
    $pdf->Cell($m0,8, "" ,'T,B',0);

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

$result = $total_class4+$total_class5+$total_class6;
$result_l = $total_class4_l+$total_class5_l+$total_class6_l;

if ($result < 0) {
    $result="(".number_format(abs($result),2).")";
    $pdf->SetTextColor(255,34,34);
} else {
    $result=number_format($result,2);
}
if ($result_l < 0) {
    $result_l="(".number_format(abs($result_l),2).")";
    $pdf->SetTextColor(255,34,34);
} else {
    $result_l=number_format($result_l,2);
}
$pdf->Ln(5);
    //-----------------------------//
    $pdf->SetFont('helvetica','B',10);
    $pdf->Cell($c1,8,(STRING) t("Profit (loss)") ,'T,B',0);
    $pdf->Cell($c2,8,"",'T,B',0);    
    $pdf->Cell($m0,8,"",'T,B',0);
    $pdf->Cell($c3,8,"",'T,B',0);   
    $pdf->Cell($c4,8,$result,'T,B',0,'R'); 
    $pdf->Cell($c5,8,$result_l,'T,B',1,'R');
    //-----------------------------//  
 
  
 
 
 $pdf->Ln(5);
    $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);