How to automatically assign customers data to each team(department) on daily basis?

[hopegeek]

Hello team, i am here to request your assistance again about this i want to get done, in my script proposal, i am taking tasks and team for example to help you guys understand very well my problem and help to solve it.
I want a script that will assign customers to each team(department) on daily basis.
Every day, script must check uploaded customer data in the system and then randomly assign them to department by taking into account each department account_limit per day.
Sharing my script with explanation text, kindly go through and help to implement this please.
Thanks so much for your support.
<?php
/*
This code snippet is a PHP function that randomly assigns tasks to teams while respecting
a limit on the number of tasks each team can have per day. The function takes three parameters:
$teams, an array that represents the teams and their task limits; $tasks, an array that represents
the available tasks; and $tasksLimitPerDay, an integer that specifies the maximum number of tasks each
team can have per day.
The function first checks a special case: if there is only one task available, it assigns it to the first
team and returns the result.
Next, it shuffles the tasks array to randomize the assignment.
Then, it iterates over each team and checks if the team has reached its task limit for the day.
If not, it finds an unassigned task for the team and assigns it. The assigned tasks are stored in
the $assignments array, and the function keeps track of the assigned tasks in the $assignedTasks array
to ensure uniqueness.
Finally, the function returns the $assignments array, which contains the tasks assigned to each team.
*/
//Yaovi : Auto assigning data to Recharge team.
// Function to connect to the database
function connectToDatabase() {
$host = 'localhost';
$username = 'root';
$password = 'dfj24444';
$database = 'db';
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
return $conn;
}
// Function to get tasks available in the database for the current date
function getTasksFromDatabase($conn, $currentDate) {
$tasks = [];
$sql = "SELECT * FROM tasks WHERE date = '$currentDate'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$tasks[] = $row['task_name'];
}
}
return $tasks;
}
// Function to get teams from the database
function getTeamsFromDatabase($conn) {
$teams = [];
$sql = "SELECT team_name, tasks_limit_per_day FROM teams";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$teams[$row['team_name']] = $row['tasks_limit_per_day'];
}
}
return $teams;
}
// Function to randomly assign tasks to teams with a total tasks limit per day
function assignTasksWithLimitAndUnique($teams, $tasks, $tasksLimitPerDay) {
$assignments = [];
$numTeams = count($teams);
$numTasks = count($tasks);
// Special case: If only one task is available, assign it to the first team
if ($numTasks <= 100) {
$assignments[$teams[0]][] = $tasks[0];
return $assignments;
}
// Shuffle tasks to randomize assignment
shuffle($tasks);
// Assign tasks to teams with the limit and uniqueness
$assignedTasks = [];
for ($i = 0; $i < $numTeams; $i++) {
$team = array_keys($teams)[$i];
$teamLimit = $teams[$team];
// Check if the team has reached the tasks limit for the day
if (!isset($assignments[$team]) || count($assignments[$team]) < $teamLimit) {
// Find an unassigned task for the team
do {
$task = $tasks[$i % $numTasks]; // Loop through tasks
} while (in_array($task, $assignedTasks));
// Assign the task to the team
$assignments[$team][] = $task;
$assignedTasks[] = $task;
}
}
return $assignments;
}
// Get today's date
$currentDate = date('Y-m-d');
// Connect to the database
$conn = connectToDatabase();
// Get tasks from the database for the current date
$tasksFromDatabase = getTasksFromDatabase($conn, $currentDate);
// Get teams and their task limits from the database
$teamsWithLimits = getTeamsFromDatabase($conn);
// Close the database connection
$conn->close();
// Get assignments for today with the limit and uniqueness
$tasksLimitPerDay = 2;
$todayAssignments = assignTasksWithLimitAndUnique($teamsWithLimits, $tasksFromDatabase, 2);
// Display assignments
echo "Assignments for $currentDate:\n";
foreach ($todayAssignments as $team => $teamTasks) {
echo "$team: " . implode(', ', $teamTasks) . "\n";
}



Thanks for your help.

[rabii]

tbh honest you make it complex. First of all why this php script when all you need is to use espocrm Hooks to do the job. a beforeSave implementation would do the job easily. check this out https://docs.espocrm.com/development/hooks/

[hopegeek]

Thanks for your response, but don't really see how i can implement this since i am new to the CRM, would appreciate your help to get it done.
the PHP script was just to explain my problem to guys and see how best you can assist to solve it.
Kindly provide me more details on how to solve this.
Thank you

[rabii]

if you share more info i could help with some code, i need to know exactly the names of the two entities you listed above and what is the relationship between them and also relationship fields on both sides.

[hopegeek]

Hello, thanks so much for your response, this is what i want to get done please, Account entity data should be automatically assign to each team available on daily basis, each account uploaded everyday should be assign to each team, each team has limit of account to be assigned every day, so looking at it, this could be done using custom cronJob, sharing my script with you here so that you help with the integration.
Kindly read my code for better understanding of what i want to get done.
Thank you.

[hopegeek]

This is the script i am working on to see how to implement this with custom cronJob to get the job done on daily basis.
<?php
/*
This code snippet is a PHP function that randomly assigns accounts to teams while respecting
a limit on the number of accounts each team can have per day. The function takes three parameters:
$teams, an array that represents the teams and their task limits; $tasks, an array that represents
the available tasks; and $tasksLimitPerDay, an integer that specifies the maximum number of tasks each
team can have per day.
The function first checks a special case: if there is only one task available, it assigns it to the first
team and returns the result.
Next, it shuffles the tasks array to randomize the assignment.
Then, it iterates over each team and checks if the team has reached its task limit for the day.
If not, it finds an unassigned task for the team and assigns it. The assigned tasks are stored in
the $assignments array, and the function keeps track of the assigned tasks in the $assignedTasks array
to ensure uniqueness.
Finally, the function returns the $assignments array, which contains the tasks assigned to each team.
*/
//Yaovi : Auto assigning data to Recharge team.
// Function to connect to the database
// Function to connect to the database
function connectToDatabase() {
$host = 'localhost';
$username = 'root';
$password = 'ndnjfkfk939384';
$database = 'db';
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
return $conn;
}
// Function to get accounts available in the database for the current date
function getAccountsFromDatabase($conn, $currentDate) {
$accounts = [];
$sql = "SELECT * FROM accounts WHERE date = '$currentDate'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$accounts[] = $row['name'];
$accounts[] = $row['codeCountryCountry'];
$accounts[] = $row['phoneNumber'];
$accounts[] = $row['followUpStage'];
$accounts[] = $row['teams'];
}
}
return $accounts;
}
// Function to get teams from the database
function getTeamsFromDatabase($conn) {
$teams = [];
$sql = "SELECT name, totalData FROM team";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$teams[$row['name']] = $row['totalData'];
}
}
return $teams;
}
// Function to reset the assignments and account limits for each team
function resetAssignmentsAndLimits($teams) {
$data = [];
foreach ($teams as $team => $tasksLimit) {
$data[$team] = [
'assignments' => [],
'tasksLimit' => $tasksLimit,
];
}
return $data;
}
// Function to randomly assign accounts to teams with a total account limit per day
function assignTasksWithLimitAndUnique($teams, $accounts, $accountsLimitPerDay) {
$data = resetAssignmentsAndLimits($teams);
$numTeams = count($teams);
$numAccounts = count($accounts);
// Special case: If only accounts <= 50 is available, assign it to the first team
if ($numAccounts <= 50) {
$data[array_keys($teams)[0]]['assignments'][] = $accounts[0];
return $data;
}
// Exclude these teams from task assignment
$excludedTeams = ['Admin', 'Searching_number'];
// Shuffle accounts to randomize assignment
shuffle($accounts);
// Assign accounts to teams with the limit and uniqueness
$assignedAccounts = [];
for ($i = 0; $i < $numTeams; $i++) {
$team = array_keys($teams)[$i];
// Check if the team is not in the excluded list
if (!in_array($team, $excludedTeams)) {
// Check if the team has reached the tasks limit for the day
if (count($data[$team]['assignments']) < $data[$team]['tasksLimit']) {
// Find an unassigned task for the team
do {
$account = $accounts[$i % $numAccounts]; // Loop through accounts
} while (in_array($account, $assignedAccounts));
// Assign the task to the team
$data[$team]['assignments'][] = $account;
$assignedAccounts[] = $account;
}
}
}
return $data;
}
// Get today's date
$currentDate = date('Y-m-d');
// Connect to the database
$conn = connectToDatabase();
// Get accounts from the database for the current date
$accountsFromDatabase = getAccountsFromDatabase($conn, $currentDate);
// Get teams and their task limits from the database
$teamsWithLimits = getTeamsFromDatabase($conn);
// Close the database connection
$conn->close();
// Get assignments for today with the limit and uniqueness
$accountsLimitPerDay = 150;
$todayAssignments = assignTasksWithLimitAndUnique($teamsWithLimits, $accountsFromDatabase, $accountsLimitPerDay);
// Display assignments
echo "Assignments for $currentDate:\n";
foreach ($todayAssignments as $team => $teamData) {
echo "$team: " . implode(', ', $teamData['assignments']) . "\n";
}

[item]

https://docs.espocrm.com/development/orm/

your queries is bad formated for forum, but easy ..
see ORM on doc

[hopegeek]

Can you just help me solve my problem??

[hopegeek]

Good morning team
So there's no any senior member here to help me solve my problem?
I though the community is here to support and help...
hummmm
My problem can be solved using cronJob, but how to go about it using my code?
My code is just to explain what i really to solve, just to understand my query and help me out.

[hopegeek]

Hello team,
This is final code, cronJob running with success but assigning accounts to team not working, can someone here look at it please to help me solve it?
Thanks in advance.

<?php
namespace Espo\Custom\Jobs;
use Espo\Core\Jobs\Job;
use Espo\Core\ORM\EntityManager;
use Espo\Core\Utils\Log;
class PushData implements Job
{
protected $entityManager;
protected Log $log;
public function __construct(EntityManager $entityManager, Log $log)
{
$this->entityManager = $entityManager;
// The logger service is automatically injected by EspoCRM
$this->log = $log;
}
/**
* Runs the custom assign data Scheduled Job.
*
* @throws \Exception if an error occurs during the job execution.
* @return void
*/
public function run(): void
{
$this->log->info('Custom assign data Scheduled Job started.');
try {
$currentDate = date('Y-m-d');
$accountsFromDatabase = $this->getAccountsFromDatabase($currentDate);
$teamsWithLimits = $this->getTeamsFromDatabase();
$todayAssignments = $this->assignTasksWithLimitAndUnique($teamsWithLimits, $accountsFromDatabase);
// Log assignments for each team
foreach ($todayAssignments as $team => $teamData) {
// Ensure $teamData['assignments'] is an array before attempting to implode
if (is_array($teamData['assignments'])) {
// Convert array values to strings before logging
$assignmentsAsString = implode(', ', array_map('strval', $teamData['assignments']));
$this->log->info("$team: " . $assignmentsAsString);
} else {
$this->log->info("$team: No assignments for today.");
}
}
$this->log->info('Custom Scheduled Job completed successfully.');
} catch (\Exception $e) {
$this->log->error('Error in Custom Scheduled Job: ' . $e->getMessage());
}
$this->log->info(['success' => true]);
}
private function getAccountsFromDatabase($currentDate)
{
$accounts = [];
$accountRepository = $this->entityManager->getRepository('Account');
$accountsData = $accountRepository->find(['createdAt' => $currentDate]);
foreach ($accountsData as $account) {
$accounts[] = [
'name' => $account->get('name'),
'codeCountryCountry' => $account->get('codeCountryCountry'),
'phoneNumber' => $account->get('phoneNumber'),
'followUpStage' => $account->get('followUpStage'),
'teams' => $account->get('teams'),
];
}
return $accounts;
}
private function getTeamsFromDatabase()
{
$teams = [];
$teamRepository = $this->entityManager->getRepository('Team');
$teamsData = $teamRepository->find();
foreach ($teamsData as $team) {
// Fetch 'accountsLimitPerDay' from the Team entity
$teams[] = [
'name' => $team->get('name'),
'totalData' => $team->get('totalData'),
];
}
return $teams;
}
//Function to reset assignement Limit on daily basis
private function resetAssignmentsAndLimits($teams)
{
$data = [];
foreach ($teams as $team => $teamData) {
$data[$team] = [
'assignments' => [],
'tasksLimit' => $teamData['totalData'],
];
}
return $data;
}
private function assignTasksWithLimitAndUnique($teams, $accounts)
{
$data = $this->resetAssignmentsAndLimits($teams);
$numTeams = count($teams);
$numAccounts = count($accounts);
// Special case: If only accounts <= 50 are available, assign them to the first team
if ($numAccounts <= 50) {
$data[array_keys($teams)[0]]['assignments'] = $accounts;
return $data;
}
// Exclude these teams from task assignment
$excludedTeams = ['Admin', 'Searching_number'];
// Remove accounts assigned to 'Searching_number' and shuffle remaining accounts
$remainingAccounts = [];
foreach ($accounts as $account) {
if (!in_array('Searching_number', $account['teams'])) {
$remainingAccounts[] = $account;
}
}
shuffle($remainingAccounts);
// Assign accounts to teams with the limit and uniqueness
$assignedAccounts = [];
for ($i = 0; $i < $numTeams; $i++) {
$team = array_keys($teams)[$i];
// Check if the team is not in the excluded list
if (!in_array($team, $excludedTeams)) {
// Check if the team has reached the tasks limit for the day
if (count($data[$team]['assignments']) < $data[$team]['tasksLimit']) {
// Find an unassigned task for the team
do {
$account = $remainingAccounts[$i % count($remainingAccounts)]; // Loop through remaining accounts
} while (in_array($account, $assignedAccounts));
// Assign the task to the team
$data[$team]['assignments'][] = $account;
$assignedAccounts[] = $account;
}
}
}
return $data;
}
}