/*
 * 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.JobEntryDao;
import com.bestray.transtracking.dao.mapper.ClientResultSetExtractor;
import com.bestray.transtracking.dao.mapper.EmployeeResultSetExtractor;
import com.bestray.transtracking.dao.mapper.NewJobEntryResulSetExtractor;
import com.bestray.transtracking.dao.mapper.PartyResultSetExtractor;
import com.bestray.transtracking.dao.mapper.VehicleResultSetExtractor;
import com.bestray.transtracking.dto.JobEntrySearchDTO;
import com.bestray.trastrack.domain.ClientUser;
import com.bestray.trastrack.domain.Employee;
import com.bestray.trastrack.domain.JobEntry;
import com.bestray.trastrack.domain.Party;
import com.bestray.trastrack.domain.Vehicle;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
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.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author User1
 */

public class JobEntryDaoImpl implements JobEntryDao{

    private DataSource ds;
    private static final String JOBENTRY_QUERY ="SELECT * from vehicle_job_entry ";
    private static final String JOBENTRY_WHERE =" where vehicle_number_id = ? ";
    private static final String ORDER_BY = " order by id desc ";
    private static final String VEHICLE_QUERY ="SELECT * from vehicle_master v where v.lock = 'NO'";
    private static final String EMPLOYEE_QUERY = "select * from employee_master e where e.lock = 'NO' ";
    private static final String ALL_EMPLOYEE_QUERY = "select * from employee_master ";
    private static final String ALL_CLIENT_QUERY = "select * from client_master ";
    private static final String ALL_VEHICLE_QUERY = "select * from vehicle_master ";
    private static final String PARTY_QUERY ="SELECT * from party_master ";
    private JdbcTemplate jdbcTemplate;
    
    public JobEntryDaoImpl(DataSource ds){
       jdbcTemplate = new JdbcTemplate(ds);
    }
    
    
    
    /*public void scheduleJob(JobEntry jobentry) throws Exception{
        
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql1 = "INSERT INTO vehicle_job_entry (`job_date`,`vehicle_number_id`,`vehicle_driver_id`,`job_transaction_type`,`client_name_id`,`from_location`,`to_location`,`distance`,`goods_quantity`,`receivable_rate`,`receivable_amount`,`payable_rate`,`payable_amount`,`receivable_exatra_charges`,`payable_extra_charges`,`total_receivable`,`total_payable`,`estimated_profit`,`remarks_1`) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql1, jobentry.getJob_date(), jobentry.getVehicle_number_id(), jobentry.getVehicle_driver_id(), jobentry.getJob_transaction_type(), jobentry.getClient_name_id(), jobentry.getFrom_location(), jobentry.getTo_location(), jobentry.getDistance(), jobentry.getGoods_quantity(), jobentry.getReceivable_rate(), jobentry.getReceivable_amount(), jobentry.getPayable_rate(), jobentry.getPayable_amount(), jobentry.getReceivable_extra_charges(), jobentry.getPayable_exira_charges(), jobentry.getTotal_receivable_amount(), jobentry.getTotal_payable_amount(), jobentry.getEstimated_profit(), jobentry.getRemarks());
        
    }*/

    public List<JobEntry> getSearchResults(final JobEntrySearchDTO search) throws Exception{
       StringBuffer queryString = new StringBuffer(JOBENTRY_QUERY);
        queryString.append(" Where vehicle_number_id=?");
        if(search.getSearchFrom() != null){
             queryString.append(" and job_date>=?");
        }
          if(search.getSearchTo() != null){
             queryString.append(" and job_date<=?");
        }
          queryString.append(ORDER_BY);
          
         //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
                   
            //System.out.println(queryString.toString());   
         return (List<JobEntry>) 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 NewJobEntryResulSetExtractor()); 
        
     
    }

    public JobEntry findJobEntryRegistraion(Long JobId)throws Exception {
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
        StringBuffer queryString = new StringBuffer(JOBENTRY_QUERY)
               .append("where id = ? ")
               .append(ORDER_BY);
             List<JobEntry> JobEntryList = (List<JobEntry>) jdbcTemplate.query(queryString.toString(), new Object[]{JobId}, new NewJobEntryResulSetExtractor());
             return   JobEntryList.size()>0?JobEntryList.get(0):null;
    }

    public Long updateJob(JobEntry jobentry) throws Exception{
        
       // JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        
        String sql = "update vehicle_job_entry set job_date=?, vehicle_number_id=?, vehicle_driver_id=?, job_transaction_type=?, client_name_id=?, from_location=?, to_location=?, distance=?, goods_quantity=?, receivable_rate=?, payable_rate=?, receivable_exatra_charges=?, payable_extra_charges=?, remarks_1=?, receivable_amount=?, payable_amount=?, total_receivable=?, total_payable=?, estimated_profit=? where id = ?";
        jdbcTemplate.update(sql, new Object[]{jobentry.getJob_date(), jobentry.getVehicle_number_id(), jobentry.getVehicle_driver_id(), jobentry.getJob_transaction_type(), jobentry.getClient_name_id(), jobentry.getFrom_location(), jobentry.getTo_location(), jobentry.getDistance(), jobentry.getGoods_quantity(), jobentry.getReceivable_rate(), jobentry.getPayable_rate(), jobentry.getReceivable_extra_charges(), jobentry.getPayable_exira_charges(), jobentry.getRemarks(), jobentry.getReceivable_amount(), jobentry.getPayable_amount(), jobentry.getTotal_receivable_amount(), jobentry.getTotal_payable_amount(), jobentry.getEstimated_profit(), jobentry.getId()});
        return jobentry.getId();
        
    }

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

    public List<JobEntry> getSearchResults2(final JobEntrySearchDTO search) throws Exception{
        StringBuffer queryString = new StringBuffer(JOBENTRY_QUERY);
        queryString.append(" Where client_name_id=?");
        if(search.getSearchFrom() != null){
             queryString.append(" and job_date>=?");
        }
          if(search.getSearchTo() != null){
             queryString.append(" and job_date<=?");
        }
          queryString.append(ORDER_BY);
          
          //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
                      
         return (List<JobEntry>) 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){
                    //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 NewJobEntryResulSetExtractor()); 
        
     
    }

    public List<Vehicle> findVehiclesList() throws Exception {
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
            StringBuffer queryString = new StringBuffer(VEHICLE_QUERY).append(" order by vehicle_number asc ");
          List<Vehicle> vehicleList = (List<Vehicle>) jdbcTemplate.query(queryString.toString(), new VehicleResultSetExtractor());
          return vehicleList;
    }

    public List<Employee> findEmployeesList() 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;
    }

    public List<ClientUser> findAllClientsList() throws Exception {
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
            StringBuffer queryString = new StringBuffer(ALL_CLIENT_QUERY).append("order by name asc");
          List<ClientUser> clientList = (List<ClientUser>) jdbcTemplate.query(queryString.toString(), new ClientResultSetExtractor());
          return clientList;
    }

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

    public List<Vehicle> findAllVehicleList() throws Exception {
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
            StringBuffer queryString = new StringBuffer(ALL_VEHICLE_QUERY).append(" order by vehicle_number asc ");
          List<Vehicle> vehicleList = (List<Vehicle>) jdbcTemplate.query(queryString.toString(), new VehicleResultSetExtractor());
          return vehicleList;
    }
    
    public Long saveVehicleJobEntry(final JobEntry jobEntry) 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 vehicle_job_entry (`job_date`,`vehicle_number_id`,`vehicle_driver_id`,`job_transaction_type`,`client_name_id`,`from_location`,`to_location`,`distance`,`goods_quantity`,`receivable_rate`,`receivable_amount`,`payable_rate`,`payable_amount`,`receivable_exatra_charges`,`payable_extra_charges`,`total_receivable`,`total_payable`,`estimated_profit`,`remarks_1`) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
                    ps.setDate(1, new java.sql.Date(jobEntry.getJob_date().getTime()));
                    ps.setLong(2, jobEntry.getVehicle_number_id());
                    ps.setLong(3, jobEntry.getVehicle_driver_id());
                    ps.setString(4, jobEntry.getJob_transaction_type());
                    ps.setLong(5, jobEntry.getClient_name_id());
                    ps.setString(6, jobEntry.getFrom_location());
                    ps.setString(7, jobEntry.getTo_location());
                    ps.setDouble(8, jobEntry.getDistance());
                    ps.setDouble(9, jobEntry.getGoods_quantity());
                    ps.setDouble(10, jobEntry.getReceivable_rate());
                    ps.setDouble(11, jobEntry.getReceivable_amount());
                    ps.setDouble(12, jobEntry.getPayable_rate());
                    ps.setDouble(13, jobEntry.getPayable_amount());
                    ps.setDouble(14, jobEntry.getReceivable_extra_charges());
                    ps.setDouble(15, jobEntry.getPayable_exira_charges());
                    ps.setDouble(16, jobEntry.getTotal_receivable_amount());
                    ps.setDouble(17, jobEntry.getTotal_payable_amount());
                    ps.setDouble(18, jobEntry.getEstimated_profit());
                    ps.setString(19, jobEntry.getRemarks());
                    return ps;
            
            }
        }, keyHolder);
        Long generatedId = new Long(keyHolder.getKey().longValue());
        
        return generatedId;
    }

    @Override
    public List<Party> findAllPartysNames() throws Exception {
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        StringBuffer queryString = new StringBuffer(PARTY_QUERY).append(" order by name asc ");;
        List<Party> partyList = (List<Party>) jdbcTemplate.query(queryString.toString(), new PartyResultSetExtractor());
        return partyList;
    }
    
}