/*
 * COPYRIGHT FH 2018 - ALL RIGHTS RESERVED.
 *
 * This software is only to be used for the purpose for which it has been
 * provided. No part of it is to be reproduced, disassembled, transmitted,
 * stored in a retrieval system nor translated in any human or computer
 * language in any way or for any other purposes whatsoever without the prior
 * written consent of FH.
 */
package br.com.fh.accounting.odata.dailycontrolline.business.bo;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.compiere.util.Env;

import com.fh.camaleo.core.utils.StringUtils;
import com.fh.camaleo.integrator.db.connection.DatabaseConnection;

import br.com.fh.accounting.model.I_FHX_ReimbursableExpense;
import br.com.fh.accounting.model.extension.MFHXDailyControl;
import br.com.fh.accounting.model.extension.MFHXReimbursableExpense;
import br.com.fh.administration.model.extension.MFHFromTo;
import br.com.fh.extension.model.extension.MFHXUser;

/**
 * @author FH - edusilva
 *
 */
public class DailyControlLineBO implements Serializable {

    /**
     * long - serialVersionUID.
     */
    private static final long serialVersionUID = -1674802600277379392L;

    private static final String OTHERSYSTEM_B1 = "B1";

    private BigDecimal amt;

    private BigDecimal amtApproved;

    private BigDecimal totalRefunded;

    private List<DailyControlLineDTO> dailyControlLines;



	public BigDecimal getAmt() {
        return amt;
    }

    public void setAmt(BigDecimal amt) {
        this.amt = amt;
    }

    public BigDecimal getAmtApproved() {
        return amtApproved;
    }

    public void setAmtApproved(BigDecimal amtApproved) {
        this.amtApproved = amtApproved;
    }

    public BigDecimal getTotalRefunded() {
        return totalRefunded;
    }

    public void setTotalRefunded(BigDecimal totalRefunded) {
        this.totalRefunded = totalRefunded;
    }

    public List<DailyControlLineDTO> getDailyControlLines() {
        return dailyControlLines;
    }

    public void setDailyControlLines(List<DailyControlLineDTO> dailyControlLines) {
        this.dailyControlLines = dailyControlLines;
    }

    public static DailyControlLineBO getAllDailyControlLinesFromB1(Properties ctx, Date dateFrom, Date dateTo, Integer userId, String trxName)
            throws Exception {

        Map<Integer, Integer> mapEmployeeCodeByUserId = MFHXUser.getMapEmployeeCodeByUserId();

        MFHXDailyControl dailyControl = MFHXDailyControl
                .getDailyControlFromPeriod(ctx, new Timestamp(dateFrom.getTime()), new Timestamp(dateTo.getTime()), trxName);

        Map<String, BigDecimal> mapOfBalances = MFHXReimbursableExpense
                .getMapOfBalances(ctx, new Timestamp(dateFrom.getTime()), new Timestamp(dateTo.getTime()), userId, trxName);

        List<String> listCompanies = DailyControlLineBO.getUserCompanies(mapEmployeeCodeByUserId, dateFrom, dateTo, userId);


        DailyControlLineBO dailyControlLineBO = new DailyControlLineBO();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        StringBuilder query = null;
        for (String company : listCompanies) {
            query = new StringBuilder();
            query.append(" SELECT a.empresa, a.empId, count(DISTINCT a.data) AS qty ");
            query.append(" FROM FH_INTRANET.dbgBuilder qo.Apontamentos a  ");
            query.append(" INNER JOIN " + company + ".dbo.ORDR p ON (a.docNum = p.DocNum AND a.empresa = ? ");
            query.append(" and p.U_tipoProjFaturavel = ?) ");
            query.append(" WHERE a.data BETWEEN ? AND ? ");
            if (userId != null) {
                query.append(" AND a.empId = ? ");
            }
            query.append(" GROUP by a.empresa, a.empId ");

            try {
                conn = DatabaseConnection.getConnection("SQLSERVER");
                pstmt = conn.prepareStatement(query.toString());
                pstmt.setString(1, company);
                pstmt.setString(2, "1");
                pstmt.setDate(3, dateFrom);
                pstmt.setDate(4, dateTo);
                if (userId != null) {
                    pstmt.setInt(5, mapEmployeeCodeByUserId.get(userId));
                }
                rs = pstmt.executeQuery();
                DailyControlLineBO.fillListDaiyControl(dailyControlLineBO, rs, dailyControl, mapOfBalances, userId, mapEmployeeCodeByUserId);
            } catch (Exception e) {
                throw e;
            } finally {
                DatabaseConnection.close(conn, pstmt);
            }
        }
        return dailyControlLineBO;
    }

    public static List<String> getUserCompanies(Map<Integer, Integer> mapEmployeeCodeByUserId, Date dateFrom,
            Date dateTo, Integer userId) throws Exception {
        List<String> listCompany = new ArrayList<String>();

        StringBuilder query = new StringBuilder();
        query.append(" SELECT DISTINCT a.empresa ");
        query.append(" FROM FH_INTRANET.dbo.Apontamentos a ");
        query.append(" WHERE a.data BETWEEN ? AND ? AND a.empresa IS NOT NULL ");
        if (userId != null) {
            query.append(" AND a.empID = ? ");
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DatabaseConnection.getConnection("SQLSERVER");
            pstmt = conn.prepareStatement(query.toString());
            pstmt.setDate(1, dateFrom);
            pstmt.setDate(2, dateTo);
            if (userId != null) {
                pstmt.setInt(3, mapEmployeeCodeByUserId.get(userId));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                listCompany.add(rs.getString("empresa"));
            }
        } catch (Exception e) {
            throw e;
        } finally {
            DatabaseConnection.close(conn, pstmt);
        }

        return listCompany;
    }

    public static void fillListDaiyControl(DailyControlLineBO dailyControlLineBO, ResultSet rs,
            MFHXDailyControl mDailyControl, Map<String, BigDecimal> mapOfBalances, Integer userId, Map<Integer, Integer> mapEmployeeCodeByUserId) throws SQLException {

        Map<String, String> mapOrgFromTo = MFHFromTo.getMapInternalByExternal(Env.getAD_Client_ID(Env.getCtx()), "AD_Org_ID", OTHERSYSTEM_B1, Env.getCtx());

        List<DailyControlLineDTO> listDailyControlLines = dailyControlLineBO.getDailyControlLines();
        if (listDailyControlLines == null) {
            listDailyControlLines = new ArrayList<DailyControlLineDTO>();
        }

        while (rs.next()) {
            DailyControlLineDTO dailyControlLineDTO = new DailyControlLineDTO();
            String org = rs.getString("empresa");
            if (StringUtils.isNotNullOrEmpty(org) && mapOrgFromTo.get(org) != null) {
                dailyControlLineDTO.setOrgId(Integer.parseInt(mapOrgFromTo.get(org)));
            }
            dailyControlLineDTO.setQty(rs.getBigDecimal("qty"));

            if (userId == null)
                dailyControlLineDTO.setUserId(getUserIdByEmployeeCode(mapEmployeeCodeByUserId, rs.getInt("empId")));
            else
                dailyControlLineDTO.setUserId(userId);

            if (mDailyControl != null)
                dailyControlLineDTO.setDailyValue(mDailyControl.getFHX_Daily());
            else
                dailyControlLineDTO.setDailyValue(BigDecimal.ZERO);

            listDailyControlLines.add(dailyControlLineDTO);
        }
        dailyControlLineBO.setDailyControlLines(listDailyControlLines);

        dailyControlLineBO.setAmt(mapOfBalances.get(I_FHX_ReimbursableExpense.COLUMNNAME_Amt));

        dailyControlLineBO.setAmtApproved(mapOfBalances.get(I_FHX_ReimbursableExpense.COLUMNNAME_FHX_AmtApproved));

        dailyControlLineBO.setTotalRefunded(mapOfBalances.get(I_FHX_ReimbursableExpense.COLUMNNAME_FHX_TotalRefunded));
    }

    private static Integer getUserIdByEmployeeCode(Map<Integer, Integer> mapEmployeeCodeByUserId, Integer empId) {
        Integer userId = null;
        for (Map.Entry<Integer, Integer> entry : mapEmployeeCodeByUserId.entrySet()) {
            if (entry.getValue().equals(empId)) {
                userId = entry.getKey();
            }
        }
        return userId;
    }



}