<?php
ini_set('display_errors',0);
ini_set('error_reporting',2047);

class Functional
{
	protected $db;
	private $database;
	
	function __construct() 
	{
		if (isset($_SERVER['OS']) AND $_SERVER['OS'] == 'Windows_NT') {
			$configFile = dirname(dirname(dirname($_SERVER["SCRIPT_FILENAME"]))) . '/app/etc/config.xml';
		} else {
			$configFile = '/var/mikbill/prod/config/admin.xml';
		}
		
		if (file_exists($configFile)) 
		{
			$xml = simplexml_load_file($configFile);
			$this->database = array(
				'host' => $xml->parameters->mysql->host,
				'user' => $xml->parameters->mysql->username,
				'passwd' => $xml->parameters->mysql->password,
				'db' => $xml->parameters->mysql->dbname,
			);
		} else {
			die("config not found");
		}

		try {
			$this->db = new PDO( "mysql:host={$this->database["host"]};dbname={$this->database["db"]}",
				$this->database["user"], $this->database["passwd"],
				array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES koi8r;"));
		}
		catch(PDOException $e) {
			echo $e->getMessage();
		}
	}

	// Отправка запросов на билинг
	public function sendRequest($url, $post = false, $cookie, $data = "")
	{		
		if( $curl = curl_init() ) {
			curl_setopt($curl, CURLOPT_URL, $url);
			curl_setopt($curl, CURLOPT_RETURNTRANSFER,true);
			curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
			curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
			if($post)
				curl_setopt($curl, CURLOPT_POST, true);
			if($data)
				curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
			curl_setopt($curl, CURLOPT_COOKIE, "PHPSESSID=" . $cookie);
			$result = curl_exec($curl);
			curl_close($curl); 
		}
		//var_dump($result);
		return $result;
	}


	public function do_search_by_data()
	{
		$QUERY = "SELECT users_view_fsb.state, users_view_fsb.user, users_view_fsb.uid, users_view_fsb.fio, users_view_fsb.phone,users_view_fsb.mob_tel, users_view_fsb.sms_tel, users_view_fsb.numdogovor, users_view_fsb_address.address
                       FROM users_view_fsb, users_view_fsb_address WHERE 1=1";

        if(isset($_GET['login']) && $_GET['login'] != "")
        {
            $arg1 = $this->db->quote("%{$_GET['login']}%");
            $QUERY .= " AND users_view_fsb.user LIKE $arg1";
        }

		if(isset($_GET['fio']) && $_GET['fio'] != "")
        {
            $arg2 = iconv('UTF-8//IGNORE', 'KOI8-U', $_GET['fio']);
            $arg2 = $this->db->quote("%{$arg2}%");
            $QUERY .= " AND users_view_fsb.fio LIKE $arg2";
        }

		if(isset($_GET['address']) && $_GET['address'] != "")
		{
            $arg3 = iconv('UTF-8//IGNORE', 'KOI8-U', $_GET['address']);
            $arg3 = $this->db->quote("%{$arg3}%");
            $QUERY .= " AND users_view_fsb_address.address LIKE $arg3";
		}

		if(isset($_GET['dogovor']) && $_GET['dogovor'] != "")
        {
            $arg4= $this->db->quote("%{$_GET['dogovor']}%");
            $QUERY .= " AND users_view_fsb.numdogovor LIKE $arg4";
        }

		if(isset($_GET['phone']) && $_GET['phone'] != "")
		{
			$arg5= $this->db->quote("%{$_GET['phone']}%");
			$QUERY .= " AND (users_view_fsb.phone LIKE $arg5
						 OR users_view_fsb.mob_tel LIKE $arg5
						 OR users_view_fsb.sms_tel LIKE $arg5)";
		}

		$QUERY .= "AND users_view_fsb.uid = users_view_fsb_address.uid LIMIT 0, 5";

		$result = $this->db->query($QUERY, PDO::FETCH_LAZY);

		//var_dump($QUERY);

		$data = array();

		foreach($result as $res)
		{
			switch($res["state"])
			{
				case 0:
				{
					$label = "primary"; $text = "Нормальное";
					if($res["blocked"])
					{
						$label = "plain"; $text = "Интернет выкл.";
					}
					break;
				}
				case 1:
				{
					$label = "success"; $text = "Заморожен.";
					break;
				}
				case 2:
				{
					$label = "warning"; $text = "Отключен.";
					break;
				}
				case 3:
				{
					$label = "danger"; $text = "Удален.";
					break;
				}
				default:
					$label = "plain"; $text = "error.";
			}

			$data[] = array(
				'label' => $label,
				'text' => $text,
				'user' => $res['user'],
                'uid' => $res['uid'],
				'fio' => iconv('KOI8-U', 'UTF-8', $res['fio']),
                'phone' => $res['phone'] . ", " . $res['mob_tel'] . ", " . $res['sms_tel'],
                'numdogovor' => $res['numdogovor'],
                'address' => iconv('KOI8-U', 'UTF-8', $res['address']),
				);
		}	

		return $data;
	}

    public function do_search_by_ip()
    {
        $date_start = $_GET["date_start"];
        $date_start = str_replace(' ', '', $date_start);
        $date_start = explode("/", $date_start);
		$date_start = "{$date_start[2]}-{$date_start[0]}-{$date_start[1]} 00:00:00";

        $date_stop = $_GET["date_stop"];
        $date_stop = str_replace(' ', '', $date_stop);
        $date_stop = explode("/", $date_stop);
		$date_stop = "{$date_stop[2]}-{$date_stop[0]}-{$date_stop[1]} 23:59:59";

        $ip =  $this->db->quote($_GET["ip"]);
		$date_start = $this->db->quote($date_start);
		$date_stop = $this->db->quote($date_stop);

		$QUERY_SESS_ONLINE = "SELECT uid FROM radacctbras WHERE acctstarttime >= {$date_start} AND framedipaddress = {$ip}";
		$QUERY_SESS_OFFLINE = "SELECT uid FROM radacct WHERE acctstarttime >= {$date_start} AND acctstoptime <= {$date_stop} AND framedipaddress = {$ip} GROUP BY uid";

		$UID_ONLINE = $this->db->query($QUERY_SESS_ONLINE)->fetchAll(PDO::FETCH_COLUMN);
		$UID_OFFLINE = $this->db->query($QUERY_SESS_OFFLINE)->fetchAll(PDO::FETCH_COLUMN);
		$USERS = array();

		foreach($UID_ONLINE as $uid)
			$USERS[] = $uid;

		foreach($UID_OFFLINE as $uid)
			$USERS[] = $uid;

		$USERS = implode(",", $USERS);

        $QUERY = "SELECT users_view_fsb.state, users_view_fsb.uid, users_view_fsb.user, users_view_fsb.fio, users_view_fsb_address.address
					FROM users_view_fsb, users_view_fsb_address
					WHERE users_view_fsb_address.uid = users_view_fsb.uid AND users_view_fsb.uid IN ({$USERS})";

		$RESULT = $this->db->query($QUERY, PDO::FETCH_LAZY);
        $data = array();
		
        foreach($RESULT as $res)
        {

			switch($res["state"])
			{
				case 0:
				{
					$label = "primary"; $text = "Нормальное";
					if($res["blocked"])
					{
						$label = "plain"; $text = "Интернет выкл.";
					}
					break;
				}
				case 1:
				{
					$label = "success"; $text = "Заморожен.";
					break;
				}
				case 2:
				{
					$label = "warning"; $text = "Отключен.";
					break;
				}
				case 3:
				{
					$label = "danger"; $text = "Удален.";
					break;
				}
				default:
					$label = "plain"; $text = "error.";
			}

            $data[] = array(
				'label' => $label,
				'text' => $text,
                'user' => $res['user'],
                'uid' => $res['uid'],
                'fio' => iconv('KOI8-U', 'UTF-8', $res['fio']),
                'phone' => $res['phone'] . ", " . $res['mob_tel'] . ", " . $res['sms_tel'],
                'numdogovor' => $res['numdogovor'],
                'address' => iconv('KOI8-U', 'UTF-8', $res['address']),
            );
        }

        return $data;
    }

    public function getInfo($uid, $date_start = "", $date_stop = "")
    {

		if(isset($date_start) && $date_start != "")
		{
			$date_start = str_replace(' ', '', $date_start);
			$date_start = explode("/", $date_start);
			$date_start = "{$date_start[2]}-{$date_start[0]}-{$date_start[1]} 00:00:00";
		}
		else
		{
			$date_start = date("Y-m-d", strtotime("-1 months"));
			$date_start = $date_start . " 00:00:00";
		}

		if(isset($date_stop) && $date_stop != "")
		{
			$date_stop = str_replace(' ', '', $date_stop);
			$date_stop = explode("/", $date_stop);
			$date_stop = "{$date_stop[2]}-{$date_stop[0]}-{$date_stop[1]} 23:59:59";
		}
		else
		{
			$date_stop = date("Y-m-d");
			$date_stop = $date_stop . " 23:59:59";
		}

		$uid = $this->db->quote($uid);
		$date_start = $this->db->quote($date_start);
		$date_stop = $this->db->quote($date_stop);

        $USERS = "SELECT state, user, fio, phone, mob_tel, sms_tel, real_ip, framed_ip, local_ip, local_mac,
								passportserie, passportpropiska, passportvoenkomat, passportgdevidan, inn,
								date_birth, numdogovor, use_router, blocked, last_connection from users_view_fsb WHERE uid=$uid";

		$USERS_SESSIONS = "SELECT * FROM radacct WHERE radacct.uid = $uid AND radacct.acctstarttime < $date_stop AND (radacct.acctstoptime > $date_start OR radacct.acctstoptime IS NULL) ORDER BY acctstarttime DESC";
		$QUERY_SESS_ONLINE = "SELECT * FROM radacctbras WHERE acctstarttime >= {$date_start} AND uid = {$uid}";
		$QUERY_SESS_OFFLINE = "SELECT * FROM radacct WHERE acctstarttime >= {$date_start} AND acctstoptime <= {$date_stop} AND uid = {$uid}";

		$SESS_ONLINE = $this->db->query($QUERY_SESS_ONLINE)->fetchAll(PDO::FETCH_ASSOC);
		$SESS_OFFLINE = $this->db->query($QUERY_SESS_OFFLINE)->fetchAll(PDO::FETCH_ASSOC);

		$USERS_PAY = "SELECT * from bugh_plategi_stat WHERE uid=$uid AND DATE >= DATE( NOW( ) - INTERVAL 1 YEAR ) ORDER BY date DESC";
		$USERS_CARDS = "SELECT * FROM  bugh_cards_log WHERE uid=$uid AND DATE >= DATE( NOW( ) - INTERVAL 1 YEAR ) ORDER BY date DESC";

		$USERS_ADDRESS = "SELECT lane, house, app from users_view_fsb_address WHERE uid=$uid";
		$USER_CUSTOM = "SELECT `key`, `value` FROM users_custom_fields WHERE uid=$uid AND `key` IN ('ext_date_fiz_contract_conclusion','ext_passport_date')";

		$result_users = $this->db->query($USERS)->fetchAll(PDO::FETCH_ASSOC);
		$result_session = array_merge($SESS_ONLINE, $SESS_OFFLINE);
		$result_pay = $this->db->query($USERS_PAY)->fetchAll(PDO::FETCH_ASSOC);
		$result_address = $this->db->query($USERS_ADDRESS)->fetchAll(PDO::FETCH_ASSOC);
		$result_custom = $this->db->query($USER_CUSTOM)->fetchAll(PDO::FETCH_ASSOC);
		$result_cards = $this->db->query($USERS_CARDS)->fetchAll(PDO::FETCH_ASSOC);

		$list2 = array();
		if(isset($result_cards) && !is_null($result_cards))
		{
			foreach ($result_cards as $key => $value) {
				$value['card'] = strtr($value['card'], "'", "");

				$list2[$key] = array(
					'date'           => $value['date'],
					'bughtypeid'     => '28',
					'before_billing' => $value['before_billing'],
					'summa'          => $value['summa'],
					'comment'        => 'ID=' . $value['cardslogid'] . ' ' . $value['card']
				);
			}
			$result_users_pay = array_merge($result_pay, $list2);
		}
		else
			$result_users_pay = $result_pay;

		switch($result_users[0]["state"])
		{
			case 0:
			{
				$label = "primary"; $text = "Нормальное";
				if($result_users[0]["blocked"])
				{
					$label = "plain"; $text = "Интернет выкл.";
				}
				break;
			}
			case 1:
			{
				$label = "success"; $text = "Заморожен.";
				break;
			}
			case 2:
			{
				$label = "warning"; $text = "Отключен.";
				break;
			}
			case 3:
			{
				$label = "danger"; $text = "Удален.";
				break;
			}
			default:
				$label = "plain"; $text = "error.";
		}

		$result = $result_users[0];

		//var_dump($result);

		$arg = array();

		if(isset($result_custom[0]) && $result_custom[0] != "")
			$arg[$result_custom[0]["key"]] = $result_custom[0]["value"];

		if(isset($result_custom[1]) && $result_custom[1] != "")
			$arg[$result_custom[1]["key"]] = $result_custom[1]["value"];

		$data = array(
			'profile' => array(
				'text' => $text,
				'label' => $label,
				'last_connection' => $result["last_connection"],
				'lane' => iconv('KOI8-U', 'UTF-8', $result_address[0]["lane"]),
				'house' => iconv('KOI8-U', 'UTF-8', $result_address[0]["house"]),
				'app' => iconv('KOI8-U', 'UTF-8', $result_address[0]["app"]),
				'user' => $result["user"],
				'fio' => iconv('KOI8-U', 'UTF-8', $result["fio"]),
				'phone' => "{$result["phone"]}, {$result["mob_tel"]}, {$result["sms_tel"]}",
				'real_ip' => $result["real_ip"],
				'framed_ip' => $result["framed_ip"],
				'local_ip' => $result["local_ip"],
				'local_mac' => $result["local_mac"],
				'passportserie' => iconv('KOI8-U', 'UTF-8', $result["passportserie"]),
				'passportpropiska' => iconv('KOI8-U', 'UTF-8', $result["passportpropiska"]),
				'passportvoenkomat' => iconv('KOI8-U', 'UTF-8', $result["passportvoenkomat"]),
				'passportgdevidan' => iconv('KOI8-U', 'UTF-8', $result["passportgdevidan"]),
				'passportdate' => $arg["ext_passport_date"],
				'inn' => $result["inn"],
				'date_birth' => $result["date_birth"],
				'numdogovor' => $result["numdogovor"],
				'datedogovor' => $arg["ext_date_fiz_contract_conclusion"],
				'use_router' => $result["use_router"],
			),
			'pay' => $result_users_pay,
			'sessions' => $result_session,
		);
		
		$this->db = null;
        return $data;
    }

	function __destruct() {
       unset($this->db);
   }
}