Hello!
Thank you for sms server tools!!!

Please, this script is tested and work fine (for me only)!

1. Create new table in database:
CREATE TABLE sms_log ( id serial NOT NULL, sent_time timestamp without time zone, received_time timestamp without time zone, sender character varying(30), receiver character varying(30), msgid character varying(20), "text" text, status character varying(100), create_time timestamp without time zone, CONSTRAINT sms_pkey PRIMARY KEY (id) )

2. Put this script in your script folder for smsd:
This script do TEST for new sms in database! You must put it in you base yourself (INSERT INTO sms_log(receiver,text,sender)...). Then script get one row and createing file in queue for sending, you may send to database many sms, if you want...
#!/bin/sh SQL_TABLE=sms_log SQL_ARGS="eventdb -c " TEST=`sudo -u pgsql psql $SQL_ARGS "select COUNT(id) from $SQL_TABLE WHERE status IS NULL and create_time is NULL;" | sed -n '3p' | sed 's/^[ \t]*//;s/[ \t]*$//'` #echo TEST=$TEST if [ $TEST = "0" ]; then exit 0 fi ID=`sudo -u pgsql psql $SQL_ARGS "select id from $SQL_TABLE WHERE status IS NULL and create_time is NULL ORDER by id desc limit 1;" | sed -n '3p' | sed 's/^[ \t]*//;s/[ \t]*$//'` #echo ID:$ID sudo -u pgsql psql $SQL_ARGS "UPDATE $SQL_TABLE set create_time=current_timestamp where id=$ID;"; TO=`sudo -u pgsql psql $SQL_ARGS "select receiver from $SQL_TABLE WHERE id=$ID;" | sed -n '3p' | sed 's/^[ \t]*//;s/[ \t]*$//'` #Remove plus sign, spaces, minus and short number prefix TO=`echo "$TO" | sed 's/ //g' | sed 's/+//g' | sed 's/s//g' | sed 's/-//g'` #echo TO:$TO TEXT=`sudo -u pgsql psql $SQL_ARGS "select text as tx from $SQL_TABLE WHERE id=$ID;" | sed '$d'|sed '$d'|sed '1,2d' | sed 's/\r//'` #echo TEXT:$TEXT TMPFILE=`mktemp -t smspgsql` || exit 1 echo "To:$TO" > $TMPFILE echo "Id:$ID" >> $TMPFILE echo "" >> $TMPFILE echo "$TEXT" >> $TMPFILE echo $TMPFILE cat $TMPFILE cp $TMPFILE /var/spool/sms/outgoing/ rm $TMPFILE

3. Script for insert status of sended message
This script is eventhandler for smsd. But this script put only CURRENT_TIMESTAMP in status report....
#!/bin/sh SQL_TABLE=sms_log SQL_ARGS="eventdb -c " FROM=`formail -zx From: < $2 | sed 's/"//g'` TO=`formail -zx To: < $2` TO=`echo "$TO" | sed 's/ //g' | sed 's/+//g' | sed 's/s//g' | sed 's/-//g'` SUBJECT=`formail -zx Subject: < $2` SENT=`formail -zx Sent: < $2` #TEXT=`formail -I "" <$2 | tr "\n" " "` ID=`formail -zx Id: < $2` #echo get id:$ID #Insert a new entry into the SQL table if [ "$1" = "FAILED" ]; then sudo -u pgsql psql $SQL_ARGS "UPDATE $SQL_TABLE set sent_time=current_timestamp, msgid='$3', status='6' where id=$ID;"; elif [ "$1" = "SENT" ]; then sudo -u pgsql psql $SQL_ARGS "UPDATE $SQL_TABLE set sent_time=current_timestamp, msgid='$3', status ='5' where id=$ID;"; elif [ "$1" = "REPORT" ]; then #Extract more data from the status report file #DISCHARGE=`sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Discharge_timestamp:` MSGID=`sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Message_id:` STATUS=`sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Status: | cut -f1 -d,` if [ "$MSGID" != "" ]; then ID=`sudo -u pgsql psql $SQL_ARGS "select id from $SQL_TABLE WHERE msgid='$MSGID' and received_time is NULL ORDER by id desc limit 1;" | sed -n '3p' | sed 's/^[ \t]*//;s/[ \t]*$//'` sudo -u pgsql psql $SQL_ARGS "update $SQL_TABLE set received_time=current_timestamp,status='$STATUS' where id=$ID;" fi fi

Please if you know how to improve this script - POST!
If you find any err - POST!
Thank you!

Hi Friends


I am new to this sms server tools and i just want to know whether your script works in suse 11 desktop with postgres 8.2 database.

If yes then can you please give me detailed explanation for running this service. I need to know about this service in detail.

And i also need to receive incoming sms request.

Regards,
rrpraveenraja