--- a/src/db.cpp	Sun Aug 04 21:42:49 2013 +0900
+++ b/src/db.cpp	Fri Aug 23 18:32:09 2013 +0900
@@ -1,11 +1,12 @@
 // Filename   : db.cpp
-// Last Change: 02-Aug-2013.
+// Last Change: 23-Aug-2013.
 //
 
 #include "db.h"
 #include "wx/wxsqlite3.h"
 
-/* $BHoJ]HV$GHoJ]81<T>pJs$r<hF@(B */
+//********** HHS-DB **********//
+/* 被保番で被保険者情報を取得 */
 wxString GetHhsInfoByHhsNo( wxString hhsno )
 {
     wxString name, addr;
@@ -14,7 +15,7 @@
     wxSQLite3Database hhsdb;
     hhsdb.Open( gszFile );
 
-    wxSQLite3Statement stmt = hhsdb.PrepareStatement("SELECT name, addr FROM hhs_master WHERE hhsno = ?");
+    wxSQLite3Statement stmt = hhsdb.PrepareStatement( "SELECT name, addr FROM hhs_master WHERE hhsno = ?" );
     stmt.Bind( 1, hhsno );
     wxSQLite3ResultSet q = stmt.ExecuteQuery();
     if ( !q.IsNull(0) ) {
@@ -34,7 +35,7 @@
     }
 }
 
-// $B;aL>%+%J$GHoJ]81<T>pJs$r8!:w(B
+// 氏名カナで被保険者情報を検索
 wxArrayString GetHhsInfoByKana( wxString kana, bool fuzzy )
 {
     wxArrayString data;
@@ -66,33 +67,54 @@
     return data;
 }
 
-/* $BHoJ]81<THV9f$+$i%U%!%$%k%Q%9$r<hF@(B */
+//********** CCN-DB **********//
+/* 被保険者番号からファイルパスを取得 */
 wxArrayString GetPathByHhsNo( wxString hhsno )
 {
-    wxArrayString date_path;
+    wxArrayString path;
 
     wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
     wxSQLite3Database ccndb;
     ccndb.Open( gszFile );
 
-    wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT date, path FROM ccn WHERE hhsno = ? ORDER BY date DESC");
+    wxString sql = wxT( "SELECT path FROM path WHERE hhsno = ? ORDER BY path DESC;" );
+    wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
     stmt.Bind( 1, hhsno );
     wxSQLite3ResultSet q = stmt.ExecuteQuery();
 
     if ( !q.IsNull(0) ) {
-        wxString str;
         while ( q.NextRow() ) {
-            str = q.GetString(0) + "_" + q.GetString(1);
-            date_path.Add( str );
+            path.Add( q.GetString(0) );
         }
     }
     stmt.Finalize();
     ccndb.Close();
 
-    return date_path;
+    return path;
 }
 
-/* $B9g5DBN3+:EF|$r<hF@(B */
+/* 被保険者が審査会にかかったかどうか */
+bool IsHhsJudged( wxString hhsno ) 
+{
+    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
+    wxSQLite3Database ccndb;
+    ccndb.Open( gszFile );
+
+    wxString sql = wxT( "SELECT path FROM path WHERE hhsno = ?;" );
+    wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
+    stmt.Bind( 1, hhsno );
+    wxSQLite3ResultSet q = stmt.ExecuteQuery();
+
+    bool ret = true;
+    if ( q.IsNull(0) ) ret = false;
+
+    stmt.Finalize();
+    ccndb.Close();
+
+    return ret;
+}
+
+/* 合議体開催日を取得 */
 wxArrayString GetCcnDate( void ) 
 {
     wxArrayString date_cnt;
@@ -101,7 +123,7 @@
     wxSQLite3Database ccndb;
     ccndb.Open( gszFile );
 
-    wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT date, count(*) FROM ccn GROUP BY date ORDER BY date desc");
+    wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT date, count(*) FROM ccn GROUP BY date ORDER BY date desc" );
     wxSQLite3ResultSet q = stmt.ExecuteQuery();
 
     wxString str;
@@ -117,33 +139,33 @@
     return date_cnt;
 }
 
-/* $BF|IU$+$i?3::2q$r<hF@(B */
+/* 日付から審査会を取得 */
 wxArrayString GetCcnByDate( wxString date ) 
 {
-    wxArrayString ccn_cnt;
+    wxArrayString data;
 
     wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
     wxSQLite3Database ccndb;
     ccndb.Open( gszFile );
 
-    wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT ccn, count(*) FROM ccn WHERE date = ? GROUP BY ccn");
+    wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT hhsno, path, date FROM path WHERE date = ? ORDER BY path" );
     stmt.Bind( 1, date );
     wxSQLite3ResultSet q = stmt.ExecuteQuery();
 
     wxString str;
     if ( !q.IsNull(0) ) {
         while ( q.NextRow() ) {
-            str = q.GetString(0) + "_" + q.GetString(1);
-            ccn_cnt.Add( str );
+            str = q.GetString(0) + "_" + q.GetString(1) + "_" + q.GetString(2);
+            data.Add( str );
         }
     }
     stmt.Finalize();
     ccndb.Close();
 
-    return ccn_cnt;
+    return data;
 }
 
-/* $B9g5DBN$+$iHoJ]81<THV9f$r<hF@(B */
+/* 合議体から被保険者番号を取得 */
 wxArrayString GetHhsNoByCcn( wxString ccn, wxString date ) 
 {
     wxArrayString hhsno;
@@ -152,7 +174,7 @@
     wxSQLite3Database ccndb;
     ccndb.Open( gszFile );
 
-    wxSQLite3Statement stmt = ccndb.PrepareStatement("SELECT hhsno FROM ccn WHERE ccn = ? AND date = ? ORDER BY hhsno");
+    wxSQLite3Statement stmt = ccndb.PrepareStatement( "SELECT hhsno FROM ccn WHERE ccn = ? AND date = ? ORDER BY hhsno" );
     stmt.Bind( 1, ccn );
     stmt.Bind( 2, date );
     wxSQLite3ResultSet q = stmt.ExecuteQuery();
@@ -168,32 +190,82 @@
     return hhsno;
 }
 
-/* $B%$%s%G%C%/%9$r99?7(B */
-void UpdateIndex( wxArrayString paths )
+/* インデックスを更新 */
+void UpdateIndex( wxString datedir, wxString date )
 {
     wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
     wxSQLite3Database ccndb;
     ccndb.Open( gszFile );
 
-    wxRegEx regex( wxT("^.+(20[0-9]{2})([01][0-9])([0-3][0-9]).(.+).(0[1238]{8})$") );
-    wxSQLite3Statement stmt = ccndb.PrepareStatement("INSERT OR REPLACE INTO ccn VALUES( ?, ?, ?, ? )");
-    wxString date, ccn, hhsno;
+    wxSQLite3Statement stmt = ccndb.PrepareStatement( "DELETE FROM path WHERE date = ?;" );
+    stmt.Bind( 1, date );
+    stmt.ExecuteQuery();
+    stmt.Finalize();
 
-    for ( int i=0; i<paths.GetCount(); i++ ) {
-        date  = paths[i];
-        ccn   = paths[i];
-        hhsno = paths[i];
-        regex.ReplaceAll( &date, wxT("\\1-\\2-\\3") );
-        regex.ReplaceAll( &date, wxT("\\4") );
-        regex.ReplaceAll( &date, wxT("\\5") );
-        stmt.Bind( 1, date );
-        stmt.Bind( 2, ccn );
-        stmt.Bind( 3, hhsno );
-        stmt.Bind( 4, paths[i] );
-        stmt.ExecuteQuery();
-        stmt.Finalize();
+    wxString ccndir;
+    wxDir dated( datedir );
+    if ( !dated.IsOpened() ) {
+        return;
     }
 
+    wxRegEx reSinsei( wxT("^00000") );
+    bool cont = dated.GetFirst( &ccndir, wxEmptyString, wxDIR_DIRS );
+
+    wxProgressDialog pd( wxT("進行状況"), wxT("処理開始..."), 240, NULL, wxPD_APP_MODAL|wxPD_REMAINING_TIME|wxPD_AUTO_HIDE );
+    pd.SetSize( wxSize( 320, 140 ) );
+    int count = 0;
+
+    while ( cont ) {
+
+        wxDir ccnd( datedir + wxFILE_SEP_PATH + ccndir );
+        if ( !ccnd.IsOpened() ) return;
+        wxString hhsdir;
+        bool c = ccnd.GetFirst( &hhsdir, wxEmptyString, wxDIR_DIRS );
+
+        while ( c ) {
+            if ( ! reSinsei.Matches( hhsdir ) ) {
+
+                wxString path = datedir + wxFILE_SEP_PATH + ccndir + wxFILE_SEP_PATH + hhsdir;
+
+                stmt = ccndb.PrepareStatement( "INSERT INTO path VALUES( ?, ?, ?, datetime( 'now', 'localtime' ) );" );
+                stmt.Bind( 1, hhsdir );
+                stmt.Bind( 2, path );
+                stmt.Bind( 3, date );
+                stmt.ExecuteQuery();
+                stmt.Finalize();
+                pd.Update( count++, hhsdir + wxT("＠") + ccndir + wxT("を処理しました．") );
+            }
+            c = ccnd.GetNext( &hhsdir );
+        }
+
+        cont = dated.GetNext( &ccndir );
+    }
     ccndb.Close();
 }
 
+/* DB整合性チェック */
+wxArrayString CheckDBs( void )
+{
+    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
+    wxSQLite3Database ccndb;
+    ccndb.Open( gszFile );
+
+    wxString sql = wxT("ATTACH 'db/hhs.db' AS hhs");
+    wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
+    wxSQLite3ResultSet q = stmt.ExecuteQuery();
+
+    sql = wxT("SELECT hhsno FROM path EXCEPT SELECT hhsno FROM hhs.hhs_master");
+    stmt = ccndb.PrepareStatement( sql );
+    q = stmt.ExecuteQuery();
+
+    wxArrayString result;
+    while ( q.NextRow() ) {
+        result.Add( q.GetString(0) );
+    }
+
+    stmt.Finalize();
+    ccndb.Close();
+
+    return result;
+}
+