package org.jleaf.erp.sls.bo.deliveryorder;

import java.util.List;

import javax.persistence.Query;

import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.Dto;
import org.jleaf.core.GeneralConstants;
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.inv.entity.ProductBalanceStock;
import org.jleaf.erp.master.entity.Product;
import org.jleaf.erp.master.entity.ProductStatus;
import org.jleaf.erp.sls.SalesConstants;
import org.jleaf.erp.sls.dao.SalesOrderBalanceItemDao;
import org.jleaf.erp.sls.entity.DraftDo;
import org.jleaf.erp.sls.entity.DraftDoItem;
import org.jleaf.erp.sls.entity.SalesOrder;
import org.jleaf.erp.sls.entity.SalesOrderBalanceItem;
import org.jleaf.erp.sls.entity.SalesOrderItem;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
 *
 * @author Ivan, 21 Jan 2020
 */

//@formatter:off
@Service("getOutstandingSalesOrderBalanceItemListForDeliveryOrder")
@InfoIn(value = {
		@Info(name = "tenantId", description = "tenant id", type = Long.class),
		@Info(name = "ouId", description = "ou id", type = Long.class),
		@Info(name = "soId", description = "so id", type = Long.class),
		@Info(name = "warehouseId", description = "warehouse id", type = Long.class),
//		@Info(name = "partnerShipAddressId", description = "partner ship address id", type = Long.class),
		@Info(name = "draftDoId", description = "draft do id", type = Long.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 = "itemList", description = "outstanding sales order balance item list for delivery order (soItemId, soItemLineNo, productId, productCode, productName, subCtgrProductId, subCtgrProductCode, subCtgrProductName, ctgrProductId, ctgrProductCode, ctgrProductName, brandId, brandCode, brandName, balanceStockQty, balanceStockBaseUomId, balanceStockBaseUomCode, balanceStockBaseUomName, outstandingQtyInt, outstandingQtyBaseUomId, outstandingQtyBaseUomCode, outstandingQtyBaseUomName, flagSerialNumber, productStatus, productStatusDesc)", type = Long.class)
})
//@formatter:on
public class GetOutstandingSalesOrderBalanceItemListForDeliveryOrderForSasa extends AbstractBusinessFunction implements BusinessFunction {

	@Autowired
	SalesOrderBalanceItemDao salesOrderBalanceItemDao;

	public String getDescription() {
		return "get outstanding sales order balance item list for delivery order";
	}

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

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

		//@formatter:off
		// create native sql

		QueryBuilder builder = new QueryBuilder();
		builder.add(" WITH filtered_stock AS( ")
				.add(" 		SELECT B.draft_do_item_id, C.product_status, D.flg_sell, SUM(COALESCE(C.qty, 0)) as balance_stock_qty, C.base_uom_id ")
				.add(" 		FROM ").add(DraftDo.TABLE_NAME).add(" A ")
				.add(" 				INNER JOIN ").add(DraftDoItem.TABLE_NAME).add(" B ON A.draft_do_id = B.draft_do_id AND A.tenant_id = B.tenant_id ")
				.add(" 				INNER JOIN ").add(ProductBalanceStock.TABLE_NAME).add(" C ON B.product_id = C.product_id AND A.tenant_id = C.tenant_id ")
				.add(" 				INNER JOIN ").add(ProductStatus.TABLE_NAME).add(" D ON C.product_status = D.product_status_code AND A.tenant_id = D.tenant_id ")
				.add(" 		WHERE A.draft_do_id = :draftDoId AND C.warehouse_id = :warehouseId ")
				.add(" 		GROUP BY B.draft_do_item_id, C.product_id, C.product_status, D.flg_sell, C.base_uom_id ")
				.add(" ) ")
				.add(" SELECT D.so_item_id, B.line_no AS soItemLineNo, B.product_id, f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id) AS product_name, ")
				.add(" 		f_get_ctgr_product_code(G.ctgr_product_id) AS ctgr_product_code, f_get_ctgr_product_name(G.ctgr_product_id) AS ctgr_product_name, ")
				.add(" 		f_get_sub_ctgr_product_code(G.sub_ctgr_product_id) AS sub_ctgr_product_code, f_get_sub_ctgr_product_name(G.sub_ctgr_product_id) AS sub_ctgr_product_name, ")
				.add(" 		f_get_brand_code(G.brand_id) AS brand_code, f_get_brand_name(G.brand_id) AS brand_name, ")
				.add(" 		F.balance_stock_qty AS balanceStockQty, COALESCE(F.base_uom_id, G.base_uom_id) AS balanceStockBaseUomId, ")
				.add(" 		f_get_uom_code(G.base_uom_id) AS balanceStockBaseUomCode, f_get_uom_name(G.base_uom_id) AS balanceStockBaseUomName, ")
				.add(" 		E.qty_so_int - E.qty_cancel_int + E.qty_add_int - E.qty_dlv_int AS outstandingQtyInt, ")
				.add(" 		f_get_uom_code(E.base_uom_id) AS outstandingQtyBaseUomCode, f_get_uom_name(E.base_uom_id) AS outstandingQtyBaseUomName, ")
				.add(" 		f_get_flg_serial_number(B.product_id) AS flg_serial_number, ")
				.add(" 		COALESCE(F.product_status, :EMPTY) AS productStatusCode, COALESCE(F.product_status, :EMPTY) AS productStatusDesc, B.qty_picking AS qty_dlv_int ")
				.add(" FROM ").add(DraftDo.TABLE_NAME).add(" A ")
				.add(" 		INNER JOIN ").add(DraftDoItem.TABLE_NAME).add(" B ON A.draft_do_id = B.draft_do_id AND A.tenant_id = B.tenant_id ")
				.add(" 		INNER JOIN ").add(SalesOrder.TABLE_NAME).add(" C ON A.ref_id = C.so_id AND A.tenant_id = C.tenant_id ")
				.add(" 		INNER JOIN ").add(SalesOrderItem.TABLE_NAME).add(" D ON B.ref_id = D.so_item_id AND C.tenant_id = D.tenant_id ")
				.add(" 		INNER JOIN ").add(SalesOrderBalanceItem.TABLE_NAME).add(" E ON D.so_item_id = E.so_item_id AND D.tenant_id = E.tenant_id ")
				.add(" 		LEFT OUTER JOIN filtered_stock F ON B.draft_do_item_id = F.draft_do_item_id ")
				.add(" 		INNER JOIN ").add(Product.TABLE_NAME).add(" G ON G.product_id = B.product_id AND B.tenant_id = G.tenant_id ")
				.add(" WHERE F.flg_sell = :YES AND f_authorize_user_role_policy_product(:tenantId,:userLoginId,:roleLoginId,B.product_id) = :ONE ")
				.add(" 		AND E.status_item = :RELEASED ")
				.add(" 		AND A.tenant_id = :tenantId ")
				.add(" 		AND A.ou_id = :ouId ")
				.add(" 		AND A.draft_do_id = :draftDoId ")
				.add(" GROUP BY D.so_item_id, B.line_no, B.product_id, G.ctgr_product_id, G.sub_ctgr_product_id, G.brand_id, F.balance_stock_qty, ")
				.add(" 		F.base_uom_id, G.base_uom_id, E.qty_so_int, E.qty_cancel_int, E.qty_add_int, E.qty_dlv_int, E.base_uom_id, F.product_status, B.qty_picking ")
				.add(" ORDER BY B.line_no ");
		//@formatter:on

		// create native query
		Query q = salesOrderBalanceItemDao.createNativeQuery(builder.toString());
		q.setParameter("tenantId", inputDto.getLong("tenantId"));
		q.setParameter("ouId", inputDto.getLong("ouId"));
		//q.setParameter("soId", inputDto.getLong("soId"));
//		q.setParameter("partnerShipAddressId", inputDto.getLong("partnerShipAddressId"));
		q.setParameter("warehouseId", inputDto.getLong("warehouseId"));
		q.setParameter("draftDoId", inputDto.getLong("draftDoId"));
		q.setParameter("userLoginId", inputDto.getLong("userLoginId"));
		q.setParameter("roleLoginId", inputDto.getLong("roleLoginId"));
		q.setParameter("RELEASED", SalesConstants.RELEASED_TRANSACTION);
		q.setParameter("YES", GeneralConstants.YES);
		q.setParameter("ONE", 1L);
		q.setParameter("EMPTY", GeneralConstants.EMPTY_VALUE);

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

		// return as Dto using DtoUtil.createDtoListFromArray
		return new Dto().putList("itemList", DtoUtil.createDtoListFromArray(result,
				"soItemId", "soItemLineNo", "productId", "productCode", "productName",
				"ctgrProductCode", "ctgrProductName", "subCtgrProductCode", "subCtgrProductName",
				"brandCode", "brandName", "balanceStockQty", "balanceStockBaseUomId",
				"balanceStockBaseUomCode", "balanceStockBaseUomName",
				"outstandingQtyInt", "outstandingQtyBaseUomCode", "outstandingQtyBaseUomName", "flagSerialNumber", "productStatus", "productStatusDesc", "qtyDlvInt"));
	}
}