dhis2-devs team mailing list archive

[Knut Staring <knutst@xxxxxxxxx>]

Just run everything in your PgAdmin SQL interface.

On Thu, Jan 25, 2018 at 1:22 PM, moses mwale <isoftcom.ic@xxxxxxxxx> wrote:

> okay nice thanks, is it to load user-defined functions the same way you
> load stored procedures, by packaging the Java class or classes into a JAR
> file and then loading the JAR file using the LOAD CLASSES statement?
>
> On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
>> Hi Moses,
>>
>> You need to load both of those functions into your database.
>>
>> 1) Script to delete an orgunit is here
>> <https://github.com/dhis2/dhis2-utils/blob/master/resources/sql/delete_orgunit_with_data.sql>
>> 2) Script to merge two orgunits is here
>> <https://github.com/dhis2/dhis2-utils/blob/master/resources/sql/merge_orgunits.sql>
>>
>> After that, just call the function with
>>
>> SELECT merge_organisationunits(source_uid,dest_uid ,strategy);
>>
>> where `source_uid` is the UID of the organisation unit you want to get
>> rid of, `dest_uid` is the UID of the organisation unit you want to keep and
>> move data to
>> and `strategy` is one of the following:
>>
>> SUM: Returns the sum of the values, when there is overlapping data.
>> MAX: Returns the max of the values, when there is overlapping data.
>> MIN: Returns the min of the values, when there is overlapping data.
>> AVG: Returns the mean of the values, when there is overlapping data.
>> LAST: Returns the last value entered, when there is overlapping data.
>> FIRST: Returns the first vale entered, when there is overlapping data.
>>
>> Note that this only applies to numeric data. For other data (Boolean,
>> text, etc) which cannot be aggregated numerically, the last value will
>> always be taken.
>>
>> The merge script will not handle situations where you have overlapping
>> tracker/event data, so you would need to figure out how to handle that
>> yourself!
>>
>> Hope that helps to clarify and good luck!
>>
>> Regards,
>> Jason Pickering
>>
>>
>>
>>
>>
>> On Thu, Jan 25, 2018 at 5:30 AM, moses mwale <isoftcom.ic@xxxxxxxxx>
>> wrote:
>>
>>> Hey Jason And Knut, thanks for the information, i have been trying to
>>> understand the sql script shared and tried to execute but to no effect,
>>> please can you help me understand where to place the source id and orgunit
>>> ids in the script after several attempts of try its has became more
>>> complex.
>>>
>>> I have attached a datavalue table and 1 duplicate orgunit to be merged.
>>> please help me.
>>>
>>> On Mon, Jan 8, 2018 at 8:26 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
>>> wrote:
>>>
>>>> Thanks very much, allow me to go through and implement the given
>>>> knowledge
>>>>
>>>> On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering <
>>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>>
>>>>> Hi Moses
>>>>> We have some SQL scripts for this here.
>>>>>
>>>>> https://github.com/dhis2/dhis2-utils/blob/master/resources/s
>>>>> ql/merge_orgunits.sql
>>>>>
>>>>> This script will not handle tracker data but could probably be adapted
>>>>> fairly easily to do so.
>>>>>
>>>>> Regards,
>>>>> Jason
>>>>>
>>>>>
>>>>>
>>>>> On Jan 7, 2018 7:28 AM, "Knut Staring" <knutst@xxxxxxxxx> wrote:
>>>>>
>>>>>> Hi Moses,
>>>>>> I would take a backup of the database and then do this with an sql
>>>>>> script, just changing the sourceid (the database internal referent to the
>>>>>> organisationunitid) in the datavalues table. But you may get blocked if the
>>>>>> same period has been filled for both, in which case you may want to do a
>>>>>> "NOT IN" or left join.
>>>>>>
>>>>>> Knut
>>>>>>
>>>>>> On Jan 7, 2018 10:29, "moses mwale" <isoftcom.ic@xxxxxxxxx> wrote:
>>>>>>
>>>>>>> Hey devs is it possible to merge facilities without losing data,
>>>>>>> some admin accidentally created other duplicates units into the system.
>>>>>>> anyone knows how its done?
>>>>>>>
>>>>>>> developer_lusaka_systems
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>> _______________________________________________
>>>>>> 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
>>>>>>
>>>>>>
>>>>
>>>>
>>>> --
>>>> developer_lusaka_systems
>>>>
>>>
>>>
>>>
>>> --
>>> developer_lusaka_systems
>>>
>>
>>
>>
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+46764147049 <+46%2076%20414%2070%2049>
>>
>
>
>
> --
> developer_lusaka_systems
>



-- 
Knut Staring

Department of Information, Evidence and Research
World Health Organization, Geneva, Switzerland
Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522
Skype:     knutstar