<?php

$kleur = true;
$data = array();
$counter = 0;

?>
<table cellpadding="5" cellspacing="0" border="0">
    <tr>
        <th>Nr</th>
        <th>Bedrijfsnaam</th>
        <th>Emailadres</th>
        <th>Foutmeldingen</th>
    </tr>
<?php 

if ( $_FILES['file']['tmp_name'] ){
	$dom = DOMDocument::load( $_FILES['file']['tmp_name'] );
	$rows = $dom->getElementsByTagName( 'Row' );
	$first_row = true;
	foreach ($rows as $row)	{

		if ( !$first_row ){
			
			$counter++;
			if($kleur){ $kleur = false; }else{ $kleur = true; }
			
			$index = 1;
			$cells = $row->getElementsByTagName( 'Cell' );
			foreach( $cells as $cell ){ 
				$ind = $cell->getAttribute( 'ss:Index' );
				if ( $ind != null ) $index = $ind;
				
				$data[$index] = htmlentities(addslashes(trim($cell->nodeValue)));
							
				$index += 1;
			}
			
			//emailadres controleren
			
			$emailadres = "";
									
			if(!preg_match('/[\w-\.]+@([\w-]+\.)+[\w-]{2,4}/', $data[1])){ 
				$valid_emailadres = false;
				$al_geregistreerd = false;
			}else{
				$emailadres = $data[1];
				$lengte_emailadres = strlen($emailadres);
				$emailadres = strtolower($emailadres);
				if($lengte_emailadres < 250){
					$q_mail = sprintf("SELECT kms_form_data.send_emailadres FROM kms_form_data WHERE kms_form_data.send_emailadres='%s' LIMIT 0,1", $emailadres);
					$r_mail = mysql_query($q_mail);
					$rec_mail = mysql_fetch_assoc($r_mail);
					$data_mailadres = $rec_mail['send_emailadres'];
					if($data_mailadres != $emailadres){
							$valid_emailadres = true;
							$al_geregistreerd = false;
					}else{
						$valid_emailadres = false;
						$al_geregistreerd = true;
					}
				}else{
					$valid_emailadres = false;
					$al_geregistreerd = false;
				}
			}
			
			$error = false;
			
			if($valid_emailadres && !$al_geregistreerd){
			
				$password = "";
				for ($i=1;$i<=15;$i++){
					$trap=rand(0,32);
					$password=$password.substr("abcdefghkmnopqrstuvwxyz1234567890",$trap,1);
				}
				$code=strtoupper($password);
				
				$status = "open";
				$hash = md5($data[1] . $code);
								
				$q_account = sprintf("INSERT INTO kms_form_data (send_emailadres, code, status, hash, bedrijfsnaam, kvk_nummer, kvk_plaats, btwnr, rechtsvorm, bezoek_straat, bezoek_postcode, bezoek_plaats, bezoek_land, postadres_anders, post_straat, post_postcode, post_plaatsnaam, post_land, leveradres_anders, lever_straat, lever_postcode, lever_plaats, lever_land, lever_specs_radio, lever_specs, telefoonnummer, emailadres, inkoop_geslacht, inkoop_voornaam, inkoop_tussenvoegsel, inkoop_achernaam, inkoop_emailadres, inkoop_tel_direct, inkoop_telefoonnummer, contact_admin_anders, admin_geslacht, admin_voornaam, admin_tussenvoegsel, admin_achternaam, admin_emailadres, admin_telefoonnummer, admin_tel_direct, banknaam, iban, bic, emailadres_factuur) 
				VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')", 
				$data[1], $code, $status, $hash, $data[2], $data[3], $data[4], $data[5], $data[6], $data[7], $data[8], $data[9], $data[10], $data[11], $data[12], $data[13], $data[14], $data[15], $data[16], $data[17], $data[18], $data[19], $data[20], $data[21], $data[22], $data[23], $data[24], $data[25], $data[26], $data[27], $data[28], $data[29], $data[30], $data[31], $data[32], $data[33], $data[34], $data[35], $data[36], $data[37], $data[38], $data[39], $data[40], $data[41], $data[42], $data[43]);
				
				$r_account = mysql_query($q_account);
				
				if(!$r_account) { $query_error = true; $error = true; }
				
			}else{
				$error = true;	
			}?>

            <tr <?php if($kleur && $error){ echo("style=\"background-color:#e6e8eb; color:#F00;\""); } else if ($kleur && !$error){echo("style=\"background-color:#e6e8eb;\""); } else if (!$kleur && !$error){echo("style=\"\""); } else if(!$kleur && $error){ echo("style=\"color:#F00;\""); }?> >
                <td><?php echo( $counter ); ?></td>
                <td><?php echo( $data[2]); ?></td>
                <td><?php echo( $data[1]); ?></td>
                <td><?php         
                    if($al_geregistreerd){ echo("E-mailadres was al geregistreerd. <br />"); }
                    if(!$valid_emailadres && !$al_geregistreerd){ echo("Opgegeven e-mailadres is ongeledig. <br />"); }
					if($query_error){ echo("Query ging fout. <br />"); }
                    ?>
                </td>
            </tr>
            <?php 
	
		} 
			
	$data = NULL;
	$first_row = false;
	
	}
} ?>
</table>