<?php
/* $Revision: 1.3 $ */
// Include functions needed for ReportCreator.php
function PrepStep($StepNum) {
// This function sets the titles and include information to prepare for the defined step number
switch ($StepNum) {
case '1': // home form with form listings
default:
$FormParams['title'] = RPT_RPRBLDR.RPT_STEP1;
$FormParams['heading'] = RPT_ADMIN;
$FormParams['IncludePage'] = 'forms/ReportsHome.html';
break;
case '2': // id, copy, new report name form
$FormParams['title'] = RPT_RPRBLDR.RPT_STEP2;
$FormParams['heading'] = RPT_RPTID;
$FormParams['IncludePage'] = 'forms/ReportsID.html';
break;
case '3': // page setup form
$FormParams['title'] = RPT_RPRBLDR.RPT_STEP3;
$FormParams['heading'] = RPT_RPTFRM;
$FormParams['IncludePage'] = 'forms/ReportsPageSetup.html';
break;
case '4': // db setup form
$FormParams['title'] = RPT_RPRBLDR.RPT_STEP4;
$FormParams['heading'] = RPT_RPTFRM;
$FormParams['IncludePage'] = 'forms/ReportsDBSetup.html';
break;
case '5': // field setup form
$FormParams['title'] = RPT_RPRBLDR.RPT_STEP5;
$FormParams['heading'] = RPT_RPTFRM;
$FormParams['IncludePage'] = 'forms/ReportsFieldSetup.html';
break;
case 'prop': // Form field properties form
global $Params; // we need the form type from the Params variable to load the correct form
$FormParams['title'] = RPT_RPRBLDR.RPT_BTN_PROP;
$FormParams['heading'] = RPT_RPTFRM;
$FormParams['IncludePage'] = 'forms/TplFrm'.$Params['index'].'.html';
break;
case '6': // criteria setup form
$FormParams['title'] = RPT_RPRBLDR.RPT_STEP6;
$FormParams['heading'] = RPT_RPTFRM;
$FormParams['IncludePage'] = 'forms/ReportsCritSetup.html';
break;
case 'imp': // import form
$FormParams['title'] = RPT_RPRBLDR.RPT_RPTIMPORT;
$FormParams['heading'] = RPT_RPTIMPORT;
$FormParams['IncludePage'] = 'forms/ReportsImport.html';
break;
} // end switch $StepNum
return $FormParams;
}
function RetrieveReports() {
global $db, $ReportGroups, $FormGroups;
$OutputString = '';
foreach ($ReportGroups as $key=>$GName) {
$OutputString .= '<tr bgcolor="#CCCCCC"><td colspan="2" align="center">'.$GName.'</td></tr>';
$OutputString .= '<tr><td align="center">'.RPT_REPORTS.'</td><td align="center">'.RPT_FORMS.'</td></tr>';
$OutputString .= '<tr><td width="250" valign="top">';
$sql= "SELECT id, reportname FROM ".DBReports."
WHERE defaultreport='1' AND reporttype='rpt' AND groupname='".$key."'
ORDER BY reportname";
$Result=DB_query($sql,$db,'','',false,true);
while ($Temp = DB_fetch_array($Result)) $OutputString .= '<input type="radio" name="ReportID" value="'.$Temp['id'].'">'.$Temp['reportname'].'<br>';
$sql= "SELECT id, reportname FROM ".DBReports."
WHERE defaultreport='0' AND reporttype='rpt' AND groupname='".$key."'
ORDER BY reportname";
$Result=DB_query($sql,$db,'','',false,true);
if (DB_num_rows($Result)>0) $OutputString .= '<u>'.RPT_CUSTRPT.'</u><br>';
while ($Temp = DB_fetch_array($Result)) $OutputString .= '<input type="radio" name="ReportID" value="'.$Temp['id'].'">'.$Temp['reportname'].'<br>';
$OutputString .= '</td>'.chr(10).'<td width="250" valign="top">';
$sql= "SELECT id, groupname, reportname FROM ".DBReports."
WHERE defaultreport='1' AND reporttype='frm'
ORDER BY groupname, reportname";
$Result=DB_query($sql,$db,'','',false,true);
$FormList = '';
while ($Temp = DB_fetch_array($Result)) $FormList[] = $Temp;
foreach ($FormGroups as $index=>$value) {
$Group=explode(':',$index); // break into main group and form group array
if ($Group[0]==$key AND $FormList<>'') { // then it's a part of the group we're showing
$WriteOnce = true;
foreach ($FormList as $Entry) {
if ($Entry['groupname']==$index) { // then it's part of this listing
if ($WriteOnce) { $OutputString .= $value.'<br>'; $WriteOnce=false; }
$OutputString .= ' <input type="radio" name="ReportID" value="'.$Entry['id'].'">'.$Entry['reportname'].'<br>';
}
}
}
}
$OutputString .= '</td></tr>';
}
return $OutputString;
}
function RetrieveFields($EntryType) {
global $db, $ReportID;
$FieldListings['fields'] = '';
$sql= "SELECT * FROM ".DBRptFields."
WHERE reportid = ".$ReportID." AND entrytype = '".$EntryType."'
ORDER BY seqnum";
$Result=DB_query($sql,$db,'','',false,true);
if (DB_num_rows($Result)>0) {
while ($FieldValues = DB_fetch_array($Result)) { $FieldListings['lists'][] = $FieldValues; }
}
// set the form field defaults
$FieldListings['defaults']['seqnum'] = '';
$FieldListings['defaults']['fieldname'] = '';
$FieldListings['defaults']['displaydesc'] = '';
$FieldListings['defaults']['visible'] = '';
$FieldListings['defaults']['columnbreak'] = '';
$FieldListings['defaults']['params'] = '';
$FieldListings['defaults']['buttonvalue'] = RPT_BTN_ADDNEW;
return $FieldListings;
}
function UpdatePageFields($ReportID) {
global $db, $Type;
// For both reports and forms start sql string
$sql = "UPDATE ".DBReports." SET
papersize = '".$_POST['PaperSize']."',
paperorientation = '".$_POST['PaperOrientation']."',
margintop = ".$_POST['MarginTop'].",
marginbottom = ".$_POST['MarginBottom'].",
marginleft = ".$_POST['MarginLeft'].",
marginright = ".$_POST['MarginRight'];
// the checkboxes to false if not checked
if ($Type<>'frm') { // then it's a report, ad more info
if (!isset($_POST['CoyNameShow'])) $_POST['CoyNameShow'] = '0';
if (!isset($_POST['Title1Show'])) $_POST['Title1Show'] = '0';
if (!isset($_POST['Title2Show'])) $_POST['Title2Show'] = '0';
$sql .= ", coynamefont = '".$_POST['CoyNameFont']."',
coynamefontsize = ".$_POST['CoyNameFontSize'].",
coynamefontcolor = '".$_POST['CoyNameFontColor']."',
coynamealign = '".$_POST['CoyNameAlign']."',
coynameshow = '".$_POST['CoyNameShow']."',
title1desc = '".addslashes($_POST['Title1Desc'])."',
title1font = '".$_POST['Title1Font']."',
title1fontsize = ".$_POST['Title1FontSize'].",
title1fontcolor = '".$_POST['Title1FontColor']."',
title1fontalign = '".$_POST['Title1FontAlign']."',
title1show = '".$_POST['Title1Show']."',
title2desc = '".addslashes($_POST['Title2Desc'])."',
title2font = '".$_POST['Title2Font']."',
title2fontsize = ".$_POST['Title2FontSize'].",
title2fontcolor = '".$_POST['Title2FontColor']."',
title2fontalign = '".$_POST['Title2FontAlign']."',
title2show = '".$_POST['Title2Show']."',
filterfont = '".$_POST['FilterFont']."',
filterfontsize = ".$_POST['FilterFontSize'].",
filterfontcolor = '".$_POST['FilterFontColor']."',
filterfontalign = '".$_POST['FilterFontAlign']."',
datafont = '".$_POST['DataFont']."',
datafontsize = ".$_POST['DataFontSize'].",
datafontcolor = '".$_POST['DataFontColor']."',
datafontalign = '".$_POST['DataFontAlign']."',
totalsfont = '".$_POST['TotalsFont']."',
totalsfontsize = ".$_POST['TotalsFontSize'].",
totalsfontcolor = '".$_POST['TotalsFontColor']."',
totalsfontalign = '".$_POST['TotalsFontAlign']."',
col1width = ".$_POST['Col1Width'].",
col2width = ".$_POST['Col2Width'].",
col3width = ".$_POST['Col3Width'].",
col4width = ".$_POST['Col4Width'].",
col5width = ".$_POST['Col5Width'].",
col6width = ".$_POST['Col6Width'].",
col7width = ".$_POST['Col7Width'].",
col8width = ".$_POST['Col8Width'];
}
$sql .=" WHERE id =".$ReportID.";";
$Result=DB_query($sql,$db,'','',false,true);
return true;
}
function UpdateCritFields($ReportID, $DateString) {
global $db, $Type;
$sql = "UPDATE ".DBRptFields." SET
reportid = '".$ReportID."',
entrytype = 'dateselect',
fieldname = '".addslashes($_POST['DateField'])."',
displaydesc = '".$DateString."',
params = '".$_POST['DefDate']."'
WHERE reportid = ".$ReportID." AND entrytype = 'dateselect';";
$Result=DB_query($sql,$db,'','',false,true);
if ($Type<>'frm') { // then write specifics for a report
// write the truncate long descriptions choice
$sql = "UPDATE ".DBRptFields." SET
reportid = '".$ReportID."',
entrytype = 'trunclong',
params = '".$_POST['TruncLongDesc']."',
displaydesc = ''
WHERE reportid = ".$ReportID." AND entrytype = 'trunclong';";
$Result=DB_query($sql,$db,'','',false,true);
} else { // it's a form update the page break info
// write the form page break fieldname
$sql = "UPDATE ".DBRptFields." SET
reportid = '".$ReportID."',
entrytype = 'grouplist',
seqnum = 1,
fieldname = '".$_POST['FormBreakField']."',
params = '',
displaydesc = ''
WHERE reportid = ".$ReportID." AND entrytype = 'grouplist';";
$Result=DB_query($sql,$db,'','',false,true);
}
return true;
}
function UpdateDBFields($ReportID) {
global $db;
// Test inputs to see if they are valid
$strTable = addslashes($_POST['Table1']);
if ($_POST['Table2']) $strTable .= ' INNER JOIN '.addslashes($_POST['Table2']).' ON '.addslashes($_POST['Table2Criteria']);
if ($_POST['Table3']) $strTable .= ' INNER JOIN '.addslashes($_POST['Table3']).' ON '.addslashes($_POST['Table3Criteria']);
if ($_POST['Table4']) $strTable .= ' INNER JOIN '.addslashes($_POST['Table4']).' ON '.addslashes($_POST['Table4Criteria']);
if ($_POST['Table5']) $strTable .= ' INNER JOIN '.addslashes($_POST['Table5']).' ON '.addslashes($_POST['Table5Criteria']);
if ($_POST['Table6']) $strTable .= ' INNER JOIN '.addslashes($_POST['Table6']).' ON '.addslashes($_POST['Table6Criteria']);
$sql = "SELECT * FROM ".$strTable." LIMIT 1";
$Result=DB_query($sql,$db,'','',false,false);
// if we have a row, sql was valid
if (DB_num_rows($Result)==0) return false;
$sql = "UPDATE ".DBReports." SET
table1 = '".addslashes($_POST['Table1'])."',
table2 = '".addslashes($_POST['Table2'])."',
table2criteria = '".addslashes($_POST['Table2Criteria'])."',
table3 = '".addslashes($_POST['Table3'])."',
table3criteria = '".addslashes($_POST['Table3Criteria'])."',
table4 = '".addslashes($_POST['Table4'])."',
table4criteria = '".addslashes($_POST['Table4Criteria'])."',
table5 = '".addslashes($_POST['Table5'])."',
table5criteria = '".addslashes($_POST['Table5Criteria'])."',
table6 = '".addslashes($_POST['Table6'])."',
table6criteria = '".addslashes($_POST['Table6Criteria'])."'
WHERE id =".$ReportID.";";
$Result=DB_query($sql,$db,'','',false,true);
return true;
}
function UpdateSequence($EntryType) {
global $db, $ReportID, $Type;
if (!isset($_POST['Visible'])) $_POST['Visible'] = '0';
if (!isset($_POST['ColumnBreak'])) $_POST['ColumnBreak'] = '0';
if (!isset($_POST['Params'])) $Params = '0'; else $Params = $_POST['Params'];
$sql = "UPDATE ".DBRptFields." SET
fieldname = '".addslashes($_POST['FieldName'])."',
displaydesc = '".addslashes($_POST['DisplayDesc'])."',
visible = '".$_POST['Visible']."',
columnbreak = '".$_POST['ColumnBreak']."' ";
// Only update params if not a form (cannot update params once initially set)
if ($Type<>'frm') $sql .= ", params = '".$Params."' ";
$sql .= "WHERE reportid = ".$ReportID." AND entrytype = '".$EntryType."' AND seqnum = ".$_POST['SeqNum'].";";
$Result=DB_query($sql,$db,'','',false,true);
return true;
}
function ChangeSequence($SeqNum, $EntryType, $UpDown) {
global $db, $ReportID;
// find the id of the row to move
$sql = "SELECT id FROM ".DBRptFields."
WHERE reportid = ".$ReportID." AND entrytype = '".$EntryType."' AND seqnum = ".$SeqNum.";";
$Result=DB_query($sql,$db,'','',false,true);
$myrow = DB_fetch_row($Result);
$OrigID = $myrow[0];
if ($UpDown=='up') $NewSeqNum = $SeqNum-1; else $NewSeqNum = $SeqNum+1;
// first move affected sequence to seqnum, then seqnum to new position
$sql = "UPDATE ".DBRptFields." SET seqnum='".$SeqNum."'
WHERE reportid = ".$ReportID." AND entrytype = '".$EntryType."' AND seqnum = ".$NewSeqNum.";";
$Result=DB_query($sql,$db,'','',false,true);
$sql = "UPDATE ".DBRptFields." SET seqnum='".$NewSeqNum."' WHERE id = ".$OrigID.";";
$Result=DB_query($sql,$db,'','',false,true);
return true;
}
function InsertSequence($SeqNum, $EntryType) {
// This function creates a hole in the sequencing to allow inserting new data
global $db, $ReportID, $Type;
if (!$SeqNum) $SeqNum = 999; // set sequence to max if not entered
// read the sequence numbers for the given EntryType
$sql = "SELECT id FROM ".DBRptFields."
WHERE reportid = ".$ReportID." AND entrytype = '".$EntryType."'
ORDER BY seqnum;";
$Result=DB_query($sql,$db,'','',false,true);
while ($FieldID = DB_fetch_array($Result)) { $IDList[] = $FieldID['id']; }
$NumRows = DB_num_rows($Result);
if (!$IDList OR ($NumRows < $SeqNum)) { $SeqNum = DB_num_rows($Result) + 1; }
if ($SeqNum <= $NumRows) { // shift the fields down to make a sequence hole
for ($j=$SeqNum-1; $j<$NumRows; $j++) {
$sql = "UPDATE ".DBRptFields." SET seqnum = ".($j+2)." WHERE id=".$IDList[$j].";";
$Result=DB_query($sql,$db,'','',false,true);
}
}
if (!isset($_POST['Visible'])) $Visible = '0'; else $Visible = $_POST['Visible'];
if (!isset($_POST['ColumnBreak'])) $ColumnBreak = '0'; else $ColumnBreak = $_POST['ColumnBreak'];
if (!isset($_POST['Params'])) {
$Params = '0';
} elseif ($Type=='frm' AND $EntryType=='fieldlist') {
$EntryIndex['index'] = $_POST['Params'];
$Params = serialize($EntryIndex);
} else {
$Params = $_POST['Params'];
}
$sql = "INSERT INTO ".DBRptFields."
(reportid, entrytype, seqnum, fieldname, displaydesc, visible, columnbreak, params)
VALUES (".$ReportID.",'".$EntryType."',".$SeqNum.",'".addslashes($_POST['FieldName'])."',
'".addslashes($_POST['DisplayDesc'])."','".$Visible."','".$ColumnBreak."','".$Params."');";
$Result=DB_query($sql,$db,'','',false,true);
return $SeqNum;
}
function DeleteSequence($SeqNum, $EntryType) {
// This function removes a sequence field and fills the sequence hole left behind
global $db, $ReportID;
// delete the sequence number from the list
$sql = "DELETE FROM ".DBRptFields."
WHERE reportid = ".$ReportID." AND entrytype = '".$EntryType."' AND seqnum = ".$SeqNum.";";
$Result=DB_query($sql,$db,'','',false,true);
// read in the remaining sequences and re-number
$sql = "SELECT id FROM ".DBRptFields."
WHERE reportid = ".$ReportID." AND entrytype = '".$EntryType."'
ORDER BY seqnum;";
$Result=DB_query($sql,$db,'','',false,true);
while ($FieldID = DB_fetch_array($Result)) { $IDList[] = $FieldID['id']; }
$NumRows = DB_num_rows($Result);
if ($NumRows >= $SeqNum) { // then not at end of list re-number sequences
for ($j=$SeqNum-1; $j<$NumRows; $j++) {
$sql = "UPDATE ".DBRptFields." SET seqnum = ".($j+1)." WHERE id=".$IDList[$j].";";
$Result=DB_query($sql,$db,'','',false,true);
}
}
return true;
}
function InsertFormSeq(&$Params, $Insert) {
// This function creates a hole in the sequencing to allow inserting new form table field data
$SeqNum = $_POST['TblSeqNum'];
if (!$SeqNum) $SeqNum = count($Params['Seq'])+1; // set sequence to last entry if not entered
if (isset($Params['Seq'][$SeqNum-1]) AND $Insert=='insert') {
// then the sequence number exists make a hole for this insert
for ($j=count($Params['Seq']); $j>=$SeqNum; $j--) {
$Params['Seq'][$j] = $Params['Seq'][$j-1]; // move the array element down one
$Params['Seq'][$j]['TblSeqNum'] = $j+1; // increment the sequence number
}
} // else it's an update which we do anyway
// Fill in the new data
$Params['Seq'][$SeqNum-1]['TblSeqNum'] = $SeqNum;
$Params['Seq'][$SeqNum-1]['TblField'] = $_POST['TblField'];
$Params['Seq'][$SeqNum-1]['TblDesc'] = $_POST['TblDesc'];
$Params['Seq'][$SeqNum-1]['Processing'] = $_POST['Processing'];
$Params['Seq'][$SeqNum-1]['Font'] = $_POST['Font'];
$Params['Seq'][$SeqNum-1]['FontSize'] = $_POST['FontSize'];
$Params['Seq'][$SeqNum-1]['FontAlign'] = $_POST['FontAlign'];
$Params['Seq'][$SeqNum-1]['FontColor'] = $_POST['FontColor'];
$Params['Seq'][$SeqNum-1]['TblColWidth'] = $_POST['TblColWidth'];
if (!isset($_POST['TblShow'])) $Params['Seq'][$SeqNum-1]['TblShow'] = '0';
else $Params['Seq'][$SeqNum-1]['TblShow'] = '1';
return true;
}
function ModFormTblEntry(&$Params) {
for ($i=1; $i<100; $i++) { // see if a button was pressed
if (isset($_POST['up'.$i.'_x']) AND $i<>1) { // sequence up[i] was pressed, swap it with the element before
$Temp = $Params['Seq'][$i-1];
$Params['Seq'][$i-1] = $Params['Seq'][$i-2];
$Params['Seq'][$i-2] = $Temp;
// update the sequence numbers
$Params['Seq'][$i-1]['TblSeqNum'] = $i;
$Params['Seq'][$i-2]['TblSeqNum'] = $i-1;
return true;
}
if (isset($_POST['dn'.$i.'_x']) AND $i<>count($Params['Seq'])) { // sequence dn[i] was pressed, swap it with the element after
$Temp = $Params['Seq'][$i-1];
$Params['Seq'][$i-1] = $Params['Seq'][$i];
$Params['Seq'][$i] = $Temp;
// update the sequence numbers
$Params['Seq'][$i-1]['TblSeqNum'] = $i;
$Params['Seq'][$i]['TblSeqNum'] = $i+1;
return true;
}
if (isset($_POST['ed'.$i.'_x'])) { // sequence ed[i] was pressed
// set the defaults to the sequence selected
// Set the form with the values from the sequence selected
$Params['TblSeqNum'] = $Params['Seq'][$i-1]['TblSeqNum'];
$Params['TblField'] = $Params['Seq'][$i-1]['TblField'];
$Params['TblDesc'] = $Params['Seq'][$i-1]['TblDesc'];
$Params['Processing'] = $Params['Seq'][$i-1]['Processing'];
$Params['Font'] = $Params['Seq'][$i-1]['Font'];
$Params['FontSize'] = $Params['Seq'][$i-1]['FontSize'];
$Params['FontAlign'] = $Params['Seq'][$i-1]['FontAlign'];
$Params['FontColor'] = $Params['Seq'][$i-1]['FontColor'];
$Params['TblColWidth'] = $Params['Seq'][$i-1]['TblColWidth'];
$Params['TblShow'] = $Params['Seq'][$i-1]['TblShow'];
return 'edit';
}
if (isset($_POST['rm'.$i.'_x'])) { // sequence rm[i] was pressed, delete the entry
for ($j=$i; $j<count($Params['Seq']); $j++) {
$Params['Seq'][$j-1] = $Params['Seq'][$j];
$Params['Seq'][$j-1]['TblSeqNum'] = $j;
}
$Temp = array_pop($Params['Seq']);
break;
}
}
return true;
}
function ValidateField($ReportID, $FieldName, $Description) {
global $db, $Type;
// This function checks the fieldname and field reference and validates that it is good.
// first check if a form (fieldname is not provided unless it's the form page break field)
if ($Type=='frm' AND $Description<>'TestField') { // then check for non-zero description unless a fieldname is present
if (strlen($Description)<1) return false; else return true;
}
// fetch the table values to build sql
$sql = "SELECT table1,
table2, table2criteria,
table3, table3criteria,
table4, table4criteria,
table5, table5criteria,
table6, table6criteria
FROM ".DBReports." WHERE id='".$ReportID."'";
$Result=DB_query($sql,$db,'','',false,true);
$Prefs = DB_fetch_assoc($Result);
// Check for a non-blank entry in the field description or fieldname
if (strlen($FieldName)<1 OR strlen($Description)<1) return false;
// Build the table to search, then test inputs to see if they are valid
$strTable = $Prefs['table1'];
if ($Prefs['table2']) $strTable .= ' INNER JOIN '.$Prefs['table2']. ' ON '.$Prefs['table2criteria'];
if ($Prefs['table3']) $strTable .= ' INNER JOIN '.$Prefs['table3']. ' ON '.$Prefs['table3criteria'];
if ($Prefs['table4']) $strTable .= ' INNER JOIN '.$Prefs['table4']. ' ON '.$Prefs['table4criteria'];
if ($Prefs['table5']) $strTable .= ' INNER JOIN '.$Prefs['table5']. ' ON '.$Prefs['table5criteria'];
if ($Prefs['table6']) $strTable .= ' INNER JOIN '.$Prefs['table6']. ' ON '.$Prefs['table6criteria'];
$sql = "SELECT ".$FieldName." FROM ".$strTable." LIMIT 1";
$Result=DB_query($sql,$db,'','',false,false);
// Try to fetch one row, if we have a row, sql was valid
if (DB_num_rows($Result)<1) return false; else return true;
}
function ReadDefReports() {
global $ReportGroups;
$dh = opendir(DefRptPath);
$i=0;
while ($DefRpt = readdir($dh)) {
$pinfo = pathinfo(DefRptPath.$DefRpt);
if ($pinfo['extension']=='txt') { // then it's a report file read name and type
$FileLines = file(DefRptPath.$DefRpt);
foreach ($FileLines as $OneLine) { // find the main reports sql statement, language and execute it
if (strpos($OneLine,'ReportData:')===0) { // then it's the line we'er after with reportname and groupname
$GrpPos = strpos($OneLine,"groupname='")+11;
$GrpName = substr($OneLine, $GrpPos, strpos($OneLine, "',", $GrpPos)-$GrpPos);
$RptPos = strpos($OneLine,"reportname='")+12;
$RptName = substr($OneLine, $RptPos, strpos($OneLine, "',", $RptPos)-$RptPos);
$ReportList[$i]['GrpName'] = $GrpName;
$ReportList[$i]['RptName'] = $RptName;
$ReportList[$i]['FileName'] = $pinfo[basename];
$i++;
}
}
}
}
closedir($dh);
$OptionList = '';
$LstGroup = '';
$CloseOptGrp = false;
$i=0;
while ($Temp=$ReportList[$i]) {
if ($Temp['GrpName']<>$LstGroup) { // then it's a new group, close old group and start new group
if ($LstGroup<>'') echo '</optgroup>';
$CloseOptGrp = true; // we need to close the last option group
$LstGroup = $Temp['GrpName'];
$OptionList .= '<optgroup label="'.$ReportGroups[$Temp['GrpName']].'" title="'.$Temp['GrpName'].'">';
}
$GrpMember = $ReportGroups[$Temp['GrpName']];
if (!$GrpMember) $Temp['GrpName'] = RPT_MISC;
$OptionList .= '<option value="'.$Temp['FileName'].'">'.$Temp['RptName'].'</option>';
$i++;
}
if ($CloseOptGrp) $OptionList .= '</optgroup>';
return $OptionList;
}
function ReadImages($Default) {
$OptionList = '';
$dh = opendir(DefRptPath);
while ($DefRpt = readdir($dh)) {
$pinfo = pathinfo(DefRptPath.$DefRpt);
$Ext = strtoupper($pinfo['extension']);
if ($Ext=='JPG' OR $Ext=='JPEG' OR $Ext=='PNG') {
if ($Default==$pinfo['basename']) $checked=' selected'; else $checked = '';
$OptionList .= '<option value="'.$pinfo['basename'].'"'.$checked.'> '.$pinfo['basename'].'</option>';
}
}
closedir($dh);
return $OptionList;
}
function ImportImage() {
global $db;
if ($_POST['ImgChoice']=='Select') { // then a locally stored image was chosen, return with image name
$Rtn['result'] = 'success';
$Rtn['message'] = $_POST['ImgFileName'].RPT_IMP_ERMSG9;
$Rtn['filename'] = $_POST['ImgFileName'];
return $Rtn;
}
$Rtn['result'] = 'error';
if ($_FILES['imagefile']['error']) { // php error uploading file
switch ($_FILES['imagefile']['error']) {
case '1': $Rtn['message'] = RPT_IMP_ERMSG1; break;
case '2': $Rtn['message'] = RPT_IMP_ERMSG2; break;
case '3': $Rtn['message'] = RPT_IMP_ERMSG3; break;
case '4': $Rtn['message'] = RPT_IMP_ERMSG4; break;
default: $Rtn['message'] = RPT_IMP_ERMSG5.$_FILES['imagefile']['error'].'.';
}
} elseif (!is_uploaded_file($_FILES['imagefile']['tmp_name'])) { // file uploaded
$Rtn['message'] = RPT_IMP_ERMSG10;
} elseif (strpos($_FILES['imagefile']['type'],'image')===false) { // not an imsge file extension
$Rtn['message'] = RPT_IMP_ERMSG6;
} elseif ($_FILES['imagefile']['size']==0) { // report contains no data, error
$Rtn['message'] = RPT_IMP_ERMSG7;
} else { // passed all error checking, save the image
$success = move_uploaded_file($_FILES['imagefile']['tmp_name'],DefRptPath.$_FILES['imagefile']['name']);
if (!$success) { // someone tried to hack the script
$Rtn['message'] = 'Upload error. File cannot be processed, check directory permissions!';
} else {
$Rtn['result'] = 'success';
$Rtn['message'] = $_FILES['imagefile']['name'].RPT_IMP_ERMSG9;
$Rtn['filename'] = $_FILES['imagefile']['name'];
}
}
return $Rtn;
}
function ExportReport($ReportID) {
global $db;
$crlf = chr(10);
$CSVOutput = '/* Report Builder Export Tool */'.$crlf;
$CSVOutput .= 'version:1.0'.$crlf;
// Fetch the core report data from table reports
$sql = "SELECT * FROM ".DBReports." WHERE id = ".$ReportID.";";
$Result=DB_query($sql,$db,'','',false,true);
$myrow = DB_fetch_assoc($Result);
// Fetch the language dependent db entries
$ReportName = $myrow['reportname'];
// Enter some export file info for language translation
$CSVOutput .= '/* Report Name: '.$ReportName.' */'.$crlf;
$CSVOutput .= '/* Export File Generated: : '.date('Y-m-d h:m:s', time()).' */'.$crlf.$crlf.$crlf;
$CSVOutput .= '/* Language Fields. */'.$crlf;
$CSVOutput .= '/* Only modify the language portion between the single quotes after the colon. */'.$crlf.$crlf;
$CSVOutput .= '/* Report Name and Title Information: */'.$crlf;
$CSVOutput .= "ReportName:'".addslashes($ReportName)."'".$crlf;
if ($myrow['reporttype']<>'frm') {
$CSVOutput .= "Title1Desc:'".addslashes($myrow['title1desc'])."'".$crlf;
$CSVOutput .= "Title2Desc:'".addslashes($myrow['title2desc'])."'".$crlf;
}
// Now add the report fields
$CSVOutput .= $crlf.'/* Report Field Description Information: */'.$crlf;
$sql = "SELECT * FROM ".DBRptFields." WHERE reportid = ".$ReportID." ORDER BY entrytype, seqnum;";
$Result=DB_query($sql,$db,'','',false,true);
$i=0;
while ($FieldRows = DB_fetch_assoc($Result)) {
if ($FieldRows['entrytype']<>'dateselect' AND $FieldRows['entrytype']<>'trunclong') {
$CSVOutput .= "FieldDesc".$i.":'".addslashes($FieldRows['displaydesc'])."'".$crlf;
}
$sql = 'FieldData'.$i.':';
foreach ($FieldRows as $key=>$value) {
if ($key<>'id' AND $key<>'reportid') $sql .= $key."='".addslashes($value)."', ";
}
$sql = substr($sql,0,-2).";"; // Strip the last comma and space and add a semicolon
$FieldData[$i] = $sql;
$i++;
}
$CSVOutput .= '/* End of language fields. */'.$crlf.$crlf;
$CSVOutput .= '/* DO NOT EDIT BELOW THIS LINE! */'.$crlf.$crlf.$crlf;
$CSVOutput .= '/* SQL report data. */'.$crlf;
// Build the report sql string
$RptData = 'ReportData:';
foreach ($myrow as $key=>$value) if ($key<>'id') $RptData .= $key."='".addslashes($value)."', ";
$RptData = substr($RptData,0,-2).";"; // Strip the last comma and space and add a semicolon
$CSVOutput .= $RptData.$crlf.$crlf;
$CSVOutput .= '/* SQL field data. */'.$crlf;
for ($i=0; $i<count($FieldData); $i++) $CSVOutput .= $FieldData[$i].$crlf;
$CSVOutput .= $crlf;
$CSVOutput .= '/* End of Export File */'.$crlf;
// export the file
$FileSize = strlen($CSVOutput);
header("Content-type: application/txt");
header("Content-disposition: attachment; filename=".preg_replace('/ /','',$ReportName).".rpt.txt; size=".$FileSize);
// These next two lines are needed for MSIE
header('Pragma: cache');
header('Cache-Control: public, must-revalidate, max-age=0');
print $CSVOutput;
exit();
}
function ImportReport($RptName) {
global $db;
if ($_POST['RptFileName']<>'') { // then a locally stored report was chosen
$arrSQL = file(DefRptPath.$_POST['RptFileName']);
} else { // check for an uploaded file
$Rtn['result'] = 'error';
if ($_FILES['reportfile']['error']) { // php error uploading file
switch ($_FILES['reportfile']['error']) {
case '1': $Rtn['message'] = RPT_IMP_ERMSG1; break;
case '2': $Rtn['message'] = RPT_IMP_ERMSG2; break;
case '3': $Rtn['message'] = RPT_IMP_ERMSG3; break;
case '4': $Rtn['message'] = RPT_IMP_ERMSG4; break;
default: $Rtn['message'] = RPT_IMP_ERMSG5.$_FILES['reportfile']['error'].'.';
}
} elseif (!is_uploaded_file($_FILES['reportfile']['tmp_name'])) { // file uploaded
$Rtn['message'] = RPT_IMP_ERMSG10;
} elseif (strpos($_FILES['reportfile']['type'],'text') === false) { // not a text file, error
$Rtn['message'] = RPT_IMP_ERMSG6;
} elseif ($_FILES['reportfile']['size']==0) { // report contains no data, error
$Rtn['message'] = RPT_IMP_ERMSG7;
} else { // passed all error checking, read file and reset error message
$arrSQL = file($_FILES['reportfile']['tmp_name']);
$Rtn['result']='';
}
if ($Rtn['result']=='error') return $Rtn;
}
$Title1Desc = ''; // Initialize to null, not used for forms
$Title2Desc = '';
foreach ($arrSQL as $sql) { // find the report translated reportname and title information
if (strpos($sql,'ReportName:')===0) $ReportName = substr(trim($sql),12,-1);
if (strpos($sql,'Title1Desc:')===0) $Title1Desc = substr(trim($sql),12,-1);
if (strpos($sql,'Title2Desc:')===0) $Title2Desc = substr(trim($sql),12,-1);
}
// check for valid file, duplicate report name
if ($RptName=='') $RptName = $ReportName; // then no report was entered use reportname from file
$sql= "SELECT id FROM ".DBReports." WHERE reportname='".addslashes($RptName)."';";
$Result=DB_query($sql,$db,'','',false,true);
if (DB_num_rows($Result)>0) { // the report name already exists, error
$Rtn['result'] = 'error';
$Rtn['message'] = RPT_REPDUP;
return $Rtn;
}
// Find the line with the table reports element, needs to be written first
$ValidReportSQL = false;
foreach ($arrSQL as $sql) { // find the main reports sql statement, language and execute it
if (strpos($sql,'ReportData:')===0) {
$sql="INSERT INTO ".DBReports." SET ".substr(trim($sql),11);
$Result=DB_query($sql,$db,'','',false,true);
$ValidReportSQL = true;
}
}
if (!$ValidReportSQL) { // no valid report sql statement found in the text file, error
$Rtn['result'] = 'error';
$Rtn['message'] = RPT_IMP_ERMSG8;
return $Rtn;
}
// fetch the id of the row inserted
$ReportID = DB_Last_Insert_ID($db,DBReports,'id');
// update the translated report name and title fields into the newly imported report
$sql = "UPDATE ".DBReports." SET
reportname = '".$RptName."',
title1desc = '".$Title1Desc."',
title2desc = '".$Title2Desc."'
WHERE id = ".$ReportID.";";
$Result=DB_query($sql,$db,'','',false,true);
foreach ($arrSQL as $sql) { // fetch the translations for the field descriptions
if (strpos($sql,'FieldDesc')===0) { // then it's a field description, find the index and save
$sql = trim($sql);
$FldIndex = substr($sql,9,strpos($sql,':')-9);
$Language[$FldIndex] = substr($sql,strpos($sql,':')+2,-1);
}
}
foreach ($arrSQL as $sql) {
if (strpos($sql,'FieldData')===0) { // a valid field, write it
$sql = trim($sql);
$FldIndex = substr($sql,9,strpos($sql,':')-9);
$sql="INSERT INTO ".DBRptFields." SET ".substr($sql,strpos($sql,':')+1);
$Result=DB_query($sql,$db,'','',false,true);
$FieldID = DB_Last_Insert_ID($db,DBRptFields, 'id');
if ($FieldID<>0) { // A field was successfully written update the report id
if (isset($Language[$FldIndex])) $DispSQL = "displaydesc='".$Language[$FldIndex]."', ";
else $DispSQL = '';
$tsql = "UPDATE ".DBRptFields." SET ".$DispSQL." reportid='".$ReportID."'
WHERE id=".$FieldID.";";
$Result=DB_query($tsql,$db,'','',false,true);
}
}
}
$Rtn['result'] = 'success';
$Rtn['message'] = $RptName.RPT_IMP_ERMSG9;
return $Rtn;
}
function CreateTableList($ReportID,$Table) {
global $db;
$sql = "SELECT table".$Table." FROM ".DBReports." WHERE id='".$ReportID."'";
$Result=DB_query($sql,$db,'','',false,true);
$myrow = DB_fetch_row($Result);
$TableList = '';
$sql = "SHOW TABLES";
$Result=DB_query($sql,$db,'','',false,true);
while ($mytable=DB_fetch_row($Result)) {
$tablename = strtolower($mytable[0]);
if ($myrow[0] == $tablename) $TableList .= "<OPTION SELECTED Value='" . $tablename . "'>" . $tablename . "</OPTION>";
else $TableList .= "<OPTION Value='" . $tablename . "'>" . $tablename . "</OPTION>";
}
return $TableList;
} // CreateTableList
function CreateLinkList($ReportID,$Table) {
global $db;
$sql = "SELECT table1, table2, table3, table4, table5, table6
FROM ".DBReports." WHERE id='".$ReportID."'";
$Result=DB_query($sql,$db,'','',false,true);
$myrow = DB_fetch_row($Result);
$LinkList = ''; $j = 0;
/* Get list of link tables from foreign keys */
for ($i = 0; $i < $Table; $i++) {
$comments = '';
$sql = "SELECT table1, table2 FROM reportlinks WHERE table1 = '" . $myrow[$i] . "'";
$Result=DB_query($sql,$db,'','',false,true);
while($mytable=DB_fetch_row($Result)) {
if ($myrow[$Table]) {
if ($myrow[$Table] == $mytable[1]){
$LinkList .= "<OPTION SELECTED Value='" . $mytable[1] . "'>" . $mytable[1];
} else {
$LinkList .= "<OPTION Value='" . $mytable[1] . "'>" . $mytable[1];
}
} else {
if ($j == 0){
$LinkList .= "<OPTION SELECTED Value='" . $mytable[1] . "'>" . $mytable[1];
} else {
$LinkList .= "<OPTION Value='" . $mytable[1] . "'>" . $mytable[1];
}
$j++;
}
} // while
} // for
if (!$myrow[$Table] && $Table > $j) {
$LinkList = '';
}
return $LinkList;
}
function CreateLinkEqList($ReportID,$Table) {
global $db;
$sql = "SELECT table1,
table2, table2criteria,
table3, table3criteria,
table4, table4criteria,
table5, table5criteria,
table6, table6criteria
FROM ".DBReports." WHERE id='".$ReportID."'";
$Result=DB_query($sql,$db,'','',false,true);
$myrow = DB_fetch_row($Result);
$LinkEqList = ''; $j = 0;
/* Get list of foreign key constraints */
for ($i = 0; $i < $Table; $i++) {
$comments = '';
$sql = "SELECT table1, table2, equation FROM reportlinks WHERE table1 = '" . $myrow[$i] . "'";
$Result=DB_query($sql,$db,'','',false,true);
while($mytable=DB_fetch_row($Result)) {
if ($myrow[$Table+3]) {
if ($myrow[$Table+3] == $mytable[2]){
$LinkEqList .= "<OPTION SELECTED Value='" . $mytable[2] . "'>" . $mytable[2];
} else {
$LinkEqList .= "<OPTION Value='" . $mytable[2] . "'>" . $mytable[2];
}
} else {
if ($j == 0){
$LinkEqList .= "<OPTION SELECTED Value='" . $mytable[2] . "'>" . $mytable[2];
} else {
$LinkEqList .= "<OPTION Value='" . $mytable[2] . "'>" . $mytable[2];
}
$j++;
}
} // while
} // for
if (!$myrow[$Table] && $Table > $j) {
$LinkEqList = '';
}
return $LinkEqList;
} // CreateLinkEqList
function CreateFieldList($ReportID,$FName,$Type) {
global $db;
if ($Type=='Company') { // then pull from the company information table
$myrow[]=CompanyDataBase;
} else { // pull from user selected tables for this report
$sql = "SELECT table1, table2, table3, table4, table5, table6
FROM ".DBReports." WHERE id='".$ReportID."'";
$Result=DB_query($sql,$db,'','',false,true);
$myrow = DB_fetch_row($Result);
}
$FieldList = '';
for ($i = 0; $i < 6; $i++) {
if ($myrow[$i]) {
$sql = "DESCRIBE " . $myrow[$i];
$Result=DB_query($sql,$db,'','',false,true);
while ($mytable=DB_fetch_row($Result)) {
$fieldname = strtolower($myrow[$i]) . "." . strtolower($mytable[0]);
if ($FName == $fieldname){
$FieldList .= "<OPTION SELECTED Value='" . $fieldname . "'>" . $fieldname . "</OPTION>";
} else {
$FieldList .= "<OPTION Value='" . $fieldname . "'>" . $fieldname . "</OPTION>";
}
} // while
} // if
} // for
return $FieldList;
} // CreateFieldList
?>