AnsweredAssumed Answered

How to query case_number and a custom field to create an after save logic hook

Question asked by Roland Cadavos on Feb 13, 2016
Latest reply on Feb 18, 2016 by Roland Cadavos

Hi everyone,

 

Just wondering how to query max(case_number) with related field custom_id_c upon Case creation in the cases_cstm. I have a sample php of which is not working.

Please help me correct it, thanks. i want the output in custum_id_c to be AAAAYYMM000001

 

<?php

class CustomNumber {

  function newCustomNumber($bean, $event, $arguments){

  //Query CASES table for the last Case Record  

  $case_id = $bean->id; 

  $query = 'SELECT MAX(cases_cstm.custom_id_c) AS idnum FROM (SELECT MAX(cases.case_number) AS casenum FROM cases LIMIT 1) AS t FROM cases_cstm LIMIT 1 ';    //i am querying max(case_number)  and related field upon case creation max(custom_id_c)

  $results = $bean->db->query($query, true); 

  $row = $bean->db->fetchByAssoc($results);  

  $last_custom_id = $row['idnum'] * 1;

  //Determine the year  

  $last_case_year = substr($last_custom_id, 0, 2);     //get year last 2 digit

  $current_year = date('Y');

  $last_case_month = substr($last_custom_id, 0, 2);  //get month 2 digit

  $current_month = date('M');      

  //Calculate the case number  

  $last_custom_id_series = substr($last_custom_id, 8, 6);    // get last 6 digit of custom_id

  if($last_case_year == $current_year) && ($last_case_month == $current_month){   

  $new_custom_id = $last_custom_id_series + 1;  

  }

  else{   

  $new_custom_id = 'AAAA'.substr($current_year,3,2).$current_month . '000001';  

  }        //Set the value  

  if (empty($bean->fetched_row['id'])){   

  $bean->custom_id_c = $new_custom_id;  

  }         

  }   

}

Outcomes