<?php
/*
<LICENSE>
This file is part of AGENCY.
AGENCY is Copyright (c) 2003-2022 by Ken Tanzer and Downtown Emergency
Service Center (DESC).
All rights reserved.
For more information about AGENCY, see http://agency-software.org/
For more information about DESC, see http://www.desc.org/.
AGENCY is free software: you can redistribute it and/or modify
it under the terms of version 3 of the GNU General Public License
as published by the Free Software Foundation.
AGENCY is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with AGENCY. If not, see <http://www.gnu.org/licenses/>.
For additional information, see the README.copyright file that
should be included in this distribution.
</LICENSE>
*/
//------------------------Process() Explained--------------------------//
//
// This takes the user through a series of steps, in the following order:
//
// 1) new - (or invalid) - an input form is displayed
//
// 2) pre-confirm - for client unduplication only, the user is prompted to verify the
// unduplication. Records are displayed side by side.
//
// 3) pre-process - a record is added to the appropriate unduplication table
// for clients only, record is marked as not approved, user is allowed to enter a comment
// this ends the cycle, and the user is returned to
// step (1) discribed above
//
// 4) for staff, and DB client unduplication. Records are displayed side-by-side with
// radio buttons to choose the desired value for the record merge.
//
// 5) process - a) child tables are unduplicated
// b) merged record is inserted into the valid ID
// c) duplicate ID is marked as deleted
//
//---------------------------------------------------------------------//
class Unduplication_Engine {
var $permission = 'admin';
var $control = array('object'=>null, //for now...normally default would be client
'step'=>'new',
'action'=>'regular'); //other actions include DB unduplication & Backlog unduplication (both for client only)
var $unduplication_table = null;
var $special_tables = array( //tables requiring special unduplication action
'user_option'=>'', //FIX ME has a unique constraint, not worth fiddling with at the moment (for clients, this won't suffice)
'log'=>'unduplicate_log',
'charge'=>'', //charges cannot be changed
'staff_password'=>''
);
var $ignore_fields = array();
var $title = null;
var $mesg = array();
var $errors = array();
var $output = null;
function Unduplication_Engine( $object ) {
global $engine;
//outline(green(dump_array(array_merge($this->control,orr($_SESSION['undupControl'],array()),orr($_REQUEST['undupControl'],array())))));
$this->control['object']=$object;
$this->control=array_merge($this->control,orr($_SESSION['undupControl'],array()),orr($_REQUEST['undupControl'],array()));
$this->object = $this->control['object'];
$this->title = ucwords($this->object.' unduplication');
$this->IDs = orr($_REQUEST['undup_ids'],$_SESSION['undup_ids']);
$def = get_def($this->object);
foreach ($def['fields'] as $k=>$pr) {
if (!is_field($def['table_post'],$k)) {
$this->ignore_fields[] = $k;
}
}
if (is_array($this->def['unduplication']['ignore_fields'])) {
foreach ($this->def['unduplication']['ignore-fields'] as $k) {
$this->ignore_fields[] = $k;
}
}
$this->unduplication_table=orr($def['unduplication']['unduplication_table'],'tbl_duplication_'.$this->object);
$this->permission=orr($def['unduplication']['permission'],$this->permission);
//outline(red("Constructed " . dump_array($this)));
}
function process() {
if (!$this->perms()) {
array_push($this->mesg,'You don\'t have proper permissions to unduplicate '.$this->object.' records.');
return;
}
$step=$this->control['step'];
if (! ($this->object and $this->IDs)) {
$step=NULL;
}
switch ($step) {
case 'confirmed':
$this->add_record_table(); //adds a record to the appropriate table
if ($this->object==AG_MAIN_OBJECT_DB) {
$this->output=$this->form();
$this->IDs = null;
break;
}
break;
case 'preprocess':
$_SESSION['undup_ids'] = $this->IDs;
$this->records = $this->get_records();
$this->output .= $this->merge_form(false,'process');
break;
case 'process':
$this->merged_record = $_REQUEST['merged'];
if (!($this->undup_db())) {
array_push($this->errors,'Failed to unduplicate db');
} else {
$this->update_record_table(); //for clients, flags the unduplication as approved
}
$this->control['step'] = 'new';
$this->IDs = null;
$this->output .= $this->form();
break;
case 'confirm':
if ($this->valid_submission()) {
$_SESSION['undup_ids'] = $this->IDs;
$this->records = $this->get_records();
$this->output .= oline(bigger(bold("Confirm this unduplication")));
//$this->output .= $this->merge_form('display','new');
$this->output .= $this->merge_form('display','confirmed');
break;
}
case 'new':
default:
$_SESSION['undup_ids']= null; //reset session vars
$this->output = $this->form();
//$this->output .= $this->show_pending();
}
$_SESSION['undupControl']=$this->control;
}
function display() {
outline(bigger(bold($this->title)),2);
$this->send_errors();
$this->send_mesg();
out($this->output);
}
function perms() {
return has_perm($this->permission,'W');
}
function N_record_form($records,$object,$format) {
//takes an arbitrary number of records, embedded in an array, and returns a form for merging
//offers user the option to merge two records field by field
//returns a form which will return a $merged variable when submitted
//which contains the merged record
global $engine;
$def = get_def($object);
$count = count($records);
$i=1;
if ($count < 1 || !is_array($records)) {
return false;
}
$checked=array();
$out=array();
foreach ($records as $rec_name => $rec) {
foreach ($rec as $key => $value) {
if ($i==1) { //first time around
$checked[$key]=true;
$out[$key]=array();
} else {
$blank_rec=($value===$rec_last[$key]);
}
$check = !is_null($value) ? $checked[$key] : false; //if it hasn't been checked yet, check it
if ($i==$count) { //last time around
$check=$checked[$key]; //check any that have yet to be checked
}
$checked[$key] = $check ? false : $checked[$key];
if ($blank_rec) {
array_push($out[$key],cell('','colspan="2" class="unduplicationDisabledCell"'));
} else {
array_push($out[$key],cell($format ? '' : formradio('merged['.$key.']',$value,$check)).cell(value_generic($value,$def,$key,'view')));
}
}
$i++;
$rec_last=$rec;
}
$rec_titles = array_keys($records);
for ($i=0;$i<$count;$i++) {
$header .= cell(bold($rec_titles[$i]), 'colspan="2"');
}
$output .= row(cell().$header);
foreach ($out as $key => $rec_array) {
//outline("doping $key");
//outline("Rec array: " . webify(dump_array($rec_array)));
$output .= row(cell(label_generic($key,$def,'view')).implode(' ',$rec_array));
}
return $output;
}
function valid_submission() {
$VALID=false;
if (!empty($this->IDs)) {
$valid=true;
foreach ($this->IDs as $which => $id) {
$tmp[]=$id;
if (!is_numeric($id) || $id < 1) {
$valid=false;
array_push($this->errors,'Please enter a numeric ID for the '.ucfirst($this->object). ' you wish to '.$which);
} elseif (!call_user_func('is_'.$this->object,$id)) {
$valid=false;
array_push($this->errors,ucfirst($this->object). ' ID '.$id.' was not found.');
} else {
}
}
if ($tmp[0]==$tmp[1]) { //entered the same ID
array_push($this->errors,'Can\'t unduplicate the same '.$this->object.'!');
$valid=false;
}
$VALID = $valid ? true : false;
return $VALID;
}
array_push($this->errors,'ID numbers are required for the '.$this->object.' you wish to unduplicate.');
return $VALID;
}
function form() {
global $agency_home_url;
$cancel_url = $agency_home_url;
$cancel_button = button_link($cancel_url,'Cancel');
$form = tablestart('','border="1" cellpadding="3"')
. formto()
//. rowrlcell('Valid '.ucfirst($this->object).' ID:',formvartext('undup_ids[keep]',$this->IDs['keep']))
//. rowrlcell('Duplicate '.ucfirst($this->object).' ID:',formvartext('undup_ids[unduplicate]',$this->IDs['unduplicate']))
. rowrlcell('Valid '.ucfirst($this->object).' ID:',formvartext('undup_ids[keep]'))
. rowrlcell('Duplicate '.ucfirst($this->object).' ID:',formvartext('undup_ids[unduplicate]'))
. hiddenvar('undupControl[step]','confirm')
. row(cell(button('Submit'))
. formend()
. cell($cancel_button))
. tableend();
return $form
. $this->list_duplicates()
;
}
function show_pending() {
// FIXME: This funciton replace by list_duplicates below. I can be deleted
$control=array(
'action'=>'list',
'object'=>'duplication',
'list'=>array(
'filter'=>array('COALESCE(approved,false)'=>sql_false()),
'filter'=>array('NULL:approved'=>'dummy'),
),
);
$pending=call_engine($control,'undupControlPending');
return $pending;
}
function list_duplicates() {
$control=array(
'action'=>'list',
'object'=>'duplication',
'list'=>array('filter'=>NULL),
'add_link_show'=>false,
);
return call_engine($control,'undupControlPending');
}
function send_errors() {
foreach ($this->errors as $error) {
outline(red($error));
}
$this->errors=array();
}
function send_mesg() {
foreach ($this->mesg as $message) {
outline($message);
}
$this->mesg=array();
}
function get_records() {
global $engine;
$records = array();
foreach ($this->IDs as $which => $id) {
$filter = array($this->object.'_id'=>$id);
$res = get_generic($filter,'','',$engine[$this->object]);
$records[$which] = array_shift($res);
foreach ($this->ignore_fields as $field) {
unset ($records[$which][$field]);
}
}
return $records;
}
function merge_form( $format=false,$next_step='confirm') {
$keepID = $this->IDs['keep'];
$undupID = $this->IDs['unduplicate'];
return
tablestart() . formto()
. row( cell('photos')
. cell($format ? '' : formradio("undup_photo",$keepID,true)).cell(object_photo($this->object,$keepID))
. cell($format ? '' : formradio("undup_photo",$undupID,false)).cell(object_photo($this->object,$undupID)))
. $this->N_record_form($this->records,$this->object,$format)
. row(cell(button('Submit'),' rowspan="2"'))
. hiddenvar('undupControl[step]',$next_step)
. formend()
//. row(cell(cancel_button($_SERVER['PHP_SELF'].'?undupControl[step]=new','Cancel')))
. row(cell(cancel_button($_SERVER['PHP_SELF'].'?undupControl[step]=new','Cancel')))
. tableend();
}
function get_objects() {
global $AG_ENGINE_TABLES;
$def=get_def('duplication');
$custom=$def['custom'];
// create an array of all tables and staff/client fields
$TABLES = array();
foreach ($AG_ENGINE_TABLES as $obj) {
if ($obj=='referemce') {
// FIXME: ugly hack. There is also a reference hack
// in undup_object
$TABLES[$obj]=array('from_id','to_id');
continue;
}
$def=get_def($obj);
if (in_array($obj,$custom['skip_objects']) //these will be handled elsewhere
|| is_view($def['table_post']) //don't care about views
|| $obj == $this->object ){ //the parent table requires special handling
// if (function_exists($this->special_tables[$obj].'_'.$this->object)) { //this should go somewhere else but it works fine here for now.
// call_user_func($this->special_tables[$obj].'_'.$this->object,$this->IDs['unduplicate'],$this->IDs['keep']);
// }
continue;
}
$table = $def['table_post'];
$TABLES[$obj]=array();
$fields =& orr($def['fields'],array());
foreach ($fields as $field_name => $info) {
//there must surely be a better way to do this via array searching... (not as of PHP 4 or 5...)
$type = $info['data_type'];
if ( ( ($type == $this->object) or ($info['db_info']['is_array'] and ($info['selector_object']==$this->object)))
and (!$info['view_field_only'])
and (!in_array($field_name,$this->ignore_fields))
) {
array_push($TABLES[$obj],$field_name);
}
}
}
return array_filter($TABLES);
}
function undup_db()
{
global $engine, $UID;
$mo_def=get_def(AG_MAIN_OBJECT_DB);
$mo_noun=$mo_def['singular'];
$newid=$this->IDs['keep'];
$oldid=$this->IDs['unduplicate'];
$objects = $this->get_objects();
outline(bigger(bold('Unduplicating '.$this->object.' '
.call_user_func($this->object.'_link',$oldid).' into '
.call_user_func($this->object.'_link',$newid))));
$failed=false;
if (!($res=sql_begin())) {
$failed=true;
array_push($this->errors,'Couldn\' start SQL transaction');
sql_abort();
$this->send_errors();
return false;
}
foreach($objects as $object => $field_list)
{
if ($object=='duplication') {
continue;
}
if (!$this->undup_object($object,$field_list)) {
$failed=true;
array_push($this->errors,'Couldn\'t unduplicate ' . $object);
sql_abort();
$this->send_errors();
return false;
}
$this->send_errors();
$this->send_mesg();
}
//PHOTO STUFF
if ($this->object == AG_MAIN_OBJECT_DB ) {
// $use_old_photo = ($_REQUEST['undup_photo']==$oldid);
$photo_res=object_photo_transfer( AG_MAIN_OBJECT_DB, $newid, $oldid , $use_old_photo);
outline($photo_res
? "Transfered all photos for ".$mo_noun." $oldid to ".$mo_noun." $newid"
: "Failed to transfer photos for ".$mo_noun." $oldid to ".$mo_noun." $newid");
} else {
$use_old_photo = ($_REQUEST['undup_photo']==$oldid);
$photo_res=$this->staff_photo_transfer($newid,$oldid,$use_old_photo);
}
if (!$photo_res) {
array_push($this->errors,'Error processing photos for ' . $this->$object);
sql_abort();
$this->send_errors();
return false;
}
//Mark old ID as deleted
$table = $engine[$this->object]['table_post'];
$del_upds=array(
'sys_log'=> ucfirst($this->object)." is a duplicate of $newid --- '||CURRENT_DATE||' by staff ID: $UID\n",
'deleted_comment'=>'deleted for undplication of ' . $this->object . " $newid",
);
$filter=array($this->object.'_id' => $oldid);
//$result = delete_void_generic($filter,get_def($this->object),'delete',$del_msg,$del_upds);
$result=agency_query(sql_delete($table,$filter,"MARK"));
if ($result) {
array_push($this->mesg, ucfirst($this->object)." ID $oldid succesfully marked as deleted in $table.");
} else {
array_push($this->errors,"Failed to mark ".$this->object." $oldid as deleted in $table.");
sql_abort();
$this-send_errors();
return false;
}
/*
//set sys log in old record to be deleted
$sys_log_mesg = ucfirst($this->object)." is a duplicate of $newid --- '||CURRENT_DATE||' by staff ID: $UID\n";
$sys = sql_query("UPDATE $table
SET sys_log = COALESCE(sys_log,'') || '$sys_log_mesg',
deleted_comment='deleted for unduplication of {$this->object} $newid\n'
WHERE {$this->object}_id = $oldid");
if (!$sys) {
array_push($this->error,"Failed to update sys_log for {$this->object} $oldid");
$this-send_errors();
return false;
}
*/
//set syslog in merged record
$this->merged_record['sys_log'] = $this->merged_record['sys_log']
."Data for a duplicate {$this->object} ($oldid) was merged into this record --- ".dateof('NOW','SQL')." by staff ID: $UID\n";
$def=get_def($this->object);
$this->merged_record[$def['id_field']]=$newid;
//merge records
$result = agency_query(sql_update($table,$this->merged_record,array($this->object.'_id'=>$newid)));
if ($result) {
array_push($this->mesg,"Merged records for {$this->object} ($oldid) and {$this->object} ($newid).");
} else {
array_push($this->errors, "Failed to merge records for {$this->object} ($oldid) and {$this->object} ($newid).");
sql_abort();
$this-send_errors();
return false;
}
if (! ($res=sql_end())) {;
array_push($this->errors, "Failed to commit transaction for {$this->object} ($oldid) and {$this->object} ($newid).");
sql_abort();
$this-send_errors();
return false;
}
$this->send_errors();
$this->send_mesg();
return true;
}
function undup_object($object,$field_list) {
global $UID;
$keepID = $this->IDs['keep'];
$undupID = $this->IDs['unduplicate'];
$def=get_def($object);
$table=$def['table'];
$table_post=$def['table_post'];
$dup_def=get_def('duplication');
$dup_custom=$dup_def['custom'];
$message = 'Unduplicating '.$this->object.' ID from '.$undupID.' to '.$keepID;
foreach ($field_list as $field) {
$pr=$def['fields'][$field];
$get_filter= $pr['db_info']['is_array']
? array('ARRAY_CONTAINS:'.$field=>$undupID)
: array($field=>$undupID)
;
$upd_rec=array(
'changed_by'=>$UID,
'changed_at'=>datetimeof('now','SQL'),
'sys_log'=>$message . ' by staff ID: ' . $UID,
);
if ($pr['db_info']['is_array']) {
$upd_rec["FIELD:$field"]=sprintf('array_replace(%s,%s,%s)',$field,sql_escape_literal($undupID),sql_escape_literal($keepID));
} else {
$upd_rec[$field]=$keepID;
}
$get_table=is_field($table,$field) ? $table : $table_post;
$get_recs=agency_query('SELECT * FROM ' . $get_table,$get_filter);
while ($rec=sql_fetch_assoc($get_recs)) {
$upd_filter=array($def['id_field']=>$rec[$def['id_field']]);
if ($object=="reference") {
// FIXME: hacking because reference can be many tables
// Also see hack in get_objects
$f_clause=($field=="from_id") ? "from_table" : "to_table";
$upd_filter[$f_clause]=$object;
}
if (in_array($object,$def_custom['delete_objects'])) {
$delete_comment='Deleting this record as part of ' . $message;
$res=delete_void_generic($upd_filter,$def,'delete',$act_message,array('deleted_comment'=>$delete_comment));
$up_action='delete';
} elseif (in_array($object,$def_custom['notate_objects'])) {
unset($upd_rec[$field],$upd_rec["FIELD:$field"]);
$upd_rec['sys_log']='Note, this record was not changed, but object referenced by field ' . $field . ' was unduplicated from ' . $undupID . ' to ' . $keepID;
$res = post_generic($upd_rec,$def,$act_message,$upd_filter);
$up_action='notate';
} else {
$res = post_generic($upd_rec,$def,$act_message,$upd_filter);
$up_action='unduplicate';
}
if (!$res) {
$error_msg=sql_error();
$error_msg=div($act_msg,'','class="hiddenDetail"');
array_push($this->errors,"Unable to update $field in $table_post ($up_action) for ID " . $rec[$def['id_field']] . $update_sql . $error_msg);
sql_abort();
return false;
} else {
$ROWS++;
}
}
}
if ($ROWS > 0) {
create_system_log($this->object.' unduplication', //event type
"$message --- changed $ROWS rows in table $table_post");
array_push($this->mesg,indent()."--- changed $ROWS records in table ".bold($table_post));
} else {
array_push($this->mesg,indent().smaller('--- No rows were changed in table '.$table_post));
}
return true;
}
function add_record_table() { //adds a record to the appropriate table
global $UID;
$record=array();
$def=get_def('duplication');
$mo_def=get_def(AG_MAIN_OBJECT_DB);
$mo_noun=$mo_def['singular'];
switch($this->object) {
case AG_MAIN_OBJECT_DB:
// $record['approved'] = sql_false();
break;
case 'staff':
$record['approved'] = sql_true();
$record['FIELD:approved_at'] = 'CURRENT_TIMESTAMP';
$record['approved_by'] = $UID;
}
$record[$this->object.'_id']=$this->IDs['keep'];
$record[$this->object.'_id_old']=$this->IDs['unduplicate'];
$record['added_by']=$record['changed_by']=$UID;
if (!valid_generic($record,$def,$msg,'add')) {
array_push($this->errors,$msg);
return false;
}
//is_ken() and toggle_query_display();
if (!($post_res=post_generic($record,$def,$msg,NULL))) {
array_push($this->errors,'Failed to insert a record in the unduplication table ('.$table.').');
return false;
}
array_push($this->mesg,$def['singular'].' '.$this->IDs['unduplicate'].' flagged for unduplication');
return true;
/*
$table = $this->unduplication_table;
toggle_query_display();
$res = sql_query(sql_insert($table,$record));
outline(sql_insert($table,$record));
outline("Table=$table, rec: " . dump_array($record));
toggle_query_display();
if (!$res) {
array_push($this->errors,'Failed to insert a record in the unduplication table ('.$table.').');
} elseif ($this->object==AG_MAIN_OBJECT_DB) {
array_push($this->mesg,$mo_noun.' '.$this->IDs['unduplicate'].' flagged for unduplication');
}
*/
}
function update_record_table() { //for clients, flags the unduplication as processed
global $UID;
$record=array();
$def=get_def('duplication');
// $mo_def=get_def(AG_MAIN_OBJECT_DB);
// $mo_noun=$mo_def['singular'];
$record['approved'] = sql_true();
$record['FIELD:approved_at'] = 'CURRENT_TIMESTAMP';
$record['approved_by'] = $UID;
$record['changed_by']=$UID;
$upd_filter=array(
$def['custom']['old_id_field']=>$this->IDs['unduplicate'],
$def['custom']['new_id_field']=>$this->IDs['keep'],
'NULL:approved'=>'dummy',
);
if (count( ($d_rec=get_generic($upd_filter,NULL,NULL,$def))) != 1) {
array_push($this->errors,'Failed to find record in the unduplication table ('.$table.'). (' . count($d_rec) . ' records found)');
return false;
}
$d_rec=array_shift($d_rec);
$upd_filter=array($def['id_field']=>$d_rec[$def['id_field']]);
if (!($post_res=post_generic($record,$def,$msg,$upd_filter))) {
array_push($this->errors,'Failed to update record in the unduplication table ('.$table.').');
return false;
}
return true;
}
function staff_photo_transfer($newid,$oldid,$use_old_photo) {
global $AG_STAFF_PHOTO_BY_FILE;
$base = $AG_STAFF_PHOTO_BY_FILE.'/';
$file = 'st_';
$ext = '.jpg';
$old_photo_file = $base.$file.$oldid.$ext;
$cur_photo_file = $base.$file.$newid.$ext;
if ($use_old_photo and is_readable($cur_photo_file)) {
$res0 = rename($old_photo_file,$base.$file.$newid.'.old'.$ext);
if (!$res0) {
array_push($this->errors,'Failed to rename old photo.');
}
$res = rename($base.$file.$oldid.$ext,$base.$file.$newid.$ext);
} elseif (is_readable($old_photo_file)) {
$res = rename($old_photo_file,$base.$file.$newid.'.old'.$ext);
}
if (!$res) {
array_push($this->errors,'Failed to transfer photos.');
}
}
}
?>
