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

import com.bestray.transtracking.dao.VehicleDao;
import com.bestray.transtracking.dao.mapper.EmployeeResultSetExtractor;
import com.bestray.transtracking.dao.mapper.VehicleOwnerResultSetExtractor;
import com.bestray.transtracking.dao.mapper.VehicleResultSetExtractor;
import com.bestray.transtracking.dto.VehicleSearchResultDTO;
import com.bestray.trastrack.domain.Employee;
import com.bestray.trastrack.domain.Vehicle;
import com.bestray.trastrack.domain.VehicleOwner;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

/**
 *
 * @author sumitHome
 */
public class VehicleDaoImpl implements VehicleDao{
    
    private DataSource ds;
    private static final String VEHICLE_QUERY ="SELECT * from vehicle_master ";
    private static final String EMPLOYEE_QUERY = "select * from employee_master e where e.lock = 'NO' ";
    private static final String VEHICLEOWNER_QUERY ="SELECT * from vehicle_owner where lock_ind='NO' ";
    private static final String ORDER_BY = "order by id asc";
    private static final String VEHICLE_RESULT_QRY = "select veh.id,veh.vehicle_name,veh.vehicle_number,"
            +" co.name as catagoryname, vo.name as ownername, emp.emp_name as empname, "
            +" veh.hsd_opening,veh.lock ,veh.last_meter_reading,veh.current_meter_reading,veh.last_oil_change_date"
            +" from vehicle_master veh, code co, vehicle_owner vo, employee_master emp where "
            +" veh.catagory_id=co.id and veh.vehicle_owner=vo.id and veh.vehicle_driver=emp.id order by veh.id desc";

    
    public VehicleDaoImpl(DataSource ds){
        this.ds=ds;
    }

    public void addVehicle(Vehicle vehicle)throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        
        String sql1 = "INSERT INTO vehicle_master (`vehicle_number`,`vehicle_name`,`catagory_id`,`vehicle_owner`,`vehicle_driver`,`hsd_opening`,`lock`,`last_meter_reading`,`current_meter_reading`,`last_oil_change_date` ) values(?,?,?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql1, vehicle.getVehicle_number(), vehicle.getVehicle_name(), vehicle.getCatagoryCodeId(), vehicle.getOwner_name(), vehicle.getDriver_name(), vehicle.getHsd_opening(), vehicle.getLock(),vehicle.getLastMeterReading(),vehicle.getCurrentMeterReading(),vehicle.getLastOilChangeDate());
        
    }

    public List<Vehicle> findAllVehicles() throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
            StringBuffer queryString = new StringBuffer(VEHICLE_QUERY).append(ORDER_BY);
          List<Vehicle> vehicleList = (List<Vehicle>) jdbcTemplate.query(queryString.toString(), new VehicleResultSetExtractor());
          return vehicleList;
    }

    public Vehicle findVehicleRegistraion(Long vehicleId) throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
        
        StringBuffer queryString = new StringBuffer(VEHICLE_QUERY)
               .append("where id = ? ")
               .append(ORDER_BY);
             List<Vehicle> vehicleList = (List<Vehicle>) jdbcTemplate.query(queryString.toString(), new Object[]{vehicleId}, new VehicleResultSetExtractor());
             return   vehicleList.size()>0?vehicleList.get(0):null;
    }

    public void updateVehicle(Vehicle vehicle)throws Exception {
        
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);       
        
        String sql = "update vehicle_master set `vehicle_number` =? ,`vehicle_name` =?,`catagory_id` =?,`vehicle_owner` =?,`vehicle_driver` =?,`hsd_opening` =?,`lock` =?,`last_meter_reading`=?,`current_meter_reading`=?,`last_oil_change_date`=? where id = ? ";      
        jdbcTemplate.update(sql, new Object[]{vehicle.getVehicle_number(), vehicle.getVehicle_name(), vehicle.getCatagoryCodeId(), vehicle.getOwner_name(), vehicle.getDriver_name(), vehicle.getHsd_opening(), vehicle.getLock(),vehicle.getLastMeterReading(),vehicle.getCurrentMeterReading(),vehicle.getLastOilChangeDate(),vehicle.getId()});
        
    }

    public void delete(Long vehicleId) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         jdbcTemplate.update("DELETE FROM vehicle_master WHERE id = ?", new Object[]{vehicleId});
    }
    
    public Vehicle getVehicleNumberById(Long vehicleId)throws Exception{
    
        JdbcTemplate template = new JdbcTemplate(ds);
        String query = "Select id,vehicle_number from vehicle_master where id =?";
        List<Vehicle> vnumberList = (List<Vehicle>) template.query(query, new Object[]{vehicleId}, new VehicleResultSetExtractor());
       return vnumberList.size()>0?vnumberList.get(0):null;
    
    }

    public List<VehicleSearchResultDTO> findAllVehiclesList() throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        List<VehicleSearchResultDTO> vehicleList = (List<VehicleSearchResultDTO>) jdbcTemplate.query(VEHICLE_RESULT_QRY, new VehicleSearchResultSetExtractor());
          return vehicleList;
    }

    public List<VehicleOwner> findAllOwners() throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
            StringBuffer queryString = new StringBuffer(VEHICLEOWNER_QUERY).append("order by name asc");
          List<VehicleOwner> vehicleownerList = (List<VehicleOwner>) jdbcTemplate.query(queryString.toString(), new VehicleOwnerResultSetExtractor());
          return vehicleownerList;
    }

    public List<Employee> findAllEmployees() throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
            StringBuffer queryString = new StringBuffer(EMPLOYEE_QUERY).append("order by emp_name asc");
          List<Employee> employeeList = (List<Employee>) jdbcTemplate.query(queryString.toString(), new EmployeeResultSetExtractor());
          return employeeList;
          
    }

   
    
    class VehicleSearchResultSetExtractor implements  ResultSetExtractor<Object>{ 

        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
             List <VehicleSearchResultDTO> vehiclelist = new ArrayList<VehicleSearchResultDTO>();
                   
                   while (rs.next()) {
                       VehicleSearchResultDTO vehicle = new VehicleSearchResultDTO();
                       vehicle.setVehicleId(rs.getString("id"));
                       vehicle.setVehicleName(rs.getString("vehicle_name"));
                       vehicle.setVehicleNumber(rs.getString("vehicle_number"));
                       vehicle.setVehicleOwner(rs.getString("ownername"));
                        vehicle.setVehicleDriver(rs.getString("empname"));
                       vehicle.setCatagory(rs.getString("catagoryname"));
                       vehicle.setHsdopening(rs.getString("hsd_opening"));
                       vehicle.setLock(rs.getString("lock"));
                       vehicle.setLastMeterReading(rs.getString("last_meter_reading"));
                       vehicle.setCurrentMeterReading(rs.getString("current_meter_reading"));
                       vehicle.setLastOilChangeDate(rs.getString("last_oil_change_date"));
                       vehiclelist.add(vehicle);
                   }
                   return vehiclelist;
        }
       
   }
}