Let auto_increment (logic_hook) start over whenever a new Project_Call is created ??

[dekleinemedia]

Hi, I have the following issue;

I have customized my Project Module with some custom fields. Now my Project Module has a subpanel Calls.
In Calls (custom/modules/Calls) i have created a logic_hook which has the function to auto-increase whenever a new Call is created.
The problem is;
When I have created a new Project, and i want to create a new Call through subpanel the auto-increment works fine. First Call gets #1, Second Call gets #2 etc..

BUT when I create a new Project (Project #2) and I create a new Call through the subpanel, the First Call gets #3. The auto-increment of logic_hook has continued. How can i make this so, that whenever I create a new Project, and i create a new Call by subpanel the logic_hook starts over again with #1 ??

I have added my add_code_hook file and a screenshot of the situation.

Please help me and tell me how I can fix this Thanks in advance

 <?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
 
class add_code {
 
    const CODE_PREFIX = "";
    const CODE_SUFFIX = "";
    const CODE_SEPARATOR = "";
    const CODE_FIELD = "index_number_c";
    const CUSTOM_TABLE = "calls_cstm";
    const ADD_DATE = "0";
    const DATE_FORMAT = "y";
    const ZERO_PADDING = 1; //minimum amount of characters desired for the number. ie 4 = 0001, 3 = 001
    const FIRST_NUM = "1"; //default to start with.
 
    function add_code(&$bean, $event, $arguments)
    {
        require_once('log4php/LoggerManager.php');
 
        //pattern to detect valid code, designed to detect non padded numbering too.
        $pattern = "/^" . self::CODE_PREFIX . (self::ADD_DATE?"[0-9]{2}":"") . self::CODE_SEPARATOR . "([0-9]+)" . self::CODE_SUFFIX . "$/";
 
        $GLOBALS['log']->debug("add_code function fired after save.");
 
        if(!preg_match($pattern, $bean->fetched_row[self::CODE_FIELD])) {
 
            $db =  DBManagerFactory::getInstance();
            $query =  " select " . self::CODE_FIELD . " from " . self::CUSTOM_TABLE .  
                   " where (" . self::CODE_FIELD . " <> '' or " . self::CODE_FIELD . " is not null) order by ".self::CODE_FIELD." desc limit 1";  
    
            $result = $db->query($query, true, 'Error selecting most recent ' . self::CODE_FIELD . ' CODE');
    
            if ($row=$db->fetchByAssoc($result)) {
                $last_code = $row[self::CODE_FIELD];
            } else {
                //no codes exist, generate default - PREFIX CURRENT_YEAR SEPARATOR FIRST_NUM
                $last_code = self::CODE_PREFIX . (self::ADD_DATE?date(self::DATE_FORMAT):"") . self::CODE_SEPARATOR . self::FIRST_NUM . self::SUFFIX;
            }
    
            $GLOBALS['log']->debug("Last Code: " . $last_code);
    
            preg_match($pattern, $last_code, $matches);
            $num=$matches[1];
            $num++;
            $pads = self::ZERO_PADDING - strlen($num);
            $new_code = self::CODE_PREFIX . (self::ADD_DATE?date(self::DATE_FORMAT):"") . self::CODE_SEPARATOR;
    
            //preform the lead padding
            for($i=0; $i < $pads; $i++) {
                $new_code .= "0";
            }
 
            $new_code .= $num . self::CODE_SUFFIX;
 
            $GLOBALS['log']->debug("New Code: " . $last_code);
            
            //write to database
            $query = "INSERT INTO " . self::CUSTOM_TABLE . " (id_c, " . self::CODE_FIELD . ") VALUES ('$bean->id', '$new_code') ON DUPLICATE KEY UPDATE " . self::CODE_FIELD . "='$new_code'";
            $result = $db->query($query, true, 'Error adding ' . self::CODE_PREFIX . ' code');
        } else {
            $GLOBALS['log']->debug("Code Exists...Exiting " . $bean->fetched_row[" . self::CODE_FIELD . "]);        
        }
    }
}
 
?>  

[andopes]

You need to modify the add_code_hook to get the latest auto increment number based on related project.
For example, you can count how many calls is related to the related project and then set the auto increment field as the total count plus one.

Cheers

[SugarDev.net]

try

 $query =  " select " . self::CODE_FIELD . " from " . self::CUSTOM_TABLE .  
                   " where (" . self::CODE_FIELD . " <> '' or " . self::CODE_FIELD . " is not null) AND parent_id = '".$bean->parent_id."' order by ".self::CODE_FIELD." desc limit 1"; 
  

[dekleinemedia]

Hi, first thanks for the help André and SugarDev!

When i try to change SugarDev's code in my add_code_hook.php (Calls) after repair i want to create a new Call in a Project, i get this message;

Error selecting most recent index_number_c CODE Query Failed: select index_number_c from calls_cstm where (index_number_c <> '' or index_number_c is not null) AND parent_id = '' order by index_number_c desc limit 1::MySQL error 1054: Unknown column 'parent_id' in 'where clause'

There is no parent_id in calls_cstm only in calls column. How to solve this? Thankss