Here is the entire code:
code: <?php
//Set info from submit
$Kiln = $_POST[Kiln];
$Charge = $_POST[Charge];
//set up the connection to the ISIS Database on our SQL Server
$myServer = "sql2008";
$myUser = "username";
$myPass = "password";
$myDB = "ISISWood";
//create an instance of the ADO connection object
$conn = new COM ("ADODB.Connection")
or die("Cannot start ADO");
//define connection string, specify database driver
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB;
$conn->open($connStr); //Open the connection to the database
//declare the SQL statement that will query the database
$query = "SELECT rudDescrip ,Supplier=ISNULL((SELECT MAX(smName)
FROM
TagMaster LEFT JOIN ReceivingHeader ON rehID=tagSourceID AND tagSourceType='Receiving' LEFT JOIN ProductionHeader ON ruhID=tagSourceID AND tagSourceType='Production' INNER JOIN SupplierMaster ON smID=rehSuppID
WHERE
(tagDestID='$Kiln' or tagDestID in (select a.ruhID from ProductionHeader a where a.ruhFileNo='<ruhFileNo>')) AND tagProductID=rudProductID AND tagLocationID=rudLocationID),'')
,rudPieces,rudVolume,Case When pvmValue is not null Then pvmValue Else 0 End AS Market,rudCost,rudTally,rudPcsPerPkg,pamDescrip=isnull(pamDescrip,'') ,TotalCost=(SELECT SUM(rd1.rudTotal) FROM ProductionDetail rd1
WHERE rd1.rudCategory='Consumed' AND (rd1.rudID='$Kiln' or rd1.rudID in (select a.ruhID from ProductionHeader a where a.ruhFileNo='<ruhFileNo>'))) ,TotalVol=isnull((select sum(a.rudVolume) from ProductionDetail a where
(a.rudID='$Kiln' or a.rudID in (select b.ruhID from ProductionHeader b where b.ruhFileNo='<ruhFileNo>')) and a.rudCategory='Consumed'),0) FROM ProductionDetail LEFT JOIN ProductValueMaster ON
rudProductID = pvmProductID and pvmDocType='GENERAL' LEFT JOIN ProductMaster on prdID=rudProductID left join ProductAttributeMaster on pamDocID=prdGradeID and pamDocType='Grade' WHERE rudCategory='Consumed'
and (rudID='$Kiln' or rudID in (select a.ruhID from ProductionHeader a where a.ruhFileNo='<ruhFileNo>')) order by rudDescrip";
//execute the SQL statement and return records - Input
?>
<h1>Kiln Input</h1>
<?php
$rs = $conn->execute($query);
$num_columns = $rs->Fields->Count();
echo $num_columns . "<br>";
for ($i=0; $i < $num_columns; $i++) {
$fld[$i] = $rs->Fields($i);
}
echo "<table>";
while (!$rs->EOF) //carry on looping through while there are records
{
echo "<tr>";
for ($i=0; $i < $num_columns; $i++) {
echo "<td>" . $fld[$i]->value . "</td>";
}
echo "</tr>";
$rs->MoveNext(); //move on to the next record
}
echo "</table>";
?>
Yeah it's a monster statement, but somehow it works :) |