Custom access to DB data

[MrEyes]

Apologies for the rather vague thread title, the question(s) I have are little difficult to summarise.

Without getting into a very long and boring story, I am looking to retrieve some custom data from the database that sits behind vBulletin. I have spent some time looking around the DB schema, however it seemed sensible to ask the community as some of you have considerably more experience of vB.

I don't need the SQL syntax for the commands, I will be able to work that out. I just need to know if it is possible and if possible what tables to look at.

So, I am looking to extract the following data:

- Total users online in the last [time period].
- All users that have received more than [X] negative rep in the last hour that are not in a specific user group.

Then foreach of the users in that result set, I would like to perform the following queries:

- Total real post count
- User registered time (days).
- Current user reputation score.
- Total negative reputation points received in the last [time period].
- Total positive reputation points received in the last [time period].
- Total posts made by user in the last [time period].
- Total user posts made by user in the last [time period] where neg rep has been given.
- Total user posts made by user in the last [time period] where pos rep has been given.
- Total user posts, in the last [time period], where negative rep received for it is greater than [value]
- Total user posts, in the last [time period], where positive rep received for it is greater than [value]

I use the term [time period] as I am not yet sure what time period I will be using for the queries, most likely and hour but if the db is flexible enough to use 24 hours or 10 minutes that would be a great advantage.

In addition to this is it possible to script a query that will move a user to a specific user group?

I intend to run this set of queries every hour, so if you have any optimisation advice I am all ears (or eyes as the case maybe here :))

The ultimate aim of all this is to produce a cron script that will run within VB, on execution it will calculate a number that will define how well behaved, in the communities eyes, a person has been. In order to achieve this the values that are returned from the queries will be weighted, so for example if a user has received 50 neg reps in the last hour this will be weighted as 10, if only 20 neg reps then it would be weighted as 4. If in the same time period they have received 50 pos reps, then it is weighted as 0 but if no pos reps then it is weighted as 10. After all the weights are calculated, these are summed and a "behaviour" score is created. This behaviour score will then be used to determine which user group somebody belongs to.

Did that last paragraph makes sense ?!

Thanks
MrEyes

BTW This is all for VB 3.6.8 PL2, however when 3.7 goes to stable release I will need to run the same queries.

[Eikinskjaldi]

- Total users online in the last [time period].

This information lives in the session table, and also the lastactivity field of the user table.

All users that have received more than [X] negative rep in the last hour that are not in a specific user group.

reputation lives in the reputation table.

Then foreach of the users in that result set, I would like to perform the following queries:

- Total real post count
- User registered time (days).
- Current user reputation score.
- Total negative reputation points received in the last [time period].
- Total positive reputation points received in the last [time period].
- Total posts made by user in the last [time period].
- Total user posts made by user in the last [time period] where neg rep has been given.
- Total user posts made by user in the last [time period] where pos rep has been given.
- Total user posts, in the last [time period], where negative rep received for it is greater than [value]
- Total user posts, in the last [time period], where positive rep received for it is greater than [value]

post count can be gotten from the post table.
user join details are in user
reputation is in the reupation table.

both reputation and post have a dateline field which contains the time of the event.

I use the term [time period] as I am not yet sure what time period I will be using for the queries, most likely and hour but if the db is flexible enough to use 24 hours or 10 minutes that would be a great advantage.

dateline goes down to the second. you can do whatever time perido you want.

In addition to this is it possible to script a query that will move a user to a specific user group?

Yes. group details are in the user table.

I intend to run this set of queries every hour, so if you have any optimisation advice I am all ears (or eyes as the case maybe here :))

Set it up as a vb cron.

You said you didnt want the queries, so I didn't include them.

[Marco van Herwaarden]

Total users online in the last [time period].

You can have a look at my profile for an example of such code. (Another Members that have Visited Statistics mod (https://vborg.vbsupport.ru/showthread.php?t=135315) )

[MrEyes]

You said you didnt want the queries, so I didn't include them.

Thanks very much for the info, that should be enough to get me started.

I didn't want to be cheeky and post a "do all my thinking for me" style thread, however if you want to post up some queries who am I to argue :D