package org.paneris.jammyjoes.controller;

 import java.sql.Date;

 import java.util.Collections;

 import java.util.Enumeration;

 import java.util.LinkedList;

 import java.util.List;

 import java.util.Vector;

 import org.melati.Melati;

 import org.melati.poem.AccessPoemException;

 import org.melati.poem.AccessToken;

 import org.melati.poem.BaseFieldAttributes;

 import org.melati.poem.Capability;

 import org.melati.poem.Field;

 import org.melati.poem.PoemThread;

 import org.melati.servlet.Form;

 import org.melati.template.ServletTemplateContext;

 import org.melati.template.SimpleDateAdaptor;

 import org.melati.util.ContextUtil;

 import org.melati.poem.util.EnumUtils;

 import org.paneris.jammyjoes.model.JammyjoesDatabase;

 import org.paneris.jammyjoes.model.Product;

 import org.paneris.jammyjoes.model.Product.StockValues;

 import org.paneris.jammyjoes.servlet.JammyJoesMelatiServlet;

 public class ProductReport extends JammyJoesMelatiServlet {

   private static final long serialVersionUID = 1L;

   private ProductSearch util;

   protected String jammyjoesRequest(Melati melati, ServletTemplateContext context) throws Exception {

     util = new ProductSearch(null,0, new Logger(null));

     JammyjoesDatabase db = (JammyjoesDatabase) melati.getDatabase();

     ServletTemplateContext tc = melati.getServletTemplateContext();

     Capability admin = db.getCanAdminister();

     AccessToken token = PoemThread.accessToken();

     if (!token.givesCapability(admin))

       throw new AccessPoemException(token, admin);

     String template = "view/StockReview.wm";

     int option = 0;

     Integer optionInteger = Form.getIntegerField(tc, "option");

     SimpleDateAdaptor sd = new SimpleDateAdaptor();

     Date date = (Date) sd.rawFrom(tc, "date");

     context.put("date", date);

     if (optionInteger != null)

       option = optionInteger.intValue();

     context.put("option", new Integer(option));

     Integer type = Form.getIntegerField(tc, "field_type");

     Integer supplier = Form.getIntegerField(tc, "field_supplier");

     Integer manufacturer = Form.getIntegerField(tc, "field_manufacturer");

     context.put(

       "type",

       new Field(

         type,

         new BaseFieldAttributes(db.getProductTable().getTypeColumn(), true)));

     context.put(

       "supplier",

       new Field(

         supplier,

         new BaseFieldAttributes(db.getProductTable().getSupplierColumn(), true)));

     context.put(

       "manufacturer",

       new Field(

         manufacturer,

         new BaseFieldAttributes(db.getProductTable().getManufacturerColumn(),

           true)));

     if (Form.getFormNulled(tc, "submit") != null) {

       Vector where = new Vector();

       List results = new LinkedList();

       if (type != null)

         where.add(q("type") + " = " + type);

       if (supplier != null)

         where.add(q("supplier") + " = " + supplier);

       if (manufacturer != null)

         where.add(q("manufacturer") + " = " + manufacturer);

       if (option == 1) {

         template = "view/AnalyzePrices.wm";

         where.add(discontinuedNoStock(db));

       }

       String orderbyClause = null;

       if (option == 4) {

         template = "view/StockReview.wm";

         where.add(discontinued(db));

         where.add(q("stocklevel") + " <= " + q("reorderlevel"));

       }

       if (option == 5) {

         template = "view/ReconciliationReport.wm";

         where.add(discontinuedNoStock(db));

       }

       if (option == 6) {

         template = "view/StockValuationReport.wm";

       }

       String whereClause = EnumUtils.concatenated(" AND ", where.elements());

       Enumeration r = db.getProductTable().selection(whereClause, orderbyClause, false);

       double totalStock = 0;

       double totalSold = 0;

       double totalMargin = 0;

       double averageMargin = 0;

       double averageMarkup = 0;

       double totalCostPrice = 0;

       double totalCostPriceIncVat = 0;

       double totalRetailPrice = 0;

       double totalRetailPriceIncVat = 0;

       while (r.hasMoreElements()) {

         Product p = (Product) r.nextElement();

         if (option == 1) {

           results.add(p);

           if (p.getMargin() != null)

             totalMargin += p.getMargin().doubleValue();

           totalCostPrice += p.getCostprice().doubleValue();

           totalCostPriceIncVat += p.getCostpriceIncVat().doubleValue();

           totalRetailPrice += p.getRetailprice().doubleValue();

           if (p.getRetailpriceincvat() != null)

             totalRetailPriceIncVat += p.getRetailpriceincvat().doubleValue();

           if (p.getMarginpercent() != null)

             averageMargin += p.getMarginpercent().doubleValue();

           if (p.getMarkuppercent() != null)

             averageMarkup += p.getMarkuppercent().doubleValue();

         }

         if (option == 4 || option == 5) {

           results.add(p);

         }

         if (option == 6) {

           StockValues stockValues = p.getStockLevels(date);

           if (stockValues.getLevel() != 0) {

             results.add(p);

             totalCostPrice += stockValues.getCostPriceExVat();

             totalCostPriceIncVat += stockValues.getCostPriceIncVat();

             totalRetailPrice += stockValues.getRetailPriceExVat();

             totalRetailPriceIncVat += stockValues.getRetailPriceIncVat();

           }

         }

       }

       double count = results.size();

       if (option == 1) {

         context.put("totalMargin", new Double(totalMargin));

         context.put("totalCostPrice", new Double(totalCostPrice));

         context.put("totalRetailPrice", new Double(totalRetailPrice));

         context.put("totalRetailPriceIncVat", new Double(totalRetailPriceIncVat));

         context.put("averageMargin", new Double(totalMargin / count));

         context.put("averageCostPrice", new Double(totalCostPrice / count));

         context.put("averageCostPriceIncVat", new Double(totalCostPriceIncVat / count));

         context.put("averageRetailPrice", new Double(totalRetailPrice / count));

         context.put("averageRetailPriceIncVat", new Double(totalRetailPriceIncVat / count));

         context.put("averageMarginPercent", new Double(averageMargin / count));

         context.put("averageMarkupPercent", new Double(averageMarkup / count));

       }

       if (option == 4 || option == 5 || option == 6) {

         context.put("totalMargin", new Double(totalMargin));

         context.put("totalCostPrice", new Double(totalCostPrice));

         context.put("totalCostPriceIncVat", new Double(totalCostPriceIncVat));

         context.put("totalRetailPrice", new Double(totalRetailPrice));

         context.put("totalRetailPriceIncVat", new Double(totalRetailPriceIncVat));

         context.put("averageMargin", new Double(totalMargin / totalStock));

         context.put("averageCostPrice", new Double(totalCostPrice / totalStock));

         context.put("averageRetailPrice", new Double(totalRetailPrice / totalStock));

         context.put("averageRetailPriceIncVat", new Double(totalRetailPriceIncVat / totalStock));

         context.put(

           "averageMarginPercent",

           new Double((totalRetailPrice - totalCostPrice) / totalRetailPrice * 100));

         context.put(

           "averageMarkupPercent",

           new Double((totalRetailPrice - totalCostPrice) / totalCostPrice * 100));

       }

       if (option == 4)

         Collections.sort(results, new SupplierNameSort());

       context.put("count", new Double(count));

       context.put("averageStock", new Integer(new Double((totalStock / count)).intValue()));

       context.put("totalStock", new Double(totalStock));

       context.put("totalSold", new Double(totalSold));

       context.put("results", results);

       context.put("util", new ContextUtil());

     }

     return template;

   }

   private String discontinuedNoStock(JammyjoesDatabase db) {

     String discontinued = "(" + discontinued(db);

     discontinued += " OR (" + q("stocklevel") + " != 0 AND " + q("stocklevel") + " != NULL))";

     return discontinued;

   }

   private String discontinued(JammyjoesDatabase db) {

     String discontinued =

       "(" + q("status") + " != " + db.getProductStatusTable().getDiscontinued().getTroid();

     discontinued += " AND "

       + q("status")

       + " != "

       + db.getProductStatusTable().getNotStocked().getTroid()

       + ")";

     return discontinued;

   }

   public String q(String name) {

     return util.q(name);

   }

 }