Policies module scope

WARNING:

in scope: Policy renewals … in “tools” menu + separate https://github.com/openimis/policy_renewal_service_vb

Current process

1. policy renewal inserts

Go through policies, families, insurees, products, villages, wards, districts, officer
where valid, policy status is active or expired, expiring within reminding interval, expiring within date range

For each of those policies:

• Check for product or officer substitutions

• Check if the expiring policy already has another policy in place

• Insert into tblPolicyRenewals

• If the photo in the insuree profile is outdated, insert into tblPolicyRenewalDetails

2. update

One simple query to set policy status = expired on all expired policies

3. send sms

The Windows service provides the stored procedure with the XML template to use for sending SMS and returns the filled-in messages.

Go through tblPolicyRenewals with the substitution officer/product/etc

Composing the SMS message has a lot of:
SET @SMSHeader = '--Renewal--' + char(10) + CONVERT(nvarchar(20),@RenewalDate,103) + char(10) + @CHFIDPhoto + char(10) + @InsLastNamePhoto + ' ' + @InsOtherNamesPhoto + char(10) + @DistrictName + char(10) + @WardName + char(10) + @VillageName + char(10) + @ProductCode + '-' + @ProductName + char(10)

SET @NewFamilyMessage = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@FamilyMessage, '@@InsuranceID', @CHFID), '@@LastName', @InsLastName), '@@OtherNames', @InsOtherNames), '@@ProductCode', @ProductCode), '@@ProductName', @ProductName), '@@ExpiryDate', FORMAT(@ExpiryDate,'dd MMM yyyy'))

=> Generate SMS XML message based on all that.

Then, the Windows Service takes these XML files and HTTP posts them to the SMS gateway, one by one. This is in fact the only time consuming task as the others are pretty quick to execute.