Question Award user a credit after x days of activity. | Not working as expected.

[Rick7C2]

As I said in this post, I setup the daily activity with a frequency of 30.

What I expected was if a user is active for 30 days they would receive the specified credit.

However I have been active for 30 days and have not received it.

So let me be clear with exactly what I want.

I want to award users with a credit for being active for x amount of days.

I don't want the counter to reset if they fail to be active one day.

So say x = 5

Day 1 User was active
Day 2 User was active
Day 3 User was active
Day 4 User was NOT active
Day 5 User was active
Day 6 User was active and Credit Received for 5 days of activity.

Can this be done? I assume so but I can't get it to work.

What would my settings need to be to accomplish this?

 

[Fillip H.]

The way the Frequency works is: It inserts a transaction record for every time it skips a transaction but with status = 3 instead of the normal status = 1, then checks this with this query:

SELECT SUM(eventid = X AND status = 3 AND dateline >= (SELECT dateline FROM xf_dbtech_credits_transaction WHERE eventid = X AND user_id = Y AND status IN (1, 2) AND negate = 0 ORDER BY transaction_id DESC LIMIT 1)) AS skipped
FROM xf_dbtech_credits_transaction
WHERE negate = 0
    AND userid = Y

Replace all instances of X with the event ID of the frequency event and replace all instances of Y with the user ID of the user you want to check.

Let me know the result.

 

[Rick7C2]

I tried running the following command in phpmyadmin

SELECT SUM(eventid = 42 AND status = 3 AND dateline >= (SELECT dateline FROM xf_dbtech_credits_transaction WHERE eventid = 42 AND user_id = 1 AND status IN (1, 2) AND negate = 0 ORDER BY transaction_id DESC LIMIT 1)) AS skipped
FROM xf_dbtech_credits_transaction
WHERE negate = 0
    AND userid = 1

And got this response...

Error
SQL query:



SELECT SUM(eventid = 42 AND status = 3 AND dateline >= (SELECT dateline FROM xf_dbtech_credits_transaction WHERE eventid = 42 AND user_id = 1 AND status IN (1, 2) AND negate = 0 ORDER BY transaction_id DESC LIMIT 1)) AS skipped
FROM xf_dbtech_credits_transaction
WHERE negate = 0
AND userid = 1 LIMIT 0, 25



MySQL said:

#1054 - Unknown column 'user_id' in 'where clause'

It seems I don't have the following table in my DB
xf_dbtech_credits_transaction

Filtering tables with "dbt"

I see only...

 

[Rick7C2]

Looking at src/addons/DBTech/Credits/setup.php I'm missing the following tables and their contents...

xf_dbtech_credits_purchase_log
xf_dbtech_credits_purchase_transaction
xf_dbtech_credits_transaction
xf_dbtech_credits_transaction_pending

I'm not sure what happened to them. What would be the essayist way to add them? Reinstall the addon? Is there a SQL command I can run in phpmyadmin?

So far I haven't noticed any other problems missing these tables.

 

[Fillip H.]

Sorry, try this:

SELECT SUM(eventid = 42 AND status = 3 AND dateline >= (SELECT dateline FROM xf_dbtech_credits_transaction WHERE eventid = 42 AND userid = 1 AND status IN (1, 2) AND negate = 0 ORDER BY transaction_id DESC LIMIT 1)) AS skipped
FROM xf_dbtech_credits_transaction
WHERE negate = 0
    AND userid = 1

I had to manually edit the query in order to make it compatible with the older version of Credits (since you're using Shop, you can't upgrade to 5.1 yet) and I missed a spot

 

[Rick7C2]

Sorry, try this:

SELECT SUM(eventid = 42 AND status = 3 AND dateline >= (SELECT dateline FROM xf_dbtech_credits_transaction WHERE eventid = 42 AND userid = 1 AND status IN (1, 2) AND negate = 0 ORDER BY transaction_id DESC LIMIT 1)) AS skipped
FROM xf_dbtech_credits_transaction
WHERE negate = 0
    AND userid = 1

I had to manually edit the query in order to make it compatible with the older version of Credits (since you're using Shop, you can't upgrade to 5.1 yet) and I missed a spot

Yes that is correct I haven't upgraded yet. I should have disclosed that.

After running that command I get the following...

Error
SQL query:



SELECT SUM(eventid = 42 AND status = 3 AND dateline >= (SELECT dateline FROM xf_dbtech_credits_transaction WHERE eventid = 42 AND userid = 1 AND status IN (1, 2) AND negate = 0 ORDER BY transaction_id DESC LIMIT 1)) AS skipped
FROM xf_dbtech_credits_transaction
WHERE negate = 0
AND userid = 1 LIMIT 0, 25



MySQL said:

#1054 - Unknown column 'transaction_id' in 'order clause'

Again though. I seem to be missing some tables in my DB if I'm not mistaken...I believe that may be the issue.

 

[Fillip H.]

Sorry, try this:

SELECT SUM(eventid = 42 AND status = 3 AND dateline >= (SELECT dateline FROM xf_dbtech_credits_transaction WHERE eventid = 42 AND userid = 1 AND status IN (1, 2) AND negate = 0 ORDER BY transactionid DESC LIMIT 1)) AS skipped
FROM xf_dbtech_credits_transaction
WHERE negate = 0
    AND userid = 1

I missed another spot

 

[Rick7C2]

I get....

skipped
0

in ssh...

mysql> SELECT SUM(eventid = 42 AND status = 3 AND dateline >= (SELECT dateline FROM xf_dbtech_credits_transaction WHERE eventid = 42 AND userid = 1 AND status IN (1, 2) AND negate = 0 ORDER BY transactionid DESC LIMIT 1)) AS skipped
    -> FROM xf_dbtech_credits_transaction
    -> WHERE negate = 0
    ->     AND userid = 1
    -> ;
+---------+
| skipped |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

 

[Fillip H.]

That means there are no transactions for the Daily Activity newer than the last time they successfully triggered the Daily Activity event.

 

[Rick7C2]

That means there are no transactions for the Daily Activity newer than the last time they successfully triggered the Daily Activity event.

So is that what it should be? I just need it so users get a credit per every 30 days of being active on my site and so far that is not happening.

 

[Fillip H.]

Okay, I see the issue.

The problem is that the Daily, Taxation, Paycheck and Interest events will only set the "last event" flag in the user table if the transaction was successful. This means that if you set Frequency to 30, the user simply has to refresh the page 30 times, then they will get credited as if they had been active for 30 days.

Since this is a showstopping issue, I will break from policy and patch v5.0.3 as well as releasing a new build of v5.1.0. If you re-download v5.0.3, then delete /upload/internal_data/dbtechCreditsUpgrade.lock, then re-upload all files, this should be fixed.

 

[Rick7C2]

I deleted the file and uploaded the new 5.03 to my server.

Not sure if it needed it but It didn't give me an option to update the add-on in admincp.

I changed the frequency to 2 just to test the functionality.

Today is day 2 since I done the above and it still hasn't rewarded a credit yet.

 

[Fillip H.]

There wasn't a version number update so that would make sense.

To test how many skipped events there is, simply do what we did above and record the result.

 

[Rick7C2]

There wasn't a version number update so that would make sense.

To test how many skipped events there is, simply do what we did above and record the result.

It's the same result as before...

mysql> SELECT SUM(eventid = 42 AND status = 3 AND dateline >= (SELECT dateline FROM xf_dbtech_credits_transaction WHERE eventid = 42 AND userid = 1 AND status IN (1, 2) AND negate = 0 ORDER BY transactionid DESC LIMIT 1)) AS skipped
    -> FROM xf_dbtech_credits_transaction
    -> WHERE negate = 0
    ->     AND userid = 1
    -> ;
+---------+
| skipped |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

 

[Fillip H.]

Try only querying for any transaction with status = 3 and see if that works.

 

[Rick7C2]

I ran the following query...

SELECT SUM(status = 3 AND dateline >= (SELECT dateline FROM xf_dbtech_credits_transaction WHERE userid = 1 AND status IN (1, 2) AND negate = 0 ORDER BY transactionid DESC LIMIT 1)) AS skipped
FROM xf_dbtech_credits_transaction
WHERE negate = 0
    AND userid = 1

and got

44

All 44 were from eventID 36

evend id 36 is using the "Viewed" event.

 

[Rick7C2]

Any ideas why this will not award credits as I need it to?

 

[Fillip H.]

My ability to look into issues with the older version is limited, and I'm quite swamped this week so I have nothing new to add at this time.

I'm keeping this ticket open so I can review it as soon as I can.

 

[Rick7C2]

@Fillip H.
It's been a little while. I was wondering if you have any time too look into this?

Thanks

 

[Fillip H.]

Unfortunately I haven't, all my focus has gone into the new versions, sorry

I'm still keeping this ticket open until such a time as the Credits and Shop mods leave Beta, so I won't forget.