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`, `user_phone2`, `user_email`, `user_email_mime`, `user_facebook`, `user_linkedin`, NULLIF(`user_addr`, '') `user_addr`, `user_addrco`, `user_postnum`, `user_postaddr`, `user_country`, `user_kommun`, `user_lanskod`, `user_inv_custom`, `user_inv_name`, `user_inv_addr`, `user_inv_addrco`, `user_inv_postnum`, `user_inv_postaddr`, `user_inv_country`, `user_inv_type`, `user_mem_fee`, `user_lic_fee`, `user_publish`, `user_info`, `user_comment`, `user_allow_pics`, `user_allow_publish`, IFNULL(`user_nationality`, 'SE') `user_nationality`, `user_auth_method`, `user_language`, FROM_UNIXTIME(`user_regdate`) `user_regdate`, `balance`, IFNULL(`balance_date`, '-') AS `balance_date`, `balance_active`, `user_activated`, `user_status`, `updated`, `user_protected`, `user_disabled`, `user_newsletter`, `user_staff`, `user_data`, `user_key_1`, `user_key_2`, UNIX_TIMESTAMP(DATE_ADD(`user_key_created`, INTERVAL 24 HOUR)) `KeyExpires`, /*DATE_FORMAT(`quit`, '%Y-%m-%d')*/ `quit`,  
			`mem_type_id`, `mem_type_name`, `mem_type_auto_replace`, `mem_type_group`, 
			`lic_type_id`, `lic_type_name`, `lic_type_auto_replace`, 
			(SELECT `pm_name` FROM `system_payment_method` WHERE `pm_id` = `user_inv_type`) AS `user_paytype`, 
			(SELECT name FROM system_country WHERE iso_code = `user_country`) AS `user_country_name`, 
			(SELECT name FROM system_country WHERE iso_code = `user_inv_country`) AS `user_inv_country_name`, 
			(SELECT COUNT(`ID`) FROM `guardian` WHERE `UserId` = `user_id` AND `Notify` = 1) `HasGuardian`, 
			(SELECT `Parent` FROM `school_user_familymember` WHERE `UserId` = `user_id` LIMIT 1) AS `FamilyId`, 
			(SELECT IF(`Master` = 2, 2, 1) FROM `school_user_familymember` WHERE `UserId` = `user_id` LIMIT 1) AS `IsMaster`,
			(SELECT `ID` FROM `school_user_addr` WHERE `Default` = 1 AND `UserId` = `user_id` LIMIT 1) `DefaultAddress`, 
			(SELECT `Since` FROM `school_user_mem_type_history` WHERE `UserId` = `user_id` AND `MemTypeId` = `user_mem_type` ORDER BY `ID` DESC LIMIT 1) `Since`,
			(SELECT `group_name` FROM `user_group` WHERE `group_id` = `user_group`) `Group`, 
			(SELECT COUNT(`not_id`) FROM `school_group` INNER JOIN `school_notifies` ON `group_id` = `not_group_id` INNER JOIN `roles` ON `not_role` = `ID` WHERE `not_user_id` = `user_id` AND `group_removed` = 1 AND `Staff` = 1 AND DATE_ADD(`group_end`, INTERVAL 45 DAY) >= CURRENT_DATE()) `IsStaff`, 
			(SELECT COUNT(`log_id`) FROM `school_user_log` WHERE `log_user` = `user_id` AND `log_action` = 2) `Cnt` 
			FROM `school_user` 
			LEFT JOIN `school_user_mem_type` 
			ON `user_mem_type` = `mem_type_id` 
			  LEFT JOIN `school_user_lic_type` 
			  ON `user_lic_type` = `lic_type_id` 
			WHERE `user_id` = :UserId
			LIMIT 1
		";

		$pst = $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']}";
		
		$PersNo = null;
		$__SYSTEM_AUTH_METHOD = (int)config("SYSTEM_AUTH_METHOD");
		$__UNIQUE_EMAIL_ADDR = (int)config("UNIQUE_EMAIL_ADDR");
		
		/* anv. namn
		-- ----------------*/
		if($__SYSTEM_AUTH_METHOD === 2){
			$PersNo = $res['user_birthdate'];

		/* personnummer
		-- ----------------*/
		}else{
			/*
			if(false !== (bool)preg_match(REGEX_PERSNUM, $res['user_pers_number'], $PN)){
				$PersNo = sprintf("%s-%s", $PN[1], $PN[7]);

			}elseif(false !== $PN = KOCommon::ValidatePersNo($res['user_pers_number'], $res['user_birthdate'], $res['user_gender'], true)){
				$PersNo = sprintf("%s-%s", $PN[0], $PN[1]);
			}
			*/
			try {
//				KOCommon::IsValidPersNo(substr($UserInfo['PersNum'], 2));
				if(false !== (bool)preg_match(REGEX_PERSNUM, $res['user_pers_number'], $PN)){
					$PersNo = sprintf("%s-%s", $PN[1], $PN[7]);
				}else{
					$PNr = KOCommon::ValidatePersNo($res['user_pers_number'], $res['user_birthdate'], $res['user_gender'], true);
					$PersNo = sprintf("%s-%s", $PNr[0], $PNr[1])ent}}
</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 !== 0){
					$params[] = $this->section_id;
					$conds[] = "`SectionId` = ? ";
				}else{
					$conds[] = "(`SectionId` = 0 OR `SectionId` IS NULL) ";
				}

			}elseif(count($this->news_tags) !== 0){
				$params = array_merge($params, $this->news_tags);
				$tags = implode(",", array_fill(0, (count($this->news_tags)), '?'));
				$conds[] = "`TagId` IN({$tags}) ";
			}

			$params[] = $this->language;
			$conds[] = "`Language` = ? ";
			
			$params[] = $this->important;
			$conds[] = "`news_important` = ? ";

			if(!is_null($this->period)){
				$params[] = $this->period;
				$conds[] = "DATE_FORMAT(`news_date`, '%Y-%m') = ? ";
			}
		}
		$query .= "WHERE (`news_date` <= CURRENT_TIMESTAMP() AND NOT (`news_date_unpublish` IS NOT NULL AND `news_date_unpublish` <= CURRENT_TIMESTAMP()))\n";

		if(true === $this->__IsAuthenticated){
			if($this->id === 0)
				$conds[] = "`news_type` <> 3 ";
		}else{
			if($this->id === 0){
				$conds[] = "`news_type` = 1 ";
			}
		}
		
		foreach($conds as $c)
			$query .= sprintf("AND %s\n", $c);
		
		$query .= " ORDER BY `news_date` DESC ";
		if($Limit !== -1) $query .= sprintf("LIMIT %d", (int)$Limit);
			
//		echo sprintf("<pre>%s\n%s</pre>", var_export($params, true), str_replace("\t","",$query));
		
		$pst = $this->dbh->prepare($query);
		foreach($params as $K => $P) $pst->bindValue(($K+1), $P);
		$pst->execute();
		if($pst->rowCount() !== 0){
			$i = 0;
			$Data = [];
			while($res = $pst->fetch(PDO::FETCH_ASSOC)){
				$Url = sprintf('%s%s/%d-%s/', $this->base_url, $res['Ym'], $res['news_id'], post_slug($res['news_topic']));
				$Data[$i] = [
					'id' => (int)$res['news_id'],
					'event' => ((int)$res['news_type'] === 3),
					'private' => ((int)$res['news_type'] === 2),
					'create_date' => strftime($this->date_format, strtotime($res['news_date'])),
					
					'event_date' => $res['event_date'],
					'event_time' => strftime(DATE_HOUR_MINUTE, $res['event_date']),
						
					'event_month' => strftime(DATE_MONTH, $res['event_date']), 
					'event_day' => strftime(DATE_DAY, $res['event_date']),
						
					'event_daymonthyear' => null, 
					
					'period' => strftime("%Y-%m", strtotime($res['news_date'])), 
					'period_label' => strftime(DATE_MONTH_YY, strtotime($res['news_date'])), 
						
					'title' => $res['news_topic'],
					'shorttitle' => CropStr($res['news_topic'], 30),
					'excerpt' => null,
					'text' => $res['news_text'],
					'text_ext' => $res['news_text_ext'],
					'readmore' => (!is_null($res['news_text_ext']) && $this->id === 0),
					'image' => null,
					'url' => $Url,
					'social' => [],
					'tags' => $this->get_tags($res['news_id']),
				];
				
				if(!is_null($res['news_date_event'])){
					$Data[$i]['event_daymonthyear'] = format_datetime(new DateTime($res['news_date_event']), "cccc dd MMMM yyyy");
				}
				
				if(!is_null($res['news_image']) && file_exists(sprintf('%s%s', ROOTPATH, ltrim($res['news_image'],'/'))) === true)
					$Data[$i]['image'] = sprintf('%s%s', rtrim($__AbsUrl, '/'), $res['news_image']);

				if(strlen($res['news_text']) !== 0){
					$Excerpt = strip_tags($res['news_text']);
					$Data[$i]['excerpt'] = CropStr($Excerpt, 100, ' ');
				}
				
				if($this->facebook_app_id === 1 && $this->facebook === 1 && (int)$res['news_type'] !== 2){
					$Data[$i]['social']['facebook'] = $Url;

				}elseif($this->facebook_app_id === 0 && $this->facebook === 1 && (int)$res['news_type'] !== 2){
					$Data[$i]['social']['facebook_simple'] = true;
				}
					
				
				if($this->twitter === 1 && (int)$res['news_type'] !== 2)
					$Data[$i]['social']['twitter'] = [ 'url' => urlencode($Url), 'title' => urlencode($res['news_topic']), 'tags' => urlencode(GLOBAL_PAGE_TITLE) ];

				if(!$this->images) $Data[$i]['image'] = null;
				$i++;
			}
			return $Data;

		}
		$pst = null;
		return false;
	}

	public function events($Limit = 5){
		global $__AbsUrl;
		$params = [];

		$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` = $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 { }
}