/**
* Data saving into the local database
*
* @param $data mixed data to be saved
* @return int array indicating the processing status and data after processing
*/
protected function save($data)
{
//initialize the parameters
$sp_rev_id = "";
$sp_re_password = "";
$sp_id = "";
$service_id = "";
$link_id = "";
$trace_unique_id = "";
$correlator = "";
$message = "";
$sender_address = "";
$dest_address = "";
$date_time = "";
//get the data from array
if (isset($data['spRevId'])) {
$sp_rev_id = $data['spRevId'];
}
if (isset($data['spRevpassword'])) {
$sp_re_password = $data['spRevpassword'];
}
if (isset($data['spId'])) {
$sp_id = $data['spId'];
}
if (isset($data['serviceId'])) {
$service_id = $data['serviceId'];
}
if (isset($data['linkid'])) {
$link_id = $data['linkid'];
}
if (isset($data['traceUniqueID'])) {
$trace_unique_id = $data['traceUniqueID'];
}
if (isset($data['correlator'])) {
$correlator = $data['correlator'];
}
if (isset($data['message'])) {
$message = $data['message'];
}
if (isset($data['senderAddress'])) {
$sender_address = $data['senderAddress'];
}
if (isset($data['smsServiceActivationNumber'])) {
$dest_address = $data['smsServiceActivationNumber'];
}
if (isset($data['dateTime'])) {
$date_time = $data['dateTime'];
}
// add some logic to handle exceptions in this script
$database = null;
try {
$database = DatabaseFactory::getFactory()->getConnection();
} catch (Exception $ex) {
return array('result' => 3, 'resultDesc' => 'Cannot connect to the database. Error: ' . $ex->getMessage());
}
try {
$database->beginTransaction();
$sql = "INSERT INTO tbl_inbound_messages (service_id, link_id, trace_unique_id, correlator, message, sender_address, dest_address, date_time, created_on) VALUES (:service_id, :link_id, :trace_unique_id, :correlator, :message, :sender_address, :dest_address, :date_time, NOW());";
$query = $database->prepare($sql);
$bind_patameters = array(':service_id' => $service_id, ':link_id' => $link_id, ':trace_unique_id' => $trace_unique_id, ':correlator' => $correlator, ':message' => $message, ':sender_address' => $sender_address, ':dest_address' => $dest_address, ':date_time' => $date_time);
if ($query->execute($bind_patameters)) {
//add last insert id, may be used in the next method calls
$data['_lastInsertID'] = $database->lastInsertId();
$row_count = $query->rowCount();
$database->commit();
if ($row_count == 1) {
return array('result' => 0, 'resultDesc' => 'Saving successful', 'data' => $data);
}
} else {
$this->logger->error('{class_mame}|{method_name}|{service_id}|error executing the query|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $database->errorCode(), 'query' => $sql, 'bind_params' => json_encode($bind_patameters)));
return array('result' => 5, 'resultDesc' => 'Error executing a query.');
}
} catch (PDOException $e) {
return array('result' => 4, 'resultDesc' => 'Error executing a query. Error: ' . $e->getMessage());
}
return array('result' => 14, 'resultDesc' => 'Saving record failed', 'data' => $data);
}
/**
* Hook - can be used to forward data to an external system (realtime forwarders)
*
* @param $data mixed data to be processed
* @return int array indicating the processing status and data after processing
*/
protected function hook($data)
{
/* Update outgoing table with the parameters */
//initialize the parameters
$correlator = "";
$dest_address = "";
$delivery_status = "";
$delivery_receipt_id = 0;
//get the data from array
if (isset($data['correlator'])) {
$correlator = $data['correlator'];
}
if (isset($data['address'])) {
$dest_address = $data['address'];
}
if (isset($data['deliveryStatus'])) {
$delivery_status = $data['deliveryStatus'];
}
if (isset($data['_lastInsertID'])) {
$delivery_receipt_id = $data['_lastInsertID'];
}
// add some logic to handle exceptions in this script
$database = DatabaseFactory::getFactory()->getConnection();
$database->beginTransaction();
$sql = "UPDATE tbl_outbound_messages SET delivery_timestamp = NOW(), delivery_status=:delivery_status, delivery_notif_type=2, delivery_receipt_id =:delivery_receipt_id, last_updated_on=NOW() WHERE dest_address=:dest_address AND correlator=:correlator";
// IMPORTANT - note dest_addresses in the where clause (to be visited later)
$query = $database->prepare($sql);
$query->execute(array(':delivery_status' => $delivery_status, ':delivery_receipt_id' => $delivery_receipt_id, ':dest_address' => $dest_address, ':correlator' => $correlator));
$row_count = $query->rowCount();
$data['_recordsUpdated'] = $row_count;
$database->commit();
if ($database->errorCode() != "0000") {
//success
return array("result" => "18", "resultDesc" => "Saving FAILED. Error: " . $database->errorCode(), "data" => $data);
}
//forward the request
if (Config::get('DELIVERY_FORWARDER') == 1) {
//
//initialize the parameters
$time_stamp = "";
$sub_req_id = "";
$trace_unique_id = "";
$correlator = "";
$dest_address = "";
$delivery_status = "";
$id = "";
//get the data from array
if (isset($data['timeStamp'])) {
$time_stamp = $data['timeStamp'];
}
if (isset($data['subReqID'])) {
$sub_req_id = $data['subReqID'];
}
if (isset($data['traceUniqueID'])) {
$trace_unique_id = $data['traceUniqueID'];
}
if (isset($data['correlator'])) {
$correlator = $data['correlator'];
}
if (isset($data['address'])) {
$dest_address = $data['address'];
}
if (isset($data['deliveryStatus'])) {
$delivery_status = $data['deliveryStatus'];
}
if (isset($data['_lastInsertID'])) {
$id = $data['_lastInsertID'];
}
// add some logic to handle exceptions in this script
$database = null;
try {
//$database = SQLSRVDatabaseFactory::getFactory()->getConnection();
$options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING);
$database = new PDO('sqlsrv:Server=SEMATEL-SERVER;Database=db_Sematel', 'sa', 'SematelServer2014', $options);
} catch (Exception $ex) {
return array('result' => 3, 'resultDesc' => 'Cannot connect to the database. Error: ' . $ex->getMessage());
}
$bind_patameters = array();
try {
$database->beginTransaction();
$sql = "INSERT INTO dbo.tbl_delivery_receipts (id, time_stamp, sub_req_id, trace_unique_id, correlator, dest_address, delivery_status, created_on) VALUES (:id, :time_stamp, :sub_req_id, :trace_unique_id, :correlator, :dest_address, :delivery_status, CURRENT_TIMESTAMP);";
$query = $database->prepare($sql);
$bind_patameters = array(':id' => $id, ':time_stamp' => $time_stamp, ':sub_req_id' => $sub_req_id, ':trace_unique_id' => $trace_unique_id, ':correlator' => $correlator, ':dest_address' => $dest_address, ':delivery_status' => $delivery_status);
$this->logger->debug('{class_mame}|{method_name}|{service_id}|forwarding-hook|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'query' => $sql, 'bind_params' => json_encode($bind_patameters)));
if ($query->execute($bind_patameters)) {
$row_count = $query->rowCount();
$database->commit();
if ($row_count == 1) {
return array('result' => 0, 'resultDesc' => 'Forwarding successful', 'data' => $data);
}
} else {
$this->logger->error('{class_mame}|{method_name}|{service_id}|error executing the query|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $database->errorCode(), 'query' => $sql, 'bind_params' => json_encode($bind_patameters)));
return array('result' => 5, 'resultDesc' => 'Error executing a query.');
}
} catch (PDOException $e) {
$this->logger->error('{class_mame}|{method_name}|{service_id}|forwarding-hook|{query}|bind_parameters:{bind_params}|{error}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'query' => $sql, 'bind_params' => json_encode($bind_patameters), 'error' => $e->getMessage()));
return array('result' => 4, 'resultDesc' => 'Error executing a query. Error: ' . $e->getMessage());
}
return array("result" => "19", "resultDesc" => "Forwarding record failed ({$sql})" . $database->errorCode() . " " . $database->errorInfo(), "data" => $data);
}
return array("result" => "0", "resultDesc" => "Hook execution successful", "data" => $data);
}
public function generateDeliveryReceiptsReport($start_date, $end_date, $delivery_status = '', $order = 'DESC')
{
$sql_total = 'SELECT id FROM tbl_delivery_receipts a WHERE a.created_on>:start_date AND a.created_on<=:end_date ';
$sql = 'SELECT date(created_on) as calendar_date, delivery_status, count(id) as message_count FROM tbl_delivery_receipts WHERE created_on>:start_date AND created_on<=:end_date ';
//query for retrieving actual records
$parameters = array(':start_date' => $start_date, ':end_date' => $end_date);
//bind parameters
if (isset($delivery_status) && !empty($delivery_status)) {
//query report for one service
$sql_total .= ' AND delivery_status = :delivery_status ';
$sql .= ' AND delivery_status = :delivery_status ';
$parameters[':delivery_status'] = $delivery_status;
}
$sql .= ' GROUP BY calendar_date, delivery_status ORDER BY calendar_date ' . $order . ' LIMIT 0,30';
$this->logger->debug('{class_mame}|{method_name}|{service_id}|queries|{query_total}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'query' => $sql, 'query_total' => $sql_total, 'bind_params' => json_encode($parameters)));
// add some logic to handle exceptions in this script
$row_count = 0;
$total_records = 0;
$messages = '';
$database = null;
try {
$database = DatabaseFactory::getFactory()->getConnection();
} catch (Exception $ex) {
$this->logger->error('{class_mame}|{method_name}|{service_id}|PDOException|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $e->getMessage()));
return array('result' => 3, 'resultDesc' => 'Cannot connect to the database. Error: ' . $ex->getMessage());
}
try {
//get total records
$query = $database->prepare($sql_total);
if ($query->execute($parameters)) {
$total_records = $query->rowCount();
} else {
$this->logger->error('{class_mame}|{method_name}|{service_id}|error executing the query|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $database->errorCode(), 'query' => $sql_total, 'bind_params' => json_encode($parameters)));
return array('result' => 5, 'resultDesc' => 'Error executing a query.');
}
//get records
$query = $database->prepare($sql);
if ($query->execute($parameters)) {
$messages = $query->fetchAll();
$row_count = $query->rowCount();
if ($row_count > 0) {
return array('result' => 0, 'resultDesc' => 'Records retrieved successfully.', '_recordsRetrieved' => $row_count, '_totalRecords' => $total_records, 'messages' => $messages);
} else {
// IMPORTANT to display not configured services
return array('result' => 0, 'resultDesc' => 'Records retrieved successfully.', '_recordsRetrieved' => $row_count, '_totalRecords' => $total_records, 'messages' => $messages);
}
} else {
$this->logger->error('{class_mame}|{method_name}|{service_id}|error executing the query|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $database->errorCode(), 'query' => $sql, 'bind_params' => json_encode($parameters)));
return array('result' => 5, 'resultDesc' => 'Error executing a query.');
}
} catch (PDOException $e) {
$this->logger->error('{class_mame}|{method_name}|{service_id}|PDOException|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $e->getMessage(), 'query' => $sql, 'bind_params' => json_encode($parameters)));
return array('result' => 4, 'resultDesc' => 'Error executing a query. Error: ' . $e->getMessage());
}
return array('result' => 1, 'resultDesc' => 'No records found.', '_recordsRetrieved' => $row_count, 'messages' => $messages);
}
/**
* Data saving into the local database
*
* @param $data mixed data to be saved
* @return int array indicating the processing status and data after processing
*/
protected function save($data)
{
//initialize the parameters
$subscriber_id = "";
$sp_id = "";
$product_id = "";
$service_id = "";
$service_list = "";
$update_type = "";
$update_time = "";
$update_desc = "";
$effective_time = "";
$expiry_time = "";
$named_parameters = "";
//get the data from array
if (isset($data['ID'])) {
$subscriber_id = $data['ID'];
}
if (isset($data['spID'])) {
$sp_id = $data['spID'];
}
if (isset($data['productID'])) {
$product_id = $data['productID'];
}
if (isset($data['serviceID'])) {
$service_id = $data['serviceID'];
}
if (isset($data['serviceList'])) {
$service_list = $data['serviceList'];
}
if (isset($data['updateType'])) {
$update_type = $data['updateType'];
}
if (isset($data['updateTime'])) {
$update_time = $data['updateTime'];
}
if (isset($data['updateDesc'])) {
$update_desc = $data['updateDesc'];
}
if (isset($data['effectiveTime'])) {
$effective_time = $data['effectiveTime'];
}
if (isset($data['expiryTime'])) {
$expiry_time = $data['expiryTime'];
}
// process named parameters - key value pairs
if (isset($data['key'])) {
$count = $data['repeatedParameters']['key'];
$named_parameters_array = array($data['key'] => $data['value']);
//initial key and value pair
for ($i = 1; $i <= $count; $i++) {
if (isset($data['key' . $i]) && isset($data['value' . $i])) {
$named_parameters_array[$data['key' . $i]] = $data['value' . $i];
}
}
$named_parameters = json_encode($named_parameters_array);
//encode into json string
}
//add some to pull the keyword - required by the application keyword//{"accessCode":"22348","chargeMode":"0","MDSPSUBEXPMODE":"1","objectType":"1","isAutoExtend":"0","shortCode":"22348","isFreePeriod":"false","payType":"0","transactionID":"404090102571507240446506653008","orderKey":"999000000160020096","isSubscribeCnfmFlow":"true","status":"0","validTime":"20361231210000","keyword":"m47","cycleEndTime":"20150823210000","durationOfGracePeriod":"-1","serviceAvailability":"0","channelID":"143","TraceUniqueID":"404090102571507240446506653009","operCode":"operCode","rentSuccess":"true","try":"false"}
$data['keyword'] = '';
//initialize the keyword
if (isset($named_parameters_array['keyword'])) {
$data['keyword'] = $named_parameters_array['keyword'];
}
// add some logic to handle exceptions in this script
$database = null;
try {
$database = DatabaseFactory::getFactory()->getConnection();
} catch (Exception $ex) {
return array('result' => 3, 'resultDesc' => 'Cannot connect to the database. Error: ' . $ex->getMessage());
}
try {
$database->beginTransaction();
$sql = "INSERT INTO tbl_subscription_messages (subscriber_id, sp_id, product_id, service_id, service_list, update_type, update_time, update_desc, effective_time, expiry_time, named_parameters, created_on) VALUES (:subscriber_id, :sp_id, :product_id, :service_id, :service_list, :update_type, :update_time, :update_desc, :effective_time, :expiry_time, :named_parameters, NOW());";
$query = $database->prepare($sql);
$bind_patameters = array(':subscriber_id' => $subscriber_id, ':sp_id' => $sp_id, ':product_id' => $product_id, ':service_id' => $service_id, ':service_list' => $service_list, ':update_type' => $update_type, ':update_time' => $update_time, ':update_desc' => $update_desc, ':effective_time' => $effective_time, ':expiry_time' => $expiry_time, ':named_parameters' => $named_parameters);
if ($query->execute($bind_patameters)) {
//add last insert id, may be used in the next method calls
$data['_lastInsertID'] = $database->lastInsertId();
$row_count = $query->rowCount();
$database->commit();
if ($row_count == 1) {
return array('result' => 0, 'resultDesc' => 'Saving successful', 'data' => $data);
}
} else {
$this->logger->error('{class_mame}|{method_name}|{service_id}|error executing the query|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $database->errorCode(), 'query' => $sql, 'bind_params' => json_encode($bind_patameters)));
return array('result' => 5, 'resultDesc' => 'Error executing a query.');
}
} catch (PDOException $e) {
return array('result' => 4, 'resultDesc' => 'Error executing a query. Error: ' . $e->getMessage());
}
return array("result" => "14", "resultDesc" => "Saving record failed ({$sql})" . $database->errorCode() . " " . $database->errorInfo(), "data" => $data);
}
/**
* getDeliveryMessages - get delivery receipt messages
*
* @param string $start_date start time to be used in filter condition
* @param string $end_date end time to be used in the filter condition
* @param string $subscriber_id subscriber number used in filter condition, default is '' - query all subscribers
* @param string $correlator correlator used to filter, default is '' - return all correlators
* @param int $start_index the start index used in sql, default is 0
* @param int $limit the limit used in sql, default is 10
*
* @return array containing sql result and result data
*/
public function getDeliveryMessages($start_date, $end_date, $subscriber_id = '', $correlator = '', $start_index = 0, $limit = 10, $order = 'DESC')
{
$sql = 'SELECT * FROM tbl_delivery_receipts WHERE created_on>:start_date AND created_on<=:end_date ';
$parameters = array(':start_date' => $start_date, ':end_date' => $end_date);
//include subscriber id filter
if (isset($subscriber_id) && !empty($subscriber_id)) {
$sql = $sql . " AND dest_address=:subscriber_id";
$parameters[':subscriber_id'] = $subscriber_id;
}
//include correlator filter
if (isset($correlator) && !empty($correlator)) {
$sql = $sql . " AND correlator=:correlator";
$parameters[':correlator'] = $correlator;
}
$query_total = $sql;
// copy query to be used to get the total number of reords (without the group by and limit clause)
$sql = $sql . ' ORDER BY id ' . $order . ' LIMIT ' . $start_index . ', ' . $limit;
// add some logic to handle exceptions in this script
$row_count = 0;
$total_records = 0;
$messages = '';
$database = null;
try {
$database = DatabaseFactory::getFactory()->getConnection();
} catch (Exception $ex) {
$this->logger->error('{class_mame}|{method_name}|{service_id}|PDOException|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $e->getMessage()));
return array('result' => 3, 'resultDesc' => 'Cannot connect to the database. Error: ' . $ex->getMessage());
}
try {
//get total records for pagination
$query = $database->prepare($query_total);
if ($query->execute($parameters)) {
$total_records = $query->rowCount();
} else {
$this->logger->error('{class_mame}|{method_name}|{service_id}|error executing the query|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $database->errorCode(), 'query' => $sql, 'bind_params' => json_encode($parameters)));
return array('result' => 5, 'resultDesc' => 'Error executing a query.');
}
//get records
$query = $database->prepare($sql);
if ($query->execute($parameters)) {
$messages = $query->fetchAll();
$row_count = $query->rowCount();
if ($row_count > 0) {
return array('result' => 0, 'resultDesc' => 'Records retrieved successfully.', '_recordsRetrieved' => $row_count, '_totalRecords' => $total_records, 'messages' => $messages);
}
} else {
$this->logger->error('{class_mame}|{method_name}|{service_id}|error executing the query|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $database->errorCode(), 'query' => $sql, 'bind_params' => json_encode($parameters)));
return array('result' => 5, 'resultDesc' => 'Error executing a query.');
}
} catch (PDOException $e) {
$this->logger->error('{class_mame}|{method_name}|{service_id}|PDOException|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $e->getMessage(), 'query' => $sql, 'bind_params' => json_encode($parameters)));
return array('result' => 4, 'resultDesc' => 'Error executing a query. Error: ' . $e->getMessage());
}
return array('result' => 1, 'resultDesc' => 'No records found.', '_recordsRetrieved' => $row_count, 'messages' => $messages);
}
/**
* Writes the new password to the database
*
* @param string $user_name username
* @param string $user_password_hash
* @param string $user_password_reset_hash
*
* @return bool
*/
public static function saveNewUserPassword($user_name, $user_password_hash, $user_password_reset_hash)
{
$database = DatabaseFactory::getFactory()->getConnection();
/*$sql = "UPDATE users SET user_password_hash = :user_password_hash, user_password_reset_hash = NULL,
user_password_reset_timestamp = NULL
WHERE user_name = :user_name AND user_password_reset_hash = :user_password_reset_hash
AND user_provider_type = :user_provider_type LIMIT 1";*/
$sql = "UPDATE tbl_users SET user_password_hash = :user_password_hash, user_password_reset_hash = NULL,\n user_password_reset_timestamp = NULL\n WHERE user_name = :user_name LIMIT 1";
$database->beginTransaction();
$query = $database->prepare($sql);
/*$query->execute(array(
':user_password_hash' => $user_password_hash, ':user_name' => $user_name,
':user_password_reset_hash' => $user_password_reset_hash, ':user_provider_type' => 'DEFAULT'
));*/
$query->execute(array(':user_password_hash' => $user_password_hash, ':user_name' => $user_name));
$row_count = $query->rowCount();
$database->commit();
// if one result exists, return true, else false. Could be written even shorter btw.
return $row_count == 1 ? true : false;
}
/**
* Write remember-me token into database and into cookie
* Maybe splitting this into database and cookie part ?
*
* @param $user_id
*/
public static function setRememberMeInDatabaseAndCookie($user_id)
{
$database = DatabaseFactory::getFactory()->getConnection();
// generate 64 char random string
$random_token_string = hash('sha256', mt_rand());
// write that token into database
$sql = "UPDATE tbl_users SET user_remember_me_token = :user_remember_me_token WHERE user_id = :user_id LIMIT 1";
$sth = $database->prepare($sql);
$sth->execute(array(':user_remember_me_token' => $random_token_string, ':user_id' => $user_id));
// generate cookie string that consists of user id, random string and combined hash of both
$cookie_string_first_part = $user_id . ':' . $random_token_string;
$cookie_string_hash = hash('sha256', $cookie_string_first_part);
$cookie_string = $cookie_string_first_part . ':' . $cookie_string_hash;
// set cookie
setcookie('remember_me', $cookie_string, time() + Config::get('COOKIE_RUNTIME'), Config::get('COOKIE_PATH'));
}
private function getService($service_id)
{
//get the database connection
$database = null;
try {
$database = DatabaseFactory::getFactory()->getConnection();
} catch (Exception $ex) {
return array('result' => 3, 'resultDesc' => 'Cannot connect to the database. Error: ' . $ex->getMessage());
}
//prepare and execute the query
try {
$sql = "SELECT * FROM tbl_services WHERE service_id = :service_id LIMIT 1";
$query = $database->prepare($sql);
$bind_parameters = array(':service_id' => $service_id);
if ($query->execute($bind_parameters)) {
$service = $query->fetch();
if ($query->rowCount() < 1) {
return array('result' => 1, 'resultDesc' => 'Service with id ' . $service_id . ' not found.', 'service' => new stdClass());
} else {
return array('result' => 0, 'resultDesc' => 'Service found.', 'data' => $service);
}
} else {
$this->logger->error('{class_mame}|{method_name}|{service_id}|error executing the query|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $database->errorCode(), 'query' => $sql, 'bind_params' => json_encode($bind_parameters)));
return array('result' => 5, 'resultDesc' => 'Error executing a query.');
}
} catch (PDOException $e) {
return array('result' => 4, 'resultDesc' => 'Error executing a query. Error: ' . $e->getMessage());
}
return array('result' => 7, 'resultDesc' => 'Unknown error', 'data' => new stdClass());
}
/**
* Gets the user's data by user's id and a token (used by login-via-cookie process)
*
* @param $user_id
* @param $token
*
* @return mixed Returns false if user does not exist, returns object with user's data when user exists
*/
public static function getUserDataByUserIdAndToken($user_id, $token)
{
$database = DatabaseFactory::getFactory()->getConnection();
// get real token from database (and all other data)
$query = $database->prepare("SELECT user_id, user_name, user_email, user_password_hash, user_active,\n user_account_type, user_has_avatar, user_failed_logins, user_last_failed_login\n FROM tbl_users\n WHERE user_id = :user_id\n AND user_remember_me_token = :user_remember_me_token\n AND user_remember_me_token IS NOT NULL\n AND user_provider_type = :provider_type LIMIT 1");
$query->execute(array(':user_id' => $user_id, ':user_remember_me_token' => $token, ':provider_type' => 'DEFAULT'));
// return one row (we only have one result or nothing)
return $query->fetch();
}
/**
* deleteService - deletes the service from the system
*
* @return array containing query result and service data
*/
public function getServices($service_id = '', $service_type = '', $short_code = '', $start_index = 0, $limit = 10, $order = 'DESC')
{
$sql = 'SELECT id, service_id, service_name, service_type, short_code, criteria, service_endpoint, delivery_notification_endpoint, interface_name, correlator, status, created_on, last_updated_on, last_updated_by FROM tbl_services WHERE 1 ';
$parameters = array();
//include service_id filter
if (isset($service_id) && !empty($service_id)) {
$sql = $sql . " AND service_id=:service_id";
$parameters[':service_id'] = $service_id;
}
//include service_type filter
if (isset($service_type) && !empty($service_type)) {
$sql = $sql . " AND service_type=:service_type";
$parameters[':service_type'] = $service_type;
}
//include short_code filter
if (isset($short_code) && !empty($short_code)) {
$sql = $sql . " AND short_code=:short_code";
$parameters[':short_code'] = $short_code;
}
$query_total = $sql;
// copy query to be used to get the total number of reords (without the group by and limit clause)
$sql = $sql . ' ORDER BY id ' . $order . ' LIMIT ' . $start_index . ', ' . $limit;
// add some logic to handle exceptions in this script
$row_count = 0;
$total_records = 0;
$services = '';
$database = null;
try {
$database = DatabaseFactory::getFactory()->getConnection();
} catch (Exception $ex) {
$this->logger->error('{class_mame}|{method_name}|{service_id}|PDOException|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $e->getMessage()));
return array('result' => 3, 'resultDesc' => 'Cannot connect to the database. Error: ' . $ex->getMessage());
}
try {
//get total records for pagination
$query = $database->prepare($query_total);
if ($query->execute($parameters)) {
$total_records = $query->rowCount();
} else {
$this->logger->error('{class_mame}|{method_name}|{service_id}|error executing the query|{error}|{query}|bind_parameters:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $database->errorCode(), 'query' => $sql, 'bind_params' => json_encode($parameters)));
return array('result' => 5, 'resultDesc' => 'Error executing a query.');
}
//get records
$query = $database->prepare($sql);
if ($query->execute($parameters)) {
// fetchAll() is the PDO method that gets all result rows
$services = $query->fetchAll();
$row_count = $query->rowCount();
if ($row_count >= 0) {
return array('result' => 0, 'resultDesc' => 'Records retrieved successfully.', '_recordsRetrieved' => $row_count, '_totalRecords' => $total_records, 'services' => $services);
}
} else {
$this->logger->error('{class_mame}|{method_name}|{service_id}|error executing the query|{error}|query:{query}|bind_params:{bind_params}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $database->errorCode(), 'query' => $sql, 'bind_params' => json_encode($parameters)));
return array('result' => 5, 'resultDesc' => 'Error executing a query.');
}
} catch (PDOException $e) {
$this->logger->error('{class_mame}|{method_name}|{service_id}|PDOException|{error}|{query}', array('class_mame' => __CLASS__, 'method_name' => __FUNCTION__, 'error' => $e->getMessage(), 'query' => $sql));
return array('result' => 4, 'resultDesc' => 'Error executing a query. Error: ' . $e->getMessage());
}
return array('result' => 1, 'resultDesc' => 'No records found', 'services' => $services);
}
