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]