function addRemark($primkey, $remark, $urid)
{
global $db;
$query = 'replace into ' . Config::dbSurvey() . '_remarks (primkey, remark, urid, ts) values (\'' . prepareDatabaseString($primkey) . '\', aes_encrypt(\'' . prepareDatabaseString($remark) . '\',\'' . Config::smsRemarkKey() . '\'), ' . $urid . ', \'' . date('Y-m-d H:i:s') . '\')';
// echo '<br/><br/><br/>' . $query;
$db->executeQuery($query);
if (isset($this->remarksArray[$primkey])) {
unset($this->remarksArray[primkey]);
//remove from array so getremarks re-reads it.
}
return $query;
}
function getRespondentsByUserSearch($user, $searchterm, $uridcheck = true)
{
global $db;
$respondents = array();
$test = 'test = 0';
if ($user->isTestMode()) {
$test = 'test = 1';
}
//$userstr = ' 1 = 1 ';
if ($uridcheck) {
$test .= ' AND t1.urid = ' . prepareDatabaseString($user->getUrid());
}
//search through respondent table
$query = 'select *, aes_decrypt(firstname, \'' . Config::smsPersonalInfoKey() . '\') as firstname_dec,
aes_decrypt(lastname, \'' . Config::smsPersonalInfoKey() . '\') as lastname_dec
from ' . Config::dbSurvey() . '_respondents as t1
where ' . $test . ' and (
t1.primkey like "%' . prepareDatabaseString($searchterm) . '%" or
aes_decrypt(firstname, \'' . Config::smsPersonalInfoKey() . '\') like "%' . prepareDatabaseString($searchterm) . '%" or
aes_decrypt(lastname, \'' . Config::smsPersonalInfoKey() . '\') like "%' . prepareDatabaseString($searchterm) . '%"
)';
$result = $db->selectQuery($query);
while ($row = $db->getRow($result)) {
$respondents[$row['primkey']] = new Respondent($row);
}
//search through remarks
$query = 'select *, aes_decrypt(firstname, \'' . Config::smsPersonalInfoKey() . '\') as firstname_dec,
aes_decrypt(lastname, \'' . Config::smsPersonalInfoKey() . '\') as lastname_dec
from ' . Config::dbSurvey() . '_remarks as t1
left join ' . Config::dbSurvey() . '_respondents as t2
on t1.primkey = t2.primkey
where t2.' . $test . ' and (
aes_decrypt(remark, \'' . Config::smsRemarkKey() . '\') like "%' . prepareDatabaseString($searchterm) . '%"
)';
// echo '<br/><br/><br/>' . $query;
$result = $db->selectQuery($query);
while ($row = $db->getRow($result)) {
$respondents[$row['primkey']] = new Respondent($row);
}
return $respondents;
}
function showOtherData()
{
global $db;
$type = getFromSessionParams('type');
if ($type != '') {
$filename = '_' . date('YmdHis');
$query = '';
switch ($type) {
case 1:
$filename = 'households' . $filename;
$query = 'select primkey,urid,puid,status,ts from ' . dbConfig::dbSurvey() . '_households where test = 0 order by primkey';
break;
case 2:
$filename = 'respondents' . $filename;
$query = 'select primkey,hhid,urid,status,selected,present,hhhead,finr,famr,permanent,validation,ts from ' . dbConfig::dbSurvey() . '_respondents where test = 0 order by primkey';
break;
case 3:
$filename = 'contacts' . $filename;
$query = 'select primkey,code,contactts,proxy,urid, aes_decrypt(remark, "' . Config::smsContactRemarkKey() . '") as remark, ts from ' . dbConfig::dbSurvey() . '_contacts where primkey not like "999%"';
break;
case 4:
$filename = 'remarks' . $filename;
$query = 'select primkey,urid, aes_decrypt(remark, "' . Config::smsRemarkKey() . '") as remark, ts from ' . dbConfig::dbSurvey() . '_remarks where primkey not like "999%"';
break;
}
if ($query != '') {
$result = $db->selectQuery($query);
createCSV($result, $filename);
}
}
}
$test .= ' AND ( ' . $uridStr . ') ';
}
//search through respondent table
$this->searchHouseholdAndRespondent($respondents, $searchterm, $test, 1);
//respondents
$this->searchHouseholdAndRespondent($respondents, $searchterm, $test, 2);
//households
/* $query = 'select *, aes_decrypt(firstname, "' . Config::smsPersonalInfoKey() . '") as firstname_dec,
aes_decrypt(lastname, "' . Config::smsPersonalInfoKey() . '") as lastname_dec
from ' . Config::dbSurvey() . '_respondents as t1
where ' . $test . ' and (
t1.primkey like "%' . prepareDatabaseString($searchterm) . '%" or
aes_decrypt(firstname, "' . Config::smsPersonalInfoKey() . '") like "%' . prepareDatabaseString($searchterm) . '%" or
aes_decrypt(lastname, "' . Config::smsPersonalInfoKey() . '") like "%' . prepareDatabaseString($searchterm) . '%"
)';
$result = $db->selectQuery($query);
while ($row = $db->getRow($result)) {
