um);

			$Addr[] = sprintf("%s", $Postaddr);
			$Addr[] = sprintf("%s", $CountryName);
		}
		return $Addr;
	}

	public static function InvoiceAddresses($UserId = null){
		$dbh = Database::getInstance();
		
		$query = "
			SELECT 
			`ID`, `Type`, `Default`, `Name`, `Addr1`, `Addr2`, `Postnum`, `Postaddr`, `Country`, `Email`, 
			(SELECT `name` FROM `system_country` WHERE CONVERT(`iso_code` USING `utf8`) COLLATE `utf8_unicode_ci` = `Country`) `CountryName` 
			FROM `school_user_addr` 
			WHERE `UserId` = ? 
			ORDER BY `ID` 
		";
		$pst = $dbh->prepare($query);
		$pst->execute(array($UserId));
		if($pst->rowCount() === 0) return false;
		
		$Data = array();
		while($res = $pst->fetch(PDO::FETCH_ASSOC)){
			$Data[$res['ID']] = array(
				"Type" => (int)$res['Type'],
				"Default" => (int)$res['Default'],
				"Name" => $res['Name'],
				"Addr1" => $res['Addr1'],
				"Addr2" => $res['Addr2'],
				"PostNum" => $res['Postnum'],
				"PostAddr" => $res['Postaddr'],
				"Country" => array("Iso" => $res['Country'], "Label" => $res['CountryName']),
				"Email" => $res['Email']
			);
		}
		return $Data;
	}

	public static function InvoiceDefault($UserId = null){
		$dbh = Database::getInstance();

		$query = "
			SELECT 
			`ID`, `Type`, `Default`, `Name`, `Addr1`, `Addr2`, `Postnum`, `Postaddr`, `Country`, 
			(SELECT `name` FROM `system_country` WHERE CONVERT(`iso_code` USING `utf8`) COLLATE `utf8_unicode_ci` = `Country`) `CountryName` 
			FROM `school_user_addr` 
			WHERE `UserId` = ? 
			AND `Default` = 1 
		";
		
		$pst = $dbh->prepare($query);
		$pst->execute(array($UserId));
		if($pst->rowCount() === 0) return false;

		$res = $pst->fetch(PDO::FETCH_ASSOC);
		return array(
			"Type" => (int)$res['Type'],
			"Default" => (int)$res['Default'],
			"Name" => $res['Name'],
			"Addr1" => $res['Addr1'],
			"Addr2" => $res['Addr2'],
			"PostNo" => $res['Postnum'],
			"PostAddr" => $res['Postaddr'],
			"Country" => array("Iso" => $res['Country'], "Label" => $res['CountryName'])	
		);

		$pst = null;
		return false;
	}

	public static function Types(){
		return [ 
			1 => [ 'Name' => _('Deltagare/person'), 'Desc' => _('Person som deltar i verksamheten.') ],
			2 => [ 'Name' => _('Kund/förening'), 'Desc' => _('Kund/förening som kan faktureras via webbshop.') ],
		];
	}

	public static function Levels(){
		return [ 
			0 => [ 'Name' => _('Deltagare/Ledare'), 'Desc' => _('Kan logga in på hemsidan, göra aktivitetsbokning mm. Aktiva ledare kan hantera sina aktiviteter/tillfällen.') ],
//			1 => [ 'Name' => _('Ledare'), 'Desc' => _('Åtkomst till ledarsidorna / närvarorapportering mm.') ],
			2 => [ 'Name' => _('Administratör'), 'Desc' => _('Åtkomst till administrationen.') ],
		];
	}

	public static function Level($Level){
		switch($Level){
			case 2 : return _("Administratör"); break;	
			case 1 : return _("Ledare");
			default : return _("Deltagare");
		}
	}

	public static function LastVisit($UserId){
		$dbh = Database::getInstance();

		$query = "
			SELECT 
			`log_datetime` `LV` 
			FROM `school_user_log` 
			WHERE `log_action` = 2 
			AND `log_user` = :UserId 
			ORDER BY `log_datetime` DESC 
			LIMIT 2
		";
		$pst = $dbh->prepare($query);
		$pst->bindParam(":UserId", $UserId, PDO::PARAM_INT);
		$pst->execute();
		if($pst->rowCount() === 0) return false;
		
//		echo sprintf("<pre>%s</pre>", print_r($pst->fetchAll(PDO::FETCH_ASSOC), true));
//		exit;

		$R = $pst->fetchAll(PDO::FETCH_NUM);
		$L = end($R);
//		echo sprintf("<pre>%s</pre>", print_r($, true));
//		exit;
		return strftime(DATE_DAY_MONTH_YEAR_HOUR_MINUTE, $L[0]);
		
	}

	/*	get user info
	-------------------------------------------------------------*/
	public static function GetUserInfo($UserId = null){
		global $__AbsUrl;
		$dbh = Database::getInstance();

		$query = "
			SELECT 
			`user_id`, `user_pers_number`, `user_name`, `user_type`, `user_group`, `user_level`, `user_mem_num`, `user_lic_num`, `user_firstname`, `user_lastname`, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(`user_birthdate`)), '%Y')+0 AS age, `user_birthdate`, `user_gender`, `user_phone1`, `usereadmore}}
		{{{ text_ext }}}
		{{/readmore}}
	</div>
	{{#social}}
	<div class="social float-left">
		{{#facebook_simple}}
		<a href="{{url}}" onclick="popIt('https://www.facebook.com/sharer/sharer.php?u=' + this.href, 'Facebook', 600, 400);return false;" class="FBShare" data-toggle="tooltip" title="Dela på Facebook"><i class="fa fa-facebook" aria-hidden="true"></i> <span>Dela</span></a>
		{{/facebook_simple}}
		{{#facebook}}
		<div class="fb-like" 
			data-href="{{url}}" 
			data-width="100" 
			data-layout="button" 
			data-action="like" 
			data-size="large" 
			data-share="true"
		></div><br /><br />
		{{/facebook}}
		{{#twitter}}
		<a href="https://twitter.com/share?url={{url}}&amp;text={{title}}&amp;hashtags={{tags}}" class="TWShare" data-toggle="tooltip" title="Dela på Twitter"><i class="fa fa-twitter" aria-hidden="true"></i></a>
		{{/twitter}}
	</div>
	{{/social}}
	{{#readmore}}
	<div class="float-right">
		<a href="{{url}}" class="btn btn-sm btn-secondary float-right" title="{{i18n.readmore}}">{{i18n.readmore}}</a>
	</div>
	{{/readmore}}
	<div class="clearfix"></div>
</div>
{{/newslist}}
</div>
EOT;

public $template_single = <<<EOT
{{#newslist}}
<div class="NewsItem">
	<header class="clearfix">
	
		{{#event_date}}
		<ul class="list-inline mb-0">
			{{#event_daymonthyear}}
			<li class="list-inline-item">
				<small>
				<i class="fa fa-calendar-o fa-fw" aria-hidden="true"></i> {{event_daymonthyear}} 
				</small>
			</li>
			{{/event_daymonthyear}}
			
			{{#event_time}}
			<li class="list-inline-item create-time"><small><i class="fa fa-clock-o" aria-hidden="true"></i> {{event_time}}</small></li>
			{{/event_time}}
			
			{{#tags}}
			<li class="list-inline-item"><small><i class="fa fa-tags fa-fw" aria-hidden="true"></i> {{tags}}</small></li>
			{{/tags}}
		</ul>
			
		{{/event_date}}
		{{^event_date}}
		<!--
		<h4 class="m-0">
			<a href="{{url}}" class="text-reset">
				{{#private}}
				<i class="fa fa-lock fa-fw" aria-hidden="true"></i>
				{{/private}}
				{{title}}
			</a>
		</h4>
		-->
		<ul class="list-inline mb-0">
			{{#create_date}}
			<li class="list-inline-item"><small>{{create_date}} {{#create_time}}<span class="create-time"><i class="fa fa-clock-o" aria-hidden="true"></i> {{create_time}}{{/create_time}}</span></small></li>
			{{/create_date}}
			{{#tags}}
			<li class="list-inline-item"><small><i class="fa fa-tags fa-fw" aria-hidden="true"></i> {{tags}}</small></li>
			{{/tags}}
		</ul>
		
		{{/event_date}}
	</header>
	
	<div class="news-content">
		{{#image}}
		<p><img src="{{image}}" alt="image: {{title}}" class="img-thumbnail"></p>
		{{/image}}
		{{#show_excerpt}}<div class="news-excerpt">{{/show_excerpt}}{{{ text }}}{{#show_excerpt}}</div>{{/show_excerpt}}
		{{^readmore}}
		{{{ text_ext }}}
		{{/readmore}}
	</div>
	{{#social}}
	<div class="social float-left">
		{{#facebook_simple}}
		<a href="{{url}}" onclick="popIt('https://www.facebook.com/sharer/sharer.php?u=' + this.href, 'Facebook', 600, 400);return false;" class="FBShare" data-toggle="tooltip" title="Dela på Facebook"><i class="fa fa-facebook" aria-hidden="true"></i> <span>Dela</span></a>
		{{/facebook_simple}}
		{{#facebook}}
		<div class="fb-like" 
			data-href="{{url}}" 
			data-width="100" 
			data-layout="button" 
			data-action="like" 
			data-size="large" 
			data-share="true"
		></div><br /><br />
		{{/facebook}}
		{{#twitter}}
		<a href="https://twitter.com/share?url={{url}}&amp;text={{title}}&amp;hashtags={{tags}}" class="TWShare" data-toggle="tooltip" title="Dela på Twitter"><i class="fa fa-twitter" aria-hidden="true"></i></a>
		{{/twitter}}
	</div>
	{{/social}}
	{{#readmore}}
	<div class="float-right">
		<a href="{{url}}" class="btn btn-sm btn-secondary float-right" title="{{i18n.readmore}}">{{i18n.readmore}}</a>
	</div>
	{{/readmore}}
	<div class="clearfix"></div>
</div>
{{/newslist}}
EOT;

public $template_archive = <<<EOT
<div class="widget-news-archive">
{{#recent_has_items}}
<ul class="news_archive list-unstyled">
{{#recent}}
<li><a href="{{url}}">{{label}}</a><span class="badge float-right">{{ant}}</span></li>
{{/recent}}
</ul>
{{/recent_has_items}}
{{#history_has_items}}
<div class="collapse" id="fullArchive">
<ul class="news_archive list-unstyled">
{{#history}}
<li><a href="{{url}}">{{label}}</a><span class="badge float-right">{{ant}}</span></li>
{{/history}}
</ul>
</div>
<button class="btn btn-secondary btn-sm float-right" type="button" data-toggle="collapse" data-target="#fullArchive" aria-expanded="false" aria-controls="fullArchive">{{i18n.expand}}</button>
<div class="clearfix"></div>
{{/history_has_items}}
</div>
EOT;

	function __construct(){
		$this->dbh = Database::getInstance();
		$query = "SELECT `config_key`, `config_value` FROM `system_config` WHERE `config_key` IN('SOCIAL_TWITTER','SOCIAL_FACEBOOK','FACEBOOK_APP_ID')";
				
		foreach($this->dbh->query($query, PDO::FETCH_ASSOC) as $res){
			if($res['config_key'] === 'SOCIAL_TWITTER' && (int)$res['config_value'] === 1){
				$this->twitter = 1;

			}elseif($res['config_key'] === 'SOCIAL_FACEBOOK' && (int)$res['config_value'] === 1){
				$this->facebook = 1;

			}elseif($res['config_key'] === 'FACEBOOK_APP_ID' && !is_null($res['config_value'])){
				$this->facebook_app_id = 1;
			}
		}
	}

	private function tags_to_query($Tags){
		$D = [];
		foreach($Tags as $T){
			if((int)$T === 0) continue;
			$D[] = (int)$T;
		}
		return implode(',', $D);
	}

	public function get_tags($NewsId = 0){
		$pst = $this->dbh->prepare("
			SELECT 
			`a`.`Name` 
			FROM `site_news_tags` `a`
			INNER JOIN `site_news_tag` `b` 
			ON `a`.`TagId` = `b`.`TagId` 
			WHERE `b`.`NewsId` = ? 
			ORDER BY CONVERT(`a`.`Name` USING `latin1`) COLLATE `latin1_swedish_ci`
		");

		$pst->execute( [ $NewsId ] );

		if($pst->rowCount() === 0) return false;
		
		$Data = [];
		while($res = $pst->fetch(PDO::FETCH_ASSOC)){
			$Data[] = $res['Name'];
		}
		$pst = null;
		return implode(', ', $Data);
	}

	public function all_tags(){
		$query = "
			SELECT 
			COUNT(`b`.`NewsId`) `Ant`, `a`.`TagId`, `a`.`Name` 
			FROM `site_news_tags` `a`
			INNER JOIN `site_news_tag` `b` 
			ON `a`.`TagId` = `b`.`TagId` 
		";
		if(!is_null($this->period)){
			$query .= "
				INNER JOIN `site_news` 
				ON `news_id` = `b`.`NewsId` 
				WHERE DATE_FORMAT(`news_date`, '%Y-%m') = '{$this->period}' 
			";
		}
		$query .= "
			GROUP BY `b`.`TagId` 
			ORDER BY CONVERT(`a`.`Name` USING `latin1`) COLLATE `latin1_swedish_ci`
		";

		$result = mysql_query($query) or die((DEBUG ? sprintf('<h3>%s</h3><p>%s</p><pre>%s</pre>', _('Ett fel uppstod'), mysql_error(), str_replace("\t", "", $query)) : _('Ett fel uppstod, kontakta supporten.')));
		if(mysql_num_rows($result) === 0) return false;
		
		$__CookieName = sprintf(NAME_NEWS_FILTER, $this->page_id);

		$Active = 0;
		if(isset($_COOKIE[$__CookieName]))
			$Active = (int)$_COOKIE[$__CookieName];

		$Data = array();
		while($res = mysql_fetch_assoc($result)){
			$Data[] = array("Id" => (int)$res['TagId'], "Name" => $res['Name'], "Ant" => (int)$res['Ant'], "Active" => ((int)$res['TagId'] === $Active));
		}
		mysql_free_result($result);

		return $Data;
	}

	public function news($Limit = 5){
		global $__AbsUrl;
		$params = [];
		$conds = [];
		
		$query = "
			SELECT DISTINCT
			`news_id`, `news_type`, DATE_FORMAT(IF(`news_type` = 3, `news_date_event`, `news_date`), '%Y-%m') AS `Ym`, IF(`news_type` = 3, `news_date_event`, `news_date`) `news_date`, `news_date_unpublish`, `news_date_event`, UNIX_TIMESTAMP(`news_date_event`) `event_date`, `news_topic`, `news_text`, NULLIF(`news_text_ext`, NULL) `news_text_ext`, `news_picture` `news_image`  
			FROM `site_news`
		";
		if($this->id !== 0){
			$params[] = $this->id;
			$conds[] = "`news_id` = ? ";
			
		}else{
			if(true === defined("KANSLIETONLINE_MULTISITE") && true === KANSLIETONLINE_MULTISITE){
				$query .= "
					LEFT JOIN `content_tag` 
					ON `news_id` = `Parent` 
					AND `Type` = 'news' 
				";

			}elseif(count($this->news_tags) !== 0){
				$query .= "
					INNER JOIN `site_news_tag` 
					ON `news_id` = `NewsId` 
				";
			}

			if(true === defined("KANSLIETONLINE_MULTISITE") && true === KANSLIETONLINE_MULTISITE){
				if($this->section_id !=res['user_comment'],
			
			"PublishSettings" => [ 'Name' => (int)$res['user_allow_publish'], 'Media' => (int)$res['user_allow_pics'] ],
						
			"Active" => (int)$res['user_activated'],
			"Status" => (int)$res['user_status'],
			"Protected" => (int)$res['user_protected'],
			"Disabled" => (int)$res['user_disabled'],
			"Newsletter" => (int)$res['user_newsletter'],
			"IsStaff" => (int)$res['user_staff'],
			"Keys" => array(
				"Key1" => $res['user_key_1'],
				"Key2" => $res['user_key_2'],
				"Expires" => strftime(DATE_DAY_MONTH_YEAR_HOUR_MINUTE, $res['KeyExpires']),
			),
			"Info" => $res['user_info'],
			"Publish" => array(),
			"HasGuardian" => (int)$res['HasGuardian'],
			"InvoiceAddr" => array(
				"Custom" => (int)$res['user_inv_custom'],
				"Name" => $res['user_inv_name'],
				"Addr1" => $res['user_inv_addr'],
				"Addr2" => $res['user_inv_addrco'],
				"Postnum" => $res['user_inv_postnum'],
				"Postaddr" => $res['user_inv_postaddr'],
				"Country" => $res['user_inv_country'],
			),
			"Balance" => array(),
			"Vars" => array(
//				"{PERSONNUMMER}" => $PersNo,
//				"{USERNAME}"	=> $res['user_name'],
				"{FIRSTNAME}"	=> $res['user_firstname'],
				"{LASTNAME}"	=> $res['user_lastname'],
				"{BIRTHDATE}"	=> $res['user_birthdate'],
				"{MEDLEMSNUMMER}"	=> $res['user_mem_num'],
				"{LICENSNUMMER}"	=> $res['user_lic_num'],
				"{EMAIL_ADDRESS}"	=> $res['user_email'],
				"{EMAIL_MIME_TYPE}" => $res['user_email_mime'],
				"{PHONE_PRIMARY}"	=> self::PhoneNo($res['user_phone1'], true),
				"{PHONE_SECONDARY}"	=> self::PhoneNo($res['user_phone2'], true),
				"{ADDRESS}"			=> $Addr,

				"{LANGUAGE}" => $res['user_language'],

				'{MEMCARD_URL}' => sprintf('%s/medlemskort/%d/?Checksum=%s', rtrim($__AbsUrl,'/'), $res['user_mem_num'], md5($res['user_id'].$res['user_mem_num'])),
			),
			"Updated" => $res['updated'],
			"Quit" => $res['quit'],
			"Tokens" => array(),
			"Meta" => self::getMeta($res['user_id']), //[],
			"Logins" => [ "Count" => (int)$res['Cnt'], "Latest" => self::LastVisit($res['user_id']) ],
		);

		/* ledare/personal
		-- --------------*/
//		if(!is_null($res['user_data']) && false !== $P = @unserialize($res['user_data']))
//			$User['Data'] = $P;

/*
		if(!is_null($res['user_pers_number'])){
			$PersNo = preg_replace("/([\d]{8})([\d]{4})/", "$1-$2", $res['user_pers_number']);
			$User['PersNum'] = $PersNo;
			$User['Vars']['{PERSONNUMMER}'] = $PersNo;
		}
*/
		if(!is_null($res['Since']))
			$User['Member']['Type']['Since'] = array("Ts" => 0, "Label" => format_datetime(new DateTime($res['Since']), 'EEEE d MMM yyyy'));

		$User['Member']['Type']['Group']['Checksum'] = md5($User['Id'] . $User['Member']['Type']['Group']['Id'] . "KanslietOnline" . SYSTEM_HASHKEY . $User['Member']['Type']['Group']['IsMaster']);

		$User['Balance']['Sum'] = (float)$res['balance'];
		$User['Balance']['Amount'] = moneyFormat((string)$res['balance']);
		$User['Balance']['Date'] = $res['balance_date'];
		$User['Balance']['Active'] = (int)$res['balance_active'];

//		if(!is_null($res['user_facebook'])) $User['Social'][0]['Link'] = $res['user_facebook'];
//		if(!is_null($res['user_linkedin'])) $User['Social'][1]['Link'] = $res['user_linkedin'];

		if(!is_null($res['user_publish'])){
			$User['Publish'] = json_decode($res['user_publish']);
		}
		
		if((int)$res['user_gender'] === 1){
			$Avatar = 'noimage_male.jpg';
		}else{
			$Avatar = 'noimage_female.jpg';
		}
		$Avatar = sprintf('%spics/avatar.png', ABSURL);

		if(false !== $IsSet = self::Avatar(
			'explorer/images/school_user/', 
			"/^avatar_{$res['user_id']}\.(jpg|jpeg|png|gif|bmp)$/i"
		)){
			$Avatar = sprintf('%sexplorer/images/school_user/%s', ABSURL, $IsSet);
		}

		$User['Avatar'] = array(
			"Preview" => null,
			"Uri" => $Avatar,
		);

		$User['Info'] = htmlentities($res['user_info'], ENT_COMPAT, "utf-8");
	
		if((defined('APP_ACTIVATED') && APP_ACTIVATED === true) && false !== $Tokens = self::GetTokens($res['user_id'])){
			$User['Tokens'] = $Tokens;
		}

		return $User;
	}

	public static function getMeta($UserId){
		$dbh = Database::getInstance();
		$pst = $dbh->prepare("SELECT `MetaKey`, `MetaValue` FROM `school_user_meta` WHERE `UserId` = ?");
		$pst->execute([ $UserId ]);
		if($pst->rowCount() !== 0){
			$Data = [];
			while($res = $pst->fetch(PDO::FETCH_ASSOC)){
				if($res['MetaKey'] === 'ForumVote'){
					continue;

				}elseif($res['MetaKey'] === 'Phone'){
					if(false !== $P = self::PhoneNo($res['MetaValue']))
						$Data[$res['MetaKey']] = $P;

					continue;
				}
				if(is_numeric($res['MetaValue'])){
					$res['MetaValue'] = (int)$res['MetaValue'];
				}
				$Data[$res['MetaKey']] = $res['MetaValue'];
			}
			return $Data;
		}
		$pst = null;
		return [];
	}

	/* set meta
	-- --------*/
	public static function setMeta($UserId, $Name, $Value){
		$dbh = Database::getInstance();
		
		$pst = $dbh->prepare("
			INSERT INTO `school_user_meta` 
			(`UserId`, `MetaKey`, `MetaValue`) 
			VALUES (:UserId, :Name, :Value) 
			ON DUPLICATE KEY UPDATE 
			`MetaValue` = :Value
		");

		$pst->bindParam(":UserId", $UserId, PDO::PARAM_INT);
		$pst->bindParam(":Name", $Name, PDO::PARAM_STR);
		$pst->bindParam(":Value", $Value, PDO::PARAM_STR);
		$pst->execute();

		return ($pst->rowCount() !== -1);
		$pst = null;
	}

		/*	get user info
	-------------------------------------------------------------*/
	public static function GetTokens($UserId = null){
		$dbh = Database::getInstance();
		$pst = $dbh->prepare("SELECT `Token` FROM `app_pushnotes` WHERE `UserId` = ?");
		$pst->execute(array($UserId));
		if($pst->rowCount() !== 0){
			$Data = array();
			while($res = $pst->fetch(PDO::FETCH_ASSOC)){
				$Data[] = $res['Token'];
			}
			return $Data;
		}
		$pst = null;
		return false;
	}
	
	/* avatar
	-- -------------*/
	public static function Avatar($D, $P = ""){
		$dp = opendir(ROOTPATH . $D);
		while ($file = readdir($dp)) {
			if ($file == '.')  continue;
			if ($file == '..') continue;
			if (is_dir($file)) continue;
			if ($P !== "" && preg_match($P, $file, $m)) return $m[0];
		}
		closedir($dp);
		return false;
	}

	public static function AvatarRemove($D, $P = "") {
		$dp = opendir(ROOTPATH . $D);
		while ($file = readdir($dp)) {
			if ($file == '.')  continue;
			if ($file == '..') continue;
			if (is_dir($file)) continue;
	//		echo $file . "\n";
	//		echo $pattern . "\n\n";
			if ($P !== "" && preg_match($P, $file, $m)){
				if(@unlink(ROOTPATH . $D . $file))
					return true;
			}
		}
		closedir($dp);
		return false;
	}

	/* next memnum
	-- -------------*/
	public static function MemberNum(){
		$dbh = Database::getInstance();

		$pst = $dbh->prepare("
			SELECT IFNULL(MAX(`user_mem_num`) + 1, 22010100)
			FROM `school_user` 
			WHERE `user_mem_num` <> 0 
			ORDER BY `user_mem_num` DESC
		");
		$pst->execute();
		if($pst->rowCount() !== 0)
			return (int)$pst->fetchColumn();
		
		$pst = null;
		return 22010100;
	}

	/* member type
	-- -------------*/
	public static function MemberType($Age){
		$dbh = Database::getInstance();

		$pst = $dbh->prepare("
			SELECT 
			`mem_type_id` 
			FROM `school_user_mem_type` 
			WHERE `mem_type_auto_set` = 2 
			AND `mem_type_removed` = 1 
			AND ? BETWEEN `mem_type_min_age` AND `mem_type_max_age`
		");
		$pst->execute(array($Age));
		if($pst->rowCount() !== 0)
			return (int)$pst->fetchColumn();
		
		$pst = null;
		return 0;
	}

	/* license type
	-- -------------*/
	public static function LicenseType($Age){
		$dbh = Database::getInstance();

		$pst = $dbh->prepare("
			SELECT 
			`lic_type_id`, `lic_type_name`  
			FROM `school_user_lic_type` 
			WHERE ? BETWEEN `lic_type_minage` AND `lic_type_maxage`
			AND `lic_type_removed` = 1 
		");
		$pst->execute(array($Age));
		if($pst->rowCount() === 0) return false;

		$res = $pst->fetch(PDO::FETCH_ASSOC);
		return array(
			"Id" => (int)$res['lic_type_id'],
			"Name" => $res['lic_type_name'],
		);
		$pst = null;
	}
	
	/* create user
	-- -------------*/
	public static function CreateUser($Params = array()){
		$dbh = Database::getInstance();
		if(!isset($Params['Passwd'])) $Params['Passwd'] = md5(generatePasswd(25));
		if(!isset($Params['Activated'])) $Params['Activated'] = 1;
		$AuthMethod = (int)config("SYSTEM_AUTH_METHOD");
		if($AuthMethod === 3) $AuthMethod = 1;
		
		try {
			$query = "
				INSERT INTO `school_user` 
				(`user_id`,`user_pers_number`,`user_name`,`user_passwd`,`user_mem_type`,`user_lic_type`,`user_firstname`,`user_lastname`,`user_birthdate`,`user_gender`,`user_email`,`user_addr`,`user_mem_num`,`user_language`, `user_regdate`,`user_auth_method`,`user_key_1`,`user_key_2`,`user_key_created`, `user_activated`) 
				VALUES 
				(NULL, :PersNo, :UserName, MD5(:Passwd), :MemType, :LicType, :Firstname, :Lastname, :Birthdate, :Gender, :Email, '', :MemNum, :Language, UNIX_TIMESTAMP(), :AuthMethod, :Key1, :Key2, CURRENT_TIMESTAMP(), :Activated)			
			";
			$pst = $dbh->prepare($query);
			$pst->bindParam(":PersNo", $Params['PersNo'], PDO::PARAM_STR);
			$pst->bindParam(":UserName", $Params['UserName'], PDO::PARAM_STR);
			$pst->bindParam(":Passwd", $Params['Passwd'], PDO::PARAM_STR);
			$pst->bindParam(":MemType", $Params['MemType'], PDO::PARAM_INT);
			$pst->bindParam(":LicType", $Params['LicType'], PDO::PARAM_INT);
			$pst->bindParam(":Firstname", $Params['Firstname'], PDO::PARAM_STR);
			$pst->bindParam(":Lastname", $Params['Lastname'], PDO::PARAM_STR);
			$pst->bindParam(":Birthdate", $Params['Birthdate'], PDO::PARAM_STR);
			$pst->bindParam(":Gender", $Params['Gender'], PDO::PARAM_INT);
			$pst->bindParam(":Email", $Params['Email'], PDO::PARAM_STR);
			$pst->bindParam(":MemNum", $Params['MemNum'], PDO::PARAM_STR);
			$pst->bindParam(":Language", $Params['Language'], PDO::PARAM_STR);
			$pst->bindParam(":AuthMethod", $AuthMethod,  PDO::PARAM_INT);
			$pst->bindParam(":Key1", $Params['Key1'], PDO::PARAM_STR);
			$pst->bindParam(":Key2", $Params['Key2'], PDO::PARAM_STR);
			$pst->bindParam(":Activated", $Params['Activated'], PDO::PARAM_INT);
			$pst->execute();

			return (int)$dbh->lastInsertId();

		} catch(Exception $e){
			throw new Exception ($e->getMessage());
		}
	}


	public function EmailVars($UserId = null){
		global $__AbsUrl;

		$query = "
			SELECT 
			`user_id`, `user_pers_number`, `user_name`, `user_firstname`, `user_lastname`, `user_birthdate`, `user_phone1`, `user_phone2`, `user_email`, `user_email_mime`, `user_addr`, `user_addrco`, `user_postnum`, `user_postaddr`, `user_country`, `user_mem_num`, `user_lic_num`, `user_language` 
			FROM `school_user` 
			WHERE `user_id` = :UserId 
			LIMIT 1
		";
		$pst = $this->dbh->prepare($query);
		$pst->bindParam(":UserId", $UserId, PDO::PARAM_INT);
		$pst->execute();
		if($pst->rowCount() !== 1) return false;

		$res = $pst->fetch(PDO::FETCH_ASSOC);
	
		$Addr = $res['user_addr'];
		if(strlen(trim($res['user_addrco'])) != 0){
			$Addr .= "<br />{$res['user_addrco']}";
		}
		$Addr .= "<br />{$res['user_postnum']} {$res['user_postaddr']}";

		return array(
			"{PERSONNUMMER}" => $res['user_pers_number'],
			"{USERNAME}"	=> $res['user_name'],
			"{FIRSTNAME}"	=> $res['user_firstname'],
			"{LASTNAME}"	=> $res['user_lastname'],
			"{BIRTHDATE}"	=> $res['user_birthdate'],
			"{MEDLEMSNUMMER}"	=> $res['user_mem_num'],
			"{LICENSNUMMER}"	=> $res['user_lic_num'],

			"{EMAIL_ADDRESS}"	=> $res['user_email'],
			"{EMAIL_MIME_TYPE}" => $res['user_email_mime'],
			"{PHONE_PRIMARY}"	=> $this->PhoneNo($res['user_phone1'], true),
			"{PHONE_SECONDARY}"	=> $this->PhoneNo($res['user_phone2'], true),
			"{ADDRESS}"			=> $Addr,
			
			"{LANGUAGE}" => $res['user_language'],

			'{MEMCARD_URL}' => sprintf('%s/medlemskort/%d/?Checksum=%s', $__AbsUrl, $res['user_mem_num'], md5($res['user_id'].$res['user_mem_num'])),
		);
	}

	/* latest member fee
	-- -------------------*/
	public static function LatestMemberFee($UserId){
		$dbh = Database::getInstance();

		$Data = array(
			"Name" => "-",
			"Paid" => _("Nej"),
		);
		
		/* rullande
		-- --------------*/
		global $__RecurringMembership;
		if(isset($__RecurringMembership) && $__RecurringMembership === true){
			$query = "
				SELECT
				`not_id` `ID`, NULL `Desc`, `Start`, `End`  
				FROM `school_notifies`
				INNER JOIN `school_user_mem_fee_recurring` 
				ON `not_id` = `NotId`
				WHERE `not_user_id` = ? 
				AND `not_confirmed` IS NOT NULL 

				UNION ALL

				SELECT
				`NotIdMaster` `ID`, NULL `Desc`, `Start`, `End` 
				FROM `school_user_familyhistory` 
				INNER JOIN `school_user_mem_fee_recurring` `t1`
				ON `NotIdMaster` = `t1`.`NotId`
				WHERE `t1`.`UserId` = ? 

				ORDER BY `ID` DESC
			";
		}else{

			/* Fix 2019-12-12
			-- --------------*/
			$query = "
				SELECT
				`not_id` `ID`, `fee_desc` `Desc`, FROM_UNIXTIME(`fee_start`) `Start`, FROM_UNIXTIME(`fee_end`) `End`
				FROM `school_notifies`
				INNER JOIN `school_user_mem_fee`
				ON `not_mem_fee` = `fee_id`
				WHERE `not_user_id` = ? 
				AND `not_confirmed` IS NOT NULL 

				UNION ALL

				SELECT
				`NotIdMaster` `ID`, `fee_desc` `Desc`, FROM_UNIXTIME(`fee_start`) `Start`, FROM_UNIXTIME(`fee_end`) `End` 
				FROM `school_user_familyhistory` 
				INNER JOIN `school_user_mem_fee`
				ON `FeeId` = `fee_id`
				WHERE `UserId` = ? 

				ORDER BY `End` DESC, `ID` DESC
			";
		}

		$pst = $dbh->prepare($query);
		$pst->execute(array($UserId,$UserId));

		if($pst->rowCount() !== 0){
			$res = $pst->fetch(PDO::FETCH_ASSOC);
			if(is_null($res['Desc'])) $res['Desc'] = format_datetime(new DateTime($res['End']), "E d MMM yyyy"); //strftime(DATE_DAY_MONTH_YEAR, $res['End']);
			return array(
				'Name' => $res['Desc'], 
				'Expires' => array('Ts' => (int)strtotime($res['End']), 'Label' => format_datetime(new DateTime($res['End']), "E d MMM yyyy")), //strftime(DATE_DAY_MONTH_YEAR, $res['End'])),
				'Valid' => (strtotime($res['Start']) < time() && strtotime($res['End']) > time()),
				'Paid' => self::FeePaidStatus($res['ID'])
			);
		}
		$pst = null;
		
//		return self::LatestGroupFee($UserId);
		return false;
		return $Data;
	}

	/* latest member fee
	-- -------------------*/
	public static function LatestGroupFee($UserId){
		$dbh = Database::getInstance();

		$Data = array(
			"Name" => "-",
			"Paid" => _("Nej"),
		);

		$query = "
			SELECT
			`NotIdMaster`, `fee_desc`, FROM_UNIXTIME(`fee_end`, '%Y-%m-%d') AS `Fee` 
			FROM `school_user_familyhistory` 
			INNER JOIN `school_user_mem_fee`
			ON `FeeId` = `fee_id`
			WHERE `UserId` = ? 
			ORDER BY `Id` DESC
			LIMIT 1
		";
		$pst = $dbh->prepare($query);
		$pst->execute(array($UserId));

		if($pst->rowCount() !== 0){
			$res = $pst->fetch(PDO::FETCH_ASSOC);
			$Data['Name'] = $res['fee_desc'];
			$Data['Paid'] = self::FeePaidStatus($res['NotIdMaster']);
		}
		$pst = null;

		return $Data;
	}
	
	/* fee paid status
	-- -------------------*/
	public static function FeePaidStatus($NotId){
		$dbh = Database::getInstance();

		$query = "
			SELECT
			`invoice_id`, CONCAT_WS('', `invoice_id`, `checknum`) AS `invoice_ocr`, MD5(CONCAT(`invoice_id`, `invoice_type`)) AS `checksum` 
			FROM `invoice` 
			INNER JOIN `invoice_part` 
			ON `invoice_id` = `parent_invoice` 
			WHERE `parent_notify` = {$NotId} 
			AND `invoice_type` <> 3 
			AND `invoice_status` = 2 
			GROUP BY `parent_invoice`
		";
		$pst = $dbh->prepare($query);
		$pst->execute(array($NotId));

		if($pst->rowCount() !== 0){
			$res = $pst->fetch(PDO::FETCH_ASSOC);
			return sprintf(
				'<i class="fa fa-file-pdf-o" aria-hidden="true"></i>&nbsp;<a href="#" class="InvoiceDetails" data-id="%d">%s</a>', 
				$res['invoice_id'], $res['invoice_ocr']
			);
		}
		$pst = null;
		return false;
	}

	public static function FamilyMembers($FamilyId = 0){
		$dbh = Database::getInstance();

		$query = "
			SELECT 
			`user_id`, `mem_type_id` 
			FROM `school_user` 
			INNER JOIN `school_user_familymember` 
			ON `user_id` = `UserId` 
			  LEFT JOIN `school_user_mem_type` 
			  ON `mem_type_auto_set` = 2  
			  AND `mem_type_removed` = 1 
			  AND DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(`user_birthdate`)), '%Y')+0 BETWEEN `mem_type_min_age` AND `mem_type_max_age`
			WHERE `Parent` = ?
		";
		$pst = $dbh->prepare($query);
		$pst->execute(array($FamilyId));
		if($pst->rowCount() === 0) return false;
		
		$Data = array();
		while($res = $pst->fetch(PDO::FETCH_ASSOC)){
			$Data[] = array('UserId' => (int)$res['user_id'], 'MemType' => (int)$res['mem_type_id']);
		}			
		$pst = null;

		return $Data;
	}

	/* group_props
	-- ---------------*/
	public static function SetProp($UserId, $Prop, $Value = null){
		if((int)$UserId === 0) return false;
		$dbh = Database::getInstance();

		$Props = self::GetProps($UserId);
		
		if(isset($Props[$Prop]) && is_null($Value)){
			unset($Props[$Prop]);

		}elseif(!is_null($Value)){
			$Props[$Prop] = $Value;
		}
		$pst = null;

		return self::SaveProps($UserId, $Props);
	}
	
	public static function GetProps($UserId){
		if((int)$UserId === 0) return false;
		$dbh = Database::getInstance();
		
		$query = "
			SELECT 
			`user_data` 
			FROM `school_user` 
			WHERE `user_id` = :UserId
		";
		$pst = $dbh->prepare($query);
		$pst->bindParam(":UserId", $UserId, PDO::PARAM_INT);
		$pst->execute();
		
		if($pst->rowCount() === 0) return [];

		$res = $pst->fetch(PDO::FETCH_ASSOC);
		if(false === $Props = @unserialize($res['user_data'])) return [];
		return $Props;
	}

	public static function SaveProps($UserId, $Props){
		if((int)$UserId === 0) return false;
		$dbh = Database::getInstance();
		
		$P = null;
		if(is_array($Props) && count($Props) !== 0)
			$P = @serialize($Props);

		$query = "
			UPDATE `school_user` SET `user_data` = :Props WHERE `user_id` = :UserId
		";

		$pst = $dbh->prepare($query);
		$pst->bindParam(":UserId", $UserId, PDO::PARAM_INT);
		$pst->bindParam(":Props", $P, PDO::PARAM_STR);
		$pst->execute(); 
		return ($pst->rowCount() === 1);
	}

	function __destruct(){
		$this->dbh = null;
	}
}

if(!class_exists('UserException')){
	Class UserException extends Exception { }
}