File not working in Joomla 4 Topic is solved

[colinj]

Not a PHP programmer but wonder if you could help me update this php file (named process_csv.php) to Joomla 4, or at least determine what's not working. It takes the contents of a csv file (upload_csv.php), extracted from a competition list, and containing two columns with member_name and member_id. It uses the file to deduct a value from the column "current_credits" in a table (jos_credits), depending on the member type. It also writes the deductions to a log file. (The last part just does a screen dump.)

It works OK, except when the upload file contains a member_id that doesn't exist in the table (for example, a visitor with their own member_ID). If this happens it stops at the line and gives an integer error for the member_id column of the table, because the value doesn't exist.

It ignored a non-existent member_id in Joomla 3, but doesn't in Joomla 4.

I have amended to first few lines after advice here.

Code: Select all

<?php
use Joomla\CMS\Factory;
define('_JEXEC', 1);
error_reporting(E_ALL);
ini_set('display_errors', 1);
define('JPATH_BASE', realpath(dirname(__FILE__)));
require_once JPATH_BASE . '/includes/defines.php';
require_once JPATH_BASE . '/includes/framework.php';

$app = new Joomla\CMS\Application\SiteApplication();
\Joomla\CMS\Factory::$application = $app;
$db = Factory::getContainer()->get('DatabaseDriver');

/*
If the Member_ID is Member_Type "Adult", then deduct 1 credit
If the Member_ID is Member_Type "Junior", then deduct 0.5 credit
If the Member_ID is Member_Type "Access", then deduct 3 credits
If the Member_ID is Member_Type "Lifestyle", then deduct 1 credit (Oct-Mar) and 4 credits (Apr-Sep).
*/	

$m = date('m');
if(isset($_POST['submit'])) 
{	
	$arr_ext = explode(',', 'csv,CSV');
	$file_ext = strtolower(pathinfo($_FILES['csv_file']['name'],PATHINFO_EXTENSION));
	
	if(in_array($file_ext, $arr_ext)){
		
	$handle = fopen($_FILES['csv_file']['tmp_name'], "r");
	
	
	//$headers = fgetcsv($handle, 1000, ",");
	$html_table = '';
	while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
	{
		$member_id = $data[1]; $credit_deduct = 0;
		//echo $member_id . '<br/>';
		if($member_id != ''){
			$query = 'SELECT * FROM jos_credits WHERE Member_ID = ' . $db->quote($member_id)
					;
			//		echo '456';
			$db->setQuery($query);
			$member = $db->loadAssoc();
			$member_type = $member['Member_Type'];
			if($member_type == 'Adult'){
				$credit_deduct = 1;
			}elseif($member_type == 'Junior'){
				$credit_deduct = 0.5;
			}elseif($member_type == 'Access'){
				$credit_deduct = 3;
			}elseif($member_type == 'Lifestyle'){
				if($m > 3 && $m < 10){ 
					$credit_deduct = 3;
				}else{
					$credit_deduct = 1;
				}
			}
			
			// add to log table
			$query = 'INSERT INTO jos_credits_log_file (Member_ID, Member_Name, Member_Credits, Credits_Deducted, Created_Date) '
			        .'VALUES (' . $db->quote($member_id) . ', ' . $db->quote($member['Member_Name']) . ', ' . $db->quote($member['Member_Credits']) . ', ' . $db->quote($credit_deduct) . ', "' .  date('Y-m-d H:i:s') . '"'
			        . ')'
			        ;
			$db->setQuery($query);
			$db->execute();
			
			
			$query = 'UPDATE jos_credits SET Member_Credits = Member_Credits - ' . $credit_deduct
					.' WHERE Member_ID = ' . $db->quote($member_id)
					; 
			$db->setQuery($query);
			$db->execute();
			
			
			    
		//echo $member_id . '--' . $query . '<br/>';
			if(isset($member['Member_Name']) && $member['Member_Name'] != ''){
				$html_table .= '<tr class="table-success">';
				$html_table .= '<td>' . $member['Member_Name'] . '</td>';
				$html_table .= '<td>' . $member['Member_Type'] . '</td>';
				$html_table .= '<td>' . $credit_deduct . '</td>';
				$html_table .= '<td>' . ($member['Member_Credits'] - $credit_deduct) . '</td>';
				$html_table .= '</tr>';
			}
			
			$credits_after = $member['Member_Credits'] - $credit_deduct;
			if($credits_after <= 0){
				$mailer = Factory::getMailer();
				$config = Factory::getConfig();
				$sender = array( 
				  $config->get( 'mailfrom' ),
				  $config->get( 'fromname' ) 
				);
				$mailer = Factory::getMailer();
			    $mailer->setSender("[email protected]");		
				$mailer->addRecipient($member['Email']);
				$mailer->setSubject("Negative competition credit balance");
				$mailer->isHtml(true);
				$mailer->Encoding = 'base64';
				$body .= '<p>This is an automated email from the sitename.</p>';
				$body .= "<p>After today's competition, your credit balance is now 0 (zero) or below.</p>";
				$body .= '<p>Before you play again, you will need to purchase more credits by going to <a href="https://www.sitename.com.au/online-entry/buy-member-competition-credits.html" target="_blank">Buy competition credits</a></p>';
				$body .= '<p>If you have any problems, please email <a href="mailto:[email protected]">[email protected].</a></p>';
				$mailer->setBody($body);
				$send = $mailer->Send();
				if ( $send !== true ) {
					//echo 'Error sending email: ';
				} else {
				   // echo 'Mail sent';
				}
			}
		}
	}
	fclose($handle);
	
	}
}

?>

<!DOCTYPE html>
<html lang="en">
<head>
  <title>The file has been uploaded</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
  <style>
  .table-success, .table-success > td, .table-success > th{background-color:#DAF9DA;}
  
  </style>
</head>
<body>

<div class="container">  
  <table class="table">
    <thead>
      <tr class="table-success">
        <th>Name</th>
        <th>Member Level</th>
        <th>Credits Deducted</th>
		<th>Balance</th>
      </tr>
    </thead>
    <tbody>
       
      <?php echo $html_table;?>
      
    </tbody>
  </table>
</div>

</body>
</html>

[toivo]

when the upload file contains a member_id that doesn't exist in the table (for example, a visitor with their own member_ID). If this happens it stops at the line and gives an integer error for the member_id column of the table

Similar to the earlier topic, check the return status of the function loadAssoc() before accessing the data:

Code: Select all

	$member = $db->loadAssoc();
	if (is_null($member)) {
		echo "This member number doesn't exist, please check your number";
		exit;
	}

[colinj]

Thanks, but it doesn't have the required effect.

Here is a sample for the uploaded .csv file (the numbers in column 2 are national identity numbers, there could be 100+ lines in the file)

Column 1 Column 2
Fred Jones 3050600666 <- number exists in the table
Jack Smith 5023124567 <- number doesn't exist in the table
Peter Brown 3050600123 <- number exists in the table

The php file should process Fred Jones, ignore Jack Smith and then go on to process Peter Brown i.e take a credit off each person where their number exists in the table. This is what it was doing under Joomla 3.

With both the old code and your code, the process still stops at Jack Smith and throws up the error with the echo "number doesn't exist ..." Peter Brown isn't processed.

[toivo]

Yes, the loop should not stop after a member id missing from the database table. Change the exit command to:

Code: Select all

    continue;

[colinj]

Thank you, that worked a treat with "continue".

I left off the echo line as it's superfluous and just appears rather meaninglessly at the top of the screen. Although it would be good to echo all the member_id numbers that were in the csv file but omitted in the process.

This is a great forum.

[spictera]

Oh a curiosity
How did you do the file upload, as I couldnt do it the "Joomla" way, using thr xml file forms, but instead by using it directly in the controller the "php" way, with the $_ variables etc.

[colinj]

We use the free Custom Code module to include the php script on a page and this upload_csv.php file. The process_csv.php file then handles the upload and makes the changes to the table.

<?php //echo date('Y-m-d H:i:s');//2021-03-31 23:21:58
//echo date_default_timezone_get();
?>

<div class="container">
<h2>Select the CSV file to upload</h2>
<form action="/process_csv.php" method="POST" enctype="multipart/form-data" name="frm_csv" id="frm_csv">
<div class="form-group">
<input type="file" class="form-control-file border" name="csv_file" id="csv_file">
</div>
<button type="submit" class="btn btn-primary" id="submit" name="submit">Submit</button>
</form>
</div>

[spictera]

Thanks for sharing.

So you are not using the XML admin/forms either.

I have not get the joomla way of have the file form to work, hence the question why I ask what been used from your end.

Regards Tomas