<?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 extends Model{
	public
		$table = 'offer',
		$fillable_columns = ['contactId', 'start_date', 'end_date', 'invoice_date', 'accepted', 'invoice_fileId'];

	protected function accessor($key, $value) {
		switch ($key) {
			case 'start_date':
			case 'end_date':
			case 'invoice_date':
				return strtotime($value);
				break;
			case 'accepted':
				return (bool) $value;
			case 'invoice_fileId':
				return $value == null ? null : (int) $value;
			default:
				return parent::accessor($key, $value);
		}
	}

	protected function mutator($key, $value) {
		switch ($key) {
			case 'start_date':
			case 'end_date':
			case 'invoice_date':
				return is_string($value) ? $value : date('Y-m-d', $value);
			default:
				return parent::mutator($key, $value);
		}
	}

	/**
	 * Get the contact that this offer is linked to
	 *
	 * @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 all discount ids for this offer
	 *
	 * @see offer::getDiscounts()           This funtion returns instances of the discount class instead of just the ids
	 *
	 * @throws PDOException                 Is something went wrong with the database
	 *
	 * @return int[]                        The ids
	 */
	public function getDiscountIds() {
		$ids = array();
		$discounts = $this->pdo->query("SELECT `id` FROM `".Constants::db_prefix."discount` WHERE `offerId`={$this->id}")->fetchAll(PDO::FETCH_ASSOC);
		foreach ($discounts as $discount) {
			$ids[] = $discount['id'];
		}
		return $ids;
	}

	/**
	 * Get all discounts for this offer
	 *
	 * @see offer::getDiscountIds()         This function returns just the ids of the discounts, and not instances of the discount class
	 *
	 * @throws PDOException                 If something went wrong with the database
	 *
	 * @return discount[]                   An array indexed by id of instances of the discount class
	 */
	public function getDiscounts() {
		$ids = $this->getDiscountIds();
		$discounts = array();
		foreach ($ids as $id) {
			$discounts[$id] = new Discount($this->pdo, $id);
		}
		return $discounts;
	}

	/**
	 * Get the payment id for this offer
	 *
	 * @see offer::getPayment()             This funtion returns an instance of the payment class instead of just the id
	 *
	 * @throws PDOException                 Is something went wrong with the database
	 *
	 * @return int|null                     The id, or null if no payment exists
	 */
	public function getPaymentId() {
		$ids = array();
		$payments = $this->pdo->query("SELECT `id` FROM `".Constants::db_prefix."payment` WHERE `offerId`={$this->id}")->fetchAll(PDO::FETCH_ASSOC);
		foreach ($payments as $payment) {
			return $payment['id'];
		}
		return null;
	}

	/**
	 * Get the payment for this offer
	 *
	 * @see offer::getPaymentId()           This function returns just the id of the payment, and not an instance of the payment class
	 *
	 * @throws PDOException                 If something went wrong with the database
	 *
	 * @return payment|null                 The payment, or null if it does not exist
	 */
	public function getPayment() {
		$id = $this->getPaymentId();
		if (is_null($id)) {
			return null;
		} else {
			return new Payment($this->pdo, $id);
		}
	}

	/**
	 * 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() {
		$payment = $this->getPayment();
		if (is_null($payment)) {
			return null;
		} else {
			return $payment->date;
		}
	}

	/**
	* 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;
		}
	}

	/**
	 * 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 CALCULATABLE_SUBTOTAL, CALCULATABLE_VAT and CALCULATABLE_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 = CALCULATABLE_TOTAL, $round = 2, $format = true) {
		$return = 0;
		switch ($what) {
			case CALCULATABLE_SUBTOTAL:
				foreach ($this->getAssignments() as $assignment) {
					$return += $assignment->calculate(CALCULATABLE_SUBTOTAL, $round + 1, false);
				}
				foreach ($this->getDiscounts() as $discount) {
					$return += $discount->calculate(CALCULATABLE_SUBTOTAL, $round + 1, false);
				}
				break;
			case CALCULATABLE_VAT:
				$assignments = $this->getAssignments();
				foreach ($assignments as $assignment) {
					$return += $assignment->calculate(CALCULATABLE_VAT, $round + 1, false);
				}
				foreach ($this->getDiscounts() as $discount) {
					$return += $discount->calculate(CALCULATABLE_VAT, $round + 1, false);
				}
				break;
			case CALCULATABLE_TOTAL:
				$return = $this->calculate(CALCULATABLE_SUBTOTAL, $round + 1, false) + $this->calculate(CALCULATABLE_VAT, $round + 1, false);
				break;
			default:
				return false;
		}
		if ($format) {
			return number_format($return, 2);
		} else {
			return round($return, 2);
		}
	}

	/**
	 * 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]);
		}
		}

	/**
	 * Make a new discount linked to this order
	 *
	 * @param string $title                 The title for this discount
	 * @param string $description           The description for this discount
	 * @param float $value                  The value for this discount
	 * @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 discount                     A new instance of the discount class containing the new discount
	 */
	public function createDiscount($title, $description, $value, $vat) {
		$stmt = $this->pdo->prepare("INSERT INTO `".Constants::db_prefix."discount` (`offerId`,`title`,`description`,`value`,`VAT_percentage`) VALUES (?,?,?,?,?)");
		$stmt->execute(array(
			$this->id,
			$title,
			$description,
			$value,
			$vat
		));
		if ($stmt->rowCount() == 1) {
			return new Discount($this->pdo, $this->pdo->lastInsertId());
		} else {
			$error = $stmt->errorInfo();
			throw new Exception($error[2]);
		}
	}

	/**
	 * Add a payment for this order
	 *
	 * @param string $date                  Optional: the date for the payment
	 *
	 * @throws PDOException                 If something went wrong with the database
	 * @throws Exception                    If there was a problem with the input
	 *
	 * @return payment                      A new instance of the payment class containing the new payment
	 */
	public function createPayment($date=null) {
		$date = is_null($date) ? time() : $date;
		$stmt = $this->pdo->prepare("INSERT INTO `".Constants::db_prefix."payment` (`offerId`,`date`) VALUES (?,?)");
		$stmt->execute([$this->id, date('Y-m-d H:i:s', $date)]);
		if ($stmt->rowCount() == 1) {
			return new Payment($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->invoice_fileId = $file->id;
		} else {
			$invoice_nr = str_replace(array('invoice-','.pdf'), array('',''), $file->filename);
		}

		$list = array();
		foreach ($this->getAssignments() as $assignment)
			$list[] = array(
				$assignment->title,
				$assignment->price_per_hour * $assignment->hours,
				$assignment->VAT_percentage . "%",
				$assignment->price_per_hour * $assignment->hours * (1 + $assignment->VAT_percentage / 100)
			);
		foreach ($this->getDiscounts() as $discount)
			$list[] = array(
				$discount->title,
				$discount->calculate(CALCULATABLE_SUBTOTAL),
				$discount->VAT_percentage . "%",
				$discount->calculate(CALCULATABLE_TOTAL)
			);

		$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(CALCULATABLE_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(CALCULATABLE_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(CALCULATABLE_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;
	}
}