Skip to content

Instantly share code, notes, and snippets.

@codemasher codemasher/db_import.php Secret
Created Jan 28, 2016

Embed
What would you like to do?
<?php
/**
* @package gwskills
* @filesource db_import.php
* @version 0.1.0
* @link https://github.com/codemasher/
* @created 20.03.14
*
* @author Smiley <smiley@chillerlan.net>
* @copyright Copyright (c) 2014 {@link https://twitter.com/codemasher Smiley} <smiley@chillerlan.net>
* @license http://opensource.org/licenses/mit-license.php The MIT License (MIT)
*/
set_time_limit(0);
mb_internal_encoding('UTF-8');
define('SOME_WEIRD_CONSTANT_NAME', true);
require_once('../common.php');
/*
foreach(['pve' => 'gw_csv_str.csv', 'pvp' => 'gw_pvp_csv_str.csv'] as $mode => $skilldb){
$data = file_get_contents('http://pawned.gwcom.de/skilldata/deutsch/v1/'.$skilldb);
$data = explode("\n", $data);
$skilldata = [
[0,'Kein Skill','Leerer Slot',0,-1,0,0,0,0,0,0,0,0,0,0,0]
];
$sort = [0];
foreach($data as $skill){
$skill = explode(';', $skill);
if(is_array($skill) && !empty($skill[0])){
$skill = utf8_arr($skill);
unset($skill[2]);
unset($skill[17]);
unset($skill[18]);
unset($skill[19]);
$skilldata[] = $skill;
$sort[] = $skill[0];
}
}
array_multisort($skilldata, SORT_NUMERIC, $sort);
$db->Q('TRUNCATE TABLE `gw1_skills_'.$mode.'`');
$db->MI('INSERT INTO `gw1_skills_'.$mode.'` (`id`,`name_de`,`desc_de`,`campaign`,`attribute`,`type`,`profession`,`upkeep`,`energy`,`activation`,`recharge`,`adrenaline`,`sacrifice`,`elite`,`pve`,`overcast`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $skilldata);
}
foreach(['pve' => 'englishPVE.csv', 'pvp' => 'englishPVP.csv'] as $mode => $skilldb){
$data = file_get_contents('http://pawned.gwcom.de/skilldata/englisch/v1/'.$skilldb);
$data = explode("\n", $data);
$skilldata = [
['No skill','Empty slot',0]
];
foreach($data as $skill){
$skill = explode(';', $skill);
if(is_array($skill) && !empty($skill[0])){
$skill = utf8_arr($skill);
$skilldata[] = [$skill[1], $skill[3], $skill[0]];
}
}
$db->MI('UPDATE `gw1_skills_'.$mode.'` SET `name_en` = ?,`desc_en` = ? WHERE `id` = ?', $skilldata);
}
*/
#$attributes = $db->Q('SELECT `attribute` FROM `gw1_skills_pve` GROUP BY `attribute` ORDER BY `attribute`');
#$db->MI('INSERT IGNORE INTO `gw1_attributes` (`id`) VALUES (?)', $attributes);
#$professions = $db->Q('SELECT `profession` FROM `gw1_skills_pve` GROUP BY `profession` ORDER BY `profession`');
#$db->MI('INSERT IGNORE INTO `gw1_professions` (`id`) VALUES (?)', $professions);
#$skilltypes = $db->Q('SELECT `type` FROM `gw1_skills_pve` GROUP BY `type` ORDER BY `type`');
#$db->MI('INSERT IGNORE INTO `gw1_skilltypes` (`id`) VALUES (?)', $skilltypes);
#$sql = 'SELECT skills.`id`, skills.`name_de` AS `name`, skills.`desc_de` AS `desc`, profs.`name_de` AS `profession`, profs.`abbr_de` AS `prof_abbr`, attribs.`name_de` AS `attribute`, attribs.`abbr` AS `attr_abbr`, attribs.`primary`, types.`name_de` AS `type`, types.`abbr` AS `type_abbr`, skills.`campaign`, skills.`elite`, skills.`pve`, skills.`activation`, skills.`recharge`, skills.`energy`, skills.`upkeep`, skills.`adrenaline`, skills.`sacrifice`, skills.`overcast` FROM `gw1_skills_pve` AS skills, `gw1_professions` AS profs, `gw1_attributes` AS attribs, `gw1_skilltypes` AS types WHERE skills.`profession` = profs.`id` AND skills.`type` = types.`id` AND skills.`attribute` = attribs.`id` ORDER BY skills.`campaign` ASC, profs.`id` ASC, attribs.`id` ASC';
#$skill_list = $db->Q($sql);
#print_r($skill_list);
// http://www.guildwiki.de/wiki/api.php?format=json&action=query&prop=revisions&rvprop=content&titles=Federklaue
// http://wiki.guildwars.com/api.php?format=json&action=query&prop=revisions&rvprop=content&titles=Quickroot
/*
$vis_prof = [
1 => 7,
2 => 6,
3 => 1,
4 => 5,
5 => 3,
6 => 4,
7 => 8,
8 => 2,
9 => 10,
10 => 9,
];
$data = file_get_contents('x.txt');
$data = explode("\n", $data);
#print_r($data);
$bossdata = [];
foreach($data as $d){
$d = explode(';', $d);
$d[6] = $vis_prof[$d[6]];
$skill_id = $db->P('SELECT `id` FROM `gw1_skills_pve` WHERE `name_de` = ?', [$d[7]]);
$d[7] = $skill_id[0]['id'];
# print_r($d);
$wikidata_de = '';
# $wikidata_de = file_get_contents('http://www.guildwiki.de/wiki/api.php?format=json&action=query&prop=revisions&rvprop=content&titles='.rawurlencode($d[4]));
$bossdata[] = [$d[6],$d[7],4,$d[2],$d[3],$d[5],$d[4],$d[9],$wikidata_de, json_encode($d)];
}
#print_r($bossdata);
#$db->Q('TRUNCATE TABLE gw1_bosses');
$db->MI('INSERT INTO gw1_bosses (profession, elite, campaign, coord_x, coord_y, mapname, name_de, info_de, wikidata_de, data) VALUES (?,?,?,?,?,?,?,?,?,?)', $bossdata);
print_r($db->debug);
*/
/*
$q = $db->Q('SELECT * FROM gw1_bosses WHERE name_en = \'\' ');
$bossdata = [];
foreach($q as $d){
$d['wikidata_de'] = file_get_contents('http://www.guildwiki.de/wiki/api.php?format=json&action=query&prop=revisions&rvprop=content&titles='.rawurlencode($d['name_de']));
$d['wikidata_de'] = json_decode($d['wikidata_de'], true);
if(isset($d['wikidata_de']['query']['pages'])){
foreach($d['wikidata_de']['query']['pages'] as $p){
$d['wikidata_de'] = $p['revisions'][0]['*'];
break;
}
}
preg_match('/(\{\{en\|)(?P<name>[\'\(\),-\w\s]+)(}})/isu', $d['wikidata_de'], $m);
if(isset($m['name'])){
$d['name_en'] = $m['name'];
$d['wikidata_en'] = file_get_contents('http://wiki.guildwars.com/api.php?format=json&action=query&prop=revisions&rvprop=content&titles='.rawurlencode($m['name']));
$d['wikidata_en'] = json_decode($d['wikidata_en'], true);
if(isset($d['wikidata_en']['query']['pages'])){
foreach($d['wikidata_en']['query']['pages'] as $p){
$d['wikidata_en'] = $p['revisions'][0]['*'];
break;
}
}
}
else{
$d['name_en'] = '';
$d['wikidata_en'] = '';
}
$bossdata[] = [$d['wikidata_en'], $d['name_en'], $d['id']];
echo $d['id'].' - '.$d['name_de'].' - '.$d['name_en']."\n";
}
*/
#print_r($bossdata);
#$db->MI('UPDATE gw1_bosses SET wikidata_en = ?, name_en = ? WHERE id = ?', $bossdata);
#SELECT * FROM `gw1_bosses` WHERE `campaign` = 1 and mapname in('Abaddons Maul (Mission)', 'Fels der Verdammnis', 'Feuerring (Mission)', 'Vorhof der Hölle (Mission)')
#UPDATE `gw1_bosses` SET `region`='feuerring' WHERE mapname in('Abaddons Maul (Mission)', 'Fels der Verdammnis', 'Feuerring (Mission)', 'Vorhof der Hölle (Mission)')
/*
$q = $db->P('SELECT * FROM gw1_bosses WHERE region_id IN(?,?)', [11,12]);
$blub = [];
foreach($q as $d){
$blub[] = [round(($d['coord_x']*1.0)+(0)), round(($d['coord_y']*1.0)+(3)), $d['id']];// eotn: 1.115
}
$db->MI('UPDATE gw1_bosses SET coord_x = ?, coord_y = ? WHERE id = ?', $blub);
print_r($blub);
*/
/*
$data = file_get_contents('x.txt');
$data = explode("\n", $data);
print_r($data);
#$maps = [];
foreach($data as $d){
// 'city', 'explorable', 'mission', 'arenaOutpost', 'arena', 'challengeMission', 'eliteMission'
$maps[] = [4, 0, 'mission', $d];
}
print_r($maps);
*/
#$db->MI('INSERT INTO gw1_maps (continent, region, type, name_de) VALUES (?,?,?,?)', $maps);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.