// Filename   : db.cpp
// Last Change: 23-May-2014.
//

#include <wx/tokenzr.h>
#include "db.h"
#include "wx/wxsqlite3.h"

//********** HHS-DB **********//
/* 被保険者台帳を更新 */
void UpdateHhs( wxArrayString info )
{
    long n = info.GetCount();

    wxProgressDialog pd( wxT("進行状況"), wxT("処理開始..."), n, NULL, wxPD_APP_MODAL|wxPD_REMAINING_TIME|wxPD_AUTO_HIDE );
    pd.SetSize( wxSize( 320, 140 ) );

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
    wxRemoveFile( gszFile );

    wxSQLite3Database hhsdb;
    hhsdb.Open( gszFile );
    hhsdb.Begin();

    wxString sql = wxT( "PRAGMA foregin_keys=OFF" );
    wxSQLite3Statement stmt = hhsdb.PrepareStatement( sql );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    sql = wxT( "CREATE TABLE 'hhs_master' ( hhsno text PRIMARY KEY, birth text, name text, kana text, addr text, sex text )" );
    stmt = hhsdb.PrepareStatement( sql );
    q = stmt.ExecuteQuery();

    wxString hhsno, birth, name, kana, addr, sex;
    for ( long i = 0; i < n; i++ ) {

        info[i].Replace( wxT("\""), wxEmptyString, true );

        wxStringTokenizer token( info[i], wxT(",") );
        hhsno = token.GetNextToken();
        birth = token.GetNextToken();
        name  = token.GetNextToken();
        kana  = token.GetNextToken();
        addr  = token.GetNextToken();
        sex   = token.GetNextToken();

        stmt = hhsdb.PrepareStatement( "INSERT INTO hhs_master VALUES( ?, ?, ?, ?, ?, ? );" );
        stmt.Bind( 1, hhsno );
        stmt.Bind( 2, birth );
        stmt.Bind( 3, name  );
        stmt.Bind( 4, kana  );
        stmt.Bind( 5, addr  );
        stmt.Bind( 6, sex   );
        stmt.ExecuteQuery();
        stmt.Finalize();

        if ( i % 1000 == 0 ) {
            pd.Update( i, wxString::Format( wxT("%d / %d done."), i, n ) );
        }
    }

    hhsdb.Commit();
    hhsdb.Close();
}

/* 被保番で被保険者情報を取得 */
wxString GetHhsInfoByHhsNo( wxString hhsno )
{
    wxString name, addr;

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
    wxSQLite3Database hhsdb;
    hhsdb.Open( gszFile );

    wxSQLite3Statement stmt = hhsdb.PrepareStatement( "SELECT name, addr FROM hhs_master WHERE hhsno = ?" );
    stmt.Bind( 1, hhsno );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();
    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            name = q.GetString(0);
            addr = q.GetString(1);
        }
    }
    stmt.Finalize();
    hhsdb.Close();

    if ( name.IsEmpty() ) {
        return wxEmptyString;
    }
    else {
        return name + wxT("_") + addr;
    }
}

// 被保険者番号リストから氏名を取得
wxArrayString GetHhsInfoByHhsNoList( wxArrayString hhsno )
{
    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
    wxSQLite3Database hhsdb;
    hhsdb.Open( gszFile );

    wxString sql = wxT( "SELECT name FROM hhs_master WHERE hhsno = ?;" );
    wxSQLite3Statement stmt;
    wxSQLite3ResultSet q;

    wxArrayString result;
    for ( unsigned int i = 0; i < hhsno.GetCount(); i++ ) {
        wxString str = hhsno[i];
        str.Append( wxT("_") );

        stmt = hhsdb.PrepareStatement( sql );
        stmt.Bind( 1, hhsno[i] );
        q = stmt.ExecuteQuery();
        if ( !q.IsNull(0) ) {
            while ( q.NextRow() ) {
                str.Append( q.GetString(0) );
            }
        }

        result.Add( str );
    }
    stmt.Finalize();
    hhsdb.Close();

    return result;
}

// 氏名カナで被保険者情報を検索
wxArrayString GetHhsInfoByKana( wxString kana, bool fuzzy )
{
    wxArrayString data;

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
    wxSQLite3Database hhsdb;
    hhsdb.Open( gszFile );

    wxString sql = wxT( "SELECT hhsno, kana, name, birth, addr FROM hhs_master " );
    if ( fuzzy ) {
        kana = wxT("%") + kana + wxT("%");
        sql += wxT( "WHERE kana LIKE ? ORDER BY kana, birth;" ); 
    }
    else {
        sql += wxT( "WHERE kana = ? ORDER BY kana, birth;" ); 
    }

    wxSQLite3Statement stmt = hhsdb.PrepareStatement( sql );
    stmt.Bind( 1, kana );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    if ( !q.IsNull(0) ) {
        wxString str;
        while ( q.NextRow() ) {
            str = q.GetString(0);
            for ( int i=1; i<5; i++ ) {
                str +=  "_" + q.GetString(i);
            }
            data.Add( str );
        }
    }
    stmt.Finalize();
    hhsdb.Close();

    return data;
}

//********** CCN-DB **********//
/* 被保険者番号からファイルパスを取得 */
wxArrayString GetPathByHhsNo( wxString hhsno )
{
    wxArrayString path;

    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 = ? ORDER BY path DESC;" );
    wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
    stmt.Bind( 1, hhsno );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            path.Add( q.GetString(0) );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return path;
}

// 審査会情報のある被保険者を取得
wxArrayString GetJudgedHhsNo( void )
{
    wxArrayString hhsno;

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
    wxSQLite3Database ccndb;
    ccndb.Open( gszFile );

    //wxString sql = wxT( "SELECT DISTINCT hhsno FROM path;" );
    wxString sql = wxT( "SELECT hhsno FROM path ORDER BY path DESC LIMIT 200;" );
    wxSQLite3Statement stmt = ccndb.PrepareStatement( sql );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            hhsno.Add( q.GetString(0) );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return hhsno;
}

/* 被保険者が審査会にかかったかどうか */
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;

    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, count(*) FROM ccn GROUP BY date ORDER BY date desc" );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    wxString str;
    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            str = q.GetString(0) + "_" + q.GetString(1);
            date_cnt.Add( str );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return date_cnt;
}

/* 日付から審査会を取得 */
wxArrayString GetCcnByDate( wxString date ) 
{
    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 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) + "_" + q.GetString(2);
            data.Add( str );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return data;
}

/* 範囲日時のパスを取得 */
wxArrayString GetPathes( wxString from, wxString to )
{
    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 path FROM path WHERE date >= ? AND date <= ?" );
    stmt.Bind( 1, from );
    stmt.Bind( 2, to   );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();

    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            path.Add( q.GetString(0) );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return path;
}

/* 合議体から被保険者番号を取得 */
wxArrayString GetHhsNoByCcn( wxString ccn, wxString date ) 
{
    wxArrayString hhsno;

    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
    wxSQLite3Database ccndb;
    ccndb.Open( gszFile );

    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();

    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            hhsno.Add( q.GetString(0) );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return hhsno;
}

/* インデックスを更新 */
void UpdateIndex( wxString datadir, wxString date )
{
    wxString gszFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
    wxSQLite3Database ccndb;
    ccndb.Open( gszFile );

    wxSQLite3Statement stmt = ccndb.PrepareStatement( "DELETE FROM path WHERE date = ?;" );
    stmt.Bind( 1, date );
    stmt.ExecuteQuery();
    stmt.Finalize();

    wxString ccndir;
    wxDir dated( datadir );
    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( datadir + 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 = datadir + 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();
}

//********** HHS-DB & CCN-DB **********//
/* DBの更新日時を取得 */
wxArrayString GetLastUpdate( void )
{
    wxArrayString date;
    wxString dbFile;
    wxDateTime t;
    
    dbFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("ccn.db");
    wxFileName ccn( dbFile );
    t = ccn.GetModificationTime();
    date.Add( t.FormatISODate() );

    dbFile = wxGetCwd() + wxFILE_SEP_PATH + wxT("db") + wxFILE_SEP_PATH + wxT("hhs.db");
    wxFileName hhs( dbFile );
    t = hhs.GetModificationTime();
    date.Add( t.FormatISODate() );

    return date;
}

/* 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;
}

// 被保険者番号リストから氏名と最新ファイルパスを取得
wxArrayString GetHhsInfoAndPathByHhsNoList( wxArrayString hhsno )
{
    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();

    wxArrayString result;
    for ( int i = 0; i < hhsno.GetCount(); i++ ) {
        wxString str = hhsno[i];
        str.Append( wxT("_") );

        sql = wxT( "SELECT name FROM hhs.hhs_master WHERE hhsno = ?;" );
        stmt = ccndb.PrepareStatement( sql );
        stmt.Bind( 1, hhsno[i] );
        q = stmt.ExecuteQuery();
        if ( !q.IsNull(0) ) {
            while ( q.NextRow() ) {
                str.Append( q.GetString(0) );
            }
        }
        str.Append( wxT("_") );

        sql = wxT( "SELECT path FROM path WHERE hhsno = ? ORDER BY path DESC LIMIT 1;" );
        stmt = ccndb.PrepareStatement( sql );
        stmt.Bind( 1, hhsno[i] );
        q = stmt.ExecuteQuery();
        if ( !q.IsNull(0) ) {
            while ( q.NextRow() ) {
                str.Append( q.GetString(0) );
            }
        }

        result.Add( str );
    }
    stmt.Finalize();
    ccndb.Close();

    return result;
}