smsq/includes/db.php

462 lines
9.4 KiB
PHP
Raw Permalink Normal View History

2017-11-15 05:23:00 +01:00
<?php
try {
$pdo = new PDO($db_dsn,$db_user,$db_pwd,$db_options);
$fpdo = new FluentPDO($pdo);
$fpdo -> debug = function ($q) {
$time = sprintf('%0.3f', $q->getTime() * 1000) . ' ms';
$rows = ($q->getResult()) ? $q->getResult()->rowCount() : 0;
$query = $q->getQuery();
$msg = "# DB query ($time; rows = $rows) : $query";
$parameters = $q->getParameters();
if ($parameters) {
if (is_array($parameters)) {
$msg .= "\n# Parameters: '" . implode("', '", $parameters) . "'";
}
else {
$msg .= "\n# Parameters: '" . varDump($parameters) . "'";
}
}
logging('DEBUG',$msg);
};
}
catch(Exception $e) {
logging('ERROR',"Fail to connect to DB (DSN : '$db_dsn') : ".$e->getMessage());
fatal_error("Impossible de se connecter à la base de données");
}
function db_now() {
// 1970-01-01 00:00:01
return date('Y-m-d G:i:s');
}
function get_incoming_msg($uuid) {
global $fpdo;
$result = $fpdo -> from('incoming_msg')
-> where('uuid=?', $uuid)
-> execute();
if ($result !== false) {
return $result -> fetch();
}
return -1;
}
function get_incoming_msgs($status=false) {
global $fpdo;
$where=array();
if ($status)
$where['status']=$status;
$result = $fpdo -> from('incoming_msg')
-> where($where)
-> execute();
if ($result !== false) {
return $result -> fetchAll();
}
return -1;
}
2017-11-15 05:23:00 +01:00
function create_incoming_msg($number, $text, $timestampms) {
global $fpdo;
$uuid = generate_uuid();
$values = array (
'uuid' => $uuid,
'number' => $number,
'text' => $text,
'timestampms' => $timestampms,
);
$result = $fpdo -> insertInto('incoming_msg',$values)
-> execute();
if ($result === false)
return -1;
return $uuid;
}
function handle_incoming_msg($msg) {
global $fpdo, $mail_sender, $forward_incoming_sms_to;
if (!is_array($msg))
$msg=get_outgoing_msg($msg);
if (is_array($msg)) {
if ($msg['status']=='pending') {
/*
var_dump($msg);
array(6) {
["datetime"]=>
string(19) "2017-11-19 19:05:26"
["uuid"]=>
string(36) "c87e8945-94fd-45db-a32c-e5bf7f42fc28"
["number"]=>
string(12) "+33612345678"
["text"]=>
string(4) "test"
["timestampms"]=>
string(13) "1511112645376"
["status"]=>
string(7) "pending"
}
*/
$subject = "SMS from ".$msg['number'];
$body = "SMS received from ".$msg['number']." at ".$msg['datetime']."\n\n";
$body .= "Text :\n";
$body .= "------\n";
$body .= wordwrap($msg['text'], 70, "\n", true);
$headers = array (
'X-SMS-UUID' => $msg['uuid'],
);
if (send_mail($mail_sender ,$forward_incoming_sms_to ,$subject, $body, $headers) !== True) {
return -1;
}
$result = $fpdo -> update('incoming_msg')
-> set(
array (
'status' => 'delivered',
'lastupdate' => db_now(),
'delivered_datetime' => db_now(),
)
)
-> where(
array (
'uuid' => $msg['uuid'],
)
)
-> execute();
if ($result === false)
return -1;
}
return True;
}
return -1;
}
function handle_incoming_msgs() {
$msgs=get_incoming_msgs('pending');
if (!is_array($msgs))
return -1;
foreach($msgs as $msg) {
if (handle_incoming_msg($msg) !== True)
return -1;
}
return True;
}
2017-11-15 05:23:00 +01:00
function get_outgoing_msg($uuid, $status=false) {
global $fpdo;
$where=array();
if ($uuid)
$where['uuid']=$uuid;
if ($status)
$where['status']=$status;
if (empty($where))
return -1;
$result = $fpdo -> from('outgoing_msg')
-> where($where)
-> execute();
if ($result !== false) {
return $result -> fetch();
}
return -1;
}
function get_outgoing_msg_by_uniqueid($uniqueid) {
global $fpdo;
$where=array('uniqueid' => $uniqueid);
$result = $fpdo -> from('outgoing_msg')
-> where($where)
-> execute();
if ($result !== false) {
return $result -> fetch();
}
return -1;
}
2017-11-19 18:27:57 +01:00
function get_outgoing_msgs($status=false) {
global $fpdo;
$where=array();
if ($status)
$where['status']=$status;
$result = $fpdo -> from('outgoing_msg')
-> where($where)
-> execute();
if ($result !== false) {
return $result -> fetchAll();
}
return -1;
}
2017-11-15 05:23:00 +01:00
function create_outgoing_msg($number, $text) {
global $fpdo;
$uuid = generate_uuid();
$values = array (
'uuid' => $uuid,
'number' => $number,
'text' => $text,
);
$result = $fpdo -> insertInto('outgoing_msg',$values)
-> execute();
if ($result === false)
return -1;
return $uuid;
}
function handle_outgoing_msg($msg) {
if (!is_array($msg))
$msg=get_outgoing_msg($msg);
if (is_array($msg)) {
if ($msg['status']=='pending') {
global $smsgw;
/* Exemple :
{
"number": "0612345678",
"text": "Hello world",
"nbfrag": 1,
"id": "1510712464683"
}
*/
$return=$smsgw->send_sms($msg['number'], $msg['text']);
if (is_array($return)) {
global $fpdo;
$result = $fpdo -> update('outgoing_msg')
-> set(
array (
'status' => 'pushed',
'uniqueid' => $return['id'],
'nbfrag' => $return['nbfrag'],
'lastupdate' => db_now(),
)
)
-> where(
array (
'uuid' => $msg['uuid'],
)
)
-> execute();
if ($result === false)
return -1;
return True;
}
else
return -1;
}
return True;
}
return -1;
}
2017-11-19 18:27:57 +01:00
function handle_outgoing_msgs() {
$msgs=get_outgoing_msgs('pending');
if (!is_array($msgs))
return -1;
foreach($msgs as $msg) {
if (handle_outgoing_msg($msg) !== True)
return -1;
}
return True;
}
function delete_outgoing_msg($msg) {
global $fpdo;
if (!is_array($msg))
$msg=get_outgoing_msg($msg);
if (is_array($msg)) {
if ($msg['uniqueid']) {
$result = $fpdo -> deleteFrom('outgoing_msg_frag')
-> where('msguid', $msg['uniqueid'])
-> execute();
if ($result === false) {
logging('ERROR', 'Error deleting fragments of message '.$msg['uuid']);
return -1;
}
}
$result = $fpdo -> deleteFrom('outgoing_msg')
-> where('uuid', $msg['uuid'])
-> execute();
if ($result === false) {
logging('ERROR', 'Error deleting message '.$msg['uuid']);
return -1;
}
return True;
}
return -1;
}
2017-11-15 05:23:00 +01:00
function get_outgoing_msg_frag($msguid, $fragid) {
global $fpdo;
$where=array(
'msguid' => $msguid,
'fragid' => $fragid,
);
$result = $fpdo -> from('outgoing_msg_frag')
-> where($where)
-> execute();
if ($result !== false) {
return $result -> fetch();
}
return -1;
}
2017-11-19 18:27:57 +01:00
function get_outgoing_msg_frags($msguid) {
global $fpdo;
$where=array(
'msguid' => $msguid,
);
$result = $fpdo -> from('outgoing_msg_frag')
-> where($where)
-> orderBy('fragid')
-> execute();
if ($result !== false) {
return $result -> fetchAll();
}
return -1;
}
2017-11-15 05:23:00 +01:00
function get_outgoing_msg_status_from_frags($msg) {
if (!is_array($msg))
$msg=get_outgoing_msg($msg);
if (!is_array($msg))
return -1;
global $fpdo;
$where=array(
'msguid' => $msg['uniqueid'],
);
$result = $fpdo -> from('outgoing_msg_frag')
-> where($where)
-> execute();
if ($result !== false) {
$frags=$result -> fetchAll();
if (!is_array($frags))
return -1;
logging('DEBUG', "Frags : ".print_r($frags,1));
$frag_states=array('pending','pushed','sent','delivered');
$state_idx=0;
$state_frag_count=0;
foreach($frags as $frag) {
$idx=array_search($frag['status'], $frag_states);
if ($idx !== false) {
if ($idx > $state_idx) {
$state_idx=$idx;
$state_frag_count=1;
}
elseif ($state_idx==$idx)
$state_frag_count++;
}
}
$status=$frag_states[$state_idx];
if ($status=="pending" || $status=="pushed" || $state_frag_count==$msg['nbfrag']) {
return $status;
}
else {
return "partially_$status";
}
}
return -1;
}
function update_outgoing_msg_status($uuid, $status) {
global $fpdo;
$changes=array (
'status' => $status,
'lastupdate' => db_now(),
);
if ($status=='delivered') {
$changes['delivered_datetime'] = db_now();
}
2017-11-15 05:23:00 +01:00
$result = $fpdo -> update('outgoing_msg')
-> set($changes)
2017-11-15 05:23:00 +01:00
-> where(
array (
'uuid' => $uuid,
)
)
-> execute();
if ($result === false)
return -1;
return True;
}
function create_outgoing_msg_frag($msguid, $fragid, $status=false) {
global $fpdo;
$values = array (
'msguid' => $msguid,
'fragid' => $fragid,
);
if ($status)
$values['status']=strtolower($status);
2017-11-15 05:23:00 +01:00
$result = $fpdo -> insertInto('outgoing_msg_frag',$values)
-> execute();
if ($result === false)
return -1;
return true;
}
function update_outgoing_msg_frag($msguid, $fragid, $status) {
global $fpdo;
$frag=get_outgoing_msg_frag($msguid, $fragid);
if ($frag==-1)
return -1;
if (is_array($frag)) {
if ($frag['status'] == 'delivered') {
logging('INFO', "Message $msguid : Fragment $fragid already delivered, ignore ack with status '$status'.");
return True;
}
2017-11-15 05:23:00 +01:00
$result = $fpdo -> update('outgoing_msg_frag')
-> set(
array (
'status' => strtolower($status),
)
)
-> where(
array (
'msguid' => $msguid,
'fragid' => $fragid,
)
)
-> execute();
if ($result === false)
return -1;
return True;
}
else {
return create_outgoing_msg_frag($msguid, $fragid, $status);
}
}
function get_smsq() {
global $fpdo;
$where=array(
'status != ?' => 'delivered',
);
$result = $fpdo -> from('outgoing_msg')
-> where($where)
-> execute();
if ($result !== false) {
return $result -> fetchAll();
}
return -1;
}