Script to send SMS via Goip4 gateway for ABillS

I will give an example of a PHP script to which I send SMS messages through the GoIP4 gateway.

First, we choose tariffs with only monthly charges:

SELECT name, tp_id FROM tarif_plans;
SELECT name, tp_id FROM tarif_plans WHERE month_fee>0;

Actually, this is the script itself, I run it from cron on a separate server (where I installed the GoIP4 SMS server):

#!/usr/bin/php
<?php
  
$mysql_host="192.168.1.5";
$mysql_user="TEXT";
$mysql_password="TEXT";
$mysql_base="abills";
  
$db=mysqli_connect($mysql_host, $mysql_user, $mysql_password, $mysql_base);
mysqli_query($db, "SET NAMES 'utf8'");
  
$Result = mysqli_query($db,
"
SELECT im.uid, uc.value, b.deposit, im.activate, tp.month_fee
FROM internet_main im
LEFT JOIN users u ON (im.uid = u.uid)
LEFT JOIN users_pi u_pi ON (im.uid = u_pi.uid)
LEFT JOIN bills b ON (u.bill_id= b.id)
LEFT JOIN tarif_plans tp ON (tp.tp_id = im.tp_id)
LEFT JOIN users_contacts uc ON (im.uid = uc.uid)
WHERE tp.month_fee>0
AND tp.abon_distribution=0
AND uc.type_id=1
AND DATEDIFF(CURDATE(), im.activate) = 27
AND b.deposit+u.credit< tp.month_fee
AND u.reduction=0
AND u.company_id=0
AND u_pi._actual_mob=1;
"
);
  
while($Row = mysqli_fetch_array($Result))
{
$uid = $Row['uid'];
$phone =$Row['value'];
$deposit = intval($Row['deposit']);
$activate = $Row['activate'];
$date_end = date("d-m", strtotime("$activate +30 days"));
$fee = intval($Row['month_fee']);
  
$phone_length=strlen($phone);
  
if($phone_length==10)
{
$mess_ = "Інтернет відключиться $date_end. На рах. $deposit грн, тариф $fee. КОМПАНІЯ";
$mess =  rawurlencode($mess_);
  
print("$phone           $mess_\n");
  
$r = file_get_contents("https://192.168.1.55/goip/en/dosend.php?USERNAME=text&amp;PASSWORD=text&amp;smsprovider=3&amp;goipname=lifecell&amp;smsnum=$phone&amp;method=2&amp;Memo=$mess");
mysqli_query($db, "INSERT INTO sms VALUES(NULL, '$uid', '$phone', NOW(), '$mess_', '$activate')");
}
}
?>

There is no “_actual_mob” table in the standard abills database, I have created an additional field with a checkbox that can be used to indicate the relevance of a mobile phone in a user account, 1 is relevant, 0 is not by default.
You can also add another SQL query that removes old records, for example older than 1 year.

For the logs sent by SMS, I created the following table in the ABillS database:

CREATE TABLE IF NOT EXISTS `sms` (
  `id` mediumint(254) NOT NULL AUTO_INCREMENT,
  `uid` mediumint(254) NOT NULL,
  `phone` varchar(32) NOT NULL,
  `datetime` datetime NOT NULL,
  `text` varchar(254) NOT NULL,
  `activate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=384 DEFAULT CHARSET=utf8 COMMENT='Sms log';

In the future, you can view the contents of the table through the module “Report Wizard (rwizard)” or the Setup menu – SQL Commander:

SELECT * FROM sms LIMIT 500
SELECT * FROM sms WHERE uid='%PARAMETER%' ORDER BY `datetime` DESC LIMIT %PG%, %PAGE_ROWS%

I use Zabbix for monitoring, so I’ve also opened the configuration of Zabbix agent:

sudo nano /etc/zabbix/zabbix_agentd.conf

And added a parameter:

UserParameter=sms_sent_today,mysql -u root -e "SELECT count(*) FROM abills.sms WHERE datetime LIKE '`date +%Y-%m-%d`%';" -s -N

And added a data item with an interval of 12 hours, a trigger warning of the excess of the sent SMS, and a schedule on the Zabbix server to see on the chart how many SMS are sent daily.

Example for Daily Charges:

#!/usr/bin/php
<?php
 
$mysql_host="192.168.1.5";
$mysql_user="TEXT";
$mysql_password="TEXT";
$mysql_base="abills";
 
$db=mysqli_connect($mysql_host, $mysql_user, $mysql_password, $mysql_base);
mysqli_query($db, "SET NAMES 'utf8'");

$Result = mysqli_query($db,
"
SELECT im.uid, uc.value, b.deposit
FROM internet_main im
LEFT JOIN users u ON (im.uid = u.uid)
LEFT JOIN users_pi u_pi ON (im.uid = u_pi.uid)
LEFT JOIN bills b ON (u.bill_id= b.id)
LEFT JOIN tarif_plans tp ON (tp.tp_id = im.tp_id)
LEFT JOIN users_contacts uc ON (im.uid = uc.uid)
WHERE tp.day_fee>0
AND uc.type_id=1
AND tp.day_fee*4 > b.deposit
AND tp.day_fee*3 < b.deposit
AND u.credit=0
AND u.reduction=0
AND u.company_id=0
AND u.disable=0
AND im.disable=0
AND u_pi._actual_mob=1;
"
);

while($Row = mysqli_fetch_array($Result))
{
$uid = $Row['uid'];
$phone =$Row['value'];
$deposit = intval($Row['deposit']);
$date_end = date("d-m-Y", strtotime("NOW +3 days"));

$phone_length=strlen($phone);

if($phone_length==10)
{
$mess_ = "Інтернет відключиться $date_end. На рахунку $deposit грн. IXNFO";
$mess =  rawurlencode($mess_);

print("$phone           $mess_\n");

$r = file_get_contents("https://192.168.1.55/goip/en/dosend.php?USERNAME=text&amp;PASSWORD=text&amp;smsprovider=3&amp;goipname=lifecell&amp;smsnum=$phone&amp;method=2&amp;Memo=$mess");
mysqli_query($db, "INSERT INTO sms VALUES(NULL, '$uid', '$phone', NOW(), '$mess_', '$deposit', '0000-00-00')");
}
}
?>

You can also send advertising to clients, for example, my script for fetching phone numbers by districts (districts.id):

#!/usr/bin/php
<?php
$mysql_host="192.168.1.5";
$mysql_user="TEXT";
$mysql_password="TEXT";
$mysql_base="abills";
 
$db=mysqli_connect($mysql_host, $mysql_user, $mysql_password, $mysql_base);
mysqli_query($db, "SET NAMES 'utf8'");
 
$Result = mysqli_query($db,
"
SELECT im.uid, uc.value
FROM internet_main im
LEFT JOIN users u ON (im.uid = u.uid)
LEFT JOIN users_pi u_pi ON (im.uid = u_pi.uid)
LEFT JOIN users_contacts uc ON (im.uid = uc.uid)
LEFT JOIN builds bu ON (u_pi.location_id = bu.id)
LEFT JOIN streets s ON (bu.street_id = s.id)
LEFT JOIN districts d ON (s.district_id = d.id)
WHERE uc.type_id=1
AND im.disable=0
AND u.disable=0
AND u_pi._actual_mob=1
AND d.id IN (2,4,7,11,15,27);
"
);
 
while($Row = mysqli_fetch_array($Result))
{
$uid = $Row['uid'];
$phone =$Row['value'];
 
$phone_length=strlen($phone);
 
if($phone_length==10)
{
$mess_ = "Підключай IP телебачення від ТРК \"IXNFO\", 250 каналів + кінозал";
$mess =  rawurlencode($mess_);
 
print("$phone           $mess_\n");
$r = file_get_contents("https://192.168.1.55/goip/en/dosend.php?USERNAME=text&amp;PASSWORD=text&amp;smsprovider=3&amp;goipname=lifecell&amp;smsnum=$phone&amp;method=2&amp;Memo=$mess");
}
}
?>

See also my articles:
SMS sending script via Goip4 gateway
Installing and configuring GoIP SMS server
SQL queries for ABillS

Did my article help you? How about buying me a cup of coffee as an encouragement? Buy me a coffe.

Leave a comment

Leave a Reply