Individual Mining View does not return all characters

[jediefe]

I noticed that not all characters that have mining ledger entries in the database are shown in the past billing view of the previous month. I made some tests in Tinker and indeed, getMainsBilling() does not return all characters that should be shown. I verified this against the built in Mining Ledger tool in the SeAT core. I finally suspect the DB query to be buggy, but I'm not 100% sure about it.

[jediefe]

The DB Query in getCharacterBilling in Helpers/BillingHelper.php queries the main_character via the DB, which does not work, since not all user groups have an entry there. The main character needs to be resolved differently.

[jediefe]

I think I found a fix for now. Since I don't have that repo cloned etc. I'll copy the relevant part of BillingHelper.php in here, even though it's not considered as good practice:

    public function getCharacterBilling($corporation_id, $year, $month)
    {

        if (setting("pricevalue", true) == "m") {
            $ledger = DB::table('character_minings')->select('users.id', DB::raw('SUM((character_minings.quantity / 100) * (invTypeMaterials.quantity * ' .
                (setting("refinerate", true) / 100) . ') * market_prices.adjusted_price) as amounts'))
                ->join('invTypeMaterials', 'character_minings.type_id', 'invTypeMaterials.typeID')
                ->join('market_prices', 'invTypeMaterials.materialTypeID', 'market_prices.type_id')
                ->join('corporation_member_trackings', function($join) use ($corporation_id) {
                     $join->on('corporation_member_trackings.character_id', 'character_minings.character_id')
                         ->where('corporation_member_trackings.corporation_id', $corporation_id);
                })
                ->join('users', 'users.id', 'corporation_member_trackings.character_id')
                //->join('user_settings', function ($join) {
                //    $join->on('user_settings.group_id', 'users.group_id')
                //        ->where('user_settings.name', 'main_character_id');
                //})
                ->where('year', $year)
                ->where('month', $month)
                //->groupby('user_settings.value')
                ->groupby('users.id')
                ->get();
        } else {

            $ledger = DB::table('character_minings')->select('users.id', DB::raw('SUM(character_minings.quantity * market_prices.average_price) as amounts'))
                ->join('market_prices', 'character_minings.type_id', 'market_prices.type_id')
                ->join('corporation_member_trackings', function($join) use ($corporation_id) {
                     $join->on('corporation_member_trackings.character_id', 'character_minings.character_id')
                         ->where('corporation_member_trackings.corporation_id', $corporation_id);
                })
                ->join('users', 'users.id', 'corporation_member_trackings.character_id')
                //->join('user_settings', function ($join) {
                //    $join->on('user_settings.group_id', 'users.group_id')
                //        ->where('user_settings.name', 'main_character_id');
                //})
                ->where('year', $year)
                ->where('month', $month)
                //->groupby('user_settings.value')
                ->groupby('users.id')
                ->get();
        }

        return $ledger;
    }

    private function getTrackingMembers($corporation_id)
    {
        return $this->getCorporationMemberTracking($corporation_id);
    }

    public function getMainsBilling($corporation_id, $year = null, $month = null)
    {
        if (is_null($year)) {
           $year = date('Y');
        }
        if (is_null($month)) {
           $month = date('n');
        }

        $summary = [];
        $taxrates = $this->getCorporateTaxRate($corporation_id);

        $ledger = $this->getCharacterBilling($corporation_id, $year, $month);

        foreach ($ledger as $entry) {
            $main_character_id = User::find($entry->id)->group->main_character->character_id;
            if (is_null($main_character_id)) {
                continue;
            }

            if (!isset($summary[$main_character_id])) {
                $summary[$main_character_id]['amount'] = 0;
            }

            $summary[$main_character_id]['amount'] += $entry->amounts;
            $summary[$main_character_id]['id'] = $main_character_id;
            $summary[$main_character_id]['taxrate'] = $taxrates['taxrate'] / 100;
            $summary[$main_character_id]['modifier'] = $taxrates['modifier'] / 100;
        }
        return $summary;
    }

So what I did is to exclude the user_settings.values from the DB query to get the mining ledger entries per single character and to resolve the main_character_id afterwards by using the core function of the User namespace and implement that into the $summary array. I tested it and it's now working for my cases I found.

[dysath]

The problem with this is that every character should have a Main character defined. If they don't have a main defined, someone needs to go in and do that step. In 3.x, it's supposed to make sure everyone has a main - so I'm not sure where the problem has occurred on your side that users do not have Mains defined.

…

On Mon, Mar 4, 2019 at 6:26 AM jediefe ***@***.***> wrote: I think I found a fix for now. Since I don't have that repo cloned etc. I'll copy the relevant part of BillingHelper.php in here, even though it's not considered as good practice: public function getCharacterBilling($corporation_id, $year, $month) { if (setting("pricevalue", true) == "m") { $ledger = DB::table('character_minings')->select('users.id', DB::raw('SUM((character_minings.quantity / 100) * (invTypeMaterials.quantity * ' . (setting("refinerate", true) / 100) . ') * market_prices.adjusted_price) as amounts')) ->join('invTypeMaterials', 'character_minings.type_id', 'invTypeMaterials.typeID') ->join('market_prices', 'invTypeMaterials.materialTypeID', 'market_prices.type_id') ->join('corporation_member_trackings', function($join) use ($corporation_id) { $join->on('corporation_member_trackings.character_id', 'character_minings.character_id') ->where('corporation_member_trackings.corporation_id', $corporation_id); }) ->join('users', 'users.id', 'corporation_member_trackings.character_id') //->join('user_settings', function ($join) { // $join->on('user_settings.group_id', 'users.group_id') // ->where('user_settings.name', 'main_character_id'); //}) ->where('year', $year) ->where('month', $month) //->groupby('user_settings.value') ->groupby('users.id') ->get(); } else { $ledger = DB::table('character_minings')->select('users.id', DB::raw('SUM(character_minings.quantity * market_prices.average_price) as amounts')) ->join('market_prices', 'character_minings.type_id', 'market_prices.type_id') ->join('corporation_member_trackings', function($join) use ($corporation_id) { $join->on('corporation_member_trackings.character_id', 'character_minings.character_id') ->where('corporation_member_trackings.corporation_id', $corporation_id); }) ->join('users', 'users.id', 'corporation_member_trackings.character_id') //->join('user_settings', function ($join) { // $join->on('user_settings.group_id', 'users.group_id') // ->where('user_settings.name', 'main_character_id'); //}) ->where('year', $year) ->where('month', $month) //->groupby('user_settings.value') ->groupby('users.id') ->get(); } return $ledger; } private function getTrackingMembers($corporation_id) { return $this->getCorporationMemberTracking($corporation_id); } public function getMainsBilling($corporation_id, $year = null, $month = null) { if (is_null($year)) { $year = date('Y'); } if (is_null($month)) { $month = date('n'); } $summary = []; $taxrates = $this->getCorporateTaxRate($corporation_id); $ledger = $this->getCharacterBilling($corporation_id, $year, $month); foreach ($ledger as $entry) { $main_character_id = User::find($entry->id)->group->main_character->character_id; if (is_null($main_character_id)) { continue; } if (!isset($summary[$main_character_id])) { $summary[$main_character_id]['amount'] = 0; } $summary[$main_character_id]['amount'] += $entry->amounts; $summary[$main_character_id]['id'] = $main_character_id; $summary[$main_character_id]['taxrate'] = $taxrates['taxrate'] / 100; $summary[$main_character_id]['modifier'] = $taxrates['modifier'] / 100; } return $summary; } — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#12 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AQF4jMIjFoYy0Eg_a2QrCdvVdAINHp_Oks5vTRD5gaJpZM4banAe> .

-- Ed Stafford

[jediefe]

That was a thing I also was wondering about since for the cases I had the main character for the user was resolved correctly and appears also in the profile menu (tested by impersonating the user). But there was no entry in user_settings in the DB for that particular user and I absolutely do not comprehend why this is and what's happening there, but I decided to replace the DB method with the core method of resolving main chars. Which is working as intended as it now resolves the correct main user for my test cases.
Maybe this is related to a bug in the core software.

[dysath]

Considering that 'user_settings' is where the main character is defined, that's a possibility. I know I've had to go into other's profiles (impersonate user) and click 'Update' in their profile so the main character sticks; but that was due to migrating from seat 2.x to 3.x some time back. You might want to try that as well and see if the table updates properly.

…

On Tue, Mar 5, 2019 at 7:24 AM jediefe ***@***.***> wrote: That was a thing I also was wondering about since for the cases I had the main character for the user was resolved correctly and appears also in the profile menu (tested by impersonating the user). But there was no entry in user_settings in the DB for that particular user and I absolutely do not comprehend why this is and what's happening there, but I decided to replace the DB method with the core method of resolving main chars. Which is working now. Maybe this is related to a bug in the core software. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#12 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AQF4jEDbNA3WWHvX605o6o91YXYzBpRgks5vTnAGgaJpZM4banAe> .

-- Ed Stafford