<?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>"; ?>
Here is a screenshot of what it produces.
As you can see I could really use a map for this DB.
I only got that select statement by capturing it on the SQL Server as I queried it from the software.
Actually that's not the code that produces this screenshot, there are two select statements to get the screen shot.
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 = '' ) ) AND tagproductid = rudproductid AND taglocationid = rudlocationid ) , '' ), /* /supplier */ 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 = '' ) ) ), /* /totalcost */ 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 = '' ) ) AND a.rudcategory = 'Consumed' ) , 0 ) /* /totalvol */ 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 = '' ) ) ORDER BY ruddescrip
Yeah it is sorta.
But here's why I'm doing it. The company that provides the software are a bunch of ass hats. As you can see by that select statement.
When the people who work where I work want to do any kind of math on that database their answer is dumbing it into excel. Fuck that. All we get done doing is typing shit into excel. It's in the database, why the fuck should I have to do that?
So I am going to automate the processes, and really using PHP might be temporary, I just want to see what I am able to farm out of it and how hard it is going to be.
I'll have to take a look at the sql server. I can get a list of the tables (all 1000 or so of them) but there isn't any logical order to them and I don't know what goes where.
Hmmm, I wouldn't be surprised if there aren't any foreign keys setup then. :/
Anyway, this is a rather long-winded video that explains how to create a diagram, if you're able to connect via Management Studio.
http://www.youtube.com/watch?v=wMbPRHeYvMU#t=40s
Peter my ad was for a PHP wizard, not for going back to school to learn to be a php wizard! :-O
Just messin. I appreciate you looking at it. I can access the Management Studio.
Oh and speaking of that, sorta. Would you do what I'm trying in PHP or in the SSRS? I haven't been too fond of the reporting services but it could probably do what I wanted as well.
I love ColdFusion and would prefer using it but the Developer version only allows 2 connections and if this works I'll need more than that.
You know what you should do. Develop a program that converts php to cf and vice versa. You could probably make some good money.
So the tags are the same? Is it free? That could be a sweet deal there!
I bet you could make a few dollars if you made a converter. I've needed to do it from time to time and a few dollars for software to do it over me would have been a no brainer.
%-)
It's funny that you have considered it.
<Host name="domain.com" appBase="webapps"> <Alias>www.domain.com</Alias> <Context path="" docBase="/path/to/webroot"/> </Host>