<?php
class PurchaseOrder {
	//$con = mysqli_connect();
	private $hasQuotes, $PRIVATE_KEY, $TABLE_NAME;
	public $PurchaseOrderID, $PODate, $Username, $VendorID, $Notes, $Sent, $SentDate, $ExpectedDate, $Freight;
	public function __construct($PurchaseOrderID){
		//include('Vendor.class.php');
		global $con;
		global $config;
		$this->hasQuotes;
		$this->PRIVATE_KEY = "PurchaseOrderID";
		$this->TABLE_NAME = "PURCHASE_ORDER_HEADER";


		$query = "SELECT * FROM {$this->TABLE_NAME} WHERE {$this->PRIVATE_KEY} = $PurchaseOrderID";
		$result = mysqli_query($con, $query);
		$row = mysqli_fetch_array($result, MYSQLI_ASSOC);


		if($PurchaseOrderID == 1 && mysqli_num_rows($result)>0){
			//echo "PO already created";
		}elseif($PurchaseOrderID == 1){
			$query = "INSERT INTO PURCHASE_ORDER_HEADER (PurchaseOrderID, PODate, Username, ShipToName, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToZip, ShipToCountry) VALUES (?,?,?,?,?,?,?,?,?,?)";

			$stmt = $con->prepare($query);

			$stmt->bind_param("isssssssss", $PurchaseOrderID, $date, $_SESSION['Username'], $config['CompanyName'], $config['ShipToAddress1'], $config['ShipToAddress2'], $config['ShipToCity'], $config['ShipToState'], $config['ShipToZip'], $config['ShipToCountry']);
			$date = date('Y-m-d');

			$stmt->execute();
			//var_dump($stmt);
			$query = "SELECT * FROM {$this->TABLE_NAME} WHERE {$this->PRIVATE_KEY} = $PurchaseOrderID";
			$result = mysqli_query($con, $query);
			$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

		}
		foreach($row as $key => $value){
				$this->$key = $value;
		}

		try{

			$this->Vendor = new Vendor($this->VendorID);
		}catch(Exception $e){

		}

		//var_dump($this->PRIVATE_KEY);
		$query = "SELECT * FROM PURCHASE_ORDER_DETAIL WHERE {$this->PRIVATE_KEY} = $PurchaseOrderID ORDER BY id";
		$this->Items = [];

		$result = mysqli_query($con, $query);
		$this->ItemID = [];
		while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
			$item = new stdClass();
			$this->ItemID[$row['Line'] . "|" . $row['SKU']] = $row['id'];
			foreach($row as $key => $value){


		//		var_dump($itemInventory);
				$item->$key = $value;

				//array_push($this->ItemsOriginalColumns, $key);
			}
			$itemInventory = new Item('%',$row['Line'], $row['SKU']);
			$this->OnHand = $itemInventory->Qty;
			array_push($this->Items,$item);
		}



		$this->hasQuotes = [];
		$query = "SHOW COLUMNS FROM `$this->TABLE_NAME`";
		$result = mysqli_query($con,$query);
		while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
			if(stripos($row['Type'], "int") === 0 || stripos($row['Type'], "double") === 0 || stripos($row['Type'], "tinyint") === 0 || stripos($row['Type'], "numeric") === 0 || stripos($row['Type'], "double") === 0 || stripos($row['Type'], "decimal") === 0 || stripos($row['Type'], "float") === 0 || stripos($row['Type'], "bigint") === 0 || stripos($row['Type'], "real") === 0 || stripos($row['Type'], "bool") === 0){
				$this->hasQuotes[$row['Field']] = false;
			}else{
				$this->hasQuotes[$row['Field']] = true;
			}

		}
		$this->Total = $this->getTotal();
	}
	public function uploadComplete(){
		global $con;
		event_log($_SESSION['Username'], "UPLOAD PO", print_r($this, true));
		$query = "SELECT PURCHASE_ORDER_DETAIL.*, ((ReceivedQty*PURCHASE_ORDER_DETAIL.Cost)+(Qty*AvgCost))/(Qty+ReceivedQty) NewAvgCost, AvgCost, Qty, Qty+ReceivedQty NewQty FROM `PURCHASE_ORDER_DETAIL` JOIN INVENTORY USING(Line, SKU) WHERE PurchaseOrderID = " . $this->PurchaseOrderID;
		$result = mysqli_query($con, $query);
		$stmt = $con->prepare("UPDATE INVENTORY SET `Qty` = ?, `AvgCost` = ?, `Cost` = ?, `VendorID` = ? WHERE `Line` = ? AND `SKU` = ?");
		$stmt->bind_param("iddiss", $Qty, $AvgCost, $Cost, $VendorID, $Line, $SKU);
		while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
			$Qty = $row['NewQty'];
			$AvgCost = $row['NewAvgCost'];
			$Cost = $row['Cost'];
			$VendorID =$row['VendorID'];
			$Line = $row['Line'];
			$SKU = $row['SKU'];
			$stmt->execute();
			event_log($_SESSION['Username'], "Inventory Item Updated", print_r($row, true));
		}
		$this->Completed = 1;
		$this->save();
		return true;
	}
	public function getReceivedSubtotal(){

		$totalAmount = 0;
		foreach($this->Items as $value){
			$totalAmount += $value->ReceivedQty*$value->Cost;
		}
		return $totalAmount;
	}
	public function getReceivedTotal(){
		$totalAmount = $this->getReceivedSubtotal();
		$totalAmount += $this->Freight;
		$totalAmount += $this->MiscCharges;
		return $totalAmount;
	}
	public function getConfirmedSubtotal(){
		$totalAmount = 0;
		foreach($this->Items as $value){
			$totalAmount += $value->ConfirmedQty*$value->Cost;

		}

		return $totalAmount;
	}
	public function getConfirmedTotal(){
		$totalAmount = $this->getConfirmedSubtotal();
		$totalAmount += $this->Freight;
		$totalAmount += $this->MiscCharges;
		return $totalAmount;
	}
	public function getOrderedSubtotal(){
		$totalAmount = 0;
		foreach($this->Items as $value){
			$totalAmount += $value->OrderQty*$value->Cost;

		}
		$totalAmount += $this->Freight;
		return $totalAmount;
	}
	public function getOrderedTotal(){
		$totalAmount = $this->getOrderedSubtotal();
		$totalAmount += $this->Freight;
		$totalAmount += $this->MiscCharges;
		return $totalAmount;
	}

	public function getSubtotal(){

		if($this->Sent && !$this->Received){
			return $this->getConfirmedSubtotal();
		}else if($this->Received){
			return $this->getReceivedSubtotal();
		}else
		if($this->Confirmed){
			return $this->getConfirmedSubtotal();
		}else{
			return $this->getOrderedSubtotal();
		}
		return $totalAmount;
	}
	public function getTotal(){

		if($this->Sent && !$this->Received){
			$totalAmount = $this->getConfirmedSubtotal();
		}else if($this->Received){
			$totalAmount = $this->getReceivedSubtotal();
		}else
		if($this->Confirmed){
			$totalAmount = $this->getConfirmedSubtotal();
		}else{
			$totalAmount = $this->getOrderedSubtotal();
		}

		$totalAmount += $this->Freight;
		$totalAmount += $this->MiscCharges;
		return $totalAmount;
	}
	private function saveString($Column, $Value){
		if(is_null($Value)){
			$returnValue = "NULL";
		}elseif(strtoupper($Value) == "NOW()"){
			$returnValue = "NOW()";
		}else{
			$returnValue = ($this->hasQuotes[$Column])?"'$Value'":"$Value";
		}

		return " `$Column` = $returnValue ";
	}
	public function addItem($SKU){
		global $con;

		$item = new Item(null, "%", $SKU);

		$query = $con->prepare("INSERT INTO PURCHASE_ORDER_DETAIL (`PurchaseOrderID`, `Line`, `SKU`, `Description`, `Cost`) VALUES (?,?,?,?,?)");
		$query->bind_param("isssd",$this->PurchaseOrderID, $item->Line, $item->SKU, $item->Description, $item->Cost);
		if($query->execute()){
			echo "true";
		}
	}
	public function save(){
		global $con;

		$query = "UPDATE {$this->TABLE_NAME} SET ";
		$saveQuery = [];
		foreach($this->hasQuotes as $fieldName => $quotes){
			if($fieldName != $this->PRIVATE_KEY){
				$saveQuery[] = $this->saveString($fieldName, $this->$fieldName);
			}


		}
		$query .= implode(", ", $saveQuery) . " WHERE `{$this->PRIVATE_KEY}` = " . $this->PurchaseOrderID;
		return mysqli_query($con, $query);

	}
	public function getOrderItem($Line, $SKU){
		$id=($this->ItemID[$Line . "|" . $SKU]);
		return new PurchaseOrderItem($id);
	}
	public function sendEmail($to){
		global $con;
		global $config;
		global $root;
		$typeText = "Purchase Order";
		$subject = "New $typeText #" . $this->PurchaseOrderID;

		require_once $_SERVER['DOCUMENT_ROOT'] . '/assets/PHPMailerAutoload.php';

		$results_messages = array();

		$mail = new PHPMailer(true);

		$mail->CharSet = 'utf-8';

		//class phpmailerAppException extends phpmailerException {}

		try {
			if(is_null($to)){
				$to = $this->Vendor->OrderEmail;
		}else{
				$to = $config["OrderEmail"];
		}
		//if(!PHPMailer::validateAddress($to)) {
		//  throw new phpmailerException("Email address " . $to . " is invalid -- aborting!");
		//}
		$mail->isMail();
		$mail->addReplyTo($config['FromEmail']);
		$mail->From       = $config['FromEmail'];
		$mail->FromName   = $config['FromEmailName'];

		//$mail->addAddress($this->Vendor->OrderEmail);
		//var_dump($this->Vendor->OrderEmail);
		try{
			$to = explode(",",$this->Vendor->OrderEmail);
			foreach($to as $email){
				$mail->addAddress($email);
			}
		} catch (Exception $e ){
			echo $e;
		}
		//
		$mail->addBCC($config["OrderEmail"]);

		$mail->Subject  = $subject;
		$title = $config["FromEmailName"];
		$body =
		'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
		<html>
		<head>
		  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
		  <title>' . $title . '</title>
		</head>
		<body>
		  <div style="width: 640px; font-family: Arial, Helvetica, sans-serif; font-size: 16px;">
			<img src="'.$root.'/img/'. $config['SmallLogo'] .'" style="width: 150px;" />
			<p>New ' . $typeText . ' for ' . $this->Vendor->Name . ' <br />
			Our Account Number: ' . $this->Vendor->OurAccountNumber . '<br />
			Notas: ' . $this->Notes . '<br />

			<a href="' . $root . 'mod.php?p=purchasing&a=viewpo&id=' . $this->PurchaseOrderID . '&key=' . $this->getTokenKey() . '">View Purchase Order #' . $this->PurchaseOrderID . '</a>
			</p>
			<div align="center">

			</div>

		  </div>
		</body>
		</html>';

		$mail->WordWrap = 78;
		$mail->msgHTML($body, dirname(__FILE__), true); //Create message bodies and embed images
		//$mail->addAttachment('images/phpmailer_mini.png','phpmailer_mini.png');  // optional name
		//$mail->addAttachment('images/phpmailer.png', 'phpmailer.png');  // optional name

		$privatekeyfile = 'dkim_private.key';
		//Make a new key pair
		//(2048 bits is the recommended minimum key length -
		//gmail won't accept less than 1024 bits)
		$pk = openssl_pkey_new(
			array(
				'private_key_bits' => 2048,
				'private_key_type' => OPENSSL_KEYTYPE_RSA
			)
		);
		openssl_pkey_export_to_file($pk, $privatekeyfile);

		$mail->DKIM_domain = 'eli-tpr.com';
		$mail->DKIM_private = $privatekeyfile;
		$mail->DKIM_selector = 'phpmailer';
		$mail->DKIM_passphrase = ''; //key is not encrypted


		try {
		  ($mail->send());
		  unlink($privatekeyfile);
		  $results_messages[] = "Message has been sent using MAIL";
		  return $results_messages;
		}catch (phpmailerException $e) {
		  throw new phpmailerException('Unable to send to: ' . $to. ': '.$e->getMessage());
		}
		}
		catch (phpmailerException $e) {
		  $results_messages[] = $e->errorMessage();
		}
	}
	public function getTokenKey(){
		global $config;
		$realKey = md5($this->PurchaseOrderID . $config["SaltKey"]);
		$realKey = substr($realKey, -16);
		return $realKey;
	}
}
class PurchaseOrderItem {

	private $hasQuotes, $PRIVATE_KEY, $TABLE_NAME;
	public function __construct($ID){
		global $con;

		//
		//	Private Key
		//
		$this->PRIVATE_KEY = "id";
		//
		// Table name
		//
		$this->TABLE_NAME = "PURCHASE_ORDER_DETAIL";
		//
		// Get Table Column and add to object.
		//
		$query = "SELECT `{$this->TABLE_NAME}`.*, SUM(PURCHASE_ORDER_RECEIVE.QtyScanned) QtyScanned FROM `{$this->TABLE_NAME}` LEFT JOIN `PURCHASE_ORDER_RECEIVE` USING(PurchaseOrderID, Line, SKU) WHERE PURCHASE_ORDER_DETAIL.`{$this->PRIVATE_KEY}` = $ID GROUP BY PURCHASE_ORDER_DETAIL.`{$this->PRIVATE_KEY}`";
		$result = mysqli_query($con, $query);
		//echo $query;
		$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

		foreach($row as $key => $value){
				$this->$key = $value;
		}
		$this->InventoryItem = new Item("%", $this->Line, $this->SKU);
		unset($this->InventoryItem->Attributes);
		$query = "SELECT SKU, Description, QtyAvailable, Discontinued, AdditionalQty, AdditionalDate FROM SUPPLIER_INVENTORY WHERE SKU = '{$this->SKU}'";
		$result = mysqli_query($con, $query);
		if(mysqli_num_rows($result)){
			$row = mysqli_fetch_array($result );
			$this->SupplierItem = new stdClass();
			$this->SupplierItem->SKU = $row['SKU'];
			$this->SupplierItem->Description = $row['Description'];
			$this->SupplierItem->QtyAvailable = $row['QtyAvailable'];
			$this->SupplierItem->Discontinued = $row['Discontinued'];
			$this->SupplierItem->AdditionalQty = $row['AdditionalQty'];
			$this->SupplierItem->AdditionalDate = $row['AdditionalDate'];
		}
		//var_dump($query);
		//Get sold qty in the last x days
		$query = "SELECT sum(ShippedQty) as ShippedQty FROM `INVOICE_DETAIL` JOIN `INVOICE_HEAD` USING(InvoiceID) where (`INVOICE_HEAD`.`Void` = 0) AND DATEDIFF(INVOICE_HEAD.InvoiceDate, NOW()) < 183 AND Line LIKE '{$this->Line}' AND SKU LIKE '{$this->SKU}'";
		$result = mysqli_query($con, $query);
		$row = mysqli_fetch_array($result);
		$this->SoldQty = $row['ShippedQty'];
		//
		// Get Columns Names and either if they need quotes in a query or not.
		//

		$this->hasQuotes = [];
		$query = "SHOW COLUMNS FROM `{$this->TABLE_NAME}`";
		$result = mysqli_query($con,$query);
		while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
			if(stripos($row['Type'], "int") === 0 || stripos($row['Type'], "double") === 0 || stripos($row['Type'], "tinyint") === 0 || stripos($row['Type'], "numeric") === 0 || stripos($row['Type'], "double") === 0 || stripos($row['Type'], "decimal") === 0 || stripos($row['Type'], "float") === 0 || stripos($row['Type'], "bigint") === 0 || stripos($row['Type'], "real") === 0 || stripos($row['Type'], "bool") === 0){
				$this->hasQuotes[$row['Field']] = false;
			}else{
				$this->hasQuotes[$row['Field']] = true;
			}
		}
	}

	public function delete(){
		global $con;
		$query = $con->prepare("DELETE FROM PURCHASE_ORDER_DETAIL WHERE id = ?");
		$query->bind_param("i", $this->id);
		return $query->execute();


	}
	//
	// Function to create a "column = value" string usable by mysql query;
	//
	private function saveString($Column, $Value){


		if(is_null($Value)){
			$returnValue = "NULL";
		}else{
			$returnValue = ($this->hasQuotes[$Column])?"'$Value'":"$Value";
		}
		return " `$Column` = $returnValue ";
	}
	//
	// function to save all the values in the object to the table.
	//
	public function save(){
		global $con;

		$query = "UPDATE `{$this->TABLE_NAME}` SET ";
		$saveQuery = [];

		foreach($this->hasQuotes as $fieldName => $quotes){
			if($fieldName != $this->PRIVATE_KEY){
				$saveQuery[] = $this->saveString($fieldName, $this->$fieldName);
				//var_dump($this->saveString($fieldName, $this->$fieldName));
			}

		}
		$query .= implode(", ", $saveQuery) . " WHERE `{$this->PRIVATE_KEY}` = " . $this->{$this->PRIVATE_KEY};

		return mysqli_query($con, $query);
	}

}

?>
