public function postIndex()
{
// Parse the Job IDs
$selected_classes = Input::get('classes');
foreach (ClassJob::get_id_abbr_list(true) as $abbr => $id) {
if (in_array($abbr, $selected_classes)) {
$job_ids[] = $id;
}
}
if (empty($job_ids)) {
$job_ids[] = 1;
}
// All Leves
$query = Leve::with(array('classjob', 'item', 'item.name', 'item.recipe', 'item.vendors'))->where('item_id', '>', 0)->orderBy('classjob_id')->orderBy('level')->orderBy('xp')->orderBy('gil');
// Job IDs
$query->whereIn('classjob_id', $job_ids);
// Level Range
$min = Input::get('min_level');
$max = Input::get('max_level');
// Invert if needed
if ($min > $max) {
list($max, $min) = array($min, $max);
}
$query->whereBetween('level', array($min, $max));
// Triple Only
if (Input::get('triple_only') == 'true') {
$query->where('triple', 1);
}
// Types
$query->whereIn('type', Input::get('types'));
// Text Searches
if (Input::get('leve_name')) {
$query->where('name', 'like', '%' . Input::get('leve_name') . '%');
}
$leve_records = $query->remember(Config::get('site.cache_length'))->get();
$location_search = strtolower(Input::get('leve_location'));
$item_search = strtolower(Input::get('leve_item'));
$rewards = LeveReward::with('item')->whereBetween('level', array($min, $max))->whereIn('classjob_id', $job_ids)->get();
$leve_rewards = array();
foreach ($leve_records as $k => $row) {
if ($item_search && !preg_match('/' . $item_search . '/', strtolower($row->item->name->term))) {
unset($leve_records[$k]);
continue;
}
// TODO this can be moved into the query itself now, most likely
if ($location_search) {
if (!preg_match('/' . $location_search . '/', strtolower($row->location)) && !preg_match('/' . $location_search . '/', strtolower($row->major_location)) && !preg_match('/' . $location_search . '/', strtolower($row->minor_location))) {
unset($leve_records[$k]);
continue;
}
}
foreach ($rewards as $reward) {
if ($reward->classjob_id == $row->classjob_id && $reward->level == $row->level) {
$leve_rewards[$row->id][] = $reward;
}
}
}
return View::make('leve.rows')->with('leves', $leve_records)->with('leve_rewards', $leve_rewards);
}
public function getGathering($my_class = '', $supported_classes = '', $min_level = 0, $max_level = 0)
{
$supported_classes = explode(',', $supported_classes);
$show_quests = in_array($my_class, $supported_classes);
if (empty($supported_classes)) {
exit('No supported class selected... Todo: real error');
}
// TODO
$all_classes = ClassJob::get_id_abbr_list();
foreach ($supported_classes as $k => $v) {
if (in_array($v, array_keys($all_classes))) {
$supported_classes[$k] = $all_classes[$v];
} else {
unset($supported_classes[$k]);
}
}
if (empty($supported_classes)) {
exit('No supported class recognized...');
}
// TODO
$jobs = ClassJob::with('name')->whereIn('id', $supported_classes)->get();
foreach ($jobs as $k => $v) {
$jobs[$k] = $v->name->term;
}
if ($my_class != 'BTL') {
$job = ClassJob::get_by_abbr($my_class);
} else {
$job = $my_class;
}
if (empty($job)) {
exit('No primary class recognized...');
}
// TODO
$top_query = $inner_query = $join = $where = $union = $having = '';
$parameters = array();
DB::statement('SET SESSION group_concat_max_len=16384');
if (in_array($my_class, array('MIN', 'BTN'))) {
// Add Nodes
$top_query .= "\n\t\t\t\t\t(\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tCOUNT(*)\n\t\t\t\t\t\tFROM `cluster_items` AS `ci`\n\t\t\t\t\t\tJOIN `clusters` AS `c` ON `c`.`id` = `ci`.`cluster_id`\n\t\t\t\t\t\tWHERE `c`.`classjob_id` = ? AND `ci`.`item_id` = `x`.`item_id`\n\t\t\t\t\t) AS nodes,\n\t\t\t";
$parameters[] = $job->id;
$having = "HAVING nodes > 0";
} else {
// Battling or Fishing
$join = "LEFT JOIN `cluster_items` AS `ci` ON `ci`.`item_id` = `i`.`id` " . 'LEFT JOIN `item_ui_category` AS `iuc` ON `iuc`.`id` = `i`.`itemuicategory_id` ' . 'LEFT JOIN `translations` AS `iuct` ON `iuct`.`id` = `iuc`.`name_en`';
// FSH where the item is "seafood"
// BTL where the item is not "seafood"
$where = "AND `iuct`.`term` " . ($my_class == 'BTL' ? '!' : '') . "= 'Seafood'";
$where .= " AND `ci`.`id` IS NULL";
}
$parameters[] = $min_level;
$parameters[] = $max_level;
$parameters = array_merge($parameters, $supported_classes);
if ($my_class != 'BTL') {
$union = "\n\t\t\t\t\tUNION\n\n\t\t\t\t\tSELECT\n\t\t\t\t\t\t`i`.`id`, t.term AS name, `i`.level, `i`.`min_price`, qi.amount AS amount, \n\t\t\t\t\t\tqi.level AS quest_level, qi.quality AS quest_quality\n\t\t\t\t\tFROM quest_items AS qi\n\t\t\t\t\tJOIN items AS i ON i.id = qi.item_id\n\t\t\t\t\tJOIN classjob AS j ON j.id = qi.classjob_id\n\t\t\t\t\tJOIN translations AS t ON t.id = i.name_" . Config::get('language') . "\n\t\t\t\t\tWHERE j.id = ?\n\t\t\t\t\t\tAND qi.level BETWEEN ? AND ?\n\t\t\t";
$parameters[] = $job->id;
$parameters[] = $min_level;
$parameters[] = $max_level;
}
// TODO Caching
$results = DB::select("\n\t\t\tSELECT x.*,\n\t\t\t\t" . $top_query . "\n\t\t\t\t(\n\t\t\t\t\tSELECT COUNT(*)\n\t\t\t\t\tFROM `items_npcs_shops` AS `ins`\n\t\t\t\t\tWHERE `ins`.`item_id` = `x`.`item_id`\n\t\t\t\t) AS vendors, \n\t\t\t\t(\n\t\t\t\t\tSELECT COUNT(*)\n\t\t\t\t\tFROM `npcs_items` AS `ni`\n\t\t\t\t\tWHERE `ni`.`item_id` = `x`.`item_id`\n\t\t\t\t) AS beasts\n\t\t\tFROM (\n\t\t\t\tSELECT \n\t\t\t\t\t`i`.`id` AS `item_id`, t.term AS name, `i`.level, `i`.`min_price`, SUM(cj.amount) AS amount,\n\t\t\t\t\tNULL AS quest_level, NULL AS quest_quality\n\t\t\t\tFROM `careers` AS `c`\n\t\t\t\tJOIN `items` AS `i` ON `i`.`id` = `c`.`identifier`\n\t\t\t\tJOIN `career_classjob` AS `cj` ON `cj`.`career_id` = `c`.`id`\n\t\t\t\tJOIN translations AS t ON t.id = i.name_" . Config::get('language') . "\n\t\t\t\t" . $join . "\n\t\t\t\tWHERE\n\t\t\t\t\t`c`.`type` = 'item'\n\t\t\t\t\tAND `c`.`level` BETWEEN ? AND ?\n\t\t\t\t\tAND `cj`.`classjob_id` in (" . str_pad('', count($supported_classes) * 2 - 1, '?,') . ")\n\t\t\t\t\t" . $where . "\n\t\t\t\tGROUP BY `c`.`identifier`\n\n\t\t\t\t" . $union . "\n\t\t\t\t\n\t\t\t\tORDER BY `item_id` ASC\n\t\t\t) AS x\n\t\t\t" . $having, $parameters);
if ($my_class != 'BTL') {
$quest_results = array();
// Rip out Quest Entries
foreach ($results as $k => $result) {
if ($result->quest_level != NULL) {
$quest_results[] = $result;
unset($results[$k]);
}
}
// Put them back in, either merge or insert
if ($show_quests) {
foreach ($quest_results as $quest_item) {
foreach ($results as $k => $result) {
if ($quest_item->item_id == $result->item_id) {
// Merge
$original_amount = $result->amount;
$quest_amount = $quest_item->amount;
$results[$k] = $quest_item;
$results[$k]->amount = $original_amount;
$results[$k]->quest_amount = $quest_amount;
continue 2;
}
}
// If a match was found it would have continued
// This means at this point we add it in straight up
$quest_item->quest_amount = $quest_item->amount;
$results[] = $quest_item;
}
}
// Fishing doesn't have an ilvl...
if ($my_class != 'FSH') {
$sortable_results = array();
foreach ($results as $row) {
$sortable_results[$row->level][] = $row;
}
ksort($sortable_results);
$results = array();
foreach ($sortable_results as $rows) {
foreach ($rows as $row) {
$results[] = $row;
}
}
unset($sortable_results);
}
}
return View::make('career.items')->with(array('items' => $results, 'show_quests' => $show_quests, 'jobs' => $jobs, 'job' => $job, 'min_level' => $min_level, 'max_level' => $max_level));
}
