Fix centers or groups with wrong meeting dates due to moving clients

Owned by Former user (Deleted)
Last updated: Dec 03, 2010 by Adam Monsen (Unlicensed)Version comment
3 min read

If you're using a version of Mifos older than 1.5.0, the following may apply to you. This document is not up-to-date for versions of Mifos past 1.5.x.

How to fix customer meeting misalignment

While fixing MIFOS-2652 , we found a problem with existing Mifos databases. If you are using a version of Mifos older than 1.5.0 and you moved a client from one group to another, the client will appear to assume the meeting date of the new group, but the meeting of the old group is actually modified.

This issue is fixed in Mifos v1.5.0 (Gazelle C). However, if you're upgrading from a release prior to v1.5.0, data cleanup is necessary after upgrading to fix customers with different IDs than their parents. The following is transcribed from MIFOS-2851  .

Queries

Identify bad data

Query A

Use this query to find customers whose meeting IDs don't match their parents' meeting ids. If this query returns nothing, you're done.

select childcm.updated_flag, childcm.updated_meeting_id,
child.customer_id, child.status_id as status, child.display_name, child.customer_level_id as Level,
child.parent_customer_id, parent.status_id as ParentStatus,
parent.display_name as ParentDisplayName, parent.customer_level_id as ParentLevel,
childcm.meeting_id as ChildMeetingId, childm.meeting_place, childrd.recur_after, childr.days,
parentcm.meeting_id as ParentMeetingId, parentm.meeting_place, parentrd.recur_after, parentr.days
from customer child
join customer_meeting childcm on childcm.customer_id = child.customer_id
join meeting childm on childm.meeting_id = childcm.meeting_id
left join recurrence_detail childrd on childrd.meeting_id = childm.meeting_id
left join recur_on_day childr on childr.details_id = childrd.details_id

join customer parent on parent.customer_id = child.parent_customer_id
join customer_meeting parentcm on parentcm.customer_id = parent.customer_id
join meeting parentm on parentm.meeting_id = parentcm.meeting_id
left join recurrence_detail parentrd on parentrd.meeting_id = parentm.meeting_id
left join recur_on_day parentr on parentr.details_id = parentrd.details_id

where childm.meeting_id <> parentm.meeting_id
/*and (childm.meeting_place = parentm.meeting_place and childrd.recur_after = parentrd.recur_after and childr.days = parentr.days) */
order by parent.customer_id

Query B

Find the 'old' structures that had their meeting details changed because of the transfer bug.

select oldstructure.status_id as "Old Structure Status",
oldstructure.customer_id as "Old Structure Parent Id",
oldstructure.display_name as "Old Structure Parent Name",
oldstructure.customer_level_Id as "Old Structure Parent Level",
oldstructureparent.customer_id as "Next Level Up Parent Id",
oldstructureparent.display_name as "Next Level Up Parent Name",
oldstructureparent.customer_level_Id as "Next Level Up Parent Level"
from customer child
join customer_meeting childcm on childcm.customer_id = child.customer_id
join meeting childm on childm.meeting_id = childcm.meeting_id
left join recurrence_detail childrd on childrd.meeting_id = childm.meeting_id
left join recur_on_day childr on childr.details_id = childrd.details_id

join customer parent on parent.customer_id = child.parent_customer_id
join customer_meeting parentcm on parentcm.customer_id = parent.customer_id
join meeting parentm on parentm.meeting_id = parentcm.meeting_id
left join recurrence_detail parentrd on parentrd.meeting_id = parentm.meeting_id
left join recur_on_day parentr on parentr.details_id = parentrd.details_id

left join customer_hierarchy ch on ch.customer_id = child.customer_id and ch.status = 0
left join customer oldstructure on oldstructure.customer_id = ch.parent_id
left join customer oldstructureparent on oldstructureparent.customer_id = oldstructure.parent_customer_id
where childm.meeting_id <> parentm.meeting_id
group by oldstructure.customer_id
order by oldstructure.display_name

Fix bad data

Query C

This query flags all customer meetings with invalid meeting IDs to be fixed the next time the RegenerateScheduleTask batch job runs. Execute this if #Query A returned any rows.

update customer child
join customer_meeting childcm on childcm.customer_id = child.customer_id
join meeting childm on childm.meeting_id = childcm.meeting_id

join customer parent on parent.customer_id = child.parent_customer_id
join customer_meeting parentcm on parentcm.customer_id = parent.customer_id
join meeting parentm on parentm.meeting_id = parentcm.meeting_id

set childcm.updated_flag = 1, childcm.updated_meeting_id = parentcm.meeting_id
where childm.meeting_id <> parentm.meeting_id

More information

Query D

A list of all top level structures and their meeting days (Monday is usually 1, Friday 5). Useful for getting a quick look at all meetings.

select c.customer_id, c.display_name, c.status_id, c.customer_level_id,
m.meeting_place,
rd.recur_after as "Schedule Every", rt.recurrence_name as "",
r.days as "Working Day"
from customer c
join customer_meeting cm on cm.customer_id = c.customer_id
join meeting m on m.meeting_id = cm.meeting_id
left join recurrence_detail rd on rd.meeting_id = m.meeting_id
left join recurrence_type rt on rt.recurrence_id = rd.recurrence_id
left join recur_on_day r on r.details_id = rd.details_id
where c.parent_customer_id is null

order by c.display_name

Incorrect meetings

In the illustration above, the meeting for Center A/Group A/Client 1 was permanently changed, and the original meeting details were lost. It is necessary for all loan officers to manually verify their meetings and correct them if necessary.

See Also