/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bestray.transtracking.dao.impl;

import com.bestray.transporttracking.DataSourceObject;
import com.bestray.transtracking.dao.OilEntryDao;
import com.bestray.transtracking.dao.mapper.ClientCollectionResultSetExtractor;
import com.bestray.transtracking.dao.mapper.ClientResultSetExtractor;
import com.bestray.transtracking.dao.mapper.OilChangeResultSetExtractor;
import com.bestray.transtracking.dao.mapper.PartyResultSetExtractor;
import com.bestray.transtracking.dao.mapper.VehicleResultSetExtractor;
import com.bestray.transtracking.dto.OilChangeSearchDTO;
import com.bestray.transtracking.dto.VehicleSearchResultDTO;
import com.bestray.trastrack.domain.ClientCollection;
import com.bestray.trastrack.domain.ClientUser;
import com.bestray.trastrack.domain.Code;
import com.bestray.trastrack.domain.OilChange;
import com.bestray.trastrack.domain.Party;
import com.bestray.trastrack.domain.Vehicle;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author Girija Sahu
 */
public class OilEntryDaoImpl implements OilEntryDao {

    private DataSource ds;
    private static final String OILCHANGE_QUERY = "SELECT * from oil_entry ";
    private static final String OILCHANGE_WHERE = " where vehicle_id = ? ";
    private static final String PARTY_QUERY = "SELECT * from party_master ";
    private static final String CLIENT_QUERY = " SELECT * from client_master ";
    private static final String ORDER_BY = " order by id desc ";
    private static final String LATEST_OIL_CHANGE = "select max(id) as id,date,vehicle_id,party_id,meter_reading, "
            +" oil_quantity,liter_price,total_price from oil_entry ";
                    
    private JdbcTemplate jdbcTemplate;
    private CodeDaoImpl codeDao;

    public OilEntryDaoImpl(DataSource ds) {
        jdbcTemplate = new JdbcTemplate(ds);
    }

    public Long save(final OilChange oilChange)throws Exception {
        //  JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);

        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement("INSERT INTO oil_entry (`vehicle_id`,`party_id`,`client_id`,`date`,`meter_reading`,`oil_quantity`,`liter_price`,`total_price`,`milage_per_liter`) values(?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
                ps.setLong(1, oilChange.getVehicleId());
                ps.setString(2, oilChange.getPartyId());
                ps.setString(3, oilChange.getClientId());
                ps.setDate(4, new java.sql.Date(oilChange.getDate().getTime()));
                ps.setDouble(5, oilChange.getMeterReading());
                ps.setDouble(6, oilChange.getOilQuantity());
                ps.setDouble(7, oilChange.getLiterPrice());
                ps.setDouble(8, oilChange.getTotalPrice());
                ps.setDouble(9, oilChange.getMilagePerLiter());

                return ps;
            }
        }, keyHolder);
        Long generatedId = new Long(keyHolder.getKey().longValue());

        return generatedId;
    }

    public Vehicle getLastOilChange(Long VehicleId) throws Exception{
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        /*String queryString = "select * from oil_entry  "+
                " where id=(select max(id) from oil_entry where vehicle_id=?) ";*/
       // StringBuffer queryString = new StringBuffer(/*OILCHANGE_QUERY*/LATEST_OIL_CHANGE).append(OILCHANGE_WHERE)/*.append(ORDER_BY)*/.append("limit 1");
        String queryString = " select current_meter_reading , last_oil_change_date, last_oil_vol from vehicle_master where id=? ";
        List<Vehicle> oilChangeList = (List<Vehicle>) jdbcTemplate.query(queryString, new Object[]{VehicleId}, new LastOilChangeResultSetExtractor());
        
        return oilChangeList.size() > 0 ? oilChangeList.get(0) : null;
    }

    public List<OilChange> getSearchResults(final OilChangeSearchDTO search)throws Exception {
        StringBuffer queryString = new StringBuffer(OILCHANGE_QUERY);
        queryString.append(" Where vehicle_id=?");
        if (search.getSearchFrom() != null) {
            queryString.append(" and date>=?");
        }
        if (search.getSearchTo() != null) {
            queryString.append(" and date<=?");
        }
        queryString.append(ORDER_BY);
       
        return (List<OilChange>) jdbcTemplate.query(queryString.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int index =0;
                ps.setLong(++index, search.getVehicleId());
                if (search.getSearchFrom() != null) {
                    ps.setDate(++index, new java.sql.Date(search.getSearchFrom().getTime()));
                }
                if (search.getSearchTo() != null) {
                    ps.setDate(++index, new java.sql.Date(search.getSearchTo().getTime()));
                }
            }
        }, new OilChangeResultSetExtractor());


    }
    
    public List<OilChange> getSearchResults2(final OilChangeSearchDTO search)throws Exception {
        StringBuffer queryString = new StringBuffer(OILCHANGE_QUERY);
        queryString.append(" Where client_id=?");
        if (search.getSearchFrom() != null) {
            queryString.append(" and date>=?");
        }
        if (search.getSearchTo() != null) {
            queryString.append(" and date<=?");
        }
        queryString.append(ORDER_BY);
       
        return (List<OilChange>) jdbcTemplate.query(queryString.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int index =0;
                ps.setLong(++index, search.getClientId());
                if (search.getSearchFrom() != null) {
                    ps.setDate(++index, new java.sql.Date(search.getSearchFrom().getTime()));
                }
                if (search.getSearchTo() != null) {
                    ps.setDate(++index, new java.sql.Date(search.getSearchTo().getTime()));
                }
            }
        }, new OilChangeResultSetExtractor());


    }
    public List<OilChange> getSearchResults3(final OilChangeSearchDTO search)throws Exception {
        StringBuffer queryString = new StringBuffer(OILCHANGE_QUERY);
        queryString.append(" Where party_id=?");
        if (search.getSearchFrom() != null) {
            queryString.append(" and date>=?");
        }
        if (search.getSearchTo() != null) {
            queryString.append(" and date<=?");
        }
        queryString.append(ORDER_BY);
       
        return (List<OilChange>) jdbcTemplate.query(queryString.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int index =0;
                ps.setLong(++index, search.getPartyId());
                if (search.getSearchFrom() != null) {
                    ps.setDate(++index, new java.sql.Date(search.getSearchFrom().getTime()));
                }
                if (search.getSearchTo() != null) {
                    ps.setDate(++index, new java.sql.Date(search.getSearchTo().getTime()));
                }
            }
        }, new OilChangeResultSetExtractor());


    }

    public OilChange findById(Long id) throws Exception {
        String sql = "SELECT * FROM oil_entry WHERE id = ?";
        //     JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        OilChange oilChange = (OilChange) jdbcTemplate.queryForObject(
                sql, new Object[]{id},
                new BeanPropertyRowMapper(OilChange.class));
        return oilChange;
    }

    public Long updateOilChage(OilChange oilChange)throws Exception {
        //    JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "update oil_entry set vehicle_id =? , party_id=?, client_id=?, date=?, meter_reading=?, oil_quantity=?, liter_price =?, total_price=?, milage_per_liter=? where id =?";
        jdbcTemplate.update(sql, new Object[]{oilChange.getVehicleId(), oilChange.getPartyId(),oilChange.getClientId(), oilChange.getDate(), oilChange.getMeterReading(), oilChange.getOilQuantity(), oilChange.getLiterPrice(), oilChange.getTotalPrice(),oilChange.getMilagePerLiter(), oilChange.getId()});
        return oilChange.getId();
    }

    public void delete(Long id)throws Exception {
        //    JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        jdbcTemplate.update("DELETE FROM oil_entry WHERE id = ?", new Object[]{id});
    }

    public List<Party> findAllPartyNames() throws Exception {
          //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
          StringBuffer queryString = new StringBuffer(PARTY_QUERY).append(" where lock_ind='NO' ").append(" order by name asc ");
          List<Party> partyNameList = (List<Party>) jdbcTemplate.query(queryString.toString(), new PartyResultSetExtractor());
          return partyNameList;
    }

    public List<ClientUser> findAllClientNames() throws Exception {
       StringBuffer queryString = new StringBuffer(CLIENT_QUERY).append(" where lock_ind='NO' ").append(" order by name asc ");
          List<ClientUser> clientNameList = (List<ClientUser>) jdbcTemplate.query(queryString.toString(), new ClientResultSetExtractor());
          return clientNameList;
    }

    public void updateVehicleLastChange(Long vehicleId,OilChange oilchange) throws Exception {
        String sql = "update vehicle_master set oil_entry_id=? , last_oil_vol=?,  current_meter_reading=?, last_oil_change_date=? where id =?";
        jdbcTemplate.update(sql, new Object[]{oilchange.getId(),oilchange.getOilQuantity(),oilchange.getMeterReading(),oilchange.getDate(),vehicleId});
    }

    public void saveClientCollection(OilChange oilChange,Long id) throws Exception {
        String sql = "insert into client_payment_entry(client_id,collection_type_id,amount,recieve_person_id,payment_date,oil_entry_id) values(?,?,?,?,?,?) ";
        Long codeid=0L;
        codeDao = new CodeDaoImpl(DataSourceObject.getDataSourceObject());
        List<Code> codeval=  codeDao.searchByCodeValue("COLLECTION_CATAGORY","GAS_CARD");
        for(Code cid:codeval){
            codeid = cid.getId();
            //System.out.println(codeid);
        }
        jdbcTemplate.update(sql, new Object[]{oilChange.getClientId(),codeid,oilChange.getTotalPrice(),oilChange.getRecievePersonId(),oilChange.getDate(),id});
    }

    public List<Vehicle> selectVehicleMaster(Long vehicleId) throws Exception {
        String sql = "SELECT * FROM vehicle_master WHERE id = ?";
        List<Vehicle> vehicle = (List<Vehicle>) jdbcTemplate.query(
                                sql, new Object[]{vehicleId},
                                new VehicleResultSetExtractor());
        return vehicle;
    }

    @Override
    public Long getVehicleOilInfo(Long vehicleid) throws Exception{
        Long oilid=0L;
        String sql = "Select * from vehicle_master where id = ? ";
        List<Vehicle> vehicle = (List<Vehicle>)jdbcTemplate.query(sql, new Object[]{vehicleid}, new VehicleResultSetExtractor());
        for(Vehicle v : vehicle){
            oilid=v.getOilEntryId();
        }
        return oilid;
    }

    @Override
    public List<OilChange> getNewOilInformation(Long vehicleId) throws Exception{
        String sql= " select *from oil_entry where id = (select max(id) from oil_entry where vehicle_id= ?) ";
        List<OilChange> newRecord = (List<OilChange>)jdbcTemplate.query(sql, new Object[]{vehicleId},new OilChangeResultSetExtractor());
        return newRecord;
    }

    @Override
    public void deleteClientCollectionOilRecord(Long oilEntryId) throws Exception{
       jdbcTemplate.update("DELETE FROM client_payment_entry WHERE oil_entry_id = ? ", new Object[]{oilEntryId});
    }

    @Override
    public List<ClientCollection> getRecivePersonID(Long id) {
        String sql = "Select * from client_payment_entry where oil_entry_id = ? ";
        List<ClientCollection> newRecieveId = (List<ClientCollection>)jdbcTemplate.query(sql, new Object[]{id}, new ClientCollectionResultSetExtractor());
          return newRecieveId;     
    }

    @Override
    public void updateClientCollection(OilChange oilChange, Long oilChangeId) {
        String sql = "update client_payment_entry set client_id=?,amount=?,recieve_person_id=?,payment_date=? where oil_entry_id =?";
        jdbcTemplate.update(sql, new Object[]{oilChange.getClientId(),oilChange.getTotalPrice(),oilChange.getRecievePersonId(),oilChange.getDate(),oilChangeId});
    }

    @Override
    public List<ClientCollection> checkForAnyClientCollection(Long oilChangeId) {
        String sql = "Select * from client_payment_entry where oil_entry_id = ? ";
        List<ClientCollection> getAllCollections = (List<ClientCollection>)jdbcTemplate.query(sql, new Object[]{oilChangeId}, new ClientCollectionResultSetExtractor());
          return getAllCollections;     
    }

    
    
    
     class LastOilChangeResultSetExtractor implements  ResultSetExtractor<Object>{ 

        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
             List <Vehicle> vehiclelist = new ArrayList<Vehicle>();
                   
                   while (rs.next()) {
                       Vehicle vehicle = new Vehicle();
                       vehicle.setCurrentMeterReading(rs.getDouble("current_meter_reading"));
                       vehicle.setLastOilChangeDate(rs.getDate("last_oil_change_date"));
                       vehicle.setLastOilVolume(rs.getDouble("last_oil_vol"));
                       vehiclelist.add(vehicle);
                   }
                   return vehiclelist;
        }
       
   }
   
}