Code Logic - Importer

[DanRz]

Hey!

I am writing a script however, I am a little stuck with the logic for import... here is the background...

I have a database of users, some are active some are inactive.

I have a new array to import, however, in this array people could be one of the following:

New User - Add to database
New User with Existing Account - Rejoin them
Left User - User ID not found in the new array so assume they have left...

I need to process the data and output the following;

Number of New Users
Number of Users Rejoined
Number of Users Left
List of New User IDs
List of Left User IDs
List of Rejoined User IDs

I have written a script, however it doesn't work well and gives me random information that doesn't match so the logic is off somewhere.. can someone help with the logic

Currently it does the following:

1. Counts the current users who are marked as inactive
2. Makes an array of those users
3. Sets all database users to inactive
4. Loops though each new user from import array
5. If user ID exists... update the user to active again
6. If user ID doesn't exist... add them to the database and add new user ID to new array
7. Counts all users who are marked as inactive after import
8. Use array_diff_key to check the difference between inactive array 1 (before import) and array 2 (after import)
9. Counts number of array items for joiners, and leavers...

I hope this makes at least some sense... any help would be appreciated.

Thanks
Dan

[gizmola]

I'm not sure what you want from us, given an outline of your requirements and a basic list of steps.   You know what you want to do, and the code doesn't work right or count right, so that means you have bugs.  We can't debug your code without seeing it.

Here are a few thoughts based on list of steps:

Quote

3. Sets all database users to inactive 

This is a terrible idea.  There is no reason to update rows of the entire table to inactive, only at some later point to set them back to active.  Don't change rows you don't need to change.

Quote

New User - Add to database
New User with Existing Account - Rejoin them
Left User - User ID not found in the new array so assume they have left...

This is really unclear.  What is the format of the "import" array?  Are there ID's?  I don't see how you could classify anyone as a "Left user".

If they were not found, then they should be added as a new user.   Thus all users are either found (existing) or not found (new).  

It seems like there are a number of baked in assumptions here that aren't clear, starting with where this import file comes from.  What constitutes "active" state in this system?  What exactly is the format of the import file?

[DanRz]

Hey,

Sorry yes I thought about this after I posted... so the file is an excel file and the array returned from 

$this->memberImporterDataSorter($rows)

is as follows... the uniqueness of this is the memberNumber which is how you know who is who...

Array
			(
			    [0] => Array
			        (
			            [memberNotes] => 
			            [memberNumber] => 123456L
			            [memberName] => Jim Jones
			            [memberAddress] => 
			            [memberEmail] => 
			            [memberCommsEmail] => 1
			            [memberLocation] => 1
			            [memberArea] => 4
			            [memberLocationTxt] => 
			            [memberAreaTxt] => 
			            [memberJob] => 3
			            [memberJobTxt] => 
			            [memberCommsSMS] => 1
			            [memberTel] => 
			            [memberJoined] => 1644192000
			            [lastUpdateUnion] => 1666998000
			        )

			    [1] => Array
			        (
			            [memberNotes] => 
			            [memberNumber] => 112233L
			            [memberName] => Jennifer Zoom
			            [memberAddress] => 
			            [memberEmail] => 
			            [memberCommsEmail] => 1
			            [memberLocation] => 1
			            [memberArea] => 4
			            [memberLocationTxt] => 
			            [memberAreaTxt] => 
			            [memberJob] => 99
			            [memberJobTxt] => 
			            [memberCommsSMS] => 1
			            [memberTel] => 
			            [memberJoined] => 1599692400
			            [lastUpdateUnion] => 1665442800
			        )
						)

Here is the actual importer code;

set_time_limit(3600);
		try {
			if(!file_exists($filePath)){
				throw new \Exception("Hmm... Import file can't be found. Please try again.");
			}

			// Read the Excel and pop into an array
			$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
			$worksheet = $spreadsheet->getActiveSheet();

			$rows = [];
			foreach ($worksheet->getRowIterator() AS $row) {
					$cellIterator = $row->getCellIterator();
					$cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
					$cells = [];
					foreach ($cellIterator as $cell) {
							$cells[] = $cell->getFormattedValue();
					}
					$rows[] = $cells;
			}

			// Pop the first array item off, not needed
			array_shift($rows);

			// Sort the data
			$rows = $this->memberImporterDataSorter($rows);

			// Set blank core array
			$coreImportArray = array(
				"addedMembers" => array(),
				"leftMembersAfter" => array(),
				"rejoinedMembers"	=>	array()
			);

			// Get Total Cancelled Before Import
			$returnData = $this->database->getAll("SELECT * FROM `members` WHERE `memberStatus` = '2';", array());
			$coreImportArray['totalCancelledBefore'] = count($returnData);

			// Decode
			$returnData = $this->encryption->decode_data($returnData, array("memberName","memberAddress","memberEmail","memberTel","memberNotes"));

			// Add left members to array
			foreach($returnData as $left){
				$coreImportArray['leftMembersBefore'][$left['memberNumber']] = array("memberNumber" => $left['memberNumber'],"memberName" => $left['memberName'],"memberArea" => $left['memberArea']);
			}

			// First Set all users to canclled (status 2)
			$this->database->execute("UPDATE `members` SET `memberStatus` = '2', `memberLeft` = ? WHERE `memberStatus` = '1';", array(time()));

			// Run each line of data and process the import
			foreach($rows as $memberData){
				// If member number empty continue...
				if($memberData['memberNumber'] == NULL){
					continue;
				}

				// Check if member exists in DB
				$checkExists = $this->database->countRows("SELECT * FROM `members` WHERE `memberNumber` = ?;", array($memberData['memberNumber']));

				if($checkExists == '1'){
					// They exist, update data and status 1
					$this->database->execute("UPDATE `members` SET `memberStatus` = '1', `memberLeft` = NULL, `memberLeftReason` = NULL WHERE `memberNumber` = ?;", array($memberData['memberNumber']));
				}else{
					// Encode Data
					$memberDataEnc = $this->encryption->encode_data_single($memberData, array("memberName","memberAddress","memberEmail","memberTel","memberNotes"));

					// New Member! Insert them...
					$this->database->execute("INSERT INTO `members` (`memberNumber`, `memberName`) VALUES (?,?);", array($memberDataEnc['memberNumber'], $memberDataEnc['memberName']));
					$lastID = $this->database->getLastId();

					// Add new member to new members array :)
					$coreImportArray['addedMembers'][$memberData['memberName']] = array(
								"id" => $lastID,
								"memberName" => $memberData['memberName'],
								"memberNumber" => $memberData['memberNumber'],
								"memberArea" => $memberData['memberArea']
							);
				}
			}

			// Run analysis
			// Total Cancelled After Import
			$returnData = $this->database->getAll("SELECT * FROM `members` WHERE `memberStatus` = '2';", array());
			$coreImportArray['totalCancelledAfter'] = count($returnData);
			$returnData = $this->encryption->decode_data($returnData, array("memberName","memberAddress","memberEmail","memberTel","memberNotes"));
			foreach($returnData as $left){
				if($coreImportArray['leftMembersBefore'][$left['memberName']]['memberNumber'] != $left['memberNumber']){
					$coreImportArray['leftMembersAfter'][$left['memberName']] = array("memberNumber" => $left['memberNumber'],"memberName" => $left['memberName'],"memberArea" => $left['memberArea']);
				}
			}
			unset($coreImportArray['leftMembersBefore']);

			// Update Stats
			$coreImportArray['totalNew'] = count($coreImportArray['addedMembers']);
			$coreImportArray['totalLeft'] = $coreImportArray['totalCancelledAfter'] - $coreImportArray['totalCancelledBefore'];
			if($coreImportArray['totalNew'] < 0){ $coreImportArray['totalNew'] = 0; }
			if($coreImportArray['totalLeft'] < 0){ $coreImportArray['totalLeft'] = 0; }
			if($coreImportArray['totalCancelledAfter'] < $coreImportArray['totalCancelledBefore']){
				$coreImportArray['totalRejoined'] = $coreImportArray['totalCancelledBefore'] - $coreImportArray['totalCancelledAfter'];
			}else{
				$coreImportArray['totalRejoined'] = 0;
			}

			// Count Arrays
			$coreImportArray['joinersCount'] = count($coreImportArray['newMembers']);
			$coreImportArray['leaversCount'] = count($coreImportArray['newLeavers']);
			$coreImportArray['rejoinCount'] = count($coreImportArray['newRejoin']);

			return $coreImportArray;

		} catch (\Exception $e){
			// Display error...
			$this->helper->set_message($this->helper->validation_errors($e->getMessage()));
		}

There is probably a much easier way to achieve what I am after so any help would be appreciated.

Basically the data is from an external source, so I need to loop through the new data from excel. 

If they are NOT already in the database add them, if they are in the database but cancelled, reactive them and anyone who's in the database but NOT on the excel sheet will be cancelled.

Thanks

[gizmola]

There are functions and an entire database class you didn't include that is mysterious.   Here's a simplified functional spec

Initialize your variables, get initial statistics

• Get the largest timestamp value in the database.   At the end you can use this to update any rows that need to be cancelled because there was no importer entry
  • This assumes that every row has some sort of timestamp row.  It appears that way, but you didn't provide the format.

• Import the file
  • Check that there are entries. 
    • You don't want to cancel the entire userbase because the import was flawed
• Get pre-import statistics for active/cancelled
  • One group by query for this will give you both numbers. 
    • SELECT COUNT(*) as count_of, memberStatus FROM Members GROUP BY memberStatus
• No need to pre-sort import data
  • foreach through the import data
    • SELECT id, status from Member where id = import id
      • If row is found
        • Based on ID, update status, timestamp
          • If it's an actual datetime/timestamp you can do this with column = NOW()
          • This guarantees that a found ID column gets its timestamp updated regardless of the status
        • Based on status add to appropriate bucket
          • rejoined
          • was still status 1
      • if row not found
        • Add new member
          • Update to new member count

 

When complete, SELECT COUNT(*) FROM Members WHERE "timestamp/updated" column is <= original timestamp you queried during initialization.  These are the rows you now need to cancel, so store in your summary array as needed

UPDATE Members SET status = 2 WHERE WHERE "timestamp/updated" column is <= original timestamp you queried during initialization

At this point you should be able to make your final reporting.  Count the arrays for new/ongoing/rejoined.  Use the cancelled count# you got from the summary.  

You probably want to get an initial count of all existing rows via SELECT COUNT(*) but if all rows have a status of 1 or 2, then you can just use the counts from the GROUP BY query summed together.