<?php

ob_end_clean();
ini_set("memory_limit", "200M");
session_start();

if (!$isInclude) {
    require("includes/check_auth_user.php");
    require("includes/dbconnect.php");
    include ('includes/function.php');
}


$zdb = $_SESSION['zdbyear_' . $dbname];
$admin = $_SESSION['admin_' . $dbname];

$zyear = $zdb ;

$zroot = $_SESSION['zdir'];
$file = 'osbill.php';
$trandb = 'actran';
$myear = $_SESSION['tit_' . $dbname];


$getcomp = mysqli_query($conn, "SELECT * FROM $zroot.`company`") or die(mysqli_error($conn));
$comp = mysqli_fetch_array($getcomp);
$compname = $comp['compname'];

mysqli_select_db($conn, $zdb);

$acond = '';
$scond = '';
$custcond = '';
$medcond = '';
$examcond = '';
$examcond1 = '';

$maccode  = $_GET['accode'];
$mfrdate = $_GET['mfrdate'];
$mtodate = $_GET['mtodate'];

    $mfile = 'invmain';
    $mtype = 'S';
    $mdate = 'vouchdt';
    $fintot = 'billamt';
    $grid = 'osbill.php?para=R';
    $formname1 = 'Outstanding Receivable';

    $formname = "$formname1 - Detail";
    $dtype = 2;


    require 'library/fpdf/fpdf.php';

    class pdf extends fpdf {

        public function Footer() {
            $this->SetY(-15);
            $this->SetFont('Arial', 'I', 8);
            $this->Cell(0, 10, 'Page ' . $this->PageNo() . '/{nb}', 0, 0, 'C');
        }

    }

    $pdf = new pdf('L', 'mm', 'A4');
    $pdf->AliasNbPages();

    $pdf->AddPage();

    $pdf->SetFont('Arial', 'B', 16);
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFillColor(255, 255, 255);
    $pdf->Cell(237, 7, 'MAHARASHTRA BHARATEEYA SHIKSHAN MANDAL', 0, 0, '', 1);
    $pdf->Ln();
    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(40, 7, 'D-43/189 ASHISH CO-OP SOCIETY,CHARKOP,SECTOR 1,KANDIWALI(W),MUMBAI- 400067', 0, 0, '', 1);
    $pdf->Ln();
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(180, 7, 'Outstanding Receivable - Detail', 0, 0, '', 1);
    $pdf->Cell(97, 7, 'Report Date - 03-11-2020', 0, 0, 'R', 1);
    $pdf->Ln();
    $pdf->SetFont('Arial', '', 12);
    $pdf->Cell(40, 7, 'To Date - 03-11-2020', 0, 0, '', 1);
    $pdf->Ln();
    $pdf->Ln(5);

    $pdf->SetFont('Arial', 'I', 10);
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFillColor(96, 130, 191);
    $pdf->Cell(237, 7, 'Name', 1, 0, '', 1);
    $pdf->Cell(40, 7, 'Amount', 1, 0, '', 1);
    $pdf->SetFont('Arial', '', 1, 0);

    $pdf->SetTextColor(0, 0, 0);
    $pdf->Ln();

    $mbalamt1 = $mtr = 0;
    $grbill = $grrecd = $grbal = 0;


        $sub1 = mysqli_query($conn, "SELECT mobile, name, email,custcode,tel FROM accmast WHERE `accode` = '$maccode' ") or die(mysqli_error($conn));
        $fetac = mysqli_fetch_array($sub1);
    
        $mcustcode = $fetac['custcode'];
        $macname = $fetac['name'];
        $tel = $fetac['tel'];
        $mobile = $fetac['mobile'];
        $email = $fetac['email'];

        $sub1 = mysqli_query($conn, "SELECT  SUM($fintot) AS amt1 FROM $mfile WHERE `accode` = '$maccode' 
                             AND `vouchdt` >= '$mfrdate' AND `vouchdt` <= '$mtodate' $examcond1 ") or die(mysqli_error($conn));

        $sub2 = mysqli_query($conn, "SELECT  SUM(`billamt`) AS amt2 FROM `prevbill` WHERE `accode` = '$maccode' 
                            and `billdate` >= '$mfrdate' AND `billdate` <= '$mtodate' $examcond1 ") or die(mysqli_error($conn));

        $msub1 = mysqli_fetch_array($sub1);
        $msub2 = mysqli_fetch_array($sub2);
        $msamt1 = $msub1['amt1'];

        $msamt2 = $msub2['amt2'];
        // browse($msamt2);
        
        $qamt1 = $msamt1 + $msamt2;
        $trn1 = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `billsett` WHERE  `party`= '$maccode' AND `type`='$mtype' 
                             AND `bnkdate` <= '$mtodate' $examcond1 ") or die(mysqli_error($conn));
        $gtrn1 = mysqli_fetch_object($trn1);
        $msetoff1 = $gtrn1->setoff;

        //                                browse($qamt1);
        //                                browse($msetoff1);
        $mbalamt1 = $qamt1 - $msetoff1;
        if ($mbalamt1 > 0) {
            $pdf->SetFont('Arial', 'I', 10);
            $pdf->SetTextColor(0, 0, 0);
            $pdf->SetFillColor(255, 255, 255);
            $pdf->Cell(162, 7, "$macname", 1, 0, '', 1);
            $pdf->Cell(115, 7, "$mcustcode", 1, 0, '', 1);
            browse($mcustcode);
            $pdf->SetTextColor(0, 0, 0);

            $pdf->Ln();
            $totmbalamt = $totmbalamt + $mbalamt1;

            if ($dtype == 2) {
                $pdf->SetFont('Arial', 'I', 10);
                $pdf->SetTextColor(0, 0, 0);
                $pdf->SetFillColor(255, 255, 255);
                $pdf->Cell(60, 7, "Tel : $tel ", 1, 0, '', 1);
                $pdf->Cell(102, 7, "Mobile : $mobile", 1, 0, '', 1);
                $pdf->Cell(115, 7, "Email : $email", 1, 0, '', 1);
                $pdf->SetTextColor(0, 0, 0);
                $pdf->Ln();

                $pdf->SetFont('Arial', 'B', 10);
                $pdf->SetTextColor(0, 0, 0);
                $pdf->SetFillColor(214, 227, 252);
                $pdf->Cell(30, 7, 'Date', 1, 0, '', 1);
                $pdf->Cell(30, 7, 'Bill. No.', 1, 0, '', 1);
                $pdf->Cell(102, 7, 'Exam', 1, 0, '', 1);
                $pdf->Cell(38, 7, 'Bill Amount', 1, 0, 'R', 1);
                $pdf->Cell(39, 7, 'Recd Amount', 1, 0, 'R', 1);
                $pdf->Cell(38, 7, 'Balance', 1, 0, 'R', 1);
                $pdf->Ln();

                mysqli_query($conn, "create temporary table datatmp 
        (SELECT `yearno`,`type`,`vouchno`, `billno`, billdate as ordbydate, 
         DATE_FORMAT(`billdate`, '%d-%m-%Y') AS `billdate`, `billamt`, `accode`, examcode  
         FROM `prevbill` WHERE `accode` = '$maccode' AND billdate >= '$mfrdate' 
         AND billdate <= '$mtodate' $examcond1 order by billdate, billno) 
         UNION all
         (SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,  billno, $mdate as ordbydate, 
         DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`, `$fintot` as billamt, accode, examcode 
         FROM $mfile WHERE `accode` = '$maccode' AND vouchdt >= '$mfrdate' 
         AND vouchdt <= '$mtodate' $examcond1 order by $mdate, billno)") or die(mysqli_error($conn));

                $subtmp1 = mysqli_query($conn, 'select * from datatmp order by ordbydate, billno') or die(mysqli_error($conn));

                $totbillamt = 0;
                $totrecdamt = 0;
                $totbalamt = 0;

                $totbillamt = 0;
                $totrecdamt = 0;
                $totbalamt = 0;

                while ($murl = mysqli_fetch_object($subtmp1)) {
                    $qyear = $murl->yearno;
                    $qbilldate = $murl->billdate;
                    $qbill = $murl->billno;
                    $qexamcode = $murl->examcode;
                    $qvouch = $murl->vouchno;
                    $qamt = $murl->billamt;
                    $qparty = $murl->accode;

                    $dt1 = date_create($mtodate);
                    $dt2 = date_create($qbilldate);
                    $subtdate = $dt1 - $dt2;
                    $mldays = $subtdate;

                    $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `billsett` WHERE  `party`= '$qparty' AND `yearno` ='$qyear' AND 
                            `vouchno`='$qvouch' AND `type`='$mtype' AND bnkdate <= '$mtodate' $examcond1") or die(mysqli_error($conn));
                    $gtrn = mysqli_fetch_object($trn);

                    $msetoff = $gtrn->setoff;
                    $mbalamt = $qamt - $msetoff;

                    if ($mbalamt > 0) {
                        $texam = mysqli_query($conn, "SELECT name FROM `exam` WHERE  `code`= '$qexamcode' ") or die(mysqli_error($conn));
                        $fexam = mysqli_fetch_object($texam);
                        $qexam = $fexam->name;

                        $pdf->SetFont('Arial', '', 8);
                        $pdf->SetTextColor(0, 0, 0);
                        $pdf->SetFillColor(255, 255, 255);
                        $pdf->Cell(30, 7, "$qbilldate", 1, 0, '', 1);
                        $pdf->Cell(30, 7, "$qbill", 1, 0, '', 1);
                        $pdf->Cell(102, 7, "$qexam", 1, 0, '', 1);
                        $pdf->Cell(38, 7, "$qamt", 1, 0, 'R', 1);
                        $pdf->Cell(39, 7, "$msetoff", 1, 0, 'R', 1);
                        $pdf->Cell(38, 7, "$mbalamt", 1, 0, 'R', 1);
                        $pdf->Ln();

                        $totbillamt = $totbillamt + $qamt;
                        $totrecdamt = $totrecdamt + $msetoff;
                        $totbalamt = $totbalamt + $mbalamt;

                        $grbill = $grbill + $qamt;
                        $grrecd = $grrecd + $msetoff;
                        $grbal = $grbal + $mbalamt;
                    }
                }
                $pdf->SetFont('Arial', 'B', 8);
                $pdf->SetTextColor(0, 0, 0);
                $pdf->SetFillColor(255, 255, 255);
                $pdf->Cell(30, 7, '', 1, 0, '', 1);
                $pdf->Cell(30, 7, '', 1, 0, '', 1);
                $pdf->Cell(102, 7, 'School Total', 1, 0, 'R', 1);
                $pdf->Cell(38, 7, "$totbillamt", 1, 0, 'R', 1);
                $pdf->Cell(39, 7, "$totrecdamt", 1, 0, 'R', 1);
                $pdf->Cell(38, 7, "$totbalamt", 1, 0, 'R', 1);
                $pdf->Ln();
                mysqli_query($conn, 'drop table datatmp') or die(mysqli_error($conn));
            }
        }
    



    if ($dtype == 1) {
        $pdf->SetFont('Arial', 'I', 10);
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetFillColor(96, 130, 191);
        $pdf->Cell(162, 7, 'Grand Total', 1, 0, 'R', 1);
        $pdf->Cell(38, 7, "$totmbalamt", 1, 0, 'R', 1);
        $pdf->Cell(39, 7, "$mtr", 1, 0, 'R', 1);
        $pdf->Ln();
    } else {
        $pdf->SetFont('Arial', 'I', 10);
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetFillColor(96, 130, 191);
        $pdf->Cell(162, 7, 'Grand Total', 1, 0, 'R', 1);
        $pdf->Cell(38, 7, "$grbill", 1, 0, 'R', 1);
        $pdf->Cell(39, 7, "$grrecd", 1, 0, 'R', 1);
        $pdf->Cell(39, 7, "$grbal", 1, 0, 'R', 1);
        $pdf->Ln();
    }


$filepath = $_SERVER['DOCUMENT_ROOT'] . "/epiweb/admin/pdf/" . $myear . "/osbill/" . $maccode . ".pdf";
$pdf->Output($filepath, 'F');

////////////////////

$to          = $email ;
$from        = "mbsmmandal@gmail.com"; // address message is sent from
$subject     = "Outstanding Bills = Pending Balance"; // email subject
$body        = "<p>The PDF is attached.</p>"; // email body
//$pdfLocation = $_SERVER['DOCUMENT_ROOT'] . "/epiweb/admin/pdf/" . $myyear . "/osbill/" . $maccode . ".pdf";
$pdfLocation = $filepath ;


$pdfName     = $maccode. ".pdf"; // pdf file name recipient will get
$filetype    = "application/pdf"; // type

// create headers and mime boundry


$eol = PHP_EOL;
$semi_rand     = md5(time());
$mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";
$headers       = "From: $from$eol" .
  "MIME-Version: 1.0$eol" .
  "Content-Type: multipart/mixed;$eol" .
  " boundary=\"$mime_boundary\"";

// add html message body
  $message = "--$mime_boundary$eol" .
  "Content-Type: text/html; charset=\"iso-8859-1\"$eol" .
  "Content-Transfer-Encoding: 7bit$eol$eol" .
  $body . $eol;

// fetch pdf
$file = fopen($pdfLocation, 'rb');
$data = fread($file, filesize($pdfLocation));
fclose($file);
$pdf = chunk_split(base64_encode($data));

// attach pdf to email
$message .= "--$mime_boundary$eol" .
  "Content-Type: $filetype;$eol" .
  " name=\"$pdfName\"$eol" .
  "Content-Disposition: attachment;$eol" .
  " filename=\"$pdfName\"$eol" .
  "Content-Transfer-Encoding: base64$eol$eol" .
  $pdf . $eol .
  "--$mime_boundary--";

// Send the email
if(mail($to, $subject, $message, $headers)) {
  echo "The email has been sent.";
}
else {
  echo "There was an error sending the mail.";
}

?>        
