<?php
function get_anagr($anagr_id=false,$arrFilter=false){
	
	if(!empty($_REQUEST)){
		foreach($_REQUEST as $k=>$v)
			$arrFilter[$k] = $v;
	}
	
	if($_SESSION['user']['grp'] == 'usr'){
		$arrFilter['flt_year'] = false;
		$filter_where .= "anagrafiche.user_id = ".$_SESSION['user']['id']." AND ";
	}
	
	//filtro id
	if($anagr_id !== false)
		$filter_where .= "anagrafiche.anagr_id = ".intval($anagr_id)." AND ";
	

	//filtro cognome
	if($arrFilter['flt_cogn'])
		$filter_where .= "LOWER(anagrafiche.anagr_cognome) LIKE '".dbClean(strtolower($arrFilter['flt_cogn']),false)."%' AND ";


	//filtro codice servizio
	if($arrFilter['flt_serv_cod']){
		
		$serv_cod = clean_str($arrFilter['flt_serv_cod']);
		$filter_join .= "
		LEFT JOIN servizi_anagr ON anagrafiche.anagr_id = servizi_anagr.anagr_id 
		LEFT JOIN servizi ON servizi_anagr.serv_id = servizi.serv_id ";
		$filter_where .= "servizi.serv_cod = '".$serv_cod."' AND ";
		
		/*
		//filtro sui parametri del servizio (iscritto, pagato, etc..)
		if($arrFilter['af_serv_opt']){
			
			$serv_table = 'serv_'.$serv_cod;
			$filter_join .= "LEFT JOIN ".$serv_table." ON anagrafiche.anagr_id = ".$serv_table.".anagr_id ";
			
			$opts = explode("|",$_GET['af_serv_opt']);
			foreach($opts as $v){
				$arr = explode("=",$v);
				$filter_where .= $serv_table.".serv".$serv_cod."_".clean_str($arr[0])." = ".dbClean($arr[1])." AND ";
			}
		}*/
		
		
		//filtro gruppo/i
		if($arrFilter['flt_grp'] || $arrFilter['flt_grp_sub']){
			$table = "serv_".$serv_cod;
			
			$filter_join .= "LEFT JOIN ".$table." ON anagrafiche.anagr_id = ".$table.".anagr_id ";
			
			if(!empty($arrFilter['flt_grp']))
				$filter_where .= $table.".scuola_id = ".intval($arrFilter['flt_grp'])." AND ";
			if(!empty($arrFilter['flt_grp_sub']))
				$filter_where .= $table.".classe_id = ".intval($arrFilter['flt_grp_sub'])." AND ";
		}
		
	}
	
	//filtro anno visualizzazione
	if($arrFilter['flt_year'])
		$anno = intval($arrFilter['flt_year']);
	else
		$anno = $_SESSION['user']['ente_anno'];
	
	if(!$arrFilter['no_limit']){
		$l = 50;
		$limit = "LIMIT ". (intval($_GET['p']) * $l) .",".$l;
	}
	
		
	//query
	$result = $GLOBALS['mysqli']->query($q="
	SELECT SQL_CALC_FOUND_ROWS *, anagrafiche.anagr_id AS anagr_id, anagrafiche.comune_id AS comune_id,anagrafiche.provincia_id AS provincia_id FROM anagrafiche 
	LEFT JOIN users ON anagrafiche.user_id = users.user_id
	LEFT JOIN province ON anagrafiche.provincia_id = province.provincia_id
	LEFT JOIN comuni ON anagrafiche.comune_id = comuni.comune_id ".$filter_join."
	WHERE ".$filter_where." anagrafiche.ente_id = ".$_SESSION['user']['ente_id']." AND anagrafiche.anno = ".$anno."
	GROUP BY anagrafiche.anagr_id 
	ORDER BY anagrafiche.anagr_cognome, anagrafiche.anagr_nome
	".$limit);
	
	if($result->num_rows > 0){

		if($l)
			$list['pagination'] = pagination($l);
		
		while($row=$result->fetch_assoc())
			$list['list'][] = $row;
		
		//se ho richiesto un'anagrafica rimando solo i dati, e' piu' comodo
		return $anagr_id ? $list['list'][0] : $list;
	}
	
	return false;
}

function get_anagr_servizi($id){
	
	$id = intval($id);
	$servizi = array();
	
	$result = $GLOBALS['mysqli']->query("
	SELECT * FROM servizi_anagr 
	LEFT JOIN servizi ON servizi_anagr.serv_id = servizi.serv_id 
	WHERE servizi_anagr.anagr_id = ".$id." ORDER BY servizi.serv_ord");
	while($row=$result->fetch_assoc())
		$servizi[$row['serv_id']] = $row;

	return $servizi;
}
function anagr_has_serv($id,$serv_cod){
	$result = $GLOBALS['mysqli']->query("
	SELECT * FROM servizi 
	LEFT JOIN servizi_anagr ON servizi.serv_id = servizi_anagr.serv_id
	WHERE servizi_anagr.anagr_id = ".intval($id)." AND servizi.serv_cod = ".dbClean($serv_cod));
	return $result->num_rows == 1;
}

function add_anagr_servizi($anagr_id,$arrServizi){
	
	$servizi_ente = get_ente_servizi(true);
	
	//assegnazione nuovi servizi all'anagrafica
	if(is_array($arrServizi)){
	
		//attuali servizi legati all'anagrafica
		$servizi_anagr = get_anagr_servizi($anagr_id);

		foreach($arrServizi as $serv_id){
			
			$serv_id = intval($serv_id);

			//se non e' ancora assegnato, lo assegno
			if(!$servizi_anagr[$serv_id]){

				$result = $GLOBALS['mysqli']->query("INSERT INTO servizi_anagr VALUES (NULL,".$anagr_id.",".$serv_id.")");
				if(!$result)
					return false;
				
				usrlog("Servizio ".$servizi_ente[$serv_id]['serv_name']." - Alunno %s. Nuovo iscritto",$anagr_id,true); //log 
			}
			
			//memorizzo id nuovi servizi che ho aggiunto
			$s[] = $serv_id;
		}
		
		//elimino servizi non piu' attivi
		if($s){
			
			//log
			$result = $GLOBALS['mysqli']->query("SELECT serv_id FROM servizi_anagr WHERE anagr_id = ".$anagr_id." AND serv_id NOT IN (".implode(',',$s).")");
			while($row = $result->fetch_assoc())
				usrlog("Servizio ".$servizi_ente[$row['serv_id']]['serv_name']." - Alunno %s. Cancellata iscrizione al servizio",$anagr_id,true); //log 
			
			//cancellazione
			$result = $GLOBALS['mysqli']->query("DELETE FROM servizi_anagr WHERE anagr_id = ".$anagr_id." AND serv_id NOT IN (".implode(',',$s).")");
			if(!$result)
				return false;
			
			
		}

		


	//non ho scelto alcun servizio, li elimino tutti
	}else{

		//log
		$result = $GLOBALS['mysqli']->query("SELECT serv_id FROM servizi_anagr WHERE anagr_id = ".$anagr_id);
		while($row = $result->fetch_assoc())
			usrlog("Servizio ".$servizi_ente[$row['serv_id']]['serv_name']." - Alunno %s. Cancellata iscrizione al servizio",$anagr_id,true); //log 

		//cancellazione
		$result = $GLOBALS['mysqli']->query("DELETE FROM servizi_anagr WHERE anagr_id = ".$anagr_id);		
		if(!$result)
			return false;
	}
	
	clear_anagr_servizi($anagr_id);
	
	//controllo sia presente la riga di configurazione default per ciascun servizio attivo su questa anagrafica
	$servizi_anagr = get_anagr_servizi($anagr_id);
	foreach($servizi_anagr as $k=>$v){
		$table = 'serv_'.$v['serv_cod'];
		$result = $GLOBALS['mysqli']->query("SELECT * FROM ".$table." WHERE anagr_id = ".$anagr_id);
		if($result->num_rows == 0){
			$result = $GLOBALS['mysqli']->query("INSERT INTO ".$table." (anagr_id) VALUES (".$anagr_id.")");
			if(!$result)
				return false;
		}
	}
	
	return true;
}

function clear_anagr_servizi($anagr_id){

	$anagr_servizi = get_anagr_servizi($anagr_id);
	$ente_servizi = get_ente_servizi();
	
	//elimino sottoscrizione ai servizi non piu attivi
	foreach($ente_servizi as $serv_id => $v){
		if(!$anagr_servizi[$serv_id]){
			$GLOBALS['mysqli']->query("DELETE FROM serv_".$v['serv_cod']." WHERE anagr_id = ".$anagr_id);

			//elimino allegati
			$result = $GLOBALS['mysqli']->query("
			SELECT attachments.att_id, attachments.att_fname FROM attachments 
			LEFT JOIN attachments_name ON attachments.attach_id = attachments_name.attach_id
			WHERE 
			attachments_name.serv_cod = '".$v['serv_cod']."' AND 
			attachments.anagr_id = ".$anagr_id);
			while($row = $result->fetch_assoc()){
				$att_id[] = $row['att_id'];	
				@unlink(ROOT.'/userfiles/allegati/'.$row['att_fname']);
			}
			if($att_id)
				$GLOBALS['mysqli']->query("DELETE FROM attachments WHERE att_id IN (".implode(",",$att_id).")");
			
			//elimino delegati
			$GLOBALS['mysqli']->query("DELETE FROM servizi_delegati WHERE serv_cod = '".$v['serv_cod']."' AND anagr_id = ".$anagr_id);
		}
		
	}

}

function assign_anagr_servizio($anagr_id,$serv_cod){
	//id servizio 
	$result = $GLOBALS['mysqli']->query("SELECT serv_id, serv_name FROM servizi WHERE serv_cod = ".dbClean($serv_cod));
	if($result && $result->num_rows == 1){
		$row = $result->fetch_assoc();
		$serv_id = $row['serv_id'];
		$serv_name = $row['serv_name'];
		$result = $GLOBALS['mysqli']->query("INSERT INTO servizi_anagr VALUES (NULL,".$anagr_id.",".$serv_id.")");
		if($result){
			usrlog("Servizio ".$serv_name." - Alunno %s. Nuovo iscritto",$anagr_id,true); //log 
			return true;
		}
	}
	return false;
}

function print_serv_anagr_select($current_anagr_id,$serv_cod){

	$anagr = get_anagr(false,array('flt_serv_cod'=>$serv_cod));
	
	//se non c'e' l'id richiesto, e' perche' sto facendo una nuova iscrizione e lo aggiungo
	if($anagr){
		foreach($anagr['list'] as $v){
			if($v['anagr_id'] == $current_anagr_id)
				$founded = true;	
		}
	}
	if(!$founded){
		$tmp_anagr = get_anagr($current_anagr_id);
		unset($anagr['list']);
		$anagr['list'][] = $tmp_anagr;
	}
	
	reset($anagr['list']);
	$n = count($anagr['list']);
	if($n > 1){
		foreach($anagr['list'] as $v){
			if($current_anagr_id == $v['anagr_id'])
				$current_user = entities($v['anagr_nome'].' '.$v['anagr_cognome']);
			else
				$content .= '<li role="presentation"><a role="menuitem" tabindex="-1" href="./?id='.$v['anagr_id'].'">'.entities($v['anagr_nome'].' '.$v['anagr_cognome']).'</a></li>';
			
			
		}
		echo '
		<div class="row m-bottom-10">
			<div class="col-sm-6 col-md-5 col-lg-4">
				<div class="dropdown">
				  <button class="btn btn-danger dropdown-toggle" type="button" id="sel-anagr-dropdown" data-toggle="dropdown">
					<span class="glyphicon glyphicon-user"></span>&nbsp; '.$current_user.'
					<span class="caret"></span>
				  </button>
				  <ul class="dropdown-menu" role="menu" aria-labelledby="sel-anagr-dropdown">
					'.$content.'
				  </ul>
				</div>			
			</div>
		</div>';
			
	}elseif($n == 1){
		$anagr = current($anagr['list']);
		echo '<h4 class="top text-danger">
		<span class="glyphicon glyphicon-user"></span><span class="m-left-10">'.entities($anagr['anagr_nome'].' '.$anagr['anagr_cognome']).'</span>
		</h4>';
	}
}

function del_anagr($anagr_id){
	$anagr_id = intval($anagr_id);
	$result = $GLOBALS['mysqli']->query("DELETE FROM anagrafiche WHERE anagr_id = ".$anagr_id." AND ente_id = ".$_SESSION['user']['ente_id']);
	if($GLOBALS['mysqli']->affected_rows == 1){
		$GLOBALS['mysqli']->query("DELETE FROM servizi_anagr WHERE anagr_id = ".$anagr_id);
		clear_anagr_servizi($anagr_id);
		return true;
	}
	return false;
}
?>