package org.jleaf.erp.cb.bo.reconcilement;

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.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.cb.CashBankConstants;
import org.jleaf.erp.cb.dao.InOutCashbankDao;
import org.jleaf.erp.cb.entity.ChequeGiroRealization;
import org.jleaf.erp.cb.entity.InOutCGBalance;
import org.jleaf.erp.cb.entity.InOutCashbank;
import org.jleaf.erp.cb.entity.InOutCashbankCost;
import org.jleaf.erp.cb.entity.InOutCashbankPayment;
import org.jleaf.erp.cb.entity.Reconcilement;
import org.jleaf.erp.cb.entity.ReconcilementItem;
import org.jleaf.erp.cb.entity.TransferCashbank;
import org.jleaf.erp.cb.entity.TransferCashbankCost;
import org.jleaf.erp.cb.entity.TransferCashbankReceive;
import org.jleaf.erp.cb.entity.TrxCashbankBalance;
import org.jleaf.erp.master.entity.CashBank;
import org.jleaf.erp.master.entity.CashBankOu;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

 @Service
 @InfoIn(value = { 
		 @Info(name = "tenantId", description = "tenant id", type = Long.class, required = true),
		 @Info(name = "ouId", description = "ou id", type = Long.class, required = true),
		 @Info(name = "userId", description = "user id", type = Long.class, required = true),
		 @Info(name = "roleId", description = "role id", type = Long.class, required = true),
		 @Info(name = "cashBankId", description = "cash/bank id", type = Long.class, required = true),
		 @Info(name = "yearMonth", description = "year month", type = String.class, required = true)
 })
 @InfoOut(value = { 
		 @Info(name = "cashBankTrxList", description = "List of cash bank trx(docTypeId, docTypeDesc, docNo, docDate, refId, refDocTypeId, refDocTypeDesc, refDocNo, refDocDate, partnerId, partnerCode, partnerName, remark, modePayment, bankPayment, noPayment, datePayment, currCode, amount)", type = List.class)
 })
 @ErrorList(errorKeys = { 

 })
 public class GetCashBankTrxListForReconcilement extends AbstractBusinessFunction implements BusinessFunction {
	 
	 @Autowired
	 InOutCashbankDao inOutCashbankDao;
	 
	 @Override
	 public String getDescription() {
		 return "Get CB trx list for reconcilement";
	 }
	 
	 @SuppressWarnings("unchecked")
	 @Override
	 public Dto execute(Dto inputDto) throws Exception {		
		ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
		ValidationUtil.valDtoContainsKey(inputDto, "ouId");
		ValidationUtil.valDtoContainsKey(inputDto, "userId");
		ValidationUtil.valDtoContainsKey(inputDto, "roleId");
		ValidationUtil.valDtoContainsKey(inputDto, "cashBankId");
		ValidationUtil.valDtoContainsKey(inputDto, "yearMonth");
		
		Long tenantId = inputDto.getLong("tenantId");
		Long ouId = inputDto.getLong("ouId");
		Long userId = inputDto.getLong("userId");
		Long roleId = inputDto.getLong("roleId");
		Long cashBankId = inputDto.getLong("cashBankId");
		String yearMonth = inputDto.getString("yearMonth");
		 
		 Dto outputDto = new Dto();
		 List<Object[]> resultList = null;
		 
		 QueryBuilder builder = new QueryBuilder();
		 builder.add(" SELECT A.doc_type_id, A.doc_type_desc, A.doc_no, A.doc_date, ") 
		 		.add(" A.ref_id, A.ref_doc_type_id, A.ref_doc_type_desc, A.ref_doc_no, A.ref_doc_date, ") 
		 		.add(" A.partner_id, A.partner_code, A.partner_name, " ) 
		 		.add(" A.remark, " ) 
		 		.add(" A.mode_payment, A.bank_payment, A.no_payment, A.date_payment, ")
		 		.add(" A.curr_code, A.amount")
		 		.add(" FROM ( ")
		 		.add(" SELECT A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc, A.doc_no, A.doc_date, " )
		 		.add(" :EMPTY_ID AS ref_id, :EMPTY_ID AS ref_doc_type_id, :SPACEVALUE AS ref_doc_type_desc, :SPACEVALUE AS ref_doc_no, :SPACEVALUE AS ref_doc_date, " )
		 		.add(" :EMPTY_ID AS partner_id, :SPACEVALUE AS partner_code, :SPACEVALUE AS partner_name, " )
		 		.add(" A.remark, " )
		 		.add(" A.mode_payment, A.bank_payment, A.no_payment, A.date_payment, ")
		 		.add(" A.curr_code, A.transfer_amount + COALESCE(B.payment_amount, 0) AS amount ")
		 		.add(" FROM ").add(TransferCashbank.TABLE_NAME).add(" A ")
		 		.add(" LEFT OUTER JOIN ").add(TransferCashbankCost.TABLE_NAME).add(" B ON A.transfer_cashbank_id = B.transfer_cashbank_id ")
		 		.add(" INNER JOIN ").add(CashBankOu.TABLE_NAME).add(" C ON A.cashbank_id = C.cashbank_id ")
		 		.add(" WHERE A.tenant_id = :tenantId ")
		 		.add(" AND C.ou_id = :ouId ")
		 		.add(" AND A.cashbank_id = :cashBankId ")
		 		.add(" AND SUBSTR(A.doc_date, 1, 6) = :yearMonth ")
		 		.add(" AND f_authorize_user_role_policy_cashbank(:tenantId, :userId, :roleId, A.cashbank_id) = 1 ")
		 
		 		.add(" UNION ALL ")
		 
		 		.add(" SELECT B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc, B.doc_no, B.doc_date, " )
		 		.add(" :EMPTY_ID AS ref_id, :EMPTY_ID AS ref_doc_type_id, :SPACEVALUE AS ref_doc_type_desc, :SPACEVALUE AS ref_doc_no, :SPACEVALUE AS ref_doc_date, " )
		 		.add(" :EMPTY_ID AS partner_id, :SPACEVALUE AS partner_code, :SPACEVALUE AS partner_name, " )
		 		.add(" A.remark, " )
		 		.add(" A.mode_payment, A.bank_payment, A.no_payment, A.date_payment, ")
		 		.add(" A.curr_code, A.receive_amount AS amount ")
		 		.add(" FROM ").add(TransferCashbankReceive.TABLE_NAME).add(" A ")
		 		.add(" INNER JOIN ").add(TransferCashbank.TABLE_NAME).add(" B ON B.transfer_cashbank_id = A.transfer_cashbank_id ")
		 		.add(" INNER JOIN ").add(CashBankOu.TABLE_NAME).add(" C ON A.cashbank_to_id = C.cashbank_id ")
		 		.add(" WHERE A.tenant_id = :tenantId ")
		 		.add(" AND C.ou_id = :ouId ")
		 		.add(" AND A.cashbank_id = :cashBankId ")
		 		.add(" AND SUBSTR(A.doc_date, 1, 6) = :yearMonth ")
		 		.add(" AND f_authorize_user_role_policy_cashbank(:tenantId, :userId, :roleId, A.cashbank_id) = 1 ")
		 
		 		.add(" UNION ALL ")
		 		
		 		.add(" SELECT B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc, B.doc_no, B.doc_date, ")
		 		.add(" B.ref_id, B.ref_doc_type_id, f_get_doc_desc(B.ref_doc_type_id) AS ref_doc_type_desc, COALESCE(C.payment_doc_no, ' ') AS ref_doc_no, COALESCE(C.payment_doc_date, ' ') AS ref_doc_date,  ")
		 		.add(" B.partner_id, f_get_partner_code(B.partner_id) AS partner_code, f_get_partner_name (B.partner_id) AS partner_name, ")
		 		.add(" A.mode_payment, A.bank_payment, A.no_payment, A.date_payment, ")
		 		.add(" A.curr_code, CASE ")
		 				.add("WHEN B.doc_type_id IN (:docTypeIdCBInOtherRcv,:docTypeIdCBOut) ")
		 				.add("THEN A.cashbank_amount ")
		 				.add("ELSE A.cashbank_amount - COALESCE((SELECT SUM(F.cost_amount) FROM ").add(InOutCashbankCost.TABLE_NAME).add(" F ")
		 												.add(" WHERE F.in_out_cashbank_id = B.in_out_cashbank_id), 0) END AS amount ")
                .add(" FROM ").add(InOutCashbankPayment.TABLE_NAME).add(" A " )
                .add(" INNER JOIN ").add(InOutCashbank.TABLE_NAME).add(" B ON A.in_out_cashbank_id = B.in_out_cashbank_id ")
                .add(" LEFT OUTER JOIN ").add(TrxCashbankBalance.TABLE_NAME).add(" C ON C.trx_cashbank_balance_id = B.ref_id ")
                .add(" INNER JOIN ").add(CashBankOu.TABLE_NAME).add(" E ON A.cashbank_id = E.cashbank_id ")
                .add(" WHERE B.tenant_id = :tenantId ")
                .add(" AND E.ou_id = :ouId ")
                .add(" AND A.cashbank_id = :cashBankId ")
                .add(" AND SUBSTR(B.doc_date, 1, 6) = :yearMonth ")
                .add(" AND f_authorize_user_role_policy_cashbank(:tenantId, :userId, :roleId, A.cashbank_id) = 1 ")
                
                .add(" UNION ALL ")
                
                .add(" SELECT B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc, C.cheque_giro_no AS doc_no, C.cheque_giro_date AS doc_date, ")
                .add(" B.in_out_cashbank_id AS ref_id, B.doc_type_id AS ref_doc_type_id, f_get_doc_desc(B.doc_type_id) AS ref_doc_type_desc, B.doc_no AS ref_doc_no, B.doc_date AS ref_doc_date,  ")
		 		.add(" A.partner_id, f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name (A.partner_id) AS partner_name, ")
		 		.add(" C.mode_payment, A.bank_payment, A.cheque_giro_no, A.realization_date, ")
		 		.add(" A.curr_code, A.cheque_giro_amount AS amount ")
                .add(" FROM ").add(ChequeGiroRealization.TABLE_NAME).add(" A " )
                .add(" INNER JOIN ").add(InOutCashbank.TABLE_NAME).add(" B ON A.in_out_cashbank_id = B.in_out_cashbank_id ")
                .add(" INNER JOIN ").add(InOutCGBalance.TABLE_NAME).add(" C ON C.in_out_cashbank_id = B.in_out_cashbank_id ")
                .add(" INNER JOIN ").add(CashBankOu.TABLE_NAME).add(" D ON C.cashbank_id = D.cashbank_id ")
                .add(" WHERE B.tenant_id = :tenantId ")
                .add(" AND D.ou_id = :ouId ")
                .add(" AND C.cashbank_id = :cashBankId ")
                .add(" AND SUBSTR(B.doc_date, 1, 6) = :yearMonth ")
		 		.add(" AND f_authorize_user_role_policy_cashbank(:tenantId, :userId, :roleId, C.cashbank_id) = 1 ")
		 	
		 		.add(" UNION ALL ")
		 		
		 		.add(" SELECT A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc, A.doc_no, A.doc_date, ")
		 		.add(" :EMPTY_ID AS ref_id, :EMPTY_ID AS ref_doc_type_id, :SPACEVALUE AS ref_doc_type_desc, :SPACEVALUE AS ref_doc_no, :SPACEVALUE AS ref_doc_date, " )
                .add(" :EMPTY_ID AS partner_id, :SPACEVALUE AS partner_code, :SPACEVALUE AS partner_name, " )
		 		.add(" :SPACEVALUE, :SPACEVALUE, :SPACEVALUE, :SPACEVALUE, ")
		 		.add(" C.curr_code, COALESCE(C.cost_amount, 0) AS amount ")
                .add(" FROM ").add(InOutCashbank.TABLE_NAME).add(" A " )
                .add(" INNER JOIN ").add(" ( ")
                .add(" 		SELECT A.in_out_cashbank_id, B.cashbank_id ")
                .add(" 		FROM ").add(InOutCashbank.TABLE_NAME).add(" A ")
                .add(" 		INNER JOIN ").add(ChequeGiroRealization.TABLE_NAME).add(" A.in_out_cashbank_id = B.in_out_cashbank_id ")
                .add("		GROUP BY A.in_out_cashbank_id, B.cashbank_id ")
                .add(" ) ") 
                .add(" B ON A.in_out_cashbank_id = B.in_out_cashbank_id ")
                .add(" INNER JOIN ").add(InOutCashbankCost.TABLE_NAME).add(" C ON C.in_out_cashbank_id = B.in_out_cashbank_id ")
                .add(" INNER JOIN ").add(CashBankOu.TABLE_NAME).add(" D ON B.cashbank_id = D.cashbank_id ")
                .add(" WHERE B.tenant_id = :tenantId ")
                .add(" AND D.ou_id = :ouId ")
                .add(" AND B.cashbank_id = :cashBankId ")
				.add(" AND SUBSTR(A.doc_date, 1, 6) = :yearMonth ")
                .add(" AND A.doc_type_id = :DOC_TYPE_CG_REALIZATION ")
		 		.add(" AND f_authorize_user_role_policy_cashbank(:tenantId, :userId, :roleId, B.cashbank_id) = 1 ")
		 		
		 		.add(" UNION ALL ")
		 		
		 		.add(" SELECT A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc, A.doc_no, A.doc_date, ")
                .add(" B.in_out_cashbank_id AS ref_id, B.doc_type_id AS ref_doc_type_id, f_get_doc_desc(B.doc_type_id) AS ref_doc_type_desc, B.doc_no AS ref_doc_no, B.doc_date AS ref_doc_date,  ")
		 		.add(" A.partner_id, f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name (A.partner_id) AS partner_name, ")
		 		.add(" :CASH, :SPACEVALUE, :SPACEVALUE, :SPACEVALUE, ")
		 		.add(" A.curr_code, SUM(A.amount_debit) - SUM(A.amount_credit) AS amount ") 
		 		.add(" FROM ( ")
                .add(" 		SELECT A.tenant_id, A.ou_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_desc, A.trx_pos_id AS ref_id, ") 
                .add("		A.doc_no, A.doc_date, A.partner_id, C.cashbank_id, C.cashbank_id AS cashbank_target_id, C.curr_code, ") 
                .add("		SUM(B.conversion_amount) AS amount_debit, 0 AS amount_credit ")
                .add(" 		FROM ").add(CashBankConstants.TABLE_TRX_POS).add(" A ")
                .add(" 		INNER JOIN ").add(CashBankConstants.TABLE_TRX_POS_CASH_PAYMENT).add(" B ON A.trx_pos_id = B.trx_pos_id AND A.tenant_id = B.tenant_id AND A.process_no = B.process_no ")
                .add(" 		INNER JOIN ").add(CashBank.TABLE_NAME).add(" C ON B.curr_payment_code = C.curr_code ")
                .add(" 		INNER JOIN ").add(CashBankOu.TABLE_NAME).add(" D ON B.cashbank_id = D.cashbank_id ")
                .add(" 		WHERE A.tenant_id = :tenantId ")
                .add(" 		AND D.ou_id = :ouId ")
                .add(" 		AND D.cashbank_id = :cashBankId ")
                .add(" 		AND SUBSTR(A.doc_date, 1, 6) = :yearMonth ")
                .add(" 		AND f_authorize_user_role_policy_cashbank(:tenantId, :userId, :roleId, D.cashbank_id) = 1 ")
                .add(" 		AND D.flg_cash_bank = :FLAG_CASH ")                
                .add(" 		GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code ")
                .add(" 		UNION ")
                .add(" 		SELECT A.tenant_id, A.ou_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_desc, A.trx_pos_id AS ref_id, ") 
                .add("		A.doc_no, A.doc_date, A.partner_id, C.cashbank_id, C.cashbank_id AS cashbank_target_id, C.curr_code, ") 
                .add("		0 AS amount_debit, SUM(A.total_refund) AS amount_credit ")
                .add(" 		FROM ").add(CashBankConstants.TABLE_TRX_POS).add(" A ")
                .add(" 		INNER JOIN ").add(CashBank.TABLE_NAME).add(" C ON A.curr_code = C.curr_code ")
                .add(" 		INNER JOIN ").add(CashBankOu.TABLE_NAME).add(" D ON B.cashbank_id = D.cashbank_id ")
                .add(" 		WHERE A.tenant_id = :tenantId ")
                .add(" 		AND D.ou_id = :ouId ")
                .add(" 		AND D.cashbank_id = :cashBankId ")
                .add(" 		AND SUBSTR(A.doc_date, 1, 6) = :yearMonth ")
                .add(" 		AND f_authorize_user_role_policy_cashbank(:tenantId, :userId, :roleId, D.cashbank_id) = 1 ")
                .add(" 		AND D.flg_cash_bank = :FLAG_CASH ")                
                .add(" 		GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code ")
                .add(" ) A ")
                .add(" GROUP BY A.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date ")
                
                .add(" UNION ALL ")
                
                .add(" SELECT A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc, A.doc_no, A.doc_date, ")
                .add(" B.in_out_cashbank_id AS ref_id, B.doc_type_id AS ref_doc_type_id, f_get_doc_desc(B.doc_type_id) AS ref_doc_type_desc, B.doc_no AS ref_doc_no, B.doc_date AS ref_doc_date,  ")
		 		.add(" A.partner_id, f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name (A.partner_id) AS partner_name, ")
		 		.add(" :CASH, :SPACEVALUE, :SPACEVALUE, :SPACEVALUE, ")
		 		.add(" A.curr_code, SUM(A.amount_debit) - SUM(A.amount_credit) AS amount ") 
		 		.add(" FROM ( ")
                .add(" 		SELECT A.tenant_id, A.ou_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_desc, A.trx_pos_id AS ref_id, ") 
                .add("		A.doc_no, A.doc_date, A.partner_id, C.cashbank_id, C.cashbank_id AS cashbank_target_id, C.curr_code, ") 
                .add("		SUM(B.conversion_amount) AS amount_debit, 0 AS amount_credit ")
                .add(" 		FROM ").add(CashBankConstants.TABLE_TRX_POS).add(" A ")
                .add(" 		INNER JOIN ").add(CashBankConstants.TABLE_TRX_POS_CASH_PAYMENT).add(" B ON A.trx_pos_id = B.trx_pos_id AND A.tenant_id = B.tenant_id AND A.process_no = B.process_no ")
                .add(" 		INNER JOIN ").add(CashBank.TABLE_NAME).add(" C ON B.curr_payment_code = C.curr_code ")
                .add(" 		INNER JOIN ").add(CashBankOu.TABLE_NAME).add(" D ON B.cashbank_id = D.cashbank_id ")
                .add(" 		WHERE A.tenant_id = :tenantId ")
                .add(" 		AND D.ou_id = :ouId ")
                .add(" 		AND D.cashbank_id = :cashBankId ")
                .add(" 		AND SUBSTR(A.doc_date, 1, 6) = :yearMonth ")
                .add(" 		AND f_authorize_user_role_policy_cashbank(:tenantId, :userId, :roleId, D.cashbank_id) = 1 ")
                .add(" 		AND D.flg_cash_bank = :FLAG_CASH ")                
                .add(" 		GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code ")
                .add(" 		UNION ")
                .add(" 		SELECT A.tenant_id, A.ou_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_desc, A.trx_pos_id AS ref_id, ") 
                .add("		A.doc_no, A.doc_date, A.partner_id, C.cashbank_id, C.cashbank_id AS cashbank_target_id, C.curr_code, ") 
                .add("		0 AS amount_debit, SUM(A.total_refund) AS amount_credit ")
                .add(" 		FROM ").add(CashBankConstants.TABLE_TRX_POS).add(" A ")
                .add(" 		INNER JOIN ").add(CashBank.TABLE_NAME).add(" C ON A.curr_code = C.curr_code ")
                .add(" 		INNER JOIN ").add(CashBankOu.TABLE_NAME).add(" D ON B.cashbank_id = D.cashbank_id ")
                .add(" 		WHERE A.tenant_id = :tenantId ")
                .add(" 		AND D.ou_id = :ouId ")
                .add(" 		AND D.cashbank_id = :cashBankId ")
                .add(" 		AND SUBSTR(A.doc_date, 1, 6) = :yearMonth ")
                .add(" 		AND f_authorize_user_role_policy_cashbank(:tenantId, :userId, :roleId, D.cashbank_id) = 1 ")
                .add(" 		AND D.flg_cash_bank = :FLAG_CASH ")                
                .add(" 		GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code ")
                .add(" ) A ")
                .add(" AND EXISTS ( ")
                .add(" 		SELECT 1 ")
                .add(" 		FROM ").add(CashBankConstants.TABLE_TRX_LOG_VOIDED_POS).add(" B ")
                .add(" 		WHERE A.tenant_id = B.tenant_id ")
                .add(" 		AND A.doc_no = B.doc_no ")
                .add(" 		AND A.doc_date = B.doc_date ")
                .add(" 		AND A.ou_id = B.ou_id ")
                .add(" ) ")
                .add(" GROUP BY A.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date ")
		 		.add(" ) A ")
		 		.add(" WHERE NOT EXISTS ( ")
		 		.add(" SELECT 1 ")
		 		.add(" FROM ").add(Reconcilement.TABLE_NAME).add(" B ")
		 		.add(" INNER JOIN ").add(ReconcilementItem.TABLE_NAME).add(" C ")
		 		.add(" WHERE B.tenant_id = :tenantId ")
		 		.add(" AND A.ou_id = :ouId")
		 		.add(" AND A.cashbank_id = :cashBankId")
		 		.add(" AND A.year_month = :yearMonth")
		 		.add(" AND C.trx_doc_type_id = A.doc_type_id")
		 		.add(" AND C.trx_doc_no = A.doc_no")
		 		.add(" AND C.trx_doc_date = A.doc_date")
		 		.add(" AND C.partner_id = A.partner_id")
		 		.add(" AND C.ref_doc_type_id = A.ref_doc_type_id")
		 		.add(" AND C.ref_id = A.ref_id")
		 		.add(" AND C.ref_doc_no = A.ref_doc_no")
		 		.add(" AND C.ref_doc_date = A.ref_doc_date")
		 		.add(" ) ");
		 
		 Query query = inOutCashbankDao.createNativeQuery(builder.toString());
		 query.setParameter("tenantId", tenantId);
		 query.setParameter("ouId", ouId);
		 query.setParameter("yearMonth", yearMonth);
		 query.setParameter("userId", userId);
		 query.setParameter("roleId", roleId);
		 query.setParameter("docTypeIdCBInOtherRcv", CashBankConstants.DOCUMENT_CASHBANK_IN_OTHER_RECEIVE);
		 query.setParameter("docTypeIdCBOut", CashBankConstants.DOCUMENT_CASHBANK_OUT);
		 query.setParameter("DOC_TYPE_CG_REALIZATION", CashBankConstants.DOCUMENT_CHEQUE_GIRO_REALIZATION);
		 query.setParameter("CASH", CashBankConstants.CASH);
		 query.setParameter("FLAG_CASH", CashBankConstants.FLAG_CASH);
		 query.setParameter("cashBankId", cashBankId);
		 query.setParameter("SPACEVALUE", GeneralConstants.SPACE_VALUE);
		 query.setParameter("EMPTY_ID", GeneralConstants.NULL_REF_VALUE_LONG);
		 
		 resultList = query.getResultList();		 
		 outputDto.putList("cashBankTrxList", DtoUtil.createDtoListFromArray(resultList, "type", "docTypeId", "docTypeDesc", "docNo", "docDate", 
				 "partnerId", "partnerCode", "partnerName", "currCode", "amount", "refDocNo", "refDocDate", "modePayment", "bankPayment"));
		 
		 return outputDto;
	 }
 }