<?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";
		$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;
		
		
		
		$pdf = false;
		if ($pdf) {
			
		}else{
			
			
			
			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 = "noemail@westingpr.com";
				}
			
			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);
			
			$mail->addBCC("info@westingpr.com");
			//$mail->addCC("ccordova@westingpr.com");
			
			$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 = 'westingpr.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(){
		$realKey = md5($this->PurchaseOrderID . "POWestingPR");
		$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);
		unlink($this->InventoryItem->Attributes);
		$query = "SELECT SKU, Description, QtyAvailable, Active FROM SUPPLIER_INVENTORY WHERE SKU = ?";
		$stmt = $con->prepare($query);
		$stmt->bind_param("s", $this->SKU);
		$stmt->execute();
		$supplierSKU = NULL;
		$supplierDescription = NULL;
		$supplierQtyAvailable = NULL;
		$supplierActive = NULL;
		$stmt->bind_result($supplierSKU, $supplierDescription, $supplierQtyAvailable, $supplierActive);
		while ($stmt->fetch()) {
			$this->SupplierItem = new stdClass();
			$this->SupplierItem->SKU = $supplierSKU;
			$this->SupplierItem->Description = $supplierDescription;
			$this->SupplierItem->QtyAvailable = $supplierQtyAvailable;
			$this->SupplierItem->Active = $supplierActive;
		}
		//
		// 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);
	}
	
}

?>