<?php

 ini_set('display_startup_errors',1);
 ini_set('display_errors',1);
 error_reporting(-1);

set_time_limit ( 300 );
ini_set('memory_limit', '512M');

ob_start();

$result= array();

if(!isset($_REQUEST['start']) || $_REQUEST['start'] == '') {
	$validationErr = "Please enter start position!";
} else if(!isset($_REQUEST['length']) || $_REQUEST['length'] == '') {
	$validationErr = "Please enter lenght of data!";
} 

require_once('database.php');

class User{
    //global $sugar_config;

	public $username;
	public $password;
	public $conn;
	public $db;

		
	function __construct() {

		
		$dbObj = new Database();
		
		$this->pdo = $dbObj->getConnection();

		// set the PDO error mode to exception
		$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		$this->pdo->beginTransaction();
		
		
		if(!$this->pdo) {
			$result = array();
			$result['status'] = '0';
			$result['message'] = "APP temporarily Unavailable!";
			print $return = json_encode($result);
			exit;
		}
	
    }

	public function user_list() {

		$user_list = array();

		$sqlEarnBalance = "SELECT * FROM my_registration WHERE 1";

		if(!empty($_REQUEST['user_id']))
			$sqlEarnBalance .= " AND refer_id = ".$_REQUEST['user_id'];

		if(!empty($_REQUEST['searchStr'])){

			//$sqlEarnBalance .= " AND (name LIKE '%".$_REQUEST['searchStr']."%' OR refer_id LIKE '%".$_REQUEST['searchStr']."%' OR sponsor_by LIKE '%".$_REQUEST['searchStr']."%' OR sponsor_id LIKE '%".$_REQUEST['searchStr']."%' ) "; 

			$sqlEarnBalance .= " AND (CONCAT(TRIM(`name`) ,' ', TRIM(`last_name`)) LIKE '%".$_REQUEST['searchStr']."%' OR refer_id LIKE '%".$_REQUEST['searchStr']."%' ) "; 
	
		}


		if(!empty($_REQUEST['orderColName']) && !empty($_REQUEST['orderDir']))
		$sqlEarnBalance .= " ORDER BY TRIM(".$_REQUEST['orderColName'].") ".$_REQUEST['orderDir']." ";


		if(!empty($_REQUEST['start']) && !empty($_REQUEST['length']))

			$sqlEarnBalance .= " Limit ".$_REQUEST['start'].",".$_REQUEST['length'];

		else if(!empty($_REQUEST['length']))
			$sqlEarnBalance .= " Limit ".$_REQUEST['length'];

		// else 
		// 	$sqlEarnBalance .= " Limit 10";

		//echo $user_list = $sqlEarnBalance;exit;
		$ebal = $this->pdo->prepare($sqlEarnBalance);

		$ebal->execute();

		if(isset($_REQUEST['totalRowCount']) && $_REQUEST['totalRowCount']=="yes"){

			$user_list = $ebal->rowCount();
		}
		else{
			while($earnBalance = $ebal->fetch(PDO::FETCH_ASSOC)) {

				//print_r($earnBalance['refer_id']);exit;

				$result = $this->get_member_package($earnBalance['refer_id']);

				//print_r($earnBalance['pack_price'] );exit;
				// $earnBalance['pakg_price_1'] = $result['pakg_price_1'];
				// $earnBalance['pakg_price_2'] = $result['pakg_price_2'];
				$earnBalance = array_merge($earnBalance,$result);
				//print_r($earnBalance);exit();
				array_push($user_list, $earnBalance);
				//print_r($user_list);exit();
			}	
		}
				

		return  $user_list;
	}

	public function get_member_package($refer_id){

		$sqlQuery = "SELECT sum(total_PV) as totalPV, date_entered as purchseDate  FROM my_member_package WHERE refer_id = ".$refer_id." order by date_entered";

		$stmt = $this->pdo->prepare($sqlQuery);

		$stmt->execute();
		$i = 0;
		$data = array();
		while($result = $stmt->fetch(PDO::FETCH_ASSOC)) {

			$data['totalPV'] = empty($result['totalPV']) ? 0 : $result['totalPV']; 
			$data['purchseDate'] = empty($result['purchseDate']) ? 0 : date('d-m-Y H:i:s',strtotime($result['purchseDate'])); 
			$i++;
				
		}	
		return $data;
	}
}

$conn 	= new User();

try {

	$userlist = $conn->user_list();

	$result['status'] = true;

	$result['userlist'] = $userlist;
	
	$conn->pdo->commit();

	echo json_encode($result);
}
catch (Exception $e ){

	$conn->pdo->rollback();

	$result['status'] = false;
	$result['userlist'] = [];
	$result['message'] = "Error While Calling User List API";

	$result['error'] = $e->getMessage();

	echo json_encode($result);
}



?>
