<?php
/*
Autor: asanch92
shortcode: shortcode  [c2em_update_roles form_name="inscripciov2"]  per poder actualitzar els roles a inscrits de tots els usuaris que han pagat i que encara tenen el rol subscrit
*/
include_once( ABSPATH . 'wp-admin/includes/plugin.php' );
if ( is_plugin_active( 'contact-form-7-to-database-extension/contact-form-7-db.php' ) ) {
	add_shortcode( 'c2em_update_roles', 'c2em_veure_roles_html' ); 

	function c2em_veure_roles_html($atts) {
		//Recuparem l'atribut del shortcode
		$var = shortcode_atts( array(
		    'form_name' => 'nom formulari',
		), $atts );
		//Recupareme el nom de la taula que crea CF7DB
		require_once (ABSPATH . '/wp-content/plugins/contact-form-7-to-database-extension/CF7DBPlugin.php');
		$plugin = new CF7DBPlugin;
		$tableName = $plugin->getSubmitsTableName();
		$content="<h4>Actualitzar rols de subscribers a inscrits </h4> <br>
				S'actualitzaran aquells usuaris que tinguin rol <code>subscriber</code> i <span class='dashicons dashicons-thumbs-up' style='color: green;'></span> <code>comprovar_tresorer==1  </code> o <span class='dashicons dashicons-thumbs-up' style='color: blue;'></span> <code>comprovar_tresorer==2 </code><br>";
		//Si s'ha enviat el formulari per canviar els rols
		if (isset($_POST['c2emupdateroles']) &&
                    wp_verify_nonce($_REQUEST['_wpnonce'])) {
			if($_POST['c2emupdateroles']=='true'){	
				$content.=c2em_update2inscrits($var, $tableName);
			}
			else{
				$content.="Operació cancel·lada";
			}
		}
		//contingut a mostrar en forma de taula html
		$content.=c2em_show_roles($var, $tableName);
		
		return $content ;
	}
	

	function c2em_update2inscrits($var, $tableName){
		global $wpdb;
		//identitats que han pagat
		$identidads = $wpdb->get_results("select `field_value` from `$tableName` 
										where `submit_time` in (select `submit_time` from `$tableName` where `field_name` ='comprovar_tresorer' and (`field_value`='1' or `field_value`='2'))
										and `field_name` ='identidad'", ARRAY_N); 
		if ($identidads){
			$count=0;
			foreach ( $identidads as $id ){
				//Recuparem la dades de l'usuari a wp
				$user = get_user_by( 'login', $id[0]);
				//Actualitzem el rol si es subcriber
				if(in_array( 'subscriber', $user->roles)){
				//li treiem el role subscriber i li posem el inscrit 
					$user->set_role('inscrit');
					$user->remove_role('subscriber');
					$count+=1;
				}
			}
		}
		$content="S'han actualitzat ".$count." usuaris";
		return $content;
	}
	//contingut a mostrar en forma de taula html
	function c2em_show_roles($var, $tableName){
				
		global $wpdb;
		//recuparem les files que te la taula (una per enviament, segons els submit_time)
		$submit_times = $wpdb->get_results( 
							$wpdb->prepare("select DISTINCT `submit_time` from `$tableName` where `form_name` = '%s' ORDER BY  `submit_time` DESC", $var['form_name']),
						ARRAY_N);	
		//Camps del formulari
		$table_field_names =$wpdb->get_results( 
							$wpdb->prepare("select DISTINCT `field_name` from `$tableName` where `form_name` = '%s' ORDER BY `field_order`", $var['form_name']),
						ARRAY_N);	
		if ( $submit_times && $table_field_names) {
			$content="<form action=".$_SERVER['REQUEST_URI']." method='post'> ";
			$content.=wp_nonce_field();
			$content.="<input id='c2emupdateroles' name='c2emupdateroles' type='hidden' value='false'/>
						<table id='taula'>
						<tbody>		
						<tr>";
			foreach ( $table_field_names as $table_field_name ){
				//no mostrem les dues ultimes columnes
				if($table_field_name[0]=="Submitted Login" || $table_field_name[0] == "Submitted From"){
					continue;
				}
				//Només posem els camps seguents 
				if( $table_field_name[0]=="identidad"|| $table_field_name[0]=="preus" || $table_field_name[0]=="comprovar_tresorer"){
					$content.="<th>". $table_field_name[0] ."</th>";	
				}		
			}
			$content.="</tr>";
		
			foreach ( $submit_times as $submit_time ){
				$content.="<tr>";
				$rows = $wpdb->get_results( 
							$wpdb->prepare("select `field_name`, `field_value` from `$tableName` 
										where `form_name` = '%s' and `submit_time` = %F ORDER BY `field_order`", 
										$var['form_name'], $submit_time[0]),ARRAY_N);
				if ( $rows ) {
					foreach ( $rows as $row ){
						if($row[0]=="Submitted Login" || $row[0] == "Submitted From"){
							continue;
						}
						//El camp comprovar_tresores, l'actualitzem si s'ha posat un preu o no
						if($row[0]=="comprovar_tresorer"){
							switch ($row[1]) {
								case '2':
									$content.="<td > <span class='dashicons dashicons-thumbs-up' style='color: blue;'></span></td> ";
									break;
								case '1':
									$content.="<td > <span class='dashicons dashicons-thumbs-up' style='color: green;'></span></td> ";
									break;
								case '-1':
									$content.="<td  ><span class='dashicons dashicons-warning' style='color: LightPink;'></span></td>";
									break;
								case '-2':
									$content.="<td><span class='dashicons dashicons-dismiss' style='color: tomato;'></span></td>";
									break;
								case '-3':
										$content.="<td><span class='dashicons dashicons-dismiss' style='color: red;'></span></td>";
								case '-4':
										$content.="<td><span class='dashicons dashicons-warning' style='color: Magenta;'></span></td>";
									break;
								 default:
									$content.="<td ><span class='dashicons dashicons-dismiss' style='color: yellow;'></span></td>";

							}
						}
						if($row[0]=='identidad' ){
							$rols='';
							$user = get_user_by( 'login', $row[1]);
							//Tots els rols separats per coma
							$rols=implode("','",$user->roles); 
							$content.="<td>".$row[1]." (rols: ".$rols.")</td>";			
						}
						if($row[0]=="preus" ){
							if($row[1]==''){
								$str="Encara no ha ingressat res ";
							}
							else{
								$str="Ha ingressat: ".$row[1]."&euro;. ";
							}
							$content.="<td>".$str."</td>";
						}
					}
				}
				$content.="</tr>";
			}
		
		
			$content.="</tbody></table> <br>
			<input type='submit' value='Actualitzar rols' id='actualitzarrols' name='c2emupdate'>
			<br>
			</form>";

			$content.="<script type='text/javascript' language='Javascript'>
				
                jQuery(document).ready(function() {
            		jQuery(':submit').click(function(){
						 var t=confirm('Acceptant, actualitzaràs tots els rols subscribers a inscrits');
						jQuery('#c2emupdateroles').val(t);
					});
                });

            </script>";

		}

		return $content;
	}
}

