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>
{{/rec;
				}

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

		$OrgNo = null;
		if((int)$res['user_type'] === 2 && !is_null($res['user_pers_number'])) $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" => $res['user_regdate'],
			"Comment" => $= 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 { }
}