Dépôt officiel du code source de l'ERP OpenConcerto

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 * 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
 * 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
 * language governing permissions and limitations under the License.
 * 
 * When distributing the software, include this License Header Notice in each file.
 */
 
 package org.openconcerto.erp.core.sales.pos.model;

import org.openconcerto.erp.core.sales.pos.POSConfiguration;
import org.openconcerto.erp.core.sales.pos.element.CaisseJournalSQLElement;
import org.openconcerto.erp.core.sales.pos.element.CaisseTicketSQLElement;
import org.openconcerto.erp.core.sales.pos.element.ClôtureCaisseSQLElement;
import org.openconcerto.erp.core.sales.pos.element.TicketCaisseSQLElement;
import org.openconcerto.erp.core.sales.pos.model.RegisterLog.EventType;
import org.openconcerto.erp.core.sales.pos.model.RegisterLogEntry.ReceiptEntry;
import org.openconcerto.erp.core.sales.pos.model.RegisterLogEntry.RegisterEntry;
import org.openconcerto.erp.core.sales.pos.model.RegisterState.Status;
import org.openconcerto.sql.element.SQLElementDirectory;
import org.openconcerto.sql.model.Order;
import org.openconcerto.sql.model.SQLRow;
import org.openconcerto.sql.model.SQLRowListRSH;
import org.openconcerto.sql.model.SQLRowValues;
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
import org.openconcerto.sql.model.SQLSelect;
import org.openconcerto.sql.model.SQLSelect.LockStrength;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.sql.model.graph.Path;
import org.openconcerto.sql.model.graph.PathBuilder;
import org.openconcerto.sql.utils.SQLUtils;
import org.openconcerto.sql.utils.SQLUtils.SQLFactory;
import org.openconcerto.utils.CollectionUtils;
import org.openconcerto.utils.CompareUtils;
import org.openconcerto.utils.ProductInfo;
import org.openconcerto.utils.TimeUtils;
import org.openconcerto.utils.cc.ITransformer;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;

public class RegisterDB {

    private final ProductInfo productInfo;
    private final CaisseTicketSQLElement registerElem;
    private final TicketCaisseSQLElement receiptElem;
    private final CaisseJournalSQLElement logElem;
    private final ClôtureCaisseSQLElement closureElem;
    private final int posID;

    public RegisterDB(final SQLElementDirectory dir, final ProductInfo productInfo, final int caisse) {
        super();
        this.productInfo = productInfo;
        this.registerElem = dir.getElement(CaisseTicketSQLElement.class);
        this.receiptElem = dir.getElement(TicketCaisseSQLElement.class);
        this.logElem = dir.getElement(CaisseJournalSQLElement.class);
        this.closureElem = dir.getElement(ClôtureCaisseSQLElement.class);
        this.posID = caisse;
    }

    public final SQLTable getRegisterTable() {
        return this.registerElem.getTable();
    }

    public final TicketCaisseSQLElement getReceiptElement() {
        return this.receiptElem;
    }

    public final CaisseJournalSQLElement getLogElement() {
        return this.logElem;
    }

    public final ClôtureCaisseSQLElement getClosureElement() {
        return this.closureElem;
    }

    public final int getPosID() {
        return this.posID;
    }

    protected final Path getRegisterToLastClosureEntry() {
        return new PathBuilder(getRegisterTable()).addForeignField("ID_DERNIERE_CLOTURE").addForeignField("ID_ENTREE_JOURNAL").build();
    }

    public final DBState fetchRegisterState() throws SQLException {
        return fetchRegisterState(LockStrength.SHARE);
    }

    private final SQLSelect createMostRecentSelect(final SQLTable t, final int limit) {
        final SQLSelect res = new SQLSelect();
        res.addSelectStar(t);
        res.setWhere(new Where(t.getField("ID_CAISSE"), "=", this.getPosID()));
        res.addFieldOrder(t.getField("DATE"), Order.desc());
        res.setLimit(limit);
        res.setLockStrength(LockStrength.SHARE);
        return res;
    }

    private final DBState fetchRegisterState(final LockStrength lockStrength) throws SQLException {
        final SQLRowValues registerVals = new SQLRowValues(getRegisterTable());
        registerVals.setAllToNull();
        registerVals.assurePath(getRegisterToLastClosureEntry()).setAllToNull();
        final SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(registerVals);
        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
            @Override
            public SQLSelect transformChecked(SQLSelect input) {
                input.setLockStrength(lockStrength);
                input.addLockedTable(registerVals.getTable().getName());
                return input;
            }
        });

        final SQLSelect selLastReceipt = createMostRecentSelect(this.receiptElem.getTable(), 1);
        final SQLSelect selLastEntries = createMostRecentSelect(this.getLogElement().getTable(), 3);

        final SQLRowValues registerR = SQLUtils.executeAtomic(getRegisterTable().getDBSystemRoot().getDataSource(), new SQLFactory<SQLRowValues>() {
            @Override
            public SQLRowValues create() throws SQLException {
                final SQLRowValues res = fetcher.fetchOne(getPosID());
                if (res == null)
                    throw new IllegalStateException("Register not found : " + getPosID());

                final List<SQLRow> lastEntries = SQLRowListRSH.execute(selLastEntries);
                for (final SQLRow r : lastEntries) {
                    final SQLRowValues vals = r.asRowValues();
                    vals.put("ID_CAISSE", res);
                }

                final List<SQLRow> receipts = SQLRowListRSH.execute(selLastReceipt);
                if (receipts.size() == 1)
                    receipts.get(0).asRowValues().put("ID_CAISSE", res);

                res.getGraph().freeze();
                return res;
            }
        });

        return new DBState(this, registerR);
    }

    public final DBState open(final String lastLocalHash, final int userID) throws SQLException {
        final DBState res = SQLUtils.executeAtomic(getRegisterTable().getDBSystemRoot().getDataSource(), new SQLFactory<DBState>() {
            @Override
            public DBState create() throws SQLException {
                final DBState fetchedState = fetchRegisterState(LockStrength.UPDATE);

                checkStatus(fetchedState, Status.CLOSED);
                checkHashes(fetchedState, lastLocalHash);
                // set the date here, since the DB is opened before the FS
                // TODO support work days other than midnight to midnight by defining a start hour
                // then openDay should be the day when the current period started.
                // E.g. if a work day starts 25/01 at 15h, even if the register is opened 26/01 at
                // 01h then openDay is 25/01
                final Date openDay = new Date();

                final SQLRowValues lastOpeningEntry = fetchedState.getLastOpeningEntry();
                if (lastOpeningEntry != null && TimeUtils.isSameDay(lastOpeningEntry.getDate("DATE"), openDay))
                    throw new IllegalStateException("The state to be created would be in the same day as the previous : " + lastOpeningEntry + " ; " + openDay);

                // verifications OK, proceed to actually open
                createUpdateVals(fetchedState, userID, Status.OPEN, openDay).commit();

                return fetchRegisterState();
            }
        });
        POSConfiguration.getLogger().log(Level.INFO, "Finished opening of DB state for register {0}", this.getPosID());
        return res;
    }

    private final void checkStatus(final DBState fetchedState, final Status expected) {
        if (!fetchedState.getRegisterState().getStatus().equals(expected))
            throw new IllegalStateException("DB is currently " + fetchedState.getRegisterState());
    }

    private final void checkHashes(final DBState fetchedState, final String lastLocalHash) {
        final SQLRowValues lastReceipt = fetchedState.getLastReceiptRow();
        final String lastDBHash = lastReceipt == null ? null : lastReceipt.getString("FILE_HASH");
        if (!CompareUtils.equals(lastLocalHash, lastDBHash))
            throw new IllegalStateException("last DB receipt (" + lastDBHash + ") doesn't match last local receipt (" + lastLocalHash + ")");
    }

    private final void checkDate(final DBState fetchedState, final Date date) {
        final SQLRowValues lastEntry = fetchedState.getLastEntry();
        if (lastEntry == null)
            return;
        final Calendar previousEntryCal = lastEntry.getDate("DATE");
        final Date previousEntryDate = previousEntryCal.getTime();
        if (previousEntryDate.compareTo(date) >= 0)
            throw new IllegalStateException("Previous date is after state to be created : " + previousEntryDate + " >= " + date);
    }

    private final SQLRowValues createUpdateVals(final DBState fetchedState, final int userID, final Status newStatus, final Date date) {
        checkDate(fetchedState, date);

        final SQLRowValues registerVals = new SQLRowValues(getRegisterTable());
        registerVals.setPrimaryKey(fetchedState.getRegisterRow());
        final SQLRowValues logVals = new SQLRowValues(getLogElement().getTable());
        logVals.put("ID_CAISSE", registerVals);
        logVals.put("DATE", date);
        logVals.put("ID_USER", userID);
        logVals.put("EVT", newStatus.name());
        logVals.put("CREATOR", this.productInfo.getFullID());
        logVals.put("CREATOR_VERSION", this.productInfo.getVersion());
        fetchedState.fillHostValues(logVals);
        return registerVals;
    }

    // TODO monthly and yearly closures

    public final DBState close(final POSConfiguration posConf, final RegisterLog log) throws SQLException, ParseException, IOException {
        final List<ReceiptEntry> receiptEvents = log.getReceiptEvents();
        final RegisterEntry closureEntry = log.getLastRegisterEvent();
        if (closureEntry.getType() != EventType.REGISTER_CLOSURE)
            throw new IllegalArgumentException("Log not closed");
        POSConfiguration.checkRegisterID(log.getRegisterID(), this.getPosID());

        final List<Ticket> receipts = log.parseReceipts();
        final DBState res = SQLUtils.executeAtomic(getRegisterTable().getDBSystemRoot().getDataSource(), new SQLFactory<DBState>() {
            @Override
            public DBState create() throws SQLException {
                final DBState fetchedState = fetchRegisterState(LockStrength.UPDATE);

                checkStatus(fetchedState, Status.OPEN);
                checkHashes(fetchedState, log.getFirstRegisterEvent().getLastReceiptHash());
                final Date ourDate = closureEntry.getDate();

                // verifications OK, proceed to import all receipts
                posConf.importReceipts(receipts, receiptEvents);

                // actually close
                final SQLRowValues registerVals = createUpdateVals(fetchedState, posConf.getUserID(), Status.CLOSED, ourDate);
                final SQLRowValues newLogEntry = CollectionUtils.getSole(registerVals.getReferentRows(getLogElement().getTable()));
                if (newLogEntry == null)
                    throw new IllegalStateException("Missing log entry in " + registerVals);
                final SQLRowValues closureVals = registerVals.putRowValues("ID_DERNIERE_CLOTURE");
                closureVals.put("ID_ENTREE_JOURNAL", newLogEntry);
                closureVals.put("PERIODE", "journalière");
                closureVals.put("DEBUT", log.getFirstRegisterEvent().getDate());
                closureVals.put("FIN", ourDate);
                fillRow(closureVals, receiptEvents, receipts);

                registerVals.commit();

                return fetchRegisterState();
            }
        });
        POSConfiguration.getLogger().log(Level.INFO, "Finished closure of DB state for register {0}", this.getPosID());
        return res;
    }

    static SQLRowValues fillRow(final SQLRowValues closureVals, final List<ReceiptEntry> receiptEvents, final List<Ticket> receipts) {
        BigDecimal totalTTC = BigDecimal.ZERO;
        for (final Ticket t : receipts) {
            totalTTC = totalTTC.add(BigDecimal.valueOf(t.getPaidTotal()).movePointLeft(2));
        }
        closureVals.put("TOTAL_TTC", totalTTC);
        if (!receiptEvents.isEmpty()) {
            closureVals.put("PREMIER_TICKET", receiptEvents.get(0).getCodeString());
            closureVals.put("PREMIER_TICKET_HASH", receiptEvents.get(0).getFileHash());
            final ReceiptEntry lastReceiptEntry = receiptEvents.get(receiptEvents.size() - 1);
            closureVals.put("DERNIER_TICKET", lastReceiptEntry.getCodeString());
            closureVals.put("DERNIER_TICKET_HASH", lastReceiptEntry.getFileHash());
        }
        return closureVals;
    }
}