/*
 * 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.CompanyDao;
import com.bestray.transtracking.dao.mapper.CompanyResultSetExtractor;
import com.bestray.trastrack.domain.Company;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 *
 * @author Anshu1
 */
public class CompanyDaoImpl implements CompanyDao{
    private DataSource ds;
    
    private static final String COMPANY_QUERY ="SELECT id,name,add1,add2,state,city,contact1,contact2,email,web,opening_bal,start_date,end_date from t_company where id=(select max(id) from t_company)";
    private static final String QUERY ="select id,name,add1,add2,state,city,contact1,contact2,email,web,opening_bal,start_date,end_date from t_company where id=(select max(id) from t_company)";
    private static final String OPENING_BAL_QUERY ="select sum(rcv_amt)-sum(Paid_amount) "+ 
"from "+
"("+
    "SELECT DATE_FORMAT(employee_payment_entry.`payment_date`, '%d/%m/%Y'),"+
    "sum(employee_payment_entry.`payment_amount`) AS Paid_amount,0.0 AS rcv_amt,employee_master.`emp_name` AS Paid_to,"+
    "(Select vehicle_number from vehicle_master where vehicle_master.`id`=employee_payment_entry.`vehicle_number_id`) "+
    "as vehicle_no,\"Driver Payment\" as comments,CONCAT(employee_payment_entry.`note_1`,'. ',employee_payment_entry.`note_2`) as remarks "+
    "FROM `employee_payment_entry` employee_payment_entry INNER JOIN `employee_master` employee_master "+
    "ON employee_payment_entry.`employee_name_id` = employee_master.`id` "+ 
    "WHERE employee_payment_entry.`payment_date` >= ? "+
    "AND employee_payment_entry.`payment_date` < ? "+
    
    
        
            
    "UNION ALL "+ 
    
    "SELECT DATE_FORMAT(vehicle_expense_entry.`vehicle_expense_date`, '%d/%m/%Y') "+
        "AS date,sum(vehicle_expense_entry.`expense_amount`) AS Paid_amount "+
        ",0.0 AS rcv_amt,party_master.`name` "+
        "AS party_master_name,vehicle_master.`vehicle_number` AS vehicle_master_vehicle_number,\"Vehicle Maintenance\" AS Comments, "+
        "CONCAT(vehicle_expense_entry.`note_1`,'. ',vehicle_expense_entry.`note_2`) AS remarks "+
        "FROM `vehicle_maintenance_alert` "+
        "vehicle_maintenance_alert "+
        "INNER JOIN `vehicle_expense_entry` vehicle_expense_entry ON "+
        "vehicle_maintenance_alert.`id` = vehicle_expense_entry.`vehicle_maintenance_id` INNER JOIN `task_master` task_master "+
    "ON vehicle_maintenance_alert.`maintenance_id` = task_master.`id` INNER JOIN `vehicle_master` vehicle_master "+ 
    "ON vehicle_maintenance_alert.`vehicle_id` = vehicle_master.`id` INNER JOIN `party_master` party_master "+
    "ON vehicle_expense_entry.`party_id` = party_master.`id` WHERE vehicle_expense_entry.`vehicle_expense_date` >= ? AND " +
    "vehicle_expense_entry.`vehicle_expense_date` < ? " +
    
   "UNION ALL "+
"SELECT DATE_FORMAT(t_vehicle_owner_payment.`pay_date`,'%d/%m/%Y') AS date,"+
"sum(t_vehicle_owner_payment.`amount`) AS Paid_amount,0.0 AS rcv_amt,"+
"(Select vehicle_owner.`name` from vehicle_owner Where vehicle_owner.`id`=t_vehicle_owner_payment.`vehicle_owner_id`) AS Paid_to,"+
"\"\" as vehicle_id,"+
"\"Vehicle Owner Payment\" AS Comments,t_vehicle_owner_payment.`remarks` AS REMARKS "+
"FROM `t_vehicle_owner_payment` t_vehicle_owner_payment "+
"WHERE t_vehicle_owner_payment.`pay_date` >= ? AND t_vehicle_owner_payment.`pay_date` < ? "+

    
    "UNION ALL "+
    
    "SELECT DATE_FORMAT(oil_entry.`date`,'%d/%m/%Y') AS date,"+
    "sum(oil_entry.`total_price`) AS Paid_amount,0.0 AS rcv_amt,(Select party_master.`name` from party_master "+
    "Where party_master.`id` = oil_entry.`party_id`) AS party_master_name,vehicle_master.`vehicle_number` AS vehicle_master_vehicle_number,"+
    "\"Oil Entry\" as remarks,concat(\"Quantity: \", oil_entry.`oil_quantity`) AS oil_entry_oil_quantity FROM `oil_entry` oil_entry "+
    "INNER JOIN `vehicle_master` vehicle_master ON oil_entry.`vehicle_id` = vehicle_master.`id` "+
    "WHERE oil_entry.`party_id` IS NOT NULL AND oil_entry.`date` >= ? AND oil_entry.`date` < ? and oil_entry.`party_id` = (select id from party_master where name='CASH') "+
    
    
    "UNION ALL "+
    "SELECT DATE_FORMAT(office_expense.`office_expense_date`, '%d/%m/%Y') AS date,sum(office_expense.`office_expense_amount`) "+ 
    "AS Paid_amount,0.0 AS rcv_amt,(Select `emp_name` from employee_master where "+
    "employee_master.`id`=office_expense.`office_staff_id`) As staff,\"\" as Vehicle_ID,\"Office Expense\" AS Comments,"+
    "office_expense.`notes` AS remarks FROM `code` code INNER JOIN `office_expense` office_expense "+
    "ON code.`id` = office_expense.`office_expense_type_id` "+
    "where office_expense.`office_expense_date` >= ? AND office_expense.`office_expense_date` < ?"+
    
    "UNION ALL SELECT DATE_FORMAT(client_payment_entry.`payment_date`, '%d/%m/%Y') "+ 
    "AS date,0.0 AS Paid_amount,sum(client_payment_entry.`amount`) AS rcv_amt,client_master.`name` AS client_master_name, \"\" as vehicle_no,"+
    "\"Client Collection\" as comments, CONCAT(client_payment_entry.`comment_1`,'. ',client_payment_entry.`comment_2`) AS remarks "+
    "FROM `client_master` client_master INNER JOIN `client_payment_entry` client_payment_entry ON "+
    "client_master.`id` = client_payment_entry.`client_id` "+
    "WHERE client_payment_entry.`payment_date` >= ? AND client_payment_entry.`payment_date` < ?"+
            
            " UNION ALL " +
"SELECT DATE_FORMAT(payment_voucher.`payment_date`, '%d/%m/%Y') AS date," +
"sum(payment_voucher.`amount_paid`) AS paid_amt," +
"0.0 AS rcv_amt," +
"\"\" AS client_master_name," +
"party_master.`name` as vehicle_no," +
"\"Payment Voucher\" as comments," +
"payment_voucher.`voucher_details` AS remarks " +
"FROM `payment_voucher` payment_voucher INNER JOIN `party_master` party_master ON party_master.`id` = payment_voucher.`party_name_id`" +
"WHERE payment_voucher.`payment_date` >= ? AND payment_voucher.`payment_date` < ? "+
          
            " UNION ALL " +
"SELECT DATE_FORMAT(t_loan_received.`loan_received_date`, '%d/%m/%Y') AS date," +
"0.0 AS paid_amt," +
"sum(t_loan_received.`loan_amount`) AS rcv_amt," +
"t_loan_received.`received_from_name` AS Received_From," +
"\"\" as vehicle_no," +
"\"Loan Received\" as comments," +
"t_loan_received.`loan_voucher_details` AS remarks " +
"FROM `t_loan_received` t_loan_received " +
"WHERE t_loan_received.`loan_received_date` >= ? AND t_loan_received.`loan_received_date` < ? "+ 
            
            " UNION ALL " +
"SELECT DATE_FORMAT(t_loan_paid.`loan_Paid_date`,'%d/%m/%Y') AS date," +
"sum(t_loan_paid.`Loan_paid_Amount`) AS Paid_amount,0.0 AS rcv_amt," +
"t_loan_paid.`paid_to_name` AS Paid_To," +
"\"\" as vehicle_id," +
"\"Loan Paid\" AS Comments,t_loan_paid.`Loan_Voucher_details` AS REMARKS " +
"FROM " +
"`t_loan_paid` t_loan_paid " +
"WHERE" +
" t_loan_paid.`loan_Paid_date` >= ? AND t_loan_paid.`loan_Paid_date` < ?"+
            ")a";

    public CompanyDaoImpl(DataSource ds){
        this.ds=ds;
    }
    
    public void saveCompany(Company company)throws Exception{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        
        String sql = "insert into t_company (name,add1, add2,state,city,contact1,contact2,email,web,opening_bal,start_date,end_date) values(?,?,?,?,?,?,?,?,?,?,?,?)";      
        jdbcTemplate.update(sql, company.getName(), company.getAddress1(), company.getAddress2(), company.getState(),company.getCity(),company.getContact1(),company.getContact2(),company.getEmail(),company.getWeb(),company.getOpen_Balance(),company.getStartDate(),company.getEndDate());
        
    }
   
    public Company findCompanyRegistraion() throws Exception{
       JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
        StringBuffer queryString = new StringBuffer(COMPANY_QUERY);
            
             List<Company> companyList = (List<Company>) jdbcTemplate.query(queryString.toString(), new Object[]{},  new CompanyResultSetExtractor());
             return   companyList.size()>0?companyList.get(0):null;
       
    }
    public double getOpeningBal() throws Exception{
       JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
         
        StringBuffer queryString = new StringBuffer(QUERY);
            
             List<Company> companyList = (List<Company>) jdbcTemplate.query(queryString.toString(), new Object[]{},  new CompanyResultSetExtractor());
             
             return   companyList.size()>0 ? companyList.get(0).getOpen_Balance():null;
       
    }

    public double getOpeningBalDateRange(Date from,Date to) throws Exception {
        try{
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        StringBuffer queryString = new StringBuffer(OPENING_BAL_QUERY);
            
             Double openBal=  jdbcTemplate.queryForObject(queryString.toString(), new Object[]{from,to,from,to,from,to,from,to,from,to,from,to,from,to,from,to,from,to},Double.class);
            return   openBal;
        }catch(Exception e){
            e.printStackTrace();
            return 0.0;
        }
    }
}