<?php

	include ("dbconfig.php");
	$mysqli = new mysqli($host, $user, $password, $db);

	if ($mysqli->connect_errno) {
	     // Let's try this:
	    echo "Sorry, DB";
	    echo "Error: Failed to make a MySQL connection, here is why: \n";
	    echo "Errno: " . $mysqli->connect_errno . "\n";
	    exit;
	}


		function getallitems($mysqli) {

		    $sql = "SELECT * FROM vreg_dept;";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {
		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}


		function getalltownship($mysqli) {

		    $sql = "SELECT * FROM vreg_township;";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {
		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}


		function getalltopic($mysqli) {

		    $sql = "SELECT * FROM vreg_topic;";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {
		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}



		function getactive($mysqli) {
		    $sql = "SELECT * FROM vreg_list WHERE status = 'Waiting' OR status = 'In Session';";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {
		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}




		function getlistbydate($mysqli, $start, $end) {
		    $sql = "SELECT * FROM vreg_list WHERE startdatetime between '".$start."' and '".$end."';";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {
		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}



		function addlog($mysqli, $data, $file_id, $file_face, $file_form) {
			// if ($data['idtype'] == 'nirc') {
			// 	$idvalue = $data['nric_region'] . "/". $data['nric_township'] . "(" . $data['nric_type']. ")" .  $data['nric_digit'];
			// } else {
			// 	$idvalue = $data['nirc_other'];
			// }

			$d = explode(",",$data['dept']);

			if (isset($data['phonenumber'])) $phnumber = $data['phonenumber'];
			else $phnumber = "";

			
			$sql = "INSERT INTO `vreg_list` (`id`, `name`, `nirc`, `phonenumber`, `email`, `address`, `dept`, `township`,`topic`, `cardnumber`, `startdatetime`, `appdatetime`, `enddatetime`,`status`, `pic_id`, `pic_face`, `imgdata`, `pic_form`) VALUES (NULL, NULL, NULL, '".$phnumber."', NULL, NULL, '".$d[0]."', '".$d[1]."', '".$data['topic']."', '".$data['vcard']."', '".
				date('Y-m-d H:i:s')."', NULL, NULL, 'Waiting', '".$file_id."', '".$file_face."', NULL, '".$file_form."');";

			//echo $sql;
		

				if (!$result = $mysqli->query($sql)) {
			        echo "Error (add log): " . $mysqli->error . "\n";
			        exit;
			    }
			

		}


		function adddept($mysqli, $data) {
						
			$sql = "INSERT INTO `vreg_dept` (`id`, `name`, `rfid_no`) VALUES (NULL, '".$data['name']."', '".$data['rfid_no']."');";


		     if (!$result = $mysqli->query($sql)) {
		        echo "Error (add log): " . $mysqli->error . "\n";
		        exit;
		    }
		}


		function addtownship($mysqli, $data) {
						
			$sql = "INSERT INTO `vreg_township` (`id`, `name`, `dept_name`) VALUES (NULL, '".$data['name']."', '".$data['dept_name']."');";


		     if (!$result = $mysqli->query($sql)) {
		        echo "Error (add log): " . $mysqli->error . "\n";
		        exit;
		    }
		}


		function addtopic($mysqli, $data) {
						
			$sql = "INSERT INTO `vreg_topic` (`id`, `name`) VALUES (NULL, '".$data['name']."');";


		     if (!$result = $mysqli->query($sql)) {
		        echo "Error (add log): " . $mysqli->error . "\n";
		        exit;
		    }
		}

		function deldept($mysqli, $data) {
						
			$sql = "DELETE FROM `vreg_dept` WHERE id = " . $data['did'];


		     if (!$result = $mysqli->query($sql)) {
		        echo "Error (add log): " . $mysqli->error . "\n";
		        exit;
		    }
		}

		function deltownship($mysqli, $data) {
						
			$sql = "DELETE FROM `vreg_township` WHERE id = " . $data['did'];


		     if (!$result = $mysqli->query($sql)) {
		        echo "Error (add log): " . $mysqli->error . "\n";
		        exit;
		    }
		}

		function deltopic($mysqli, $data) {
						
			$sql = "DELETE FROM `vreg_topic` WHERE id = " . $data['did'];


		     if (!$result = $mysqli->query($sql)) {
		        echo "Error (add log): " . $mysqli->error . "\n";
		        exit;
		    }
		}



		function getcardbynumber($mysqli, $cardnumber) {

		    $sql = "SELECT * FROM vreg_list where `cardnumber` = '".$cardnumber."' AND (status = 'Waiting' OR status = 'In Session');";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {

		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}



		function checkout($mysqli, $cid) {
		    $sql = "UPDATE `vreg_list` SET `status` = 'Done', `enddatetime` = '".date('Y-m-d H:i:s')."' WHERE `id` = '".$cid."';";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error (add log): " . $mysqli->error . "\n";
		        exit;
		    }
		}



/*

		function getuserbyuandp($mysqli, $username, $password) {

		    $sql = "SELECT * FROM tolldb_users where `username` = '".$username."' AND `password` = '".$password."';";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {

		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}

		function getallusers($mysqli) {

		    $sql = "SELECT * FROM tolldb_users;";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {
		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}


		function getallcars($mysqli) {
		    $sql = "SELECT * FROM tolldb_cars;";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {

		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}

		function getgates($mysqli) {
		    $sql = "SELECT * FROM tolldb_tollgates;";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {

		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}


		function getunit($mysqli) {
		    $sql = "SELECT * FROM tolldb_unit;";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {

		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}

		function getlatesttoll($mysqli) {
		    $sql = "select max(id) as id from tolldb_tolls;";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {

		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}

		function gettollbyid($id, $mysqli) {
		    $sql = "select * from tolldb_tolls where id = ".$id.";";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {

		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}


		function getalldept($mysqli) {

		    $sql = "SELECT * from tolldb_department";
		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {

		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}

		function getalldeptbyid($mysqli, $id) {

		    $sql = "SELECT d.*  FROM tolldb_department as d, tolldb_users as u where d.`id` = u.`deptname` and u.`id` = " . $id;

		    if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
		    } else {

		        $rooms = array();
		        while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        }
		    }
		    return $rooms;
		}




	
	function getuserbyid($mysqli, $id) {
	    $sql = "SELECT * FROM tolldb_users where `id` = " . $id;

	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: getuserbyid " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}


	function getcarbyid($mysqli, $id) {
	    $sql = "SELECT * FROM tolldb_cars where `id` = " . $id;
	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: getuserbyid " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}


	function getgatebyid($mysqli, $id) {
	    $sql = "SELECT * FROM tolldb_tollgates where `id` = " . $id;
	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: getuserbyid " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}


	function gettodaytoll($mysqli) {

		$start = date('Y-m-d 00:00:00');
		$end = date('Y-m-d 23:59:59');


	    $sql = "SELECT * FROM tolldb_tolls WHERE `dtime` between '".$start."' AND '".$end."' order by `type` desc ;";

	    //echo $sql;

	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: gettoll " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}


	function get12hourlabel() {

			$yesterday = date("d/m - ", strtotime( '-1 days' ) );
			$today = date("d/m - ");
			$tomorrow = date("d/m - ", strtotime( '+1 days' ) );

		if (date('H') < 6 ) {
			// start from yesterday 18 to today 6 
			$lables =  array( $yesterday . "18hr" , $yesterday . "19hr" ,$yesterday . "20hr" ,$yesterday . "21hr" ,$yesterday . "22hr" ,$yesterday . "23hr" , $today . "00hr", $today . "01hr", $today . "02hr", $today . "03hr", $today . "04hr", $today . "05hr");

			$currentshift = $yesterday . " 18:00 နာရီ မှ " . $today . " 05:59 နာရီထိ";
			$lastshift = $yesterday . " 06:00 နာရီ မှ " . $yesterday . " 17:59 နာရီထိ";

		} elseif (date('H') < 18 ) {
			// start from today 6 to 18 
			$lables =  array(  $today . "06" , $today . "07" ,$today . "08" ,$today . "09" ,$today . "10" ,$today . "11",  $today . "12" , $today . "13" ,$today . "14" ,$today . "15" ,$today . "16" ,$today . "17" );

			$currentshift = $today . " 06:00 နာရီ မှ " . $today . " 17:59 နာရီထိ";
			$lastshift = $yesterday . " 18:00 နာရီ မှ " . $today . " 05:59 နာရီထိ";

		} else {
			// start from today 18 to tomorrow 6
			$lables =  array( $today . "18Hr" , $today . "19Hr" ,$today . "20Hr" ,$today . "21Hr" ,$today . "22Hr" ,$today . "23Hr" , $tomorrow . "00Hr", $tomorrow . "01Hr", $tomorrow . "02Hr", $tomorrow . "03Hr", $tomorrow . "04Hr", $tomorrow . "05Hr");

			$currentshift = $today . " 18:00 နာရီ မှ " . $tomorrow . " 05:59 နာရီထိ";
			$lastshift = $today . " 06:00 နာရီ မှ " . $today . " 17:59 နာရီထိ";

		}

		return array ("lable" => $lables, "currentshift" => $currentshift , "lastshift" => $lastshift);
	}

	function get24hourlabel($type) {

				$yesterday = date("d/m/Y", strtotime( '-1 days' ) );
				$today = date("d/m/Y");
				$tomorrow = date("d/m/Y", strtotime( '+1 days' ) );
				$thedaybefore = date("d/m/Y ", strtotime( '-2 days' ) );

		if ($type == "6to6") {

				if (date('H') < 6 ) {
					// start from yesterday 18 to today 6 
					$lables =  array( $yesterday . "06hr" , $yesterday . "07hr" ,$yesterday . "08hr" ,$yesterday . "09hr" ,$yesterday . "10hr" , $yesterday . "11hr" , $yesterday . "12hr" , $yesterday . "13hr" , $yesterday . "14hr" , $yesterday . "15hr" , $yesterday . "16hr" , $yesterday . "17hr" , $yesterday . "18hr" , $yesterday . "19hr" , $yesterday . "20hr" , $yesterday . "21hr" , $yesterday . "22hr" , $yesterday . "23" ,  $today . "00hr" , $today . "00hr", $today . "01hr", $today . "02hr", $today . "03hr", $today . "04hr", $today . "05hr");
					$currentshift = $yesterday . " 06:00 နာရီ မှ " . $today . " 05:59 နာရီထိ";
					$lastshift = $thedaybefore . " 06:00 နာရီ မှ " . $yesterday . " 05:59 နာရီထိ";

				} else {
					$lables =  array( $today . "06hr" , $today . "07hr" ,$today . "08hr" ,$today . "09hr" ,$today . "10hr" , $today . "11hr" , $today . "12hr" , $today . "13hr" , $today . "14hr" , $today . "15hr" , $today . "16hr" , $today . "17hr" , $today . "18hr" , $today . "19hr" , $today . "20hr" , $today . "21hr" , $today . "22hr" , $today . "23" ,  $tomorrow . "00hr" , $tomorrow . "00hr", $tomorrow . "01hr", $tomorrow . "02hr", $tomorrow . "03hr", $tomorrow . "04hr", $tomorrow . "05hr");

					$currentshift = $today . " 06:00 နာရီ မှ " . $tomorrow . " 05:59 နာရီထိ";
					$lastshift = $yesterday . " 06:00 နာရီ မှ " . $today . " 05:59 နာရီထိ";
				}

			} else {

				$lables =  array("00hr","01hr","02hr","03hr","04hr","05hr","06hr","07hr","08hr","09hr","10hr","11hr","12hr","13hr","14hr","15hr","16hr","17hr","18hr", "19hr","20hr","21hr","22hr","23hr"); 

				$currentshift = " ယနေ့ ( ". $today . " ) ";
				$lastshift = "မနေ့က ( ". $yesterday . " ) ";


			}

		return array ("lable" => $lables, "currentshift" => $currentshift , "lastshift" => $lastshift);

	}


	function getcurrentshift($period = 'current', $type,  $mysqli) {


		// This function return data of current, last or choosen period date/time range. 
		// for 103, added type to select if it is a shift (6 a.m. to 6 p.m. or the whole day)


		if ($type == "6to6") {

				if ($period == 'lastshift') {

					if (date('H') < 6 ) {
						$start = date('Y-m-d 06:00:00', strtotime( '-2 days' ));
					 	$end = date('Y-m-d 05:59:59', strtotime( '-1 days' ));

					} else {
						$start = date('Y-m-d 06:00:00', strtotime( '-1 days' ));
					 	$end = date('Y-m-d 05:59:59');
					}


				} elseif ($period == 'current') {

					// if (date('H') < 6 ) {
					// 	$start = date('Y-m-d 18:00:00', strtotime( '-1 days' ));
					// 	$end = date('Y-m-d 05:59:59');
					// } elseif (date('H') < 18 ) {
					// 	// start from today 6 to 18 
					// 	$start = date('Y-m-d 06:00:00');
					// 	$end = date('Y-m-d 17:59:59');
					// } else {
					// 	// start from today 18 to tomorrow 6
					// 	$start = date('Y-m-d 18:00:00');
					// 	$end = date('Y-m-d 05:59:59', strtotime( '+1 days' ));
					// }

					if (date('H') < 6 ) {
						$start = date('Y-m-d 06:00:00', strtotime( '-1 days' ));
					 	$end = date('Y-m-d 05:59:59');

					} else {
						$start = date('Y-m-d 06:00:00');
					 	$end = date('Y-m-d 05:59:59', strtotime( '+1 days' ));
					}

				} else {

					$tt = explode('-', $period);

					$start = str_replace(".","-",$tt[0]);
					$end = str_replace(".","-",$tt[1]);
					
				}

		} else { 

			if ($period == 'lastshift') {

				$start = date('Y-m-d 00:00:00', strtotime( '-1 days' ));
				$end = date('Y-m-d 23:59:59', strtotime( '-1 days' ));

			} elseif ($period == 'current') {

				$start = date('Y-m-d 00:00:00');
				$end = date('Y-m-d 23:59:59');

			} else {

					$tt = explode('-', $period);

					$start = str_replace(".","-",$tt[0]);
					$end = str_replace(".","-",$tt[1]);
					
			}

		}



	    $sql = "SELECT * FROM tolldb_tolls WHERE `dtime` between '".$start."' AND '".$end."' order by `type` desc ;";

	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: gettoll " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}

	function getcurrentshifttollbyuidgid($mysqli, $uid, $gid) {

		// if (date('H') < 6 ) {
		// 	$start = date('Y-m-d 18:00:00', strtotime( '-1 days' ));
		// 	$end = date('Y-m-d 05:59:59');
		// } elseif (date('H') < 18 ) {
		// 	// start from today 6 to 18 
		// 	$start = date('Y-m-d 06:00:00');
		// 	$end = date('Y-m-d 17:59:59');
		// } else {
		// 	// start from today 18 to tomorrow 6
		// 	$start = date('Y-m-d 18:00:00');
		// 	$end = date('Y-m-d 05:59:59', strtotime( '+1 days' ));
		// }


		$start = date('Y-m-d 00:00:00');
		$end = date('Y-m-d 23:59:59');

	    $sql = "SELECT * FROM tolldb_tolls WHERE `dtime` between '".$start."' AND '".$end."' AND `userid` = '".$uid."' AND `tollgate` = '".$gid."' order by `type` desc ;";

	    //echo $sql;

	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: gettoll " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}


	function gettodaytollbyuidgid($mysqli, $uid, $gid) {

		$start = date('Y-m-d 00:00:00');
		$end = date('Y-m-d 23:59:59');

	    $sql = "SELECT * FROM tolldb_tolls WHERE `dtime` between '".$start."' AND '".$end."' AND `userid` = '".$uid."' AND `tollgate` = '".$gid."' order by `type` desc ;";

	    //echo $sql;

	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: gettoll " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}

	function gettodaytollbyuidgidstartandend($mysqli, $uid, $gid, $start, $end) {

	    $sql = "SELECT * FROM tolldb_tolls WHERE `dtime` between '".$start."' AND '".$end."' AND `userid` = '".$uid."' AND `tollgate` = '".$gid."' order by `type` desc ;";

	    //echo $sql;

	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: gettoll " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}



	function gettodaytollvtype($mysqli) {

	    $sql = "SELECT `type`  FROM tolldb_tolls Group by `type` order by `type` DESC;";

	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: gettoll " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}

	function getcolorbyname($name, $mysqli) {

	    $sql = "SELECT `colorcode`  FROM tolldb_cars WHERE vtype = '" . $name . "'";

	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: gettoll " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}

	function getuserbydept($mysqli, $did) {
	    $sql = "SELECT * FROM tolldb_users where `deptname` = " . $did;
	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: getuserbyid " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}

	function getsamedeptuser($mysqli, $id){

		//select * from tolldb_users where deptname = 1 and status <> 0; 
		$sql = "SELECT * FROM tolldb_users where  `status` <> 0 AND `deptname` = " . $id;
	    if (!$result = $mysqli->query($sql)) {
	    	echo $sql;
	        echo "Error: getuserbyid " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;


	}


	function adduser($mysqli, $data) {
		$sql = "INSERT INTO `tolldb_users` (`id`, `username`, `password`, `usertype`, `fullname`, `deptname`, `email`, `status`, `lastupdate`) 
		VALUES (NULL, '".htmlspecialchars($data['useremail'], ENT_QUOTES)."', '1', '".htmlspecialchars($data['usertype'], ENT_QUOTES)."', '".htmlspecialchars($data['username'], ENT_QUOTES)."', '".htmlspecialchars($data['userdept'], ENT_QUOTES)."', '".htmlspecialchars($data['useremail'], ENT_QUOTES)."', 1 , ".time().");";
	     if (!$result = $mysqli->query($sql)) {
	        echo "Error (addnewuser): " . $mysqli->error . "\n";
	        exit;
	    } 
	}

	function adduserwithid($mysqli, $data) {
		$sql = "INSERT INTO `tolldb_users` (`id`, `username`, `password`, `usertype`, `fullname`, `deptname`, `email`, `status`, `lastupdate`) 
		VALUES (".$data['id'].", '".htmlspecialchars($data['username'], ENT_QUOTES)."', '".$data['password']."', '".htmlspecialchars($data['usertype'], ENT_QUOTES)."', '".htmlspecialchars($data['fullname'], ENT_QUOTES)."', '".htmlspecialchars($data['deptname'], ENT_QUOTES)."', '".htmlspecialchars($data['email'], ENT_QUOTES)."', 1 , ".$data['lastupdate'].");";
	     if (!$result = $mysqli->query($sql)) {
	        echo "Error (addnewuser): " . $mysqli->error . "\n";
	        exit;
	    } 
	}


	function addcar_old($mysqli, $data, $sdata) {
	$sql = "INSERT INTO `tolldb_cars` (`id`, `vtype`, `colorcode`, `value`, `status`, `lastupdate`, `lastupdatedby`, `lastupdatedbyid`, `maxload`, `maxloadrate`) VALUES (NULL, '".htmlspecialchars($data['vtype'], ENT_QUOTES)."', '".$data['colorcode']."', '".$data['value']."', '".$data['status']."', '".date("Y-m-d H:i:s")."', '".$sdata['user_name']."', '".$sdata['user_id']."' , '".$data['maxload']."' , '".$data['maxloadrate']."');
";
	     if (!$result = $mysqli->query($sql)) {
	        echo "Error (addnewcard): " . $mysqli->error . "\n";
	        exit;
	    } 
	}


	function addcar($mysqli, $data, $sdata) {
	$sql = "INSERT INTO `tolldb_cars` (`id`, `vtype`, `colorcode`, `status`, `lastupdate`, `lastupdatedby`, `lastupdatedbyid`, `unit`, `l-limit`) VALUES (NULL, '".htmlspecialchars($data['vtype'], ENT_QUOTES)."', '".$data['colorcode']."', '".$data['status']."', '".date("Y-m-d H:i:s")."', '".$sdata['user_name']."', '".$sdata['user_id']."', '".htmlspecialchars($data['unit'])."', '".$data['l-limit']."' );
";
	     if (!$result = $mysqli->query($sql)) {
	        echo "Error (addnewcard): " . $mysqli->error . "\n";
	        exit;
	    } 
	}



	function addcar__($mysqli, $data, $sdata) {
	$sql = "INSERT INTO `tolldb_cars` (`id`, `vtype`, `colorcode`, `value`, `status`, `lastupdate`, `lastupdatedby`, `lastupdatedbyid`, `maxload`, `maxloadrate`, `n-limit` , `n-rate` , `a-limit` , `a-rate` , `b-limit` , `b-rate` , `c-limit` , `c-rate` , `l-limit`) VALUES (NULL, '".htmlspecialchars($data['vtype'], ENT_QUOTES)."', '".$data['colorcode']."', '0', '".$data['status']."', '".date("Y-m-d H:i:s")."', '".$sdata['user_name']."', '".$sdata['user_id']."' , '0' , '0', '".$data['n-limit']."' , '".$data['n-rate']."', '".$data['a-limit']."', '".$data['a-rate']."', '".$data['b-limit']."', '".$data['b-rate']."' , '".$data['c-limit']."', '".$data['c-rate']."' , '".$data['l-limit']."' );
";
	     if (!$result = $mysqli->query($sql)) {
	        echo "Error (addnewcard): " . $mysqli->error . "\n";
	        exit;
	    } 
	}


	function getvdetailbytype($mysqli, $type) {
		$sql = "select * from tolldb_cars where `vtype` = '" .$type. "'";

		if (!$result = $mysqli->query($sql)) {
        echo "Error (send1): " . $mysqli->error . "\n";
        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }

	    return $rooms;

	}

	function edituser($mysqli, $data) {
		$sql = "UPDATE `tolldb_users` 
	            SET
	            `username` = '".htmlspecialchars($data['euseremail'], ENT_QUOTES)."',
	            `usertype` = '".htmlspecialchars($data['eusertype'], ENT_QUOTES)."',
	            `fullname` = '".htmlspecialchars($data['eusername'], ENT_QUOTES)."',
	            `deptname` = '".htmlspecialchars($data['euserdept'], ENT_QUOTES)."',
	            `email` = '".htmlspecialchars($data['euseremail'], ENT_QUOTES)."',
	            `lastupdate` = '".time()."'
	            WHERE `id` = ".$data['euserid']; 
	        
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } 
	}

	function updatelocaluser($mysqli, $data) {
		$sql = "UPDATE `tolldb_users` 
	            SET
	            `username` = '".$data['username']."',
	            `usertype` = '".$data['usertype']."',
	            `password` = '".$data['password']."',
	            `fullname` = '".$data['fullname']."',
	            `deptname` = '".$data['deptname']."',
	            `email` = '".$data['email']."',
	            `lastupdate` = '".$data['lastupdate']."'
	            WHERE `id` = ".$data['id']; 
	        
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } 
	}


	function editcar_old($mysqli, $data, $sdata) {
		$sql = "UPDATE `tolldb_cars` 
	            SET
	            `vtype` = '".htmlspecialchars($data['evtype'], ENT_QUOTES)."',
	            `colorcode` = '".$data['ecolorcode']."',
	            `value` = '".$data['evalue']."',
	            `status` = '".$data['estatus']."',
	            `lastupdate` = '".date("Y-m-d H:i:s")."',
	            `lastupdatedby` = '".htmlspecialchars($sdata['user_name'], ENT_QUOTES)."',
	            `lastupdatedbyid` = '".$sdata['user_id']."',
	            `maxload` = '".$data['emaxload']."',
	            `maxloadrate` = '".$data['emaxloadrate']."'
	           WHERE `id` = ".$data['ecarid']; 

	    
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } 
	}

	function editcar($mysqli, $data, $sdata) {
		$sql = "UPDATE `tolldb_cars` 
	            SET
	            `vtype` = '".htmlspecialchars($data['evtype'], ENT_QUOTES)."',
	            `colorcode` = '".$data['ecolorcode']."',
	            `status` = '".$data['estatus']."',
	            `lastupdate` = '".date("Y-m-d H:i:s")."',
	            `lastupdatedby` = '".htmlspecialchars($sdata['user_name'], ENT_QUOTES)."',
	            `lastupdatedbyid` = '".$sdata['user_id']."',

	           

	            `l-limit` = '".$data['el-limit']."'

	           WHERE `id` = ".$data['ecarid']; 

	    
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } 
	}

	function editcar____($mysqli, $data, $sdata) {
		$sql = "UPDATE `tolldb_cars` 
	            SET
	            `vtype` = '".htmlspecialchars($data['evtype'], ENT_QUOTES)."',
	            `colorcode` = '".$data['ecolorcode']."',
	            `status` = '".$data['estatus']."',
	            `lastupdate` = '".date("Y-m-d H:i:s")."',
	            `lastupdatedby` = '".htmlspecialchars($sdata['user_name'], ENT_QUOTES)."',
	            `lastupdatedbyid` = '".$sdata['user_id']."',

	            `n-limit` = '".$data['en-limit']."',
	            `n-rate` = '".$data['en-rate']."',

	            `a-limit` = '".$data['ea-limit']."',
	            `a-rate` = '".$data['ea-rate']."',

	            `b-limit` = '".$data['eb-limit']."',
	            `b-rate` = '".$data['eb-rate']."',

	            `c-limit` = '".$data['ec-limit']."',
	            `c-rate` = '".$data['ec-rate']."', 

	            `l-limit` = '".$data['el-limit']."'

	           WHERE `id` = ".$data['ecarid']; 

	    
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } 
	}



	function updateuser($mysqli, $data) {
		$sql = "UPDATE `tolldb_users` 
	            SET `password` = '".$data['repassword']."',
	            `lastupdate` = '".time()."'
	            WHERE `id` = ".$data['upid']; 

	    
	        
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: uu " . $mysqli->error . "\n";
	        exit;
	    } 
	}


	function updatuserpasswordbyid($mysqli, $data) {
		$sql = "UPDATE `tolldb_users` 
	            SET `password` = '".$data['password']."',
	            `lastupdate` = '".$data['lastupdate']."'
	            WHERE `id` = ".$data['id'];

	     echo $sql; 

	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: kkk " . $mysqli->error . "\n";
	        exit;
	    } 
	}

	function updateuserself($mysqli, $repass, $userid) {
		$sql = "UPDATE `tolldb_users` 
	            SET `password` = '".$repass."',
	            `lastupdate` = '".time()."'
	            WHERE `id` = ".$userid; 

	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: uu " . $mysqli->error . "\n";
	        exit;
	    } 
	}


	function getdeptemails($mysqli, $id) {

	    $sql = "SELECT * FROM tolldb_users WHERE deptname =  " . $id;
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } else {
	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row['email'];
	        }
	    }
	    return $rooms;
	}


	function adddept($mysqli, $data){

		$sql = "INSERT INTO `tolldb_department` (`id`, `name`) 
		VALUES (NULL, '".htmlspecialchars($data['deptname'], ENT_QUOTES)."');";
	     if (!$result = $mysqli->query($sql)) {
	        echo "Error (addnewwsemail): " . $mysqli->error . "\n";
	        exit;
	    } 
	}

	function updatedept($mysqli, $data) {
		$sql = "UPDATE `tolldb_department` 
	            SET
	            `name` = '".htmlspecialchars($data['mdeptname'], ENT_QUOTES)."'
	            WHERE `id` = ".$data['mdeptid']; 
	        
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } 
	}

	function deletedept($mysqli, $id){

		$sql = "DELETE FROM `tolldb_department` WHERE `id` = " . $id;
	     if (!$result = $mysqli->query($sql)) {
	        echo "Error (deletews): " . $mysqli->error . "\n";
	        exit;
	    } 
	}

	function deleteuser($mysqli, $id) {
		$sql = "DELETE FROM `tolldb_users` WHERE `id` IN ('".$id."');";
	     if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } 
		    
	}

	function deletev($mysqli, $id) {
		$sql = "DELETE FROM `tolldb_cars` WHERE `id` IN ('".$id."');";
	     if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } 
		    
	}


	function genrandom() {
		return md5(uniqid(rand(), true));
	}

	function addtollrequest_old($mysqli, $data, $mid) {
		if (!isset($data['weight'])) $weight = "0";
		else $weight = $data['weight'];

		$extraamount = ($data['extraweight'] * $data['roomrate']) * 10;

		$sql = "INSERT INTO `tolldb_tolls` (`id`, `type`, `amount`, `platenumber`, `weight`, `dtime`, `username`, `userid`, `tollgate`, `gatename`, `machineid`,`remoteip` , `extraweight` , `extrarate`, `extraamount`) VALUES (NULL, '".htmlspecialchars($data['vtype'], ENT_QUOTES)."', '".htmlspecialchars($data['roomrate'], ENT_QUOTES)."', '".htmlspecialchars(strtoupper($data['platenumber']), ENT_QUOTES)."', '".htmlspecialchars($weight, ENT_QUOTES)."', '".date('Y-m-d H:i:s')."', '".$_SESSION['user_name']."', '".$_SESSION['user_id']."', '".$data['gateid']."', '".htmlspecialchars($data['gatename'], ENT_QUOTES)."', '".$mid."', '".$_SERVER['REMOTE_ADDR']."' , '".$data['extraweight']."', '".$data['extrarate']."', '".$extraamount."');";

	     if (!$result = $mysqli->query($sql)) {
	        echo "Error (addnewrequest): " . $mysqli->error . "\n";
	        exit;
	    }
	}


	function addtollrequest($mysqli, $data, $mid) {
		if (!isset($data['weight'])) $weight = "0";
		else $weight = $data['weight'];

		

		$sql = "INSERT INTO `tolldb_tolls` (`id`, `type`, `amount`, `platenumber`, `weight`, `dtime`, `username`, `userid`, `tollgate`, `gatename`, `machineid`,`remoteip` , `gunit` , `extrarate`, `extraamount`) VALUES (NULL, '".htmlspecialchars($data['vtype'], ENT_QUOTES)."', '".htmlspecialchars($data['vvalue'], ENT_QUOTES)."', '".htmlspecialchars(strtoupper($data['platenumber']), ENT_QUOTES)."', '".htmlspecialchars($weight, ENT_QUOTES)."', '".date('Y-m-d H:i:s')."', '".$_SESSION['user_name']."', '".$_SESSION['user_id']."', '".$data['gateid']."', '".htmlspecialchars($data['gatename'], ENT_QUOTES)."', '".$mid."', '".$_SERVER['REMOTE_ADDR']."' , '".htmlspecialchars($data['gunit'], ENT_QUOTES)."', '".htmlspecialchars($data['grate'], ENT_QUOTES)."', '0');";

	     if (!$result = $mysqli->query($sql)) {
	        echo "Error (addnewrequest): " . $mysqli->error . "\n";
	        exit;
	    }
	}



	function gettolleight($mysqli) {

	    $sql = "SELECT * FROM `tolldb_tolls` order by dtime DESC  LIMIT 0,3;";
    	if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
	    	} else {

	        	$rooms = array();
	        	while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        	}
	    	}
    	return $rooms;
	}


	function gettolleightbyuser($uid, $gid, $mysqli) {

	    $sql = "SELECT * FROM `tolldb_tolls` WHERE `userid` = ".$uid."  AND `tollgate` = ".$gid." order by dtime DESC  LIMIT 0,3;";
    	if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
	    	} else {

	        	$rooms = array();
	        	while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        	}
	    	}
    	return $rooms;
	}


	function getalltolls($mysqli) {

	    $sql = "SELECT * FROM `tolldb_tolls` order by dtime DESC;";
    	if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
	    	} else {

	        	$rooms = array();
	        	while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        	}
	    	}
    	return $rooms;
	}


	function getalltollsbygate($mysqli, $id) {

	    $sql = "SELECT * FROM `tolldb_tolls` where `tollgate` = '".$id."' order by dtime DESC;";
    	if (!$result = $mysqli->query($sql)) {
		        echo "Error: " . $mysqli->error . "\n";
		        exit;
	    	} else {

	        	$rooms = array();
	        	while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        	}
	    	}
    	return $rooms;
	}

	function getallroomrequestbyuserdept($mysqli, $dept) {

	    //$sql = "SELECT * FROM approval_entapproval WHERE requestedby = " . $id . " order by id DESC";
	    $sql = "SELECT a.*, u.`deptname` FROM approval_roomrequest as a, `tolldb_users` as u WHERE  a.`requestedby` = u.`id` AND u.`deptname` = " . $dept;

	    	if (!$result = $mysqli->query($sql)) {
			        echo "Error: " . $mysqli->error . "\n";
			        exit;
		    	} else {

		        	$rooms = array();
		        	while($row = $result->fetch_assoc()) {
		            $rooms[] = $row;
		        	}
		    	}
    	return $rooms;
    }


	function syncupdate($mysqli, $key) {

		$result = $mysqli->query("SELECT * from `tolldb_synstatus` WHERE `key` = '".$key."';" );
		$rr = $result->fetch_assoc();

		if ($rr == NULL) $sql = "INSERT INTO `tolldb_synstatus` (`id`, `key`, `value`) VALUES (NULL, '".$key."', '".time()."');";
		else $sql = "UPDATE `tolldb_synstatus` SET `value` = '" . time() . "' WHERE `key` = '".$key."'";

		if (!$result = $mysqli->query($sql)) {
	        echo "Error (addnewrequest): " . $mysqli->error . "\n";
	        exit;
	    }
		
	}

	
	function getmylatest($mysqli) {
	    $sql = "select max(id) as maxid from tolldb_tolls;";
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}


	function getlateststatus($mysqli) {
	    $sql = "select * from tolldb_synstatus;";
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}


	function getlateststatus_user($mysqli) {
	    $sql = "select `lastupdate` from tolldb_users order by `lastupdate` DESC limit 1;";
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}


	function getmytollfrom($mysqli, $id) {
	    $sql = "select * from tolldb_tolls where id > " . $id;
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}

	function getlatestbymid($mysqli, $mid) {
	    $sql = "select max(remoteid) as remoteid from tolldb_tolls where machineid = '".$mid."';";
	    if (!$result = $mysqli->query($sql)) {
	        echo "Error: " . $mysqli->error . "\n";
	        exit;
	    } else {

	        $rooms = array();
	        while($row = $result->fetch_assoc()) {
	            $rooms[] = $row;
	        }
	    }
	    return $rooms;
	}
	

	function addtollremote($mysqli, $data) {
	
		$sql = "INSERT INTO `tolldb_tolls` (`id`, `type`, `amount`, `platenumber`, `weight`, `dtime`, `username`, `userid`, `tollgate`, `gatename`, `machineid`,`remoteip`, `remoteid` , `extraweight` , `extrarate`, `extraamount`) 
		VALUES (NULL, '".htmlspecialchars($data['type'], ENT_QUOTES)."', '".htmlspecialchars($data['amount'], ENT_QUOTES)."', '".htmlspecialchars(strtoupper($data['platenumber']), ENT_QUOTES)."', '".htmlspecialchars($data['weight'], ENT_QUOTES)."', '".$data['dtime']."', '".$data['username']."', '".$data['userid']."', '".$data['tollgate']."', '".htmlspecialchars($data['gatename'], ENT_QUOTES)."', '".$data['machineid']."', '".$_SERVER['REMOTE_ADDR']."' , '".$data['remoteid']."', '".$data['extraweight']."', '".$data['extrarate']."', '".$data['extraamount']."');";

	     if (!$result = $mysqli->query($sql)) {
	        echo "Error (addnewrequest): " . $mysqli->error . "\n";
	        exit;
	    }

	    //syncupdate($mysqli, $mid . "-tolls");

	}

*/


?>