/******************************************************************************

  * PostersDaoJdbc.java - Implement the Dao interface for the Posters

  * 

  * PicMan - The BuckoSoft Picture Manager in Java

  * Copyright(c) 2006 - Dick Balaska

  * 

  */ 

 package com.buckosoft.PicMan.db;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.sql.Types;

 import java.util.List;

 import javax.sql.DataSource;

 import org.apache.commons.logging.Log;

 import org.apache.commons.logging.LogFactory;

 import org.springframework.dao.DataAccessException;

 import org.springframework.jdbc.core.SqlParameter;

 import org.springframework.jdbc.object.MappingSqlQuery;

 import org.springframework.jdbc.object.SqlFunction;

 import org.springframework.jdbc.object.SqlUpdate;

 import com.buckosoft.PicMan.domain.Poster;

 /** Implement the Dao Interface for the {@link com.buckosoft.PicMan.domain.Poster}s.

  * @author Dick Balaska

  * @since 2012/07/22

  * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/src/com/buckosoft/PicMan/db/PostersDaoJdbc.java">PostersDaoJdbc.java</a>

  */

 public class PostersDaoJdbc implements PostersDao {

         private static final boolean DEBUG = true;

         protected final Log logger = LogFactory.getLog(getClass());

         private DataSource ds;

         /** Set the reference to the JDBC datasource.

          * @param ds The datasource as configured by Spring.

          * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/WebContent/WEB-INF/picManDatabase/picManDatabase.xml">picManDatabase.xml</a>

          */ 

         public void setDataSource(DataSource ds) {

                 this.ds = ds;

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.PostersDao#getPoster(int)

          */

         public Poster getPoster(int pid) {

                 if (DEBUG)

                         logger.info("getPoster: " + pid);

                 if (sql_getPosterQUERY == null)

                         sql_getPosterQUERY = new PostersQuery(ds, pid);

         Poster m = (Poster)sql_getPosterQUERY.findObject(pid);

                getPosterConfig(m);

         return(m);

         }

         private        PostersQuery        sql_getPosterQUERY = null;

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.PostersDao#getPosters()

          */

         @SuppressWarnings("unchecked")

         public List<Poster> getPosters() {

                 if (DEBUG)

                         logger.info("GetPostersList");

                 if (sql_getPostersListQUERY == null)

                         sql_getPostersListQUERY = new PostersQuery(ds);

                 List<Poster> PosterList = (List<Poster>)sql_getPostersListQUERY.execute();

                 for (Poster m : PosterList)

                         this.getPosterConfig(m);

                 return(PosterList);

         }

         private        PostersQuery        sql_getPostersListQUERY = null;

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.PostersDao#deletePoster(int)

          */

         public void deletePoster(int mid) {

                 if (DEBUG)

                         logger.info("deletePoster");

                 // TODO deletePoster not implemented

                 throw new RuntimeException("Not implemented");

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.PostersDao#storePoster(com.buckosoft.PicMan.domain.Poster)

          */

         public void storePoster(Poster poster) {

                 if (DEBUG)

                         logger.info("storePoster " + poster.getPid());

                 if (poster.getPid() <= 0) {

                         PostersInsert mi = new PostersInsert(ds);

                         mi.insert(poster);

                         int c = getPosterCount();

                         poster.setPid(c);

                 } else {

                         PostersUpdate mu = new PostersUpdate(ds);

                         mu.update(poster);

                 }

                 deletePosterConfigs(poster.getPid());

                 if (sql_storePosterConfigsINSERT == null)

                         sql_storePosterConfigsINSERT = new PosterConfigsInsert(ds);

                 for (String key : poster.getConfig().keySet()) {

                         sql_storePosterConfigsINSERT.insert(poster.getPid(), key, poster.getConfig().get(key));

                 }

         }

         private        PosterConfigsInsert        sql_storePosterConfigsINSERT = null;

         /** Get the number of Virgins in the database

          * @return the count

          */

         public int        getPosterCount() {

                 if (DEBUG)

                         logger.info("getPosterCount:");

                 if (sql_getPosterCountQUERYr == null)

                         sql_getPosterCountQUERYr = new PostersCount(ds);

                 return(sql_getPosterCountQUERYr.run());

         }

         private PostersCount        sql_getPosterCountQUERYr;

         @SuppressWarnings("unchecked")

         private        void getPosterConfig(Poster poster) {

                 if (poster == null)

                         return;

                 if (sql_getPosterConfigQUERY == null)

                         sql_getPosterConfigQUERY = new PosterConfigsQuery(ds, 0);

                 List<KeyValue>kvlist = (List<KeyValue>)sql_getPosterConfigQUERY.execute(poster.getPid());

                 for (KeyValue kv : kvlist) {

                         poster.getConfig().put(kv.key, kv.value);

                 }

         }

         private        PosterConfigsQuery        sql_getPosterConfigQUERY = null;

         private void deletePosterConfigs(int sid) throws DataAccessException {

                 PosterConfigsDelete msd = new PosterConfigsDelete(ds);

                 msd.delete(sid);

         }

         /**

          * Object to Query for Posters

          */

         private class PostersQuery extends MappingSqlQuery {

                 PostersQuery(DataSource ds) {

                         super(ds, "SELECT * from posters");

                         compile();

                 }

                 PostersQuery(DataSource ds, int pid) {

                         super(ds, "SELECT * from posters where pid = ?");

                         declareParameter(new SqlParameter(Types.INTEGER));

                         compile();

                 }

                 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {

                         Poster p = new Poster();

                         p.setName(rs.getString("name"));

                         p.setOutputPath(rs.getString("outputPath"));

                         p.setPid(rs.getInt("pid"));

                         p.setMasterMid(rs.getInt("mosaicId"));

 //                        try {

 //                                m.setStartTime(rs.getDate("startTime"));

 //                        } catch (Exception e) {logger.info(e);}

                         return(p);

                 }

         }

         /**

          * <code>Virgin</code> Count Object.

          */

         class PostersCount extends SqlFunction {

                 PostersCount(DataSource ds) {

                         super(ds, "SELECT COUNT(*) from posters");

                         compile();

                 }                

         }

         /**

          * <code>Poster</code> Insert Object.

          */

         private class PostersInsert extends SqlUpdate {

                 /**

                  * Create a new instance of PostersInsert.

                  * @param ds the DataSource to use for the insert

                  */

                 protected PostersInsert(DataSource ds) {

                         super(ds, "INSERT INTO posters VALUES(?,?,?,?)");

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         compile();

                 }

                 protected void insert(Poster p) {

                         Object[] objs = new Object[] {

                                 null,

                                 p.getName(),

                                 new Integer(p.getMasterMid()),

                                 p.getOutputPath(),

                         };

                         super.update(objs);

                 }

         }

         ///////////////////////////////////////////////////////////////////////////////////////////////

         /**

          * <code>Poster</code> Update Object.

          */

         private class PostersUpdate extends SqlUpdate {

                 //private        DataSource        ds;

                 /**

                  * Create a new instance of PostersUpdate.

                  * @param ds the DataSource to use for the update

                  */

                 protected PostersUpdate(DataSource ds) {

                         super(ds, "UPDATE posters SET name=?, mosaicId=?, outputPath=? WHERE pid = ? LIMIT 1");

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         compile();

                 }

                 /**

                  * Method to update the <code>Set</code>'s data.

                  * @param List sets The sets that should be stored

                  * @return 0

                  */

                 protected int update(Poster p) {

                         return this.update(new Object[] {

                                         p.getName(),

                                         new Integer(p.getMasterMid()),

                                         p.getOutputPath(),

                                         new Integer(p.getPid())

                         });

                 }

         }

         private class KeyValue {

                 public String        key;

                 public String        value;

         }

         private class PosterConfigsQuery extends MappingSqlQuery {

                 PosterConfigsQuery(DataSource ds, int pid) {

             super(ds, "SELECT * from posterConfigs where pid=?");

                         declareParameter(new SqlParameter(Types.INTEGER));

             compile();

         }

         protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {

                 KeyValue k = new KeyValue();

                 k.key = rs.getString("key");

                 k.value = rs.getString("value");

                 return(k);

         }

         }

         /**

          * <code>PosterConfig</code> Delete Object.

          */

         protected class PosterConfigsDelete extends SqlUpdate {

                 /**

                  * Create a new instance of MetaSetRemove.

                  * @param ds the DataSource to use for the delete

                  */

                 protected PosterConfigsDelete(DataSource ds) {

                         super(ds, "DELETE FROM posterConfigs WHERE pid = (?)");

                         declareParameter(new SqlParameter(Types.INTEGER));

                         compile();

                 }

                 protected void delete(int sid) {

                         super.update(sid);

                 }

         }

         /**

          * <code>PosterConfigs</code> Insert Object.

          */

         protected class PosterConfigsInsert extends SqlUpdate {

                 /**

                  * Create a new instance of MetaSetInsert.

                  * @param ds the DataSource to use for the insert

                  */

                 protected PosterConfigsInsert(DataSource ds) {

                         super(ds, "INSERT INTO posterConfigs VALUES(?,?,?)");

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         compile();

                 }

                 protected void insert(int pid, String key, String value) {

                         Object[] objs = new Object[] {

                                         new Integer(pid),

                                         key,

                                         value,

                         };

                         super.update(objs);

                 }

         }

 }