<?php

/**
 * Name: Dub Knowledge Service API
 * Description: Manages communication between WordPress and Member Portal Database
 * Version: 0.9.8.9
 * Author: Nebu bv
 * Author URI: http://www.nebu.com
 */

require_once 'EncryptQuestURL.php';

class NebuAPIDB {

	private $conn;
	private $driver = null;
	public $error = null;

	public function __construct($host, $database, $username, $password) {
		// check whether there is a suitable SQL Server driver installed
		if(function_exists('sqlsrv_connect')){
			$this->driver = "sqlsrv";
		}
		elseif(function_exists('mssql_connect')){
			$this->driver = "mssql";
		}

		// die if there isn't, try to connect otherwise
		if($this->driver == null){
			$this->error = "No suitable SQL Server driver found";
		}
		else{
			if($this->driver == "sqlsrv"){
				$this->conn = sqlsrv_connect($host, array(
					'Database' => $database,
					'UID' => $username,
					'PWD' => $password,
					'ReturnDatesAsStrings' => true
				));
				if(!$this->conn){
					$this->error = 'Unable to connect to database or database server';
				}
			}
			else{
				$this->conn = mssql_connect($host, $username, $password);
				if(!$this->conn){
					$this->error = 'Unable to connect to database server';
				}
				else{
					if(!mssql_select_db('[' . $database . ']', $this->conn)){
						$this->error = 'Unable to connect to database';
					}
				}
			}
		}
	}

	public function execSP($name, $type, $params, $bUseTransaction = false) {
		$sql = $this->buildSpQuery($name, $type, $params, $bUseTransaction, 'xdb_result');
		$out = array();

		if($this->driver == 'sqlsrv'){
			$result = sqlsrv_query($this->conn, $sql);
			if ($result) {
				do {
					if ($result)
						while ($row = sqlsrv_fetch_array($result)){
							foreach($row as $key=>$el) if (is_numeric($key)) unset($row[$key]);
							$out[] = $row;
						}
				} while(sqlsrv_next_result($result));
			}
		}
		else{
			$result = mssql_query($sql);
			if ($result)
				do {
					if ($result)
						while ($row = mssql_fetch_array($result)){
							foreach($row as $key=>$el) if (is_numeric($key)) unset($row[$key]);
							$out[] = $row;
						}
				} while(mssql_next_result($result));
		}

		if (empty($out)) {
			return $out;
		}

		// if 'type' is passed then we only want the return value
		// otherwise we only want the result set
		if ($type) {
			$out = array($out[count($out)-1]);
		} else {
			unset($out[count($out)-1]);
		}

		return $out;
	}

	private function escape($data) {
		if (!isset($data) or empty($data)) {
			return '';
		}
		if (is_numeric($data)) {
			return $data;
		}

		$non_displayables = array(
			'/%0[0-8bcef]/',    // url encoded 00-08, 11, 12, 14, 15
			'/%1[0-9a-f]/',     // url encoded 16-31
			'/[\x00-\x08]/',    // 00-08
			'/\x0b/',           // 11
			'/\x0c/',           // 12
			'/[\x0e-\x1f]/'     // 14-31
		);
		foreach ($non_displayables as $regex) {
			$data = preg_replace($regex, '', $data);
		}
		$data = str_replace("'", "''", $data );
		return $data;
	}

	private function buildSpQuery($spName, $returnType = null, $params = array(), $bUseTransaction = false, $returnValueName = 'xdb_result') {
		$nl = "\n";

		$sql = "DECLARE	@$returnValueName INT";
		$sp_params = '';
		$out_pre_select = '';
		$out_post_select = '';
		$matches = array();

		$tpconv = array(
			'STR'=>'WNAME'
		);

		$assoc = false;

		foreach ($params as $name => $val) {
			preg_match('/(\\w+)(\\[(\\w+)\\])?/', $name, $matches);

			$is_null = is_null($val);
			if (is_string($val)) {
				$val = "'" . trim($this->escape($val),'"\'') . "'";
			}
			elseif (is_null($val)) {
				$val = 'NULL';
			}
			elseif (is_bool($val)) {
				$val = $val ? 'TRUE' : 'FALSE';
			}

			$name = $matches[1];

			if (isset($matches[3])) {
				//output param found
				$type = $matches[3]; //eg. 'INT'

				if (isset($tpconv[$type])) {
					$type = $tpconv[$type];
				}

				//continuation of the DECLARE part
				$sql .= ',@' . $name . ' ' . $type;

				//SP params: two approaches are possible
				if ($assoc) {
					$sp_params .= '@' . $name . '=@' . $name . ' OUTPUT,';
				} else {
					$sp_params .= '@' . $name . ' OUTPUT,';
				}

				//Initialize the IN/OUT params
				if (!$out_pre_select) {
					$out_pre_select = 'SELECT ';
				}
				$out_pre_select .= '@' . $name . '=' . $val . ',';

				//SELECT part at the end
				$out_post_select .= ',@' . $name . ' AS \'' . $name . '\'';
			} else {
				//SP params: two approach
				if ($assoc) {
					$sp_params .= '@'.$name . '=' . $val . ',';
				} else {
					$sp_params .= $val . ',';
				}
			}
		}

		$sp_params = rtrim($sp_params,',');
		$out_pre_select = rtrim($out_pre_select,',');
		$out_post_select = rtrim($out_post_select,',');

		$sql .= $nl . $out_pre_select;
		$sql .= $nl . "EXEC @$returnValueName = [dbo].[" . $spName . '] ' . $sp_params . $nl;
		$sql .= "SELECT '$returnValueName' = @$returnValueName" . $out_post_select . $nl;

		return $sql;
	}
}

class NebuAPI {

	protected $clients;
	protected $method;
	protected $params;
	protected $panelDB;
	protected $perks;       // Settings for calling the Perks API if the client is integrated with it
	protected $customLangs; // Custom languages for WPML
	protected $dbConn;

	protected static $_instance = null;

	protected function __construct() {
		$this->clients = require 'clients.php';
		$this->authenticate();
		$this->validateRequest();

		if($this->method->getShortName() != "actionCheckPluginCredentials"){
			$conn = new NebuAPIDB($this->panelDB['server'], $this->panelDB['database'], $this->panelDB['username'], $this->panelDB['password']);
			if (!$conn->error) {
				$this->dbConn = $conn;
			}
			else{
				$this->terminate(500, "Internal Server Error", $conn->error);
			}
		}
	}

	protected function __clone() {}

	public static function getInstance() {
		if(!isset(static::$_instance)) {
			static::$_instance = new static;
		}
		return static::$_instance;
	}

	public function dispatchRequest() {
		return $this->method->invokeArgs($this, $this->params);
	}

	protected function terminate($statusCode, $statusMessage, $errorMessage = null) {
		header("HTTP/1.0 {$statusCode} {$statusMessage}");
		if($errorMessage !== null) {
			$result = array(
				'errorCode' => 1,
				'data' => $errorMessage,
			);
			echo json_encode($result);
		}
		exit();
	}

	protected function authenticate() {
		if(!isset($_SERVER['PHP_AUTH_USER'])) {
			header('WWW-Authenticate: Basic realm="Nebu API"');
			$this->terminate(401, 'Unauthorized', '401');
		}
		$user = $_SERVER['PHP_AUTH_USER'];
		$password = $_SERVER['PHP_AUTH_PW'];
		if(!array_key_exists($user, $this->clients) || ($this->clients[$user]['password'] != sha1($password))) {
			$this->terminate(403, 'Forbidden', '403');
		}
		$this->panelDB = $this->clients[$user]['panelDB'];
		$this->perks = isset($this->clients[$user]['perks']) ? $this->clients[$user]['perks'] : false;
		$this->customLangs = isset($this->clients[$user]['WPMLCustomLanguages']) ? $this->clients[$user]['WPMLCustomLanguages'] : array();
	}

	protected function validateRequest() {
		$rawPostData = file_get_contents("php://input");
		$decodedPostData = json_decode($rawPostData, true);
		if(
			$decodedPostData === NULL
			|| !array_key_exists('method', $decodedPostData)
			|| !array_key_exists('params', $decodedPostData)
			|| !is_array($decodedPostData['params'])
		) {
			$this->terminate(400, 'Bad Request');
		}

		$this->method = 'action' . $str = str_replace(' ', '', ucwords(str_replace('_', ' ', $decodedPostData['method'])));
		$params = $decodedPostData['params'];
		$this->params = array();
		try {
			$this->method = new ReflectionMethod(__CLASS__, $this->method);
		} catch(Exception $e) {
			$this->terminate(400, 'Bad Request', 'Method does not exist');
		}

		foreach($this->method->getParameters() as $i => $param) {
			$name = $param->getName();
			if(array_key_exists($name, $params)) {
				$this->params[] = $params[$name];
			} elseif($param->isDefaultValueAvailable()) {
				$this->params[] = $param->getDefaultValue();
			} else {
				$this->terminate(400, 'Bad Request', 'Wrong parameter list');
			}
		}
	}

	protected function getFeatureValue($eid, $fid, $force_value = false){
		if($this->dbConn){
			// no need to check feature existence, result set will just be empty in unhappy cases
			$db_result = $this->dbConn->execSP('get_value_by_entity_id_and_feature_id', null, array(
				'entity_id' => $eid,
				'feature_id' => $fid
			));
			if (is_array($db_result) && count($db_result) > 0){
				$row = $db_result[0];
				return ($force_value || trim($row['value_name']) == '') ? $row['value'] : $row['value_name'];
			}
		}
		// at this point we either don't have a connection or the result set wasn't usable
		return null;
	}

	protected function setFeatureValue($eid, $fid, $value){
		if($this->dbConn){
			$this->dbConn->execSP('set_feature_value', null, array(
				'mode' => 0,
				'id' => $eid,
				'entity_type_id' => 1,
				'user_id' => 0,
				'feature_id' => $fid,
				'value' => $value,
				'validity' => 10
			));
		}
	}

	public function actionAuthenticateUser($username, $password) {
		$result = array(
			'errorCode' => 0,
			'data' => null,
		);

		// default: all panel databases have do_member_login_general
		$sp = 'do_member_login_general';

		// sniffing out SP config from clients.php, is there is any
		if(isset($this->panelDB['loginSP']) && isset($this->panelDB['loginSP']['name'])){
			$sp = $this->panelDB['loginSP']['name'];
		}

		if ($this->dbConn) {
			$db_result = $this->dbConn->execSP($sp, 'int', array(
				'username' => $username,
				'password' => $password,
				'username_feature_id' => 1200,
				'password_feature_id' => 1201,
				'valid_membership_statuses' => $this->panelDB['validMembershipStatuses'],
				'sub_panel_id' => $this->panelDB['subPanelId']
			));
			if (is_array($db_result) && count($db_result) > 0 && array_key_exists('xdb_result', $db_result[0]) && $db_result[0]['xdb_result']) {
				$eid = $db_result[0]['xdb_result'];
				$result['data'] = $eid;

				$this->setFeatureValue($eid, 1260, date("Y.m.d"));
				$loginCount = $this->getFeatureValue($eid, 1261);
				if(!is_numeric($loginCount)){
					$loginCount = 0;
				}
				$this->setFeatureValue($eid, 1261, $loginCount + 1);
			}
		}

		if($result['data'] <= 0) {
			$result['errorCode'] = 1;
		}

		return $result;
	}

	public function actionGetFeatureValue($eid, $fid, $force_value = false) {
		$result = array(
			'errorCode' => 0,
			'data' => $this->getFeatureValue($eid, $fid, $force_value)
		);

		if($result['data'] === null) {
			$result['errorCode'] = 1;
		}

		return $result;
	}

	public function actionGetSurveyList($eid, $status_list) {
		$list = array();
		$result = array(
			'errorCode' => 0,
			'data' => null,
		);

		// sniffing out SP config from clients.php

		// default: all panel databases have do_get_questionnaires
		$sp = 'do_get_questionnaires';

		// default: some panel databases don't have parameter active_questionnaires_only
		// if they do and it's omitted, it defaults to false so it's a safe bet to only send it in case it's true in our config
		$active = false;

		if(isset($this->panelDB['questionnareListSP'])){
			if(isset($this->panelDB['questionnareListSP']['name'])){
				$sp = $this->panelDB['questionnareListSP']['name'];
			}
			if(isset($this->panelDB['questionnareListSP']['activeQuestionnairesOnly'])){
				$active = $this->panelDB['questionnareListSP']['activeQuestionnairesOnly'];
			}
		}

		$spParams = array(
			'entity_id' => $eid,
			'status_list' => $status_list
		);

		if($active === true){
			$spParams['active_questionnaires_only'] = 1;
		}

		// generating list

		if ($this->dbConn) {
			$db_result = $this->dbConn->execSP($sp, null, $spParams);
			if (is_array($db_result)) {
				foreach ($db_result as $row) {
					$listItem = $row;
					if($row['project_application'] == 2) {
						$sub_result = $this->dbConn->execSP('get_invitation_link_by_project_id_and_entity_id', null, array(
							'project_id' => $row['Code'],
							'entity_id' => $eid
						));
						if (is_array($sub_result) && count($sub_result) > 0) {
							$listItem['link'] = $sub_result[0]['invitation_link'];
						}
					} else {
						$listItem['link'] = null;
					}
					$list[] = $listItem;
				}
				$result['data'] = $list;
			}
		}

		if($result['data'] === null) {
			$result['errorCode'] = 1;
		}

		return $result;
	}

	public function actionGetSurveyURL($pid, $eid){
		$result = array(
			'errorCode' => 0,
			'data' => null,
		);

		if ($this->dbConn) {
			$db_result = $this->dbConn->execSP('get_invitation_link_by_project_id_and_entity_id', null, array(
				'entity_id' => $eid,
				'project_id' => $pid
			));
			if (is_array($db_result) && count($db_result) > 0) {
				$row = $db_result[0];
				$result['data'] = trim($row['value_name']) == '' ? $row['value'] : $row['value_name'];
			}
		}

		if($result['data'] === null) {
			$result['errorCode'] = 1;
		}

		return $result;

	}


	public function actionGetPointList($eid, $type, $date_format = false, $start_date = null) {
		$list = array();
		$result = array(
			'errorCode' => 0,
			'data' => null,
		);

		if(!in_array($type, array('all', 'spent', 'earned'))) {
			$type = 'all';
		}

		if($date_format === false && isset($this->panelDB['dateFormat'])){
			$date_format = $this->panelDB['dateFormat'];
		}

		if ($this->dbConn) {
			if($type == 'spent') {
				$sp_name = 'get_spent_points_list';
			} else {
				$sp_name = 'get_earned_points_list';
			}

			$order = 0;
			$asc = 1;
			$page_from = 0;
			$page_rows = 0;
			$language = 0;
			$dateField = "";

			$db_result = $this->dbConn->execSP($sp_name, null, array(
				'person_id'   => $eid,
				'order'       => $order,
				'asc'         => $asc,
				'page_from'   => $page_from,
				'page_rows'   => $page_rows,
				'language_id' => $language,
			));
			if (is_array($db_result)) {
				foreach ($db_result as $row) {
					if($type == 'earned' && $row['point'] < 0) {
						continue;
					}

					$date = $row[$type == 'spent' ? 'date_spent' : 'date_earned'];

					if ($start_date && (strtotime($date) < strtotime($start_date))) {
						continue;
					}

					if($date_format === false){
						$dateField = $date;
					}
					else{
						$dateField = date($date_format, strtotime($date));
					}

					$list[] = array(
						'Date' => $dateField,
						'Description' => $row['name'],
						'Point' => $row['point']
					);
				}
				$result['data'] = $list;
			}
		}

		if($result['data'] === null) {
			$result['errorCode'] = 1;
		}

		return $result;
	}

	public function actionGetLParam($quest, $code, $password) {
		$equ = new EncryptQuestURL();

		$result = array(
			'errorCode' => 0,
			'data' => $equ->EncodeQuestLogin($quest, $code, $password),
		);

		return $result;
	}

	public function actionGetValidMembershipStatuses() {
		return array(
			'errorCode' => 0,
			'data' => array_map('intval', explode(',', $this->panelDB['validMembershipStatuses'])),
		);
	}

	public function actionGetCustomLanguages() {
		return array(
			'errorCode' => 0,
			'data' => $this->customLangs,
		);
	}

	public function actionPerksEnabled() {
		return array(
			'errorCode' => 0,
			'data' => $this->perks !== false,
		);
	}

	/* Generates a one-time login URL via the Perks API using a UserInfo object constructed from panel database information,
	** or falls back to the client's base URL, which leads to Perks' login screen.
	** ErrorCodes: 0 - successful URL generation, 1 - fallback to base URL, 2 - Armageddon
	*/
	public function actionPerksLoginRedirect($eid) {
		// in case perks is not configured (anymore?) in clients.php
		if($this->perks === false){
			$this->terminate(400, "Bad Request", "Perks is not enabled");
		}

		$sendRegEmail = false;
		if(isset($this->perks['sendRegistrationEmail'])){
			$sendRegEmail = $this->perks['sendRegistrationEmail'];
		}

		// init result
		$result = array(
			'errorCode' => 2,
			'data' => null,
		);

		$userInfo = null;
		if($this->dbConn) {

			// DK SP (coming from our DKPerks app) for collecting data needed to construct a Perks UserInfo object
			$db_result = $this->dbConn->execSP('perks_get_userinfo_by_entity_id', null, array(
				'entity_id' => $eid
			));
			if(is_array($db_result) && count($db_result) > 0) {
				$d = $db_result[0];
				$userInfo = array(
					"userInfo" => array(
						"ExternalUserId"               => "" . $eid,        // entity_id as string
						"Username"                     => $d["username"],   // feature #1200
						"FirstName"                    => utf8_encode($d["firstname"]), // #7
						"LastName"                     => utf8_encode($d["lastname"]),  // #9
						"EmailAddress"                 => $d["email"],      // #30
						"Locale"                       => $d["locale"],     // from Perks_countries table by 17 (country feature)
						"EmployeeId"                   => "" . $eid,        // entity_id as string
						"SiteIdentifier"               => $d["siteid"],     // from Perks_countries table
						"CompanyIdentifier"            => $d["companyid"],  // from Perks_countries table
						"LocationIdentifier"           => $d["locationid"], // from Perks_countries table
						"JobRoleIdentifier"            => "Panelist",       // this is surely a Person entity (as of now all portals are b2c)
						"GroupIdentifiers"             => null,             // we send null to avoid overriding and existing groups in Perks
						"ApplicationRoleIdentifiers"   => null,             // we send null to avoid overriding and existing app roles in Perks
						"SendRegistrationNotification" => $sendRegEmail     // if true and the request causes a new user to be registered, Perks will send a register notification mail to #30
					)
				);
			}

			if($userInfo !== null){

				// calling Perks' API to get a one-time login URL for the user
				$perksResult = json_decode(@file_get_contents(
					"" . $this->perks['serviceURL'] . "/WebServices/User/UserService.svc/json/GetSignOnUrl",
					false, // param is used for file reads, not needed for HTTP requests
					stream_context_create(array(
						'http' => array(
							'method' => 'POST',
							'header' => array(
								"Authorization: Basic " . base64_encode($this->perks['serviceUser'] . ":" . $this->perks['servicePassword']),
								"Content-Type: application/json; charset=UTF-8"
							),
							'content' => json_encode($userInfo, JSON_UNESCAPED_UNICODE)
						)
					))
				), JSON_OBJECT_AS_ARRAY);

				if(is_array($perksResult) && isset($perksResult['GetSignOnUrlResult'])){
					$result['errorCode'] = 0;
					$result['data'] = $perksResult['GetSignOnUrlResult'];
				}

			}

		}

		if($result['data'] === null){
			$result['errorCode'] = 1;
			$result['data'] = $this->perks['serviceURL'];
		}

		return $result;
	}

	public function actionCheckPluginCredentials(){
		return array(
			"errorCode" => 0,
			"data" => "check_plugin_credentials"
		);
	}

}

$api = NebuAPI::getInstance();

$result = $api->dispatchRequest();

echo json_encode($result);

?>