// Filename   : db.cpp
// Last Change: 11-Sep-2013.
//

#include "db.h"
#include "wx/wxsqlite3.h"

//********** HHS-DB **********//
/* 被保番で被保険者情報を取得 */
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 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 WHERE kana = ? ORDER BY kana, birth;" ); 
    //if ( fuzzy ) ;//*****

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

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

//********** HHS-DB & CCN-DB **********//
/* 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;
}