Jump to content

How to do hyperlink using date in daily report?

xavier.rayne

By xavier.rayne
in PHP Coding Help

 Share

Recommended Posts

xavier.rayne Newbie

xavier.rayne

Posted
Posted

Hi, there.

 

I'm currently doing on a sales system assignment which can generate daily report order by branch and date.

 

There is a report menu which users can choose the branch and date that they would like to view. my currently report form is to show all the chosen date report in one page.so if the user choose from 1st of the month to 31st of the month, then the page will become so long.

 

So, is there any means to generate the daily report that just show one-day daily record with the hyperlinks of other selected date below the report.if the user wish to see other day's daily report,then he can click on the hyperlink itself. 

 

Below is my code:

There are two section in my daily report:

- selected date without selected branch(show all branches)

- selected date with selected branch

 

<?php 
$date = date("Y-m-d");
if(isset($_GET['b'])){
$branch = $_GET['b'];
$from = $_GET['f'];
$to   = $_GET['t']; 
}else{
$branch = $_POST['branch'];
$from = $_POST['from'];
$to   = $_POST['to'];
}?>
<?php
echo "<form action=menuReport.php method=post>
<p>";
//show records from all branches
if(empty($branch)){
while($from<=$to){
$wk=diswk($from);				
$querySales = "SELECT * FROM sales WHERE date1='$from' ORDER BY timeStart";
$resultSales= mysql_query($querySales);
$numSales   = mysql_num_rows($resultSales); 
		if($numSales==0){
		echo "<table><tr><td width=680 colspan=5><table border=0 cellpadding=0 cellspacing=0 bgcolor=#FDDFFC class=table width=100%></table></td></tr>";
		echo "<tr><td colspan=5><center><font color=red>No Record on $from has been found!</font></center></td></tr>";
		echo "</table>";
		} 
		if($numSales!=0){

		echo "<table><tr><td width=680 colspan=5><table border=0 cellpadding=0 cellspacing=0 class=table width=100%></td></tr>";
		echo "<tr bgcolor=#6699FF><th colspan=5><font color=#FFFFFF>$from $wk</font></th></tr>";
		//echo "<tr bgcolor=#D8FCFA><th colspan=5>Branch $name</th></tr><tr bgcolor=#FDDFFC><th>Time</th><th>Employee Name</th><th>Sales (RM)</th></tr>";
		$i=0; 
		while($i<$numSales){
			$start = mysql_result($resultSales,$i,'timeStart');
			$empID = mysql_result($resultSales,$i,'empId');
			$amt   = mysql_result($resultSales,$i,'amt');
			$total+=$amt;

			$queryE  = "SELECT name,branchId FROM employee WHERE empId='$empID'";
			$resultE = mysql_query($queryE);
			$rowE    = mysql_fetch_array($resultE);
			$branchid= $rowE['branchId'];
			$empName = $rowE['name'];

			$queryB  = "SELECT name FROM branch WHERE branchId='$branchid'";
			$resultB = mysql_query($queryB);
			$rowB    = mysql_fetch_array($resultB);
			$name    = $rowB['name'];

			echo "<tr bgcolor=#D8FCFA><th colspan=5>Branch $name</th></tr><tr bgcolor=#FDDFFC><th>Time</th><th>Employee Name</th><th>Sales (RM)</th></tr>";			
			echo "<tr><td><center>$start</td><td><center>$empName</td><td><center>$amt</td></tr>";
			$i++;
			}	//end while($i<$numSales)

			echo "</table></td><tr><td colspan=5 height=5><hr></td></tr></table>";	

		}	//end if($numSales!=0)

		$from = day_tomorrow($from); 
	}	//end while($from<=$to)
		//hyperlink for each day's sales
		/*for($i=0;$i<=count($date);$i++){
		echo "<a href=daily_sales.php?f=$from&&t=$to>".$date[$i].";</a>";		
		}*/

	echo "<table><tr><td width=600 colspan=5 align=right><font color=red><b>Overall Total Sales: </font></td><td><font color=red><b> RM $total</b></font></td></tr></table>";

}	//end if(empty($name))

else if(!empty($branch)){
		$queryB   = "SELECT * FROM branch WHERE name ='$branch'";
		$resultB  = mysql_query($queryB);
		$rowB 	  = mysql_fetch_array($resultB);
		$branchid = $rowB['branchId'];  
		$name     = $rowB['name'];
		$queryE   = "SELECT * FROM sales WHERE branchId='$branchid'";
		$resultE  = mysql_query($queryE);
		$numE     = mysql_num_rows($resultE);

		if($numE!=0){
			echo "<table><tr><td width=680 colspan=5><table border=0 cellpadding=0 cellspacing=0 class=table width=100%></td></tr>";
			echo "<tr bgcolor=#000066><td colspan=3><center><font color=ffffff><b>Branch: $name</b></font></center></td></tr>";
			$overallTotal[] = displayDay($from,$to,$branchid);	
			echo "</table></td>";
			echo "<tr><td colspan=5 height=5><hr></td></tr></table>";
			?><p class="pagebreak"></p><?php				
		}
		echo "<table><tr><td width=600 colspan=5 align=right><font color=red><b>Overall Total Sales: </font></td><td><font color=red><b> RM ".array_sum($overallTotal)."</font></td></tr></table>";
}	//end if(!empty($name))
?>	
</center></table></form>
</body>
</html>
<?php
}
else
{header("Location:../signin.php?msg=You do not have the authority to access the page");}	
}
else
{header("Location:../signin.php");} 
?>
<?php 
function displayDay($from,$to,$branchid)
{
	while($from<=$to){
	$wk = diswk($from);

	$queryDate = "SELECT DISTINCT date1 FROM sales WHERE date1 BETWEEN '$from' AND '$to'"; 
	$resultDate = mysql_query($queryDate);
	while($rowDate   = mysql_fetch_array($resultDate)) {
	$date[] = $rowDate['date1']; 
	}
	echo "<tr bgcolor=#6699FF><td colspan=3><center><b>$from $wk</center></td></tr>";
	echo "<tr bgcolor=#FDDFFC><td valign=top><p><span style=color: #FFFFFF;><b>Time </span></p></td><td valign=top><p><span style=color: #FFFFFF;><b>Employee Name </span></p></td><td valign=top><p><span style=color: #FFFFFF;><b>Total Sales(RM) </span></p></td></tr>";	

	if(isset($_GET['f'])){
	$selectedDate = $_GET['f']; echo "AA".$selectedDate;
	}else{
	$selectedDate = $date[0]; 
	}

	$queryS   = "SELECT DISTINCT empId FROM sales WHERE date1='$from' AND branchId=$branchid"; 
	$resultS  = mysql_query($queryS);
	$total    = 0;
	while($rowS = mysql_fetch_array($resultS)) {
	$empid   = $rowS['empId']; 
	$queryE  = "SELECT * FROM sales WHERE date1='$from' AND empId=$empid ORDER BY timeStart";
	$resultE = mysql_query($queryE);

	$empTotal =0;
	while ($rowE = mysql_fetch_array($resultE)){
		//get sales amount for each employee
		$amount = $rowE['amt']; 
		$start  = $rowE['timeStart'];
		$amount1[] = $rowE['amt'];
		}
		//calculate employee's total sales
		$empTotal+=$amount;

		//show employee name
		$query3  = "SELECT * FROM employee WHERE empId=$empid";
		$result3 = mysql_query($query3);
		$row3    = mysql_fetch_array($result3);
		$name    = $row3['name']; 

		//show branch name
		$query4  = "SELECT * FROM branch WHERE branchId=$branchid";
		$result4 = mysql_query($query4);
		$row4    = mysql_fetch_array($result4);
		$branch    = $row4['name']; 

		echo "<tr bgcolor=#D8FCFA><td>$start</td><td>$name</td><td><font color=0000ff> $empTotal </td>";				
		$total+=$empTotal;	//calculate daily total
		$netTotal=array_sum($amount1);

	}	//end while ($rowS = mysql_fetch_array($resultS))

		echo "<tr><td align=right colspan=2><font color=red>Daily Total Sales: </font></td><td><b>$total</td>";
		$from = day_tomorrow($from);

		}	//end while($from<=$to)
		//hyperlink for each day's sales
		for($i=0;$i<=count($date);$i++){
		echo "<a href=daily_sales.php?b=$branch&f=$from&t=$to>".$date[$i].";</a>";			
		}
		return $netTotal;
}

function day_tomorrow($d){
  $y1= substr($d,0,4);
  $m1= substr($d,5,2);
  $d1= substr($d,8,2);
  $tomorrow  = date("Y-m-d", mktime(0,0,0, $m1, $d1+1, $y1));
  return $tomorrow;
}

function diswk($d){
  $y1= substr($d,0,4);
  $m1= substr($d,5,2);
  $d1= substr($d,8,2);

  $wk  = date("l", mktime(0,0,0, $m1, $d1, $y1));
  return $wk;
}
?>

 

Please help me.

Thanks alot.

xavier.rayne Newbie

xavier.rayne

Posted
  • Author
Posted

I've test one part of my program, that is the selected date with selected branch part and below is the code for that:

<? if(!empty($branch)){
		$queryB   = "SELECT * FROM branch WHERE name ='$branch'";
		$resultB  = mysql_query($queryB);
		$rowB 	  = mysql_fetch_array($resultB);
		$branchid = $rowB['branchId'];  
		$name     = $rowB['name'];
		$queryE   = "SELECT * FROM sales WHERE branchId='$branchid'";
		$resultE  = mysql_query($queryE);
		$numE     = mysql_num_rows($resultE);

		if($numE!=0){
			echo "<table><tr><td width=680 colspan=5><table border=0 cellpadding=0 cellspacing=0 class=table width=100%></td></tr>";
			echo "<tr bgcolor=#000066><td colspan=3><center><font color=ffffff><b>Branch: $name</b></font></center></td></tr>";
			$overallTotal[] = displayDay($from,$to,$branchid);	
			echo "</table></td>";
			echo "<tr><td colspan=5 height=5><hr></td></tr></table>";
			?><p class="pagebreak"></p><?php				
		}
		echo "<table><tr><td width=600 colspan=5 align=right><font color=red><b>Overall Total Sales: </font></td><td><font color=red><b> RM ".array_sum($overallTotal)."</font></td></tr></table>";
}	//end if(!empty($name))
?>	
<?php 
function displayDay($from,$to,$branchid)
{
	//while($from<=$to){
	$wk = diswk($from);

	$queryDate = "SELECT DISTINCT date1 FROM sales WHERE date1 BETWEEN '$from' AND '$to'"; 
	$resultDate = mysql_query($queryDate);
	while($rowDate   = mysql_fetch_array($resultDate)) {
	$date[] = $rowDate['date1']; 
	}
	echo "<tr bgcolor=#6699FF><td colspan=3><center><b>$from $wk</center></td></tr>";
	echo "<tr bgcolor=#FDDFFC><td valign=top><p><span style=color: #FFFFFF;><b>Time </span></p></td><td valign=top><p><span style=color: #FFFFFF;><b>Employee Name </span></p></td><td valign=top><p><span style=color: #FFFFFF;><b>Total Sales(RM) </span></p></td></tr>";	

	if(isset($_GET['f'])){
	$selectedDate = $_GET['f']; echo "AA".$selectedDate;
	}else{
	$selectedDate = $date[0]; 
	}

	$queryS   = "SELECT DISTINCT empId FROM sales WHERE date1='$from' AND branchId=$branchid"; 
	$resultS  = mysql_query($queryS);
	$total    = 0;
	while($rowS = mysql_fetch_array($resultS)) {
	$empid   = $rowS['empId']; 
	$queryE  = "SELECT * FROM sales WHERE date1='$from' AND empId=$empid ORDER BY timeStart";
	$resultE = mysql_query($queryE);

	$empTotal =0;
	while ($rowE = mysql_fetch_array($resultE)){
		//get sales amount for each employee
		$amount = $rowE['amt']; 
		$start  = $rowE['timeStart'];
		$amount1[] = $rowE['amt'];
		}
		//calculate employee's total sales
		$empTotal+=$amount;

		//show employee name
		$query3  = "SELECT * FROM employee WHERE empId=$empid";
		$result3 = mysql_query($query3);
		$row3    = mysql_fetch_array($result3);
		$name    = $row3['name']; 

		//show branch name
		$query4  = "SELECT * FROM branch WHERE branchId=$branchid";
		$result4 = mysql_query($query4);
		$row4    = mysql_fetch_array($result4);
		$branch    = $row4['name']; 

		echo "<tr bgcolor=#D8FCFA><td>$start</td><td>$name</td><td><font color=0000ff> $empTotal </td>";				
		$total+=$empTotal;	//calculate daily total
		$netTotal=array_sum($amount1);

	}	//end while ($rowS = mysql_fetch_array($resultS))

		echo "<tr><td align=right colspan=2><font color=red>Daily Total Sales: </font></td><td><b>$total</td>";

		$from = day_tomorrow($from);
		//}	//end while($from<=$to)
		//hyperlink for each day's sales
		for($i=0;$i<=count($date);$i++){
		echo "<a href=daily_sales.php?b=$branch&f=$from&t=$to>".$date[$i].";</a>";			
		}
		return $netTotal;
}

function day_tomorrow($d){
  $y1= substr($d,0,4);
  $m1= substr($d,5,2);
  $d1= substr($d,8,2);
  $tomorrow  = date("Y-m-d", mktime(0,0,0, $m1, $d1+1, $y1));
  return $tomorrow;
}

function diswk($d){
  $y1= substr($d,0,4);
  $m1= substr($d,5,2);
  $d1= substr($d,8,2);

  $wk  = date("l", mktime(0,0,0, $m1, $d1, $y1));
  return $wk;
}
?>

 

It can link to the page that i want to view but why after i've selected a date, it will disappeared and how if i wish to select it again???

Thanks.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.