Jump to content

Creating a link and passing data to open another report

kat35601

By kat35601
in PHP Coding Help

 Share
Go to solution Solved by Barand,

Recommended Posts

kat35601 Advanced Member

kat35601

Posted
Posted

I have a web page that I get data from my sql server and I want to link to a report from the output of my main report. I have included a picture of my report and circled a couple of the items I want the user to click on and then show a report with details of what that summary number is. so in the link I will need to pass the schedule number and the column that they click on to the sub report. below is my code

<html>
<head>
<title>Status Screen</title>
</head>
<body>
<p> Status Screen </p>

<?php
$connect =odbc_connect("Removed");
if(!$connect) {
   exit("Connection Failed: " . $connect);
}

$sql="SELECT distinct  WIP_master.uompScheduleNumber
,CASE WIP_master.uompScheduleColor
WHEN 'NEON' then 'ORANGERED' 
WHEN 'PINK' THEN 'FUCHSIA'
ELSE WIP_master.uompScheduleColor end AS COLOR


      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE  WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and  (WIP_master1.itemtype ='DR' and WIP_master1.status = '2')
      ) as PRT_DR
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '2' and WIP_master1.itemtype='DDF'
      ) as PRT_DDF
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '2' and WIP_master1.itemtype='FR'
      ) as PRT_FR
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '2' and WIP_master1.itemtype not in ('DR','DDF','FR')
      ) as PRT_Other
      
      
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE  WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and  (WIP_master1.itemtype ='DR' and WIP_master1.status = '3')
      ) as DR
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '3' and WIP_master1.itemtype='DDF'
      ) as DDF
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '3' and WIP_master1.itemtype='FR'
      ) as FR
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '3' and WIP_master1.itemtype not in ('DR','DDF','FR')
      ) as Other
      
      /***************************************************************************************************/
            ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE  WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and  (WIP_master1.itemtype ='DR' and WIP_master1.status = '4')
      ) as DR_PRP
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '4' and WIP_master1.itemtype='DDF'
      ) as DDF_PRP
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '4' and WIP_master1.itemtype='FR'
      ) as FR_PRP
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '4' and WIP_master1.itemtype not in ('DR','DDF','FR')
      ) as Other_PRP
      
      /******************************************************************************************************/
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location 
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '9'
      ) as Shortage                                          
      
         ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location 
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '10'
      ) as Shortage_Prt                                  
      
         ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location 
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '11'
      ) as Red                                               
      
            ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location 
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '12'                                                                                     
      ) as Neon                                                
   
            ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location 
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '20'                                                                                   
      ) as QC_Hold       
      
        ,s1.UOMPBUILDDATE                            
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master
LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations ON WIP_master.location = WIP_locations.location
LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status ON WIP_master.STATUS = WIP_status.STATUS

left join m1_DC.dbo.SalesOrders s1 on s1.UOMPSCHEDULENUMBER=WIP_master.uompScheduleNumber and s1.ompSalesOrderID=WIP_master.ompSalesOrderID



where  s1.UOMPBUILDDATE >= GETDATE()-7  
order by WIP_master.uompScheduleNumber    
" ;
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
echo "<table  cellpadding='pixels'cellspacing='10'><tr>";
echo "<th>ScheduleNumber</th>";
echo "<th>PRT_DR</th>";
echo "<th>PRT_DDF</th>";
echo "<th>PRT_FR</th>";
echo "<th>ScanDR</th>";
echo "<th>ScanDDF</th>";
echo "<th>ScanFR</th>";
echo "<th>PrpDR</th>";
echo "<th>PrpDDF</th>";
echo "<th>PrpFR</th>";



while (odbc_fetch_row($result)) {
  $bgcolor = odbc_result($result, "COLOR");  
  $uompScheduleNumber=odbc_result($result,"uompScheduleNumber");
   $uompScheduleColor=odbc_result($result,"Color");
  $PRT_DR=odbc_result($result,"PRT_DR");
  $PRT_DDF=odbc_result($result,"PRT_DDF");
   $PRT_FR=odbc_result($result,"PRT_FR");
  $DR=odbc_result($result,"DR");
  $DDF=odbc_result($result,"DDF");
  $FR=odbc_result($result,"FR");
   $DR_PRP=odbc_result($result,"DR_PRP");
  $DDF_PRP=odbc_result($result,"DDF_PRP");
  $FR_PRP=odbc_result($result,"FR_PRP");
  $Other=odbc_result($result,"Other");      

  echo "<tr><td bgcolor=$bgcolor >$uompScheduleNumber</td>";
  echo "<td style='text-align:center'>$PRT_DR</td>";
  echo "<td style='text-align:center'>$PRT_DDF</td>";
    echo "<td style='text-align:center'>$PRT_FR</td>";
  echo "<td style='text-align:center'>$DR</td>";
  echo "<td style='text-align:center'>$DDF</td>";
  echo "<td style='text-align:center'>$FR</td>";
   echo "<td style='text-align:center'>$DR_PRP</td>";
  echo "<td style='text-align:center'>$DDF_PRP</td>";
  echo "<td style='text-align:center'>$FR_PRP</td>";
}

odbc_close($connect);
?>
</body>
</html>

 

post-132948-0-25850300-1433880238_thumb.jpg

Barand Prolific Member

Barand

Posted
Posted

Can't you just make those 2 items links. EG

echo "<td style='text-align:center'><a href='subreport1.php?sn=$uompScheduleNumber'>$PRT_FR</a></td>";
echo "<td style='text-align:center'><a href='subreport2.php?sn=$uompScheduleNumber'>$DR</a></td>";
kat35601 Advanced Member

kat35601

Posted
  • Author
Posted 
  echo "<td style='text-align:center'><a href='kk_schedule_subreport.php?sn=$uompScheduleNumber&itm=DR'>$PRT_FR</a></td>";

what if I wanted to add a constant like in the example itm='DR' but what would be the correct syntax for that????

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.