// Filename   : db.cpp
// Last Change: 21-Jul-2013.
//

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

/* $BHoJ]81<THV9f$+$iHoJ]81<TL>$r<hF@(B */
wxString GetHhsNameByNo( wxString hhsno )
{
    wxString name;

    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 FROM hhs_master WHERE hhsno = ?");
    stmt.Bind( 1, hhsno );
    wxSQLite3ResultSet q = stmt.ExecuteQuery();
    if ( !q.IsNull(0) ) {
        while ( q.NextRow() ) {
            name = q.GetString(0);
        }
    }
    stmt.Finalize();
    hhsdb.Close();

    return name;
}
/* $B%+%J$+$iHoJ]81<T>pJs$r<hF@(B */
wxArrayString GetHhsByKana( 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 );

    wxSQLite3Statement stmt = hhsdb.PrepareStatement("SELECT hhs_no, kana, name, birth, addr FROM hhs_master WHERE kana = ? ORDER BY kana, birth DESC");
    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;
}

/* $BHoJ]81<THV9f$+$i%U%!%$%k%Q%9$r<hF@(B */
wxArrayString GetPathByHhsNo( wxString hhsno )
{
    wxArrayString date_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");
    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 );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return date_path;
}

/* $B3+:EF|$r<hF@(B */
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;
}

/* $B9g5DBN$r<hF@(B */
wxArrayString GetCcnByDate( wxString date ) 
{
    wxArrayString ccn_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 ccn, count(*) FROM ccn WHERE date = ? GROUP BY ccn");
    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 );
        }
    }
    stmt.Finalize();
    ccndb.Close();

    return ccn_cnt;
}

/* $BHoJ]81<THV9f$r<hF@(B */
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;
}

/* $B%$%s%G%C%/%9$r99?7(B */
void UpdateIndex( wxArrayString paths )
{
    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;

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

    ccndb.Close();
}