package org.paneris.jammyjoes.controller;

 import java.sql.Date;

 import java.util.Enumeration;

 import java.util.HashMap;

 import java.util.Iterator;

 import java.util.LinkedList;

 import java.util.List;

 import java.util.Map;

 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.poem.util.EnumUtils;

 import org.melati.servlet.Form;

 import org.melati.template.ServletTemplateContext;

 import org.melati.template.SimpleDateAdaptor;

 import org.paneris.jammyjoes.model.JammyjoesDatabase;

 import org.paneris.jammyjoes.model.Product;

 import org.paneris.jammyjoes.model.StockTransaction;

 import org.paneris.jammyjoes.model.StockTransactionTypeTable;

 import org.paneris.jammyjoes.mvp.CollectionSorter;

 import org.paneris.jammyjoes.servlet.JammyJoesMelatiServlet;

 import org.paneris.jammyjoes.util.JammyJoesUtil;

 import org.webmacro.servlet.WebContext;

 public class SalesAnalysis extends JammyJoesMelatiServlet {

   private static final long serialVersionUID = 1L;

     public class StockTransactionSummary implements Comparable {

       private Product product;

       private StockTransactionTypeTable table;

       private int internet = 0;

       private int telephone = 0;

       private int shop = 0;

       private double purchases = 0D;

       private double sales = 0D;

       private double vatSales = 0D;

       private double vatPurchases = 0D;

       public double getPurchases() {

         return purchases;

       }

       public int getInternet() {

         return internet;

       }

       public Product getProduct() {

         return product;

       }

       public double getSales() {

         return sales;

       }

       public int getShop() {

         return shop;

       }

       public int getQuantity() {

         return shop + internet + telephone;

       }

       public int getTelephone() {

         return telephone;

       }

       public double getVatSales() {

         return vatSales;

       }

       public double getVatPurchases() {

         return vatPurchases;

       }

       public double getVatToPay() {

         return vatSales - vatPurchases;

       }

       public StockTransactionSummary(StockTransactionTypeTable table) {

         this.table = table;

       }

       public void add(StockTransaction transaction) {

         this.product = transaction.getProduct();

         int quantity = transaction.getQuantity().intValue();

         if (transaction.getType().equals(table.getInternetSale())) {

           internet += quantity;

         }

         if (transaction.getType().equals(table.getTelephoneSale())) {

           telephone += quantity;

         }

         if (transaction.getType().equals(table.getShopSale())) {

           shop += quantity;

         }

         double thisSales = transaction.getRetailpriceincvat().doubleValue() * quantity;

         double thisPurchases = transaction.getCostprice().doubleValue() * quantity;

         if (!transaction.getVatexempt().booleanValue()) {

           vatSales += thisSales / 1.175 * 0.175;

           thisPurchases = thisPurchases * 1.175;

           vatPurchases += thisPurchases / 1.175 * 0.175;

         }

         purchases += thisPurchases;

         sales += thisSales;

       }

       public int compareTo(Object o) {

         StockTransactionSummary other = (StockTransactionSummary)o;

         return other.getProfitDouble().compareTo(this.getProfitDouble());

       }

       public double getProfit() {

         return sales - vatSales - purchases +vatPurchases;

       }

       public double getMargin() {

         return (getProfit() / sales) * 100;

       }

       public Double getProfitDouble() {

         return new Double(getProfit());

       }

     }

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

     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/SalesAnalysis.wm";

     SimpleDateAdaptor sd = new SimpleDateAdaptor();

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

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

     context.put("startdate", startdate);

     context.put("enddate", enddate);

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

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

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

     context.put(

       "transactionType",

       new Field(

     transactionType,

         new BaseFieldAttributes(db.getStockTransactionTable().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)));

     StockTransactionTypeTable sttt = db.getStockTransactionTypeTable();

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

       Vector where = new Vector();

       where.add("product = product.id");

       if (transactionType == null) {

         String typeClause = "(type = " + sttt.getInternetSale().getTroid() + " OR ";

         typeClause += "type = " + sttt.getTelephoneSale().getTroid() + " OR ";

         typeClause += "type = " + sttt.getShopSale().getTroid() + ")";

         where.add(typeClause);

       } else {

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

       }

       if (startdate != null) {

         where.add("date >= " +  JammyJoesUtil.formatDateForSQL(db, startdate));

       }

       if (enddate != null) {

         where.add("date < " +  JammyJoesUtil.formatDateForSQL(db, enddate));

       }

       if (supplier != null)

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

       if (manufacturer != null)

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

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

       Enumeration r = db.getStockTransactionTable().selection(whereClause, "product.name", false);

       Map products = new HashMap();

       while (r.hasMoreElements()) {

         StockTransaction transaction = (StockTransaction) r.nextElement();

         Integer id = transaction.getProduct().getTroid();

         StockTransactionSummary summary = (StockTransactionSummary)products.get(id);

         if (summary == null) {

           summary = new StockTransactionSummary(db.getStockTransactionTypeTable());

         }

         summary.add(transaction);

         products.put(id, summary);

       }

       int internetTotal = 0;

       int telephoneTotal = 0;

       int shopTotal = 0;

       int stockTotal = 0;

       double purchasesTotal = 0D;

       double salesTotal = 0D;

       double salesVatTotal = 0D;

       double purchasesVatTotal = 0D;

       List results = new LinkedList();

       Iterator iter = new CollectionSorter().sort(products).iterator();

       while (iter.hasNext()) {

         StockTransactionSummary summary = (StockTransactionSummary) iter.next();

         results.add(summary);

         internetTotal += summary.getInternet();

         telephoneTotal += summary.getTelephone();

         shopTotal += summary.getShop();

         purchasesTotal += summary.getPurchases();

         salesTotal += summary.getSales();

         salesVatTotal += summary.getVatSales();

         purchasesVatTotal += summary.getVatPurchases();

         stockTotal += summary.getProduct().getStocklevel().intValue();

       }

       double profitTotal = salesTotal;

       profitTotal -= salesVatTotal;

       profitTotal -= purchasesTotal;

       profitTotal += purchasesVatTotal;

       double marginTotal = (profitTotal / salesTotal) * 100;

       double quantityTotal = internetTotal + telephoneTotal + shopTotal;

       WebContext webContext = (WebContext)context.getContext();

       webContext.put("internetTotal", internetTotal);

       webContext.put("telephoneTotal", telephoneTotal);

       webContext.put("quantityTotal", quantityTotal);

       webContext.put("stockTotal", stockTotal);

       webContext.put("shopTotal", shopTotal);

       webContext.put("purchasesTotal", purchasesTotal);

       webContext.put("salesTotal", salesTotal);

       webContext.put("profitTotal", profitTotal);

       webContext.put("marginTotal", marginTotal);

       webContext.put("salesVatTotal", salesVatTotal);

       webContext.put("purchasesVatTotal", purchasesVatTotal);

       webContext.put("vatToPayTotal", salesVatTotal - purchasesVatTotal);

       webContext.put("results", results);

     }

     return template;

   }

 }