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.crmclient.business;
35
36 import fr.paris.lutece.plugins.crmclient.service.CRMClientPlugin;
37 import fr.paris.lutece.portal.service.util.AppLogService;
38 import fr.paris.lutece.util.sql.DAOUtil;
39 import fr.paris.lutece.util.sql.Transaction;
40
41 import java.io.ByteArrayOutputStream;
42 import java.io.IOException;
43 import java.io.InputStream;
44 import java.io.ObjectInputStream;
45 import java.io.ObjectOutputStream;
46
47
48
49
50
51
52 public class CRMItemQueueDAO implements ICRMItemQueueDAO
53 {
54 private static final String SQL_QUERY_NEW_PK = " SELECT max(id_crm_queue) FROM crm_client_crm_queue ";
55 private static final String SQL_QUERY_SELECT_NEXT_CRM_ITEM_QUEUE_ID = " SELECT min(id_crm_queue) FROM crm_client_crm_queue WHERE is_locked = 0 ";
56 private static final String SQL_QUERY_SELECT_COUNT = " SELECT COUNT(id_crm_queue) FROM crm_client_crm_queue ";
57 private static final String SQL_QUERY_LOAD_CRM_ITEM = " SELECT id_crm_queue, crm_item FROM crm_client_crm_item WHERE id_crm_queue = ? ";
58 private static final String SQL_QUERY_INSERT = " INSERT INTO crm_client_crm_queue( id_crm_queue ) VALUES( ? ) ";
59 private static final String SQL_QUERY_INSERT_CRM_ITEM = " INSERT INTO crm_client_crm_item(id_crm_queue, crm_item) VALUES( ?,? ) ";
60 private static final String SQL_QUERY_LOCK_CRM_ITEM = " UPDATE crm_client_crm_queue SET is_locked = 1 WHERE id_crm_queue = ? ";
61 private static final String SQL_QUERY_DELETE = " DELETE FROM crm_client_crm_queue WHERE id_crm_queue = ? ";
62 private static final String SQL_QUERY_DELETE_CRM_ITEM = " DELETE FROM crm_client_crm_item WHERE id_crm_queue = ? ";
63
64
65
66
67
68
69 private int newPrimaryKey( )
70 {
71 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, CRMClientPlugin.getPlugin( ) );
72 daoUtil.executeQuery( );
73
74 int nKey = 1;
75
76 if ( daoUtil.next( ) )
77 {
78 nKey = daoUtil.getInt( 1 ) + 1;
79 }
80
81 daoUtil.free( );
82
83 return nKey;
84 }
85
86
87
88
89 @Override
90 public int nextCRMItemQueueId( )
91 {
92 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NEXT_CRM_ITEM_QUEUE_ID, CRMClientPlugin.getPlugin( ) );
93
94 daoUtil.executeQuery( );
95
96 int nIdCRMItemQueue = -1;
97
98 if ( daoUtil.next( ) )
99 {
100 nIdCRMItemQueue = daoUtil.getInt( 1 );
101 }
102
103 daoUtil.free( );
104
105 return nIdCRMItemQueue;
106 }
107
108
109
110
111 @Override
112 public void lockCRMItemQueue( int nIdCRMItemQueue )
113 {
114 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_LOCK_CRM_ITEM, CRMClientPlugin.getPlugin( ) );
115 daoUtil.setInt( 1, nIdCRMItemQueue );
116 daoUtil.executeUpdate( );
117 daoUtil.free( );
118 }
119
120
121
122
123 @Override
124 public synchronized void insert( CRMItemQueue crmItemQueue )
125 {
126 Transaction transaction = null;
127
128 try
129 {
130 ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream( );
131 ObjectOutputStream objectOutputStream;
132 objectOutputStream = new ObjectOutputStream( byteArrayOutputStream );
133 objectOutputStream.writeObject( crmItemQueue.getCRMItem( ) );
134 objectOutputStream.close( );
135 byteArrayOutputStream.close( );
136
137 transaction = new Transaction( CRMClientPlugin.getPlugin( ) );
138
139 int nNewPrimaryKey = newPrimaryKey( );
140 crmItemQueue.setIdCRMItemQueue( nNewPrimaryKey );
141 transaction.prepareStatement( SQL_QUERY_INSERT );
142 transaction.getStatement( ).setInt( 1, nNewPrimaryKey );
143 transaction.executeStatement( );
144 transaction.prepareStatement( SQL_QUERY_INSERT_CRM_ITEM );
145 transaction.getStatement( ).setInt( 1, nNewPrimaryKey );
146 transaction.getStatement( ).setBytes( 2, byteArrayOutputStream.toByteArray( ) );
147 transaction.executeStatement( );
148
149 transaction.commit( );
150 }
151 catch( Exception e )
152 {
153 if ( transaction != null )
154 {
155 transaction.rollback( e );
156 }
157
158 AppLogService.error( e );
159 }
160 }
161
162
163
164
165 @Override
166 public CRMItemQueue load( int nIdCRMItemQueue )
167 {
168 CRMItemQueue crmItemQueue = null;
169 ICRMItem crmItem = null;
170 InputStream inputStream;
171 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_LOAD_CRM_ITEM, CRMClientPlugin.getPlugin( ) );
172 daoUtil.setInt( 1, nIdCRMItemQueue );
173 daoUtil.executeQuery( );
174
175 if ( daoUtil.next( ) )
176 {
177 crmItemQueue = new CRMItemQueue( );
178 crmItemQueue.setIdCRMItemQueue( daoUtil.getInt( 1 ) );
179 inputStream = daoUtil.getBinaryStream( 2 );
180
181 try
182 {
183 ObjectInputStream objectInputStream = new ObjectInputStream( inputStream );
184 crmItem = (ICRMItem) objectInputStream.readObject( );
185 objectInputStream.close( );
186 inputStream.close( );
187 }
188 catch( IOException e )
189 {
190 AppLogService.error( e );
191 }
192 catch( ClassNotFoundException e )
193 {
194 AppLogService.error( e );
195 }
196
197 crmItemQueue.setCRMItem( crmItem );
198 }
199
200 daoUtil.free( );
201
202 return crmItemQueue;
203 }
204
205
206
207
208 @Override
209 public void delete( int nIdCRMItemQueue )
210 {
211 Transaction transaction = new Transaction( CRMClientPlugin.getPlugin( ) );
212
213 try
214 {
215 transaction.prepareStatement( SQL_QUERY_DELETE_CRM_ITEM );
216 transaction.getStatement( ).setInt( 1, nIdCRMItemQueue );
217 transaction.executeStatement( );
218 transaction.prepareStatement( SQL_QUERY_DELETE );
219 transaction.getStatement( ).setInt( 1, nIdCRMItemQueue );
220 transaction.executeStatement( );
221 transaction.commit( );
222 }
223
224 catch( Exception e )
225 {
226 transaction.rollback( e );
227 AppLogService.error( e );
228 }
229 }
230
231
232
233
234 @Override
235 public int getCountCRMItem( )
236 {
237 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COUNT, CRMClientPlugin.getPlugin( ) );
238 daoUtil.executeQuery( );
239
240 int nCount = 0;
241
242 if ( daoUtil.next( ) )
243 {
244 nCount = daoUtil.getInt( 1 );
245 }
246
247 daoUtil.free( );
248
249 return nCount;
250 }
251 }