[56K WARN] I have a report issue
Posted: Tue Dec 12, 2006 10:15 am
feyd | Please use
Then the report sorts just fine
If I post like this
It will only sort one time and then break, but excel works
Ive tried to do things like
but it works for sorting but when you go to post value of ac it doesnt work right
in order to work the excel writer it requires the following 3 variables
But if you want to sort and it passes these
it breaks
Below is a working version of the sort, just add
to break the sort but make excel to work
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Im trying to make this report to beable to both sort and excel export at the same time
I have found that my hidden variables seem to be the key to messing things up
If i post hidden variables like thisCode: Select all
echo "<input type='hidden' name='where' value='profiletype'>";
echo "<input type='hidden' name='extapi' value='external'>";
echo "<input type='hidden' name='dsrt' value=''>";
echo "<input type='hidden' name='ac' value=''>";If I post like this
Code: Select all
echo "<input type='hidden' name='where' value='profiletype'>";
echo "<input type='hidden' name='extapi' value='external'>";
echo "<input type='hidden' name='ac' value=''>";
echo "<input type='hidden' name='col' value='bw'>";
echo "<input type='hidden' name='stype' value='rpt'>";
echo "<input type='hidden' name='dsrt' value=''>";Ive tried to do things like
Code: Select all
if ($ac == NULL) {
echo "<input type='hidden' name='dsrt' value=''>";
echo "<input type='hidden' name='where' value='profiletype'>";
echo "<input type='hidden' name='extapi' value='external'>";
echo "<input type='hidden' name='ac' value=''>";
} else {
echo "<input type='hidden' name='where' value='profiletype'>";
echo "<input type='hidden' name='extapi' value='external'>";
echo "<input type='hidden' name='ac' value=''>";
echo "<input type='hidden' name='col' value='bw'>";
echo "<input type='hidden' name='stype' value='rpt'>";
echo "<input type='hidden' name='dsrt' value=''>";in order to work the excel writer it requires the following 3 variables
Code: Select all
echo "<input type='hidden' name='ac' value=''>";
echo "<input type='hidden' name='col' value='bw'>";
echo "<input type='hidden' name='stype' value='rpt'>";Code: Select all
echo "<input type='hidden' name='col' value='bw'>";
echo "<input type='hidden' name='stype' value='rpt'>";Below is a working version of the sort, just add
Code: Select all
echo "<input type='hidden' name='col' value='bw'>";
echo "<input type='hidden' name='stype' value='rpt'>";Code: Select all
<?php
/* The below line validates the users has access to this file - All access is based on the user settings on the
user admin screen. */
if(count($HTTP_SESSION_VARS['acl']['rpt_gen']) < 1 &&
!auth_no_access(_('to access this form'))) {
return;
}
trim($dsrt);
$dsrt = _addslashes($HTTP_POST_VARS['dsrt']);
if (isset($HTTP_GET_VARS['ac']))
$HTTP_POST_VARS['ac'] = $HTTP_GET_VARS['ac'];
$ac = intval($HTTP_POST_VARS['ac']);
// OUTPUTING TO EXCEL -
if($ac == '1')
{ // Start the excel function
require_once($ediCONFIG['include_path']."/lib/spreadsheet/excel/Writer.php");
$workbook = new Spreadsheet_Excel_Writer();
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$worksheet =& $workbook->addWorksheet();
$worksheet->write(0, 0, "MC #", $format_bold);
$worksheet->write(0, 1, "MC First Name", $format_bold);
$worksheet->write(0, 2, "MC Last Name", $format_bold);
$worksheet->write(0, 3, "MC Street Address", $format_bold);
$worksheet->write(0, 4, "MC City", $format_bold);
$worksheet->write(0, 5, "MC State", $format_bold);
$worksheet->write(0, 6, "MC Zipcode", $format_bold);
$worksheet->write(0, 7, "MC Phone 1", $format_bold);
$worksheet->write(0, 8, "MC Phone 2", $format_bold);
$worksheet->write(0, 9, "MC Email", $format_bold);
$worksheet->write(0, 10, "MC Employee Type", $format_bold);
$worksheet->write(0, 11, "# Shifts MC assigned to", $format_bold);
// The SELECT STATEMENT
$sqlact = "SELECT DISTINCT c.id, c.fname, c.lname, c.address1, c.city, c.st, c.zip, c.phone, c.cell, c.email, c.profile_type";
// THE FROME STATEMENT
$sqlact .= " FROM edi_activity a, edi_shifts b, edi_projects d LEFT JOIN edi_surveyors c ON b.merch = c.id";
// SET THE WHERE CRITERIA
$sqlact .= " WHERE a.activity_id = b.activity_id AND a.project_id = d.project_id AND c.profile_type <> '2' AND c.profile_type <> '3' AND c.profile_type <> '5' AND d.project_status = 1 AND a.activity_status = '1'AND a.archived != 1 AND d.archived != 1 ";
// SET HOW RESULTS ARE DISPLAYED
$sqlact .= " ORDER BY c.id";
$result1 = mysql_query($sqlact);
$rc = mysql_num_rows($result1);
$i=1;
while(list($mcid, $mfname, $mlname, $mstreet, $mcity, $mstate, $mzip, $mphone1, $mphone2, $memail, $memptype) = mysql_fetch_array($result1))
{
// The SELECT STATEMENT
$sqlshift = "SELECT count(a.id)";
// THE FROME STATEMENT
$sqlshift .= " FROM edi_shifts a, edi_activity b, edi_projects c";
// SET THE WHERE CRITERIA
$sqlshift .= " WHERE a.merch = '$mcid' AND b.activity_id = a.activity_id AND b.project_id = c.project_id AND c.project_status = 1 AND b.activity_status = '1'AND b.archived != 1 AND c.archived != 1";
// SET HOW RESULTS ARE DISPLAYED
$sqlshift .= " GROUP BY a.merch";
$resultshift = mysql_query($sqlshift);
$rc = mysql_num_rows($resultshift);
list($shift) = mysql_fetch_array($resultshift);
// Change profile type from number to words
if ($memptype == '1')
$memptype= "IC's";
if ($memptype == '2')
$memptype= "Direct Hourly";
if ($memptype == '3')
$memptype= "Direct Salary";
if ($memptype == '4')
$memptype= "Hourly";
if ($memptype == '5')
$memptype= "3rd Party";
if ($memptype == '7')
$memptype= "IC & Hourly";
$worksheet->write($i, 0, "$mcid");
$worksheet->write($i, 1, "$mfname");
$worksheet->write($i, 2, "$mlname");
$worksheet->write($i, 3, "$mstreet");
$worksheet->write($i, 4, "$mcity");
$worksheet->write($i, 5, "$mstate");
$worksheet->write($i, 6, "$mzip");
$worksheet->write($i, 7, "$mphone1");
$worksheet->write($i, 8, "$mphone2");
$worksheet->write($i, 9, "$memail");
$worksheet->write($i, 10, "$memptype");
$worksheet->write($i, 11, "$shift");
$i++;
} // end of second while clause
$workbook->send('profiletype.xls');
$workbook->close();
} //end excel --Choice 2
// OUTPUTING DIRECTLY TO THE SCREEN
// The SELECT STATEMENT
$sqlact = "SELECT DISTINCT c.id, c.fname, c.lname, c.address1, c.city, c.st, c.zip, c.phone, c.cell, c.email, c.profile_type";
// THE FROME STATEMENT
$sqlact .= " FROM edi_activity a, edi_shifts b, edi_projects d LEFT JOIN edi_surveyors c ON b.merch = c.id";
// SET THE WHERE CRITERIA
$sqlact .= " WHERE a.activity_id = b.activity_id AND a.project_id = d.project_id AND c.profile_type <> '2' AND c.profile_type <> '3' AND c.profile_type <> '5' AND d.project_status = 1 AND a.activity_status = '1' AND a.archived != 1 AND d.archived != 1 ";
// SET HOW RESULTS ARE DISPLAYED
$sqlact .= ' ORDER BY ';
if($HTTP_POST_VARS['dsrt'] != NULL) {
if ($HTTP_POST_VARS['colsort'] == 'DESC')
$sqlact .= $dsrt." DESC";
else
$sqlact .= $dsrt." ASC";
}
Else
$sqlact .= "c.id";
//Return the results
$result1 = mysql_query($sqlact);
$rc = mysql_num_rows($result1);
?>
<div style="background-image:url(http://mingster.us/Convergencemktg/images/gray.jpg)" >
<table border="0" cellspacing="0" cellpadding="0" background="http://mingster.us/Convergencemktg/images/background.JPG"align="center" width="95%" style="FONT-SIZE: 8pt; VERTICAL-ALIGN: top; FONT-FAMILY: Verdana, Arial, Helvetica, sans-serif">
<tr>
<form>
<th align="left">
<?php echo(_('Merchandisers Using The Wrong Profile Type')); ?>
</th>
<script>
function choice(a1) {
document.forms[0].ac.value=a1;
document.forms[0].submit();
}
</script>
<td align="right">
<?php echo (date ("l dS of F Y h:i:s A")); ?>
</td>
</tr>
<tr>
<td align="right">
<?php echo (_('Number of Merchandisers is ' . $rc)); ?>
</table>
Sort Order:
<?php echo( makeRadioButtons('colsort','ASC',array("ASC","DESC"),array("Ascending","Descending")));?>
<table bgcolor="#FFFFFF" border="1" cellspacing="0" cellpadding="0" align="center" width="100%" style="FONT-SIZE: 8pt; VERTICAL-ALIGN: top; FONT-FAMILY: Verdana, Arial, Helvetica, sans-serif">
<!-- Script to use to help sort headers -->
<script>function col_sort(as){
document.forms[0].dsrt.value=as;
document.forms[0].submit();
}
</script>
<tr>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.id')">MC#</a></div></td>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.fname')">MC First Name</a></div></td>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.lname')">MC Last Name</a></div></td>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.address1')">MC Street Address</a></div></td>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.city')">MC City</a></div></td>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.st')">MC State</a></div></td>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.zip')">MC Zipcode</a></div></td>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.phone')">MC Home Phone</a></div></td>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.cell')">MC Cell Phone</a></div></td>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.email')">MC Email</a></div></td>
<td width="5%" align="left"><div align="center"><a href="javascript:onClick=col_sort('c.profile_type')">MC Profile Type</a></div></td>
<th width = "10%" align="left"><?php echo(_('# Shifts Assigned To')); ?></a></th>
</tr>
<?php
while(list($mcid, $mfname, $mlname, $mstreet, $mcity, $mstate, $mzip, $mphone1, $mphone2, $memail, $memptype) = mysql_fetch_array($result1))
{
// The SELECT STATEMENT
$sqlshift = "SELECT count(a.id)";
// THE FROME STATEMENT
$sqlshift .= " FROM edi_shifts a, edi_activity b, edi_projects c";
// SET THE WHERE CRITERIA
$sqlshift .= " WHERE a.merch = '$mcid' AND b.activity_id = a.activity_id AND b.project_id = c.project_id AND c.project_status = 1 AND b.activity_status = '1'AND b.archived != 1 AND c.archived != 1";
// SET HOW RESULTS ARE DISPLAYED
$sqlshift .= " GROUP BY a.merch";
$resultshift = mysql_query($sqlshift);
$rc = mysql_num_rows($resultshift);
list($shift) = mysql_fetch_array($resultshift);
// Change profile type from number to words
if ($memptype == '1')
$memptype= "IC's";
if ($memptype == '2')
$memptype= "Direct Hourly";
if ($memptype == '3')
$memptype= "Direct Salary";
if ($memptype == '4')
$memptype= "Hourly";
if ($memptype == '5')
$memptype= "3rd Party";
if ($memptype == '7')
$memptype= "IC & Hourly";
?>
<tr border = "1">
<td><?php echo($mcid); ?></td>
<td><?php echo($mfname); ?></td>
<td><?php echo($mlname); ?></td>
<td><?php echo($mstreet); ?></td>
<td><?php echo($mcity); ?></td>
<td><?php echo($mstate); ?></td>
<td><?php echo($mzip); ?></td>
<td><?php echo($mphone1); ?></td>
<td><?php echo($mphone2); ?></td>
<td><?php echo($memail); ?></td>
<td><?php echo($memptype); ?></td>
<td><?php echo($shift); ?></td>
</tr>
<?php } ?>
<?php
echo "<input type='hidden' name='where' value='profiletype'>";
echo "<input type='hidden' name='extapi' value='external'>";
echo "<input type='hidden' name='dsrt' value=''>";
echo "<input type='hidden' name='ac' value=''>";
?>
</form>
<tr>
<th colspan="5" align="center"> <table border="0" cellpadding="4">
<tr>
<th><input type="button" value="Search Again" onClick=location.href="http://www.edataintelligence.com/edisurveyor/admin/edismgmt.php?where=profiletype&extapi=external";></th>
<th> <input type="button" onClick="javascript:choice(1);" value="EXCEL EXPORT"></th>
<th><input type="button" value="Main Menu" onClick=location.href="http://www.edataintelligence.com/edisurveyor/admin/edismgmt.php"; /></th>
</tr>
</table></th>
</tr>
</table>feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]