Mercurial > mercurial > hgweb_searcher03.cgi

comparison src/db.cpp @ 2:c066fde99517

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added Batch Print Mode.
author pyon@macmini
date Fri, 23 Aug 2013 18:32:09 +0900
parents 7b6dab24f4b8
children fdba695b99f1
comparison
equal deleted inserted replaced
1:7b6dab24f4b8 2:c066fde99517
1 // Filename : db.cpp 1 // Filename : db.cpp
2 // Last Change: 02-Aug-2013. 2 // Last Change: 23-Aug-2013.
3 // 3 //
4 4
5 #include "db.h" 5 #include "db.h"
6 #include "wx/wxsqlite3.h" 6 #include "wx/wxsqlite3.h"
7 7
8 /* $BHoJ]HV$GHoJ]81<T>pJs$r<hF@(B */ 8 //********** HHS-DB **********//
9 /* 被保番で被保険者情報を取得 */
9 wxString GetHhsInfoByHhsNo( wxString hhsno ) 10 wxString GetHhsInfoByHhsNo( wxString hhsno )
10 { 11 {
11 wxString name, addr; 12 wxString name, addr;
12 13
13 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db"); 14 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
14 wxSQLite3Database hhsdb; 15 wxSQLite3Database hhsdb;
15 hhsdb.Open( gszFile ); 16 hhsdb.Open( gszFile );
16 17
17 wxSQLite3Statement stmt = hhsdb.PrepareStatement("SELECT name, addr FROM hhs_master WHERE hhsno = ?"); 18 wxSQLite3Statement stmt = hhsdb.PrepareStatement( "SELECT name, addr FROM hhs_master WHERE hhsno = ?" );
18 stmt.Bind( 1, hhsno ); 19 stmt.Bind( 1, hhsno );
19 wxSQLite3ResultSet q = stmt.ExecuteQuery(); 20 wxSQLite3ResultSet q = stmt.ExecuteQuery();
20 if ( !q.IsNull(0) ) { 21 if ( !q.IsNull(0) ) {
21 while ( q.NextRow() ) { 22 while ( q.NextRow() ) {
22 name = q.GetString(0); 23 name = q.GetString(0);
32 else { 33 else {
33 return name + wxT("_") + addr; 34 return name + wxT("_") + addr;
34 } 35 }
35 } 36 }
36 37
37 // $B;aL>%+%J$GHoJ]81<T>pJs$r8!:w(B 38 // 氏名カナで被保険者情報を検索
38 wxArrayString GetHhsInfoByKana( wxString kana, bool fuzzy ) 39 wxArrayString GetHhsInfoByKana( wxString kana, bool fuzzy )
39 { 40 {
40 wxArrayString data; 41 wxArrayString data;
41 42
42 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db"); 43 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
64 hhsdb.Close(); 65 hhsdb.Close();
65 66
66 return data; 67 return data;
67 } 68 }
68 69
69 /* $BHoJ]81<THV9f$+$i%U%!%$%k%Q%9$r<hF@(B */ 70 //********** CCN-DB **********//
71 /* 被保険者番号からファイルパスを取得 */
70 wxArrayString GetPathByHhsNo( wxString hhsno ) 72 wxArrayString GetPathByHhsNo( wxString hhsno )
71 { 73 {
72 wxArrayString date_path; 74 wxArrayString path;
73 75
74 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db"); 76 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
75 wxSQLite3Database ccndb; 77 wxSQLite3Database ccndb;
76 ccndb.Open( gszFile ); 78 ccndb.Open( gszFile );
77 79
78 wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT date, path FROM ccn WHERE hhsno = ? ORDER BY date DESC"); 80 wxString sql = wxT( "SELECT path FROM path WHERE hhsno = ? ORDER BY path DESC;" );
81 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
79 stmt.Bind( 1, hhsno ); 82 stmt.Bind( 1, hhsno );
80 wxSQLite3ResultSet q = stmt.ExecuteQuery(); 83 wxSQLite3ResultSet q = stmt.ExecuteQuery();
81 84
82 if ( !q.IsNull(0) ) { 85 if ( !q.IsNull(0) ) {
83 wxString str; 86 while ( q.NextRow() ) {
84 while ( q.NextRow() ) { 87 path.Add( q.GetString(0) );
85 str = q.GetString(0) + "_" + q.GetString(1); 88 }
86 date_path.Add( str ); 89 }
87 } 90 stmt.Finalize();
88 } 91 ccndb.Close();
89 stmt.Finalize(); 92
90 ccndb.Close(); 93 return path;
91 94 }
92 return date_path; 95
93 } 96 /* 被保険者が審査会にかかったかどうか */
94 97 bool IsHhsJudged( wxString hhsno )
95 /* $B9g5DBN3+:EF|$r<hF@(B */ 98 {
99 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
100 wxSQLite3Database ccndb;
101 ccndb.Open( gszFile );
102
103 wxString sql = wxT( "SELECT path FROM path WHERE hhsno = ?;" );
104 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
105 stmt.Bind( 1, hhsno );
106 wxSQLite3ResultSet q = stmt.ExecuteQuery();
107
108 bool ret = true;
109 if ( q.IsNull(0) ) ret = false;
110
111 stmt.Finalize();
112 ccndb.Close();
113
114 return ret;
115 }
116
117 /* 合議体開催日を取得 */
96 wxArrayString GetCcnDate( void ) 118 wxArrayString GetCcnDate( void )
97 { 119 {
98 wxArrayString date_cnt; 120 wxArrayString date_cnt;
99 121
100 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db"); 122 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
101 wxSQLite3Database ccndb; 123 wxSQLite3Database ccndb;
102 ccndb.Open( gszFile ); 124 ccndb.Open( gszFile );
103 125
104 wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT date, count(*) FROM ccn GROUP BY date ORDER BY date desc"); 126 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT date, count(*) FROM ccn GROUP BY date ORDER BY date desc" );
105 wxSQLite3ResultSet q = stmt.ExecuteQuery(); 127 wxSQLite3ResultSet q = stmt.ExecuteQuery();
106 128
107 wxString str; 129 wxString str;
108 if ( !q.IsNull(0) ) { 130 if ( !q.IsNull(0) ) {
109 while ( q.NextRow() ) { 131 while ( q.NextRow() ) {
115 ccndb.Close(); 137 ccndb.Close();
116 138
117 return date_cnt; 139 return date_cnt;
118 } 140 }
119 141
120 /* $BF|IU$+$i?3::2q$r<hF@(B */ 142 /* 日付から審査会を取得 */
121 wxArrayString GetCcnByDate( wxString date ) 143 wxArrayString GetCcnByDate( wxString date )
122 { 144 {
123 wxArrayString ccn_cnt; 145 wxArrayString data;
124 146
125 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db"); 147 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
126 wxSQLite3Database ccndb; 148 wxSQLite3Database ccndb;
127 ccndb.Open( gszFile ); 149 ccndb.Open( gszFile );
128 150
129 wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT ccn, count(*) FROM ccn WHERE date = ? GROUP BY ccn"); 151 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT hhsno, path, date FROM path WHERE date = ? ORDER BY path" );
130 stmt.Bind( 1, date ); 152 stmt.Bind( 1, date );
131 wxSQLite3ResultSet q = stmt.ExecuteQuery(); 153 wxSQLite3ResultSet q = stmt.ExecuteQuery();
132 154
133 wxString str; 155 wxString str;
134 if ( !q.IsNull(0) ) { 156 if ( !q.IsNull(0) ) {
135 while ( q.NextRow() ) { 157 while ( q.NextRow() ) {
136 str = q.GetString(0) + "_" + q.GetString(1); 158 str = q.GetString(0) + "_" + q.GetString(1) + "_" + q.GetString(2);
137 ccn_cnt.Add( str ); 159 data.Add( str );
138 } 160 }
139 } 161 }
140 stmt.Finalize(); 162 stmt.Finalize();
141 ccndb.Close(); 163 ccndb.Close();
142 164
143 return ccn_cnt; 165 return data;
144 } 166 }
145 167
146 /* $B9g5DBN$+$iHoJ]81<THV9f$r<hF@(B */ 168 /* 合議体から被保険者番号を取得 */
147 wxArrayString GetHhsNoByCcn( wxString ccn, wxString date ) 169 wxArrayString GetHhsNoByCcn( wxString ccn, wxString date )
148 { 170 {
149 wxArrayString hhsno; 171 wxArrayString hhsno;
150 172
151 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db"); 173 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
152 wxSQLite3Database ccndb; 174 wxSQLite3Database ccndb;
153 ccndb.Open( gszFile ); 175 ccndb.Open( gszFile );
154 176
155 wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT hhsno FROM ccn WHERE ccn = ? AND date = ? ORDER BY hhsno"); 177 wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT hhsno FROM ccn WHERE ccn = ? AND date = ? ORDER BY hhsno" );
156 stmt.Bind( 1, ccn ); 178 stmt.Bind( 1, ccn );
157 stmt.Bind( 2, date ); 179 stmt.Bind( 2, date );
158 wxSQLite3ResultSet q = stmt.ExecuteQuery(); 180 wxSQLite3ResultSet q = stmt.ExecuteQuery();
159 181
160 if ( !q.IsNull(0) ) { 182 if ( !q.IsNull(0) ) {
166 ccndb.Close(); 188 ccndb.Close();
167 189
168 return hhsno; 190 return hhsno;
169 } 191 }
170 192
171 /* $B%$%s%G%C%/%9$r99?7(B */ 193 /* インデックスを更新 */
172 void UpdateIndex( wxArrayString paths ) 194 void UpdateIndex( wxString datedir, wxString date )
173 { 195 {
174 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db"); 196 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
175 wxSQLite3Database ccndb; 197 wxSQLite3Database ccndb;
176 ccndb.Open( gszFile ); 198 ccndb.Open( gszFile );
177 199
178 wxRegEx regex( wxT("^.+(20[0-9]{2})([01][0-9])([0-3][0-9]).(.+).(0[1238]{8})$") ); 200 wxSQLite3Statement stmt = ccndb.PrepareStatement( "DELETE FROM path WHERE date = ?;" );
179 wxSQLite3Statement stmt = ccndb.PrepareStatement("INSERT OR REPLACE INTO ccn VALUES( ?, ?, ?, ? )"); 201 stmt.Bind( 1, date );
180 wxString date, ccn, hhsno; 202 stmt.ExecuteQuery();
181 203 stmt.Finalize();
182 for ( int i=0; i<paths.GetCount(); i++ ) { 204
183 date = paths[i]; 205 wxString ccndir;
184 ccn = paths[i]; 206 wxDir dated( datedir );
185 hhsno = paths[i]; 207 if ( !dated.IsOpened() ) {
186 regex.ReplaceAll( &date, wxT("\\1-\\2-\\3") ); 208 return;
187 regex.ReplaceAll( &date, wxT("\\4") ); 209 }
188 regex.ReplaceAll( &date, wxT("\\5") ); 210
189 stmt.Bind( 1, date ); 211 wxRegEx reSinsei( wxT("^00000") );
190 stmt.Bind( 2, ccn ); 212 bool cont = dated.GetFirst( &ccndir, wxEmptyString, wxDIR_DIRS );
191 stmt.Bind( 3, hhsno ); 213
192 stmt.Bind( 4, paths[i] ); 214 wxProgressDialog pd( wxT("進行状況"), wxT("処理開始..."), 240, NULL, wxPD_APP_MODAL|wxPD_REMAINING_TIME|wxPD_AUTO_HIDE );
193 stmt.ExecuteQuery(); 215 pd.SetSize( wxSize( 320, 140 ) );
194 stmt.Finalize(); 216 int count = 0;
195 } 217
196 218 while ( cont ) {
197 ccndb.Close(); 219
198 } 220 wxDir ccnd( datedir + wxFILE_SEP_PATH + ccndir );
199 221 if ( !ccnd.IsOpened() ) return;
222 wxString hhsdir;
223 bool c = ccnd.GetFirst( &hhsdir, wxEmptyString, wxDIR_DIRS );
224
225 while ( c ) {
226 if ( ! reSinsei.Matches( hhsdir ) ) {
227
228 wxString path = datedir + wxFILE_SEP_PATH + ccndir + wxFILE_SEP_PATH + hhsdir;
229
230 stmt = ccndb.PrepareStatement( "INSERT INTO path VALUES( ?, ?, ?, datetime( 'now', 'localtime' ) );" );
231 stmt.Bind( 1, hhsdir );
232 stmt.Bind( 2, path );
233 stmt.Bind( 3, date );
234 stmt.ExecuteQuery();
235 stmt.Finalize();
236 pd.Update( count++, hhsdir + wxT("＠") + ccndir + wxT("を処理しました．") );
237 }
238 c = ccnd.GetNext( &hhsdir );
239 }
240
241 cont = dated.GetNext( &ccndir );
242 }
243 ccndb.Close();
244 }
245
246 /* DB整合性チェック */
247 wxArrayString CheckDBs( void )
248 {
249 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
250 wxSQLite3Database ccndb;
251 ccndb.Open( gszFile );
252
253 wxString sql = wxT("ATTACH 'db/hhs.db' AS hhs");
254 wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
255 wxSQLite3ResultSet q = stmt.ExecuteQuery();
256
257 sql = wxT("SELECT hhsno FROM path EXCEPT SELECT hhsno FROM hhs.hhs_master");
258 stmt = ccndb.PrepareStatement( sql );
259 q = stmt.ExecuteQuery();
260
261 wxArrayString result;
262 while ( q.NextRow() ) {
263 result.Add( q.GetString(0) );
264 }
265
266 stmt.Finalize();
267 ccndb.Close();
268
269 return result;
270 }
271