<?php
// Example of billing requests via API for self-subscription of subscribers to operator service packages via TV screen.

// For self-subscription, the following fields have been added in the API tables:
//
// Field added to subscriber table:
//  'account_balance' - account balance, it is analyzed when the subscriber orders a package on the console.
//
// Fields added to the subscriber_package table:
//  'demanded' (boolean), - the package was ordered by the subscriber. It is set true when the subscriber orders the package, and billing should clear this flag and write off money when updating data.
//  'canceled' (boolean), - the packet was canceled by the subscriber. It is set true when the subscriber is unsubscribing from the package, and billing should clear this flag and take this information into account when updating data.
//  
// Field added to the package table:
//  'hidden' (boolean), - if true, then the package is hidden from self-subscription by subscribers, but you can sign up through the admin panel or subscriber API for the package.
//  
//The logic of work is as follows:
//1. billing with some periodicity (for example, once every 1-2 hours) transmits account balance data (subscriber table, account_balance field, set a numerical value),
//so that this balance is more or less relevant.
//2. The subscriber goes to the "Subscriptions" item and connects a new package.
//2.1. If the difference between the current account of the subscriber and the cost of the package is positive, then the cost of the package is deducted from the current account and the package is included in the subscriber.
//in this case, MW assigns a packet to the subscriber and changes the order flag to true (subscriber_package table, demanded field, set to True).
//2.2. If the difference between the current account of the subscriber and the cost of the package is negative, then the package will not be ordered.
//3. Billing with some periodicity (for example, every 5-10 minutes) makes a request to MW of all new orders (a request from the subscriber_package table of all records with the demanded = True field),
//3.1 checks against its internal logic the possibility of ordering packages by subscribers, and
//3.1.1 if everything is correct, then the billing performs the operation of connecting the package at home.
//3.1.2. if for some reason billing decides that the subscriber cannot be provided with a package, then billing unbinds the subscriber of the package in MW.
//3.2 after processing each order, billing changes the order flag to false
//
//Thus, instant connection of the package to the subscriber is realized when ordering from a TV.
//and billing after some time will update the information about connecting the package and write off the money from the subscriber’s account.


$_iptvportal_server = 'admin.DOMAIN.iptvportal.ru'; // DOMAIN = Your domain in the IPTVPORTAL platform
$_username='admin';
$_password='PASSWORD';


date_default_timezone_set('Europe/Moscow');
set_time_limit(1260);

$_auth_uri='https://'.$_iptvportal_server.'/api/jsonrpc/';
$_jsonsql_uri='https://'.$_iptvportal_server.'/api/jsonsql/';
$_iptvportal_header=null;


function send($url, $data, $extra_headers=null) {
    $ch=curl_init();
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 1260);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_TIMEOUT, 1260);
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
    if (isset($extra_headers)) {
        curl_setopt($ch, CURLOPT_HTTPHEADER, $extra_headers);
    }
    //echo "HTTP fetching '$url'...\n" . chr(10);
    $content=curl_exec($ch);
    $http_code=curl_getinfo($ch, CURLINFO_HTTP_CODE);
    if ($content === false) {
        $err_msg="HTTP error: $http_code (" . curl_error($ch) . ')' . '\n';
        echo $err_msg . chr(10);
        throw new Exception($err_msg);
    }
    if ($http_code != 200) {
        $err_msg="HTTP request failed ($http_code)\n";
        echo $err_msg . chr(10);
        throw new Exception($err_msg);
		print $content;
    }
    //echo "HTTP OK ($http_code)\n";
    curl_close($ch);
    return $content;
}

// end send -------------------------

function jsonrpc_call($url, $method, $params, $extra_headers=null) {
    static $req_id=1;
	if (is_array($method)) {
		$methodlist = $method;
		//echo "<br/>methodlist = ".json_encode($methodlist)."<br/>";
		$req=array();
		foreach ($methodlist as $method_params) {
			$method = $method_params[0];
			$params = $method_params[1];
			//echo "<br/>method = ".json_encode($method)."<br/>";
			//echo "<br/>params = ".json_encode($params)."<br/>";
			array_push($req,array(
            	"jsonrpc"=>'2.0',
            	"id"=>$req_id++,
            	"method"=>$method,
            	"params"=>$params
    		));
		}
	} else {
		//echo "<br/>method1 = ".json_encode($method)."<br/>";
		//echo "<br/>params1 = ".json_encode($params)."<br/>";
    	$req=array(
            "jsonrpc"=>'2.0',
            "id"=>$req_id++,
            "method"=>$method,
            "params"=>$params
    	);
	}
    $req=json_encode($req);
	//echo "<br/>".$req ."<br/>";
    $res=send($url, $req, $extra_headers=$extra_headers);
    //echo "<br/>".$res ."<br/>";
    $res=json_decode($res, true);
    if (!isset($res)) {
        echo "<br/>"."error: not result\n"."<br/>";
        return null;
    } else if (!array_key_exists('result', $res) || !isset($res ['result'])) {
		if (is_array($res)) {
			return $res;
		} else {
			print_r("<br/> Error 'result not found'".$res ['error']."<br/>");
        	return null;
		}
    } else {
        return $res ['result'];
    }
    return $res;
}


function jsonsql_call($cmd, $params = null) {
    global $_jsonsql_uri, $_iptvportal_header;
    //echo 'iptvportal_header: ';	print_r ($_iptvportal_header);
    return jsonrpc_call($_jsonsql_uri, $cmd, $params, $extra_headers=$_iptvportal_header);
}

function authorize_user($auth_uri, $username, $password) {
    global $_iptvportal_header;
    $res=jsonrpc_call($auth_uri, $cmd="authorize_user", $params=array(
            'username'=>$username,
            'password'=>$password
    ));
    if (isset($res) && array_key_exists('session_id', $res)) {
        $_iptvportal_header=array('Iptvportal-Authorization: ' . 'sessionid=' . $res ['session_id']);
    }
    return $res;
}


//The first step is to authorize and get the session key.
$user=authorize_user($auth_uri=$_auth_uri, $username=$_username, $password=$_password);
//echo 'authorize user result: '; print_r ($user); echo '<br> <br>';

function PrintTable($array_table_head, $array_table_values) {
	print_r('<br/><table border=1>');
	
	print_r('<tr>');
	for ($i=0; $i < count($array_table_head); $i++) {
		print_r('<td>');
		print_r($array_table_head[$i]);
		print_r('</td>');
	}
	print_r('</tr>');
	
	foreach ($array_table_values as $array_table_row) {
		print_r('<tr>');
		for ($i=0; $i < count($array_table_row); $i++) {
			print_r('<td>');
			print_r($array_table_row[$i]);
			print_r('</td>');
		}
		print_r('</tr>');
	}
	
	print_r('</table><br/>');
	return true;
}


//start sql select -----------------------------------------------------------------------------------------------------------------------


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('create test-subscribers list with checking of subscriber existence <br/>');
print_r('if the test-subscriber already exists, then updating the information <br/>');

$subscriber_username_password_balance = array(
	array("new_test_subscriber_1","pass111",1000),
	array("new_test_subscriber_2","pass222",2000),
	array("new_test_subscriber_3","pass333",3300),
);


$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "subscriber",
    "columns" => array ("username", "password","account_balance"),
	"select" => array(
		"data" => array ("username", "password","account_balance"),
		"from"=> array(
			"values" => $subscriber_username_password_balance,
			"as"=> array("values_array","username","password","account_balance") // first value = table name. following = column names
		),
		"where" => array(
			"not_in"=> array(
				array("values_array"=>"username"),
				array("select" => array(
					"data" => array ("username"),
					"from"=> array(
						array("table"=> "subscriber", "as"=> "s"),
					),
				)),
			),
		),
	),
    "returning" => array("id","username","account_balance"),
));
array_push($jsonrcp_list,$jsonrcp);

$jsonrcp = array("update", array (
    "table" => array("table"=> "subscriber", "as"=> "s"),
    "set" => array (
        "account_balance" => array('values_array'=>"account_balance"),
    ),
	"from" => array("values"=> $subscriber_username_password_balance, 
					"as"=> array("values_array","username","password","account_balance") ),
    "where" => array ('and'=> array( 
		//array('in'=> array( array('s'=>"username"), array("username_1","username_2","username_3") )), 
		array('eq'=> array( array('s'=>"username"), array('values_array'=>"username") )), 
	)),
    "returning" => array(array('s'=>"id"),array('s'=>"username"),array('s'=>"account_balance")),
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('create test-packages list with checking of package existence <br/>');

$package_name_paid_cost = array(
	array("new_test_package_1",true,100),
	array("new_test_package_2",true,200),
	array("new_test_package_3",true,300),
);


$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "package",
    "columns" => array ("name","paid","cost"),
	"select" => array(
		"data" => array ("name","paid","cost"),
		"from"=> array(
			"values" => $package_name_paid_cost,
			"as"=> array("values_array","name","paid","cost") // first value = table name. following = column names
		),
		"where" => array(
			"not_in"=> array(
				array("values_array"=>"name"),
				array("select" => array(
					"data" => array ("name"),
					"from"=> array(
						array("table"=> "package", "as"=> "p"),
					),
				)),
			),
		),
	),
    "returning" => array("id","name","cost"),
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('Get subscribers list + account_balance <br/>');

$subscriber_username = array(
	"new_test_subscriber_1",
	"new_test_subscriber_2",
	"new_test_subscriber_3",
);

$jsonrcp_list = array();
$jsonrcp = array("select", array(
	"data"=> array(
		array("s"=>"id"), 
		array("s"=>"username"),
		array("s"=>"account_balance"),
		array("s"=>"disabled"),
	),
	"from"=> array(
		array("table"=> "subscriber", "as"=> "s"),
	),
	"where"=> array('and'=> array( 
		//array('eq'=> array( array('s'=>"username"), "new_test_subscriber_1" )), 
		array('in'=> array( array('s'=>"username"), $subscriber_username )), 
	)),
	//"group_by"=>array(),
	//"order_by"=> array()
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		PrintTable($jsonrcp_list[0][1]['data'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('Emulation - set subscriber_package flag demanded=true <br/>');
// ---------- These requests do not need to be implemented on the billing side. 
// ---------- This is only an emulation of the fact that the subscriber ordered the package

$subscriber_username_package_demanded = array(
	array("new_test_subscriber_1","new_test_package_1"),
	array("new_test_subscriber_2","new_test_package_2"),
	array("new_test_subscriber_3","new_test_package_3"),
);


$jsonrcp_list = array();
$jsonrcp = array("insert", array (
    "into" => "subscriber_package",
    "columns" => array ("subscriber_id","package_id","enabled","demanded"),
	"select" => array(
		"data" => array (array('s'=>"id"),array('p'=>"id"),true,true),
		"from"=> array(
			array( "values" => $subscriber_username_package_demanded, 
					"as"=> array("values_array","username","package_name") // first value = table name. following = column names
			),
			array('join'=> "subscriber", 'as'=> "s", 'on'=> 
				array('and'=> array( 
					array('eq'=> array( array('s'=>"username"), array('values_array'=>"username") )),
				))
			),
			array('join'=> "package", 'as'=> "p", 'on'=> 
				array('and'=> array( 
					array('eq'=> array( array('p'=>"name"), array('values_array'=>"package_name") )), 
				))
			),
		),
		"where" => array(
			"not_in"=> array(
				array("concat" => array( array("s"=>"id"),'---',array("p"=>"id") )),
				array("select" => array(
					"data" => array("concat" => array( array("sp"=>"subscriber_id"),'---',array("sp"=>"package_id") )),
					"from"=> array(
						array("table"=> "subscriber_package", "as"=> "sp"),
					),
				)),
			),
		),
	),
    "returning" => array("subscriber_id","package_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$jsonrcp = array("update", array (
    "table" => array("table"=> "subscriber_package", "as"=> "sp"),
    "set" => array (
		"enabled" => true,
		"demanded" => true,
	),
//	"from" => array( 
//		"values" => $subscriber_username_package_demanded, 
//		"as"=> array("values_array","username","package_name") // first value = table name. following = column names
//	),
	"where" => array(
		"in"=> array(
			array("concat" => array( array("sp"=>"subscriber_id"),'---',array("sp"=>"package_id") )),
			array("select"=> array(
				"data" => array("concat" => array( array("s"=>"id"),'---',array("p"=>"id") )),
				"from"=> array( 
					array("values" => $subscriber_username_package_demanded, 
						"as"=> array("values_array","username","package_name") // first value = table name. following = column names
					), 
					array('join'=> "subscriber", 'as'=> "s", 'on'=> 
						array('and'=> array( 
							array('eq'=> array( array('s'=>"username"), array('values_array'=>"username") )),
						))
					),
					array('join'=> "package", 'as'=> "p", 'on'=> 
						array('and'=> array( 
							array('eq'=> array( array('p'=>"name"), array('values_array'=>"package_name") )), 
						))
					),
				),

			))
		)
	
		
	),
    "returning" => array("subscriber_id","package_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}

print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('Get subscribers and subscriber_package + demanded=true <br/>');
$jsonrcp_list = array();
$jsonrcp = array("select", array(
	"data"=> array(
		array("s"=>"id"), 
		array("s"=>"username"),
		array("s"=>"disabled"),
		array("s"=>"account_balance"),
		array("p"=>"id"),
		array("p"=>"name")
	),
	"from"=> array(
		array("table"=> "subscriber_package", "as"=> "sp"),
		array('join'=> "subscriber", 'as'=> "s", 'on'=> 
			array('and'=> array( 
				array('eq'=> array( array('s'=>"id"), array('sp'=>"subscriber_id") )),
			))
		),
		array('join'=> "package", 'as'=> "p", 'on'=> 
			array('and'=> array( 
				array('eq'=> array( array('p'=>"id"), array('sp'=>"package_id") )), 
			))
		),
	),
	"where"=> array('and'=> array( 
		array('eq'=> array( array('sp'=>"demanded"), true )), 
	)),
	//"group_by"=>array(),
	//"order_by"=> array()
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		PrintTable($jsonrcp_list[0][1]['data'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}

print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('Get subscribers and subscriber_package + canceled=true <br/>');
$jsonrcp_list = array();
$jsonrcp = array("select", array(
	"data"=> array(
		array("s"=>"id"), 
		array("s"=>"username"),
		array("s"=>"disabled"),
		array("s"=>"account_balance"),
		array("p"=>"id"),
		array("p"=>"name")
	),
	"from"=> array(
		array("table"=> "subscriber", "as"=> "s"),
		array('join'=> "subscriber_package", 'as'=> "sp", 'on'=> 
			array('and'=> array( 
				array('eq'=> array( array('sp'=>"subscriber_id"), array('s'=>"id") )),
				array('eq'=> array( array('sp'=>"canceled"), true )), 
			))
		),
		array('join'=> "package", 'as'=> "p", 'on'=> 
			array('and'=> array( 
				array('eq'=> array( array('p'=>"id"), array('sp'=>"package_id") )), 
			))
		),
	),
	//"where"=> array('in'=> array( array('s'=>"username"), ['12345','abon1'] ))  , // -- if need select only for users list
	//"group_by"=>array(),
	//"order_by"=> array()
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		PrintTable($jsonrcp_list[0][1]['data'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('update subscriber account_balance <br/>');


$subscriber_username_balance = array(
	array("new_test_subscriber_1",111),
	array("new_test_subscriber_2",222),
	array("new_test_subscriber_3",333),
);


$jsonrcp_list = array();

$jsonrcp = array("update", array (
    "table" => array("table"=> "subscriber", "as"=> "s"),
    "set" => array (
        "account_balance" => array('values_array'=>"account_balance"),
    ),
	"from" => array("values"=> $subscriber_username_balance, 
					"as"=> array("values_array","username","account_balance") ),
    "where" => array ('and'=> array( 
		array('eq'=> array( array('s'=>"username"), array('values_array'=>"username") )), 
	)),
    "returning" => array(array('s'=>"id"),array('s'=>"username"),array('s'=>"account_balance")),
));
array_push($jsonrcp_list,$jsonrcp);
$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


print_r('<br/><br/>------------------------------------------------------------------------------------------------<br/><br/>');
print_r('set subscriber_package flag demanded=false <br/>');


$subscriber_username_package_demanded = array(
	array("new_test_subscriber_1","new_test_package_1"),
	array("new_test_subscriber_2","new_test_package_2"),
	array("new_test_subscriber_3","new_test_package_3"),
);


$jsonrcp_list = array();
$jsonrcp = array("update", array (
    "table" => array("table"=> "subscriber_package", "as"=> "sp"),
    "set" => array (
		"enabled" => true,
		"demanded" => false,
	),
	"where" => array(
		"in"=> array(
			array("concat" => array( array("sp"=>"subscriber_id"),'---',array("sp"=>"package_id") )),
			array("select"=> array(
				"data" => array("concat" => array( array("s"=>"id"),'---',array("p"=>"id") )),
				"from"=> array( 
					array("values" => $subscriber_username_package_demanded, 
						"as"=> array("values_array","username","package_name") // first value = table name. following = column names
					), 
					array('join'=> "subscriber", 'as'=> "s", 'on'=> 
						array('and'=> array( 
							array('eq'=> array( array('s'=>"username"), array('values_array'=>"username") )),
						))
					),
					array('join'=> "package", 'as'=> "p", 'on'=> 
						array('and'=> array( 
							array('eq'=> array( array('p'=>"name"), array('values_array'=>"package_name") )), 
						))
					),
				),

			))
		)
	
		
	),
    "returning" => array("subscriber_id","package_id"),
));
array_push($jsonrcp_list,$jsonrcp);

$res_jsonsql_call=jsonsql_call($jsonrcp_list);

foreach ($res_jsonsql_call as $r) {
	if (isset($r['result'])){
		//print_r($r['result']);
		PrintTable($jsonrcp_list[0][1]['returning'],$r['result']);
	} else {
		print_r("'result' not found: ".json_encode($r['error']) );
	}
}


?>