pdestefanis/NTD-ORBIS — Issue #27: Complete the phone-based approvals process
Author: pdestefanis
Created: 2012-02-03 22:42:52+00:00
State: open

The approvals process does not seem to be completely implemented as specified in the design:

OK [Item ID] : Approves the aggregate count of [Item ID] at the facility to which the phone is registered. The phone sending the message must be assigned to a user linked to the facility.

The approval should insert a record indicating that an authorized user approved the current [aggregated] quantity for a given facility at a given moment in time. The information below seems to provide *some* of that functionality.

For an approval to be accepted by the system, it needs to originate from a phone associated with a user. The facility associated with the user (not with the phone) will indicate the level at which the aggregate counts are approved. If the sending phone is not associated with any user, then system returns an error.

/* Find the phone ID of the sender
SELECT phones.id as pid FROM phones WHERE substring(phones.phonenumber FROM -8)=substring(' 251923799639' FROM -8) limit 1

/* Find if the sending phone is active
SELECT CASE WHEN phones.deleted = 1 THEN 0 ELSE phones.active END as status FROM phones WHERE phones.id='86' limit 1

/* Finds the location ID associated with the phone
SELECT locationLongitude, locationLatitude, locations.id as lid, phones.id as pid FROM locations, phones WHERE location_id = locations.id and phones.id='86' limit 1

/* Finds the item whose quantity we want to approve
SELECT items.id as did FROM items WHERE items.code = UPPER('TEO') limit 1

/* Finds the last quantity reported for that item in that location
/* This will not work if the sender is associated with an upstream facility, as it is usually the case
/* We need to mark approval for all the quantities for the given item in the child facilities
SELECT quantity_after FROM stats s WHERE s.item_id = 24 AND s.location_id = 191 AND s.id = (select max(st.id) from stats st where st.item_id = s.item_id AND location_id = 191 ) ORDER by created DESC

/* Stores the message received
INSERT INTO messagereceiveds (phone_id, created, rawmessage) VALUES (86, '2012-02-04 00:17:38', 'OK TEO 251923799639')

There is no indication that the approval process is storing information related to the approval in the approvals or approvals_stats tables.
Need to verify if the system is checking that the user can actually post an approval