Some questions related to "shaiya web services" are often asked in the Shaiya forums.
This tutorial will explain how to install and configure your web server to meet Shaiya needs and will detail how to implement certain techniques.
Depending of the actual web server you will use the installation can be mandatory or impossible, with all intermediate options.
With a Dedicated Server, the web services are usually pre-installed but you can, and maybe you want, to reinstall everything.
With a Virtual Private Server, some services can be common to the physical host and you can only customize the configuration.
This section explains the full process, if some steps are not required or even not available for you, skip them.
Note that some bundles (like xampp) exist, they allow fast installation. They also grant that the user / admin does not control or understand what is done. If you are looking for a quick solution that claims to answer all possible questions without any effort from you, thank to use these tools and forget this paper.
So since you are following your reading, you want to control the web services you will use. The first question that can arise is the kind of binaries to be used. Win32 (x86) or 64-bits (x64). As of today (April 2013), the stable releases of the PHP processor are only available as 32-bit modules. The 5.5 version is the first available as a x64 library but only beta 2 release is available. Since we can definitively assume that memory management or performances will never an issue for a small to medium Shaiya web server, this tutorial will use 32-bit components.
1.1 Apache Server
The first source for the HTTP service provided by Apache (the httpd.exe executable in windows) should be , but this site offers up to date binaries for linux / netware systems but not for windows.
For windows installation, provides the latest releases.
So go to the web site and download Apache 2.4.4 as a zip archive, be sure to select the 32-bit version on top-left of the page and download the file.
Unpack the zip file. Note that the unzipper integrated to WinSvr 2008 R2 is bugged enough to be able to extract only half of the files (w/o any alerts ofc), so use a reliable unzipper (like WinZip 7.0 (c) Nico Mak 1998 !!)
Open the folder containing the extracted files. There is one folder namled "Apache24" which contains all required files (it's a "portable installation") and one "ReadMe.txt" file. Open it, and read it (!), the first 2 paragraphs are important.
First, this http server will work under XP SP3, Vista (according some, it would have existed), Seven, W8, as well as WinSvr 2003, 2008 & 2012. This tutorial is using a 2008 R2 release.
Second, the http server is compiled with VS 2010 and requires the VC9/10 C/C++ runtime; since we install a x86 version, we also need the Win32 runtime, they are . Download and install these redistribuable packages.
Now, move the "Apache24" folder where you want on your hard disk. It can be anywhere, just choose a smart path.
For this tutorial, we move it to "S:\bin\".
Once moved, browse the "conf" sub-directory of the "Apache24" folder and open the "httpd.conf" file with your favorite text editor (notepad or any other).
The "httpd.conf" is the main configuration file of the http server. It contains 2 important options and other settings:
ServerRoot (line 37) defines the root of the server files-tree, in our case, we set it to "S:/bin/Apache24".
ServerName (line 213) defines the fully-qualified name of your server, the easier value is the IP of the machine. If you have a registered domain name, use it. If the server is on a LAN for domestic or test purpose, use any local name resolved by your etc/hosts file.
DocumentRoot (line 237) defines the the root of the documents served by the webserver, it can be whatever you want, for this tutorial we set it to "S:/www".
ScriptAlias (line 354) defines the folder containing script, change it to "S:/bin/Apache24/cgi-bin" and change that path in its Directory block a few lines below.
Note that you have to change "c:/Apache24/htdocs" to your own path twice, one time in the definition of the "DocumentRoot" item. A second time immediately afterw<ard where the options for the directory are defined.
In our case, the lines 237-238 become:
Code:
DocumentRoot "S:/www"
<Directory "S:/www">
Almost completed. The web server is ready to run ... but it contains no document.
Let's define at least one page. In "s:\www" directory, create a "index.html" document and define it as:
Note that the default name of index file - "index.html" in this case - is defined line 272 of the httpd.conf file.
If you want to use "index.htm" or "toc.html" as a default index page, change the value accordingly.
It's time to start the webserver & test it. Open a DOS box / "Windows PowerShell" window, go to "s:\bin\Apache24\bin" folder, type-in ".\httpd.exe -k install" & validate. This registers the daemon as a service and (usually) start it.
Also start the s:\bin\Apache24\bin\ApacheMonitor.exe tool to check the status of the service (it's a systray application that can be added to your Startup Appl.).
Launch your browser, type-in the url "localhost" and get it. Completed.
1.2 PHP processor
Next step is to install the PHP processor. Depending of your context / settings, you may have a PHP engine yet installed and not upgradable, or you may want to update it, or simply install a a fresh new version.
The main reference for PHP doc & binaries is .
We go to the where the version 5.3 & 5.4 are available, both in "Thread Safe" & "Not Thread Safe" version. The not TS version is dedicated to run PHP as a cgi binary, we want it to be used by multithreaded webservers (Apache or IIS) so we will use the "Thread Safe" version.
We so download the version (only exist as Win32 lib). We unzip the archive and we obtain another portable installation set of files.
Rename the folder to a smarter / shorter name (for instance, "php-5.4.13-Win32-VC9-x86" becomes "php-5.4.13") and move that directory under "s:\bin\", we so now have the "s:\bin\php-5.4.13\" folder.
Go to that folder, duplicate the "php.ini-development" file & rename the copy "php.ini".
The "php.ini" file is the configuration file of the PHP processor. Open it with your text editor and defines the following settings:
short_open_tag (line 211) allows to use the "‹?" open block tag and not only "‹?php" one. I set it to "On".
date.timezone (line 922) defines the timezone of server, you must set it to prevent error with SQL date.
The list of supported timezones is
sendmail_from (line 1006) allows to define the email of a sender, define it if required.
So far, no other changes are needed, but we must setup Apache so that it calls this processor when required, meaning when a .php file is about to be served.
So open back your httpd.conf file ("s:\bin\Apache24\conf\httpd.conf") and add at the very end of the file:
These statements define (for the whole Apache env.) the path of the PHP tools and load the PHP processor at startup.
Of course, if you are using another paths change them to your choices.
Now open the mime.types config file ("s:\bin\Apache24\conf\mime.types") and add at the end of the file:
Launch your browser, type-in the url "localhost/index.php".
Second step completed. We have a fully fonctionnal Apache / PHP server.
1.3 MSSQL Native Driver
We now need to be able to get information from our Shaiya world, and it means from MSSQL databases.
We will use the MS native driver and its PHP wrapper. Both provided by Microsoft.
The PHP drivers for SQL Server are described . Download the Client 3.01 () and copy it on the web server host.
Launch the SQLSVR30.exe file, it shows a small dialog asking you where to extract files. Browse to the extensions folder of your PHP installation; in our case, we indicate & validate the "s:\bin\php-5.4.13\ext\" folder.
The program copies 4 help files and 8 libraries in the extensions folder.
The libraries are available for Apache 5.3 ("_53_") and 5.4 ("_54_") and use the "Thread Safe" ("ts") or the "Not Thread Safe" ("nts") model.
Since we had install a thread safe, 5.4 version, we will use the php_sqlsvr_54_ts.dll file.
This library depends on PHP5TS.dll which is on the root folder of PHP, one option is to add the root folder of PHP in your global path variable environment, a faster way is to copy this lib. in its parent directory. So copy php_sqlsvr_54_ts.dll into "s:\bin\php-5.4.13\".
Now edit your php.ini file ("s:\bin\php-5.4.13\php.ini") and in the extension block, or at end of file add:
Code:
extension=s:/bin/php-5.4.13/php_sqlsvr_54_ts.dll
The PHP Driver now needs the SQL Native Driver that manages actual connection to a local or remote MSSQL server, the Native Driver must so also be installed on the machine hosting the web server.
The SQL Server native client is but the latest version, updated for MSSQL 2012 and compliant with Svr 2005 & 2008 is available and . The 32 or 64-bits version must be chosen according to your system and not according to the Apache or PHP versions we just installed. So if running a x64 system, use the x64 driver.
Once the sqlncli.msi file is downloaded, launch it and process installation.
Restart the web server. Change the index.php file to be:
and reload the index.php page. You should see a "sqlsvr" block.
1.4 Gain access to Shaiya DB
The last part of our installation is the actual connection to the Shaiya databases.
If the MSSQL Server is on the same host than the web server, you should already be able to manage exchanges between them.
If they are on two different hosts, you must ensure that:
the SQL server allows TCP/IP connection
the SQL instance allows remote connection
the firewall of the SQL Svr host allows connection on port 1433 (you may have to add an exception, name: whatever; Port: 1433; Mode: TCP).
With your text editor, create a "s:\www\sqltest.php" document and define it as:
Code:
<html>
<head>
<style>
body {
font-family: verdana, arial, sans-serif;
font-size: 11px;
margin-bottom: 0px;
}
table {
border: 1px solid black;
border-collapse: collapse;
margin: 0;
padding: 0;
}
td {
border: 1px solid black;
font-family: verdana, arial, sans-serif;
font-size: 8pt;
padding: 2px;
}
</style>
</head>
<body>
<?
// utility methods
function FormatErrors($errors){
// Display errors.
echo "Error information: <br/>";
foreach ($errors as $error){
echo "SQLSTATE: ".$error['SQLSTATE']."<br/>";
echo "Code: ".$error['code']."<br/>";
echo "Message: ".$error['message']."<br/><br/>";
}
}
// Specify the server and connection string attributes.
// define IP / name of the machine hosting the MS SQL Svr
$serverInfo = '127.0.0.1';
// $serverInfo = '192.168.1.3';
// $serverInfo = 'a full qualified domain name';
// define your account details (they will be on an unique protected file)
$connectionInfo = array(
'UID' => 'YOUR SHAIYA ACCOUNT NAME',
'PWD' => 'YOUR SHAIYA ACCOUNT PASSWORD',
'Database' => 'PS_UserData'
);
// Connect to MSSQL server using Shaiya account
$conn = sqlsrv_connect($serverInfo, $connectionInfo);
if ($conn === false)
die (FormatErrors(sqlsrv_errors()));
// get information on registered player accounts
$smt = "select top 10 UserUID,UserID,AdminLevel from Users_Master order by UserUID";
// execute the query
$set = sqlsrv_query($conn, $smt);
if ($set === false){
echo "Error in executing query.</br>";
die(FormatErrors(sqlsrv_errors()));
}
// build a table
echo "<table>\n<tr><td>UserUID<td>UserID<td>Admin Level</tr>\n";
// iterate rows from response
while ($row = sqlsrv_fetch_array($set)){
echo "<tr><td>$row[0]<td>$row[1]<td>$row[2]</tr>\n";
// echo "<tr><td>$row[0]<td>XXXX<td>$row[2]</tr>\n";
}
echo "</table>\n";
// release resources
sqlsrv_free_stmt($set);
sqlsrv_close($conn);
?>
</body>
</html>
Launch your browser, type-in the url "localhost/sqltest.php", you will obtain:
or, if an error has occurred, you will obtain full details on it.
In such cases, double-check the required points listed above.
If you fail to fix the issue, google the text of the error, you will (usually) obtain valuable answers.
2- Framework
2.1 Overview
The very first rule to respect when developping a web service is the protection of the system. All operations shall be safe, and because we will interact with end users, all inputs shall be filtered, analysed & securized.
Another important concern is the ease to maintain and enhance the site. An efficient framework and some rules are required. But let's check security first.
2.2 SQL Injections
The main attacks, which we are exposed, are code injections. Let's remember what is a SQL injection.
We suppose that a service need the account name of the player to display some information.
For instance we want to display the name of all toons (characters) of a player.
We know that the characters are stored in the 'Chars' table, the name of account is in the 'UserID' column, the name of characters are in the 'CharName' column. Let's imagine a fast and unsafe solution:
A form will let the player type-in his/her account's name.
The given name will in turn be used in a query, a SQL statement. If we do something like ...
get_account_info.php
HTML Code:
<html><head><meta http-equiv="content-type" content="text/html; charset=UTF-8"><link rel="stylesheet" type="text/css" href="styles.css"><title>Shaiya Web Services</title></head><body>
<?
// manage connection to SQL server
// ...
// build query
$stmt = "SELECT CharName FROM PS_GameData.dbo.Chars ".
"WHERE UserID='{$_POST['name']}'";
// (don't !!) execute the query
/// $set = sqlsrv_query($conn, $smt);
print($smt);
?>
</body></html>
we allow any kind of SQL injection.
This will not create a "potential" risk. It creates the guarantee that the server will be corrupted or completely destroyed in a very short time.
Simply provide a name like "foo'; DROP TABLE PS_GameData.dbo.Chars; go;" and the query becomes:
Code:
$stmt = "SELECT CharName FROM PS_GameData.dbo.Chars WHERE UserID='foo'; DROP TABLE PS_GameData.dbo.Chars; go;'";
meaning:
Code:
SELECT CharName FROM PS_GameData.dbo.Chars WHERE UserID='foo';
DROP TABLE PS_GameData.dbo.Chars;
go;
'
In this block, all sequences but the final simple quote are valid SQL statements, they will be executed and the table 'Chars' will disappear.
So, all inputs shall always be filtered, analysed & securized. Some generic methods exist, using basic tests or regular expressions, some were posted on ePvP, a lot are available with a google search.
Also note that MSSQL servers (at least until release 2008) are very permissive; for instance, the semi-colon is not mandatory to separate statements and the simple quotes are not always reqiured, so be sure to perform all required checks.
Another option to secure input is to parametrize your SQL queries. The PDO engine can help to do it.
2.3 PHP Data Object
'PDO' stands for 'PHP Data Object', it is an extension dedicated to construction and handling of SQL queries. The full documentation of the service is .
This generic extension is integrated to PHP 5.3+ (it was provided by the module "php_pdo.dll" with previous versions), the implementation of actual exchanges with a MSSQL server is provided by the "php_pdo_sqlsrv_5X_[n]ts.dll" libraries installed with the PHP drivers for SQL Server.
Let's modify the php.ini file to use these services.
Open it with your text editor and add a new extension load statement, our changes become:
Restart the Apache server, and reload the phpinfo page defined above. A 'PDO' block is now present as well as a block related to the "PDO for MSSQL" extension.
The extension provide a "PDO" class that will be used to build a parametrized query and to obtain a PDOStatement (a SQL response object). Let rewrite our previous SQL test with these classes:
pdotest.php
Code:
<!doctype html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<style>
body {
font-family: verdana, arial, sans-serif;
font-size: 11px;
margin-bottom: 0px;
}
table {
border: 1px solid black;
border-collapse: collapse;
margin: 0;
padding: 0;
}
td {
border: 1px solid black;
font-family: verdana, arial, sans-serif;
font-size: 8pt;
padding: 2px;
}
</style>
</head>
<body>
<?
// utility methods
function FormatErrors($error){
// Display errors.
echo "Error information: <br/>";
echo "SQLSTATE: ".$error[0]."<br/>";
echo "Code: ".$error[1]."<br/>";
echo "Message: ".$error[2]."<br/><br/>";
}
// define IP or name of the machine hosting the MS SQL Svr
$server = '127.0.0.1';
// define your account details
$sqlUser = 'YOUR SHAIYA ACCOUNT NAME';
$sqlPass = 'YOUR SHAIYA ACCOUNT PASSWORD';
$database = 'PS_UserData';
// create a PDO instance *within* a try / catch block !!
try {
$conn = new PDO("sqlsrv:Server=$server;Database=$database", $sqlUser, $sqlPass);
}
catch (PDOException $e){
// on error the PDO constructor throw an error will all connection details
// including provided user name & password, we do *not* want them to be
// displayed, this error handler is so mandatory.
die("Fail to connect");
}
// setup a query to get information on registered player accounts
$query = $conn->prepare('SELECT TOP 10 UserXUID,UserID,AdminLevel FROM Users_Master ORDER BY UserUID');
// execute the query
if ($query->execute() == false){
echo "Error in executing query.</br>";
die(FormatErrors($query->errorInfo()));
}
// build a table
echo "<table>\n<tr><td>UserUID<td>UserID<td>Admin Level</tr>\n";
// iterate rows from response
while ($row = $query->fetch(PDO::FETCH_NUM)){
echo "<tr><td>$row[0]<td>$row[1]<td>$row[2]</tr>\n";
}
echo "</table>\n";
// release resources
$query = null;
$conn = null;
?>
</body>
</html>
Launch your browser, type-in the url "localhost/pdotest.php", you shall obtain the same output than with the sqltest.php page.
Now, let rewrite the get_account_info.php page to take benefit of a parametrized query.
get_account_info.php
Code:
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link rel="stylesheet" type="text/css" href="styles.css">
<title>Shaiya Web Services</title>
</head>
<body>
<?
function FormatErrors($error){
// Display errors.
echo "Error information: <br/>";
echo "SQLSTATE: ".$error[0]."<br/>";
echo "Code: ".$error[1]."<br/>";
echo "Message: ".$error[2]."<br/><br/>";
}
// define IP or name of the machine hosting the MS SQL Svr
$server = '127.0.0.1';
// define your account details
$sqlUser = 'YOUR SHAIYA ACCOUNT NAME';
$sqlPass = 'YOUR SHAIYA ACCOUNT PASSWORD';
$database = 'PS_GameData';
// Connect to MSSQL server
try {
$conn = new PDO("sqlsrv:Server=$server;Database=$database", $sqlUser, $sqlPass);
}
catch (PDOException $e){
die("Fail to connect");
}
// setup a query to get information on characters, the '?' character is used
// to define the placeholder of one parametrized parameter.
$query = $conn->prepare("SELECT CharName,Level FROM PS_GameData.dbo.Chars WHERE UserID=?");
if ($query === false){
echo "Error in preparing query.</br>";
die(FormatErrors($conn->errorInfo()));
}
// bind the parameter with the variable posted from the FORM, apply String validations.
$query->bindParam(1, $_POST['name'], PDO::PARAM_STR);
// execute the query
if ($query->execute() === false){
echo "Error in executing query.</br>";
die(FormatErrors($query->errorInfo()));
}
// build a table
echo "<table>\n<tr><td>CharName<td>Level</tr>\n";
// iterate rows from response
while ($row = $query->fetch(PDO::FETCH_NUM)){
echo "<tr><td>$row[0]<td>$row[1]</tr>\n";
}
echo "</table>\n";
// release resources
$query->closeCursor();
$query = null;
$conn = null;
?>
</body>
</html>
The manages safe replacement of the parametrized argument. Use it whenever it helps and provides better security than personal checks.
In the sample codes provided below, both methods of communication will be used, the native sqlsrv_xxx methods and the PDO* classes.
2.4 Pages Design
As your shaiya website grows and web services are added, the complexity will of course also grows.
Keeping a stable layout across all pages and not reinventing the wheel with each new page require you to define the general style and some models at the very beginning.
This tutorial is not focused on CSS, nor on a particular controler/model/view PHP diagram. These points are left to you.
However since the tutorial comes with runnable samples, it will also need an efficient framework.
The following layout will be used.
Server menu will lists the server-oriented services (lile the PvP Ranks) and the "my Account" mennu will list player-oriented services. You will add items to these menus with the update of this tutorial.
The page structure is classical with the a 2-layers div.
The code related to menu is in files css/styles.css for styles (!) and css/menu.js for generic handler (that uses jQuery, so the include of jquery.min.js script from the jQuery CDN) and in inc/menu.php, sole file requiring edition that contains the menus / items definition.
Nice Tut bro but i think that 90% of shaiya server developers dont know how wright simple html code they use web.com and **** like that but totorial realy nice!!
This section will detail the implementation of server-oriented web services. These services are chosen because they are relevant for a Shaiya server but they are also used to provide methods and rules, not to argue that we should always resolve them this way.
3.1 Information
TBP
3.2 PvP Ranks (coded from strach)
3.2.1 Introduction
[GM]Nvia recently proposes a page (php & SQL code) to display a ranking of PvP players, his post is present .
This chapter will explain how to obtain more or less the same ranking with AJAX requests.
3.2.2 Layout & User interaction
An Ajax approach - meaning asynchronous requests used to build step by step the page - is perfect here. Indeed the user will select a faction (light or dark) to obtain 6 classes and these he/she will select one class to obtain the ranking of the top 25 players.
basically, it means we could solve the whole puzzle with 3 div.
Code:
[div id="faction"] some HTML code here [/div]
[div id="classes"][/div]
[div id="players"][/div]
or
Code:
[div id="faction"] some HTML code here [/div]
[div id="classes"]
[div id="players"][/div]
[/div]
a few code in 'faction' block will provide the code (the innerHTML content) of the 'classes' block.
a few code in 'classes' block will provide the code (the innerHTML content) of the 'players' block.
in the previous chapter, we did use the XMLHttpRequest class and the httpRequest() helper function to obtain some JS scripts; in this chapter the httpRequest function will be use to obtain html blocks.
3.2.3 The Faction Div
The main 'PvP Rank' page is feathery. It defines the 'faction' div and provides 2 JS functions.
The page include the 'httpRequests.js' that we already know.
An 'onload' event-handler is defined to fill-in the classes div upon page loading.
The same function is used as response to click on faction image and this function simply performs an httpRequest of the "pvp_classes.php" file (1st argument) to fill-in the "classes" div (2nd argument).
3.2.4 The Classes Div
This block is build with the response of the pvp_classes.php page.
This file is defined as: pvp_classes.php
HTML Code:
<!doctype html><div><?
// this content will define the innerHTML property of a div of caller-parent
// the returned content consists in 6 img defining the classes of AoL or UoF faction
// side parameter is required
if (!isset($_GET['side']))
die();
// get & check its value
$side = intval($_GET['side']);
if (''.$side != $_GET['side'])
die();
// define the classes data: ie the name of img defining the 6 class for each faction
// this array allows to build the page with a simple loop.
$classes = array(
0 => array(
0 => 'Fighter', 1 => 'Defender', 2 => 'Ranger',
3 => 'Archer', 4 => 'Mage', 5 => 'Priest'
),
1 => array(
0 => 'Warrior', 1 => 'Guardian', 2 => 'Assassin',
3 => 'Hunter', 4 => 'Pagan', 5 => 'Oracle'
)
);
// build the image definition thank to these arrays
for ($k = 0; $k < 6; $k++)
echo " <img width='75' height='150' onclick='selectClass($side, $k);' ".
"src='./jpg/{$classes[$side][$k]}.jpg' title='{$classes[$side][$k]}' />\n";
// (re)insert empty div for players table
?></div><div id="players" class="centered"></div>
Can it more easy?
With this insertion within the initial 'classes' div, the DOM of the page becomes:
With this view, we better see how the page is built div by div.
The classes img define another click handler function, the selectClass() that was defined in the (top) page.
We already deal with this constraint with previous chapter, the page obtained by the http-request can not contain JS that will be automatically merged to the global JS context. In previous chapter, we decide to receive full / plain JS scripts and merge it manually. In this chapter we receive plain html and thus any required JS functions must be defined in the top page.
And according the top page, this function performs an httpRequest of the "pvp_players.php" file to fill-in the "players" div.
3.2.4 The Players Div
This block is build with the response of the pvp_players.php page. This page, and only this one, read some data from the daatbases.
pvp_players.php
HTML Code:
<!doctype html><?
// this content will define the innerHTML property of a div of caller-parent
// the returned content consists in a table with the top 25 players
// side & class parameters are required
if (!isset($_GET['side']) || !isset($_GET['class']))
die();
// get & check their values
$side = intval($_GET['side']);
$class = intval($_GET['class']);
if (''.$side != $_GET['side'] || ''.$class != $_GET['class'])
die();
if ($side < 0 || $side > 1 || $class < 0 || $class > 5)
die();
// build a table with the top 25 players matching the given side ('Country') and class ('Job')
// chars must also be not deleted ('Chars.Del=0) and not admin (Users_Master.Status=0)
$smt = 'SELECT TOP 25 c.CharName,c.Level,c.K1,c.K2,gu.GuildName '.
'FROM PS_GameData.dbo.Chars c '.
'JOIN PS_UserData.dbo.Users_Master m ON m.UserUID=c.UserUID '.
'JOIN PS_GameData.dbo.UserMaxGrow g ON g.UserUID=c.UserUID '.
'LEFT JOIN PS_GameData.dbo.GuildChars gc ON gc.CharID=c.CharID '.
'LEFT JOIN PS_GameData.dbo.Guilds gu ON gu.GuildID=gc.GuildID '.
'WHERE c.Del=0 AND g.Country=:cntr AND c.Job=:job '.
'ORDER BY c.K1 desc,c.K2,c.CharName';
// or to exclude admins from the response
// 'WHERE c.Del=0 AND m.Status=0 AND g.Country=:cntr AND c.Job=:job '
// connect to database using a PDO
include('./inc/pdoConnect.php');
// setup the query, bind the parameter & execute the query
$query = $conn->prepare($smt);
if ($query != null){
if ($query->bindParam(':cntr', $side, PDO::PARAM_INT) &&
$query->bindParam(':job', $class, PDO::PARAM_INT)){
if ($query->execute()){
echo "<table width='650' id='top_pvp'>\n<tr>\n";
echo "<th width= '50''>Rank</th>\n";
echo "<th width='200'>Character</th>\n";
echo "<th width='100'>Level</th>\n";
echo "<th width='100'>Kills</th>\n";
echo "<th width='200'>Guild</th>\n";
echo "</tr>\n";
// iterate rows
$i = 1;
while ($row = $query->fetch(PDO::FETCH_NUM)){
echo '<tr><td>'.$i++.'</td>';
echo '<td>'.$row[0].'</td>';
echo '<td>'.$row[1].'</td>';
echo '<td>'.$row[2].'</td>';
$guild = $row[4];
if ($guild == null)
$guild = 'none';
echo '<td>'.$guild.'</td>';
echo "</tr>\n";
}
$query->closeCursor();
echo "</table>\n";
}
}
$query = null;
}
$conn = null;
?>
This PHP script receives 2 parameters, the faction as 'side' mus be 0 or 1, the class as 'class' must be between 0 and 5.
After retreiving and testing these parameters, the code opens a PDO-connection with the server and manages a request to obtain:
- the name of player
- the level of player
- the number of kills
- the number of deaths (we actually don't use it in thi spage)
- and the optional name of the guild of the player
Not all players are in a guild. When they are, the table GuildChars contains a row with the character ID (CharID) and the Guild ID. The details of the guild itself, including its name, are stored in the Guilds table, and each guild has an unique GuildID identifier.
So if we join the GuildChars table on CharID and then the Guilds table on the GuildID, we will obtain the guild's name ... unless the character is not in a guild; In such cases the join will fail because ni link on CHarID can be done, for this reason we use a "left join" on not "a (natural) join". Left join allows the link to not be done and provide null values in such cases.
Knowing that the request is straightforward, its parametrization uses named values (':cntr' and ':job') instead of the numerical fields already seen.
The returned rows are finally used to build a table. That table defines the innerHTML property of the 'players' div which is auto-rendered as soon as the httpRequest() function defines it.
3.3 PvP Ranks (using jQuery frmk)
The implementation presented in the previous chapter was coded before the definition of the global layout of this site.
We can revisit it to insert the same service in our framework and take benefit of jQuery.
3.3.1 Layout & User interaction
The Ajax controller was efficient, we will simply replace our httpRequest helper by jQuery calls (a "load()").
The page view was a bit less efficient. Indeed the selection of the faction generated a request to server to obtain the light or dark classes while the result is perfectly known. We so suppress one layer (and one server-request) to obtain:
Code:
[div id="classes"][/div]
[div id="players"][/div]
where the 'classes' div contains all html/css code to display all classes of both light and dark faction.
the 'players' div is updated with an Ajax request to contain the top players of the class selected above.
3.3.2 The Faction Div
The new "pvp ranking" page is again feathery. To comply with our new view rules, we place all resources of this page in a dedicated sub-folder, the entry point is so an "index.php" page invoked by the Ajax loader of the main page:
In menu.php loaded by the top level index.php, you insert a link to 'pvp/':
we keep the same controller logic with the functions 'selectClass' and 'selectSide' but the implementation of 'selectSide' is 100% client-sided with two image-maps that define all needed requests.
The 'selectClass' stays as simple as previous model, it changes:
Code:
from
httpRequest("pvp_players.php?side=" + side + "&class=" + clazz, "players");
to
$('#players').load("pvp/ranks.php?side=" + side + "&class=" + clazz);
easy rewriting to use jQuery accessor (the $(#<id name>) one) and its Ajax wrapper (the load method of a tag recipient).
3.3.3 The Players Div
This block is build with the response of the pvp/ranks.php page. This page has access to the daatbases to read player's PvP stats.
ranks.php
HTML Code:
<!doctype html><?
// side & class parameters are required
if (!isset($_GET['side']) || !isset($_GET['class']))
die();
// get & check their values
$side = intval($_GET['side']);
$class = intval($_GET['class']);
if (''.$side != $_GET['side'] || ''.$class != $_GET['class'])
die();
if ($side < 0 || $side > 1 || $class < 0 || $class > 5)
die();
// build a table with the top 25 players matching the given side ('Country') and class ('Job')
// chars must also be not deleted ('Chars.Del=0) and not admin (Users_Master.Status=0)
$smt = 'SELECT TOP 25 c.CharName,c.Level,c.K1,c.K2,gu.GuildName '.
'FROM PS_GameData.dbo.Chars c '.
'JOIN PS_UserData.dbo.Users_Master m ON m.UserUID=c.UserUID '.
'JOIN PS_GameData.dbo.UserMaxGrow g ON g.UserUID=c.UserUID '.
'LEFT JOIN PS_GameData.dbo.GuildChars gc ON (gc.CharID=c.CharID and gc.Del=0) '.
'LEFT JOIN PS_GameData.dbo.Guilds gu ON gu.GuildID=gc.GuildID '.
'WHERE c.Del=0 AND g.Country=:cntr AND c.Job=:job '.
'ORDER BY c.K1 desc,c.K2,c.Level desc,c.CharName';
// or 'WHERE c.Del=0 AND m.Status=0 AND g.Country=:cntr AND c.Job=:job '.
// connect to database using a PDO
include('../inc/pdoConnect.php');
// setup the query, bind the parameter & execute the query
$query = $pdo->prepare($smt);
if ($query != null){
if ($query->bindParam(':cntr', $side, PDO::PARAM_INT) &&
$query->bindParam(':job', $class, PDO::PARAM_INT)){
if ($query->execute()){
echo "<table width='650' id='top_pvp'>\n<tr>\n";
echo "<th width= '50''>Rank</th>\n";
echo "<th width='200'>Character</th>\n";
echo "<th width='100'>Level</th>\n";
echo "<th width='100'>Kills</th>\n";
echo "<th width='200'>Guild</th>\n";
echo "</tr>\n";
// iterate rows
$i = 1;
while ($row = $query->fetch(PDO::FETCH_NUM)){
echo '<tr><td>'.$i++.'</td>';
// echo '<td>'.$row[0].'</td>';
echo '<td>Xxxxxx</td>';
echo '<td>'.$row[1].'</td>';
echo '<td>'.$row[2].'</td>';
$guild = $row[4];
if ($guild == null)
$guild = 'none';
echo '<td>'.$guild.'</td>';
echo "</tr>\n";
}
$query->closeCursor();
echo "</table>\n";
}
}
$query = null;
}
?>
This page is nearly the same than the one of the previous implementation, it is quite a pure PHP controller and had no reason to be changed. A change reflects the change from 'conn' to 'pdo' for the name of the global PDO instance. Another fixes an error in the request, the join with the GuildChars table must ensure that the row is still valid (CharID still in that guild) by checking the 'Del' field that shall be equal to '0' (it is settled to '1' when the player leaves the guild)
The selected rows are used to build a table similar to the previous one. The details specific to the applied view (layout & styles) can not be discuted there.
This section will provide the implementation of player-oriented web services.
These services require player authentication and manage characters and items of the player; security and reliability will be very important.
4.1 Account Creation
TBP
4.2 Player Authentication
Before offering any service to players (users, customers, ...) we must authenticate them.
They are several accounts schemas in the field, the easier (and not weakest one) relies on the sole Users_Master table defined in the PS_UserData database. It is the mandatory entry point since the game client pings the ps_login service which in turn invokes the usp_Try_GameLogin_Taiwan stored procedure. But that SP is known to have several versions, some store & compare plain passwords while others use digests (MD5 or SHA1) of these passwords. Some procedures also rely on another database (omg_gameweb), if your actual login process is tailored, adapt the code to follow.
The login page is a simple form with an User ID & Password fields.
This page is built to perform the following actions:
- upon initial verification attempt, it displays the form.
- upon form validation, it tries to authenticate player:
+ on success, it updates a session variable and redirect to the page that requested the authentication
+ on failure, the form is displayed for another attempt.
inc/login.php
HTML Code:
<?php
// enable session
if (session_id() == '')
session_start();
// (re)forget ID of logged-in user
unset($_SESSION['UserUID']);
unset($_SESSION['UserName']);
// get optional back link
if (!isset($back))
$back = '';
// processing result placeholder
$isLogged = false;
// check posted params
if (isset($_POST['name']) && isset($_POST['pass'])){
// process form validation
$name = $_POST['name'];
if (isset($_POST['link']))
$back = $_POST['link'];
include('pdoConnect.php'); // connect to database using a PDO
include('functions.php'); // controllers
// try to obtain unique identifier of player
$userUID = getPlayerUID($name, $_POST['pass']);
if ($userUID != 0){
// data were valid, store the UserUID (his/her numerical unique ID)
$_SESSION['UserUID'] = $userUID;
// also store UserID (his/her name) for user friendly msg
$_SESSION['UserName'] = $name;
// update processing state
$isLogged = true;
}
}
?><html><head><meta http-equiv="content-type" content="text/html; charset=UTF-8"><link rel="stylesheet" type="text/css" href="css/styles.css"><link rel="stylesheet" type="text/css" href="inc/login.css"></head><body><?
// if login was successful, load the caller page or the entry page
if ($isLogged){
if ($back != '')
echo "<script>\n$('#content').load('$back');\n</script>\n";
else
echo "<script>\nwindow.top.location.replace('index.php');\n</script>\n";
}
else {
?><div id="login"><form id="form"><h4>Authentification is required to gain access to personal data.</h4><p>Please type in your ID and password to login your game account.</p><label for="name" class="capt">User ID:</label><input id="name" name="name" type="text"/><br/><label for="pass" class="capt">Password:</label><input id="pass" name="pass" type="password"/><br/><br/><input type="submit" value="OK"/></form></div><script>
/* form submit validation */
$("#form").submit(function(e) {
e.preventDefault();
var request = $.ajax({
url: 'inc/login.php',
type: 'POST',
data: { name: $('#name').val(), pass: $('#pass').val(), link: '<?php echo $back ?>' },
dataType: 'html'
});
request.done(function(msg){
$("#content").html(msg);
});
request.fail(function(jqXHR, textStatus){
alert("login fail with: " + textStatus);
});
});
</script><?
}
?></html>
Regarding design:
- the styles relevant for that login page is defined in 'inc/login.css', the page loads it in header
- the page is loaded with an Ajax request into the '#content' div, so it can not submit itself without breaking the rules (default method will update the whole page), a jQuery-based script is used to define the submit function of the form.
- in that submit handler, an ajax request is built, one should note that the provided URL is relative to the top window and not the "source file used to define the div where this script was loaded" ... long description, the fact is that 'inc/login.php' loaded by '/index.php' (so current location is '/') shall reload itself as 'inc/login.php' and not 'login.php', the same logic will applies for all ajax-loaded pages.
- if another parameters shall be verified during the login - such as a captcha pattern - you will include it in the JS array 'data' present in the parameter of the ajax() call.
- finally, the access to the DB and the verification method is defined in the 'inc/functions.php' file.
The verification method is:
Code:
/**
* Get the unique identifier of a player with given account name & password
* @param name the account name of the player
* @param pass the account password of the player
* @return the UserUID field of the account or 0 in case of error
*/
function getPlayerUID($name, $pass){
// use global PDO instance
global $pdo;
if (empty($pdo))
die('pdo is null, pdoConnect.php must be included');
// result placeholder
$userUID = 0;
// setup a query to get information on player account
if (($query = $pdo->prepare('SELECT UserUID,Pw FROM PS_UserData.dbo.Users_Master WHERE UserID=?')) == null)
die(FormatErrors($query->errorInfo()));
// bind the parameter & execute the query
if ($query->bindParam(1, $name, PDO::PARAM_STR) && $query->execute()){
// get response (a single row is expected)
$row = $query->fetch(PDO::FETCH_NUM);
if ($row != null){
// UserID exists, get UID & password in temporary
$uid = $row[0];
$pwd = $row[1];
// make sure no other records were returned
// if another row is returned, the DB is corrupted and we stop login
if ($query->fetch(PDO::FETCH_NUM) == null){
// verify password, depending on actual DB you may have to verify a hash
if (''.$pwd == $pass){
// UserID / Pwd are valid, return the UserUID
$userUID = $uid;
}
}
}
$query->closeCursor();
}
$query = null;
return $userUID;
}
In this code, I choose to select the record with a matching UserID and to later compare the passwords in PHP.
After parameter binding, the SQL request is:
Code:
SELECT UserUID,Pw FROM PS_UserData.dbo.Users_Master WHERE UserID=$name
This way allows flexible tests. If the 'Pw' field (or another field to compare) is an hash, you want to compare values in PHP. Also if other fields shall be checked (like banned account) you will select them in the request and process them by code.
Still, it is of course possible to use a SQL request like:
Code:
SELECT UserUID FROM PS_UserData.dbo.Users_Master WHERE UserID=$name AND Pw=$pass
This second option does all the work, but it is not possible to handle invalid conditions. We got one ID or nothing, no way to know if the $name, $pass or both were invalid.
Upon successful verification, the caller page (whose URL was stored in a '$back' variable) is loaded into the '#content' div if it is defined, or the main entry page is loaded.
4.3 Reroll Items
4.3.1 Definition
Rerolling an item is achieved in game with the Professional Blacksmith. And since this is a statement, not a question, the topic could be considered as ended.
Nevertheless, OS is using some 'new' recreation runes to rebuild and/or to maximise one statictic, and not one among the 9 possibles (str/dex/rec/int/wis/luc/hp/mp/sp).
So we can ask how to implement a service that will also optimize specifically some statistics.
4.3.2 Modification of game (client & server)
Let's check the Nexon solution. Six items are defined, their Type is 101, TypeID is in range 1 up to 6 and they use an effect value ('Special' column) ranging 86 up to 91. Why wasting 6 effects instead of using 1 effect and the classical parameter stored in 'ReqVg'? Fortunately it is not the question (and we don't have to explain that this is a completely stupid choice). The relevant question for us is can we use the same principle? Definitely not.
Use the same method would require some patchs of the client to learn it to handle these items (with same Type/TypeID or another values) in one of the PB's dialogs. And strong modifications of the server to manage these new effects and modify specific stat accordingly. No way.
A web service can so be defined to allow the optimization of one chosen statistic.
The optimization can be to change it with a new random value while keeping the values of other statistics, or maximise the designated statistic. The token to allow the optimization will also be a specific recreation rune (to not reinvent the wheel) that come from in game loots or from item mall. As a basic scheme, if runes drop fairly / are cheap, they will be used to generate random values for the designated stat. If they are scare / expensive, they will change the orange stat to its maximum. These choices are yours, we will not comment hereafter the details of items drops or item mall.
Let's just suppose that we have defined 6 items with a 'Special' field set to 90 and a ReqVg field set to 1 up to 6. These items should be consumables, so with a Type in 25, 27, 28, 29, 38, 43, 44, 94 or 100. For their icons, the OS clients contain the re-colored rec runes, simply use them (they have indexes 180 & 189..193).
Define your rec runes with these small constraints (or prepare yourself to do some change in the code to follow) or find in the download section an Item.sdata file that contain only these 6 items, they use Type 100 and TypeID 181 to 186, the Drop Group is set to 440. To merge these items into your items definition, simply open your Item.sdata file with shStudio 0.7.1, make sure your items with same Type/TypeID are not used for something useful, select the 6 lines in the 'All Items' panel (Type = 100, TypeID = 181 up to 186) and delete (!) them (do not 'Erase' but 'Delete' them, sole case where it's safe to remove lines, so enjoy), now choose 'File' / 'Import...' and select the downloaded file. The 6 items are inserted to your file (existing items are not replaced / updated if they exist, that is why we had deleted the existing items).
At server side, simply update the Items database with the following script.
Code:
UPDATE PS_GameDefs.dbo.Items SET ItemName='Recreation Rune STR', Country=6, Special=90, ReqVg=1, Buy=10000, Sell=1000, Grade=440, Count=255 WHERE ItemID=100181;
UPDATE PS_GameDefs.dbo.Items SET ItemName='Recreation Rune DEX', Country=6, Special=90, ReqVg=2, Buy=10000, Sell=1000, Grade=440, Count=255 WHERE ItemID=100182;
UPDATE PS_GameDefs.dbo.Items SET ItemName='Recreation Rune REC', Country=6, Special=90, ReqVg=3, Buy=10000, Sell=1000, Grade=440, Count=255 WHERE ItemID=100183;
UPDATE PS_GameDefs.dbo.Items SET ItemName='Recreation Rune INT', Country=6, Special=90, ReqVg=4, Buy=10000, Sell=1000, Grade=440, Count=255 WHERE ItemID=100184;
UPDATE PS_GameDefs.dbo.Items SET ItemName='Recreation Rune SAG', Country=6, Special=90, ReqVg=5, Buy=10000, Sell=1000, Grade=440, Count=255 WHERE ItemID=100185;
UPDATE PS_GameDefs.dbo.Items SET ItemName='Recreation Rune LUC', Country=6, Special=90, ReqVg=6, Buy=10000, Sell=1000, Grade=440, Count=255 WHERE ItemID=100186;
Again, if you choose a different Type/TypeID or Drop Group ('Grade')(possibly 99 to prevent drop), change the script.
So far, so good, you have some new tokens, and you have define a way for players to get them (NPC or drop or item mall).
Of course, we can not use them with the Professional Blacksmith, but we already known that.
4.3.3 Character Management (1st version)
4.3.3.1 Player Authentication
The first version of the reroll pages presented there was coded before the definition of the current framework.
It used a specific login page with an interface similar to the client game.
<?
// UserUID shall exist, if not defined redirect to login page
session_start();
if (!isset($_SESSION['UserUID'])){
header("Location: login.php");
exit;
}
$uid = $_SESSION['UserUID'];
?><!doctype html><html><head><meta http-equiv="content-type" content="text/html; charset=UTF-8"><title>Shaiya Web Services</title><link rel="stylesheet" type="text/css" href="./css/styles.css"><link rel="stylesheet" type="text/css" href="./css/chars.css"></head><body><div id="container"><div id="contents"><table cellpadding=0 cellspacing=0><tr><td colspan=3 width="538" id="header">Character Management</td></tr><tr><td width="13"><img src="./png/left.png" width="13" class="borders"></td><td width="513" class="inside"><label for="char" class="capt">Chars:</label><select id="char" style="width: 326px;"><option value='0'>--</option><?
// connect to database using a PDO
include('./inc/pdoConnect.php');
// setup a query to get chararcters of logged in player
$query = $conn->prepare('SELECT CharID,CharName FROM PS_GameData.dbo.Chars WHERE UserUID=? ORDER BY CharName');
if ($query != null){
// bind the parameter & execute the query
if ($query->bindValue(1, $uid, PDO::PARAM_INT) && $query->execute()){
// iterate all records in response
while ($row = $query->fetch(PDO::FETCH_NUM)){
echo " <option value='$row[0]'>$row[1]</option>\n";
}
}
// dispose of query
$query->closeCursor();
$query = null;
}
$conn = null;
?>
</select></td><td width="12"><img src="./png/right.png" width="12" class="borders"></td></tr><tr><td colspan=3 width="538" id="footer"></td></tr></table></div></div></body></html>
At beginning of the page, we check the context with the 'UserUID' session variable. If it isn't defined, the page was loaded directly, we so abort and redirect the browser to the login.php page.
Code:
<?
session_start();
if (!isset($_SESSION['UserUID'])){
header("Location: login.php");
exit;
}
?>
Otherwise, we define a table centered in the page. Most of the work is done by css.
The characters of the authenticated player are selected from the PS_GameData.Chars table and are used to fill in a combo-list.
Code:
// connect to database using a PDO
include('./inc/pdoConnect.php');
// setup a query to get chararcters of logged in player
$query = $conn->prepare('SELECT CharID,CharName FROM PS_GameData.dbo.Chars WHERE UserUID=? ORDER BY CharName');
if ($query != null){
// bind the parameter & execute the query
if ($query->bindValue(1, $uid, PDO::PARAM_INT) && $query->execute()){
// iterate all records in response
while ($row = $query->fetch(PDO::FETCH_NUM)){
echo " <option value='$row[0]'>$row[1]</option>\n";
}
}
// dispose of query
$query->closeCursor();
$query = null;
}
$conn = null;
The logic will always be the same:
The connection is established by the pdoConnect.php file (it allows to have ID/password in a single file)
A SQL Query is prepared with the prepare() method that receive a parametrized request as argument, all input fields are defined by a '?' mark (a ':name' notation is also possible)
All input parameters are binded, by value (using the bindValue() method) or by reference (using the bindParam() method)
The query is executed (execute() method), note that a query can be executed more than once, it's one of the advantage of these parametrized queries: they are compiled at SQL server side and can be used several time w/o additional analysis.
You have the list of characters, now you must load some information when a character is selected from the combo-list.
4.3.3.3 Minimal workload for Ajax requests
They are several ways to do that. The old fashion way is to post a form and to reload a full page with the same data (list of chars) plus some data related to the selected char. A lighter way is to post such request in a embedded / hidden frame (an 'iframe') to avoid to reload the common part of the page, but iframe have some security concerns. A modern way to process is an AJAX ('Asynchronous JavaScript and XML') request, meaning that the http request and the processing of the response with be handled by some JavaScript code.
It will be the purpose of the 'httpRequests.js' file.
httpRequests.js
Code:
function httpRequest(url, div) {
// request placeholder
var request = null;
// instantiate http-request
if (typeof window.ActiveXObject != 'undefined'){
try {
if (request == null)
request = new ActiveXObject("Microsoft.XMLHTTP");
}
catch (e) {
}
try {
if (request == null)
request = new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e) {
}
}
else if (window.XMLHttpRequest){
request = new XMLHttpRequest();
if (request && request.overrideMimeType)
request.overrideMimeType('text/xml');
}
if (request == null)
return false;
// on success, store the ouput div & set completion method
request.output = div;
request.onreadystatechange = function() { processReply(request); };
// setup & send the http request
request.open('GET', url, true);
request.send(null);
return true;
}
function processReply(aRequest){
try {
// does end of processing reached?
if (aRequest.readyState == 4){
// does process OK?
if (aRequest.status == 200){
try {
// if an output div was defined sets its content
var div = aRequest.output;
if (div != 'undefined' && div != null){
if (typeof div == 'string')
div = document.getElementById(div);
if (div != null && typeof div == 'object')
div.innerHTML = aRequest.responseText;
}
else {
// no output div container was given, response is assumed
// to be a JS script to be executed
var src = aRequest.responseText;
var script = document.createElement('script');
script.type = "text/javascript";
script.innerHTML = src;
document.getElementsByTagName('head')[0].appendChild(script);
}
}
catch (e){
alert("Error: " + e);
}
}
else {
alert("HttpRequest returns error: " + aRequest.status);
}
}
}
catch (e){
}
}
This file provides helper functions to manage the http requests.
httpRequest(url, div) setup and send the request.
url is a full, domain qualified, URL
div is a div element (as obtained by getElementById), or the 'id' of a div element, or null
the method stores the optional div reference, sets a generic callback method (the processReply() function, that we will never call ourselves) and sends the request.
Upon reception of server response, the XMLHttpRequest class (as per W3C or the MS ActiveX) invokes our method and forwards the HTTP server response. The 'AJAX' acronym defines 'X' as 'XML' but you can use it for any kind of server's response, not only XML, and in our case, it will never be XML stream.
Instead we manage two cases, the requested pages will be plain html code or JavaScript code.
In first case, the html code is used to add some content to the page, a valid 'div' parameter (a div element or the id of an existing div) is expected and the innerHTML property of this div will be defined with the received code.
In second case, you want to receive some JS code and we want it to be executed. We need to be smart here. If the received page contains some '<script> ... </script>' blocks and if you assign the full code to a div, the scripts won't be executed. Any html content will be rendered but the scripts and not processed. The global scripting context of the page is not aware of these new scripts so nobody will launch them. On other hands, these new scripts are not aware of the global context and thus they can not invoke an existing function sof the page to be invoked back. The solution is to perform 'code injection' (again). The page sent by the server shall contain script only (the requested URL can be a .js file, but also a .php file is the script need to be built at server side) and that script will be added to the page header. Using this trick, the new code is able to trigger itself, for instance with a window.setTimeout() call.
For a lot of cases, the two approachs are similar. For instance having:
The loadTheContentOfCharacterDetails function will perform a request to obtain plain html code used to define the inner content of the CharacterDetails div.
Here I will design the page with sevral div blocks that need to interact between them, so I prefer to be able to manage pure script code. But still, there never was a unique way to define / code / manage a page, so use this method if it is useful and use yours if they are better.
Last remark, even when you expect a JS script in the server response, you can manage it as a text-stream, and it is very helpful to debug some server-side generated scripts. To display the full script and debug them, define a <pre id='output'></pre> block and use a reference to that block or the 'output' string as the second argument of the httpRequest() function.
4.3.3.4 Display of Recreation Runes
The first thing we want to manage is the recreation runes owned by the selected character.
We need to provide visual feedback for the rec runes, a div with icons coming from icon_somo2.dds will do the job.
#runes {
/* the block containing the 6 rec runes at right of table */
margin: 0;
padding: 0;
position: absolute;
left: 469px; /* div is 44px width, container is (538-12-13)*/
top: 42px; /* = height of table's header */
width: 44px; /* height of icons (32px) + right margin (12px) */
height: 260px; /* same as L/R img's height */
}
.rec {
/* common properties for recreation runes */
background: transparent url('../png/recrunes.png') no-repeat 0 0;
cursor: pointer;
font: bold 11pt Verdana, Arial, Helvetica, sans-serif;
text-align: center;
line-height: 32px; /* so that text is v-centered on the img */
position: absolute;
left: 0px; height: 32px; width: 32px;
}
/* individual recreation runes, define actual top of Div and offset of image */
#recStr { top: 10px; background-position: 0 0; }
#recDex { top: 52px; background-position: -128px 0; }
#recRec { top: 94px; background-position: -32px 0; }
#recInt { top: 136px; background-position: -64px 0; }
#recWis { top: 178px; background-position: -96px 0; }
#recLuc { top: 220px; background-position: -160px 0; }
The (unique) id(entifier) "runes" setup a block at right side of the table. The "rec" class, valid for all kind of runes, defines the common properties (including image prodiving icons and text appareance) and the "recXXX" identifiers define exact position and portion of image for each rune.
We also need server-side code to read the number of available rec runes. Such script must (also) be protected against any attacks, including attempts de read data from not legitime person. We have to verify that a valid player is currently logged in, that information related to a character is given only if this character belong to this player, and of course protect all SQL requests against injection.
Here is a way to read the rec runes owned by the selected character.
getCharDetails.php (version 1)
Code:
<?
header("Cache-Control: no-cache");
session_start();
// this page is invoked by getChars.php, $_SESSION['UserUID'] shall exist
// and a 'charID' parameter, belonging to this UserUID shall be provided.
if (!isset($_SESSION['UserUID']) || !isset($_GET['charID']))
die();
$uid = $_SESSION['UserUID'];
$charID = intval($_GET['charID']);
// easy test on numerical value validity
if (''.$charID != $_GET['charID'])
die();
// verify that CharID is owned by the currently logged in player
$isCharValid = false;
include('./inc/pdoConnect.php');
// setup a query to get information on player account
$query = $conn->prepare('SELECT UserUID FROM PS_GameData.dbo.Chars WHERE CharID=?');
if ($query != null){
// bind the parameter & execute the query
if ($query->bindParam(1, $charID, PDO::PARAM_INT)){
if ($query->execute()){
// get response
$row = $query->fetch(PDO::FETCH_NUM);
// verify ownership of CharID
if ($row != null && $row[0] == $uid){
$isCharValid = true;
}
}
}
$query->closeCursor();
$query = null;
}
if (!$isCharValid)
die();
// at this point, context & CharID are valid
?>
function defineRecRunes(){
<?
// runes information placeholder
$qty = array(0 => '0', 1 => '0', 2 => '0', 3 => '0', 4 => '0', 5 => '0');
// get the rec. runes owned by this Char
// (the runes are defined as Special=90 and RegVg in 1..6)
$smt = 'SELECT it.ReqVg,sum(ci.Count) '.
'FROM PS_GameData.dbo.CharItems ci '.
'JOIN PS_GameDefs.dbo.Items it ON it.ItemID=ci.ItemID '.
'WHERE ci.CharID=? and it.Special=90 and it.ReqVg between 1 and 6 '.
'GROUP BY it.ReqVg';
// setup the query, bind the parameter & execute the query
$query = $conn->prepare($smt);
if ($query != null){
if ($query->bindParam(1, $charID, PDO::PARAM_INT)){
if ($query->execute()){
// read the count of each runes
while ($row = $query->fetch(PDO::FETCH_NUM)){
$type = $row[0]; // type as a number in 1..6
$cnt = $row[1]; // sum of all runes of this type
$qty[$type - 1] = $cnt;
}
}
}
$query->closeCursor();
$query = null;
}
// update content of owner page
?>
document.getElementById('recStr').innerHTML = '<?= $qty[0] ?>';
document.getElementById('recDex').innerHTML = '<?= $qty[1] ?>';
document.getElementById('recRec').innerHTML = '<?= $qty[2] ?>';
document.getElementById('recInt').innerHTML = '<?= $qty[3] ?>';
document.getElementById('recWis').innerHTML = '<?= $qty[4] ?>';
document.getElementById('recLuc').innerHTML = '<?= $qty[5] ?>';
}
function execScripts(){
defineRecRunes();
}
window.setTimeout(execScripts, 100);
<?
$conn = null;
?>
As previously explained, the page conatins only script. It is requested as a .php page (and not a .js file) to allow reception of parameter by the server (the 'CharID' field) and dynamic processing of the response. The PHP instructions are inserted and managed so that a valid JS script is always returned (except for attack detection).
So far, so good, the players will be able to login in with their account details. They can select one of their characters and the number of available rec runes of this character is displayed. We now need to do something with these runes.
We will implement a selection and display of the weapons owned by the selected character (those present in its inventory, not those in the account's warehouse) and manage the reconstruction of them. One can follow the same principles to manage reconstruction of gears.
4.3.3.5 Display of Weapons Details
A few more html code. A second combo-list to manage the weapons:
The getCharDetails.php file is modified to retreive information on weapons and populate the weapons list.
(The returned page is still a .js script)
getCharDetails.php
Code:
<?
header("Cache-Control: no-cache");
session_start();
// this page is invoked by getChars.php, $_SESSION['UserUID'] shall exist
// and a 'charID' parameter, belonging to this UserUID shall be provided.
if (!isset($_SESSION['UserUID']) || !isset($_GET['charID']))
die();
$uid = $_SESSION['UserUID'];
$charID = intval($_GET['charID']);
// easy test on numerical value validity
if (''.$charID != $_GET['charID'])
die();
// verify that CharID is owned by the currently logged in player
$isCharValid = false;
include('./inc/pdoConnect.php');
// setup a query to get information on player account
$query = $conn->prepare('SELECT UserUID FROM PS_GameData.dbo.Chars WHERE CharID=?');
if ($query != null){
// bind the parameter & execute the query
if ($query->bindParam(1, $charID, PDO::PARAM_INT)){
if ($query->execute()){
// get response
$row = $query->fetch(PDO::FETCH_NUM);
// verify ownership of CharID
if ($row != null && $row[0] == $uid){
$isCharValid = true;
}
}
}
$query->closeCursor();
$query = null;
}
if (!$isCharValid)
die();
// at this point, context & CharID are valid
// 1. retreive info on rec runes and display their numbers
?>
function defineRecRunes(){
<?
// runes information placeholder
$qty = array(0 => '0', 1 => '0', 2 => '0', 3 => '0', 4 => '0', 5 => '0');
// get the rec. runes owned by this Char
// (the runes are defined as Special=90 and RegVg in 1..6)
$smt = 'SELECT it.ReqVg,sum(ci.Count) '.
'FROM PS_GameData.dbo.CharItems ci '.
'JOIN PS_GameDefs.dbo.Items it ON it.ItemID=ci.ItemID '.
'WHERE ci.CharID=? and it.Special=90 and it.ReqVg between 1 and 6 '.
'GROUP BY it.ReqVg';
// setup the query, bind the parameter & execute the query
$query = $conn->prepare($smt);
if ($query != null){
if ($query->bindParam(1, $charID, PDO::PARAM_INT)){
if ($query->execute()){
// read the count of each runes
while ($row = $query->fetch(PDO::FETCH_NUM)){
$type = $row[0]; // type as a number in 1..6
$cnt = $row[1]; // sum of all runes of this type
$qty[$type - 1] = $cnt;
}
}
}
$query->closeCursor();
$query = null;
}
// update content of owner page
?>
document.getElementById('recStr').innerHTML = '<?= $qty[0] ?>';
document.getElementById('recDex').innerHTML = '<?= $qty[1] ?>';
document.getElementById('recRec').innerHTML = '<?= $qty[2] ?>';
document.getElementById('recInt').innerHTML = '<?= $qty[3] ?>';
document.getElementById('recWis').innerHTML = '<?= $qty[4] ?>';
document.getElementById('recLuc').innerHTML = '<?= $qty[5] ?>';
}
<?
// 2. manage weapons owned by this Char, build & run a SQL query to
// dynamically build a JS script that will update the 'Weapons' combo-list
?>
function defineWeapons(){
try {
// gain access to weapons combo-list
var select = document.getElementById('wpns');
// empty it
while (select.length > 0)
select.remove(0);
// insert blank record
var opt = document.createElement("option");
opt.value = 0;
opt.text = '--';
select.add(opt);
// insert weapons
<?
// retreive weapons of given char. (get rowID from CharItems, generic name & level from Items)
$smt = 'SELECT ci.RowID,it.ItemName,it.ReqLevel '.
'FROM PS_GameData.dbo.CharItems ci '.
'JOIN PS_GameDefs.dbo.Items it ON it.ItemID=ci.itemID '.
'WHERE ci.CharID=? AND '.
'((ci.Type BETWEEN 1 AND 15) OR (ci.Type BETWEEN 45 AND 65) OR ci.Type IN (19,34,69,84)) '.
'ORDER BY it.ReqLevel,it.ItemName';
// setup the query, bind the parameter & execute the query
$query = $conn->prepare($smt);
if ($query != null){
if ($query->bindParam(1, $charID, PDO::PARAM_INT)){
if ($query->execute()){
// build JS code to insert all weapons into the combo
while ($row = $query->fetch(PDO::FETCH_NUM)){
?>
var opt = document.createElement("option");
opt.value = <?= $row[0] ?>;
opt.text = "<?= $row[1] ?> (lvl.<?= $row[2] ?>)";
select.add(opt);
<?
}
}
}
$query->closeCursor();
$query = null;
}
?>
}
catch (e){
alert("Error: " + e);
}
}
function execScripts(){
defineRecRunes(); // update rec runes divs
defineWeapons(); // update weapons list
// false weapon selection to force refresh of stats divs
selectWeapon(document.getElementById('wpns'));
}
window.setTimeout(execScripts, 100);
<?
$conn = null;
?>
Such code contains both instruction executed at server-side (the PHP part) and some JS script executed at client-side. And of course, we use PHP code to build the JS code. Errors are frequent, mainly typo, since both instructions flows are interleaved. As indicated above, verify the writing of the JS code with a reference to a PRE block in the call to httpRequest() can help.
Also note that the script gets only name and ID of the weapons. We can imagine to also retreive all static stats and current orange stats in the same request and store all this information in tailored properties of each created option element. If we do so, we will be able to display information on selected weapon without additional request but you will have to update these properties of the option elements when a weapon is reconstructed.
Since we prefer a request on weapon-selection, we have to provide a script to do that:
getWeaponDetails.php
Code:
<?
header("Cache-Control: no-cache");
session_start();
// this page is invoked by getChars.php, $_SESSION['UserUID'] shall exist and
// an 'itemID' describing an item of a character of this UserUID shall be provided.
if (!isset($_SESSION['UserUID']) || !isset($_GET['itemID']))
die();
$uid = $_SESSION['UserUID'];
$itemID = intval($_GET['itemID']);
// easy test on numerical value validity
if (''.$itemID != $_GET['itemID'])
die();
// connect to database using a PDO
include('./inc/pdoConnect.php');
// utilities to extract orange stats
include('./inc/craftname.php');
?>
function setWeaponInfo(){
<?
// retreive info on given weapon: get orange stats (craftname) from CharItems,
// static statictics from Items and player ID from Chars.
$row = null;
$smt = 'SELECT it.ConstStr,it.ConstDex,it.ConstRec,it.ConstInt,it.ConstWis,it.ConstLuc,'.
'ci.Craftname,ch.UserUID '.
'FROM PS_GameData.dbo.CharItems ci '.
'JOIN PS_GameDefs.dbo.Items it ON it.ItemID=ci.itemID '.
'JOIN PS_GameData.dbo.Chars ch ON ch.CharID=ci.CharID '.
'where ci.RowID=?';
// setup the query, bind the parameter & execute the query
$query = $conn->prepare($smt);
if ($query != null){
if ($query->bindParam(1, $itemID, PDO::PARAM_INT)){
if ($query->execute()){
// get SQL response
$row = $query->fetch(PDO::FETCH_NUM);
// if $row is not null, it means that itemID was valid but we must verify
// that the item is owned by a character of the currently logged in player
if ($row != null);{
// verify ownership
if ($row[7] == $uid){
// build JS code to update the 6 static stats with the ConstXXX columns
for ($i = 0; $i < 6; $i++){
?>
document.getElementById('base<?= $i ?>').value = <?= $row[$i] ?>;
<? }
// get Craftname column & update the 6 orange stats
$craft = $row[6];
for ($i = 0; $i < 6; $i++){
?>
document.getElementById('orng<?= $i ?>').value = <?= getStats($craft, $i * 2) ?>;
<? }
}
}
}
}
$query->closeCursor();
$query = null;
}
$conn = null;
?>
}
window.setTimeout(setWeaponInfo, 100);
This script is obtained by another http-request each time a new weapon is selected from the combo-list:
(from getChars.php)
HTML Code:
function selectWeapon(select){
var choice = select.selectedIndex;
if (choice == 0){
for (var i = 0; i < 6; i++){
document.getElementById('base' + i).value = '0';
document.getElementById('orng' + i).value = '';
}
}
else {
var option = select.options[choice];
httpRequest("getWeaponDetails.php?itemID=" + option.value, null);
}
}
We now have:
We are quite ready to recreate the orange stats.
4.3.3.6 Recreation
We first add a click handler on the rec runes icons to trigger the required action.
function reroll(aDiv, id, name){
// perform some controls at client side, stronger controls will be done at server side too
var count = parseInt(aDiv.innerHTML);
if (isNaN(count) || typeof count != 'number' || count < 1){
alert('You need a recreation rune to process.');
return;
}
var select = document.getElementById('wpns');
if (select.selectedIndex == 0){
alert('A weapon must be selected to process.');
return;
}
if (!confirm('The stat ' + name + ' will be set to its maximum.\nDo you want to process?'))
return;
httpRequest('setItemStats.php?itemID=' + select.options[select.selectedIndex].value + '&stat=' + id, null);
}
<?
// UserUID shall exist, if not defined redirect to login page
session_start();
if (!isset($_SESSION['UserUID'])){
header("Location: login.php");
exit;
}
$uid = $_SESSION['UserUID'];
?><!doctype html><html><head><meta http-equiv="content-type" content="text/html; charset=UTF-8"><title>Shaiya Web Services</title><link rel="stylesheet" type="text/css" href="./css/styles.css"><link rel="stylesheet" type="text/css" href="./css/chars.css"><script language="JavaScript" src="./inc/httpRequests.js"></script></head><body><script language="javascript">
function getCharDetails(select){
var charID = parseInt(select.options[select.selectedIndex].value);
if (!isNaN(charID) && charID > 0)
httpRequest("getCharDetails.php?charID=" + charID, null);
}
function selectWeapon(select){
var choice = select.selectedIndex;
if (choice == 0){
for (var i = 0; i < 6; i++){
document.getElementById('base' + i).value = '0';
document.getElementById('orng' + i).value = '';
}
}
else {
httpRequest("getWeaponDetails.php?itemID=" + select.options[choice].value, null);
}
}
function reroll(aDiv, id, name){
// perform some controls at client side, stronger controls will be done at server side too
var count = parseInt(aDiv.innerHTML);
if (isNaN(count) || typeof count != 'number' || count < 1){
alert('You need a recreation rune to process.');
return;
}
var select = document.getElementById('wpns');
if (select.selectedIndex == 0){
alert('A weapon must be selected to process.');
return;
}
if (!confirm('The stat ' + name + ' will be set to its maximum.\nDo you want to process?'))
return;
httpRequest('setItemStats.php?itemID=' + select.options[select.selectedIndex].value + '&stat=' + id, null);
}
</script><div id="container"><div id="contents"><table cellpadding=0 cellspacing=0><tr><td colspan=3 width="538" id="header">Character Management</td></tr><tr><td width="13"><img src="./png/left.png" width="13" class="borders"></td><td width="513" class="inside"><label for="char" class="capt">Chars:</label><select id="char" style="width: 326px;" onChange="getCharDetails(this);"><option value='0'>--</option><?
// connect to database using a PDO
include('./inc/pdoConnect.php');
// setup a query to get chararcters of logged in player
$query = $conn->prepare('SELECT CharID,CharName FROM PS_GameData.dbo.Chars WHERE UserUID=? ORDER BY CharName');
if ($query != null){
// bind the parameter & execute the query
if ($query->bindValue(1, $uid, PDO::PARAM_INT) && $query->execute()){
// iterate all records in response
while ($row = $query->fetch(PDO::FETCH_NUM)){
echo " <option value='$row[0]'>$row[1]</option>\n";
}
}
// dispose of query
$query->closeCursor();
$query = null;
}
$conn = null;
?>
</select><br/><label for="wpns" class="capt">Weapons:</label><select id="wpns" style="width: 326px;" onChange="selectWeapon(this);"><option value='0'>--</option></select><br/><br/><div id="stats"><label for="base0" class="stat">STR:</label><input type="text" id="base0" readonly value="0" /><input type="text" id="orng0" readonly value="" class="orange" /><label for="base1" class="stat">DEX:</label><input type="text" id="base1" readonly value="0" /><input type="text" id="orng1" readonly value="" class="orange" /><label for="base2" class="stat">REC:</label><input type="text" id="base2" readonly value="0" /><input type="text" id="orng2" readonly value="" class="orange" /><br /><label for="base3" class="stat">INT:</label><input type="text" id="base3" readonly value="0" /><input type="text" id="orng3" readonly value="" class="orange" /><label for="base4" class="stat">WIS:</label><input type="text" id="base4" readonly value="0" /><input type="text" id="orng4" readonly value="" class="orange" /><label for="base5" class="stat">LUC:</label><input type="text" id="base5" readonly value="0" /><input type="text" id="orng5" readonly value="" class="orange" /></div><div id="runes"><div class="rec" id="recStr" onclick="reroll(this, 1, 'Str');"></div><div class="rec" id="recDex" onclick="reroll(this, 2, 'Dex');"></div><div class="rec" id="recRec" onclick="reroll(this, 3, 'Rec');"></div><div class="rec" id="recInt" onclick="reroll(this, 4, 'Int');"></div><div class="rec" id="recWis" onclick="reroll(this, 5, 'Wis');"></div><div class="rec" id="recLuc" onclick="reroll(this, 6, 'Luc');"></div></div></td><td width="12"><img src="./png/right.png" width="12" class="borders"></td></tr><tr><td colspan=3 width="538" id="footer"></td></tr></table></div></div></body></html>
setItemStats.php will now update the weapon whose RowID is the given 'itemID' param to set the statistic designated by 'stat' to its maximum. (It is also possible to update it with a random value as explained in introduction of this chapter.)
The reconstruction implies several points: first being sure, once again, that the weapon belongs to a character of the logged in player. Second make sure that the character does have the required rec rune, not some time ago when it was selected from the char. combo but when the operation is performed. Then verify that the weapon suppports orange stats. And finally and not least, make sure that the player is not currently logged in in-game; if we don't verify this point, our update can be lost, or the player can try to cheat.
The status of account is managed by the PS_UserData.Users_Master table with the 'Leave' field, when a player is logged in, the field contains 1, if it is not logged in, it contains 0.
We define the following function in pdoConnect.php.
Code:
// test whether a player is connected
// @return 1: connected, 0: not connected, -1: does not exist or error
function getPlayerStatus($conn, $userUID){
// define query
$query = $conn->prepare('SELECT Leave FROM PS_UserData.dbo.Users_Master WHERE UserUID=?');
// setup & execute query
if ($query == null ||
!$query->bindValue(1, $userUID, PDO::PARAM_INT) ||
!$query->execute())
return -1;
// get response & verify status (Leave is '1' when user is connected)
if (($row = $query->fetch(PDO::FETCH_NUM)) == null)
return -1;
$status = $row[0];
// release resource
$query->closeCursor();
$query = null;
// return player status
return $status;
}
We are now ready to reroll item:
setItemStats.php
Code:
<?
header("Cache-Control: no-cache");
session_start();
// this page is invoked by getChars.php, $_SESSION['UserUID'] shall exist
// and the parameters 'itemID' & 'stat' shall be provided.
if (!isset($_SESSION['UserUID']) || !isset($_GET['itemID']) || !isset($_GET['stat']))
die();
$userUID = $_SESSION['UserUID'];
$itemID = intval($_GET['itemID']);
$stat = intval($_GET['stat']);
// easy test on numerical values validity
if (''.$itemID != $_GET['itemID'] || ''.$stat != $_GET['stat'] || $stat < 1 || $stat > 6)
die();
// helper function to exit with a valid JS script
function haltWithAlert($msg){
echo " alert(\"$msg\");\n";
echo "}\n";
die();
}
// define the client JS script
?>
window.setTimeout(rerollItem, 100);
function rerollItem(){
try {
<?
// connect to database using a PDO
include('./inc/pdoConnect.php');
// get craft utilities
include('./inc/craftname.php');
// player shall not be logged in
$stts = getPlayerStatus($conn, $userUID);
if ($stts == -1)
haltWithAlert('Invalid process.');
if ($stts == 1)
haltWithAlert('You must log off to allow the operation.');
// user exists and [s]he's not logged in
$charID = 0; // id of char
$maxVal = 0; // max. value for the orange bonus
$craft = ''; // craft info
$recID = 0; // id of a rec rune
$recCnt = 0; // number of runes
// get Craftname of designated weapon to allow its update and max. orange stat,
// CharID's owner of this item and UserUID of CharID's owner
$smt = 'SELECT ch.UserUID,ch.CharID,it.ReqWis,ci.Craftname '.
'FROM PS_GameData.dbo.CharItems ci '.
'JOIN PS_GameDefs.dbo.Items it on it.ItemID=ci.ItemID '.
'JOIN PS_GameData.dbo.Chars ch on ch.CharID=ci.CharID '.
'WHERE ci.RowID=?';
$query = $conn->prepare($smt);
if ($query != null){
if ($query->bindValue(1, $itemID, PDO::PARAM_INT)){
if ($query->execute()){
$row = $query->fetch(PDO::FETCH_NUM);
if ($row != null){
// verify ownership
if ($row[0] == $userUID){
// store owner, max. value of orange stat & current craft
$charID = $row[1]; // id of Char that must have rec rune
$maxVal = $row[2]; // reqWis in DB
$craft = normaliseCraft($row[3]); // get a valid craft string
}
$row = null;
}
}
}
$query->closeCursor();
$query = null;
}
// charID is still zero is the process failed / was invalid
if ($charID == 0)
haltWithAlert('The operation is not allowed. (1)');
// stop if orange stats are not defined
if ($maxVal < 1)
haltWithAlert('The selected item does not support orange stats.');
// verify availability of rec rune
$smt = 'SELECT ci.RowID,ci.Count '.
'FROM PS_GameData.dbo.CharItems ci '.
'JOIN PS_GameDefs.dbo.Items it ON it.ItemID=ci.ItemID '.
'WHERE ci.CharID=? AND ci.Count>0 AND it.Special=90 AND it.ReqVg=?';
$query = $conn->prepare($smt);
if ($query != null){
if ($query->bindValue(1, $charID, PDO::PARAM_INT) && $query->bindValue(2, $stat, PDO::PARAM_INT)){
if ($query->execute()){
$row = $query->fetch(PDO::FETCH_NUM);
if ($row != null){
// store rowID of rec rune & its count
$recID = $row[0];
$recCnt = $row[1];
}
}
}
$query->closeCursor();
$query = null;
}
// recID is still zero is the process failed / was invalid
if ($recID == 0)
haltWithAlert('The operation is not allowed. (2)');
// double check the number of rec runes
if ($recCnt < 1)
haltWithAlert('You do not have the required rune.');
// define new craftname field (set designated stat to the provided maximum)
$craft = setStats($craft, ($stat - 1) * 2, $maxVal);
// update item & remove 1 rec rune in a transaction
// disable auto-commit
$conn->beginTransaction();
// update item
$smt = 'UPDATE PS_GameData.dbo.CharItems '.
"SET Craftname=? ".
'WHERE RowID=?';
$query = $conn->prepare($smt);
$success = ($query != null);
if ($success)
$success = ($query->bindValue(1, $craft, PDO::PARAM_STR) && $query->bindValue(2, $itemID, PDO::PARAM_INT));
if ($success)
$success = $query->execute();
$query = null;
// remove one rec rune
if ($recCnt > 1)
$smt = 'UPDATE PS_GameData.dbo.CharItems SET Count=Count-1 '.
'WHERE RowID=?';
else
$smt = 'DELETE FROM PS_GameData.dbo.CharItems '.
'WHERE RowID=?';
if ($success)
$success = (($query = $conn->prepare($smt)) != null);
if ($success)
$success = $query->bindValue(1, $recID, PDO::PARAM_INT);
if ($success)
$success = $query->execute();
$query = null;
// make sure user is still not logged in
if ($success)
$success = (getPlayerStatus($conn, $userUID) == 0);
if ($success){
// commit all changes
$conn->commit();
// update number of rec runes
$statDivs = array(
1 => 'recStr', 2 => 'recDex', 3 => 'recRec',
4 => 'recInt', 5 => 'recWis', 6 => 'recLuc');
echo " document.getElementById('".$statDivs[$stat]."').innerHTML = \"".($recCnt - 1)."\";\n";
// update seelcted weapon
echo " selectWeapon(document.getElementById('wpns'));\n";
}
else {
// cancel changes
$conn->rollBack();
echo " alert('The operation can not be performed.');\n";
}
$conn = null;
?>
}
catch (e){
alert("Error: " + e);
}
}
The system presented in previous chapter can be enhanced.
First, the coding can (shall) be better organised. It is always important to split the code responsible of the layout (the 'view' part) and the code responsible of the processing of data (input & output). Some pages were clean, others was not. We will clean them.
Second, the system doesn't manage regular rec. runes to provide random stats. We will add this feature.
More generally, the following points must be addressed:
- the view description must contains as few as possible code to allow easy changes (so that designer will be able to focus on styles & layout, not on confusing code or scripts)
- and grouping of all functions managing SQL requests in a library.
For the functionnality, the generation of random stats must be added.
For the layout, the maximal value for OJ stats must be displayed so that player can serenely choose whether or not rebuilt his/her item.
4.3.4.1 Player Authentication
Of course, the player must login prior to access the service. Thanks to the generic system presented in chapter 4.1, the 'reroll/index.php' page will solve this with:
Code:
<?php
// enable session
session_start();
// if user is not logged-in, load login page
if (empty($_SESSION['UserUID'])){
$back = 'reroll/';
include '../inc/login.php';
}
else {
// player is logged-in
// do something
}
?>
If no session variable 'UserUID' exists, no player is logged-in, in such cases, the back link 'reroll/' (or 'reroll/index.php') is recorded and the login page is displayed. Otherwise, we display the reroll page.
<?php
// enable session
session_start();
// if user is not logged-in, load login page
if (empty($_SESSION['UserUID'])){
$back = 'reroll/';
include '../inc/login.php';
exit;
}
else {
// player is logged-in
$userUID = $_SESSION['UserUID'];
include('../inc/pdoConnect.php'); // connect to database using a PDO
include("../inc/functions.php"); // helper functions (controllers)
?><html><head><meta http-equiv="content-type" content="text/html; charset=UTF-8"><link rel="stylesheet" type="text/css" href="inc/styles.css"><link rel="stylesheet" type="text/css" href="reroll/styles.css"></head><body><script>
function selectWeapon(select){
var choice = select.selectedIndex;
if (choice == 0){
for (var k = 0; k < 6; k++){
document.getElementById('base' + k).value = '';
document.getElementById('orng' + k).value = '';
document.getElementById('orng' + k).placeholder = '';
}
}
else {
var option = select.options[choice];
var cstStat = option.baseStats;
var orgStat = option.orngStats;
for (var k = 0; k < 6; k++){
document.getElementById('base' + k).value = cstStat[k];
document.getElementById('orng' + k).placeholder = option.maxOrgStt;
document.getElementById('orng' + k).value = orgStat[k];
}
}
}
function reroll(id, name){
// perform some controls at client side, stronger controls will be done at server side too
// gain access to div containing the count of clicked rune
var count = document.getElementById('nbr' + name).innerHTML;
if (count == '--'){
alert('You must select a character to process.');
return;
}
// check available rune
var count = parseInt(count);
if (isNaN(count) || typeof count != 'number' || count < 1){
alert('You need a recreation rune to process.');
return;
}
// verify selection of weapon
var select = document.getElementById('wpns');
if (select.selectedIndex == 0){
alert('A weapon must be selected to process.');
return;
}
// and make sure it can have OJ stats (max value was stored with name & ID of items)
var weapon = select.options[select.selectedIndex];
if (weapon.maxOrgStt <= 0){
alert('This weapon can be not reconstructed.');
return;
}
// confirm use of specific rune
if (id >= 0){
if (!confirm('The stat ' + name + ' will be set to its maximum.\nDo you want to process?'))
return;
}
// post request
$.post('reroll/reroll.php', { itemID: weapon.value, stat: name })
.done(function(data){
$('#working').html(data);
});
}
</script><h3>Professional Blacksmith Services</h3><p>This area lets you manage operations handled by professional blacksmiths.<br />
Generic recreation runes will generate new random OJ stats for the selected weapon.
Specific recreation runes will give you the maximal value on the designated stat.
Please select a character and then an item to process.</p><table cellpadding='0' cellspacing='0'><tr><td><label for="char" class="caption">Characters:</label><select id="char" name="char" style="width: 300px; margin-bottom: 8px;"
onChange="$.post('reroll/getChar.php', { charID: this.value }).done(function(data){ $('#working').html(data); })"><option value='0'>--</option><?php
// get characters of currently logged-in player
$chars = getCharacters($userUID);
if ($chars != null && is_array($chars)){
foreach ($chars as $id => $name)
echo " <option value='$id'>$name</option>\n";
}
?>
</select></td></tr><tr><td><label for="item" class="caption" style="height: 44px;vertical-align: top;padding-top: 18px;">Runes:</label><table class="runes"><tr><!-- names recreation runes --><td>Rnd</td><td>Str</td><td>Dex</td><td>Rec</td><td>Int</td><td>Wis</td><td>Luc</td></tr><tr><!-- icons of recreation runes --><td><div class="rec" id="recRnd" onclick="reroll(-1, 'Rnd');"></div></td><td><div class="rec" id="recStr" onclick="reroll( 0, 'Str');"></div></td><td><div class="rec" id="recDex" onclick="reroll( 1, 'Dex');"></div></td><td><div class="rec" id="recRec" onclick="reroll( 2, 'Rec');"></div></td><td><div class="rec" id="recInt" onclick="reroll( 3, 'Int');"></div></td><td><div class="rec" id="recWis" onclick="reroll( 4, 'Wis');"></div></td><td><div class="rec" id="recLuc" onclick="reroll( 5, 'Luc');"></div></td></tr><tr><!-- quantities of recreation runes --><td id="nbrRnd">--</td><td id="nbrStr">--</td><td id="nbrDex">--</td><td id="nbrRec">--</td><td id="nbrInt">--</td><td id="nbrWis">--</td><td id="nbrLuc">--</td></tr></table></td></tr><tr><td><label for="wpns" class="caption">Weapons:</label><select id="wpns" name="wpns" style="width: 300px; margin-bottom: 8px;" onChange="selectWeapon(this);"><option value='0'>--</option></select></td></tr><tr><td><label class="caption" style="width: 46px;"><!-- align block --></label><div class="stats"><label for="base0" class="stat">STR:</label><input type="text" id="base0" readonly value='' /><input type="text" id="orng0" readonly value='' class="orange" /><label for="base1" class="stat">DEX:</label><input type="text" id="base1" readonly value='' /><input type="text" id="orng1" readonly value='' class="orange" /><label for="base2" class="stat">REC:</label><input type="text" id="base2" readonly value='' /><input type="text" id="orng2" readonly value='' class="orange" /><br /><label for="base3" class="stat">INT:</label><input type="text" id="base3" readonly value='' /><input type="text" id="orng3" readonly value='' class="orange" /><label for="base4" class="stat">WIS:</label><input type="text" id="base4" readonly value='' /><input type="text" id="orng4" readonly value='' class="orange" /><label for="base5" class="stat">LUC:</label><input type="text" id="base5" readonly value='' /><input type="text" id="orng5" readonly value='' class="orange" /></div></td></tr></table><?
} // user logged-in
?></body></html>
The first half of the page consists in processing / behaviours with the JS functions.
The second part is the design, several styles manages the display the runes as well as stats.
When the page is loaded (in the '#content' div using an Ajax request), a player is logged-in.
The PHP code retreives the list of characters and insert them in the first combo-list.
This is acheived by:
Code:
// get characters of currently logged-in player
$chars = getCharacters($userUID);
if ($chars != null && is_array($chars)){
foreach ($chars as $id => $name)
echo " <option value='$id'>$name</option>\n";
}
In order to manage these characters, the 'onChange' handler of this combo-list is defined as:
This is another Ajax request that requests the loading of page 'reroll/charDetails.php' with a 'charID' parameter.
A successful completion handler is defined to load the server response into the 'working' div.
The 'reroll/getChar.php' page itself is a PHP script that generates, server-side, a JS script which in turns, at client side, updates the recreation runes owned by the selected character and insert their weapons in the weapon combo-list.
<?php
// enable session
session_start();
// if user is not logged-in, load login page
if (empty($_SESSION['UserUID'])){
$back = 'reroll/';
include '../inc/login.php';
exit;
}
else {
// player is logged-in
$userUID = $_SESSION['UserUID'];
include('../inc/pdoConnect.php'); // connect to database using a PDO
include("../inc/functions.php"); // helper functions (controllers)
?>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link rel="stylesheet" type="text/css" href="inc/styles.css">
<link rel="stylesheet" type="text/css" href="reroll/styles.css">
</head>
<body>
<script>
function selectWeapon(select){
var choice = select.selectedIndex;
if (choice == 0){
for (var k = 0; k < 6; k++){
document.getElementById('base' + k).value = '';
document.getElementById('orng' + k).value = '';
document.getElementById('orng' + k).placeholder = '';
}
}
else {
var option = select.options[choice];
var cstStat = option.baseStats;
var orgStat = option.orngStats;
for (var k = 0; k < 6; k++){
document.getElementById('base' + k).value = cstStat[k];
document.getElementById('orng' + k).placeholder = option.maxOrgStt;
document.getElementById('orng' + k).value = orgStat[k];
}
}
}
function reroll(id, name){
// perform some controls at client side, stronger controls will be done at server side too
// gain access to div containing the count of clicked rune
var count = document.getElementById('nbr' + name).innerHTML;
if (count == '--'){
alert('You must select a character to process.');
return;
}
// check available rune
var count = parseInt(count);
if (isNaN(count) || typeof count != 'number' || count < 1){
alert('You need a recreation rune to process.');
return;
}
// verify selection of weapon
var select = document.getElementById('wpns');
if (select.selectedIndex == 0){
alert('A weapon must be selected to process.');
return;
}
// and make sure it can have OJ stats (max value was stored with name & ID of items)
var weapon = select.options[select.selectedIndex];
if (weapon.maxOrgStt <= 0){
alert('This weapon can be not reconstructed.');
return;
}
// confirm use of specific rune
if (id >= 0){
if (!confirm('The stat ' + name + ' will be set to its maximum.\nDo you want to process?'))
return;
}
// post request
$.post('reroll/reroll.php', { itemID: weapon.value, stat: name })
.done(function(data){
$('#working').html(data);
});
}
</script>
<h3>Professional Blacksmith Services</h3>
<p>This area lets you manage operations handled by professional blacksmiths.<br />
Generic recreation runes will generate new random OJ stats for the selected weapon.
Specific recreation runes will give you the maximal value on the designated stat.
Please select a character and then an item to process.</p>
<table cellpadding='0' cellspacing='0'>
<tr><td>
<label for="char" class="caption">Characters:</label>
<select id="char" name="char" style="width: 300px; margin-bottom: 8px;"
onChange="$.post('reroll/getChar.php', { charID: this.value }).done(function(data){ $('#working').html(data); })">
<option value='0'>--</option>
<?php
// get characters of currently logged-in player
$chars = getCharacters($userUID);
if ($chars != null && is_array($chars)){
foreach ($chars as $id => $name)
echo " <option value='$id'>$name</option>\n";
}
?>
</select>
</td></tr>
<tr><td>
<label for="item" class="caption" style="height: 44px;vertical-align: top;padding-top: 18px;">Runes:</label>
<table class="runes">
<tr><!-- names recreation runes -->
<td>Rnd</td>
<td>Str</td>
<td>Dex</td>
<td>Rec</td>
<td>Int</td>
<td>Wis</td>
<td>Luc</td>
</tr>
<tr><!-- icons of recreation runes -->
<td><div class="rec" id="recRnd" onclick="reroll(-1, 'Rnd');"></div></td>
<td><div class="rec" id="recStr" onclick="reroll( 0, 'Str');"></div></td>
<td><div class="rec" id="recDex" onclick="reroll( 1, 'Dex');"></div></td>
<td><div class="rec" id="recRec" onclick="reroll( 2, 'Rec');"></div></td>
<td><div class="rec" id="recInt" onclick="reroll( 3, 'Int');"></div></td>
<td><div class="rec" id="recWis" onclick="reroll( 4, 'Wis');"></div></td>
<td><div class="rec" id="recLuc" onclick="reroll( 5, 'Luc');"></div></td>
</tr>
<tr><!-- quantities of recreation runes -->
<td id="nbrRnd">--</td>
<td id="nbrStr">--</td>
<td id="nbrDex">--</td>
<td id="nbrRec">--</td>
<td id="nbrInt">--</td>
<td id="nbrWis">--</td>
<td id="nbrLuc">--</td>
</tr>
</table>
</td></tr>
<tr><td>
<label for="wpns" class="caption">Weapons:</label>
<select id="wpns" name="wpns" style="width: 300px; margin-bottom: 8px;" onChange="selectWeapon(this);">
<option value='0'>--</option>
</select>
</td></tr>
<tr><td>
<label class="caption" style="width: 46px;"><!-- align block --></label>
<div class="stats">
<label for="base0" class="stat">STR:</label>
<input type="text" id="base0" readonly value='' />
<input type="text" id="orng0" readonly value='' class="orange" />
<label for="base1" class="stat">DEX:</label>
<input type="text" id="base1" readonly value='' />
<input type="text" id="orng1" readonly value='' class="orange" />
<label for="base2" class="stat">REC:</label>
<input type="text" id="base2" readonly value='' />
<input type="text" id="orng2" readonly value='' class="orange" />
<br />
<label for="base3" class="stat">INT:</label>
<input type="text" id="base3" readonly value='' />
<input type="text" id="orng3" readonly value='' class="orange" />
<label for="base4" class="stat">WIS:</label>
<input type="text" id="base4" readonly value='' />
<input type="text" id="orng4" readonly value='' class="orange" />
<label for="base5" class="stat">LUC:</label>
<input type="text" id="base5" readonly value='' />
<input type="text" id="orng5" readonly value='' class="orange" />
</div>
</td></tr>
</table>
<?
} // user logged-in
?>
</body>
</html>
When a weapon is selected from the combo-list, the 'selectWeapon' function retreives its information from tailored properties of the option element ('cstStat' & 'orgStat' created by the 'getChar.php' page).
The 'placeholder' property of input field is used to display the maximal value of the orange stat when no bonus exist for the stat.
4.3.4.3 Recreation
The logic is the same than previous one. A click on the rec runes icons triggers the 'reroll' function.
This local (client) function performs some tests and posts another request:
Code:
// post request
$.post('reroll/reroll.php', { itemID: weapon.value, stat: name })
.done(function(data){ $('#working').html(data); });
The 'reroll/reroll.php' code is responsible of the full recraation process. It also generates a small JS script to update the stats of the reconstructed weapon and the count of avaialable runes.
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<?php
// the page is responsible of the full reconstruction process
// it must returns a valid JS script to the caller, on success
// the script will define the new stats; on failure an alert
// will provide the reason of error (except for attack attempt)
// helper function to exit with a valid JS script
function dieWithAlert($msg){
echo " alert(\"$msg\");\n";
die("</script>\n</body>\n</html>\n");
}
echo "<script>\n";
// verify context & parameters
session_start();
if (!isset($_SESSION['UserUID']) || !isset($_POST['itemID']) || !isset($_POST['stat']))
dieWithAlert('Invalid process.');
$userUID = $_SESSION['UserUID'];
$itemID = intval($_POST['itemID']);
$stat = $_POST['stat'];
// easy test on numerical values validity
if (''.$itemID != $_POST['itemID'])
dieWithAlert('Invalid parameters.');
include('../inc/pdoConnect.php'); // connect to database using a PDO
include("../inc/functions.php"); // helper functions (controllers)
// verify ownership of weapon
$charID = getItemOwner($itemID);
if ($charID == 0){
// $_POST['itemID'] was invalid
dieWithAlert('The operation is not allowed.');
}
// verify character ownership
if (!isValidChar($charID, $userUID)){
// $_POST['itemID'] exists but belongs to another account
dieWithAlert('The operation is not allowed.');
}
// get OJ nbr stats & max. value, verify eligibility of reconstruction
$settings = getMaxBonusStats($itemID);
if ($settings == null || $settings[0] <= 0 || $settings[1] <= 0){
dieWithAlert('This item can not be reconstructed.');
}
// verify availability of rec rune
$runes = getRecreationRunes($charID);
if (!isset($runes[ $stat ])){
// $_POST['stat'] was invalid
dieWithAlert('The operation is not allowed.');
}
if (($count = intval($runes[$stat])) <= 0){
// no runes left
dieWithAlert('There is no more required rune for the operation.');
}
// player shall not be logged in
if (getPlayerStatus($userUID) != 0){
// player is currently logged-in
dieWithAlert('The operation is not allowed.');
}
// start recreation
$isSuccess = false;
$newStats = null;
if (!$pdo->beginTransaction()){
// internal error
dieWithAlert('The operation failed to complete.');
}
if ($stat == 'Rnd'){
// generate up to '$settings[0]' stats ranging 0 up to '$settings[1]' on weapon '$itemID'
$newStats = generateRandomStats($itemID, $settings[0], $settings[1]);
if ($newStats != null){
// get ItemID of normal runes (100171 in default (OS) base) & remove 1
$isSuccess = (removeItemByID($charID, getTypeIDOfRecRune('Rnd'), 1) == 1);
}
}
else {
// get ItemID of specific runes (100181 .. 100186 in tutorial) & remove one
if (removeItemByID($charID, getTypeIDOfRecRune($stat), 1) == 1){
// set one stat to its maximal value
$isSuccess = setOrangeStat($itemID, $stat, $settings[1]);
}
}
// rollback (cancel) changes if an error has occured or if player is now logged-in
if (!$isSuccess || getPlayerStatus($userUID) != 0)
$pdo->rollBack();
else {
// commit SQL changes
$pdo->commit();
// update displayed nbr of runes
echo "document.getElementById('nbr$stat').innerHTML = '".($count - 1)."';\n";
// get selected option from weapon combo-list
echo "var select = document.getElementById('wpns');\n";
echo "var option = select.options[select.selectedIndex];\n";
// update orange stats property of selected option & displayed stats
if ($newStats != null){ // was random recreation
for ($k = 0; $k < 6; $k++){
$val = $newStats[$k];
if ($val == 0) $val = ''; // placeholder prop is displayed
echo "option.orngStats[$k] = '$val';\n";
echo "document.getElementById('orng$k').value = '$val';\n";
}
}
else {
// convert literal ident to numerical one
if ($stat == 'Str') $k = 0;
else if ($stat == 'Dex') $k = 1;
else if ($stat == 'Rec') $k = 2;
else if ($stat == 'Int') $k = 3;
else if ($stat == 'Wis') $k = 4;
else if ($stat == 'Luc') $k = 5;
$val = $settings[1];
echo "option.orngStats[$k] = '$val';\n";
echo "document.getElementById('orng$k').value = '$val';\n";
}
}
echo "</script>\n";
?>
</body>
</html>
The code defined in 'reroll.php' manages the required controls and the workflow of the process, all elementary operations (and all access to databases) are managed by functions declared in the main library 'functions.php'.
<?
/* load server specific settings */
include_once "defines.php";
/**
* Get the unique identifier of a player with given account name & password
* @param name the account name of the player
* @param pass the account password of the player
* @return the UserUID field of the account or 0 in case of error
*/
function getPlayerUID($name, $pass){
// use global PDO instance
global $pdo;
if (empty($pdo))
die('pdo is null, pdoConnect.php must be included');
// result placeholder
$userUID = 0;
// setup a query to get information on player account
if (($query = $pdo->prepare('SELECT UserUID,Pw FROM PS_UserData.dbo.Users_Master WHERE UserID=?')) == null)
die(FormatErrors($query->errorInfo()));
// bind the parameter & execute the query
if ($query->bindValue(1, $name, PDO::PARAM_STR) && $query->execute()){
// get response (a single row is expected)
$row = $query->fetch(PDO::FETCH_NUM);
if ($row != null){
// UserID exists, get UID & password in temporary
$uid = $row[0];
$pwd = $row[1];
// make sure no other records were returned
// if another row is returned, the DB is corrupted and we stop login
if ($query->fetch(PDO::FETCH_NUM) == null){
// verify password, depending on actual DB you may have to verify a hash
if (''.$pwd == $pass){
// UserID / Pwd are valid, return the UserUID
$userUID = $uid;
}
}
}
$query->closeCursor();
}
$query = null;
return $userUID;
}
/**
* Test whether a player is connected
* @param userUID the unique identifier of account
* @return 1 if connected, 0 if not connected, -1 if does not exist or error
*/
function getPlayerStatus($userUID){
// use global PDO instance
global $pdo;
// define query
if (($query = $pdo->prepare('SELECT Leave FROM PS_UserData.dbo.Users_Master WHERE UserUID=?')) == null)
return -1;
// setup & execute query
if (!$query->bindValue(1, $userUID, PDO::PARAM_INT) || !$query->execute())
return -1;
// get response (Leave is '1' when user is connected, 0 otherwise)
$row = $query->fetch(PDO::FETCH_NUM);
$query->closeCursor();
$query = null;
return ($row == null) ? -1 : $row[0];
}
/**
* Get characters of a given account (userUID)
* @param userUID the unique user identifier of logged-in player
* @return array containing (CharID, CharName) pairs or null if error
*/
function getCharacters($userUID){
// use global PDO instance
global $pdo;
if (empty($pdo))
die('pdo is null, pdoConnect.php must be included');
// get characters of currently logged-in player
$smt = "SELECT CharID,CharName FROM PS_GameData.dbo.Chars WHERE UserUID=? ORDER BY CharName";
// prepare the query
if (($query = $pdo->prepare($smt)) == null)
return null;
// execute the query
$chars = array();
if ($query->bindValue(1, $userUID, PDO::PARAM_INT) && $query->execute()){
// iterate rows to add them to array
while (($row = $query->fetch(PDO::FETCH_NUM)) != null)
$chars[ $row[0] ] = $row[1];
}
$query->closeCursor();
$query = null;
return $chars;
}
/**
* Verify that given charID is a character of the given userUID
* @param charID the dbo.Chars.CharID to test
* @param userUID the unique user identifier of logged-in player
* @return true if charID is a character of userUID, false otherwise
*/
function isValidChar($charID, $userUID){
// use global PDO instance
global $pdo;
if (empty($pdo))
die('pdo is null, pdoConnect.php must be included');
// setup a query to get information on player account
$result = false;
if (($query = $pdo->prepare('SELECT UserUID FROM PS_GameData.dbo.Chars WHERE CharID=?')) != null){
// bind the parameter & execute the query
if ($query->bindValue(1, $charID, PDO::PARAM_INT) && $query->execute()){
// get response & verify ownership of CharID
if (($row = $query->fetch(PDO::FETCH_NUM)) != null)
$result = ($row[0] == $userUID);
}
$query->closeCursor();
$query = null;
}
return $result;
}
/**
* Get the owner character of the given item
* @param itemID the identifier of item (dbo.CharItems.RowID) of interest
* @return the character (dbo.Chars.CharID) that owns the item, or 0 if item doesn't exist
*/
function getItemOwner($itemID){
global $pdo;
$char = 0;
// get the user UID that owns the item
$smt = 'SELECT CharID FROM PS_GameData.dbo.CharItems WHERE RowID=?';
if (($query = $pdo->prepare($smt)) == null)
return 0;
if ($query->bindValue(1, $itemID, PDO::PARAM_INT) && $query->execute()){
if (($row = $query->fetch(PDO::FETCH_NUM)) != null)
$char = $row[0];
$query->closeCursor();
}
$query = null;
return $char;
}
/**
* Remove item from the inventory of a character
* @param charID the character (Chars::CharID) that owns the item to remove
* @param itemID the ID (CharItems::ItemID = Items::ItemID) of item to remove
* @param count the number of items to remove
* @return the number of actually removed items
*/
function removeItemByID($charID, $itemID, $count){
// to remove items, we have to either update rows if they contain more items
// than the number to remove or delete the rows if all items are removed.
// the items to remove come from 1 row (if they were all stacked together)
// or from several (if not stacked).
global $pdo;
// select row(s) of given character containing given item
$smt = 'SELECT RowID,Count FROM PS_GameData.dbo.CharItems WHERE CharID=? AND ItemID=? ORDER BY Count';
if (($query = $pdo->prepare($smt)) == null)
return 0;
if (!$query->bindValue(1, $charID, PDO::PARAM_INT) ||
!$query->bindValue(2, $itemID, PDO::PARAM_INT) ||
!$query->execute())
return 0;
// iterate rows to remove as many items as requested
$removed = 0;
while ($count > 0){
if (($row = $query->fetch(PDO::FETCH_NUM)) == null)
break;
// get number of items for this row
$cnt = $row[1];
if ($cnt > $count){
// row contains more items than needed update it
$smt = "UPDATE PS_GameData.dbo.CharItems SET Count=Count- $count WHERE RowID={$row[0]}";
if (($updt = $pdo->prepare($smt)) != null && $updt->execute()){
$removed += $count;
$count = 0;
}
}
else {
// row contains less or same number of items than needed, delete it
$smt = "DELETE FROM PS_GameData.dbo.CharItems WHERE RowID={$row[0]}";
if (($delt = $pdo->prepare($smt)) != null && $delt->execute()){
$removed += $cnt;
$count -= $cnt;
}
}
}
$query->closeCursor();
$query = null;
return $removed;
}
/**
* Remove item from the inventory of a character
* @param charID the character (Chars::CharID) that owns the item to remove
* @param type the type (Items::Type) of item to remove
* @param typeID the type (Items::TypeID) of item to remove
* @param count the number of items to remove
* @return the number of actually removed items
*/
function removeItemByType($charID, $type, $typeID, $count){
return removeItemByID($charID, $type * 1000 + $typeID, $count);
}
/**
* Get the maximal number of bonus (OJ) stats and the maximal value
* @param itemID the identifier of item (dbo.CharItems.RowID) of interest
* @return an array holding in this order, the number of bonus'ed stats and the maximal value, or null
*/
function getMaxBonusStats($itemID){
global $pdo;
$smt = 'SELECT it.server,it.ReqWis '.
'FROM PS_GameData.dbo.CharItems ci '.
'JOIN PS_GameDefs.dbo.Items it ON it.ItemID=ci.ItemID '.
'WHERE ci.RowID=?';
$row = null;
if (($query = $pdo->prepare($smt)) != null){
if ($query->bindValue(1, $itemID, PDO::PARAM_INT) && $query->execute()){
$row = $query->fetch(PDO::FETCH_NUM);
$query->closeCursor();
}
$query = null;
}
return $row;
}
/**
* Get the recreation runes owned by given charID
* @param charID the dbo.Chars.CharID of interest
* @return array an associative array storing the count of each rec runes, or null if error
* the named entries are:
* 'Rnd': normal runes generating several random stats,
* 'Str': rune giving maximized Str stat, 'Dex': rune giving maximized Dex stat,
* 'Rec': rune giving maximized Rec stat, 'Int': rune giving maximized Int stat,
* 'Wis': rune giving maximized Wis stat, 'Luc': rune giving maximized Luc stat
*/
function getRecreationRunes($charID){
global $pdo;
// get server's list of rec runes
$idents = getSupportedRecRunes();
// get the rec. runes owned by this Char
$smt = 'SELECT ci.ItemID,sum(ci.Count) FROM PS_GameData.dbo.CharItems ci '.
'WHERE ci.CharID=? and ci.Type=100 and ci.ItemID in ('.$idents.') '.
'GROUP BY ci.ItemID';
// setup the query, bind the parameter & execute the query
if (($query = $pdo->prepare($smt)) == null ||
!$query->bindValue(1, $charID, PDO::PARAM_INT) ||
!$query->execute())
return null;
// initialise an array with all required entries and null quantities
$qty = array('Rnd' => 0,
'Str' => 0, 'Dex' => 0, 'Rec' => 0,
'Int' => 0, 'Wis' => 0, 'Luc' => 0);
// read the count of each runes
while ($row = $query->fetch(PDO::FETCH_NUM)){
// get literal type of rec rune & store sum of runes
if (($ident = getKindOfRecRune($row[0])) != null)
$qty[$ident] = $row[1];
}
$query->closeCursor();
$query = null;
return $qty;
}
/**
* Get the weapons of given charID as a PDO-response
* @param charID the dbo.Chars.CharID of interest
* @return PDOStatement the PDO statement containing response or null if error
*/
function getWeaponRows($charID){
global $pdo;
// retreive weapons of given char.
$smt = 'SELECT ci.RowID,it.ItemName,it.ReqLevel,ci.Craftname,'.
'it.ConstStr,it.ConstDex,it.ConstRec,it.ConstInt,it.ConstWis,it.ConstLuc,it.ReqWis '.
'FROM PS_GameData.dbo.CharItems ci '.
'JOIN PS_GameDefs.dbo.Items it ON it.ItemID=ci.itemID '.
'where ci.CharID=? and '.
'(ci.Type between 1 and 15 or ci.Type between 45 and 65 or ci.Type in (19,34,69,84)) '.
'ORDER BY it.ReqLevel,it.ItemName';
// setup the query, bind the parameter & execute the query
if (($query = $pdo->prepare($smt)) == null)
return null;
if (!$query->bindValue(1, $charID, PDO::PARAM_INT) || !$query->execute()){
// an error has occured
$query->closeCursor();
$query = null;
}
return $query;
}
/**
* Get the Orange Stats of given item
* @param itemID the identifier of item (dbo.CharItems.RowID) of interest
* @return associative array containing orange stats, or null on error
*/
function getOrangeStat($itemID){
global $pdo;
// read current craftname ('StDxRcInWsLcHpMpSpLp')
$craft = '';
$smt = 'SELECT Craftname FROM PS_GameData.dbo.CharItems WHERE RowID=?';
if (($query = $pdo->prepare($smt)) == null)
return null;
if ($query->bindValue(1, $itemID, PDO::PARAM_INT) && $query->execute()){
if (($row = $query->fetch(PDO::FETCH_NUM)) != null)
$craft = $row[0];
$query->closeCursor();
}
$query = null;
if ($craft == null) // item doesn't exist
return null;
// make sure craft is a valid craft-string
$craft = normaliseCraft($craft);
// extract stats
$stats = array();
$stats['Str'] = getOrangeValue($craft, 0, 1);
$stats['Dex'] = getOrangeValue($craft, 2, 1);
$stats['Rec'] = getOrangeValue($craft, 4, 1);
$stats['Int'] = getOrangeValue($craft, 6, 1);
$stats['Wis'] = getOrangeValue($craft, 8, 1);
$stats['Luc'] = getOrangeValue($craft, 10, 1);
return $stats;
}
/**
* Set one Orange Stat of given item to the given value
* @param itemID the identifier of item (dbo.CharItems.RowID) of interest
* @param stat the stat to maximize (string identifier in 'Str','Dex','Rec','Int','Wis','Luc')
* @param value the new value for the designated stat
* @return bool indicating the success of operation
*/
function setOrangeStat($itemID, $stat, $value){
global $pdo;
// read current craftname ('StDxRcInWsLcHpMpSpLp')
$craft = '';
$smt = 'SELECT Craftname FROM PS_GameData.dbo.CharItems WHERE RowID=?';
if (($query = $pdo->prepare($smt)) == null)
return false;
if ($query->bindValue(1, $itemID, PDO::PARAM_INT) && $query->execute()){
if (($row = $query->fetch(PDO::FETCH_NUM)) == null){
// item doesn't exist, exit with error
return false;
}
$craft = $row[0];
$query->closeCursor();
}
// make sure it's a valid craft-string
$craft = normaliseCraft($craft);
// get offset of designated stat
if ($stat == 'Str') $offset = 0;
else if ($stat == 'Dex') $offset = 2;
else if ($stat == 'Rec') $offset = 4;
else if ($stat == 'Int') $offset = 6;
else if ($stat == 'Wis') $offset = 8;
else if ($stat == 'Luc') $offset = 10;
else return false; // invalid identifier
// pad new value
if ($value <= 0)
$value = '00';
else if ($value < 10)
$value = '0'.$val;
else if ($value >= 100)
$value = 99;
// replace designated stat with new one
$craft = substr($craft, 0, $offset).$value.substr($craft, $offset + 2, 18 - $offset);
// store it
$smt = 'UPDATE PS_GameData.dbo.CharItems SET Craftname=? WHERE RowID=?';
if (($query = $pdo->prepare($smt)) == null)
return false;
if (!$query->bindValue(1, $craft, PDO::PARAM_STR) ||
!$query->bindValue(2, $itemID, PDO::PARAM_INT) ||
!$query->execute()){
// an error has occured
return false;
}
return true;
}
/**
* Recreate Orange Stats for given item
* @param itemID the identifier of item (dbo.CharItems.RowID) of interest
* @param nbrStat the maximal number of stats to create
* @param maxStat the maximal value for the stats
* @return an array containing the new stats, or null if error
*/
function generateRandomStats($itemID, $nbrStat, $maxStat){
global $pdo;
// new generated stats
$newVals = array(0 => 0, 1 => 0, 2 => 0, 3 => 0, 4 => 0, 5 => 0);
// make sure $maxStat is valid -- (Hp, Mp, Sp not supported)
if ($nbrStat > 6)
$nbrStat = 6;
// define the number of stats to recreate
$count = rand(0, $nbrStat);
// for each stat to create
for ($k = 0; $k < $count; $k++){
// choose a stat & make sure if is not already chosen
$statID = rand(0, 5);
while ($newVals[$statID] != 0){
// incr ID (instead of rand-create a new one) to avoid 'infinite-unlucky-loop'
$statID++;
if ($statID > 5)
$statID = 0;
}
// generate a value for the stat & store it
$newVals[$statID] = rand(1, $maxStat);
}
// generate craft string
$newCraft = '';
for ($k= 0; $k < 6; $k++){
$val = $newVals[$k];
if ($val == 0)
$val = '00';
else if ($val < 10)
$val = '0'.$val;
else if ($val >= 100)
$val = 99;
$newCraft .= $val;
}
// read current craftname ('StDxRcInWsLcHpMpSpLp')
$craft = '';
$smt = 'SELECT Craftname FROM PS_GameData.dbo.CharItems WHERE RowID=?';
if (($query = $pdo->prepare($smt)) != null){
if ($query->bindValue(1, $itemID, PDO::PARAM_INT) && $query->execute()){
if (($row = $query->fetch(PDO::FETCH_NUM)) == null){
// item doesn't exist, exit with error
return null;
}
$craft = $row[0];
$query->closeCursor();
}
}
// make sure it's a valid craft-string
$craft = normaliseCraft($craft);
// replace stats values with new ones
$craft = $newCraft.substr($craft, 12, 8);
// store it
$smt = 'UPDATE PS_GameData.dbo.CharItems SET Craftname=? WHERE RowID=?';
if (($query = $pdo->prepare($smt)) == null)
return null;
if (!$query->bindValue(1, $craft, PDO::PARAM_STR) ||
!$query->bindValue(2, $itemID, PDO::PARAM_INT) ||
!$query->execute()){
// an error has occured
return null;
}
return $newVals;
}
/* intenal use */
function normaliseCraft($craft){
// clean up old craft field (episo 3 uses it to store actual craftsman's name)
$valid = (strlen(''.$craft) == 20);
if ($valid){
for ($i = 0; $i < 20; $i++){
$c = $craft[$i];
$valid = $valid && ($c >= '0' && $c <= '9');
}
}
// return given string if it is valid
if ($valid)
return $craft;
// return a string with stats set to zero otherwise
return '00000000000000000000';
}
function getOrangeValue($craft, $pos, $coef = 1){
$s = substr($craft, $pos, 2);
if (strlen($s) != 2 || $s == '00' || !is_numeric($s))
return '';
return (intval($s, 10) * $coef);
}
All the functions defined in this library are generic and should work without any modifications on all p-servers.
Some additional functions / settings that depend on your server are defined in the 'inc/defines.php'.
For this release, this file contains rec. runes oriented functions.
<?
/**
* The functions defined in this file are server specific.
* Their implementation comply with the explanation & details
* published on elitePvP. If your databases use different settings
* change the file accordingly.
*/
/**
* Get a list of the TypeID of supported rec runes.
* @return a string containing the list of runes separated by commas
*/
function getSupportedRecRunes(){
return '100171,100181,100182,100183,100184,100185,100186';
}
/**
* Get the type-identifier of a specific recreation rune
* @param name a 3-character identifier specifying one stat
* @return the ItemID of the rec rune (as per PS_GameDefs.dbo.Items::ItemID)
* The name of rune shall be:
* 'Str': rune giving maximized Str stat or 'Dex': rune giving maximized Dex stat
* 'Rec': rune giving maximized Rec stat or 'Int': rune giving maximized Int stat
* 'Wis': rune giving maximized Wis stat or 'Luc': rune giving maximized Luc stat
*/
function getTypeIDOfRecRune($name){
// IF YOU HAVE OTHER TypeID VALUES, USE THEM
if ($name == 'Rnd') return 100171;
if ($name == 'Str') return 100181;
if ($name == 'Dex') return 100182;
if ($name == 'Rec') return 100183;
if ($name == 'Int') return 100184;
if ($name == 'Wis') return 100185;
if ($name == 'Luc') return 100186;
// given rec runes is not supported
return 0;
}
/**
* Get the name identifier for a specific recreation rune
* @param itemID identifier of the rec rune (as per PS_GameDefs.dbo.Items::ItemID)
* @return 3-char string identifying the rune, onr of:
* 'Str': rune giving maximized Str stat or 'Dex': rune giving maximized Dex stat
* 'Rec': rune giving maximized Rec stat or 'Int': rune giving maximized Int stat
* 'Wis': rune giving maximized Wis stat or 'Luc': rune giving maximized Luc stat
*/
function getKindOfRecRune($typeID){
// IF YOU HAVE OTHER TypeID VALUES, USE THEM
switch ($typeID){
case 100171: return 'Rnd';
case 100181: return 'Str';
case 100182: return 'Dex';
case 100183: return 'Rec';
case 100184: return 'Int';
case 100185: return 'Wis';
case 100186: return 'Luc';
}
// given rec runes is not supported
return null;
}
The 'generateRandomStats()' function defined in 'functions.php' is the sole 100% new as compared to the previous reroll implementation (all the others have still been documented and retested).
The specification of such function should be:
- all existing bonus stats are erased (reset to '0') before processing
- from 0 up to a fixed number of stats are created
- newly created stats have a value ranging 0 up to a fixed value
Resetting stats is easy, no comments.
Then chosing some stats to create may authorize several options. We can choose, for the number of stats to create, one stat among the available and not already chosen stats. In we do that, we will obtain the promised number of recreations expect when the generated value is zero. Well, obviously the OS don't work like this, the number of not generated stat is far more important than the frequency of zero (which is 1 / (max value + 1)).
Next, the choice of the recreated stat among the existing rebuildable stat (6 for weapon, 9 for gear & shield) can be made from all avialable stats or from the ones not already chosen. This choice actually linked to the first. If the implementation starts to generate a number of stats it will recreate (from 0 up to the maximal number of OJ stats) then the choice of stats shall ensure thay are all unique (otherwise the number will descrease again). The proposed solution uses this principle.
Finally, the randomly chosen stats are regenerated with value ranging 1 up to the maximal value, zero is excluded to not create additional not-created-stat (their number is defined by previous step).
Here is the implementation for this feature:
Code:
// define the number of stats to recreate
$count = rand(0, $nbrStat);
// for each stat to create
for ($k = 0; $k < $count; $k++){
// choose a stat & make sure if is not already chosen
$statID = rand(0, 5);
while ($newVals[$statID] != 0){
// incr ID (instead of rand-create a new one) to avoid 'infinite-unlucky-loop'
$statID++;
if ($statID > 5)
$statID = 0;
}
// generate a value for the stat & store it
$newVals[$statID] = rand(1, $maxStat);
}
Random processes are often tricky. There we must avoid too slow selection of stats to recreate. The index of stat to build shall so not be a random choice from all possible and until it is not already used. Otherwise, when the number of indexes to generate is equal (or close) to the maximal number of stats (meaning when the list of indexes shall be a basic copy of all available stat), a random process will need an important number of tries to enumerate all the indexes. To avoid this, the code simply tries to use the next one.
Random processes are also often unfair ... so you may want to change or adapt the strategy to be more generous (nexon likely doesn't use this strategy neither but to be less generous).
Let's consider that up to 3 stats can be rebuild (quite all low level stuffs use that figure). This proposed solution will first computes the number of stats as a random between 0 and 3 (limits included), this means that in 25% of cases the player will have 0 stat. If we imagine that this low-level weapon belongs to a low-level toon who hardly farms the rec runes, it's quite unfair. To compensate this, we can choose the number of stats to create among a list of non-equiprobable elements such as {0, 1, 1, 2, 2, 2, 3, 3}; with such list the probability of having 0 stat is only 1 / 8 or 12.5% (half of previous), 37.5% for 2 stats and so on.
If you want to balance the destiny, the coding should start there.
.
A lot of details related to login process were already posted, so in short:
the ps_login.exe service rely on the PS_UserData.dbo.usp_Try_GameLogin_Taiwan stored procedure to manage the login. it may depends on the sole Users_Master table and use another. In all cases, it shall (at least so that my function works) update the 'Leave' field of the Users_Master table according to the login result.
USE [PS_UserData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER Proc [dbo].[usp_Try_GameLogin_Taiwan]
@UserID varchar(18),
@InPassword varchar(32),
@SessionID bigint,
@UserIP varchar(15),
@UserUID int = -1,
@LoginType smallint = 1,
@LoginTime datetime = NULL
AS
SET NOCOUNT ON
DECLARE
@Status smallint,
@Leave tinyint,
@LeaveDate datetime
/*==================================================
-1 Invalid ID and Password
-2 Can't connect with the account since it's not selected as free challenger
-3 Account doesn't exist
-4 ID inprocess of termination follwing user's request.
-5 Your account is blocked now. You can't log into the game.
-6 Your account is blocked now. You can't log into the game.
-7 Account still restricted
==================================================*/
SET @Status = -1
SET @LoginTime = GETDATE()
-- ------------------------------------------------
SELECT @UserUID=[UserUID], @Status=[Status], @Leave=[Leave], @LeaveDate=LeaveDate
FROM Users_Master
WHERE UserID = @UserID AND Pw = @InPassword
-- NotExist User OR Leave User
IF (@UserUID = -1) -- account does not exist, will create it
BEGIN
set @Status = -3
/* uncomment if required
-- get current higher UserUID
select @UserUID=isnull(max(UserUID),0)+1 FROM Users_Master
set @Status = 0
-- auto-register new account
INSERT INTO Users_Master ([UserUID],[UserID],[Pw],[JoinDate],[Status])
VALUES (@UserUID,@UserID,@InPassword,GETDATE(),@Status)
*/
END
ELSE IF (@Leave = 1) -- This blocks a logged in account from being kicked
BEGIN
SET @Status = -5
END
-- ELSE IF (DATEDIFF(SECOND, @LeaveDate, GETDATE()) < 6)--This is the time delay
-- BEGIN
-- SET @Status = -7
-- END
-- BlockUser Check
IF( (@Status >= 2) AND (@Status <= 6) )
BEGIN
-- Get Block Limit Date AND Replace date text
DECLARE @BlockEndDate datetime
SELECT @BlockEndDate = BlockEndDate FROM Users_Block WHERE UserUID = @UserUID
IF ( @@ROWCOUNT <> 0 )
BEGIN
-- Block Release
IF (@BlockEndDate <= @LoginTime)
BEGIN
SET @Status = 0
UPDATE Users_Master SET Status=@Status WHERE UserUID=@UserUID
END
END
END
-- Select
SELECT @Status AS Status, @UserUID AS UserUID
-- Log Insert
IF (@Status=0 OR @Status=16 OR @Status=32 OR @Status=48 OR @Status=64 OR @Status=80)
BEGIN
UPDATE Users_Master SET Leave=1, JoinDate=GETDATE() WHERE UserUID=@UserUID
END
SET NOCOUNT OFF
this one is a very minimal version, with plain password, no time-attack protection, etc, the only statements you should keep is - if they are missing in your - the final update of the Leave field.
upon brutal disconnection, the game service will (after some time) detect that the client is no longer transmitting and will log off the account, to do so it communicates with the ps_login service that shall also update Leave field:
USE [PS_UserData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[usp_Try_GameLogout_R]
@UserUID int,
@SessionID bigint,
@LogoutType smallint = 0,
@ErrType int = 0
AS
SET NOCOUNT ON
DECLARE @LogTime datetime
DECLARE @LogType bit -- Login:0, Logout:1
SET @LogType = 1
SET @LogTime = GETDATE()
INSERT INTO PS_GameLog.dbo.UserLog (SessionID, UserUID, LogType, LogTime, LogoutType, ErrType)
VALUES(@SessionID, @UserUID, @LogType, @LogTime, @LogoutType, @ErrType)
UPDATE Users_Master set LeaveDate=@LogTime,Leave=0 where UserUID=@UserUID
SET NOCOUNT OFF
I know you commented on it but I really like to request being able to use that script you made for normal rec runes too. this is because it takes a long time to reroll at the pro blacksmith AND the speed enchanter has some security issues with it. This could help alot of servers by making reroling easier.
![[How To] Shaiya Web Service](https://www.elitepvpers.com/forum/iconimages/shaiya-pserver-guides-releases/how-shaiya-web-service_ltr.gif){kind=small}
, but this site offers up to date binaries for linux / netware systems but not for windows.
Shaiya PServer Development -> Shaiya PServer Guides & Releases
but totorial realy nice!! 
this helpful of other's
