/src/azora.local/domain/product.php

<?php
class Product
{
	function getAllProductInfo() {
		$result = null;
		Loader::Load('Database');
		$dbh = Database::getInstance()->getConnection();			
		$sth = $dbh->prepare('SELECT p.*, cu.username as created_username, mu.username as updated_username, pb.product_balance FROM products p 
								LEFT JOIN users cu ON p.created_by = cu.user_id
								LEFT JOIN users mu ON p.updated_by = mu.user_id
								LEFT JOIN product_balance pb ON p.product_id = pb.product_id
								');			
		
		$sth->execute();
		$result = $sth->fetchAll(PDO::FETCH_ASSOC);
		$sth = null;
		$dbh = null;
		return $result;
	}
	
	function getActiveProducts() {
		$products = null;
		Loader::Load('Database');
		$dbh = Database::getInstance()->getConnection();			
		$sth = $dbh->prepare('SELECT product_id FROM products WHERE product_status = 1 ORDER BY created_dt DESC');					
		$sth->execute();
		$result = $sth->fetchAll(PDO::FETCH_ASSOC);
		$sth = null;
		$dbh = null;
		
		if (isset($result) && count($result) > 0) {
			$products = array();
			foreach($result as $handle) {
				array_push($products, $this->getProduct($handle['product_id']));
			}
		}
		return $products;
	}
	
	function getLatestProducts($limit = 10) {
		$products = null;
		Loader::Load('Database');
		$dbh = Database::getInstance()->getConnection();			
		$sth = $dbh->prepare('SELECT product_id FROM products WHERE product_status = 1 ORDER BY created_dt DESC LIMIT 0, '.$limit);
		$sth->execute();
		$result = $sth->fetchAll(PDO::FETCH_ASSOC);
		$sth = null;
		$dbh = null;
		
		if (isset($result) && count($result) > 0) {
			$products = array();
			foreach($result as $handle) {
				array_push($products, $this->getProduct($handle['product_id']));
			}
		}
		return $products;
	}
	
	function getProductsByCategory($category_id) {
		$products = null;
		Loader::Load('Database');
		$dbh = Database::getInstance()->getConnection();			
		$sth = $dbh->prepare('SELECT DISTINCT p.product_id FROM products p LEFT JOIN product_categories pc ON p.product_id = pc.product_id WHERE p.product_status = 1 AND pc.category_id = '. $category_id .' ORDER BY created_dt DESC');
		$sth->execute();
		$result = $sth->fetchAll(PDO::FETCH_ASSOC);
		$sth = null;
		$dbh = null;
		
		if (isset($result) && count($result) > 0) {
			$products = array();
			foreach($result as $handle) {
				array_push($products, $this->getProduct($handle['product_id']));
			}
		}
		return $products;
	}
	
	function getProduct($product_id) {
		$result = array();
		Loader::Load('Database');
		$dbh = Database::getInstance()->getConnection();
			
		$sth = $dbh->prepare('SELECT p.*, cu.username as created_username, mu.username as updated_username FROM products p 
								LEFT JOIN users cu ON p.created_by = cu.user_id
								LEFT JOIN users mu ON p.updated_by = mu.user_id
								WHERE p.product_id = :product_id');		

		$sth->bindParam(':product_id', $product_id);
		$sth->execute();
		$result['product'] = $sth->fetch(PDO::FETCH_ASSOC);
		
		
		$sth = $dbh->prepare('SELECT * FROM product_balance p							
								WHERE p.product_id = :product_id');		

		$sth->bindParam(':product_id', $product_id);
		$sth->execute();
		$result['product_balance'] = $sth->fetch(PDO::FETCH_ASSOC);
		
		$sth = $dbh->prepare('SELECT p.*, c.category_name FROM product_categories p 								
								INNER JOIN categories c ON p.category_id = c.category_id
								WHERE p.product_id = :product_id');		

		$sth->bindParam(':product_id', $product_id);
		$sth->execute();
		$result['product_categories'] = $sth->fetchAll(PDO::FETCH_ASSOC);
		
		
		$sth = $dbh->prepare('SELECT * FROM product_images p 																
								WHERE p.product_id = :product_id');		

		$sth->bindParam(':product_id', $product_id);
		$sth->execute();
		$result['product_images'] = $sth->fetchAll(PDO::FETCH_ASSOC);
		
		
		$sth = null;
		$dbh = null;
		return $result;
	}
	
	function isExistProductCode($product_code, $product_id) {
		$result = null;
		Loader::Load('Database');
		$dbh = Database::getInstance()->getConnection();
			
		$sth = $dbh->prepare('SELECT 1 FROM products p 								
								WHERE p.product_code = :product_code AND p.product_id <> :product_id');		

		$sth->bindParam(':product_code', $product_code);
		$sth->bindParam(':product_id', $product_id);
		$sth->execute();
		$result = ($sth->rowCount() > 0);
		$sth = null;
		$dbh = null;
		return $result;
	}
	
	function saveProduct($product) {
		$result = false;
		Loader::Load('Database');
		$dbh = Database::getInstance()->getConnection();
			
		try {
		
			$product_id = 0;
		
			$dbh->beginTransaction();
			
			$sth = $dbh->prepare('INSERT INTO products (product_code, product_name, product_description, product_status, product_cost, product_points, created_dt, created_by, updated_dt, updated_by) VALUES 
														(:product_code, :product_name, :product_description, :product_status, :product_cost, :product_points, NOW(), :created_by, NOW(), :updated_by)');		

			$sth->bindParam(':product_code', $product['product']['product_code']);			
			$sth->bindParam(':product_name', $product['product']['product_name']);			
			$sth->bindParam(':product_description', $product['product']['product_description']);			
			$sth->bindParam(':product_status', $product['product']['product_status']);			
			$sth->bindParam(':product_cost', $product['product']['product_cost']);			
			$sth->bindParam(':product_points', $product['product']['product_points']);			
			$sth->bindParam(':created_by', $product['product']['created_by']);			
			$sth->bindParam(':updated_by', $product['product']['updated_by']);			
			$sth->execute();
			$product_id = $dbh->lastInsertId('product_id');
			
			
			$sth = $dbh->prepare('INSERT INTO product_balance (product_id, product_balance) VALUES 
														(:product_id, :product_balance)');		

			$sth->bindParam(':product_id', $product_id);			
			$sth->bindParam(':product_balance', $product['product_balance']['product_balance']);		
			$sth->execute();

			
			foreach ($product['product_categories'] as $productcategory) {
				$sth = $dbh->prepare('INSERT INTO product_categories (product_id, category_id) VALUES 
														(:product_id, :category_id)');		

				$sth->bindParam(':product_id', $product_id);			
				$sth->bindParam(':category_id', $productcategory['category_id']);
				$sth->execute();
			}
			
			
			foreach ($product['product_images'] as $productimage) {
				$sth = $dbh->prepare('INSERT INTO product_images (product_id, image_name, image_type, sys_file_name) VALUES 
														(:product_id, :image_name, :image_type, :sys_file_name)');		

				$sth->bindParam(':product_id', $product_id);			
				$sth->bindParam(':image_name', $productimage['image_name']);
				$sth->bindParam(':image_type', $productimage['image_type']);
				$sth->bindParam(':sys_file_name', $productimage['sys_file_name']);
				$sth->execute();
			}			
			
			$dbh->commit();
			
			$result = true;
			
		} catch(PDOException $e) {
			$dbh->rollback();
			throw new Exception($e->getMessage());
		}
		
		$sth = null;
		$dbh = null;
		return $result;
	}
	
	function updateProduct($product) {
		$result = false;
		Loader::Load('Database');
		$dbh = Database::getInstance()->getConnection();
			
		try {
		
			$product_id = $product['product']['product_id'];
			$filesTBD = array();
		
			$dbh->beginTransaction();
			
			$sth = $dbh->prepare('UPDATE products SET product_code = :product_code, product_name = :product_name, product_description = :product_description, 
									product_status = :product_status, product_cost = :product_cost, product_points = :product_points, updated_dt = NOW(), updated_by = :updated_by WHERE product_id = :product_id');		

			$sth->bindParam(':product_code', $product['product']['product_code']);			
			$sth->bindParam(':product_name', $product['product']['product_name']);			
			$sth->bindParam(':product_description', $product['product']['product_description']);			
			$sth->bindParam(':product_status', $product['product']['product_status']);						
			$sth->bindParam(':product_cost', $product['product']['product_cost']);						
			$sth->bindParam(':product_points', $product['product']['product_points']);						
			$sth->bindParam(':updated_by', $product['product']['updated_by']);
			$sth->bindParam(':product_id', $product_id);			
			$sth->execute();			
			
			
			$sth = $dbh->prepare('UPDATE product_balance SET product_balance = :product_balance WHERE 
														product_id = :product_id');		
			
			$sth->bindParam(':product_balance', $product['product_balance']['product_balance']);		
			$sth->bindParam(':product_id', $product_id);			
			$sth->execute();

			
			$sth = $dbh->prepare('DELETE FROM product_categories WHERE 
									product_id = :product_id');		
			$sth->bindParam(':product_id', $product_id);			
			$sth->execute();
			
			foreach ($product['product_categories'] as $productcategory) {
				$sth = $dbh->prepare('INSERT INTO product_categories (product_id, category_id) VALUES 
														(:product_id, :category_id)');		

				$sth->bindParam(':product_id', $product_id);			
				$sth->bindParam(':category_id', $productcategory['category_id']);
				$sth->execute();
			}
			
			
			foreach ($product['product_imagesTBD'] as $productremovedimage) {
				$sth = $dbh->prepare('SELECT * FROM product_images WHERE  
														product_id = :product_id AND image_type = :image_type');		
				$sth->bindParam(':product_id', $product_id);							
				$sth->bindParam(':image_type', $productremovedimage);
				$sth->execute();
				if ($sth->rowCount() > 0) {
					$imgTBD = $sth->fetch(PDO::FETCH_ASSOC);
					$sth = $dbh->prepare('DELETE FROM product_images WHERE  
														product_image_id = :product_image_id');
					$sth->bindParam(':product_image_id', $imgTBD['product_image_id']);	
					$sth->execute();					
					array_push($filesTBD, dirname(__FILE__).'/images/products/'.$imgTBD['sys_file_name']);
				}
			}
			
			foreach ($product['product_images'] as $productimage) {
			
				$sth = $dbh->prepare('SELECT * FROM product_images WHERE  
														product_id = :product_id AND image_type = :image_type');		
				$sth->bindParam(':product_id', $product_id);							
				$sth->bindParam(':image_type', $productimage['image_type']);
				$sth->execute();
				if ($sth->rowCount() > 0) {
					$imgTBD = $sth->fetch(PDO::FETCH_ASSOC);
					$sth = $dbh->prepare('DELETE FROM product_images WHERE  
														product_image_id = :product_image_id');
					$sth->bindParam(':product_image_id', $imgTBD['product_image_id']);	
					$sth->execute();					
					array_push($filesTBD, dirname(__FILE__).'/images/products/'.$imgTBD['sys_file_name']);
				}
				
				if ($productimage['sys_file_name'] != '') {
					$sth = $dbh->prepare('INSERT INTO product_images (product_id, image_name, image_type, sys_file_name) VALUES 
															(:product_id, :image_name, :image_type, :sys_file_name)');		

					$sth->bindParam(':product_id', $product_id);			
					$sth->bindParam(':image_name', $productimage['image_name']);
					$sth->bindParam(':image_type', $productimage['image_type']);
					$sth->bindParam(':sys_file_name', $productimage['sys_file_name']);
					$sth->execute();
				}
			}			
			
			$dbh->commit();
			
			foreach($filesTBD as $fileTBD)
				unlink($fileTBD);
			
			$result = true;
			
		} catch(PDOException $e) {
			$dbh->rollback();
			throw new Exception($e->getMessage());
		}
		
		$sth = null;
		$dbh = null;
		return $result;
	}
	
	function deleteProduct($products) {
		$result = false;
		Loader::Load('Database');
		$dbh = Database::getInstance()->getConnection();
			
		try {
					
			$filesTBD = array();
			
			$product_ids = implode(',', $products);
		
			$dbh->beginTransaction();

			$sth = $dbh->prepare('SELECT * FROM product_images WHERE product_id IN (' . $product_ids . ')');			
			$sth->execute();
			$images = $sth->fetchAll(PDO::FETCH_ASSOC);
			
			foreach($images as $image) {
				array_push($filesTBD, dirname(__FILE__).'/images/products/'.$image['sys_file_name']);
			}			
			
			$sth = $dbh->prepare('DELETE FROM product_images WHERE product_id IN (' . $product_ids . ')');					
			$sth->execute();
			
			$sth = $dbh->prepare('DELETE FROM product_categories WHERE product_id IN (' . $product_ids . ')');					
			$sth->execute();
			
			$sth = $dbh->prepare('DELETE FROM product_balance WHERE product_id IN (' . $product_ids . ')');		
			$sth->execute();
			
			$sth = $dbh->prepare('DELETE FROM products WHERE product_id IN (' . $product_ids . ')');		
			$sth->execute();
			
			$dbh->commit();
			
			foreach($filesTBD as $fileTBD)
				unlink($fileTBD);
			
			$result = true;
			
		} catch(PDOException $e) {
			$dbh->rollback();
			throw new Exception($e->getMessage());
		}
		
		$sth = null;
		$dbh = null;
		return $result;
	}
	
	function updateAllProductPointValue($newrate, $rounding) {
		$result = false;
		Loader::Load('Database');
		$dbh = Database::getInstance()->getConnection();
			
		try {

			$products = $this->getAllProductInfo();
			
			if ($products) {
			
				$dbh->beginTransaction();			
				
				foreach ($products as $product) {
				
					$points = 0;
					if ($rounding == 0)
						$points = floor($product['product_cost'] / $newrate);
					else {
						$points = round($product['product_cost'] / $newrate);
					}
				
					$sth = $dbh->prepare('UPDATE products SET product_points = :product_points WHERE 
											product_id = :product_id');		

					$sth->bindParam(':product_points', $points);
					$sth->bindParam(':product_id', $product['product_id']);			
					
					$sth->execute();
				}			
				$dbh->commit();
				$result = true;
			}			
			
		} catch(PDOException $e) {
			$dbh->rollback();
			throw new Exception($e->getMessage());
		}
		
		$sth = null;
		$dbh = null;
		return $result;
	}
	
}
?>