Author: Martin Donovan | GitHub gist | profile.json
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package net.martindonovan.Inventory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import net.martindonovan.Inventory.util.ApplicationData;
import net.martindonovan.Inventory.util.SimpleDate;
/**
*
* @author Martin Donovan
*/
// TODO
// change so that the correct previous inventory date is used for items
// of each major category
//TODO - make sure to adjust for beginning inventory + 1 in queries
public class TheoreticalCountMap
{
// private HashMap<Integer, Vector<Integer>> majorCategoryProductInstanceLocationMap = new HashMap();
private HashMap<Integer, Float> locationTheoreticalMap = new HashMap();
private HashMap<Integer, Vector<Integer>> productInstanceIDMap = new HashMap();
private HashMap<Integer, Float> startingInventoryMap = new HashMap();
private HashMap<Integer, Float> bottleSalesMap = new HashMap();
private HashMap<Integer, Float> glassSalesMap = new HashMap();
private HashMap<Integer, Float> purchasesMap = new HashMap();
// private HashMap<Integer, Float> creditPurchaseMap = new HashMap();
// private HashMap<Integer, Float> staffEducationCreditMap = new HashMap();
// private HashMap<Integer, Float> giveawayCreditsMap = new HashMap();
private HashMap<Integer, Float> transfersFromMap = new HashMap();
private HashMap<Integer, Float> transfersToMap = new HashMap();
private HashMap<Integer, ArrayList<Integer>> productInstancesMap = new HashMap();
private HashMap<Integer, ArrayList<Integer>> categoryMap;
private Connection conn;
private SimpleDate endDate;
private HashMap<Integer, Inventory> beginningInventoryMap = new HashMap();
public TheoreticalCountMap()
{
this.endDate = new SimpleDate();
try
{
conn = ApplicationData.getConnection();
getBeginningInventories();
getProductInstanceIDs();
getProductInstanceLocations();
for( Integer i : beginningInventoryMap.keySet() )
{
getLocationTheoreticals(i);
}
computeLocationTheoreticals();
conn.close();
}
catch (java.sql.SQLException ex)
{
ex.printStackTrace();
}
}
public HashMap<Integer, ArrayList<Integer>> getProductInstancesMap()
{
return productInstancesMap;
}
private void getProductInstanceIDs()
{
try
{
categoryMap = new HashMap();
Connection c = ApplicationData.getConnection();
String q = "select pi.product_id, pi.product_instance_id, wine_list_category_id " +
// "from product_instances pi where pi.active = ?";
"from product_instances pi";
PreparedStatement ps = conn.prepareStatement(q);
// ps.setBoolean(1, true);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
int pid = rs.getInt("product_id");
int piid = rs.getInt("product_instance_id");
int wlcid = rs.getInt("wine_list_category_id");
if (! productInstancesMap.containsKey(pid))
{
productInstancesMap.put(pid, new ArrayList<Integer>());
}
productInstancesMap.get(pid).add(piid);
if (! categoryMap.containsKey(wlcid))
categoryMap.put(wlcid, new ArrayList<Integer>());
categoryMap.get(wlcid).add(piid);
}
c.close();
}
catch (SQLException ex)
{
ex.printStackTrace();
}
}
public TheoreticalCountMap(SimpleDate date)
{
this.endDate = date;
try
{
conn = ApplicationData.getConnection();
getBeginningInventories();
getProductInstanceLocations();
for( Integer i : beginningInventoryMap.keySet() )
{
getLocationTheoreticals(i);
}
computeLocationTheoreticals();
conn.close();
}
catch (java.sql.SQLException ex)
{
ex.printStackTrace();
}
}
public TheoreticalCountMap(java.util.Date date)
{
this.endDate = new SimpleDate(date);
try
{
conn = ApplicationData.getConnection();
getBeginningInventories();
getProductInstanceLocations();
for( Integer i : beginningInventoryMap.keySet() )
{
getLocationTheoreticals(i);
}
computeLocationTheoreticals();
conn.close();
}
catch (java.sql.SQLException ex)
{
ex.printStackTrace();
}
}
private void getBeginningInventories() throws SQLException
{
Connection conn = ApplicationData.getConnection();
String q = "select major_category_id from major_categories where major_category_id in " +
"(select distinct(inventory_major_category_id) from inventories)";
// where clause - only major categories that have an inventory
PreparedStatement ps = conn.prepareStatement(q);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Integer cid = rs.getInt("major_category_id");
//TODO: handle case where getBeginningInventory returns null
//maybe insert an inventory before first invoice / credit / sales date for that category?
Inventory i = getBeginningInventory(cid);
beginningInventoryMap.put(cid, i);
}
conn.close();
}
private Inventory getBeginningInventory(Integer cid) throws SQLException
{
Inventory i;
Connection conn = ApplicationData.getConnection();
String q = "select inventory_id, inventory_date from inventories " +
"where inventory_major_category_id = ? and " +
"inventory_date = " +
"(select max(inventory_date) from inventories where " +
"inventory_major_category_id = ? and inventory_date < ?)";
PreparedStatement ps = conn.prepareStatement(q);
ps.setInt(1, cid);
ps.setInt(2, cid);
ps.setDate(3, endDate.toSQLDate());
ResultSet rs = ps.executeQuery();
if (rs.next())
i = new Inventory(rs.getInt("inventory_id"), new SimpleDate(rs.getDate("inventory_date")));
else
i = null;
conn.close();
return i;
}
public Float getTheoreticalByProductInstanceId(int pid)
{
Float total = 0f;
if (productInstanceIDMap.containsKey(pid))
{
for (Integer lid : productInstanceIDMap.get(pid))
{
total += locationTheoreticalMap.containsKey(lid) ? locationTheoreticalMap.get(lid) : 0;
}
}
return total;
}
public Float getTotalBottlesForWineListCategory(int wlcid)
{
Float total = null;
if (categoryMap.containsKey(wlcid))
{
float t = 0;
for (Integer piid : categoryMap.get(wlcid))
{
t += this.getTheoreticalByProductInstanceID(piid);
}
}
return total;
}
public ArrayList<Integer> getWineListCategoryProductInstanceIDs(Integer wlcid)
{
ArrayList al = null;
if (categoryMap.containsKey(wlcid))
al = categoryMap.get(wlcid);
return al;
}
public void refreshProductID(Integer id)
{
ArrayList<Integer> locationIds = new ArrayList();
String q = "select pila.product_instance_location_id from "
+ "product_instances pi, product_instanceLocation_associations pila "
+ "where pi.product_instance_id = pila.product_instance_id "
+ "and pi.product_id = ? ";
PreparedStatement ps;
ResultSet rs;
Connection c = ApplicationData.getConnection();
try
{
ps = c.prepareCall(q);
ps.setInt(1, id);
rs = ps.executeQuery();
while (rs.next())
{
locationIds.add(rs.getInt("product_instance_location_id"));
}
}
catch (SQLException ex)
{
ex.printStackTrace();
}
for (Integer lid : locationIds)
{
refreshProductInstanceLocationID(lid);
}
}
public void refreshProductInstanceID(Integer id)
{
ArrayList<Integer> locationIds = new ArrayList();
String q = "select pila.product_instance_location_id from "
+ "product_instanceLocation_associations pila "
+ "where pila.product_instance_id = ? ";
PreparedStatement ps;
ResultSet rs;
Connection c = ApplicationData.getConnection();
try
{
ps = c.prepareCall(q);
ps.setInt(1, id);
rs = ps.executeQuery();
while (rs.next())
{
locationIds.add(rs.getInt("product_instance_location_id"));
}
}
catch (SQLException ex)
{
ex.printStackTrace();
}
for (Integer lid : locationIds)
{
refreshProductInstanceLocationID(lid);
}
}
public void refreshProductInstanceLocationID(Integer id)
{
}
public void addNewLocation(int pilid)
{
boolean exists = false;
Integer piid = null;
String q = "select pi.product_instance_id "
+ "from product_instances pi, "
+ "product_instance_location_associations pila "
+ "where pila.product_instance_id = pi.product_instance_id "
+ "and pila.product_instance_location_id = ?";
try
{
PreparedStatement ps = conn.prepareStatement(q);
ps.setInt(1, pilid);
ResultSet rs = ps.executeQuery();
if (rs.next())
{
exists = true;
piid = rs.getInt("product_instance_id");
}
}
catch (SQLException ex)
{
ex.printStackTrace();
}
if (! locationTheoreticalMap.containsKey(pilid) && exists)
{
if (productInstanceIDMap.containsKey(piid) )
{
locationTheoreticalMap.put(pilid, 0f);
productInstanceIDMap.get(piid).add(pilid);
}
}
}
// Should work correctly - could have a problem
public void refresh()
{
try
{
conn = ApplicationData.getConnection();
getBeginningInventories();
getProductInstanceLocations();
clearMaps();
for(Integer i: beginningInventoryMap.keySet() )
{
getLocationTheoreticals(i);
}
computeLocationTheoreticals();
conn.close();
}
catch (java.sql.SQLException ex)
{
ex.printStackTrace();
}
}
private void clearMaps()
{
//TODO - clear all maps?
glassSalesMap.clear();
}
public Float getTheoreticalByLocationID(int plid)
{
Float theo = new Float(0);
if (locationTheoreticalMap.containsKey(plid))
{
theo = locationTheoreticalMap.get(plid);
}
else
theo = null;
return theo;
}
public Float getTheoreticalByProductID(int pid)
{
Float theo = null;
ArrayList<Integer> productInstanceIDs = productInstancesMap.get(pid);
if ( productInstanceIDs != null && productInstanceIDs.size() > 0)
{
theo = 0f;
for (Integer piid : productInstanceIDs)
{
theo += this.getTheoreticalByProductInstanceID(piid);
}
}
return theo;
}
public Float getTheoreticalByProductInstanceID(int pid)
{
Float theo = new Float(0);
if (productInstanceIDMap.containsKey(pid))
{
for(Integer i : productInstanceIDMap.get(pid))
{
theo += getTheoreticalByLocationID(i);
}
}
else
theo = null;
return theo;
}
private void getProductInstanceLocations() throws java.sql.SQLException
{
String q = "select pila.product_instance_location_id, pila.product_instance_id " +
"from product_instance_location_associations pila " ;
PreparedStatement ps = conn.prepareStatement(q);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Integer piid = rs.getInt("product_instance_id");
Integer plid = rs.getInt("product_instance_location_id");
locationTheoreticalMap.put(plid, new Float(0f));
if (! productInstanceIDMap.containsKey(piid))
{
productInstanceIDMap.put(piid, new Vector<Integer>());
}
productInstanceIDMap.get(piid).add(plid);
}
}
public HashMap<Integer, Vector<Integer>> getProductInstanceIDMap()
{
return productInstanceIDMap;
}
private void getLocationTheoreticals(Integer mcid) throws SQLException
{
getStartingInventory(mcid);
getPurchases(mcid);
getSales(mcid);
getTransfers(mcid);
// getCredits(mcid);
}
private void computeLocationTheoreticals()
{
for (Integer plid : locationTheoreticalMap.keySet())
{
Float si = startingInventoryMap.containsKey(plid) ? startingInventoryMap.get(plid) : 0f;
Float p = purchasesMap.containsKey(plid) ? purchasesMap.get(plid) : 0f;
Float tt = transfersToMap.containsKey(plid) ? transfersToMap.get(plid) : 0f;
Float tf = transfersFromMap.containsKey(plid) ? transfersFromMap.get(plid) : 0f;
Float gs = glassSalesMap.containsKey(plid) ? glassSalesMap.get(plid) : 0f;
Float bs = bottleSalesMap.containsKey(plid) ? bottleSalesMap.get(plid) : 0f;
// Float cp = creditPurchaseMap.containsKey(plid) ? creditPurchaseMap.get(plid) : 0f;
// Float sec = staffEducationCreditMap.containsKey(plid) ? staffEducationCreditMap.get(plid) : 0f;
// Float ga = giveawayCreditsMap.containsKey(plid) ? giveawayCreditsMap.get(plid) : 0f;
locationTheoreticalMap.put(plid, locationTheoreticalMap.get(plid) + si);
locationTheoreticalMap.put(plid, locationTheoreticalMap.get(plid) + p);
locationTheoreticalMap.put(plid, locationTheoreticalMap.get(plid) + tt);
locationTheoreticalMap.put(plid, locationTheoreticalMap.get(plid) - tf);
locationTheoreticalMap.put(plid, locationTheoreticalMap.get(plid) - gs);
locationTheoreticalMap.put(plid, locationTheoreticalMap.get(plid) - bs);
// locationTheoreticalMap.put(plid, locationTheoreticalMap.get(plid) - cp);
// locationTheoreticalMap.put(plid, locationTheoreticalMap.get(plid) - sec);
// locationTheoreticalMap.put(plid, locationTheoreticalMap.get(plid) - ga);
}
}
private void getTransfersTo(Integer mcid) throws SQLException
{
String q = "select pilt.to_location, sum (pilt.quantity) as total from " +
"product_instance_location_associations pila, " +
"product_instances pi, products p, " +
"product_instance_location_transfers pilt where " +
"pilt.to_location = pila.product_instance_location_id " +
"and pila.product_instance_id = pi.product_instance_id " +
"and pi.product_id = p.product_id " +
"and p.major_category_id = ? " +
"and transfer_date > ? " +
"and transfer_date <= ? " +
"group by to_location";
PreparedStatement ps = conn.prepareStatement(q);
ps.setInt(1, mcid);
ps.setDate(2, beginningInventoryMap.get(mcid).inventoryDate.toSQLDate());
ps.setDate(3, endDate.toSQLDate());
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Integer plid = rs.getInt("to_location");
float total = rs.getFloat("total");
transfersToMap.put(plid, total);
}
}
private void getTransfersFrom(Integer mcid) throws SQLException
{
String q = "select pilt.from_location, sum (pilt.quantity) as total from " +
"product_instance_location_associations pila, " +
"product_instances pi, products p, " +
"product_instance_location_transfers pilt where " +
"pilt.from_location = pila.product_instance_location_id " +
"and pila.product_instance_id = pi.product_instance_id " +
"and pi.product_id = p.product_id " +
"and p.major_category_id = ? " +
"and transfer_date > ? " +
"and transfer_date <= ? " +
"group by from_location";
PreparedStatement ps = conn.prepareStatement(q);
ps.setInt(1, mcid);
ps.setDate(2, beginningInventoryMap.get(mcid).inventoryDate.toSQLDate());
ps.setDate(3, endDate.toSQLDate());
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Integer plid = rs.getInt("from_location");
float total = rs.getFloat("total");
transfersFromMap.put(plid, total);
}
}
private void getTransfers(Integer mcid) throws java.sql.SQLException
{
getTransfersTo(mcid);
getTransfersFrom(mcid);
}
// private void getCredits(Integer mcid) throws java.sql.SQLException
// {
// getStaffEducationCredits(mcid);
// getCreditPurchases(mcid);
// getGiveawayCredits(mcid);
// }
// private void getGiveawayCredits(Integer mcid) throws java.sql.SQLException
// {
//
// }
// private void getCreditPurchases(Integer mcid) throws java.sql.SQLException
// {
// String q = "select cprd.product_instance_location_id, sum (cprd.quantity) as total from " +
// "credit_purchase_record_details cprd, credit_purchase_records cpr, " +
// "product_instance_location_associations pila, " +
// "product_instances pi, products p " +
// "where " +
// "cprd.product_instance_location_id = pila.product_instance_location_id " +
// "and pila.product_instance_id = pi.product_instance_id " +
// "and pi.product_id = p.product_id " +
// "and cprd.credit_purchase_record_id = cpr.credit_purchase_record_id " +
// "and cpr.purchase_date > ? " +
// "and cpr.purchase_date <= ? " +
// "and p.major_category_id = ? " +
// "group by cprd.product_instance_location_id";
// PreparedStatement ps = conn.prepareStatement(q);
// ps.setDate(1, beginningInventoryMap.get(mcid).inventoryDate.toSQLDate());
// ps.setDate(2, endDate.toSQLDate());
// ps.setInt(3, mcid);
// ResultSet rs = ps.executeQuery();
// while (rs.next())
// {
// Integer pil = rs.getInt("product_instance_location_id");
// Float tAmount = rs.getFloat("total");
// creditPurchaseMap.put(pil, tAmount);
// }
// }
// private void getStaffEducationCredits(Integer mcid) throws java.sql.SQLException
// {
// String q = "select sec.product_instance_location_id, sum (sec.quantity) as total from " +
// "staff_education_credits sec, " +
// "product_instance_location_associations pila, " +
// "product_instances pi, products p " +
// "where " +
// "sec.product_instance_location_id = pila.product_instance_location_id " +
// "and pila.product_instance_id = pi.product_instance_id " +
// "and pi.product_id = p.product_id " +
// "and (" +
// "(sec.date_initiated > ? and expect_reimbursement = 'true' " +
// "and date_resolved is null) " +
// "or" +
// "( date_initiated > ? and date_initiated <= ? and expect_reimbursement = 'false' ) " +
// ") " +
// "and p.major_category_id = ? " +
// "group by sec.product_instance_location_id";
// PreparedStatement ps = conn.prepareStatement(q);
// ps.setDate(1, beginningInventoryMap.get(mcid).inventoryDate.toSQLDate());
// ps.setDate(2, beginningInventoryMap.get(mcid).inventoryDate.toSQLDate());
// ps.setDate(3, endDate.toSQLDate());
// ps.setInt(4, mcid);
// ResultSet rs = ps.executeQuery();
// while (rs.next())
// {
// Integer pil = rs.getInt("product_instance_location_id");
// Float tAmount = rs.getFloat("total");
// staffEducationCreditMap.put(pil, tAmount);
// }
// }
private void getSales(Integer mcid) throws java.sql.SQLException
{
oldGetBottleSales(mcid);
oldGetGlassSales(mcid);
}
private void oldGetBottleSales(int mcid) throws java.sql.SQLException
{
String q = "select pc.product_instance_location_id, sum (pc.num_sold) as total " +
"from pos_chkitems pc, " +
"product_instance_location_associations pila, " +
"product_instances pi, products p " +
"where " +
"pc.product_instance_location_id = pila.product_instance_location_id " +
"and pila.product_instance_id = pi.product_instance_id " +
"and pi.product_id = p.product_id " +
"and pc.date_of_sale > ? " +
"and pc.date_of_sale <= ? " +
"and p.major_category_id = ? " +
"and pc.is_inventory_unit = 'true' and " +
//********* changed 7/16/2011 ////////////
"pc.deletion = 0 " + // and pc.overring = 'false'" +
"group by pc.product_instance_location_id";
PreparedStatement ps = conn.prepareStatement(q);
ps.setDate(1, beginningInventoryMap.get(mcid).inventoryDate.toSQLDate());
ps.setDate(2, endDate.toSQLDate());
ps.setInt(3, mcid);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Integer plid = rs.getInt("product_instance_location_id");
Float bSales = rs.getFloat("total");
bottleSalesMap.put(plid, bSales);
}
}
private void getBottleSales(Integer mcid) throws SQLException
{
String q = "select ds.product_instance_location_id, ds.bottle_sales_amount as total " +
"from daily_sales ds, " +
"product_instance_location_associations pila, " +
"product_instances pi, products p " +
"where " +
"ds.product_instance_location_id = pila.product_instance_location_id " +
"and pila.product_instance_id = pi.product_instance_id " +
"and pi.product_id = p.product_id " +
"and ds.date_of_sale > ? " +
"and ds.date_of_sale <= ? " +
"and p.major_category_id = ? ";
PreparedStatement ps = conn.prepareStatement(q);
ps.setDate(1, beginningInventoryMap.get(mcid).inventoryDate.toSQLDate());
ps.setDate(2, endDate.toSQLDate());
ps.setInt(3, mcid);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Integer plid = rs.getInt("product_instance_location_id");
Float bSales = (float) rs.getInt("total");
bottleSalesMap.put(plid, bSales);
}
}
private void getGlassSales(Integer mcid) throws SQLException
{
String q = "select ds.product_instance_location_id,"
+ " ds.glass_sales_amount as total_sold " +
"from daily_sales ds, " +
"product_instance_location_associations pila, " +
"product_instances pi, products p " +
"where " +
"ds.product_instance_location_id = pila.product_instance_location_id " +
"and pila.product_instance_id = pi.product_instance_id " +
"and pi.product_id = p.product_id " +
"and p.major_category_id = ? " +
"and ds.date_of_sale > ? " +
"and ds.date_of_sale <= ? ";
PreparedStatement ps = conn.prepareStatement(q);
ps.setInt(1, mcid);
ps.setDate(2, beginningInventoryMap.get(mcid).inventoryDate.toSQLDate());
ps.setDate(3, endDate.toSQLDate());
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Integer plid = rs.getInt("product_instance_location_id");
float totalSold = rs.getFloat("total_sold");
if (! glassSalesMap.containsKey(plid))
glassSalesMap.put(plid, totalSold);
else
glassSalesMap.put(plid, glassSalesMap.get(plid) + totalSold);
}
}
private void oldGetGlassSales(Integer mcid) throws SQLException
{
HashMap bs = getBottleSizes();
String q = "select pc.product_instance_location_id, vu.equivalent_ml, " +
"pc.sales_units_sold, " +
"sum ( pc.num_sold ) as total_sold " +
"from pos_chkitems pc, volume_units vu, " +
"product_instance_location_associations pila, " +
"product_instances pi, products p " +
"where " +
"pc.product_instance_location_id = pila.product_instance_location_id " +
"and pila.product_instance_id = pi.product_instance_id " +
"and pi.product_id = p.product_id " +
"and pc.sales_volume_unit_id = vu.volume_unit_id " +
"and p.major_category_id = ? " +
"and pc.is_inventory_unit = 'false' " +
"and date_of_sale > ? " +
"and date_of_sale <= ? " +
//********* added 7/16/2011 ////////////
"and pc.deletion = 0" +
/////////////////////////////////////
"group by pc.product_instance_location_id, vu.equivalent_ml, pc.sales_units_sold";
PreparedStatement ps = conn.prepareStatement(q);
ps.setInt(1, mcid);
ps.setDate(2, beginningInventoryMap.get(mcid).inventoryDate.toSQLDate());
ps.setDate(3, endDate.toSQLDate());
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Integer plid = rs.getInt("product_instance_location_id");
float eqMl = rs.getFloat("equivalent_ml");
float sus = rs.getFloat("sales_units_sold");
float totalSold = rs.getFloat("total_sold");
float bottleMl = Float.parseFloat(bs.get(plid).toString());
Float gSales = (eqMl * sus * totalSold) / bottleMl;
if (! glassSalesMap.containsKey(plid))
glassSalesMap.put(plid, gSales);
else
glassSalesMap.put(plid, glassSalesMap.get(plid) + gSales);
}
}
private void getPurchases(Integer mcid) throws java.sql.SQLException
{
String q = "select id.product_instance_location_id, sum (id.number_received) as total " +
"from invoice_details id, invoices i, " +
"product_instance_location_associations pila, " +
"product_instances pi, products p " +
"where id.product_instance_location_id = " +
"pila.product_instance_location_id " +
"and pila.product_instance_id = pi.product_instance_id " +
"and pi.product_id = p.product_id " +
"and i.invoice_id = id.invoice_id and " +
"i.payment_date > ?" +
"and i.payment_date <= ? " +
"and p.major_category_id = ?" +
"group by id.product_instance_location_id";
PreparedStatement ps = conn.prepareStatement(q);
ps.setDate(1, beginningInventoryMap.get(mcid).inventoryDate.toSQLDate());
ps.setDate(2, endDate.toSQLDate());
ps.setInt(3, mcid);
ResultSet rs = ps.executeQuery();
while(rs.next())
{
Integer plid = rs.getInt("product_instance_location_id");
Float purch = rs.getFloat("total");
purchasesMap.put(plid, purch);
}
}
private void getStartingInventory(Integer mcid) throws java.sql.SQLException
{
String q = "select product_instance_location_id, quantity_counted from " +
"inventory_details id " +
// "product_instances pi, products p " +
// "and id.inventory_id = ?";
"where id.inventory_id = ?";
PreparedStatement ps = conn.prepareStatement(q);
ps.setInt(1, beginningInventoryMap.get(mcid).inventoryID );
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Integer plid = rs.getInt("product_instance_location_id");
Float qc = rs.getFloat("quantity_counted");
startingInventoryMap.put(plid, qc);
}
}
private HashMap getBottleSizes() throws java.sql.SQLException
{
HashMap hm = new HashMap();
String q = "select pc.product_instance_location_id, " +
"vu.equivalent_ml * bs.bottle_size as bottle_ml " +
"from pos_chkitems pc, product_instance_location_associations pil, " +
"product_instances pi, bottle_sizes bs, volume_units vu " +
"where pc.product_instance_location_id = pil.product_instance_location_id " +
"and pil.product_instance_id = pi.product_instance_id " +
"and pi.bottle_size_id = bs.bottle_size_id " +
"and bs.bottle_size_unit = vu.volume_unit_id ";
PreparedStatement ps = conn.prepareStatement(q);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
hm.put(rs.getInt("product_instance_location_id") , rs.getFloat("bottle_ml"));
}
return hm;
}
private class Inventory
{
public Integer inventoryID;
public SimpleDate inventoryDate;
public Inventory(Integer iid, SimpleDate id)
{
inventoryID = iid;
inventoryDate = id;
}
}
}