|
0
|
1 // Filename : db.cpp
|
|
21
|
2 // Last Change: 12-Dec-2014.
|
|
0
|
3 //
|
|
|
4
|
|
13
|
5 #include <wx/tokenzr.h>
|
|
0
|
6 #include "db.h"
|
|
|
7 #include "wx/wxsqlite3.h"
|
|
|
8
|
|
2
|
9 //********** HHS-DB **********//
|
|
13
|
10 /* 被保険者台帳を更新 */
|
|
|
11 void UpdateHhs( wxArrayString info )
|
|
|
12 {
|
|
|
13 long n = info.GetCount();
|
|
|
14
|
|
|
15 wxProgressDialog pd( wxT("進行状況"), wxT("処理開始..."), n, NULL, wxPD_APP_MODAL|wxPD_REMAINING_TIME|wxPD_AUTO_HIDE );
|
|
|
16 pd.SetSize( wxSize( 320, 140 ) );
|
|
|
17
|
|
|
18 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
|
|
|
19 wxRemoveFile( gszFile );
|
|
|
20
|
|
|
21 wxSQLite3Database hhsdb;
|
|
|
22 hhsdb.Open( gszFile );
|
|
|
23 hhsdb.Begin();
|
|
|
24
|
|
|
25 wxString sql = wxT( "PRAGMA foregin_keys=OFF" );
|
|
|
26 wxSQLite3Statement stmt = hhsdb.PrepareStatement( sql );
|
|
|
27 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
28
|
|
|
29 sql = wxT( "CREATE TABLE 'hhs_master' ( hhsno text PRIMARY KEY, birth text, name text, kana text, addr text, sex text )" );
|
|
|
30 stmt = hhsdb.PrepareStatement( sql );
|
|
|
31 q = stmt.ExecuteQuery();
|
|
|
32
|
|
|
33 wxString hhsno, birth, name, kana, addr, sex;
|
|
|
34 for ( long i = 0; i < n; i++ ) {
|
|
|
35
|
|
|
36 info[i].Replace( wxT("\""), wxEmptyString, true );
|
|
|
37
|
|
|
38 wxStringTokenizer token( info[i], wxT(",") );
|
|
|
39 hhsno = token.GetNextToken();
|
|
|
40 birth = token.GetNextToken();
|
|
|
41 name = token.GetNextToken();
|
|
|
42 kana = token.GetNextToken();
|
|
|
43 addr = token.GetNextToken();
|
|
|
44 sex = token.GetNextToken();
|
|
|
45
|
|
|
46 stmt = hhsdb.PrepareStatement( "INSERT INTO hhs_master VALUES( ?, ?, ?, ?, ?, ? );" );
|
|
|
47 stmt.Bind( 1, hhsno );
|
|
|
48 stmt.Bind( 2, birth );
|
|
|
49 stmt.Bind( 3, name );
|
|
|
50 stmt.Bind( 4, kana );
|
|
|
51 stmt.Bind( 5, addr );
|
|
|
52 stmt.Bind( 6, sex );
|
|
|
53 stmt.ExecuteQuery();
|
|
|
54 stmt.Finalize();
|
|
|
55
|
|
|
56 if ( i % 1000 == 0 ) {
|
|
|
57 pd.Update( i, wxString::Format( wxT("%d / %d done."), i, n ) );
|
|
|
58 }
|
|
|
59 }
|
|
|
60
|
|
|
61 hhsdb.Commit();
|
|
|
62 hhsdb.Close();
|
|
|
63 }
|
|
|
64
|
|
2
|
65 /* 被保番で被保険者情報を取得 */
|
|
1
|
66 wxString GetHhsInfoByHhsNo( wxString hhsno )
|
|
0
|
67 {
|
|
1
|
68 wxString name, addr;
|
|
0
|
69
|
|
|
70 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
|
|
|
71 wxSQLite3Database hhsdb;
|
|
|
72 hhsdb.Open( gszFile );
|
|
|
73
|
|
2
|
74 wxSQLite3Statement stmt = hhsdb.PrepareStatement( "SELECT name, addr FROM hhs_master WHERE hhsno = ?" );
|
|
0
|
75 stmt.Bind( 1, hhsno );
|
|
|
76 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
77 if ( !q.IsNull(0) ) {
|
|
|
78 while ( q.NextRow() ) {
|
|
|
79 name = q.GetString(0);
|
|
1
|
80 addr = q.GetString(1);
|
|
0
|
81 }
|
|
|
82 }
|
|
|
83 stmt.Finalize();
|
|
|
84 hhsdb.Close();
|
|
|
85
|
|
1
|
86 if ( name.IsEmpty() ) {
|
|
|
87 return wxEmptyString;
|
|
|
88 }
|
|
|
89 else {
|
|
|
90 return name + wxT("_") + addr;
|
|
|
91 }
|
|
0
|
92 }
|
|
1
|
93
|
|
9
|
94 // 被保険者番号リストから氏名を取得
|
|
|
95 wxArrayString GetHhsInfoByHhsNoList( wxArrayString hhsno )
|
|
|
96 {
|
|
|
97 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
|
|
|
98 wxSQLite3Database hhsdb;
|
|
|
99 hhsdb.Open( gszFile );
|
|
|
100
|
|
|
101 wxString sql = wxT( "SELECT name FROM hhs_master WHERE hhsno = ?;" );
|
|
|
102 wxSQLite3Statement stmt;
|
|
|
103 wxSQLite3ResultSet q;
|
|
|
104
|
|
|
105 wxArrayString result;
|
|
|
106 for ( unsigned int i = 0; i < hhsno.GetCount(); i++ ) {
|
|
|
107 wxString str = hhsno[i];
|
|
|
108 str.Append( wxT("_") );
|
|
|
109
|
|
|
110 stmt = hhsdb.PrepareStatement( sql );
|
|
|
111 stmt.Bind( 1, hhsno[i] );
|
|
|
112 q = stmt.ExecuteQuery();
|
|
|
113 if ( !q.IsNull(0) ) {
|
|
|
114 while ( q.NextRow() ) {
|
|
|
115 str.Append( q.GetString(0) );
|
|
|
116 }
|
|
|
117 }
|
|
|
118
|
|
|
119 result.Add( str );
|
|
|
120 }
|
|
|
121 stmt.Finalize();
|
|
|
122 hhsdb.Close();
|
|
|
123
|
|
|
124 return result;
|
|
|
125 }
|
|
|
126
|
|
2
|
127 // 氏名カナで被保険者情報を検索
|
|
1
|
128 wxArrayString GetHhsInfoByKana( wxString kana, bool fuzzy )
|
|
0
|
129 {
|
|
|
130 wxArrayString data;
|
|
|
131
|
|
|
132 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
|
|
|
133 wxSQLite3Database hhsdb;
|
|
|
134 hhsdb.Open( gszFile );
|
|
|
135
|
|
6
|
136 wxString sql = wxT( "SELECT hhsno, kana, name, birth, addr FROM hhs_master " );
|
|
|
137 if ( fuzzy ) {
|
|
|
138 kana = wxT("%") + kana + wxT("%");
|
|
|
139 sql += wxT( "WHERE kana LIKE ? ORDER BY kana, birth;" );
|
|
|
140 }
|
|
|
141 else {
|
|
|
142 sql += wxT( "WHERE kana = ? ORDER BY kana, birth;" );
|
|
|
143 }
|
|
1
|
144
|
|
|
145 wxSQLite3Statement stmt = hhsdb.PrepareStatement( sql );
|
|
0
|
146 stmt.Bind( 1, kana );
|
|
|
147 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
148
|
|
|
149 if ( !q.IsNull(0) ) {
|
|
|
150 wxString str;
|
|
|
151 while ( q.NextRow() ) {
|
|
|
152 str = q.GetString(0);
|
|
|
153 for ( int i=1; i<5; i++ ) {
|
|
|
154 str += "_" + q.GetString(i);
|
|
|
155 }
|
|
|
156 data.Add( str );
|
|
|
157 }
|
|
|
158 }
|
|
|
159 stmt.Finalize();
|
|
|
160 hhsdb.Close();
|
|
|
161
|
|
|
162 return data;
|
|
|
163 }
|
|
|
164
|
|
2
|
165 //********** CCN-DB **********//
|
|
|
166 /* 被保険者番号からファイルパスを取得 */
|
|
0
|
167 wxArrayString GetPathByHhsNo( wxString hhsno )
|
|
|
168 {
|
|
2
|
169 wxArrayString path;
|
|
0
|
170
|
|
|
171 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
172 wxSQLite3Database ccndb;
|
|
|
173 ccndb.Open( gszFile );
|
|
|
174
|
|
2
|
175 wxString sql = wxT( "SELECT path FROM path WHERE hhsno = ? ORDER BY path DESC;" );
|
|
|
176 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
|
|
0
|
177 stmt.Bind( 1, hhsno );
|
|
|
178 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
179
|
|
|
180 if ( !q.IsNull(0) ) {
|
|
|
181 while ( q.NextRow() ) {
|
|
2
|
182 path.Add( q.GetString(0) );
|
|
0
|
183 }
|
|
|
184 }
|
|
|
185 stmt.Finalize();
|
|
|
186 ccndb.Close();
|
|
|
187
|
|
2
|
188 return path;
|
|
0
|
189 }
|
|
|
190
|
|
9
|
191 // 審査会情報のある被保険者を取得
|
|
|
192 wxArrayString GetJudgedHhsNo( void )
|
|
|
193 {
|
|
|
194 wxArrayString hhsno;
|
|
|
195
|
|
|
196 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
197 wxSQLite3Database ccndb;
|
|
|
198 ccndb.Open( gszFile );
|
|
|
199
|
|
|
200 //wxString sql = wxT( "SELECT DISTINCT hhsno FROM path;" );
|
|
|
201 wxString sql = wxT( "SELECT hhsno FROM path ORDER BY path DESC LIMIT 200;" );
|
|
|
202 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
|
|
|
203 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
204
|
|
|
205 if ( !q.IsNull(0) ) {
|
|
|
206 while ( q.NextRow() ) {
|
|
|
207 hhsno.Add( q.GetString(0) );
|
|
|
208 }
|
|
|
209 }
|
|
|
210 stmt.Finalize();
|
|
|
211 ccndb.Close();
|
|
|
212
|
|
|
213 return hhsno;
|
|
|
214 }
|
|
|
215
|
|
2
|
216 /* 被保険者が審査会にかかったかどうか */
|
|
|
217 bool IsHhsJudged( wxString hhsno )
|
|
|
218 {
|
|
|
219 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
220 wxSQLite3Database ccndb;
|
|
|
221 ccndb.Open( gszFile );
|
|
|
222
|
|
|
223 wxString sql = wxT( "SELECT path FROM path WHERE hhsno = ?;" );
|
|
|
224 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
|
|
|
225 stmt.Bind( 1, hhsno );
|
|
|
226 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
227
|
|
|
228 bool ret = true;
|
|
|
229 if ( q.IsNull(0) ) ret = false;
|
|
|
230
|
|
|
231 stmt.Finalize();
|
|
|
232 ccndb.Close();
|
|
|
233
|
|
|
234 return ret;
|
|
|
235 }
|
|
|
236
|
|
|
237 /* 合議体開催日を取得 */
|
|
0
|
238 wxArrayString GetCcnDate( void )
|
|
|
239 {
|
|
|
240 wxArrayString date_cnt;
|
|
|
241
|
|
|
242 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
243 wxSQLite3Database ccndb;
|
|
|
244 ccndb.Open( gszFile );
|
|
|
245
|
|
21
|
246 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT date, count(*) FROM path GROUP BY date ORDER BY date desc" );
|
|
0
|
247 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
248
|
|
|
249 wxString str;
|
|
|
250 if ( !q.IsNull(0) ) {
|
|
|
251 while ( q.NextRow() ) {
|
|
21
|
252 //str = q.GetString(0) + "_" + q.GetString(1);
|
|
|
253 str = q.GetString(0);
|
|
0
|
254 date_cnt.Add( str );
|
|
|
255 }
|
|
|
256 }
|
|
|
257 stmt.Finalize();
|
|
|
258 ccndb.Close();
|
|
|
259
|
|
|
260 return date_cnt;
|
|
|
261 }
|
|
|
262
|
|
2
|
263 /* 日付から審査会を取得 */
|
|
0
|
264 wxArrayString GetCcnByDate( wxString date )
|
|
|
265 {
|
|
2
|
266 wxArrayString data;
|
|
0
|
267
|
|
|
268 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
269 wxSQLite3Database ccndb;
|
|
|
270 ccndb.Open( gszFile );
|
|
|
271
|
|
2
|
272 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT hhsno, path, date FROM path WHERE date = ? ORDER BY path" );
|
|
0
|
273 stmt.Bind( 1, date );
|
|
|
274 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
275
|
|
|
276 wxString str;
|
|
|
277 if ( !q.IsNull(0) ) {
|
|
|
278 while ( q.NextRow() ) {
|
|
2
|
279 str = q.GetString(0) + "_" + q.GetString(1) + "_" + q.GetString(2);
|
|
|
280 data.Add( str );
|
|
0
|
281 }
|
|
|
282 }
|
|
|
283 stmt.Finalize();
|
|
|
284 ccndb.Close();
|
|
|
285
|
|
2
|
286 return data;
|
|
0
|
287 }
|
|
|
288
|
|
21
|
289 /* 指定した範囲の日付のパスを取得 */
|
|
|
290 wxArrayString GetPathesByPeriod( wxString from, wxString to )
|
|
11
|
291 {
|
|
|
292 wxArrayString path;
|
|
|
293
|
|
|
294 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
295 wxSQLite3Database ccndb;
|
|
|
296 ccndb.Open( gszFile );
|
|
|
297
|
|
|
298 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT path FROM path WHERE date >= ? AND date <= ?" );
|
|
|
299 stmt.Bind( 1, from );
|
|
|
300 stmt.Bind( 2, to );
|
|
|
301 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
302
|
|
|
303 if ( !q.IsNull(0) ) {
|
|
|
304 while ( q.NextRow() ) {
|
|
|
305 path.Add( q.GetString(0) );
|
|
|
306 }
|
|
|
307 }
|
|
|
308 stmt.Finalize();
|
|
|
309 ccndb.Close();
|
|
|
310
|
|
|
311 return path;
|
|
|
312 }
|
|
|
313
|
|
21
|
314 /* 指定日のパスを取得 */
|
|
|
315 wxArrayString GetPathesByDate( wxString date )
|
|
|
316 {
|
|
|
317 wxArrayString path;
|
|
|
318
|
|
|
319 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
320 wxSQLite3Database ccndb;
|
|
|
321 ccndb.Open( gszFile );
|
|
|
322
|
|
|
323 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT path FROM path WHERE date = ?" );
|
|
|
324 stmt.Bind( 1, date );
|
|
|
325 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
326
|
|
|
327 if ( !q.IsNull(0) ) {
|
|
|
328 while ( q.NextRow() ) {
|
|
|
329 path.Add( q.GetString(0) );
|
|
|
330 }
|
|
|
331 }
|
|
|
332 stmt.Finalize();
|
|
|
333 ccndb.Close();
|
|
|
334
|
|
|
335 return path;
|
|
|
336 }
|
|
|
337
|
|
2
|
338 /* 合議体から被保険者番号を取得 */
|
|
0
|
339 wxArrayString GetHhsNoByCcn( wxString ccn, wxString date )
|
|
|
340 {
|
|
|
341 wxArrayString hhsno;
|
|
|
342
|
|
|
343 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
344 wxSQLite3Database ccndb;
|
|
|
345 ccndb.Open( gszFile );
|
|
|
346
|
|
2
|
347 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT hhsno FROM ccn WHERE ccn = ? AND date = ? ORDER BY hhsno" );
|
|
0
|
348 stmt.Bind( 1, ccn );
|
|
|
349 stmt.Bind( 2, date );
|
|
|
350 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
351
|
|
|
352 if ( !q.IsNull(0) ) {
|
|
|
353 while ( q.NextRow() ) {
|
|
|
354 hhsno.Add( q.GetString(0) );
|
|
|
355 }
|
|
|
356 }
|
|
|
357 stmt.Finalize();
|
|
|
358 ccndb.Close();
|
|
|
359
|
|
|
360 return hhsno;
|
|
|
361 }
|
|
|
362
|
|
2
|
363 /* インデックスを更新 */
|
|
8
|
364 void UpdateIndex( wxString datadir, wxString date )
|
|
0
|
365 {
|
|
|
366 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
367 wxSQLite3Database ccndb;
|
|
|
368 ccndb.Open( gszFile );
|
|
|
369
|
|
2
|
370 wxSQLite3Statement stmt = ccndb.PrepareStatement( "DELETE FROM path WHERE date = ?;" );
|
|
|
371 stmt.Bind( 1, date );
|
|
|
372 stmt.ExecuteQuery();
|
|
|
373 stmt.Finalize();
|
|
0
|
374
|
|
2
|
375 wxString ccndir;
|
|
8
|
376 wxDir dated( datadir );
|
|
2
|
377 if ( !dated.IsOpened() ) {
|
|
|
378 return;
|
|
0
|
379 }
|
|
|
380
|
|
2
|
381 wxRegEx reSinsei( wxT("^00000") );
|
|
|
382 bool cont = dated.GetFirst( &ccndir, wxEmptyString, wxDIR_DIRS );
|
|
|
383
|
|
|
384 wxProgressDialog pd( wxT("進行状況"), wxT("処理開始..."), 240, NULL, wxPD_APP_MODAL|wxPD_REMAINING_TIME|wxPD_AUTO_HIDE );
|
|
|
385 pd.SetSize( wxSize( 320, 140 ) );
|
|
|
386 int count = 0;
|
|
|
387
|
|
|
388 while ( cont ) {
|
|
|
389
|
|
8
|
390 wxDir ccnd( datadir + wxFILE_SEP_PATH + ccndir );
|
|
2
|
391 if ( !ccnd.IsOpened() ) return;
|
|
|
392 wxString hhsdir;
|
|
|
393 bool c = ccnd.GetFirst( &hhsdir, wxEmptyString, wxDIR_DIRS );
|
|
|
394
|
|
|
395 while ( c ) {
|
|
|
396 if ( ! reSinsei.Matches( hhsdir ) ) {
|
|
|
397
|
|
8
|
398 wxString path = datadir + wxFILE_SEP_PATH + ccndir + wxFILE_SEP_PATH + hhsdir;
|
|
2
|
399
|
|
|
400 stmt = ccndb.PrepareStatement( "INSERT INTO path VALUES( ?, ?, ?, datetime( 'now', 'localtime' ) );" );
|
|
|
401 stmt.Bind( 1, hhsdir );
|
|
|
402 stmt.Bind( 2, path );
|
|
|
403 stmt.Bind( 3, date );
|
|
|
404 stmt.ExecuteQuery();
|
|
|
405 stmt.Finalize();
|
|
|
406 pd.Update( count++, hhsdir + wxT("＠") + ccndir + wxT("を処理しました．") );
|
|
|
407 }
|
|
|
408 c = ccnd.GetNext( &hhsdir );
|
|
|
409 }
|
|
|
410
|
|
|
411 cont = dated.GetNext( &ccndir );
|
|
|
412 }
|
|
0
|
413 ccndb.Close();
|
|
|
414 }
|
|
|
415
|
|
4
|
416 //********** HHS-DB & CCN-DB **********//
|
|
7
|
417 /* DBの更新日時を取得 */
|
|
|
418 wxArrayString GetLastUpdate( void )
|
|
|
419 {
|
|
|
420 wxArrayString date;
|
|
|
421 wxString dbFile;
|
|
|
422 wxDateTime t;
|
|
|
423
|
|
|
424 dbFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
425 wxFileName ccn( dbFile );
|
|
|
426 t = ccn.GetModificationTime();
|
|
|
427 date.Add( t.FormatISODate() );
|
|
|
428
|
|
|
429 dbFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
|
|
|
430 wxFileName hhs( dbFile );
|
|
|
431 t = hhs.GetModificationTime();
|
|
|
432 date.Add( t.FormatISODate() );
|
|
|
433
|
|
|
434 return date;
|
|
|
435 }
|
|
|
436
|
|
2
|
437 /* DB整合性チェック */
|
|
|
438 wxArrayString CheckDBs( void )
|
|
|
439 {
|
|
|
440 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
441 wxSQLite3Database ccndb;
|
|
|
442 ccndb.Open( gszFile );
|
|
|
443
|
|
|
444 wxString sql = wxT("ATTACH 'db/hhs.db' AS hhs");
|
|
|
445 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
|
|
|
446 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
447
|
|
|
448 sql = wxT("SELECT hhsno FROM path EXCEPT SELECT hhsno FROM hhs.hhs_master");
|
|
|
449 stmt = ccndb.PrepareStatement( sql );
|
|
|
450 q = stmt.ExecuteQuery();
|
|
|
451
|
|
|
452 wxArrayString result;
|
|
|
453 while ( q.NextRow() ) {
|
|
|
454 result.Add( q.GetString(0) );
|
|
|
455 }
|
|
|
456
|
|
|
457 stmt.Finalize();
|
|
|
458 ccndb.Close();
|
|
|
459
|
|
|
460 return result;
|
|
|
461 }
|
|
|
462
|
|
4
|
463 // 被保険者番号リストから氏名と最新ファイルパスを取得
|
|
|
464 wxArrayString GetHhsInfoAndPathByHhsNoList( wxArrayString hhsno )
|
|
|
465 {
|
|
|
466 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
467 wxSQLite3Database ccndb;
|
|
|
468 ccndb.Open( gszFile );
|
|
|
469
|
|
|
470 wxString sql = wxT( "ATTACH 'db/hhs.db' AS hhs;" );
|
|
|
471 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
|
|
|
472 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
473
|
|
|
474 wxArrayString result;
|
|
|
475 for ( int i = 0; i < hhsno.GetCount(); i++ ) {
|
|
|
476 wxString str = hhsno[i];
|
|
|
477 str.Append( wxT("_") );
|
|
|
478
|
|
|
479 sql = wxT( "SELECT name FROM hhs.hhs_master WHERE hhsno = ?;" );
|
|
|
480 stmt = ccndb.PrepareStatement( sql );
|
|
|
481 stmt.Bind( 1, hhsno[i] );
|
|
|
482 q = stmt.ExecuteQuery();
|
|
|
483 if ( !q.IsNull(0) ) {
|
|
|
484 while ( q.NextRow() ) {
|
|
|
485 str.Append( q.GetString(0) );
|
|
|
486 }
|
|
|
487 }
|
|
|
488 str.Append( wxT("_") );
|
|
|
489
|
|
|
490 sql = wxT( "SELECT path FROM path WHERE hhsno = ? ORDER BY path DESC LIMIT 1;" );
|
|
|
491 stmt = ccndb.PrepareStatement( sql );
|
|
|
492 stmt.Bind( 1, hhsno[i] );
|
|
|
493 q = stmt.ExecuteQuery();
|
|
|
494 if ( !q.IsNull(0) ) {
|
|
|
495 while ( q.NextRow() ) {
|
|
|
496 str.Append( q.GetString(0) );
|
|
|
497 }
|
|
|
498 }
|
|
|
499
|
|
|
500 result.Add( str );
|
|
|
501 }
|
|
|
502 stmt.Finalize();
|
|
|
503 ccndb.Close();
|
|
|
504
|
|
|
505 return result;
|
|
|
506 }
|
|
7
|
507
|
