Mustard - Little edit’s helper

array( 'timeout'=>5 ), 'socket'=>array( 'tcp_nodelay'=>true ) ); $context=stream_context_create($contextopts); $langs=array('xx'=>'Local language','ca'=>'Català','en'=>'English','es'=>'Español','fr'=>'Français','it'=>'Italiano'); $exvalsjsarr='null;'.N; $morejs=''; $dbg.='

GET:'.N.print_r($_GET,1).'POST:'.N.print_r($_POST,1).'

'; function check(&$link,&$account,$mode,$table,$lntable,$lncolumn,$id) { if ($mode=='edit' || $mode=='remove') { $res=mysqli_query($link,'SELECT * FROM '.$table.' WHERE ID='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); if (mysqli_num_rows($res)==1) { $row=mysqli_fetch_assoc($res); if ($account['Level']=='guest') { if ($row['AddedBy']!=$account['ID']) muoribene('You can’t modify an entry you didn’t add.',true); if ($table!='Localities') { $que='SELECT * FROM '.$lntable.' LEFT JOIN Instances ON Instances.ID='.$lntable.'.InstID WHERE '.$lntable.'.'.$lncolumn.'='.$id.' AND Instances.ID NOT IN (SELECT ID FROM Instances WHERE GuestID='.$account['ID'].')'; } else { $que='SELECT * FROM Instances WHERE LocalityID='.$id.' AND Instances.ID NOT IN (SELECT ID FROM Instances WHERE GuestID='.$account['ID'].')'; } $rres=mysqli_query($link,$que) or muoribene(__LINE__.': '.mysqli_error($link).'
Query: '.hspech($que),true); if (mysqli_num_rows($rres)>0) muoribene('You can’t modify an entry that
has already been linked by others.',true); } } else { muoribene('There is no «'.$table.'» entry with ID='.$id.N,true); } } elseif ($mode=='add') { if ($account['Level']=='guest') { $res=mysqli_query($link,'SELECT * FROM '.$table.' WHERE AddedBy='.$account['ID']) or muoribene(__LINE__.': '.mysqli_error($link),true); if (mysqli_num_rows($res)>=$account['Max'.$table]) muoribene('You can’t add more «'.$table.'»
entries ('.$account['Max'.$table].' max).',true); } } } function edaddrem($id,$table,$column,$dispselarr,$lntable,$lncolumn,$searchid,$dispselid,$recbid) { global $link, $morejs, $account, $dlang; check($link,$account,$_POST['m'],$table,$lntable,$lncolumn,$id); if ($_POST['m']=='edit' || $_POST['m']=='add') { $res=mysqli_query($link,'SELECT * FROM '.$table.' WHERE '.$column.'=\''.myesc($link,$_POST['v']).'\'') or muoribene(__LINE__.': '.mysqli_error($link),true); if (mysqli_num_rows($res)>0) muoribene('«'.hspech($_POST['v']).'» already exists!',true); } if ($_POST['m']=='edit') { if ($table!='Languages') { mysqli_query($link,'UPDATE '.$table.' SET '.$column.'=\''.myesc($link,$_POST['v']).'\' WHERE ID='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); } else { $langs=array('NameOrig'=>mb_ucfirst(locale_get_display_name($_POST['v'],$_POST['v'])), 'NameCA'=>mb_ucfirst(locale_get_display_name($_POST['v'],'ca')), 'NameEN'=>mb_ucfirst(locale_get_display_name($_POST['v'],'en')), 'NameES'=>mb_ucfirst(locale_get_display_name($_POST['v'],'es')), 'NameFR'=>mb_ucfirst(locale_get_display_name($_POST['v'],'fr')), 'NameIT'=>mb_ucfirst(locale_get_display_name($_POST['v'],'it'))); if ($_POST['v']==$langs['NameOrig']) { muoribene('«'.$_POST['v'].'»: unknown language identifier.',true); } else { mysqli_query($link,'UPDATE '.$table.' SET Code=\''.myesc($link,$_POST['v']).'\', NameOrig=\''.myesc($link,$langs['NameOrig']).'\', NameCA=\''.myesc($link,$langs['NameCA']).'\', NameEN=\''.myesc($link,$langs['NameEN']).'\', NameES=\''.myesc($link,$langs['NameES']).'\', NameFR=\''.myesc($link,$langs['NameFR']).'\', NameIT=\''.myesc($link,$langs['NameIT']).'\' WHERE ID='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); $_POST['v']=$langs['Name'.$dlang].' ['.$_POST['v'].']'; } } $morejs.='parent.'.$dispselarr.'=parent.cheltxtbyval(parent.'.$dispselarr.','.$id.',"'.jsencode($_POST['v']).'");'.N; $morejs.='parent.'.$dispselarr.'=parent.sortByKey(parent.'.$dispselarr.',0);'.N; $morejs.='parent.'.$dispselarr.'=parent.selonebyval(parent.'.$dispselarr.','.$id.');'.N; } elseif ($_POST['m']=='add') { if ($table!='Languages') { mysqli_query($link,'INSERT INTO '.$table.' (ID, '.$column.', AddedBy) VALUES (NULL, \''.myesc($link,$_POST['v']).'\', \''.$account['ID'].'\')') or muoribene(__LINE__.': '.mysqli_error($link),true); $newid=mysqli_insert_id($link); } else { $langs=array('NameOrig'=>mb_ucfirst(locale_get_display_name($_POST['v'],$_POST['v'])), 'NameCA'=>mb_ucfirst(locale_get_display_name($_POST['v'],'ca')), 'NameEN'=>mb_ucfirst(locale_get_display_name($_POST['v'],'en')), 'NameES'=>mb_ucfirst(locale_get_display_name($_POST['v'],'es')), 'NameFR'=>mb_ucfirst(locale_get_display_name($_POST['v'],'fr')), 'NameIT'=>mb_ucfirst(locale_get_display_name($_POST['v'],'it'))); if (strtolower($_POST['v'])==strtolower($langs['NameOrig'])) { muoribene('«'.$_POST['v'].'»: unknown language identifier.',true); } else { mysqli_query($link,'INSERT INTO '.$table.' (ID, Code, NameOrig, NameCA, NameEN, NameES, NameFR, NameIT, AddedBy) VALUES (NULL, \''.myesc($link,$_POST['v']).'\', \''.myesc($link,$langs['NameOrig']).'\', \''.myesc($link,$langs['NameCA']).'\', \''.myesc($link,$langs['NameEN']).'\', \''.myesc($link,$langs['NameES']).'\', \''.myesc($link,$langs['NameFR']).'\', \''.myesc($link,$langs['NameIT']).'\', '.$account['ID'].')') or muoribene(__LINE__.': '.mysqli_error($link),true); $newid=mysqli_insert_id($link); $_POST['v']=$langs['Name'.$dlang].' ['.$_POST['v'].']'; } } $morejs.='parent.'.$dispselarr.'.push(["'.jsencode($_POST['v']).'",'.$newid.',true]);'.N; $morejs.='parent.'.$dispselarr.'=parent.sortByKey(parent.'.$dispselarr.',0);'.N; $morejs.='parent.'.$dispselarr.'=parent.selonebyval(parent.'.$dispselarr.','.$newid.');'.N; } elseif ($_POST['m']=='remove') { if (!array_key_exists('subst',$_POST) || preg_match('/^[0-9]+$/',$_POST['subst'])!==1) muoribene(__LINE__.': Malformed input.',true); $newid=$_POST['subst']+0; mysqli_query($link,'DELETE FROM '.$table.' WHERE ID='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); if ($newid==0) { $res=mysqli_query($link,'SELECT * FROM '.$lntable.' WHERE '.$lncolumn.'='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); mysqli_query($link,'DELETE FROM '.$lntable.' WHERE '.$lncolumn.'='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); while ($row=mysqli_fetch_assoc($res)) { $rres=mysqli_query($link,'SELECT * FROM '.$lntable.' WHERE InstID='.$row['InstID'].' ORDER BY Pos ASC') or muoribene(__LINE__.': '.mysqli_error($link),true); $pos=0; while ($rrow=mysqli_fetch_assoc($rres)) { $pos++; mysqli_query($link,'UPDATE '.$lntable.' SET Pos='.$pos.' WHERE InstID='.$rrow['InstID'].' AND '.$lncolumn.'='.$rrow[$lncolumn].' AND Pos='.$rrow['Pos']) or muoribene(__LINE__.': '.mysqli_error($link),true); } } } else { $res=mysqli_query($link,'SELECT * FROM '.$lntable.' WHERE '.$lncolumn.'='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); while ($row=mysqli_fetch_assoc($res)) { $rres=mysqli_query($link,'SELECT * FROM '.$lntable.' WHERE InstID='.$row['InstID'].' AND '.$lncolumn.'='.$newid) or muoribene(__LINE__.': '.mysqli_error($link),true); if (mysqli_num_rows($rres)==0) { mysqli_query($link,'UPDATE '.$lntable.' SET '.$lncolumn.'='.$newid.' WHERE InstID='.$row['InstID'].' AND '.$lncolumn.'='.$id.' AND Pos='.$row['Pos']) or muoribene(__LINE__.': '.mysqli_error($link),true); //+++ potrebbe aver senso mettere js qui che sposti la voce agli associati se non è già lì, magari con un alerta } else { mysqli_query($link,'DELETE FROM '.$lntable.' WHERE InstID='.$row['InstID'].' AND '.$lncolumn.'='.$id.' AND Pos='.$row['Pos']) or muoribene(__LINE__.': '.mysqli_error($link),true); $rres=mysqli_query($link,'SELECT * FROM '.$lntable.' WHERE InstID='.$row['InstID'].' ORDER BY Pos ASC') or muoribene(__LINE__.': '.mysqli_error($link),true); $pos=0; while ($rrow=mysqli_fetch_assoc($rres)) { $pos++; mysqli_query($link,'UPDATE '.$lntable.' SET Pos='.$pos.' WHERE InstID='.$rrow['InstID'].' AND '.$lncolumn.'='.$rrow[$lncolumn].' AND Pos='.$rrow['Pos']) or muoribene(__LINE__.': '.mysqli_error($link),true); } } } } $morejs.='parent.'.$dispselarr.'=parent.rmelbyval(parent.'.$dispselarr.','.$id.');'.N; } $morejs.='parent.filtsel (parent.'.$dispselarr.', parent.document.getElementById("'.$searchid.'").value, "'.$dispselid.'", "'.$recbid.'", "'.$searchid.'", "white", "#ff8080");'.N; $morejs.='parent.closeinpup();'.N; } if (array_key_exists('w',$_GET) && array_key_exists('i',$_GET) && preg_match('/^[0-9]+$/',$_GET['i'])===1 && array_key_exists('m',$_GET) && in_array($_GET['m'],array('edit','add','remove'))) { $id=$_GET['i']+0; switch ($_GET['w']) { case 'LocalityID': $out=buildform($id,'Localities','Name'.$dlang,'Location name','Instances','LocalityID'); $exvalsjsarr=setjsarr('Localities','Name'.$dlang); break; case 'DispLangs': $out=buildform($id,'Languages','Code','Language code','InstOurLangs','OurLangID'); $exvalsjsarr=setjsarr('Languages','Code'); break; case 'DispFinModes': $out=buildform($id,'Financing','Type','Financing modality','InstFinancing','FinID'); $exvalsjsarr=setjsarr('Financing','Type'); break; case 'DispPolicies': $out=buildform($id,'Policies','Name','Policy','InstPolicies','PolID'); $exvalsjsarr=setjsarr('Policies','Name'); break; case 'DispTags': $out=buildform($id,'Tags','Name','Tag','InstTags','TagID'); $exvalsjsarr=setjsarr('Tags','Name'); break; default: muoribene(__LINE__.': Malformed input.',true); break; } } elseif (array_key_exists('m',$_POST) && in_array($_POST['m'],array('edit','add','remove')) && array_key_exists('t',$_POST) && in_array($_POST['t'],array('Localities','Languages','Financing','Policies','Tags')) && array_key_exists('i',$_POST) && preg_match('/^[0-9]+$/',$_POST['i'])===1) { $id=$_POST['i']+0; if (array_key_exists('v',$_POST)) { $out=''; if ($_POST['t']=='Localities') { if ($_POST['m']!='remove') { //https://nominatim.openstreetmap.org/search?q=bresso,+milano&format=json&accept-language=fr&email=pezcurrel@tiscali.it //https://nominatim.openstreetmap.org/lookup?osm_ids=R45266&format=json&accept-language=fr&addressdetails=1&email=pezcurrel@tiscali.it $url='https://nominatim.openstreetmap.org/search?q='.urlencode($_POST['v']).'&format=json&email='.$iniarr['ref_email']; $osmd=@file_get_contents($url,false,$context); if ($osmd!==false) { $osmd=json_decode($osmd,true); if (count($osmd)>0) { $addrkeys=array('neighbourhood','borough','suburb','city','municipality','county','district','province','region','state','country'); $onegood=false; $divs=''; foreach ($osmd as $loc) { $good=false; $div=''; //$loc=array(); // simula errore if (array_key_exists('osm_id',$loc) && array_key_exists('osm_type',$loc) && $loc['osm_type']=='relation') { $div.=''.N; $div.='

'.$lang.': '.hspech($dispname).'
'.$lang.': No useful «address» data found in OpenStreetMap lookup data.
'.$lang.': No «address» found in OpenStreetMap lookup data.
'.$lang.': Couldn’t fetch OpenStreetMap lookup data.

'.N.$div; } else { $divs.='

'.N.$div; } $divs.='

'.N.''.N; } } if ($onegood) { $out.=''.N; } else { $out.='

Sorry, no good OpenStreetMap entry was found
(they are either incomplete or already in our database)

'.N; $out.=$divs; } } else { $out='

Couldn’t find OpenStreetMap data for «'.hspech($_POST['v']).'».

'; } } else { $out='

Couldn’t fetch OpenStreetMap search data.

'.N; } } else { // locality remove check($link,$account,$_POST['m'],'Localities','Instances','LocalityID',$id); $out='CANCELLO!'; if (!array_key_exists('subst',$_POST) || preg_match('/^[0-9]+$/',$_POST['subst'])!==1) muoribene(__LINE__.': Malformed input.',true); $_POST['subst']+=0; if ($_POST['subst']==0) { mysqli_query($link,'UPDATE Instances SET LocalityID=NULL WHERE LocalityID='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); } else { mysqli_query($link,'UPDATE Instances SET LocalityID='.$_POST['subst'].' WHERE LocalityID='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); } mysqli_query($link,'DELETE FROM Localities WHERE ID='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); $morejs.='parent.locselarr=parent.rmelbyval(parent.locselarr,'.$id.');'.N; $morejs.='parent.filtsel (parent.locselarr, parent.document.getElementById("locsearch").value, "LocalityID", "locselre", "locsearch", "white", "#ff8080");'.N; $morejs.='parent.closeinpup();'.N; } } elseif ($_POST['t']=='Languages') { edaddrem($id,'Languages','Name'.$dlang,'langselarr','InstOurLangs','OurLangID','langsearch','DispLangs','langselre'); } elseif ($_POST['t']=='Financing') { edaddrem($id,'Financing','Type','finselarr','InstFinancing','FinID','finsearch','DispFinModes','finselre'); } elseif ($_POST['t']=='Policies') { edaddrem($id,'Policies','Name','polselarr','InstPolicies','PolID','polsearch','DispPolicies','polselre'); } elseif ($_POST['t']=='Tags') { edaddrem($id,'Tags','Name','tagselarr','InstTags','TagID','tagsearch','DispTags','tagselre'); } else { // questo è ridondante ma fa niente muoribene(__LINE__.': Malformed input.',true); } } elseif (array_key_exists('OSMID',$_POST) && preg_match('/^[0-9]+$/',$_POST['OSMID'])===1 && array_key_exists('LocXX',$_POST) && array_key_exists('LocCA',$_POST) && array_key_exists('LocEN',$_POST) && array_key_exists('LocES',$_POST) && array_key_exists('LocFR',$_POST) && array_key_exists('LocIT',$_POST)) { // locality edit/add check($link,$account,$_POST['m'],'Localities','Instances','LocalityID',$id); $res=mysqli_query($link,'SELECT * FROM Localities WHERE Name'.$dlang.'=\''.myesc($link,$_POST['Loc'.$dlang]).'\'') or muoribene(__LINE__.': '.mysqli_error($link),true); if (mysqli_num_rows($res)>0) muoribene('«'.hspech($_POST['Loc'.$dlang]).'» already exists!',true); $out='EDITO/AGGIUNGO!'; if ($_POST['m']=='add') { mysqli_query($link,'INSERT INTO Localities (ID, OSMID, NameOrig, NameCA, NameEN, NameES, NameFR, NameIT, AddedBy) VALUES (NULL, \''.myesc($link,$_POST['OSMID']).'\', \''.myesc($link,$_POST['LocXX']).'\', \''.myesc($link,$_POST['LocCA']).'\', \''.myesc($link,$_POST['LocEN']).'\', \''.myesc($link,$_POST['LocES']).'\', \''.myesc($link,$_POST['LocFR']).'\', \''.myesc($link,$_POST['LocIT']).'\', \''.$account['ID'].'\')') or muoribene(__LINE__.': '.mysqli_error($link),true); $id=mysqli_insert_id($link); $morejs.='parent.locselarr.push(["'.jsencode($_POST['Loc'.$dlang]).'",'.$id.',true]);'.N; } elseif ($_POST['m']=='edit') { mysqli_query($link,'UPDATE Localities SET OSMID=\''.myesc($link,$_POST['OSMID']).'\', NameOrig=\''.myesc($link,$_POST['LocXX']).'\', NameCA=\''.myesc($link,$_POST['LocCA']).'\', NameEN=\''.myesc($link,$_POST['LocEN']).'\', NameES=\''.myesc($link,$_POST['LocES']).'\', NameFR=\''.myesc($link,$_POST['LocFR']).'\', NameIT=\''.myesc($link,$_POST['LocIT']).'\' WHERE ID='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); $morejs.='parent.locselarr=parent.cheltxtbyval(parent.locselarr,'.$id.',"'.jsencode($_POST['Loc'.$dlang]).'");'.N; } $morejs.='parent.locselarr=parent.sortByKey(parent.locselarr,0);'.N; $morejs.='parent.locselarr=parent.selonebyval(parent.locselarr,'.$id.');'.N; $morejs.='parent.filtsel (parent.locselarr, parent.document.getElementById("locsearch").value, "LocalityID", "locselre", "locsearch", "white", "#ff8080");'.N; $morejs.='parent.closeinpup();'.N; } else { muoribene(__LINE__.': Malformed input.',true); } } else { muoribene(__LINE__.': Malformed input.',true); } function buildform($id,$table,$column,$title,$lntable,$lncolumn) { global $account, $tables, $link; check($link,$account,$_GET['m'],$table,$lntable,$lncolumn,$id); $out=''; if ($_GET['m']=='edit' || $_GET['m']=='remove') { $res=mysqli_query($link,'SELECT * FROM '.$table.' WHERE ID='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); $row=mysqli_fetch_assoc($res); if ($_GET['m']=='edit') { $out.='

'.$title.'

'.N; $out.=''.N; $out.=''.N; } elseif ($_GET['m']=='remove') { $lnres=mysqli_query($link,'SELECT * FROM '.$lntable.' WHERE '.$lncolumn.'='.$id) or muoribene(__LINE__.': '.mysqli_error($link),true); $nlinking=mysqli_num_rows($lnres); if ($nlinking>0) { $out.='

«'.hspech($row[$column]).'» is referenced by '.$nlinking.' '.(($nlinking==1) ? 'instance' : 'instances').'!

'.N; $out.='

What do you want to do with '.(($nlinking==1) ? 'this reference' : 'those '.$nlinking.' references').'?
If you choose “Delete” '.(($nlinking==1) ? 'it' : 'they').' will be removed; otherwise you can select a substitution record.

'.N; $out.=''.N; } else { $out.=''.N; } $out.='

Are you sure you want to remove «'.$row[$column].'»?

'.N; $out.=''.N; } $out.=''.N; } elseif ($_GET['m']=='add') { $out.='

'.$title.'

'.N; $out.=''.N; $out.=''.N; $out.=''.N; } $out.=''.N; $out.=''.N; $out.='

'.N; $out.='

'.N; $out=''.N; return($out); } function setjsarr($table,$column) { global $link; $jsarr=array(); $res=mysqli_query($link,'SELECT '.$column.' FROM '.$table) or muoribene(__LINE__.': '.mysqli_error($link),true); while ($row=mysqli_fetch_assoc($res)) { $jsarr[]=jsencode(strtolower($row[$column])); } $jsarr='["'.implode('","',$jsarr).'"];'.N; return($jsarr); } mysqli_close($link); ?> Mustard - Little edit’s helper

...