/*
 * 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.ReceiptVoucherDao;
import com.bestray.transtracking.dao.mapper.EmployeeResultSetExtractor;
import com.bestray.transtracking.dao.mapper.LoanCalcResultSetExtractor;
import com.bestray.transtracking.dao.mapper.LoanResultSetExtractor;
import com.bestray.transtracking.dao.mapper.PaidLoanResultSetExtractor;
import com.bestray.transtracking.dao.mapper.PartyResultSetExtractor;
import com.bestray.transtracking.dao.mapper.ReceiptVoucherResultSetExtractor;
import com.bestray.transtracking.dao.mapper.ReceivedLoanResultSetExtractor;
import com.bestray.transtracking.dto.LoanMaintenanceSearchDTO;
import com.bestray.trastrack.domain.Employee;
import com.bestray.trastrack.domain.LoanCalc;
import com.bestray.trastrack.domain.LoanList;
import com.bestray.trastrack.domain.LoanPaid;
import com.bestray.trastrack.domain.LoanReceiving;
import com.bestray.trastrack.domain.Party;
import com.bestray.trastrack.domain.ReceiptVoucher;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author user3
 */
public class ReceiptVoucherDaoImpl implements ReceiptVoucherDao{
    
    private DataSource ds;
    private static final String PARTY_QUERY ="SELECT * from party_master ";
    private static final String EMPLOYEE_QUERY ="SELECT * from employee_master e";
    private static final String ORDER_BY = " order by id desc ";
    private static final String PAYMENT_QUERY ="SELECT * from receipt_voucher_entry ";
    private static final String PAYMENT_WHERE =" where id = ? ";
    private static final String LOAN_QUERY = "select max(id) as id, name, address_id  from "+
            " (select id,name,address_id  from client_master union select id, name, address_id from party_master union select id, emp_name, prsnt_address_id"+
            " from employee_master) ALL_NAME_ID ";
    private static final String PAID_QUERY = "Select * from t_loan_paid ";
    private static final String RECEIVED_QUERY = "Select * from t_loan_received ";
    
    public ReceiptVoucherDaoImpl(DataSource ds){
        this.ds=ds;
    }

    public List<Party> findAllPartys()throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        StringBuffer queryString = new StringBuffer(PARTY_QUERY).append("where lock_ind='NO' ").append(" order by name asc ");;
        List<Party> partyList = (List<Party>) jdbcTemplate.query(queryString.toString(), new PartyResultSetExtractor());
        return partyList;
    }

    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 saveReceiptVoucher(ReceiptVoucher receipt)throws Exception {
       JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
       String sql = "insert into receipt_voucher_entry (date, party_name, party_current_balance, collected_by, receipt_mode, amount_taken, company_current_balance, diposited_at, voucher_details) values(?,?,?,?,?,?,?,?,?)";      
       jdbcTemplate.update(sql, receipt.getVoucherdate(), receipt.getParty_name(), receipt.getParty_current_balance(), receipt.getCollected_by(), receipt.getReceipt_mode(), receipt.getAmount_taken(), receipt.getCompany_current_balance(), receipt.getDiposited_at(), receipt.getVoucher_details());
    }
    
    public ReceiptVoucher getTotalPartyBalance(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<ReceiptVoucher> paymentList = (List<ReceiptVoucher>) jdbcTemplate.query(queryString.toString(),new Object[] { partyId },new ReceiptVoucherResultSetExtractor());
        return paymentList.size()>0?paymentList.get(0):null;
    }

    public List<LoanList> findAllLoanNames() throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        StringBuffer queryString = new StringBuffer(LOAN_QUERY)
                .append(" group by name ").append(" order by name asc ");
                //.append(ORDER_BY);
        List<LoanList> loanList = (List<LoanList>) jdbcTemplate.query(queryString.toString(), new LoanResultSetExtractor());
        return loanList;
    }

    public LoanCalc getTotalLoanBalance(Long loanId) throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String Sql = "SELECT `Loan_Received_from_id`,Sum(`Loan_Amount`) as Loan_received, ifnull(A.LOAN_PAID_AMOUNT,0) "+
                " as LOAN_PAID_AMOUNT, Sum(`Loan_Amount`) - ifnull(A.LOAN_PAID_AMOUNT,0) "+
                " AS Remaining_balance from `t_loan_received` LEFT JOIN "+
                " (SELECT `Loan_Paid_to_id` ID,SUM(`Loan_paid_Amount`) AS LOAN_PAID_AMOUNT from `t_loan_paid` "+
                " group by Loan_Paid_to_id) AS A ON Loan_Received_from_id = A.ID where Loan_Received_from_id=? "+
                " group by `Loan_Received_from_id` ";
        List<LoanCalc> totalLoanbalance = (List<LoanCalc>) jdbcTemplate.query(Sql.toString(), new Object[]{loanId}, new LoanCalcResultSetExtractor());
        return totalLoanbalance.size() > 0 ? totalLoanbalance.get(0) : null;
    }

    public Long saveLoanPayment(final LoanPaid paying) throws Exception{
        /*JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "insert into t_loan_paid (loan_paid_voucher_number, loan_Paid_date, Loan_Paid_to_id, Loan_paid_Amount, Loan_Paid_BY, Loan_Paid_Mode, Loan_Paid_Mode_Diposited_at, Loan_paid_mode_cheque_number, Loan_Voucher_details) values(?,?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql, paying.getLoan_paying_number(),paying.getLoan_paid_date(),paying.getLoan_paid_to_id(),paying.getLoan_paid_amount(),paying.getLoan_paid_by(),paying.getLoan_paid_mode(),paying.getLoan_paid_mode_diposited_at(),paying.getLoan_paid_mode_cheque_number(),paying.getLoan_voucher_details());*/
        KeyHolder keyHolder = new GeneratedKeyHolder();
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement("insert into t_loan_paid (loan_Paid_date, Loan_Paid_to_id, paid_to_type, Loan_paid_Amount, Loan_Paid_BY, Loan_Paid_Mode, Loan_Paid_Mode_Diposited_at, Loan_paid_mode_cheque_number, Loan_Voucher_details,paid_to_name) values(?,?,?,?,?,?,?,?,?,?) ", Statement.RETURN_GENERATED_KEYS);
                //ps.setLong(1, paying.getLoan_paying_voucher_number());
                ps.setDate(1, new java.sql.Date(paying.getLoan_paid_date().getTime()));
                ps.setLong(2, paying.getLoan_paid_to_id());
                ps.setString(3, paying.getPaidToType());
                ps.setDouble(4, paying.getLoan_paid_amount());
                ps.setLong(5, paying.getLoan_paid_by());
                ps.setLong(6, paying.getLoan_paid_mode());
                ps.setString(7, paying.getLoan_paid_mode_diposited_at());
                ps.setString(8, paying.getLoan_paid_mode_cheque_number());
                ps.setString(9, paying.getLoan_voucher_details());
                ps.setString(10, paying.getPaidToName());
                return ps;
            }
        }, keyHolder);
        Long generatedId = new Long(keyHolder.getKey().longValue());

        return generatedId;
    }

    public Long saveLoanReceived(final LoanReceiving receiving) throws Exception{
        /*JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "insert into t_loan_received (loan_received_voucher_number, loan_received_date, Loan_Received_from_id, Loan_Amount, Loan_Received_BY, Loan_Received_Mode, Loan_Received_Mode_Deposited_at, Loan_Recieved_Mode_Cheque_number, Loan_Voucher_details) values(?,?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql, receiving.getLoan_received_number(),receiving.getLoan_received_date(),receiving.getLoan_received_from_id(),receiving.getLoan_received_amount(),receiving.getLoan_received_by(),receiving.getLoan_received_mode(),receiving.getLoan_received_mode_diposited_at(),receiving.getLoan_received_mode_cheque_number(),receiving.getLoan_received_voucher_details());*/
        KeyHolder keyHolder = new GeneratedKeyHolder();
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement("insert into t_loan_received (loan_received_date, loan_received_from_id, received_from_type, loan_amount, loan_received_by, loan_received_Mode, loan_received_mode_deposited_at, loan_received_mode_cheque_number, loan_voucher_details,received_from_name) values(?,?,?,?,?,?,?,?,?,?) ", Statement.RETURN_GENERATED_KEYS);
               // ps.setLong(1, receiving.getLoanReceivedVoucherNumber());
                ps.setDate(1, new java.sql.Date(receiving.getLoanReceivedDate().getTime()));
                ps.setLong(2, receiving.getLoanReceivedFromId());
                ps.setString(3, receiving.getReceivedFromType());
                ps.setDouble(4, receiving.getLoanAmount());
                ps.setLong(5, receiving.getLoanReceivedBy());
                ps.setLong(6, receiving.getLoanReceivedMode());
                ps.setString(7, receiving.getLoanReceivedModeDepositedAt());
                ps.setString(8, receiving.getLoanReceivedModeChequeNumber());
                ps.setString(9, receiving.getLoanVoucherDetails());
                ps.setString(10, receiving.getReceivedFromName());
                return ps;
            }
        }, keyHolder);
        Long generatedId = new Long(keyHolder.getKey().longValue());

        return generatedId;
    }

    public List<LoanPaid> getSearchResults(final LoanMaintenanceSearchDTO searchpaid) throws Exception{
        StringBuffer queryString = new StringBuffer(PAID_QUERY);
        queryString.append(" where Loan_Paid_to_id = ?");
        queryString.append(" and paid_to_type = ?");
        if(searchpaid.getSearchFrom()!=null){
            queryString.append(" and loan_Paid_date>=?");
        }
        if(searchpaid.getSearchTo()!=null){
            queryString.append(" and loan_Paid_date<=?");
        }
        //queryString.append(ORDER_BY);
          
         
          JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
                   
            //System.out.println(queryString.toString());   
         return (List<LoanPaid>)jdbcTemplate.query(queryString.toString(),  new PreparedStatementSetter() {

            public void setValues(PreparedStatement ps) throws SQLException {
               int index =0;
                ps.setLong(++index, searchpaid.getName());
                ps.setString(++index, searchpaid.getType());
                if(searchpaid.getSearchFrom() != null){
                    //System.out.println(searchpaid.getSearchFrom());
                    ps.setDate(++index, new java.sql.Date(searchpaid.getSearchFrom().getTime()));
                }
                 if(searchpaid.getSearchTo() != null){
                     //System.out.println(searchpaid.getSearchTo());
                  ps.setDate(++index, new java.sql.Date(searchpaid.getSearchTo().getTime()));
                 }
            }
        } , new PaidLoanResultSetExtractor());
    }

    public List<LoanReceiving> getSearchReceivedResults(final LoanMaintenanceSearchDTO searchrecieved) throws Exception{
        StringBuffer queryString = new StringBuffer(RECEIVED_QUERY);
        queryString.append(" where Loan_Received_from_id = ?");
        queryString.append(" and received_from_type = ?");
        if(searchrecieved.getSearchFrom()!=null){
            queryString.append(" and loan_received_date>=?");
        }
        if(searchrecieved.getSearchTo()!=null){
            queryString.append(" and loan_received_date<=?");
        }
        //queryString.append(ORDER_BY);
          
         
          JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
                   
            //System.out.println(queryString.toString());   
         return (List<LoanReceiving>)jdbcTemplate.query(queryString.toString(),  new PreparedStatementSetter() {

            public void setValues(PreparedStatement ps) throws SQLException {
                int index =0;
                ps.setLong(++index, searchrecieved.getName());
                ps.setString(++index, searchrecieved.getType());
                System.out.println(searchrecieved.getType());
                if(searchrecieved.getSearchFrom() != null){
                    //System.out.println(searchrecieved.getSearchFrom());
                    ps.setDate(++index, new java.sql.Date(searchrecieved.getSearchFrom().getTime()));
                }
                 if(searchrecieved.getSearchTo() != null){
                     //System.out.println(searchrecieved.getSearchTo());
                  ps.setDate(++index, new java.sql.Date(searchrecieved.getSearchTo().getTime()));
                 }
            }
        } , new ReceivedLoanResultSetExtractor());
    }
    
    public List<LoanPaid> getSearchPaidResultsDateRange(final LoanMaintenanceSearchDTO searchpaid) throws Exception{
        StringBuffer queryString = new StringBuffer(PAID_QUERY);
        if(searchpaid.getSearchFrom()!=null && searchpaid.getSearchTo()!=null){
            queryString.append(" where loan_Paid_date>=? and loan_Paid_date<=?");
        }else if(searchpaid.getSearchFrom()!=null){
            queryString.append(" where loan_Paid_date>=?");
        }else if(searchpaid.getSearchTo()!=null){
            queryString.append(" where loan_Paid_date<=?");
        }
        
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
                   
            //System.out.println(queryString.toString());   
         return (List<LoanPaid>)jdbcTemplate.query(queryString.toString(),  new PreparedStatementSetter() {

            public void setValues(PreparedStatement ps) throws SQLException {
               int index =0;
                if(searchpaid.getSearchFrom() != null && searchpaid.getSearchTo() != null){
                    ps.setDate(++index, new java.sql.Date(searchpaid.getSearchFrom().getTime()));
                    ps.setDate(++index, new java.sql.Date(searchpaid.getSearchTo().getTime()));
                }else if(searchpaid.getSearchFrom() != null){
                    ps.setDate(++index, new java.sql.Date(searchpaid.getSearchFrom().getTime()));
                }else if(searchpaid.getSearchTo() != null){
                    ps.setDate(++index, new java.sql.Date(searchpaid.getSearchTo().getTime()));
                }
                
            }
        } , new PaidLoanResultSetExtractor());
    }

    public List<LoanReceiving> getSearchReceivedResultsDateRange(final LoanMaintenanceSearchDTO searchrecieved) throws Exception{
        StringBuffer queryString = new StringBuffer(RECEIVED_QUERY);
        if(searchrecieved.getSearchFrom()!=null && searchrecieved.getSearchTo()!=null){
            queryString.append(" where loan_received_date>=? and loan_received_date<=?");
        }else if(searchrecieved.getSearchFrom()!=null){
            queryString.append(" where loan_received_date>=?");
        }else if(searchrecieved.getSearchTo()!=null){
            queryString.append(" where loan_received_date<=?");
        }
        
          JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
                   
         return (List<LoanReceiving>)jdbcTemplate.query(queryString.toString(),  new PreparedStatementSetter() {

            public void setValues(PreparedStatement ps) throws SQLException {
                int index =0;
                if(searchrecieved.getSearchFrom() != null && searchrecieved.getSearchTo() != null){
                    ps.setDate(++index, new java.sql.Date(searchrecieved.getSearchFrom().getTime()));
                    ps.setDate(++index, new java.sql.Date(searchrecieved.getSearchTo().getTime()));
                }else if(searchrecieved.getSearchFrom() != null){
                    ps.setDate(++index, new java.sql.Date(searchrecieved.getSearchFrom().getTime()));
                }else if(searchrecieved.getSearchTo() != null){
                    ps.setDate(++index, new java.sql.Date(searchrecieved.getSearchTo().getTime()));
                }
            }
        } , new ReceivedLoanResultSetExtractor());
    }

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

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

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

    public Long updateLoanReceived(LoanReceiving receiving) throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "update t_loan_received set loan_received_date=?, loan_received_from_id=?, received_from_type=?, loan_amount=?, loan_received_by=?, loan_received_Mode=?, loan_received_mode_deposited_at=?, loan_received_mode_cheque_number=?, loan_voucher_details=? where id = ? ";
        jdbcTemplate.update(sql, new Object[]{receiving.getLoanReceivedDate(),receiving.getLoanReceivedFromId(),receiving.getReceivedFromType(),receiving.getLoanAmount(),receiving.getLoanReceivedBy(),receiving.getLoanReceivedMode(),receiving.getLoanReceivedModeDepositedAt(),receiving.getLoanReceivedModeChequeNumber(),receiving.getLoanVoucherDetails(),receiving.getId()});
        return receiving.getId();
    }

    public Long updateLoanPayment(LoanPaid paying) throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "update t_loan_paid set loan_Paid_date=?, Loan_Paid_to_id=?, paid_to_type=?, Loan_paid_Amount=?, Loan_Paid_BY=?, Loan_Paid_Mode=?, Loan_Paid_Mode_Diposited_at=?, Loan_paid_mode_cheque_number=?, Loan_Voucher_details=? where Loan_paid_ID = ? ";
        jdbcTemplate.update(sql, new Object[]{paying.getLoan_paid_date(),paying.getLoan_paid_to_id(),paying.getPaidToType(),paying.getLoan_paid_amount(),paying.getLoan_paid_by(),paying.getLoan_paid_mode(),paying.getLoan_paid_mode_diposited_at(),paying.getLoan_paid_mode_cheque_number(),paying.getLoan_voucher_details(),paying.getLoan_paid_id()});
        return paying.getLoan_paid_id();
    }

    public LoanPaid findByPaidId(Long id) throws Exception{
         JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        String sql = "SELECT * FROM t_loan_paid WHERE Loan_paid_ID = ?";
        LoanPaid paidChange = (LoanPaid) jdbcTemplate.queryForObject(
                sql, new Object[]{id},
                new BeanPropertyRowMapper(LoanPaid.class));
        return paidChange;
    }
    
    public LoanList findLoanDetails(Long loanId) throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
        StringBuffer queryString = new StringBuffer(LOAN_QUERY)
                .append(" where id = ?");
             List<LoanList> vehicleList = (List<LoanList>) jdbcTemplate.query(queryString.toString(), new Object[]{loanId}, new LoanResultSetExtractor());
             return   vehicleList.size()>0?vehicleList.get(0):null;
    }
    
}