<?php include('_mod_security.php'); ?>
<?php
	//var_dump($_POST);
	//var_dump($_GET);
	$ar_dest_fields = array(
								salutation => 	array(field => 'Salutation', dbfield => 'salutation', maxlength => 25, required => false),
								first_name => 	array(field => 'First Name', dbfield => 'first_name', maxlength => 50, required => true),
								middle_name => 	array(field => 'Middle Name', dbfield => 'middle_name', maxlength => 50, required => false),
								last_name => 	array(field => 'Last Name', dbfield => 'last_name', maxlength => 50, required => true),
								suffix => 		array(field => 'Suffix', dbfield => 'suffix', maxlength => 25, required => false),
								street => 		array(field => 'Street 1', dbfield => 'street', maxlength => 100, required => false),
								street2 => 		array(field => 'Street 2', dbfield => 'street2', maxlength => 100, required => false),
								city => 		array(field => 'City', dbfield => 'city', maxlength => 50, required => false),
								state => 		array(field => 'State', dbfield => 'state', maxlength => 25, required => false),
								zip =>	 		array(field => 'Zip Code', dbfield => 'zip', maxlength => 15, required => false),
								country => 		array(field => 'Country', dbfield => 'country', maxlength => 50, required => false),
								phone => 		array(field => 'Phone', dbfield => 'phone', maxlength => 25, required => false),
								mobile =>		array(field => 'Mobile', dbfield => 'mobile', maxlength => 25, required => false),
								email => 		array(field => 'Email', dbfield => 'email', maxlength => 100, required => false),
							);
	$err_flag = false;
	$validate_function = '';
	if ($_POST['submitted'] == 1) :
		//var_dump($_FILES);
		if (!empty($_FILES['import_file']['name'])) :
			$uploaddir = APP_UPLOAD_DIR.'/nymp_data_import/'.date('YYYY_MM_DD_hh_mm_ss');
			if (!is_dir($uploaddir)) :
				//echo 'trying to make directory';
				mkdir($uploaddir, 0777, true);
			endif;
			$uploadfile = $uploaddir.'/'.date("Ymd_His").'_'.basename($_FILES['import_file']['name']);
			if (move_uploaded_file($_FILES['import_file']['tmp_name'], $uploadfile)) :
				//echo 'File upload successful!';
				require_once dirname(__FILE__) . '/../classes/PHPExcel/IOFactory.php';
				if (strtoupper(substr(PHPExcel_IOFactory::identify($uploadfile),0,5)) == 'EXCEL') :
					$objPHPExcel = PHPExcel_IOFactory::load($uploadfile);
					$import_sheet_data = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
					//var_dump($import_sheet_data);
					if (count($import_sheet_data) <= 1 && count($import_sheet_data[1]) <= 1) :
						$err_flag = true;
						$form_message = "File does not appear to contain valid import data! &nbsp;Please select a valid Excel file to import.";
					else :
						if ($_POST['first_row_field_names'] == '1') :
							$r_start = 2;
						else :
							$r_start = 1;
						endif;
						for ($r = $r_start; $r <= count($import_sheet_data); $r++) :
							$sql = "INSERT INTO locations SET ".
									//"region_name = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['A']))).
									"region_id = IFNULL((SELECT region_id FROM regions WHERE region_name = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['A'])))."), 0)".
									//",category_name = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['E']))).
									",category_id = IFNULL((SELECT category_id FROM categories WHERE category_name = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['E'])))."), 0)".
									",location_name = ".$db->quote(ucwords(strtolower(trim(strip_tags($import_sheet_data[$r]['C']))))).
									",bldg_nbr = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['F']))).
									",street = ".$db->quote(ucwords(strtolower(trim(strip_tags($import_sheet_data[$r]['G']))))).
									",cross_street = ".$db->quote(ucwords(strtolower(trim(strip_tags($import_sheet_data[$r]['H']))))).
									",city = ".$db->quote(ucwords(strtolower(trim(strip_tags($import_sheet_data[$r]['J']))))).
									",state = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['K']))).
									",zip = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['L']))).
									",delivery_address = ".$db->quote(ucwords(strtolower(trim(strip_tags($import_sheet_data[$r]['M']))))).
									",route = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['I']))).
									//",neighborhood_name = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['B']))).
									",neighborhood_id = IFNULL((SELECT neighborhood_id FROM neighborhoods WHERE neighborhood_name = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['B'])))."), 0)".
									",phone = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['N']))).
									",contact_name = ".$db->quote(ucwords(strtolower(trim(strip_tags($import_sheet_data[$r]['O']))))).
									",instructions = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['P']))).
									",notes = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['Q']))).
									",nbr_del = ".nz(trim(strip_tags($import_sheet_data[$r]['S'])),'0').
									",racks = ".$db->quote(trim(strip_tags($import_sheet_data[$r]['R']))).
									",create_by = ".nz(trim($_SESSION['user_id']), '0')." ".
									",create_date = '".date('Y-m-d H:i:s')."' ";
							try {
								//echo ' === '.$sql.' === ';
								$db->query($sql);
								$last_id = $db->lastInsertId();
							} catch(PDOException $ex) {
								die('Database Error! '.$ex->getMessage.' === '.$sql.' === ');
							}
							$sql = "INSERT INTO deliveries SET ".
									"location_id = ".nz($last_id, '0').
									",qty = ".nz(trim(strip_tags($import_sheet_data[$r]['T'])),'0').
									",return_qty = ".nz(trim(strip_tags($import_sheet_data[$r]['U'])),'0').
									",restock_qty = ".nz(trim(strip_tags($import_sheet_data[$r]['V'])),'0').
									",create_by = ".nz(trim($_SESSION['user_id']), '0')." ".
									",create_date = '".date('Y-m-d H:i:s')."' ";
							try {
								$db->query($sql);
							} catch(PDOException $ex) {
								die('Database Error! '.$ex->getMessage.' === '.$sql.' === ');
							}
						endfor;
					endif;
				else :
					$err_flag = true;
					$form_message = "File is invalid! &nbsp;Please select a valid Excel<sup>&reg;</sup> file to import.";
				endif;
			else :
				$err_flag = true;
				$form_message = "File upload failed! &nbsp;Please select a valid Excel<sup>&reg;</sup> file to import.";
			endif;
		else :
			$err_flag = true;
			$form_message = "Please specify import file.";
		endif;
	endif;
?>

<script>
	var active_tab = '';

	$(document).ready(function()	
	{
		//switch_tab('<?php echo $active_tab; ?>');
		$("#tab_content_general").show();
	});
</script>

<h1>Data Load</h1>
<?php echo form_message($form_message); ?>

<form name="frm_import" id="frm_import" method="post" action="" enctype="multipart/form-data" target="" >
	<input name="submitted" type="hidden" value="1" />
	<input name="active_tab" id="active_tab" type="hidden" value="general" />
	<div class="cleardiv">
		&nbsp;
	</div>
	<div class="tab_content" id="tab_content_general" style="display:block; position:relative;">
		<div class="form_header">
			Import File Selection 
		</div>
		Select Excel&reg; file to import: &nbsp;
		<input type="file" name="import_file" /><br /><br />
		<input type="checkbox" name="first_row_field_names" value="1" /> First row contains field names
	</div>
	<!-- -------------------------------------------------------------------------------------------------------------- -->
	<div class="form_strip">
		<input type="submit" value="Submit" />
		&nbsp;&nbsp;
		<input type="button" value="Cancel" onclick="window.location.href = 'index.php?IX=locations';" />
	</div>
</form>