Impose upper limit on editable table according to user membership level

cpshart

I have a MySQL Editable portfolio table which I need to impose an upper limit, according to the user membership level of the logged in user e.g.

Level 1 Bronze, Portfolio Limit = 1
Level 2 Silver, Portfolio Limit = 5
Level 3 Gold, Portfolio Limit = 10

Is it possible add logic to the client and/or server side script to check membership by querying the members subscription table entry for the member level and then query the portfolio table for number of entries and if they are equal I want to prevent any New Entries but also display a button on the webpage to prompt for an upgrade which on pressing would redirect to the pricing page of my site.

I have added my scripts below, but I just wanted to know in principle is this possible and how to achieve it, with thanks.

**This is my server script
**<?php

/*
 * portfolios.php
 */
 
// DataTables PHP library
include( "../lib/DataTables.php" );
 
 
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

$userid = $_POST['userid'];

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dm_portfolios' )
    ->fields(
        Field::inst( 'dm_portfolios.user_id' )
// default the value of the userid to $userid        
            ->setValue( $userid ),            
        Field::inst( 'dm_portfolios.code' )
                ->getFormatter('Format::UppercaseString')
                ->setFormatter('Format::UppercaseString')            
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A portfolio code is required' )
            ) ),
        Field::inst( 'dm_portfolios.name' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A portfolio name is required' )
            ) ),
        Field::inst( 'dm_portfolios.portfolio_type' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A portfolio type is required' )
            ) )
            )

->where( function ( $q ) use ( $userid) {
$q->where( 'user_id', $userid);
} )
->process( $_POST )
->json();


**Extract of Client Script
**<?php
global $wpdb;    
global $current_user;
get_currentuserinfo();
$user_id = $current_user->ID;

$rows = $wpdb->get_results("
SELECT 
p.user_id AS user_id,
p.code AS code,
p.name AS name,
p.portfolio_type AS portfolio_type
FROM
dm_portfolios p
WHERE
p.user_id >= IF($user_id=4,2,$user_id)
AND
p.user_id <= IF($user_id=4,3,$user_id)
");
        
foreach ($rows as $row ){
    echo "<tr>";
    echo "<td>$row->code</td>";
    echo "<td>$row->name</td>";
    echo "<td>$row->portfolio_type</td>";
    echo "</tr>";}
echo "</table>";
?>
<input type='hidden' id='passuserid' value='<?php echo $current_user->ID; ?>'>
    
<script type="text/javascript">
(function($) {
var editor; // use a global for the submit and return data rendering in the examples
 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: {
            url: '../../Editor-PHP-1.8.1/controllers/stock_portfolios.php',
            type: 'POST',
            data: function ( d ) {
                d.userid = $('#passuserid').val();
            }
        },
        table: "#stock-portfolios",
        fields: [ 
            {
                label: "Portfolio:",
                name: "dm_portfolios.code"
            }, {
                label: "Name:",
                name: "dm_portfolios.name"
            }, {
                label: "Portfolio Type:",
                name: "dm_portfolios.portfolio_type",
                type:  "radio",
                options: [
                    { label: "Dealing", value: "DEA" },
                    { label: "ISA",   value: "ISA" },
                    { label: "SIPP",   value: "SIPP" }
                    ]
            }
        ]
    } );
    
    $('#stock-portfolios').DataTable( {
        dom: "lBfrtip",
        "scrollY":      true,
        "scrollX":      true,
        
        ajax: {
            url: '../../Editor-PHP-1.8.1/controllers/stock_portfolios.php',
            type: 'POST',
            data: function ( d ) {
                d.userid = $('#passuserid').val();
                                }
            },
        columns: [
            { data: "dm_portfolios.code" },
            { data: "dm_portfolios.name" },
            { data: "dm_portfolios.portfolio_type" }
        ],

The part structure of the members table

id
user_id
subscription_plan_id
etc.

sample data
table wp_pms_member_subscriptions
id, user_id, subscription_plan_id
1,  3, 100
2,  4, 101
3,  5, 102

where 
subscription_plan_id
100 => Bronze, Portfolio Limit = 1 (relating to user_id 3)
101 => Silver,    Portfolio Limit = 5
102 => Gold,     Portfolio Limit = 10
etc.

Any help much appreciated.

Best Regards

Colin

allan

Hi Colin,

I'd suggest doing it server-side, since client-side validation is trivial to bypass.

Since you are using the Editor libraries, a global vaildator would be the way to do this. Query the database to see how many records the user has, and then reject or allow the request based on what is found and their account allowances.

Allan

cpshart

Hi Allan

Thanks for your response, I will investigate your suggestion.

Best Regards

Colin

cpshart

Hi allan

Bit late getting back to this one, I have added the following code just to test the global validator functionality in advance of adding the specific code I need as per your suggestion.

Extract of script below

$userid = $_POST['userid'];

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dm_portfolios' )
    ->fields(
        Field::inst( 'dm_portfolios.user_id' )
// default the value of the userid to $userid        
            ->setValue( $userid ),            
        Field::inst( 'dm_portfolios.code' )
                ->getFormatter('Format::UppercaseString')
                ->setFormatter('Format::UppercaseString')            
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A portfolio code is required' )
            ) ),
        Field::inst( 'dm_portfolios.name' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A portfolio name is required' )
            ) ),
        Field::inst( 'dm_portfolios.portfolio_type' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A portfolio type is required' )
            ) )
            )
            
    ->validator( function ( $editor, $action, $data ) {
        if ( $action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) {
           foreach ( $data['data'] as $pkey => $values ) {
                    $count = $editor->db()->query('select')->get('*')->table('dm_portfolios')
                                ->where('dm_portfolios.user_id', $userid)
                                ->exec()
                                ->count();
                    if ($count == 4){
                        return 'You cannot add more portfolios.';
                    }
            }
        }
    } )

->where( function ( $q ) use ( $userid) {
$q->where( 'user_id', $userid);
} )
->process( $_POST )
->json();

There are 4 portfolios defined for the current logged in user.
I am not getting an alert prompting "You cannot add more portfolios.", I may have made a syntax error in the code above ??

Also, how can I debug the server code to check the value of variables such as $count, (tried echo, console.log etc.)

Also, when the above condition is true, this should prevent any new rows being created, but how can I apply the same condition to change the HTML to for instance display an upgrade Button on the page (which would link to a pricing page allowing the user to upgrade to a higher membership, thus enabling them to add more portfolios)

Many Thanks

Colin

allan

Hi Colin,

The first thing to do is check what the JSON is that the server is returning. You can use the browser's network extension tools to see that.

but how can I apply the same condition to change the HTML to for instance display an upgrade Button on the page (which would link to a pricing page allowing the user to upgrade to a higher membership, thus enabling them to add more portfolios)

Get the above working first so you know the server-side bit is working. Then you can use button().disable() when the condition is met to disable the button (use draw to check if the condition is met on each draw of the table).

Allan

cpshart

Hi Allan

Thanks for getting back to me, the JSON shown below is no different before or after the code change.

Is the $count value and/or message 'You cannot add more portfolios.' supposed to be written to the JSON output ?

    ->validator( function ( $editor, $action, $data ) {
        if ( $action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) {
           foreach ( $data['data'] as $pkey => $values ) {
                    $count = $editor->db()->query('select')->get('*')->table('dm_portfolios')
                                ->where('dm_portfolios.user_id', $userid)
                                ->exec()
                                ->count();
                    if ($count == 4){
                        return 'You cannot add more portfolios.';
                    }
            }
        }
    } )

JSON before code change
{"data":[{"DT_RowId":"row_1","dm_portfolios":{"user_id":"2","code":"SFT_DEA_CO","name":"Selftrade Dealing Colin","portfolio_type":"DEA"}},{"DT_RowId":"row_3","dm_portfolios":{"user_id":"2","code":"SFT_ISA_CO","name":"Selftrade ISA Colin","portfolio_type":"ISA"}},{"DT_RowId":"row_5","dm_portfolios":{"user_id":"2","code":"AJB_SIPP_CO","name":"AJ Bell SIPP Colin","portfolio_type":"SIPP"}},{"DT_RowId":"row_7","dm_portfolios":{"user_id":"2","code":"XTR_SIPP_CO","name":"Extra SIPP Funds","portfolio_type":"DEA"}}],"options":[],"files":[]}

JSON after code change
{"data":[{"DT_RowId":"row_1","dm_portfolios":{"user_id":"2","code":"SFT_DEA_CO","name":"Selftrade Dealing Colin","portfolio_type":"DEA"}},{"DT_RowId":"row_3","dm_portfolios":{"user_id":"2","code":"SFT_ISA_CO","name":"Selftrade ISA Colin","portfolio_type":"ISA"}},{"DT_RowId":"row_5","dm_portfolios":{"user_id":"2","code":"AJB_SIPP_CO","name":"AJ Bell SIPP Colin","portfolio_type":"SIPP"}},{"DT_RowId":"row_7","dm_portfolios":{"user_id":"2","code":"XTR_SIPP_CO","name":"Extra SIPP Funds","portfolio_type":"DEA"}}],"options":[],"files":[]}

On a side issue the Quick Support 60 you offer on your website.

Are you able to connect remotely to my system and investigate and fix my problem under the terms of the quick support contract, and if so can you indicate how many support credits would be used up in doing so very approximately.

I am wondering whether a combination of paid support and my time developing the tables may be required in the future.

It is however shame that the Quick Support does not offer a longer duration contract option as it is a large price hike to the 12 month support.

I do however thank the team for their free support which I have found to be excellent.

Best Regards

Colin

allan

Yes - the returned string should be shown in the form's general error message. And a return true; for cases where the data is valid. Worth making sure you are using the 1.9.0 PHP libraries for Editor if you haven't already.

Regarding the JSON response - that looks like the JSON for loading the table's data initially. Rather than the response from the edit action.

Regarding the support - yes we could arrange that. To be honest, I'm likely to remove the "Quick support" for a period of time. Its virtually never used.

If you could let me know the JSON response from the server before picking up the quick support option. I don't want to charge you if its something trivial!

Allan

allan

One thing I just spotted - remember to add use ($userid) to your validator function similar to your where function, otherwise it won't be accessible in the function.

Allan

cpshart

Hi Allan

Thanks for your help and recommendation to avoid a charge if possible, much appreciated.

I am using 1.8.1 libraries, on my list to update, so I will do that tonight and also implement your other suggestions, then I should make some progress. I will report back later tonight, or tomorrow morning latest.

Best Regards

Colin

cpshart

Hi Allan

I have upgraded to 1.9.0, issue with connection so changed Editor-PHP-1.9.0/lib/config.php, now OK

Good news is The portfolio limit is now working

Extract of server file

    ->validator( function ( $editor, $action, $data ) {
        if ( $action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) {
           foreach ( $data['data'] as $pkey => $values ) {
                    $count = $editor->db()->query('select')->get('*')->table('dm_portfolios')
/*                                ->where( function ( $q ) use ( $userid) {
                                $q->where( 'user_id', $userid);
                                } )*/
                                ->exec()
                                ->count();
                    if ($count > 4){
                        return 'Upgrade to add more than 4 portfolios.';
                    }
            }
        }
    } )

{"data":[{"DT_RowId":"row_1","dm_portfolios":{"user_id":"2","code":"SFT_DEA_CO","name":"Selftrade Dealing Colin","portfolio_type":"DEA"}},{"DT_RowId":"row_3","dm_portfolios":{"user_id":"2","code":"SFT_ISA_CO","name":"Selftrade ISA Colin","portfolio_type":"ISA"}},{"DT_RowId":"row_5","dm_portfolios":{"user_id":"2","code":"AJB_SIPP_CO","name":"AJ Bell SIPP Colin","portfolio_type":"SIPP"}},{"DT_RowId":"row_7","dm_portfolios":{"user_id":"2","code":"XTR_SIPP_CO","name":"Extra SIPP Funds","portfolio_type":"DEA"}}],"options":[],"files":[]}

After Create JSON returned from server

{"error":"Upgrade to add more than 4 portfolios.","data":[]}

My only problem is if I uncomment the where condition on the $userid variable it results in an error

1st JSON response same as before
Next after Create new entry

<br />
<b>Notice</b>:  Undefined variable: userid in <b>/home/ukincome/public_html/Editor-PHP-1.9.0/controllers/stock_portfolios.php</b> on line <b>49</b><br />
{"data":[{"DT_RowId":"row_32","dm_portfolios":{"user_id":"2","code":"5thportfolio","name":"5th","portfolio_type":"DEA"}}]}

This is line 49

                                ->where( function ( $q ) use ( $userid) {
                                $q->where( 'user_id', $userid);
                                } )

Note $userid is used in where clause at base of script when section commented out and works correctly, so $userid is populated ok, also dm_portfolios.user_id exists on the table, you can see the value of 2 from JSON response.

{"data":[{"DT_RowId":"row_1","dm_portfolios":{"user_id":"2","code":"SFT_DEA_CO","name":"Selftrade Dealing 

Is my syntax for that command incorrect, must be nearly there so I can start building the server code to meet my needs.

Best Regards

Colin

allan

Hi Colin,

Could you show me your full latest PHP please? I don't immediately see what would be causing that from the above snippets.

Allan

cpshart

Hi Allan

This shows section below ->where clause resulting in errors
If I remove that section it works, but I do need to be able filter by userid

<?php

/*
 * portfolios.php
 */
 
// DataTables PHP library
include( "../lib/DataTables.php" );
 
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

$userid = $_POST['userid'];

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dm_portfolios' )
    ->fields(
        Field::inst( 'dm_portfolios.user_id' )
// default the value of the userid to $userid        
            ->setValue( $userid ),            
        Field::inst( 'dm_portfolios.code' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'A portfolio code is required' )
            ) ),
        Field::inst( 'dm_portfolios.name' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A portfolio name is required' )
            ) ),
        Field::inst( 'dm_portfolios.portfolio_type' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A portfolio type is required' )
            ) )
            )

/* INSERT GLOBAL VALIDATOR  https://editor.datatables.net/manual/php/validation */

    ->validator( function ( $editor, $action, $data ) {
        global $portfolio_limit;
        if ( $action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) { //1
           foreach ( $data['data'] as $pkey => $values ) { //2
                    $count = $editor
                            ->db()
                            ->query('select')
                            ->get('user_id')
                            ->table('dm_portfolios')
/* section causing error                            
                                ->where( function ( $q ) use ( $userid) {
                                $q->where( 'user_id', $userid);
                                } )
 secrtion causing error */
                            ->exec()
                            ->count();
                                
                    if ($count > 4){ //3
                        return 'Upgrade to add more than 4 portfolios.';
                    } //3
            } //2
            var_dump( $count );
        } //1
    } )
    
/* INSERT GLOBAL VALIDATOR  https://editor.datatables.net/manual/php/validation */

->where( function ( $q ) use ( $userid) {
$q->where( 'user_id', $userid);
} )
->process( $_POST )
->json();

JSON Errors

<br />
<b>Notice</b>:  Undefined variable: userid in <b>/home/ukincome/public_html/Editor-PHP-1.9.0/controllers/stock_portfolios.php</b> on line <b>54</b><br />
int(0)
{"data":[{"DT_RowId":"row_61","dm_portfolios":{"user_id":"2","code":"5th","name":"5 here","portfolio_type":"DEA"}}]}

when I remove
/* section causing error ....

JSON returned

{"error":"Upgrade to add more than 4 portfolios.","data":[]}

On modal
Message Upgrade to add more than 4 portfolios

as $count > 4

there are currently 7 rows for 2 user id's on the table
4 rows for userid=2, my test user logged in currently
3 rows for userid=3, my other test user

The system is assigning 7 to $count, it needs to be the number for a given userid

Can I debug and get the value of $count or other variable, also is there some useful documentation on the options re programming on the server scripts.

I look forward to hearing from you.

Best Regards

Colin

cpshart

Quick one Allan, would you query the account allowance table (to compare against portfolio row count) in the server script or in the client side and pass through in the same way as the userid ?

I think you were advocating server side ?

Regards

Colin

allan

Server-side - no question. If this needs to be secure (i.e. its open to the public rather than an internal application were you might have a higher level of trust that someone isn't going to mess around with it) then you must do validation at the server-side (irrespective of if you also do it at the client-side).

Client-side sanity check can be good (i.e. stop the user making the mistake in the first place), but validation needs to be done at the server-side since client-side validation is trivial to bypass.

Regaring the $userid error - you also need to change:

function ( $editor, $action, $data ) {

to be:

function ( $editor, $action, $data ) use ($userid) {

since otherwise $userid isn't available inside that function (PHP's mental variable scoping...).

Allan

cpshart

Hi Allan

Thanks for your advice, the server script is now working to my requirements after the change above, I should be able to add the code to meet my exact requirements.

Much appreciated.

Best
Colin

cpshart

Hi Allan

Sorry, stuck on converting the Object $subscription_plan_id to a String, then it should work ok.

I am assuming from the documentation that the $subscription_plan_id variable will be returned as an Object type.

I have tried unsuccessfully using cast and implode..

Conversion object to an integer would also work by changing the case statement.

    ->validator( function ( $editor, $action, $data ) use ($userid) {
        global $subscription_plan_id;
        global $portfolio_limit;
        
        
        if ( $action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) { //1
           foreach ( $data['data'] as $pkey => $values ) { //2
                    $count = $editor
                            ->db()
                            ->query('select')
                            ->get('*')
                            ->table('dm_portfolios')
                            ->where( function ( $q ) use ( $userid) {
                            $q->where( 'user_id', $userid);
                            } )
                            ->exec()
                            ->count();

                            $subscription_plan_id = $editor
                            ->db()
                            ->query('select')
                            ->get('subscription_plan_id')
                            ->table('wp_pms_member_subscriptions')
                            ->where( function ( $q ) use ( $userid) {
                            $q->where( 'user_id', $userid);
                            } )
                            ->exec();
                            
                    switch ($subscription_plan_id) {
                        case "18649": $portfolio_limit = 5;break; 
                        case "18650": $portfolio_limit = 6;break; 
                        case "18651": $portfolio_limit = 7;break; 
                        default: $portfolio_limit = 4;break; 
                    }
                        // subscription_plan_id     description     portfolio limit
                        // 18649                    Bronze Plan     3
                        // 18650                    Silver Plan     4
                        // 18651                    Gold Plan       5
                        
                    if ($count == $portfolio_limit){ //3
                        return 'Upgrade to add more than '. $portfolio_limit . ' portfolios.';
                    } //3
            } //2
        } //1
    } )

Best Regards

Colin

allan

$subscription_plan_id on line 19 is a DataTables\Database\Result object. Use $subscription_plan_id->fetch() to get the row returned (keeping in mind that there might be no rows depending on the query and data in the db - so you might wish to guard against null).

Allan

cpshart

Hi Allan

Thanks for response, this works with no errors, but

                            $q->where( 'user_id', $userid);
                            } )
                            ->exec()
                            ->fetch();
                            
                    switch ($subscription_plan_id) {
                        case "18649": $portfolio_limit = 7;break; 
                        case "18650": $portfolio_limit = 6;break; 
                        case "18651": $portfolio_limit = 4;break; 
                        default: $portfolio_limit = 5;break; 
                    }

JSON output of the table wp_pms_member_subscriptions

[... extract here 
{"id":"1","user_id":"3","subscription_plan_id":"18649","start_date":"2019-08-30 00:00:00","expiration_date":"2020-08-29 00:00:00","status":"active","payment_profile_id":"","payment_gateway":"","billing_amount":"0","billing_duration":"0","billing_duration_unit":"","billing_cycles":"0","billing_next_payment":null,"billing_last_payment":null,"trial_end":null},
{"id":"2","user_id":"2","subscription_plan_id":"18651","start_date":"2019-08-30 00:00:00","expiration_date":"0000-00-00 00:00:00","status":"active","payment_profile_id":"","payment_gateway":"","billing_amount":"0","billing_duration":"0","billing_duration_unit":"","billing_cycles":"0","billing_next_payment":null,"billing_last_payment":null,"trial_end":null}
]

The case statement always drops through to the default value (the client behaves as expected with regard to $portfolio_limit)

So the value of "18651" for subscription_plan_id (for user_id = 2, current user_id) is never being matched.

The user_id and subscription_plan_id are held on a 3rd party table so i cannot modify the structure, both columns are defined as BigInt(20)

N.B I have tried numeric matches to no avail

Any thoughts appreciated.

Thanks

Colin

cpshart

Hi Allan

JSON Results of var_dump($subscription_plan_id); below

array(1) {
  ["subscription_plan_id"]=>
  string(5) "18651"
}

                            $q->where( 'user_id', $userid);
                            } )
                            ->exec()
                            ->fetch();
 var_dump($subscription_plan_id);                           
                    switch ($subscription_plan_id) {
                        case "18649": $portfolio_limit = 7;break; 
                        case "18650": $portfolio_limit = 6;break; 
                        case "18651": $portfolio_limit = 4;break; 
                        default: $portfolio_limit = 5;break; 
                    }

Colin

cpshart

Some progress

TRIM($subscription_plan_id) results in an error

<br />
<b>Warning</b>:  trim() expects parameter 1 to be string, array given in <b>/xxxxxxxxxxxxxx/stock_portfolios.php</b> on line <b>83</b><br />
{"data":[{"DT_RowId":"row_113","dm_portfolios":....

so $subscription_plan_id is not a string datatype

allan

$subscription_plan_id['subscription_plan_id']

Remember its fetching a row of data (even if you are only asking for one column).

Allan

cpshart

Great Allan

All working now !!, a big thank you for all your help.

Best Regards

Colin