<?php
include("dbconnect.php");
require("pdf_gen/fpdf.php");
require("word2.php");
class PDF extends FPDF {
function Footer(){
include("dbconnect.php");
require("fpdf1.php");
		$this->SetFont('Times', '', 9);  
		$this->SetTextColor(0,33,56);
		$this->Cell(0,30,'Licensed to '.$vjhvkbnl, 0, 0, 'C');

    }
function RotatedText($x, $y, $txt, $angle)
{
	//Text rotated around its origin
	$this->Rotate($angle,$x,$y);
	$this->Text($x,$y,$txt);
	$this->Rotate(0);
}
function Header() {


}

}

$pdf = new PDF();
//cats=2&min=&max=2500&class=3&stream=1
$classid=@$_GET['class'];
$streamsid=@$_GET['stream'];
$maxvalue=@$_GET['max'];
$category=@$_GET['cats'];
$endyear=@$_GET['endyear'];

$sql1 = 'SELECT * FROM school_info';

$result1 = mysqli_query($con,$sql1) or die( "Could not execute sql: $sql");
$row1 = mysqli_fetch_array($result1);
$datetoday=date("Y-m-d");
$data1= $row1['school_name'];
$data2='Address:'.$row1['address'].' '.$row1['city'];
$data3='Mobile No: '.$row1['mobile'];
$data4='Email:'.$row1['email'];
$data7='--------------------------------------------------------Produced on date: '.$datetoday.'-----------------------------------------------';
$type=$row1['type'];
$sqlquery="";
if(@$_GET['bulk']=='1'){
	$admnnn=@$_GET['admn'];
	$sqlquery="SELECT *,(SELECT academicyear FROM academicyears WHERE academicyears.id=feesdue.academicyear) AS academicy,(SELECT form FROM forms WHERE forms.id=feesdue.form_id) as form,(SELECT stream FROM streams WHERE streams.id=feesdue.stream_id) as stream,(SELECT CONCAT(fname,' ',sname,' ',tname) FROM students WHERE students.adm_no=feesdue.adm_no) AS studentname FROM `feesdue` where adm_no='$admnnn' AND adm_no IN(SELECT adm_no FROM students WHERE students.adm_no=feesdue.adm_no AND `status`='1')";
}
else{
if($classid=='all' && $streamsid=='all'){

	$sqlquery="SELECT *,(SELECT academicyear FROM academicyears WHERE academicyears.id=feesdue.academicyear) AS academicy,(SELECT form FROM forms WHERE forms.id=feesdue.form_id) as form,(SELECT stream FROM streams WHERE streams.id=feesdue.stream_id) as stream,(SELECT CONCAT(fname,' ',sname,' ',tname) FROM students WHERE students.adm_no=feesdue.adm_no) AS studentname FROM `feesdue` where adm_no IN(SELECT adm_no FROM students WHERE students.adm_no=feesdue.adm_no AND `status`='1') order by adm_no ASC";

}
elseif($classid=='all' && $streamsid!='all'){
	$sqlquery="SELECT *,(SELECT academicyear FROM academicyears WHERE academicyears.id=feesdue.academicyear) AS academicy,(SELECT form FROM forms WHERE forms.id=feesdue.form_id) as form,(SELECT stream FROM streams WHERE streams.id=feesdue.stream_id) as stream,(SELECT CONCAT(fname,' ',sname,' ',tname) FROM students WHERE students.adm_no=feesdue.adm_no) AS studentname FROM `feesdue` WHERE stream_id='$streamsid' AND adm_no IN(SELECT adm_no FROM students WHERE students.adm_no=feesdue.adm_no AND `status`='1') order by adm_no ASC";
}
elseif($classid!='all' && $streamsid=='all'){
	$sqlquery="SELECT *,(SELECT academicyear FROM academicyears WHERE academicyears.id=feesdue.academicyear) AS academicy,(SELECT form FROM forms WHERE forms.id=feesdue.form_id) as form,(SELECT stream FROM streams WHERE streams.id=feesdue.stream_id) as stream,(SELECT CONCAT(fname,' ',sname,' ',tname) FROM students WHERE students.adm_no=feesdue.adm_no) AS studentname FROM `feesdue` WHERE form_id='$classid' AND adm_no IN(SELECT adm_no FROM students WHERE students.adm_no=feesdue.adm_no AND `status`='1') order by adm_no ASC";
}
else{

	$sqlquery="SELECT *,(SELECT academicyear FROM academicyears WHERE academicyears.id=feesdue.academicyear) AS academicy,(SELECT form FROM forms WHERE forms.id=feesdue.form_id) as form,(SELECT stream FROM streams WHERE streams.id=feesdue.stream_id) as stream,(SELECT CONCAT(fname,' ',sname,' ',tname) FROM students WHERE students.adm_no=feesdue.adm_no) AS studentname FROM `feesdue` WHERE form_id='$classid' and stream_id='$streamsid' AND adm_no IN(SELECT adm_no FROM students WHERE students.adm_no=feesdue.adm_no AND `status`='1') order by adm_no ASC";
	
}
}
//echo $sqlquery;
$ssql=mysqli_query($con,$sqlquery); 
   $rrr=0; 
while($ssrowssql=mysqli_fetch_array($ssql)){
      $adm_no=$ssrowssql['adm_no'];
      $studentname=$ssrowssql['studentname'];
      $form=$ssrowssql['form'];
      $stream=$ssrowssql['stream'];
      $academicyear=$ssrowssql['academicy'];
      $academicperiod='';
      $acyear=$ssrowssql['academicyear'];
      $acperiod='';
	  $fff4000=mysqli_query($con,"SELECT * FROM students WHERE adm_no='$adm_no'");
	  $rows4000=mysqli_fetch_array($fff4000);
	  $ss_ttype=$rows4000['s_type'];
	  $entryyear=$rows4000['year_ofentry'];
	  $entryclass=$rows4000['form_id'];
	  
	  $fff5000=mysqli_query($con,"SELECT * FROM activeyear");
	  $rows5000=mysqli_fetch_array($fff5000);
	  $activeyear=$rows5000['activeyear'];
	  $nextclasses=($activeyear-$entryyear)+$entryclass+1;
	  $fff6000=mysqli_query($con,"SELECT * FROM school_info");
	  $rows6000=mysqli_fetch_array($fff6000);
	  $maxclasses=$rows6000['maxclass'];
	  
		$qqq45=mysqli_query($con,"SELECT *,(SELECT periods FROM academicyearsperiods WHERE academicyearsperiods.id=`activeperiod`.academicperiod) AS periods FROM `activeperiod` WHERE academicyear='$acyear'");
		$val42=mysqli_fetch_array($qqq45);
		$academicperiod=$val42['periods'];
		$acperiod=$val42['academicperiod'];
		  
        $orderss=0;
        $qqqwwgyy=mysqli_query($con,"SELECT * FROM `academicyearsperiods` WHERE academicyear='$acyear' AND id='$acperiod'");
        $vals1yy=mysqli_fetch_array($qqqwwgyy);
          $orderss=$vals1yy['orders'];
      // studentsfee
      $fd=1;
      $fp=2;
      $feedue=0;
      $paid=0;
      $balance=0;
	  $feedueyear=0;
        $ssqlfee=mysqli_query($con,"SELECT * FROM `studentsfee` WHERE transaction_type='$fd' AND adm_no='$adm_no' AND academicyear='$acyear' AND academicperiod IN (SELECT id FROM `academicyearsperiods` WHERE academicyear='$acyear' AND orders<='$orderss')"); 
		while($sqfee=mysqli_fetch_array($ssqlfee)){
		$feedue=$feedue+$sqfee['amount'];	
		}
		$ssqlfeeyear=mysqli_query($con,"SELECT * FROM `studentsfee` WHERE transaction_type='$fd' AND adm_no='$adm_no' AND academicyear='$acyear'"); 
		while($sqfeeyear=mysqli_fetch_array($ssqlfeeyear)){
		$feedueyear=$feedueyear+$sqfeeyear['amount'];	
		}
        /*$ssqlfee5=mysqli_query($con,"SELECT * FROM `studentsfee` WHERE transaction_type='$fp' AND adm_no='$adm_no' AND academicyear='$acyear' AND academicperiod IN (SELECT id FROM `academicyearsperiods` WHERE academicyear=? AND orders<=?)",[$fp,$adm_no,$acyear,$acyear,$orderss]);
		while($sqfee1=mysqli_fetch_array($ssqlfee5)){
		$paid=$paid+$sqfee1['amount'];	
		}
		*/
		$ssqlfee5=mysqli_query($con,"SELECT * FROM `studentsfee` WHERE transaction_type='$fp' AND adm_no='$adm_no' AND academicyear='$acyear'");
		while($sqfee1=mysqli_fetch_array($ssqlfee5)){
		$paid=$paid+$sqfee1['amount'];	
		}
        $opaid=0;
        $ssqlfeeo=mysqli_query($con,"SELECT * FROM `overpayments` WHERE adm_no='$adm_no' AND academicyear='$acyear'"); 
        while($sqfeeo=mysqli_fetch_array($ssqlfeeo)){
          $opaid=$opaid+$sqfeeo['amount'];
        }
        if($feedue==null || $feedue==''){
          $feedue=0;
        }
		if($feedueyear==null || $feedueyear==''){
          $feedueyear=0;
        }
        if($paid==null || $paid==''){
          $paid=0;
        }
        if($opaid==null || $opaid==''){
          $opaid=0;
        }
        $paid1=$paid+$opaid;
        $tbalance=$feedue-$paid1;
		if($tbalance<0){
			$tbalance=0;
		}
		$ytbalance=$feedueyear-$paid1;
		$newyeeearsfees=0;
		$newtermsfees=0;
		if($nextclasses<=$maxclasses){
			
		$qqqwwgyy10000=mysqli_query($con,"SELECT * FROM `academicyears` WHERE id>'$acyear' order by id ASC limit 1");
		$val1000=mysqli_fetch_array($qqqwwgyy10000);
		$nextyear=$val1000['id'];
		$ssq2000=mysqli_query($con,"SELECT * FROM academicyearsperiods WHERE academicyear='$nextyear' order by orders ASC LIMIT 1");
		$val2000=mysqli_fetch_array($ssq2000);
		$nexttermm=$val2000['id'];
		
		$ssq8000=mysqli_query($con,"SELECT * FROM feestructure WHERE academicyear='$nextyear' AND formclass='$nextclasses' AND academicperiod='$nexttermm' AND s_type='$ss_ttype'");
		$val8000=mysqli_fetch_array($ssq8000);
		$newtermsfees=$val8000['amount'];
		
		$ssq9000=mysqli_query($con,"SELECT SUM(amount) AS amtt FROM feestructure WHERE academicyear='$nextyear' AND formclass='$nextclasses' AND s_type='$ss_ttype'");
		$val9000=mysqli_fetch_array($ssq9000);
		$newyeeearsfees=$val9000['amtt'];
		
		}
		
		
		if($category==1){
		
	  if($rrr%4==0){
		  $pdf->AddPage();
	  }
		$dat1='Dear parent, your child  '.$studentname.' ADMNO. '.$adm_no.'  Class  '.$type.' '.$form.'  Stream  '.$stream.' has an outstanding fee  ';
	  $dat5='current Term balance of  Kshs.'.number_format($tbalance,2).'.This year arrears of Kshs.'.number_format($ytbalance,2);
	  $nextarrears=$ytbalance+$newyeeearsfees;
	  $dat255='Next term fee is Kshs.'.number_format($newtermsfees,2).' and next years fees is Kshs.'.number_format($newyeeearsfees,2).'Net Arrear as at Next Year '.number_format($nextarrears,2);
	  $dat2='Kindly make arrangements to clear these arrears to avoid learning interruptions.';
	  $dat3='Thank you.';
	  $dat4='--------------------------------------------------------------END-----------------------------------------------------------------------------';
		$pdf->SetFont('Times','',10);

        $w = $pdf->GetStringWidth($pdf->title)+150;

        $pdf->SetTextColor(0);
		
       $pdf->Cell($w,9,$pdf->title,0,0,'C');
	    $pdf->Ln(10);
       $pdf->Cell(170,0,$data1,0 ,0 ,'C');
	    $pdf->Ln(5);
	   $pdf->Cell(170,0,$data2 ,0 ,0 ,'C');
	    $pdf->Ln(5);
	   $pdf->Cell(170,0,$data3.'  '.$data4 ,0 ,0 ,'C');
	   $pdf->Ln(7);
		 $pdf->Cell(170,0,$data7 ,0 ,0 ,'C');
        $pdf->Ln(5);
		$pdf->Cell(170,0,$dat1 ,0 ,0 ,'L');
       $pdf->Ln(5);
	   $pdf->Cell(170,0,$dat5 ,0 ,0 ,'L');
       $pdf->Ln(5);
	   if($endyear==1){
		$pdf->Cell(170,0,$dat255 ,0 ,0 ,'L');
       $pdf->Ln(5);
	   }
	    $pdf->Cell(170,0,$dat2 ,0 ,0 ,'L');
       $pdf->Ln(5);
	   $pdf->Cell(170,0,$dat3 ,0 ,0 ,'L');
       $pdf->Ln(5);
	    $pdf->Cell(170,0,'' ,0 ,0 ,'C');
       $pdf->Ln(5);
	   $pdf->Cell(170,0,$dat4 ,0 ,0 ,'C');
       $pdf->Ln(5);
		$rrr++;
		}
		elseif($category==2){
		if($tbalance>=$maxvalue){
		if($rrr%5==0){
		  $pdf->AddPage();
		}
		$dat1='Dear parent, your child  '.$studentname.' ADMNO. '.$adm_no.'  Class  '.$type.' '.$form.'  Stream  '.$stream.' has an outstanding fee  ';
	  $dat5='current Term balance of  Kshs.'.number_format($tbalance,2).'.This year arrears of Kshs.'.number_format($ytbalance,2);
	  $nextarrears=$ytbalance+$newyeeearsfees;
	  $dat255='Next term fee is Kshs.'.number_format($newtermsfees,2).' and next years fees is Kshs.'.number_format($newyeeearsfees,2).'Net Arrear as at Next Year '.number_format($nextarrears,2);;
	  $dat2='Kindly make arrangements to clear these arrears to avoid learning interruptions.';
	  $dat3='Thank you.';
	  $dat4='--------------------------------------------------------------END-----------------------------------------------------------------------------';
		$pdf->SetFont('Times','',10);

        $w = $pdf->GetStringWidth($pdf->title)+150;

        $pdf->SetTextColor(0);
		
       $pdf->Cell($w,9,$pdf->title,0,0,'C');
	    $pdf->Ln(10);
       $pdf->Cell(170,0,$data1,0 ,0 ,'C');
	    $pdf->Ln(5);
	   $pdf->Cell(170,0,$data2 ,0 ,0 ,'C');
	    $pdf->Ln(5);
	   $pdf->Cell(170,0,$data3.'  '.$data4 ,0 ,0 ,'C');
	   $pdf->Ln(7);
		 $pdf->Cell(170,0,$data7 ,0 ,0 ,'C');
        $pdf->Ln(5);
		$pdf->Cell(170,0,$dat1 ,0 ,0 ,'L');
       $pdf->Ln(5);
	   $pdf->Cell(170,0,$dat5 ,0 ,0 ,'L');
       $pdf->Ln(5);
	    
	   if($endyear==1){
		$pdf->Cell(170,0,$dat255 ,0 ,0 ,'L');
       $pdf->Ln(5);
	   }
	   $pdf->Cell(170,0,$dat2 ,0 ,0 ,'L');
       $pdf->Ln(5);
	   $pdf->Cell(170,0,$dat3 ,0 ,0 ,'L');
       $pdf->Ln(5);
	    $pdf->Cell(170,0,'' ,0 ,0 ,'C');
       $pdf->Ln(5);
	   $pdf->Cell(170,0,$dat4 ,0 ,0 ,'C');
       $pdf->Ln(5);
		$rrr++;
			}
		}
    }


	 

$pdf->Output();

?>