Command .shop

This is a exemple created by Sipherion :

package com.aionemu.gameserver.command.player;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.aionemu.commons.database.DB;
import com.aionemu.commons.database.DatabaseFactory;
import com.aionemu.commons.database.ParamReadStH;
import com.aionemu.gameserver.command.BaseCommand;
import com.aionemu.gameserver.dataholders.DataManager;
import com.aionemu.gameserver.model.gameobjects.player.Player;
import com.aionemu.gameserver.model.templates.item.ItemTemplate;
import com.aionemu.gameserver.services.SystemMailService;
import com.aionemu.gameserver.utils.PacketSendUtility;

/**
 * @author Sipherion
 */

public class CmdShop extends BaseCommand {
	
	public static Logger log = LoggerFactory.getLogger(CmdShop.class);
	
	public static final String DELETE_QUERY = "DELETE FROM `myshop` WHERE `id`=?";
	public static final String SELECT_QUERY = "SELECT `id`, `item`, `nb` FROM `myshop` WHERE `player_id`=?";
	
	public void execute(final Player player, String... params) {
		
		// There is no parameter needed
		if (params.length > 1) {
			showHelp(player);
			return;
		}
		
		final int playerId = player.getObjectId();
		final String playerName = player.getName();
		
		try {
			DB.select(SELECT_QUERY, new ParamReadStH() {
				
				// Set unique parameter to SQL query				
				public void setParams(PreparedStatement stmt) throws SQLException {
					stmt.setInt(1, playerId);
				}
				
				// count will be used to inform player on how many objects he bought in the shop
				int count = 0;
		
				public void handleRead(ResultSet rset) throws SQLException {
					// Loop on each result
					while (rset.next()) {
						Connection con = null;
						int nbMail = 0;
						// SQL to get number of inbox mails for this player 
						try {
							con = DatabaseFactory.getConnection();
							PreparedStatement stmtMail = con.prepareStatement("SELECT COUNT(*) FROM mail WHERE mail_recipient_id = ?");
							stmtMail.setInt(1, playerId);
							ResultSet rsetMail = stmtMail.executeQuery();
							
							if(rsetMail.next()){
								nbMail = rsetMail.getInt(1);
							}
							
							rsetMail.close();
							stmtMail.close();
						}
						catch (Exception e) {
							log.error("[Shop] Could not access mailbox for player: " + playerId + " : " + e.getMessage(), e);
						}
						finally {
							DatabaseFactory.close(con);
						}
						
						if (nbMail >= 100) {
							// "Your mailbox is full, unable to receive WebShop items."
							PacketSendUtility.sendMessage(player, "[Shop] Votre boite aux lettres est pleine, impossible de recevoir les objets command\u00E9s.");
							return;
						}
						
						count += 1;
						
						int transactionId = rset.getInt("id");
						int item = rset.getInt("item");
						int nb = rset.getInt("nb");
						
						// Catch item template for its name and maxStackCount
						ItemTemplate itemTemplate = DataManager.ITEM_DATA.getItemTemplate(item);
						int maxStackCount = (int) itemTemplate.getMaxStackCount();
						
						// First of all, check if we can remove the row from database
						if (removeShopTransaction(transactionId, playerId)) {
							// Variables creation
							
							String yourCommand = "Voici votre commande"; // Here is your command
							String thankYou = "\nNous vous remercions pour votre achat."; // Thank you for your order
							
							// These checks are used to see if there is more than maxStackCount to send
							// If yes, multiple mails will be send to player
							if (nb > maxStackCount && maxStackCount != 0 && item != 182400001) {
								int qtyLeft = nb;
								int qtySend = 0;
								for(int i = 1; i <= nb ; i += maxStackCount) {
									if (qtyLeft > maxStackCount) {
										qtySend = maxStackCount;
										qtyLeft -= qtySend;
									}
									else {
										qtySend = qtyLeft;
									}
									SystemMailService.getInstance().sendMail(
											"WebShop", playerName, itemTemplate.getName(), yourCommand + 
											// One mail was not enough, please check your mails
											" (un seul courrier ne suffisait pas pour cette commande ; veuillez v\u00E9rifier vos autres courriers)." 
											+ thankYou, item, qtySend, 0, true
									);
								}
							}
							else if (item == 182400001) {
								SystemMailService.getInstance().sendMail("WebShop", playerName, itemTemplate.getName(), yourCommand + thankYou, 0, -1, nb, true);
							}
							else
								SystemMailService.getInstance().sendMail("WebShop", playerName, itemTemplate.getName(), yourCommand + thankYou, item, nb, 0, true);
						}
						else {
							// A database error has occurred. Please contact an Administrator
							PacketSendUtility.sendMessage(player, "[Shop][Error] Une erreur de base de donn\u00E9es est survenue. Veuillez contacter un adminitrateur.");
						}
						
					}
					// Inform player that there is no transaction waiting for this character
					if(count == 0) {
						// No WebShop order found for <playerName>
						PacketSendUtility.sendMessage(player, "[Shop] Aucun achat boutique n'est en attente pour " + playerName);
					}
					else if (count == 1) {
						// Your WebShop order for <playerName> has been correctly sent.
						PacketSendUtility.sendMessage(player, "[Shop] Votre achat effectu\u00E9 pour " + playerName + " a bien \u00E9t\u00E9 r\u00E9cup\u00E9r\u00E9.");
					}
					else {
						// The X WebShop orders for <playerName> have been correctly sent.
						PacketSendUtility.sendMessage(player, "[Shop] Les " + count + " achats effectu\u00E9s pour " + playerName + " ont bien \u00E9te r\u00E9cup\u00E9r\u00E9s.");
					}
				}
				
			});
		}
		// Log error and inform player if there is a database error
		catch (Exception ex) {
			log.error("[Shop][Error] Could not catch transaction list for player " + playerId + " from DB: " + ex.getMessage(), ex);
			PacketSendUtility.sendMessage(player, "[Shop]{Error] A database error has occured. Unable to satisfy your request.");
			return;
		}
	}
	
	// Function to remove transaction from database. Must be done first for security reasons.
	public boolean removeShopTransaction(int transactionId, int playerId) {
		Connection con = null;
		try {
			con = DatabaseFactory.getConnection();
			PreparedStatement stmt = con.prepareStatement(DELETE_QUERY);
			stmt.setInt(1, transactionId);
			stmt.execute();
			stmt.close();
		}
		catch (Exception e) {
			log.error("[Shop][Error] Could not delete transaction " + transactionId + " for player " + playerId + " from DB: " + e.getMessage(), e);
			return false;
		}
		finally {
			DatabaseFactory.close(con);
		}
		return true;
	}
}