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 package fr.paris.lutece.plugins.identityimport.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38 import org.apache.commons.collections4.CollectionUtils;
39
40 import java.sql.Statement;
41 import java.util.ArrayList;
42 import java.util.List;
43 import java.util.Optional;
44 import java.util.stream.Collectors;
45
46
47
48
49 public final class CandidateIdentityHistoryDAO implements ICandidateIdentityHistoryDAO
50 {
51
52 private static final String QUERY_SELECT_ALL = "SELECT ih.id_history, ih.id_wf_resource_history, ih.status, ih.comment, wrh.id_resource as id_candidate_identity FROM identityimport_candidate_identity_history ih JOIN workflow_resource_history wrh on ih.id_wf_resource_history = wrh.id_history";
53 private static final String ID_LIST = "%{id_list}";
54 private final static String SQL_QUERY_SELECT_BY_ID = QUERY_SELECT_ALL + " WHERE id_history = ?";
55 private final static String SQL_QUERY_SELECT_BY_WF_RESOURCE_ID = QUERY_SELECT_ALL + " WHERE id_wf_resource_history = ?";
56
57 private final static String SQL_QUERY_INSERT = "INSERT INTO identityimport_candidate_identity_history(id_wf_resource_history, status, comment) VALUES (?, ?, ?)";
58
59 private final static String SQL_QUERY_DELETE = "DELETE FROM identityimport_candidate_identity_history WHERE id_history = ?";
60
61 private static final String SQL_QUERY_DELETE_LISTS = "DELETE wrh, cih FROM workflow_resource_history wrh JOIN identityimport_candidate_identity_history cih ON wrh.id_history = cih.id_wf_resource_history where wrh.id_resource IN ( "
62 + ID_LIST + " )";
63
64 private final static String SQL_QUERY_UPDATE = "UPDATE identityimport_candidate_identity_history SET status = ?, comment = ? WHERE id_wf_resource_history = ?";
65
66 private final static String SQL_QUERY_SELECT_ALL = QUERY_SELECT_ALL
67 + " JOIN workflow_resource_history rh ON rh.id_history = ih.id_wf_resource_history WHERE rh.id_resource = ?";
68
69
70
71
72 @Override
73 public void insert( CandidateIdentityHistory candidateIdentityHistory, Plugin plugin )
74 {
75 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
76 {
77 int nIndex = 1;
78 daoUtil.setInt( nIndex++, candidateIdentityHistory.getWfResourceHistoryId( ) );
79 daoUtil.setString( nIndex++, candidateIdentityHistory.getStatus( ) );
80 daoUtil.setString( nIndex, candidateIdentityHistory.getComment( ) );
81
82 daoUtil.executeUpdate( );
83 if ( daoUtil.nextGeneratedKey( ) )
84 {
85 candidateIdentityHistory.setId( daoUtil.getGeneratedKeyInt( 1 ) );
86 }
87 }
88
89 }
90
91
92
93
94 @Override
95 public Optional<CandidateIdentityHistory> select( int nId, Plugin plugin )
96 {
97 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID, plugin ) )
98 {
99 daoUtil.setInt( 1, nId );
100 daoUtil.executeQuery( );
101 CandidateIdentityHistory candidateIdentityHistory = null;
102
103 if ( daoUtil.next( ) )
104 {
105 candidateIdentityHistory = this.getCandidateIdentityHistory( daoUtil );
106 }
107
108 return Optional.ofNullable( candidateIdentityHistory );
109 }
110 }
111
112
113
114
115 @Override
116 public Optional<CandidateIdentityHistory> selectByWfHistory( int nWfResourceHistoryId, Plugin plugin )
117 {
118 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_WF_RESOURCE_ID, plugin ) )
119 {
120 daoUtil.setInt( 1, nWfResourceHistoryId );
121 daoUtil.executeQuery( );
122 CandidateIdentityHistory candidateIdentityHistory = null;
123
124 if ( daoUtil.next( ) )
125 {
126 candidateIdentityHistory = this.getCandidateIdentityHistory( daoUtil );
127 }
128 return Optional.ofNullable( candidateIdentityHistory );
129 }
130 }
131
132
133
134
135 @Override
136 public void delete( int nKey, Plugin plugin )
137 {
138 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
139 {
140 daoUtil.setInt( 1, nKey );
141 daoUtil.executeUpdate( );
142 }
143 }
144
145
146
147
148 @Override
149 public void deleteList( final List<Integer> idList, Plugin plugin )
150 {
151 if ( CollectionUtils.isNotEmpty( idList ) )
152 {
153 final String query = SQL_QUERY_DELETE_LISTS.replace( ID_LIST, idList.stream( ).map( String::valueOf ).collect( Collectors.joining( "," ) ) );
154 try ( final DAOUtil daoUtil = new DAOUtil( query, plugin ) )
155 {
156 daoUtil.executeUpdate( );
157 }
158 }
159 }
160
161
162
163
164 @Override
165 public void update( CandidateIdentityHistory candidateIdentityHistory, Plugin plugin )
166 {
167 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
168 {
169 int nIndex = 1;
170
171 daoUtil.setString( nIndex++, candidateIdentityHistory.getStatus( ) );
172 daoUtil.setString( nIndex++, candidateIdentityHistory.getComment( ) );
173 daoUtil.setInt( nIndex, candidateIdentityHistory.getWfResourceHistoryId( ) );
174
175 daoUtil.executeUpdate( );
176 }
177 }
178
179
180
181
182 @Override
183 public List<CandidateIdentityHistory> selectAll( int nCandidateIdentityId, Plugin plugin )
184 {
185 final List<CandidateIdentityHistory> histories = new ArrayList<>( );
186 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin ) )
187 {
188 daoUtil.setInt( 1, nCandidateIdentityId );
189 daoUtil.executeQuery( );
190
191 while ( daoUtil.next( ) )
192 {
193 histories.add( this.getCandidateIdentityHistory( daoUtil ) );
194 }
195
196 return histories;
197 }
198 }
199
200 private CandidateIdentityHistory getCandidateIdentityHistory( final DAOUtil daoUtil )
201 {
202 final CandidateIdentityHistorysiness/CandidateIdentityHistory.html#CandidateIdentityHistory">CandidateIdentityHistory history = new CandidateIdentityHistory( );
203 int nIndex = 1;
204 history.setId( daoUtil.getInt( nIndex++ ) );
205 history.setWfResourceHistoryId( daoUtil.getInt( nIndex++ ) );
206 history.setStatus( daoUtil.getString( nIndex++ ) );
207 history.setComment( daoUtil.getString( nIndex++ ) );
208 history.setCandidateIdentityId( daoUtil.getInt( nIndex ) );
209 return history;
210 }
211 }