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;

    }

}