/*
 * Copyright (C) 2018, 2019, 2021 Andrew Gegg
 *
 *	This file is part of the Garden Notebook application
 *
 * The Garden Notebook application is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/gpl.html>.
 */

/*
	Change log
	2.2.0   Support hsqldb dialect
    2.3.0   Retrieve generated keys properly!
            Date handling change - seems to be a MySQL 8 thing.
    2.4.0   Support MS SQLServer
	3.0.0	Support converting ToDoList items (back) into Reminders to delay the action.
			Support 'watch-for' entries from PurchaseItems.
			Use DBCommentHandler
 */

package uk.co.gardennotebook.mysql;

import uk.co.gardennotebook.spi.*;

import uk.co.gardennotebook.util.StoryLineTree;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Date;
import java.sql.Timestamp;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
import org.apache.logging.log4j.Level;

/**
*
*{@inheritDoc}
*
*	@author	Andy Gegg
*	@version	3.0.0
*	@since	1.0
*/
final class ReminderBuilder implements IReminderBuilder
{
	private static final Logger LOGGER = LogManager.getLogger();

	private IReminder oldInstance = null;

	private final boolean newInstance;

//	private INotebookEntry newAncestor = null;
	private boolean changedAncestor = false;

	private int id;

	/*
	*	The reminder is for an activity on plants of this species.
@apiNote
If present, plantVarietyId may or may not be given.
	*/
	private Integer plantSpeciesId;
	private boolean changedPlantSpeciesId = false;

	/*
	*	The reminder is for an activity on plants of this variety.
@apiNote
If present, plantSpeciesId must be given.
	*/
	private Integer plantVarietyId;
	private boolean changedPlantVarietyId = false;

	/*
	*	The reminder is to perform this activity.
	*/
	private Integer husbandryClassId;
	private boolean changedHusbandryClassId = false;

	/*
	*	The reminder is to perform this activity.
	*/
	private Integer groundWorkActivityId;
	private boolean changedGroundWorkActivityId = false;

	/*
	*	The Husbandry editor can set up a ToDoList entry intended as a 'watch for this to happen' (e.g. on sowing, add a 'watch for germination').<BR>
This field is the id of that parent/ancestor Husbandry event so when this ToDoList entry is processed the appropriate Storyline links can be set up.
	*/
	private Integer husbandryId;
	private boolean changedHusbandryId = false;

	/*
	*	The Purchase editor can set up a ToDoList entry intended as a 'watch for this to happen' (e.g. for a seed purchase add a 'watch for sowing').<BR>
This field is the id of that parent/ancestor Husbandry event so when this ToDoList entry is processed the appropriate Storyline links can be set up.
	*/
	private Integer purchaseItemId;
	private boolean changedPurchaseItemId = false;

	/*
	*	For a single shot reminder, the first date it will be shown on.<BR>
For a repeating reminder, the current or next interval when the reminder will be active.
	*/
	private LocalDate showFrom;
	private boolean changedShowFrom = false;

	/*
	*	If true, the Reminder is only activated once.
	*/
	private boolean singleShot = true;
	private boolean changedSingleShot = false;

	/*
	*	For a repeating reminder, the unit of the repeat interval (see repeatQuantifier).<UL>
<LI>D - daily
<LI>W - weekly
<LI>M - monthly
<LI>Y - annually
</UL>
A value is required for repeating reminders so set a default for safety
	*/
	private String repeatInterval = "W";
	private boolean changedRepeatInterval = false;

	/*
	*	For a repeating Reminder, the number of repeatIntervals between repeats (see repeatInterval).<BR>
So 'weekly' and '2' means every fortnight.  0 means no repetition (avoids complications with nullable ints in the code - an impossible combination!)<BR>
A value is required for repeating reminders so set non-null with a default for safety
	*/
	private Integer repeatQuantifier = 0;
	private boolean changedRepeatQuantifier = false;

	/*
	*	For a repeating reminder, the LAST time this reminder will be shown.<BR>
Null means 'repeat forever'.
	*/
	private LocalDate repeatUntil;
	private boolean changedRepeatUntil = false;

	/*
	*	The actual text of the reminder.
	*/
	private String description;
	private boolean changedDescription = false;
	private LocalDateTime lastUpdated;
	private LocalDateTime created;
	private boolean somethingChanged = false;

	private boolean changedComments = false;

	private DBCommentHandler commentHandler;	//	compiler will not allow declaration as final

	/**
	*	constructor to use for a new entry
	*/
	ReminderBuilder()
	{
		this(null);
	}

	/**
	*	constructor to use to edit or delete an existing entry
	*
	*	@param	oldVal	the existing item to modify or delete; if null a new entry will be created
	*/
	ReminderBuilder(final IReminder oldVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("constructor(): oldVal={}", oldVal);
		if (oldVal == null || oldVal.getKey() == null || !(oldVal.getKey() instanceof Integer))
		{
			newInstance = true;
			oldInstance = null;
			this.id = -1;
			//	need to set the default value for dates
			this.showFrom = LocalDate.now();
			commentHandler = new DBCommentHandler(NotebookEntryType.REMINDER);
			return;
		}

		newInstance = false;
		oldInstance = oldVal;

//		Reminder baseObj;
		if (oldVal instanceof Reminder baseObj)
		{
//			baseObj = (Reminder)oldVal;
			this.id = baseObj.getId();
			this.plantSpeciesId = baseObj.getPlantSpeciesId();
			this.plantVarietyId = baseObj.getPlantVarietyId();
			this.husbandryClassId = baseObj.getHusbandryClassId();
			this.groundWorkActivityId = baseObj.getGroundWorkActivityId();
			this.husbandryId = baseObj.getHusbandryId();
			this.purchaseItemId = baseObj.getPurchaseItemId();
			this.showFrom = baseObj.getShowFrom();
			this.singleShot = baseObj.isSingleShot();
			this.repeatInterval = baseObj.getRepeatInterval().orElse(null);
			this.repeatQuantifier = baseObj.getRepeatQuantifier().orElse(null);
			this.repeatUntil = baseObj.getRepeatUntil().orElse(null);
			this.description = baseObj.getDescription().orElse(null);
			this.lastUpdated = baseObj.getLastUpdated();
			this.created = baseObj.getCreated();
		}
		else
		{
			Object ky = oldVal.getKey();
			if (ky == null) return;
			if (ky instanceof Integer)
				this.id = (Integer)ky;
			ky = oldVal.getPlantSpecies().orElse(null);
			if (ky == null)
			{
				this.plantSpeciesId = null;
			}
			else
			{
				this.plantSpeciesId = ((IPlantSpecies)ky).getKey();
			}
			ky = oldVal.getPlantVariety().orElse(null);
			if (ky == null)
			{
				this.plantVarietyId = null;
			}
			else
			{
				this.plantVarietyId = ((IPlantVariety)ky).getKey();
			}
			ky = oldVal.getHusbandryClass().orElse(null);
			if (ky == null)
			{
				this.husbandryClassId = null;
			}
			else
			{
				this.husbandryClassId = ((IHusbandryClass)ky).getKey();
			}
			ky = oldVal.getGroundworkActivity().orElse(null);
			if (ky == null)
			{
				this.groundWorkActivityId = null;
			}
			else
			{
				this.groundWorkActivityId = ((IGroundworkActivity)ky).getKey();
			}

			ky = oldVal.getHusbandry().orElse(null);
			if (ky == null)
			{
				this.husbandryId = null;
			}
			else
			{
				this.husbandryId = ((IHusbandry)ky).getKey();
			}

			ky = oldVal.getPurchaseItem().orElse(null);
			if (ky == null)
			{
				this.purchaseItemId = null;
			}
			else
			{
				this.purchaseItemId = ((IPurchaseItem)ky).getKey();
			}

			this.showFrom = oldVal.getShowFrom();
			this.singleShot = oldVal.isSingleShot();
			this.repeatInterval = oldVal.getRepeatInterval().orElse(null);
			this.repeatQuantifier = oldVal.getRepeatQuantifier().orElse(null);
			this.repeatUntil = oldVal.getRepeatUntil().orElse(null);
			this.description = oldVal.getDescription().orElse(null);
			this.lastUpdated = oldVal.getLastUpdated();
			this.created = oldVal.getCreated();
		}
		commentHandler = new DBCommentHandler(NotebookEntryType.REMINDER, this.id);

		LOGGER.traceExit();
	}	//	constructor()

	/**
	*	give the (new) value of plantSpeciesId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IReminderBuilder plantSpeciesId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantSpeciesId(): oldVal={}, newVal={}", this.plantSpeciesId, newVal);
		if (this.plantSpeciesId == newVal) return this;
		this.plantSpeciesId = newVal;
		changedPlantSpeciesId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IReminderBuilder plantSpecies(final IPlantSpecies newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantSpecies(): oldVal={}, newVal={}", this.plantSpeciesId, newVal);
		if ((newVal == null) && (this.plantSpeciesId == null)) return this;
		if ((newVal != null) && (this.plantSpeciesId != null) && (this.plantSpeciesId.equals(newVal.getKey()))) return this;
		if (newVal == null)
		{
			this.plantSpeciesId = null;
			if (this.plantVarietyId != null)
			{
				this.plantVarietyId = null;
				changedPlantVarietyId = true;
			}
		}
		else
		{	//	non-null value
			this.plantSpeciesId = newVal.getKey();
			if (this.plantVarietyId != null)
			{
				this.plantVarietyId = null;
				changedPlantVarietyId = true;
			}
		}
		changedPlantSpeciesId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	*	give the (new) value of plantVarietyId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IReminderBuilder plantVarietyId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantVarietyId(): oldVal={}, newVal={}", this.plantVarietyId, newVal);
		if (this.plantVarietyId == newVal) return this;
		this.plantVarietyId = newVal;
		changedPlantVarietyId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IReminderBuilder plantVariety(final IPlantVariety newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantVariety(): oldVal={}, newVal={}", this.plantVarietyId, newVal);
		if ((newVal == null) && (this.plantVarietyId == null)) return this;
		if ((newVal != null) && (this.plantVarietyId != null) && (this.plantVarietyId.equals(newVal.getKey()))) return this;
		if (newVal == null)
		{
			this.plantVarietyId = null;
		}
		else
		{	//	non-null value
			this.plantVarietyId = newVal.getKey();
			Integer ps_Id = newVal.getPlantSpecies().getKey();	// cannot be null
			if (!ps_Id.equals(this.plantSpeciesId))	// equals returns false if arg is null
			{
				this.plantSpeciesId = ps_Id;
				changedPlantSpeciesId = true;
			}
		}
		changedPlantVarietyId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	*	give the (new) value of husbandryClassId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IReminderBuilder husbandryClassId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("husbandryClassId(): oldVal={}, newVal={}", this.husbandryClassId, newVal);
		if (this.husbandryClassId == newVal) return this;
		this.husbandryClassId = newVal;
		changedHusbandryClassId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IReminderBuilder husbandryClass(final IHusbandryClass newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("husbandryClass(): oldVal={}, newVal={}", this.husbandryClassId, newVal);
		if ((newVal == null) && (this.husbandryClassId == null)) return this;
		if ((newVal != null) && (this.husbandryClassId != null) && (this.husbandryClassId.equals(newVal.getKey()))) return this;
		if (newVal == null)
		{
			this.husbandryClassId = null;
		}
		else
		{	//	non-null value
			this.husbandryClassId = newVal.getKey();
		}
		changedHusbandryClassId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	*	give the (new) value of groundWorkActivityId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IReminderBuilder groundWorkActivityId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("groundWorkActivityId(): oldVal={}, newVal={}", this.groundWorkActivityId, newVal);
		if (this.groundWorkActivityId == newVal) return this;
		this.groundWorkActivityId = newVal;
		changedGroundWorkActivityId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IReminderBuilder groundworkActivity(final IGroundworkActivity newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("groundworkActivity(): oldVal={}, newVal={}", this.groundWorkActivityId, newVal);
		if ((newVal == null) && (this.groundWorkActivityId == null)) return this;
		if ((newVal != null) && (this.groundWorkActivityId != null) && (this.groundWorkActivityId.equals(newVal.getKey()))) return this;
		if (newVal == null)
		{
			this.groundWorkActivityId = null;
		}
		else
		{	//	non-null value
			this.groundWorkActivityId = newVal.getKey();
		}
		changedGroundWorkActivityId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	 *	give the (new) value of husbandryId
	 *
	 *	@param	newVal	the new value
	 *	@return	this Builder
	 */
	IReminderBuilder husbandryId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("husbandryId(): oldVal={}, newVal={}", this.husbandryId, newVal);
		if (this.husbandryId == newVal) return this;
		this.husbandryId = newVal;
		changedHusbandryId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IReminderBuilder husbandry(final IHusbandry newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("husbandry(): oldVal={}, newVal={}", this.husbandryId, newVal);
		if ((newVal == null) && (this.husbandryId == null)) return this;
		if ((newVal != null) && (this.husbandryId != null) && (this.husbandryId.equals(newVal.getKey()))) return this;
		if (newVal == null)
		{
			this.husbandryId = null;
		}
		else
		{	//	non-null value
			this.husbandryId = newVal.getKey();
			//	cannot have Husbandry AND PurchaseItem parents
			this.purchaseItemId = null;
		}
		changedHusbandryId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	/**
	 *	give the (new) value of purchaseItemId
	 *
	 *	@param	newVal	the new value
	 *	@return	this Builder
	 */
	IReminderBuilder purchaseItemId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("husbandryId(): oldVal={}, newVal={}", this.purchaseItemId, newVal);
		if (this.purchaseItemId == newVal) return this;
		this.purchaseItemId = newVal;
		changedPurchaseItemId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IReminderBuilder purchaseItem(final IPurchaseItem newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("purchaseItem(): oldVal={}, newVal={}", this.purchaseItemId, newVal);
		if ((newVal == null) && (this.purchaseItemId == null)) return this;
		if ((newVal != null) && (this.purchaseItemId != null) && (this.purchaseItemId.equals(newVal.getKey()))) return this;
		if (newVal == null)
		{
			this.purchaseItemId = null;
		}
		else
		{	//	non-null value
			this.purchaseItemId = newVal.getKey();
			//	cannot have Husbandry AND PurchaseItem parents
			this.husbandryId = null;
		}
		changedPurchaseItemId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IReminderBuilder showFrom(final LocalDate newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("showFrom(): oldVal={}, newVal={}", this.showFrom, newVal);

		if (newVal == null) return this;
		if (newVal.equals(this.showFrom)) return this;
		this.showFrom = newVal;
		changedShowFrom = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IReminderBuilder singleShot(final boolean newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("singleShot(): oldVal={}, newVal={}", this.singleShot, newVal);

		if (!(newVal ^ this.singleShot)) return this;	// ^ is exclusive or
		this.singleShot = newVal;
		changedSingleShot = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IReminderBuilder repeatInterval(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("repeatInterval(): oldVal={}, newVal={}", this.repeatInterval, newVal);

		if (newVal == null && this.repeatInterval == null) return this;
		if (newVal != null && this.repeatInterval != null && newVal.equals(this.repeatInterval)) return this;
		this.repeatInterval = newVal;
		changedRepeatInterval = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IReminderBuilder repeatQuantifier(final Integer newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("repeatQuantifier(): oldVal={}, newVal={}", this.repeatQuantifier, newVal);

		if (newVal == null && this.repeatQuantifier == null) return this;
		if (newVal != null && this.repeatQuantifier != null && newVal.equals(this.repeatQuantifier)) return this;
		this.repeatQuantifier = newVal;
		changedRepeatQuantifier = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IReminderBuilder repeatUntil(final LocalDate newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("repeatUntil(): oldVal={}, newVal={}", this.repeatUntil, newVal);

		if (newVal == null && this.repeatUntil == null) return this;
		if (newVal != null && this.repeatUntil != null && newVal.equals(this.repeatUntil)) return this;
		this.repeatUntil = newVal;
		changedRepeatUntil = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IReminderBuilder description(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("description(): oldVal={}, newVal={}", this.description, newVal);

		if (newVal == null && this.description == null) return this;
		if (newVal != null && this.description != null && newVal.equals(this.description)) return this;
		this.description = newVal;
		changedDescription = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IReminderBuilder addComment(final String... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("addComment[array]()");

		commentHandler.addComment(newVals);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IReminderBuilder addComment(final List<String> newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("addComment<list>()");

		commentHandler.addComment(newVals);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit("addComment");
		return this;
	}

	/**
	*	remove a comment from this item
	*
	*	@param	newVals	the comment to remove.  If the comment does not exist, this is a null-op
	*	@return	 this Builder
	*/
	IReminderBuilder deleteComment(int... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

		commentHandler.deleteComment(newVals);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IReminderBuilder deleteComment(final IComment... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

		commentHandler.deleteComment(newVals);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IReminderBuilder deleteComment(final List<IComment> vals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

		commentHandler.deleteComment(vals);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IReminderBuilder changeComment(final IComment base, final String comment)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

		commentHandler.changeComment(base, comment);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IReminderBuilder changeComment(final IComment base, final LocalDate date)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

		commentHandler.changeComment(base, date);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IReminderBuilder changeComment(final IComment base, final LocalDate date, final String comment)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

		commentHandler.changeComment(base, date, comment);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IReminder save() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("save(): somethingChanged: {}, newInstance: {}, changedComments: {}, changedAncestor: {}",
                                                                somethingChanged, newInstance, changedComments, changedAncestor);

		if (!somethingChanged && !newInstance && !changedComments && !changedAncestor)
		{
			LOGGER.traceExit("nothing changed");
			return MySQLCache.cacheReminder.get(this.id);
		}

		if (newInstance)
		{
			doInsert();
		}
		else if (somethingChanged)
		{
			doUpdate();
		}

		if (changedComments)
		{
			commentHandler.setParentId(this.id);
			commentHandler.save();
		}

// mark cache as dirty
		if (!newInstance &&(somethingChanged || changedComments))
		{
			MySQLCache.cacheReminder.remove(this.id);
		}
// populate the cache
		new ReminderLister().id(this.id).fetch();
		IReminder newValue = MySQLCache.cacheReminder.get(this.id);
		if (oldInstance != null)
		{
			oldInstance.flagReplaced(newValue);
		}

	//	tell any parent beans the child list has mutated
	//	only additions and deletions matter, other changes will be reflected through the child bean
		if (newInstance)
		{
			if (changedPlantSpeciesId && (plantSpeciesId != null) )
			{
				MySQLCache.cachePlantSpecies.get(plantSpeciesId).flagChildAdded(newValue);
			}
			if (changedPlantVarietyId && (plantVarietyId != null) )
			{
				MySQLCache.cachePlantVariety.get(plantVarietyId).flagChildAdded(newValue);
			}
			if (changedHusbandryClassId && (husbandryClassId != null) )
			{
				MySQLCache.cacheHusbandryClass.get(husbandryClassId).flagChildAdded(newValue);
			}
			if (changedGroundWorkActivityId && (groundWorkActivityId != null) )
			{
				MySQLCache.cacheGroundworkActivity.get(groundWorkActivityId).flagChildAdded(newValue);
			}
			if (changedHusbandryId && (husbandryId != null) )
			{
				MySQLCache.cacheHusbandry.get(husbandryId).flagChildAdded(newValue);
			}
			if (changedPurchaseItemId && (purchaseItemId != null) )
			{
				MySQLCache.cachePurchaseItem.get(purchaseItemId).flagChildAdded(newValue);
			}
		}
		else
		{	//	updated
			if (changedPlantSpeciesId)
			{
				if (oldInstance != null)
				{
					oldInstance.getPlantSpecies().ifPresent(item -> MySQLCache.cachePlantSpecies.get(item.getKey()).flagChildDeleted(oldInstance) );
				}
				newValue.getPlantSpecies().ifPresent(item -> MySQLCache.cachePlantSpecies.get(item.getKey()).flagChildAdded(newValue) );
			}
			if (changedPlantVarietyId)
			{
				if (oldInstance != null)
				{
					oldInstance.getPlantVariety().ifPresent(item -> MySQLCache.cachePlantVariety.get(item.getKey()).flagChildDeleted(oldInstance) );
				}
				newValue.getPlantVariety().ifPresent(item -> MySQLCache.cachePlantVariety.get(item.getKey()).flagChildAdded(newValue) );
			}
			if (changedHusbandryClassId)
			{
				if (oldInstance != null)
				{
					oldInstance.getHusbandryClass().ifPresent(item -> MySQLCache.cacheHusbandryClass.get(item.getKey()).flagChildDeleted(oldInstance) );
				}
				newValue.getHusbandryClass().ifPresent(item -> MySQLCache.cacheHusbandryClass.get(item.getKey()).flagChildAdded(newValue) );
			}
			if (changedGroundWorkActivityId)
			{
				if (oldInstance != null)
				{
					oldInstance.getGroundworkActivity().ifPresent(item -> MySQLCache.cacheGroundworkActivity.get(item.getKey()).flagChildDeleted(oldInstance) );
				}
				newValue.getGroundworkActivity().ifPresent(item -> MySQLCache.cacheGroundworkActivity.get(item.getKey()).flagChildAdded(newValue) );
			}
			if (changedHusbandryId)
			{
				if (oldInstance != null)
				{
					oldInstance.getHusbandry().ifPresent(item -> MySQLCache.cacheHusbandry.get(item.getKey()).flagChildDeleted(oldInstance) );
				}
				newValue.getHusbandry().ifPresent(item -> MySQLCache.cacheHusbandry.get(item.getKey()).flagChildAdded(newValue) );
			}
			if (changedPurchaseItemId)
			{
				if (oldInstance != null)
				{
					oldInstance.getPurchaseItem().ifPresent(item -> MySQLCache.cachePurchaseItem.get(item.getKey()).flagChildDeleted(oldInstance) );
				}
				newValue.getPurchaseItem().ifPresent(item -> MySQLCache.cachePurchaseItem.get(item.getKey()).flagChildAdded(newValue) );
			}
		}

		//	stop multiple saves!
		oldInstance = null;

		somethingChanged = false;
		changedComments = false;
		changedAncestor = false;
		changedPlantSpeciesId = false;
		changedPlantVarietyId = false;
		changedHusbandryClassId = false;
		changedGroundWorkActivityId = false;
		changedHusbandryId = false;
		changedPurchaseItemId = false;
		changedShowFrom = false;
		changedSingleShot = false;
		changedRepeatInterval = false;
		changedRepeatQuantifier = false;
		changedRepeatUntil = false;
		changedDescription = false;

		LOGGER.traceExit(log4jEntryMsg);
		return newValue;
	}	//	save()

	@Override
	public boolean needSave()
	{
		return somethingChanged || changedComments || changedAncestor;
	}	// needSave()

	@Override
	public boolean canSave()
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("canSave(): newInstance={}", newInstance);

		if (!needSave())
		{//	save() will be a null-op but that's OK
			return true;
		}
		if (this.showFrom == null ||
			this.showFrom == LocalDate.MAX ||
			this.showFrom == LocalDate.MIN)
		{
			LOGGER.debug("showFrom not set");
			return false;
		}
		return true;
	}	// canSave()

	@Override
	public boolean canDelete() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("canDelete(): newInstance={}", newInstance);

		return LOGGER.traceExit(log4jEntryMsg, !newInstance);
	}	// canDelete()

	@Override
	public void delete() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("delete(): newInstance={}", newInstance);

		if (newInstance) return;
		if (!canDelete()) return;

		int res = 0;
		String query = "delete from reminder where reminderId = " + this.id;
LOGGER.debug("delete(): query: {}", query);
		try (	Connection conn = DBConnection.getConnection();
				Statement stmt = conn.createStatement();	)
		{
			res = stmt.executeUpdate(query);
LOGGER.debug("delete(): result: {}", res);
			// tidy up dependencies with nullable keys
			if (res > 0) {
				query = "delete from comment where ownerId = " + this.id + " and ownerType = 'RM'";
				int res2 = stmt.executeUpdate(query);
LOGGER.debug("delete() comments: result: {}", res2);
			}
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("delete(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}
		if (res > 0)
		{
			oldInstance.flagDeleted();
			MySQLCache.cacheReminder.remove(this.id);
	//	tell any parent beans the child list has mutated
	//	only additions and deletions matter, other changes will be reflected through the child bean
			oldInstance.getPlantSpecies().ifPresent(item -> MySQLCache.cachePlantSpecies.get(item.getKey()).flagChildDeleted(oldInstance) );
			oldInstance.getPlantVariety().ifPresent(item -> MySQLCache.cachePlantVariety.get(item.getKey()).flagChildDeleted(oldInstance) );
			oldInstance.getHusbandryClass().ifPresent(item -> MySQLCache.cacheHusbandryClass.get(item.getKey()).flagChildDeleted(oldInstance) );
			oldInstance.getGroundworkActivity().ifPresent(item -> MySQLCache.cacheGroundworkActivity.get(item.getKey()).flagChildDeleted(oldInstance) );
			oldInstance.getHusbandry().ifPresent(item -> MySQLCache.cacheHusbandry.get(item.getKey()).flagChildDeleted(oldInstance) );
			oldInstance.getPurchaseItem().ifPresent(item -> MySQLCache.cachePurchaseItem.get(item.getKey()).flagChildDeleted(oldInstance) );
		}
		oldInstance = null;
LOGGER.traceExit(log4jEntryMsg);
	}	// delete()

	private void doUpdate() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("doUpdate(): newInstance={}, somethingChanged={}", newInstance, somethingChanged);

		if (newInstance) return;
		if (!somethingChanged) return;
		StringBuilder query = new StringBuilder("update reminder set ");
		if (changedPlantSpeciesId)
		{
			query.append("plantSpeciesId = ?, ");
		}

		if (changedPlantVarietyId)
		{
			query.append("plantVarietyId = ?, ");
		}

		if (changedHusbandryClassId)
		{
			query.append("husbandryClassId = ?, ");
		}

		if (changedGroundWorkActivityId)
		{
			query.append("groundWorkActivityId = ?, ");
		}

		if (changedHusbandryId)
		{
			query.append("husbandryId = ?, ");
		}

		if (changedPurchaseItemId)
		{
			query.append("purchaseItemId = ?, ");
		}

		if (changedShowFrom)
		{
			query.append("showFrom = ?, ");
		}

		if (changedSingleShot)
		{
			query.append("singleShot = ?, ");
		}

		if (changedRepeatInterval)
		{
			query.append("repeatInterval = ?, ");
		}

		if (changedRepeatQuantifier)
		{
			query.append("repeatQuantifier = ?, ");
		}

		if (changedRepeatUntil)
		{
			query.append("repeatUntil = ?, ");
		}

		if (changedDescription)
		{
			query.append("description = ?, ");
		}

		query.delete(query.length()-2, query.length());
		query.append(" where reminderId = ").append(this.id);
LOGGER.debug("doUpdate(): query={} ", query.toString());
		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString());	)
		{
			int paramIx = 1;
			if (changedPlantSpeciesId)
			{
				if (this.plantSpeciesId == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.plantSpeciesId);
					stmt.setInt(paramIx++, this.plantSpeciesId);
				}
			}

			if (changedPlantVarietyId)
			{
				if (this.plantVarietyId == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.plantVarietyId);
					stmt.setInt(paramIx++, this.plantVarietyId);
				}
			}

			if (changedHusbandryClassId)
			{
				if (this.husbandryClassId == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.husbandryClassId);
					stmt.setInt(paramIx++, this.husbandryClassId);
				}
			}

			if (changedGroundWorkActivityId)
			{
				if (this.groundWorkActivityId == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.groundWorkActivityId);
					stmt.setInt(paramIx++, this.groundWorkActivityId);
				}
			}

			if (changedHusbandryId)
			{
				if (this.husbandryId == null)
				{
					LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
					LOGGER.debug("doUpdate(): param {}={}", paramIx, this.husbandryId);
					stmt.setInt(paramIx++, this.husbandryId);
				}
			}

			if (changedPurchaseItemId)
			{
				if (this.purchaseItemId == null)
				{
					LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
					LOGGER.debug("doUpdate(): param {}={}", paramIx, this.purchaseItemId);
					stmt.setInt(paramIx++, this.purchaseItemId);
				}
			}

			if (changedShowFrom)
			{
LOGGER.debug("doUpdate(): param {}={}", paramIx, Date.valueOf(this.showFrom));
				stmt.setDate(paramIx++, Date.valueOf(this.showFrom), java.util.Calendar.getInstance()); //  2.3.0
			}

			if (changedSingleShot)
			{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.singleShot);
				stmt.setBoolean(paramIx++, this.singleShot);
			}

			if (changedRepeatInterval)
			{
				if (this.repeatInterval == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.repeatInterval);
					stmt.setString(paramIx++, this.repeatInterval);
				}
			}

			if (changedRepeatQuantifier)
			{
				if (this.repeatQuantifier == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.repeatQuantifier);
					stmt.setInt(paramIx++, this.repeatQuantifier);
				}
			}

			if (changedRepeatUntil)
			{
				if (this.repeatUntil == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.DATE);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, Date.valueOf(this.repeatUntil));
					stmt.setDate(paramIx++, Date.valueOf(this.repeatUntil), java.util.Calendar.getInstance()); //  2.3.0
				}
			}

			if (changedDescription)
			{
				if (this.description == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.description);
					stmt.setString(paramIx++, this.description);
				}
			}

			stmt.executeUpdate();

		}catch (SQLException ex) {
			LOGGER.error("doUpdate(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}
LOGGER.traceExit(log4jEntryMsg);
	}	// doUpdate

	private void doInsert() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("doInsert(): newInstance={}", newInstance);

		if (!newInstance) return;
		if (!canSave())
		{
			throw LOGGER.throwing(Level.ERROR, new IllegalStateException("doInsert(): save criteria not met"));
		}

		StringBuilder query = new StringBuilder("insert into reminder (");
		query.append("showFrom, ");
		query.append("singleShot, ");
		if (changedPlantSpeciesId)
		{
			query.append("plantSpeciesId, ");
		}

		if (changedPlantVarietyId)
		{
			query.append("plantVarietyId, ");
		}

		if (changedHusbandryClassId)
		{
			query.append("husbandryClassId, ");
		}

		if (changedGroundWorkActivityId)
		{
			query.append("groundWorkActivityId, ");
		}

		if (changedHusbandryId)
		{
			query.append("husbandryId, ");
		}

		if (changedPurchaseItemId)
		{
			query.append("purchaseItemId, ");
		}

		if (changedRepeatInterval)
		{
			query.append("repeatInterval, ");
		}

		if (changedRepeatQuantifier)
		{
			query.append("repeatQuantifier, ");
		}

		if (changedRepeatUntil)
		{
			query.append("repeatUntil, ");
		}

		if (changedDescription)
		{
			query.append("description, ");
		}

		query.replace(query.length()-2, query.length(), ") values (");
		query.append("?, ");
		query.append("?, ");
		if (changedPlantSpeciesId)
		{
			query.append("?, ");
		}

		if (changedPlantVarietyId)
		{
			query.append("?, ");
		}

		if (changedHusbandryClassId)
		{
			query.append("?, ");
		}

		if (changedGroundWorkActivityId)
		{
			query.append("?, ");
		}

		if (changedHusbandryId)
		{
			query.append("?, ");
		}

		if (changedPurchaseItemId)
		{
			query.append("?, ");
		}

		if (changedRepeatInterval)
		{
			query.append("?, ");
		}

		if (changedRepeatQuantifier)
		{
			query.append("?, ");
		}

		if (changedRepeatUntil)
		{
			query.append("?, ");
		}

		if (changedDescription)
		{
			query.append("?, ");
		}

		query.replace(query.length()-2, query.length(), ")");
LOGGER.debug("doInsert(): query={}", query.toString());

		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS); )
		{
			int paramIx = 1;
LOGGER.debug("doInsert(): param {}={}", paramIx, Date.valueOf(this.showFrom));
			stmt.setDate(paramIx++, Date.valueOf(this.showFrom), java.util.Calendar.getInstance()); //  2.3.0
LOGGER.debug("doInsert(): param {}={}", paramIx, this.singleShot);
			stmt.setBoolean(paramIx++, this.singleShot);
			if (changedPlantSpeciesId) {
				if (this.plantSpeciesId == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.plantSpeciesId);
					stmt.setInt(paramIx++, this.plantSpeciesId);
				}
			}

			if (changedPlantVarietyId) {
				if (this.plantVarietyId == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.plantVarietyId);
					stmt.setInt(paramIx++, this.plantVarietyId);
				}
			}

			if (changedHusbandryClassId) {
				if (this.husbandryClassId == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.husbandryClassId);
					stmt.setInt(paramIx++, this.husbandryClassId);
				}
			}

			if (changedGroundWorkActivityId) {
				if (this.groundWorkActivityId == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.groundWorkActivityId);
					stmt.setInt(paramIx++, this.groundWorkActivityId);
				}
			}

			if (changedHusbandryId) {
				if (this.husbandryId == null)
				{
					LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
					LOGGER.debug("doInsert(): param {}={}", paramIx, this.husbandryId);
					stmt.setInt(paramIx++, this.husbandryId);
				}
			}

			if (changedPurchaseItemId) {
				if (this.purchaseItemId == null)
				{
					LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
					LOGGER.debug("doInsert(): param {}={}", paramIx, this.purchaseItemId);
					stmt.setInt(paramIx++, this.purchaseItemId);
				}
			}

			if (changedRepeatInterval) {
				if (this.repeatInterval == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.repeatInterval);
					stmt.setString(paramIx++, this.repeatInterval);
				}
			}

			if (changedRepeatQuantifier) {
				if (this.repeatQuantifier == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.INTEGER);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.repeatQuantifier);
					stmt.setInt(paramIx++, this.repeatQuantifier);
				}
			}

			if (changedRepeatUntil) {
				if (this.repeatUntil == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.DATE);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, Date.valueOf(this.repeatUntil));
					stmt.setDate(paramIx++, Date.valueOf(this.repeatUntil), java.util.Calendar.getInstance()); //  2.3.0
				}
			}

			if (changedDescription) {
				if (this.description == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.description);
					stmt.setString(paramIx++, this.description);
				}
			}

			stmt.executeUpdate();

			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			int newId = rs.getInt(1);
LOGGER.debug("doInsert(): newId: {}", newId);
			this.id = newId;
		}catch (SQLException ex) {
			LOGGER.error("doInsert(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}

LOGGER.traceExit(log4jEntryMsg);
	}	// doInsert

	/**
	*	Used to restore a table from a JSON dump.
	*	The ReminderBuilder(oldVal) constructor MUST be used with a Reminder object created using its JSON constructor.
	*	All fields (including timestamps) are written to the database, no checks are made.
	*
	*	@param	newVal	A Reminder object created from a JSON object written as a DUMP.
*
*	@throws	GNDBException	If the underlying MySQL database throws SQLException it is translated to this.
*				The causal SQLException can be retrieved by <code>getCause()</code>
	*/
	void doJsonInsert(Reminder newVal) throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("doJsonInsert(): newVal={}", newVal);

		int currId = newVal.getId();
		StringBuilder query = new StringBuilder("insert into reminder (");
		if (newVal.getId() > 0)
		{//this forces the value of the id field.  The >0 test is a bodge.
			query.append("reminderId, ");
		}
		query.append("plantSpeciesId, ");
		query.append("plantVarietyId, ");
		query.append("husbandryClassId, ");
		query.append("groundWorkActivityId, ");
		query.append("husbandryId, ");
		query.append("purchaseItemId, ");
		query.append("showFrom, ");
		query.append("singleShot, ");
		query.append("repeatInterval, ");
		query.append("repeatQuantifier, ");
		query.append("repeatUntil, ");
		query.append("description, ");
		query.append("lastUpdated, ");
		query.append("created, ");
//		query.replace(query.length()-2, query.length(), ") values (");
		query.replace(query.length()-2, query.length(), ")");
        if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.hsqldb && newVal.getId() > 0)
        {
            query.append(" overriding system value ");
        }
        query.append(" values (");
		if (newVal.getId() > 0)
		{//this forces the value of the id field.  The >0 test is a bodge.
			query.append(newVal.getId()).append(", ");
		}
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.replace(query.length()-2, query.length(), ")");
LOGGER.debug("doJsonInsert(): query={}", query.toString());

		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS); )
		{
			int paramIx = 1;
			if (newVal.getPlantSpeciesId() == null)
			{
LOGGER.debug("doJsonInsert(): param {} null", paramIx);
				stmt.setNull(paramIx++, java.sql.Types.INTEGER);
			}
			else
			{
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getPlantSpeciesId());
				stmt.setInt(paramIx++, newVal.getPlantSpeciesId());
			}

			if (newVal.getPlantVarietyId() == null)
			{
LOGGER.debug("doJsonInsert(): param {} null", paramIx);
				stmt.setNull(paramIx++, java.sql.Types.INTEGER);
			}
			else
			{
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getPlantVarietyId());
				stmt.setInt(paramIx++, newVal.getPlantVarietyId());
			}

			if (newVal.getHusbandryClassId() == null)
			{
LOGGER.debug("doJsonInsert(): param {} null", paramIx);
				stmt.setNull(paramIx++, java.sql.Types.INTEGER);
			}
			else
			{
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getHusbandryClassId());
				stmt.setInt(paramIx++, newVal.getHusbandryClassId());
			}

			if (newVal.getGroundWorkActivityId() == null)
			{
LOGGER.debug("doJsonInsert(): param {} null", paramIx);
				stmt.setNull(paramIx++, java.sql.Types.INTEGER);
			}
			else
			{
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getGroundWorkActivityId());
				stmt.setInt(paramIx++, newVal.getGroundWorkActivityId());
			}

			if (newVal.getHusbandryId() == null)
			{
				LOGGER.debug("doJsonInsert(): param {} null", paramIx);
				stmt.setNull(paramIx++, java.sql.Types.INTEGER);
			}
			else
			{
				LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getHusbandryId());
				stmt.setInt(paramIx++, newVal.getHusbandryId());
			}

			if (newVal.getPurchaseItemId() == null)
			{
				LOGGER.debug("doJsonInsert(): param {} null", paramIx);
				stmt.setNull(paramIx++, java.sql.Types.INTEGER);
			}
			else
			{
				LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getPurchaseItemId());
				stmt.setInt(paramIx++, newVal.getPurchaseItemId());
			}

			LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getShowFrom());
			stmt.setDate(paramIx++, Date.valueOf(newVal.getShowFrom()), java.util.Calendar.getInstance()); //  2.3.0
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.isSingleShot());
			stmt.setBoolean(paramIx++, newVal.isSingleShot());
			if (!newVal.getRepeatInterval().isPresent())
			{
LOGGER.debug("doJsonInsert(): param {} null", paramIx);
				stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
			}
			else
			{
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getRepeatInterval().get());
				stmt.setString(paramIx++, newVal.getRepeatInterval().get());
			}

			if (!newVal.getRepeatQuantifier().isPresent())
			{
LOGGER.debug("doJsonInsert(): param {} null", paramIx);
				stmt.setNull(paramIx++, java.sql.Types.INTEGER);
			}
			else
			{
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getRepeatQuantifier().get());
				stmt.setInt(paramIx++, newVal.getRepeatQuantifier().get());
			}

			if (!newVal.getRepeatUntil().isPresent())
			{
LOGGER.debug("doJsonInsert(): param {} null", paramIx);
				stmt.setNull(paramIx++, java.sql.Types.DATE);
			}
			else
			{
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getRepeatUntil().get());
				stmt.setDate(paramIx++, Date.valueOf(newVal.getRepeatUntil().get()), java.util.Calendar.getInstance()); //  2.3.0
			}

			if (!newVal.getDescription().isPresent())
			{
LOGGER.debug("doJsonInsert(): param {} null", paramIx);
				stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
			}
			else
			{
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getDescription().get());
				stmt.setString(paramIx++, newVal.getDescription().get());
			}

LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getLastUpdated());
			stmt.setTimestamp(paramIx++, Timestamp.valueOf(newVal.getLastUpdated()));
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, newVal.getCreated());
			stmt.setTimestamp(paramIx++, Timestamp.valueOf(newVal.getCreated()));

            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT reminder ON");
            }
            
			stmt.executeUpdate();

			if (currId <= 0)
			{
                ResultSet rs = stmt.getGeneratedKeys();
				rs.next();
				currId = rs.getInt(1);
LOGGER.debug("doJsonInsert(): currId: {}", currId);
			}

            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT reminder OFF");
            }

            if (!newVal.getComments().isEmpty())
            {
                CommentBuilder cb = new CommentBuilder(NotebookEntryType.REMINDER, currId);
                cb.doJsonInsert(newVal.getComments(), conn);
            }
		}catch (SQLException ex) {
			LOGGER.error("doJsonInsert(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}

LOGGER.traceExit(log4jEntryMsg);
	}	// doJsonInsert

	@Override
	public boolean hasAncestor() throws GNDBException
	{
		//	this class does not participate in story lines
		return false;
	}	//	hasAncestor()

	@Override
	public StoryLineTree<? extends INotebookEntry> getAncestors() throws GNDBException
	{
		//	this class does not participate in story lines
		return StoryLineTree.emptyTree();
	}	//	getAncestors()

	@Override
	public IReminderBuilder ancestor(IPurchaseItem ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(PurchaseItem)

	@Override
	public IReminderBuilder ancestor(IGroundwork ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(Groundwork)

	@Override
	public IReminderBuilder ancestor(IAfflictionEvent ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(AfflictionEvent)

	@Override
	public IReminderBuilder ancestor(IHusbandry ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(Husbandry)

	@Override
	public IReminderBuilder ancestor(ISaleItem ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(SaleItem)

	@Override
	public boolean hasDescendant() throws GNDBException
	{
		//	this class does not participate in story lines
		return false;
	}	//	hasDescendant()

	@Override
	public StoryLineTree<? extends INotebookEntry> getDescendants() throws GNDBException
	{
		//	this class does not participate in story lines
		return StoryLineTree.emptyTree();
	}	//	getDescendants()

	@Override
	public boolean addDescendant(IPurchaseItem descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(PurchaseItem)

	@Override
	public boolean addDescendant(IGroundwork descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(Groundwork)

	@Override
	public boolean addDescendant(IAfflictionEvent descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(AfflictionEvent)

	@Override
	public boolean addDescendant(IHusbandry descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(Husbandry)

	@Override
	public boolean addDescendant(ISaleItem descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(SaleItem)

}

