Triggering a new sequence number from a php script ?

[lcollong]

Hi,

I'm using the sequence element (on submit) for a custom CRM (offers, invoices, contracts, etc...). It's doing perfect as far as the new row follows the manual mechanism (add a new row, fill the elements and submit the form to create it).
But in some situations, I need to create several of them from an external script (actually onAfterProcess php plugin from a specific form). I create the rows and the joined tables ones "by hand", directly in the DB. Not using the Fabrik's class/method.
Is there any "easy way" to trigger a new sequence number from the script ? The "getSequence" method from the plugin class expects a complex array. Is there a way I can build this array and call the getSequence from a script in another form (not knowing about the sequence element params) ?

Meanwhile, I did an horrible hack so that the first time the user see the newly created row (form view) the sequence element says "Assigned after form is submitted" and on the next submit the actual sequence number is calculated (onStoreRow around line 94) :

        $element = $this->getElement();
        $formModel = $this->getFormModel();
        $params = $this->getParams();
        $method = $params->get('sequence_method', 'load');
//        if ($formModel->isNewRecord() && $method === 'submit')
        if (($formModel->isNewRecord() || $formModel->getElementData($element->name) == JText::_('PLG_ELEMENT_SEQUENCE_ASSIGNED_AFTER_SUBMIT')) && $method === 'submit')
        {
            $formData = json_decode(json_encode($formModel->formDataWithTableName));
            $this->swapValuesForLabels($formData);
            $this->setStoreDatabaseFormat($formData, $repeatCounter);
            $data[$element->name] = $data[$element->name . '_raw'] = $this->getSequence($formData);
        }

Definitively not something for which I could make a github's PR ;-)

Other point :
Is there any underlying mechanism to prevent two "getSequence" updating/inserting the table on the same time with the same number (such as the new lock feature) ?
Should I check by myself that the sequence number is effectively unique (or add a SQL constraint) ?

Thanks,

 

[cheesegrits]

I'm not entirely understanding what you are trying to do, but I can answer some questions.

The "getSequence" method from the plugin class expects a complex array. Is there a way I can build this array and call the getSequence from a script in another form

Hmmm. Probably. However, you would have to load an instance of the other form, and get the sequence element model ...

// get a form model instance
$otherForm = JModelLegacy::getInstance('Form', 'FabrikFEModel');
// set the ID for the other form
$otherForm->setId(123);
// grab the sequence element model by name
$sequenceElement = $otherForm->getElement('othertable___sequence');
// or by id ...
// $sequenceElement = $otherForm->getElement(345, true);

Then you can call $elementModel->getSequence(). Which brings up your next question ...

Is there any underlying mechanism to prevent two "getSequence" updating/inserting the table on the same time with the same number (such as the new lock feature) ?

I presume you aren't using PK mode, in which case we still pretty much take care of that - we store the sequence in the #__fabrik_sequence table. Yes, there is a very, very slim chance that there could be a collision. But the window for that is tiny ... the submissions would have to be running within a handful of milleseconds of each other. And actually, about the same chance as lockrow failing, as it uses the same technique of gating using a database.

So ... calling getSequence(), not using PK mode, all you need to pass it is an array with any placeholders you use in the 'affix' ...

$sequence = $sequenceElement->getSequence(array('othertable___somethingused_in_affix' => 'foo'));

If you don't use placeholders in the affix, you can just pass it an empty array.

If you are using PK mode (which from your description I don't think you can be), you would also need to add the rowid to the array, array('rowid' => '123', 'othertable___whatever' => 'foo').

-- hugh

 

[lcollong]

Thanks for these very valuable explanation which could be added to the wiki ?
I've had to set the getSequence function as "public" rather than "private" in plugins/fabrik_element/sequence/sequence.php as it's throw an error due to not being in the right context. Now, it's perfectly working.

You're right, I'm not using PK as we wish to reset the numbering every 1st of January (either "flushing" the fabrik_sequence table - did not try - , either using a "year" placeholder in the numbering scheme as per the wiki example).

Indeed, I don't think the risk of having twice the same number is high enough to justify taking care of it for now. However, it could be a subject in some heavy used app. Could be solved using a column unique constraint in SQL or by checking after saving the record that the number is really unique.... we'll see if it happens !

The reason why I need to achieve this is because we use two different ways of creating an offer (which is build as an invoice with a main table row and several joined items rows from itemlist table). The first one is the regular one for which the standard features works perfectly. The second way is using a cart together with a fancy product list (images) to let the user build his offer the way he wants with some automated process and "easy to understand" ways to go. At the end, there is a button "build the offer" which create the actual offer (main table), convert the cart into itemlist (joined table) and open the resulting form for modification, and submission (which build the pdf). We need to save the cart without actually create the offer... hence the way I went.

As a side question, my script code to create the offer body (main table) looks like this :

    public function create() {
        $db = JFactory::getDbo();       
        $now = date('Y-m-d H:i:s');
       
        // Attribuer numéro devis
        $fabrikFormDevis = JModelLegacy::getInstance('Form', 'FabrikFEModel');
        $fabrikFormDevis->setId(_FABRIK_DEVIS_FORM_ID);
        $sequenceElement = $fabrikFormDevis->getElement(_FABRIK_DEVIS_SEQUENCE_ELEMENT);
        $newNumber = $sequenceElement->getSequence(array());       
       
        $dev = new stdClass();
        $dev->dev_num                = $newNumber;
        $dev->dev_ref_projet        = $this->dev_ref_projet;
        $dev->dev_ref_organisation    = $this->dev_ref_organisation;       
        $dev->dev_ref_contact        = $this->dev_ref_contact;       
        $dev->dev_creation_time        = $now;
        $dev->dev_modif_time        = $now;   
        $dev->dev_creation_user        = $this->currentUser;       
        $dev->dev_date_devis        = $now;   
        $dev->dev_statut            = _STATUT_DEVIS_BROUILLON;   
       
       
        try {
            $result = $db->insertObject('devis', $dev, 'dev_id');
        }
        catch (Exception $e) {
            JLog::add('Création devis. Erreur : '.$e->getMessage(), JLog::ERROR, 'Synapse');                           
            return false;
        }
       
        $this->dev_id = $dev->dev_id;
        return $this->dev_id;
    }

Since we are now loading the form instance to set the number, there is probably a better way to populate the elements and actually create the record using the "fabrikFormDevis" object rather than doing by "hand" as I did ?

 

[lcollong]

Another point about sequence element that may be a new feature start ?

The invoice has several states. A "draft" state means that it can be modified and is not still a "real" invoice. A "validate" state means that no users (apart DB access of course) can change it. It should remain the exact replication of the physical invoice sent to the customer (paper/pdf). At this step the invoice number has to be set such a way that the chronological order is maintain vs the sequence number. It's a legal requirement.

So, I can't set the definitive sequence number on creating the invoice. It has to be a temporary number. The definitive number has to be set on submit with a condition on the invoice status. If it set to "validate" then give it it's final number.
This because, one user can create an invoice far before submitting it to the customer. Meantime another user (or the same) could create another invoice that will be sent to the customer before the former one.

Reason why I think the sequence element should have a conditional eval field in order to be triggered only it this condition is true (or empty) as well as a kind of temporary replacement value based on the rowid for example.

Meanwhile I'm thinking about a way to produce such a behavior with "traditional" go (calc element, onAfterProcess script and so on) as I did for some other project. But it'd be nice to have consistency between numbering scheme and controls over the different elements managed by the CRM I'm building (contracts, quotation, invoices, refunds, etc...) using a standard Fabrik's feature !

 

[cheesegrits]

Reason why I think the sequence element should have a conditional eval field in order to be triggered only it this condition is true (or empty) as well as a kind of temporary replacement value based on the rowid for example.

So do you mean a condition that simply stops the sequence element from generating a sequence entirely?

-- hugh

 

[lcollong]

Actually I think the plugin is triggered only "onNewRecord". Never if the record already exists. Right ?
So I think it needs a way to be triggered also "onUpdateRecord" together with a check of the value being empty ?...
Thus the idea to have a "temporary replacement" value seems not doable.

Pseudo :
If (eval(condition) AND (newRecord OR empty(this))) this->setSequence ?

On my app, I think I would organize things this way :
One sequence element for the temporary number with a condition always true -> set on record creation
One sequence element for the definitive number with a condition on a status -> set on record update+condition=true
One calc element : return empty(definitive_number) ? temporary_number : definitive_number ;

This have an additional advantage to track the original number ("where I came from") vs pdf generation.

 

[cheesegrits]

Actually I think the plugin is triggered only "onNewRecord". Never if the record already exists. Right ?

Yup, that's kind of why I asked that question. We only ever generate a sequence on new, as it's intended to be an immutable identifier.

I guess there's no reason I/you couldn't add an option to conditionally generate a new one on edit, but that option doesn't exist atm.

If you wanted to look at adding that to the code yourself, I could offer advice.

-- hugh

 

[lcollong]

Thanks fo that. I'll take a look and do some hard coded tests and be back to you.