|
0
|
1 // Filename : db.cpp
|
|
1
|
2 // Last Change: 02-Aug-2013.
|
|
0
|
3 //
|
|
|
4
|
|
|
5 #include "db.h"
|
|
|
6 #include "wx/wxsqlite3.h"
|
|
|
7
|
|
1
|
8 /* $BHoJ]HV$GHoJ]81<T>pJs$r<hF@(B */
|
|
|
9 wxString GetHhsInfoByHhsNo( wxString hhsno )
|
|
0
|
10 {
|
|
1
|
11 wxString name, addr;
|
|
0
|
12
|
|
|
13 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
|
|
|
14 wxSQLite3Database hhsdb;
|
|
|
15 hhsdb.Open( gszFile );
|
|
|
16
|
|
1
|
17 wxSQLite3Statement stmt = hhsdb.PrepareStatement("SELECT name, addr FROM hhs_master WHERE hhsno = ?");
|
|
0
|
18 stmt.Bind( 1, hhsno );
|
|
|
19 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
20 if ( !q.IsNull(0) ) {
|
|
|
21 while ( q.NextRow() ) {
|
|
|
22 name = q.GetString(0);
|
|
1
|
23 addr = q.GetString(1);
|
|
0
|
24 }
|
|
|
25 }
|
|
|
26 stmt.Finalize();
|
|
|
27 hhsdb.Close();
|
|
|
28
|
|
1
|
29 if ( name.IsEmpty() ) {
|
|
|
30 return wxEmptyString;
|
|
|
31 }
|
|
|
32 else {
|
|
|
33 return name + wxT("_") + addr;
|
|
|
34 }
|
|
0
|
35 }
|
|
1
|
36
|
|
|
37 // $B;aL>%+%J$GHoJ]81<T>pJs$r8!:w(B
|
|
|
38 wxArrayString GetHhsInfoByKana( wxString kana, bool fuzzy )
|
|
0
|
39 {
|
|
|
40 wxArrayString data;
|
|
|
41
|
|
|
42 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
|
|
|
43 wxSQLite3Database hhsdb;
|
|
|
44 hhsdb.Open( gszFile );
|
|
|
45
|
|
1
|
46 wxString sql = wxT( "SELECT hhsno, kana, name, birth, addr FROM hhs_master WHERE kana = ? ORDER BY kana, birth;" );
|
|
|
47 //if ( fuzzy ) ;//*****
|
|
|
48
|
|
|
49 wxSQLite3Statement stmt = hhsdb.PrepareStatement( sql );
|
|
0
|
50 stmt.Bind( 1, kana );
|
|
|
51 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
52
|
|
|
53 if ( !q.IsNull(0) ) {
|
|
|
54 wxString str;
|
|
|
55 while ( q.NextRow() ) {
|
|
|
56 str = q.GetString(0);
|
|
|
57 for ( int i=1; i<5; i++ ) {
|
|
|
58 str += "_" + q.GetString(i);
|
|
|
59 }
|
|
|
60 data.Add( str );
|
|
|
61 }
|
|
|
62 }
|
|
|
63 stmt.Finalize();
|
|
|
64 hhsdb.Close();
|
|
|
65
|
|
|
66 return data;
|
|
|
67 }
|
|
|
68
|
|
|
69 /* $BHoJ]81<THV9f$+$i%U%!%$%k%Q%9$r<hF@(B */
|
|
|
70 wxArrayString GetPathByHhsNo( wxString hhsno )
|
|
|
71 {
|
|
|
72 wxArrayString date_path;
|
|
|
73
|
|
|
74 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
75 wxSQLite3Database ccndb;
|
|
|
76 ccndb.Open( gszFile );
|
|
|
77
|
|
|
78 wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT date, path FROM ccn WHERE hhsno = ? ORDER BY date DESC");
|
|
|
79 stmt.Bind( 1, hhsno );
|
|
|
80 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
81
|
|
|
82 if ( !q.IsNull(0) ) {
|
|
|
83 wxString str;
|
|
|
84 while ( q.NextRow() ) {
|
|
|
85 str = q.GetString(0) + "_" + q.GetString(1);
|
|
|
86 date_path.Add( str );
|
|
|
87 }
|
|
|
88 }
|
|
|
89 stmt.Finalize();
|
|
|
90 ccndb.Close();
|
|
|
91
|
|
|
92 return date_path;
|
|
|
93 }
|
|
|
94
|
|
1
|
95 /* $B9g5DBN3+:EF|$r<hF@(B */
|
|
0
|
96 wxArrayString GetCcnDate( void )
|
|
|
97 {
|
|
|
98 wxArrayString date_cnt;
|
|
|
99
|
|
|
100 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
101 wxSQLite3Database ccndb;
|
|
|
102 ccndb.Open( gszFile );
|
|
|
103
|
|
|
104 wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT date, count(*) FROM ccn GROUP BY date ORDER BY date desc");
|
|
|
105 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
106
|
|
|
107 wxString str;
|
|
|
108 if ( !q.IsNull(0) ) {
|
|
|
109 while ( q.NextRow() ) {
|
|
|
110 str = q.GetString(0) + "_" + q.GetString(1);
|
|
|
111 date_cnt.Add( str );
|
|
|
112 }
|
|
|
113 }
|
|
|
114 stmt.Finalize();
|
|
|
115 ccndb.Close();
|
|
|
116
|
|
|
117 return date_cnt;
|
|
|
118 }
|
|
|
119
|
|
1
|
120 /* $BF|IU$+$i?3::2q$r<hF@(B */
|
|
0
|
121 wxArrayString GetCcnByDate( wxString date )
|
|
|
122 {
|
|
|
123 wxArrayString ccn_cnt;
|
|
|
124
|
|
|
125 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
126 wxSQLite3Database ccndb;
|
|
|
127 ccndb.Open( gszFile );
|
|
|
128
|
|
|
129 wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT ccn, count(*) FROM ccn WHERE date = ? GROUP BY ccn");
|
|
|
130 stmt.Bind( 1, date );
|
|
|
131 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
132
|
|
|
133 wxString str;
|
|
|
134 if ( !q.IsNull(0) ) {
|
|
|
135 while ( q.NextRow() ) {
|
|
|
136 str = q.GetString(0) + "_" + q.GetString(1);
|
|
|
137 ccn_cnt.Add( str );
|
|
|
138 }
|
|
|
139 }
|
|
|
140 stmt.Finalize();
|
|
|
141 ccndb.Close();
|
|
|
142
|
|
|
143 return ccn_cnt;
|
|
|
144 }
|
|
|
145
|
|
1
|
146 /* $B9g5DBN$+$iHoJ]81<THV9f$r<hF@(B */
|
|
0
|
147 wxArrayString GetHhsNoByCcn( wxString ccn, wxString date )
|
|
|
148 {
|
|
|
149 wxArrayString hhsno;
|
|
|
150
|
|
|
151 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
152 wxSQLite3Database ccndb;
|
|
|
153 ccndb.Open( gszFile );
|
|
|
154
|
|
|
155 wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT hhsno FROM ccn WHERE ccn = ? AND date = ? ORDER BY hhsno");
|
|
|
156 stmt.Bind( 1, ccn );
|
|
|
157 stmt.Bind( 2, date );
|
|
|
158 wxSQLite3ResultSet q = stmt.ExecuteQuery();
|
|
|
159
|
|
|
160 if ( !q.IsNull(0) ) {
|
|
|
161 while ( q.NextRow() ) {
|
|
|
162 hhsno.Add( q.GetString(0) );
|
|
|
163 }
|
|
|
164 }
|
|
|
165 stmt.Finalize();
|
|
|
166 ccndb.Close();
|
|
|
167
|
|
|
168 return hhsno;
|
|
|
169 }
|
|
|
170
|
|
|
171 /* $B%$%s%G%C%/%9$r99?7(B */
|
|
|
172 void UpdateIndex( wxArrayString paths )
|
|
|
173 {
|
|
|
174 wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
|
|
|
175 wxSQLite3Database ccndb;
|
|
|
176 ccndb.Open( gszFile );
|
|
|
177
|
|
|
178 wxRegEx regex( wxT("^.+(20[0-9]{2})([01][0-9])([0-3][0-9]).(.+).(0[1238]{8})$") );
|
|
|
179 wxSQLite3Statement stmt = ccndb.PrepareStatement("INSERT OR REPLACE INTO ccn VALUES( ?, ?, ?, ? )");
|
|
|
180 wxString date, ccn, hhsno;
|
|
|
181
|
|
|
182 for ( int i=0; i<paths.GetCount(); i++ ) {
|
|
|
183 date = paths[i];
|
|
|
184 ccn = paths[i];
|
|
|
185 hhsno = paths[i];
|
|
|
186 regex.ReplaceAll( &date, wxT("\\1-\\2-\\3") );
|
|
|
187 regex.ReplaceAll( &date, wxT("\\4") );
|
|
|
188 regex.ReplaceAll( &date, wxT("\\5") );
|
|
|
189 stmt.Bind( 1, date );
|
|
|
190 stmt.Bind( 2, ccn );
|
|
|
191 stmt.Bind( 3, hhsno );
|
|
|
192 stmt.Bind( 4, paths[i] );
|
|
|
193 stmt.ExecuteQuery();
|
|
|
194 stmt.Finalize();
|
|
|
195 }
|
|
|
196
|
|
|
197 ccndb.Close();
|
|
|
198 }
|
|
|
199
|
