<?php
/**
* Provides the offer class, an interface to the offer table in the database
*
* @author Camil Staps
*
* BusinessAdmin: administrative software for small companies
* Copyright (C) 2015 Camil Staps (ViviSoft)
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
/**
* An interface to the offer table in the database
*/
class offer {
/**
* @var pdo $pdo The PDO class for database communication
* @var int $id The id of the offer
* @var int $contactId The id of the contact this offer is linked to
* @var int $start_date A UNIX timestamp of the start date
* @var int $end_date A UNIX timestamp of the end date
* @var int $invoice_date A UNIX timestamp of the invoice date
* @var bool $accepted Whether the offer is accepted or not
* @var null|int $invoice_fileId If an invoice has been generated, an the id of the file
* @var null|int $payment_received A UNIX timestamp of the date the payment has been received
*/
protected $pdo, $id, $contactId, $start_date, $end_date, $invoice_date, $accepted, $invoice_fileId, $payment_received;
const SUBTOTAL = 1;
const VAT = 2;
const TOTAL = 3;
/**
* Create a new instance
*
* Blah
*
* @param PDO $pdo The PDO class, to access the database
* @param int $id The id of the offer to fetch
*
* @throws PDOException If something went wrong with the database
* @throws Exception If the offer could not be found
*/
public function __construct($pdo, $id) {
$this->pdo = $pdo;
$stmt = $this->pdo->prepare("SELECT * FROM `".constants::db_prefix."offer` WHERE `id`=?");
$stmt->execute(array($id));
if ($stmt->rowCount() == 0) {
throw new Exception("The offer with id '$id' could not be found.");
}
$offer = $stmt->fetch(PDO::FETCH_ASSOC);
$this->id = $offer['id'];
$this->contactId = $offer['contactId'];
$this->start_date = strtotime($offer['start_date']);
$this->end_date = strtotime($offer['end_date']);
$this->invoice_date = strtotime($offer['invoice_date']);
$this->accepted = (bool) $offer['accepted'];
$this->invoice_fileId = $offer['invoice_fileId'];
$this->payment_received = ($offer['payment_received'] == null) ? null : strtotime($offer['payment_received']);
}
//------------------------------------------------------------------------------
// Getters and setters
//------------------------------------------------------------------------------
/**
* Get the ID of the offer
*
* @return int The ID
*/
public function getId() {
return $this->id;
}
/**
* Get the ID of the contact that this offer is linked to
*
* @see offer::getContact() This function returns the contact as an instance of the object class
*
* @return int The ID
*/
public function getContactId() {
return $this->contactId;
}
/**
* Get the contact that this offer is linked to
*
* @see offer::getContactId() This function returns just the id
*
* @return contact The contact
*/
public function getContact() {
return new contact($this->pdo, $this->contactId);
}
/**
* Get all assignment ids for this offer
*
* @see offer::getAssignments() This funtion returns instances of the assignment class instead of just the ids
*
* @throws PDOException Is something went wrong with the database
*
* @return int[] The ids
*/
public function getAssignmentIds() {
$ids = array();
$assignments = $this->pdo->query("SELECT `id` FROM `".constants::db_prefix."assignment` WHERE `offerId`={$this->id}")->fetchAll(PDO::FETCH_ASSOC);
foreach ($assignments as $assignment) {
$ids[] = $assignment['id'];
}
return $ids;
}
/**
* Get all assignments for this offer
*
* @see offer::getAssignmentIds() This function returns just the ids of the assignments, and not instances of the assignment class
*
* @throws PDOException If something went wrong with the database
*
* @return assignment[] An array indexed by id of instances of the assignment class
*/
public function getAssignments() {
$ids = $this->getAssignmentIds();
$assignments = array();
foreach ($ids as $id) {
$assignments[$id] = new assignment($this->pdo, $id);
}
return $assignments;
}
/**
* Get the start date of the assignment
*
* @return int The start date as a UNIX timestamp
*/
public function getStartDate() {
return $this->start_date;
}
/**
* Set the start date of the assignment
*
* @param int $start_date The new start date for the assignment as a UNIX timestamp
*
* @throws PDOException If something went wrong with the database
*
* @return bool True on succes, false on failure
*/
public function setStartDate($start_date) {
$stmt = $this->pdo->prepare("UPDATE `".constants::db_prefix."offer` SET `start_date`=? WHERE `id`=?");
$stmt->execute(array(date('Y-m-d', $start_date), $this->id));
if ($stmt->rowCount() == 1) {
$this->start_date = $start_date;
return true;
} else {
return false;
}
}
/**
* Get the end date of the assignment
*
* @return int The end date as a UNIX timestamp
*/
public function getEndDate() {
return $this->end_date;
}
/**
* Set the end date of the assignment
*
* @param int $end_date The new end date for the assignment as a UNIX timestamp
*
* @throws PDOException If something went wrong with the database
*
* @return bool True on succes, false on failure
*/
public function setEndDate($end_date) {
$stmt = $this->pdo->prepare("UPDATE `".constants::db_prefix."offer` SET `end_date`=? WHERE `id`=?");
$stmt->execute(array(date('Y-m-d', $end_date), $this->id));
if ($stmt->rowCount() == 1) {
$this->end_date = $end_date;
return true;
} else {
return false;
}
}
/**
* Get the invoice date of the assignment
*
* @return int The invoice date as a UNIX timestamp
*/
public function getInvoiceDate() {
return $this->invoice_date;
}
/**
* Set the invoice date of the assignment
*
* @param int $invoice_date The new invoice date for the assignment as a UNIX timestamp
*
* @throws PDOException If something went wrong with the database
*
* @return bool True on succes, false on failure
*/
public function setInvoiceDate($invoice_date) {
$stmt = $this->pdo->prepare("UPDATE `".constants::db_prefix."offer` SET `invoice_date`=? WHERE `id`=?");
$stmt->execute(array(date('Y-m-d', $invoice_date), $this->id));
if ($stmt->rowCount() == 1) {
$this->invoice_date = $invoice_date;
return true;
} else {
return false;
}
}
/**
* Get the date the payment was received
*
* @return int|null The date as a UNIX timestamp, or null if it wasn't received yet
*/
public function getPaymentReceived() {
return $this->payment_received;
}
/**
* Set the payment received date of the assignment
*
* @param int $payment_received The new date the payment has been received as a UNIX timestamp
*
* @throws PDOException If something went wrong with the database
*
* @return bool True on succes, false on failure
*/
public function setPaymentReceived($payment_received) {
$stmt = $this->pdo->prepare("UPDATE `".constants::db_prefix."offer` SET `payment_received`=? WHERE `id`=?");
$stmt->execute(array(date('Y-m-d', $payment_received), $this->id));
if ($stmt->rowCount() == 1) {
$this->payment_received = $payment_received;
return true;
} else {
return false;
}
}
/**
* Check if the offer is accepted or not
*
* @return bool True if the offer is accepted, false if not
*/
public function isAccepted() {
return $this->accepted;
}
/**
* Toggle the `accepted' status of the offer
*
* @throws PDOException If something went wrong with the database
*
* @return bool True on success, false on failure
*/
public function toggleAccepted() {
$stmt = $this->pdo->prepare("UPDATE `".constants::db_prefix."offer` SET `accepted`=? WHERE `id`=?");
$new_value = !$this->accepted;
$stmt->execute(array($new_value, $this->id));
if ($stmt->rowCount() == 1) {
$this->accepted = $new_value;
return true;
} else {
return false;
}
}
/**
* Get the ID of the file that the invoice of this offer is linked to
*
* @see offer::getInvoiceFile() This function returns the file as an instance of the file class
*
* @return int The ID
*/
public function getInvoiceFileId() {
return $this->invoice_fileId;
}
/**
* Get the file that the invoice this offer is linked to
*
* @see offer::getInvoiceId() This function returns just the id
*
* @return file|null The file, or null if it doesn't exist
*/
public function getInvoiceFile() {
if ($this->invoice_fileId != null) {
return new file($this->pdo, $this->invoice_fileId);
} else {
return null;
}
}
/**
* Set the invoice file id of the assignment
*
* @param int $invoice_fileId The new invoice file id for the assignment
*
* @throws PDOException If something went wrong with the database
*
* @return bool True on succes, false on failure
*/
public function setInvoiceFileId($invoice_fileId) {
$stmt = $this->pdo->prepare("UPDATE `".constants::db_prefix."offer` SET `invoice_fileId`=? WHERE `id`=?");
$stmt->execute(array($invoice_fileId, $this->id));
if ($stmt->rowCount() == 1) {
$this->invoice_fileId = $invoice_fileId;
return true;
} else {
return false;
}
}
//------------------------------------------------------------------------------
// Other functions
//------------------------------------------------------------------------------
/**
* Calculate a handy number about the invoice
*
* Subtotal: the sum of the prices of the assignments excl. VAT
*
* VAT: the sum of all the VAT from all the assignments
*
* Total: the sum of subtotal and total
*
* @param int $what Any of offer::SUBTOTAL, offer::VAT and offer::TOTAL
* @param int $round How many decimals to round the result on
* @param bool $format Whether to format the number nicely (for output) or not (for calculations)
*
* @throws PDOException If something went wrong with the database
*
* @return float|bool The calculated value rounded to $round decimals, or false on incorrect input
*/
public function calculate($what = self::TOTAL, $round = 2, $format = true) {
$return = 0;
switch ($what) {
case self::SUBTOTAL:
$assignments = $this->getAssignments();
foreach ($assignments as $assignment) {
$return += $assignment->calculate(assignment::SUBTOTAL, $round + 1, false);
}
break;
case self::VAT:
$assignments = $this->getAssignments();
foreach ($assignments as $assignment) {
$return += $assignment->calculate(assignment::VAT, $round + 1, false);
}
break;
case self::TOTAL:
$return = $this->calculate(self::SUBTOTAL, $round + 1, false) + $this->calculate(self::VAT, $round + 1, false);
break;
default:
return false;
}
if ($format) {
return number_format($return, 2);
} else {
return round($return, 2);
}
}
/**
* Remove this offer from the database
*
* If this doesn't succeed (i.e. false is returned), that means the offer was removed manually or by another instance of this class
*
* @throws PDOException If something went wrong with the database
*
* @return bool True on success, false on failure
*/
public function delete() {
$stmt = $this->pdo->prepare("DELETE FROM `".constants::db_prefix."offer` WHERE `id`=?");
$stmt->execute(array($this->id));
if ($stmt->rowCount() != 1) {
return false;
} else {
return true;
}
}
/**
* Make a new assignment linked to this order
*
* @param string $title The title for this assignment
* @param string $description The description for this assignment
* @param int $hours The amount of hours to work on this assignment
* @param float $price_per_hour The price per hour on this assignment
* @param float $vat The VAT percentage (so, 21 for 21%, not 0.21!)
*
* @throws PDOException If something went wrong with the database
* @throws Exception If there was a problem with the input
*
* @return assignment A new instance of the assignment class containing the new assignment
*/
public function createAssignment($title, $description, $hours, $price_per_hour, $vat) {
$stmt = $this->pdo->prepare("INSERT INTO `".constants::db_prefix."assignment` (`offerId`,`title`,`description`,`hours`,`price_per_hour`,`VAT_percentage`) VALUES (?,?,?,?,?,?)");
$stmt->execute(array(
$this->id,
$title,
$description,
$hours,
$price_per_hour,
$vat
));
if ($stmt->rowCount() == 1) {
return new assignment($this->pdo, $this->pdo->lastInsertId());
} else {
$error = $stmt->errorInfo();
throw new Exception($error[2]);
}
}
/**
* Generate a PDF invoice
*
* @throws PDOException If something went wrong with the database
* @throws Exception If the file could not be written or an other error occured
*
* @return file An instance of the file class with information on the invoice file generated
*/
public function generateInvoice() {
// Check if we already have a file
$file = $this->getInvoiceFile();
if (!($file instanceof file)) {
// If not, create a new file
$i = 1;
do {
$invoice_nr = date('Y',$this->invoice_date) . str_pad($i++, 2, '0', STR_PAD_LEFT);
$filename = 'invoice-' . $invoice_nr . '.pdf';
} while (file_exists(constants::files_folder . $filename));
$file = BusinessAdmin::createFile($this->pdo, $filename);
$this->setInvoiceFileId($file->getId());
} else {
$invoice_nr = str_replace(array('invoice-','.pdf'), array('',''), $file->getFilename());
}
$assignments = $this->getAssignments();
$list = array();
foreach ($assignments as $assignment)
$list[] = array(
$assignment->getTitle(),
$assignment->getPricePerHour() * $assignment->getHours(),
$assignment->getVAT() . "%",
$assignment->getPricePerHour() * $assignment->getHours() * (1 + $assignment->getVAT() / 100)
);
$pdf = new correspondence();
$pdf->SetContact($this->getContact());
$pdf->SetTitle($pdf->_('invoice') . ' ' . $invoice_nr);
$pdf->AddPage();
$pdf->correspondenceHeader();
$pdf->SetY(100);
$pdf->SetFont('','B',14);
$pdf->SetTextColor(correspondence::HEAD_RED, correspondence::HEAD_GREEN, correspondence::HEAD_BLUE);
$pdf->Cell(60,6, $pdf->_('invoice'),'B');
$pdf->SetTextColor(0);
$pdf->Ln();
$width = array(90,25,20,25);
$subtotal = 0;
$btw = array();
$total = 0;
// Header
$pdf->SetFont('','',9);
$pdf->Cell(60,4);
$pdf->Ln();
$pdf->SetFont('','B');
$pdf->Cell(30,4.5,$pdf->_('invoice-date'));
$pdf->SetFont('','');
$pdf->Cell(50,4.5,date("d-m-Y", $this->invoice_date));
$pdf->Ln();
$pdf->SetFont('','B');
$pdf->Cell(30,4.5,$pdf->_('invoice-nr'));
$pdf->SetFont('','');
$pdf->Cell(50,4.5,$invoice_nr);
$pdf->Ln();
$pdf->SetFont('','B');
$pdf->Cell(30,4.5,$pdf->_('due-date'));
$pdf->SetFont('','');
$pdf->Cell(50,4.5,date("d-m-Y",$this->invoice_date+3600*24*30));
$pdf->Ln();
$pdf->Cell(60,4.5,'','B');
$pdf->Ln();
$pdf->SetY(140);
// Table
$pdf->SetFont('','B',11);
$pdf->SetTextColor(correspondence::HEAD_RED, correspondence::HEAD_GREEN, correspondence::HEAD_BLUE);
$pdf->Cell($width[0],7,$pdf->_('description'),'B');
$pdf->Cell($width[1],7,$pdf->_('price-excl'),'B',0,'R');
$pdf->Cell($width[2],7,$pdf->_('vat'),'B',0,'R');
$pdf->Cell($width[3],7,$pdf->_('price-incl'),'B',0,'R');
$pdf->SetTextColor(0);
$pdf->Ln();
$pdf->SetFont('','');
foreach ($list as $row) {
$x = $pdf->getX();
$y = $pdf->getY();
$pdf->MultiCell($width[0],6,iconv('utf-8', 'iso-8859-1', $row[0]),0,'L');
$newy = $pdf->getY();
$pdf->SetXY($x + $width[0], $y);
$pdf->Cell($width[1],6,correspondence::valuta().number_format($row[1],2),'',0,'R');
$pdf->Cell($width[2],6,round($row[2],0) . '%','',0,'R');
$pdf->Cell($width[3],6,correspondence::valuta().number_format($row[3],2),'',0,'R');
$pdf->Ln();
$pdf->SetY($newy);
$pdf->addPageIfOnEnd();
$subtotal += $row[1];
if (!isset($btw[$row[2]])) $btw[$row[2]] = 0;
$btw[$row[2]] += $row[3] - $row[1];
}
$total = $subtotal;
foreach ($btw as $m) {
$total += $m;
}
$pdf->Cell(array_sum($width),5,'','T');
$pdf->Ln();
$pdf->Cell(array_sum($width),5);
$pdf->Ln();
$pdf->Cell($width[0],7);
$pdf->SetFont('','B');
$pdf->Cell($width[1] + $width[2],7,$pdf->_('amount'));
$pdf->SetFont('','');
$pdf->Cell($width[3],7,correspondence::valuta() . $this->calculate(self::SUBTOTAL),'',0,'R');
$pdf->Ln();
foreach ($btw as $p => $m) {
$pdf->Cell($width[0],7);
$pdf->Cell($width[1] + $width[2],7,$pdf->_('vat') . ' '.round($p,0).'%');
$pdf->Cell($width[3],7,correspondence::valuta() . number_format($m,2),'',0,'R');
$pdf->Ln();
}
$pdf->Cell(array_sum($width),5);
$pdf->Ln();
$pdf->Cell($width[0],7);
$pdf->SetFont('','B');
$pdf->Cell($width[1] + $width[2],7,$pdf->_('total'));
$pdf->SetFont('','');
$pdf->Cell($width[3],7,correspondence::valuta() . $this->calculate(self::TOTAL),'T',0,'R');
$pdf->Ln();
// Footer
$pdf->Ln();
$pdf->addPageIfOnEnd();
if ($pdf->GetY() < 230) {
$pdf->SetY(230);
}
$oldY = $pdf->GetY();
$pdf->Cell(45,20,'',1);
$pdf->Cell(12.5,20);
$pdf->Cell(45,20,'',1);
$pdf->Cell(12.5,20);
$pdf->Cell(45,20,'',1);
$pdf->SetFont('','B',10);
$pdf->SetTextColor(correspondence::HEAD_RED, correspondence::HEAD_GREEN, correspondence::HEAD_BLUE);
$pdf->SetY($oldY + 3);
$pdf->Cell(5,5);
$pdf->Cell(40,5,$pdf->_('iban'));
$pdf->Cell(17.5,5);
$pdf->Cell(40,5,$pdf->_('invoice-nr'));
$pdf->Cell(17.5,5);
$pdf->Cell(40,5,$pdf->_('amount-due'));
$pdf->SetTextColor(0);
$pdf->SetFont('','',8);
$pdf->Ln();
$pdf->Ln();
$oldY = $pdf->GetY();
$pdf->Cell(5,5);
$pdf->Cell(40,5, constants::invoice_iban);
$pdf->Cell(17.5,5);
$pdf->Cell(40,5,$invoice_nr);
$pdf->Cell(17.5,5);
$pdf->Cell(40,5,correspondence::valuta() . $this->calculate(self::TOTAL));
$pdf->SetY($oldY + 14);
$pdf->SetFontSize(7);
$pdf->Cell(160,5,$pdf->_('request'),0,0,'C');
$pdf->Ln();
$pdf->Cell(160,5,str_replace('%%', constants::invoice_bic, $pdf->_('biccode')),0,0,'C');
if (file_exists($file->getFilenamePath())) {
unlink($file->getFilenamePath());
}
$pdf->Output($file->getFilenamePath(),'F');
chmod($file->getFilenamePath(),0644);
return $file;
}
}