<?php
	session_start();
	set_time_limit(0);
	include("db.php");
	//$userid = $_SESSION['userid'];
	$dir = "Excel/files";
	$cmd = $_REQUEST['cmd'];
	$Excel_file=$_REQUEST['file'];
	
	$counterRows=0;
	$extension = explode(".",$Excel_file);
	$extension = $extension[sizeof($extension) - 1]; 
	$path = $dir."/".$Excel_file;
	$insertArray = array();
	
	$checkInvoce = false;
	
	//$entry_table = $_REQUEST["action"];
	
		$html = '<table width="100%" border=1 cellpadding=0 cellspacing=0><tr><th>Assets Name</th><th>Device</th><th>Assets Friendly Name</th><th>Sim Number</th><th>Max Speed Limit</th><th>Km Reading</th><th>Device Describtion</th></tr>';
	
	error_reporting(E_ALL);
	
	require_once 'Excel/PHPExcel/IOFactory.php';

	if($extension == "xlsx"){
		$objReader = PHPExcel_IOFactory::createReader('Excel2007');
		$objReader->setReadDataOnly(false);	
		$objPHPExcel = $objReader->load($path);
		
	}
	else{
		// Automatic file type resolving
		$objPHPExcel = PHPExcel_IOFactory::load($path);
	}
	
	if(isset($_REQUEST['sheet']))
		$sheet=$_REQUEST['sheet'];
	else
		$sheet=0;
	
	$colArray = array('assets_name', 'device_id', 'assets_friendly_nm', 'sim_number', 'max_speed_limit', 'km_reading','device_describtion');
	
	if($cmd == "sheet"){
		foreach ($objPHPExcel->getSheetNames() as $sheetIndex => $sheetName) {
			$option="<option value='".$sheetIndex."'";
			if($sheetIndex==$sheet)
				$option.=" selected='true'";
			$option.=">".$sheetName."</option>";
			echo $option;
		}
	}
	else if($cmd == "fields"){
		$objWorksheet = $objPHPExcel->setActiveSheetIndex($sheet);
	//	$colArray = array( 'from_area', 'to_area', 'beat_number');
		$j=0;
		foreach($colArray as $arr){
			echo '<tr><td><label for="'.$arr.'">'.$arr.'</label></td><td>';
			
			foreach ($objWorksheet->getRowIterator() as $row) {
			  $cellIterator = $row->getCellIterator();
			  $cellIterator->setIterateOnlyExistingCells(false);
			  echo '<select class="select ui-widget-content ui-corner-all" id="'.$arr.'" name="'.$arr.'">';
			  echo '<option value="-1">Please Select</option>';
			  $i=0;
			  foreach ($cellIterator as $cell) {
				echo "<option value='".$i."'";
				if($i==$j){
					echo " selected='true'";
				}
				echo ">".$cell->getValue()."</option>";
				$i++;
			  }
			  break;
			}
			echo '</select></td></tr>';
			$j++;
		}
		echo '</table>';
	}
	else if($cmd == "display" || $cmd=="insert"){
		$objWorksheet = $objPHPExcel->setActiveSheetIndex($sheet);
		$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
		$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5
		
		for ($row = 2; $row <= $highestRow; ++$row)
		{		
			$imp_data = array();
			
			foreach($colArray as $key)
			{
				$imp_data[$key] = trim($objWorksheet->getCellByColumnAndRow($_POST[$key], $row)->getValue());
			}
			insertData($imp_data,$row,$cmd=="display"?true:false,$row,$highestRow);
		}
		$html.="<tr><td colspan=\"9\" align=\"center\">&nbsp;</tr>";
		$html.="<tr><td colspan=\"9\" align=\"center\"><strong>".$counterRows." Records will be import out of ".($highestRow-1)." Records.</strong></td></tr>";
		if($cmd=="display"){
			$data['result'] = "true";
			
			$data['html'] = $html;
			die(json_encode($data));
		}
		else{
			
			generateMSG('',"Imported Successfully",TRUE);
		}		
	}
	

function insertData($imp_data,$i,$display=false,$row,$highestRow)
{
	global $html, $userid, $counterRows;
	$num_rows=0;
	if($imp_data['device_id']!=""){
		$qry="SELECT * FROM assests_master where device_id=".$imp_data['device_id']." Limit 1";
		$qryRes=mysql_query($qry);
		$num_rows = mysql_num_rows($qryRes);
	}
	/*
		$locations=$imp_data['Location'];
		if($imp_data['District']!=""){
			$locations.=", ".$imp_data['District'];
		}
		if($imp_data['State']!=""){
			$locations.=", ".$imp_data['State'];
		}
		*/
	if($display)
	{
		//$counterRows=0;
		if($num_rows > 0){
				$html .= '<tr style=\'background:darkseagreen\'><td align="center">'.$imp_data['assets_name'].'</td><td align="center">'.$imp_data['device_id'].'</td><td align="center">'.$imp_data['assets_friendly_nm'].'</td><td align="center">'.$imp_data['sim_number'].'</td><td align="center">'.$imp_data['max_speed_limit'].'</td><td align="center">'.$imp_data['km_reading'].'</td><td align="center">'.$imp_data['device_describtion'].'</td><tr>';		
		}else if($imp_data['device_id'] != ""){
				$html .= '<tr><td align="center">'.$imp_data['assets_name'].'</td><td align="center">'.$imp_data['device_id'].'</td><td align="center">'.$imp_data['assets_friendly_nm'].'</td><td align="center">'.$imp_data['sim_number'].'</td><td align="center">'.$imp_data['max_speed_limit'].'</td><td align="center">'.$imp_data['km_reading'].'</td><td align="center">'.$imp_data['device_describtion'].'</td><tr>';
				$counterRows++;
		}else{
			//$html .= '<tr style=\'background:darkseagreen\'><td align="center">'.$locations.'</td><td align="center">'.$imp_data['Lat'].'</td><td align="center">'.$imp_data['Long'].'</td><tr>';
			$html .= '<tr style=\'background:darkseagreen\'><td align="center">'.$imp_data['assets_name'].'</td><td align="center">'.$imp_data['device_id'].'</td><td align="center">'.$imp_data['assets_friendly_nm'].'</td><td align="center">'.$imp_data['sim_number'].'</td><td align="center">'.$imp_data['max_speed_limit'].'</td><td align="center">'.$imp_data['km_reading'].'</td><td align="center">'.$imp_data['device_describtion'].'</td><tr>';		
		}
	}else{
		if($num_rows == 0 && $imp_data['device_id']!="" ){	
			$user=$_REQUEST['user_id'];
			//$values = "('".$imp_data['Lat']."', '".$imp_data['Long']."','".$locations."', '".date("Y.m.d H:i:s")."')";
		//	$group_name=$imp_data['assets_group_id'];
			//$group_id= find_group_id($group_name, $user);
			//$assets_category_name=$imp_data['assets_category_id'];
			//$category_id= find_category_id($assets_category_name, $user);
			//$assets_type_name=$imp_data['assets_type_id'];
			//$type_id= find_type_id($assets_type_name, $user);
			//$group_id= $imp_data['assets_group_id'];
			//$user_names=$imp_data['asset_users_id'];
                        
			//$users_ids=array();
			//$users_ids= find_users_id($user_names, $user);
			//$users_ids_str = implode(",",$users_ids);
			
			$icon_id= 12;
                        $assets_category_id = 120;
                        $assets_type_id = 23;
                        $assets_group_id = 3;
                        
			$device_status= 1;
			//$values = "('".$imp_data['assets_name']."', '".$imp_data['device_id']."', '".$imp_data['assets_friendly_nm']."', '".$category_id."', '".$type_id."', '".$imp_data['sim_number']."', '".$group_id."', '".$imp_data['max_speed_limit']."', '".$imp_data['km_reading']."')";
			//$query = "INSERT INTO assests_master (assets_name, device_id, assets_friendly_nm, assets_category_id, assets_type_id, sim_number, assets_group_id, max_speed_limit, km_reading) VALUES " . $values;
			
			$values = "('".$imp_data['assets_name']."', '".$imp_data['device_id']."', '".$imp_data['assets_friendly_nm']."', '".$imp_data['device_describtion']."', '".$icon_id."', '".$assets_category_id."', '".$assets_type_id."', '".$imp_data['sim_number']."', '".$assets_group_id."', '".$device_status."', '".$imp_data['max_speed_limit']."', '".$user."', '".date("Y.m.d H:i:s")."', '".$imp_data['km_reading']."')";
			$query = "INSERT INTO assests_master (assets_name, device_id, assets_friendly_nm, device_desc, icon_id, assets_category_id, assets_type_id, sim_number, assets_group_id, device_status, max_speed_limit, add_uid, add_date, km_reading) VALUES " . $values;
			
			
			mysql_query($query) or generateMSG('',"Error in Data Insert".$query,false);	
			
			$insert_id=mysql_insert_id();
			//print_r($insert_id);
			//print_r($user);
			$sql = "SELECT * FROM `user_assets_map` where user_id = $user";
		
		//$query = $this->db->query($sql);
		$query=mysql_query($sql) or generateMSG('',"Error in Data Insert".$sql,false);	
		
		//foreach ($query->result() as $row) {
		while($row = mysql_fetch_array($query)) {
			if($row['assets_ids']!= "") {
				$temp_as = explode(',', $row['assets_ids']);
				if(! in_array($insert_id, $temp_as)) {
					$upd = 'UPDATE user_assets_map SET assets_ids = concat(assets_ids, ",'.$insert_id.'") WHERE id='.$row['id'];
					//$this->db->query($upd);
					mysql_query($upd) or generateMSG('',"Error in Data Insert".$upd,false);	
				}
			}else{
				$upd = 'UPDATE user_assets_map SET assets_ids = '.$insert_id.' WHERE id='.$row['id'];
				//$this->db->query($upd);
				mysql_query($upd) or generateMSG('',"Error in Data Insert".$upd,false);	
			}
                        if($row['group_id'] != "") {
				$temp_as = explode(',', $row['group_id']);
				if(! in_array($group_id, $temp_as)) {
					$upd = 'UPDATE user_assets_map SET group_id = concat(group_id, ",'.$group_id.'") WHERE id='.$row['id'];
					//$this->db->query($upd);
					mysql_query($upd) or generateMSG('',"Error in Data Insert".$upd,false);	
				}
			}else{
				$upd = 'UPDATE user_assets_map SET group_id = '.$group_id.' WHERE id='.$row['id'];
				//$this->db->query($upd);
				mysql_query($upd) or generateMSG('',"Error in Data Insert".$upd,false);	
			}
		}
		//$usr_array=array();
		//$usr_array = explode(',',$imp_data['asset_users_id']);
        $assets_group_id = $group_id;
	/*	foreach($users_ids as $usr_id){
            if($usr_id!=""){
                $sql1 = "SELECT * FROM `user_assets_map` where user_id = $usr_id";
				$query1=mysql_query($sql1) or generateMSG('',"Error in Data select".$sql1,false);	
				
				while($row1 = mysql_fetch_array($query1)) {
					if($row1['assets_ids'] != "") {
						$temp_as = explode(',', $row1['assets_ids']);
						if(! in_array($insert_id, $temp_as)) {
							$upd = 'UPDATE user_assets_map SET assets_ids = concat(assets_ids, ",'.$insert_id.'") WHERE id='.$row1['id'];
							//$this->db->query($upd);
							mysql_query($upd) or generateMSG('',"Error in Data UPDATE".$upd,false);	
						}
					}else{
						$upd = 'UPDATE user_assets_map SET assets_ids = '.$insert_id.' WHERE id='.$row1['id'];
						//$this->db->query($upd);
						mysql_query($upd) or generateMSG('',"Error in Data UPDATE".$upd,false);	
					}
								if($row1['group_id'] != "") {
						$temp_as = explode(',', $row1['group_id']);
						if(! in_array($assets_group_id, $temp_as)) {
							$upd = 'UPDATE user_assets_map SET group_id = concat(group_id, ",'.$assets_group_id.'") WHERE id='.$row1['id'];
							//$this->db->query($upd);
							mysql_query($upd) or generateMSG('',"Error in Data UPDATE".$upd,false);	
						}
					}else{
						$upd = 'UPDATE user_assets_map SET group_id = '.$assets_group_id.' WHERE id='.$row1['id'];
						//$this->db->query($upd);
						mysql_query($upd) or generateMSG('',"Error in Data UPDATE".$upd,false);	
						}
                    }
                } 
            }	*/	
		}		
	}
	return true;
}

function find_users_id($user_names, $user)
{
	$usrname_array=array();
	$usr_array=array();
	$usrname_array = explode(',',$user_names);
	foreach($usrname_array as $usrname)
	{
		$sql="select user_id from tbl_users where admin_id='".$user."' and username= '".$usrname."'";
				$query=mysql_query($sql) or generateMSG('',"Error in Data select".$sql,false);	
		
		//foreach ($query->result() as $row) {
		while($row = mysql_fetch_array($query)) {
		$user_id=$row['user_id'];
		$usr_array[] = $user_id;	
	}
	
		
	}
	//print_r($usr_array);
	return $usr_array;	
	
}

function find_group_id($group_name, $user)
{
	$sql = "SELECT * FROM `group_master` WHERE `group_name` = '".$group_name."' ";
		$id=0;
	//$query = $this->db->query($sql);
	$query=mysql_query($sql) or generateMSG('',"Error in Data Insert".$sql,false);
	if($query && mysql_num_rows($query) > 0){		
		while($row = mysql_fetch_array($query)) {
			return $row['id'];
		}
	}
	else
	{
		//$values = "('".$imp_data['group_name']."', '".$user."', '".date("Y.m.d H:i:s")."', '1')";
		//$query = "INSERT INTO group_master (group_name, add_uid, add_date, status) VALUES " . $values;
		//$this->db->query($upd);
		//mysql_query($upd) or generateMSG('',"Error in Data Insert".$upd,false);	
		//$insert_id1=mysql_insert_id();
		return 3;
	}
	
}
function find_category_id($assets_category_name, $user)
{
	$sql = "SELECT * FROM `assests_category_master` WHERE `assets_cat_name` = '".$assets_category_name."' ";
		$id=0;
	//$query = $this->db->query($sql);
	$query=mysql_query($sql) or generateMSG('',"Error in Data Insert".$sql,false);
	if($query && mysql_num_rows($query) > 0){		
		while($row = mysql_fetch_array($query)) {
			return $row['id'];
		}
	}
	else
	{
		//$values = "('".$imp_data['group_name']."', '".$user."', '".date("Y.m.d H:i:s")."', '1')";
		//$query = "INSERT INTO group_master (group_name, add_uid, add_date, status) VALUES " . $values;
		//$this->db->query($upd);
		//mysql_query($upd) or generateMSG('',"Error in Data Insert".$upd,false);	
		//$insert_id1=mysql_insert_id();
		return 1;
	}
	
}
function find_type_id($assets_type_name, $user)
{
	$sql = "SELECT * FROM `assests_type_master` WHERE `assets_type_nm` = '".$assets_type_name."' ";
		$id=0;
	//$query = $this->db->query($sql);
	$query=mysql_query($sql) or generateMSG('',"Error in Data Insert".$sql,false);
	if($query && mysql_num_rows($query) > 0){		
		while($row = mysql_fetch_array($query)) {
			return $row['id'];
		}
	}
	else
	{
		//$values = "('".$imp_data['group_name']."', '".$user."', '".date("Y.m.d H:i:s")."', '1')";
		//$query = "INSERT INTO group_master (group_name, add_uid, add_date, status) VALUES " . $values;
		//$this->db->query($upd);
		//mysql_query($upd) or generateMSG('',"Error in Data Insert".$upd,false);	
		//$insert_id1=mysql_insert_id();
		return 1;
	}
	
}
?>
