/*
 * 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.ClientCollectionDao;
import com.bestray.transtracking.dao.mapper.ClientCollectionResultSetExtractor;
import com.bestray.transtracking.dao.mapper.ClientResultSetExtractor;
import com.bestray.transtracking.dao.mapper.EmployeeResultSetExtractor;
import com.bestray.transtracking.dto.ClientCollectionSearchDTO;
import com.bestray.trastrack.domain.ClientCollection;
import com.bestray.trastrack.domain.ClientUser;
import com.bestray.trastrack.domain.CollectionCalcu;
import com.bestray.trastrack.domain.Employee;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;

/**
 *
 * @author user3
 */
public class ClientCollectionDaoImpl implements ClientCollectionDao {

    private DataSource ds;
    private static final String CLIENT_LIST_QUERY ="SELECT * from client_master ";
    private static final String CLIENT_QUERY ="SELECT * from client_payment_entry ";
    private static final String CLIENT_WHERE =" where client_id = ? ";
    private static final String ORDER_BY = " order by id desc ";
    private static final String EMPLOYEE_LIST_QUERY ="SELECT * from employee_master e";
    public ClientCollectionDaoImpl(DataSource ds){
        this.ds=ds;
    }
    
    public List<ClientCollection> getSearchResults(final ClientCollectionSearchDTO search) throws Exception {
        StringBuffer queryString = new StringBuffer(CLIENT_QUERY);
        queryString.append(" where client_id = ?");
        if(search.getSearchFrom()!=null){
            queryString.append(" and payment_date>=?");
        }
        if(search.getSearchTo()!=null){
            queryString.append(" and payment_date<=?");
        }
        queryString.append(ORDER_BY);
          
         
          JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
                   
           // System.out.println(queryString.toString());   
         return (List<ClientCollection>) jdbcTemplate.query(queryString.toString(),  new PreparedStatementSetter() {

            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 0;
                ps.setLong(++index, search.getClientname());
                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 ClientCollectionResultSetExtractor());
    }
    
    public void saveCollection(ClientCollection collect) throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        
        String sql = "insert into client_payment_entry (client_id, collection_type_id,amount,recieve_person_id,diposited_at,comment_1,comment_2,payment_date) values(?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql,collect.getClient_id(), collect.getCollection_type_id(), collect.getAmount(), collect.getRecived_person_id(), collect.getDiposited_at(), collect.getComment1(), collect.getComment2(), collect.getPayment_date());

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

    public ClientCollection findCollectionDetails(Long collectionId)throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        StringBuffer queryString = new StringBuffer(CLIENT_QUERY)
               .append("where id = ? ")
               .append(ORDER_BY);
             List<ClientCollection> collectionList = (List<ClientCollection>) jdbcTemplate.query(queryString.toString(), new Object[]{collectionId}, new ClientCollectionResultSetExtractor());
             return   collectionList.size()>0?collectionList.get(0):null;
       
    }

    public void updateCollection(ClientCollection collect) throws Exception{
       JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
       final java.util.Date date= new java.util.Date();
       String sql = "update client_payment_entry set client_id=?, collection_type_id=?, amount=?, recieve_person_id=?, diposited_at=?, comment_1=?, comment_2=?, payment_date=? where id = ?";      
       jdbcTemplate.update(sql, new Object[]{collect.getClient_id(), collect.getCollection_type_id(), collect.getAmount(), collect.getRecived_person_id(), collect.getDiposited_at(), collect.getComment1(), collect.getComment2(), collect.getPayment_date(), collect.getId()});
    }

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

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

    public CollectionCalcu getTotalPayment(Long clientId) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String queryString = " SELECT client_master.`id` AS id, client_master.`name` AS client_name,"
            +" (select COALESCE(sum(vehicle_job_entry.`total_receivable`),0) "
            +" from vehicle_job_entry where vehicle_job_entry.`client_name_id`=client_master.`id`) AS sum_new_job,"
            +" (select COALESCE(sum(client_payment_entry.`amount`),0)"
            +" from client_payment_entry where client_payment_entry.`client_id`=client_master.`id`) AS sum_client_collection"
            +" FROM `client_master` client_master Where client_master.`id`= ? ";
        List<CollectionCalcu> domainList = (List<CollectionCalcu>) jdbcTemplate.query(queryString.toString(), new Object[]{clientId}, new CollectionDomainResultSetExtractor());
        return domainList.size() > 0 ? domainList.get(0) : null;
    }
    public class CollectionDomainResultSetExtractor implements  ResultSetExtractor<Object>{

    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
        List <CollectionCalcu> payment = new ArrayList<CollectionCalcu>();
        while (rs.next()) {
            CollectionCalcu paymentclient = new CollectionCalcu();
          //  Address address = new Address();
            paymentclient.setId(rs.getLong("id"));
            paymentclient.setClientName(rs.getString("client_name"));
            paymentclient.setSumNewJob(rs.getDouble("sum_new_job"));
            paymentclient.setSumClientCollection(rs.getDouble("sum_client_collection"));
            payment.add(paymentclient);
        }
        return payment;
    }
    }
}