View Single Post
Posts: 539 | Thanked: 165 times | Joined on Feb 2010 @ Berlin, Germany
#10
As there just has been the question of how to adapt this script to his own needs, I'll try to explain by example how the extraction of the neccessary information works.

First of all: it all depends on the structure of the SMS you receive with the account balance or data plan balance (you lucky guys, I have no chance to get the data plan usage by SMS).

As an example, this is the SMS I receive when calling *100#:
Code:
Ihr Guthaben für <HEREISMYPHONENUMBER> berträgt <XX,YY> EUR.
Now let's dissect the skript from post #1. We can divide it into three separate commands which are linked together by using the pipe command '|'. First command is the sqlite SELECT-Statement.

We are using the database from RTCOM eventlogger, where the SMS are stored:
Code:
sqlite3 -separator ": " /home/user/.rtcom-eventlogger/el.db
Then we define to read only the fields containing date (storage_time) and content of SMS (free_text). The transformation of the timestamp is needed because the time is stored in unixepoch, which means in seconds since 1.1.1970. This is the first point one might change. In my script the date is displayed as DD.MM.YYY which is a typical german notation. Others might want to change this to the format MM.DD.YYYY (%m.%d.%Y) or even to the ISO conforming YYY-MM-DD (%Y-%m-%d). And users who frequently check their balance might want to add the time: HH:MM would be an additional %h:%s. I guess the 'localtime' statement is needed for most users, as the time is stored in UTC and not being automatically converted to local timezone.
Code:
SELECT strftime('%d.%m.%Y',storage_time,'unixepoch','localtime'), free_text
Next we select to only look at incoming SMS (type 7) and within these only the ones containing the string "Ihr Guthaben" in the beginning of the text (compare with the example SMS above!). This might lead to false results if someone sends me a SMS beginning with the same words. But thats very unlikely to happen. I could also try to match the whole SMS but this could be tricky regarding the german umlauts. One solution would be to also match the end of the SMS, which in my case ends with "EUR.": "Ihr Guthaben%EUR.". Finally the ORDER BY and LIMIT statements just order all these matching SMS by date, newst first and only pick the first of the list, which by definition is the newest one.
Code:
FROM Events WHERE event_type_id = '7' AND free_text LIKE 'Ihr Guthaben%' ORDER BY end_time DESC LIMIT 1"
Now the first sed statement. It just strips everything from the beginning of the SMS until the end of the word "beträgt" which is then followed by the actual balance which we will keep in output.
Code:
sed s/Ihr.*gt\ //
The second statement replaces the letter-coded currency EUR and the trailing dot by the symbol €.
Code:
sed s/.EUR./€/
That's it!

The output in this example looks as follows:

Code:
04.03.2010: 17.21€
Next will be an example which also incudes the data plan usage.

Last edited by x-lette; 2010-03-07 at 15:52. Reason: added example output
 

The Following User Says Thank You to x-lette For This Useful Post: