import com.haulmont.cuba.core.global.*

import com.groupstp.rtneo.entity.*

import com.google.gson.Gson;

import com.google.gson.JsonObject;

import com.google.gson.JsonParser;

import com.haulmont.cuba.core.global.PersistenceHelper;

import com.haulmont.cuba.core.*

DataManager dataManager = AppBeans.get(DataManager.NAME)

Persistence persistence= AppBeans.get(Persistence.NAME)

Transaction tr = persistence.createTransaction()

EntityManager em = persistence.getEntityManager();

Query query = em.createNativeQuery("with\n" +

"periods as(\n" +

"SELECT \n" +

" distinct a.period as per\n" +

"FROM \n" +

" public.rtneo_contragent c, \n" +

" public.rtneo_contract ct, \n" +

" public.rtneo_contract_position cp, \n" +

" public.rtneo_accrual a\n" +

"WHERE \n" +

" ct.contragent_id = c.id AND\n" +

" cp.contract_id = ct.id AND\n" +

" a.contract_position_id = cp.id AND\n" +

" c.personal_account = 'ААА3812525543'\n" +

" and a.delete_ts isnull\n" +

" order by a.period),\n" +

"contracts as(\n" +

"select\n" +

"p.per as perct,\n" +

"(select ct.id\n" +

"from \n" +

"public.rtneo_contract ct join\n" +

"public.rtneo_contragent c on c.id = ct.contragent_id\n" +

"where\n" +

"c.personal_account = 'ААА3812525543'\n" +

"and ct.create_ts = \n" +

"(select\n" +

"max(ct.create_ts)\n" +

"from \n" +

"public.rtneo_contract ct join\n" +

"public.rtneo_contragent c on c.id = ct.contragent_id\n" +

"where\n" +

"c.personal_account = 'ААА3812525543'\n" +

" and ct.accepted = true\n" +

" and ct.delete_ts is null\n" +

"and p.per between ct.from_ and ct.before_)\n" +

") as contract\n" +

"from \n" +

"periods p)\n" +

"\n" +

"SELECT\n" +

"a.period, a.document_number\n" +

"FROM \n" +

" contracts ct,\n" +

" rtneo_accrual a left join\n" +

" public.rtneo_contract_position cp\n" +

" on a.contract_position_id = cp.id\n" +

"where\n" +

" a.period = ct.perct\n" +

" and ct.contract = cp.contract_id\n" +

" and a.delete_ts is null\n" +

"order by a.period\n" +

"limit 200");

//query.setParameter(1, "%Company%");

List list = query.getResultList();

for (Iterator it = list.iterator(); it.hasNext(); ) {

Object[] row = (Object[]) it.next();

def per = row[0];

def name = row[1];

_("${per} = ${name}")

}

/**

* Логирование

*/

import com.haulmont.cuba.core.app.serialization.EntitySerializationAPI;

import com.groupstp.rtneo.util.JsonUtil;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

private _(Object obj, String... options){

if(obj == null){log("LOG.ERROR: Object is null!!!");return}

if(options.size() == 0){log(obj)}

for(def option : options){

if(option.equals("str")){log(obj)}

if(option.equals("for")){obj.each({_(it)})}

if(option.equals("json")){

EntitySerializationAPI entitySerializationAPI = AppBeans.get(EntitySerializationAPI.NAME)

try{log(entitySerializationAPI.toJson(obj))}

catch(Exception e){log("LOG.ERROR: JSON entity serialization failed")}

}

if(option.equals("objJson")){

JsonUtil jsonUtil = AppBeans.get(JsonUtil.NAME)

try{log.(jsonUtil.toJson(obj))}

catch(Exception e){log("LOG.ERROR: JSON object serialization failed")}

}

if(option.equals("date")){

DateFormat df = new SimpleDateFormat("dd.MM.yyyy");

try{log(df.format(obj))}

catch(Exception e){log("LOG.ERROR: Failed date format")}

}

}

}

private log(Object obj){log.debug(obj)}