<? include('includes/main.php'); ?>
<? //invitemlstsum.php
echo texttitle('Item List - Summary');
if ($order) {
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('For All Locations');
};
if ($order=="i") {
$recordSet = &$conn->Execute('select item.id, item.itemcode, item.description, itemcategory.name, sum(itemlocation.onhandqty), unitname.unitname, avg(itemlocation.firstcost), sum(itemlocation.firstqty), avg(itemlocation.midcost), sum(itemlocation.midqty), avg(itemlocation.lastcost), sum(itemlocation.lastqty), item.priceunitnameid from item,itemcategory,company,unitname,itemlocation,inventorylocation where inventorylocation.companyid=company.id and itemlocation.inventorylocationid=inventorylocation.id and itemlocation.itemid=item.id and itemcategory.id=item.categoryid and item.companyid='.sqlprep($active_company).' and item.stockunitnameid=unitname.id'.$locationstr.' group by item.id, item.itemcode, item.description, itemcategory.name, unitname.unitname, item.priceunitnameid order by item.itemcode');
if ($recordSet->EOF) die(texterror('No matching items found.'));
echo '<table border="1"><tr><th rowspan="2">Item Code</th><th rowspan="2">Item Name</th><th rowspan="2">Category</th><th rowspan="2">Qty on hand</th><th rowspan="2">Stock(Qty) Unit</th><th rowspan="2">Price Unit</th><th colspan="2">First</th><th colspan="2">Mid</th><th colspan="2">Last</th></tr>';
echo '<tr><th>Cost</th><th>Qty</th><th>Cost</th><th>Qty</th><th>Cost</th><th>Qty</th></tr>';
while (!$recordSet->EOF) {
$recordSet2 = &$conn->Execute('select unitname from unitname where id='.sqlprep($recordSet->fields[12]));
if (!$recordSet2->EOF) {
$priceunitname=$recordSet2->fields[0];
};
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>'.$priceunitname.'<td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[6],2).'</td><td>'.$recordSet->fields[7].'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[8],2).'</td><td>'.$recordSet->fields[9].'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[10],2).'</td><td>'.$recordSet->fields[11].'</td></tr>';
$recordSet->MoveNext();
};
echo '</table>';
} elseif ($order=="ci") {
$recordSet = &$conn->Execute('select item.id, itemcategory.name, item.itemcode, item.description, sum(itemlocation.onhandqty), unitname.unitname, avg(itemlocation.firstcost), sum(itemlocation.firstqty), avg(itemlocation.midcost), sum(itemlocation.midqty), avg(itemlocation.lastcost), sum(itemlocation.lastqty), item.priceunitnameid from item,itemcategory,company,unitname,itemlocation,inventorylocation where inventorylocation.companyid=company.id and itemlocation.inventorylocationid=inventorylocation.id and itemlocation.itemid=item.id and itemcategory.id=item.categoryid and item.companyid='.sqlprep($active_company).' and item.stockunitnameid=unitname.id'.$locationstr.' group by item.id, itemcategory.name, item.itemcode, item.description, unitname.unitname, item.priceunitnameid order by itemcategory.name, item.itemcode');
if (!$recordSet||$recordSet->EOF) die(texterror('No matching items found.'));
echo '<table border="1"><tr><th rowspan="2">Category</th><th rowspan="2">Item Code</th><th rowspan="2">Item Name</th><th rowspan="2">Qty on hand</th><th rowspan="2">Stock(Qty) Unit</th><th rowspan="2">Price Unit</th><th colspan="2">First</th><th colspan="2">Mid</th><th colspan="2">Last</th></tr>';
echo '<tr><th>Cost</th><th>Qty</th><th>Cost</th><th>Qty</th><th>Cost</th><th>Qty</th></tr>';
while (!$recordSet->EOF) {
$recordSet2 = &$conn->Execute('select unitname from unitname where id='.sqlprep($recordSet->fields[12]));
if (!$recordSet2->EOF) {
$priceunitname=$recordSet2->fields[0];
};
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>'.$priceunitname.'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[6],2).'</td><td>'.$recordSet->fields[7].'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[8],2).'</td><td>'.$recordSet->fields[9].'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[10],2).'</td><td>'.$recordSet->fields[11].'</td></tr>';
$recordSet->MoveNext();
};
echo '</table>';
} elseif ($order=="cn") {
$recordSet = &$conn->Execute('select item.id, itemcategory.name, item.description, item.itemcode, sum(itemlocation.onhandqty), unitname.unitname, avg(itemlocation.firstcost), sum(itemlocation.firstqty), avg(itemlocation.midcost), sum(itemlocation.midqty), avg(itemlocation.lastcost), sum(itemlocation.lastqty), item.priceunitnameid from item,itemcategory,company,unitname,itemlocation,inventorylocation where inventorylocation.companyid=company.id and itemlocation.inventorylocationid=inventorylocation.id and itemlocation.itemid=item.id and itemcategory.id=item.categoryid and item.companyid='.sqlprep($active_company).' and item.stockunitnameid=unitname.id'.$locationstr.' group by item.id, itemcategory.name, item.description, item.itemcode, unitname.unitname, item.priceunitnameid order by itemcategory.name, item.description, item.itemcode');
if (!$recordSet||$recordSet->EOF) die(texterror('No matching items found.'));
echo '<table border="1"><tr><th rowspan="2">Category</th><th rowspan="2">Item Name</th><th rowspan="2">Item Code</th><th rowspan="2">Qty on hand</th><th rowspan="2">Stock(Qty) Unit</th><th rowspan="2">Price Unit</th><th colspan="2">First</th><th colspan="2">Mid</th><th colspan="2">Last</th></tr>';
echo '<tr><th>Cost</th><th>Qty</th><th>Cost</th><th>Qty</th><th>Cost</th><th>Qty</th></tr>';
while (!$recordSet->EOF) {
$recordSet2 = &$conn->Execute('select unitname from unitname where id='.sqlprep($recordSet->fields[12]));
if (!$recordSet2->EOF) {
$priceunitname=$recordSet2->fields[0];
};
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>'.$priceunitname.'<td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[6],2).'</td><td>'.$recordSet->fields[7].'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[8],2).'</td><td>'.$recordSet->fields[9].'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[10],2).'</td><td>'.$recordSet->fields[11].'</td></tr>';
$recordSet->MoveNext();
};
echo '</table>';
};
} else {
echo '<form action="invitemlstsum.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="ci">Category, Item Code';
echo '<option value="i">Item Code';
echo '<option value="cn">Category, Item Name';
echo '</select></td></tr>';
echo '</table><input type="submit" value="Create Report"></form>';
};
?>
<? include('includes/footer.php'); ?>