package com.ampiere.web.struts.form;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParsePosition;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Properties;
import java.util.logging.Level;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.compiere.model.MForm;
import org.compiere.model.MLookupFactory;
import org.compiere.model.MLookupInfo;
import org.compiere.model.MRole;
import org.compiere.util.CLogger;
import org.compiere.util.Ctx;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Language;
import org.compiere.util.Msg;
import org.compiere.util.WebSessionCtx;

import com.ampiere.util.Constants;

/**
 * @author siqinbilige
 */
public class PaymentSelection extends Action {

    /** Logger. */
    private CLogger log = CLogger.getCLogger(this.getClass());
    
    /** Order List Forward. */
    private static final String PAYMENT_LIST = "paymentlist";

    /** Action Form. */
    private static final String ACTION_FORM = "PaymentSelectionForm";

    /** Global Error Forward. */
    private static final String ERROR_FORWARD = "error";

    /** Form ID. */
    private static final int FORM_ID = 107;

    /**
     * Action execute.
     * @param mapping mapping
     * @param actionForm actionForm
     * @param request request
     * @param response response
     * @throws Exception Exception
     * @return ActionForward
     * @see org.apache.struts.action.Action#execute(
     *      org.apache.struts.action.ActionMapping,
     *      org.apache.struts.action.ActionForm,
     *      javax.servlet.http.HttpServletRequest,
     *      javax.servlet.http.HttpServletResponse)
     */
    public final ActionForward execute(
            final ActionMapping mapping,
            final ActionForm actionForm,
            final HttpServletRequest request,
            final HttpServletResponse response)
    throws Exception {
    	
		//201001 
        WebSessionCtx wscTest = WebSessionCtx.get(request); 
        if (wscTest == null) {
            log.log(Level.SEVERE, "Session Time Out.");
            request.setAttribute(Constants.SESSION_TIMEOUT_INFO, "Session Time Out. Please login again.");
            return mapping.findForward(Constants.SESSION_TIMEOUT);
        }
        //--201001    	

        log.fine("Begin " + this.getClass().getName() + ";execute");

        WebSessionCtx wsc = WebSessionCtx.get(request); 
        if (wsc == null) {
            log.log(Level.SEVERE, "Session Time Out.");
            return mapping.findForward(ERROR_FORWARD);
        }

//        Env.setContext(wsc.ctx, 0, "IsSOTrx", "Y");
        wsc.ctx.setContext(0, "IsSOTrx", "Y");

        MForm form = getForm(wsc.ctx, FORM_ID);
//        Env.setContext(wsc.ctx, "FormName", form.getName());
        wsc.ctx.setContext("FormName", form.getName());

        PaymentSelectionForm myForm =
            (PaymentSelectionForm) request.getAttribute(ACTION_FORM);
        if (myForm == null) {
            myForm = (PaymentSelectionForm) actionForm;
            if (myForm == null) {
                myForm = new PaymentSelectionForm();
            }
        }
        
        // Main Window Title
        myForm.setMainWindowTitle(form.getName() + " - " + wsc.loginInfo);

        // Page title.
        myForm.setPageTitle(form.getName());

        // Bank Account Label
        myForm.setBankAccountLabel(Msg.translate(wsc.ctx, "C_BankAccount_ID"));

        // Current Balance Label
        myForm.setCurrentBalanceLabel(Msg.translate(wsc.ctx, "CurrentBalance"));

        // Bank Account Selection Data
        ArrayList < HashMap > dataList = new ArrayList < HashMap > ();
        HashMap < String, Object > data;

        //  Bank Account Info
        String sql = MRole.getDefault().addAccessSQL(
            "SELECT ba.C_BankAccount_ID,"                       //  1
            + "b.Name || ' ' || ba.AccountNo AS Name,"          //  2
            + "ba.C_Currency_ID, c.ISO_Code,"                   //  3..4
            + "ba.CurrentBalance "                              //  5
            + "FROM C_Bank b, C_BankAccount ba, C_Currency c "
            + "WHERE b.C_Bank_ID=ba.C_Bank_ID"
            + " AND ba.C_Currency_ID=c.C_Currency_ID "
            + " AND EXISTS (SELECT * FROM C_BankAccountDoc d WHERE d.C_BankAccount_ID=ba.C_BankAccount_ID) "
            + "ORDER BY 2",
            "b", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RW);
        try {
            PreparedStatement pstmt = DB.prepareStatement(sql, null);
            ResultSet rs = pstmt.executeQuery();
            int count = 0;
            while (rs.next()) {
                data = new HashMap < String, Object > ();
                String value = rs.getString(1) + "|" + rs.getString(3) + "|" + rs.getString(4) + "|" +rs.getString(5);
                data.put("key", value);
                data.put("value", rs.getString(2));
                dataList.add(data);
                if (count == 0 && myForm.getBankAccount() == null) {
                    myForm.setBankAccount(value);
                    myForm.setCurrentBalance(wsc.amountFormat.format(rs.getBigDecimal(5)));
                    myForm.setCurrentCurrency(rs.getString(4));
                }
                count++;
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            log.log(Level.SEVERE, sql, e);
        }
        if (myForm.getBankAccount() != null) {
            String [] bankInfo = myForm.getBankAccount().split("\\|");
            myForm.setCurrentBalance(wsc.amountFormat.format(new BigDecimal(bankInfo[3])));
            myForm.setCurrentCurrency(bankInfo[2]);
        }

        myForm.setBankAccountList(dataList);
        if (dataList.size() == 0) {
            myForm.setResultInfo(Msg.translate(wsc.ctx, "VPaySelectNoBank"));
        }

        // Business Partner Label
        myForm.setBusinessPartnerLabel(Msg.translate(wsc.ctx, "C_BPartner_ID"));
        
        //  Optional BusinessPartner with unpaid AP Invoices
        dataList = new ArrayList < HashMap > ();
        data = new HashMap < String, Object > ();
        data.put("0", "");
        dataList.add(data);

        sql = MRole.getDefault().addAccessSQL(
            "SELECT bp.C_BPartner_ID, bp.Name FROM C_BPartner bp", "bp", 
            MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO)
            + " AND EXISTS (SELECT * FROM C_Invoice i WHERE bp.C_BPartner_ID=i.C_BPartner_ID"
              + " AND i.IsSOTrx='N' AND i.IsPaid<>'Y') "
            + "ORDER BY 2";
        try {
            PreparedStatement pstmt = DB.prepareStatement(sql, null);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                data = new HashMap < String, Object > ();
                data.put("key", rs.getInt(1));
                data.put("value", rs.getString(2));
                dataList.add(data);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            log.log(Level.SEVERE, sql, e);
        }
        myForm.setBusinessPartnerList(dataList);

        // Only Due Invoices
        myForm.setOnlyDueInvoicesLabel(Msg.getMsg(wsc.ctx, "OnlyDue"));
        if (myForm.getOnlyDueInvoices() == null) {
            myForm.setOnlyDueInvoices("off");
        }

        // Payment Date
        myForm.setPaymentDateLabel(Msg.translate(wsc.ctx, "PayDate"));
        if (myForm.getPaymentDate() == null) {
//            myForm.setPaymentDate(wsc.dateFormat.format(Env.getContextAsDate(wsc.ctx, "Date")));
            myForm.setPaymentDate(wsc.dateFormat.format(wsc.ctx.getContextAsTime("Date")));
        }

        // Payment Rule
        myForm.setPaymentRuleLabel(Msg.translate(wsc.ctx, "PaymentRule"));
        dataList = new ArrayList < HashMap > ();

        int referenceId = 195;  //  MLookupInfo.getAD_Reference_ID("All_Payment Rule");
        Language language = Env.getLanguage(wsc.ctx);
        MLookupInfo info = MLookupFactory.getLookup_List(language, referenceId);
        sql = info.Query.substring(0, info.Query.indexOf(" ORDER BY"))
            + " AND " + info.KeyColumn
            + " IN (SELECT PaymentRule FROM C_BankAccountDoc WHERE C_BankAccount_ID=?) "
            + info.Query.substring(info.Query.indexOf(" ORDER BY"));
        String [] bankInfo = myForm.getBankAccount().split("\\|");
        try {
            PreparedStatement pstmt = DB.prepareStatement(sql, null);
            pstmt.setInt(1, Integer.valueOf(bankInfo[0]));
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                data = new HashMap < String, Object > ();
                data.put("key", rs.getString(2));
                data.put("value", rs.getString(3));
                dataList.add(data);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            log.log(Level.SEVERE, sql, e);
        }
        myForm.setPaymentRuleList(dataList);

        // Invoice List
        myForm.setInvoiceList(getInvoiceList(wsc, myForm));
        
        // Save form to request.
        request.setAttribute(ACTION_FORM, myForm);

        log.fine("End " + this.getClass().getName() + ";execute");

        return mapping.findForward(PAYMENT_LIST);

    }
    
    /**
     *  Get Form from Form ID.
     *  @param ctx Properties
     *  @param formId Form ID
     *  @return form
     */
    private MForm getForm(final Ctx ctx, final int formId) {

        // Get Form from Form ID
        MForm form = new MForm(ctx, formId, null);
        
        boolean trl = !Env.isBaseLanguage(ctx, "AD_Form");
        if (trl) {
            String sql = "SELECT t.Name, t.Description, t.Help "
                       + "FROM AD_Form f INNER JOIN AD_Form_Trl t"
                       + " ON (f.AD_Form_ID=t.AD_Form_ID AND AD_Language=?)"
                       + "WHERE f.AD_Form_ID=?";
            try {
                PreparedStatement pstmt = DB.prepareStatement(sql, null);
                if (trl) {
                    pstmt.setString(1, Env.getAD_Language(ctx));
                    pstmt.setInt(2, formId);

                    ResultSet rs = pstmt.executeQuery();
                    if (rs.next()) {
                        form.setName(rs.getString(1));
                        form.setDescription(rs.getString(2));
                        form.setHelp(rs.getString(3));
                    }
                    rs.close();
                    pstmt.close();
                }
            } catch (SQLException e) {
                log.log(Level.SEVERE, sql, e);
            }
        }

        return form;
    }   //  getForm

    /**
     * Get Invoice List.
     * @param wsc Context
     * @param form Action form
     * @return Invoice List
     */
    private ArrayList getInvoiceList(final WebSessionCtx wsc, final PaymentSelectionForm form) {
        ArrayList < HashMap > dataList = new ArrayList < HashMap > ();
        HashMap < String, Object > data = new HashMap < String, Object > ();

        data.put("RowType", "title");
        data.put("C_Invoice_ID", "");
        data.put("DateDue", Msg.translate(wsc.ctx, "DateDue"));
        data.put("BPartner", Msg.translate(wsc.ctx, "C_BPartner_ID"));
        data.put("DocumentNo", Msg.translate(wsc.ctx, "DocumentNo"));
        data.put("Currency", Msg.translate(wsc.ctx, "C_Currency_ID"));
        data.put("GrandTotal", Msg.translate(wsc.ctx, "GrandTotal"));
        data.put("DiscountAmt", Msg.translate(wsc.ctx, "DiscountAmt"));
        data.put("DiscountDate", Msg.getMsg(wsc.ctx, "DiscountDate"));
        data.put("AmountDue", Msg.getMsg(wsc.ctx, "AmountDue"));
        data.put("AmountPay", Msg.getMsg(wsc.ctx, "AmountPay"));
        dataList.add(data);
        
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT  i.C_Invoice_ID,");
        sql.append("        i.DateInvoiced+p.NetDays AS DateDue,");
        sql.append("        bp.Name,");
        sql.append("        i.C_BPartner_ID,");
        sql.append("        i.DocumentNo,");
        sql.append("        c.ISO_Code,");
        sql.append("        i.C_Currency_ID,");
        sql.append("        i.GrandTotal,");
        sql.append("        paymentTermDiscount(i.GrandTotal,i.C_Currency_ID,i.C_PaymentTerm_ID,i.DateInvoiced, ?),");
        sql.append("        SysDate-paymentTermDueDays(i.C_PaymentTerm_ID,i.DateInvoiced,SysDate),");
        sql.append("        currencyConvert(invoiceOpen(i.C_Invoice_ID,i.C_InvoicePaySchedule_ID),i.C_Currency_ID, ?,?,i.C_ConversionType_ID, i.AD_Client_ID,i.AD_Org_ID),");
        sql.append("        currencyConvert(invoiceOpen(i.C_Invoice_ID,i.C_InvoicePaySchedule_ID)-paymentTermDiscount(i.GrandTotal,i.C_Currency_ID,i.C_PaymentTerm_ID,i.DateInvoiced, ?),i.C_Currency_ID, ?,?,i.C_ConversionType_ID, i.AD_Client_ID,i.AD_Org_ID)");
        sql.append("FROM C_Invoice_v i");
        sql.append(" INNER JOIN C_BPartner bp ON (i.C_BPartner_ID=bp.C_BPartner_ID)");
        sql.append(" INNER JOIN C_Currency c ON (i.C_Currency_ID=c.C_Currency_ID)");
        sql.append(" INNER JOIN C_PaymentTerm p ON (i.C_PaymentTerm_ID=p.C_PaymentTerm_ID)");
        sql.append("WHERE i.IsSOTrx='N'");
        sql.append("  AND IsPaid='N'");
        sql.append("  AND NOT EXISTS (  SELECT *");
        sql.append("                    FROM C_PaySelectionLine psl");
        sql.append("                    WHERE i.C_Invoice_ID=psl.C_Invoice_ID");
        sql.append("                      AND psl.C_PaySelectionCheck_ID IS NOT NULL)");
        sql.append("  AND i.DocStatus IN ('CO','CL')");
        sql.append("  AND i.AD_Client_ID=?");
        if (form.getOnlyDueInvoices().equals("on")) {
            sql.append("  AND i.DateInvoiced+p.NetDays <= ?");
        }
        if (form.getBusinessPartner() > 0) {
            sql.append("  AND i.C_BPartner_ID=?");
        }
        sql.append("ORDER BY 2,3");

        Date payDate = wsc.dateFormat.parse(form.getPaymentDate(), new ParsePosition(0));
        String [] bankInfo = form.getBankAccount().split("\\|");
        int currencyId = Integer.valueOf(bankInfo[1]);
        int businessPartner = form.getBusinessPartner();
//        int clientId = Env.getAD_Client_ID(wsc.ctx);
        int clientId = wsc.ctx.getAD_Client_ID();
        //  Get Open Invoices
        try {
            int index = 1;
            PreparedStatement pstmt = DB.prepareStatement(sql.toString(), null);
            pstmt.setTimestamp(index++, new Timestamp(payDate.getTime()));       //  DiscountAmt
            pstmt.setInt(index++, currencyId);    //  DueAmt
            pstmt.setTimestamp(index++, new Timestamp(payDate.getTime()));       
            pstmt.setTimestamp(index++, new Timestamp(payDate.getTime()));       //  PayAmt
            pstmt.setInt(index++, currencyId);
            pstmt.setTimestamp(index++, new Timestamp(payDate.getTime()));
            pstmt.setInt(index++, clientId);

            if (form.getOnlyDueInvoices().equals("on")) {
                pstmt.setTimestamp(index++, new Timestamp(payDate.getTime()));
            }

            if (businessPartner > 0) {
                pstmt.setInt(index++, businessPartner);
            }

            ResultSet rs = pstmt.executeQuery();
            int count = 0;
            while (rs.next()) {
                count++;
                data = new HashMap < String, Object > ();
                // Row Type
                if ((count % 2) == 0) {
                    data.put("RowType", "even");
                } else {
                    data.put("RowType", "odd");
                }
                String invoiceId = rs.getString(1) + "|" + rs.getString(11) + "|" + rs.getString(12);
                data.put("C_Invoice_ID", invoiceId);
                data.put("DateDue", wsc.dateFormat.format(rs.getDate(2)));
                data.put("BPartner", rs.getString(3));
                data.put("DocumentNo", rs.getString(5));
                data.put("Currency", rs.getString(6));
                data.put("GrandTotal", wsc.amountFormat.format(rs.getBigDecimal(8)));
                data.put("DiscountAmt", wsc.amountFormat.format(rs.getBigDecimal(9)));
                data.put("DiscountDate", wsc.dateFormat.format(rs.getDate(10)));
                data.put("AmountDue", wsc.amountFormat.format(rs.getBigDecimal(11)));
                data.put("AmountPay", wsc.amountFormat.format(rs.getBigDecimal(12)));
                dataList.add(data);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            log.log(Level.SEVERE, sql.toString(), e);
        }
        
        // calculateSelection();

        return dataList;
    }
}
