/*
 * 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.VehicleExpenseDao;
import com.bestray.transtracking.dao.mapper.PartyResultSetExtractor;
import com.bestray.transtracking.dao.mapper.TaskResulsetExtractor;
import com.bestray.transtracking.dao.mapper.VehicleExpenseResultSetExtractor;
import com.bestray.transtracking.dao.mapper.VehicleResultSetExtractor;
import com.bestray.transtracking.dto.VehicleExpenseSearchDTO;
import com.bestray.trastrack.domain.Party;
import com.bestray.trastrack.domain.Task;
import com.bestray.trastrack.domain.Vehicle;
import com.bestray.trastrack.domain.VehicleExpense;
import java.sql.Connection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author user2
 */
public class VehicleExpenseDaoImpl implements VehicleExpenseDao{
    private DataSource ds;
    private static final String TASK_LIST_QUERY = "SELECT * from task_master t ";
    private static final String PARTY_LIST_QUERY = "SELECT * from party_master ";
    //private static final String PAIDBY_LIST_QUERY = "SELECT * from employee_master t ";
    private static final String SEARCH_QUERY = "SELECT vehicle_expense_entry.*,vehicle_maintenance_alert.vehicle_id FROM `vehicle_maintenance_alert` vehicle_maintenance_alert INNER JOIN `vehicle_expense_entry` vehicle_expense_entry ON vehicle_maintenance_alert.`id` = vehicle_expense_entry.`vehicle_maintenance_id` ";
    private static final String ORDER_BY = " order by id desc ";
    private static final String VEHICLE_LIST_QUERY = "SELECT * from vehicle_master v ";
    private JdbcTemplate jdbcTemplate;
    
    public VehicleExpenseDaoImpl(DataSource ds){
        jdbcTemplate = new JdbcTemplate(ds);
    }

    public Long saveVehicleExpenseData(final VehicleExpense vehicleExpense) throws Exception{
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        // String sql1 = "INSERT INTO vehicle_expenses_entry (`expense_scroll_id`,`expense_date`,`vehicle_id`,`expense_name`,`expense_amount`,`paid_to`,`remarks1`,`remarks2`) values(?,?,?,?,?,?,?,?)";
         // jdbcTemplate.update(sql1, vehicleExpense.getExpense_scroll_id(), vehicleExpense.getExpense_date(), vehicleExpense.getVehicle_id(), vehicleExpense.getExpense_name(), vehicleExpense.getExpense_amount(), vehicleExpense.getPaid_to(), vehicleExpense.getRemarks1(), vehicleExpense.getRemarks2());
        
        //vehicleExpense.setVehicle_maintenance_id(vehicleExpense.getVehicleMaintenance().getId());
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement("INSERT INTO vehicle_expense_entry (`vehicle_expense_date`,`vehicle_maintenance_id`,`party_id`,`expense_amount`,`note_1`,`note_2`) values(?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
               // ps.setLong(1, vehicleExpense.getVehicle_expense_scroll_id());
                ps.setDate(1, new java.sql.Date(vehicleExpense.getExpense_date().getTime()));
                //ps.setLong(3, vehicleExpense.getVehicle_maintenance_id()!=0?vehicleExpense.getVehicle_maintenance_id():0);
                ps.setLong(2, vehicleExpense.getVehicle_maintenance_id());
                ps.setLong(3, vehicleExpense.getParty_id());
                ps.setDouble(4, vehicleExpense.getExpense_amount());
                ps.setString(5, vehicleExpense.getNote_1());
                ps.setString(6, vehicleExpense.getNote_2());
                return ps;
            }
        }, keyHolder);
        Long generatedId = new Long(keyHolder.getKey().longValue());
        
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        /*
        String sql1 = "INSERT INTO vehicle_maintenance_alert (`vehicle_maintenance_scroll`,`alert_setup_date`,`vehicle_id`,`maintenance_id`,`maintenenance_job_date`,`remind_me_on`,`status_id`,`note_1`,`note_2`) values(?,?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql1, vehicleExpense.getVehicleMaintenance().getMaintenance_scroll_id(), vehicleExpense.getVehicleMaintenance().getMaintenance_date(), vehicleExpense.getVehicleMaintenance().getVehicle_id(), vehicleExpense.getVehicleMaintenance().getMaintenance_id(), vehicleExpense.getVehicleMaintenance().getNext_job_date(), vehicleExpense.getVehicleMaintenance().getRemind_me_on(), vehicleExpense.getVehicleMaintenance().getMaintenance_status(), vehicleExpense.getVehicleMaintenance().getNote1(), vehicleExpense.getVehicleMaintenance().getNote2());
        */

        return generatedId;
    }

    public List<Task> findAllMaintenanceName() throws Exception{
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
          StringBuffer queryString = new StringBuffer(TASK_LIST_QUERY).append("where t.lock='NO' ").append(" order by maintenance_name asc ");
          List<Task> nameList = (List<Task>) jdbcTemplate.query(queryString.toString(), new TaskResulsetExtractor());
          return nameList;
    }

    public List<Party> findPartyName() throws Exception{
        //  JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
          
        StringBuffer queryString = new StringBuffer(PARTY_LIST_QUERY).append("where lock_ind='NO' ").append(" order by name asc ");
        List<Party> partyList = (List<Party>) jdbcTemplate.query(queryString.toString(), new PartyResultSetExtractor());
        return partyList;
    }
/*
    public List<Employee> findPaidByName() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
          
        StringBuffer queryString = new StringBuffer(PAIDBY_LIST_QUERY).append("where t.lock='NO' ").append(ORDER_BY);
        List<Employee> paidByList = (List<Employee>) jdbcTemplate.query(queryString.toString(), new EmployeeResultSetExtractor());
        return paidByList;
    }
*/
    public List<VehicleExpense> getSearchResults(final VehicleExpenseSearchDTO search) throws Exception{
         StringBuffer queryString = new StringBuffer(SEARCH_QUERY);
        queryString.append(" WHERE vehicle_maintenance_alert.`vehicle_id`=? ");
        if(search.getSearchFrom() != null){
             queryString.append(" and vehicle_expense_date>=?");
        }
          if(search.getSearchTo() != null){
             queryString.append(" and vehicle_expense_date<=?");
        }
          queryString.append(ORDER_BY);
         
                 //  JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
                   
            //System.out.println(queryString.toString());   
         return (List<VehicleExpense>) 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){
                    //System.out.println(search.getSearchFrom());
                    ps.setDate(++index, new java.sql.Date(search.getSearchFrom().getTime()));
                }
                 if(search.getSearchTo() != null){
                     //System.out.println(search.getSearchTo());
                  ps.setDate(++index, new java.sql.Date(search.getSearchTo().getTime()));
                 }
            }
        } , new VehicleExpenseResultSetExtractor()); 
    }

    public VehicleExpense findVehicleExpenseDetails(Long vehicleExpenseId) throws Exception{
     //   JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
       StringBuffer queryString = new StringBuffer("select * from vehicle_expense_entry ")
               .append("where id = ? ")
               .append(ORDER_BY);
             List<VehicleExpense> vehicleExpenseList = (List<VehicleExpense>) jdbcTemplate.query(queryString.toString(), new Object[]{vehicleExpenseId}, new VehicleExpenseResultSetExtractor());
             return   vehicleExpenseList.size()>0?vehicleExpenseList.get(0):null;
    }

    public Long update(VehicleExpense vehicleExpense) throws Exception{
        // JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
          String sql1 = "update vehicle_expense_entry set vehicle_expense_date=?, vehicle_maintenance_id=?, party_id=?, expense_amount=?, note_1=?, note_2=? where id = ?";
          jdbcTemplate.update(sql1, vehicleExpense.getExpense_date(), vehicleExpense.getVehicle_maintenance_id(), vehicleExpense.getParty_id(), vehicleExpense.getExpense_amount(), vehicleExpense.getNote_1(),vehicleExpense.getNote_2(), vehicleExpense.getId());
         return vehicleExpense.getId();
    }

    public List<Vehicle> findAllVehicles() throws Exception{
       // JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
            StringBuffer queryString = new StringBuffer(VEHICLE_LIST_QUERY).append("where v.lock='NO' ").append(" order by vehicle_number asc ");
          List<Vehicle> VehicleList = (List<Vehicle>) jdbcTemplate.query(queryString.toString(), new VehicleResultSetExtractor());
          return VehicleList;
    }

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