Amol Kumbhar
Reputation: 329
Follow up cases must be assigned to active user in sugarcrm
all, I am newly working on SugarCRM. We have updated handleCreateCase function on InboundMail Module. In this module, we have to assign any case to the active user. Supposed any user is handling the case but now he leaves the company so we deactivated his account but now customer will replay the cases then this case assigned to that user so we check before assigning the case this the user is active or not
So I have written the code for these,
**// Write The Code for Active User -**
$qry_user = "SELECT status FROM users WHERE id='" . $assign_id . "' LIMIT 1";
$res_user = $this -> db -> query($qry_user, true);
$resultUser = $this -> db -> fetchByAssoc($res_user);
$status = $resultUser['status'];
if($status != 'Active'){
$GLOBALS['log'] -> fatal('Not Active User'. $assign_id);
$qry_db = "SELECT id FROM users WHERE is_group=0 AND deleted = 0 AND status = 'active' ORDER BY RAND() LIMIT 1";
$res_db = $this -> db ->query($qry_db, true);
$fetch_db = $this -> db -> fetchByAssoc($res_db);
if (is_array($fetch_db) && count($fetch_db) > 0) {
$assign_id = $fetch_db['id'];
} else {
$GLOBALS['log'] -> fatal('User Not Present in Users table');
}
}
else{
$assign_id = $assign_id;
}
Before assign the case.
I have written the code for follow up cases must be assigned to an active user but it's not working please help me on these.... Code:
function handleCreateCase($email, $userId) {
global $current_user, $mod_strings, $current_language;
$mod_strings = return_module_language($current_language, "Emails");
$GLOBALS['log']->debug('In handleCreateCase');
$c = new aCase();
$this->getCaseIdFromCaseNumber($email->name, $c);
if (!$this->handleCaseAssignment($email) && $this->isMailBoxTypeCreateCase()) {
// create a case
$GLOBALS['log']->debug('retrieveing email');
$email->retrieve($email->id);
$c = new aCase();
/*Amol If Have open case from this email*/
$new_case = 1;
//create new case?
$skip_checking = 0;
// skip mailbox for attached into existing case?
$email_from = $email -> from_addr;
$sql = "SELECT c.id FROM cases c INNER JOIN emails e on e.parent_id=c.id INNER JOIN emails_text et on et.email_id=e.id WHERE ( c.`status`='New' OR c.`status`='FollowUp' OR c.`status`='PartsReqCompleted' ) AND et.from_addr LIKE '%" . $email_from . "%' ORDER BY c.date_entered DESC LIMIT 1"; //Amol
$results = $this -> db -> query($sql, true);
$row_c = $this -> db -> fetchByAssoc($results);
$skip_emailboxes = $this -> get_stored_options('create_case_skip_emails', "");
// skip mailboxes
if (!empty($skip_emailboxes)) {
$case_skip_followup_mailboxes_from = explode(',', $skip_emailboxes);
if (in_array($email_from, $case_skip_followup_mailboxes_from)) {
$skip_checking = 1;
}
}
if (strpos((trim(strtolower($email->to_addrs))), "[email protected]") !== false) {
$fixedstring = "CASE";
if(count($row_c) <= 0){
if (strpos($email->name, $fixedstring) !== false) {
list($first_str, $second_str) = explode('[CASE:', $subject);
list($first_string, $second_string) = explode(']', $second_str);
$caseId = $first_string;
$new_case=0; // Already exist case
}else{
$new_case=1; // New Case
}
}
else{
$new_case=1;
}else{
preg_match("/Name:(.*)/", $email -> description, $data);
$mailer_name = trim($data[1]);
$email_sender= $email->reply_to_email;
$sql_acc = "SELECT eabr.bean_id, eabr.bean_module FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address = '$email_sender' AND eabr.bean_module='Accounts'";
$res = $this->db->query($sql_acc);
$id;
while ($row = $GLOBALS['db']->fetchByAssoc($res)) {
$id = $row['bean_id'];
}
if(is_null($id)){
$mailer_name = mysql_escape_string($mailer_name);
$acc_id=create_guid();
$id = $acc_id;
$insert_acc="INSERT INTO `accounts`(`id`, `name`) VALUES ('$acc_id','$mailer_name')";
$this->db->query($insert_acc);
$email_add_id = create_guid();
$email_sender_upper = strtoupper($email_sender);
$insert_emai_add="INSERT INTO `email_addresses`(`id`, `email_address`, `email_address_caps`) VALUES ('$email_add_id','$email_sender','$email_sender_upper')";
$this->db->query($insert_emai_add);
$relation_id = create_guid();
$insert_rel="INSERT INTO `email_addr_bean_rel`(`id`, `email_address_id`, `bean_id`, `bean_module`) VALUES ('$relation_id','$email_add_id','$acc_id','Accounts')";
$this->db->query($insert_rel);
}
$c -> account_id = $id;
}
$c -> description = $email -> description;
/* Asssign Case*/
if ($new_case) {
$createCaseAssignId = $this -> get_stored_options('create_case_user_id', "");
} else {
$createCaseAssignId = '';
}
if (!empty($createCaseAssignId)) {
$case_assign_uids = explode(',', $createCaseAssignId);
//check for last assigned
$query = "SELECT * FROM case_email_quene WHERE mailbox_id = '" . $this -> id . "' LIMIT 1 ";
$results = $this -> db -> query($query, true);
$row = $this -> db -> fetchByAssoc($results);
if (is_array($row) && count($row) > 0) {
if ($row['last_uid'] == end($case_assign_uids)) {
$query = "UPDATE case_email_quene SET last_uid='" . $case_assign_uids[0] . "' WHERE mailbox_id = '" . $this -> id . "' ";
$results = $this -> db -> query($query, true);
$assign_id = $case_assign_uids[0];
} else {// if not end of array
$keys = array_keys($case_assign_uids, $row['last_uid']);
if (is_array($keys) && count($keys) > 0) {//if we have this user selected
$new_key = $keys[0] + 1;
$query = "UPDATE case_email_quene SET last_uid='" . $case_assign_uids[$new_key] . "' WHERE mailbox_id = '" . $this -> id . "' ";
$results = $this -> db -> query($query, true);
$assign_id = $case_assign_uids[$new_key];
} else {// if record was updated
$query = "UPDATE case_email_quene SET last_uid='" . $case_assign_uids[0] . "' WHERE mailbox_id = '" . $this -> id . "' ";
$results = $this -> db -> query($query, true);
$assign_id = $case_assign_uids[0];
}
}
} else {//but if not registered?
$query = "INSERT INTO case_email_quene (mailbox_id,last_uid) VALUES ('" . $this -> id . "','" . $case_assign_uids[0] . "') ";
$results = $this -> db -> query($query, true);
$assign_id = $case_assign_uids[0];
}
//$this->id mailbox id
$GLOBALS['log'] -> fatal('New Case - User Id: ' . $assign_id);
**// Write The Code for Active User -**
$qry_user = "SELECT status FROM users WHERE id='" . $assign_id . "' LIMIT 1";
$res_user = $this -> db -> query($qry_user, true);
$resultUser = $this -> db -> fetchByAssoc($res_user);
$status = $resultUser['status'];
if($status != 'Active'){
$GLOBALS['log'] -> fatal('Not Active User'. $assign_id);
$qry_db = "SELECT id FROM users WHERE is_group=0 AND deleted = 0 AND status = 'active' ORDER BY RAND() LIMIT 1";
$res_db = $this -> db ->query($qry_db, true);
$fetch_db = $this -> db -> fetchByAssoc($res_db);
if (is_array($fetch_db) && count($fetch_db) > 0) {
$assign_id = $fetch_db['id'];
} else {
$GLOBALS['log'] -> fatal('User Not Present in Users table');
}
}
else{
$assign_id = $assign_id;
}
$c -> assigned_user_id = $assign_id;
} elseif ($new_case) {
/*check if database not empty then empty it*/
$query = "SELECT * FROM case_email_quene WHERE mailbox_id = '" . $this -> id . "' LIMIT 1 ";
$results = $this -> db -> query($query, true);
$row_id = $this -> db -> fetchByAssoc($results);
if (is_array($row_id) && count($row_id) > 0) {
$query = "DELETE FROM case_email_quene WHERE mailbox_id = '" . $this -> id . "' ";
$results = $this -> db -> query($query, true);
}
$GLOBALS['log'] -> fatal('Existing Case - User Id: ' . $userId);
$c -> assigned_user_id = $userId;
}
Upvotes: 1
Views: 142
Answers (1)
Amol Kumbhar
Reputation: 329
I resolved this issue,
Please Put the code on handleCaseAssignment()
function handleCaseAssignment($email) {
$c = new aCase();
if ($caseId = $this -> getCaseIdFromCaseNumber($email -> name, $c)) {
$c -> retrieve($caseId);
if (($c -> status == "Closed") || ($c -> status == "Rejected")) {//Amol
$c -> status = "ReOpened";
} else {
$c -> status = "FollowUp";
}
$c -> save();
$email -> retrieve($email -> id);
//assign the case info to parent id and parent type so that the case can be linked to the email on Email Save
$email -> parent_type = "Cases";
$email -> parent_id = $caseId;
// assign the email to the case owner
$GLOBALS['log'] -> fatal('Case - cassigned_user_id11: ' . $c->assigned_user_id);
$GLOBALS['log'] -> fatal('Case - caseId Id11: ' . $caseId);
$GLOBALS['log'] -> fatal('emailid11: ' . $email->id);
$qry_user = "SELECT status FROM users WHERE id='" . $c->assigned_user_id . "' LIMIT 1";
$res_user = $this -> db -> query($qry_user, true);
$resultUser = $this -> db -> fetchByAssoc($res_user);
$status = $resultUser['status'];
if($status != 'Active'){
$qry_db = "SELECT id FROM users WHERE is_group=0 AND deleted = 0 AND status = 'active' AND department='CS' ORDER BY RAND() LIMIT 1";
$res_db = $this -> db ->query($qry_db, true);
$fetch_db = $this -> db -> fetchByAssoc($res_db);
if (is_array($fetch_db) && count($fetch_db) > 0) {
$email->assigned_user_id = $fetch_db['id'];
$c -> assigned_user_id = $email->assigned_user_id;
$c -> save();
} else {
$GLOBALS['log'] -> fatal('User Not Present in Users table');
}
}
else{
$email->assigned_user_id = $c->assigned_user_id;
//$email->assigned_user_id = $c->assigned_user_id;
$email->save();
$GLOBALS['log']->debug('InboundEmail found exactly 1 match for a case: '.$c->name);
return true;
} // if
return false;
} // fn
Upvotes: 1
Related Questions
- sugarcrm users insert in magento
- How to add auto increment in field of SuiteCRM
- assigned_user_id doesn't change
- How to find record id in aftersave hooks logic-SuiteCRM
- Assign multiple roles to a user programmatically in SugarCRM
- SugarCRM Relate a Case to a Contact record through API?
- Lead Save Database Error In SuiteCRM
- CRUD operation with Sugar CRM
- Assign one project to multiple users in SugarCRM
- Sugar CRM SOAP API - Problems with Auto Incrementing Key and set_entry