Print to PDF

[winuser2003]

Hi,

I have a print to pdf script written that allows a specific chart I have to be printed to PDF. I just need to add 2 more columns to this table. I am not used to the print to pdf function so this is why I am asking. Let me introduce what the report currently shows as far as its headers.

 

What I would like to add to these headers is a check in and check out column

 

The Print to PDF code is as follows:

<?php
ini_set('display_errors', 1);
session_start();
if (!isset($_SESSION['user_id'])) {
    header("Location: pm_login.php");
    exit;
}

include '../reporttest/fpdf/fpdf.php';
include 'db_inc.php';
$conn = pdoConnect();

class PMpdf extends fpdf
{
    protected $today;
    protected $headbase;
    protected $db;
    protected $month;
    protected $monthname;
    protected $brand;
    protected $state;
    protected $store;
    protected $widths;
    protected $aligns;
                                                                                                    
    //constructor
    public function __construct($db, $month, $brand, $state, $store)
    {
        parent::__construct('P', 'mm', 'Letter');
        $this->today = date('jS M Y');
        $this->monthname = (new DateTime("{$month}-01"))->format('F Y');
        $this->db = $db;
        $this->month = $month;
        $this->brand = $brand;
        $this->statecode = $state;
        $this->store = $store;
        $this->SetMargins(20, 10, 15);
        $this->widths = [25,65,30,20,20,20];
        $this->aligns = ['L','L','L','R','R','R'];
    }

    //Page header
    public function Header()
    {        
        $this->Image('badger_logo.PNG', 18, 10, 40);
        $this->SetX(90);
        $this->SetFont('Helvetica', '', 18);
        $this->Cell(90, 8, "PM Total Hours", 0, 2, 'C');
        $this->SetFontSize(14);
        $this->Cell(90, 8, $this->monthname, 0, 1, 'C');
        $this->SetFont('','B',8);
        $this->Ln(5);
        $heads = ['Brand Name', 'Store', 'Robot', 'Date', 'Duration', 'Store Hrs'];
        foreach ($heads as $k => $h) {
            $this->Cell($this->widths[$k], 5, $h, 'TB', 0, $this->aligns[$k]);
        }
        $this->Ln(5);
        $this->headbase = $this->GetY();
        $this->SetFont('','');
    }
    
    //Page footer
    public function Footer()
    {
        $this->setY(-18);
        $this->setX(20);
        $this->SetFont('Helvetica', '', 8);
        $this->Cell(90,5,'( '.$this->today.' )', 'T', 0);
        $this->Cell(0,5,$this->PageNo().' of {nb}', 'T', 0, 'R');
    }
    
    public function createSummaryReport()
    {
        $where = ['v.checkin_time LIKE ?', 'a.user_id = ?'];
        $params = [$this->month . '%', $_SESSION['user_id'] ];
        $whereclause = '';

        if ($this->brand) {
            $where[] = "s.client_id = ?";
            $params[] = $this->brand;
        }
        if ($this->statecode) {
            $where[] = "s.state = ?";
            $params[] = $this->statecode;
        }
        if ($this->store) {
            $where[] = "v.store_num = ?";
            $params[] = $this->store;
        }

        $tdata = '';


        $whereclause = 'WHERE ' . join(' AND ', $where);
        
        $res = $this->db->prepare("SELECT c.brand
                                    , CONCAT(LPAD(s.store_num,4,'0'), ' ', s.store_address) as store
                                    , v.robot_num
                                    , date_format(v.checkin_time, '%m/%d/%Y') as date
                                    , round(timestampdiff(SECOND, checkin_time, checkout_time)/3600,2) as total
                               FROM pm_visit v
                                    JOIN 
                                    store s USING (store_num)
                                    JOIN 
                                    client c USING (client_id)
                                    JOIN
                                    user_client_access a USING (client_id)
                               $whereclause   
                               ORDER BY brand, store, robot_num
                              ");
        $res->execute($params);
        $prevs='';
        $prevb = '';
        $stotal = $btotal = 0;
        foreach ($res as $r) {
            $brandname = $r['brand']==$prevb ? '' : $r['brand'];
            $storename = $r['store']==$prevs ? '' : $r['store'];
            $saveb = $r['brand'];
            $saves = $r['store'];
            array_shift($r);
            array_shift($r);
            if ($saveb <> $prevb) {
                if ($prevb) {
                    $this->Cell($this->widths[5], 5, number_format($stotal, 2), 0, 1, $this->aligns[5]);
                    $this->Cell(180 - $this->widths[5], 5, $prevb, 0, 0, 'R', 1);
                    $this->Cell($this->widths[5], 5, number_format($btotal, 2), 'TB', 1, $this->aligns[5], 0);
                    $stotal = $btotal = 0;
                    $this->AddPage();
                }
            }
            elseif ($saves <> $prevs) {
                $this->Cell($this->widths[5], 5, number_format($stotal, 2), 0, 1, $this->aligns[5]);
                $stotal = 0;
            }
            else $this->Ln();
            
            $cells = [$brandname, $storename, $r['robot_num'], $r['date'], $r['total']];
            foreach ($cells as $k => $v) {
                $this->Cell($this->widths[$k], 5, $v, 0, 0, $this->aligns[$k]);
            }
             
            $stotal += $r['total'];
            $btotal += $r['total'];
            $prevb = $saveb;
            $prevs = $saves;
        }
        if ($btotal) {
            $this->Cell($this->widths[5], 5, number_format($stotal, 2), 0, 1, 'R');
            $this->Cell(180 - $this->widths[5], 5, $prevb, 0, 0, 'R', 1);
            $this->Cell($this->widths[5], 5, number_format($btotal, 2), 'TB', 1, 'R', 0);
        }
    }
}

$pdf = new PMpdf($conn, $_GET['ym'], $_GET['brand'], $_GET['state'], $_GET['store']);
$pdf->AliasNbPages();
$pdf->SetFillColor(200);
$pdf->SetDrawColor(120);
$pdf->addPage();
$pdf->createSummaryReport();
$pdf->output();  
?>

 

Can you please advise where I would need to alter the code to add-in the columns? Thank you for your time and suggestions. Let me know if I need to provide more information.

[ginerjm]

Are these 2 new cols already in your db so that your query can retrieve them?  If so - then you just need to add their CELL outputs in the right place to output them such as after the CELL call for the Date field/column.  

Seems like a simple problem with an easy solution so I have to wonder why you are asking.  Are you not a coder perhaps?

[winuser2003]

Hi,

An old friend of mine coded this, so I am just trying to work with his script he helped me out with is all. I am just weeding through how he has everything setup. I will try your suggestion and thank you for your help... 

[winuser2003]

38 minutes ago, ginerjm said:

Are these 2 new cols already in your db so that your query can retrieve them?  If so - then you just need to add their CELL outputs in the right place to output them such as after the CELL call for the Date field/column.  

Seems like a simple problem with an easy solution so I have to wonder why you are asking.  Are you not a coder perhaps?

Yes the 2 cols are in the database already, I am just looking for the right spot to add the cell outputs to the code is all.  Yes, its a simple problem I believe. I am a coder yes, but not as experienced.

[ginerjm]

You have to locate all of the calls to the CELL (or MUTLICELL) function.  That is what puts the data on the page.  They will be in this script in the order of the output you currently get and when you get to the ''date" value that is being output you will insert a couple of new CELL calls with the data items that you retrieve with your query statement.

[winuser2003]

this is what I have in the DB

[ginerjm]

Looking at your current query I see that you only get the following fields:

c.brand, store, v.robot_num, date, total

You need to edit the sql statement to add the two new columns from the appropriate table.  Then use those names in the output portion (the CELL calls) to add them to the output layout.

Suggestion - amend the 'date' field name to a non-reserved word.  Describe the date a little better perhaps as 'trans_date'?  If you change the query you will have to locate any other uses of a fieldname of 'date' and match the new name.

[winuser2003]

Just showing my changes, its just showing 1 error now on line 141

<?php
ini_set('display_errors', 1);
session_start();
if (!isset($_SESSION['user_id'])) {
    header("Location: pm_login.php");
    exit;
}

include '../reporttest/fpdf/fpdf.php';
include 'db_inc.php';
$conn = pdoConnect();

class PMpdf extends fpdf
{
    protected $today;
    protected $headbase;
    protected $db;
    protected $month;
    protected $monthname;
    protected $brand;
    protected $state;
    protected $store;
    protected $widths;
    protected $aligns;
                                                                                                    
    //constructor
    public function __construct($db, $month, $brand, $state, $store)
    {
        parent::__construct('P', 'mm', 'Letter');
        $this->today = date('jS M Y');
        $this->monthname = (new DateTime("{$month}-01"))->format('F Y');
        $this->db = $db;
        $this->month = $month;
        $this->brand = $brand;
        $this->statecode = $state;
        $this->store = $store;
        $this->SetMargins(20, 10, 15);
        $this->widths = [25,65,30,20,20,20];
        $this->aligns = ['L','L','L','R','R','R'];
    }

    //Page header
    public function Header()
    {        
        $this->Image('badger_logo.PNG', 18, 10, 40);
        $this->SetX(90);
        $this->SetFont('Helvetica', '', 18);
        $this->Cell(90, 8, "PM Total Hours", 0, 2, 'C');
        $this->SetFontSize(14);
        $this->Cell(90, 8, $this->monthname, 0, 1, 'C');
        $this->SetFont('','B',8);
        $this->Ln(5);
        $heads = ['Brand Name', 'Store', 'Robot', 'Date', 'Duration', 'Store Hrs'];
        foreach ($heads as $k => $h) {
            $this->Cell($this->widths[$k], 5, $h, 'TB', 0, $this->aligns[$k]);
        }
        $this->Ln(5);
        $this->headbase = $this->GetY();
        $this->SetFont('','');
    }
    
    //Page footer
    public function Footer()
    {
        $this->setY(-18);
        $this->setX(20);
        $this->SetFont('Helvetica', '', 8);
        $this->Cell(90,5,'( '.$this->today.' )', 'T', 0);
        $this->Cell(0,5,$this->PageNo().' of {nb}', 'T', 0, 'R');
    }
    
    public function createSummaryReport()
    {
        $where = ['v.checkin_time LIKE ?', 'a.user_id = ?'];
        $params = [$this->month . '%', $_SESSION['user_id'] ];
        $whereclause = '';

        if ($this->brand) {
            $where[] = "s.client_id = ?";
            $params[] = $this->brand;
        }
        if ($this->statecode) {
            $where[] = "s.state = ?";
            $params[] = $this->statecode;
        }
        if ($this->store) {
            $where[] = "v.store_num = ?";
            $params[] = $this->store;
        }

        $tdata = '';


        $whereclause = 'WHERE ' . join(' AND ', $where);
        
        $res = $this->db->prepare("SELECT c.brand
                                    , CONCAT(LPAD(s.store_num,4,'0'), ' ', s.store_address) as store
                                    , v.robot_num
                                    , v.checkin_time
                                    , v.checkout_time
                                    , date_format(v.checkin_time, '%m/%d/%Y') as date
                                    , round(timestampdiff(SECOND, checkin_time, checkout_time)/3600,2) as total
                               FROM pm_visit v
                                    JOIN 
                                    store s USING (store_num)
                                    JOIN 
                                    client c USING (client_id)
                                    JOIN
                                    user_client_access a USING (client_id)
                               $whereclause   
                               ORDER BY brand, store, robot_num
                              ");
        $res->execute($params);
        $prevs='';
        $prevb = '';
        $stotal = $btotal = 0;
        foreach ($res as $r) {
            $brandname = $r['brand']==$prevb ? '' : $r['brand'];
            $storename = $r['store']==$prevs ? '' : $r['store'];
            $saveb = $r['brand'];
            $saves = $r['store'];
            array_shift($r);
            array_shift($r);
            if ($saveb <> $prevb) {
                if ($prevb) {
                    $this->Cell($this->widths[5], 5, number_format($stotal, 2), 0, 1, $this->aligns[5]);
                    $this->Cell(180 - $this->widths[5], 5, $prevb, 0, 0, 'R', 1);
                    $this->Cell($this->widths[5], 5, number_format($btotal, 2), 'TB', 1, $this->aligns[5], 0);
                    $stotal = $btotal = 0;
                    $this->AddPage();
                }
            }
            elseif ($saves <> $prevs) {
                $this->Cell($this->widths[5], 5, number_format($stotal, 2), 0, 1, $this->aligns[5]);
                $stotal = 0;
            }
            else $this->Ln();
            
            $cells = [$brandname, $storename, $r['checkin_time'], $r['checkout_time'], $r['robot_num'], $r['date'], $r['total']];
            foreach ($cells as $k => $v) {
                $this->Cell($this->widths[$k], 5, $v, 0, 0, 0, 0, $this->aligns[$k]);
            }
             
            $stotal += $r['total'];
            $btotal += $r['total'];
            $prevb = $saveb;
            $prevs = $saves;
        }
        if ($btotal) {
            $this->Cell($this->widths[5], 5, number_format($stotal, 2), 0, 1, 'R');
            $this->Cell(180 - $this->widths[5], 5, $prevb, 0, 0, 'R', 1);
            $this->Cell($this->widths[5], 5, number_format($btotal, 2), 'TB', 1, 'R', 0);
        }
    }
}

$pdf = new PMpdf($conn, $_GET['ym'], $_GET['brand'], $_GET['state'], $_GET['store']);
$pdf->AliasNbPages();
$pdf->SetFillColor(200);
$pdf->SetDrawColor(120);
$pdf->addPage();
$pdf->createSummaryReport();
$pdf->output();  
?>

 

[ginerjm]

I see that 'date' is your new field but it has a horrible name.  A good programmer assigns good names to make it clear what he is working with.  Comes in very handy 6 months from now when he has to go back in for maintenance or upgrades.   I would therefore call it 'ckin_date' (I like short names).  And I also see you are calculating a duration between check in and check out so I would call it 'dur' or 'duration' .   Then add the CELL calls in the logical spot to output these values.  You are so close.

Hint - you may want to alter the date formats to condense the data into a shorter width.  Unless you need a report that shows the seconds and a 4-digit year.

[winuser2003]

is this because because of the width? this is the error I got

Notice: Undefined offset: 6 in /var/www/html/pmapp/reporttest/pm_bill_pdf.php on line 141

Fatal error: Uncaught Exception: FPDF error: Some data has already been output, can't send PDF file (output started at /var/www/html/pmapp/reporttest/pm_bill_pdf.php:141) in /var/www/html/pmapp/reporttest/fpdf/fpdf.php:271 Stack trace: #0 /var/www/html/pmapp/reporttest/fpdf/fpdf.php(1049): FPDF->Error() #1 /var/www/html/pmapp/reporttest/fpdf/fpdf.php(999): FPDF->_checkoutput() #2 /var/www/html/pmapp/reporttest/pm_bill_pdf.php(163): FPDF->Output() #3 {main} thrown in /var/www/html/pmapp/reporttest/fpdf/fpdf.php on line 271

 

[ginerjm]

Well - I don't know what is on line 141.  Do you?  Show me.

As for the 2nd line - you cannot output anything ahead of the pdf output.  That is probably the error message causing this.

[winuser2003]

Line 141 :                 $this->Cell($this->widths[$k], 5, $v, 0, 0, $this->aligns[$k]);

[Barand]

You are outputting 7 data cells (indexes 0 - 6)

            $cells = [$brandname, $storename, $r['checkin_time'], $r['checkout_time'], $r['robot_num'], $r['date'], $r['total']];
            foreach ($cells as $k => $v) {
                $this->Cell($this->widths[$k], 5, $v, 0, 0, 0, 0, $this->aligns[$k]);
            }

In the class constructor, only 6 widths and alignments (0 - 5) are specified, so index 6 is missing for those arrays

        $this->widths = [25,65,30,20,20,20];
        $this->aligns = ['L','L','L','R','R','R'];
    

 

[ginerjm]

Are you Really going to make me search thru your code to find this line?  No thanks.

Obviously the guy who did this thought he was doing something very clever instead of just writing out the Cell lines he built a structure and looped thru it just to make maintenance difficult.  Yes - the k value if going out of range.  You probably have to add a couple entries to those arrays.

[winuser2003]

41 minutes ago, Barand said:

You are outputting 7 data cells (indexes 0 - 6)

            $cells = [$brandname, $storename, $r['checkin_time'], $r['checkout_time'], $r['robot_num'], $r['date'], $r['total']];
            foreach ($cells as $k => $v) {
                $this->Cell($this->widths[$k], 5, $v, 0, 0, 0, 0, $this->aligns[$k]);
            }

In the class constructor, only 6 widths and alignments (0 - 5) are specified, so index 6 is missing for those arrays

        $this->widths = [25,65,30,20,20,20];
        $this->aligns = ['L','L','L','R','R','R'];
    

 

So this is where I need to make the adjustment?

[winuser2003]

42 minutes ago, ginerjm said:

Are you Really going to make me search thru your code to find this line?  No thanks.

Obviously the guy who did this thought he was doing something very clever instead of just writing out the Cell lines he built a structure and looped thru it just to make maintenance difficult.  Yes - the k value if going out of range.  You probably have to add a couple entries to those arrays.

No definitely not.... I will try adding a couple entries to the array and see what happens

[ginerjm]

That's the spirit!