dhis2-devs team mailing list archive

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

This is a start maybe for postgres.



CREATE OR REPLACE FUNCTION migrate_cdes() RETURNS INTEGER AS
$BODY$
DECLARE

this_indicatortypeid integer;
BEGIN

SELECT INTO this_indicatortypeid indicatortypeid From IndicatorType Where
Indicatorfactor = 1 LIMIT 1;

EXECUTE 'INSERT INTO indicator (
indicatorid, uuid, name, alternativename, shortname, code,
description, annualized, indicatortypeid, numerator, numeratordescription,
numeratoraggregationtype, denominator, denominatordescription,
denominatoraggregationtype, sortorder, url, lastupdated )
(
select de.dataelementid, de.uuid, de.name, de.alternativename, de.shortname,
de.code,
de.description, false,'
|| this_indicatortypeid
||',e.expression, e.description,
de.aggregationtype, null, null, de.aggregationtype, null, de.url,
de.lastupdated
from dataelement de, calculateddataelement cde, expression e
where (de.dataelementid = cde.calculateddataelementid) and (cde.expressionid
= e.expressionid));';

RETURN 1;

  END;
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100

2011/4/27 Lars Helge Øverland <larshelge@xxxxxxxxx>

>
> I also believe that a SQL script would be appropriate for this and it seems
> Hieu is conceptually right here..
>
> Lars
>
>
> On Mon, Apr 25, 2011 at 9:19 AM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
>> Hi Hieu,
>>
>> Yes, this is along the same lines of  what I was thinking, but was not as
>> quick as you about coming up with the SQL. :)
>>
>> I will try this query out (modified to fit Postgres) and see how it works.
>>
>>
>> Regards,
>> Jason
>>
>>
>> On Mon, Apr 25, 2011 at 8:55 AM, Hieu Dang Duy <
>> hieu.hispvietnam@xxxxxxxxx> wrote:
>>
>>> Hello Jason,
>>>
>>> In this case, I think we have to make a store procedure or something like
>>> this statement to convert from CDEs to Indicator as:
>>>
>>> Declare @IndicatorTypeId;
>>>
>>> Set @IndicatorTypeId = (Select indicatortypeid From IndicatorType Where
>>> Indicatorfactor = 1);
>>>
>>> INSERT INTO indicator (
>>>             indicatorid, uuid, name, alternativename, shortname, code,
>>>             description, annualized, indicatortypeid, numerator,
>>> numeratordescription,
>>>             numeratoraggregationtype, denominator,
>>> denominatordescription,
>>>             denominatoraggregationtype, sortorder, url, lastupdated )
>>>     (
>>>         select de.dataelementid, de.uuid, de.name, de.alternativename,
>>> de.shortname, de.code,
>>>             de.description, false, *@IndicatorTypeId*, e.expression,
>>> e.description,
>>>             de.aggregationtype, null, null, de.aggregationtype, null,
>>> de.url, de.lastupdated
>>>             from dataelement de, calculateddataelement cde, expression e
>>>             where (de.dataelementid = cde.calculateddataelementid) and
>>> (cde.expressionid = e.expressionid
>>>   ));
>>>
>>> How to you think ?
>>>
>>> On Mon, Apr 25, 2011 at 11:20 AM, Jason Pickering <
>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>
>>>>  Feeling like some spring cleaning on Easter, Lars?
>>>>
>>>> I am thinking if may be necessary to document how to convert CDEs to
>>>> indicators. We have  a large number of them in , and recreation by hand will
>>>> be very painful.
>>>>
>>>> It would seem that transferring of the "expression"
>>>> from calculateddataelement to the "numerator" field of indicator would be
>>>> feasible.
>>>>
>>>> The other thing I had thought before we hacked up the 1.4 code was to
>>>> import 1.4 CDEs as indicators. This would seem to be feasible as well but
>>>> would obviously require work.
>>>>
>>>> Any thoughts about how to convert CDEs to indicators, and if we are
>>>> still going to maintain some level of backwards compatibility?
>>>>
>>>> Regards,
>>>> Jason
>>>>
>>>>
>>>>
>>> --
>>> Good health !
>>>
>>>
>>
>>
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+260974901293
>>
>> _______________________________________________
>> 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
>>
>>
>


-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+260974901293