-1

I have a code which should assign customer to liveagent in round robin manner .

For example i have two table

table -> ChatLiveAgents

Name   MobileNumber  
 A      123456
 B      654324
 C      099887

and CustomerName is randomly passed using callback URL (working fine)

So what i need is when customer 1 texts it should be assigned to LiveAgent A from ChatLiveAgents table and when customer 2 approached it should be given to LiveAgent B , and Customer 3 to LiveAgent3 and then Customer 4 should be assigned to LiveAgent A.

so i tried but am getting the same value again and again.and it's not passing in round robin style.

below is my code.

$AssignLiveAgent = ""SELECT  `Name`, `MobileNumber` , `email` FROM ChatLiveAgents WHERE Name NOT IN (SELECT `LiveAgentName` FROM `AssignAgentstoCustomer`) ORDER BY RAND() LIMIT 1";;
 $resultofAssignLiveAgent = $conn->query($AssignLiveAgent);
 if ($resultofAssignLiveAgent->num_rows > 0) {
    while($row = $resultofAssignLiveAgent->fetch_assoc()) {
        $nameofliveagent = $row['Name'];
    $sql = "INSERT INTO `AssignAgentstoCustomer`(`LiveAgentName`, `AssignedMobileNumber`, `Date`) VALUES ('".$row['Name']."','".$mobile."','".$currentDayTImeZone."')";                    
        if ($conn->query($sql) === TRUE){$logger->info("Inserted Value Succesfully");}  
     }
 }

Where

$sql = "INSERT INTO `AssignAgentstoCustomer`(`LiveAgentName`, `AssignedMobileNumber`, `Date`) VALUES ('".$row['Name']."','".$mobile."','".$currentDayTImeZone."')";

$row['Name'] is from ChatLiveAgents ,$mobile is from callbackURL , $currentDayTImeZone from PHP Code

Result of query when all three agents are engaged with sigle customer

so as in screenshot my query is not getting looped so that if a fourth customer customs it should allocate the first Liveagent but here it returns null.

Can anyone please help me with this.

Stack Overflow
  • 1
  • 1
  • You need to query `ChatLiveAgents` but exclude `AssignAgentstoCustomer`, which can be done with [`NOT IN`](https://stackoverflow.com/a/1519296/231316) – Chris Haas May 25 '22 at 11:58
  • i tried that . I will show you SELECT `Name`, `MobileNumber` , `email` FROM ChatLiveAgents WHERE Name NOT IN (SELECT `LiveAgentName` FROM `AssignAgentstoCustomer` WHERE AssignedMobileNumber = ".$mobile.") ORDER BY RAND() LIMIT 1 @ChrisHaas – Stack Overflow May 25 '22 at 12:02
  • You inner `WHERE` shouldn’t be there. You aren’t “looking for agents in use that are also talking to this person”, you just want to query for “agents in use”. – Chris Haas May 25 '22 at 12:14
  • I tried testing it after removing inner Where , but still it was not going in round robin style @ChrisHaas – Stack Overflow May 25 '22 at 12:16
  • Here’s an [example](https://www.db-fiddle.com/f/oJiiqMsECswgq3rpmkkz3K/0). I’ve got five agents, 1 through 5, and agents 2 and 4 are in use. My SELECT statements will only ever pull agents 1, 3 and 5. – Chris Haas May 25 '22 at 12:20
  • so it assigns to all 3 LiveAgents and for the next one it is not assigning to the first live agent like it is not following round @ChrisHaas – Stack Overflow May 25 '22 at 13:17
  • "SELECT `Name`, `MobileNumber` , `email` FROM ChatLiveAgents WHERE Name NOT IN (SELECT `LiveAgentName` FROM `AssignAgentstoCustomer`) ORDER BY RAND() LIMIT 1"; @ChrisHaas – Stack Overflow May 25 '22 at 13:18
  • Kindly check the screenshot attached @ChrisHaas – Stack Overflow May 25 '22 at 13:28
  • [Here's a version](https://www.db-fiddle.com/f/5YxDcKfFQQVRyYEUTbfGRK/0) that more closely matches your database. It does work. It is absolutely possible for you to have all agents busy. Also, it is possible for a race condition to occur where two people check for an available agent and get the same one, but that's a problem for later. Your code should not be looping at all, there's just one row that either exists or it doesn't. I'd confirm your data, and I'd also encourage you to use IDs instead of names, to avoid collisions. – Chris Haas May 25 '22 at 13:33
  • https://www.db-fiddle.com/f/5YxDcKfFQQVRyYEUTbfGRK/1 , Please take a look @ChrisHaas – Stack Overflow May 25 '22 at 13:41
  • Yep, looks correct, `Alice` comes back each time which is logically correct. In your code you obviously wouldn't run four SELECT queries, just one for the request, and you'd INSERT into `AssignAgentstoCustomer` directly after which would mark them as taken. – Chris Haas May 25 '22 at 13:52
  • So each time a New Agent is assigned to a customer the value goes to AssignedAgentToCustomer Table , So when all the three Agents get assigned it will be able to pick other agents as it is NOT IN in quer @ChrisHaas – Stack Overflow May 27 '22 at 05:29
  • You remove agents from `AssignedAgentToCustomer` once they are done, right? – Chris Haas May 27 '22 at 12:24

0 Answers0