'', //default separator/delimter character. Empty so FoxCSV can determine sep from data, without need to use sep= parameter 'nl' => '', //new line (line break) character, for fields using multi-lines 'case' => 0, //case-insensitive queries, set to 1 for case-sensitive queries 'regex' => 0, //simplified pagelist-like wildcards in queries. Set to 1 to use regular expression query patterns 'header' => 1,//no column/field headers included in data rows. Set to 1 to include a row of header names 'hideidx'=> 0, //default is to show index as column in auto templates 'idxname'=> 'Row', //default name for IDX header field 'textname' => 'Text', //default name for Text header field 'datename' => 'Date', //default name for TMX header field 'datefmt' => "Y-m-d @H:i", //default date/time format for TMX field 'ptvsum-suffix' => '_SUM', //default suffix for PTV sum names 'popups' => 1, //set to 1 to enable popup confirmation dialogues 'order' => 'natcase', //default sort order keyword 'sortable' => 1, //default class=sortable (sorting via js) for auto tables 'buttons' => 1, //by default show add, edit and delete buttons if authorised to edit 'editheaderbutton' => 1, //include edit header button 'addbutton' => 1, //include add item button 'deletebutton' => 1, //include delete item buttons 'buttonicon' => 1, // use button icons (if images are found with url) 'editiconurl' => "$FarmPubDirUrl/fox/edit-icon.png", //url path to icon image 'addiconurl' => "$FarmPubDirUrl/fox/add-icon.png", 'deleteiconurl' => "$FarmPubDirUrl/fox/delete-icon.png", 'saveasnew' => 0, //set to 1 to show 'Save as New' button in edit form 'reindex' => 0, //by default no automatic re-indexing of IDX fields when adding new items 'new' => 'bottom', //by default new entry to bottom, set to 'top' to put new entry just below header row 'env' => 0, //set to 1 to enclose each item in double quotes 'copycsv' => 0, //set to 1 to show form for copying/importing csv/txt files 'fileedit' => 1, // set to 0 to inhibit file editing (no writing to files, only to wiki pages) 'decimals' => 2, // decimal places for 'num' fields number format 'decisep' => '.', // decimal separator for 'num' fields number format 'thousep' => ',', // thousands separator for 'num' fields number format 'popedit' => 0, // set to 1 for popup (fixed position) edit form 'popbottom' => 0, //set to 1 for bottom popup edit form position, instead of top position 'popmax'=> 10, // sets maximum of popedit columns (fields in each row) )); //debug info: 1 show & run, 2 stop end of FoxCSV_Update (before save), // 3 stop end of Preprocess_Input. SDV($FoxCSVDebug,0); //for popup edit form js to keep scroll position if ($action=='foxcsvedit') { $HTMLFooterFmt['csvscrollpos'] = ""; } // markup expression {(newidx [sep=..])} . Can be used as template var {$$(newidx .... )} in fox query form template $MarkupExpr['newidx'] = 'fxc_Next_Idx_Num_ME($pagename, $argp)'; function fxc_Next_Idx_Num_ME( $pagename, $argp ) { $src = $argp['source'] ?? $argp[''][0] ?? ''; if ($src=='') { $GLOBALS['FoxMsgFmt'][] = "%red%'''Error''' in form: '''newidx''' needs specific source. Check form, and correct wrong idxs%%"; return '000'; //error } $sep = $argp['sep'] ?? $argp['seperator'] ?? $GLOBALS['FoxCSVConfig']['sep']; $rows = fxc_Get_Text_Rows( $pagename, $src ); if (strstr($rows[0], "IDX$sep")) $n = fxc_Get_Row_Key($rows, "new", $sep); else $n = count($rows); return $n; } //}}} // markup expression {(csv ......)} . Can be used as template var {$$(csv .... )} in fox query form template $MarkupExpr['csv'] = 'fxc_Display_ME($pagename, $argp)'; function fxc_Display_ME( $pagename, $argp ) { unset($argp['#']); $out = fxc_Display($pagename, 'mxcsv', $argp); return str_replace("\n\n","\n", $out); } //}}} // markup directive (:csv source= template= [sort= ] [query="..."] [seperator=".."] :) Markup('foxcsvdisplay', '>if', '/\\(:csv\\s+(\\S.*?):\\)/i', "fxc_Display"); // main function for csv display function fxc_Display ($m) { global $FoxCSVConfig, $FoxMsgFmt, $RegexQueryExclude, $InputValues; extract($GLOBALS['MarkupToHTML']); $args = ParseArgs($m[1]); unset($args['#']); // initialise option variables if ((isset($args['template']) OR isset($args['fmt'])) AND empty($args['header'])) $args['header'] = 0; // by default no header row if we use template, not auto template $opt = array_merge($FoxCSVConfig, $args); $source = $opt['source'] ?? $opt['src'] ?? array_shift($opt['']); if (empty($source)) return; $template = $opt['template'] ?? $opt['fmt'] ?? ''; //suppress display of add, edit and delete buttons, because they dont work when displaying file contents if (preg_match('/\.(csv|txt)$/i',$source,$m) && $FoxCSVConfig['fileedit']==0) $opt['buttons'] = 0; // get text rows from page, page section or file $text = fxc_Get_Text ($pagename, $source); if ($text=='') return "'''Error:''' cannot get source text from '''$source'''"; $text = trim($text,"\r\n"); // get csv separator/delimiter direct from text, or via sep= parameter list($sep, $nl, $opt) = fxc_Set_sep_nl($text, $opt); // parse text into data 2-dimensional array, items per row $data = fxc_Parse_CSV($text, $sep, $nl); if (empty($data)) return "'''Error:''' Parsing csv data failed!"; // checking for errors in header names. Display errors by using markup (:foxmessages:) foreach ($data[0] as $k=>$v) { if (preg_match('/^[\d]|[^\-a-zA-Z0-9_]/', $v, $m)) $FoxMsgFmt[] = "'''Error:''' %black%invalid header name: %blue%'''$v''' %black%in $source%%"; } // display specific data item only if (isset($opt['cell'])) $cell = $opt['cell']; elseif (isset($opt[''][0]) && strpos($opt[''][0],'/')>0) $cell = array_shift($opt['']); if (isset($cell)) return fxc_Get_Item($data, $cell, $sep); // create data array with field names as keys for each item array_walk($data, function(&$a) use ($data) { $a = fxc_Array_Combine_Special($data[0], $a); }); // create header array, remove header row from data array $header = array_shift($data); // add SOURCE and IDX fields to data array, (:csv-delete ...:) and (:csv-edit ...:) rely on their presence foreach($data as $k=>$item) { $add1 = array('SOURCE'=>$source); if (array_key_exists('IDX',$item)) { $data[$k] = $add1 + $item; } else { //no IDX, add it! $add2 = array('IDX'=>$k+1); $data[$k] = $add2 + $add1 + $item; } } // filter data. Query-filter is constructed from query string and/or strings from header field names submitted $opt['query'] = $opt['query'] ?? $opt['filter'] ?? $opt['q'] ?? ""; fxc_Query($data, $header, $opt); // sort data if (isset($opt['sort'])) fxc_Multi_Column_Sort($data, $header, $opt['sort'], $opt['order']); // add CNT field, can be used with template var {$$CNT}, // giving descending result row count (Not the IDX of the item) foreach($data as $k=>$item) { $new = array('CNT'=>$k+1); $data[$k] = $new + $item; } // extract subset of data rows according to 'count=' parameter, using function on scripts/pagelist.php if (isset($opt['count'])) FPLTemplateSliceList($pagename, $data, $opt); $data = array_values($data); //re-index $rowcnt = count($data); $header = array('SOURCE'=>$source) + $header; //source field needed for possible var replacement, does not display as column //build automatic table or custom display for display of csv data // set a function call number, used to feed correct text to Iput Values for copycsv static $pin = 0; $pin++; // with no display template build table using inbuilt template automatic table template for all fields, or fetch template string from a page $head = $body = $foot = $csvbody = ''; // with no display template build table using inbuilt template automatically if ($template=='') { $auto = 1; $out = fxc_Make_Auto_Table ($pagename, $data, $header, $opt, $pin); } // use an external template. // The header row may be supplied via (:foxcsv ...:) markup, or not at all else { $auto = 0; $template = RetrieveAuthSection ($pagename, $template, $list=NULL, $auth='read'); // add pin=$pin to each csv markups in the template, so no need to use {$$PIN} in template $template = preg_replace('/(\\(:(button|csvform)-(add|edit|delete)\\s)/s', "$0pin=$pin ", $template); $template = preg_replace('/\{\$\$PIN\}/',$pin, $template); $tp = preg_split('/\(:template\s(\w+):\)/si', $template,-1,PREG_SPLIT_NO_EMPTY|PREG_SPLIT_DELIM_CAPTURE); if (count($tp)==1) $tpeach = $tp[0]; else { for($i=0; $i}] if (isset($tplast)) { $sums = array(); if (isset($opt['num'])) { $numf = fxc_Fields($opt['num'], $header); } foreach($header as $k=>$hd) { if (isset($numf) && in_array($hd, $numf)) $sums['SUM_'.$k] = fxc_Array_Sum(array_column($data,$hd),$hd,$opt); } $str = FoxVarReplace($pagename, $sums, '', $tplast); $foot .= preg_replace('/\{\$\$\(?(.*?)\)?\}/'," ",$str); } $out = $head.$body.$foot; } // return output as wiki markup, Markup to HTML will be done later by PmWiki return $out; } //}}} // create output as a table for display function fxc_Make_Auto_Table( $pagename, $data, $header, $opt, $pin ) { // show specific columns only, or to exclude certain columns $source = $header['SOURCE']; if (isset($opt['show'])) $header = fxc_Fields($opt['show'], $header); //remove fields not for display unset($header['SOURCE']); if ($opt['hideidx']==1) unset($header['IDX']); // set cell alignment $ralf = $calf = array(); if (isset($opt['ralign'])) $ralf = fxc_Fields($opt['ralign'], $header); //right aligned columns if (isset($opt['calign'])) $calf = fxc_Fields($opt['calign'], $header); //centred columns // build template string $tmpl = ''; $header = array_values($header); //index // format numbers in 'num=' specified fields: displays rounded to n decimal places, sets decimal and thousands separtors if (isset($opt['num'])) { $numf = fxc_Fields($opt['num'], $header); $ralf = array_merge($ralf, $numf); // num=... number fields get right-aligned foreach ($data as &$row) { foreach ($row as $k => $it) { if (in_array($k, $numf)) { if ($k=='IDX') { $row[$k] = $it; continue; } list($dc, $ds, $ts) = fxc_Set_Number_Fmt($k, $opt); $it = fxc_String_To_Float($it, $ds); $row[$k] = fxc_Format_Number($it, $dc, $ds, $ts); } } } } foreach ($header as $k=>$v) { $nr = ($k==0) ? "nr" : ''; if (in_array($v, $calf)) $al='center'; elseif (in_array($v, $ralf)) $al='right'; else $al='left'; $tmpl .= "(:cell$nr align=$al:){\$\$".$v."} \n"; } $head = $foot = $body = ''; if ($opt['buttons']==1 && CondAuth($pagename,'edit')) { // insert delete and edit links for each row if ($GLOBALS['FoxCSVConfig']['saveasnew']==1 && $opt['saveasnew']==1) { if ($opt['new']=='top') $opt['saveasnew'] = 'top'; else $opt['saveasnew'] = 'bottom'; } if (!empty($opt['popupedit'])) $opt['popedit'] = 1; //legacy //parameters passed to csvform-edit as hidden inputs $opkeys = array('sep','env','csvtitle','icon','multiline','saveasnew','popedit','autoreindex', 'popbottom','popmax','popcols','poprows','eshow','eidx','base'); $epars = ''; $pin = "pin=$pin"; foreach ($opkeys as $n) if (!empty($opt[$n])) $epars .= " $n='$opt[$n]'"; $head .= "(:csvform-edit source=$source $pin $epars:)". (($opt['deletebutton']==1)? "(:csvform-delete target=$source $pin $epars:)":''); $icons = (isset($opt['icon'])) ? $opt['icon'] : $GLOBALS['FoxCSVConfig']['buttonicon']; $icon = ($icons==1) ? 'icon=1' : 'icon=0'; $template = $tmpl."(:cell:)\n(:div class='csvbuttons':)(:button-edit idx={\$\$IDX} $pin $icon:)". (($opt['deletebutton']==1)? "(:button-delete idx={\$\$IDX} $pin $icon:)":''). "\n(:divend:)\n"; } else $template = $tmpl."\n"; $head .= (!empty($opt['csvtitle'])) ? "\n!!!".$opt['csvtitle']."\n" : ''; // make table rows (body) for ($k=0; $k$hd) { $nr = ($k==0) ? "nr" : ''; if ($hd=='IDX') $foot .= "(:cell$nr:) \n"; else if (in_array($hd, $sumf)) { $col = array_column($data, $hd); $sum = "%tsum%".fxc_Array_Sum($col, $hd, $opt); if (in_array($hd, $calf)) $al='center'; elseif (in_array($hd, $ralf)) $al='right'; else $al='left'; $foot .= "(:cell$nr align=$al class=csvsum:)$sum \n"; } else $foot .= "(:cell$nr:) \n"; //no sum for this column } } //make csv table; insert copy form first in head if (CondAuth($pagename,'admin') && $opt['buttons']==1) { $copyform = ''; if ($opt['copycsv']==1) { fxc_Copy_To_CSV ($pagename, $data, $header, $opt, $pin); // generate new csv text and put into $InputValues $copyform = "\n(:fox copycsv class='csvform':)". "(:input submit post '$[Copy to Page]':) (:input text target size=9:)". "(:foxtemplate \"{\$\$text$pin}\":)\n(:input hidden text$pin :)". "(:foxend copycsv:)\n"; } // add toolbar with csv-action forms if (!empty($opt['toolbar']) ) $head .= "(:div class='csvtoolbar':)(:csv-index $source:)(:csv-reindex $source:)(:csv-trim $source:)(:csv-quote $source:)". "(:csv-reformat $source:)(:csv-newcol $source:)(:csv-coldel $source:)(:csv-export $source:)$copyform\n(:divend:)\n"; elseif ( $opt['copycsv']==1 ) $head .= $copyform; } // build header row as first row for output, the others are added after var substitutions by FoxVarReplace if ($opt['header']==0) $head .= "(:table:)\n"; else { $htpl = ''; if ($opt['sortable']==1 && empty($opt['sum'])) $class = "class='sortable csvtable'"; // last row is included for sorting $htpl .= "\n(:table $class :)\n"; //sortable tables need to be enabled in config via: $EnableSortable = 1; foreach ($header as $k=>$v) { $nr = ($k==0) ? "nr" : ''; if ($v=='IDX') $v = $opt['idxname']; // give IDX a friendlier name, but IDX stays internally as special name if ($v=='Text') $v = $opt['textname']; if ($v=='TMX') $v = $opt['datename']; if (in_array($v, $calf)) $al='center'; elseif (in_array($v, $ralf)) $al='right'; else $al='left'; $htpl .= "(:head$nr align=$al:)$v \n"; } $header['SOURCE'] = $source; // add SOURCE back in if ($opt['buttons']==1 && CondAuth($pagename,'edit')) { $nw = ($opt['new']=='top') ? 'newtop' : 'new'; $htpl .= "(:cell:)"; $htpl .= "\n(:div class='colnosort csvbuttons':)". (($opt['editheaderbutton']==1)? "(:button-edit idx=header $pin $icon:)":''). (($opt['addbutton']==1)? "(:button-add idx='$nw' $pin $icon:)":''). "\n(:divend:)\n"; } $head .= FoxVarReplace($pagename, $header, '', $htpl)."\n"; } $foot .= "(:tableend:)"; return $head.$body.$foot; } //}}} // set column specific number-fmt parameters, return array of // dc=decimal places; ds=decimal separator; ts=thousands separator function fxc_Set_Number_Fmt($hd, $opt) { $dc = $opt['decimals']; if ($dc=='' OR $dc=='auto') $dc = 10; $ds = $opt['decisep']; $ts = $opt['thousep']; if ($ts==$ds) $ts = ($ds==",")?".":","; $dcn= array(); for ($i=0; $i<=10; $i++) if (isset($opt['deci'.$i])) $dcn[$i] = explode(',',$opt['deci'.$i]); foreach ($dcn as $i => $dd) if (in_array($hd, $dd)) $dc = $i; $dc = intval($dc); return array( $dc, $ds, $ts ); } // create sum of column array values function fxc_Array_Sum($arr, $hd, $opt) { if(!is_array($arr)) return $arr; list($dc, $ds, $ts) = fxc_Set_Number_Fmt($hd, $opt); $sum = 0; foreach ($arr as $k => $x) { if (empty($x)) continue; $sum = $sum + fxc_String_To_Float($x, $ds); //sum with std float values, non-numbers as +0 } # echo "
hd=$hd ts=$ts dc=$dc SUM=$sum "; $new = fxc_Format_Number($sum, $dc, $ds, $ts); # echo " -->$new"; return $new; } //}}} function fxc_Format_Number($x, $dc, $ds, $ts) { // reformat without setting number of decimals; ( '' or 'auto' => 10 ) if ($dc==10) { if (strpos($x,".")) { list($ip, $fp) = explode(".", $x); $x = number_format($ip, 0, "." ,$ts).$ds.$fp; } else $x = number_format($x, 0, $ds, $ts); //format integer } // else reformat with setting number of decimals according to decimals= or one of dec= parameters given else { $x = number_format(floatval($x), $dc, $ds, $ts); } return $x; } //}}} // make number string into float number function fxc_String_To_Float($x, $ds){ # echo "
str2float: $x"; if (!in_array($ds,array('.',','))) return 0; //decisep either , or . if (!strpos($x, $ds)) { $x = preg_replace('/[^0-9]/',"",$x); //integer number, no thousands separators if (empty($x)) $x = 0; return $x; } else { list($ip, $fp) = explode($ds, $x); $ip = preg_replace('/[^0-9]/',"",$ip); //integer part $fp = (isset($fp)) ? ".".$fp : ''; //dot + fraction part, or nil $num = $ip.$fp; # echo " ->$num"; return floatval($num); } } //}}} // determine and set separator/delimiter function fxc_Set_sep_nl( $text, $opt ) { $sep = $opt['sep'] ?? $opt['csvsep'] ?? $GLOBALS['FoxCSVConfig']['sep']; $nl = $opt['nl'] ?? $opt['csvnl'] ?? $GLOBALS['FoxCSVConfig']['nl']; if ($sep=='') { if (preg_match('/^(")?IDX\1?(;)/', $text, $m)) { $sep = $opt['sep'] = $m[2]; if ($m[1]=='"') $opt['quotes'] = 1; //assuming data elements are enclosed in dbl-quotes } elseif (preg_match('/^(")?[a-zA-Z][-\w]*\1?(.)/', $text, $m)) { //guessing sep is first non-alpha-numeric char after alphanum start $sep = $opt['sep'] = $m[2]; if ($m[1]=='"') $opt['quotes'] = 1; //assuming data elements are enclosed in dbl-quotes } else $sep = ' '; } $opt['sep'] = $sep; if ($sep=='\t') $sep = "\t"; //catching tab character if ($nl=='') { if (preg_match('/¦/', $text, $m)) $nl = '¦'; else if (strstr($text,'\\')) $nl = '\\\\'; //nl = \\ } $opt['nl'] = $nl; return array($sep, $nl, $opt); } //}}} // get text from file or page (section) function fxc_Get_Text( $pagename, $src ) { // get rows array from a file in uploads if (preg_match('/\.(csv|txt)$/i',$src,$ext)) { $txt = @fxc_Get_File_Content($pagename, $src); if ($txt===FALSE) $GLOBALS['FoxMsgFmt'][] = "'''Error:''' could not find file '''$src'''"; } else { $anc = strstr($src,'#'); if ($src[0]=='#') $src = $pagename.$src; $pn = MakePageName($pagename, $src); $src = $pn.$anc; $txt = RetrieveAuthSection($pn, $src); } if (empty($txt)) return ''; if (array_key_exists(1,$ext) && strtolower($ext[1])=="txt") { $txt = "Text\n".$txt; // setting header field 'Text' for text import } return $txt; } //}}} // get file contents from uploaded (attached) file function fxc_Get_File_Content( $pagename, $filename ) { global $UploadDir, $UploadPrefixFmt; $fpn = explode('/',$filename); if (isset($fpn[1])) { $fname = $fpn[1]; $fpre = "/".$fpn[0]; } else { $fname = $fpn[0]; $fpre = $UploadPrefixFmt; } $uppath = FmtPageName("$UploadDir$fpre", $pagename); $csvfile = FmtPageName("$uppath/$fname", $pagename); return file_get_contents($csvfile); } //}}} // parse 'data' text and create two-dimensional data array // The replacement patterns detemine how the csv items get translated for view // Adjusting patterns might need related adjustments in fxc_Make_CSV_Item() function fxc_Parse_CSV( $text, $sep, $nl ) { global $FoxCSVConfig, $FoxCSVDebug; # if ($FoxCSVDebug>1) echo "fxc_Parse_CSV >"; $data = explode("\n", $text); $data = fxc_Remove_Empty_Rows( $data, $sep); // text import with single 'Text' header field if ($data[0]=='Text') { foreach ($data as &$row) $row = array($row); return $data; } $csvTokenPatterns = array( "`\`" => "`¬¦`", //escaped \ backslash '`¦' => "`¬¬¬`", //escaped ¦ split pipe '`'.$sep => "``¦``", //escaped csv separator "\\".$sep => "``¦``", //escaped csv separator "\\\\" => "`¬¦`", //escaped backslash ); $csvRestorePatterns = array( '%25' => '%', //restored % html entity '%0a' => "\n", // ... \n '%0d' => "\r", // ... \r '%22' => '"', // ... " '``¦``' => $sep, //restored csv separator '`"' => '"', //restored quote-mark '`¬¦`' => "\\", //restored \ backslash # '\\' => "\n", //restored \n line break from \ # '¦' => "\n", //restored \n line break from ¦ '`¬¬¬`'=> "¦", //restored ¦ split pipe ); if ($nl=='¦') $csvRestorePatterns['¦'] = "\n"; if ($nl=='\\\\') $csvRestorePatterns['\\'] = "\n"; foreach ($data as $i => $row) { //replace some escaped characters with tokens, so the csv parsing does not foul foreach($csvTokenPatterns as $p => $r) $row = str_replace($p, $r, $row); //parse the items in row, each row becomes array of items (fields) $row = str_getcsv($row, $sep, "\"", "`"); //restore each item for view foreach ($row as $j => $it) { foreach($csvRestorePatterns as $p => $r) $it = str_replace($p, $r, $it); $row[$j] = $it; } $data[$i] = $row; //update data array } $data[0] = fxc_Normalise_Header( $data[0] ); return $data; } //}}} // making header names with pattern (adjusting input to get valid names) function fxc_Normalise_Header ( $header ) { $hdpat = array( "/^0$/"=> 'IDX', # 0 to IDX column "/'/" => '', # strip single-quotes "/^\d+/" => 'N$0', #prepend leading digits with 'N' "/[^_[:alnum:]]+/" => ' ', # convert everything else to space '/ /' => '-', # convert space to hyphen ); $hdr = array_map('trim', $header); //assuming first row is header $header = PPRA($hdpat, $hdr); //sanitising header names //check for duplicates in header $duplis = array_diff_assoc($header, array_unique($header)); if (!empty($duplis)) { $GLOBALS['FoxMsgFmt'][] = "%red%'''Error:''' header has duplicate field names. Edit header!"; foreach ($duplis as $k=>$v) $header[$k] = $v."-duplic"; } foreach ($header as $name) if (count($header)==1 ) $GLOBALS['FoxMsgFmt'][] = "%green%'''Warning:''' just one header field! Check if 'sep=..' is correct. "; return $header; } //}}} // Get item by header and idx: name/idx or col/idx {(csv /)} function fxc_Get_Item( $data, $ref, $sep ) { $ref = explode('/', $ref); if (!isset($ref[1])) return "'''Error:''' wrong or no name or idx!"; $col = $ref[0]; $row = $ref[1]; //get row by idx number if ($data[0][0]=='IDX') { foreach ($data as $k => $rw) if ($rw[0]==$row) $row = $k; } if (!array_key_exists($row,$data)) return "'''Error:''' row '''$row''' does not exist!"; $el = $data[$row]; if (!preg_match('/^\d+$/',$col)) $el = array_combine($data[0], $el); if (!array_key_exists($col,$el)) return "'''Error:''' header column '''$col''' does not exist, or wrong separator!"; return $el[$col]; } //}}} // filter data array according to query (regular expression or PmWiki pagelist query syntax) function fxc_Query( &$data, $header, $opt ) { $query = ParseArgs($opt['query']); $quin = (isset($query[''])) ? implode(',',$query['']) : ''; $quex = (isset($query['-'])) ? implode(',',$query['-']) : ''; unset($query['#'], $query[''], $query['-']); foreach($header as $k=>$v) { if (array_key_exists($k,$opt) && !empty($opt[$k])) { if (array_key_exists($k,$query)) $query[$k] = ",".$query[$k].",".$opt[$k]; //combine strings if we got double keys else $query[$k] = ",".$opt[$k]; $query[$k] = trim($query[$k], ","); continue; } } foreach($header as $k=>$v) { if (!isset($query[$k])) $query[$k] = ''; if ($quin!='') $query[$k] .= ",".$quin; if ($quex!='') $query[$k] .= ",-".$quex; } if(!array_filter($query)) return; // create regex patterns for including and excluding $exclude = $include = array(); foreach($query as $field => $pat) { if(!in_array($field, $header)) continue; if ($opt['regex']==0) { list($incl, $excl) = GlobToPCRE($pat); } $include[$field] = ($opt['regex']==0)? $incl : $pat; $exclude[$field] = ($opt['regex']==0)? $excl : ""; } // check each data item in turn if (!empty($query)) $matches = array(); foreach($data as $i=>$item) { foreach($include as $k => $pat) { if ($pat=="") continue; if (isset($opt['case']) && $opt['case']==0) $pat = "(?i)".$pat; if (preg_match("($pat)", $item[$k])) { $matches[$i] = $data[$i]; continue 2; } } } if ($opt['regex']==0) //for non-regex queries we may have exclude patterns foreach($matches as $i=>$item) { foreach($exclude as $k => $pat) { if ($pat=="") continue; if (isset($opt['case']) && $opt['case']==0) { $pat = "(?i)".$pat; } if (preg_match("($pat)",$item[$k])) { unset($matches[$i]); continue 2; } } } $data = $matches; } //}}} // sort a multi-column data array by up to four header fields function fxc_Multi_Column_Sort(&$data, $header, $sort, $order ) { global $FoxCSVConfig; global $FoxMsgFmt; $sort = explode(',',$sort); if (empty($sort[0])) return; $cnt = count($sort); //we want max 4 sort names if ($cnt > 4) { $FoxMsgFmt[] = "Warning: no more than 4 sort names allowed!"; for ($i=$cnt-1; $i>3 ; $i--) unset($sort[$i]); //reduce list to 3 names $cnt = 4; } $order = ParseArgs($order); unset($order['#']); $dir = $col = $flags = array(); // set sort direction, general or by sort field foreach($sort as $k=>$n) { if(substr($n, 0,1)=='-') { $sort[$k] = ltrim($n, '-'); $dir[$k] = SORT_DESC; } else { $sort[$k] = $n; $dir[$k] = SORT_ASC; } if (isset($order['-'])) $dir[$k] = SORT_DESC; if (isset($order[$n])) { if (substr($order[$n], 0,1)=='-') { $order[$n] = ltrim($order[$n], '-'); $dir[$k] = SORT_DESC; //the specific overrides the general } else $dir[$k] = SORT_ASC; } } // get columns for array_multisort(), set flags, pass to array_multisort() according to number of sort columns $so = array(); foreach($sort as $k=>$n) { if (!in_array($n,$header)) { $FoxMsgFmt[] = "Error: Could not sort Data. Invalid ''sort'' parameter!"; return; } $col[$k] = array_column($data, $n); // set sort order flags for each sort field if (in_array($n,array_keys($order))) $so[$k] = $order[$n]; else if (isset($order[''])) $so[$k] = $order[''][0]; else if (isset($order['-'])) $so[$k] = $order['-'][0]; else $so[$k] = $FoxCSVConfig['order']; //default // setting sort flags array $fl for array_multisort() switch($so[$k]) { case 'reg': case 'regular': $fl[$k] = SORT_REGULAR; break;// default; compare items normally (don't change types) case 'nat': case 'natural': $fl[$k] = SORT_NATURAL; break; // compare items as strings using "natural ordering" like natsort() case 'natcase': $fl[$k] = SORT_NATURAL|SORT_FLAG_CASE; break; // sort as strings natural and case-insensitively case 'str': case 'string': $fl[$k] = SORT_STRING; break; // compare items as strings case 'strcase': $fl[$k] = SORT_STRING|SORT_FLAG_CASE; break;// sort as strings and case-insensitively case 'num': case 'numeric': $fl[$k] = SORT_NUMERIC; break; // compare items numerically case 'loc': case 'locale': $fl[$k] = SORT_LOCALE_STRING; break; // compare items as strings, based on the current locale. It uses the locale, which can be changed using setlocale() default: $fl[$k] = SORT_REGULAR; break;// default; compare items normally (don't change types) } } switch ($cnt) { //do the ordering according to sort col number case '0': $FoxMsgFmt[] = "Error: Missing or invalid sort parameters!"; break; case '1': array_multisort($col[0], $dir[0] , $fl[0], $data); break; case '2': array_multisort($col[0], $dir[0] , $fl[0], $col[1], $dir[1], $fl[1], $data); break; case '3': array_multisort($col[0], $dir[0] , $fl[0], $col[1], $dir[1], $fl[1], $col[2], $dir[2], $fl[2], $data); break; case '4': array_multisort($col[0], $dir[0] , $fl[0], $col[1], $dir[1], $fl[1], $col[2], $dir[2], $fl[2], $col[3], $dir[3], $fl[3],$data); break; } } //}}} // create csv text and set to $InputValues for copy form function fxc_Copy_To_CSV( $pagename, $data, $fields, $opt, $pin ) { $sep = $opt['newsep'] ?? $opt['sep']; $head = ($GLOBALS['EnablePostDirectives']==1)? "(:csv #data sep=$sep:)\n(:if false:)\n[[#data]]\n" : ""; $tmpl = ''; foreach ($fields as $n) { $tmpl .= "{\$\$".$n."}".$sep; $head .= $n.$sep; } $tmpl = rtrim($tmpl,$sep)."\n"; //trim trailing separators, add line breaks $head = rtrim($head,$sep)."\n"; $body = ''; if (array_key_exists('header',$data)) unset($data['header']); // enclose items in quotes if they contain separator or newrow characters foreach ($data as &$row) foreach ($row as &$it) if (preg_match('/\n/',$it) OR strstr($it, $sep)) $it = '"'.$it.'"'; // "enclose item" if needed //data rows to text body foreach ($data as $k=>$v) { $str = FoxVarReplace($pagename, $v, '', $tmpl); $str = fxc_Make_CSV_Row ($str, $sep, $opt['nl'], $opt['env'])."\n"; $body .= preg_replace('/\{\$\$\(?(.*?)\)?\}/'," ",$str); } $foot = ($GLOBALS['EnablePostDirectives']==1)? "[[#dataend]](:ifend:)\n\n" : ""; $text = $head.$body.$foot; // set $InputValues for hidden input field when copoying/importing $GLOBALS['InputValues']['text'.$pin] = $text; } //}}} // write csv to file function fxc_Write_To_File ( $pagename, $filename, $text ) { global $UploadDir, $UploadPrefixFmt, $FoxMsgFmt; $fpn = explode('/',$filename); if (isset($fpn[1])) { $fname = $fpn[1]; $fpre = "/".$fpn[0]; } else { $fname = $fpn[0]; $fpre = $UploadPrefixFmt; } $uppath = FmtPageName("$UploadDir$fpre", $pagename); $file = FmtPageName("$uppath/$fname", $pagename); $backup = preg_replace("/.csv$/","-backup.csv",$file); if (file_exists($file)) copy($file, $backup); $fp = fopen($file, 'w'); fwrite($fp, $text); fclose($fp); } //}}} // make single text line into a valid csv record (row) function fxc_Make_CSV_Row( $row, $sep, $nl, $env='' ) { if ($GLOBALS['FoxCSVDebug']>0) show($row,'row'); $items = str_getcsv($row, $sep, "\"", "`"); foreach($items as &$str) { $str = fxc_Make_CSV_Item($str, $sep, $nl, $env); } $row = implode($sep, $items); //return row as string return $row; } //}}} // make single csv item. Replace line breaks with tokens, add quotes to quoted parts, enclose items in quotes if needed // function is called within functions: fxc_Make_CSV_Row, fxc_Preprocess_Input, FoxCSV_Update function fxc_Make_CSV_Item( $str, $sep, $nl='', $env='' ) { global $FoxCSVConfig, $FoxCSVDebug, $FoxDebug; if ($nl=='') $nl = ($FoxCSVConfig['nl']!='')? $FoxCSVConfig['nl'] : '¦'; $str = preg_replace('/\r\n?/', "\n", $str); // replace any CR or CRNR with NL $str = preg_replace('/(?$rows); foreach($rows as $k=>&$v) { $rows[$k] = trim($v,"\r\n"); //not trimming seps at right! if (empty($rows[$k])) unset($rows[$k]); } $rows = array_values($rows); //reindex return $rows; } //}}} // returns a field array as subset of header according to arg parameter [called from fxc_Make_Auto_Tables] function fxc_Fields( $arg, $header ) { if ($arg==1) return $header; $args = explode(',', $arg); $flg = 1; foreach ($args as $k=>$v) if ($v[0]=='-') { $args[$k] = substr($v,1); $flg = 0; continue; } if ($flg==1) return $args; else return array_diff($header, $args); } //}}} // combines arrays of different sizes by special paddings. Code from comment on PHP Manual:array_combine function fxc_Array_Combine_Special( $a, $b, $pad = TRUE ) { $acount = count($a); $bcount = count($b); // more elements in $a than $b but we don't want to pad either if (!$pad) { $size = ($acount > $bcount) ? $bcount : $acount; $a = array_slice($a, 0, $size); $b = array_slice($b, 0, $size); } else { // more headers than row fields if ($acount > $bcount) { $more = $acount - $bcount; // Add empty strings to ensure arrays $a and $b have same number of elements $more = $acount - $bcount; for($i = 0; $i < $more; $i++) { $b[] = " "; //HB: add space, not empty string, so replacement variable can match something! } // more fields than headers } else if ($acount < $bcount) { $more = $bcount - $acount; // fewer elements in the first array, add extra keys for($i = 0; $i < $more; $i++) { $key = 'extra_0' . $i; $a[] = $key; } } } return array_combine($a, $b); } //}}} function fxc_Get_Row_Key( $rows, $idx, $sep ) { $idnr = array(); $cnt = count($rows); for($k=1; $k < $cnt; $k++) { $r = explode($sep,$rows[$k]); if (is_numeric($r[0])) { if ($r[0]==$idx) return $k; //return row key $idnr[] = $r[0]; } } return $idx; //?? return idx if no row key is found } //}}} function fxc_PTV_Sums( $pagename, $fx ) { global $FoxCSVConfig, $FoxCSVDebug; $suffix = $FoxCSVConfig['ptvsum-suffix']; //default is _SUM $text = fxc_Get_Text( $pagename, $fx['target']); if ($text=='') return "'''Error:''' cannot get source text from '''$source'''"; $text = trim($text,"\r\n"); $opt = array(); list($sep, $nl, $opt) = fxc_Set_sep_nl($text, $opt); $data = fxc_Parse_CSV($text, $sep, $nl); if (empty($data)) FoxAbort($pagename, "'''Error:''' Parsing csv data failed!"); $header = array_shift($data); $numh = (!empty($fx['num']))? fxc_Fields($fx['num'],$header) : $header; $header = array_flip($header); $colsums = array(); foreach($numh as $k=>$hd) { if ($hd=='IDX') continue; $col = array_column($data, $header[$hd]); $colsums[$hd] = $fx['ptv_'.$hd.$suffix] = fxc_Array_Sum($col,$hd,$fx); } if ($FoxCSVDebug>0) show($colsums,'colsums: '); return $fx; } //}}} // csv action button markup (:csv- [target=]{$$SOURCE} idx={$$IDX} label=' X ']} Markup('foxcsvact','directives', '/\(:csv-(index|reindex|reformat|trim|quote|table|coldel|newcol|ptvsums|import|export)\\s+(.*?)\\s*:\)/', "fxc_Action_Form_Fmt"); # Creates the HTML output for csv action buttons function fxc_Action_Form_Fmt( $m ) { global $FoxCSVConfig, $ScriptUrl, $EnablePathInfo; extract($GLOBALS['MarkupToHTML']); $act = $m[1]; $opt = ParseArgs($m[2]); unset($opt['#']); $opt[''] = (array)@$opt['']; # show($opt,'opt actbtn: '); if ($act=='import') $filename = array_shift($opt['']); $target = $opt['target'] ?? array_shift($opt['']) ?? $pagename; if ($target=='') return; if ((empty($opt['idx']) || $opt['idx']=='IDX') && ($act=='del' || $act=='delete')) return; $idx = $opt['idx'] ?? ''; $csum = '$[Table updated]'; $imageurl = ''; // set labels, titles, messages switch ($act) { case 'del': case 'delete': $imageurl = $FoxCSVConfig['deletebuttonurl']; $label = 'X'; $title = "$[Delete row] $idx"; $onclickmessage = '$[Please confirm: Do you want to delete this csv row?]'; $csum = '$[Table row deleted]'; break; case 'index': $label = '$[Index]'; $title = "$[Index] $target"; $onclickmessage = '$[Please confirm: Do you want to index this csv table?]'; $csum = '$[CSV Table Index added]'; break; case 'reindex': $label = '$[Re-Index]'; $title = "$[Re-index] $target"; $onclickmessage = '$[Please confirm: Do you want to reindex this csv table?]'; $csum = '$[CSV Table re-indexed]'; break; case 'reformat': $label = '$[Reformat]'; $title = "$[Reformat with new separator] $target"; $onclickmessage = '$[Please confirm: Do you want to reformat this csv table?]'; $csum = '$[CSV Table reformatted]'; break; case 'trim': $label = '$[Trim Quotes]'; $title = "$[Trim surrounding quotes and white spaces from] $target"; $onclickmessage = '$[Please confirm: Do you want to trim items of this csv table?]'; $csum = '$[CSV Items trimmed]'; break; case 'quote': $label = '$[Add Quotes]'; $title = "$[Enclose with double quote marks on] $target"; $onclickmessage = '$[Please confirm: Do you want to add quote marks to items of this csv table?]'; $csum = '$[CSV Items enclosed in quote marks]'; break; case 'table': $label = '$[Make Table]'; $title = "$[Make Table] $target"; $onclickmessage = '$[Please confirm: Do you want to convert csv table into simple table format?]'; break; case 'coldel': $label = '$[Delete Column]'; $title = "$[Delete Column on] $target"; $onclickmessage = '$[Please confirm: Do you want to delete this column from the table?]'; $csum = '$[Column deleted]'; break; case 'newcol': $label = '$[Add New Column]'; $title = "$[Add New empty Column to] $target"; $onclickmessage = '$[Please confirm: Do you want to add this new column to the table?]'; $csum = '$[New column added]'; break; case 'ptvsums': $label = '$[Save Sums]'; $title = "$[Save Sums to] $target"; $onclickmessage = '$[Please confirm: Do you want to update the SUM PTVs for this table?]'; $csum = '$[SUM PTVs updated]'; break; case 'import': $label = '$[Import]'; $title = "$[Import] $target"; $onclickmessage = '$[Please confirm: Do you want to import the csv file?]'; $csum = '$[CSV file imported]'; break; case 'export': $label = '$[Export]'; $title = "$[Export] $target"; $onclickmessage = '$[Please confirm: Do you want to export the table to a file?]'; } // set labels and titles (tooltips) from markup parameters if (isset($opt['label'])) { $label = $opt['label']; $title = $opt['title'] ?? $label; } if (isset($opt['confirm'])) $onclickmessage = $opt['confirm']; $sep = $opt['sep'] ?? $FoxCSVConfig['sep'] ?? ''; $env = $opt['env'] ?? $FoxCSVConfig['env'] ?? 0; $decisep = $opt['decisep'] ?? $FoxCSVConfig['decisep'] ?? ''; $thousep = $opt['thousep'] ?? $FoxCSVConfig['thousep'] ?? ''; $decimals = $opt['decimals'] ?? $FoxCSVConfig['decimals'] ?? ''; $popedit = $opt['popedit'] ?? $FoxCSVConfig['popedit'] ?? ''; $col = $opt['col'] ?? ''; // sanitising target parameter, so it has group, name and possible anchor $csvfile = 0; if (preg_match('/\.(csv|txt)$/i', $target, $m)) { //file update $csvfile = 1; //file edit flag $TargetPageUrl = PUE(($EnablePathInfo) ? "$ScriptUrl/$pagename" : "$ScriptUrl?n=$pagename"); $tpn = $pagename; } else { //page /section update $tt = explode("#",$target); if (empty($tt[0])) $tpn = $pagename; else $tpn = MakePageName($pagename, $tt[0]); $target = (isset($tt[1])) ? $tpn."#".$tt[1] : $tpn; $TargetPageUrl = PUE(($EnablePathInfo) ? "$ScriptUrl/$target" : "$ScriptUrl?n=$tpn"); } // javascript delete message dialogue $onclick = ($FoxCSVConfig['popups']==true)? "onclick='return confirm(\"{$onclickmessage}\")'" : ""; // construct HTML delete button as output, additional input given via foxfilter function $out = "\n
". "". "". "". "". "". "". (!empty($opt['newsep'])? "" : ""). (!empty($decisep)? "" : ""). (!empty($thousep)? "" : ""). (!empty($decimals)? "" : ""). (!empty($popedit)? "" : ""). (!empty($opt['num'])? "" : ""). (!empty($opt['ptvtarget'])? "" : ""). ($env==1 ? "" : ""). (!empty($opt['new']) && $opt['new']=='top' ? "" : ""). ($act=='reformat' ? "NewSep:" : ""). ($act=='coldel' ? "Col:" : ""). ($act=='newcol' ? "New:" : ""). ($act=='newcol' ? " After:" : ""). "". "". ($act!='import' ? "" : ""). (!empty($imageurl) ? "" : ""). ($act=='import' ? " to:" : ""). ($act=='export' ? " to:" : ""). "
"; return Keep(FmtPagename($out,$tpn)); } //}}} $FoxFilterFunctions['csvaction'] = 'fxc_CSV_Action_Filter'; function fxc_CSV_Action_Filter ( $pagename, $fx ) { $fx['foxpage'] = $pagename; $fx['foxname'] = 'CSVUpdate'; $fx['foxaction'] = 'csv'; $fx['foxtemplate'] = 'csv'; return $fx; } //}}} // gets text content from csv file and fills template $FoxFilterFunctions['csvimport'] = 'fxc_Import_Filter'; function fxc_Import_Filter ( $pagename, $fx) { if (!preg_match('/\.(csv|txt)$/i',$fx['filename'],$m)) FoxAbort($pagename, "$[Error: Wrong file type!]"); $text = fxc_Get_File_Content($pagename, $fx['filename']); if (strtolower($m[1])=='csv') $tmpl = "(:csv #data:)\n(:if false:)\n[[#data]]\n".$text."\n[[#dataend]]"; //text into data section and added csv markup else $tmpl = $text; //plain text import $fx['foxtemplate'] = $tmpl; $fx['csum'] = 'CSV file import'; return $fx; } //}}} // pre-process input // if submit post2 button is used, 'saveasnew' option is processed for correct placement // with calls to fxc_Make_CSV_Item() quoted text gets double quoted, newrow replaced with tokem, items with separator characters enclosed with tokens function fxc_Preprocess_Input($pagename, &$fx) { global $FoxCSVDebug, $FoxMsgFmt, $FoxCSVConfig; if ($FoxCSVDebug>0) { echo "fxc_Preprocess_Input BEGIN>"; show($fx,'fx pre-process: ');} if ($fx['csvact']=='export') { $GLOBALS['EnableFoxDefaultMsg'] = 0; return; } //called by (:csv-ptvsums ...:) csv action to save sums into PTVs if ($fx['csvact']=='ptvsums') { $fx['foxaction'] = 'ptv'; if (empty($fx['ptvtarget'])) $fx['ptvtarget'] = $fx['target'].'-sums'; $fx = fxc_PTV_Sums($pagename, $fx); unset($fx['target']); unset($fx['foxtemplate']); unset($fx['redir']); if ($FoxCSVDebug>2) { show($fx,'fx pre-process ptvsums: '); exit; } return $fx; } unset($fx['put']); // 'Save as New' post (submit post2 button): fix csvidx and IDX to treat as addnew in FoxCSV_Update() if (isset($fx['post2'])) { if (!isset($fx['saveasnew'])) $fx['saveasnew'] = $FoxCSVConfig['saveasnew']; if ($fx['saveasnew']==0) FoxAbort($fx['foxpage'], "Error: 'Save as New' is not enabled!"); else { if ($fx['saveasnew']==1) $fx['saveasnew'] = 'bottom'; $fx['csvact'] = 'addnew'; //process saveasnew as addnew $idx = (isset($fx['IDX'])) ? $fx['IDX'] : $fx['csvidx']; foreach(array('bottom','top','below','above') as $n) if ($fx['saveasnew']==$n) $idx = $n.$idx; if (isset($fx['IDX'])) $fx['IDX'] = $idx; $fx['csvidx'] = $idx; } } // get header field names and sep from template (reverse engineer) // correct any field input from these names $temp = $fx['foxtemplate']; $env = (!empty($fx['csvenv'])) ? 1 : 0; $nl = $fx['csvnl'] ?? $FoxCSVConfig['nl'] ?? '¦'; if (preg_match_all('~\{\$\$(.*?)\}(.)?~', $temp, $m)) { $sep = $m[2][0]; if (isset($fx['csvsep']) && $fx['csvsep']!=$sep) $FoxMsgFmt[] = "WARNING: Seperator mismatch! Please check!"; foreach($m[1] as $k=>$name) { if (!empty($fx[$name])) { $fx[$name] = fxc_Make_CSV_Item($fx[$name], $sep, $nl, $env); } } } if ($FoxCSVDebug>0) show($fx,'fx pre-process END: '); else if ($FoxCSVDebug>2) { show($fx,'fx pre-process END: '); exit; } return; } //}}} // processing various csv-modifying actions via parameter 'csvact=...' from a fox form (i.e. from fxc_Action_Form_Fmt) // !called via 'foxaction=csv' from FoxProcessTargets() in fox.php! function FoxCSV_Update( $pagename, $text, $newrow, $fx ) { global $FoxDebug, $FoxCSVDebug, $FoxCSVConfig, $FoxMsgFmt; if($FoxDebug) { echo "
FoxCSV_Update> "; show($fx['csvidx'],'row index:'); show($newrow,'new line:'); } if (empty($fx['csvact'])) { $FoxMsgFmt[] = "Error: no csv update action provided"; return $text; } $idx = $fx['csvidx']; $env = $fx['csvenv'] ?? ''; $nl = $fx['csvnl'] ?? $FoxCSVConfig['nl']; if (isset($fx['csvfile']) && $fx['csvfile']==1) $text = fxc_Get_Text($pagename, $fx['target']); $text = trim($text,"\n\r"); list($sep, $nl, $fx) = fxc_Set_sep_nl($text, $fx); if ($sep=='\t') $sep = "\t"; $rows = explode("\n", $text); $rows = fxc_Remove_Empty_Rows($rows, $sep); $cnt = count($rows); if($FoxDebug >1) show($rows,'rows old:'); if (empty($rows)) return $text; // make idx for new item for custom form submissions if ($fx['csvact']=='addnew') { if (!preg_match('/^([a-z]+)(\\d+)?/',$idx,$m)) FoxAbort($fx['foxpage'], "%red%Error: item parameter nor recognised for adding!"); switch ($m[1]) { case 'bottom': //add new at bottom case 'new': $idx = $cnt; break; case 'top': //add new at top case 'newtop': $idx = 0; break; case 'below': //add new below row case 'above': //add new above row $idx = (isset($fx['IDX'])) ? fxc_Get_Row_Key($rows, $m[2], $sep) : $m[2]; } if ($m[1]=='above') $idx--; //IDX fields cases: (new row needs IDX written) if (isset($fx['IDX'])) { //put new IDX in row $next = fxc_Get_Max_Index ($rows, $idx, $fx['sep']) + 1; $fx['IDX'] = $nIDX = $next; $newrow = preg_replace('/^([a-z]+)(\\d+)?/', $nIDX, $newrow); } } switch ($fx['csvact']) { // add a new record after row $idx case 'addnew': $pos = (int)$idx +1; array_splice($rows, $pos, 0, $newrow); // auto re-index when new item added if (isset($fx['IDX']) && isset($fx['reindex'])) { $rows = fxc_Reindex($rows, $sep, $fx); } break; // update single row (replace original row) case 'replace': if ($idx != '0') $rows[$idx] = $newrow; else { // idx==0 normalise header fields! $row = fxc_Normalise_Header(str_getcsv($newrow, $sep)); foreach($row as &$it) $it = fxc_Make_CSV_Item($it, $sep, $nl, $env); $rows[0] = implode($sep, $row); } break; // remove single row according to idx given. row number can be different from idx number for indexed tables case 'del': case 'delete': $key = fxc_Get_Row_Key($rows, $idx, $sep); unset($rows[$key]); $FoxMsgFmt[] = "CSV row deleted"; break; // add a first field with index numbers and 'IDX' in the header row case 'index': if (preg_match('/^IDX/', $rows[0])) FoxAbort($fx['redir'], "Error: IDX field already present, table may need re-indexing instead. No index added."); $rows[0] = "IDX".$sep.$rows[0]; for ( $i=1; $i < $cnt; $i++ ) { $n = (isset($fx['reverseindex'])) ? $cnt-$i : $i; $rows[$i] = $n.$sep.$rows[$i]; } $FoxMsgFmt[] = "CSV table index added"; break; // refresh index numbers if csv table has index (IDX column as first column) case 'reindex': $rows = fxc_Reindex($rows, $sep, $fx); $FoxMsgFmt[] = "CSV table reindexed"; break; // replace separator with new separator case 'reformat': if (empty($fx['newsep']) OR strlen($fx['newsep'])>1) { FoxAbort($fx['redir'], "Error: new separator 'newsep' missing or bad. Reformat failed."); return $text; } if ($fx['newsep']=='\t') $fx['newsep'] = "\t"; foreach($rows as &$row) { $row = str_getcsv($row, $sep, "\"", "`"); foreach($row as &$it) $it = fxc_Make_CSV_Item($it, $sep, $nl, $env); $row = implode($fx['newsep'], $row); } $FoxMsgFmt[] = "CSV table reformatted"; break; // rewrite csv data table with PmWiki simple table markup case 'table': $rows[0] = "||class='sortable csvtable'\n||!".preg_replace("~(\s?$sep\s*)~", " ||!", $rows[0])." ||"; $rows[0] = str_replace("IDX", $FoxCSVConfig['idxname'], $rows[0]); for($k=1; $k (int)$hdcnt) $colnum = $hdcnt; //column number should not be higher than count, i.e. just one higher than actual //for each row: make csv array, slice into two, add the new between and rebuild row foreach($rows as $k=>$v) { $row = str_getcsv($v, $sep); foreach($row as &$it) $it = fxc_Make_CSV_Item($it, $sep); $rwa = array_slice($row,0,(int)$colnum); $rwb = array_slice($row,(int)$colnum); $new = ($k==0) ? $name : ""; $a = (isset($rwa[0])) ? trim(implode($sep,$rwa)).$sep : ''; $b = (isset($rwb[0])) ? $sep.trim(implode($sep,$rwb)) : ''; $rows[$k] = $a.$new.$b; } $FoxMsgFmt[] = "CSV table column added"; break; // save all to csv file case 'export': $fx['target'] = $fx['exportto']; $FoxMsgFmt[] = "Success: exported text to file {$fx['exportto']}"; break; } if ($FoxCSVDebug>1) { show($rows[$idx],"Fox_CSV_Update end: rows[$idx]: "); } else if ($FoxDebug >1) show($rows,'rows new: '); //rebuild text from rows array $text = implode("\n", $rows); $text = rtrim($text); if ($FoxCSVDebug>0) show($text,'text to be saved:
'); if ($FoxCSVDebug>1) { echo "FoxCSV_Update> STOPPED before CSV save!"; exit; //stop; otherwise page save and reload, and no debug info shows! } if (!empty($fx['csvfile'])) { if ($GLOBALS['FoxCSVConfig']['fileedit']==0) FoxAbort($fx['redir'],"Error: file editing is not allowed"); else fxc_Write_To_File($pagename, $fx['target'], $text); return ''; } return $text; } //}}} function fxc_Reindex( &$rows, $sep, $fx ) { if (preg_match("/^(?!IDX$sep).*/", $rows[0])) { FoxAbort($fx['redir'], "Error: IDX field missing, table may need indexing first. Reindex failed"); return $text; } $cnt = count($rows); for ( $i=1; $i<$cnt; $i++ ) { $n = (isset($fx['reverseindex'])) ? $cnt-$i : $i; if (preg_match("/^([\d]+$sep)/", $rows[$i], $m)) $rows[$i] = preg_replace('/^([\d]+)/', $n, $rows[$i]); else { FoxAbort($fx['redir'], "Error: check row ".($i+1)." for invalid IDX entry! Reindex failed"); return $text; } } return $rows; } //}}} function fxc_Get_Max_Index ($rows, $idx, $sep) { $idxarr = array(); foreach ($rows as $i => $r) $idxarr[$i] = intval(substr($r,0,strpos($r,$sep))); return max($idxarr); } //}}} ////----- FoxCSVEdit 2026-03-03 -----////// //FoxEdit Globals $EditSource = $EditTarget = $EditBase = $EditSection = $EditItem = $EditAction = $CSVAction = ''; //init; will be set by form $FmtPV['$EditSource'] = '$GLOBALS["EditSource"]'; $FmtPV['$EditTarget'] = '$GLOBALS["EditTarget"]'; $FmtPV['$EditSection'] ='$GLOBALS["EditSection"]'; $FmtPV['$EditItem'] ='$GLOBALS["EditItem"]'; $FmtPV['$EditBase'] ='$GLOBALS["EditBase"]'; $FmtPV['$CSVAction'] ='$GLOBALS["CSVAction"]'; // make csv edit form by setting page vars and loading edit form $HandleActions['foxcsvedit'] = 'fxc_Edit_Form'; function fxc_Edit_Form($pagename, $auth) { global $FoxCSVConfig, $FoxCSVDebug, $EditSource, $EditTarget, $EditBase, $EditSection, $EditItem, $CSVAction, $InputValues, $HTMLHeaderFmt; $args = RequestArgs($_POST); // fetch POST arguments if (empty($args['source'])) FoxAbort($pagename,"%red%'''Error:''' no source given"); if ($GLOBALS['FoxCSVDebug']>1) show($args,'fxc_Edit_Form args'); // initialising variables $section = $args['section'] ?? ''; // check for file edit or page (section) edit if (preg_match('/\.(csv|txt)$/i', $args['source'], $m)) { $source = $args['source']; $EditSource = $target = $source; $csvfile = 1; // file edit flag, will be passed on at form submit to Fox processing } else { $csvfile = 0; $ss = explode('#', $args['source']); $source = ($ss[0]=='') ? $pagename : $ss[0]; if (isset($ss[1])) { $section = "#".$ss[1]; } } $EditSource = $source; $EditSection = $section; $idx = $args['idx'] ?? ''; if (empty($idx)) FoxAbort($pagename,"%red%'''Error:''' no idx given"); $target = $args['target'] ?? $source; $EditTarget = MakePagename($pagename, $target); $EditBase = $base = $args['base'] ?? $EditTarget; $fulltarget = (isset($section) && strstr($section,'#')) ? $target.$section : $target; // open target page or file, get text section, set InputValues for 'text' and 'mark' controls $text = fxc_Get_Text( $pagename, $fulltarget); if (empty($text)) FoxAbort($pagename,"%red%$[Error: cannot find data section] $fulltarget"); // get rows array from text $text = trim($text,"\n\r"); $rows = explode("\n", $text); //get rows // get csv separator/delimiter list($sep, $nl, $args) = fxc_Set_sep_nl( $text, $args); if ($idx=='header') $idx = 0; $data = fxc_Parse_CSV( $text, $sep, $nl ); #show($data); $rowcnt = count($data); $header = $data[0]; $multi = (isset($args['multiline'])) ? fxc_Fields($args['multiline'], $header) : array(); foreach ($header as $k=>$v) { if (preg_match('/^[\d]|[^\-a-zA-Z0-9_]/', $v, $m)) FoxAbort($pagename, "'''Error: invalid header name(s)!''' (No digits at beginning, no spaces, no punctuations)"); } // row key is row number, not necessarily idx number! $idxtitle = " $idx"; $idx0 = $idx; $put = 'bottom'; //default //add new row (idx is word or word with number: top, newtop, bottom, new, below, above) //rowkey needs to be determined at submission in Fox_CSV_Update() if (preg_match('/^([a-z]+)(\\d+)?/', $idx, $m)) { $act = 'addnew'; if ($m[1]=='bottom' || $m[1]=='new') { //add new at bottom $idxtitle = " to bottom"; } else if ($m[1]=='top' || $m[1]=='newtop') { //add new at top $idxtitle = " to top"; } else if ($m[1]=='below') { //add new below row $idxtitle = " below item $m[2]"; } else if ($m[1]=='above') { $idxtitle = " above item $m[2]"; } $key = $idx; //full idx with keyword needs transmission at submission } else { // edit existing item and replace it with the edit $act = 'replace'; $EditItem = $key = ($header[0]=='IDX') ? fxc_Get_Row_Key($rows, $idx, $sep) : $idx; if ($idx>$rowcnt) FoxAbort($pagename,"%red%Error: idx given is too high!"); } if (!array_key_exists($key, $data)) $item = array(); // new item else $item = $data[$key]; // existing item // set InputValues for the item (csv data from row) to fill form's text fields [input defaults requests=1] foreach($item as $k => $value) { $fn = $header[$k] ?? ''; if (!isset($InputValues[$fn])) $InputValues[$fn] = trim(str_replace('$','$',htmlspecialchars($value,ENT_NOQUOTES))); } //use a custom edit form via form= parameter if (isset($args['form'])) { // if popedit=1 inject styles important for popup edit form. if (isset($args['popedit'])) fxc_Set_Editform_Styles($args); $eform = fxc_Get_Custom_Edit_Form($pagename, $args['form']); if (!empty($args['csvtitle'])) $eform = preg_replace('/\{\$\$TITLE\}/',$args['csvtitle'], $eform); $eform = "\n(:div0 id=csvedit:)".$eform."\n(:div0end:)"; //wrapper for custom popedit return fxc_Display_Editform($pagename, $eform, $args, $auth); // return ends this handling of foxcsvedit action } //no custom edit form, use pop (fixed position) or classic hardcoded forms fxc_Set_Editform_Styles($args); // build edit form $eform, // common part for popedit and classic form $etitle = ($args['csvtitle']) ?? $fulltarget; $eform = "\n(:div0 id=csvedit:)". "(:fox eform foxaction=csv put=$put csvact=$act csvidx=$key target=$fulltarget :)"; if ($act=='addnew') $eform .= "(:input hidden saveasnew $put:)"; // set template $tv= ''; foreach ($header as $n) $tv .= "{\$\$".$n."}$sep"; //no space after seperator! $eform .= "(:foxtemplate \"".rtrim($tv,$sep)."\":)"; //trim trailing sep! trailing sep stays from text row! $eform .= "(:input defaults request=1:)(:input hidden csum '$[Updated CSV Table]':)" ."(:input hidden csvsep $sep:)(:input hidden csvnl $nl:)". (isset($args['autoreindex']) ? "(:input hidden autoreindex 1:)" : ''). (($csvfile==1)? "(:input hidden csvfile $csvfile:)":''). (isset($args['env']) ? "(:input hidden csvenv 1:)" : ''); $submit = "\n(:div1 class='ebuttons':)(:input submit post '$[Save]' class=inputbutton:)". (isset($args['saveasnew']) ? "  (:input submit post2 '$[Save as New]' class=inputbutton:)(:input hidden saveasnew ".$args['saveasnew'].":)": ''). "  (:input submit cancel '$[Cancel]' class=inputbutton:)\n(:div1end:)"; $eform .= $submit; $eform .= ($act=='addnew' ? "\n(:div2 id=etitle:)$[Adding record] " : "\n(:div2 id=etitle:)$[Editing record] "). ($idx0==0 ? "$[Header]": $idxtitle)." $[of] $etitle \n(:div2end:)"; //setup eform input fields $ehead = $header; // set TMX for time stamp as hidden input if (in_array('TMX',$ehead)) { $eform .= "(:input hidden TMX '{\$\$(ftime %s)}':)"; if (empty($args['eidx'])) { if (($key=array_search('TMX',$ehead)) !== false) unset($ehead[$key]); } } // move IDX to hidden input if (in_array('IDX',$ehead)) { $eform .= "(:input hidden IDX $idx:)"; if (empty($args['eidx'])) { if (($key=array_search('IDX',$ehead)) !== false) unset($ehead[$key]); } } //make edit fields from 'eshow' arg list, put all others into hidden inputs if (isset($args['eshow'])) { $eshow = fxc_Fields($args['eshow'], $ehead); foreach ($ehead as $name) if (!in_array($name, $eshow)) $eform .= "(:input hidden $name:)"; $ehead = $eshow; } //end eform init and field setup // popup edit form with horizontal column layout if (isset($args['popedit'])) { // css column layout according to field count $cmax = $args['popmax'] ?? $FoxCSVConfig['popmax'] ?? 10; $FC = count($ehead); // if ($FC > $cmax) $FC = $cmax; if (isset($args['popcols'])) $colwidth = explode(',',$args['popcols']); if (isset($args['poprows'])) { $theight = $args['poprows'] * 1.25; $GLOBALS['HTMLStylesFmt']['poprows'] = "#epoptable textarea { height:{$theight}em;}"; } // make div with text input or textarea fields filled with values $chunks = array_chunk($ehead,$FC); $eform .= Keep("\n"); foreach($chunks as $k => $erow) { foreach ($erow as $i => $name) { //table columns $n = $k + $i; $cw = (isset($colwidth))? "style='width:$colwidth[$n]%;'" : ''; $eform .= Keep("\n"); } $eform .= Keep("\n"); foreach ($erow as $i => $name) { //field labels "); } $eform .= Keep("\n"); foreach ($erow as $i => $name) { //field inputs "); elseif (in_array($name, $multi) OR (isset($InputValues[$name]) && preg_match('/\n/',$InputValues[$name]))) $eform .= Keep("\n"); else $eform .= Keep("\n"); } $eform .= Keep("\n"); } $eform .= Keep("
if ($name=='IDX' && !empty($args['eidx'])) $name = $args['eidx']; $eform .= "\n".Keep("$name
$focus = ($i==1) ? "autofocus" : ''; if ($act=='addnew') $InputValues[$name] = ''; $n = $k + $i; if ($name=='IDX' && !empty($args['eidx'])) $eform .= Keep("\n
")."\n"; //form end } //end eform popup // classic edit form with vertical layout, not popup else { $eform .= Keep("\n \n\n"); foreach ($ehead as $i => $name) { $focus = ($i==1) ? "autofocus" : ''; if ($act=='addnew') $InputValues[$name] = ''; if ($name=='IDX' && !empty($args['eidx'])) $eform .= Keep("\n"); elseif (in_array($name, $multi) OR (isset($InputValues[$name]) && preg_match('/\n/',$InputValues[$name]))) $eform .= Keep("\n\n"); else $eform .= Keep("\n\n"); } #buttons at bottom: $eform .= Keep("\n"); $eform .= Keep("\n
{$args['eidx']}
$name\n
$name\n
").$submit.Keep("
")."\n"; //form end } //end eform classic fxc_Display_Editform($pagename, $eform, $args, $auth); } //}}} //injection style css into page HTML head //some essential for popedit to get fixed position! function fxc_Set_Editform_Styles($args) { global $HTMLStylesFmt, $FoxCSVConfig; if (isset($args['popedit'])) { $position = (!empty($args['popbottom']))? 'bottom:0' : 'top:0'; $HTMLStylesFmt['csveditpopessential'] = "#csvedit { position:fixed; $position; left:0; width:99vw; z-index:20; padding:.4em; margin:0;} .ebuttons { float:right;} #epoptable { width:100%; margin:0; padding:0;} #etitle {font-size:1.2em; margin-left:.4em;} .efield {vertical-align:top;} .elabel { text-align:left; } .csvinput { width:100%; min-width:2em; max-width:100%; padding:0;} "; SDV($HTMLStylesFmt['csvedit'], " #csvedit {background-color:hsl(50 100 97); border:2px solid hsl(50 100 60); } .ebuttons .inputbutton { background:hsl(40 100 90); border-radius:5px; } .ebuttons .inputbutton:hover { background:hsl(40 100 80); cursor:pointer; } " ); } else SDV($HTMLStylesFmt['csveditclassic'], "#csvedit {width:97%; margin:.5em; padding:.4em; } .ebuttons {float:right; margin-right:2em;} td .ebuttons { float:left; margin-top:.25em;} #eclassictable {width:80%; margin:0.5em;} #eclassictable textarea { height:6em;} #ecolLabel {width:15%;} #ecolInput {width:60%;} #etitle {font-size:1.2em; margin-left:.4em; font-weight:600;} .efield {vertical-align:top;} .elabel {text-align:right; padding-right:.25em;} .csvinput {width:100%; min-width:2em; max-width:100%; } " ); return; } //}}} //retrieve edit form from page or page section function fxc_Get_Custom_Edit_Form($pagename, $formpage) { if (empty($formpage)) return; $eform = ''; if (substr($formpage,0,1)=='#') $formpage = $pagename.$formpage; $formname = MakePagename($pagename, $formpage); if (PageExists($formname)) { $text = RetrieveAuthSection($formname, $formpage); if(empty($text)) FoxAbort($pagename,"$[Error: cannot find edit template] $formpage"); $eform = "\n(:div11 class=csveditform:)\n".$text."\n(:div11end:)"; } return $eform; } //}}} function fxc_Display_Editform($pagename, $eform, $args, $auth) { global $FoxCSVConfig, $EditBase, $FmtV, $FoxEditForm, $PageStartFmt, $FoxPageEditFmt, $PageEndFmt; //popedit adds text of base page (the calling page) after eform if (!empty($args['popedit'])) { $basepage = MakePagename($pagename, $EditBase); if (PageExists($basepage)) $text = RetrieveAuthSection($basepage, $basepage); $eform = '(:groupheader:)'.$eform.@$text.'(:groupfooter:)'; } //all Markup to HTML and print to screen $FmtV['$FoxEditFrm'] = MarkupToHTML($pagename, $eform).""; $FoxPageEditFmt = '$FoxEditFrm'; $HandleEditFmt = array(&$PageStartFmt, $FoxPageEditFmt, &$PageEndFmt); PrintFmt($pagename, $HandleEditFmt); } //}}} // row edit (:csv-edit [] [