|
0
|
1 // Filename : db.cpp
|
|
6
|
2 // Last Change: 18-Sep-2013.
|
|
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
|
|
2
|
38 // 氏名カナで被保険者情報を検索
|
|
1
|
39 wxArrayString GetHhsInfoByKana( wxString kana, bool fuzzy )
|
|
0
|
40 {
|
|
|
41 wxArrayString data;
|
|
|
42
|
|
|
43 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
|
|
|
44 wxSQLite3Database hhsdb;
|
|
|
45 hhsdb.Open( gszFile );
|
|
|
46
|
|
6
|
47 wxString sql = wxT( "SELECT hhsno, kana, name, birth, addr FROM hhs_master " );
|
|
|
48 if ( fuzzy ) {
|
|
|
49 kana = wxT("%") + kana + wxT("%");
|
|
|
50 sql += wxT( "WHERE kana LIKE ? ORDER BY kana, birth;" );
|
|
|
51 }
|
|
|
52 else {
|
|
|
53 sql += wxT( "WHERE kana = ? ORDER BY kana, birth;" );
|
|
|
54 }
|
|
1
|
55
|
|
|
56 wxSQLite3Statement stmt = hhsdb.PrepareStatement( sql );
|
|
0
|
57 stmt.Bind( 1, kana );
|
|
|
58 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
59
|
|
|
60 if ( !q.IsNull(0) ) {
|
|
|
61 wxString str;
|
|
|
62 while ( q.NextRow() ) {
|
|
|
63 str = q.GetString(0);
|
|
|
64 for ( int i=1; i<5; i++ ) {
|
|
|
65 str += "_" + q.GetString(i);
|
|
|
66 }
|
|
|
67 data.Add( str );
|
|
|
68 }
|
|
|
69 }
|
|
|
70 stmt.Finalize();
|
|
|
71 hhsdb.Close();
|
|
|
72
|
|
|
73 return data;
|
|
|
74 }
|
|
|
75
|
|
2
|
76 //********** CCN-DB **********//
|
|
|
77 /* 被保険者番号からファイルパスを取得 */
|
|
0
|
78 wxArrayString GetPathByHhsNo( wxString hhsno )
|
|
|
79 {
|
|
2
|
80 wxArrayString path;
|
|
0
|
81
|
|
|
82 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
83 wxSQLite3Database ccndb;
|
|
|
84 ccndb.Open( gszFile );
|
|
|
85
|
|
2
|
86 wxString sql = wxT( "SELECT path FROM path WHERE hhsno = ? ORDER BY path DESC;" );
|
|
|
87 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
|
|
0
|
88 stmt.Bind( 1, hhsno );
|
|
|
89 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
90
|
|
|
91 if ( !q.IsNull(0) ) {
|
|
|
92 while ( q.NextRow() ) {
|
|
2
|
93 path.Add( q.GetString(0) );
|
|
0
|
94 }
|
|
|
95 }
|
|
|
96 stmt.Finalize();
|
|
|
97 ccndb.Close();
|
|
|
98
|
|
2
|
99 return path;
|
|
0
|
100 }
|
|
|
101
|
|
2
|
102 /* 被保険者が審査会にかかったかどうか */
|
|
|
103 bool IsHhsJudged( wxString hhsno )
|
|
|
104 {
|
|
|
105 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
106 wxSQLite3Database ccndb;
|
|
|
107 ccndb.Open( gszFile );
|
|
|
108
|
|
|
109 wxString sql = wxT( "SELECT path FROM path WHERE hhsno = ?;" );
|
|
|
110 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
|
|
|
111 stmt.Bind( 1, hhsno );
|
|
|
112 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
113
|
|
|
114 bool ret = true;
|
|
|
115 if ( q.IsNull(0) ) ret = false;
|
|
|
116
|
|
|
117 stmt.Finalize();
|
|
|
118 ccndb.Close();
|
|
|
119
|
|
|
120 return ret;
|
|
|
121 }
|
|
|
122
|
|
|
123 /* 合議体開催日を取得 */
|
|
0
|
124 wxArrayString GetCcnDate( void )
|
|
|
125 {
|
|
|
126 wxArrayString date_cnt;
|
|
|
127
|
|
|
128 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
129 wxSQLite3Database ccndb;
|
|
|
130 ccndb.Open( gszFile );
|
|
|
131
|
|
2
|
132 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT date, count(*) FROM ccn GROUP BY date ORDER BY date desc" );
|
|
0
|
133 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
134
|
|
|
135 wxString str;
|
|
|
136 if ( !q.IsNull(0) ) {
|
|
|
137 while ( q.NextRow() ) {
|
|
|
138 str = q.GetString(0) + "_" + q.GetString(1);
|
|
|
139 date_cnt.Add( str );
|
|
|
140 }
|
|
|
141 }
|
|
|
142 stmt.Finalize();
|
|
|
143 ccndb.Close();
|
|
|
144
|
|
|
145 return date_cnt;
|
|
|
146 }
|
|
|
147
|
|
2
|
148 /* 日付から審査会を取得 */
|
|
0
|
149 wxArrayString GetCcnByDate( wxString date )
|
|
|
150 {
|
|
2
|
151 wxArrayString data;
|
|
0
|
152
|
|
|
153 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
154 wxSQLite3Database ccndb;
|
|
|
155 ccndb.Open( gszFile );
|
|
|
156
|
|
2
|
157 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT hhsno, path, date FROM path WHERE date = ? ORDER BY path" );
|
|
0
|
158 stmt.Bind( 1, date );
|
|
|
159 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
160
|
|
|
161 wxString str;
|
|
|
162 if ( !q.IsNull(0) ) {
|
|
|
163 while ( q.NextRow() ) {
|
|
2
|
164 str = q.GetString(0) + "_" + q.GetString(1) + "_" + q.GetString(2);
|
|
|
165 data.Add( str );
|
|
0
|
166 }
|
|
|
167 }
|
|
|
168 stmt.Finalize();
|
|
|
169 ccndb.Close();
|
|
|
170
|
|
2
|
171 return data;
|
|
0
|
172 }
|
|
|
173
|
|
2
|
174 /* 合議体から被保険者番号を取得 */
|
|
0
|
175 wxArrayString GetHhsNoByCcn( wxString ccn, wxString date )
|
|
|
176 {
|
|
|
177 wxArrayString hhsno;
|
|
|
178
|
|
|
179 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
180 wxSQLite3Database ccndb;
|
|
|
181 ccndb.Open( gszFile );
|
|
|
182
|
|
2
|
183 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT hhsno FROM ccn WHERE ccn = ? AND date = ? ORDER BY hhsno" );
|
|
0
|
184 stmt.Bind( 1, ccn );
|
|
|
185 stmt.Bind( 2, date );
|
|
|
186 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
187
|
|
|
188 if ( !q.IsNull(0) ) {
|
|
|
189 while ( q.NextRow() ) {
|
|
|
190 hhsno.Add( q.GetString(0) );
|
|
|
191 }
|
|
|
192 }
|
|
|
193 stmt.Finalize();
|
|
|
194 ccndb.Close();
|
|
|
195
|
|
|
196 return hhsno;
|
|
|
197 }
|
|
|
198
|
|
2
|
199 /* インデックスを更新 */
|
|
|
200 void UpdateIndex( wxString datedir, wxString date )
|
|
0
|
201 {
|
|
|
202 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
203 wxSQLite3Database ccndb;
|
|
|
204 ccndb.Open( gszFile );
|
|
|
205
|
|
2
|
206 wxSQLite3Statement stmt = ccndb.PrepareStatement( "DELETE FROM path WHERE date = ?;" );
|
|
|
207 stmt.Bind( 1, date );
|
|
|
208 stmt.ExecuteQuery();
|
|
|
209 stmt.Finalize();
|
|
0
|
210
|
|
2
|
211 wxString ccndir;
|
|
|
212 wxDir dated( datedir );
|
|
|
213 if ( !dated.IsOpened() ) {
|
|
|
214 return;
|
|
0
|
215 }
|
|
|
216
|
|
2
|
217 wxRegEx reSinsei( wxT("^00000") );
|
|
|
218 bool cont = dated.GetFirst( &ccndir, wxEmptyString, wxDIR_DIRS );
|
|
|
219
|
|
|
220 wxProgressDialog pd( wxT("進行状況"), wxT("処理開始..."), 240, NULL, wxPD_APP_MODAL|wxPD_REMAINING_TIME|wxPD_AUTO_HIDE );
|
|
|
221 pd.SetSize( wxSize( 320, 140 ) );
|
|
|
222 int count = 0;
|
|
|
223
|
|
|
224 while ( cont ) {
|
|
|
225
|
|
|
226 wxDir ccnd( datedir + wxFILE_SEP_PATH + ccndir );
|
|
|
227 if ( !ccnd.IsOpened() ) return;
|
|
|
228 wxString hhsdir;
|
|
|
229 bool c = ccnd.GetFirst( &hhsdir, wxEmptyString, wxDIR_DIRS );
|
|
|
230
|
|
|
231 while ( c ) {
|
|
|
232 if ( ! reSinsei.Matches( hhsdir ) ) {
|
|
|
233
|
|
|
234 wxString path = datedir + wxFILE_SEP_PATH + ccndir + wxFILE_SEP_PATH + hhsdir;
|
|
|
235
|
|
|
236 stmt = ccndb.PrepareStatement( "INSERT INTO path VALUES( ?, ?, ?, datetime( 'now', 'localtime' ) );" );
|
|
|
237 stmt.Bind( 1, hhsdir );
|
|
|
238 stmt.Bind( 2, path );
|
|
|
239 stmt.Bind( 3, date );
|
|
|
240 stmt.ExecuteQuery();
|
|
|
241 stmt.Finalize();
|
|
|
242 pd.Update( count++, hhsdir + wxT("＠") + ccndir + wxT("を処理しました．") );
|
|
|
243 }
|
|
|
244 c = ccnd.GetNext( &hhsdir );
|
|
|
245 }
|
|
|
246
|
|
|
247 cont = dated.GetNext( &ccndir );
|
|
|
248 }
|
|
0
|
249 ccndb.Close();
|
|
|
250 }
|
|
|
251
|
|
4
|
252 //********** HHS-DB & CCN-DB **********//
|
|
2
|
253 /* DB整合性チェック */
|
|
|
254 wxArrayString CheckDBs( void )
|
|
|
255 {
|
|
|
256 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
257 wxSQLite3Database ccndb;
|
|
|
258 ccndb.Open( gszFile );
|
|
|
259
|
|
|
260 wxString sql = wxT("ATTACH 'db/hhs.db' AS hhs");
|
|
|
261 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
|
|
|
262 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
263
|
|
|
264 sql = wxT("SELECT hhsno FROM path EXCEPT SELECT hhsno FROM hhs.hhs_master");
|
|
|
265 stmt = ccndb.PrepareStatement( sql );
|
|
|
266 q = stmt.ExecuteQuery();
|
|
|
267
|
|
|
268 wxArrayString result;
|
|
|
269 while ( q.NextRow() ) {
|
|
|
270 result.Add( q.GetString(0) );
|
|
|
271 }
|
|
|
272
|
|
|
273 stmt.Finalize();
|
|
|
274 ccndb.Close();
|
|
|
275
|
|
|
276 return result;
|
|
|
277 }
|
|
|
278
|
|
4
|
279 // 被保険者番号リストから氏名と最新ファイルパスを取得
|
|
|
280 wxArrayString GetHhsInfoAndPathByHhsNoList( wxArrayString hhsno )
|
|
|
281 {
|
|
|
282 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
283 wxSQLite3Database ccndb;
|
|
|
284 ccndb.Open( gszFile );
|
|
|
285
|
|
|
286 wxString sql = wxT( "ATTACH 'db/hhs.db' AS hhs;" );
|
|
|
287 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
|
|
|
288 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
289
|
|
|
290 wxArrayString result;
|
|
|
291 for ( int i = 0; i < hhsno.GetCount(); i++ ) {
|
|
|
292 wxString str = hhsno[i];
|
|
|
293 str.Append( wxT("_") );
|
|
|
294
|
|
|
295 sql = wxT( "SELECT name FROM hhs.hhs_master WHERE hhsno = ?;" );
|
|
|
296 stmt = ccndb.PrepareStatement( sql );
|
|
|
297 stmt.Bind( 1, hhsno[i] );
|
|
|
298 q = stmt.ExecuteQuery();
|
|
|
299 if ( !q.IsNull(0) ) {
|
|
|
300 while ( q.NextRow() ) {
|
|
|
301 str.Append( q.GetString(0) );
|
|
|
302 }
|
|
|
303 }
|
|
|
304 str.Append( wxT("_") );
|
|
|
305
|
|
|
306 sql = wxT( "SELECT path FROM path WHERE hhsno = ? ORDER BY path DESC LIMIT 1;" );
|
|
|
307 stmt = ccndb.PrepareStatement( sql );
|
|
|
308 stmt.Bind( 1, hhsno[i] );
|
|
|
309 q = stmt.ExecuteQuery();
|
|
|
310 if ( !q.IsNull(0) ) {
|
|
|
311 while ( q.NextRow() ) {
|
|
|
312 str.Append( q.GetString(0) );
|
|
|
313 }
|
|
|
314 }
|
|
|
315
|
|
|
316 result.Add( str );
|
|
|
317 }
|
|
|
318 stmt.Finalize();
|
|
|
319 ccndb.Close();
|
|
|
320
|
|
|
321 return result;
|
|
|
322 }
|
