dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Hi Jose,

There are many ways to skin a cat, and as Knut says, creating an "App" is
one way to possibly achieve this. I personally have no idea how to do this,
but could imagine it would be possible in some way.

Maybe Lars or the other devs can comment, but I am thinking the easiest way
would be to somehow calculate your indicator when the "Complete" button is
pressed, and save this effectively as a data value. This indicator might
take the form of  a hidden field, but would be posted back to the
application and saved as a normal data value when the "On-complete" button
is pressed. The advantage with this of course is that all of the values
would be available through the normal analytics resources. I do not know
exactly how this could be done, but if the indicator would be calculated on
the fly and then effectively treated as a data value and posted back to the
application when the complete button is pressed, it would seem to be the
easiest method I think. Since indicators can already be inserted into a
form, it would seem like little code required to calculate the value from
the data elements, and then post it back through the WebAPI and save it as
a data value. Again, just hypothesizing, but it would seem to be possible.

The other option (which I would likely prefer given my choice of tools)
would be a trigger which would happen when a record is inserted into the
"completedatasetregistration" table. This happens when the user completes
the records, so it is certainly possible to create a trigger from such an
action. Thinking though the code, you could detect when  a record is
completed, and then create a stored procedure to calculate the indicator
from  the values. I see a few problems with this though, and one of them is
the parsing of the indicators. There are robust methods to do this in Java,
but you would need to develop methods to parse out the indicator into
something which could be used with PLSQL. Not really sure it would be worth
it.

Would be interested to see what you come up with.

Regards,
Jason



On Fri, Jul 19, 2013 at 12:37 PM, Jose Garcia Muñoz <josemp10@xxxxxxxxx>wrote:

>
> Many thanks Jason,
>
> we plan to present the information to the users as dataelements associated
> to a period, Org. unit, and forms. So, we have different forms with a questionnaire
> and for each one we create also a dataelement, not editable for the users,
> to store the value of the indicator. This was the initial plan. So, when
> the indicator (score) is calculated we want to "copy" that value inside the
> that dataelement (using a trigger in the database).
>
> The ideal solution would be to generate all this process automatically
> when the user completes the questionnaire, but I think that it is not
> possible right? So, in my understanding the options are:
>
> 1) As you said before, run the datamart, and with a trigger get the value
> of the indicator already stored in the database, and copying to our
> dataelement.
>
> and maybe...??
>
> 2) Create a script that could use the analytic API to get the current
> value of the score. Could be also a valid approach?
>
> Maybe I am missing something (I am not an expert in this area of DHIS2),
> Any other ideas or suggestions?
>
> Thanks
> Jose
>
>
> On Sun, Jul 14, 2013 at 10:07 PM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
>> Hi Jose,
>> The completeddatasetgregistration object is not really important in this
>> case (well it might be, but not sure). The point I was trying to make was
>> the use of triggers when a value is inserted into a table. I have not
>> started to pick apart the analytics stuff yet to even know whether or not
>> the values actually exist there but I suppose you could use the datamart to
>> get the value, and then create an auditing mechanism to keep track of all
>> values. The problem with the auditing tables is they typically tend to be
>> really huge, so I am sure the procedure would need to be significantly
>> different than what I have illustrated, but the approach might be worth
>> attempting. Probably not really challenging from a code standpoint, but
>> making sense of what comes out of the SP, and presenting it back to the
>> users in a digestible format might be a big challenge.
>>
>> Regards,
>> Jason
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Sun, Jul 14, 2013 at 9:48 PM, Jose Garcia Muñoz <josemp10@xxxxxxxxx>wrote:
>>
>>>
>>> Many thanks Jason, Bob,
>>>
>>> Bob, I get your point, but the tricky part is that the final score is
>>> not calculated as a sum of the answers of the dataset, because also each
>>> question (I mean each "trueOnly" DE) has associated a weight, so the final
>>> score is = W1*Q1 + W2*Q2 + ... Wn*Qn. So in the future not only the number
>>> of questions could change (that will be slightly easier to deal with, as
>>> you could define a generic SP no matter the number of questions the dataset
>>> contain), but also the weights could change and there is no chance to
>>> generalize the formula. So that was my reason to try to define one
>>> indicator (with a formula defined by the local DHIS users), copy the
>>> indicator value into a dataelement, as the formula could change in the
>>> future but still we need to maintain the old values of the scores.
>>>
>>> Jason, we don't know if the value is finalized but it is not really
>>> important. What we'd like to do is to let the users to know the score of
>>> one specific period of time no matter which formula is used in the present.
>>> But yes, maybe one trigger on the completedatasetregistration would make
>>> part of the job. Many thanks for the code, I will try to follow and
>>> understand it :)
>>>
>>>
>>> On Sun, Jul 14, 2013 at 9:06 PM, Jason Pickering <
>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>
>>>> Hi Jose,
>>>> I do not understand exactly what you are trying to do. It sounds a bit
>>>> tricky, but if I understand correctly, you would like to somehow preserve
>>>> the original state of your indicator, in spite of the fact that you may
>>>> want to change it at a later point in time. I think this is particularly
>>>> difficult to do. Creating a table of inserted values is fairly simple using
>>>> a function similar to the one below. This SP will insert records into a
>>>> separate table called "pricate.completeddatasetregistration_new_records,
>>>> whenever a record is inserted into the "completeddatasetregistration"
>>>> table. This is a pretty typical approach to create an audit table. I am
>>>> guessing that you could create an audit table of your indicators, and then
>>>> create a separate function to prune out all values except the first one
>>>> (which would know through a time stamp). However, the problem with this
>>>> approach is, you may not really know if this value is the "final" value or
>>>> not; the data might change, and in this case, the indicator value would
>>>> need to change (but not because of a change to the indicator formula).
>>>>
>>>> Sounds pretty complex to me. How do you know when the value is
>>>> finalized and will never be changed again?
>>>>
>>>> Regards,
>>>> Jason
>>>>
>>>>
>>>> SQL below..
>>>>
>>>> Here is the SP which inserts the records into another table.
>>>>
>>>> CREATE OR REPLACE FUNCTION completedatasetregistration_new()
>>>>   RETURNS trigger AS
>>>> $BODY$
>>>> DECLARE
>>>>  changed boolean;
>>>> BEGIN
>>>>
>>>>    INSERT INTO
>>>> private.completedatasetregistration_new_records(datasetid,
>>>> periodid,sourceid,
>>>> date, storedby)
>>>>    VALUES (new.datasetid, new.periodid,new.sourceid,
>>>> now(),new.storedby);
>>>>    RETURN new;
>>>>
>>>> END;
>>>> $BODY$
>>>>   LANGUAGE plpgsql VOLATILE
>>>>   COST 100;
>>>> ALTER FUNCTION completedatasetregistration_new()
>>>>   OWNER TO postgres;
>>>>
>>>> Here is the trigger function  attached to the
>>>> "completeddatasetregistration" table...only after an insert.
>>>>
>>>>
>>>> CREATE TRIGGER inserts_completedatasetregistration
>>>>   AFTER INSERT
>>>>   ON completedatasetregistration
>>>>   FOR EACH ROW
>>>>   EXECUTE PROCEDURE completedatasetregistration_new();
>>>>
>>>>
>>>> On Sun, Jul 14, 2013 at 8:40 PM, Jose Garcia Muñoz <josemp10@xxxxxxxxx>wrote:
>>>>
>>>>>
>>>>> Thanks Bob!
>>>>>
>>>>> You are right in the sense that the indicator becomes redundant, but I
>>>>> think we will still need it because we can train the end-users in the use
>>>>> of formulas and indicators inside DHIS2, but stored procedures is quite
>>>>> hard if they don't have much technical skills. So the idea I think is to
>>>>> define a function in plpgsql to make a copy of the value of the indicator
>>>>> into the dataelement (knowing the period and source of course) linking the
>>>>> function to a trigger. Please correct me if I am wrong, as I don't have
>>>>> much experience using triggers.
>>>>>
>>>>> Also, when you introduce an indicator into a dataset, when does the
>>>>> indicator update its value? (when you click in the complete button of the
>>>>> dataentry maybe?) and which table stores the value of the indicator?
>>>>> (attributevalue?)
>>>>>
>>>>> Thanks
>>>>> Jose
>>>>>
>>>>>
>>>>> On Sun, Jul 14, 2013 at 12:40 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>wrote:
>>>>>
>>>>>> Hi Jose
>>>>>>
>>>>>> Copying to devs list as there may be greater wisdom there :-)
>>>>>>
>>>>>>  I think your approach can work .. ie store your historical scores
>>>>>> as dataelement.
>>>>>>
>>>>>> Regarding regenerating the formula, again I think you are right and
>>>>>> will have to resort to a database function as I can't think of an easy
>>>>>> workaround through the application and you really don't want to do these
>>>>>> manually.
>>>>>>
>>>>>> Though I start to think that if you go down this route perhaps the
>>>>>> indicator becomes redundant.  You will have your formula as stored proc in
>>>>>> database and can probably use that to populate your "score" dataelement
>>>>>> directly.
>>>>>>
>>>>>> Bob
>>>>>>
>>>>>>
>>>>>> On 14 July 2013 12:15, Jose Garcia Muñoz <josemp10@xxxxxxxxx> wrote:
>>>>>>
>>>>>>>
>>>>>>> Hi guys,
>>>>>>>
>>>>>>> How are you doing?
>>>>>>>
>>>>>>> first at all, maybe I should send this email to the dev user list,
>>>>>>> if so, sorry about that and please feel free to resend it.
>>>>>>>
>>>>>>> I am dealing with several forms in DHIS2 with all its DE of the
>>>>>>> "trueOnly" type value. For each form, one final score is calculated
>>>>>>> depending on the number of questions answered by the users and one weight
>>>>>>> associated for each question. Of course, we have defined an indicator to
>>>>>>> calculate this value. But, what about when the number of questions (or the
>>>>>>> different weights) change along the time (so the formula needs to be
>>>>>>> redefined) and we still need to maintain the historical score of the "old"
>>>>>>> formulas?
>>>>>>>
>>>>>>> We were thinking about the possibility of creating a data element
>>>>>>> for each form to store the historical scores. Does it make sense? If so,
>>>>>>> the big challenge will be the connection of the indicator (when its value
>>>>>>> is calculated) with the Data Element. Some ideas about how could I deal
>>>>>>> with this? Create a trigger inside the database (with an 'on completion'
>>>>>>> event)?
>>>>>>>
>>>>>>> Best regards
>>>>>>> Jose
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Mailing list: https://launchpad.net/~dhis2-devs
>>>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>
>>>>>
>>>>
>>>
>>
>