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.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']);
$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&PASSWORD=text&smsprovider=3&goipname=lifecell&smsnum=$phone&method=2&Memo=$mess");
mysqli_query($db, "INSERT INTO admin_actions VALUES('$phone:$mess_', NOW(), 'x.x.x.x', '$uid', '2', NULL, 'sms', '11')");
//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 deleted = 0.
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';
GRANT SELECT, INSERT ON abills.sms TO user@ip;
Or you can write to an existing log table:
mysqli_query($db, "INSERT INTO admin_actions VALUES('$phone:$mess_', NOW(), 'x.x.x.x', '$uid', '2', NULL, 'sms', '11')");
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&PASSWORD=text&smsprovider=3&goipname=lifecell&smsnum=$phone&method=2&Memo=$mess");
mysqli_query($db, "INSERT INTO admin_actions VALUES('$phone:$mess_', NOW(), 'x.x.x.x', '$uid', '2', NULL, 'sms', '11')");
//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&PASSWORD=text&smsprovider=3&goipname=lifecell&smsnum=$phone&method=2&Memo=$mess");
}
}
?>
An example of a script for tariffs with monthly accrual and alignment of the month on the 1st day:
#!/usr/bin/php
<?php
$mysql_host="192.168.1.5";
$mysql_user="ixnfo.com";
$mysql_password="ixnfo.com";
$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 im.activate=0
AND b.deposit+u.credit< tp.month_fee
AND u.reduction=0
AND im.disable=0
AND u.disable=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("first day of next month"));
$fee = intval($Row['month_fee']);
$phone_length=strlen($phone);
if($phone_length==10)
{
$mess_ = "Інтернет відключиться $date_end. На рах. $deposit грн, тариф $fee. ixnfo.com";
$mess = rawurlencode($mess_);
print("$phone $mess_\n");
$r = file_get_contents("https://192.168.1.55/goip/en/dosend.php?USERNAME=text&PASSWORD=text&smsprovider=3&goipname=lifecell&smsnum=$phone&method=2&Memo=$mess");
mysqli_query($db, "INSERT INTO admin_actions VALUES('$phone:$mess_', NOW(), '167772360', '$uid', '2', NULL, 'sms', '11')");
}
}
?>
See also my articles:
SMS sending script via Goip4 gateway
Installing and configuring GoIP SMS server
SQL queries for ABillS