Limit number of appointments per membership payment

At https://premium.wpmudev.org/forums/topic/code-help-for-appointments-and-membership

There's a nice bit of code for limiting appointments within a time span.

But my use case is that I have memberships which are a certain number of appointments per membership cycle, like 5 in 30 days, or 10 in 90 days, etc.

Just checking the days is not quite sufficient, because the visits counter resets after every membership cycle (payment).

Is there some function/hook/filter for finding the last payment date by the member? If so, could I see how that would change the code sample referred to above?

Actually, I see that the wp_m_member_payments table has both level_id and member_id.

Oh, and assume a table called packages which has fields package_id/membership_level_id, service_id, and limit. I think that should work.

Also, later, since members can have multiple memberships which cover different services, I will need to write a function to return the memberships/packages that include those services, so that I can check for the right payment.

Thanks!

  • Vaughan

    Hi @ken_lyle,

    That would be a bit more difficult.

    Here's modified code to check for 90 days too.

    // Works only if login required
    function limit_active_apps( $reply_array ) {
    global $wpdb, $current_user, $appointments;
    // Change statuses as required. i.e. remove pending condition if you wish
    // count for 90 Days
    $count = $wpdb->get_var( "SELECT COUNT(*) FROM " . $appointments->app_table . " WHERE user=".$current_user->ID." AND (status='pending' OR status='confirmed' OR status='paid' ) and start > NOW() - INTERVAL 90 DAY " );
    if ( $count >= 10 AND current_user_on_subscription($sub_id) )
    return array( 'error'=>'You have reached maximum allowed number of appointments' );
    // count for 30 Days
    $count = $wpdb->get_var( "SELECT COUNT(*) FROM " . $appointments->app_table . " WHERE user=".$current_user->ID." AND (status='pending' OR status='confirmed' OR status='paid' ) and start > NOW() - INTERVAL 30 DAY " );
    if ( $count >= 5 AND current_user_on_subscription($sub_id) )
    return array( 'error'=>'You have reached maximum allowed number of appointments' );
    return $reply_array;
    }
    add_filter( 'app_pre_confirmation_reply', 'limit_active_apps' );

    However, I'm not sure we can do it with the last payment date. Let me just ask the developer if he can suggest any way of achieving this with the above.

    Hope this helps

  • ken_lyle

    Thanks very much, Vaughn. Did you see that I added a little info about those fields in the payments table? From that I may be able to formulate the date to replace the 30 or 90 in your sample, but I am sure you, Ash, etc. would know best.

    I just removed a monologue from this paragraph about the issue where the subscriber may have multiple subscriptions which cover a particular service...but because they are on different cycles, it would involve pro-rating, etc. For now, I think I will prevent subscription to subscriptions where services overlap.

  • Hoang Ngo

    Hi @ken_lyle,

    I hope you are doing well.

    I think, when user make subscribe to a membership level, so when the payment return, we will process the limit of appointments in that time. And add a meta value to the usermeta table,just like "appointment_limit", and the value will be base on the membership level. Also, we will require something like "appointments count", and the init value will be 0. This meta will be reset each time new payment made. Because a user can only have a membership, so that's value will be refresh eachtime user subscribe to new membership or will be same when extend.

    So the perfomance will not be impact, and in the frontend, when a user made appointment, we will update the meta "appointments count". And when it go to limit, we show a message to inform them.

    In the membership folder, there's a folder called "gateways", and in the code there will be have some function like "handle_paypal_return", the name will be similiar, but you can only apply to the gateway you are using. That's the code to handler payment return.

    For appointment, here are some action
    "wp_ajax_pre_confirmation", this is when user prepare to make appointment on frontend, you can check the limit and show him the information.
    "wp_ajax_cancel_app", this is when client cancel an appointment, yeah, we need to reduce the limit if this cancel.
    "wp_ajax_post_confirmation", this is fire when new appointment has been submit, you can check the status, if that is complete, so we will increase the counter

    That is my stragery if i working some task like this :slight_smile:

    If you have any issues please don't hesitate to let us know so we can assist

    Best Regards
    Hoang

  • ken_lyle

    Thanks very much Hoang. I really appreciate the detailed information.
    I like your strategy OK. I am thinking that it may be simpler to just query the database for executed appointments during the booking process, rather than deal with updating counters based on cancellations.

    I also have two complications, in that I will also be wanting to do this check for Events, and I have some subscriptions which are for a mix of multiple services, like 5 of one and one of another, so I would need to make multiple counters.

    I am working on this with my developer, and will update you.

    Thanks again!

  • ken_lyle

    Thanks, Vinod. The SQL is :

    SELECT wp_users.user_nicename, Format(([transaction_stamp]/86400)+#1/1/1970#,"mm/dd/yyyy") AS [Date], DateAdd([level_period_unit],[level_period],[Date]) AS Expires, wp_m_subscriptions.sub_name AS [Subscription Name], wp_m_membership_levels.level_title, wp_eab_bookings.event_id, wp_posts.post_title, wp_posts.post_date
    FROM (((((wp_m_subscription_transaction INNER JOIN wp_eab_bookings ON wp_m_subscription_transaction.transaction_user_ID = wp_eab_bookings.user_id) INNER JOIN wp_users ON wp_m_subscription_transaction.transaction_user_ID = wp_users.ID) INNER JOIN wp_m_subscriptions ON wp_m_subscription_transaction.transaction_subscription_ID = wp_m_subscriptions.id) INNER JOIN wp_posts ON wp_eab_bookings.event_id = wp_posts.ID) INNER JOIN wp_m_subscriptions_levels ON wp_m_subscriptions.id = wp_m_subscriptions_levels.sub_id) INNER JOIN wp_m_membership_levels ON wp_m_subscriptions_levels.level_id = wp_m_membership_levels.id
    ORDER BY wp_posts.post_date;

    I don't think that I see the association in the database between the payment and the period that it covers (start and end date/time). Could you please inform me where to find or how to calculate that?

    Thanks!

  • Vaughan

    Hi @ken_lyle,

    The relationships for membership subscriptions are in the wp_m_membership_relationships table.

    In there, you can find the relationship for each user_id & the subscription_id they are linked, you will find the start date, which is the date they first subscribed, the update date which is whenever a subscription is changed/updated, & the expiry date which is when that current subscription expires.

    If this is not what you mean, I apologize.

    hope this helps

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.