/*
 * 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.BalanceSheetDao;
import com.bestray.transtracking.dao.mapper.ClientCollectionResultSetExtractor;
import com.bestray.transtracking.dao.mapper.NewJobEntryResulSetExtractor;
import com.bestray.transtracking.dao.mapper.PaymentVoucherResultSetExtractor;
import com.bestray.transtracking.dto.ClientCollectionSearchDTO;
import com.bestray.transtracking.dto.JobEntrySearchDTO;
import com.bestray.transtracking.dto.PaymentVoucherSearchDTO;
import com.bestray.transtracking.dto.VehicleExpenseSearchDTO;
import com.bestray.trastrack.domain.ClientCollection;
import com.bestray.trastrack.domain.JobEntry;
import com.bestray.trastrack.domain.VehicleExpenseDetails;
import com.bestray.trastrack.domain.PaymentVoucher;
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 Bitu
 */
public class BalanceSheetDaoImpl implements BalanceSheetDao {

    private DataSource ds;
    private static final String JOBENTRY_QUERY = "SELECT * from vehicle_job_entry ";
    private static final String JOBENTRY_WHERE = " where vehicle_number_id = ? ";
    private static final String ORDER_BY = " order by id desc ";
    private static final String CLIENT_QUERY = "SELECT * from client_payment_entry ";
    private static final String CLIENT_WHERE = " where client_id = ? ";
    private static final String SEARCH_QUERY_MAINTENANCE = "SELECT expense.vehicle_expense_date 'expenseDate',task.maintenance_name 'taskName',expense.expense_amount 'amount',party_id FROM `vehicle_maintenance_alert` alert INNER JOIN `vehicle_expense_entry` expense ON alert.`id` = expense.`vehicle_maintenance_id` INNER JOIN `task_master` task on alert.maintenance_id = task.id ";
    private static final String SEARCH_QUERY_OILCHANGE = "select  oil.date,'Oil Change',oil.total_price,party_id from oil_entry oil ";
    private static final String PAYMENT_QUERY = "SELECT * from payment_voucher ";
    private static final String PAYMENT_PAYMENT_QUERY = "SELECT coalesce(sum(amount_paid),0.0) from payment_voucher ";
    private static final String SEARCH_JOBENTRY_TOTAL = "SELECT coalesce(sum(tot_payable),0.0) FROM `vehicle_job_entry` ";
    private JdbcTemplate jdbcTemplate;

    public BalanceSheetDaoImpl(DataSource ds) {
        jdbcTemplate = new JdbcTemplate(ds);
    }

    public List<JobEntry> getSearchResults(final JobEntrySearchDTO search) throws Exception {
        StringBuffer queryString = new StringBuffer(JOBENTRY_QUERY);
        queryString.append(" Where client_name_id=?");
        if (search.getSearchFrom() != null) {
            queryString.append(" and job_date>=?");
        }
        if (search.getSearchTo() != null) {
            queryString.append(" and job_date<=?");
        }
        queryString.append(ORDER_BY);


        return (List<JobEntry>) jdbcTemplate.query(queryString.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 0;
                ps.setLong(++index, search.getClientId());
                if (search.getSearchFrom() != null) {
                    ps.setDate(++index, new java.sql.Date(search.getSearchFrom().getTime()));
                }
                if (search.getSearchTo() != null) {
                    ps.setDate(++index, new java.sql.Date(search.getSearchTo().getTime()));
                }
            }
        }, new NewJobEntryResulSetExtractor());
    }

    public List<ClientCollection> getCollectionSearchResults(final ClientCollectionSearchDTO search2) throws Exception {
        StringBuffer queryString = new StringBuffer(CLIENT_QUERY);
        queryString.append(" where client_id = ?");
        if (search2.getSearchFrom() != null) {
            queryString.append(" and payment_date>=?");
        }
        if (search2.getSearchTo() != null) {
            queryString.append(" and payment_date<=?");
        }
        queryString.append(ORDER_BY);

        return (List<ClientCollection>) jdbcTemplate.query(queryString.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 0;
                ps.setLong(++index, search2.getClientname());
                if (search2.getSearchFrom() != null) {
                    ps.setDate(++index, new java.sql.Date(search2.getSearchFrom().getTime()));
                }
                if (search2.getSearchTo() != null) {

                    ps.setDate(++index, new java.sql.Date(search2.getSearchTo().getTime()));
                }
            }
        }, new ClientCollectionResultSetExtractor());
    }

    public List<VehicleExpenseDetails> getExpenseSearchResults(final VehicleExpenseSearchDTO expensesearch) throws Exception {
        StringBuffer queryString = new StringBuffer("(")
                .append(SEARCH_QUERY_MAINTENANCE);
        queryString.append(" WHERE party_id=? ");
        if (expensesearch.getSearchFrom() != null) {
            queryString.append(" and  expense.vehicle_expense_date >=?");
        }
        if (expensesearch.getSearchTo() != null) {
            queryString.append(" and expense.vehicle_expense_date<=?");
        }
        queryString.append(") UNION ALL (")
                .append(SEARCH_QUERY_OILCHANGE)
                .append(" WHERE party_id=? ");
        if (expensesearch.getSearchFrom() != null) {
            queryString.append(" and  date >=?");
        }
        if (expensesearch.getSearchTo() != null) {
            queryString.append(" and date<=?");
        }

        queryString.append(")");
        //  JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);

        //System.out.println(queryString.toString());

        return (List<VehicleExpenseDetails>) jdbcTemplate.query(queryString.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 0;
                ps.setLong(++index, expensesearch.getPartyId());
                if (expensesearch.getSearchFrom() != null) {
                    //System.out.println(expensesearch.getSearchFrom());
                    ps.setDate(++index, new java.sql.Date(expensesearch.getSearchFrom().getTime()));
                }
                if (expensesearch.getSearchTo() != null) {
                   // System.out.println(expensesearch.getSearchTo());
                    ps.setDate(++index, new java.sql.Date(expensesearch.getSearchTo().getTime()));
                }
                ps.setLong(++index, expensesearch.getPartyId());
                if (expensesearch.getSearchFrom() != null) {
                    //System.out.println(expensesearch.getSearchFrom());
                    ps.setDate(++index, new java.sql.Date(expensesearch.getSearchFrom().getTime()));
                }
                if (expensesearch.getSearchTo() != null) {
                   // System.out.println(expensesearch.getSearchTo());
                    ps.setDate(++index, new java.sql.Date(expensesearch.getSearchTo().getTime()));
                }

            }
        }, new VehicleCombinedExpenseResultSetExtractor());
    }

    public List<PaymentVoucher> getPaymentSearchResults(final PaymentVoucherSearchDTO paymentsearch) throws Exception {
        StringBuffer queryString = new StringBuffer(PAYMENT_QUERY);
        queryString.append(" where party_name_id = ?");
        if (paymentsearch.getSearchFrom() != null) {
            queryString.append(" and payment_date>=?");
        }
        if (paymentsearch.getSearchTo() != null) {
            queryString.append(" and payment_date<=?");
        }
        queryString.append(ORDER_BY);



       // 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, paymentsearch.getPartyname());
                if (paymentsearch.getSearchFrom() != null) {
                   // System.out.println(paymentsearch.getSearchFrom());
                    ps.setDate(++index, new java.sql.Date(paymentsearch.getSearchFrom().getTime()));
                }
                if (paymentsearch.getSearchTo() != null) {
                   // System.out.println(paymentsearch.getSearchTo());
                    ps.setDate(++index, new java.sql.Date(paymentsearch.getSearchTo().getTime()));
                }
            }
        }, new PaymentVoucherResultSetExtractor());
    }

    public Double getJobEntryTotalAmount(final JobEntrySearchDTO search) throws Exception {
        StringBuffer queryString = new StringBuffer(SEARCH_JOBENTRY_TOTAL);
        queryString.append(" Where client_name_id=" + search.getClientId());
        if (search.getSearchFrom() != null) {
            queryString.append(" and job_date>= '" + new java.sql.Date(search.getSearchFrom().getTime()) + "'");
        }
        if (search.getSearchTo() != null) {
            queryString.append(" and job_date<= '" + new java.sql.Date(search.getSearchTo().getTime()) + "'");
        }
        queryString.append(ORDER_BY);


        return (Double) jdbcTemplate.queryForObject(queryString.toString(), Double.class);
    }

    public Double getPartyPaymentTotalAmount(final PaymentVoucherSearchDTO search) throws Exception {
        StringBuffer queryString = new StringBuffer(PAYMENT_PAYMENT_QUERY);
        queryString.append(" Where party_name_id=" + search.getPartyname());
        if (search.getSearchFrom() != null) {
            queryString.append(" and job_date>= '" + new java.sql.Date(search.getSearchFrom().getTime()) + "'");
        }
        if (search.getSearchTo() != null) {
            queryString.append(" and job_date<= '" + new java.sql.Date(search.getSearchTo().getTime()) + "'");
        }
        queryString.append(ORDER_BY);


        return (Double) jdbcTemplate.queryForObject(queryString.toString(), Double.class);
    }
}

class VehicleCombinedExpenseResultSetExtractor implements ResultSetExtractor {

    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {

        List<VehicleExpenseDetails> vehicleExpenses = new ArrayList<VehicleExpenseDetails>();

        while (rs.next()) {
            VehicleExpenseDetails vehicleExpense = new VehicleExpenseDetails();
            vehicleExpense.setTaskName(rs.getString("taskName"));
            vehicleExpense.setExpenseDate(rs.getDate("expenseDate"));
            vehicleExpense.setAmount(rs.getDouble("amount"));
            vehicleExpenses.add(vehicleExpense);
        }
        return vehicleExpenses;
    }
}