dhis2-devs team mailing list archive

[moses mwale <isoftcom.ic@xxxxxxxxx>]

Alright thanks Jason on it.

On Mon, Jan 29, 2018 at 1:10 PM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

> Hi Moses,
>
> The version of the function looks to be a bit outdated. I just updated it
> again in this commit
> <https://github.com/dhis2/dhis2-utils/commit/0b5026c70c6f0117dad83c7215ca89395f271f18>.
> Maybe you can try again with this new version and see if it works?
>
> Regards,
> Jason
>
>
> On Mon, Jan 29, 2018 at 11:25 AM, moses mwale <isoftcom.ic@xxxxxxxxx>
> wrote:
>
>> wow some major progress i thought it was special sql. I now know its
>> normal sql.
>>
>> dhis2_test=> SELECT merge_organisationunits('FfX6DGeVsJd', 'qqkYhasbAcf'
>> ,'SUM');
>> ERROR:  column "attributeoptioncomboid" is of type integer but expression
>> is of type character varying
>> LINE 6: value,
>>         ^
>> HINT:  You will need to rewrite or cast the expression.
>> QUERY:  INSERT INTO datavalueaudit SELECT nextval('hibernate_sequence'::
>> regclass),
>> dataelementid,
>> periodid,
>> ( SELECT organisationunitid from organisationunit where uid =
>> 'qqkYhasbAcf' ) as organisationunitid,
>> categoryoptioncomboid,
>> value,
>> now()::timestamp without time zone,
>> 'admin'::character varying(100) as modifiedby,
>> 'MERGE_SOURCE'::character varying(255) as audittype,
>> attributeoptioncomboid
>> FROM datavalue where sourceid = ( SELECT organisationunitid
>> from organisationunit where uid = 'FfX6DGeVsJd' )
>> CONTEXT:  PL/pgSQL function merge_organisationunits(character,character,character
>> varying) line 178 at EXECUTE
>>
>> if i change in the function will it work?
>>
>> On Mon, Jan 29, 2018 at 10:01 AM, Jason Pickering <
>> jason.p.pickering@xxxxxxxxx> wrote:
>>
>>> Hi Moses,
>>>
>>> You need to use the UID of the organisation unit not their integer ID.
>>>
>>> You need to place string literals in single quotes.
>>>
>>> So, it should be something like
>>>
>>> SELECT merge_organisationunits('foo', 'bar' ,'SUM');
>>>
>>>
>>> Regards,
>>> Jason
>>>
>>>
>>> On Sun, Jan 28, 2018 at 5:28 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
>>> wrote:
>>>
>>>> Hello Jason, Knut and Bob
>>>>
>>>> I did according to your per instruction, i had a dump or copy of the
>>>> db, i created a test db with it and loaded the functions then called using
>>>> the shared query
>>>>
>>>> dhis2_test=> SELECT merge_organisationunits(37442, 24872 ,SUM);
>>>> ERROR:  column "sum" does not exist
>>>> LINE 1: SELECT merge_organisationunits(37442, 24872 ,SUM);
>>>>                                                      ^
>>>> That's the error i receive, what is wrong?
>>>>
>>>> thanks in advance.
>>>>
>>>> On Thu, Jan 25, 2018 at 3:32 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
>>>> wrote:
>>>>
>>>>> Thanks all you are really help i appreciate so much. let me do test
>>>>> first on the backed up db if satisfied will run the merge on the Prod.
>>>>> Thanks.
>>>>>
>>>>> On Fri, Jan 26, 2018 at 3:24 AM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
>>>>> wrote:
>>>>>
>>>>>> If you really must work on your production database directly then at
>>>>>> the very least you should shutdown the dhis2 instance while you make
>>>>>> changes.  Naturally you want to keep that down time to a minimum.
>>>>>>
>>>>>> So I would suggest a sequence of:
>>>>>>
>>>>>> (i) make a copy of the database to test these procedures
>>>>>> (ii) test out all of this sql stuff against your db copy (making sure
>>>>>> you keep your sql in files so you can rerun against production)
>>>>>> Once you are happy you can do what you need to do quickly and
>>>>>> correctly,
>>>>>> (iii) shutdown the dhis2 instance
>>>>>> (iv) make a backup of database (again)
>>>>>> (v) run the sql
>>>>>> (vi) restart dhis2 instance
>>>>>>
>>>>>> Stay safe.
>>>>>>
>>>>>> On 25 January 2018 at 13:05, moses mwale <isoftcom.ic@xxxxxxxxx>
>>>>>> wrote:
>>>>>> > This is live (Production) db created a backup. if something goes
>>>>>> wrong will
>>>>>> > revert. But the function itself doesnt affect anything right? what
>>>>>> am going
>>>>>> > to write now will i guess by merging duplicate orgs right?
>>>>>> >
>>>>>> > On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering
>>>>>> > <jason.p.pickering@xxxxxxxxx> wrote:
>>>>>> >>
>>>>>> >> That looks correct.
>>>>>> >>
>>>>>> >> It is of course worth saying, that you should not attempt to
>>>>>> perform this
>>>>>> >> procedure on your production system, unless you have thoroughly
>>>>>> tested it in
>>>>>> >> a development environment!
>>>>>> >>
>>>>>> >> Regards,
>>>>>> >> Jason
>>>>>> >>
>>>>>> >>
>>>>>> >>
>>>>>> >> On Thu, Jan 25, 2018 at 1:55 PM, moses mwale <
>>>>>> isoftcom.ic@xxxxxxxxx>
>>>>>> >> wrote:
>>>>>> >>>
>>>>>> >>> Am using psql. see attached if its correct loading procedure.
>>>>>> >>>
>>>>>> >>> On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring <knutst@xxxxxxxxx>
>>>>>> wrote:
>>>>>> >>>>
>>>>>> >>>> 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
>>>>>> >>>>>> 2) Script to merge two orgunits is here
>>>>>> >>>>>>
>>>>>> >>>>>> 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/sql/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
>>>>>> >>>>>
>>>>>> >>>>>
>>>>>> >>>>>
>>>>>> >>>>>
>>>>>> >>>>> --
>>>>>> >>>>> 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
>>>>>> >>>
>>>>>> >>>
>>>>>> >>>
>>>>>> >>>
>>>>>> >>> --
>>>>>> >>> developer_lusaka_systems
>>>>>> >>
>>>>>> >>
>>>>>> >>
>>>>>> >>
>>>>>> >> --
>>>>>> >> Jason P. Pickering
>>>>>> >> email: jason.p.pickering@xxxxxxxxx
>>>>>> >> tel:+46764147049
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> > --
>>>>>> > 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
>>>>>> >
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> developer_lusaka_systems
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> developer_lusaka_systems
>>>>
>>>
>>>
>>>
>>> --
>>> Jason P. Pickering
>>> email: jason.p.pickering@xxxxxxxxx
>>> tel:+46764147049 <+46%2076%20414%2070%2049>
>>>
>>
>>
>>
>> --
>> developer_lusaka_systems
>>
>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+46764147049 <+46%2076%20414%2070%2049>
>



-- 
developer_lusaka_systems