/*
 * 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.OfficeExpenseDao;
import com.bestray.transtracking.dao.mapper.EmployeeResultSetExtractor;
import com.bestray.transtracking.dao.mapper.OfficeExpenseResultSetExtractor;
import com.bestray.transtracking.dto.OfficeExpenseSearchDTO;
import com.bestray.trastrack.domain.Employee;
import com.bestray.trastrack.domain.OfficeExpense;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;

/**
 *
 * @author User1
 */
public class OfficeExpenseDaoImpl implements OfficeExpenseDao {
    
    private DataSource ds;
    private JdbcTemplate jdbcTemplate;
    private static final String OFFICE_EXPENSE_SEARCH_QUERY ="SELECT * from office_expense ";
    private static final String EMPLOYEE_QUERY = "SELECT e.`id`,e.`emp_name`,e.`emp_shortname`,e.`category_id`,e.`marital_status`,e.`birth_date`,e.`gender`,e.`nationality`,e.`hire_date`,e.`father_name`,e.`prmnt_address_id`,e.`prsnt_address_id`,e.`contact_1`,e.`contact_2`,e.`open_balance`,e.`balance_mode`,e.`lock`,e.`mother_name` FROM `code` c INNER JOIN `Employee_master` e ON c.`id` = e.`category_id` WHERE e.lock = 'NO' AND c.`name`='Office Staff' ";
    private static final String ORDER_BY = " order by id desc ";

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

    public void saveOfficeExpense(OfficeExpense officeexpense) throws Exception {
        
        String sql = "insert into office_expense (office_expense_date, office_expense_type_id, office_staff_id, office_expense_name,office_expense_amount,expense_paid_by,notes) values(?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql,officeexpense.getExpense_date(), officeexpense.getExpense_type_id(), officeexpense.getOffice_staff_id(), officeexpense.getExpense_name(), officeexpense.getExpense_amount(), officeexpense.getPaid_by(), officeexpense.getNote());
        
    }

    public List<OfficeExpense> getSearchResults(final OfficeExpenseSearchDTO search){
        StringBuffer queryString = new StringBuffer(OFFICE_EXPENSE_SEARCH_QUERY);
            queryString.append(" where ");
        if(search.getSearchFrom()!=null&&search.getSearchTo()!=null){
            queryString.append(" office_expense_date>=? and ");
            queryString.append(" office_expense_date<=?");
            //queryString.append(ORDER_BY);
        }else if(search.getSearchFrom()==null&&search.getSearchTo()!=null){
            //queryString.append(" office_expense_date>=? and ");
            queryString.append(" office_expense_date<=?");
            //queryString.append(ORDER_BY);
        }else if(search.getSearchFrom()!=null&&search.getSearchTo()==null){
            queryString.append(" office_expense_date>=? ");
            //queryString.append(" and office_expense_date<=?");
            //queryString.append(ORDER_BY);
        }
        
        queryString.append(ORDER_BY);
                 
            //System.out.println(queryString.toString());   
         return (List<OfficeExpense>) jdbcTemplate.query(queryString.toString(),  new PreparedStatementSetter() {

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

    public OfficeExpense findOfficeExpenseRegistration(Long id) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
       StringBuffer queryString = new StringBuffer(OFFICE_EXPENSE_SEARCH_QUERY)
               .append("where id = ? ")
               .append(ORDER_BY);
             List<OfficeExpense> officeExpenseDetails = (List<OfficeExpense>) jdbcTemplate.query(queryString.toString(), new Object[]{id}, new OfficeExpenseResultSetExtractor());
             return   officeExpenseDetails.size()>0?officeExpenseDetails.get(0):null;
    }

    public OfficeExpense findExpenseDetails(Long expenseID) throws Exception {
        StringBuffer queryString = new StringBuffer(OFFICE_EXPENSE_SEARCH_QUERY)
               .append("where id = ? ")
               .append(ORDER_BY);
             List<OfficeExpense> expenseList = (List<OfficeExpense>) jdbcTemplate.query(queryString.toString(), new Object[]{expenseID}, new OfficeExpenseResultSetExtractor());
             return   expenseList.size()>0?expenseList.get(0):null;
    }

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

    public List<Employee> findOfficeStaffList() throws Exception {
        StringBuffer queryString = new StringBuffer(EMPLOYEE_QUERY).append("order by e.`emp_name` asc");
          List<Employee> employeeList = (List<Employee>) jdbcTemplate.query(queryString.toString(), new EmployeeResultSetExtractor());
          return employeeList;
    }

  
    public OfficeExpense findCollectionDetails(Long valueOf) throws Exception {
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        StringBuffer queryString = new StringBuffer(OFFICE_EXPENSE_SEARCH_QUERY)
               .append("where id = ? ")
               .append(ORDER_BY);
             List<OfficeExpense> collectionList = (List<OfficeExpense>) jdbcTemplate.query(queryString.toString(), new Object[]{valueOf}, new OfficeExpenseResultSetExtractor());
             return   collectionList.size()>0?collectionList.get(0):null;
       
    }

   
    public void updateOfficeExpense(OfficeExpense officeexpense) throws Exception {
        //JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
       //final java.util.Date date= new java.util.Date();
       String sql = "update office_expense set office_expense_date=?, office_expense_type_id=?, office_staff_id=?, office_expense_name=?, office_expense_amount=?, expense_paid_by=?, notes=? where id = ?";      
       jdbcTemplate.update(sql, new Object[]{officeexpense.getExpense_date(), officeexpense.getExpense_type_id(), (officeexpense.getOffice_staff_id()!= null && officeexpense.getOffice_staff_id().longValue()>0)?officeexpense.getOffice_staff_id():null , officeexpense.getExpense_name(), officeexpense.getExpense_amount(), officeexpense.getPaid_by(), officeexpense.getNote(), officeexpense.getId()});
    }
    
}