smsq/includes/db.php

<?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;
}

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;
}

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;
}

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;
}

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;
}

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;
}


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;
}

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;
}

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();
	}
	$result = $fpdo -> update('outgoing_msg')
					-> set($changes)
					-> 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);

	$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;
		}
		$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;
}