Продвинутые пользователи явы взываю к вам XD, пожайлуста помогите написать sql запросы под это:
Скрытый текст
private static final String SQL_LOAD_STATE = "SELECT * FROM politic_system";
public final class PoliticDistrict
{
private static final SystemLogger _log = SystemLogger.getLogger(PoliticDistrict.class.getName());
// Info about owner, cur points, sale taxes
private static final String SQL_LOAD_DISTRICT_INFO = "SELECT * FROM politic_system_district_data WHERE zone_id = ?";
private static final String SQL_UPDATE_DISTRICT_DATA = "UPDATE politic_system_district_data SET owner_id = ?, owner_name = ?, tax = ?, politic_points=?, WHERE zone_id = ?";
// Spawn management of the ncs that leader can add
private static final String SQL_POLITIC_NPC_SPAWN = "SELECT * FROM politic_system_npc_spawn WHERE zone_id = ?";
private static final String SQL_INSERT_NPC_SPAWN = "INSERT INTO politic_system_npc_spawn VALUES (?,?,?,?,?,?)";
// Spawn management of the builds that leader can add
private static final String SQL_POLITIC_BUILD_SPAWN = "SELECT * FROM politic_system_build_spawn WHERE zone_id = ?";
private static final String SQL_INSERT_BUILD_SPAWN = "INSERT INTO politic_system_build_spawn VALUES (?,?,?,?,?,?)";
// Spawn places for the npcs and builds that leader can add
private static final String SQL_LOAD_COORDS = "SELECT * FROM politic_system_general_spawn WHERE zone_id = ?";
// Vote tables
private static final String SQL_SAVE_EMERGENCY_VOTES = "INSERT INTO politic_system_votes VALUES (?,?,?)";
private static final String SQL_LOAD_EMERGENCY_VOTES = "SELECT * FROM politic_system_votes WHERE zone_id = ?";
private int _ownerId;
private String _ownerName;
private L2PoliticOwnZone _zone;
private TIntIntHashMap _voteControl;
private int _politicPoints;
private double _tax = 0;
private List<L2Spawn> _npcs;
private List<L2Spawn> _builds;
private FastList<Integer[]> _coordinates = new FastList<Integer[]>();
public PoliticDistrict(final L2PoliticOwnZone zone)
{
_zone = zone;
_voteControl = new TIntIntHashMap();
_npcs = new FastList<L2Spawn>();
_builds = new FastList<L2Spawn>();
if(PoliticManager.getInstance().isInReignPeriod())
loadDistrictData();
}
private void loadDistrictData()
{
Connection con = null;
try
{
con = Connector.getInstance().getConnection();
PreparedStatement data = con.prepareStatement(SQL_LOAD_DISTRICT_INFO);
data.setInt(1, _zone.getId());
ResultSet rset = data.executeQuery();
if(rset.next())
{
_ownerId = rset.getInt("owner_id");
_ownerName = rset.getString("owner_name");
_tax = rset.getDouble("tax");
_politicPoints = rset.getInt("politc_points");
}
rset.close();
data.close();
PreparedStatement coords = con.prepareStatement(SQL_LOAD_COORDS);
coords.setInt(1, getZone().getId());
ResultSet rset4 = coords.executeQuery();
while(rset4.next())
{
Integer[] array =
{
rset4.getInt("x"),
rset4.getInt("y"),
rset4.getInt("z"),
rset4.getInt("heading")
};
_coordinates.add(array);
}
rset4.close();
coords.close();
if(PoliticManager.getInstance().isInVotePeriod())
{
PreparedStatement votes = con.prepareStatement(SQL_LOAD_EMERGENCY_VOTES);
votes.setInt(1, getZone().getId());
ResultSet rset3 = votes.executeQuery();
while(rset3.next())
{
int candidate = rset3.getInt("candidate_id");
int vots = rset3.getInt("votes");
_voteControl.put(candidate, vots);
}
rset3.next();
votes.close();
}
else if(PoliticManager.getInstance().isInReignPeriod())
{
PreparedStatement npcs = con.prepareStatement(SQL_POLITIC_NPC_SPAWN);
npcs.setInt(1, _zone.getId());
ResultSet rset2 = npcs.executeQuery();
while(rset2.next())
{
final L2NpcTemplate templ = NpcTable.getInstance().getTemplate(rset2.getInt("npc_id"));
try
{
L2Spawn spawn = new L2Spawn(templ);
spawn.setLocx(rset2.getInt("x"));
spawn.setLocy(rset2.getInt("y"));
spawn.setLocz(rset2.getInt("z"));
spawn.setHeading(rset2.getInt("heading"));
spawn.setAmount(1);
spawn.setRespawnDelay(0);
spawn.init();
_npcs.add(spawn);
}
catch(Exception e)
{
_log.severe("Couldnt load npc "+templ.toString()+" in zone "+_zone.getId());
e.printStackTrace();
}
}
rset2.close();
npcs.close();
PreparedStatement builds = con.prepareStatement(SQL_POLITIC_BUILD_SPAWN);
builds.setInt(1, _zone.getId());
ResultSet rset3 = builds.executeQuery();
while(rset3.next())
{
final L2NpcTemplate templ = NpcTable.getInstance().getTemplate(rset3.getInt("npc_id"));
try
{
L2Spawn spawn = new L2Spawn(templ);
spawn.setLocx(rset3.getInt("x"));
spawn.setLocy(rset3.getInt("y"));
spawn.setLocz(rset3.getInt("z"));
spawn.setHeading(rset3.getInt("heading"));
spawn.setAmount(1);
spawn.setRespawnDelay(0);
spawn.init();
_builds.add(spawn);
}
catch(Exception e)
{
_log.severe("Couldnt load npc "+templ.toString()+" in zone "+_zone.getId());
e.printStackTrace();
}
}
rset3.close();
builds.close();
}
}
catch(Exception e)
{
_log.severe("Couldnt load data for politic district zone: "+_zone.getId());
e.printStackTrace();
}
finally
{
Connector.close(con);
}
}
protected void saveDistrictData()
{
Connection con = null;
try
{
con = Connector.getInstance().getConnection();
PreparedStatement data = con.prepareStatement(SQL_UPDATE_DISTRICT_DATA);
data.setInt(1, getOwnerId());
data.setString(1, getOwnerName());
data.setDouble(1, getTax());
data.setInt(4, getPoints());
data.setInt(5, getZone().getId());
data.execute();
data.close();
if(PoliticManager.getInstance().isInVotePeriod())
{
final int zoneId = getZone().getId();
for(final int cand : _voteControl.keys())
{
PreparedStatement votes = con.prepareStatement(SQL_SAVE_EMERGENCY_VOTES);
votes.setInt(1, zoneId);
votes.setInt(2, cand);
votes.setInt(3, _voteControl.get(cand));
votes.execute();
votes.close();
}
}
}
catch(Exception e)
{
_log.severe("Couldnt save politc district data for zone "+_zone.getId());
e.printStackTrace();
}
finally
{
Connector.close(con);
}
}
public int getOwnerId()
{
return _ownerId;
}
public String getOwnerName()
{
return _ownerName;
}
public L2PcInstance getOwner()
{
return L2World.getInstance().getPlayer(_ownerId);
}
public L2PoliticOwnZone getZone()
{
return _zone;
}
public int getPoints()
{
return _politicPoints;
}
public double getTax()
{
return _tax;
}
public void registerCandidate(final int objectId)
{
getVoteControl().put(objectId, 0);
}
public synchronized TIntIntHashMap getVoteControl()
{
return _voteControl;
}
public synchronized void addVote(int candidateId)
{
int newPoints = _voteControl.get(candidateId) + 1;
_voteControl.put(candidateId, newPoints);
}
public void reducePoints(final int reduction)
{
_politicPoints -= reduction;
}
public void setTax(final double tax)
{
_tax = tax;
}
public L2Clan getCandidateClan(final int objId)
{
for(L2Clan clan : ClanTable.getInstance().getClans())
if(clan.getCandidate() == objId)
return clan;
return null;
}
protected void setWinner()
{
int counter = 0;
for(final int cand : _voteControl.keys())
{
if(_voteControl.get(cand) > counter)
{
counter = _voteControl.get(cand);
_ownerId = cand;
}
}
_voteControl.clear();
L2PcInstance player = L2World.getInstance().getPlayer(_ownerId);
if(player != null)
_ownerName = player.getName();
else
_ownerName = CharNameTable.getInstance().getNameById(_ownerId);
}
public void setOwner()
{
int voteCounter = 0;
int winner = 0;
L2Clan winnerClan = null;
for(final int candidate : _voteControl.keys())
{
L2Clan clan = getCandidateClan(candidate);
if(clan == null || clan.getDissolvingExpiryTime() > 0)
continue;
final int candidateVotes = _voteControl.get(candidate);
if(candidateVotes > voteCounter)
{
winner = candidate;
voteCounter = candidateVotes;
winnerClan = clan;
}
}
winnerClan.setOwnedZone(getZone().getId());
Announcements.getInstance().announceToAll("The clan "+winnerClan.getName()+" is now the "+getZone().getName()+ " owner!", 1);
L2PcInstance lord = L2World.getInstance().getPlayer(winner);
if(lord != null)
{
lord.setOwnedZone(getZone().getId());
lord.sendMessage("You are now the "+getZone().getName()+" lord!");
}
}
protected void clearOwner()
{
_ownerId = 0;
_ownerName = "No Owner";
L2Clan ownerClan = null;
for(L2Clan clan : ClanTable.getInstance().getClans())
if(clan.getOwnedZone() == _zone.getId())
{
ownerClan = clan;
break;
}
if(ownerClan != null)
{
ownerClan.setCandidate(0);
ownerClan.setOwnedZone(0);
}
_tax = 0;
_politicPoints = 0;
for(L2Spawn spawn : _npcs)
if(spawn != null)
{
spawn.stopRespawn();
spawn.getLastSpawn().deleteMe();
}
_npcs.clear();
for(L2Spawn spawn : _builds)
if(spawn != null)
{
spawn.stopRespawn();
spawn.getLastSpawn().deleteMe();
}
_builds.clear();
}
public FastList<Integer[]> getSpawnCoordinates()
{
return _coordinates;
}
public Integer[] getCoordinates(int pos)
{
if(pos >= _coordinates.size())
return null;
Integer[] coords = _coordinates.get(pos);
// For sniffer users lol
if(_npcs.size() > 0)
{
for(L2Spawn sp : _npcs)
if(sp.getLocx() == coords[0] && sp.getLocy() == coords[1])
return null;
}
else if(_builds.size() > 0)
{
for(L2Spawn sp : _builds)
if(sp.getLocx() == coords[0] && sp.getLocy() == coords[1])
return null;
}
return coords;
}
public boolean addNewNpc(final int npcId, final int position)
{
boolean result = false;
Integer[] coord = getCoordinates(position);
if(coord != null)
{
final L2NpcTemplate template = NpcTable.getInstance().getTemplate(npcId);
Connection con = null;
try
{
L2Spawn spawn = new L2Spawn(template);
spawn.setLocx(coord[0]);
spawn.setLocy(coord[1]);
spawn.setLocz(coord[2]);
spawn.setHeading(coord[3]);
spawn.setAmount(1);
spawn.setRespawnDelay(0);
spawn.init();
_npcs.add(spawn);
con = Connector.getInstance().getConnection();
PreparedStatement insert = con.prepareStatement(SQL_INSERT_NPC_SPAWN);
insert.setInt(1, getZone().getId());
insert.setInt(2, npcId);
insert.setInt(3, coord[0]);
insert.setInt(4, coord[1]);
insert.setInt(5, coord[2]);
insert.setInt(6, coord[3]);
insert.execute();
insert.close();
result = true;
}
catch(Exception e)
{
_log.warning("Couldnt add new npc ["+npcId+"] to politic zone ["+getZone().getName()+"]");
e.printStackTrace();
}
finally
{
Connector.close(con);
}
}
return result;
}
public boolean addNewBuild(final int npcId, final int position)
{
boolean result = false;
Integer[] coord = getCoordinates(position);
if(coord != null)
{
final L2NpcTemplate template = NpcTable.getInstance().getTemplate(npcId);
Connection con = null;
try
{
L2Spawn spawn = new L2Spawn(template);
spawn.setLocx(coord[0]);
spawn.setLocy(coord[1]);
spawn.setLocz(coord[2]);
spawn.setHeading(coord[3]);
spawn.setAmount(1);
spawn.setRespawnDelay(0);
spawn.init();
_builds.add(spawn);
con = Connector.getInstance().getConnection();
PreparedStatement insert = con.prepareStatement(SQL_INSERT_BUILD_SPAWN);
insert.setInt(1, getZone().getId());
insert.setInt(2, npcId);
insert.setInt(3, coord[0]);
insert.setInt(4, coord[1]);
insert.setInt(5, coord[2]);
insert.setInt(6, coord[3]);
insert.execute();
insert.close();
result = true;
}
catch(Exception e)
{
_log.warning("Couldnt add new build ["+npcId+"] to politic zone ["+getZone().getName()+"]");
e.printStackTrace();
}
finally
{
Connector.close(con);
}
}
return result;
}
}
Скрытый текст
public final class PoliticManager
{
private static final class SingletonHolder
{
private static final PoliticManager INSTANCE = new PoliticManager();
}
private final SystemLogger _log = SystemLogger.getLogger(PoliticManager.class.getName());
public static final int QUESTION_MARK_KEY_ID = 9090;
private static final String SQL_LOAD_STATE = "SELECT * FROM politic_system";
private static final String SQL_SAVE_STATE = "UPDATE politic_system SET state = ?, next_change = ?";
private static final String SQL_SAVE_VOTERS = "INSERT INTO politic_system_voters VALUES (?)";
private static final String SQL_LOAD_VOTERS = "SELECT * FROM politic_system_voters";
private static final String SQL_CLEAR_VOTERS = "DELETE FROM politic_system_voters";
private static final String SQL_CLEAN_LORDS_NPCS = "DELETE FROM politic_system_npc_spawn";
private static final String SQL_CLEAN_LORDS_BUILDS = "DELETE FROM politic_system_build_spawn";
private TIntObjectHashMap<PoliticDistrict> _politicControl;
private FastList<Integer> _voters;
private TIntObjectHashMap<PoliticQuest> _politicQuests;
// Progress control
private SystemState _state = null;
private long _nextChange;
private PoliticManager()
{
SystemConfig.loadConfigs(false);
if(SystemConfig.ENABLE_SYSTEM)
{
_log.config("Initializing...");
_politicControl = new TIntObjectHashMap<PoliticDistrict>();
_politicQuests = new TIntObjectHashMap<PoliticQuest>();
_voters = new FastList<Integer>();
loadSystemData(); // Load state and zone owners
scheduleNextTask(); // Schedule the next politic system task (including recovered from restart)
SystemFuncManager.getInstance();
}
}
public TIntObjectHashMap<PoliticDistrict> getPoliticDistricts()
{
return _politicControl;
}
public void setState(SystemState state)
{
_state = state;
}
public boolean isInVotePeriod() { return _state == SystemState.VOTING; }
public boolean isInValidationPeriod() { return _state == SystemState.VALIDATING; }
public boolean isInReignPeriod() { return _state == SystemState.REIGN; }
public boolean isInRegisterPeriod() { return _state == SystemState.REGISTERING; }
public void setNextChangeTime(final long time)
{
_nextChange = time;
}
public void registerQuest(PoliticQuest quest)
{
_politicQuests.put(quest.getQuestIntId(), quest);
}
public void openVoteAdvice(L2PcInstance player, int markId)
{
if(markId == QUESTION_MARK_KEY_ID)
{
String html = HtmCache.getInstance().getHtm("data/html/politic/voteadvice.htm");
if(html != null)
{
// If you are not going to change html during runtime, is recommendted to create a single
// NpcHtmlMessage and cache it
NpcHtmlMessage msg = new NpcHtmlMessage(5);
msg.setHtml(html);
player.sendPacket(msg);
}
}
}
public void checkLord(L2PcInstance player)
{
if(isInReignPeriod())
if(player.getClan() != null && player.getObjectId() == player.getClan().getCandidate())
player.setOwnedZone(player.getClanOwnedZone());
}
public synchronized FastList<Integer> getVoters()
{
return _voters;
}
public boolean hasVote(final int objectId)
{
return getVoters().contains(objectId);
}
public PoliticDistrict getPoliticDistrict(final int zoneId)
{
return _politicControl.get(zoneId);
}
private void loadSystemData()
{
Connection con = null;
try
{
con = Connector.getInstance().getConnection();
PreparedStatement state = con.prepareStatement(SQL_LOAD_STATE);
ResultSet rset = state.executeQuery();
if(rset.next())
{
_state = SystemState.valueOf(rset.getString("state"));
_nextChange = rset.getLong("next_change");
}
rset.close();
state.close();
if(_state == SystemState.VOTING)
{
PreparedStatement loadVotes = con.prepareStatement(SQL_LOAD_VOTERS);
ResultSet rset2 = loadVotes.executeQuery();
while(rset2.next())
{
_voters.add(rset2.getInt("voter_id"));
}
rset2.close();
loadVotes.close();
}
for(L2PoliticOwnZone zon : ZoneManager.getInstance().getAllZones(L2PoliticOwnZone.class))
_politicControl.put(zon.getId(), new PoliticDistrict(zon));
}
catch(Exception e)
{
_log.severe("Couldnt finish to load system dinamyc data!");
e.printStackTrace();
SystemConfig.ENABLE_SYSTEM = false;
}
finally
{
Connector.close(con);
}
}
public void saveSystemData()
{
Connection con = null;
try
{
con = Connector.getInstance().getConnection();
PreparedStatement saveState = con.prepareStatement(SQL_SAVE_STATE);
saveState.setString(1, _state.toString());
saveState.setLong(2, _nextChange);
saveState.execute();
saveState.close();
if(_state == SystemState.VOTING)
{
PreparedStatement clearVote = con.prepareStatement(SQL_CLEAR_VOTERS);
clearVote.execute();
clearVote.close();
for(final int voter : _voters)
{
PreparedStatement saveVote = con.prepareStatement(SQL_SAVE_VOTERS);
saveVote.setInt(1, voter);
saveVote.execute();
saveVote.close();
}
}
if(isInReignPeriod())
for(PoliticDistrict pd : (PoliticDistrict[])_politicControl.getValues())
pd.saveDistrictData();
}
catch(Exception e)
{
_log.severe("Couldnt finish to save the system dinamyc data!");
e.printStackTrace();
}
finally
{
Connector.close(con);
}
}
private void scheduleNextTask()
{
long delay = _nextChange > 0? _nextChange - Calendar.getInstance().getTimeInMillis() : 0;
Runnable arg = null;
switch(_state)
{
case REGISTERING:
arg = new VoteTask();
if(delay <= 0)
delay = SystemConfig.REGISTRATION_TIME * 24 * 3600 * 1000;
break;
case VOTING:
{
arg = new ValidationTask();
if(delay <= 0)
delay = SystemConfig.VOTE_PERIOD_DURATION * 3600 * 1000;
break;
}
case VALIDATING:
{
arg = new ReignTask();
if(delay <= 0)
delay = SystemConfig.VOTES_VALIDATION_DAYS * 24 * 3600 * 1000;
break;
}
case REIGN:
{
arg = new BreakTask();
if(delay <= 0)
delay = SystemConfig.LORD_PERIOD_DURATION * 24 * 3600 * 1000;
break;
}
}
if(arg != null)
{
if(delay > 0)
ThreadPoolManager.getInstance().scheduleGeneral(arg, delay);
else
ThreadPoolManager.getInstance().executeTask(arg);
_log.config("Next process is "+arg.toString()+" in "+delay /60000+" minute(s)");
}
else
_log.severe("Couldnt schedule next politic task! Cannot find current state!");
}
public void clearLeaders()
{
for(PoliticDistrict pd : (PoliticDistrict[])_politicControl.getValues())
pd.clearOwner();
Connection con = null;
try
{
con = Connector.getInstance().getConnection();
PreparedStatement npcs = con.prepareStatement(SQL_CLEAN_LORDS_NPCS);
npcs.execute();
npcs.close();
PreparedStatement builds = con.prepareStatement(SQL_CLEAN_LORDS_BUILDS);
builds.execute();
builds.close();
}
catch(Exception e)
{
_log.warning("There was a problem deleting lords npcs!");
e.printStackTrace();
}
finally
{
Connector.close(con);
}
}
public static PoliticManager getInstance()
{
return SingletonHolder.INSTANCE;
}
}
Скрытый текст
public final class SystemFuncManager
{
private static final class SingletonHolder
{
private static final SystemFuncManager INSTANCE = new SystemFuncManager();
}
private static final SystemLogger _log = SystemLogger.getLogger(SystemFuncManager.class.getName());
private static final String SQL_LOAD_SKILLS = "SELECT * FROM politic_system_skills";
private static final String SQL_LOAD_ITEMS = "SELECT * FROM politic_system_items";
private TIntObjectHashMap<L2Skill> _buffs;
private TIntIntHashMap _buyItems;
private SystemFuncManager()
{
_buffs = new TIntObjectHashMap<L2Skill>();
_buyItems = new TIntIntHashMap();
loadFunctionData();
}
private void loadFunctionData()
{
Connection con = null;
try
{
con = Connector.getInstance().getConnection();
PreparedStatement skills = con.prepareStatement(SQL_LOAD_SKILLS);
ResultSet rset1 = skills.executeQuery();
while(rset1.next())
{
final int skillId = rset1.getInt("skill_id");
final int skillLevel = rset1.getInt("skill_level");
L2Skill skill = SkillTable.getInstance().getInfo(skillId, skillLevel);
if(skill != null)
_buffs.put(skillId, skill);
}
rset1.close();
skills.close();
PreparedStatement items = con.prepareStatement(SQL_LOAD_ITEMS);
ResultSet rset2 = items.executeQuery();
while(rset2.next())
{
final int itemId = rset2.getInt("item_id");
final int pointPrice = rset2.getInt("points_price");
if(itemId > 0 && pointPrice > 0)
_buyItems.put(itemId, pointPrice);
}
rset2.close();
items.close();
}
catch(Exception e)
{
_log.severe("Couldnt load shop and teach skill lists!");
e.printStackTrace();
}
finally
{
Connector.close(con);
}
}
public TIntObjectHashMap<L2Skill> getSupportSkills()
{
return _buffs;
}
public TIntIntHashMap getPurchaseItems()
{
return _buyItems;
}
public void sendLoudSpeakMessage(final PoliticDistrict pd, final L2PcInstance player, final String msg)
{
L2PoliticOwnZone zone = pd.getZone();
if(zone != null)
{
final CreatureSay say =
new CreatureSay(player.getObjectId(), Say2.ALLIANCE, player.getName(), msg);
for(L2Character character : zone.getCharactersInside().values())
{
if(character == null)
continue;
character.sendPacket(say);
}
}
}
public void switchTerritoryGuardsAI(final PoliticDistrict pd)
{
Collection<L2Character> chars = pd.getZone().getCharactersInside().values();
for(L2Character cha : chars)
{
if(cha != null && cha instanceof L2GuardInstance)
cha.getKnownList().switchAI();
}
}
public void sendVirtualNpcs(final PoliticDistrict pd, final L2PcInstance player)
{
FastList<Integer[]> coords = pd.getSpawnCoordinates();
for(int i = 0; i < coords.size(); i++)
player.sendPacket(new VirtualNpcInfo(i+1, coords.get(i)));
}
public void destroyVirtualNpcs(final PoliticDistrict pd, final L2PcInstance player)
{
int size = pd.getSpawnCoordinates().size();
for(int i = 1; i<=size; i++)
player.sendPacket(new DeleteObject(i));
}
public static SystemFuncManager getInstance()
{
return SingletonHolder.INSTANCE;
}
}
У самого пока руки кривоваты, под написание скулов(((
