/** * 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); }