<?php
ini_set('display_errors', 'On');
error_reporting(E_ERROR);
include 'set_factors.php';
include 'send_gcm.php';
include 'update_timestamp.php';
include 'insert_into_sync_table.php';

/*DEPRECATED Use version 2*/
/*The following code will upd data in SETTINGS table. Take appropriate action to update records in other tables. 
Insert data into SYNC_TABLE. And notify partner phones*/

// array for JSON response (default to failure)
$response = array();
$response["success"] = 0;
$response["message"] = "Failed";

// check for required fields
if (isset($_POST['androidId']) && isset($_POST['email']) && isset($_POST['_id']) && isset($_POST['new_val'])) {
	// include db config class
	require_once __DIR__ . '/db_config.php';
	
	// connecting to db
    	$mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE);
    	mysqli_set_charset($mysqli, "utf8");
	
	//get email and android id
	$email = $_POST['email'];
	$androidId = $_POST['androidId'];
	
	//check that android id is not in sync table
	if($stmt = $mysqli->prepare("SELECT * FROM SYNC_TABLE WHERE ANDROID_ID = ?")){
		$stmt->bind_param("s", $androidId);
		$stmt->execute(); 
		$stmt->store_result();
    		$num_rows = $stmt->num_rows;
    		
    		//proceed only if android id does not exist
    		if($num_rows == 0){
    			$id = $_POST['_id'];
    			$new_val = $_POST['new_val'];
    			
    			//get user id
			if($stmt = $mysqli->prepare("SELECT USER_ID FROM USER_ID WHERE EMAIL = ?")){
				$stmt->bind_param("s", $email);	
				$stmt->execute();
				$stmt->bind_result($temp_user_id);
				while($stmt->fetch()){
		    			$user_id = $temp_user_id;
		    		}	
		    		
		    		//continue based on id
    				//row ids for Settings: 1=distance unit change, convert data, 2=distance unit change, dont convert data, 
    				//3=volume unit change, convert data, 4=volume unit change, don't convert data, 6=curr unit change
			
				//dist unit change 
				if($id==1 || $id==2){
					if(strcmp($new_val, "Kilometers")==0){
						$new_val = "km";
					}
					else{
						$new_val = "mi";
					}
					
					//get curr value and store in var
					$stmt = $mysqli->prepare("SELECT DISTANCE FROM SETTINGS WHERE USER_ID=?");
					$stmt->bind_param("i", $user_id);
					$stmt->execute();
					$stmt->bind_result($temp_prev_dist);
					while($stmt->fetch()){
				    		$prev_dist = $temp_prev_dist;
				    	}
				    	
				    	//update distance in the table with new val			
					if($stmt = $mysqli->prepare("UPDATE SETTINGS SET DISTANCE=? WHERE USER_ID=?")){
						$stmt->bind_param("si", $new_val, $user_id);
						$result = $stmt->execute();
						
						// rec has been updated succesfully
		    				if($result){			
		    					//if convert data
		    					if($id==1){	    				
			    					//if new val is kilometers and old value is miles
								if(strcmp($new_val, "km")==0 && strcmp($prev_dist, "mi")==0){
									//update odo for fillups, services and expenses
									$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET ODO=ROUND((ODO*1.61),2) WHERE USER_ID=?");
									$stmt->bind_param("i", $user_id);
					    				$stmt->execute();
					    				
					    				//update odo for trips
									$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET QTY=ROUND((QTY*1.61),2) WHERE TYPE=3 AND USER_ID=?");
									$stmt->bind_param("i", $user_id);
					    				$stmt->execute();
					    				
					    				//update distance in service table
									$stmt = $mysqli->prepare("UPDATE SERVICE_TABLE SET DUE_MILES=ROUND((DUE_MILES*1.61),2), LAST_ODO=ROUND((LAST_ODO*1.61),2) WHERE USER_ID=?");
									$stmt->bind_param("i", $user_id);
					    				$stmt->execute();
								}
								//else if new val is miles and old is kilometers
								else if(strcmp($new_val, "mi")==0 && strcmp($prev_dist, "km")==0){
									//update odo for fillups, services and expenses
									$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET ODO=ROUND((ODO/1.61),2) WHERE USER_ID=?");
									$stmt->bind_param("i", $user_id);
					    				$stmt->execute();
					    				
					    				//update odo for trips
									$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET QTY=ROUND((QTY/1.61),2) WHERE TYPE=3 AND USER_ID=?");
									$stmt->bind_param("i", $user_id);
					    				$stmt->execute();
					    				
					    				//update distance in service table
									$stmt = $mysqli->prepare("UPDATE SERVICE_TABLE SET DUE_MILES=ROUND((DUE_MILES/1.61),2), LAST_ODO=ROUND((LAST_ODO/1.61),2) WHERE USER_ID=?");
									$stmt->bind_param("i", $user_id);
					    				$stmt->execute();
								}
								
								//update dist and eff for all vehicles
								$stmt = $mysqli->prepare("SELECT VEHID FROM VEH_TABLE WHERE USER_ID=?");
								$stmt->bind_param("i", $user_id);	
								$stmt->execute();
								$stmt->bind_result($temp_vehid);
								while($stmt->fetch()){
						    			$vehid[] = $temp_vehid;
						    		}
						    		
						    		if(count($vehid)>0){
						    			for($i=0; $i<count($vehid); $i++){
						    				//update dist
				    						updateDist($mysqli, $user_id, $vehid[$i]);
				    						
				    						//update eff
				    						updateEff($mysqli, $user_id, $vehid[$i]);
						    			}
						    		}
					    		}
					    		//else if don't convert data
					    		else if($id==2){
					    			//update eff for all vehicles
								updateEffForAllVeh($mysqli, $user_id);
					    		}
								
		    					//update sync table		    		
		    					if(insertIntoSyncTable($mysqli, $id, "edit", $user_id, "SETTINGS", $androidId)){
		    						//get other reg ids of the same email
		    						$reg_id = getOtherRegIds($mysqli, $user_id, $androidId);		    						
		    						
		    						//send message to other phones belonging to same email
		    						sendGCMMessage($reg_id, "pull");
		    					}	
		    					
		    					//update timestamp in Profile
		    					updateTimestamp($user_id);    		
		    						    					
		    					$response["success"] = 1;
							$response["message"] = "Success";
				
							// echoing JSON response
							echo json_encode($response);
		    				}
					}
				}
				//volume change
				else if($id==3 || $id==4){
					if(strcmp($new_val, "Litre")==0){
						$new_val = "Ltr";
					}
					else if(strcmp($new_val, "Gallon(US)")==0){
						$new_val = "gal(US)";
					}	
					else{
						$new_val = "gal(UK)";
					}
					
					//get curr value and store in var
					$stmt = $mysqli->prepare("SELECT VOLUME FROM SETTINGS WHERE USER_ID=?");
					$stmt->bind_param("i", $user_id);
					$stmt->execute();
					$stmt->bind_result($temp_prev_vol);
					while($stmt->fetch()){
				    		$prev_vol = $temp_prev_vol;
				    	}
				    	
				    	//update volume in the table with new val			
					if($stmt = $mysqli->prepare("UPDATE SETTINGS SET VOLUME=? WHERE USER_ID=?")){
						$stmt->bind_param("si", $new_val, $user_id);
						$result = $stmt->execute();
						
						// rec has been updated succesfully
		    				if($result){
		    					//if convert data
		    					if($id==3){
		    						//if change from litre
		    						if(strcmp($prev_vol, "Ltr")==0){
		    							if(strcmp($new_val, "gal(US)")==0){
		    								$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET QTY=ROUND((QTY/3.79),2) WHERE USER_ID=?");
										$stmt->bind_param("i", $user_id);
					    					$stmt->execute();
		    							}
		    							else{
		    								$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET QTY=ROUND((QTY/4.55),2) WHERE USER_ID=?");
										$stmt->bind_param("i", $user_id);
					    					$stmt->execute();
		    							}
		    						}
		    						//change from gal(US)
		    						else if(strcmp($prev_vol, "gal(US)")==0){
		    							if(strcmp($new_val, "Ltr")==0){
		    								$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET QTY=ROUND((QTY*3.79),2) WHERE USER_ID=?");
										$stmt->bind_param("i", $user_id);
					    					$stmt->execute();
		    							}
		    							else{
		    								$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET QTY=ROUND((QTY/1.2),2) WHERE USER_ID=?");
										$stmt->bind_param("i", $user_id);
					    					$stmt->execute();
		    							}
		    						}
		    						//else change from gal(UK)
		    						else{
		    							if(strcmp($new_val, "Ltr")==0){
		    								$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET QTY=ROUND((QTY*4.55),2) WHERE USER_ID=?");
										$stmt->bind_param("i", $user_id);
					    					$stmt->execute();
		    							}
		    							else{
		    								$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET QTY=ROUND((QTY*1.2),2) WHERE USER_ID=?");
										$stmt->bind_param("i", $user_id);
					    					$stmt->execute();
		    							}
		    						}
		    					}
		    					
		    					//update eff for all vehicles (this is regardless of whether it is 3 or 4)
							updateEffForAllVeh($mysqli, $user_id);
							
							//update sync table		    		
		    					if(insertIntoSyncTable($mysqli, $id, "edit", $user_id, "SETTINGS", $androidId)){
		    						//get other reg ids of the same email
		    						$reg_id = getOtherRegIds($mysqli, $user_id, $androidId);		    						
		    						
		    						//send message to other phones belonging to same email
		    						sendGCMMessage($reg_id, "pull");
		    					}	   
		    					
		    					//update timestamp in Profile
		    					updateTimestamp($user_id); 		
		    						    					
		    					$response["success"] = 1;
							$response["message"] = "Success";
				
							// echoing JSON response
							echo json_encode($response);
		    				}
					}
				}
				//consumption change
				else if($id==5){
					//update consumption in the table with new val			
					if($stmt = $mysqli->prepare("UPDATE SETTINGS SET CONSUMPTION=? WHERE USER_ID=?")){
						$stmt->bind_param("si", $new_val, $user_id);
						$result = $stmt->execute();
						
						// rec has been updated succesfully
		    				if($result){
		    					//update eff for all vehicles
		    					updateEffForAllVeh($mysqli, $user_id);
		    					
		    					//update sync table		    		
		    					if(insertIntoSyncTable($mysqli, $id, "edit", $user_id, "SETTINGS", $androidId)){
		    						//get other reg ids of the same email
		    						$reg_id = getOtherRegIds($mysqli, $user_id, $androidId);		    						
		    						
		    						//send message to other phones belonging to same email
		    						sendGCMMessage($reg_id, "pull");
		    					}
		    					
		    					//update timestamp in Profile
		    					updateTimestamp($user_id);	    		
		    						    					
		    					$response["success"] = 1;
							$response["message"] = "Success";
				
							// echoing JSON response
							echo json_encode($response);														
		    				}
					}
				}
				//curr change
				else if($id==6){
					//update curr in the table with new val			
					if($stmt = $mysqli->prepare("UPDATE SETTINGS SET CURRENCY=? WHERE USER_ID=?")){
						$stmt->bind_param("si", $new_val, $user_id);
						$result = $stmt->execute();
						
						// rec has been updated succesfully
		    				if($result){
		    					//update sync table		    		
		    					if(insertIntoSyncTable($mysqli, $id, "edit", $user_id, "SETTINGS", $androidId)){
		    						//get other reg ids of the same email
		    						$reg_id = getOtherRegIds($mysqli, $user_id, $androidId);		    						
		    						
		    						//send message to other phones belonging to same email
		    						sendGCMMessage($reg_id, "pull");
		    					}
		    					
		    					//update timestamp in Profile
		    					updateTimestamp($user_id);	    		
		    						    					
		    					$response["success"] = 1;
							$response["message"] = "Success";
				
							// echoing JSON response
							echo json_encode($response);	
		    				}
					}					
				}								
			}    			    		
    		}
    		//else user's android id exists in the Sync table, inform him that data on his phone is out of sync				 			
		else{		
			//get reg_id
			if($stmt = $mysqli->prepare("SELECT REG_ID FROM PROFILE WHERE ANDROID_ID=?")){
				$stmt->bind_param("s", $androidId);	
				$stmt->execute();
				$stmt->bind_result($temp_reg_id);
				while($stmt->fetch()){
					$reg_id[] = $temp_reg_id;
				}
				
				//send message to user
				sendGCMMessage($reg_id, "out of sync");
			}
		}

		// Close the prepared statement.
		if($stmt != null){
	    		$stmt->close(); 
	    	}
    		
	}
	
	//else send generic failure message
	if($response["success"] == 0){
		$response["success"] = 0;
		$response["message"] = "Failed";
								 
		// echoing JSON response
		echo json_encode($response);
	}
	
	mysqli_close($mysqli);
}
else{
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
    // echoing JSON response
    echo json_encode($response);
}

//function to get other reg ids
function getOtherRegIds($mysqli, $userID, $androidIDToIgnore){
	$sql = "SELECT REG_ID FROM PROFILE WHERE USER_ID = ? AND ANDROID_ID!=?";

	if($stmt = $mysqli->prepare($sql)){	
		$stmt->bind_param("ss", $userID, $androidIDToIgnore);			
		$stmt->execute();
		$stmt->bind_result($temp_reg_ids);
		while($stmt->fetch()){
			$reg_id[] = $temp_reg_ids;
		}
		
		// Close the prepared statement.
		if($stmt != null){
			$stmt->close(); 
	    	}

		return $reg_id;
	}
}

//function to update distance for all columns of a user/vehid
function updateDist($mysqli, $user_id, $vehid){
	if($stmt = $mysqli->prepare("SELECT _ID, ODO FROM LOG_TABLE WHERE USER_ID=? AND VEHID=? AND TYPE=0 ORDER BY ODO ASC")){
		$stmt->bind_param("is", $user_id, $vehid);
		$stmt->execute();
		$stmt->bind_result($temp_id, $temp_odo);
		while($stmt->fetch()){
			$id[] = $temp_id;
			$odo[] = $temp_odo;						
		}

		if(count($id)>0){
			$curr_odo = 0;
			$prev_odo = 0;
			
			//update first record
			$curr_id = $id[0];
			$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET DIST=0 WHERE USER_ID=? and _ID=?");
			$stmt->bind_param("ii", $user_id, $curr_id);
			$stmt->execute();
			
			//update remaining records
			if(count($id>1)){
				for($i=1; $i<count($id); $i++){
					$curr_id = $id[$i];
					$curr_odo = $odo[$i];
					$prev_odo = $odo[$i-1];
					$curr_dist = $curr_odo - $prev_odo;
					
					$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET DIST=? WHERE USER_ID=? and _ID=?");
					$stmt->bind_param("dii", $curr_dist, $user_id, $curr_id);
					$stmt->execute();
				}
			}
		}
		
		// Close the prepared statement.
		if($stmt != null){
			$stmt->close(); 
	    	} 
	}
}

//function to update eff for all columns of user id/vehid
function updateEff($mysqli, $user_id, $vehid){
	//get value from set factors
	$factors = getUnits($user_id);
	$dist_fact = $factors[0];
	$vol_fact = $factors[1];
	
	$curr_qty = 0;
	$curr_dist = 0;
	$curr_id = 0;
	$prev_qty = 0;
	$prev_dist = 0;
	$firstFullFillUp = FALSE;
	
	if($stmt = $mysqli->prepare("SELECT _ID, QTY, DIST, PARTIAL_FILL, MISSED_FILL FROM LOG_TABLE WHERE USER_ID=? AND VEHID=? and TYPE = 0 ORDER BY ODO ASC")){
		$stmt->bind_param("is", $user_id, $vehid);
		$stmt->execute();
		$stmt->bind_result($temp_id, $temp_qty, $temp_dist, $temp_pfill, $temp_mfill);
		while($stmt->fetch()){
			$id[] = $temp_id;
			$qty[] = $temp_qty;
			$dist[] = $temp_dist;
			$pfill[] = $temp_pfill;
			$mfill[] = $temp_mfill;							
		}
		
		if(count($id)>0){
			for($i=0; $i<count($id); $i++){
				$curr_id = $id[$i];
				
				//if partial record then update eff as -1000
				if($pfill[$i]==1){
					$curr_dist = $dist[$i];
					
					//to take care for first rec or missed fill up record in case OT=odo
					if($curr_dist==0 || $mfill[$i]==1){
						$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET CONSUMPTION=NULL WHERE USER_ID=? and _ID=?");
						$stmt->bind_param("ii", $user_id, $curr_id);
						$stmt->execute();						
					}
					//else it is not the first record
					else{		
						$prev_qty = $prev_qty + $qty[$i];
						$prev_dist = $prev_dist + $dist[$i];
						
						$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET CONSUMPTION=-1000 WHERE USER_ID=? and _ID=?");
						$stmt->bind_param("ii", $user_id, $curr_id);
						$stmt->execute();
					}
				}
				//else it is a normal record
				else{
					$curr_qty = $prev_qty + $qty[$i];			
					$curr_dist = $prev_dist + $dist[$i];
					$prev_qty = 0;
					$prev_dist = 0;
					
					//if rec is is a missed fill up then update cons with 0
					if($mfill[$i]==1){
						$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET CONSUMPTION=0 WHERE USER_ID=? and _ID=?");
						$stmt->bind_param("ii", $user_id, $curr_id);
						$stmt->execute();

						//mark first full fill up as complete
						$firstFullFillUp = TRUE;
					}
					// if dist = 0 or this is the first full fill up (adding this since first fill up can be partial)
					else if($curr_dist==0 || !$firstFullFillUp){
						$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET CONSUMPTION=NULL WHERE USER_ID=? and _ID=?");
						$stmt->bind_param("ii", $user_id, $curr_id);
						$stmt->execute();
						
						//mark first full fill up as complete
						$firstFullFillUp = TRUE;
					}
					else{
						$f = ($curr_dist*$dist_fact)/($curr_qty*$vol_fact);
						
						if(is_infinite($f) || is_nan($f)){
							$f = 0;				
						}								
						
						$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET CONSUMPTION=? WHERE USER_ID=? AND _ID=?");
						$stmt->bind_param("dii", $f, $user_id, $curr_id);
						$stmt->execute();
					}
				}
			}
			
			//update -1000's to eff of above record
			if($stmt = $mysqli->prepare("SELECT _ID, CONSUMPTION, DIST FROM LOG_TABLE WHERE USER_ID=? AND VEHID=? and TYPE = 0 ORDER BY ODO DESC")){
				$stmt->bind_param("is", $user_id, $vehid);
				$stmt->execute();
				$stmt->bind_result($temp_id2, $temp_eff2, $temp_dist2);
				while($stmt->fetch()){
					$id2[] = $temp_id2;
					$eff2[] = $temp_eff2;
					$dist2[] = $temp_dist2;
				}
				
				$curr_eff = 0;
				$prev_eff = 0;
				$curr_id = 0;
				
				for($i=0; $i<count($id2); $i++){
					$curr_id = $id2[$i];
					$curr_eff = $eff2[$i];
					$curr_dist = $dist2[$i];
					
					//update eff from above eff only if dist is not 0 
					//(in case first rec is partial and OT is odo we do not want this updated)
					if($curr_eff==-1000){
						if($curr_dist==0){
							$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET CONSUMPTION=NULL WHERE USER_ID=? AND _ID=?");
							$stmt->bind_param("ii", $user_id, $curr_id);
							$stmt->execute();
						}
						else{
							$stmt = $mysqli->prepare("UPDATE LOG_TABLE SET CONSUMPTION=? WHERE USER_ID=? AND _ID=?");
							$stmt->bind_param("dii", $prev_eff, $user_id, $curr_id);
							$stmt->execute();
						}
					}
					else{
						$prev_eff = $curr_eff;
					}
				}
			}
		}
		
		// Close the prepared statement.
		if($stmt != null){
			$stmt->close(); 
	    	} 
	}
}

//function to update eff for all vehicles of a user id
function updateEffForAllVeh($mysqli, $user_id){
	$stmt = $mysqli->prepare("SELECT VEHID FROM VEH_TABLE WHERE USER_ID=?");
	$stmt->bind_param("i", $user_id);	
	$stmt->execute();
	$stmt->bind_result($temp_vehid);
	while($stmt->fetch()){
		$vehid[] = $temp_vehid;
	}
							    		
	if(count($vehid)>0){
		for($i=0; $i<count($vehid); $i++){
			//update eff
			updateEff($mysqli, $user_id, $vehid[$i]);
		}
	}
}
?>