1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
public List<AffiliateStatementItemsDTO> retrieveAffiliates(int aMin, int aMax, LocalDate yearBegin, LocalDate yearEnd)
{
logger.info("We are going to retrieve affiliates from aMin = " + aMin + " and aMax = " + aMax);
long dbBegin = System.nanoTime();
EntityManager em;
List<AffiliateStatementItemsDTO> result;
List<String> journalIds1 = Arrays.asList("83", "93", "94", "95", "96");
List<String> vatBaseIds1 = Arrays.asList("44", "48");
List<Integer> organizations = Arrays.asList(8000, 9000);
String firstType = "12";
String secondType = "29";
String thirdType = "18";
List<String> types1 = Arrays.asList("06", "41", "42");
List<String> journalIds2 = Arrays.asList("83", "93", "94", "95", "96");
List<String> vatBaseIds2 = Arrays.asList("44", "48");
Integer org = 9000;
List<String> types2 = Arrays.asList("20", "21", "22", "29");
String sql1 = "select a1.no_aff as aff1, a1.COD_LAN as lan, a1.PRE_TVA as pre_tva, a1.NUM_TVA as num_tva, a1.STA_TVA as sta_tva, a1.NO_TVA_EUR as no_tva_eur, a1.NO_AGR_BUR as no_agr_bur, ar1.DEN as den, ar1.ADR as adr, ar1.NO_COD_POS as no_cod_pos, ar1.LOC as loc, sum(c1.mt_cre) as sum1 from AFF a1 "
+ "inner join ADR ar1 on a1.NO_AFF = ar1.NO_AFF and a1.COD_ADR_SOC = ar1.COD_ADR and a1.COD_LAN = ar1.COD_LAN "
+ "inner join REL r1 on a1.NO_AFF = r1.NO_AFF "
+ "inner join CRE c1 on c1.NO_AFF = r1.NO_AFF and c1.NO_JOU = r1.NO_JOU and c1.EXE_REL = r1.EXE_REL and c1.NO_REL = r1.NO_REL "
+ "where a1.NO_AFF between ?1 and ?2 "
+ "and r1.DAT_EXP between ?3 and ?4 "
+ "and r1.NO_JOU in ?5 "
+ "and c1.NO_BAS_TVA not in ?6 "
+ "and c1.NO_ORG in ?7 "
+ "and (c1.TYP_CRE < ?8 OR c1.TYP_CRE > ?9 OR c1.TYP_CRE = ?10 ) "
+ "and c1.TYP_CRE not in ?11 "
+ "group by a1.NO_AFF, a1.COD_LAN, a1.PRE_TVA, a1.NUM_TVA, a1.STA_TVA, a1.NO_TVA_EUR, a1.NO_AGR_BUR, ar1.DEN, ar1.ADR, ar1.NO_COD_POS, ar1.LOC "
+ "order by a1.NO_AFF";
String sql2 = "select a2.no_aff as aff2, sum(c2.mt_cre) as sum2 from AFF a2 "
+ "inner join ADR ar2 on a2.NO_AFF = ar2.NO_AFF and a2.COD_ADR_SOC = ar2.COD_ADR and a2.COD_LAN = ar2.COD_LAN "
+ "inner join REL r2 on a2.NO_AFF = r2.NO_AFF "
+ "inner join CRE c2 on c2.NO_AFF = r2.NO_AFF and c2.no_jou = r2.no_jou and c2.exe_rel = r2.exe_rel and c2.no_rel = r2.no_rel "
+ "where a2.NO_AFF between ?12 and ?13 "
+ "and r2.DAT_EXP between ?14 and ?15 "
+ "and r2.no_jou in ?16 "
+ "and c2.no_bas_tva not in ?17 "
+ "and c2.no_org = ?18 "
+ "and c2.typ_cre in ?19 "
+ "group by a2.NO_AFF "
+ "order by a2.NO_AFF";
String sql = "select aff1, aff2, sum1, sum2, lan, pre_tva, num_tva, sta_tva, no_tva_eur, no_agr_bur, den, adr, no_cod_pos, loc from ( " + sql1 + " ) full outer join ( " + sql2 + " ) on aff1 = aff2";
em = JpaUtil.getEntityManagerFactory().createEntityManager();
em.getTransaction().begin();
Query query = em.createNativeQuery(sql, "AffiliateAddressStatements")
.setParameter(1, aMin)
.setParameter(2, aMax)
.setParameter(3, Date.valueOf(yearBegin), TemporalType.DATE)
.setParameter(4, Date.valueOf(yearEnd),TemporalType.DATE)
.setParameter(5, journalIds1)
.setParameter(6, vatBaseIds1)
.setParameter(7, organizations)
.setParameter(8, firstType)
.setParameter(9, secondType)
.setParameter(10, thirdType)
.setParameter(11, types1)
.setParameter(12, aMin)
.setParameter(13, aMax)
.setParameter(14, Date.valueOf(yearBegin), TemporalType.DATE)
.setParameter(15, Date.valueOf(yearEnd), TemporalType.DATE)
.setParameter(16, journalIds2)
.setParameter(17, vatBaseIds2)
.setParameter(18, org)
.setParameter(19, types2);
result = query.getResultList();
em.getTransaction().commit();
long dbEnd = System.nanoTime();
logger.info("We get all the data (" + result.size() + ") in " + (dbEnd - dbBegin) / 1000000 + " milliseconds.");
logger.info("------------------------------------------");
return result; |
