/**
 *
 */
package org.jleaf.erp.purch.bo.po;

import java.util.List;

import javax.persistence.Query;

import org.jleaf.common.entity.OU;
import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.CoreExceptionConstants;
import org.jleaf.core.Dto;
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.entity.OuStructure;
import org.jleaf.erp.master.entity.Partner;
import org.jleaf.erp.purch.PurchasingConstants;
import org.jleaf.erp.purch.dao.PurchaseOrderDao;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * get authorized product not in stock list for purchase order
 *
 * @author david 13 Sep 2012
 *
 */

/**
 * Menghilangkan kondisi exists in status_item F atau I
 * modified by Adrian
 * Jun 16, 2017
 */

//@formatter:off
@Service("getOutstandingSoListForPo")
@InfoIn(value = {
        @Info(name = "tenantId", description = "tenant id", type = Long.class),
        @Info(name = "ouId", description = "ou id", type = Long.class)
})
@InfoOut(value = {
        @Info(name = "soList", description = "list of SO (docTypeId, soId, soNo, soDate, partnerId, partnerCode, partnerName, extDocNo, extDocDate, remark)", type = List.class)
})
@ErrorList(errorKeys = {
        CoreExceptionConstants.DTO_CANNOT_NULL
})
//@formatter:on
public class GetOutstandingSoListForPoForIndocom extends AbstractBusinessFunction implements BusinessFunction {

    @Autowired
    PurchaseOrderDao purchaseOrderDao;

    @Override
    public String getDescription() {
        return "get outstanding so list for po";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto execute(Dto inputDto) throws Exception {
        // validate inputDto have key in @InfoIn
        ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
        ValidationUtil.valDtoContainsKey(inputDto, "ouId");

        // prepare list for query result
        List<Object[]> result = null;

        QueryBuilder builder = new QueryBuilder();
        builder.add("SELECT A.doc_type_id, A.so_id, A.doc_no, A.doc_date, A.partner_id, E.partner_code, E.partner_name, ")
                .add(" A.ext_doc_no, A.ext_doc_date, f_get_ou_name(A.ou_id) AS ou_name, A.remark ")
                .add(" FROM sl_so A INNER JOIN ")
                .add(OuStructure.TABLE_NAME)
                .add(" B ON A.ou_id = B.ou_id INNER JOIN ")
                .add(Partner.TABLE_NAME)
                .add(" E ON A.partner_id=E.partner_id ")
                .add(" WHERE a.tenant_id = :tenantId ")
                .add(" AND B.ou_bu_id = :ouId")
                    .add(" AND EXISTS( SELECT 1 FROM ")
                        .add(PurchasingConstants.TABLE_SO__ITEM)
                        .add(" C INNER JOIN ")
                            .add(PurchasingConstants.TABLE_SO_PO_BALANCE_ITEM)
                            .add(" D ON C.so_item_id = D.so_item_id ")
                        .add(" INNER JOIN ")
                            .add(PurchasingConstants.TABLE_SO_BALANCE_ITEM)
                            .add(" E ON D.so_item_id = E.so_item_id ")
                        .add(" WHERE C.so_id = A.so_id ")
                        .add(" AND A.ou_id = D.ou_id ")
                        .add(" AND C.tenant_id = A.tenant_id ")
                        .add(" AND D.status_item = :RELEASED AND E.status_item = :RELEASED ")
                        .add(" GROUP BY A.so_id )")
                    .add(" AND EXISTS( SELECT 1 FROM ")
                        .add(PurchasingConstants.TABLE_SO__ITEM)
                        .add(" C INNER JOIN ")
                            .add(PurchasingConstants.TABLE_SO_PO_BALANCE_ITEM)
                            .add(" D ON C.so_item_id = D.so_item_id ")
                        .add(" INNER JOIN ")
                            .add(PurchasingConstants.TABLE_SO_BALANCE_ITEM)
                            .add(" E ON D.so_item_id = E.so_item_id ")
                        .add(" WHERE C.so_id = A.so_id ")
                        .add(" AND A.ou_id = D.ou_id ")
                        .add(" AND C.tenant_id = A.tenant_id ")
                        .add(" AND D.qty_po_int > 0 ")
                        .add(" GROUP BY A.so_id ) ")
                .add(" GROUP BY A.doc_type_id, A.so_id, A.doc_no, A.doc_date, A.partner_id, partner_code, partner_name, A.ext_doc_no, A.ext_doc_date, A.remark ")
                .add(" ORDER BY A.doc_no ");

//      QueryBuilder builder = new QueryBuilder();
//      builder.add("SELECT c.doc_type_id, c.so_id, c.doc_no, c.doc_date, c.partner_id, d.partner_code, d.partner_name, c.ext_doc_no, c.ext_doc_date, f.ou_name ")
//             .add(" FROM sl_so_po_balance_item z ")
//             .add(" INNER JOIN sl_so_balance_item a ON A.so_item_id = z.so_item_id ")
//             .add(" INNER JOIN sl_so_item b ON a.so_item_id = b.so_item_id ")
//             .add(" INNER JOIN sl_so c ON b.so_id = c.so_id ")
//             .add(" INNER JOIN "+Partner.TABLE_NAME+" d ON c.partner_id = d.partner_id ")
//             .add(" INNER JOIN "+OuStructure.TABLE_NAME+" e ON e.ou_id = a.ou_id ")
//             .add(" INNER JOIN "+OU.TABLE_NAME+" f ON a.ou_id=f.ou_id")
//             .add(" WHERE a.tenant_id = :tenantId AND e.ou_bu_id = :ouId ")
//             .add("   AND (a.qty_so_int - a.qty_cancel_int + a.qty_add_int) - (z.qty_po_int + z.qty_po_int_add - z.qty_po_int_cancel ) > 0 ")
//             .add("   AND a.status_item = :RELEASED ")
//             .add("   AND z.status_item = :RELEASED ")
//             .add(" GROUP BY c.doc_type_id, c.so_id, c.doc_no, c.doc_date, c.partner_id, d.partner_code, d.partner_name, c.ext_doc_no, c.ext_doc_date, f.ou_name ")
//             .add(" ORDER BY c.doc_no ASC ");


        // create native query
        Query q = purchaseOrderDao.createNativeQuery(builder.toString());

        // set query parameter (if any)
        q.setParameter("tenantId", inputDto.getLong("tenantId"));
        q.setParameter("ouId", inputDto.getLong("ouId"));
        q.setParameter("RELEASED", PurchasingConstants.RELEASED_TRANSACTION);
        //q.setParameter("FINAL", PurchasingConstants.FINAL_TRANSACTION);
        //q.setParameter("INPROGRESSTRANSACTION", PurchasingConstants.IN_PROGRESS_TRANSACTION);

        // get query result
        result = q.getResultList();

        // return as Dto using DtoUtil.createDtoListFromArray
        return new Dto().putList("soList",
                DtoUtil.createDtoListFromArray(result, "docTypeId", "soId", "soNo", "soDate", "partnerId", "partnerCode", "partnerName", "extDocNo", "extDocDate", "ouName", "remark"));
    }

}