/*
 * 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.PartyDao;
import com.bestray.transtracking.dao.mapper.PartyResultSetExtractor;
import com.bestray.transtracking.dto.PartySearchResultDTO;
import com.bestray.trastrack.domain.Address;
import com.bestray.trastrack.domain.Party;
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 user3
 */
public class PartyDaoImpl implements PartyDao{
    
    private DataSource ds;
    private Address address;
    private static final String PARTY_QUERY ="SELECT id,name,billing_name,contact_person,party_type_id,email,address_id,contact_1,contact_2,opening_balance,opening_balance_type,lock_ind from party_master ";
    private static final String ORDER_BY = "order by id desc";
     private static final String PARTY_RESULT_QRY = "select prt.id,prt.name, prt.contact_person,cd.name as code_name, "+
            " prt.opening_balance,prt.lock_ind ,prt.contact_1 "
            + "from party_master prt, code cd where "
            + " prt.party_type_id = cd.id order by prt.id desc";
    private static final String PARTY_NEW_RESULT_QUERY ="SELECT party_master.`id` AS id, "+
  "party_master.`name` AS name,  party_master.`contact_person` AS contact_person, party_master.`contact_1` AS contact_1, "+
               "(SELECT code.`name` FROM `code` WHERE code.`id` = party_master.`party_type_id`) AS 'code_name' , "+
               "party_master.`opening_balance` AS 'opening_balance' , party_master.`lock_ind` AS lock_ind "+
               "FROM `party_master` party_master order by id desc " ;
    public PartyDaoImpl(DataSource ds)throws Exception{
        this.ds = ds;
    }
    
    public void saveParty(Party user)throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "insert into party_master (name, billing_name, contact_person, party_type_id, email, address_id, contact_1, contact_2, opening_balance, opening_balance_type, lock_ind) values(?,?,?,?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql, user.getPartyname(), user.getBillingname(), user.getContactperson(), user.getPartytype_id(), user.getEmail(), user.getPartyaddress()!=null?user.getPartyaddress().getId():null, user.getContact1(), user.getContact2(), user.getOpeningbal1(), user.getOpeningbaltype1(), user.getLock());
     }
    
    public void updateParty(Party user)throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "update party_master set name =? , billing_name=?, contact_person=?, party_type_id=?, email=?, address_id =?, contact_1=?, contact_2=?, opening_balance=?, opening_balance_type=?, lock_ind=? where id = ?";      
        jdbcTemplate.update(sql, new Object[]{user.getPartyname(), user.getBillingname(), user.getContactperson(), user.getPartytype_id(), user.getEmail(), user.getPartyaddress()!=null?user.getPartyaddress().getId():null, user.getContact1(), user.getContact2(), user.getOpeningbal1(), user.getOpeningbaltype1(), user.getLock(), user.getId()});
     }
    
     public List<Party> findAllPartys() throws Exception{
      
         JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
          StringBuffer queryString = new StringBuffer(PARTY_QUERY).append(ORDER_BY);
          List<Party> partyList = (List<Party>) jdbcTemplate.query(queryString.toString(), new PartyResultSetExtractor());
          return partyList;
    }
    
      public Party findPartyRegistraion(Long partyId) throws Exception{
       JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
        StringBuffer queryString = new StringBuffer(PARTY_QUERY)
               .append("where id =? ")
               .append(ORDER_BY);
             List<Party> partyList = (List<Party>) jdbcTemplate.query(queryString.toString(), new Object[]{partyId},  new PartyResultSetExtractor());
             return   partyList.size()>0?partyList.get(0):null;
    }
    
      public void delete(Long partyId) throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         jdbcTemplate.update("DELETE FROM party_master WHERE id = ?", new Object[]{partyId});
    }

    public List<PartySearchResultDTO> findAllPartysList() throws Exception{
         JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        List<PartySearchResultDTO> partyList = (List<PartySearchResultDTO>) jdbcTemplate.query(PARTY_NEW_RESULT_QUERY, new PartySearchResultSetExtractor());
         return partyList;
    }
    
    class PartySearchResultSetExtractor implements  ResultSetExtractor<Object>{ 

        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
             List <PartySearchResultDTO> partylist = new ArrayList<PartySearchResultDTO>();
                   
                   while (rs.next()) {
                       PartySearchResultDTO party = new PartySearchResultDTO();
                       party.setPartyId(rs.getString("id"));
                       party.setPartyName(rs.getString("name"));
                       party.setAccountType(rs.getString("code_name"));
                       party.setContactPerson(rs.getString("contact_person"));
                       party.setContactNumber(rs.getString("contact_1"));
                       party.setOpeningBalance(rs.getString("opening_balance"));
                       party.setLock(rs.getString("lock_ind"));
                       partylist.add(party);
                   }
                   return partylist;
        }
       
   }
}