/*
 * 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.OwnerPaymentDao;
import com.bestray.transtracking.dao.mapper.OwnerPaymentResultsetExtractor;
import com.bestray.transtracking.dao.mapper.OwnerResultSetExtractor;
import com.bestray.transtracking.dto.OwnerPaymentSearchDTO;
import com.bestray.trastrack.domain.OwnerPayment;
import com.bestray.trastrack.domain.OwnerPaymentBalanceSum;
import com.bestray.trastrack.domain.VehicleOwner;
import java.sql.PreparedStatement;
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.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;

/**
 *
 * @author User1
 */
public class OwnerPaymentDaoImpl implements OwnerPaymentDao{
    
    private JdbcTemplate jdbcTemplate;
    private static final String VEHICLE_OWNER_QUERY = "SELECT distinct(vehicle_owner.`name`) AS name,vehicle_owner.`id` AS id, code.`name` AS code_name FROM `vehicle_owner` vehicle_owner INNER JOIN `vehicle_master` vehicle_master ON vehicle_owner.`id` = vehicle_master.`vehicle_owner` INNER JOIN `code` code ON vehicle_master.`catagory_id` = code.`id` WHERE code.`name`='Rented Vehicle' ";
    private static final String ORDER_BY = " order by id desc ";
    private static final String CLIENT_QUERY ="SELECT * from t_vehicle_owner_payment ";
    
    public OwnerPaymentDaoImpl(DataSource ds) {
        jdbcTemplate = new JdbcTemplate(ds);
    }

    @Override
    public List<VehicleOwner> findVehicleOwnerList() throws Exception {
        StringBuffer queryString = new StringBuffer(VEHICLE_OWNER_QUERY);
          List<VehicleOwner> vehicleOwnerList = (List<VehicleOwner>) jdbcTemplate.query(queryString.toString(), new OwnerResultSetExtractor());
          return vehicleOwnerList;
    }
 

   @Override
    public void updatePayment(OwnerPayment ownerPayment) throws Exception {
        String sql = "update t_vehicle_owner_payment set pay_date=?, vehicle_owner_id=?, amount=?, payment_mode=?, paidby_id=?, remarks=? where id = ?";      
       jdbcTemplate.update(sql, new Object[]{ownerPayment.getPayment_date(), ownerPayment.getOwner_id(), ownerPayment.getAmount(), ownerPayment.getPayment_mode_id(), ownerPayment.getPaid_by_id(), ownerPayment.getRemarks(), ownerPayment.getId()});
       
    }
    @Override
    public void savePayment(OwnerPayment ownerPayment) throws Exception {
        String sql = "insert into t_vehicle_owner_payment (pay_date, vehicle_owner_id, amount, payment_mode, paidby_id, remarks) values(?,?,?,?,?,?)";      
        jdbcTemplate.update(sql,ownerPayment.getPayment_date(), ownerPayment.getOwner_id(), ownerPayment.getAmount(), ownerPayment.getPayment_mode_id(), ownerPayment.getPaid_by_id(), ownerPayment.getRemarks());
    }

    @Override
    public void deletePayment(Long valueOf) throws Exception {
        jdbcTemplate.update("DELETE FROM t_vehicle_owner_payment WHERE id = ?", new Object[]{valueOf});
    }
    
    @Override
    public OwnerPaymentBalanceSum getBalanceSum(Long ownerId) throws Exception {
        String queryString = " SELECT "
            +" COALESCE(sum(vehicle_job_entry.`total_payable`),0) AS sumbalance, "
            +" code.`name` AS codename, "
            +" vehicle_owner.`id` AS id, "
            +" vehicle_owner.`name` "
            +" FROM "
                +" `vehicle_master` vehicle_master INNER JOIN `vehicle_job_entry` vehicle_job_entry ON vehicle_master.`id` = vehicle_job_entry.`vehicle_number_id` "
                +" INNER JOIN `code` code ON vehicle_master.`catagory_id` = code.`id` "
                +" INNER JOIN `vehicle_owner` vehicle_owner ON vehicle_master.`vehicle_owner` = vehicle_owner.`id` "
                +" WHERE "
                +" vehicle_owner.`id` = ? "
                +" and code.`name` = 'Rented Vehicle' ";
        List<OwnerPaymentBalanceSum> domainList = (List<OwnerPaymentBalanceSum>) jdbcTemplate.query(queryString.toString(), new Object[]{ownerId}, new SumBalanceDomainResultSetExtractor());
        return domainList.size() > 0 ? domainList.get(0) : null;
    }

    @Override
    public List<OwnerPayment> getSearchResults(final OwnerPaymentSearchDTO search) throws Exception{
        StringBuffer queryString = new StringBuffer(CLIENT_QUERY);
        queryString.append(" where vehicle_owner_id = ?");
        if(search.getSearchFrom()!=null){
            queryString.append(" and pay_date>=?");
        }
        if(search.getSearchTo()!=null){
            queryString.append(" and pay_date<=?");
        }
        queryString.append(ORDER_BY);
            
         return (List<OwnerPayment>) jdbcTemplate.query(queryString.toString(),  new PreparedStatementSetter() {

            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 0;
                ps.setLong(++index, search.getVehicleOwnerName());
                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 OwnerPaymentResultsetExtractor());
    }

    @Override
    public OwnerPayment findPaymentDetails(Long valueOf) throws Exception {
        StringBuffer queryString = new StringBuffer(CLIENT_QUERY)
               .append("where id = ? ")
               .append(ORDER_BY);
             List<OwnerPayment> collectionList = (List<OwnerPayment>) jdbcTemplate.query(queryString.toString(), new Object[]{valueOf}, new OwnerPaymentResultsetExtractor());
             return   collectionList.size()>0?collectionList.get(0):null;
    }
    
    @Override
        public OwnerPaymentBalanceSum getPaymentBalanceSum(Long ownerId) throws Exception {
            String queryString = " SELECT "
            +" vehicle_owner.`id` AS id, "
            +" vehicle_owner.`name` AS name, "
            +" COALESCE(sum(t_vehicle_owner_payment.`amount`),0) AS sumpaidbalance "
            +" FROM "
            +" `vehicle_owner` vehicle_owner INNER JOIN `t_vehicle_owner_payment` t_vehicle_owner_payment ON vehicle_owner.`id` = t_vehicle_owner_payment.`vehicle_owner_id` "
            +" WHERE "
            +" vehicle_owner.`id`= ? ";
            List<OwnerPaymentBalanceSum> domainList = (List<OwnerPaymentBalanceSum>) jdbcTemplate.query(queryString.toString(), new Object[]{ownerId}, new SumPaidBalanceDomainResultSetExtractor());
            return domainList.size() > 0 ? domainList.get(0) : null;
        }

    
    public class SumBalanceDomainResultSetExtractor implements  ResultSetExtractor<Object>{

    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
        List <OwnerPaymentBalanceSum> payment = new ArrayList<OwnerPaymentBalanceSum>();
        while (rs.next()) {
            OwnerPaymentBalanceSum paymentowner = new OwnerPaymentBalanceSum();
          //  Address address = new Address();
            paymentowner.setId(rs.getLong("id"));
            paymentowner.setOwnerName(rs.getString("name"));
            paymentowner.setSumBalance(rs.getDouble("sumbalance"));
            paymentowner.setCodename(rs.getString("codename"));
            payment.add(paymentowner);
        }
        return payment;
    }
    }
    
        public class SumPaidBalanceDomainResultSetExtractor implements ResultSetExtractor<Object>{

            public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            List <OwnerPaymentBalanceSum> payment = new ArrayList<OwnerPaymentBalanceSum>();
            while (rs.next()) {
            OwnerPaymentBalanceSum paymentowner = new OwnerPaymentBalanceSum();
            paymentowner.setId(rs.getLong("id"));
            paymentowner.setOwnerName(rs.getString("name"));
            paymentowner.setSumBalance(rs.getDouble("sumpaidbalance"));
            payment.add(paymentowner);
            }
            return payment;
            }
          }

  }