Last active
June 15, 2016 07:16
-
-
Save soloincc/aac2de4afdcaf3cbbc39615899ec5825 to your computer and use it in GitHub Desktop.
mod_pull_odk_submissions.php is part of the Ngombe Planner scripts that is used to pull EADD II longitudinal submissions, submitted to an ODK Aggregate, http://opendatakit.org, process the submissions and merge them to a MySQL database which has data submitted by Ng'ombe Planner, http://np.azizi.ilri.org/ngombeplanner
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| define('OPTIONS_COMMON_FOLDER_PATH', '../../common/'); | |
| define('OPTIONS_MODULE_EMAIL_CONFIG_FILE', 'np_main.ini'); | |
| require_once OPTIONS_COMMON_FOLDER_PATH . 'mod_general_v0.6.php'; | |
| require_once 'ngombeplanner_config'; | |
| require_once OPTIONS_COMMON_FOLDER_PATH . 'dbmodules/mod_objectbased_dbase_v1.1.php'; | |
| /** | |
| * Description of mod_pull_odk_submissions | |
| * | |
| * @author mwanzo | |
| */ | |
| class mergeNewODKSubmissions { | |
| private $authCookies; | |
| private $tmpDir; | |
| private $sessionID; | |
| private $userAgent; | |
| private $submissionIDs; | |
| private $json; | |
| private $ROOT = './'; | |
| private $submissionXObjects; | |
| private $selectManyItems = array(); | |
| private $np_db = 'azizi_eadd_np'; | |
| private $additionalMessage = ''; | |
| private $curInstanceID = ''; | |
| private $curTopElement = ''; | |
| private $formsDetails = array( | |
| array('instanceId' => 'eadd_tz_long_v1_4', 'topElement' => 'eadd_tz_long'), | |
| array('instanceId' => 'eadd_tz_long_v1_5_3', 'topElement' => 'eadd_tz_long_v1.5.3') | |
| ); | |
| public $stats = array(); | |
| private $gps_cols = array( | |
| 'lat' => 's1q2_a17_gps_Latitude', | |
| 'lon' => 's1q2_a17_gps_Longitude', | |
| 'alt' => 's1q2_a17_gps_Altitude', | |
| 'acc' => 's1q2_a17_gps_Accuracy' | |
| ); | |
| private $eventDetails = array( | |
| 'illness' => array ('db_name' => 'Sickness', 'id' => NULL), | |
| 'in_calf' => array ('db_name' => 'Pregnancy Confirmation', 'id' => NULL), | |
| 'insemination' => array ('db_name' => 'Artificial Insemination', 'id' => NULL), | |
| 'dry' => array ('db_name' => 'Dry Off', 'id' => NULL), | |
| 'calved_down' => array ('db_name' => 'Calving', 'id' => NULL), | |
| 'exited' => array ('db_name' => 'Exit', 'id' => NULL), | |
| 'death' => array ('db_name' => 'Death', 'id' => NULL), | |
| 'acquired' => array ('db_name' => 'Acquisition', 'id' => NULL) | |
| ); | |
| private $calvingTypes = array( | |
| 'full_term' => 'Normal', | |
| 'still' => 'Still', | |
| 'premature' => 'Premature', | |
| 'abort' => 'Abortion', | |
| 'prob_full_term' => 'Problematic Fulll-term' | |
| ); | |
| public function __construct(){ | |
| //setting the date settings | |
| date_default_timezone_set ('Africa/Nairobi'); | |
| if(!file_exists($this->ROOT.'errors')){ | |
| mkdir($this->ROOT.'errors',0777,true); | |
| } | |
| $this->Dbase = new DBase('mysql'); | |
| $this->Dbase->InitializeConnection(Config::$puller_db_config); | |
| if(is_null($this->Dbase->dbcon)) { | |
| $this->sendEmail("Couln't initiate the db connection.", 'DB Error'); | |
| return; | |
| } | |
| $this->Dbase->InitializeLogs(); | |
| if(!file_exists($this->ROOT.'tmp')){ | |
| mkdir($this->ROOT.'tmp',0777,true); | |
| } | |
| $this->tmpDir = $this->ROOT.'tmp/'.$this->sessionID; | |
| if(!file_exists($this->tmpDir)){ | |
| mkdir($this->tmpDir,0777,true); | |
| } | |
| $this->authCookies = $this->tmpDir."/"."AUTH".mt_rand(); | |
| $this->userAgent = "Mozilla/5.0 (X11; Linux x86_64; rv:26.0) Gecko/20100101 Firefox/26.0"; | |
| $this->authUser(); | |
| } | |
| public function trafficController(){ | |
| foreach($this->formsDetails as $curForm){ | |
| $this->stats = array( | |
| 'submissionCount' => 0, | |
| 'processedSubmissions' => 0, | |
| 'submissionsNotDownloaded' => 0, | |
| 'submissionsWithErrors' => 0, | |
| 'processedEvents' => 0, | |
| 'submissionsAlreadyProcessed' => 0 | |
| ); | |
| $this->curInstanceID = $curForm['instanceId']; | |
| $this->curTopElement = $curForm['topElement']; | |
| $this->Dbase->CreateLogEntry("Processing the form '{$this->curInstanceID}'", 'info'); | |
| $this->additionalMessage .= "<br /><br /><b>Processing the form '{$this->curInstanceID}'</b><br />"; | |
| while($this->processXMLForm()){ | |
| $this->Dbase->CreateLogEntry("There was an error while fetching the XForm. Sleeping for 10 secs...", 'info'); | |
| sleep(10); | |
| } | |
| $this->getSubmissions(); | |
| // remove the auth files | |
| $this->additionalMessage .= "<br /><br />Processing Results:<br />Total Submissions: {$this->stats['submissionCount']}<br />" | |
| . "Processed Submissions: {$this->stats['processedSubmissions']}<br />Submissions NOT Downloaded: {$this->stats['submissionsNotDownloaded']}<br />" | |
| . "Already processed submissions: {$this->stats['submissionsAlreadyProcessed']}<br />Processed Events: {$this->stats['processedEvents']}<br />"; | |
| unlink($this->tmpDir); | |
| } | |
| $message = "Finished processing of the new submissions"; | |
| $this->sendEmail($message, 'Finished processing new submissions'); | |
| } | |
| /** | |
| * This fuction authenticates the ODK user on Aggregate | |
| * @return string | |
| */ | |
| private function authUser(){ | |
| if(file_exists($this->authCookies) === FALSE){ | |
| $authURL = Config::$puller_config['odkAuthURL']; | |
| touch($this->authCookies); | |
| chmod($this->authCookies, 0777); | |
| $authCh = curl_init($authURL); | |
| curl_setopt($authCh, CURLOPT_USERAGENT, $this->userAgent); | |
| curl_setopt($authCh, CURLOPT_RETURNTRANSFER, TRUE); | |
| curl_setopt($authCh, CURLOPT_FOLLOWLOCATION, TRUE); | |
| curl_setopt($authCh, CURLOPT_CONNECTTIMEOUT, TRUE); | |
| curl_setopt($authCh, CURLOPT_COOKIEJAR, $this->authCookies); | |
| curl_setopt($authCh, CURLOPT_HTTPAUTH, CURLAUTH_DIGEST); | |
| curl_setopt($authCh, CURLOPT_USERPWD, Config::$puller_config['odkUploadUser'].":".Config::$puller_config['odkUploadPass']); | |
| $result = curl_exec($authCh); | |
| $http_status = curl_getinfo($authCh, CURLINFO_HTTP_CODE); | |
| curl_close($authCh); | |
| if($http_status == 401){//user not authenticated | |
| $this->Dbase->CreateLogEntry("auth status = ".$http_status.". ".Config::$puller_config['odkUploadUser']." is not authenticated to upload forms to aggregate. Exiting", 'info'); | |
| $message = "The user '". Config::$puller_config['odkUploadUser']."' is not authenticated to download data from aggregate. Error:<br /><br />"; | |
| $header = "Error while authenticating the user. The "; | |
| $this->sendEmail($message, $header); | |
| return; | |
| } | |
| $this->Dbase->CreateLogEntry("User was authenticated successfully", 'info'); | |
| } | |
| } | |
| public function getSubmissions(){ | |
| $this->sessionID = session_id(); | |
| if($this->sessionID == NULL || $this->sessionID == "") { | |
| $this->sessionID = round(microtime(true) * 1000); | |
| } | |
| if(!file_exists($this->ROOT.'tmp')){ | |
| mkdir($this->ROOT.'tmp',0777,true); | |
| } | |
| $this->tmpDir = $this->ROOT.'tmp/'.$this->sessionID; | |
| if(!file_exists($this->tmpDir)){ | |
| mkdir($this->tmpDir,0777,true); | |
| } | |
| //2. get submission list | |
| $this->lastCursor = ""; | |
| $this->submissionIDs = array(); | |
| $this->getSubmissionList(); | |
| $submissionsCount = count($this->submissionIDs); | |
| $this->Dbase->CreateLogEntry("Found $submissionsCount submissions for the form {$this->curInstanceID}", 'info'); | |
| //3. download submission data | |
| $this->json = ""; | |
| $this->submissionXObjects = array(); | |
| for($i = 0; $i < $submissionsCount; $i++){ | |
| // if($this->stats['processedSubmissions'] > 0) return; | |
| $currSubmissionID = $this->submissionIDs[$i]; | |
| // check whether this submission has been processed before | |
| $query = 'select uuid from '.$this->np_db.'.processed_submissions where uuid = :instance_id'; | |
| $pk = $this->Dbase->ExecuteQuery($query, array('instance_id' => $currSubmissionID)); | |
| if($pk == 1){ | |
| return; | |
| } | |
| else if(count($pk) != 0 ){ | |
| $this->stats['submissionsAlreadyProcessed']++; | |
| continue; | |
| } | |
| $data = $this->downloadSubmissionData($currSubmissionID); | |
| if(is_array($data)){ | |
| $this->processSingleSubmission($data); | |
| } | |
| } | |
| } | |
| /** | |
| * This function fetches the list of submissions for the set ODK form on Aggregate | |
| */ | |
| private function getSubmissionList(){ | |
| $numSubmissionEntries = 100; | |
| $listURL = "http://azizi.ilri.cgiar.org/aggregate/view/submissionList?formId=".$this->curInstanceID; | |
| if(strlen($this->lastCursor) > 0) $listURL = $listURL . "&cursor=" . urlencode ($this->lastCursor); | |
| $ch = curl_init($listURL); | |
| curl_setopt($ch, CURLOPT_USERAGENT, $this->userAgent); | |
| curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); | |
| curl_setopt($ch, CURLOPT_FOLLOWLOCATION, TRUE); | |
| curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, TRUE); | |
| curl_setopt($ch, CURLOPT_COOKIEFILE, $this->authCookies); | |
| $curlResult = curl_exec($ch); | |
| $http_status = curl_getinfo($ch, CURLINFO_HTTP_CODE); | |
| curl_close($ch); | |
| if($http_status == 200){ | |
| $listXML = $curlResult; | |
| $listXObject = simplexml_load_string($listXML); | |
| if(is_object($listXObject->idList)){ | |
| if(isset($listXObject->idList->id[0])){ | |
| //add all submission ids to submissionIDs array | |
| foreach ($listXObject->idList->id as $currSubmission){ | |
| $this->submissionIDs[] = "$currSubmission";//convert simplexml array values into normal string | |
| } | |
| if($this->lastCursor != $listXObject->resumptionCursor){ | |
| $this->lastCursor = $listXObject->resumptionCursor; | |
| $this->getSubmissionList(); | |
| } | |
| else{ | |
| $this->Dbase->CreateLogEntry("It appears like all the submission IDs have been obtained from aggregate server", 'info'); | |
| } | |
| } | |
| else{ | |
| $this->additionalMessage .= "No submissions gotten for the form '".$this->curInstanceID."'<br />"; | |
| } | |
| } | |
| else | |
| $this->Dbase->CreateLogEntry("Unable to get idList from xml. XML object looks like this ". print_r($listXObject, true) . $curlResult, 'fatal'); | |
| } | |
| else{ | |
| $this->additionalMessage .= "There was a problem getting submission list from server. http status = ".$http_status. " & result = ".$curlResult ."<br />"; | |
| } | |
| } | |
| /** | |
| * Downloads the submission data given a submission id | |
| * | |
| * @param string $submissionID The uuid of the submission to download the data | |
| * @return array Returns an array with the submission data | |
| */ | |
| private function downloadSubmissionData($submissionID){ | |
| $formId = $this->curInstanceID."[@version=null]/".$this->curTopElement."[@key=".$submissionID."]"; | |
| $downloadURL = "http://azizi.ilri.cgiar.org/aggregate/view/downloadSubmission?formId=".urlencode($formId); | |
| $ch = curl_init($downloadURL); | |
| curl_setopt($ch, CURLOPT_USERAGENT, $this->userAgent); | |
| curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); | |
| curl_setopt($ch, CURLOPT_FOLLOWLOCATION, TRUE); | |
| curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, TRUE); | |
| curl_setopt($ch, CURLOPT_COOKIEFILE, $this->authCookies); | |
| $curlResult = curl_exec($ch); | |
| $http_status = curl_getinfo($ch, CURLINFO_HTTP_CODE); | |
| curl_close($ch); | |
| if($http_status == 200){ | |
| $submissionXObject = simplexml_load_string($curlResult); | |
| $submissionChildren = (array) $submissionXObject->data->children(); | |
| $sub = $this->processXMLObject($submissionChildren[$this->curTopElement]); | |
| return $sub; | |
| } | |
| else{ | |
| $this->additionalMessage .= "Unable to get data for submission with id = $submissionID, form id = ".$this->curInstanceID." http status = $http_status & result = $curlResult\nDownload URL: $downloadURL<br />"; | |
| $this->stats['submissionsNotDownloaded']++; | |
| } | |
| } | |
| /** | |
| * Processes a single submission and saves the data to the database | |
| * | |
| * @param array $submission An array with the submission data | |
| * @return type | |
| */ | |
| private function processSingleSubmission($submission){ | |
| $attr = $submission['@attributes']; | |
| $this->Dbase->CreateLogEntry("Processing the submission with the instance '{$attr['instanceID']}'", 'info'); | |
| // $this->saveData($submission, 'main_sheet', NULL); | |
| $farmerQ = 'select id from '.$this->np_db.'.farmer where hh_id = :hh_id'; | |
| $farmerId = $this->Dbase->ExecuteQuery($farmerQ, array('hh_id' => $submission['s1q9_calculated_hh'])); | |
| if($farmerId == 1){ | |
| return; | |
| } | |
| elseif(count($farmerId) == 0){ | |
| $this->additionalMessage .= "FATAL: Could not find the household <b>'{$submission['s1q9_calculated_hh']}'</b> in the database<br />"; | |
| return; | |
| } | |
| $farmerId = $farmerId[0]['id']; | |
| // get the farmer cows | |
| $cowsQ = 'select id, ear_tag_number from '.$this->np_db.'.cow where farmer_id = :farmer_id'; | |
| $cowIds = $this->Dbase->ExecuteQuery($cowsQ, array('farmer_id' => $farmerId)); | |
| if($cowIds == 1){ | |
| return; | |
| } | |
| elseif(count($cowIds) == 0){ | |
| $this->additionalMessage .= "FATAL: The household <b>'{$submission['s1q9_calculated_hh']}'</b> has no defined cows<br />"; | |
| } | |
| $cow_ids = array(); | |
| $cow_ids_assoc = array(); | |
| foreach($cowIds as $c){ | |
| $cow_ids[] = $c['id']; | |
| $cow_ids_assoc[$c['ear_tag_number']] = $c['id']; | |
| } | |
| // save the milk production and concentrates | |
| if($submission['s2_gen_details_count'] == 1) $data = array($submission['s2_gen_details']); | |
| else if($submission['s2_gen_details_count'] > 1) $data = $submission['s2_gen_details']; | |
| $cdate = date('Y-m-d', strtotime('-1 day', strtotime($submission['s0q3_survey_date']))); | |
| $this->Dbase->StartTrans(); | |
| $ret = $this->saveMilkAndConcentrates($data, $cdate); | |
| if($ret){ | |
| $this->Dbase->RollBackTrans(); | |
| $this->stats['submissionsWithErrors']++; | |
| return; | |
| } | |
| $ret = $this->saveMilkUsage($submission['s4_milk_usage'], $cdate, $farmerId); | |
| if($ret){ | |
| $this->Dbase->RollBackTrans(); | |
| $this->stats['submissionsWithErrors']++; | |
| return; | |
| } | |
| $ret = $this->saveWaterRecs($submission['s5q3_feed_water'], $cdate, $cow_ids); | |
| if($ret){ | |
| $this->Dbase->RollBackTrans(); | |
| $this->stats['submissionsWithErrors']++; | |
| return; | |
| } | |
| $ret = $this->saveFeedingRecs($submission['s5q1_feed_type'], $cdate, $cow_ids); | |
| if($ret){ | |
| $this->Dbase->RollBackTrans(); | |
| $this->stats['submissionsWithErrors']++; | |
| return; | |
| } | |
| if($submission['s3q1_animal_events'] != 'none'){ | |
| if($submission['s3_event_count'] == 1) $events = array($submission['s3_event']); | |
| else $events = $submission['s3_event']; | |
| $ret = $this->saveEvents($events, $cdate, $cow_ids_assoc, $farmerId); | |
| if($ret){ | |
| $this->Dbase->RollBackTrans(); | |
| $this->stats['submissionsWithErrors']++; | |
| return; | |
| } | |
| } | |
| $processedSubQ = 'insert into '.$this->np_db.'.processed_submissions(uuid) values(:uuid)'; | |
| $cowIds = $this->Dbase->ExecuteQuery($processedSubQ, array('uuid' => $attr['instanceID'])); | |
| if($cowIds == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) return; | |
| } | |
| $this->stats['processedSubmissions']++; | |
| $this->Dbase->CommitTrans(); | |
| } | |
| private function processXMLObject($object){ | |
| $thisSubmission = array(); | |
| $obj_array = (array) $object; | |
| if(count($obj_array) == 0) return NULL; | |
| foreach($obj_array as $sub_name => $sub){ | |
| if(is_array($sub)){ | |
| if(count($sub) == 0) $thisSubmission[$sub_name] = $sub; | |
| else{ | |
| $sub = $this->processXMLObject($sub); | |
| $thisSubmission[$sub_name] = $sub; | |
| } | |
| } | |
| else if(is_string($sub)) $thisSubmission[$sub_name] = $sub; | |
| else if(is_a($sub, "SimpleXMLElement")){ | |
| $sub = $this->processXMLObject($sub); | |
| $thisSubmission[$sub_name] = $sub; | |
| } | |
| else{ | |
| } | |
| } | |
| return $thisSubmission; | |
| } | |
| /** | |
| * Atempts to create a db structure from the data as supplied. The function is not yet finished | |
| * | |
| * @param array $data An array with the data as processed from aggregate | |
| * @param string $table_name The name of the table where the data should be saved | |
| * @param string $prev_table_name The parent/previous table in case where we have a repeating group | |
| * @return | |
| * @deprecated since version 1 | |
| */ | |
| private function saveData($data, $table_name, $prev_table_name){ | |
| $cols = array(); | |
| $coldata = array(); | |
| foreach($data as $cname => $cdata){ | |
| if($cname == '@attributes') continue; | |
| if(is_array($cdata)){ | |
| if(isset($cdata[0]) && is_array($cdata[0])){ | |
| foreach($cdata as $index => $ccdata){ | |
| $this->saveData($ccdata, $cname, NULL); | |
| } | |
| continue; | |
| } | |
| $this->saveData($cdata, $cname, $table_name); | |
| } | |
| else{ | |
| $matches = preg_grep("/$cname/", $this->selectManyItems); | |
| if(count($matches) != 0) echo 'Matches ==> '. print_r($matches, true); | |
| if(!is_null($cdata)){ | |
| if(preg_match('/gps/', $cname)){ | |
| $full_gps = $this->processGPS($cdata); | |
| if(is_array($full_gps)){ | |
| foreach($full_gps as $cname => $cdata){ | |
| $cols[] = $cname; | |
| $coldata[] = $cdata; | |
| } | |
| continue; | |
| } | |
| } | |
| else if(count($matches) != 0){ | |
| echo "We have a multi select item"; | |
| // we have a select many instance, so break up the selection and append them to the name | |
| $parts = explode(' ', $cdata); | |
| echo print_r($parts, true); | |
| foreach($parts as $part){ | |
| $cols[] = "{$cname}_{$part}"; | |
| $coldata[] = 1; | |
| } | |
| continue; | |
| } | |
| $cols[] = $cname; | |
| $coldata[] = $cdata; | |
| } | |
| } | |
| } | |
| if(count($cols) == 0 ) return; | |
| $cols_csv = implode(', ', $cols); | |
| $coldata_csv = "'". implode("', '", $coldata) ."'"; | |
| $query = "insert into $table_name($cols_csv) values($coldata_csv)"; | |
| } | |
| /** | |
| * Split the GPS components and assign them to their respective placeholders | |
| * | |
| * @param string $fullGPS The full gps as received | |
| * @return mixed Returns the split up GPS parts in case we have a valid GPS else returns NULL | |
| */ | |
| private function processGPS($fullGPS){ | |
| $parts = explode(' ', $fullGPS); | |
| if(count($parts) == 4) return array($this->gps_cols['lat'] => $parts[0], $this->gps_cols['lon'] => $parts[1], $this->gps_cols['alt'] => number_format($parts[2],1,''), $this->gps_cols['acc'] => number_format($parts[3],0,'')); | |
| else return NULL; | |
| } | |
| /** | |
| * Fetches the XForm from the server and fetches all the nodes which have select many option | |
| * @return type | |
| */ | |
| private function processXMLForm(){ | |
| $formURL = "http://azizi.ilri.cgiar.org/aggregate/www/formXml?readable=false&formId=".$this->curInstanceID; | |
| if(strlen($this->lastCursor) > 0) $formURL = $formURL . "&cursor=" . urlencode ($this->lastCursor); | |
| $ch = curl_init($formURL); | |
| curl_setopt($ch, CURLOPT_USERAGENT, $this->userAgent); | |
| curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); | |
| curl_setopt($ch, CURLOPT_FOLLOWLOCATION, TRUE); | |
| curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, TRUE); | |
| curl_setopt($ch, CURLOPT_COOKIEFILE, $this->authCookies); | |
| $curlResult = curl_exec($ch); | |
| $http_status = curl_getinfo($ch, CURLINFO_HTTP_CODE); | |
| curl_close($ch); | |
| if($http_status != 200){ | |
| $this->additionalMessage .= "Unable to get the xml for the form with the instance = ". $this->curInstanceID ." http status = $http_status & result = $curlResultForm URL: $formURL <br />"; | |
| return true; | |
| } | |
| // get all the select many selections | |
| preg_match_all('/<select ref="(.+)"/', $curlResult, $matches); | |
| $this->selectManyItems = $matches[1]; | |
| return false; | |
| } | |
| /** | |
| * Saves the milk and concentrates data | |
| * | |
| * @param array $data An array with the data to be saved | |
| * @param string $cdate The date for the records | |
| * @return boolean Returns false if all is ok, else returns true | |
| */ | |
| private function saveMilkAndConcentrates($data, $cdate){ | |
| $cowQ = 'select id from '.$this->np_db.'.cow where ear_tag_number = :tag'; | |
| $query = 'insert into '.$this->np_db.'.milk_production(cow_id, time, quantity, date_added, date, quantity_type, app_used, comments) values(:cow_id, :time, :quantity, :date_added, :date, :quantity_type, :app_used, :comments)'; | |
| foreach($data as $dt){ | |
| if($dt['s2q1_cow_id'] == ''){ | |
| $this->Dbase->CreateLogEntry('Empty record from the submission.', 'info'); | |
| continue; | |
| } | |
| $cow = $this->Dbase->ExecuteQuery($cowQ, array('tag' => $dt['s2q1_cow_id'])); | |
| if($cow == 1){ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| elseif(count($cow) == 0){ | |
| $this->additionalMessage .= "FATAL: The cow <b>'{$dt['s2q1_cow_id']}'</b> was not found in the database.<br />"; | |
| return true; | |
| } | |
| $coldata = array( | |
| 'cow_id' => $cow[0]['id'], 'time' => 'Morning', 'quantity' => $dt['s2q3_am_y_milk'], 'date_added' => date('Y-m-d H:i:s'), | |
| 'date' => $cdate, 'quantity_type' => 'Litres', 'app_used' => 'ODK', 'comments' => 'Auto processed by the puller' | |
| ); | |
| // save Morning milk | |
| if((float)$dt['s2q3_am_y_milk'] == 0){ | |
| $this->Dbase->CreateLogEntry("ERROR: {$dt['s2q1_cow_id']} morning record is 0. Skipping it."); | |
| } | |
| else{ | |
| $res = $this->Dbase->ExecuteQuery($query, $coldata); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| } | |
| // save evening milk | |
| if((float)$dt['s2q4_pm_y_milk'] == 0){ | |
| $this->Dbase->CreateLogEntry("ERROR: {$dt['s2q1_cow_id']} evening record is 0. Skipping it."); | |
| } | |
| else{ | |
| $coldata['time'] = 'Evening'; | |
| $coldata['quantity'] = $dt['s2q4_pm_y_milk']; | |
| $res = $this->Dbase->ExecuteQuery($query, $coldata); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| } | |
| // save the concentrates | |
| $concQ = 'insert into '.$this->np_db.'.feeding(cow_id, date_added, date, food, quantity, app_used) values(:cow_id, :date_added, :date, :food, :quantity, :app_used)'; | |
| if((float)$dt['s2q5_y_concent'] == 0){ | |
| $this->Dbase->CreateLogEntry("ERROR: {$det['s3q6_cow_id']} concentrate record is 0. Skipping it."); | |
| } | |
| else{ | |
| $conc_data = array( | |
| 'cow_id' => $cow[0]['id'], 'date_added' => date('Y-m-d H:i:s'), 'date' => $cdate, | |
| 'food' => 'Concentrates', 'quantity' => $dt['s2q5_y_concent'], 'app_used' => 'ODK' | |
| ); | |
| $res = $this->Dbase->ExecuteQuery($concQ, $conc_data); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($conc_data, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| } | |
| } | |
| return false; | |
| } | |
| /** | |
| * Saves the milk usage in the household | |
| * | |
| * @param array $data The data as an array to be saved in the database | |
| * @param string $cdate The date of collection | |
| * @param integer $farmerId The farmer id of this household | |
| * @return boolean | |
| */ | |
| private function saveMilkUsage($data, $cdate, $farmerId){ | |
| $useQ = 'insert into '.$this->np_db.'.milk_usage(farmer_id, usage_type, date_added, date, quantity, app_used) values(:farmer_id, :usage_type, :date_added, :date, :quantity, :app_used)'; | |
| $coldata_c = array('farmer_id' => $farmerId, 'usage_type' => 'Consumed', 'date_added' => date('Y-m-d H:i:s'), 'date' => $cdate, 'quantity' => $data['s4q2_milk_used'], 'app_used' => 'ODK'); | |
| $coldata_s = array('farmer_id' => $farmerId, 'usage_type' => 'Sale', 'date_added' => date('Y-m-d H:i:s'), 'date' => $cdate, 'quantity' => $data['s4q3_milk_sold'], 'app_used' => 'ODK'); | |
| // save the consumption | |
| $res = $this->Dbase->ExecuteQuery($useQ, $coldata_c); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata_c, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| // save the sale | |
| $res = $this->Dbase->ExecuteQuery($useQ, $coldata_s); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata_s, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| return false; | |
| } | |
| /** | |
| * Save watering of animals. It assume that all the animals are watered the same way | |
| * | |
| * @param string $water_regime The watering regime that was used | |
| * @param string $cdate The date of data collection | |
| * @param array $cow_ids An array with the farmer animals | |
| * @return boolean Returns false if all is ok, else it returns true | |
| */ | |
| private function saveWaterRecs($water_regime, $cdate, $cow_ids){ | |
| // echo "$water_regime<br />"; | |
| $useQ = 'insert into '.$this->np_db.'.water(cow_id, date_added, date, admin_type, app_used) values(:cow_id, :date_added, :date, :admin_type, :app_used)'; | |
| if($water_regime == 'ad_libitum') $admin = 'Throughout the day'; | |
| else if($water_regime == 'Gt_2') $admin = 'Twice or more'; | |
| else if($water_regime == 'once') $admin = 'Once'; | |
| else if($water_regime == 'not_provided') $admin = 'Not Provided'; | |
| else{ | |
| } | |
| foreach($cow_ids as $c_id){ | |
| $coldata = array('cow_id' => $c_id, 'date_added' => date('Y-m-d H:i:s'), 'date' => $cdate, 'admin_type' => $admin, 'app_used' => 'ODK'); | |
| $res = $this->Dbase->ExecuteQuery($useQ, $coldata); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| } | |
| return false; | |
| } | |
| /** | |
| * Saves the feeding records. Assumes that the food type was given to all the animals | |
| * | |
| * @param string $feeds A space separated string with the feeds given to the animals | |
| * @param string $cdate The collection date | |
| * @param array $cow_ids An array with the cow ids for this farmer | |
| * @return boolean Returns false, if all is ok | |
| */ | |
| private function saveFeedingRecs($feeds, $cdate, $cow_ids){ | |
| $all_feeds = explode(' ', $feeds); | |
| $all_feeds_count = count($all_feeds); | |
| $cow_count = count($cow_ids); | |
| $foodQ = 'insert into '.$this->np_db.'.feeding(cow_id, date_added, date, food, app_used) values(:cow_id, :date_added, :date, :food, :app_used)'; | |
| for($i = 0; $i < $all_feeds_count; $i++){ | |
| $food = $all_feeds[$i]; | |
| if($food == 'hay') $food_c = 'Hay/Dry crop residue'; | |
| elseif($food == 'legumes') $food_c = 'Legumes'; | |
| elseif($food == 'mineral_lick') $food_c = 'Mineral licks'; | |
| elseif($food == 'napier') $food_c = 'Napier/Green crop residue'; | |
| elseif($food == 'nat_grass') $food_c = 'Natural grass'; | |
| elseif($food == 'silage') $food_c = 'Silage'; | |
| elseif($food == 'concentrates') continue; | |
| for($j = 0; $j < $cow_count; $j++){ | |
| $c_id = $cow_ids[$j]; | |
| $coldata = array('cow_id' => $c_id, 'date_added' => date('Y-m-d H:i:s'), 'date' => $cdate, 'food' => $food_c, 'app_used' => 'ODK'); | |
| $res = $this->Dbase->ExecuteQuery($foodQ, $coldata); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| } | |
| } | |
| return false; | |
| } | |
| /** | |
| * Saves the events for each submission | |
| * | |
| * @param array $events An array of the events' details to save | |
| * @param string $cdate The date of the event | |
| * @param array $cows An array with the cow ear tags and their corresponding ids | |
| * @param integer $farmer_id The id of the farmer | |
| * @return boolean Returns true if there was an error, else returns false | |
| */ | |
| private function saveEvents($events, $cdate, $cows, $farmer_id){ | |
| $eventQ = 'insert into '.$this->np_db.'.cow_event(%s) values(%s)'; | |
| $t_cols = array('cow_id', 'event_id', 'event_date', 'date_added', 'remarks', 'app_used'); | |
| $t_col_phs = array(':cow_id', ':event_id', ':event_date', ':date_added', ':remarks', ':app_used'); | |
| foreach($events as $event){ | |
| if($event['s3_details_count'] == 0) continue; | |
| // echo print_r($event, true); | |
| $eventIdQ = 'select id from '.$this->np_db.'.event where name = :event_name'; | |
| $cowQ = 'select id from '.$this->np_db.'.cow where ear_tag_number = :tag'; | |
| if($this->eventDetails[$event['s3q2_curr_event']]['id'] == NULL){ | |
| $eventId = $this->Dbase->ExecuteQuery($eventIdQ, array('event_name' => $this->eventDetails[$event['s3q2_curr_event']]['db_name'])); | |
| if($eventId == 1){ | |
| $this->additionalMessage .= $this->Dbase->lastError . "<br />"; | |
| return true; | |
| } | |
| $this->eventDetails[$event['s3q2_curr_event']]['id'] = $eventId[0]['id']; | |
| } | |
| // now lets save the event | |
| for($i = 0; $i < $event['s3_details_count']; $i++){ | |
| $det = $event['s3_details'][$i]; | |
| if($det['s3q6_cow_id'] == '') continue; | |
| // ensure that we have the id of the cow | |
| if(!isset($cows[$det['s3q6_cow_id']])){ | |
| // interesting, this cow does not belong to the list of farmers cows' | |
| $this->additionalMessage .= "FATAL: <b>The cow {$det['s3q6_cow_id']} might not be registered with the right farmer</b><br />"; | |
| $cow = $this->Dbase->ExecuteQuery($cowQ, array('tag' => $det['s3q6_cow_id'])); | |
| if($cow == 1){ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| elseif(count($cow) == 0){ | |
| $this->additionalMessage .= "FATAL: The cow <b>'{$det['s3q6_cow_id']}'</b> was not found in the database.<br />"; | |
| return true; | |
| } | |
| $cows[$det['s3q6_cow_id']] = $cow[0]['id']; | |
| } | |
| $t_coldata = array( | |
| 'cow_id' => $cows[$det['s3q6_cow_id']], | |
| 'event_id' => $this->eventDetails[$event['s3q2_curr_event']]['id'], | |
| 'event_date' => $cdate, 'app_used' => 'ODK', | |
| 'date_added' => date('Y-m-d H:i:s'), | |
| 'remarks' => 'Auto processed by the puller' | |
| ); | |
| $coldata = $t_coldata; | |
| $col_phs = $t_col_phs; | |
| $cols = $t_cols; | |
| $this->stats['processedEvents']++; | |
| if($event['s3q2_curr_event'] == 'dry'){ | |
| // add the event details | |
| $eventQuery = sprintf($eventQ, implode(', ', $cols), implode(', ', $col_phs)); | |
| $res = $this->Dbase->ExecuteQuery($eventQuery, $coldata); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| // if we have a drying event, we most def have the last milk before drying | |
| $milkQ = 'insert into '.$this->np_db.'.milk_production(cow_id, time, quantity, date_added, date, quantity_type, app_used, comments) ' | |
| . 'values(:cow_id, :time, :quantity, :date_added, :date, :quantity_type, :app_used, :comments)'; | |
| $m_coldata = array( | |
| 'cow_id' => $cows[$det['s3q6_cow_id']], 'time' => 'Morning', 'quantity' => $det['s3q15_calved_last_milk'], 'date_added' => date('Y-m-d H:i:s'), | |
| 'date' => $det['s3q14_calved_when_milk'], 'quantity_type' => 'Litres', 'app_used' => 'ODK', 'comments' => 'Last milk before dry off' | |
| ); | |
| // save Morning milk | |
| if((float)$det['s3q15_calved_last_milk'] == 0){ | |
| $this->Dbase->CreateLogEntry("ERROR: {$det['s3q6_cow_id']} morning record is 0. Skipping it."); | |
| } | |
| else{ | |
| $res = $this->Dbase->ExecuteQuery($milkQ, $m_coldata); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($m_coldata, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| } | |
| } | |
| else if($event['s3q2_curr_event'] == 'calved_down'){ | |
| // the event date is the date of calving | |
| $coldata['event_date'] = $event['s3q13_calved_when']; | |
| // add the type of calving | |
| $cols[] = 'birth_type'; | |
| $col_phs[] = ':birth_type'; | |
| $coldata['birth_type'] = $this->eventDetails[$event['s3q12_calved_type']]; | |
| $eventQuery = sprintf($eventQ, implode(', ', $cols), implode(', ', $col_phs)); | |
| $res = $this->Dbase->ExecuteQuery($eventQuery, $coldata); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| } | |
| else if($event['s3q2_curr_event'] == 'insemination'){ | |
| $coldata['event_id'] = $this->eventDetails[$event['s3q2_curr_event']]['id']; | |
| if($det['s3q7_insem_serv_source'] != ''){ | |
| $cols[] = 'service_source'; | |
| $col_phs[] = ':service_source'; | |
| $coldata['service_source'] = $det['s3q7_insem_serv_source']; | |
| } | |
| if($det['s3q8_insem_when'] != ''){ | |
| $coldata['event_date'] = $det['s3q8_insem_when']; | |
| } | |
| if($det['s3q9_insem_sire'] != ''){ | |
| $cols[] = 'bull_name'; | |
| $col_phs[] = ':bull_name'; | |
| $coldata['bull_name'] = $det['s3q9_insem_sire']; | |
| } | |
| if($det['s3q10_insem_cost'] != ''){ | |
| $cols[] = 'cost'; | |
| $col_phs[] = ':cost'; | |
| $coldata['cost'] = $det['s3q10_insem_cost']; | |
| } | |
| if($det['s3q11_insem_result'] != ''){ | |
| $coldata['remarks'] = $det['s3q11_insem_result']; | |
| } | |
| $eventQuery = sprintf($eventQ, implode(', ', $cols), implode(', ', $col_phs)); | |
| $res = $this->Dbase->ExecuteQuery($eventQuery, $coldata); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| } | |
| else if($event['s3q2_curr_event'] == 'death'){ | |
| // we need to record a death and also update the cow records | |
| $eventQuery = sprintf($eventQ, implode(', ', $cols), implode(', ', $col_phs)); | |
| $res = $this->Dbase->ExecuteQuery($eventQuery, $coldata); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| $deathQ = 'update '.$this->np_db.'.cow set farmer_id = :zero, old_farmer_id = :farmer_id where id = :id'; | |
| $res = $this->Dbase->ExecuteQuery($deathQ, array('zero' => 0, 'farmer_id' => $farmer_id, 'id' => $cows[$det['s3q6_cow_id']])); | |
| if($res == 1){ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| else{ | |
| // just add the event since all the special events have been taken care of | |
| $eventQuery = sprintf($eventQ, implode(', ', $cols), implode(', ', $col_phs)); | |
| $res = $this->Dbase->ExecuteQuery($eventQuery, $coldata); | |
| if($res == 1){ | |
| if($this->Dbase->lastResult[1] == 1062) $this->additionalMessage .= 'Duplicate Entry: '. print_r($coldata, true) ."<br />"; | |
| else{ | |
| $this->additionalMessage .= $this->Dbase->lastError ."<br />"; | |
| return true; | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| /** | |
| * Sends an email to the administrator | |
| * @param string $message The content of the email | |
| */ | |
| private function sendEmail($message, $header){ | |
| $settings = parse_ini_file(OPTIONS_MODULE_EMAIL_CONFIG_FILE); | |
| $addressedTo = 'wangoru.kihara@badili.co.ke'; | |
| if($this->additionalMessage != ''){ | |
| $message .= "<br /><br />Additional Message:<br />". $this->additionalMessage; | |
| } | |
| $this->Dbase->CreateLogEntry("sending an email to $addressedTo with the daily digest\n\n$message", 'info'); | |
| $command = "echo '$message' | {$settings['mutt_bin']} -e 'set content_type=text/html' -s '$header' -- $addressedTo"; | |
| shell_exec($command); | |
| } | |
| } | |
| $ODKSubmissions = new mergeNewODKSubmissions(); | |
| $ODKSubmissions->trafficController(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment