I help manage a Citrix MetaFrame environment of 5 Terminal Servers,
one of the managers came to me asking that I put MS Access on the
Terminal server to allow our customers to run an access db that
generated a report.

After looking at the report I told the manager I could generate
the same report from a web applications. Not telling her what 
technology I was going to use, I downloaded FreeTDS and configured
it, recompiled PHP. Now I had access to MSSQL server that housed the
data for this report...

This manager also stated that only certian customers needed accesses
to the data, so I configured UserServer to handle this task...
Since I didn't want to have to manage another user database, I decided
to build the application around NT's security and groups. 

Started Hacking ...

Created a login script and it did what it was designed to do. Check it
out  here ... Using functions from UserServer.

THE CODE HERE ONLY LETS YOU SEE HOW TO USE THE PHP FUNCTIONS!!!
NO OUTPUT....!!!

rptvariables.php

< ?php
// Db include file
 $f=mssql_connect("server","sa",'passwd') or die("Failed to Connect to DB");
 mssql_select_db("database");

// Start Session
 session_start();
$SID = session_id();


 // Format date report ran
$today = date("F j, Y, g:i a");
$time = date("H:i:s");
$_connectedfrom = $COMPUTERNAME;

$loginpage = "http:///erptsys/login.php";
$rptpage = "http:///erptsys/createrpt.php?SID=$SID";
$mainpage = "< A HREF=".$rptpage."> Report Tool< /a> ";

function navBar($link_array,$page_dir=".")
{
   reset($link_array);
   echo "< TABLE BORDER='0'> < TR> \n";
   while (list($key, $val) = each($link_array)) {
      $label = ucfirst($key);
      $link = $page_dir . "/$val";
         echo "< TD align='CENTER'> < A HREF=$link> ";
         echo "< FONT SIZE='1' FACE='Arial'> $label
         < /FONT> < /TD> \n";
   }
   echo "< /TR> < /TABLE> \n";
}



 ?> 




After a successful login this script was called...

createrpt.php

< ?php
require_once('rptvariables.php');
if(!$LOGGEDIN) {
 header ("Location: $loginpage");
 } else {
$links_array = array(
"PO# Report With Descr" =>  "genportp.php?SID=$SID",
"PO# Report Only" =>  "listponumbers.php?SID=$SID",
"Logout" =>  "logout.php?SID=$SID"

);
print "< center> ";
print "WELCOME < br> < b> ". $realname ."< /b> ";
print "< H3> Choose the Type of Report< /H3> ";
print $today;
navBar($links_array);
print "< /center> ";
}
?> 


A complete Report..

genportp.php


< ?php
/*
** Function: Create a Purchase report by Account
** Coder: Chauncey Thorn
** 
*/

// get variables for database connection

require_once('rptvariables.php');


if(!$LOGGEDIN) {
 header ("Location: $loginpage");
 } else {

// set the timeout to longer, the script was timing out after 30 sec
set_time_limit(180);

// Format date report ran
$today = date("F j, Y, g:i a");

if($submit) {
 if((!$date1) || (!$date2)) {
     print "< H3> $realname, You Must enter a Date Range< /H3> ";
 } else {
// Which site are we dealing with
// Views created to handle each site...
// 03-15-01 moved to whichsite.php

// include_once('whichsite.php');
//  changed to decrease code..

// This report required some very large SQL queries
// So I created VIEWs for each site....
$viewhist = "WEB".$sitecode."RPT";
$view= "WEB".$sitecode."RPT;
$oursite = $sitecode;


// had to turn this on for query to execute
$setansi_null = mssql_query("SET ANSI_NULLS ON");
$setansi_warnings = mssql_query("SET ANSI_WARNINGS ON");

// get the total of all polt

$getpolt_total = "SELECT  SUM(POLT) AS GTotalpolt
    FROM $viewhist
    WHERE (PURORDDAT BETWEEN \"$date1\" AND \"$date2\")
    UNION
    SELECT  SUM(POLT) AS GTotalpolt
    FROM $view    WHERE (PURORDDAT BETWEEN \"$date1\" AND \"$date2\")
    ";

 // Execute query to get grand total

 $getGtotalpolt = mssql_query($getpolt_total);
      if(!$getGtotalpolt) {
        print "ERROR Getting POLT Total";
      } else {
        $pototal = mssql_result($getGtotalpolt,0,"GTotalpolt");
        $pototal = "\$ " .sprintf("%.2f", $pototal);
      } // end of getGtotalpolt


// get the account number and total
// defined $viewhist and $viewto handle muiltple sites

$getactnumbr = "SELECT ACTNUMBR, ACTDESCR, SUM(POLT) AS Totalpolt
                FROM $viewhist
                WHERE (PURORDDAT BETWEEN \"$date1\" AND \"$date2\")
                GROUP BY ACTNUMBR, ACTDESCR
                UNION
                SELECT ACTNUMBR, ACTDESCR, SUM(POLT) AS Totalpolt
                FROM $view                WHERE (PURORDDAT BETWEEN \"$date1\" AND \"$date2\")
                GROUP BY ACTNUMBR, ACTDESCR
                ";

// Get account numbers and the sum of each
$resactnumbr=mssql_query($getactnumbr);

if($resactnumbr) {
   // Display Header
   print "< H2> Purchase Orders By Account< /H2> ";
   print $today . "< br> ";
   // Display date range for query and display the site location
   print "< font size=-2> ";
   print "Report ran for ". $date1 ." through ".$date2 . " for  Location ".$oursite ;
   print "< br> ";
   print $realname ." Requested the Report";
   print "< /font> ";
   print "< P> ";

   // Fetch data into an array
   // and extract the variables from the fieldname

   while($row=mssql_fetch_array($resactnumbr)) {
     extract($row);
     // did this to pass it to the where clause

     $ACCTNUM = $ACTNUMBR;

     $getdata = " SELECT ACTNUMBR, PURORDDAT, PURORDNUM, ITMNUM,";
     $getdata .= " LOCNCODE, QYORD, ACTUNTCST, POLT, ACTDESCR, VENDNAME";
     $getdata .= " FROM $viewhist";
     $getdata .= "   WHERE (PURORDDAT BETWEEN '$date1' AND '$date2') AND ACTNUMBR =
'$ACCTNUM'";
     $getdata .= "   ORDER BY ACTNUMBR";
     $getdate .= " UNION";
     $getdata = " SELECT ACTNUMBR, PURORDDAT, PONUMWRK AS PURORDNUM, ITMNUM,";
     $getdata .= " LOCNCODE, QYORD, Cost_Per_Unit_UFM_DOL AS ACTUNTCST, POLT, ACTDESCR,
VENDNAME";
     $getdata .= " FROM $view;
     $getdata .= "   WHERE (PURORDDAT BETWEEN '$date1' AND '$date2') AND ACTNUMBR =
'$ACCTNUM'";
     $getdata .= "   ORDER BY ACTNUMBR";

// execute query assign variable $res
     $res=mssql_query($getdata);

// get the total rows returned from query
     $totalrows = mssql_num_rows($res);

// Check to make sure there's data and no error

        if(!$res) {

             print "ERROR: Running Query";

             // Print the query if there was an error

             print  "$getdata";
        } else {

           // Format total POLT to look like money

           $Totalpolt = "\$ " .sprintf("%.2f", $Totalpolt);

           // Send data to the browser using reqular HTML statements
           // Table headers

           print "< center> ";
           print "< table border=0 cellpadding=2 cellspacing=0 width=640> ";
           print "< tr bgcolor=CCCCCC> < td colspan=2 align=left> < b>
$ACCTNUM< /b> < /td> ";
           print "< td colspan=2 align=center> < b> $ACTDESCR< /b> < /td>
< td>  < /td> ";
           print "< td>  < /td> < td>  < /td> < td>
 < /td> < /tr> ";
           print "< tr> 

                < th> Date< /th> 
                < th> PO# < /th> 
                < th> Vendor< /th> 
                < th> Item< /th> 
                < th> Site< /th> 
                < th> Qty< /th> 
                < th align=right> Unit Cost< /th> 
                < th align=right> Extended Cost< /th> 
               < /tr> ";
         print "< tr> < td colspan=8> < hr noshade> < /td> < /tr> ";
               while($row=mssql_fetch_array($res)) {
                      extract($row);
                       // Format the output
                        $QYORD = sprintf("%.0f", $QYORD);
                        $ACTUNTCST = "\$ ".sprintf("%.2f", $ACTUNTCST);
                        $POLT = "\$ ".sprintf("%.2f", $POLT);

                        // Change the size of the text to fit on page better


                        $PURORDDAT = "< font size=\"-2\"> ".$PURORDDAT."< /font> ";
                        $PURORDNUM = "< font size=\"-2\"> ".$PURORDNUM."< /font> ";
                        $VENDNAME = "< font size=\"-2\"> ".$VENDNAME."< /font> ";
                        $ITMNUM = "< font size=\"-2\"> ".$ITMNUM."< /font> ";
                        $LOCNCODE = "< font size=\"-2\"> ".$LOCNCODE."< /font> ";
                        $QYORD = "< font size=\"-2\"> ".$QYORD." Each< /font> ";
                        $ACTUNTCST = "< font size=\"-2\"> ".$ACTUNTCST."< /font> ";
                        $POLT = "< font size=\"-2\"> ".$POLT."< /font> ";

                        // HACK this may cause problems later...
                        // hard coding the 12:00AM
                        // The 12:00AM may not be included
                        // will fix later..

                        $PURORDDAT = ereg_replace("12:00AM","",$PURORDDAT);

                        // send the data to the client
                        // data has been formatted to small fonts..

                        print "< tr> < td> "
                        .$PURORDDAT."< /td> < td> ".$PURORDNUM."< /td> <
td> ".$VENDNAME
                        ."< /td> < td> ".$ITMNUM
                        ."< /td> < td> ".$LOCNCODE."< /td> < td
align=right> ".$QYORD
                        ." < /td> < td align=right> ".$ACTUNTCST
                        ."< /td> < td align=right> ".$POLT."< /td> < /tr>
";

               } // end while loop

               print "< tr> < td colspan=8> < hr noshade> < /td> <
/tr> ";
               print "< tr bgcolor=CCCCCC> < td colspan=4 align=left> < b>
TOTAL< /b> < /td> ";
               print "< td colspan=4 align=right> < b> $Totalpolt< /b> <
/td> < /tr> ";
               print "< /table> ";
               print "< p> ";
         } // end of if(!$res) check

      } // end of while loop
      print "< table border=0 cellpadding=2 cellspacing=0 width=640> ";
      print "< tr> < td colspan=4 align=left> < b> GRAND TOTAL< /b> <
/td> < td colspan=4 align=right> < b> $pototal< /b> < /td> < /tr>
";
      print "< /table> ";
      print $mainpage;
      mssql_close($f);
   } else {
           print "Error in Query";
   }
 }
} else {



print< << FORMTOP
< CENTER> 
$realname   Please Enter Begin and End Date
< BR> 

< FORM ACTION=$PHP_SELF?SID=$SID METHOD=post> 
< INPUT TYPE=text NAME=date1> 
  to  
< INPUT TYPE=text NAME=date2> 
< BR> 
CHOSE YOUR SITE< br> 
FORMTOP;
include('oursites.php');

print< << FORMBOTTOM
< P> 
< INPUT TYPE=submit NAME=submit VALUE=EXECUTE> 
< /FORM> 
< P> 
$mainpage
< /CENTER> 
FORMBOTTOM;
}

} // of checklogin
?> 

>> Comments/FeedBack