Jump to content

Creating a link and passing data to open another report

kat35601

By kat35601
in PHP Coding Help

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????

Archived

This topic is now archived and is closed to further replies.

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.