db_import.php

<?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);