Location: PHPKode > projects > NOLA > invitemlststatus.php
<? include('includes/main.php'); ?>
<? include('includes/invfunctions.php'); ?>
<? //invitemlststatus.php


     echo texttitle('Item List - Status');
     if ($order) {
          if (!$userid) die(texterror('UserID not found.'));
          $conn->Execute('drop table invitemliststatus'.$userid);
          $conn->Execute('create table invitemliststatus'.$userid.' (companycompanyname char(50), itemcategoryname char(50), itemitemcode char(50), itemitemname char(100), itemqtyonhand double, itemqtyordered double, itemqtycommitted double, itemqtyavailable double, itemqtyonorder double)');
          if ($location) {
               $locationstr=' and inventorylocation.id='.sqlprep($location);
               $recordSet = &$conn->Execute('select company.companyname from inventorylocation,company where company.id=inventorylocation.companyid and inventorylocation.id='.sqlprep($location));
               if (!$recordSet->EOF) {
                     echo texttitle("For Location: ".$recordSet->fields[0]);
               };
          } else {
               echo texttitle("All Locations");
          };
          $recordSet = &$conn->Execute('select company.companyname, itemcategory.name, item.itemcode, item.description, itemlocation.onhandqty as onhand from item,itemcategory, inventorylocation, company, itemlocation where company.id=inventorylocation.companyid and inventorylocation.id=itemlocation.inventorylocationid and itemlocation.itemid=item.id and item.companyid='.sqlprep($active_company).' and itemcategory.id=item.categoryid'.$locationstr.' group by itemlocation.id order by company.companyname, itemcategory.name, item.itemcode');
          while (!$recordSet->EOF) {
               $conn->Execute('insert into invitemliststatus'.$userid.' (companycompanyname, itemcategoryname, itemitemcode, itemitemname, itemqtyonhand) values ('.sqlprep($recordSet->fields[0]).', '.sqlprep($recordSet->fields[1]).', '.sqlprep($recordSet->fields[2]).', '.sqlprep($recordSet->fields[3]).', '.sqlprep($recordSet->fields[4]).')');
               $recordSet->MoveNext();
          };
          $recordSet = &$conn->Execute('select item.itemcode, sum(arorderdetail.qtyorder) from item,arorderdetail where arorderdetail.itemid=item.id group by item.id');
          while (!$recordSet->EOF) {
               $conn->Execute('update invitemliststatus'.$userid.' set itemqtyordered='.sqlprep($recordSet->fields[1]).' where itemitemcode='.sqlprep($recordSet->fields[0]));
               $recordSet->MoveNext();
          };
          $recordSet = &$conn->Execute('select item.itemcode, sum(arordershipdetail.shipqty) from item,arorderdetail,arordershipdetail where arorderdetail.itemid=item.id and arordershipdetail.orderdetailid=arorderdetail.id group by item.id');
          while (!$recordSet->EOF) {
               $conn->Execute('update invitemliststatus'.$userid.' set itemqtycommitted=(itemqtyordered-'.sqlprep($recordSet->fields[1]).'), itemqtyavailable=(itemqtyonhand-itemqtycommitted) where itemitemcode='.sqlprep($recordSet->fields[0]));
               $recordSet->MoveNext();
          };
          $conn->Execute('update invitemliststatus'.$userid.' set itemqtycommitted=0 where itemqtycommitted is null');
          $conn->Execute('update invitemliststatus'.$userid.' set itemqtyavailable=0 where itemqtyavailable is null');
          $conn->Execute('update invitemliststatus'.$userid.' set itemqtyonorder=0 where itemqtyonorder is null');
          if ($order=="lci") {
               $recordSet = &$conn->Execute('select stat.companycompanyname, stat.companycompanyname, stat.itemcategoryname, stat.itemitemcode, stat.itemitemname, stat.itemqtyonhand, stat.itemqtycommitted, stat.itemqtyavailable, stat.itemqtyonorder from invitemliststatus'.$userid.' as stat order by stat.companycompanyname, stat.itemcategoryname, stat.itemitemcode');
               if (!$recordSet||$recordSet->EOF) die(texterror('No matching items found.'));
               echo '<table border="1"><tr><th>Location</th><th>Category</th><th>Item Code</th><th>Item Name</th><th>Qty On Hand</th><th>Qty Committed</th><th>Qty Available</th><th>Qty On Order</th></tr>';
               while (!$recordSet->EOF) {
                    echo '<tr><td><b>'.$recordSet->fields[1].'</b></td><td>'.$recordSet->fields[2].'</td><td>'.$recordSet->fields[3].'</td><td>'.$recordSet->fields[4].'</td><td>'.$recordSet->fields[5].'</td><td><a href="arorderitemlst.php?itemcode='.$recordSet->fields[1].'">'.$recordSet->fields[6].'</a></td><td>'.$recordSet->fields[7].'</td><td>&nbsp;'.$recordSet->fields[8].'</td></tr>';
                    $recordSet->MoveNext();
               };
               echo '</table>';
          } elseif ($order=="li") {
               $recordSet = &$conn->Execute('select stat.companycompanyname, stat.companycompanyname, stat.itemitemcode, stat.itemitemname, stat.itemcategoryname, stat.itemqtyonhand, stat.itemqtycommitted, stat.itemqtyavailable, stat.itemqtyonorder from invitemliststatus'.$userid.' as stat order by stat.companycompanyname, stat.itemitemcode');
               if (!$recordSet||$recordSet->EOF) die(texterror('No matching items found.'));
               echo '<table border="1"><tr><th>Location</th><th>Item Code</th><th>Item Name</th><th>Category</th><th>Qty On Hand</th><th>Qty Committed</th><th>Qty Available</th><th>Qty On Order</th></tr>';
               while (!$recordSet->EOF) {
                    echo '<tr><td><b>'.$recordSet->fields[1].'</b></td><td>'.$recordSet->fields[2].'</td><td>'.$recordSet->fields[3].'</td><td>'.$recordSet->fields[4].'</td><td>'.$recordSet->fields[5].'</td><td><a href="arorderitemlst.php?itemcode='.$recordSet->fields[1].'">'.$recordSet->fields[6].'</a></td><td>'.$recordSet->fields[7].'</td><td>'.$recordSet->fields[8].'</td></tr>';
                    $recordSet->MoveNext();
               };
               echo '</table>';
          } elseif ($order=="ci") {
               $recordSet = &$conn->Execute('select stat.companycompanyname, stat.itemcategoryname, stat.itemitemcode, stat.itemitemname, sum(stat.itemqtyonhand), stat.itemqtycommitted, stat.itemqtyonorder from invitemliststatus'.$userid.' as stat group by stat.itemitemcode order by stat.itemcategoryname, stat.itemitemcode');
               if (!$recordSet||$recordSet->EOF) die(texterror('No matching items found.'));
               echo '<table border="1"><tr><th>Category</th><th>Item Code</th><th>Item Name</th><th>Qty On Hand</th><th>Qty Committed</th><th>Qty Available</th><th>Qty On Order</th></tr>';
               while (!$recordSet->EOF) {
                    echo '<tr><td><b>'.$recordSet->fields[1].'</b></td><td>'.$recordSet->fields[2].'</td><td>'.$recordSet->fields[3].'</td><td>'.$recordSet->fields[4].'</td><td><a href="arorderitemlst.php?itemcode='.$recordSet->fields[1].'">'.$recordSet->fields[5].'</a></td><td>'.($recordSet->fields[4]-$recordSet->fields[5]).'</td><td>'.$recordSet->fields[6].'</td></tr>';
                    $recordSet->MoveNext();
               };
               echo '</table>';
          } elseif ($order=="i") {
               $recordSet = &$conn->Execute('select stat.companycompanyname, stat.itemitemcode, stat.itemitemname, stat.itemcategoryname, sum(stat.itemqtyonhand), stat.itemqtycommitted, stat.itemqtyonorder from invitemliststatus'.$userid.' as stat group by stat.itemitemcode order by stat.itemitemcode');
               if (!$recordSet||$recordSet->EOF) die(texterror('No matching items found.'));
               echo '<table border="1"><tr><th>Item Code</th><th>Item Name</th><th>Category</th><th>Qty On Hand</th><th>Qty Committed</th><th>Qty Available</th><th>Qty On Order</th></tr>';
               while (!$recordSet->EOF) {
                    if ($recordSet->fields[5]) $link='<a href="arorderitemlst.php?itemcode='.$recordSet->fields[1].'">';
                    echo '<tr><td><b>'.$recordSet->fields[1].'</b></td><td>'.$recordSet->fields[2].'</td><td>'.$recordSet->fields[3].'</td><td>'.$recordSet->fields[4].'</td><td>'.$link.$recordSet->fields[5].'</a></td><td>'.($recordSet->fields[4]-$recordSet->fields[5]).'</td><td>'.$recordSet->fields[6].'</td></tr>';
                    unset($link);
                    $recordSet->MoveNext();
               };
               echo '</table>';
          };
          $conn->Execute('drop table invitemliststatus'.$userid);
     } else {
          echo '<form action="invitemlststatus.php" method="post"><table>';
          $recordSet = &$conn->Execute('select count(*) from inventorylocation,company where company.id=inventorylocation.companyid and inventorylocation.gencompanyid='.sqlprep($active_company));
          if (!$recordSet->EOF) if ($recordSet->fields[0]>1) {
                  echo '<tr><td align="'.TABLE_LEFT_SIDE_ALIGN.'">Location:</td><td><select name="location"'.INC_TEXTBOX.'><option value="0">All';
                  $recordSet = &$conn->Execute('select inventorylocation.id,company.companyname from inventorylocation,company where company.id=inventorylocation.companyid and inventorylocation.gencompanyid='.sqlprep($active_company).' order by company.companyname');
                  while (!$recordSet->EOF) {
                       echo '<option value="'.$recordSet->fields[0].'">'.$recordSet->fields[1]."\n";
                       $recordSet->MoveNext();
                  };
                  echo '</select></td></tr>';
          };
          echo '<tr><td align="'.TABLE_LEFT_SIDE_ALIGN.'">Sort By:</td><td><select name="order"'.INC_TEXTBOX.'>';
          echo '<option value="i">Item Code';
          echo '<option value="ci">Category, Item Code';
          echo '<option value="lci">Location, Category, Item Code';
          echo '<option value="li">Location, Item Code';
          echo '</select></td></tr>';
          echo '</table><input type="submit" value="Create Report"></form>';
     };
?>
<? include('includes/footer.php'); ?>
Return current item: NOLA