Invoice module 'Paid' counter

👨‍💻 Development Help
1

Hi,
I’m need to make a kind of counter for paid invoices similar to an auto-increment number.
I want it so that when the user checks the ‘Paid?’ tick box the number increases by one and then when the next record is created it uses the latest number to start with until another tick box is checked and the number increases by one again.
Any idea how to do this?
Thanks!

2

Hi ajgisme,

if you bit elaborate the question, then it will be helpful for us to understand what you actually need. though we understand its has some issue with auto increment value. i think i have one solution but first i need to understand what you actually want.

cheers

3

Basically, there is already a unique invoice number ID; an auto increment number that increases by one every time a record is created.
But what I need now is another unique ID that is only for ‘Paid’ invoices, that only shows up and increases when a paid checkbox is clicked.

Example:

Invoice No 1 - Not Paid
Invoice No 2 - Paid No 1
Invoice No 3 - Not Paid
Invoice No 4 - Paid No 2
Invoice No 5 - Paid No 3

4

ok.
Then You have to create,

  1. before_save logic hook.
  2. then in before_save logic hook function you have to find, on save paid is check or not.
  3. if it is checked, then

(I am considering your paid is your databse table fieldname)


 $number  = $this->db->getOne("SELECT MAX(CAST(paid)) FROM tablename where paid!='Not Paid' ");
 $this->paid = 'paid No '.$number +1;

this will work for you.

5

Thanks for your reply, I have been trying this out but I get this error:
Fatal error: Call to a member function getOne() on a non-object in /home/younggu0/public_html/crm/custom/modules/AOS_Invoices/InvoicesNumberHook.php on line 11

My code is:

<?php if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point'); class InvoicesNumberHook { function DoAnythingNumber(& $focus, $event, $arguments){ global $current_user, $db; if($event=="before_save") { if(isset($_POST["paidcheckbox"])){ $number = $this->db->getOne("SELECT MAX(CAST(invoicenumbergeneration_c))"); $this->invoicenumbergeneration_c = 'paid No '.$number +1; } } } }
6

ok. if getOne is not working , then write simple sql query,

$sql= 'SELECT (MAX(CAST(invoicenumbergeneration_c)) FROM tablename ';
$query=$this->db->query($sql); 
$result= $this->db->fetchByAssoc($query);

$number = $result['invoicenumbergeneration_c'];

Here you will get the result and then do the following,

$this->invoicenumbergeneration_c = 'paid No '.$number +1;

I think now it will work for you.

:cheer:

1 Like
7

I recently got round to trying this and I get this error:

Fatal error: Call to a member function query() on a non-object in /home/younggu0/public_html/crm/custom/modules/AOS_Invoices/ContactHook.php on line 74

8

Can you post the code that you are using which causes this error ?

Ian.

9

Yes well it’s the code that jay deep wrote that I tried:

$sql= 'SELECT (MAX(CAST(numbernew_c)) FROM aos_invoices_cstm ';
$query=$this->db->query($sql);
$result= $this->db->fetchByAssoc($query);

$number = $result[‘numbernew_c’];
$this->numbernew_c = 'paid No '.$number +1;

10

Hi,

I would suggest using the following. I have not tried this but would be something along these lines.

$sql= 'SELECT (MAX(CAST(numbernew_c)) FROM aos_invoices_cstm ';
$query=$GLOBALS[‘db’]->query($sql);
$result= $GLOBALS[‘db’]->fetchByAssoc($query);

$number = $result;
$this->numbernew_c = 'paid No '.$number +1;

The last line doesnt look right. If you are in a logic hook then $this would be the class that this function would exist in and not the bean that you look like you are trying to update.

11

Just a recommendation, it’s cleaner and safer to use the Bean methods, not ugly SQL, for updating data in Sugar (Suite) records…

http://developer.sugarcrm.com/2012/03/23/howto-using-the-bean-instead-of-sql-all-the-time/


$focus = new Account();
$focus->retrieve('my record id');
$focus->name = 'Bar Foo';
$focus->save();

http://apidocs.sugarcrm.com/api/6.5.16/ce/db_data_SugarBean.html


_get_num_rows_in_query( string $query, boolean $is_count_query = false ) : int

http://support.sugarcrm.com/02_Documentation/04_Sugar_Developer/Sugar_Developer_Guide_6.6/02_Application_Framework/SugarBean

http://developer.sugarcrm.com/2012/05/30/howto-load-a-bean-using-the-beanfactory/

http://www.geekgumbo.com/2014/10/07/sugarcrm-the-sugarbean/

1 Like
12

I have tried this and at first I got countless errors but now I have stopped the errors and nothing happens, nothing is returned?

$sqlno = "SELECT MAX(CAST(INVOICENUMBERGENERATION_C AS SIGNED)) FROM aos_invoices_cstm;";
$queryno = $db->query($sqlno);
$resultno = $db->fetchByAssoc($queryno);
$numberno = $resultno;
$bean->resolution = $numberno +1;