BF

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

import java.util.List;

import javax.persistence.Query;

import org.jleaf.common.CommonConstants;
import org.jleaf.common.dao.OUDao;
import org.jleaf.common.entity.ComboValue;
import org.jleaf.common.entity.OU;
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.ErrorList;
import org.jleaf.core.annotation.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.annotation.InfoOut;
import org.jleaf.core.dao.CriteriaHelper;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.master.MasterConstants;
import org.jleaf.erp.master.bo.ComboIdMasterConstants;
import org.jleaf.erp.master.dao.CashBankDao;
import org.jleaf.erp.master.entity.CashBank;
import org.jleaf.erp.master.entity.OuStructure;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Business Function : Get Cash Bank List For Bank And Overdraft Type Advance
 *  > Menampilkan Bank Account untuk tipe Bank dan juga Overdraft
 *
 * @author nadia 29 Juni 2018
 */

// @formatter:off
@Service
@InfoIn({
	@Info(name="tenantId",description="tenant id", type=Long.class),
	@Info(name="code",description="cash bank code", type=String.class),
	@Info(name="name", description="cash bank name", type=String.class),
	@Info(name="bankCode", description="bank code", type=String.class),
	@Info(name="currencyCode", description="currency code", type=String.class),
	@Info(name="active", description="active", type=String.class)

})
@InfoOut({
	@Info(name="cashBankList", description="list of cash bank (id, tenantId, code, "
															+ "name, flagCashBank, flagCashBankPropKey, "
															+ "bankCode, bankPropKey, accountNo, "
															+ "currencyCode, currencyPropKey, accountName1, "
															+ "accountName2, address1, address2, "
															+ "address3, coaId, active)", type=List.class)
})
@ErrorList(errorKeys = {})
//@formatter:on
public class GetCashBankListForBankAndOverdraftTypeAdvance extends AbstractBusinessFunction implements BusinessFunction {

	@Autowired
	private CashBankDao cashBankDao;

	public String getDescription() {
		return "Get Cash Bank List For Bank And Overdraft Type advance";
	}

	public Dto execute(Dto inputDto) throws Exception {

		ValidationUtil.valDtoContainsKey(inputDto, "tenantId");

		Long tenantId = inputDto.getLong("tenantId");

		List<Object[]> result = null;

		QueryBuilder builder = new QueryBuilder();
		builder.add("SELECT e.cashbank_id, e.tenant_id, e.cashbank_code, e.cashbank_name, e.flg_cash_bank, f.prop_key AS flagCashBankPropKey, e.bank_code, COALESCE(g.prop_key, '') AS bankPropKey, e.account_no, e.curr_code, h.prop_key AS currencyPropKey, e.account_name1, e.account_name2, e.address1, e.address2, e.address3, e.coa_id, e.active FROM ")
			   .add(CashBank.TABLE_NAME)
			   .add(" e ")
			   .add(" INNER JOIN ")
			   .add(ComboValue.TABLE_NAME)
			   .add(" f ON e.flg_cash_bank = f.code AND f.combo_id = '" + MasterConstants.FLAG_BANK + "'")
			   .add(" INNER JOIN ")
			   .add(ComboValue.TABLE_NAME)
			   .add(" h ON e.curr_code = h.code AND h.combo_id = '" + ComboIdMasterConstants.COMBO_CURRENCY + "'")
			   .add(" LEFT JOIN ")
			   .add(ComboValue.TABLE_NAME)
			   .add(" g ON e.bank_code = g.code AND g.combo_id = '" + ComboIdMasterConstants.COMBO_BANK + "'")
			   .add(" WHERE e.cashbank_id = e.cashbank_id ")
			   .add(" AND e.flg_cash_bank IN ( '" + MasterConstants.FLAG_BANK+"'" +","+ "'" + MasterConstants.FLAG_OVERDRAFT + "')")
			   .addIfNotNull(inputDto.getLong("tenantId"), " AND e.tenant_id = " + inputDto.getLong("tenantId"))
			   .addIfNotEmpty(inputDto.getString("code"), " AND " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("code"), "e.cashbank_code"))
			   .addIfNotEmpty(inputDto.getString("name"), " AND " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("name"), "e.cashbank_name"))
			   .addIfNotEquals(inputDto.getString("bankCode"), CommonConstants.ID_NOT_USED.toString(), " AND " + CriteriaHelper.equalsExpressionIgnoreCase(inputDto.getString("bankCode"), "e.bank_code"))
			   .addIfNotEquals(inputDto.getString("currencyCode"), CommonConstants.ID_NOT_USED.toString(), " AND " + CriteriaHelper.equalsExpressionIgnoreCase(inputDto.getString("currencyCode"), "e.curr_code"))
			   .addIfNotEmpty(inputDto.getString("active"), " AND " + CriteriaHelper.equalsExpressionIgnoreCase(inputDto.getString("active"), "e.active"))
			   .add(" ORDER BY e.cashbank_name ASC ");

		Query q = cashBankDao.createNativeQuery(builder.toString());

		result = q.getResultList();

		return new Dto().putList("cashBankList", DtoUtil.createDtoListFromArray(result,
				"id", "tenantId", "code", "name", "flagCashBank", "flagCashBankPropKey", "bankCode", "bankPropKey", "accountNo", "currencyCode", "currencyPropKey", "accountName1", "accountName2", "address1", "address2", "address3", "coaId", "active"));
	}

//	public static void main(String[] args) {
//		QueryBuilder builder = new QueryBuilder();
//	builder.add("SELECT e.cashbank_id, e.tenant_id, e.cashbank_code, e.cashbank_name, e.flg_cash_bank, f.prop_key AS flagCashBankPropKey, e.bank_code, COALESCE(g.prop_key, '') AS bankPropKey, e.account_no, e.curr_code, h.prop_key AS currencyPropKey, e.account_name1, e.account_name2, e.address1, e.address2, e.address3, e.coa_id, e.active FROM ")
//	   .add(CashBank.TABLE_NAME)
//	   .add(" e ")
//	   .add(" INNER JOIN ")
//	   .add(ComboValue.TABLE_NAME)
//	   .add(" f ON e.flg_cash_bank = f.code AND f.combo_id = '" + ComboIdMasterConstants.COMBO_FLAG_CASH_BANK + "'")
//	   .add(" INNER JOIN ")
//	   .add(ComboValue.TABLE_NAME)
//	   .add(" h ON e.curr_code = h.code AND h.combo_id = '" + ComboIdMasterConstants.COMBO_CURRENCY + "'")
//	   .add(" LEFT JOIN ")
//	   .add(ComboValue.TABLE_NAME)
//	   .add(" g ON e.bank_code = g.code AND g.combo_id = '" + ComboIdMasterConstants.COMBO_BANK + "'")
//	   .add(" WHERE e.cashbank_id = e.cashbank_id ")
//	   .add(" AND e.flg_cash_bank IN ( '" + MasterConstants.FLAG_BANK+"'" +","+ "'" + MasterConstants.FLAG_OVERDRAFT + "')");
//
//		System.out.println(builder.toString());
//
//	}

}