/*
 * 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.PaymentVoucherDao;
import com.bestray.transtracking.dao.mapper.EmployeeResultSetExtractor;
import com.bestray.transtracking.dao.mapper.PartyResultSetExtractor;
import com.bestray.transtracking.dao.mapper.PaymentVoucherResultSetExtractor;
import com.bestray.transtracking.dao.mapper.ViewResultSetExtractor;
import com.bestray.transtracking.dto.PaymentVoucherSearchDTO;
import com.bestray.trastrack.domain.Employee;
import com.bestray.trastrack.domain.Party;
import com.bestray.trastrack.domain.PaymentVoucher;
import com.bestray.trastrack.domain.ViewDomain;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;

/**
 *
 * @author Bitu
 */
public class PaymentVoucherDaoImpl implements PaymentVoucherDao{
    
    private DataSource ds;
    private static final String PARTY_QUERY ="SELECT * from party_master ";
    private static final String PAYMENT_QUERY ="SELECT * from payment_voucher ";
    private static final String PAYMENT_WHERE =" where party_name_id = ? ";
    private static final String ORDER_BY = " order by id desc ";
    private static final String EMPLOYEE_QUERY ="SELECT * from employee_master e";
    private static final String PARTY_WHERE =" where party_name_id = ? ";
    private static final String OIL_TOTAL =" SELECT sum(total_price) as sum, party_id as party from oil_entry ";
    private static final String OIL_PARTY_WHERE =" where party_id = ? ";
     private static final String PAYMENT_TOTAL =" SELECT sum(amount_paid) as sum, party_name_id as party from payment_voucher ";
    private static final String PAYMENT_PARTY_WHERE =" where party_name_id = ? ";
    private static final String EXPENSE_TOTAL =" SELECT sum(expense_amount) as sumexpense, paid_to from vehicle_expenses_entry ";
    private static final String EXPENSE_PARTY_WHERE =" where paid_to = ? ";
    private JdbcTemplate jdbcTemplate;
    
    public PaymentVoucherDaoImpl(DataSource ds){
        this.ds=ds;
    }
    
    /*public List<Party> findallReceipt() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        StringBuffer queryString = new StringBuffer(PARTY_QUERY).append(ORDER_BY);
        List<Party> partyList = (List<Party>) jdbcTemplate.query(queryString.toString(), new ReceiptVoucherResultSetExtractor());
        return partyList;
    }

    public PaymentVoucher getTotalPartyBalance(Long partyId) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        StringBuffer queryString = new StringBuffer(PAYMENT_QUERY).append(PAYMENT_WHERE).append(ORDER_BY).append("limit 1");
        List<PaymentVoucher> paymentList = (List<PaymentVoucher>) jdbcTemplate.query(queryString.toString(),new Object[] { partyId },new ReceiptVoucherResultSetExtractor());
        return paymentList.size()>0?paymentList.get(0):null;
    }*/
    public List<Employee> findAllEmployees() throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        StringBuffer queryString = new StringBuffer(EMPLOYEE_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 void savePayment(PaymentVoucher payment)throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "insert into payment_voucher (payment_date, party_name_id, employee_name_id, amount_paid, payment_mode, diposited_at, cheque_number, voucher_details) values(?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql, payment.getPayment_date(), payment.getParty_name_id(), payment.getEmployee_name_id(), payment.getAmount_paid(), payment.getPayment_mode(), payment.getDiposited_at(), payment.getCheque_number(), payment.getVoucher_details());

    }

    public List<PaymentVoucher> getSearchResults(final PaymentVoucherSearchDTO search) throws Exception{
        StringBuffer queryString = new StringBuffer(PAYMENT_QUERY);
        queryString.append(" where party_name_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<PaymentVoucher>)jdbcTemplate.query(queryString.toString(),  new PreparedStatementSetter() {

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

    public PaymentVoucher getLastBalance(Long partyId)throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        StringBuffer queryString = new StringBuffer(PAYMENT_QUERY).append(PAYMENT_WHERE).append(ORDER_BY).append("limit 1");
        List<PaymentVoucher> balanceChangeList = (List<PaymentVoucher>) jdbcTemplate.query(queryString.toString(), new Object[]{partyId}, new PaymentVoucherResultSetExtractor());
        return balanceChangeList.size() > 0 ? balanceChangeList.get(0) : null;
    }

    public ViewDomain getTotalOilBalance(Long partyId) throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        //StringBuffer queryString = new StringBuffer(OIL_TOTAL).append(OIL_PARTY_WHERE);
        String sql = "select sum(total_price) as sum , party_id as party from oil_entry where party_id=? ";
        List<ViewDomain> totalOilbalance = (List<ViewDomain>) jdbcTemplate.query(sql.toString(), new Object[]{partyId}, new ViewResultSetExtractor());
        return totalOilbalance.size() > 0 ? totalOilbalance.get(0) : null;
    }

    public ViewDomain getTotalPaymentBalance(Long partyId) throws Exception{
       JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        //StringBuffer queryString = new StringBuffer(OIL_TOTAL).append(OIL_PARTY_WHERE);
        String sql = "select sum(amount_paid) as sum , party_name_id as party from payment_voucher where party_name_id=? ";
        List<ViewDomain> totalpaymentbalance = (List<ViewDomain>) jdbcTemplate.query(sql.toString(), new Object[]{partyId}, new ViewResultSetExtractor());
        return totalpaymentbalance.size() > 0 ? totalpaymentbalance.get(0) : null;
    }

    public ViewDomain getTotalExpenseBalance(Long partyId)throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "select sum(expense_amount) as sum , party_id as party from vehicle_expense_entry where party_id=? ";
        List<ViewDomain> totalpaymentbalance = (List<ViewDomain>) jdbcTemplate.query(sql.toString(), new Object[]{partyId}, new ViewResultSetExtractor());
        return totalpaymentbalance.size() > 0 ? totalpaymentbalance.get(0) : null;
    }

    public PaymentVoucher findById(Long id) throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "SELECT * FROM payment_voucher WHERE id = ?";
                PaymentVoucher oilChange = (PaymentVoucher) jdbcTemplate.queryForObject(
                sql, new Object[]{id},
                new BeanPropertyRowMapper(PaymentVoucher.class));
        return oilChange;
    }

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

    public void updatePayment(PaymentVoucher payment)throws Exception {
       JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
       final java.util.Date date= new java.util.Date();
       String sql = "update payment_voucher set payment_date=?, party_name_id=?, employee_name_id=?, amount_paid=?, payment_mode=?, diposited_at=?, cheque_number=?, voucher_details=?  where id = ?";      
       jdbcTemplate.update(sql, new Object[]{payment.getPayment_date(), payment.getParty_name_id(),  payment.getEmployee_name_id(), payment.getAmount_paid(), payment.getPayment_mode(), payment.getDiposited_at(), payment.getCheque_number(), payment.getVoucher_details(),payment.getId()});
    }

    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;
    }

    public ViewDomain getTotalPayment(Long partyId) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String queryString = " SELECT party_master.`id` AS id, party_master.`name` AS party_name,"
                +" (select COALESCE(sum(oil_entry.`total_price`),0) "
                + "from oil_entry where oil_entry.`party_id`=party_master.`id`) AS sum_oil,"
                + "(select COALESCE(sum(vehicle_expense_entry.`expense_amount`),0)"
                + " from vehicle_expense_entry where vehicle_expense_entry.`party_id`=party_master.`id`) AS sum_expense,"
                + "(select COALESCE(sum(payment_voucher.`amount_paid`),0) "
                + "from payment_voucher where payment_voucher.`party_name_id`=party_master.`id`) AS sum_payment FROM "
                + "`party_master` party_master Where party_master.`id`= ? ";
        List<ViewDomain> domainList = (List<ViewDomain>) jdbcTemplate.query(queryString.toString(), new Object[]{partyId}, new DomainResultSetExtractor());
        return domainList.size() > 0 ? domainList.get(0) : null;
    }
    public class DomainResultSetExtractor implements  ResultSetExtractor<Object>{

    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
        List <ViewDomain> payment = new ArrayList<ViewDomain>();
        while (rs.next()) {
            ViewDomain paymentparty = new ViewDomain();
          //  Address address = new Address();
            paymentparty.setId(rs.getLong("id"));
            paymentparty.setParty_name(rs.getString("party_name"));
            paymentparty.setSum_oil(rs.getDouble("sum_oil"));
            paymentparty.setSum_expense(rs.getDouble("sum_expense"));
            paymentparty.setSum_payment(rs.getDouble("sum_payment"));
            
            payment.add(paymentparty);
        }
        return payment;
    }
    }
    
}