dhis2-devs team mailing list archive

[moses mwale <isoftcom.ic@xxxxxxxxx>]

Thanks very much it has worked just analyzing the data now. You a savior.

On Mon, Jan 29, 2018 at 1:20 PM, moses mwale <isoftcom.ic@xxxxxxxxx> wrote:

> 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
>



-- 
developer_lusaka_systems