<?php
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;

    function localTyme($time){
        $localTime = date('h:i A', strtotime($time));
        if (empty($time)) {
            $localTime = "N/A";
            return $localTime;
        }else{
            return $localTime;
        }
        
    } 


	function uploadFile($file, $target_dir) {
		$value = "";
		$original_name = $file["name"];
		$target_file = $target_dir . basename($original_name);

		// Check if file already exists
		$counter = 1;
		while (file_exists($target_file)) {
			$new_name = pathinfo($original_name, PATHINFO_FILENAME) . "_" . $counter . "." . pathinfo($original_name, PATHINFO_EXTENSION);
			$target_file = $target_dir . $new_name;
			$counter++;
		}

		// Check file size
		if ($file["size"] > 500000) {
			//$value .= "Sorry, your file is too large. ";
			//return $value;
			$value .= "error";
		}

		// Allow only certain file formats
		$allowed_types = array("jpg", "jpeg", "png", "gif");
		$file_extension = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));
		if (!in_array($file_extension, $allowed_types)) {
			//$value .= "Sorry, only JPG, JPEG, PNG, and GIF files are allowed. ";
			$value .= "error";
			//return $value
		}

		// Try to upload the file
		if (move_uploaded_file($file["tmp_name"], $target_file)) {
			
			//return "The file " . htmlspecialchars(basename($target_file)) . " has been uploaded.";
			$uploaded = htmlspecialchars(basename($target_file));
		} else {
			$uploaded = "error";
			
		}
		
			return $uploaded;
		
		
	}

     function localDate($date){
        $localDate = date('d/m/Y', strtotime($date));
        if (empty($date)) {
            $localDate = "N/A";
            return $localDate;
        }else{
            return $localDate;
        }
    }

 function getCommand($commandId){
        $results = DB::queryFirstRow("SELECT * FROM command WHERE command_id=%i", $commandId);
        $array=[];
        if($results==true){
            $array['command_name'] = $results['command_name'];
            $array['location'] = $results['location'];
            $array['command_code'] = $results['command_code'];
        }
    return $array;
    }
    
    

function getPassChange($passId){
        if($passId=='1'){
			$passChange='Yes';
			return $passChange;
		}else{
			$passChange='No';
			return $passChange;	
		}
    
    }

    function getCountry($countryId){
        $results = DB::queryFirstRow("SELECT * FROM country WHERE country_id=%i", $countryId);
        $array=[];
        if($results==true){
            $array['country_name'] = $results['country_name'];
        }
    return $array;
    }
   
    function getVehicleDetails($vehicleId){
        $results = DB::queryFirstRow("SELECT * FROM vehicles WHERE vehicle_id=%i", $vehicleId);
        $array=[];
        if($results==true){
			
            $array['vehicle_make'] = $results['vehicle_make'];
            $array['vehicle_model'] = $results['vehicle_model'];
            $array['vehicle_year'] = $results['vehicle_year'];
            $array['vehicle_status'] = $results['vehicle_status'];
			$array['vin'] = $results['vin'];
			$array['vin_type'] = $results['vin_type'];
			$array['milage'] = $results['milage'];
			$array['colour'] = $results['colour'];
			$array['vehicle_category'] = $results['vehicle_category'];
			$array['origin_country'] = $results['origin_country'];
			$array['ref_no'] = $results['ref_no'];
			$array['custody_type'] = $results['custody_type'];
			//$array['min_price'] = $results['min_price'];
			$array['command_code'] = $results['command_code'];
			$array['status'] = $results['status'];
			$array['transmission'] = $results['transmission'];
			$array['engine_no'] = $results['engine_no'];
			$array['engine_type'] = $results['engine_type'];
			$array['drive_train'] = $results['drive_train'];
			$array['created_by'] = $results['created_by'];
			$array['date_created'] = $results['date_created'];
        }else{
			$array['vehicle_make'] = "N/A";
            $array['vehicle_model'] = "N/A";
            $array['vehicle_year'] = "N/A";
            $array['vehicle_status'] = "N/A";
			$array['vin'] = "N/A";
			$array['vin_type'] = "N/A";
			$array['milage'] = "N/A";
			$array['colour'] = "N/A";
			$array['vehicle_category'] = "N/A";
			$array['origin_country'] = "N/A";
			$array['ref_no'] = "N/A";
			$array['custody_type'] = "N/A";
			//$array['min_price'] = "N/A";
			$array['command_code'] = "N/A";
			$array['status'] = "N/A";
			$array['transmission'] = "N/A";
			$array['engine_no'] = "N/A";
			$array['engine_type'] = "N/A";
			$array['drive_train'] = "N/A";
			$array['created_by'] = "N/A";
			$array['date_created'] = "N/A";
		}
    return $array;
    }
    
        function getContainerDetails($containerId){
        $results = DB::queryFirstRow("SELECT * FROM containers WHERE container_id=%i", $containerId);
        $array=[];
        if($results==true){
			
            $array['container_number'] = $results['container_number'];
            $array['container_size'] = $results['container_size'];
            $array['origin_country'] = $results['origin_country'];
            $array['container_year'] = $results['container_year'];
			$array['gross_weight'] = $results['gross_weight'];
			$array['custody_type'] = $results['custody_type'];
			$array['command'] = $results['command'];
			$array['status'] = $results['status'];
			$array['mini_price'] = $results['mini_price'];
			$array['ref_no'] = $results['ref_no'];
			$array['created_by'] = $results['created_by'];
			$array['date_created'] = $results['date_created'];
        }else{
			$array['container_number'] = "N/A";
            $array['container_size'] = "N/A";
            $array['origin_country'] = "N/A";
            $array['container_year'] = "N/A";
			$array['gross_weight'] = "N/A";
			$array['custody_type'] = "N/A";
			$array['command'] = "N/A";
			$array['status'] = "N/A";
			$array['mini_price'] = "N/A";
			$array['ref_no'] = "N/A";
			$array['created_by'] = "N/A";
			$array['date_created'] = "N/A";
		}
    return $array;
    }
    
    function getVehicleImage($vehicleId){
        $results = DB::queryFirstRow("SELECT * FROM vehicle_images WHERE vehicle_id='$vehicleId' and (image_title='Front View' or image_title='Right View' or image_title='Left View' or image_title='Rear View' or image_title='Interior')");
        $array=[];
        if($results==true){
			
            $array['image_title'] = $results['image_title'];
            $array['image_url'] = $results['image_url'];
			$array['status'] = $results['status'];
			$array['created_by'] = $results['created_by'];
			$array['date_created'] = $results['date_created'];
        }else{
			 $array['image_title'] = "N/A";
            $array['image_url'] = "N/A";
			$array['status'] = "N/A";
			$array['created_by'] = "N/A";
			$array['date_created'] = "N/A";
		}
    return $array;
    }
 
     function getContainerImage($containerId){
        $results = DB::queryFirstRow("SELECT * FROM container_images WHERE container_id='$containerId' and (image_title='Front View' or image_title='Right View' or image_title='Left View' or image_title='Rear View') ");
        $array=[];
        if($results==true){
			
            $array['image_title'] = $results['image_title'];
            $array['image_url'] = $results['image_url'];
			$array['status'] = $results['status'];
			$array['created_by'] = $results['created_by'];
			$array['date_created'] = $results['date_created'];
        }else{
			 $array['image_title'] = "N/A";
            $array['image_url'] = "N/A";
			$array['status'] = "N/A";
			$array['created_by'] = "N/A";
			$array['date_created'] = "N/A";
		}
    return $array;
    }
  
  function listedItem($vehicleId){
        $results = DB::queryFirstRow("SELECT * FROM listed_items WHERE item_id=%i", $vehicleId);
        $array=[];
        if($results==true){
			
            $array['item_id'] = $results['item_id'];
            $array['window_ref'] = $results['window_ref'];
			$array['status'] = $results['status'];
			$array['current_bid'] = $results['current_bid'];
			$array['created_by'] = $results['created_by'];
			$array['date_created'] = $results['date_created'];
        }else{
			$array['item_id'] = "N/A";
            $array['window_ref'] = "N/A";
			$array['status'] = "N/A";
			$array['current_bid'] = "N/A";
			$array['created_by'] = "N/A";
			$array['date_created'] = "N/A";
		}
    return $array;
    }


    function getUserDetails($applicantId){
        $results = DB::queryFirstRow("SELECT * FROM applicants WHERE applicant_id=%i", $applicantId);
        $array=[];
        if($results==true){
			//$passChange = getPassChange($results['pass_change']);
            $array['tin'] = $results['tin'];
            $array['fullname'] = $results['fullname'];
            $array['phone'] = $results['phone'];
            $array['company_name'] = $results['company_name'];
			$array['email'] = $results['email'];
			$array['rc_number'] = $results['rc_number'];
			//$array['pass_change'] = $passChange;
			$array['access_level'] = $results['access_level'];
			$array['nin'] = $results['nin'];
			$array['password'] = $results['password'];
			$array['status'] = $results['status'];
			$array['date_created'] = $results['date_created'];
			$array['last_login'] = $results['last_login'];
        }else{
				//$passChange = getPassChange($results['pass_change']);
            $array['tin'] = 'N/A';
            $array['fullname'] = 'N/A';
            $array['phone'] = 'N/A';
            $array['company_name'] = 'N/A';
			$array['email'] = 'N/A';
			$array['rc_number'] = 'N/A';
		//	$array['pass_change'] = 'N/A';
			$array['access_level'] = 'N/A';
			$array['nin'] = 'N/A';
			$array['password'] = 'N/A';
			$array['status'] = 'N/A';
			$array['date_created'] = 'N/A';
			$array['last_login'] = 'N/A';
		}
    return $array;
    }
  
  
 function getWallet($userId){
        $results = DB::queryFirstRow("SELECT * FROM wallets WHERE applicant_id=%i", $userId);
        $array=[];
        if($results==true){
            $array['applicant_id'] = $results['applicant_id'];
            $array['wallet_id'] = $results['wallet_id'];
            $array['transaction_ref'] = $results['transaction_ref'];
            $array['bank'] = $results['bank'];
            $array['amount'] = $results['amount'];
            $array['balance'] = $results['balance'];
            $array['status'] = $results['status'];
            $array['date_created'] = $results['date_created'];
            // $array['transaction_ref'] = $results['command_code'];
        }
    return $array;
    }
    
    function getTransaction($transactionId){
        $results = DB::queryFirstRow("SELECT * FROM transactions WHERE transaction_id=%i", $transactionId);
        $array=[];
        if($results==true){
            $array['applicant_id'] = $results['applicant_id'];
            $array['wallet_id'] = $results['wallet_id'];
            $array['transaction_ref'] = $results['transaction_ref'];
            $array['transaction_type'] = $results['transaction_type'];
            $array['ballance_before'] = $results['ballance_before'];
            $array['transaction_amount'] = $results['transaction_amount'];
            $array['ballance_after'] = $results['ballance_after'];
            $array['date_created'] = $results['date_created'];
            $array['time_stamp'] = $results['time_stamp'];
        }
    return $array;
    }
      
 

 function getVehicleCategory($vehicleCategoryId){
        $results = DB::queryFirstRow("SELECT * FROM vehicle_category WHERE vehicle_category_id=%i", $vehicleCategoryId);
        $armStatus=[];
        if($results==true){
            $armStatus['vehicle_category_id'] = $results['vehicle_category_id'];
            
            return $armStatus;
        }
    }
 
function getApplication($refNo){
        $results = DB::queryFirstRow("SELECT * FROM applications WHERE ref_no=%s", $refNo);
        $application=[];
        if($results==true){
            $application['command_id'] = $results['command_id'];
			$application['terminal_id'] = $results['terminal_id'];
			$application['ref_no'] = $results['ref_no'];
			$application['lock_status'] = $results['lock_status'];
			$application['locked_by'] = $results['locked_by'];
			$application['expiry_time'] = $results['expiry_time'];
			$application['consignee'] = $results['consignee'];
			$application['declarant_email'] = $results['declarant_email'];
			$application['bill_number'] = $results['bill_number'];
			$application['paar_number'] = $results['paar_number'];
			$application['container_size'] = $results['container_size'];
			$application['container_number'] = $results['container_number'];
			$application['currency'] = $results['currency'];
			$application['paar_available'] = $results['paar_available'];
			$application['submission_status'] = $results['submission_status'];
			$application['qr_code'] = $results['qr_code'];
			$application['reason'] = $results['reason'];
			$application['review_comment'] = $results['review_comment'];
			$application['commento'] = $results['commento'];
			$application['reviewed_by'] = $results['reviewed_by'];
			$application['date_reviewed'] = $results['date_reviewed'];
			$application['valued_by'] = $results['valued_by'];
			$application['date_valued'] = $results['date_valued'];
			$application['supervisor_svn'] = $results['supervisor_svn'];
			$application['supervisor_signature'] = $results['supervisor_signature'];
			$application['cac_signature'] = $results['cac_signature'];
			$application['cac_svn'] = $results['cac_svn'];
			$application['date_created'] = $results['date_created'];
            
            return $application;
        }else{
			$application['command_id'] = 'N/A';
			$application['terminal_id'] = 'N/A';
			$application['ref_no'] = 'N/A';
			$application['lock_status'] = 'N/A';
			$application['locked_by'] = 'N/A';
			$application['expiry_time'] = 'N/A';
			$application['consignee'] = 'N/A';
			$application['declarant_email'] = 'N/A';
			$application['bill_number'] = 'N/A';
			$application['paar_number'] = 'N/A';
			$application['container_size'] = 'N/A';
			$application['container_number'] = 'N/A';
			$application['currency'] = 'N/A';
			$application['paar_available'] = 'N/A';
			$application['submission_status'] = 'N/A';
			$application['qr_code'] = 'N/A';
			$application['reason'] = 'N/A';
			$application['review_comment'] = 'N/A';
			$application['commento'] = 'N/A';
			$application['reviewed_by'] = 'N/A';
			$application['date_reviewed'] = 'N/A';
			$application['valued_by'] = 'N/A';
			$application['date_valued'] = 'N/A';
			$application['supervisor_svn'] = 'N/A';
			$application['supervisor_signature'] = 'N/A';
			$application['cac_signature'] = 'N/A';
			$application['cac_svn'] = 'N/A';
			$application['date_created'] = 'N/A';
            
            return $application;
		}
    }



 function getTerminalDropdown($commandId){
        $query = DB::query("SELECT * FROM terminal WHERE command_id=%s", $commandId);
        
        if($query==true){
            $terminal = ' <option value="">Select Terminal</option>';
                foreach($query as $results){
                    $terminal .="<option value='".$results['terminal_id']."'>".$results['terminal_name']."</option>";
                }
        }else{
            $terminal = '<option>No Terminal</option>';
        }
        return $terminal;
    }
    
    
    
   

function sendMessage($email, $subject, $message, $recipient){
	// SEND MAIL

                    require 'phpmailer/src/Exception.php';
                    require 'phpmailer/src/PHPMailer.php';
                    require 'phpmailer/src/SMTP.php';
                    
                    // Instantiation and passing `true` enables exceptions
                    $mail = new PHPMailer(true);
                    
                    try {
                        //Server settings
                        // $mail->SMTPDebug = SMTP::DEBUG_SERVER;                      // Enable verbose debug output
                        $mail->isSMTP();                                            // Send using SMTP
						$mail->Host       = 'teqbro.com';                    // Set the SMTP server to send through
						$mail->SMTPAuth   = true;                                   // Enable SMTP authentication
						$mail->Username   = 'info.eauction@teqbro.com';                     // SMTP username 
						$mail->Password   = 'eauction2023';                               // SMTP password
						$mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS;         // Enable TLS encryption ENCRYPTION_STARTTLS; 			`PHPMailer::ENCRYPTION_SMTPS` also accepted
						$mail->Port       = 465;                                     // TCP port to connect to 

						//Recipients
						$mail->setFrom('info.eauction@teqbro.com', 'e-Auction NIGERIA CUSTOMS SERVICE');
						$mail->addAddress($email);     // Add a recipient
						// Content
						$mail->isHTML(true);                                 // Set email format to HTML
                        $mail->Subject = $subject;
                        $mail->Body = '<div style="width:100%; background:#efefef; padding:20px 20px;">
                          <div style="font-family: Arial, sans-serif; background:#fff; font-size: 15px; width: 80%; margin: 5% 5%; border: 10px solid #ccc; box-shadow: 0 0 10px rgba(0,0,0,0.2); padding: 20px; line-height: 2.0; text-align: justify;">
						  		<h3 style="text-align:center"> NCS e-Auction </h3>
                                <p>Dear <b>'.$recipient.'</b>,</p>
                                <p style="width: 80%; word-wrap: break-word; text-align: justify;">'.$message.'</p>
                                <p>If you have any questions or need further assistance, please do not hesitate to contact the System Administrator.</p>
                                <p>Best regards,<br>ICT Technical Team</p>
                               </div> 
                            </div>
                        ';
                     
                        $mail->send();
                        //echo "<script>alert('Mail Sent');</script>";
                    } catch (Exception $e) {
                        echo "Message could not be sent. Mailer Error: {$mail->ErrorInfo}";
                    }
}

	//($date)
	//getMonthySumVehicle($date)

		//Get the total Sum of Value given for Non PAAR Application in a Command
	function getMonthySumRevenue($date){
			$results = DB::queryFirstRow("SELECT SUM(new_value) as vehicleSumCommand FROM vehicles WHERE status='Valued' AND application_type='Non Standard' AND date_valued LIKE '$date'" );

			if($results==true){
				$sum = $results['vehicleSumCommand'];
				return $sum;
			}else{
				 $sum = '0.00';
				return $sum;
			}
	}

	function getMonthySumVehicle($date){
			$results = DB::queryFirstRow("SELECT count(new_value) as vehicleSumCommand FROM vehicles WHERE status='Valued' AND application_type='Non Standard' AND date_valued LIKE '$date'" );

			if($results==true){
				$sum = $results['vehicleSumCommand'];
				return $sum;
			}else{
				 $sum = '0.00';
				return $sum;
			}
	}

	function getSumByStatus($status){
		$currentYear = date('Y')."%";
				$results = DB::queryFirstRow("SELECT count(application_id) as vehicleSumCommand FROM applications WHERE submission_status='$status' AND date_created LIKE '$currentYear'" );

				if($results==true){
					$sum = $results['vehicleSumCommand'];
					return $sum;
				}else{
					 $sum = '0.00';
					return $sum;
				}
		}
?>