[56K WARN] I have a report issue

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mingster
Forum Newbie
Posts: 4
Joined: Fri Jul 21, 2006 9:22 am

[56K WARN] I have a report issue

Post by mingster »

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 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='dsrt' value=''>";
		echo "<input type='hidden' name='ac' value=''>";
Then the report sorts just fine

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=''>";
It will only sort one time and then break, but excel works

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=''>";
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

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'>";
But if you want to sort and it passes these

Code: Select all

echo "<input type='hidden' name='col' value='bw'>";
		echo "<input type='hidden' name='stype' value='rpt'>";
it breaks

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'>";
to break the sort but make excel to work

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]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You might have to clarify this a little bit... You are sorting what? A result set? A query? An array? Please explain... Also, explain why it is you believe that after a sort your Excel writing gets broken.
mingster
Forum Newbie
Posts: 4
Joined: Fri Jul 21, 2006 9:22 am

Post by mingster »

Jason,

Thanks for taking a moment to look at this

Im posting a screen shot its not the best but it shows my issues kinda

Image

The top part shows what the excel writer does after I have sorted a column

Headers you see here that can be sorted are MC# And mc#first name, by clicking on them with the following code
it resubmits the query and ORDERS BY the selected column

Code: Select all

<!-- 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

So you have a query that returns a result data set. You want users to be able to sort the result set and in the same process export to Excel? Is that right?
mingster
Forum Newbie
Posts: 4
Joined: Fri Jul 21, 2006 9:22 am

Post by mingster »

I want them to beable to do both features seperately, Users sometimes just need a quick view of the data, or just need to find 1 value so sorting the columns solves that issue, but at other times or analyst use the data to build reports and that is when they just click the export to excel.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What I have done in situations like this in the past is execute the query and then use array_multisort() to sort the result set. Then I use that sorted result set as the export data for Excel.
  • The goods:
  • 1 query
  • 1 result set (sorted however you like)
  • No requerying logic to handle
mingster
Forum Newbie
Posts: 4
Joined: Fri Jul 21, 2006 9:22 am

Post by mingster »

Sounds good to me,

Ive never used that multi sort, could write a quick template that I could view as my guide, to cover both the output to the web with sorting ability and then push that information to the excel writer?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Everything you need to get by is in the PHP Manual page on array_multisort().
Post Reply