Invoice number system

[mkainz]

Hi! I've created 5 elements:

-Date Complete
-Year (Got from date, calc on save)
-Document Type (Invoice, pre-invoice)
-Number (If are a record present in the DB with X number where Year(date) = $Year and DocumentType = $document, return $number+1, else, return $number_confirm)
-Number Confirm

It works only when creating a new row, if i try to modify the already existing row it auto add +1 to the number.
Is there a logic way to fix it?

I can't use the ID element type, 'cause invoice number returns to 0 when the new year comes.

 

[myfatebiz]

You might can use like code below on Number:

$ids = '{tablename___id_raw}';

if($ids == '') {
return $number+1; // your code here for add new record
} else {
return $data; // your code here for edit record
}

 

[mkainz]

that if is not working, i've changed it to this, but it still incresing the number value on every edit>save:

$numberconfirm = '{table___numberconfirm}';
$cdd = '{table___tipo_cdd}';
$date = '{table___date}';
$year = substr($date, 0, 4);
$ids = '{table___id}';

/* COUNT HOW MANY DOCS HAVE THIS VALUE*/
  $db =& JFactory::getDBO();
  $query = "SELECT COUNT(`number`)
  FROM `table`
  WHERE YEAR(`date`) = '$year' AND `tipo_cdd` = '$cdd' AND `numberconfirm` ='$numberconfirm' ";
  $db->setQuery($query);
  $found= $db->loadResult();

if($found != 0) {
/* FIND THIS PRECISE NUMBER */
  $db2 =& JFactory::getDBO();
  $query2 = "SELECT `number`
  FROM `table`
  WHERE YEAR(`date`) = '$year' AND `tipo_cdd` = '$cdd' AND `conferma_numero` ='$confermanumero' ";
  $db2->setQuery($query2);
  $precise = $db2->loadResult();
return $precise;
}

elseif ($found == 0 && is_numeric($ids)) {

  /* FIND ALL DOCUMENTS OF THIS TYPE */
  $db1 =& JFactory::getDBO();
  $query1 = "SELECT COUNT(`number`)
  FROM `table`
  WHERE YEAR(`date`) = '$year' AND `tipo_cdd` = '$cdd'";
  $db1->setQuery($query1);
  $total = $db1->loadResult(); 
return $total +1;

}

 

[myfatebiz]

Try the code below.

$isnew = $this->getFormModel()->isNewRecord();

if($isnew) {
return 'This is for add new record';
}
if(!$isnew) {
return 'This is for edit record';
}

 

[mkainz]

Already solved using the Field with Eval , but thanks

 

[dimoss]

Hi

Can you describe how you did it finally?

Thanks

 

[mkainz]

two elements:
Calc Element
Field element

Calc Element:
If ID != NULL, get value from field element, else count rows on database where date (year) = this year and return value +1, (AJAX CALCULATION)

Field Element: Eval option turned to ON, used like calc field, i wrote code in EVAL:
return the calc element. (HIDDEN, IT AUTO CALCS ON SAVE, at first look it will report code like {table___element}, after saved it really shows the number. (weird but real))

Hope this trick will help.