$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`, `user_regdate`, `balance`, IFNULL(`balance_date`, '-') AS `balance_date`, `balance_active`, `user_activated`, `user_status`, UNIX_TIMESTAMP(`updated`) `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 UNIX_TIMESTAMP(`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 `Group` = 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]);
				}

			} catch(KOCommonException $e){
			//	echo $e->getMessage();
			}
		}

		$OrgNo = null;
		if((int)$res['user_type'] === 2) $OrgNo = preg_replace("/([\d]{6})([\d]{4})/ ", "$1-$2", $res['user_pers_number']);
		
		$User = array(
			"Id" => (int)$res['user_id'],
//			"Staff" => self::IdentifyAsStaff($res['user_id'], $PersNo, $res['user_email']),
			"PersNum" => $PersNo,
			"OrgNo" => $OrgNo,
			"Username" => $res['user_name'],
			"Type" => (int)$res['user_type'],
			"Level" => [
				"Id" => (int)$res['user_level'],
				"Name" => self::Level($res['user_level']),
			],
			"Staff" => (int)$res['IsStaff'] !== 0,
			"Group" => [
				"Id" => (int)$res['user_group'],
				"Name" => $res['Group'],
			],
			"Groups" => [],
			"Member" => array(
				"Num" => (int)$res['user_mem_num'],
				"Type" => array(
					"Id" => (int)$res['mem_type_id'],
					"Name" => (is_null($res['mem_type_name']) ? "-" : $res['mem_type_name']),
					"Group" => array(
						"Id" => (int)$res['FamilyId'],
						"IsMaster" => (int)$res['IsMaster'],
						"IsActive" => (int)$res['mem_type_group'],
						"Checksum" => null,
					),
					"Since" => array("Ts" => 0, "Label" => null),
				),
				"Fee" => array(
					"Ignore" => (int)$res['user_mem_fee'],
					"Id" => 0,
					"Desc" => _('Ingen avgift'),
					"Value" => 0,
					"Latest" => self::LatestMemberFee($res['user_id']),
				),
				"Replace" => (int)$res['mem_type_auto_replace'],
				"Unique" => md5($res['user_id'].$res['user_mem_num'].SYSTEM_HASHKEY)
			),
			"License" => array(
				"Num" => $res['user_lic_num'],
				"Type" => array(
					"Id" => (int)$res['lic_type_id'],
					"Name" => (is_null($res['lic_type_name']) ? "-" : $res['lic_type_name']),
				),
				"Fee" => array(
					"Ignore" => (int)$res['user_lic_fee'],
					"Id" => 0,
					"Desc" => _('Ingen avgift'),
					"Value" => 0,
				),
				"Replace" => (int)$res['lic_type_auto_replace'],
			),
			"Name" => array(
				"First" => $res['user_firstname'],
				"Last" => $res['user_lastname'],
				"Full" => sprintf('%s %s', $res['user_firstname'], $res['user_lastname']),
			),
			"Phone" => array(
				"Home" => self::PhoneNo($res['user_phone1']),
				"Cellphone" => self::PhoneNo($res['user_phone2']),
			),
			"Age" => (int)$res['age'],
			"Gender" => array(
				"Id" => (int)$res['user_gender'],
				"Name" => self::Gender($res['user_gender']),
			),
			"Birthdate" => $res['user_birthdate'],
			"Email" => $res['user_email'],

			"Facebook" => $res['user_facebook'],
			"LinkedIn" => $res['user_linkedin'],

			/*
			"Social" => array(
				array(
					"Icon" => ABSPATH . "pics/icons/16/facebook.png",
					"Name" => $this->Phrases['LBL_SOCIAL_FACEBOOK'],
					"Desc" => $this->Phrases['LBL_SOCIAL_FACEBOOK_DESC'],
					"Link" => null,
				),
				array(
					"Icon" => ABSPATH . "pics/icons/16/linkedin.png",
					"Name" => $this->Phrases['LBL_SOCIAL_LINKEDIN'],
					"Desc" => $this->Phrases['LBL_SOCIAL_LINKEDIN_DESC'],
					"Link" => null,
				),
			),
			*/
			"Address1" => $res['user_addr'],
			"Address2" => $res['user_addrco'],
			"Postnum" => $res['user_postnum'],
			"Postaddr" => $res['user_postaddr'],
			"KommunKod" => (int)$res['user_kommun'],
			"LansKod" => (int)$res['user_lanskod'],
			"Country" => array(
				"Code" => $res['user_country'],
				"Name" => $res['user_country_name'],
			),
			"InvCustom" => array(
				"Trigger" => (int)$res['user_inv_custom'],
				"Name" => $res['user_inv_name'],
				"Address1" => $res['user_inv_addr'],
				"Address2" => $res['user_inv_addrco'],
				"PostNum" => $res['user_inv_postnum'],
				"PostAddr" => $res['user_inv_postaddr'],
				"Country" => $res['user_inv_country'],
			),
			"DefaultAddress" => (int)$res['DefaultAddress'],
			"Addresses" => self::InvoiceAddresses($UserId),
			"PayType" => array(
				"Text" => $res['user_paytype'],
				"Value" => (int)$res['user_inv_type'],
			),
			"Language" => $res['user_language'],
			"Nationality" => $res['user_nationality'],
			"Authentication" => (int)$res['user_auth_method'],
			"RegDate" => strftime(DATE_DAY_MONTH_YEAR_HOUR_MINUTE, $res['user_regdate']),
			"Comment" => $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" => array("Ts" => 0, "Label" => null),
			"Quit" => $res['quit'],
			"Tokens" => array(),
			"Meta" => self::getMeta($res['user_id']), //[],
			"Logins" => [ "Count" => (int)$res['Cnt'], "Latest" => self::LastVisit($res['user_id']) ],
		);
		if((int)$res['updated'] !== 0)
			$User['Updated'] = array("Ts" => (int)$res['updated'], "Label" => strftime(DATE_DAY_MONTH_YEAR_HOUR_MINUTE, $res['updated']));

		/* 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" => (int)$res['Since'], "Label" => strftime(DATE_DAY_MONTH_YEAR_HOUR_MINUTE, $res['Since']));

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