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