src/Repositories/Character/Skills.php
public function getCharacterSkillCoverage(int $character_id): Collection
    {

        $in_game_skills = DB::table('invTypes')
            ->join(
                'invMarketGroups',
                'invMarketGroups.marketGroupID', '=', 'invTypes.marketGroupID'
            )
            ->where('parentGroupID', '?')// binding at [1]
            ->select(
                'marketGroupName',
                DB::raw('COUNT(invTypes.marketGroupID) * 5 as amount')
            )
            ->groupBy('marketGroupName')
            ->toSql();

        $character_skills = CharacterSkill::join(
            'invTypes',
            'invTypes.typeID', '=',
            'character_skills.skill_id'
        )
            ->join(
                'invMarketGroups',
                'invMarketGroups.marketGroupID', '=',
                'invTypes.marketGroupID'
            )
            ->where('character_id', '?')// binding at [2]
            ->select(
                'marketGroupName',
                DB::raw('COUNT(invTypes.marketGroupID) * character_skills.trained_skill_level  as amount')
            )
            ->groupBy(['marketGroupName', 'trained_skill_level'])
            ->toSql();

        $skills = DB::table(
            DB::raw('(' . $in_game_skills . ') a')
        )
            ->leftJoin(
                DB::raw('(' . $character_skills . ') b'),
                'a.marketGroupName',
                'b.marketGroupName'
            )
            ->select(
                'a.marketGroupName',
                DB::raw('a.amount AS gameAmount'),
                DB::raw('SUM(b.amount) AS characterAmount')
            )
            ->groupBy(['a.marketGroupName', 'a.amount'])
            ->addBinding(150, 'select')// binding [1]
            ->addBinding($character_id, 'select')// binding [2]
            ->get();

        return $skills;
    }
public function getCharacterSkillCoverage(int $character_id): Collection
    {

        $in_game_skills = DB::table('invTypes')
            ->join(
                'invMarketGroups',
                'invMarketGroups.marketGroupID', '=', 'invTypes.marketGroupID'
            )
            ->where('parentGroupID', '?')// binding at [1]
            ->select(
                'marketGroupName',
                DB::raw('COUNT(invTypes.marketGroupID) * 5 as amount')
            )
            ->groupBy('marketGroupName')
            ->toSql();

        $character_skills = CharacterSkill::join(
            'invTypes',
            'invTypes.typeID', '=',
            'character_skills.skill_id'
        )
            ->join(
                'invMarketGroups',
                'invMarketGroups.marketGroupID', '=',
                'invTypes.marketGroupID'
            )
            ->where('character_id', '?')// binding at [2]
            ->select(
                'marketGroupName',
                DB::raw('COUNT(invTypes.marketGroupID) * character_skills.trained_skill_level  as amount')
            )
            ->groupBy(['marketGroupName', 'trained_skill_level'])
            ->toSql();

        $skills = DB::table(
            DB::raw('(' . $in_game_skills . ') a')
        )
            ->leftJoin(
                DB::raw('(' . $character_skills . ') b'),
                'a.marketGroupName',
                'b.marketGroupName'
            )
            ->select(
                'a.marketGroupName',
                DB::raw('a.amount AS gameAmount'),
                DB::raw('SUM(b.amount) AS characterAmount')
            )
            ->groupBy(['a.marketGroupName', 'a.amount'])
            ->addBinding(150, 'select')// binding [1]
            ->addBinding($character_id, 'select')// binding [2]
            ->get();

        return $skills;
    }
public function getCharacterSkillCoverage ( int $ character_id ) : Collection { $ in_game_skills = DB :: table ( 'invTypes' ) -> join ( 'invMarketGroups' , 'invMarketGroups.marketGroupID' , '=' , 'invTypes.marketGroupID' ) -> where ( 'parentGroupID' , '?' ) // binding at [1] -> select ( 'marketGroupName' , DB :: raw ( 'COUNT(invTypes.marketGroupID) * 5 as amount' ) ) -> groupBy ( 'marketGroupName' ) -> toSql ( ) ; $ character_skills = CharacterSkill :: join ( 'invTypes' , 'invTypes.typeID' , '=' , 'character_skills.skill_id' ) -> join ( 'invMarketGroups' , 'invMarketGroups.marketGroupID' , '=' , 'invTypes.marketGroupID' ) -> where ( 'character_id' , '?' ) // binding at [2] -> select ( 'marketGroupName' , DB :: raw ( 'COUNT(invTypes.marketGroupID) * character_skills.trained_skill_level  as amount' ) ) -> groupBy ( [ 'marketGroupName' , 'trained_skill_level' ] ) -> toSql ( ) ; $ skills = DB :: table ( DB :: raw ( '(' . $ in_game_skills . ') a' ) ) -> leftJoin ( DB :: raw ( '(' . $ character_skills . ') b' ) , 'a.marketGroupName' , 'b.marketGroupName' ) -> select ( 'a.marketGroupName' , DB :: raw ( 'a.amount AS gameAmount' ) , DB :: raw ( 'SUM(b.amount) AS characterAmount' ) ) -> groupBy ( [ 'a.marketGroupName' , 'a.amount' ] ) -> addBinding ( 150 , 'select' ) // binding [1] -> addBinding ( $ character_id , 'select' ) // binding [2] -> get ( ) ; return $ skills ; }
Get a characters skill as well as category completion
ration rate.

TODO: This is definitely a candidate for a better refactor!

@param int $character_id

@return \Illuminate\Support\Collection
Get a characters skill as well as category completion ration rate .