This is my solution.
1. Create a list
Numeration with elements field for the required sequential numbers (Sales Sequential Number, Orders Sequential Number, Invoices Sequential Number).
2. Create list
Users with fields User (element plug-in user) and Group Of Numbers (element databasejoin with list Numeration to get group id).
3. Create list
Sales with element calc
Sales Number with this code:
PHP:
//group of numbers for logged Joomla User
$grn = JFactory::getDBO();
$grn->setQuery("SELECT numeration FROM table_users WHERE user = '{$my->id}'");
$grn = $grn->loadResult();
//sales seguential number from list Numeration
$sn = JFactory::getDBO();
$sn->setQuery("SELECT sales_sequential_number FROM table_numeration WHERE id = '$grn'");
$sn = $sn->loadResult();
//sales number of this sale
$sn2b = JFactory::getDBO();
$sn2b->setQuery("SELECT sales_number FROM table_sales WHERE id = '{table_sales___id}'");
$sn2b = $sn2b->loadResult();
//new record variable
$isnew = $this->getFormModel()->isNewRecord();
IF ($isnew)
{ return $sn; }
ELSE
{ return $sn2b; }
!!! Important: Set
Only Calc on Save.
4. Create field
Group Of Numbers with element calc with this code:
PHP:
$nowgrn = JFactory::getDBO();
$nowgrn->setQuery("SELECT numeration FROM oerp_users WHERE user = '{$my->id}'");
$nowgrn = $nowgrn->loadResult();
//sales number of this record
$grn = JFactory::getDBO();
$grn->setQuery("SELECT group_numbers FROM oerp_sales WHERE id = '{oerp_sales___id}'");
$grn = $grn->loadResult();
$isnew = $this->getFormModel()->isNewRecord();
IF ($isnew)
{ return $nowgrn; }
ELSE
{ return $grn; }
Create some other fields.
5. Add Autofill plugin to Form
Sales (get
Sales Sequential Number from List
Numeration and autofill element field
Sales Number from list
Sales, when add new record).
List -> Numeration
Lookup field -> Group
Field to observe -> Group Of Numbers
Map data -> {"table_numeration___sales_sequential_number":"table_sales___sales_number"}
Options: Autofill on load ->New Forms Only
6. Add PHP plugin to Form
Sales (update
Sales Sequential Number field in list
Numeration when you create a new record).
PHP:
$grn = JFactory::getDBO();
$grn->setQuery("SELECT numeration FROM oerp_users WHERE user = '{$my->id}'");
$grn = $grn->loadResult();
$ssqn = JFactory::getDBO();
$ssqn -> setQuery("SELECT sales_number FROM oerp_sales WHERE group_numbers = '{$grn}' ORDER BY id DESC LIMIT 1");
$ssqn = $ssqn->loadResult()+1;
$ssqnu = JFactory::getDBO();
$ssqnu -> setQuery("UPDATE oerp_numeration SET sales_sequential_number = '$ssqn' WHERE id = '$grn'");
$ssqnu->query();
* This code must be used when add new sale (PHP on
NEW) and Process script -> End of form submission (onAfterProcess)
7. Some validations:
7.1. List
Users->field
Group Of Numbers
- Action: not empty
7.2. List
Numerations->field
Sales Sequential Number
- Action: php
php code
PHP:
$value = '{table_numeration___sales_sequential_number}';
$db =JFactory::getDBO();
$query=("SELECT sales_number FROM table_sales where sales_number = $db->quote($value)");
$db->setQuery($query);
$response = $db->loadResult();
if ($response == $value) {
return false;
} else {
return true;
}
Match or Replace: Match
* check for an existing number in the list
Sales
- Action: php
php code
PHP:
$value = '{table_numeration___sales_sequential_number}';
$db =JFactory::getDBO();
$query=("SELECT sales_sequential_number FROM table_numeration where sales_sequential_number = $db->quote($value)");
$db->setQuery($query);
$response = $db->loadResult();
if ($response == $value) {
return false;
} else {
return true;
}
Match or Replace: Match
* check for an existing number in the list
Numeration