<?
class common_stock{						
	public function createOrder($order_no,$order_date, $delivery_date, $status,$job_no=0){			
		global $database;
		
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
		
		$order_date = dates::change_to_sql($order_date);
		$delivery_date = dates::change_to_sql($delivery_date);
		
		$sql = "
		INSERT
		INTO $db_name"."stocks
		(order_no,job_no, order_date, delivery_date, status, creator,date_created, submitter, date_submitted)
		VALUES ('$order_no', '$job_no', '$order_date', '$delivery_date', '$status', '$submitter','$current_date_time', '$submitter', '$current_date_time')";
		
		$result=$database->query($sql);
		$lastid = $database->insert_ID();
		
		return $lastid;
	}

	public function updateOrder($id,$order_no,$order_date, $delivery_date, $status,$job_no=0){
		global $database;
		
		$order_date = dates::change_to_sql($order_date);
		$delivery_date = dates::change_to_sql($delivery_date);
		
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
		$update  = "
		UPDATE $db_name"."stocks
		SET
		order_no='$order_no',
		job_no='$job_no',
		order_date='$order_date', 
		delivery_date='$delivery_date', 
		status='$status', 
		submitter='$submitter', 
		date_submitted='$current_date_time'
		WHERE id = '$id'
		";
		$database->query($update);
	}
	
	public function createOrderMaterial($order_no, $supplier_no, $item_no, $order_quantity, $num_received,
			$cost, $order_date, $delivery_date, $status){
		global $database;
		/**@todo insert order**/
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
	
		$order_date = dates::change_to_sql($order_date);
		$delivery_date = dates::change_to_sql($delivery_date);
	
		$sql = "
		INSERT
		INTO $db_name"."stock_order_materials
		(order_no, supplier_no, item_no, order_quantity, num_received,
		cost, order_date, delivery_date, status, creator,
		date_created, submitter, date_submitted)
		VALUES ('$order_no', '$supplier_no', '$item_no', '$order_quantity', '$num_received',
		'$cost', '$order_date', '$delivery_date', '$status', '$submitter',
		'$current_date_time', '$submitter', '$current_date_time')";
	
		$result=$database->query($sql);
		$lastid = $database->insert_ID();
	
		return $lastid;
	}
	
	public function updateOrderMaterial($id,$order_no, $supplier_no, $item_no, $order_quantity, $num_received,
			$cost, $order_date, $delivery_date, $status){
		global $database;
	
		$order_date = dates::change_to_sql($order_date);
		$delivery_date = dates::change_to_sql($delivery_date);
	
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
		$update  = "
		UPDATE $db_name"."stock_order_materials
		SET
		order_no='$order_no',
		supplier_no='$supplier_no',
		item_no='$item_no',
		order_quantity='$order_quantity',
		num_received='$num_received',
		cost='$cost',
		order_date='$order_date',
		delivery_date='$delivery_date',
		status='$status',
		submitter='$submitter',
		date_submitted='$current_date_time'
		WHERE order_material_id = '$id'
		";
		$database->query($update);
	}
	
	public function updateTotalOrderReceived($matid,$order_no, $just_received, $note){
		global $database;
	
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
		
		$get_total_sql = "	SELECT 
									sum(som.order_quantity) as total_quantity,
									sum(som.num_received) as total_received,
									sum(som.cost) as totol_cost 
							FROM stock_order_materials  som
							WHERE order_no 	 = '$order_no'
							GROUP BY order_no";
		$current_order = $database->getRow($get_total_sql);
		
		$get_material_sql = "	SELECT *
		FROM stock_order_materials
		WHERE order_material_id = '$matid'";
		$current_material = $database->getRow($get_material_sql);
		
		if($current_order&&$current_material){
			$order_quantity = $current_order['total_quantity'];
			$num_received = $current_order['total_received'];
			$item_no = $current_material['item_no'];
			
			/*update the tootal of material received*/
			$update  = "
			UPDATE $db_name"."stock_order_materials
			SET
			order_no='$order_no',
			num_received=num_received+$just_received,
			submitter='$submitter',
			date_submitted='$current_date_time'
			WHERE order_material_id = '$matid'
			";
			$database->query($update);
			
			$update  = "
			INSERT INTO $db_name"." stock_tracking
			(order_material_id,order_no,amount_delivered,note,creator,date_created)
			VALUE('$matid','$order_no','$just_received','$note','$submitter','$current_date_time');";
			$database->query($update);
			
			if($num_received==0&& $current_material['status']==1){
				/*change the status from awaiting to partial*/
				$update  = "
				UPDATE $db_name"."stocks
				SET
				status ='2',
				submitter='$submitter',
				date_submitted='$current_date_time'
				WHERE id = '$order_no'
				";
				$database->query($update);
			}
			
			/*update inventory stock*/
			$update  = "
			UPDATE $db_name"."materials_inventory
			SET
			total_quantity=total_quantity+$just_received,
			submitter='$submitter',
			date_submitted='$current_date_time'
			WHERE id = '$item_no'
			";
			$database->query($update);
			
			logs::submit_logs(array("table" => "materials_inventory", "action_type" => 2,"foreign_id" => $item_no, "description" => "Received quantity $just_received"));

			if($num_received+$just_received>=$order_quantity){
				$is_all_received_sql = "	SELECT
				count(som.order_no) as total
				FROM stock_order_materials  som
				WHERE som.order_no 	 = '$order_no'
				AND som.order_quantity > som.num_received
				GROUP BY som.order_no";
				$is_all_received = $database->getOne($is_all_received_sql);
				
				if($is_all_received==0){
					/*change the status to complete*/
					$update  = "
					UPDATE $db_name"."stocks
					SET
					status ='3',
					submitter='$submitter',
					date_submitted='$current_date_time'
					WHERE id = '$order_no'
					";
					$database->query($update);
				}
			}
			
			
		}
	}
	
	public function get_status($id)
	{
		global $database;
	
		$sql = "
		SELECT status
		FROM stocks
		WHERE id = '$id'
		";
		return $database->getOne($sql);
	}
	
	public function get_details($id)
	{
		global $database;
			
		$sql = "SELECT * FROM stocks WHERE id = '$id'";
		$result = $database->getRow($sql);
	
		$result['order_date'] = dates::change_date_time($result['order_date']);
		$result['delivery_date'] = dates::change_date_time($result['delivery_date']);
		
		$result['creator'] = common_users::full_name($result['creator']);
		$result['date_created'] = dates::change_date_time($result['date_created']);
		
		$result['submitter'] = common_users::full_name($result['submitter']);
		$result['date_submitted'] = dates::change_date_time($result['date_submitted']);
		$result['supplier'] = common_suppliers::get_suppliers_name($result['supplier_no']);
		$result['item'] = common_inventory::get_material($result['item_no']);
		
									
		return $result;
	}
	
	public function get_details_raw($id)
	{
		global $database;
			
		$sql = "SELECT * FROM stocks WHERE id = '$id'";
		$result = $database->getRow($sql);
	
			
		return $result;
	}
	
	public function get_material_detail($id){
		global $database;
			
		$sql = "SELECT * FROM  stock_order_materials WHERE order_material_id = '$id'";
		$result = $database->getRow($sql);
	
		$result['order_date'] = dates::change_date_time($result['order_date']);
		$result['delivery_date'] = dates::change_date_time($result['delivery_date']);
	
		$result['creator'] = common_users::full_name($result['creator']);
		$result['date_created'] = dates::change_date_time($result['date_created']);
	
		$result['submitter'] = common_users::full_name($result['submitter']);
		$result['date_submitted'] = dates::change_date_time($result['date_submitted']);
		$result['supplier'] = common_suppliers::get_suppliers_name($result['supplier_no']);
		$result['item'] = common_inventory::get_material($result['item_no']);
		$result['order'] = common_stock::get_details($result['order_no']);
	
			
		return $result;
	}
	
	public function get_material_detail_raw($id){
		global $database;
			
		$sql = "SELECT * FROM stock_order_materials WHERE order_material_id = '$id'";
		$result = $database->getRow($sql);
		
			
		return $result;
	}
	
	public function get_order_tracking_list($order_no,$matid){
		global $database;
		
		$query = "
		SELECT *
		FROM stock_tracking
		WHERE order_no = '$order_no'
		AND order_material_id = '$matid'
		";
		$results = $database->getAll($query);
		
		
		if($results){
			foreach($results as $index =>$result){
				$result['creator'] = common_users::full_name($result['creator']);
				$result['date_created'] = dates::change_date_time($result['date_created']);
				$results[$index] = $result;
			}
			return $results;
		}else{
			return null;
		}
	}
	
	public function getTotalMaterialByOrderID($order_id){
		global $database;
		
		$sql = "
		SELECT count(*) as total
		FROM stock_order_materials
		WHERE order_no = '$order_id'
		";
		return $database->getOne($sql);
	}

    public function getMaxOrderNoByDate($date){
        global $database;

        $sql = "
		SELECT MAX(order_no)
		FROM stocks
		WHERE order_no LIKE '$date%'
		";
        return $database->getOne($sql);
    }
}

?>