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