package org.jleaf.erp.master.bo.partner;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.BusinessTransaction;
import org.jleaf.core.CoreException;
import org.jleaf.core.DefaultBusinessTransaction;
import org.jleaf.core.Dto;
import org.jleaf.core.GeneralConstants;
import org.jleaf.core.annotation.ErrorList;
import org.jleaf.core.annotation.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.annotation.InfoOut;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.master.dao.PartnerDao;
import org.jleaf.util.ValidationUtil;
import org.jleaf.validator.CommonBusinessValidator;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
// @formatter:off
@InfoIn(value = {
        @Info(name = "tenantLoginId", description = "tenant login Id", type = Long.class),
        @Info(name = "sessionId", description = "Session Id", type = String.class),
        @Info(name = "salesmanId", description = "Salesman Id", type = Long.class),
        @Info(name = "period", description = "Period", type = String.class),
        @Info(name = "keyword", description="keyword of code or name", type=String.class),
        @Info(name = "ctgrPartnerId", description="category partner id", type=Long.class),
        @Info(name = "active", description="active", type=String.class),
        @Info(name = "userLoginId", description = "user login id", type = Long.class),
        @Info(name = "roleLoginId", description = "role login id", type = Long.class)})
@InfoOut(value = {@Info(name = "customerFromHierarchyList", description = "list of customer from hierarchy(id, code, name, ctgrPartnerId, extName, npwpId, holdingId, flagHolding, rankCode, rankPropKey, active, version, phone1, phone2, fax1, fax2)", type = List.class)})
@ErrorList(errorKeys = {
})

// @formatter:on
public class GetAuthorizedCustomerFromSalesmanHierarchyList extends AbstractBusinessFunction {

    @Autowired
    private PartnerDao partnerDao;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static final Logger log = LoggerFactory.getLogger(GetAuthorizedCustomerFromSalesmanHierarchyList.class);

    public String getDescription() {
        return "Get Authorized Customer List From Salesman Hierarchy";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto execute(Dto inputDto) throws Exception {
        List<Dto> results = new ArrayList<Dto>();

        ValidationUtil.valDtoContainsKey(inputDto, "tenantLoginId");
        ValidationUtil.valDtoContainsKey(inputDto, "sessionId");
        ValidationUtil.valDtoContainsKey(inputDto, "salesmanId");
        ValidationUtil.valDate(inputDto, "period");
        ValidationUtil.valDtoContainsKey(inputDto, "keyword");
        ValidationUtil.valDtoContainsKey(inputDto, "ctgrPartnerId");
        ValidationUtil.valDtoContainsKey(inputDto, "active");
        ValidationUtil.valDtoContainsKey(inputDto, "userLoginId");
        ValidationUtil.valDtoContainsKey(inputDto, "roleLoginId");

        Long tenantId = inputDto.getLong("tenantLoginId");
        String sessionId = inputDto.getString("sessionId");
        Long salesmanId = inputDto.getLong("salesmanId");
        String period = inputDto.getString("period");
        String keyword = inputDto.getString("keyword");
        Long ctgrPartnerId = inputDto.getLong("ctgrPartnerId");
        String active = inputDto.getString("active");
        Long userId = inputDto.getLong("userLoginId");
        Long roleId = inputDto.getLong("roleLoginId");

        Connection conn = jdbcTemplate.getDataSource().getConnection();
        conn.setAutoCommit(false);
        PreparedStatement p = null;

        try {
            p = conn.prepareStatement("SELECT f_get_authorized_customer_from_salesman_hierarchy(?, ?, ?, ?, ?, ?, ?, ?, ?)");
            p.setString(1, sessionId);
            p.setLong(2, tenantId);
            p.setLong(3, salesmanId);
            p.setString(4, period);
            p.setString(5, keyword);
            p.setLong(6, ctgrPartnerId);
            p.setString(7, active);
            p.setLong(8, userId);
            p.setLong(9, roleId);

            ResultSet resultSetFromFunction = p.executeQuery();
            ResultSet resultSetFromQuery = null;
            if (resultSetFromFunction.next()) {
                resultSetFromQuery = (ResultSet) resultSetFromFunction.getObject(1);
            }

            resultSetFromFunction.close();

            //"id", "code", "name", "ctgrPartnerId", "ctgrPartnerCode", "ctgrPartnerName", "extName", "npwpId", "holdingId", "flagHolding", "rankCode", "rankPropKey", "active", "version",
            //"phone1", "phone2", "fax1", "fax2"

            while (resultSetFromQuery != null && resultSetFromQuery.next()) {
                log.debug("detail : "+resultSetFromQuery);
                Dto detailDto = new Dto();
                detailDto.put("id", resultSetFromQuery.getLong(1));
                detailDto.put("code", resultSetFromQuery.getString(2));
                detailDto.put("name", resultSetFromQuery.getString(3));
                detailDto.put("ctgrPartnerId", resultSetFromQuery.getLong(4));
                detailDto.put("ctgrPartnerCode", resultSetFromQuery.getString(5));
                detailDto.put("ctgrPartnerName", resultSetFromQuery.getString(6));
                detailDto.put("extName", resultSetFromQuery.getString(7));
                detailDto.put("npwpId", resultSetFromQuery.getLong(8));
                detailDto.put("holdingId", resultSetFromQuery.getLong(9));
                detailDto.put("flagHolding", resultSetFromQuery.getString(10));
                detailDto.put("rankCode", resultSetFromQuery.getString(11));
                detailDto.put("rankPropKey", resultSetFromQuery.getString(12));
                detailDto.put("active", resultSetFromQuery.getString(13));
                detailDto.put("version", resultSetFromQuery.getLong(14));
                detailDto.put("phone1", resultSetFromQuery.getString(15));
                detailDto.put("phone2", resultSetFromQuery.getString(16));
                detailDto.put("fax1", resultSetFromQuery.getString(17));
                detailDto.put("fax2", resultSetFromQuery.getString(18));

                results.add(detailDto);
            }

            p.close();
            conn.commit();
            conn.close();

            return new Dto().put("customerFromHierarchyList", results);

        } catch (Exception e) {
            if (p != null)
                p.close();

            if (conn != null && !conn.isClosed())
                conn.rollback();

            throw e;
        }

    }

}