package uk.ltd.conntrol.dao.cost.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;

import uk.ltd.conntrol.dao.cost.CostYearDAO_JDBC;

/**
* CostYear JDBC DAO
* Contains JDBC implementations of CostYear related methods
* which can not be performed by Hibernate yet.
*
* @spring.bean
*   name = "costYearDAOJDBC"
*   autowire = "byName"
*/
public class CostYearDAOJDBC implements CostYearDAO_JDBC {
   
    private class CostYearValidVO {
        private int id = 0;
        private String name = null;
        private String valid = null;
       
        CostYearValidVO(){
        }
       
        public CostYearValidVO(
            int id,
            String name,
            String valid
        ){
            this.id = id;
            this.name = name;
            this.valid = valid;
        }

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getValid() {
            return valid;
        }

        public void setValid(String valid) {
            this.valid = valid;
        }
    }
   
    private class ValidForActivationRowMapper implements RowMapper  {
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            CostYearValidVO costYearValidVO = new CostYearValidVO(
                rs.getInt("id"),
                rs.getString("name"),
                rs.getString("valid")
            );
            return costYearValidVO;
        }
    };
   
    /* ******************************************** *
     * JAVADOC COMMENTS IN THE INTERFACE PLEASE !!! *
     * ******************************************** */
   
    public String costYearPeriodsAreValidForActivation(int costYearId){
        String validForActivation = null;
        String sql = null;
        sql = "select\n"
            + "   CostYear.id as id,\n"
            + "   CostYear.[name] as name,\n"
            + "   case\n"
            + "       when Gaps.costYearID is not null then\n"
            + "           'form.period.costPeriod.error.gapsBetweenPeriods'\n" // There are gaps between the cost periods.
            + "       when min(CostPeriod.start_date) <> CostYear.start_date then\n"
            + "           'form.period.costPeriod.error.firstPeriodStartDate'\n" // The first cost period does not start on same day as the cost year.
            + "       when MAX(CostPeriod.end_date) <> CostYear.end_date then\n"
            + "           'form.period.costPeriod.error.lastPeriodEndDate'\n" // The last cost period does not end on same day as the cost year.
            + "       when DupeStartDates.costYearID is not null then\n"
            + "           'form.period.costPeriod.error.multipleStartDates'\n" // Multiple cost periods have the same start date.
            + "       when DupeEndDates.costYearID is not null then\n"
            + "           'form.period.costPeriod.error.multipleEndDates'\n" // Multiple cost periods have the same end date.
            + "       else\n"
            + "           'VALID'\n"
            + "   end as valid\n"
            + "from\n"
            + "CostYear\n"
            + "left join CostPeriod on CostPeriod.cost_year_id = CostYear.id\n"
            + "left join(\n"
            + "   select\n"
            + "       CostYear.id as costYearID\n"
            + "   from Costperiod as CostPeriodStart\n"
            + "   left join CostPeriod as CostPeriodEnd on\n"
            + "       dateAdd(day, 1, CostPeriodStart.end_date) = CostPeriodEnd.start_date\n"
            + "       and CostPeriodStart.cost_year_id = CostPeriodEnd.cost_year_id\n"
            + "   inner join CostYear on CostYear.id = CostPeriodStart.cost_year_id\n"
            + "   where CostPeriodStart.end_date <> (\n"
            + "       select max(MaxCostPeriod.end_date)\n"
            + "       from CostPeriod as MaxCostPeriod\n"
            + "       where MaxCostPeriod.cost_year_id = CostPeriodStart.cost_year_id\n"
            + "   )\n"
            + "   and CostPeriodEnd.id is null\n"
            + "   group by CostYear.id, CostPeriodEnd.id\n"
            + ")\n"
            + "as Gaps on Gaps.costYearID = CostYear.id\n"
            + "left join(\n"
            + "   select cost_year_id as CostYearID\n"
            + "   from CostPeriod\n"
            + "   group by cost_year_id, CostPeriod.start_date\n"
            + "   having count(*) > 1\n"
            + ")\n"
            + "as DupeStartDates on DupeStartDates.costYearID = CostYear.id\n"
            + "left join(\n"
            + "   select cost_year_id as costYearID\n"
            + "   from CostPeriod\n"
            + "   group by cost_year_id, CostPeriod.end_date\n"
            + "   having count(*) > 1\n"
            + ")\n"
            + "as DupeEndDates on DupeStartDates.costYearID = CostYear.id\n"
            + "where CostYear.id = '" + costYearId + "'\n"
            + "group by\n"
            + "   CostYear.id,\n"
            + "   CostYear.[name],\n"
            + "   Gaps.costYearID,\n"
            + "   DupeStartDates.costYearID,\n"
            + "   DupeEndDates.costYearID,\n"
            + "   CostYear.start_date,\n"
            + "   CostYear.end_date\n";
        RowMapper mapper = new ValidForActivationRowMapper();
        List results = jdbcTemplate.query(sql, new RowMapperResultReader(mapper));
        if( (results != null) && (!results.isEmpty()) ){
            validForActivation = ((CostYearValidVO)(results.get(0))).getValid();
        }
        return validForActivation;
    }

    // *******************************************
    // *            SPRING  SERVICES             *
    // *******************************************
    // Services are injected automatically by Spring.
    //  ------------------------------------------------------------------------------

    private JdbcTemplate jdbcTemplate;
    /**
     * @spring.property ref = "jdbcTemplate"
     */
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    //  ------------------------------------------------------------------------------
}