playSMS Forum

Database cleaning reversing balance

playSMS Help
#1

Hello, I’m having trouble perform the cleanup of the database with phpmyadmin.

I switched to the latest version of playsms, the previous I usually cleaned the tables:
gatewayGeneric_log
tblBilling
tblDLR
tblSMSOutgoing
tblSMSOutgoing_queue
tblSMSOutgoing_queue_dst

Now the latest version can not clear because the customer’s account balance returns to the previous value to submissions.

I noticed that when removing credit transactions on the panel the customer’s balance is also removed.

Example:
Customer purchase 100,000 sms and send … when I do the cleanup the balance of 100,000 returns allowing shipments causing complications.

How can I make the cleanup via phpMyAdmin database keeping the balance left in the account?

(Excuse the English translator …)

#2

hey, no one is going through this problem? :confounded:

#3

Has this problem been addressed ?

#4

No one to solve this case , i have the same problem if i clean the database users credit return to initial state.
Please can anyone help

#5

You have to annotate user balance before cleaning, and reduce credits after.

#6

I have this problem too.
how can clean DB without reverse balance ?

#7

Hi, sadly this can’t be done today.

Up to now playSMS calculates billing from tblBilling join with tblSMSOutgoing, it needs tblSMSOutgoing for uid and parent_uid.

See plugin/feature/credit/fn.php starts from line 211, function _credit_get_billing() and _credit_get_billing_parent(). The SQL query on those 2 functions used to calculate billing.

To minimize space I suppose you can empty the message part on tblSMSOutgoing, but don’t delete the row.

If you have programmer, then perhaps ask them to add uid and parent_uid to tblBilling when billing posted. After that you can modify above functions to calculate billing just from tblBilling.

Billing posted by simplebilling_hook_billing_post() in plugin/feature/simplebilling/fn.php.

anton

1 Like
#8

I made some changes to billing in branch billing_update:

Above changes will work for new installation. If you already have data in your tblSMSOutgoing and tblBilling then you must alter tblBilling (see https://github.com/antonraharja/playSMS/commit/6167ac02f3543bc0a232164cddd1db4539cf173f#diff-8d372647db1396f4ec8d9deb45d654266195244bad2e89eecb84c315062bc5c0), and somehow fill empty uid in tblBilling from tblSMSOutgoing (this I haven’t work on it, but perhaps someone can help).

If everything works as expected then I’ll merge the branch to master and we will have this feature in 1.4.4 later.

anton

1 Like
#9

@anton can you create a summary table to hold billing, failed sms, delivered etc on daily basis as that will really help with the database clean up. and will not affect customer account balance.

e.g. i sometimes do a few millions of sms a month and the details data is not needed after say 45 days so i have to clean the tblSMSOutgoing. and tblBilling tables.