package com.bestray.transtracking.dao.impl;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author user3
 */
import com.bestray.transtracking.dao.ClientDao;
import com.bestray.transtracking.dao.mapper.ClientResultSetExtractor;
import com.bestray.transtracking.dto.ClientSearchResultDTO;
import com.bestray.trastrack.domain.Address;
import com.bestray.trastrack.domain.ClientCollection;
import com.bestray.trastrack.domain.ClientUser;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import java.util.List;
import javax.sql.*;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

public class ClientDaoImpl implements ClientDao{
    
    private DataSource ds;
    private Address address;
    private static final String CLIENT_QUERY ="SELECT id,name,address_id,contact_1,contact_2,contact_3,opening_balance,opening_balance_type,email,lock_ind from client_master ";
   
    private static final String ORDER_BY = "order by id desc";
    private static final String CLIENT_RESULT_QRY = "select clt.id,clt.name,ad.address1,ad.city,"+
            " clt.opening_balance,clt.lock_ind "
            + "from client_master clt, address ad where "
            + " clt.address_id = ad.id order by clt.id desc";
    private static final String CLIENT_NEW_QUERY = "SELECT client_master.`id` AS id, "+
               //"client_master.`address_id` AS client_master_address_id,"+
               "client_master.`name` AS name, "+
               "(SELECT address.`address1` FROM `address` WHERE address.`id` = client_master.`address_id`) "+
               "AS 'address1', (SELECT address.`CITY` FROM `address` WHERE address.`id` = client_master.`address_id`) "+
               "AS 'city', client_master.`opening_balance` AS 'opening_balance' , client_master.`lock_ind` AS lock_ind "+
               "FROM `client_master` client_master order by id desc ";
    public ClientDaoImpl(DataSource ds){
        this.ds=ds;
    }
    
    public void saveClient(ClientUser user)throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        
        String sql = "insert into client_master (name,address_id, contact_1,contact_2,contact_3,opening_balance,opening_balance_type,lock_ind,email) values(?,?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql, user.getClientname(), user.getClientaddress()!=null?user.getClientaddress().getId():null, user.getClientcontact1(), user.getClientcontact2(), user.getClientcontact3(), user.getOpeningbal(), user.getOpeningbaltype(), user.getLock(), user.getEmail_id());
        
    }
    
      public void updateClient(ClientUser user)throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        
        
        
        String sql = "update client_master set name =? ,address_id =?, contact_1=?,contact_2=?,contact_3=?,opening_balance=?,opening_balance_type=?,lock_ind=?,email=? where id = ?";      
        jdbcTemplate.update(sql, new Object[]{user.getClientname(), user.getClientaddress()!=null?user.getClientaddress().getId():null, user.getClientcontact1(), user.getClientcontact2(), user.getClientcontact3(), user.getOpeningbal(), user.getOpeningbaltype(), user.getLock(),user.getEmail_id(),user.getId()});
        
    }


    public List<ClientUser> findAllClients() throws Exception{
      
         JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
            StringBuffer queryString = new StringBuffer(CLIENT_QUERY).append(ORDER_BY);
          List<ClientUser> clientList = (List<ClientUser>) jdbcTemplate.query(queryString.toString(), new ClientResultSetExtractor());
          return clientList;
    }

    public ClientUser findClientRegistraion(Long clientId) throws Exception{
       JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
        StringBuffer queryString = new StringBuffer(CLIENT_QUERY)
               .append("where id =? ")
               .append(ORDER_BY);
             List<ClientUser> clientList = (List<ClientUser>) jdbcTemplate.query(queryString.toString(), new Object[]{clientId},  new ClientResultSetExtractor());
             return   clientList.size()>0?clientList.get(0):null;
       
    }

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

   /* public void saveCollection(ClientCollection collect) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        
        String sql = "insert into client_payment_entry (payment_number,client_id, collection_type_id,amount,recieve_person_id,diposited_at,comment_1,comment_2,payment_date) values(?,?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql, collect.getPayment_id(), collect.getClient_id(), collect.getCollection_type_id(), collect.getAmount(), collect.getRecived_person_id(), collect.getDiposited_at(), collect.getComment1(), collect.getComment2(), collect.getPayment_date());

    }*/

    public ClientUser getClientNameById(Long clientId)throws Exception {
        
        JdbcTemplate template = new JdbcTemplate(ds);
        String query = "Select id,name from client_master where id =?";
        List<ClientUser> cnameList = (List<ClientUser>) template.query(query, new Object[]{clientId}, new ClientResultSetExtractor());
       return cnameList.size()>0?cnameList.get(0):null;
        
    }

    public List<ClientSearchResultDTO> findAllClientsList() throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        List<ClientSearchResultDTO> clientList = (List<ClientSearchResultDTO>) jdbcTemplate.query(CLIENT_NEW_QUERY, new ClientSearchResultSetExtractor());
          return clientList;
    }
    
    class ClientSearchResultSetExtractor implements  ResultSetExtractor<Object>{ 

        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
             List <ClientSearchResultDTO> clientlist = new ArrayList<ClientSearchResultDTO>();
                   
                   while (rs.next()) {
                       ClientSearchResultDTO client = new ClientSearchResultDTO();
                       client.setClientId(rs.getString("id"));
                       client.setClientName(rs.getString("name"));
                       client.setAddress(rs.getString("address1"));
                       client.setCity(rs.getString("city"));
                       client.setOpeningBalance(rs.getString("opening_balance"));
                       client.setLock(rs.getString("lock_ind"));
                       clientlist.add(client);
                   }
                   return clientlist;
        }
       
   }
    
    
}